"Turn" Analysis of SQL Server parameterized query

Reprint to: http://www.cnblogs.com/lzrabbit/archive/2012/04/21/2460978.html

Error awareness 1. No need to prevent SQL injection from being parameterized
Parameterized query is to prevent the use of SQL injection, and other uses do not know, do not care, in principle, can not use parameters without parameters, why? Much trouble, I just do the internal system of the company do not worry about SQL injection risk, using parameterized query is not to make trouble for themselves, simple SQL, everything OK

Error 2. Whether parameter types are specified when parameterized queries, parameter lengths are no different
Have always been aware of the addition and the parameter length should be no difference, just the difference in writing, and feel that the addition of parameter type and length is too troublesome, recently only to understand that the two are not the same, in order to improve the speed of SQL execution, Add the SqlDbType and size properties to the SqlParameter parameter, and many developers ignore the type of the specified query parameter during the parameterized query code, which causes managed code to not automatically recognize the parameter type during execution. A full table scan of the field contents is then performed to determine the type of the parameter and convert it, consuming unnecessary query performance. As explained by MSDN: If size is not explicitly set in the size parameter, the size is inferred from the value of the DbType parameter. If you think the above inferred that the size is inferred from the SqlDbType type, then you are wrong, it is actually inferred from the value of the parameter that you passed, such as the value that is passing over is "username", the size value is 8, "username1", the size value is 9. So what kind of results will the different size values cause? And the test found that the value of size is not the same, it will cause the execution plan of the database will not be reused, so that each time the execution of SQL to regenerate the new execution plan, and waste the database execution time.

Here's a look at specific tests

Clear the query plan first

DBCC Freeproccache

Pass value username, do not specify parameter length, generate query plan

using (SqlConnection conn=New SqlConnection (connectionString)) {Conn.Open(); SqlCommand Comm=New SqlCommand (); Comm. Connection=Conn Comm.commandtext= "Select*From Userswhere UserName= @UserName // @UserName varchar (8)) select * from Users where username=@ Username Comm. Parameters. add (New SqlParameter (" @UserName", Sqldbtype.varchar) {Value =              

Pass value username1, do not specify parameter length, generate query plan

using (SqlConnection conn=New SqlConnection (connectionString)) {Conn.Open(); SqlCommand Comm=New SqlCommand (); Comm. Connection=Conn Comm.commandtext= "Select*From Userswhere UserName= @UserName ////query plan for ( @UserName select * from Users where UserName = @UserName  Comm. Parameters. add (New SqlParameter (" @UserName", Sqldbtype.varchar) {Value =              

Value username, specifying a parameter length of 50, to generate a query plan

using (SqlConnection conn=New SqlConnection (connectionString)) {Conn.Open(); SqlCommand Comm=New SqlCommand (); Comm. Connection=Conn Comm.commandtext= "Select*From Userswhere UserName=@UserName";////query plan for ( @UserName select * from Users where UserName = @UserName  Comm. Parameters. add (New SqlParameter (" @UserName", Sqldbtype.varchar,50) {Value = "username"}); Comm. ExecuteNonQuery ();}               

Value username1, specifying a parameter length of 50, to generate a query plan

using (SqlConnection conn=New SqlConnection (connectionString)) {Conn.Open(); SqlCommand Comm=New SqlCommand (); Comm. Connection=Conn Comm.commandtext= "Select*From Userswhere UserName=@UserName";////query plan for ( @UserName select * from Users where UserName = @UserName  Comm. Parameters. add (New SqlParameter (" @UserName", Sqldbtype.varchar,50) {Value = "username1"}); Comm. ExecuteNonQuery ();}               

Use the following statement to view the executed query plan

From'%users%'%syscacheobjects%'    

The result is as shown

You can see that a query that specifies the length of a parameter can reuse a query plan, and a query that does not specify a parameter length alters the query plan based on a specific value, resulting in a loss of performance.

The specified parameter length only refers to variable length data type, mainly refers to Varchar,nvarchar,char,nchar, etc., for fixed-length value types such as int,bigint,decimal,datetime, no need to specify (even if specified is not used), See the following test, UserID is of type int, regardless of length specified as 2, 20,-1 The query plan is exactly the same as (@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 Userswhere UserID=@UserID";Pass Value2, Parameter length 2Execution Plan (@UserIDintSelect*From Userswhere UserID=@UserIDComm. Parameters.ADD (New SqlParameter ("@UserID ", SqlDbType.Int2) {Value=2}); Comm. ExecuteNonQuery ();} using (SqlConnection conn=New SqlConnection (connectionString)) {Conn.Open(); SqlCommand Comm=New SqlCommand (); Comm. Connection=Conn Comm.commandtext= "Select*From Userswhere UserID=@UserID";Pass Value2, Parameter length 20Execution Plan (@UserIDintSelect*From Userswhere UserID=@UserIDComm. Parameters.ADD (New SqlParameter ("@UserID ", SqlDbType.Int{Value=2}); Comm. ExecuteNonQuery ();} using (SqlConnection conn=New SqlConnection (connectionString)) {Conn.Open(); SqlCommand Comm=New SqlCommand (); Comm. Connection=Conn Comm.commandtext= "Select*From Userswhere UserID=@UserID";Pass Value2, parameter length-1  Execution plan ( @UserID int) * from Users  where Userid= @UserID add (New SqlParameter (" @UserID", Sqldbtype.int, -1) {Value = 2}); Comm. ExecuteNonQuery ();}               

In this case, to pass the value of the varchar (max) or nvarchar (max) type How to pass, in fact, as long as the set length of 1 can be

using (SqlConnection conn=New SqlConnection (connectionString)) {Conn.Open(); SqlCommand Comm=New SqlCommand (); Comm. Connection=Conn Comm.commandtext= "Select*From Userswhere UserName=@UserName";Type is varchar (Max), specify a parameter length of-1  The query plan is ( @UserName varchar ( max)) select * from Users Span style= "color: #0000ff;" >where username= @UserName  Comm. Parameters. add (New SqlParameter (" @UserName", Sqldbtype.varchar,-1) {Value =              

Of course, if you do not use parametric query, directly splicing SQL, so there is no query plan reuse, unless you spell the SQL is exactly the same

Summary, the significance of parameterized query and the point of attention

1. can prevent SQL injection

2. Can improve query performance (mainly can be reused query plan), which is particularly important when the volume of data is large

3. Parameterized query parameter type is variable length (Varchar,nvarchar,char, etc.) specify the parameter type and length, if the value type (int,bigint,decimal,datetime, etc.) specify only the parameter type

4. When the value is varchar (max) or nvarchar (max), the parameter length is specified as-1.

5. See some children's shoes for the stored procedure to specify the length of the parameter is somewhat confused, here is added, if the call is a stored procedure, the parameter does not need to specify the length, if the specified is also ignored, the length defined in the stored procedure is the same, not because the length of the parameter is not specified to cause recompilation, However, it is recommended that even if you call the stored procedure with a length, keep good habits

"Turn" Analysis of SQL Server parameterized query