Beginning SQL Server 2005 for Developers

From Novice to Professional

By: Robin Dewson

Write A Review

Paperback | 1 January 2006 | Edition Number 2

At a Glance

Paperback
536 Pages

Edition Type
Revised

Dimensions(cm)
23.5 x 19.05 x 2.74

Paperback

$69.99

or 4 interest-free payments of $17.50 with

Aims to ship in 7 to 10 business days

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.

Shipping

	Standard Shipping	Express Shipping
Metro postcodes:	$9.99	$14.95
Regional postcodes:	$9.99	$14.95
Rural postcodes:	$9.99	$14.95

Orders over $79.00 qualify for free shipping.

How to return your order

At Booktopia, we offer hassle-free returns in accordance with our returns policy. If you wish to return an item, please get in touch with Booktopia Customer Care.

Additional postage charges may be applicable.

Defective items

If there is a problem with any of the items received for your order then the Booktopia Customer Care team is ready to assist you.

For more info please visit our Help Centre.

You Can Find This Book In

Non-Fiction Computing & I.T.Computer Programming & Software Development Database Programming Databases Database Software SQL Server & MS SQL Software Engineering

About the Author	p. xvii
About the Technical Reviewer	p. xix
Acknowledgments	p. xxi
Introduction	p. xxiii
SQL Server 2005 Overview and Installation	p. 1
Why SQL Server 2005?	p. 2
Evolution of SQL Server	p. 3
Hardware Requirements	p. 4
CPU	p. 4
Memory	p. 5
Hard Disk Space	p. 5
Operating System Requirements	p. 5
The Example	p. 6
Installation	p. 6
A Standard Installation	p. 6
Preparing to Install	p. 7
Security	p. 18
Services Accounts	p. 18
Looking at the Authentication Mode	p. 18
The sa Login	p. 22
Summary	p. 23
SQL Server Management Studio	p. 25
A Quick Overview of SSMS	p. 25
Examining SSMS's Options	p. 34
Environment Node	p. 34
Source Control Node	p. 37
Text Editor Node	p. 38
Query Execution Node	p. 40
Query Results Node	p. 42
Query Editor	p. 45
Surface Area Configuration Tool	p. 47
Summary	p. 51
Database Design and Creation	p. 53
Defining a Database	p. 54
Databases Within SQL Server	p. 55
Master	p. 56
Tempdb	p. 56
Model	p. 57
Msdb	p. 58
AdventureWorks/AdventureWorksDW	p. 58
Choosing the Database System Type	p. 58
OLTP	p. 58
OLAP	p. 59
Example System Choice	p. 60
Gathering the Data	p. 60
Determining the Information to Store in the Database	p. 62
Financial Products	p. 63
Customers	p. 64
Customer Addresses	p. 64
Shares	p. 64
Transactions	p. 64
External and Ignored Information	p. 65
Building Relationships	p. 65
Using Keys	p. 65
Creating Relationships	p. 67
More on Foreign Keys	p. 70
Normalization	p. 71
Each Entity Should Have a Unique Identifier	p. 73
Only Store Information That Directly Relates to That Entity	p. 73
Avoid Repeating Values or Columns	p. 73
Normalization Forms	p. 73
Denormalization	p. 75
Creating the Sample Database	p. 76
Creating a Database in SQL Server Management Studio	p. 76
Dropping the Database in SQL Server Management Studio	p. 89
Creating a Database in a Query Pane	p. 92
Summary	p. 94
Security	p. 95
Logins	p. 95
Server Logins and Database Users	p. 105
Roles	p. 105
Fixed Server Roles	p. 105
Database Roles	p. 107
Application Roles	p. 108
Schemas	p. 111
Before You Can Proceed with Your Solution	p. 113
Summary	p. 117
Defining Tables	p. 119
What Is a Table?	p. 120
Defining a Table: SQL Server Management Studio	p. 121
Different Table Data Types	p. 121
Different Program Data Types	p. 125
Columns Are More Than Simple Data Repositories	p. 126
Default Values	p. 126
Generating IDENTITY Values	p. 126
The Use of NULL Values	p. 127
Why Define a Column to Allow NULL?	p. 127
Image and Large Text Storage in SQL Server	p. 127
Creating a Table in SQL Server Management Studio	p. 128
Defining a Table Through the Query Editor	p. 134
Defining a Table: Using a Template	p. 136
Creating and Altering a Template	p. 139
The ALTER TABLE Command	p. 141
Defining the Remaining Tables	p. 142
Setting a Primary Key	p. 143
Creating a Relationship	p. 144
Check Existing Data on Creation	p. 148
Enforce Foreign Key Constraints	p. 149
Delete Rule/Update Rule	p. 149
Using the ALTER TABLE SQL Statement	p. 150
Summary	p. 151
Creating Indexes and Database Diagramming	p. 153
What Is an Index?	p. 153
Types of Indexes	p. 154
Uniqueness	p. 156
Determining What Makes a Good Index	p. 156
Using Low-Maintenance Columns	p. 156
Primary and Foreign Keys	p. 157
Finding Specific Records	p. 157
Using Covering Indexes	p. 157
Looking for a Range of Information	p. 158
Keeping the Data in Order	p. 158
Determining What Makes a Bad Index	p. 159
Using Unsuitable Columns	p. 159
Choosing Unsuitable Data	p. 159
Including Too Many Columns	p. 159
Including Too Few Records in the Table	p. 159
Reviewing Your Indexes for Performance	p. 160
Creating an Index	p. 160
Creating an Index with the Table Designer	p. 161
Indexes and Statistics	p. 163
The CREATE INDEX Syntax	p. 164
Creating an Index in Query Editor: Template	p. 166
Creating an Index in Query Editor: SQL Code	p. 170
Dropping an Index	p. 173
Altering an Index in Query Editor	p. 174
Diagramming the Database	p. 176
Database Diagramming Basics	p. 176
The SQL Server Database Diagram Tool	p. 177
The Default Database Diagram	p. 178
The Database Diagram Toolbar	p. 180
Summary	p. 183
Database Backups, Recovery, and Maintenance	p. 185
Transaction Logs	p. 186
Backup Strategies	p. 188
When Problems May Occur	p. 189
Taking a Database Offline	p. 190
Backing the Data Up	p. 191
Backing Up the Database Using T-SQL	p. 196
Transaction Log Backup Using T-SQL	p. 204
Restoring a Database	p. 207
Restoring Using SQL Server Management Studio	p. 207
Restoring Using T-SQL	p. 210
Detaching and Attaching a Database	p. 214
Detaching and Attaching Using SQL Server Management Studio	p. 215
Detaching and Attaching Using T-SQL	p. 220
Producing SQL Script for the Database	p. 222
Maintaining Your Database	p. 230
Creating a Database Maintenance Plan	p. 230
Summary	p. 243
Working with the Data	p. 245
The T-SQL INSERT Command Syntax	p. 246
INSERT SQL Command	p. 247
Default Values	p. 249
Using NULL Values	p. 249
DBCC CHECKIDENT	p. 254
Column Constraints	p. 255
ADD CONSTRAINT	p. 256
Dealing with Several Records at Once	p. 262
Inserting Several Records in a Query Batch	p. 262
Retrieving Data	p. 263
Using SQL Server Management Studio to Retrieve Data	p. 264
The SELECT Statement	p. 266
Naming the Columns	p. 268
The First Searches	p. 269
Varying the Output Display	p. 271
Limiting the Search: The Use of WHERE	p. 273
SET ROWCOUNT n	p. 276
TOP n	p. 277
TOP n PERCENT	p. 278
String Functions	p. 278
Order! Order!	p. 280
The LIKE Operator	p. 282
Creating Data: SELECT INTO	p. 284
Who Can Add, Delete, and Select Data	p. 286
Securables	p. 291
Updating Data	p. 291
The UPDATE Command	p. 292
Updating Data Within Query Editor	p. 293
Transactions	p. 296
BEGIN TRAN	p. 298
COMMIT TRAN	p. 298
ROLLBACK TRAN	p. 298
Locking Data	p. 299
Updating Data: Using Transactions	p. 299
Nested Transactions	p. 301
Deleting Data	p. 303
DELETE Syntax	p. 303
Using the DELETE Statement	p. 304
Truncating a Table	p. 306
Dropping a Table	p. 307
Summary	p. 308
Building a View	p. 309
What Is a View?	p. 310
Using Views for Security	p. 310
Encrypting View Definitions	p. 311
Creating a View: SQL Server Management Studio	p. 312
Creating a View Using a View	p. 318
CREATE VIEW Syntax	p. 324
Creating a View: A Query Editor pane	p. 326
Creating a View: SCHEMABINDING	p. 327
Indexing a View	p. 330
Summary	p. 332
Stored Procedures	p. 335
What Is a Stored Procedure?	p. 335
CREATE PROCEDURE Syntax	p. 336
Returing a Set of Records	p. 339
Creating a Stored Procedure: Management Studio	p. 339
Different Methods of Executing	p. 344
No EXEC	p. 344
With EXEC	p. 344
Using RETURN	p. 344
Controlling the Flow	p. 348
IF...ELSE	p. 349
BEGIN...END	p. 349
WHILE...BREAK Statement	p. 350
CASE Statement	p. 352
Bringing It All Together	p. 355
Summary	p. 357
T-SQL Essentials	p. 359
Using More Than One Table	p. 359
Variables	p. 365
Temporary Tables	p. 367
Aggregations	p. 369
COUNT/COUNT_BIG	p. 369
SUM	p. 370
MAX/MIN	p. 371
AVG	p. 372
GROUP BY	p. 372
HAVING	p. 374
Distinct Values	p. 375
Functions	p. 376
Date and Time	p. 376
String	p. 380
System Functions	p. 386
RAISERROR	p. 391
Error Handling	p. 394
@@ERROR	p. 395
TRY...CATCH	p. 396
Summary	p. 401
Advanced T-SQL	p. 403
Subqueries	p. 403
IN	p. 405
EXISTS	p. 406
The APPLY Operator	p. 407
CROSS APPLY	p. 407
OUTER APPLY	p. 408
Common Table Expressions	p. 409
Recursive CTE	p. 410
Pivoting Data	p. 412
PIVOT	p. 413
UNPIVOT	p. 414
Ranking Functions	p. 415
ROW_NUMBER	p. 416
RANK	p. 418
DENSE_RANK	p. 419
NTILE	p. 420
Using the MAX Data Type	p. 421
Image LOB	p. 424
Summary	p. 426
Triggers	p. 427
What Is a Trigger?	p. 427
The DML Trigger	p. 428
CREATE TRIGGER Syntax for DML triggers	p. 429
Why Not Use a Constraint?	p. 431
Deleted and Inserted Logical Tables	p. 431
Creating a DML FOR Trigger	p. 432
Checking Specific Columns	p. 436
Using UPDATE()	p. 436
Using COLUMNS_UPDATED()	p. 441
DDL Triggers	p. 444
DDL_DATABASE_LEVEL_EVENTS	p. 445
Dropping a DDL trigger	p. 447
EVENTDATA()	p. 447
Summary	p. 450
SQL Server 2005 Reporting Services	p. 451
What Is Reporting Services?	p. 451
Reporting Services Architecture from 5000 Feet	p. 452
Reporting Services Architecture: A Closer Look	p. 453
The Application Layer	p. 454
The Server Layer	p. 456
The Data Layer	p. 458
Building Your First Report Using Report Wizard	p. 458
Building a Report from Scratch	p. 462
Data-Related Elements	p. 463
Report Items	p. 463
Reports Structure	p. 464
Summary	p. 472
Glossary of Terms	p. 473
Index	p. 485
Table of Contents provided by Ingram. All Rights Reserved.

Beginning SQL Server 2005 for Developers

From Novice to Professional

At a Glance

Paperback

Shipping

How to return your order

Defective items

You Can Find This Book In

More in Databases

The Crypto Trader

How anyone can make money trading Bitcoin and other cryptocurrencies

Microsoft Power BI For Dummies

For Dummies (Computer/Tech)

Hands-On Machine Learning with Scikit-Learn, Keras, and TensorFlow 3e

Concepts, Tools, and Techniques to Build Intelligent Systems

Feature Engineering for Machine Learning

Principles and Techniques for Data Scientists

R for Data Science

Import, Tidy, Transform, Visualize, and Model Data

Data Without Labels

Python All-in-One For Dummies

3rd Edition

SPSS Survival Manual

7th Edition - A step by step guide to data analysis using IBM SPSS

Data Science from Scratch

First Principles with Python

SQL Cookbook

Query Solutions and Techniques for All SQL Users 2nd Edition

Social Research Methods

4th Edition

Fundamentals of Data Engineering

Plan and Build Robust Data Systems

Web Scraping with Python

3rd Edition - Data Extraction from the Modern Web

Spark : The Definitive Guide

Big Data Processing Made Simple

Microsoft 365 Access For Dummies

Data Pipelines Pocket Reference

Moving and Processing Data for Analytics

Data Analytics for Accounting ISE

3rd Edition

Deciphering Data Architectures

Choosing Between a Modern Data Warehouse, Data Fabric, Data Lakehouse, and Data Mesh

Designing Data-Intensive Applications

Big Ideas Behind Reliable, Scalable, and Maintainable Systems

Data Governance: The Definitive Guide

People, Processes, and Tools to Operationalize Data Trustworthiness

Deep Learning for Coders with fastai and PyTorch

AI Applications Without a PhD

Data Management at Scale

Modern Data Architecture with Data Mesh and Data Fabric

High Performance MySQL

Proven Strategies for Running MySQL at Scale

Deep Learning from Scratch

Building with Python from First Principles

This product is categorised by