SQL Query with fields of type NUMERIC

I have a table like this one

CREATE TABLE MyTable (
    [MyTableID] [int] NOT NULL,
    [Description]开发者_如何学Go [varchar](50) NOT NULL,
    CONSTRAINT [PK_AddressType] PRIMARY KEY CLUSTERED (
        [MyTableID] ASC
    )
) ON [PRIMARY]

Can somebody explain why the following query works even if I am including the field value within single quotes?

SELECT *
FROM MyTable
WHERE MyTableID = '1'

Is this a SQL Server specific feature? It is ANSI/SQL?

What you've demonstrated is commonly referred to as implicit data conversion - the data is automatically converted to the data type of the column being compared to. Because a string with a value of "1" can be converted to a numeric data type with a value of 1, things work just fine. Implicit data type conversions can occur according to the precedence list here.

Explicit data conversion is when you use functions like CAST or CONVERT.

String to numeric (and vice versa) is supported on all the databases I know of - SQL Server, Oracle, MySQL, PostgreSQL... The CAST function is ANSI, IIRC. Other data types aren't as flexible - dates & datetimes for instance.

In addition to OMG Ponies answer.

Implicit data type conversions can occur according to the precedence list here.

They can be a source of performance problems when the datatype of a constant or parameter in a predicate has a higher precedence than that of the column. In this case the column values will need to undergo an implicit conversion meaning an index cannot be used.