SQL数据库中查找一条记录是否存在 count（推荐）

来源：互联网发布：简便算法公式编辑：程序博客网时间：2024/05/27 14:12

最快方法：SELECT top 1 @count=1,同时item_urlkey字段做了聚集索引或非聚集索引

如果语句执行时，不走索引的话，也可以用with(index=IX_t2_col1)指定索引，例如：select *from t2 with(index=IX_t2_col1)where col1=201

GOdeclare @num int declare @beforDateTime datetime declare @count int set @num=0;set @beforDateTime=GETDATE()while (@num<100000)beginset @count=0SELECT top 1 @count=1 FROM [iWOMTrackData].[dbo].[SEC_Item] WHERE [Item_URLKey]='e6d71e8d1c410cd84317897be0d0b5b6'set @num=@num+1endprint datediff(MILLISECOND,@beforDateTime,GETDATE())

其他方法1：SELECT top 1 @count=COUNT(主键)

/****** Script for SelectTopNRows command from SSMS  ******/GOdeclare @num int declare @beforDateTime datetime declare @count int set @num=0;set @beforDateTime=GETDATE()while (@num<100000)beginset @count=0SELECT top 1 @count=COUNT(item_id) FROM [iWOMTrackData].[dbo].[SEC_Item] WHERE [Item_URLKey]='e6d71e8d1c410cd84317897be0d0b5b6'set @num=@num+1endprint datediff(MILLISECOND,@beforDateTime,GETDATE())

其他方法2：SELECT top 1 @count=COUNT(*)

/****** Script for SelectTopNRows command from SSMS  ******/GOdeclare @num int declare @beforDateTime datetime declare @count int set @num=0;set @beforDateTime=GETDATE()while (@num<100000)beginset @count=0SELECT top 1 @count=COUNT(*) FROM [iWOMTrackData].[dbo].[SEC_Item] WHERE [Item_URLKey]='e6d71e8d1c410cd84317897be0d0b5b6'set @num=@num+1endprint datediff(MILLISECOND,@beforDateTime,GETDATE())

其他方法3：SELECT top 1 @count=COUNT(1)

/****** Script for SelectTopNRows command from SSMS  ******/GOdeclare @num int declare @beforDateTime datetime declare @count int set @num=0;set @beforDateTime=GETDATE()while (@num<100000)beginset @count=0SELECT top 1 @count=COUNT(1) FROM [iWOMTrackData].[dbo].[SEC_Item] WHERE [Item_URLKey]='e6d71e8d1c410cd84317897be0d0b5b6'set @num=@num+1endprint datediff(MILLISECOND,@beforDateTime,GETDATE())

其中select top 1 1 形式是最快的，其他三种方法的速度都差不多。