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

2012

需积分: 0 144 浏览量更新于2024-07-15 收藏 18.95MB PDF 举报

"Pro T-SQL 2012 Programmer’s Guide, 3rd Edition" 是一本面向开发者的专业指南，旨在全面介绍SQL Server 2012中的Transact-SQL（T-SQL）语言的强大功能。书中涵盖了新特性和现有特性，通过专家引领的方式深入解析T-SQL的功能。书中包含可运行的示例代码和可下载的源代码，确保技术内容的准确无误。本书的主要内容包括： 1. **基础篇**：第1章介绍T-SQL的基础知识，帮助读者构建对T-SQL的基本理解。 2. **工具篇**：第2章讲解用于编写和调试T-SQL的工具和技术，使开发者能够更高效地工作。 3. **过程编程与CASE表达式**：第3章探讨如何编写过程化代码以及使用CASE表达式进行条件判断和逻辑处理。 4. **用户定义函数**：第4章详细介绍用户定义函数（UDF），包括其创建、使用和优化。 5. **存储过程**：第5章深入研究存储过程，讨论它们在数据库管理中的作用和使用场景。 6. **触发器**：第6章讲解触发器的使用，包括行级和语句级触发器，以及最佳实践和潜在陷阱。 7. **加密**：第7章讨论SQL Server 2012中的数据加密技术，以保护敏感信息的安全。 8. **常见表表达式和窗口函数**：第8章介绍CTE和窗口函数，用于处理复杂的数据聚合和排序问题。 9. **数据类型和高级数据类型**：第9章深入探讨各种数据类型，包括特殊类型的处理和转换。 10. **全文搜索**：第10章讲解如何使用SQL Server的全文索引和搜索功能，提高文本数据的检索效率。 11. **XML**：第11章介绍XML在T-SQL中的使用，包括XML数据类型、XML方法和XML索引。 12. **XQuery和XPath**：第12章教授如何使用XQuery和XPath对XML数据进行查询和操作。 13. **目录视图和动态管理视图**：第13章揭示这些视图如何提供对数据库内部状态的洞察，以进行诊断和性能分析。 14. **CLR集成编程**：第14章探讨如何使用CLR（公共语言运行时）将.NET代码与SQL Server集成。 15. **.NET客户端编程**：第15章介绍如何使用.NET Framework与SQL Server进行交互。 16. **数据服务**：第16章涉及SQL Server提供的数据服务，如Service Broker和StreamInsight。 17. **错误处理和动态SQL**：第17章讨论错误处理策略和动态SQL的使用，以增强代码的健壮性。 18. **性能调优**：第18章提供性能优化的技巧和最佳实践，以提升T-SQL查询的执行效率。此外，书后还附有答案的练习和参考附录，以便读者巩固所学知识。这本书是针对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,

www.allitebooks.com

剩余678页未读，继续阅读

beapkapchy

粉丝: 0
资源: 5

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

Pro T-SQL 2012 Programmer's Guide

VBScript Programmer's Reference, 3rd Edition.pdf

Pro T-SQL 2008 Programmer’s Guide

Computer Systems A Programmer’s Perspective(3rd).pdf

JDK9-JSE-Java Scripting Programmer's Guide-21.pdf

pro.t-sql.2005.programmers.guide

Computer Systems A Programmers Perspective 3rd Edition.pdf

Foundations-for-Analytics-with-Python-From-non-programmer-to-hacker.pdf.pdf

Apress.A.Programmer's.Guide.to.C#.5.0.4th.Edition

Foundations of Python Network Programming, 3rd Edition.pdf 英文原版. Python网络编程基础

最新资源