Why are Subqueries Dangerous?

T-SQL
If you go around to the various forums, you'll see postings, including some I've put up, that say using subqueries, especially correlated subqueries in the SELECT statements of queries is bad because it effectively acts as a cursor. I got called on it. So I had to do a bit of research. Books Online talks about it right off when describing subqueries. That's not proof though. I did some more looking around. Adam Machanic does a nice job of slicing up Functions, which are basically the same thing. But it's not exactly the same. I kept looking. This blog entry is just flat wrong, but the second comment points out the fallacy. Jeff Moden would also like this example since it shows the disparity between the actual cost of queries…
Read More

Performance Data Warehouse

Uncategorized
The new functionality coming out with 2008 includes the Data Collector which feeds to the Performance Data Warehouse. I presented this topic at the SNESSUG Heroes Launch event. It's pretty slick functionality. I posted about it once before. A new article by Derek Comingore is available now over at SQL Mag. It's worth the read. The beauty of the new tool is the fact that it's not all that new. It's doing all this work using tools that we know and are comfortable with. This makes it very easy to implement and maintain. I agree with Mr. Comingore and I hope they expand the client to cover SQL Server 2000 and 2005.
Read More

VSTS 2008 Database Edition GDR: Final First Impression

Tools, Visual Studio
I finally finished my initial set of tests with the GDR CTP release. It's great! They've solved so many of the deployment problems that we had been experiencing that it's now hard to wait until they actually release the product. The bad news is, it completely changes my presentation at PASS. I'm still going to cover 2005/2008, but now I'm going to cover the GDR as well. I have one hour to hit both processes... That's not going to be easy. Anyway, they've broken everything down to either work locally only or as part of the larger project. It all gets checked into source control. It all comes back out. No manual processes (like checking in the .user file) necessary. Thank you Gert Drapers, wherever you are.
Read More

VSTS 2008 Database Edition GDR: Still More First Impressions

Tools, Visual Studio
Following on to my adventures in creating multi-environment deployment processes with the new version of Data Dude (DBPro, VSTS Database Edition, whatever we're calling it this week). I've create a new configuration, copying all the settings from the Debug configuration. I'm adding a new Deployment configuration file and making a change. The deploy worked. Woo hoo! Now to get really funky. I'll create a new "Sql command variables file:" and add a variable for setting the data directory. Now to deploy and... Urk! Failed. It's not recognizing my variable. Now I'm stuck. I've checked the syntax. It's right. I double checked it all and reran deploy. Now it works... Color me confused. Whatever. Successful test. Time to create another configuration, simulating a QA server... Got that working too. I don't…
Read More

VSTS 2008 Database Edition GDR: More First Impressions

Tools, Visual Studio
Because I was having so many problems yesterday after I tried doing everything at once, creating multiple deployments to multiple servers with a variety of different settings... Anyway, it didn't work. So today I went back to square one. I created a blank project.  Yesterday I used the schema compare to capture the database, but today I right clicked on the project and found "Import Database Objects and Settings..." which did it all for me. Yee Ha! Anyway, after getting all the objects in, I started trying to deploy. This time I got errors on FK's. It somehow changed the order on the compound keys columns. While editing, I found that if I messed up the syntax and then saved the file, I didn't get an instantaneous error. That bummed…
Read More

SNESSUG Speaker

Misc, PASS
Tonight we have Rob Walters of Microsoft speaking on Visual Studio 2008 Reporting Services. This should be a good one. Rob also recently published a book, Accelerated SQL Server 2008. I'm going to a party at the MS building in Waltham next week to celebrate the release of Rob's book. Hopefully that means I can finagle a copy too.
Read More

VSTS 2008 Database Edition GDR: First Impressions

Tools, Visual Studio
Since I have to work through this thing to set up a test project to verify our build & deployment processes with the new tool (see my teeth grinding post below) I might as well post a spot evaluation of the tool as I go through it. I'm creating a new database project (and I'd rather not talk about that). Rather than go through a wizard, I decided to create a blank database project and now I'm capturing the database by using the new Schema Compare. It looks mostly the same. One new thing that came up immediately is a "Permissions" folder. It has all the GRANT CONNECT, GRANT EXECUTE ON SCHEMA that I had in my database. In the 2005 version of VSTS, we maintained this stuff in post…
Read More

VSTS 2008 Database Edition GDR

Visual Studio
OK. I'm jazzed about this. I'm diving in to test it out & try it out. <kicking desk> I can't convert existing projects to it... CRUD! Fine. Reverse engineering a database and then building all the customizations by hand will be a good learning experience. Yes, that sound you hear is my teeth grinding, not tectonic plates shifting... CRUD! A quick & easy test, no real work was that too much to ask?
Read More

Visual Studio Team Edition for Databases 2008

SQL Server, Visual Studio
Great news. All the processes we had created for automating our deployments and builds in VSDB 2005 work in VSDB 2008. Bad news. All the work arounds and crutches we had to figure out and maintain are still necessary. I've heard that it makes the MS guys crazy, but we found that the only way to get the configurations to work with VSDB 2005, and now 2008, was to check-in the .USER file along with the code. This then needed to be checked out of source control, replacing the local copy (and heaven help you if you don't use a forced get on the check out) before doing any work with the project. I did a full build & deploy from 2008 through our usual batch file. No problems at…
Read More