sql 2005 的新用法

BEGIN TRANSACTION
DECLARE @changes table (change varchar(2000))
UPDATE TOP (10) Person.Contact
SET FirstName = Reverse(FirstName)
OUTPUT 'Was: ''' + DELETED.FirstName +
''' Is: ''' + INSERTED.FirstName + ''''
INTO @changes
SELECT *
FROM @changes
ROLLBACK TRANSACTION
--note that local variable tables are not affected by transactions!

 

WITH salesSubset AS
(
SELECT product.name as product, sum(salesOrderDetail.lineTotal) as total
FROM sales.salesOrderDetail as salesOrderDetail
JOIN sales.salesOrderHeader as salesOrderHeader
ON salesOrderHeader.salesOrderId = salesOrderDetail.salesOrderId
JOIN production.product as product
ON product.productId = salesOrderDetail.productId
WHERE orderDate >= '1/1/2004' and orderDate < '1/1/2005'
GROUP BY product.name
)
SELECT product, total,
RANK() over (order by total desc) as 'RANK',
NTILE(4) over (order by total desc) as 'NTILE(4)'
FROM salesSubset

 

 

SQL Server 2000.
SELECT personId
FROM projectPerson as projLittle
WHERE projectId = 'projLittle'
AND NOT EXISTS ( SELECT *
FROM projectPerson as projBig
WHERE projBig.projectId = 'projBig'
and projBig.personId = projLittle.personId)
In 2005, you can run the following:
--worked on projBig but not projLittle
SELECT personId
FROM projectPerson
WHERE projectId = 'projLittle'
EXCEPT
SELECT personId
FROM projectPerson
WHERE projectId = 'projBig'
This returns the following: