SQL Server things, indexes, views

Transaction concepts:
A transaction is a mechanism that contains a set of database operations commands, and submits or revokes all commands as a whole to the database.
This set of commands is either executed or not executed, so the transaction is an inseparable logical unit of work.

Features of the transaction:
1, atomicity: The transaction as a complete operation, its elements are indivisible, either all execute, or undo all operations, the unit of the transaction can not be partial success of the situation.
2, consistency: Before and after the completion of the transaction, the data must be maintained in a fully consistent state. That is, the data stored in the database must be in a consistent state after the transaction starts or completes.
3. Isolation: Transactions are relatively independent, and when a transaction modifies the data, other transactions become impossible to modify.
4, Persistence: After the completion of the transaction, its impact on the system is permanent. That is, if a transaction succeeds, the DBMS guarantees that its changes to the data in the database are permanent and unaffected by any system failure.

In T-SQL, you always use the following syntax to manage transactions:
1. Start a transaction: Begin TRANSACTION
2, Commit the thing: Commit TRANSACTION
3. ROLLBACK TRANSACTION: ROLLBACK TRANSACTION

Indexes can be divided into 3 categories:
1. Unique index: Two rows of the same index value are not allowed, so a unique index is typically created on a primary key or column that creates a unique constraint. When a unique constraint is created on a column,
A unique index is automatically created in this column.
2. Clustered index: The clustered index arranges and stores the data rows in a table or view based on the key values of the data rows, that is, the rows in the table are in the same physical order as the index order. Each table can have only one
Clustered indexes, because the data rows themselves can only be sorted in one order. If the table has a clustered index, the table is called a clustered table, and the rows of data in the table are sorted in indexed order if
Without a clustered index, its data rows are stored in an unordered structure called a heap.
3. Nonclustered indexes: Nonclustered indexes have a structure that is independent of the data rows. Nonclustered indexes contain nonclustered index key values, and each key value has a pointer to the data row that contains the key value.

Creating an index using T-SQL
Grammar:
CREATE [UNIQUE] [clustered| Noclustered] INDEX IndexName on TableName
(Field name)
[With Fillfactor=x]
Syntax Description: unique specifies that unique indexes are created, options clustered, noclustered Specify whether to be clustered or nonclustered, option IndexName represents the index name,
The naming convention for indexes is "IX_ table name _ Column name". FILLFACTOR represents a fill factor that sets the percentage of space the index page data fills, that is, the remaining space for each index page. The specified range is
0~100, if the value is 70%, then there is still 30% of the space for later expansion use. 0 equals 100, which means that the page level is almost filled, but leaves some room for at least one index row to be added.
Examples are as follows:
Use Bank
GO
If exists (select name from sysindexes where name= ' Index_card_no ')
BEGIN
Drop INDEX All_purpose_card.index_card_no
END
Create nonclustered index index_card_no on All_purpose_card
(Card_no) with fillfactor=30
GO
As the previous example uses the EXISTS subquery to check for the existence of an index named Index_card_no from System View Sysindex. If present, the DROP INDEX statement is used to delete, and then the table All_purpose_card
Create a nonclustered index on the card_no column. In general, after you create an index, you do not need to display the query at query time, because the database engine automatically optimizes the query.
To query using an index:
SELECT * from All_purpose_card with (index=index_card_no) where last_used_date between ' 2011-07-01 ' and ' 2012-12-30 '
If there are multiple indexes, you can use the WITH (index= index name) to display the specified index for querying
* Reminder: Using an index can speed up data retrieval, but it is not necessary to create an index for each column because the index itself needs to be maintained
You can choose to create an index based on the following criteria: 1, the column is used for frequent searches, 2, the column is used to sort the data, 3, there are fewer duplicate values in the column.
The following columns are not suitable for indexing: 1, the number of duplicate values in the column, 2, the low data in the index (indexing for small tables is not necessary, because retrieving the index may take longer than retrieving the index); 3. Frequent
Insert the column of the operation (because each time new data is added, the index needs to be re-maintained).

Views: A view is a virtual table whose contents are defined by a query.
Create a view using T-SQL statements
Syntax: Creeate VIEW viewname as <select statement >
Example:
Use Bank
GO
if exists (select * from sysobjects where name= ' View_credit_detail ')
Drop View_credit_detail
GO
CREATE VIEW View_credit_detail
As SELECT account. Account_name, Credit_card. Credit_card_no, EXCHANGE. Loan,exchange. Extime,exchange. Exaddress,exchange. Pay_month
From account INNER JOIN Credit_card
On account. Account_id=credit_card. account_id INNER JOIN EXCHANGE
On Credit_card. Credit_card_id=exchange. credit_card_id
GO
The above code indicates that if the view object to be created already exists in System view Sysobject,
You need to delete and then create.

SQL Server things, indexes, views