SQL Serever Learning 16--indexes, triggers, database maintenance

SQLSERVER2014 database application Technology "Tsinghua University Press" index

This is a very important concept, we know that the data in the computer is actually paged storage, as if the word exists in the dictionary

Database indexing helps us quickly locate where the data is stored in the page area without scanning the entire database

Once the index is created, the database will be automatically managed and maintained, and additions and deletions to the socket database will modify the index.

Index Classification:

• Clustered index
• Nonclustered indexes
• Include Index of the column
• Indexed views
• Full-Text Indexing
• XML index

A clustered index, which is the equivalent of a sorted dictionary (the data in the table is completely reordered), only one table, occupying space equivalent to 120% of the data in the table, the data to establish a clustered index, will change the storage physical structure of the data row

Nonclustered index, do not change the physical storage structure of the data row, create index by default to establish a nonclustered index, the theory that a table can have 249 nonclustered indexes

Indexes and constraints

Setting the primary key automatically creates a primary key and creates a clustered index

Creating a unique constraint automatically creates a unique nonclustered index

To create an index of a table

Using SQL statements

CREATE INDEX ix_name_mjon Buyer form (buyer name) GO

View Index

EXEC Sp_helpindex Buyers Table

Analysis Index

View the query plan, using the index (use clustered index preference)

SET showplan_all Ongoselect * from buyer table Goset Showplan_all offgo

Display statistics to see the amount of disk IO activity spent

SET STATISTICS io ongoselect * from buyer table Goset STATISTICS IO offgo

Maintain indexes

Adding and deleting data tables results in a large number of index fragments, discontinuous index tables, reduced index performance, and the need to collate indexes

View index Fragmentation SQL

DBCC Showcontig (Buyers table, pk_ buyers table) GO

SSMS View Index

Index defragmentation

DBCC Indexdefrag (Sales management, Buyer's form, pk_ Buyer's table)

Trigger

Trigger is an advanced data constraint, he is a special stored procedure, can not be triggered by the execution of SQL, by adding and deleting events automatically triggered

SQLSERVER2014 offers 3 types of triggers:

• DML triggers, including post-event triggers, alternative triggers, CLR runtime triggers
• DDL triggers, modifying table structure triggering
• Login trigger, triggered when logged in

DML triggers

Insert Trigger

If the employee is under the age of 18, do not insert the operation

CREATE TRIGGER Employee_inserton employeeafter insertasbegin--from inserted table get new insertion employee's birth date declare @birthday dateselect @ Birthday=birthday from inserted--determine the age of the new employee if (year (GETDATE ())-year (@birthday) <18) BeginPrint ' The employee is under the age of 18 and cannot enter the job! ' ROLLBACK TRANSACTION  --roll back all operations before this node and proceed to the following statement EndEnd

Verify

INSERT Employee VALUES (' xiaoming ', ' 2012-10-10 ')

Re-verify

INSERT Employee VALUES (' xiaoming ', ' 1912-10-10 ')

Note that the primary key ID will still grow, even if the operation has just been rolled back, the ID has increased by 1

UPDATE trigger

Prevent users from modifying the Employee Name Name field

CREATE TRIGGER employee_updateon employeeafter updateasbeginif (UPDATE name) BeginPrint ' prohibit modification of employee names! ' ROLLBACK TRANSACTION  --roll back all operations before this node and proceed to the following statement EndEnd

Verify

UPDATE Employee SET name= ' XX '

Maintenance backup of the database

Using stored procedures to create a backup device

EXEC sp_addumpdevice ' DISK ', ' comb ', ' E ' \data\comb. BAK '

Deleting a backup device

EXEC sp_dropdevice ' comb '

To create a database backup using SQL

BACKUP DATABASE Sales Management to Comb

Restoring a database using SQL

RESTORE DATABASE combfrom disk= ' E:/data/comb. BAK '

SQL Serever Learning 16--indexes, triggers, database maintenance