What was the first SQL Server concept that stumped you?

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:

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.

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.

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!”

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.

That constantly surprises people in classes, too!

When I query msdb.dbo.backupset, I still have to remind myself, “Oh yeah, D isn’t Differential.”


Previous Post
Where do I file bugs and feature requests for Microsoft Data Platform products?
Next Post
SQL Server 2019 Result Set Caching?

20 Comments. Leave new

  • I’m not a cool DBA, I don’t use the twitter, so I didn’t get to respond initially, but for me Window Functions baffled me for the longest time. I was given a query that used one and told to run it and was amazed by the seemingly voodoo magic that produced a running query within a group of a partitioned column.

  • Dynamic sql combined with linked servers using openquery hitting different databases depending on parameters 😀

  • Parameter sniffing. A procedure with parameter for @ID took over 15 minutes to complete. Adding a variable inside the procedure (declare @LocalID int = @ID) and using it instead ran in less than 5 seconds.

  • Sub-queries, sub selects, joins and the like seemed to be such a mystical decision on when they were needed. The eureka moment came when I was introduced to and understood CTE’s. The doors flew open, the clouds dissipated and the ability to write and understand more meaningful queries became a reality!
    (I still hate pivots)

    • I can handle pivots (though they don’t do everything I WANT them to do), and CTEs are great (though they are often seen as “The Answer To All Problems” (the first query I ran across that used 15 CTEs in one query, including one that joined the output of two CTEs just to pass that information to a third CTE permanently damaged my forehead).

      Recursive CTEs require a full refresher course before each use, though.

  • Not exactly a “concept” that I had problems with but the first thing about SQL Server that gave me major headaches was … believe it or not .. the fact that DELETEs were immediate and permanent. I was moving from Foxpro where you could undelete rows until you manually ran a cleanup command. I had a number of very unhappy moments as I learned that wasn’t a feature of all database systems.

  • Regarding joins, my favorite one is the FULL OUTER JOIN. Knowing how to use it properly has helped me find missing data between live databases and backups, or before and afters on complex database changes. It’s one excellent tool for investigations and troubleshooting.

  • WHERE. When I was just a callow non-techie, fumbling through an ACCESS “database” “maintained” by other non-techies, I started using this “SQL” stuff, but it took me a few days to learn about the WHERE clause. That was such an amazingly magical moment!

  • A very general concept first confused me. That was “How can a company sell a database server that is both 1) successful and 2) still required a huge industry of consultants, specialists and researchers to make it run well in production”. Still confuses me to this day.

  • I learned SQL and writing SQL scripts with Oracle databases in the 90s. There are some cool features which I’m still missing in SQL Server. And till now I can’t understand why those are not there.
    – why do I have to check whether a stored procedure, function, trigger, index, etc. is already there and then have to drop it before running a CREATE statement? The Oracle CREATE OR REPLACE does that for me.
    – a “real” programming language which I can use in triggers, SPs, etc. PL/SQL is so much more powerful than T-SQL. Why did Microsoft introduce almost no enhancements in T-SQL in all those years? T-SQL is still boring and awful.

  • Robbert Michel
    March 1, 2019 8:35 am

    SQL Server date formats… Everything else I can do from memory, but the date formats do not make any sense at all. You just need to have them bookmarked, or taped to your desk somewhere.
    105 = dd-mm-yyyy
    110 = mm-dd-yyyy
    111 = yyyy/mm/dd
    WTF? why would you enumerate that?
    Gods do I mis the simple data formats that Oracle uses.

  • For me it was transaction log truncation- why it’s necessary and how it’s not the same as shrinking.

  • Foreign Keys. Understood them immediately, but couldn’t explain them and the T-SQL is still something I have to Google.

  • Hmm, well Brent… I’ve been writing SQL Queries since 2000 and sometimes I needed to have a formatted date. In Holland we use dd-MM-yyyy so back in SQL 2000 I figured out I could do it like this:

    right(’00’ + cast(datepart(day, getdate()) as nvarchar(2)), 2) + ‘-‘ +
    right(’00’ + cast(datepart(month, getdate()) as nvarchar(2)), 2) + ‘-‘ +
    cast(datepart(year, getdate()) as nvarchar(4)) as FormatedDate

    Over the years I got pretty good (if I may say so myself) in writing the most complex queries and most co-workers knew when to find me to help them fix queries.

    So, somewhere in 2016 or 2017, one of our consultants dropped in with a query. Now this guy was just starting with SQL and by far not the greatest. But in the query he presented to me he used:

    select format(getdate(), ‘dd-MM-yyyy’)

    Up until that point I still was using my old “proven technology” and I was baffled.

    The Eureka moment was not finding out it could be done so much easier, but realizing that if you don’t keep your skills up to date, you are missing out on a lot of the good stuff! The realization that one should never stop learning.

    p.s. To save face, I told the guy that my way of doing it was to keep backwards compatibility. 😉


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.