浅析Sql Server参数化查询

说来惭愧，工作差不多4年了，直到前些日子被DBA找上门让我优化一个CPU占用很高的复杂SQL语句时，我才突然意识到了参数化查询的重要性。

相信有很多开发者和我一样对于参数化查询认识比较模糊，没有引起足够的重视

错误认识1.不需要防止sql注入的地方无需参数化

　　参数化查询就是为了防止SQL注入用的，其它还有什么用途不知道、也不关心，原则上是能不用参数就不用参数，为啥？多麻烦，我只是做公司内部系统不用担心SQL注入风险，使用参数化查询不是给自己找麻烦，简简单单拼SQL，万事OK  www.2cto.com  

错误认识2.参数化查询时是否指定参数类型、参数长度没什么区别

　　以前也一直都觉的加与不加参数长度应该没有什么区别，仅是写法上的不同而已，而且觉得加参数类型和长度写法太麻烦，最近才明白其实两者不一样的，为了提高sql执行速度，请为SqlParameter参数加上SqlDbType和size属性，在参数化查询代码编写过程中很多开发者忽略了指定查询参数的类型，这将导致托管代码在执行过程中不能自动识别参数类型，进而对该字段内容进行全表扫描以确定参数类型并进行转换，消耗了不必要的查询性能所致。
根据MSDN解释：如果未在size参数中显式设置Size，则从dbType参数的值推断出该大小。如果你认为上面的推断出该大小是指从SqlDbType类型推断，那你就错了，它实际上是从你传过来的参数的值来推断的，比如传递过来的值是"username"，则size值为8，"username1"，则size值为9。那么，不同的size值会引发什么样的结果呢？且经测试发现，size的值不同时，会导致数据库的执行计划不会重用，这样就会每次执行sql的时候重新生成新的执行计划，而浪费数据库执行时间。

下面来看具体测试

首先清空查询计划

DBCC FREEPROCCACHE

传值username，不指定参数长度，生成查询计划

 

using (SqlConnection conn = new SqlConnection(connectionString))

{

    conn.Open();

    SqlCommand comm = new SqlCommand();

    comm.Connection = conn;  www.2cto.com  

    comm.CommandText = "select * from Users where UserName=@UserName";

    //传值 username，不指定参数长度

    //查询计划为(@UserName varchar(8))select * from Users where UserName=@UserName

    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username" });

    comm.ExecuteNonQuery();

}

 

 

传值username1，不指定参数长度，生成查询计划

 

using (SqlConnection conn = new SqlConnection(connectionString))

{

    conn.Open();

    SqlCommand comm = new SqlCommand();

    comm.Connection = conn;

    comm.CommandText = "select * from Users where UserName=@UserName";

    //传值 username1，不指定参数长度

    //查询计划为(@UserName varchar(9))select * from Users where UserName=@UserName

    comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar) { Value = "username1" });