Jacob's Blog

SQL Server - How do I Insert an explicit value into an IDENTITY column? How do I Update the value of an IDENTITY Column?

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...

by Jacob Sebastian on 01-30-2009 | 17,986 Views

SQL Server - IDENTITY - How to change/reset the IDENTITY values?

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...

by Jacob Sebastian on 01-30-2009 | 4,845 Views

SQL Server - My IDENTITY values are not sequential! Why do I have missing numbers in the IDENTITY column?

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...

by Jacob Sebastian on 01-28-2009 | 1,867 Views

SQL Server - Restrictions on the SEED and INCREMENT values of an IDENTITY column

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...

by Jacob Sebastian on 01-28-2009 | 1,413 Views

SQL Server - How to create an Auto-number (IDENTITY) column that counts backwards?

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...

by Jacob Sebastian on 01-28-2009 | 3,115 Views

SQL Server - What are the data types supported in IDENTITY columns?

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...

by Jacob Sebastian on 01-28-2009 | 4,572 Views

TSQL - How to revalidate/refresh/recompile all stored procedures in a database?

When you create/alter a stored procedure, SQL Server will perform a certain set of validations and will throw an error if the validation fails. After a stored procedure is created, you might make changes to the underlying objects that could break the...

by Jacob Sebastian on 01-28-2009 | 2,914 Views

XQuery Lab 40 - Extracting words matching a pattern from a varchar column

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...

by Jacob Sebastian on 01-27-2009 | 1,579 Views

How to create an Auto-number column in SQL Server?

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...

by Jacob Sebastian on 01-27-2009 | 1,905 Views

XQuery Lab 39 - Retrieve Fully qualified path of attributes and elements with OPENXML()

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...

by Jacob Sebastian on 01-23-2009 | 3,587 Views

SQLXML Lab 4 - Loading information into multiple unrelated tables

All the examples we saw in the previous posts loaded information into a single table. There are times when you might need to load data into multiple tables. Some times these tables may be related and some times they may not be. In this post, we will see...

by Jacob Sebastian on 01-21-2009 | 434 Views

How to write a query to filter rows against a parameter list

I wrote a couple of posts that show how to do string parsing using XML, some time back. Those posts explained how to retrieve a result set from a delimited string using the XML approach. You can find those articles in the links given below. http://beyondrelational...

by Jacob Sebastian on 01-18-2009 | 790 Views

SQL Server Query Optimization Basics 1 - Does adding an index always help?

One of the questions I used to ask while interviewing developers for my company, is about optimizing queries. Most of the times I hear people say that the first thing to do is to add enough indexes so that the joins and filters will execute faster. Seldom...

by Jacob Sebastian on 01-14-2009 | 943 Views

FOR XML PATH – Yet another shaping example using FOR XML PATH

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 ...

by Jacob Sebastian on 01-09-2009 | 4,914 Views

FOR XML EXPLICIT Tutorial – Part 4

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...

by Jacob Sebastian on 01-07-2009 | 14,184 Views