Hi all, it's my first post in this forum, correct me if I do something incorrect.

I just want to discuss this question from the performance point of view, so don't worry about the design or other stuff.

I have read through many threads and articles, it seems that no one has explained my concern.

If we replace the implicit conversion by explicit conversion, does it help for the performance?

For example, I created the following table

CREATE TABLE student (s_id tinyint, name varchar(20));

INSERT INTO student VALUES (1,'name1'),(2,'name2'),(3,'name3')

Does the following two queries make any difference from the performance point of view? As you can see, the first one applies implicit conversion, whereas the second one applies explicit conversion

SELECT * FROM student WHERE s_id = '2';

SELECT * FROM student WHERE s_id = CAST('2' AS tinyint);

I understand that I should query using WHERE s_id = 2, but I just want to make this as a example to understand the difference between implicit and explicit conversion.

Another related question is when I insert the records, is it better if I Cast the number to tinyint as follows?

INSERT INTO student VALUES (CAST(4 AS tinyint),'name4')

Thanks guys

I think the issue is that in some cases, for reasons unknown, SQL Server fails to recognize join optimizations and index uses across implicit type conversions and making them explicit makes the optimizer smarter.

In your particular examples I don't think it would make any difference.

In general, if your data model is well constructed the need for conversions implicit or explicit is minimal and SQL Server may handle them all well.  So as a first cut don't even worry about it, but if you have any performance issues it's something to look for.

Josh

Hi,

Explicit Conversions occurs when you use convert or cast.

Implicit conversions is not visible to the user, sql server automatically converts the datatypes.

For your reference. 

https://www.simple-talk.com/sql/t-sql-programming/data-conversion-in-sql-server/

also you can see the difference in Display Estimated execution plan

Hi,

Explicit conversions leads to scans and can cause performance issue. Can you please refer to below technet article

http://social.technet.microsoft.com/wiki/contents/articles/24563.sql-server-be-aware-of-the-correct-data-type-for-predicates-in-queries.aspx

Hi,

Implicit conversions leads to scans and can cause performance issue. Can you please refer to below technet article

http://social.technet.microsoft.com/wiki/contents/articles/24563.sql-server-be-aware-of-the-correct-data-type-for-predicates-in-queries.as

Explicit conversion helps to make your code clear, but any damage is the same, no matter conversion is implicit or explicit.

Note here:

SELECT * FROM student WHERE s_id = '2';

'2' is varchar, and that's sort of silly to use with number columns.

Obviously, you need explicit converstion when on implicit conversion is available, or the implicit conversion is not the one you want.

hi, the article you referred is useful to me, but the first word in your reply did you mean to say implicit rather than explicit?

hi, the article you referred is useful to me, but the first word in your reply did you mean to say implicit rather than explicit?

Yeah!!..corrected my comment