InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 20:30:22 GMT20http://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[url]http://msdn.microsoft.com/en-us/library/bb510478%28v=sql.105%29.aspx[/url]Thu, 11 Oct 2012 03:07:08 GMTGilaMonsterhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxI heard a lot a word 'FORCESEEk' can anyone explain me what is this?Thu, 11 Oct 2012 02:15:09 GMTkapil_kkhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxGood question, though I answered wrong!:(Wed, 22 Feb 2012 11:55:23 GMTSathishKhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxA good question... Thank you.Tue, 21 Feb 2012 17:22:07 GMTJagadish Kumar Punnapuhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]L' Eomot Inversé (2/18/2012)[/b][hr][quote][b]Cliff Jones (2/18/2012)[/b][hr][quote][b]DBA_Dom (2/17/2012)[/b][hr][quote][b]bitbucket-25253 (2/17/2012)[/b][hr][quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]Cliff Jones (2/17/2012)[/b][hr]Interesting but I don't think I want my developers to discover the FORCESEEK hint.[/quote]This is [i]completely[/i] the wrong approach, in my opinion. I prefer to work [i]with[/i] developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. [b] I find that working positively with developers produces benefits for everyone.[/b][/quote]Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is + 100[/quote]I have to say that I am in total agreement. It is far more beneficial to give people knowledge than to keep it from them as long as you teach them to use it properly. Meaning that using hints as a last resort.[/quote]Actually I agree also, my post was a bit tongue in cheek. But with 50 developers sometimes bad practices get copied around like a virus before you have a chance to educate or remind. Code reviews would help if only we did them more than we currently do.[/quote]I'm sure that making sure developers understand what features are available, where those features are most likely to be beneficial, what potential issues there are with these features, and how they should go about evaluating the pros and cons of using a feature in a situation where they are considering it is the only sensible way to go (this applies to any language, not just SQL dialects); if the time taken to educate developers about new features is sufficiently long that they may go and misuse them before the education happens, the thing to do is to fix the working system so that the education happens in a more timely fashion. Yes there need to be warnings about some features (but perhaps not as extreme as the labelling suggested in my earlier post :-D - ) - in fact as DBA_Dom suggests developers have to be told that using hints to override the optimiser is a last resort, but a new query hint (which is what we have here) hardly needs new guidance since some query hints have been around for a long time and if the developers don't yet know that query hints are a last resort that little piece of education is more than a decade overdue so something is seriously wrong.[/quote]Yes, I think you hit the nail on the head. I work at a very fast paced and very successful software company and I don’t think anyone in our organization would use a Query Hint without checking with me to be sure it was appropriate. When you are dealing with a large development team sometimes that is the level at which you have to train. You can spend a lot of time training 50 dot net programmers how to write SQL but only a percentage will listen and only a percentage will remember everything. Would I spend 10 minutes of valuable training time to discuss the FORCESEEK Hint? No. Would I spend 30 minutes showing them why they shouldn’t try to out-guess the Optimizer with query hints? Yes. So it is easy to say give everyone all the details but sometimes it just not that simple.Sat, 18 Feb 2012 20:50:51 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Cliff Jones (2/18/2012)[/b][hr][quote][b]DBA_Dom (2/17/2012)[/b][hr][quote][b]bitbucket-25253 (2/17/2012)[/b][hr][quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]Cliff Jones (2/17/2012)[/b][hr]Interesting but I don't think I want my developers to discover the FORCESEEK hint.[/quote]This is [i]completely[/i] the wrong approach, in my opinion. I prefer to work [i]with[/i] developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. [b] I find that working positively with developers produces benefits for everyone.[/b][/quote]Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is + 100[/quote]I have to say that I am in total agreement. It is far more beneficial to give people knowledge than to keep it from them as long as you teach them to use it properly. Meaning that using hints as a last resort.[/quote]Actually I agree also, my post was a bit tongue in cheek. But with 50 developers sometimes bad practices get copied around like a virus before you have a chance to educate or remind. Code reviews would help if only we did them more than we currently do.[/quote]I'm sure that making sure developers understand what features are available, where those features are most likely to be beneficial, what potential issues there are with these features, and how they should go about evaluating the pros and cons of using a feature in a situation where they are considering it is the only sensible way to go (this applies to any language, not just SQL dialects); if the time taken to educate developers about new features is sufficiently long that they may go and misuse them before the education happens, the thing to do is to fix the working system so that the education happens in a more timely fashion. Yes there need to be warnings about some features (but perhaps not as extreme as the labelling suggested in my earlier post :-D - ) - in fact as DBA_Dom suggests developers have to be told that using hints to override the optimiser is a last resort, but a new query hint (which is what we have here) hardly needs new guidance since some query hints have been around for a long time and if the developers don't yet know that query hints are a last resort that little piece of education is more than a decade overdue so something is seriously wrong.Sat, 18 Feb 2012 19:53:16 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]DBA_Dom (2/17/2012)[/b][hr][quote][b]bitbucket-25253 (2/17/2012)[/b][hr][quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]Cliff Jones (2/17/2012)[/b][hr]Interesting but I don't think I want my developers to discover the FORCESEEK hint.[/quote]This is [i]completely[/i] the wrong approach, in my opinion. I prefer to work [i]with[/i] developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. [b] I find that working positively with developers produces benefits for everyone.[/b][/quote]Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is + 100[/quote]I have to say that I am in total agreement. It is far more beneficial to give people knowledge than to keep it from them as long as you teach them to use it properly. Meaning that using hints as a last resort.[/quote]Actually I agree also, my post was a bit tongue in cheek. But with 50 developers sometimes bad practices get copied around like a virus before you have a chance to educate or remind. Code reviews would help if only we did them more than we currently do.Sat, 18 Feb 2012 09:23:53 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]bitbucket-25253 (2/17/2012)[/b][hr][quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]Cliff Jones (2/17/2012)[/b][hr]Interesting but I don't think I want my developers to discover the FORCESEEK hint.[/quote]This is [i]completely[/i] the wrong approach, in my opinion. I prefer to work [i]with[/i] developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. [b] I find that working positively with developers produces benefits for everyone.[/b][/quote]Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is + 100[/quote]I have to say that I am in total agreement. It is far more beneficial to give people knowledge than to keep it from them as long as you teach them to use it properly. Meaning that using hints as a last resort.Fri, 17 Feb 2012 21:52:59 GMTDBA_Domhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]Cliff Jones (2/17/2012)[/b][hr]Interesting but I don't think I want my developers to discover the FORCESEEK hint.[/quote]This is [i]completely[/i] the wrong approach, in my opinion. I prefer to work [i]with[/i] developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. [b] I find that working positively with developers produces benefits for everyone.[/b][/quote]Bold set by this poster. Now most would say +1 but for what I would say to SQL Kiwi's post is + 100Fri, 17 Feb 2012 21:31:43 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Cliff Jones (2/17/2012)[/b][hr]Interesting but I don't think I want my developers to discover the FORCESEEK hint.[/quote]This is [i]completely[/i] the wrong approach, in my opinion. I prefer to work [i]with[/i] developers to share knowledge rather than hoping they stay in the dark. The most successful places I have worked have all had a healthy relationship between DBAs and developers, with regular sessions for each team to share ideas and techniques. I find that working positively with developers produces benefits for everyone.Fri, 17 Feb 2012 21:22:58 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxI like it. I ran into this exact same issue when tuning some users queries. It is a little interesting how poorly this implicit conversion performs since most all data used in system views, tables and procedures is nvarchar but a good portion of existing user data is still varchar.Fri, 17 Feb 2012 14:15:47 GMTDBA_Domhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxGood straightforward question. Thanks for submitting.Fri, 17 Feb 2012 12:28:36 GMTBritt Cluffhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]L' Eomot Inversé (2/17/2012)[/b][hr]I guess FORCESEEK always works, though (?).[/quote]Nothing [i]always[/i] works :-). FORCESEEK will result in a plan with a seek operation on the hinted table (or index + columns in the extended syntax after R2 SP1), or in query compilation failure (error 8622). It cannot be used to force the seek that occurs on a partitioned heap with no indexes though (shame, it would have made for a classic QotD: "can FORCESEEK be used successfully on a table with no indexes at all..."). There is a related FORCESCAN hint (again, introduced in R2 SP1).[quote]Of course that means it should be labelled with a trefoil (purple on yellow background), a skull and crossbones (black on white baground) and red text reading"High explosive, corrosive, and risk of allergic reaction; use only when necessary and even then with great care".[/quote]Oh I don't know. Anything is dangerous in the wrong hands; it's just another hint to be used when all else fails, and with due care.[/quote]Interesting but I don't think I want my developers to discover the FORCESEEK hint.Fri, 17 Feb 2012 12:12:24 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxgood question to round out the week - tksFri, 17 Feb 2012 12:01:34 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Revenant (2/17/2012)[/b][hr]The easiest one this year... thanks![/quote]Actually I chose NO because I knew that for a small enough table and/or the right lack of selectivity of the where predicate for the data actually in the table the optimiser would choose a scan anyway (it would choose a scan because that would give better performance than a seek). I'd forgotten about FORCESEEK, so I got it wrong - that was me being sloppy, since SQL 2008 R2 with latest SP and patches is my favorite toy, so I ought to know about it, so I'm not saying that for me it was hard. But other people may still be living with SQL 2008 or even SQL 2005 (or even SQL 7) and might not know about this shiny new hint, so for them it might be hard - they'd have to read up 2008 R2, using up to date documentation not the documentation of the RTM version, and not just SQL 2008 doumentation, to find the answer (unless they didn't know that the optimiser would take account of any statistics available to it when choosing a plan, in which case they would get it right by accident). I suspect the proportion of correct answers may be higher than it would have been if everone was aware that the optimiser might prefer a scan over seek plus bookmark lookups (wrong terminology: I've forgotten what those are called now, and I'm too lazy to hunt for the current term) when the scan would cost less.Fri, 17 Feb 2012 11:54:27 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxNice easy and straight forward question.Fri, 17 Feb 2012 10:22:14 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxI chose yes for a different reason, upon researching I found this article:[url]http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure[/url]I didn't think of the implicit conversion but ANSI_NULLS sounded like a reasonable option for index seek.Has anybody had any experience with ANSI_NULLS and what si your opinion about it?Thanks,"El" JerryFri, 17 Feb 2012 09:46:08 GMTEL Jerryhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxThe easiest one this year... thanks!Fri, 17 Feb 2012 09:33:55 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]msurasky-905715 (2/17/2012)[/b][hr]I find strange that when I tested this on my SQL Server 2008 box I always got Index Seeks (never a scan, even when the tables was defined with a VARCHAR(100) column and the stored procedure's parameter was a NVARCHAR(20) one).Can anybody explain this behaviour?[/quote]Read the first reply to this thread.Fri, 17 Feb 2012 08:58:28 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxI find strange that when I tested this on my SQL Server 2008 box I always got Index Seeks (never a scan, even when the tables was defined with a VARCHAR(100) column and the stored procedure's parameter was a NVARCHAR(20) one).Can anybody explain this behaviour?Fri, 17 Feb 2012 08:54:21 GMTmsurasky-905715http://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxThe seek with no indexes:[code="sql"]USE tempdb;-- Partitioning stuffCREATE PARTITION FUNCTION PF(integer) AS RANGE RIGHT FOR VALUES (NULL);CREATE PARTITION SCHEME PS AS PARTITION PF ALL TO ([PRIMARY]);-- Partitioned heapCREATE TABLE #T (col1 integer) ON PS (col1);INSERT #T VALUES (NULL);-- Seek (query plan below)SELECT t.col1 FROM #T AS t WHERE $PARTITION.PF(col1) = 2;-- Sadly, an error :(SELECT t.col1 FROM #T AS t WITH (FORCESEEK) WHERE $PARTITION.PF(col1) = 2;[/code][img]http://www.sqlservercentral.com/Forums/Attachment10887.aspx[/img]Fri, 17 Feb 2012 08:43:05 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]L' Eomot Inversé (2/17/2012)[/b][hr]I guess FORCESEEK always works, though (?).[/quote]Nothing [i]always[/i] works :-). FORCESEEK will result in a plan with a seek operation on the hinted table (or index + columns in the extended syntax after R2 SP1), or in query compilation failure (error 8622). It cannot be used to force the seek that occurs on a partitioned heap with no indexes though (shame, it would have made for a classic QotD: "can FORCESEEK be used successfully on a table with no indexes at all..."). There is a related FORCESCAN hint (again, introduced in R2 SP1).[quote]Of course that means it should be labelled with a trefoil (purple on yellow background), a skull and crossbones (black on white baground) and red text reading"High explosive, corrosive, and risk of allergic reaction; use only when necessary and even then with great care".[/quote]Oh I don't know. Anything is dangerous in the wrong hands; it's just another hint to be used when all else fails, and with due care.Fri, 17 Feb 2012 08:33:04 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]bitbucket-25253 (2/16/2012)[/b][hr] The comments so far are in themselves a wonderful lesson. And for that I thank SQL Kiwi, Hugo Kornelis and others to numerous to mention.[/quote]This discussion supports your point well. I always feel pity (well, OK, annoyance actually) for the people who "join" the discussion well along by making a point which indicates they have not bothered to read the valuable thread of comments previously submitted.Fri, 17 Feb 2012 08:19:16 GMTRose Budhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Thomas Abraham (2/17/2012)[/b][hr][quote][b]danielfountain (2/17/2012)[/b]Agreed - unfortunately due to the likelyhood of nincompoops its still a viable option![/quote]We're big on "Quality Initiatives" where I work, and our biggest customer is always sending along a Six Sigma Black Belt to work on them. However, my idea for a simple, but effective, quality program has yet to be adopted: "hire fewer idiots" :w00t:[/quote]In that case maybe you should concentrate on improving recruitment standards (which may mean firing half of the HR department and refusing to use recruitment agencies whose staff are not technically aware as well as HR aware).Fri, 17 Feb 2012 07:56:57 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]SQL Kiwi (2/16/2012)[/b][hr]It is certainly very good practice to match data types, but that is not the only way to obtain a seek here (assuming the default collation is a Windows one like Latin1_General_CI_AS rather than a SQL one like SQL_Latin1_General_CP1_CI_AS):[/quote]Even worse, it isn't even a guaranteed way to obtain a seek: if the table is small enough, optimiser can pick a scan regardless of whether the implicit conversion is there or not. Maybe degree of selectivity of the where clause (which depends on the frequency distribution of values for that field in the table) has an effect too.I guess FORCESEEK always works, though (?). Of course that means it should be labelled with a trefoil (purple on yellow background), a skull and crossbones (black on white baground) and red text reading"High explosive, corrosive, and risk of allergic reaction; use only when necessary and even then with great care".Fri, 17 Feb 2012 07:50:33 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxNice question! I chose "Yes" because I felt you can apply hints to this query to force the engine to use the index.Kwex.Fri, 17 Feb 2012 07:41:19 GMTKwexhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxGood question, thanks. Great follow on discussion.Fri, 17 Feb 2012 07:10:28 GMTDaniel Bowlinhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxI only was able to answer the question correctly by assumption.If the answer was no, then would there be a need to have an explanation. Every question has an explanation so therefore the answer is yes.Fri, 17 Feb 2012 06:29:09 GMTMike Is Herehttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Hugo Kornelis (2/17/2012)[/b][hr][quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]John Mitchell-245523 (2/17/2012)[/b][hr]Got it right, [color="#0000FF"][b]but I think the answer should be "it depends"[/b][/color]. On what? The selectivity of the data in Col [...] Therefore, in the second case, there'd be no way of optimising the stored procedure.[/quote][code="sql"]ALTER PROCEDURE dbo.QOD_Performance_1 @Get VARCHAR(100)ASBEGIN SET NOCOUNT ON; SELECT ft.ID, ft.Col FROM dbo.FirstTable AS ft WITH (FORCESEEK) WHERE ft.Col = @Get;END;[/code][/quote]That would force a seek (as asked in the QotD question), but it would not optimise the procedure (as John indicates).</nitpicking>[/quote]:laugh: That's beyond nitpicking, even in QotD comments!. I was primarily responding to the statement highlighted above (that the answer should be "it depends"). Clearly the only correct answer to the question "Can I alter the procedure to do an index seek?" is yes.If I were feeling nitpicky myself, I would have pointed out earlier that the original procedure and table (as written in the question) is likely to use a seek anyway, assuming the database (or table column) has a Windows collation, and the optimizer decides a seek would be best.Fri, 17 Feb 2012 05:44:55 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Koen Verbeeck (2/16/2012)[/b][hr]Nice question. I immediately chose "yes", because it would have been quite sad if you couldn't force a seek somehow :-)[/quote]Yes, I answered "yes" immediately, as I want to live in a world where it's possible. The leading nature of the question may be responsible for the high number of correct answers. However, thanks to OP for the question! Excellent discussion has resulted.Fri, 17 Feb 2012 05:36:31 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]danielfountain (2/17/2012)[/b]Agreed - unfortunately due to the likelyhood of nincompoops its still a viable option![/quote]We're big on "Quality Initiatives" where I work, and our biggest customer is always sending along a Six Sigma Black Belt to work on them. However, my idea for a simple, but effective, quality program has yet to be adopted: "hire fewer idiots" :w00t:Fri, 17 Feb 2012 05:34:00 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxgood question!!!thanks!Fri, 17 Feb 2012 05:13:41 GMTrfr.ferrarihttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspxHugoI was about to make precisely that point. Paul is quite right, since the question asks how to get the proc to do an index seek. But it is often inadvisable to overrule the query optimizer - you'd want to do plenty of testing before you put anything like that live.JohnFri, 17 Feb 2012 05:13:33 GMTJohn Mitchell-245523http://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]SQL Kiwi (2/17/2012)[/b][hr][quote][b]John Mitchell-245523 (2/17/2012)[/b][hr]Got it right, but I think the answer should be "it depends". On what? The selectivity of the data in Col [...] Therefore, in the second case, there'd be no way of optimising the stored procedure.[/quote][code="sql"]ALTER PROCEDURE dbo.QOD_Performance_1 @Get VARCHAR(100)ASBEGIN SET NOCOUNT ON; SELECT ft.ID, ft.Col FROM dbo.FirstTable AS ft WITH (FORCESEEK) WHERE ft.Col = @Get;END;[/code][/quote]That would force a seek (as asked in the QotD question), but it would not optimise the procedure (as John indicates).</nitpicking>Fri, 17 Feb 2012 05:09:00 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Hugo Kornelis (2/17/2012)[/b][hr][quote][b]danielfountain (2/17/2012)[/b][hr][quote][b]Hugo Kornelis (2/17/2012)[/b][hr]Nice, easy question - though I'm sad that as of this time, 8% of respondends got it wrong.There is an error in the explanation, though. You can not avoid the implicit conversion. You can only change it. Making the parameter varchar(100) instead of nvarchar(20) means that now the implicit conversion from an implicit conversion from varchar to nvvarchar during the scan, to an implicit conversion from nvarchar to varchar during the procedure call.Or you can leave the parameter as is and add an explicit cast in the query. Again, not avoiding the conversion, but making it explicit.[/quote]Surely that is assuming that the procedure is passed an nvarchar at the moment. Surely it could be being passed a varchar that is converted to an nvarchar for the proc and back to a varchar for the seek?Perhaps the above is the case - in which case you are getting rid of all conversions.[/quote]True.In that case, the best way to improve performance is to fire the nincompoop developer who came up with the crazy idea of making the parameter nvarchar. :hehe:[/quote]Agreed - unfortunately due to the likelyhood of nincompoops its still a viable option!Fri, 17 Feb 2012 05:01:29 GMTdanielfountainhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]John Mitchell-245523 (2/17/2012)[/b][hr]Got it right, but I think the answer should be "it depends". On what? The selectivity of the data in Col [...] Therefore, in the second case, there'd be no way of optimising the stored procedure.[/quote][code="sql"]ALTER PROCEDURE dbo.QOD_Performance_1 @Get VARCHAR(100)ASBEGIN SET NOCOUNT ON; SELECT ft.ID, ft.Col FROM dbo.FirstTable AS ft WITH (FORCESEEK) WHERE ft.Col = @Get;END;[/code]Fri, 17 Feb 2012 04:59:06 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]danielfountain (2/17/2012)[/b][hr][quote][b]Hugo Kornelis (2/17/2012)[/b][hr]Nice, easy question - though I'm sad that as of this time, 8% of respondends got it wrong.There is an error in the explanation, though. You can not avoid the implicit conversion. You can only change it. Making the parameter varchar(100) instead of nvarchar(20) means that now the implicit conversion from an implicit conversion from varchar to nvvarchar during the scan, to an implicit conversion from nvarchar to varchar during the procedure call.Or you can leave the parameter as is and add an explicit cast in the query. Again, not avoiding the conversion, but making it explicit.[/quote]Surely that is assuming that the procedure is passed an nvarchar at the moment. Surely it could be being passed a varchar that is converted to an nvarchar for the proc and back to a varchar for the seek?Perhaps the above is the case - in which case you are getting rid of all conversions.[/quote]True.In that case, the best way to improve performance is to fire the nincompoop developer who came up with the crazy idea of making the parameter nvarchar. :hehe:Fri, 17 Feb 2012 04:58:38 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Hugo Kornelis (2/17/2012)[/b][hr]Nice, easy question - though I'm sad that as of this time, 8% of respondends got it wrong.There is an error in the explanation, though. You can not avoid the implicit conversion. You can only change it. Making the parameter varchar(100) instead of nvarchar(20) means that now the implicit conversion from an implicit conversion from varchar to nvvarchar during the scan, to an implicit conversion from nvarchar to varchar during the procedure call.Or you can leave the parameter as is and add an explicit cast in the query. Again, not avoiding the conversion, but making it explicit.[/quote]Surely that is assuming that the procedure is passed an nvarchar at the moment. Surely it could be being passed a varchar that is converted to an nvarchar for the proc and back to a varchar for the seek?Perhaps the above is the case - in which case you are getting rid of all conversions.Fri, 17 Feb 2012 04:26:32 GMTdanielfountainhttp://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]Hugo Kornelis (2/17/2012)[/b][hr][quote][b]cengland0 (2/17/2012)[/b][hr]I immediately thought of using a hint to force the use of the index. Is that wrong somehow?[code="plain"]SELECT ID,Col FROM FirstTable WITH INDEX(IX_Col) WHERE Col = @Get; [/code][/quote]Not wrong, just less good. With this hint, I expect you to get an index scan, which could be a bit better than a scan of the clustered index, but still far worse than an index seek.[/quote]The question is: Can I alter the procedure to do an index seek? So, I immediately thought of adding the hint and that would force the use of the index. I then answered Yes to the question without even considering any other ways to cause the index to work naturally by changing the data type for example.In other words, I got the question right but not for the reason specified.Fri, 17 Feb 2012 03:18:22 GMTcengland0http://www.sqlservercentral.com/Forums/Topic1253572-1222-1.aspx[quote][b]cengland0 (2/17/2012)[/b][hr]I immediately thought of using a hint to force the use of the index. Is that wrong somehow?[code="plain"]SELECT ID,Col FROM FirstTable WITH INDEX(IX_Col) WHERE Col = @Get; [/code][/quote]Not wrong, just less good. With this hint, I expect you to get an index scan, which could be a bit better than a scan of the clustered index, but still far worse than an index seek.Fri, 17 Feb 2012 03:13:23 GMTHugo Kornelis