动态语句和使用参数的相同动态语句和静态语句三者之间的性能差异

今天一个无聊人问我为什么SQL语句要放在存储过程中，而不是放在程序里面。我回头正好看到一篇类似的文章，并做了补足。阐述一下a.动态语句，和b.使用参数的相同动态语句，和c.静态语句三者之间的性能差异。

技术底层就是对语句缓存的性能效果

a.动态语句(类似把语句直接放在程序里面)
DECLARE @t datetime
SET @t = getdate()
SET NOCOUNT ON
DECLARE @i INT, @count INT, @sql nvarchar(4000)
SET @i = 20000
WHILE @i <= 30000
BEGIN
SET @sql = 'SELECT @count=count(*) FROM table_1 WHERE idx = ' + cast( @i as varchar(10) )
EXEC sp_executesql @sql ,N'@count INT OUTPUT', @count OUTPUT
SET @i = @i + 1
END
PRINT DATEDIFF( second, @t, current_timestamp )
go
17 sec

b.使用参数的相同动态语句
DECLARE @t datetime
SET @t = getdate()
SET NOCOUNT ON
DECLARE @i INT, @count INT, @sql nvarchar(4000)
SET @i = 20000
WHILE @i <= 30000
BEGIN
SET @sql = 'select @count=count(*) FROM table_1 WHERE idx = @i'
EXEC sp_executesql @sql, N'@count int output, @i int', @count OUTPUT, @i
SET @i = @i + 1
END
PRINT DATEDIFF( second, @t, current_timestamp )
go
2 sec

c.静态语句
DECLARE @t datetime
SET @t = getdate()
SET NOCOUNT ON
DECLARE @i INT, @count INT, @sql nvarchar(4000)
SET @i = 20000
WHILE @i <= 30000
BEGIN
select @count=count(*) FROM table_1 WHERE idx = @i
SET @i = @i + 1
END
PRINT DATEDIFF( second, @t, current_timestamp )
go
1 sec