CODEWITHSUNDEEP
sqlsql-serverfloating-pointint
Marcus
Marcus

Reputation: 9439

Converting to float: CAST vs CONVERT vs *1.0

Which is preferred and why? I'm using integer values for clarity however these would be field names.

  1. SELECT 2*1.0 / 3
  2. SELECT CAST(2 AS float) / 3
  3. SELECT CONVERT(float, 2) / 3

Upvotes: 3

Views: 1867

Answers (1)

Kprof
Kprof

Reputation: 752

From a performance perspective regarding the first one and the CAST/CONVERT, I quickly created a temp table and queried it using the value of 2:

CREATE TABLE #temp (Two FLOAT)
INSERT INTO #temp VALUES (2)
SELECT * FROM #temp

SELECT Two*1.0 / 3 FROM #temp
SELECT Two*1E0 / 3 FROM #temp
SELECT CAST(Two AS FLOAT) / 3 FROM #temp
SELECT CONVERT(FLOAT,Two) / 3 FROM #temp

When selecting from it, no matter how many different values I enter, the Execution plan and Statistics IO give me the same numbers (each a fourth). I tried entering other values like 5, 6, 7, 8, 9 and so on, and even more values, but the cost came as the same. From the beyond relational article listed above this (here: http://beyondrelational.com/modules/2/blogs/77/posts/11334/cast-vs-convert-is-there-a-difference-as-far-as-sql-server-is-concerned-which-is-better.aspx), it appears that using your first method doesn't have any effect, as least on the small values (I did use 2,000,000).

Upvotes: 3

Related Questions