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 : 12209 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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.

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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

0	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

0	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

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

0	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

Latest Posts