Sequence feature TIP #76

Although it is a old feature for those who knows ORACLE but for SQL server developers it is a new feature.

Let understand it by an example. Suppose we want an auto incremented column a part from primary key which is a identity column,

then to achieve this we can use sequence feature.

We can create  sequence feature by following command

Sequence

“CREATE SEQUENCE StudentEnrollmentId AS INT
START WITH 2014000
INCREMENT BY 1”

so if you see above statement we have created a sequence with name StudentEnrollmentId which is an integer type sequence and first value means starting point is 2014000 and each time when we call sequence it will be incremented by 1.

We can create same sequence by screen also as shown in below figure

SequenceView

We have other option also  as shown in below

CREATE SEQUENCE SEQUENCE_NAME
AS DATA_TYPE
START WITH <constant>
INCREMENT BY <constant>
MINVALUE value
MAXVALUE value
CYCLE | NO CYCLE
CACHE int | NO CACHE

as shown in above option we can provide minimum & maximum for sequence. We have cycle option mean restart again after reaching maximum or minimum.

Now we can use it with following way

Sequence_1

“SELECT NEXT VALUE FOR StudentEnrollmentId”

I hope this might help you somewhere.

Enjoy !!!

Rj !!!

A Interview question How to determine what value you have updated of a column ? A output clause example #TIP 48

 

Problem:-

Sometimes it may require that whatever you have updated you want to know what was the previous value of that column?

It was asked by many interviewer in the interview how to avoid trigger or how to know what was previous value before update ?

Solution:-

Now to achieve this the basic step is before updating the record select those value and have it in a temporary variable.

But We are doing here by a new feature (not exactly new introduced in SQL Server 2005) which is OUTPUT CLAUSE

Suppose I have tbluser table in which a user record with id 1 having userame “staff”

Now I have to update this in capital letter. To achieve this we have to write following query

UPDATE tblUser
SET UserName = ‘STAFF’
OUTPUT INSERTED.UserName,
   deleted.UserName
WHERE userId= 1
   

When you run it you will get output as shown below

Output_Clause

 

I hope it may helpful you somewhere enjoy !!!

RJ!!!