Re: use a quote for integer data type in SQL statement?

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 09/18/04 

Date: Sat, 18 Sep 2004 18:59:07 +0100

If you compare a string value to an integer value then the string value will
be implicitly converted to an integer. This means that if you specify
something like ID='1001' where ID is an integer then '1001' gets converted
(provided the literal string represents a valid number) and the expression
will work as you might have expected.

On the other hand if ID is a CHAR/VARCHAR column and you write ID=1001 then
*every value* in the ID column has to converted to an integer in order for
the comparison to take place. This can be a significant performance hit and
will also result in an error if any row in the table contains an ID that
isn't valid for numeric conversion. For these reasons you should always try
to avoid unnecessary data type converions. Specify literal values correctly:
1001 for integers, '1001' for strings.

For more information see the Data Type Precedence topic in Books Online.

Hope this helps. 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • Proposal: String::Format::General
    ... It provides format string parsing and output assembly, you provide the code that implements the individual conversion characters. ... Format syntax is kind of a cross between sprintf and strftime, but how close it is to each of these depends on the semantics implemented by the user. ... Note that the following is pre-alpha documentation; the interface to the output conversion code has changed since yesterday, ... conversion character, and contain a number of optional fields which may ...
    (comp.lang.perl.modules)
  • Re: compare numbers behaves strange...
    ... compare them and depending on the value I increment a counter. ... to a string somewhere in an unquoted part of his program (i.e. ... the opposite of the conversion you propose below). ...
    (comp.lang.awk)
  • Re: what if (f)printf returns EINTR ?
    ... vsnprintf - formatted output conversion ... int fprintf; ... write to the character string str. ...
    (comp.unix.programmer)
  • Re: Sets and portability (was) Re: Is ISO Pascal compatible with J&W (original) Pascal ?
    ... > So library code would be better if it could handle huge sets. ... widestring, in the future maybe also string). ... Note that ansi->wide conversion is codepage sensitive. ... compiler links in correct conversion code or table). ...
    (comp.lang.pascal.misc)
  • Re: Dynamic type conversion
    ... there is much more to dynamic conversion in the .Net ... Enumerations are considered a special form of a value type, ... This assumes the value is a string which can be converted to a Int32, byte, ... or enum that exists in the .Net Framework, or a custom type or enum ...
    (microsoft.public.dotnet.general)
Loading