Refactoring and static code analysis tool for SQL Server
Got a SQL Server or .NET question? Discuss it in the forums. (SQL Server Forums | Dot NET Forums)
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

Jacob's Blog

Recent Blog Posts

Most Viewed

Archive

SQL Server IDENTITY Columns

I have written over a dozen articles covering almost all aspects of SQL Server IDENTITY columns. The intention of this page is to serve as an index, so that people can find the right post based on the specific piece of information they are looking for...
Posted: 03-09-2009 5:44 AM by Jacob Sebastian | with no comments
Filed under:

SQL Server IDENTITY Columns - A few more interesting facts

This is my 14th article on SQL Server IDENTITY values. In each post we discussed a couple of interesting points related to the IDENTITY columns. I saw a number of IDENITY related questions in various online SQL Server forums and saw a lot of cases where...
Posted: 03-08-2009 1:50 PM by Jacob Sebastian | with no comments
Filed under:

SQL Server - IDENTITY related functions, commands, system variables, catalog views

In the previous posts we have examined IDENTITY columns in detail. We discussed several IDENTITY related functions, commands, system variables and terms. This post intends to summarize them. IDENTITY Property IDENTITY is an attribute that you can assign...
Posted: 02-12-2009 5:56 AM by Jacob Sebastian | with no comments
Filed under:

SQL Server - How to retrieve the identity values generated by a multi-row insert?

After inserting a row to a table with an IDENTITY column, you can query the global variable @@identity or call SCOPE_IDENTITY() function to retrieve the newly inserted IDENTITY value. In a previous post we saw that @@identity returns the last IDENTITY...
Posted: 02-06-2009 5:59 AM by Jacob Sebastian | with no comments
Filed under:

SQL Server - @@IDENTITY, SCOPE_IDENTITY() and IDENT_CURRENT()

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...
Posted: 02-05-2009 12:38 AM by Jacob Sebastian | with no comments
Filed under: , ,

SQL Server - How to find out the SEED and INCREMENT values of an IDENTITY column?

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...
Posted: 02-03-2009 12:32 AM by Jacob Sebastian | with no comments
Filed under: , , , , , , , , , , , , ,

SQL Server - IDENTITY - Why do I have duplicate identity values?

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...
Posted: 02-03-2009 12:15 AM by Jacob Sebastian | with no comments
Filed under: , , , , , , ,

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...
Posted: 01-30-2009 12:10 AM by Jacob Sebastian | with no comments
Filed under: , , , , ,

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...
Posted: 01-30-2009 12:05 AM by Jacob Sebastian | with no comments
Filed under: , , , , , , , ,

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...
Posted: 01-28-2009 11:59 PM by Jacob Sebastian | with no comments
Filed under: , , ,

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...
Posted: 01-29-2009 7:54 AM by Jacob Sebastian | with no comments
Filed under:

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...
Posted: 01-28-2009 3:10 PM by Jacob Sebastian | with no comments
Filed under:

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...
Posted: 01-28-2009 2:58 PM by Jacob Sebastian | with no comments
Filed under:

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...
Posted: 01-27-2009 2:51 PM by Jacob Sebastian | with no comments
Filed under:

SQL Server - Issues with @@identity

I have had a bad time debugging a stored procedure recently which apparently does not update foreign keys correctly on a few related tables. The part of the procedure where the problem occurred looked like the following: 1. Insert values to a certain...
Posted: 10-16-2006 8:45 AM by Jacob Sebastian | with no comments
Filed under:

Copyright © Beyondrelational.com