I am designing a table for a financial application and I have a situation
where there are more than 100 different attributes of an entity to be
persisted in the database. All these attributes depend directly and
completely on the entity's identity ( which is the primary key of the table
).The question I have is : What is the downside if any of a table with , lets
say, 120 columns ? The principles of normalization alone do not rule out the
table deisgn in my question.

Hi
).The question I have is : What is the downside if any of a table with ,
lets
> say, 120 columns ?

1) Read ability
2) Maintance
3) could be Performance also at some point

Its hard to with DESIGN issue at this forum, because only you know
what exactly business requirements should be...



"Bhaskar" <Bhaskar@discussions.microsoft.com> wrote in message
news:EFCEE672-AB4D-41E0-A00A-6FA2C1FBC0D4@microsoft.com...
>
> I am designing a table for a financial application and I have a situation
> where there are more than 100 different attributes of an entity to be
> persisted in the database. All these attributes depend directly and
> completely on the entity's identity ( which is the primary key of the
> table
> ).The question I have is : What is the downside if any of a table with ,
> lets
> say, 120 columns ? The principles of normalization alone do not rule out
> the
> table deisgn in my question.

"Uri Dimant" wrote:

> Hi
> ).The question I have is : What is the downside if any of a table with ,
> lets
> > say, 120 columns ?
>
> 1) Read ability
> 2) Maintance
> 3) could be Performance also at some point
>
> Its hard to with DESIGN issue at this forum, because only you know
> what exactly business requirements should be...
>
>
>
> "Bhaskar" <Bhaskar@discussions.microsoft.com> wrote in message
> news:EFCEE672-AB4D-41E0-A00A-6FA2C1FBC0D4@microsoft.com...
> >
> > I am designing a table for a financial application and I have a situation
> > where there are more than 100 different attributes of an entity to be
> > persisted in the database. All these attributes depend directly and
> > completely on the entity's identity ( which is the primary key of the
> > table
> > ).The question I have is : What is the downside if any of a table with ,
> > lets
> > say, 120 columns ? The principles of normalization alone do not rule out
> > the
> > table deisgn in my question.
>
>
Hi Uri , Thanks for the reply. No , I am not particulalry seeking the design
of the table , just interested to know the drwabacks of a table with very
large number of columns. Could you elaborate on the performance point ? When
and how exactly does the performance get affected by the huge column number
in tha table ? I am never going to do a select * kind of thing.

Hi
>Could you elaborate on the performance point ? When
> and how exactly does the performance get affected by the huge column
> number
> in tha table?

> I am never going to do a select * kind of thing.
Very , good that what I was thinking in terms of performance as well

Locks on table (particularly table lock) will consume more memory than
small table....


It depends on what kind of queries you are ruinnig against the table. Let
say you run SELECT statement and each time number of columns you return
has changed. Well, you need good indexes , right? And if you need to
'cover' all columns you return in order to get rid of BOOKMARK lookups
you create a covering index , but we do not want its too large? Sure you
cannot create n index to cover all columns :-))))

Well the above might be related to the maintanace more than performance




"Bhaskar" <Bhaskar@discussions.microsoft.com> wrote in message
news:5C362056-2C2E-4C5D-9116-0A2616665A79@microsoft.com...
>
>
> "Uri Dimant" wrote:
>
>> Hi
>> ).The question I have is : What is the downside if any of a table with ,
>> lets
>> > say, 120 columns ?
>>
>> 1) Read ability
>> 2) Maintance
>> 3) could be Performance also at some point
>>
>> Its hard to with DESIGN issue at this forum, because only you know
>> what exactly business requirements should be...
>>
>>
>>
>> "Bhaskar" <Bhaskar@discussions.microsoft.com> wrote in message
>> news:EFCEE672-AB4D-41E0-A00A-6FA2C1FBC0D4@microsoft.com...
>> >
>> > I am designing a table for a financial application and I have a
>> > situation
>> > where there are more than 100 different attributes of an entity to be
>> > persisted in the database. All these attributes depend directly and
>> > completely on the entity's identity ( which is the primary key of the
>> > table
>> > ).The question I have is : What is the downside if any of a table with
>> > ,
>> > lets
>> > say, 120 columns ? The principles of normalization alone do not rule
>> > out
>> > the
>> > table deisgn in my question.
>>
>>
> Hi Uri , Thanks for the reply. No , I am not particulalry seeking the
> design
> of the table , just interested to know the drwabacks of a table with very
> large number of columns. Could you elaborate on the performance point ?
> When
> and how exactly does the performance get affected by the huge column
> number
> in tha table ? I am never going to do a select * kind of thing.

On Thu, 4 Sep 2008 04:33:01 -0700, Bhaskar wrote:

> When
>and how exactly does the performance get affected by the huge column number
>in tha table ?

SQL Server stores data in pages of approximately 8K in size. If more
bytes are required per row, less rows fit in a single data page. So more
pages have to be read (either from cache or -very slow!- from disk) for
each query.

And a smaller part of the table will be able to fit in cache.

I'd double-check the design first. That many columns in a single table
is extremely rare in a well designed and completely normalized database,
so there's a big chance that you did make some mistakes.

If the design is really correct, then you should consider splitting the
columns over two or more tables (all with the same key). Try to seperate
seldom-used columns from often used columns, and try to keep columns
that are often used together in the same table.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis