Changing Settings in SQL Server Management Studio
Part two of a two-part series of blogs

If you've spent time in the SQL Server Management Studio application you'll probably have noticed that it's not the most user-friendly environment for writing SQL queries. This blog explains how to change a few settings to make your query writing just a little easier.

  1. Configuring SQL Server Management Studio
  2. Useful Settings in SQL Server Management Studio (this blog)

You can find many more articles like this in our complete SQL tutorial.  We're also happy to provide training in person on one of our SQL courses!

Posted by Andrew Gould on 16 February 2012

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Useful Settings in SQL Server Management Studio

There are all sorts of things that you can change using the Options dialog box in SQL Server Management Studio.  This page lists some of the more useful things that you can modify to make your SQL coding experience more enjoyable, or at least less painful!

Fonts and Colours

If you find the default fonts, colours and text size not to your liking you can change all of them as shown below:

Changing fonts

To begin with, select the Fonts and Colors tab on the Options dialog box.


You can change several settings for text in each part of Management Studio.

Changing font settings

The parts of the dialog box highlight with the numbered circles are described below.

  1. Choose which part of Management Studio you want to change the font settings for.  The Text Editor is the area you'll spend most of your time in as it's where you'll write your queries.
  2. Choose which type of text you want to change.  The options in this list will change depending on what you've selected in option number 1.
  3. Choose a font from this list.  You can write your queries in Comic Sans if you really want to, although you'll land yourself with a lifetime ban from the SQL Query Writers Guild if you do!
  4. The default font size is quite small - change it using this option.
  5. Choose foreground and background colours from these two lists.
  6. If everything goes horribly wrong, click this button to return all the font settings to their default values.

Display Line Numbers

By default you don't see line numbers next to your code when writing queries.  You can switch them on quite easily using the Options dialog box.

Line numbering

Expand the Text Editor option by clicking on the + symbol to the left of it, then expand All Languages and click on General.  Finally, check the box next to the Line numbers option.

You can see the difference in the two screenshots below:

SQL without line numbers Same code with line numbers
Without line numbers With line numbers

Line numbers are particularly useful when trying to decode error messages.

Autocomplete or IntelliSense

IntelliSense is the name given to a drop down list that appears to help you finish your sentences in SQL.


Here we've started typing the word Film and the IntelliSense list appears to show us the matching options. When a word is highlighted in the list you can press the Enter, Tab, space or comma keys (among others) to type it in.


IntelliSense is only available from SQL Server 2008 onwards.  This Owl finds the feature pretty handy (if stupidly-named), but if it annoys you and you'd like to turn it off, you can!


Expand the Text Editor section, then the Transact-SQL section and click on IntelliSense.  You can turn the feature off completely by un-checking the Enable IntelliSense.

You can also disable IntelliSense using a different option, as shown below:

Disable IntelliSense

Un-check the Auto list members box shown here to disable IntelliSense in new query windows.

Changes you make to IntelliSense settings will only affect new query windows that you open.

Getting Column Headers when Copying Query Results

If you ever need to copy the results of a query into Excel to format the results you'll want to make sure you get the column headers along with the data.  Strangely, the default option is to not include column headers when copying results, so here's how to change it.

Getting column headers

Expand Query Results, then SQL Server and select Results to Grid.  Check the box shown here to ensure that you pick up column headers next time you copy results.

In SQL Server 2008 and onwards you can choose to pick up the column headers each time you copy the results of a query.

Choosing column headers

Choose the option shown here to include column headers when you copy the results of a query.


Viewing or Editing all the Records in a Table

In SQL Server 2008 and onwards when you choose to view or edit the records in a table you can only do so to a limited number of records.

Editing top 200

By default, you can only view the first 1000 and edit the top 200 records in a table.


Fortunately, you can change this to allow you to view and edit all the records.

Changing number of records

Expand the SQL Server Object Explorer group and click Commands. Change the Table and View Options shown here to any number you like.  If you enter a value of 0 you will see all of the records in a table.

Once you've done this you can right-click on a table and view or edit all of the records in a table.

Viewing all records

Now you can view or edit an entire table.



There are many more settings available in the Options dialog box, but these are a few of our favourites that should make your SQL code writing that little bit easier.  If you have any personal favourites why not share them in the comments below?

  1. Configuring SQL Server Management Studio
  2. Useful Settings in SQL Server Management Studio (this blog)
This blog has 0 threads Add post