Tag Archives: sql

Databases, MS SQL Server 2008 R2, MS SQL Server 2016, Tech Notes

T-SQL Rename Logical and Physical Database Files

2018-08-10 Jon Culp Leave a comment

I used to get really nervous about renaming database files, which I think is an appropriate response. Changing the attributes of the database’s physical objects does sound a bit scary on the surface, but once you’ve done it a few times you’ll realize it’s easy to do, and easy to fix if you make a mistake. This simple script template shows the basic steps of the process.

-- USE [master];
GO
--Change logical file names.
ALTER DATABASE [My_Database] 
  MODIFY FILE(NAME = [old_name_data], NEWNAME = [new_name_data]);
ALTER DATABASE [My_Database] 
  MODIFY FILE(NAME = [old_name_log], NEWNAME = [new_name_log]);
GO
--Disconnect all existing sessions.
ALTER DATABASE [My_Database] SET SINGLE_-- USER WITH ROLLBACK IMMEDIATE;
GO
--Change database to OFFLINE mode.
ALTER DATABASE [My_Database] SET OFFLINE;
GO

/******************************************
STOP! NOW RENAME THE PHYSICAL FILES ON DISK
******************************************/

--Point databases at new phyisical files.
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_data', 
  FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_data.mdf');
ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_log', 
  FILENAME = 'L:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_log.ldf');
GO
--Change database to ONLINE mode.
ALTER DATABASE [My_Database] SET ONLINE;
GO
--Reopen connections to all users
ALTER DATABASE [My_Database] SET MULTI_-- USER;
GO

-- USE [master];

--Change logical file names.

ALTER DATABASE [My_Database]

MODIFY FILE(NAME = [old_name_data], NEWNAME = [new_name_data]);

ALTER DATABASE [My_Database]

MODIFY FILE(NAME = [old_name_log], NEWNAME = [new_name_log]);

--Disconnect all existing sessions.

ALTER DATABASE [My_Database] SET SINGLE_-- USER WITH ROLLBACK IMMEDIATE;

--Change database to OFFLINE mode.

ALTER DATABASE [My_Database] SET OFFLINE;

/******************************************

STOP! NOW RENAME THE PHYSICAL FILES ON DISK

******************************************/

--Point databases at new phyisical files.

ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_data',

FILENAME = 'D:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_data.mdf');

ALTER DATABASE [My_Database] MODIFY FILE(NAME = 'new_name_log',

FILENAME = 'L:\MSSQL13.MSSQLSERVER\MSSQL\DATA\new_name_log.ldf');

--Change database to ONLINE mode.

ALTER DATABASE [My_Database] SET ONLINE;

--Reopen connections to all users

ALTER DATABASE [My_Database] SET MULTI_-- USER;

Databases, Links & Lists, Tech Notes

Microsoft Released SQL Operations Studio (preview)

2017-11-20 Jon Culp Leave a comment

Microsoft recently announced at the 2017 SQL PASS event that they would be previewing a new tool for sql server that works across multiple operating systems.

“SQL Operations Studio (preview) is a free, light-weight tool, that runs on Windows, macOS, and Linux, for managing SQL Server, Azure SQL Database, and Azure SQL Data Warehouse; wherever they’re running.”

It’s now available for preview.

Read and Download:
https://docs.microsoft.com/en-us/sql/sql-operations-studio/what-is

More info (video) at TechNet:
https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/15/announcing-sql-operations-studio-for-preview/

MS SQL Server 2016, R

Resolve SQL Server Fatal error: cannot create ‘R_TempDir’

2017-01-18 Jon Culp Leave a comment

I was trying to test an install of R in SQL Server 2016 and when running a script I received this error: Fatal error: cannot create ‘R_TempDir’

Following the instructions here, I enabled external scripts, restarted the sql server service, and then tried to run the following test script:

exec sp_execute_external_script  @language =N'R',  
@script=N'OutputDataSet<-InputDataSet',    
@input_data_1 =N'select 1 as hello'  
with result sets (([hello] int not null));  
go

exec sp_execute_external_script @language =N'R',

@script=N'OutputDataSet<-InputDataSet',

@input_data_1 =N'select 1 as hello'

with result sets (([hello] int not null));

This is when the fatal error occurred. As the error suggests, R is having some issues creating a temporary directory. After some internet searching and trial and error I got past the issue.

Enable 8dot3 File Names

R configuration uses the 8dot3 file name convention, also known as “short names”. To enable this on windows 10, run the following command in CMD (command prompt):

fsutil.exe behavior set disable8dot3 0

1	fsutil.exe behavior set disable8dot3 0

For more options and information look here: https://support.microsoft.com/en-us/kb/121007

Give access to the working directory to R

Locate and open “rlauncher.config” file in a text editor. This file will be under the “<sqlserver_instance>\binn” directory. Take a look at the location of WORKING_DIRECTORY. This should have a “short name” file path. The path should be something like “<sqlserver_instance>\EXTENS~1”, and “\EXTENS~1” is equivalent to “\ExtensibilityData”. We need to give access to R to this folder. I did this by changing the permissions to full control to everyone. You may want to be more restrictive here, but in my case this did not matter.

Right click folder > Properties > Security tab > Advanced > Add
Select a principal (I entered “Everyone”)
Tick “Full control” under basic permissions and click “OK”
Tick “Replace all child object and permissions entries with inheritable permissions entries from this object” and click “OK”

Now if you rerun the script above you should get a result of “hello, 1”.

Databases, MS SQL Server 2008 R2, Tech Notes

Microsoft SQL Server Developer Edition is now free

2016-05-19 Jon Culp Leave a comment

https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

“Exciting news! Starting today, SQL Server 2014 Developer Edition is now a free download for Visual Studio Dev Essentials members (you will be prompted to sign in to Visual Studio Dev Essentials before you can download SQL Server 2014 Developer Edition). We are making this change so that all developers can leverage the capabilities that SQL Server 2014 has to offer for their data solution, and this is another step in making SQL Server more accessible. SQL Server Developer Edition is for development and testing only, and not for production environments or for use with production data.”

“SQL Server 2016 Developer Edition, when released later this year, will also be free. To learn more about the exciting new features in SQL Server 2016, read the datasheet.”

MS SQL Server 2008 R2, Tech Notes

Drop Login and Users in MS SQL Server

2016-02-22 Jon Culp 1 Comment

This code will drop the login and users for all databases for a provided user name. and reports on its progress. This only works where the user and login match. This script does not check if users own any objects.

/*********************************************
DROP LOGIN AND -- USERS FOR ALL DBs IN A SERVER
Possible errors could be if a user is the owner of a database object.
*********************************************/
 
       SET NOCOUNT ON
       -- USE master
 
       --DECLARE VARIABLES
       DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY -- USER TO DROP, typical format: domain\user
       DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX))
       DECLARE @Database_Name NVARCHAR(MAX)
       DECLARE @SQL_Drop_Login NVARCHAR(MAX)   
       DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX)
      
 
       PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME
 
             
       --DROP LOGIN FROM SERVER
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure'
       IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name)
       BEGIN TRY
              SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']'
              EXEC (@SQL_Drop_Login)
              PRINT 'Server Principal Dropped'
       END TRY
       BEGIN CATCH
              PRINT 'Server Principal Found But Could Not Drop'
       END CATCH
       ELSE
              PRINT 'Server Principal Not Found'
      
 
       ---- USER DROP PROCEDURE
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure'
      
       --LOAD DATABASE NAMES INTO TEMP TABLE TO -- USE FOR LOOP
       INSERT INTO @tbl_Databases (Name)
       SELECT Name
       FROM master.sys.databases WITH(NOLOCK)
      
       --LOOP THROUGH EACH DATABASE AND DROP -- USER IF THEY EXIST
       WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases)
       BEGIN
              SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases)
              SET @SQL_Drop_Login ='-- USE '+@Database_Name
              +' IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'')
              BEGIN TRY
                     DROP -- USER [' + @Dropping_Windows_Logon_Name +']
                     PRINT '' Dropped User in '+@Database_Name+'''
              END TRY
              BEGIN CATCH
                     PRINT '' User Found But Could Not Drop in '+@Database_Name+'''
              END CATCH
              ELSE PRINT '' User Not Found in '+@Database_Name+'''
              '
              EXEC (@SQL_Drop_Login)
              DELETE FROM @tbl_Databases WHERE Name = @Database_Name
       END
 
       PRINT 'End User Drop Procedure'
 
       --END
       PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME
 
       SET NOCOUNT OFF

/*********************************************

DROP LOGIN AND -- USERS FOR ALL DBs IN A SERVER

Possible errors could be if a user is the owner of a database object.

*********************************************/

SET NOCOUNT ON

-- USE master

--DECLARE VARIABLES

DECLARE @Dropping_Windows_Logon_Name NVARCHAR(MAX) = N'starwars\amlegault' --SPECIFY -- USER TO DROP, typical format: domain\user

DECLARE @tbl_Databases TABLE (Name NVARCHAR(MAX))

DECLARE @Database_Name NVARCHAR(MAX)

DECLARE @SQL_Drop_Login NVARCHAR(MAX)

DECLARE @SQL_Drop_DB_Users NVARCHAR(MAX)

PRINT 'Begin Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME

--DROP LOGIN FROM SERVER

PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin Server Principal Drop Procedure'

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = @Dropping_Windows_Logon_Name)

BEGIN TRY

SET @SQL_Drop_Login = 'DROP LOGIN [' + @Dropping_Windows_Logon_Name +']'

EXEC (@SQL_Drop_Login)

PRINT 'Server Principal Dropped'

END TRY

BEGIN CATCH

PRINT 'Server Principal Found But Could Not Drop'

END CATCH

ELSE

PRINT 'Server Principal Not Found'

---- USER DROP PROCEDURE

PRINT CHAR(0x0D) + CHAR(0x0A) + 'Begin User Drop Procedure'

--LOAD DATABASE NAMES INTO TEMP TABLE TO -- USE FOR LOOP

INSERT INTO @tbl_Databases (Name)

SELECT Name

FROM master.sys.databases WITH(NOLOCK)

--LOOP THROUGH EACH DATABASE AND DROP -- USER IF THEY EXIST

WHILE EXISTS (SELECT TOP 1 * FROM @tbl_Databases)

BEGIN

SET @Database_Name = (SELECT TOP 1 Name FROM @tbl_Databases)

SET @SQL_Drop_Login ='-- USE '+@Database_Name

+' IF EXISTS (SELECT * FROM sys.database_principals WHERE name = ''' + @Dropping_Windows_Logon_Name + ''' AND sys.database_principals.type = ''U'')

BEGIN TRY

DROP -- USER [' + @Dropping_Windows_Logon_Name +']

PRINT '' Dropped User in '+@Database_Name+'''

END TRY

BEGIN CATCH

PRINT '' User Found But Could Not Drop in '+@Database_Name+'''

END CATCH

ELSE PRINT '' User Not Found in '+@Database_Name+'''

EXEC (@SQL_Drop_Login)

DELETE FROM @tbl_Databases WHERE Name = @Database_Name

END

PRINT 'End User Drop Procedure'

--END

PRINT CHAR(0x0D) + CHAR(0x0A) + 'End Login and User Drop Procedure for Windows Logon Name ' + @Dropping_Windows_Logon_Name + ' on server ' + @@SERVERNAME

SET NOCOUNT OFF

MS SQL Server 2008 R2, Tech Notes, Uncategorized

Orphan Users in MS SQL Server

2016-02-22 Jon Culp Leave a comment

Checks all databases on a server for any users who do not have a correlated login, based on SID. Uses the undocumented command of sp_msforeachdb. You could put in a loop if you want to avoid this command.

DECLARE @orphan_users TABLE (Server NVARCHAR(MAX),DBName sysname, [user_name] sysname,type_desc nvarchar(60),default_schema_name sysname, create_date datetime, modify_date DATETIME, [sid] VARBINARY(85))
INSERT INTO @orphan_users (Server, DBname,[user_name],type_desc,default_schema_name,create_date,modify_date,[sid])
EXEC sp_msforeachdb
'use [?]
SELECT
@@SERVERNAME
,DB_NAME() DBname
,name
,type_desc
,default_schema_name
,create_date
,modify_date
,[sid]
from sys.database_principals where type in (''G'',''S'',''U'')
and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') )
and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'')'
SELECT * FROM @orphan_users
--WHERE user_name LIKE '%SomeUser%' /*optional*/
ORDER BY user_name

DECLARE @orphan_users TABLE (Server NVARCHAR(MAX),DBName sysname, [user_name] sysname,type_desc nvarchar(60),default_schema_name sysname, create_date datetime, modify_date DATETIME, [sid] VARBINARY(85))

INSERT INTO @orphan_users (Server, DBname,[user_name],type_desc,default_schema_name,create_date,modify_date,[sid])

EXEC sp_msforeachdb

'use [?]

SELECT

@@SERVERNAME

,DB_NAME() DBname

,name

,type_desc

,default_schema_name

,create_date

,modify_date

,[sid]

from sys.database_principals where type in (''G'',''S'',''U'')

and [sid] not in ( select [sid] from sys.server_principals where type in (''G'',''S'',''U'') )

and name not in (''dbo'',''guest'',''INFORMATION_SCHEMA'',''sys'',''MS_DataCollectorInternalUser'')'

SELECT * FROM @orphan_users

--WHERE user_name LIKE '%SomeUser%' /*optional*/

ORDER BY user_name

PostgreSQL 9, Tech Notes

Get Database Sizes in PestgreSQL

2016-02-18 Jon Culp Leave a comment

Returns the database name, owner, and size on a given server. Tested in PostgrSQL 9.2 and 9.3.

SELECT 
 d.datname AS Name
 ,pg_catalog.pg_get_userbyid(d.datdba) AS Owner
 ,CASE 
   WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
   THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))
   ELSE 'No Access'
  END AS SIZE
FROM
 pg_catalog.pg_database d
ORDER BY
 CASE
  WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')
  THEN pg_catalog.pg_database_size(d.datname)
  ELSE NULL
 END DESC

SELECT

d.datname AS Name

,pg_catalog.pg_get_userbyid(d.datdba) AS Owner

,CASE

WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname))

ELSE 'No Access'

END AS SIZE

FROM

pg_catalog.pg_database d

ORDER BY

CASE

WHEN pg_catalog.has_database_privilege(d.datname, 'CONNECT')

THEN pg_catalog.pg_database_size(d.datname)

ELSE NULL

END DESC

Source: https://wiki.postgresql.org/wiki/Disk_Usage

MS SQL Server 2008 R2, Tech Notes

SQL Server Job to Capture Reads

2016-02-05 Jon Culp Leave a comment

We needed to capture physical reads over time from our SQL server. One method we decided to deploy was to check sys.dm_exec_query_stats periodically and capture the differences between each check. The way I am doing it below is not 100% accurate because sys.dm_exec_query_stats is based on cached plans and the server will let clear the plan data over time. This means that when the differences are calculated any plan that was cleared from cache would not be included in the current check, but would still be in the comparison record. This could be solved by storing and comparing at the plan level but for my purposes I prefer to store summary information and do not mind this issue because I am running my comparison every hour and then will be averaging the results over time, and the result does not need to be exactly precise, just close enough to give us a general understanding.

This is the query inside the job:

IF  NOT EXISTS (SELECT * FROM dba.sys.objects WHERE object_id = OBJECT_ID(N'dba.[dbo].[dm_exec_query_Stats_summary]') AND type in (N'U'))
CREATE TABLE dba.dbo.dm_exec_query_Stats_summary (
	SysDateTime DATETIME NOT NULL, PhysicalReads BIGINT NOT NULL, LogicalReads BIGINT NOT NULL
	, PhysicalReads_Delta BIGINT NULL, LogicalReads_Delta BIGINT NULL
	)

INSERT INTO dba.dbo.dm_exec_query_Stats_summary (SysDateTime, PhysicalReads, LogicalReads, PhysicalReads_Delta, LogicalReads_Delta)

SELECT A.SYSDATETIME, A.PhysicalReads, A.LogicalReads
,A.PhysicalReads - B.PhysicalReads
,A.LogicalReads - B.LogicalReads
FROM(
	SELECT 
	SYSDATETIME() AS SYSDATETIME
	,SUM(total_physical_reads) as PhysicalReads
	,sum(total_logical_Reads)  as LogicalReads
	FROM sys.dm_exec_query_Stats A
)A
OUTER APPLY (
	SELECT TOP 1 B.PhysicalReads, B.LogicalReads 
	FROM dba.dbo.dm_exec_query_Stats_summary B
	WHERE B.sysdatetime < A.SYSDATETIME
	ORDER BY B.SysDateTime DESC
)B

IF NOT EXISTS (SELECT * FROM dba.sys.objects WHERE object_id = OBJECT_ID(N'dba.[dbo].[dm_exec_query_Stats_summary]') AND type in (N'U'))

CREATE TABLE dba.dbo.dm_exec_query_Stats_summary (

SysDateTime DATETIME NOT NULL, PhysicalReads BIGINT NOT NULL, LogicalReads BIGINT NOT NULL

, PhysicalReads_Delta BIGINT NULL, LogicalReads_Delta BIGINT NULL

)

INSERT INTO dba.dbo.dm_exec_query_Stats_summary (SysDateTime, PhysicalReads, LogicalReads, PhysicalReads_Delta, LogicalReads_Delta)

SELECT A.SYSDATETIME, A.PhysicalReads, A.LogicalReads

,A.PhysicalReads - B.PhysicalReads

,A.LogicalReads - B.LogicalReads

FROM(

SELECT

SYSDATETIME() AS SYSDATETIME

,SUM(total_physical_reads) as PhysicalReads

,sum(total_logical_Reads) as LogicalReads

FROM sys.dm_exec_query_Stats A

OUTER APPLY (

SELECT TOP 1 B.PhysicalReads, B.LogicalReads

FROM dba.dbo.dm_exec_query_Stats_summary B

WHERE B.sysdatetime < A.SYSDATETIME

ORDER BY B.SysDateTime DESC

This is what the table looks like with a few records. Note that this is a result of running the job multiple times with a few seconds between each interval.

Links & Lists

2016 Learning Goals

2016-01-07 Jon Culp Leave a comment

MCSA & MCSE SQL Server Certification

MCSA: SQL Server
- 70-461 – Querying Microsoft SQL Server 2012
  - https://www.udemy.com/microsoft-sql-server-2012-certification-70-461/
- 70-462 – Administering Microsoft SQL Server 2012 Databases
- 70-463 – Implementing a Data Warehouse with Microsoft SQL Server 2012
MCSE: Data Platform
- 70-464 – Developing Microsoft SQL Server Databases
- 70-465 – Designing Solutions for SQL Server
MCSE: Business Intelligence
- 70-466 – Implementing Data Models and Reports with Microsoft SQL Server
- 70-467 – Designing Business Intelligence Solutions with Microsoft SQL Server

Neural Networks and Machine Learning

Training Sources:

Coursera
- https://www.coursera.org/course/neuralnets
- 8 weeks of study
- 7-9 hours/week
Udemy
- https://www.udemy.com/neural-networks_in_matlab/
- 23 Lectures and 2 hours of content

Other Interests

Internet of Things Development
Mobile Application Development
Python
Ruby on Rails
Node.js, MongoDB, and Express
Quartz Composer
Actuary Exams

Learning Websites

MS SQL Server 2008 R2, Tech Notes

Performance of Like vs Left/Right for Indexed and Non Indexed Columns

2015-12-24 Jon Culp 2 Comments

I have have heard and read many times over the years that Left or Right should always be used, where possible, instead of Like. On the surface, this does sound logical since the Like command is often used for pattern searching and one could assume that the Like command is causing some extra work to be performed by the SQL server engine even though the predicate expressions are essentially equivalent. I personally have always assumed that they operated equivalently, however, I had never performed a performance analysis to infer the true answer. Also, was curious to find if there was a difference in performance when applied specifically to an indexed column vs non-indexed column.

The names and data structures in this example have been changed to protect the innocent. For this test, assume [SOME_TABLE] has 530 million records and contains two columns: [INDEXED_COLUMN], and [NONINDEXED_COLUMN]. Indexed_Column has a clustered index and Nonindexed_Column does not have an index. We will run a test using like and left against each column using a basic count(*) command.

Test 1 – Non Indexed Column

--QUERY 1) LIKE, NOT INDEXED
SELECT COUNT(*) FROM [SOME_TABLE] WITH(NOLOCK)
WHERE [NONINDEXED_COLUMN] like 'ABC%'

--QUERY 2) LEFT, NOT INDEXED
SELECT COUNT(*) FROM [SOME_TABLE] WITH(NOLOCK)
WHERE LEFT([NONINDEXED_COLUMN],3) = 'ABC'

--QUERY 1) LIKE, NOT INDEXED

SELECT COUNT(*) FROM [SOME_TABLE] WITH(NOLOCK)

WHERE [NONINDEXED_COLUMN] like 'ABC%'

--QUERY 2) LEFT, NOT INDEXED

SELECT COUNT(*) FROM [SOME_TABLE] WITH(NOLOCK)

WHERE LEFT([NONINDEXED_COLUMN],3) = 'ABC'

Output for queries 1 and 2 = 63 million.

Query 1 used like and averaged 102 seconds over 5 measurements.
Query 2 used left and averaged 103 seconds over 5 measurements.

Execution plans for queries 3 and 4:

Test 2 – Indexed Column

--QUERY 1) LIKE, INDEXED
SELECT COUNT(*) FROM [SOME_TABLE] WITH(NOLOCK)
WHERE [INDEXED_COLUMN] like 'DEF%'

--QUERY 2) LEFT, INDEXED
SELECT COUNT(*) FROM [SOME_TABLE] WITH(NOLOCK)
WHERE LEFT([INDEXED_COLUMN],3) = 'DEF'

--QUERY 1) LIKE, INDEXED

SELECT COUNT(*) FROM [SOME_TABLE] WITH(NOLOCK)

WHERE [INDEXED_COLUMN] like 'DEF%'

--QUERY 2) LEFT, INDEXED

SELECT COUNT(*) FROM [SOME_TABLE] WITH(NOLOCK)

WHERE LEFT([INDEXED_COLUMN],3) = 'DEF'

Output for queries 1 and 2 = 87 million.

Query 1 used like and averaged 3 seconds over 5 measurements.
Query 2 used left and averaged 14 seconds over 5 measurements.

Execution plans for queries 1 and 2:

Conclusion

You can see from the results that Like is actually considerably faster than Left when applied to an indexed column. This is because Like is treated as a computed column, which can utilize indexes, while Left is a function which can not utilize indexes. If an index is not involved then Like and Left perform the same.

There might be additional factors that impact performance differences which I am not considering such as other index types, and other data types. I came across another blog (http://cc.davelozinski.com/sql/like-vs-substring-vs-leftright-vs-charindex) where a similar test was done. Surprisingly, their results showed that Chardindex is the most efficient out of Like, Substring, Left/Right, and Charindex, and their results between Like and Left/Right are quite different than mine. At some point I’d like to go through their code and identify exactly what is different.

Edit: The author reached out to me and mentioned that the main difference between our results is likely due to my analysis being ran on SQL Server 2008 R2 vs theirs being done on SQL Server 2014 and there have been changes to the query engine between versions. I encourage anyone seeing this to check out Dave’s blog.

More about Indexes on Computed Columns: https://msdn.microsoft.com/en-us/library/ms189292.aspx

Jon Culp [.com]

Tag Archives: sql

T-SQL Rename Logical and Physical Database Files

Microsoft Released SQL Operations Studio (preview)

Resolve SQL Server Fatal error: cannot create ‘R_TempDir’

Enable 8dot3 File Names

Give access to the working directory to R

Microsoft SQL Server Developer Edition is now free

Get Database Sizes in PestgreSQL

SQL Server Job to Capture Reads

2016 Learning Goals

MCSA & MCSE SQL Server Certification

Neural Networks and Machine Learning

Other Interests

Learning Websites

Performance of Like vs Left/Right for Indexed and Non Indexed Columns

Test 1 – Non Indexed Column

Test 2 – Indexed Column

Conclusion

An Information Archive for a Frivolous Human