Microsoft SQL Server 2008 Bible

Chapter 1: SQL and Relational Database Management Systems (RDBMS)

Table 1-1: Maximum Name Length Restrictions for Some of Database Objects

Table 1-2: Flat File Records Keeping

Chapter 2: Fundamental SQL Concepts and Principles

Table 2-1: Key SQL99 Areas

Table 2-2: Four Basic SQL Statements

Chapter 3: SQL Data Types

Table 3-1: Major Vendor Implementations Character String Data Types

Table 3-2: Binary String Data Types

Table 3-3: Exact Numeric Data Types

Table 3-4: Approximate Numeric Data Types

Table 3-5: Date and Time Data Types

Table 3-6: Interval Literals in Oracle

Chapter 4: Creating RDBMS Objects

Table 4-1: Valid CREATE Statements Cross-Reference

Chapter 5: Altering and Destroying RDBMS Objects

Table 5-1: Valid ALTER Statements Cross-Reference

Table 5-2: Valid DROP Statements Cross-Reference

Chapter 7: Sessions, Transactions, and Locks

Table 7-1: SQL Standard SET Statements

Table 7-2: Microsoft SQL Server 2000 SQL-92 Settings

Table 7-3: Microsoft SQL Server 2000 SET Statements

Table 7-4: Vendor-Specific ROLLBACK Statements

Table 7-5: SQL99 Transaction Isolation Levels

Table 7-6: Oracle 9i Lock Modes

Table 7-7: Microsoft SQL Server 2000 Lock Modes

Table 7-8: Microsoft SQL Server 2000 Locking Hints

Chapter 10: SQL Functions

Table 10-1: Standard SQL2 Functions

Table 10-2: Numeric Functions

Table 10-3: String Functions

Table 10-4: Date and Time Functions

Table 10-5: Getting Current Date Out of RDBMS

Table 10-6: Valid Range of the Time-Zone Values for Oracle's NEW_TIME Function:

Table 10-7: Aggregate Functions

Table 10-8: Conversion Functions

Table 10-9: Formatting MS SQL Server CONVERT Function Output for Dates

Table 10-10: Selected Oracle Datetime Format Templates

Table 10-11: Selected Oracle Number Format Templates

Table 10-12: IBM DB2 UDB Date Strings Formats

Table 10-13: IBM DB2 UDB Time String Formats

Table 10-14: Miscellaneous Functions

Chapter 11: SQL Operators

Table 11-1: Arithmetic Operators

Table 11-2: SQL Logical Operators

Table 11-3: Wildcard Characters for use with Operator LIKE

Table 11-4: Operators Precedence

Table 11-5: Comparison Operators

Table 11-6: Bitwise Operators

Table 11-7: Data Type Compatibility for Bitwise Operands

Chapter 12: SQL and RDBMS Security

Table 12-1: Selected Microsoft SQL Server 2000 User Management

Table 12-2: GRANT Statement Options

Table 12-3: Common Oracle 9i System Privileges

Table 12-4: Oracle 9i SYSDBA and SYSOPER System Privileges

Table 12-5: Common IBM DB2 UDB System Privileges

Table 12-6: SQL Object-Level Privileges

Table 12-7: IBM DB2 UDB Object-Level Privileges

Table 12-8: Oracle 9i Predefined Roles

Table 12-9: IBM DB2 UDB System Authorities

Table 12-10: Microsoft SQL Server Fixed Server Roles

Table 12-11: Microsoft SQL Server Fixed Database Roles

Table 12-12: Selected Microsoft SQL Server 2000 Role Management System-Stored Procedures

Table 12-13: IBM DB2 UDB Encryption Functions

Table 12-14: Oracle 9i Audit Levels

Table 12-15: Oracle 9i AUDIT Level Options

Table 12-16: IBM DB2 UDB Events Categories Available for Audit

Table 12-17: Microsoft SQL Server 2000 Event Categories

Chapter 13: The System Catalog and INFORMATION_SCHEMA

Table 13-1: SQL Standard INFORMATION_SCHEMA Views

Table 13-2: Static View Prefixes

Table 13-3: Selected Oracle Data Dictionary views

Table 13-4: Oracle Data Dictionary Views Correspondence to SQL99 INFORMATION_SCHEMA.

Table 13-5: Selected IBM DB2 UDB INFORMATION_SCHEMA Views

Table 13-6: INFORMATION_SCHEMA Views Column Names

Table 13-7: Updateable IBM DB2 UDB INFORMATION_SCHEMA Views

Table 13-8: Microsoft SQL Server 2000 INFORMATION_SCHEMA Views

Table 13-9: Microsoft SQL Server 2000 System Stored Procedure Categories

Table 13-10: Result Set Returned by sp_tables System Stored Procedures

Chapter 15: Dynamic and Embedded SQL Overview

Table 15-1: Data type Correspondence

Table 15-2: Indicator Variable Values (Input)

Table 15-3: Indicator Variable Values (Output)

Table 15-4: Selected SQLSTATE Codes

Table 15-5: Useful Embedded SQL Statements

Table 15-6: ANSI/ISO SQL Data type Codes

Chapter 16: SQL API

Table 16-1: SQL/CLI and ODBC Functions

Table 16-2: JDBC 3.0 Features

Table 16-3: Selected JDBC Drivers Vendors

Table 16-4: DB2 CLI vs. DB2 ODBC Driver Comparison

Table 16-5: OCI Compatibility Between Different Versions of Server and Client Software

Table 16-6: Selected Oracle OCI Functions

Table 16-7: ADO.NET Top Level Namespaces

Chapter 17: New Developments—XML, OLAP, and Objects

Table 17-1: The RDBMS/OODBMS Standards Comparison

Table 17-2: Database Products Supporting Pure OO Technology

Appendix C: Basics of Relational Database Design

Table C-1: "One Table" Design

Table C-2: Table CUSTOMER after First Normal Form

Table C-3: New ORDER Table

Table C-4: Table CUSTOMER after Second Normal Form

Table C-5: New ORDER Table

Table C-6: New PRODUCT Table

Table C-7: Table CUSTOMER After First Normal Form

Table C-8: New REGION Table

Appendix D: Installing RDBMS Software

Table D-1: Installation of Oracle on Windows: Platforms Requirements

Table D-2: Installation of Oracle on Sun Solaris: Platforms Requirements

Table D-3: Unix Environment Variables Necessary for Oracle 9i Installation on Sun Solaris Platform

Table D-4: Installation of Oracle on Linux: Requirements Overview

Table D-5: Environment Variables Necessary for Oracle 9i Installation on Linux

Table D-6: Installation of DB2 UDB on Windows: Platforms Requirements

Table D-7: Requirements for MS SQL Server Installation on Windows 2000

Appendix E: Accessing RDBMS

Table E-1: Most Commonly Used SQL*Plus Commands

Table E-2: Common CLP Options

Table E-3: Common CLP commands

Table E-4: Basic OSQL Commands

Appendix F: Installing the ACME Database

Table F-1: Common Errors from Running Oracle Scripts

Table F-2: Common Errors from Running DB2 Scripts

Table F-3: Common Errors from Running MS SQL Server Scripts

Appendix G: SQL Functions

Table G-1: Oracle 9i SQL Number Functions

Table G-2: Oracle 9i SQL Character Functions

Table G-3: Oracle 9i SQL Datetime Functions

Table G-4: Oracle 9i SQL Conversion Functions

Table G-5: Oracle 9i SQL Miscellaneous Single-Row Functions

Table G-6: Oracle 9i SQL Aggregate Functions

Table G-7: Oracle 9i SQL Analytic Functions

Table G-8: Oracle 9i SQL Object Reference Functions

Table G-9: IBM DB2 UDB Built-in Scalar Functions

Table G-10: IBM DB2 UDB Built-in Column Functions

Table G-11: IBM DB2 UDB Built-in Table Functions

Table G-12: IBM DB2 UDB Built-in Procedures

Table G-13: Microsoft SQL Server 2000 Built-in String Functions

Table G-14: Microsoft SQL Server 2000 Built-in Mathematical Functions

Table G-15: Microsoft SQL Server 2000 Built-in Date and Time Functions

Table G-16: Microsoft SQL Server 2000 Built-in Aggregate Functions

Table G-17: Microsoft SQL Server 2000 Built-in Text and Image Functions

Table G-18: Microsoft SQL Server 2000 Built-in Cursor Functions

Table G-19: Microsoft SQL Server 2000 Built-in Metadata Functions

Table G-20: Microsoft SQL Server 2000 Built-in Configuration Functions

Table G-21: Microsoft SQL Server 2000 Built-in Security Functions

Table G-22: Microsoft SQL Server 2000 Built-in System Functions

Table G-23: Microsoft SQL Server 2000 Built-in System Statistical Functions

Table G-24: Microsoft SQL Server 2000 Built-in Rowset Functions

Appendix H: SQL Syntax Reference

Table H-1: Simplified BNF Notation

Table H-2: Predicates

Appendix J: SQL99 Major Features Compliance Across Different RDBMS

Table J-1: SQL99-Defined Features across RDBMS

Appendix K: The Other RDBMS

Table K-1: RDBMS Software Vendors

Appendix L: A Brief Introduction to the Number Systems, Boolean Algebra, and Set Theory

Table L-1: Number Systems

Table L-2: Binary Number Represented in Hexadecimal Groups

Table L-3: Binary to Decimal Conversion

Table L-4: Decimal to Binary Conversion

Table L-5: NOT Truth Table

Table L-6: AND Truth table

Table L-7: OR Truth table

Table L-8: XOR Truth table

Table L-9: NAND Truth table

Table L-10: NOR Truth table

Table L-11: Boolean Algebra Operator Precedence

Table L-12: Precedence Rules Illustration

Table L-13: Identities of Boolean Algebra

Table L-14: Identities of Set Algebra