Author: Latheesh NK

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:

Formats:
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]
GO
Create Function dbo.fn_GenerateText 
				(@Length integer, @Format varchar(6) = 'Mixed')
Returns varchar(256)
as 
Begin

--	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)
--

Declare 
	@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'; 
else
	Set @ValidCharacters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz';


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

Set @Count = 1; 


While @Count <= @Length 
Begin 
	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; 
End 


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`


ReturnData:

Return @RandomValue 

End 

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’)

Advertisements

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.

Assumptions:
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')
Begin

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
begin

print @move
--Exec (@move)

fetch next from cur_move into @move
end
close cur_move
deallocate cur_move

End
Else
Begin
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
return;
End

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
[$RootKey$\Themes\{1ded0138-47ce-435e-84ef-9ec1f439b749}]

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
as
Begin

	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 )
	Begin
		Set @LoadtestrunID = (Select Top 1 Loadtestrunid from TempTobeDeletedLoadRecords ORder by 1 asc)
		Exec Prc_DeleteLoadTestRun @Loadtestrunid
		Delete TempTobeDeletedLoadRecords Where Loadtestrunid = @LoadtestrunID
	End

	Drop Table TempTobeDeletedLoadRecords

End

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

;WITH XMLNAMESPACES(DEFAULT N'http://schemas.microsoft.com/sqlserver/2004/07/showplan'),
CachedPlans
(
	ParentOperationID, OperationID, PhysicalOperator, LogicalOperator, EstimatedCost, EstimatedIO,
	EstimatedCPU, EstimatedRows, QueryText, QueryPlan, CacheObjectType, ObjectType
)
AS
(
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.

XACT_ABORT in SQL Server

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:

SET XACT_ABORT {ON/OFF}

Another option is at Database Engine level as below:

EXEC sp_configure 'user options', 16384
RECONFIGURE WITH OVERRIDE

You can refer more about this here.

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

DECLARE @XACT_ABORT VARCHAR(3)  = 'OFF'
IF ( (16384 & @@OPTIONS) = 16384 ) SET @XACT_ABORT = 'ON';  
SELECT @XACT_ABORT AS XACT_ABORT; 

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
as
Begin

	Declare @Options Bigint
	SET @Options = @@options

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

	SET XACT_ABORT ON

	/*

	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' 
	SELECT @XACT_ABORT AS XACT_ABORT; 	


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

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

End

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

CONTAINSTABLE is not returning records from full text enabled table in SQL Server

Recently, we have an issue, FULL TEXT search was not working with CONTIANSTABLE. My friend was searching for a word in a document where the table is enabled with FULL TEXT feature, however the search was not bringing any records.

My first thought was there could be a missing join condition that could not get the results. The actual query in the issue was really a big one with lots of tables and filters involved.So the first thing that we did remove all joins or conditions and see if CONTAINSTABLE works good, but that attempt was also failing. It was a clear indication that there is something wrong that it could not return results at FULL TEXT functionality.

For everyone to understand better, Let us go through a sample script to replicate the issue:

1. Create a table for our example

CREATE TABLE [DBO].[DOCUMENTTABLE](
	[DOCUMENTID] [UNIQUEIDENTIFIER] NOT NULL,
	[DOCUMENT_CONTENT] [IMAGE] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE INDEX IX_DOCUMENTTABLE ON DOCUMETTABLE(DOCUMENTID)

2. Load some data into Table – We uploaded the same file in question which is a .xlsx type

3. Query table with CONTAINSTABLE

Select * From CONTAINSTABLE (DocumentTable, DOCUMENT_CONTENT,'reporting')

Msg 7601, Level 16, State 2, Line 2
Cannot use a CONTAINS or FREETEXT predicate on table or indexed view ‘DocumentTable’ because it is not full-text indexed.

4. Enable FULL TEXT Indexing on the table

5. While executing the query, it is observed again no data returned

Further, we decided to check the document and its content for the search word manually and found the word was present and the document was also the same “.xlsx”. It is to be noted that we saved the data in an IMAGE datatype as shown in the example. With that information, we wanted to know the supporting file types in our full text enabled table with the below query.

select document_type, path from sys.fulltext_document_types

The above query was not returning the document type – “.xlsx” and it clearly indicated that the reason why the document could not search the word as the document type was a non-supporting one. [DOCUMENTTABLE] is a table that enabled FULL TEXT and the datatype of the field “[DOCUMENT_CONTENT]” was IMAGE. Then, my analysis took me a very important information that if we need to index a document type for which a pre-installed iFilter does not exists at OS level, we need to install the appropriate iFilter in the OS and then load those into SQL Server for a successful search functionality.

How to install the iFilter and Load into SQL Server

1. Download the iFilter , the below supports a wide range of document types

Microsoft Office 2010 Filter Packs

(Please note that MSmight retire the above as latest version is released, you may need to check for the latest.)

The following file types are supported by the Office 2010 Filter Pack:

2. Install the downloaded set

3. Load the installed OS resources to SQL Server as below.

exec sp_fulltext_service 'load_os_resources', 1;
exec sp_fulltext_service 'verify_signature', 0;
exec sp_fulltext_service 'Update_languages'
exec sp_fulltext_service 'Restart_all_fdhosts'

Once the above steps are completed, we were able to query the word and the results were returned as expected.

Hope this helps you in a similar situation and it was a good lesson learned to me!

References:
How to register iFilters