NAV/SQL Troubleshooter’s Wish-List

Ho, ho, ho … well, X-Mas is coming soon, time to make a wish-list! In my special case I would like to ask for several things making life with NAV and SQL easier. I'm not asking for "science fiction" features like "non-blocking posting routines" or "different code versions for C/SIDE and SQL"; but small (?) and smart enhancements.

Hope someone northern from here (not necessarily Santa C. from North Pole, someone from Vedbaek would be OK ;c) ) takes care about it …

  1. I want to have full access to all fields from system-table 2000000063 "Key". Especially to the field "SQL Index". And I would like to have access to the "SIFTLevels", too, so that I could define all via C/AL Code.
  2. I want new properties for "Tables" and "Keys" where I could define the SQL Server site "File-Group" where I want to store them.
  3. I want a new C/AL command like "SETINDEX", specifying if I would hint a specific index; not using the "$ndo$dbconfig" thing.
  4. I want a new C/AL command like "SETRECOMPILE" to specify if I would like to force the OPTION(RECOMPILE) for a query; not using the "$ndo$dbconfig" thing.
  5. I want an official list about all available NAV releases, service packs and updates (see Waldo's BLOG for reference).

And all should be neatly wrapped in a package labeled "MS Dynamics NAV 4.00 SP4". Probably that's just a start; to be continued …

What would you wish? Please add!

Posted: Nov 19, 2007 21:06 by Jörg A. Stryk | with 18 comment(s) |
Filed under: ,
Bookmark and Share

Comments

# re: NAV/SQL Troubleshooter’s Wish-List

6. In each table and each index a new property to define an index-fill factor for the index. The property in the table is valid for the table if there isn't one for an index in the table.

7. a command to be used with RESET, SETCURRENTKEY, SETRANGE/FILTER in which we define  a fieldlist we want => "SELECT [fieldlist] FROM" and not "SELECT * FROM". This could be also interesting as a property in the objects.

Tuesday, November 20, 2007 11:58 AM by kriki

# re: NAV/SQL Troubleshooter’s Wish-List

8. File=>Database=>Tables=>Optimize in Navision: possibility to select a SQL-index to optimize. Possibility to select between indexrebuild/defrag/0-SIFT-cleaning. Also a new field : an indication of fragmentation of an index.

Tuesday, November 20, 2007 12:03 PM by kriki

# re: NAV/SQL Troubleshooter’s Wish-List

9. Specify sumindexfields to be included fields in the index in stead of in seperate SIFT tables

10. Function to temporary turn on and off maintenance of sumindexfields during batch updates/inserts

11. Notion of client-side resultset that can be searched/filtered locally i.s.o. quering the database every time.

Tuesday, November 20, 2007 2:59 PM by PeterW

# re: NAV/SQL Troubleshooter’s Wish-List

reply to 10. This already exists. Use "Key Groups". In NAV 5.0 there is a command to disable a keygroup and then enable it again.

reply to 11. This is (partly) done by the FINDSET-command.

12. Possibility to NOT use the primary key fields in the "order by" generated by a SETCURRENTKEY.

E.g. T37: SETCURRENTKEY("No.",Type); becomes "Order by No.,Type,Document Type,Document No.,Line No.". I would like to have "Order by No.,Type".

Wednesday, November 21, 2007 7:23 AM by kriki

# re: NAV/SQL Troubleshooter’s Wish-List

Somewhere was written that sumindex tables go away and Index Views come on.

So i guess some of wishes related to this are to be released and new wishes are on the way :)

Friday, November 23, 2007 6:27 AM by Gedas

# re: NAV/SQL Troubleshooter’s Wish-List

13. (As 13 is my lucky number Smile) A possibility to work with large texts.  I know this replaces my "WaldoNavPad", but it's something so common.

Friday, November 23, 2007 9:08 AM by waldo

# re: NAV/SQL Troubleshooter’s Wish-List

14. Extended DataTypes (something I explained a while ago on my blog... dynamicsuser.net/.../extended-datatypes.aspx ):

Friday, November 23, 2007 9:09 AM by waldo

# re: NAV/SQL Troubleshooter’s Wish-List

15. User specific setup of "Cache Record Size", "Lock Timeout", "Always Rowlock"

16. User specific setup for "DEADLOCK_PRIORITY"

Friday, November 23, 2007 9:26 AM by Jörg A. Stryk

# re: NAV/SQL Troubleshooter’s Wish-List

Reply to "Gedas": I'm not sure if we really want to SIFT Tables being replaced by "Indexed Views" ... ;c)

Friday, November 23, 2007 9:28 AM by Jörg A. Stryk

# re: NAV/SQL Troubleshooter’s Wish-List

reply to 15 and 16.

15+16 Why not a C/AL command we can use when needed? And it remains valid until the next COMMIT or (FIELD)ERROR.

Friday, November 23, 2007 1:37 PM by kriki

# re: NAV/SQL Troubleshooter’s Wish-List

This post is really great!  

Saturday, November 24, 2007 12:59 PM by Erik P. Ernst

# re: NAV/SQL Troubleshooter’s Wish-List

17.

I would like the possibility of setting up security with defining what tables the user should NOT have access to.  I know I cannot get the years back I have spent on setting up security, but with this feature, I at least would be able to enjoy my senior years a little more :)

And I fully support #3 and #13 !

/Daniel

Monday, November 26, 2007 12:54 AM by Daniel Ekvall

# re: NAV/SQL Troubleshooter’s Wish-List

18. Versioning

19. Automated Documentation

Monday, November 26, 2007 8:19 AM by waldo

# re: NAV/SQL Troubleshooter’s Wish-List

Some extra for:

9) a property in which we define included fields (not limited to SIFT-fields) to the index. I know it is only useful for SQL2005 and not SQL2000, but this would be an extra reason to go on SQL2005 :-)

Wednesday, November 28, 2007 12:35 AM by kriki

# re: NAV/SQL Troubleshooter’s Wish-List

20) for use with SQL2008. I forgot the name of the technology but the basic is that it is possible to index only records with certain values in the key-fields. E.g. In Vendor Ledger Entry is an index "Open,Due Date". This index is only usefull for records with Open=TRUE.

Tuesday, December 11, 2007 9:23 AM by kriki

# re: NAV/SQL Troubleshooter’s Wish-List

21) a property on index to say if has to be ordered ascending (default) or descending.

Wednesday, December 12, 2007 5:53 PM by kriki

# re: NAV/SQL Troubleshooter’s Wish-List

To 5. : This would be great! And no more "Rollup Installer EXEs". I want to know and see what files are replaced... If there would just be a list of files beeing replaced would also be fine.

Thursday, December 13, 2007 11:06 AM by Gregor

# re: NAV/SQL Troubleshooter’s Wish-List

22: I would like to have a posiblity to start C/AL Batches by using the Server Agent job sheduler!

23: I would like to have someone who says you can use this Runtime with that SQL Build. And someone who says "Hallo there ist new build in for NAV"

Wednesday, October 01, 2008 2:32 PM by sven

Leave a Comment

(required) 
(required) 
(optional)
(required)