SQL Server limits, done the way a record book does them. Not what the documentation says, but what happened when I tried one more. Eleven records, each attempted on SQL Server 2025 build 17.0.4085.5, each with the exact error text at the edge. One documented figure turned out to be wrong on this build, by a factor of three. One test of mine measured the wrong thing.
Every record below has three parts. The attempt that passed, the attempt that failed, and SQL Server’s own words when it did. If a number is in a box, I hit it.
Record 1: columns in one table
1,024
1,024 BIT columns created. 1,025 refused.
Msg 1702: CREATE TABLE failed because column 'c1025' in table 'L_cols1025' exceeds the maximum of 1024 columns.
Record 2: characters in an identifier
128
A table named with 128 letters was created. 129 refused. Note for the record: QUOTENAME returns NULL past 128. My first attempt at this test ran nothing and reported success. The edge had to be built with square brackets by hand.
Msg 103: The identifier that starts with 'bbbb...' is too long. Maximum length is 128.
Record 3: bytes in a fixed-length row
8,060, of which 7 are overhead
CHAR(8000) + CHAR(49) + INT is 8,053 bytes of data and was accepted. One more byte and SQL Server counted for me.
Msg 1701: Creating or altering table 'L_row8054' failed because the minimum row size would be 8061, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
Record 4: varchar(n)
8,000
varchar(8000) created. varchar(8001) refused.
Msg 131: The size (8001) given to the column 'v' exceeds the maximum allowed for any data type (8000).
Record 5: key columns in one index
32
A 32-column key was created. 33 refused.
Msg 1904: The index 'ix33' on table 'dbo.L_keys' has 33 columns in the key list. The maximum limit for index key column list is 32.
Record 6: bytes in a nonclustered index key
1,700
CHAR(1700) indexed. CHAR(1701) refused.
Msg 1944: Index 'ix1701' was not created because the index key size is at least 1701 bytes. The nonclustered index key size cannot exceed 1700 bytes.
Record 7: parameters in one stored procedure
2,100
A procedure with 2,100 parameters compiled. 2,101 refused. Nobody should ever find this out the hard way, and somebody has.
Msg 180: There are too many parameters in this CREATE PROCEDURE statement. The maximum number is 2100.
Record 8: columns in a SELECT list
4,096, with a catch
4,097 columns in the outer select list refused. 4,097 columns inside a derived table passed. The limit is checked on the outer list. And a SELECT INTO of 4,096 columns fails before it gets there, on record 1.
Msg 1056: The number of elements in the select list exceeds the maximum allowed number of 4096 elements.
Record 9: nonclustered indexes on one table
999
999 indexes on the same column created, one at a time. The thousandth refused.
Msg 1910: Could not create nonclustered index 'ix1000' because it exceeds the maximum of 999 allowed per table or view.
Where the documentation and the build part company
Record 10: levels of subquery nesting
100 nested subqueries (documented: 32)
The documented limit is 32 levels. A chain of 101 SELECT layers, which is 100 nested derived-table subqueries, passed. 102 layers refused. The documented figure is off by a factor of three on this build, in the reader’s favour.
Msg 191: Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries.
Record 11: tables in one SELECT
at least 257 (documented: limited by available resources)
Older material quotes 256. The current documentation says the limit is set by available resources, and my result agrees with it. A 257-table join compiled. I tried 300 next and the optimiser was still compiling the join after 504 seconds, so I stopped it. The edge is at least 257 and I did not reach it.
A record I withdrew
I created 254 foreign keys on one table, all pointing at the same parent, and they all passed. Then I read the documentation properly. The 253 limit counts the distinct tables one table may reference. The 10,000 figure from 2016 is the number of tables that may reference one table. My test pointed every key at one parent, so it measured neither. It’s out of the book.
What the SQL Server limits are for
Not for hitting. Nobody should be near 999 indexes or 2,100 parameters. A table with 1,024 columns is a spreadsheet with a database habit.
They’re for knowing which of the numbers you were taught are still true. One of them wasn’t, on this build. One I had wrong myself, and one test I’d have sworn was right was measuring something else.
Trust the error message. It’s the only limit that’s always current.
If your schema is closer to these edges than you’d like, that’s a sign of a different problem. I’d like to see it.
A documented limit is not a fact, it is a claim, and the error message is the fact.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
