GUID和自增ID的比较_top

有5张表

test是原始表,表结构如下

SELECT TOP 10 * FROM test

其他四张表都是复制test表

现做如下字段添加修改

--test_id添加自增ID

ALTER TABLE dbo.test_id ADD

ID int NOT NULL IDENTITY (1, 1)

SELECT TOP 10 * FROM test_id

--test_guid添加guidid,默认值newid

ALTER TABLE dbo.test_guid ADD

guidid [uniqueidentifier] NOT NULL DEFAULT (newid())

SELECT TOP 10 * FROM test_guid

--test_id_guid添加自增ID

ALTER TABLE dbo.test_id_guid ADD

ID int NOT NULL IDENTITY (1, 1)

--test_id_guid添加guidid,默认值newid

ALTER TABLE dbo.test_id_guid ADD

guidid [uniqueidentifier] NOT NULL DEFAULT (newid())

SELECT TOP 10 * FROM test_id_guid

--test_id_guid1添加自增ID

ALTER TABLE dbo.test_id_guid1 ADD

ID int NOT NULL IDENTITY (1, 1)

--test_id_guid1添加guidid,默认值newid

ALTER TABLE dbo.test_id_guid1 ADD

guidid [uniqueidentifier] NOT NULL DEFAULT (newid())

--test_id_guid1添加guidid1,默认值newid

ALTER TABLE dbo.test_id_guid1 ADD

guidid1 [uniqueidentifier] NOT NULL DEFAULT (newid())

SELECT TOP 10 * FROM test_id_guid1

------------------------------------------------------------------------------------------------

以上表都没有索引

------------------------------------------------------------------------------------------------

a.top 查询

------------------------------------------------------------------------------------------------

--清空缓存

DBCC FREEPROCCACHE WITH NO_INFOMSGS

DBCC FREESESSIONCACHE WITH NO_INFOMSGS

DBCC FREESYSTEMCACHE('All') WITH NO_INFOMSGS

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

SET NOCOUNT on

DECLARE @top INT

SET @top=1

CREATE TABLE #result

(

状态 varchar(20),

[test(毫秒)] INT,

[test_id(毫秒)] INT,

[test_guid(毫秒)] INT,

[test_id_guid(毫秒)] INT,

[test_id_guid1(毫秒)] INT

)

DECLARE @start INT

DECLARE @end INT

SET @start=1

SET @end=20

WHILE (@start<=@end)

BEGIN --循环开始

DBCC FREEPROCCACHE

DBCC FREESESSIONCACHE

DBCC FREESYSTEMCACHE('All')

DBCC DROPCLEANBUFFERS

declare @d datetime

set @d = getdate()

SELECT TOP (@top)[费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test

declare @result int

select @result = datediff(ms, @d, getdate())

declare @d1 datetime

set @d1 = getdate()

SELECT TOP (@top)[费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id

declare @result1 int

select @result1 = datediff(ms, @d1, getdate())

declare @d2 datetime

set @d2 = getdate()

SELECT TOP (@top)[费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_guid

declare @result2 int

select @result2 = datediff(ms, @d2, getdate())

declare @d3 datetime

set @d3 = getdate()

SELECT TOP (@top)[费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id_guid

declare @result3 int

select @result3 = datediff(ms, @d3, getdate())

declare @d4 datetime

set @d4 = getdate()

SELECT TOP (@top)[费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id_guid1

declare @result4 int

select @result4 = datediff(ms, @d4, getdate())

INSERT INTO #result

SELECT '正常',

@result AS 'test(毫秒)',

@result1 'test_id(毫秒)',

@result2 'test_guid(毫秒)',

@result3 'test_id_guid(毫秒)',

@result4 'test_id_guid1(毫秒)'

SET @start=@start+1

END --循环结束

SELECT * FROM #result

UNION ALL

SELECT '最小值',

min([test(毫秒)]),

min([test_id(毫秒)]),

min([test_guid(毫秒)]),

min([test_id_guid(毫秒)]),

min([test_id_guid1(毫秒)])

FROM #result

UNION ALL

SELECT '平均值',

AVG([test(毫秒)]),

AVG([test_id(毫秒)]),

AVG([test_guid(毫秒)]),

AVG([test_id_guid(毫秒)]),

AVG([test_id_guid1(毫秒)])

FROM #result

UNION ALL

SELECT '最大值',

max([test(毫秒)]),

max([test_id(毫秒)]),

max([test_guid(毫秒)]),

max([test_id_guid(毫秒)]),

max([test_id_guid1(毫秒)])

FROM #result

--DROP TABLE #result

------------------------------------------------------------------------------------------------

1.@TOP=1,@end=20

2.@TOP=1,@end=100

SELECT COUNT(*) FROM #result WHERE [test_id(毫秒)]>[test_guid(毫秒)]

SELECT COUNT(*) FROM #result WHERE [test_id(毫秒)]>[test_id_guid(毫秒)]

SELECT COUNT(*) FROM #result WHERE [test_id(毫秒)]>[test_id_guid1(毫秒)]

SELECT COUNT(*) FROM #result WHERE [test_id(毫秒)]>[test_guid(毫秒)]

3.@TOP=10000,@end=50

3.@TOP=50000,@end=10

4.@TOP=2000,@end=50

--添加索引

CREATE INDEX test1 ON test_id(id)

CREATE INDEX test2 ON test_guid(guidid)

CREATE INDEX test3 ON test_id_guid(id)

CREATE INDEX test4 ON test_id_guid(guidid)

CREATE INDEX test5 ON test_id_guid1(id)

CREATE INDEX test6 ON test_id_guid1(guidid)

CREATE INDEX test7 ON test_id_guid1(guidid1)

1.@top=1