Sql Advanced

Advanced sql

1. Indexes and views

Common Data Structures

a wooden or bamboo pen for sheep or cattle: First in, last out
formation: First-in, first-out (FIFO)
arrays; fast querying, based on subscript querying
linked list: There are double and single linked tables. A single linked table points to the storage location of the next data; a double linked table points to the storage location (reference address) of the previous and next data.
binary tree: Small left, large right
balanced binary tree: An optimized version of a binary tree to make the tree as low in degree as possible and improve the finding efficiency. Requires a height (depth difference of no more than 1).levitra: The node being rotated rises from the left to the parent.right-hand side: The node being rotated rises from the right to the parent node. The problem of too many rotations occurs.
red and black trees: is a self-balancing binary lookup tree. There will betoo highThe problem. The root node must be black; there cannot be two red nodes connected; the left and right sides contain the same number of black nodes
B-tree: There are many orders. All leaf nodes are on the same level. Solves the depth problem, doesn't solve the interval lookup problem.
B+ Tree: Leaf nodes are connected by pointers and keywords are sorted by size. The problem of interval lookup is solved.

indexing

corresponds English -ity, -ism, -ization

The speed of the query can be improved. Why? It is implemented using the b+tree data structure, where only leaf nodes need to be traversed to traverse the entire tree.

define

A portion of structured information is extracted and reorganized so that it becomes somewhat structured, and we call this portion of information an index. (automatically generated outline, secondary information)

categorization

Aggregated Index: An index whose logical order determines its physical order so that physical addresses are stored consecutively. Only one aggregated index can exist in a table, usually the primary key.

Under what circumstances is an aggregated index not a primary key? When you build a table, you don't add a primary key, then you add an index, and then you add the primary key later, in which case the index is not the primary key.

Non-aggregated indexes: indexes in which the logical and physical order of the records in the table are different. There can be more than one.

Each index can contain up to 16 columns of data. The maximum index key record size is 900 bytes.

Design Principles

Is it better to have more indexes? No (2-3 indexes in a table is recommended)

Indexes also require space, and the more indexes there are, the more space they take up.
The index also needs to be reorganized when adding, deleting, or changing data. Changes in the content of the book are reorganized. (Increased maintenance costs)
The maintenance process creates too much index fragmentation (indexes don't point to data anymore, indexes are lost), which in turn is detrimental to queries.

Scenarios for indexing (following principles, data at least 200,000)

The primary key must be built (the system builds it automatically)
Foreign keys must be (automatically created by the system)
Frequently queried columns
Columns often used as query conditions
Columns often used after order by,group by,distinct
Columns with more duplicate values cannot be indexed
Fields of type text, image, bit cannot be indexed.
Do not index frequently accessed columns

grammatical

CREATE INDEX index_name ON table_name (column_name1,column_name2...) --up to 16 columns
CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME()--Create a unique index on the column that does not allow duplicates.
EXEC SP_HELPINDEX 'TABLE_NAME' -- query the index
EXEC SP_RENAME 'TABLE_NAME.NAME','NEWNAME','INDEX' -- change the index name
DROP INDEX INDEX_NAME ON TABLE_NAME--delete the index
ALTER INDEX INDEX_NAME ON TABLE_NAME REBUILD--Rebuild the index, organize the index fragmentation, rebuild the table is locked, the request about the table will be blocked, generally very little operation.

view

Role: improve security, simplify the query process

is a virtual table object that simplifies the query process and improves the data security of the database. Essentially it is a bunch of encapsulated sql.

CREATE VIEW VIEW_NAME AS ... .SQL query statement
CREATE VIEW VIEW_ALLCITY
 AS --Creating a view
SELECT ,,
FROM City A

JOIN Province B
ON =
ALTER VIEW VIEW_NAME AS ... ...SQL query statement - modify view
DROP VIEW VIEW_ALLCITY -- Delete View
ALTER VIEW VIEW_NAME
 WITH ENCRYPTION
 AS ... SQL Query Statement - Encrypting a View

Note: Save the code for creating views, especially encrypted ones, for easy modification.

Transactions and Locks

Transactions: Used to handle business failures for rollback.

--Exception Case
BEGIN TRANSACTION -- Abbreviated TRAN
BEGIN TRY
--Transfer 500 from Zhang San to Li Si
UPDATE account SET balance=balance-200 WHERE NAME='ZhangSan';
UPDATE account SET balance=balance+'' WHERE NAME='Li Si';
COMMIT
END TRY
BEGIN CATCH
ROLLBACK--Rollback
END CATCH

Principles of transactions

After the transaction is opened, all the operations performed will be stored in the transaction log first, and will be updated to the database only when COMMIT is encountered, otherwise the transaction log will be cleared, and the link will be broken when ROLLBACK is encountered.

Steps in the execution of a transaction

The client connects to the database server and creates a temporary log file for this user when the connection is created.
When a transaction is turned on, all operations are first written to a temporary log file.
All query operations are queried from the table, but are processed by the log file before being returned
Write the data in the log file to the table if the transaction commits, otherwise, empty the log file.

The Four Characteristics of a Transaction (ACID)

Atoc (atomicity): the operations contained in a transaction are viewed as a logical unit, and the operations in this logical unit either all succeed or all fail, ensuring data integrity.
Consistency (consistency): transaction completion, the data must be in a consistent state, the integrity of the data constraints have not been destroyed, the transaction in the execution process of the error will be rolled back Rollback) to the state before the start of the transaction, as if the transaction has never been executed.

For example, the total amount for 2 people before the transfer is 2000, and the total amount for 2 people after the transfer is also 2000.
Isolation: Transactions allow concurrent access to the same data by multiple users without jeopardizing the correctness and integrity of the data. At the same time, the modification of parallel transactions must be independent of the modification of other parallel transactions.
Durability (persistence): the end of the transaction, the results of transaction processing must be able to get solid (permanently stored in the database). Even if the power is off, it is also saved.

Isolation of transactions

During transaction execution, the operations of multiple concurrent transactions are completely independent and do not affect each other.

Concurrent access issues	hidden meaning
dirty reading	Reads data that has not yet been committed by another transaction
fantasy reading	A transaction where two concurrent transactions do not read the same amount of data
non-repeatable	In one transaction, two concurrent transactions read data with different contents.

The four isolation levels of a database

(military) rank	name (of a thing)	Isolation level	dirty reading	non-repeatable	fantasy reading
1	Read unsubmitted	read uncommitted	be	be	be
2	Read submitted	read committed	clogged	be	be
3	repeatable	repeatable read	clogged	clogged	be
4	serialize	serializable	clogged	clogged	clogged
2-3	snapshotting	SNAPSHOT	clogged	clogged	clogged

grammatical

SET TRAN ISOLATION LEVEL read uncommitted-Sets the number transaction isolation level

The use of snapshots begins with switching the database to a state that supports snapshots.

padlock

Multi-user access to the same data resource, the order of access rights management mechanism.

Classification of locks

shared lock
exclusive lock
update lock
Architecture locks
intentional lock
Large capacity update lock

Pessimistic locks: always assume the worst state every time, all assuming that someone else will be manipulating the data when it is manipulated. Examples include row locks, table locks, shared locks, etc.

Optimistic locking: Each operation on the data always assumes that the data will not be modified by other operations before it is operated on, but the data will be judged before it is returned. Optimistic locks are suitable for data types with multiple reads and can improve throughput. Snapshots are optimistic locks.

ROWLOCK

SELECT * FROM TABLEN_NAME ROWLOCK

Table Lock (TABLELOCKX)

SELECT * FROM TABLEN_NAME TABLELOCKX

Shared Lock (WITH(HOLDLOCK))

Used for reading operations, allowing multiple transactions to read data at the same time, at this time do not allow things to modify the data, but the modification request will be carried out after the completion of the read.

--Window 1
BEGIN TRAN
SELECT * FROM [dbo]. [account] A WITH(HOLDLOCK) WHERE =1
WAITFOR DELAY '00:00:10'
COMMIT
--Window 2
BEGIN TRAN
SELECT * FROM [dbo]. [account] A WHERE =1
COMMIT
--Window 3
BEGIN TRAN
UPDATE [dbo]. [account] SET [balance]=1000
COMMIT

Exclusion Lock (WITH(UPDlOCK))

The locks are rows, which are used for the transaction's add, delete, and modify operations.

--Window 1
BEGIN TRAN
UPDATE [dbo]. [account] SET [balance]=100 WHERE Id=2 --Updates are automatically transferred to exclusive locks
--Equivalent to UPDATE [dbo]. [account] WITH(UPDlOCK) SET [balance]=100 WHERE Id=2
WAITFOR DELAY '00:00:10'
COMMIT
--Window 2
BEGIN TRAN
SELECT * FROM [dbo]. [account] A WHERE =2
COMMIT
--Window 3
BEGIN TRAN
SELECT * FROM [dbo]. [account] A WHERE =1
COMMIT

deadlock

During multitasking, each task locks the resources required by the other tasks, thus causing the tasks to block permanently, resulting in a deadlock, at which point the system is in a deadlock state. a locks the resources required by b, and b locks the resources required by a.

--Window 1
BEGIN TRAN
UPDATE [dbo]. [account] SET [balance]=1000 WHERE Id=2
WAITFOR DELAY '00:00:10'
UPDATE [dbo]. [account] SET [balance]=1000 WHERE Id=1
COMMIT
--Window 2
BEGIN TRAN
UPDATE [dbo]. [account] SET [balance]=1000 WHERE Id=1
WAITFOR DELAY '00:00:08'
UPDATE [dbo]. [account] SET [balance]=1000 WHERE Id=2
COMMIT

How to reduce deadlocks?

Use resources in the same order in all transactions
Keep transactions as short as possible and in a batch.
Avoiding interaction with users within a transaction reduces resource lockup time
Set a reasonable range for the deadlock timeout parameter

The relationship between locks and transactions

Transactions are not the same as locks.

Transactions have ACID (Atomicity, Consistency, Isolation, and Extra Duration), and locks are a mechanism used to address isolation.
The isolation level of a transaction is achieved through a locking mechanism.
In addition, locks have a different level of granularity, while the transaction is also a different isolation level (generally there are four: read uncommitted Read uncommitted, read committed Read committed, Repeatable read Repeatable read, Serializable Serializable).

-SQL Programming

naming convention

Cannot start with numbers, spaces or special characters
The first character can contain letters, numbers, Chinese characters, _@#
Cannot be a system keyword

variant

global variable

@@ERROR Returns the error number of the last T-SQL statement executed -- 0 means there was no error, non-0 means there was an error
@@IDENTITY Returns the last value of the IDENTITY column inserted into the current table.
@@LANGUAGE Get the name of the language used by the current computer system.
@@MAX_CONNECTIONS The maximum number of connections supported by the current database.
@@ROWCOUNT Returns the number of rows affected by the last sql statement.
@@SERVERNAME Returns the name of the server.
@@TIMETICKS Returns the number of microseconds.
@@TRANCOUNT Returns the number of transactions currently running in the database.
@@VERSION Returns the version of the database.

local variable

Local variables can only begin with the @ character

--DECLARE @variable name variable type = variable content
declare @name nvarchar(20) = 'ytt'

variable assignment

declare @name nvarchar(20) ='ytt'--Initialization Assignment
set @name='ytt1'--setassign a value to something
select @name='ytt2'-selectassign a value to something

logic code

declare @age int;
select @age=Age from [dbo].[PersonInfo] where AutoId=5
if(@age<=10)
begin
print('violence')
end
else if(@age<=40)
begin
print('youthful years')
end
else
begin
print('autumn of one's years')
end

customizable function

create function function name(join) returns become involved (in a dispute)
begin
end

4. Trigger

Classification: After-the-fact triggers (AFTER), alternative triggers (INSTED OF)

--DECLARE @Variable Name Variable Type = Variable Content
CREATE TRIGGER Trigger name ON Table name
{AFTER|INSTEAD OF}[operation]
AS
BEGIN
--T-SQL Statement
END