Cover image for Professional SQL server 7.0 programming
Title:
Professional SQL server 7.0 programming
Author:
Vieira, Robert.
Personal Author:
Publication Information:
Birmingham, UK : Wrox Press, [1999]

©1999
Physical Description:
xxix, 1139 pages : illustrations ; 24 cm
General Note:
Includes index.
Language:
English
Title Subject:
Subject Term:
ISBN:
9781861002310
Format :
Book

Available:*

Library
Call Number
Material Type
Home Location
Status
Item Holds
Searching...
QA76.9.D3 V54 1999 Adult Non-Fiction Central Closed Stacks
Searching...

On Order

Summary

Summary

With a general understanding of programming principles, SQL Server Programming provides a comprehensive overview of the new areas of SQL Server, for programmers. There is an in-depth discussion of T-SQL, the query language of Microsoft SQL Server. From basic database design concepts a tutorial explains more advanced topics and specific areas including Stored Procedures and Advanced Queries. Advanced topics of performance tuning, security and a solid description of OLAP, which is built in with SQL Server, are then introduced. Data Transformation Services are explained in detail and Remote and Linked SQL Server Agents are covered. With over 1,000 pages this book covers the main topics to provide an in-depth understanding of concepts and procedures, so programmers can get the most out of SQL Server 7.0. Online discussion of the topics in this book available at Wrox's P2P site Whether you're coming from another relational database management system, upgrading your existing system, or perhaps want to add programming skills to your DBA knowledge, you'll find what you need in this book to get to grips with SQL Server 7.0 development. There is no particular programming language, so you'll


Table of Contents

Introductionp. 1
What's Covered in this Bookp. 1
Version Issuesp. 4
Conventions Usedp. 4
In Case of a Crisis...p. 5
Feedbackp. 5
Chapter 1 SQL Server 7.0 - Particulars and Historyp. 7
A Brief History of Databasesp. 8
About SQL Server 7.0p. 10
Building Database Connected Systemsp. 14
Data Access Modelsp. 20
Summaryp. 22
Chapter 2 RDBMS Basics: What Makes Up a SQL Server Database?p. 25
An Overview of Database Objectsp. 26
SQL Server Data Typesp. 36
SQL Server Identifiers for Objectsp. 42
Summaryp. 43
Chapter 3 Tools of the Tradep. 45
Books Onlinep. 46
The Client and Server Network Utilitiesp. 47
The Enterprise Managerp. 54
Data Transformation Services (DTS)p. 55
MS DTC Administrative Consolep. 56
Performance Monitorp. 56
SQL Server Profilerp. 57
The Query Analyzerp. 57
OSQLp. 66
The Service Managerp. 67
Summaryp. 68
Chapter 4 The Foundation Statements of T-SQLp. 71
Getting Started with a Basic SELECT Statementp. 72
Adding Data with the INSERT Statementp. 94
Changing What You've Got with the UPDATE Statementp. 99
The DELETE Statementp. 102
Summaryp. 103
Chapter 5 Joining Tablesp. 105
Joinsp. 106
Inner Joinsp. 107
Outer Joinsp. 115
Seeing Both Sides with Full Joinsp. 125
Cross Joinsp. 127
Exploring Alternative Syntax for Joinsp. 128
The Unionp. 131
Summaryp. 135
Chapter 6 Creating and Altering Tablesp. 137
Object Names in SQL Serverp. 137
The Create Statementp. 141
Using the GUI Toolp. 157
Summaryp. 162
Chapter 7 Constraintsp. 165
Types of Constraintsp. 166
Constraint Namingp. 168
Key Constraintsp. 169
Check Constraintsp. 176
Default Constraintsp. 177
Disabling Constraintsp. 179
Rules and Defaults - Cousins of Constraintsp. 183
Triggers for Data Integrityp. 187
Choosing What to Usep. 187
Summaryp. 189
Chapter 8 Normalization and Other Basic Design Issuesp. 191
Tablesp. 192
Time for Therapy - Keeping Your Data "Normal"p. 192
Relationshipsp. 204
Diagrammingp. 211
De-Normalizationp. 220
Beyond Normalizationp. 221
Drawing Up a Quick Examplep. 223
Summaryp. 232
Chapter 9 Speeding Performance: SQL Server Storage and Index Structuresp. 235
SQL Server Storage: Past and Presentp. 235
Understanding Indexesp. 246
Creating and Dropping Indexesp. 257
Choosing Wisely: Deciding What Index Goes Where and Whenp. 260
Maintaining Your Indexesp. 265
Summaryp. 270
Chapter 10 Viewsp. 273
Simple Viewsp. 273
More Complex Viewsp. 278
Editing Views with T-SQLp. 284
Dropping Viewsp. 284
Creating and Editing Views in EMp. 284
Auditing: Displaying Existing Codep. 288
Protecting Code: Encrypting Viewsp. 290
Summaryp. 292
Chapter 11 Writing Scripts and Batchesp. 295
Script Basicsp. 296
Batchesp. 305
Errors in Batchesp. 307
When to Use Batchesp. 307
OSQLp. 310
Summaryp. 312
Chapter 12 Code Storage: Stored Proceduresp. 315
Creating the Sproc: Basic Syntaxp. 316
Changing Stored Procedures with ALTERp. 317
Dropping Sprocsp. 317
Parameterizationp. 318
Control of Flow Statementsp. 323
Dealing with Errorsp. 344
What a Sproc Offersp. 359
Extended Stored Procedures (XPs)p. 364
Summaryp. 366
Chapter 13 Transactions and Locksp. 369
Transactionsp. 369
How the SQL Server Long Worksp. 375
Locks and Concurrencyp. 381
Setting the Isolation Levelp. 395
Dealing with Deadlocks (aka "A 1205")p. 398
Summaryp. 402
Chapter 14 Triggersp. 405
What is a Trigger?p. 406
Using Triggers for Referential Integrityp. 410
Using Triggers for Data Integrity Rulesp. 421
Other Common Uses for Triggersp. 426
Other Trigger Issuesp. 432
Performance Considerationsp. 432
Dropping Triggersp. 438
Summaryp. 438
Chapter 15 Asking a Better Question: Advanced Queriesp. 441
What is a Subquery?p. 442
Building a Nested Subqueryp. 443
Correlated Subqueriesp. 448
The EXISTS Operatorp. 454
Mixing Data Types: CAST and CONVERTp. 457
Performance Considerationsp. 460
Summaryp. 462
Chapter 16 Spreading Things Out: Distributed Queries and Transactionsp. 465
Distributed Transactionsp. 466
Distributed Queriesp. 467
Summaryp. 482
Chapter 17 SQL Cursorsp. 485
What Is A Cursor?p. 486
The Lifespan of a Cursorp. 487
Types of Cursors and Extended Declaration Syntaxp. 490
Navigating the Cursor: The FETCH Statementp. 515
Altering Data within Your Cursorp. 516
Summaryp. 518
Chapter 18 Making a Change... Bulk Copy Program and Data Transformation Servicesp. 521
Overviewp. 521
BCP Utilityp. 524
BCP Importp. 529
Format Filep. 531
BCP Exportp. 535
Data Transformation Services (DTS)p. 536
DTS Wizardp. 543
DTS Designerp. 568
Summaryp. 586
Chapter 19 Replicationp. 589
Supporting Distributed Datap. 590
Considerations when Planning for Replicationp. 590
The Publishing Metaphorp. 591
Types of Replicationp. 594
Replication Model Scenariosp. 603
Implementation Examplesp. 607
Planning for Replicationp. 609
Replication Wizardsp. 610
Enabling Publishing and Distributionp. 610
Managing Replicationp. 648
Summaryp. 654
Chapter 20 Advanced Designp. 657
More on Diagramming And Relationshipsp. 658
Logical vs. Physical Designp. 662
Dealing with File Based Informationp. 665
Sub-Categoriesp. 668
Database Reusep. 674
Summaryp. 676
Chapter 21 OLAPp. 679
The Requirements of End Usersp. 679
Dimensional Databasesp. 684
OLAP Storage Typesp. 686
Data Warehouse Conceptsp. 687
Data Transformation Servicesp. 689
Metadata and the Repositoryp. 691
Decision Support Systemsp. 692
OLAP Managerp. 692
Summaryp. 715
Chapter 22 English Query and Full-Text Searchp. 717
What is English Query?p. 718
Installing English Queryp. 720
The Architecture of an English Query Applicationp. 721
Database Structure and English Queryp. 722
Creating an English Query Projectp. 723
Testing Our Applicationp. 745
Deploying an English Query Applicationp. 753
What is Full-Text Search?p. 767
Creating and Populating a Catalogp. 769
Executing Queries Using Full-Text Searchp. 782
Maintenance and Failure Recoveryp. 789
Summaryp. 791
Chapter 23 Securityp. 793
Security Basicsp. 794
Security Optionsp. 799
User Rightsp. 807
Server and Database Rolesp. 815
Application Rolesp. 821
More Advanced Securityp. 824
Summaryp. 826
Chapter 24 Performance Tuningp. 829
Index Choicesp. 830
Client vs. Server-Side Processingp. 832
Strategic De-Normalizationp. 833
Routine Maintenancep. 834
Organizing Your Sprocs Wellp. 834
Uses for Temporary Tablesp. 836
Sometimes, it's the Little Things...p. 837
Hardware Considerationsp. 837
Trouble-shootingp. 846
Summaryp. 859
Chapter 25 Administration Overviewp. 861
Scheduling Jobsp. 862
Backup and Restore Operationsp. 883
Alertsp. 892
Full-Text Catalog Populationp. 895
Carrying Data from One Place to Anotherp. 896
Index Rebuildingp. 897
Archival of Datap. 897
Summaryp. 897
Appendix A Global Variablesp. 899
Appendix B Function Listingp. 909
Aggregate Functionsp. 910
Cursor Functionsp. 912
Date and Time Functionsp. 913
Mathematical Functionsp. 915
Metadata Functionsp. 920
Rowset Functionsp. 929
Security Functionsp. 930
String Functionsp. 933
System Functionsp. 938
Text and Image Functionsp. 944
Appendix C Tools for Our Timep. 945
ERD Toolsp. 946
Coding Toolsp. 949
Backup Utilitiesp. 950
Summaryp. 950
Appendix D Error Listingp. 953
Appendix E Access Upsizingp. 1087
Using The Wizardp. 1088
Checking The Workp. 1095
Summaryp. 1096
Indexp. 1099