Author: Latheesh NK

Curious case of varchar to uniqueidentifier in SQL Server

Converting a datatype to another datatype is a very common requirement in real world and we usually do it with CAST or CONVERT (eg. int to char or varchar etc).

There are two types of conversions – implicit and explicit.

Explicit conversions are the conversions done by developers using SQL server functions like cast/convert etc and Implicit conversions are mainly managed by SQL Server internally. If you want to see these types of conversions, you can check for IMPLICIT_CONVERT in the execution plan generated by SQL Server.

Today, we are going to see a curious case of an implicit conversion – varchar to uniquidentifier.

SQL Server does a implicit conversion from varchar to uniqueidentifier by default as below:

Declare @varchar nvarchar(MAX), @uniqueidentifier uniqueidentifier
Set @varchar ='f124656c-136b-4beb-ab3a-b348053f898a,7f0b0dd5-22bc-421b-9416-3a7c24146a98'

Set @uniqueidentifier = @varchar
Select @uniqueidentifier

Now, if you look at the results, you can see the first 36 character is being converted implicitly. Since its a trivial, the operator may not be able to see in your execution plan.

The word of caution

Sometimes, if we are assigning more values/characters in the varchar variable, it would ONLY pick the first 36 characters and ignoring the rest characters by default. This default character of the conversion may end up with a wrong results, however, there would not be any sign of error! So beware of this characteristic while you writing a code.

Generate Random characters in SQL Server

Here is a script to generate randon characters in SQL Server

It also allows you to generate the characters in random as well as required format. There are four types of format the function supports:

1. ‘Proper’ – proper name form (i.e. Xxxxx)
2. ‘Upper’ – all uppercase (i.e. XXXXX)
3. ‘Lower’ – all lowercase (i.e. xxxxx)
5. ‘Mixed’ – randomly mixed case (i.e. xXxxxXXxx)

Create View dbo.Q_Random as 
	Select Rand() as [RandomNumber]
Create Function dbo.fn_GenerateText 
				(@Length integer, @Format varchar(6) = 'Mixed')
Returns varchar(256)

--	Formats:	
--		'Proper' - proper name form (i.e. Xxxxx)
--		'Upper'  - all uppercase (i.e. XXXXX)
--		'Lower'  - all lowercase (i.e. xxxxx)
--		'Mixed'  - randomly mixed case (i.e. xXxxxXXxx)
--		 null    - randomly mixed case (i.e. xXxXxxxxxX)

	@RandomValue             varchar(256), 
	@Count                   integer,
	@RandomNumber            float, 
	@RandomNumberInteger     integer, 
	@CurrentCharacter        char(1),
	@ValidCharactersLength   integer,
	@ValidCharacters         varchar(255) 

Set @RandomValue = '';

If (@Length = 0) 
	Goto ReturnData 

If (@Format = 'Mixed') 
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789'; 
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';

Set @ValidCharactersLength = Len(@ValidCharacters); 
Set @CurrentCharacter      = ''; 
Set @RandomNumber          = 0; 
Set @RandomNumberInteger   = 0; 

Set @Count = 1; 

While @Count <= @Length 
	Set @RandomNumber = (Select RandomNumber from Q_Random); 

	Set @RandomNumberInteger = Convert(integer, ((@ValidCharactersLength - 1) * @RandomNumber + 1)); 
	Set @CurrentCharacter = SubString(@ValidCharacters, @RandomNumberInteger, 1); 

	Set @RandomValue = @RandomValue + @CurrentCharacter; 

	Set @Count = @Count + 1; 

If @Format = 'Lower' 
	Set @RandomValue = Lower(@RandomValue); 

If @Format = 'Upper' 
	Set @RandomValue = Upper(@RandomValue); 

If @Format = 'Proper' 
	Set @RandomValue = Upper(Left(@RandomValue, 1)) + Substring(Lower(@RandomValue), 2, (@Length - 1));  

--	... or the default  gives random `casing`, and 'Mixed' gives random alphanumeric `casing`


Return @RandomValue 


Sample Executions:

Select dbo.fn_GenerateText(10,’Lower’)
Select dbo.fn_GenerateText(10,’Upper’)
Select dbo.fn_GenerateText(10,’Mixed’)–default value
Select dbo.fn_GenerateText(10,’Proper’)

How to change mdf and ldf files path to instance default path in SQL Server

Here is a code snippet to change the mdf and ldf file path of an existing database to instance default path in SQL Server.
If you want to move to fixed path, then you can change the script as required.

1. The script is mainly written for SQL Server 2016, for other version, please change as required.
2. The script will not execute or change the path automatically. It just prints the statements to execute. The user can validate the scripts and do the action as required. This is to prevent any unexpected events.

--Print 'Collect Default Data & Log information'
declare @DefLOG nvarchar(512)
declare @DefDATA nvarchar(512)

--Prepare the SQL statements for remapping
if (Cast(SERVERPROPERTY('Productmajorversion') as varchar(2))='13')

Set @DefLog = Cast( Serverproperty('InstanceDefaultLogPath') as varchar(512))
Set @DefDATA = Cast( Serverproperty('InstanceDefaultDataPath') as varchar(512))

--Print 'Move all datafiles'
declare cur_move cursor for
select --DB_NAME(dbid),name,filename, reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1)),
'ALTER DATABASE ['+DB_NAME(dbid)+'] MODIFY FILE ( NAME = '''+name+''' , FILENAME = '''+@DefDATA+reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1))+''' )'
from sys.sysaltfiles where dbid <32000
and groupid=1 and dbid>5 and charindex(@DefDATA,filename)=0
union all
select --DB_NAME(dbid),name,filename, reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1)),
'ALTER DATABASE ['+DB_NAME(dbid)+'] MODIFY FILE ( NAME = '''+name+''' , FILENAME = '''+@DefLOG+reverse(SUBSTRING(reverse(filename),1,CHARINDEX('\',reverse(filename))-1))+''' )'
from sys.sysaltfiles where dbid <32000 and charindex(@DefDATA,filename)=0
and groupid=0 and dbid>5

declare @move varchar(max)
open cur_move
fetch next from cur_move into @move

while @@fetch_status=0

print @move
--Exec (@move)

fetch next from cur_move into @move
close cur_move
deallocate cur_move

Select 'This script supports SQL Server 2016 or later! For older version, you need to below to identify the default path, otherwise all are same.'
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @DefLOG OUTPUT
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @DefDATA OUTPUT

Dark theme in Sql Server Management Studio

Many people prefer dark theme for computer displays. It actually enhances the visual ergonomics by reducing eye strain and providing comfort of use at all conditions.Another important benefit, it conserves battery power, there by enabling device to run longer period of time.

Please refer this link for more details.

Now, let us take a look at the fact that dark theme can be applied to SSMS and how do we do and what are implications.

Writing this blog, I am using SSMS 18.2 version, the latest by now. In future, the facts may change if MS decided to make a change.

Do we have Dark theme by default available in SSMS 18.2?

The answer is NO. We do not have an option to change the SSMS into a dark theme by default.However, it provides a way for us to change to “Blue”, “Blue (Extra Contrast)” and “Light” as below.

How do we make SSMS in Dark theme?

There are two ways from my analysis:

1. Custom your SSMS window using Tools -> Options -> Environment -> Fonts and Colors.

The challenge here is to customize each and every items from the list and provide the color combinations. Honestly, this is something very challenging for me to identify the better combinations. There is a blog written by Aaron Betrand on the same, and its really helpful for the basic settings.

2. Change the SSMS settings

There is a file called ssms.pkgundef in the path – “Program Files (x86)\Microsoft SQL Server Management Studio 18\Common7\IDE“. This file is where you will place registry entries that are to be removed during setup of your application. If you look at the content of this files, you can see the below code:

// Remove Dark theme

The above code, actually removes the dark options from SSMS while setting up of SSMS. To enable dark theme for SSMS, you can simply comment the code as below and it will give the option of “Dark” in Tool -> Option -> General -> Color Theme as below.

Please be aware, this is a hack or undocumented way of changing SSMS config files. It may or may not work and its not supported by Microsoft. This may be made unavailable or documented because this may not be interoperable with other Windows display settings. So please be use this as an option at your own risk.

Cleanup of Load Runner Test data from SQL Server

Here is a script to purge the Load test data from your load runner database. This would be handy at times like running of disk space or performance issues related to load runner etc.

--Provide your Load runner database name
USE <<Loadrunner DBName>>
Create proc SQLZealot_CleanupTestData  (@DeleteDateUpto Datetime)
with encryption

	If not exists(Select 1 From sys.tables where name = 'TempTobeDeletedLoadRecords')
		Select LoadtestrunID into TempTobeDeletedLoadRecords from Loadtestrun (nolock) where StartTime < @DeleteDateUpto

	Declare @LoadtestrunID bigint
	While exists(Select 1 From TempTobeDeletedLoadRecords )
		Set @LoadtestrunID = (Select Top 1 Loadtestrunid from TempTobeDeletedLoadRecords ORder by 1 asc)
		Exec Prc_DeleteLoadTestRun @Loadtestrunid
		Delete TempTobeDeletedLoadRecords Where Loadtestrunid = @LoadtestrunID

	Drop Table TempTobeDeletedLoadRecords


How to identify Scans (Table/index) from cached plan in SQL Server

Today, we will quickly see how to identify the scans happening on SQL Server. I had to analyse a test SQL Server environment to identify performance bottlenecks. So, the team was looking for a way to get the scans happening on their server to further optimize and confirm the performance.

Here is a small script I created to understand the Scans on the server. Please note that I divided the script into two parts,
1. To generate a snapshot of the cached plans into a table called – Temp_CacheDump_Analyser
2. To query the Temp_CacheDump_Analyser table for scan operators

This way, even if you want to query it multiple times for different reasons, you can query the cache dump table instead of cached plan tables.

–Generate the snapshot with the required fields

SELECT usecounts,cacheobjtype,objtype,query.text
 ,executionplan.query_plan into Temp_CacheDump_Analyser
 FROM sys.dm_exec_cached_plans
 OUTER APPLY sys.dm_exec_sql_text(plan_handle) as query
 OUTER APPLY sys.dm_exec_query_plan(plan_handle) as executionplan
 WHERE [text] NOT LIKE '%sys%'
 AND cacheobjtype ='compiled plan' 

–Query the row data to identify scans on your SQL Server

	ParentOperationID, OperationID, PhysicalOperator, LogicalOperator, EstimatedCost, EstimatedIO,
	EstimatedCPU, EstimatedRows, QueryText, QueryPlan, CacheObjectType, ObjectType
SELECT	RelOp.op.value(N'../../@NodeId', N'int') AS ParentOperationID,
		RelOp.op.value(N'@NodeId', N'int') AS OperationID,
		RelOp.op.value(N'@PhysicalOp', N'varchar(50)') AS PhysicalOperator,
		RelOp.op.value(N'@LogicalOp', N'varchar(50)') AS LogicalOperator,
		RelOp.op.value(N'@EstimatedTotalSubtreeCost ', N'float') AS EstimatedCost,
		RelOp.op.value(N'@EstimateIO', N'float') AS EstimatedIO,
		RelOp.op.value(N'@EstimateCPU', N'float') AS EstimatedCPU,
		RelOp.op.value(N'@EstimateRows', N'float') AS EstimatedRows,
		qp.TEXT AS QueryText, qp.query_plan AS QueryPlan,
		qp.cacheobjtype AS CacheObjectType, qp.objtype AS ObjectType
	FROM Temp_CacheDump_Analyser qp
	CROSS APPLY qp.query_plan.nodes(N'//RelOp') RelOp (op)
SELECT	QueryPlan, ParentOperationID, OperationID, PhysicalOperator, LogicalOperator, QueryText,
		CacheObjectType, ObjectType, EstimatedCost, EstimatedIO, EstimatedCPU, EstimatedRows
FROM CachedPlans
	WHERE CacheObjectType = N'Compiled Plan'
		AND	(PhysicalOperator = 'Clustered Index Scan' OR PhysicalOperator = 'Table Scan'
		OR PhysicalOperator = 'Index Scan' OR PhysicalOperator = 'Lookup')

The caveat is the above results are based on the data available at the point in time in the cache. There may be sceanrios these data gets flushed, so the data should be collected and analysed in a regular way that means, its not a one time activity.

Hope, you enjoyed this post, please share your thoughts and feedback.


Today’s post will explain XACT_ABORT in SQL Server. An efficient way of error handling before SQL Server 2005.Post SQL Server 2005, there is a new feature included in SQL Server, TRY… CATCH. But, I would still think there is a good amount of use cases and a great value addition where XACT_ABORT is important in SQL Server. Let us cover few things in the below sections.

Whats the significance of XACT_ABORT in SQL Server?

On a run time error scenario, XACT_ABORT On settings will terminate and rollback the entire transaction. XACT_ABORT does not have any significance on compile or parse time exceptions.

How do we set up XACT_ABORT in SQL Server?

A simple and common method is to set using T-SQL as below:


Another option is at Database Engine level as below:

EXEC sp_configure 'user options', 16384

You can refer more about this here.

We can always understand the current setting of XACT_ABORT as below:

IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';  

Whats the scope if XACT_ABORT?

— SET XACT_ABORT ON/OFF is applied ONLY for the session
— By default XACT_ABORT is OFF

— XACT_ABORT ON, Try…Catch & Transaction

As mentioned earlier, by default XACT_ABORT is OFF by default and if there are any issue in one of transaction of a batch, the transaction alone gets failed as below:

There is no difference even with XACT_ABORT ON in similar case as below:

However, There is a difference in XACT_ABORT when its applied with a transaction. If there are any issue in a batch, the entire batch gets terminated as below:(please note, there is no explicit rollback applied in the example, still no transactions are committed.)

— XACT_ABORT, Transaction and Object Resolution

This is interesting to know about the fact that if there are any failures, the transaction becomes open without XACT_ABORT. If we specify the XACT_ABORT ON, then the transaction is terminated automatically.

Finally, How to preserve XACT_ABORT state in SQL Server?

Sometimes, preserving the XACT_ABORT is a requirement for many of us. As we discussed, we can use @@options and a bit operation with the corresponding user options value to preserve the value. Here is a small example, how can this be achieved as below:

Create procedure Preserve_XACTABORT_Settings

	Declare @Options Bigint
	SET @Options = @@options

	--To know the setting of XACT_ABORT for testing purpose
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF'



	You can put your actual procedure implementation


	--To know the setting of XACT_ABORT for testing purpose
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF' 

	--Preserve the XACT_ABORT settings
	IF ( (@OPTIONS & 16384 ) = 0 ) 

	--To know the setting of XACT_ABORT for testing purpose
	IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON' Else Set @XACT_ABORT = 'OFF'


Hope, you enjoyed this post, share your thoughts and feedback as always!