November 5, 2012 at 12:03 pm
hi,
i need to create table with 1000 column with varchar(100), for every column.
is there any way i can do it faster,instead of doing it manually and writing each of the column
thanks
November 5, 2012 at 12:15 pm
harri.reddy (11/5/2012)
hi,i need to create table with 1000 column with varchar(100), for every column.
is there any way i can do it faster,instead of doing it manually and writing each of the column
thanks
Sheesh that sounds like some normalization would do you wonders. But the easy way is to let sql build most of the create statement for you with a tally table.
select 'Somecol_' + cast(N as varchar(4)) + ' varchar(100), '
from Tally
where N <= 1000 --or < 1000 if your tally starts with 0
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 5, 2012 at 12:37 pm
The other issue could be running over 8060 bytes for a row. its possible since a varchar(100) can take up as much as 102 bytes. SQL Server will throw a warning but will allow you to create the table however if a row you INSERT (OR if you make an UPDATE) is over 8060 bytes it will fail.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 5, 2012 at 12:45 pm
capnhector (11/5/2012)
The other issue could be running over 8060 bytes for a row. its possible since a varchar(100) can take up as much as 102 bytes. SQL Server will throw a warning but will allow you to create the table however if a row you INSERT (OR if you make an UPDATE) is over 8060 bytes it will fail.
It'll work on SQL 2008 due to row overflow. Probably be way slower than desired or expected, but it'll work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 5, 2012 at 12:46 pm
Also given the sheer number of columns you are going to have a number of these that are mostly null. You should take a look at sparse columns.
http://msdn.microsoft.com/en-us/library/cc280604%28v=sql.105%29.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply