วีดีโอ

The always important caveat "outside of New Zealand"

never using table vars, very tricky and very unexpecable in real world applications. it is there on SQL just like that, for no reason! 😅

This becomes even more fun when that int column is also an identity column...

Table variables would so useful if they worked. Imagine if we instead of implicitly sending table data around and checking if it exists and holds something sensible, could just explicitly send it in parameter...but no who needs that...

Interesting timing on this video. Just today, I rejected a pull request where the developer used a @tableVariable instead of a #tempTable. I did my own comparisons just now between @tableVariables and #tempTables myself similar to your test scenario. What is revealing in the Properties of the final SELECT of the query plan is that the query using the #tempTable has OptimizerStatsUsage available to it whereas the query using the @tableVariable does not. It’s as if the SQL Optimizer is saying “You go figure it out yourself! Good luck!” As for your estimation of 3 rows on your Posts table query, I wonder if there is a cached plan somewhere that is pinning usage of that table to think it has 3 rows for certain JOIN scenarios. As a Dev Manager having DBA and DB Developer experience since SQL Server 2000, I wish Microsoft would just sunset table variables and be done with this nonsense; or maybe under the cover start treating @tableVariables like #tempTables. The dynamically generated #tablename is already there for the @tableVariable in TEMPDB during its usage. 🙄

I find table variables useful mainly for preserving data after a transaction rollback. Otherwise, I prefer temporary tables for their flexibility and performance.

Wow, this is amazing! I can tell a lot of effort went into making this video. I can't wait to see more!

Best video ever

Nice trick with TOP (1)

Got to love the spirit, go Erik go!

I am always astonished that the SQL engine cannot implicitly infer a count aggregate will only ever return 1 row. What do those MS interns do with all their time?

I am gonna start following this playlist, any prerequest sir ?

We have the INT to BIGINT dilemma approaching for a few of our tables. Switching to BIGINT is only the beginning. The real work resides in ensuring we find 100% of the existing INT column references in all the stored procedures, functions, ETL packages, etc. Then full regression testing.

This is one reason why I'm glad that int is a 64-bit number in CockroachDB. Also, no SCH lock-waits and PK changes are online and surprisingly fast.

I consider this video a pure gold bar, and glitches as mud and dust, which I only need to remove. Or accept.

Thanks Erik! This was very helpful. On a side note, why do you think Azure and Fabric are not good products?

Guys that setup GUID's as identity columns spiking the football right now...probably.

"At some point you gotta dance with who brung ya". Just made my day. 😊

It is so stupid this isn't a meta-data only change.

Andy's page is the best writeup on shadow table walkovers. Legit awesome stuff.

APPRECIATED

The next year that starts with zero is 2100 or 00

It's always a joy watching Eric make queries so quickly!

Have a leave, you deserve it ♥

Happy Chrismants!

Thanks for the explanation, it was very useful! Sorry to ask, but where may I have access to the script of this video?

For "y_m_Yes_and" , I like to use : Year * 100 + Month

is columnstore_helper table used in the last query is materialized view or a columnstore index?

I have a question that is outside the scope of this tutorial. When I perform a rebuild of the indexes on the database, its size increases, which is really surprising because I was expecting the database size to decrease. On the other hand, the unallocated space decreased. Any explanation, sir? I have been searching for the right answer, but none has given me a reasonable explanation.

What a trick on that column store helper! 😮

4:28 It's funny, I had exactly the same idea. I was wondering how Erik was planning to spend 16 minutes to say "just use batch mode".

A W E S O M E.

😁

i thought you were more of a Misfits guys, but DMX is fun. "InDEX is gunna give it to ya"...ill see myself out

Do you find using DATA_COMPRESSION = PAGE on indexes has any disadvantages?

i bit miss that you did nothing else than nonSarg column put on second level. this is pretty funny, because many of us using count distinct to see density of column, but forget about SARGability! thank you for evangelisation in this! 😊

I think I'm going to make an Eric Darling video bingo card: * SQL Server interns * Operator timings making no sense * FORCESEEK * CROSS APPLY trickery * Dislike for OR clauses * Zoomit failing * Batch mode fixing everything * Hints of Postgres supremacy * Paul White worship * The armpit

😁

almost to 6k.

First

Tak!

I think this explains how I've managed to go my whole career without writing an indexed view.

I laughed for like 15 mins at the SQL comments for the first 1/3 of the video, then I cried for the last 1/3 of the video because I had no idea how I could take advantage of column store indexes. In the end of this emotional rollercoaster, I thank you.

Funny you mentioned web edition last episode. In 2018 I was asked if I was keen to take on a hopeless case with cpu att 100? Sure, sounds like fun! Did it and thats how I found Darling Data. Happy to report its doing great still thanks to your advice :)

Coldfusion! I’ve heard of that. Heard of code written that generates deadlocks. How fun!

Why for the plan with clusterd index scan server doesn't use key(row) locks?

Please explain about Table Hint such as TABLOCK, ROWLOCK thanks!

Been upset with third party vendor code lately that uses a FORCESEEK hint to update a few million records. The web app will lock up for an hour or so and causes all sorts of fun problems for downstream products

If there is a single thing that I'm going to take away from Erik's videos, it's to not fear FORCESEEK.

Many moons ago at my very first dev job, my first mentor basically wrapped my knuckles with a ruler when she saw me right click a missing index suggestion. It was nice to have someone early on in my career teach me "you cant always trust microsoft" with things like this. Nice content by the way :)