SQL Server parameterized Query

I am ashamed to say that I have been working for almost four years. I was not aware of the importance of parameterized query until I was visited by the DBA a few days ago to optimize a complicated SQL statement with a high CPU usage.

I believe many developers, like me, have a vague understanding of parameterized queries and have not paid enough attention to them.

Error description 1. There is no need to prevent SQL injection and no parameterization is required.
Parameterized query is used to prevent SQL injection. If you do not know what other functions are available or do not care about it, you can choose not to use parameters in principle. Why? Too much trouble. I just do not have to worry about SQL Injection risks in the internal system of the company. using parameterized queries is not a problem for myself. It is easy to spell SQL and everything is OK.

Error awareness 2. There is no difference in whether the parameter type and length are specified during parameter query.
I have always felt that there should be no difference between the length of parameters added and not added. It is just a different way of writing, and it is too troublesome to add parameter types and length writing methods, recently, the two are different. To improve the SQL Execution speed, add the sqldbtype and size attributes to the sqlparameter parameter and query the parameters in parameters.CodeDuring the compilation process, many developers ignore the specified query parameter type, which will make the managed code unable to automatically identify the parameter type during execution, then, a full table scan is performed on the field content to determine the parameter type and convert it. This results in unnecessary query performance. According to msdn: If the size parameter is not explicitly set, the size is inferred from the value of dbtype. If you think the above inference indicates that the size is inferred from the sqldbtype type, then you are wrong. It is actually inferred from the value of the parameter you passed in, for example, if the passed value is "username", the size value is 8, "username1", and the size value is 9. So what results will different size values lead? The test shows that the execution plan of the database will not be reused if the size value is different. In this way, a new execution plan will be generated every time the SQL statement is executed, this is a waste of database execution time.

Next let's take a look at the specific test

First, clear the query plan.

DBCCFreeproccache
 

Pass username,No parameter length is specified to generate a query plan

Using (sqlconnection Conn =  New sqlconnection (connectionstring) {Conn.  Open  (); Sqlcommand comm  =  New sqlcommand (); Comm. Connection  =  Conn; Comm. commandtext  = " Select   *  From Users Where Username =  @ Username  ";  //  The parameter length is not specified.  // The query plan is ( @ Username   Varchar ( 8 )) Select   *   From Users Where Username =  @ Username  Comm. parameters.  Add (New sqlparameter (" @ Username ", Sqldbtype. Varchar ) {Value =  "Username"}); Comm. executenonquery ();} 

 

Pass username1,No parameter length is specified to generate a query plan

Using (sqlconnection Conn = New sqlconnection (connectionstring) {Conn.  Open  (); Sqlcommand comm  =  New sqlcommand (); Comm. Connection  =  Conn; Comm. commandtext  = " Select   *   From Users Where Username =  @ Username ";  //  The parameter length is not specified.  // The query plan is ( @ Username   Varchar ( 9 )) Select   *   From Users Where Username =  @ Username  Comm. parameters.  Add (New sqlparameter (" @ Username ", Sqldbtype. Varchar ) {Value =  "Username1"}); Comm. executenonquery ();} 

Pass username,Specify the parameter length to 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 Users Where Username =  @ Username  ";  //  Specify the parameter length as 50  // The query plan is ( @ Username   Varchar ( 50 )) Select   *   From Users Where Username =  @ Username  Comm. parameters.  Add (New sqlparameter (" @ Username ", Sqldbtype. Varchar , 50 ) {Value =  "Username"}); Comm. executenonquery ();} 

 

Pass username1,Specify the parameter length to 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 Users Where Username =  @ Username  ";  //  Specify the parameter length as 50  // The query plan is ( @ Username   Varchar ( 50 )) 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 execution query plan

SelectCacheobjtype, objtype, usecounts, SQLFromSYS. syscacheobjectsWhereSQLLike '% Users %'AndSQLNot Like '% Syscacheobjects %'
 

Shows the result.

You can see that queries with the specified parameter length can reuse the query plan. Queries without the specified parameter length will change the query plan based on the specific value, resulting in performance loss.

The length of the specified parameter here only refers to the variable-length data type, mainly refers to varchar, nvarchar, Char, nchar, etc. For int, bigint, decimal, datetime and other fixed-length value types, no need to specify (even if it is specified). For details, see the test below. The USERID is of the int type, and the query plan with the length specified as 2, 20, and-1 is exactly the same.(@ UseridInt)Select*FromUsersWhereUserid=@ Userid

Using (sqlconnection Conn =  New sqlconnection (connectionstring) {Conn. Open  (); Sqlcommand comm  =  New sqlcommand (); Comm. Connection  =  Conn; Comm. commandtext  = " Select   *   From Users Where Userid =  @ Userid  ";  // Pass Value2  , Parameter length 2  // Execution Plan ( @ 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  ";  // Pass Value 2  , Parameter length: 20  // Execution Plan ( @ 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  "; // Pass Value 2 , Parameter length -  1      // Execution Plan ( @ Userid   Int ) Select   *   From Users Where Userid =  @ Userid  Comm. parameters.  Add (New sqlparameter (" @ Userid ", Sqldbtype. Int , -  1 ) {Value =   2  }); Comm. executenonquery ();} 

Here, if you want to pass the value of the varchar (max) or nvarchar (max) type, you only need to set the length to-1.

Using (sqlconnection Conn =  New sqlconnection (connectionstring) {Conn.  Open  (); Sqlcommand comm  = New sqlcommand (); Comm. Connection  =  Conn; Comm. commandtext  = " Select   *   From Users Where Username =  @ Username  ";  // Type: varchar ( Max ), The specified parameter length is - 1      // The query plan is ( @ Username   Varchar ( Max )) Select   *   From Users Where Username =  @ Username  Comm. parameters.  Add (New sqlparameter (" @ Username ", Sqldbtype.Varchar , -  1 ) {Value =  "Username1"}); Comm. executenonquery ();} 

Of course, if you do not use parameterized queries and directly concatenate SQL statements, there will be no query plans to be reused, unless the SQL statements you spell each time are exactly the same.

Summary: Meanings and notes of parameterized Query

1. SQL Injection prevention

2. query performance can be improved (query plans can be reused), which is especially important when the data volume is large.

3. when the parameter type of parameterized query is variable length (varchar, nvarchar, Char, etc.), please specify the parameter type and length. If it is a value type (INT, bigint, decimal, datetime, etc) only the parameter type is specified.

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

5. some children's shoes have some doubts about whether to specify the parameter length for the stored procedure. Here, we add that if the stored procedure is called, the parameter does not need to be specified. If it is specified, it will be ignored, the length defined in the stored procedure is used as the standard and will not be re-compiled because the parameter length is not specified. However, we recommend that you add the length even when calling the stored procedure to maintain a good habit.