SQL 2008 - 2012

Inserted Keyword in Sql2008

create table personbackup

--person

(

  id int primary key,

  fname varchar(10) not null,

  lname varchar(10) not null,

  city varchar(10) not null

)

INSERT INTO person OUTPUT INSERTED.City

VALUES(3,'Ratan','Gupta','Jaipur')

If you want to try the examples in this article, you should first run the following script to create the Person table in a SQL Server database:

Output Keyword in Sql2008

 update person

set

    fname='atul',

    lname='Kumar'

output

    inserted.id,

    deleted.fname,

    deleted.lname,

    inserted.city

where

id=3

Output Keyword in Sql2008

delete from person

output deleted.id,deleted.fname,deleted.lname,deleted.city

into personbackup

where id=3

SElect * from person

select * from personbackup

Through Keyword in Sql2008

Select * from Branch_master

SELECT * FROM Branch_master

WHERE brhcode = 22222

IF @@ROWCOUNT = 0

THROW 50001, 'No results found.', 1

Aggregate function with distinct Keyword in Sql2008

Select Max(distinct brhCode) from Branch_master

Features in Sql2008

Creating a Table in SQL Server

Create TABLE UserDetail

(

       User_Id int NOT NULL IDENTITY(1,1),     

       FirstName varchar(20),

       LastName varchar(40) NOT NULL,

       Address varchar(255),     

       PRIMARY KEY (User_Id)

)

INSERT INTO UserDetail(FirstName, LastName, Address)

VALUES ('Smith', 'Kumar','Capetown'),

    ('Crown', 'sharma','Sydney'),

    ('Copper', 'verma','Jamaica'),

    ('lee', 'verma','Sydney'),

    ('Rajesh', 'Kumar','India'),

    ('Rahu', 'sharma','India'),

    ('Ravi', 'verma','Jamaica'),

    ('Leon', 'verma','Sydney'),

    ('Methews', 'Anglo','Srilanka'),

    ('Ben', 'lon','Newzealand'),

    ('Derrin', 'summy','Jamaica')

go

SELECT * FROM [master].[dbo].[UserDetail]

The table looks as in the following:

In SQL Server 2008
In SQL Server 2005/2008, we have been doing this data paging by writing a stored procedure or a complex query. Here is a sample of how we were using data paging in SQL Server 2005/2008 using the row_number function with an order by clause:

SELECT *

FROM (

SELECT ROW_NUMBER() OVER(ORDER BY User_Id) AS number, *

FROM userdetail) AS TempTable

WHERE number > 0 and number <= 4

The ROW_NUMBER function enumerates the rows in the sort order defined in the over clause.
Now using OFFSET and FETCH NEXT Keywords
OFFSET Keyword -  If we use offset with the order by clause then the query will skip the number of records we specified in OFFSET n Rows.

Select *

from userdetail

Order By User_Id

OFFSET 5 ROWS

In the preceding example, we used OFFSET 5 ROWS, so SQL will skip the first 5 records from the result and display the rest of all the records in the defined order. Now select the query and press F5 to execute the query:
OUTPUT

FETCH NEXT Keywords - When we use Fetch Next with an order by clause only, without Offset then SQL will generate an error.

SELECT *

FROM userdetail

ORDER BY User_Id

--OFFSET 5 ROWS

FETCH NEXT 10 ROWS ONLY;

Output

ORDER BY OFFSET and FETCH NEXT
In this example, query guides how to use both ORDER BY OFFSET and FETCH NEXT with a select statement for creating paging in SQL Server 2012.

SELECT *

FROM userdetail

ORDER BY User_Id

OFFSET 5 ROWS

FETCH NEXT 8 ROWS ONLY;

Output

Paging with stored procedure

In this example, creating a stored procedure with both ORDER BY OFFSET and FETCH NEXT keyword to enhancement the paging in SQL Server 2012.

CREATE PROCEDURE TestPaging

(

  @PageNumber INT,

  @PageSize INT

)

AS

DECLARE @OffsetCount INT

SET @OffsetCount = (@PageNumber-1)*@PageSize

SELECT  *

FROM [UserDetail]

ORDER BY [User_Id]

OFFSET @OffsetCount ROWS

FETCH NEXT @PageSize ROWS ONLY

Now execute the stored procedure and give the page number and page size to test paging. 

EXECUTE TestPaging 1,5

In the preceding query:

1: This is for the first page

5: Number of records to display on the page

Output

 

EXECUTE TestPaging 2,5

In the preceding query:

2: This is for the second page

5: Number of records to display on the page

Output

 



Sequence Keyword in Sql 2012

CREATE SEQUENCE dbo.DemoSequence
START WITH 1
INCREMENT BY 1;

SELECT Next VALUE FOR DemoSequence
SELECT NextOrderID = Next VALUE FOR DemoSequence

Generating a sequence number, a.k.a. auto number, is a common task in an enterprise application. For a single table, you can specify identity field. But, if you want to have database wide sequential number, then you must devise something by yourself before SQL Server 2012. One solution to this problem is to create a table that has a numeric field can be used to store sequential number, then use SQL to increase it every time used one. In SQL Server 2012, we have a new solution - use Sequence.

Create Sequence

To create a Sequence in SQL Server 2012 is very simple. You can create it with SQL Server Management Studio or T-SQL.

1. Create Sequence with SQL Server Management Studio
   In Object Explorer window of SQL Server Management Studio, there is a Sequences node under Database -> [Database Name] -> Programmability. You can right click on it to bring up context menu, and then choose New Sequence… to open the New Sequence window. In New Sequence window, you can define the new Sequence, like Sequence Name, Sequence schema, Data type, Precision, Start value, Increment by, etc. After entering all the required information, click OK to save it. The new Sequence will show up in Sequences node.
2. Create Sequence with T-SQL
   The following T-SQL script is used to create a new Sequenc 

3. CREATE SEQUENCE DemoSequence
   START WITH 1
   INCREMENT BY 1;

Use Sequence

The new NEXT VALUE FOR T-SQL keyword is used to get the next sequential number from a Sequence.

SELECT VALUE FOR DemoSequence

One thing I want to mention in here is Sequence doesn’t support transaction, if you run this script:

BEGIN TRAN
SELECT NEXT VALUE FOR dbo.DemoSequence
ROLLBACK TRAN

You can see even the transaction is rolled back at the end. The NEXT VALUE FOR will still return the next sequential number. This behavior is consistent with identity field.