5

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.

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.