SQL Server Logbook-will my database be full?

Source: Internet
Author: User
Tags failover

Background

Today, a client asked me a pretty interesting question. I am using a database of SQL SERVER 2008. The database is now more than 130 g. One of the tables has

More than 310 million of records, taking up more than 50 g. Does a table in the SQL SERVER database have a limit on the number of rows and columns? Will it suddenly one day, over this limit

My system is not going to work?

Analysis

This issue is actually explained in Microsoft's official documentation. It's just that everyone may not be looking. The exact results are as follows:

SQL Server Database Engine objects

Maximum size/Quantity-SQL Server (32-bit)

Maximum size/Quantity-SQL Server (64-bit)

Number of bytes per GROUP by and ORDER by

8,060

8,060

Number of bytes per index key 2

900

900

Number of bytes per foreign key

900

900

Number of bytes per primary key

900

900

Number of bytes per row 8

8,060

8,060

The number of bytes per varchar (max), varbinary (max), XML, text, or image column

2^31-1

2^31-1

Number of characters per ntext or nvarchar (max) column

2^30-1

2^30-1

Number of clustered indexes per data table

1

1

Number of columns in GROUP by and ORDER by

Limited by byte count only

Limited by byte count only

Number of columns or expressions in the GROUP by with CUBE or with ROLLUP statement

10

10

Number of columns per index key 7

16

16

Number of columns per foreign key

16

16

Number of columns per primary key

16

16

Number of columns per non-wide table

1,024

1,024

Number of columns per wide table

30,000

30,000

Number of columns per SELECT statement

4,096

4,096

Number of columns per INSERT statement

4096

4096

Number of connections per client

Configured maximum value of the connection

Configured maximum value of the connection

Database size

524,272 TB

524,272 TB

Number of databases per instance of SQL Server

32,767

32,767

Number of filegroups per database

32,767

32,767

Number of files per database

32,767

32,767

File Size (data)

TB

TB

File Size (log)

2 TB

2 TB

Number of foreign key table references per table 4

253

253

Identifier length (in characters counted)

128

128

Number of instances per computer

All SQL Server versions (except Workgroup) are 50 instances on a standalone server. The Workgroup supports a maximum of 16 instances per computer.

SQL Server supports 25 instances on a failover cluster.

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

65,536 * Network packet size

Number of locks per connection

Maximum number of locks per server

Maximum number of locks per server

Number of locks per instance of SQL Server 5

Up to 2,147,483,647

Limited by memory only

Number of nested stored procedure levels 6

32

32

Number of nested subqueries

32

32

Number of nested trigger layers

32

32

Number of nonclustered indexes per data table

999

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

32

Number of grouping sets generated by operators in the GROUP BY clause

4,096

4,096

Number of parameters per stored procedure

2,100

2,100

Number of arguments per user-defined function

2,100

2,100

Number of REFERENCE per data table

253

253

Number of rows per data table

Limited by Available storage space

Limited by Available storage space

Number of tables per database 3

Limited by the number of objects in the database

Limited by the number of objects in the database

Number of partitions per partitioned table or index

1,000

1,000

Number of statistics bars for non-indexed columns

30,000

30,000

Number of tables per SELECT statement

Limited by available resources only

Limited by available resources only

Number of triggers per table 3

Limited by the number of objects in the database

Limited by the number of objects in the database

Number of columns per UPDATE statement (wide table)

4096

4096

user connections

32,767

32,767

XML Index

249

249

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. However, it is worth noting that

The size limit for a single data file is 16TB.

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.

Other items in the table are not described. It's better to look at it when you need it.

Reference

Address of official website: https://technet.microsoft.com/zh-cn/library/ms143432 (v=sql.100)

Note: This article is original, welcome reprint, please in the article page obvious location give this article link!
If you think this article is not bad please click on the lower right corner of the recommendation , thank you very much!

SQL Server Logbook-will my database be full?

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.