Presenting Through Time and Space with the Baltimore SQL Server User Group

A couple weeks ago I was delighted to give my first remote user group session to the Baltimore SQL Server User Group. I want to thank the organizers for giving me the opportunity to do so, and to both the organizers and the attendees for dealing with the silliness that often comes with giving remote presentations. I think it went well. The organizers did a great job on their end and the attendees asked great questions, and gave me some excellent feedback. A good time was had all around.

Speaking of asking good questions, I wanted to follow up on a couple of specific ones that I did not have answers for during the session. Here they are:

1. On a system-versioned temporal table, does the primary key need to be clustered or can it be a non-clustered index? Are other non-clustered indexes replicated to the history table?

Indexes are handled independently for system-versioned temporal tables and their history tables. While the primary key is required for a temporal table, it can be either clustered or nonclustered. The clustered index of the history table will always be the period columns. Additional indexes can be added to or removed from the temporal table or its history table independently. Indexes created on the temporal table are not automatically created on the history table.

2. Can you join two tables using different system times.

Yes. There’s no syntax that works with the join clause itself, but you can do it via CTE, temporary tables, or subqueries like the example below.

/* Invalid syntax - the 'for system_time as of' breaks this query. */
select T.ID, 
       T.Name,  
       OldSalary = S.Salary,  
       NewSalary = T.Salary
from dbo.TemporalTest1 T 
inner join dbo.TemporalSD S  
    on T.ID = S.ID 
    for system_time as of '2018-05-20 00:00:00.0000000'

/* Subquery version that works: */
select T.ID, 
       T.Name,  
       OldSalary = S.Salary,  
       NewSalary = T.Salary
from dbo.TemporalTest1 T 
inner join ( 
    select ID, Salary  
    from dbo.TemporalSD  
    for system_time as of '2018-05-20 00:00:00.0000000' ) S  
    on T.ID = S.ID

/* CTE Version that works: */
with OldSalaryTable AS ( 
    select ID, Salary  
    from dbo.TemporalSD  
    for system_time as of '2018-05-20 00:00:00.0000000'
) 
select T.ID, 
       T.Name,  
       OldSalary = S.Salary,  
       NewSalary = T.Salary
from dbo.TemporalTest1 T 
inner join OldSalaryTable S  
    on T.ID = S.ID

The download for this presentation is available on my Speaking page. I hope you had as much fun as I did and thanks again for the opportunity. :-)

-David.

Advertisements

Speaker Idol. I’m in.

A little over a week ago, Denny Cherry posted the 12 contestants for this year’s Speaker Idol competition at the PASS Summit. If you’re unfamiliar with the event, it’s patterned after the “American Idol” singing competition, where people compete for a record contract, except that you’re in for a speaking slot at next year’s PASS Summit. Sounds like fun, doesn’t it? :-)

If you look close and have a good attention span, you’ll see my name at the bottom of that list. So I will be presenting a five minute technical session on… something. I’m going back and forth between a couple of different things. The difficulty is presenting a complete technical concept in a professional manner with a time span so short. Am I confident that I can do it? Absolutely. I feel like I have enough technical presenting under my belt at this point to do this.

Am I worried about the competition? Nope. But not in the way you might think. You see, I don’t really view this as a competition in the traditional sense. I know a couple of the other participants personally, and have seen more than one of them present before. They’re good – very good. So why aren’t I worried? Because there’s nothing to worry about. There’s no way to “lose”.

At the very least, I get to present a five minute session to a panel of experienced speakers who are going to give me valuable feedback on my presentation skills. Is that not worth participating? I get to work with 11 peers on improving my speaking skills. Yep – totally worth it. And whoever wins – good for you. If I don’t get the speaking slot for 2016, then I just submit as I normally would. See? Nothing to lose and plenty to gain.

Actually, now that I think about it, the people I feel bad for are the judges. Last year’s event was a really difficult decision. So… many… good… presenters… I do not envy the job of the judges one bit. In fact, I’m glad I’m the one on stage. All I have to do is present.

I can do that. :-)

See you there?

-David.

SQL Saturday Roundup

After having an absolute BLAST in Cleveland for SQL Saturday 241, I got busy getting some things ready for the rest of this year. Hopefully some good stuff coming your way.

In addition to Cleveland, I have a few more SQL Saturdays coming up that I wanted to highlight. The schedule page has been updated to reflect this. My session, DBA 911 – Database Corruption, continues to be very popular. I should really add a next-level session with more on different types of corruption, data internals and CHECKDB. That would be fun.

SQL Saturday 287 in Madison WI – I’ll be presenting and volunteering there on March 29 2014. This is a totally new city for me, and I’m looking forward to meeting the team up there, and checking out what Madison has to offer.

SQL Saturday 291 in Chicago IL – I’m definitely no stranger to Chicago, or to their SQL Saturdays, but this time I get to view it from the speaker’s perspective, instead of an attendee. Definitely looking forward to catching up with everyone there.

SQL Saturday 299 in Columbus OH – I’m helping to organize this one a bit (or at least as much as my schedule has allowed recently…)  We’re still accepting speaker submissions, and looking for volunteers.  If you’re interested in speaking, please sign up on the website.  If you’re looking to volunteer, you can do that there as well, or feel free to contact me personally if that’s your preference.

I hope to see you there. Thanks for reading.

-David.