Browse by Tags
All Tags »
IDENTITY (
RSS)
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...
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...