Howdy !!

This post elucidates the basic functionality,syntax and usage of two of the Sql Server concepts  ROW_NUMBER function and OUTPUT Clause. 

Row_Number()
Row_Number() gives the temporary sequential number of a row when the query is run.
Syntax
Row_Number() OVER([PARTITION BY [col1]…[coln]|[value expression]] ORDER BY [Col1]….[Coln])
PARTITION BY clause is optional. If it is specified then Row_Number() will return the sequential number within the partitions of a result set starting at 1 for the first row in each partition.
Example:
select ROW_NUMBER() over(PARTITION BY BOOKTITLE Order By BookId) as SN ,BookId,BookTitle from Book

rowpartition
select ROW_NUMBER Over(Order By BookId) as SN,BookId,BookTitle from Book

rownum

OUTPUT 
The OUTPUT clause returns a copy of the data that you’ve inserted into or deleted from your tables. You can return that data to a table variable, a temporary or permanent table, or to the processing application that’s calls the DML statement. You can then use that data for such purposes as archiving, confirmation messaging, or other application requirements.
Syntax
[ OUTPUT INTO { @table_variable | output_table } [ ( column_list ) ] ]
[ OUTPUT ]
::=
{ | scalar_expression } [ [AS] column_alias_identifier ]     [ ,...n ]
::=
{ DELETED | INSERTED | from_table_name } . { * | column_name }
| $action

Example:
Output clause in Insert statement to send the result of inserted row to a  Table variable.
declare @tbook table(
BookId int not null,
BookTitle nvarchar(50),
CreatedDate DateTime);
insert into Book (BookId,BookTitle,CreatedDate) output  inserted.* into @tbook  VALUES (60,'Wings of Fire',GetDate())
select *from @tbook

Note that we can even have different names for table variable columns but the order should match.
declare @tbook table(
BId int not null,
BTitle nvarchar(50),
CreatedDate DateTime);
insert into Book (BookId,BookTitle,CreatedDate)output inserted.*into @tbook
VALUES(65,'A Serreptious plot',Current_Timestamp)
select * from @tbook

tempvariable
Note: Select and execute the entire script at once since the table variable has scope of a batch.If we execute it command by command then we can’t see the expected output since table variable will be out of scope.

If the order of tables doesn’t match then we have to explicity specify the columns of the table variable in output clause as
declare @tbook table(BTitle nvarchar(50),BId int not null,CreatedDate DateTime);
insert into Book(BookTitle,BookId,CreatedDate)output inserted.*into @tbook(BTitle,BId,CreatedDate) VALUES('A Serreptious plot',65,Current_Timestamp)
select * from @tbook

Output Clause in Update statement to send result to an Application.
Now that you understood lets look at the queries directly.

update Book SET BookTitle='A Conscientious Girl' output inserted.BookTitle as NewCol ,deleted.BookTitle as oldCol where BookId=61
Output Clause in Delete Statement.
delete from Book output deleted.BookId,deleted.BookTitle where BookId=51
Output clause to send result to a  temporary table.
CREATE Table #TeTABLE(BookId int,BookTitle nvarchar(50),CreatedDate DateTime);
delete from Book output deleted.* into #TEMPTABLE where BookId=51
select *from #TEMPTABLE

temptable
Output clause to send the result to a permanent table.
update Book set BookTitle='Knightingale' output inserted.BookTitle,deleted.BookTitle into PrmntTable where BookId='61'
select * from PrmntTable

prmnttable

 This is for today’s post.Hope you dived into and enticed to learn it today.Please feel free to any edits or suggestions as I love to hear from you.

Advertisements