Sql Server Hidden Features - Part 2

A lot of SQL Server developers still don't seem to know about the OUTPUT clause (SQL Server 2005 and newer) on the DELETE, INSERT and UPDATE statement.
It can be extremely useful to know which rows have been INSERTed, UPDATEd, or DELETEd, and the OUTPUT clause allows to do this very easily -

it allows access to the "virtual" tables called inserted and deleted (like in triggers):

 

DELETE FROM (table)OUTPUT deleted.ID, deleted.DescriptionWHERE (condition)

 

If you're inserting values into a table which has an INT IDENTITY primary key field, with the OUTPUT clause, you can get the inserted new ID right away:

INSERT INTO MyTable(Field1, Field2)OUTPUT inserted.IDVALUES (Value1, Value2)

 

And if you're updating, it can be extremely useful to know what changed - in this case, inserted represents the new values (after the UPDATE), while deleted refers to the old values before the UPDATE:

 

UPDATE (table)SET field1 = value1, field2 = value2OUTPUT inserted.ID, deleted.field1, inserted.field1WHERE (condition)

If a lot of info will be returned, the output of OUTPUT can also be redirected to a temporary table or a table variable (OUTPUT INTO @myInfoTable).

 

More tips

• Use ctrl-0 to insert a null value in a cell
• WITH (FORCESEEK) which forces the query optimizer to use only an index seek operation as the access path to the data in the table.
  http://msdn.microsoft.com/en-us/library/bb510478.aspx
• PIVOT and UNPIVOT
• Save transactions
• Select xact_state()  --Returns whether transaction pending/committed
• xp_fixeddrives       --Returns the free disk space in the server
• SELECT ASCII('A'),CHAR(65),CHAR(ASCII('A')),UNICODE(N'?'),NCHAR(923),NCHAR(UNICODE(N'?')) --Some Ascii/unicode features
• select * from tablename tablesample(2 percent) --Returns the 2% of the records in the db.Alt select top 10 percent * from table
• select cast(GETDATE()as varchar) as datetime --Returns the date time as formatted.
• WITH TIES
  The SELECT TOP N query always return exactly N records, and arbitrarily drops any record that have the same value as the last record in the group
• select top 5 with ties * from scores order by name desc
• SELECT Column FROM Table ORDER BY CHECKSUM(NEWID()) -- Return rows in a random order

• master..xp_cmdshell to run commands from a command prompt on the server
  master..xp_cmdshell 'dir d:\'   --List the Directory
  master..xp_cmdshell 'sc query "ccm6.5_ess"' --Query a service
  master..xp_cmdshell 'sc start "ccm6.5_ess"' --Start a service