Shortcut: Finding error locations within queries in SQL Server Management Studio

This is probably one of the simplest tips that I've published, yet I'm endlessly surprised how many people do not realize that it's available.

When you have a script loaded in SQL Server Management Studio (SSMS), and you execute the script, you might run into an error like this:

To find where the error is, just double-click the error down in the Messages tab. I double-clicked it, and it took me directly to the error and highlighted it:

Shortcut: Change the number of rows selected or edited in Object Explorer in SSMS

When you right-click a table in SQL Server Management Studio, you get options for selecting or editing but the number of rows is limited:

Those values can be changed. By default, these numbers are both 200, but I've decided to change the default number of rows selected to 1000.

In Tools, Options, SQL Server Object Explorer, then Commands, you can set the values to whatever suits you:

I don't tend to ever use the Edit option but I'd suggest not making it too large.

SDU Tools: Is XACT_ABORT on in my SQL Server session?

XACT_ABORT is one of the least well understood options that you can configure in a SQL Server session. Yet it's very important. XACT_ABORT makes statement-terminating errors become batch-terminating errors. Without it, even within a transaction, many errors only terminate the statement that they occur in, and control passes to the next statement within the transaction, not out of the transaction.

In nearly every stored procedure that I write, the template includes the following lines:

SET XACT_ABORT ON;
SET NOCOUNT ON;

In our free SDU Tools for developers and DBAs,  we added a function IsXactAbortOn to let you determine in code, if it's enabled.

You can see the outcome in the main image above.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL Server Indexing for Developers – new online on-demand training course

Do you need to understand SQL Server indexing in detail? Do you know someone else who needs to? Either way, our new SQL Server Indexing for Developers course could be just what you're looking for.

It's detailed. It has a full set of practical exercises (hands-on labs) with downloadable test data and scripts.

The best part is it's on sale until October 31st for $195 USD as an introductory special.

What Greg will teach you:

  • How SQL Server indexes really work
  • How to design effective SQL Server indexes
  • Why SQL Server seems to be ignoring the indexes that you designed
  • How data type choices affect indexes
  • If filtered indexes would work better in your application
  • How to check whether or not SQL Server "likes" your index
  • How to avoid having too many indexes (and how many is too many)
  • Which indexes you can safely remove

You'll find more info here:

https://training.sqldownunder.com/p/sql-server-indexing-for-developers

 

Shortcut: XML editor in SSMS and increasing XML output size

Most people use SQL Server Management Studio (SSMS) to edit SQL queries. No big surprise there. The files will have a file type of .sql.

But what many people don't understand is that SSMS inherits many of its underlying Visual Studio's abilities to edit other document types.

For example, if you open a .txt text file, you can edit it just fine, and you can also include files like this in SSMS script projects. That can be useful for additional notes and documentation.

SSMS also knows how to open related file types like .sqlplan (for query plans) and .xdl files (for deadlocks), and more.

Most of these other file types though, are actually XML files with specific schemas constraining their contents. SSMS also contains a perfectly acceptable XML editor.

Here's an example:

If I execute the above query, the outcome is some XML. Note that SSMS recognizes that the output data type is XML and then provides a hyperlink for opening it. If I click on the link, I see this:

The important item to notice here though is the red squiggly on the second line. If we hover over that, we'll see this:

XML documents can only have a single root element. This XML is actually a fragment, not a complete document, and so it thinks that all the stock item lines are all root elements.

The important thing is that this is an XML editor, not just an XML viewer. Notice that when an XML file is open, an XML menu also appears:

Now, while it's not a bad XML editor, it has limits on the size of the data that you can work with, but you can control that too. In Tools, Options, Query Results, SQL Server, Results to Grid, you can see this:

By default, you are limited to 2MB of XML data. You can increase this to unlimited but keep in mind that SSMS is (unfortunately) still a 32 bit application and can struggle to work with gigantic files.

SDU Tools: Is a SQL Server Agent Job Running ?

I often need to write scripts that interact with SQL Server Agent jobs. One thing that I regularly need to know is if a particular job is currently running.

In our free SDU Tools for developers and DBAs,  we added a function IsJobRunning to work that out.

You can see the outcome in the main image above.

You can see it in action here:

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL Server Q&A: Does SQL Server have a boolean data type? Is that a bit?

Simple answer: No

SQL Server has a concept of boolean data type but it doesn't have an actual boolean data type. I can't declare a column with a boolean data type, and I can't use it that way. However, the concept of boolean data is present.

For example, if I write the following:

SELECT *
FROM Sometable
WHERE Somecolumn = Someothercolumn

the outcome of that comparison is TRUE, FALSE, or (don't forget) NULL. And remember that NULL isn't a value; it's the lack of a value. WHERE clauses are satisfied when the value of the predicate is TRUE.

So, there is a boolean concept in the language.

However, note that I cannot write this:

SELECT Somecolumn = Someothercolumn AS TheSameValue
FROM Sometable

I can't just return a TRUE or FALSE value.

If I write it without the alias, it will work:

SELECT Somecolumn = Someothercolumn AS TheSameValue
FROM Sometable

but in that case, I'm returning the value of Someothercolumn aliased (named as) Somecolumn. I'm not returning the comparison.

But isn't that just the same as a bit?

No. I can't use a bit value like a boolean. For example, I can't just write this:

IF ISNUMERIC('somestring')
BEGIN

END

Instead, I have to write this:

IF ISNUMERIC('somestring') <> 0
BEGIN

END

Note that I could have written "= 1" but I prefer with bit values to always compare them to zero across all languages, given some languages use -1 for TRUE and others use 1 for TRUE.

 

Shortcut: Connecting to Azure Storage and other services in SSMS

SQL Server Management Studio (SSMS) is a great tool for working with SQL Server relational databases but it can do much more than that.

In Object Explorer, note that you can easily connect to other types of services:

For a long time, it has been able to connect to Analysis Services to manage SSAS databases, both tabular and multi-dimensional. It can connect to Integration Services but that's to the older style interface for SSIS. Nowadays, you should use the SSIS Catalog instead. There are a few items that you can configure via the Reporting Services connection as well.

One option that is often quite unexpected though, is that you can connect to Azure Storage. Here's an example. After I click Azure Storage, I'm prompted for the storage account and the key. This can be the primary or secondary account key. (Note that it can't just be a shared access signature connection).

From there, once the connection is made, you can drill into the containers and their contents:

Generally, I would use Azure Storage Explorer to manipulate these storage accounts, but this option can be useful if you are using BACKUP TO URL and you need to just check the backup files that you are creating.

SDU Tools: Convert a hexadecimal character string to an integer in T-SQL

I mentioned in a previous post about how I sometimes need to work with binary strings in SQL Server using T-SQL.  The literal values are hexadecimal strings. T-SQL doesn't have a simple function to just convert one of these character-pair strings to an integer.

In our free SDU Tools for developers and DBAs, as well as the HexCharStringToChar function, we added a function HexCharStringToInt to do just this.

You can see the outcome in the main image above.

You can see it in action here:

 

To become an SDU Insider and to get our free tools and eBooks, please just visit here:

http://sdutools.sqldownunder.com

SQL: Implementing Optimistic Concurrency in SQL Server with RowVersion

It's common to need to have a way to read a row of data from a table, to be able to modify it, and to then update it back into the table, but only if it hasn't been changed in the meantime. But we don't want to lock it in the meantime. That's the basis of optimistic concurrency. By contrast, pessimistic concurrency would hold locks the whole time. In SQL Server, you should use the rowversion data type to implement optimistic concurrency.

Early versions of SQL Server and Sybase had a timestamp data type. That was a really poorly named data type because it had nothing to do with time. It was an incrementing binary value that could be used to implement optimistic concurrency. Since SQL Server 2005, it has been renamed to the rowversion data type. That's good because it more accurately describes what it actually is.

Let's take a look:

First I'll create a table that uses a column that has a rowversion data type.

I've called the column Concurrency but it could be called any standard column name. It would be good though, if you named these columns consistently across your tables where you use them. Now let's insert some data.

Note that I didn't include the Concurrency column in my INSERT statement. You can't insert directly into that column. Let's see what got stored.

You can see that a different value was stored in each row. The values are actually quite predictable and the binary value just keeps increasing. Now let's look what happens when we update a row.

The value in the updated row has increased. Any time the row is modified, the value changes. But what if you make a change but leave the value the same?

Notice that the value changed again. So the issue isn't whether or not any column value changed; it's just whether or not an update was performed on the row.

This is perfect for an optimistic concurrency system. All I need to do is to read the value of the Concurrency column when I read other columns from the table, and when I update the row, I include a WHERE clause that checks the value is still the same. Then if no rows are matched for my UPDATE, I know that someone else modified the row in the meantime.

Having this support in the back end of the database avoids all the potential race conditions that might come from trying to implement this in code yourself.

As an interesting note, the last value used for the rowversion column is calculated at the database level, not the table level. If I create another table, the same set of values will just continue on. At times, we've used these to find all changes in a database. You can find the last value for a database by reading the @@DBTS system variable.