The Number 999 Related to SQL SERVER

Posted by Sahoomanoj27 under Sql Server on 2/6/2012 | Points: 10 | Views : 896 | Status : [Member] | Replies : 4
999 - this number is related to something interesting with SQL Server - with what this number is associated in SQL Server.




Responses

Posted by: Avdesh on: 2/7/2012 [Member] Starter | Points: 25

Up
0
Down
you can have up to 999 non clustered index per table from SQL Server 2008 onwards but in previous versions this was limited to 249.
THANKS REGARDS
AVDESH

Sahoomanoj27, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/22/2012 [Member] Bronze | Points: 25

Up
0
Down
Nonclustered indexes per table-999

Refer this link
http://msdn.microsoft.com/en-us/library/ms143432.aspx
http://blog.sqlauthority.com/2009/06/29/sql-server-maximum-number-of-index-per-table/

Sahoomanoj27, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 6/23/2012 [Member] Silver | Points: 25

Up
0
Down
999 Nonclustered Index per table in sqlserver 2008

Here is an example, where we are creating 999 indexes on a single table.

use tempdb
go
CREATE TABLE IndexTest
(
column1 int identity
)
go
-- Creation of Multiple indexes
DECLARE @IndexID smallint,
@Exec nvarchar(500)
SELECT @IndexID = 1
WHILE @IndexID <= 999
BEGIN
SELECT @Exec = 'CREATE INDEX [Test' + convert(varchar(5), @IndexID) + '] ON IndexTest (column1)'
EXEC(@Exec)
SELECT @IndexID = @IndexID + 1
END
go
-- Select number of indexes on a table
SELECT count(*) FROM sys.indexes
WHERE object_id = object_id('IndexTest')
go
-- DROP TABLE
go
Source http://www.sqlserver-training.com/how-many-non-clustered-index-can-be-created-on-a-single-table/-

Sahoomanoj27, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: CGN007 on: 7/31/2012 [Member] Silver | Points: 25

Up
0
Down
please mark it as answer....That helps other who search the same...

Sahoomanoj27, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response