CODEWITHSUNDEEP
mysqlsqlsql-serverdatabaset-sql
Tjodalv
Tjodalv

Reputation: 370

Is OK to enclosing all values in SQL statement with single quotes?

Is it ok to enclose all values in SQL statement with single quotes ? For example:

This is simple table called books:

id  |  title
1   |  Some book name
2   |  Second book name

Is it OK to write statement like this:

SELECT * FROM books WHERE id = '1'

I've tested that query in SQL server 2008 and MySQL 5 and it works good, but I am curious is there any performance issue, because ID field is acctualy integer.

And second question is it OK to write statement like this:

SELECT * FROM books WHERE id = N'1'

N prefix is used in SQL server for UTF-8 fields, but I've tested that in SQL server and MySQL and both worked OK. I don't know if SQLite support N prefix, because I didn't test that.

The reason why I am asking this is because I am building database class that will work with popular SQL databases (SQL Server, MySQL, SQLite and maybe MS Access), so when performing selecting, inserting or updating data I don't have to worry about field datatype. I can always enclose value with N'Some value', but I am curious is this correct and is there any performance issues?

Upvotes: 3

Views: 2235

Answers (4)

Geoff
Geoff

Reputation: 8850

SQL Server and MySQL both perform implicit type conversions, but it doesn't make it a good practice; use the appropriate native type when you can. This will let you avoid trying to spot the difference between:

SELECT * FROM books WHERE id = '1'

and

SELECT * FROM books WHERE id = 'l'

and

SELECT * FROM books WHERE id = 'O'

and

SELECT * FROM books WHERE id = '0'

(that's a one, a lower case L, a capital 'o', and a zero respectively)

Upvotes: 2

Bill Karwin
Bill Karwin

Reputation: 562368

Here's an example bug in MySQL that says the implicit typecast from quoted string of digits to an integer causes a severe performance issue: http://bugs.mysql.com/bug.php?id=43319

It's best to not quote numbers.

Upvotes: 2

000
000

Reputation: 27247

SELECT ... WHERE int_type = '123' is fine to do. SQL will convert '123' to an integer once and be done with it.

However, SELECT ... WHERE char_type = 123 is no okay, because SQL will have to convert every cell into an integer. See that char_type = '0123' and char_type = '00123' will also match. So it has to do more work.

Thanks to @MartinSmith for pointing out a resource for precedence in casting: http://msdn.microsoft.com/en-us/library/ms190309.aspx

Upvotes: 3

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

In SQL Server, you can get an implicit conversion when doing this. Sometimes it won't affect the plan or have noticeable impacts on performance, but sometimes it might. They are generally deemed to be bad. Inspect the plans for these two queries against AdventureWorks2012:

SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = 43659;

SELECT * FROM Sales.SalesOrderHeader WHERE SalesOrderID = '43659';

The latter contains a CONVERT_IMPLICIT (hover over the tooltip for the CI seek).

You also want to be very careful about switching between VARCHAR and NVARCHAR - in some cases this can be dreadful for performance, depending on the underlying data type and whether the literal has the N prefix.

Long story short: don't do this. Write your ORM thingy so that it understands the different data types and handles them appropriately.

Upvotes: 6

Related Questions