“Black Arts” of T-SQL Dates and Times #2 – Ranges, Reports, and Random Data
Details
Abstract:
This is Part 2 of a multi-part series on the “Black Arts” of T-SQL Dates and Times.
In Part 1, we learned some critical information about temporal datatypes and some basic but equally critical temporal functions that you’ll use every day. We also learned about how DATETIMEs are built and what a DATE SERIAL# is.
This presentation is written to take beginners to the 300 Level and to reinforce what 300 to 500 Level personnel already know.
In this part, Part 2, we’ll learn how to count and then how to generate ranges dates as quick as lightning along with performance comparisons of the 3 typical/slower/more resource intensive methods and a 4th method that blows the doors off the first 3. From there, we’ll learn how to create a very high-performance Inline Table Valued Function to generate ranges of dates and then how to use those ranges of dates to quickly and easily generate two very common types of reports that also frequently show up on interviews, especially interviews for BI, Data Analyst, and many more recently new job descriptions. We’ll also learn the serious performance mistakes people make when they use such functions as a panacea and what they can easily be replaced with.
Finally, we’ll learn the “secret” to writing high performance code… How to generate massive amounts of Random Constrained Test Data to practice your report writing skills against. This is an essential skill that will make you a hero among hero’s when no test data “from prod” available no matter what your job title is, including QA Tester.
Speaker Bio:
Jeff Moden is a 9-year SQL Server MVP Veteran, is the longtime leading poster on SQLServerCentral.com where he has written 39 mostly 5-star articles and has more than 55 thousand post replies, has spoken at the “old” PASS Summit and many SQL Saturdays, many SQL User Groups and, lately, has given many online presentations. Some of his more heterodoxic presentations have shattered many myths that are more than 2 decades old and his article on the Tally Table has made it easy even for newbies to avoid loops and other forms of RBAR (and he coined that term, as well). Like a lot of us, he is an “accidental DBA”, has been working with T-SQL for 24 years, and has been teaching the “Black Arts” of T-SQL for the better part of that. Jeff’s mantra is “Performance is in the code”.
Agenda:
6:00-6:30 - Networking and speaker setup time
6:30-6:35 - Opening remarks & Introductions
6:35 - Speaker time!