Beginning T-SQL 2012 | springerprofessional.de

Springer Professional

Top

2012 | Book

Read chapter Read first chapter

Beginning T-SQL 2012

Authors: Scott Shaw, Kathi Kellenberger

Publisher: Apress

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

Login to get access

About this book

Beginning T-SQL 2012 is the first step toward learning the T-SQL language that underlies Microsoft’s SQL Server database engine. T-SQL is essential in writing SQL statements to get data into and out of a database. T-SQL is the foundation for business logic embedded in the database in the form of stored procedures and functions. Beginning T-SQL 2012 starts you on the path to mastering T-SQL, with an emphasis on best practices and sound coding techniques.

Beginning T-SQL 2012 begins with an introduction to databases, normalization, and to SQL Server Management Studio. Each subsequent chapter teaches an aspect of T-SQL, building on the skills learned in previous chapters. Exercises in each chapter give readers an opportunity for the hands-on practice that leads to true learning and distinguishes the competent professional.

Imparts best practices for writing T-SQL Helps you avoid common errors Shows how to write scalable code for good performance

Advertisement

Table of Contents

Frontmatter

Chapter 1. Getting Started

Abstract

If you are reading this book, you probably know about T-SQL. T-SQL, also known as Transact-SQL, is Microsoft’s implementation of the Structured Query Language (SQL) for SQL Server. T-SQL is the language that is most often used to extract or modify data stored in a SQL Server database, regardless of which application or tool you use. SQL Server 2012 T-SQL is based on standards created by the American National Standards Institute (ANSI), but Microsoft has added several functionality enhancements. You will find that T-SQL is a very versatile and powerful programming language.

Scott Shaw, Kathi Kellenberger

Chapter 2. Writing Simple SELECT Queries

Abstract

Chapter 1 had you preparing your computer by installing SQL Server 2012 and the AdventureWorks2012 sample database. You learned how to get around in SQL Server Management Studio and a few tips to help make writing queries easier.

Scott Shaw, Kathi Kellenberger

Chapter 3. Using 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 it.

Scott Shaw, Kathi Kellenberger

Chapter 4. Querying Multiple 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 detail information about each item ordered. The order table has a column, called a foreign key, that points to a row in the customer table. The detail table has a foreign key column that points to the order table. By using joins, you can link these tables together so that you can display columns from each table in the same result set.

Scott Shaw, Kathi Kellenberger

Chapter 5. 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. The previous chapter 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.

Scott Shaw, Kathi Kellenberger

Chapter 6. Manipulating Data

Abstract

The data stored in most databases is not static. The application users constantly add data to tables as customers place orders, the company hires employees, and the payroll 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.

Scott Shaw, Kathi Kellenberger

Chapter 7. 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 learn in this chapter.

Scott Shaw, Kathi Kellenberger

Chapter 8. Working with XML

Abstract

Beginning with SQL Server 2005, Microsoft added the XML data type, the XQuery language, and several new functions for working with XML data. XML stands for Extensible Markup Language, and it looks a lot like HTML except that it generally contains data. Companies often use XML to exchange data between incompatible systems or with their vendors and customers. SQL Server also extensively uses XML data to store query plans.

Scott Shaw, Kathi Kellenberger

Chapter 9. Moving Logic to 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. 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 learn about constraints on tables, views, stored procedures, user-defined functions, and user-defined types. You will also learn about triggers, special objects that fire when data is modified. 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.

Scott Shaw, Kathi Kellenberger

Chapter 10. Working with Data Types

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 these skills, but you have learned to think about the best way to solve a problem, not just the easy way.

Scott Shaw, Kathi Kellenberger

Chapter 11. Writing Advanced Queries

Abstract

In Chapter 10, you learned about some useful data types in SQL Server 2012. In this chapter, you will learn about some of the functions and advanced queries available with these most recent releases. For example, you will learn more about common table expressions (CTEs), how to write a pivot query, how to use the new ranking functions, 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 you are interested in. Consider the information in this chapter as a head start in becoming an expert T-SQL developer.

Scott Shaw, Kathi Kellenberger

Chapter 12. 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 SQL Server, including new T-SQL features, giving you more to learn about. In fact, this book describes numerous, completely new T-SQL enhancements only found in SQL Server 2012.I can guarantee the next SQL Server release will have even more.

Scott Shaw, Kathi Kellenberger

Backmatter

Title: Beginning T-SQL 2012
Authors: Scott Shaw
Kathi Kellenberger
Copyright Year: 2012
Publisher: Apress
Electronic ISBN: 978-1-4302-3705-1
Print ISBN: 978-1-4302-3704-4
DOI: https://doi.org/10.1007/978-1-4302-3705-1

Premium Partner