Hi, I’m Doug Lane, and I’m the instructor for the T-SQL Level Up course. A little bit about me. I began my career with SQL Server way back in the year 2000. I had just earned my MCSE with Windows NT 4. That tells you how long ago that was, and I was asked to do a little bit of database administration. Over the years I progressed through desktop application development, web app development, BI development, and finally in 2013 I joined Brent Ozar Unlimited as a consultant.

The reason I made this particular course is I still remember how it felt those first couple of years. Remember the year 2000, 2001, there wasn’t a lot of video on the internet, and I really enjoy watching videos in order to learn things. As a way of sending something out to the people that are like 2001 Doug, I wanted to make a video course on T-SQL with all the stuff that really would have helped me get ahead at the time. We’ll cover stuff like case expressions, computed columns, window functions, and reasons why you should be using set based operations as opposed to cursors. I’m hoping that, like 2001 Doug would have, you’ll get a lot of benefit out of this, and hopefully you’ll be kind of entertained in the process. Enjoy, and thanks for watching.

11 Comments. Leave new

  • Nico van der Walt
    April 27, 2017 3:50 pm

    Thanks Doug for an excellent series of videos. A quick question. Can set based queries to replace cursors cover all scenarios, or would there be some cases where cursors are unavoidable?

    Reply
  • Matthew Giles
    October 2, 2017 10:41 pm

    IT’S A TRAP!!!

    Reply
  • This was a great course to fill in some gaps! Presented very clearly.

    Reply
  • Great course!! More, please sir.

    Reply
  • Great and very useful course. One question, as I work with french dates always, I tried to add columns, type Date to table dimDate and then updated the insert statement in the create object script as follows

    , CONVERT(date, CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) as datetime), 5) as FrenchShortYear
    –, FORMAT ( CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) as date), ‘d’, ‘fr-FR’ ) AS ‘FrenchLongYear’
    , FORMAT( CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) as date), ‘dd/MM/yyyy’, ‘fr-FR’ ) AS ‘FrenchUSLongYear’
    , CONVERT(VARCHAR(12), CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) as date), 7) as ‘FrenchLongYear’

    I always get errors and execution stops, however when I change the columns type from DATE to varchar (12), statements executes successfuly, can you please help?

    Thanks

    Reply
    • Salam – for questions, we really need the entire script at a point where we can just run it to see what you see. Otherwise it’s tough to guess what you’re doing.

      Reply
  • Here are the 2 scripts, creating the table, and the updated insert in the dimTable

    — The Insert statement
    INSERT dbo.DimDate
    ( ShortDate,
    FullDate,
    MonthShortName,
    MonthLongName,
    FrenchShortYear,
    FrenchUSLongYear,
    FrenchLongYear,
    DateAge

    )
    SELECT CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) AS DATE),
    CAST(DATENAME(mm, DATEADD(dd, Number.n-1, ‘1/1/2015’)) AS VARCHAR(10))
    + ‘ ‘
    + CAST(DATEPART(dd, DATEADD(dd, Number.n-1, ‘1/1/2015’)) AS VARCHAR(2))
    + ‘, ‘
    + CAST(YEAR(DATEADD(dd, Number.n-1, ‘1/1/2015’)) AS VARCHAR(4)),
    CAST(DATENAME(mm, DATEADD(dd, Number.n-1, ‘1/1/2015’)) AS VARCHAR(10))
    ,
    CAST(DATENAME(mm, DATEADD(dd, Number.n-1, ‘1/1/2015’)) AS VARCHAR(10))
    + ‘ ‘
    + CAST(YEAR(DATEADD(dd, Number.n-1, ‘1/1/2015’)) AS VARCHAR(4))
    , CONVERT(date, CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) as datetime), 5) as FrenchShortYear
    –, FORMAT ( CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) as date), ‘d’, ‘fr-FR’ ) AS ‘FrenchLongYear’
    , FORMAT( CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) as date), ‘dd/MM/yyyy’, ‘fr-FR’ ) AS ‘FrenchUSLongYear’
    , CONVERT(VARCHAR(12), CAST(DATEADD(dd, Number.n-1, ‘1/1/2015’) as date), 7) as ‘FrenchLongYear’
    ,
    CASE WHEN DATEADD(dd, Number.n-1, ‘1/1/2015’) BETWEEN ‘1/1/2015’ AND ‘3/31/2015’ THEN ‘Age of Goats’
    WHEN DATEADD(dd, Number.n-1, ‘1/1/2015’) BETWEEN ‘4/1/2015’ AND ‘4/30/2015’ THEN ‘Age of Copper’
    ELSE ‘Age of the Unknown’
    END
    FROM dbo.Number
    WHERE DATEADD(dd, Number.n-1, ‘1/1/2015’) <= '4/30/2020'
    ORDER BY Number.n

    — The table which does not work
    CREATE TABLE [dbo].[DimDate](
    [ShortDate] [date] NULL,
    [FullDate] [varchar](20) NULL,
    [MonthShortName] [varchar](10) NULL,
    [MonthLongName] [varchar](15) NULL,
    [FrenchShortYear] [date] NULL,
    [FrenchUSLongYear] [date] NULL,
    [FrenchLongYear] [date] NULL,
    [DateAge] [varchar](20) NULL
    ) ON [PRIMARY]
    GO

    — The table that works

    CREATE TABLE [dbo].[DimDate](
    [ShortDate] [date] NULL,
    [FullDate] [varchar](20) NULL,
    [MonthShortName] [varchar](10) NULL,
    [MonthLongName] [varchar](15) NULL,
    [FrenchShortYear] [varchar](12) NULL,
    [FrenchUSLongYear] [varchar](12) NULL,
    [FrenchLongYear] [varchar](12) NULL,
    [DateAge] [varchar](20) NULL
    ) ON [PRIMARY]
    GO

    Reply
    • Salam – okay, let’s step back for a second. Instead of giving you an answer, I’m going to teach you how to ask a great question. This lesson will help you get the fastest answers when you run into problems.

      Your goal, when you ask a question, is to give people as little information as possible, but enough that they can see what you’re asking.

      You don’t want them to have to read any extra stuff, but you DO want them to have enough information to see what you’re seeing. It’s a tricky balance. You’re posting a whole big script that relies on other tables, and I can’t tell which parts are erroring.

      Read this post by Jon Skeet to learn more about the technique: https://codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question/

      Then, after you’ve read that, post your question on a Q&A site like https://dba.stackexchange.com. Don’t post it again here: this is also part of your lesson, learning where to post a question. The biography page for a teacher just isn’t a great place to post general questions.

      Hope that helps!

      Reply
  • Kevin Henriques
    June 9, 2020 7:59 am

    Hi Doug

    Very informative and helped me simplify couple of sql queries I was writing.

    Thanks

    Reply
  • Doug/Brent, This was Very Helpful. Thank You

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}