As a developer or a database administrator (DBA), it is important to have a solid understanding of SQL Server and its various concepts. One fundamental concept that often comes up in interviews is the usage of identity columns. In this article, we will explore the importance of identity columns and how to handle them correctly.
Let’s start with a scenario: imagine you have a stored procedure in SQL Server 2000 that takes two variables (firstname and lastname) and inserts them into a table called TEST. This table has columns with the same names (firstname and lastname), and it also has a primary key column named ContactID, which is an integer and is marked as an identity column.
The question is: how do you obtain and return the primary key for the row just inserted?
This question may seem trivial, but it serves as a fundamental test to determine if someone has experience working with data beyond a basic level. It also highlights the importance of understanding related tables and key generation in SQL Server.
Let’s explore some common wrong answers:
- Selecting the maximum value of the ContactID column from the TEST table using
SELECT MAX(ContactID) FROM TEST
. This approach assumes that no one else will be inserting a row, which is not a reliable assumption. - Selecting the top 1 value of the ContactID column from the TEST table ordered by ContactID in descending order using
SELECT TOP 1 ContactID FROM TEST ORDER BY ContactID DESC
. This approach suffers from the same issue as the previous one. - Querying the row back by using other data that was inserted into the table, essentially creating an alternative primary key made up of one or more columns. While this may work in some cases, it is not a recommended approach.
- Using the
@@Identity
function, which retrieves the last identity value for the session. While this may work in SQL Server 7, it can lead to unexpected results in the future if auditing triggers are added.
The correct answer is to use the Scope_Identity()
function in SQL Server 2000 and the @@Identity
function in SQL Server 7. Additionally, the result should be returned as an output parameter, as the return value is typically reserved for error conditions.
Let’s run a couple of tests to demonstrate the correct approach:
CREATE DATABASE IdentityTest
USE IdentityTest
CREATE TABLE TEST (
ContactID INT NOT NULL IDENTITY(1, 1),
firstname VARCHAR(100) NULL,
lastname VARCHAR(100) NULL
)
INSERT INTO TEST DEFAULT VALUES
SELECT @@Identity
The above code will return the value 1. Repeating the same code will return 2.
INSERT INTO TEST DEFAULT VALUES
SELECT Scope_Identity()
This code will return a value of 3.
Now, let’s consider a scenario where an auditing trigger is added to the TEST table:
CREATE TABLE TESTHISTORY (
HistoryID INT NOT NULL IDENTITY(1, 1),
ContactID INT NOT NULL,
firstname VARCHAR(100) NULL,
lastname VARCHAR(100) NULL
)
CREATE TRIGGER i_TEST ON dbo.TEST FOR INSERT
AS
SET NOCOUNT ON
INSERT INTO TESTHISTORY (ContactID, FirstName, LastName)
SELECT ContactID, FirstName, LastName
FROM Inserted
If we insert a row into the TEST table and then query the identity value using @@Identity
, we may get unexpected results. This is because the auditing trigger inserts rows into another table that also has an identity column.
The correct approach is to use Scope_Identity()
in this scenario:
INSERT INTO TEST DEFAULT VALUES
SELECT @@Identity -- Returns a value of 1
INSERT INTO TEST DEFAULT VALUES
SELECT @@Identity -- Returns a value of 2
SELECT Scope_Identity() -- Returns a value of 3
As you can see, using Scope_Identity()
ensures that we get the correct identity value even when triggers are involved.
Understanding SQL Server identity columns and how to handle them correctly is crucial for working with databases effectively. By using the appropriate functions and techniques, you can avoid potential issues and ensure the integrity of your data.
Next time you interview a developer or a DBA, consider asking them about their knowledge of identity columns. It will not only test their understanding of SQL Server but also provide an opportunity for professional development.
Remember, the more you know about SQL Server, the better equipped you are to handle complex data scenarios and avoid potential headaches in the future.