Size of Database

Question

HI, can i know maximum number of columns in one table and the max size of a database with SQL server ?

Javeed M Shaikh · Answer

what version of SQL Server you are looking at? Below is what i got from MSDN, also refer this http://msdn.microsoft.com/en-us/library/aa933149(v=sql.80).aspx SQL Server Database Engine object Maximum sizes/numbers SQL Server (32-bit) Maximum sizes/numbers SQL Server (64-bit) Batch size1 65,536 * Network Packet Size 65,536 * Network Packet Size Bytes per short string column 8,000 8,000 Bytes per GROUP BY, ORDER BY 8,060 8,060 Bytes per index key2 900 900 Bytes per foreign key 900 900 Bytes per primary key 900 900 Bytes per row8 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 Bytes per varchar(max) , varbinary(max) , xml , text , or image column 2^31-1 2^31-1 Characters per ntext or nvarchar(max) column 2^30-1 2^30-1 Clustered indexes per table 1 1 Columns in GROUP BY, ORDER BY 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 Columns per index key7 16 16 Columns per foreign key 16 16 Columns per primary key 16 16 Columns per nonwide table 1,024 1,024 Columns per wide table 30,000 30,000 Columns per SELECT statement 4,096 4,096 Columns per INSERT statement 4096 4096 Connections per client Maximum value of configured connections Maximum value of configured connections Database size 524,272 terabytes 524,272 terabytes Databases per instance of SQL Server 32,767 32,767 Filegroups per database 32,767 32,767 Files per database 32,767 32,767 File size (data) 16 terabytes 16 terabytes File size (log) 2 terabytes 2 terabytes Foreign key table references per table4 253 253 Identifier length (in characters) 128 128 Instances per computer 50 instances on a stand-alone server for all SQL Server editions. SQL Server supports 25 instances on a failover cluster. 50 instances on a stand-alone server. 25 instances on a failover cluster. Length of a string containing SQL statements (batch size)1 65,536 * Network packet size 65,536 * Network packet size Locks per connection Maximum locks per server Maximum locks per server Locks per instance of SQL Server5 Up to 2,147,483,647 Limited only by memory Nested stored procedure levels6 32 32 Nested subqueries 32 32 Nested trigger levels 32 32 Nonclustered indexes per table 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 Number of grouping sets generated by operators in the GROUP BY clause 4,096 4,096 Parameters per stored procedure 2,100 2,100 Parameters per user-defined function 2,100 2,100 REFERENCES per table 253 253 Rows per table Limited by available storage Limited by available storage Tables per database3 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 Statistics on non-indexed columns 30,000 30,000 Tables per SELECT statement Limited only by available resources Limited only by available resources Triggers per table3 Limited by number of objects in a database Limited by number of objects in a database Columns per UPDATE statement (Wide Tables) 4096 4096 User connections 32,767 32,767 XML indexes 249 249

Size of Database

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (1)