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:
- Open SSMS.
- From the menu bar, select Tools > Options.
- Go into the SQL Server Object Explorer node.
- From here, you can change your SELECT TOP option, as well as your EDIT TOP option
Screenshot showing the aforementioned dialogue box with its default settings
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
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
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
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
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.
A lot of errors in SQL Server give you a line number to indicate where your code is falling down, but for some reason line numbers aren’t enabled in SQL Server Management Studio by default.
To enable them, simply go to Tools > Options > Expand Text Editor > All Languages > Check Line numbers:
Enabling Line Numbers
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!