May 102016
 

I’ve loved using Redgate’s tools ever since I discovered what they were, and now that I’m a Friend of Redgate it’s even more fun because I get to give feedback to their developers and hear all about what’s coming out in new releases! Recently, Redgate announced SQL Prompt 7.2, with a bunch of new features and improvements. My personal favorite of all these is execution warnings.

Databases (and computers in general) have this pesky habit of always doing exactly what we tell them to do, instead of doing what we really meant to tell them to do. Have you ever been burned by running a query without the WHERE clause? Perhaps you ended up updating or deleting ALL the rows in a table instead of just a few? A common way to reduce the risk of this is to run those commands inside a transaction, and if you see an abnormally high number of rows affected, it’s simple to rollback. This works great, until you’re in a hurry and forget to run BEGIN TRAN, greatly upping the chances of disaster. Now in SQL Prompt 7.2 you have an added layer of protection – the tool is watching your queries and can warn you! Check it out in action:

If I try to update my table of important data and don’t specify a WHERE clause, I’ll see the following:

The same happens for deletes:

And I think it’s great that I have the option of checking the box and not showing that warning again, but I definitely won’t be doing that.

A lot of times it’s the little things that really make a difference, and I think these warnings are a simple and unobtrusive way to make sure you really meant to run what you typed.

Dec 032015
 

I’m already at peace with the fact that I’ll never know all of SQL Server’s secrets, but that doesn’t stop me from being surprised every time I figure out something new. In this case, it’s another “secret” hiding in plain sight (Books Online).

It turns out that the DROP DATABASE statement doesn’t just have to drop one database. BOL shows that multiple databases can be specified when separated with commas. Let’s see it in action.

First, create 4 databases:

CREATE DATABASE DB1;
CREATE DATABASE DB2;
CREATE DATABASE DB3;
CREATE DATABASE DB5;

Now drop them:

DROP DATABASE DB1, DB2, DB3, DB5;

Yep, all gone.

But what if there’s an error? Re-run the CREATE statements above, but now let’s drop 5 databases instead of 4. DB4 doesn’t exist (much like Terminal 4 at Chicago’s O’Hare Airport).

DROP DATABASE DB1, DB2, DB3, DB4, DB5;

The above statement will throw an error that it is unable to drop DB4 because that database doesn’t exist, but the other 4 databases are dropped without incident. The same will happen if a user is connected to one of the databases: that one will remain, but all others will be dropped.

So there you have it: you can drop multiple databases with a single statement in SQL Server. (According to Books Online this does not work in Windows Azure SQL Database.) It’s amazing the things you can learn from reading documentation!

 

Dec 132011
 

T-SQL Tuesday LogoThis month’s T-SQL Tuesday is hosted by Allen White (blog | @SQLRunr) and is an invitation to show off a nifty T-SQL trick you use to make your job easier. Here’s one of my favorites as of late:

Some of the source systems we copy from shard their data across multiple similar tables. For example, instead of having 1 table named dbo.Sales, they might have 30 of them named dbo.Sales_001 to dbo.Sales_030. If there were a rhyme or reason to this sharding it might be seen as an advantage, but unfortunately neither myself nor others on the team have ever found a method to this madness, and the vendor will not divulge the way they do this. As if that’s not bad enough, additional tables can pop up out of nowhere, and not all these tables are guaranteed to contain the same columns. Table dbo.Sales_031 may pop up tomorrow and have only a subset of the columns from the previous 30 tables, or may contain new columns not present in any of the others.

To keep this all straight, I have a process that compares the columns in all applicable tables and generates a view that combines their contents using UNION ALL. I’ve actually blogged about an aspect of this before, but today I have a function containing a nice little trick that’s proven very helpful for me. Since the tables I’m generating a view of aren’t guaranteed to have the same columns in them, a simple “SELECT *” from each of them won’t work because unioning tables together requires identical datatypes in the same order. Instead I have to generate a select statement that explicitly lists all columns for every table. T-SQL can easily accomplish this with cursors and loops, but then I found FOR XML PATH, which made life a lot simpler.

FOR XML PATH has many features which are outside the scope of this post, but the one behavior we’re going to exploit today is that you can pass a row tag name that’s blank. When you do this, instead of sandwiching each row between tags, it simply concatenates all the row values together into one string. If you add commas, you’ve got a great way to list out all the columns of a table which you can then form into a SELECT statement. Without any further ado, here’s the demo code:

-- create our demo table with a few columns
CREATE TABLE dbo.MyTable (
   ID INT NOT NULL,
   Value VARCHAR(20),
   Notes NVARCHAR(MAX)
);

-- select the table's columns from system tables
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id;

-- now let's format it as XML
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id
FOR XML PATH;

-- now pass a blank string argument to PATH
-- note how the <row> tags are now removed but column tags remain
SELECT c.name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.name = 'MyTable'
ORDER BY c.column_id
FOR XML PATH('');

-- casting it to NVARCHAR removes the tags for each column
DECLARE @s NVARCHAR(MAX);

-- now let's add a blank string argument to PATH
SET @s = (
      SELECT ',' + QUOTENAME(c.name) -- comma to separate names
      FROM sys.columns c
      INNER JOIN sys.tables t ON c.object_id = t.object_id
      WHERE t.name = 'MyTable'
      ORDER BY c.column_id
      FOR XML PATH('') -- XML that really isn't
   );

-- remove the leading comma
SELECT SUBSTRING(@s, 2, LEN(@s));

Wrap the final output with a SELECT and a table name and you’re good to go. There’s tons more uses for this than creating lists of columns – basically anytime you want to turn column values into a delimited list you can make it happen using this method. Happy coding!

Oct 182011
 

Have you ever had one of those times where you spent hours trying different ways to make something work the way you envisioned, only to find that the solution was staring you in the face all along? I have those more often than I’d like to admit – and while the title of this post seems like a DBA 101 topic, the answer isn’t really all that simple. To hopefully help others (and perhaps my future self) I’ll document a recent one here. I’m sure there will be plenty more in the future as well!

The story goes that I was working on a stored procedure to generate views in other databases via dynamic sql, and the hangup was that you can’t create a view in another database – at least not easily. I realize that views can reference tables in other databases with no problem at all, but due to security restrictions and other issues outside the scope of this post, these views needed to exist in specific databases.

Creating a table in another database is a piece of cake because you can specify the database name in the CREATE TABLE statement:

CREATE TABLE databaseName.schemaName.tableName (...

Creating a view, however, is not so easy. You can’t specify a database name in the CREATE VIEW statement – doing so will get you a hot date with error 166. The only remark in Books Online is that “A view can be created only in the current database.”

View_In_Another_DB_Fail

Since these views were being generated with dynamic sql, my next thought was to issue a USE [OtherDatabase] statement before the CREATE VIEW and just pass all of that into sp_executesql, the idea being that USE will change the context of execution to the desired destination database and all will be well. Unfortunately things didn’t go according to plan:

Dynamic SQL Statement Fail

This yields 3 errors – two for “Incorrect syntax near ‘GO'”, and another that “CREATE VIEW must be the first statement in a query batch.” If GO signals the end of a batch, then removing the GOs makes it all one batch, right? Not quite. Losing the GOs will still give you the error 111, because CREATE VIEW must come first.

So how can we do this? After much experimentation I figured out that you can specify the database sp_executesql runs on by prepending a database and schema name to it just like other objects – something so simple that I thought right past it. It’s kind of crazy to think that in all my years working with SQL Server I’ve never had a need to do it with dynamic sql until now, but that’s the case.

View_Dynamic_Success

If you weren’t already aware of this, now you are. Hopefully it helps someone!

Oct 042011
 

T-SQL Tuesday LogoThis month’s T-SQL Tuesday topic comes to us courtesy of Stuart Ainsworth (blog|@codegumbo), and is early due to the PASS Summit being next week. Perhaps those planning next year’s summit will do a better job to make sure they don’t interfere with any blogging parties :)

Stuart’s topic for the month is joins, and while they are certainly a building block of the skyscraper that is database usage, their complexities can still trip up experienced users from time to time. I ran into a new (to me) join “gotcha” not too long ago and had this post in my queue to release later, but once this topic was announced I knew it would be appropriate to discuss here.

When learning SQL, one of the first keywords taught is there WHERE clause, as filtering is a rather basic operation that anyone can conceptualize. After that, INNER JOINs tend to follow pretty quickly, and then OUTER JOINs. WHERE can of course be used in conjunction with any JOIN, and that’s where things can get a little tricky, if not down right confusing.

To make my point we’ll need an example, and rather than use AdventureWorks we’ll just create our own example tables since they needn’t be very large.

CREATE TABLE TestOld (
	Ltr CHAR(1),
	Nbr TINYINT
);

CREATE TABLE TestNew (
	Ltr CHAR(1),
	Nbr TINYINT
);

INSERT INTO TestOld VALUES ('A',1),('A',2),('A',3),('B',4),('B',6);
INSERT INTO TestNew VALUES ('B',2),('B',3),('B',6),('C',7),('C',12);

SELECT * FROM TestOld;
SELECT * FROM TestNew;

We’ll start with a simple left join query based on the Ltr column of each table:

SELECT o.Ltr AS OldLtr, o.Nbr AS OldNbr, n.Ltr AS NewLtr, n.Nbr AS NewNbr
FROM TestOld o
LEFT JOIN TestNew n ON o.Ltr = n.Ltr;

No surprises here. Our results contain all combinations of rows from both tables matching on the Ltr value of “B” as well as rows from TestOld that have no match, just as we would expect. Now let’s put a twist on it and say we’re only interested in results from TestNew where the Nbr value is even. We execute the following:

SELECT o.Ltr AS OldLtr, o.Nbr AS OldNbr, n.Ltr AS NewLtr, n.Nbr AS NewNbr
FROM TestOld o
LEFT JOIN TestNew n ON o.Ltr = n.Ltr
WHERE n.Nbr%2=0;

But wait, this looks more like an INNER JOIN than a LEFT JOIN. In fact it is an INNER JOIN, as the rows with NULL values for the TestNew columns are now gone. Filtering on TestNew.Nbr gives you way more than you bargained for because not only does it remove values that aren’t even from the result set like we specified, it also removes values that aren’t NULL. The WHERE clause implies that rows in TestNew must exist in order to be compared, and when rows exist on both sides of a join, it’s an inner join.

To get what we want and enforce our constraint in the context of a left join, the filter has to be part of the join statement. The query can be rewritten like this:

SELECT o.Ltr AS OldLtr, o.Nbr AS OldNbr, n.Ltr AS NewLtr, n.Nbr AS NewNbr
FROM TestOld o
LEFT JOIN TestNew n ON o.Ltr = n.Ltr AND n.Nbr%2=0;

Now our results contain records for which TestNew has no matching rows in addition to records where TestNew has even values – just like we asked for. The moral of the story is that if you’re going to filter values from an outer-joined table, you need to include the filtering statement as part of the JOIN instead of in it’s own WHERE clause.