title: How best to represent rational numbers in SQL Server?
body: <p>I'm working with data that is natively supplied as rational numbers. I have a slick <a href="http://exif-utils.googlecode.com/svn/trunk/ExifUtils/ExifUtils/Rational.cs" rel="nofollow noreferrer">generic C# class which beautifully represents this data</a> in C# and allows conversion to many other forms. Unfortunately, when I turn around and want to store this in SQL, I've got a couple solutions in mind but none of them are very satisfying.</p>
  
  <p>Here is an example. I have the raw value <code>2/3</code> which my <code>new Rational&lt;int&gt;(2, 3)</code> easily handles in C#. The options I've thought of for storing this in the database are as follows:</p>
  
  <ol>
  <li><p>Just as a decimal/floating point, i.e. value = <code>0.66666667</code> of various precisions and exactness.
  <strong>Pros:</strong> this allows me to query the data, e.g. find values &lt; 1.
  <strong>Cons:</strong> it has a loss of exactness and it is ugly when I go to display this simple value back in the UI.</p></li>
  <li><p>Store as two exact integer fields, e.g. numerator = <code>2</code>, denominator = <code>3</code> of various precisions and exactness.
  <strong>Pros:</strong> This allows me to precisely represent the original value and display it in its simplest form later.
  <strong>Cons:</strong> I now have two fields to represent this value and querying is now complicated/less efficient as every query must perform the arithmetic, e.g. find numerator / denominator &lt; 1.</p></li>
  <li><p>Serialize as string data, i.e. <code>"2/3"</code>. I would be able to know the max string length and have a varchar that could hold this.
  <strong>Pros:</strong> I'm back to one field but with an exact representation.
  <strong>Cons:</strong> querying is pretty much busted and pay a serialization cost.</p></li>
  <li><p>A combination of #1 &amp; #2.
  <strong>Pros:</strong> easily/efficiently query for ranges of values, <em>and</em> have precise values in the UI.
  <strong>Cons:</strong> <em>three</em> fields (!?!) to hold one piece of data, must keep multiple representations in sync which breaks D.R.Y.</p></li>
  <li><p>A combination of #1 &amp; #3.
  <strong>Pros:</strong> easily/efficiently query for ranges of values, <em>and</em> have precise values in the UI.
  <strong>Cons:</strong> back down to two fields to hold one piece data, must keep multiple representations in sync which breaks D.R.Y., and must pay extra serialization costs.</p></li>
  </ol>
  
  <p>Does anyone have another out-of-the-box solution which is better than these? Are there other things I'm not considering? Is there a relatively easy way to do this in SQL that I'm just unaware of?</p>