SqlParameter 使用

 看到很多朋友在实例化SqlParameter时，通常都没有指定参数的长度就直接给参数赋值了。就像下面的写法：

new SqlParameter("@address", SqlDbType.Varchar) { Value = address};

    以前也一直都觉的加与不加参数长度应该没有什么区别，仅是写法上的不同而已，直到做完这个项目，才明白其实两者不一样的，为了提高sql执行速度，请为SqlParameter参数加上size属性。

 

    下面我们分析一下两者的区别：

    根据MSDN解释：如果未在size参数中显式设置Size，则从dbType参数的值推断出该大小。

如果你认为上面的推断出该大小是指从SqlDbType类型推断，那你就错了，它实际上是从你传过来的参数的值来推断的，比如传递过来的值是"shengzhen"，则size值为9，"shanghai"，则size值为8。那么，不同的size值会引发什么样的结果呢？且经测试发现，size的值不同时，会导致数据库的执行计划不会重用，这样就会每次执行sql的时候重新生成新的执行计划，而浪费数据库执行时间。

    比如：

[csharp]  view plain copy print ?

1. <span style="font-size:18px;">string sql = "select top 1 * from table1 where address = @address";  
2. SqlParameter[] parameter = new SqlParameter("@address", SqlDbType.VarChar) {Value = "shanghai"};  
3. SqlHelper.ExecuteReader(ConnectionString, CommandType.Text, sql, parameter)  
4. </span>  

    我们通过数据库跟踪工具SqlProfiler捕获到的sql如下：

[sql]  view plain copy print ?

1. <span style="font-size:18px;">exec sp_executesql N'select top 1 * from table1 where address = @address',N'@address nvarchar(8)',@address=N'shanghai'  
2. </span>  

    如果把参数的值改成shengzhen，则通过SqlProfiler捕获到的sql如下：

[sql]  view plain copy print ?

1. <span style="font-size:18px;">exec sp_executesql N'select top 1 * from table1 where address = @address',N'@address nvarchar(9)',@o=N'shengzhen'  
2. </span>  

     然后通过下面的sql来测试执行计划是否有重用：

[sql]  view plain copy print ?

1. <span style="font-size:18px;">--清空执行计划缓存  
2. DBCC FREESYSTEMCACHE('ALL')  
3. DBCC FREEPROCCACHE  
4. GO  
5. exec sp_executesql N'select top 1 * from T_GroupInfo where groupName = @groupName',N'@groupName nvarchar(8)',@groupName=N'第1组'  
6. GO  
7. SELECT * FROM sys.dm_exec_cached_plans WHERE cacheobjtype = 'Compiled Plan'  
8. GO  
9. exec sp_executesql N'select top 1 * from T_GroupInfo where groupName = @groupName',N'@groupName nvarchar(9)',@groupName=N'第2组'  
10. GO  
11. SELECT * FROM sys.dm_exec_cached_plans WHERE cacheobjtype = 'Compiled Plan'  
12. GO  
13. </span>  

    你会发现，第一条sql与第二条sql会各自生成自己的执行计划，而如果@address参数的长度一样的话，会使用同一个执行计划。

 

    总结：

    实例化SqlParameter时，如果是字符型，一定要指定size属性，如最上面的定义，应该修改为：

new SqlParameter("@address", SqlDbType.Varchar, 4000) { Value = address};

如果是Int、Float、Bigint,DateTime之类的参数，可以不用指定size属性。但如果size指定错误，则可能出现无法预知的错误。