One of the things that I noticed while doing some development in NAV2013 is that keys are now part of the Symbol Menu (F5) in the C/AL editor!
This allows you to select all active keys for example when using SETCURRENTKEY.
Very handy, since most of the time you had to go to for example the design of the table to look for keys, copy and paste, and then implement the quotes.
How cool is this!
@Mohana: I was also at NAVTechDays2012 and saw the example with the improved performance by removing the SETCURRENTKEY by Bardur. However; this example was not very good and can not be used as a general rule. The example was browsing millions of records and a very high percentage of them were inside the filter. That's why the benefit of using the clustered index was bigger than the wasted time discarding some of the records. This is rarely the case in real life.
He also indirectly said that if you skipped the SETCURRENTKEY it will not issue an ORDER BY clause. This is not true either. It will issue an ORDER BY <primary key> just like in older versions.
That's exactly my guess and how it should be done.
However, when developing you don't always know the resultset.
And if the resultset is small, sorting is done in a blink of an eye for SQL server without it even caughing.
If the resultset is large and you forget SETCURRENTKEY and SQL makes a mistake in the index (which happens quite frequently) the results are devastating.
Therefore, I would always use SetCurrentKey.
But that's just my opinion.
Fun fact: this feature - being able to select and paste the key in to the source code - was available in DOS version of NAV a.k.a. Navigator. So, 15 years later, we get it back :).
Brilliant! :-) Although, to add some perspective - the character-based version of NAV could do the exact same trick, back in 199x…
If I am not wrong, in NAVTechDays2012, MS has suggested not to use SETCURRENTKEY as SQL server will automatically pick right key to sort the records.
If the table order is not important, then follow the following rules
If the expected result set is small, then do not call SETCURRENTKEY. The result will be sorted by the primary key.
If the expected result set is large, then call SETCURRENTKEY.
Hey Rashed, you're fast!
What do you mean by that?
Do you mean this version of NAV works better without ORDER BY in SQL when searching for an index?
I hope it doesn't since I kinda liked the way it worked in NAV2009. Very predictable.
you should only use it if you need to sort the record.