Docstoc

Symbols A

Document Sample
Symbols A Powered By Docstoc
					                                      Symbols
                                      1204 trace flags, 1197-1199
                                      1222 trace flags, 1199

                                      A
                                      access to data, Database Engine, 13
                                      access path costs, estimating, 1040
                                          clustered indexes, 1041
                                          nonclustered indexes, 1042-1046
                                          table scan costs, 1046-1048
                                      accounts
                                          for Database Mail, creating, 342-344
                                          proxy accounts, SQL Server Agent, 367-368
                                          startup accounts, configuring SQL Server Agent,
                                             363-365
                                      ACID properties, transactions, 874
                                      @action parameter, CD:1927
                                      actions, CD:1845
                                      active multisite DR pattern, CD:1776-1777
                                      active/active configuration, SQL Server
NOTE                                    Clustering, 517
                                      active/active DR sites pattern, CD:1775
Page numbers prefaced by CD:          active/passive configuration, SQL Server
signify topics found in the bonus       Clustering, 516
                                      active/passive DR sites pattern, CD:1774
chapters included on the CD, not in   ActiveX Data Object Multidimensional (ADO
the printed book.                       MD), 1476
                                      Activity Monitor, SSMS, 68-70
                                      ad hoc distributed queries, CD:1704
                                      ad hoc reporting, 1645
                                      ADD, ALTER FULLTEXT INDEX, CD:1925-1926
                                      Add References dialog, 1332
                                      adding
                                          CLR functions to databases, 828-829
                                          CLR stored procedures to databases, 792-793
                                          columns, T-SQL, 600-601
                                          data sources to OLAP databases, 1488-1490
                                          extended stored procedures to SQL Server,
                                             794-795
                                          linked servers, CD:1673-1680
                                      ADFs, SSNS, CD:1850
                                          Database, CD:1851
                                          Distributors, CD:1862-1863
                                          EventClasses, CD:1851-1852
                                          Generator, CD:1862
                                          NotificationClasses, CD:1854-1859
                                          ParameterDefaults, CD:1851
                                          Providers, CD:1859-1862
                                          sample applications, CD:1850-1851
                                          SubscriptionClasses, CD:1852-1854
                                      administration tools, SSMS, 64-71
                                      ADO MD (ActiveX Data Object
                                        Multidimensional), 1476
                                          in OLAP database creation, 1521
                                      ADO.NET, 1319-1323
                                          connection pooling, 1328
                                          MARS, 1324-1326
                                          new features, 1324
                                          Query Notifications, 1328-1330
                                          System.Data.SqlTypes namespace, 1326-1328
1942      Advanced page


Advanced page, 362                                  analyzing
Advanced Windowing Extensions (AWE),                    slowed stored procedures or queries with SQL
  CD:1707-1709                                            Server Profiler, 145
adXactReadcommitted function, 1155                      STATISTICS IO, 1141-1142
affinity I/O mask, CD:1704-1706                         stored procedures with Query Analyzer,
affinity mask, CD:1706-1707                               1129-1130
AFTER triggers, 854-856                                 traces (SQL Server Profiler) with Database Engine
agent history clean up: distribution, 442                 Tuning Advisor, 128-129
agents                                              anonymous subscriptions, 434
    replication agents. See replication agents      ANSI (American National Standards Institute), 257
    snapshot agent, 437                             ANY, 1067
         processing, 438-439                        application data values, CD:1780
         synchronization, 437-438                   Application locking level (SQL Server), 1178
Aggregate() method, 1363                            application locks, granularity, 1181-1184
Aggregates, UDAs, 1363-1366                         application progress, monitoring (SQL Server Profiler),
aggregating data within cubes in OLAP database        150-152
  creation, 1510-1514                               application roles, 264-265
Aggregation Design Wizard, 1513                     ApplicationName data column (SQL Profiler), 1169
aging, query plans, 1075                            Applications, creating via SSMS for SSNS,
alert mail notifications, SQL Server Agent Mail,      CD:1866-1868
  356-357                                           Applications ICF elements, CD:1863-1865
alert responses, 384-386                            APPLY, 1309-1311
Alert System page, 363                              architecture
alerts, 361, 1237                                       delivery architecture, subscriptions, 1650
    creating with SQL Server Agent Mail, 356-357        SQL Server instance architecture, CD:1694
    scripting, 387                                      of SQL Server Profiler, 112-113
    SQL Server Agent, 381-386                           SSIS (SQL Server Integration Services),
alias data types, 588                                     1545-1549
AllocUnit locking level (SQL Server), 1177          archiving data, VLDB data maintenace, CD:1755-1761
ALLOW_PAGE_LOCKS, 1184                              arguments for CREATE INDEX, 628
ALTER, 267                                          articles, 420
ALTER ASSEMBLY command, 1333                            data replication, 421
ALTER DATABASE, 548, 560, 563, 573-574                       filtering, 421-425
ALTER FULLTEXT INDEX, CD:1924-1926                  AS DEFAULT, CREATE FULLTEXT CATALOG, CD:1918
ALTER INDEX, 46, 624, 633, 1184                     AS HTTP, 1445-1448
ALTER INDEX … REORGANIZE, CD:1754                   ASP.NET report controls, using in websites, 1654-1659
ALTER INDEX REBUILD, 635, CD:1753                   assemblies, creating managed stored procedures,
ALTER INDEX REORGANIZE, 953                           1342-1343
ALTER PARTITION SCHEME, 615                         Assert iconm Query Analyzer, 1123
ALTER PROCEDURE, 722                                association rules, data mining algorithms, 1527
ALTER ROLE, 277                                     asynchronous operations, database mirroring
ALTER TABLE, creating constraints, 597                operating modes, 486
    T-SQL, 653                                      asynchronous statistics updating, indexes, 992-993
ALTER VIEW, 681                                     Attach Databases dialog, 575
altering                                            ATTACH REBUILD LOG, 548
    indexes, 624                                    attaching
    views with T-SQL, 681-682                           databases, 574-576
American National Standards Institute (ANSI), 257       full-text catalogs, CD:1938
analysis phase, transaction recovery process, 890   attachments
Analysis Services, 184                                  sending CSV data as, 353
    enhancements, 52-53                                 sending XML as, 351
    upgrading, 217                                  attributes, managed stored procedures,
Analysis Services (SSAS), migrating, 209-210          1335-1336, 1339
Analysis Services features, sac, 102                authentication, 249
Analysis Services Migration Wizard, 209                 authentication modes, setting, 250-251
Analysis Wizard, 199-202                                mixed authentication mode, 250
                                                        securing reports, 1648
                                                        Windows Authentication mode, 250
                                                    AUTHORIZATION, 1445
                                                        CREATE FULLTEXT CATALOG, CD:1918
                                                    AUTHORIZATION clause, CD:1882
                                   browsing data within cubes in OLAP database creation                 1943



AUTHORIZATION keyword, CD:1903                             replication monitoring, 478-479
Auto Close, 561                                            scenarios
Auto Create Statistics, 562, CD:1752                            copy backups, 316-317
AUTO mode, XML, 1385-1389                                       differential backups, 312-313
Auto Shrink, 562                                                file/filegroup backups, 315-316
Auto Update Statistics, 562-563, CD:1752                        full database backups, 311
auto-parameterization, 1109                                     full database backups with transaction log
auto-update statistics, monitoring, 150                            backups, 311-312
AutoCommit, processing transactions, 876                        mirrored backups, 316
automatic checkpoints, logs, 888-889                            partial backups, 313-315
automatic query plan recompilation, stored                      system database backups, 317
  procedures, 765-766                                      snapshot backups, 337
automatically updating index statistics, 990-992           standby servers, 336-337
AUTOSHRINK, 568                                            transaction log backups, 296
availability, enterprise computing, 515                    transaction logs, 307
Average Wait Time counter (SQLServer:Locks                      creating with SSMS, 308
  object), 1171                                                 creating with T-SQL, 309-310
avoiding unnecessary function executions, T-SQL,       backupset, 325
  CD:1809                                              BASIC authentication, 1445
AWE (Advanced Windowing Extensions) enabled,           .BAT, 90
  CD:1707-1709                                         batches
                                                           bulk-copy operations, SSIS, 1602
B                                                          transactions, 897-899
                                                       bcp (Bulk Copy Program), 104-105, 1539
B-tree indexes, 971
                                                           hints, supplying to, 1603-1604
backing up
                                                       bcp utility, SSIS, 1586-1588
    databases, Maintenance Plan Wizard, 948-951
                                                           exporting/importing data, 1589-1591
    system tables, 325-326
                                                           file data types, 1591
    tail of transaction logs, 330
                                                           format files, 1591-1601
    VLDB, CD:1745-1747
                                                           views, 1601
         snapshot backups, CD:1748
                                                       BEGIN CONVERSATION DIALOG, CD:1890-1892
BACKUP, 305
                                                       BEGIN DISTRIBUTED TRANSACTION, 755
BACKUP CERTIFICATE, 235, CD:1902
                                                       BEGIN TRAN statements, 884
BACKUP DATABASE, 305-306
                                                           transactions, 909
backup devices, 300-302
                                                       BEGIN TRANSACTION, 755
BACKUP LOG, 305, 309
                                                       BI (business intelligence), 1473
backupfile, 325
                                                       bidirectional traffic, 245
backupfilegroup, 325
                                                       BIDS (Business Intelligence Development
backupmediafamily, 325
                                                         Studio), 1610
backupmediaset, 325
                                                       BIDS Report Designer, designing reports, 1619
backups, 291
                                                           adding interactivity, 1625-1626
    considerations for very large databases, 337-338
                                                           building reports, 1620
    copy-only backups, 48, 292, 296
                                                           creating simple lists, 1620-1621
    creating
                                                           deploying sample reports, 1626
         with SSMS, 302-305
                                                           grouping and sorting, 1621-1623
         with T-SQL, 305-307
                                                           layout choices, 1619-1620
    developing a plan, 292-293
                                                           queries, 1619
    differential database backups, 295
                                                           tables and hierarchies, 1623-1624
    differential partial backups, 295
                                                       binary columns, RAW XML mode, 1383
    enhancements to, 48-49
                                                       binding multiple applications, 917
    file and filegroup backups, 295-296
                                                       bipubs2005 database, installing, 6
    frequency of, 335-336
                                                       blobs, CREATE FULLTEXT INDEX, CD:1920
    full database backups, 294
                                                       bottlenecks, identifying with SQL Server Profiler,
    full-text catalogs, CD:1938
                                                         148-149
    HA, 397
                                                       bound connections, 915-917
    maintenance plans, 338
                                                       bound defaults, 662-663
    mirrored backups, 48, 292
                                                       breaking down database snapshots, 938
    new features, 291-292
                                                       Browser role, 1647
    partial backups, 49, 292, 295
                                                       browsing data within cubes in OLAP database
    recovery models, 296
                                                         creation, 1514-1518




             How can we make this index more useful? Email us at indexes@samspublishing.com
1944       built-in methods


built-in methods, new xml data type built-in methods,     checkpoints, 555
 1411-1430                                                    logs, 886-889
built-in roles, securing reports, 1647-1648               CHECK_CONSTRAINTS hint, supplying to bulk-copy
BUILTIN\Administrators, 253                                 operations, 1604
Bulk Copy Program (bcp), 1539                             choosing disaster recovery patterns, CD:1777-1778
bulk update locks, 1176                                   CLEAR PORT, 1446
bulk-copy operations, SSIS, 1601-1604                     client access provisioning, 225-227
bulk-logged recovery model, 298-299                       client applications, redistributing SNAC, 231
Bulk-Logged recovery setting, 559                         client components, 184
bulkadmin, 259                                                installing, 1613
Business Intelligence Development Studio (BIDS), 44,      client data access technologies, 237
 219, 1610                                                    drivers, 238
business intelligence (BI), 1473                              JDBC, 244
                                                              MDAC, 242-243
C                                                             .NET Framework, 240-241
                                                              .NET Framework (SQLCLR), 241-242
C# client applications, examples
                                                              providers, 237
    calling web methods-bound stored procedures
                                                              SNAC, 238-240
       that return XML, 1462-1465
                                                          client tools, client installation, 229
    running a web method bound to a stored
                                                          clients
       procedure from C#, 1453-1458
                                                              configuring, 231
    running ad hoc T-SQL batches from SQL Server
                                                                  connection encryption, 235-236
       web services, 1458-1463
                                                                  SSCM, 232-234
cached reports, 1651-1652
                                                              installing, 228-230
caches, procedure caches, 763-764
                                                              setup and configuration for database mirroring,
caching, query plan caching, 763, 1072-1073
                                                                509-511
calculated members (calculations), OLAP database
                                                          CLR (common language runtime), 37-38, 589-590,
  creation, 1521-1522
                                                            799, 1331
calculating dates, CD:1817-1820
                                                          CLR enabled, 1709
calling stored procedures from transactions, 783-786
                                                          CLR functions, 827-828
CAS (Code Access Security) permission sets, .NET
                                                              adding to databases, 828-829
  Framework, 1332
                                                              versus T-SQL functions, 830-831
CASCADE, 648
                                                          CLR stored procedures, 791-793
cascading deletes, 1214
                                                          CLR triggers, 834, 866-869
    DML triggers, 847-849
                                                          CLR user-defined data types, 589-590
cascading FOREIGN KEY constraints, 850
                                                          Cluster Administrator, viewing properties, 537
cascading referential integrity, FOREIGN KEY
                                                          Clustered Index Delete icon, Query Analyzer, 1124
  constraint, 648-650
                                                          Clustered Index Scan icon, Query Analyzer, 1124
cascading updates, DML triggers, 849-850
                                                          clustered indexes, 624-625, 971-973
CASE, 1301
                                                              costs, 1041
catalog views, 156, 164-166
                                                              designing, 1014-1016
    system stored procedures and, 1466-1467
                                                              indexed views, 1021-1022
    viewing DDL triggers, 866
                                                          clustering, 518
CATCH, 786
                                                              building solutions with HA options, 402-404
CATCH block, 787
                                                          clusters, 518
central publisher replication model, data replication,
                                                              server clusters, 518
  426-427
                                                              upgrading, 219
central publisher with remote distributor replication
                                                          Code Access Security (CAS) permission sets, .NET
  model, data replication, 427
                                                            Framework, 1332
central subscriber replication model, data replication,
                                                          Codezone Community, 62
  428-430
                                                          coding recommendations for T-SQL. See T-SQL, coding
certificates
                                                            recommendations
    conversation encryption, SQL Server Service
                                                          coding transactions, 912-913
       Broker, CD:1901-1908
                                                          Collapse icon, Query Analyzer, 1124
    root-level certificates, 235
                                                          collation, 188, 558
CHARACTER SET keyword, 1452
                                                          column lists, T-SQL, CD:1794-1796
CHECK constraint, 596, 650-651
                                                          COLUMN NAME, CREATE FULLTEXT INDEX, CD:1919
checkpoint duration option, transaction
                                                          column operator values, sp trace setfilter, 139
  management, 873
                                                          column sub-setting, 1643
                                                          ColumnCount parameter (SqlTriggerContext), 1367
                                                          COLUMNPROPERTY, full-text indexes, 1929
                                                                           connection pooling            1945



columns, 579, 584                                       CompSales International (OLAP requirements
    adding in T-SQL, 600-601                              example), 1485-1486
    checking for updates, 843-845                           cube creation, 1486-1487
    computed columns                                        cube perspectives, 1524-1525
         denormalization, 1220-1221                         data mining, 1526-1534
         indexes, 1022-1024                                 generating relational databases, 1523-1524
    data types. See data types                              KPIs, 1525
    indexes                                                 OLAP database creation. See databases, OLAP
         included columns, 1020                                database creation
         joins, 1014                                        security and roles, 1536-1537
    naming, 585                                             SQL Server BIDS, 1487-1488
    new xml data type, 1404-1406                        Compute Scalar icon, Query Analyzer, 1125
    properties, 590                                     computed columns, 593-594
         changing with T-SQL, 599                           denormalization, 1220-1221
         computed columns, 593-594                          indexes, 1022-1024
         IDENTITY, 591-592                                  SARG, 1038-1040
         NULL and NOT NULL keywords, 590                Concatenation icon, Query Analyzer, 1125
         ROWGUIDCOL, 592-593                            condition action, 1845
    renumbering, 1599-1601                              condition expression, 660
    selecting from sys.databases catalog view, 566      configuration, SSIS, 1548
    statistics, generating, 990-996, 998                configuration data, CD:1780
    stored computed columns, 580                        configuration options
    traces (SQL Server Profiler), 137-138                   fixing incorrect option settings, CD:1702
    xml columns                                             for instances, CD:1695-1702
         full-text indexing, 1436                           obsolete configuration options, CD:1703
         indexing, 1430-1436                                performance. See performance, configuration
command lines, DTA, CD:1737-1742                               options
command sourcing phase (dtexec), 1576                       setting with SSMS, CD:1702-1703
command-line utilities                                  configuration phase (dtexec), 1576
    bcp, 104-105                                        Configure Database Mirroring Security Wizard,
    dta, 90, 95-98                                        495-501
    dtswizard, 91                                       configuring
    installation locations, 90                              clients, 231
    new features, 90-91                                          connection encryption, 235-236
    profiler, 90-91                                              SSCM, 232-234
    sac, 91, 101-104                                        database mirroring, 486-490
         parameters, 102                                         client setup, 509-511
    sqlcmd, 90-92                                                Configure Database Mirroring Security Wizard,
         executing, 92-94                                          495-501
         scripting variables with, 94-95                         creating database on the mirror server,
    sqldiag, 105-107                                               493-495
    sqlservr, 107-108                                            creating endpoints, 490-492
    sqlwb, 91                                                    granting permissions, 492
    tablediff, 90, 98-101                                        identifying endpoints, 492-493
    utilities that have been removed or deprecated in       linked servers, CD:1682-1683
      SQL Server 2005, 108                                  Reporting Services, 1615
command-prompt utilities, SSIS, 1552                             report server database catalog, 1617-1618
commenting, T-SQL code, 1806-1807                                report server encryption, 1616
common language runtime, 37-38, 589-590,                         Reporting Services Configuration tool, 1615
  799, 1331                                                      Surface Area Configuration tool, 1618
common table expressions. See CTE                                Web service, 1617
Community Technology Preview (CTP), 178-179                      Windows service, 1616
compatibility, locks, 1187-1188                             SQL Server Agent, 362
compatibility levels, 209                                        email notification, 365-366
compatibility views, 155, 161-162                                properties, 362-363
compiling                                                        proxy accounts, 367-368
    DML statements, query compilation, 1031                      startup account, 363-365
    query plans, 1076-1077                                  SQL Server database disks, 525-526
Components to Install dialog, 529                       connection encryption, configuring clients, 235-236
composite indexes, designing, 1013                      Connection page, 363
COMPRESSION, 1448                                       connection pooling, ADO.NET, 1328



             How can we make this index more useful? Email us at indexes@samspublishing.com
1946      Connection Test Program for SQL Server Cluster


Connection Test Program for SQL Server Cluster,           creating remote service bindings, CD:1908
  539-542                                                 planning between services, SQL Server Service
connections, bound connections, 915-917                     Broker, CD:1890-1892
connectivity, 245-246                                 conversion deadlocks, 1194
consistency                                           converting dates for comparison, T-SQL,
   immediate transactional consistency, 443             CD:1820-1822
   latent transactional consistency, 443              Copy Database Wizard, 206-208
   VLDB, CD:1749-1751                                 copy-on-write technology, 394, 924-927
Constant Scan icon, Query Analyzer, 1125                  database mirroring, 484
constraints                                           copy-only backups, 48, 292, 296
   CHECK, 650-651                                         scenarios, 316-317
   creating                                           copying packages, dtutil utility, 1582-1585
        with ALTER TABLE, 597                         correlated subqueries, 1068-1070
        with CREATE TABLE, 596                        cost, parallelism, CD:1709-1710
   data integrity. See data integrity, constraints    covering indexes, 1018-1019
   defining table constraints, 596-597                CREATE ASSEMBLY, 792, 828
   FOREIGN KEY, 646-648                               CREATE ASSEMBLY command, 1333
        cascading referential integrity, 648-650      CREATE CERTIFICATE, 235, CD:1901
   PRIMARY KEY, 643-644                               CREATE DATABASE, 16, 551
   UNIQUE, 645-646                                        T-SQL, 559-560
construction phase, OLAP design                       CREATE ENDPOINT, T-SQL statement, 1442
  methodologies, 1484                                 CREATE ENDPOINT keywords, 1444
constructs, SQL Server Service Broker, CD:1881        CREATE FULL TEXT, 1916
   creating queues for message storage,               CREATE FULLTEXT CATALOG, CD:1916-1918
     CD:1887-1889                                     CREATE FULLTEXT INDEX, CD:1919-1924
   defining messages and choosing message types,      CREATE FUNCTION
     CD:1882-1886                                         custom function templates, 817
   defining services to send and receive messages,        inline table-valued functions, 808
     CD:1889-1890                                         multistatement table-valued functions, 808
   planning conversations between services,               scalar functions, 807
     CD:1890-1892                                     CREATE INDEX, 628-629
   setting up contracts for communication,            CREATE INDEX WITH DROP EXISTING, 635
     CD:1886-1887                                     CREATE LOGIN, CD:1670
containers, SSIS, 1546                                CREATE PARTITION FUNCTION, 608-609, 615
CONTAINS, FTS, CD:1933-1936                           CREATE PARTITION SCHEME, 615
CONTAINSTABLE, FTS, CD:1933-1937                      CREATE PROCEDURE, 707, 792
Content Manager role, 1647                            CREATE STATISTICS command, 996-998
contention, locking, 1188-1189                        CREATE TABLE
   identifying, 1189-1191                                 creating constraints, 596
   minimizing, 1192-1193                                  T-SQL, 582-584, 652
context connection, managed stored procedures, 1340   credentials, 367
CONTEXT INFO, T-SQL, CD:1824-1825                     CROSS APPLY, 1309-1311
context switching, 286-288                            crosstabs, 1306
contracts, setting up for communication (SQL Server   CSV, sending data as an attachment with Database
  Service Broker), CD:1886-1887                         Mail, 353
CONTROL, 267                                          CTE (common table expressions), 1284-1286
control flow, SSIS, 1546                                  recursive queries, 1286-1288
CONTROL keyword, 1469                                          expanding hierarchies, 1288-1294
control of flow, 376                                           MAXRECURSION, 1294-1295
controlling                                           CTP (Community Technology Preview), 178-179
   access permissions, 1468-1469                      cube perspectives, 1524-1525
   access to data, with views, 671-674                Cube Wizard, 1505
conversation encryption, certificates (SQL Server     cubes
  Service Broker), CD:1901-1908                           aggregating with data in OLAP database
conversation initiators, creating, CD:1892-1896             creation, 1510-1514
conversation targets, creating, CD:1896-1901              browsing data in OLAP database creation,
conversations, CD:1890                                      1514-1518
   building routes to map conversations between           building and deploying in OLAP database creation,
     SQL Server instances, CD:1908                          1506-1508
   creating conversation initiator, CD:1892-1896          creating in OLAP database creation, 1503-1507
   creating conversation targets, CD:1896-1901            OLAP cube creation, 1486-1487
                                                                               data replication        1947



    populating with data in OLAP database creation,        providers, 237
      1509-1510                                            SNAC, 238
    SSAS, 1477-1480                                        SNAC (ADO), 240
CURSOR, stored procedures, 738-743                         SNAC (ODBC), 239-240
CURSOR STATUS, 740                                         SNAC (OLE DB), 239
cursor threshold, CD:1710-1711                          data archival design changes, VLDB data
cursors                                                  maintenance, CD:1760
    stored procedures, 733-738                          data characteristics, data replication, 448-450
         CURSOR variables, 738-743                      data columns, SQL Profiler, 1168
    T-SQL, performance, CD:1810-1813                       traces, 118-120
custom (files-only) installation, Reporting Services,   Data Definition Language (DDL), 1273, 1404
  1613-1615                                             data distribution, data replication, 444
custom function templates, creating, 815-818            data files, 548
custom managed database objects                         data flow, SSIS, 1547
    managed triggers, 1366-1372                         data flow task, SSIS, 1547
    permissions, 1332-1334                              data integrity, 641
    related system catalogs, 1374-1375                     constraints, 643, 656
    stored procedures, 1335-1336, 1339-1344                     CHECK, 650-651
         assembly creation, 1342-1343                           creating, 651
         attributes, 1335-1336, 1339                            creating with SSMS, 653-656
         context connection, 1340                               creating with T-SQL, 651-653
         debugging, 1343-1344                                   disabling, 659
         implementation contract, 1335-1336, 1339               dropping, 658-659
         Microsoft.SqlServer.Server objects,                    FOREIGN KEY, 646-650
            1340-1341                                           gathering constraint information, 657-658
    transactions, 1372-1374                                     PRIMARY KEY, 643-644
    UDAs, 1363-1366                                             UNIQUE, 645-646
    UDFs                                                   Database Engine, 14
         scalar UDFs, 1344-1347                            declarative data integrity, 643
         TVFs, 1348-1349, 1352-1353                        domain integrity, 642
    UDTs, 1354-1355, 1359-1363                             enforcing, 642-643
    Visual Studio 2005, 1334-1335                          entity integrity, 642
customizing                                                new features, 641-642
    drill-through reports, 1640-1642                       procedural data integrity, 643
    format files, bcp utility, 1591-1592                   referential integrity, 642
cycle deadlocks, 1193-1194                                 rules, 659-660
                                                        Data Junction, 1539
D                                                       data maintenance, VLDB, CD:1751
                                                           purging/archiving data, CD:1755-1761
DAC (Dedicated Administrator Connection), 39
                                                           rebuilding indexes, CD:1752-1755
data
                                                           updating statistics, CD:1751-1752
   application data values, CD:1780
                                                        data manipulation, simplifying with views, 669-670
   configuration data, CD:1780
                                                        Data Manipulation Language. See DML
   controlling access to, with views, 671-674
                                                        data mining, 1526-1534
   delivering to users, in OLAP database creation,
                                                           OLAP performance, 1535-1536
     1518-1519
                                                           SSIS, 1534-1535
   metadata, CD:1780
                                                        Data Mining Wizard, 1528-1530
   purging/archiving, VLDB data maintenance,
                                                        data modifications, views and, 683-684
     CD:1755-1761
                                                        data partitioning
data abstraction, views, 670-671
                                                           HA, 410
data access, Database Engine, 13
                                                           horizontal data partitioning, denormalization,
data access technologies, clients, 237
                                                              1221-1223
   drivers, 238
                                                           vertical data partitioning, denormalization,
   JDBC, 244
                                                              1223-1224
   MDAC, 242-243
                                                        data replication, 417-418, CD:1780-1781
   MDAC (ODBC), 243
                                                           articles, 421
   MDAC (OLE DB), 243
                                                                filtering, 421-422, 424-425
   .NET Framework data provider for SQL Server,
                                                           building solutions with HA options, 404-406
     240-241
                                                           central publisher replication model, 426-427
   .NET Framework data provider for SQL Server
                                                           central publisher with remote distributor
     (SQLCLR), 241-242
                                                              replication model, 427



              How can we make this index more useful? Email us at indexes@samspublishing.com
1948      data replication


   central subscriber replication model, 428-430         data-centric approach to disaster recovery,
   database mirroring and, HA, 480                        CD:1779-1780
   distribution server, 419                              data-driven subscriptions, 1649-1650
   log shipping, 480                                     DataAccess parameter (scalar UDFs), 1345
   merge replication, 446-447                            DataAccess parameter (SqlMethod), 1359
   methods of data distribution, 444                     Database ADF element, 1851
   monitoring, 471                                       database backups. See backups
        backup and recovery, 478-479                     database compatibility levels, 209
        in heterogeneous environments, 477-478           Database Console Commands. See DBCC
        Performance Monitor, 477                         database design
        within SQL Server Management Studio,                denormalization, 1218-1219
          474-476                                                computed columns, 1220-1221
        SQL statements, 472-475                                  horizontal data partitioning, 1221-1223
        troubleshooting replication failures, 476-477            redundant data, 1219-1220
   multiple publishers or multiple subscribers                   summary data, 1221
      replication model, 429-431                                 vertical data partitioning, 1223-1224
   new features, 416-417                                         zero-to-one relationships, 1224-1225
   peer-to-peer replication model, 431-433                  designing for performance, 1214
   performance, 479-480                                     logical database design issues, 1215-1217
   planning for, 443                                        new features, 1213-1214
   publication server, 418-420                           database design changes, VLDB data maintenance,
   publications, 421                                      CD:1760
   publisher subscriber replication model, 427-428       Database Diagram Editor
   replication agents, 436-439                              creating constraints, 655
   scripting replication, 470-471                           creating tables, 580-581
   setting up, 450-451                                   database diagrams, modifying tables, 604-605
        creating distributors and enabling publishing,   Database Engine, 11
          451-455                                           access to data, 13
        creating publications, 456-462                      data integrity, 14
        creating subscriptions, 465-469                     storage, 12
        horizontal and vertical filtering, 463-465       database engine enhancements, 46
   snapshot replication, 444-445                         Database engine features, sac, 102
   subscription server, 420                              Database Engine Tuning Advisor, 978
   subscriptions. See subscriptions                         analyzing trace output, 128-129
   transactional replication, 445-446                    Database Engine Tuning Advisor. See DTA
   updating subscribers replication model, 430-432       database file movement, 46, 548
   user requirements, 447-448                            database files, 549-550
        data characteristics, 448-450                       filegroups, 551-553
Data Source View Wizard, 1492                               master database files, 557
data source views, creating, 1490-1493                      partitions, 554
data sources, adding to OLAP databases, 1488-1490           primary data files, 550
data storage, 548-549                                       secondary data files, 550
data transformation requirement, SSIS, 1555-1556            transaction log files, 554-555
Data Transformation Services. See DTS                    Database ICF elements, CD:1865
data types, 44, 585-587                                  database integrity, Maintenance Plan Wizard, 951
   CLR user-defined data types, 589-590                  database level, SSMS, managing permissions,
   file data types, bcp utility, 1591                     281-283
   large row support, 588                                Database locking level (SQL Server), 1177
   large-value data types, 580, 587-588                  Database Mail, 40, 339, 365
   new xml data type, 1402-1403                             deleting mail objects, with T-SQL, 345
        built-in methods, 1411-1430                         mail configuration objects, viewing, 357-358
        columns, 1404-1406                                  mail message data, viewing, 359-360
        schema collections, 1407-1412                       mail profiles and accounts, creating, 342-344
   Object Explorer (SSMS), 587                              receiving email, 354
   search argument problems, 1093                           security profiles, 344
   user-defined data types, 588-589                         sending email, 347-348, 351-353
   varbinary(max), 45                                       sending XML as attachments, 351
   varchar(max), 45                                         Service Broker (SSB), 347
   xml, 45, 588                                             setting up, 339-342
                                                            sp send dbmail, parameters for, 348
                                                            systemwide mail settings, 345-346
                                                                                      databases          1949



   testing setup, 346                                      creating, 932-937
   updating, with T-SQL, 345                               HA, 408-410
Database Mail Configuration Wizard, 339                    limitations and restrictions, 925-926
   SMTP accounts, creating, 343                            from mirrors, using for reporting, 512-513
database maintenance, 945-946                              new features, 920
   Maintenance Plan Wizard, 946-947                        number of database snapshots per source
        backing up databases, 948-951                        database, 943
        checking database integrity, 951                   reciprocal principal/mirror reporting confirmation,
        maintaining indexes and statistics, 953-956          941-942
        scheduling maintenance plans, 956-959              reverting to, 924
        shrinking databases, 952-953                       reverting to for recovery, 938
   without a plan, 965                                     security, 942
database maintenance plans, 946                            setting up against database mirrors, 940-941
database management system (DBMS), 1152                    sparse file size management, 943
database mirroring, 19, 41, 481-482, CD:1783               testing with QA, 939-940
   building solutions with HA options, 407                 when to use, 927-931
   client setup and configuration, 509-511              DATABASEPROPERTYEX, 564-566
   copy-on-write technology, 484                        databases
   data replication and, HA, 480                           adding CLR functions to, 828-829
   failovers, 486                                          adding CLR stored procedures to, 792-793
   mirror database server, 483                             ALTER DATABASE, 573-574
   monitoring, 501-505                                     attaching, 574-576
   operating modes, 485-486                                creating, 555-556
   principal database server, 482                              with SSMS, 556-559
   removing, 505-507                                           with T-SQL, 559-560
   replication and, 511-512                                data structure, 548-549
   role switching, 486                                     defined, 547
   roles, 485                                              detaching, 574-576, CD:1791
   setting up and configuring, 486-490                     expanding, 567-568
        Configure Database Mirroring Security Wizard,      managing file growth, 566-567
          495-501                                          migrating, side-by-side migration. See side-by-side
        creating endpoints, 490-492                          migration
        creating the database on the mirror server,        moving, 572
          493-495                                          new features, 548
        granting permissions, 492                          OLAP database creation, 1488
        identifying endpoints, 492-493                         adding data sources, 1488-1490
   testing failover, 507-508                                   ADO MD, 1521
   when to use, 484-485                                        aggregating data within the cube, 1510-1514
   witness database server, 483                                browsing data in the cube, 1514-1518
Database Mirroring Monitor, 501-505                            building and deploying the cube, 1506-1508
database mirrors                                               calculated members (calculations),
   providing reporting databases, database                       1521-1522
     snapshots, 930-931                                        creating data source views, 1490-1493
   setting up database snapshots against, 940-941              creating the cube, 1503-1504, 1506-1507
database options                                               defining dimensions and hierarchies,
   retrieving option information, 564-566                        1493-1502
   setting, 560-563                                            delivering data to users, 1518-1519
   in SSMS, 561                                                multidimensional expressions, 1519-1521
database partitioning for VLDB, CD:1762                        populating cubes with data, 1509-1510
   federated servers, CD:1767-1768                             query analysis and optimization, 1523
   horizontal data partitioning, CD:1765-1767              relational databases, 1523-1524
   table and index partitioning, CD:1768-1770              replicated databases, upgrading, 217
   vertical data partitioning, CD:1763-1765                shrinking, 568
Database Properties dialog, 561                                DBCC SHRINKDATABASE, 568-569
Database Read-Only, 562                                        DBCC SHRINKFILE, 569-571
database roles, managing, 276-277                              log files, 571
Database services, 184                                         in Maintenance Plan Wizard, 952-953
database snapshot sparse files, 924                            with SSMS, 571-573
database snapshots, 41, 292, 919-924                       system databases, 157-159, 547
   breaking down, 938                                      transaction management, checkpoint duration
   copy-on-write technology, 926-927                         option, 873



             How can we make this index more useful? Email us at indexes@samspublishing.com
1950     databases


    upgrading, 216-217                            Decision Support Systems (DSS), 526
    user databases, 547                           decision trees, data mining algorithms, 1527
Datasets window, 1621                             declarative data integrity, implementing, 643
date calculations, T-SQL, CD:1817-1822            declarative defaults, 661-662
DATEADD, CD:1817                                  declarative referential integrity (DRI), 835
DATEDIFF, CD:1817                                 Dedicated Administrator Connection (DAC), 39
datediff(), 1145                                  dedicated administrator connections, HA, 410
db backupoperator, 260                            DEFAULT, 661, 663-664
db datareader, 260-263                            DEFAULT constraint, 596
db datawriter, 260-262                            default full-text language, CD:1711-1712
db ddladmin, 260                                  default installation, Reporting Services, 1613-1614
db denydatareader, 260                            default language, CD:1712-1713
db denydatawriter, 260                            defaults, 661
db owner, 260                                         application of, 663-664
db securityadmin, 260                                 bound defaults, 662-663
DBCC (Database Console Commands), CD:1749-1751        declarative defaults, 661-662
    examining performance, 1261                       restrictions on, 664-665
        DBCC SQLPERF, 1262-1263                   deferred name resolution, stored procedures, 715-717
        INPUTBUFFER, 1265                             identifying objects referenced in stored
        OUTPUTBUFFER, 1265                              procedures, 717-719
        PERFMON, 1263                             Deleted Scan icon, Query Analyzer, 1126
        PROCCACHE, 1264-1265                      deleted tables, DML triggers, 841-843
        SHOWCONTIG, 1263-1264                     deleting
DBCC DROPCLEANBUFFERS, 1144                           logins, SSMS, 271
DBCC FREEPROCCACHE, 1082                              mail objects from Database Mail, with T-SQL, 345
DBCC MEMORYSTATUS, 1252                               packages, dtutil utility, 1582-1585
DBCC OPENTRAN command, 913                        delivery architecture, subscriptions, 1650
DBCC PERFMON, 1241                                DeliveryChannels ICF elements, CD:1865
DBCC PROCCACHE, 764                               denormalization, 1218-1219
DBCC SHOWCONTIG command, 1001                         computed columns, 1220-1221
DBCC SHOW_STATISTICS command, 982-984                 horizontal data partitioning, 1221-1223
DBCC SHRINKDATABASE, 568-569                          redundant data, 1219-1220
DBCC SHRINKFILE, 569-571                              summary data, 1221
DBCC SQLPERF, 1262-1263                               vertical data partitioning, 1223-1224
dbcreator, 259                                        zero-to-one relationships, 1224-1225
DBMS (database management system), 1152           DENSE RANK, 1298-1299
dbo users, 255                                    densities, indexes, 987-988
DB_accessadmin, 260                               DENY, 266, 285
DDL (Data Definition Language), 1273, 1404        deploying
DDL statements, 860                                   cubes in OLAP database creation, 1506-1508
DDL triggers, 834, 859-861                            reports, 1646
    creating, 861-864                                     with BIDS Report Designer, 1626
    managing, 864-866                             design
de-duping data with ranking functions, T-SQL,         databases. See database design
  CD:1837-1840                                        indexes, 1013-1014, 1213
Deadlock Graph event (SQL Profiler), 1168                 clustered indexes, 1014-1016
deadlocks, 1193-1195                                      composite indexes, 1013
    1204 trace flags, setting, 1197-1199                  covering, 1018-1019
    1222 trace flags, setting, 1199                       included columns, 1020
    avoiding, 1195-1196                                   multiple indexes, 1020-1021
    conversion deadlocks, 1194                            nonclustered indexes, 1016-1017
    cycle deadlocks, 1193-1194                            wide indexes, 1020-1021
    examining, 1196-1197                              for VLDB, CD:1761-1762
    handling, 1196-1197                                   database partitioning. See database
    monitoring, Server Profiler, 1200-1203                  partitioning for VLDB
    SQL Server Profiler, 145-147                  design methodologies for OLAP, 1482-1485
debugging                                         design phase, OLAP design methodologies, 1484
    managed code, 1343-1344                       design surface, Report Builder, 1634-1635
    models and model queries, 1636-1638           Designer IDE (SSIS), 1551-1552, 1566-1574
    stored procedures, with Visual Studio .NET,
      756-760
                                                                                 DML triggers         1951



designing                                                  Level 2, CD:1773
    example systems, SQL Server Service Broker,            Level 3, CD:1773
      CD:1880-1881                                         Level 4, CD:1773
    reports with BIDS Report Designer, 1619                Microsoft Virtual Server 2005, HA, 412
        adding interactivity, 1625-1626                    options for, CD:1780-1783
        building reports, 1620                             patterns, CD:1773-1778
        creating simple lists, 1620-1621                   planning and executing, CD:1790
        deploying sample reports, 1626                     recovery objectives, CD:1778-1779
        grouping and sorting, 1621-1623                    reverting to database snapshots, 927-928, 938
        layout choices, 1619-1620                               source databases, 938-939
        queries, 1619                                      third-party alternatives, CD:1791
        tables and hierarchies, 1623-1624              disaster recovery planning, new features, CD:1772
    reports with models, 1629-1630                     disaster recovery process, CD:1784
    reports with Report Builder, 1627-1629                 focus of, CD:1784-1788
detaching databases, 574-576, CD:1791                      SQLDIAG.EXE, CD:1788-1790
detachment of full-text catalogs, CD:1938              disconnected editing, Query Editor (SSMS), 74
developing                                             discouraging grouping in models, 1636
    custom managed database objects                    discovery, 1440
        managed triggers, 1366-1368, 1370-1372         disk activity, monitoring, 1259
        permissions, 1332-1334                         disk devices, 300
        related system catalogs, 1374-1375             Disk Queue Length counter, 551
        stored procedures, 1335-1336, 1339-1344        disk systems, monitoring, 1254-1257
        transactions, 1372-1374                        diskadmin, 259
        UDAs (user-defined aggregates), 1363-1366      DISTINCT, CD:1808
        UDFs (user-defined functions), 1344-1349,          query processing, 1084
          1352-1353                                    distribute streams, 1089
        UDTs (user-defined types), 1354-1355,          Distribute Streams, Query Analyzer, 1129
          1359-1363                                    distributed messaging, CD:1875
        Visual Studio 2005, 1334-1335                  distributed partitioned views (DVPs), 688-690, 858
development tools, SSMS, 71                                horizontal data partitioning, CD:1767
    integrating with source control, 81-83             distributed queries, linked servers, CD:1672
    managing projects, 79-81                           Distributed Transaction Coordinator (DTC), 443, 516
    Query Editor, 71-72. See also Query Editor, SSMS   distributed transactions, 444
    templates, 83-86                                       linked servers, CD:1672-1673
device CALs, 30                                            managing, 918
diagnostics, full-text indexes, CD:1927-1930           distribution agent, 441
dialog handles, CD:1890                                distribution clean up, distribution, 442
DialogTimer, CD:1886                                   distribution database, 159, 435-436
differential backups, scenarios, 312-313               distribution server, data replication, 419
differential database backups, 295                     distributors, creating for data replication, 451-455
differential partial backups, 295                      Distributors ADF element, CD:1862-1863
DIGEST authentication, 1445                            dm exe sql text, 764
DIGEST authentication, 1446                            dm exec cached plans, 764
Dimension Wizard, 1495, 1499                           dm exec plan attributes, 764
dimensions, defining in OLAP database creation,        DML (Data Manipulation Language), 1273
  1493-1502                                                max, 1274-1276
direct recursion, 871                                      new features, 1273
dirty pages, 555                                           OUTPUT, 1280-1284
dirty reads, transaction isolation levels, 1153                ,
                                                           TOP 1276-1280
Dirty Writer process, logs, 886-889                        xml data type, 1274
DISABLE, ALTER FULLTEXT INDEX, 1924                    DML statements, compiling (query compilation), 1031
DISABLE BROKER, 1877                                   DML triggers, 834-835
disabling                                                  AFTER triggers, 837-839
    constraints, 659                                            executing, 839
    indexes, 1011-1012                                          special considerations, 840-841
disaster recovery                                               trigger firing order, 840
    approaching, CD:1772-1773                              cascading deletes, 847-849
    data-centric approach, CD:1779-1780                    cascading updates, 849-850
    Level 0, CD:1773                                       creating, 835-837
    Level 1, CD:1773




             How can we make this index more useful? Email us at indexes@samspublishing.com
1952      DML triggers


     inserted and deleted tables, 841-843             email
         checking for column updates, 843-845             receiving, 354
     referential integrity, 845-847                       sending, 347-348, 351-353
DMVs (Dynamic Management Views), 38, 156,                 SQL Server Agent Mail. See SQL Server Agent
  168-170, CD:1909                                           Mail, 354
     access to perfmon counters, 1261                 email notification, configuring SQL Server Agent,
     monitoring disk system items, 1256-1257            365-366
     monitoring memory items, 1252-1254               ENABLE, ALTER FULLTEXT INDEX, CD:1924
     monitoring network items, 1243                   ENABLE BROKER, CD:1877
     monitoring processor items, 1248                 enabling SQL Server Agent Mail, 354
     sys.dm exec query plan, 1137-1139                encryption, 40
dm_db_index_physical_stats, 1001                          connection encryption, configuring clients,
     result columns, 1002-1003                               235-236
Document Type Definition (DTD), 1378                      report server encryption, 1616
documents, XML documents, 1378                        ENCRYPTION, creating views with T-SQL, 677-678
domain integriity, 642                                END CONVERSATION, CD:1896
DRI (declarative referential integrity), 835          EndDialog, CD:1886
drill-through reports                                 endpoints, 224, 1442
     customizing, 1640-1642                               catalog views and system stored procedures,
     generating, 1639                                        1466-1467
     models, 1636                                         controlling access permissions, 1468-1469
drivers, client data access technologies, 238             database mirroring, 490-492
DROP, ALTER FULLTEXT INDEX, CD:1926                                  ,
                                                          FOR SOAP 1467
DROP DATABASE, 938                                        identifying for database mirroring, 492-493
DROP ROLE, 277                                            server endpoint layers, 224-225
DROP TABLE, 606                                           TDS endpoints, 224
dropping                                              enforcing
     constraints, 658                                     data integrity, 642-643
     indexes, 637                                         referential integrity, using DML triggers, 845-847
     tables, 605-606                                  enhancements
     views, with T-SQL, 682                               Analysis Services enhancements, 52-53
DSS (Decision Support Systems), 526                       backup and restore enhancements, 48-49
dta, 90, 95-98, CD:1731                                   database engine enhancements, 46
DTA (Database Engine Tuning Advisor), 90, CD:1694         failover clustering enhancements, 51
     command line, CD:1737-1742                           Full-Text Search enhancements, 52
     GUI, CD:1731-1736                                    index enhancements, 46
DTC (Distributed Transaction Coordinator), 443, 516       notification enhancements, 51-52
DTD (Document Type Definition), 1378                      recovery enhancements, 49
dtexec utility, 1574-1577                                 replication enhancements, 50
     packages, running, 1577-1582                         Reporting Services enhancements, 53
     phases, 1576                                         security enhancements, 47-48
DTS (Data Transformation Services), 198, 211, 1539        SQL Server Agent enhancements, 49
     upgrading, 218                                       T-SQL enhancements, 47
DTS packages, migrating, 211-212                          web services enhancements, 52
DTS Parameters, 201                                   enterprise computing, 515
dtsrun utility, 1574                                  Enterprise Edition of Reporting Services, 1613
dtswizard, 91                                         entity integrity, 642
dtutil utility, 1582-1586                             EOIO (exactly-once-in-order) messaging, CD:1890
DVPs (distributed partitioned views), 688             @@ERROR, 786
Dynamic Management Views. See DMVs                        T-SQL, CD:1836-1837
dynamic SQL, stored procedures, 772-774               Error, CD:1885
     sp executesql, 774-776                           ERROR BROKER CONVERSATIONS, CD:1877
     sp executesql, output parameters, 776-777        error handling, TRY…CATCH, 1312-1314
                                                      ERROR LINE, 787
E                                                     error logs, SQL Server Agent (viewing), 368-369
                                                      ERROR MESSAGE, 787
Eager Spool, Query Analyzer, 1127
                                                      ERROR NUMBER, 787
editing SQLCMD scripts, Query Editor, 74-75
                                                      ERROR PROCEDURE, 787
editions of SSNS, 1842-1843
                                                      ERROR SEVERITY, 787
element-centric XML shape, 1379
                                                      ERROR STATE, 787
                                                      errors, stored procedures from, 786-789
                                                                             filegroup backups              1953



escalation, locks, 1186                                execution snapshots, 1651-1652
Estimated CPU Cost, 1119                               EXISTS, 1067
Estimated I/O Cost, 1119                               exists() new xml data type method, 1412, 1420-1421
Estimated Number of Rows, 1119                         EXPAND VIEWS hints, 1100
Estimated Operator Cost, 1119                          expanding
Estimated Row Size, 1119                                   databases, 567-568
Estimated Subtree Cost, 1119, 1121                         hierarchies with recursive CTE, 1288-1294
Estimating access path costs, 1040                     expansion, CD:1934
    clustered indexes, 1041                                indexed views, 696-697
    nonclustered indexes, 1042-1046                    expired subscription clean up, 442
    table scan costs, 1046-1048                        explicit context switching, 287
ETL (extraction, transformation, and loading), 1539    EXPLICIT mode, XML, 1389-1393
evaluating indexes, 979-982                            explicit transactions, 875
event chronicles, CD:1845                                  implicit transactions, compared, 884
event forwarding, 390                                  explicit user-defined transactions, processing, 876-878
event handlers, SSIS, 1547                                 nested transactions, 879-882
event log entries, Reporting Services, 1653                savepoints, 878-879
Event parameter (SqlTrigger), 1366                     exporting traces (SQL Server Profiler), 123
event providers, SSNS, CD:1844                         exporting data, bcp utility, 1589-1591
event rules, SSNS, CD:1845                             Expression Builder, SSIS, 1553-1554
EventClass data column (SQL Profiler), 1169            expressions, multidimensional expressions,
EventClasses ADF element, CD:1851-1852                   1519-1521
EVENTDATA function, 863                                Extended MAPI (Extended Messaging Application
EventData parameter (SqlTriggerContext), 1367            Programming Interface), 339
events                                                 extended stored procedures, 793-794
    providing to applications, SSNS, CD:1871-1874          adding to SQL Server, 794-795
    SSNS, CD:1844                                          obtaining information on, 795
    traces (SQL Server Profiler), 116-118                  provided with SQL Server, 795-796
        categories, 132-136                                xp cmdshell, 796-798
exactly-once-in-order (EOIO) messaging, CD:1890        extensions, 550
EXCEPT IP, 1447                                        extent, 548
exclusive locks, 1174                                  Extent locking level (SQL Server), 1177
EXEC keyword, 710-711                                  EXTERNAL ACCESS, 792, 828
exec sp helplogins, CD:1785                            external activation, CD:1880
exec sp helpdb dbnamexyz, CD:1786                      external fragmentation, indexes, 999
exec sp helplinkedsrvlogin, CD:1785                    ExternalMailQueue, 347
exec sp helpserver, CD:1785                            extraction, transformation, and loading (ETL), 1539
exec sp linkedservers, CD:1785
exec sp server info, CD:1786                           F
EXEC statement, scalar functions, 804
                                                       failover
EXECUTE AS, executing stored procedures, 713-715
                                                            combining with scale-out options, building
EXECUTE AS clause, 288, 811
                                                              solutions with HA options, 408
EXECUTE AS statement, 287
                                                            database mirroring, testing, 507-508
Execute Report Definitions, 1645
                                                       failover clustering, enhancements, 51
executing
                                                       failover clusters, requirements for installing SQL
    AFTER triggers, 839
                                                          Server 2005, 178
    disaster recovery, CD:1790
                                                       failovers, database mirroring, 486
    INSTEAD OF triggers, 852-853
                                                       FAST n hints, 1099
    maintenance plans, 964-965
                                                       Feature Selection screen, 185
    sqlcmd, 92-94
                                                       federated servers, CD:1767-1768
    stored procedures, 710-711
                                                       fields, format files
        execution context and EXECUTE AS, 713-715
                                                            lengths, 1595-1596
        via linked servers, CD:1689
                                                            terminators, 1596-1599
        in SSMS, 711-713
                                                       file backups, 295-296
    traces (SQL Server Profiler), 123
                                                            scenarios, 315-316
execution context, 286-288, 765
                                                       file data types, bcp utility, 1591
execution log, Reporting Services, 1653
                                                       file growth, managing databases, 566-567
execution options, reports, 1650-1651
                                                       File locking level (SQL Server), 1177
execution plan selection, Query Optimizer, 1070-1072
                                                       filegroup backups, 295-296
execution plans, graphical execution plans,
                                                            scenarios, 315-316
  1130-1131



             How can we make this index more useful? Email us at indexes@samspublishing.com
1954      filegroups


filegroups, 551-553                                     FOREIGN KEY constraint, 596, 646-648
      performance and, 1225-1226                             cascading referential integrity, 648-650
FILEGROWTH, 560                                         FOREIGN KEY constraints, 847
files                                                        cascading, 850
      data files, 548                                   foreign key references, finding, 606
      database files, 550                               format files
      format files, bcp utility, 1591-1601                   bcp utility, 1591-1601
      installation log files, viewing, 190                   fields
      saving trace output to, 124                                 lengths, 1595-1596
      transaction log files, 548, 554-555                         terminators, 1596-1599
fill factor, 1714-1715                                       prefixes, lengths, 1595
      indexes, setting, 1008-1011                            storage types, 1593-1594
filter errors, 1640                                     FORMAT keyword, 1450
Filter icon, Query Analyzer, 1126                       Format parameter (SqlUserDefinedAggregate), 1364
filtering                                               Format parameter (SqlUserDefinedType), 1354
      articles, data replication, 421-425               formatting notifications, CD:1857-1859
      horizontal filtering, data replication, 463-465   fragmentation, indexes, 999
      MDS-based filtering, 1537                         fragments, XML, 1378
      vertical filtering, data replication, 463-465     FREETEXT, FTS, CD:1933, CD:1937
filters, traces (SQL Server Profiler), 121-122          FREETEXTTABLE, FTS, CD:1933, CD:1937
finding foreign key references, 606                     frequency of backups, 335-336
FIRE TRIGGERS, 840                                      FTS (Full-Text Search), CD:1913
firewalls, connectivity, 245                                 CONTAINS, CD:1933-1936
FIRE_TRIGGER hint, supplying bulk-copy                       CONTAINSTABLE, CD:1933-1937
   operations, 1604                                          FREETEXT, CD:1933, CD:1937
fixed-database roles, 259-261                                FREETEXTTABLE, CD:1933, CD:1937
fixed-server roles, 258-259                                  Gatherer, CD:1915
flow control, 377                                            how it works, CD:1914-1916
fn get sql(sqlhandle), 825                                   Indexer, CD:1915
fn helpcollations(), 825                                     maintenance, CD:1938
fn listextendedproperty, 826                                 new features, CD:1914
fn servershareddrives, 826                                   noise words, CD:1937
fn trace geteveninfo(traceID), 826                           performance, CD:1938-1939
fn trace geteventinfo, 141                              full database backups, 294
fn trace getfilterinfo, 141                                  scenarios, 311-312
fn trace getfilterinfo(traceID), 826                    full outer joins, T-SQL, CD:1833-1835
fn trace getinfo, 141                                   full recovery model, 297-298
fn trace getinfo(traceID), 826                          full-text catalogs, CD:1938
fn trade gettable(filename, numfiles), 826              full-text indexes, T-SQL command, CD:1916
fn virtualfilestats(dbid, fileid), 826                       ALTER FULLTEXT INDEX, CD:1924-1926
fn virtualservernodes(), 826                                 CREATE FULLTEXT CATALOG, CD:1916-1918
focus of disaster recovery, CD:1784-1788                     CREATE FULLTEXT INDEX, CD:1919-1924
focusing on specific data with views, 670                    diagnostics, CD:1927-1930
for clause, query() new xml data type method,                managing MSFTESQL, CD:1927
   1415-1416                                            full-text indexing
FOR SOAP, 1449-1453                                          Full-Text Indexing Wizard, CD:1930-1932
      viewing endpoints, 1467                                xml columns, 1436
FOR XML, 1274                                           Full-Text Indexing Wizard, CD:1930-1932
FOR XML modes, 1378                                     Full-Text Search, 20-21
      AUTO mode, 1385-1389                                   enhancements, 52
      EXPLICIT mode, 1389-1391, 1393                    Full-Text Search. See FTS
      new xml data type, 1396-1399                      FULLTEXTCATALOGPROPERTY, full-text indexes,
      PATH mode, 1393-1396                                CD:1929
      RAW mode, 1379-1383                               Function Properties dialog (SSMS), 824
FORCE ORDER hints, 1099                                 functions
forced parameterization, managing Query Optimizer,           adXactReadcommitted, 1155
   1109-1111                                                 avoiding unnecessary executions, T-SQL, CD:1809
forcing                                                      CLR functions, 827-828
      query plan recompiles, 1076-1077                       deciding between T-SQL and CLR functions,
      recompilation of query plans, 768-771                    830-831
                                                             EVENTDATA, 863
                                                                                     histograms        1955



    getdate(), 800                                           data partitioning, 410
    getonlydate(), 801                                       data replication and database mirroring, 480
    ITransactionLocal::StartTransaction, 1155                database snapshots, 408-410
    object definition, 721                                   dedicated administrator connections, 410
    OBJECTPROPERTY, 820                                      defined, 395-396
    partition functions, creating, 608-610                   fast recovery, 408
    rewriting stored procedures as, 826-827                  hardware, 397
    SQLSetConnectAttr, 1155                                  new features, 394-395
    systemwide table-valued functions, 825-826               online indexing, 408
    tsequal(), 1152                                          online restore, 408
    UPDATE, 843-845                                          operating systems, 397
    user-defined functions. See user-defined functions       quality assurance, 398
                                                             server instance isolation, 398-399
G                                                            snapshot isolation levels, 410
                                                             standards/procedures, 398
GAM (global allocation map), 549
                                                             training, 398
gather streams, 1089
                                                             vendor agreements, 398
    Query Analyzer, 1129
                                                             Windows Servers, 410
Gatherer, FTS, CD:1915
                                                                 Virtual Server 2005, 411-412
generating
                                                         hardware, HA, 397
    column statistics, 990-998
                                                         hardware requirements for installing SQL Server
    drill-through reports, 1639
                                                           2005, 174-175
    index statistics, 990-998
                                                         hash joins, 1063-1065
    page numbers, with NTILE, 1304
                                                         Hash Match icon, Query Analyzer, 1126
    relational databases, 1523-1524
                                                         Heap or B-Tree (HOBT) locking level (SQL
    T-SQL statements, with T-SQL, CD:1835-1836
                                                           Server), 1177
generation
                                                         heartbeat, 519
    CONTAINS, FTS, CD:1934-1935
                                                         help, integrated help (SSMS), 62-64
    CONTAINSTABLE, FTS, CD:1934-1935
                                                         Help resources, SSMS, 62
generator, CD:1845
                                                         heterogeneous environments, replication monitoring,
Generator ADF element, CD:1862
                                                           477-478
GET CONVERSATION DIALOG, CD:1896, CD:1900
                                                         hierarchies
getdate(), 800
                                                             defining in OLAP database creation, 1493-1502
getonlydate(), 801
                                                             in reports, with BIDS Report Designer, 1623-1624
global allocation map (GAM), 549
                                                         high-availability. See HA
grace hash joins, 1065
                                                         hints
GRANT, 266, 285
                                                             bulk-copy operations, SSIS, 1603-1604
granularity, locks, 1176-1187
                                                             granularity hints, locks, 1206
granularity hints, locks, 1206
                                                             Query Optimizer, 1096
graphic charts, 1236
                                                                 EXPAND VIEWS, 1100
graphical execution plans, saving and viewing,
                                                                 FAST n, 1099
  1130-1131
                                                                 FORCE ORDER, 1099
GROUP BY, query processing, 1083-1084
                                                                 GROUP BY, 1098
GROUP BY hints, 1098
                                                                 join, 1099
grouping
                                                                 join hints, 1098
    discouraging in models, 1636
                                                                 KEEP PLAN, 1100
    in reports, with BIDS Report Designer, 1621-1623
                                                                 KEEPFIXED PLAN, 1100
GROUPING, sorting results (T-SQL), CD:1822-1824
                                                                 MAXDOP number, 1100
guest users, 255-256
                                                                 MAXRECURSION number, 1100
GUI for DTA, CD:1731-1736
                                                                 OPTIMIZER FOR, 1100
                                                                 processing hints, 1098, 1101
H                                                                RECOMPILE, 1100
HA (high-availability), 393, 397                                 ROBUST PLAN, 1099
   backups, 397                                                  table hints, 1096-1097
   building solutions, 400-401                                   UNION, 1098-1099
       combining failover with scale-out options, 408            USE PLAN, 1101-1103
       data replication, 404-406                                 USE PLAN N, 1101
       database mirroring, 407                               transaction isolation, 1204-1205
       log shipping, 406-407                                 type hints, locks, 1206
       MSCS, 401                                         histogram charts, 1236
       SQL Clustering, 402-404                           histograms, index statistics, 984-987



              How can we make this index more useful? Email us at indexes@samspublishing.com
1956       History page


History page, 363                                    identifying
history snapshots, 1651-1652                              ad hoc queries, SQL Server Profiler, 147-148
HOLAP (hybrid OLAP), 1482                                 endpoints, database mirroring, 492-493
HOLDLOCK, 911                                             JOIN clauses, query analysis, 1034
HOLDLOCK transaction isolation level, 1204                objects referenced in stored procedures, 717-719
HOME\Administrator, 253                                   OR clauses, query analysis, 1033-1034
horizontal data partitioning, CD:1765-1766                parallel queries, 1089-1090
    denormalization, 1221-1223                            performance bottlenecks, SQL Server Profiler,
    distributed partitioned views, CD:1767                  148-149
horizontal filtering, data replication, 463-465           search arguments, query analysis, 1032-1033
hybrid joins, 1065                                   identity columns, 591-592
hybrid OLAP (HOLAP), 1482                            IDEs (integrated development environments), 1439
                                                     IF EXISTS, CD:1807
I                                                    IFilter, CD:1915
                                                     IIS (Internet Information Services), 213, 1609
IAM (index allocation map), 549
                                                     immediate transactional consistency, 443
ICFs, SSNS, CD:1863-1865
                                                     immediate updating, replication, 19
icons, logical and physical operators icons (Query
                                                     IMPERSONATE, 267, 287
  Analyzer), 1121
                                                     implementation contract, managed stored procedures,
    Assert, 1123
                                                       1335-1339
    Clustered Index Delete, 1124
                                                     implementation phase, OLAP design
    Clustered Index Scan, 1124
                                                       methodologies, 1484
    Collapse, 1124
                                                     implementing data integrity, 643
    Compute Scalar, 1125
                                                     implicit context switching, 288
    Concatenation, 1125
                                                     implicit transactions, 875
    Constant Scan, 1125
                                                          explicit transactions, compared, 884
    Deleted Scan, 1126
                                                          processing, 882-884
    Distribute Streams, 1129
                                                     Import and Export Wizard (SSIS), 1551
    Eager Spool, 1127
                                                     importing traces (SQL Server Profiler), 125-126
    Filter, 1126
                                                          into trace tables, 126-128
    Gather Streams, 1129
                                                     importing data, bcp utility, 1589-1591
    Hash Match, 1126
                                                     IN, 1067
    Insert Scan, 1126
                                                     IN PATH, CREATE FULLTEXT CATALOG, CD:1917
    Lazy Spool, 1127
                                                     in-place upgrading. See upgrading in-place
    Log Row Scan, 1127
                                                     included columns, indexes, 1020
    Merge Join, 1127
                                                     index allocation map (IAM), 549
    Nested Loops, 1127
                                                     index create memory, CD:1715
    Nonclustered Index Delete, 1124
                                                     INDEX CREATE statement, 864
    Nonclustered Index Scan, 1124
                                                     index intersection, 1048-1051
    Nonclustered Index Spool, 1126
                                                     index joins, 1055-1056
    Parallelism, 1129
                                                     index locks, granularity, 1184-1185
    Parameter Table Scan, 1127
                                                     index partitioning, 42-43
    Remote Delete, 1128
                                                          for VLDB, CD:1768-1770
    Remote Insert, 1128
                                                     index selection, Query Optimizer, 1034-1035
    Remote Query, 1128
                                                          estimating access path costs, 1040-1048
    Remote Scan, 1128
                                                          evaluating SARG and join selectivity, 1035-1040
    Remote Update, 1128
                                                          multiple indexes, 1048-1056
    Repartition Streams, 1129
                                                     index union strategy, 1052-1055
    RID Lookup, 1128
                                                     indexed views, 690, 1021-1022
    Row Count Spool, 1126
                                                          creating, 690-693
    Sequence, 1128
                                                          expansion, 696-697
    Sort, 1128
                                                          optimizing, 1056-1059
    Stream Aggregate, 1128
                                                          performance and, 693-696
    Table Delete, 1128
                                                     indexes, 969
    Table Insert, 1128
                                                          altering, 624
    Table Scan, 1128
                                                          B-tree indexes, 971
    Table Spool, 1126
                                                          choosing, 1024-1026
    Table Update, 1128
                                                          clustered indexes, 624-625, 971-973
    Table-valued Function, 1128
                                                              costs, 1041
    Top, 1129
                                                              designing, 1014-1016
                                                              indexed views, 1021-1022
                                                           InstanceName ICF elements                 1957



columns                                                   wide indexes, 1020-1021
      computed columns, 1022-1024                         XML indexes, 624
      included columns, 1020                        IndexID data column (SQL Profiler), 1169
      joins, 1014                                   indexing
composite indexes, design, 1013                           online indexing, HA, 408
covering, 1018-1019                                       xml columns, 1430-1436
creating                                                      full-text indexing, 1436
      with SSMS, 631-632                            indirect recursion, 871
      with T-SQL, 627-631                           inequality operators, SARG and, 1037
densities, 987-988                                  Informatica, 1539
design, 1013-1014, 1213                             INFORMATION SCHEMA, 819-820
disabling, 1011-1012                                INFORMATION SCHEMA users, 256
dropping, 637                                       information schema views, 166-167
enhancements to, 46                                 Infoset, XML, 1432-1433
evaluating, 979-982                                 inheritance, defining in models, 1632-1634
fill factor                                         .ini, 191
      reapplying, 1010-1011                         Init() method, 1363
      setting, 1008-1010                            INITIATOR, CD:1886
fragmentation, 999                                  inline table-valued functions, 805
FTS, CD:1915                                              CREATE FUNCTION, 808
full-text indexes. See full-text indexes            input parameters, stored procedures, 724-725
included columns, 624                                     passing object names as, 728-729
intermediate nodes, 624                                   setting default values for, 725-728
maintenance, 998-1008                                     wildcards, 729-730
      disabling indexes, 1011-1012                  INPUTBUFFER, 1265
      fill factor, 1008-1011                        INSERT, 266
      Maintenance Plan Wizard, 953-956              Insert Scan icon, Query Analyzer, 1126
      SSMS, 1012-1013                               inserted tables, DML triggers, 841-843
managing, 633-636                                   inserting trace data into a trace table from a trace
multiple indexes, 1020-1021                            file, 127-128
nonclustered indexes, 626-627, 973-975              installation log files, viewing, 190
      costs, 1042-1046                              installation paths, 190-191
      designing, 1016-1017                          Installation Wizard, 181
      fill factor, 1010                             installing
      rebuilding, 1008                                    bigpubs2005 database, 6
      SQL Server performance, 1269                        clients, 228-230
on views, 639-640                                         Reporting Services, 1613-1615
online index operations, 623                              SP1, 193-194
online indexing operations, 637-638                           unattended installation, 195
parallel index operations, 624                            SQL Server for SQL Server Clustering, 528-537
poor selectivity, 988, 1014                               SQL Server 2005, 203
problems with query optimization, 1092                        installation log files, 190
query indexes, 1024-1026                                      installation paths, 190-191
Query Optimizer, multiple indexes. See multiple               remote installation, 193
   indexes                                                    requirements for, 173-179
querying, 975-978                                             screens, 180-190
rebuilding, VLDB data maintenance,                            side-by-side migration. See side-by-side
   CD:1752-1755                                                 migration
selecting, 978                                                unattended installation, 191-193
SQL Server 2005, new features, 970                            upgrading in-place. See upgrading in-place
statistics, 982-984                                       SQL Server Clustering, 524-525
      generating, 990-998                                     configuring SQL Server database disks,
      histograms, 984-987                                       525-526
      rows, estimating, 988-989                               Connection Test Program, 539-542
      string summary statistics, 998                          failure of nodes, 537-539
structures, 970-975                                           installing MSCS, 527
tables                                                        installing network interfaces, 527
      over-definition, 977                          Instance Name screen, 185
      Query Optimizer, 969                          INSTANCENAME, 219
update performance indexes, 1024-1026               InstanceName ICF elements, CD:1863-1865




          How can we make this index more useful? Email us at indexes@samspublishing.com
1958      instances


instances                                              J
     configuration options for, CD:1695-1702
     creating via SSMS for SSNS, CD:1866-1868          JDBC (Java Database Connectivity)
     SQL Browser, 228                                      client data access technologies, 244
     SSNS, CD:1846-1847                                    drivers, 238
     XML, 1378                                         Job Activity Monitor, 49, 362
instant file initialization, 46                        job history, viewing, 380-381
     databases, 548                                    job mail notifications, SQL Server Agent Mail, 354-355
INSTEAD OF trigger, 851-853                            job notifications, 379
     versus AFTER triggers, 854-856                    job schedules, 49, 377-378
     restrictions, 859                                 job steps, 374-376
     views, 856-859                                        multiple job steps, 376-377
INTEGRATED, 1445                                       Job System page, 363
integrated development environments (IDEs), 1439       jobs, 361
integrated environments, SSMS, 58                          creating with SQL Server Agent Mail, 355
     integrated help, 62-64                                managing in SQL Server Agent, 373
     window management, 59-62                                  job history, 380-381
integrated help, SSMS, 62-64                                   job notifications, 379
Integration Services, 184, 443                                 job properties, 373
     SSIS, 1552                                                job schedules, 377-378
integrity                                                      job steps, 374-376
     data integrity, Database Engine, 14                   multiserver job management, 388-390
     database integrity. See database integrity            scripting, 387
intent locks, 1174-1175                                JOIN, identifying for query analysis, 1034
interactivity in reports, with BIDS Report Designer,   join hints, 1099
  1625-1626                                                Query Optimizer, 1098
intergrating SSMS with source control, 81-83           join processing strategies, 1060
intermediate nodes, indexes, 624                           hash joins, 1063-1065
internal activation, CD:1880                               merge joins, 1061-1063
internal fragmentation, indexes, 999                       nested loops joins, 1060-1061
InternalMailQueue, 347                                 join selection, Query Optimizer, 1059-1060
Internet Information Services (IIS), 213, 1609             determining optimal join order, 1065-1066
interoperability, enterprise computing, 515                join processing strategies, 1060-1065
InvokeIfReceiverIsNull parameter (SqlMethod), 1359         subquery processing, 1067-1070
IS (Integration Services), 443                         join selectivity, evaluating, 1035-1040
IsByteOrdered parameter (SqlUserDefinedType), 1355     joins
IsDeterministic, 820                                       columns, indexes, 1014
IsDeterministic parameter (scalar UDFs), 1345              hash joins, 1063-1065
IsDeterministic parameter (SqlMethod), 1359                hybrid joins, 1065
IsFixedLength parameter (SqlUserDefinedType), 1355         merge joins, 1061-1063
IsInlineFunction, 820                                      nested loops joins, 1060-1061
IsInvariantToDuplicates parameter                          optimal join order, 1065-1066
  (SqlUserDefinedAggregate), 1364                          subquery processing, 1067-1070
IsInvariantToNulls parameter
  (SqlUserDefinedAggregate), 1364                      K
IsInvariantToOrder parameter                           KEEP PLAN hints, 1100
  (SqlUserDefinedAggregate), 1364                      KEEP REPLICATION, 323
IsMutator parameter (SqlMethod), 1359                  KEEPFIXED PLAN hints, 1100
IsNullIfEmpty parameter                                KERBEROS, 1445
  (SqlUserDefinedAggregate), 1364                      KEY INDEX, CREATE FULLTEXT INDEX, CD:1922
isolation levels, transaction isolation levels,        Key locking level (SQL Server), 1177
  1153-1159                                            key-range locking, 1178-1180
IsPrecise parameter (scalar UDFs), 1345                keygen, CD:1813
IsScalarFunction, 820                                  keyword groups
IsSchemaBound, 820                                                ,
                                                           AS HTTP 1445-1448
IsTableFunction, 820                                                ,
                                                           FOR SOAP 1449-1453
IsUpdatedColumn parameter                              keywords
  (SqlTriggerContext), 1367                                AUTHORIZATION, 1445, CD:1903
ITransactionLocal::StartTransaction function, 1155         CHARACTER SET, 1452
                                                           COMPRESSION, 1448
                                                                                           locks       1959



    CONTROL, 1469                                           obtaining general information about,
    CREATE ENDPOINT, 1444                                     CD:1687-1689
    DEFAULT, 661, 663-664                                   removing, CD:1681-1682
    EXEC, 710-711                                           setting up through SQL Server Management
    FORMAT, 1450                                              Studio, CD:1689-1692
    INITIATOR, CD:1886                                      viewing, CD:1680-1681
    NAMESPACE, 1448                                    LISTENER IP, 1445
    NOT NULL, 590                                      LISTENER PORT, 1445
    NULL, 590                                          lists, creating with BIDS Report Designer, 1620-1621
    RELATED CONVERSATION, 1892                         live reports, report execution options, 1650
    SCHEMA, 1450                                       load os resources, CD:1927
    SITE, 1448                                         LOB (large object), 551
    TARGET, CD:1886                                    LOB reads, STATISTICS IO, 1141
    UNIQUE, 596                                        local mode, report controls, 1653
    XMLDATA, 1383                                      lock activity, monitoring, 1160-1161
KILOBYTES_PER_BATCH hint, supplying to bulk-copy            Performance Monitor, 1169-1171
  operations, 1604                                          SQL Server Profiler, 1167-1170
KPIs, 1525                                                  SSMS (SQL Server Management Studio),
                                                              1164-1167
L                                                           sys.dm_tran_locks view, 1161-1164
                                                       lock events, SQL Profiler, 1167
LANGUAGE
                                                       Lock Manager, 1160
    CONTAINS, FTS, CD:1936
                                                            locks, 1171-1176
    CONTAINSTABLE, FTS, CD:1936
                                                                granularity, 1176-1187
    CREATE FULLTEXT INDEX, CD:1920-1922
                                                       Lock Requests/sec counter (SQLServer:Locks
large object (LOB), 551
                                                          object), 1171
large row support, data types, 588
                                                       Lock Timeouts/sec counter (SQLServer:Locks
large-value data types, 587-588
                                                          object), 1171
latent transactional consistency, 443
                                                       Lock Wait Time counter (SQLServer:Locks
layout, designing with BIDS Report Designer,
                                                          object), 1171
   1619-1620
                                                       Lock Waits/sec counter (SQLServer:Locks
Lazy Spool, Query Analyzer, 1127
                                                          object), 1171
Legato software, CD:1791
                                                       Lock:Acquired event (SQL Profiler), 1167
lengths
                                                       Lock:Cancel event (SQL Profiler), 1167
    format file prefixes, 1595
                                                       Lock:Deadlock Chain event (SQL Profiler), 1168
    format file fields, 1595-1596
                                                       Lock:Deadlock event (SQL Profiler), 1168
Level 0, CD:1773
                                                       Lock:Escalation event (SQL Profiler), 1168
Level 1, CD:1773
                                                       Lock:Released event (SQL Profiler), 1168
Level 2, CD:1773
                                                       Lock:Timeout event (SQL Profiler), 1168
Level 3, CD:1773
                                                       locking
Level 4, CD:1773
                                                            contention, 1188-1189
level hints, transaction isolation, 1204-1205
                                                                identifying, 1189-1191
libraries, net-libraries, 237
                                                                minimizing, 1192-1193
licensing models, 29-33
                                                            importance of, 1152-1153
lightweight pooling, CD:1715-1716
                                                            lock activity, monitoring, 1160-1171
LIKE, SARG and, 1038
                                                            Lock Manager, 1160
limitations
                                                            new features, 1151
    for cached reports, execution snapshots, and
                                                            optimistic locking, 1207
       history snapshots, 1652
                                                                snapshot isolation, 1209-1211
    of database snapshots, 925-926
                                                                timestamp data type, 1207-1209
    of relational databases, 1524
                                                            page-level locking, 1185-1186
    of SQL Server web services, 1469
                                                            row-level locking, 1185-1186
linear regression, data mining algorithms, 1527
                                                            SQL Server levels, 1177
linked servers, CD:1671-1672
                                                            table hints, 1203-1206
    adding, CD:1673-1680
                                                            transaction isolation levels, 1153-1159
    configuring, with sp serveroption, CD:1682-1683
                                                            transactions, 911-912
    distributed queries, CD:1672
                                                            VLDB data maintenance, 1757
    distributed transactions, CD:1672-1673
                                                       locks, 1171, CD:1716
    executing stored procedures, CD:1689
                                                            bulk update locks, 1176
    mapping local logins to logins, CD:1683-1687
                                                            compatibility, 1187-1188




             How can we make this index more useful? Email us at indexes@samspublishing.com
1960      locks


    deadlocks, 1193-1195                               M
         1204 trace flags, 1197-1199
         1222 trace flags, 1199                        mail configuration objects, viewing, 357-358
         avoiding, 1195-1196                           mail message data, viewing, 359-360
         conversion deadlocks, 1194                    mail profiles, 342
         cycle deadlocks, 1193-1194                       creating in Database Mail, 342-344
         examining, 1196-1197                          maintaining
         handling, 1196-1197                              indexes, Maintenance Plan Wizard, 953-956
         monitoring, 1200-1203                            system databases, 159
    escalation, 1186                                   maintenance
    exclusive locks, 1174                                 FTS, 1938
    granularity, 1176-1187                                indexes, 998-1008
    granularity hints, 1206                                   disabling, 1011-1012
    intent locks, 1174-1175                                   fill factor, 1008-1011
    schema locks, 1175                                        SSMS, 1012-1013
    shared locks, 1172                                 maintenance issues, VLDB, CD:1745
    SQL Server performance counters, 1259                 backing up and restoring, CD:1745-1748
    timeout intervals, setting, 1191-1192                 consistency, CD:1749-1751
    type hints, 1206                                   maintenance phase, OLAP design
    update locks, 1173-1174                             methodologies, 1485
locks option, configuration setting, 1187              Maintenance Plan Wizard, 946-947
Log File Viewer, 380                                      backing up databases, 948-951
    SSMS, 70-71                                           checking database integrity, 951
log files                                                 maintaining indexes and statistics, 953-956
    installation log files, viewing, 190                  scheduling maintenance plans, 956-959
    shrinking, 571, 895-897                               shrinking databases, 952-953
log reader agent, 439-440                              maintenance plans, 946
Log Row Scan, Query Analyzer, 1127                        executing, 964-965
log sequence numbers (LSNs), 874                          managing without a wizard, 959-964
log shipping, 946, CD:1782                                scheduling with Maintenance Plan Wizard,
    building solutions with HA options, 406-407             956-959
    data replication, 480                              majority node sets, 520
logged bulk-copy operations, SSIS, 1601-1602           Manage Indexes tool, 631
    batches, 1602                                      managed database objects
    hints, 1603-1604                                      managed triggers, 1366-1372
    parallel loading, 1602-1603                           permissions, 1332
logging                                                   related system catalogs, 1374-1375
    SSIS, 1549                                            stored procedures, 1335-1336, 1339-1344
    transaction logging, 885-897                              assembly creation, 1342-1343
    VLDB data maintenance, CD:1757-1758                       attributes, 1335-1336, 1339
logical and physical operator icons, Query Analyzer.          context connection, 1340
  See icons                                                   debugging, 1343-1344
Logical Operation, 1118                                       implementation contract, 1335-1336, 1339
logical reads, STATISTICS IO, 1141                            Microsoft.SqlServer.Server objects,
LoginName data column (SQL Profiler), 1169                       1340-1341
logins                                                    transactions, 1372-1374
    managing, 268-273                                     UDAs, 1363-1366
    principals, 251-253                                   UDFs
    users, 254                                                scalar UDFs, 1344-1347
logistic regression, data mining algorithms, 1528             TVFs, 1348-1349, 1352-1353
logmarkhistory, 325                                       UDTs, 1354-1355, 1359-1363
logs                                                      Visual Studio 2005, 1334-1335
    checkpoints, 886-889                               managed stored procedures, developing managed
    transaction log files, 548, 554-555                 database objects, 1335-1336, 1339-1344
    write-ahead logs, 555                              managed triggers, developing managed database
long-running transactions, managing, 913-915            objects, 1366-1372
loosely coupled, CD:1876                               Management Studio, XML (SSNS), CD:1848-1849
lost updates, transaction isolation levels, 1153       management tools, 1613
LSNs (log sequence numbers), 874
                                                                                      methods        1961



managing                                               markups, documents (XML), 1378
  alerts (SQL Server Agent), 381                       MARS (multiple active result sets), 44, 221
      properties, 382-384                                  ADO.NET, 1324-1326
      responses, 384-386                               masks
  constraints, 656                                         affinity I/O masks, CD:1704-1706
      disabling, 659                                       affinity masks, CD:1706-1707
      dropping, 658-659                                master databases, 158, 333
      gathering constraint information, 657-658        master database files, 557
  database, file growth, 566-567                       Master Server Wizard, 389
  database roles, 276-277                              master servers, creating, 388-389
  databases                                            materialized subqueries, 1067-1068
      ALTER DATABASE, 573-574                          max, DML, 1274-1276
      expanding, 567-568                               max degree of parallelism, CD:1716-1717
      shrinking, 568-571                               max server memory, CD:1717-1718
      shrinking log files, 571                         max text repl size, CD:1719
      shrinking with SSMS, 571-573                     max worker threads, CD:1719-1720
  DDL triggers, 864-866                                MaxByteSize parameter
  indexes, 633-636                                       (SqlUserDefinedAggregate), 1364
  jobs (SQL Server Agent), 373                         MaxByteSize parameter (SqlUserDefinedType), 1355
      job history, 380-381                             MAXDOP number hints, 1100
      job notifications, 379                           MAXRECURSION, CTE, 1294-1295
      job properties, 373                              MAXRECURSION number hints, 1100
      job schedules, 377-378                           MDAC (Microsoft Data Access Components),
      job steps, 374-376                                 221, 1440
  logins, 268-273                                          client data access technologies, 242-243
  maintenance plans, without a wizard, 959-964         .mdf extension, 550
  MSFTESQL, CD:1927                                    MDX (multidimensional expressions), 73
  operators, SQL Server Agent, 370-372                 MDX-based filtering, 1537
  permissions, 277                                     measuring runtime, datediff(), 1145
      with SSMS, 277-278                               media families, 301
      with SSMS (database level), 281-283              media sets, 301
      with SSMS (object level), 283-284                memory, monitoring, 1250-1254
      with SSMS (server level), 278-280                merge agent, 442
      with T-SQL, 285-286                              Merge Join, Query Analyzer, 1127
      user-defined functions, 824-825                  merge joins, 1061-1063
  plan guides, 1106                                    merge replication, 19, 444, 446-447
  projects, SSMS, 79-81                                Merge() method, 1363
  Query Optimizer, 1094-1096                           message storage, queues (SQL Server Service Broker),
      forced parameterization, 1109-1111                 CD:1887-1889
      join hints, 1098                                 message types, choosing for SQL Server Service
      plan guides, 1103-1109                             Broker, CD:1882-1886
      processing hints, 1098, 1101                     messages, defining in SQL Server Service Broker,
      query governor, 1111-1113                          CD:1882-1886
      table hints, 1096-1097                           Meta Data Services Repository, 218
      USE PLAN, 1101-1103                              metadata, CD:1780
  remote servers, CD:1664-1666                         Metadata locking level (SQL Server), 1177
      setup, 1666-1671                                 methods
  users                                                    Aggregate(), 1363
      with SSMS, 273-275                                   Init(), 1363
      with T-SQL, 275                                      Merge(), 1363
  views, 681                                               new xml data type built-in methods, 1411-1430
      with SSMS, 683                                            exists() method, 1412, 1420-1421
manual checkpoints, logs, 889                                   modify() method, 1412, 1425-1430
manual synchronization, 438                                     nodes() method, 1412, 1423, 1425
manually updating index statistics, 993-994                     query() method, 1412-1420
mapping local logins to logins on linked servers,               value() method, 1412, 1421-1422
 CD:1683                                                   Parse(), 1355
  sp addlinkedsrvlogin, CD:1684-1685                       SqlConnection.BeginTransaction, 1155
  sp droplinkedsrvlogin, CD:1685-1686                      Terminate(), 1363
  sp helplinkedsrvlogin, CD:1686-1687                      ToString(), 1355
                                                           value(), 1435



             How can we make this index more useful? Email us at indexes@samspublishing.com
1962      Microsoft Cluster Service


Microsoft Cluster Service. See MSCS                   modifying
Microsoft Data Access Components. See MDAC               data, through partitioned views, 688
Microsoft English Query, 216                             logins, SSMS, 271
Microsoft Full-Text Engine for SQL Server                stored procedures, 722-724
 (MSFTESQL), 20                                          tables, 598
Microsoft Message Queuing (MSMQ), CD:1875                    with database diagrams, 604-605
Microsoft ODBC driver for SQL Server, 238                    with Object Explorer and Table Designer,
Microsoft OLE DB provider for ODBC, 237                        601-604
Microsoft OLE DB provider for SQL Server, 237                with T-SQL, 598-601
Microsoft Tape Format (MTF), 300                         user-defined functions, 821-822
Microsoft Virtual Server 2005, HA, 411-412            MOLAP (Multidimensional OLAP), 1481
Microsoft Visual Studio .NET, debugging stored        monitoring
 procedures, 756-760                                     application progress, with SQL Server Profiler,
Microsoft.SqlServer.Server objects, managed stored         150-152
 procedures, 1340-1341                                   auto-update statistics, with SQL Server
migrating                                                  Profiler, 150
   Analysis Services, 209-210                            data replication, 471
   databases, side-by-side migration. See side-by-           backup and recovery, 478-479
     side migration                                          in heterogeneous environments, 477-478
   DTS packages, 211-212                                     Performance Monitor, 477
   Notification Services (SSNS), 213                         SQL statements, 472-475
   Reporting Services, 212-213                               troubleshooting replication failures, 476-477
migration, side-by-side migration. See side-by-side          within SQL Server Management Studio,
 migration                                                     474-476
min memory per query, CD:1720                            database mirroring, 501-502, 504-505
min server memory, CD:1717-1718                          deadlocks, Server Profiler, 1200-1203
mirror database servers, database mirroring, 483         disk systems, 1254-1257
mirror role, database mirroring, 485                     lock activity, 1160-1161
mirror server, creating datasbase on, 493-495                Performance Monitor, 1169-1171
mirrored backups, 48, 292                                    SQL Server Profiler, 1167-1170
   scenarios, 316                                            SSMS, 1164-1167
mixed authentication mode, 250                               sys.dm_tran_locks view, 1161-1164
mixed extent, 548                                        memory, 1250-1254
Mode data column (SQL Profiler), 1169                    network interfaces, 1239-1243
model database, 158                                      plan cache, 1077
Model Designer                                               sys.dm exec cached plans, 1077-1079
   designing reports, 1629-1630                              sys.dm exec plan attributes, 1081-1082
   promoted properties, 1634                                 sys.dm exec query stats, 1079-1081
Model Explorer, Report Builder, 1628-1629                    sys.dm exec sql text, 1079
model queries, debugging, 1636-1638                      processors, 1244-1249
models                                                   running traces, 141-142
   debugging, 1636-1638                                  SQL Server disk activity, 1259
   design examples, 1631-1632                            SQL Server performance, 1233-1235
   design surface, Report Builder, 1634-1635             store procedure recompilation, 766-768
   designing reports, 1629-1630                              forcing recompilation of query plans, 768-771
   grouping, discouraging, 1636                          values, Performance Monitor, 1237-1238
   inheritance, defining, 1632-1634                   moving
   permissions, setting, 1645                            databases, 572
   properties, promoting, 1634                           packages, dtutil utility, 1582-1585
   roles and drill-through reports, 1636              MSCS (Microsoft Cluster Service), 51, 481
   security, 1643-1644                                   building solutions with HA options, 401
   sorting, in Report Builder, 1639                      extending with NLB, 522
modes, FOR XML modes, 1378                               installing, 527
   AUTO mode, 1385-1389                                  SQL Server Clustering, 517-524
   EXPLICIT mode, 1389-1391, 1393                     MSDASQL, 237
   new xml data type, 1396-1397, 1399                 msdb database, 158
   PATH mode, 1393-1396                               MSDN Online, 62
   RAW mode, 1379-1383                                MSFTESQL (Microsoft Full-Text Engine for SQL
modify() new xml data type method, 1412,               Server), 20
 1425-1430                                               managing, CD:1927
                                                                                    new features          1963



MSMQ (Microsoft Message Queuing), CD:1875                     custom managed database objects
MSSQL$:Plan Cache Object, 1258                                    managed triggers, 1366-1368, 1370-1372
MSXML 6.0, 216                                                    permissions, 1332-1334
MTF (Microsoft Tape Format), 300                                  related system catalogs, 1374-1375
multidimensional expressions (MDX), 73                            stored procedures, 1335-1336, 1339-1344
   in OLAP database creation, 1519-1521                           transactions, 1372-1374
Multidimensional OLAP (MOLAP), 1481                               UDAs, 1363-1366
Multiple Active Result Sets. See MARS                             UDFs, 1344-1349, 1352-1353
multiple applications, binding, 917                               UDTs, 1354-1355, 1359-1363
multiple indexes                                                  Visual Studio 2005, 1334-1335
   Query Optimizer, 1048                                      namespaces, 1319
        index intersection, 1048-1051                         new features, 1319
        index joins, 1055-1056                            .NET Framework 2.0, 216
        index union strategy, 1052-1055                   .NET Framework data provider for SQL Server, 237
   wide indexes, compared, 1020-1021                      .NET Framework Data Provider for SQL Server, client
multiple publishers or multiple subscribers replication     data access technologies, 240-242
 model, data replication, 429-431                         NET SEND, 371
multiserver job management, 388                           net-libraries, 237
   master servers, creating, 388-389                      network interfaces
   multiserver jobs, creating, 390                            installing, 527
   target servers, enlisting, 389-390                         monitoring, 1239-1243
multiserver jobs, creating, 390                           network packet size, 1721-1722
multistatement table-valued functions, 806-807            network protocol support, requirements for installing
   CREATE FUNCTION, 808                                     SQL Server 2005, 177-178
multistatement transactions, triggers, 907-909            network protocols, server network protocols, 222-223
My Reports role, 1647                                     network shares, 301
                                                          neural networks, data mining algorithms, 1528
N                                                         New Alert dialog, 382
                                                          NEW BROKER, CD:1877
Naïve Bayes, data mining algorithms, 1528
                                                          new features
NAME, 1449
                                                              backups and restoration, 291-292
Name parameter (scalar UDFs), 1345
                                                              command-line utilities, 90-91
Name parameter (SqlMethod), 1359
                                                              data integrity, 641-642
Name parameter (SqlTrigger), 1366
                                                              data replication, 416-417
Name parameter (SqlUserDefinedAggregate), 1364
                                                              of database design, 1213
Name parameter (SqlUserDefinedType), 1355
                                                              database file movement, 548
NAMESPACE, 1448
                                                              database snapshots, 920
namespaces, .NET Framework, 1319
                                                              for databases, 548
naming columns, 585
                                                              disaster recovery planning, 1772
.ndf extension, 550
                                                              DML, 1273
Nested Loops, Query Analyzer, 1127
                                                              FTS, CD:1914
nested loops joins, 1060-1061
                                                              HA, 394-395
nested outer joins, T-SQL, 1832-1833
                                                              instant file initialization, databases, 548
nested stored procedures, 743-745
                                                              Job Activity Monitor, 362
    recursion, 745-748
                                                              large rows, 579
nested transactions
                                                              large-value data types, 580
    explicit user-defined transactions, processing,
                                                              monitoring SQL Server performance, 1234-1235
       879-882
                                                              online index operations, 623
    triggers, 905-907
                                                              partial availability, databases, 548
nested triggers, 869, 1721
                                                              partitioned tables, 579
.NET Framework, 37-38
                                                              query analysis, 1116
    ADO.NET, 1319-1324
                                                              query optimization, 1028-1030
         connection pooling, 1328
                                                              for remote servers, 1664
         MARS (Multiple Active Result Sets),
                                                              in SSAS, 1473-1474
           1324-1326
                                                              shared job schedules, 362
         Query Notifications, 1328-1330
                                                              SNAC, 221
         System.Data.SqlTypes namespace,
                                                              of SQL Server 2005, 35-36
           1326-1328
                                                                  Business Intelligence Development Studio, 44
    Code Access Security (CAS) permission
                                                                  CLR, 37-38
       sets, 1332
                                                                  DAC, 39
                                                                  data types, 44-45



              How can we make this index more useful? Email us at indexes@samspublishing.com
1964     new features


         Database Mail, 40                          non-T-SQL (non-Transact-SQL), 367
         database mirroring, 41                     non-Transact-SQL (non-T-SQL), 367
         database snapshots, 41                     Nonclustered Index Delete icon, Query Analyzer, 1124
         DMVs, 38                                   Nonclustered Index Scan icon, Query Analyzer, 1124
         encryption, 40                             Nonclustered Index Spool, Query Analyzer, 1126
         MARS, 44                                   nonclustered indexes, 626-627, 973-975
         .NET Framework, 37-38                          costs, 1042-1046
         online index and restore operations, 40        designing, 1016-1017
         query notification, 44                         fill factor, setting, 1010
         Service Broker, 41-42                          rebuilding, 1008
         SMO, 39                                        SQL Server performance, 1269
         snapshot isolation, 43                     nonexistent rows, searching, 1180
         SQL Server Configuration Manager, 37       nonrepeatable reads, transaction isolation
         SQLCMD, 39-40                                levels, 1153
         SSIS, 42                                   NORECOVERY, 321
         SSMS, 36-37                                    transaction logs, 309
         system catalog views, 38-39                normalization, logical database design, 1215
         table and index partitioning, 42-43            benefits of, 1217
    SQL Server Service Broker. See SQL Server           disadvantages of, 1217
       Service Broker                               normalization forms, logical database design,
    for SQL Server Clustering, 516                    1215-1217
    of SQL Server Profiler, 111-112                 NOT NULL, column properties, 590
    SQL Server web services, 1439                   notification, 44
    SSMS, 57-58                                         enhancements, 51-52
    SSNS, CD:1841-1842                                  managing operators, 370-372
    stored computed columns, 580                    notification cycles, SSNS, CD:1845-1846
    stored procedures, 699-700                      Notification Services. See SSNS (SQL Server
    triggers, 834                                     Notification Services)
    user-defined functions, 799-800                 NotificationClasses ADF element, CD:1854-1859
    views, 667                                      notifications, formatting, 1857-1859
    VLDB, CD:1743-1744                              nscontrol, CD:1866
New Job Step dialog, 374                            NSDiagnosticDeliveryChannel, CD:1874
New Project dialog (Visual Studio 2005), 1334       NSDiagnosticEventClass, CD:1873
new xml data type, 1402-1403                        NSDiagnosticEventProvider, CD:1873
    built-in methods, 1411-1430                     NSDiagnosticNotificationClass, CD:1874
         exists() method, 1412, 1420-1421           NSDiagnosticSubscriptionClass, CD:1874
         modify() method, 1412, 1425-1430           NT AUTHORITY\SYSTEM, 253
         nodes() method, 1412, 1423, 1425           NTILE, 1299-1301
         query() method, 1412-1420                      generating page numbers, 1304
         value() method, 1412, 1421-1422            NULL, column properties, 590
    columns, 1404-1406                              num proc buffs, 764, 1264
    FOR XML modes, 1396-1397, 1399                  num proc buffs active, 764, 1264
    schema collections, 1407-1412                   num proc buffs used, 764, 1264
NEXT USED, 615                                      Number of Deadlocks/sec counter (SQLServer:Locks
NLB, MSCS, 522                                        object), 1171
NMO, choosing programming methods (SSNS),
  CD:1847-1848                                      O
NO ACTION, 648
                                                    Object, 1119
NO LOG:transaction logs, 310
                                                    object definition function, 721
NO TRUNCATE, transaction logs, 309
                                                    Object Explorer (SSMS), 66-67
Node ID, 1119
                                                        databases, creating, 556-559
nodes, 1117
                                                        creating tables, 580
    failure of, SQL Server Clustering, 537, 539
                                                        tables
    XML documents, 1378
                                                            creating, 580
nodes() new xml data type method, 1412, 1423-1425
                                                            dropping, 605-606
noise words, FTS, CD:1937
                                                            modifying, 601-604
NOLOCK transaction isolation level, 1205
                                                    Object Explorer tree, 61
non-logged bulk-copy operations, SSIS, 1601-1602
                                                    object level (SSMS), managing permissions, 283-284
         batches, 1602
                                                    object names, T-SQL, CD:1796-1799
         hints, 1603-1604
                                                    ObjectID data column (SQL Profiler), 1169
         parallel loading, 1602-1603
                                                                                     parallelism        1965



objectives of disaster recovery, CD:1778-1779          operators, 361
ObjectName data column (SQL Profiler), 1169                creating with SQL Server Agent Mail, 354
OBJECTPROPERTY, CD:1928                                    managing in SQL Server Agent, 370-372
OBJECTPROPERTY function, 820                           optimistic locking, 1207
objects, identifying objects referenced in stored          snapshot isolation, 1209-1211
  procedures, 717-719                                      timestamp data type, 1207-1209
obtaining information on extended stored               optimization, OLAP database creation, 1523
  procedures, 795                                      OPTIMIZER FOR hints, 1100
ODBC (Open Database Connectivity), 238, 1440           optimizing indexed views, Query Optimizer, 1056-1059
    MDAC, client data access technologies, 243         OR, identifying for query analysis, 1033-1034
OLAP (online analytical processing), 198, 1473         ORDER BY, CD:1808
    design methodologies, 1482-1485                    order by clause, query() new xml data type method,
           ,
    HOLAP 1482                                           1417-1418
            ,
    MOLAP 1481                                         ORDER hint, supplying to bulk-copy operations, 1604
                 ,
    versus OLTP 1480-1481                              Ordered, 1119
    performance, data mining, 1535-1536                OUTER APPLY, 1311
    preparing for database creation, 1482              outer joins, T-SQL, CD:1826-1827
    requirements example, CompSales International,         full outer joins, CD:1833-1835
       1485-1486                                           nested outer joins, CD:1832-1833
        cube creation, 1486-1487                           versus WHERE clause, CD:1827-1832
        cube perspectives, 1524-1525                   OUTPUT, DML, 1280-1284
        data mining, 1526-1534                         Output List, 1119
        generating relational databases, 1523-1524     output parameters
        KPIs, 1525                                         sp executesql, 776-777
        OLAP database creation, 1488. See also             stored procedures, 731-732
          databases, OLAP database creation            OUTPUTBUFFER, 1265
        security and roles, 1536-1537
        SQL Server BIDS, 1487-1488                     P
           ,
    ROLAP 1481
                                                       Package Execution Utility (SSIS), 1574-1576
    SSAS and, 1474-1476
                                                          dtexec utility, 1574, 1576-1577
OLE DB
                                                          dtsrun utility, 1574
    linked servers, 1671
                                                          dtutil utility, 1582-1586
    MDAC, client data access technologies, 243
                                                          packages, running, 1577-1582
OLTP (online transaction processing), 526, 1475
                                                       package loading phase (dtexec), 1576
                 ,
    versus OLAP 1480-1481
                                                       Package Migration Wizard, 212
ON DELETE CASCADE, 847, 1214
                                                       packages
on failure workflows, SSIS, 1547
                                                          running, dtexec utility, 1577-1582
ON FILEGROUP, CREATE FULLTEXT CATALOG, CD:1917
                                                          SSIS, 1546, 1549
ON FULLTEXT CATALOG, CREATE FULLTEXT INDEX,
                                                       PAD_INDEX option, 1010
  CD:1922
                                                       page free space (PFS), 549
on success workflows, SSIS, 1547
                                                       Page locking level (SQL Server), 1177
ON UPDATE CASCADE, 847
                                                       Page Verify, 562
online analytical processing. See OLAP
                                                       page-level locking, compared to row-level locking,
online index, 40
                                                        1185-1186
online index operations, 623
                                                       pages, 548
online indexing, HA, 408
                                                          dirty pages, 555
online indexing operations, 637-638
                                                       paging results, ROW NUMBER, 1301-1305
online restorations, 291, 332
                                                       PAGELOCK optimizer hint, 1206
online restore, 49
                                                       parallel index operations, 624
    HA, 408
                                                       parallel loading, bulk-copy operations (SSIS),
online transaction processing. See OLTP
                                                        1602-1603
OnNullCall parameter (SqlMethod), 1359
                                                       parallel query processing, 1086-1087
Open Database Connectivity (ODBC), 238, 1440
                                                          configuration options, 1088-1089
opening reports, Report Builder, 1640
                                                          identifying, 1089-1090
OPENXML, relational data, 1399-1402
                                                       parallel snapshot preparation, 416
operating modes, database mirroring, 485-486
                                                       parallelism
operating systems, HA, 397
                                                          cost of, CD:1709-1710
operations, SSIS, 1601-1604
                                                          max degree of, CD:1716-1717
                                                          Query Analyzer, 1129




             How can we make this index more useful? Email us at indexes@samspublishing.com
1966      Parameter Table Scan


Parameter Table Scan, Query Analyzer, 1127                   cursor threshold, CD:1710-1711
ParameterDefaults ADF element, CD:1851                       default full-text language, CD:1711-1712
ParameterDefaults ICF elements, CD:1863-1865                 default language, CD:1712-1713
parameters                                                   fill factor, CD:1714-1715
    dm_db_index_physical_stats, 1001                         index create memory, CD:1715
    for sac, 102                                             lightweight pooling, CD:1715-1716
    input parameters, 724-730                                locks, CD:1716
    output parameters                                        max degree of parallelism, CD:1716-1717
        sp executesql, 776-777                               max server memory, CD:1717-1718
        stored procedures, 731-732                           max text repl size, CD:1719
    scalar UDFs, 1345                                        max worker threads, CD:1719-1720
    SqlMethod, 1359                                          min memory per query, CD:1720
    SqlTrigger attribute, 1366                               min server memory, CD:1717-1718
    SqlTriggerContext attribute, 1367                        miscellaneous options, CD:1730-1731
    SqlUserDefinedAggregate attribute, 1364                  nested triggers, CD:1721
    SqlUserDefinedType, 1354-1355                            network packet size, CD:1721-1722
Parse() method, 1355                                         parallelism, CD:1709-1710
partial availability, 46                                     priority boost, CD:1722
    databases, 548                                           query governor cost limit, CD:1722-1723
partial backups, 49, 292, 295                                query wait, CD:1723
    scenarios, 313-315                                       recovery interval, CD:1724
partition functions, creating, 608-610                       remote admin connections, CD:1724
partition schemes, creating, 610-612                         remote login timeout, CD:1725
partitioned tables, 579, 607-608                             remote proc trans, CD:1725
    adding partitions, 614-616                               remote query timeout, CD:1726
    creating, 612-613                                        scan for startup procs, CD:1726
        partition functions, 608-610                         show advanced options, CD:1727
        partition schemes, 610-612                           user connections, CD:1727
    dropping partitions, 616-618                             user options, CD:1728-1729
    switching partitions, 618-621                            XP-related configuration options, CD:1729
    viewing information, 613                            data replication, 479-480
partitioned views, 684-687                              designing for database performance, 1214
    distributed partitioned views, 688-690              filegroups and, 1225-1226
    modifying data through, 688                         FTS, CD:1938-1939
partitioning                                            indexed views and, 693-696
    databases across servers, CD:1767-1768              monitoring SQL Server performance, 1233-1235
    table and index partitioning, 42-43                 new features, 1151
partitions, 554, 607                                    stored procedures, 762-766
    adding table partitions, 614-616                    T-SQL. See T-SQL, performance
    dropping table partitions, 616-618                  VLDB data maintenance, CD:1757
passing object names as parameters, stored          performance counters, Reporting Services, 1653
  procedures, 728-729                               performance logs, 1237
passive server/failover licensing, 32               Performance Monitor, 1236
passwords, SQL login password policies, 247             lock activity, monitoring, 1169-1171
PATH, 1446                                              monitoring values, 1237-1238
PATH mode, XML, 1393-1396                               replication monitoring, 477
PATH secondary index (XML), 1434                        views, 1236-1237
patterns, SQL Server web services, 1440-1441        performance output, Query Editor (SSMS), 76-78
patterns of disaster recovery, CD:1773-1777         permission granularity, 248
pause indexing, CD:1927                             permissions, 248, 266-267
peer-to-peer replication model, data replication,       access permissions, controlling, 1468-1469
  431-433                                               database mirroring, 492
PERFMON, 1263                                           Execute Report Definitions, 1645
perfmon counters, access to, 1261                       managed database objects, 1332
performance. See also SQL Server performance                 three-permission sets, 1332-1334
    configuration options, CD:1703                      managing, 277
        ad hoc distributed queries, CD:1704                  with SSMS, 277-278
        affinity I/O mask, CD:1704-1706                      with SSMS (at database level), 281-283
        affinity mask, CD:1706-1707                          with SSMS (at object level), 283-284
        AWE enabled, CD:1707-1709                            with SSMS (at server level), 278-280
        CLR enabled, CD:1709                                 with T-SQL, 285-286
                                                                              proxy accounts          1967



    roles, 258                                             statistics, 1090-1091
    securing reports, 1647-1648                            triggers, 1094
    setting on models, 1645                            proc cache active, 764, 1264
    system permissions, securing reports, 1648         proc cache size, 764, 1264
    user-defined functions, 824-825                    proc cache used, 764, 1264
perspectives, cubes, 1524-1525                         PROCCACHE, 1264-1265
PFS (page free space), 549                             procedural data integrity, implementing, 643
phantom reads, transaction isolation levels, 1153      procedure caches
Physical Operation, 1118                                   SQL Server performance counters, 1260
physical reads, STATISTICS IO, 1141                        stored procedures, 763-764
PhysicalDisk object, 551                               procedure status, returning in stored procedures,
pipes, SQL Browser, 228                                  732-733
PIVOT, 1305-1309                                       procedures
plan cache, 1072                                           HA, 398
    monitoring, 1077                                       startup procedures, 778-781
         sys.dm exec cached plans, 1077-1079               stored procedures, transactions, 899-904
         sys.dm exec plan attributes, 1081-1082        Proceedings of the 31st International Conference on
         sys.dm exec query stats, 1079-1081              Very Large Data Bases (i), 1432
         sys.dm exec sql text, 1079                    processadmin, 259
plan guides, managing Query Optimizer, 1103-1109       processes
planning                                                   deadlocks. See deadlocks
    for data replication, 443                              locking contention, 1188-1189
    disaster recovery, 1790                                     identifying, 1189-1191
plans                                                           minimizing, 1192-1193
    developing for backups and restoration, 292-293    processing
    maintenance plans, 338                                 snapshot agents, 438-439
point in time, restoring to, 331-332                       transactions, 875
point of failure, restoring to, 328-330                processing hints, Query Optimizer, 1098, 1101
point-in-time reporting databases, providing with      processing instructions, XML, 1378
  database snapshots, 930                              processor affinity, 1704
populating cubes, with data in OLAP database           processors, monitoring, 1244-1249
  creation, 1509-1510                                  profiler, 90-91
population, CD:1922                                    Profiler GUI, 123
POPULATION TYPE, CREATE FULLTEXT INDEX,                    saving trace output to, 125
  CD:1922-1924                                         programming methods, SSNS, 1847-1848
PORTS, 1446                                            projects, managing in SSMS, 79-81
ports, SQL Browser, 228                                promoting properties in models, 1634
Predicate, 1119-1121                                   properties
predicate transitivity, query processing, 1083             alert properties, 382-384
prefixes, format files (lengths), 1595                     columns, 590
primary data files, 550                                         changing with T-SQL, 599
PRIMARY KEY constraint, 596, 643-644                            computed columns, 593-594
principal database servers, database mirroring, 482             IDENTITY, 591-592
principal role, database mirroring, 485                         NULL and NOT NULL, 590
principals, 248, 251                                            ROWGUIDCOL, 592-593
    logins, 251-253                                        configuring, SQL Server Agent, 362-363
    roles, 258                                             DATABASEPROPERTYEX, 564
         application roles, 264-265                        job properties, 373
         fixed-database roles, 259-261                     promoting in models, 1634
         fixed-server roles, 258-259                   PROPERTY secondary index (XML), 1435
         public roles, 261-262                         providers, client data access technologies, 237
         user-defined roles, 262-264                   Providers ADF element, CD:1859-1862
    user/schema separation, 257-258                    providing events to applications, SSNS, CD:1871-1874
    users, 254-256                                     provisioning, 225
PRINT, CD:1900                                             client access provisioning, 225-227
priority boost, 1722                                   proximity
problems with query optimization, 1090                     CONTAINS, FTS, CD:1935
    index design, 1092                                     CONTAINSTABLE, FTS, CD:1935
    large complex queries, 1094                        proxy, 1441
    search arguments, 1092-1093                        proxy accounts, configuring (SQL Server Agent),
                                                         367-368



             How can we make this index more useful? Email us at indexes@samspublishing.com
1968      public roles


public roles, 261-262                                    Query Designer, 78-79
publication server, data replication, 418-420            Query Editor types, 73
publications, 420                                        regular expressions and wildcards, 75-76
    creating for data replication, 456-459, 461-462   query execution plan, 1030
    data replication, 421                             query governor, managing Query Optimizer,
Publisher role, 1647                                   1111-1113
publisher subscriber replication model, data          query governor cost limit, CD:1722-1723
 replication, 427-428                                 query indexes, compared to update performance
publishing, enabling for data replication, 451-455     indexes, 1024-1026
pull subscriptions, 434                               query notification, 44
purging data, VLDB data maintenance, CD:1755-1761        ADO.NET, 1328-1330
                                                      query optimization
Q                                                        defined, 1027
                                                         new features, 1028-1030
QA (quality assurance), testing database snapshots,
                                                         problems with, 1090-1094
 939-940
                                                      Query Optimizer, 1027, 1030, 1115
   HA, 398
                                                         indexes, evaluating, 979-982
queries
                                                         managing, 1094-1096
   100 worst-performing queries, 1265-1269
                                                             forced parameterization, 1109-1111
   ad hoc distributed queries, CD:1704
                                                             join hints, 1098
   analyzing with SQL Server Profiler, 145
                                                             plan guides, 1103-1109
   designing with BIDS Report Designer, 1619
                                                             processing hints, 1098, 1101
   distributed queries, linked servers, CD:1672
                                                             query governor, 1111-1113
   identifying ad hoc queries, SQL Server Profiler,
                                                             table hints, 1096-1097
     147-148
                                                             USE PLAN, 1101-1103
   model queries, debugging, 1636-1638
                                                         optimization steps, 1032
   partitioned tables, 607
                                                             execution plan selection, 1070-1072
   problems with query optimization, 1094
                                                             join selection, 1059-1070
   recursive queries, CTE, 1286-1295
                                                             query analysis, 1032-1034
   semantic queries, 1642
                                                             row estimation and index selection,
   subquery processing, 1067-1070
                                                               1034-1059
query analysis
                                                         table scans, 970
   new features, 1116
                                                      query parameterization, query plan reuse, 1074-1075
   OLAP database creation, 1523
                                                      query plan aging, 1075
   Query Optimizer, 1032-1034
                                                      query plan caching, 1072-1073
   SQL Server Profiler, 1147-1148
                                                         stored procedures, performance, 763
Query Analyzer, 1117-1118
                                                      query plan execution, limiting with query governor,
   execution plan ToolTips, 1118-1121
                                                       1111-1113
   graphical execution plans, saving and viewing,
                                                      query plan reuse, 1073-1074
     1130-1131
                                                         query parameterization, 1074-1075
   logical and physical operator icons. See icons,
                                                      query plans
     logical and physical operator icons (Query
                                                         automatic query plan recompilation, 765-766
     Analyzer)
                                                         forcing recompilation, 768-771
   stored procedures, analyzing, 1129-1130
                                                         query plan caching, 763
Query Analyzer (QA), 205
                                                         recompiling, 1076-1077
Query Builder, SSIS, 1552-1554
                                                         shared query plans, 764
query compilation, 1030
                                                      query processing
   DML statements, compiling, 1031
                                                         DISTINCT, 1084
   optimization steps, 1032
                                                         GROUP BY, 1083-1084
        execution plan selection, 1070-1072
                                                         parallel query processing, 1086-1090
        join selection, 1059-1070
                                                         predicate transitivity, 1083
        query analysis, 1032-1034
                                                         UNION, 1084-1086
        row estimation and index selection,
                                                         UNION ALL, 1086
          1034-1059
                                                      query statistics, 1139
Query Designer, Query Editor (SSMS), 78-79
                                                         datediff(), 1145
Query Editor, SSMS, 71-73
                                                         STATISTICS IO, 1139-1140
   disconnected editing, 74
                                                             analyzing, 1141-1142
   editing SQLCMD scripts, 74-75
                                                             LOB reads, 1141
   performance output, 76-78
                                                             logical reads, 1141
                                                                remote admin connections              1969



        physical reads, 1141                           REBUILD, indexes, 634
        read-ahead reads, 1141                         Rebuild Index task, 954
        scan count, 1140                               rebuilding
   STATISTICS PROFILE, 1146                                indexes, VLDB data maintenance, CD:1752-1755
   STATISTICS TIME, 1142-1145                              nonclustered indexes, 1008
   STATISTICS XML, 1146-1147                           RECEIVE, CD:1896, CD:1900
query trees, 1031                                      receiving email, Database Mail, 354
query wait, CD:1723                                    reciprocal principal/mirror reporting configuration,
query() new xml data type method, 1412-1420              941-942
   for clause, 1415-1416                               RECOMPILE hints, 1100
   order by clause, 1417-1418                          recompiling query plans, 1076-1077
   return clause, 1418-1420                            recovering, full database recovery, 330
   where clause, 1417                                  recovery
querying                                                   enhancements, 49
   indexes, 975-978                                        HA, 408
   sys.dm_tran_locks view, 1161-1164                       replication monitoring, 478-479
questions, SSMS, 62                                    recovery interval, CD:1724
queue monitor, CD:1911                                 Recovery Model setting, 558
queues                                                 recovery models, 296-300
   creating for message storage, SQL Server Service    recovery point objective (RPO), CD:1778
     Broker, CD:1887-1889                              recovery processes, transactions, 885-886, 889-891
   transmission queues, CD:1890                            analysis phase, 890
quorum drives, 519                                         checkpoint process, 886-889
quorums, 520                                               redo (roll-forward) phase, 890
                                                           undo (rollback) phase, 891
R                                                      recovery time objective (RTO), CD:1778
                                                       recursion, 871
RAID (redundant array of inexpensive disks), 1227
                                                           nested stored procedures, 745-748
RAID Level 0, 1227-1228
                                                       recursive queries, CTE, 1286-1288
RAID Level 1, 1228-1229
                                                           expanding hierarchies, 1288-1294
RAID Level 5, 1230-1231
                                                           MAXRECURSION, 1294-1295
RAID Level 10, 1229
                                                       recursive triggers, 870-871
RANGE LEFT partitions, 610
                                                       redistributing SNAC, with custom client
RANGE RIGHT partitions, 610
                                                         applications, 231
range searching, key-range locking, 1178-1180
                                                       redo (roll-forward) phase, transaction recovery
RANK, 1298-1299
                                                         process, 890
ranking functions, 1295
                                                       redundant array of inexpensive disks (RAID), 1227
    DENSE RANK, 1298-1299
                                                       redundant data, denormalization, 1219-1220
    NTILE, 1299-1301
                                                       referential integrity, 642
         generating page numbers, 1304
                                                           cascading FOREIGN KEY constraint, 648-650
    RANK, 1298-1299
                                                           DML triggers, 845-847
    ROW NUMBER, 1295-1297
                                                           VLDB data maintenance, CD:1759
         for paging results, 1301-1305
                                                       registered servers, SSMS, 65-66
         partitioning by ROW NUMBER, 1297-1298
                                                       regular expressions, Query Editor (SSMS), 75-76
RAW mode, XML, 1379-1383
                                                       reinitialize subscriptions having data validation
RDL (Report Definition Language), 213, 1610
                                                         failures, 442
read committed isolation, 1155
                                                       RELATED CONVERSATION keyword, CD:1892
READ COMMITTED option (SET TRANSACTION
                                                       related system catalogs, developing managed
  ISOLATION LEVEL statement), 911
                                                         database objects, 1374-1375
Read Committed Snapshot, 1151
                                                       relational data, XML
read committed snapshot isolation, 1155-1156
                                                           FOR XML modes, 1378-1383, 1385-1391,
read uncommitted isolation, 1154
                                                              1393-1399
READ UNCOMMITTED option (SET TRANSACTION
                                                           OPENXML, 1399-1402
  ISOLATION LEVEL statement), 911
                                                       relational databases, generating, 1523-1524
read-ahead reads, STATISTICS IO, 1141
                                                       relational index options, for CREATE INDEX, 629
READCOMMITTED transaction isolation level, 1205
                                                       Relational OLAP (ROLAP), 1481
READCOMMITTEDLOCK transaction isolation
                                                       relationships, zero-to-one relationships
  level, 1205
                                                         (denormalization), 1224-1225
READPAST transaction isolation level, 1206
                                                       reliability, enterprise computing, 515
READUNCOMMITTED transaction isolation level, 1205
                                                       remote admin connections, CD:1724
reapplying fill factor, indexes, 1010-1011



             How can we make this index more useful? Email us at indexes@samspublishing.com
1970      remote connections


remote connections, enable launch of (Surface Area          saving reports, 1640
  Configuration) tool, 223                                  sorting, 1639
Remote Delete, Query Analyzer, 1128                     Report Builder role, 1647
Remote Insert, Query Analyzer, 1128                     Report Definition Language (RDL), 213, 1610
remote installation, installing SQL Server 2005, 193    report design tools, 1613
remote login timeout, CD:1725                           report displays, 1236
remote proc trans, CD:1725                              report server database catalog, configuring,
remote procedure calls (RPC), CD:1663                     1617-1618
Remote Query, Query Analyzer, 1128                      Report Viewer, 202-203
remote query timeout, CD:1726                           report viewer controls, 1610-1611
Remote Scan, Query Analyzer, 1128                           building applications for SQL Server Reporting
remote servers, CD:1663                                       Services 2005, 1653
    managing, CD:1664-1666                                       ASP .NET report controls, 1654-1659
         setup, CD:1666-1671                            Reporting Services, 23, 184, 1607-1608. See also
    new features for, CD:1664                             reports
remote service bindings, creating for conversations,        configuration options and tools, 1615-1618
  CD:1908                                                   enhancements, 53
remote stored procedures, 755-756                           Enterprise Edition, 1613
Remote Update, Query Analyzer, 1128                         installing, 1613-1615
removing                                                    migrating, 212-213
    database mirroring, 505-507                             performance and monitoring tools, 1652-1653
    linked servers, CD:1681-1682                            Report Builder, 1610
    mappings for linked servers, sp                         report viewer controls, 1610-1611, 1653
       droplinkedsrvlogin, CD:1685-1686                          ASP .NET report controls, 1654-1659
    snapshots, from cache reports, 1651                     scripting support, 1646
renumbering columns, 1599-1601                              system architecture, 1611-1613
REORGANIZE, indexes, 634                                    upgrading, 218-219
Reorganize Index task, 954                                  web service, 1609
repartition streams, 1089                                   Windows service, 1609
    Query Analyzer, 1129                                Reporting Services Configuration tool, 1615
repeatable read isolation, 1156-1157                    Reporting Services features, sac, 102
REPEATABLE READ option (SET TRANSACTION                 /Reports, 1617
  ISOLATION LEVEL statement), 911                       reports, 1610
REPEATABLEREAD transaction isolation level, 1205            ad hoc reporting, 1645
replaying trace data, 129-131                               building with BIDS Report Designer, 1620
replicated databases, upgrading, 217                        cached reports, 1651-1652
replication. See also data replication                      deploying, 1646
    database mirroring and, 511-512                         designing with BIDS Report Designer, 1619
    enhancements, 50                                             adding interactivity, 1625-1626
    immediate updating, 19                                       building reports, 1620
    merge replication, 19                                        creating simple lists, 1620-1621
    snapshot replication, 18                                     deploying sample reports, 1626
    transactional replication, 18-19                             grouping and sorting, 1621-1623
replication agents, 436-437                                      layout choices, 1619-1620
    agent history cleanup: distribution, 442                     queries, 1619
    distribution agent, 441                                      tables and hierarchies, 1623-1624
    distribution cleanup: distribution, 442                 designing with models and Model Designer,
    expired subscription cleanup, 442                         1629-1630
    log reader agent, 439-440                               designing with Report Builder, 1627-1629
    merge agent, 442                                        drill-through reports
    reinitialize subscriptions having data validation            customizing, 1640-1642
       failures, 442                                             generating, 1639
    replication agents checkup, 442                              models, 1636
    snapshot agent, 437-439                                 Execute Report Definitions, 1645
replication agents checkup, 442                             execution options, 1650-1651
Report Builder, 1610                                        models. See models
    ad hoc reporting, 1645                                  opening, in Report Builder, 1640
    design surface, 1634-1635                               saving, in Report Builder, 1640
    designing reports, 1627-1629                            securing, 1647-1648
    models, 1632                                            security, 1644
    opening reports, 1640                                   subscriptions, 1648-1650
                                                                                           rules      1971



/ReportServer, 1617                                    retrieval of archived data, VLDB data maintenance,
requirements                                             CD:1759-1760
    for installing SQL Server 2005, 173                retrieving database option information, 564-566
         hardware requirements, 174-175                Retry Attempts, 376
         software requirements, 175-179                Retry Interval, 376
    for SSNS, 1842-1843                                Retry options, 376
    user requirements. See user requirements           return clause, query() new xml data type method,
requirements phase, OLAP design                          1418-1420
  methodologies, 1483                                  returning procedure status, stored procedures,
resource database, 158                                   732-733
resource usage, CD:1927                                reusing query plans, 1073-1075
responsibilities of system administrators, 156-157     reverting to database snapshots, 924
restorating, 317                                           to database snapshots for recovery, 927-928,
restoration                                                   938-939
    developing a plan, 292-293                         REVOKE, 266, 285
    new features, 291-292                              rewriting stored procedures as functions, 826-827
    online restoration, 291                            RID Lookup, Query Analyzer, 1128
    restore information, 324-325                       ROBUST PLAN hints, 1099
    scenarios, 326                                     ROLAP (Relational OLAP), 1481
         online restorations, 332                      role assignments, 1647
         restoring system databases, 333-335           role switching, database mirroring, 486
         restoring to a different database, 327        roles, 258
         restoring to point in time, 331-332               application roles, 264-265
         restoring to point of failure, 328-330            of database mirroring, 485
         restoring transaction logs, 328                   fixed-database roles, 259-261
    with SSMS, 322-323                                     fixed-server roles, 258-259
    with T-SQL, 318-321                                    models, 1636
         transaction logs, 321-322                              ,
                                                           OLAP 1536-1537
RESTORE, 318                                               public roles, 261-262
restore, enhancements, 48-49                               user-defined roles, 262-264
RESTORE DATABASE, 318-319                              ROLLBACK, CD:1901
Restore dialog, SSMS, 60                               rollback transaction statement, 783
RESTORE FILELISTONLY, 324                              root-level certificate, 235
RESTORE HEADERONLY, 325                                routing, building routes to map conversations between
restore operations, 40                                   SQL Server instances, CD:1908
RESTORE TRANSACTION, 318                               Row Count Spool, Query Analyzer, 1126
RESTORE VERIFYONLY, 325                                row estimation, Query Optimizer, 1034-1035
restorefile, 325                                           estimating access path costs, 1040-1048
restorefilegroup, 326                                      evaluating SARG and join selectivity, 1035-1040
restorehistory, 326                                        multiple indexes, 1048-1056
restores, online restores, 49                              optimizing indexed views, 1056-1059
restoring                                              Row ID (RID) locking level (SQL Server), 1177
    database backups to new locations, 573             ROW NUMBER, 1295-1297
    to different databases, 327                            paging results, 1301-1305
    full-text catalogs, 1938                               partitioning, 1297-1298
    to point in time, 331-332                              partitioning by ROW NUMBER, 1297-1298
    to point of failure, 328-330                       ROW OVERFLOW DATA, 588
    system databases, 333-335                          row-level locking, compared to page-level locking,
    system tables, 325-326                               1185-1186
    transaction log backups, 330-331                   @@ROWCOUNT, T-SQL, 1836-1837
    transaction logs, 328                              ROWGUIDCOL, columns, 592-593
    VLDB, CD:1745-1747                                 ROWLOCK optimizer hint, 1206
Restrict Access, 562                                   rows, 579
RESTRICT IP, 1447                                          indexes, estimating, 988-989
restrictions                                               new features, 579
    of database snapshots, 925-926                     ROWS_PER_BATCH hint, supplying bulk-copy
    on defaults, 664-665                                 operations to, 1603-1604
    INSTEAD OF triggers, 859                           RPC (remote procedure calls), CD:1663
result columns, dm_db_index_physical_stats,            RPO (recovery point objective), CD:1778
  1002-1003                                            RTO (recovery time objective), CD:1778
                                                       rules, data integrity, 659-660



             How can we make this index more useful? Email us at indexes@samspublishing.com
1972      running


running                                               search arguments
    packages, dtexec utility, 1577-1582                   identifying for query analysis, 1032-1033
    SSIS Wizard, 1556-1566                                problems with query optimization, 1092-1093
runtime, measuring with datediff(), 1145              search phrase, CD:1934
                                                      secondary data files, 550
S                                                     secondary GAM (SGAM), 550
                                                      securables, 248, 265
sac, 91, 104
                                                      Secure Sockets Layer (SSL), 388, 1445
    parameters, 102
                                                      securing reports, 1647-1648
SAC (Surface Area Configuration) tool, 223
                                                      security, 249
safeguarding databases prior to making mass
                                                          authentication, 249-251
  changes, database snapshots, 928-929
                                                          database snapshots, 942
SAN (storage area network), 520, 549
                                                          enhancements to, 47-48
SARG
                                                          execution context, 286-288
    computed columns, 1038-1040
                                                          models, 1643-1644
    evaluating, 1035-1036
                                                          module execution context, 248
    inequality operators, 1037
                                                               ,
                                                          OLAP 1536-1537
    LIKE, 1038
                                                          permission granularity, 248
    search argument problems, 1092
                                                          permissions, 266-267. See also permissions
savepoints
                                                          principals, 251
    explicit user-defined transactions, processing,
                                                              logins, 251-253
       878-879
                                                              users, 254-255. See also users
    triggers, transactions, 909-910
                                                          securables, 265
SAVESYSDB, 219
                                                          SQL login password policies, 247
saving
                                                          SQL Server 2005 security components, 248
    graphical execution plans, 1130-1131
                                                          SQL Server Agent, 370
    reports, Report Builder, 1640
                                                          SQL Server Service Broker, conversation
    traces (SQL Server Profiler), 123-125
                                                             encryption with certificates, CD:1901-1908
scalability, enterprise computing, 515
                                                          user/schema separation, 247
scalar functions, 803-804
                                                      security components, 248
    CREATE FUNCTION, 807
                                                      security roles, 1647
scalar UDFs (user-defined functions), 1344-1347
                                                      securityadmin, 259
Scalar Vector Graphics (SVG), 351
                                                      Seek Predicates, 1119
scaling out, 522
                                                      select @@SERVERNAME, CD:1785
scan count, STATISTICS IO, 1140
                                                      select @@SERVICENAME, CD:1785
scan for startup procs, 1726
                                                      select @@VERSION, CD:1785
SCC (System Configuration Checker), 174
                                                      SELECT COUNT(*), CD:1807
scheduled rules, 1845
                                                      SELECT statement, 711
scheduling maintenance plans, Maintenance Plan
                                                          users, 257
  Wizard, 956-959
                                                      selecting
SCHEMA, 1450
                                                          columns, from sys.databases catalog view, 566
schema collections, new xml data type, 1407-1412
                                                          indexes, 978
schema locks, 1175
                                                      self-configuring options, CD:1695
SCHEMABINDING
                                                      self-signed certificates, 235, CD:1901
    creating views with T-SQL, 678
                                                      semantic queries, 1642
    indexes, 639
                                                      SEND, CD:1895
    user-defined functions, 809
                                                      SEND ON CONVERSATION, CD:1896
schemes, creating partition schemes, 610-612
                                                      sending email (Database Mail), 347-348, 351-353
screens
                                                      sequence, Query Analyzer, 1128
    Feature Selection screen, 185
                                                      sequence clustering, data mining algorithms, 1527
    installing SQL Server 2005, 180-190
                                                      sequence trees, 1031
    Instance Name screen, 185
                                                      SERIALIZABLE option (SET TRANSACTION ISOLATION
    Service Account screen, 187
                                                        LEVEL statement), 912
scripting
                                                      serializable read isolation, 1157-1158
    alerts, 387
                                                      SERIALIZABLE transaction isolation level, 1205
    jobs, 387
                                                      serialization locking, granularity, 1178-1180
    support, in Reporting Services, 1646
    variables, with sqlcmd, 94-95                     server aliases, 234
scripting replication, 470-471                        server clusters, 518
                                                                              Solution Explorer          1973



server components, installing, 1613                     SHOWPLAN SET options, 1133
server endpoint layer, 224-225                              SHOWPLAN ALL, 1136
    client access provisioning, 225-227                     SHOWPLAN TEXT, 1134-1135
server instance isolation, HA, 398-399                      SHOWPLAN XML, 1137
server level (SSMS), managing permissions, 278-280      SHOWPLAN TEXT, 1134-1135
server mode, report controls, 1654                      SHOWPLAN XML, 1137
server network protocols, ensuring appropriate          Shrink Database dialog, 571
  network protocols are configured on server, 222-223   shrinking
SERVER ROLE, 252                                            databases, 568
server trace log, Reporting Services, 1652                      DBCC SHRINKDATABASE, 568-569
server-side traces                                              DBCC SHRINKFILE, 569-571
    creating and starting, script for, 139-140                  Maintenance Plan Wizard, 952-953
    defining, 131-136, 138-140                                  shrinking log files, 571
    monitoring running traces, 141-142                          with SSMS, 571-573
    stopping, 143-144                                       log files, 571
serveradmin, 259                                        side-by-side migration (installing SQL Server
servers                                                   2005), 204
    federated servers, CD:1767-1768                         avoiding an unintentional in-place upgrade during
    linked servers. See linked servers                        setup, 204
    master servers, 388-389                                 migrating Analysis Services, 209-210
    multiserver jobs, creating, 390                         migrating databases, 206-209
    remote servers. See remote servers                      migrating DTS packages, 211-212
    target servers, 388                                     migrating Notification Services (SSNS), 213
        enlisting, 389-390                                  migrating Reporting Services, 212-213
Service Account screen, 186-187                             SQL Server client tools, 205
Service Broker, 24, 41-42                               Simple Mail Transfer Protocol (SMTP), 339, CD:1841
service program, CD:1880                                Simple Object Access Protocol (SOAP), 1440, 1609,
services, defining to send and receive messages (SQL      CD:1841
  Server Service Broker), CD:1889-1890                  simple recovery model, 299-300
session snapshots, 1650-1651                            simplifying data manipulation with views, 669-670
    removing from cache reports, 1651                   SITE, 1448
sessions, report execution options, 1650                site autonomy, 443
SET CHANGE TRACKING, ALTER FULLTEXT INDEX,              SMO (SQL Server Management Objects), 39, 206
  CD:1925                                               SMS (Short Messaging Service), CD:1841
SET DEFAULT, 649                                        SMTP (Simple Mail Transfer Protocol), 339, CD:1841
SET LOCK_TIMEOUT command, 1191                              creating accounts in Database Mail, 342
set nocount on, 763                                     SMTP failover priority, 344
SET NULL, 648                                           SNAC (SQL Native Client), 221
SET REMOTE PROC TRANSACTIONS, 755                           client data access technologies, 238-240
SET ROWCOUNT, 1278                                          installing for clients, 230
SET SHOWPLAN XML ON, 78                                     redistributing with custom client applications, 231
SET TRANSACTION ISOLATION LEVEL command, 1203           snapshot agent, 437-439
SET TRANSACTION ISOLATION LEVEL READ                    snapshot backups, 337
  COMMITTED statement, 1155                                 VLDB, 1748
SET TRANSACTION ISOLATION LEVEL statement,              snapshot databases, 924
  options, 911-912                                      snapshot isolation, 43, 1151, 1158-1159
SET TRANSACTION ISOLATION SERIALIZABLE                      optimistic data type, 1209-1211
  command, 1178                                         snapshot isolation levels, HA, 410
setupadmin, 259                                         SNAPSHOT option (SET TRANSACTION ISOLATION
SGAM (secondary GAM), 550                                 LEVEL statement), 912
SGML (Standard Generalized Markup                       snapshot replication, 18, 444-445
  Language), 1377                                       snapshots. See database snapshots
shared disk arrays, 519                                 SOAP (Simple Object Access Protocol), 1440, 1609,
shared job schedules, 362                                 CD:1841
shared locks, 1172                                      SOAP nodes, 1442
shared nothing disk arrays, 519                         SOAP receivers, 1442
shared query plans, stored procedures, 764-765          SOAP senders, 1442
Short Messaging Service (SMS), 1841                     software requirements for installing SQL Server 2005,
show advanced options, 1727                               175-179
SHOWCONTIG, 1263-1264                                   software scaling, 522
SHOWPLAN ALL, 1136                                      Solution Explorer, 81-82



             How can we make this index more useful? Email us at indexes@samspublishing.com
1974      Sort


Sort, Query Analyzer, 1128                             spid data column (SQL Profiler), 1169
sorting                                                SPLIT RANGE, 614
    in Report Builder, 1639                            split-brain scenarios, 520
    in reports, with BIDS Report Designer, 1621-1623   SQL Browser, 227-228
source code control, stored procedures, 789-790        SQL Clustering, building solutions with HA options,
source control, integrating SSMS, 81-83                  402-404
source databases, 923                                  SQL injection attacks, avoiding with T-SQL,
    number of database snapshots per, 943                CD:1799-1806
    reverting from database snapshots, 938-939         SQL Mail, 365
sp helplinkedsrvlogin, 1688                            SQL Native Client ODBC driver, 238
sp addextendedproc, 794                                SQL Native Client OLE DB provider, 237
sp addlinkedserver, CD:1673-1680                       SQL Native Client. See SNAC
sp addlinkedsrvlogin, CD:1684-1686                     SQL Profiler
sp catalogs, CD:1688                                       data columns, 1168
sp columns ex, CD:1688                                     lock events, 1167
sp configure, 171, 1246, CD:1697-1698                  SQL Profiler Templates, 114
SP Counts, 114                                         SQL Server
sp createstats, 171                                        installing for SQL Server Clustering, 528-537
sp dboption, 563-564                                       transaction management. See transaction
sp delete jobsteplog, 376                                     management
sp dropserver, CD:1681-1682                                upgrading, 197
sp executesql, 774-776                                 SQL Server 2005, indexes, 970
    output parameters, 776-777                         SQL Server 2005 Analysis Services. See SSAS
sp executsql, CD:1805                                  SQL Server 2005 Developer Edition, 28
sp foreignkeys, CD:1688                                    licensing, 31
sp help, 171                                           SQL Server 2005 Enterprise Edition, 26
sp help constraint, 657                                SQL Server 2005 Express Edition, 28-29
sp helparticle, 472                                        licensing, 31
sp helpconstraint, 606                                 SQL Server 2005 Full-Text Search. See FTS
sp helpdb, 171, 566                                    SQL Server 2005 Mobile Edition, 29
SP HELPDINDEX, 636                                         licensing, 31
sp helpdistributor, 472                                SQL Server 2005 Notification Services. See SSNS
sp helpextendedproc, 795                                 (SQL Server Notification Services)
sp helpfile, 171                                       SQL Server 2005 Reporting Services. See Reporting
sp helplinkedsrvlogin, CD:1686-1687                      Services
sp helppublication, 472                                SQL Server 2005 security model, 248-249
sp helpsubscriberinfo, 472                             SQL Server 2005 Standard Edition, 25-26
sp helpsubscription, 472                               SQL Server 2005 Workgroup Edition, 27-28
sp helptext, 720                                       SQL Server Agent, 16-17
sp indexes, CD:1688                                        alerts, 381
sp linkedservers, CD:1680-1681, CD:1687                         properties, 382-384
sp lock, 171                                                    responses, 384-386
sp monitor, 1242                                           configuring, 362
sp primarykeys, CD:1688                                         email notification, 365-366
sp procoption, 141                                              properties, 362-363
sp recompile, 771                                               proxy accounts, 367-368
sp send dbmail, 348                                             startup account, 363-365
sp serveroption, CD:1682-1683                              enhancements, 49
sp setapprole, 265                                         error logs, viewing, 368-369
sp settriggerorder, 840                                    Job Activity Monitor, 362
sp spaceused, 171, 594                                     jobs. See jobs
sp tables ex, 1688                                         operators, managing, 370-372
sp trace create, 131                                       security, 370
sp trace setevent, 131                                     shared job schedules, 362
sp trace setfilter, 131                                SQL Server Agent Mail, 354
    column operator values, 139                            alert mail notifications, 356-357
sp trace setstatus, 131, 143                               job mail notifications, 354-355
sp who, 171                                            SQL Server Agent proxy accounts, 367-368
SP1, installing, 193-195                               SQL Server Analysis Services. See SSAS
sparse file size management, database                  SQL Server BIDS, OLAP, 1487-1488
  snapshots, 943
                                                               SQL Server Upgrade Advisor               1975



SQL Server client tools, side-by-side migration, 205       user-defined counters, 1260-1261
SQL Server Clustering, 516                                 users, 1259
   active/active configuration, 517                     SQL Server procedure cache, stored procedures,
   active/passive configuration, 516                     763-764
   installing, 524-525                                  SQL Server Profiler, 17, 1147-1148
       configuring SQL Server database disks,              analyzing, slowed stored procedures or
         525-526                                             queries, 145
       Connection Test Program, 539-542                    application progress, monitoring, 150-152
       failure of nodes, 537-539                           architecture, 112-113
       MSCS, 527                                           auto-update statistics, monitoring, 150
       network interfaces, 527                             deadlocks, 145-147
       SQL Server, 528-537                                     monitoring, 1200-1203
   MSCS, 517-524                                           indexes, selecting, 978
   new features, 516                                       lock activity, monitoring, 1167-1170
   problems with, 543                                      monitoring running traces, 141-142
SQL Server Configuration Manager, 16, 37, 364              new features, 111-112
SQL Server database disks, configuring, 525-526            performance bottlenecks, identifying, 148-149
SQL Server instance architecture, CD:1694                  queries, identifying ad hoc queries, 147-148
SQL Server Integration Services. See SSIS (SQL Server      replaying trace data, 129-131
 Integration Services)                                     server-side traces
SQL Server Lock Manager, 1160                                  defining, 131-140
SQL Server Management Objects (SMO), 39, 206                   stopping, 143-144
SQL Server Management Studio                               traces
   linked servers, setting up, CD:1689-1692                    analyzing trace output with Database Engine
   replication monitoring, 474-476                                Tuning Advisor, 128-129
SQL Server Management Studio (SSMS). See SSMS                  creating, 113-116
 (SQL Server Management Studio)                                data columns, 118-120
SQL Server Management Studio wizards, data                     events, 116-118
 replication. See data replication, setting up                 executing, 123
SQL Server Notification Services. See SSNS                     exporting, 123
SQL Server performance                                         filters, 121-122
   100 worst-performing queries, 1265-1269                     importing, 125-126
   DBCC, 1261                                                  importing into tables, 126-128
       DBCC SQLPERF, 1262-1263                                 saving, 123
       INPUTBUFFER, 1265                                       saving Profiler GUI output, 125
       OUTPUTBUFFER, 1265                                      saving to files, 124
       PERFMON, 1263                                           saving to tables, 124
       PROCCACHE, 1264-1265                                user configurable events, 151
       SHOWCONTIG, 1263-1264                            SQL Server Service Broker, CD:1875
   nonclustered indexes, 1269                              basics of, CD:1876-1879
   Performance Monitor, 1236-1238                          constructs, CD:1881
   performance monitoring approach, 1235-1236                  creating queues for message storage,
   SQL Server performance counters, 1257-1258                     CD:1887-1889
       locks, 1259                                             defining messages and choosing message
       monitoring disk activity, 1259                             types, CD:1882-1886
       MSSQL$:Plan Cache Object, 1258                          defining services to send and receive
       procedure cache, 1260                                      messages, CD:1889-1890
       user-defined counters, 1260-1261                        planning conversations between services,
       users, 1259                                                CD:1890-1892
   tempdb, 1269                                                setting up contracts for communication,
   Windows performance counters, 1239                             CD:1886-1887
       monitoring disk systems, 1254-1257                  designing example systems, CD:1880-1881
       monitoring memory, 1250-1254                        distributed messaging, CD:1875
       monitoring network interfaces, 1239-1243            routing and security, conversation encryption with
       monitoring processors, 1244-1249                      certificates, CD:1901-1908
SQL Server performance counters, 1257-1258                 system catalogs, CD:1909-1911
   locks, 1259                                          SQL Server Service Broker, 24, 41-42
   monitoring disk activity, 1259                       SQL Server Surface Area Configuration tool, 16
   MSSQL$:Plan Cache Object, 1258                       SQL Server Upgrade Advisor (UA), 197
   procedure cache, 1260




             How can we make this index more useful? Email us at indexes@samspublishing.com
1976      SQL Server Web services


SQL Server Web services                                    bulk-copy operations, 1601-1604
    creating, 1442-1445                                    configurations, 1548
        AS HTTP 1445-1448
                 ,                                         containers, 1546
        FOR SOAP 1449-1453
                   ,                                       control flow, 1546
    history and overview, 1439-1440                        data flow, 1547
    limitations, 1469                                      data flow task, 1547
    new features, 1439                                     data mining, 1534-1535
    patterns, 1440-1441                                    data transformation requirement, 1555-1556
SQL statements                                             event handlers, 1547
    replication monitoring, 472-475                        logging, 1549
    transactions, 874                                      new features, 1540
SQLAgentOperatorRole, 370                                  Package Execution utility, 1574-1576
SQLAgentReaderRole, 370                                        dtexec utility, 1574, 1576-1577
SQLAgentUserRole, 370                                          dtsrun utility, 1574
SqlCacheDependency object, 1329                                dtutil utility, 1582-1586
SQLCLR, .NET Framework data provider for SQL                   running packages, 1577-1582
  Server, 241-242                                          packages, 1546, 1549
SQLCMD, 39-40, 90-92                                       SSIS Wizard, running, 1556-1566
sqlcmd                                                     tasks, 1546
    executing, 92-94                                       tools, 1549-1551
    scripting variables with, 94-95                            command-prompt utilities, 1552
SQLCMD scripts, editing in SSMS, 74-75                         Expression Builder, 1553-1554
SqlConnection.BeginTransaction method, 1155                    Import and Export Wizard, 1551
SqlDependency object, 1328-1330                                integration services, 1552
sqldiag, 105-107                                               Query Builder, 1552-1554
SQLDIAG.EXE, disaster recovery, CD:1788-1790                   SSIS Designer, 1551-1552, 1566-1574
SQLiMail. See Database Mail                                transformations, 1547
SQLMAINT, 946                                              variables, 1549
SqlMethod, parameters, 1359                                workflows, 1546
SqlNotificationRequest object, 1329                        XML configuration file, 1548
SQLOLEDB, 237                                           SSIS Designer, 1551-1552, 1566-1574
SQLRowCount, 763                                        SSIS Wizard, running, 1556-1566
SqlServerSystem ICF elements, CD:1863-1865              SSL (Secure Sockets Layer), 388, 1445
sqlservr, 107-108                                       SSL PORT, 1446
SQLSetConnectAttr() function, 1155                      SSMS (SQL Server Management Studio), 14-16, 36-
SqlTrigger attribute, parameters, 1366                   37, 57, 205, 340, 631, 1151
SqlTriggerContext attribute, parameters, 1367              administration tools, 64
SqlUserDefinedAggregate attribute, parameters, 1364            Activity Monitor, 68-70
SqlUserDefinedType, parameters, 1354-1355                      Log File Viewer, 70-71
sqlwb, 91                                                      Object Explorer, 66-67
SQL_LOGIN, 252                                                 registered servers, 65-66
SSAS (SQL Server 2005 Analysis Services), 22-23, 73,       backups, creating, 302-305
  209, 1473                                                configuration options, setting, CD:1702-1703
    cube perspectives, 1524-1525                           constraints, creating, 653-656
    cubes, 1477-1480                                       creating instance and application, for SSNS,
    OLAP 1481-1482. See also OLAP
         ,                                                   CD:1866-1868
    OLAP and, 1474-1476                                    creating user-defined functions, 812-814
    wizards, 1476-1477                                     Database Diagram Editor, creating tables,
    new features, 1473-1474                                  580-581
SSCM                                                       database options, 561
    configuring clients, 232-234                           databases, creating, 556-559
    testing connectivity, 246                              development tools, 71
SSIS (SQL Server Integration Services), 42, 946, 1539          integrating with source control, 81-83
    architecture, 1545-1549                                    managing projects, 79-81
    bcp utility, 1586-1588                                     Query Editor. See Query Editor
        exporting/importing data, 1589-1591                    templates, 83-86
        file data types, 1591                              indexes
        format files, 1591-1601                                creating, 631-632
        views, 1601                                            managing, 636
                                                               managing with, 1012-1013
                                                               stopping server-side traces         1977



   integrated environments, 58                             reasons for using, CD:1843
       integrated help, 62-64                              requirements for, CD:1842-1843
       window management, 59-62                            subscribers, CD:1844-1845
   lock activity, monitoring, 1164-1167                    subscriptions, CD:1844-1845
   logins, 271                                                  creating, CD:1869-1871
   managing database roles, 276                            upgrading, 219
   managing logins, 268-271                                XML and Management Studio, CD:1848-1849
   managing permissions, 277-278                       Standard, 114
       at database level, 281-283                      Standard Generalized Markup Language
       at object level, 283-284                          (SGML), 1377
       at server level, 278-280                        standards, HA, 398
   managing users, 273-275                             STANDBY, 321
   new features, 57-58                                     transaction logs, 310
   Object Explorer                                     standby servers, backups, 336-337
       creating tables, 580                            START, ALTER FULLTEXT INDEX, CD:1926
       data types, 587                                 startup accounts, configuring (SQL Server Agent),
       dropping tables, 605-606                          363-365
       modifying tables with Table Designer, 601-604   startup procedures, 778-781
   Query Analyzer, 1117-1118                           statements
       execution plan ToolTips, 1118-1121                  BEGIN CONVERSATION DIALOG, CD:1890
   restoration, 322-323                                    BEGIN TRAN statement, transactions, 909
   shrinking databases, 571-573                            BEGIN TRAN statements, 884
   startup accounts, SQL Server Agent, 365                 CREATE CERTIFICATE, CD:1901
   stored procedures                                       CREATE ENDPOINT, T-SQL, 1442
       creating, 702-705                                   END CONVERSATION, CD:1896
       creating (custom stored procedure templates),       HOLDLOCK, 911
         705-709                                           PRINT, CD:1900
       executing, 711-713                                  RECEIVE, CD:1900
       modifying, 723-724                                  ROLLBACK, CD:1901
   Summary page, 61                                        SEND, CD:1895
   Template Explorer, 704                                  SET TRANSACTION ISOLATION LEVEL READ
   transaction logs, creating, 308                           COMMITTED, 1155
   user-defined functions, 822                             SET TRANSACTION ISOLATION LEVEL statement,
   views, managing, 683                                      options, 911-912
SSMS client statistics, 1132-1133                          SQL statements, transactions, 874
SSMS Shrink File dialog, 896                               WAITFOR, CD:1900
SSNS (SQL Server Notification Services), 23-24, 184,   statistics
 213, CD:1841                                              columns, generating, 990-996, 998
   ADFs, CD:1850                                           indexes, 982-984
       Database ADF, CD:1851                                    generating, 990-998
       Distributors ADF, CD:1862-1863                           histograms, 984-987
       EventClasses ADF, CD:1851-1852                      maintaining, with Maintenance Plan Wizard,
       Generator ADF, CD:1862                                953-956
       NotificationClasses ADF, CD:1854-1859               problems with query optimization, 1090-1091
       ParameterDefaults ADF, CD:1851                      query statistics, 1139
       Providers ADF, CD:1859-1862                              datediff(), 1145
       sample applications, CD:1850-1851                        STATISTIC IO, 1141-1142
       SubscriptionClasses ADF, CD:1852-1854                    STATISTICS IO, 1139-1141
   choosing programming methods, CD:1847-1848                   STATISTICS PROFILE, 1146
   creating instance and application via SSMS,                  STATISTICS TIME, 1142-1145
     CD:1866-1868                                               STATISTICS XML, 1146-1147
   editions of, CD:1842-1843                               updating, VLDB data maintenance, CD:1751-1752
   event providers, CD:1844                            STATISTICS IO, 1139-1142
   event rules, CD:1845                                STATISTICS PROFILE, 1112, 1146
   events, CD:1844                                     STATISTICS TIME, 1112, 1142-1145
   ICFs, CD:1863-1865                                  STATISTICS XML, 1146-1147
   instances, CD:1846-1847                             statistics, indexes, 988-989
   migrating, 213                                      SteelEye Technologies, CD:1791
   new features, CD:1841-1842                          steps (values), histograms, 984
   notification cycles, CD:1845-1846                   STOP, ALTER FULLTEXT INDEX, CD:1926
   providing events to applications, CD:1871-1874      stopping server-side traces, 143-144



             How can we make this index more useful? Email us at indexes@samspublishing.com
1978      storage


storage, Database Engine, 12                                    errors, 786-789
storage area network (SAN), 520, 549                            source code control, 789-790
storage data types, format files, 1593-1594                 temporary stored procedures, 709-710
storage of archived data, VLDB data maintenance,            temporary tables, 749-750
  CD:1759-1760                                                  performance tips, 751-752
stored procedures                                               table data type, 752-755
    advantages of, 700-701                                  transactions, 899-904
    analyzing with Query Analyzer, 1129-1130                using BEGIN CONVERSATION DIALOG, CD:1892
    calling from transactions, 783-786                      viewing, 719-722
    CLR stored procedures, 791-793                      Stream Aggregate, Query Analyzer, 1128
    creating, 701-702                                   streams, 1089
        in SSMS, 702-705                                string summary statistics, indexes, 998
        in SSMS (custom stored procedure                structures, indexes, 970-975
          templates), 705-709                           stub, 1441
    cursors, 733-738                                    subquery processing, joins, 1067
        CURSOR variables, 738-743                           correlated subqueries, 1068-1070
    Debugging, with Visual Studio .NET, 756-760             materialized subqueries, 1067-1068
    deferred name resolution, 715-717                   subscribers, SSNS, 1844-1845
        identifying objects referenced in stored        subscription server, data replication, 420
          procedures, 717-719                           SubscriptionClasses ADF element, CD:1852-1854
    defined, 699                                        subscriptions, 24, 433-434
    dynamic SQL, 772-774                                    anonymous subscriptions, 434
        sp executesql, 774-776                              creating
        sp executesql, output parameters, 776-777               for data replication, 465-469
    errors, 786-789                                             for SSNS, CD:1869-1871
    executing, 710-711                                      distribution database, 435-436
        execution context and EXECUTE AS, 713-715           reports, 1648-1649
        in SSMS, 711-713                                        data-driven subscriptions, 1649-1650
        via linked servers, CD:1689                             delivery architecture, 1650
    extended stored procedures, 793-794                     SSNS, CD:1844-1845
        adding to SQL Server, 794-795                   summary data, denormalization, 1221
        obtaining information on, 795                   Summary page, reports, 61
        provided with SQL Server, 795-796               Surface Area Configuration (SAC) tool, 223, 340, 1618
        xp cmdshell, 796-798                            suspect pages, 326
    input parameters, 724-725                           SVG (Scalar Vector Graphics), 351
        passing object names as, 728-729                svmail configure sp, 346
        setting default values for, 725-728             SWITCH, 621
        wildcards, 729-730                              switching table partitions, 618-621
    managed database objects, developing,               Symantec, CD:1791
      1335-1336, 1339-1344                              synchronization, 437-438
    for managing logins, 272                            synchronous operations, database mirroring operating
    modifying, 722                                        modes, 485
        with SSMS, 723-724                              sys users, 256
    monitoring recompilation, 766-768                   sys.conversation groups, CD:1910
        forcing recompilation of query plans, 768-771   sys.databases catalog view, selecting columns, 566
    nested stored procedures, 743-745                   sys.dm broker activated tasks, CD:1911
        recursion, 745-748                              sys.dm broker connections, CD:1911
    new features, 699-700                               sys.dm broker forwarded messages, CD:1911
    output parameters, 731-732                          sys.dm broker queue monitors, CD:1911
    performance, 762-763                                sys.dm broker transmission status, CD:1911
        query plan caching, 763                         sys.dm exec cached plans, 1077-1079
        qutomatic query plan recompilation, 765-766     sys.dm exec plan attributes, 1077, 1081-1082
        shared query plans, 764-765                     sys.dm exec query plan, 1137-1139
        SQL Server procedure cache, 763-764             sys.dm exec query stats, 1077-1081
    remote stored procedures, 755-756                   sys.dm exec sql text, 1077-1079
    returning procedure status, 732-733                 sys.dm_db_index_physical_stats, 1003
    rewriting as functions, 826-827                     sys.dm_tran_locks view, querting, 1161-1164
    system stored procedures, 170-171, 760-762          sys.endpoint webmethods, 1467
    T-SQL                                               sys.endpoints, 1466
        calling from transactions, 783-786              sys.indexes, 1097
        coding guidelines, 781-783                      sys.master files, 550
                                                                                          T-SQL       1979



sys.plan guides, 1106-1107                             System.Data.SqlTypes namespace, ADO.NET,
sys.service contract message usages, CD:1910             1326-1328
sys.service contracts, CD:1910                         System.Xml namespace (.NET Framework), 1320
sys.service message types, CD:1910                     SystemDataAccess parameter (scalar UDFs), 1345
sys.service queues, CD:1909                            SystemDataAccess parameter (SqlMethod), 1359
sys.services, CD:1910                                  systemwide mail settings, Database Mail, 345-346
sysadmin, 259                                          systemwide table-valued functions, 825-826
sysjobstepslogs, 376
sysmail account, 358                                   T
sysmail configuration, 358
                                                       T-SQL, CD:1793
sysmail delete account sp, 345
                                                           @@ERROR, CD:1836-1837
sysmail delete log sp, 360
                                                           @@ROWCOUNT, CD:1836-1837
sysmail delete principalprofile sp, 345
                                                           altering views, 681-682
sysmail delete profile sp, 345
                                                           backups, creating, 305-307
sysmail delete profileaccount sp, 345
                                                           coding recommendations, CD:1794
sysmail faileditems, 359
                                                                avoiding SQL injection attacks when using
sysmail help queue sp, 360
                                                                  dynamic SQL, CD:1799-1806
sysmail help status sp, 360
                                                                commenting, CD:1806-1807
sysmail principalprofile, 357
                                                                explicit column lists, CD:1794-1796
sysmail profile, 357
                                                                qualifying object names with schema
sysmail profileaccount, 358
                                                                  names,CD:1796-1799
sysmail server, 358
                                                           constraints, creating, 651-653
sysmail servertype, 358
                                                           CONTEXT INFO, CD:1824-1825
sysmail start sp, 348
                                                           CREATE ENDPOINT, 1442
sysmail unsentitems, 360
                                                           CREATE TABLE, 582-584
sysmail update account sp, 345
                                                           creating tables, 582-584
sysmail update principalprofile sp, 345
                                                           creating user-defined functions, 807-811
sysmail update profile sp, 345
                                                           creating views, 675-678
sysmail update profileaccount sp, 345
                                                           database options, setting, 563
sysmessages, 382-383
                                                           databases, creating, 559-560
sysopentapes, 326
                                                           date calculations, CD:1817-1820
system administrators, responsibilities of, 156-157
                                                           de-duping data with ranking functions,
system architecture, Reporting Services, 1611-1613
                                                             CD:1837-1840
system catalog views, 38-39
                                                           deleting mail objects, 345
system catalogs, SQL Server Service Broker,
                                                           DML. See DML
  CD:1909-1911
                                                           dropping views, 682
System Configuration Checker (SCC), 174
                                                           enhancements to, 47
system database backups, scenarios, 317
                                                           execution engine, 209
system databases, 157-159, 547
                                                           full-text indexes, CD:1916
    restoring, 333-335
                                                                ALTER FULLTEXT INDEX, CD:1924-1926
system permissions, securing reports, 1648
                                                                CREATE FULLTEXT CATALOG, CD:1916-1918
system roles, securing reports, 1648
                                                                CREATE FULLTEXT INDEX, CD:1919-1924
system stored procedures, 170-171, 760-762
                                                                diagnostics, CD:1927-1930
    catalog views and, 1466-1467
                                                                managing MSFTESQL, CD:1927
system tables, 160-161
                                                           functions, versus CLR functions, 830-831
    backing up and restoring, 325-326
                                                           generating statements, CD:1835-1836
system views, 161
                                                           GROUPING function, CD:1822-1824
    access to perfmon counters, 1261
                                                           indexes
    catalog views, 164-166
                                                                creating, 627-631
    compatibility views, 161-162
                                                                managing, 633-636
    DMVs, 168-170
                                                           INITIATOR, CD:1886
    information schema views, 166-167
                                                           managing
    monitoring disk system items, 1256-1257
                                                                database roles, 277
    monitoring memory items, 1252-1254
                                                                logins, 272-273
    monitoring network items, 1243
                                                                permissions, 285-286
    monitoring processor items, 1248
                                                                users, 275
System.Data namespace (.NET Framework), 1319
                                                           modifying tables, 598
System.Data.SqlClient namespace (.NET
                                                                adding and dropping columns, 600-601
  Framework), 1319
                                                                changing column properties, 599




             How can we make this index more useful? Email us at indexes@samspublishing.com
1980      T-SQL


    outer joins, CD:1826-1827                            table-valued functions, 805-807
        full outer joins, CD:1833-1835                   tablediff, 90, 98-101
        nested outer joins, CD:1832-1833                 tables, 579
        versus WHERE clause, CD:1827-1832                    columns. See columns
    performance                                              creating, 580
        avoiding unnecessary function executions,                with Database Diagram Editor (SSMS),
          CD:1809                                                   580-581
        cursors, CD:1810-1813                                    with Object Explorer (SSMS), 580
        DISTINCT, CD:1808                                        with T-SQL, 582-584
        IF EXISTS, CD:1807                                   dropping, 605-606
        ORDER BY, CD:1808                                    fragmentation, 999
        temp tables versus table variables versus            importing trace files into, 126-128
          common table expressions, CD:1808-1809             in reports, with BIDS Report Designer, 1623-1624
        UNION versus UNION ALL, CD:1807                      indexes. See indexes
        UPDATE, CD:1813-1816                                 inserting trace data into trace tables, 127-128
    PIVOT, 1305-1309                                         modifying, 598
    restoration, 318-321                                         with database diagrams, 604-605
        transaction logs, 321-322                                with Object Explorer and Table Designer,
    running ad hoc T-SQL batches from SQL Server                    601-604
      web services, 1458-1463                                    with T-SQL, 598-601
    stored procedures                                        partitioned tables. See partitioned tables
        calling from transaction, 783-786                    saving trace output to, 124
        coding guidelines, 781-783                           system tables, 160-161
        errors, 786-789                                          backing up and restoring, 325-326
        source code control, 789-790                         table location defining, 594-596
    TARGET, 1886                                             temporary tables
    transaction logs, creating, 309-310                          creating, 622
    UNPIVOT, 1305-1309                                           stored procedures, 749-750
    updating Database Mail, 345                              temporary tables. See temporary tables
    user-defined functions                               TABLESAMPLE, 1314-1318
        modifying, 821-822                               TABLOCK hint, supplying bulk-copy
        viewing, 818-821                                   operations to, 1604
T-SQL stored procedures, versus CLR stored               TABLOCK optimizer hint, 1206
  procedures, 793                                        TABLOCKX optimizer hint, 1206
table constraints, defining, 596-597                     Tabular Data Stream (TDS), 1440
table data types, temporary tables (stored procedure),   Tabular Data Stream endpoints, 224
  752-755                                                tape devices, 300
Table Delete, Query Analyzer, 1128                       TARGET, CD:1886
Table Designer, modifying tables, 601-604                Target parameter (SqlTrigger), 1366
table expressions, T-SQL, CD:1808-1809                   Target Server Wizard, 389
table hints                                              target servers, 388-390
    locking, 1203-1206                                   TARGET SIZE, 570
    Query Optimizer, 1096-1097                           tasks, SSIS, 1546
Table Insert, Query Analyzer, 1128                       TDS (Tabular Data Stream), 1440
table location, 594-596                                  TDS (Tabular Data Stream) endpoints, 224
Table locking level (SQL Server), 1177                   temp tables, T-SQL, CD:1808-1809
table partitioning, 42-43                                tempdb
    for VLDB, CD:1768-1770                                   SQL Server performance, 1269
table partitions                                             temporary table and stored procedures,
    adding, 614-616                                            performance tips, 751-752
    dropping, 616-618                                    tempdb database, 159
    switching, 618-621                                   Template Explorer, 84, 836
Table Scan, Query Analyzer, 1128                             SSMS, 704
table scans                                              templates
    costs, 1046-1048                                         custom function templates, creating for user-
    Query Optimizer, 970                                       defined functions, 815-818
Table Spool, Query Analyzer, 1126                            custom stored procedure templates, creating in
Table Update, Query Analyzer, 1128                             SSMS, 705-709
table variables, T-SQL, CD:1808-1809                         intergrating SSMS, 83-86
Table-valued Function, Query Analyzer, 1128              temporary stored procedures, 709-710
                                                                                transactions        1981



temporary tables                                           replaying trace data, 129-131
    creating, 622                                          saving, 123-125
    stored procedures, 749-750                             server-side traces
         performance tips, 751-752                              defining, 131-140
         table data types, 752-755                              stopping, 143-144
Terminate() method, 1363                               training, HA, 398
terminators, fields (format files), 1596-1599          @@trancount, 783
testing                                                Transact-SQL. See T-SQL
    alerts, SQL Server Agent Mail, 357                 transaction isolation, level hints, 1204-1205
    connectivity, 246                                  transaction isolation levels, 1153-1154
    Database Mail setup, 346                               dirty reads, 1153
    failover, of database mirroring, 507-508               lost updates, 1153
    job-completion notification, SQL Server Agent          nonrepeatable reads, 1153
       Mail, 355                                           phantom reads, 1153
TextData data column (SQL Profiler), 1169                  read committed isolation, 1155
third-party disaster recovery alternatives, 1791           read committed snapshot isolation, 1155-1156
three-permission sets, managed database objects,           read uncommitted isolation, 1154
  1332-1334                                                repeatable read isolation, 1156-1157
time series, data mining algorithms, 1528                  serializable read isolation, 1157-1158
time slices, 519                                           snapshot isolation, 1158-1159
timeout intervals setting locks, 1191-1192             transaction log backups, 296
timestamp data type, optimistic data type, 1207-1209   transaction log files, 548, 554-555
tokens, 475                                            transaction logs, 307
tools                                                      backing up tail of, 330
    administration tools, 64-71                            creating, 308-310
    client tools, installing, 229                          full database backups, 311-312
    development tools. See development tools               restoration, T-SQL, 321-322
    Manage Indexes tool, 631                               restoring, 328
    performance and monitoring tools, 1652-1653            restoring backups, 330-331
    Reporting Services Configuration tool, 1615        transaction management, 873-875
    SAC tool, 223                                          AutoCommit, 876
    SQL Server Agent, 16-17                                batches, 897-899
    SQL Server client tools, side-by-side                  bound connections, 915-917
       migration, 205                                      checkpoint duration option, 873
    SQL Server Configuration Manager, 16                   coding, 912-913
    SQL Server Profiler, 17                                distributed transactions, 918
    SQL Server Surface Area Configuration tool, 16         explicit user-defined transactions, 876-878
    SSIS. See SSIS, tools                                       nested transactions, 879-882
    Surface Area Configuration tool, 340, 1618                  savepoints, 878-879
ToolTips, execution plan ToolTips (Query Analyzer),        implicit transactions, 882-884
  1118-1121                                                locks, 911-912
TOP, DML, 1276-1280                                        long-running transactions, 913-915
Top, Query Analyzer, 1129                                  recovery process, 885-886, 889-891
ToString() method, 1355                                    stored procedures, 899-904
trace flags, 1197-1199                                     transaction logging, 885-897
Trace Name, 115                                            transactions, processing, 875
tracer tokens, 416                                         triggers, 904-905
traces, analyzing output with Database Engine Tuning            multistatement transactions, 907-909
  Advisor, 128-129                                              savepoints, 909-910
traces (SQL Server Profiler)                                    transaction nesting, 905-907
    creating, 113-116                                  transaction processing phase, 1072
    data columns, 118-120                              transactional integrity, VLDB data maintenance,
    events, 116-118                                      CD:1759
         categories and, 132-136                       transactional replication, 18-19, 444-446
         columns, 137-138                              transactions, 874
    executing, 123                                         ACID properties, 874
    exporting, 123                                         batches, 897-899
    filters, 121-122                                       BEGIN TRAN statement, 909
    importing, 125-126                                     bound connections, 915-917
    inserting trace data into trace tables, 127-128        calling stored procedures from, 783-786
    monitoring running traces, 141-142                     coding, 912-913



             How can we make this index more useful? Email us at indexes@samspublishing.com
1982      transactions


     distributed transactions                          TVFs (table-valued UDFs), 1348-1349, 1352-1353
         linked servers, 1672-1673                     TYPE COLUMN, CREATE FULLTEXT INDEX,
         managing, 918                                   CD:1919-1920
     explicit transactions, 875, 884                   type hints, locks, 1206
     implicit transactions, 875, 884                   types, UDTs, 1354-1355, 1359-1363
     locks, 911-912
     long-running transactions, managing, 913-915      U
     managed database objects, developing,
                                                       UA (SQL Server Upgrade Advisor), 197-198
        1372-1374
                                                           Analysis Wizard, 199-202
     processing, 875
                                                           Report Viewer, 202-203
         AutoCommit, 876
                                                       UDAs (user-defined aggregates), developing managed
         explicit user-defined transactions, 876-882
                                                        database objects, 1363-1366
         implicit transactions, 882-884
                                                       UDFs. See user-defined functions
     stored procedures, 899-904
                                                       UDTs (user-defined types), 221, 589-590
     transaction logging, 885-897
                                                           managed database objects, developing,
     triggers, 904-905
                                                             1354-1355, 1359-1363
         multistatement transactions, 907-909
                                                       unattended installation
         savepoints, 909-910
                                                           installing SQL Server 2005, 191-193
         transaction nesting, 905-907
                                                           of SP1, 195
transformations, SSIS, 1547
                                                       unattended upgrades, 219-220
transmission queues, 1890
                                                       UNC (Universal Naming Convention), 301
trigger firing order, AFTER triggers, 840
                                                       unconditional workflows, SSIS, 1547
TriggerAction parameter (SqlTriggerContext), 1367
                                                       undo (rollback) phase, transaction recovery
triggers, 833
                                                        process, 891
     AFTER triggers, 837-839
                                                       uniform extent, 549
         executing, 839
                                                       UNION
         special considerations, 840-841
                                                           query processing, 1084, 1086
         trigger firing order, 840
                                                           versus UNION ALL, 1807
     CLR triggers, 834, 866-869
                                                       UNION ALL
         creating, 866-868
                                                           query processing, 1086
     DDL triggers. See DDL triggers
                                                           versus UNION, 1807
     DML triggers. See DML triggers
                                                       UNION hints, 1098-1099
     INSTEAD OF triggers, 851-853
                                                       UNIQUE constraint, 596, 645-646
         restrictions, 859
                                                       UNIQUE keyword, 596
         views, 856-859
                                                       Universal Naming Convention (UNC), 301
     managed triggers, 1366-1368, 1370-1372
                                                       UNPIVOT, 1305-1309
     nested triggers, 869, CD:1721
                                                       UNSAFE, 792, 829
     new features, 834
                                                       UPDATE, T-SQL (performance), CD:1813-1816
     problems with query optimization, 1094
                                                       update languages, CD:1927
     recursive triggers, 870-871
                                                       update locks, 1173-1174
     transactions, 904-905
                                                       update performance indexes, compared to query
         multistatement transactions, 907-909
                                                        indexes, 1024-1026
         savepoints, 909-910
                                                       UPDATE STATISTICS command, 990-994
         transaction nesting, 905-907
                                                       UPDATE(), 843-845
trivial plan optimization, 1071
                                                       updates, checking for column updates, 843-845
troubleshooting
                                                       updating
     connectivity issues, 244-245
                                                           column statistics, 990-996, 998
     replication failures, replication monitoring,
                                                           Database Mail, with T-SQL, 345
        476-477
                                                           index statistics, 990-996, 998
TRUNCATE, 288
                                                           statistics, VLDB data maintenance,
TRUNCATE ONLY, transaction logs, 310
                                                             CD:1751-1752
TRUNCATE TABLE, 840
                                                           subscribers replication model, data replication,
TRY…CATCH, 786-788, 1312-1314
                                                             430-432
tsequal() function, 1152
                                                       UPDLOCK optimizer hint, 1206
TSQL, 114
                                                       UPGRADE, 219
TSQL Default TCP, 226
                                                       upgrading
TSQL Duration, 115
                                                           Analysis Services, 217
TSQL Grouped, 115
                                                           clusters, 219
TSQL Replay, 115
                                                           databases, 216-217
TSQL SPs, 115
                                                           DTS, 218
Tuning, 115
                                                                                       viewing      1983



    Notification Services (SSNS), 219                  utilities, SSIS, 1549-1551
    replicated databases, 217                               bcp utility, 1586-1601
    Reporting Services, 218-219                             command-prompt utilities, 1552
    SQL Server, 197                                         Expression Builder, 1553-1554
    unattended upgrades, 219-220                            Import and Export Wizard, 1551
upgrading in-place (installing SQL Server 2005), 214        integration services, 1552
    .NET Framework 2.0, 216                                 Package Execution utility, 1574-1586
    MSXML 6.0, 216                                          Query Builder, 1552-1554
    SQL Server 2005 upgrade matrix, 214-215                 SSIS Designer, 1551-1552, 1566-1574
    upgrading
        Analysis Services, 217                         V
        databases, 216-217
                                                       valid documents (XML), 1378
        DTS, 218
                                                       validation and execution phase (dtexec), 1577
        Notification Services (SSNS), 219
                                                       ValidationMethodName parameter
        Reporting Services, 218-219
                                                         (SqlUserDefinedType), 1355
Usage-Based Optimization Wizard, 1523
                                                       VALUE secondary index (XML), 1434
USE PLAN, Query Optimizer, 1101-1103
                                                       value() method, 1435
USE PLAN N hints, 1101
                                                       value() new xml data type method, 1412, 1421-1422
user CALs, 30
                                                       values, monitoring with Performance Monitor,
user configurable events, SQL Server Profiler, 151
                                                         1237-1238
user connections, 1727
                                                       varbinary(max), 45
user databases, 547
                                                       varchar(max), 45
user options, CD:1728-1729
                                                       variables
user requirements, data replication, 447-448
                                                           CURSOR, stored procedures, 738-743
    data characteristics, 448-450
                                                           scripting, with sqlcmd, 94-95
user-defined counters, SQL Server performance
                                                           SSIS, 1549
  counters, 1260-1261
                                                       VDI (Virtual Device Interface), 1748
user-defined data types, 588-589
                                                       vendor agreements, HA, 398
user-defined functions, 799, 802
                                                       verification, packages (dtutil utility), 1582-1585
    creating, 807
                                                       verify signature, 1927
        custom function templates, 815-818
                                                       VeriSign, 235
        with SSMS, 812-814
                                                       vertical data partitioning, 1763-1765
        T-SQL functions, 807-811
                                                           denormalization, 1223-1224
    managed database objects, developing, 1344-
                                                       vertical filtering, data replication, 463-465
      1349, 1352-1353
                                                       very large database. See VLDB
    managing permissions, 824-825
                                                       VHD (Virtual Hard Disk), 412
    modifying, 821-822
                                                       VIEW DEFINITION, 267
    new features, 799-800
                                                       View Designer, creating views, 679-681
    reasons for using, 800-802
                                                       VIEW METADATA:creating views with T-SQL, 678
    scalar functions, 803-804
                                                       viewing
    scalar UDFs, 1344-1347
                                                           DDL triggers, with catalog views, 866
    table-valued functions, 805-807
                                                           error logs, SQL Server Agent, 368-369
    TVFs, 1348-1349, 1352-1353
                                                           graphical execution plans, 1130-1131
    viewing
                                                           installation log files, 190
        with SSMS, 822
                                                           job history, 380-381
        with T-SQL, 818-821
                                                           last generated report, Report Viewer, 202
user-defined roles, 262-264
                                                           linked servers, CD:1680-1681
user-defined types. See UDTs
                                                           lock activity
user/schema separation, 247
                                                                Performance Monitor, 1169-1171
    principals, 257-258
                                                                SQL Server Profiler, 1167-1170
users
                                                                SSMS, 1164-1167
    delivering data to, in OLAP database creation,
                                                           mail configuration objects, Database Mail,
      1518-1519
                                                              357-358
    logins, 254
                                                           mail message data, Database Mail, 359-360
    managing, 273-275
                                                           partitioned table information, 613
    principals, 254-256
                                                           stored procedures, 719-722
    SELECT statement, 257
                                                           user-defined functions, 818-822
    SQL Server performance counters, 1259
USESYSDB, 219




             How can we make this index more useful? Email us at indexes@samspublishing.com
1984      views


views                                            W
    altering, with T-SQL, 681-682
    bcp utility, 1601                            W3C (World Wide Web), 1440
    catalog views, 156                           WAITFOR, CD:1900
    compatibility views, 155, 161-162            web methods, examples
    controlling access to data, 671-674              calling web methods-bound stored procedure that
    creating, 674-675                                  returns XML, 1462-1465
    creating                                         running ad hoc T-SQL batches from SQL Server
        with T-SQL, 675-678                            web services, 1458-1463
        with View Designer, 679-681                  running web methods bound to stored procedures
    data abstraction, 670-671                          from C#, 1453-1456, 1458
    data modifications and, 683-684              web services
    defined, 667-668                                 enhancements, 52
    distributed partitioned view, 858                Reporting Services, 1609
    DMVs, 156                                            configuring, 1617
    dropping, with T-SQL, 682                    Web Services Description Language, 1440, 1451
    focusing on specific data, 670               web services. See SQL Server Web services
    indexed views, 690, 1021-1022                Web Sites Properties dialog, 1448
        creating, 690-693                        WEBMETHOD, 1449
        expansion, 696-697                       weighted, FTS, CD:1936
        performance and, 693-696                 well formed documents (XML), 1378
    indexes on, 639-640                          WHERE
    INSTEAD OF triggers, 856-859                     search argument problems, 1093
    managing, 681                                    versus OUTER JOIN, CD:1827-1832
        with SSMS, 683                           where clause, query() new xml data type
    new features, 667                             method, 1417
    partitioned views, 684-687                   wide indexes, compared to multiple indexes,
        distributed partitioned views, 688-690    1020-1021
        modifying data through, 688              wildcards
    Performance Monitor, 1236-1237                   parameters, stored procedures, 729-730
    simplifying data manipulation, 669-670           Query Editor, SSMS, 75-76
    system views. See system views               window management, SSMS, 59-62
Virtual Device Interface (VDI), 1748             windows, Datasets window, 1621
Virtual Hard Disk (VHD), 412                     Windows Authentication mode, 250, 268
Virtual Machine Monitor (VMM), 412               Windows Firewall, 245
virtual server licensing, 33                     WINDOWS GROUP, 252
Virtual Server name dialog, 529                  Windows Installer 3.1, 228
Visual Studio 2005                               WINDOWS LOGIN, 252
    managed database objects, developing,        Windows performance counters, 1239
      1334-1335                                      monitoring
    report viewer controls, 1611                         disk systems, 1254-1257
VLDB (very large database), CD:1743                      memory, 1250-1254
    consistency, checking, CD:1749-1751                  network interfaces, 1239-1243
    data maintenance, CD:1751                            processors, 1244-1249
        purging/archiving data, CD:1755-1761     Windows Performance Monitor, replication
        rebuilding indexes, CD:1752-1755          monitoring, 477
        updating statistics, CD:1751-1752        Windows servers, HA, 410-412
    design considerations, CD:1761-1762          Windows Service
        database partitioning. See database          Reporting Services, 1609
          partitioning for VLDB                          configuring, 1616
    determining if you have one, CD:1744-1745        requirements for installing SQL Server 2005, 177
    maintenance issues, CD:1745                  Windows Service Control Manager, 364
        backing up and restoring, CD:1745-1748   Windows Vista support, requirements for installing
        consistency, CD:1749-1751                 SQL Server 2005, 178
    new features, CD:1743-1744                   WITH ACCENT SENSITIVITY, CREATE FULLTEXT
VMM (Virtual Machine Monitor), 412                CATALOG, CD:1917
Volume Shadow Copy Service (VSS), CD:1748        WITH CHECK OPTION, creating views with T-SQL,
VSS (Volume Shadow Copy Service), CD:1748         678-679
                                                 WITH clause, options for planning conversations
                                                  between services, CD:1892
                                                 WITH CLEANUP clause, CD:1896
                                                                   zero-to-one relationships           1985



WITH RECOMPILE, 769                                        new xml data type, 1402-1403
witness database servers, database mirroring, 483             built-in methods, 1411-1430
witness role, database mirroring, 485                         columns, 1404-1406
wizards                                                       schema collections, 1407-1412
    Aggregation Design Wizard, 1513                        nodes, 1378
    Analysis Services Migration Wizard, 209                OPENXML, 1399-1402
    Analysis Wizard, 199-202                               sending as attachments, 351
    Configure Database Mirroring Security Wizard,          xml columns
      495-501                                                 full-text indexing, 1436
    Copy Database Wizard, 206-208                             indexing, 1430-1436
    Cube Wizard, 1505                                  XML configuration file, SSIS, 1548
    Data Mining Wizard, 1528, 1530                     XML Data Modification Language (XMLDML), 1425
    Data Source View Wizard, 1492                      xml data type, DML, 1274
    Database Mail Configuration Wizard, 339            xml data types, 45
        creating SMTP accounts, 343                    XML for analysis (XMLA), 73
    Dimension Wizard, 1495, 1499                       XML indexes, 624
    Full-Text Indexing Wizard, CD:1930-1932            XML Schema Definition (XSD), 1378
    Installation Wizard, 181                           XMLA (XML for analysis), 73
    Maintenance Plan Wizard, 946-947                   XMLDATA keyword, 1383
        backing up databases, 948-951                  XMLDML (XML Data Modification Language), 1425
        checking database integrity, 951               xp cmdshell, 796-798
        maintaining indexes and statistics, 953-956    XP-related configuration options, 1729
        scheduling maintenance plans, 956-959          XSD (XML Schema Definition), 1378
        shrinking databases, 952-953
    Master Server Wizard, 389                          zero-to-one relationships, denormalization, 1224-1225
    Package Migration Wizard, 212
    SSAS, 1476-1477
    SSIS Wizard, 1551
        running, 1556-1566
    Target Server Wizard, 389
    Usage-Based Optimization Wizard, 1523
workflows, SSIS, 1546
World Wide Web Consortium (W3C), 1440
write-ahead logs, 555
WRITETEXT, 840
WSDL (Web Services Description Language),
 1440, 1451

X-Y-Z
XLOCK optimizer hint, 1206
XML, 1377
   attribute-centric XML shape, 1379
   calling web methods-bound stored procedure that
     returns XML, 1462-1465
   choosing programming methods, SSNS, CD:1847
   CREATE FULLTEXT INDEX, CD:1920
   data types, 588
   documents, 1378
   element-centric XML shape, 1379
   FOR XML modes, 1378
       AUTO mode, 1385-1389
       EXPLICIT mode, 1389-1391, 1393
       newxml data type, 1396-1399
       PATH mode, 1393-1396
       RAW mode, 1379-1383
   Infoset, 1432-1433
   Management Studio, SSNS, CD:1848-1849
   new features, 1377




             How can we make this index more useful? Email us at indexes@samspublishing.com

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:482
posted:8/4/2011
language:English
pages:45