使用下面语句查看执行的查询计划
SELECT cacheobjtype,objtype,usecounts,sql FROM sys.syscacheobjects
WHERE sql LIKE '%Users%' and sql not like '%syscacheobjects%'
结果如下图所示
可以看到指定了参数长度的查询可以复用查询计划，而不指定参数长度的查询会根据具体传值而改变查询计划，从而造成性能的损失。
这里的指定参数长度仅指可变长数据类型，主要指varchar，nvarchar,char,nchar等，对于int,bigint,decimal,datetime等定长的值类型来说，无需指定(即便指定了也没有用)，详见下面测试,UserID为int类型，无论长度指定为2、20、-1查询计划都完全一样为(@UserIDint)select*from Users where UserID=@UserID
using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = "select * from Users where UserID=@UserID"; //传值 2，参数长度2 //执行计划(@UserID int)select * from Users where UserID=@UserID comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 2) { Value = 2 }); comm.ExecuteNonQuery(); } using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = "select * from Users where UserID=@UserID"; //传值 2，参数长度20 //执行计划(@UserID int)select * from Users where UserID=@UserID comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, 20) { Value = 2 }); comm.ExecuteNonQuery(); } using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = "select * from Users where UserID=@UserID"; //传值 2，参数长度-1 //执行计划(@UserID int)select * from Users where UserID=@UserID comm.Parameters.Add(new SqlParameter("@UserID", SqlDbType.Int, -1) { Value = 2 }); comm.ExecuteNonQuery(); } |
这里提一下，若要传值varchar(max)或nvarchar(max)类型怎么传，其实只要设定长度为-1即可
using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); SqlCommand comm = new SqlCommand(); comm.Connection = conn; comm.CommandText = "select * from Users where UserName=@UserName"; //类型为varchar(max)时，指定参数长度为-1 //查询计划为 (@UserName varchar(max) )select * from Users where UserName=@UserName comm.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar,-1) { Value = "username1" }); comm.ExecuteNonQuery(); } |
当然了若是不使用参数化查询，直接拼接SQL，那样就更没有查询计划复用一说了，除非你每次拼的SQL都完全一样
总结，参数化查询意义及注意点
1.可以防止SQL注入
2.可以提高查询性能(主要是可以复用查询计划)，这点在数据量较大时尤为重要
3.参数化查询参数类型为可变长度时(varchar,nvarchar,char等)请指定参数类型及长度，若为值类型(int,bigint,decimal,datetime等)则仅指定参数类型即可
4.传值为varchar(max)或者nvarchar(max)时，参数长度指定为-1即可
5.看到有些童鞋对于存储过程是否要指定参数长度有些疑惑，这里补充下，若调用的是存储过程时，参数无需指定长度，如果指定了也会忽略，以存储过程中定义的长度为准，不会因为没有指定参数长度而导致重新编译，不过还是建议大家即便时调用存储过程时也加上长度，保持良好的变成习惯
