how many number of columns can create with one Table in sql server

Posted by Johnseelan under Sql Server on 3/7/2011 | Points: 10 | Views : 10251 | Status : [Member] | Replies : 4
how many number of columns can create with one Table in sql server
please can any one help me
i need to create max number of columns with one table.




Responses

Posted by: Karthikanbarasan on: 3/7/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

Up
0
Down
1024 columns can be created i guess!!!

Thanks
Karthik
www.f5Debug.net

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: SheoNarayan on: 3/7/2011 [Administrator] HonoraryPlatinum | Points: 25

Up
0
Down
For more details on maximum capacity specification for SQL Server, you can visit http://msdn.microsoft.com/en-us/library/ms143432.aspx.

Thanks

Regards,
Sheo Narayan
http://www.dotnetfunda.com

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: PandianS on: 3/7/2011 [Member] [MVP] Silver | Points: 25

Up
0
Down
Hi

Maximum column can be 1024.

Do you want to confirm it ?
DECLARE @nTable	INT,

@Sql NVARCHAR(MAX)

SELECT @nTable =1, @Sql ='CREATE TABLE TB_TABLE1('

WHILE(@nTable<=1025)
BEGIN
SELECT @Sql = @Sql + 'Col' + CAST(@nTable AS VARCHAR(4)) + ' INT,'
SELECT @nTable = @nTable +1
END

SELECT @Sql = LEFT(@Sql,LEN(@Sql)-1) + ')'

EXEC(@Sql)
You'll get an error
Msg 1702, Level 16, State 1, Line 1

CREATE TABLE failed because column 'Col1025' in table 'TB_TABLE1' exceeds the maximum of 1024 columns.
So, The maximum column can be 1024

Cheers
www.sqlserverbuddy.blogspot.com

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Madhu.b.rokkam on: 3/7/2011 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Even though the max number is 1024.. Try to normalize your tables for better performance and easy readable.

Thanks and Regards
Madhu

Johnseelan, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response