What are the maximum number of columns in a table in SQL Server

table.hovertable {
font-family: verdana,arial,sans-serif;
font-size:11px;
color:#333333;
border-width: 1px;
border-color: #999999;
border-collapse: collapse;
}
table.hovertable th {
background-color:#c3dde0;
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}
table.hovertable tr {
background-color:#d4e3e5;
}
table.hovertable td {
border-width: 1px;
padding: 8px;
border-style: solid;
border-color: #a9c6c9;
}

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