| Author |
Topic |
|
kiri
Starting Member
38 Posts |
 Posted - 2008-02-25 : 10:12:38
|
| CREATE unique clustered INDEX salesdate_index ON salesdocumentry(salesdate) WITH (IGNORE_DUP_KEY = ON); GOgetting error:The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.salesdocumentry' and the index name 'salesdate_index' . The duplicate key value is (Jan 1 1900 12:00AM).The statement has been terminated.can anyone guide what should i do?thanks. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-25 : 10:15:35
|
1) Locate the already existing duplicate records and decide what to do with them.2) Why would you like to do a UNIQUE clustered index over a datetime column?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
kiri
Starting Member
38 Posts |
Posted - 2008-02-25 : 10:22:22
|
| In my procedure i have to pass salesdate and will get all sales results..so to get results faster i have to create index on datecolumn.if i m creating nonclustered index it bot gives me error but query is not running fast...so i m trying to create clustered index.can u tell me whether i have to create just cluster index or uique cluster index.thanks for ur help and guidance. |
|
|
|
Abu-Dina
Posting Yak Master
206 Posts |
Posted - 2008-02-25 : 10:26:35
|
quote:
2) Why would you like to do a UNIQUE clustered index over a datetime column?
It could be a daily aggregate table in which case a unique index on the date could be feasible IMHO. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-25 : 10:35:45
|
quote:
Originally posted by kiri
The duplicate key value is (Jan 1 1900 12:00AM).
You have at least two records with value 0 (Jan 1 1900 12:00AM) in the column.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-25 : 10:36:25
|
You can still make the index clustered and having it non-unique.CREATE unique clustered INDEX salesdate_index ON salesdocumentry (salesdate)
E 12°55'05.25"N 56°04'39.16" |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
kiri
Starting Member
38 Posts |
Posted - 2008-02-25 : 10:47:51
|
| i can run my query fast better than previous - before indexing...so after creating cluster index...i m getting results faster..thanks peso. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-02-25 : 10:54:09
|
Please notice that adding a CLUSTERED index severely affects future DELETE and INSERT statements.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
kiri
Starting Member
38 Posts |
Posted - 2008-02-25 : 11:08:48
|
| sure peso..will keep in mind.thanks a lot!! |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
kiri
Starting Member
38 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
index - error