SQL Server Management Studio Tips

  • kast218

    SSCertifiable

    Points: 6286

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

  • LeeFAR

    SSCarpal Tunnel

    Points: 4634

    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!.

  • kast218

    SSCertifiable

    Points: 6286

    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.

  • Kenny Jozi

    SSCrazy

    Points: 2004

    Great tips
    Thanks

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

  • Alan Burstein

    SSC Guru

    Points: 61039

    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

  • Rod at work

    SSC-Dedicated

    Points: 33140

    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.

  • kast218

    SSCertifiable

    Points: 6286

    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

  • kast218

    SSCertifiable

    Points: 6286

    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.

  • kast218

    SSCertifiable

    Points: 6286

    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?

  • francesco.mantovani

    Right there with Babe

    Points: 789

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

  • drew.allen

    SSC Guru

    Points: 76595

    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

  • francesco.mantovani

    Right there with Babe

    Points: 789

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

    SSCertifiable

    Points: 6286

    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.

  • DarthJurious

    SSC Journeyman

    Points: 84

    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.

  • Paresh Motiwala

    SSCommitted

    Points: 1741

    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
    DBA, Project Manager, Big Data Enthusiast

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

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