Monday, November 5, 2018

SQL & SSMS Tricks

Just posting some tricks I've used or just learned

http://www.e-squillace.com/ssms-tricks-shortcuts/

SSMS Tricks or Options I like
  • ALT + SHIFT =multi-line select > cool
  • Options > Text Editor > All Languages > Scroll Bars > Use map mode for vertical scroll bar > cool
  • Splitting query windows
  • Options > Text Editor > All Languages > check Line Numbers
  • Dark theme/Font
  • Options > keyboard > Keyboard: search command Query.ChangeConnection , press shortcut keys ALT + G, and Assign
  • Options > keyboard > Query shortcuts; Set to anything you want. In future, highlight a table name (with or without schema) and CTRL+3. these are my settings
    • ALT + F1 = sp_help
    • CTRL + F1 = sp_helptext
    • CTRL + 1 sp_who
    • CTRL + 2 sp_lock
    • CTRL + 3 SELECT TOP 100 * FROM 
    • CTRL + 4 SELECT COUNT(*) FROM
    • CTRL + 5 SELECT * FROM
    • CTRL + 6
    • CTRL + 7 sp_helpdb
    • CTRL + 8 USE <database name>
    • CTRL + 9 sp_helptext
    • CTRL + 0 sp_whoisactive
  • CTRL + SHIFT + R to refresh IntelliSense
  • ALT + SHIFT + T to move down 1 line
  • CTRL + SHIFT + ENTER to create new empty line below
  • Options > Query Execution > Advanced - uncheck SET ARITHABORT
  • Options > Query Results - Play the Windows default beep
  • Change Environment (Object Explorer font) to go bigger
  • Script out permissions in SSMS
    • In SSMS (Management Studio) you need to go to Tools-->Options  then look for "SQL Server Object Explorer" and expand it and go to "Scripting".Then look for Object scripting options and change "Script Permissions" to "True".
Starting SSMS with a specific connection and script file - SQLServerCentral

Configuring SSMS for presenting - Paul S. Randal


SSMS Shortcut Keys
Bookmark - CTRL K, K to bookmark, N for Next, P for Previous
ALT+F8 to open Object Explorer
SHIFT+F1 on a keyword/object (or DMV) to open up in browser

SSMS Add-Ons (FREE)


SQL Code tricks

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
--instead of (NOLOCK) on every table
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

-- /* First line. Removing the two dashes activates the block comment
SELECT 
patientname,
Patientid,
Language
FROM whatevertable
Where name = 'Whatever'
-- */ Last line. When the block comment is on, this terminates it


Resources

http://www.sqlservercentral.com/articles/Management+Studio+(SSMS)/160267/