sql最佳实践总结

1：为了遵循最佳实践，在子查询返回的结果中应该排除Null值。

2：in是三值谓词逻辑而exists是二值谓词逻辑（true，false）觉不会有Unknown.exists会自动把null过滤掉。因此使用Not exists比使用not in更安全。

-- Exclude NULLs
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE custid NOT IN(SELECT O.custid
FROM Sales.Orders AS O
WHERE O.custid IS NOT NULL);

-- Using NOT EXISTS
SELECT custid, companyname
FROM Sales.Customers AS C
WHERE NOT EXISTS
(SELECT *
FROM Sales.Orders AS O
WHERE O.custid = C.custid);

-- Cleanup
DELETE FROM Sales.Orders WHERE custid IS NULL;
DBCC CHECKIDENT('Sales.Orders', RESEED, 11077);

3:在创建视图时指定SCHEMABINDING选项这样可以防止视图引用的表删除等。

ALTER VIEW Sales.USACusts WITH SCHEMABINDING
AS

SELECT
custid, companyname, contactname, contacttitle, address,
city, region, postalcode, country, phone, fax
FROM Sales.Customers
WHERE country = N'USA';
GO