Few SQL Server Concepts And Queries
This Article describes about few Few Sql Server Concepts like SQL Transaction like COMMIT TRANSACTION,ROLLBACK,SAVEPOINT,@@TRANCOUNT,etc. AS well as Exception And Error Handling in SQL Transactions, CHUNK in SQL Server.
OUTPUT CLAUSE IN SQL SERVER:
The "OUTPUT" Clause is used in Sql server DML (INSERT, UPDATE, DELETE) statements. The clause returns a copy of the data that we have inserted/updated into or deleted from our tables. The Output (Result Set) data can be inserted into a Table Variable, Temporary table or Permanent table as well.
These Data values can be used for Achieving, confirmation messaging, or other application requirements on a later stage.
History:
The output Clause was first introduced in SQL Server 2005 to be used in INSERT, UPDATE, or DELETE statements. But SQL Server 2008 latter improvised it to be used in MERGE statements as well.
Below are the various syntax statements:
For Example, I have a Table called Files with FileId, FileType And UpdatedDate AS Columns for the table.
Output Clause Syntax For Insertion:
INSERT INTO dbo.Files
OUTPUT INSERTED.*
INTO @tblFiles
VALUES (28, 'XML', GETDATE());
OR
INSERT INTO dbo.Files
OUTPUT INSERTED.Id INTO @tblFiles(FileId)
SELECT LIVE.*
FROM MainFiles LIVE WITH (NOLOCK)
WHERE UpdateDate <= @CUTOffDate
Output Clause Syntax For Updating:
UPDATE Files
SET
FileId = 105,
UpdatedDate = GETDATE()
OUTPUT
DELETED. FileId,
INSERTED. FileId,
INSERTED. FileType,
DELETED. UpdatedDate,
INSERTED. UpdatedDate
INTO @tblFiles
WHERE UpdateDate <= @CUTOffDate;
Output Clause Syntax For Deletion:
DELETE dbo.Files
OUTPUT DELETED.Id,DELETED.Type,DELETED.LatestDate
WHERE UpdateDate <= @CUTOffDate
OR
DECLARE @CUTOffDate DateTime, @chunk_size BIGINT
SET @CUTOffDate = DATEADD(M, -6, GETDATE())
SET @chunk_size=1000
INSERT INTO dbo.Files (FileId,FileType,UpdatedDate)
SELECT (Id,Type,LatestDate)
FROM (
DELETE TOP(@chunk_size) dbo.FilesMaster
OUTPUT DELETED.Id,DELETED.Type,DELETED.LatestDate
WHERE UpdateDate <= @CUTOffDate) AS RowsToMove
NOTE: CHUNK is another concept in SQL Server where we can modify the data in batches.We can specify the ChunkSize to Modify/Delete data from a table then we can form a loop to increase the ChunkSize.Below is an example to descibe the Concept of chunk for better understanding.
DECLARE @CUTOffDate DateTime, @chunk_size BIGINT,@Count INT,@TotalCount BIGINT
SELECT @TotalCount=COUNT(FileId) FROM dbo.Files WITH (NOLOCK) WHERE UpdateDate <= @CUTOffDate
BEGIN TRY
BEGIN TRAN
WHILE (@Count<@TotalCount)
BEGIN
INSERT INTO dbo.Files
OUTPUT INSERTED.Id INTO @tblFiles(FileId)
SELECT LIVE.*
FROM MainFiles LIVE WITH (NOLOCK)
WHERE UpdateDate <= @CUTOffDate
SET @TranCount=@@ROWCOUNT
IF @TranCount > 0
BEGIN
INSERT INTO DBO.tblArchievalLog
(LogDateTime, TableName, [Action], [Rowcount], TranStatus, [Description])
VALUES (@CurrentDateTime, Files , 'INSERT', @TranCount, 1, NULL)
END
COMMIT TRAN
SELECT @Count = @Count + @ChunkSize
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT >0
ROLLBACK TRAN
INSERT INTO DBO.tblArchievalLog
(LogDateTime, TableName, [Action], [Rowcount], TranStatus, [Description])
VALUES (@CurrentDateTime, @TableName, 'ERROR', 0, 0, ERROR_MESSAGE())
END CATCH
The Above Example Is a Very good case to Explain the SQL SERVER TRANSACTIONS AND ERROR HANDLING.
SQL SERVER TRANSACTIONS AND ERROR HANDLING:
A transaction is a unit of work that is performed against a database in a logical order. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure.
There are following commands used to control transactions:
• Begin Transaction
• Rollback Transaction
• Commit Transaction
COMMIT: The COMMIT command is the transactional command used to save changes invoked by a transaction to the database.
ROLLBACK: The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database. This means it can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.
SAVEPOINT: It creates points within groups of transactions in which to ROLLBACK.A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.
The syntax for SAVEPOINT command is as follows:
SAVEPOINT SAVEPOINT_NAME;
The ROLLBACK command is used to undo a group of transactions.
ROLLBACK TO SAVEPOINT_NAME;
@@TRANCOUNT --- It is a global variable that increases automatic variable count from 0 to 1 to specify that the transaction is successful. When you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0.And if the Transaction fails it also returns 0.
We can include TRY….CATCH Block for Error Handling as we do in normal coding. You can understand better from the above example.