DBAs & Developers using everyday UPDATE statement, and everytime before update they want to see what to update, after this they want to see what has been updated.

OUTPUT clause can give us new value & old value in one way. According to the T-SQL syntax the OUTPUT clause can be used before condition statement WHERE, so let's take a look with example:

--CREATE TEST TABLE
CREATE TABLE TEST
(
ID INT IDENTITY(1,1) NOT NULL,
FNAME NVARCHAR(30),
LNAME NVARCHAR(30),
LOCATION NVARCHAR(40),
CONSTRAINT pk_ID PRIMARY KEY (ID)
);

--ADDING SOME TEST DATA
INSERT INTO TEST
SELECT 'A', 'A', 'PRISHTINA'UNION ALL
SELECT 'B', 'B', 'PARIS'UNION ALL
SELECT 'C', 'C', 'FLORIDA'UNION ALL
SELECT 'D', 'D', 'LONDON'UNION ALL
SELECT 'E', 'E', 'BERLIN'UNION ALL
SELECT 'F', 'F', 'TIRANA'

Now we are going to use the Update statement and to see how we can retrieve results before and after:

--USING UPDATE
UPDATE TEST
SET FNAME = 'AAA'
OUTPUT INSERTED.FNAME AS NEW_VALUE, DELETED.FNAME AS OLD_VALUE
WHERE ID = 1

And the results will be:

NEW_VALUE    OLD_VALUE
 AAA                     A                   

As you can see we are available to see the two values, before and after, during update. OUTPUT clause can be used also during the INSERT & DELETE.

Let's clean the table from database:

DROP TABLE TEST;
GO

Have a nice SQLing...!