Development history of SQL Server database stored procedures

SQL Server stored procedures can be Transact-SQL (T-SQL) statements, or. NET Framework's common language runtime (CLR), which is designed to facilitate database queries. They accept input parameters, return output parameters, can be executed in the database to check the various operations. Stored procedures provide a great deal of convenience, from reducing network traffic to reusing cache execution plans, as well as enhancing security and promoting ease of maintenance.

The SQL Server 2008 version introduces a number of new features that provide a significant boost in the performance of stored procedures compared to earlier versions of the same product, SQL Server 2005. Three years later, 2008 R2 launched, which has only a few minor adjustments to the stored procedure. SQL Server 2012, which is due to be released early this year, will achieve a significant increase in its performance.

Development of stored procedures

The biggest elevation of stored procedures in SQL Server 2008 and 2008 R2 is table-valued parameters. This type of parameter can group multiple rows of data into datasets and then send them to the database server at once, reducing the round-trip interaction between the client and the server, and it does not need to create a temporary table or a large number of parameters. Table-valued parameters do not require any locks to be applied when extracting data. This feature also supports specifying a sort order.

The structure of table-valued parameters is easy to understand and use, and it can be adapted to complex business logic. Using table-valued parameters is no different from using traditional stored procedure parameters. First, declare a user-defined table type and use the table type input parameter to create the stored procedure. Next, declare the table type variable and reference it. Use the INSERT statement to assign a value to a table variable, instead of using a "SET" statement or a "select" statement. Finally, the filled table variable is added to the stored procedure as an input parameter.

SQL Server 2008 also has new "merge" statements that support the merging of multiple data Manipulation language (DML) operations into a T-SQL statement. Using the appropriate index on the join table, you can get the performance of the query improved. This is because a single "MERGE" statement reduces the time that data is processed in the source and target tables. In SQL Server 2005 and earlier versions, each INSERT, DELETE, and UPDATE statement must process the data at once. "MERGE" is very useful, for example, when you are synchronizing two tables, you can use it.

In SQL Server 2008 and 2008 R2 stored procedures, the ability to group operations is enhanced through the "GROUPING SET" syntax. A simple GROUP BY statement returns one row for each combination of all column values, and "GROUPING SETS" returns one row for each unique value of each column. Without this new feature, the latter will use the "UNION" structure to execute multiple "GROUP by" statements for each column. That would obviously require more resources, such as disk IO operations, memory, and elapsed time.

When the stored procedure is programmed, SQL Server 2008 and 2008 R2 can also use the new row builder to insert multiple rows in a row of "insert" statements. The database administrator may be familiar with this part of the syntax: INSERT into TABLE (Cola, Colb, Colc) ... VALUES (v1a, v1b, v1c ...), (V2A, v2b, v2c ...), ... (v1000a, v1000b, v1000c ...) ". Where the number "1000" represents the maximum number of rows allowed for each INSERT statement. The new INSERT syntax is more concise because it goes beyond repeating strings and enables faster interaction with the server by reducing network traffic.

In my third experiment, I ran 10 batches of statements, 1000 new types of inserts per batch, and 10,000 separate inserts executed in parallel. Each test counts as an interaction, but compared to the old "INSERT", the simplified row structure cuts the number of transactions and the number of bytes sent by the server by 1000 times times, reducing the client's time to send data three times times, and the client processing time 300 times times. Overall execution time fell by more than half.

Stored procedures in the 2012

SQL Server stored procedures will also have some adjustments in the new release that is about to be released. In SQL Server 2012, the performance of stored procedures in a data warehouse can be increased by 10 times times, multiplied, or thousands of thousand by using columns to store indexes, and the column storage index is the extensible feature of Third-party Community technology previews, which is expected to be released this summer. A column storage index is unlike a traditional row-based index structure (also known as a row store index), which groups a column at a time and stores index data, thus greatly reducing query execution time.

SQL Server 2012 also supports developers online rebuilding indexes on large object data types. That is, in SQL Server 2008 R2 and earlier versions, users cannot perform rebuild operations online for varchar (max), nvarchar (max), or varbinary (max) columns, or XML data types. Stored procedures now extract data from these data types and do not have to wait for the Database Maintenance window to collate the index, thereby enhancing the performance of the stored procedure.

Another new feature is the query optimizer hint called "Forcescan." When the SQL Server query optimizer underestimates the number of rows returned and mistakenly selects an index lookup operation, SQL Server developers can have the query scan alternate indexes. In this case, the query processing takes less time to complete.

I'm just here to make a brief introduction to the features of the SQL Server 2008,2008 R2 and 2012 versions about the performance of stored procedures. There are also a number of subtle changes in these versions of SQL Server that also indirectly contribute to the performance improvement of stored procedures, such as new functions and statements, filtering indexes, and new execution plan guides. But the features listed here, regardless of which version you are using, will definitely give you more performance improvement than ever before.