Skip to main content
Disciplines
Automotive Business IT + Informatics Construction + Real Estate Electrical Engineering + Electronics Energy + Sustainability Insurance + Risk Finance + Banking Management + Leadership Marketing + Sales Mechanical Engineering + Materials
Events
DE
EN
Books
Journals
Topic Page
Marketing
Start single access now
Access for companies
EXTENDED SEARCH
Log in
Top

2021 | Book

Getting Started Read chapter Read first chapter

Beginning T-SQL

A Step-by-Step Approach

Authors: Kathi Kellenberger, Lee Everest

Publisher: Apress

Part of: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Login to get access
insite
SEARCH

About this book

Get a performance-oriented introduction to the T-SQL language underlying the Microsoft SQL Server and Azure SQL database engines. This fourth edition is updated to include SQL Notebooks as well as up-to-date syntax and features for T-SQL on-premises and in the Azure cloud. Exercises and examples now include the WideWorldImporters database, the newest sample database from Microsoft for SQL Server. Also new in this edition is coverage of JSON from T-SQL, news about performance enhancements called Intelligent Query Processing, and an appendix on running SQL Server in a container on macOS or Linux.

Beginning T-SQL starts you on the path to mastering T-SQL with an emphasis on best practices. Using the sound coding techniques taught in this book will lead to excellent performance in the queries that you write in your daily work. Important techniques such as windowing functions are covered to help you write fast-executing queries that solve real business problems.The book begins with an introduction to databases, normalization, and to setting up your learning environment. You will learn about the tools you need to use such as SQL Server Management Studio, Azure Data Studio, and SQL Notebooks. Each subsequent chapter teaches an aspect of T-SQL, building on the skills learned in previous chapters. Exercises in most chapters provide an opportunity for the hands-on practice that leads to true learning and distinguishes the competent professional.

A stand-out feature in this book is that most chapters end with a Thinking About Performance section. These sections cover aspects of query performance relative to the content just presented, including the new Intelligent Query Processing features that make queries faster without changing code. They will help you avoid beginner mistakes by knowing about and thinking about performance from day 1.

What You Will Learn

Install a sandboxed SQL Server instance for learningUnderstand how relational databases are designedCreate objects such as tables and stored proceduresQuery a SQL Server table Filter and order the results of a queryQuery and work with specialized data types such as XML and JSONApply modern features such as window functionsChoose correct techniques so that your queries perform well

Who This Book Is For

Anyone who wants to learn T-SQL from the beginning or improve their T-SQL skills; those who need T-SQL as an additional skill; and those who write queries such as application developers, database administrators, business intelligence developers, and data scientists. The book is also helpful for anyone who must retrieve data from a SQL Server database.

Table of Contents

Frontmatter
Chapter 1. Getting Started
Abstract
T-SQL, also known as Transact-SQL, is Microsoft’s implementation of the Structured Query Language (SQL) for SQL Server. Most database professionals pronounce SQL like the word “sequel,” but you will occasionally hear someone say it as S-Q-L.
Kathi Kellenberger, Lee Everest
Chapter 2. Exploring Database Concepts
Abstract
This chapter will explain just what SQL Server is and what a database is and describe the objects that make up those databases. You will learn how data is stored in a database, and you’ll learn about objects, called indexes, that help SQL Server return the results of your queries quickly.
Kathi Kellenberger, Lee Everest
Chapter 3. Writing Simple SELECT Queries
Abstract
Chapter 1 had you prepare your computer by installing SQL Server and the sample databases. You learned how to get around in Azure Data Studio and a few tips to help make writing queries easier. In Chapter 2, you learned about databases, tables, and the other objects that make up a database.
Kathi Kellenberger, Lee Everest
Chapter 4. Using Built-in Functions and Expressions
Abstract
Now that you have the knowledge to write simple SELECT statements, it is time to explore some of the other features of T-SQL that allow you to manipulate how the data is displayed, filtered, or ordered. To create expressions in T-SQL, you use functions and operators along with literal values and columns. The reasons for using expressions in T-SQL code are many. For example, you may want to display only the year of a column of the DATETIME data type on a report, or you may need to calculate a discount based on the order quantity in an order-entry application. Any time the data must be displayed, filtered, or ordered in a way that is different from how it is stored, you can use expressions and functions to manipulate the results.
Kathi Kellenberger, Lee Everest
Chapter 5. Joining Tables
Abstract
Now that you know how to write simple queries using one table and how to use functions and expressions in queries, it is time to learn how to write queries involving two or more tables. In a properly designed relational database, a table contains data about one thing or entity. For example, an order-entry application will have a table storing customer information, a table containing data about orders, and a table containing order detail information about each item ordered. As was mentioned in Chapter 1, a primary key is the column (or columns) that uniquely identifies a row in a table. The order table has a column, called a foreign key, which refers to the primary key in a row in the customer table. The order detail table has a foreign key column that refers to the primary key in a row of the order table. By using joins, you can link these tables together within the query so you can display columns from each table in the same result set.
Kathi Kellenberger, Lee Everest
Chapter 6. Building on Subqueries, Common Table Expressions, and Unions
Abstract
The most common way to use more than one table in a query is by joining them, which you learned about in Chapter 5. This chapter will explain several other ways to combine tables in a query. Subqueries and common table expressions (CTEs) allow you to isolate the logic of complex queries. Union queries allow you to combine the results of two independent queries into one result set. The first thing you will learn in this chapter is how to substitute a query for a hard-coded IN list.
Kathi Kellenberger, Lee Everest
Chapter 7. Grouping and Summarizing Data
Abstract
So far, you have learned to write simple queries that include filtering and ordering. You can also work with expressions built with operators and functions. Chapters 5 and 6 taught you how to write queries with multiple tables so that the data makes sense in applications and reports. Now it’s time to learn about a special type of query, aggregate queries, used to group and summarize data. You may find that writing aggregate queries is more challenging than the other queries you have learned so far, but by taking a step-by-step approach, you will see that they are not difficult to write at all. Be sure to take the time to understand the examples and complete all the exercises before moving on to the next section.
Kathi Kellenberger, Lee Everest
Chapter 8. Discovering Windowing Functions
Abstract
The windowing functions, sometimes called window or windowed functions, are the most exciting features added to T-SQL over the past several versions. Starting with SQL Server 2005, the window functions, which have nothing to do with the Windows operating system, enable T-SQL developers to solve complex queries in new and innovative ways. Window functions perform calculations over a “window” or set of rows. They allow the developer to solve problems in easier and frequently better performing ways. This chapter will explain the ranking and window aggregate functions added with SQL Server 2005 and the many enhancements and new analytic functions that are part of SQL Server 2012.
Kathi Kellenberger, Lee Everest
Chapter 9. Advanced WHERE Clauses
Abstract
In Chapter 3, you learned how to write queries with SELECT, FROM, WHERE, and ORDER BY clauses. Chapter 3 covered a lot of options for filtering results with the WHERE clause; however, there is still more to learn. This chapter will discuss using the comparison operator LIKE and matching against a partial value or a pattern, full-text search, WHERE clauses with more than two predicates, and the PATINDEX function.
Kathi Kellenberger, Lee Everest
Chapter 10. Manipulating Data
Abstract
The data stored in most databases is not static. The application users are constantly adding data to tables as customers place orders, the company hires employees, and the accounts payable department writes checks. Automated processes periodically load new data into reporting databases, such as data warehouses, and into production systems. Users and processes also update existing rows or delete rows from tables.
Kathi Kellenberger, Lee Everest
Chapter 11. Managing Transactions
Abstract
In Chapter 10, you learned how to manipulate data using T-SQL statements. In this chapter, you will learn about a very important aspect of manipulating data: transactions. A transaction is a unit of work in SQL Server. Most of the time, a transaction is one statement that inserts, updates, or deletes data. It is possible, however, to define an explicit transaction that includes one or more statements. You can also include SELECT statements in a transaction. A transaction can be committed or rolled back as a unit.
Kathi Kellenberger, Lee Everest
Chapter 12. Understanding T-SQL Programming Logic
Abstract
Even though the primary purpose of T-SQL is to retrieve and manipulate data, like other programming languages, it also contains logic elements. Most of the time, you will write T-SQL statements that retrieve or update data, but you can also set up loops and write code with conditional flow. Often database administrators write scripts in T-SQL to perform maintenance tasks that require more than just retrieving or updating data. For example, you might need to write a script that checks the last backup date of all databases on the server or checks the free space of all the databases. Although most administrative tasks are beyond the scope of this book, you may find many uses in your environment for the techniques you will learn in this chapter.
Kathi Kellenberger, Lee Everest
Chapter 13. Implementing Logic in the Database
Abstract
So far, you have worked exclusively with tables by using Data Manipulation Language (DML) statements. You have learned to manipulate data by inserting new rows and updating or deleting existing rows. You can use many other objects in a SQL Server database to make your database applications more efficient and secure. You have also used control flow language. This chapter teaches you how to add restrictions to tables and to create other objects that help to enforce key business rules. In this chapter, you will learn about Data Definition Language (DDL) statements and constraints on tables, views, stored procedures, user-defined functions, and user-defined types. Because SQL Server provides so many choices for creating these objects, this chapter doesn’t attempt to explore every possible option. The chapter does, however, provide enough detail to teach you most of what you will encounter on your journey to becoming an expert T-SQL developer.
Kathi Kellenberger, Lee Everest
Chapter 14. Expanding on Data Type Concepts
Abstract
You have learned how to retrieve data from SQL Server tables in a number of ways: through simple queries, through joins, with functions, and more. You have learned to manipulate data, write scripts, and create database objects. Essentially, you have learned the T-SQL basics. Not only have you learned the mechanics of T-SQL, but you have also learned to think about the best way to solve a problem, not just the easy way.
Kathi Kellenberger, Lee Everest
Chapter 15. Working with XML and JSON
Abstract
Beginning with SQL Server 2005, Microsoft added the XML data type, the XQuery language, and several new functions for working with XML data in addition to the functionality found in SQL Server 2000. XML stands for Extensible Markup Language, and it looks a lot like HTML except that it generally contains data, while HTML is used to create web pages. Companies often use XML to exchange data between incompatible systems or with their vendors and customers.
Kathi Kellenberger, Lee Everest
Chapter 16. Writing Advanced Queries
Abstract
In this chapter, you will learn some advanced query techniques. For example, you will learn more about common table expressions (CTEs), how to write a pivot query, and more. As a beginning T-SQL developer, you may or may not need this information right away. This chapter doesn’t contain any exercises, but I encourage you to experiment and come up with your own examples for any of the features that interest you. Consider the information in this chapter as a head start in becoming an expert T-SQL developer.
Kathi Kellenberger, Lee Everest
Chapter 17. Where to Go Next?
Abstract
I hope you have enjoyed learning about T-SQL as much as I have enjoyed writing about it. Not everyone is cut out to be a T-SQL developer; it helps to really enjoy writing code. Programming is not something you can just learn and be done with it. You will continue to learn new techniques as long as you are programming T-SQL. The other thing you can count on is that Microsoft will continue to add new features to T-SQL giving you more to learn about. In fact, this book describes numerous T-SQL enhancements only found in SQL Server 2016 and later versions.
Kathi Kellenberger, Lee Everest
Backmatter
Metadata
Title
Beginning T-SQL
Authors
Kathi Kellenberger
Lee Everest
Copyright Year
2021
Publisher
Apress
Electronic ISBN
978-1-4842-6606-9
Print ISBN
978-1-4842-6605-2
DOI
https://doi.org/10.1007/978-1-4842-6606-9

Premium Partner

    Image Credits