精通T-SQL编程指南：面向SQL Server 2014的数据库开发

T-SQL

需积分: 5 94 浏览量更新于2024-07-18 收藏 24.11MB PDF 举报

身份认证购VIP最低享 7 折!

领优惠券(最高得80元）

"《Pro T-SQL Programmer's Guide》是一本专为SQL Server开发者设计的指南，特别是那些需要将代码从旧版本迁移到SQL Server 2014的开发者，以及希望充分利用2014版数据库开发功能的读者。本书假设读者已经具备SQL的基础知识，最好是T-SQL在SQL Server 2005或更高版本的经验，因为书中的大多数示例都是用T-SQL编写的。" 在本书中，作者深入探讨了T-SQL（Transact-SQL）这一强大的SQL Server编程语言，旨在提升读者的数据库开发技能。以下是书中主要内容的概览： 1. **基础篇（Chapter1: Foundations of T-SQL）** - 在这一章，作者涵盖了T-SQL的基本概念，包括数据类型、变量声明、控制流语句（如IF-ELSE, WHILE循环）以及查询数据的基本语法，如SELECT语句。读者将学习如何创建、修改和删除数据库对象，如表和视图，以及如何处理SQL Server中的数据。 2. **工具篇（Chapter2: Tools of the Trade）** - 这一章讨论了用于开发和调试T-SQL代码的各种工具和技巧。可能包括SQL Server Management Studio（SSMS）的使用，查询分析，性能调优工具，以及如何编写高效的查询。读者将学习如何有效地利用这些工具来提高工作效率。 3. **过程化代码（Chapter3: Procedural Code）** - 本章深入讲解存储过程和触发器的使用，这是T-SQL中重要的过程化编程元素。读者将学习如何编写、执行和管理存储过程，以及理解触发器的工作原理和在数据库事务中的作用。 4. **用户自定义函数（Chapter4: User-Defined Functions）** - 在这一部分，读者将学习如何创建和使用各种类型的用户自定义函数（UDFs），包括标量值函数和表值函数。这将帮助他们扩展SQL Server的功能，以满足特定的业务需求。 5. **其他章节** - 尽管未提供详细内容，但可以预期后续章节将涵盖更高级的主题，如错误处理、事务管理、并行性、性能优化、动态SQL、高级查询技术（如窗口函数和递归查询）以及与XML和JSON的集成等。通过这本书，读者不仅可以深化对T-SQL的理解，还能掌握在SQL Server 2014中进行高效、可靠和高性能数据库开发的关键技能。无论你是新手还是有经验的开发者，都能从中受益，提升你的T-SQL编程能力。

资源详情

资源推荐

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

the aDVeNtUreWOrKS2014 CID teSt

SQL folks, and IT professionals in general, love their acronyms. A common acronym in the SQL world

is ACID, which stands for “atomicity, consistency, isolation, durability.” These four words form a set of

properties that database systems should implement to guarantee reliability of data storage, processing,

and manipulation:

• Atomicity : All data changes should be transactional in nature. That is, data changes

should follow an all-or-nothing pattern. The classic example is a double-entry

bookkeeping system in which every debit has an associated credit. Recording a

debit-and-credit double entry in the database is considered one transaction, or a single

unit of work. You can’t record a debit without recording its associated credit, and vice

versa. Atomicity ensures that either the entire transaction is performed or none of it is.

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

Data types and constraints can help enforce consistency in the database. For instance,

you can’t insert the name Meghan in an integer 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 can’t 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 isn’t 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 2014 supports database schemas, which are logical groupings by the owner of database objects.

The AdventureWorks2014 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’re 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.

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

reStrICtING the Data DOMaIN: a COMparISON

This section has 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’re trying to update or insert in a table doesn’t exist in the referenced table,

an error is raised and any changes are rolled back. The foreign key constraint provides a

flexible means of altering the data domain, because adding values to or removing them

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(s) are within a given range or a member

of a given set of values. Check constraints, however, aren’t as flexible as foreign key

constraints and triggers because the data domain is normally defined using hard-coded

constant values or logical expressions.

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

view. Triggers can be set on DML or DDL events. Both DML and DDL triggers provide a

flexible solution for constraining data, but they may require more maintenance than the

other options because they’re essentially a specialized form of stored procedure. Unless

they’re extremely well designed, triggers have the potential to be much less efficient

than other methods of constraining data. Generally triggers are avoided in modern

databases in favor of more efficient methods of constraining data. The exception to this

is when you’re trying to enforce a foreign key constraint across databases, because

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 isn’t stored in the view object itself. Views are composed of

SQL queries that reference tables and other views, but they’re 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 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 into, updating, and deleting from an

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

indexed view. For instance, you can’t perform data modifications on an indexed view if more than one of the

view’s base tables will be affected. You also can’t 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, because the view must

be updated as well.

Indexes

Indexes are SQL Server’s mechanisms for optimizing access to data. SQL Server 2014 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, whereas 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•

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 allows for efficient and extremely fast retrieval of large data sets. Prior

to SQL Server 2014, tables with columnstore indexes were required to be read-

only. In SQL Server 2014, columnstore indexes are now updateable. This feature is

discussed further in Chapter 6.

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

XML data. See Chapter 11 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.

CHAPTER 1 ■ FOUNDATIONS OF T-SQL

• Memory-optimized index: SQL Server 2014 introduced In-Memory tables that bring with

them new index types. These types of indexes only exist in memory and must be created

with the initial table creation. These index types are covered at length in Chapter 6:

• Nonclustered hash index: This type of index is most efficient in scenarios where

the query will return values for a specific value criteria. For example, SELECT *

FROM <Table> WHERE <Column> = @<ColumnValue>.

• Memory-optimized nonclustered index: This type of index supports the same

functions as a hash index, in addition to seek operations and sort ordering.

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 can’t use certain

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

modifications to the database structure or content. They can’t perform dynamic SQL queries or change the

state of the database (cause side effects).

SQL CLR Assemblies

SQL Server 2014 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 extended stored procedures (XPs) on SQL Server 2014. 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.

Elements of Style

Now that you’ve had a broad overview of the basics of SQL Server, let’s 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.

剩余726页未读，继续阅读

shizidushu

粉丝: 394
资源: 2

精通T-SQL编程指南：面向SQL Server 2014的数据库开发

gd-link programmer

gd-link programmer 4.6.0

progppcnexus - flash programmer

gd-link_programmer

cx-programmer升级包

cx-programmer 9.4

cx-programmer v9.7安装包

cx-programmer9.7安装包

ql-programmer_data

cx-programmer 9.74

win32 programmer's reference guide big

mcu-isp-programmer

欧姆龙cx-programmer下载

ERROR 1045 (28000): Access denied for user 'abstract-programmer'@'localhost' (using password: YES)

vxworks 5.4 programmer's guide

nuvoton_isp-icp_programmer

flash-programmer-2软件下载

programmer's guide to java se 8 oracle certified associate

openhaptics_toolkit_programmersguide3.5

armv7 programmer guide

最新资源