Changing the TOP 1000 option

I often use a quick SELECT TOP 1000 to get a quick understand of what data is stored in an unfamiliar table, or to retrieve a small extract from a view to hand off to someone else in a spread sheet, and that’s… about it.

For those two purposes, do I really need 1000 rows? Certainly not for the first, and the second depends on someone else’s needs – and that’s why I like to lower this to SELECT TOP 100. It’s ever so slightly less intrusive as it’s having to ask for fewer rows, but still gets me enough data for me to get the information I want.

Here’s a very quick run down on how to change your “SELECT TOP” option to the number of your choosing:

  1. Open SSMS.
  2. From the menu bar, select Tools > Options.
  3. Go into the SQL Server Object Explorer node.
  4. From here, you can change your SELECT TOP option, as well as your EDIT TOP option
Top 1000 Option

Screenshot showing the aforementioned dialogue box with its default settings

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Changing Bar Colour Based on SQL Instance Connection

I found something extremely useful whilst perusing the MSDN forums the other day – The fact that you can change the colour of the connection bar in SSMS based on the instance of SQL Server you’re connected to!

It’s extremely easy to implement, but helps differentiate which server you’re looking at when working with multiple servers in multiple tabs. This means you can have one colour for a local server, another for a development testing server, and another for a live environment.

To start using a colour, open up SSMS.

From the Connect to Server dialogue box, click “Options >>” to expand the window.

On the Connection tab, select the instance you’d like to configure.

Go to the Connection Properties tab, and tick “Use custom color” – this while make the “Select” button on the same line available. Click “Select”, and choose a colour you’d like to associate with this instance – note that you can change this as much as you like.

SSMS Custom Colour

SSMS Custom Colour

Now click “Connect” to connect to your chosen instance, and you should notice that the bar towards the bottom of the screen is colour you picked in the last step.

SSMS Bar Custom Colour

SSMS Bar Custom Colour

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Keyboard Shortcut to comment code in SSMS

I’m a bit of a junky when it comes to keyboard shortcuts, and I’ve been wondering for awhile if there’s a shortcut available to comment out code in SQL Server Management Studio.

It turns out that there is!

To comment the code:

Highlight the rows that you would like to comment and press CTRL + K, then CTRL + C

To un-comment the code:

Highlight the rows that you would like to un-comment and press CTRL + K, then CTRL + U

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Connecting SSMS through a specified port

I recently needed to connect my SQL Server Management Studio to a database that wasn’t using the default port of 1433. I’d never needed to do this before, and assumed I’d just need to add :port-number after the IP of the server I was connecting too, but I was wrong!

Now, after a fair amount of googling, there appears to be plenty of information on how to set up an instance of SQL server to listen for traffic on another port – but barely anything about how to actually connect SSMS to it! So I decided to write something quick and simple about how to do it!

It’s still just as simple as the IP:Port, but instead of using a colon like most things, it’s a comma.

SQL Login Port Specification

SQL Login Port Specification

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Keyboard Shortcut to Upper or Lower Case Code in SSMS

If, like me, you like your code to be formatted in a specific way, then this little shortcut may save you a bit of time when you need to pick up and modify someone elses code.

For example, I like all of my SQL keywords to be written in upper case letters, so if I find one that isn’t in a colleagues code – I simply highlight the text with my cursor then press CTRL + SHIFT + U to make all of the letters upper case.

The reverse of this, should you ever need to make letters lower case is CTRL + SHIFT + L.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

Keyboard Shortcut to minimize result set in SSMS

If you’ve ever been working on a large stored procedure, or just a big chunk of SQL, then you know how annoying it is to have half of your screen taken up by the result set when your query doesn’t quite return the data you were expecting and need to go back to the drawing board!

Insteading of grabbing the result set and shrinking it down, then having to drag it back up when you re-execute your code, use this command:

CTRL + R

The same command will also bring the result set back up on screen should it already be minimized!

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)