Home
%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%3E%0ASELECT%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%3E%0ASELECT%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%3E%0ASET%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%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%3E%0ASELECT%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Query%20Optimizer%20Trace%20Flags%20%3C%2FA%3E%20)%3C%2FP%3E%0A%3CPRE%3E%0ASELECT%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%0A%3CPRE%3E%0A****************************************%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%3E%0ASELECT%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%3E%0AUSE%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%3E%0ASELECT%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%0A%3CPRE%3E%0ASELECT%20*%0AFROM%20testcollate%0AWHERE%20myname%20%3D%20'Denzil'%0AOPTION%20(%0A%20RECOMPILE%0A%20%2CQueryTraceon%208606%0A%20)%0A%3C%2FPRE%3E%0A%0A%3CPRE%3E%0A****************************************%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%0A%0A%3CP%3EHere%20you%20can%20see%20the%20Constant%20is%20being%20collated%20to%20the%20Column%20collation%20and%20not%20the%20database%20collation.%3C%2FP%3E%0A%0A%3CPRE%3E%0ASELECT%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%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%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%3Edatabase%20engine%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%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%20noopener%20noreferrer%20noopener%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%20noopener%20noreferrer%20noopener%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%20noopener%20noreferrer%20noopener%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%20noopener%20noreferrer%20noopener%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
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
Occasional Visitor

Hi there. A few clarifications need to be made to the info presented here.

 

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

  1. String literals and variables initially use the database's default collation as their collation.
  2. Assuming no explicit collation set via COLLATE clause on the predicate, the collation of the literal or variable is irrelevant (from a performance perspective, though for VARCHAR data it can be relevant if any characters having values 128 - 255 / 0x80 - 0xFF are present) since collation precedence uses the collation of the column.
  3. implicit conversions, by themselves, are not the issue, as whatever performance degradation they cause is minimal and likely barely noticeable.

 

 

If the literal being compared has a different collation or is explicitly converted to a different collation, collation precedence kicks in

  1. yes, if there is any difference in collation, for any reason, collation precedence will determine which collation to use, of if both sides have equal weight, then an error will be thrown.
  2. a literal (or variable) always starts out with the database's default collation. But this doesn't matter since collation precedence will use the collation of the column.
  3. an explicit collation (i.e. set via the COLLATE {collation_name} clause on a predicate) will override the column's collation, but if there is any degradation in performance in this scenario (and that's usually only possible, though not even guaranteed, if the column is indexed), then nobody should really be surprised because it was an explicit change that is clearly stated in the query ;)

 

Is the literal collated to the collation of the Database or the collation of the column given they both are different? ... We actually get a Seek here, which means the literal here was converted to the collation of the column and not the database.

The question and answer here are both poorly phrased and hence can be misleading.

  1. the actual answer to the question is: BOTH. Initially (i.e. when the query is being parsed), literals and variables use the database's default collation. Then, if compared to a column having a different collation than the database (or COLLATE clause is used), the collation will be converted to the column's collation (or to what is specified by the COLLATE clause).
  2. literals (and variables) need to have a collation, so it doesn't make sense that they would be converted to the database's collation; they are already using the database's collation.

 

With regards to a collation difference causing performance degradation, that can only really happen under the following conditions:

  1. column is indexed, and ...
  2. COLLATE clause is specified on the predicate
  3. COLLATE clause specifies a different collation than the column is using (and hence was used in creating the index)

 

However, the scenario that typically does cause a performance degradation and is characterized as being caused by an "implicit conversion" (even though that isn't the real issue) is datatype precedence:

  1. column is VARCHAR.
  2. column is using a SQL Server collation (i.e. name starting with "SQL_").
  3. column is indexed.
  4. column is compared to NVARCHAR literal or variable, or NVARCHAR column using the same collation (different collation would simply error)

I have a detailed write-up about this here:

Impact on Indexes When Mixing VARCHAR and NVARCHAR Types

 

Take care,
Solomon...

https://SqlQuantumLift.com/
https://SqlQuantumLeap.com/
https://SQLsharp.com/