CODEWITHSUNDEEP
c#sql-server-2008identityidentity-column
Kevin Clary
Kevin Clary

Reputation: 43

Duplicate Records in SQL Server with Identity Column

I have an ASP.Net Web Forms app that is inserts new records (requests) into a SQL Server table using a SqlCommand and using the ExecuteNonQuery method. The table has a single primary key (int id) that is set for Identity Specification = Yes, Identity Increment 1, Identity Seed 1. The app has been working for years. About a month ago, the database was moved from a physical server to virtual server without any apparent issues. The database is running on SQL Server 10.50.1617.0.

Very recently, a user reported creating about 90 requests but not being able to find them. When I examined the request table, I could only find one request created for her in the last few days. The highest id on the request table was 5404. As an experiment, I had her create a new request. I was expecting it to create a new id of 5405, but it actually created an id of 2975. I had her create another request and it over-wrote the first request with another on id 2975. I created a test request and it created one at id 5405. What could possibly cause this?

Here is the c# for the insert:

        string sql = "insert into requests (end_user_email) values (@email)";
        SqlConnection cn = new SqlConnection(_cnString);

        SqlCommand cmdIns = new SqlCommand(sql, cn);
        cmdIns.Parameters.Add("@email", SqlDbType.NVarChar);
        cmdIns.Parameters["@email"].Value = email;

        cmdIns.Connection.Open();
        cmdIns.ExecuteNonQuery();

Upvotes: 0

Views: 1810

Answers (2)

MERLIN
MERLIN

Reputation: 436

It's also possible the client moved from a single server to a sand box or cloud & failed to change the web.config of the application..

<!--
        If you are deploying to a cloud environment that has multiple web server instances,
        you should change session state mode from "InProc" to "Custom". In addition,
        change the connection string named "DefaultConnection" to connect to an instance
        of SQL Server (including SQL Azure and SQL  Compact) instead of to SQL Server Express.
  -->
<sessionState mode="InProc" customProvider="DefaultSessionProvider">

in this case it may also benefit to move to an nvarchar(128) or GUID & use newID() to assign unique ID's to additional records.

Upvotes: 0

Nicholas Carey
Nicholas Carey

Reputation: 74197

Your table name is not schema-qualified (e.g., dbo.requests). I suspect you've got two different tables in your database with identical names. One is owned by the dbo schema; the other by your default schema.

SQL Server name resolution works as follows for unqualified references:

  1. Probe the namespace for an oject of the specified name under the default schema as defined by the current connections credentials. If one is found, that object resolves the reference. Otherwise...

  2. Probe the namespace for an object of the specified name under the dbo schema. If one is found, that object resolves the reference. Otherwise...

  3. If the object is a stored procedure whose name begins with sp_, further probes of the namespace are performed against the master database as in steps #1 and #2 above.

  4. Finally, if the reference was not resolved, name resolution fails.

Your user is connecting using credentials with one default schema and resolving to a table named requests that is either owned by that schema or dbo.

You, on the other hand, are using credentials with another default schema and finding a different table with the same name, either owned by your default schema or dbo.

One should always schema-qualify object references, for two-reasons:

  • Performance. Two probes of the namespaces to find the dbo-owned object that you probably want costs you in terms of performance.

  • Further, execution plans involving unqualified references may not get cached, leading to additional recompiles.

  • Lastly, you or somebody else will, at some point, shoot yourself in the foot. Often, it will be by one party executing one version of a stored procedure and another party a different version. Hilarity ensues -- "It works on my machine!". Even better is when different people are hitting different versions of the same table, receiving seemingly random errors regarding missing columns.

HOWEVER...

It's not unheard of :-) for identity columns to have the value of the identity property get out of sync with data for various reason.

So, if you've ruled out two different flavors of the same table, you'll want to have your DBA run DBCC CHECKIDENT against the table to get the table's identity counter back in sync with the high-water mark in the table.

  • DBCC CHECKIDENT( {table-name} , noreseed )

    reports on the current values of both the identity counter for the table and the current high-water mark in the table.

  • DBCC CHECKIDENT( {table-name} , reseed )

    reseeds the identity counter to match the high-water mark in the table.

Upvotes: 4

Related Questions