SQL Server Management Studio Basics
SQL Server Management Studio (SSMS) is the instance management tool provided by Microsoft for connecting to, configuring, and administering SQL Server.
SSMS is backward compatible with previous SQL Server versions. To get the maximum benefit from SSMS, it is recommended that the latest version be installed on a Jump Host. Using an older SSMS version on a newer instance may cause features to not work properly.
The SSMS version can be determined by clicking Help > About...
SSMS can connect to multiple instance at the same time. Additionally, Multiple copies of SSMS can be opened at the same time.
A chapter dedicated to SQL Server Management Studio is included later in this book.
Additional Reading: SQL Server Management Studio Overview — Microsoft Docs
Active Server Connection
One of the most important things to remember when using SSMS is that the context focus must be correct. Always verify that the correct server and database is selected before executing any query or script.
Look at the lower right corner of SSMS for the focused server and database.
The database focus for the current query can be seen and changed in the toolbar.
Ensure that a query tab is selected.
The lower right of the SSMS application windows displays the current Server, Login and Database for the query. It also shows the spid for the selected tab.
Server Focus
The server connection for the current query can be changed by clicking the Change Connection button to the left of the database name
It can also be changed with the USE command.
USE [msdb]
Database Focus
The database focus for the current query can be changed by clicking the drop down in the upper left of SSMS.
Ensure that a query tab is selected.
Line Numbers
While working on a large SQL script or a stored procedure with many lines of code, it’s beneficial to have the line numbers displayed in the Query Editor. By default, displaying Line numbers in SSMS is turned off. This is sometimes frustrating because it makes the navigation between long lines of code tougher. Also, think about a scenario when you run a TSQL query and SQL Server throws you an error message referencing the exact location of the error.
How convenient would it be if the SSMS Query Editor displays the Line Number out of the box?
This is one of the hidden features of SSMS and you can display line numbers with all the earlier versions of SSMS starting SQL Server 2005. You can enable this feature by navigating to
Tools > Options > Text Editor > All Languages > General > Line numbers
Or
Tools > Options > Text Editor > Transact-SQL (line numbers checkbox)
Once you select the Line numbers checkbox, click OK and return to the Query Editor, you will see that line numbers have been enabled.
You can also leverage a handy shortcut to easily jump to a particular line number – CTRL + G
Object Explorer
The Object Explorer list (left panel) does not update real-time as objects are created. The fastest way to refresh the list is to Right-Click the parent node and select Refresh. All object at or within the node clicked will be refreshed.
IntelliSense
Sometimes when a new database object is created, SSMS is not able to identify the newly created object and may throw an error stating – ‘Invalid object name’
This is because the SSMS IntelliSense cache has not been refreshed and It could take a while before it is automatically updated.
In such cases, use the shortcut CTRL+SHIFT+R to update the IntelliSense cache or navigate to Edit > IntelliSense > Refresh Local Cache.
Word Wrap
Word wrap can be enabled in the query editor via:
Tools > Options > Text Editor > All Languages > General > Word wrap
Syntax Checker
It is not always necessary to execute a script to determine if a syntax error exists. Instead of executing the script, click the checkmark in the toolbar. Then if there are no syntax errors, proceed to executing the script.
Recover Unsaved Queries
In can be frustrating and time costly when unsaved work is lost – due to SSMS crashes or sudden machine reboots due to a windows update. It is very helpful to be aware of the ‘auto-recovery feature’ in SSMS where you can configure how frequently you want to save your work and how long you want to preserve the information. This is not a substitute to saving your work on a regular basis, but can minimize data loss and save rework time, in the case of unfortunate incidents.
Navigate to this setting by going to Tools > Options > Environment > AutoRecover.
Based on the above setting, When SSMS opens it tries to recover the unsaved queries and prompts a dialog box to the user, in the case of an unexpected SSMS shutdown.
If you do not get the file recovery prompt, navigate to the folder below where the backup files are stored:
%AppData%\Microsoft\SQL Server Management Studio\<version>\Backup Files\