Every now and then i see a question pop up that asks what is the max this or that in sql server?
Well here are some maximum values:
| Bytes per short string column | 8,000 |
|
| Bytes per GROUP BY, ORDER BY | 8,060 |
|
| Columns in GROUP BY, ORDER BY | Limited only by number of bytes |
|
| Bytes per index key | 900 |
|
| Bytes per foreign key | 900 |
|
| Bytes per primary key | 900 |
|
| Bytes per row | 8,060 |
|
| Bytes per varchar(max), varbinary(max), xml, text, or image column | 2^31-1 |
|
| Characters per ntext or nvarchar(max) column | 2^30-1 |
|
| Clustered indexes per table | 1 |
|
| Columns per index key | 16 |
|
| Columns per foreign key | 16 |
|
| Columns per primary key | 16 |
|
| Columns per base table | 1,024 |
|
| Columns per SELECT statement | 4,096 |
|
| Columns per INSERT statement | 1,024 |
|
| Connections per client | 32,767 |
|
| Database size | 1,048,516 terabytes |
|
| Databases per instance of SQL Server | 32,767 |
|
| Filegroups per database | 32,767 |
|
| Files per database | 32,767 |
|
| File size (data) | 16 terabytes |
|
| File size (log) | 2 terabytes |
|
| Foreign key table references per table | 253 |
|
| Identifier length (in characters) | 128 |
|
| Instances per computer | 50 (Workgroup Edition only 16) |
|
| Locks per connection | Maximum locks per server |
|
| Locks per instance of SQL Server | Up to 2,147,483,647 |
|
| Nested stored procedure levels | 32 |
|
| Nested subqueries | 32 |
|
| Nested trigger levels | 32 |
|
| Nonclustered indexes per table | 249 |
|
| Parameters per stored procedure | 2,100 |
|
| Parameters per user-defined function | 2,100 |
|
| REFERENCES per table | 253 |
|
| Rows per table | Limited by available storage |
|
| Tables per database | Limited by number of objects in a database |
|
| Partitions per partitioned table or index | 1,000 |
|
| Statistics on non-indexed columns | 2,000 |
|
| Tables per SELECT statement | 256 |
|
| Triggers per table | Limited by number of objects in a database |
|
| UNIQUE indexes or constraints per table | 249 nonclustered and 1 clustered |
|
| User connections | 32,767 |
|
| XML indexes | 249 |
|
For a complete listing go here.
