Table column count competition

[Vasily Tserekh]

I have a table in the application i am developing of 155 columns i dare anyone to beat me:mad: ps. the table is already in 3rd normal form

[GuyThiebaut]

Erm... :^) One database I worked on had a table that had to be split into two as there were more than 255 columns that were required for each row.... It's not as rare as it may sound particularly when you are dealing with something as complex as the human body(it was a medical database). That said - I am sure there are better ways of representing data than having so many columns populated for each row...

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[Vasily Tserekh]

guess I am one the 2nd place so far!!

[PIEBALDconsult]

GuyThiebaut wrote:

required for each row

GuyThiebaut wrote:

medical database

I'm not convinced -- if the table had columns for right and left arm etc., what did you do for patients with missing arms? Leave NULLs? I'd prefer to have different tables for different body parts. :-D

[lewax00]

GuyThiebaut wrote:

I am sure there are better ways of representing data

I would imagine in that case you could have broken it down into sections like specific body parts/systems. But then again I have no idea what the data looked like, it may not have been that simple (or not clearly divided enough).

[AspDotNetDev]

Darn, highest I could find was 84, using this query:

SELECT
t.Name AS [Table Name],
COUNT(*) AS [Column Count]
FROM sys.Tables AS t
JOIN sys.Columns AS C
ON t.Object_ID = c.Object_ID
GROUP BY
t.Name
ORDER BY
COUNT(*) DESC

Credit to Pinal Dave for most of the code.

Thou mewling ill-breeding pignut!

[Mike Hankey]

and how would you represent a person that is a dick head?

VS2010/Atmel Studio 6.0 ToDo Manager Extension
Version 3.0 now available. There is no place like 127.0.0.1

[AspDotNetDev]

I'd add a record to the [Brendan Fraser] table.

Thou mewling ill-breeding pignut!

[GuyThiebaut]

:laugh: :laugh: :laugh: +5

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

[dan sh]

Assuming you use SQL 2008, you are still 29845 short.

"The worst code you'll come across is code you wrote last year.", wizardzz[^]

[jeron1]

AspDotNetDev wrote:

[Brendan Fraser] table.

Congratulations! You have just won the 'Came Out of Left Field Reference of the Day' award (COOLFRODA). :-D

[PIEBALDconsult]

Referential integrity would prevent that.

[Mike Hankey]

So would birth control!

VS2010/Atmel Studio 6.0 ToDo Manager Extension
Version 3.0 now available. There is no place like 127.0.0.1

[PIEBALDconsult]

SELECT TOP 1 * FROM
(
SELECT s.Name AS [Schema name]
, t.Name AS [Table Name]
, COUNT(*) AS [Column Count]
FROM sys.Tables AS t
JOIN sys.Schemas AS s
ON T.schema_id=s.schema_id
JOIN sys.Columns AS C
ON t.Object_ID = c.Object_ID
GROUP BY s.Name,t.Name
) T
ORDER BY [Column Count] DESC

Shows me 107.

[PIEBALDconsult]

Too late for that.

[Mike Hankey]

I'd recommend a lobotomy, but I'm afraid Mr. Fraiser has already had his!

VS2010/Atmel Studio 6.0 ToDo Manager Extension
Version 3.0 now available. There is no place like 127.0.0.1

[lewax00]

Murder is just late term birth control.

[Ennis Ray Lynch Jr]

Move it to the fourth normal and get rid of the nulls.

Need custom software developed? I do custom programming based primarily on MS tools with an emphasis on C# development and consulting. I also do Android Programming as I find it a refreshing break from the MS. "And they, since they Were not the one dead, turned to their affairs" -- Robert Frost

[Jeremy Hutchinson]

We have a table with 289 columns. It's in our insurance reserves calculating database, and I have no idea what the table actually does, and I don't think I want to know (there are 7 tables with more than 140 columns)

My Blog[^] Chess Tactics for WP7[^]

[GuyThiebaut]

Yes there are better ways of representing the data - it all depends on what you are going to do with it. If you are just looking at a simple full table scan then a wide row can be the best approach. The difficulty is that when you design your tables inevitably six months later there will be a better way to store the data based on how you now want to query it. In the end you just do the best you can with the information you have at the time and rely on the database engine to do the optimisation for you later - if things get really slow you can always de-normalise your data...

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens