SQL Server 2012 高性能T-SQL窗口函数实战

4星 · 超过85%的资源需积分: 50 66 浏览量更新于2024-07-28 1 收藏 16.96MB PDF 举报

"《Microsoft SQL Server 2012》英文版电子书是关于数据库管理和优化的专业书籍，由Itzik Ben-Gan撰写，由O'Reilly Media, Inc.出版。本书专注于利用窗口函数实现SQL Server 2012中的高性能T-SQL查询。" 在SQL Server 2012中，窗口函数是一项强大的功能，它允许开发人员在单个查询中进行复杂的分析操作，而无需使用子查询或自连接。这些函数在处理时间序列数据、排名、分组计算以及计算累积值等方面非常有用。Itzik Ben-Gan，作为SQL Server领域的知名专家，深入探讨了如何有效利用这些功能来提升查询性能。本书的主要内容可能包括以下几个方面： 1. **窗口函数基础**：介绍窗口函数的基本概念，如RANK(), ROW_NUMBER()和DENSE_RANK()，它们用于对行进行排序和分配唯一的序号。 2. **LEAD()和LAG()函数**：这两个函数可以向前或向后查看相邻行的数据，对于处理滑动窗口计算和预测性分析非常有用。 3. **OVER()子句**：OVER()子句是窗口函数的核心，它定义了计算的“窗口”，可以基于行集进行计算，而不只是当前行。 4. **PARTITION BY和ORDER BY**：在窗口函数中，PARTITION BY用于将数据集划分为多个分区，而ORDER BY则控制每个分区内的行顺序。 5. **累计和移动聚合**：如SUM()和AVG()等聚合函数的窗口版本，可以计算累计总和或平均值。 6. **高级窗口功能应用**：可能涵盖了窗口函数在复杂查询、多表联接、性能优化等实际场景中的应用。 7. **性能调优策略**：针对使用窗口函数的查询，书中可能会提供性能优化建议，如索引策略、查询计划优化等。 8. **案例研究和实战示例**：通过具体的实例，读者可以学习如何在实际工作中应用所学知识。《Microsoft SQL Server 2012》英文版电子书旨在帮助数据库管理员、开发人员和数据分析师充分利用SQL Server 2012的窗口函数功能，提高查询效率，简化复杂的数据处理任务。无论你是SQL新手还是经验丰富的专业人士，这本书都能为你提供宝贵的学习资源和实践经验。

xiv

Introduction

Chapter 3, “Ordered Set Functions,” describes the support standard SQL has for or-

dered set functions, including hypothetical set functions, inverse distribution functions,

and others. The chapter also explains how to achieve similar calculations in SQL Server.

Chapter 4, “Optimization of Window Functions,” covers in detail the optimization of

window functions in SQL Server 2012. It provides indexing guidelines for optimal per-

formance, explains how parallelism is handled and how to improve it, discusses the new

Window Spool iterator, and more.

Chapter 5, “T-SQL Solutions Using Window Functions,” covers practical uses of win-

dow functions to address common business tasks.

System Requirements

Window functions are part of the core database engine of Microsoft SQL Server

2012; hence, all editions of the product support this feature. To run the code samples

in this book, you need access to an instance of the SQL Server 2012 database en-

gine (any edition), and you need to have the sample database installed. If you don’t

have access to an existing instance, Microsoft provides trial versions. You can nd

details at: http://www.microsoft.com/sql. For hardware and software requirements,

please consult SQL Server Books Online at: http://msdn.microsoft.com/en-us/library/

ms143506(v=sql.110).aspx.

Code Samples

This book features a companion website that makes available to you all the code used

in the book, sample data, the errata, additional resources, and more, at the following

page:

http://www.insidetsql.com

In this website, go to the Books section and select the main page for the book in

question. The book’s page has a link to download a compressed le with the book’s

source code, including a le called TSQL2012.sql that creates and populates the book’s

sample database, TSQL2012.

Introduction xv

Acknowledgments

A number of people contributed to making this book a reality, whether directly or indi-

rectly, and deserve thanks and recognition.

To Lilach, for giving reason to everything I do, for tolerating me, and for helping

review the text.

To my parents, Mila and Gabi, and to my siblings, Mickey and Ina, for the constant

support and for accepting the fact that I’m away.

To members of the Microsoft SQL Server development team: Tobias Ternström,

Lubor Kollar, Umachandar Jayachandran, Marc Friedman, Milan Stojic, and I’m sure

many others. I know it wasn’t a trivial effort to add support for window functions in SQL

Server. Thanks for the great effort, and thanks for all the time you spent meeting with

me and responding to my emails, addressing my questions, and answering my requests

for clarication.

To the editorial team at O’Reilly and MSPress. Ken Jones, you spent the most Itzik

hours of all, and it’s a real pleasure working with you. Also thanks to Ben Ryan, Kristen

Borg, Curtis Philips, and Roger LeBlanc.

To Adam Machanic. Thanks for agreeing to be the technical editor of the book.

There aren’t many people who understand SQL Server development as well as you do.

You were the natural choice for me to ll this role for this book.

To “Q2,” “Q3,” and “Q4.” It’s great to be able to share ideas with people who under-

stand SQL as well as you do, and are such good friends and take life lightly. I feel that

I can share everything with you without worrying about any boundaries or conse-

quences. Thanks for your early review of the text.

To SolidQ, my company for the last decade. It’s gratifying to be part of such a great

company that evolved to what it is today. The members of this company are much

more than colleagues to me; they are partners, friends, and family. Thanks to Fernando

G. Guerrero, Douglas McDowell, Herbert Albert, Dejan Sarka, Gianluca Hotz, Jeanne

Reeves, Glenn McCoin, Fritz Lechnitz, Eric Van Soldt, Joelle Budd, Jan Taylor, Marilyn

Templeton, Berry Walker, Alberto Martin, Lorena Jimenez, Ron Talmage, Andy Kelly,

Rushabh Mehta, Eladio Rincón, Erik Veerman, Johan Richard Waymire, Carl Rabeler,

Chris Randall, Åhlén, Raoul Illyés, Peter Larsson, Peter Myers, Paul Turley, and so many

others.

To members of the SQL Server Pro editorial team: Megan Keller, Lavon Peters,

Michele Crockett, Mike Otey, and I’m sure many others. I’ve been writing for the

剩余243页未读，继续阅读

kumikoda

粉丝: 0
资源: 3

SQL Server 2012 高性能T-SQL窗口函数实战

探索Microsoft SQL Server 2012

Microsoft SQL Server 2012编程实战指南

探索：微软SQL Server 2012详解

[电子书] SQL Server 2012 编程 (英文版)

[电子书] SQL Server 2012 安全经典实例 (英文版)

Microsoft SQL Server 2012查询指南：Exam 70-461精髓

SQL Server 2005精髓(SQL Server 2005 Reporting Essentials) 英文版

Inside Microsoft SQL Server 2008 T-SQL Programming.pdf

SQL Server 2005 技术内幕（4本 chm英文版）

SQL Server 2005数据挖掘实战电子书：详尽操作指南

最新资源