Ed Pollack (5/18/2014)
Comments posted to this topic are about the item <A HREF="/articles/Data+Types/109214/">Comparing Different Data Types</A>
Is there any benefit using CAST Vs Convert to explicitly convert the data types , also which one's should we be converting, is the one on right of operator?
In terms of CAST vs. CONVERT, they will functionally do the same thing. The primary difference between the two is that CONVERT can be used for a variety of formatting functions. For example, you can convert a DATETIME to a 10 character VARCHAR using CONVERT like this:
CONVERT(VARCHAR, MyDate, 10)
Microsoft could easily bore you to death with their long explanation of CAST & CONVERT:
CAST is simpler, though, and I tend to prefer using it if I have no additional formatting needs.
When choosing an operator to convert, your goal should be to always convert the scalar variable and leave the column from the table clean. If you are forced to convert a column, such as in the example from the article:
FROM EmployeeData EMPLOYEE_DATA
INNER JOIN HumanResources.Employee EMPLOYEE
ON EMPLOYEE.NationalIDNumber = CAST(EMPLOYEE_DATA.NationalIDNumber AS NVARCHAR(15))
WHERE EMPLOYEE_DATA.NationalIDNumber = 658797903
I would honestly consider altering your database design or finding a way around this. How the query performs will be based somewhat on statistics and the optimizer, but you'll often suffer a table scan or index scan, rather than a seek. In this case, I'd definitely recommend experimentation and try out both to confirm if either performs better.
More than anything else, look for any way to avoid having to do such an ugly join in the first place. Even if they perform acceptably now, there's no guarantee they will in the future as your row counts grow.