SQL Server Management Studio Tips

  • Comments posted to this topic are about the item SQL Server Management Studio Tips

  • Thank you for this great list!  One keyboard shortcut I would like to add is CTRL+SHIFT+R.  This will refresh the intellisense cache to allow that new object you just created to show up when you start typing!.

  • LeeFAR - Monday, August 14, 2017 6:59 AM

    Thank you for this great list!  One keyboard shortcut I would like to add is CTRL+SHIFT+R.  This will refresh the intellisense cache to allow that new object you just created to show up when you start typing!.

    Thanks for feedback and request - I will add this shortcut tomorrow in github repo.

  • Great tips
    Thanks

    SQL 2000/2005/2008/2012 DBA - MCTS/MCITP

  • Well done - great collection of useful info. 

    I will advise, however, developers avoid using GO for inserting sample data as it would be horrifically slow. 

    For example, instead of this:
    INSERT INTO TestData(CreatedDate) SELECT GetDate()
    GO 10

    I would strongly encourage people to do this:
    INSERT TestData(CreatedDate)
    SELECT GetDate()
    FROM (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(x)

    For more records than that I'd use a permanent or inline tally table (or larger group of dummy rows).

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • These tips are really great. However, I was a little disappointed to find I could use the 2 system SP's I use most frequently (sp_tables and SP_columns), into the Keyboard Shortcuts for Favorite Stored Procedures. I put them there but once I tried executing them they failed. They expect parameters, which SSMS doesn't give me a chance to enter. I expected it to paste sp_tables into the query window and then wait for me to type in a portion of the name of the table I was searching for. That didn't happen. SSMS just runs sp_tables and then immediately complained because I didn't supply parameters.

    So, I removed my added custom commands. What's the point of trying to use something when it won't let me?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Kenny Jozi - Monday, August 14, 2017 8:21 AM

    Great tips
    Thanks

    Thank you for your feedback. Be free add some issues and pull requests: https://github.com/ktaranov/sqlserver-kit/blob/master/SSMS/SSMS_Tips.md

  • Alan.B - Monday, August 14, 2017 8:29 AM

    Well done - great collection of useful info. 

    I will advise, however, developers avoid using GO for inserting sample data as it would be horrifically slow. 

    For example, instead of this:
    INSERT INTO TestData(CreatedDate) SELECT GetDate()
    GO 10

    I would strongly encourage people to do this:
    INSERT TestData(CreatedDate)
    SELECT GetDate()
    FROM (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))x(x)

    For more records than that I'd use a permanent or inline tally table (or larger group of dummy rows).

    Yes, I completely agree with you. But this tip excellent works for simple demos.

  • Rod at work - Monday, August 14, 2017 8:54 AM

    These tips are really great. However, I was a little disappointed to find I could use the 2 system SP's I use most frequently (sp_tables and SP_columns), into the Keyboard Shortcuts for Favorite Stored Procedures. I put them there but once I tried executing them they failed. They expect parameters, which SSMS doesn't give me a chance to enter. I expected it to paste sp_tables into the query window and then wait for me to type in a portion of the name of the table I was searching for. That didn't happen. SSMS just runs sp_tables and then immediately complained because I didn't supply parameters.

    So, I removed my added custom commands. What's the point of trying to use something when it won't let me?

    If you have some mandatory parameters in stored procedure you must add it, for example I use sp_WhoIsActive for query shortcut like this:
    sp_WhoIsActive @get_additional_info=1, @show_own_spid=1, @get_full_additional_info=1

    Also could you share sp_tables and SP_columns code via github or post public link here?

  • Hello, where so I find the Favorite Stored Procedures? 
    How do I load them into SSMS?

  • You should be aware that the regex syntax changed dramatically from SQL 2014 to SQL 2016.

    Also, keyboard shortcuts vary depending on the keyboard mapping scheme. Occasionally, my keyboard mapping scheme will have changed when I open up SSMS.

    Also, for the stored procedure shortcuts, if you highlight text, it will supply that text as the first parameter.  Obviously that only works if it only takes one required parameter.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • For the favourites Stored Procedure:- uspBulkUpload: the link is broken 
  • francesco.mantovani - Monday, August 14, 2017 10:24 PM

    For the favourites Stored Procedure:- uspBulkUpload: the link is broken 

    Thanks for issue, I will fix it tomorrow.

  • A trick for CMS...

    A significant limitation with CMS is that the CMS server itself can’t be included in the list of servers.

    You can register the CMS server just use a different value in Server Name.  So if your CMS is SERVER\INSTANCE, you can register as SERVER,PORT.

  • This is a very neatly compiled article. Thank you for it.

    One more thing I find all of us DBAs have to do is to run SSMS against SQL Servers in different domains using different credentials. 
    It is true you can right click on the shortcut and painfully enter the data every time you login.
    But you can use the following tip to achieve that. 

    runas /savecred /user:USER-NAME "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\Ssms.exe"

    Save this shortcut in a different corner of your desktop and color code this shortcut using any of the options of the SSMS.

    Paresh Motiwala Manager of Data Team, Big Data Enthusiast, ex DBA

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply