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
• https://sqlstudies.com/2018/04/04/ssmss-dark-theme/
• or Dark fonts only https://sqlstudies.com/2015/11/16/ssms-setup/
• Making SSMS Pretty : My Dark Theme - SentryOne Team Blog
• https://github.com/tcartwright/SSMSThemeEditor
• 
  
• 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)

• Red Gate SQL Search
• APEX SQL Complete - free SQL complete
• APEX SQL Refactor - free SQL Formatter
• APEX SQL Search
• Poor SQL http://poorsql.com/  - SQL Formatter

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/ 

DBA Salary

Sharing interesting info as I was reading Brent's blog

- at least I was doing pretty OK in Canada, relatively
- DBA in Canada do earn (much less) than USA

What Should We Change About the Data Professional Salary Survey for 2019?
The 2018 Data Professionals Salary Survey Results - Brent Ozar Unlimited®
Analyzing Salary Data with Power BI and R – Part 1 |

Enable Telnet in Windows 10

Telnet is off by default in Windows 10

How to enable it?
Open Command prompt as Admin
Run this code - dism /online /Enable-Feature /FeatureName:TelnetClient
Voila!

Do it via GUI
https://social.technet.microsoft.com/wiki/contents/articles/38433.windows-10-enabling-telnet-client.aspx

SSMS XEvent Profiler to read Extended Events

I'm learning the new SQL Profiler, found in SSMS 17.3+, called XEvent Profiler which uses Extended Events


Resources
Overview of XEvent Profiler in SQL Server Management Studio

Inside the XEvent Profiler - SQL Hammer | SQL Hammer

I didn't know this - DBCC CLONEDATABASE

To copy a database for debug/troubleshoot - but only copy structure & stats

DBCC CLONEDATABASE(SourceDB, TargetDB);

SQL 2014 and above

https://www.itprotoday.com/data-privacy/why-you-should-be-using-dbcc-clonedatabase-performance-tuning-queries

How to use DBCC CLONEDATABASE to generate a schema and statistics only copy of a user database in SQL Server 2014 SP2 and SQL Server 2016 SP1

List All Groups, DisplayName, ServerName in CMS (Central Management Server)

If you use CMS
If you have a large group of servers and can't memorize all the names/IPs
Use below code to show them all (and copy to Excel to format)

Connect to your CMS directly (not as a group) and run below code (using Recursion CTE)

WITH MyCTE
AS (
--root, anchor
SELECT server_group_id, name, G.parent_id, ParentName = CAST('' AS sysname), 1 AS Grouplevel
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
WHERE is_system_object <>1 AND parent_id = 1
UNION ALL
--child
SELECT G.server_group_id, G.name, G.parent_id, parent.name, parent.Grouplevel+1
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
INNER JOIN MyCTE AS parent ON G.parent_id = parent.server_group_id
)
SELECT
G.grouplevel, Parent = CASE G.ParentName WHEN '' THEN G.name ELSE G.ParentName END, Child = CASE G.ParentName WHEN '' THEN '' ELSE G.name END
,G.name, G.ParentName
,svr.name AS 'Display Name',svr.server_name AS 'Server Name'
FROM MyCTE  G
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr ON G.server_group_id = svr.server_group_id
WHERE 1 = 1
ORDER BY
GroupLevel ASC, Parent, Child, [Display name]

Testing SQL connection using file.udl trick

Besides the usual
>telnet SQLname 1433

One can create a file and open it (note: show the extention in your File Explorer settings)
TestDatabase.udl 


Source
https://dougrathbone.com/blog/2013/11/18/testing-connectivity-to-microsoft-sql-server-without-any-tools-installed

Set Default template for SSMS New Query button by editing SQLFile.sql

1. Go to SQL install path, for me it's "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql"
2. Edit the SQLFile.sql in Admin mode
3. Type in whatever you want for default
4. Save and try in SSMS [New Query] button

Source
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b56b9ed6-1786-4ad6-87c9-7e4e81068672/how-to-set-the-default-query-for-the-ssms-new-query-button?forum=transactsql

SQL Cluster Registry Checkpoint

Error
[sqsrvres] GetRegKeyAccessMask: Could not get registry access mask for registry key Software\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Replication (status 2)).

Fix - Populate the registry to the other node

https://www.mssqltips.com/sqlservertip/3440/registry-check-pointing-in-a-windows-cluster-to-bring-sql-server-online/

https://sqlserver-help.com/2015/04/17/information-checkpoint-in-sql-server-cluster-resources/

> cluster res /checkpoints

Find your resource name to replace below
Run in cmd (Admin)

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Cluster" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\MSSQLServer" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Replication" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Providers" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\SQLServerSCP" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\CPE" 

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\SQLServerAgent"

2008 Cluster install fails on newer Windows 2012/2016

Tried to install SQL 2008 R2 on Windows 2016 = fails, cluster verification fails
SQL 2014 was a-okay

PowerShell (Admin mode)
Install-WindowsFeature -Name RSAT-Clustering-AutomationServer

Error to install SQL Server 2008 on Windows 2012 – Marcelo's Spaces
https://marcelodba.wordpress.com/2014/08/17/error-to-install-sql-server-2008-on-windows-2012/

https://manjusullad.wordpress.com/2015/11/26/unable-to-install-sql-2008-on-windows-server-2012-failover-cluster/