SSMS

SQL Server: Script out all indexes in a database

by

Kendra Little has a gist to script out all indexes HOW TO SCRIPT OUT INDEXES FROM SQL SERVER but it didn’t include XML or columnstore indexes, so I’ve forked her gist and added a few things to it. I changed the FOR XML/STUFF trick into STRING_AGG() (which is SQL Server 2017 onwards) for no other reason than I’m not working with any instance versions less than that.

The updated gist is here.

/*****************************************************************************
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com
Copyright (c) 2018 SQL Workbooks LLC
Copyright (c) 2024 Mitch Wheat
Permission is hereby granted, free of charge, to any person
obtaining a copy of this software and associated documentation
files (the "Software"), to deal in the Software without
restriction, including without limitation the rights to use,
copy, modify, merge, publish, distribute, sublicense, and/or
sell copies of the Software, and to permit persons to whom
the Software is furnished to do so, subject to the following
conditions:
The above copyright notice and this permission notice shall be
included in all copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES
OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT
HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY,
WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING
FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
OTHER DEALINGS IN THE SOFTWARE.
*****************************************************************************/
-- Removed FOR-XML 'trick' used to concatenate comma separated values with STRING_AGG()
-- STRING_AGG() is SQL Server 2017 onwards...
-- Added:
-- is_autocreated
-- xml indexes
-- columnstore indexes
-- Currently not showing:
--
-- 1. xml compression by partition (SQL Server2022 onwards)
-- 2. spatial indexes
-- 3. selective xml indexes
-- 4. NONCLUSTERED HASH indexes
SELECT
DB_NAME() AS [database],
sc.name + N'.' + t.name AS [table],
si.name AS [index],
si.type_desc AS indextype,
CASE si.index_id
WHEN 0 THEN N'/* Heap */'
ELSE
CASE WHEN si.is_primary_key = 1 THEN
N'ALTER TABLE ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N' ADD CONSTRAINT ' + QUOTENAME(si.name) + N' PRIMARY KEY ' +
CASE
WHEN si.index_id > 1 THEN N'NON'
ELSE N''
END + N'CLUSTERED '
ELSE N'CREATE ' +
CASE WHEN si.is_unique = 1 then N'UNIQUE ' ELSE N'' END +
CASE WHEN xi.using_xml_index_id IS NULL AND (si.type = 3) THEN N'PRIMARY ' ELSE N'' END +
CASE
WHEN si.type = 1 THEN N'CLUSTERED'
WHEN si.type = 2 THEN N'NONCLUSTERED'
WHEN si.type = 3 THEN N'XML'
WHEN si.type = 5 THEN N'CLUSTERED COLUMNSTORE'
WHEN si.type = 6 THEN N'NONCLUSTERED COLUMNSTORE'
--WHEN si.type = 4 THEN N'SPATIAL'
--WHEN si.type = 7 THEN N'NONCLUSTERED HASH'
ELSE N''
END +
N' INDEX ' + QUOTENAME(si.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + N''
END +
CASE
WHEN key_column_list IS NOT NULL AND (si.type != 5) THEN '(' + key_column_list + N')'
WHEN xmlkey_column_list IS NOT NULL AND (si.type= 3) THEN '(' + xmlkey_column_list + N')'
WHEN include_column_list IS NOT NULL AND (si.type = 6) THEN '(' + include_column_list + N')'
ELSE N''
END +
CASE WHEN primary_name IS NOT NULL THEN N' USING XML INDEX ' + primary_name + N' FOR ' + xi.secondary_type_desc COLLATE Latin1_General_CI_AS ELSE N'' END +
CASE WHEN include_column_list IS NOT NULL AND (si.type != 5 AND si.type != 6) THEN N' INCLUDE (' + include_column_list + N')' ELSE N'' END +
CASE WHEN si.filter_definition IS NOT NULL THEN N' WHERE ' + si.filter_definition ELSE N'' END +
/* with clause */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL OR columnstore_compression_partition_list IS NOT NULL
THEN N' WITH (' +
CASE
WHEN row_compression_partition_list IS NOT NULL THEN
N'DATA_COMPRESSION = ROW ' + CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + row_compression_partition_list + N')' END
ELSE N''
END +
CASE
WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N', '
ELSE N''
END +
CASE
WHEN page_compression_partition_list IS NOT NULL THEN N'DATA_COMPRESSION = PAGE' +
CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + page_compression_partition_list + N')' END
ELSE N''
END +
CASE
WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL AND columnstore_compression_partition_list IS NOT NULL THEN N', '
ELSE N''
END +
CASE
WHEN columnstore_compression_partition_list IS NOT NULL THEN N'DATA_COMPRESSION = COLUMNSTORE' +
CASE WHEN psc.name IS NULL THEN N'' ELSE + N' ON PARTITIONS (' + columnstore_compression_partition_list + N')' END
ELSE N''
END +
CASE
WHEN si.compression_delay IS NOT NULL THEN N', COMPRESSION_DELAY = ' + CAST(si.compression_delay AS NVARCHAR(16))
ELSE N''
END
+ N')'
ELSE N''
END +
CASE
WHEN si.type != 3 THEN
/* ON where? filegroup? partition scheme? */
' ON ' + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name), N'')
ELSE psc.name + N' (' + partitioning_column.column_name + N')'
END
ELSE N''
END
+ N';'
END AS create_statement,
partition_sums.row_count,
si.is_primary_key,
si.fill_factor,
si.is_unique,
si.auto_created AS is_auto_created,
si.is_hypothetical,
stat.user_seeks,
stat.user_scans,
stat.user_lookups,
user_updates,
(SELECT MAX(user_reads) FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
last_user_update,
partition_sums.reserved_in_row_GB,
partition_sums.reserved_LOB_GB,
si.allow_page_locks,
si.allow_row_locks,
partition_sums.partition_count,
ISNULL(pf.name, '/* Not partitioned */') AS partition_function,
ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
t.create_date AS table_created_date,
t.modify_date AS table_modify_date
FROM
sys.indexes si
JOIN sys.tables t ON si.object_id = t.object_id
JOIN sys.schemas sc ON t.schema_id = sc.schema_id
LEFT JOIN sys.dm_db_index_usage_stats stat ON stat.database_id = DB_ID() and si.object_id = stat.object_id and si.index_id = stat.index_id
LEFT JOIN sys.partition_schemes psc ON si.data_space_id = psc.data_space_id
LEFT JOIN sys.partition_functions pf ON psc.function_id = pf.function_id
LEFT JOIN sys.filegroups fg ON si.data_space_id = fg.data_space_id
LEFT JOIN sys.xml_indexes xi ON xi.object_id = si.object_id and xi.index_id = si.index_id
LEFT JOIN
(
SELECT
i2.object_id,
i2.index_id,
i2.name as primary_name
FROM sys.xml_indexes i2
) sxi ON sxi.object_id = xi.object_id AND xi.using_xml_index_id = sxi.index_id
/* Key list */
OUTER APPLY (
SELECT STRING_AGG(QUOTENAME(c.name) + CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END, N', ') WITHIN GROUP (ORDER BY ic.key_ordinal)
FROM sys.index_columns ic
JOIN sys.columns AS c ON ic.column_id = c.column_id and ic.object_id = c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id = si.index_id
and (ic.key_ordinal > 0)
) AS keys ( key_column_list )
/* XML Key list */
OUTER APPLY (
SELECT STRING_AGG(QUOTENAME(c.name) + CASE ic.is_descending_key WHEN 1 then N' DESC' ELSE N'' END, N', ') WITHIN GROUP (ORDER BY ic.key_ordinal)
FROM sys.index_columns ic
JOIN sys.columns AS c ON ic.column_id = c.column_id and ic.object_id = c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id = si.index_id
and (ic.key_ordinal = 0 and is_included_column = 0)
) AS xmlkeys ( xmlkey_column_list )
/* Partitioning Column */
OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM sys.index_columns ic
JOIN sys.columns AS c ON ic.column_id = c.column_id and ic.object_id = c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id = si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */
OUTER APPLY (
SELECT STRING_AGG(QUOTENAME(c.name), N', ') WITHIN GROUP (ORDER BY c.name)
FROM sys.index_columns ic
JOIN sys.columns AS c ON ic.column_id = c.column_id and ic.object_id = c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id = si.index_id
and ic.is_included_column = 1
) AS includes ( include_column_list )
/* Partitions */
OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM sys.partitions p
JOIN sys.dm_db_partition_stats AS ps ON p.partition_id = ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id = si.index_id
) AS partition_sums
/* row compression list by partition */
OUTER APPLY (
SELECT STRING_AGG(CAST(p.partition_number AS NVARCHAR(16)), N', ') WITHIN GROUP (ORDER BY p.partition_number)
FROM sys.partitions p
WHERE
p.object_id = si.object_id
and p.index_id = si.index_id
and p.data_compression = 1
) AS row_compression ( row_compression_partition_list )
/* page compression list by partition */
OUTER APPLY (
SELECT STRING_AGG(CAST(p.partition_number AS NVARCHAR(16)), N', ') WITHIN GROUP (ORDER BY p.partition_number)
FROM sys.partitions p
WHERE
p.object_id = si.object_id
and p.index_id = si.index_id
and p.data_compression = 2
) AS page_compression ( page_compression_partition_list )
/* columnstore compression list by partition */
OUTER APPLY (
SELECT STRING_AGG(CAST(p.partition_number AS NVARCHAR(16)), N', ') WITHIN GROUP (ORDER BY p.partition_number)
FROM sys.partitions p
WHERE
p.object_id = si.object_id
and p.index_id = si.index_id
and p.data_compression = 3
) AS columnstore_compression ( columnstore_compression_partition_list )
--/* XML compression list by partition: SQL Server 2022 onwards */
--OUTER APPLY (
-- SELECT STRING_AGG(CAST(p.partition_number AS NVARCHAR(16)), N', ') WITHIN GROUP (ORDER BY p.partition_number)
-- FROM sys.partitions p
-- WHERE
-- p.object_id = si.object_id
-- and p.index_id = si.index_id
-- and p.xml_compression = 1 -- 2022 onwards
-- ) AS xml_compression ( xml_compression_partition_list )
WHERE
si.type IN (0, 1, 2, 3, 5, 6)
ORDER BY
sc.name, t.name, si.index_id
OPTION(RECOMPILE);
GO
view raw Script-out-all-indexes-in-a-database.sql hosted with ❤ by GitHub

SQL Server: Compressing a Table and/or Indexes

by

I always forget whether the first syntax compresses the NC indexes as well, so posting here so I don’t forget again!

This compresses just the clustered index (i.e. the table data):

1
2
3
4
-- Just clustered index
ALTER TABLE dbo.Table
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

This compresses all indexes including the clustered index:

1
2
3
4
-- All indexes including clustered index
ALTER INDEX ALL ON dbo.Table
REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = PAGE);

LINQPad script to Generate SQL Server Database Restore Script from Ola Hallengren’s Backup Solution

by

Unless you perform regular restores of your database backups, you don’t know that you actually have a valid backup. In a career spanning over 30 years, I’ve seen two occasions where a company was performing backups (or so they thought!) and sending tapes offsite, assuming they were good when in fact the tapes were blank!

The majority of SQL Server installations use Ola Hallengren’s maintenance solution (and certainly all the ones I’ve had anything to do with).

If you are doing regular (5 minutes or less) transaction log backups, a restore might involve applying quite a few transaction logs.

I’ve written a short LINQPad script here which will generate the TSQL to perform a database restore either from a point in time or the latest available, based upon the default locations and naming conventions used by Ola’s backups. It’s Differential backup aware, as well as creating the multiple Transaction Log restore statements. It’s also takes into account where backups are split into separate backup files (which is quite common). You specify the server name, the database name, the root folder where the backups are stored, and either a point in time or the latest.

Disclaimer: Use at your own risk AND test thoroughly!

Example output:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
USE [master]
 
RESTORE DATABASE [AdventureWorks] FROM
   DISK = N'C:\temp\Backup\K7\AdventureWorks\FULL\K7_AdventureWorks_FULL_20211118_151558.bak'
 WITH NORECOVERY, REPLACE
 
RESTORE DATABASE [AdventureWorks] FROM
   DISK = N'C:\temp\Backup\K7\AdventureWorks\DIFF\K7_AdventureWorks_DIFF_20211118_152101.bak'
 WITH NORECOVERY
 
RESTORE DATABASE [AdventureWorks] FROM
   DISK = N'C:\temp\Backup\K7\AdventureWorks\LOG\K7_AdventureWorks_LOG_20211118_152226.trn'
 WITH NORECOVERY, STOPAT = '2021-11-21 17:07:22'
 
RESTORE DATABASE [AdventureWorks] WITH RECOVERY

SSMS: Tips and Tricks to Enhance Productivity

by

 

Set colours to differentiate between servers/environments

You can set SSMS connection properties to give a visual indication of which server your queries are connected to.

When you connect to a SQL Server instance, click on the ‘Options’ button:

ssms1

Then click on the ‘Connection Properties’ tab and choose a custom colour for your connection:

ssms2

Suggested colours for your environments:

  • Production – Red
  • UAT – Orange
  • QA – Yellow
  • Dev – Blue
  • Local – Green

Once set, every time you open a connection to a server, it will display the assigned colour in the SSMS status bar.

Configure SSMS tabs to only show file names

Follow Brent Ozar’s simple instructions here: SSMS 2016: It Just Runs More Awesomely (It’s not just for SSMS 2016). This makes tabs easier to read and pinning tabs is a great idea for often used scripts. [I also like to set my status bar position to the top of the query window]:

ssms3

While you are in the Options dialog, go to Tools -> Options -> Environment -> AutoRecover and make sure AutoRecover files is turned on, with appropriate values set.

Cycle through clipboard text

All Windows users will be familiar with the shortcut keys CTRL+C and CTRL+V. The ‘Cycle Clipboard Ring’ feature in SSMS keeps track of last 20 items you have cut/copied. You can use CTRL+SHIFT+V to paste the last copied item from the clipboard just as you would with CTRL+V. If you repeatedly press CTRL+SHIFT+V, you cycle through the entries in the Clipboard Ring, selecting the item you want to paste.

This also works in Visual Studio 2015+

List all columns in a table

To quickly list all the columns in a table as a comma separated list, simply drag the ‘Columns’ folder in Object Explorer and drop it onto a query window. This creates a single line of comma separated column names; if you want to format as one column per line, you can use a search and replace utilising a newline with the regex search option turned on.

Highlight the comma separated list of columns you just created, type CTRL+H, turn on regular expression searching, enter a comma followed by a space ,  as the search text, and replace with a comma followed by a newline ,\n

ssms4

Disable Copy of Empty Text

Ever had this happen to you? You select a block of text to copy, move to the place you want to paste it, and then accidentally hit CTRL+C again instead of CTRL+V. Your block of copied text has been replaced by an empty block!

You can disable this behaviour (I have no idea why disabled is not the default): go to Tools -> Options -> Text Editor -> All Languages -> General -> ‘Apply Cut or Copy Commands to blank lines when there is no selection’ and uncheck the checkbox.

ssms7

Set Tabs to Insert 4 Spaces

Avoid indentation inconsistencies when opening TSQL files in different editors: go to Tools -> Options -> Text Editor -> Transact-SQL -> Tabs -> Insert Spaces and click the radio button. Set Tab and indent size to 4.

Use GO X to Execute a Batch or Statement Multiple Times

The ‘GO’ command is not a Transact SQL statement but marks the end of a batch of statements to be sent to SQL Server for processing. By specifying a number after ‘GO’ the batch will be run the specified number of times. You can use this to repeat  statements for creating test data. This can be a simpler alternative to writing a cursor or while loop.

create table MyTestTable
(
Id int not null identity(1,1) primary key,
CreatedDate datetime2
)
GO

This will run the insert statement 100 times:

insert into MyTestTable(CreatedDate)select GetDate()
GO 100

Templates and Code Snippets

Many users are not aware of SSMS’s Template Browser. These templates contain placeholders/parameters that help you to create database objects such as tables, indexes, views, functions, stored procedures etc.

By default when you open SSMS, the Template Explorer isn’t visible.  Press Ctrl+Alt+T or use the View -> Template Explorer menu to open it. One of my favourite templates is the database mail configuration:

ssms8

Template Explorer provides a view of a folder structure inside the SSMS installation, which is located at C:\Program Files (x86)\Microsoft SQL Server\XXX\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql

Templates contain parameter place holders: press Ctrl + Shift + M to open a dialog box that substitutes values for the template place holders:

ssms9

You can also add your own templates. Right-click on the SQL Server Templates node of the Explorer and choose New -> Folder and set the folder name. Then right-click on the folder and choose New -> Template. Add your code, with any parameters defined as:

< ParameterName, Datatype, DefaultValue >

Click Ctrl + Shift + M to check the parameter code blocks are well formed.

Code snippets are similar but simpler without parameters. Type CTRL + K + X to insert a code snippet.

Registered Servers

Most users have a number of servers they frequently connect to. The Registered Servers feature allows you to save the connection information of these frequently accessed servers.

You can create your own server groups, perhaps grouped by environment or by project.

Navigate to View -> Registered Servers. Then right-click on the ‘Local Server Groups’ and click on ‘New Server Registration’, and enter your connection details.

There is also a feature that allows windows only authentication to be used against a central server management server

Built in Performance Reports in SSMS

SSMS provides a number of built in standard reports. To access the database level reports, right click on a Database –> Reports –> Standard Reports –> Select a Report:

ssms6

 Useful SSMS Keyboard Shortcuts

Shortcut Action
CTRL+N Open new query with current database connection
CTRL+O Open a file in a new tab with current database connection
CTRL+R Toggle between displaying and hiding Results Pane
CTRL+M Include actual query execution plan
CTRL+L Display estimated query execution plan
CTRL+TAB Cycle through query windows
F4 Display the Properties Window
CTRL + ] Navigate to the matching parenthesis
CTRL+ALT+T Open Template Explorer
CTRL+SHIFT+M Specify values for Template parameters
CTRL+K+X Insert SQL code snippets
CTRL+SHIFT+U Change text to upper case
CTRL+SHIFT+L Change text to lower case
CTRL+K+C / CTRL+K+U Comment / Uncomment selected text
CTRL+F / CTRL+H Find / Replace

Splitting the Query Window to work on large queries

The query window can be split into two panes so that you can view two parts of the same query simultaneously. To split the window, simply drag the splitter bar at the top right hand side of the query window downwards. Both parts of the split window can be scrolled independently. This is useful if you have a large query and want to compare different parts of the same query.

ssms5

Vertical Block Select Mode

This is a feature I use often. You can use it to select multiple lines or a block of text over multiple lines, you can type text and it will be entered across all the selected rows, or you can paste blocks of text. To use it, hold down the ALT key, then left click on your mouse to drag the cursor over the text you want to select and type/paste the text you want to insert into multiple lines.

Keyboard Shortcut – ALT + SHIFT + Arrow Keys
Mouse – ALT + Left-Click + Drag

Object Explorer details

The Object Explorer Details window is a feature which very few developers use (including me, as I always forget it’s there!). It lists all the objects in a server and additional information like Row Count, Data Space Used, Index Space Used etc. It’s a quick way to see table row counts for all tables in a database.

The Object Explorer Details window is not visible by default. Click F7 or navigate to View -> Object Explorer Details to open it. To add columns, right click on the column header row and select those columns you want to see.

ssms10

Display Query Results in a Separate Tab

If you want to focus on the results after you run a query, and would like to give it as much screen real estate as possible, go to Tools -> Options -> Query Results -> SQL Server -> Results To Grid and enable the option “Display Results in a separate tab”.

Do you Encrypt your Remote Connections to SQL Azure Databases?

by

If you’re not encrypting connections to SQL Azure (or any remote SQL Server instance), then you probably should.

Encrypted connections to SQL Server use SSL,  and that is about as secure as you can get (currently).

[Remember: SSL protects only the connection, i.e. the data as it is transmitted ‘on the wire’ between the client and SQL Server. It says nothing about how the data is actually stored on the server].

Update: Don’t forget to also set TrustServerCertificate=false

SSMS

When you open SSMS’s ‘Connect to Server’ dialog, click the bottom right ‘Options’ button, and make sure you tick the checkbox ‘Encrypt Connection’:

image

SQLCMD

Ensure you add the -N command line option. The -N switch is used by the client to request an encrypted connection. This option is equivalent to the ADO.net option ENCRYPT = true.

e.g.

sqlcmd –N –U username –P password  –S servername –d databasename –Q “SELECT * FROM myTable”

Linked Servers

When creating a linked server to SQL Azure,  the @provstr parameter must be set to ‘Encrypt=yes;’:

-- Create the linked server:
EXEC sp_addlinkedserver
@server     = 'LocalLinkedServername',
@srvproduct = N'Any',
@provider   = 'SQLNCLI',
@datasrc    = '???.database.windows.net', -- Azure server name
@location   = '', 
@provstr    = N'Encrypt=yes;',       -- <<--  Important!
@catalog    = 'RemoteDatabaseName';  -- remote(Azure) database name
go

 

ADO.NET Connection strings

Add “ENCRYPT = true” to your connection string, or set the SqlConnectionStringBuilder property to True.

[Remember: don’t distribute passwords by sending as plaintext over the Internet, i.e. don’t email passwords! ]