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 112011
 

SQL Saturday 101I’m very happy to have been chosen to speak at SQL Saturday #101 in Kansas City on October 29th. Those responsible for planning have put together an excellent schedule with a great group of speakers that I’m proud to be among!

I’ll be presenting 2 sessions that day:

Backups and Recovery In Depth takes a good look at how backup and restore operations work in SQL Server, how your choice of recovery model affects what they do, and how this knowledge can be utilized to develop an effective restore strategy for your database. This session got lots of positive feedback at SQL Saturday #67 in Chicago earlier this year, so I’m hoping it’s a hit this time as well.

The Skinny on Data Compression is a new presentation of mine that covers the different types of data compression available in SQL Server and the details of how they work. We’ll also discuss the pros and cons of compressing your data and how to determine which type of compression is the most appropriate for a given object.

Registration is still open as of when this post went live, so if you’d like to attend please do so here. If you’re on Twitter, I highly recommend following the hashtag #sqlsat101 for the latest information. There will also be lots of chatter there the weekend of the event.

Hope to see you there!

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.