The segmap Column

segmap shows the storage that is permitted for the database fragment it represents.

You control the bit values in this mask using stored procedures for segment management. The valid bit’s numbers in the mask come from syssegments in the local database. (Your “local” database is the database you are currently using: either your default database from login, or the database you most recently used with use database.)

Use sp_addsegment to create additional segments. If you create segments in the model database, these segments exist in all databases you subsequently create. If you create them in any other database, they exist only for that database. Different segment names in different databases can have the same segment number. For example, newseg1 in database testdb and mysegment in database mydb can both have segment number 4.

The segmap column is a bitmask linked to the segment column in the user database’s syssegments table. Since the logsegment in each user database is segment 2, and these user databases have their logs on separate devices, segmap contains 4 (2²) for the devices named in the log on statement and 3 for the data segment that holds the system segment (2⁰ = 1) + default segment (2¹ = 2).

Some possible values for segments containing data or logs are:

Values higher than 7 indicate user-defined segments. The segmap column is explained more fully in the segments tutorial section in “Creating and Using Segments.”

select dbid, lstart, segmap, name as 'segment name'
from syssegments s, master..sysusages u
where u.segmap & power(2,s.segment) != 0
and dbid = db_id()
order by 1,2

dbid   lstart    segmap       segment name
-----   ------    --------     --------------
4       0         3           system
4       0         3           default
4       5120      4           logsegment
4       7680      3           system
4       7680       3           default

This example shows that disk fragment for lstart value 0 and the fragment for lstart value 7680 use segments system number 0 and default number 1, while the fragment for lstart value 5120 uses segment logsegment number 2. This database was created using both the on and log on clauses of create database, and was then extended once using the on clause of alter database.

Because the sysusages segmap uses an int datatype, it can contain only 32 bits, so no database can hold more than 32 segments (numbered 0 - 31). Because segmap is a signed quantity (that is, it can display both positive and negative numbers), segment 31 is perceived as a very large negative number, so the query above generates an arithmetic overflow when you use it in a database that uses segment 31.