Webcast Tomorrow!
Filed under: PASS, Presentations, SQL 2008, SQL Tips, Syndication
I’m excited to be doing a webcast tomorrow with the infamous illustrious Brent Ozar for Quest’s Pain-of-the-Week. The title is “Getting Started with SQL Server Management Studio,” and as you’ve probably gathered, it’s pretty entry-level stuff. If you read my blog, then chances are you don’t need to watch this webcast. But if you know anyone who’s trying to learn SQL Server or is trying to make the upgrade from 2000 to 2005/2008, this may be a good webcast for them.
I’ve also got a few other speaking engagements coming up:
June 2nd: Cedar Valley .NET User Group
I’ll be reprising my Iowa Code Camp presentation on “SQL Server for the .NET Developer” for CVINETA. This presentation focuses on what you need to know about good table design, indexing strategies, and fragmentation… you know, what you wish every .NET developer knew about SQL Server. 
June 11th: PoTW: Time-Saving SQL Server Management Studio Tips & Tricks
I’ll also be doing this webcast with @BrentO as a follow-up to our webcast tomorrow. It will focus on how to save time and improve your sanity by using some neat little tricks in SSMS 2008.
BOL 2008 Update Released
If you haven’t heard, Microsoft released an update to Books Online for SQL Server 2008 yesterday. You can find the download here:
Registered Servers in SSMS
Filed under: Miscellaneous, SQL 2008, SQL Tips, Syndication
In my last blog post, I discussed changing the color of the status bar in SSMS 2008. I received a couple of comments and even an e-mail discussing how this doesn’t seem to always work. After playing with it for a little bit, I’ve found that the status bar color needs to be set in both Query->Connection->Connect/Change Connection… (here-in referred to as simply the Query menu) and Registered Servers.
Let’s run through this. First, connect to an instance with any color using the Query menu.
Now, create a new registered server. Make sure to use the same server.
New Server Registration
Pick a color, but make sure that it’s different than the previous color. This is just for demonstration purposes only. Since the whole point is to have a consistent color, you would normally use the same color in both connection methods for the same server.
Pick a color
Open a new query window via Registered Servers.
New Query Window
Registered Server Query Window
Here’s what happens when I connect to the same server using both Registered Servers (left) and another window using the Query menu (right).
SSMS - Same Server, Different Colors
For anyone who’s using both the Query menu and Registered Servers to connect to servers, then you should walk through the process of connecting to each server via both means and changing the colors to ensure consistency. I did this for 22 servers and it took me less than 10 minutes.
I hope that helps clear up some of the confusion.
Source: http://sqlfool.com/2009/03/registered-servers-in-ssms/
SSMS Server Settings
I think this has been discussed before on better blogs than mine, but it’s just so darn cool that I want to help spread the word.
In SSMS 2008, you can change the color of the status bar for servers. This gives you a nice visual reminder as to which server you’re currently connecting to. Since I’ve made the DEV/PROD mistake before, this is something I’m a big fan of.
So let’s walk through how you can set this up:
Opening a new connection
Click on Options >>
Connection Properties
Select Use custom color and click on Select…
Pick a color
Choose the color you prefer, then click on OK
Open a new query
Click on Connect.
That’s all there is to it. Pretty easy, huh? Now let’s see what happens when we connect to multiple servers…
Multi-Server Rainbow
Beautiful! SSMS seems to remember the color settings too, so you should only have to set this up once.
Source: http://sqlfool.com/2009/03/ssms-server-settings/
SQL Tweaks and Tools That Make My Life Easier
It still surprises me how many people don’t know about some of the very things that make my job so much easier. So this next post is dedicated to sharing some of the tweaks and tools I’ve run across that will help anyone who works with SQL:
Indexes
Anyone who uses included columns is probably well aware of the frustrations that can come from having to look up information on which columns are included. I wrote a stored procedure, dba_indexLookup_sp, to help me with this, before discovering sp_helpindex2. If you haven’t heard of sp_helpindex2, it’s a re-write of sp_helpindex by Kimberly Tripp. You can find it on Kimberly’s blog. The main difference is Kimberly’s is a system stored procedure (mine is not) and my version returns partitioning information (Kimberly’s does not). Check both out and use whichever one meets your needs best.
KeyBoard ShortCuts
In SQL Server Management Studio (SSMS), click on:
Tools –> Options… –> Environment –> Keyboard
Keyboard Shortcuts
For your copying convenience:
Ctrl+3 Select Top 100 * From
Ctrl+4 sp_tables @table_owner = ‘dbo’
Ctrl+5 sp_columns
Ctrl+6 sp_stored_procedures @sp_owner = ‘dbo’
Ctrl+7 sp_spaceused
Ctrl+8 sp_helptext
Ctrl+9 dba_indexLookup_sp or sp_helpindex2
Please note that these settings will not take effect until you open a new query window. Here’s an example of how you could use this: use Ctrl+4 to find a list of tables, then copy one into your query window; to view a sample of that table’s data, highlight the table name (I usually double-click on it) and press Ctrl+3. It’s a thing of beauty. Oh, and you may want to remove/change the schema filters if you use schemas other than dbo.
Query Execution Settings
After having one too many issues arise from non-DBA’s connecting to the production environment to run a devastating ad hoc, I’ve had all of our developers and analysts adopt the following settings. The only thing difference between my setting and theirs is that I have “Set Statistics IO” selected. FYI – you can also make these same setting changes in Visual Studio.
In SQL Server Management Studio (SSMS), click on:
Tools –> Options… –> Query Execution –> SQL Server –> Advanced
Query Execution Settings
Copy Behavior
This next tip actually has nothing to do with SQL Server, and can be done with any Microsoft product. However, I just learned about it a few weeks ago and already I use it quite frequently.
Holding down “Alt” while you drag your mouse will change your selection behavior to block selection.
Block Selection
Please note: The following tools requires SQL 2008 Management Studio. These tools will also work when you connect SQL 2008 SSMS to a 2005 instance.
Object Detail Explorer
Finally, there’s a reason to use the Object Detail Explorer! My favorite use is to quickly find the table size and row counts of all the tables in a database. If these options are not currently available, you may just need to right click on the column headers and add it to the display.
Object Detail Explorer
Missing Indexes
And lastly, when using SSMS 2008 to execute Display Estimated Query Plan (Ctrl+L), it will show you if you’re missing any indexes. This will even work if you connect SSMS 2008 to SQL 2005!
Missing Index
That pretty much covers it for now. HTH!
Michelle
Subscribe to my blog!
It's easy, free, and will keep you informed of my latest SQL tips and scripts.Like what you see? Subscribe! 
Around the Web
SQLRockstar Blog Ranking
