table.hovertable {
\nfont-family: verdana,arial,sans-serif;
\nfont-size:11px;
\ncolor:#333333;
\nborder-width: 1px;
\nborder-color: #999999;
\nborder-collapse: collapse;
\n}
\ntable.hovertable th {
\nbackground-color:#c3dde0;
\nborder-width: 1px;
\npadding: 8px;
\nborder-style: solid;
\nborder-color: #a9c6c9;
\n}
\ntable.hovertable tr {
\nbackground-color:#d4e3e5;
\n}
\ntable.hovertable td {
\nborder-width: 1px;
\npadding: 8px;
\nborder-style: solid;
\nborder-color: #a9c6c9;
\n}<\/p>\n
What are the maximum number of Columns in a table in SQL Server?<\/b> \nNow if you want to test the maximum number of columns limitation in a table you can use the below test script.<\/p>\n Test Script:<\/strong> SET @SQLTABLE = ‘CREATE TABLE TestCreateMaximumColumnsTable(COLUMN1 BIT)’<\/p>\n EXEC(@SQLTABLE) When the script tries to create 1025th column you get the below error:
\nSQL Server supports a maximum of 1,024 Columns in a nonwide table.
\nSQL Server supports a maximum of 30,000 Columns in a wide table.
\nSQL Server supports a maximum of 1,024 Columns in a base table.
\n<\/p>\n\n\n
\n Max Capacity For<\/th>\n SQL Server 2012<\/th>\n SQL Server 2008 R2<\/th>\n SQL Server 2008<\/th>\n SQL Server 2005<\/th>\n<\/tr>\n \n \nmaximum number of Columns in a nonwide table in SQL Server\n<\/td>\n \n1,024 Columns\n<\/td>\n \n1,024 Columns\n<\/td>\n \n1,024 Columns\n<\/td>\n \n–\n<\/td>\n<\/tr>\n \n \nmaximum number of Columns in a wide table in SQL Server\n<\/td>\n \n30,000 Columns\n<\/td>\n \n30,000 Columns\n<\/td>\n \n30,000 Columns\n<\/td>\n \n–\n<\/td>\n<\/tr>\n \n \nmaximum number of Columns in a base table in SQL Server\n<\/td>\n \n–\n<\/td>\n \n–\n<\/td>\n \n–\n<\/td>\n \n1,024\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n[sql]
\n–DROP TABLE TestCreateMaximumColumnsTable
\nDECLARE @i INT = 2
\nDECLARE @SQLTABLE VARCHAR(MAX) <\/p>\n
\nWHILE @i <= 1025
\nBEGIN
\nSELECT @SQLTABLE = ‘ALTER TABLE TestCreateMaximumColumnsTable
\n ADD COLUMN’+CAST(@i AS VARCHAR(5))+’ BIT’
\nEXEC(@SQLTABLE)
\nSET @i = @i + 1
\nEND
\n[\/sql]<\/p>\n
\n
\nMsg 1702, Level 16, State 1, Line 1
\nCREATE TABLE failed because column ‘COLUMN1025’ in table ‘TestCreateMaximumColumnsTable’ exceeds the maximum of 1024 columns.
\n<\/font><\/p>\n