Table with 2950 columns ???

Question

Table with 2950 columns ???

jbalbo

SSCertifiable

Points: 7583
More actions
December 19, 2023 at 4:01 pm

#4349000

Hi
I need to import some data from a spreadsheet with 2950 columns on a regular basis
Is it possible to create a table with that many columns?

Thanks
January 26, 2024 at 4:34 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

No can do in SQL Server according to the docs: https://learn.microsoft.com/en-us/sql/sql-server/maximum-capacity-specifications-for-sql-server?view=sql-server-ver16

Those state we have 1024 columns max in a table.

You'll need to break it up into multiple tables; probably want to look at normalizing the data.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Ant-Green SSC Guru Points: 113507 More actions · Answer 2

Well you “could” do 30,000 columns in a table IF you marked them all as “SPARSE” columns.

Now the question is “should” you mark them as sparse.

What does this excel file and data look like.

Does all 2950 columns all have data in them for every row? If yes then NO don’t mark them as sparse and you’ll need to split the excel into multiple tables.

But if most 85-95% of the fields in excel are empty, then you could get away with it (that % is a rough guesstimate as each data type has a limit where it’s best to mark it as sparse or not, but it’s a general ballpark figure to say where sparse is good or not).

jbalbo SSCertifiable Points: 7583 More actions · Answer 3

I Think I'll have to split them into multiple tables. Thank You for the advice...

pietlinden SSC Guru Points: 63512 More actions · Answer 4

A table with that many columns makes me wonder what the table is describing. it's hard to imagine storing that many bits of information about each instance of a thing. So I'd be suspicious about the design.