Temporary Tables and Table Variables

coming soon.

Magic Tables in SQL Server

Magic tables are used to put all the deleted and updated rows. We can retrieve the column values from the deleted rows using the keyword “deleted”

To project the deleted data into the deleted table we will use “output” clause.  Below is the sample code to retrieve the deleted data.

DECLARE @DeletedTable TABLE(DeletedTableID INT, DeletedData VARCHAR(20))  

DELETE MyTable
OUTPUT Deleted.KeyID, Deleted.Name INTO @DeletedTable WHERE KeyID > 3
SELECT * FROM @DeletedTable

 

Similarly, we can retrieve the updated and inserted data too using the keyword “Inserted”

DECLARE @InsertedAccounts(AccountName VARCHAR(100), AccountId INT)

INSERT Accounts(AccountName,AccountTypeId)
OUTPUT Inserted.AccountName, Inserted.AccountId INTO @InsertedAccounts
SELECT AccountName, 1 FROM #AccountData

Similarly for update :

DECLARE @AcctTypes table(AccountTypeId INt, AccountType VARCHAR(100))

UPDATE AccountTypes
SET AccountType = ‘Testw123’
OUTPUT Inserted.AccountTypeId,Inserted.AccountType into @AcctTypes
WHERE AccountTypeId > 1
SELECT  * FROM @AcctTypes

Useful Links

Indexed views in SQL server 2005:

It is possible to create a unique clustered index on a view, as well as nonclustered indexes, to improve data access performance on the most complex queries. In SQL Server 2000 and 2005, a view that has a unique clustered index is referred to as an indexed view.

LEN and DATALENGTH in SQL Server

The differences between LEN and DATALENGTH in SQL Server!
I have seen a bunch of questions about this recently and decided to do a little post to clear up the confusion.
First let’s take a look what Books On Line has to say about these two functions

LEN
Returns the number of characters, rather than the number of bytes, of the given string expression, excluding trailing blanks.

DATALENGTH
Returns the number of bytes used to represent any expression.

So what does that mean? It means that the LEN function will first right trim the value and then give you a count of the charaters, the DATALENGTH function on the other hand does not right trim the value and gives you the storage space required for the characters.

1. DECLARE @v NCHAR(5)
2. SELECT @v =’ABC  ‘
3. SELECT LEN(@v),datalength(@v)

The output for len is 3 while the output for datalength =10. The reason that datalength returns the value 10 is because nvarchar uses 2 bytes to store 1 character by using unicode while varchar is using ascii which requires 1 byte per charaters.