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:

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

(Visited 613 times, 1 visits today)



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.




Write a comment



Tags: , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,