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.