Stuff and Replace Function in SQL Server : Detailed Description

(This is a guest blog post by Daniel Jones (li | t | fb). Daniel is a SQL Server DBA and contributor at SQL Tech Tips. Having 2 + years of experience in SQL recovery and system infrastructure.)

In SQL Server, STUFF and REPLACE functions are used to replace the characters in a string. Both functions play an important role in Transact-SQL with performing the distinct functionality. In the following section, we are going to discuss about Stuff and Replace function in detail.

STUFF Function in SQL Server

In SQL Server, the STUFF function is utilized to replace the part of the string with some another string. Moreover it can remove the specified length of the characters in the first string and after that, insert the second string into the first string at the starting position.

Syntax of STUFF Function

STUFF ( character_expression , start , length , replaceWith_expression )

Parameters

    • character_expression

This argument can be a constant, variable or may be the column of either binary or character data

    • start

It is an integer value that indicates the location from where to begin the deletion and insertion as well. In case, if start or length parameters may be negative then, it will return a null string. Besides, if start is used longer than the first-character_expression, again null string is returned. It also can be of bigint type.

    • length

Also, it is an integer that denotes the number of characters to remove. If using length longer than the first character_expression, then, deletion happens to the last character within the last character_expression. It could be of type bigint.

    • replaceWith_expression

This attribute represents same as character_expression. Even it may be a variable, constant, or columns of character or binary data. Even this expression can replace length characters from character_expression in the beginning at start

Return Types

In case, if character_expression is one of the supported character data, returns the character type data. Otherwise, gives binary data if character_expression is a single supported binary data types.

Remarks

In the above parameters, if the length or start integer is negative, or if the start position is larger than the length of the primary string, then, returns the null string. Although, returns null value if the starting position is zero. If the length is longer than the first string, it has decided to remove the first character data type in the first string.

Replace Function in SQL Server

As the above function name indicates, the REPLACE function in SQL server allows to replace all the occurrences of a specified string value with another set of the string value.

Syntax of REPLACE Function

REPLACE ( string_expression , string_pattern , string_replacement )

Parameters

    • string_expression

This string expression to be searched. Either it can be of character data or binary data type.

    • string_pattern

It can also be of a binary as well as character data type. In fact, it cannot be an empty string and may not exceed the maximum number of bytes that is suitable on a page.

    • string_replacement

A string_replacement argument can be of any data type either character or binary as well.

Return Types

If one of the input parameters is of nvarchar type then, returns nvarchar value; else, REPLACE gives varchar value.

In addition, if any individual argument is NULL then, returns the NULL value.

Moreover, if string-expression cannot be of type varchar or nvarchar, then, REPLACE function truncates the return range at 8,000 bytes. To provide values larger than 8,000 bytes, even string-expression may be cast explicitly to a greater value data type.

Remarks

REPLACE function performs the comparison of the input, which is based on collation. In a specified collation performing a comparison, also users can use COLLATE to apply collation to the desired input.

Char(zero) is an undefined character in collation and unable to include in REPLACE function.

Conclusion

STUFF and REPLACE are two major SQL Server functions that have discussed in above section. Moreover, to understand the various aspects of both the functions, we have presented the syntax and parameters in detail.

How to Create LOGON Trigger in SQL Server

Most recently I had to create a SQL Logon trigger that set a limit to sessions a user could open in SQL Server.

For example, I log into SSMS and cannot open more than 10 sessions. This logon trigger will enforce that I don’t exceed 10 concurrent sessions.

The script below is what I came up with. It’s fairly straight-forward. At the bottom of the query you will see 2 SELECT statements:

  1. sys.triggers – You will find database level triggers here.
  2. sys.server_triggers – You will find server level triggers here.

Feel free to modify the script below to suit your specific needs.

CREATE TRIGGER limit_sessions_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
DECLARE @ErrorText varchar(128)

SET @ErrorText = 'Cannot exceed 20 sessions. Close out older sessions to open newer ones.
    				If you need help, contact your Database Admin.';

IF ORIGINAL_LOGIN()= 'JohnDoe' AND
    (SELECT COUNT(*) FROM sys.dm_exec_sessions
            WHERE is_user_process = 1 AND
                original_login_name = 'JohnDoe') > 20
	BEGIN
		PRINT @ErrorText
		ROLLBACK;
	END
END;
GO

--Instance/Server level Triggers
SELECT *
FROM sys.server_triggers


--Database Level Triggers
SELECT *
FROM sys.triggers

--Delete Server level Trigger
DROP TRIGGER limit_sessions_trigger
ON ALL SERVER;
GO

INDEX REORGANIZE vs REBUILD in SQL Server

This blog post is meant to help distinguish the differences between REORGANIZE and REBUILD when it comes to index fragmentation. If you are serious about learning more of the internals of how indexing works in SQL Server, watch Kimberly Tripp’s (blog | twitter) video called, “Index Internals” here (open link and scroll down to “Index Internals”).

INDEX REORGANIZE vs REBUILD in SQL Server

REORGANIZE – This is the lightweight version. It reorders the leaf pages of the index in a logical order. It is done ONLINE and doesn’t ROLLBACK if interrupted. If for some reason the REORGANIZE operation is interrupted, it can stop at the last operation and continue where it left off. The fill-factor is reset to the previously set fill factor. REORGANIZE is single threaded only and only requires 8 KB of free space to run.

ALTER INDEX [PK_Index_Name] on [MyTable] REORGANIZE;
GO

REBUILD – Creates new indexes and you can define the fill factor. Can be done ONLINE (with SQL Server Enterprise Edition) or OFFLINE. REBUILD is a fully transactions operation. If the operation is interrupted it will have to ROLLBACK the transaction and that can take a while depending how large your indexes are, etc.

ALTER INDEX [PK_Index_Name] ON [MyTable] REBUILD;
GO

For all the specific ALTER commands that you can use with REORGANIZE / REBUILD, visit the MSDN article here.

How to Find SQL Server Edition Information

Most recently I had to figure out the edition (Enterprise, Standard, etc.) of SQL Server running on a specific production database server. The client wanted to know to what extent was the database server STIG’d.

I initially ran SELECT @@VERSION but that doesn’t bring back the edition information. So I ran:

SELECT SERVERPROPERTY(‘Edition’)

…and that returned the edition information. I also included the @@VERSION information as that brings back what Service Pack level is applied (see snapshot below).

Find Edition of SQL Server

After seeing this database server was running on SQL Server Standard Edition, I let the client know that Common Criteria Compliance and TDE (Transparent Database Encryption) cannot be enabled as those are Enterprise features.

SQL Server DDL vs DML

For the longest time I’ve had a hard time remembering the difference between SQL Server DDL vs DML statements. I had a hard time remembering what statement fell under what category. Was INSERT, UPDATE, DELETE a DML command or DDL? What about CREATE / ALTER?  It was all confusing to me.

I finally figured out a way to remember the difference. Before I tell you my “secret”, here’s a quick explanation of each.

DDL

DDL, or Data Definition Language, consists of the following commands:

CREATE, ALTER and DROP

Below is an image taken straight from MSDN that includes all the DDL statements.

DDL vs DML SQL Server DDL

DML

DML, or Data Manipulation Language, consists of the following commands:

INSERT, UPDATE, DELETE, SELECT…

Below is an image taken straight from MSDN.

DDL vs DML SQL Server

How did I memorize the difference? Well, for DML the ‘M’ is for ‘manipulation’, so I automatically associate “INSERTING” or “UPDATING” data as “manipulating” data. Once I understood that, DDL became easy to remember and associated it with CREATE, ALTER, DROP. By memorizing one of them you automatically know the second. :)

How to Stop & Delete a SQL Server Trace

There were a few times where I had to delete/close an existing SQL Server Trace and create a new one. It’s a 2-step process to completely remove a SQL Server Trace. First, you stop the trace. Second, you delete/close it.

sp_trace_setstatus takes 2 parameters, trace id and status.

The trace id is the id you find by doing a select * from sys.traces

The status is either a 0, 1 or 2:

0 stops the trace, 1 starts the trace, 2 closes the specified trace and deletes its definition from the server. (see MSDN image screenshot below)

Status for sp_trace_setstatus(According to Microsoft, “A trace must be stopped first before it can be closed.”)

Let’s walk through the process.

First, do a select * from sys.traces to get the trace ID. Second, stop the trace by executing sp_trace_setstatus trace_id, 0 then, close/delete the trace by executing sp_trace_setstatus trace_id, 2. (see below screenshot for example)

sp_trace_setstatus screenshot

Hope that helps!

How to Find the Space Used in Your Data Files

I recently had to resize a data file on of my production SQL Server databases and needed to know an appropriate size to resize it to. So, I created this simple script that queries sys.database_files and brings back the File ID, File Location, File Name, Original Size, Space Used and Space Left.

SELECT sdb.file_id as [File ID],
sdb.physical_name [File Location],
sdb.name [File Name],
CONVERT(numeric(10,2),ROUND(sdb.size/128.,2)) AS [Original Size in MB],
CONVERT(numeric(10,2),ROUND(FILEPROPERTY(sdb.name, 'SpaceUsed')/128.,2)) AS [Space Used in MB], 
CONVERT(numeric(10,2),ROUND((sdb.size - FILEPROPERTY(sdb.name, 'SpaceUsed'))/128.,2)) AS [Space Left in MB]
FROM sys.database_files sdb

Here is a sample result of the output:

SCRIPT-Space-Used-vs-Space-Left-Datafiles

 

Msg 5041, Level 16, State 1, Line 1 – Resize Datafile

Recently, I wanted to resize the tempdb file size to 2 MB so I ran the below script:

ALTER DATABASE tempdb MODIFY FILE (name = tempdb, size = 2MB);
GO

and got the below error:

Msg 5041, Level 16, State 2, Line 1
MODIFY FILE failed. File ‘tempdb’ does not exist. (screenshot below)

Msg 5041, Level 16, State 2, Line 1

After some research I found out that the file name that I was using was incorrect. To find the correct logical name, you have to right-click the database, go to Properties —> Files and you’ll see it under Logical Name (see green arrow in screenshot image below). In my case it was “tempdev” NOT “tempdb” (which is what I used and got the error).

Msg 5041, Level 16, State 2, Line 1

Once I put that name in the script it ran perfectly fine. Make sure to restart SQL Service in order to see the change.

ALTER DATABASE tempdb MODIFY FILE (name = tempdev, size = 2MB);
GO