SQL Tutorial - Clustered Indexes OLTP

Опубликовано: 10 Сентябрь 2018
на канале: BeardedDev

433

Another video brought to you by BeardedDev, bringing you tutorials on Business Intelligence, SQL Programming and Data Analysis.

You can now support me on patreon - / beardeddev

In this video tutorial we talk all about Clustered Indexes in SQL Server. Indexes allow data to be retrieved quickly but we have to respect a proper indexing strategy as too many indexes can cause DML operations to be come slow. Working with indexes is all about finding the balance between read and write operations on the database.

Code featured in the video to allow you to follow along:
-- Add Table
IF OBJECT_ID(N'dbo.Employees', N'U') IS NOT NULL
DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees
(
EmployeeId INT IDENTITY(1, 1) NOT NULL
, EmployeeFirstName VARCHAR(50) NOT NULL
, EmployeeLastName VARCHAR(50) NOT NULL
, EmployeeDateOfBirth DATE NOT NULL
, EmployeePosition VARCHAR(50) NOT NULL
, EmployeeStartDate DATE NOT NULL
CONSTRAINT DF_EmployeesNew_EmployeeStartDate DEFAULT (GETDATE())
, EmployeeEndDate DATE NULL
, EmployeePayrollId INT NOT NULL
);

-- Add Data
INSERT INTO dbo.Employees (EmployeeFirstName, EmployeeLastName, EmployeeDateOfBirth, EmployeePosition, EmployeeStartDate, EmployeeEndDate, EmployeePayrollId)
VALUES
('Tony', 'Smith', '19860415', 'Sales Executive', '20180101', NULL, 123456)
, ('Robin', 'Jones', '19860415', 'Sales Manager', '20180101', NULL, 123457)
, ('Michelle', 'Parker', '19860415', 'Sales Executive', '20180101', NULL, 123458)
, ('Alex', 'Weaver', '19860415', 'Sales Executive', '20180101', NULL, 123459)
, ('Paul', 'Attenborough', '19860415', 'Sales Assistant', '20180101', NULL, 123460)

-- Retrieve Data
SELECT * FROM dbo.Employees
WHERE EmployeeId = 3;

-- ADD CLUSTERED INDEX TO EXISTING TABLE
ALTER TABLE dbo.Employees
ADD CONSTRAINT PK_Employee_EmployeeID PRIMARY KEY (EmployeeId);

-- DOES THE TABLE HAVE AN INDEX?
SELECT
name
, syspart.index_id
FROM sys.tables AS systab
INNER JOIN sys.partitions AS syspart
ON systab.[object_id] = syspart.[object_id]
WHERE systab.name = 'Employees';

-- WHAT TYPE OF INDEX?
SELECT
name
, [type]
, type_desc
FROM sys.indexes
WHERE name = 'PK_Employees_EmployeeId'