Microsoft Dynamics NAV 5.0 SP1 and SQL Server 2000

re: Microsoft Dynamics NAV 5.0 SP1 and SQL Server 2000

waldo1001 — Thu, 05 Jun 2008 10:52:25 GMT

May be a stupid or unnecessary question but anyway ... .

I've seen some database which the customers "upgraded" themselves to SQL2005 still in compatibility mode '80', being SQL2000. Is this going to suffer from the same problem?

re: Microsoft Dynamics NAV 5.0 SP1 and SQL Server 2000

Lohndorf — Fri, 06 Jun 2008 12:10:51 GMT

Hi,

Good question! I can't be sure, but as far as I can see the problem does not exist on SQL2005, even in compatibility mode 80.

I tested it here, and get the same query plan with compatibility mode 80 and 90, and the same number of reads when SQL updates an indexed view. But to be completely sure, you would need to test this on the customer's database. I use this code to test it:

ItemLedgEntry.FINDLAST;

ItemLedgEntry.Quantity := ItemLedgEntry.Quantity + 1;

ItemLedgEntry."Invoiced Quantity" := ItemLedgEntry."Invoiced Quantity" + 1;

ItemLedgEntry.MODIFY;

With Profiler running, including the "Showplan Text" event. I then look at the query plan, and see if it contains "Clustered index scan". On SQL200 it does. On SQL2005 it doesn't. SQL2005 has clustered index seek, but no scans.

So, even if I cannot be sure, it looks like the indexed views run OK on SQL2005, also in compatibility mode 80. But I would still recommend that customers run a clean SQL2005. Are there some reasons why customers need to run in compatibility mode 80?

Best regards

Lars

re: Microsoft Dynamics NAV 5.0 SP1 and SQL Server 2000

waldo1001 — Fri, 06 Jun 2008 15:57:55 GMT

Not that I know of. But it is also easily overlooked.

What impact does my C/AL have on SQL - SIFT on 5.0 Sp1

Fri, 01 Aug 2008 14:20:50 GMT

Normally, I would have done an article about Sumindexfields on versions previous to 5.0 SP1, but I decided