PowerShell script to create SQL Server alias name

This script is useful for your environment having multiple always-on with replication setup and your listener value is persisted with RegisterallIP set to 1. When initiating the always-on failover, It will create/update the alias in the distributor server by pointing to the new primary server without RDP to each distributor server and that makes the […]

How to shrink tempdb database in SQL Server

This article discusses different methods that you can use to shrink the tempdb database in Microsoft SQL Server. Before you shrink the tempdb database using the methods described in this article, note the following: The tempdb size is reset to the final configured size (i.e. to the default size or the final size that was […]

Space allocated to the databases by disk letters

Found this useful for database migrations. This helps to identify how much space will need to be allocated for a new server and how a SQL Sever instance is using discs currently. Note. This won't show correct information on Linux instances and if you use mounting points in Windows.

Get all the Child Tables up to N level

Get all the dependent tables for a master table up to N level(Till the leaf ) you can either find dependency or you can find all the child tables.

Script for no of Index in a table

This script will tell you how many indexes there are for a single table and also it specifically shows how many non clustered index, and also which tables have a heap index.

A varchar(MAX) STRING_SPLIT function for SQL 2012 and above

An alternative for Microsoft's STRING_SPLIT function that will work on SQL Server 2012 and higher.

Execute on DMVs without user having View Server State

Wanted to share this script to the community just in case anyone out there may be search for a way to allow a hosted customer the ability to query certain DMVs for information without granting them View Server State. There are some other options out there, but this worked better for me after going through […]

Searching for orphaned DB files on the SQL server

See how to find those files that are not in use, but are taking up space on your instance.

Fiscal/Retail 4-5-4 Calendar Function

This function returns a 3 year calendar based on a 4-5-4/5-4-4/4-4-5 calendar, also known as a 52/53 week calendar.  The basis of this calendar function was derived from the NRF retail calendar published at https://nrf.com/resources/4-5-4-calendar and the Wikipedia article published at https://en.wikipedia.org/wiki/4%E2%80%934%E2%80%935_calendar. The calendar function requires 2 helper functions.  The helper functions were derived from […]

Table Variable Deferred Compilation (SQL 2019)

I have explored the SQL Server 2019, Intelligent Query Processing Feature – “Table Variable Deferred Compilation”.

The script contains some theory at the top and links to read.
After that, there are required queries to run on SQL Server 2019.
This way we can see the feature in action and look at it's strengths and caveats.