B, CINDEXAALTER DATABASE COMMAND, 212, 446ACCENT SENSITIVITY, 247ALTER DDL STATEMENT, 615ADDRESS PARAMETER, 540ALTER INDEX STATEMENT WITH FILLFACTOR, 264ADDRESS WINDOWING EXTENSIONS (AWE), 104ALTER PARTITION FUNCTION AD HOC DISTRIBUTED QUERIESTSQL COMMAN...

20. B, C

Index

AALTER DATABASE command, 212, 446accent sensitivity, 247ALTER DDL Statement, 615ADDRESS parameter, 540ALTER INDEX statement with FILLFACTOR, 264Address Windowing Extensions (AWE), 104ALTER PARTITION FUNCTION ad hoc distributed queriesTSQL command, 585enabling, 339–340ALTER QUEUE command, 530OPENDATASOURCE functionALTER TABLE statement, 253basic syntax for, 348Analysis Services, 17, 32of various editions, 41–43querying data with, 349application log, 444OPENQUERY functioncomputer management MMC snap-in, basic syntax of, 353455–456querying data with, 354OPENROWSET functionsevere errors, 455AS HTTP settings, 487–489basic syntax of, 343asymmetric encryption, 176for bulk loading data, 347asymmetric keyscopying data with, 346for encrypting SSN, 179database.schema.object identifier, 343encryption algorithms, 180modifying data with, 346symmetric and, 176problem with, 353auditingquerying data with, 344–345automatic, 146table-valued function, 342XML file, 347–348configuring CDC for, 8authentication modeadministrator account, 61advantages and disadvantages of, 140Advanced tab, 93changing, 138, 141AdventureWorks2008 databaseselection of, 138bulk operations and recovery automatic failover, 204model, 335AWE (Address Windowing querying data from a table in, 338–339Extensions), 104schemas, 133AES algorithm, 538Baliascreating, 99–100backup compression default option, 108backupsmanaging, 99ALL_ERRORMSGS option, 269collation considerations, 253–254compressed. See compressed backupsALTER CONTRACT command, 528

659

660

Index

backups (Continued)native files, 321optional arguments, 317–318filegroups, 582replicate XML data, 480structure of, 315syntax arguments, 316–317replication performance, 421tab delimited files, 324SQL Server, 466BULK INSERT Transact-SQL strategies for, 246, 278–279statementsystem databases, 282–283basic syntax for, 330types available with SQL Server 2008, common options of, 331275–277importing data file contents into backup service master key, 174–175table, 331BCP (Bulk Copy Program)in query window in SQL Server csv files, 322–323Management Studio, 331–332exporting data from with data sources, 314AdventureWorks2008.Person.BULK INSERT statement, 329Person table, 319–321BULK INSERT Transact-SQL statementflat files and SQL Server, 315format filecreating, 324–325importing data file contents into export from AdventureWorks2008.dbo.Presidents table, 326–329minimally logged, 334field definitions for, 326Bulk-Logged recovery model, 275, 333–334bulk operations. See also bulk copying data, tools forUnicode files, 322performance optimization of, 336BEGIN DIALOG command, 540and recovery modelBest Practices Analyzer (BPA), 50AdventureWorks2008 database, 335binary collations, 249–250bulk load, 335binary file locations, 71minimal logging, 334–335_BIN2 binary collation, 249transaction log backup, 333–334BPA (Best Practices Analyzer), 50Business Intelligence Development BROKER_INSTANCE Studio (BIDS)parameter, 539creating SSIS packages inbulk copying data, tools forIntegration Services Project, 366–367BCPnew project, 368transformations, 371–372

Index

661

integrated development clustered SQL Serverchanging IP Address of, 229environment, 33EFS in, 181–183with package open, 368–370projects and solutions, 367–368FILESTREAM, 223troubleshooting, 229Package Designerusing MSDTC, 225–226control flow, 369–370Cluster Installation Rules, 222data flow, 370–371Cluster Resource Group Screen, 218Cluster Security Policy, 221Ccollationcase sensitivity, 247binary, 249–250C2 auditing, 107bit pattern, 247CDC (Change Data Capture)choice and query results, 252–253configuration, auditing table in for columns. See column-level database, 8collationsdata capture functionality of, 148considerations for backup and restore, cell-level encryption, 178253–254centralized performance data for database. See database-level repository, 246certificate, 176functionality of, 246Change Data Capture (CDC), 8options, customizing, 248–249CHECK constraints, 642rules, data comparison and sorting, clustered index, 263247–248Clustered installation, SQLselection during installation of considerations for, 66SQL Server, 246–247creating clustered resource group of SQL Server 2008, 250during. See clustered resource collection item, 291group, creatingcolumn-level collations, 251–252Data Directories tab for, 223column-level encryption, 178Installation Rules page, 223column setsSQL Server installer, 214and calculated column, static IP addresses for, 224comparison of, 260use of, 261clustered installer, launching, Combining System Monitor and 214–215Profiler Traces, 568–569clustered resource group, command-line interface, 67creating, 217command prompt utilities, 34disk selection, 218–219Common Criteria Compliance, 107domain groups, 220–222Compact Edition, SQL Server 2008, 35network configuration, 219–220