删除数据

USE tempdb;
GO

-- 准备测试数据
IF OBJECT_ID(‘dbo.Orders‘,‘U‘) IS NOT NULL DROP TABLE dbo.Orders;
IF OBJECT_ID(‘dbo.Customers‘,‘U‘) IS NOT NULL DROP TABLE dbo.Customers;
GO

SELECT * INTO dbo.Orders FROM TSQLFundamentals2008.Sales.Orders;
SELECT * INTO dbo.Customers FROM TSQLFundamentals2008.Sales.Customers;

ALTER TABLE dbo.Customers ADD
CONSTRAINT PK_Customers PRIMARY KEY(custid);

ALTER TABLE dbo.Orders ADD
CONSTRAINT PK_Orders PRIMARY KEY(orderid),
CONSTRAINT FK_Orders_Customers FOREIGN KEY(custid) REFERENCES dbo.Customers(custid);

-- 删除数据
-- 方法一：DELETE(完整模式日志,性能低)
DELETE FROM dbo.Orders
WHERE orderdate<‘20070101‘;

-- 基于联接的DELETE
DELETE FROM orders 
FROM dbo.Orders AS orders
LEFT JOIN dbo.Customers AS customers ON orders.custid= customers.custid
WHERE customers.country= N‘USA‘;

-- 方法三：TRUNCATE(最小模式日志,性能高；重置标识种子)
TRUNCATE TABLE dbo.Orders;