Programming Microsoft SQL Server 2005,9780735619234

Programming Microsoft SQL Server 2005

by ;
Edition: 1st
ISBN13: 9780735619234
ISBN10: 0735619239
Format: Paperback
Pub. Date: 2006-06-21
Publisher(s): Microsoft Press
  • Free Shipping Icon

    This Item Qualifies for Free Shipping!*

    *Excludes marketplace orders.

List Price: $49.99

Rent Book

Select for Price
Add to Cart
There was a problem. Please try again later.

New Book

We're Sorry
Sold Out

Used Book

We're Sorry
Sold Out

eBook

We're Sorry
Not Available

Buy from our Marketplace starting at $7.14

How Marketplace Works:

  • This item is offered by an independent seller and not shipped from our warehouse
  • Item details like edition and cover design may differ from our description; see seller's comments before ordering.
  • Sellers much confirm and ship within two business days; otherwise, the order will be cancelled and refunded.
  • Marketplace purchases cannot be returned to eCampus.com. Contact the seller directly for inquiries; if no response within two days, contact customer service.
  • Additional shipping costs apply to Marketplace purchases. Review shipping costs at checkout.

Summary

This thorough, hands-on reference for developers and database administrators teaches the basics of programming custom database applications with Microsoft SQL Server 2005. It includes in-depth coverage of new programmability features - including integration with the Microsoft .NET Framework. You'll get real-world examples and see how to draw from your existing skills and knowledge to exploit new SQL Server technology, enhance your database security, and debug with Visual Studio 2005. Plus it includes extensive code samples.

Author Biography

Andrew J. Brust is the Chief, New Technology at twentysix New York, a Microsoft Gold Certified Partner specializing in business intelligence, enterprise integration and Microsoft .NET application development. Andrew is a Microsoft Regional Director for New York/New Jersey and a member of Microsoft’s Business Intelligence Partner Advisory Council.

Table of Contents

Acknowledgements xix
Introduction xxiii
Who This Book Is For xxiv
How This Books is Organized xxv
System Requirements xxvi
Using the Samples xxvii
Support for This Book xxix
Questions and Comments xxix
Part I Design Fundamentals and Core Technologies
Overview
3(8)
A Tough Act to Follow
3(1)
The Software Industry and Disruptive Change
3(1)
Industry Trends, SQL Server Features, and a Book to Show You the Way
4(5)
Programming the Server
4(1)
Application Code and SQL Server: Extending Your Database's Reach
5(2)
It's the Strategy, Stupid
7(2)
A Collaborative Effort for, and by, Developers
9(2)
Exploring the T-SQL Enhancements in SQL Server 2005
11(34)
Introducing SQL Server Management Studio
12(1)
Common Table Expressions
13(7)
Recursive Queries with CTEs
16(4)
The Pivot and Unpivot Operators
20(5)
Using Unpivot
21(1)
Dynamically Pivoting Columns
22(2)
The Apply Operator
24(1)
Top Enhancements
25(1)
Ranking Functions
26(12)
Row_Number()
26(4)
Rank()
30(2)
Dense_Rank() and Ntile(n)
32(2)
Using All the Ranking Functions Together
34(1)
Ranking over Groups: Partition By
35(3)
Exception Handling in Transactions
38(2)
New Data Types
40(1)
varchar(max) Data Type
40(1)
xml Data Type
40(1)
The Waitfor Command
41(1)
DDL Triggers and Notifications
42(1)
Snapshot Isolation
43(1)
Statement-Level Recompile
44(1)
Summary
44(1)
An Overview of SQL CLR
45(48)
Getting Started: Enabling CLR Integration
46(2)
Visual Studio/SQL Server Integration
48(5)
SQL Server Projects in Visual Studio
49(2)
Automated Deployment
51(1)
SQL CLR Code Attributes
52(1)
Your First SQL CLR Stored Procedure
53(2)
CLR Stored Procedures and Server-Side Data Access
55(4)
Piping Data with SqlDataRecord and SqlMetaData
57(2)
Deployment
59(6)
Deploying Your Assembly
59(2)
Deploying Your Stored Procedures
61(2)
Testing Your Stored Procedures
63(2)
CLR Functions
65(4)
CLR Triggers
69(5)
CLR Aggregates
74(4)
CLR Types
78(6)
Security
84(1)
Examining and Managing CLR Types in a Database
85(6)
Best Practices for SQL CLR Usage
91(1)
Summary
92(1)
XML and the Relational Database
93(46)
XML in SQL Server 2000
95(1)
The XML Data Type
96(9)
Working with the XML Data Type as a Variable
96(2)
Working with XML in Tables
98(1)
XML Schemas
99(3)
XML Indexes
102(3)
For XML Commands
105(17)
For XML Raw
106(1)
For XML Auto
106(2)
For XML Explicit
108(12)
OPENXML Enhancements in SQL Server 2005
120(1)
XML Bulk Load
121(1)
Querying XML Data Using XQuery
122(15)
XQuery Defined
122(3)
SQL Server 2005 XQuery in Action
125(9)
XML DML
134(1)
Converting a Column to XML
135(2)
Summary
137(2)
Introducing SQL Server Management Studio
139(30)
The New Management Studio Interface
140(5)
Overview of New Features
140(1)
Window Types
141(1)
Positioning a Docking Window
142(1)
Window Customization Options
143(1)
Connecting to a Database Server
144(1)
Using Object Explorer
145(4)
Object Explorer Filters
146(3)
Management Studio Solutions, Projects, and Files
149(5)
Code and Text Editor
150(2)
Track Changes Indicator
152(1)
Bookmarks
153(1)
Creating Objects
154(4)
Creating Tables
154(1)
Creating Table-Related Objects
155(1)
Creating Indexes
156(1)
Setting Properties for New Users
157(1)
Generating Scripts from Objects
158(1)
Creating Queries
158(4)
Executing Queries
159(3)
Using Templates
162(1)
Maintenance Features
163(3)
Using a Maintenance Plan
164(2)
Performance Tools
166(1)
SQL Server Profiler
166(1)
Database Engine Tuning Advisor
167(1)
Summary
167(2)
Using SQL Server Management Objects (SMO)
169(22)
What Is SMO?
170(4)
What About SQL-DMO?
170(4)
New Features in SMO
174(1)
Working SMO in Visual Studio
174(16)
Iterating Through Available Servers
177(1)
Retrieving Server Settings
178(4)
Creating Backup-and-Restore Applications
182(6)
Performing Programmatic DBCC Commands with SMO
188(2)
Summary
190(1)
SQL Server 2005 Security
191(28)
Four Themes of the Security Framework
192(1)
Secure by Design
192(1)
Secure by Default
192(1)
Secure by Deployment
192(1)
Communications
192(1)
SQL Server 2005 Security Overview
193(3)
SQL Server Logins
194(1)
Database Users
194(1)
The Guest User Account
195(1)
Authentication and Authorization
196(8)
How Clients Establish a Connection
196(1)
Password Policies
197(2)
User-Schema Separation
199(2)
Execution Context
201(3)
Encryption Support in SQL Server 2005
204(7)
Encrypting Data on the Move
205(1)
Encrypting Data at Rest
206(5)
Protecting SQL Server 2005
211(2)
Reducing the Surface Area for Attack
211(2)
How Hackers Attack SQL Server
213(2)
Direct Connection to the Internet
213(1)
Weak SA Passwords
213(1)
SQL Server Browser Service
213(1)
SQL Injection
214(1)
Intelligent Observation
214(1)
Summary
215(4)
Part II Applications Development and Reach Technologies
ADO.NET 2.0, Typed DataSet Objects, and .NET Data Binding
219(42)
A Brief History of Data Access Object Models
220(1)
DAO: A Golden Oldie
220(1)
RDO: A Thin API Wrapper
220(1)
Enter OLE DB and ADO ``Classic''
220(1)
ADO + .NET = ADO.NET
221(1)
What's New in ADO.NET 2.0?
221(2)
New Typed DataSet Members
222(1)
Other Enhancements
222(1)
Typed DataSet Enhancements
223(10)
DataTable Objects in the Typed DataSet Designer
225(1)
TableAdapter Objects
226(1)
Connection String Management
226(2)
Using the TableAdapter Configuration Wizard
228(2)
More on Queries and Parameters
230(1)
Adding Query Objects
231(1)
DBDirect Methods and Connected Use of Typed DataSet Objects
232(1)
Standalone DataTable Objects
233(1)
``Pure'' ADO.NET: Working in Code
233(11)
Querying 101
234(1)
Keeping Data Up to Date
235(2)
Responsive Uls: Executing Queries Asynchronously
237(5)
Not for Servers Only: Client-Side Bulk Copy
242(2)
It's Not Just Text: Processing XML Columns with ADO.NET and the System.Xml Namespace
244(3)
Nothing but .NET: Consuming SQL CLR UDT Data as Native .NET Objects
245(1)
Back to the Drawing Board
246(1)
Embedding SQL CLR Objects in Typed DataSet Objects
247(4)
Adding a CLR Stored Procedure to a Typed DataSet
247(2)
TVFs: Easy Living
249(1)
Aggregates and UDTs
250(1)
Windows Forms Data Binding
251(7)
DataGridView Binding
252(1)
Details View Binding
253(1)
Smart Defaults
253(1)
Binding to Stored Procedures and Views
254(1)
SQL CLR Binding
254(1)
Master-Detail and Lookup Binding
254(3)
Parameterized Query Data Binding
257(1)
Data Binding on the Web
258(3)
Typed DataSet Objects and the Web.config File
258(1)
The Data Source Configuration Wizard, the ObjectDataSource Control, and the New Data-Bound Controls
259(1)
Summary
260(1)
Debugging
261(40)
About the Sample Code
262(2)
Ad Hoc Debugging
264(12)
Creating Database Connections
264(3)
T-SQL ``Step Into'' Debugging
267(9)
Application Debugging
276(8)
Entering Debugging Mode
279(1)
Debugging SQL CLR Code
280(3)
Breakpoints and Context Switching
283(1)
Mixing SQL CLR and T-SQL Code
284(5)
Test Script Debugging
289(1)
Debugging Queries External to Visual Studio
289(3)
Remote Debugging
292(7)
Server Configuration
292(2)
Server Firewall Considerations
294(1)
Back to the Client
295(1)
Client Firewall Configuration
295(2)
Attaching to a Remote Process
297(2)
Summary
299(2)
SQL Server 2005 Native XML Web Services
301(28)
Understanding Native XML Web Services
302(1)
Comparing Native XML Web Services and SQLXML
302(1)
Exposing SQL Programmability as Web Services
303(9)
Stored Procedures and User-Defined Functions
303(1)
SQL Batch
303(1)
Reserving URLs with Http.sys
303(1)
Creating and Managing Endpoints
304(5)
Granting Endpoint Permissions
309(1)
Calling Native XML Web Service Endpoints from Client Applications
310(2)
Example Native XML Web Services Project
312(12)
Creating the SQL Server Functionality
312(4)
Registering the URL with Http.sys
316(1)
Exposing the Endpoints
316(1)
Granting Security Access to the Endpoints
317(2)
Creating the Client Application
319(5)
Best Practices for Using Native XML Web Services
324(4)
Advantages of Native XML Web Services
325(1)
Limitations of Native XML Web Services
325(1)
Security Recommendations
326(1)
Performance Recommendations
326(1)
When to Avoid Native XML Web Services
326(1)
When to Use Native XML Web Services
327(1)
Summary
328(1)
Transactions
329(42)
What Is a Transaction?
330(2)
Understanding the ACID Properties
330(2)
Local Transactions Support in SQL Server 2005
332(8)
Autocommit Transaction Mode
333(1)
Explicit Transaction Mode
333(3)
Implicit Transaction Mode
336(1)
Batch-Scoped Transaction Mode
337(1)
Using Local Transactions in ADO.NET
338(2)
Transaction Terminology
340(1)
Isolation Levels
341(6)
Isolation Levels in SQL Server 2005
341(5)
Isolation Levels in ADO.NET
346(1)
Distributed Transactions
347(17)
Distributed Transaction Terminology
348(1)
Rules and Methods of Enlistment
349(2)
Distributed Transactions in SQL Server 2005
351(1)
Distributed Transactions in the .NET Framework
352(8)
Using a Resource Manager in a Successful Transaction
360(4)
Transactions in SQL CLR (CLR Integration)
364(4)
Putting It All Together
368(1)
Summary
369(2)
SQL Server Service Broker: The New Middleware
371(22)
What Is Middleware?
371(1)
What Is SQL Server Service Broker?
372(1)
Comparing Service Broker and MSMQ
372(1)
What Is a SQL Server Service Broker Application?
373(1)
Service Broker Architecture
374(3)
Integrated Management and Operation
377(1)
Routing and Load Balancing
377(2)
Service Broker Programming in T-SQL
379(1)
A Word About Programming Languages
380(1)
Enabling Service Broker
380(1)
Defining Service Broker Objects
380(7)
The Sending Service Program
382(1)
The Receiving Service Program
383(2)
Running the Application
385(1)
A More Robust, Real-World Application
386(1)
Service Broker and Query Notification
387(4)
Service Broker's Place in the Middleware World
391(1)
Summary
392(1)
Using SQL Server 2005 Notification Services
393(34)
What Is a Notification Application?
393(4)
Notification Services Components
394(2)
Notification Services Deployment Strategies
396(1)
Working with Notification Services
397(3)
Creating Notification Applications
398(2)
A Sample Notification Application
400(24)
Flight Price Notification Sample Application
400(24)
Summary
424(3)
Developing Desktop Applications with SQL Server Express Edition
427(60)
What Is SQL Server Express Edition?
427(8)
Licensing
429(1)
Feature Review
429(2)
SQL Server 2005 Express Edition with Advanced Services
431(4)
Configuration
435(2)
Working with SQL Server Express Edition
437(14)
SQLCMD Command-Line Tool
442(4)
User Instances
446(3)
SSEUTIL
449(2)
Installing SQL Server Express Edition
451(34)
Using the Setup Wizard to Manually Install Express Edition
452(4)
Installing via Command-Line Parameters or a Configuration File
456(4)
Deploying Express Edition Applications Using a Wrapper
460(11)
Deploying Express Edition Applications Using ClickOnce
471(4)
Updating ClickOnce Deployments That Use Express Edition
475(10)
Summary
485(2)
Developing Applications with SQL Server 2005 Everywhere Edition and SQL Server Merge Replication
487(36)
SQL Everywhere Integration with SQL Server 2005
488(8)
Working with SQL Everywhere Databases in Management Studio
489(4)
Working with SQL Everywhere Data in Management Studio
493(3)
Creating a SQL Everywhere Application with SQL Server Replication and Visual Studio 2005
496(24)
Creating a Publication
498(7)
Installing and Configuring SQL Everywhere Server Components for IIS
505(3)
Creating a Subscription Using Management Studio
508(5)
Creating a Mobile Application Using Visual Studio 2005
513(7)
Summary
520(3)
Part III Reporting and Business Intelligence
Using SQL Server 2005 Integration Services
523(50)
History of Data Transfer in SQL Server
523(1)
DTS Packages
524(1)
Working with Integration Services Packages
524(8)
Control Flow
524(2)
Data Flow
526(6)
Using Integration Services Packages
532(5)
Creating Packages Using the Import And Export Wizard
532(1)
Creating Packages Using BI Development Studio
533(1)
Managing Packages Using Management Studio
533(1)
Using the Command Line to Execute and Manage Packages
533(1)
Scheduling Packages Using SQL Server Agent
534(1)
Configuring and Deploying Packages
535(1)
Overview of Programming Package Extension
536(1)
Security
537(2)
Dealing with Sensitive Information and Assets
537(1)
Considerations for Working on a Single Development Machine
538(1)
Considerations for Workgroups
538(1)
Programming Integration Services
539(15)
Programming in Visual Studio
539(1)
Loading and Executing Packages in Applications
539(1)
Creating Packages Programmatically
540(14)
Extensibility
554(18)
Script Tasks
554(1)
Custom Tasks
555(1)
Custom Components
556(10)
Script Components
566(5)
Custom Connection Managers
571(1)
Log Providers
572(1)
Foreach Enumerator
572(1)
Summary
572(1)
Basic OLAP
573(28)
Wherefore Bl?
573(2)
OLAP 101
575(3)
OLAP Vocabulary
576(1)
Dimensions, Axes, Stars, and Snowflakes
576(2)
Building Your First Cube
578(19)
Preparing Star Schema Objects
579(1)
A Tool by Any Other Name
579(2)
Creating the Project
581(1)
Adding a Data Source View
582(4)
Creating a Cube with the Cube Wizard
586(3)
Using the Cube Designer
589(2)
Using the Dimension Wizard
591(3)
Using the Dimension Designer
594(2)
Working with the Properties Window and Solution Explorer
596(1)
Processing the Cube
597(1)
Running Queries
597(2)
Summary
599(2)
Advanced OLAP
601(66)
What We'll Cover in This Chapter
602(1)
MDX in Context
602(1)
And Now a Word from Our Sponsor
603(1)
Advanced Dimensions and Measures
603(19)
Keys and Names
604(2)
Changing the All Member
606(1)
Adding a Named Query to a Data Source View
607(2)
Parent-Child Dimensions
609(5)
Member Grouping
614(1)
Server Time Dimensions
615(2)
Fact Dimensions
617(3)
Role-Playing Dimensions
620(1)
Advanced Measures
621(1)
Calculations
622(11)
Calculated Members
623(6)
Named Sets
629(1)
More on Script View
629(4)
Key Performance Indicators
633(12)
KPI Visualization: Status and Trend
634(1)
A Concrete KPI
635(2)
Testing KPIs in Browser View
637(2)
KPI Queries in Management Studio
639(4)
Other BI Tricks in Management Studio
643(2)
Actions
645(3)
Actions Simply Defined
645(1)
Designing Actions
645(2)
Testing Actions
647(1)
Partitions, Aggregation Design, Storage Settings, and Proactive Caching
648(6)
Editing and Creating Partitions
649(2)
Aggregation Design
651(1)
Partition Storage Options
652(1)
Proactive Caching
652(2)
Additional Features and Tips
654(1)
Perspectives
654(3)
Translations
657(5)
Roles
662(3)
Summary
665(2)
OLAP Application Development
667(74)
Using Excel
668(23)
Working Within Excel
669(7)
Using PivotTables and Charts in Applications and Web Pages
676(15)
Beyond OWC: Full-On OLAP Development
691(48)
MDX and Analysis Services APIs
691(1)
Moving to MDX
692(1)
Management Studio as an MDX Client
692(14)
OLAP Development with ADO MD.NET
706(12)
XMLA at Your (Analysis) Service
718(11)
Analysis Services CLR Support: Server-Side ADO MD.NET
729(10)
Summary
739(2)
Extending Your Database System with Data Mining
741(78)
Why Mine Your Data?
742(3)
Getting Started
745(7)
Preparing Your Source Data
745(2)
Creating Training and Test Samples
747(5)
Adding an SSAS Project
752(1)
Using the Data Mining Wizard and Data Mining Designer
752(30)
Creating a Mining Structure
755(1)
Creating a Mining Model
755(3)
Editing and Adding Mining Models
758(5)
Deploying and Processing Data Mining Objects
763(2)
Viewing Mining Models
765(8)
Validating and Comparing Mining Models
773(3)
Nested Tables
776(6)
Using Data Mining Extensions
782(18)
Data Mining Modeling Using DMX
782(10)
Data Mining Predictions Using DMX
792(7)
DMX Templates
799(1)
Data Mining Applied
800(17)
Data Mining and API Programming
801(1)
Using the WinForms Model Content Browser Controls
801(3)
Executing Prediction Queries with ADO MD.NET
804(1)
Model Content Queries
804(1)
ADO MD.NET and ASP.NET
805(1)
Using the Data Mining Web Controls
805(1)
Developing Managed Stored Procedures
806(2)
XMLA and Data Mining
808(2)
Data Mining and Reporting Services
810(7)
Summary
817(2)
Reporting Services
819(50)
Report Definition and Design
820(23)
Data Sources
820(2)
Report Layouts
822(4)
Report Designer
826(12)
Report Builder
838(5)
Report Definition Language
843(1)
Report Management
843(9)
Publishing
843(1)
Report Manager
844(7)
SQL Server Management Studio
851(1)
Command-Line Utilities
852(1)
Programming: Management Web Services
852(1)
Report Access and Delivery
852(10)
Delivery on Demand
852(3)
Subscriptions
855(2)
Presentation Formats
857(2)
Programming: Rendering
859(3)
Report Server Architecture
862(5)
Deployment Modes
865(1)
Extensibility
865(2)
Report Integration
867(1)
Summary
867(2)
Index 869

An electronic version of this book is available through VitalSource.

This book is viewable on PC, Mac, iPhone, iPad, iPod Touch, and most smartphones.

By purchasing, you will be able to view this book online, as well as download it, for the chosen number of days.

Digital License

You are licensing a digital product for a set duration. Durations are set forth in the product description, with "Lifetime" typically meaning five (5) years of online access and permanent download to a supported device. All licenses are non-transferable.

More details can be found here.

A downloadable version of this book is available through the eCampus Reader or compatible Adobe readers.

Applications are available on iOS, Android, PC, Mac, and Windows Mobile platforms.

Please view the compatibility matrix prior to purchase.