Building Microsoft SQL Server 7 Applications With Com

by Purba, Sanjiv

• ISBN13: 9780471192336
• ISBN10: 0471192333
• Format: Paperback
• Copyright: 1999-02-01
• Publisher: John Wiley & Sons Inc

Summary

Total coverage of the latest SQL Server release, from a leading SQL expert. With the release of Microsoft SQL Server 7, client/server developers must master the latest features and SQL2s new architecture-vastly different from earlier editions. To build full-scale business applications, they will also need to learn how to harness the power of COM distributed objects and MTS. This book will ease the pressure, providing a complete tutorial on each developmental phase, from planning and design to prototyping, coding, and reporting.

Author Biography

Sanjiv Purba is a Senior Manager with Deloitte and Touche consulting. He is a well-known speaker and writer on database and application development and on project management techniques.

Table of Contents

Preface	xi
Part One Quick Start	1	(142)
Chapter 1 Getting Started	3	(36)
The Road Map	4	(1)
What You Get	5	(2)
What's New in SQL Server 7	7	(6)
Capacity	7	(1)
Scalability	7	(1)
Interoperability	7	(1)
Integration	8	(1)
Ease of Use	9	(1)
Performance	9	(1)
Architecture	10	(1)
Administration	11	(1)
Functionality	11	(1)
Data Warehousing, Replication, and eCommerce	12	(1)
Generally Painless Installation	13	(11)
Follow the Prompts	14	(1)
Installation Options	15	(2)
After Successful Installation	17	(1)
Upgrading from a Previous Version of SQL Server	18	(2)
Installing Microsoft Visual Database Tools	20	(1)
Starting Microsoft SQL Server	20	(1)
Stopping Microsoft SQL Server	21	(1)
Inspecting the Errorlog File	21	(3)
Signing On	24	(1)
Administrative Applications	24	(8)
Microsoft SQL Server Service Manager	25	(3)
SQL Server Enterprise Manager	28	(2)
SQL Server Client Network Utility	30	(1)
SQL Performance Monitor	31	(1)
SQL Server Profiler	31	(1)
Getting Things Done	32	(2)
Transact-SQL: Database Programming Dialect	32	(1)
Finding Out Who's Using the System	33	(1)
Finding Your Way around the Microsoft SQL Server Environment	33	(1)
Limitations and Ceilings	34	(2)
New Wizards	36	(1)
Hardware Requirements	37	(1)
Test Environment	37	(1)
Summary	38	(1)
Chapter 2 Learning the Basics of N-Tier Applications and COM	39	(20)
Defining Tiered Architectures	40	(5)
Defining a Transaction	45	(1)
Components	45	(6)
Component Object Model	46	(2)
DCOM versus CORBA	48	(1)
ActiveX	49	(2)
Microsoft Transaction Server	51	(6)
A Definition	51	(1)
Why MTS	52	(1)
MTS Architecture	52	(5)
COM+	57	(1)
Summary	57	(2)
Chapter 3 Primer for Building an Application: Part A	59	(36)
Where to Start?	59	(9)
Prepare for Development	59	(4)
GUI versus Batch	63	(1)
Design the Application	63	(5)
Scripting the Tables	68	(6)
A Reusable Script Template	69	(1)
Creating Tables in a Database	70	(3)
Batches and the Go Command	73	(1)
Building the Physical Database and Transaction Logs	74	(1)
Building a Database Environment Using Wizards	75	(8)
A Note about the Database Naming Process through the Create Database Wizard	77	(1)
Using Scripts to Manage Databases in Pre-SQL Server 7	78	(3)
Dropping Databases	81	(1)
Useful Help System Procedures	81	(1)
Completing the Script File to Create the Address Database	82	(1)
Go Modifying the Table Creation Script	83	(10)
Adding Data to User-Defined Tables	86	(5)
Retrieving the Data	91	(2)
Summary	93	(2)
Chapter 4 Primer for Building an Application: Part B	95	(34)
The SQL Server Environment	95	(5)
SQL Server Databases and Objects	96	(4)
Data Validation through Column Constraints	100	(15)
Identity	101	(3)
Primary Key Constraints	104	(2)
Foreign Key and References Constraints	106	(5)
Default Constraints	111	(4)
Complex Database Objects	115	(3)
Joins	115	(1)
Subqueries	116	(1)
Views	116	(2)
Leveraging Stored Procedures	118	(10)
Stored Procedure Syntax	119	(4)
Leveraging Triggers	123	(2)
Getting Help	125	(3)
Debugging Your Application	128	(1)
Summary	128	(1)
Chapter 5 Leveraging the Project Life Cycle to Build N-Tier, COM Database Applications	129	(14)
A Generic Project Life Cycle and Development Methodology	130	(4)
Subactivities within the SQL Server Environment	131	(3)
Approach for Building an N-Tier, COM Database Application	134	(1)
Selecting a Project Development Methodology	134	(2)
Deploying a Project Development Methodology in an Organization	136	(1)
Allowing for Change	137	(3)
The Internet and the World Wide Web	137	(1)
Intranets	138	(1)
Object-Oriented Techniques	138	(1)
Packages	138	(1)
Frameworks	139	(1)
Second-Generation Client/Server Tools	139	(1)
Hardware Advances (64-Bit Processing)	140	(1)
Project Roles	140	(1)
Development Standards	140	(2)
Summary	142	(1)
Part Two Building the Application Framework	143	(104)
Chapter 6 Getting Physical	145	(30)
Memory Utilization	147	(1)
Understanding Physical SQL Server Architecture	148	(3)
Preparing the Environment for a New Database	151	(2)
Preparing the Environment in SQL Server 7	151	(1)
Preparing the Environment in SQL Server 6.5	151	(2)
Altering Device Sizes	153	(1)
Transaction Logs	153	(3)
Performance Impacts	155	(1)
Clearing Transaction Logs	155	(1)
Environment Options	156	(11)
Configuration Options	156	(1)
Configuration Values for SQL Server 6.5	157	(7)
Understanding the Configuration Options	164	(3)
Configurable Database Options	167	(3)
Database Option Maintenance Dialogue	167	(1)
sp_dboption Stored Procedure	167	(1)
Understanding the Database Options	170	(1)
Set Command	171	(2)
Summary	173	(2)
Chapter 7 Defining and Validating Your Data	175	(22)
Built-in Datatypes	177	(5)
Creating and Manipulating Datatypes	182	(4)
Binding Defaults to User-Defined Datatypes	183	(1)
User-Defined Datatypes	183	(3)
Data Validation	186	(10)
Constraints	187	(7)
Rules	194	(2)
Summary	196	(1)
Chapter 8 Using the Data Definition Language	197	(18)
Building Database Objects with the Data Definition Language	197	(5)
Creating Databases	198	(3)
Dropping Databases	201	(1)
Altering Databases	201	(1)
Adding/Removing Databases to SQL Server	201	(1)
Defining Database Objects	202	(2)
Creating Tables	202	(1)
Altering Tables	203	(1)
Dropping Tables	203	(1)
Using Indexes	204	(7)
Building Indexes	206	(3)
Create Index Wizard	209	(2)
Index Tuning Wizard	211	(1)
Using the Temporary Database	211	(3)
Summary	214	(1)
Chapter 9 Using the Data Manipulation Language	215	(18)
The Essential DML Commands	216	(15)
Setting up a Test Environment	216	(4)
Adding Test Data to the Test Environment	220	(6)
Select	226	(2)
Update	228	(2)
Delete	230	(1)
Truncate	230	(1)
Summary	231	(2)
Chapter 10 Using the Data Control Language--Controlling Access to SQL Server	233	(14)
Connection Options	235	(1)
Roles	235	(1)
Adding Login Name	236	(4)
Using the Login Wizard	236	(2)
Using System Procedures	238	(2)
Permissions	240	(6)
Managing Users	240	(2)
Managing Permissions	242	(4)
Summary	246	(1)
Part Three Incorporating Advanced Features	247	(94)
Chapter 11 Writing Stored Procedures (Transact-SQL Programs)	249	(38)
The Future of Stored Procedures	250	(1)
The Benefits Offered by Stored Procedures	250	(1)
Concerns about Stored Procedures	251	(1)
Stored Procedure Considerations	251	(4)
Change Management	251	(1)
Portability and Interoperability	252	(1)
ANSI SQL Standards	253	(2)
N-Tier Architecture and Partitioning Decisions	255	(1)
Additional Skill Sets	255	(1)
Using Stored Procedures	255	(17)
Stored Procedure Syntax	257	(1)
Steps for Working with Stored Procedures	258	(1)
Some Stored Procedure Examples	259	(4)
Variable Declarations and Manipulation	263	(4)
Control-of-Flow Commands	267	(4)
Generating Errors with Raiserror	271	(1)
Arithmetic Operators	271	(1)
Boolean Operators	271	(1)
Stored Procedure Examples	272	(14)
Summary	286	(1)
Chapter 12 Leveraging SQL Server Functions	287	(18)
Creating a Test Environment	287	(2)
String Functions	289	(3)
Aggregate Functions	292	(1)
Mathematical Functions	292	(2)
Date/Time Functions	294	(1)
Convert	294	(3)
System Functions	295	(2)
System Procedures	297	(7)
Help Functions	298	(1)
Environment Information Functions	299	(1)
Administrative Functions	300	(1)
Object Maintenance Functions	301	(1)
Indexes and Keys	302	(1)
Roles, Users, Security, and Permission Functions	302	(1)
Changing Defaults and Options	303	(1)
Statistics Functions	304	(1)
Performance Functions	304	(1)
Summary	304	(1)
Chapter 13 Triggering Events	305	(16)
Trigger Syntax	306	(14)
Maintaining Referential Integrity	310	(5)
Raiserror	315	(1)
Including Rollbacks in Triggers	316	(2)
Delete Trigger	318	(1)
Trigger Limitations	319	(1)
Summary	320	(1)
Chapter 14 Using Advanced Data Manipulation Techniques	321	(20)
Setting Up the Test Database and Test Data	323	(1)
Join Operations	324	(5)
Join Performance	326	(1)
Equi-Joins and Natural Joins	326	(3)
Subqueries	329	(1)
Views	330	(5)
Create View Wizard	333	(2)
Server-Side Cursors	335	(5)
Summary	340	(1)
Part Four Build Advanced Applications Using COM, Internet/Intranet, Visual Basic, and MTS	341	(106)
Chapter 15 Building a COM-Based, N-Tier Application	343	(56)
Building 3-Tiered Distributed Applications	343	(2)
Distributed 3-Tiered Architecture	343	(2)
N-Tier Architecture Challenges	345	(1)
Connection to Remote Servers	345	(1)
Proper Redirection of Function Calls	345	(1)
Handling Return Data	345	(1)
Error Handling	345	(1)
Performance Issues for Remote Function Calls	345	(1)
Reporting and Printing Functions	346	(1)
Scalability and Security	346	(1)
The Solution	346	(4)
Connection to Remote Servers	346	(1)
Proper Redirection of Function Calls	347	(1)
Handling Return Data	347	(1)
Error Handling	348	(1)
Performance Issues for Remote Function Calls	349	(1)
Reporting Functions	349	(1)
Building a 3-Tiered Application	350	(47)
Business Requirements	350	(1)
Application Functions	350	(1)
Application Data	350	(1)
Business Rules	351	(1)
Business Object Model	352	(1)
Application Layers	352	(3)
The Data Model	355	(1)
Application Framework	356	(1)
Code Decode Component	356	(1)
Fatal Error-Handling Component	356	(1)
Data Service Component	357	(1)
Business Error Component	357	(1)
Common Components	357	(1)
Getting Started	357	(1)
Creating a Project	357	(5)
The GUI Forms and GUI Classes	362	(4)
The MTS-Specific Component	366	(25)
MTS Application Packaging and Security	391	(3)
The Application	394	(1)
How the Application Gets Connected to the SQL Server Database	394	(2)
Move to the Web	396	(1)
Summary	397	(2)
Chapter 16 Developing the User Interface	399	(14)
User Interface Development	399	(7)
Defining Architecture	399	(2)
Designing the User Interface	401	(1)
Building the Process	401	(1)
Interacting with Data	402	(4)
Locking	406	(1)
Locking Levels	406	(2)
Isolation Levels and Locking Modes	408	(1)
Manipulating Locks	408	(1)
Understanding Deadlocks	409	(1)
Pessimistic versus Optimistic Locking	410	(1)
Transactions	410	(2)
Summary	412	(1)
Chapter 17 Connecting SQL Server to the Internet	413	(34)
Microsoft's Way of Data Access	413	(3)
VBSQL	413	(1)
ODBC	414	(1)
DAO	414	(1)
RDO	414	(1)
OLE DB	414	(1)
ADO	415	(1)
RDS	415	(1)
Getting SQL Server Data to the Internet Options	416	(1)
High-Performance Remote Data Access	417	(1)
Publishing Data on the Web Using ASP	417	(10)
Technology Architecture	417	(1)
Software Requirements	417	(1)
Building the Application	417	(10)
HTML with Java Server Pages and Java Servlets	427	(18)
SQL Server Web Assistant Wizard	427	(12)
Connecting to SQL Server Using IBM WebSphere	439	(2)
WebStudio's Database Access Wizard	441	(4)
Summary	445	(2)
Part Five Deploying Production-Ready N-tier Applications and System Administration	447	(48)
Chapter 18 Testing, Optimization, Debugging, and Implementation	449	(14)
Learning from SQL Server Experiences	449	(2)
Testing	451	(8)
Testing Approach	451	(8)
Optimization and Performance	459	(2)
Implementation	461	(1)
Summary	462	(1)
Chapter 19 System Administration through the Visual Tools and Wizards	463	(20)
Visual Tools	463	(15)
Enterprise Manager	464	(9)
Query Analyzer	473	(3)
Service Manager	476	(1)
MSDTC Administrative Console	476	(1)
Client Network Utility	477	(1)
Server Network Utility	477	(1)
Uninstall SQL Server 7	477	(1)
Administrative Wizards	478	(3)
Backup Wizard	479	(1)
Create Alert Wizard	480	(1)
Create Job Wizard	480	(1)
Create Trace Wizard	480	(1)
Database Maintenance Plan Wizard	480	(1)
Index Tuning Wizard	480	(1)
Summary	481	(2)
Chapter 20 Maintaining Your N-Tier COM Applications	483	(12)
Maintenance and Operational Activities	483	(1)
SQL Server Utilities	484	(10)
ISQL	485	(1)
Bulk Copy Program	486	(3)
Database Consistency Checker	489	(4)
Distributed Applications Considerations	493	(1)
Summary	494	(1)
Appendix A Relational Data Modeling, Normal Forms, and Other Models	495	(2)
Bibliography	497	(2)
Index	499