Optimizing the MySchool database (transactions, views, indexes)

Java Training,Android training,IOS Training,. Net Training , look forward to communicating with you!

Transactions, views, indexes:

Transactions: When the "one step" in the logic of life requires multiple SQL to complete, the transaction must be used to ensure its "integrity".

View: simplifies the database structure so you write SQL statements (simplifying the writing of SQL statements)

Index: Improve the efficiency of data retrieval

Transaction:

----used to ensure synchronous execution of multiple SQL statements (either all executed together successfully, or all rolled back does not execute)

----Syntax:

Begin Transaction (START transaction)

{More than one SQL statement}

Commit Transaction (Prompt transaction)

Rollback Transaction (ROLLBACK TRANSACTION)

----Actual code:

DECLARE @errorSum int

Set @errorSum = 0

Begin Transaction

Insert INTO ..... (SQL statement)

Set @errorSum = @errorSum + @ @ERROR

Insert INTO ..... (SQL statement)

Set @errorSum = @errorSum + @ @ERROR

......

If @errorSum = 0

Commit TRANSACTION

Else

ROLLBACK TRANSACTION

Transaction Features:

------Atomicity, consistency, isolation, persistence

Atomicity: The whole transaction, no matter how many SQL statements it contains, is a whole! Either commit, or roll back;

Consistency: Before and after transaction execution, the entire database satisfies various constraints

Isolation: Multiple transactions operate on the same data object, and each other's "execution process" and "effect" are isolated from each other.

Persistence: Although the impact on the database is uncertain (commit, rollback) during transaction execution, the impact on the database will persist once the transaction has ended (commit, rollback).

Transaction classification:

------Explicit transactions, implicit transactions, auto-commit transactions

Explicit transactions: Start with BEGIN TRANSACTION, rollback \COMMIT end of transaction

Implicit transaction: Opens a transaction as a "switch" (on), and automatically starts a new transaction when the transaction ends, until the switch is off (off)

Autocommit transactions: The default transaction mode for SQL SERVER, with each SQL being a separate transaction

View:

----When we do database design, in order to reduce the data redundancy, often the life itself is associated with the data, split into different forms! As a result, SQL code is complex and inefficient when accessing data (multi-table). You can pre-store the "Multi-table" SQL statement in the database, from a reduction in each use, the trouble of rewriting!

-----View: Is a "SELECT statement" that is saved in the database. The use of a view is similar to "table", but the data is stored directly in the table, but the data is not stored in the view, and the data is still stored in its corresponding underlying table.

-----The SELECT statement in the view, note when writing:

• Order by (unless top is included)
• Into
• Table variable (temporary table)

Scenarios for------Views:

• Simplifies the structure of the backend database and facilitates writing data access code to simplify SQL (multiple physical databases as a logical database)
• Protect "sensitive data" to prevent unauthorized access! (Filter rows and columns by its select)

Index:

-------sort a column in a datasheet so that its query on that column can be done later on the results of the "sorted" result, which improves the efficiency of the data retrieval (the process of sorting can "nonclustered index" in the new Index table, or "clustered index" in the original form)

--------Create Syntax:

if exists (SELECT * from sysindexes WHERE name = ' Index name ')

Drop Index Table name. Index Name

Go

Create index index name on table name (column name)

Go

Index classification:

• A unique index----all index entries in the Index table and does not allow duplicates
• Primary KEY index----Create an index table based on the primary key column of the form
• Nonclustered indexes-----Copy the search columns from the form to the index table and sort. (Data tables and index tables exist separately)
• Clustered index----on the original physical form, sort the search columns directly. (Data table and Index table are merged)

Attention:

1, a form can only create one "clustered index", but can create multiple "nonclustered indexes"

2. The form will automatically create a primary key index and a clustered index on the form when the primary key is created

Advantages and disadvantages of the index:

Benefit: Improve data retrieval efficiency

Cons: Increases the system's data storage overhead and reduces efficiency when data changes

Application scenarios for the index:

Application: Frequent search (where), frequently queried (select), sort, associated query columns (primary foreign key column)

Not applicable: Fewer rows, less data changes in columns

Tips:

            & nbsp;         & nbsp            

system table name &NB sp;	Database	Stored system Information
sysdatabases	master	Database information
sysobjects	User database	Tables, views, stored procedures
sysindexes	& nbsp User database	Index

Select ....

into new table name

From ....

-----function, save the query results to a new data form.

Optimizing the MySchool database (transactions, views, indexes)