If you use SQL Server Management Studio, I've assembled a few helpful tips that can help you save time and work more efficiently.
Here's a video where I discuss and demonstrate the tips.
1. By far the most valuable time saving tip is to use the Object Explorer Details window in SSMS. I have had hundreds of GoToMeeting sessions with customers and consultants who only used the Object Explorer pane and weren't familiar with the benefits of the Object Explorer Details window. If you are using the Object Explorer pane to locate tables or stored procedures, press F7 to open the Details window and save yourself a ton of time. Check out the video to see how to quickly navigate and search using Object Explorer Details.
2. When you are testing or debugging complex queries, error messages will be displayed below the query, showing an error message and noting the line number of the error. If you double click on the error message, SSMS will take you to the line in the query where the error occurred.
3. A related feature is the option to display line numbers next to the query window, allowing you to easily reference the line numbers in the query.
4. If you've ever had to copy the results of a query and paste it into Excel, you should definitely use the Copy with Headers feature. This allows you to easily paste the data with headers into an Excel file. Just right click on the blank square above row 1 in the query results grid and select Copy with Headers--or press CTRL+SHIFT+C.
5. Next is a feature that you may not need to use regularly, but may come in handy for situations where you'd like to save the query results directly to a text file rather than paste them into Excel. Under Options -> Query Results -> SQLServer -> Results to Text, check out the Output format options. SSMS can format the text query results to be comma or tab delimited. But a word of caution: the Comma delimited format does not produce a CSV compliant file--it will not put apostrophes around text field values that contain a comma. So be aware of your data before using that option.
6. The last tip involves how to fix the scaling of SQL Server Management Studio on a high DPI display, such as a 4K monitor, or a high resolution notebook, like the Surface Pro 4. In the video, I show you how to install the fixes outlined in this post on SQL Server Central.
Before the fix, notice that the icons on the left are tiny and the text looks crammed into the window.
After the fix, the scaling is completely different, with properly sized icons and readable text.
I hope you learned at least one new trick to help you work more efficiently with SQL Server Management Studio.
Steve Endow is a Microsoft MVP
for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.
He is the owner of Precipio Services, which provides Dynamics GP
integrations, customizations, and automation solutions.
Steve,
ReplyDeleteThe only quibble I have with Object Explorer Details comes when I've located a stored proc that I want to edit. When I open the context menu, there's no "Modify" option as there is in the full list of procs. Ditto if I search for a table; the context menu doesn't have all of the options, such as "Select Top 1000 rows". That keeps me from using the Search function very much: I can't do the same things with the SQL object as I can in the full list.
Regards,
Steve Erbach
Steve,
ReplyDeleteThe only quibble I have with Object Explorer Details comes when I've located a stored procedure I want to edit. But when I open the context menu for the selected proc, there's no "Modify" option like there is in the full list of procedures. Do you know why that would be?
Steve Erbach
Hi Steve,
ReplyDeleteWhen I right click on a proc in the Details window, it does give me the Modify and Script options. And when I right click on a table, I see Select Top 1000 Rows and Edit Top 200 Rows.
I recall that there were one or two things I couldn't access via the Details window, but I can't find them at the moment.
If you do find a feature that isn't available in Details, locate the object in Details, then click on the Synchronize button at the top of the Details window (shortcut is Alt + Down Arrow). That will sync the Object Explorer pane and highlight the object you had selected in the Details pane, locating it for you.
Let me know if you find a feature that isn't available in the Details pane--I am sure I have seen at least one thing.
Steve
Steve,
ReplyDeleteAh! Right! I should have been more clear that I wasn't seeing the "Select Top 1000" or "Modify" in the context menu of the search RESULTS window. I've never used the Synchronize option in the search results. Thank you.
Steve Erbach
Hi Steve,
ReplyDeleteGood observation, I didn't notice that. I do see that when the object is listed as a search result, the context menu changes. It looks like the Synchronize option is the best bet, as that will locate the object in Object Explorer for you.
Steve