Improvements in SQL Server 2008 T-SQL command

SQL Server 2008 integrates many remarkable new features. One of these features is improvements in T-SQL statements that reduce the time spent working on these statements.

Network Management - SQL Server 2008 is integrated with many notable new features. One of these features is improvements in T-SQL statements that reduce the time spent working on these statements.

In the first part of this series we will explore some of the improvements of T-SQL.

Intellisense improvements (smart touch)

With Intellisense's improvements, users can now save data, find the necessary information, directly insert T-SQL language components into the code and this smart sensor can help shorten the time to enter. command from the keyboard. This function can shorten software development time by reducing keyboard operation time and minimizing references to external documents. Intellisense improvements include an extended T-SQL language area and a color system integration.

This feature works like automatic syntax checking in Visual Studio. When entering the T-SQL command, it will automatically complete the syntax for the command, and for the objects in the database, even though the variables were previously declared. Users may choose to use this feature or disable it if deemed unnecessary.

Improvements in SQL Server 2008 T-SQL command Picture 1

Improvements in T-SQL command syntax

In SQL Server 2008, the syntax of the T-SQL command has three main improvements:

1. Initialize the variable at the same time as the declaration . Now users can initialize variables right after declaring instead of using two separate DECLARE and SET commands as before. It can work with most data types including SQLCLR data types, but will not work with data types TEXT, NTEXT or IMAGE.

2. Compound Operators . Compound Operators are operators like the ones in C ++ and C #. They perform some operations and set an original value for the result of the operation. They help avoid some errors and allow abbreviations when working with code. In addition, this operator can be used in the SET clause of the UPDATE statement. Below is a list of supported operators in SQL Server 2008:

+ = Add and assign
- = Subtract and assign
* = Multiply and assign
/ = Divide and assign
% = Modulo and assign
& = Bitwise AND and assign
= = Bitwise XOR and assign
| = Bitwise OR and assign

For example:

Improvements in SQL Server 2008 T-SQL command Picture 2

3. Row Constructor . Improved T-SQL allows inserting multiple values with a single INSERT statement. This means that users can include multiple row attributes in the VALUE clause.

For example:

Improvements in SQL Server 2008 T-SQL command Picture 3

Improvements in the dependencies of the object

Improvements in object-dependent components provide reliable information of many dependencies between objects through the newly introduced catalog view and dynamic management functions. The dependency component information is always updated for the object schema scope (where object A cannot be deleted because object B depends on it) and outside the object schema (where object A can deleted or even unable to be created but object B still depends on it). Dependent components are checked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema collections, etc. SQL Server 2008 introduces three new objects that provide information believe the dependency of the object, including:

1. sys.sql_expression_dependenciescatalog view : Displays the names of the dependencies of the object. It includes a record for each dependent component on a user-defined object in the current database.

2. sys.dm_sql_referenced_entitiesDMF : Provides all entities that the entry entity depends on, returning a row for each user defined reference by name in the definition of the specified reference entity.

3. sys.dm_sql_referencing_entitiesDMF : Providing all objects depending on the import entity, it will return a record for each user defined in the current database referenced by the name of an object Other users are also defined.

There are two methods the user can use to see if those dependencies are using SSMS (right-click on the object and then choose View Dependencies) or by writing the query according to the view and the DMF just mentioned above.

Improvements in SQL Server 2008 T-SQL command Picture 4

Improvements in SQL Server 2008 T-SQL command Picture 5

Use FORCESEEK suggestion table

The FORCESEEK hint table is quite useful in the case of Query Plan (the steps used to access or edit information in a SQL-related database management system) use a table or Index Scan operator ( index scan) on a table or view, however, the Index Seek operator searches the index can be more efficient (for example, in the case of too many choices). Hint The FORCESEEK table forces Query Optimizer (the query optimizer) to use Index Seek operations only as the path to access data in the table or view referenced in the query. We can use this hint table to override Query Plan by default by the Query Optimizer to avoid the execution problems caused by Query Plan ineffective. For example, if a Plan contains a table or Index Scan operators, and the corresponding tables are continuously accessed during query execution, applying an Index Seek operation may be more efficient than using a query. . This ability will occur in the case that the components in the collection are incorrect or take into account the amount of time it takes to write a Query Plan that the Query Optimizer uses to perform a search.

Improvements in SQL Server 2008 T-SQL command Picture 6

One of the situations where this hint table becomes useful is the ability to work with Parameter Sniffing (a tool that the SQL Server optimizer uses to find variable values from queries while actually shows the command for the first time and creates an optimal execution plan based on that value). Let's verify this by running the following queries in the AdventureWorks database and analyzing the differences.

Case 1 : The first query returns 450 rows and is less selective than the second query only returns 16 records. Therefore, it is necessary to use Index Scan for the first query rather than using Index Seek and Lookup of the second query.

Improvements in SQL Server 2008 T-SQL command Picture 7

Case 2 : We will re-run the above queries, but in this case we will use variables to assign values to the query instead of directly entering the values. If the Plan tests are executed, we will see that both queries are using the same Index Scan even though the parameter values are completely different. In case 1, the second query is more selective and needs to use Index Seek and Lookup. That's because while the first query runs, the SQL query optimizer doesn't recognize the variable value until it runs. Because we used the variable and entered the filtering option on the filter and created the Plan to execute on the basis of this filter and store it again, the second query would also use the same executable Plan stored.

Improvements in SQL Server 2008 T-SQL command Picture 8

Case 3 : If the query uses parameters and every time these variable values return with high filtering capability, which method should we apply to force Query Optimizer to execute Index Seek instead of Index Scan? They look for two options here, either using the FORCESEEK hint or using the RECOMPILE option.

Improvements in SQL Server 2008 T-SQL command Picture 9

FORCESEEK applies to group and non-group indexing operations. It can be specified for every table or view in the FROM clause of the STATEMENT command and in the FROM clause of the UPDATE or DELETE statement.

Note: Do Query Optimizer of SQL Server only selects the best execution Plan for a query, so Microsoft proposes that experienced developers and database administrators only use suggestions like one. The final method because Optimizer always performs this task well.

GROUPING SETS

GROUPING SETS allows users to write a query to create multiple groups then only return a set of values. This value set is equivalent to a UNION ALL of different grouped rows. Using GROUPING SETS we can focus on the different levels of information to use in addition to using the method of combining a number of yvaans results.

With improved querying capabilities, GROUPING SETS allows us to make reports with multiple groups easily. Because the number of groups can always be increased, the simplicity and the performance enhancements brought by GROUPING SETS will become much more useful.

Improvements in SQL Server 2008 T-SQL command Picture 10

In other words, the GROUP BY clause using GROUPING SETS can render a result group corresponding to the result generated by a UNION ALL of multiple single GROUP BY clauses because the GROUP BY clause is only effective when combined. .

Conclude

In this first part we have explored some of the improvements in the T-SQL statement in SQL Server 2008 that support programmers' workflows. In the next part of this writing series, we will learn more about the data types introduced in SQL Server 2008.