Executing SQL Server Queries

Most of the modules in this course are geared towards constructing different types of queries. Once you construct a query, you will need to know how to send it to SQL Server for processing, also known as executing^[1] or issuing^[2] a query. Although you can take a complicated approach to issuing a query, such as writing a Visual Basic or Visual C++ program, the easiest way to execute a query is to use Query Analyzer, which is installed automatically with SQL Server 2000-2005. Query Analyzer was an important tool because it gave you the ability to interactively analyze the results of your queries and determine if the query could be optimized^[3].

The Query Analyzer (QA) was the query tool provided with SQL Server up until SQL Server 2000. In SQL Server 2005 and above it was superceded by (SSMS) SQL Server Management Studio. If you would like to detect any performance problems in some of your queries you can use the execution plan to determine what is causing the performance problem.

The SQL Server Management Studio is pretty much home base when administering a SQL Server. It provides a variety of functionality for managing your server using a relatively easy-to-use graphical user interface. Branched off of the code base of Visual Studio IDE, it combines a myriad of functionality that used to be in separate tools. For the purposes of this course, I am not going to cover everything that the Management Studio has to offer, but here is a quick rundown of the things you can do:

1. Create, edit, and delete databases and database objects
2. Query your database using T-SQL
3. Manage scheduled tasks, such as backups and the execution of SSIS package runs
4. Display current activity, such as who is logged on, what objects are locked, and from whichclient they are running
5. Manage security, including such items as roles, logins, and remote and linked servers
6. Initiate and manage the Database Mail Service
7. Create and manage full-text search catalogs
8. Manage confi guration settings for the server
9. Initiate an instance of the new PowerShell console
10. Create and manage publishing and subscribing databases for replication
11. Manage data processing in cubes or tabular databases for SQL Server Analysis Services (SSAS)
12. Create queries against a multidimensional SSAS database in MDX, or against a tabular SSAS database in DAX

All of the query lessons in this course assume that you are not using Query Analyzer to issue your queries since it has been deprecated since SQL-Server 2005. In the next lesson, batch queries and how they compare to single queries will be discussed.
In SQL Server 2008 and later, the following is used to analyze a query in place of Query Analyzer.

The SQL Server Database Engine can display how it navigates tables and uses indexes to access or process the data for a query or other DML statement, such as an update. This is a display of an execution plan. To analyze a slow-running query, it is useful to examine the query execution plan to determine what is causing the problem. For more information about how SQL Server creates and uses execution plans, see SQL Statement

You can display execution plans by using the following methods:

1. SQL Server Management Studio : Displays either an estimated graphical execution plan (statements do not execute) or an actual graphical execution plan (on executed statements), which you can save and view in Management Studio.
2. Transact-SQL SET statement options: When you use the Transact-SQL SET statement options, you can produce estimated and actual execution plans in XML or text.
3. SQL Server Profiler event classes: You can select SQL Server Profiler event classes to include in traces that produce estimated and actual execution plans in XML or text in the trace results.

[1]Executing: The process of sending a query to SQL Server for processing. This is synonymous with Issuing.

[2]Issuing: The process of sending a query to SQL Server for processing. This is synonymous with Executing.

[3]Optimized: The process of determining the fastest possible way to execute a query. Optimization can be simple or complex, depending on the complexity of the query or stored procedure being issued.