12. About Doug (2m)
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.
- 01. Prologue and Script Files
- 02. Thinking in Sets (5m)
- 03. Number and Date Tables (10m)
- 04. Case Expressions (17m)
- 05. Computed Columns (10m)
- 06. Windowing Functions – Design (21m)
- 07. Windowing Functions – Performance (8m)
- 08. Replacing Cursors, Part 1 (6m)
- 09. Replacing Cursors, Part 2 (5m)
- 10. Replacing Cursors, Part 3 (7m)
- 11. Let the Adventure Begin! (9m)
12 Comments. Leave new
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?
Nico – there are some DBA tasks that SQL Server requires to be run separately, not in sets, like BACKUP DATABASE.
IT’S A TRAP!!!
This was a great course to fill in some gaps! Presented very clearly.
Great course!! More, please sir.
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
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.
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
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!
Hi Doug
Very informative and helped me simplify couple of sql queries I was writing.
Thanks
Doug/Brent, This was Very Helpful. Thank You
Want to say, Doug, that I really appreciate your style of delivery. You kept the pace slower than many, and steady. But you didn’t skip any steps in the process, and kept the video very visual. It was clear you rehearsed; the videos were free of all verbal clutter! Slow is a good choice, since people can always speed the video up, and you remain intelligible.
I also so appreciate how you brought fun and story into the content. That really makes it stick even better. I’ve been doing SQL Server for 12 years, but learned much more about the performance and abilities of windowing functions than I have ever known!