Geek Answers Handbook

What is the best way to represent rational numbers in SQL Server?

I work with data that originally comes as rational numbers. I have a slick common C # class that beautifully represents this data in C # and allows you to convert many other forms. Unfortunately, when I turned around and want to save this in SQL, I have a couple of solutions, but none of them are very satisfying.

Here is an example. I have an original value 2/3that my new Rational<int>(2, 3)easily processes in C #. The options that I thought about storing this in the database are as follows:

  • Same as decimal / float, i.e. value = 0.66666667various accuracy and precision. Pros:, it allows me to request data, for example. find the values ​​<1. Cons: it has a loss of accuracy and it is ugly when I go to display this simple value in the user interface.

  • Store as two exact integer fields, for example. numerator = 2, denominator = 3various accuracy and precision. Pros: This allows me to accurately represent the original value and display it in its simplest form later. Cons: Now I have two fields to represent this value, and now the query is more complicated / less efficient, since each query must do arithmetic, for example. find the numerator / denominator <1.

  • Serialize as string data, i.e. "2/3". I could find out the maximum string length and have a varchar that could hold this. Pros: I returned to one field, but with an accurate view. Cons: the request is heavily overloaded and pays for serialization.

  • Combination # 1 and # 2. Pros: easily / efficiently request ranges of values ​​and have exact values ​​in the user interface. Cons: three fields (!?!) For storing one piece of data should contain simultaneously several views that break DRY

  • Combination # 1 and # 3. Pros: easily / efficiently request ranges of values ​​and have exact values ​​in the user interface. Cons: Go back to the two fields for storing individual data so that they synchronize several views that interrupt DRY and have to pay the additional cost of serialization.

- , , ? , ? SQL, ?

+5
5

, , Option # 4, , / ( , 2 , " " ).

SQL- :

CREATE TABLE dbo.Whatever(
   Numerator INT NOT NULL,
   Denominator INT NOT NULL,
   Value AS (Numerator / Denominator) PERSISTED
)

( , , , , ..).

, SQL 2005 PERSISTED, .

+6

SQL Server 2005 2008, CLR:

SQL Server 2005, (UDT) , CLR SQL Server. , SQL Server .

UDT , . , . UDT SQL Server :

  • , ,

, , .

+7

?

, # , 2/3rds . , , , , 10, db.

, . , :

numerator INT,
denominator INT,
result AS CASE WHEN denominator > 0 THEN numerator / denominator ELSE NULL END
+2

SQL Server 2008 . , , X-, Y .

, . (, ..). , T-SQL .

0

? double/float ( ). , / , . , , matlab mathematica, . , .net.

:

"When Mathematica works on rational numbers, it gives an accurate result no matter how many digits are required," from here

Another good read , but you have to implement it. I think,

0
source

More articles:


All Articles