I am curious, why does SQL Server save the values in msdb.dbo.sysschedules for date and times as int instead of datetime. I assume the reason dates back to something in SQL Server 2000.

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

  • It goes back further than that IIRC, to SQL 6.5 or 7.0. No idea why though... – gbn Aug 3 '12 at 13:43

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).

| improve this answer | |
  • It's pretty surprising there aren't any DMVs that cover Agent. I'm guessing they don't want to add it now for the existing structures because they're probably going to have to tear it apart to implement database-contained jobs. – Jon Seigel Aug 3 '12 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.