Does inserting an instruction without joins lead to duplication if duplicates did not exist before?

Question

I have a problem with some SQL, which leads to results that I did not expect. I store information from different tables in another table, which is used as part of the search page on a website. All page data for each page, as well as data of other elements on other pages (for example, calendars, etc.) are listed in a table named pageContentCache . Usually this table has an index against the one created with the following:

 alter table pageContentCache add constraint [IX_pageContentCache] PRIMARY KEY CLUSTERED ( [objectId] ) 

For some reason, that there will be a duplicate objectId for me, the problem occurred with one instance of this software, which led to the following error:

Msg 1505, Level 16, State 1 sp_rebuildPageContentCache Procedure, line 50

The CREATE UNIQUE INDEX operation completes because a duplicate key was found for the object name "dbo.pageContentCache" and the index name "IX_pageContentCache". The duplicate key value is (21912).

So, in order to debug the problem, I had a procedure for loading all the data that she was going to enter into the pageContentCache table in the temporary table, #contentcache , firstly, so I could view This.

Here I am a little confused ...

Once the data has been inserted into #contentcache (which has two columns, objectId and content ), I can run the following SQL statement and will return nothing:

 select objectId, count(objectId) from #contentcache group by objectId having count(objectId) > 1 

This does not return any records. If I then ran the following SQL:

 insert into pageContentCache (objectId, contentData) select objectId, content from #contentcache 

Inserts all data from #contentcache into pageContentCache as you expected. However, if I then run the following SQL, it returns duplicates:

 select objectId, count(objectId) from pageContentCache group by objectId having count(objectId) > 1 

Then duplicates are returned:

 objectId (no column name) 21912 2 

There are no triggers or the like in this table, and the insert statement just copies data from one table to another, so ... where is the duplicate coming from?

Answer 1

Try the following:

 insert into pageContentCache (objectId, contentData) select distinct objectId, content from #contentcache 

I can’t understand why you will have duplicates, because, as you mentioned, there are no unions in your select statement. In any case, I assume that the keyword is different in that duplicates are excluded.

Answer 2

This is a SQL Server database error that I saw earlier. You can fix the latest service pack and try again.

Answer 3

I'm not sure this statement does what you think:

 select objectId, count(objectId) from #contentcache group by objectId having count(objectId) > 1 

Can you try this:

 WITH SUBQUERY AS ( select COUNT(objectId) OVER (PARTITION BY objectId) AS CNT_OBJECT_IDS, objectId FROM #contentcache) SELECT * FROM SUBQUERY WHERE CNT_OBJECT_IDS > 1 

See if this will return any rows to you.

In addition, I have never worked with clusters before, and I wonder if they are doing any additional things that we don’t know about. Can you just say

 PRIMARY KEY 

instead

 PRIMARY KEY CLUSTERED 

in the definition of the constraint and see if this affects your problem?