Beginning SQL Server 2008 for Developers (eBook)

From Novice to Professional

Robin Dewson (Autor)

eBook Download: PDF

2008 | 1st ed.
XXIV, 472 Seiten
Apress (Verlag)
978-1-4302-0584-5 (ISBN)

Infos

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.

---

SQL Server 2008 is a first–rate database management system. It offers more capability than any previous release of SQL Server. More than just a classic relational database management system, SQL Server 2008 includes exciting and powerful features that make it useful for everything from large corporate data warehouses to ad hoc departmental databases. You’ll find enhanced support for XML, new support for spatial data, transparent data encryption, a policy–based management system, and more. Author and developer Robin Dewson will show you the way from beginner to SQL Server 2008 professional. Learn to install SQL Server 2008 and navigate around Management Studio before getting right to the heart of mastering fundamental SQL Server 2008 tasks: creating tables, storing data, securing data, and retrieving it again. Dewson ensures you’ll be fully prepared to use all the basics and create a solid foundation for your own projects.Don’t forget about backups! Your database will house important data, so backing up is essential to protect yourself from inevitable hardware failure. Dewson walks you through SQL Server 2008’s easy–to–use backup and recovery feature set, giving you the grounding that you need in order to set up a reliable plan for recovery in your own environment.Learn to use Transact–SQL, a full–blown procedural language that is built right into the database system. Transact–SQL is the key to unlocking everything that SQL Server 2008 has to offer. Using Transact–SQL, you can write centrally encapsulated business logic through the use of stored procedures, automatically trigger processing through the use of triggers, and manipulate data within the server without having to move data back and forth across the network.Finally, you’ll learn a bit about SQL Server 2008 Reporting Services, a powerful tool that allows enterprise reporting. Reporting Services enables you to develop and serve reports across your organization and even to business partners outside your company. Reporting Services also gives end users the ability to create their own reports, helping them transform business data into valuable, usable information to guide their day–to–day decisions.

Inhalt

Contents at a Glance 5
Contents 6
About the Author 15
About the Technical Reviewer 16
Acknowledgments 17
Introduction 18
Who This Book Is For 18
How This Book Is Structured 18
Prerequisites 18
Downloading the Code 19
Contacting the Author 19
SQL Server 2008 Overview and Installation 20
Why SQL Server 2008? 21
Evolution of SQL Server 22
Hardware Requirements 23
The Example 24
Installation 24
Security 36
Summary 42
SQL Server Management Studio 43
A Quick Overview of SSMS 43
Examining SSMS’s Options 51
Query Editor 66
Summary 68
Database Design and Creation 69
Defining a Database 70
Prebuilt Databases Within SQL Server 71
Choosing the Database System Type 74
Gathering the Data 75
Determining the Information to Store in the Database 77
External and Ignored Information 79
Building Relationships 80
Normalization 85
Creating the Sample Database 89
Summary 107
Security and Compliance 108
Logins 108
Server Logins and Database Users 118
Roles 118
Schemas 124
Before You Can Proceed with Your Solution 126
Declarative Management Framework 130
Summary 134
Defining Tables 136
What Is a Table? 136
SQL Server Data Types 137
Columns Are More Than Simple Data Repositories 143
Image and Large Text Storage in SQL Server 145
Creating a Table in SQL Server Management Studio 145
Creating a Table Through the Query Editor 151
Creating a Table: Using a Template 153
Creating and Altering a Template 156
The ALTER TABLE Statement 157
Defining the Remaining Tables 158
Setting a Primary Key 159
Creating a Relationship 160
Building a Relationship via T-SQL 165
Summary 167
Creating Indexes and Database Diagramming 168
What Is an Index? 168
Determining What Makes a Good Index 171
Determining What Makes a Bad Index 173
Reviewing Your Indexes for Performance 174
Creating an Index 175
Dropping an Index 187
Altering an Index in Query Editor 188
When an Index Does Not Exist 189
Diagramming the Database 189
Summary 196
Database Backups, Recovery, and Maintenance 197
Transaction Logs 198
Backup Strategies 199
When Problems May Occur 201
Taking a Database Offline 201
Backing Up the Data 203
Restoring a Database 216
Detaching and Attaching a Database 223
Producing SQL Script for the Database 231
Maintaining Your Database 236
Creating a Database Maintenance Plan 237
Setting Up Database Mail 250
Modifying a Maintenance Plan 259
Summary 263
Working with the Data 264
The T- SQL INSERT Command Syntax 264
INSERT SQL Command 265
DBCC CHECKIDENT 272
Column Constraints 273
Inserting Several Records at Once 278
Retrieving Data 279
Using SQL Server Management Studio to Retrieve Data 280
The SELECT Statement 281
Naming the Columns 283
The First Searches 283
Varying the Output Display 285
Limiting the Search: the Use of WHERE 287
String Functions 293
Order! Order! 294
The LIKE Operator 296
Creating Data: SELECT INTO 298
Who Can Add, Delete, and Select Data 299
Updating Data 304
Transactions 309
Deleting Data 315
Truncating a Table 318
Dropping a Table 319
Summary 319
Building a View 321
Why a View? 321
Using Views for Security 322
Encrypting View Definitions 323
Creating a View: SQL Server Management Studio 323
Creating a View Using a View 329
CREATE VIEW Syntax 335
Creating a View: a Query Editor Pane 336
Creating a View: SCHEMABINDING 337
Indexing a View 339
Summary 341
Stored Procedures and Functions 343
What Is a Stored Procedure? 344
CREATE PROCEDURE Syntax 344
Returning a Set of Records 346
Creating a Stored Procedure: Management Studio 347
Different Methods of Executing 351
Using RETURN 351
Controlling the Flow 355
Bringing It All Together 361
User- Defined Functions 363
Summary 367
T-SQL Essentials 368
Using More Than One Table 368
Variables 373
Temporary Tables 375
Aggregations 377
Grouping Data 380
HAVING 382
Distinct Values 383
Functions 383
RAISERROR 397
Error Handling 400
@@ ERROR 401
TRY. . . CATCH 402
Summary 406
Advanced T-SQL 407
Subqueries 407
The APPLY Operator 411
Common Table Expressions 414
Pivoting Data 417
Ranking Functions 419
PowerShell Within SQL Server 424
Summary 428
Triggers 429
What Is a Trigger? 429
The DML Trigger 430
CREATE TRIGGER Syntax for DML Triggers 431
Why Not Use a Constraint? 432
Deleted and Inserted Logical Tables 433
Creating a DML FOR Trigger 433
Checking Specific Columns 437
DDL Triggers 444
Summary 450
SQL Server 2008 Reporting Services 451
Reporting Services Architecture 451
Configuring Reporting Services 453
Building Your First Report Using Report Designer 460
Summary 469
Index 470

Produkt-Details

• Erscheint lt. Verlag: 29.8.2008
• Zusatzinfo: XXIV, 496 p.
• Verlagsort: Berkeley
• Sprache: englisch
• Themenwelt: Informatik ► Datenbanken ► SQL Server
• Schlagworte: Database • Database Design • Database System • Data Warehouse • Microsoft SQL Server • Relational Database • Reporting • SQL • SQL Server 2005 • SQL Server 2008 • Transact-SQL • T-SQL • XML
• ISBN-10: 1-4302-0584-9 / 1430205849
• ISBN-13: 978-1-4302-0584-5 / 9781430205845