Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
MS SQL SERVER: LIMITS FOR DATABASE RECORDS AND FILE SIZE
- Thread starter chequi
- Start date
From what I understand, the only maximum file size and record number for a SQL Server database is only limited by your hardware...<br><br>I've heard that query performance on a table starts to degrade once it has a couple million records or so. <br><br>Here's a couple limitations I found for SQL Server 7.0:<br><br> Maximum number of tables : 2 <b>BILLION</b><br> Maximum bytes per row: 8060<br> Maximum non-clustered indexes per table: 249<br> Maximum clustered indexes per table: 1<br> Maximum cells returned in a query 2,147,483,647<br><br>Hope this helps....<br>
From SQL Server BOL
Sorry about formatting, Right Hand Column is SQLS 7
Maximum Capacity Specifications
This table specifies the maximum sizes and numbers of various objects defined in Microsoft® SQL Server™ databases, or referenced in Transact-SQL statements.
Maximum sizes/numbers
Object SQL Server 6.5 SQL Server 7.0
Batch size 128 KB 536* Network Packet Size
Bytes per short string column
255 8000
Bytes per text, ntext, or image column
2 GB-2 2 GB-2
Bytes per GROUP BY, ORDER BY
900 8060
Bytes per index 900 900
Bytes per foreign key 900 900
Bytes per primary key 900 900
Bytes per row 1962 8060
Bytes in source text of a stored procedure
65025 Lesser of batch size or 250 MB
Clustered indexes per table 1 1
Columns in GROUP BY, ORDER BY
16 Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement 10 10
Columns per index 16 16
Columns per foreign key 16 16
Columns per primary key 16 16
Columns per base table 250 1024
Columns per SELECT statement 4096 4096
Columns per INSERT statement 250 1024
Connections per client Max.
value of configured connections for both
Database size 1 TB 1,048,516 TB
Databases per server 32,767 32,767
Filegroups per database N/A 256
Files per database 32 32,767
File size (data) 32 GB 32 TB
File size (log) 32 GB 4 TB
Foreign key table references per table 16 253
Identifier length (in characters) 30 128
Locks per connection Max. locks per server(both)
Locks per server 2,147,483,647( both static or
40% of SQL Server memory (dynamic))
Nested stored procedure levels 16 32
Nested subqueries 16 32
Nested trigger levels 16 32
Nonclustered indexes per table 249 249
Objects concurrently open in a server*
2 billion 2,147,483,647
Objects in a database* 2 billion 2,147,483,647
Parameters per stored procedure 255 1024
REFERENCES per table 31 63
Rows per table
Limited by available storage for both
SQL string length (batch size) 128 KB 128* TDS packet size
Tables per database
2 billion Limited by number of
objects in a database
Tables per SELECT statement 16 256
Triggers per table 3 Limited by number of objects in a database
UNIQUE indexes or constraints per table 249 249 nonclustered and 1 clustered
* Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.
(c) 1988-98 Microsoft Corporation. All Rights Reserved.
Sorry about formatting, Right Hand Column is SQLS 7
Maximum Capacity Specifications
This table specifies the maximum sizes and numbers of various objects defined in Microsoft® SQL Server™ databases, or referenced in Transact-SQL statements.
Maximum sizes/numbers
Object SQL Server 6.5 SQL Server 7.0
Batch size 128 KB 536* Network Packet Size
Bytes per short string column
255 8000
Bytes per text, ntext, or image column
2 GB-2 2 GB-2
Bytes per GROUP BY, ORDER BY
900 8060
Bytes per index 900 900
Bytes per foreign key 900 900
Bytes per primary key 900 900
Bytes per row 1962 8060
Bytes in source text of a stored procedure
65025 Lesser of batch size or 250 MB
Clustered indexes per table 1 1
Columns in GROUP BY, ORDER BY
16 Limited only by number of bytes
Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement 10 10
Columns per index 16 16
Columns per foreign key 16 16
Columns per primary key 16 16
Columns per base table 250 1024
Columns per SELECT statement 4096 4096
Columns per INSERT statement 250 1024
Connections per client Max.
value of configured connections for both
Database size 1 TB 1,048,516 TB
Databases per server 32,767 32,767
Filegroups per database N/A 256
Files per database 32 32,767
File size (data) 32 GB 32 TB
File size (log) 32 GB 4 TB
Foreign key table references per table 16 253
Identifier length (in characters) 30 128
Locks per connection Max. locks per server(both)
Locks per server 2,147,483,647( both static or
40% of SQL Server memory (dynamic))
Nested stored procedure levels 16 32
Nested subqueries 16 32
Nested trigger levels 16 32
Nonclustered indexes per table 249 249
Objects concurrently open in a server*
2 billion 2,147,483,647
Objects in a database* 2 billion 2,147,483,647
Parameters per stored procedure 255 1024
REFERENCES per table 31 63
Rows per table
Limited by available storage for both
SQL string length (batch size) 128 KB 128* TDS packet size
Tables per database
2 billion Limited by number of
objects in a database
Tables per SELECT statement 16 256
Triggers per table 3 Limited by number of objects in a database
UNIQUE indexes or constraints per table 249 249 nonclustered and 1 clustered
* Database objects include all tables, views, stored procedures, extended stored procedures, triggers, rules, defaults, and constraints. The sum of the number of all these objects in a database cannot exceed 2,147,483,647.
(c) 1988-98 Microsoft Corporation. All Rights Reserved.
- Status
Similar threads
- Question
- Locked
- Question