CODEWITHSUNDEEP
sqlsql-server
user979189
user979189

Reputation: 1298

What is '.' in SQL Server query

I just started working on one existing project and see the query which looks something like this - 

select 100. * somecolumn1 / NULLIF(somecolumn2, 0.) AS ColumnValue,
from dbo.SomeTable

I am not sure what is '.' operator in select statement. Could anyone please help me understand?

Also this is just a portion of massive SQL Server query. And if I comment this particular select statement the query runs in about 7 seconds otherwise it takes about 5 minutes to execute the query. Can this statement be optimized?

Upvotes: 1

Views: 98

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

SQL Server does integer division. So, 1/2 = 0, not 0.5.

To avoid integer division, you want to use values with decimal places. The simplest way to accomplish that is to use numeric literals. I would always include a 0 after (and before) the decimal place, so the value is easier to see:

select 100.0 * somecolumn1 / NULLIF(somecolumn2, 0.0) AS ColumnValue,
from dbo.SomeTable

I often accomplish this by multiplying by 1.0. Alternative, you can use cast() or convert() on an integer column.

Upvotes: 1

Related Questions