区别一:
<
PRE lang
=
sql
>
CREATE
table
#T (s
varchar
(
128
))
DECLARE
@T
table
(s
varchar
(
128
))
INSERT
into
#T
select
'
old value #
'
INSERT
into
@T
select
'
old value @
'
BEGIN
transaction
UPDATE
#T
set
s
=
'
new value #
'
UPDATE
@T
set
s
=
'
new value @
'
ROLLBACK
transaction
SELECT
*
from
#T
SELECT
*
from
@T
s
--
-------------
old value #
![None.gif](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
s
--
-------------
new value @
</
PRE
>
这行代码一目了然,临时表能起到事务回滚的作用,但是table变量不可以.作者解释是table变量不在事务作用范围之内.所以当table变量即使遇到回滚命令,但是也不会真的执行回滚.
区别二:
任何含有临时表的存储过程是不能被预编译的.这在一个很长的存储过程中,优势会更加凸显.
区别三:
table变量作为变量只能在同范围内存在,不可能跨范围.还有就是table变量在内置的存储过程中或者exec(string)语句中是不可见的还有就是不能被用于INSERT/EXEC statements.
下面的例子我来演示如何对比临时表和table变量在存储过程中预编译的区别.
创建一个测试表:
CREATE
table
NUM (n
int
primary
key
, s
varchar
(
128
))
GO
SET
nocount
on
DECLARE
@n
int
SET
@n
=
1000000
WHILE
@n
>
0
begin
INSERT
into
NUM
SELECT
@n
,
'
Value:
'
+
convert
(
varchar
,
@n
)
SET
@n
=
@n
-
1
END
GO
然后创建存储过程:T1
CREATE
procedure
T1
@total
int
AS
CREATE
table
#T (n
int
, s
varchar
(
128
))
INSERT
into
#T
select
n,s
from
NUM
WHERE
n
%
100
>
0
and
n
<=
@total
DECLARE
@res
varchar
(
128
)
SELECT
@res
=
max
(s)
from
NUM
WHERE
n
<=
@total
and
NOT
exists
(
select
*
from
#T
WHERE
#T.n
=
NUM.n)
GO
这个存储过程的参数@Total给定的越大,那么执行的时间越长.
为精确测量存储过程执行时间,我使用了以下代码:
DECLARE
@t1
datetime
,
@n
int
SET
@t1
=
getdate
()
SET
@n
=
100
– (
**
)
WHILE
@n
>
0
begin
EXEC
T1
1000
– (
*
)
SET
@n
=
@n
-
1
end
SELECT
datediff
(ms,
@t1
,
getdate
())
GO
(*) 是存储过程参数.
现在我们给这个存储过程来第一次提速:个它加个主键
CREATE
procedure
T2
@total
int
AS
CREATE
table
#T (n
int
primary
key
, s
varchar
(
128
))
INSERT
into
#T
select
n,s
from
NUM
WHERE
n
%
100
>
0
and
n
<=
@total
DECLARE
@res
varchar
(
128
)
SELECT
@res
=
max
(s)
from
NUM
WHERE
n
<=
@total
and
NOT
exists
(
select
*
from
#T
WHERE
#T.n
=
NUM.n)
GO
在下面的对比中你可以发现已经大大的提速了.
然后再来一次提速:给它加个聚集索引
CREATE
procedure
T3
@total
int
AS
CREATE
table
#T (n
int
, s
varchar
(
128
))
INSERT
into
#T
select
n,s
from
NUM
WHERE
n
%
100
>
0
and
n
<=
@total
CREATE
clustered
index
Tind
on
#T (n)
DECLARE
@res
varchar
(
128
)
SELECT
@res
=
max
(s)
from
NUM
WHERE
n
<=
@total
and
NOT
exists
(
select
*
from
#T
WHERE
#T.n
=
NUM.n)
GO
很惊讶速度已经提高了很多!
那好了,咱们再来测试一下table变量的速度吧
CREARE
procedure
V1
@total
int
AS
DECLARE
@V
table
(n
int
, s
varchar
(
128
))
INSERT
into
@V
select
n,s
from
NUM
WHERE
n
%
100
>
0
and
n
<=
@total
DECLARE
@res
varchar
(
128
)
SELECT
@res
=
max
(s)
from
NUM
WHERE
n
<=
@total
and
NOT
exists
(
select
*
from
@V
V
WHERE
V.n
=
NUM.n)
GO
然后再来创建一个有主键的:
CREATE
procedure
V2
@total
int
AS
DECLARE
@V
table
(n
int
primary
key
, s
varchar
(
128
))
INSERT
into
@V
select
n,s
from
NUM
WHERE
n
%
100
>
0
and
n
<=
@total
DECLARE
@res
varchar
(
128
)
SELECT
@res
=
max
(s)
from
NUM
WHERE
n
<=
@total
and
NOT
exists
(
select
*
from
@V
V
WHEREre V.n
=
NUM.n)
GO
<
然后咱们来看看我测试的结果吧!
Table 1, using SQL Server 2000, time in ms
Records | T1 | T2 | T3 | V1 | V2 |
10 | 0.7 | 1 | 13.5 | 0.6 | 0.8 |
100 | 1.2 | 1.7 | 14.2 | 1.2 | 1.3 |
1000 | 7.1 | 5.5 | 27 | 7 | 5.3 |
10000 | 72 | 57 | 82 | 71 | 48 |
100000 | 883 | 480 | 580 | 840 | 510 |
1000000 | 45056 | 6090 | 15220 | 20240 | 12010 |
But the real shock is when you try the same on SQL Server 2005:
Table 2
N | T1 | T2 | T3 | V1 | V2 |
10 | 0.5 | 0.5 | 5.3 | 0.2 | 0.2 |
100 | 2 | 1.2 | 6.4 | 61.8 | 2.5 |
1000 | 9.3 | 8.5 | 13.5 | 168 | 140 |
10000 | 67.4 | 79.2 | 71.3 | 17133 | 13910 |
100000 | 700 | 794 | 659 | Too long! | Too long! |
1000000 | 10556 | 8673 | 6440 | Too long! | Too long! |
发现对比在某些情况下sql2000的速度要比2005的要快上很多!
结论:
没有通用的规则指导你什么时候用临时表什么时候用table变量.
将复杂逻辑的存储过程移植到sql2005的时候,要格外小心!他可能比2000的效率要慢上好几十倍的!
在你的实际测试中,请测试两个极端:销量数据和超大量数据.
此文章翻译自codeproject:http://www.codeproject.com/cs/database/SQP_performance.asp