新建测试表:
USE [gk4] GO /****** 对象: Table [dbo].[Table_1] 脚本日期: 07/18/2014 15:34:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE [dbo].[Table_1]( [refId] [int] IDENTITY(1,1) NOT NULL, [a] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [b] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [c] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [d] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [e] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [f] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [refId] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF
插入100W条数据:
declare @i int set @i=0 while @i<1000000 begin insert into gk4..table_1(a,b,c,d,e,f)values(@i,@i,@i,@i,@i,@i) set @i=@i+1 end
每次执行前清理数据库缓存:DBCC DROPCLEANBUFFERS
执行:
select * from gk4..table_1
select * from gk4..table_1 where 1=1
可以看出,对sqlserver而言,加1=1对查询效率没任何影响。
现在加下top,在比较下:
select top 1000000 * from gk4..table_1
加上top会有1%会占用1%的开销。
结论:
对sqlserver而言,有没有where 1=1对性能没影响,加上top会对性能开销有一定影响。