Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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

1 Answer 1

up vote 5 down vote accepted

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

share|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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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