:::: MENU ::::

Posts Categorized / Identity Columns

  • Mar 07 / 2012
  • 0
Data Modeling and Database Design, DBA best practices, dbDigger, Identity Columns, Performance Tunning and Optimization

Utilize the negative half of identity column data type

Identity columns are auto generated sequences used for ensuring row uniqueness at table level. Following data types of Numeric family may be used as data type of identity column.

  • int
  • bigint
  • smallint
  • tinyint
  • decimal/numeric

Above mentioned data types of numeric family may store negative values except the TINYINT. It is important to note that half of data types storage capacity of these data types is assigned to negative values. For example SMALLINT can have values from -32768 to 32767. It can hold -32768 to -1 negative values, a zero and 1 to 32767 positive values. Please also have a look at following table (Microsoft Technet) to get an idea about all numeric data types

Utilize the negative part of data type for identity columns

We may analyze that except the TINYINT data type, all other numeric data types have half capacity reserved for non negative values.

Point to Consider

Point to consider here is that identity column capacity should be fully utilized by including the negative part of data type also. Otherwise you are going to leave unused almost half of data type capacity. Very simple point is to give the lowest negative end of data type as SEED value of identity column. Consider the following demo

 -- Create table for demo  
 IF EXISTS   
 (SELECT * FROM sys.objects where name = 'IdentityDemo')  
 DROP TABLE IdentityDemo  
 GO  
 CREATE TABLE IdentityDemo (id SMALLINT identity(1 ,1), EmpName varchar(50))  
 GO  
 -- Insert values more than +ive part of smallint  
 insert into IdentityDemo values ('Atif')  
 GO 32769  

In above demo we used SMALLINT data type with seed and increment both 1, while creating the table. So inserting the values it failed for values more than 32767 and error was generated.

Error when identity column is saturated

Same may be verified by selecting the data from table

 -- Verify the inserted rows  
 SELECT * FROM IdentityDemo order by ID DESC  

The identity column is saturated and while negative position of data type is there unused. Now following demo would utilize the negative portion also and would have double capacity of storage

 -- Create table for demo with negative seed  
 IF EXISTS  
 (SELECT * FROM sys.objects where name = 'IdentityDemo')  
 DROP TABLE IdentityDemo  
 GO  
 CREATE TABLE IdentityDemo (id SMALLINT identity(-32768 ,1), EmpName varchar(50))  
 GO  
 -- Insert values more than +ive part of smallint  
 insert into IdentityDemo values ('Atif')  
 GO 34769  

This time insertion is successful with 34769 values. Can be verified by

 -- Verify the inserted rows  
 SELECT * FROM IdentityDemo order by ID DESC  

Same is the case for int, bigint and decimal/numeric also. So use the identity column as valuable resource and fully utilize it to avoid early saturation.

  • Feb 07 / 2012
  • 0
Data Modeling and Database Design, DBA Interview questions, dbDigger, Identity Columns, SQL Server Error messages, T-SQL Interview Questions

Multiple identity columns specified for table %’. Only one identity column per table is allowed

Following error message would be generated if more than one identity column is tried to define in a table.
Multiple identity columns specified for table ‘%’. Only one identity column per table is allowed
SQL Server does not allow to have more than one identity column in a single table. Consider following design for generating error message.

 CREATE TABLE identTest  
 (ID1 SMALLINT IDENTITY(1,1),   
 ID2 SMALLINT IDENTITY(1,1))  
 GO  

There would be hardly any scenario when such a design is required. However such functionality may be achieved by a couple of work around

  • Use trigger to insert values in second column
  • Configure second column as computed column and reflect values from identity column
  • Feb 07 / 2012
  • 0
dbDigger, Identity Columns, T-SQL Interview Questions, T-SQL Tips and Tricks

Removing gaps in identity column values, duplicate value errors

I have got a query about gaps in values of identity column and error messages related to duplication in values of identity column. The problem may be divided in two parts Gaps in identity column values and Duplication error messages related to seed property. Let me discus these one by one

Gaps in identity column values

Gaps in values of identity columns are generated by failed inserts or successful delete operations on table. It has no considerable negative impact but may be removed for optimized usage of identity column data type. For example if there is an identity column with data type tinyint then after reaching value of 255 the column would not be able to hold further rows. Same is true for other identity data types like int, bigint, smallint, decimal or numeric. In such circumstances removing the gaps among values of identity column may optimize the usage of identity column data type.
A simple way to this task may be to drop and re-create the identity column with same name and properties. When re-created it would be optimized and have no gaps among values. Performing drop and re-create through T-SQL would be much more efficient than through SSMS (have a look at my article on ALTER TABLE operations through T-SQL and SSMS). But through T-SQL you would not be able to re-create the column at any position in table but last. So if you have used select * any where in application code (that i always try to avoid), then change of column order may raise issues. In that case you may use SSMS to delete and re-create the identity column at required position in table. Consider the following example for above process

 -- Create table for test  
 if exists (select * from sys.objects where name = 'IDTest' )  
 drop table IDTest  
 GO  
 create table  
 IDTest(ID smallint primary key identity (1,1), name varchar(50))  
 GO  
 -- Populate the table  
 insert into IDTest values ('Atif ')  
 GO 300  
 -- Delete records  
 delete from IDTest where id between 11 and 60  
 GO  
 -- Verify the gap created by delete operation  
 select * from IDTest order by ID  
 GO  

removing gaps in values of identity column

We may look that gap exists in values of identity column after delete operation. Now we may apply the drop and re-create approach for identity column to remove the gaps and make the column optimized. For task through SSMS open table in designer, delete the identity column and then create it again with same properties by using insert column option.
I have used T-SQL to drop and recreate the identity column.

 
-- Drop the primary key constraint first (do not forget to change the constraint name).  
 ALTER TABLE IDTest DROP PK__IDTest__3214EC2720C1E124  
 GO  
 -- drop the identity column  
 ALTER TABLE IDTest DROP COLUMN ID  
 GO  
 -- Re-create the identity column  
 ALTER TABLE IDTest ADD ID SMALLINT PRIMARY KEY IDENTITY (1,1)  
 GO  

At this point identity column ID is created and populated without any gap. But it would be created as last column with respect to columns order in table.

Duplication error messages related to seed and incremental value

Going back to second problem, it relates to SEED property of identity column. If after manipulating seed value, errors are being generated for duplication then follow these steps.

  1. Get maximum value in identity column
  2. ReSeed by providing the maximum value

It should be kept in mind that value retrieved through max() function may not necessarily be the maximum identity value. That may be the case when some rows have been deleted with last (highest) identity values. However setting the max value as SEED would work fine and no duplication issue would be raised.

 
-- Get last identity value  
 SELECT Max(ID) from IDTest  
 GO  

As we have 250 as max value in our re-created identity column so use it for RESEED

 -- Get last identity value  
 DBCC CHECKIDENT ('IDTest', RESEED, 250)  
 GO  

In this way duplication due to invalid SEED value may be prevented. It is considerable that DROP and RE-CREATE of identity column may also solve duplication error issue.

  • Feb 01 / 2010
  • 0
Data Modeling and Database Design, dbDigger, Identity Columns, T-SQL Interview Questions, T-SQL Tips and Tricks

Insert value in identity column when identity column is only column in table

I came across a question that how to insert value in identity column when identity column is only column in the table. And to set identity insert on is not allowed. It is interesting scenario. So answer is to use DEFAULT VALUES. Consider following code for demo that how to insert value in identity column when identity column is only column in table.

USE AdventureWorks  
GO  
CREATE TABLE #TestInsert(onlyColum TINYINT IDENTITY (1,1))  
GO  

INSERT INTO #TestInsert DEFAULT VALUES 
GO  

SELECT * FROM #TestInsert  
GO 

And you would get inserted value from this temporary table.

  • Jan 29 / 2009
  • 0
dbDigger, Identity Columns

Msg 1077, Level 16, State 1, Line 3 INSERT into an identity column not allowed on table variables.

The error message
Msg 1077, Level 16, State 1, Line 3
INSERT into an identity column not allowed on table variables.
is a run time error message of severity level 16. This error message appears when you try to insert explicitly values into an identity column of a table variable. You cannot explicitly insert values into an identity column of a table variable.

  • Jan 17 / 2009
  • 0
dbDigger, Identity Columns

Cannot add identity column, using the SELECT INTO statement, to table ‘%.*ls’, which already has column ‘%.*ls’ that inherits the identity property.

The error message
Msg 8108, Level 16, State 1, Line 1
Cannot add identity column, using the SELECT INTO statement, to table ‘%.*ls’, which already has column ‘%.*ls’ that inherits the identity property.

is a run time error message of severity level 16. This error message can appear on all versiond of SQL Server when you try to execute a SELECT INTO statement in which you use the IDENTITY function although the table you select from already contains a column with the IDENTITY property that the same column in the new table will inherit.
As Every table can contain only one IDENTITY column. You must remove the IDENTITY function from the SELECT INTO statement to avoid the error.

Pages:12
Consult us to explore the Databases. Contact us