Archive

Posts Tagged ‘IDENTITY Columns’

All about IDENTITY columns in SQL Server

May 3, 2012 1 comment

We know that IDENTITY column property creates an IDENTITY column in a table. This property can be used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
But there are certain things that some of us are not aware of about the IDENTITY property.

Do you know that:

1. Not only INT, but you can also use IDENTITY property with other datatypes like: SMALLINT, TINY INT, BIGINT, NUMERIC and DECIMAL.

2. System function @@IDENTITY returns the last identity value used by the current session.

3. Function IDENT_SEED returns the seed value which is the starting number of IDENTITY column, default is 1.

4. Function IDENT_INCR returns the increment value of IDENTITY column, default is 1.

5. Function IDENT_CURRENT accepts table name as parameter and returns the last identity value inserted into that table.

6. IDENTITY() function as mentioned above can be used with CREATE/ALTER TABLE statements, but it can also be used with SELECT INTO statement while creating a table on the fly with SELECT statement. Check [here].

7. With INSERT statement you can’t provide IDENTITY column name and value, unless you explicitly SET IDENTITY_INSERT ON.

8. SET IDENTITY_INSERT ON does not work with Table Variables.

9. Keyword IDENTITYCOL automatically refers to the IDENTITY column of the table.

–> Let’s check how we can use IDENTITYCOL keyword mentioned on 9th point discussed above:

-- Using IDENTITYCOL at WHERE clause and ORDER BY clause:
SELECT *
FROM [Sales].[SalesOrderDetail]
WHERE IDENTITYCOL <= 100
ORDER BY IDENTITYCOL


-- Using IDENTITYCOL with COUNT() function instead of the original column name & with Column name separately:
SELECT 
	SalesOrderID, 
	COUNT(IDENTITYCOL) AS Cnt1,
	COUNT(SalesOrderDetailID) AS Cnt2
FROM [Sales].[SalesOrderDetail]
WHERE ModifiedDate BETWEEN '01/01/2006' AND '02/01/2006'
GROUP BY SalesOrderID


-- Using IDENTITYCOL at column level and at JOIN's ON clause:
SELECT 
	D.SalesOrderID, D.IDENTITYCOL, D.OrderQty, D.UnitPrice, D.UnitPriceDiscount, [ProductID], 
	H.AccountNumber, H.SubTotal, H.TotalDue
FROM [Sales].[SalesOrderDetail] D
INNER JOIN [Sales].[SalesOrderHeader] H
	ON H.IDENTITYCOL = D.SalesOrderID
WHERE H.OrderDate BETWEEN '01/01/2006' AND '02/01/2006'

Check the output of the last 2 SQL statements:
– The 1st output shows same COUNTS for both the columns.
– And 2nd output shows the original name of Column name appearing on the header even when we are using the IDENTITYCOL keyword.

As shown above you don’t have to go, look and type the actual IDENTITY column names of different tables and can be taken care of by using the common IDENTITYCOL keyword.
 

Check the same demo here in YouTube:

Advertisement
Categories: SQL Tips Tags: ,

SQL Basics – IDENTITY property of a Column (in a table)

October 19, 2010 1 comment

With IDENTITY property you can:

1. Creates an identity column in a table.

2. Used for generating key values, based on the current seed & increment.

3. Each new value for a particular transaction is different from other concurrent transactions on the table.
 

–> You can check the demo about IDENTITY property here:


 

–> IDENTITY property on a column does not guarantee:

1. Uniqueness of the value,

2. Consecutive values within a transaction,

3. Consecutive values after server restart or other failures,

4. Reuse of values,
 

–> SQL Script used in Demo:

-- IDENTITY property of a Column

CREATE TABLE [dbo].[Employee](
	[EmployeeID]	int				NOT NULL IDENTITY (100, 1),
	[EmployeeName]	nvarchar(100)	NOT NULL,
	[Gender]		nchar(1)		NULL,
	[DOB]			datetime		NULL,
	[DOJ]			datetime		NULL,
	[DeptID]		int				NULL
)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('JHON K', 'M', NULL, '2010-01-01', NULL)

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Brock H')
GO

SELECT * FROM [dbo].[Employee]
GO


-- Inserting Explicit value in IDENTITY column:

SET IDENTITY_INSERT [dbo].[Employee] ON

INSERT INTO [dbo].[Employee] ([EmployeeID],[EmployeeName])
VALUES (1000, 'Brock H')

SET IDENTITY_INSERT [dbo].[Employee] OFF
GO


SELECT * FROM [dbo].[Employee]
GO

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Jhon B')
GO

SELECT * FROM [dbo].[Employee]
GO

 

–> Check more articles on IDENTITY property:

– RE-SEED an IDENTITY value of a Column

– Using IDENTITY() function with SELECT into statement

– All about IDENTITY columns, some more secrets

– IDENTITY property behavior with SQL Server 2012 and above
 

Categories: SQL Basics Tags: ,