SQL Server transactions, indexes, views, SQL Server

SQL Server transactions, indexes, views, SQL Server

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

Features of transactions:
1. atomicity: As an integrity operation, each element of the transaction is inseparable. It is impossible for the transaction unit to partially succeed, either to execute all or to cancel all operations.
2. Consistency: data must be completely consistent before and after the transaction is completed. That is, after the transaction starts or completes, the data stored in the database must be consistent.
3. Isolation: transactions are relatively independent. When a transaction modifies data, other transactions cannot be modified.
4. Durability: after a transaction is completed, its impact on the system is permanent. That is, if a transaction is committed successfully, the DBMS ensures that its changes to the data in the database are permanent and will not be affected by any system failure.

Transactions are always managed using the following syntax in the T-SQL:
1. Start TRANSACTION: BEGIN TRANSACTION
2. Submit TRANSACTION: COMMIT TRANSACTION
3. rollback transaction: ROLLBACK TRANSACTION

There are three types of indexes:
1. Unique index: two rows of the same index value are not allowed. Therefore, a unique index is generally created on a primary key or a column with a unique constraint. After a unique constraint is created on the column,
A unique index is automatically created for this column.
2. Clustered index: the clustered index sorts and stores the data rows in the table or view based on the key values of the data rows. That is, the physical order of the rows in the table is the same as that of the index. Each table can have only one
Clustered index, because data rows can only be sorted in one order. If a table has a clustered index, the table is called a clustered table. The data rows in the table are sorted by index order.
If no clustered index exists, its data rows are stored in a unordered structure called a heap.
3. Non-clustered indexes: Non-clustered indexes have a structure independent of data rows. A non-clustered index contains a non-clustered index key value, and each key value has a pointer to a data row containing the key value.

Creating an index using a T-SQL
Syntax:
CREATE [UNIQUE] [CLUSTERED | NOCLUSTERED] INDEX indexname ON tablename
(Field name)
[With fillfactor = x]
Syntax description: UNIQUE indicates that the created index is a UNIQUE index. You can select CLUSTERED or NOCLUSTERED to specify whether the index is CLUSTERED or non-CLUSTERED. You can select indexname to indicate the index name,
The index naming convention is "ix _ TABLE name_column name ". FILLFACTOR indicates the fill factor, which is used to set the percentage of space filled by the index page data, that is, the remaining space of each index page. The specified range is
0 ~ 100. If the value is 70%, there is still 30% space for future extension. 0 is equivalent to 100, indicating that the page level is almost full, but some space is provided, at least one index row can be added.
Example:
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
The above example uses the exists subquery to check whether the index named index_card_no exists from the System View sysindex. If yes, use the drop index statement to delete the table, and then use
Create a non-clustered index on the CARD_NO column. Generally, after creating an index, you do not need to specify the index to be queried during the query, because the database engine automatically optimizes the query.
Query using indexes:
Select * from ALL_PURPOSE_CARD with (index = index_card_no) where LAST_USED_DATE between '2017-07-01 'and '2017-12-30'
If multiple indexes exist, you can use WITH (INDEX = INDEX name) to display the specified INDEX for query.
* Note: Using indexes can accelerate data retrieval, but there is no need to create an index for each column, because the index itself also needs to be maintained.
You can create an index based on the following criteria: 1. This column is used for frequent search; 2. This column is used to sort data; 3. There are few duplicate values in this column.
The following columns are not suitable for indexing: 1. There are many duplicate values in the column; 2. There is less data in the tag (it is not necessary to create an index for a small table, because the retrieval index time may be longer than the retrieval index time); 3. Frequent Retrieval
Insert operation column (because the index needs to be re-maintained every time new data is added ).

View: A view is a virtual table whose content is defined by the 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 code above indicates that if the view object to be created already exists in the system view sysobject,
You need to delete the file before creating it.

 

What is the use of the index and view in SQL server 2005?

Indexes are used to improve data query performance. Views are generally used to provide users with a unified interface to shield useless or confidential information.
Generally, if the data volume is not too large, for example, there are only tens of thousands of records, and no index is required, the index itself is also a type of data. In order to maintain the index, the system requires overhead. If the data volume is very large, the indexing technology will be used. For example, millions or tens of millions of data records. In this case, if there is no index on the data table, the data query will be very slow. An index is used to sort data in advance. For example, if the ID card number is indexed in this field, it is sorted from large to small. You need to query a number, the system can quickly retrieve the corresponding data.
In databases, not all users need all information. In some cases, some sensitive information can only be given to people with proper permissions, even if the user enters the database (instead of the program interface retrieval, but directly enters the database management system), he cannot view all the information. In this case, the view method can be used, this allows users to view only the information they should read, and the real basic table is blocked.

What are the differences between SQL server 2005 indexes and views?

Indexing principles in Databases
In fact, you can think of indexes as a special directory. Microsoft SQL SERVER provides two types of indexes:

Clustered index (also known as clustering index and cluster index) and non-clustered index (nonclustered index, also known as non-clustering index and non-cluster index ). Below, we

For example, the difference between clustered index and non-clustered index is as follows:

In fact, the body of our Chinese dictionary is a clustered index. For example, if we want to check the word "an", we will naturally open the first few pages of the dictionary, because the Pinyin of "an" is ""

", And the dictionary of Chinese characters sorted by pinyin starts with the English letter" a "and ends with" z ", then the word" an "is naturally placed at the front of the dictionary. If you have finished all"

If you still cannot find this word in the start part, it means that your dictionary does not have this word. Similarly, if you query the word "Zhang, then you will turn your dictionary to the last part, because

"The Pinyin is" zhang ". That is to say, the body of the dictionary itself is a directory, and you do not need to query other directories to find the content you need.

We refer to this text content as a directory arranged according to certain rules as "clustered Index ".

If you know a word, you can quickly find it automatically. However, you may also encounter a word you do not know and do not know its pronunciation. At this time, you cannot follow

Find the words you want to query, and you need to find the words you want according to the "radicals, then, based on the page number after the word, go to a page to find the word you are looking. However

The words in the Directory and the word checklist are not really sorted by text. For example, you can check the word "Zhang, we can see the "Zhang" page in the Word Table after the department head is checked.

The code is 672 pages. In the word table, "Zhang" is preceded by "Chi", but the page number is 63 pages. "Zhang" is followed by "" and the page is 390 pages. Obviously, these words are not true points.

Do not place on the top or bottom of the word "Zhang". Now, the continuous "Chi, Zhang, and crossbow" words you see are actually their sorting in non-clustered indexes, it is a non-clustered index of words in the dictionary body.

. We can find the words you need in this way, but it requires two steps: first find the results in the directory, and then flip to the page number you need.

We refer to this directory as a directory, and the text as a non-clustered index ".

Through the above example, we can understand what is "clustered index" and "non-clustered index ".

Further, we can easily understand that each table can only have one clustered index, because the directory can only be sorted in one way.

The benefits of a view can be divided into four main points:
I want you to understand it.

First:
You can use views to customize user data and focus on specific data.

Explanation:
In the actual process, the company has different roles of staff, we take the sales company as an example,
The purchaser may need data that is not relevant to him.
In any sense, we can create a view for the purchaser based on the actual situation.
Figure. When you query data in the future, you only need to select * from view_caigou.

Second, use a view to simplify data operations.

Explanation: When we use a query, we usually need to use an aggregate function.
Displays information about other fields and may need to be associated with other tables. The written statement may
It will take a long time. If this action occurs frequently, we can create a view.
You only need to select * from view1 ~, Is it convenient ~

Third: using a view, the data in the base table is secure.

Because the view is virtual and does not exist physically, it only stores a set of data. We can
... The remaining full text>