Violation of PRIMARY KEY on select statement

Question

I have a table that looks like this:

CREATE TABLE [dbo].[SomeTable](
    [Guid] [uniqueidentifier] NOT NULL,
    [Column1] [int] NOT NULL,
    [Column2] [datetime] NOT NULL,
    [Column3] [bit] NOT NULL,
    [Column4] [smallint] NOT NULL,
    [Column5] [uniqueidentifier] NULL,
    [Column6] [varchar](100) NULL,
    [Column7] [datetime] NULL,
    [Column8] [datetime] NULL,
 CONSTRAINT [pkSomeTable] PRIMARY KEY CLUSTERED 
(
    [Guid] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
)

NOTE: The names have been changed to protect the innocent.

Through a stored procedure, I am running this query:

SELECT SomeTable.Guid, SomeTable.Column1, SomeTable.Column2, SomeTable.Column3, SomeTable.Column4, SomeTable.Column5, SomeTable.Column6, SomeTable.Column7, SomeTable.Column8
        FROM SomeTable
        WHERE SomeTable.Guid = @Guid 
        ORDER BY SomeTable.Guid

After running this, I get a: Violation of PRIMARY KEY constraint 'pkSomeTable'. Cannot insert duplicate key in object 'dbo.SomeTable'. UniqueKeyConstraintException: Duplicate key.

I'm very confused on how I can have an exception like this running only a select statement. There are no triggers on the table. Does anyone have an idea on how this can happen?

Edit: The entire stp is:

    CREATE PROCEDURE dbo.stpSelectSomeTable
        @Guid UNIQUEIDENTIFIER = NULL
    AS
    BEGIN
    SET NOCOUNT ON

    IF  (@Guid is NULL) 

         SELECT SomeTable.Guid, SomeTable.Column1, SomeTable.Column2, SomeTable.Column3, SomeTable.Column4, SomeTable.Column5, SomeTable.Column6, SomeTable.Column7, SomeTable.Column8
        FROM SomeTable
        ORDER BY SomeTable.Guid

    Else

         SELECT SomeTable.Guid, SomeTable.Column1, SomeTable.Column2, SomeTable.Column3, SomeTable.Column4, SomeTable.Column5, SomeTable.Column6, SomeTable.Column7, SomeTable.Column8
        FROM SomeTable
        WHERE SomeTable.Guid = @Guid 
        ORDER BY SomeTable.Guid

END
GO

I am sure that GUID is not null when this STP is called. I am also sure that this is the statement that caused the problem. The stack trace of the exception shows me this. It turns out the database that this happened on has been deleted a restored to a previous version. Because of this, I can't test the statement. That was done before this issue was given to me.

Answer 1

I just tried the SQL you posted above and got no PK violation, so as others have pointed out, there is no problem with that statement in itself so it's got to be somewhere else.

As for how to solve the problem, you could try using the SQL profiler to create a trace and then run the SP. That should help you figure out where the statement that is causing the PK violation is occurring. You need SQL profiler installed to use this feature. Here's a link for more information: http://technet.microsoft.com/en-us/library/ms175047.aspx

Answer 2

I suspect you have misinterpreted the SQL error message which tells you the line on which it detected the error.

The line number will be relative to the START of query batch in which the error occurred. GO is used as a batch separator. So the the following example a PK error on the INSERT would be reported as having occurred at line 1.

/*A comment
Next line starts a new batch*/
GO
INSERT INTO T1 VALUES(1)

Most certainly the comment is not responsible for the PK violation.

Edit To track the problem down, run sections of the script 'piece-meal' by commenting out irrelevant sections.

Answer 3

I hate triggers.

Answer 4

Another thought... are you sure that is the stored proc you are calling?

SQL Server has a few things that can cause confusion. For example: A single database can have multiple 'schemas'. If you do not call your stored proc using the convention ., you could end up calling a proc with the same name from a different schema.

I suggest you modify your stored proc to print a simple output line to confirm that is the one that was called.

Answer 5

It looks like the problem was really an insert. Not sure why the error had that select as the statement that caused the problem. It was a threading issue. We had code like this:

row = dbo.stpSelectSomeTable @Guid = {SomeGuid}
if (row exists)
{
  do stuff to update the row
}
else
{
  insert new row with {SomeGuid}
}

There wasn't any kind of synchronization here so more than one thread was trying to insert a row with the same guid. We added a lock and all was good.

Thanks all for your suggetions.