SQL Execution Plan

Courtesy From:Grant Fritchey

What is an execution plan?

Every day, out in the various discussion boards devoted to Microsoft SQL Server, the same types of questions come up again and again: Why is this query running slow? Is my index getting used? Why isn’t my index getting used? Why does this query run faster than this query?. The correct response is probably different in each case, but in order to arrive at the answer you have to ask the same return question in each case: have you looked at the execution plan? An execution plan, simply put, is the result of the query optimizer’s attempt to calculate the most efficient way to imple­ment the request represented by the T-SQL query you sub­mitted.

Execution plans can tell you how a query will be executed, or how a query was executed. They are, therefore, the DBA’s primary means of troubleshooting a poorly performing query. Rather than guess at why a given query is performing thousands of scans, putting your I/O through the roof, you can use the execution plan to identify the exact piece of SQL code that is causing the problem. For example, it may be scanning an entire table-worth of data when, with the proper index, it could simply backpack out only the rows you need. All this and more is displayed in the execution plan.

The aim of this chapter is to enable you to capture actual and estimated execution plans, in either graphical, text or XML for­mat, and to understand the basics of how to interpret them. In order to do this, we’ll cover the following topics:

A brief backgrounder on the query optimizer – execution plans are a result of the optimizer’s calculations so it’s useful to know at least a little bit about what the optimizer does, and how it works

Actual and Estimated execution plans – what they are and how they differ

Capturing and interpreting the different visual execution plan formats – we’ll investigate graphical, text and XML execution plans for a very basic SELECT query

Automating execution plan capture – using the SQL Server Profiler tool

What Happens When a Query is submitted?

When you submit a query to a SQL Server database, a number of processes on the server go to work on that query. The purpose of all these processes is to manage the system such that it will provide your data back to you, or store it, in as timely a manner as possible, whilst maintaining the integrity of the data.

These processes are run for each and every query submitted to the system. While there are lots of different actions occurring simultaneously within SQL Server, we’re going to focus on the processes around T-SQL. They break down roughly into two stages:

1. Processes that occur in the relational engine

2. Processes that occur in the storage engine.

In the relational engine the query is parsed and then processed by the Query Optimizer , which generates an execution plan. The plan is sent (in a binary format) to the storage engine, which it then uses to retrieve or update the underlying data. The storage engine is where processes such as locking, index maintenance and transactions occur. Since execution plans are created in the relational engine, that’s where we’ll be focusing our attention.

Query Parsing

When you pass a T-SQL query to the SQL Server system, the first place it goes to is the rel­ation­al engine. [1]

As the T-SQL arrives, it passes through a process that checks that the T-SQL is written correctly, that it’s well formed. This process is known as query parsing. The output of the Parser process is a parse tree, or query tree (or even sequence tree). The parse tree represents the logical steps necessary to execute the query that has been requested.

If the T-SQL string is not a data manipulation language (DML) statement, it will be not be optimized because, for example, there is only one “right way” for the SQL Server system to create a table; therefore, there are no opportunities for improving the performance of that type of statement. If the T-SQL string is a DML statement, the parse tree is passed to a process called the algebrizer . The algebrizer resolves all the names of the various objects, tables and columns, referred to within the query string. The algebrizer identifies, at the individual column level, all the types (varchar(50) versus nvarchar(25) and so on) of the objects being accessed. It also determines the location of aggregates (such as GROUP BY, and MAX) within the query, a process called aggregate binding. This algebrizer process is important because the query may have aliases or synonyms, names that don’t exist in the database, that need to be resolved, or the query may refer to objects not in the database.

The algebrizer outputs a binary called the query processor tree , , which is then passed on to the query optimizer.

The Query Optimizer

The query optimizer is essentially a piece of software that “models” the way in which the database relational engine works. Using the query processor tree and the statistics it has about the data, and applying the model, it works out what it thinks will be the optimal way to execute the query – that is, it generates an execution plan.

In other words, the optimizer figures out how best to implement the request represented by the T-SQL query you submitted. It decides if the data can be accessed through indexes, what types of joins to use and much more. The decisions made by the optimizer are based on what it calculates to be the cost of a given execution plan, in terms of the required CPU processing and I/O, and how fast it will execute. Hence, this is known as a cost-based plan.

The optimizer will generate and evaluate many plans (unless there is already a cached plan) and, generally speaking, will choose the lowest-cost plan i.e. the plan it thinks will execute the query as fast as possible and use the least amount of resources, CPU and I/O. The calculation of the execution speed is the most important calculation and the optimizer will use a process that is more CPU-intensive if it will return results that much faster. Sometimes, the optimizer will select a less efficient plan if it thinks it will take more time to evaluate many plans than to run a less efficient plan.

If you submit a very simple query – for example, a single table with no indexes and with no aggregates or calculations within the query – then rather than spend time trying to calculate the absolute optimal plan, the optimizer will simply apply a single, trivial plan to these types of queries.

If the query is non-trivial, the optimizer will perform a cost-based calculation to select a plan. In order to do this, it relies on statistics that are maintained by SQL Server.

Statistics are collected on columns and indexes within the database, and describe the data distribution and the uniqueness, or selectivity of the data. The information that makes up statistics is represented by a histogram, a tabulation of counts of the occurrence of a particular value, taken from 200 data points evenly distributed across the data. It’s this “data about the data” that provides the information necessary for the optimizer to make its calculations.

If statistics exist for a relevant column or index, then the optimizer will use them in its calculations. Statistics, by default, are created and updated automatically within the system for all indexes or for any column used as a predicate, as part of a WHERE clause or JOIN ON clause. Table variables do not ever have statistics generated on them, so they are always assumed by the optimizer to have a single row, regardless of their actual size. Temporary tables do have statistics generated on them and are stored in the same histogram as permanent tables, for use within the optimizer.

The optimizer takes these statistics, along with the query processor tree , and heuristically determines the best plan. This means that it works through a series of plans, testing different types of join, rearranging the join order, trying different indexes, and so on, until it arrives at what it thinks will be the fastest plan. During these calculations, a number is assigned to each of the steps within the plan, representing the optimizer’s estimation of the amount of time it thinks that step will take. This shows what is called the estimated cost for that step. The accumulation of costs for each step is the cost for the execution plan itself.

It’s important to note that the estimated cost is just that – an estimate. Given an infinite amount of time and complete, up-to-date statistics, the optimizer would find the perfect plan for executing the query. However, it attempts to calculate the best plan it can in the least amount of time possible, and is obviously limited by the quality of the statistics it has available. Therefore these cost estimations are very useful as measures, but may not precisely reflect reality.

Once the optimizer arrives at an execution plan, the actual plan is created and stored in a memory space known as the plan cache – unless an identical plan already exists in the cache (more on this shortly, in the section on Execution Plan Reuse ). As the optimizer generates potential plans, it compares them to previously generated plans in the cache. If it finds a match, it will use that plan.

Query Execution

Once the execution plan is generated, the action switches to the storage engine, where the query is actually executed, according to the plan.

We will not go into detail here, except to note that the carefully generated execution may be subject to change during the actual execution process. For example, this might happen if:

• · A determination is made that the plan exceeds the threshold for a parallel execution (an execution that takes advantage of multiple processors on the machine – more on parallel execution in the book).
• · The statistics used to generate the plan were out of date, or have changed since the original execution plan was created by the optimizer.

The results of the query are returned to you after the relational engine changes the format to match that requested in your T-SQL statement, assuming it was a SELECT.

Estimated and Actual Execution Plan s

As discussed previously, there are two distinct types of execution plan. First, there is the plan that represents the output from the optimizer. This is known as an Estimated execution plan. The operators, or steps, within the plan will be labeled as logical, because they’re representative of the optimizer’s view of the plan.

Next is the plan that represents the output from the actual query execution. This type of plan is known, funnily enough, as the Actual execution plan. It shows what actually happened when the query executed.

Execution Plan Reuse

It is expensive for the Server to generate execution plans so SQL Server will keep and reuse plans wherever possible. As they are created, plans are stored in a section of memory called the plan cache ).

When a query is submitted to the server, an estimated execution plan is created by the optimizer. Once that plan is created, and before it gets passed to the storage engine, the optimizer compares this estimated plan to actual execution plans that already exist in the plan cache . If an actual plan is found that matches the estimated one, then the optimizer will reuse the existing plan, since it’s already been used before by the query engine. This reuse avoids the overhead of creating actual execution plans for large and complex queries or even simple plans for small queries called thousands of times in a minute.

Each plan is stored once, unless the cost of the plan lets the optimizer know that a parallel execution might result in better performance (more on parallelism in Chapter 8). If the optimizer sees parallelism as an option, then a second plan is created and stored with a different set of operations to support parallelism. In this instance, one query gets two plans.

Execution plans are not kept in memory forever. They are slowly aged out of the system using an “age” formula that multiplies the estimated cost of the plan by the number of times it has been used (e.g. a plan with a cost of 10 that has been referenced 5 times has an “age” value f of 50). The lazywriter process, an internal process that works to free all types of cache (including plan cache ), periodically scans the objects in the cache and decreases this value by one each time.

If the following criteria are met, the plan is removed from memory:

• · More memory is required by the system
• · The “age” of the plan has reached zero
• · The plan isn’t currently being referenced by an existing connection

Execution plans are not sacrosanct. Certain events and actions can cause a plan to be recompiled. It is important to remember this because recompiling execution plans can be a very expensive operation. The following actions can lead to recompilation of an execution plan:

• · Changing the structure or schema of a table referenced by the query
• · Changing an index used by the query
• · Dropping an index used by the query
• · Updating the statistics used by the query
• · Calling the function, sp_recompile
• · Subjecting the keys in tables referenced by the query to a large number of inserts or deletes
• · For tables with triggers, significant growth of the inserted or deleted tables
• · Mixing DDL and DML within a single query, often called a deferred compile
• · Changing the SET options within the execution of the query
• · Changing the structure or schema of temporary tables used by the query
• · Changes to dynamic views used by the query
• · Changes to cursor options within the query
• · Changes to a remote rowset, like in a distributed partitioned view
• · When using client side cursors, if the FOR BROWSE options are changed

Since the cache plays such an important role in how execution plans operate, you need a few tools for querying and working with the plan cache . First off, while testing, you may want to see how long a plan takes to compile, or to investigate how minor adjustments might create slightly different plans. To completely clear the cache, run this:

DBCC FREEPROCCACHE

You’re going to want to see the objects within the cache in order to see how the optimizer and storage engine created your plan. With dynamic management views and dynamic management functions, we can easily put together a query to get a very complete set of information about the execution plans on our system:

SELECT [cp].[refcounts]
, [cp].[usecounts]
, [cp].[objtype]
, [st].[dbid]
, [st].[objectid]
, [st].[text]
, [qp].[query_plan]
FROM sys.dm_exec_cached_plans cp
CROSS APPLY sys.dm_exec_sql_text ( cp.plan_handle ) st
CROSS APPLY sys.dm_exec_query_plan ( cp.plan_handle ) qp ;

With this query we can see the SQL called and the XML plan generated by the execution of that SQL. You can use the XML directly or open it as a graphical execution plan.

Why the Actual and Estimated Execution Plan s Might Differ

Generally, you probably won’t see any differences between your esti­mated and actual execution plans. However, circum­stances can arise that can cause differences between the esti­mated and actual execution plans.

When Statistics are Stale

The main cause of a difference between the plans is differ­ences between the statistics and the actual data. This gen­er­ally occurs over time as data is added and deleted. This causes the key values that define the index to change, or their distribution (how many of what type) to change. The automatic update of statistics that occurs, assuming it’s turned on, only samples a subset of the data in order to reduce the cost of the operation. This means that, over time, the statistics become a less-and-less accurate reflection of the actual data. Not only can this cause diff­er­ences between the plans, but you can get bad execution plans because the statistical data is not up to date. [2]

When the Estimated Plan is Invalid

In some instances, the estimated plan won’t work at all. For example, try generating an estimated plan for this simple bit of code:

CREATE TABLE TempTable

(

Id INT IDENTITY (1 , 1 )

,Dsc NVARCHAR (50 )

);

INSERT INTO TempTable ( Dsc )

SELECT [Name]

FROM [Sales] .[Store] ;

SELECT *

FROM TempTable ;

DROP TABLE TempTable ;

You will get this error:

Msg 208, Level 16, State 1, Line 7

Invalid object name ‘TempTable’.

The optimizer, which is what is used to generate Estimated Execution plans, doesn’t execute T-SQL. It does run the state­ments through the algebrizer , the process outlined earlier that is responsible for verifying the names of database objects. Since the query has not yet been executed, the temporary table does not yet exist. This is the cause of the error. Running this same bit of code through the Actual execution plan will work perfectly fine.

When Parallelism is Requested

When a plan meets the threshold for parallelism (more about this in Chapter two plans are created. Which plan is actually executed is up to the query engine. So you might see a plan with, or without, parallel operators in the estimated execution plan. When the query actually executes, you may see a completely different plan if the query engine determines that it either can’t support a parallel query at that time or that a parallel query is called for.

Execution Plan Format s

SQL Server offers only one type of execution plan (be it estimated or actual), but three different formats in which to view that execution plan.

• · Graphical Plans
• · Text Plans
• · XML Plan s

The one you choose will depend on the level of detail you want to see, and on the individual DBA’s preferences and meth­ods.

Graphical Plans

These are quick and easy to read but the detailed data for the plan is masked. Both Estimated and Actual execution plans can be viewed in graphical format.

Text Plans

These are a bit harder to read, but more information is immediately available. There are three text plan formats:

• · SHOWPLAN_ALL : a reasonably complete set of data showing the Estimated execution plan for the query
• · SHOWPLAN_TEXT : provides a very limited set of data for use with tools like osql.exe . It too only shows the Estimated execution plan
• · STATISTICS PROFILE: similar to SHOWPLAN_ALL except it represents the data for the Actual execution plan

XML Plan s

XML plans present the most complete set of data available on a plan, all on display in the structured XML format. There are two varieties of XML plan:

• · SHOWPLAN_XML : The plan generated by the optimizer prior to execution.
• · STATISTICS_XML : The XML format of the Actual execution plan.

Getting Started

Execution plans are there to assist you in writing efficient T-SQL code, troubleshooting existing T-SQL behavior or monitoring and reporting on your systems. How you use them and view them is up to you, but first you need to understand the information contained within the plans and how to interpret it. One of the best ways to learn about execution plans is to see them in action, so let’s get started.

Please note that occasionally, especially when we move on to more complex plans, the plan that you see may differ slightly from the one presented in the book. This might be because we are using different versions of SQL Server (different SP levels and hot fixes), that we are using slightly different versions of the AdventureWorks database, or because of how the AdventureWorks database has been altered over time as each of us has played around in it. So while most of the plans you get should be very similar to what we display here, don’t be too surprised if you try the code and see something different

Sample Code

Throughout the following text, I’ll be supplying T-SQL code that you’re encouraged to run for yourself. All of the source code is freely downloadable from the Simple Talk Publishing website (http://www.simpletalkpublishing.com).

The examples are written for SQL 2005 sample database, Adventureworks. You can get hold of get a copy of Adventureworks from here:

http://www.codeplex.com/MSFTDBProdSamples

If you are working with procedures and scripts other than those supplied, please remember that encrypted procedures will not display an execution plan.

The plans you see may not precisely reflect the plans generated for the book. Depending on how old a given copy of AdventureWorks may be, the statistics could be different, the indexes may be different, the structure and data may be different. So please be aware that you won’t always see the same thing if you run the examples.

The initial execution plans will be simple and easy to read from the samples presented in the text. As the queries and plans become more complicated, the book will describe the situation but, in order to easily see the graphical execution plans or the complete set of XML, it will be necessary to generate the plans. So, please, read next to your machine, so that you can try running each query yourself!

Permissions Required to View Execution Plans

In order to see the execution plans for the following queries you must have the correct permissions within the database. Once that’s set, assuming you’re not sysadmin, dbcreator or db_owner, you’ll need to be granted the ShowPlan permission within the database being tested. Further, you’ll need this permission on each database referenced by the queries for which you hope to generate a plan. Run the statement:

GRANT SHOWPLAN TO [username]

Substituting the user name will enable execution plans for that user on that database.

Working with Graphical Execution Plans

In order to focus on the basics of capturing Estimated and Actual execution plans, the first query will be one of the simplest possible queries, and we’ll build from there. Open up Management Studio, and type the following into the query window:

SELECT *
FROM [dbo].[DatabaseLog];

Getting the Estimated Plan

We’ll start by viewing the graphical estimated execution plan that is generated by the query optimizer, so there’s no need to actually run the query yet.

We can find out what the optimizer estimates to be the least costly plan in one of following ways:

• · Click on the “Display Estimated Execution Plan icon on the tool bar.
• · Right-click the query window and select the same option from the menu.
• · Click on the Query option in the menu bar and select the same choice.
• · Simply hit CTRL-L on the keyboard.

I tend to click the icon more often than not but, either way, we see our very first Estimated execution plan, as in Figure 1.

Figure 1

We’ll explain what this plan means shortly, but first, let’s capture the Actual execution plan.

Getting the Actual Plan

Actual execution plans, unlike Estimated execution plans, do not represent the calculations of the optimizer. Instead this execution plan shows what happened when the query was executed. The two will often be identical but will sometimes differ, due to changes to the execution plan made by the storage engine.

Again, there are several ways to generate our first graphical Actual Execution Plan :

• · Click on the icon on the tool bar called “Include Actual Execution Plan
• · Right-click within the query window and choose the “Include Actual Execution Plan menu item.
• · Choose the same option in the Query menu choice.
• · Type Control-M.

Each of these methods functions as an “on” switch and an execution plan will be created for all queries run from that query window until you turn it off again.

So, activate execution plans by your preferred method and execute the query. You should see an execution plan like the one in Figure 2.

Figure 2

In this simple case the Actual plan is identical to the Estima­ted plan.

Interpreting Graphical Execution Plan

The icons you see in Figures 1 and 2 are the first two of app­rox­imately 78 operators that represent various actions and decis­ions that potentially make up an execution plan. On the left is the SELECT icon, an icon that you’ll see quite a lot of and that you can usually completely ignore. It’s the final result and formatting from the relational engine. The icon on the right represents a table scan [3]. This is the first, and one of the easiest, icons to look for when trying to track down performance problems.

Usually, you read a graphical execution plan from right to left and top to bottom. You’ll also note that there is an arrow pointing between the two icons. This arrow represents the data being passed between the operators, as represented by the icons. So, in this case, we simply have a table scan operator producing the result set (represented by the Select operator). The thickness of the arrow reflects the amount of data being passed, thicker meaning more rows. This is another visual clue as to where performance issues may lie. You can hover with the mouse pointer over these arrows and it will show the number of rows that it represents. For example, if your query returns two rows, but the execution plan shows a big thick arrow indicating many rows being processed, then that’s something to possibly investigate.

Below each icon is displayed a number as a percentage. This number represents the relative cost to the query for that operator. That cost, returned from the optimizer, is the estimated execution time for that operation. In our case, all the cost is associated with the table scan. While a cost may be represented as 0% or 100%, remember that, as these are ratios, not actual numbers, even a 0% operator will have a small cost associated with it.

Above the icons is displayed as much of the query string as will fit and a “cost (relative to batch)” of 100%. Just as each query can have multiple steps, and each of those steps will have a cost relative to the query, you can also run multiple queries within a batch and get execution plans for them. They will then show up as different costs as a part of the whole.

ToolTips

Each of the icons and the arrows has, associated with it, a pop-up window called a ToolTip, which you can access by hovering your mouse pointer over the icon.

Pull up the Estimated execution plan, hover over the SELECT operator, and you should see the ToolTip window shown in Figure 3.

Figure 3

Here we get the numbers generated by the optimizer on the following:

• · Cached plan size – how much memory the plan generated by this query will take up in stored procedure cache . This is a useful number when investigating cache performance issues because you’ll be able to see which plans are taking up more memory.
• · Estimated Operator Cost – we’ve already seen this as the percentage cost in Figure 1.
• · Estimated Subtree Cost – tells us the accumulated optimizer cost assigned to this step and all previous steps, but remember to read from right to left. This number is meaningless in the real world, but is a mathematical evaluation used by the query optimizer to determine the cost of the operator in question; it represents the amount of time that the optimizer thinks this operator will take.
• · Estimated number of rows – calculated based on the statistics available to the optimizer for the table or index in question.

Below this information, we see the statement that represents the entire query that we’re processing. If we look at the ToolTip information for the Table Scan we see the information in Figure 4.

Figure 4

Each of the different operators will have a distinct set of data. The operator in Figure 4 is performing work of a different nature than that in Figure 3, and so we get a different set of details. First, the Physical and Logical Operations are listed. The logical operators are the results of the optimizer’s calculations for what should happen when the query executes. The physical operators represent what actually occurred. The logical and physical operators are usually the same, but not always – more on that in the book.

After that, we see the estimated costs for I/O, CPU, Operator and Subtree. The Subtree is simply the section of the execution tree that we have looked at so far, working right to left again, and top to bottom. All estimations are based on the statistics available on the columns and indexes in any table.

The I/O Cost and CPU cost are not actual operators, but rather the cost numbers assigned by the Query Optimizer during its calculations. These numbers are useful when determining whether most of the cost is I/O-based (as in this case), or if we’re putting a load on the CPU. A bigger number means more processing in this area. Again, these are not hard and absolute numbers, but rather pointers that help to suggest where the actual cost in a given operation may lie.

You’ll note that, in this case, the operator cost and the subtree cost are the same, since the table scan is the only operator. For more complex trees, with more operators, you’ll see that the cost accumulates as the individual cost for each operator is added to the total. You get the full cost of the plan from the final operation in the query plan, in this case the Select operator.

Again we see the estimated number of rows. This is displayed for each operation because each operation is dealing with different sets of data. When we get to more complicated execution plans, you’ll see the number of rows change as various operators perform their work on the data as it passes between each operator. Knowing how the rows are added or filtered out by each operator helps you understand how the query is being performed within the execution process.

Another important piece of information, when attempting to troubleshoot performance issues, is the Boolean value displayed for Ordered. This tells you whether or not the data that this operator is working with is in an ordered state. Certain operations, for example, an ORDER BY clause in a SELECT statement, may require data to be placed in a particular order, sorted by a particular value or set of values. Knowing whether or not the data is in an Ordered state helps show where extra processing may be occurring to get the data into that state.

Finally, Node ID is the ordinal, which simply means numbered in order, of the node itself, interestingly enough numbered left to right, despite the fact that the operations are best read right to left.

All these details are available to help you understand what’s happening within the query in question. You’ll be able to walk through the various operators, observing how the subtree cost acc­umu­lates, how the number of rows changes, and so on. With these details you’ll be able to identify processes that are using excessive amounts of CPU or tables that need more indexes, or indexes that are not used, and so on.

Operator Properties

More information is available than that presented in the ToolTips. Right-click any icon within a graphical execution plan and select the “Properties” menu item to get a detailed list of information about that operation. Figure 5 shows the details from the original table scan.

Figure 5

Most of this information should be familiar, but some of it is new. Starting from the top, Defined Values displays the infor­mation that this operation adds to the process. These can be a part of the basic query, in this case the columns being selected, or they can be internally created values as part of the query processing, such as a flag used to determine referential integrity, or a placeholder for counts for aggregate functions.

Under the Defined Values we get a description of the operation and then some familiar Estimated Cost data. After that we see:

• · Estimated Rebinds and Rewinds, values which describe the number of times an Init() operator is called in the plan.
• · The Forced Index value would be True when a query hint is used to put a specific index to use within a query. SQL Server supplies the functionality in query hints as a way to give you some control over how a query is executed. Query hints are covered in detail in the book
• · NoExpandHint this is roughly the same concept as Forced Index, but applied to indexed views.

By expanding the Object property, you can see details on the object in question. The Output List property provides details of each of the output columns. You’ll also find out whether or not this operator is taking part in a parallel operation, (when multiple CPUs are used by one operator).

Working with Text Execution Plan

The graphical execution plans are very useful because they’re so easy to read. However, a lot of the data about the operators is not immediately visible to you. Some can be seen in a limited form in the ToolTip windows, and the complete set is available in the Properties window. Wouldn’t it be great if there was a way to see all that information at once?

In the case of really large queries with incredibly complex plans or large number of batch statements, wouldn’t it be handy to be able to search through for particular bits of information, table scans or the highest operator cost or something? Well, you can. Two methods exist: Text Execution Plan .

Microsoft is planning on deprecating Text Execution Plan , so we’ll cover them in relatively little detail.

Getting the Estimated Text Plan

To activate the text version of the Estimated text execution plan, simply issue the following command at the start of the query:

SET SHOWPLAN_ALL ON;

It’s important to remember that, with SHOWPLAN_ALL set to ON, execution information is collected for all subsequent T-SQL statements, but those statements are not actually executed. Hence, we get the estimated plan. It’s very important to remember to turn SHOWPLAN_ALL OFF after you have captured the information you require. If you forget, and submit a CREATE, UPDATE or DELETE statement with SHOWPLAN_ALL turned on, then those statements won’t be executed, and a table you might expect to exist, for example, will not.

To turn SHOWPLAN_ALL off, simply issue:

SET SHOWPLAN_ALL ;

We can also use the equivalent commands for SHOWPLAN_TEXT. The text-only show plan is meant for use with tools like osql.exe , where the result sets can be readily parsed and stored by a tool dealing with text values, as opposed to actual result sets, as the SHOWPLAN_ALL function does .

We focus only on SHOWPLAN_ALL here.

Getting the Actual Text Plan

In order to activate and deactivate the text version of the Actual execution plan, use:

SET STATISTICS PROFILE ON

And:

SET STATISTICS PROFILE OFF

Interpreting Text Plan

We’ll stick with the same basic query we used when discussing graphical plans, so execute the following:

GO
SELECT *
FROM [dbo].[DatabaseLog] ;
GO
SET SHOWPLAN_ALL OFF ;
GO

When you execute this query, the estimated plan is shown in the results pane. Here is the first column of the results:

Figure 6

This screen shot was trimmed to keep the text as readable as possible. The text plan generated roughly parallels the graphical plan. The first row is the SELECT statement that was submitted. The rows following are the physical operations occurring within the query plan. In or case that means one row i.e. the table scan.

As we progress and view more complex text plans, in the book, you’ll quickly realize that they are not as readily readable as the graphical plan. There’s also no easy route through the query, such as we have with the “read it right to left” approach in the graphical plans. You start in the middle and move outwards, helped by the indentation of the data and the use of pipe ( | ) to connect the statements parent to child.

In addition to the first column, the details that were hidden in the ToolTip or in the Properties window are displayed in a series of columns. Most of the information that you’re used to seeing is here, plus a little more. So, while the NodeId was available in the graphical plan, because of the nature of the graphical plan, nothing was required to identify the parent of a given node. In the SHOWPLAN_ALL we get a column showing the Parent NodeId. As you scan right you’ll see many other familiar columns, such as the TotalSubTreeCost , EstimateRows and so on. Some of the columns are hard to read, such as the Defined List (the values or columns introduced by this operation to the data stream), which is displayed as just a comma-separated list .

Working with XML Execution Plan

XML Plan s are the new and recommended way of displaying the execution plans in SQL Server 2005. They offer functionality not previously available.

Getting the Actual and Estimated XML Plan

In order to activate and deactivate the XML version of the Estimated execution plan, use:

SET SHOWPLAN_XML ON
…
SET SHOWPLAN_XML OFF

As for SHOWPLAN_ALL command is essentially an instruction not to execute any T-SQL statements that follow, but instead to collect execution plan information for those statements, in the form of an XML document. Again, it’s important to turn SHOWPLAN_XML off as soon as you have finished collecting plan information, so that subsequent T-SQL execute as intended.

For the XML version of the Actual plan, use:

SET STATISTICS XML ON
…
SET STATISTICS XML OFF

Interpreting XML Plan

Once again, let’s look at the same execution plan as we evaluated with the text plan.

GO

SET SHOWPLAN_XML ON ;

GO

SELECT *

FROM [dbo] .[DatabaseLog] ;

SET SHOWPLAN_XML OFF ;

GO

The result, in the default grid mode, is shown in figure 7:

Figure 7

The link is a pointer to an XML file located here:

\Microsoft SQL Server\90\Tools\Binn\schemas\sqlserver\20033\showplan\showplanxml.xsd

Clicking on this link opens the execution plan in XML format in a browser window within the SQL Server Management Studio (SSMS). You can view the output in text, grid or file (default is grid). You can change the output format from the Query | Results To menu option.

A lot of information is put at your fingertips with XML plans – much of which we won’t encounter here with our simple example, but will get to in later, more complex plans. Nevertheless, even this simple plan will give you a good feel for the XML format.

The results, even for our simple query, are too large to output here. I’ll go over them by reviewing various elements and attri­butes. The full schema is available here:

http://schemas.microsoft.com/sqlserver/2004/07/showplan/.

Listed first are the BatchSequence , Batch and Statements elements. In this example, we’re only looking at a single Batch and a single Statement, so nothing else is displayed. Next, like all the other execution plans we’ve reviewed so far, we see the query in question as part of the StmtSimple element. Within that, we receive a list of attributes of the statement itself, and some physical attributes of the QueryPlan :

< StmtSimpleStatementText=”SELECT * &#xD;&#xA; FROM [dbo].[DatabaseLog]; &#xD;&#xA;” StatementId=”1″ StatementCompId=”1″ StatementType=”SELECT” StatementSubTreeCost=”0.108154″ StatementEstRows=”389″ StatementOptmLevel=”TRIVIAL”>

< StatementSetOptions QUOTED_IDENTIFIER=”false” ARITHABORT=”true” CONCAT_NULL_YIELDS_NULL=”false” ANSI_NULLS=”false” ANSI_PADDING=”false” ANSI_WARNINGS=”false” NUMERIC_ROUNDABORT=”false” />

< QueryPlan CachedPlanSize=”9″>

Clearly a lot more information is on immediate display than was provided for SHOWPLAN_ALL . Notice that the optimizer has chosen a trivial execution plan, as we might expect. Information such as the CachedPlanSize will help you to determine if, for example, your query exceeds one page in length, and gets sent into the LeaveBehind

memory space .

After that, we have the RelOp element, which provides the information we’re familiar with, regarding a particular operation, in this case the table scan.

< RelOp NodeId=”0″ PhysicalOp=”Table Scan” LogicalOp=”Table Scan” EstimateRows=”389″ EstimateIO=”0.107569″ EstimateCPU=”0.0005849″ AvgRowSize=”8569″ EstimatedTotalSubtreeCost=”0.108154″ Parallel=”0″ EstimateRebinds=”0″ EstimateRewinds=”0″>

Not only is there more information than in the text plans, but it’s also much more readily available and easier to read than in either the text plans or the graphical plans (although the flow through the graphical plans is much easier to read). For example, a problematic column, like the Defined List mentioned earlier, that is difficult to read, becomes the OutputList element with a list of ColumnReference elements, each containing a set of attributes to describe that column:

<OutputList>

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”DatabaseLogID” />

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”PostTime” />

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”DatabaseUser” />

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”Event” />

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”Schema” />

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”Object” />

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”TSQL” />

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”XmlEvent” />

</ OutputList>

This makes XML not only easier to read, but much more readily translated directly back to the original query.

Back to the plan, after RelOpelement referenced above we have the table scan element:

< TableScan Ordered=”0″ ForcedIndex=”0″ NoExpandHint=”0″>

Followed by a list of defined values that lays out the columns referenced by the operation:

< DefinedValues>

< DefinedValue>

< ColumnReference Database=”[AdventureWorks]” Schema=”[dbo]” Table=”[DatabaseLog]” Column=”DatabaseLogID” />

</ DefinedValue>

< DefinedValue>

…<output cropped>……..

Saving XML Plans as Graphical Plan

You can save the execution plan without opening it by right-clicking within the results and selecting “Save As.” You then have to change the filter to “*.*” and when you type the name of the file you want to save add the extension “.sqlplan.” This is how the Books Online recommends saving an XML execution plan. In fact, what you get when you save it this way is actually a graphical execution plan file. This can actually be a very useful feature. For example, you might collect multiple plans in XML format, save them to file and then open them in easy-to-view (and compare) graphical format.

One of the benefits of extracting an XML plan and saving it as a separate file is that you can share it with others. For example, you can send the XML plan of a slow-running query to a DBA friend and ask them their opinion on how to rewrite the query. Once the friend receives the XML plan, they can open it up in Management Studio and review it as a graphical execution plan.

In order to actually save an XML plan as XML, you need to first open the results into the XML window. If you attempt to save to XML directly from the result window you only get what is on display in the result window. Another option is to go to the place where the plan is stored, as defined above, and copy it.

Automating Plan Capture Using SQL Server Profiler

During development you will capture execution plans for targeted T-SQL statements, using one of the techniques described in this chapter. You will activate execution plan capture, run the query in question, and then disable it again.

However, if you are troubleshooting on a test or live production server, the situation is different. A production system may be subject to tens or hundreds of sessions executing tens or hundreds or queries, each with varying parameter sets and varying plans. In this situation we need a way to automate plan capture so that we can collect a large number of plans simultaneously. In SQL Server 2005 you can use Profiler to capture XML execution plans, as the queries are executing. You can then examine the collected plans, looking for the queries with the highest costs, or simply searching the plans to find, for example, Table Scan operations that you’d like to eliminate.

SQL Server 2005 Profiler is a powerful tool that allows you to capture data about events, such as the execution of T-SQL or a stored procedure, occurring within SQL Server. Profiler events can be tracked manually, through a GUI interface, or traces can be defined through T-SQL (or the GUI) and automated to run at certain times and for certain periods.

These traces can be viewed on the screen or sent to or to a file or a table in a database. [4]

Execution Plan events

The various trace events that will generate an execution plan are as follow:

• · Showplan Text : This event fires with each execution of a query and will generate the same type of estimated plan as the SHOWPLAN_TEXT T-SQL statement. Showplan Text will work on SQL 2005 databases, but it only shows a subset of the information available to ShowPlan XML. We’ve already discussed the shortcomings of the text execution plans, and this is on the list for deprecation in the future.
• · Showplan Text (unencoded) : Same as above, but it shows the information as a string instead of binary. This is also on the list for deprecation in the future.
• · Showplan All : This event fires as each query executes and will generate the same type of estimated execution plan as the SHOWPLAN_ALL TSQL statement. This has the same shortcomings as Showplan Text, and is on the list for future deprecation.
• · Showplan All for Query Compile : This event generates the same data as the Showplan All event, but it only fires when a query compile event occurs. This is also on the list for deprecation in the future.
• · Showplan Statistics Profile : This event generates the actual execution plan in the same way as the TSQL command STATISTICS PROFILE. It still has all the shortcomings of the text output, including only supplying a subset of the data available to STATISTICS XML in TSQL or the Showplan XML Statistics Profile event in SQL Server Profiler . The Showplan Statistics Profile event is on the list for deprecation.
• · Showplan XML : The event fires with each execution of a query and generates an estimated execution plan in the same way as SHOWPLAN_XML .
• · Showplan XML For Query Compile : Like Showplan XML above, but it only fires on a compile of a given query.
• · Performance Statistics : Similar to the Showplan XML For Query Compile event, except this event captures performance metrics for the query as well as the plan. This only captures XML output for certain event subclasses, defined with the event. It fires the first time a plan is cached, compiled, recompiled or removed from cache.
• · Showplan XML Statistics Profile : This event will generate the actual execution plan for each query, as it runs.

Capturing all of the execution plans, using Showplan XML or Showplan XML Statistics Profile, inherently places a sizeable load on the server. These are not lightweight event capture scenarios. Even the use of the less frequent Showplan XML for Query Compile will cause a small performance hit. Use due diligence when running traces of this type against any production machine.

Capturing a Showplan XML Trace

The SQL Server 2005 Profiler Showplan XML event captures the XML execution plan used by the query optimizer to execute a query. To capture a basic Profiler trace, showing estimated execution plans, start up Profiler, create a new trace and connect to a server [5].

Switch to the “Events Selection” tab and click on the “Show all events” check box. The Showplan XML event is located within the Performance section, so click on the plus (+) sign to expand that selection. Click on the Showplan XML event.

While you can capture the Showplan XML event by itself in Profiler, it is generally more useful if you capture it along with some other basic events, such as:

• · RPC: Completed
• · SQL:BatchStarting
• · SQL:BatchCompleted

 

Figure 8

These extra events provide additional information to help put the XML plan into context. For example, you can see what occurred just before and after the event you are interested in.

Once Showplan XML is selected, or any of the other XML events, a third tab appears called Events Extraction Settings . On this tab, you can choose to output the XML as it’s generated to a separate file, for later use. Not only can you define the file, but also determine whether or not all the XML will go into a single file or a series of files, unique to each execution plan.

Figure 9

Click on the “Run” button in order to start the trace. When you capture the above events, you get a trace like the one shown in Figure 10.

Figure 10

Notice that I have clicked on the Showplan XML event. Under the TextData column, you see the actual XML plan code. While you can’t see all of it in the screen shot above, it is all there and can be saved to an individual file. In the second window, you can see the graphical execution plan, which is how most people prefer to read and analyze execution plans. So, in effect, the Showplan XML event available in Profiler not only shows you the XML plan code, but also the graphical execution plan.

At this stage, you can also save the code for this particular Showplan XML event to a separate file. Simply right-click on the Showplan XML event you want to save, then select “Extract Event Data.”

Figure 11

This brings up a dialog box where you can enter the path and filename of the XML code you want to store. Instead of storing the XML code with the typical XML extension, the extension used is .SQLPlan. By using this extension, when you double-click on the file from within Windows Explorer, the XML code will open up in Management Studio in the form of a graphical execution plan.

Whether capturing Estimated execution plans or Actual execution plans, the Trace events operate in the same manner as when you run the T-SQL statements through the query window within Management Studio. The main difference is that this is automated across a large number of queries, from ad-hoc to stored procedures, running against the server.

Summary

In this article we’ve approached how the optimizer and the storage engine work together to bring data back to your query. These operations are expressed in the estimated execution plan and the actual execution plan. You were given a number of options for obtaining either of these plans, graphically, output as text, or as XML. Either the graphical plans or the XML plans will give you all the data you need, but it’s going to be up to you to decide which to use and when based on the needs you’re addressing and how you hope to address them.

Google Advanced Operators Cheat Sheet

Basic Examples
This Search		Finds Pages Containing…
biking Italy		the words biking and Italy
recycle steel OR iron		information on recycling steel or recycling iron
“I have a dream“		the exact phrase I have a dream
salsa –dance		the word salsa but NOT the word dance
Louis +I France		information about Louis the First (I), weeding out other kings of France
castle ~glossary		glossaries about castles, as well as dictonaries, lists of terms, terminology, etc.
fortune-telling		all forms of the term, whether spelled as a single word, a phrase, or hyphenated
define:imbroglio		definitions of the word imbroglio from the Web
“Nancy * Blachman“		the words Nancy and Blachman separated by exactly one word

<!–

–>

Calculator
Operators		Meaning		Type Into Search Box (& Results)
+ – * /		basic arithmetic		12 + 34 – 56 * 7 / 8
% of		percentage of		45% of 39
^ or **		raise to a power		2^5 or 2**5
the nth root of		nth root		4th root of 16
old units in new units		convert units		300 Euros in USD, 130 lbs in kg, or 31 in hex

Restrict Search
Operators		Meaning		Type Into Search Box (& Results)
city1 city2		Book flights.		sfo bos (Book flights from San Francisco (SFO) to Boston (BOS).)
site:		Search only one website or domain.		Halloween site:www.census.gov (Search for information on Halloween gathered by the US Census Bureau.)
[#]..[#]		Search within a range of numbers.		Dave Barry pirate 2002..2006 (Search for Dave Barry articles mentioning pirates written in these years.)
filetype: (or ext:)		Find documents of the specified type.		Form 1098-T IRS filetype:pdf (Find the US tax form 1098-T in PDF format.)
link:		Find linked pages, i.e., show pages that point to the URL.		link:warriorlibrarian.com (Find pages that link to Warrior Librarian’s website.)

Specialized Information Queries
Operators		Meaning		Type Into Search Box (& Results)
book (or books)		Search full-text of books.		book Ender’s Game (Show book-related information. Note: No colon needed after book.)
define, what is, what are		Show a definition for a word or phrase.		define monopsony, what is podcast (Show a definition for the words monopsony and podcast. Note: No colon after define, what is, or what are.)
define:		Provide definitions for words, phrases, and acronyms from the Web.		define:kerning (Find definitions for kerning from the Web.)
phonebook:		Show all phonebook listings.		phonebook: Disney CA (Search for Disney’s phone numbers in California - CA.)
rphonebook:		Show residential phonebook listings.		rphonebook: bill jones NY (Search for the phone number of every Bill Jones in New York State - NY.)
movie:		Find reviews and showtimes.		movie: traffic (Search for information about this movie, including reviews, showtimes, etc.)
stocks:		Given ticker symbols, show stock information		stocks: goog (Find Google’s current stock price.)
weather		Given a location (US zip code or city), show the weather		weather Seattle WA, weather 81612 (Show the current weather and forecast. Note: No colon after weather.)

Alternative Query Types
Operators		Meaning		Type Into Search Box (& Results)
cache:		Display Google’s cached version of a web page.		cache:www.irs.gov (Show Google’s cached version of the US Internal Revenue Service home page.)
info: (or id:)		Find info about a page.		info:www.theonion.com (Find information about The Onion website.)
related:		List web pages that are similar or related to the URL.		related:www.healthfinder.gov (Find websites related to the Healthfinder website.)

Restrict Search to Sites where Query Words Appear
Operators		Meaning		Type Into Search Box (& Results)
allinanchor:		All query words must appear in anchor text of links to the page.		allinanchor:useful parenting sites (Search for pages that are called useful parenting sites by others.)
inanchor:		Terms must appear in anchor text of links to the page.		restaurants Portland inanchor:kid-friendly (Search for pages on Portland restaurants for which links to the page say they are “kid friendly.”)
allintext:		All query words must appear in the text of the page.		allintext:ingredients cilantro chicken lime (Search for recipes with these three ingredients.)
intext:		The terms must appear in the text of the page.		Dan Shugar intext:Powerlight (Find pages mentioning Dan Shugar where his company, Powerlight, is included in the text of the page, i.e., less likely to be from the corporate website.)
allintitle:		All query words must appear in the title of the page.		allintitle: Google Advanced Operators (Search for pages with titles containing “Google,” “Advanced,”, and “Operators”.)
intitle:		The terms must appear in the title of the page.		movies comedy intitle:top ten (Search for pages with the words movie and comedy that include top ten in the title of the page.)
allinurl:		All query words must appear in the URL.		allinurl:pez faq (Search for pages containing the words pez & faq in the URL.)
inurl:		The terms must appear in the URL of the page.		pharmaceutical inurl:investor (Search for pages in which the URL contains the word investor.)

Restrict Search to Google Groups
Operators		Meaning		Type Into Search Box (& Results)
author:		Find Groups messages from the specified author.		flying author:Hamish author:Reid (Search for Hamish Reid’s articles on flying.)
group:		Find Groups messages from the specified newsgroup.		ivan doig group:rec.arts.books (Search for postings about Ivan Doig in the group rec.arts.books.)
insubject:		Find Groups messages containing crazy quilts in the subject.		insubject:”crazy quilts” (Find articles containing crazy quilts in the subject line.)

Restrict Search to Google News
Operators		Meaning		Type Into Search Box (& Results)
location:		Find News articles from sources located in the specified location.		queen location:uk (Find British news articles on the Queen.)
source:		Find News articles from specified sources.		peace source:ha_aretz (Show articles on peace from the Israeli newspaper Ha’aretz.)

Restrict Search to Froogle
Operators		Meaning		Type Into Search Box (& Results)
store:		Find Froogle products from the specified store.		jeans store:gap (Find Gap jeans.)

Top 10 Obscure Google Search Tricks

 

10. Get the local time anywhere

What time is it in Bangkok right now? Ask Google. Enter simply what time is it to get the local time in big cities around the world, or add the locale at the end of your query, like what time is it hong kong to get the local time there.

9. Track flight status

Enter the airline and flight number into the Google search box and get back the arrival and departure times right inside Google’s search results.

8. Convert currency, metrics, bytes, and more

Google’s powerful built-in converter calculator can help you out whether you’re cooking dinner, traveling abroad, or building a PC. Find out how many teaspoons are in a quarter cup (quarter cup in teaspoons) or how many seconds there are in a year (seconds in a year) or how many euros there are to five dollars (5 USD in Euro). For the geekier set, bits in kilobytes (155473 bytes in kilobytes) and numbers in hex or binary (19 in binary) are also pretty useful.

7. Compare items with “better than” and find similar items with “reminds me of”

Reader Adam taps the wisdom of the crowds by searching for like items using key phrases. He writes in:

Simply search for, in quotes: “better than _keyword_”Some example results:

Results 1 – 100 of about 550 English pages for ” better than WinAmp”.

Results 1 – 57 of 57 English pages for ” better than mIRC”.

Results 1 – 100 of about 17,500 English pages for ” better than Digg”. (Wow. Poor Digg.)

The results will almost always lead you to discovering alternatives to whatever it is you’re searching for. Using the same concept, you can use this trick to discover new music or movies. For example, ” reminds me of _someband_” or “sounds like _someband_” will pull up artists people have thought sounded similar to the one you typed in. This is also a great way to find good, no-name musicians you’d probably never know of otherwise.

Examples:

Results 1 – 88 of 88 English pages for ” reminds me of Metallica”.

Results 1 – 36 of 36 English pages for ” similar to Garden State”.

Results 1 – 66 of 66 English pages for ” sounds like The Shins”.

Just get creative and you’ll, without a doubt, find cool new stuff you probably never knew existed.

6. Use Google as a free proxy

What, your company blocks that hip new web site just because it drops the F bomb occasionally? Use Google’s cache to take a peek even when the originating site’s being blocked, with cache:example.com.

5. Remove affiliate links from product searches

When you’re sick of seeing duplicate product search results from the likes of eBay, Bizrate, Pricerunner, and Shopping.com, clear ‘em out by stacking up the -site:ebay.com -site:bizrate.com -site:shopping.com operator. Alternately, check out Give Me Back My Google (original post), a service that does all that known reseller cleaning up for you when you search for products. Compare this GMBMG search for a Cruzer 1GB flash drive to the regular Google results.

4. Find related terms and documents

Ok, this one’s direct from any straight-up advanced search operator cheat sheet, but it’s still one of the lesser-used tricks in the book. Adding a tilde (~) to a search term will return related terms. For example, Googling ~nutrition returns results with the words nutrition, food, and health in them.

3. Find music and comic books

Using a combination of advanced search operators that specify music files available in an Apache directory listing, you can turn Google into your personal Napster. Go ahead, try this search for Nirvana tracks: -inurl:(htm|html|php) intitle:"index of" +"last modified" +"parent directory" +description +size +(wma|mp3) "Nirvana". (Sub out Nirvana for the band you’re interested in; use this one in conjunction with number 7 to find new music, too.) The same type of search recipe can find comic books as well.

2. ID people, objects, and foreign language words and phrases with Google Image Search

Google Image search results show you instead of tell you about a word. Don’t know what jicama looks like? Not sure if the person named “Priti” who you’re emailing with is a woman or a man? Spanish rusty and you forgot what “corazon” is? Pop your term into Google Image Search (or type image jicama into the regular search box) to see what your term’s about.

 

1. Make Google recognize faces

If you’re doing an image search for Paris Hilton and don’t want any of the French city, a special URL parameter in Google’s Image search will do the trick. Add &imgtype=face to the end of your image search to just get images of faces, without any inanimate objects. Try it out with a search for rose (which returns many photos of flowers) versus rose with the face parameter.

What’s your favorite ninja Google search technique? Tell us about it in the comments.

SQL Server – Find Field Value in Database

The following is a SQL Script that can be run in a database to return all tables and columns where a particular value is present.  This can be used for strings or values with a small modification.

This type of thing is great when moving applications/products between servers.  This is certainly a good script to include in your master table to be used over and over.

DECLARE @value VARCHAR(64) 
DECLARE @sql VARCHAR(1024) 
DECLARE @table VARCHAR(64) 
DECLARE @column VARCHAR(64)

SET @value = ‘valuehere’

CREATE TABLE #t ( 
    tablename VARCHAR(64), 
    columnname VARCHAR(64) 
)

DECLARE TABLES CURSOR 
FOR

    SELECT o.name, c.name 
    FROM syscolumns c 
    INNER JOIN sysobjects o ON c.id = o.id 
    WHERE o.type = ‘U’ AND c.xtype IN (167, 175, 231, 239) 
    ORDER BY o.name, c.name

OPEN TABLES

FETCH NEXT FROM TABLES 
INTO @table, @column

WHILE @@FETCH_STATUS = 0 
BEGIN 
    SET @sql = ‘IF EXISTS(SELECT NULL FROM [' + @table + '] ‘ 
    –SET @sql = @sql + ‘WHERE RTRIM(LTRIM([' + @column + '])) = ”’ + @value + ”’) ‘ 
    SET @sql = @sql + ‘WHERE RTRIM(LTRIM([' + @column + '])) LIKE ”%’ + @value + ‘%”) ‘ 
    SET @sql = @sql + ‘INSERT INTO #t VALUES (”’ + @table + ”’, ”’ 
    SET @sql = @sql + @column + ”’)’

    EXEC(@sql)

    FETCH NEXT FROM TABLES 
    INTO @table, @column 
END

CLOSE TABLES 
DEALLOCATE TABLES

SELECT * 
FROM #t

DROP TABLE #t

Working with files and the FileSystemObject

By Darren Green
Version 7.0/2000
Level Intermediat

The ability to work with files gives us great flexibility in how we use DTS. It allows us to add some intelligence into a package, and cope with unexpected situations automatically. The key to working with files is the Scripting Run Time as this gives us the FileSystemObject, which can be fully exploited through ActiveX Script Tasks, Workflow scripts, and even transformations, inside your DTS packages. We start by demonstrating some simple file manipulation procedures, and then move on to how we can fully integrate them into a DTS package and make our solutions more flexible.

Full documentation from Microsoft on the FileSystemObject object and related objects can be found here

Copy File

This shows a simple file copy operation, using hardcoded source and destination filenames.

' Copy File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"
	sDestinationFile = "C:\DestinationFile.txt"

	oFSO.CopyFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Move or Rename File

This shows a simple file move operation, using hardcoded source and destination filenames. There is no explicit rename method in the scripting object, but a move is just the same.

' Move File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"
	sDestinationFile = "C:\Folder\DestinationFile.txt"

	oFSO.MoveFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Delete File

This shows a slightly more advanced delete operation. When using the DeleteFile method, if the file does not exist, an error is raised (File not found). We use the FileExists method to check for the file, and only call the DeleteFile when required. For another example of using FileExists see the article How can I check if a file exists?

' Delete File
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = "C:\SourceFile.txt"

	' Check if file exists to prevent error
	If oFSO.FileExists(sSourceFile) Then
		oFSO.DeleteFile sSourceFile
	End If

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Using Global Variables

To try and make our packages more flexible we should avoid using hard coded filenames as much as possible. One good method of doing this is to store all parameters required in global variables. In this way, no matter how many times we use a filename or file path inside our package, we only have to make one change. This sample is a move operation again, and uses two global variables. The source filename is held in one global variable, and the destination path or folder is held in a second. We are only supplying the destination folder in this example, so the existing filename will be preserved. It is important that the path is qualified with a backslash, so we use a helper function QualifyPath to ensure this is the case.

' Global Variable Move
Option Explicit

Function Main()

	Dim oFSO
	Dim sSourceFile
	Dim sDestinationFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value
	sDestinationFile = QualifyPath(DTSGlobalVariables("DestinationPath").Value)

	MsgBox sSourceFile
	MsgBox sDestinationFile
	oFSO.MoveFile sSourceFile, sDestinationFile

	' Clean Up
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

Function QualifyPath(ByVal sPath)
	If Right(sPath, 1) = "\" Then
		QualifyPath = sPath
	Else
		QualifyPath = sPath & "\"
	End If
End Function

File Date & Time

A simple validation check we may wish to do would be to check the data and time of a file to ensure it has been created within a defined time period. In this example we check that the file has been created today, otherwise we return a failure result from the task. In some case you may not want to fail the task, which in turn leads to a failed package result, in which case the code should be placed inside a Workflow script instead. For an example of using Workflow scripts to avoid failure conditions, see the article How can I check if a file exists?

' File Date & Time
Option Explicit

Function Main()

	Dim oFSO
	Dim oFile
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value

	Set oFile = oFSO.GetFile(sSourceFile)

	If oFile.DateCreated < Date Then
		Main = DTSTaskExecResult_Success
	Else
		Main = DTSTaskExecResult_Failure
	End If

	' Clean Up
	Set oFile = Nothing
	Set oFSO = Nothing
End Function

File Size

Another simple validation check similar to the date check shown above uses the Size property. In this example we will return an error if the file is empty or 0 bytes in size.

' File Size
Option Explicit

Function Main()

	Dim oFSO
	Dim oFile
	Dim sSourceFile

	Set oFSO = CreateObject("Scripting.FileSystemObject")

	sSourceFile = DTSGlobalVariables("SourceFileName").Value

	Set oFile = oFSO.GetFile(sSourceFile)

	If oFile.Size > 0 Then
		Main = DTSTaskExecResult_Success
	Else
		Main = DTSTaskExecResult_Failure
	End If

	' Clean Up
	Set oFile = Nothing
	Set oFSO = Nothing
End Function

Using Connections

The majority of your file manipulations will be related to files used by connections within your DTS package. A common requirement is to be import a file of a changing name, for which many people would first think of using the move or rename method shown above, but why not just change the connection instead? You can see how to do this in the article How can I change the filename for a text file connection?

The obvious integration of connections into your file manipulations is to read or set a filename on a connection, and use that within the rest of your script. Connections have a DataSource property, and for Text File connections and Jet (Excel, Access) connections, this property is the filename. For DBase and FoxPro connections then DataSource is the folder, the filename or table will be selected in the task that uses the connection.

In this simple example we will read the filename from a connection and then rename the file, such as you may want to do after importing a file to indicate that the file has been dealt with. To get reference to the connection object we use the name, this is what you see as the label in the designer and it is also shown in the “Existing connection” drop-down in the connection properties dialog.

' Rename File from Connection
Option Explicit

Function Main()

	Dim oPkg
	Dim oConn
	Dim oFSO
	Dim oFile
	Dim sFilename

	' Get reference to the current Package object
	Set oPkg = DTSGlobalVariables.Parent

	' Get reference to the named connection
	Set oConn = oPkg.Connections("Text File (Source)")

	' Get the filename from the connection
	sFilename = oConn.DataSource

	' Rename the file
	Set oFSO = CreateObject("Scripting.FileSystemObject")
	oFSO.MoveFile sFilename, sFilename & ".bak"

	' Clean Up
	Set oConn = Nothing
	Set oPkg = Nothing
	Set oFSO = Nothing

	Main = DTSTaskExecResult_Success
End Function

How can I check if a file exists?

By Darren Green
Version 2000
Level Intermediate

Checking for the existence of a file before trying to import it can be very useful in ensuring your systems run smoothly. You can use the Scripting FileSystemObject to check for the presence of a file using ActiveX Script. If the file exists you can obviously import it or initiate an alternative process, such as a sending a warning to someone or just ignore this error all together.

The simplest method is to use an ActiveX Script Task that returns a success (DTSTaskExecResult_Success) or failure (DTSTaskExecResult_Failure) result as appropriate. The following example reads the filename (DataSource) property from a named connection, and returns success or failure based upon the existence of the file:

' Pkg 211 (File Exists - 1)
Option Explicit

Function Main()
        Dim oFSO, sFileName

        ' Get the name of the file from the Connection "Text File (Source)"
        sFilename = DTSGlobalVariables.Parent.Connections("Text File (Source)").DataSource

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
                Main = DTSTaskExecResult_Success
        Else
                Main = DTSTaskExecResult_Failure
        End If

        Set oFSO = Nothing
End Function

The problem with using an ActiveX Script Task is that because the task fails, so does the package. This is often inappropriate, for example you may have package scheduled every 15 minutes that imports a file. This file is only produced when certain conditions have been met on an external system, and the absence of the file is by no means cause for concern. This is where you can use the power of an ActiveX Workflow Script. Workflow scripts use the DTSStepScriptResult constants, one of which is DTSStepScriptResult_DontExecuteTask. As you’d expect from the name, the task associated with this step does not execute, but importantly it does not raise any errors either.

To use an ActiveX workflow script, select the task that must be prevented from executing when the file is missing. Right-click Workflow Properties, or Workflow followed by Properties as appropriate. On the Options tab select “Use ActiveX Script”, and then click Properties. You will now need to code the checking mechanism and return the appropriate result. DTSStepScriptResult_ExecuteTask allows the task to execute and DTSStepScriptResult_DontExecuteTask blocks the workflow path and prevents the task from executing.

The example below takes it’s filename name from a global variable, purely to demonstrate and alternative mechanism of supplying this information. Reading it from a connection as above would work equally well. The actual check using the FileExists method is the same:

' Pkg 211 (File Exists - 2)
Option Explicit

Function Main()
        Dim oFSO, sFileName

        ' Get the name of the file from the global variable "ImportFileName"
        sFilename = DTSGlobalVariables("ImportFileName").Value

        Set oFSO = CreateObject("Scripting.FileSystemObject")

        ' Check for file and return appropriate result
        If oFSO.FileExists(sFilename) Then
                Main = DTSStepScriptResult_ExecuteTask
        Else
                Main = DTSStepScriptResult_DontExecuteTask
        End If

        Set oFSO = Nothing
End Function

Global Variables and SQL statements in DTS

By Darren Green
Version 7.0+/2000
Level Advanced

For those fortunate enough to have SQL 2000, using global variables with an Execute SQL Task, or as part of the source statement in the DataPump task is now a standard feature. For those still using SQL Server 7.0 this tedious process, but here are some examples of how it can be done. Even if you are using SQL Server 2000, you may still have a need for this due to certain syntax limitations imposed when using parameters.

In SQL Server 7.0 the only way to integrate a global variable into your SQL is to actually code the value as a literal in the SQL statement. The simplest solution is to just build a new statement with the value embedded in it at run-time. You can do this from an ActiveX Script Task as illustrated below.

This example amends the SELECT statement for a DataPump task, to filter based on the value of the global variable HireDate:

' 205 (Change SourceSQLStatement)
Option Explicit

Function Main()
	Dim oPkg, oDataPump, sSQLStatement

	' Build new SQL Statement
	sSQLStatement = "SELECT * FROM dbo.employee WHERE hire_date > '" & _
		DTSGlobalVariables("HireDate").Value & "'"

	' Get reference to the DataPump Task
	Set oPkg = DTSGlobalVariables.Parent
	Set oDataPump = oPkg.Tasks("DTSTask_DTSDataPumpTask_1").CustomTask

	' Assign SQL Statement to Source of DataPump
	oDataPump.SourceSQLStatement = sSQLStatement

	' Clean Up
	Set oDataPump = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

A similar method can be used to alter the Execute SQL Task:

' 205 (Change SQLStatement)
Option Explicit

Function Main()
	Dim oPkg, oExecSQL, sSQLStatement

	' Build new SQL Statement
	sSQLStatement = "INSERT dbo.NewEmployees" & vbCrLf & _
		"SELECT * FROM dbo.employee" & vbCrLf & _
		"WHERE hire_date > '" & DTSGlobalVariables("HireDate").Value & "'"

	' Get reference to the Exec SQL Task
	Set oPkg = DTSGlobalVariables.Parent
	Set oExecSQL = oPkg.Tasks("DTSTask_DTSExecuteSQLTask_2").CustomTask

	' Assign SQL Statement to Exec SQL Task
	oExecSQL.SQLStatement = sSQLStatement

	' Clean Up
	Set oExecSQL = Nothing
	Set oPkg = Nothing

	Main = DTSTaskExecResult_Success
End Function

 

To save time and reduce potential errors there is a custom task available from the Archive page which will prefix the SQL with T-SQL declares for all global variables in a package.

Global Variables and Stored Procedure Parameters

By Darren Green
Version 2000

Input Parameters

One of the benefits of the SQL Server 2000 is that the SQL tasks support mapping of global variables to parameter placeholders within the SQL. This means you can use the global variable as an input parameter for your stored procedure.

Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next add an Execute SQL Task, with the code to call your stored procedure, using a question mark as the placeholder for your parameter:

EXEC dbo.spInputTest ?

 

Once you have entered the SQL statement you can click Parameters and you will be presented with the Input Parameters tab. The mapping list view will have one row for each placeholder found in the SQL. There parameters have no meaningful names, they are listed in order, so Parameter 1 will be the first meaningful found, Parameter 2 will be the second placeholder found on so on. For each parameter found you can select a global variable to map to that parameter. At run time the provider (ODBC or OLE-DB) parameter support will be invoked and the mapped global variable value will be substituted for the placeholder.

It is important to note that the parameter handling uses the provider parameter support since not all providers support parameters, and others provide only limited or unreliable support. Obviously the Microsoft SQL Server providers have full support.

Another important point about parameters is that just like when using local variables in SQL they cannot be used to parameterise object names such as procedure names, table names or column names. In a normal SQL statement you can workaround this by using dynamic SQL, but in DTS you have the option of dynamically building the SQL statement. For more on this strategy see Global Variables and SQL statements in DTS

Ouput Parameters

You can also capture stored procedure output parameters, although this cannot be done through the same placeholder mechanism. This simple example demonstrates how you can capture stored procedure return values and output parameters into global variables using the Output Parameter functionality of the Execute SQL Task in SQL Server 2000.

The Execute SQL Task supports two types or output parameter types, row values and rowsets. There is no placeholder functionality as used for input parameters. Confusion often arises when people try and retrieve values from the input parameter placeholder. In this case we have to use a SELECT statement to return our values as normal query rowset and map them in this way.

To build a simple demonstration package, first create the stored procedure below:

CREATE PROCEDURE dbo.spReturnAndOutputTest
    @OutTest int OUTPUT
AS
  SET @OutTest = 9
  RETURN 1

 

Create a new package, adding a SQL Server connection pointing to the database where you have created the sample stored procedure. Next add an Execute SQL Task, with the code to call our stored procedure and return the values as a query result or rowset:

DECLARE @ReturnValue int, @OutTest int
EXEC @ReturnValue=dbo.spReturnAndOutputTest @OutTest OUTPUT
SELECT @ReturnValue AS ReturnValue, @OutTest AS OutTest

 

Once you have entered the SQL statement you can click Parameters and select the Output Parameters tab. Select the Output Parameter Type of Row Value. The columns of our SELECT, the values we want to return, are shown in the left hand column of the Parameter Mapping grid, using the names specified as column aliases in the SELECT statement. They can now be mapped to global variables of your choice.

Input and Output Parameters

To use both input and ouput parameters for the same stored procedure call, a combination of the two methods described above might look like this:

DECLARE @OutputTest int
EXEC dbo.spInputAndOutputTest ?,  @OutputTest OUTPUT
SELECT @OutputTest AS OutputTest

 

Unfortunately when you click on the Parameters button you will get the following error:

Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Syntax error or access violation

The error is raised by the design-time syntax validation routine. Fortunately the run-time routine is more forgiving than the design-time routine. The way to workaround this is to substitute your stored procedure call with a simple SQL statement that produces the same meta-data, which will allow you to map the parameters as required. Below is our sample stored procedure:

CREATE PROCEDURE dbo.spInputAndOutputTest
    @InputTest int,
    @OutputTest int OUTPUT
AS
  SET @OutputTest = @InputTest * 2

 

The following simple SQL statement, whilst functionaly different, produces the same meta-data as our real SQL statement:

SELECT id AS OutputTest FROM sysobjects where id = ?

 

Enter the meta-data matching SQL and click Parameters. We can then map the input parameter as in the first example. Next select Output Parameters and go onto select a Row Value output parameter as in the second example. Once complete close Execute SQL Task.

The parameter mapping has now been done, but to complete the operation you need replace the matching SQL with the real SQL. To do this open the Disconnected Edit feature (found on the Package menu). Expand the Tasks node, and select the correct Execute SQL Task, e.g. DTSTask_DTSExecuteSQLTask_1. Now double click the SQLStatement property for our selected task, and overwrite the matching SQL with the real SQL as shown at the top of this example. Execute the package.

In summary, you can use some dummy SQL to setup the parameter mapping at design-time, but then change the SQL afterwards. This does mean that if you ever need to change the parameter mappings you must repeat the substitution process to setup the new mappings.

Using SET NOCOUNT ON

If your stored procedure performs one or more DML operations that cause informational resultsets to be generated, then these will confuse the task. An informational resultset will be familiar to anyone that uses Query Analyser, as you will have seen them as messages in results, for example (1 row(s) affected). These messages are indistinguishable from the SELECT resultset you are really interested in, and since the task reads the first resultset it encounters, an informational resultset can mask your real data. To suppress this behaviour add the SET NOCOUNT ON statement to the top or your stored procedure or script.

SINGARAYA KONDA

Sri Lakshmi Narasimha Swamy - It is famous temple of God Sri Lakshmi Narasimha Swamy which is located at Patha Singaraya Konda near 30 kms to Ongole (Prakasam District, Andhra Pradesh),which dates back to the 15th century. The grand Brahmostavaalu celebrations held in june every year, attracts a large number of devotees from all over.

To view temple visually please click the below link.
http://www.flickr.com/photos/34087317@N08/sets/72157612140937017/

SQL Interview Questions

1. What are two methods of retrieving SQL?
2. What cursor type do you use to retrieve multiple recordsets?
3. What is the difference between a “where” clause and a “having” clause? - “Where” is a kind of restiriction statement. You use where clause to restrict all the data from DB.Where clause is using before result retrieving. But Having clause is using after retrieving the data.Having clause is a kind of filtering command.
4. What is the basic form of a SQL statement to read data out of a table? The basic form to read data out of table is ‘SELECT * FROM table_name; ‘ An answer: ‘SELECT * FROM table_name WHERE xyz= ‘whatever’;’ cannot be called basic form because of WHERE clause. 
5. What structure can you implement for the database to speed up table reads? - Follow the rules of DB tuning we have to: 1] properly use indexes ( different types of indexes) 2] properly locate different DB objects across different tablespaces, files and so on.3] create a special space (tablespace) to locate some of the data with special datatype ( for example CLOB, LOB and …)
6. What are the tradeoffs with having indexes? – 1. Faster selects, slower updates. 2. Extra storage space to store indexes. Updates are slower because in addition to updating the table you have to update the index.
7. What is a “join”? - ‘join’ used to connect two or more tables logically with or without common field.
8. What is “normalization”? “Denormalization”? Why do you sometimes want to denormalize? - Normalizing data means eliminating redundant information from a table and organizing the data so that future changes to the table are easier. Denormalization means allowing redundancy in a table. The main benefit of denormalization is improved performance with simplified data retrieval and manipulation. This is done by reduction in the number of joins needed for data processing.
9. What is a “constraint”? - A constraint allows you to apply simple referential integrity checks to a table. There are four primary types of constraints that are currently supported by SQL Server: PRIMARY/UNIQUE – enforces uniqueness of a particular table column. DEFAULT – specifies a default value for a column in case an insert operation does not provide one. FOREIGN KEY – validates that every value in a column exists in a column of another table. CHECK – checks that every value stored in a column is in some specified list. Each type of constraint performs a specific type of action. Default is not a constraint. NOT NULL is one more constraint which does not allow values in the specific column to be null. And also it the only constraint which is not a table level constraint.
10. What types of index data structures can you have? - An index helps to faster search values in tables. The three most commonly used index-types are: – B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases. – Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only few space and is very fast.(however, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD) – Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.
11. What is a “primary key”? - A PRIMARY INDEX or PRIMARY KEY is something which comes mainly from 
    database theory. From its behavior is almost the same as an UNIQUE INDEX, i.e. there may only be one of each value in this column. If you call such an INDEX PRIMARY instead of UNIQUE, you say something about 
    your table design, which I am not able to explain in few words. Primary Key is a type of a constraint enforcing uniqueness and data integrity for each row of a table. All columns participating in a primary key constraint must possess the NOT NULL property.
12. What is a “functional dependency”? How does it relate to database table design? – Functional dependency relates to how one object depends upon the other in the database. for example, procedure/function sp2 may be called by procedure sp1. Then we say that sp1 has functional dependency on sp2.
13. What is a “trigger”? - Triggers are stored procedures created in order to enforce integrity rules in a database. A trigger is executed every time a data-modification operation occurs (i.e., insert, update or delete). Triggers are executed automatically on occurance of one of the data-modification operations. A trigger is a database object directly associated with a particular table. It fires whenever a specific statement/type of statement is issued against that table. The types of statements are insert,update,delete and query statements. Basically, trigger is a set of SQL statements A trigger is a solution to the restrictions of a constraint. For instance: 1.A database column cannot carry PSEUDO columns as criteria where a trigger can. 2. A database constraint cannot refer old and new values for a row where a trigger can.
14. Why can a “group by” or “order by” clause be expensive to process? – Processing of “group by” or “order by” clause often requires creation of Temporary tables to process the results of the query. Which depending of the result set can be very expensive.
15. What is “index covering” of a query? – Index covering means that “Data can be found only using indexes, without touching the tables”
16. What types of join algorithms can you have?
17. What is a SQL view? – An output of a query can be stored as a view. View acts like small table which meets our criterion. View is a precomplied SQL query which is used to select data from one or more tables. A view is like a table but it doesn’t physically take any space. View is a good way to present data in a particular format if you use that query quite often. View can also be used to restrict users from accessing the tables directly.

 

1. Which of the following has the highest order of precedence?
   • Functions and Parenthesis
   • Multiplication, Division and Exponents
   • Addition and Subtraction
   • Logical Operations
2. When designing a database table, how do you avoid missing column values for non-primary key columns?
   • Use UNIQUE constraints
   • Use PRIMARY KEY constraints
   • Use DEFAULT and NOT NULL constraints
   • Use FOREIGN KEY constraints
   • Use SET constraints
3. Which of the following is the syntax for creating an Index?
   • CREATE [UNIQUE] INDEX index_name OF tbl_name (index_columns)
   • CREATE [UNIQUE] INDEX OF tbl_name (index_columns)
   • CREATE [UNIQUE] INDEX ON tbl_name (index_columns)
   • CREATE [UNIQUE] INDEX index_name ON tbl_name (index_columns)
4. Which of the following is not a valid character datatype in SQL Server?
   • BLOB
   • CHAR
   • VARCHAR
   • TEXT
   • VARTEXT
5. Which of the following statements about SQL Server comments is false?
   • /* … */ are used for multiline comments
   • // is used for single line comments
   • – is used for single line comments
   • Nested comments are allowed i.e. /* comment 1 /* comment 2 */ comment 1*/
   • ‘ is used for single line comments
6. Consider the following transaction code:
   
   Begin Transaction
Update names_table set employee_name = "Ramesh" where employee_name = "Mahesh"
Save Transaction SAVE_POINT
Update salaries set salary=salary + 900 where employee_job = "Engineer"
Rollback transaction
Commit transaction
   What will be the result produced by this transaction?   
   • “Ramesh” will be updated to “Mahesh”, but salaries of engineers will not be

updated

1.  
   • Neither “Ramesh” will be updated to “Mahesh”, nor the salary of engineers will be updated.
   • “Ramesh” will be updated to “Mahesh” and salary of engineers will also be

updated.

1. Which of the following constraints can be used to enforce the uniqueness of rows in a table?
   • DEFAULT and NOT NULL constraints
   • FOREIGN KEY constraints
   • PRIMARY KEY and UNIQUE constraints
   • IDENTITY columns
   • CHECK constraints
2. Which of the following are not date parts?
   • quarter
   • dayofweek
   • dayofyear
   • weekday
3. The IF UPDATE (column_name) parameter in a trigger definition will return
   TRUE in case of an INSERT statement being executed on the triggered table:   
   • Yes
   • No
   • It returns TRUE only if an UPDATE query is executed
   • Both b and c
4. Which one of the following must be specified in every DELETE statement?
   • Table Name
   • Database name
   • LIMIT clause
   • WHERE clause
   • Column Names
5. Which one of the following correctly selects rows from the table myTable that have null in column column1?
   • SELECT * FROM myTable WHERE column1 is null
   • SELECT * FROM myTable WHERE column1 = null
   • SELECT * FROM myTable WHERE column1 EQUALS null
   • SELECT * FROM myTable WHERE column1 NOT null
   • SELECT * FROM myTable WHERE column1 CONTAINS null
6. Is this statement true or false:
   A cursor is a pointer that identifies a specific working row within a set   
   • True
   • False
7. Which of the following commands is used to change the structure of table?
   • CHANGE TABLE
   • MODIFY TABLE
   • ALTER TABLE
   • UPDATE TABLE
8. Consider the following statements and pick the correct answer:
   
   1. ceiling() - returns the smallest integer greater than or equal to the specified value
2. floor() - returns the largest integer less than or equal to the specified value   
   • 1 is true and 2 is false
   • 1 is false and 2 is true
   • Both 1 and 2 are true
   • Both 1 and 2 are false
9. What is the correct SQL syntax for returning all the columns from a table named “Persons” sorted REVERSE alphabetically by “FirstName”?
   • SELECT * FROM Persons WHERE FirstName ORDER BY FirstName DESC
   • SELECT * FROM Persons SORT REVERSE ‘FirstName’

c . SELECT * FROM Persons ORDER BY ‘FirstName’

1.  
   • SELECT * FROM Persons ORDER BY FirstName DESC

SELECT * FROM Persons ORDER BY DESC FirstName

1. What is the maximum value that can be stored for a datetime field?
   • Dec 31, 9999
   • Jun 6, 2079
   • Jan 1, 2753
   • Jan 1, 2100
2. Consider the following queries:
   1. select * from employee where department LIKE "[^F-M]%”;
2. select * from employee where department = “[^F-M]%”;
   Select the correct option:   
   • Query 2 will return an error
   • Both the queries will return the same set of records
   • Query 2 is perfectly correct
   • Query 2 would return one record less than Query 1
3. How can you view the structure of a table named “myTable” in SQL Server?
   • desc myTable
   • desc table myTable
   • sp_columns myTable
   • None of the above
   • Using either option a or c
4. What does referential integrity (also called relational integrity) prevent?
   • Loss of data from employee sabotage
   • Loss of data from any one corrupted table
   • Recursive joins
   • One-to-many or many-to-many relationships between columns in a table
   • Data redundancy
5. Which of the following is not a global variable?
   • @@colcount
   • @@error
   • @@rowcount
   • @@version
   • All are valid global variables
6. Consider the following two tables:1. customers( customer_id, customer_name)
   2. branch ( branch_id, branch_name )
   What will be the output if the following query is executed:
   Select * branch_name from customers,branch   
   • It will return the fields customer_id, customer_name, branch_name
   • It will return the fields customer_id, customer_name, branch_id, branch_name
   • It will return the fields customer_id, customer_name, branch_id, branch_name, branch_name
   • It will return an empty set since the two tables do not have any common field name
   • It will return an error since * is used alone for one table only

    

    

    

7. Which of the following is not a control statement?
   • if…else
   • if exists
   • do…while
   • while
   • begin…end
8. Which of the following is not a valid Numeric datatypes in SQL Server?
   • INT
   • SMALLINT
   • TINYINT
   • BIGINT
   • MONEY
9. Which of the following datatypes is not supported by SQL-Server?
   • Character
   • Binary
   • Logical
   • Date
   • Numeric
   • All are supported
10. What will the output be if you try to perform arithmetic on NULL values?
    • 0
    • NULL
    • It will generate an error message
    • Can’t be determined
11. Which of the following options is not correct about the DATEDIFF() function?
    • It returns the difference between parts of two specified dates
    • It takes three arguments
    • It returns a signed integer value equal to second date part minus first date part
    • It returns a signed integer value equal to first date part minus second date part
12. Sample Code
    
    CREATE TABLE table1(
column1 varchar(50),
column2 varchar(50),
column3 varchar(50),
column4 varchar(50));
    Which one of the following is the correct syntax for adding the column named “column2a” after column2 to the table shown above?   
    • ALTER TABLE table1 ADD column2a varchar(50) AFTER column2;
    • MODIFY TABLE table1 ADD column2a AFTER column2;
    • INSERT INTO table1 column2a AS varchar(50) AFTER column2;
    • ALTER TABLE table1 INSERT column2a varchar(50) AFTER column2;
    • CHANGE TABLE table1 INSERT column2a BEFORE column3;
13. State which of the following are true
    • Views are a logical way of looking at the logical data located in the tables
    • Views are a logical way of looking at the physical data located in the tables
    • Tables are physical constructs used for storage and manipulation of data in databases
    • Tables are logical constructs used for storage and manipulation of data in databases
14. Which of the following is not a valid binary datatype in SQL Server?
    • BINARY
    • VARBINARY
    • BIT
    • IMAGE
    • TESTAMP
15. Which of the following is false with regards to sp_help?
    • When a procedure name is passed to sp_help, it shows the parameters
    • When a table name is passed to sp_help, it shows the structure of the table
    • When no parameter is passed, it provides a list of all objects and user-defined datatypes in a database
    • All of the above are true
    • Which of the following are false for batches (batch commands)?
      • Statements in a batch are parsed, compiled and executed as a group
      • None of the statements in the batch is executed if there are any syntax errors in the batch
      • None of the statements in the batch is executed if there are any parsing errors in the batch
      • None of the statements in the batch is executed if there are any fatal errors in the batch
    • Select the correct option:
      • Optimistic locking is a locking scheme handled by the server, whereas pessimistic locking is handled by the application developer
      • Pessimistic locking is a locking scheme handled by the server, whereas optimistic locking is handled by the application developer