February 2009 - Posts
I heard this question from many people. Executing a dynamic OPENXML() query is little tricky.
Before we look at a dynamic OPENXML() query, let us look at the static version. Here is an example document that we will parse using OPENXML() in this session.
<Employees>
<Employee ID="1001" Name="Jacob"/>
<Employee ID="1002" Name="Steve"/>
</Employees>
Let us write an OPENXML() query that retrieves the Employee ID and Name from the above XML value.
DECLARE @hdoc INT
DECLARE @xml VARCHAR(MAX)
SELECT @xml = '
<Employees>
<Employee ID="1001" Name="Jacob"/>
<Employee ID="1002" Name="Steve"/>
</Employees>'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
SELECT *
FROM OPENXML (@hdoc, '/Employees/Employee', 1)
WITH (
ID varchar(10),
Name varchar(20)
)
EXEC sp_xml_removedocument @hdoc
/*
ID Name
---------- --------------------
1001 Jacob
1002 Steve
*/
Let us see how to write the dynamic version of this query.
Using sp_executesql
It is pretty easy to execute a dynamic OPENXML() query with sp_executesql. sp_executesql accepts parameterized queries and hence the document handle can be passed as a parameter to this procedure.
Here is the version of the dynamic query that can be executed with sp_executesql
DECLARE @hdoc INT
DECLARE @xml VARCHAR(MAX)
SELECT @xml = '
<Employees>
<Employee ID="1001" Name="Jacob"/>
<Employee ID="1002" Name="Steve"/>
</Employees>'
DECLARE @qry NVARCHAR(500), @param NVARCHAR(50)
SELECT @qry = N'
SELECT *
FROM OPENXML (@hdoc, ''/Employees/Employee'', 1)
WITH (
ID varchar(10),
Name varchar(20)
)'
SELECT @param = N'@hdoc INT'
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml
EXEC sp_executesql @qry, @param, @hdoc
EXEC sp_xml_removedocument @hdoc
/*
ID Name
---------- --------------------
1001 Jacob
1002 Steve
/
Using EXEC()
It is not very handy to write dynamic OPENXML() queries with EXEC(). This is because there is no easy way to pass the document handle to the EXEC() function. To execute a dynamic OPENXML() query with EXEC() we need to make sure that calls to "sp_xml_preparedocument" and "sp_xml_removedocument" are also part of the dynamic query.
Here is the version of the query that can be executed with EXEC()
DECLARE @hdoc INT
DECLARE @xml VARCHAR(MAX)
SELECT @xml = '
<Employees>
<Employee ID="1001" Name="Jacob"/>
<Employee ID="1002" Name="Steve"/>
</Employees>'
EXEC('
DECLARE @hdoc INT
EXEC sp_xml_preparedocument @hdoc OUTPUT, ''' + @xml + '''
SELECT *
FROM OPENXML (@hdoc, ''/Employees/Employee'', 1)
WITH (
ID varchar(10),
Name varchar(20)
)
EXEC sp_xml_removedocument @hdoc'
)
/*
ID Name
---------- --------------------
1001 Jacob
1002 Steve
*/
We have seen a number of string parsing/manipulating examples using XQuery and FOR XML in the previous sessions of XQuery Labs. This session presents yet another string parsing example using XML data type methods.
Here is the string value that we need to parse.
'id1-s1,s2,s3|id2-s4,s5,s6|id3-s7,s8,s9'
We need to parse the above string and generate a row set as follows:
idname id1 id2 id3
------ ---- ---- ----
id1 s1 s2 s3
id2 s4 s5 s6
id3 s7 s8 s9
Let us try do the parsing using XQuery methods. Before we can apply XQuery method on this value, we need to convert it to a well formed XML value. Let us use the REPLACE() function to convert this string to a well-formed XML value as given in the following example:
<v>
<i>id1</i>
<a>s1</a>
<a>s2</a>
<a>s3</a>
</v>
<v>
<i>id2</i>
<a>s4</a>
<a>s5</a>
<a>s6</a>
</v>
<v>
<i>id3</i>
<a>s7</a>
<a>s8</a>
<a>s9</a>
</v>
The following code shows the REPLACE() operation needed to transform the original string to the XML representation.
DECLARE @str VARCHAR(MAX), @x XML
SELECT @str = 'id1-s1,s2,s3|id2-s4,s5,s6|id3-s7,s8,s9'
SELECT @x = REPLACE(
REPLACE(
'<v><i>' +
REPLACE(
@str,'|','</a></v><v><i>'
)
+ '</a></v>','-','</i><a>'
),',','</a><a>'
)
Now let us use XQuery to retrieve the information from the XML document. Here is the complete source code listing.
DECLARE @str VARCHAR(MAX), @x XML
SELECT @str = 'id1-s1,s2,s3|id2-s4,s5,s6|id3-s7,s8,s9'
SELECT @x = REPLACE(
REPLACE(
'<v><i>' +
REPLACE(
@str,'|','</a></v><v><i>'
)
+ '</a></v>','-','</i><a>'
),',','</a><a>'
)
SELECT @x
SELECT
x.value('i[1]', 'CHAR(3)') AS idname,
x.value('a[1]','CHAR(3)') AS id1,
x.value('a[2]','CHAR(3)') AS id2,
x.value('a[3]','CHAR(3)') AS id3
FROM @x.nodes('/v') a(x)
/*
idname id1 id2 id3
------ ---- ---- ----
id1 s1 s2 s3
id2 s4 s5 s6
id3 s7 s8 s9
*/
Notes:
This is yet another string parsing example using XQuery methods. The purpose of XQuery labs series is to help people learn XQuery within the context of SQL Server. The approach presented in this series of articles may be good in some cases and may not be ideal for other cases.
The difference between @@IDENTITY and SCOPE_IDENTITY() is not very clear to many people and they often face problems due to the incorrect usage of @@IDENTITY. The primary difference between @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT() is the scope of each one of them.
A few years back, I was helping some one to debug a very strange bug. A sales entry application that worked for several years, stopped working all of a sudden. At the end, we found that the stored procedure used @@IDENTITY to retrieve the newly created IDENTITY value. One of the developers added some code to a trigger that inserted some auditing stuff to a set of audit tables. The triggers were also generating IDENTITY values. So the value returned by @@IDENTITY was not the IDENTITY value of the sales table, but it was the IDENTITY value of the audit tables. You can read more about it here.
So the common mistake that people make unknowingly is something like the following:
DECLARE @NewVal INT
INSERT INTO Sales (col1, col2) SELECT 'val1', 'val2'
SELECT @NewVal = @@IDENTITY
-- do something with @NewVal
Most of the times, you will get the correct value during your testing.
In fact, this will always give you correct value, if there is no trigger on the table. In the above example, @@identity returns the new IDENTITY value generated in the Sales table. However this can go wrong, if the table has a trigger. @@identity returns the last IDENTITY value generated in the current session. If the trigger also generates IDENTITY values, @@identity will return the IDENTITY value generated inside the trigger, not the identity value generated in the "sales" table.
The following is the correct version of the above example, that uses SCOPE_IDENTITY() to retrieve the new IDENTITY value generated in the "sales" table, in the current scope.
DECLARE @NewVal INT
INSERT INTO yourtable (col1, col2) SELECT 'val1', 'val2'
SELECT @NewVal = SCOPE_IDENTITY()
-- do something with @NewVal
SCOPE_IDENTITY() returns the last @@IDENTITY value generated in the current scope. It is a safe practice to assign the IDENITTY value to a variable and use it as shown in the above example.
We examined IDENT_CURRENT() in a previous post. IDENT_CURRENT() returns the last identity value generated for the given table, in any session and any scope.
Question to Readers: All our stored procedures use SCOPE_IDENTITY() to retrieve the new IDENTITY value. I am not able to find a case where @@identity is used. Do you have a real-life example where the usage of @@identity is required?
Every IDENTITY column has two basic attributes: SEED and INCREMENT. The SEED attribute stores the ROOT or Starting value. INCREMENT specifies the value to be added to the current IDENTITY value to generate the next value. The default value of both these attributes is 1. So unless you specify a different value, an IDENTITY column starts with 1 and increments by 1.
How to find the SEED value of an IDENTITY column?
You can use the system function IDENT_SEED() to retrieve the SEED value of an IDENTITY column. for example:
IF OBJECT_ID('Employees','U') IS NOT NULL
DROP TABLE Employees
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO
SELECT IDENT_SEED('Employees') AS IdentitySeed
/*
IdentitySeed
---------------------------------------
1
*/
How to find the INCREMENT value of an IDENTITY column?
Just like IDENT_SEED(), there is another function that returns the INCREMENT value of an IDENTITY column: IDENT_INCR(). The following example demonstrates that.
IF OBJECT_ID('Employees','U') IS NOT NULL
DROP TABLE Employees
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO
SELECT IDENT_INCR('Employees') AS IdentityIncrement
/*
IdentityIncrement
---------------------------------------
1
*/
How to find the current IDENTITY value of a table?
The system function IDENT_CURRENT() returns the last generated IDENTITY value of a table. If the table is empty (no records generated yet or truncated), this function will return the IDENTITY SEED. Let us look at an example:
IF OBJECT_ID('Employees','U') IS NOT NULL
DROP TABLE Employees
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO
SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 1
INSERT INTO Employees(Name) SELECT 'Jacob'
SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 1
INSERT INTO Employees(Name) SELECT 'Steve'
SELECT IDENT_CURRENT('Employees') AS LastIdentityValue
-- returns 2
SQL Server 2005 Books Online documentation incorrectly states that the function returns NULL if called for a table that is empty (no records inserted yet or the table is truncated). The above example shows that in such case, SQL Server returns the IDENTITY SEED value, instead of NULL value. This documentation error is corrected in SQL Server 2008 Books Online Documentation.
Retrieve the SEED, INCREMENT and LAST IDENTITY value of all tables in a database
Let us try to write a query that returns the SEED, INCREMENT and LAST IDENTITY value of all tables in the AdventureWorks database. Here is the query:
WITH cte AS (
SELECT
s.name + '.' + t.name AS TableName,
c.name AS ColumnName
FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
INNER JOIN sys.columns c ON c.object_id = t.object_id
AND c.is_identity = 1
)
SELECT
TableName,
ColumnName,
IDENT_SEED(TableName) AS Seed,
IDENT_INCR(TableName) AS Increment,
IDENT_CURRENT(TableName) AS LastIdentity
FROM cte
/*
TableName ColumnName Seed Increment LastIdentity
------------------------------ ------------------------- ----------- ----------- ------------
Person.Address AddressID 1 1 32521
Production.ProductReview ProductReviewID 1 1 4
Production.TransactionHistory TransactionID 100000 1 213442
Person.AddressType AddressTypeID 1 1 6
Production.ProductSubcategory ProductSubcategoryID 1 1 37
...
...
*/
Question to Readers: Can you think of a scenario where you need to know the SEED, INCREMENT or CURRENT IDENTITY value of a table?
IDENTITY columns are managed by SQL Server. SQL Server generates a new value for every INSERT and does not generate DUPLICATE values. However, if you interfere with the management of IDENTITY values, you can land up with duplicate IDENTITY values. Let us examine a few cases where duplicate IDENTITY values can be generated.
Inserting explicit values can result in duplicate IDENTITY values
You need to be very careful when inserting explicit values to an IDENTITY column. An IDENITY column does not complain if you insert duplicate values (unless you have a constraint: primary key, unique index etc). So a mistake while inserting explicit values to an IDENTITY column can result in duplicate values. Let us see an example.
IF OBJECT_ID('Employees','U') IS NOT NULL
DROP TABLE Employees
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
*/
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees(EmpID, Name) SELECT 2, 'Bob'
SET IDENTITY_INSERT Employees OFF
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
2 Bob
*/
Note that we inserted a duplicate IDENTITY value to the EmpID column and SQL Server accepted it. When you insert an explicit identity value you should make sure that the it does not generate duplicate values. It can also happen that you execute the insert script twice, by mistake, that again can lead to duplicate IDENTITY values. For example:
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees(EmpID, Name) SELECT 3, 'Joe'
INSERT INTO Employees(EmpID, Name) SELECT 3, 'Joe'
SET IDENTITY_INSERT Employees OFF
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
2 Bob
3 Joe
3 Joe
*/
Resetting the IDENTITY SEED can result in duplicate IDENTITY values
Another case that I think, can generate duplicate IDENTITY values is when you reset the IDENTITY SEED. When you RESET your identity column, if the table contains values that are higher than the new IDENTITY SEED, there are chances that duplicate rows will be generated later on.
For example:
IF OBJECT_ID('Employees','U') IS NOT NULL
DROP TABLE Employees
CREATE TABLE Employees (EmpID INT IDENTITY, Name VARCHAR(20))
GO
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
*/
DBCC CHECKIDENT('Employees',RESEED, 1)
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
2 Bob
*/
Both the problems I explained above can occur only in very rare cases. The purpose of this post is to demonstrate that a coding mistake can result in duplicate IDENTITY values.
Question to Readers: Did you ever come across a table having duplicate identity values? If yes, did you find out how the record got generated?