What’s the first thing you remember struggling with on SQL Server? Your first memory of, “Dang, I just can’t figure this out,” and then you had a Eureka moment later when it was so much more clear?”
I asked folks on Twitter, and the answers were great, so I had to round ’em up here to share ’em with you:
JOINs. I remember just starting out and trying to get the query to return the data that I wanted. I figured CROSS JOIN might be A viable option …it wasn’t. Query was taking a while, went to get coffee, people were pissed
— Dan Clemens (@dan_clemens) February 22, 2019
Oh, man, that was a stumper for me too. I remember having to whiteboard out a couple of tables and think through the logic to put it together.
Pivots and recursive CTEs. Pivots I can just about live with now but I'll nope away from recursive CTEs all day long.
— Steve (@OmegaFluxx) February 22, 2019
I still struggle with those. I have to copy/paste from a starter script whenever I write those, because I just can’t remember the syntax.
My first surprise was my first day learning SQL.
I assumed queries were compiled and not optimized. I saw a from clause like A,B,C,D and filters (joins) in the where clause. I assumed applying the filter on every possible row would be slow. I didn't get why it was fast.
— Michael J Swart (@MJSwart) February 22, 2019
The first time I saw SQL Server rewrite my query in a different order, I wanted to bang on the box and go, “THANK YOU, WHATEVER MAGIC GENIE IS INSIDE HERE!”
figuring out how someone had the permissions that they did, when I'm showing they have none. (I hadn't discovered roles yet)
— Bob Pusateri (@SQLBob) February 22, 2019
Active Directory groups did that to me too at first – as in, “Wait, this person isn’t listed anywhere in the server, how on earth were they able to do that?” Especially when they’d been later removed from the group.
Itzik’s demos are always chock full of those moments for me. He’ll show something, and it’ll vaguely register, but if I’m facing a particular problem at the moment, I’ll learn something new every time.
Not the first thing but certainly something I can remember clearly: memory grants. The eureka moment was discovering that by default a query will take at most ~25% of memory allocated to queries.
— DJ (@CasualFisher) February 22, 2019
That constantly surprises people in classes, too!
Matching Full, Differential and Log to 'D', 'I' and 'L', circa 1999.
— SQL Cyclist (@Kevin3NF) February 22, 2019
When I query msdb.dbo.backupset, I still have to remind myself, “Oh yeah, D isn’t Differential.”
Transactional replication on a SQL 7.0 cluster 🙁
— Dave Wall (@DaveWall79) February 22, 2019