This 90 page book contains a comprehensive description of the major features of SQL Server 2000 and many technical notes on performance and programming issues.
The SQL Sever 2000 Handbook is now available online.
The book is designed for programmers who need to look at the power of SQL Server beyond simple queries and data update applications. Most areas of SQL Server implementation are covered including:
- SQL Server Tools including the Enterprise Manager.
- SQL syntax including different methods for joining tables and complex select criteria involving sub-queries.
- Defining a database table and associated indexes and specifying field properties, constraints, relationships between tables and referential integrity.
- Different views available including partitioned views and the integration of data from other database management systems.
- Stored procedures, the Transact-SQL programming language and parameters, output parameters, and return values.
- Using triggers to program business rules and data validation into the database management system.
- Optimisation techniques.
- Server configuration and security.
All topics are covered in a concise manner at a high level and the SQL Server Handbook is useful both as an introduction and as a reference used together with the full documentation from Microsoft.
| 1. CONTENTS 3
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 |
6. INDEXES 43
UNIQUE INDEX CONSTRAINT 43
CLUSTERED INDEX 43
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 |
