Sql-server – Setting up a table with a calculated column

• Computed columns are your friend. Use them to capture simple calculations you're going to do anyway, and to guarantee that the computed values are correct.
• Persist the results if you want to index or filter on them; don't persist them if you just need to pull the value once in a while.
• Capture all data constraints using CHECK constraints.

Here is a pseudo-schema definition:

  id               PRIMARY KEY
, truck_num

, start_date
, end_date         CHECK (end_date > start_date)

, start_miles      CHECK (start_miles >= 0)
, end_miles        CHECK (end_miles > start_miles)

                   -- what if they refill the tank?
, start_gals       CHECK (start_gals >= 0)
, end_gals         CHECK (end_gals < start_gals AND end_gals >= 0)

-- all these should be computed
, miles = end_miles - start_miles
, gals = start_gals - end_gals
, mpg = miles/gals

As for indexing the table, here's what I think:

• Cluster on start_date ASC. This will satisfy your WHERE clause. You are always inserting data by increasing start_date, meaning your writes will always be sequential under this clustering scheme. You are also always querying by start_date so you satisfy your biggest query pattern as well. (3,000 inserts per week is nothing. Because you have such a low volume of inserts, you could even cluster on start_date ASC, end_date ASC.)
• Create a non-clustered index on truck_num and INCLUDE mpg. This should satisfy your SELECT, JOIN, and GROUP BY clauses. If you want to ORDER BY mpg, then make mpg part of the index key after truck_num instead of just INCLUDE-ing it.

When you're done, test your setup as follows:

1. Create a test table
2. Pump it full of test data
3. Create the indexes
4. Update statistics
5. Run your most common queries
6. Check their plans and run times

The cost of using a simple synthetic integer PK is small, and the benefit in your case would probably be quite considerable.

• As you point out, you'll have a much simpler FK relationship.
• A small PK makes for small (and fast) indices. Your total table space will probably be made less by adding such a column.
• If business rules ever change, you won't have to reorder the table.

The only material downside that comes to mind is that you may lose performance on queries which benefited from clustering on the composite PK. If you think that's likely to be significant, than continue clustering on the composite candidate key, but put the PK on the synthetic key.