The debugger of any IDE is a very powerful tool because it gives developers the insight on how their program or script is functioning. I have throughout been a SQL person, and have always felt that at some stage, Microsoft SQL Server did not deliver at par with it’s Visual Studio counterparts. This was despite the fact that the SQL Server Management Studio (SSMS) was a derivative of Visual Studio.

However, with the upcoming release of SQL 11 (“Denali”), I have no complaints. The new SSMS is a derivative of the VS2010 shell and therefore extends the same debugging experience (with the obvious differences related to technology) that it would deliver to let’s say a C# developer. The Visual Studio debugger provides a variety of tools for inspecting and modifying the state of a script. Obviously, these tools function only in break/debug mode.

We have recently been discussing about a couple of features related to Breakpoints. If you missed them or would like a refresher, you can click on the respective links below:

• Setting, Labeling, Using & Searching Conditional Breakpoints
• Export/Import Breakpoints
• “When Hit” do something

Today, we will be looking at something called “Data Tips”. We will be continuing the example that we used last time after a bit more simplification.

DECLARE @iCounter INT = 0

WHILE (@iCounter <= 20)
BEGIN
    PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    SET @iCounter += 1
END
GO

Data Tips

Per MSDN, “DataTips are one of the most convenient tools for viewing information about the variables and objects in your program during debugging. When the debugger is in break mode, you can view the value of a variable within the current scope by placing the mouse pointer over the variable in a source window.”

I like to think of it as a very light-weight version of the Watch window. When debugging, almost any troubleshooter likes to be in full control – knowing exactly what is going on where. If a variable changes it’s value, it might be important because it may not be warranted and might produce incorrect results. Data Tips help us in being “aware” about our surroundings, and at times, in controlling the flow of execution.

Please note that to the best of my knowledge, Data Tips are available in all the flavours of Visual Studio 2010.

To Display A Data Tip

Start a debug session
However the mouse over the variable to be evaluated, a Data Tip appears

To Move, Pin and Unpin A Data Tip

Click the “pin” icon
The data-tip is now “pinned”
Drag the data tip to the desired location to move it
Hover over the data-pin and click the “pin” icon again to un-pin the data-tip

Adding Comments to a Data Tip

Click the “Expand” arrow on the data-tip
Add required comments in the edit box
Click anywhere on the “Canvas”

To Close all Data Tips

We have not one, but 3 distinct options to clear out the data-tips on a Microsoft SQL Server Query Editor window.

Option #1 – Debug Menu
Option #2 – The Data tip itself
Option #3 – Right-click the “indicator” column

Later this week, we will see:

• Preservation of data tips after debug session is over
• Editing data in a data-tip
• Exporting & Importing data-tips

Finally, I will summarize which of the features I think I will be using the most once SQL 11 (“Denali”) hits production.

Until we meet next time,

Be courteous. Drive responsibly.

During Tech-Ed (read my 3 part series here – Part 01, Part 02, Part 03), I learnt about some of the cool new T-SQL debugging enhancements in the next public release of Microsoft SQL Server, SQL 11 (Code Named: “Denali”). I am currently summarizing these enhancements, and will finally provide a ranking of which features I liked best and would tend to use more often once Denali goes into production.

We are currently looking at debugger enhancements related to breakpoints. In the recent posts, we have seen how to setup conditional breakpoints, label them & search for them. We also saw how to export breakpoints from one SSMS session & import them into another SSMS session to stop & continue debugging. Today, we will look at the final debugging enhancement related to breakpoints – “When Hit” do something.

Let’s say that after considerable debugging, you are almost certain that a particular piece of code isn’t the real culprit. You do not want to break anymore when it executes, but yet want to keep an eye on it. The most common solution earlier was to have the expression added to the watch window. But, the watch window only displays the current value. What if you need a log of the various values along with information like the thread/process id? The only option was to introduce custom logging – which may not always be convenient or possible (as it might disturb the arrangement of the existing breakpoints).

“When Hit” do something

Let’s continue our debugging example from last week. For your reference, the script being used can be found below. We had placed conditional debug points at each PRINT statement in the script – one was conditional, while the other was a hit-count based breakpoint.

DECLARE @iCounter INT = 0

WHILE (@iCounter <= 20)
BEGIN
    PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    PRINT 'Hello Nakul! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    SET @iCounter += 1
END
GO

Now, let us see how we can ask SSMS to do something when a particular breakpoint is hit.

Right-click the breakpoint in question, and choose the “When Hit” option
The “When breakpoint is hit” window comes up Notice that we have customized the message (details below) and also chosen to “Continue execution”
When you hit “OK”, notice that the shape of the breakpoint indicator changes
Now, when you debug the script, notice that execution does not stop on breakpoint #2
When the debugging ends (or even as it is progressing), analyze the “Output” window

As you can see, the “When breakpoint is hit” settings cause the custom message with the appropriate debug values to come up in the Output window. That way, while the execution does not stop at a particular set of breakpoints, you can still keep an eye on what’s going on at those points.

(For those who are wondering why the values for @iCounter are coming up as 3, 7, 11… when we set breakpoint to hit in multiples of 4, the simple reason is that we started the loop at @iCounter = 0)

Message options

Notice that the message in the “When breakpoint is hit” window can be customized. One can use the following keywords:

• $ADDRESS = Current instruction
• $CALLER = Previous Function name – useful when debugging nested SPs and triggers
• $CALLSTACK = Call stack
• $FUNCTION – Current Function Name
• $PID, $PNAME = Process Id and Name
• $TID, $TNAME = Thread Id and Name

Or, have the value of a given expression printed by placing the expression within curly braces “{}”, as we have done in this example – “The value of @iCounter is {@iCounter}.”

Conclusion

Overall, debugging in SQL 11 (“Denali”) is a very welcome, drastic improvement over the prior releases. Enhancements to breakpoints, especially “When hit do something” are some which I see myself using a lot once SQL 11 is in production.

Until we meet next time,

Be courteous. Drive responsibly.

During Tech-Ed (read my 3 part series here – Part 01, Part 02, Part 03), I learnt about some of the cool new T-SQL debugging enhancements in the next public release of Microsoft SQL Server, SQL 11 (Code Named: “Denali”). I am currently summarizing these enhancements, and will finally provide a ranking of which features I liked best and would tend to use more often once Denali goes into production.

We are currently looking at debugger enhancements related to breakpoints. Last week, we saw how to setup conditional breakpoints, label them & search for them. In today’s environment, it is common to have a team of individuals working on a particular feature/enhancement. Such a team of individuals would end up debugging a piece of code together, or on behalf of one another. In production support, with complex systems and interfaces, it is common to have an issue that continues to linger for multiple days.

When debugging takes time, it is beneficial if the debugging effort is split across multiple sessions or multiple machines. With SQL 11 (“Denali”), this is now possible.

Exporting Breakpoints

Let’s continue our debugging example from last week. For your reference, the script being used can be found below. We had placed conditional debug points at each PRINT statement in the script – one was conditional, while the other was a hit-count based breakpoint.

DECLARE @iCounter INT = 0

WHILE (@iCounter <= 20)
BEGIN
    PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    PRINT 'Hello Nakul! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    SET @iCounter += 1
END
GO

Now, let us see how to export breakpoints from one SSMS session to another.

Once the script file has been saved to any location of your choice, click on the “Export” button on the toolbar of the “Breakpoints” window
In the File->Save dialog, select a location and file name of your choice to export the breakpoints

Importing Breakpoints

Now that we have exported breakpoints to a file, let’s see how we can import them into another SSMS session.

Launch SSMS and open the query to debug. Launch the breakpoints window (Debug –> Windows –> Breakpoints, or Ctrl + Alt +B) and click the Import button:
In the File->Open dialog box, choose the breakpoints file saved above
Notice that the breakpoints are imported and applied successfully such that the IDE is now ready for debugging

Limitation

As with any enhancement, there are always things that can be improved upon after the fact. The only major limitation that I see with exporting & importing breakpoint is:

• The name & location of the script file must be same as that when the breakpoints are exported
• Therefore, if when the breakpoints were exported, the script file was at “E:ScriptsBreakpoints.sql”, I cannot change it to be “E:DevelopmentBreakpoints.sql” for the import

However, I do not see this as a limiting factor because in an heterogeneous environment with a central IT team, developers tend to work on similar/same code paths and directory structures.

For those who missed the previous post on debugging enhancements, please click here.

Until we meet next time,

Be courteous. Drive responsibly.

Way back in March 2011, I attended Tech-Ed India 2011 (read my 3 part series here – Part 01, Part 02, Part 03). During Tech-Ed, I learnt about some of the cool new T-SQL debugging enhancements in the next public release of Microsoft SQL Server, SQL 11 (Code Named: “Denali”). In the next couple of posts, I will attempt to summarize these enhancements, and finally provide a ranking of which features I liked best and would tend to use more often once Denali goes into production.

Why so late?

Some of you might be thinking – “Why is this guy posting this stuff so late? We are already in CTP03!” Well, the answer is simple – I believe in using a feature in practice before writing about it. Debuggers are a very powerful set of tools, and it would not be fair to write about something if I could not provide a few helpful tips & pointers.

As a  background, you may read my previous posts on T-SQL debugging:

• T-SQL Debugging: A hands-on
• Enabling the T-SQL Debugger
• T-SQL Debugger: Connection Errors & Firewall settings
• T-SQL Debugger: SSMS error – MSDBG2.dll

It is important to remember that the SQL Server Management Studio for SQL 11 “Denali” is based on the Visual Studio 2010 shell. Hence, the debugging features of VS2010 “magically” flow into SSMS.

Debugging in Denali – Breakpoints

Test Script for this session

We will debug the following script in this session on SQL 11 (“Denali”) CTP03. It is a very simple script that loops through for 20 iterations.

DECLARE @iCounter INT = 0

WHILE (@iCounter <= 20)
BEGIN
    PRINT 'Hello World! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    PRINT 'Hello Nakul! The counter reads: ' + CAST(@iCounter AS VARCHAR(2))

    SET @iCounter += 1
END
GO

Creating Conditional Breakpoints & Labeling them

One of the major features that was missing in the earlier versions of SSMS was the ability to have conditional breakpoints. In SSMS for Denali, conditional break-pointing is possible. To explore it’s features, let’s start debugging by pressing the F10 key as high-lighted in the Hands-On session. I would re-iterate that debugging in SSMS is not at all different than debugging in VS2010.

To demonstrate the fact, let’s place a breakpoint near the line for “Hello World!”. We will make this a conditional breakpoint, to break only if the value of the counter hits 10.

Hit F9 to setup the breakpoint on the desired line Note that the information in the “Breakpoint” window is non-descriptive. Let’s correct that first
Right-click on the breakpoint, and choose “Edit Labels”
In the “Edit Labels” window, type in a descriptive detail and click “Add” If pre-existing, you can choose one if you like
On clicking “OK”, notice that the breakpoint can now be identified easily amongst a pile of breakpoints
Now, right-click on the breakpoint and go to “Condition”
Add the required expression as a condition and click “OK”
Notice that in both the “Breakpoints” window &  editor window, the breakpoint symbol now has a little "plus (+) sign in it
Let’s create a “Hit Count” based breakpoint for the second line
For the test, let’s set this breakpoint to hit at intervals in multiples of 4 – at 4, 8, 12,16 & 20. Hit OK once done to set the breakpoint
The Breakpoints window would look like the one in the screenshot

Hit “Alt + F5” to run through the sample code. The first breakpoint hit will be “Hello Nakul!” when the hit count is 4. To verify whether the hit is correct or not, you can either use the “Locals” window or the “Immediate” window (Go to Debug –> Windows –> Immediate or press Ctrl+Alt+I):

Hit Alt+F5 again. The next breakpoint to be hit will again be “Hello Nakul!”. Hit Alt + F5 again, and we will hit the “Hello World!” breakpoint and so on.

Searching for specific breakpoints – using Labels to your advantage

Go to Debug->Windows->Breakpoints or press Ctrl+Alt+B to launch the Breakpoints window.

While we only have 2 breakpoints in this session and it is quite easy to remember them, in production code, there might be tens of breakpoints and it might be difficult to remember each one. What’s more, out of all breakpoints, you might want to work with only the breakpoints that are related to a particular operation – an update to a given table, for example.

In the breakpoints window, take the time out to label each breakpoint carefully. You may want to use your labels such that each label identifies a particular group of operations in question – for example, a label can be – “Updating Production”, “Updating Sales Orders” and so on.

Notice the addition of a “Search” box in the Breakpoint window. The “Search” box allows us to search on a specific column or on all visible columns of the breakpoint window.

Choosing “All Visible” in the “In Column” drop-down, let’s type in “World” in the “Search” box and hit Enter. Notice that SSMS will take the other breakpoints off the list. Clearing the search results will bring them back.

Concluding for today…

I believe that we have just barely scratched the surface of the debugging features available in SQL 11 (“DenaIi”). In my next post, I will cover how to Export & Import Breakpoints – a boon in collaborative development environment. After that, I will cover “Data Points”, which is in itself a very exciting development to the debugger.

Until we meet next time,

Be courteous. Drive responsibly.

Whenever we talk about inclusive date comparison & filtering in T-SQL, the only two options that come to mind are the BETWEEN clause and the greater than (>) and less than (<) operators paired with the equal to operator. Today, let us attempt to find out if there is a difference between the two.

Let me mention that our study today will be restricted to dates only. DATETIME values need extra “precautions” on the part of the query developer to cover the time portion in the comparison. However, most legacy systems (and as we would see from the examples in the AdventureWorks2008R2 database), use separate storage for date and time values and therefore, the need to filter on the date value is a very realistic user scenario.

Dissecting BETWEEN

Without spending much time, let’s get right along to the simple test. Let’s assume that once a year, the Adventure Works Bicycle Company prepares a list of all employees who were hired in the organization 10 years ago, (because we are in 2011, this would be in 2001) to send out an organization-wide recognition of service notification. Quite simply, the query would be something like:

SELECT * FROM HumanResources.Employee
WHERE HireDate BETWEEN '2001-01-01' AND '2001-12-31'
GO

We get 2 records in return:

Now, let us study the actual execution plan to find out what goes on under the covers.

As it turns out, BETWEEN is internally implemented by pairing a set of comparison operators with the equal to operator!

Conclusion

We can safely conclude that BETWEEN is just a facade for the comparison operators. The only difference is in the fact that comparison operators, by themselves offer exclusive comparison (i.e. the comparison excludes the value being compared to). For an inclusive comparison, they need to be paired with the equal to (=) operator – something which the BETWEEN does out-of-the-box.

So is BETWEEN really required?

I studied the Books-On-Line pages for BETWEEN and the Comparison operators, and could not find any difference other than the one mentioned above (about BETWEEN being inclusive). So, the big question now becomes whether BETWEEN is really required? Wouldn’t our code be much better if we avoid the need for Microsoft SQL Server to transform the between into comparison operators?

Well, in my humble opinion, that’s where the issue is. We, as developers and ultimately, humans, tend to make mistakes. The compilers warn us of syntactical issues with our code, but there is nothing that would warn us about a logical bug. A single miss in placing an equal to operator (or placing of an operator in excess), might result in code that would be logically incorrect. Using BETWEEN when we need inclusive comparison just helps in avoiding such errors.

So, BETWEEN is required – it’s not mandatory, but recommended. And that’s why it’s a T-SQL coding best practice.

Interested in more such surprises?

If you are interested in more such surprises, please do check out my post on the differences between CAST & CONVERT here: http://beyondrelational.com/blogs/nakul/archive/2011/07/18/cast-v-s-convert-is-there-a-difference-as-far-as-sql-server-is-concerned-which-is-better.aspx.

Until we meet next time,

Be courteous. Drive responsibly.