sysschedules datatype choices

Question

I am Curious.

Why does TSQL save the values in msdb.dbo.sysschedules for date and times as ints instead of using datetime. I assume the reason dates back to something in SQL Server 2000.

Was it a storage capacity issue, performance, or other?

Answer 1

Because back then design controls were much looser than they are today, and the person who was responsible for designing this table thought it would be easier to deal with ints than with separate datetime columns (remember this was back before we had separate date and time data types, and also when the prevailing logic was that dealing with integers was much more efficient than strings or dates). We're all still paying for this design choice. I've been tempted to try creating my own table, replacing the existing table with a view, and seeing how much I could break (not sure if it's possible, but it would make any custom coding around Agent a lot less painful).

Not sure you'll ever track down why now, unless you corner that developer and feed him or her a case of scotch. It's likely the same mentality that led to two SPID columns in sp_who2 output (true story, or so I'm told - the developer got sick of scrolling back to the left to see which SPID was responsible for the metrics on the right, so he or she added another entry at the end of the column list).