How to Reset identity column values in sql server

Here is the sample demonstration for reset identity column value

Step-1: Create table

CREATE TABLE dbo.Emp

(

ID INT IDENTITY(1,1),

Name VARCHAR(10)

)

Step-2:Insert some sample data

INSERT INTO dbo.Emp(name)

VALUES ('Rakesh')

INSERT INTO dbo.Emp(Name)

VALUES ('Rakesh Kalluri')

When we run above query the second insert statement will failed because of varchar(10) length.

Step-3:Check the identity column value

DBCC CHECKIDENT ('Emp')

Even second insert was failed but the identity value is increased .if we insert the another record the identity value is 3

INSERT INTO dbo.Emp(Name)

VALUES ('Kalluri')

SELECT * FROM Emp

Step-4:Reset the identity column value

DELETE FROM EMP WHERE ID=3

DBCC CHECKIDENT ('Emp', RESEED, 1)

INSERT INTO dbo.Emp(Name)

VALUES ('Kalluri')

SELECT * FROM Emp

Comments

Coalesce function

Coalesce function returns the first non-null value among the arguments. Syntax: Coalesce (expression [,..n]) Here is example using Coalesce function Example 1 DECLARE @Str1 varchar ( 10 ), @str2 varchar ( 20 ), @Str3 varchar ( 20 ) SET @Str2 = 'Sql' , @Str3 = 'Server' SELECT COALESCE ( @Str1 , @str2 , @Str3 ) As [Coalesce] In above example @Str2 value is ‘Sql’ , @str3 value is ‘Server’ and @str1 values is Null because it not assigned any value . Output: It return’s “Sql” because Coalesce function return’s first non null value. Example 2: Coalesce in select statement. IF OBJECT_ID ( 'Employee' , 'U' ) IS NOT NULL DROP TABLE Employee CREATE TABLE Employee ( ID INT IDENTITY ( 1 , 1 ) PRIMARY KEY , NAME VARCHAR ( 20 ), SALARY INT ) INSERT INTO Employee ( NAME , SALARY ) VALUES ( 'Rakesh' , 5000 ),(NULL, 6000 ),( 'Naresh...

CONCAT FUNCTION IN SQL SERVER 2012

Concat function is used concatnating the values among the different arguments. Syntax: Concat([String1],[String2]..[StringN]); DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT CONCAT ( @FName , ' ' , @LName ) as [FullName] ; Output: Rakesh Kalluri --Before SQL 2012 Versions we can use like this way DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SET @LName = 'Kalluri' ; SELECT @FName + ' ' + @LName as [FullName] ; Output: Rakesh Kalluri --But Before SQL 2012 Versions there are 2 problems when we contacting . --1.Need to replace NULL Value to Empty.if we directly concat NULL Value the Entire result also NULL. --Before 2012: DECLARE @FName VARCHAR ( 50 ), @LName VARCHAR ( 50 ); SET @FName = 'Rakesh' ; SELECT @FName + ' ' + @LName a...

Rank Functions in SQL SERVER

1 . ROW_NUMBER () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns the sequantial number of a row within the a partition of result set at 1 for the first row of the each partition. 2. RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set. 3. DENSE_RANK () OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Returns rank for rows within the partition of result set.With out any gaps in the ranking. 4. NTILE ( INTEGER_EXPRESSION ) OVER ( [PARTITION BY CLAUSE] < ORDER BY CLUASE >): Distributes the rows in an ordered partition into a specified number of groups. Examples: --create Employee table create table Employee ( EmpId int identity ( 1 , 1 ) primary key , ...

Tech Learn

Search This Blog