Earlier, we discussed a situation wherein the database objects (an ad-hoc query, stored procedures, functions, etc) need to supply meta-data to the calling applications so that the format of the response can be tested without actually running the query. ADO uses this mechanism to make a “dry-run”.

In SQL Server 2008 R2 and below, one SET option came in very handy in realizing this requirement. This option was the FMTONLY option. The FMTONLY option is marked as a deprecated feature in SQL Server 2012, and is replaced by:

• sp_describe_first_result_set
• sys.dm_exec_describe_first_result_set
• sys.dm_exec_describe_first_result_set_for_object

sp_describe_first_result_set

You can revisit the discussion related to FMTONLY here. The core requirement is that at execute time, the SQL Server database engine should parse (PARSEONLY), compile (NOEXEC) and executed a query, but return only meta-data back to the client application. No data rows are to be returned. Please note that the name of the procedure suggests that information is returned only for the first result set (this could be applicable in case your procedures return more than one result set/MARS).

Here’s how the sp_describe_first_result_set stored procedure can be used to get this information:

USE AdventureWorks2012
GO
--SQL 2012 and above only!
EXEC sp_describe_first_result_set @tsql = N'SELECT Employee.BusinessEntityID,
                                                   Employee.BirthDate,
                                                   Employee.Gender,
                                                   Employee.JobTitle
                                            FROM HumanResources.Employee AS Employee'
GO

One of the first things we see is that the result set is much more comprehensive and user-friendly when compared with the older FMTONLY.

The extended stored procedure also accepts:

• A set of parameters (@params), similar to sp_executesql
• A @browse_information_mode parameter which has the following possible values:
  • 0 = No information is returned
  • 1 = Query is analyzed as if it contains the FOR BROWSE option specified
  • 2 = Query is analyzed as if it would be used in preparing or executing a cursor

Return Value information:

1. When called from T-SQL, sp_describe_first_result_set will always have a return value of 0
2. If the procedure throws an error and the procedure is called as an RPC, return status is populated by the type of error described in the error_type column of sys.dm_exec_describe_first_result_set

sys.dm_exec_describe_first_result_set

This is the DMV equivalent of sp_describe_first_result_set. Sample execution is as shown below:

USE AdventureWorks2012
GO
SELECT * 
FROM sys.dm_exec_describe_first_result_set(N'SELECT Employee.BusinessEntityID,
                                                    Employee.BirthDate,
                                                    Employee.Gender,
                                                    Employee.JobTitle
                                            FROM HumanResources.Employee AS Employee',
                                            NULL,
                                            0)
GO

As you may have noticed, the function takes 3 parameters which are same as that shown above for sp_describe_first_result_set. The result set is also the same.

sys.dm_exec_describe_first_result_set_for_object

Functionally equivalent to sys.dm_exec_describe_first_result_set dynamic management function, sys.dm_exec_describe_first_result_set_for_object accepts an Object Id as input. This Object Id can be that of a T-SQL stored procedure or a T-SQL trigger. If it is the ID of any other object (such as a view, table, function, or CLR procedure), an error will be specified in the error columns of the result.

The result set is same as that of sys.dm_exec_describe_first_result_set.

Please NOTE:

Since these procedures/functions return meta-data about the return result-set, one of the following will be true if the batch is executed after execution of sp_describe_first_result_set:

1. Result in an optimization-time error
2. Result in a run-time error (depending upon the parameters passed or the state of the server/database, or another general run-time error)
3. Return no result set (depending upon the parameters passed)
4. Returns a result-set conforming to the same meta-data

References:

1. sp_describe_first_result_set – http://msdn.microsoft.com/en-us/library/ff878602.aspx
2. sys.dm_exec_describe_first_result_set – http://msdn.microsoft.com/en-us/library/ff878258.aspx
3. sys.dm_exec_describe_first_result_set_for_object – http://msdn.microsoft.com/en-us/library/ff878236.aspx

Until we meet next time,

Be courteous. Drive responsibly.

One may encounter many situations wherein the database objects (an ad-hoc query, stored procedures, functions, etc) need to supply meta-data to the calling applications so that the format of the response can be tested without actually running the query. A real-world example is to ensure that the application is working with the correct version of the database. If the number or type of the columns returned are not as per expectations, the user can be presented with a well-formed message rather than an exception. I believe ADO uses this mechanism to make a “dry-run”.

In SQL Server 2008 R2 and below, one SET option comes in very handy in realizing this requirement. This option is the FMTONLY option.

NOTE: The FMTONLY option is marked as a deprecated feature in SQL Server 2012. I will be writing about the replacement in the immediate future.

SET FMTONLY

At execute time, if the SQL Server database engine encounters the option FMTONLY as being set to ON, no rows are processed or sent to the client. However, the query/batch is parsed (PARSEONLY), compiled (NOEXEC) and executed. Here’s a simple demo script:

USE AdventureWorks2012
GO

--Set the FMTONLY option to ON, telling the database engine
--to Parse, Compile, Execute the query, 
--and return meta-data, but not return any records
SET FMTONLY ON

SELECT '3' AS RoundNum,
       Employee.BusinessEntityID,
       Employee.BirthDate,
       Employee.Gender,
       Employee.JobTitle
FROM HumanResources.Employee AS Employee
GO

SET FMTONLY OFF

--Check for caching
SELECT '3' AS RoundNum, usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
        text like '%FROM HumanResources.Employee%'
    AND text NOT LIKE '%Check%'
ORDER BY usecounts DESC;
GO

Executing the query returns two result sets – first one from the actual query (indicating that the query was actually executed), and the second result set showing that the query plan was compiled.

The messages tab supports the observation:

Please NOTE:

Please do not confuse the FMTONLY feature with the following:

1. Query result options – Discard result after query executes – this does not return meta-data back to the client/calling application
   
2. OPTION (FAST n)/Table Hint: FASTFIRSTROW – this returns n number of data rows back to the client/calling application

Until we meet next time,

Be courteous. Drive responsibly.

What would you do if someone tells you to parse and compile a T-SQL query without executing it?

This could possibly be required under a situation wherein one needs to have a compiled plan available in the procedure cache before users get into a system and the server has either been restarted or has been rebuilt.

To the best of my knowledge, there is no UI element (a toolbar button, or a check-box) to achieve this. The only way I know is to use a SET option – NOEXEC.

SET NOEXEC

As discussed in a previous post, SET options allow us to change the behaviour of SQL Server for the current session with respect to handling of specific information like date and time values, locking, Query execution, transaction, ISO settings and Statistics.

Execution of a query is a two-step process and involves query compilation and subsequent execution of the compiled plan. When the NOEXEC option is set to ON, the SQL Server database engine only compiles the query, but does not execute it. Therefore, a compiled plan is produced and stored in the procedure cache (under normal circumstances).

Let’s do a simple test to confirm this.

USE AdventureWorks2012
GO
--Set the NOEXEC option to ON, telling the database engine
--to Parse and Compile the query, but not execute it
SET NOEXEC ON

SELECT '2' AS RoundNum,
       Employee.BusinessEntityID,
       Employee.BirthDate,
       Employee.Gender,
       Employee.JobTitle
FROM HumanResources.Employee AS Employee;
GO

SET NOEXEC OFF

--Check for caching
SELECT '2' AS RoundNum, usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
        text like '%FROM HumanResources.Employee%'
    AND text NOT LIKE '%Check%'
ORDER BY usecounts DESC;
GO

Execution of both queries succeeds, and the 2nd query (which looks for presence of a cached plan) indicates that compilation has indeed taken place and the query plan generated.

Here’s what I would like to know: Have you ever encountered a situation wherein you required to set NOEXEC to ON? Do leave a note in the blog comments as you go.

Until we meet next time,

Be courteous. Drive responsibly.

What would you do if you wanted to check whether a particular query/batch you wrote is free from any errors without using Intelli-sense and without executing or compiling the statement?

It’s a fairly simple question for anyone who has worked with SQL Server for a considerable amount of time. You would use the tiny blue check-box besides the “Execute” and “Debug” buttons on the SSMS toolbar, right?

Parse button on the SSMS for SQL 2012 toolbar

SET PARSEONLY

Well, if you are a keyboard-savvy developer (like me), you would use a SET option. SET options allow us to change the behaviour of SQL Server for the current session with respect to handling of specific information like date and time values, locking, Query execution, transaction, ISO settings and Statistics.

One such option is the SET PARSEONLY option, which when set to ON, parses a T-SQL query/batch for errors without executing or compiling the query/batch.

Let’s do a simple test to confirm if this indeed works or not. After parsing the query using the PARSEONLY option, we will attempt to see if a cached plan was generated for the query. If the query compiles or gets executed, an entry would exist for the cached plan.

USE AdventureWorks2012
GO

--Set PARSEONLY to ON, indicating that the query should not be compiled or executed
SET PARSEONLY ON

SELECT '1' AS RoundNum,
       Employee.BusinessEntityID,
       Employee.BirthDate,
       Employee.Gender,
       Employee.JobTitle
FROM HumanResources.Employee AS Employee;
GO

--Set PARSEONLY to OFF
SET PARSEONLY OFF

--Check for caching of the query plan. 
--If the query was compiled, an entry would be available here
SELECT '1' AS RoundNum, usecounts, cacheobjtype, objtype, text 
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE usecounts > 0 AND 
        text like '%FROM HumanResources.Employee%'
    AND text NOT LIKE '%Check%'
ORDER BY usecounts DESC;
GO

Execution of both the queries succeed, but no records are returned by the 2nd query (one which returns cached plans). Checking the “Messages” tab also tells us that no records were returned back to the client, indicating that the first query was only parsed, but not compiled or executed.

Please NOTE:

1. The SET option – PARSEONLY is set at parse time, and not at execution or run-time. Hence, please do not use this inside a stored procedure or a trigger
   
2. Successful parsing of a query does not protect against:




• Incorrect object names
  
• Any logical or business-rule related errors that may exist


3. Hence, before integrating your code changes into source control, please make sure that you unit test your queries/batches by setting PARSEONLY to OFF

Until we meet next time,

Be courteous. Drive responsibly.

Recently, Pinal Dave, a.k.a SQLAuthority (B|T) wrote a piece about Managing Help settings in the SSMS for SQL Server 2012. That post prompted me to write this post around local deployment of the SQL Server Books On Line collection for SQL Server 2012.

The SSMS for SQL Server 2012 comes with very visible UI changes. For starters, it comes with the Visual Studio 2010 shell (if you would like to know more about the capabilities of the SSMS, please follow my 10-part tutorial on the topic here: Getting started with SSMS). One of the other major differences is that the Books-On-Line or Help collection is no longer available locally (i.e. deployed on the client machine) by default. The default mode for the help is online.

There are at least two benefits that I can see from having the help online:

1. Saves space on the client machine
   
2. One never has out-of-date help content, because the help is online!

There are however, a few instances wherein you would prefer to have the help installed locally. For example, when you would like to work while travelling or in case the IT policy at your office does not allow open Internet access. For such circumstances, the SSMS for SQL Server 2012 does allow the help collection to be deployed locally, which is what I will be demonstrating through this post.

Pre Requisite

Download and extract the contents of Product Documentation for Microsoft SQL Server 2012 for firewall and proxy restricted environments. This is approximately a 200MB download, so please check your available bandwidth and data plan costs before proceeding with the download.

Steps to install & configure local help

Press the key combination “Ctrl+Alt+F1” or go to Help –> Manage Help Settings

The Help Library Manager would be launched

Installing Help from local source

Once the offline product documentation has been extracted, you can use the following steps to install:

Choose to “Install Content from disk”

Select the “HelpContentSetup.msha” file from the extracted offline documentation.

Click “Next” and choose to “Add” any/all help packages of your choice

Click “Update” to begin extraction and deployment of the local help content

Once the installation is complete, click “Finish”

Installing Help from online source

If you do not want to download the entire 200MB help/product documentation package, you can also install it from an online source. This mode allows you to choose the specific topics for which you would like to download the help.

Choose “Install Content from Online” on the Help Library Manager window.

The system fetches the product group and associated production documentation material listing.

Choose “Add” to add the documentation to the download and install list. Notice that upon clicking “Add”, the status changes to “Update pending” indicating that the content will be updated from the online store when the user presses “Update” button (similar to offline install shown above).

Configuring online v/s local help collection usage

Once that the local help collection is downloaded and deployed, users can choose to switch from online to local help mode.

Click on “Choose online or local help” in the Help Library Manager

Set your preferred Help experience and click “OK”. You are now ready to use your newly downloaded local help collection.

Until we meet next time,

Be courteous. Drive responsibly.