Share via

Difference between SQL and .Net Framework built in functions

  • Article

A common problem when using different programming languages like SQL on the server and C# or VB on the client is that certain functions are almost the same, but not completely. A good example is SQL Round vs. CLR Math.Round.

For example, rounding to the next integer would round 2.1 and 2.499 to 2, 2.501 and 2.987 to 3, but different implementations do different things with 2.5:

On SQL Server, Round always rounds up a trailing 5 (where “up” means that for positive numbers, the result is greater. For negative numbers, only the absolute value goes up: The result is actually lower).

In the CLR Math class, Math.Round uses “Banker’s rounding”, which means that a trailing 5 is rounded either up or down such that the result is even.

 

DLinq sits between the managed languages and SQL, it allows users to write an expression e.g. in C#, which is then executed as a SQL expression.
Now DLinq has a problem: Should the semantics be the one of SQL or the one of C#? This has been debated in our team some while ago, and probably we will debate it again before we release the next CTP.

Here are some options:

1) Our current solution translates to the SQL built in function if its meaning is “reasonable close” to the .Net Framework function. So Math.Round translates to SQL’s Round function.

2) We could translate Math.Round(x) to some SQL expression in x that behaves in the same way as Math.Round(x) on the client.

 

3) We could have 1 as the default behavior, but add libraries of additional functions that behave the same on SQL and .Net, one that does the Banker’s rounding in both cases, one that does the SQL “always rounding up” in both cases.

 

One reason we went with 1 instead of 2 is that the performance is much better than for a special expression that would replace the simple ROUND function.

Since it could both be said that developers expect that Math.Round translates to SQL’s ROUND or that it behaves the same as CLR’s Math.Round on the client, we went for the simple and efficient solution.
One argument against 3 is that it only solves the problem for users who know the problem and know where to look for these functions.

 

What are your thoughts? Would you care at all? Do you strongly prefer another solution? Would you need additional libraries?

Comments

  • Anonymous
    February 10, 2006
    I make scientfic software and sometimes I need to use other forms of rounding like round up if even down if odd, and vice versa, also other forms which I can not think of right now. But I would vote for an overloaded version of 3 along with 1.

    blair
  • Anonymous
    June 19, 2009
    PingBack from http://edebtsettlementprogram.info/story.php?id=24561