Breakthrough SQL Skills By Rick Losk Last Updated Feb. 15, 2018 No Comments Yet Sorting SQL users into skill level groups is tricky. There are many skills to consider, and many different learning paths. Still, over the years I’ve noticed patterns of knowledge gaps and points of confusion, centered on certain skills and concepts. SQL Skill Level All Levels Post Length Medium (1-2K) These "Breakthrough SQL Skills" define the boundary between one skill level and the next, and they offer an opportunity, but also a challenge. Conquering them opens up new possibilities, but some business SQL users plateau in their learning progress because the time and effort needed to overcome the challenge doesn’t seem worth it. Table of ContentsSQL Skill LevelsBreakthrough SQL Skills – Examples#1: Multi-Table Queries#2: Index Basics#3: Subqueries#4: SQL Programming#5: SQL PerformanceThe Bottom Line SQL Skill Levels Before we look at examples, here’s my list of SQL skill levels common among non-IT business users: Novice: Creating & loading tables, writing basic queries, using built-in and aggregate functions. Analyst: Relational DB model, writing multi-table queries, using basic subqueries and stored procedures. PowerUser: SQL programming, ETL, automation, complex queries and subqueries, performance. And here are important Breakthrough SQL Skills for the boundaries between levels: Beginner to Novice: Basic queries, tables, views, built-in & aggregate functions. Novice to Analyst: Multi-table queries, more built-in functions, index basics. Analyst to PowerUser: Subqueries, CTE, EXISTS, programming, logging & error handling. PowerUser to Developer: Performance, ETL, dynamic SQL, CROSS APPLY, source control. NOTE: The above lists reflect my opinion based on experience. Breakthrough SQL Skills – Examples #1: Multi-Table Queries Understanding the variations of JOIN & UNION is a challenge for many Novice-level users. A common hurdle is the behavior of OUTER JOIN, which can be confusing when first encountered. It’s an area where training can avoid a lot of frustration. This skill increases the range of what you can do with a single query. Useful queries against the back-end database of just about any business system (CRM, HR, Finance, etc.) require joining multiple tables. Even a simple data mart star schema joins fact tables and dimension tables. Mastering this breakthrough SQL skill is a big step forward. #2: Index Basics Indexes are another stumbling block for some business users. The CREATE INDEX syntax is not difficult, but there are many "rules of thumb" for proper index usage. The best approach can vary from one situation to the next, and the data itself is often a factor. Focused effort is required to develop your own methods for figuring out what to do about indexes. Some might ask, "Why bother? Queries always complete, even without indexes." My answer is: "Yes, they do … eventually." If your SQL is valid, the query optimizer will find a way to deliver the results, but "eventually" could mean hours or even days. Indexes are an important tool for managing SQL performance. Sooner or later, you’re going to need to understand how to use them. Now, you may have noticed that at the start of this post I listed "Index Basics" as a breakthrough skill at the "Novice-to-Analyst" boundary. In fact, most Novice-level users and some Analyst-level users don’t use indexes properly (or at all). Even so, I believe SQL users should be exposed early on to index basics like using a primary key, reading the execution plan to identify table scans and missing indexes, using INCLUDE to cover non-key columns, dropping indexes before and re-creating them after a full table refresh, etc. #3: Subqueries Sometimes I don’t understand why the subquery concept is hard for some Analyst-level users to grasp. "Just put parentheses around a query and treat it like a set of rows." Piece of cake, right? It’s true that some variations depend on context (correlated, derived, EXISTS, etc.), and performance can be an issue, but still … I guess it’s like trying to learn the basic golf swing. "Just hit the ball with the clubface square." Yeah, that’s a lot easier said than done. It’s probably fair to say that mastering either skill tends to be an epiphany event: once you get it, a new world of possibilities opens up, but until then it takes a lot of extra effort to get the hang of it. Using subqueries is one of the most important breakthrough SQL skills, and I’m convinced it’s worth the amount of work it takes to learn it. But I have to admit there are times when it seems like all the examples in the world can’t dissolve the glazed-over look in the eyes of someone struggling to grasp the subquery concept. #4: SQL Programming Unlike the subquery concept, I have found it relatively easy to teach the basic use of stored procedures: "Take that script you’ve been running manually and put a CREATE PROCEDURE statement in front of it." Again, there’s more to it (compiling vs. execution, parameters, control blocks, nesting levels, error handling, etc.), but the ability to automate SQL processing is appealing to anyone who has been writing queries for a while. I consider the use of stored procedures, user-defined functions, and other programming techniques to be breakthrough SQL skills at the Analyst-to-PowerUser boundary because they’re such versatile additions to your toolbox. #5: SQL Performance For the final example I’m going with SQL Performance, even though I listed it as a breakthrough skill at the PowerUser-to-Developer boundary, which isn’t relevant for most non-IT business SQL users. This is a large topic involving many concepts and techniques. It’s easy to write a query or procedure that slows processing to a crawl, but it’s not always easy to figure out what the problem is and how to address it, let alone master techniques for avoiding bottlenecks in the first place. It takes study and practice to develop reliable skills in this area. But here’s the thing: It is just plain unacceptable for a monthly update of a few million rows to take days to complete, or for a basic report to take more than a minute to return results. There are simple ways to avoid most performance issues, and at some point you need to recognize that upgrading your SQL performance skills is no longer optional. The Bottom Line Why is the "Breakthrough SQL Skills" concept important? To get the most out of your investment of time and money in upgrading your skills, you need to follow a SQL learning plan that focuses on the specific topics that are most helpful in advancing from your current SQL skill set to the next level. This focus is what you should look for in any SQL course. While it’s not a bad thing to be exposed to a lot of new information in a short period, SQL training tends to "stick" best when you can see clearly how it will help you get better results in your work today. This motivates you to put your new skills into practice as soon as you learn them, which reinforces and accelerates your learning experience. Make sure your training investment gives you what you need, when you need it. Focusing on the breakthrough SQL skills is the best way to advance to the next level of SQL expertise, and beyond.