Output and Output Into clause in SQL Server 2005

SQL Server 2005 introduced new OUTPUT and OUTPUT INTO Clause for showing the result after the Insert/Update/Delete statement in single go. Earlier (SQL Server 2000), we are using two T-SQL statement for getting result after Insert/Update/Delete statement.
For example we have a table “tbl_Employee” and it has three columns (ID, Name, Salary).
Create table tbl_Employee
(
EmpID Int,
EmpName varchar(50),
Salary Decimal(10,2)
)
Example
In SQL Server 2000, first we execute Insert/Update/Delete statement, and then we execute the select statement to see the result.
Example with Insert Statement.
Insert Into tbl_Employee (EmpID, EmpName, Salary)
Values (1, ‘AAA’, 5000)
Go
Select * from tbl_Employee
Go
The same thing we do with UPDATE and DELETE statement in SQL Server 2000. First we update or Delete the record and then see the output (result).
But in SQL Server 2005, there is an OUTPUT clause for providing output (result) after performing Insert/Update/Delete action in single step (in single query).
OUTPUT Clause with INSERT Statement
Insert into tbl_Employee (EmpID, EmpName, Salary)
OUTPUT Inserted.*
Values (1, 'AAA', 3000)
Go
OUTPUT INTO Clause with INSERT Statement
If you want to insert the output (result) in different table, use OUTPUT INTO clause with other table name. We have another table named tbl_EmployeeLog with same column name and data type of tbl_Employee table.

Insert into tbl_Employee (EmpID, EmpName, Salary)
OUTPUT Inserted.* INTO tbl_EmployeeLog
Values (2, 'BBB', 3000)
Go

If you see the record of tbl_EmployeeLog table, you will get following record.







But don’t start to think that you would use OUTPUT INTO clause for maintaining log. This is good option for inserting output in another table, but for maintaining the log, TRIGGER is the best option. Always use TRIGGER wherever you need to maintain the log of any (Insert/Update/Delete) action.

Varchar(MAX) Datatype and its limitation

In SQL Server 2000 and SQL Server 7, a row cannot exceed 8000 bytes in size. This means that a VARBINARY column can only store 8000 bytes (assuming it is the only column in a table), a VARCHAR column can store up to 8000 characters and an NVARCHAR column can store up to 4000 characters (2 bytes per unicode character). This limitation stems from the 8 KB internal page size SQL Server uses to save data to disk.

To store more data in a single column, you needed to use the TEXT, NTEXT, or IMAGE data types (BLOBs) which are stored in a collection of 8 KB data pages that are separate from the data pages that store the other data in the same table. These data pages are arranged in a B-tree structure. (Binary Large Objects) BLOBs are hard to work with and manipulate. They cannot be used as variables in a procedure or a function and they cannot be used inside string functions such as REPLACE, CHARINDEX or SUBSTRING. In most cases, you have to use READTEXT, WRITETEXT, and UPDATETEXT commands to manipulate BLOBs.

To solve this problem, Microsoft introduced the VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) data types in SQL Server 2005. These data types can hold the same amount of data BLOBs can hold (2 GB) and they are stored in the same type of data pages used for other data types. When data in a MAX data type exceeds 8 KB, an over-flow page is used. SQL Server 2005 automatically assigns an over-flow indicator to the page and knows how to manipulate data rows the same way it manipulates other data types. You can declare variables of MAX data types inside a stored procedure or function and even pass them as variables. You can also use them inside string functions.

Microsoft recommends using MAX data types instead of BLOBs in SQL Server 2005.
Varchar(Max) Data type can hold more than 8000 characters. But it doesn’t means, we can specify varchar(8001), varchar(9000) or something like this in a table’s field.
If we try to create table’s field with VARCHAR(9000) it gives following error.
Create table tbl_Test
(
Col varchar(9000)
)
Msg 131, Level 15, State 2, Line 3
The size (9000) given to the column 'Col' exceeds the maximum allowed for any data type (8000).


Limitation of varchar(Max)
----------------------------------------------------------
1) If a variable hold more than 8000 character. But in print option it display only 8000 character. We should use select statement instead of print.
2) If you add a literal value in a variable of varchar(max) datatype without casting. It will only store 8000 character. See the below example.

Declare @tst Varchar(max)
Set @tst = space(7998) + 'test'
Select len(@tst)

Output
---------
8000

But when you add literal value after casting, it can hold more than 8000 characters. See the below example.

Set @tst = space(7998) + CAST('test' as varchar(max))
Select len(@tst)
Output
--------
8002