Математика | ||||
SQL Server 2000 Fast Answers for DBAs and Developers - Joseph Sack 2005 str940 | ||||
SQL Server 2000 Fast Answers for DBAs and Developers - Joseph Sack 2005 str940
Table of Contents Preface xxi Introduction xxiii Chapter 1: Installation, Upgrades, Service Packs, and Database Migration 1 Common Installation Problems 2 Hardware and Operating System Requirements for SQL Server 2000 3 Pre-Installation Best Practices 5 1.1 How to… Install SQL Server 2000 11 1.2 How to… Install Analysis Services 13 1.3 How to… Install English Query 14 1.4 How to… Perform an Unattended Installation 15 1.5 How to… Perform a Remote Installation 17 1.6 How to… Install Only the Client Utilities 17 1.7 How to… Install Only Client Connectivity 18 1.8 How to… Uninstall SQL Server 18 1.9 How to… Troubleshoot a Failed Installation 19 SQL Server Instances: Overview and Best Practice 20 1.10 How to… Install a Named Instance of SQL Server 21 1.11 How to… Register Your Named Instance in SQL Server Enterprise Manager 21 1.12 How to… Determine the Port Number for a Named Instance 22 Microsoft Data Access Components (MDAC) Defined 22 1.13 How to… Troubleshoot MDAC Installation 22 Ensuring Client Connectivity to Your Server 23 Table of Contents ii Upgrading to SQL Server 2000 from SQL Server 6.5 and 7.0 25 Database Compatibility Settings 25 Service Packs and Security Patches 26 1.14 How to… Find the Latest SQL Server Service Packs and Security Patches 27 1.15 How to… Install a Service Pack 27 1.16 How to… Uninstall a SQL Server Service Pack 29 1.17 How to… Install a Security Patch 32 1.18 How to… Roll Back a Security Patch 33 Post-Installation Best Practices 33 Database Migration Techniques 37 1.19 How to… Move Logins 37 1.20 How to… Fix Orphaned Logins 41 1.21 How to… Restore Another SQL Server's Database from Backup to SQL Server 2000 42 1.22 How to… Use BCP 50 1.23 How to… Use BULK INSERT 54 Database Migration – Bringing It All Together 55 1.24 How to… Migrate Databases from SQL Server 6.5 to SQL Server 2000 55 1.25 How to… Migrate Data from Microsoft Access 60 1.26 How to… Migrate Data from Microsoft Excel 64 Chapter 2: Configuring SQL Server 67 2.1 How to… Configure Server Options with Enterprise Manager 67 2.2 How to… Configure and View Server Options with sp_configure 68 2.3 How to… Use RECONFIGURE WITH OVERRIDE 70 2.4 How to... Configure Server Options 70 2.5 How to… View Server Property Information with SERVERPROPERTY 103 2.6 How to… Show Version Information for Your Server – xp_msver 105 2.7 How to… View Server Default Collation using SELECT SERVERPROPERTY 109 Table of Contents iii 2.8 How to… View Server Default Collation using sp_helpsort 109 2.9 How to… Create a Database with a Collation Different from the Server Default 110 2.10 How to… Change Database Collation 111 2.11 How to… Create a Table with a Column using a Non-Default Collation 111 2.12 How to… Change a Column's Collation 112 2.13 How to… Use the COLLATE Statement in a Transact-SQL Statement 113 2.14 How to… Use COLLATIONPROPERTY 113 2.15 How to… Change Default Server Collation with the Rebuild Master Utility 114 Chapter 3: Creating and Configuring Databases 119 SQL Server Storage Internals 119 Estimating Growth 123 The System Databases 126 Creating, Configuring, and Modifying Your Database 126 3.1 How To… Create a Database in SQL Server 2000 126 3.2 How to… Add a Data or Log File to an Existing Database 134 3.3 How to… Remove a Data or Log File from a Database 135 3.4 How to… Change a File's Logical Name 137 3.5 How to… Change a Database Owner 137 Database Options 138 3.6 How to… View Database Options with DATABASEPROPERTYEX 153 3.7 How to… View User Options for the Current Connection with DBCC USEROPTIONS 155 3.8 How to… Set User Options in Query Analyzer 156 3.9 How to… Rename a Database with sp_renamedb 160 3.10 How to… Show Database Information with sp_helpdb 160 3.11 How to… Show Log Size and % Space Used for Each Database 161 3.12 How to… Report and Correct Inaccuracies in the sysindexes Table with DBCC UPDATEUSAGE 162 Table of Contents iv 3.13 How to… View Space Usage with sp_spaceused 163 3.14 How to… Shrink Data and Log Files in a Database with DBCC SHRINKDATABASE 164 3.15 How to… Shrink Data Files in a Database using Enterprise Manager 165 3.16 How to… Shrink a Specific Data Or Log File using DBCC SHRINKFILE 167 Filegroups 170 3.17 How to… Add a User-Defined Filegroup 171 3.18 How to… Make a User-Defined Filegroup the Default Filegroup 172 3.19 How to… Make a Filegroup Read-Only 174 3.20 How to… Place a Table into a Filegroup 174 3.21 How to… Change a Filegroup for an Existing Table 177 3.22 How to… Change a Filegroup for an Existing Index 178 3.23 How to… Remove a Filegroup 179 3.24 How to… Create Database Objects using Enterprise Manager 180 Chapter 4: SQL Server Agent and SQL Logs 197 4.1 How to… Configure SQL Server Agent Properties 198 4.2 How to… Use xp_cmdshell 202 4.3 How to… Set up the Proxy Account 203 4.4 How to… Add Alerts 206 Checklist… Recommended Alerts – What to Monitor 208 4.5 How to… Add Operators 210 4.6 How to… Add and Configure Jobs 211 4.7 How to… Create Job Categories 215 Configuring SQL Mail and SQLAgentMail 216 4.8 How to… Troubleshoot SQL Mail and SQLAgentMail 217 4.9 How to… Configure SQL Mail for a Valid POP3 Server 218 4.10 How to… Use Alternative E-Mail Solutions 218 4.11 How to… Use xp_sendmail 219 4.12 How to… Process Incoming E-Mails with SQL Mail Procedures 221 Table of Contents v 4.13 How to… Configure Multi-Server Administration 222 4.14 How to… Configure the Master Server 223 4.15 How to… Set up Target Servers 223 4.16 How to… Create a Multi Server Job 223 4.17 How to… View Multi-Server Job History 225 4.18 How to… Defect a Target Server 225 4.19 How to… Script Jobs, Operators, and Alerts 226 4.20 How to… Transfer Jobs using DTS 227 4.21 How to… Delete or Re-Assign Jobs Belonging to a Specified Login using sp_manage_jobs_by_login 228 4.22 How to… Purge Job History Rows with sp_purge_jobhistory 228 4.23 How to… Start and Stop a Job with Transact-SQL Stored Procedures sp_start_job and sp_stop_job 229 4.24 How to… View and Interpret SQL Server Agent Error Logs 230 4.25 How to… Change the Name or Location of the SQL Server Agent Error Log 231 SQL Server Agent Stored Procedures 231 SQL Server Error Log 234 4.26 How to… Read the SQL Server Error Log 234 4.27 How to… Setting the Number of SQL Server Error Logs to Keep 235 4.28 How to… Start up a New SQL Server Error Log without Restarting the SQL Server Service 235 4.29 How to… Change the Logging Status of an Error Message with sp_altermessage 236 4.30 How to… Log a User-Defined Message with xp_logevent 238 Chapter 5: Transactions, Locking, Blocking, and Deadlocking 241 Understanding Transactions 241 5.1 How to… Use a Transaction Mark 248 Best Practices for using Transactions 249 5.2 How to… Display the Oldest Active Transaction with DBCC OPENTRAN 249 Table of Contents vi 5.3 How to… Use SET TRANSACTION ISOLATION LEVEL 251 5.4 How to… Use SET CURSOR_CLOSE_ON_COMMIT 252 5.5 How to… Use SET REMOTE_PROC_TRANSACTIONS 253 Lock Types, Compatibility, Isolation Levels, and Hints 254 5.6 How to… View Locking Activity in Enterprise Manager 262 5.7 How to… Use sp_who 263 5.8 How to… Use DBCC INPUTBUFFER and DBCC OUTPUTBUFFER 265 5.9 How to… Use fn_get_sql 266 5.10 How to… Use SET LOCK_TIMEOUT 266 Blocking and Deadlocking Defined 267 Why Blocking and Deadlocking Happen 268 5.11 How to… Identify and Resolve Blocking 268 5.12 How to… Identify Deadlocks with Trace Flag 1204 271 5.13 How to… Enable Trace Flags at SQL Server Startup 273 5.14 How to… SET DEADLOCK_PRIORITY 274 5.15 How to… Capture Deadlock Activity using SQL Profiler 275 Troubleshooting Blocking and Deadlocking Problems 281 Chapter 6: Backup, Recovery, and Maintenance 2 85 Creating a Backup and Recovery Plan 285 Checklist... When Problems Occur… 286 Checklist... If You Have to Rebuild Your Server... 286 Database Backup Methods 287 Understanding the Transaction Log 291 Checkpoints 292 6.1 How to… Perform Full, Differential, File, Filegroup, and Transaction Log B ackups with Enterprise Manager 293 6.2 How to… Create a Named Backup Device 296 6.3 How to… Perform a Full Database Backup with Transact-SQL 298 6.4 How to… Perform a Transaction Log Backup using Transact-SQL 301 Table of Contents vii 6.5 How to... Perform a Differential Backup using Transact-SQL 303 6.6 How to... Backup Individual Files or Filegroups using Transact-SQL 303 Checklist... Backup Reminders 304 6.7 How to… Restore a Database using Enterprise Manager 305 6.8 How to… Use RESTORE FILELISTONLY 309 6.9 How to… Use RESTORE HEADERONLY 310 6.10 How to… Use RESTORE LABELONLY 312 6.11 How to… Restore a Database from a Full Backup using Transact-SQL 313 6.12 How to… Restore a Database from a Differential Backup using Transact-SQL 316 6.13 How to… Restore a Database from a Transaction Log Backup using Transact-SQL 317 6.14 How to… Restore a File or Filegroup 320 6.15 How to… Do a PARTIAL Restore to a New Database 320 6.16 How to… Recover from File Loss or Database Corruption 321 Corrupted Binary Files 323 6.17 How to… Reset a Suspect Database with sp_resetstatus 324 6.18 How to… Rebuild the Registry 326 6.19 How to… Start SQL Server in Single-User Mode 326 6.20 How to… Set Up a Database Maintenance Plan 326 6.21 How to… Investigate Maintenance Plan Failures 336 6.22 How to… Use the xp_sqlmaint or SQLMaint Utility 337 6.23 How to… Check Consistency of the Disk Space Allocation Structures with DBCC CHECKALLOC 340 6.24 How to… Check System Table Consistency with DBCC CHECKCATALOG 344 6.25 How to… Check Table Integrity with DBCC CHECKCONSTRAINTS 344 6.26 How to… Check Allocation and Structural Integrity of All Database Objects with DBCC CHECKDB 347 6.27 How to… Check data, index, text, ntext, and image Pages for Tables or Indexed Views Using DBCC CHECKTABLE 350 Table of Contents viii 6.28 How to… Check Allocation and Structural Integrity of All Tables in a Filegroup using DBCC CHECKFILEGROUP 352 6.29 How to… Disable Parallelism in DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE 354 6.30 How to… Output DBCC Data in Tabular Form using WITH TABLERESULTS 355 Index Fragmentation 356 6.31 How to… Rebuild Indexes with DBCC DBREINDEX 356 6.32 How to… Defragment Indexes with DBCC INDEXDEFRAG 358 6.33 How to… Defragment Heaps 360 6.34 How to… Reclaim Space after Dropping Variable Length or Text Columns with DBCC CLEANTABLE 361 Chapter 7: Security 363 SQL Server Logins, Users, Roles, and Permissions 364 7.1 How to... Create Logins using Enterprise Manager and Transact-SQL 369 7.2 How to... Remove a SQL Server Login using Enterprise Manager or Transact-SQL 373 7.3 How to... Change the Default Database for a Login with sp_defaultdb 373 7.4 How to... Change the Default Login Language using sp_defaultlanguage 374 7.5 How to... List Defunct NT Users or Groups with Access to SQL Server 374 7.6 How to... Change or Add a Password in Enterprise Manager or Transact-SQL 375 7.7 How to... Create Database Users in Enterprise Manager or Transact-SQL 376 7.8 How to... Remove a User From the Database 377 7.9 How to... Create and Manage User-Defined Database Roles 379 7.10 How to... Display User-Defined or Fixed Database Roles in the Current Database using sp_helprole 382 7.11 How to... Display Members of a Specific Database Role using sp_helprolemember 383 Table of Contents ix 7.12 How to... Create, Manage, and Drop Application Roles 384 7.13 How to... Show Users and Roles with Access to the Current Database using sp_helpuser 386 7.14 How to... Display Account Permissions and Permission Path using xp_logininfo 387 7.15 How to... Deny SQL Server Access to a Windows NT User or Group using sp_denylogin 388 7.16 How to... Return a List of Fixed Server Roles with sp_helpsrvrole 388 7.17 How to... Display Permissions for Fixed Server Roles with sp_srvrolepermission 389 7.18 How to... List Members of a Fixed Server Role using sp_helpsrvrolemember 390 7.19 How to... Return a List of Fixed Database Roles with sp_helpdbfixedrole 391 7.20 How to... Display Fixed Database Role Permissions 392 SQL Server Permission Types 392 7.21 How to... GRANT, REVOKE, or DENY Statement and Object Permissions 394 7.22 How to... Report User Permissions and Statement Permissions using sp_helprotect 402 7.23 How to... Audit Security with SQL Profiler 406 7.24 How to... Audit Logins at the OS Level 411 7.25 How to... Monitor Updates with Triggers 412 Chapter 8: Replication 419 Replication Topology 420 Replication Types 421 Replication Configuration Methods 426 Data Type Considerations 426 SQL Server Version Compatibility 427 Pre-Replication Checklist 428 8.1 How to… Configure Snapshot Replication 429 8.2 How to… Create a Transactional Replication Publication 440 8.3 How to… Create a Merge Replication Publication 448 Table of Contents x 8.4 How to… Push a Subscription 456 8.5 How to… Create a Pull Subscription 460 8.6 How to… Transform Data Within Your Publication 464 8.7 How to… Use Dynamic Filters and Dynamic Snapshots for a Merge Publication 468 8.8 How to… Use Windows Synchronization Manager 476 8.9 How to… Configure Publication Properties 481 8.10 How to… Use Interactive Conflict Resolvers 491 8.11 How to… View Merge Replication Conflicts using Enterprise Manager 494 8.12 How to… Configure an Attachable Subscription Database 495 8.13 How to… Validate Replicated Data 497 8.14 How to… Launch Replication Wizards from Enterprise Manager 501 8.15 How to… Create and Manage Publications in Enterprise Manager 502 8.16 How to… Generate a Transact-SQL Script of Your Publication and Subscribers 502 8.17 How to… Configure Topology Roles from Enterprise Manager 504 8.18 How to… Configure Agent Profiles 507 8.19 How to… Disable Publishing and Distribution on Your Server 512 Replication System Tables 513 8.20 How to… Find Help Troubleshooting Replication Problems 517 Chapter 9: Linked Servers 521 Checklist: Functionality and Performance Considerations for Linked Servers 521 9.1 How to... Add a Linked Server 522 9.2 How to... Execute Distributed Queries with a Linked Server 534 9.3 How to... Execute Ad Hoc Distributed Queries using OPENROWSET and OPENDATASOURCE 536 9.4 How to... List All Tables from a Linked Server using sp_tables_ex 538 9.5 How to... Show Privilege Information about a Linked Server Table with sp_table_privileges_ex 539 9.6 How to... Display Column Data for a Linked Server Table using sp_columns_ex 540 Table of Contents xi 9.7 How to… Return Column Privileges for a Linked Server Table using sp_column_privileges_ex 541 9.8 How to... List Linked Servers Defined on Your Local Server 542 9.9 How to… Troubleshoot Linked Servers and Distributed Queries 543 Chapter 10: Failover Clustering 549 Cluster Meta Data 553 Cluster Administrator 553 Pre-Installation Checklist for SQL Server Failover Clustering 554 10.1 How to… Install a SQL Server 2000 Virtual Server 555 10.2 How to… Install a Service Pack for a SQL Server 2000 Virtual Server 557 10.3 How to… Implement Post-Installation Steps 558 Adding Additional Disk Resources 559 Other Post-Installation Configurations to Monitor 559 10.4 How to… Troubleshoot a Failed SQL Server Virtual Server 560 10.5 How to… Uninstall a SQL Server 2000 Virtual Server 560 Chapter 11: DTS 563 11.1 How to... Use DTS Designer 564 11.2 How to... Add and Configure a Connection Object 565 11.3 How to... Add a New Task 568 11.4 How to... Configure the File Transfer Protocol Task 568 11.5 How to... Configure the ActiveX Script Task 570 11.6 How to... Configure the Transform Data Task 572 Configuring the Execute Process Task 587 11.7 How to... Configure the Execute SQL Task 588 11.8 How to... Configure the Data Driven Query Task 589 11.9 How to... Configure the Copy SQL Server Objects Task 594 11.10 How to... Configure the Send Mail Task 595 11.11 How to... Configure the Bulk Insert Task 596 Table of Contents xii 11.12 How to... Configure the Execute Package Task 597 11.13 How to... Configure the Message Queue Task 598 11.14 How to... Configure Transfer Tasks 602 11.15 How to... Configure the Dynamic Properties Task 604 Configuring Workflow 606 Understanding the DTS Package Execution Context 609 11.16 How to… Configure Package Properties 611 11.17 How to… Set the Package Owner and User Password 613 11.18 How to… Schedule a DTS Package 614 11.19 How to… Use dtsrun 616 11.20 How to… Generate a dtsrun String Dynamically via dtsrunui 619 11.21 How to… Register a Custom Task 620 11.22 How to… Transfer DTS Packages between Servers 621 11.23 How to… Troubleshoot DTS Packages 622 Chapter 12: Log Shipping 625 The Log Shipping Process 625 Checklist… Log Shipping Pre-Configuration Recommendations and Best Practices 627 12.1 How to… Set up Log Shipping with the Database Maintenance Plan Wizard 628 12.2 How to… Monitor Log Shipping Status 634 12.3 How to… Configure Log Shipping Properties 636 12.4 How to… Create a Primary Role Change for the Secondary Server 637 12.5 How to… Troubleshoot Log Shipping 640 Chapter 13: Other Utilities 645 13.1 How to… Use osql 645 13.2 How to… Use Query Analyzer Templates 651 13.3 How to… Use the Query Analyzer Object Browser 654 13.4 How to… Use the Query Analyzer Keyboard Shortcuts 657 Table of Contents xiii 13.5 How to… Use SQLDIAG 659 13.6 How to… Check the Port Number of a Named Instance using the Server Network Utility 661 13.7 How to… Create a Client-Side Alias using the Client Network Utility 662 13.8 How to… Configure Full-Text Indexing 663 13.9 How to… Use Full-Text Index Stored Procedures 668 13.10 How to… Query a Full-Text Index 672 13.11 How to… Create Custom Messages 677 Chapter 14: DDL 681 14.1 How to… Create a Table 681 14.2 How to… Create a Table with a Primary Key 684 14.3 How to… Create a Unique Constraint 686 14.4 How to… Create a Computed Column 686 14.5 How to… Place a Table on a Filegroup 688 14.6 How to… Place Text or Images on a Separate Filegroup 688 14.7 How to… Create a Table with a Foreign Key Reference 689 14.8 How to... Set Recursive Foreign Key References 690 14.9 How to... Create a Cascading Change with ON DELETE 691 14.10 How to... Create a Cascading Change with ON UPDATE 691 14.11 How to… Use CHECK Constraints 692 14.12 How to… Use the DEFAULT Property During Table Creation 693 14.13 How to… Use the IDENTITY Property During Table Creation 694 14.14 How to… Use DBCC CHECKIDENT to View and Correct IDENTITY Seed Values 697 14.15 How to… Use ROWGUIDCOL 698 14.16 How to… Specify Column Collation 699 14.17 How to… Add a Column to an Existing Table 699 14.18 How to… Drop a Column and Constraints from a Table 700 14.19 How to… Change a Column Definition 700 Table of Contents xiv 14.20 How to… Remove the ROWGUIDCOL Property 701 14.21 How to… Add a Primary Key Constraint to an Existing Table 702 14.22 How to… Add a Foreign Key Constraint to an Existing Table 702 14.23 How to… Add a DEFAULT Constraint to an Existing Table 703 14.24 How to… Add a CHECK Constraint to an Existing Table 703 14.25 How to… Add a UNIQUE Constraint to an Existing Table 704 14.26 How to… Disable and Enable Constraints 704 14.27 How to… Drop a Table 704 14.28 How to… Create a Temporary Table 705 14.29 How to… Create a Table Variable 706 14.30 How to… Set Table Options with sp_tableoption 706 14.31 How to… Display Constraint Information for a Table Using sp_helpconstraint 707 14.32 How to… Use DBCC PINTABLE to Place a Table's Data Pages Into Memory 708 Performance Impact of Constraints 709 Views, Updateable Views, Indexed Views, Partitioned Views 709 14.33 How to… Create a View 712 14.34 How to… Create an Indexed View 713 14.35 How to… Create a Partitioned View 715 14.36 How to… Change a View 717 14.37 How to… Drop a View 718 14.38 How to… Refresh Meta Data for a Specific View 718 Stored Procedures 718 14.39 How to… Create Stored Procedures 719 14.40 How to… Modify Stored Procedures 721 14.41 How to… Drop Stored Procedures 722 14.42 How to… Run Stored Procedures at Startup 722 14.43 How to… Force a Stored Procedure to Recompile 722 14.44 How to… Print the Text of the Stored Procedure 723 Table of Contents xv 14.45 How to… Force a Flush of the Procedure Cache 723 14.46 How to… Add an Extended Stored Procedure 724 14.47 How to… Show the Name of the DLL Associated with an Extended Stored Procedure 724 14.48 How to… Unload the Extended Stored Procedure DLL from Memory 725 14.49 How to… Remove an Extended Stored Procedure 725 User-Defined Functions 725 14.50 How to… Create Scalar User-Defined Functions 727 14.51 How to… Create Inline User-Defined Functions 728 14.52 How to… Create Multistatement User-Defined Functions 729 14.53 How to… Modify User-Defined Functions 731 14.54 How to… Drop User-Defined Functions 731 Indexes 731 14.55 How to… Create an Index 733 14.56 How to… Drop an Index 735 14.57 How to… Display Indexes Defined for a Table 736 Triggers 736 14.58 How to… Create a Trigger 737 14.59 How to… Alter a Trigger 740 14.60 How to… Drop a Trigger 740 14.61 How to… Monitor the Nesting Level within a Trigger 740 14.62 How to… Specify the Firing Order of AFTER Triggers 741 14.63 How to… List Triggers for Table 741 14.64 How to… Enable and Disable Table Triggers 742 Working with Database Objects 743 14.65 How to… Change the Name of a User-Created Database Object 743 14.66 How to… Display Information About a Database Object 744 14.67 How to… Display Information on Database Object Dependencies 745 14.68 How to… Change an Object Owner 746 14.69 How to… Query the Meta Data of Database Objects 746 Table of Contents xvi Chapter 15: DML 7 4 9 15.1 How to… Use a Simple SELECT Statement 749 15.2 How to… Use DISTINCT to Remove Duplicate Values 751 15.3 How to… Use Column Aliases 753 15.4 How to… Use the TOP Keyword 754 15.5 How to… Use the WITH TIES Clause to Return Repeating Values in the TOP Clause 754 15.6 How to… Create a Table using INTO 755 15.7 How to… Use the FROM Clause 757 15.8 How to… Use Table JOINs 757 15.9 How to… Use Table Aliases and Self Joins 763 15.10 How to… Use Derived Tables 764 15.11 How to… Use the WHERE Clause to Specify Rows Returned in the Result Set 765 15.12 How to… Use the GROUP BY Clause 772 15.13 How to… Use the HAVING Clause 775 15.14 How to… Use the ORDER BY Clause 776 15.15 How to… Use Subqueries 778 15.16 How to… Use UNION to Combine Result Sets 780 15.17 How to… Use COMPUTE in a SELECT Statement 782 15.18 How to… Use the FOR Clause 783 15.19 How to… Use the INSERT Statement 784 15.20 How to… Use the UPDATE Statement 786 15.21 How to… Use the DELETE Statement 788 15.22 How to… TRUNCATE a TABLE 788 15.23 How to… Use Hints 789 Table of Contents xvii Chapter 16: Transact-SQL Techniques 793 16.1 How to… Use Aggregate Functions 793 16.2 How to… Use String Functions 800 16.3 How to… Use Date Functions 811 16.4 How to… Use Display Information with @@ Functions 814 16.5 How to… Use Mathematical Functions 818 16.6 How to… Use Security functions 825 16.7 How to… Use Meta Data Functions 827 16.8 How to… Use System Functions 830 16.9 How to… Use IMAGE, TEXT, and NTEXT Functions 836 16.10 How to… Use Information Schema Views 840 Normalization in a Nutshell 841 16.11 How to… Use CASE 844 BEGIN and END 845 16.12 How to… Use IF…ELSE 846 16.13 How to… Use WHILE, BREAK, and CONTINUE 847 16.14 How to… Use RETURN 849 16.15 How to... Use RAISERROR 850 Transact-SQL Cursors 852 16.16 How to… Create and use Transact- SQL Cursors 852 16.17 How to… Create Extended Properties 857 16.18 How to… Create a Script that Creates a Script 861 16.19 How to… Use sp_executesql 861 16.20 How to… Use String Concatenation 862 Batches 863 16.21 How to… Use EXEC 863 16.22 How to… Bind a Connection to Other Transactions using sp_bindsession and sp_getbindtoken 864 16.23 How to… Use COM Automation sp_oa Procedures 865 16.24 How to… Remove All Clean Buffers from the Buffer Pool 870 16.25 How to… Use WAITFOR 870 Table of Contents xviii Chapter 17: Performance Tuning 873 17.1 How to… Use System Monitor to Identify Hardware Bottlenecks 873 Query Tuning Best Practices 886 17.2 How To… Use SQL Profiler to Capture Activity 887 17.3 How to… Script a Server-Side Profiler Trace 894 17.4 How to… Use SHOWPLAN_ALL, SHOWPLAN_TEXT 904 17.5 How to… Use Graphical Showplan 906 17.6 How to… Use SET STATISTICS IO 912 17.7 How to… Use SET STATISTICS TIME 912 17.8 How to… Use SET STATISTICS PROFILE 913 17.9 How to… Use Query Analyzer's Server Trace and Client Statistics 915 Checklist: Index Best Practices 916 17.10 How to… Display fragmentation using DBCC SHOWCONTIG 920 17.11 How to… Use the Index Tuning Wizard 922 17.12 How to… Use CREATE STATISTICS 925 17.13 How to… Display Columns with Statistics with sp_helpstats 926 17.14 How to… Show Current Distribution Statistics for a Table DBCC SHOW_STATISTICS 927 17.15 How to… Update Statistics against all User-Defined Tables using sp_updatestats 928 Chapter 18: XML 9 3 1 XML 931 The Anatomy of an XML Document 932 18.1 How to… Use FOR XML 935 18.2 How to… Use sp_makewebtask to Output XML Documents 939 18.3 How to… Use OPENXML 940 Цена: 200руб. |
||||