nach oben

2009 | Buch

Beginning T-SQL 2008

verfasst von: Kathi Kellenberger

Verlag: Apress

Enthalten in: Springer Professional "Wirtschaft+Technik" , Springer Professional "Technik" , Springer Professional "Wirtschaft"

Einloggen, um Zugang zu erhalten

Über dieses Buch

Even if you have little or no knowledge of T-SQL, Beginning T-SQL 2008 will bring you up to intermediate level and teach you best practices along the way. You'll learn how to write code that will help you to achieve the best-performing applications possible.

You'll find an introduction to databases, normalization, and SQL Server Management Studio. You'll understand how data is stored in a database and learn how to use at least one of the available tools to get to that data.

Each subsequent chapter teaches an aspect of T-SQL, building on the skills learned in previous chapters. Exercises are included in each chapter because the only way to learn T-SQL is to write some code.

This book will do more than just give the syntax and examples. It will teach you techniques to help you avoid common errors and create robust and well-performing code.

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

Inhaltsverzeichnis

Frontmatter

Chapter 1. Getting Started

Abstract

If you are reading this book, you probably know what T-SQL is. 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 that is stored in a SQL Server database, regardless of the application or tool used. SQL Server 2008 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.

Kathi Kellenberger

Chapter 2. Writing Simple SELECT Queries

Abstract

In Chapter 1 you prepared prepared your computeryour computer by installing SQL Server Express and the AdventureWorks2008 sample databases. You learned how to get around in SQL Server Management Studio and learned a few other tips to help make writing queries easier.

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.

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.

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 is 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 realize 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

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.

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 write a script that 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.

Kathi Kellenberger

Chapter 8. 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 add restrictions to tables and to create these other objects by using In this chapter, you will Data Definition Language (DDL) statements. 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 options for creating these objects, this chapter does not explore every possible one. The chapter does, however, provide enough detail to teach you most of what you will encounter as you become an expert T-SQL developer.

Kathi Kellenberger

Chapter 9. Working with New 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.

Kathi Kellenberger

Chapter 10. Writing Advanced Queries

Abstract

In Chapter 9 you learned about some exciting new data types that Microsoft introduced with SQL Server 2005 and 2008. In this chapter, you will learn about some of the new 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 does not 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.

Kathi Kellenberger

Chapter 11. 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. At the time of this writing, Microsoft has just announced SQL Server 2008 R2 due out in 2010.

Kathi Kellenberger

Backmatter

Titel: Beginning T-SQL 2008
verfasst von: Kathi Kellenberger
Verlag: Apress
Electronic ISBN: 978-1-4302-2462-4
Print ISBN: 978-1-4302-2461-7
DOI: https://doi.org/10.1007/978-1-4302-2462-4