SQL2005's Management Studio with SQL2000

Stuck in SQL2000 land

I've had SQL2005 installed on my local machine for some time. For a while, I just used a virtual machine for it, but when the release candidate came out, I installed it on my machine proper, and frequently try things out on SQL2005. I'm a developer rather than a DBA, and I happily try things out in SQL2005 when I see some way that I could use the new functionality. I now have several queries that have two versions - the one that I'm using on under SQL2000, and the one that I'll use when I get to use SQL2005 in production.

A compromise...

A while back, I was talking to the senior DBA where I work about some of the new SQL2005 things, and he mentioned that he loved using SQL2005's Management Studio. I asked him which of our customers he was using it for, and he said "All of them." Somehow, I had missed the fact that Management Studio works REALLY well with SQL2000 databases. So I started to do the same, and thought I'd write down some of my tips.

Main benefits...

Firstly, it really does work well. It feels like it's supposed to. I can even use it to edit DTS packages, because I have the Feature Pack installed, including the SQL Server 2000 DTS Designer Components. My queries run nicely, it's all the one window, instead of swapping between Enterprise Manager and Query Analyzer all the time, and that's great.

Object Explorer and Summary page

It's worth mentioning the Object Explorer and Summary page. The Object Explorer is a mix of the Object Browser within Query Analyzer and the Explorer part of Enterprise Manager. Now, the first thing that I did when I started using Management Studio was to tell it to start with an empty environment. I don't need it trying to connect anywhere before I've decided which particular server I want to connect to. But then I came to a situation where I wanted to delete about 5 tables, and I couldn't see how to do that easily in Management Studio. So I asked a friend, and he said "Use the Summary page". Doh! Of course. I pressed F7, up it popped, and it showed me everything I needed. Gives me lists of the things that are within the selected item in the Object Explorer. It's handy, but to be honest, I rarely use it. I would rather have a query window there, which is what I do!

Renaming registered servers

Object Explorer is really great, of course. I love being able to see the linked servers. I love the grouping of functions into table, scalar and system. The way that the windows can dock is great. Personally, I think I'm typically going to use the default layout, but it's great to have windows like the Registered Servers pane able to auto-hide when you don't want them. And speaking of Registered Servers - it's great to finally be able to change the display-name of servers. No more IP addresses or annoyingly named servers for me. I can call them useful things like "Test Server in Sydney" or "Production Server for client XYZ", or "DON'T USE THIS ONE, but if you really need to, then it's Jeremy's" - and Management Studio still does the friendly thing of putting the address/servername in brackets after my own description.

Go handled properly

Here's a thing I came across the other day which has won me over even more:

How many times do you have a bunch of code that you want to comment out in Query Analyzer, so you put a /* and later, put a */. Then you run it, and you find that there was a 'go' in there somewhere, giving you not only an error saying that it couldn't find the pairs of the comment brackets, but also running anything in between that you really didn't want to have run! Well, Management Studio is nicer than that. A 'go' statement surrounded by comment brackets is also ignored. So there's no need to highlight it all and do Ctrl-Shift-C (or Ctrl-K, Ctrl-C in Management Studio). Actually, there's another thing - commenting is now consistent with Visual Studio. This is a tremendous thing for anyone in the developer space. Probably not so great if you're a DBA, but I find it good. I do wish that Ctrl-Shift-C still worked, but only because there are times I hit it and find that nothing happened. And you can now undo comments! In Query Analyzer, I used to get really frustrated that I couldn't hit Ctrl-Z and undo my bulk-comment. But now, Ctrl-Z will undo a Ctrl-K, Ctrl-C. And I'm a much less frustrated coder.

use pubs
go
select * from authors
/*
select * from titles
go
select * from employee
*/select * from jobs

Copying works better

How much did you hate copying stuff out of the grid in Query Analyzer. You'd copy it, paste it into another query, and automatically hit delete to remove the newline character that it put in there for you. Now, it doesn't put the newline in there. I know this could be annoying if you want it, but frankly, I don't! I like the fact that I can now paste a string in between two apostrophes, and not have to hit backspace immediately.

Movement

Finally, I want to point out that Management Studio understands words much better than Query Analyzer. If I use Ctrl-Left or Ctrl-Right to move through a string (let's say, a URL) in Query Analyzer, then it consider that each word ends at whitespace. Aargh! That used to kill me. But Management Studio will stop at each dot, hyphen, plus, you name it! Unless you name it 'underscore' - they get ignored, but that's fair enough, because underscores are different. I know that if you like the way that Query Analyzer handled these things, then you might get frustrated by Management Studio, but is there really anyone out there who will find that?

So my conclusion...

I have to recommend using Management Studio to anyone who uses SQL2000. If nothing else, you'll find yourself looking forward to when you get the clearance to use SQL2005 itself. But even if SQL2005 isn't on the horizon, you can use a much nicer interface to the SQL2000 databases you do have.