January 2009 - Posts
IDENTITY columns are managed by SQL Server. Most of the times you would not dare to touch the values, except for reading them. However, there may be times when you really need to update/insert explicit values, instead of letting SQL Server generate a value for you.
How do I insert an explicit value into an IDENTITY column?
By default SQL Server does not allow you to insert a value into an IDENTITY column. You need to explicitly enable IDENTITY INSERT. Let us see some sample code to understand this. Run the following script to create the sample table and data.
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'
DELETE FROM Employees WHERE Name = 'Steve'
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
3 Bob
*/
Note that we inserted two records, then deleted the record of 'Steve' and then inserted 'Bob'. This indeed created a missing sequence number. The next insert will generate IDENTITY value 4. Let us try to insert a record for Steve with EmpID 2.
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 2, 'Steve'
SET IDENTITY_INSERT Employees OFF
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
3 Bob
*/
SET IDENTITY_INSERT ON enables IDENTITY INSERT on the specified table. You should TURN it off as soon as you are done with inserting the value. Only one TABLE per session can have IDENTITY INSERT set to ON at a time. If you try to enable IDENTITY INSERT on another table, SQL Server will generate an error.
SET IDENTITY_INSERT Employees ON
CREATE TABLE Departments (DeptID INT IDENTITY)
GO
SET IDENTITY_INSERT Departments ON
/*
Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table 'master.dbo.Employees'.
Cannot perform SET operation for table 'Departments'.
*/
To enable IDENTITY INSERT on another table, you should set it to OFF on the previous table. The following is valid.
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 5, 'Jacob'
SET IDENTITY_INSERT Employees OFF
-- enable IDENTITY INSERT on Departments Table
SET IDENTITY_INSERT Departments ON
When IDENTITY INSERT is set to ON, you should always specify an IDENTITY value, else the insert operation will fail.
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (Name) SELECT 'Steve'
/*
Msg 545, Level 16, State 1, Line 2
Explicit value must be specified for identity column in table
'Employees' either when IDENTITY_INSERT is set to ON or when a
replication user is inserting into a NOT FOR REPLICATION
identity column.
*/
When you explicitly insert a new IDENTITY value, if that is higher than the current identity value, SQL Server will automatically adjust the current IDENTITY value to the new value.
TRUNCATE TABLE Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
-- current IDENTITY VALUE is 2
SET IDENTITY_INSERT Employees ON
INSERT INTO Employees (EmpID, Name) SELECT 100, 'Jacob'
SET IDENTITY_INSERT Employees OFF
-- current IDENTITY VALUE IS 100
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
100 Jacob
101 Jacob
*/
How do I UPDATE the value of an identity column?
You cannot update an IDENTITY column. SQL Server will generate an error if you attempt to do that. The following example demonstrates that.
SET IDENTITY_INSERT Employees ON
UPDATE Employees SET
EmpID = EmpID + 100
WHERE Name = 'Jacob'
/*
Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'EmpID'.
*/
Question to Readers: How often do you insert explicit values to an IDENTITY column? Did you ever come across cases where you needed to UPDATE an IDENTITY value?
In the previous post, we saw that the IDENTITY values can go out of sequence in several cases. Some times, you might decide to delete all rows from a table, and start filling fresh data. At this time, you might prefer to reset the IDENTITY value to start a new sequence.
Run the following script to create a sample table.
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
*/
Let us now delete all rows from this table and insert a new row.
DELETE FROM Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
3 Jacob
*/
As we figured earlier, the IDENTITY value started with 3, not with 1. Let us now see how we can reset the IDENTITY value back to 1.
TRUNCATE TABLE resets IDENTITY values
When you truncate a table, the IDENTITY value will be RESET. Here is an example:
TRUNCATE TABLE Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
*/
Note that the new IDENTITY value started from 1.
DBCC CHECKIDENT
You can use DBCC CHECKIDENT to reset the IDENTITY seed. Before we run the example, let us rebuild the table again.
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
*/
Now, let us try DBCC CHECKIDENT
DELETE FROM Employees
DBCC CHECKIDENT('Employees',RESEED, 0)
INSERT INTO Employees (Name) SELECT 'Jacob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
*/
It is also possible to change the NEXT identity value to a value of your choice. The following example changes the current identity value to be 99, so that the next value generated will be 100.
DBCC CHECKIDENT('Employees',RESEED, 99)
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
100 Bob
*/
Question to Readers: Did you ever come across a requirement for resetting the IDENTITY value? If so, which method did you use?
IDENTITY columns are not expected to be sequential. Most of the times you will notice that the IDENTITY columns have missing numbers. This behavior is by design, and there is nothing to worry about it. If your application requires a sequential number and cannot tolerate gaps in the sequence, you should make it a regular column and manage the values by yourself. An IDENTITY column cannot do that.
Let us examine a couple of cases that can cause missing numbers in the IDENTITY column.
Deletion of records can cause missing sequence numbers
One of the common reasons for IDENTITY values getting out of sequence (missing numbers) is deletion of records. 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
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
*/
We created a table with an IDENTITY column and inserted two records into it. So far the EmpID column has values in sequence. Now let us delete the row with ID 2 and insert a new record.
DELETE FROM Employees WHERE EmpID = 2
INSERT INTO Employees (Name) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
3 Bob
*/
Note that we have values out of sequence. EmpID 2 is missing. Let us now delete all the records and try to insert new records.
DELETE FROM Employees
INSERT INTO Employees (Name) SELECT 'Jacob'
INSERT INTO Employees (Name) SELECT 'Steve'
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
4 Jacob
5 Steve
*/
Note that after we deleted all records, the IDENTITY started from 4, not from 1.
Transaction rollbacks can cause missing sequence numbers
When you ROLLBACK a transaction, all IDENTITY values generated by the transaction will not rollback. This can cause missing numbers in the IDENTITY column. Let us look at an example to understand this.
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
*/
We have values in sequence so far. Let us now see a TRANSACTION rollback can break the sequence.
-- insert a row and ROLLBACK
BEGIN TRAN
INSERT INTO Employees (Name) SELECT 'Bob'
ROLLBACK TRAN
-- insert another row and COMMIT
BEGIN TRAN
INSERT INTO Employees (Name) SELECT 'Bob'
COMMIT TRAN
SELECT * FROM Employees
/*
EmpID Name
----------- --------------------
1 Jacob
2 Steve
4 Bob
*/
Note that your IDENTITY column has a missing value now. EmpID 3 was generated for the transaction that we rolled back. When the transaction rolls back, the IDENTITY values are not reset back to the previous value.
Question to Readers: I am eager to know how many of you think that IDENTITY columns missing sequence numbers are NOT acceptable.
Most of the times, we create an IDENTITY column that starts with a seed value of 1 and then increment by 1. In a previous post we saw that the seed value of an IDENTITY column can be any positive or negative value. Similarly, the INCREMENT value can be any positive or negative number. Let us examine some of the restrictions that SQL Server applies on the SEED and INCREMENT value.
SEED value cannot be higher than the MAX capacity of the data type
The SEED value should be lesser than or equal to the MAX value the data type can store. The following is an illegal IDENTITY key declaration.
DECLARE @t TABLE (id TINYINT IDENTITY(256, 1))
/*
Msg 2752, Level 16, State 1, Line 1
Identity column 'id' contains invalid SEED.
*/
However, the following is valid.
DECLARE @t TABLE (id TINYINT IDENTITY(255, 1))
DECLARE @t TABLE (id INT IDENTITY(256,1))
Both statements given above are valid because the SEED value is within the storage capacity of associated data type.
TINYINT cannot store negative values and hence the SEED value cannot be negative.
DECLARE @t TABLE (id TINYINT IDENTITY(-5, 1))
/*
Msg 2752, Level 16, State 1, Line 1
Identity column 'id' contains invalid SEED.
*/
DECLARE @t TABLE (id SMALLINT IDENTITY(-5, 1))
-- SUCCEEDS
INCREMENT value cannot be higher than the MAX capacity of the data type
Just like SEED value, the INCREMENT value too, should be lesser than or equal to the MAX storage capacity of the data type. Look at the following example:
DECLARE @t TABLE (id TINYINT IDENTITY(1, 256))
/*
Msg 2753, Level 16, State 1, Line 1
Identity column 'id' contains invalid INCREMENT.
*/
DECLARE @t TABLE (id SMALLINT IDENTITY(1, 256))
-- SUCCEEDS
TINYINT cannot be used for counting backwards
In the previous post we saw how to create IDENTITY values that counts backwards. However, you cannot do this with TINYINT. The INCREMENT value of a TINYINT IDENTITY column cannot be a negative value.
DECLARE @t TABLE (id TINYINT IDENTITY(100, -1))
/*
Msg 2753, Level 16, State 1, Line 1
Identity column 'id' contains invalid INCREMENT.
*/
DECLARE @t TABLE (id SMALLINT IDENTITY(100, -1))
-- SUCCEEDS
INCREMENT value should be whole numbers, decimal part is not allowed
The INCREMENT part of an IDENTITY specification should be integer values without decimal point. The following is illegal:
DECLARE @t TABLE (id INT IDENTITY(1, 1.5))
/*
Msg 2753, Level 16, State 1, Line 1
Identity column 'id' contains invalid INCREMENT.
*/
DECLARE @t TABLE (id INT IDENTITY(1, 1.0))
/*
Msg 2753, Level 16, State 1, Line 1
Identity column 'id' contains invalid INCREMENT.
*/
SEED and INCREMENT cannot take a variable, expression or a function
The value you assign to the SEED and INCREMENT of an IDENTITY should be a numeric constant. The following is illegal.
DECLARE @t TABLE (id INT IDENTITY(1, CAST(1.0 AS INT)))
/*
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'CAST'.
*/
DECLARE @t TABLE (id INT IDENTITY(1+5, 1))
/*
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.
*/
DECLARE @t TABLE (id INT IDENTITY(1, 1+0))
/*
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.
*/
DECLARE @i INT
SELECT @i = 1
DECLARE @t TABLE (id INT IDENTITY(@i, @i))
/*
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '@i'.
*/
The SEED and INCREMENT value of an IDENTITY column should be the same data type as the column. So the SEED and INCREMENT values of a TINYINT column should be TINYINT (the value should be within the limits of TINYINT).
Question to Readers: Did you ever use a seed value that is not 1? Did you ever use an INCREMENT value that is greater than 1? Did you ever use a NEGATIVE SEED or INCREMENT value? Did you ever use a data type other than INT and BIGINT for an identity column?
I have never created an IDENTITY column that counts backwards in a production environment. I cant think of a case when we might need it. However, the purpose of this post is to show that IDENTITY columns can count backwards. The SEED and INCREMENT attributes of an IDENTITY column can be negative values.
Let us see a few examples to understand this.
DECLARE @t TABLE (ID INT IDENTITY(100, -1), Name VARCHAR(20))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
INSERT INTO @t (Name) SELECT 'Bob'
SELECT * FROM @t
/*
ID Name
----------- --------------------
100 Jacob
99 Steve
98 Bob
*/
The example creates a table with an IDENTITY column that has a negative INCREMENT value. Identity SEED starts with 100 and then keeps going backward. After a few inserts the identity value will be 0 and then will get to negative values.
DECLARE @t TABLE (ID INT IDENTITY(2, -1), Name VARCHAR(20))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
INSERT INTO @t (Name) SELECT 'Bob'
INSERT INTO @t (Name) SELECT 'Peter'
SELECT * FROM @t
/*
ID Name
----------- --------------------
2 Jacob
1 Steve
0 Bob
-1 Peter
*/
Just like the INCERMENT value, the SEED value can also be negative. Here is an example:
DECLARE @t TABLE (ID INT IDENTITY(-100, 1), Name VARCHAR(20))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
INSERT INTO @t (Name) SELECT 'Bob'
INSERT INTO @t (Name) SELECT 'Peter'
SELECT * FROM @t
/*
ID Name
----------- --------------------
-100 Jacob
-99 Steve
-98 Bob
-97 Peter
*/
It is also possible that both SEED and INCREMENT values are negative.
DECLARE @t TABLE (ID INT IDENTITY(-100, -10), Name VARCHAR(20))
INSERT INTO @t (Name) SELECT 'Jacob'
INSERT INTO @t (Name) SELECT 'Steve'
INSERT INTO @t (Name) SELECT 'Bob'
INSERT INTO @t (Name) SELECT 'Peter'
SELECT * FROM @t
/*
ID Name
----------- --------------------
-100 Jacob
-110 Steve
-120 Bob
-130 Peter
*/
Question to Readers: In what context do you think some one would need an IDENTITY column that counts backwards?
SQL Server allows only one IDENTITY column per table. The DATA TYPE of an IDENTITY column can be INT, BIGINT, SMALLINT, TINYINT, DECIMAL or NUMERIC. In case of NUMERIC, the scale should be defined as 0.
For example, all the declarations given below are valid.
DECLARE @t1 TABLE (id INT IDENTITY)
DECLARE @t2 TABLE (id BIGINT IDENTITY)
DECLARE @t3 TABLE (id SMALLINT IDENTITY)
DECLARE @t4 TABLE (id TINYINT IDENTITY)
DECLARE @t5 TABLE (id DECIMAL IDENTITY)
DECLARE @t6 TABLE (id NUMERIC(8,0) IDENTITY)
However the following is invalid.
DECLARE @t6 TABLE (id NUMERIC(8,2) IDENTITY)
The following is a valid IDENTITY key declaration.
DECLARE @t4 TABLE (id numeric IDENTITY)
The following data types can store numeric values, but cannot be used as IDENTITY columns.
DECLARE @t4 TABLE (id bit IDENTITY)
DECLARE @t4 TABLE (id float IDENTITY)
DECLARE @t4 TABLE (id money IDENTITY)
DECLARE @t4 TABLE (id real IDENTITY)
DECLARE @t4 TABLE (id smallmoney IDENTITY)
Though you can define any of the six data types (INT, BIGINT, SMALLINT, TINYINT, DECIMAL and NUMERIC) as IDENTITY columns, you may not want to do that most of the times. Most people tend to use INT or BIGINT data type as IDENTITY columns. Using a smaller data type like TINYINT has a risk of IDENTITY value growing past the storage capacity of the associated data type. Since the IDENTITY value is managed and incremented by SQL Server, you need to make sure that the identity value does not grow beyond the max capacity of the given data type. The INSERT operation will fail if the new identity value generated is bigger than the data type can store.
DECLARE @Employees TABLE (id tinyint IDENTITY(100,100), Name VARCHAR(20))
INSERT INTO @Employees(Name) SELECT 'Jacob'
INSERT INTO @Employees(Name) SELECT 'Steve'
INSERT INTO @Employees(Name) SELECT 'Bob'
/*
Msg 8115, Level 16, State 1, Line 4
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.
*/
The third INSERT statement in the above example will FAIL because the IDENTITY value generated for the third row will be 300. The highest value a TINYINT data type can store is 255, hence the INSERT operation will fail.
The risk of IDENTITY value growing out of the capacity of the data type is more in cases where you delete and insert data frequently. The IDENTITY value will keep growing every time you insert a row. So it can happen that your table may have only a few thousand records, but your identity value will be in millions. This can also happen when you perform a lot of transaction ROLLBACKS. When you insert a row and ROLLBACK transaction, the IDENTITY key will not rollback. It will keep growing. I will show an example of this in another post.
You can run a query against the system metadata to retrieve a list of all IDENTITY columns in a given database. The following query retrieves all the IDENTITY columns in AdventureWorks database along with their table name and data type.
SELECT
OBJECT_NAME(object_id) AS TableName,
c.name AS ColumnName,
t.name AS DataType
FROM sys.columns c
INNER JOIN sys.types t ON t.system_type_id = c.system_type_id
WHERE is_identity = 1
/*
TableName ColumnName DataType
-------------------- -------------------- --------------------
Address AddressID int
ProductReview ProductReviewID int
TransactionHistory TransactionID int
AddressType AddressTypeID int
ProductSubcategory ProductSubcategoryID int
AWBuildVersion SystemInformationID tinyint
BillOfMaterials BillOfMaterialsID int
... ... ...
*/
Question to Readers: What data types do you usually use for IDENTITY columns?
We saw a number of string manipulation examples using FOR XML and XML Data Type methods. Here is yet another string parsing requirement that we will solve using the XML Data type methods. In this post, we will see how to extract words from strings stored in a column, that match with a given pattern.
As usual, I wrote this example to help a user in one of the SQL Server forums. Here is the sample data we have for this lab.
col1
--------------------------------------------------
MOTOROLA RAZR V3 CAR CHARGER
MOTOROLA V71 BATT COVER/SLVR
POWER CHARGER MOT V223
KODAK V550 LI-ION 720mAh
Our job is to extract all words that starts with "V" from the above column, so that the result should be:
col
------------------------------
V3
V71
V223
V550
This can be achieved in a number of ways and we will examine the XML way of doing this.
Let us create a table variable to store the sample data.
DECLARE @t table (col1 varchar(50))
INSERT INTO @t SELECT 'MOTOROLA RAZR V3 CAR CHARGER'
INSERT INTO @t SELECT 'MOTOROLA V71 BATT COVER/SLVR'
INSERT INTO @t SELECT 'POWER CHARGER MOT V223'
INSERT INTO @t SELECT 'KODAK V550 LI-ION 720mAh'
The first step is to convert the string stored in each row to an XML document, breaking each word into an XML element.
SELECT
CAST('<i>' + REPLACE(col1, ' ', '</i><i>') + '</i>' AS XML) AS col
FROM @t
/*
col
-------------------------------------------------------------
<i>MOTOROLA</i><i>RAZR</i><i>V3</i><i>CAR</i><i>CHARGER</i>
<i>MOTOROLA</i><i>V71</i><i>BATT</i><i>COVER/SLVR</i>
<i>POWER</i><i>CHARGER</i><i>MOT</i><i>V223</i>
<i>KODAK</i><i>V550</i><i>LI-ION</i><i>720mAh</i>
*/
And the next step is to shred the XML document into a row-set using XML data type methods.
SELECT
x.i.value('.', 'VARCHAR(30)') AS col
FROM (
SELECT
CAST('<i>' + REPLACE(col1, ' ', '</i><i>') + '</i>' AS XML) AS col
FROM @t
) a CROSS APPLY col.nodes('//i') x(i)
/*
col
------------------------------
MOTOROLA
RAZR
V3
CAR
CHARGER
MOTOROLA
V71
BATT
COVER/SLVR
POWER
CHARGER
MOT
V223
KODAK
V550
LI-ION
720mAh
*/
Finally, we can filter the output using the required pattern in the WHERE clause.
SELECT
x.i.value('.', 'VARCHAR(30)') AS col
FROM (
SELECT
CAST('<i>' + REPLACE(col1, ' ', '</i><i>') + '</i>' AS XML) AS col
FROM @t
) a CROSS APPLY col.nodes('//i') x(i)
WHERE x.i.value('.', 'VARCHAR(30)') LIKE 'V%'
/*
col
------------------------------
V3
V71
V223
V550
*/
Here is the complete code listing:
DECLARE @t table (col1 varchar(50))
INSERT INTO @t SELECT 'MOTOROLA RAZR V3 CAR CHARGER'
INSERT INTO @t SELECT 'MOTOROLA V71 BATT COVER/SLVR'
INSERT INTO @t SELECT 'POWER CHARGER MOT V223'
INSERT INTO @t SELECT 'KODAK V550 LI-ION 720mAh'
SELECT
x.i.value('.', 'VARCHAR(30)') AS col
FROM (
SELECT
CAST('<i>' + REPLACE(col1, ' ', '</i><i>') + '</i>' AS XML) AS col
FROM @t
) a CROSS APPLY col.nodes('//i') x(i)
WHERE x.i.value('.', 'VARCHAR(30)') LIKE 'V%'
/*
col
------------------------------
V3
V71
V223
V550
*/
This approach is pretty handy and very compact to write. While this is good for small sets of data, you might find performance problems if applied on very large volume of data. Did you ever come across an interesting string manipulation requirement that solved (or want to solve) in TSQL? Feel free to share it with us!
I heard this question a few times recently. Most database products support Auto-number columns that generate a unique number, every time a row is inserted. In SQL Server, they are called IDENTITY columns.
While creating a table, you can specify a certain column as an IDENTITY column. The following script shows an example.
USE Tempdb
GO
CREATE TABLE Employees (
EmpID INT IDENTITY,
EmpName VARCHAR(20) )
GO
INSERT INTO Employees (EmpName) SELECT 'Jacob'
INSERT INTO Employees (EmpName) SELECT 'Steve'
SELECT * FROM Employees
/*
EmpID EmpName
----------- --------------------
1 Jacob
2 Steve
*/
You can specify a SEED value while creating an IDENTITY column. For example, the following example specifies that the SEED value of the IDENTITY column is 100. The next identity value generated will be 100.
IF OBJECT_ID('Employees','U') IS NOT NULL
DROP TABLE Employees
CREATE TABLE Employees (
EmpID INT IDENTITY (100, 1),
EmpName VARCHAR(20) )
GO
INSERT INTO Employees (EmpName) SELECT 'Jacob'
INSERT INTO Employees (EmpName) SELECT 'Steve'
SELECT * FROM Employees
/*
EmpID EmpName
----------- --------------------
100 Jacob
101 Steve
*/
Most of the times, you would want to increment IDENTITY values by 1. However, there may be times when you want to use a different incremental value. The following example creates an IDENTITY column that increments by 5, instead of 1.
IF OBJECT_ID('Employees','U') IS NOT NULL
DROP TABLE Employees
CREATE TABLE Employees (
EmpID INT IDENTITY (1, 5),
EmpName VARCHAR(20) )
GO
INSERT INTO Employees (EmpName) SELECT 'Jacob'
INSERT INTO Employees (EmpName) SELECT 'Steve'
INSERT INTO Employees (EmpName) SELECT 'Bob'
SELECT * FROM Employees
/*
EmpID EmpName
----------- --------------------
1 Jacob
6 Steve
11 Bob
*/
I found this question in the MSDN SQL Server XML forum and wrote a query to help the user who posted it. I wanted to write a query using XQuery but could not write it instantly. There does not seem to be an easy way to get the full path of an element or attribute. One option I can think of, is writing a recursive function that walks through the XML tree and generates the path string. When we examined the function that compares two XML documents, we saw how to recursively walk through the elements of an XML document. We could probably use a similar approach to achieve this as well.
The sample code that I wrote for that user did not use XQuery, but used OPENXML. It was quicker to write this using OPENXML. However, this has a number of limitations: the code cannot be embedded into a function, only one XML document can be processed at a time etc etc.
Here is the sample XML document.
<books>
<book id="101">
<title>my book</title>
<author>Myself</author>
</book>
<book id="202">
<title>your book</title>
<author>you</author>
</book>
</books>
And here is the output we are trying to generate.
/*
path text
-------------------------------------------------- ------------------------------
books\book\@id 101
books\book\title my book
books\book\author Myself
books\book\@id 202
books\book\title your book
books\book\author you
*/
Let us start writing the query. The first step is to shred the data into a rowset using OPENXML(). Here is the code that shreds the XML document to a rowset.
DECLARE
@idoc INT,
@xml XML
SELECT @xml = '
<books>
<book id="101">
<title>my book</title>
<author>Myself</author>
</book>
<book id="202">
<title>your book</title>
<author>you</author>
</book>
</books>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
SELECT * FROM OPENXML(@idoc,'/',3)
EXEC sp_xml_removedocument @idoc
/*
id parentid nodetype localname prev text
---- -------- -------- -------------------- -------------------- ---------------
0 NULL 1 books NULL NULL
2 0 1 book NULL NULL
3 2 2 id NULL NULL
10 3 3 #text NULL 101
4 2 1 title NULL NULL
11 4 3 #text NULL my book
5 2 1 author 4 NULL
12 5 3 #text NULL Myself
6 0 1 book 2 NULL
7 6 2 id NULL NULL
13 7 3 #text NULL 202
8 6 1 title NULL NULL
14 8 3 #text NULL your book
9 6 1 author 8 NULL
15 9 3 #text NULL you
*/
The above code builds a tabular result. The table implements a parent-child relationship, using the id and parentid columns. We can use these columns to build the relationship hierarchy. We can then write a recursive query that builds a relationship tree and can retrieve the page of each node and attribute in the XML document.
We have seen a number of posts in the past, that performs recursive operation. You can find a few of them below:
- TSQL Lab 10 - Performing recursive updates in SQL Server
- TSQL Lab 11 - Writing a recursive procedure to update the count of child items under each parent category
- TSQL Lab 12 - Writing a recursive procedure to handle more than 32 levels
- TSQL Lab 14 - Performing a recursive update for more than 32 levels
- TSQL Lab 18 - Performing Recursive Updates using CTE
- TSQL Lab 20 - Performing recursive updates using a BOTTOM to TOP recursive CTE
- Recursive CTE and Ordering of the hierarchical result
If you are not familiar with recursive queries, I would suggest reading the above articles or refer "recursive CTE" in books online or in a location of your choice. Assuming that you are familiar with recursive queries, I am presenting the final solution below.
DECLARE
@idoc INT,
@xml XML
SELECT @xml = '
<books>
<book id="101">
<title>my book</title>
<author>Myself</author>
</book>
<book id="202">
<title>your book</title>
<author>you</author>
</book>
</books>'
EXEC sp_xml_preparedocument @idoc OUTPUT, @xml
;WITH cte AS (
-- shreds XML to rowset
SELECT * FROM OPENXML(@idoc,'/',3)
), rcte AS (
-- anchor part of recursive query
SELECT 0 AS Level, id, parentid, nodetype, localname, prev,
CAST(text AS VARCHAR(30)) AS Text,
CAST(localname AS VARCHAR(50)) AS path,
CAST(id AS VARBINARY(128)) AS Sort
FROM cte WHERE id = 0
-- recursive part
UNION ALL
SELECT p.level + 1, c.id, c.parentid, c.nodetype, c.localname, c.prev,
CAST(c.text AS VARCHAR(30)),
CAST(p.path + CASE WHEN c.nodetype = 3 THEN '' ELSE '\' END +
CASE WHEN c.nodetype = 2 THEN '@' ELSE '' END +
CASE WHEN c.nodetype = 3 THEN '' ELSE c.localname END AS VARCHAR(50)),
CAST(p.Sort + CAST(c.id AS VARBINARY(4)) AS VARBINARY(128))
FROM cte c
INNER JOIN rcte p ON p.id = c.parentid
)
-- the final query
SELECT path, text FROM rcte
WHERE text IS NOT NULL
ORDER BY sort
EXEC sp_xml_removedocument @idoc
/*
OUTPUT:
path text
-------------------------------------------------- ------------------------------
books\book\@id 101
books\book\title my book
books\book\author Myself
books\book\@id 202
books\book\title your book
books\book\author you
*/
The sample code presented above does not handle XML namespaces. I will write a follow-up post that handles namespaces as well. I want to write an XQuery version of the above query and will try to post them soon.
One of my readers recently contacted me with an XML shaping requirement. He wanted to generate an XML document with a specific structure from a result set that he has created. Here is the source data.
RootNode ParentNode Node Name Number Valid Value
--------------- ---------- ----- ----- ------ ----- ---------------
Reference Basic Book Book1 1 true AH.KL.LO
Reference App A App1 1 true AIK.LPO
Reference App A App2 2 true JUI.MKJ
Reference Sub B SubA 1 false LOP.MJH
Reference Sub B SubB 2 false GTY.JUI
Reference DI C NULL 1 NULL PLW.KJU
Here is the XML document that he wanted to generate from the above source data.
<Reference>
<Basic>
<Book Name="Book1" number="1" Valid="true">AH.KL.LO</Book>
</Basic>
<App>
<A Name="App1" number="1" Valid="true">AIK.LPO</A>
<A Name="App2" number="2" Valid="true">JUI.MKJ</A>
</App>
<Sub>
<B Name="SubA" number="1" Valid="false">LOP.MJH</B>
<B Name="SubB" number="2" Valid="false">GTY.JUI</B>
</Sub>
<DI>
<C number="1">PLW.KJU</C>
</DI>
</Reference>
Let us go ahead and try to write a FOR XML query to generate the above XML document. First of all, let us create a table variable and fill it with the sample data. Here is the script to create the sample data.
DECLARE @t TABLE (
RootNode VARCHAR(15),
ParentNode VARCHAR(10),
Node VARCHAR(5),
Name VARCHAR(5),
Number VARCHAR(1),
Valid VARCHAR(5),
Value VARCHAR(15)
)
INSERT INTO @t
SELECT 'Reference','Basic','Book','Book1','1','true','AH.KL.LO' UNION ALL
SELECT 'Reference','App','A','App1','1','true','AIK.LPO' UNION ALL
SELECT 'Reference','App','A','App2','2','true','JUI.MKJ' UNION ALL
SELECT 'Reference','Sub','B','SubA','1','false','LOP.MJH' UNION ALL
SELECT 'Reference','Sub','B','SubB','2','false','GTY.JUI' UNION ALL
SELECT 'Reference','DI','C',NULL,'1',NULL,'PLW.KJU'
Let us start writing the query. Let us first generate the "Book" node with its 3 attributes.
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid'
FROM @t
WHERE node = 'Book'
FOR XML PATH('Book')
The above code generates the following XML output.
<Book Name="Book1" number="1" valid="true" />
The "Book" node holds a text value too. Let us write the code to generate the text value.
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'Book'
FOR XML PATH('Book')
<Book Name="Book1" number="1" valid="true">AH.KL.LO</Book>
Now, let us add the parent nodes: "Basic" and "Reference".
SELECT
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'Book'
FOR XML PATH('Book'), TYPE
) AS Basic
FOR XML PATH(''), ROOT('Reference')
<Reference>
<Basic>
<Book Name="Book1" number="1" valid="true">AH.KL.LO</Book>
</Basic>
</Reference>
Let us add the "App" node to this version of the query.
SELECT
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'Book'
FOR XML PATH('Book'), TYPE
) AS Basic,
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'A'
FOR XML PATH('A'), TYPE
) AS App
FOR XML PATH(''), ROOT('Reference')
<Reference>
<Basic>
<Book Name="Book1" number="1" valid="true">AH.KL.LO</Book>
</Basic>
<App>
<A Name="App1" number="1" valid="true">AIK.LPO</A>
<A Name="App2" number="2" valid="true">JUI.MKJ</A>
</App>
</Reference>
Now it is time to add the "Sub" element.
SELECT
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'Book'
FOR XML PATH('Book'), TYPE
) AS Basic,
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'A'
FOR XML PATH('A'), TYPE
) AS App,
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'B'
FOR XML PATH('B'), TYPE
) AS Sub
FOR XML PATH(''), ROOT('Reference')
<Reference>
<Basic>
<Book Name="Book1" number="1" valid="true">AH.KL.LO</Book>
</Basic>
<App>
<A Name="App1" number="1" valid="true">AIK.LPO</A>
<A Name="App2" number="2" valid="true">JUI.MKJ</A>
</App>
<Sub>
<B Name="SubA" number="1" valid="false">LOP.MJH</B>
<B Name="SubB" number="2" valid="false">GTY.JUI</B>
</Sub>
</Reference>
Let us now add the "DI" node and write the final version of the query.
SELECT
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'Book'
FOR XML PATH('Book'), TYPE
) AS Basic,
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'A'
FOR XML PATH('A'), TYPE
) AS App,
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'B'
FOR XML PATH('B'), TYPE
) AS Sub,
(
SELECT
name AS '@Name',
number AS '@number',
valid AS '@valid',
value AS 'data()'
FROM @t
WHERE node = 'C'
FOR XML PATH('C'), TYPE
) AS DI
FOR XML PATH(''), ROOT('Reference')
<Reference>
<Basic>
<Book Name="Book1" number="1" valid="true">AH.KL.LO</Book>
</Basic>
<App>
<A Name="App1" number="1" valid="true">AIK.LPO</A>
<A Name="App2" number="2" valid="true">JUI.MKJ</A>
</App>
<Sub>
<B Name="SubA" number="1" valid="false">LOP.MJH</B>
<B Name="SubB" number="2" valid="false">GTY.JUI</B>
</Sub>
<DI>
<C number="1">PLW.KJU</C>
</DI>
</Reference>
FOR XML Tutorials
Continued from FOR XML EXPLICIT Tutorial – Part 3
This is a late addition to the 3 part FOR XML TUTORIAL I wrote last year. You can find Part 1 here, Part 2 here and Part 3 here.
When generating the XML document, FOR XML EXPLICIT processes rows in the same order as they are returned by the query. So, most of the times, you need to specify an ORDER BY clause in your query, so that the XML output will contain information in the desired order. In Part 3, we used a calculated column to generate certain values and used those values to order the result. Since we did not want the 'artificial sort column' in the XML output, we used an outer query to filter out the sort column.
One of the readers, GooseCandy, suggested that it will be a better idea to use the 'hide' directive, rather than using an outer query to hide the 'sort' column. He is right and I would like to post a new version of the sample code we saw in Part 3, using the 'hide' directive.
Here is the new version of the query using the 'hide' directive.
declare @agent table
(
AgentID int,
Fname varchar(5),
SSN varchar(11)
)
insert into @agent
select 1, 'Vimal', '123-23-4521' union all
select 2, 'Jacob', '321-52-4562' union all
select 3, 'Tom', '252-52-4563'
declare @address table
(
AddressID int,
AddressType varchar(12),
Address1 varchar(20),
Address2 varchar(20),
City varchar(25),
AgentID int
)
insert into @address
select 1, 'Home', 'abc', 'xyz road', 'RJ', 1 union all
select 2, 'Office', 'temp', 'ppp road', 'RJ', 1 union all
select 3, 'Home', 'xxx', 'aaa road', 'NY', 2 union all
select 4, 'Office', 'ccc', 'oli Com', 'CL', 2 union all
select 5, 'Temp', 'eee', 'olkiu road', 'CL', 2 union all
select 6, 'Home', 'ttt', 'loik road', 'NY', 3
SELECT
1 AS Tag,
NULL AS Parent,
0 AS 'Agents!1!Sort!hide',
NULL AS 'Agents!1!',
NULL AS 'Agent!2!AgentID',
NULL AS 'Agent!2!Fname!Element',
NULL AS 'Agent!2!SSN!Element',
NULL AS 'AddressCollection!3!Element',
NULL AS 'Address!4!AddressType!Element',
NULL AS 'Address!4!Address1!Element',
NULL AS 'Address!4!Address2!Element',
NULL AS 'Address!4!City!Element'
UNION ALL
SELECT
2 AS Tag,
1 AS Parent,
AgentID * 100,
NULL, AgentID, Fname, SSN,
NULL,NULL, NULL, NULL, NULL
FROM @Agent
UNION ALL
SELECT
3 AS Tag,
2 AS Parent,
AgentID * 100 + 1,
NULL,NULL,NULL, NULL,
NULL, NULL, NULL, NULL, NULL
FROM @Agent
UNION ALL
SELECT
4 AS Tag,
3 AS Parent,
AgentID * 100 + 2,
NULL,NULL,NULL,NULL,NULL,
AddressType, Address1, Address2, City
FROM @Address
ORDER BY [Agents!1!Sort!hide]
FOR XML EXPLICIT
Note the usage of the "hide" directive on the column we generated for sorting. Columns marked with "hide" will be ignored by the XML processor. FOR XML EXPLICIT supports a few other interesting directives too. I will cover them in a future post.
FOR XML EXPLICIT Tutorial- Part 1
FOR XML EXPLICIT Tutorial - Part 2
FOR XML EXPLICIT Tutorial – Part 3
FOR XML EXPLICIT Tutorial – Part 4
FOR XML Tutorials