In my SQL Server database, I have a datetime column. What is a good way to create a new column that represents the long value for the datetime column. The long would represent a number of seconds.
- Anybody can ask a question
- Anybody can answer
- The best answers are voted up and rise to the top
Create a new column (ALTER TABLE) then run an UPDATE on it
You can add a new column and manually update it as @gbn suggested, but now you have to constantly keep this column up to date with insert/update triggers or some other mechanism. Borrowing @gbn's guesses on table/column names, here are a couple of different approaches that don't require constant maintenance.
You could also persist and index this column, trading off query performance for storage, but you'll need to make a slight change to the calculation (trying to persist the above will yield an error about the computation being non-deterministic):
You would want to persist the column if you are more concerned about read performance than write performance (or storage).
One benefit of a view over a new column is that you don't have to change the base table schema (or worry about keeping it up to date). You pay the cost of computation at query time, which is the same as a non-persisted computed column.
Since the above calculations are not overly complex, just include the calculation in your query. Hopefully you are using stored procedures for data access so you aren't repeating this often.