| Author |
Topic |
|
RichardSteele
Posting Yak Master
160 Posts |
 Posted - 2003-06-05 : 14:36:51
|
| We have many tables that have duplication of records. The bizarre thing is that they have identity columns! How could SQL 2k let this happen? Isn't that one of the purposes of identity fields? Some tables actually have been duplicated 3 or 4 times. We do not believe that we caused this to happen. Any light shed on this problem would be appreciated. |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-06-05 : 14:38:56
|
| Can you post the Insert statements that you use to insert the records and the crete table statements? Maybe you aren't creating the ID's properly or don't haev the correct constraints.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
 |
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-06-05 : 14:41:50
|
| I repeat, it is not our code. Some of the tables duplicated are not even being used except for testing and we haven't tested on them in many month.sSQL should always prevent a duplication of an identity autoid. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-05 : 14:47:49
|
| Could you post the DDL for the table?Tara |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-06-05 : 14:50:20
|
| This is on a shared SQL server. What is a DDL?There are many tables with this problem. Some are no longer being used. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-06-05 : 15:27:58
|
quote:
We have many tables that have duplication of records. The bizarre thing is that they have identity columns! How could SQL 2k let this happen? Isn't that one of the purposes of identity fields?
Nope.An identity just allocates the next value from the current seed. It is not guaranteed to be unique or sequential. And that's without poeple adding specific values.In v6.5 it was quite common to lose the seed if the server crashed - not so common now but still happens.Always put a unique index on an identity column (if you need it to be unique).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.Edited by - nr on 06/05/2003 15:28:38 |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-06-05 : 15:39:27
|
| Further to this problem, we cannot delete the duplicated records as there is a constraint violation. What's next? |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-06-05 : 15:57:03
|
| How many do you have?Try deleting one from Enterprise manager...Brett8-) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2003-06-05 : 16:08:27
|
| You need to post your DDL (CREATE TABLE statements) and the code that is used to insert data into the table. Most probably, the problem lies in one or the other. If you want some help, help us help you man.In your insert statments, are you doing "SELECT MAX(ID) + 1 FROM mytable" and using that as your ID?To get the DDL, open Enterprise Manager and drill down to one of the tables in question. Right-click the table and Goto All Tasks -->Generate ScriptsOn the Options tab, check the "Script Primary keys...."When you get that script, post it here.Michael<Yoda>Use the Search page you must. Find the answer you will.</Yoda> |
|
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2003-06-05 : 17:06:15
|
| Having an IDENTITY column is NOT the same as having a PRIMARY KEY or UNIQUE constraint. How difficult is it to insert the same values when there is no UNIQUE constraint? Take a look at the SET IDENTITY INSERT <table_name> ON|OFF setting in the Books Online. Perhaps that should give you a clue.Owais |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-06-05 : 17:20:13
|
| Michael, thanks. Here's the information:There is no insert statement for this table as it is not being used. Yet it and 35 other tables had duplicate or triplicate records.if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Vendors]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[Vendors]GOCREATE TABLE [dbo].[Vendors] ( [AutoID] [int] IDENTITY (1, 1) NOT NULL , [Code] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [City] [varchar] (44) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [ShowinList] [bit] NOT NULL , [Showcase] [bit] NOT NULL , [ShowcaseID] [int] NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[Vendors] WITH NOCHECK ADD CONSTRAINT [DF_Vendors_ShowinList] DEFAULT (1) FOR [ShowinList], CONSTRAINT [DF_Vendors_Showcase] DEFAULT (0) FOR [Showcase]GO |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-06-05 : 17:23:42
|
| Well here's the thing. Somebody could have inserted the duplicates/triplicates by doing the SET IDENTITY_INSERT ON thing for the table. Or maybe somebody changed the column so that it wasn't an IDENTITY column and then did some updates or inserts. The point is that without a unique constraint or primary key on that column, you can not guarantee uniqueness. If SQL Server is the one generating the IDs for you, then yes SQL Server will insert the next possible value for you. But you can override this with IDENTITY_INSERT ON or by changing the table DDL temporarily.Tara |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-06-05 : 18:06:14
|
| If the table is not used, delete it and go on.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-06-05 : 19:35:43
|
| Well, there are many more tables infected with the duplication that are still being used. It's also a matter of trying to figure out how on earth this ever happened. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-06-05 : 19:47:26
|
| It happened because there was no primary key or unique index applied to the identity columns. No great mystery there. |
|
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-06-05 : 19:53:06
|
| If there are tables that are in use that you see the issue on, then post the insert code for those tables. When asked for the insert code, you said the tables are no longer in use. If they aren't in use, delete them and forget about it. If they are, make sure you put a unique or primary key constraint on the column.-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
|
|
|
RichardSteele
Posting Yak Master
160 Posts |
Posted - 2003-06-05 : 20:25:00
|
| Ok, will do. But.... the mystery still remains why at 9:00 am this morning huge amounts of data over 35 tables were duplicated and sometimes triplicated, including many tables that are not even being used by our live site. No inserts were run and our two tech people had not even logged in. I now know how we can prevent it in the future. But perhaps some SQL corruption caused it, or some slammer. That is what we are concerned about now. How it happened, all of a sudden, without us being in the database, without any code being run. |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2003-06-06 : 01:41:20
|
| OK, first of all, even if you had a primary key (or unique index) on your AutoID column you could still get duplicate data inserted. This issue has been discussed many many times on this site. Do some searching and you can find many threads discussing the use of identity columns and primary keys. Bottom line is that an identity column w/unique index will only prevent duplicate identity values from being inserted. You could insert the same data millions of times into a table with an identity column w/unique index and not violate that constraint - even though you have millions of duplicates.Also, the data was no doubt mistakenly inserted into these tables by a person. I would check DTS packages that touch these tables to see which one might have been run, I would check scheduled jobs, I would look on other SQL boxes that might touch this one. Maybe somebody ran a portion of a DTS package and just doesn't want to fess up to it, maybe somebody got confused and worked on the production box when they meant to work on the dev box. Maybe they ran it 3 times before they figured out their mistake.-ec |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-06-06 : 07:04:06
|
| Hum...is it getting about time to do another Natural vs. Surrogate debate? It's been a couple weeks, at least ...Jay White{0} |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-06-06 : 08:15:32
|
quote:
Hum...is it getting about time to do another Natural vs. Surrogate debate? It's been a couple weeks, at least ...
You wouldn't dare!  |
|
|
|
Next Page
|
Urgent! Identity Duplication Problem