Use of EXCEPT operator and comparing tables

EXCEPT operator exists in SQL Server 2005 and higher. It is particularly useful when one needs to compare two tables of identical structure and to select those records from the first table that do not exist in the second one.

SELECT column1, column2, column3 FROM Table1
EXCEPT
SELECT column1, column2, column3 FROM Table2

or even

SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2

Some interesting features I found in SQL Server 2005 that did not exist in SQL Server

TOP clause

1. Ability to pass a variable to the TOP clause
   

   
   DECLARE @Rows INT
   SET @Rows = 10
   
   SELECT TOP ( @Rows ) *
   FROM dbo.MyTable

   
   
   @Rows can be replaced with anything that evaluates to a number
   
   
2. TOP clause can be used with INSERT, UPDATE and DELETE statements. For example if you want to delete rows in batches
   
   of 50, you can do that using the TOP clause.
   
   

CROSS APPLY


CROSS APPLY can be used to combine data from a table with table data returned by user-defined function. In SQL Server

2000 there existed functions that return table but there was no CROSS APPLY functionality

CREATE FUNCTION dbo.fn_GetTopTableRecords(@id AS int, @count AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP(@count) *
  FROM dbo.MyTable
  WHERE ID = @id
  ORDER BY Field1 DESC
GO

Then one can just use CROSS APPLY to combine data returned by the function with a data from some other table

SELECT  t1.ID, 
 t2.ID,
 t2.Field1
FROM 
 dbo.Table1 as t1
CROSS APPLY
 dbo.fn_GetTopTableRecords(ID, 5) AS t2
ORDER BY 
 t1.ID ASC, t2.Field1 DESC

The APPLY clause is similar to a CROSS JOIN or an OUTER JOIN without the ON clause. APPLY clause can also be preceded by

either CROSS or OUTER keyword. The OUTER APPLY clause will return all the rows on the left side whether they return any

rows from the function or not. The CROSS APPLY only returns rows from the left side if the function returns rows.