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:
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.
Watching Itzik Ben Gan solve the gaps and islands problems on stage at SQL connections in 2005. The Eureka moment was when I realized that was the solution to a problem I had at that moment.
— Chuck Rummel (@crummel4) February 22, 2019
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!
https://twitter.com/Kevin3NF/status/1098940501636644865
When I query msdb.dbo.backupset, I still have to remind myself, “Oh yeah, D isn’t Differential.”
https://twitter.com/DaveWall79/status/1098940543529304064
UNSUBSCRIBE
Free, 3× a week
Get my new posts by email
Three posts a week, plus a Monday roundup of the best database news from around the web.
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.
I still have to go back to the documentation I have to write one of those though, hahaha.
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.
https://stackoverflow.com/questions/900055/is-sql-or-even-tsql-turing-complete
TL;DR; is has been proved that SQL is Turing-complete, meaning that is as “real” as a programming language as C, C++, …
That is not to say that I disagree with you that PL/SQL is so much more “developer-friendly” than T-SQL. But WRT “real-ness”, both are as real as one can be.
Stefan,
– you have Create or Alert in SQL Server (starting with SQL Server 2016)
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-2017
– you can run R (v2016) or python (v2017) script from SQL Server and in SQL Server 2019 you will be able to run Java.
https://docs.microsoft.com/en-us/sql/advanced-analytics/java/howto-call-java-from-sql?view=sqlallproducts-allversions
From my memories, before stackoverflow existed people were pointing to BOL, it took me a while to figure it out it was Books Online
If you want a real programming language in SQL just use the CLR
Starting from SQL 2016 the CREATE OR ALTER syntax is viable on SQL Server too, it’s really a time/complexity saver.
As far as “real” programming languages in the engine goes, Python is supported in the engine in SQL Server 2017 and Java will be supported in SQL Server 2019
I’d like to know why Oracle programmers still use (and teach) comma syntax from the year 1992. I see it on Stack Overflow every day. Also, SQL Server has that functionality for Create or Replace for 3 years now. So I’m beginning to think the real confusion comes from the Oracle ecosystem and lack of knowledge from Oracle developers… and *not* all other RDBMSes being “behind” Oracle. It’s quite the opposite in 2019.
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:
select
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. 😉