SQL Variant

megawavez

Hi,

I thinking of using this to solve the sorting problem inherent in SQL (10 sorts before 2). Are there any drawbacks to doing this? I haven't been able to find any in depth pluses or minuses on this subject in the forum. I'm planning on changing the "Sales Header"."Document No." and Job."No." fields to SQL Type = Variant.

Thanks,
Mega

megawavez

Opps... make that "Sales Header"."No."

stryk

Well, the advantage of using the "SQLDataType" "Variant" is that you are back to the old/native numeric sorting (thus filtering!) and you still could use alphanumeric values ...

Downsides are these:
+ When using "Variant" SQL Server has to do implicitly internal datatype-conversions, which could slightly degrade performance (depending on the business processes)
+ You cannot use any "leading zeros" like 01, 001, 0001 etc. in Primary Keys (as "Sales Header"."No."), this all would create a "Variant" of 1 (hence a PK violation)

megawavez

stryk wrote:

Well, the advantage of using the "SQLDataType" "Variant" is that you are back to the old/native numeric sorting (thus filtering!) and you still could use alphanumeric values ...

Downsides are these:
+ When using "Variant" SQL Server has to do implicitly internal datatype-conversions, which could slightly degrade performance (depending on the business processes)
+ You cannot use any "leading zeros" like 01, 001, 0001 etc. in Primary Keys (as "Sales Header"."No."), this all would create a "Variant" of 1 (hence a PK violation)

Thanks !

kine

And you cannot use big numbers like "2010016546544" because they will "overflow" the maxint during the conversion.