Creating Views in Another Database

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…
Read More

T-SQL Tuesday #23: WHERE The JOIN Ends

This 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…
Read More

T-SQL Tuesday #17: Applying APPLY

This month's T-SQL Tuesday is the brainchild of Matt Velic (blog | twitter) and was inspired by a quote from Adam Machanic (blog | twitter) on the importance of understanding the APPLY operator. It's a good trick to have up your sleeve as it can do some nifty things and really save you some keystrokes when used properly. My first experience with APPLY occurred many moons ago when I was working with a system that required a pipe-delimited list of values. Fortunately whoever developed it long ago had the good sense to store the data properly (first normal form) in…
Read More

BETWEEN a DATE and a DATETIME Place

I'm not a fan of the BETWEEN operator. While the syntax is very easy to understand and requires less typing than my favorite alternative, it can lead to situations where unwanted results are returned. Because of this I've gotten into the habit of avoiding it all together. If you'd like a refresher on BETWEEN, its syntax works like this: a BETWEEN b AND c where a, b, and c are expressions of the same data type. The result will be all values of a falling in the inclusive range between b and c. This means the following statements are equivalent:…
Read More

Savepoints: The Other Other White Meat

Most T-SQL guides and tutorials I've seen concentrate on the following transaction commands: BEGIN, COMMIT, and ROLLBACK, which I'll henceforth refer to as the "big three". While they're certainly very important, there's also a fourth command, SAVE, which is typically treated like a distant cousin who's only seen at family reunions every few years. It's a shame because while SAVE is not always necessary, it can be a huge help when you need it. If you've ever found yourself in a situation where you have a multi-step process that takes a long time to complete, you're probably using the big…
Read More