提高SQL Server应用程序的效率

1、当检查记录是否存在时，使用EXISTS代替COUNT(*)

--不用

IF (SELECT COUNT(*) FROM Orders
   WHERE ShipVia = 3) > 0
PRINT 'You cannot delete this shipper'

 

 

2、谨慎使用WHERE IN 和 WHERE NOT IN

--不用

SELECT *
   FROM Customers
   WHERE CustomerID NOT IN
   (SELECT CustomerID FROM Orders)

--推介

SELECT c.*
   FROM Customers c
   LEFT OUTER JOIN Orders o
   ON o.CustomerID = c.CustomerID
   WHERE o.CustomerID IS NULL 

3、NewID()来产生随机记录集

 SELECT * FROM Products
ORDER BY NEWID()

SELECT TOP 1 * FROM Products
ORDER BY NEWID()

 --推介

IF EXISTS (SELECT * FROM Orders
   WHERE ShipVia = 3)
PRINT 'You cannot delete this shipper'