SQL Table

Basic Components of SQL Table Structure

Columns, Data Types and Constraints

• CHAR: Fixed-length character string
• VARCHAR: Variable-length character string
• INT: Integer numeric value
• DECIMAL: Fixed-point numeric value
• DATE: Date value
• TIME: Time value

• NOT NULL: Ensures a column always contains a value
• UNIQUE: Ensures that each value in a column is unique
• PRIMARY KEY: Helps uniquely identify each row in a table
• FOREIGN KEY: Links two tables together
• CHECK: Validates if a specified condition is true

Primary and Foreign Keys

Indexes and Performance Optimisation

• Clustered index: Determines the physical order of the data in a table
• Non-clustered index: Maintains a separate index structure, with pointers to the actual data
• Unique index: Enforces the uniqueness of the indexed columns
• Full-text index: Allows searching for words or phrases within text columns

SQL Table Types and Their Uses

SQL table types have various classifications based on their usage and functionality. Among the most common are temporary tables, system tables, and partitioned tables. These serve different purposes for efficient data storage, intermediate results storage, and database management.

Temporary Tables for Intermediate Results

• Local Temporary Table: Created with the prefix #, they are only visible within the current user session and are automatically dropped when the session concludes.
• Global Temporary Table: Prefixed with ##, they can be shared across different user sessions but are dropped automatically when the last session referencing the table is closed.

• Complex data manipulation: When dealing with large data sets or calculations, temporary tables can help break down operations into manageable parts.
• Data summarisation: They facilitate calculations on aggregated data and can refine the results for reporting or analysis purposes.
• Data filtering: Temporary tables can be used to filter data or store preliminary results for further processing by subsequent queries.

System Tables for Database Management

Partitioned Tables for Efficient Data Storage

• Query performance: Due to data locality, queries that filter data based on the partitioning column can efficiently access only relevant partitions, reducing the need for full table scans.
• Data management: Partitioned tables enable efficient data maintenance operations such as data archiving, deletion, or loading using partition switching techniques.
• Storage optimisation: Partitions can be stored on different filegroups, allowing for better storage management and resource utilisation.
• Parallelism: Queries involving data aggregation or complex calculations can be executed in parallel across multiple partitions, resulting in faster response times.

Exploring SQL Table Examples

Creating SQL Tables with Basic Syntax

In SQL, the CREATE TABLE statement is used to create and define tables. To create an SQL table, it's necessary to define the columns, the data types associated with each column and, optionally, any constraints to enforce data integrity. Here's an example of a basic CREATE TABLE statement:

 CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL, DateOfBirth DATE, Email VARCHAR(100) UNIQUE, Position VARCHAR(50), Salary DECIMAL(10, 2) );

In this example, we create an Employees table with the following columns:

• EmployeeID: Integer data type and serves as the primary key uniquely identifying each record
• FirstName: Variable-length character string with a maximum of 50 characters and cannot be null
• LastName: Variable-length character string with a maximum of 50 characters and cannot be null
• DateOfBirth: Date data type to store the date of birth
• Email: Variable-length character string with a maximum of 100 characters and enforces uniqueness
• Position: Variable-length character string with a maximum of 50 characters
• Salary: Decimal data type with 10 total digits and two decimal places

Modifying SQL Tables: ALTER and UPDATE

  ALTER TABLE Employees
  ADD Department VARCHAR(50);
  

  ALTER TABLE Employees
  ALTER COLUMN Salary DECIMAL(12, 2);
  

  ALTER TABLE Employees
  DROP COLUMN DateOfBirth;
  

Retrieving Data from SQL Tables: SELECT and JOIN

 SELECT EmployeeID, FirstName, LastName, Salary 
FROM Employees 
ORDER BY LastName; 

SELECT E.EmployeeID, E.FirstName, E.LastName, E.Salary, D.DepartmentName 
FROM Employees E 
INNER JOIN Departments D 
ON E.DepartmentID = D.DepartmentID 
ORDER BY D.DepartmentName, E.LastName; 

SQL Table Variables: Temporary Data Storage

Defining and Using SQL Table Variables

SQL table variables are declared using the DECLARE statement along with the table structure and are populated with data by using INSERT statements. They provide a flexible means of storing intermediate results for complex data manipulation tasks and data summarisation processes. Here's an example of creating a table variable and populating it with data:

DECLARE @EmployeeSales TABLE ( EmployeeID INT, LastName VARCHAR(50), SalesAmount DECIMAL(10, 2) ); 
INSERT INTO @EmployeeSales (EmployeeID, LastName, SalesAmount) 
SELECT EmployeeID, LastName, SUM(SalesAmount) 
FROM Sales GROUP BY EmployeeID, LastName;

In this example, a table variable named @EmployeeSales is declared with the columns EmployeeID, LastName, and SalesAmount. The INSERT statement then populates the table variable with aggregated sales data for each employee.

Table variables have certain advantages compared to their temporary table counterparts, including:

• Transaction log usage: Table variable operations are logged less extensively, resulting in less transaction log space utilisation and, consequently, improved performance.
• Scope and lifetime: Table variables have a well-defined scope and are automatically cleaned up once they go out of scope, which simplifies memory management.
• Recompilations: Table variable modifications do not cause recompilation of the stored procedure or query containing them, leading to more predictable query performance.

Comparing Temporary Tables and Table Variables

• Scope and Lifetime: Temporary tables have a broader scope and exist until the current user session ends or until they are explicitly dropped. Table variables have a local scope, confined to the context in which they were defined, and are automatically cleaned up when they go out of scope.
• Performance: Table variables generally have better performance due to less logging and no recompilation on modification. However, this advantage might become insignificant for large data sets or complex operations with multiple modifications.
• Indexes: Temporary tables support the creation of indexes, while table variables only allow the creation of primary key and unique constraints in their declaration.
• Statistics: SQL Server maintains statistics for temporary tables, which can lead to better query optimisation, whereas table variables do not have statistics.
• Parallelism: Temporary tables allow parallel execution plans, while table variables are limited to serial execution plans.
• Constraints: Temporary tables support adding constraints, while table variables only support PRIMARY KEY and UNIQUE constraints at the time of declaration.
• Transaction Safety: Temporary table modifications are part of transactions and can be rolled back, while table variable operations are not subject to transaction rollbacks.

SQL Table vs View: Understanding the Differences

Advantages of SQL Views over SQL Tables

• Data Security: Views can limit access to a subset of records or columns, providing an extra layer of data protection and access control. For instance, a view can hide sensitive columns, like salary or social security number, while still displaying relevant information to users.
• Abstraction and Simplification: Views can simplify complex queries involving multiple tables and joins, providing a user-friendly interface for accessing and manipulating related data. Users can interact with the view as if it were a single table without needing to understand the underlying table structure or relationships.
• Data Aggregation: Views can store aggregated data, making it easier to work with summary information, retrieve pre-calculated results, and perform reporting or analysis tasks.
• Consistent Data Representation: Views can maintain a consistent data representation in the face of schema or table changes. Applications or queries relying on a view will require minimal updates, if any, when the underlying tables are modified.
• Query Optimisation: Views can optimise query performance by storing precomputed query results or applying specific filters, reducing data retrieval overhead and improving response times.

Use Cases for SQL Tables and Views

• Storing customer information in an e-commerce platform.
• Maintaining a list of products and their stock levels in an inventory management system.
• Logging user activities in a web application.

• Restricting access to confidential data, like salaries or personal emails, by creating views that exclude such columns.
• Creating a view that combines customer and order information to facilitate reporting or analysis tasks in an e-commerce platform.
• Defining views with pre-calculated metrics, such as total sales per product, to speed up data retrieval in an inventory management system.