Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
IceDread
Yak Posting Veteran
66 Posts |
 Posted - 2007-10-29 : 08:34:53
|
| I've a question. I've an old db here with no Primary keys nearly but a lot of identity's set, one for each table (nearly). I'm wondering, could something somehow go wrong with some sp's or anything if I make primary keys of these identities? Question why I'm asking is that we're takinging about doing replication but can't since there are primary keys lacking but we dont dare to change ém into primary keys if something might go wrong since we dont have knowledge about that.Thanks for help. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-29 : 08:52:06
|
Assuming that the Identity columns contain UNIQUE values  then I can't see any side effect of adding a PK. One big advantage (assuming that none of the other Indexes on the tables are CLUSTERED) is that an Index Rebuild on the table will be able to defrag the data, which if there is NO Clustered Index currently the data will be a mess and probably wasting space etc.That in itself may create a side effect as the size of your Log file may increase dramatically the next time the Index Rebuild housekeeping job runs.Kristen |
 |
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-29 : 09:02:03
|
| Thanks for youre answere!There are a lot of indexes and they have a very very low logicalfragmentation. Most of the tables have no clusterd index. The Identity columns are Unique in nearly all tables. There are a few tables that lacks identity thou, but those tables do not need to be replicated. They are archivetables for old copies of data where a row might have been deleted or updated etc. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-29 : 09:51:05
|
| "They are archivetables for old copies of data"We put a non-unique clustered index on the original PK columns, plus the "Update Date" column in our Archive Tables |
|
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-30 : 04:53:49
|
| I would have preferd it that way. The unique identifier in some of the archive tables is a "changedate" column. |
|
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-30 : 04:54:27
|
| All thou a "changedate" column is very usefull for other things. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 07:44:58
|
| "The unique identifier in some of the archive tables is a "changedate" column"A bulk update is likely to create multiple updates all with the same millisecond time, which will give problems if you require the ChangeDate to be unique ... |
|
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-30 : 10:08:49
|
quote:
Originally posted by Kristen
"The unique identifier in some of the archive tables is a "changedate" column"A bulk update is likely to create multiple updates all with the same millisecond time, which will give problems if you require the ChangeDate to be unique ...
That has happend actually and I've pointed it out to the boss.. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-30 : 10:25:08
|
| Hahaha ... can you fire your boss?!! |
|
|
|
IceDread
Yak Posting Veteran
66 Posts |
Posted - 2007-10-30 : 11:19:44
|
| Hehehe He is usually a wise man thou. |
|
|
|
|
|
|
Identity -> PrimaryKey