|
|
The following tables specify the maximum sizes and numbers of various objects defined in SQL Server 2012 components, and compared against the maximum sizes and number of various objects defined in SQL Server 2008 and SQL Server 2008 R2 components.
|
Database Engine Objects
The following table specifies the maximum sizes and number of various objects defined in SQL Server databases or referenced in Transact-SQL statements.
| Batch size |
65,536 * Network Packet Size |
65,536 * Network Packet Size |
65,536 * Network Packet Size |
| Bytes per short string column |
8,000 |
8,000 |
8,000 |
| Bytes per GROUP BY ORDER BY |
8,060 |
8,060 |
8,060 |
| Bytes per index key |
900 |
900 |
900 |
| Bytes per foreign key |
900 |
900 |
900 |
| Bytes per primary key |
900 |
900 |
900 |
| Bytes per row |
8,060 |
8,060 |
8,060 |
| Bytes in source text of a stored procedure. |
Lesser of batch size or 250 MB |
Lesser of batch size or 250 MB |
Lesser of batch size or 250 MB |
| Bytes per VARCHAR(MAX), VARBINARY(MAX), XML, TEXT, or IMAGE column |
2^31-1 |
2^31-1 |
2^31-1 |
| Characters per NTEXT or NVARCHAR(MAX) column |
2^30-1 |
2^30-1 |
2^30-1 |
| Clustered indexes per table |
1 |
1 |
1 |
| Columns in GROUP BY, ORDER BY |
Limited only by number of bytes |
Limited only by number of bytes |
Limited only by number of bytes |
| Columns or expressions in a GROUP BY WITH CUBE or WITH ROLLUP statement |
10 |
10 |
10 |
| Columns per index key |
16 |
16 |
16 |
| Columns per foreign key |
16 |
16 |
16 |
| Columns per primary key |
16 |
16 |
16 |
| Columns per nonwide table |
1,024 |
1,024 |
1,024 |
| Columns per wide table |
30,000 |
30,000 |
30,000 |
| Columns per SELECT statement |
4,096 |
4,096 |
4,096 |
| Columns per INSERT statement |
4,096 |
4,096 |
4,096 |
| Connections per client |
Maximum value of configured connections |
Maximum value of configured connections |
Maximum value of configured connections |
| Database size |
524,272 terabytes |
524,272 terabytes |
524,272 terabytes |
| Databases per instance of SQL Server |
32,767 |
32,767 |
32,767 |
| Filegroups per database |
32,767 |
32,767 |
32,767 |
| Files per database |
32,767 |
32,767 |
32,767 |
| File size (data) |
16 terabytes |
16 terabytes |
16 terabytes |
| File size (log) |
2 terabytes |
2 terabytes |
2 terabytes |
| Foreign key table references per table |
253 |
253 |
253 |
| Identifier length (in characters) |
128 |
128 |
128 |
| Instances per computer |
50 instances on a stand-alone server for all SQL Server editions. |
50 instances on a stand-alone server for all SQL Server editions. |
50 instances on a stand-alone server for all SQL Server editions. |
| Length of a string containing SQL statements (batch size) |
65,536 * Network packet size |
65,536 * Network packet size |
65,536 * Network packet size |
| Locks per connection |
Maximum locks per server |
Maximum locks per server |
Maximum locks per server |
| Locks per instance of SQL Server |
Up to 2,147,483,647 |
Up to 2,147,483,647 |
Up to 2,147,483,647 |
| Nested stored procedure levels |
32 |
32 |
32 |
| Nested subqueries |
32 |
32 |
32 |
| Nested trigger levels |
32 |
32 |
32 |
| Nonclustered indexes per table |
999 |
999 |
999 |
| Number of distinct expressions in the GROUP BY clause when any of the following are present: CUBE, ROLLUP, GROUPING SETS, WITH CUBE, WITH ROLLUP |
32 |
32 |
32 |
| Number of grouping sets generated by operators in the GROUP BY clause |
4,096 |
4,096 |
4,096 |
| Parameters per stored procedure |
2,100 |
2,100 |
2,100 |
| Parameters per user-defined function |
2,100 |
2,100 |
2,100 |
| REFERENCES per table |
253 |
253 |
253 |
| Rows per table |
Limited by available storage |
Limited by available storage |
Limited by available storage |
| Tables per database |
Limited by number of objects in a database |
Limited by number of objects in a database |
Limited by number of objects in a database |
| Partitions per partitioned table or index |
1,000 |
1,000 |
15,000 |
| Statistics on non-indexed columns |
30,000 |
30,000 |
30,000 |
| Tables per SELECT statement |
Limited only by available resources |
Limited only by available resources |
Limited only by available resources |
| Triggers per table |
Limited by number of objects in a database. |
Limited by number of objects in a database. |
Limited by number of objects in a database. |
| Columns per UPDATE statement (Wide Tables) |
4,096 |
4,096 |
4,096 |
| User connections |
32,767 |
32,767 |
32,767 |
| XML Indexes |
249 |
249 |
249 |
SQL Server Utility Objects
The following table specifies the maximum sizes and number of various objects that were tested in the SQL Server Utility.
| Computers (physical computers or virtual machines) per SQL Server Utility |
100 |
100 |
100 |
| Instances of SQL Server per computer |
5 |
5 |
5 |
| Total number of instances of SQL Server per SQL Server Utility |
200 |
200 |
200 |
| User databases per instance of SQL Server, including data-tier applications |
50 |
50 |
50 |
| Total number of user databases per SQL Server Utility |
1,000 |
1,000 |
1,000 |
| File groups per database |
1 |
1 |
1 |
| Data files per file group |
1 |
1 |
1 |
| Log files per database |
1 |
1 |
1 |
| Volumes per computer |
3 |
3 |
3 |
SQL Server Data-Tier Application Objects
The following table specifies the maximum sizes and number of various objects that were tested in the SQL Server data-tier applications (DAC).
| Databases per DAC |
1 |
1 |
1 |
| Objects per DAC |
Limited by the number of objects in a database, or available memory. |
Limited by the number of objects in a database, or available memory. |
Limited by the number of objects in a database, or available memory. |
SQL Server Replication Objects
The following table specifies the maximum sizes and number of various objects defined in SQL Server Replication.
| Articles (merge publication) |
256 |
256 |
256 |
| Articles (snapshot or transactional publication) |
32,767 |
32,767 |
32,767 |
| Columns in a table (merge publication) |
246 |
246 |
246 |
| Columns in a table (SQL Server snapshot or transactional publication) |
1,000 |
1,000 |
1,000 |
| Columns in a table (Oracle snapshot or transactional publication) |
995 |
995 |
995 |
| Bytes for a column used in a row filter (merge publication) |
1,024 |
1,024 |
1,024 |
| Bytes for a column used in a row filter (snapshot or transactional publication) |
8,000 |
8,000 |
8,000 |
|