Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
581 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Written by Andrew Gould
In this tutorial
Before you start writing SQL queries it's worth spending a little time setting up the SQL Server Management Studio environment to make query-writing as simple as possible.
In order to change the settings in Management Studio, you first have to open it! The screenshot below shows how to do this for a default installation of SQL Server 2008 R2.
The option for Management Studio will be in a folder similar to this one in the Start menu.
When you first open Management Studio you'll be asked which server you'd like to connect to. You don't have to pick one at this stage, but it makes sense to do so.
Choose which server you want to connect to and click Connect. You may have to provide a login and password depending on how the server has been configured.
You don't need to be connected to a server in order to make changes to the settings in Management Studio, but the effects of some changes are easier to see if you are.
At this point you should be looking at the SQL Server Management Studio environment.
This is what your screen should resemble. The Object Explorer window on the left shows you all of the objects stored on the server you chose to connect to.
If you chose not to connect to a server when opening Management Studio you can do so now, as shown in the diagram below:
Click the Connect button and choose Database Engine... from the list. This will open the dialog box that appears when you first open Management Studio.
To change most settings in Management Studio you need to use the Options dialog box. To display it, from the menu choose: Tools -> Options...
The first page you see when opening the Options dialog box.
Now that you've got access to the Options dialog box you can start making changes to some of the settings. The next page of this blog lists a few of the most useful things that you can change 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!
If you find the default fonts, colours and text size not to your liking you can change all of them as shown below:
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.
The parts of the dialog box highlight with the numbered circles are described below.
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.
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:
Without line numbers | With line numbers |
Line numbers are particularly useful when trying to decode error messages.
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:
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.
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.
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.
Choose the option shown here to include column headers when you copy the results of a query.
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.
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.
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.
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?
You can learn more about this topic on the following Wise Owl courses:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.