Understanding Indexing in SQL Server: Types and Usage

What is an Index?   

An index in SQL Server is a data structure associated with a table or view that speeds up the retrieval of rows based on the values in one or more columns. It serves as a well-organized reference guide, allowing SQL Server to efficiently locate rows that match query criteria without scanning the entire table.

Types of Indexes:

1. Clustered Index: Determines the physical order of data in a table, affecting the order of data when modified.
2. Non-clustered Index: Creates a separate structure with sorted references to actual data rows, useful for enhancing SELECT query performance.
3. Unique Index: Ensures uniqueness of values in the indexed column(s) across the table, aiding in data integrity.
4. Covering Index: Includes all columns needed to fulfill a query, minimizing I/O operations and improving query performance.
5. Filtered Index: Includes only a subset of rows in the table based on a WHERE clause, useful for optimizing queries targeting specific subsets of data.
6. Spatial Index: Specialized for spatial data types, facilitating efficient spatial queries such as distance calculations and intersections.
7. Columnstore Indexes: Organizes data by columns, beneficial for analytical queries involving aggregations and scans across large datasets.

Usage of Indexes:

 Faster Data Retrieval: Provides a shortcut to desired rows, reducing the time to locate and retrieve data, particularly helpful for SELECT queries.  
Optimizing Joins: Indexes on join columns enhance performance by quickly identifying matching rows.  
Sorting and Grouping: Speed up ORDER BY and GROUP BY operations by efficiently retrieving and organizing data.  
Constraint Enforcement: Unique indexes ensure data integrity by preventing duplicate values in indexed columns.  
Covering Queries: Minimizes I/O operations and speeds up query execution by scanning the index alone.  
Reducing I/O Operations: Efficient use of indexes minimizes I/O operations required to satisfy a query.

Best Practices for Indexing:

1. Selective Indexing: Focus on columns frequently used in WHERE clauses, JOIN conditions, and ORDER BY clauses to avoid unnecessary overhead.
2. Regular Maintenance: Monitor and maintain indexes regularly, including rebuilding or reorganizing to minimize fragmentation.
3. Avoid Over-Indexing: Strike a balance between performance gains and maintenance overhead to avoid diminishing returns.
4. Consider Clustered Index Carefully: Choose based on typical table queries and access patterns.
5. Use Indexing Tools: Leverage tools such as the Database Engine Tuning Advisor to recommend appropriate indexes based on query performance analysis.
6. Understand Query Execution Plans: Analyse plans to identify areas where indexes can optimize query performance.

Conclusion:  

Indexes in SQL Server play a crucial role in enhancing query speed by enabling quicker data retrieval and minimizing the need for full-table scans. Selecting the right type of index and adhering to best practices, including regular maintenance and thorough understanding of database access patterns, are vital for extracting maximum benefits from indexing. 

How to identify duplicate indexes along with columns in SQL Server?

To get the key column list from indexes that are duplicates in SQL Server, you can use the following query:

use databasename
go

WITH DuplicateIndexes AS (
    SELECT 
        i.OBJECT_ID,
        i.index_id
    FROM 
        sys.index_columns ic
    JOIN 
        sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                     AND i.index_id = ic.index_id
    WHERE 
        i.type_desc <> 'HEAP' 
		AND OBJECT_NAME(i.OBJECT_ID) NOT LIKE '%sys%' --excluding system tables
    GROUP BY 
        i.OBJECT_ID, i.index_id
    HAVING 
        COUNT(*) > 1 -- to check duplicates 
)

SELECT 
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    OBJECT_NAME(ic.OBJECT_ID) AS TableName,
    i.name AS IndexName,
    STRING_AGG(c.name, ', ') WITHIN GROUP (ORDER BY ic.key_ordinal) AS IndexedColumns
FROM 
    sys.index_columns ic
JOIN 
    sys.indexes i ON i.OBJECT_ID = ic.OBJECT_ID 
                 AND i.index_id = ic.index_id
JOIN 
    sys.objects o ON o.OBJECT_ID = ic.OBJECT_ID
JOIN 
    sys.columns c ON ic.OBJECT_ID = c.OBJECT_ID 
                 AND ic.column_id = c.column_id
JOIN 
    DuplicateIndexes di ON di.OBJECT_ID = ic.OBJECT_ID 
                        AND di.index_id = ic.index_id
GROUP BY 
    o.schema_id, ic.OBJECT_ID, i.name;
  

This query first identifies the indexes that are duplicates, and then retrieves the table name, index name, and the key column list for each duplicate index.

Execute this query in your SQL Server management tool to get the key column list from indexes that are duplicates in your database.

How to Review transaction order and lock acquisition in SQL Server

In SQL Server, you can review the transaction order and lock acquisition by analysing the queries and transactions that are being executed against the database. Here are some approaches to review transaction order and lock acquisition:

1. Transaction isolation levels:

   • Review the transaction isolation levels used in your database transactions. Isolation levels such as Read Uncommitted, Read Committed, Repeatable Read, and Serializable can impact the order of lock acquisition and the behaviour of concurrent transactions.

2. Query execution plans:

   • Use SQL Server Management Studio (SSMS) or other database management tools to analyse the query execution plans for your transactions.
   • The execution plans can provide insights into the order in which data is accessed and the types of locks acquired during query execution.

3. Locking and blocking:

   • Monitor and analyse the locking and blocking behaviour of concurrent transactions using tools like SQL Server Profiler, Extended Events, or dynamic management views (DMVs) such as sys.dm_tran_locks and sys.dm_os_waiting_tasks.
   • Identify instances of blocking and analyse the lock types and resources involved to understand the order of lock acquisition.

4. Transaction log and history:

   • Review the transaction log and history to understand the sequence of transactions and their impact on lock acquisition.
   • SQL Server's transaction log and history can provide valuable information about the order in which transactions are executed and their associated locks.

By using these approaches, you can gain insights into the transaction order and lock acquisition behaviour in SQL Server, which can help in identifying potential issues related to deadlocks, blocking, and overall transaction concurrency.

What is deadlock priority and how to address in SQL Server

In SQL Server, deadlock priority is a mechanism that allows you to influence the selection of the transaction that will be chosen as the deadlock victim when a deadlock occurs. You can use deadlock priority to specify the importance of individual transactions in the event of a deadlock.

To address deadlock priority in SQL Server, you can consider the following:

1. Setting deadlock priority:
   • You can use the SET DEADLOCK_PRIORITY statement to specify the priority of a session or transaction.
   • The priority levels range from -10 to 10, where -10 is the lowest priority and 10 is the highest.
   • By setting the deadlock priority, you can influence the selection of the victim transaction when a deadlock occurs.

Here's an example of how to set the deadlock priority for a session:

SET DEADLOCK_PRIORITY LOW; -- Set the deadlock priority to low
  

2. Adjusting transaction logic:

   • Design your transaction logic to handle the potential impact of being chosen as the deadlock victim based on the assigned deadlock priority.
   • Consider implementing retry logic for transactions with lower deadlock priority after being chosen as the deadlock victim.

3. Analyzing and tuning deadlock priority:

   • Evaluate the impact of deadlock priority settings on your application's transactions and overall performance.
   • Tune the deadlock priority based on the specific requirements and characteristics of your application to effectively manage deadlocks.

It's important to carefully consider the implications of deadlock priority settings in SQL Server and design your transaction logic to handle deadlock situations appropriately. Understanding the behavior of deadlock priority in SQL Server is crucial for effectively addressing and managing deadlocks.

How to implement retry logic for DB Transactions

In SQL Server, you can implement retry logic for transactions using T-SQL and error handling. Here's an example of how you can create a stored procedure that includes retry logic for handling deadlock errors:

CREATE PROCEDURE usp_RetryTransaction
AS
BEGIN
    DECLARE @retryCount INT = 0
    DECLARE @maxRetries INT = 3

    WHILE @retryCount < @maxRetries
    BEGIN
        BEGIN TRY
            BEGIN TRANSACTION
            -- Your transactional logic goes here
            COMMIT TRANSACTION
            RETURN
        END TRY
        BEGIN CATCH
            IF ERROR_NUMBER() = 1205  -- Deadlock error number
            BEGIN
                ROLLBACK TRANSACTION
                SET @retryCount = @retryCount + 1
                WAITFOR DELAY '00:00:01'  -- Wait for 1 second before retrying
            END
            ELSE
            BEGIN
                -- Handle other types of errors
                THROW
            END
        END CATCH
    END
    -- If the maximum number of retries is reached, handle the situation as needed
    -- For example, raise an error or log the issue
END
  

In this example, the stored procedure attempts the transaction logic within a retry loop, and if a deadlock error (error number 1205) occurs, it rolls back the transaction, increments the retry count, and waits for a short duration before retrying the transaction. If the maximum number of retries is reached, you can handle the situation as needed based on your application's requirements.

You can then call this stored procedure whenever you need to perform a transaction with retry logic for deadlock handling.

How to find a view in database where its used in SQL Server

To find where a specific view is used in a SQL Server database, you can query the system catalog views. Here's a query to achieve this:

SELECT 
    referencing_schema_name, 
    referencing_entity_name
FROM 
    sys.dm_sql_referencing_entities('YourSchema.YourView', 'OBJECT');
  

Replace YourSchema with the schema of your view and YourView with the name of the view you want to find. This query will return the schema and name of the objects that reference the specified view.

Execute this query in your SQL Server management tool to find where a specific view is used in your database.

Hope this help!!

Best YouTube channels for Data Science

❯ Python ➟ Corey Schafer

❯ SQL ➟ Joey Blue

❯ Data Analyst ➟ AlexTheAnalyst

❯ Tableau ➟ Tableau Tim

❯ PowerBI ➟ Guy in a Cube

❯ MS Excel ➟ ExcelIsFun

❯ Machine Learning ➟ sentdex

❯ Mathematics ➟ 3Blue1Brown

❯ And the winner is  ➟ Socratica, who does educational vidoes on math, science and computers

Top 10 FREE courses to learn SQL in 2023

FREE Certificates

❯ DBMS, SQL http://learn.saylor.org/course/view.php?id=93

❯ SQL http://openclassrooms.com/en/courses/2071486-retrieve-data-using-sql

❯ MySQL http://coursera.org/learn/database-structures-and-management-with-mysql

Paid Certificate

❯ SQL Server http://learn.microsoft.com/training/paths/program-transact-sql/ *

Free Badge

❯ PostgreSQL http://coursera.org/specializations/postgresql-for-everybody

Paid Certificate

❯ Oracle http://mygreatlearning.com/academy/learn-for-free/courses/oracle-sql

❯ RDBMS, SQL http://online.stanford.edu/courses/soe-ydatabases0005-databases-relational-databases-and-sql

Paid Certificate

❯ RDBMS, SQL http://udacity.com/course/database-systems-concepts-design—ud150

No Certificate

❯ PostgreSQL http://freecodecamp.org/learn/relational-database/

❯ Big Query http://kaggle.com/learn/intro-to

Happy SQL

How to implement impersonation in SQL Server

To implement impersonation in SQL Server, you can follow these steps:

1. Create a Login:
First, create a SQL Server login for the user you want to impersonate. Use the `CREATE LOGIN` statement to create the login and provide the necessary authentication credentials.

Example:

CREATE LOGIN [ImpersonatedUser] WITH PASSWORD = 'password';
  

2. Create a User:
Next, create a user in the target database associated with the login you created in the previous step. Use the `CREATE USER` statement to create the user and map it to the login.

Example:  

CREATE USER [ImpersonatedUser] FOR LOGIN [ImpersonatedUser];
  

3. Grant Permissions:
Grant the necessary permissions to the user being impersonated. Use the `GRANT` statement to assign the required privileges to the user.

Example:

GRANT SELECT, INSERT, UPDATE ON dbo.TableName TO [ImpersonatedUser];
  

4. Impersonate the User:
To initiate impersonation, use the `EXECUTE AS USER` statement followed by the username of the user you want to impersonate. This will switch the execution context to the specified user.

Example:

EXECUTE AS USER = 'ImpersonatedUser';
  

5. Execute Statements:
Within the impersonated context, execute the desired SQL statements or actions. These statements will be performed with the permissions and privileges of the impersonated user.

Example:

SELECT * FROM dbo.TableName;
-- Perform other actions as needed
  

6. Revert Impersonation:
After completing the necessary actions, revert back to the original security context using the `REVERT` statement. This will switch the execution context back to the original user.

Example:

REVERT;
  

By following these steps, you can implement impersonation in SQL Server. Ensure that you grant the appropriate permissions to the user being impersonated and consider security implications when assigning privileges.

Here is the full syntax:

EXECUTE AS LOGIN = 'DomainName\impersonatedUser'
EXEC  uspInsertUpdateGridSettings @param1, @param2
REVERT;
  

Additionally, be mindful of auditing and logging to track and monitor impersonated actions for accountability and security purposes.

Find tables or procedures that are associated in SQL Jobs via Query

Recently we need to look for a procedure where we are using in SQL Jobs. There is no easy way to find unless you script all jobs and find in the script.

But there is some easy way to find it using below query. You could also might have similar ask to find a procedure or table that you might have used in SQL Jobs in any of those steps. It could be any string like comment, procedure, function or table, this below query works.

USE msdb
GO

SELECT [sJOB].[job_id] AS [JobID]
	,[sJOB].[name] AS [JobName]
	,step.step_name AS JobStepName
	,step.command AS JobCommand
	,[sJOB].enabled AS ActiveStatus
FROM [msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN [msdb].dbo.sysjobsteps step ON sJOB.job_id = step.job_id
WHERE step.command LIKE '%uspPopulateAggregatorUsageData%' ----You can change here what you are searching for
ORDER BY [JobName]
  

Thank you

How to get comma separated values from SQL

There are few types where you can get comma separated values form SQL SERVER using SQL

1. XML PATH method:

SELECT 
   STUFF((SELECT ', ' + column_name
          FROM table_name
          WHERE conditions
          FOR XML PATH('')), 1, 2, '') AS csv_values;
  

2. COALESCE and FOR XML method:

SELECT 
   STUFF((
      SELECT ', ' + column_name
      FROM table_name
      WHERE conditions
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS csv_values;
  

3. FOR XML PATH method

SELECT 
   STUFF((SELECT ',' + column_name
          FROM table_name
          WHERE conditions
          FOR XML PATH('')), 1, 1, '') AS csv_values;
  

In these examples, replace column_name with the actual column name and table_name with the appropriate table name. Customize the WHERE clause to filter the desired rows if necessary.

When executing these queries, a single row with a single column will be returned, containing the comma-separated values from the specified column. Please note that the XML-related methods convert the values to XML and then manipulate them, resulting in a string of comma-separated values.

Hope this helps!!

How to Find Tables that Contain a Specific Column in SQL Server?

Basic concept to understand about SQL Server is that of catalog views, which are effectively database tables (catalogs in this case) that display system-wide information about the SQL Server Database Engine.

All catalog views are accessed via a SELECT SQL statement FROM a specific catalog within the sys. namespace.
For example, the following statement can be used to view information about all database tables in the system via the sys.tables catalog

use mobility
go
select  
        s.[name]            'Schema',
        t.[name]            'Table',
        c.[name]            'Column',
        d.[name]            'Data Type',
        c.[max_length]      'Length',
        d.[max_length]      'Max Length',
        d.[precision]       'Precision',
        c.[is_identity]     'Is Id',
        c.[is_nullable]     'Is Nullable',
        c.[is_computed]     'Is Computed',
        d.[is_user_defined] 'Is UserDefined',
        t.[modify_date]     'Date Modified',
        t.[create_date]     'Date created'
from        sys.schemas s
inner join  sys.tables  t
on s.schema_id = t.schema_id
inner join  sys.columns c
on t.object_id = c.object_id
inner join  sys.types   d
on c.user_type_id = d.user_type_id
where c.name like '%ProjectManagerID%'
  

Hope this helps!!

Use RAND() in User Defined Function

Here is the issue,  you cannot call a non-deterministic function from inside a user-defined function.

So there is a workaround, By creating a view to return RAND(), call RAND() function inside a view and use that view inside your function, something like below.

CREATE VIEW ReturnRANDValue
AS
SELECT RAND() AS Value
  

Here is the function where we call above view inside this function

CREATE FUNCTION [dbo].[ReturnMobileNotificationCount] (
	@MobilityOrderID INT,
	@MobilityOrderItemID INT,
	@LineStatusMasterID INT,
	@LineSubStatusMasterID INT
	)
RETURNS BIT
AS
BEGIN
	DECLARE @LineSubStatusMatch BIT = 0

	SELECT MobilityOrderID,
		MobilityOrderItemID,
		LineStatusMasterID,
		LineSubStatusMasterID,
		HistoryID,
		ROW_NUMBER() OVER (
			ORDER BY (
					SELECT Value
					FROM ReturnRANDValue
					) DESC
			) SerialNo
	FROM MobileNotificationCriteriaHistory MNC(NOLOCK)
	WHERE MobilityOrderID = @MobilityOrderID
		AND MobilityOrderItemID = @MobilityOrderItemID
	ORDER BY HistoryID DESC

	RETURN @LineSubStatusMatch
END
  

How to check active transactions in SQL Server

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database.

Some times, if there are any issues we will get into this deadlock stage and we don't get any responses from database. In those cases, if you want know what are active connections going on at that point of time, will help identifying issue.

if you want to know more details about active sessions like session ID, Host Name, Login Name, Transaction ID, Transaction Name, Transaction Begin Time,Database ID,Database Name etc.

Use the below query for details,

SELECT
trans.session_id AS [SESSION ID],
execSession.host_name AS [HOST NAME],login_name AS [Login NAME],
trans.transaction_id AS [TRANSACTION ID],
tas.name AS [TRANSACTION NAME],tas.transaction_begin_time AS [TRANSACTION 
BEGIN TIME],
tds.database_id AS [DATABASE ID],DBs.name AS [DATABASE NAME]
FROM sys.dm_tran_active_transactions tas
JOIN sys.dm_tran_session_transactions trans
ON (trans.transaction_id=tas.transaction_id)
LEFT OUTER JOIN sys.dm_tran_database_transactions tds
ON (tas.transaction_id = tds.transaction_id )
LEFT OUTER JOIN sys.databases AS DBs
ON tds.database_id = DBs.database_id
LEFT OUTER JOIN sys.dm_exec_sessions AS execSession
ON trans.session_id = execSession.session_id
WHERE execSession.session_id IS NOT NULL
  

What is the Query to display the failed SQL Jobs

We can get this information by the standard reports available in SQL Server, right click on SQL server agent> Standard reports and select the desired report to see Job History

It can be frustrating to find recently failed jobs in the job history in SQL Server Management Studio. A quicker way to do it is to just run a query to see what jobs have failed recently.

Below Query will give you list of failed jobs, you can also filter by name.

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0
--and name = 'jobName'
order by run_date desc
  

If you want to check results based on data range, you can use below query to find desired results.

-- Variable Declarations 
DECLARE @FinalDate INT;
SET @FinalDate = CONVERT(int
    , CONVERT(varchar(10), DATEADD(DAY, -2, GETDATE()), 112)
    ) -- last two days date as Integer in YYYYMMDD format

-- Final Logic 

SELECT  j.[name],  
        s.step_name,  
        h.step_id,  
        h.step_name,  
        h.run_date,  
        h.run_time,  
        h.sql_severity,  
        h.message,   
        h.server  
FROM    msdb.dbo.sysjobhistory h  
        INNER JOIN msdb.dbo.sysjobs j  
            ON h.job_id = j.job_id  
        INNER JOIN msdb.dbo.sysjobsteps s  
            ON j.job_id = s.job_id 
                AND h.step_id = s.step_id  
WHERE    h.run_status = 0 -- Failure  
         AND h.run_date > @FinalDate  
ORDER BY h.instance_id DESC;
  

Hope this helps!

How to Get the Last Day of the Month in T-SQL

We’ll use the function EOMONTH() to find the last day of the month.

DECLARE @fromdate DATETIME, @EOMMonthdate DATETIME

SET @fromdate = '11/01/2022'

SET @EOMMonthdate = EOMONTH(@fromdate,0) -- for current month

SELECT @EOMMonthdate as lastdayOfthemonth
  

If you want to return the last day of the second, third, etc. month from a given date, use EOMONTH()’s optional second argument: the number of months to add. Look at the examples for last day of next month or last day of the previous month.

DECLARE @fromdate DATETIME, @EOMMonthdate DATETIME

SET @fromdate = '11/01/2022'

SET @EOMMonthdate = EOMONTH(@fromdate,1) -- for next month last day

SELECT @EOMMonthdate as nextMonthLastDayOfthemonth

SET @EOMMonthdate = EOMONTH(@fromdate,-1) -- for previous month last day

SELECT @EOMMonthdate as previousMonthLastday
  

Hope this helps!!

How to Get Columns details from SQL Tables

Here is how you can get column names from specified table

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns 

WHERETABLE_NAME = 'MobilityOrders'

Here is how you can get column count from specified table

SELECT COUNT(COLUMN_NAME) as COUNT FROM INFORMATION_SCHEMA.Columns 

WHERE TABLE_NAME = 'MobilityOrders'

Here is how you can get column count from temp table

 SELECT COUNT(*) as Cnt FROM tempdb.sys.columns

 WHERE object_id = object_id('tempdb..#temp2')

Hope this helps 😀

How to remove special characters from a string using a function with RegEx

Here is how you can get remove special characters from a string in SQL

-- SELECT dbo.[RemoveCharSpecialSymbolValue] ('naga3fg@#sai') 
CREATE FUNCTION [dbo].[RemoveCharSpecialSymbolValue](@str VARCHAR(500))  
RETURNS VARCHAR(500)  
BEGIN  
DECLARE @startingIndex int  
SET @startingIndex=0  
WHILE 1=1  
	BEGIN  
	SET @startingIndex= patindex('%[^0-9a-zA-Z]%',@str)  
	IF @startingIndex <> 0  
		BEGIN  
			SET @str = replace(@str,substring(@str,@startingIndex,1),'')  
		END  
	ELSE BREAK;  
END  
RETURN @str  
END   

Hope this helps 😀

How to remove alpha numeric characters from a string using function with RegEx

Here is how you can get alpha numeric characters from a string in SQL

-- SELECT dbo.[RemoveCharSpecialSymbolIntValue] ('naga3@#sai') 
CREATE FUNCTION [dbo].[RemoveCharSpecialSymbolIntValue](@str VARCHAR(500))  
RETURNS VARCHAR(500)  
BEGIN  
DECLARE @startingIndex int  
SET @startingIndex=0  
	WHILE 1=1  
	BEGIN  
		SET @startingIndex= patindex('%[^0-9.]%',@str)  
		IF @startingIndex <> 0  
		BEGIN  
			SET @str = replace(@str,substring(@str,@startingIndex,1),'')  
		END  
		ELSE BREAK;  
	END  
	RETURN @str  
END 

Hope this helps 😀

How to remove ASCII Characters from a string using function

Here is how you can remove ASCII characters from a string in SQL

CREATE FUNCTION [dbo].RemoveASCIICharactersInRange(@InputString VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
    IF @InputString IS NOT NULL
    BEGIN
      DECLARE @Counter INT, @TestString NVARCHAR(40)

      SET @TestString = '%[' + NCHAR(0) + NCHAR(1) + NCHAR(2) + NCHAR(3) 
      + NCHAR(4) + NCHAR(5) + NCHAR(6) + NCHAR(7) + NCHAR(8) + NCHAR(11) 
      + NCHAR(12) + NCHAR(14) + NCHAR(15) + NCHAR(16) + NCHAR(17) 
      + NCHAR(18) + NCHAR(19) + NCHAR(20) + NCHAR(21) + NCHAR(22) 
      + NCHAR(23) + NCHAR(24) + NCHAR(25) + NCHAR(26) + NCHAR(27) 
      + NCHAR(28) + NCHAR(29) + NCHAR(30) + NCHAR(31) 
      + NCHAR(127)+ NCHAR(160)+ ']%'

      SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)

      WHILE @Counter <> 0
      BEGIN
        SELECT @InputString = STUFF(@InputString, @Counter, 1, '')
        SELECT @Counter = PATINDEX (@TestString, @InputString COLLATE Latin1_General_BIN)
      END
    END
    RETURN(@InputString)
END

Hope this helps 😀