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() gives the temporary sequential number of a row when the query is run.


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.


select ROW_NUMBER() over(PARTITION BY BOOKTITLE Order By BookId) as SN ,BookId,BookTitle from Book

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



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.

[ OUTPUT INTO { @table_variable | output_table } [ ( column_list ) ] ]
{ | scalar_expression } [ [AS] column_alias_identifier ]     [ ,...n ]
{ DELETED | INSERTED | from_table_name } . { * | column_name }
| $action


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


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 columns in 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

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


 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.