sql server programming: How about adding an identity column for each table? -- sql server programming

How about adding an identity column for each table?

Edward Yang
8/31/2003 8:08:14 PM

sql server programming: We are developing a database project using ASP.NET/VS.NET.
The database ends up with more than 200 tables!!!

We have a headache here. In many cases we have to modify
the values of primary keys (the customers ask for this)!
The original design did not include an identity column for
each table.

So you can imaging that we have to preserve the old values
for almost every columns in a row in some sort of way.
This is extremely heavy work when doing ASP.NET using HTML.

That is even not the worst. When you update the value of a
primay key, you have to pass in all those old values! I'm
not a guru to SQL, but I do realize that where clause with
dozens of AND condition will end up with SLOWWWWWWWW
performance.

Once we talked with the database designer, but he said it
would cause many problems if we add an identity column for
each table. But I don't think so.

So, did anybody here ever have similar situation here? Is
the gain more than loss if we add an idenity column for
each table to ease the process of updating primary keys???

Thanks!

Re: How about adding an identity column for each table?

Uri Dimant
9/1/2003 7:21:43 AM

Edward

Of cause you can add Identity property to the table and then declare it as
PRIMARY KEY. It gives you opportunity to update other column which was as
PIMARY KEY.

Try to avoid updating PRIMARY KEY columns.Every time that a column used for
a clustered index is modified ,all of the
non-clustered indexes must also be updated, createtin additional overhead.

[quoted text, click to view]