Professional SQL Server 2000 programming:
Gespeichert in:
| 1. Verfasser: | |
|---|---|
| Format: | Buch |
| Sprache: | English |
| Veröffentlicht: |
Birmingham u.a.
Wrox Press
2000
|
| Schriftenreihe: | Programmer to programmer
|
| Schlagworte: | |
| Online-Zugang: | Inhaltsverzeichnis |
| Beschreibung: | XXIII, 1391 S. Ill. |
| ISBN: | 1861004486 |
Internformat
MARC
| LEADER | 00000nam a2200000 c 4500 | ||
|---|---|---|---|
| 001 | BV013649731 | ||
| 003 | DE-604 | ||
| 005 | 20021129 | ||
| 007 | t | ||
| 008 | 010323s2000 xxua||| |||| 00||| eng d | ||
| 020 | |a 1861004486 |9 1-861004-48-6 | ||
| 035 | |a (OCoLC)45660168 | ||
| 035 | |a (DE-599)BVBBV013649731 | ||
| 040 | |a DE-604 |b ger |e rakwb | ||
| 041 | 0 | |a eng | |
| 044 | |a xxu |c US | ||
| 050 | 0 | |a QA76.9.D3 | |
| 082 | 0 | |a 005.75/85 |2 21 | |
| 084 | |a ST 271 |0 (DE-625)143639: |2 rvk | ||
| 100 | 1 | |a Vieira, Robert |e Verfasser |4 aut | |
| 245 | 1 | 0 | |a Professional SQL Server 2000 programming |c Robert Vieira |
| 264 | 1 | |a Birmingham u.a. |b Wrox Press |c 2000 | |
| 300 | |a XXIII, 1391 S. |b Ill. | ||
| 336 | |b txt |2 rdacontent | ||
| 337 | |b n |2 rdamedia | ||
| 338 | |b nc |2 rdacarrier | ||
| 490 | 0 | |a Programmer to programmer | |
| 630 | 0 | 4 | |a SQL server |
| 650 | 7 | |a Oracle |2 gtt | |
| 650 | 7 | |a SQL |2 gtt | |
| 650 | 4 | |a Database management | |
| 650 | 0 | 7 | |a SQL |0 (DE-588)4134010-3 |2 gnd |9 rswk-swf |
| 650 | 0 | 7 | |a Programmierung |0 (DE-588)4076370-5 |2 gnd |9 rswk-swf |
| 650 | 0 | 7 | |a SQL Server 2000 |0 (DE-588)4623337-4 |2 gnd |9 rswk-swf |
| 689 | 0 | 0 | |a SQL Server 2000 |0 (DE-588)4623337-4 |D s |
| 689 | 0 | |5 DE-604 | |
| 689 | 1 | 0 | |a SQL |0 (DE-588)4134010-3 |D s |
| 689 | 1 | 1 | |a Programmierung |0 (DE-588)4076370-5 |D s |
| 689 | 1 | |5 DE-604 | |
| 856 | 4 | 2 | |m HBZ Datenaustausch |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=009325770&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |3 Inhaltsverzeichnis |
| 999 | |a oai:aleph.bib-bvb.de:BVB01-009325770 | ||
Datensatz im Suchindex
| _version_ | 1804128459592564736 |
|---|---|
| adam_text | Titel: Professional SQL server 2000 programming
Autor: Vieira, Robert
Jahr: 2000
Table of Contents
Introduction__1
Chapter 1: SQL Server 2000 - Particulars and History_9
A Brief History of Databases 10
About SQL Server 2000 12
Which Edition Should You Use? 14
Hardware and OS Requirements 17
Building Database Connected Systems 17
Single Tier (Host) Systems 18
2-Tier Architecture (Client-Server) 19
Client-Centric 19
Server-Centric 20
Three-Tier 21
n-Tier 22
About .NET 23
Data Access Models 24
Microsoft Certification 26
Summary 26
Chapter 2: RPBMS Basics: What Makes Up a SOL Server Database?_29
An Overview of Database Objects 30
The Database Object 30
The Transaction Log 33
The Most Basic Database Object - Table 34
Filegroups 36
Diagrams 37
Views 38
Stored Procedures 39
User Defined Functions 40
Users and Roles 40
Rules 40
Defaults 41
User-Defined Data Types 41
Full-Text Catalogs 41
SQL Server Data Types 42
NULL Data 46
SQL Server Identifiers for Objects 47
Summary 48
Table of Contents
Chapter 3: Tools of the Trade_51
Books Online 52
The Client and Server Network Utilities 53
The Protocols 54
On to the Client 57
The Enterprise Manager 59
Data Transformation Services (DTS) 60
Bulk Copy Program (bcp) 60
The MS DTC Administrative Console 61
SQL Server Profiler 61
The Query Analyzer 61
Getting Connected 62
Making the Connection 65
OSQL 71
The Service Manager 71
sqlmaint.exe 72
Summary 72
Chapter 4: The Foundation Statements of T-SOL___Z5
Getting Started with a Basic SELECT Statement 76
The SELECT Statement and FROM Clause 78
The WHERE Clause 79
ORDER BY 83
Aggregating Data Using the GROUP BY Clause 85
Placing Conditions on Groups with the HAVING Clause 94
Outputting XML Using the FOR XML Clause 98
Making Use of Hints Using the OPTION Clause 98
The DISTINCT and ALL Predicates 97
Adding Data with the INSERT Statement 99
The INSERT INTO...SELECT Statement 103
Changing What You ve Got with the UPDATE Statement 105
The DELETE Statement 108
Summary HO
ii
Table of Contents
Chapter 5: Joining Tables__113
JOINs 114
iNNER JOINS 115
How an INNER JOIN is Like a WHERE Clause 119
OUTER JOINs 123
The Simple OUTER JOIN 124
Finding Orphan or Non-Matching Records 126
Dealing with More Complex OUTER JOINs 129
Seeing Both Sides with FULL JOINs 133
CROSS JOINs 134
Exploring Alternative Syntax for Joins 135
An Alternative INNER JOIN 136
An Alternative OUTER JOIN 136
An Alternative CROSS JOIN 137
The UNION 138
Summary 143
Chapter 6: Creating and Altering Tables___145
Object Names in SQL Server 145
Ownership 146
The Database Name 147
Naming by Server 148
Reviewing the Defaults 148
The CREATE Statement 148
CREATE DATABASE 149
Building a Database 152
CREATE TABLE 153
Tabte and Column Names 154
Data Types 155
Column Constraints 158
Computed Columns 158
Table Constraints 159
Creating a Table 160
The ALTER Statement 165
ALTER DATABASE 166
Option and Termination Specs 168
ALTER TABLE 169
The DROP Statement 172
Using the GUI Tool 173
Creating a Database Using EM 173
Backing into the Code: The Basics of Creating Scripts with EM 180
Summary 185
iii
Table of Contents
Chapter 7: Constraints_187
Types of Constraints 188
Domain Constraints 189
Entity Constraints 189
Referential Integrity Constraints 189
Constraint Naming 190
Key Constraints 191
PRIMARY KEY Constraints 191
Creating the Primary Key at Table Creation 192
Creating a Primary Key on an Existing Table 193
FOREIGN KEY Constraints 193
Adding a Foreign Key to an Existing Table 195
Making a Table be Self-Referencing 195
Cascading Updates and Deletes 197
Other Things to Think about with Foreign Keys 202
UNIQUE Constraints 204
Creating UNIQUE Constraints on Existing Tables 204
CHECK Constraints 205
DEFAULT Constraints 206
Defining a DEFAULT Constraint in Your CREATE TABLE Statement 207
Adding a DEFAULT Constraint to an Existing Table 207
Disabling Constraints 208
Ignoring Bad Data when You Create the Constraint 208
Temporarily Disabling an Existing Constraint 210
Rules and Defaults - Cousins of Constraints 212
Rules 212
Dropping Rules 214
Defaults 214
Dropping Defaults 215
Determining What Tables and Data Types Use a Given Rule or Default 215
Triggers for Data Integrity 215
Choosing What to Use 216
Summary 217
Iv
Table of Contents
Chanter 8: Normalization and Other Basic Design Issues_219
Tables 220
Keeping Your Data Normal 220
Before the Beginning 221
The First Normal Form 223
The Second Normal Form 226
The Third Normal Form 228
Other Normal Forms 230
Relationships 230
0ne-to-0ne 231
Zero or One-to-One 232
One-to-One or Many 232
One-to-Zero, One, or Many 233
Many-to-Many 234
Diagramming 237
The daVinci Tools 238
Tables 241
Relationships 249
De-Normalization 252
Beyond Normalization 253
Keep It Simple 253
Choosing Data Types 253
Err on the Side of Storing Things 254
Drawing up a Quick Example 254
Creating the Database 254
Adding the Diagram and Our Initial Tables 255
Adding the Relationships 260
Adding Some Constraints 263
Summary 265
V
Table of Contents
Chapter 9: SOL Server Storage and Index Structures_267
SQL Server Storage: Past and Present 267
SQL Server Storage - What All Versions Have in Common 268
SQL Server Storage in Version 6.5 and Below -v 272
SQL Server Storage in 7.0 and Beyond 273
Understanding Indexes 277
B-Trees n 278
Page Splits - A First Look 280
How Data is Accessed in SQL Server 281
Use of Table Scans 282
Use of Indexes 282
Index Types and Index Navigation * 283
Clustered Indexes 283
Non-Clustered Indexes on a Heap 285
Non-Clustered Indexes on a Clustered Table 286
Creating and Dropping Indexes 289
The CREATE INDEX Statement 289
Implied Indexes Created with Constraints 293
Choosing Wisely: Deciding What Index Goes Where and When 293
Selectivity ! 293
Watching Costs: When Less is More «( : - 294
Choosing that Clustered Index 294
Column Order Matters 297
Dropping Indexes 297
Use the Index Tuning Wizard 297
Maintaining Your Indexes 293
Fragmentation 29®
Identifying Fragmentation vs. Likelihood of Page Splits 299
DBREINDEX and FILLFACTOR 301
Summary 3®3
vi
Table of Contents
Chanter 10: Views_305
Simple Views 305
Views as Filters 309
More Complex Views 310
The DATEADD and CONVERT Functions 313
Using a View to Change Data - Before INSTEAD OF Triggers 314
Editing Views with T-SQL 317
Dropping Views 318
Creating and Editing Views In EM 318
Editing Views in EM 322
Auditing: Displaying Existing Code 322
Protecting Code: Encrypting Views 324
About Schema Binding 325
M king Your View Look Like a Table with VI EW_ MET A DAT A 326
Indexed (Materialized) Views 326
Summary 331
Chapter 11: Writing Scripts and Batches_333
Script Basics 334
The USE Statement 335
Declaring Variables 335
Setting the Value in Your Variables 336
Reviewing System Functions 337
Using @@IDENTITY 339
Using @@R0WC0UNT 343
Batches 344
A Line to Itself 344
Each Batch is Sent to the Server Separately 345
GO is Not a T-SQL Command 345
Errors in Batches 346
When to Use Batches 346
Statements that Require their Own Batch 346
Using Batches to Establish Precedence 347
OSQL 349
Dynamic SQL: Generating Your Code on the Fly with the EXEC Command 351
The Gotchas of EXEC 352
Summary 356
vii
Table of Contents
Chapter 12: Stored Procedures_359
Creating the Sproc: Basic Syntax 360
An Example of a Basic Sproc 360
Changing Stored Procedures with ALTER 361
Dropping Sprocs 362
Parameterization 362
Declaring Parameters 362
Supplying Default Values 364
Creating Output Parameters 365
Control-of-Flow Statements 367
The IF...ELSE Statement 367
The ELSE Clause 370
Grouping Code into Blocks 374
The CASE Statement 378
A Simple CASE • 379
A Searched CASE 381
Looping with the WHILE Statement 385
The WAITFOR Statement 386
Confirming Success or Failure with Return Values 387
How to Use RETURN 387
Dealing with Errors 389
Handling Inline Errors 390
Making Use of @@ERR0R 390
Using @@ERR0R in a Sproc 392
Handling Errors Before They Happen 395
Manually Raising Errors 399
Adding Your Own Custom Error Messages 402
What a Sproc Offers 406
Creating Callable Processes 406
Using Sprocs for Security 407
Sprocs and Performance 408
When a Good Sproc Goes Bad 409
Extended Stored Procedures (XPs) 410
xp_cmdshell 411
xp_msver 412
System Stored Procedures 414
A Brief Look at Recursion 416
Debugging 418
Setting up SQL Server for Debugging 419
Starting the Debugger 420
Parts of the Debugger 423
Using the Debugger once It s Started 424
Summary 432
Vlll
Table of Contents
Chapter 13: User Defined Functions_435
What a UDF Is 436
UDFs Returning a Scalar Value 437
UDFs that Return a Table 440
Understanding Determinism 448
Creating System Functions 450
Deleting the System Functions after You Create Them 451
Summary 452
Chaster 14: Transactions and Locks_455
Transactions 455
BEGIN TRAN 456
COMMIT TRAN 457
ROLLBACK TRAN 457
SAVE TRAN 457
Row the SQL Server Log Works 461
Failure and Recovery 464
Implicit Transactions 465
Locks and Concurrency 467
What Problems Can Be Prevented by Locks 467
Lockable Resources 471
Lock Escalation and Lock Effects on Performance 472
Lock Modes 473
Lock Compatibility 475
Specifying a Specific Lock Type - Optimizer Hints 476
Using spjock - and an Extra Surprise 478
Using spDBLocks 479
Determining Locks Using EM 480
Setting the Isolation Level 481
About Isolation Levels and MTS/COM+ 483
Dealing with Deadlocks (aka A 1205 ) 484
How SQL Server Figures out there s a Deadlock 484
How Deadlock Victims Are Chosen 484
Avoiding Deadlocks 485
Summary 488
ix
Table of Contents
Chapter 15: Triggers_491
What is a Trigger? 492
ON 493
WITH ENCRYPTION 493
The FOR[AFTER vs. the INSTEAD OF Clause 494
FOR|AFTER 495
WITH APPEND 496
NOT FOR REPLICATION 496
AS 496
Using Triggers for Referential Integrity 496
Using Triggers for Simple Referential Integrity 497
Using Triggers for More Flexible Referential Integrity 501
One-to-One Relationships 501
Exclusive Subcategories 505
Using Triggers for Data Integrity Rules 508
Dealing with Requirements Sourced from Other Tables 508
Using Triggers to Check the Delta of an Update 510
Using Triggers for Custom Error Messages 512
Other Common Uses for Triggers 512
Updating Summary Information 513
Feeding Data into De-Normalized Tables for Reporting 516
Setting Condition Rags 516
Other Trigger Issues 518
Triggers Can Be Nested 519
Triggers Can Be Recursive 519
Triggers Don t Prevent Architecture Changes 519
Triggers Can Be Turned Off Without Being Removed 520
Trigger Firing Order 520
Controlling Firing Order for Logic Reasons 522
Controlling Firing Order for Performance Reasons 522
INSTEAD OF Triggers 522
INSTEAD OF INSERT Triggers 523
INSTEAD OF UPDATE Triggers 527
INSTEAD OF DELETE Triggers 529
Performance Considerations 531
Triggers are Reactive rather than Proactive 531
Triggers don t have Concurrency Issues with the Process that Rres them 531
Using IF UPDATEO and UPDATED_COLUMNS 532
The UPDATEO Function 532
The UPDATED.COLUMNSO Function 532
Keep it Short and Sweet 535
Don t Forget Triggers when Choosing Indexes 536
JOINs in Triggers Count towards the Pre-7.0 Table Limit 536
Try Not to Rollback within Triggers 536
Dropping Triggers 536
Debugging Triggers 536
Summary 539
x
Table of Contents
Chapter 16: Advanced Queries_541
What Is a Subquery? 542
Building a Nested Subquery 543
Nested Queries Using Single Value SELECT Statements 543
Nested Queries Using Subqueries that Return Multiple Values 544
The ANY, SOME, and ALL Operators 546
Correlated Subqueries 548
How Correlated Subqueries Work 548
Correlated Subqueries in the WHERE Clause 549
Correlated Subqueries in the SELECT List 552
Derived Tables 554
The EXISTS Operator 556
Mixing Data Types: CAST and CONVERT 560
Performance Considerations 562
JOINs vs. Subqueries vs. ? 563
Summary 564
Chapter 17: Distributed Queries and Transactions_567
Distributed Transactions 568
The Prepare Phase 568
The Commit Phase 569
Distributed Queries 570
Creating Linked Servers 570
Using sp_addlinkedserver 570
Deleting a Linked Server 571
Using sp_addlinkedsrvlogin 572
Using a Linked Server 573
Executing Sprocs on a Linked Server 577
Gathering Meta-data Information from the Remote Server 578
Creating and Using Pass-Through Queries 581
Using Ad Hoc Queries Against Remote Data Sources 582
Other Distributed Query Points to Ponder 583
Federated Servers (Distributed Partitioned Views) 585
Summary 594
xi
Table of Contents
Chapter 18: SOL Cursors_597
What Is a Cursor? 598
The Lifespan of a Cursor 599
Types of Cursors and Extended Declaration Syntax 602
Scope 602
Scrollability 607
Cursor Types 609
Static Cursors 609
Keyset-Driven Cursors 612
Dynamic Cursors 616
Fast-Forward Cursors , 620
Concurrency Options 621
READ.ONLY 622
SCROLL_LOCKS 622
OPTIMISTIC 624
Detecting Conversion of Cursor Types: TYPE__WARNING 625
FOR SELECT 626
FOR UPDATE 626
Navigating the Cursor: the FETCH Statement 627
Altering Data within your Cursor 628
Summary 631
Chapter 19: A Brief XML Primer__633
XML Basics 634
Parts of an XML Document 635
No Defects - Being Well Formed 638
An XML Example 639
Determining Elements vs. Attributes 641
Namespaces 642
Element Content 843
Valid vs. Well Formed - DTDs and Schemas 644
DTDs 645
XML Schemas 646
A Quick Word on DTDs/Schemas and Performance 647
Transformations - XSLT 647
Reviewing some Key XSL Elements 653
Summary 653
xii
Table of Contents
Chapter 20: Integration of XML into SOL Server_655
The FOR XML Clause 657
RAW 659
AUTO 661
EXPLICIT 662
Tag and Parent 663
Column Naming 664
OPENXML 677
HTTP Access 682
Setting Up HTTP Access 683
URL-Based Queries 691
Using Templates 693
POST 697
XPsth 703
Styling Your Results 711
A Brief Word About Updategrams 715
Streaming XML 716
A Quick Look at the Stream Object 716
Implementing a Stream Object into a Component 717
Summary 723
Chapter 21: The Bulk Copy Program (bcp)_725
The bcp Utility 726
bcp Syntax 726
bcp Import 730
Data Import Example 731
Logged vs. Non-logged 733
Format Files 734
Mismatched Held Order 736
Using Format Files 737
Maximizing Import Performance 737
bcp Export 738
BULK INSERT 739
Summary 740
xiii
Table of Contents
Chapter 22: Introduction to Data Transformation Services_743
The DTS Package Editor 744
Connection 745
Task 746
Workflow 748
Using the Import/Export Wizard 749
Execute SQL 759
Workflow 760
The Connection Objects 760
The Transform 760
Creating a Simple Transformation Package 761
Setting up Workflow 779
Saving a Package 781
Using DTS in the place of bcp 782
Running from the Command Line - dtsrun.exe 786
Summary 788
Chapter 23; Replication_ 791
Supporting Distributed Data 792
Considerations when Planning for Replication 792
Autonomy 792
Latency 792
Data Consistency 793
Schema Consistency 793
Other Considerations 793
The Publishing Metaphor 793
Subscriptions 794
Types of Subscribers 795
Filtering Data 795
Types of Replication 796
Snapshot Replication 796
Merge Replication 799
Transactional Replication 801
Immediate-Update Subscribers 804
Mixing Replication Types 805
Replication Model Scenarios 805
Standard Models 805
Central Publisher/Distributor 806
Central Publisher/Remote Distributor 806
Central Subscriber 807
Mixed Models 808
Publishing Subscriber 808
Publisher/Subscriber 808
Multiple Subscribers/Multiple Publishers 809
One More Issue... 809
Publish to Self 809
xlv
Table of Contents
Implementation Examples 810
Contractor Supply 810
Cleanzit 811
Planning for Replication 812
Data Concerns 812
Replication Type 813
Replication Wizards 813
Enabling Publishing and Distribution 813
Starting the Wizard 814
Default Configuration 815
Custom Configuration 817
After Configuration 820
Disabling Distribution 821
Transact-SQL Procedures 821
Transactlonal/Snapshot Publications 822
The Create and Manage Publications Wizard 822
Queued Updating 824
After Configuration 833
Transact-SQL Procedures 834
Merge Publications 835
The Create and Manage Publications Wizard 835
After Configuration 846
Transact-SQL Procedures 847
Push Subscriptions 847
Push Subscription Wizard 847
About Merge Subscriptions 852
Transact-SQL Procedures 853
Pull Subscriptions 853
Pull Subscription Wizard 853
Transact-SQL Procedures 855
Managing Replication 856
Replication Scripts 856
Supporting Heterogeneous Replication 856
Publishing to the Internet 857
Replication and Schema Changes 858
sp_repladdcolumn 858
sp.repldropcolumn 859
Replication Monitor 860
Keeping it Going 863
Summary 865
XV
Table of Contents
Chanter 24: Advanced Design_867
More on Diagramming and Relationships 868
A Couple of Relationship Types 869
The Entity Box 869
The Relationship Line 870
Terminators 870
Logical vs. Physical Design 873
Purpose of a Logical Model 874
Parts of a Logical Model 875
Dealing with File-Based Information 876
Subcategories 879
Types of Subcategories 880
Implementing Subcategories 881
The Physical Implementation of Subcategories 883
How Subcategories Add to Extensibility 884
Database Reuse 884
Candidates for Reusable Databases 885
How to Break Things Up 885
The High Price of Reusability 886
Partitioning for Scalability 886
Summary 888
Chapter 25: Analysis Services__891
The Requirements of End Users 892
Online Transaction Processing (OLTP) 892
Online Analytical Processing (OLAP) 893
Data Mining 894
OLTP or OLAP? 894
Dimensional Databases 896
The Fact Table 896
The Dimension Tables 896
The Star and Snowflake Schemas 897
Data Cubes 897
OLAP Storage Types 898
MOLAP 899
ROLAP 899
HOLAP 899
Data Warehouse Concepts 899
Data Warehouse Characteristics 900
Data Marts 901
xvi
Table of Contents
Data Transformation Services 901
Data Validation 902
Data Scrubbing 902
Data Migration 902
Data Transformation 903
DTS Components 903
Meta-data and the Repository 903
Data Mining Models 904
Data Mining Algorithms 905
Decision Trees 905
Clustering 906
Analysis Manager 906
Creating an OLAP Solution 907
How Did We Get to this Schema? 910
Creating the OLAP Database in the Analysis Manager 910
Adding the Data Sales Cube 911
Designing Cube Storage 926
Cube Security 928
Browsing the Cube Data 931
Building a Data Mining Model 933
Summary 939
Chapter 26: Full-Text Search_941
Full-Text Search Architecture 943
Setting up Full-Text Indexes and Catalogs 944
Enabling Full-Text for your Database 944
Creating a Full-Text Catalog 945
Enabling Full-Text Search for Individual Tables 947
Index Population 950
Full-Text Query Syntax 952
CONTAINS 952
FREETEXT 954
CONTAINSTABLE 954
FREETEXTTABLE 956
Dealing with Phrases 956
Proximity 959
Prefix Terms 961
Weighting 961
Inflectional 962
A Brief Word about Rank 963
Noise Words 963
Linguistics 964
sp_fulltext_servlce 964
Summary 965
xvii
Table of Contents
Chapter 27; English Query_967
What is English Query? 968
The Benefits and Limitations of English Query 969
The Architecture of an English Query Application , 970
Creating an English Query Project 971
Create a New Project using SQL Project Wizard 972
Basic English Queries 977
Testing a Basic Query 978
Viewing and Changing Entity Properties 979
Retesting the Basic Query 981
Refining and Expanding Entities and Relationships 982
Refining and Expanding a Field Entity 983
Defining Additional Phrasings 987
Saving Queries and Regression Testing 990
Defining Relationships between Tables 991
Adding a Prepositional Phrase to a Verb Phrasing , 994
Supporting Questions about Time 995
Relationships Based on Multiple Joins 995
Phrasings and Relationships among Table and Field Entities 997
Defining Subset Phrasings 998
Defining Adjective Phrasings 1000
Defining Synonyms 1004
Synonyms for Entities 1004
Synonyms for Verbs 1004
Dictionary Entries 1005
Testing Our Application Revisited 1006
Regression Testing 1009
Deploying an English Query Application 1009
Project Files 1010
Building an English Query Application 1013
Using an English Query Application 1013
Setting a Reference to the Library 1013
Project Overview 1014
Opening a Session 1015
Parsing a Request 1016
Handling a Response 1017
Distribution 1021
Summary 1022
xviil
Table of Contents
Chanter 28: Security_____1025
Security Basics 1029
One Person, One Login, One Password 1029
Password Expiration 1030
Tracking Password Use 1032
Password Length and Makeup 1032
Number of Tries to Login 1033
What to Do About Login Failure 1033
Storage of User and Password Information 1034
Security Options 1035
SQL Server Security 1035
Converting from NT Integrated Security to NT/SQL Server Security 1036
Creating a New SQL Server Login 1036
NT Integrated Security 1040
What Is NT Security? 1040
Granting Access to NT User Accounts 1040
Why Use NT Security? 1042
User Rights 1042
Granting Access to a Specific Database 1043
Granting Object Permissions within the Database 1044
GRANT 1045
DENY 1047
REVOKE 1048
User Rights and Statement-Level Permissions 1049
Server and Database Roles 1050
Server Roles 1051
Database Roles 1052
Application Roles 1056
Creating Application Roles 1057
Adding Permissions to the Application Role 1057
Using the Application Role 1057
Getting Rid of Application Roles 1058
Security with XML 1058
More Advanced Security 1059
What to Do about the Guest Account 1059
TCP/IP Port Settings 1060
Don t Use the sa Account 1060
Keep xp_cmdshell under Wraps 1061
Don t Forget Views, Stored Procedures, and UDFs as Security Tools 1061
Summary 1061
xix
Table of Contents
Chapter 29: Performance Tuning_1063
When to Tune 1064
Index Choices 1065
Covering Indexes 1066
Don t Forget the Index Tuning Wizard « 1067
Client- vs. Server-Side Processing 1067
Strategic De-Normalization 1069
Routine Maintenance 1069
Organizing Your Sprocs Well 1070
Uses for Temporary Tables 1072
Sometimes, It s the Little Things... 1072
Hardware Considerations 1073
Exclusive Use of the Server 1074
I/O vs. CPU Intensive 1074
OLTP vs. CLAP 1078
On-Site vs. Off-Site 1078
The Risks of Being Down 1079
Lost Data 1079
Is Performance Everything? 1080
Driver Support 1080
The Ideal System 1080
Trouble-Shooting 1081
The Various Showplans and STATISTICS 1081
SHOWPLAN.TEXT and SHOWPLAN_ALL 1081
Graphical Showplan 1083
STATISTICS 1084
The Database Consistency Checker (DBCC) 1087
The Query Governor 1087
The SQL Server Profiler 1088
Using Profiler Lite (Query Analyzer) 1092
The Performance Monitor (Perfmon) 1092
Summary 1094
xx
Table of Contents
Chapter 30: Administration Overview_1097
Scheduling Jobs 1098
Creating an Operator 1099
Creating an Operator Using EM 1099
Creating an Operator Using T-SQL 1101
Creating Jobs and Tasks 1103
Creating Jobs and Tasks Using EM 1103
Creating Jobs and Tasks Using T-SQL 1108
Maintaining and Deleting Jobs and Tasks 1117
Backup and Restore Operations 1118
Backup Media 1118
Backing Up 1120
Restoring Data 1122
Alerts 1127
Creating an Alert in EM 1127
Creating an Alert in T-SQL 1128
Full-Text Catalog Population 1130
Backup and Restore 1130
Schedule Your Populations 1130
Copying Data from One Place to Another 1130
The Copy Database Wizard 1130
Backup And Restore 1135
Attach/Detach 1135
Index Rebuilding 1136
Archiving of Data 1136
Summary 1137
Chapter 31: Advanced DTS___1139
Introduction to the DTS Object Model 1140
Setting The Reference 1142
The Package Object 1143
Dynamic Package 1145
Global Variables 1145
The Dynamic Properties Task 1146
The Multiphase Data Pump 1154
Enabling the Multiphase Data Pump 1156
Multiphase Data Pump Example 1157
Optimizing Data Loads in DTS 1167
Summary 1170
xxi
Table of Contents
Chapter 32: Scripting Administrative Functions with WMI_1173
The History (and Future) of Scripting Administration for SQL Server 1174
Windows Management Instrumentation 1175
WMI Architecture 1176
The Basics of WMI Objects 1178
Classes - 1178
Instances 1180
Associations 1180
Namespaces . , - 1181
Installing the WMI SQL Server Administration Provider 1182
The SQL Server Provider Schema . 1182
Connecting to WMI 1190
A Few Notes on Security 1191
Instance Retrieval Operations 1 1192
Enumerating All Instances 1192
Getting a Specific Instance 1194
Querying for Instances 1196
Association Queries 1198
Associators Of ; 1198
References Of . 1201
Tips for Getting the Best Performance from Instance Calls 1202
Modifying Instances 1205
Creating Instances 1207
Deleting Instances 1208
Invoking Methods 1208
Dynamic Methods 1208
Static Methods 1211
Using Events 1213
Instance Events 1213
Instance Creation Events 1214
Instance Deletion Events 1216
Instance Modification Events 1216
Reporting All Events 1218
Intrinsic Events that don t Require Polling 1218
Non-Instance Events 1219
Static Settings Classes 1219
Accessing Qualifiers 1221
Using SQL Server-Specific Qualifiers 1224
SQLVersion 1224
WriteAtCreate and WriteAtUpdate 1224
Summary 1225
xxii
_Table of Contents
Appendix A: System Functions_1227
Appendix B: Function Listing_1239
Appendix C: Tools for Our Time_ 1281
Appendix D: Access Upslzlng_ 1289
Appendix E: Microsoft Data Transformation Services Package
Oblect Library Reference_1293
index_ 1349
xxiii
|
| any_adam_object | 1 |
| author | Vieira, Robert |
| author_facet | Vieira, Robert |
| author_role | aut |
| author_sort | Vieira, Robert |
| author_variant | r v rv |
| building | Verbundindex |
| bvnumber | BV013649731 |
| callnumber-first | Q - Science |
| callnumber-label | QA76 |
| callnumber-raw | QA76.9.D3 |
| callnumber-search | QA76.9.D3 |
| callnumber-sort | QA 276.9 D3 |
| callnumber-subject | QA - Mathematics |
| classification_rvk | ST 271 |
| ctrlnum | (OCoLC)45660168 (DE-599)BVBBV013649731 |
| dewey-full | 005.75/85 |
| dewey-hundreds | 000 - Computer science, information, general works |
| dewey-ones | 005 - Computer programming, programs, data, security |
| dewey-raw | 005.75/85 |
| dewey-search | 005.75/85 |
| dewey-sort | 15.75 285 |
| dewey-tens | 000 - Computer science, information, general works |
| discipline | Informatik |
| format | Book |
| fullrecord | <?xml version="1.0" encoding="UTF-8"?><collection xmlns="http://www.loc.gov/MARC21/slim"><record><leader>01590nam a2200457 c 4500</leader><controlfield tag="001">BV013649731</controlfield><controlfield tag="003">DE-604</controlfield><controlfield tag="005">20021129 </controlfield><controlfield tag="007">t</controlfield><controlfield tag="008">010323s2000 xxua||| |||| 00||| eng d</controlfield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">1861004486</subfield><subfield code="9">1-861004-48-6</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(OCoLC)45660168</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(DE-599)BVBBV013649731</subfield></datafield><datafield tag="040" ind1=" " ind2=" "><subfield code="a">DE-604</subfield><subfield code="b">ger</subfield><subfield code="e">rakwb</subfield></datafield><datafield tag="041" ind1="0" ind2=" "><subfield code="a">eng</subfield></datafield><datafield tag="044" ind1=" " ind2=" "><subfield code="a">xxu</subfield><subfield code="c">US</subfield></datafield><datafield tag="050" ind1=" " ind2="0"><subfield code="a">QA76.9.D3</subfield></datafield><datafield tag="082" ind1="0" ind2=" "><subfield code="a">005.75/85</subfield><subfield code="2">21</subfield></datafield><datafield tag="084" ind1=" " ind2=" "><subfield code="a">ST 271</subfield><subfield code="0">(DE-625)143639:</subfield><subfield code="2">rvk</subfield></datafield><datafield tag="100" ind1="1" ind2=" "><subfield code="a">Vieira, Robert</subfield><subfield code="e">Verfasser</subfield><subfield code="4">aut</subfield></datafield><datafield tag="245" ind1="1" ind2="0"><subfield code="a">Professional SQL Server 2000 programming</subfield><subfield code="c">Robert Vieira</subfield></datafield><datafield tag="264" ind1=" " ind2="1"><subfield code="a">Birmingham u.a.</subfield><subfield code="b">Wrox Press</subfield><subfield code="c">2000</subfield></datafield><datafield tag="300" ind1=" " ind2=" "><subfield code="a">XXIII, 1391 S.</subfield><subfield code="b">Ill.</subfield></datafield><datafield tag="336" ind1=" " ind2=" "><subfield code="b">txt</subfield><subfield code="2">rdacontent</subfield></datafield><datafield tag="337" ind1=" " ind2=" "><subfield code="b">n</subfield><subfield code="2">rdamedia</subfield></datafield><datafield tag="338" ind1=" " ind2=" "><subfield code="b">nc</subfield><subfield code="2">rdacarrier</subfield></datafield><datafield tag="490" ind1="0" ind2=" "><subfield code="a">Programmer to programmer</subfield></datafield><datafield tag="630" ind1="0" ind2="4"><subfield code="a">SQL server</subfield></datafield><datafield tag="650" ind1=" " ind2="7"><subfield code="a">Oracle</subfield><subfield code="2">gtt</subfield></datafield><datafield tag="650" ind1=" " ind2="7"><subfield code="a">SQL</subfield><subfield code="2">gtt</subfield></datafield><datafield tag="650" ind1=" " ind2="4"><subfield code="a">Database management</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">SQL</subfield><subfield code="0">(DE-588)4134010-3</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">Programmierung</subfield><subfield code="0">(DE-588)4076370-5</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">SQL Server 2000</subfield><subfield code="0">(DE-588)4623337-4</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="689" ind1="0" ind2="0"><subfield code="a">SQL Server 2000</subfield><subfield code="0">(DE-588)4623337-4</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2=" "><subfield code="5">DE-604</subfield></datafield><datafield tag="689" ind1="1" ind2="0"><subfield code="a">SQL</subfield><subfield code="0">(DE-588)4134010-3</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="1" ind2="1"><subfield code="a">Programmierung</subfield><subfield code="0">(DE-588)4076370-5</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="1" ind2=" "><subfield code="5">DE-604</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">HBZ Datenaustausch</subfield><subfield code="q">application/pdf</subfield><subfield code="u">http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=009325770&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Inhaltsverzeichnis</subfield></datafield><datafield tag="999" ind1=" " ind2=" "><subfield code="a">oai:aleph.bib-bvb.de:BVB01-009325770</subfield></datafield></record></collection> |
| id | DE-604.BV013649731 |
| illustrated | Illustrated |
| indexdate | 2024-07-09T18:49:35Z |
| institution | BVB |
| isbn | 1861004486 |
| language | English |
| oai_aleph_id | oai:aleph.bib-bvb.de:BVB01-009325770 |
| oclc_num | 45660168 |
| open_access_boolean | |
| physical | XXIII, 1391 S. Ill. |
| publishDate | 2000 |
| publishDateSearch | 2000 |
| publishDateSort | 2000 |
| publisher | Wrox Press |
| record_format | marc |
| series2 | Programmer to programmer |
| spelling | Vieira, Robert Verfasser aut Professional SQL Server 2000 programming Robert Vieira Birmingham u.a. Wrox Press 2000 XXIII, 1391 S. Ill. txt rdacontent n rdamedia nc rdacarrier Programmer to programmer SQL server Oracle gtt SQL gtt Database management SQL (DE-588)4134010-3 gnd rswk-swf Programmierung (DE-588)4076370-5 gnd rswk-swf SQL Server 2000 (DE-588)4623337-4 gnd rswk-swf SQL Server 2000 (DE-588)4623337-4 s DE-604 SQL (DE-588)4134010-3 s Programmierung (DE-588)4076370-5 s HBZ Datenaustausch application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=009325770&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA Inhaltsverzeichnis |
| spellingShingle | Vieira, Robert Professional SQL Server 2000 programming SQL server Oracle gtt SQL gtt Database management SQL (DE-588)4134010-3 gnd Programmierung (DE-588)4076370-5 gnd SQL Server 2000 (DE-588)4623337-4 gnd |
| subject_GND | (DE-588)4134010-3 (DE-588)4076370-5 (DE-588)4623337-4 |
| title | Professional SQL Server 2000 programming |
| title_auth | Professional SQL Server 2000 programming |
| title_exact_search | Professional SQL Server 2000 programming |
| title_full | Professional SQL Server 2000 programming Robert Vieira |
| title_fullStr | Professional SQL Server 2000 programming Robert Vieira |
| title_full_unstemmed | Professional SQL Server 2000 programming Robert Vieira |
| title_short | Professional SQL Server 2000 programming |
| title_sort | professional sql server 2000 programming |
| topic | SQL server Oracle gtt SQL gtt Database management SQL (DE-588)4134010-3 gnd Programmierung (DE-588)4076370-5 gnd SQL Server 2000 (DE-588)4623337-4 gnd |
| topic_facet | SQL server Oracle SQL Database management Programmierung SQL Server 2000 |
| url | http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=009325770&sequence=000001&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |
| work_keys_str_mv | AT vieirarobert professionalsqlserver2000programming |