T-SQL CRUD
By Jim Christopher on February 6, 2015
CRUD! It’s everywhere. And by CRUD I mean the Create/Read/Update/Delete operations used by applications that rely on persistent storage. In my career, I would venture that 90 percent of the applications on which I’ve worked have revolved around CRUD operations against a relational database. Transact Structured Query Language (otherwise known as T-SQL) is a superset of the ANSI SQL language that operates on Microsoft SQL Server. Being able to leverage T-SQL is key to incorporating SQL Server in your business workflows and custom software applications.
Editor's playlist objective:
This editor's playlist aims to help make you proficient in using T-SQL to query and manage data on SQL Server 2012. The path begins with gentle introductions to reading and updating data using ANSI SQL, and then guides you through more specialized aspects of applying T-SQL to business problems including working with dates and times, XML data, Common Table Expressions and analytic functions.
Target audience:
This editor's playlist is for anyone new to T-SQL. The path assumes no knowledge of the SQL language or relational databases. That said, the path is focused on the T-SQL language and using it for CRUD operations, not on relational database design. If you’re looking for a course on the design of these relational systems, please see:
Later in the editor's playlist, you’ll notice that several courses discuss the integration of various technologies with SQL Server and T-SQL including XML, XQuery and unit testing. If you don’t already have experience with these technologies, the following courses can help:
Editor's playlist sequence:
Editor's playlist description:
If you’re new to programming, SQL is a fantastic gateway into data science. If you’re already a fluent programmer or scripter, SQL requires a remarkably different mindset than the one you’ve likely cultivated. Rather than thinking about solutions as iterative or procedural operations, you need to start thinking about them as set operations. Either way, you’ll want to start with Jon Flanders’ Introduction to SQL. This course provides a gentle introduction to relational databases and how SQL is used to manipulate and query them.
Although Flanders’ course targets ANSI SQL, this information provides a good foundation for Joe Sack’s SQL Server: Transact-SQL Basic Data Retrievaland SQL Server: Transact-SQL Basic Data Modification courses. These two courses expand on Flanders’ introduction to fold in query and management language features that are specific to Microsoft T-SQL.
You’ll follow up with Christopher Harrison’s SQL Server 2012 Querying (70-461) courses. These are designed to help you pass the 70-461 Microsoft Certification Exam (Querying Microsoft SQL Server 2012) and, as such, they explore many different aspects of querying data from SQL Server including the use of views and stored procedures.
Up to this point the editor's playlist has focused heavily on the R, U and D of CRUD — that is, on reading, updating and deleting data. SQL Server: Database Creation, Configuration, and Alteration by Joe Sack rounds out the acronym by exploring the creation and configuration of databases on SQL Server.
From here, you’ll move into more vertical topics. In Introduction to Dates and Times in SQL Server, Scott Hecht discusses the caveats and best practices around working with time and date data inside of SQL Server. Bob Beauchemin’s Using XML and XQuery Effectively with SQL Serverdemonstrates how to incorporate XML data into and extract XML data from SQL Server using T-SQL language features. Joe Sack then returns with SQL Server: Transact-SQL Common Table Expressions to elucidate one of the more powerful query language extensions available in SQL Server. In SQL Server 2012: Transact-SQL Error Handling Joe Sack returns again to address one of the most common areas of strife in SQL development.
Finally, the editor's playlist ends with two rather advanced courses. In Dave Green’s Unit Testing T-SQL Code with tSQLt, you’ll learn how to apply unit testing techniques that are common in procedural programming to your T-SQL code. Then, Scott Hecht returns to finish the path with a discussion of some more recent SQL language additions, including analytic functions and data pivoting in Advanced SQL Queries in Oracle and SQL Server.
While this marks the end of the “official” T-SQL editor's playlist, your learning is by no means complete. Relational database theory and related technology is a very deep topic, one in which you can readily lose yourself with abandon. For instance, you may now be interested in learning how to optimize the performance of your T-SQL queries, in which case you’ll want to check out these courses:
- SQL Server: Optimizing Ad Hoc Statement Performance
- SQL Server: Optimizing Stored Procedure Performance
- SQL Server: Common Query Tuning Problems and SOlutions – Part 1
- SQL Server: Common Query Tuning Problems and SOlutions – Part 2
- SQL Server: Query Plan Analysis
If SQL Server is of particular interest to you, I also recommend getting involved in the SQL Server community. The Professional Association of SQL Server (PASS) is the keystone to the SQL Server community, providing organization and funding for events and education related to SQL Server. Part of this effort takes the form of SQL Saturdays, which are local one-day events where you can get hands-on learning from SQL Server experts and community leaders.
