Microsoft SQL 7.0 Server Resource Guide Book

Index

.
.eqd files  705
.ldf files  12
.mdf files  12
.ndf files  12
.sql files  552
.trc files  169

|
|| string concatenation operator  507

+
+ string concatenation operator  507

1
1K Challenge project  428

2
2PC (two-phase commit)  52, 55 - 56, 487

A
about this guide  4 - 5
Access
        information analysis  310
    migrating to SQL Server  See migrating Access databases to SQL Server
access control  384
Access Control Lists (ACLs)  385, 392
Access Denied message  266
accessing large object columns  723
accessing Oracle functionality  84
accounts
    login  441
    Windows NT  390
ACID properties  59
ACLs (Access Control Lists)  385, 392
Active Data Connector  87
Active Directories  395
Active Server Pages (ASP)  705 - 706
ActiveX Data Objects (ADO)
    connectivity options  87
    data warehouses  299
    described  84
ad hoc names  712
adding constraints  465
additional information
    connectivity  88
    database tuning guide  518
    ISVs building OLE DB providers  88
    Microsoft SQL Server  5
    migrating applications to run on SQL Server  123
    migrating Btrieve applications to SQL Server  583, 589
    migrating data to SQL Server  122
    migrating Sybase applications to SQL Server  561
    OLAP Services  322, 328, 333
    security  403
    SMS (Systems Management Server)  123
    TPC benchmarks  108
    Windows DNA  119
    Windows NT security  394
administering mobile applications  123 - 129
administration tools architecture  124
administrator tasks
    data warehouses  312 - 318
    full-text queries  654 - 655
    stored procedures  658 - 662
ADO (ActiveX Data Objects)
    connectivity options  87
    data warehouses  299
    described  84
advertised disk transfer rates  145
aggregate functions  501
aggregations  324, 328 - 332, 494
alerts  248 - 250
analyzing Index Tuning Wizard output  194
ANSI NULL behavior  572
applications
    integrity  92
    migrating from Access to SQL Server  555
    mobile  See mobile applications
    supporting mobile clients  122
architecture
    comparing SQL Server and Sybase  560 - 562
    data warehouses  295 - 296
    DTS  305 - 308
    full-text queries  655 - 657
    Index Tuning Wizard  196 - 198
    indexing  651 - 655
    log manager  375 - 376
    memory algorithms  30 - 40
    migrating Oracle databases to SQL Server  435 - 444
    ODBC  521
    OLAP Services  324 - 326
    storage engine  362
ARRAY COMPARISON predicate  692 - 693
AS/400  89
ASP (Active Server Pages)  705 - 706
assigning files and file groups  14, 15 - 18
assigning variables  509
asynchronous technology  83
audience for this guide  3
auditing  388
authentication  384
authentication modes  100, 441
Authenticode  386, 400
authoring, English Query  698 - 705
autocommit mode  532
autogrow  21
automatic conflict resolution  56
automatic covering indexes  161
automatic parameters  347
automatic recovery  438
automatic statistics maintenance  110
autoshrink  22

B
back up and restore
    common problems  283 - 285
    comparing SQL Server and Oracle  97, 439
    differential backups  282
    FORMAT command  281
    frequently asked questions (FAQs)  277 - 281
    INIT command  281
    insufficient disk space  288
    introduction  277
    limitations  111
    mobile applications  129
    orphaned users  286
    parallelism  281
    performance tuning  188
    recovery performance  287
    tips for using  281 - 282
    troubleshooting  283 - 286
backing up SQL Server 6.x  414
backup utilities for SQL Server  95
backward database compatibility  427 - 428
base-table organization  371
bcp (bulk copy program) utility  111, 281
binary large objects (BLOBs)  77, 496
binding WinSock ports for data communications  215
biometric authentication  391
BLOBs (binary large objects)  77, 496
blocking  252
blocks  437
blue screen  262 - 265
bookmark lookups  177
B-tree structures  157
Btrieve
    concurrency  619
    locking  619
    migrating applications to SQL Server  See migrating Btrieve applications to SQL Server
    navigational data processing model  613
    posBlock  607
    server cursors  616 - 618
buffer manager  39
buffers  140, 378
bulk copy program (bcp) utility  111, 281

C
C2 evaluation process  394
CA (Certification Authority)  386, 387, 399
cache management  110
caching, RAID  150
Cartesian products  352 - 353
CASE expression  502
CAST function  503
catalogs, full-text  653
Certificate Management  399
Certificate Server (CS)  399
certificates  387
Certification Authority (CA)  386, 387, 399
character functions  499
character sets  728
CHECK constraint  467
checkpoint  141
classic SQL Server indexes vs. full-text indexes  657
client application queries  82
CLOSE CURSOR statement  517
clustered indexes
    See also indexes; nonclustered indexes
    described  372, 454
    performance tuning  159
    selecting  163 - 165
    structure of  158
clustered tables  371
clusters  455
Cnfgsvr.out  237
COM (Component Object Model)
    described  90 - 91
    mobile applications  119
combining predicates  647
combining terms  643, 686
Common.cpp  531
comparison operators
    comparing SQL Server and Oracle  505 - 507
    pattern matches  506
    string concatenation  507
    using NULL  507
Component Object Model (COM)
    described  90 - 91
    mobile applications  119
components
    comparing SQL Server and Oracle  97
    consuming resources  140 - 144
    data warehouses  296 - 298
    query  683 - 684
    replication  50 - 52
COMPUTE clause  494
concatenation operators  507
concurrency
    Btrieve model  619
    comparing SQL Server and Oracle  478 - 487
    SQL Server  620 - 622
conditional processing  510
conditional tests  502
configuring FTP services  211
configuring SQL Server
    FTP services  216
    Internet replication  214 - 219
    max async IO option  139
    migrating Oracle databases to SQL Server  444
    proxy clients  215
configuring WinSock Proxy services  213
conflict resolution  56, 58 - 59, 128
connecting to OLE DB providers  712 - 715
connections
    Named Pipes  231 - 234
    ODBC errors  240
    remote  259
    TCP/IP Sockets  234
    troubleshooting  227 - 235
connectivity options
    ADO  87
    DAO  86
    DB–Library  88
    described  85
    middleware  88
    ODBC  86
    ODBCDirect  86
    OLE DB  87
    RDO  86
    RDS  88
    third-party  88
consistency  63, 64
constraints
    adding  465
    CHECK  467
    DEFAULT  467
    naming  464
    removing  465
    UNIQUE  464
consumption-side mapping  720 - 724
CONTAINS predicate  641 - 646, 685 - 689
CONTAINSTABLE function  648 - 649
controlling access to data  384
control-of-flow language  507 - 513
conventions used in this guide  6
convergence  64
conversion functions  500
CONVERT function  503
converting applications to support mobile clients  122
cost-based query optimizers  339, 520
costs
    OLAP Services  335
    queries  339, 355
covered queries  161
covering indexes  160 - 161, 345
CPU resources  140 - 144
CREATE INDEX statement  166
creating wrapper DLLs  602 - 606
CryptoAPI  396
Cryptographic Service Providers (CSP)  396
cryptography  385 - 388, 396
CS (Certificate Server)  399
CSP (Cryptographic Service Providers)  396
CT-Libraries  560
Cube Wizard  328
cubes, virtual  332
CURRENT OF clause  517
cursors
    closing  517
    comparing SQL Server and Btrieve  616 - 618
    comparing SQL Server and Oracle  513 - 517
    declaring  515
    described  513
    distributed queries  710 - 711
    forward-only  522
    opening  516
    scrollable  525
    server  524, 525
    syntax  514
custom conflict resolution  58
customizing Index Tuning Wizard  193 - 194

D
DAO (Data Access Object) connectivity options  86
data
    aggregations  328 - 332, 494
    back up and restore  See back up and restore
    fetching  516
    hot spots  163
    image  374
    integrity  386
    limits  374
    managing size  21 - 23
    marts  70, 295 - 296, 320 - 322, 327
    models  323 - 324, 326 - 328, 330 - 332
    non-Unicode  374, 728
    ntext  374
    pages  363
    replicating from heterogeneous sources  62
    replicating to heterogeneous sources  61
    snowflake schemas  301, 327 - 328
    space allocation  366 - 368
    star schemas  301, 327 - 328, 350 - 351
    storage engine  373 - 375
    striping  19, 437
    text  374
    transforming for data warehouses  305 - 309
    Unicode  373, 460
    validation  268
data access
    connectivity options  85 - 89
    Intersolv  89
    ISG Navigator  89
    methods that work with Oracle  93
    middleware  88
    overview  84
    Sequiter Software  89
Data Access Object (DAO) connectivity options  86
Data Definition Language (DDL), comparing SQL Server and Sybase  560
data integrity
    described  463
    domain integrity  467 - 469
    entity integrity  463 - 467
    referential integrity  470 - 471
    user-defined integrity  471 - 477
data source name (DSN)  592
data transfer rates  147
Data Transformation Services (DTS)
    architecture  305 - 308
    described  67, 70, 76 - 78, 546
    goals  305
    migrating data to SQL Server  122
    migrating Oracle databases to SQL Server  535
    Oracle integration  95
    troubleshooting  250
data types
    comparing SQL Server and Oracle  459
    converting values  503
    distributed queries  720 - 724
    mappings  527
    timestamp  461, 494
    user-defined  460
data warehouses
    architecture  295 - 296
    components  296 - 298
    database  302 - 304
    described  70, 78 - 79, 320 - 322, 588
    designing  301
    features  335
    framework  296 - 299
    information analysis  309 - 312
    metadata  300
    Microsoft Repository  300
    models  327
    overview  293 - 294
    process  294 - 295
    system administration  312 - 318
    tools for information analysis  309 - 312
    transforming data  305 - 309
    visual database diagrams  314
database ID (DBID)  272
database owner (dbo)  444
databases
    See also storage engine  
    accessing  442
    architecture  94
    backward compatibility  427 - 428
    comparing SQL Server and Oracle object identifiers  448
    comparing SQL Server and Oracle objects  446
    data warehouses  302 - 304
    master  98, 436, 578, 580
    migrating Sybase applications to SQL Server  562
    model  98, 580
    msdb  98, 580
    queries  See textual queries, database; textual queries, file
    rapid deployment  359
    size of  96
    storage engine features  359
    system catalogs  436
    tempdb  28 - 30, 98, 156, 580
    troubleshooting  245
date entity  703
date functions  500
date values  529
db_owner role  444
DBCC DBREINDEX statement  166
DBCC SHOWCONTIG statement  166
DBCC SHRINKDB statement  280
DBID (database ID)  272
DB–Library (Ntwdblib.dll)
    client configuration and Net-Libraries  560
    connectivity options  88
dbo (database owner)  444
DCOM (Distributed COM)
    described  90
    mobile applications  119
    security  398
Dcomcnfg.exe  266
DDL (Data Definition Language), comparing SQL Server and Sybase  560
DDM (Distributed Data Management)  89
deadlocking  186, 251, 485
deadly embrace  485
DEALLOCATE CURSOR statement  517, 569
debugging  121 
    See also errors; troubleshooting
declarative referential integrity (DRI)  630
DECLARE CURSOR statement  515
declaring variables  508
DECODE statement  502
DEFAULT constraint  467
default locking behavior  481
default result sets  525, 615
defecting errors  256
deferred updates  277
defined tables  678 - 684
defining identity columns  573
degree of parallelism  44
delaying execution of stored procedures  474
DELETE statement  492, 729
DENY statement  462
DES algorithm  386
desktop OLAP (DOLAP)  333
Developer 2000  539
developing mobile applications  105
differential backups  282
disabling IP forwarding  211
disk I/O
    hot spots  163
    minimizing  377
    parallelism  154 - 157
    performance  144 - 157, 341
    performance counters  184
    query processor  340 - 341
    resource consumption  140 - 144
    sequential vs. nonsequential operations  146, 340
    transfer rates and PCI bus bandwidth  147
disk queue counters  180
disk space
    dynamic allocation of  96, 109
    insufficient  288
    scalability  110
disk transfer rates  145
diskperf.exe  152
Distributed COM (DCOM)
    described  90
    mobile applications  119
    security  398
Distributed Data Management (DDM)  89
Distributed interNet Applications (DNA)  118 - 119
Distributed Password Protocol (DPA)  398
distributed queries
    See also distributed queries: OLE DB connectivity  
    data type handling  720 - 724
    described  70, 354 - 355
    execution scenarios  711 - 725
    heterogeneous  80
    mapping methods  720 - 724
    query execution scenarios  726 - 730
distributed queries: OLE DB connectivity
    See also distributed queries  
    cursor support  710 - 711
    interfaces consumed by SQL Server  731
    OLE DB provider categories  708 - 709
    OLE DB provider interaction phases  711 - 725
    overview  707
    query execution scenarios  726 - 730
    SQL Server-specific properties  735
    terminology  707
    Transact–SQL  709, 734
Distributed Query Processor  650
Distributed Transaction Coordinator (DTC)  66
distributed transactions  486
Distribution Agent
    defined  52
    troubleshooting  267
distribution statistics  373
Distributor  51
DNA (Distributed interNet Applications)  118 - 119
document conventions  6
DOLAP (desktop OLAP)  333
domain integrity  467 - 469
domains
    models  390
    trust relationships  390, 395
    Windows NT  390
DPA (Distributed Password Protocol)  398
DRI (declarative referential integrity)  630
DROP_EXISTING option  166
DSN (data source name)  592
DTC (Distributed Transaction Coordinator)  66
DTS (Data Transformation Services)
    architecture  305 - 308
    described  67, 70, 76 - 78, 546
    goals  305
    migrating data to SQL Server  122
    migrating Oracle databases to SQL Server  535
    Oracle integration  95
    troubleshooting  250
DTS Data Pump  308
DTS Package Designer  308 - 309
DTS Transformation Engine  306
DTS Wizard
    described  76 - 77, 547
    migrating Btrieve data to SQL Server  592 - 601
  &