While building an internal repository for monitoring and reporting for my SQL network, I ran across a small but important issue with the sysjobhistory in msdb. The run_date and run_time are of the INT datatype. I have created a small function to allow you to convert the columns inline and produce a single manageable DATETIME column for reporting.  The second script is a slightly fuller version and takes the duration into consideration.

Statement using the Simple version listed below:

Results…
small

Statement using the Full version listed below:

Results…

full

Simple Version - [udf_Convert_Int_DateTime] combines the run_date and run_time to build a final DATETIME column

Full Version - [udf_IntToDateParts] combines the run_date, run_time, run_duration to build a final column and adds in the duration time

Hope this can help make someones day a bit easier!
COMMIT TRAN
–ANDREW

Thanks to all who attended the Tampa Bay SQL BI user group meeting September 9, 2013. I had a terrific time presenting and appreciate the opportunity to share details on SQL Server partitioning.

Partitioning is near and dear to me so I encourage you to ask any outstanding questions… I’ll continue to develop more posts related to partitioning as there is much more detail that can be shared.

—————-

Click below to download the Slide Deck and Code .zip file

Partition_Presentation

—————-

Abstract:
SQL Server Data Warehouse Partitioning for Performance & Archiving

In this session you will learn the fundamentals of SQL Server Table Partitioning and how it applies to Data Warehousing. We will begin with the key concepts of the partition function, scheme and what it means to be index and storage aligned. Then we will move into two practical examples that include loading your table with Partition Switching and Sliding Window Table Partitioning for archiving older data. We’ll end the session with best practices and important considerations to help you decide if partitioning is right for you.

What you can take away from this presentation:
1. Ability to design a sliding window table partition to archive your largest datasets in a fast elegant way
2. How to load your Data Warehouse faster with partition switching
3. Better understand partition elimination and the advantages of partition compression

This post is a nice simple way to connect into a SQL Server and run a quick SQL query. I have purposely left it simple so anyone can use it as a template to build on and add features. I use it all the time for sp_configure, sys.database etc…

Also for bringing in a list of servers to iterate just substitute the (foreach) “see example” with a file that lists the names of the servers you want to target. Don’t forget to create the file…

Hope this can help make your day a bit easier!
COMMIT TRAN
–ANDREW

%d bloggers like this: