Advanced Transact-SQL for SQL Server 2000.pdf

Table of Contents

Advanced Transact−SQL for SQL Server 2000.............................................................................................1

Dedication............................................................................................................................................................3

Foreword..............................................................................................................................................................5

Preface..................................................................................................................................................................7

The Passing of Knowledge, by Itzik Ben−Gan.......................................................................................7

I Can't Believe I'm Doing This..., by Tom Moreau..............................................................................10

Introduction.......................................................................................................................................................15

Who Should Read This Book...............................................................................................................15

Our Feelings on Beta Software.............................................................................................................15

What You Will See...............................................................................................................................15

Chapter 1: Joins in T−SQL.............................................................................................................................18

Overview...............................................................................................................................................18

And Then There Were Two..................................................................................................................18

The Latex Manufacturing Company Example......................................................................................18

Inner Joins.............................................................................................................................................20

Old−Style SQL−89 Join Syntax.....................................................................................................20

Old−Style SQL−89 Two−Way Inner Joins....................................................................................20

SQL−92 Join Syntax......................................................................................................................21

SQL−92 Two−Way Inner Joins.....................................................................................................22

Old−Style SQL−89 Three−Way Inner Joins..................................................................................22

SQL−92 Three−Way Inner Joins...................................................................................................23

Join Query Table Order..................................................................................................................24

Cross Joins............................................................................................................................................24

Outer Joins............................................................................................................................................26

Old−Style Outer Joins....................................................................................................................26

Old−Style Two−Way Left Outer Joins..........................................................................................26

SQL−92 Two−Way Outer Joins.....................................................................................................27

SQL−92 Two−Way Left Outer Joins.............................................................................................27

Old−Style Two−Way Right Outer Joins........................................................................................28

SQL−92 Two−Way Right Outer Joins...........................................................................................28

SQL−92 Two−Way Full Outer Joins.............................................................................................29

SQL−92 Three−Way Outer Joins...................................................................................................30

The Catch.......................................................................................................................................32

Controlling the Order of Join Processing.......................................................................................34

Self and Non−Equal Joins.....................................................................................................................37

The Dating Service Scenario..........................................................................................................37

The Salary Levels Scenario............................................................................................................41

Using Joins to Modify Data..................................................................................................................43

Using Joins to Delete Data.............................................................................................................43

Using Joins to Update Data............................................................................................................44

Performance Considerations.................................................................................................................45

Completely Qualified Filter Criteria..............................................................................................45

Join Hints........................................................................................................................................46

Performance Guidelines.................................................................................................................47

SQL Puzzle 1−1: Joins..........................................................................................................................47

Table of Contents

Chapter 2: Subqueries and Derived Tables...................................................................................................48

Understanding Subqueries....................................................................................................................48

Creating Nested Scalar Subqueries................................................................................................48

Using the IN Predicate as a Subquery............................................................................................49

Using Correlated Subqueries..........................................................................................................51

Calculating Running Totals............................................................................................................60

Creating Derived Tables.......................................................................................................................61

Using a SELECT with a GROUP BY as a Derived Table.............................................................61

Finding Duplicate Rows in a Table................................................................................................63

Updating Rows with a GROUP BY Clause...................................................................................63

Using Unions in Derived Tables....................................................................................................64

Creating Histograms.......................................................................................................................65

Comparing Performance.......................................................................................................................66

SQL Puzzle 2−1: Bank Interest............................................................................................................70

SQL Puzzle 2−2: Managing Orders and Their Payments.....................................................................71

SQL Puzzle 2−3: Finding Unread Messages........................................................................................73

Chapter 3: Populating Tables.........................................................................................................................75

Overview...............................................................................................................................................75

Using the INSERT Statement...............................................................................................................75

Using the INSERT DEFAULT VALUES Statemen.....................................................................76

Using the INSERT VALUES Statemen........................................................................................77

Using the INSERT SELECT Statement.........................................................................................78

Leveraging Defaults.......................................................................................................................79

Using the INSERT EXEC Statement.............................................................................................79

Using the SELECT INTO Statement....................................................................................................81

The Bulk Copy Programbcp.................................................................................................................83

Using the BULK INSERT Statement...................................................................................................83

BATCHSIZE..................................................................................................................................84

CHECK_CONSTRAINTS.............................................................................................................84

CODEPAGE...................................................................................................................................84

DATAFILETYPE..........................................................................................................................85

FIELDTERMINATOR..................................................................................................................85

FIRETRIGGERS............................................................................................................................86

FIRSTROW....................................................................................................................................86

FORMATFILE...............................................................................................................................86

KEEPIDENTITY...........................................................................................................................86

KEEPNULLS.................................................................................................................................86

KILOBYTES_PER_BATCH.........................................................................................................86

LAST_ROW...................................................................................................................................86

MAXERRORS...............................................................................................................................86

ORDER..........................................................................................................................................86

ROWS_PER_BATCH....................................................................................................................87

ROWTERMINATOR....................................................................................................................87

TABLOCK.....................................................................................................................................87

Loading Data.........................................................................................................................................87

Normalizing Data...........................................................................................................................88

Generating Test Data......................................................................................................................91

SQL Puzzle 3−1: Populating the Customers Table..............................................................................94

Table of Contents

Chapter 4: Other Data Manipulation Issues.................................................................................................95

Leveraging CASE Expressions.............................................................................................................95

Using the Simple CASE Expression..............................................................................................95

Using the Searched CASE Expression...........................................................................................96

Determining Your Own Sort Order................................................................................................97

Creating Updates with the CASE Expression................................................................................98

Creating Pivot Tables.....................................................................................................................98

Creating TOP Queries.........................................................................................................................100

Using the TOP n Option...............................................................................................................100

Using the WITH TIES Option......................................................................................................101

Using the TOP n PERCENT Option............................................................................................103

Using the SET ROWCOUNT Option..........................................................................................103

Using Logical Expressions and Bitwise Operations...........................................................................104

Using Logical Expressions...........................................................................................................105

Using Bitwise Operations.............................................................................................................111

Operator Precedence.....................................................................................................................119

Displaying Leading Zeroes.................................................................................................................121

Handling Dates....................................................................................................................................121

Using DATEPART() and YEAR(), MONTH(), and DAY().......................................................122

Using the DATENAME() Function....................................................................................................123

Using the DATEADD() Function................................................................................................123

Using the DATEDIFF() Function................................................................................................124

Avoiding WHERE Clause Traps in Date Manipulation..............................................................124

Using the CONVERT() Function.................................................................................................125

Finding the First Day of the Month..............................................................................................126

Finding the Last Day of the Month..............................................................................................126

Adding Months to a Date....................................................................................................................127

Finding the Month Name Based on the Month Number..............................................................127

Handling Language−Independent Date Input..............................................................................128

Using the GETUTCDATE() Function.........................................................................................128

SQL Puzzle 4−1: Euro 2000 Them (posted by Colin Rippey)...........................................................128

Chapter 5: Summarizing Data......................................................................................................................131

Refining Your GROUP BY Queries...................................................................................................131

CUBE...........................................................................................................................................134

ROLLUP......................................................................................................................................141

Should I CUBE or ROLLUP?......................................................................................................144

To CUBE or Not to CUBE...........................................................................................................146

COMPUTE.........................................................................................................................................150

Using the COMPUTE Option......................................................................................................150

Using the COMPUTE BY Option................................................................................................152

COMPUTE Considerations..........................................................................................................154

SQL Puzzle 5−1: Management Levels...............................................................................................155

Chapter 6: Special Datatypes and Properties.............................................................................................156

Using Unicode Datatypes...................................................................................................................156

Using the rowversion Datatype...........................................................................................................157

Using the text, ntext, and image Datatypes.........................................................................................159

Using Text in Row.......................................................................................................................159

Using the sp_invalidate_textptr Stored Procedure.......................................................................161

iii

Table of Contents

Chapter 6: Special Datatypes and Properties

Using Text Pointers and the TEXTPTR() and TEXTVALID() Functions..................................161

Using the READTEXT Statement...............................................................................................161

Using the WRITETEXT Statemen..............................................................................................162

Using the UPDATETEXT Statemen...........................................................................................163

Using SET TEXTSIZE.................................................................................................................165

Using the bigint Datatype...................................................................................................................165

Using the uniqueidentifier Datatype...................................................................................................166

Using the sql_variant Datatype...........................................................................................................166

Using the SQL_VARIANT_PROPERTY() Function..................................................................168

Using the table Datatype.....................................................................................................................170

Using the IDENTITY Property and the IDENTITY() Function........................................................172

Using the IDENTITY Property....................................................................................................172

Creating and Querying Tables with IDENTITY Columns..........................................................172

Using the IDENTITY_INSERT Session Option..........................................................................174

What Did I Just Insert?.................................................................................................................175

SQL Puzzle 6−1: Customers with and without Sales...................................................................181

Chapter 7: Writing Code in Transact−SQL................................................................................................182

Overview.............................................................................................................................................182

Working with Variables......................................................................................................................182

Assigning Values with SET and SELECT.........................................................................................183

Control of Flow...................................................................................................................................185

The IF ELSE Construct................................................................................................................185

The WHILE Construct.................................................................................................................187

Handling Errors...................................................................................................................................188

Managing Error Messages............................................................................................................188

Raising Errors...............................................................................................................................190

Trapping Errors............................................................................................................................194

Processing Batches..............................................................................................................................195

Commenting Code..............................................................................................................................195

Coding Styles......................................................................................................................................195

Tom's Style...................................................................................................................................196

Itzik's Style...................................................................................................................................197

Other Styles..................................................................................................................................198

Using Transactions..............................................................................................................................198

Using Implicit Transactions.........................................................................................................200

Understanding Deadlocks.............................................................................................................200

Understanding Nested Transactions and @@TRANCOUNT.....................................................200

Leveraging Savepoints.................................................................................................................201

SQL Puzzle 7−1: Eliminating an Explicit Transaction.......................................................................201

Chapter 8: Views............................................................................................................................................202

View Limitations and Requirements..................................................................................................202

Each Column Must Have a Name and the Name Must Be Unique.............................................202

Cannot Use SELECT INTO in a View........................................................................................204

Cannot Use the ORDER BY Clause in a View............................................................................204

Hiding the Complexity of the Underlying Query...............................................................................207

Using Views as a Security Mechanism...............................................................................................208

Altering a View...................................................................................................................................210

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: