Knowing the internals of how a system works has always fascinated me. Although a bit theoretical, knowing about query evaluation is a very exciting topic and I attempt to learn something new about it every now and then.

One of my seniors had a mantra which I remember till date – when evaluating a logical “AND” condition, the costliest condition should always be the last one to be evaluated. Today, I will try to demonstrate why this is a good practice.

The evaluation of the logical AND is actually quite simple. A set of checks evaluate to FALSE even if one of the checks returns FALSE. This simple rule means that the AND evaluation must be a “short-circuit” operation, i.e. as soon as a condition is encountered which determines the end result, all checks beyond that point can be ignored.

The test

The test below demonstrates this concept. What I have in the script is a simple query that returns results based on the evaluation of two conditions – one of which is bound to raise an exception.

USE AdventureWorks2008R2;
GO
BEGIN TRY
    SELECT *
    FROM HumanResources.Employee
    WHERE (1 = 0)    --This one is false making the entire AND condition FALSE
      AND (1/0 = 0); --This one results in an exception
PRINT 'Execution was successful.';
END TRY
BEGIN CATCH
PRINT 'All conditions were evaluated resulting in an exception!'
+ CHAR(10) + 'Error Message : ' + ERROR_MESSAGE()
+ CHAR(10) + 'Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
END CATCH
GO

/*********************
RESULTS
*********************/
/*
(0 row(s) affected)
Execution was successful.
*/

In this first query, the first condition itself evaluates to FALSE. Hence, there is no reason for the evaluation of the logical AND to continue. The query therefore returns no results. Had both conditions been evaluated, we would have received an exception.

USE AdventureWorks2008R2;
GO
BEGIN TRY
    SELECT *
    FROM HumanResources.Employee
    WHERE (1 = 1)    --This one is TRUE,
                     --because of which the next condition will be evaluated
      AND (1/0 = 0); --This one results in an exception
PRINT 'Execution was successful.';
END TRY
BEGIN CATCH
PRINT 'All conditions were evaluated resulting in an exception!'
+ CHAR(10) + 'Error Message : ' + ERROR_MESSAGE()
+ CHAR(10) + 'Error Number : ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
END CATCH
GO
/*********************
RESULTS
*********************/
/*
(0 row(s) affected)
All conditions were evaluated resulting in an exception!
Error Message : Divide by zero error encountered.
Error Number : 8134
*/

Because the first condition retuned a TRUE in the query above, it returned an exception because the evaluation of the logical AND operation had to evaluate both conditions.

Conclusion

This simple test proves that the logical AND operation is a “short-circuit” operation. Therefore, the costliest condition (maybe a sub-query) in a set to be evaluated with a logical AND must be placed towards the end of the sequence to prevent un-necessary evaluations. Only if the preceding conditions are TRUE, will the more complex conditions be evaluated under this logic.

Until we meet next time,

Be courteous. Drive responsibly.

Today’s post is actually a quick tip about something I learnt over the week-end. We may often have a long line of text that needs to be embedded into our T-SQL code for inserting static data. Depending upon the IDE settings on the client workstation, the long line of text would either automatically wrap-around to a new line or continue in a single line forever, affecting the readability of the entire T-SQL query.

The easiest way to split a long line of text into multiple lines for readability purposes is to use the back-slash character as shown in the script below:

USE tempdb;

GO

DECLARE @stringOperationsTest TABLE (strVale VARCHAR(100));

INSERT INTO @stringOperationsTest (strVale)

VALUES

(‘Microsoft SQL Server’);
SELECT strVale

FROM @stringOperationsTest;

GO
/**********************

       RESULTS

**********************/

/*

strVale

———————

Microsoft SQL Server

*/

Until we meet next time,

Be courteous. Drive responsibly.

When working on quality assurance, staging and production environments, it is very important to have control over the changes made to the database definition – objects and design. Today, I present a simple script to help you get a list of objects in your database with their creation and modification dates.

The script below (which will also be available on the Scripts module of the website shortly) can be customized to suit your requirements by modifying the WHERE clause to filter on the object types, creation and modification dates as required.

USE AdventureWorks2008R2 ;

GO

SELECT  so.name AS ObjectName,

        OBJECT_NAME(so.parent_object_id) AS ParentObjectName,

        SCHEMA_NAME(so.schema_id) AS SchemaName,

        so.type_desc AS ObjectType,

        so.create_date AS ObjectCreationDateTime,

        so.modify_date AS ObjectModificationDateTime,

        so.is_published AS IsPublished

FROM    sys.objects AS so

WHERE   so.is_ms_shipped = 0

ORDER BY CASE WHEN so.parent_object_id = 0 THEN so.object_id

              ELSE so.parent_object_id

         END,

        so.schema_id,

        so.type DESC,

        so.modify_date DESC,

        so.create_date DESC ;

GO

Until we meet next time,

Be courteous. Drive responsibly.

Often, the correct answer to any SQL Server problem is just two words – “It depends!” And the fact is that the more I work with SQL Server, the importance of these two words cements itself into the foundations of my thinking.

Let’s take an example which I came across recently, and which prompted this post.

The Challenge

For one of the ad-hoc training assignments that I was supervising, the design was such that there would be a comma-separated list as input to one of the stored procedures. These values had to be inserted as records into a table. The application/script was to be run on SQL Server 2008 R2 and above only.

If we talk about implementation, there are a couple of different ways in which it can be done with approaches ranging from using a simple WHILE loop to using PATINDEX. While all participants came up with these, I decided to spice things up a bit. The challenge I presented to them was:

 

Generate the statement to insert values from a comma-separated string to a table in a single T-SQL statement.

The Answer

The answer to this problem is in thinking simple. Given that the script needs to run only on SQL Server 2008 R2 and above, we can convert the comma-separated string into row constructors for use with the VALUES clause. This can be done in a single T-SQL statement by using the REPLACE function as shown below:

USE tempdb;
GO
BEGIN
    SET NOCOUNT ON;
    --Safety Check
    IF OBJECT_ID(N'#productNames', N'U') IS NOT NULL
        BEGIN
            DROP TABLE #productNames;
    END;
    
    CREATE TABLE #productNames(Product VARCHAR(25));
    
    --Input Parameter
    DECLARE @parameterNames NVARCHAR(MAX)= N'Microsoft,SQL Server,Windows,Office,Sharepoint,BizTalk';

    --Query Statement
    DECLARE @stmnt NVARCHAR(1000);

    --Build the query
    --Key is to use the REPLACE function here
    SELECT @stmnt = N'INSERT INTO #productNames (Product) VALUES ' + N'(''' + REPLACE(@parameterNames, ',', '''),(''') + N''')';
    
    --Check the query
    SELECT @stmnt AS QueryStatement;
    
    --Execute the query
    EXEC sp_executesql 
         @stmnt;
    
    --Check the query result
    SELECT pn.Product
    FROM #productNames AS pn;
    
    --Cleanup
    IF OBJECT_ID(N'#productNames', N'U') IS NOT NULL
        BEGIN
            DROP TABLE #productNames;
    END;
END; 
GO

/***********************************RESULT(formatted for sake of readability)***********************************/
/*QueryStatement
------------------------------------
INSERT INTO #productNames (Product)VALUES ('Microsoft'),('SQL Server'),('Windows'),('Office'),('Sharepoint'),('BizTalk')

Product
-----------
Microsoft
SQL Server
Windows
Office
Sharepoint
BizTalk
*/

Needless to say this approach comes with all the restrictions of row constructor, the prime one being that the number of records is restricted to 1000. The script above can easily be modified to take this into account as well.

Conclusion

In conclusion, all I would say is there are many different ways to approach a problem in SQL Server. The correct approach is one that works for the situation and problem at hand.

Until we meet next time,

Be courteous. Drive responsibly.

The topic for today’s post is one that has frequently come up in the minds of a lot of developers:

Why should you use SERVERPROPERTY(‘ServerName’) over @@SERVERNAME?

Both the @@SERVERNAME and the SERVERNAME property return similar result sets. So, why are two configuration functions/options required?

One look at Books-On-Line for @@SERVERNAME shows why:

“Although the @@SERVERNAME function and the SERVERNAME property of SERVERPROPERTY function may return strings with similar formats, the information can be different. The SERVERNAME property automatically reports changes in the network name of the computer.

In contrast, @@SERVERNAME does not report such changes. @@SERVERNAME reports changes made to the local server name using the sp_addserver or sp_dropserver stored procedure.”

Essentially, what this means is that @@SERVERNAME will return the instance name set by the user. It can end up returning an incorrect server name after a pre-configured/stored disk image is restored (Refer my script: IT Admins-Rename your local SQL Server instance-Ghost image restores, renaming host server). The SERVERNAME property on the other hand, will return the correct servername in this case.

Have a great day ahead!

Further Reading:

• @@SERVERNAME [Link]


• SERVERPROPERTY [Link]

Until we meet next time,

Be courteous. Drive responsibly.