結論
可能な限り変数の宣言や代入は1ステートメントでやりましょう！
T-SQLでの変数の扱い方
変数の宣言
--DECLAREを並べて宣言することもできるし(3ステートメント)
DECLARE @variable001 int = 1
DECLARE @variable002 int = 2
DECLARE @variable003 int --値を指定しない場合はNULLで初期化されます
--カンマ区切りで宣言することもできる(1ステートメント)
DECLARE @variable001 int = 1
,@variable002 int = 2
,@variable003 int --値を指定しない場合はNULLで初期化されます
変数への代入
--SETを並べて代入することもできるし(3ステートメント)
SET @variable001 = 10
SET @variable002 = 20
SET @variable003 = 30
--SELECTで一気に代入することもできる(1ステートメント)
SELECT @variable001 = 10
,@variable002 = 20
,@variable003 = 30
ただし、SELECTでの代入は以下の点に注意。
1 つの SELECT ステートメントに複数の代入句がある場合、SQL Server では式の評価順序が保証されません。
https://docs.microsoft.com/ja-jp/sql/t-sql/language-elements/variables-transact-sql
実行速度の違い
書き方以外は同じかと思いきや、意外にも実行速度に違いがあります。
以下のように大量に実行してみると、手元の環境で15倍程度違いがありました。
コードサンプル
DECLARE @i int = 0
WHILE @i < 500000
BEGIN
DECLARE @variable001 int = 1
DECLARE @variable002 int = 2
DECLARE @variable003 int = 3
--...以下100個
SET @i +=1
END
ちなみに、変数の宣言については一括で値を指定しない場合が最速です。
コードの全文は以下を展開して確認してください。
サンプルコード全文(1つずつ宣言)
DECLARE @i int = 0
WHILE @i < 500000
BEGIN
DECLARE @variable001 int = 1
DECLARE @variable002 int = 2
DECLARE @variable003 int = 3
DECLARE @variable004 int = 4
DECLARE @variable005 int = 5
DECLARE @variable006 int = 6
DECLARE @variable007 int = 7
DECLARE @variable008 int = 8
DECLARE @variable009 int = 9
DECLARE @variable010 int = 10
DECLARE @variable011 int = 11
DECLARE @variable012 int = 12
DECLARE @variable013 int = 13
DECLARE @variable014 int = 14
DECLARE @variable015 int = 15
DECLARE @variable016 int = 16
DECLARE @variable017 int = 17
DECLARE @variable018 int = 18
DECLARE @variable019 int = 19
DECLARE @variable020 int = 20
DECLARE @variable021 int = 21
DECLARE @variable022 int = 22
DECLARE @variable023 int = 23
DECLARE @variable024 int = 24
DECLARE @variable025 int = 25
DECLARE @variable026 int = 26
DECLARE @variable027 int = 27
DECLARE @variable028 int = 28
DECLARE @variable029 int = 29
DECLARE @variable030 int = 30
DECLARE @variable031 int = 31
DECLARE @variable032 int = 32
DECLARE @variable033 int = 33
DECLARE @variable034 int = 34
DECLARE @variable035 int = 35
DECLARE @variable036 int = 36
DECLARE @variable037 int = 37
DECLARE @variable038 int = 38
DECLARE @variable039 int = 39
DECLARE @variable040 int = 40
DECLARE @variable041 int = 41
DECLARE @variable042 int = 42
DECLARE @variable043 int = 43
DECLARE @variable044 int = 44
DECLARE @variable045 int = 45
DECLARE @variable046 int = 46
DECLARE @variable047 int = 47
DECLARE @variable048 int = 48
DECLARE @variable049 int = 49
DECLARE @variable050 int = 50
DECLARE @variable051 int = 51
DECLARE @variable052 int = 52
DECLARE @variable053 int = 53
DECLARE @variable054 int = 54
DECLARE @variable055 int = 55
DECLARE @variable056 int = 56
DECLARE @variable057 int = 57
DECLARE @variable058 int = 58
DECLARE @variable059 int = 59
DECLARE @variable060 int = 60
DECLARE @variable061 int = 61
DECLARE @variable062 int = 62
DECLARE @variable063 int = 63
DECLARE @variable064 int = 64
DECLARE @variable065 int = 65
DECLARE @variable066 int = 66
DECLARE @variable067 int = 67
DECLARE @variable068 int = 68
DECLARE @variable069 int = 69
DECLARE @variable070 int = 70
DECLARE @variable071 int = 71
DECLARE @variable072 int = 72
DECLARE @variable073 int = 73
DECLARE @variable074 int = 74
DECLARE @variable075 int = 75
DECLARE @variable076 int = 76
DECLARE @variable077 int = 77
DECLARE @variable078 int = 78
DECLARE @variable079 int = 79
DECLARE @variable080 int = 80
DECLARE @variable081 int = 81
DECLARE @variable082 int = 82
DECLARE @variable083 int = 83
DECLARE @variable084 int = 84
DECLARE @variable085 int = 85
DECLARE @variable086 int = 86
DECLARE @variable087 int = 87
DECLARE @variable088 int = 88
DECLARE @variable089 int = 89
DECLARE @variable090 int = 90
DECLARE @variable091 int = 91
DECLARE @variable092 int = 92
DECLARE @variable093 int = 93
DECLARE @variable094 int = 94
DECLARE @variable095 int = 95
DECLARE @variable096 int = 96
DECLARE @variable097 int = 97
DECLARE @variable098 int = 98
DECLARE @variable099 int = 99
DECLARE @variable100 int = 100
SET @i +=1
END
サンプルコード全文(一括で宣言)
DECLARE @i int = 0
WHILE @i < 500000
BEGIN
DECLARE @variable001 int = 1
,@variable002 int = 2
,@variable003 int = 3
,@variable004 int = 4
,@variable005 int = 5
,@variable006 int = 6
,@variable007 int = 7
,@variable008 int = 8
,@variable009 int = 9
,@variable010 int = 10
,@variable011 int = 11
,@variable012 int = 12
,@variable013 int = 13
,@variable014 int = 14
,@variable015 int = 15
,@variable016 int = 16
,@variable017 int = 17
,@variable018 int = 18
,@variable019 int = 19
,@variable020 int = 20
,@variable021 int = 21
,@variable022 int = 22
,@variable023 int = 23
,@variable024 int = 24
,@variable025 int = 25
,@variable026 int = 26
,@variable027 int = 27
,@variable028 int = 28
,@variable029 int = 29
,@variable030 int = 30
,@variable031 int = 31
,@variable032 int = 32
,@variable033 int = 33
,@variable034 int = 34
,@variable035 int = 35
,@variable036 int = 36
,@variable037 int = 37
,@variable038 int = 38
,@variable039 int = 39
,@variable040 int = 40
,@variable041 int = 41
,@variable042 int = 42
,@variable043 int = 43
,@variable044 int = 44
,@variable045 int = 45
,@variable046 int = 46
,@variable047 int = 47
,@variable048 int = 48
,@variable049 int = 49
,@variable050 int = 50
,@variable051 int = 51
,@variable052 int = 52
,@variable053 int = 53
,@variable054 int = 54
,@variable055 int = 55
,@variable056 int = 56
,@variable057 int = 57
,@variable058 int = 58
,@variable059 int = 59
,@variable060 int = 60
,@variable061 int = 61
,@variable062 int = 62
,@variable063 int = 63
,@variable064 int = 64
,@variable065 int = 65
,@variable066 int = 66
,@variable067 int = 67
,@variable068 int = 68
,@variable069 int = 69
,@variable070 int = 70
,@variable071 int = 71
,@variable072 int = 72
,@variable073 int = 73
,@variable074 int = 74
,@variable075 int = 75
,@variable076 int = 76
,@variable077 int = 77
,@variable078 int = 78
,@variable079 int = 79
,@variable080 int = 80
,@variable081 int = 81
,@variable082 int = 82
,@variable083 int = 83
,@variable084 int = 84
,@variable085 int = 85
,@variable086 int = 86
,@variable087 int = 87
,@variable088 int = 88
,@variable089 int = 89
,@variable090 int = 90
,@variable091 int = 91
,@variable092 int = 92
,@variable093 int = 93
,@variable094 int = 94
,@variable095 int = 95
,@variable096 int = 96
,@variable097 int = 97
,@variable098 int = 98
,@variable099 int = 99
,@variable100 int = 100
SET @i +=1
END
サンプルコード全文(1つずつ代入)
DECLARE @i int = 0
DECLARE @variable001 int = 1
,@variable002 int = 2
,@variable003 int = 3
,@variable004 int = 4
,@variable005 int = 5
,@variable006 int = 6
,@variable007 int = 7
,@variable008 int = 8
,@variable009 int = 9
,@variable010 int = 10
,@variable011 int = 11
,@variable012 int = 12
,@variable013 int = 13
,@variable014 int = 14
,@variable015 int = 15
,@variable016 int = 16
,@variable017 int = 17
,@variable018 int = 18
,@variable019 int = 19
,@variable020 int = 20
,@variable021 int = 21
,@variable022 int = 22
,@variable023 int = 23
,@variable024 int = 24
,@variable025 int = 25
,@variable026 int = 26
,@variable027 int = 27
,@variable028 int = 28
,@variable029 int = 29
,@variable030 int = 30
,@variable031 int = 31
,@variable032 int = 32
,@variable033 int = 33
,@variable034 int = 34
,@variable035 int = 35
,@variable036 int = 36
,@variable037 int = 37
,@variable038 int = 38
,@variable039 int = 39
,@variable040 int = 40
,@variable041 int = 41
,@variable042 int = 42
,@variable043 int = 43
,@variable044 int = 44
,@variable045 int = 45
,@variable046 int = 46
,@variable047 int = 47
,@variable048 int = 48
,@variable049 int = 49
,@variable050 int = 50
,@variable051 int = 51
,@variable052 int = 52
,@variable053 int = 53
,@variable054 int = 54
,@variable055 int = 55
,@variable056 int = 56
,@variable057 int = 57
,@variable058 int = 58
,@variable059 int = 59
,@variable060 int = 60
,@variable061 int = 61
,@variable062 int = 62
,@variable063 int = 63
,@variable064 int = 64
,@variable065 int = 65
,@variable066 int = 66
,@variable067 int = 67
,@variable068 int = 68
,@variable069 int = 69
,@variable070 int = 70
,@variable071 int = 71
,@variable072 int = 72
,@variable073 int = 73
,@variable074 int = 74
,@variable075 int = 75
,@variable076 int = 76
,@variable077 int = 77
,@variable078 int = 78
,@variable079 int = 79
,@variable080 int = 80
,@variable081 int = 81
,@variable082 int = 82
,@variable083 int = 83
,@variable084 int = 84
,@variable085 int = 85
,@variable086 int = 86
,@variable087 int = 87
,@variable088 int = 88
,@variable089 int = 89
,@variable090 int = 90
,@variable091 int = 91
,@variable092 int = 92
,@variable093 int = 93
,@variable094 int = 94
,@variable095 int = 95
,@variable096 int = 96
,@variable097 int = 97
,@variable098 int = 98
,@variable099 int = 99
,@variable100 int = 100
WHILE @i < 500000
BEGIN
SET @variable001 = 1
SET @variable002 = 2
SET @variable003 = 3
SET @variable004 = 4
SET @variable005 = 5
SET @variable006 = 6
SET @variable007 = 7
SET @variable008 = 8
SET @variable009 = 9
SET @variable010 = 10
SET @variable011 = 11
SET @variable012 = 12
SET @variable013 = 13
SET @variable014 = 14
SET @variable015 = 15
SET @variable016 = 16
SET @variable017 = 17
SET @variable018 = 18
SET @variable019 = 19
SET @variable020 = 20
SET @variable021 = 21
SET @variable022 = 22
SET @variable023 = 23
SET @variable024 = 24
SET @variable025 = 25
SET @variable026 = 26
SET @variable027 = 27
SET @variable028 = 28
SET @variable029 = 29
SET @variable030 = 30
SET @variable031 = 31
SET @variable032 = 32
SET @variable033 = 33
SET @variable034 = 34
SET @variable035 = 35
SET @variable036 = 36
SET @variable037 = 37
SET @variable038 = 38
SET @variable039 = 39
SET @variable040 = 40
SET @variable041 = 41
SET @variable042 = 42
SET @variable043 = 43
SET @variable044 = 44
SET @variable045 = 45
SET @variable046 = 46
SET @variable047 = 47
SET @variable048 = 48
SET @variable049 = 49
SET @variable050 = 50
SET @variable051 = 51
SET @variable052 = 52
SET @variable053 = 53
SET @variable054 = 54
SET @variable055 = 55
SET @variable056 = 56
SET @variable057 = 57
SET @variable058 = 58
SET @variable059 = 59
SET @variable060 = 60
SET @variable061 = 61
SET @variable062 = 62
SET @variable063 = 63
SET @variable064 = 64
SET @variable065 = 65
SET @variable066 = 66
SET @variable067 = 67
SET @variable068 = 68
SET @variable069 = 69
SET @variable070 = 70
SET @variable071 = 71
SET @variable072 = 72
SET @variable073 = 73
SET @variable074 = 74
SET @variable075 = 75
SET @variable076 = 76
SET @variable077 = 77
SET @variable078 = 78
SET @variable079 = 79
SET @variable080 = 80
SET @variable081 = 81
SET @variable082 = 82
SET @variable083 = 83
SET @variable084 = 84
SET @variable085 = 85
SET @variable086 = 86
SET @variable087 = 87
SET @variable088 = 88
SET @variable089 = 89
SET @variable090 = 90
SET @variable091 = 91
SET @variable092 = 92
SET @variable093 = 93
SET @variable094 = 94
SET @variable095 = 95
SET @variable096 = 96
SET @variable097 = 97
SET @variable098 = 98
SET @variable099 = 99
SET @variable100 = 100
SET @i +=1
END
サンプルコード全文(一括で代入)
DECLARE @i int = 0
DECLARE @variable001 int = 1
,@variable002 int = 2
,@variable003 int = 3
,@variable004 int = 4
,@variable005 int = 5
,@variable006 int = 6
,@variable007 int = 7
,@variable008 int = 8
,@variable009 int = 9
,@variable010 int = 10
,@variable011 int = 11
,@variable012 int = 12
,@variable013 int = 13
,@variable014 int = 14
,@variable015 int = 15
,@variable016 int = 16
,@variable017 int = 17
,@variable018 int = 18
,@variable019 int = 19
,@variable020 int = 20
,@variable021 int = 21
,@variable022 int = 22
,@variable023 int = 23
,@variable024 int = 24
,@variable025 int = 25
,@variable026 int = 26
,@variable027 int = 27
,@variable028 int = 28
,@variable029 int = 29
,@variable030 int = 30
,@variable031 int = 31
,@variable032 int = 32
,@variable033 int = 33
,@variable034 int = 34
,@variable035 int = 35
,@variable036 int = 36
,@variable037 int = 37
,@variable038 int = 38
,@variable039 int = 39
,@variable040 int = 40
,@variable041 int = 41
,@variable042 int = 42
,@variable043 int = 43
,@variable044 int = 44
,@variable045 int = 45
,@variable046 int = 46
,@variable047 int = 47
,@variable048 int = 48
,@variable049 int = 49
,@variable050 int = 50
,@variable051 int = 51
,@variable052 int = 52
,@variable053 int = 53
,@variable054 int = 54
,@variable055 int = 55
,@variable056 int = 56
,@variable057 int = 57
,@variable058 int = 58
,@variable059 int = 59
,@variable060 int = 60
,@variable061 int = 61
,@variable062 int = 62
,@variable063 int = 63
,@variable064 int = 64
,@variable065 int = 65
,@variable066 int = 66
,@variable067 int = 67
,@variable068 int = 68
,@variable069 int = 69
,@variable070 int = 70
,@variable071 int = 71
,@variable072 int = 72
,@variable073 int = 73
,@variable074 int = 74
,@variable075 int = 75
,@variable076 int = 76
,@variable077 int = 77
,@variable078 int = 78
,@variable079 int = 79
,@variable080 int = 80
,@variable081 int = 81
,@variable082 int = 82
,@variable083 int = 83
,@variable084 int = 84
,@variable085 int = 85
,@variable086 int = 86
,@variable087 int = 87
,@variable088 int = 88
,@variable089 int = 89
,@variable090 int = 90
,@variable091 int = 91
,@variable092 int = 92
,@variable093 int = 93
,@variable094 int = 94
,@variable095 int = 95
,@variable096 int = 96
,@variable097 int = 97
,@variable098 int = 98
,@variable099 int = 99
,@variable100 int = 100
WHILE @i < 500000
BEGIN
SELECT @variable001 = 1
,@variable002 = 2
,@variable003 = 3
,@variable004 = 4
,@variable005 = 5
,@variable006 = 6
,@variable007 = 7
,@variable008 = 8
,@variable009 = 9
,@variable010 = 10
,@variable011 = 11
,@variable012 = 12
,@variable013 = 13
,@variable014 = 14
,@variable015 = 15
,@variable016 = 16
,@variable017 = 17
,@variable018 = 18
,@variable019 = 19
,@variable020 = 20
,@variable021 = 21
,@variable022 = 22
,@variable023 = 23
,@variable024 = 24
,@variable025 = 25
,@variable026 = 26
,@variable027 = 27
,@variable028 = 28
,@variable029 = 29
,@variable030 = 30
,@variable031 = 31
,@variable032 = 32
,@variable033 = 33
,@variable034 = 34
,@variable035 = 35
,@variable036 = 36
,@variable037 = 37
,@variable038 = 38
,@variable039 = 39
,@variable040 = 40
,@variable041 = 41
,@variable042 = 42
,@variable043 = 43
,@variable044 = 44
,@variable045 = 45
,@variable046 = 46
,@variable047 = 47
,@variable048 = 48
,@variable049 = 49
,@variable050 = 50
,@variable051 = 51
,@variable052 = 52
,@variable053 = 53
,@variable054 = 54
,@variable055 = 55
,@variable056 = 56
,@variable057 = 57
,@variable058 = 58
,@variable059 = 59
,@variable060 = 60
,@variable061 = 61
,@variable062 = 62
,@variable063 = 63
,@variable064 = 64
,@variable065 = 65
,@variable066 = 66
,@variable067 = 67
,@variable068 = 68
,@variable069 = 69
,@variable070 = 70
,@variable071 = 71
,@variable072 = 72
,@variable073 = 73
,@variable074 = 74
,@variable075 = 75
,@variable076 = 76
,@variable077 = 77
,@variable078 = 78
,@variable079 = 79
,@variable080 = 80
,@variable081 = 81
,@variable082 = 82
,@variable083 = 83
,@variable084 = 84
,@variable085 = 85
,@variable086 = 86
,@variable087 = 87
,@variable088 = 88
,@variable089 = 89
,@variable090 = 90
,@variable091 = 91
,@variable092 = 92
,@variable093 = 93
,@variable094 = 94
,@variable095 = 95
,@variable096 = 96
,@variable097 = 97
,@variable098 = 98
,@variable099 = 99
,@variable100 = 100
SET @i +=1
END
極端な例ですが、例えばカーソルのフェッチ結果を計する場合等には注意しましょう。
トレース取得時には更に注意
単純な実行速度の違いもありますが、個別に書く場合は当然その分ステートメント数が増加するため、拡張イベントやプロファイラでトレースを取っている場合のイベントの増加に直結します。
このとき注意が必要なのが、Duration等でフィルターを掛けていた場合、記録はされないが負荷を増大させるという点です。
例えば、以下のように実行時間が1秒以上のステートメントを取得する拡張イベントのセッションを開始している状態だと、手元の環境で約7倍実行時間が変わります。
CREATE EVENT SESSION [sp_statement_completed] ON SERVER
ADD EVENT sqlserver.sp_statement_completed
(
ACTION
(
sqlserver.sql_text
)
WHERE
(
[Duration] >= (1000000)
)
)
ADD TARGET package0.event_file(SET filename=N'sp_statement_completed.xel')
WITH
(
MAX_MEMORY=4096 KB,
EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY=30 SECONDS,
MAX_EVENT_SIZE=0 KB,
MEMORY_PARTITION_MODE=NONE,
TRACK_CAUSALITY=OFF,
STARTUP_STATE=OFF
)
GO