Beginning Microsoft SQL Server 2012 Programming E-Book

Paul Atkinson

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Sprache: Englisch

Seitenzahl: 1536

Veröffentlichungsjahr: 2012

Beschreibung

Get up to speed on the extensive changes to the newest release of Microsoft SQL Server The 2012 release of Microsoft SQL Server changes how you develop applications for SQL Server. With this comprehensive resource, SQL Server authority Robert Vieira presents the fundamentals of database design and SQL concepts, and then shows you how to apply these concepts using the updated SQL Server. Publishing time and date with the 2012 release, Beginning Microsoft SQL Server 2012 Programming begins with a quick overview of database design basics and the SQL query language and then quickly proceeds to show you how to implement the fundamental concepts of Microsoft SQL Server 2012. You'll explore the key additions and changes to this newest version, including conditional action constructs, enhanced controls for results paging, application integration with SharePoint and Excel, and development of BI applications. * Covers new features such as SQL Azure for cloud computing, client-connectivity enhancements, security and compliance, data replication, and data warehouse performance improvements * Addresses essential topics including managing keys, writing scripts, and working with store procedures * Shares helpful techniques for creating and changing tables, programming with XML, and using SQL Server Reporting and Integration Services Beginning Microsoft SQL Server 2012 Programming demystifies even the most difficult challenges you may face with the new version of Microsoft SQL Server.

Leseprobe

CONTENTS

Cover

Contents

Title page

Copyright

Dedication

Credits

About the Authors

About the Technical Editor

Acknowledgments

Introduction

1 RDBMS Basics: What Makes Up a SQL Server Database?

An Overview of Database Objects

SQL Server Data Types

SQL Server Identifiers for Objects

Summary

2 Learning the Tools of the Trade

Getting Help with Books Online

SQL Server Configuration Manager

SQL Server Management Studio

SQL Server Data Tools (formerly BIDS)

SQL Server Integration Services (SSIS)

SQL Server Reporting Services (SSRS)

SQL Server Analysis Services (SSAS)

Bulk Copy Program (BCP)

SQL Server Profiler

sqlcmd

PowerShell

Summary

3 The Foundation Statements of T-SQL

Getting Started with a Basic SELECT Statement

Adding Data with the INSERT Statement

Changing What You’ve Got with the UPDATE Statement

The DELETE Statement

Summary

4 Joins

Combining Table Data with JOINs

Selecting Matching Rows with INNER JOIN

Retrieving More Data with OUTER JOIN

Seeing Both Sides with FULL JOINs

Understanding CROSS JOINs

Exploring Alternative Syntax for Joins

Stacking Results with UNION

Summary

5 Creating and Altering Tables

Object Names in SQL Server

The CREATE Statement

The ALTER Statement

The DROP Statement

Using the GUI Tool

Summary

6 Keys and Constraints

Types of Constraints

Constraint Naming

Key Constraints

CHECK Constraints

DEFAULT Constraints

Disabling Constraints

Rules and Defaults — Cousins of Constraints

Triggers for Data Integrity

Choosing What to Use

Summary

7 Adding More to Your Queries

What Is a Subquery?

Building Correlated Subqueries

Derived Tables

Using Common Table Expressions (CTEs)

Using the EXISTS Operator

Mixing Data Types: CAST and CONVERT

Synchronizing Data with the MERGE Command

Gathering Affected Rows with the OUTPUT Clause

Through the Looking Glass: Windowing Functions

One Chunk at a Time: Ad Hoc Query Paging

Performance Considerations

Summary

8 Being Normal: Normalization and Other Basic Design Issues

Understanding Tables

Keeping Your Data “Normal”

Understanding Relationships

Diagramming Databases

Denormalization

Beyond Normalization

Drawing Up a Quick Example

Summary

9 SQL Server Storage and Index Structures

SQL Server Storage

Understanding Indexes

Creating, Altering, and Dropping Indexes

Choosing Wisely: Deciding Which Index Goes Where and When

Maintaining Your Indexes

Summary

10 Views

Creating Simple Views

Editing Views with T-SQL

Dropping Views

Creating and Editing Views in the Management Studio

Auditing: Displaying Existing Code

Protecting Code: Encrypting Views

About Schema Binding

Making Your View Look Like a Table with VIEW_METADATA

Indexed (Materialized) Views

Indexing an Aggregate View

Summary

11 Writing Scripts and Batches

Understanding Script Basics

Grouping Statements into Batches

Running from the Command Prompt: sqlcmd

Dynamic SQL: Using the EXEC Command

Using Control-of-Flow Statements

Summary

12 Stored Procedures

Creating the Sproc: Basic Syntax

Changing Stored Procedures with ALTER

Dropping Sprocs

Parameterizing Sprocs

More on Dealing with Errors

What a Sproc Offers

Extended Stored Procedures (XPs)

A Brief Look at Recursion

Debugging

Understanding .NET Assemblies

When to Use Stored Procedures

Summary

13 User-Defined Functions

What a UDF Is

UDFs Returning a Scalar Value

UDFs That Return a Table

Debugging User-Defined Functions

Using .NET in a Database World

Summary

14 Transactions and Locks

Understanding Transactions

How the SQL Server Log Works

Understanding Locks and Concurrency

Setting the Isolation Level

Dealing with Deadlocks (aka “a 1205”)

Summary

15 Triggers

What Is a Trigger?

Using Triggers for Data Integrity Rules

Other Common Uses for Triggers

Other Trigger Issues

Instead of Triggers

Performance Considerations

Dropping Triggers

Debugging Triggers

Summary

16 A Brief XML Primer

XML Basics

What SQL Server Brings to the Party

A Brief Word on XSLT

Summary

17 Business Intelligence Fundamentals

What Is Business Intelligence?

Those Who Forget History: The Data Warehouse

Dimensional Modeling: Why Be Normal?

ETLs

Making Your Data Actionable: BI Reporting Techniques

Summary

18 BI Storage and Reporting Resources

BI in T-SQL

SQL Server Analysis Services, or How I Learned to Stop Worrying and Love the Cube

Building Your First Cube

Self-Service BI: User Tools

Summary

19 Reporting for Duty, Sir! A Look at Reporting Services

Reporting Services 101

Understanding the SSRS Report Lifecycle

Understanding the Reporting Services Architecture

Building Simple Report Models

Report Server Projects

Summary

20 Getting Integrated with Integration Services

Understanding the Problem

Using the Import/Export Wizard to Generate Basic Packages

Examining Package Basics

Executing Packages

A Final Word on Packages

Summary

21 Playing Administrator

Scheduling Jobs

Logins and Users

Backup and Recovery

Index Maintenance

Policy Based Management

Automating Administration Tasks with PowerShell

Summary

Appendix: Answers to Exercises

Bonus Appendices

Bonus Appendix 1: System Functions

System Statistical Functions

Aggregate Functions

Analytic Functions

Configuration Functions

Conversion Functions

Cryptographic Functions

Cursor Functions

Date and Time Functions

Hierarchy Functions

Mathematical Functions

Basic Metadata Functions

Ranking Functions

Rowset Functions

Security Functions

String Functions

System Functions

Text and Image Functions

Bonus Appendix 2: Very Simple Connectivity Examples

Some General Concepts

Connectivity Examples

A Brief Word on LINQ

Advertisement

End User License Agreement

List of Illustrations

1 RDBMS Basics: What Makes Up a SQL Server Database?

FIGURE 1-1

FIGURE 1-2

FIGURE 1-3

2 Learning the Tools of the Trade

FIGURE 2-1

FIGURE 2-2

FIGURE 2-3

FIGURE 2-4

FIGURE 2-5

FIGURE 2-6

FIGURE 2-7

FIGURE 2-8

FIGURE 2-9

FIGURE 2-10

FIGURE 2-11

FIGURE 2-12

FIGURE 2-13

FIGURE 2-14

3 The Foundation Statements of T-SQL

FIGURE 3-1

FIGURE 3-2

FIGURE 3-3

4 Joins

FIGURE 4-1

5 Creating and Altering Tables

FIGURE 5-1

FIGURE 5-2

FIGURE 5-3

FIGURE 5-4

FIGURE 5-5

FIGURE 5-6

FIGURE 5-7

FIGURE 5-8

FIGURE 5-9

FIGURE 5-10

6 Keys and Constraints

FIGURE 6-1

8 Being Normal: Normalization and Other Basic Design Issues

FIGURE 8-1

FIGURE 8-2

FIGURE 8-3

FIGURE 8-4

FIGURE 8-5

FIGURE 8-6

FIGURE 8-7

FIGURE 8-8

FIGURE 8-9

FIGURE 8-10

FIGURE 8-11

FIGURE 8-12

FIGURE 8-13

FIGURE 8-14

FIGURE 8-15

FIGURE 8-16

FIGURE 8-17

FIGURE 8-18

FIGURE 8-19

FIGURE 8-20

FIGURE 8-21

FIGURE 8-22

FIGURE 8-23

FIGURE 8-24

FIGURE 8-25

FIGURE 8-26

FIGURE 8-27

FIGURE 8-28

FIGURE 8-29

FIGURE 8-30

FIGURE 8-31

FIGURE 8-32

FIGURE 8-33

9 SQL Server Storage and Index Structures

FIGURE 9-1

FIGURE 9-2

FIGURE 9-3

FIGURE 9-4

FIGURE 9-5

FIGURE 9-6

FIGURE 9-7

FIGURE 9-8

FIGURE 9-9

FIGURE 9-10

10 Views

FIGURE 10-1

FIGURE 10-2

FIGURE 10-3

FIGURE 10-4

FIGURE 10-5

FIGURE 10-6

12 Stored Procedures

FIGURE 12-1

FIGURE 12-2

FIGURE 12-3

FIGURE 12-4

FIGURE 12-5

FIGURE 12-6

FIGURE 12-7

FIGURE 12-8

FIGURE 12-9

FIGURE 12-10

FIGURE 12-11

FIGURE 12-12

FIGURE 12-13

FIGURE 12-14

FIGURE 12-15

13 User-Defined Functions

FIGURE 13-1

14 Transactions and Locks

FIGURE 14-1

FIGURE 14-2

FIGURE 14-3

15 Triggers

FIGURE 15-1

FIGURE 15-2

FIGURE 15-3

FIGURE 15-4

FIGURE 15-5

FIGURE 15-6

16 A Brief XML Primer

FIGURE 16-1

FIGURE 16-2

17 Business Intelligence Fundamentals

FIGURE 17-1

FIGURE 17-2

FIGURE 17-3

FIGURE 17-4

FIGURE 17-5

FIGURE 17-6

FIGURE 17-7

FIGURE 17-8

FIGURE 17-9

FIGURE 17-10

FIGURE 17-11

18 BI Storage and Reporting Resources

FIGURE 18-1

FIGURE 18-2

FIGURE 18-3

FIGURE 18-4

FIGURE 18-5

FIGURE 18-6

FIGURE 18-7

FIGURE 18-8

FIGURE 18-9

FIGURE 18-10

19 Reporting for Duty, Sir! A Look at Reporting Services

FIGURE 19-1

FIGURE 19-2

FIGURE 19-3

FIGURE 19-4

FIGURE 19-5

FIGURE 19-6

FIGURE 19-7

FIGURE 19-8

FIGURE 19-9

FIGURE 19-10

FIGURE 19-11

FIGURE 19-12

FIGURE 19-13

FIGURE 19-14

FIGURE 19-15

FIGURE 19-16

FIGURE 19-17

FIGURE 19-18

FIGURE 19-19

FIGURE 19-20

FIGURE 19-21

FIGURE 19-22

FIGURE 19-23

FIGURE 19-24

FIGURE 19-25

FIGURE 19-26

FIGURE 19-27

FIGURE 19-28

FIGURE 19-29

FIGURE 19-30

FIGURE 19-31

FIGURE 19-32

FIGURE 19-33

FIGURE 19-34

20 Getting Integrated with Integration Services

FIGURE 20-1

FIGURE 20-2

FIGURE 20-3

FIGURE 20-4

FIGURE 20-5

FIGURE 20-6

FIGURE 20-7

FIGURE 20-8

FIGURE 20-9

FIGURE 20-10

FIGURE 20-11

FIGURE 20-12

FIGURE 20-13

FIGURE 20-14

FIGURE 20-15

FIGURE 20-16

FIGURE 20-17

FIGURE 20-18

FIGURE 20-19

FIGURE 20-20

FIGURE 20-21

FIGURE 20-22

FIGURE 20-23

FIGURE 20-24

FIGURE 20-25

21 Playing Administrator

FIGURE 21-1

FIGURE 21-2

FIGURE 21-3

FIGURE 21-4

FIGURE 21-5

FIGURE 21-6

FIGURE 21-7

FIGURE 21-8

FIGURE 21-9

FIGURE 21-10

FIGURE 21-11

FIGURE 21-12

FIGURE 21-13

FIGURE 21-14

FIGURE 21-15

FIGURE 21-16

FIGURE 21-17

FIGURE 21-18

FIGURE 21-19

FIGURE 21-20

Appendix: Answers to Exercises

FIGURE A-1

FIGURE A-2

FIGURE A-3

FIGURE A-4

FIGURE A-5

FIGURE A-6

FIGURE A-7

FIGURE A-8

FIGURE A-9

FIGURE A-10

FIGURE A-11

FIGURE A-12

FIGURE A-13

FIGURE A-14

FIGURE A-15

FIGURE A-16

FIGURE A-17

FIGURE A-18

List of Table

3 The Foundation Statements of T-SQL

TABLE 3-1: WHERE Clause Operators

6 Keys and Constraints

TABLE 6-1: Data Integrity Decision Matrix

7 Adding More to Your Queries

TABLE 7-1: Windowing Functions

TABLE 7-2: Choosing among Subqueries, Derived Tables, and CTEs

8 Being Normal: Normalization and Other Basic Design Issues

TABLE 8-1: Orders before Normalization

TABLE 8-2: Orders Table with Repeating Groups Removed

TABLE 8-3: Customers Table from Repeating Groups

TABLE 8-4: The OrderDetails Table

TABLE 8-5: Orders as a Header Table

TABLE 8-6: The Products Table

TABLE 8-7: Removing Products from the OrderDetails Table

9 SQL Server Storage and Index Structures

TABLE 9-1: dm_db_index_physical_stats Output

11 Writing Scripts and Batches

TABLE 11-1: Common System Functions

TABLE 11-2: SEQUENCE Syntax Elements

TABLE 11-3: SQL Error Levels

TABLE 11-4: System Functions to Retrieve the Error Condition

12 Stored Procedures

TABLE 12-1: SQL Server Error Severity Levels

TABLE 12-2: Parameter Placeholders

TABLE 12-3: Placeholder Options

15 Triggers

TABLE 15-1: Bitwise Operators

16 A Brief XML Primer

TABLE 16-1: XML_SCHEMA_NAMESPACE Parameters

TABLE 16-2: Example of a Universal Table

TABLE 16-3: Mapping Flag Options

17 Business Intelligence Fundamentals

TABLE 17-1: A Sample Date Dimension

TABLE 17-2: A Type 2 Slowly Changing Dimension

Bonus Appendix 1: System Functions

TABLE B-1: @@OPTIONS Bitmask Values

TABLE B-2: Local Server Name Information That @@SERVERNAME Returns

TABLE B-3: Dateparts and Their Abbreviations

TABLE B-4: Database Property Values

TABLE B-5: Properties for SQL_VARIANT PROPERTY

TABLE B-6: propertyname Values

Guide

Cover

Table of Contents

Start Reading

Pages

v

vi

vii

viii

ix

x

xxv

xxvi

xxvii

xxviii

xxix

xxx

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

213

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

328

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

305

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425

426

427

428

429

430

431

432

433

435

436

437

438

439

440

441

442

443

444

445

446

447

448

449

450

451

452

453

454

455

456

457

458

459

460

461

462

463

464

465

466

467

468

469

470

471

472

473

474

475

476

477

478

479

480

481

482

483

484

485

486

487

489

490

491

492

493

494

495

496

497

498

499

500

501

502

503

504

505

506

507

508

509

510

511

512

513

514

515

516

517

518

519

520

521

522

523

524

525

526

527

528

529

530

531

532

533

534

535

536

537

538

539

540

541

542

543

544

545

546

547

548

549

550

551

552

553

554

555

556

557

558

559

561

562

563

564

565

566

567

568

569

570

571

572

573

574

575

576

577

578

579

580

581

582

583

584

585

586

587

588

589

590

591

592

593

594

595

596

597

598

599

600

601

602

603

604

605

606

607

608

609

610

611

612

613

614

615

616

617

618

619

620

621

622

623

624

625

626

627

628

629

630

631

632

633

634

635

636

637

638

639

640

641

642

643

644

645

646

647

648

649

650

651

652

653

654

655

656

657

658

659

660

661

662

663

664

665

666

667

668

669

670

671

672

673

674

675

676

677

678

679

680

681

682

683

684

685

686

687

688

689

690

691

692

693

694

695

696

697

698

699

700

701

702

703

704

705

706

707

708

709

710

711

712

713

714

715

716

717

718

719

720

721

722

723

724

725

726

727

728

729

730

731

732

733

734

735

736

737

738

739

740

741

742

743

744

745

746

747

748

749

750

751

752

753

754

755

756

757

758

759

760

761

762

763

764

765

766

767

768

769

770

771

772

773

774

775

776

777

778

779

780

781

783

784

785

786

787

788

789

790

791

792

793

794

795

796

797

798

799

800

801

802

803

804

805

BEGINNINGMicrosoft® SQL Server® 2012 Programming

Paul Atkinson

Robert Vieira

Beginning Microsoft® SQL Server® 2012 Programming

Published by

John Wiley & Sons, Inc.

10475 Crosspoint Boulevard

Indianapolis, IN 46256

www.wiley.com

Copyright © 2012 by John Wiley & Sons, Inc., Indianapolis, Indiana

Published simultaneously in Canada

ISBN: 978-1-118-10228-2

ISBN: 978-1-118-22388-8 (ebk)

ISBN: 978-1-118-23621-5 (ebk)

ISBN: 978-1-118-26216-0 (ebk)

Manufactured in the United States of America

10 9 8 7 6 5 4 3 2 1

No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, [phone], fax [phone]. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, [phone], fax [phone], or online at http://www.wiley.com/go/permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/or a potential source of further information does not mean that the author or the publisher endorses the information the organization or Web site may provide or recommendations it may make. Further, readers should be aware that Internet Web sites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at [phone], outside the United States at [phone] or fax [phone].

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley.com.

Library of Congress Control Number: 2012933622

Trademarks: Wiley, the Wiley logo, Wrox, the Wrox logo, Wrox Programmer to Programmer, and related trade dress are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission. Microsoft and SQL Server are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc., is not associated with any product or vendor mentioned in this book.