InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:28:14 GMT20http://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspx[quote][b]uncle_boris (12/4/2012)[/b][hr]Hello, I have read your replies and looked at the link so hopefully I have the cause of the SEEK / SCAN action clear???When passing in a NVarchar to be compared to the NC Index of VarChar type one value has to be converted.Based on the link you sent the Varchar is lower in order so this value must be converted. Because this value is in the index ALL values must be converted to compare, therefore a SCAN must be performed.CONVERT_IMPLICIT(nvarchar(19),[clientname].[dbo].[CARD].[CARD_NUMBER],0)=N'123456789'When a Varchar is compared to a Varchar no conversion is necessary therefore a SEEK in done.If I convert the field to a BIGINT datatype a conversion is needed between a NVarchar and a BIGINT. But this time the BIGINT is higher up the list so the NVARCHAR get converted to BIGINT, therefore it only happens once so a SEEK can be performed.Seek Keys[1]: Prefix: [clientname].[dbo].[CARD].CARD_NUMBER = Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0))thanks[/quote]that is exactly right. its also why one of the most common mistakes in writing queries is data type mismatch. as you have found a mismatch between the column data type and the datatype it is being compared to will cause an implicit conversion and which side has the data type of higher importance will take precidence. its why i always like to avoid implicit conversions where ever possible and use CONVERT or CAST to make the types match by converting the constant value in the where clause[code="sql"]SELECT * FROM MyTable WHERE SomeCol = CAST('2012-12-11' AS DATETIME)[/code]Tue, 04 Dec 2012 11:11:18 GMTCapnHectorhttp://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspxHello, I have read your replies and looked at the link so hopefully I have the cause of the SEEK / SCAN action clear???When passing in a NVarchar to be compared to the NC Index of VarChar type one value has to be converted.Based on the link you sent the Varchar is lower in order so this value must be converted. Because this value is in the index ALL values must be converted to compare, therefore a SCAN must be performed.CONVERT_IMPLICIT(nvarchar(19),[clientname].[dbo].[CARD].[CARD_NUMBER],0)=N'123456789'When a Varchar is compared to a Varchar no conversion is necessary therefore a SEEK in done.If I convert the field to a BIGINT datatype a conversion is needed between a NVarchar and a BIGINT. But this time the BIGINT is higher up the list so the NVARCHAR get converted to BIGINT, therefore it only happens once so a SEEK can be performed.Seek Keys[1]: Prefix: [clientname].[dbo].[CARD].CARD_NUMBER = Scalar Operator(CONVERT_IMPLICIT(bigint,[@1],0))thanksTue, 04 Dec 2012 00:55:02 GMTuncle_borishttp://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspxThe following link should help you understand data type precedence better:[b][url]http://msdn.microsoft.com/en-us/library/ms190309(v=sql.100).aspx[/url][/b]Fri, 30 Nov 2012 07:24:00 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspx[quote][b]UncleBoris (11/29/2012)[/b][hr]thanks, I see the conversion but what I do not get at this point is if a conversion is made then why wouldn't a SEEK happen like if there was no N'?As mentioned if I convert the table field to BIGINT and run the query with the N' clause a SEEK is performed, but of course it does not if the table field type is VARCHAR.Assuming this is the case and a SCAN must be performed then the overhead from a lot of transactions is very high in extra reads and cpu time.[/quote]Converting the VARCHAR column to BIGINT changes where the implicit conversion occurs. Instead of converting the value of the column in each row, the constant value is implicitly converted. Again, you can see this by looking at the properties of the seek operator in your actual execution plan.Thu, 29 Nov 2012 23:25:41 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspxthanks, I see the conversion but what I do not get at this point is if a conversion is made then why wouldn't a SEEK happen like if there was no N'?As mentioned if I convert the table field to BIGINT and run the query with the N' clause a SEEK is performed, but of course it does not if the table field type is VARCHAR.Assuming this is the case and a SCAN must be performed then the overhead from a lot of transactions is very high in extra reads and cpu time.Thu, 29 Nov 2012 21:57:18 GMTUncleBorishttp://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspxI had it backwards. SQL Server needed to implicitly convert each of the varchar values in your table to nvarchar prior to comparing it to your constant value N'123456789', thus requiring it to scan the index rather than just doing a seek.If you right click on the index scan in your actual execution plan and click on properties, then scroll down the properties page to the bottom and look at the Predicate, you will see the implicit conversion that SQL Server needed to accomplish for the comparision.Thu, 29 Nov 2012 17:43:38 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspxthanks for the reply, for my own reference I am trying to understand how this would make any difference in the way the index is seeked or scanned.Thu, 29 Nov 2012 16:49:41 GMTuncle_borishttp://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspxCould be due to the implicit conversion from nvarchar to varchar you introduce by putting the N at the front of the string (N'1234567').Thu, 29 Nov 2012 16:14:39 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1390897-1550-1.aspxHello, I am trying to work out why an N' before the value in the WHERE clause would make a difference in the index plan used and in this case a large performance difference.I am getting different stats when the [N'] is before the value. The values being sent are numeric not unicode.The queries from a Java app were flagged as being slow. When run in SSMS they were OK. I noticed the difference was the profiler had an N' before the WHERE clause value and when I copied the query to SSMS I had omitted this.Table Size:570691 rowsNon Clustered Index on Card_NumberCard_Number Data Type: VARCHAR(19) [although only holds numeric values of BIGINT length]I run this query from the profiler.SELECT card0_.CARD_ID AS CARD1_1058_,card0_.CARD_NUMBER AS CARD2_1058_.[other fields].FROM dbo.CARD card0_WHERE card_number = N'123456789' The stats are:Table 'CARD'. Scan count 1, logical reads 1868, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.CPU time = 156 ms, elapsed time = 168 ms.The query plan does a non clustered index SCAN (on card_number) and then a key lookup for the rest of the fieldsI have tried to attach the query plans.I then run this query:SELECT card0_.CARD_ID AS CARD1_1058_,card0_.CARD_NUMBER AS CARD2_1058_.[other fields].FROM dbo.CARD card0_WHERE card_number = '123456789' Table 'CARD'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.CPU time = 15 ms, elapsed time = 1 ms.The query plan does a non clustered index SEEK (on card_number) and then a key lookup for the rest of the fieldsSo what is annoying me is trying to find out why this is happening as the only difference is the N'?I can see the times in the profiler for duration, reads and CPU match my SSMS findings so it certainly looks like the N' Query is being sent and executed by SQL.On my test box I converted the datatype for the card_number field from VARCHAR(19) to BIGINT and re-created the index. When running the query again with 'N the output was only 6 reads.Can anybody explain why this is happening as it definitely looks like a straight SEEK can not be achieved from the non clustered index when the WHERE clause is prefixed with an N', thus a SCAN is forced? Why I do not know!!From someones experience of this issue can this be prevented in any way i.e by seeing if the front end app can be stopped from sending the unicode N' with every value?If anymore information is needed please let me know.thanks for any helpThu, 29 Nov 2012 15:38:18 GMTuncle_boris