The Guru's guide to Transact-SQL [covers Microsoft Transact-SQL]

583 Pages·5.33 MB·English

by  Henderson, Kenneth

The Guru’s Guide
to Transact-SQL
Ken Henderson
ADDISON-WESLEY
An imprint of Addison Wesley Longman, Inc.
Reading, Massachusetts • Harlow, England • Menlo Park, California
Berkeley, California • Don Mills, Ontario • Sydney
Bonn • Amsterdam • Tokyo • Mexico City
Many of the designations used by manufacturers and sellers to distinguish their products are claimed as
trademarks. Where those designations appear in this book and Addison-Wesley was aware of a trademark
claim, the designations have been printed in initial caps or all caps.
The author and publisher have taken care in the preparation of this book but make no expressed or im-
plied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed
for incidental or consequential damages in connection with or arising out of the use of the information or
programs contained herein.
The publisher offers discounts on this book when ordered in quantity for special sales. For more informa-
tion, please contact:
Corporate, Government, and Special Sales Group
Addison Wesley Longman, Inc.
One Jacob Way
Reading, Massachusetts 01867
[phone]
Visit AW on the Web: www.awl.com/cseng
Library of Congress Cataloging-in-Publication Data
Henderson, Kenneth W.
The guru’s guide to Transact-SQL / Kenneth W. Henderson.
p. cm.
Includes bibliographical references and index.
ISBN 0-201-61576-2
1. SQL (Computer program language) I. Title.
QA76.73.S67 H47 2000
005.75(cid:2)6—dc21 99-057209
Copyright © 2000 by Addison Wesley Longman, Inc.
All rights reserved. No part of this publication may be reproduced, stored in a retrieval system, or trans-
mitted, in any form, or by any means, electronic, mechanical, photocopying, recording, or otherwise,
without the prior consent of the publisher. Printed in the United States of America. Published simulta-
neously in Canada.
Acquisitions Editor: Michael Slaughter
Editorial Assistant: Marisa Meltzer
Production Coordinator: Jacquelyn Doucette
Composition: G&S Typesetters, Inc.
Text printed on recycled and acid-free paper.
ISBN 0201615762
151617181920 DOC 09 08 07
15th Printing September 2007
For H
This page intentionally left blank
Contents
Foreword xvi
Preface xvii
About the Sample Databases xviii
Results Abridged xviii
On Formality xviii
Acknowledgments xix
Chapter 1: Introductory Transact-SQL 1
Choosing a SQL Editor 2
Creating a Database 3
Creating Tables 3
Inserting Data 5
Updating Data 6
Deleting Data 7
Querying Data 7
Column Lists 8
SELECTing Variables and Expressions 9
Functions 9
Converting Data Types 10
CASE 10
Aggregate Columns 11
Filtering Data 11
Joins 13
Outer Joins 14
Other Types of Joins 16
Subqueries 18
Grouping Data 18
HAVING 19
Ordering Data 19
Column Aliases 20
Table Aliases 21
v
vi Contents
Managing Transactions 21
Summary 22
Chapter 2: Transact-SQL Data Type Nuances 23
Dates 23
Y2K and Other Date Problems 24
Date Functions 25
Dates and Simple Arithmetic 26
Determining Time Gaps 27
Building Calendars 31
Strings 34
Concatenation 35
Char vs. Varchar 35
SET ANSI_PADDING 36
String Functions 37
CHARINDEX() 37
SOUNDEX() 37
A Better SOUNDEX() 38
DIFFERENCE() 46
Xp_sprintf 47
Xp_sscanf 47
Masks 48
Executing Strings 49
Unicode 54
Numerics 54
Floating Point Fun 55
Division by Zero 57
Funny Money 58
Formatting Numeric Data 58
BLOBs 59
Caveats 59
Retrieving BLOB Data 60
Updating BLOB Data 63
BLOB Updates and the Transaction Log 65
Bits 66
UNIQUEIDENTIFIER 67
Cursor Variables 68
Timestamps 73
Summary 75
Chapter 3: Missing Values 77
NULL and Expressions 78
NULL and Functions 78
NULL and ANSI SQL 79
Contents vii
NULL and Stored Procedures 81
NULL If You Must 83
Chapter 4: DDL Insights 85
CREATE TABLE 86
Some Thoughts on Referential Integrity 86
Foreign Keys 86
ANSI Referential Actions 87
The NULL Exception 87
Unique Index Requirement 87
No TRUNCATE TABLE 87
Default Constraints 87
Dropping Objects 89
CREATE INDEX 90
No Bit or BLOB Indexes 90
No Computed Column Indexes 90
PAD_INDEX 90
DROP_EXISTING 91
TEMPORARY OBJECTS 91
No More Unusable Temporary Objects 91
Can’t Create Objects in Other Databases 91
Temporary Stored Procedures 92
Increased Temporary Table Name Length 92
Global Temporary Status Tables 92
Object Naming and Dependencies 93
Changing the Database Context Temporarily 94
Temporary Table Indexes 94
Be Wary of Unusable Views 94
Object Dependencies 94
Summary 95
Chapter 5: DML Insights 97
INSERT 97
DEFAULT and NULL 98
SET IDENTITY_INSERT 99
INSERT...DEFAULT VALUES 100
INSERT...SELECT 100
INSERT...EXEC 101
Extended Procedures 102
INSERT and Errors 103
Using INSERT to Remove Duplicate Rows 104
INSERT and Clustered Indexes 105
BULK INSERT 105
BULK INSERT and Triggers 106
viii Contents
BULK INSERT and Constraints 106
BULK INSERT and Identity Columns 106
UPDATE 107
The Halloween Problem 108
UPDATE and CASE 109
Using UPDATE to Check Constraints 110
Limiting the Number of Rows Affected by an UPDATE 111
Swapping Column Values with UPDATE 112
UPDATE and Cursors 113
DELETE 114
DELETE and Cursors 116
TRUNCATE TABLE 117
Detecting DML Errors 118
Summary 118
Chapter 6: The Mighty SELECT Statement 119
Simple SELECTs 119
Computational and Derived Fields 120
SELECT TOP 121
Derived Tables 122
Joins 126
Outer Joins and Join Order 126
Predicates 128
BETWEEN 128
LIKE 131
EXISTS 133
NULLs 134
EXISTS and IN 135
Joins 136
Result Set Emptiness 137
EXISTS Outside WHERE and HAVING 137
IN 138
Optimizing IN 138
ANY and ALL 140
Subqueries 140
WHERE and Subqueries 141
Correlated Subqueries 141
Relational Division 145
Aggregate Functions 147
GROUP BY and HAVING 150
Pivot Tables 152
CUBE and ROLLUP 153
HAVING 157
UNION 157
ORDER BY 159
Summary 161
Contents ix
Chapter 7: Views 163
Restrictions 163
DML Restrictions 164
ANSI SQL Schema VIEWs 165
Getting a VIEW’s Source Code 166
Updatable VIEWs 166
WITH CHECK OPTION 167
Derived Tables 167
Dynamic VIEWs 168
Partitioning Data Using Views 170
Summary 172
Chapter 8: Statistical Functions 173
The Case for CASE 174
Efficiency Concerns 176
Variance and Standard Deviation 176
Medians 177
The Identity Column Technique 177
The CASE Technique 181
Vector Medians 183
Duplicate Values 184
Clipping 185
Returning the Top nRows 186
SET ROWCOUNT 188
Rankings 190
Modes 193
Histograms 193
Stratified Histograms 194
Cumulative and Sliding Aggregates 195
Sliding Aggregates 196
Extremes 197
Determining Extreme Attributes 198
Summary 199
Chapter 9: Runs and Sequences 201
Sequences 201
Time Series Fluctuation 202
Sampling Every nth Value 203
Regions 203
Relative Condition Regions 204
Constraining Region Sizes 205
Region Boundaries 206
Runs 207
Regions 207
Region Boundaries 208
Constrained Regions 209