深入学习T-SQL 2012编程指南

5星 · 超过95%的资源需积分: 9 175 浏览量更新于2024-07-24 收藏 11.39MB PDF 举报

"Pro T-SQL 2012 Programmer’s Guide" 是一本关于 SQL Server 的编程指南，旨在帮助读者深入理解并熟练掌握 T-SQL（Transact-SQL）语言，提升在 SQL Server 2012 环境中的编程能力。本书详细介绍了 T-SQL 的基础和高级特性，包括以下章节内容： 1. **基础篇** - 第1章“FoundationsofT-SQL”：这部分内容将阐述T-SQL的基本概念、语法结构以及数据查询的基础知识，包括SELECT语句、JOIN操作、聚合函数等。 2. **工具篇** - 第2章“ToolsoftheTrade”：讲解了用于开发和管理T-SQL代码的各种工具，可能包括SQL Server Management Studio (SSMS)、查询分析器等，以及如何高效使用这些工具。 3. **流程控制与表达式** - 第3章“ProceduralCodeandCASEExpressions”：讨论了流程控制结构（如IF-ELSE, WHILE循环）和CASE表达式的用法，使开发者能够编写更复杂的T-SQL脚本。 4. **用户自定义函数** - 第4章“User-DefinedFunctions”：涵盖了如何创建和使用用户自定义函数（UDF），包括标量函数、表值函数和聚合函数，以增强数据库的功能。 5. **存储过程** - 第5章“StoredProcedures”：详细介绍了存储过程的概念、创建、执行和管理，以及它们在数据库设计中的重要性。 6. **触发器** - 第6章“Triggers”：讲解了触发器的工作原理和应用，如何利用触发器实现数据的自动处理和验证。 7. **安全性与加密** - 第7章“Encryption”：介绍SQL Server的加密技术，包括如何保护数据的安全性和隐私。 8. **常见表表达式与窗口函数** - 第8章“CommonTableExpressionsandWindowingFunctions”：讨论了CTE（公共表表达式）和窗口函数，这两种高级查询技术在复杂数据分析和报表生成中的作用。 9. **数据类型与高级数据类型** - 第9章“DataTypesandAdvancedDataTypes”：详细介绍了SQL Server的各种数据类型，以及如何选择和使用它们。 10. **全文搜索** - 第10章“Full-TextSearch”：探讨了SQL Server的全文搜索功能，如何建立和优化全文索引，以及进行文本检索。 11. **XML支持** - 第11章“XML”：介绍了XML在SQL Server中的处理，包括XML数据类型、XML索引和XQuery操作。 12. **XQuery和XPath** - 第12章“XQueryandXPath”：讲解了如何使用XQuery语言来查询和操作XML数据，以及XPath的使用。 13. **目录视图和动态管理视图** - 第13章“CatalogViewsandDynamicManagementViews”：介绍了如何利用这些视图获取数据库元数据和性能信息。 14. **CLR集成编程** - 第14章“CLRIntegrationProgramming”：探讨了如何使用.NET框架中的Common Language Runtime（CLR）来创建SQL Server的自定义函数、存储过程和触发器。 15. **.NET客户端编程** - 第15章“.NETClientProgramming”：讲解了如何使用.NET语言（如C#或VB.NET）与SQL Server进行交互。 16. **数据服务** - 第16章“DataServices”：可能涵盖了SQL Server的数据服务，如Reporting Services、Integration Services和Analysis Services等。 17. **错误处理与动态SQL** - 第17章“ErrorHandlingandDynamicSQL”：讨论了如何在T-SQL中处理错误，以及动态SQL的使用和安全问题。 18. **性能调优** - 第18章“PerformanceTuning”：提供了性能优化的策略和技巧，包括查询优化、索引设计和资源监控。此外，书后还附有练习答案（Appendix A），以便读者检验学习成果。通过阅读本书，读者可以全面了解并精通SQL Server 2012中的T-SQL编程，从而更好地设计、开发和维护高效、安全的数据库应用程序。

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

• Consistency: Only data that is consistent with the rules set up in the database will be

stored. Data types and constraints can help enforce consistency within the database.

For instance, you cannot insert the name Meghan in an int column. Consistency

also applies when dealing with data updates. If two users update the same row of a

table at the same time, an inconsistency could occur if one update is only partially

complete when the second update begins. The concept of isolation, described in the

following bullet point, is designed to deal with this situation.

• Isolation: Multiple simultaneous updates to the same data should not interfere with

one another. SQL Server includes several locking mechanisms and isolation levels

to ensure that two users cannot modify the exact same data at the exact same time,

which could put the data in an inconsistent state. Isolation also prevents you from

even reading uncommitted data by default.

• Durability: Data that passes all the previous tests is committed to the database. The

concept of durability ensures that committed data is not lost. The transaction log and

data backup and recovery features help to ensure durability.

The transaction log is one of the main tools SQL Server uses to enforce the ACID concept when storing and

manipulating data.

Schemas

SQL Server 2012 supports database schemas, which are logical groupings by the owner of database objects. The

AdventureWorks2012 sample database, for instance, contains several schemas, such as HumanResources, Person,

and Production. These schemas are used to group tables, stored procedures, views, and user-defined functions

(UDFs) for management and security purposes.

Tip ■ When you create new database objects, like tables, and don’t specify a schema, they are automatically

created in the default schema. The default schema is normally dbo, but DBAs may assign different default

schemas to different users. Because of this, it’s always best to specify the schema name explicitly when creating

database objects.

Tables

SQL Server supports several types of objects that can be created within a database. SQL stores and manages data

in its primary data structures, tables. A table consists of rows and columns, with data stored at the intersections

of these rows and columns. As an example, the AdventureWorks HumanResources.Department table is shown in

Figure 1-2.

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

Figure 1-2. Representation of the HumanResources.Department Table

In the table, each row is associated with columns and each column has certain restrictions placed on its

content. These restrictions comprise the data domain. The data domain defines all the values a column can

contain. At the lowest level, the data domain is based on the data type of the column. For instance, a smallint

column can contain any integer values between −32,768 and +32,767.

The data domain of a column can be further constrained through the use of check constraints, triggers, and

foreign key constraints. Check constraints provide a means of automatically checking that the value of a column

is within a certain range or equal to a certain value whenever a row is inserted or updated. Triggers can provide

similar functionality to check constraints. Foreign key constraints allow you to declare a relationship between the

columns of one table and the columns of another table. You can use foreign key constraints to restrict the data

domain of a column to only include those values that appear in a designated column of another table.

reStrICtING the Data DOMaIN: a COMparISON

In this section, we have given a brief overview of three methods of constraining the data domain for a

column. Each method restricts the values that can be contained in the column. Here’s a quick comparison of

the three methods:

Foreign key constraints allow SQL Server to perform an automatic check against •

another table to ensure that the values in a given column exist in the referenced

table. If the value you are trying to update or insert in a table does not exist in

the referenced table, an error is raised. The foreign key constraint provides a

flexible means of altering the data domain, since adding or removing values from

the referenced table automatically changes the data domain for the referencing

table. Also, foreign key constraints offer an additional feature known as cascading

declarative referential integrity (DRI), which automatically updates or deletes rows

from a referencing table if an associated row is removed from the referenced table.

Check constraints provide a simple, efficient, and effective tool for ensuring that the values •

being inserted or updated in a column are within a given range or a member of a given set

of values. Check constraints, however, are not as flexible as foreign key constraints and

triggers since the data domain is normally defined using hard-coded constant values.

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

Triggers are stored procedures attached to insert, update, or delete events on a •

table. Triggers can also be set on changes to an object’s structure. Both DDL and

DML triggers provide a flexible solution for constraining data, but they may require

more maintenance than the other options since they are essentially a specialized

form of stored procedure. Unless they are extremely well designed, triggers have

the potential to be much less efficient than the other methods as well. Triggers to

constrain the data domain are generally avoided in modern databases in favor of

the other methods. The exception to this is when you are trying to enforce a foreign

key constraint across databases, since SQL Server doesn’t support cross-database

foreign key constraints.

Which method you use to constrain the data domain of your column(s) needs to be determined by your

project-specific requirements on a case-by-case basis.

Views

A view is like a virtual table—the data it exposes is not stored in the view object itself. Views are composed of

SQL queries that reference tables and other views, but they are referenced just like tables in queries. Views serve

two major purposes in SQL Server: they can be used to hide the complexity of queries, and they can be used as a

security device to limit the rows and columns of a table that a user can query. Views are expanded, meaning that

their logic is incorporated into the execution plan for queries when you use them in queries and DML statements.

SQL Server may not be able to use indexes on the base tables when the view is expanded, resulting in less-than-

optimal performance when querying views in some situations.

To overcome the query performance issues with views, SQL Server also has the ability to create a special type

of view known as an indexed view. An indexed view is a view that SQL Server persists to the database like a table.

When you create an indexed view, SQL Server allocates storage for it and allows you to query it like any other

table. There are, however, restrictions on inserting, updating, and deleting from an indexed view. For instance,

you cannot perform data modifications on an indexed view if more than one of the view’s base tables will be

affected. You also cannot perform data modifications on an indexed view if the view contains aggregate functions

or a DISTINCT clause.

You can also create indexes on an indexed view to improve query performance. The downside to an indexed

view is increased overhead when you modify data in the view’s base tables, since the view must be updated as well.

Indexes

Indexes are SQL Server’s mechanisms for optimizing access to data. SQL Server 2012 supports several types of

indexes, including the following:

• Clustered index: A clustered index is limited to one per table. This type of index defines

the ordering of the rows in the table. A clustered index is physically implemented using a

b-tree structure with the data stored in the leaf levels of the tree. Clustered indexes order

the data in a table in much the same way that a phone book is ordered by last name.

A table with a clustered index is referred to as a clustered table, while a table with no

clustered index is referred to as a heap.

• Nonclustered index: A nonclustered index is also a b-tree index managed by SQL Server.

In a nonclustered index, index rows are included in the leaf levels of the b-tree. Because

of this, nonclustered indexes have no effect on the ordering of rows in a table. The index

rows in the leaf levels of a nonclustered index consist of the following:

A nonclustered key value•

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

A row locator, which is the clustered index key on a table with a clustered index, or a •

SQL-generated row ID for a heap

Nonkey columns, which are added via the • INCLUDE clause of the CREATE INDEX statement

• Columnstore index: A columnstore index is a special index used for very large tables

(>100 million rows) and is mostly applicable to large data warehouse implementations.

A columnstore index creates an index on the column as opposed to the row and although

they allow for efficient and extremely fast retrieval of large data sets. Tables with

columnstore indexes are required to be readonly.

A nonclustered index is analogous to an index in the back of a book.

• XML index: SQL Server supports special indexes designed to help efficiently query XML

data. See Chapter 10 for more information.

• Spatial index: A spatial index is an interesting new indexing structure to support efficient

querying of the new geometry and geography data types. See Chapter 2 for more

information.

• Full-text index: A full-text index (FTI) is a special index designed to efficiently perform

full-text searches of data and documents.

You can also include nonkey columns in your nonclustered indexes with the INCLUDE clause of the CREATE

INDEX statement. The included columns give you the ability to work around SQL Server’s index size limitations.

Stored Procedures

SQL Server supports the installation of server-side T-SQL code modules via stored procedures (SPs). It’s very

common to use SPs as a sort of intermediate layer or custom server-side application programming interface (API)

that sits between user applications and tables in the database. Stored procedures that are specifically designed to

perform queries and DML statements against the tables in a database are commonly referred to as CRUD (create,

read, update, delete) procedures.

User-Defined Functions

User-defined functions (UDFs) can perform queries and calculations, and return either scalar values or

tabular result sets. UDFs have certain restrictions placed on them. For instance, they cannot utilize certain

nondeterministic system functions, nor can they perform DML or DDL statements, so they cannot make

modifications to the database structure or content. They cannot perform dynamic SQL queries or change the

state of the database (i.e., cause side effects).

SQL CLR Assemblies

SQL Server 2012 supports access to Microsoft .NET functionality via the SQL Common Language Runtime

(SQL CLR). To access this functionality, you must register compiled .NET SQL CLR assemblies with the server.

The assembly exposes its functionality through class methods, which can be accessed via SQL CLR functions,

procedures, triggers, user-defined types, and user-defined aggregates. SQL CLR assemblies replace the

deprecated SQL Server extended stored procedure (XP) functionality available in prior releases.

Tip ■ Avoid using XPs on SQL Server 2012. The same functionality provided by XPs can be provided by SQL CLR

code. The SQL CLR model is more robust and secure than the XP model. Also keep in mind that the XP library is

deprecated and XP functionality may be completely removed in a future version of SQL Server.

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

Elements of Style

Now that we’ve given a broad overview of the basics of SQL Server, we’ll take a look at some recommended

development tips to help with code maintenance. Selecting a particular style and using it consistently helps

immensely with both debugging and future maintenance. The following sections contain some general

recommendations to make your T-SQL code easy to read, debug, and maintain.

Whitespace

SQL Server ignores extra whitespace between keywords and identifiers in SQL queries and statements.

A single statement or query may include extra spaces and tab characters, and can even extend across several

lines. You can use this knowledge to great advantage. Consider Listing 1-3, which is adapted from the

HumanResources.vEmployee view in the AdventureWorks2012 database.

Listing 1-3. The HumanResources.vEmployee View from the AdventureWorks2012 Database

SELECT e.BusinessEntityID, p.Title, p.FirstName, p.MiddleName, p.LastName, p.Suffix, e.JobTitle,

pp.PhoneNumber, pnt.Name AS PhoneNumberType, ea.EmailAddress,

p.EmailPromotion, a.AddressLine1, a.AddressLine2, a.City, sp.Name AS StateProvinceName,

a.PostalCode, cr.Name AS CountryRegionName, p.AdditionalContactInfo

FROM HumanResources.Employee AS e INNER JOIN Person.Person AS p ON p.BusinessEntityID =

e.BusinessEntityID INNER JOIN Person.BusinessEntityAddress AS bea ON bea.BusinessEntityID =

e.BusinessEntityID INNER JOIN Person.Address AS a ON a.AddressID = bea.AddressID INNER JOIN

Person.StateProvince AS sp ON sp.StateProvinceID = a.StateProvinceID INNER JOIN

Person.CountryRegion AS cr ON cr.CountryRegionCode = sp.CountryRegionCode LEFT OUTER JOIN

Person.PersonPhone AS pp ON pp.BusinessEntityID = p.BusinessEntityID LEFT OUTER JOIN

Person.PhoneNumberType AS pnt ON pp.PhoneNumberTypeID = pnt.PhoneNumberTypeID LEFT OUTER JOIN

Person.EmailAddress AS ea ON p.BusinessEntityID = ea.BusinessEntityID

This query will run and return the correct result, but it’s very hard to read. You can use whitespace and table

aliases to generate a version that is much easier on the eyes, as demonstrated in Listing 1-4.

Listing 1-4. The HumanResources.vEmployee View Reformatted for Readability

SELECT

e.BusinessEntityID,

p.Title,

p.FirstName,

p.MiddleName,

p.LastName,

p.Suffix,

e.JobTitle,

pp.PhoneNumber,

pnt.Name AS PhoneNumberType,

ea.EmailAddress,

p.EmailPromotion,

a.AddressLine1,

a.AddressLine2,

a.City,

剩余678页未读，继续阅读

rettychen

粉丝: 1
资源: 21

深入学习T-SQL 2012编程指南

精通T-SQL 2012：开发者指南

Pro T-SQL程序员指南（第4版）：权威详解

SQL Server 2008 T-SQL编程权威指南

Pro T-SQL 2012 Programmer's Guide

Pro T-SQL 2008 Programmer’s Guide

[T-SQL] T-SQL 2012 程序员高级开发技术指南 (英文版)

pro.t-sql.2005.programmers.guide

[T-SQL] T-SQL 2008 程序员高级开发技术指南 (英文版)

Pro*COBOL Precompiler Programmer’s Guide Release 8.1.6

Pro C/C++ Precompiler Programmer's Guide

最新资源