GUID和自增ID的比较

GUID和自增ID的比较_select

1.取最后一条数据

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

--清空缓存

DBCC FREEPROCCACHE WITH NO_INFOMSGS

DBCC FREESESSIONCACHE WITH NO_INFOMSGS

DBCC FREESYSTEMCACHE('All') WITH NO_INFOMSGS

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

SET NOCOUNT on

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=10

WHILE (@start<=@end)

BEGIN --循环开始

DBCC FREEPROCCACHE

DBCC FREESESSIONCACHE

DBCC FREESYSTEMCACHE('All')

DBCC DROPCLEANBUFFERS

declare @d datetime

set @d = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test

WHERE 费用_ID=4406668

declare @result int

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

declare @d1 datetime

set @d1 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_ID

WHERE 费用_ID=4406668

declare @result1 int

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

declare @d2 datetime

set @d2 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_guid

WHERE 费用_ID=4406668

declare @result2 int

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

declare @d3 datetime

set @d3 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id_guid

WHERE 费用_ID=4406668

declare @result3 int

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

declare @d4 datetime

set @d4 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id_guid1

WHERE 费用_ID=4406668

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

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

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(毫秒)]

2.between and范围

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

--清空缓存

DBCC FREEPROCCACHE WITH NO_INFOMSGS

DBCC FREESESSIONCACHE WITH NO_INFOMSGS

DBCC FREESYSTEMCACHE('All') WITH NO_INFOMSGS

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

SET NOCOUNT on

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=10

WHILE (@start<=@end)

BEGIN --循环开始

DBCC FREEPROCCACHE

DBCC FREESESSIONCACHE

DBCC FREESYSTEMCACHE('All')

DBCC DROPCLEANBUFFERS

declare @d datetime

set @d = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test

WHERE 费用_ID between 4406668 and 4406768

declare @result int

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

declare @d1 datetime

set @d1 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_ID

WHERE 费用_ID between 4406668 and 4406768

declare @result1 int

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

declare @d2 datetime

set @d2 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_guid

WHERE 费用_ID between 4406668 and 4406768

declare @result2 int

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

declare @d3 datetime

set @d3 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id_guid

WHERE 费用_ID between 4406668 and 4406768

declare @result3 int

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

declare @d4 datetime

set @d4 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id_guid1

WHERE 费用_ID between 4406668 and 4406768

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

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

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(毫秒)]

--添加索引

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)

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

--清空缓存

DBCC FREEPROCCACHE WITH NO_INFOMSGS

DBCC FREESESSIONCACHE WITH NO_INFOMSGS

DBCC FREESYSTEMCACHE('All') WITH NO_INFOMSGS

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS

SET NOCOUNT on

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=10

WHILE (@start<=@end)

BEGIN --循环开始

DBCC FREEPROCCACHE

DBCC FREESESSIONCACHE

DBCC FREESYSTEMCACHE('All')

DBCC DROPCLEANBUFFERS

declare @d datetime

set @d = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test

WHERE 费用_ID between 4106668 and 4106768

declare @result int

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

declare @d1 datetime

set @d1 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_ID

WHERE 费用_ID between 4106668 and 4106768

declare @result1 int

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

declare @d2 datetime

set @d2 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_guid

WHERE 费用_ID between 4106668 and 4106768

declare @result2 int

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

declare @d3 datetime

set @d3 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id_guid

WHERE 费用_ID between 4106668 and 4106768

declare @result3 int

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

declare @d4 datetime

set @d4 = getdate()

SELECT [费用_ID],

[水表编码],

[水表口径],

[水表用途],

[收费方式],

[水表状态],

[本期抄表日],

[上期读数],

[本期读数],

[实抄水量]

FROM test_id_guid1

WHERE 费用_ID between 4106668 and 4106768

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

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

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(毫秒)]