Sql Server. Why field value of [almost] any type may be treated as quoted string in Query/DML?

Question

I discovered SUDDENLY that in SQL Server (2000) any field type value may be treated as quoted string (in query or DML).

Question: Is it normal behavior or accidentally successful result?

Example: 
CREATE TABLE [Test_table] (
    [int_field] [int] NULL ,
    [float_field] [float] NULL ,
    [date_field] [datetime] NULL ,
    [id] [int] NOT NULL 
) ON [PRIMARY]
GO

update test_table set int_field = 100, float_field = 10.01, date_field = CAST('2013-11-10' AS DATETIME) where id = 1

update test_table set int_field = '200', float_field = '20.02', date_field = '2014-12-10' where id = '2'

select * from test_table where id in ('1', 2) -- WHY '1' DOES WORK!???

Why i need this? It exists idea to send in one Stored Procedure over 270 parameters as integral text (XML or custom serialization by delimiters or like Len1+value1+len2+value2+..) then parse and extract all desired values and use them in UPDATE statement. This SO Question.

Q2: Is there any limitations for some types? Q3: Is this reliable way or CAST anyway is recommended?

Answer 1

If you check the CAST and CONVERT topic, you'll find a handy table:

You'll note that conversion from char and varchar is supported for every other type, and only a few of them require explicit casts. For some types, there's no obvious way to type a literal of that type, so allowing implicit conversions from a string makes sense.

(But oh, how I wish conversion to datetime required an explicit case with a format code...)