Get top average elapsed time queries

Ever wondered which queries are hogging resources in your SQL Server database? This handy SQL script can help you identify those troublesome queries by showing you the top 50 based on average elapsed time. It's a fantastic way to quickly pinpoint performance bottlenecks!

SELECT TOP (50) DB_NAME(t.[dbid]) AS [Database Name]
 ,REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10), ''), CHAR(13), '') AS [Short Query Text]
 ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time]
 ,qs.min_elapsed_time
 ,qs.max_elapsed_time
 ,qs.last_elapsed_time
 ,qs.execution_count AS [Execution Count]
 ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
 ,qs.total_physical_reads / qs.execution_count AS [Avg Physical Reads]
 ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,qs.creation_time AS [Creation Time]
--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_elapsed_time / qs.execution_count DESC
OPTION (RECOMPILE);

This script leverages two powerful SQL Server Dynamic Management Views (DMVs):

• sys.dm_exec_query_plan: This DMV provides the execution plan for a query, which is crucial for understanding how SQL Server intends to run your queries and identifying potential issues.
• sys.dm_exec_query_stats: This DMV returns aggregated performance statistics for cached query plans. It's your go-to for seeing how queries have performed over time.

By combining data from these two DMVs, the script gives you a comprehensive overview of your top-performing (or rather, top-resource-consuming) queries, including metrics like average elapsed time, logical and physical reads, and even whether a missing index is suggested in the query plan. This can be an invaluable tool for any database administrator or developer looking to optimize SQL Server performance!

How to find SQL Server Cluster information

 How to find SQL Server Cluster information

In this post, I'll walk you through how to retrieve information about a SQL Server cluster.

SELECT SERVERPROPERTY('IsClustered'), SERVERPROPERTY('ComputerNamePhysicalNetBIOS'),
       SERVERPROPERTY('MachineName'), SERVERPROPERTY('InstanceName'), SERVERPROPERTY('ServerName')
;

WITH ClusterActiveNode AS (
    SELECT SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS NodeName, CAST('Active' AS VARCHAR(10)) AS Active
),
ClusterNodes AS (
    SELECT NodeName FROM sys.dm_os_cluster_nodes
)
SELECT b.nodename, ISNULL(active, 'Passive') AS NodeStatus
FROM ClusterNodes AS b
LEFT JOIN ClusterActiveNode AS a ON a.NodeName = b.nodename;

Machine Name: This is the name of the Windows computer where the server instance is running. If the instance is clustered, it returns the name of the virtual server for a SQL Server instance running on a Microsoft Cluster Service.
Instance Name: The name of the specific SQL Server instance the user is connected to.
Server Name: This provides both the Windows server and instance information related to a specified SQL Server instance.

The following query displays the location of the SQL Server failover cluster diagnostic log (SQL Server Error Log). This information is valuable for troubleshooting and also reveals the locations of other error and diagnostic logs.

SELECT is_enabled, [path], max_size, max_files
FROM sys.dm_os_server_diagnostics_log_configurations WITH (NOLOCK) OPTION (RECOMPILE);

To find information about your storage, you can use the query below:

/* Using In-Built Function to Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM fn_virtualservernodes();
GO
EXEC XP_FIXEDDRIVES;

/* Using DMV to Identify Nodes of SQL Server Failover Cluster */
SELECT * FROM sys.dm_os_cluster_nodes;
GO
SELECT * FROM fn_servershareddrives();

/* Using DMV Function to Identify Shared Drives Used by SQL Server Failover Cluster Instance */
SELECT * FROM sys.dm_io_cluster_shared_drives;

EXEC master.dbo.xp_readerrorlog 1, 1, N'NETBIOS', NULL, NULL, NULL, N'desc';

Sql server Database list with Size

 

How to List Databases with Their Sizes in SQL Server

If you're managing a SQL Server instance and want to check the size of each database—including both data and log files—you can use the following query. This script retrieves all user databases, calculates the size of their data and log files in megabytes, and displays the results in descending order by data file size.

SQL Query

SELECT
    DB.name,
    SUM(CASE WHEN [type] = 0 THEN MF.size * 8 / 1024 ELSE 0 END) AS DataFileSizeMB,
    SUM(CASE WHEN [type] = 1 THEN MF.size * 8 / 1024 ELSE 0 END) AS LogFileSizeMB
FROM    sys.master_files MF
JOIN sys.databases DB ON DB.database_id = MF.database_id
WHERE DB.source_database_id IS NULL -- Exclude database snapshots
GROUP BY DB.name
ORDER BY DataFileSizeMB DESC;

Explanation

• sys.master_files contains information about all database files, including data and log files.

• The type column distinguishes file types: 0 for data files and 1 for log files.

• The size is reported in 8KB pages, so multiplying by 8 and dividing by 1024 converts it to megabytes (MB).

• Snapshots are excluded using the source_database_id IS NULL condition.

This query is useful for DBAs who want to monitor and manage database storage more effectively.

How to add DB2 as a linked server in SQL

 How to add DB2 as a linked server  in SQL

In this post, I will demonstrate how to add a new linked server in SQL Server.

-- Creating the Linked Server: [AS400DB]
EXEC master.dbo.sp_addlinkedserver 
    @server = N'AS400DB',
    @srvproduct = N'AS400',
    @provider = N'IBMDASQL',
    @datasrc = N'10.0.1.1';

-- Setting up the linked server login
EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname = N'AS400DB',
    @useself = N'False',
    @locallogin = NULL,
    @rmtuser = N'MyUser',
    @rmtpassword = '########';
GO

-- Configuring server options
EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'collation compatible',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'data access',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'dist',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'pub',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'rpc',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'rpc out',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'sub',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'connect timeout',
    @optvalue = N'0';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'collation name',
    @optvalue = NULL;
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'lazy schema validation',
    @optvalue = N'false';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'query timeout',
    @optvalue = N'0';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'use remote collation',
    @optvalue = N'true';
GO

EXEC master.dbo.sp_serveroption 
    @server = N'AS400DB',
    @optname = N'remote proc transaction promotion',
    @optvalue = N'true';
GO

This script will successfully configure the linked server, allowing you to connect to an AS400 database from SQL Server. Ensure that all server names, IP addresses, and credentials are accurate before executing.

Restore database in SQL Server TSQL Statement

In this post, I will demonstrate how to retrieve backup file details and relevant information using SQL queries. The query below fetches the latest 100 backup records ordered by date.

SELECT TOP 100
    DatabaseName = bs.database_name,
    BackupStartDate = bs.backup_start_date,
    CompressedBackupSize = bs.compressed_backup_size,
    ExpirationDate = bs.expiration_date,
    BackupSetName = bs.name,
    RecoveryModel = bs.recovery_model,
    ServerName = bs.server_name,
    CASE bs.[type]
        WHEN 'D' THEN 'Full'
        WHEN 'I' THEN 'Differential'
        WHEN 'L' THEN 'Transaction Log'
        WHEN 'F' THEN 'File or filegroup'
        WHEN 'G' THEN 'Differential file'
        WHEN 'P' THEN 'Partial'
        WHEN 'Q' THEN 'Differential partial'
        ELSE bs.[type]
    END AS BackupType,
    LogicalDeviceName = bmf.logical_device_name,
    PhysicalDeviceName = bmf.physical_device_name,
    'RESTORE LABELONLY FROM DISK = ''' + bmf.physical_device_name + ''' WITH CHECKSUM ' AS ChkSQL,
    'RESTORE ' + CASE bs.[type]
        WHEN 'D' THEN 'DATABASE'
        WHEN 'I' THEN 'DATABASE'
        WHEN 'L' THEN 'LOG'
    END + ' ' + bs.database_name + ' FROM DISK = ''' + bmf.physical_device_name + ''' WITH NORECOVERY' AS 'RestoreSQL'
FROM msdb.dbo.backupset bs
INNER JOIN msdb.dbo.backupmediafamily bmf ON bs.media_set_id = bmf.media_set_id
WHERE bs.database_name = '<Database Name>'
ORDER BY bs.backup_start_date DESC;

Make sure to replace `` with the actual database name, or simply omit this part from the query.

The following query extracts backup media information based on the backup file name retrieved from the previous query:

RESTORE LABELONLY FROM DISK = 'I:\\MSSQL\\Backup\\dbname\\dbname_backup_2020_12_04_090003_0380615.trn' WITH CHECKSUM

This query will retrieve file details from the backup file name provided:

RESTORE FILELISTONLY FROM DISK = 'I:\\MSSQL\\Backup\\DBNAME\\DBNAME_backup_2020_12_03_200002_1858707.bak'

The following query provides the full backup header information for all backups stored on a specific device in SQL Server:

RESTORE HEADERONLY FROM DISK = N'I:\\MSSQL\\Backup\\AdventureWorks-FullBackup.bak';

Here’s a query that creates a temporary table and stores file information from the backup file. This can be useful if you want to restore the database to a different file path.

CREATE TABLE #RestoreFileList (
    RowID INT IDENTITY(1, 1),
    LogicalName VARCHAR(250),
    PhysicalName VARCHAR(500),
    [Type] VARCHAR(2),
    FileGroupName VARCHAR(100),
    Size VARCHAR(100),
    MaxSize VARCHAR(100),
    FileId INT,
    CreateLSN VARCHAR(250),
    DropLSN VARCHAR(250),
    UniqueId VARCHAR(250),
    ReadOnlyLSN VARCHAR(250),
    ReadWriteLSN VARCHAR(250),
    BackupSizeInBytes VARCHAR(250),
    SourceBlockSize INT,
    FileGroupId INT,
    FileGroupGUID VARCHAR(250),
    DifferentialBaseLSN VARCHAR(250),
    DifferentialBaseGUID VARCHAR(250),
    IsReadOnly VARCHAR(2),
    IsPresent VARCHAR(2),
    TDEThumbPrint VARCHAR(250)
);

INSERT INTO #RestoreFileList (
    LogicalName,
    PhysicalName,
    [Type],
    FileGroupName,
    Size,
    MaxSize,
    FileId,
    CreateLSN,
    DropLSN,
    UniqueId,
    ReadOnlyLSN,
    ReadWriteLSN,
    BackupSizeInBytes,
    SourceBlockSize,
    FileGroupId,
    FileGroupGUID,
    DifferentialBaseLSN,
    DifferentialBaseGUID,
    IsReadOnly,
    IsPresent,
    TDEThumbPrint
)
EXEC ('RESTORE FILELISTONLY FROM DISK = ''I:\\MSSQL\\Backup\\DBNAME\\DNNAME_backup_2020_12_03_200002_1858707.bak''');

The following query generates a restore script with the specified path. You should replace `@newPath` and the backup filename as per your requirements. Depending on your SQL Server version, you might need to adjust the columns accordingly.

DECLARE @dataFile VARCHAR(1000) = '', @newPath VARCHAR(100) = 'c:\\testpat\\';
SET @dataFile = 'RESTORE DATABASE [signphoto] FROM DISK = N''I:\\MSSQL\\signphoto.bak'' WITH FILE = 1, ';
SELECT @dataFile = @dataFile + 'MOVE' + '''' + LogicalName + '''' + ' TO '
 + '''' + @newPath + '\\' + RIGHT(PhysicalName, CHARINDEX('\\', REVERSE(PhysicalName)) - 1) + ''',' + CHAR(13)
FROM #RestoreFileList
ORDER BY FileId;
PRINT @dataFile + ', NOUNLOAD, STATS = 5';

The generated restore script will look like this:

RESTORE DATABASE [signphoto] FROM DISK = N'I:\\MSSQL\\signphoto.bak' WITH FILE = 1, MOVE 'signphoto' TO 'c:\\testpat\\signphoto.mdf',
MOVE 'signphoto_log' TO 'c:\\testpat\\signphoto_log.ldf', NOUNLOAD, STATS = 5;

Additional References:

Restore HeaderOnly Documentation

Restore FileListOnly Documentation

Related blog posts:

Backup History Using T-SQL

Backup History

How to Add Linked server with a different name

In this post, I will walk you through the process of creating a linked server with a custom name.

EXEC master.dbo.sp_addlinkedserver @server = N'STUDENTINFO'
, @srvproduct = N'SQL Server'


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'STUDENTINFO'
, @useself = N'False'
, @locallogin = NULL
, @rmtuser = N'ReadLogin'
, @rmtpassword = 'hJa1b3v&*7NB5q98j76('
GO

For additional reference, check the following articles:

DB2 as a Linked Server

Oracle as a Linked Server

How to Find Compressed Objects and Their Properties in SQL Server

📦 How to Find Compressed Objects and Their Properties in SQL Server

SQL Server offers data compression to help reduce storage requirements and improve I/O performance. Over time, it's important to review which objects in your database are compressed, what type of compression is used, and whether further optimization or decompression might be needed.

The following query helps you identify compressed objects in your database along with key details, such as the compression type and a ready-to-run command to remove compression if needed.

🧾 SQL Query to List Compressed Database Objects

SELECT 
    SCHEMA_NAME(o.schema_id) AS [Schema Name],
    OBJECT_NAME(o.object_id) AS [Object Name],
    'ALTER INDEX ALL ON ' + SCHEMA_NAME(o.schema_id) + '.' + OBJECT_NAME(o.object_id) + 
    ' REBUILD WITH (DATA_COMPRESSION = None);' AS [Decompress CMD],
    (
        SELECT OBJECTPROPERTY(OBJECT_ID(OBJECT_NAME(o.object_id)), 'TableHasVarDecimalStorageFormat')
    ) AS [Table Has VarDecimal Storage Format],
    p.[rows],
    p.[data_compression_desc] AS [Compression Type],
    p.[index_id] AS [Index ID on Table]
FROM 
    sys.partitions p
INNER JOIN 
    sys.objects o ON p.object_id = o.object_id
WHERE 
    p.data_compression > 0
    AND SCHEMA_NAME(o.schema_id) <> 'sys'
ORDER BY 
    [Schema Name],
    [Object Name];

🧠 What This Query Does

• Retrieves a list of all compressed objects (tables or indexes) in the current database.
• Shows the schema and object names for clarity.
• Displays the compression type (ROW, PAGE, or COLUMNSTORE).
• Provides an ALTER INDEX command to decompress each object if needed.
• Checks whether a table uses the VarDecimal Storage Format, which helps save space for decimal values.

✅ Why This Is Useful

• Helps DBAs audit storage savings across the database.
• Assists in evaluating whether certain tables or indexes should be recompressed, left as-is, or decompressed.
• Supports performance tuning efforts when analyzing I/O or CPU trade-offs for compression.

---

💡 Tip: Regularly reviewing compressed objects is a good habit—especially in environments where performance and storage costs are closely monitored.

How to add oracle as a linked server in SQL server

In this post, I will guide you through the steps to add a new Oracle linked server in SQL Server.

/****** Linked Server Creation for [PAYROLL] ******/
EXEC master.dbo.sp_addlinkedserver @server = N'PAYROLL'
   ,@srvproduct = N'MSDAORA'
   ,@provider = N'MSDAORA'
   ,@datasrc = N'pay4win20'  -- pay4win20 is the service name, payroll is the server name, and MSDAORA is the Oracle driver name

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PAYROLL'
   ,@useself = N'False'
   ,@locallogin = NULL
   ,@rmtuser = N'system'
   ,@rmtpassword = '########'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'collation compatible'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'data access'
   ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'dist'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'pub'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'rpc'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'rpc out'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'sub'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'connect timeout'
   ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'collation name'
   ,@optvalue = NULL
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'lazy schema validation'
   ,@optvalue = N'false'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'query timeout'
   ,@optvalue = N'0'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'use remote collation'
   ,@optvalue = N'true'
GO

EXEC master.dbo.sp_serveroption @server = N'PAYROLL'
   ,@optname = N'remote proc transaction promotion'
   ,@optvalue = N'true'
GO

Additional References:

DB2 as a Linked Server

Linked Server with a Different Name

Get the most frequently executed queries for this database

Uncovering Your Most Frequent and Resource-Intensive SQL Queries

Ever wonder which queries are truly hammering your SQL Server? Or which ones are eating up the most resources? This handy T-SQL script helps you pinpoint the top 50 queries based on their execution count, along with crucial performance metrics like logical reads and worker time.

SQL Script to Analyze Query Performance

Here's the SQL query that will give you insights into your database's performance:

SELECT TOP (50) LEFT(t.[text], 50) AS [Short Query Text]
 ,qs.execution_count AS [Execution Count]
 ,qs.total_logical_reads AS [Total Logical Reads]
 ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
 ,qs.total_worker_time AS [Total Worker Time]
 ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time]
 ,qs.total_elapsed_time AS [Total Elapsed Time]
 ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,qs.creation_time AS [Creation Time]
--,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
WHERE t.dbid = DB_ID()
ORDER BY qs.execution_count DESC
OPTION (RECOMPILE);

Understanding the Metrics

This script leverages two powerful SQL Server Dynamic Management Views (DMVs):

• sys.dm_exec_query_stats: Provides aggregate performance statistics for cached query plans.
• sys.dm_exec_query_plan: Returns the showplan in XML format for the batch specified by the plan handle.

By analyzing the output, you can quickly identify queries that are frequently executed, have high logical reads (indicating significant I/O operations), or consume a lot of worker time (CPU usage). The "Has Missing Index" column is particularly useful for spotting potential performance bottlenecks that could be resolved with a new index.

Xml with null Column

How to Retrieve Null Values from an XML Column

XML Basics

Some key points to remember when working with XML:

• XML tags are case-sensitive; make sure the opening and closing tags match in case.
• Elements in XML must be correctly nested.
• Attribute values should always be enclosed in quotes.
• The attribute xsi:nil is used to represent a null value in XML.

Special Characters in XML:

<message>salary < 1000</message>
&lt; < less than
&gt; > greater than
&amp; & ampersand 
&apos; ' apostrophe
&quot; " quotation mark

Example SQL Query:

select name, object_id, principal_id from sys.tables for XML PATH, ELEMENTS XSINIL;

Sample XML Output:

object_id	principal_id	name
117575457	NULL	spt_fallback_db
133575514	NULL	spt_fallback_dev
149575571	NULL	spt_fallback_usg
1803153469	NULL	spt_monitor
2107154552	NULL	MSreplication_options

SQL to Parse XML with Null Values:

declare @xml xml
select @xml = N'<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_db</name>
  <object_id>117575457</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_dev</name>
  <object_id>133575514</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_fallback_usg</name>
  <object_id>149575571</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>spt_monitor</name>
  <object_id>1803153469</object_id>
  <principal_id xsi:nil="true" />
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <name>MSreplication_options</name>
  <object_id>2107154552</object_id>
  <principal_id xsi:nil="true" />
</row>'
; 
with xmlnamespaces('http://www.w3.org/2001/XMLSchema-instance' as xsi)
select  N.C.value('object_id[1]', 'int') as object_id,
        N.C.value('principal_id[1][not(@xsi:nil = "true")]', 'int') as principal_id,
        N.C.value('name[1]', 'varchar(100)') as name
from @xml.nodes('//row') N(C)

For Additional Information:

You can refer to the following article for more details: XML Type Using Query

How to find orphan database files

In this post, I will share a sample SQL query that helps identify database files that are not in use by SQL Server.

DECLARE @data_file_path VARCHAR(255)

SET @data_file_path = 'D:\\data\\MSSQL10_50.MSSQLSERVER\\MSSQL\\DATA\\'

-- Creating a temporary table to store file names from the directory.
IF object_id('tempdb..#folder') IS NOT NULL
    DROP TABLE #folder

CREATE TABLE #folder (
    cid INT identity(1, 1) PRIMARY KEY CLUSTERED,
    subdirectory VARCHAR(255),
    depth INT,
    isfile INT
)

-- Populating the temporary table with file names using xp_dirtree.
INSERT INTO #folder (
    [subdirectory],
    [depth],
    [isfile]
)
EXEC master..xp_dirtree @data_file_path, 1, 1

-- Comparing files found in the OS data file location to files linked to live databases.
-- WARNING: This does not account for detached data files. If you have detached data files, consult your DBA before cleaning up orphaned files.
SELECT 'path location' = @data_file_path,
       'orphaned data files' = subdirectory
FROM #folder
WHERE subdirectory LIKE '%df' -- Only compares .mdf, .ndf, and .ldf files
  AND subdirectory NOT IN (
        SELECT right(smf.physical_name, charindex('\\', reverse('\\' + smf.physical_name)) - 1)
        FROM sys.master_files smf
        JOIN sys.databases sd ON smf.database_id = sd.database_id
    )
ORDER BY subdirectory ASC

Escape XML special characters in SQL Server Query

In this post, I'll demonstrate how to handle special characters in SQL Server.

DECLARE @xml XML = '<zzz xmlns="http://himanshupatel.in"><aaa>aaa</aaa> <bbb>param1=xyz&amp;para2=dasdasdfdas&amp;param3</bbb></zzz>'

SELECT @xml [before], DATALENGTH(@xml) dl

;WITH XMLNAMESPACES (DEFAULT 'http://himanshupatel.in')
SELECT @xml.value('(zzz/bbb/text())[1]', 'VARCHAR(100)')

Here’s an image that illustrates the output when using special characters in the XML.

SELECT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc,
    (SELECT m.definition FOR XML PATH(''), type) AS Object_Definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

SELECT s.name AS Schema_Name, o.name AS Object_Name, o.type_desc,
    (SELECT m.definition FOR XML PATH('')) AS Object_Definition
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id

Here’s the output when you include the 'type' keyword in the query:

Now, here’s the result without the 'type' keyword:

select (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''), type )

select (select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''))

select stuff((select ', <' + name + '>' from sys.databases where database_id > 4 order by name for xml path(''), type).value('(./text())[1]', 'varchar(max)'), 1, 2, '') as namelist;

Here’s the output of the three queries above:

For further reference, check out the following resources:

Using XML Type in SQL Queries

XML with Null Columns

Difference between row_number(), rank() and dense_rank() window functions in SQL Server

Difference between row_number(), rank() and dense_rank() window functions 

All three functions operate according to the ORDER BY clause.

The ROW_NUMBER() function generates a unique ranking for each row, even when there are duplicate values.

The RANK() function works like ROW_NUMBER(), but it assigns the same rank to rows with identical values.

The DENSE_RANK() function is similar to RANK(), but it ensures that there are no gaps in the ranking between groups.

SELECT *, ROW_NUMBER() OVER(ORDER BY Col) AS [ROW_NUMBER],
       RANK() OVER(ORDER BY Col) AS [RANK],
       DENSE_RANK() OVER(ORDER BY Col) AS [DENSE_RANK]
FROM (VALUES('a'),('a'),('a'),('b'),('c'),('c'),('d'),('e')) AS Tab(Col)

Other References:

Table Value Constructor - Transact-SQL

SQL Server Functions

SELECT with VALUES

How to read the SQL Server query plan file using TSQL

 How to read the SQL Server query plan file using T-SQL

In this post, I'll explain how to read an SQL Server execution query plan from the SSMS query file.

DECLARE @plancontents xml, @xml XML
SET @plancontents = (SELECT * FROM OPENROWSET(BULK 'P:\\Users\\hpatterson\\Documents\\myplan.sqlplan', SINGLE_CLOB) x)
-- Remove unnecessary namespace
-- SET @plancontents = REPLACE(@plancontents, 'xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan"', '')
-- Store the cleaned content into the XML variable
SET @xml = CAST(@plancontents AS XML)

-- You can also get the query plan directly like this:
-- SELECT @xml = query_plan FROM sys.dm_exec_query_plan
SELECT @xml

-- Notice the removal of the xmlns=http://schemas.microsoft.com/sqlserver/2004/07/showplan attribute

-- Now, let's process all the execution plan nodes and extract relevant details
WITH xmlnamespaces (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    c.value('.[1]/@PhysicalOp', 'nvarchar(max)') AS PhysicalOp,
    c.value('.[1]/@LogicalOp', 'nvarchar(max)') AS LogicalOp,
    c.value('.[1]/@AvgRowSize', 'nvarchar(max)') AS AvgRowSize,
    c.value('.[1]/@TableCardinality', 'nvarchar(max)') AS TableCardinality,
    c.value('.[1]/@Parallel', 'nvarchar(max)') AS Parallel,
    c.value('.[1]/@EstimateRebinds', 'nvarchar(max)') AS EstimateRebinds,
    c.value('.[1]/@EstimateRewinds', 'nvarchar(max)') AS EstimateRewinds,
    c.value('.[1]/@NodeId', 'nvarchar(max)') AS NodeId,
    c.value('.[1]/@EstimatedTotalSubtreeCost', 'nvarchar(max)') AS EstimatedTotalSubtreeCost,
    c.value('.[1]/@EstimateRows', 'nvarchar(max)') AS EstimateRows,
    c.value('.[1]/@EstimateIO', 'nvarchar(max)') AS EstimateIO,
    c.value('.[1]/@EstimateCPU', 'nvarchar(max)') AS EstimateCPU,
    -- This will return the node XML for easier inspection
    c.query('.') AS ExecPlanNode        
FROM 
    -- Only returns nodes with the name RelOp, including those that are nested
    @xml.nodes('//child::RelOp') T(c)
ORDER BY EstimatedTotalSubtreeCost DESC

How to find a backup task by percentage complete

How to find a backup task by percentage complete

In this post, I will demonstrate how to track the completion of a backup task using a percentage indicator.

SELECT query = a.text, start_time, percent_complete,
    eta = dateadd(second, estimated_completion_time/1000, getdate())
FROM sys.dm_exec_requests r
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a
WHERE r.command LIKE 'BACKUP%'

For more related information, check out the following resources:

Find Databases Without Backup

Backup History Using T-SQL

Restore Database

SQL Server find database without backup from a given date

SQL Server finds a database without a backup from a given date

Below is a sample SQL query that retrieves the list of databases that have not had a backup since a specific date:

SELECT S.NAME AS database_name,
       'No Backups' AS [Backup Age (Hours)]
FROM master.dbo.sysdatabases S
LEFT JOIN msdb.dbo.backupset B
       ON S.name = B.database_name
       AND B.backup_start_date > '20201101'
WHERE B.database_name IS NULL 
      AND S.name <> 'tempdb'
ORDER BY B.database_name;

Additional Resources:

Backup History

Restore Database

Backup History Using TSQL

How to work with a certificate in SQL Server

How to work with a certificate in SQL Server

This SQL command adds a certificate to a SQL Server database.

To perform this action, you need to have the CREATE CERTIFICATE permission on the database. It's important to note that only Windows logins, SQL Server logins, and application roles can own certificates. Groups and other roles are not allowed to own certificates.

-- Create a self-signed certificate
CREATE CERTIFICATE selfsigned
   ENCRYPTION BY PASSWORD = 'selfsigned2439587y'  
   WITH SUBJECT = 'Testing self-signed Records',
   EXPIRY_DATE = '20251031';

-- Restoring the certificate involves using the same process as creating a new certificate.
create certificate MySalaryCert
from file = N'c:\\Encryption\\MyCerti.cer'
with private key
  ( file = N'c:\\Encryption\\MyCerti.pvk'
  , decryption by password = N'hiMaNshu#paTel#dBa'
   );

CREATE CERTIFICATE MySalaryCert  
    FROM EXECUTABLE FILE = 'c:\\MySalaryCert.dll';  
GO

CREATE ASSEMBLY MySalaryCert  
    FROM 'c:\\MySalaryCert.dll'   
    WITH PERMISSION_SET = SAFE;  
GO  
CREATE CERTIFICATE MySalaryCert FROM ASSEMBLY MySalaryCert;  
GO

-- The following query displays the details of the certificate, including the public and private key information:
SELECT
  name,
  certificate_id,
  pvt_key_encryption_type_desc,
  subject,
  expiry_date,
  start_date,
  thumbprint,
  pvt_key_last_backup_date
FROM sys.certificates;

For more information, refer to the following resources:

Microsoft Docs: CREATE CERTIFICATE (Transact-SQL)

MSSQLTips: How to Configure SSL Encryption in SQL Server

Implementing Dynamic Data Masking in SQL Server

Introduction to Dynamic Data Masking (DDM)

Dynamic Data Masking (DDM) is a technique that restricts access to sensitive data by masking it for users without the required privileges. This approach makes it easier to design and implement security measures in your applications, ensuring that sensitive information is protected while allowing authorized users to access necessary data.

Permissions Required for Dynamic Data Masking

To create a table with dynamic data masking, you don’t need special permissions beyond the standard CREATE TABLE and ALTER permissions for the schema. However, there are additional permissions needed for manipulating the data masks.

1. Modifying Masks: You need the ALTER ANY MASK permission, in addition to ALTER on the table.
2. Viewing Data: Users with SELECT permissions can see only the masked data. Assign UNMASK permission to allow viewing unmasked data.
3. Control Permissions: The CONTROL permission includes both ALTER ANY MASK and UNMASK.

Limitations and Restrictions of Data Masking

There are some column types that cannot be masked, including:

• Encrypted columns (Always Encrypted)
• FILESTREAM columns
• Sparse columns that are part of a column set
• Computed columns: If a computed column depends on a masked column, it will show masked data.
• Columns involved in a FULLTEXT index

Example: Creating a Table with a Mask

You can create a table with dynamic data masking as shown below:

CREATE TABLE MembershipMask  
(
  MemberID int IDENTITY PRIMARY KEY,  
  FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,  
  LastName varchar(100) NOT NULL,  
  Phone varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,  
  Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL  
);

Insert sample data:

INSERT MembershipMask (FirstName, LastName, Phone, Email) VALUES  
('Vipul', 'Bhatt', '456.123.4567', 'Vipul@Himanshupatel.in'),  
('Dev', 'Shah', '456.123.3333', 'Dev@Himanshupatel.in'),  
('Raj', 'Patel', '555.123.4569', 'raj@Himanshupatel.in');

Querying the table will display masked data:

SELECT * FROM MembershipMask;

Example output for a user with SELECT permission:

MemberID FirstName LastName Phone Email
1        VXXXXXXX  Bhatt     xxxx  VXXX@XXXX.com
2        DXXXXXXX  Shah      xxxx  DXXX@XXXX.com
3        RXXXXXXX  Patel     xxxx  RXXX@XXXX.com

Viewing the Data Masking Definition

Use the following query:

SELECT TBLS.name AS TableName, MC.NAME AS ColumnName, MC.is_masked AS IsMasked, MC.masking_function AS MaskFunction  
FROM sys.masked_columns AS MC  
JOIN sys.tables AS TBLS ON MC.object_id = TBLS.object_id  
WHERE is_masked = 1;

Dropping a Mask

To remove a mask from a column:

ALTER TABLE Membership  
ALTER COLUMN LastName DROP MASKED;

Adding or Editing a Mask on an Existing Column

ALTER TABLE Membership  
ALTER COLUMN LastName ADD MASKED WITH (FUNCTION = 'partial(2,"XXX",0)');

Granting and Revoking Mask Permissions

GRANT UNMASK TO TestUserins;
GO
REVOKE UNMASK FROM TestUserins;
GO

Implementing Partial Masking

To reveal only the last 4 digits of a national ID number:

ALTER TABLE EmpInfo  
ALTER COLUMN NatID NVARCHAR(15)  
MASKED WITH (FUNCTION = 'partial(0, "xxxxx", 4)') NOT NULL;  
-- e.g., 716374314 becomes xxxxx4314

Implementing Random Masking

To mask a monetary value within a range:

ALTER TABLE EmpInfo  
ALTER COLUMN SalesYTD MONEY  
MASKED WITH (FUNCTION = 'random(101, 999)') NOT NULL;

Further Reading

For more information, refer to the official documentation:

Microsoft Docs on Dynamic Data Masking

Database Email and system objects

Database Email and system objects

Database Mail allows your database applications to send emails to users. These emails can include query results and files from your network.

Database Mail operates with two configuration objects: these allow you to set up the necessary settings for sending emails from your database applications or the SQL Server Agent.

Database Mail Accounts

A Database Mail account stores the details used by Microsoft SQL Server to send emails through an SMTP server. Each account is linked to a specific email server.

Create a New Database Mail Account with SMTP Details

sysmail_add_account_sp  [ @account_name = ] 'account_name',  
    [ @email_address = ] 'email_address' ,  
    [ [ @display_name = ] 'display_name' , ]  
    [ [ @replyto_address = ] 'replyto_address' , ]  
    [ [ @description = ] 'description' , ]  
    [ @mailserver_name = ] 'server_name'  
    [ , [ @mailserver_type = ] 'server_type' ]  
    [ , [ @port = ] port_number ]  
    [ , [ @username = ] 'username' ]  
    [ , [ @password = ] 'password' ]  
    [ , [ @use_default_credentials = ] use_default_credentials ]  
    [ , [ @enable_ssl = ] enable_ssl ]  
    [ , [ @account_id = ] account_id OUTPUT ]

Database Mail Profiles

A Database Mail profile is a group of related Database Mail accounts. Applications that send mail via Database Mail should specify a profile rather than individual accounts.

Profiles also allow database administrators to manage email access. Users need to be a member of the DatabaseMailUserRole to send email via Database Mail.

Profiles can either be public or private.

Create a New Database Mail Profile

sysmail_add_profile_sp [ @profile_name = ] 'profile_name'  
    [ , [ @description = ] 'description' ]  
    [ , [ @profile_id = ] new_profile_id OUTPUT ]

Add an Account to a Database Mail Profile

After creating a Database Account with sysmail_add_account_sp and a Database Profile with sysmail_add_profile_sp, you can use sysmail_add_profileaccount_sp to associate them.

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp  
    @profile_name = 'AdventureWorks Administrator',  
    @account_name = 'Audit Account',  
    @sequence_number = 1 ;

Retrieve Database Mail Configuration

sysmail_help_configure_sp
sysmail_help_principalprofile_sp 
sysmail_help_profile_sp
sysmail_help_profileaccount_sp

Set Database Mail Configuration

The sysmail_configure_sp stored procedure modifies configuration settings for Database Mail at the SQL Server instance level.

Parameter Name	Value	Description
AccountRetryAttempts	1	Number of retry attempts for a mail server
AccountRetryDelay	60	Delay between retry attempts
DatabaseMailExeMinimumLifeTime	600	Minimum process lifetime (seconds)
DefaultAttachmentEncoding	MIME	Default attachment encoding type
LoggingLevel	2	Logging level for Database Mail (normal - 1, extended - 2, verbose - 3)
MaxFileSize	1000000	Maximum file size for attachments
ProhibitedExtensions	exe,dll,vbs,js	Extensions not allowed in email attachments

sysmail_configure_sp [ [ @parameter_name = ] 'parameter_name' ]  
    [ , [ @parameter_value = ] 'parameter_value' ]  
    [ , [ @description = ] 'description' ]

Send an Email Using Database Mail

This procedure sends an email with options for attaching files or query results.

sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]  
    [ , [ @recipients = ] 'recipient_email' ]  
    [ , [ @copy_recipients = ] 'cc_email' ]  
    [ , [ @blind_copy_recipients = ] 'bcc_email' ]  
    [ , [ @from_address = ] 'sender_email' ]  
    [ , [ @reply_to = ] 'reply_to_email' ]  
    [ , [ @subject = ] 'email_subject' ]  
    [ , [ @body = ] 'email_body' ]  
    [ , [ @body_format = ] 'html' ]  
    [ , [ @importance = ] 'high' ]  
    [ , [ @sensitivity = ] 'normal' ]  
    [ , [ @file_attachments = ] 'file_path' ]  
    [ , [ @query = ] 'sql_query' ]  
    [ , [ @execute_query_database = ] 'database_name' ]  
    [ , [ @attach_query_result_as_file = ] 1 ]  
    [ , [ @query_attachment_filename = ] 'file_name' ]  
    [ , [ @query_result_separator = ] ',' ]  
    [ , [ @exclude_query_output = ] 0 ]  
    [ , [ @append_query_error = ] 1 ]  
    [ , [ @query_no_truncate = ] 1 ]  
    [ , [ @query_result_no_padding = ] 1 ]  
    [ , [ @mailitem_id = ] mailitem_id ] [ OUTPUT ]

Enable Database Mail

sp_configure 'show advanced', 1; 
GO
RECONFIGURE;
GO

sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

Troubleshooting queries:

select * from msdb.dbo.sysmail_sentitems
select * from msdb.dbo.sysmail_unsentitems
select * from msdb.dbo.sysmail_faileditems
SELECT * FROM msdb.dbo.sysmail_event_log;

SELECT [sysmail_server].[account_id],
       [sysmail_account].[name] AS [Account Name],
       [servertype],
       [servername] AS [SMTP Server Address],
       [Port]
FROM [msdb].[dbo].[sysmail_server]
     INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
GO
SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs'
GO

SELECT * 
FROM msdb.dbo.sysmail_profileaccount pa 
INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id 
INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id

SMTP server details for co

How to find the missing foreign key indexes

Finding Missing Indexes on Foreign Key Relation Tables

In this article, I'm exploring how to identify missing indexes in tables with foreign key relationships.

SELECT DB_NAME() AS DBName
    , rc.Constraint_Name AS FK_Constraint
    , ccu.Table_Name AS FK_Table
    , ccu.Column_Name AS FK_Column
    , ccu2.Table_Name AS ParentTable
    , ccu2.Column_Name AS ParentColumn
    , I.Name AS IndexName
    , CASE WHEN I.Name IS NULL
        THEN 'IF NOT EXISTS (SELECT * FROM sys.indexes
                    WHERE object_id = OBJECT_ID(N''' + RC.Constraint_Schema + '.' + ccu.Table_Name + ''') 
            AND name = N''IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ''') ' + '
            CREATE NONCLUSTERED INDEX IX_' + ccu.Table_Name + '_' + ccu.Column_Name + ' 
            ON ' + rc.Constraint_Schema + '.' + ccu.Table_Name + '( ' + ccu.Column_Name + ' ASC ) 
            WITH (PAD_INDEX = OFF
            ,STATISTICS_NORECOMPUTE = OFF
            ,SORT_IN_TEMPDB = ON
            ,IGNORE_DUP_KEY = OFF
            ,DROP_EXISTING = OFF, ONLINE = ON);'
        ELSE ''
    END AS SQL
FROM information_schema.referential_constraints RC
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON rc.CONSTRAINT_NAME = ccu.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu2 ON rc.UNIQUE_CONSTRAINT_NAME = ccu2.CONSTRAINT_NAME
LEFT JOIN sys.columns c ON ccu.Column_Name = C.name
    AND ccu.Table_Name = OBJECT_NAME(C.OBJECT_ID)
LEFT JOIN sys.index_columns ic ON C.OBJECT_ID = IC.OBJECT_ID
    AND c.column_id = ic.column_id
    AND index_column_id = 1
-- index found has the foreign key
-- as the first column 
LEFT JOIN sys.indexes i ON IC.OBJECT_ID = i.OBJECT_ID
    AND ic.index_Id = i.index_Id
WHERE I.name IS NULL
ORDER BY FK_table
    ,ParentTable
    ,ParentColumn;

Other References:

• Missing Indexes – Himanshu Patel
• Finding SQL Server Object Dependencies
• SQL Without Clustered Index
• Foreign Key Scripts

SQL Server Functions

Retrieve ASCII Code Value

ASCII ('A') = 65 
ASCII ('BEE') = 66 

Convert Integer ASCII Code to Character

CHAR (65) = 'A'
CHAR (1000) = NULL

Search for a Character

CHARINDEX ('A', 'B', [, S])
CHARINDEX ('Z', 'HELLO') = 0
CHARINDEX ('H', 'HELLO') = 1
CHARINDEX ('OR', 'WORLD') = 2
CHARINDEX ('L', 'HELLO', 4) = 4

Replace a Character

REPLACE ('A', 'B', 'C')
REPLACE('HELLOW',NULL,'')=NULL
REPLACE('HELLOW','','_')='HELLOW'
REPLACE('HELLOW','ELLOW','I')='HI'
REPLACE('HELLOW','L',1) = 'HE11OW'

PATINDEX ('%pattern%' , ‘A’)

PATINDEX('H','HELLO') = 0
PATINDEX('H%','HELLO') = 1
PATINDEX('%L_%','HELLO') = 3
PATINDEX('%L_','HELLO') = 4
PATINDEX ('Z','HELLO') = 0
PATINDEX('%A[0-9]%','AA1A') = 2
PATINDEX('%L[^L]%','HELLO') = 4

Using STUFF to Replace Substring

STUFF('HELLOW',2,5,'I') = 'HI'
STUFF('HELLOW',2,15,'I') = 'HI '  
STUFF('HELLOW',20,1,'I') = NULL
STUFF('HELLOW',0,1,'I') = NULL
STUFF('HELLOW',1,0,'I') = 'IHELLOW'
STUFF('HELLOW',2,5,'I123456')  = 'HI123456'
STUFF('HELLOW ',2,2,'I') = 'HILOW'

Soundex Function

-- Returns a four-character SOUNDEX code for string similarity
SOUNDEX ('Smith') = 'S530'
SOUNDEX ('Smythe') = 'S530'

DIFFERENCE ('A', 'B') of Soundex

-- Calculates the difference between two soundex codes
DIFFERENCE('GREEN','GREENE') = 4

LEN Function - Length of a String

LEN ('HELLOW WORLD') = 11
LEN ('HELLOW ') = 6
LEN (12) = 2
LEN ('') = 0

Extract a Substring

-- Returns L characters starting at position S in string A
SUBSTRING('HELLOW',2,1) = 'E'
SUBSTRING('HELLOW',5,10) = 'OW'
SUBSTRING('HELLOW',10,1) = ''
SUBSTRING('HELLOW',0,1) = ''
SUBSTRING('HELLOW',1,0) = ''

Extract Right/Left Characters

RIGHT ('', 1) = ''
LEFT ('HI', 0) = ''
RIGHT ('HI', 3) = 'HI'
RIGHT ('HELLOW WORLD',5) = 'WORLD'
LEFT ('HELLOW WORLD', 6) = 'HELLOW'

Insert Spaces

SPACE(5) = '     '

REPLICATE Function

REPLICATE ('*', 4) = '****'
REPLICATE ('-', 0) = ''
REPLICATE ('^', NULL) = NULL

Convert Number to String

STR (2.234) = ' 2'
STR (2.234, 4) = '   2'
STR (2.234, 4, 2) = '2.23'
STR (2.234, 6, 2) = '  2.23'
STR (123456.789,4,2) = '****'
STR(123456.789,14,2) = '      123456.79'

CONCAT Function

CONCAT('HELLO')=ERROR
CONCAT(NULL, NULL)= ''
CONCAT('Hi',' ','Patel')='Hi Patel'
CONCAT(12,NULL,34) = '1234'
CONCAT(2014,12,31) = '20141231'

Case Change Functions

LOWER ('SQL') = 'sql'
UPPER ('hi') = 'HI'

Trim Whitespace from Strings

LTRIM (' HI ') = 'HI '
LTRIM (' ') = ''
RTRIM(' HI ') = ' HI'

QUOTENAME Function

QUOTENAME('H NAME') = [TB NAME]
QUOTENAME('H NAME', '] ') =[H NAME]
QUOTENAME('H NAME', '\"') = "H NAME"
QUOTENAME('abc[]dff') = [abc[]]dff]
QUOTENAME('TB NAME', '''')='TB NAME'

REVERSE Function

REVERSE('HELLOW') = 'WOLLEH' REVERSE(12) = 21

Track data changes using CDC in SQL Server Enterprise edition

SQL Server offers two features that monitor changes in a database: Change Data Capture and Change Tracking. These features help applications identify Data Manipulation Language (DML) changes such as insertions, updates, and deletions made to user tables in a database.

Change Data Capture Configuration Settings

? maxtrans* -- Specifies the maximum number of transactions to process per scan cycle.
? maxscans* -- The maximum number of scan cycles to be executed to extract all rows from the log.
? continuous* -- A flag indicating whether the capture job should run continuously (1) or in one-time mode (0). For further details, refer to sys.sp_cdc_add_job (Transact-SQL).*
? pollinginterval* -- Defines the interval in seconds between log scan cycles.
? retention** -- Specifies how long change rows will be stored in the change tables.
? threshold -- The maximum number of deletions allowed in a single cleanup statement.

Enabling Change Data Capture for a Database

USE MyDB  
GO  
EXEC sys.sp_cdc_enable_db  
GO

Disabling Change Data Capture for a Database

USE MyDB  
GO  
EXEC sys.sp_cdc_disable_db  
GO

Enabling Change Data Capture for a Table

  
EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name = N'MyTable',  
@role_name = N'MyRole',  
@filegroup_name = N'MyDB_CT',  
@supports_net_changes = 1  
GO

Viewing CDC Job Configurations

EXEC sys.sp_cdc_help_jobs

Modifying CDC Job Settings

EXECUTE sys.sp_cdc_change_job  
@job_type = 'cleanup',  
@retention = 86400; -- 60 days

Getting Capture_Instance Name

EXEC sys.sp_cdc_help_change_data_capture

Example 1: Reading Changed Columns

USE AdventureWorks2014

DECLARE @from_lsn binary (10), @to_lsn binary (10)

SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Shift')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Shift(@from_lsn, @to_lsn, 'all')
ORDER BY __$seqval

Example 2: Reading Changed Columns

USE AdventureWorks2014

DECLARE @from_lsn binary (10), @to_lsn binary (10)

SET @from_lsn = sys.fn_cdc_get_min_lsn('HumanResources_Shift')
SET @to_lsn = sys.fn_cdc_get_max_lsn()

SELECT * FROM cdc.fn_cdc_get_all_changes_HumanResources_Shift(@from_lsn, @to_lsn, 'all')
ORDER BY __$seqval

Check for changes:

SELECT *  
FROM HumanResources.Shift

SELECT *  
FROM cdc.HumanResources_Shift_CT

Checking If CDC Is Already Enabled for a Database

SELECT [name], database_id, is_cdc_enabled  
FROM sys.databases

Checking If CDC Is Already Enabled for a Table

SELECT [name], is_tracked_by_cdc  
FROM sys.tables

CDC System Tables

? cdc.captured_columns - Lists the columns captured.
? cdc.change_tables - Displays all tables enabled for change capture.
? cdc.ddl_history - Records all DDL changes since CDC was enabled.
? cdc.index_columns - Shows the indexes associated with change tables.
? cdc.lsn_time_mapping - Maps LSN numbers.

https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/track-data-changes-sql-server?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-functions/cdc-fn-cdc-get-all-changes-capture-instance-transact-sql?view=sql-server-ver15

How to work with Temporal Tables in SQL Server

How to work with Temporal Tables in SQL Server

SQL Server 2016 introduced the support for temporal tables (also referred to as system-versioned temporal tables). This feature enables you to track historical data by capturing not only the current data but also the state of data at any given point in time. Temporal tables are a significant addition to databases, based on the ANSI SQL 2011 standard.

SELECT name, object_id, temporal_type, temporal_type_desc FROM sys.tables;

temporal_type	temporal_type_desc
0	NON_TEMPORAL_TABLE
1	HISTORY_TABLE
2	SYSTEM_VERSIONED_TEMPORAL_TABLE

SELECT OBJECT_NAME(object_id) AS table_name, * 
FROM sys.periods
table_name name period_type period_type_desc object_id start_column_id end_column_id
Employee SYSTEM_TIME 1 SYSTEM_TIME_PERIOD 581577110 7 8

SELECT name AS 'Column_Name', generated_always_type, generated_always_type_desc 
FROM sys.columns

List all temporal tables along with their history tables.

SELECT schema_name(t.schema_id) AS temporal_table_schema, 
       t.name AS temporal_table_name,
       schema_name(h.schema_id) AS history_table_schema, 
       h.name AS history_table_name, 
       CASE 
         WHEN t.history_retention_period = -1 
         THEN 'INFINITE' 
         ELSE CAST(t.history_retention_period AS VARCHAR) + ' ' + t.history_retention_period_unit_desc + 'S' 
       END AS retention_period 
FROM sys.tables t 
LEFT OUTER JOIN sys.tables h ON t.history_table_id = h.object_id 
WHERE t.temporal_type = 2 
ORDER BY temporal_table_schema, temporal_table_name

Creating a table with a history table.

CREATE TABLE dbo.Employee 
( 
  [EmployeeID] int NOT NULL PRIMARY KEY CLUSTERED, 
  [Name] nvarchar(100) NOT NULL, 
  [Position] varchar(100) NOT NULL, 
  [Department] varchar(100) NOT NULL, 
  [Address] nvarchar(1024) NOT NULL, 
  [AnnualSalary] decimal(10,2) NOT NULL, 
  [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START, 
  [ValidTo] datetime2 GENERATED ALWAYS AS ROW END, 
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo) 
) 
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));

The script above will create two tables: Employee and EmployeeHistory.

Inserting and updating data in the tables above.

INSERT INTO Employee (EmployeeID, Name, Position, Department, Address, AnnualSalary) 
SELECT 1, 'himanshu', 'dba', 'IT', 'BC', 4000;
GO
INSERT INTO Employee (EmployeeID, Name, Position, Department, Address, AnnualSalary) 
SELECT 100, 'himanshu', 'dba', 'IT', 'BC', 4000;
GO
UPDATE Employee SET AnnualSalary = 5000 WHERE EmployeeID = 1;
GO
UPDATE Employee SET AnnualSalary = 4500 WHERE EmployeeID = 100;
GO
SELECT * FROM Employee;
SELECT * FROM EmployeeHistory;
GO
SELECT * FROM Employee 
FOR SYSTEM_TIME 
BETWEEN '2014-01-01 00:00:00.0000000' AND '2020-11-01 00:00:00.0000000';

Here is the result of the queries above:

How to work with xml type using Query

Working with OPENXML in SQL Server

OPENXML is a Transact-SQL keyword that allows you to treat XML data stored in memory as if it were a relational table or view. This functionality is especially helpful when importing or transforming XML content into tabular format.

To use OPENXML, the sp_xml_preparedocument procedure is first called to parse the XML and return a handle to the document, which can then be referenced in SQL queries.

Example: Populating Tables Using OPENXML

-- Create necessary tables
CREATE TABLE Customers (
    CustomerID varchar(20) PRIMARY KEY,
    ContactName varchar(20),
    CompanyName varchar(20)
);

CREATE TABLE Orders (
    CustomerID varchar(20),
    OrderDate datetime
);

-- Declare XML and handle
DECLARE @docHandle int;
DECLARE @xmlDocument nvarchar(max);

-- Set XML data
SET @xmlDocument = N'
<ROOT>
  <Customers CustomerID="XYZAA" ContactName="Joe" CompanyName="Company1">
    <Orders CustomerID="XYZAA" OrderDate="2000-08-25T00:00:00" />
    <Orders CustomerID="XYZAA" OrderDate="2000-10-03T00:00:00" />
  </Customers>
  <Customers CustomerID="XYZBB" ContactName="Steve" CompanyName="Company2">
    No Orders yet!
  </Customers>
</ROOT>';

-- Prepare the XML document
EXEC sp_xml_preparedocument @docHandle OUTPUT, @xmlDocument;

-- Insert customers
INSERT INTO Customers
SELECT * FROM OPENXML(@docHandle, '/ROOT/Customers') WITH Customers;

-- Insert orders
INSERT INTO Orders
SELECT * FROM OPENXML(@docHandle, '//Orders') WITH Orders;

-- Query with OPENXML
SELECT * FROM OPENXML(@docHandle, '/ROOT/Customers/Orders')
  WITH (CustomerID nchar(5) '../@CustomerID', OrderDate datetime);

-- Clean up the XML handle
EXEC sp_xml_removedocument @docHandle;

Example: Using nodes() Method

SELECT 
    nref.value('(first-name/text())[1]', 'nvarchar(50)') AS FirstName,
    nref.value('(last-name/text())[1]', 'nvarchar(50)') AS LastName
FROM T
CROSS APPLY xCol.nodes('//author') AS R(nref)
WHERE nref.exist('first-name[. != "David"]') = 1;

Looping Through XML Using OPENXML()

DECLARE name_cursor CURSOR FOR 
    SELECT xCol FROM T;

OPEN name_cursor;

DECLARE @xmlVal XML;
DECLARE @idoc int;

FETCH NEXT FROM name_cursor INTO @xmlVal;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sp_xml_preparedocument @idoc OUTPUT, @xmlVal;

    SELECT * 
    FROM OPENXML(@idoc, '//author') 
    WITH (
        FirstName varchar(50) 'first-name',
        LastName varchar(50) 'last-name'
    ) R
    WHERE R.FirstName != 'David';

    EXEC sp_xml_removedocument @idoc;

    FETCH NEXT FROM name_cursor INTO @xmlVal;
END

CLOSE name_cursor;
DEALLOCATE name_cursor;

Example XML Structure

<Customers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
           xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
   <Customer>
       <ItemId>1001</ItemId>
       <Value>Mr Patel</Value>
   </Customer>
   <Customer>
      <ItemId>2002</ItemId>
      <Value>Mr Bhatt</Value>
   </Customer>
</Customers>

Extracting Data from XML

SELECT
    Cst.value('(ItemId)[1]', 'int') AS ItemID,
    Cst.value('(Value)[1]', 'Varchar(50)') AS CustomerName
FROM dbo.Sales.CustomerList.nodes('/Customers/Customer') AS A(Cst);

Using CROSS APPLY with XML

CREATE TABLE Sales2020 (ID int, CustomerList xml);

INSERT INTO Sales2020
SELECT 1, '
    <Customers xmlns:xsd="http://www.w3.org/2001/XMLSchema"
               xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
       <Customer>
           <ItemId>1001</ItemId>
           <Value>Mr Patel</Value>
       </Customer>
       <Customer>
          <ItemId>2</ItemId>
          <Value>Mr Bhatt</Value>
       </Customer>
    </Customers>';

SELECT
   N.C.value('ItemId[1]', 'int') AS ItemId,
   N.C.value('Value[1]', 'varchar(100)') AS Value
FROM Sales2020
CROSS APPLY CustomerList.nodes('//Customer') AS N(C);

References

• XML with Null Column
• Tables with XML Columns
• OPENXML in Microsoft Docs

How to write PowerShell script using TSQL

Executing PowerShell Scripts via T-SQL in SQL Server

In scenarios where direct access to the server is unavailable, SQL Server allows the execution of PowerShell scripts through T-SQL by utilizing the xp_cmdshell extended stored procedure. This method enables administrators to perform system-level tasks without leaving the SQL Server environment.

Steps to Execute PowerShell Scripts:

1. Enable xp_cmdshell:
   First, enable the xp_cmdshell feature to allow the execution of operating system commands:

   sp_configure 'xp_cmdshell', 1;
   RECONFIGURE;

2. Run PowerShell Commands:
   Execute PowerShell commands using xp_cmdshell. For example, to list all running processes:

   xp_cmdshell 'powershell.exe -command Get-Process';

   To check disk space:

   xp_cmdshell 'PowerShell.exe -command "get-diskspace ."';

   For performance counters:

   xp_cmdshell 'PowerShell.exe -noprofile -command "Get-counter -counter ''\LogicalDisk(*)\% Free Space'' | select -expand countersamples"';

Security Considerations:

• Permissions: Ensure that only trusted users have access to xp_cmdshell due to its potential security risks.
• Execution Policy: PowerShell's execution policy might restrict script execution. Adjust it as necessary:

  Set-ExecutionPolicy RemoteSigned;

Use Cases:

This approach is particularly useful for automating administrative tasks, monitoring system performance, and gathering system information directly from within SQL Server.

How to Reset Identity Value in SQL Server

In SQL Server, identity columns are used to automatically generate unique numeric values, commonly for primary key columns. However, situations may arise, such as after deleting records or resetting test data, where you need to reset the identity value so that new records start with a specific number again.

If you've ever deleted all the rows from a table but noticed that the identity column keeps incrementing from the last used value, this post will show you how to reset it using the DBCC CHECKIDENT command.

👉 Original post: How to Reset Identity Value

What is DBCC CHECKIDENT?

SQL Server provides the A DBCC CHECKIDENT command to manually reseed (reset) the identity value of a table. This is particularly useful after large deletions or when reloading data during development or ETL processes.

Syntax:

DBCC CHECKIDENT ('TableName', RESEED, NewSeedValue);

• 'TableName': Your table name.
• RESEED: Instructs SQL Server to change the identity value.
• NewSeedValue: The number you want the next identity value to start from.

Example Scenario

Suppose you have a table called Employee With an identity column ID.

To reset the identity to start from 1 after deleting all records:

DELETE FROM Employee;
DBCC CHECKIDENT ('Employee', RESEED, 0);

⚠️ Note: If you reseed to 0, the next row inserted will receive ID = 1 (since identity starts from the seed + 1).

How to Check the Current Identity Value

To see the current identity seed without making changes:

DBCC CHECKIDENT ('Employee', NORESEED);

This will return the current seed and the next value to be used.

Important Considerations

• TRUNCATE vs DELETE: Using TRUNCATE TABLE resets the identity automatically, but DELETE does not.
• Existing Records: Be careful when reseeding if the table still contains rows—setting the seed too low can cause primary key violations.
• Permissions: Running DBCC CHECKIDENT may require ALTER permissions on the table.

When Should You Reset Identity Values?

• After deleting test or demo data in development environments.
• During staging or ETL operations, when reloading data.
• When maintaining a consistent identity sequence is necessary for reports or integrations.

Resetting identity values is a simple but powerful feature in SQL Server. With DBCC CHECKIDENT, you can take control over how identity values are managed in your tables.

For more details, you can refer to the following links:

Reassign the Identity of All Tables

The table has Identity Columns

Get top total worker time queries

 Get top total worker time queries

Ever wondered which SQL queries are hogging resources on your database? This handy SQL script helps you identify the top 50 culprits based on their total worker time. It provides a quick overview of query performance, including average execution times, logical reads, and even flags queries that might benefit from a missing index!

SELECT TOP (50) DB_NAME(t.[dbid]) AS [Database Name]
 ,REPLACE(REPLACE(LEFT(t.[text], 255), CHAR(10), ''), CHAR(13), '') AS [Short Query Text]
 ,qs.total_worker_time AS [Total Worker Time]
 ,qs.min_worker_time AS [Min Worker Time]
 ,qs.total_worker_time / qs.execution_count AS [Avg Worker Time]
 ,qs.max_worker_time AS [Max Worker Time]
 ,qs.min_elapsed_time AS [Min Elapsed Time]
 ,qs.total_elapsed_time / qs.execution_count AS [Avg Elapsed Time]
 ,qs.max_elapsed_time AS [Max Elapsed Time]
 ,qs.min_logical_reads AS [Min Logical Reads]
 ,qs.total_logical_reads / qs.execution_count AS [Avg Logical Reads]
 ,qs.max_logical_reads AS [Max Logical Reads]
 ,qs.execution_count AS [Execution Count]
 ,CASE WHEN CONVERT(NVARCHAR(max), qp.query_plan) COLLATE Latin1_General_BIN2 LIKE N'%<MissingIndexes>%' THEN 1 ELSE 0 END AS [Has Missing Index]
 ,qs.creation_time AS [Creation Time]
--,t.[text] AS [Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t
CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
ORDER BY qs.total_worker_time DESC
OPTION (RECOMPILE);

This script leverages two powerful SQL Server Dynamic Management Views (DMVs) for its insights:

• sys.dm_exec_query_stats: Provides aggregate performance statistics for cached query plans.
• sys.dm_exec_query_plan: Returns the showplan in XML format for the batch specified by the plan handle.

Give this script a try and see what insights you can uncover about your database's performance!

Tables without a Clustered Index (Heap)

How to List SQL Server Tables Without a Primary Key

If you're working with SQL Server databases, it's essential to know which tables lack primary keys. Having a primary key on your tables ensures that each record is uniquely identified, which helps in data integrity and query optimization. However, some tables may not have a primary key, and you might need to identify them for maintenance or optimization purposes.

Query to List Tables Without a Primary Key

The following SQL query can be used to list all the tables in a SQL Server database that do not have a primary key:

SELECT [Table] = s.[Name] + N'.' + t.[Name]
FROM [sys].tables t
INNER JOIN [sys].schemas s
    ON t.[schema_id] = s.[schema_id]
WHERE NOT EXISTS (
    SELECT 1 
    FROM [sys].indexes i
    WHERE i.[object_id] = t.[object_id]
    AND i.index_id = 1
);

Explanation of the Query

• s.Name and t.Name: These refer to the schema and table names, respectively.
• sys.tables: This system view holds information about the tables in the database.
• sys.schemas: This system view contains information about the schemas.
• sys.indexes: This view provides details about the indexes on tables. The index_id = 1 corresponds to the clustered index, which is typically created when a primary key is defined.

The query checks for the absence of a primary key by ensuring that there is no clustered index (index_id = 1) associated with the table.

Additional Resources

For further reading on SQL indexes and optimization, you can explore the following blog posts:

1. Unused Indexes: Learn About Unused Indexes in SQL Server
2. Over-Indexes: Understanding and Managing Over-Indexes
3. Missing Indexes: How to Identify Missing Indexes for Better Performance

By using these resources and the provided SQL query, you can identify tables without primary keys and take appropriate actions to ensure the efficiency and integrity of your database.

Find Server property using TSQL code

Retrieving SQL Server Information Using T-SQL

In this article, we will discuss how to retrieve information about your installed SQL Server instance using a T-SQL query that interacts with internal system objects. This query can provide various details, such as the service account, paths, configuration settings, and much more.

Query Overview

The following T-SQL query retrieves multiple configuration settings and properties from SQL Server. It covers information such as the server's version, instance details, backup paths, and various registry settings. This is useful for system administrators and those who need to audit or document SQL Server environments.

The query returns a result in two columns: one with the name of the configuration setting and the other with its value.

Query Explanation

The query starts by declaring a series of variables that will hold the registry paths and values related to SQL Server configuration.

DECLARE @HkeyLocal NVARCHAR(18),
        @ServicesRegPath NVARCHAR(34),
        @SqlServiceRegPath SYSNAME,
        @BrowserServiceRegPath SYSNAME,
        @MSSqlServerRegPath NVARCHAR(31),
        @InstanceNamesRegPath NVARCHAR(59),
        @InstanceRegPath SYSNAME,
        @SetupRegPath SYSNAME,
        @NpRegPath SYSNAME,
        @TcpRegPath SYSNAME,
        @RegPathParams SYSNAME,
        @FilestreamRegPath SYSNAME;

The paths for various registry keys related to SQL Server configuration are then set:

SELECT @HkeyLocal = N'HKEY_LOCAL_MACHINE';

-- Instance-based paths
SELECT @MSSqlServerRegPath = N'SOFTWARE\\Microsoft\\MSSQLServer';
SELECT @InstanceRegPath = @MSSqlServerRegPath + N'\\MSSQLServer';
SELECT @FilestreamRegPath = @InstanceRegPath + N'\\Filestream';
SELECT @SetupRegPath = @MSSqlServerRegPath + N'\\Setup';
SELECT @RegPathParams = @InstanceRegPath + '\\Parameters';

-- Services
SELECT @ServicesRegPath = N'SYSTEM\\CurrentControlSet\\Services';
SELECT @SqlServiceRegPath = @ServicesRegPath + N'\\MSSQLSERVER';
SELECT @BrowserServiceRegPath = @ServicesRegPath + N'\\SQLBrowser';

-- InstanceId setting
SELECT @InstanceNamesRegPath = N'SOFTWARE\\Microsoft\\Microsoft SQL Server\\Instance Names\\SQL';

-- Network settings
SELECT @NpRegPath = @InstanceRegPath + N'\\SuperSocketNetLib\\Np';
SELECT @TcpRegPath = @InstanceRegPath + N'\\SuperSocketNetLib\\Tcp';

Fetching Configuration Details

The query then retrieves various configuration values using the xp_instance_regread extended stored procedure. Some of the configuration details include:

• Audit level
• Number of error logs
• Login mode
• Mail profile
• Backup directory

For example, the following code fetches the audit level:

DECLARE @SmoAuditLevel INT;
EXEC master.dbo.xp_instance_regread @HkeyLocal, @InstanceRegPath, N'AuditLevel', @SmoAuditLevel OUTPUT;

File Paths and Error Logs

The query continues by retrieving file paths for the master database, error logs, and other important system directories:

DECLARE @MasterPath NVARCHAR(512),
        @LogPath NVARCHAR(512),
        @ErrorLog NVARCHAR(512),
        @ErrorLogPath NVARCHAR(512);

SELECT @MasterPath = SUBSTRING(physical_name, 1, LEN(physical_name) - CHARINDEX('\\', REVERSE(physical_name)))
FROM master.sys.database_files WHERE name = N'master';

SELECT @LogPath = SUBSTRING(physical_name, 1, LEN(physical_name) - CHARINDEX('\\', REVERSE(physical_name)))
FROM master.sys.database_files WHERE name = N'mastlog';

SELECT @ErrorLog = CAST(SERVERPROPERTY(N'errorlogfilename') AS NVARCHAR(512));
SELECT @ErrorLogPath = SUBSTRING(@ErrorLog, 1, LEN(@ErrorLog) - CHARINDEX('\\', REVERSE(@ErrorLog)));

Extracting Additional Information

The query also retrieves additional server and instance details, including the service account name, whether named pipes and TCP are enabled, and more.

DECLARE @ServiceAccount NVARCHAR(512);
EXEC master.sys.xp_instance_regread @HkeyLocal, @SqlServiceRegPath, N'ObjectName', @ServiceAccount OUTPUT;

DECLARE @NamedPipesEnabled INT;
EXEC master.dbo.xp_instance_regread @HkeyLocal, @NpRegPath, N'Enabled', @NamedPipesEnabled OUTPUT;

DECLARE @TcpEnabled INT;
EXEC master.sys.xp_instance_regread @HkeyLocal, @TcpRegPath, N'Enabled', @TcpEnabled OUTPUT;

Retrieving Server and Instance Properties

The final output includes several important properties, such as the version of SQL Server, the server collation, and whether SQL Server is clustered:

SELECT
  SERVERPROPERTY(N'ProductVersion') AS [VersionString],
  CAST(SERVERPROPERTY(N'Edition') AS SYSNAME) AS [Edition],
  CAST(SERVERPROPERTY(N'ProductLevel') AS SYSNAME) AS [ProductLevel],
  SERVERPROPERTY('Collation') AS [Collation],
  CAST(SERVERPROPERTY('IsClustered') AS BIT) AS [IsClustered];

Query Result

The query outputs a list of configuration names along with their corresponding values. Here is an example of the result structure:

• Audit Level: 1
• Number of Error Logs: 10
• Login Mode: 2
• Mail Profile: NULL
• Backup Directory: C:\Backup

Conclusion

This T-SQL query is a valuable tool for retrieving SQL Server configuration details from system registry entries and properties. By executing this query, administrators can gain insight into the SQL Server instance’s configuration without needing to manually inspect the system or configuration files.

Availability group fail-over

Forced Manual Failover with Data Loss (2-step Process)

To force a failover with potential data loss, connect to the SQL Server instance where the secondary replica is hosted, and execute the following command:

ALTER AVAILABILITY GROUP [ag1] FORCE_FAILOVER_ALLOW_DATA_LOSS;

Once the original primary replica recovers, it will try to assume the primary role. To ensure the previous primary becomes secondary again, run the command below on that instance:

ALTER AVAILABILITY GROUP [ag1] SET (ROLE = SECONDARY);

Steps to Manually Failover Without Data Loss:

1. First, configure the target secondary replica to use SYNCHRONOUS_COMMIT mode:

ALTER AVAILABILITY GROUP [ag1]
     MODIFY REPLICA ON N''
     WITH (AVAILABILITY_MODE = SYNCHRONOUS_COMMIT);

2. To verify that active transactions are committed to the primary and at least one synchronous secondary replica, run the following query:

SELECT ag.name, drs.database_id, drs.group_id, drs.replica_id,
   drs.synchronization_state_desc, ag.sequence_number
FROM sys.dm_hadr_database_replica_states drs, sys.availability_groups ag
WHERE drs.group_id = ag.group_id;

3. Update the REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT setting to 1:

ALTER AVAILABILITY GROUP [ag1]
     SET (REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 1);

4. Offline the primary replica in preparation for the role change:

ALTER AVAILABILITY GROUP [ag1] OFFLINE;

5. Promote the target secondary replica to the primary role:

ALTER AVAILABILITY GROUP ag1 FORCE_FAILOVER_ALLOW_DATA_LOSS;

6. Change the role of the previous primary replica to SECONDARY. Run this on the SQL Server instance hosting the previous primary replica:

ALTER AVAILABILITY GROUP [ag1] SET (ROLE = SECONDARY);

For more information, check out this resource:

Check the Writable Copy

Additional reference: Manual SQL Server Availability Group Failover

How to find SQL server Object dependency

How to find SQL server Object dependencies

In T-SQL, we can retrieve all dependent objects across all databases, including those from remote server linked objects. The `sql_expression_dependencies` system view contains one row for each dependency on a user-defined entity in the current database. This includes dependencies between scalar, user-defined functions and other SQL Server modules, including natively compiled ones.

CREATE TABLE #alldep (
  dbname VARCHAR(100),
  objname VARCHAR(100),
  sdbname VARCHAR(100),
  sschma VARCHAR(100),
  stable VARCHAR(100),
  sserver VARCHAR(100)
)

INSERT INTO #alldep
EXEC SP_MSFOREACHDB 'USE ? 
  SELECT db_name(), 
         OBJECT_NAME(referencing_id) AS referencing_object,
         referenced_database_name,
         referenced_schema_name,
         referenced_entity_name,
         referenced_server_name
  FROM sys.sql_expression_dependencies
  WHERE referenced_database_name IS NOT NULL
  AND is_ambiguous = 0'

SELECT * FROM #alldep
SELECT * FROM #alldep WHERE sserver <> ''

For more information on the system view `sys.sql_expression_dependencies`, check the official documentation here: Microsoft Docs

How to find SQL Server installation Date

To find the installation date of SQL Server, we need to examine the creation date of the "NT Authority\\System" login. This login is created during the SQL Server installation process.

-- Query to retrieve the server installation date
SELECT create_date 
FROM sys.server_principals 
WHERE name = 'NT AUTHORITY\\SYSTEM';

-- Alternate query to retrieve the server installation date using SID
SELECT create_date, *  
FROM sys.server_principals 
WHERE sid = 0x010100000000000512000000;

-- Query to retrieve the creation date for the "NT AUTHORITY\\NETWORK SERVICE" login
SELECT create_date 
FROM sys.server_principals 
WHERE name = N'NT AUTHORITY\\NETWORK SERVICE';

For more information, check out the following articles:

• Server Roles & Permissions
• SQL Server OS Information

How Find SQL Server collation

How to Find SQL Server Collation

Collations in SQL Server determine the sorting rules, case sensitivity, and accent sensitivity for your data. They play a crucial role when working with character data types like char and varchar, as they define the code page and the corresponding characters that can be represented.

Case Sensitivity

Case sensitivity refers to whether uppercase and lowercase characters are treated as distinct. For example, if the letters "A" and "a" (or "B" and "b") are considered different, the collation is case-sensitive. Computers differentiate between these letters using their ASCII values: "A" has an ASCII value of 65, while "a" has an ASCII value of 97. Similarly, "B" is 66, and "b" is 98.

Accent Sensitivity

Accent sensitivity determines whether characters with accents are considered different from their non-accented counterparts. For example, if "a" and "á" or "o" and "ó" are treated as the same, the collation is accent-insensitive. However, when treated differently, it is accent-sensitive. This distinction is based on the ASCII values of characters: "a" has a value of 97, while "á" has a value of 225; "o" is 111, and "ó" is 243.

Kana Sensitivity

Kana sensitivity occurs when Japanese kana characters, Hiragana and Katakana, are treated as distinct. When these characters are considered different in sorting, it is referred to as kana-sensitive collation.

Width Sensitivity

Width sensitivity refers to how single-byte characters (half-width) and their double-byte counterparts (full-width) are treated. When these characters are considered distinct, the collation is width-sensitive.

SQL Queries for Collation Insights

Here are a few SQL queries you can use to inspect and work with collations in SQL Server:

1. View Columns with Collations:

   This query retrieves the schema, table, column names, and their respective collation names:

   
   SELECT s.name AS 'Schema_Name', t.name AS Table_Name,
          c.name AS Column_Name, c.collation_name AS Collation
   FROM sys.schemas s
   INNER JOIN sys.tables t ON t.schema_id = s.schema_id
   INNER JOIN sys.columns c ON c.object_id = t.object_id
   WHERE collation_name IS NOT NULL
   ORDER BY Column_Name;
       

2. List All Supported Collations:

   You can use the following query to get a list of all supported collations:

   
   SELECT name, description FROM fn_helpcollations();
       

3. List All Databases with Collation Names:

   This query lists all databases along with their collation names:

   
   SELECT name, collation_name FROM sys.databases;
       

4. Find Server-Level Collation:

   To find the collation for the SQL Server instance, run the following query:

   
   SELECT @@servername AS server_name, 
          SERVERPROPERTY('Collation') AS Collation;
       

5. Comparing Different Collation Strings:

   If you need to compare columns with different collation settings, you can use the COLLATE clause as shown here:

   
   SELECT *
   FROM TABLE1
   INNER JOIN TABLE2 
     ON TABLE1.Col1 COLLATE Latin1_General_CS_AS = 
        TABLE2.Col1 COLLATE Latin1_General_CS_AS;
       

These queries can help you manage and investigate collation settings, ensuring your database handles string data according to the desired rules for case sensitivity, accent sensitivity, kana sensitivity, and width sensitivity.

Database property

SQL Server Database Properties and Status

In SQL Server, each row in the system catalog represents a database within the instance. Understanding the status and properties of these databases is crucial for database administrators.

Database Status and Visibility

If a database is not ONLINE, or if the AUTO_CLOSE option is enabled and the database is closed, certain column values might return as NULL. When a database is in the OFFLINE state, users with lower privileges will not see the corresponding row. To access the row of an offline database, users must have at least the ALTER ANY DATABASE permission on the server or CREATE DATABASE permissions in the master database.

Retrieving Database Information

To retrieve detailed information about the databases, you can use the following SQL query:

SELECT database_id,
       CONVERT(VARCHAR(25), DB.name) AS dbName,
       CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status],
       state_desc,
       (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS DataFiles,
       (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'rows') AS [Data MB],
       (SELECT COUNT(1) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS LogFiles,
       (SELECT SUM((size*8)/1024) FROM sys.master_files WHERE DB_NAME(database_id) = DB.name AND type_desc = 'log') AS [Log MB],
       user_access_desc AS [User access],
       recovery_model_desc AS [Recovery model],
       CASE compatibility_level
         WHEN 60 THEN '60 (SQL Server 6.0)'
         WHEN 65 THEN '65 (SQL Server 6.5)'
         WHEN 70 THEN '70 (SQL Server 7.0)'
         WHEN 80 THEN '80 (SQL Server 2000)'
         WHEN 90 THEN '90 (SQL Server 2005)'
         WHEN 100 THEN '100 (SQL Server 2008)'
         WHEN 110 THEN '110 (SQL Server 2012)'
         WHEN 120 THEN '120 (SQL Server 2014)'
         WHEN 130 THEN '130 (SQL Server 2016)'
         WHEN 140 THEN '140 (SQL Server 2017)'
         WHEN 150 THEN '150 (SQL Server 2019)'
       END AS [compatibility level],
       CONVERT(VARCHAR(20), create_date, 103) + ' ' + CONVERT(VARCHAR(20), create_date, 108) AS [Creation date],
       ISNULL((SELECT TOP 1
                 CASE TYPE WHEN 'D' THEN 'Full'
                           WHEN 'I' THEN 'Differential'
                           WHEN 'L' THEN 'Transaction log' END + ' – ' +
                 LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' +
                 CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' +
                 CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' +
                 CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' +
                 CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' +
                 CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' seconds)'
                 FROM msdb..backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC), '-') AS [Last backup],
       CASE WHEN is_fulltext_enabled = 1 THEN 'Fulltext enabled' ELSE '' END AS [fulltext],
       CASE WHEN is_auto_close_on = 1 THEN 'autoclose' ELSE '' END AS [autoclose],
       page_verify_option_desc AS [page verify option],
       CASE WHEN is_read_only = 1 THEN 'read only' ELSE '' END AS [read only],
       CASE WHEN is_auto_shrink_on = 1 THEN 'autoshrink' ELSE '' END AS [autoshrink],
       CASE WHEN is_auto_create_stats_on = 1 THEN 'auto create statistics' ELSE '' END AS [auto create statistics],
       CASE WHEN is_auto_update_stats_on = 1 THEN 'auto update statistics' ELSE '' END AS [auto update statistics],
       CASE WHEN is_in_standby = 1 THEN 'standby' ELSE '' END AS [standby],
       CASE WHEN is_cleanly_shutdown = 1 THEN 'cleanly shutdown' ELSE '' END AS [clean shutdown]
FROM sys.databases DB
ORDER BY DB.name;

  

This SQL query retrieves various details about each database, including:

• Database name
• Status (e.g., ONLINE, OFFLINE)
• Number of data and log files
• Size of data and log files
• User access
• Recovery model
• Last backup time
• Other configuration options like auto-shrink, read-only status, etc.

Recovery Model

The recovery model of a database determines how transactions are logged and whether the database can be restored to a point in time. The common recovery models are:

• FULL: Full recovery model
• BULK_LOGGED: Bulk-logged recovery model
• SIMPLE: Simple recovery model

Database Status

Here are some common database statuses:

• ONLINE: The database is available for queries.
• OFFLINE: The database is explicitly taken offline.
• RESTORING: The database is in the process of being restored.
• RECOVERING: The database is recovering and not yet ready for queries.
• SUSPECT: The database is in a state that cannot be recovered.

For more detailed information on system catalog views, refer to the official Microsoft documentation:
Sys Databases View - Microsoft Documentation

Additional Resources:

• Find Server Property Using T-SQL Code
• Get Table Property
• Get Server Properties