Variable length columns are pushed off-row if the maximum sizes for all the columns in the table exceeds 8,060 bytes this action is a compile-time decision. Memory-optimized tables on SQL Server 2016 (13.x) and later support off-row storage. For more information, see Large Row Support. Only a 24-byte root is stored in the main record for variable length columns pushed out of row. SQL Server supports row-overflow storage, which enables variable length columns to be pushed off-row. However, the maximum declared sizes of all key columns for all indexes on a table, plus any additional fixed-length columns in the table, must fit in the 8,060 bytes. It doesn't matter if the actual data in the key columns would be shorter than the maximum declared sizes.įor a hash index key, there's no hard limit on size.įor indexes on memory-optimized tables, there's no concept of included columns, since all indexes inherently cover all columns.įor a memory-optimized table, even though the row size is 8,060 bytes, some variable-length columns can be physically stored outside those 8,060 bytes. On a memory-optimized table, a nonclustered index can't have key columns whose maximum declared sizes exceed 2,500 bytes. No limit for a hash index, as long as all index keys fit in-row. The non-key columns might help some queries perform better.īytes per index key for memory-optimized tablesĢ,500 bytes for a nonclustered index. In a nonclustered index, you can include extra non-key columns, and they don't count against the size limit of the key. However, the combined sizes of the data in those columns can never exceed the limit. You can define a key using variable-length columns whose maximum sizes add up to more than the limit. For a nonclustered index key, the maximum is 1,700 bytes. The maximum number of bytes in a clustered index key can't exceed 900. For SQL Server 2014 (12.x) and earlier, all versions supported 900 bytes for all index types. The default packet size is 4 KB, and is controlled by the network packet size configuration option.īyte length of a string containing Transact-SQL statements (batch size)ĩ00 bytes for a clustered index. Network packet size is the size of the tabular data stream (TDS) packets used to communicate between the relational Database Engine and applications. Maximum values of various objects defined in SQL Server databases, or referenced in Transact-SQL statements. If you want to view edition limits, see Compute capacity limits by edition of SQL Server.įor SQL Server 2014 (12.x), see Maximum capacity specifications for SQL Server 2014. This article shows maximum sizes and numbers of various objects defined in SQL Server 2016 (13.x) and later.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |