Need to created table with 2000 columns in sql server 2005

Question

Post reply

Need to created table with 2000 columns in sql server 2005

Sathiya priya

Mr or Mrs. 500

Points: 560
More actions
October 8, 2008 at 3:28 am

#382989

Hi ,
In sql server 2005 i want create table with 2000 columns. Is it Possible in any way?
Regards,
Priya

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 1

Why would you even want to do this? Seriously. What is the design rationale for it?

Sathiya priya Mr or Mrs. 500 Points: 560 More actions · Answer 2

Sathiya priya

Mr or Mrs. 500

Points: 560

October 8, 2008 at 3:40 am

#882152

It is a requirement from client 🙁 .............

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 3

There is a maximum of 1024 columns per table. You would ned to create two tables with a common key and then select across both tables.

Is the table design fully normalised?

Also be aware of other issues.

see http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

In addition to the maximum number of columns, you cannot make a record size larger than 8k.

You really need to explain to your client that this is a really bad idea. If they don't want to listen, I would try to find a better client.

Sathiya priya Mr or Mrs. 500 Points: 560 More actions · Answer 5

Thanks Rjohal,Michael for your reply. I wish i could change the client but :(.

We can't have it in two tables, Our application already bulit in.i read Sql server 2008 supports 30000 columns(wide tables). But there are some restriction like 40% of the table should be null.

I can't assure my table will be having 40% null values.

I need to suggest the client whether with sql server 2005 itself is possible or i need to confirm on SQL-Server 2008 :(.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 6

SQL 2008 allows 30000 columns if a table is 'wide'. A wide table is one that contains a set of sparse columns (defined as a columnset, see Books Online for more details). The number of non-sparse columns is still 1024 and the max size of the row is still 8000 bytes.

If most of the columns are populated, then you are going to run into the max row size and there is no way around this.

Why can you not split the table in half?

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 7

Unless you can really ensure you will never hit the 8k limit for a record size, even SQL 2008 is not going to help you.

If you want to use SQL Server for the database, the application needs to be fixed. The alternative is to find a database engine that will allow that many columns and a large enough record size.

GSquared SSC Guru Points: 260824 More actions · Answer 8

I don't think the database is going to be the only problem with this. I can't imagine trying to make an application deal with this. Or a report.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 9

Of course, if you really, really need 2000 "columns", you could probably fake it with XML data types. I can just imagine the grinding noises that will come from the performance that will cause, but it could probably be done.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jerry Hung SSChampion Points: 12908 More actions · Answer 10

Kalen has a good article just recently

As I am working on my new SQL Server 2008 internals book, I am finding many test situations in which I need to create a table with LOTS of columns. First of all, you're probably aware of the new SPARSE column feature that allows you to have up to 30,000 columns in a table! I did some testing with sparse columns, but just today I realized I had never actually created a table with more than the old limit of 1024. In addition, the new row compression had some special tricks for dealing with lots of columns, and groups columns into clusters of 30 columns each. (You'll have to wait for the new book to get the details.)

Geek City: Too Many Columns!

http://sqlblog.com/blogs/kalen_delaney/archive/2008/09/20/too-many-columns.aspx

SQLServerNewbieMCITP: Database Administrator SQL Server 2005