December 9, 2003 at 7:53 am
What would cause an index table to get out of synch? I've been working w/ SQL for the last two months and don't know what I would like but I do a lot of reading.
December 9, 2003 at 1:08 pm
What do you mean by 'out of synch'?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 9, 2003 at 1:42 pm
There were two index tables. A processes was run to update the tables from another system. According to our vendor, they indicated the two index tables were out of synch. The update was looking for the next available name to use and there were duplicats which prevented the users from creating a customer profile.
December 9, 2003 at 1:51 pm
You mean you have duplicates in your tables?
If so, search this site, there are scripts for this around.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
December 9, 2003 at 9:27 pm
Yes, there were duplicates in the tables. A quick workaround wwas to clear the tables, now I need to reindex. Thanks for your responses, it helped. I'll look on this site for duplicate tables, however, I did not get my questions answered. How does a table get out of synch or create duplcates?
quote:
You mean you have duplicates in your tables?If so, search this site, there are scripts for this around.
Frank
December 10, 2003 at 1:40 am
Stahura,
with SQL, duplicate data usually occurs because of duplicate data.
select *
From
(
select 'A' c, 'data' i, 'this is unique' n
union all
select 'B' c, 'data' i, 'this is unique' n
) notdups
JOIN
(
Select 'A' c, 'moredata' i, 'this is not unique' n
union all
Select 'A' c, 'moredata' i, 'this is not unique' n
union all
Select 'B' c, 'moredata' i, 'this is unique' n
) dups on dups.c = notdups.c
garbage in garbage out. You should look at your data sources and maybe add some primary keys or unique constraints if necessary.
Signature is NULL
December 10, 2003 at 5:29 am
Ok, now I understand better, Thanks. However, the two index tables are setup with PKs, that's why I do not understand how the data duplicated and why creating new data was denied. Again, I am two months new at this and I know I have a lot to learn. Thanks again.
quote:
Stahura,with SQL, duplicate data usually occurs because of duplicate data.
select *
From
(
select 'A' c, 'data' i, 'this is unique' n
union all
select 'B' c, 'data' i, 'this is unique' n
) notdups
JOIN
(
Select 'A' c, 'moredata' i, 'this is not unique' n
union all
Select 'A' c, 'moredata' i, 'this is not unique' n
union all
Select 'B' c, 'moredata' i, 'this is unique' n
) dups on dups.c = notdups.c
garbage in garbage out. You should look at your data sources and maybe add some primary keys or unique constraints if necessary.
December 10, 2003 at 10:39 am
Calvin,
I went researching and came across some notes about reorganizing index pages so they perform more efficiently, which is to run a defrag. Also, the root cause of the original problem of the index getting out of synch was that the connection updating the tables were lost. The connection was reestablished then the update began again, creating duplicates. I hope troubleshooting gets easier, but somehow I don't think so. Thank for the script.
Starhura
quote:
Stahura,with SQL, duplicate data usually occurs because of duplicate data.
select *
From
(
select 'A' c, 'data' i, 'this is unique' n
union all
select 'B' c, 'data' i, 'this is unique' n
) notdups
JOIN
(
Select 'A' c, 'moredata' i, 'this is not unique' n
union all
Select 'A' c, 'moredata' i, 'this is not unique' n
union all
Select 'B' c, 'moredata' i, 'this is unique' n
) dups on dups.c = notdups.c
garbage in garbage out. You should look at your data sources and maybe add some primary keys or unique constraints if necessary.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply