--测试环境,创建100W的数据 SET NOCOUNT ON; USE tempdb; GO IF OBJECT_ID('dbo.Nums') IS NOT NULL DROP TABLE dbo.Nums; GO CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY); DECLARE @max AS INT, @rc AS INT; SET @max = 1000000; SET @rc = 1;INSERT INTO Nums VALUES(1); WHILE @rc * 2 <= @max BEGIN INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums; SET @rc = @rc * 2; ENDINSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;set statistics time onselect * from Nums where n = 10000select * from Nums where n like '10000' /* --结果 SQL Server 执行时间: CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。(1 行受影响)SQL Server 执行时间: CPU 时间 = 516 毫秒,占用时间 = 517 毫秒。 */
结果一样的;
效率应该是 where name=1 快..
name 上建立索引
SET NOCOUNT ON;
USE tempdb;
GO
IF OBJECT_ID('dbo.Nums') IS NOT NULL
DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
SET @rc = @rc * 2;
ENDINSERT INTO dbo.Nums
SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;set statistics time onselect * from Nums
where n = 10000select * from Nums
where n like '10000'
/*
--结果
SQL Server 执行时间:
CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。(1 行受影响)SQL Server 执行时间:
CPU 时间 = 516 毫秒,占用时间 = 517 毫秒。
*/