What are the maximum number of columns in a table in SQL Server
Posted on : 16-04-2012 | By : Devi Prasad | In : SQL Server Basics, SQLServerMaximumLimitations
Share:
| Tweet |
0
What are the maximum number of Columns in a table in SQL Server?
SQL Server supports a maximum of 1,024 Columns in a nonwide table.
SQL Server supports a maximum of 30,000 Columns in a wide table.
SQL Server supports a maximum of 1,024 Columns in a base table.
| Max Capacity For | SQL Server 2012 | SQL Server 2008 R2 | SQL Server 2008 | SQL Server 2005 |
|---|---|---|---|---|
| maximum number of Columns in a nonwide table in SQL Server | 1,024 Columns | 1,024 Columns | 1,024 Columns | – |
| maximum number of Columns in a wide table in SQL Server | 30,000 Columns | 30,000 Columns | 30,000 Columns | – |
| maximum number of Columns in a base table in SQL Server | – | – | – | 1,024 |
Now if you want to test the maximum number of columns limitation in a table you can use the below test script.
Test Script:
--DROP TABLE TestCreateMaximumColumnsTable
DECLARE @i INT = 2
DECLARE @SQLTABLE VARCHAR(MAX)
SET @SQLTABLE = 'CREATE TABLE TestCreateMaximumColumnsTable(COLUMN1 BIT)'
EXEC(@SQLTABLE)
WHILE @i <= 1025
BEGIN
SELECT @SQLTABLE = 'ALTER TABLE TestCreateMaximumColumnsTable
ADD COLUMN'+CAST(@i AS VARCHAR(5))+' BIT'
EXEC(@SQLTABLE)
SET @i = @i + 1
END
When the script tries to create 1025th column you get the below error:
Msg 1702, Level 16, State 1, Line 1
CREATE TABLE failed because column ‘COLUMN1025’ in table ‘TestCreateMaximumColumnsTable’ exceeds the maximum of 1024 columns.
http://msdn.microsoft.com/en-us/library/ms143432.aspx
References : Devi Prasad (sqlserverlearner.com)
Need Help On SQL Server?
Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.
Do you like my blog?
If you liked reading this blog, please help spread the word by sharing this blog with your friends.
