rkumar
Active Member
Can somebody clarify if there is any maximum number of index that we can add on a table. We were debating it to be 15 index on a table.
Raj
Implementing E-One 8.10, SQL
Raj
Implementing E-One 8.10, SQL
WhippingBoy
VIP Member
I voted for 'Index'. 
WhippingBoy
VIP Member
seriously though. . . I found this out on the web, so I'm not sure how accurate it is:
These are the general SQL Server 97/2000 characteristics:
Batch size = 65,536 * Network Packet Size
Bytes per short string column = 8,000
Bytes per text, ntext,or image column = 2 GB-2
Bytes per index = 900
Bytes per primary key = 900
Bytes per foreign key = 900
Bytes per row = 8,060
Clustered indexes per table = 1
Bytes in source text of a stored procedure = Lesser of batch size or 250 MB
Columns per index = 16
Columns per primary key = 16
Columns per foreign key = 16
Columns per base table = 1,024
Columns per INSERT statement = 1,024
Columns per SELECT statement = 4,096
Database size = 1,048,516 TB
Databases per instance of SQL Server = 32,767
Filegroups per database = 256
File size (data) = 32 TB
Files per database = 32,767
Identifier length (in characters) = 128
Nested stored procedure levels = 32
Locks per connection = Max. locks per server
Nested subqueries = 32
Nested trigger levels = 32
Objects in a database = 2,147,483,647
Nonclustered indexes per table = 249
Parameters per stored procedure = 1,024
REFERENCES per table = 253
Rows per table = Limited by available storage
Tables per database = Limited by number of objects in a database
Tables per SELECT statement = 256
UNIQUE indexes or constraints per table = 249 nonclustered and 1 clustered
Triggers per table = Limited by number of objects in a database
These are the general SQL Server 97/2000 characteristics:
Batch size = 65,536 * Network Packet Size
Bytes per short string column = 8,000
Bytes per text, ntext,or image column = 2 GB-2
Bytes per index = 900
Bytes per primary key = 900
Bytes per foreign key = 900
Bytes per row = 8,060
Clustered indexes per table = 1
Bytes in source text of a stored procedure = Lesser of batch size or 250 MB
Columns per index = 16
Columns per primary key = 16
Columns per foreign key = 16
Columns per base table = 1,024
Columns per INSERT statement = 1,024
Columns per SELECT statement = 4,096
Database size = 1,048,516 TB
Databases per instance of SQL Server = 32,767
Filegroups per database = 256
File size (data) = 32 TB
Files per database = 32,767
Identifier length (in characters) = 128
Nested stored procedure levels = 32
Locks per connection = Max. locks per server
Nested subqueries = 32
Nested trigger levels = 32
Objects in a database = 2,147,483,647
Nonclustered indexes per table = 249
Parameters per stored procedure = 1,024
REFERENCES per table = 253
Rows per table = Limited by available storage
Tables per database = Limited by number of objects in a database
Tables per SELECT statement = 256
UNIQUE indexes or constraints per table = 249 nonclustered and 1 clustered
Triggers per table = Limited by number of objects in a database
rkumar
Active Member
Thanks for the response. I am sorry (vote option - I am not sure how that got in). If anybody had replied to this posting can you please reply back with your response. Just for testing I started adding index,upto 18 and Table Design Aid did not stop me. Does that mean I can have as many as I want, I am sure the performance will be hit with more indices.
WhippingBoy
VIP Member
How high does the speedometer go on your car?
rkumar
Active Member
I have no idea what you are talking about. If you want help, please do so, if not just ...
Similar threads
