Advanced T-SQL Webinar / Free Training

Thank You!

First of all thank you to everyone who attended my T-SQL presentation on 5/10/2016. We had over 1400 people registered for the training and 871 people joined and listened in, that’s a lot of people! I also received a lot of good and encouraging feedback so thank you for that as well.

Resources

The number one question I received is will the T-SQL scripts be available for download. Of course they will. If you want to download the T-SQL scripts that I used for my presentation you can find those here:

Free Recording:

The full one hour webinar that I did for Pragmatic Works on 5/11/2016 can be found here:

Questions and Answers:

I just received the list of questions from the webinar so I will get this section updated in the next couple days. I wanted to go ahead and post this blog so you could have access to the SQL Scripts.

Question:

Les Said: BTW, best presentation EVER!!!! Very clear and straight to the point in each case. Congrats!!!

Answer:

Thank you!, Best question ever!

Question:Recursive CTEs in SQL

Ken Asked: Should anchor be unique? What happens if there are two rows returned in the Anchor member?

Answer:

Hey Ken, the anchor member does not need to be unique here. In our example we were specifying that the CEO is at the first level or Level 0. If there are multiple members in the Anchor then multiple members would show up at Level 0.

Question: Pivot in SQL

Travis asked: Can you do multiple columns, such as minutes and cost?

Answer:

Travis I believe you are asking about the pivot example that I showed in my webinar. The answer is yes, you can definitely do a double pivot or pivot on multiple columns. I will write a blog on how to do this in the next week, so please check back!

Question: Merge in SQL vs. SSIS Update

Vineet asked: How does the merge statement compare to updates done in SSIS.

Answer:

The only native built in update capability that we have in SSIS is the OLE DB Command so I assume that this question is in regards to comparing the merge with the OLE DB Command. The merge pattern will perform light years better than doing updates in SSIS using the OLE DB Command. The merge pattern I showed in the webinar is a very popular design pattern used for loading data warehouses.

Question: SubTotals with CTEs in SQL Server

Vineet asked: Can you share CTE Examples on your blog.

Answer:

Hi again, you were not the only one to ask this question as I have also received a few emails requesting this example. I will write a blog post with an example on this in the next week, so please check back!

Question: What SQL Books would you recommend?

Answer:

There are a lot of great SQL Books out there. I will stick with one author here. I have three books by Itzik Ben-Gan.

  • T-SQL Fundamentals
  • T-SQL Querying
  • T-SQL Programming
Advertisements

11 thoughts on “Advanced T-SQL Webinar / Free Training

  1. Les Montani May 11, 2016 / 6:27 pm

    Just wanted to share a dynamic pivot:

    declare @sql varchar(max), @pt varchar(max), @Aux varchar(max)

    declare C cursor for
    select distinct c from [Table_1] –Select one of each values that will become columns
    where c !=’c’ — had to put this because the column name can’t be the same as any value in it.
    open C
    fetch next from C into @Aux
    — Loop to concatenate values comma separated
    while @@FETCH_STATUS = 0
    begin
    set @pt = isnull(@pt,”) + ‘[‘ + @Aux + ‘],’
    fetch next from C into @Aux
    end
    close C
    deallocate C

    set @pt = left(@pt,len(@pt)-1)
    –select @pt

    — Script to show pivot table —
    set @sql =
    ‘select * from [Table_1]
    pivot (max (id) for c in (‘ + @pt + ‘)) as Aux

    print @sql

    exec (@sql)

  2. Les Montani May 12, 2016 / 2:52 pm

    A couple more of interesting things:

    How to create test data:
    ;WITH
    Pass0 as (select 1 as C union all select 1), –2 rows
    Pass1 as (select 1 as C from Pass0 as A, Pass0 as B),–4 rows
    Pass2 as (select 1 as C from Pass1 as A, Pass1 as B),–16 rows
    Pass3 as (select 1 as C from Pass2 as A, Pass2 as B),–256 rows
    Pass4 as (select 1 as C from Pass3 as A, Pass3 as B),–65536 rows
    Pass5 as (select 1 as C from Pass4 as A, Pass4 as B),–4,294,967,296 rows
    Pass6 as (select 1 as C from Pass5 as A, Pass5 as B),–18,446,744,073,709,551,616 rows
    –I removed Pass5, since I’m only populating the Numbers table to 10,000
    Tally as (select
    –row_number() over(order by C)
    abs(checksum(newid())) % 1000000
    as Number
    from Pass4 –Pass6
    )

    Select count(*) from Tally –Pass4
    Select * from Tally –Pass4

    How to create random numbers 9and dates):
    select
    abs(checksum(newid())),
    abs(checksum(newid())) % 365,
    dateadd(d,abs(checksum(newid())) % 365, ‘2016-01-01’)

  3. Terry Zieke May 12, 2016 / 6:37 pm

    You talked about two really good reference books, what are the names of those books?

    • Mitchell Pearson May 13, 2016 / 12:42 pm

      Hi Terry, Thanks for reminding me. I have updated the post. Thanks!

  4. Cristhian June 2, 2016 / 5:18 pm

    Hi Mitchell, thanks for your training. It really awesome! Very clear examples and great content, I found this very useful when working with TSQL.

  5. Srinivas Kativarapu August 2, 2016 / 7:18 pm

    Thanks Mitchell for todays session with Pragmatics works. It was like a recorded session very clear and precise. Got better understanding on some concepts that I already use and thought I know :).

    • Mitchell Pearson August 2, 2016 / 7:20 pm

      Thanks! This blog post is from a following webinar I did. I will post a follow up blog to this webinar this evening.

  6. Lisa Modglin August 2, 2016 / 7:24 pm

    I got a lot out of the webinar today. I’m already applying some of the functions you reviewed and was able to recreate some of them from my notes, but am missing something on others. I look forward to working through more examples as soon as you post the scripts.

    • Mitchell Pearson August 2, 2016 / 7:27 pm

      Thanks Lisa, I will have the scripts uploaded by end of day. I just have to clean up a few things. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s