Become a T-SQL Hero with SQL Prompt

Since 1999, Red Gate Software has produced ingeniously simple and effective tools for over 500,000 technology professionals worldwide. From their HQ in Cambridge UK, they create a number of great tools for MS SQL Server, .NET, and Oracle. The philosophy of Red Gate is to design highly usable, reliable tools that solve the problems of DBAs and developers.

Every year Red Gate selects a number of active and influential community members (such as popular blog writers and community site owners) as well as SQL and .NET MVPs who are experts in their respective fields, to be part of the Friends of Red Gate (FORG) program. I’m proud to announce that I’m part of the 2014 FORG selection. This post is a part of a series of post, in which I try to explain and show you why the tools of Red Gate are so loved by the community.



 
What SSMS misses
The tool that Microsoft provides you with when you install SQL Server is pretty nice. It’s nicely designed (even though I’ve heard other opinions), it’s stable, and it does what it should do: it allows you to administer your servers. But that’s not the only thing that it should do in my opinion. If you take a look at Visual Studio as an example, that studio contains more options that helps developers do their job. And remember, SQL Server Management Studio (SSMS) is actually a Visual Studio instance with a different layout (just check the Ssms.exe.config)…

So why doesn’t SSMS have a schema compare option, like Visual Studio has? Visual Studio is no longer the environment that is used only by developers that work with ASP.NET and C#, but it evolved to much more the last few years. It’s now the tool for working with Data Quality Services (DQS) and SQL Server Integration Services (SSIS). So let’s talk about some other features that SSMS misses in my opinion, and let’s see how SQL Prompt can fill that gap.

 
IntelliSense
SSMS ships with a default intelliSense, but this isn’t an implementation that I would like to see. It misses a few vital features. For example, the fact that SSMS IntelliSense doesn’t take relations between objects into account, is one of the biggest shortcomings. One of the companies that created a tool to fix that is Red Gate. If you install SQL Prompt, you get IntelliSense 2.0, or IntelliSense on steroids if you like.

When you installed SQL Prompt, it gives you suggestions when you write a JOIN clause. This means that it scans column names, and traces primary- and foreign key relationships on the tables you are joining. The join suggestion based on keys can be recognized by the little key symbol in front of it:

 
Object discovery
Whenever you’re working in a database, and you’re writing your queries, there comes a point that you can’t remember a column name or datatype. In SSMS you need to navigate the object explorer to the object (let’s say a table), and generate a create script, or click on the table to get to the column list. SQL Prompt allows you to hover your mouse over an object, and see some extra information:

 
If you click on the popup, you’ll get another popup window with the creation script (by default), or a summary of the object:

 
Scripting options
Whenever you need to script an object, or want to see the contents of for example a Stored Procedure, you need to navigate to the object in your object explorer. With SQL Prompt, you can also use the mouse context menu to script objects. Just right-click an object you referenced in your query, and choose the “Script Object as ALTER” option:

 
This will generate an alter script for the object you selected. This makes it a lot easier to see the contents of a Stored Procedure or View, and change it when needed.

 
Useful functions
The last feature I want to show you is the menu of SQL Prompt. This shows you another set of useful tools and functions. For example, how do you format your T-SQL query? SQL Prompt can do that for you with a few mouse clicks, or if you press the hotkey combination. Another great feature is the “Find Unused Variables and Parameters”. This saves you time when you try to find out which declared variables you don’t use anymore, in a very large query. All of these options can be found in the SQL Prompt menu:

 
If you want, you can also create a style-export for all your colleagues, so your entire department or company formats queries according to the same layout. You can find out more about this in the SQL Prompt menu, under Options -> Format -> Styles. You can export your formatting options as a .sqlpromptstyle file, or import one.

 
Is it worth it?
If you would ask me, my answer would be: yes! Even though it’ll cost you about €285,- (or $390,-), it’s definitely worth it. It saves you a lot of time, and it adds a lot of useful (and needed) features to SSMS.

If you want to try it out, just go to Red-Gate.com, or the product site for SQL Prompt. You can download a trial there that contains all features, for a limited time.

 
If you want to read more about this topic, don’t forget to check out these blog posts:

- Julie Koesmarno: Clean And Tidy SQL With SQL Prompt
- Mickey Stuewe: Becoming a SQL Prompt Power User
- Chris Yates: SQL Prompt – The Power Within

It’s the little things that make a difference

I still can get enthusiastic when I discover tiny new features of SQL Server Management Studio (SSMS). It’s the tool that I use every day, but still I discover new and cool things to use, that I never noticed before.

 
Filter Object Explorer
If you look at the Object Explorer, you’ll see a little button that can come in quite handy: the filter button:

 
This allows you to filter the results in Object Explorer. If you click on it, you will see the filter window pop up:

 
Just as a test, I filtered my object on name contains “Test”. This will filter only the database and object type you selected. In my case, I selected the Table-node, and it will filter only these objects:

 
There are a few drawbacks on this. One of them is that you can’t remove the filter without opening the filter pop-up again. Another one is you only filter results once. If you want to adjust your filter, you need to remove it completely, and reapply your new filter.

 
Scripting Magic
One of the things I use on a regular basis is the “Generate and Publish Scripts” wizard. But did you know this wizard had some hidden gems? One of the options you have, it to script objects to individual files. That’s an option that is hidden in plain sight:

 
But another gem is hidden behind the Advanced button:

 
This allows you to generate insert scripts for you tables, without the use of a 3rd party tool.

 
Splitter Bar
One of the hidden gems I wanted to show you, isn’t one I found out myself. This one I discovered via a blog post from Kendra Little (Blog | @Kendra_Little). She blogged about the Splitter Bar in SSMS, which is quite handy sometimes! Go check out her other blog posts as well, for example about Scripting changes from the GUI.

 
In the picture above you see the same stored procedure (in this case from the AdventureWorks 2012 database), split in 2 by the splitter bar. This makes it for example easier to look at the declare statements in the top of the script, and the query your working on in the bottom of the script.

 
Tab Groups
The last one is one I use on a regular basis. At the past few employers, I’ve worked with 2 monitors. This makes it easy to compare files or result sets. But what if you don’t have that luxury? There’s an option in SSMS to create a new tab group. Just right-click a query tab, and choose the option you like:

 
Let’s say you want to compare the resultsets of 2 queries, you can use the Horizontal Tab Group option:

 
To return to your normal view, just right-click on the tab again, and click “Move to Previous Tab Group”.

 
Save time
One of the things Mickey Stuewe (Blog | @SQLMickey) pointed out, is that you can rearrange the columns in the result window of SSMS. Just drag and drop columns the way you like. It could save you a lot of time rerunning the query to change the order of your columns. The order of the columns is reset the next time you run the query.

 
Never stop learning!
There are plenty more hidden gems in SSMS, waiting to be found by you. So never stop learning, and always try to take it a step further than needed. You’ll be surprised to see what awaits you…

My first month as DBA – The right tools for the job

Last month I started my first real DBA job. Until then I only had “accidental DBA” experience, and I’m glad I got the opportunity to prove myself as a real full time DBA.

As a SQL Server developer you and I both know that using the right tools can be a lifesaver. But my first weeks as DBA gave me the feeling this is more important than ever before. Having the right tools can save you a lot of time, and can actually help you make time for the important stuff. In this blog I’ll try to show you which tools I use nowadays, and the reason why you should use them as well.

 
SQL Server Management Studio (SSMS) for SQL Server 2012
If you’re working with SQL Server, you’ll need a version of SSMS (3rd party tools excluded). My first experience was with SQL Server 2000, and back then the “Enterpise Manager” and “Query Analyzer” were a drama to work with. If you look at the last version of the SSMS that is shipped with SQL Server 2012, then you’ll see that SSMS has come a long way!

Because I’m administering SQL Server 2008R2, I can’t use SSMS 2012 for everything, but it’s still my main tool. Just because of the performance enhancements, and the Visual Studio look and feel.

You can download the studio as a separate installation from this location.

 
SSMSBoost
One of my favorite SSMS add-ins is SSMSBoost. This allows you to quickly create and use snippets in your SSMS, script data with a few clicks, and quickly find objects in your databases.

SSMSBoost won the Bronze Community award for “Best Database Development Product” 2012, so I’m not the only one who appreciates this add-in! You can download the tool from their website. After the installation, you can request a free community license on the website.

 
sp_Blitz
If you take over a server as DBA, there’s only one way to determine the health of that server: sp_Blitz! This script, build by Brent Ozar’s company “Brent Ozar Unlimited” (Website | @BrentOzarULTD ), gives you a full overview of the “health status” of your SQL Server.

This also gives you a list of items you might want to fix, in order to ensure a stable and maintainable environment. The items are sorted based on importance, so you know which items you need to fix first. An excellent start for every new environment!

You can download the sp_Blitz code from this location.

 
sp_WhoIsActive
If you start out as a DBA it’s hard to find a good point to start from. What do you want to fix first? Your users keep complaining that they’re queries are running slow, your manager wants more and more performance from the same hardware without any real hardware changes, etc. A good point to start from is finding our which slow running queries and stored procedures your users are executing.

sp_WhoIsActive, written by Adam Machanic (Blog | @AdamMachanic ), gives you the ability to quickly gather this information, without any hassle. Once you’ve deployed the stored procedure to your machine, you can start using it to pinpoint issues on your SQL Server.

You can download the sp_WhoIsActive code from this location.

 
SQLjobvis
The last hurdle I needed to take, is to find out which SQL Server Agent Jobs were running on our environment, and at which time. Because I didn’t want to document this manually, I tried to find a tool that did this for me. Then I came across SQLjobvis.

SQLjobvis, written by SQLsoft (Website), is a free tool that visualizes the jobs on your SQL Server. It shows you all jobs and the result of the execution. You can select the data you want to see by date, and with color codes it shows the result within the date range you set.

You can download SQLjobvis from this location.

 
SQL Sentry Plan Explorer
And last, but not least: SQL Sentry Plan Explorer. I’m glad Pieter Vanhove (Blog | @Pieter_Vanhove) reminded me I forgot an important tool!

SQL Sentry Plan Explorer, written by SQL Sentry Inc. (Website), is a lightweight standalone app that helps you analyse execution plans. By making it more graphical than the default execution plan viewer in SSMS, it’s easier to spot the bottleneck.

You can download the tool from this location. And don’t forget to install the SSMS add-in, so you can directly view your execution plan in the SQL Sentry Plan Explorer from SSMS, when you right-click your execution plan.

 
What tools do you use?
There are many more DBA’s out there, and every DBA has it’s own toolbelt. So I’d like to know which tools do YOU use to get the job done? Let me now by leaving a comment, or contact me by Twitter or mail, and I’ll add it to the list of must-haves!

#SQLHelp – SQL 2012 Management Studio Freezes

As I told you in a few of my previous blogposts, I try to follow the #SQLHelp hashtag / topic. And two weeks ago, I could help another colleague via this communication channel.

When SQL Server 2012 RTM came out, I installed it as quick as possible. Just to try it out, and to see what the differences were compared to the other version I installed on my machine: SQL Server 2008. When using SQL Server Management Studio 2012, I encountered random freezes of SSMS. The freezes didn’t occur every time I opened a menu, or started a wizard or something. So it was a problem with my installation.

After a while, I remembered that the installations of SQL Server 2005 and 2008 had the same issue. These SSMS installations also froze, because they shared some dll’s with Visual Studio. So the issues I had now, might have the same cause. And eventually I re-applied Visual Studio SP1, and this solved the issue for me.

And after a few weeks, I saw a similar question from Samson J. Loo (Blog | @ayesamson) coming by, using the #SQLHelp hashtag:

@ayesamson, 2012-05-23

has anyone experienced random unresponsiveness with SSMS 2012 to a point where you have to kill the process? #sqlhelp #sql

So because I saw this issue before, I replied to his tweet:

@DevJef, 2012-05-23

@ayesamson: Yes. Are you running into this issue constantly, or just once? Problem might come from shared DLL’s with VS2010…

Apparently he was still having these issues:

@ayesamson, 2012-05-23

@DevJef its been happening more frequently now. I do have VS2010 installed as well. ‪#sqlhelp

So from my previous experience, I gave him the tip to re-apply Visual Studio 2010 SP1:

@DevJef, 2012-05-23

@ayesamson: I had the same issue. I actually fixed it by applying VS210 SP1 again. This might help you as well! ‪#SQLHelp

The next day, I got the confirmation that SP1 was re-applied:

@ayesamson, 2012-05-24

@DevJef I re-applied VS2010 SP1 this morning, rebooted and haven’t had an issue. If I don’t have an issue come Mon. then we’re golden!

And a week later, I got the great news it helped him get rid of the freezes:

@ayesamson, 2012-05-31

@DevJef well I haven’t experienced any lockups with SSMS 2012 since reapplying VS2010 SP1. Thanks!! ‪#sqlhelp

So I was glad I could help him out, and happy he actually got back to me about resolving the issues. So thank you for that Samson! And for the rest of the community, I hope I helped you with writing this post!

Follow

Get every new post delivered to your Inbox.

Join 41 other followers