SQLServer2012高级技术探索

需积分: 10 161 浏览量更新于2024-07-24 2 收藏 26.97MB PDF 举报

"SQLServer2012进阶版，这是一本高级教程，涵盖了SQLServer中的各种高级应用，包括T-SQL、用户定义函数、存储过程、触发器、加密、全文搜索、XML、错误处理和性能调优等主题。" SQLServer 2012进阶版教程深入探讨了数据库管理和开发的各个方面，适合已经具备一定基础的SQLServer用户进一步提升技能。以下是对各章节内容的详细说明： 1. **基础T-SQL**：这一章将引导读者深入理解T-SQL语言的基础，包括查询语句（SELECT）、数据操作（INSERT、UPDATE、DELETE）以及数据定义和控制流语句。 2. **工具箱**：介绍了用于管理SQLServer的各种工具，如SQL Server Management Studio (SSMS)、查询分析器和Transact-SQL调试器等。 3. **过程化代码与CASE表达式**：讲解如何编写复杂的流程控制语句（如IF-ELSE、WHILE）以及使用CASE表达式进行条件判断。 4. **用户定义函数**：涵盖创建和使用用户自定义函数（UDF），包括 Scalar UDF 和 Table-valued UDF，以增强SQLServer的功能。 5. **存储过程**：讨论存储过程的创建、执行和优化，以及如何利用它们来封装和重用代码。 6. **触发器**：解释触发器的工作原理，包括INSERT、UPDATE和DELETE触发器，以及如何避免潜在的副作用。 7. **加密**：介绍SQLServer的加密机制，包括透明数据加密（TDE）、列级加密和证书/密钥管理。 8. **公共表表达式和窗口函数**：深入学习CTE（公共表表达式）和窗口函数，以实现更复杂的数据分析和报表生成。 9. **数据类型和高级数据类型**：详细讨论SQLServer支持的各种数据类型，包括日期时间、数值、文本和二进制数据类型的使用。 10. **全文搜索**：讲解如何在SQLServer中实施全文索引和搜索，以提高文本数据的查找效率。 11. **XML**：涵盖XML的存储、查询和处理，以及XML数据类型和XQuery。 12. **XQuery和XPath**：介绍如何使用XQuery语言和XPath表达式来查询和操作XML文档。 13. **目录视图和动态管理视图**：探讨这些视图的用途，帮助监控和诊断SQLServer实例的运行状态。 14. **CLR集成编程**：介绍如何使用.NET Framework的组件与SQLServer进行交互，实现自定义函数、存储过程和触发器。 15. **.NET客户端编程**：讲解如何通过.NET应用程序与SQLServer进行通信，包括ADO.NET和Entity Framework。 16. **数据服务**：可能涉及到SQLServer Reporting Services、Integration Services和Analysis Services等，提供数据的报表、ETL和分析解决方案。 17. **错误处理和动态SQL**：讨论如何在T-SQL中处理错误，并安全地使用动态SQL来防止SQL注入攻击。 18. **性能调优**：分享性能优化技巧，包括查询优化、索引策略、资源监控和SQL Server Profiler的使用。此书还包含了练习答案的附录，使读者能够验证和巩固所学知识。通过这本教程，读者可以掌握SQLServer 2012的高级特性，提升数据库管理和开发的专业能力。

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页未读，继续阅读

HelloWorld-WaitForMe

粉丝: 0
资源: 6

SQLServer2012高级技术探索

SQL进阶（完整总结版||ppt）

SQLServer2012

SQL Server2012

SQL Server 2012

SQL Server 2000菜鸟进阶

SQLServer调优系列进阶

SQL server2012教学课件

Beginning Microsoft SQL Server 2012 Programming

vs2012+sql server 2012的MFC数据库课程设计.zip

T-SQL性能调优秘笈-基于SQL Server 2012窗口函数

最新资源