Select Top N Number of Records for an ID or Grouping

How do I select the top 'N' records from a table by some sort of grouping?

This example selects the top 5 invoice records for each customer by creating a grouping on the Customer table's primary key: CustID

SELECT *
FROM dbo.Invoice AS i
WHERE InvoiceID IN
(
SELECT TOP 5 InvoiceID
FROM dbo.Invoice AS i2
WHERE i.CustID = i2.CustID
ORDER BY i.PostDate DESC
)
ORDER BY i.CustID

You can also change this to any type of grouping: Category of customer, amount billed, highest totals by region... whatever you want.

TSQL INSERT, UPDATE, DELETE with the OUTPUT clause

A new feature with SQL Server 2005 is the ability to add an OUTPUT to an INSERT, UPDATE or DELETE statement.

The OUTPUT clause allows you to retrieve data affected by the INSERT, UPDATE, or DELETE statement.

The resulting output can be selected, inserted into a table, or inserted into a table variable, making it ideal for all sorts of uses. The OUTPUT Clause (Transact-SQL) article in SQL Server 2005 Books Online (September 2007) defines it as useful for "such things as confirmation messages, archiving, and other such application requirements. "

For an INSERT, you can specify any of the values being included in the INSERT statement, such as in this example from SQL Server 2005 Books Online (September 2007):

USE AdventureWorks;
GO
DECLARE @MyTableVar table( ScrapReasonID smallint,
Name varchar(50),
ModifiedDate datetime);
INSERT Production.ScrapReason
OUTPUT INSERTED.ScrapReasonID, INSERTED.Name, INSERTED.ModifiedDate INTO @MyTableVar
VALUES (N'Operator error', GETDATE());

For an UPDATE you can include INSERTED or DELETED and values from_table_name (more explained below), and for DELETE, you can include DELETED values and values from_table_name.

from_table_name refers to a table used in the FROM clause in determining the rows for UPDATE, DELETE.

A delete example from SQL Server 2005 Books Online (September 2007) (note the reference to Production.Product table in the OUTPUT clause):

USE AdventureWorks
GO
DECLARE @MyTableVar table (
ProductID int NOT NULL,
ProductName nvarchar(50)NOT NULL,
ProductModelID int NOT NULL,
PhotoID int NOT NULL);

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;

This forum post explains the reason why values from_table_name cannot be used for an INSERT statement, and the explanation makes sense:
"The UPDATE and DELETE however has the non-standard TSQL specific FROM clause as part of the DML statement itself so you can reference those tables in the OUTPUT clause."

This article also offers some additional information on the subject: http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic12325.aspx

Finding missing sequence values in sql server

Recently while troubleshooting a problem, I had the need to identify missing rows from a table with an identity column.

There is always the simple way of joining to a lookup table with all of the possible values, and then search where the value appears in the lookup table, and not in your source table.

However, a quick google found this blog post, that has some great examples, and a fairly complete analysis of the different alternatives.

How to find missing values in a sequence with SQL