SQL Collation and related performance impact, viewing collation in query plans

Published Mar 15 2019 11:42 AM 4,432 Views
Occasional Visitor

First published on MSDN on Feb 04, 2013

It has been a while since there has been activity on this blog, we as a team will be trying to post weekly going forwards so as to share what we do on a regular basis – happy reading!

 

I was posed a question by a fellow PFE during his performance `engagement. The specific question was whether a Literal predicate which had different accent sensitivity or collation when compared to a column would trigger an implicit conversion resulting in potential performance degradation and more importantly where in the plan would you see this.

 

Collation settings exist at the Server level, the Database Level and potentially defined at the column level as well. By default if no collation is specified at the column level when creating the table, database collation is assumed

 

To check the collation of a database:

SELECT DATABASEPROPERTYEX('TEMPDB','COLLATION')

 

And further to see the collation of a column, we can use the query below

SELECT object_name(object_id) AS ObjectName
	,name AS ColName
	,collation_name
FROM sys.columns
WHERE object_id = object_id('testcollate')

Now moving on to more of the Performance aspects of the question:

Same Collation comparison – If the literal or columns being compared are the same collection, we have no problem as we can see below

SET NOCOUNT ON

USE tempdb
GO

DROP TABLE testcollate
GO

CREATE TABLE testcollate (
	myid INT identity
	,myname VARCHAR(810)
	)
GO

INSERT INTO testcollate
VALUES (replicate('a', 800)) GO 10000

INSERT INTO testcollate
VALUES ('Denzil')
GO

CREATE INDEX myind ON testcollate (myname)
GO

SET STATISTICS IO ON
GO

SELECT myname
FROM testcollate
WHERE myname = 'Denzil'

 

Table 'testcollate'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

If the literal being compared has a different collation or is explicitly converted to a different collation, collation precedence kicks in- http://msdn.microsoft.com/en-US/library/ms179886(v=SQL.90).aspx

If the literal has an explicit collation, we will get a plan with a CONVERT/SCAN. The CONVERT_IMPLICIT by itself gives no indication this is due to a Collation difference per say, in fact almost looks like it could be some data type mismatch which it is not and on the constant side, there is a CONVERT given that we were explicitly collating it to a particular collation.

SELECT myname
FROM testcollate
WHERE myname = 'Denzil' collate SQL_Latin1_General_Cp437_CS_AS

Table 'testcollate'. Scan count 1, logical reads 1240, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You will have to look at the input/output trees to actually see where the change in collation is happening as that is not exposed in the query plan itself as far as I know. I am using QUERYTRACEON which is an undocumented command in order to demonstrate collation related converts. QueryTraceON is blogged about in several places – See Benjamin blog ( Query Optimizer Trace Flags )

SELECT myname
FROM testcollate
WHERE myname = 'Denzil' collate SQL_Latin1_General_Cp437_CS_AS
OPTION (
	RECOMPILE
	,QueryTraceon 8606
	)
GO
****************************************
*** Input Tree: ***

        LogOp_Project QCOL: [tempdb].[dbo].[testcollate].myid QCOL: [tempdb].[dbo].[testcollate].myname

            LogOp_Select

                LogOp_Get TBL: testcollate testcollate TableID=773577794 TableReferenceID=0 IsRow: COL: IsBaseRow1001

                ScaOp_Comp x_cmpEq

                    ScaOp_Convert varchar collate 520142856,Null,Var,Trim,ML=810

                        ScaOp_Identifier QCOL: [tempdb].[dbo].[testcollate].myname

                    ScaOp_Const TI(varchar collate 520142856,Var,Trim,ML=6) XVAR(varchar,Owned,Value=Len,Data = (6,Denzil))

            AncOp_PrjList 

In order to get the Collation Name of that ID:

SELECT convert(SYSNAME, collationpropertyfromid(520142856, 'name'))

If the Column has different collation than the database would we need to collate the literal to the column itself?  Is the constant or literal collated to the collation of the Database or the collation of the column given they both are different?

USE tempdb
GO

DROP TABLE testcollate
GO

CREATE TABLE testcollate (
	myid INT identity PRIMARY KEY
	,myname VARCHAR(810) collate SQL_Latin1_General_Cp437_CS_AS
	)
GO

INSERT INTO testcollate
VALUES (replicate('a', 800)) GO 10000

INSERT INTO testcollate
VALUES ('Denzil')
GO

CREATE INDEX myind ON testcollate (myname)
GO

--As you can see below, the Database collation is different than the Column collation
SELECT DATABASEPROPERTYEX('TEMPDB', 'COLLATION') AS DBCollation
	,object_name(object_id) AS ObjectName
	,name AS ColName
	,collation_name AS ColumnCollation
FROM sys.columns
WHERE object_id = object_id('testcollate')

SELECT *
FROM testcollate
WHERE myname = 'Denzil'

We actually get a Seek here, which means the literal here was converted to the collation of the column and not the database.

SELECT *
FROM testcollate
WHERE myname = 'Denzil'
OPTION (
	RECOMPILE
	,QueryTraceon 8606
	)
****************************************
*** Input Tree: ***

        LogOp_Project QCOL: [tempdb].[dbo].[testcollate].myid QCOL: [tempdb].[dbo].[testcollate].myname

            LogOp_Select

                LogOp_Get TBL: testcollate testcollate TableID=741577680 TableReferenceID=0 IsRow: COL: IsBaseRow1001

                ScaOp_Comp x_cmpEq

                    ScaOp_Identifier QCOL: [tempdb].[dbo].[testcollate].myname

                    ScaOp_Const TI(varchar collate 520142856,Var,Trim,ML=6) XVAR(varchar,Owned,Value=Len,Data = (6,Denzil))

            AncOp_PrjList

Here you can see the Constant is being collated to the Column collation and not the database collation.

SELECT convert(SYSNAME, collationpropertyfromid(520142856, 'name'))

There have been several blogs on Collation conflict and how to resolve that so I intentionally stayed away from that. Arvind also has a blog on some collation and performance scenarios which is a great read - SQL collation and performance

-Denzil Ribeiro , SQL Dedicated Premier Field Engineer

1 Comment
%3CLINGO-SUB%20id%3D%22lingo-sub-1028881%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20Collation%20and%20related%20performance%20impact%2C%20viewing%20collation%20in%20query%20plans%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1028881%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there.%20A%20few%20clarifications%20need%20to%20be%20made%20to%20the%20info%20presented%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3Ewhether%20a%20Literal%20predicate%20which%20had%20different%20accent%20sensitivity%20or%20collation%20when%20compared%20to%20a%20column%20would%20trigger%20an%20implicit%20conversion%20resulting%20in%20potential%20performance%20degradation%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3COL%3E%3CLI%3E%3CSPAN%3EString%20literals%20and%20variables%20initially%20use%20the%20database's%20default%20collation%20as%20their%20collation.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3EAssuming%20no%20explicit%20collation%20set%20via%20COLLATE%20clause%20on%20the%20predicate%2C%20the%20collation%20of%20the%20literal%20or%20variable%20is%20irrelevant%20(from%20a%20performance%20perspective%2C%20though%20for%20VARCHAR%20data%20it%20%3CEM%3Ecan%3C%2FEM%3E%20be%20relevant%20if%20any%20characters%20having%20values%20128%20-%20255%20%2F%200x80%20-%200xFF%20are%20present)%20since%20collation%20precedence%20uses%20the%20collation%20of%20the%20column.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Eimplicit%20conversions%2C%20by%20themselves%2C%20are%20not%20the%20issue%2C%20as%20whatever%20performance%20degradation%20they%20cause%20is%20minimal%20and%20likely%20barely%20noticeable.%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%3CSPAN%3EIf%20the%20literal%20being%20compared%20has%20a%20different%20collation%20or%20is%20explicitly%20converted%20to%20a%20different%20collation%2C%20collation%20precedence%20kicks%20in%3C%2FSPAN%3E%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3COL%3E%3CLI%3E%3CSPAN%3Eyes%2C%20if%20there%20is%20any%20difference%20in%20collation%2C%20for%20any%20reason%2C%20collation%20precedence%20will%20determine%20which%20collation%20to%20use%2C%20of%20if%20both%20sides%20have%20equal%20weight%2C%20then%20an%20error%20will%20be%20thrown.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Ea%20literal%20(or%20variable)%20always%20starts%20out%20with%20the%20database's%20default%20collation.%20But%20this%20doesn't%20matter%20since%20collation%20precedence%20will%20use%20the%20collation%20of%20the%20column.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Ean%20explicit%20collation%20(i.e.%20set%20via%20the%20%3CSTRONG%3ECOLLATE%20%7Bcollation_name%7D%3C%2FSTRONG%3E%20clause%20on%20a%20predicate)%20will%20override%20the%20column's%20collation%2C%20but%20if%20there%20is%20any%20degradation%26nbsp%3Bin%20performance%20in%20this%20scenario%20(and%20that's%20usually%20only%20possible%2C%20though%20not%20even%20guaranteed%2C%20if%20the%20column%20is%20indexed)%2C%20then%20nobody%20should%20really%20be%20surprised%20because%20it%20was%20an%20explicit%20change%20that%20is%20clearly%20stated%20in%20the%20query%20%3B)%3C%2Fimg%3E%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CBLOCKQUOTE%3E%3CP%3E%3CSPAN%3EIs%20the%20literal%20collated%20to%20the%20collation%20of%20the%20Database%20or%20the%20collation%20of%20the%20column%20given%20they%20both%20are%20different%3F%20...%26nbsp%3BWe%20actually%20get%20a%20Seek%20here%2C%20which%20means%20the%20literal%20here%20was%20converted%20to%20the%20collation%20of%20the%20column%20and%20not%20the%20database.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%3CSPAN%3EThe%20question%20and%20answer%20here%20are%20both%20poorly%20phrased%20and%20hence%20can%20be%20misleading.%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3Ethe%20actual%20answer%20to%20the%20question%20is%3A%20BOTH.%20Initially%20(i.e.%20when%20the%20query%20is%20being%20parsed)%2C%20literals%20and%20variables%20use%20the%20database's%20default%20collation.%20Then%2C%20if%20compared%20to%20a%20column%20having%20a%20different%20collation%20than%20the%20database%20(or%20%3CSTRONG%3ECOLLATE%3C%2FSTRONG%3E%20clause%20is%20used)%2C%20the%20collation%20will%20be%20converted%20to%20the%20column's%20collation%20(or%20to%20what%20is%20specified%20by%20the%20%3CSTRONG%3ECOLLATE%3C%2FSTRONG%3E%20clause).%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Eliterals%20(and%20variables)%20need%20to%20have%20a%20collation%2C%20so%20it%20doesn't%20make%20sense%20that%20they%20would%20be%20converted%20to%20the%20database's%20collation%3B%20they%20are%20already%20using%20the%20database's%20collation.%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EWith%20regards%20to%20a%20collation%20difference%20causing%20performance%20degradation%2C%20that%20can%20only%20really%20happen%20under%20the%20following%20conditions%3A%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3Ecolumn%20is%20indexed%2C%20%3CEM%3Eand%3C%2FEM%3E%20...%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E%3CSTRONG%3ECOLLATE%3C%2FSTRONG%3E%20clause%20is%20specified%20on%20the%20predicate%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3E%3CSTRONG%3ECOLLATE%3C%2FSTRONG%3E%20clause%20specifies%20a%20different%20collation%20than%20the%20column%20is%20using%20(and%20hence%20was%20used%20in%20creating%20the%20index)%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EHowever%2C%20the%20scenario%20that%20typically%20does%20cause%20a%20performance%20degradation%20and%20is%20characterized%20as%20being%20caused%20by%20an%20%22implicit%20conversion%22%20(even%20though%20that%20isn't%20the%20%3CEM%3Ereal%3C%2FEM%3E%20issue)%20is%20%3CEM%3Edatatype%3C%2FEM%3E%20precedence%3A%3C%2FSPAN%3E%3C%2FP%3E%3COL%3E%3CLI%3E%3CSPAN%3Ecolumn%20is%20%3CSTRONG%3EVARCHAR%3C%2FSTRONG%3E.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Ecolumn%20is%20using%20a%20SQL%20Server%20collation%20(i.e.%20name%20starting%20with%20%22%3CSTRONG%3ESQL_%3C%2FSTRONG%3E%22).%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Ecolumn%20is%20indexed.%3C%2FSPAN%3E%3C%2FLI%3E%3CLI%3E%3CSPAN%3Ecolumn%20is%20compared%20to%20%3CSTRONG%3ENVARCHAR%3C%2FSTRONG%3E%20literal%20or%20variable%2C%20or%20%3CSTRONG%3ENVARCHAR%3C%2FSTRONG%3E%20column%20using%20the%20same%20collation%20(different%20collation%20would%20simply%20error)%3C%2FSPAN%3E%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%3CSPAN%3EI%20have%20a%20detailed%20write-up%20about%20this%20here%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fsqlquantumleap.com%2F2017%2F07%2F10%2Fimpact-on-indexes-when-mixing-varchar-and-nvarchar-types%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3EImpact%20on%20Indexes%20When%20Mixing%20VARCHAR%20and%20NVARCHAR%20Types%3C%2FA%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ETake%20care%2C%3CBR%20%2F%3ESolomon...%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2FSqlQuantumLift.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2FSqlQuantumLift.com%2F%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2FSqlQuantumLeap.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2FSqlQuantumLeap.com%2F%3C%2FA%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2FSQLsharp.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2FSQLsharp.com%2F%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-370368%22%20slang%3D%22en-US%22%3ESQL%20Collation%20and%20related%20performance%20impact%2C%20viewing%20collation%20in%20query%20plans%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-370368%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Feb%2004%2C%202013%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CP%3EIt%20has%20been%20a%20while%20since%20there%20has%20been%20activity%20on%20this%20blog%2C%20we%20as%20a%20team%20will%20be%20trying%20to%20post%20weekly%20going%20forwards%20so%20as%20to%20share%20what%20we%20do%20on%20a%20regular%20basis%20%E2%80%93%20happy%20reading!%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20was%20posed%20a%20question%20by%20a%20fellow%20PFE%20during%20his%20performance%20%60engagement.%20The%20specific%20question%20was%20whether%20a%20Literal%20predicate%20which%20had%20different%20accent%20sensitivity%20or%20collation%20when%20compared%20to%20a%20column%20would%20trigger%20an%20implicit%20conversion%20resulting%20in%20potential%20performance%20degradation%20and%20more%20importantly%20where%20in%20the%20plan%20would%20you%20see%20this.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECollation%20settings%20exist%20at%20the%20Server%20level%2C%20the%20Database%20Level%20and%20potentially%20defined%20at%20the%20column%20level%20as%20well.%20By%20default%20if%20no%20collation%20is%20specified%20at%20the%20column%20level%20when%20creating%20the%20table%2C%20database%20collation%20is%20assumed%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20check%20the%20collation%20of%20a%20database%3A%3C%2FP%3E%0A%3CPRE%3ESELECT%20DATABASEPROPERTYEX('TEMPDB'%2C'COLLATION')%0A%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20244px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89375i57C218F74CF33C36%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20further%20to%20see%20the%20collation%20of%20a%20column%2C%20we%20can%20use%20the%20query%20below%3C%2FP%3E%0A%3CPRE%3ESELECT%20object_name(object_id)%20AS%20ObjectName%0A%20%2Cname%20AS%20ColName%0A%20%2Ccollation_name%0AFROM%20sys.columns%0AWHERE%20object_id%20%3D%20object_id('testcollate')%0A%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20396px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89376i9232411B1ECD07C7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ENow%20moving%20on%20to%20more%20of%20the%20Performance%20aspects%20of%20the%20question%3A%3C%2FP%3E%0A%3CP%3ESame%20Collation%20comparison%20%E2%80%93%20If%20the%20literal%20or%20columns%20being%20compared%20are%20the%20same%20collection%2C%20we%20have%20no%20problem%20as%20we%20can%20see%20below%3C%2FP%3E%0A%3CPRE%3ESET%20NOCOUNT%20ON%0A%0AUSE%20tempdb%0AGO%0A%0ADROP%20TABLE%20testcollate%0AGO%0A%0ACREATE%20TABLE%20testcollate%20(%0A%20myid%20INT%20identity%0A%20%2Cmyname%20VARCHAR(810)%0A%20)%0AGO%0A%0AINSERT%20INTO%20testcollate%0AVALUES%20(replicate('a'%2C%20800))%20GO%2010000%0A%0AINSERT%20INTO%20testcollate%0AVALUES%20('Denzil')%0AGO%0A%0ACREATE%20INDEX%20myind%20ON%20testcollate%20(myname)%0AGO%0A%0ASET%20STATISTICS%20IO%20ON%0AGO%0A%0ASELECT%20myname%0AFROM%20testcollate%0AWHERE%20myname%20%3D%20'Denzil'%0A%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20577px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89377iAA467C04B5922844%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETable%20'testcollate'.%20%3CMARK%3EScan%20count%201%2C%20logical%20reads%205%3C%2FMARK%3E%2C%20physical%20reads%200%2C%20read-ahead%20reads%200%2C%20lob%20logical%20reads%200%2C%20lob%20physical%20reads%200%2C%20lob%20read-ahead%20reads%200.%3C%2FP%3E%0A%3CP%3EIf%20the%20literal%20being%20compared%20has%20a%20different%20collation%20or%20is%20explicitly%20converted%20to%20a%20different%20collation%2C%20collation%20precedence%20kicks%20in-%20%3CA%20href%3D%22http%3A%2F%2Fmsdn.microsoft.com%2Fen-US%2Flibrary%2Fms179886(v%3DSQL.90).aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20http%3A%2F%2Fmsdn.microsoft.com%2Fen-US%2Flibrary%2Fms179886(v%3DSQL.90).aspx%3C%2FA%3E%20%3C%2FP%3E%0A%3CP%3EIf%20the%20literal%20has%20an%20explicit%20collation%2C%20we%20will%20get%20a%20plan%20with%20a%20CONVERT%2FSCAN.%20The%20CONVERT_IMPLICIT%20by%20itself%20gives%20no%20indication%20this%20is%20due%20to%20a%20Collation%20difference%20per%20say%2C%20in%20fact%20almost%20looks%20like%20it%20could%20be%20some%20data%20type%20mismatch%20which%20it%20is%20not%20and%20on%20the%20constant%20side%2C%20there%20is%20a%20CONVERT%20given%20that%20we%20were%20explicitly%20collating%20it%20to%20a%20particular%20collation.%3C%2FP%3E%0A%3CPRE%3ESELECT%20myname%0AFROM%20testcollate%0AWHERE%20myname%20%3D%20'Denzil'%20collate%20SQL_Latin1_General_Cp437_CS_AS%0A%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20604px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89378iBC19469A88889A97%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ETable%20'testcollate'.%20%3CMARK%3EScan%20count%201%2C%20logical%20reads%201240%3C%2FMARK%3E%2C%20physical%20reads%200%2C%20read-ahead%20reads%200%2C%20lob%20logical%20reads%200%2C%20lob%20physical%20reads%200%2C%20lob%20read-ahead%20reads%200.%3C%2FP%3E%0A%3CP%3EYou%20will%20have%20to%20look%20at%20the%20input%2Foutput%20trees%20to%20actually%20see%20where%20the%20change%20in%20collation%20is%20happening%20as%20that%20is%20not%20exposed%20in%20the%20query%20plan%20itself%20as%20far%20as%20I%20know.%20I%20am%20using%20QUERYTRACEON%20which%20is%20an%20undocumented%20command%20in%20order%20to%20demonstrate%20collation%20related%20converts.%20QueryTraceON%20is%20blogged%20about%20in%20several%20places%20%E2%80%93%20See%20Benjamin%20blog%20(%20%3CA%20href%3D%22http%3A%2F%2Fwww.benjaminnevarez.com%2F2012%2F04%2Fmore-undocumented-query-optimizer-trace-flags%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%20Query%20Optimizer%20Trace%20Flags%20%3C%2FA%3E%20)%3C%2FP%3E%0A%3CPRE%3ESELECT%20myname%0AFROM%20testcollate%0AWHERE%20myname%20%3D%20'Denzil'%20collate%20SQL_Latin1_General_Cp437_CS_AS%0AOPTION%20(%0A%20RECOMPILE%0A%20%2CQueryTraceon%208606%0A%20)%0AGO%0A%3C%2FPRE%3E%0A%3CPRE%3E****************************************%0A***%20Input%20Tree%3A%20***%0A%0A%20%20%20%20%20%20%20%20LogOp_Project%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btestcollate%5D.myid%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btestcollate%5D.myname%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20LogOp_Select%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LogOp_Get%20TBL%3A%20testcollate%20testcollate%20TableID%3D773577794%20TableReferenceID%3D0%20IsRow%3A%20COL%3A%20IsBaseRow1001%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ScaOp_Comp%20x_cmpEq%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%3CMARK%3EScaOp_Convert%20varchar%20collate%20520142856%3C%2FMARK%3E%2CNull%2CVar%2CTrim%2CML%3D810%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%3CMARK%3EScaOp_Identifier%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btestcollate%5D.myname%3C%2FMARK%3E%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ScaOp_Const%20TI(varchar%20collate%20520142856%2CVar%2CTrim%2CML%3D6)%20XVAR(varchar%2COwned%2CValue%3DLen%2CData%20%3D%20(6%2CDenzil))%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20AncOp_PrjList%20%0A%3C%2FPRE%3E%0A%3CP%3EIn%20order%20to%20get%20the%20Collation%20Name%20of%20that%20ID%3A%3C%2FP%3E%0A%3CPRE%3ESELECT%20convert(SYSNAME%2C%20collationpropertyfromid(520142856%2C%20'name'))%0A%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20244px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89379i89CFEF1F11D03C08%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIf%20the%20Column%20has%20different%20collation%20than%20the%20database%20would%20we%20need%20to%20collate%20the%20literal%20to%20the%20column%20itself%3F%20%26nbsp%3BIs%20the%20constant%20or%20literal%20collated%20to%20the%20collation%20of%20the%20Database%20or%20the%20collation%20of%20the%20column%20given%20they%20both%20are%20different%3F%3C%2FP%3E%0A%3CPRE%3EUSE%20tempdb%0AGO%0A%0ADROP%20TABLE%20testcollate%0AGO%0A%0ACREATE%20TABLE%20testcollate%20(%0A%20myid%20INT%20identity%20PRIMARY%20KEY%0A%20%2Cmyname%20VARCHAR(810)%20collate%20SQL_Latin1_General_Cp437_CS_AS%0A%20)%0AGO%0A%0AINSERT%20INTO%20testcollate%0AVALUES%20(replicate('a'%2C%20800))%20GO%2010000%0A%0AINSERT%20INTO%20testcollate%0AVALUES%20('Denzil')%0AGO%0A%0ACREATE%20INDEX%20myind%20ON%20testcollate%20(myname)%0AGO%0A%0A--As%20you%20can%20see%20below%2C%20the%20Database%20collation%20is%20different%20than%20the%20Column%20collation%0ASELECT%20DATABASEPROPERTYEX('TEMPDB'%2C%20'COLLATION')%20AS%20DBCollation%0A%20%2Cobject_name(object_id)%20AS%20ObjectName%0A%20%2Cname%20AS%20ColName%0A%20%2Ccollation_name%20AS%20ColumnCollation%0AFROM%20sys.columns%0AWHERE%20object_id%20%3D%20object_id('testcollate')%0A%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20610px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89380i0FB31923F3108824%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3ESELECT%20*%0AFROM%20testcollate%0AWHERE%20myname%20%3D%20'Denzil'%0A%3C%2FPRE%3E%0A%3CP%3EWe%20actually%20get%20a%20Seek%20here%2C%20which%20means%20the%20literal%20here%20was%20converted%20to%20the%20collation%20of%20the%20column%20and%20not%20the%20database.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20611px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89381i990080C1F1B44D58%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3ESELECT%20*%0AFROM%20testcollate%0AWHERE%20myname%20%3D%20'Denzil'%0AOPTION%20(%0A%20RECOMPILE%0A%20%2CQueryTraceon%208606%0A%20)%0A%3C%2FPRE%3E%0A%3CPRE%3E****************************************%0A***%20Input%20Tree%3A%20***%0A%0A%20%20%20%20%20%20%20%20LogOp_Project%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btestcollate%5D.myid%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btestcollate%5D.myname%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20LogOp_Select%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20LogOp_Get%20TBL%3A%20testcollate%20testcollate%20TableID%3D741577680%20TableReferenceID%3D0%20IsRow%3A%20COL%3A%20IsBaseRow1001%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ScaOp_Comp%20x_cmpEq%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20ScaOp_Identifier%20QCOL%3A%20%5Btempdb%5D.%5Bdbo%5D.%5Btestcollate%5D.myname%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%3CMARK%3EScaOp_Const%20TI(varchar%20collate%20520142856%2CVar%2CTrim%2CML%3D6)%20XVAR(varchar%2COwned%2CValue%3DLen%2CData%20%3D%20(6%2CDenzil))%3C%2FMARK%3E%0A%0A%20%20%20%20%20%20%20%20%20%20%20%20AncOp_PrjList%0A%3C%2FPRE%3E%0A%3CP%3EHere%20you%20can%20see%20the%20Constant%20is%20being%20collated%20to%20the%20Column%20collation%20and%20not%20the%20database%20collation.%3C%2FP%3E%0A%3CPRE%3ESELECT%20convert(SYSNAME%2C%20collationpropertyfromid(520142856%2C%20'name'))%0A%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20245px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F89382i36DB1BA931EBC610%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThere%20have%20been%20several%20blogs%20on%20Collation%20conflict%20and%20how%20to%20resolve%20that%20so%20I%20intentionally%20stayed%20away%20from%20that.%20Arvind%20also%20has%20a%20blog%20on%20some%20collation%20and%20performance%20scenarios%20which%20is%20a%20great%20read%20-%20%3CA%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Farvindsh%2Farchive%2F2012%2F09%2F18%2Fsql-collation-and-performance.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3E%20SQL%20collation%20and%20performance%3C%2FA%3E%20%3C%2FP%3E%0A%3CP%3E%20%3CSTRONG%3E-Denzil%20Ribeiro%20%3C%2FSTRONG%3E%20%2C%20SQL%20Dedicated%20Premier%20Field%20Engineer%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-370368%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Feb%2004%2C%202013%20It%20has%20been%20a%20while%20since%20there%20has%20been%20activity%20on%20this%20blog%2C%20we%20as%20a%20team%20will%20be%20trying%20to%20post%20weekly%20going%20forwards%20so%20as%20to%20share%20what%20we%20do%20on%20a%20regular%20basis%20%E2%80%93%20happy%20reading!I%20was%20posed%20a%20question%20by%20a%20fellow%20PFE%20during%20his%20performance%20%60engagement.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-370368%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDenzilRibeiro%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Version history
Last update:
‎Apr 28 2020 12:51 PM
Updated by: