MSDN Documentation

SQL Reference Guide

This section provides a comprehensive reference for SQL (Structured Query Language), the standard language for managing and manipulating relational databases.

Core Concepts

SQL is built around several fundamental concepts:

• Tables: The basic structure for storing data, consisting of rows and columns.
• Rows (Records): A single entry in a table, representing one item or entity.
• Columns (Fields): Attributes of the data stored in a table. Each column has a specific data type.
• Primary Key: A column or set of columns that uniquely identifies each row in a table.
• Foreign Key: A column or set of columns in one table that refers to the primary key in another table, establishing a link between them.
• SQL Queries: Commands used to retrieve, insert, update, or delete data from a database.

Common SQL Data Types

Understanding data types is crucial for efficient data storage and manipulation:

• INT / INTEGER: Whole numbers.
• DECIMAL(p, s) / NUMERIC(p, s): Exact fixed-point numbers, where p is precision and s is scale.
• FLOAT / REAL / DOUBLE PRECISION: Approximate floating-point numbers.
• CHAR(n) / VARCHAR(n): Fixed-length and variable-length character strings.
• DATE: Stores dates (year, month, day).
• TIME: Stores time of day (hour, minute, second).
• DATETIME / TIMESTAMP: Stores both date and time.
• BOOLEAN: Stores TRUE or FALSE values.
• BLOB / CLOB: Binary Large Objects and Character Large Objects for storing large data.

SQL Operators

Operators perform operations on data:

• Arithmetic: +, -, *, /, %
• Comparison: =, <> (or !=), <, >, <=, >=
• Logical: AND, OR, NOT
• Special: BETWEEN, LIKE, IN, IS NULL, IS NOT NULL

Built-in Functions

SQL provides numerous built-in functions for various tasks:

• Aggregate Functions: COUNT(), SUM(), AVG(), MIN(), MAX()
• Scalar Functions: UPPER(), LOWER(), SUBSTRING(), LENGTH(), COALESCE()
• Date/Time Functions: GETDATE(), DATEPART(), DATEADD()
• String Functions: CONCAT(), REPLACE(), TRIM()

SQL Statements

SQL statements are categorized into different languages:

Data Manipulation Language (DML)

Used for managing data within schema objects:

• SELECT: Retrieves data from one or more tables.

  SELECT column1, column2
  FROM table_name
  WHERE condition;

• INSERT: Inserts new rows into a table.

  INSERT INTO table_name (column1, column2)
  VALUES (value1, value2);

• UPDATE: Modifies existing data in a table.

  UPDATE table_name
  SET column1 = value1, column2 = value2
  WHERE condition;

• DELETE: Removes rows from a table.

  DELETE FROM table_name
  WHERE condition;

Data Definition Language (DDL)

Used for defining and modifying database structure:

• CREATE TABLE: Creates a new table.

  CREATE TABLE table_name (
      column1 datatype constraints,
      column2 datatype constraints,
      ...
  );

• ALTER TABLE: Modifies an existing table.

  ALTER TABLE table_name
  ADD column_name datatype;

• DROP TABLE: Deletes a table.

  DROP TABLE table_name;

• CREATE INDEX: Creates an index on a table.
• DROP INDEX: Deletes an index.

Data Control Language (DCL)

Used for managing permissions and access:

• GRANT: Grants privileges to users.
• REVOKE: Revokes privileges from users.

Transactions

A transaction is a sequence of one or more SQL operations performed as a single logical unit of work.

• BEGIN TRANSACTION (or START TRANSACTION): Starts a new transaction.
• COMMIT: Saves all changes made within the transaction.
• ROLLBACK: Undoes all changes made within the transaction.

Indexing

Indexes are special lookup tables that the database search engine can use to speed up data retrieval operations. They work similarly to the index in the back of a book.

• Clustered Index: Determines the physical order of data in a table. A table can have only one clustered index.
• Non-Clustered Index: A separate structure from the data rows. It contains pointers to the data rows. A table can have multiple non-clustered indexes.

Performance Tuning

Optimizing SQL queries is crucial for application responsiveness:

• Use appropriate indexes.
• Write efficient queries: Avoid SELECT *, use JOIN clauses effectively, and filter data early with WHERE clauses.
• Analyze query execution plans.
• Optimize database schema design.
• Regularly update statistics.