Should I quote numbers in SQL?

I don't know what you may have read, but don't quote numbers.

Here's an example, where quoting would produce inconsistent results (in MySQL):

select 1 < 1.0;      // returns 0

select '1' < '1.0';  // returns 1

That's because the second comparison is performed using the current string collation rather than numerically.

It's better not to quote numbers, since that would just be an extra unnecessary step for the database to convert the string literal into a numeric value for comparison and could alter the meaning of comparisons.

This answer is for Microsoft SQL Server, in particular MSSQL 2008 R2.

In handwritten SQL I would never quote numbers (unless inserting a value into a varchar column where the string inserted just happens to be a number). But sometimes when generating SQL programmatically it can make life simpler to just quote everything. (This is in database maintenance scripts or library routines that work on any table, without knowing the column types in advance.)

I wondered whether doing so would impose a performance penalty. If I've used a quoted value in my SQL statement, the server must parse it as a string and then have to convert it to integer. But then, parsing SQL would involve converting strings to integers anyway. And the query parse time is usually only a small fraction of the total.

I ran some test statements looking like

insert into #t values (123, 123, 123), (123, 123, 123)
insert into #t values ('123', '123', '123'), ('123', '123', '123')

but with a larger number of columns in #t, a larger number of value tuples inserted in one go, and each statement repeated many times. I happened to use Perl for that:

$dbh = my_database_connection(); # using DBD::Sybase
$n = 20; # this many value tuples, and also repeated this many times
$v = "'123'";
# $v = 123;    # uncomment this to insert without quoting

@cols = 'aa' .. 'zz';
@ds = map { "[$_] int not null" } @cols;
@vs = map { $v } @cols;
$" = ", ";
$dbh->do("create table #t (@ds)");
foreach (1 .. $n) {
    $sql = 'insert into #t values ';
    $sql .= "(@vs), " foreach 1 .. $n;
    $sql =~ s/, \z//;
    $dbh->do($sql);
}

but you can trivially write the same benchmark in any language. I ran this several times for the quoted case and for the unquoted, and observed no significant difference in speed. (On my setup a single run took about ten seconds; you can obviously change $n to make it faster or slower.)

Of course, the generated SQL is bigger if it has the redundant quote characters in it. It surely can't be any faster, but it does not appear to be noticeably slower.

Given this result, I will simplify my SQL-generation code to add single quotes around all values, without needing to know the data type of the column to be inserted into. (The code does still need to defend against SQL injection by making sure the input value doesn't itself contain the ' character, or otherwise quoting cleverly.)

I still don't recommend quoting numbers in SQL in the normal course of things, but if you find you have to do it, it doesn't appear to cause any harm. Similarly, in generated code I put [] around column names whether needed or not, but I consider that unnecessary cruft in handwritten SQL.

You should not quote numbers if you want to treat them as numbers.

You're correct by remembering that it makes it a string.

SELECT 10 AS x

is perfectly legal and will return (in most database engines) a column of datatype int (or a variation thereof.)

If you do this:

SELECT '10' AS x

instead you'll get a textual data type. This might too be suitable in some cases, but you need to decide whether you want the result as text or as a number.