Will my SQL Server database fill up?

Original: Will my SQL Server database fill up?

Overview

Today, a client asked me a pretty interesting question. I am using a SQL SERVER 2008 database, which currently has more than 130 g of database, with a table with a record number of more than 310 million, which takes up more than 50 g. Does the table in the SQL SERVER database have a limit on the number of rows and columns? Will suddenly one day, beyond this limit, my system can not be used?

Maximum capacity

Whether SQL SERVER will be full, mainly in the maximum capacity of the database. Includes the maximum number of rows and maximum columns, and others. For details, we can look at the following list:

SQL Server Database Engine objects	Maximum Size/Quantity
Number of bytes per GROUP by and ORDER by	8,060
Number of bytes per index key 2	900
Number of bytes per foreign key	900
Number of bytes per primary key	900
Number of bytes per row 8	8,060
The number of bytes per varchar (max), varbinary (max), XML, text, or image column	2^31-1
Number of characters per ntext or nvarchar (max) column	2^30-1
Number of clustered indexes per data table	1
Number of columns in GROUP by and ORDER by	Limited by byte count only
Number of columns or expressions in the GROUP by with CUBE or with ROLLUP statement	10
Number of columns per index key 7	16
Number of columns per foreign key	16
Number of columns per primary key	16
Number of columns per non-wide table	1,024
Number of columns per wide table	30,000
Number of columns per SELECT statement	4,096
Number of columns per INSERT statement	4096
Number of connections per client	Configured maximum value of the connection
Database size	524,272 TB
Number of databases per instance of SQL Server	32,767
Number of filegroups per database	32,767
Number of files per database	32,767
File Size (data)	TB
File Size (log)	2 TB
Number of foreign key table references per table 4	253
Identifier length (in characters counted)	128
Number of instances per computer	50 instances on a stand-alone server. 25 instances on a failover cluster.
Length of the string containing the SQL statement (batch size) 1	65,536 * Network packet size
Number of locks per connection	Maximum number of locks per server
Number of locks per instance of SQL Server 5	Limited by memory only
Number of nested stored procedure levels 6	32
Number of nested subqueries	32
Number of nested trigger layers	32
Number of nonclustered indexes per data table	999
The number of distinct expressions in the GROUP by clause if any of the following clauses exist: CUBE, ROLLUP, GROUPING sets, with CUBE, with ROLLUP	32
Number of grouping sets generated by operators in the GROUP BY clause	4,096
Number of parameters per stored procedure	2,100
Number of arguments per user-defined function	2,100
Number of REFERENCE per data table	253
Number of rows per data table	Limited by Available storage space
Number of tables per database 3	Limited by the number of objects in the database
Number of partitions per partitioned table or index	1,000
Number of statistics bars for non-indexed columns	30,000
Number of tables per SELECT statement	Limited by available resources only
Number of triggers per table 3	Limited by the number of objects in the database
Number of columns per UPDATE statement (wide table)	4096
user connections	32,767
XML Index	249

Maximum number of rows

As you can see from the table, the number of rows per table is limited by the available space. The available space is 524,272 TB for the entire database. It's very big. It is worth noting, however, that the size limit for a single data file is 16TB.

Self-increment column

Just now the friend in the garden mentioned some special situation, what if the self-increment on the table?

Create TABLE Test (
ID INT IDENTITY (2147483647,1),
Name VARCHAR (100)
)

INSERT into Test (name) VALUES (' Owen ')
INSERT into Test (name) VALUES (' Zeng ')

Sure enough, it overflowed. Therefore, for tables with self-increment columns, the limit on the number of rows on the table is related to the data type of the custom increment column, the starting value, and the step size.

You can use the bigint type for this situation. prevent exceeding the line limit.

Maximum number of columns

For the number of columns, the maximum number of columns for a non-wide table is 1024. For relational databases This value is also very large, it is hard to imagine that your number of columns exceeds this value.

Of course, this 1024 is only a theoretical value, as with the above self-increment column or other conditions that restrict the number of columns. Take a look at the following example:

CREATE TABLE Test (id char (4030), name char (4030), title char (10))

My watch only 3 columns won't let me create,,, this database is too heartless,,,,,

What is this for? From the previous table we can see that the number of bytes is 8060. These include 7 bytes of internal overhead.

Therefore, the column number limit is also related to the length of the column you define.

Other

For other capacity restrictions will not be mentioned, follow up if there is a time to add.

Reference

Official document: https://technet.microsoft.com/zh-cn/library/ms143432 (v=sql.100)

Will my SQL Server database fill up?