The Bit Bucket

When I’m doing a lot of query editing, I often get a bit mesmerized, particularly if there’s a lot of manual copy and paste or cut and paste going on.

One thing that often drives me crazy is when I use Ctrl-C (ie: copy) when I meant to use Ctrl-V (ie: paste). Invariably, I do this when I have nothing highlighted at all. So not only did I not get the value pasted, I just copied an empty value into the clipboard.

But SQL Server Management Studio (SSMS) has your back on this. (And so does Visual Studio)

In Tools, then Options, then Text Editor, then Transact-SQL, under the General tab, there’s an option for Apply Cut of Copy commands to blank lines when there is no selection.

The default is that it works as expected (like you don’t want it to), but if you uncheck it, you might have just saved yourself some annoyance.

If I’m working with this code:

and I highlight the word DECLARE and hit Ctrl-C, then move to the blank line and hit Ctrl-C instead of Ctrl-V, I’d have just lost my first clipboard item. With this option unchecked, I can just smile, and hit Ctrl-V again, and it will still paste:

Service Broker is one of my favorite tools in SQL Server. So many applications need to have a transactional queue, and many people try to build them using tables and other objects. But SQL Server has Service Broker and with it, you get the beauty of using the knowledge of someone who already knows about queues having thought about how they need to work.

But while Service Broker is quite forgiving, it’s common while developing Service Broker applications to make mistakes and end up with messages in queues that will never be delivered because you had some bug in your code. Service Broker doesn’t want to ever just throw your data away.

One of our free SDU Tools for T-SQL developers and DBAs is ClearServiceBrokerTransmission queue. Let’s look at an example of using it.

We’ll create a new database called Development and use it:

Then create a master encryption key, a queue, and a service:

Now let’s send a message that will never be delivered (because the target service doesn’t exist):

We can query to see that it’s still in the transmission queue:

If we scroll to the right, we can see why it’s stuck:

Now we realize that we messed up the service name and it will never be delivered, so we want to flush the queue. We can do that easily:

Note that if you are following along with the code and don’t have SDU_Tools installed in that database, you’ll need to change the EXEC line to point to a database where they are installed.

And finally, we could just clean up:

You can see this tool in action here:

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

http://sdutools.sqldownunder.com

 

 

 

Visual Studio is a very configurable tool, and particularly in the area of keyboard shortcuts. Because SQL Server Management Studio (SSMS) is based on Visual Studio, it inherits many of these configuration options.
SSMS has a very rich set of keyboard shortcuts. Without trying to cover most of them, I do want to highlight a few that I think are really important to know how to use.

Let’s start with an easy set of commands:

You might need to change the case of some values. If I have this code:

I might decide that our new coding standards say that variables that are treated like constants, must be in all capitals and with words separated by underscores. (This is often called SHOUTY_SNAKE_CASE).

I can just highlight the name (or double-click it), the hit Ctrl-Shift-U to make it upper case.

And Ctrl-Shift-L would make it lower case.

Now, what if I want to rearrange the order of these declaration lines. If I needed to move the @A_CONSTANT_VALUE line below the other line, I often see people highlight the whole line, then cut it, then paste it below.

A great keyboard shortcut for doing that, is to hit Alt-Shift-T. No matter where you are on the line, it just moves the line down by one.

If I wanted to then add a line in between these two lines, what I typically see people do is to put the cursor on the D in the second line (at the beginning of the line), then hit Enter to move it down one, then use the up arrow to go back to the newly emptied line.

What you can do instead is put the cursor anywhere on the first line, and hit Ctrl-Shift-Enter.

I’m sure that I’m somewhat anal, and because of this, I also often spend time cleaning up lines. So if I come across a line like the second one here, the spacing grates on me:

The quickest way to clean up the second line is to highlight the whole line, then hit Ctrl-K followed by Ctrl-Backslash.

Notice that it automagically removed all the messy whitespace. It can do multiple lines at once, but it won’t rearrange what’s on each line.

Finally, if I needed to comment out this code, I’d highlight it and hit Ctrl-K then Ctrl-C.

And Ctrl-K followed by Ctrl-U will uncomment it.

Two more easy shortcuts: Ctrl-Home takes you to the top of the script. Ctrl-End takes you to the bottom of the script.

The final most useful keyboard shortcut is Ctrl-R. It hides or shows the results pane.

You’ll find a detailed list of keyboard shortcuts here:

https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-keyboard-shortcuts

 

I often need to convert values from one data type to another, particularly when working with strings. One function that I’m surprised wasn’t already built in to SQL Server was one that converts a character to its hexadecimal representation.

There is an ASCII function. You can see it here:

This converts a character to its ASCII value (see http://asciitable.com)

We’ve added a function to our free SDU Tools collection for developers and DBAs. It’s called CharToHexadecimal and you can see what it does in the main image above.

You can also see it in action here:

For more information on being an SDU Insider and getting our tools and eBooks, just visit here: http://sdutools.sqldownunder.com.

 

 

We help a lot of customers upgrade their systems. Part of that job involves looking for code smells before starting the upgrades. If you aren’t familiar with code smells, this Wikipedia article explains it pretty well: https://en.wikipedia.org/wiki/Code_smell.

Specifically, they describe a code smell as any characteristic in the source code of a program that possibly indicates a deeper problem.

One of these is the presence of TOP 100 PERCENT or perhaps written as TOP (100) PERCENT in SELECT queries. Almost always, we see this in the definition of views.

In SQL Server, a view is basically just a SELECT statement that’s been given a name.

Tables don’t have a natural order, and neither do views. If you want to output rows in a specific order, you need to use an ORDER BY clause in the queries that reference the tables or views.

What usually leads to this code smell is that the developers have attempted to create an ordered view. Let’s see an example.

In the WideWorldImporters database, I could write a query to return all the USB food items:

Note that I haven’t included an ORDER BY clause. The data happens to be in StockItemName order but that’s not guaranteed at all.

Now imagine that I want to create a view, but I want that view to return the rows in StockItemID order. I could try to create it like this:

That will return this error:

You are correctly told that the ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions. That’s because those objects don’t have any natural order.

But it’s the final part of the message that leads developers astray: “unless TOP, OFFSET or FOR XML is also specified”.

So they change the query as follows:

And the error goes away.

Unfortunately, in SQL Server 2000, because of a qwerk in how that version was implemented, it also worked as expected.

The problem here is that the TOP statement wasn’t implemented well, back in SQL Server 2000. It used the ORDER BY clause both for determining the TOP rows, and for the output order of the query. It really should have had a separate option on TOP instead, something like an OVER clause:

SELECT TOP(10) OVER (ORDER BY ProductID)
….
ORDER BY CustomerID;

So this led to people thinking they could create ordered views by including a TOP statement.

In SQL Server 2005, this stopped working as expected. The ORDER BY in the view was used for implementing TOP but it wasn’t used for setting the order of the view itself. Customers started complaining that it was now broken. It wasn’t broken at all. It used the TOP statement to work out which rows to return (ie: all of them in this case), and then returned the rows in any order it felt like.

Views don’t have any natural order.

So if we see TOP (100) PERCENT in code, we suspect that the developer has attempted to create an ordered view and we check if that’s important before going any further. Chances are that the query that uses that view (or the client application) need to be modified instead.