3 Steps to Work More Efficiently in SSMS.

Throughout my career I have worked with different programming and database tools.  Thinking back the tools that I enjoyed using were the tools that were easy to get the job done, and the tools that I hated using were the tools that were the most difficult to use. Being good at what I do means mastering the tools so that I can be more productive with my time.

Yesterday at SQL Saturday 198 in Vancouver I learned lots of great things with SQL Server, but there were a couple small things that I picked up on to improve my overall performance when working in SQL Server Management Studio (SSMS).

I thought it might be fun to create a list of the thing that I normally do, along with a few new things that I learned just yesterday at SQL Saturday.

1.  Display results in a separate tab.

This has been my favorite for a couple of years now.  Here is how you set it up.

Open SSMS, and from the top “Tools” menu choose “Options…”.

From the options dialog navigate through the heirarchy to the Query Results, SQL Server, Results to Grid page as shown here.

ResultsToANew

You will need to check the “Display results in a spererate tab” checkbox, and I suggest that you also check the “Switch to results tab after the query executes”.

You will need to open a new query windows to see this change.  Once it runs you will end up with the results grid taking the entire query window area, and a new tab up top to switch back to your query.  This saves time when viewing results because you need to do less scrolling to see your results.   This is also impacts the execution plan window.  Give it a try it will look something like this.

tabs_instead_of_split_screen

Once you have this configured, there will be no need to battle with that split screen results again.

2.  Hotkey for SELECT TOP 100 * FROM …

Just learned this one yesterday at SQL Saturday.  Quite often when working in a database, I need to take a look at what some of the data looks like in a table, so I find myself opening a new query window and typing SELECT TOP 100 * FROM Table name. I find myself doing this all the time.  Yesterday I learned a new trick, and that is to set up a keyboard shortcut that allows you to highlighta table name anywhere in a query window, then just hit a shortcut key, in my case CTRL+3, and it runs the SELECT TOP 100 * FROM … query for the table name that is highlighted.

Here is how you do it.   From the options dialog navigate through the heirarchy to the Environment, Keyboard, Query Shortcuts page.

SSMS_Hotkeys

Here, choose the shortcut key you want to map, and enter “SELECT TOP 100 * FROM ” into the Stored Procedure column.  Yes, I know its not a stored procedure, but just give it a try.

Then hit OK on the dialog.  You may need to restart SSMS for this to work.

After restarting, then just highlight a table name in any query editing window and hit the short combination, CTRL + 3 in this example.  This will save me lots of time.

3. sp_help by using Alt+F1

Another big time saver, just highlight the name of a table, then hit Alt+F1, and sp_help will be run against that table name.

Thats my time saving for today.  3 steps to make your SSMS experience more productive.

Enjoy!

Over 300 Dowloads of the Database Health Reports

Today, the Database Health Reports had its 300th dowload.  I would like to say thank you to everyone who is using the product, and an extra thank you to those who have provided me with feedback.

Since I started  planning the Database Health Reports a year and a half ago, until the latest download release on January 10th, 2013, it has been quite an adventure. I have met many great people, learned lots about their specific environments and needs, and grown the product to more than I ever considered.

Here is a history of the Database Health Report project, http://databasehealth.stevestedman.com/about.

My Goal:  I would like to provide the Database Health Reports project for free to as many people as I possibly can for as long as I can.  One day I am sure that it will require more of a support team, and more developers, but as long as I can do this for FREE with just me working on it I will continue to do that.

Download

If you haven’t tried out the Database Health Reports project you can get it from the FREE download page.

I am currently working on Beta 5, and plan to add some great new features in the next beta release.

Thanks!!!

Thanks everyone for using the Database Health Reports project!

-Steve Stedman

SQL Saturday Presentation

I am at SQL Saturday in Vancouver BC today.

Here is the download of my presentation.

Download zip file.

Here is the presentation outline.

  • OVER Clause Enhancements
  • ROWS PRECEDING, FOLLOWING, UNBOUNDED
  • RANGE PRECEDING, FOLLOWING, UNBOUNDED
  • IIF – Immediate IF or Inline IF (from Access)
  • CHOOSE (from Access)
  • OFFSET / FETCH
  • FORMAT
  • CONCAT
  • SEQUENCE (from Oracle)
  • sp_describe_first_result_set
  • New Date and Time Functions
  • Conversion Functions
  • PARSE, TRY_PARSE, TRY_CONVERT
  • THROW exception

VARCHAR diff function

Last month I posted another CTE blog entry Using a CTE to Split a String Into Rows with Line Numbers.  Since then I have used it but, but realized that what I really needed was a diff tool to compare two strings similar to the command line diff tool.

First we take a look the SplitWithLineNumber from the previous article.  This will be used in the CTE diff tool

CREATE FUNCTION dbo.SplitWithLineNumber (@sep char(1), @s varchar(max))
RETURNS table
AS
RETURN (
WITH splitter_cte AS (
  SELECT CHARINDEX(@sep, @s) as pos,
         cast(0 as bigint) as lastPos,
         cast(0 as int) as LineNumber
  UNION ALL
 SELECT CHARINDEX(@sep, @s, pos + 1),
        cast(pos as bigint),
        LineNumber + 1 as LineNumber
  FROM splitter_cte
 WHERE pos > 0
)
SELECT LineNumber,
       SUBSTRING(@s, lastPos + 1,
         case when pos = 0 then 2147483647
         else pos - lastPos -1 end) as chunk
  FROM splitter_cte
);

Next we look at the new function which calls the original, and compares the results.

CREATE FUNCTION [dbo].[varcharDiff](@s1 varchar(max), @s2 varchar(max))
RETURNS table
AS
RETURN (
WITH FirstStringAsTable as
(
SELECT leftDiff.lineNumber,
       leftDiff.chunk AS leftChunk,
       rightDiff.chunk AS rightChunk
  FROM dbo.SplitWithLineNumber(char(10), @s1) AS leftDiff
  LEFT JOIN dbo.SplitWithLineNumber(char(10), @s2) AS rightDiff
    ON leftDiff.lineNumber = rightDiff.lineNumber,
)
SELECT *
  FROM FirstStringAsTable
 WHERE leftChunk <> rightChunk
)

In order to run it we need to include two varchar’s that are slightly diff so that we can run the diff tool against them.

declare @InputString as varchar(8000);
set @InputString = 'Anyone can perform a SQL Server upgrade, but the risk of failure is much
greater if you don’t plan it correctly. By the end of this chapter you will understand how
to plan for the things that could go wrong during the upgrade, and options you can take to
mitigate those risks.
This chapter will cover the details that you will need to know when upgrading a stand alone,
non High Availability (HA), SQL Server from one version to a newer version, with minimal
downtime. I use the term “minimal downtime” as the goal, as it is very expensive to get to the
point of zero downtime with a SQL Server upgrade without an HA solution. For the purpose of
this upgrade process we will be considering a single SQL Server being upgraded, although it
may end up on different hardware or it may be the same hardware.
Upgrading may involve upgrading just the hardware for better performance or more capacity and
not the SQL Server version. The specific upgrade path may depend on your business needs and
the resources or budget that you have available.
Most of the summaries and stories in this chapter are based on my experiences using SQL Server
over the last 21 years. I will refer to several examples in this chapter of things that went
wrong, and then at the end follow up with a case study of an upgrade that went very well.
Considering the things that can go wrong will help you think about ways to prevent them, or
reduce their likelihood of happening.'

declare @InputString2 as varchar(8000);
set @InputString2 = 'Anyone can perform a SQL Server upgrade, but the risk of failure is much
greater if you don’t plan it correctly. By the end of this chapter you will understand how
to plan for the things that could go wrong during the upgrade, and options you can take to
mitigate those risks.
This chapter will cover the details that you need to know when upgrading a stand alone,
non High Availability (HA), SQL Server from one version to a newer version, with minimal
downtime. I use the term “minimalistic downtime” as the goal, as it is very expensive to get to the
point of zero downtime with a SQL Server upgrade without an HA solution. For the purpose of
this upgrade process we will be considering a single SQL Server being upgraded, although it
may end up on different hardware or it may be the same hardware.
Upgrading may involve upgrading just the hardware for better performance or more capacity and
not the SQL Server version. The specific upgrade path may depend on your business needs and
the resources or budget that you have.
Most of the summaries and stories in this chapter are based on my experiences using SQL Server
over the last 21 years. I will refer to several examples in this chapter of things that went
wrong, and then at the end follow up with a case study of an upgrade that went very well.
Considering the things that can go wrong will help you think about ways to prevent them, or
reduce their likelihood of happening.'

And then call the new function.

SELECT *
  FROM [dbo].[varcharDiff](@InputString, @InputString2);

Which produces the following output.

CTE_DIFFWhere we can see that there are 3 lines different between the two varchar strings.  Lines 4, 6 and 12 are the lines that are different, and the left and right pieces are shown here.

Useful for doing Test Driven Development (TDD) to test the output of a function against what yoa are expecting it to return.

I hope this script helps you and that you find enjoy it.

Beta 4 of the Database Health Reports

Just a reminder, we are a couple weeks into beta 4 of the database health reports.  If you haven’t tried it out, take a look at http://databasehealth.stevestedman.com.

 

You can use the Database Health Reports to increase the overall health of your SQL Server from finding duplicate indexes to tracking down wait stats on your SQL server.  Beta 4 has been going well, and I would encourage you to give it a try for free.

 

Find out more and download it here.

http://databasehealth.stevestedman.com/download/

 

Here is a preview of the Waits Advisor.

WaitsAdvisor