Why You Should Learn T-SQL Before Plain SQL

When taking on a procedural programming language, you need to consider where you're trying to take your skills. There are options out there and completing your research before diving in is critical.

If you decide to stick your toes into the SQL water (whether you pronounce it sequel or ess que el), it's an excellent dip to take. T-SQL syncs with business tools like PowerBI and Dynamics. It takes math functions to the next level and adds user-defined functions to the equation.

Whether you're a database admin, a developer, or an engineer, T-SQL's complex syntax gives you the ability to do more. To enhance your SQL knowledge, you should immerse yourself in T-SQL first, not SQL.

T-SQL 101

Before diving into T-SQL, let's get a bit of context. In the early 1970s, researchers developed plain SQL in a project called System R. Back then, the relational database model was brand new. Although innovative, researchers wanted to advance the tech further. Coming up with a straightforward way to maintain these models seemed like a reasonable next step.

System R was a test to prove that new types of database building could provide new levels of transaction performance. Along with the database, they needed a powerful and flexible way to write and manipulate data, so SQL was born.

As SQL caught on, it inspired many vendors to create their own relational systems (a.k.a. rip them off), starting with Relational Software, Inc. (which later became Oracle) and continuing with the likes of SAP, Borland, Sybase, and Microsoft.

To compete in the SQL field, Microsoft teamed up with now-defunct Ashton-Tate, makers of dBASE, and got a little help from Sybase in 1988. Their goal was two-fold: Save the tanking Ashton-Tate, whose dBASE sales were in sharp decline, and expand upon dBASE to a server-client model. Up to that point, dBASE only ran on local machines. There was no sharing of data across the network.

The result of this partnership was SQL Server. This placed them into the enterprise-level SQL server world, with the likes of Oracle and IBM. The first version ran on IBM's OS/2. By 1993, Sybase and Ashton-Tate both ended their relationships with Microsoft. The tech giant then released the first version of SQL Server for Windows NT.

Competition and Specialization Ramp Up

Vendors all around continued creating and updating their systems. So today, you can't turn around without running into one.

We've already mentioned the big boys still in the game like Microsoft, Oracle, and SAP. The glory days are over for defunct Sybase, Ashton-Tate, and IBM's System R. Open-source databases are now littering the dev scene. MySQL, MongoDB, and PostgreSQL only start to scratch the surface.

However, we're here to talk about Microsoft SQL Server. So what makes SQL Server special? Moreover, what exactly is T-SQL?

It's worth mentioning that ANSI and ISO created standards for SQL syntax in 1988. These standards solidified its ubiquity across computing up to the current day. Neither IBM or any other company owned or copyrighted SQL, it's a language in this context instead of a specific product.

That set the stage for Microsoft to expand upon the now standardized SQL with their own set of proprietary extensions. These became known as Transact SQL or T-SQL. The "transact" name came from new transaction controls Microsoft added. These helped application flow with procedural programming.

The Nuts and Bolts Behind T-SQL

Procedural programming adds the concept of subroutines and functions. If you need a programming refresher, both subroutines and functions are chunks of code separate from your main body of code. You give them a name, and elsewhere in your code you call the subroutine or function by name, pass it some arguments, and it does its thing.

Subroutines and functions make your code cleaner, more compact, less Kill-prone, and more human-readable. Some say both can help performance, and others say they hinder it. As always, it's not the tools, but the implementation by the developer, that makes or breaks their code's efficiency.

All this is true with T-SQL as well. Any SQL query is a line of code. An unwieldy, complicated, multi-step query can be a bugger to troubleshoot or alter. Break it into smaller chunks, and your actual function-calling query becomes a clean one-liner.

T-SQL has more tricks up its sleeve though. It can do math inside of queries, from simple addition up to scientific and trigonometric functions. This unlocked a massive amount of power to do business and engineering analysis of data. This added feature also exposed SQL Server to a whole new market at the time that could not find value with plain SQL. An alternative now existed for expensive niche software to chew data.

The UPDATE and DELETE commands were also beefed up with T-SQL. You can now use them with a FROM command to use joins. This simplified filtering records for applying criteria when updating or deleting. This command sounds pretty rudimentary today, but it simplified what used to be a much more complicated query.

Taking on T-SQL First

After learning about all the functionalities of T-SQL, taking it on first gives you all you need and more. It's an extension of SQL, and the basic underlying SQL essentials are all there. The syntax still requires queries to flow the same way. Data is still in tables connected with critical values. The primary usage of basic commands (like SELECT and UPDATE) are still the same. T-SQL adds frosting on the cake with the user-defined functions and advanced math components.

Starting out with plain SQL, you're going to learn all the same stuff — and that core knowledge follows over to any other SQL flavor. You know that fundamental concepts are the same across any language (variables, functions, loops, and so on). Once you wrap your head around the ideas behind one language, they translate pretty well over to another, only with changes in syntax. SQL is like that, but better, the core syntax pretty much doesn't change.

Once you have the basics down it then becomes a matter of asking what your goal is. Backend web developer? Then you aren't planning on learning SQL super in-depth, only enough to do what you need to do. Since open source currently rules the web dev world, most SaaS startups aren't using SQL Server.

T-SQL is an Enterprise Solution

Are you in the enterprise? Keep one thing in mind, we're talking about a Microsoft product here, and Microsoft still has saturation in the enterprise. T-SQL knowledge will serve you well, if not as a database admin then with business tools such as PowerBI and Dynamics. Let's say your Microsoft shop runs mission-critical, custom-built .NET apps. You bet they're using SQL Server on the backend.

It's worth also looking at specific fields. We could turn this into a Microsoft vs. Oracle discussion. However, SQL Server and T-SQL reign supreme in a variety of areas from data science to healthcare and insurance to genetic research.

The future of SQL Server is very bright, and learning T-SQL reaps dividends. Azure SQL is leading Microsoft's bold cloud charge. Exciting developments of SQL Server on Linux are continuing to plow down the wall gardens of Microsoft shops vs. Linux shops. As you start your journey into SQL, resources here at CBT Nuggets are plentiful, let us help you unleash your inner DBA!