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
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:
[sql]
–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
[/sql]
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
Reference : Devi Prasad (sqlserverlearner.com)
Do you like my blog?
If you liked reading this blog, please help spread the word by sharing this blog with your friends.
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.

Tags: CREATE TABLE failed because column in table exceeds the maximum of 1024 columns., max columns in sql server, max no of columns in sql server 2008, max number of columns in a table in SQL Server, max number of columns in a table in SQL Server 2005, max number of columns in a table in SQL Server 2008, max number of columns in a table in SQL Server 2012, max number of columns in sql server, max number of columns in sql server 2005, max number of columns in sql server 2005 table, max number of columns in sql server 2008, max number of columns in sql server 2008 r2, max number of columns in sql server 2008 table, max number of columns in sql server 2012, max number of columns in sql server 2012 table, maximum number of Columns in a base table in SQL Server, maximum number of Columns in a base table in SQL Server 2005, maximum number of Columns in a nonwide table in SQL Server, maximum number of Columns in a nonwide table in SQL Server 2008, maximum number of Columns in a nonwide table in SQL Server 2008 r2, maximum number of Columns in a nonwide table in SQL Server 2012, maximum number of Columns in a wide table in SQL Server, maximum number of Columns in a wide table in SQL Server 2008, maximum number of Columns in a wide table in SQL Server 2008 r2, maximum number of Columns in a wide table in SQL Server 2012, maximum number of columns in sql, maximum number of columns in sql server 2005, maximum number of columns in sql server 2005 table, maximum number of columns in sql server 2008, maximum number of columns in sql server 2008 r2, maximum number of columns in sql server 2008 r2 table, maximum number of columns in sql server 2008 table, maximum number of columns in sql server 2012, maximum number of columns in sql server 2012 table, maximum number of columns in sql server table, maximum number of columns per table in sql server, Msg 1702 Level 16 State 1, Msg 1702 Level 16 State 1 Line 1, what are the maximum number of columns in a table in SQL Server 2005, what are the maximum number of columns in a table in SQL Server 2005 express, what are the maximum number of columns in a table in SQL Server 2008, what are the maximum number of columns in a table in SQL Server 2008 r2, what are the maximum number of columns in a table in SQL Server 2012, what are the maximum number of columns in a table in SQL Server express, what is the maximum number of columns per table in sql server, what is the maximum number of columns per table in sql server 2005, what is the maximum number of columns per table in sql server 2005 express, what is the maximum number of columns per table in sql server 2008, what is the maximum number of columns per table in sql server 2008 r2, what is the maximum number of columns per table in sql server 2012