Beginning SQL Server 2005 for Developers: From Novice to Professional

Front Cover
Apress, Nov 21, 2006 - Computers - 536 pages

Purchase this book and you'll get the free, fully searchable eBook—a $25 value! (Details are printed inside the book.)

SQL Server 2005 will increase your programming options, productivity, analysis, and database management. If you have some basic knowledge of relational databases and want to start a career as a developer using SQL Server, then this book is your ideal first step. It explains the core jobs and roles for developing a database in both SQL Server 2000 and 2005.

This book features practical steps to help you overcome issues youre likely to encounter. You’ll learn to use SQL for querying, inserting, updating, and deleting data. You’ll also learn how to back up and restore databases for basic administration in SQL Server. Further, you’ll cover how to build a complete database, from the fundamentals of relational database design to table and index creation.

Additionally, you’ll start to program in T-SQL, SQL Servers implementation (and extension) of the SQL programming language, and you’ll come away with effective programming techniques using stored procedures and triggers.

 

What people are saying - Write a review

User Review - Flag as inappropriate

nice book

Contents

Dealing with Several Records at Once
272
Retrieving Data
273
Using SQL Server Management Studio to Retrieve Data
274
The SELECT Statement
276
Naming the Columns
278
The First Searches
279
Varying the Output Display
281
The Use of WHERE
283

The sa Login
28
Summary
29
SQL Server Management Studio
31
Examining SSMSs Options
40
Source Control Node
43
Text Editor Node
44
Query Execution Node
46
Query Results Node
48
Query Editor
51
Surface Area Configuration Tool
54
Summary
58
Database Design and Creation
60
Defining a Database
61
Databases Within SQL Server
62
master
63
model
64
msdb
65
OLAP
66
Example System Choice
67
Determining the Information to Store in the Database
69
Financial Products
70
Customers
71
External and Ignored Information
72
Creating Relationships
74
More on Foreign Keys
77
Normalization
78
Each Entity Should Have a Unique Identifier
80
Denormalization
82
Creating the Sample Database
83
Dropping the Database in SQL Server Management Studio
96
Creating a Database in a Query Pane
99
Summary
101
Security
102
Server Logins and Database Users
112
Database Roles
114
Application Roles
115
Schemas
118
Before You Can Proceed with Your Solution
120
Summary
124
Defining Tables
126
What Is a Table?
127
SQL Server Management Studio
128
Different Program Data Types
132
Columns Are More Than Simple Data Repositories
133
The Use of NULL Values
134
Creating a Table in SQL Server Management Studio
135
Defining a Table Through the Query Editor
141
Using a Template
143
Creating and Altering a Template
146
The ALTER TABLE Command
148
Defining the Remaining Tables
149
Setting a Primary Key
150
Creating a Relationship
151
Check Existing Data on Creation
156
Enforce Foreign Key Constraints
157
Using the ALTER TABLE SQL Statement
158
Summary
159
Creating Indexes and Database Diagramming
161
Types of Indexes
162
Uniqueness
164
Primary and Foreign Keys
165
Looking for a Range of Information
166
Determining What Makes a Bad Index
167
Reviewing Your Indexes for Performance
168
Creating an Index with the Table Designer
169
Indexes and Statistics
171
The CREATE INDEX Syntax
172
Template
174
SQL Code
178
Dropping an Index
181
Altering an Index in Query Editor
182
Diagramming the Database
185
The SQL Server Database Diagram Tool
186
The Default Database Diagram
187
The Database Diagram Toolbar
189
Summary
192
Database Backups Recovery and Maintenance
194
Transaction Logs
195
Backup Strategies
197
When Problems May Occur
198
Taking a Database Offline
199
Backing the Data Up
200
Backing Up the Database Using TSQL
205
Transaction Log Backup Using TSQL
213
Restoring a Database
216
Restoring Using TSQL
219
Detaching and Attaching a Database
223
Detaching and Attaching Using SQL Server Management Studio
224
Detaching and Attaching Using TSQL
229
Producing SQL Script for the Database
231
Maintaining Your Database
239
Summary
253
Working with the Data
255
The TSQL INSERT Command Syntax
256
INSERT SQL Command
257
Default Values
259
DBCC CHECKIDENT
264
Column Constraints
265
ADD CONSTRAINT
266
SET ROWCOUNT n
286
TOPn
287
TOP n PERCENT
288
Order Order
290
The LIKE Operator
292
SELECT INTO
294
Who Can Add Delete and Select Data
296
Securables
301
The UPDATE Command
302
Updating Data Within Query Editor
303
Transactions
306
BEGIN TRAN
308
Locking Data
309
Nested Transactions
311
Deleting Data
313
Using the DELETE Statement
314
Truncating a Table
316
Dropping a Table
317
Summary
318
Building a View
319
What Is a View?
320
Encrypting View Definitions
321
SQL Server Management Studio
322
Creating a View Using a View
328
CREATE VIEW Syntax
335
A Query Editor pane
337
SCHEMABINDING
338
Indexing a View
341
Summary
343
Stored Procedures
346
CREATE PROCEDURE Syntax
347
Returning a Set of Records
350
Different Methods of Executing
355
Controlling the Flow
360
IF ELSE
361
WHILE BREAK Statement
362
CASE Statement
364
Bringing It All Together
367
Summary
369
TSQL Essentials
371
Variables
377
Temporary Tables
379
Aggregations
381
SUM
382
MAXMIN
383
AVG
384
HAVING
386
Distinct Values
387
Functions
388
String
392
System Functions
398
RAISERROR
404
Error Handling
407
ERROR
408
TRY CATCH
409
Summary
414
Advanced TSQL
416
IN
418
EXISTS
419
The APPLY Operator
420
OUTER APPLY
421
Common Table Expressions
422
Recursive CTE
423
Pivoting Data
425
PIVOT
426
UNPIVOT
427
Ranking Functions
428
ROW_NUMBER
429
RANK
431
DENSE_RANK
432
NTILE
433
Using the MAX Data Type
434
Image LOB
437
Summary
439
Triggers
440
The DML Trigger
441
CREATE TRIGGER Syntax for DML triggers
442
Why Not Use a Constraint?
444
Creating a DML FOR Trigger
445
Checking Specific Columns
449
Using COLUMNS_UPDATED
454
DDL Triggers
457
DDL_DATABASE_LEVEL_EVENTS
458
Dropping a DDL trigger
460
Summary
463
SQL Server 2005 Reporting Services
464
Reporting Services Architecture from 5000 Feet
465
A Closer Look
466
The Application Layer
467
The Server Layer
469
The Data Layer
471
Building a Report from Scratch
475
DataRelated Elements
476
Reports Structure
477
Summary
486
Glossary of Terms
487
INDEX
499
Copyright

Other editions - View all

Common terms and phrases

Popular passages

Page 23 - Microsoft SQL Server 2005 Setup Completing Microsoft SQL Server 2005 Setup Setup has finished configuration of Microsoft SQL Server 2005 Refer to the setup error logs for information describing any failure(s) that occurred during setup. Click Finish to exit the installation wizard. To minimize the server surface area of SQL Server 2005, some features and services are disabled by default for new installations. To configure the surface area of SQL Server, use the Surface Area Configuration tool. Analysis...
Page 18 - Instance Name You can install a default instance or you can specify a named instance. Provide a name for the instance. For a default installation, click Default instance and click Next. To upgrade an existing default instance, click Default instance. To upgrade an existing named instance select Named instance and specify the instance name. G: Default instance O Named instance r Help Figure 1-6.

About the author (2006)

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.

Bibliographic information