Impact of SQL Variant Datatype on performance

tle-cargo

Hi,

We are managing 12 companies in Microsoft Dynamics NAV, on 5 distinct C/Side databases. All the databases have exactly the same version of objects, based on a highly customized French 3.6 NAV version, with a lot of specific functionalities, running with a 4.0 SP2 server version.
The DB sizes range from 140GB to 410GB, the number of sessions from 50 to 120. On the biggest DB, a very fast growing one, the largest tables are Value Entry 103 GB, Sales Invoice Line 70 GB and Item Ledger Entry 59 GB.

We are in process to move to SQL server, using the more recent SQL server and NAV server versions. Then arises the question of the SQL datatype for the Code fields.
To avoid renumbering of a huge quantity of data and maybe missing some impacts in the fields to convert, we are wondering to convert ALL Code fields to SQL Variant Datatype, except maybe Postcodes and a few other fields.
Does someone has an idea of the impact this may have on the posting processes and the general performance of Dynamics NAV ? In comparison to all code fields in Varchar Datatype, will we lose around 2% speed, around 10% or even more ?

Thanks for sharing your experience on this topic.

rhpnt

Why do you feel the need to incorporate variant data types?

David_Singleton

I have done a lot of Native to SQL conversions, and NEVER used variant datatype. Better to take the hit fix the errors and move on.

tle-cargo

About Variant Datatype, I guess it's not a very good solution.
I guess because I know how SQL server is managing this kind of data.
For the moment I found no one able to tell, by his own experience, the real impact on NAV's global performance of the use of Variant datatype instead of Varchar datatype.

In fact, I'm facing a serious problem in most of the NAV companies I'm in charge of, because on several main tables (item, customer,...) the users use to allocate significant id numbers.

For example, some item numbers may be purely numerals (standard items), other may also contain letters (spare parts), the item numbers may run from 1 to over 1.000.000, many ranges of numbers having special meanings : under 20.000, under 50.000, under 80.000, over 100.000, over 200.000 ...
With Varchar Datatype, all this organisation will be down.

But renumbering the items would have big physical impact on the warehouse because the item numbers are present on stickers on the items, on the sale packaging and on the parcels. Also, part of the item number may be inserted in the GS1 barcode. And so on.

So, I'm not meaning Variant datatype is really OK, I'm just looking for trusted information to avoid spending too much time testing by myself, because I'm not sure I can avoid this solution, specially for the Item table.

Anyway, I will have to prefer a test than a guess if no one ever experienced.

rhpnt

tle-cargo wrote:

For example, some item numbers may be purely numerals (standard items), other may also contain letters (spare parts), the item numbers may run from 1 to over 1.000.000, many ranges of numbers having special meanings : under 20.000, under 50.000, under 80.000, over 100.000, over 200.000 ...

Again, falsely interpreted content ends up in the NAV department as a technical requirement. My opinion regarding variants is - don't do it - since many of the fields in question represent clustered primary key fields with tons of records.