SQL Data Type experiences

sanoj

Hi,

Has anyone any experiences of the property SQL Data Type on a code field? Good/bad?

I'm working with a customer who had numeric customer no's in there previous system, and I'm thinking of if its a good idea to use this property on the Customer No field in NAV.

Marije_Brummel

I've changed it once or twice in special cases.

It's like changing the length of the item description, it does trough the entire system. Be prepared for a lot of changed objects and prepare the customer.

Manage expectations.

If you change the SQL Type to Variant all the leading 0 characters will dissapear.

Good luck...

vaprog

Mark, assuming there are no leading zeros, do you really have to make any adjustments appart from the field property of the field which is part of a key (and even then, only if it is relevant to searching)?
I have applied the property like that it in the past in one or two cases and had not had any complaints.
I have no idea, though, what effect this has on performance when linking tables on fields with different SQL Types.

Marije_Brummel

The sorting goes through the entire system, if you sort Sales Invoices on Customer No. you'll face the same issues etc.

Also if a field in one table is VarChar and the other table Variant, what happens if you compare them or move them...

vaprog

Mark Brummel wrote:

The sorting goes through the entire system, if you sort Sales Invoices on Customer No. you'll face the same issues etc.

That's what I thought of when writing "... and even then, only if it is relevant to searching" where I actually ment to say ... relevant to sorting.
When you change the SQL Type of the primary key of one of the main master tables, you will have to change quite some fields along with it in order to maintain consistent sorting (and thus consistent filtering), and might even get into trouble on fields with conditional relations to several masters. But you don't necessarily have to change all occurencies of related fields for NAV to work allright. If the chosen SQL Type is capable of preserving your existing data (i.e. there are no leading zeros), NAV seems to be capable to cope with differing SQL types.

Mark Brummel wrote:

Also if a field in one table is VarChar and the other table Variant, what happens if you compare them or move them...

C/AL works with the fields of the record variable, which consists of data with C/AL data types. You cannot acces the value in the SQL data type: NAV converts the types behind the scenes. So I'd expect to see no issues here.