RE: When is too many columns too many columns?

SSC Guru

Points: 1003863

January 12, 2018 at 6:29 pm

#1975820

Kevlarmpowered - Friday, January 12, 2018 3:25 PM
I know you can have some insane number of columns in SQL server... but just because you could, doesn't mean you should. Right now I am battling with a table that has almost 900 columns. 34 of those columns are dedicated to integer values which define what user is defined to what part of the process. 280 columns are defined to statuses of the particular thing. It is actually 140 datetime values, but when it was built, it was built to split datetime into two columns, one for date and one for time.
Locking is the first thing that comes to mind as a high traffic table is going to lock the entire thing up if you are updating one or many of the columns...

There's actually nothing wrong with a 900 column table IF the rules of normalization were applied... which also usually means that you won't have a table with 900 columns. 😉

I've worked with tables with similar insane numbers of columns. Most of those had to deal with CDRs (Call Detail Records). For those, it made no sense to split the columns off into separate tables.

If you status/datetime columns have a lot of NULLs in them, it may be worthwhile to build a "sister" table in the form of an EAV to store those statuses. A whole lot of people will tell you that EAVs are the wrong way to go but this is one of those places where they can really shine if you put some thought into it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)