SQL Server 2000 handbook

2. SQL SERVER OVERVIEW 6
RELATIONAL DATABASE TERMINOLOGY 6
SQL SERVER HISTORY 6
Sybase 6
SQL Server 4.2 6
SQL Server 6.0 7
SQL Server 6.5 7
SQL Server 7.0 7
SQL Server 2000 7
SQL SERVER FEATURES 8
Transactions 8
Data Dictionary 8
Constraints 8
Structured Query Language 8
Enterprise Networking 9
Administration 9
Connectivity 10
Views 10
Triggers 10
Stored Procedures 10
Replication 11
User Defined Functions 11
XML 11
HTML 11

3. SQL TOOLS 12
SERVICE MANAGER 12
ENTERPRISE MANAGER 12
Register the Server 13
SQL QUERY ANALYSER 14
OSQL 15

4. SQL SYNTAX 17
PUBS 17
SELECT STATEMENT 17
Field List 18
WHERE Clause 18
Wild Cards 19
FROM Clause 19
ORDER BY 20
Natural Join 20
GROUP BY Clause 21
HAVING Clause 21
DISTINCT 21
Inner (Natural) Join 22
Outer Join 22
Sub Queries 22
UNION 23
FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64] 23
SELECT .. INTO 24
INSERT STATEMENT 24
UPDATE STATEMENT 24
DELETE STATEMENT 25

5. DATABASE DEFINITION 26
ENTERPRISE MANAGER 26
CREATE A DATABASE 27
CREATE A TABLE 29
Data Types 30
Nulls and Defaults 31
Table Ownership 31
FIELD PROPERTIES 32
Null Values 33
DEFAULT CONSTRAINTS 34
CHECK CONSTRAINTS 34
CREATE A PRIMARY KEY 36
Identity Columns 36
Unique Identifiers 37
PRIMARY KEY CONSTRAINT 38
FOREIGN KEYS AND REFERENTIAL INTEGRITY 39
USER DEFINED DATA TYPES 41
DEFAULTS AND RULES 41
Defaults 42
Rules 42

7. VIEWS 45
INDEXED VIEWS 46
CHECK OPTION 46
PARTITIONED VIEWS 46
OPENROWSET 47
LINKED SERVERS 47
TEMPORARY TABLES 48

8. STORED PROCEDURES 49
EXECUTING A STORED PROCEDURE 50
PASSING PARAMETERS 52
RETURNING A VALUE 53
OUTPUT PARAMETERS 53
PROGRAM STRUCTURES 54
LOCAL VARIABLES 55
SYSTEM VARIABLES 56
SCALAR FUNCTIONS 57
CASE EXPRESSION 57
CURSORS 58
SYSTEM PROCEDURES 59
EXTENDED PROCEDURES 60
EXTENDED MAIL PROCEDURES 61
ERROR HANDLING 61
TRANSACTIONS 63
DISTRIBUTED TRANSACTIONS 64

9. TRIGGERS 66
TRIGGER PROGRAM STRUCTURE 66
FIELD LEVEL VALIDATION 68
RECORD LEVEL VALIDATION 69
CHECKING VALUES AGAINST ANOTHER TABLE 69
PREVENTING CHANGES TO A FIELD 69
REFERENTIAL INTEGRITY CHECKS 70
Checking a Foreign Key 70
Ensuring Unique Candidate Keys 71
Checking Referential Integrity on Delete 71
CASCADING DELETE 71
UPDATING ANOTHER TABLE 72

10. SQL SERVER OPTIMISATION 74
QUERY OPTIMISATION 74
Update Statistics 74
Index Design 74
Ordering 76
Showplan 76
SQL Trace 77
Optimiser Hints 78
CLUSTERED INDEXES 78
INDEX TUNING WIZARD 79
STORED PROCEDURE RECOMPILATION 79
DEFERRED UPDATES 80
LOCKING ISSUES 80

11. CONFIGURATION 82
SERVER CONFIGURATION 82
Memory 82
Lock Escalation Percentage 83
Network Packet Size 83
Open Databases 83
User Connections 83
DATABASE CONFIGURATION 84
Size of tempdb 84
Truncate Log on Checkpoint 84
Deleting a Database 84
BACKUPS 84

12. SECURITY 85
SERVER LOGINS 85
DATABASE USERS 86
Permissions 87

13. INDEX 90