Insert statement with single quote for Integer datatype

Question

I have a table table1 (id integer, Name varchar, Age integer)

While inserting rows into the table I use two statements.

INSERT INTO TABLE1('1','John','33'); and
INSERT INTO TABLE1(1,'John',33);

Both of them work fine. In the first insert statement I have used the value between single quote for the integer datatype as well.

Can I know what can be the difference between both of the statements?

The only difference is that the database has to convert the char '1' in first case to an integer. — Jens
– Jens, Commented Sep 10, 2015 at 7:16
And there's no reason at all to give integer values as character strings! — jarlh
– jarlh, Commented Sep 10, 2015 at 7:19
As Jens and jarlh wrote - the first one cause the database to implicitly convert your char value to an int value (and there really is no reason to do it), so it will make the server work a tiny bit harder. of course, where it's only one insert statement with one value set, it's impossible to feel the difference. However, when working on big, real time systems where there are a lot of insert statements per second (and by a lot I mean 10,000 and more), this may cause a noticeable difference. — Zohar Peled
– Zohar Peled, Commented Sep 10, 2015 at 7:38
Never rely on implicit data type conversion. Use the literal that is appropriate for the data type. — user330315
– user330315, Commented Sep 10, 2015 at 7:38

Yagnesh Agola · Accepted Answer · 2015-09-10 07:41:40Z

7

There is no difference between using single quote for Integer datatype or not.

In first case it will load db server to convert your string literal to integer data type whereas in second case it will directly store integer value to database.

Its good to used without single quote, since that would just be an extra unnecessary step for the database to convert the string literal into a numeric value and also make some little bit difference in performance when there are more records inserted at a time.

edited Sep 10, 2015 at 7:41

answered Sep 10, 2015 at 7:36

Yagnesh Agola

4,6656 gold badges39 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Insert statement with single quote for Integer datatype

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related