I’m an outspoken advocate of always using a clustered index on each and every table you create as a matter of best practice. But even I will agree that there’s a case for using the odd heap now and then.
For practically every piece of code you develop, there will be trade-offs. Sometimes, you can combine the best of two worlds, other times it comes down to some hard choices. For T-SQL developers, it typically boils down to a few key questions:
- How much time can you spend perfecting code instead of just shipping?
- Can we just fix it when it becomes a problem?
- Is buying more hardware cheaper than paying for developers to tune their code?
- Is better code harder to read, and will a junior developer be able to work with it?
This is a real-world problem that I came across the other day. In a reporting scenario, I wanted to output a number of values in an easy, human-readable way for a report. But just making a long, comma-separated string of numbers doesn’t really make it very readable. This is particularly true when there are hundreds of values.
So here’s a powerful pattern to solve that task.
In an attempt to try a different approach, here’s a three-minute video explanation of how the different physical join operators in SQL Server work and why you would choose one over the other.
I’ve written a few blog posts on join operators befores, so if this video wet your appetite, here’s some recommended reading:
I’d love to hear what you think of the short video format! Please leave feedback in the comments below or on Twitter.
If you haven’t heard of Slack, it’s a wonderful team messaging platform. At first glance, it looks a bit like a private Twitter where you can set up “channels” to have conversations with colleagues. But the great thing with Slack is its flexible API and all the marvelous ways in which you can extend its functionality. You can send rich text messages with status updates from your production servers or you can interface with popular web services like Trello, right there in the chat window!
So as a way to kick start an effort at learning node.js and re-discover web development (which I haven’t really done in about 15 years now), I set out to build a Slack API. Here’s what I learned.
Do you ever compare the values of a lot of columns in two tables? Sure you do. Like, for instance, in a cross update, when you need to figure out which rows you should actually update. But it gets worse if the columns are nullable. The fact that any value could potentially be NULL vastly complicates the comparison and might wreak havoc not only on your code but also on your query performance.
But there’s hope.
If you’re a consultant connecting to remote client servers, or if you have a heterogenous network environment with different Active Directory forests without established trust relationships, you’ll have a few extra challenges connecting to SQL Server using Windows authentication, and SQL Server authentication may not be available.
The easy way to solve this is to just log on directly to the remote server using Remote Desktop and use Management Studio on that session, but this is not really desirable for several reasons: not only will your Remote Desktop session consume quite a bit of memory and server resources, but you’ll also lose all the customizations and scripts that you may have handy in your local SSMS configuration.
Your mileage may vary with these solutions, and I don’t have the requisite skills to elaborate on the finer points with regards to when one solution will work over another, so just give them a try and see what works for you.
You can establish a SMB (network file share) connection to the server from your workstation. First, open the “Run…” dialog, then enter the name of the server, prefixed by two backslashes:
When you click OK, it takes anywhere from a few seconds to even a minute or two to establish the network connection. You’ll get this authentication dialog, where you have to provide your credentials:
Enter the fully qualified username that you want to connect with, for instance user@domain, domain\user or server\user (for a local account on the server).
Once you’ve been authenticated, try starting SQL Server Management Studio and connect to the server using Windows authentication. In my experience, Windows will also remember these credentials the next time you want to connect, as long as you check the “Remember my credentials” box.
The screenshots are from Windows 10, but I’ve used this technique on a multitude of different Windows versions.
You can launch Management Studio using the command-line utility “runas.exe“. You can do this from the “Run…” prompt or by altering the start menu shortcut, depending on your needs.
runas.exe gives you a lot of other options, as detailed in the documentation, but it’s a little more work in my opinion. Remember to make corrections for your path to the SSMS executable.
runas.exe /netonly /user:domain\user "C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\ManagementStudio\Ssms.exe"
If runas.exe doesn’t work, double-check that the “Secondary logon” service is running on your local machine. The “/netonly” option tells Windows that this process is only for remote connections.
Again, your mileage may vary. Try both options to see which one will work for you.
In a sense, you could call me lazy. If there’s a script that will perform a task for me, I’d rather use that script than reinvent another wheel. Then again, if needs be, I’d rather spend a day writing such a script, rather than spending ten minutes just getting the job done.
Somehow, that makes me a happier developer.
Anyway, one common problem that you’ll stumble across is the need to extract, version, and deploy data from one environment to another. This could for instance be meta-data that you want to sync from your production database to your test/dev database or the other way. In that process, a few requirements typically come up:
- Sometimes the existing data in the target table(s) needs to be deleted first,
- Any delete or insert operations (across multiple tables) need to happen in the correct order – so the foreign key constraints aren’t violated,
- Using IDENTITY_INSERT solves a few problems but creates others, so if you don’t use IDENTITY_INSERT, you’ll want to make sure that all those pesky identity columns in the target tables don’t mess up your foreign keys,
- And, of course, all of this has to happen atomically. All or nothing.
So that’s the essence of what my stored procedure LoadTableBlobs does – script one or more tables using FOR XML AUTO with a SELECT statement into a single XML blob. That XML blob is then passed to the stored procedure that will magically insert everything into the correct tables in a given target database.
Here’s an idea of how you might use it: In the source database, run something like this:
--- 1. Collect one or more tables' contents as a XML AUTO blob: DECLARE @xml xml=CAST(( SELECT * FROM dbo.A FOR XML AUTO, TYPE) AS varchar(max))+CAST(( SELECT * FROM dbo.B FOR XML AUTO, TYPE) AS varchar(max))+CAST(( SELECT * FROM dbo.C FOR XML AUTO, TYPE) AS varchar(max));
The resulting XML blob may look something like this:
<dbo.A a="5" name="five" /> <dbo.A a="4" name="four" /> <dbo.A a="1" name="one" /> <dbo.A a="3" name="three" /> <dbo.A a="2" name="two" /> <dbo.B b="5" _a="1" name="five" /> <dbo.B b="10" _a="2" name="five" /> <dbo.B b="15" _a="3" name="five" /> <dbo.B b="20" _a="4" name="five" /> ...
Each element represents a single row; the element’s name is the name of the table and all the attributes are column values. NULL values are just left out of the element entirely. The relations between tables are declared in the DDL of the target database, so make sure you have proper foreign key constraints in place where you need them.
Now, move that XML blob any way you want to the destination database, and feed it into the LoadTableBlobs procedure, like so:
--- 2. Then, on the target database (with identical, existing tables), --- run the procedure, passing the XML blob as argument: EXECUTE dbo.LoadTableBlobs @blob=@xml, @use_identity_insert=0, --- If you need IDENTITY_INSERT ON @truncate_table=0, --- If you want to empty the target table(s) before inserting @print_only=0 --- If you want a "dry run" that only prints the T-SQL code
If you’re using IDENTITY_INSERT, you may just as well want to implement this using something like an SSIS package. But if you’re not, a neat trick that LoadTableBlobs does is that it manages your inserts so they happen in the correct dependency order, and it keeps track of identity columns (you’ll need a unique index on one or more non-identity columns, a natural key if you will). So even though your insert generates new identity values in the target tables, this is mapped, so everything is still glued together the way you’d expect.
I’ll probably follow up this post with more details on the inner workings of the procedure some day.
Get the script on the Downloads page, have fun with it somewhere safe (please test everything, don’t just take my word for it!) and let me know in the comments or on social media what you think of it.
Performance tuning the other day, I was stumped by a query plan I was looking at. Even though I had constructed a covering index, I was still getting a Key Lookup operator in my query plan. What I usually do when that happens is to check the operator’s properties to see what its output columns are, so I can include those columns in my covering index.
Here’s the interesting thing: there weren’t any output columns. What happened?
The “include actual execution plan” feature in SQL Server Management Studio is an invaluable tool for performance tuning. It returns the actual execution plan used for each statement, including actual row counts, tempdb spills and a lot of other information you need to do performance tuning.
But sometimes you want to run a series of statements or procedures where you only want the execution plan for some of the statements. Here’s how: