Anders wrote:
> Hi Gert-Jan, thanks for that elaborate answer. I can tell from your
> directions
> the best solution is to convert my varchar tables containing degits
> into int.
>
> Thanks again, I am repeatedly surprised by the prompt and high-quality
> feedback these newsgroups supply.
>
> cheers,
> Anders
> http://thedotnet.com
>
> "Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
> news:41E464A9.A6D540A@toomuchspamalready.nl...
>> Sorting the int column will be faster or much faster, depending on
>> the collation of the varchar column.
>>
>> The most important performance difference is that for all but the
>> binary collations, sorting a char or varchar column requires a
>> conversion to see if two values are equal according to the used
>> collation. This is not needed for sorting ints, since all different
>> int values are by definition not equal to each other.
>>
>> Even if you use binary collation, then sorting the varchar(4) column
>> will probably still be slower, because the column width is not fixed,
>> and this has to be taken into consideration when sorting.
>>
>> Last factor is how much data needs to be handled. An int requires 4
>> bytes per row. A varchar(4) requires 6 bytes per row, and the row
>> overhead will be a few bytes (I think it was 5) more. On average,
>> this will yield a bigger row size compared with int, which means
>> more memory or I/O is needed.
>>
>> HTH,
>> Gert-Jan
>>
>> Anders wrote:
>>>
>>> Hi, do any of you know what difference there is in performance
>>> between sorting rows by a int column versus a varchar(4) column ?
>>>
>>> Thanks for any reply,
>>> Anders

"Gert-Jan Strik" <sorry@toomuchspamalready.nl> wrote in message
news:41E464A9.A6D540A@toomuchspamalready.nl...
> Sorting the int column will be faster or much faster, depending on the
> collation of the varchar column.
>
> The most important performance difference is that for all but the binary
> collations, sorting a char or varchar column requires a conversion to
> see if two values are equal according to the used collation. This is not
> needed for sorting ints, since all different int values are by
> definition not equal to each other.
>
> Even if you use binary collation, then sorting the varchar(4) column
> will probably still be slower, because the column width is not fixed,
> and this has to be taken into consideration when sorting.
>
> Last factor is how much data needs to be handled. An int requires 4
> bytes per row. A varchar(4) requires 6 bytes per row, and the row
> overhead will be a few bytes (I think it was 5) more. On average, this
> will yield a bigger row size compared with int, which means more memory
> or I/O is needed.
>
> HTH,
> Gert-Jan
>
> Anders wrote:
>>
>> Hi, do any of you know what difference there is in performance between
>> sorting rows by a int column versus a varchar(4) column ?
>>
>> Thanks for any reply,
>> Anders