No, seriously, this one is about time.

I recently had an issue in a work database where a query was giving random results, sometimes the query would return a row and other times it wouldn't. There was quite a bit of work distilling the query down to find the reason for this and I'll try to explain by demonstrating what was happening by using some sample data in a table with rather a contrived use case.

Let's assume we have a table that is designed to have a start and end date for something, maybe a working session needs to be recorded, or a maybe its a set of rules that have start and end dates, the key thing is we are working with dates and times; A person starts working and then stops working, a rule becomes enforced at a certain time and ceases to be relevant at a later time. We can safely use a SMALLDATETIME data type for the Start and End columns as we don't need more accuracy.

Let's use our first example and create a table to log when a person is working.

We have a very simple design that enforces us to have an employee number and a start time but the end time can be NULL to allow for a person starting a session but not yet having completed it. Those sessions would be the ones that are in progress.

I use SQL Data Generator from RedGate* to create some random data in the table and then we can start analysing the contents.

With this information we can query the table to see who is currently on their shift or who was on a shift by using an historical date in a WHERE clause as in these two examples

The results show 0 records started before the current date and ended after it and 14 records started before 2012-05-02 12:00:00 and ended after it.

That doesn't seem right.

I generated the data with a fixed percentage of nulls to be inserted into the PersonOut column, there must be some records where the session is still in progress. Lets allow for NULLs in our TSQL ...

There, that's better, we handle the NULL by substituting the @SessionDate value. No, hang on, still no rows?

Let's try that again ...

I kid you not, this is the exact same query, executed against the exact same data only moments after the one above.

What is happening is very subtle and could cause a lot of upset if it occurs in a critical system.

We can see the issue more simply by executing a query based solely on the ISNULL function and passing in different data types.

We don't assign values to the variables so they are all NULL which means that the ISNULL returns the value if GETDATE(). However you will notice that the value of smalldatetime is greater than datetime. Two things are happening:

1. First, the ISNULL function is having an effect on the data type of GETDATE. This is because it can only return one data type and that is dictated by the data type of the first parameter inside the parenthesis. Where the first and second parameters have different data types ISNULL carries out an implicit conversion between them. There is no error here, it silently changes the value if GETDATE into a SMALLDATETIME data type.
2. Secondly the IMPLICIT CONVERSION of the GETDATE() value from it's default of DATETIME to SMALLDATETIME means that the seconds part of the time is taken and used to either round up or round down the minutes part of the time. Anything from 0 to 29.998s means the minutes stay the same, anything over that and the minutes value is increased by 1.

There is full details of these two effects in the BOL pages here;

• ISNULL function - http://msdn.microsoft.com/en-us/library/ms184325
• SMALLDATETIME data type - http://msdn.microsoft.com/en-us/library/ms182418.aspx.

Going back to our code that gives us different results, we get the different results depending on when in the minute we execute the query but, why?

Well, let's substitute fixed values into the code and see

So, there we have it, an IMPLICIT CONVERSION occurring within a function could easily catch out the unwary SQL developer. Knowing what data types are in use in your databases and how you write code to interact with them is important. It's also important to have a thorough understanding of how functions handle data types and may cause unexpected issues to you if you are not paying attention.

* - Disclosure: I am a Friend of RedGate and therefore have a bias towards their products. There are ways to produce demo data using just TSQL and there are other products available. All are very useful but I chose SQL Data Generator this time.

The most common T-SQL command in use has to be the SELECT statement, it is the bedrock of any SQL Professional's day. Sometimes it's used to snatch some data from a table or two while some quick investigation is done, other times it is at the heart of a stored procedure or view that will inform business decisions for coming months or even years.

The latter purpose means you should spend some time making sure it is as efficient as possible. Not endless hours to save a millisecond or two (I mentioned that in my last blog on balancing effort with expected reward) but a little while making sure it is pretty good.

During this time you might read through the MSDN details on the SELECT statement (http://msdn.microsoft.com/en-us/library/ms176104.aspx ) and that might lead you to the Query Hint help (http://msdn.microsoft.com/en-us/library/ms181714.aspx). Now as a diligent DBA you are looking at your execution plans and may want to guide the query to use a particular index that you know exists on the table but for some stupid reason the query optimiser isn't using. This might be a bad idea. It might be a good idea but, it might be a very bad idea. It isn't often that the average DBA knows better than the optimiser.

Lets look at a simple table and a simple query.

CREATE TABLE [dbo].[AccountFlags]
    (
      [AccountsFlagID] [int] NOT NULL
                             IDENTITY(1, 1) ,
      [Account_Ref] [char](7) NOT NULL ,
      [FlagID] [int] NOT NULL ,
      [FlagValue] [bit] NULL
    )
        
ALTER TABLE [dbo].[AccountFlags]        
ADD CONSTRAINT [PK_ACCOUNTSFLAGS]        
PRIMARY KEY CLUSTERED  ([AccountsFlagID]) 
ON [PRIMARY]
                
CREATE UNIQUE NONCLUSTERED INDEX 
[IX_ACCOUNTSFLAGS]                
ON [dbo].[AccountFlags]
([Account_Ref],
[FlagID]) 
ON [PRIMARY]
        
CREATE NONCLUSTERED INDEX [IX_ACCOUNTSFLAGS2]        
ON [dbo].[AccountFlags] 
([Account_Ref],
[FlagID] DESC)
ON [PRIMARY]
    

    
CREATE NONCLUSTERED INDEX [IX_ACCOUNTSFLAGS_IDValue]    
ON [dbo].[AccountFlags] ([FlagID], 
[FlagValue])    
INCLUDE ([Account_Ref]) ON [PRIMARY]

NOTE: Now this post isn't about what indexes to create, how to create them or any such intricacies, it is purely to show how an query hint suggesting a certain index is used can disrupt TSQL execution immediately and in an on-going fashion mislead your decisions and as a DBA. If you want explanations of what indexes to create then you can find plenty of that advise in other blogs and Microsoft content.

On our table we have plenty of indexes covering the columns we are querying so we should be seeing really fast query execution. However, if you have a query that has an query hint in it as follows

SELECT  FlagValue
FROM    dbo.[AccountFlags] AS af WITH ( INDEX ( PK_AccountsFLAGS ) )
WHERE   FlagID IN ( 1, 5 )
        AND Account_Ref = 'HAYNE04'

then the results of running the query in SSMS will be misleading and the effect of running it within a production system will draw heavily on your server's resources

For a start the missing index suggestion will show in the results pane and that missing index suggestion is always the same.

CREATE NONCLUSTERED INDEX []
   ON[dbo].[AccountFlags]
([Account_Ref],[FlagID])

By using the query hint you are forcing the query to run in a particular way and the table doesn't get evaluated for existing indexes so the suggestion is always made to have an index created that would help.

Now the query above has a cost of 10.0609, if we remove the query hint however the cost drops to 0.0075, some 1300 times less effort. This is born out by the execution times of 1196ms for the query with the hint and 26ms for the one without. The results of using SET STATISTICS IO ON is another compelling argument too:

With query hint -
Table 'AccountFlags'. Scan count 5, logical reads 9461, physical reads 167, read-ahead reads 9397, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Without query hint -
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'AccountFlags'. Scan count 1, logical reads 3, physical reads 3, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

This is clearly causing a lot of problems:

1. The index usage statistics show a high occurrences of Primary Key scans.
   Having the index related DMVs in SQL Server has been a godsend to anyone working on indexes, knowing how indexes are being used (or not) and what indexes might be useful if created based on actual usage statistics is great. If the collection of those statistics is skewed by inappropriate query hints then all bets are off. You have no idea what indexes would actually be any use.
2. The missing index DMVs recommend an index that is already in existence but there is nothing preventing that index being duplicated.
   As explained above, having bad statistics is worse than having no statistics. You wont know your  DMV statistics are being skewed by Query hints unless you go and find them so you may well be getting it wrong already.
3. The database is storing an awful lot more data than it might need to.
   If you are following the advice of the missing index information in the query plan then you could be creating index upon index on your tables. These will be having no effect on improving your performance as the query hint will prevent them being used and they will be taking up a lot of space. This is space that is being updated every time an INSERT, UPDATE or DELETE takes place and also affects your Backup and Restore times and may even force you onto more hardware in order to cope.
   
   
   
   
4. The query is still performing badly.
   In this case it is only (!) 1300 times worse than it might be. If you have more complex tables then you will suffer even more.

My advice? Leave Query Hints to extreme cases and apply them with advice from an index expert. Going it alone means you could be making hard work of your data access. Do you hate your servers that much?

I would be deafened by the chorus of DBA's shouting "It depends" at me after a question like that in person, I somehow feel safer from the far side of Live Writer.

I have recently been looking at the number of indexes in one of my databases and have found a curiously high number of indexes in comparison to the number of columns.

Right, all the code now and some discussion underneath it

USE [AdventureWorks]
GO

/*
To locate tables that may be over populated with indexes and 
list them in order of concern.

Jonathan Allen
Dec 2011

*/
IF OBJECT_ID('tempdb..#IndexStats') > 0 
    DROP TABLE #IndexStats ;
WITH    I_Count
          AS ( SELECT   [ddips].[object_id] ,
                        COUNT([ddips].[index_id]) AS [Index_Count]
               FROM     [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL,
                                                           NULL, NULL) AS ddips
               WHERE    [ddips].[index_id] <> 0 
               GROUP BY [ddips].[object_id]
             ),
        C_Count
          AS ( SELECT   [c].[object_id] ,
                        COUNT([c].[object_id]) AS [Column Count]
               FROM     [sys].[columns] AS c
               GROUP BY [c].[object_id]
             ),
        Merged
          AS ( SELECT   OBJECT_SCHEMA_NAME([C_Count].[object_id]) + '.'
                        + OBJECT_NAME([C_Count].[object_id]) AS [TableName] ,
                        [C_Count].[Column Count] ,
                        [I_Count].[Index_Count] ,
                        CONVERT(DECIMAL(4, 2), ( [I_Count].[Index_Count] * 1.0
                                                 / [C_Count].[Column Count]
                                                 * 1.0 )) AS [I/C ratio]
               FROM     [C_Count]
                        INNER JOIN [I_Count] 
                        ON [C_Count].[object_id] = [I_Count].[object_id]
             )
    SELECT  [m].[TableName] AS [Schema/Table name] ,
            [m].[Column Count] AS [Column count] ,
            [m].[Index_Count] AS [Index count] ,
            [m].[I/C ratio] AS [Index:Column ratio] ,
            CASE WHEN [m].[I/C ratio] < 0.1
                 THEN '1 - Low. Having fewer indexes on a table allows Insert, 
Update and Delete performance to remain high but be sure to check for missing 
indexes.' -- 1 in 10 indexes to columns
                 WHEN [m].[I/C ratio] < 0.25
                 THEN '2 - Moderate. There are plenty of indexes, in relation 
to the number of columns, on the table. Consider altering existing indexes rather 
than creating new ones if you need to index data to improve query 
performance.' -- 1 in 4 indexes to columns
                 WHEN [m].[I/C ratio] < 0.5
                 THEN '3 - High. Having lots of indexes will make everything 
except Select queries, run more slowly. Consider removing/merging indexes that 
are similar or get used very little.' -- 1 in 2 indexes to columns
                 WHEN [m].[I/C ratio] BETWEEN 0.5 AND 10 -- 1 to 1 indexes to columns
                      THEN CASE WHEN [column count] < 4
                                THEN '3 - High. Many people recommend having a 
clustered index in every table and then creating a 2nd index is not unrealistic. 
You should be concerned though if there are more than 
this.' -- in a table with < 3 columns it may not be too big an issue
                                ELSE '7 - V High. Having a high ratio of indexes 
to columns could mean you are slowing down an awful lot of your OLTP transactions 
while the indexes and statistics are updated every time the base data is 
altered.' -- You have an average of 1 index per two columns.
                           END
                 ELSE '9 - Really ? ! ' -- Really? more than 10 indexes to columns?!?!?
            END AS [Concern]
    INTO    [#IndexStats]
    FROM    merged AS m
    ORDER BY concern DESC ,
            [m].[I/C ratio] DESC ,
            [m].[Column Count] DESC
    
    
-- analysis
DECLARE @sum INT

SELECT  @sum = COUNT(*)
FROM    [#IndexStats]

-- Summary of index:column ratio stats, by % of tables affected
SELECT  [is].[Concern] ,
        COUNT([Concern]) [Table count],
        CONVERT(DECIMAL(3, 2), 
        COUNT([Concern]) * 1.0 / @sum * 1.0) * 100 AS [Pct_Split]
FROM    [#IndexStats] AS [is]
GROUP BY [is].[Concern]
ORDER BY pct_split DESC
        
--	All index:column details by concern
SELECT  *
FROM    [#IndexStats] AS [is]

So this query collects data regarding column counts per table and then counts indexes per table and then merges (JOINS) them together to compare the details. When I am querying data like this and want to analyse it in a variety of ways I prefer to insert it into a temporary table. A personal preference.

The first extract from the temporary table is purely a summary of the data - how many tables are there in each category of concern? The last query lists the whole data set.

Running this against my (somewhat battered) copy of AdventureWorks we get the results as below

In the summary section we can see that 21 tables (29% of the tables in the database) have an index count that is more than half of the column count. In the detail in the second grid we can see that one table has 5 indexes on a table with 4 columns.

Now I am not promoting any particular ratio of indexes to columns, all I am suggesting is that keeping an eye on this might give you a pointer that you are adding too many indexes and possibly altering existing ones would be a better solution for the performance of your server. Do you have any thoughts on whether this is a good way, or a worthwhile way to review your indexes?

Feel free to alter the percentages in the TSQL to something that you feel more appropriate.

Usual conditions apply. Don't trust this code on your production servers without reading and understanding how it works. You will not get any support if this code does bad things.

Do you know how to find the time that your SQL Server started? Since SQL Server 2008 you can use:

SELECT sqlserver_start_time
FROM sys.dm_os_sys_info

On one of my servers this gives me:

This is great, and can be used in lots of ways. I happened across the [sys].[dm_exec_requests]view the other day and out of curiosity ran the query

SELECT MIN(start_time) AS [start time]
FROM [sys].[dm_exec_requests] AS der

And I was surprised to see the result as:

Almost exactly an hour different. Now as an observant DBA this caught my eye and as both dates are earlier than our last DayLight Saving time change this concerned me.

I went to the server event log for clarification of the details - This is easy using LogParser by running the command logparser "select top 100 * from \\SERVERNAMEHERE\system where eventid = 12" -o:datagrid -i:evt) and got the result of:

Now this seems to prove the value from dm_exec_requests as accurate and makes the value from dm_os_sys_info erroneous and in fact impossible - a service cannot have been running for an hour longer than the operating system.

At this point I opened a connect item with Microsoft - https://connect.microsoft.com/SQLServer/feedback/details/708164/dm-exec-requests-data-conflicts-with-dm-os-sys-info

The network admin team however did a bit of digging and found a different way of querying when the server restarted. Executing the command NET STATISTICS WORKSTATION at a DOS prompt on the server in question returns the detail that the server thinks it actually started at 28/09/2011 10:14:57. This more closely aligns with the value from dm_os_sys_info, and certainly makes it a valid date/time anyway.

On servers that have restarted since the clocks changed here (Oct 30^th 2011) this anomaly is not seen.

Please feel free to run the 4 tests on any of your servers to check whether SQL Server is using the right or wrong start time in your systems and if you feel so inclined your vote for the connect item would be much appreciated.

Many thanks to some very helpful DBAs who have already run these scripts to help me confirm I wasn't seeing things; John Martin (twitter.com/sqlservermonkey), Phil Jones (twitter.com/philjones88 | http://psjones.co.uk/), Thomas Rushton (twitter.com/thomasrushton | http://thelonedba.wordpress.com) and Naomi Alexander (twitter.com/nomi_wilts). All of whom, reported seeing similar differences in the values from their systems as I did.

[UPDATE - 07-Dec-2011] I have just had the following advice from Microsoft:

sys.dm_od_sys_info stores the value in UTC and converts it to local time. Therefore it changes with daylight savings and timezone. It will get fixed. Is this causing problems for you or was it just something you noticed?

I have covered SQL Server Alerts (Alerts are good, arent they?) on this blog before and I more recently did a post regarding Notifications (Are your Jobs talking to you) and how they should be configured. Now we need to check that these things are linked up so that when an Alert condition is met that you get the appropriate Notifications sent to Operators.

Straight into the code we need and then a review of what it does ...
DECLARE @ChosenOperator SYSNAME
DECLARE @FailSafeOp TABLE
    (
      AlertFailSafeOperator NVARCHAR(255) ,
      AlertNotificationMethod INT ,
      AlertForwardingServer NVARCHAR(255) ,
      AlertForwardingSeverity INT ,
      AlertPagerToTemplate NVARCHAR(255) ,
      AlertPagerCCTemplate NVARCHAR(255) ,
      AlertPagerSubjectTemplate NVARCHAR(255) ,
      AlertPagerSendSubjectOnly NVARCHAR(255) ,
      AlertForwardAlways INT
    )
-- Table to hold results of procedure to query server settings
INSERT  INTO @FailSafeOp
        EXEC MASTER.[dbo].[sp_MSgetalertinfo] @includeaddresses = 0
        
IF EXISTS ( SELECT  1
            FROM    @FailSafeOp AS fso
            WHERE   [fso].[AlertNULLafeOperator] IS NOT NULL )
    SELECT  @ChosenOperator = AlertFailSafeOperator
    FROM    @FailSafeOp AS fso
ELSE
    RAISERROR('No FailSafeOperator found. You should alter your SQL Agent settings to include one.',16,0,1) WITH NOWAIT
-- Assumes that the Operator "DBA_Team" exists and has an email address detail
SET @ChosenOperator = ISNULL(@ChosenOperator, N'DBA_Team' )
-- Output the results of whether there are Alerts with no assigned operator notification
SELECT  [s].[name] ,
        [s].[severity] ,
        ISNULL([s3].[name], '| = - No operator assigned - = |') AS [Operator] ,
        CASE WHEN [s3].[name] IS NULL
             THEN 'EXEC [dbo].[sp_add_notification] @alert_name = N'
                  + QUOTENAME([s].name, '''') + ', @operator_name = N'
                  + QUOTENAME(@ChosenOperator, '''')
                  + ', @notification_method = 1'
        END AS [Add Notification TSQL] ,
        [s3].[enabled] ,
        [s3].[email_address] ,
        [s].[occurrence_count] ,
        CASE [s2].[notification_method]
          WHEN 1 THEN 'Email'
          WHEN 2 THEN 'Pager'
          WHEN 3 THEN 'netsend'
          WHEN 7 THEN 'All'
          ELSE CONVERT(CHAR(30), [s2].[notification_method])
        END AS [Notification Method]
FROM    [dbo].[sysalerts] AS s
        LEFT  JOIN [dbo].[sysnotifications] AS s2 ON [s].[id] = [s2].[alert_id]
        LEFT JOIN [dbo].[sysoperators] AS s3 ON [s2].[operator_id] = [s3].[id]
WHERE   [s].[enabled] = 1
-- include this line if you want to only see problem alerts
        AND [s3].[id] IS NULL

So this code creates a temporary table variable to hold operator information briefly;
runs the undocumented sp_msgetalertinfo stored procedure (some details here) to get failsafe operator data from the registry on the server;
queries sysalerts, sysnotifications and sysoperators to check that all alerts are set to notify operators

Where there is no operator being notified then the [Add Notification TSQL] column will have the TSQL to create a notification to the failsafe operator, if there is one.

As always, understand this code, especially the way that you can't rely on undocumented stored procedures to be consistent or reliable, before you run this on any of your servers, especially those that hold information that is precious to you.

Most DBAs will have at least a couple of servers that have SQL Agent jobs that are scheduled to do various things on a regular basis. There is a whole host of supporting configuration settings for these jobs but some of the most important are notifications.

Notification settings are there to keep you up to date on how your job executions went. You have options on types of notification - email, pager, net send, or an entry in the SQL Server Event Log and you get options on when each of these channels gets a message - on completion, on successful completion, on failure, or no action. If you know all about the creation of operators and notifications then feel free to skip down to the last section where there are a couple of sections of TSQL that will help make sure you keep everything configured right.

The notifications are really easy to configure and are well worth the few clicks it takes. first things first, you need an Operator or two set up. These are objects in SQL Server that represent people or better yet, teams of people, on your network that have an interest in the job status. It is best to set up operators that represent a team, with a group email account so that regardless of staff changes and occasional absences the notification messages will reach someone, every time.

As you can see on my server there are 3 operators, two sadly appear to be individuals but at least one refers to a team.

Now, back to the jobs. On this job we are configuring the notifications to go to the DBA_Team operator. We have ticked we want an email to be sent on the condition that the job fails and we want an entry in the SQL Event Log whenever the job completes.

This means anyone in the DBAteam email group will get told if the job fails to complete successfully and every time the job completes (either successfully or not) the Event Log will be updated.

Now knowing this is all well and good, and from now on all your jobs can be set up with this sort of scheme in place but what about if you take over a server that hasn't been configured quite right previously or you have been learning on the job since the last DBA left. There may be hundreds of SQL Agent job with dozens of operators and it would be a life's work to check every one via the SSMS interface. How about a script then?

USE [msdb] 
GO 

-- list all jobs in order of notification settings. 
-- jobs at the top of the list may need more rapid attention than those lower down 
SELECT  [j].[name], 
        CASEWHEN[j].[notify_level_eventlog]=1THEN'On success' 
            WHEN[j].[notify_level_eventlog]=2THEN'On failure' 
            WHEN[j].[notify_level_eventlog]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Event Log Entry], 
        CASEWHEN[j].[notify_level_page]=1THEN'On success' 
            WHEN[j].[notify_level_page]=2THEN'On failure' 
            WHEN[j].[notify_level_page]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Page Alert], 
        CASEWHEN[j].[notify_level_netsend]=1THEN'On success' 
            WHEN[j].[notify_level_netsend]=2THEN'On failure' 
            WHEN[j].[notify_level_netsend]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Net Send Alert], 
        CASEWHEN[j].[notify_level_email]=1THEN'On success' 
            WHEN[j].[notify_level_email]=2THEN'On failure' 
            WHEN[j].[notify_level_email]=3THEN'On completion' 
            ELSE'| = - No alert - = |' 
        END AS[Email Alert] 
FROM    [dbo].[sysjobs]ASj 
ORDER BY([j].[notify_level_email]+[j].[notify_level_eventlog] 
          +[j].[notify_level_page]+[j].[notify_level_netsend])DESC 

If you want to check up on the operators on your server then this is another useful piece of code:

--	list all operators that are either not enabled or are not set to 
--       receive any notifications
SELECT  [o].[name] ,
        CASE WHEN [o].[enabled] = 0 THEN '| = - Not Enabled - = |'
             ELSE 'Enabled'
        END AS [Enabled] ,
        CASE WHEN [o].[email_address] IS NULL THEN '| = - No details - = |'
             ELSE 'Enabled'
        END AS [Email Details] ,
        CASE WHEN [o].[pager_address] IS NULL THEN '| = - No details - = |'
             ELSE 'Enabled'
        END AS [Pager details] ,
        CASE WHEN [o].[netsend_address] IS NULL THEN '| = - No details - = |'
             ELSE 'Enabled'
        END AS [NetSend Details]
FROM    [dbo].[sysoperators] AS o
WHERE   [o].[enabled] = 0
        OR ( [o].[email_address] IS NULL
             OR [o].[pager_address] IS NULL
             OR [o].[netsend_address] IS NULL
           )
ORDER BY [o].[enabled]

The results of this script show which operators are cued up for what sort of alert, if any at all. Ted appears to be getting it easy currently.

As always, understand any script you get from the internet before you execute it on your own servers.

If you haven't already heard, Red Gate are running some big events currently - there is DBA in Space where a lucky prize winner will win the chance to take a flight to the edge of space, all the details on how to enter are at www.dbainspace.com. On a more accessible level they also have the SQL in the City event in Los Angeles on 28th October, all the details (along with a video from the first event in London earlier this year) are at www.sqlinthecity.com.

At the SQL Relay event last week all the UK user group leaders did a combined session - The A to Z of SQL - where we all took two letters of the alphabet and gave a 2 minute (it was strictly timed) talk on something SQL related beginning with those letters. It was quite a riot working through 26 different talks in an hour with 25 speaker handovers and the associated switches between SSMS and the slide deck. As a speaker I thoroughly enjoyed it and i hoe we informed as much as  we entertained the audience in Microsoft London HQ.

Ironically, writing this up has already taken longer than the talk did but any way, here is the code from my demo and some text to try and take the place of my explanation on the night.

*-* Warning. this information changes settings in SSMS that can seriously affect *-*
*-*      the way you work with TSQL if you don't undo the changes correctly.       *-*
*-*                                   You follow this content at your own risk.                         *-*

We all know the GO key word as a batch separator, it sits at the bottom of a piece of TSQL and apart from that we don't do very much with it.

USE [AdventureWorks]
GO 

SELECT TOP 5
        *
FROM    [HumanResources].[vEmployeeDepartment] AS ved

GO

If we declare a variable in one batch, it is not available in a different batch. The second part of this script will error as the variable has not been declared in the second batch.

DECLARE @Dept1 CHAR(4) = 'Prod'

SELECT TOP 5
        *
FROM    [HumanResources].[vEmployeeDepartment] AS ved
WHERE   LEFT([Department], 4) = @Dept1

GO

SET @Dept1 = 'Tool'

SELECT TOP 5
        *
FROM    [HumanResources].[vEmployeeDepartment] AS ved
WHERE   LEFT([Department], 4) = @Dept1

SELECT  ABS(CHECKSUM(NEWID()))% 49 + 1 AS RandomNumber
GO

Also, if we add an integer after GO SSMS executes the batch that number of times. So, to get 8 random numbers, with a half second pause between them we would use

SELECT  ABS(CHECKSUM(NEWID()))% 49 + 1 AS RandomNumber
WAITFOR DELAY '00:00:00.5'
GO 8

But GO is something particular to SSMS, it is not part of the TSQL ANSI standard, Microsoft added it for convenience.
This means we can actually choose whether it is 'GO' or something else. In SSMS select the Tools menu and then Options and then select the Query Execution tab...
See where is says GO? How about changing that to '88'? Click OK when you have and then close and reopen SSMS.

Once this is done the following code will parse successfully

SELECT TOP 5 * FROM [HumanResources].[vEmployeeDepartment] AS ved

88

This means that some obscure piece of code like this will parse and execute successfully

SELECT 2 
88 3

SELECT 2 
GO 

Despite being on a different line and the fact that the 'AS' keyword is missing, the above code completes without error. The GO in the code is used as a column name.

*-* Be sure to change the batch separator back to GO before you close SSMS otherwise you will have some infuriating moments when you are next working with TSQL and finding it wont parse or execute successfully *-*

I have just had an email from Mark Ginnebaugh welcoming me to the role of PASS UK Regional Mentor. This was a role previously carried out by Simon Sabin but he has recently stepped down due to other pressures on his time so Chris Testa O'Neill and I have been appointed. There are 16 user groups in the UK currently and they are all taking part in SQL Relay on 3rd to 6th Oct. If you can get to any of the meetings then please come along and help boost the SQL Community in your area. I'm looking forward to working with Chris and the PASS Chapters in the UK for the time that I am a Regional Mentor and supporting them in any way I can. If you see me at SQLBits don't be shy, come up and have a chat, if you don't see me then please do make an effort to visit Community Corner where other user group leaders will be talking to conference attendees and explaining the benefits of joining your local user group.