October 30, 2012 at 9:15 am
Hi,
I like to know why value from 251-255 are not used in sys.sysindexes. what is the use of these value in sql server.
Thanks.
Kuldeep
October 30, 2012 at 9:20 am
Which column in sys.sysindexes are you talking about?
Also sys.sysindexes is the SQL 2000 view and is included for backwards compatability, you should be using sys.indexes for 2005 onwards.
October 30, 2012 at 9:32 am
In SQL 2008, you can have up to 999 non-clustered indexes. You can confirm that by reviewing the maximum number of nonclustered indexes per table from the following maximum capacity site on MSDN: http://msdn.microsoft.com/en-us/library/ms143432.aspx
Run the following in the AdventureWorks2008R2 database and it will confirm that you end up with 999 non-clustered indexes on the Customer table.
declare @ctr int, @stmt varchar(500)
select @ctr = 1
while @ctr <= 999
BEGIN
select @stmt = 'CREATE NONCLUSTERED INDEX [idx' + convert(varchar(10), @ctr) + '] ON [dbo].[customer] ([CustomerID] ASC)'
exec (@stmt)
select @ctr = @ctr + 1
END
select * from sys.indexes
where object_id = object_id('customer')
October 30, 2012 at 9:40 am
You also see the 999 non-clustered indexes if you reference the legacy sysindexes compatibility view.
select * from sysindexes
where id = object_id('customer')
In my sample script, I get index ID values up to 1005 with a gap between 250 and 256. 251-255 must be reserved, I know that 255 is reserved for LOB data, but I am not sure what 251-254 are reserved for.
October 30, 2012 at 10:28 am
It's historical. 255 used to be reserved for LOB (it's not any longer). It's basically legacy from SQL 2000 and before.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 30, 2012 at 1:39 pm
you all are correct,
but I want to know the usage of 251-255 these id used by indid column in sys.sysindexes....
October 30, 2012 at 1:52 pm
251-254 are reserved and are not used as non-clustered index id's. Prior to SQL Server 2005, you could only have 249 non-clustered indexes per table. SQL 2000 uses an index id of 0 for the Heap, 1 for the Clustered Index and index id's of 2-250 for the non-clustered. 255 is used for BLOB data and 251-254 were reserved for internal SQL Server use.
The best explanation I can find on line is from Paul Randal but he never really says what 251-254 are reserved for: http://www.sqlskills.com/blogs/paul/post/Inside-the-Storage-Engine-IAM-pages-IAM-chains-and-allocation-units.aspx
IAM chains
If we continued to grow the file and fill up the table then eventually we'd need another IAM page to map the next GAM interval. This is where an IAM chain comes in. It's a linked-list of IAM pages that track the space allocated to a single entity. The linked-list is not sorted at all - IAM pages are appended to it in the order that they're needed. The IAM pages within the list are numbered, again, in the order that they were appended to the list.
Definition of 'entity' - what uses an IAM chain? This is vastly different in SQL Server 2000 and 2005.
In SQL Server 2000, a single IAM chain is used for each:
•Heap or clustered index
?A table can only have one or the other, not both. These have index IDs of 0 and 1 respectively.
•Non-clustered index
?These have index IDs from 2 to 250 (i.e. you can only have 249 of them)
•Table's complete LOB storage
?For LOB columns (text, ntext, image) in the heap or clustered index. This is sometimes called the 'text index' and has a fixed index ID of 255.
This gives a maximum of 251 IAM chains per object in SQL Server 2000 and before. I usually generalize and say that in SQL Server 2000, there's one IAM chain per index (which fits nicely if you remember that IAM stands for Index Allocation Map).
October 30, 2012 at 2:06 pm
panwar.jt (10/30/2012)
you all are correct,but I want to know the usage of 251-255 these id used by indid column in sys.sysindexes....
None. The fact that they are not used for indexes is because historically 255 was reserved and there were only 249 NC indexes. That's all it is, nothing secret or profound.
p.s. sys.sysindexes is deprecated, should not be used any longer, is included only for backward compatibility with SQL 2000.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply