Click here to monitor SSC
  • Av rating:
  • Total votes: 209
  • Total comments: 41
Brad McGehee

Management Studio Improvements in SQL Server 2008

14 August 2008

Not everybody  who had to use Management Studio when it first appeared in SQL Server 2005 liked it. The paint seemed still wet and there was still scaffolding around. To give Microsoft its' credit, it has succeeded in transforming it into a much more useful product, in its SQL Server 2008 reincarnation. Brad McGehee gives the details.

DBAs have never really taken to SQL Server Management Studio (SSMS) with enthusiasm. However, SQL Server 2008 has brought with it a multitude of improvements to SSMS, which are intended to make it a little easier and more convenient to use.

I originally intended to include in this article every new change I could find, but I had to give up. There are so many improvements that it would take a short book to cover them all. No, this is not to say that Microsoft has changed SSMS so much that you will have to relearn it. SSMS is essentially the same tool we learned when it was first introduced with SQL Server 2005. On the other hand, it includes many new features and enhancements that make performing the routine tasks of a DBA a little easier. In addition, SSMS has gotten a speed boost, thanks to faster  communications between SSMS and the SQL Server engine.

Here is what I consider to be some of the most important improvements in SSMS 2008:

  • Activity Monitor
  • Object Explorer Details
  • Object Search
  • Multi-Server Queries
  • Intellisense Added to the Query Editor
  • T-SQL Debugger Added to the Query Editor

Besides these key new features, there are other hidden gems that I hope you discover for yourself when you begin using SQL Server 2008 SSMS.

Activity Monitor

In SQL Server 2005, it was easy to find and start the Activity Monitor. You just opened up the Management object in SSMS and double-clicked on Activity Monitor, and you could instantly view process information. In SQL Server 2008, they have made the  Activity Monitor a little harder to find, but once you have learned where it is, you will probably be very impressed with all the new features that have been added.

To start Activity Monitor in SQL Server 2008 SSMS, right-click on the SQL Server name you want to monitor, and then click on Activity Monitor. The following screen appears:

Figure 1: The Activity Monitor has had a radical facelift, and feature boost.

Immediately, you can see that Activity Monitor looks entirely different than in SQL Server 2005. The first things that jump out at you are the four graphs. These display % Processor time (of the SQL Server process “sqlserv ” spread over all of the available CPUs, not for the entire server), Waiting tasks, Database I/O, and Batch Requests/sec. In the past when you needed this information, you had to use System Monitor or some other tool. Now, if your server is behaving strangely and you need a quick overview of what is happening, you can get it directly from Activity Monitor.

Below the graphs you will find four additional windows of information, the first of which (Processes) is shown in Figure 2:

Figure 2: This looks a little more familiar. Here, we see all of the active SPIDS.

When you open the Processes window, you see the SPIDs that you are so familiar with from the SQL Server 2005 SSMS Activity Monitor. Now, not only can you see the SPIDS, and sort them, but you can also filter on them using the drop-down boxes at the top of each column. And if you right-click on any of the SPIDs, you can choose to automatically launch Profiler, which will begin a trace on the SPID in question. This makes it very easy to begin a Profiler analysis of any SPID that you want to investigate.

Next, we move on to the Resource Waits window, shown in Figure 3:

Figure 3: We can see the current wait states of active threads.

The ‘Resource Waits’ screen provides a snapshot of key resource waits occurring on the server, thereby helping you to identify potential trouble with your SQL Server. Resource waits measure the amount of time a worker thread has to wait until it can gain access to the resources on the server that it needs, such as memory or CPU. A high resource wait time might indicate a resource bottleneck. As with Processes, you can sort and filter on any column.

The third window is Data File I/O, as shown in Figure 4:

Figure 4: Use the Data File I/O screen to identify databases with heavy I/O activity.

If you suspect that a particular database is being heavily hit with disk I/O, you can quickly find out by using the Data File I/O screen. You can sort and filter on any column with this or the other related screens.

The final screen is "Recent Expensive Queries":

Figure 5: Want to know what your most expensive queries are? Find out here.

If you are having performance problems due to resource-intensive queries, then the Recent Expensive Queries window will show you the most recent expensive queries (those currently in cache), allowing you to sort or filter them by any column, making it easy to identify problem queries. If you right-click any of the queries, you have the option of displaying the entire query (not just the small part of the query you see in the window) and you also have the option of displaying a graphical execution plan of the query.

Another feature that you might miss, if you are not careful, it the use of Tool Tips throughout all the screens of the Activity Monitor. If you move the cursor on top of almost any text on the Activity Monitor screen, a Tool Tip will appear, providing you with useful information on what you are seeing. Most of the data displayed in the Activity Monitor are from DMVs. Many of the Tool Tips even tell you the name of the DMV used to return the data you are viewing.

When you first lay hands on a copy of SQL Server 2008, you should start by trying out the new Activity Monitor. I guarantee it will make it much easier for you to quickly get a high-level perspective on what might be ailing your SQL Server. Once you know the big picture, then you can use other tools, such as DMVs, Profiler, or System Monitor, to drill down for more details.

Object Explorer Details

In SQL Server 2005, the Object Explorer’s  ‘Details’ screen, the one that appears by default to the right of the Object Explorer, wasn't particularly useful. Essentially, it just displayed the same information that you saw in the Object Explorer and I generally closed it because it didn't offer me any value.

This has changed in SQL Server 2008. Instead of repeating what you see in Object Explorer, you are generally presented with a lot of useful information about the object selected in Object Explorer. The level of detail depends on which object in the Object Explorer you have selected. For a quick demo, let's take a look at the Object Explorer Details window when "Databases" has been selected in the Object Browser.

Note
If the Object Explorer Details window does not appear by default when the Object Explorer is displayed, either press the F7 key, or select "Object Explorer Details" from the "View" menu.

Figure 6: The Object Explorer Details screen can provide detailed information about many of the objects found in the Object Explorer.

Figure 7: You can display up to 36 different columns of information about each database.

When I click on "Databases" in Object Explorer, I can see in the Object Explorer Details screen a variety of information about each of the databases, such as its Policy Health State, Recover Model, Compatibility Level, Collation, and so on. Notice the small folder icons next to System Databases and Database Snapshots in figure 6 above. Since these are represented as folders, you can click on these to drill down for additional information.

By default, five columns of data are displayed for each database, although you can only see four in figure 6 because of the lack of room. But what is really cool is that you are not limited to only five columns of data. If you right click on any of the column headings, you get a drop-down box, see figure 7, which allows you to select up to 36 different columns to be displayed. In addition, you can sort any of the rows, and you can move them in any order you prefer.

Any changes you make are automatically remembered, so the next time you come back to this screen, it will appear just like you left it, as you see if figure 8.

Figure 8: I have rearranged the screen to better meet my needs and interests. Notice that I have listed the Database ID right after the database name. Now I no longer have to look up the ID whenever I need it.

 What if you decide that 36 rows of data are too much to deal with inside of SSMS? One option would be to select all of the rows displayed in the Object Explorer Details screen you want to work with, then press CTRL-C, and all of the data is copied to the clipboard in the tab-delimited format, and then you can paste the results directly into an Excel spreadsheet, and voila, you have an instant report describing the current state of all your databases.

As you can see, the Object Explorer Details window has lots of potential for quickly assessing the status of any of your databases.

Object Search

If you looked closely at the past several screen shots, you probably noticed the search box shown in Figure 9:

Figure 9: I bet you can guess what this search box does.

Technically speaking, the Object Search box is part of the Object Explorer Details window, but it works independently of the window. Let me show you what I mean. Let's say that I want to find all the objects, in all the databases on my SQL Server instance, which include the word "currency". To do this, I would enter into the search box %currency%. The percent signs on either side of the word are wildcard symbols, which mean that any object in all my databases that includes the word "currency" will be found, as you can see in figure 10:

Figure 10: You can search for any object using the Search box.

When the search is complete, all objects that contain the word "currency" are displayed, along with their object type, schema name (if applicable), and the path to the object (most of the path has been cut off from figure 10).

Of course, you don't have to use wildcards if you don't want to, but I wanted to show you the power of this feature. One thing to keep in mind about object searching is that the scope of the search depends on what object has been selected in the Object Explorer. If you want to search all databases, then you need to select the "Databases" object. If you only want to select objects for a specific database, then first select that specific database from Object Explorer, and then perform the search. This way, you only get results from the database you are interested in searching.

In the past, finding objects by name was not intuitive or easy. Now, with Object Search, you can quickly find objects on your SQL Server instances.

Multi-Server Queries

While third-party tools have been offering this feature for years, SSMS now offers the ability to query multiple servers at the same time, returning the results to a single window. This makes it very easy for DBAs to run the same script on multiple SQL Server instances simultaneously.

To accomplish this task, the first step is to create a Server Group from the Registered Servers window, then add SQL Server registrations to the group. Next, right-click on the Server Group and select "New Query." A new Query Editor window appears, where you can enter Transact-SQL code. When you click on "Execute," the query runs on all the registered servers belonging to the group, and returns the results in a single window.

Figure 11: You can now query multiple servers from the same Query Editor window.

In the left-hand pane of figure 11, there is a Server Group named "My SQL Servers" that has three registered servers in it. I right-clicked on the "My SQL Servers" group, and a new Query Editor window opened. I then entered a short query and executed it. As you can see, the names and versions of each of my servers were returned. This feature is backward-compatible with SQL Server 2005.

If you want to query just a selection of SQL Servers, rather than all of the SQL Servers you manage, you will need to create separate Server Groups for each subset, and then add the appropriate servers to the groups. If need be, a SQL Server instance can be a member of more than one Server Group. As a result, you have the facility to create Server Groups, to define sets of servers in any combination that you require, in order to control precisely on which SQL Server instances your queries will execute.

IntelliSense Added to the Query Editor

One of the most requested features for the SSMS Query Editor has been IntelliSense, and it's finally available in SQL Server 2008, although it does not provide all the features you may have become accustomed to  when using Visual Studio or third-party add-ins.  For example, it only works with SQL Server 2008 and is not backward compatible with SQL Server 2005, nor does it provide advanced formatting capabilities, or refactoring.

While there are some DBAs who have every Transact-SQL statement and parameter memorized, I'm not one of them. This means that I often spend a lot of time in Books Online looking up the syntax of a particular statement. The addition of IntelliSense reduces my need to refer to Books Online, and so helps me to write code faster, and more accurately (the first time).

Figure 12: Syntax errors become obvious very quickly when they have a red underline underneath them.

You don't have to do anything to use IntelliSense in the Query Editor. All you have to do is to begin typing. As you type, IntelliSense can automatically:

  • Identify incorrect syntax, underlining it in red so you can immediately identify and correct it.
  • Complete a word as you type in a variable, command, or function once you have typed in enough characters so that it is uniquely identified.
  • List the available parameters required by a function or stored procedure.

  • Open a list that provides available database objects and user-defined variables that you have previously declared.

Figure 13: IntelliSense helps you to complete variables, commands, or functions as type them in.

Figure 14: IntelliSense can tell you what parameters are needed for a function or a stored procedure.

Figure 15: Under some conditions, IntelliSense can provide a list of available objects you can select from.

Using IntelliSense can take a little time to get used to, but once you do, you will find that it can help boost your Transact-SQL coding performance.

T-SQL Debugger Added to the Query Editor

Another feature that has been sorely missing from SQL Server 2005 is a query debugger. In SQL Server 2008, a debugger is now integrated within the Query Editor, making it easy for you to debug your Transact-SQL code.

The debugger includes many features, including the ability to:

  • Step through Transact-SQL statements, line by line, or by using breakpoints.
  • Step into or over Transact-SQL stored procedures, functions, or triggers that are part of your code.
  • Watch values assigned to variables, in addition to viewing system objects, including the call stack and threads.

To use the debugger, all you have to do is to add the code you want to debug to a Query Editor window, then click on the green debug arrow  (next to Execute on the Query Editor tool bar), click ALT-F5, or go to the "Debug" menu option and select "Start Debugging." At this point, the Query Editor starts the debugging process, and you can use any of the features previously described to step through your code, looking for potential problems.

Figure 16: Transact-SQL is currently being debugged.

If you have not used a debugger before, then it might take some time getting used to, as you have the learning curve of understanding exactly how the debugger can be used, besides learning the proper techniques. If you have used a debugger before, you should be able to begin using it immediately, as its features are very straight-forward for the experienced developer.

Keep in mind that the debugger should only be used on test servers, not on production servers. This is because debugging sessions may take some time to complete, and often during debugging sessions, locks are acquired and may be held by the debugging session for long periods of time.

Summary

This ends our brief look at some of the new, cool features added to the SQL Server 2008 SSMS. All of these features are available in all editions of SQL Server 2008 that come with SSMS. As you can imagine, this is just a sampling of the many new improvements made to SSMS. When you first start using SQL Server 2008 SSMS, the first thing you will want to do is to spend extra time learning about all these features, and the many more that I did not have time to discuss. By investing time upfront, you will more quickly be able to master the new features, making you that much more productive right at the start.

This is taken from the first chapter of Brad's new book, 'Brad's Sure Guide to SQL Server 2008', which is now released by Simple-Talk publications. You can obtain a free copy by downloading SQL Toolbelt from here

Brad McGehee

Author profile:

Brad M. McGehee is a MCITP, MCSE+I, MCSD, and MCT (former), and, until recently, the Director of DBA Education for Red Gate Software. He is now the editor of the SQL Server Central Stairway series. Brad is also an accomplished Microsoft SQL Server MVP, with over 16 years SQL Server experience and over 7 years training experience. Brad is a frequent speaker at User Groups and industry events (including SQL PASS, SQL Server Connections, devLINK, SQLBits, SQL Saturdays, TechFests and Code Camps), where he shares his 16 years of cumulative knowledge and experience. A well-respected name in SQL Server literature, Brad is the author or co-author of more than 15 technical books (freely available on SQLServerCentral) and over 275 published articles.

Search for other articles by Brad McGehee

Rate this article:   Avg rating: from a total of 209 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: About bloody time.
Posted by: dj (not signed in)
Posted on: Friday, August 15, 2008 at 10:54 AM
Message: A major sore point for me was the loss of the T-SQL Debugger, present in 2000 but dropped in 2005. Granted, it could be a little clunky, but it was handy. Welcome back, old friend!

Nice summary, Brad.

Subject: Which features will work against a 2005 server?
Posted by: JJ (not signed in)
Posted on: Saturday, August 16, 2008 at 7:45 AM
Message: Will any of these new features in SSMS work when connecting to a SQL 2005 server?

I can upgrade my workstation to SQL 2008, but we are not ready to touch the production servers (or even development, since they must match production).

Subject: Which SSMS 2008 Features Work with SQL Server 2005
Posted by: bradmcgehee (view profile)
Posted on: Monday, August 18, 2008 at 1:23 AM
Message: It's hit and miss. Some of the new features work, but other don't. For example, the T-SQL Debugger and Intellisense won't work with 2005. On the other hand, replacing 2005 SSMS with 2008 SSMS won't hurt you (you won't loose functionality), so moving your workstation to SSMS 2008 is not a problem.

Subject: Management Studio Improvements in SQL Server 2008
Posted by: Anonymous (not signed in)
Posted on: Monday, August 18, 2008 at 8:04 AM
Message: Intellisense kills the production server, because lot of table and columns exist. Will see how far it successed.

):(

Subject: SSMS 2008 is a step forward
Posted by: jerryhung (view profile)
Posted on: Tuesday, August 19, 2008 at 11:15 AM
Message: I am getting used to the Debugger
Activity Monitor is not bad
IntelliSense is good if I don't have SQL Prompt, but sometimes it doesn't even work

Overall I like where this is going, and hope next version will be even better

Subject: Not worth it for those who still have 2000 DBs
Posted by: Kevin (view profile)
Posted on: Tuesday, August 19, 2008 at 2:50 PM
Message: I'd warn against updating your workstation to 2008 SSMS if you're still working with SQL 2000 instances, you'll lose the activity monitor for sure... I used it extensively in 2005 against a 2000 DB, I don't know why they couldn't include it in SSMS 2008.

All in all I don't think any of the new features work against a 2000 DB... Object explorer details maybe, but not the search. Quite a let down for my team.

Subject: Pointers to other URLs
Posted by: Anonymous (not signed in)
Posted on: Tuesday, August 19, 2008 at 4:41 PM
Message: In this BLOG you said "There are so many improvements that it would take a short book to cover them all", atleast can you please add the URL to those pages within your page?

Subject: Dyson should be worried
Posted by: Adolf garlic (not signed in)
Posted on: Wednesday, August 20, 2008 at 4:09 AM
Message: Considering that MS have had 13 years to improve the UI. It still sucks.

Intellisense is rubbish.
Query results window has no functionality or export facilities.

What object search? Use sqldbtools.com instead

No clipboard ring
etc etc

I could go on

Comes with a free mini-disc player...

Subject: Intellisense an oxymoron ?
Posted by: GrumpyOldDBA (not signed in)
Posted on: Wednesday, August 20, 2008 at 4:58 AM
Message: ever tried typing select count(*)... if you have a table called country ? Intellisense ( ! ) will change count to country as soon as you hit the (

Subject: another poor change
Posted by: GrumpyOldDBA (not signed in)
Posted on: Wednesday, August 20, 2008 at 5:00 AM
Message: oh yes I also forgot - help the user from sql2000 by making the green tick ( execute ) the debugger in ssms2008

Subject: Excellent
Posted by: Granted (view profile)
Posted on: Wednesday, August 20, 2008 at 7:23 AM
Message: Great little round-up. Thanks Brad. I've been working with the beta for over six months and I missed a few of these. Too much focus on TSQL I guess.

Subject: Very interesting!
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 20, 2008 at 8:55 AM
Message: But one thing surprises me... the lack of an SQL code formatting tool. Something that "beautifies" your SQL code providing proper indentation etc. I miss this from PL/SQLDeveloper that I used on Oracle.

Subject: Nice.
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 20, 2008 at 8:59 AM
Message: Nice, I was looking for Activity Monitor the other day and couldn't find it. Search gives me an error every time - something about casting a boolean to a string.

Query results window does have export facilities - right click -> save as csv.

I also like the ability to assign a color to each connection in the connection properties dialog box.

Subject: 2 steps forward, 2 steps back?
Posted by: Chris B (not signed in)
Posted on: Wednesday, August 20, 2008 at 10:03 AM
Message: I just installed 2008 on a spare workstation the other day, so I've only touched on a few of the new features. But to some of the points in the article & from other comments:

activity monitor - Looks nice, but the fact that it doesn't work w/ SQL2k means I have to keep the old version around or RDP into the server anytime I want to get a simple process list. There's no excuse for this; connecting to an old server should *at least* get me the same info that version would show. Leave out the fancy new stuff, but a process list & lock info of some sort should be available.

query window navigation - Query analyzer had a handy shortcut (Ctrl-W) for pulling up a window selector. The list had columns for server, db, user & file. This went away in 2005 with the "Windows..." command being the closest alternative. I wasn't able to find a way to assign this command to the same hotkey.
In 2008, for me, it doesn't work at all! Hitting "Windows..." in the windows menu causes the UI to freeze while it seems to be opening some invisible window. I'm hoping this is due to my upgrade...

So as usual, this won't come near any of my production servers until they release a servicepack... and from a client-tools angle, these flaws might hold me back further, unlike SQL 2005.

Subject: 2 steps forward, 2 steps back?
Posted by: Chris B (not signed in)
Posted on: Wednesday, August 20, 2008 at 10:31 AM
Message: I just installed 2008 on a spare workstation the other day, so I've only touched on a few of the new features. But to some of the points in the article & from other comments:

activity monitor - Looks nice, but the fact that it doesn't work w/ SQL2k means I have to keep the old version around or RDP into the server anytime I want to get a simple process list. There's no excuse for this; connecting to an old server should *at least* get me the same info that version would show. Leave out the fancy new stuff, but a process list & lock info of some sort should be available.

query window navigation - Query analyzer had a handy shortcut (Ctrl-W) for pulling up a window selector. The list had columns for server, db, user & file. This went away in 2005 with the "Windows..." command being the closest alternative. I wasn't able to find a way to assign this command to the same hotkey.
In 2008, for me, it doesn't work at all! Hitting "Windows..." in the windows menu causes the UI to freeze while it seems to be opening some invisible window. I'm hoping this is due to my upgrade...

So as usual, this won't come near any of my production servers until they release a servicepack... and from a client-tools angle, these flaws might hold me back further, unlike SQL 2005.

Subject: Projects and TFS
Posted by: Randy In Marin (view profile)
Posted on: Wednesday, August 20, 2008 at 10:59 AM
Message: Is the integrated development environment still non-integrated? Does a SSMS project still have an inflexible folder structure? It's not reasonable to expect a developer to have 1000+ scripts in one folder.

It's also a PITA to have incompatible projects when using SSMS and VS2005. Source control should hold one master copy of the source. Can the tools all gracefully use one master copy - all projects point to the same source? If I create a project in data dude and add it to source control, I need the developers using VS2005, VS2008, SSMS 2005, and SSMS 2008 to be able to use a single standard folder structure. I hope that VS2008 and SSMS 2008 will solve some of this for me. I would like to focus on issues other than how to make the tools work the way we need them to.

Subject: Great Writeup
Posted by: Buck Woody (not signed in)
Posted on: Wednesday, August 20, 2008 at 11:06 AM
Message: Couldn't have said it better myself!

Buck Woody - SQL Server Management Team, Microsoft

Subject: A DBA is not a developer and vice versa
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 20, 2008 at 3:48 PM
Message: When will Microsoft figure out that DBA's and database developers work in different ways? When will they finally grasp the fact that DB developers are not C# or VB developers? SSMS 2005 and 2008 are examples of tools that don't do a good job for either practitioner. Wake up MS and look at the third party tools that provide the proper functionality that you should include in your product. And for goodness sake, bring back Query Analyzer and work on improving it rather than forcing us either to work in a VS-like manner or to pay someone else for a decent environment.

Subject: A list of all the improvements to SQL Server 2008 SSMS
Posted by: bradmcgehee (view profile)
Posted on: Wednesday, August 20, 2008 at 4:30 PM
Message: In regard to this question, I have not seen a comprehensive listing of all of the new features. The closest listing I have seen is available here:

http://sqlblogcasts.com/blogs/simons/archive/2008/03/11/SQL-Server-2008---Whats-new-in-the-tools.aspx

Subject: Its still a step forward
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 20, 2008 at 5:20 PM
Message: By all accounts if any dba has been in the same job for the same company for any amount of time.
You should have, by now, created all the system control scripts you would ever need.
HINT ( you have been including parameters in your scripts havent you? ) and saving them as fuctions havent you? Then of course you just call them and chuck the function a variable ?
Thus you wouldn't actually need to spend very much time in ssms anyway. All you should need is a dashboard report. (you have been using reporting services) and you can use parameters in them aswell, run them from ssis

Oh i could go on about the new product, but i wont because really we are the ones that drive the development.
We are the ones that fire off those emails to the development teams giving them useful advice and insight into what we might need or could really use. We dont just complain all the time do we.

Oh and Who needs query Analyzer , Haven't you tried right clicking on tables and databases etc all the same functions and abilities to script out anything to a new window or file or anything else for matter are right there at you finger tips.




Subject: SMO- great interface
Posted by: Anil Yadav (view profile)
Posted on: Thursday, August 21, 2008 at 4:56 AM
Message: SMO is best ever interface shipped by Microsoft


http://anil83.blogspot.com/

Subject: who's activity monitor
Posted by: randyvol (view profile)
Posted on: Friday, August 22, 2008 at 11:05 AM
Message: First - great summary write up.

One thing though, which team at MSFT is trying to take credit for activity monitor?

I ask this because, having just gone through the weed patch in updating my PC to Vista Ultimate, I have the same activity monitor sans SQL Server 2008 - looks like an overhauled perfmon was done to the Vista O/S rather than a nifty new activity monitor created for SQL Server 2008.

Of course I could be wrong and am just witness to a clever chuck of code re-use on the GUI portion of both... Just sayin'

Regards


Subject: Danger Will Robinson
Posted by: Regan Galbraith (view profile)
Posted on: Friday, August 22, 2008 at 12:10 PM
Message: Looking at the feature:
"Multi-Server Queries
While third-party tools have been offering this feature for years, SSMS now offers the ability to query multiple servers at the same time, returning the results to a single window. This makes it very easy for DBAs to run the same script on multiple SQL Server instances simultaneously."

I thuogh - that's nice. Wonder if it work's across version (my laptop has SQL 2000, SQL2005 and SQL2008).

Running @@version happily reported all the different version, as expected. Then I thought - hmm - wonder if I could use the for deployment to multiple servers. So I tried to run:
CREATE DATABASE Test

This threw some errors, and created on the SQL 2008.... or so I thought. Looking at the DB listing (after a refresh), I found DB entires for all the databases). hmm - a little bug, but ok.
Then I though ... let's get rid of the databases...
DROP DATABASE test
Again - SQL2008 reports it did it, but SQL2005 said it error, and no response re: the SQL 2000. Refresh DB listing and ... ther are all deleted.

So, firstly, there seems to be a bug (I'm using Developer Edition, SQL 2008 RTM), regarding error's/successes of the commands. BUt, MORE IMPORTANTLY - look at what I was doing...

Let's say you have 2 DB servers - test and live. Let's say you want to DROP a DB on the test server, and you connect to a GROUP, and not the server. DROP DATABASE OurBankingSystem - 2 reponses... and yes, that was your LIVE OurBankingEngine database you just dropped!!!!

Subject: object explorer details in SSMS 2005
Posted by: David McKinney (not signed in)
Posted on: Tuesday, August 26, 2008 at 6:45 AM
Message: A word in defence of object explorer details in SSMS 2005.

What it did allow you to do is select multiple objects, and perform selected actions on them.

For example, if you go to the tables node, you can see the list of tables in Ob Ex Details. You can then choose a few of them (using CTRL), and right-click to script them or delete them. This isn't possible via the object explorer.

Subject: Thanks
Posted by: Anonymous (not signed in)
Posted on: Tuesday, August 26, 2008 at 8:00 AM
Message: Great review of the major changes, looking forward to reading the rest of your book!

Thanks
Ian

Subject: A little happier on this end
Posted by: Anonymous (not signed in)
Posted on: Tuesday, August 26, 2008 at 8:32 AM
Message: I remained a QA devotee mainly because it's light-weight (loads faster) than SSMS and allows an option to automatically create drop command when scripting a db object. SQL2008 SSMS seems to load quicker than 2005 and also includes an extra context command to 'Drop and Create to' script for a selected object. This I like.

At this point, we're reommending SSMS 2008 client tools on the developer computers, even if they do not install the full database engine. And no, will not uninstall QA anytime soon. I still love the quickness, have enough add-in tools and shortcuts for the power usages, and especially love the 'Start SQL Server if it is stopped' option at login. I have four versions of SQL on my dev machine and don't start them up unless I need to.

Thanks for the informative article,
Tom

Subject: Not so bad
Posted by: SwissJon (not signed in)
Posted on: Tuesday, August 26, 2008 at 9:07 AM
Message: You know, there's a little saying that "You can please some of the people some of the time, but not all of the people all of the time" and never a truer word has been said in this case.

I come from SQL6.5 onward, and have used Enterprised Manager extensively, but after getting over the learning curve, SSMS is by far my preferred tool. And because of that, I can really appreciate the upgrades in 2008.

OK, so SQL 2000 isn't supported widely, but Microsoft never pretended that they would continue to support an 8 year old application, did they?

My boss won't shell out for the clip on products that some DBA's here are crowing about, so the intellisense is a welcomed feature, as is a lot of other upgrades, most especially the perceiveable performance boost.

All in all, I like SSMS 2008, it's got a few places where it can be improved, but then MS were late enough releasing it in the first place, so this will do until SSMS 2011 :-)

Subject: express
Posted by: Mactor (not signed in)
Posted on: Tuesday, August 26, 2008 at 9:28 AM
Message: are this tools included in the 2008 express?

Subject: SSMS2008 cannot replace SSMS2005 for some components
Posted by: Anonymous (not signed in)
Posted on: Tuesday, August 26, 2008 at 1:33 PM
Message: >>
bradmcgehee wrote:It's hit and miss. Some of the new features work, but other don't. ... On the other hand, replacing 2005 SSMS with 2008 SSMS won't hurt you (you won't loose functionality), so moving your workstation to SSMS 2008 is not a problem.
<<
In fact, SSMS2008 cannot work with 2005 Integration Services, or Reporting Services (and perhaps more) so you WILL need SSMS2005 around for as long as you need to reference earlier SqlServer components.

Subject: SSMS2008 cannot replace SSMS2005 for some components
Posted by: bradmcgehee (view profile)
Posted on: Wednesday, August 27, 2008 at 5:34 PM
Message: Thanks for making this point. I have not yet had time to even explore Integration Services or Reporting Services in SQL Server 2008. If I only had 48 hours in each day instead of 24, that would be great.

Subject: DTS Designer Issues
Posted by: Anonymous (not signed in)
Posted on: Thursday, August 28, 2008 at 8:26 AM
Message: Looks like they forgot to fix the DTS designer tool (seperate install) to work with SQL 2008 for legacy packages in SQL2000. It wouldn't work in 2005 either without a fix. Thanks Microsoft!

Subject: excellent
Posted by: unruledboy (not signed in)
Posted on: Friday, August 29, 2008 at 4:30 AM
Message: hi, really impressive, I love the return of debugger.

yours,
http://www.xnlab.com

Subject: Good and Bad
Posted by: sly-dba (not signed in)
Posted on: Wednesday, September 03, 2008 at 11:58 AM
Message: I'm not a fan of the Intellisense and thankfully they provided a way to turn it off, we have developers that love it though. I like a lot of the other funtionality.

It will take a while to learn how to set it up and to alter our tasks to work with it, but all in all it seems to be a change for the better.

I'm just hoping that we won't have to go through these types of changes every 2-3 years as MS wants to release new versions.

Great article,

Thanks

Subject: Object Explorer Filters
Posted by: Anonymous (not signed in)
Posted on: Wednesday, September 10, 2008 at 10:04 AM
Message: Are filters persisted in this new version? The current version sucks when it comes to filtering objects.

Subject: Create Users
Posted by: Naima (Nigeria) (not signed in)
Posted on: Tuesday, September 23, 2008 at 12:36 PM
Message: How can I write a script to create users in 2005 version? Please help.

Subject: Object search does not work correctly ??
Posted by: Charles Rex (not signed in)
Posted on: Wednesday, September 24, 2008 at 7:17 AM
Message: Hello,

I want to search all tables names matching some criteria.

I select the "Tables" node on the left.

Then in the 'Search' textbox I type %charges%
and press Enter.


In the result list I get stored procedures as well, not just the table names I am interested in ??

Microsoft idiots.



Subject: Perfect
Posted by: Erdal Koç (not signed in)
Posted on: Wednesday, November 05, 2008 at 11:33 AM
Message: This site is very nice and also i have a lot of information from it.

Subject: Good One
Posted by: Anonymous1 (not signed in)
Posted on: Thursday, November 20, 2008 at 2:06 AM
Message: Good Stuff

Subject: Installing SQL Server Management Studio 2008
Posted by: Vin (not signed in)
Posted on: Tuesday, November 25, 2008 at 7:18 PM
Message: http://cogniscribe.blogspot.com/2008/11/installing-sql-server-management-studio.html

Subject: Activity Monitor !
Posted by: samuelT (view profile)
Posted on: Tuesday, June 22, 2010 at 11:23 AM
Message: You say it is an improvement - I can hardly understand a thing it does. In SQL 2005 I could switch from SPID to Object ID to Locking information. Now I cannot find anything about locking and blocking in the entire gui interface

Subject: Excellent Site
Posted by: hetty (view profile)
Posted on: Monday, July 11, 2011 at 9:13 AM
Message: This site is very good and educative.

I learnt a lot from it!

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Highway to Database Recovery
 Discover the best backup and recovery articles on Simple-Talk, all in one place. Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.