>Hi,
>
>I am deciding on what type of field/column/expression to use for extracting records from a table. My choices are:
>1. Char field (of about 10 to 20) (depends on the business rule) that will be empty ('') for certain records
>2. Integer field that will have an entry 1000 (or some large number) for certain records.
>
>So the SQL Select will be approximately as following:
>
>1.
>
>select * from Mytable where <SomeWhereExpression> and len(rtrim(ltrim(char_field))) > 0
>
>
>2.
>
>select * from Mytable where <SomeWhereExpression> and integ_field < 1000
>
>
>Generally the size of the MyTable will be about 10,000 to 30,000 (just guess on my part).
>
>Which of the above (given that there is an index in SQL Server on both fields) will execute faster (generally)?
>
>TIA.
Although I concur with the others that Int should be faster, in reality I can't imagine there would be enough of a performance difference to be noticeable at all given appropriate indices. (Even without them if the table contains 10K to 30K records; SQL Server doesn't even break a sweat operating on tables of that size). You don't say what data the column holds but I would go with whichever type seems intrinsically more suitable for the data. The rule of thumb is if it's used in calculations it should be a numeric type, otherwise it should be Char or Varchar. For example, zip codes and telephone numbers (in the U.S.) consist of numeric digits but are character type data.