Getting rid of OR & LIKE in Sql Server

Ever have a WHERE statement that looks like this:

...
WHERE 
(T.ColumnName IS NULL OR T.ColumnName LIKE @ColumnName)
AND (T2.ColumnName IS NULL OR T2.ColumnName LIKE @ColumnName)
AND (T3.ColumnName IS NULL OR T3.ColumnName LIKE @ColumnName)
AND (T4.ColumnName IS NULL OR T4.ColumnName LIKE @ColumnName)

Might as well just rip all the indexes off the tables it’s accessing.  They’re getting invalidated.  Twice.  Apparently the developer really did not want to use indexes and wanted to be sure of it.

So, first things first.  Invite the developer that wrote said code to a friendly match of The Most Dangerous Game.  After that problem has been taken care of, you can replace the functionality with a CTE and some CASE statements.  Observe.

;WITH CTE_Table AS CTE(
	SELECT 
		Column1,
		Column2,
		Column3
	FROM dbo.Table
)
SELECT 
	Column1,
	Column2,
	Column3
FROM CTE_Table
WHERE
Column1 = CASE WHEN @Var1 IS NULL THEN Column1 ELSE @Var1 END
AND Column2 = CASE WHEN @Var2 IS NULL THEN Column2 ELSE @Var2 END
AND Column2 = CASE WHEN @Var3 IS NULL THEN Column3 ELSE @Var3 END

In my case, the developer was just throwing LIKE statements in, even though the variables being passed were from drop-downs, so exact matches were guaranteed.  You could replace the Column1 = with Column1 LIKE …etc…, but be sure to append a ‘%’ at the end of the variable.  If you say LIKE %@Var1% then you’ll invalidate your indexes all over again.  The LIKE needs to start looking somewhere in the index to be effective, and the starting % will cause it to scan everything.

A word of caution though, this will throw your statistics off drastically.  In my particular case, the estimated rows are 6, whereas the actual rows are 81,888.  Logical reads are out of this world.

OR statement in WHERE clause

Don’t do it.  Here is a good example why:

The images are blurry on purpose.  What I want you to notice is the thickness of the lines on the plans between the two images. 

Table scans galore against a table with 1 million + rows.  This is the most commonly called sql statement in the database as well.  In case you can’t read it in the image below, it’s returning almost 750,000 rows from the scan.

image

Take the OR out, boom. All seeks.  A nasty bookmark lookup resulted, but I’ll fix that little red wagon later.

image

Due to the removal of the OR statement it’s not returning as many rows, but the row count difference is marginal with and without the OR. As opposed to the above, this returns only 369 rows from the table.

Unfortunately, the developers have coded about 5 OR statements in a dynamically generated sql string.  They’ve essentially painted themselves into a corner.  It’s pretty evil.

Script Indexes for a Publication

I like to script the Indexes manually to apply to a subscriber when creating a publication.  Call me a sadist, but I am what I am.  I whipped up a quick script to connect to a published database, grab the articles in the publication and script out the indexes on said articles (obviously, not the clustered index).  It’s very very slow, but I’m too tired to try to optimize this now.  Use at your own risk.  Minimally tested and not a lot of error handling is involved.

param(
	[string]$InstanceName = {throw "You must enter an Instance Name."},
	[string]$PubName = {throw "You must enter the name of the publication for which to script the indexes."},
	[string]$DistDBName = {throw "You must enter the name of the published database."},
	[string]$FileLocation = {throw "You must enter the location of where you want the scripts to be put."}
)

function ScriptIndexes([string]$TableName)
{	
	$Table = Get-ChildItem | where {$_.Name -eq $TableName}
	foreach($ix in $Table.Indexes)
	{
		if($ix.IsClustered -eq $false)
		{
			$ix.Script() | out-file "$FileLocation\$($Table.Name).sql" -append; "GO`n$($separator)`n" | out-file "$FileLocation\$($Table.Name).sql" -append -Force
		}
	}
}

function Main()
{
	# Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
	$smo = [System.Reflection.Assembly]::LoadWithPartialName( 'Microsoft.SqlServer.SMO')
	if ((($smo.FullName.Split(','))[1].Split('='))[1].Split('.')[0] -ne '9') 
	{
	  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMOExtended') | out-null
	  [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SQLWMIManagement') | out-null
	}

	# Handle any errors that occur
	Trap 
	{
		# Handle the error
		$err = $_.Exception
		write-host $err.Message
		while( $err.InnerException ) 
		{
			$err = $err.InnerException
			write-output $err.Message
		};
		# End the script.
		break
	}

	$server = new-object ('Microsoft.SqlServer.Management.Smo.Server') $InstanceName

	$DistQuery = @"
SELECT art.name AS TableName FROM dbo.sysarticles art (NOLOCK) 
INNER JOIN dbo.syspublications pub 
ON art.pubid = pub.pubid 
WHERE  pub.name = '$PubName'
"@

	$PubRes = invoke-sqlcmd -ServerInstance $InstanceName -Database $DistDBName -Query $DistQuery
	sl SQLSERVER:SQL\$InstanceName\DATABASES\$DistDBName\TABLES
	foreach($row in $PubRes)
	{
		ScriptIndexes $row.TableName
	}	
}

cls
Main

Full Backup of User Databases with Litespeed

Quick script to show how to backup all user databases using litespeed.  This particular one was for sql server 2k (ya, I know, will upgrade this to 2008R2 on Monday).  If using it for 2005+ best change the sysdatabases reference to sys.databases. 

ALTER PROCEDURE dbo.lsp_FullBackupUserDBs(
	@BackupPath		VARCHAR(255) = 'F:\MSSQL\BACKUP\Full\User\'
)
AS
BEGIN
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
	
	DECLARE @mkdir VARCHAR(4000)
	DECLARE @timestamp VARCHAR(50)
	DECLARE @filenamein VARCHAR(512)
	DECLARE @IDENT INT
	DECLARE @DBNAME VARCHAR(200)
	
	SELECT @IDENT = MIN([DBID]) 
	FROM sysdatabases 
	WHERE [DBID] > 0
	AND NAME NOT IN ('master', 'model', 'msdb', 'tempdb')
	GROUP BY DBID
	
	WHILE @IDENT IS NOT NULL
	BEGIN
		SELECT @DBNAME = NAME FROM sysdatabases WHERE [DBID] = @IDENT

		SET @mkdir = 'mkdir ' + @BackupPath + @DBNAME + '"'

		SELECT @timestamp = DATENAME(MONTH, GETDATE()) + '_' + DATENAME(DAY, GETDATE()) + '_'  + DATENAME(YEAR, GETDATE()) + '__' + STUFF('00', 3-LEN(HOUR), LEN(HOUR), HOUR ) + '_' + STUFF('00', 3-LEN(MINUTE), LEN(MINUTE), MINUTE )
		FROM (SELECT DATENAME(HOUR, GETDATE()) AS HOUR, DATENAME(MINUTE, GETDATE()) AS MINUTE) AS timedata

		SET @filenamein = @BackupPath + @DBNAME + '\' + @DBNAME + '_db_%DateTimePostfix.bak'
		SET @filenamein = REPLACE(@filenamein, '%DateTimePostfix', @timestamp)

		EXECUTE xp_procedure @mkdir, no_output

		EXECUTE master.dbo.xp_backup_database 
			@database=@DBNAME, 
			@filename=@filenamein, 
			@threads=3, 
			@init=1, 
			@priority=0, 
			@logging=0, 
			@affinity=0, 
			@throttle=90, 
			@buffercount=20, 
			@maxtransfersize=1048576, 
			@compressionlevel=6

	SELECT @IDENT=MIN([DBID]) 
	FROM sysdatabases 
	WHERE [DBID] > 0 
	AND [DBID]>@IDENT
	AND NAME NOT IN ('master', 'model', 'msdb', 'tempdb', 'distribution')

	END

END
GO

Code Formatting for Windows Live Writer and WordPress

Just to save you the trouble, here is the best code formatter for windows live writer when publishing to wordpress.  This is more of a reminder for myself so’s I don’t have to scour the web to find this again, but hopefully this will save someone else some time in trying to track down a good code formatter.  You can grab it here.

It’s the one that I’m using in the latest posts on this site.  If you scroll to the earlier posts, you’ll definitely notice the difference.

View Remote Drive Space in Powershell

Quick script to view the drive information for remote systems in powershell.  Just pass in the server name as a param.

param
(
	[string]$ComputerName = ""
)

$drives = gwmi win32_logicaldisk -filter "drivetype=3" -ComputerName ($ComputerName)
$server = @{n = "Server"; e = {$_.__SERVER}}
$free = @{n = "FreeSpace(GB)"; e = {"{0:N2}" -f ($_.FreeSpace/1GB)}}
$Size = @{n = "Size(GB)"; e = {"{0:N2}" -f ($_.Size/1GB)}}
$Label = @{n = "Volume Name"; e = {$_.VolumeName}}
$Percentage = @{n = "PercentageFree"; e = {"{0:N2}" -f `
	([Int64]$_.FreeSpace / [Int64]($_.Size) * 100)}}
$drives | select $server, $Label, DeviceID, $free, $Size, $Percentage | ft -auto

image

Manually uninstall sql server

Note, this works for Sql Server 2005 only!! If, for some reason Sql Server does not uninstall cleanly or at all, here are a few processes that you can use to
manually uninstall sql server.

If you’re on a clustered server, the first step you’ll want to take is to make sql server think it is no longer part of a
cluster. To achieve this, you’ll need to modify a key within the registry. Secondly, ensure that the data disks are
located on the node that you are attempting to remove sql server from.

Open the registry editor by typing regedit at the run command.

image

Next, navigate to HKLM\Software\Microsoft\Microsoft SQL Server\<instid>\Setup, where <instid> represents the specific instance of SQL Server 2005 being uninstalled. Under this key, set the SqlCluster value to 0.

image

If you don’t have Sql Server under the Add/Remove Programs, you’ll have to reinstall the Sql Server support tools. Navigate to the Sql Server install, and execute the SqlSupport.msi under Servers\Setup.

image

Next, open up a command prompt and navigate to c:\Program Files\Microsoft SQL Server\90\Setup Bootstrap. Type in ARPWrapper.exe /Remove. This should start the uninstall of sql server. If you have multiple instances on this machine, only uninstall one instance at a time, otherwise the uninstall will error.

image

For more drastic measures, see this article.