Sql-server – the usage of sys.sysrowsets in SQL Server

sql server

I just wonder what is the usage of sys.sysrowsets in SQL Server? Microsoft said it “Exists in every database. Contains a row for each partition rowset for an index or a heap.” But what is a partition?

Also what is the meaning of idmajor and idminor for this table? I try to find the object ID of a table(246486340) and then execute the query:

Select * from sys.sysrowsets where idmajor = 246486340

The results are two records:

rowsetid           ownertype       idmajor idminor  numpart status  fgidfs  rcrows

72057603504865280   1              246486340     1     1        6     0     2582    
72057603612213248   1              246486340     6     1        2     0     2582

But I think there should only be one record with that major id.

Best Answer

Its an underlying table for sys.partitions, which returns the same information as

SELECT * FROM sys.partitions

But what is a partition?

Refer to Partitioned Tables and Indexes on MSDN.

idmajor is the column name that's commonly known as object_id

idminor is the index_id.

Lets do some testing now: I have used AdventureWorks 2012 from CodePlex.

USE AdventureWorks2012_Data;
GO
DECLARE @MyID int;
SET @MyID = (SELECT OBJECT_ID('HumanResources.Employee',
'U'));
SELECT name, object_id, type_desc
FROM sys.objects
WHERE name = OBJECT_NAME(@MyID);

Output:

name        object_id   type_desc

Employee    1237579447  USER_TABLE

Lets query the sys.partitions view now:

SELECT partion_id, object_id, index_id FROM sys.partitions 
WHERE object_id = '1237579447'

Output:

partition_id      Object_id      index_id

72057594045136896   1237579447  1

72057594050510848   1237579447  2

72057594050576384   1237579447  3

72057594050641920   1237579447  5

72057594050707456   1237579447  6

72057594050772992   1237579447  7

If we look at the sys.indexes view:

SELECT object_id, name, index_id from sys.indexes where object_id = '1237579447'

Output:

Object_id           name                                index_id

1237579447  PK_Employee_BusinessEntityID                    1

1237579447  IX_Employee_OrganizationNode                    2

1237579447  IX_Employee_OrganizationLevel_OrganizationNode  3

1237579447  AK_Employee_LoginID                             5

1237579447  AK_Employee_NationalIDNumber                    6

1237579447  AK_Employee_rowguid                             7

In the above output, index_id is just an id for the indexes. 1 for Clustered index and the others (2-7) for other non clustered indexes.

The object_id for all indexes under a table is same as the object_id for that table.

Lets take a look at the sys.sysrowsets table:

USE AdventureWorks2012_data
GO
SELECT rowsetid, idmajor, idminor from sys.sysrowsets 
WHERE idmajor = '1237579447'

I used the object_id for the HumanResources.Employee table to filter data.

Output:

rowsetid               idmajor         idminor
-------------------- ----------- -----------
   72057594045136896  1237579447           1

   72057594050510848  1237579447           2

   72057594050576384  1237579447           3

   72057594050641920  1237579447           5

   72057594050707456  1237579447           6

   72057594050772992  1237579447           7

From this its clear that object_id or idmajor is the same for the table and all the indexes under it and idminor is nothing but the index_id for an index.

Related Question