Dewson Beginning SQL Server 2008 for Developers

From Novice to Professional
1. ed
ISBN: 978-1-4302-0584-5
Verlag: Apress
Format: PDF
Kopierschutz: 1 - PDF Watermark

E-Book, Englisch, 496 Seiten

Beginning SQL Server 2008 for Developers is the starting-point in the Apress roadmap of titles for developers who wish to base their projects upon Microsoft's flagship database management system. The book takes developers from the point of installing SQL Server 2008, through the process of storing, retrieving, and securing data, to the point of being able to serve up business reports using SQL Server 2008 Reporting Services. The author has been hooked on programming ever since he bought his first computer, a Sinclair ZX80, in 1980. He maintains visibility in the field through his website and his other books.

Robin Dewson has been hooked on programming ever since he bought his first computer, a Sinclair ZX80, in 1980. He has been working with SQL Server since version 6.5 and Visual Basic since version 5. Robin is a consultant mainly in the city of London, where he has been for nearly eight years. He also has been developing a rugby-related website as well as maintaining his own site at Fat-Belly.com.

Autoren/Hrsg.

Dewson, Robin

Inhaltsverzeichnis

1;Contents at a Glance;5
2;Contents;6
3;About the Author;15
4;About the Technical Reviewer;16
5;Acknowledgments;17
6;Introduction;18
6.1;Who This Book Is For;18
6.2;How This Book Is Structured;18
6.3;Prerequisites;18
6.4;Downloading the Code;19
6.5;Contacting the Author;19
7;SQL Server 2008 Overview and Installation;20
7.1;Why SQL Server 2008?;21
7.2;Evolution of SQL Server;22
7.3;Hardware Requirements;23
7.4;The Example;24
7.5;Installation;24
7.6;Security;36
7.7;Summary;42
8;SQL Server Management Studio;43
8.1;A Quick Overview of SSMS;43
8.2;Examining SSMS’s Options;51
8.3;Query Editor;66
8.4;Summary;68
9;Database Design and Creation;69
9.1;Defining a Database;70
9.2;Prebuilt Databases Within SQL Server;71
9.3;Choosing the Database System Type;74
9.4;Gathering the Data;75
9.5;Determining the Information to Store in the Database;77
9.6;External and Ignored Information;79
9.7;Building Relationships;80
9.8;Normalization;85
9.9;Creating the Sample Database;89
9.10;Summary;107
10;Security and Compliance;108
10.1;Logins;108
10.2;Server Logins and Database Users;118
10.3;Roles;118
10.4;Schemas;124
10.5;Before You Can Proceed with Your Solution;126
10.6;Declarative Management Framework;130
10.7;Summary;134
11;Defining Tables;136
11.1;What Is a Table?;136
11.2;SQL Server Data Types;137
11.3;Columns Are More Than Simple Data Repositories;143
11.4;Image and Large Text Storage in SQL Server;145
11.5;Creating a Table in SQL Server Management Studio;145
11.6;Creating a Table Through the Query Editor;151
11.7;Creating a Table: Using a Template;153
11.8;Creating and Altering a Template;156
11.9;The ALTER TABLE Statement;157
11.10;Defining the Remaining Tables;158
11.11;Setting a Primary Key;159
11.12;Creating a Relationship;160
11.13;Building a Relationship via T-SQL;165
11.14;Summary;167
12;Creating Indexes and Database Diagramming;168
12.1;What Is an Index?;168
12.2;Determining What Makes a Good Index;171
12.3;Determining What Makes a Bad Index;173
12.4;Reviewing Your Indexes for Performance;174
12.5;Creating an Index;175
12.6;Dropping an Index;187
12.7;Altering an Index in Query Editor;188
12.8;When an Index Does Not Exist;189
12.9;Diagramming the Database;189
12.10;Summary;196
13;Database Backups, Recovery, and Maintenance;197
13.1;Transaction Logs;198
13.2;Backup Strategies;199
13.3;When Problems May Occur;201
13.4;Taking a Database Offline;201
13.5;Backing Up the Data;203
13.6;Restoring a Database;216
13.7;Detaching and Attaching a Database;223
13.8;Producing SQL Script for the Database;231
13.9;Maintaining Your Database;236
13.10;Creating a Database Maintenance Plan;237
13.11;Setting Up Database Mail;250
13.12;Modifying a Maintenance Plan;259
13.13;Summary;263
14;Working with the Data;264
14.1;The T- SQL INSERT Command Syntax;264
14.2;INSERT SQL Command;265
14.3;DBCC CHECKIDENT;272
14.4;Column Constraints;273
14.5;Inserting Several Records at Once;278
14.6;Retrieving Data;279
14.7;Using SQL Server Management Studio to Retrieve Data;280
14.8;The SELECT Statement;281
14.9;Naming the Columns;283
14.10;The First Searches;283
14.11;Varying the Output Display;285
14.12;Limiting the Search: the Use of WHERE;287
14.13;String Functions;293
14.14;Order! Order!;294
14.15;The LIKE Operator;296
14.16;Creating Data: SELECT INTO;298
14.17;Who Can Add, Delete, and Select Data;299
14.18;Updating Data;304
14.19;Transactions;309
14.20;Deleting Data;315
14.21;Truncating a Table;318
14.22;Dropping a Table;319
14.23;Summary;319
15;Building a View;321
15.1;Why a View?;321
15.2;Using Views for Security;322
15.3;Encrypting View Definitions;323
15.4;Creating a View: SQL Server Management Studio;323
15.5;Creating a View Using a View;329
15.6;CREATE VIEW Syntax;335
15.7;Creating a View: a Query Editor Pane;336
15.8;Creating a View: SCHEMABINDING;337
15.9;Indexing a View;339
15.10;Summary;341
16;Stored Procedures and Functions;343
16.1;What Is a Stored Procedure?;344
16.2;CREATE PROCEDURE Syntax;344
16.3;Returning a Set of Records;346
16.4;Creating a Stored Procedure: Management Studio;347
16.5;Different Methods of Executing;351
16.6;Using RETURN;351
16.7;Controlling the Flow;355
16.8;Bringing It All Together;361
16.9;User- Defined Functions;363
16.10;Summary;367
17;T-SQL Essentials;368
17.1;Using More Than One Table;368
17.2;Variables;373
17.3;Temporary Tables;375
17.4;Aggregations;377
17.5;Grouping Data;380
17.6;HAVING;382
17.7;Distinct Values;383
17.8;Functions;383
17.9;RAISERROR;397
17.10;Error Handling;400
17.11;@@ ERROR;401
17.12;TRY. . . CATCH;402
17.13;Summary;406
18;Advanced T-SQL;407
18.1;Subqueries;407
18.2;The APPLY Operator;411
18.3;Common Table Expressions;414
18.4;Pivoting Data;417
18.5;Ranking Functions;419
18.6;PowerShell Within SQL Server;424
18.7;Summary;428
19;Triggers;429
19.1;What Is a Trigger?;429
19.2;The DML Trigger;430
19.3;CREATE TRIGGER Syntax for DML Triggers;431
19.4;Why Not Use a Constraint?;432
19.5;Deleted and Inserted Logical Tables;433
19.6;Creating a DML FOR Trigger;433
19.7;Checking Specific Columns;437
19.8;DDL Triggers;444
19.9;Summary;450
20;SQL Server 2008 Reporting Services;451
20.1;Reporting Services Architecture;451
20.2;Configuring Reporting Services;453
20.3;Building Your First Report Using Report Designer;460
20.4;Summary;469
21;Index;470