Chapter 1 Overview, installation and configuration of SQL Server 2008 2

Overview of SQL Server 2008 2

1. 1. 1 database engine 3

1. 1.2 Analysis Service3

1. 1.3 integration service3

1. 1.4 copy 4

1. 1.5 reporting service4

1. 1.6 notification service4

1. 1.7 full-text search 5

1. 1.8 service broker 5

1.2 Advantages of choosing SQL Server 2008 5

1.3 Software and hardware requirements for installing SQL Server 2008 6

1.4 installation example 7

1.5 server network configuration 13

1.5. 1 SQL Server Configuration Manager 13

1.5.2 Configure the server network protocol and network library 14.

1.5.3 Encrypt the connection with SQL Server 14.

1.6 client network configuration 16

1.6. 1 local client configuration 17

1.6.2 using firewall system 20

1.7 Summary 23

Chapter 2 Introduction of New Functions and Enhancements of SQL Server 2008 24

2. 1 Introduction of new and enhanced functions 24

2.2 Safety aspects 25

2.2. 1 encryption 25

Audit 26

2.3 Management aspects 26

2.3. 1 data compression 26

The resource manager 26

2.3.3 Managing unstructured data 27

2.4 Development aspects 27

2.4. 1 Insert multiple rows of data at a time

2.4.2 Mandatory Search Prompt 27

2.4.3 Grouping Set 28

Compatibility level 28

2.4.5 User-defined table data types 29

Table-valued parameter 29

2.4.7 consolidated statement 30

2.5 New data type 3 1

2.6 Installation 34

2.7 aspects of system performance 34

2.8 Other Enhancements 34

2.8. 1 New function of report service 34

2.8.2 Server Consolidation Solution 35

2.8.3 Integration with Microsoft Office 2007 36

SQL Server integration service 36

SQL Server analysis service 36

2.9 Summary 36

Chapter 3 SQL Server Management Tool Set 37

3. Introduction to1SQL Server Management Toolset 37

3.2 Introduction to Functional Components and Tool Windows 39

3.3 using SQL Server management toolset to manage the server 44

3.4 using SQL Server management toolset to write, analyze and edit scripts 45

3.5 Using SQL Server Management Toolset Template 52

3.6 Developing solutions and projects in SQL Server management toolset 55

3.7 SQL Server management toolset works with other components 6 1

3.8 Summary 6 1

Chapter 4 Databases and Specifications 62

4. 1 overview 62

4.2 Physical storage structure design and logical structure design of database 64

4.2. 1 Design of physical storage structure of database 64

4.2.2 Database logical structure design

4.3 Creating a Database 73

4.4 Delete the database 76

4.5 Modify the database 78

4.5. 1 Modify the database name 78

4.5.2 Changing the database owner 78

4.6 Database Design Specification 79

4.7 System Database 82

4.8 Summary 85

Chapter V Table 86

5. 1 overview 86

5.2 Table Structure 86

5.3 Create Table 87

5.3. 1 field 89

Data type 89

Table design 93

5.3.4 Globally Unique Identifier and Automatic Number Fields 93

5.3.5 User-defined data types 95

5.3.6 Create a calculated field 96

5.4 Modify data records 97

5.5 Change the Form Design 98

5.6 Delete table 102.

5.7 Table Connection 103

5.8 Other table operations 106

5.8. 1 table import 106

5.8.2 Lock some records in table 109.

5.9 Summary 1 1 1

Chapter VI Index 1 12

6. 1 Overview 1 12

6.2 Basic concept of index 1 13

6.2. 1 index structure 1 13

6.2.2 Advantages and disadvantages of index 1 13

6.3 Design standard of index 1 13

6.4 index type 1 15

6.4. 1 clustered index and nonclustered index 1 15

6.4.2 Unique index and non-unique index 1 18

6.4.3 Single-column index and multi-column index 1 19

6.5 Filling factor 1 19

6.6 Create index 120

6.7 disable index 125

6.8 Delete the index 128

6.9 Summary 132

Chapter VII View 133

7. 1 view overview 133

7. Advantages of1.1view 134

7. 1.2 view type 134

7.2 Create View 135

7.2. 1 standard for creating views 135

7.2.2 Method of Creating Views 136

7.3 Use the view 14 1

Use indexed view 14 1.

7.3.2 Use partition view 145

7.4 updatable view 147

7.5 Update data through view 148

7.6 Change the view 15 1

7. 6. 1 Change the name of the view 152

7.6.2 Change the definition of view 152

7.7 Delete View 154

7.8 Summary 154

Chapter VIII Backup, Recovery and Maintenance of Database 155

8. 1 overview 155

8.2 Backup database 156

8.2. 1 Backup Overview 156

8.2.2 Full backup 158

8.2.3 Differential backup 16 1

8.2.4 Backup with transaction log 163

8.2.5 Migrate the database to other systems for backup 164

8.2.6 Import and Export 165

8.3 Recovering and restoring the database 168

8.3. 1 reduction scheme 168

8.3.2 Solving orphaned users 169

8.3.3 Restore differential database backup 170.

8.3.4 Restore transaction log backup 174

8.3.5 Full recovery 178

8.3.6 Large-capacity log record recovery 179

8.4 Backup and recovery system database 180

8.4. 1 backup system database 180

8.4.2 Restore the system database 18 1

8.5 Attaching and Detaching Databases 182

8. 5. 1 detailed database 182

8.5.2 Additional database 185

8.5.3 Move the database by separating and attaching 187.

8.6 copy 187

8.6. 1 Overview of replication types

8.6.2 Copy 189

8.7 Maintenance Plan and Strategy 195

8.7. 1 maintenance plan overview 195

8.7.2 How to create a maintenance plan 196

8.8 Summary 198

Chapter 9 Stored Procedures 199

9. 1 Overview of stored procedures 199

9. Advantages and disadvantages of1.1stored procedure 199

9. 1.2 type stored procedure 200

9.2 Create a Stored Procedure 20 1

9. 2. 1 Some Points for Attention in Creating Custom Stored Procedure 20 1

9.2.2 Create a user-defined stored procedure 20 1

9.2.3 Extended Stored Procedure 203

9.2.4 Syntax Analysis of Stored Procedure Expressions 203

9.2.5 Parameter 2 1 1

9.2.6 Cursor 2 1 1

9.2.7 Nested Stored Procedure 2 13

9.2.8 Using database transaction 2 15 in stored procedures

9.3 Execute the stored procedure 2 15

9.3. 1 Execute stored procedures in SQL Server 2 15.

9.3.2 Calling Stored Procedure 2 16 in Java/JSP

9.4 Update Stored Procedure 2 16

9. 4. 1 Rename stored procedure 2 16

9.4.2 Modify the stored procedure 2 17

9.4.3 recompile stored procedure 2 19

9.5 Delete Stored Procedure 2 19

9.6 System Stored Procedure 22 1

9.7 Optimizing stored procedures 222

9.8 Pagination of stored procedures 222

9.9 Project Example: Enterprise Staff Attendance Inquiry System 226

9. 10 Summary 232

Chapter 10 T-SQL foundation 233

10. 1 overview 233

On the characteristics of10.1.1t-SQL language50000.00000000015

10. 1.2 T-SQL language type 234

10.2 data type 234

10.3 relational algebra 236

10.4 T-SQL Basic Syntax Convention 240

10.5 variable 240

10.6 Operator 243

10.7 function 245

10.8 control flow statement 253

10.9 summary 258

Chapter 1 1 Advanced T-SQL 259

Overview of 1 1. 1 259

1 1.2 scripts and batch processing 259

1 1.2. 1 Add and Insert in Batch ... Select 259.

1 1.2.2 batch addition, insert top 26 1

1 1.2.3 Batch processing with selection ... plus 26 1

1 1.3 Add the query result of stored procedure 262.

1 1.4 modify data records with update TOP 263.

1 1.5 Delete data record 264

1 1.5. 1 delete data records with delete TOP 264.

1 1.5.2 Delete the data record with output clause 264.

1 1.5.3 Delete data records with truncated table expression 265.

1 1.6 summarizing and grouping data 266

1 1.6. 1 grouped by 266.

1 1.6.2 Use the calculation times 268.

1 1.7 fuzzy query 270

1 1.8 full-text index using T-SQL code 27 1

1 1.9 Summary 284

Chapter 12 Trigger 285

12. 1 overview 285

12.2 Create Trigger 287

12.3 modify trigger 290

12. 3. 1 View Trigger 290

Modify the definition of trigger 292

12.3.3 Rename trigger 292

12.4 disable and enable the trigger 293

Delete trigger 293

12.6 nested triggers 294

12.7 recursive trigger 297

12.8 uses trigger 298.

Overview of using triggers 298

12.8.2 Check whether a specific field has been modified 299

12.8.3 uses a method that includes rolling back a transaction.

The trigger 300 of the expression

12.8.4 conditional insert trigger 30 1

12.8.5 specifies the order of AFTER triggers.

12.8.6 Update view 304 with INSTEAD OF trigger.

12.8.7 Capture expression 306 with TRY ...

12.8.8 Create a user-defined error message using sp_addmessage 309.

12.9 DDL trigger, DML trigger and CLR trigger 309

12.9. 1 DDL trigger 309

12.9.2 DML trigger 3 14

CLR trigger 3 15

12. 10 Example: Employee Dining Statistics System 3 17

12. 1 1 summary 324

Chapter 13 operation and alarm 325

13. 1 overview 325

13.2 Create a job 325

13.3 management work 329

13. 3. 1 modification work 329

Run job 332

13.3.3 Disable job 333

13.3.4 Stop operation 334

Delete job 335

13.4 job response 336

Alarm 338

13. 5. 1 Create Event Alert 338

13.5.2 Create Performance Alerts 340

13.5.3 Edit Alarm 34 1

Delete alarm 343

13.6 Operator 343

1 create operator 343

13.6.2 Assign alarms to operators 345

Modify operator 346

Delete operator 348

13.7 Configure SQL Server Agent Mail for Job Notifications and Alerts 348

13.8 Diagnosing and solving operation and alarm breakdowns 349

13.9 Example: Enterprise Patrol Attendance System 350

13. 10 summary 352

Chapter 14 security 353

14. 1 overview 353

14.2 certification 354

14.2. 1 authentication mode overview 354

14.2.2 change of authentication mode 354

14.3 safety management 355

1 SQL Server login management 356

14.3.2 database role management

Rights management 36 1

Application Role Management 365

Transaction log management 366

14.4 access management 367

14.4. 1 Enable guest users 368

14.4.2 user access denied 368

14.4.3 Manage database access 370

Authorize database access 372

14.4.5 Manage access to tables and columns 373

14.5 data file security 375

14.6 security configuration 377

Security Configuration Policy 377

14.7 encryption 380

14.8 summary 384

Chapter 15 SQL Server Reporting Service 385

15. 1 SQL Server Report Service Overview 385

15.2 reporting service architecture 386

15.3 reporting service function 388

15.4 Install SQL Server Reporting Service 390

15.5 Configure reporting service 394

15.6 Creating and designing reports 399

15.7 Deploying Report Services 402

15.7. 1 report service deployment mode 402

15.7.2 reporting service deployment plan 404

15.7.3 Copy Report Server Environment 406

15.8 access report 407

15.9 release report 408

15. 10 management report service411

Manage report service requirements

15. 10.2 report server management tool 4 1 1

Backup and recovery of reporting service installation 4 13

15. 10.4 report server database

15. 1 1 summary 4 16

Chapter 16 Business Intelligence Development Toolkit 4 17

16. 14 17 Overview

16.2 Introduction to the bidding graphical interface 4 17

16.2. 1 Start Page 4 18

16.2.2 Main bidding interface 4 18

Menu 4 19

16.2.4 Customize environment, tools and windows 42 1

16.3 Generate BI solution 423

16.4 design, develop and debug SQL Server Integration Services package 426

16.5 Summary 436

Chapter 17 Analysis Services 437

1 overview 437

17.2 multidimensional data 437

17.3 data mining 438

17.4 using analysis services 440

17.5 Summary 456

Chapter 18 Integrated Services 457

18. 1 overview 457

18.2 copying and downloading files 459

18.2. 1 FTP task

18.2.2 file system task

18.3 send email in response to event 467.

18.4 Managing SQL Server objects and data 472

18.5 Summary 476

Chapter 19 Performance Tools 477

19. 1 overview 477

19.2 Database Engine Optimization Advisor 477

19.2. 1 Database Engine Tuning Advisor GUI program 478

19.2.2 dta utility 484

19.3 SQL Server profiler) 489

1sqltrace 489

19.3.2 using SQL Server Profiler 490

19.4 Summary 495

Chapter 20 Configuration tool 496

20. 1 overview 496

20.2 Report Service Configuration Manager 496

20.3 SQL Server Configuration Manager 498

20.4 SQL Server Error and Usage Report 500

20.5 SQL Server Installation Center 50 1

20.6 Summary 502

The second interactive improvement article

Chapter 2 1 Improving query performance 504

2 1. 1 overview 504

2 1.2 index to quickly execute the query 504.

2 1.3 using database engine tuning advisor to optimize queries 509

2 1.4 Other methods to improve query performance 5 13

2 1.5 Summary 5 14

Chapter 22 Access to SQL Server 2008 5 16

22. 1 configure server 5 16

22.2 access to SQL Server 5 19

22.3 Remote Access 522

22.4 Multiple SQL Server 2008 interconnected synchronization data in LAN 524

22.5 Project Example: Catering Statistics System of Service Department 526

22.6 Summary 532

Chapter 23 Integration of SQL Server 2008 and Office 2007 533

23. 1 overview 533

23.2 Integration of SQL Server and Excel 533

23.2. 1 using SQL Server data in Excel 533

23.2.2 Excel data transmission to SQL Server 536.

23.3 SQL Server 2008 Data Mining Add-in 54 1

23.3. 1 Excel table analysis tool 542

23.3.2 Excel data mining client 550

23.3.3 data mining Visio template 556

23.4 using SQL Server 2008 and Office 2007

OLAP construction report application 56 1

23.5 Summary 564

Chapter 24 Java and SQL Server 565

24. 1 overview 565

24.2 using JDBC to interact with SQL Server 565

24.3 using ODBC to interact with SQL Server 567

24.4 project example: digital book management system 57 1

24.5 Summary 579

The third project actual combat article

Chapter 25 Example of Project Completion 58 1

25. 1 overview 58 1

25.2 enterprise patrol attendance system (B/S) 58 1

25.3 Digital Library Management System (combining C/S with B/S) 598

25.4 Summary 620

Instance directory

Example 2- 1 Insert multiple rows of data at once 27

Example 2-2 Force Search Tip 28

Example 2-3 Creating a User-defined Table Data Type with the CREATE TYPE Statement 29

Example 2-4 Using Table-Valued Parameter 29

Example 2-5 Use MERGE to update and delete table 3 1

Example 2-6 New Data Type 1 3 1

Example 2-7 New Data Type 2 32

Example 2-8 New Data Type 3 32

Example 2-9 Setting System Data Type datetime2 32

Example 2- 10 Controlling the Accuracy of Data Types 33

Example 2- 1 1 new data type 4 33

Example 2- 12 Father-son relationship between managers and employees 33

Example 3- 1 Indent Code 49

Example 3-2 Stored Procedure for Finding Specific Parts Order 53

Example 3-3 Using Custom Template 54

Example 3-4 Determining Work Orders with End Date Earlier than Start Date 59

Example 4- 1 Create a database containing unspecified files 75

Example 4-2 Creating a Database of Specified Data and Transaction Log Files 76

Example 4-3 Deleting a Single Database 77

Example 4-4 Deleting Multiple Databases 77

Example 4-5 Deleting Database Snapshot 77

Example 4-6 Modify Database Name 78

Example 4-7 Modify Database Owner 79

Example 4-8 Rebuilding System Database 85

Example 8- 1 Backup to Disk Device 159

Example 8-2 Creating Full and Differential Backups 162

Example 8-3 Data Consolidation 166

Example 8-4 query table t 1 166 through OPENDATASOURCE.

Example 8-5 Query Table t 1 166 through OPENROWSET.

Example 8-6 using bcp 167

Example 8-7 Execute bcp 167 through SQL.

Example 8-8 Data Conversion 167

Example 8-9 Finding Orphaned Users 170

Example 8- 10 Add the login name corresponding to the user name to syslogins 170.

Example 8- 1 1 Map the user name to the specified login name 170

Example 8- 12 Solving the Problem that dbo is an Independent User 170

Example 8- 13 Restore Database and Backup Differential Database 173

Example 8- 14 Restore database and its differential database and transaction log backup 173

Example 8- 15 The transaction log is restored to the mark 177.

Example 8- 16 Restoring a Complete Database 179

Example 8- 17 separating database 1 183

Example 8- 18 Detaching Database 2 183

Example 8- 19 Opening Database Master Key 185

Example 8-20 Enable Automatic Encryption for Master Key 185

Example 9- 1 Judge whether there is 20 1 in the stored procedure.

Example 9-2 Creating a Stored Procedure 202

Example 9-3 Simple Process Using Complex SELECT Statement 205

Example 9-4 Simple Process with Parameters 206

Example 9-5 Simple Process of Using Wildcard Parameters 207

Example 9-6 Process Using Output Parameters 208

Example 9-7 Process of Using Output Cursor Parameter 209

Example 9-8 process of using WITH RECOMPILE option 2 10

Example 9-9 Using WITH ENCRYPTION Option Procedure 2 10

Example 9- 10 Create a stored procedure with parameter 2 1 1

Example 9- 1 1 Using Cursor 2 12 in Stored Procedure

Example 9- 12 Calling Stored Procedure 2 16 in JSP

Example 9- 13 Renaming Stored Procedure 2 17

Example 9- 14 Modifying Stored Procedure Test 2 18

Example 9- 15 Deleting Stored Procedure 220 in Database Engine Query

Example 9- 16 Delete Extended Stored Procedure 220

Example 9-18 staffQuery.jsp 227

Example 9- 19 Creating a Stored Procedure kqOnDutyReport 1 229

Example 9-20 Creating a Stored Procedure SelectCDate 1 230

Example 12- 1 Send notification email 288

Example 12-2 replaces the trigger 288.

Example 12-3 If the application 288 in the trigger is updated,

Example 12-4 virtual tables inserted and deleted 288

Example 12-5 Creating Trigger 289

Example 12-6 Updating Data in t2 Table 290

Example 12-7 queries all triggers in database 290.

Example 12-8 uses SQL statements to check the contents of the trigger tg_sum 29 1

Modify the contents of the trigger 292

Example 12- 10 Modify Trigger Name 293

Example 12- 1 1 disables the trigger tg_sum 293.

Example 12- 12 Delete Trigger 294

Example 12- 13 Creating Operation Record Table 295

Example 12- 14 creates a trigger 295 on a data table.

Example 12- 15 Create Category Table 295

Example 12- 16 creates a trigger 296 on the category table.

Example 12- 17 Check whether the second and third columns of the checklist T have been modified.

Example 12- 18 triggers rollback 300.

Example 12- 19 triggers rollback 30 1

Example 12-20 triggers rollback 30 1

Example 12-2 1 conditional insert trigger 30 1

Example 12-22 Creating Product Insert Trigger 302

Example 12-23 creates product_Insert 1 trigger 302.

Example 12-24 Changing Trigger Order 303

Example 12-25 uses Instead Of trigger 305.

Example 12-26 contains a stored procedure 307 of an error handling function.

Example 12-27 Capture Block Capture Error 308

Example 12-28 tries to catch construction processing error 308.

Example 12-29 Creating a User-Defined Message 309

Example 12-30 calls the defined error message 309.

Example 12-3 1 Create a stored procedure 3 12 to protect the data table in the database from being modified and deleted.

The example 12-32 was created to protect all databases in the current SQL Server server from being deleted.

3 12 stored procedure

Example 12-33 creates a trigger 3 13 to record the database modification status.

Example 12-34 Judge whether the inventory quantity is 0 3 15.

Example 12-35 uses the inserted table 3 16.

Example 12-36 employee dining statistics system 3 17

Example 13- 1 Add non-functional work order 328.

Example 13-2 adds a job 328 with paging, email and network sending information.

Example 13-3 Modify Job 329

Example 13-5 Change Original Job Step 330

Example 13-6 Delete the original operation step 33 1.

Example 13-7 Delete Associated Server from Job 33 1

Example 13-8 Assign Job to Local Server 33 1

Example 13-9 assigns jobs to different servers 332.

Example 13- 10 Start Job 333

Example 13- 1 1 Change the name of the job 334

Example 13- 12 Stop Job 335

Example 13- 13 Delete Job 336

Example 13- 14 Add an email notification to alert 337.

Example 13- 15 Create Event Alert 340

Example 13- 16 Edit Alert 342

Example 13- 17 Delete Alarm 343

Example 13- 18 Setting Operator Information 345

Example 13- 19 Modifying Operator Information 347

Example 13-20 Delete Operator 348

Example 13-2 1 enterprise patrol attendance system 350

Example 14- 1 Create Login ID and Default Database 358

Example 14-2 creates login ID 358 in other default languages.

Example 14-3 creates a login ID 358 with a specific SID.

Example 14-4 Delete Login 358

Example 14-5 sets the Windows NT user as SQL Server login 358.

Example 14-6 denies Windows NT users to log on to SQL Server 359.

Example 14-7 Delete the login information of Windows NT user 359.

The example 14-8 shows the login information 360.

Example 14-9 Adding Users to Fixed Server Role 36 1

Example 14- 10 Creating Application Role 365

Example 14- 1 1 Delete application roles in database 365.

Example 14- 12 Modifying the Password of Application Role 366

Example 14- 13 Enable Guest User 368

Example 14- 14 Enable/Disable Login 368

Example 14- 15 Check the disabled login 368

Example 14- 16 Delete Login 370

Example 14- 17 Creating Database Roles and Adding Users 370

Example 14- 18 Judging the current user 37 1

Example 14- 19 Delete Users and Roles 37 1

Example 14-20 grants database user 37 1 some permissions.

Example 14-2 1 Create Database User 372

Example 14-22 Revoking user authorization 372

Example 14-24 grants the user the operation right to table 373.

Example 14-25 Revoking the rights granted to users 374

Example 14-26 User access denied 374

Example 14-27 Grant Permission 374

Example 14-29 Generate Database Master Key 38 1

Example 14-30 Generate Key 382

Example 14-3 1 encrypted data 382

Example 14-32 data decryption 383

Example 14-33 uses RC4 algorithm to generate symmetric key 383.

Example 15- 1 report server URL example 396

Example 22- 1 Query the protocol used by the current connection 52 1

Example 22-2 Connecting SQL Server 522 with Connection String

Example 22-3 Creating a Linked Server 526

Example 22-4 Service Department Dining Statistics System 526

Example 24- 1 connecting to MS SQL Server 566 using JDBC

Example 24-2 connecting MS SQL Server 57 1 with ODBC data source.

Example 24-3 Digital Book Management System 57 1

Example 25- 1 enterprise patrol attendance system (B/S) 58 1

Example 25-2 Digital Library Management System (Combination of C/S and B/S) 598