Say you have a column of int (also bit, etc.)
CREATE TABLE TEST (a int)
It seems it doesn't matter if you insert value with '1' or 2
INSERT INTO TEST VALUES ('1')
INSERT INTO TEST VALUES (2)
Is there any side-effect if I insert/update values by single-quoting them (e.g.'value') for any column type?
Best How To :
SQL Server will implicitly convert data types according to data type precedence rules as documented in the Books Online https://msdn.microsoft.com/en-us/library/ms190309.aspx. Integer has a higher precedence so string literals will be converted to integer in your examples. The downside is a little more work for SQL Server to do but the overhead probably isn't significant.
However, beware of implicit conversion of expressions in
WHERE clauses. If the compared column is indexed and has a lower precedence, the index can't be used efficiently and a scan may be required. This is a common issue with Unicode literals/parameters and varchar columns.
It's best to match types for maximum performance and to avoid ambiguity.