How to gain advanced T-SQL programming skills

This article aims to provide various tricks that help to gain advanced T-SQL programming skills.

Introduction

In the last decade, organizations have started to generate data with incredible momentum. At the same time, they understood the importance of data and began to process and model data effectively and they also started interpreting the data results to create actionable plans. As a result of all this, simple queries will not be enough to create advanced data models. Therefore, it is an undeniable need for database professionals to add advanced T-SQL programming to their skill sets.

The starting point of learning advanced T-SQL programming

In order to learn advanced T-SQL techniques, all we have to do is work smartly thus we can be more productive in our learning period. At this point, we need to decide which training method will help us because some people want to reinforce the topics through the book, while others may think they can be more successful in online education.

However, if we decide to complete this learning period by ourselves, we need to create a content outline that includes which topics we need to learn. The following topics will benefit us if they are on our content list:

Aggregate functions (AVG,MIN,MAX,SUM,COUNT)
Using SQL Server stored procedures with input and output parameters
User-defined functions
After and instead of triggers
Usage details of the common table expressions (CTE)
Global and local temporary tables
Pagination in SQL Server
CUBE, ROLLUP, and GROUPING SETS extensions
TRY-CATCH blocks in SQL Server
CASE statements in SQL Server
DO-WHILE statements in SQL Server
PIVOT and UNPIVOT operators

Learn Query Optimization Techniques

Another point that helps to gain advanced T-SQL programming skills is to learn query optimization. Assume that, we wrote a query and it retrieves the correct data from the database but it runs very slowly. In this case, it would not be the right approach to evaluate this query as very successful. In my thought, learning query optimization is the most important factor that helps improve advanced T-SQL programming skills. In order to learn query optimization, we can look at the following topics:

Fundamentals of the index (clustered,non-clustered, and columnstore) structures in SQL Server
Sargable expressions
Interpret the execution plans in SQL Server
Understand the execution plan operator
Understand the query processing phase of the query optimizer

To be proactive in your learning

Continuous learning is a philosophy that is based on always expanding your knowledge to gain new skills and expertise. In this concept, we can always expand our skills thus adapt the life changes more easily. We can apply this concept to our advanced T-SQL programming learning period because new versions of SQL Server are released about every two years with many new features. After learning the fundamentals of the advanced T-SQL, we also require to follow up on the new version features.

Another point about proactive learning is taking part in communities. In the communities, we can learn incredible things or witness heated discussions. Such as, we can join LinkedIn groups or try to attend some WhatsApp, Slack, or Telegram groups that are set up particularly for the database professionals. So that, we can share our questions on this community platform. At the same time, we can subscribe to some blogs that post SQL Server-related articles. Here, List of top SQL Server blogs, we can find all popular blogs which create content about the SQL Server.

At a last, Microsoft offers some certification exams that validate our expertise. Study for these exams always helps to improve our advanced T-SQL programming skills and provides an advantage in our professional careers.

Don’t hesitate to ask questions

Normally, we sometimes have difficulty understanding certain topics during our learning process. This is where we have some reservations about how and where to ask our questions. If there are experts on the subject from your friends at your workplace, you can ask anything you can think of. Another point is that you can research the issues you are stuck on the internet because be sure that you are not the first person to experience a problem or go through this process. Despite all this, if you could not get a satisfactory answer to the questions or questions in your mind, this time we can turn our direction to StackOverflow.

StackOverflow is a platform where you can ask questions and they are answered by the people who know them. It has become such a platform in the last decade that I think it is at the top of the list of everybody dealing with computer programming. However, before asking questions in Stackoverflow, it is useful to research whether the same question is asked because otherwise, that link will be shown to you as the answer to the question. Another point about Stackoverflow is how to ask a question because you need to ask the point in your head in plain and understandable language so that people can understand it. In addition, at this point, you will earn points as you answer questions about the subjects you have mastered as much as you can. At the same time, reading the most viewed and rated questions will benefit us.

Summary

This article mentioned some tricks that help to gain advanced T-SQL programming skills. I hope you find them useful.

Author
Recent Posts

Esat Erkec

Esat Erkec is a SQL Server professional who began his career 8+ years ago as a Software Developer. He is a SQL Server Microsoft Certified Solutions Expert.

Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.

View all posts by Esat Erkec