SQL Server Task Manager using TSQL

SQL Server Task Manager & System Memory Query

In this post, we’ll break down a SQL query that retrieves the running tasks from the Windows Task Manager and fetches important system memory statistics from SQL Server. This combined approach helps system administrators monitor running tasks and memory usage on the server effectively. Let's dive right in!

1. Retrieving Task Information from the Task Manager

The first part of the query extracts the list of tasks running on your SQL Server. It uses the xp_cmdshell extended stored procedure to execute the Windows tasklist command, which lists the running processes in CSV format. This data is then inserted into a temporary table.

-- Drop the temporary table if it exists
drop table if exists #TM

-- Create the temporary table for storing task data
Create table #TM (id int identity, tsk varchar(1000))

-- Insert tasklist output into the temporary table
insert into #TM (tsk) exec xp_cmdshell 'tasklist /v /FO csv'
    

Here’s a quick explanation of the steps in this code:

• xp_cmdshell runs the tasklist command with the /v option for detailed information and /FO csv for CSV formatting.
• The task list is inserted into the temporary table #TM</>, where each row contains the task details like process name, PID, memory usage, etc.

2. Cleaning and Formatting the Task Data

Once we have the raw task data, we need to clean it up. A Common Table Expression (CTE) is used here to remove unwanted characters like commas and double quotes from the CSV output, making it easier to extract individual task details later.

;with ct as (
    select *, 
           replace(replace(replace(tsk,'","','|'),'"',''),',','') ntsk
    from #TM where id >= 2
)
    

What’s happening here:

• The replace function is applied multiple times to remove unwanted characters like quotes and commas from the task data.
• The cleaned data is stored in a new column ntsk</>, which will be processed further.

3. Parsing the Data and Extracting Specific Information

Next, we use SQL Server’s JSON_VALUE function to parse the cleaned task data and extract specific fields, such as the task name, PID, memory usage, and others. We also use CROSS APPLY to convert the cleaned data into a JSON array format that’s easier to work with.

Select A.id,
       ImageName = nullif(JSON_VALUE(JS,'$[0]'),''),
       PID = nullif(JSON_VALUE(JS,'$[1]'),''),
       SessionName = nullif(JSON_VALUE(JS,'$[2]'),''),
       SessionNo = nullif(JSON_VALUE(JS,'$[3]'),''),
       MemUsage = cast(replace(nullif(JSON_VALUE(JS,'$[4]'),''), ' k','') as int),
       Status = nullif(JSON_VALUE(JS,'$[5]'),''),
       UserName = nullif(JSON_VALUE(JS,'$[6]'),''),
       CPUTime = nullif(JSON_VALUE(JS,'$[7]'),''),
       WindowTitle = nullif(JSON_VALUE(JS,'$[8]'),'')
From ct A
CROSS APPLY (values ('["'+replace(string_escape(ntsk,'json') ,'|','","')+'"]') ) B(JS)
order by MemUsage desc
    

Here’s how the data is processed:

• The string_escape function prepares the data for JSON formatting by escaping special characters, and the replace function replaces pipes with commas to create a valid JSON array.
• JSON_VALUE is then used to extract specific fields from the JSON array. For example, $[0] extracts the image name (task name), $[1] gets the PID, and so on.
• We use nullif to handle empty strings and replace them with NULL.
• The result is ordered by memory usage in descending order to highlight the processes that are consuming the most resources.

4. Retrieving System Memory Information

Finally, we fetch system memory details using the sys.dm_os_sys_memory DMV (Dynamic Management View). This provides crucial information about the physical memory available on the server.

-- Retrieve system memory information
SELECT   @@servername as servername,
         total_physical_memory_kb / 1024 AS total_physical_memory_mb,
         available_physical_memory_kb / 1024 AS available_physical_memory_mb,
         system_memory_state_desc
FROM     sys.dm_os_sys_memory;
    

What this query does:

• total_physical_memory_kb and available_physical_memory_kb give the total and available physical memory in kilobytes. We divide these by 1024 to convert them into megabytes (MB).
• system_memory_state_desc describes the current state of the system’s memory (whether it’s under stress, healthy, etc.).
• @@servername Returns the name of the SQL Server instance where this query is running.

5. Conclusion

This SQL query provides an effective way to monitor running tasks and system memory on your server. It allows you to:

• Retrieve detailed task/process information from the Windows Task Manager.
• Format and clean up raw task data into a readable structure.
• Get important memory statistics to keep your server healthy and optimized.

By using this query, you can quickly identify high-memory processes and track overall system performance in a simple, easy-to-understand format.

Note: The xp_cmdshell extended stored procedure must be enabled for this query to work. Make sure your environment allows the execution of this command.

If you found this tutorial helpful, be sure to share it with your fellow SQL enthusiasts or subscribe for more beginner-friendly guides!

How to Remove Duplicate Records in SQL Server

If you're working with databases, one common problem you might face is duplicate data. Duplicates can cause a lot of headaches, especially when it comes to data analysis or reporting. But don’t worry! In this post, we’ll walk through a simple way to remove duplicate records in SQL Server using a stored procedure that you can use in your own projects.

We’ll break down the SQL code step-by-step to make sure you understand how it works. By the end of this tutorial, you’ll be able to use this technique to keep your data clean and organized.

Step 1: What is a Duplicate Record?

A duplicate record is when you have two or more rows in your table with the exact same data in one or more columns. For example, if you have a list of customers and two customers have the same name, email, and phone number, those would be considered duplicates.

Step 2: The Concept of the RemoveDuplicate Procedure

In SQL Server, you can use a stored procedure to automate the process of removing duplicates. A stored procedure is like a saved set of SQL commands that can be executed later. The RemoveDuplicate procedure that we’ll look at helps to:

• Find duplicate records in a table.
• Remove the extra copies, leaving only one unique row.

Step 3: Understanding the Code

Let’s break down the SQL code that removes duplicates. Don’t worry if it seems complex at first. We’ll explain it in a way that’s easy to follow.

Step 3.1: The Procedure and Parameters

The procedure is created with the following parameters:

CREATE PROCEDURE RemoveDuplicate @SCDID sysname, @IsDebug bit = 0

• @SCDID: This is an ID that identifies the specific rule you want to apply for removing duplicates. It helps the procedure know which table to work with.
• @IsDebug: This is a flag that lets you print out the SQL commands for debugging. If you're just starting out, you can use this to see the queries that will be executed.

Step 3.2: Using ROW_NUMBER() to Identify Duplicates

The first part of the code uses a Common Table Expression (CTE) and the ROW_NUMBER() function to label rows:

WITH ct AS (
    SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ColumnSearch ORDER BY ColumnSearch)
    FROM SourceDatabase.SourceSchema.SourceObject
)

The ROW_NUMBER() function assigns a unique number to each row, starting from 1. The key part is PARTITION BY, which groups rows based on a specific column. For example, if you're looking for duplicates in the "Email" column, all rows with the same email will be grouped together.

After the row numbers are assigned, we can delete the duplicates.

Step 3.3: Removing Duplicates

Now that the rows are numbered, we can delete the duplicates by selecting rows with rn >= 2 (anything that’s not the first occurrence):

DELETE FROM ct WHERE rn >= 2;

This means that if there are multiple rows with the same data in the ColumnSearch, all except the first one will be deleted.

Step 3.4: Handling Column Exclusions

Sometimes, you don’t want to compare all columns for duplicates. In that case, we use the ColumnExclude parameter to exclude certain columns from the comparison. The code dynamically builds a list of column names, excluding any columns specified in ColumnExclude:

SELECT @rOut = STRING_AGG(c.name, ',')
    FROM SourceDatabase.sys.COLUMNS c
    WHERE c.name NOT IN (@ColumnExclude)

Step 3.5: Final Cleanup Using LAG()

In the final step, we use the LAG() function, which allows us to compare each row with the previous row in a group. If the current row has the same data as the previous one, it’s considered a duplicate and will be removed:

WITH ct AS (
    SELECT *, Ckp = CONCAT(@rval),
           PRv = LAG(CONCAT(@rval)) OVER(PARTITION BY ColumnSearch ORDER BY ColumnSearch)
    FROM SourceDatabase.SourceSchema.SourceObject
)
DELETE FROM ct WHERE ISNULL(ckp, '') = ISNULL(PRv, '');

Step 4: Executing the Procedure

Once you understand how the procedure works, you can execute it to remove duplicates. Simply call the procedure with the SCDID (rule ID) and @IsDebug parameter (set to 1 for debugging):

EXEC RemoveDuplicate @SCDID = 'yourRuleID', @IsDebug = 1;

If @IsDebug is set to 1, the procedure will print out the SQL commands it is going to execute, so you can check them before they run.

Step 5: Original Table Design for SCD2_Rule

Here’s the original design of the SCD2_Rule table used in the stored procedure. This table contains information about your source and target tables and the columns used for comparison:

SCDID	SourceDatabase	SourceSchema	SourceObject	TargetDatabase	TargetSchema	TargetObject	ColumnSearch	ColumnExclude
sysname	sysname	sysname	sysname	sysname	sysname	sysname	nvarchar(-1)	nvarchar(-1)

Step 6: Complete SQL Stored Procedure Code

Here’s the full SQL code for the RemoveDuplicate procedure, including everything we’ve discussed so far:

CREATE PROCEDURE RemoveDuplicate
    @SCDID sysname,
    @IsDebug bit = 0
AS
BEGIN
    DECLARE @Sql AS NVARCHAR(MAX), @rVal varchar(max)

    -- Fetch the SQL query template for the given SCDID
    SELECT @Sql = 'WITH ct AS (
        SELECT *, rn = ROW_NUMBER() OVER(PARTITION BY ' + ColumnSearch + ' ORDER BY ' + ColumnSearch + ')
        FROM ' + CONCAT(SourceDatabase, '.', SourceSchema, '.', SourceObject) + '
    ) DELETE FROM ct WHERE rn >= 2'
    FROM dbo.SCD2_Rule WHERE SCDID = @SCDID

    -- Debugging: Print the dynamic SQL if IsDebug is set to 1
    IF @IsDebug = 1
        PRINT @Sql

    -- Execute the dynamic SQL to remove duplicates
    EXEC SP_EXECUTESQL @Sql

    -- Other duplicate handling: Exclude specified columns
    SELECT @Sql = 'SELECT @rOut = STRING_AGG(c.name, '','')
        FROM ' + SourceDatabase + '.sys.COLUMNS c
        JOIN ' + SourceDatabase + '.sys.tables t ON c.object_id = t.object_id
        WHERE t.name = ''' + SourceObject + ''' 
        AND c.name NOT IN (' + b.a + ') AND c.name <> ''DataValidTo'''
    FROM dbo.SCD2_Rule a
    OUTER APPLY (
        SELECT STRING_AGG(''' + value + ''', ',') a
        FROM STRING_SPLIT(a.ColumnSearch + ',' + a.ColumnExclude, ',') b1
    ) b
    WHERE SCDID = @SCDID

    -- Debugging: Print the dynamic SQL for column exclusions
    IF @IsDebug = 1
        PRINT CONCAT('@Sql : ', @Sql)

    -- Execute the query for excluding columns
    EXEC sp_executesql @Sql, N'@rOut varchar(max) OUTPUT', @rOut=@rVal OUTPUT;

    -- Debugging: Print the result of excluded columns
    IF @IsDebug = 1
        PRINT CONCAT('@rVal : ', @rVal)

    -- Final cleanup: Remove records with identical values in key columns
    SELECT @Sql = 'WITH ct AS (
        SELECT *, Ckp = CONCAT(' + @rVal + '),
            PRv = LAG(CONCAT(' + @rVal + ')) OVER(PARTITION BY ' + REPLACE(ColumnSearch, ',DataSystemDate', '') + ' ORDER BY ' + ColumnSearch + ')
        FROM ' + CONCAT(SourceDatabase, '.', SourceSchema, '.', SourceObject) + '
    ) DELETE FROM ct WHERE ISNULL(ckp, '''') = ISNULL(PRv, '''')'
    FROM dbo.SCD2_Rule WHERE SCDID = @SCDID

    -- Debugging: Print the final SQL for cleanup
    IF @IsDebug = 1
        PRINT @Sql

    -- Execute the final cleanup query
    EXEC SP_EXECUTESQL @Sql
END
        

Step 7: Conclusion

You’ve just learned how to remove duplicate records from your SQL Server tables using a stored procedure. The RemoveDuplicate procedure is powerful because it allows you to automate the process of cleaning up your data.

Key Takeaways:

• ROW_NUMBER() is used to assign a unique number to each row based on certain columns.
• LAG() compares rows to detect duplicates.
• Dynamic SQL is used to make the procedure flexible for different tables and columns.
• Debugging is made easy with the @IsDebug flag to see the SQL commands before they run.

Now you can keep your SQL Server tables clean by removing duplicate data with ease. If you have any questions or need further clarification, feel free to ask in the comments!

Bonus Tip:

If you're working with large datasets, be sure to test the procedure on a smaller subset of your data before running it on your production database. Always back up your data first!

If you found this tutorial helpful, be sure to share it with your fellow SQL enthusiasts or subscribe for more beginner-friendly guides!

Pivot table

Below is the general syntax used when working with the PIVOT operator in SQL. This technique transforms rows into columns to better analyze aggregated data.

SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that retrieves the data>) AS <source alias>  
PIVOT  
(  
    <aggregate function>(<column to aggregate>)  
    FOR [<column with values to become headers>]  
    IN ([first pivoted column], [second pivoted column], ..., [last pivoted column])  
) AS <pivot table alias>  
<optional ORDER BY clause>;

Example: Basic PIVOT Query

-- This example creates a pivot table with one row and five columns
SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days,  
[0], [1], [2], [3], [4]  
FROM  
(SELECT DaysToManufacture, StandardCost FROM Production.Product) AS SourceTable  
PIVOT  
(  
AVG(StandardCost)  
FOR DaysToManufacture IN ([0], [1], [2], [3], [4])  
) AS PivotTable;

Advanced PIVOT Example

USE AdventureWorks2014;  
GO  
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5  
FROM  
(SELECT PurchaseOrderID, EmployeeID, VendorID FROM Purchasing.PurchaseOrderHeader) AS p  
PIVOT  
(  
COUNT(PurchaseOrderID)  
FOR EmployeeID IN ([250], [251], [256], [257], [260])  
) AS pvt  
ORDER BY pvt.VendorID;

Example: UNPIVOT Operation

-- Creating the base table and inserting sample values
CREATE TABLE pvt (
    VendorID INT, 
    Emp1 INT, 
    Emp2 INT, 
    Emp3 INT, 
    Emp4 INT, 
    Emp5 INT
);  
GO  

INSERT INTO pvt VALUES (1,4,3,5,4,4);  
INSERT INTO pvt VALUES (2,4,1,5,5,5);  
INSERT INTO pvt VALUES (3,4,3,5,4,4);  
INSERT INTO pvt VALUES (4,4,2,5,5,4);  
INSERT INTO pvt VALUES (5,5,1,5,5,5);  
GO  

-- Applying the UNPIVOT operator
SELECT VendorID, Employee, Orders  
FROM  
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5 FROM pvt) AS p  
UNPIVOT  
(Orders FOR Employee IN (Emp1, Emp2, Emp3, Emp4, Emp5)) AS unpvt;  
GO

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

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

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 calculate Easter date

In this example, we'll explore the usage of Date and Integer data types in SQL Server. The code snippets demonstrate how to manipulate and extract date-related information.

1. Sample Code: Calculating Easter Date for a Given Year

Below is a SQL script to calculate the Easter date for a given year:

DECLARE @Date DATE, @c INT, @n INT, @i INT, @k INT, @j INT, @l INT, @m INT, @d INT, @Year INT = 2025
SET @n = @Year - 19 * (@Year / 19)
SET @c = @Year / 100
SET @k = (@c - 17) / 25
SET @i = @c - @c / 4 - (@c - @k) / 3 + 19 * @n + 15
SET @i = @i - 30 * (@i / 30)
SET @i = @i - (@i / 28) * (1 - (@i / 28) * (29 / (@i + 1)) * ((21 - @n) / 11))
SET @j = @Year + @Year / 4 + @i + 2 - @c + @c / 4
SET @j = @j - 7 * (@j / 7)
SET @l = @i - @j
SET @m = 3 + (@l + 40) / 44
SET @d = @l + 28 - 31 * (@m / 4)
SELECT EasterDate = CAST(@Year AS VARCHAR) + '-' + CAST(@m AS VARCHAR) + '-' + CAST(@d AS VARCHAR)
    

2. Retrieve Date Information Using SQL Server Functions

The following SQL queries return various date components such as the year, week, month, and the current date and time:

-- Retrieve various components of the current date
SELECT DATENAME(year, GETDATE()) AS Year,
       DATENAME(week, GETDATE()) AS Week,
       DATENAME(dayofyear, GETDATE()) AS DayOfYear,
       DATENAME(month, GETDATE()) AS Month,
       DATENAME(day, GETDATE()) AS Day,
       DATENAME(weekday, GETDATE()) AS Weekday;

-- Higher precision datetime functions
SELECT SYSDATETIME() AS 'DateAndTime',       -- returns datetime2(7)
       SYSDATETIMEOFFSET() AS 'DateAndTime+Offset', -- datetimeoffset(7)
       SYSUTCDATETIME() AS 'DateAndTimeInUtc';  -- returns datetime2(7)

-- Lesser precision datetime functions
SELECT CURRENT_TIMESTAMP AS 'DateAndTime',  -- note: no parentheses
       GETDATE() AS 'DateAndTime',
       GETUTCDATE() AS 'DateAndTimeUtc';
    

3. Example of Date Output

Here’s an example of a date output in SQL Server:

References

For further details and in-depth exploration, check out the original article on the Simple Talk Website.

T-SQL function

T-SQL Function Overview in SQL Server

What Functions Are Available in SQL Server?

Microsoft SQL Server provides a rich set of built-in functions to help developers and administrators handle complex queries, data transformations, calculations, and data analysis. This article outlines the major categories of T-SQL functions, complete with helpful links to official documentation.

---

Analytic Functions

Analytic functions compute an aggregate value across a group of rows and return a value for each row. These are ideal for tasks such as running totals, moving averages, and ranking.

• CUME_DIST
• LEAD
• FIRST_VALUE
• PERCENTILE_CONT
• LAG
• PERCENTILE_DISC
• LAST_VALUE
• PERCENT_RANK

---

Aggregate Functions

These functions perform a calculation on a set of values and return a single value.

• APPROX_COUNT_DISTINCT
• MIN
• AVG
• STDEV
• CHECKSUM_AGG
• STDEVP

---

Configuration Scalar Functions

These functions return information about the current configuration of the SQL Server session.

• @@DATEFIRST
• @@OPTIONS

---

Data Type Casting and Conversion Functions

Functions for converting and casting between SQL Server data types:

• CAST and CONVERT
• PARSE
• TRY_CAST
• TRY_CONVERT
• TRY_PARSE

---

Security Functions

Symmetric Encryption & Decryption

• ENCRYPTBYKEY
• DECRYPTBYKEY

Asymmetric Encryption & Decryption

• ENCRYPTBYASYMKEY
• DECRYPTBYASYMKEY

Signature and Validation

• SIGNBYASYMKEY
• VERIFYSIGNEDBYASYMKEY

---

Date and Time Data Types

SQL Server provides rich support for various date and time types. Each type has specific attributes including format, range, precision, and size.

• time: High-precision time values

---

Date and Time Functions

Functions That Return Date Parts

Sql Server code template & guideline

 

Best Practices for Writing SQL Code: Stored Procedures, Views, and Functions

When working with large development teams or frequent production releases, it's essential to follow a consistent coding structure in SQL Server. This is particularly important in environments with restricted permissions where operations like DROP or CREATE may be limited for security or governance reasons.

The following guide outlines a standardized approach to writing SQL code — especially stored procedures, views, and functions — which enhances maintainability, consistency, and collaboration across teams.

---

📌 Sample SQL Procedure Template

Below is a template you can adapt for writing stored procedures:

/******************************************************************
* DATABASE        : OMAPINTERNET
* PROCEDURE NAME  : usp_SelectAllResults
* CREATED ON      : 04/03/2009
* AUTHOR          : Homer Simpson
* DESCRIPTION     : This procedure returns all results for a given NDC.
* WORK ORDER      : #13798
*******************************************************************
* MODIFICATIONS:
* DATE            : DEVELOPER         : CHANGES
* 08/01/2013      : Himanshu Patel    : Added Indicator column
******************************************************************/

---

✅ Recommended Coding Standards for SQL Procedures

To ensure clarity and best practices in SQL development, follow these guidelines:

• Use CamelCase for naming procedures and variables.
• Implement error handling using TRY...CATCH.
• Avoid cursors whenever possible; use set-based operations instead.
• Indent your code for better readability.
• Add blank lines between logical sections of your code.
• Use ANSI JOINs rather than old-style joins.
• Write clear and meaningful comments.
• Avoid using DROP or CREATE statements inside procedures.
• Declare all variables at the top of the procedure.
• Do not use column numbers in ORDER BY clauses — use column names instead.
• Avoid the use of the GOTO statement.
• Always use primary keys in WHERE clauses for UPDATE or DELETE statements.
• Add SET NOCOUNT ON at the beginning to reduce unnecessary result sets.
• Avoid SELECT *; explicitly define columns.
• Keep transactions short and commit as soon as possible.
• Use BEGIN TRANSACTION and COMMIT blocks for insert/update operations.
• Use sp_executesql instead of EXEC(@sql) for dynamic SQL.
• Avoid using WITH RECOMPILE unless absolutely necessary.
• Never use SQL reserved keywords as object names.
• Structure schemas so that each module has its own schema if the number of tables is large.
• Implement server-side paging to minimize network traffic.

---

🔠 Standard Variable Prefixes

To improve code readability and maintain consistency, use the following naming conventions for variable prefixes based on data type:

No	Data Type	Prefix	Notes
1	tinyint	@ti
2	smallint	@sm
3	int	@in
4	bigint	@bi
5	numeric	@nu
6	hierarchyid	@hi
7	bit	@bl
8	date	@Dt
9	time	@tm
10	datetimeoffset	@do
11	datetime	@Dt
12	datetime2	@Dt
13	money	@mo
14	smallmoney	@Sm
15	decimal	@De
16	float	@fl
17	char	@Ch
18	nchar	@nc
19	varchar	@Vc
20	nvarchar	@Nv
21	uniqueidentifier	@Ui
22	text	@Tx
23	ntext	@Nt
24	sql_variant	@Sv
25	varbinary	@Vb
26	image	@Im
27	xml	@Xm

Refresh all views in a database

 

Refreshing SQL Server Views Automatically

When working with SQL Server, views can become outdated if the underlying tables or objects they depend on are modified. This is especially true for non-schema-bound views—those that don’t explicitly enforce dependency checks on referenced objects. Fortunately, SQL Server provides tools to refresh the metadata of these views, ensuring that they continue to function correctly.

Script to Refresh All Views in a Database

The following T-SQL script automates the process of refreshing all non-schema-bound views in a selected database. It loops through each eligible view and executes the sp_refreshview stored procedure.

DECLARE @ActualView VARCHAR(255);
DECLARE viewlist CURSOR FAST_FORWARD
FOR
SELECT DISTINCT s.[Name] + '.' + o.[Name] AS ViewName
FROM [sys].objects o
JOIN [sys].schemas s ON o.SCHEMA_ID = s.SCHEMA_ID 
WHERE o.[type] = 'V'
  AND OBJECTPROPERTY(o.[object_id], 'IsSchemaBound') <> 1
  AND OBJECTPROPERTY(o.[object_id], 'IsMsShipped') <> 1;

OPEN viewlist;
FETCH NEXT FROM viewlist INTO @ActualView;

WHILE @FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        EXECUTE sp_refreshview @ActualView;
    END TRY
    BEGIN CATCH
        PRINT 'View ' + @ActualView + ' cannot be refreshed.';
    END CATCH;

    FETCH NEXT FROM viewlist INTO @ActualView;
END;

CLOSE viewlist;
DEALLOCATE viewlist;

This script:

• Selects all user-defined, non-schema-bound views
• Iteratively refreshes each view using sp_refreshview
• Handles errors gracefully using TRY...CATCH

Refreshing a Specific View

If you want to refresh a single view manually, you can run:

EXECUTE sp_refreshview N'Sales.vIndividualCustomer';

Refresh Views Dependent on a Specific Object

Sometimes, you may want to refresh only those views that depend on a particular table or object. Here’s how you can identify and generate sp_refreshview commands for them:

SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''
FROM sys.objects AS so
INNER JOIN sys.sql_expression_dependencies AS sed 
    ON so.object_id = sed.referencing_id
WHERE so.type = 'V'
  AND sed.referenced_id = OBJECT_ID('Person.Person');

Replace 'Person.Person' with the name of the object that has changed. This query will list the commands needed to refresh only the affected views.

Learn More

For more details, see the official Microsoft documentation:
sp_refreshview (Transact-SQL) – Microsoft Docs

Leverage Standard RegEx in SSMS for Powerful Searching

SQL Server Management Studio (SSMS) allows you to perform advanced search and replace operations using Regular Expressions (RegEx). These can be particularly helpful for cleaning up code, refactoring queries, or removing unnecessary characters. Below are some useful RegEx examples that you can apply to your SSMS queries.

---

1. Removing Blank Lines

If you’ve imported code from a source like Firefox, you might encounter unwanted blank lines. To remove these, you can search for:

\n @\n

And replace it with:

\n

---

2. Finding Un-commented 'INT' Instances

To find all instances of INT that aren't commented out, use:

^~(:b<--).:bINT>

This RegEx checks for lines that aren't preceded by a comment (--), making it easier to find INT instances in active code.

---

3. Selecting Up to 100 Characters in a Line

To select the first 100 characters of a line (and all characters if fewer than 100), use:

^(.^100)|(.*)

This will capture up to 100 characters at the start of the line and will fall back to matching the entire line if the line is shorter.

---

4. Inserting a String at a Specific Position

To insert a string at a specific column position (e.g., after the first 100 characters), use the following:

Find:

^{.^100}

Then, replace it with:

\1'I've inserted this'

This will insert the specified text after the first 100 characters.

---

5. Deleting Columns After a Specific Position

To delete 10 characters after column 100, use:

^{(.^100)}{.^10}

Replace with:

\1

This RegEx will preserve the first 100 characters while removing the next 10.

---

6. Finding Quoted or Delimited Strings

To find strings enclosed in either quotes or delimiters, use:

("[^"]*")|([[^[]])

This captures quoted strings as well as strings enclosed by square brackets.

---

7. Replacing Quoted Strings with Delimiters

To replace all quoted strings with delimited strings, use the following find and replace pattern:

Find:

("[^"]*")

Replace with:

[\1]

---

8. Removing Inline Comments

If you want to remove inline comments that take up the entire line, use:

^:b<--.>$

Replace with nothing. This RegEx will remove lines that only contain comments.

---

9. Finding Valid Object Names with Delimiters

To find valid object names within delimiters, use:

[{[A-Za-z_#@][A-Za-z0-9#$@_]*}]

And replace with:

\1

This will remove unnecessary delimiters while ensuring the object name is valid.

---

10. Finding 'Tibbling' Prefixes (tbl, vw, fn, usp)

To find object names that begin with prefixes like tbl, vw, fn, or usp, use:

<(tbl|vw|fn|usp)[A-Za-z0-9#$@_]*>

This RegEx helps identify objects using certain naming conventions.

---

11. Removing Tibbling Prefixes

To remove these prefixes (e.g., tbl or usp) from the object names, use:

Find:

<(tbl|vw|fn|usp){[A-Za-z0-9#$@_]*}>

And replace with:

\1

This will keep the object name while removing the prefix.

---

12. Matching Words with 3 or More Characters

If you need to match words that are at least 3 characters long, use:

<:c^3:c*>

This is a workaround for the lack of proper range quantifiers in SSMS.

---

13. Finding Multi-line Comments

To find multi-line comments that start with /* and end with */, use:

/*(:Wh|[^:Wh])*/

This RegEx works around the limitation of stopping at the end of a line.

---

14. Finding Title-Cased Words

To find words that start with a capital letter followed by lowercase letters, use:

<:Lu:Ll[a-zA-Z0-9_$]*>

This helps find properly capitalized words in your code.

---

15. Removing Headers in Code (e.g., SMO Comments)

To remove headers added by SMO (e.g., comments about object names and script dates), use:

Find:

/[*]^6.<[*]^*6/

Replace with nothing.

---

16. Commenting and Uncommenting Lines

• To comment out lines, select the lines and use:

  Find:

  ^

  Replace with:

  -- (or your comment character)

• To uncomment lines, use:

  Find:

  ^:b<--{.}*$

  Replace with:

  \1

---

17. Finding Two Words Separated by Up to Three Words

To find two words (e.g., FROM and AS) separated by up to three words, use:

\bFROM(?:\W+\w+){1,3}?\\W+AS\b

This RegEx allows for more flexibility in matching phrases between two keywords.

---

18. Finding the First Object Referenced by a FROM Clause

To find the first object referenced by a FROM clause, use:

^~(:b<--.){(:Wh|[^:Wh])#}&< (ON|CROSS|RIGHT|INNER|OUTER|LEFT|JOIN|WHERE|ORDER|GROUP|AS)

This matches keywords after FROM, such as INNER JOIN or WHERE.

---

19. Finding Integers or Floating-point Numbers

To find either integers or floating-point numbers, use:

~(. )<([+-]@[0-9]@.[0-9]E[+-][0-9])|([+-]@[0-9].[0-9]*)|([+-]@[0-9]+)

This captures both types of numeric values in your code.

---

Metacharacters Outside Square Brackets

Character	SSMS Equivalent	Meaning
\	\\	General escape character
^	^	Assert start of string or line
$	$	Assert end of string or line
.	.	Match any character except newline
[	[	Start character class
]	]	End character class
`	`	`
(	(	Start sub-pattern
)	)	End sub-pattern