T-SQL things, views, and indexes

Things, views, and indexes

Skill Mastery Goal:

1. using things to ensure the integrity of operations

2. Learn how to create a view

3. Learn how to create and use an index

The first thing we want to talk about is things, then the question comes, what do you mean, what role does he have?

What are some of the great benefits? What the difference is.

The answer: Things are the process of treating many things as one thing. That is, everyone in a boat, to live together live, to over together over! , which guarantees the consistency and completeness of things.

Its role is to ensure the consistency of things, persistence, atomicity, and isolation.

1. Consistency: When we need to update more than one piece of data, to ensure that they are all proud, a loss.

Good for example, there are lang a grasshopper in the same boat, if the boat leaks, two grasshoppers will die, if the ship does not leak, then two grasshoppers survived

2. Persistence: When a database completes an operation through something, its results and database changes are persisted in the database.

3. Isolation: The isolation of things is to ensure that the data does not have the following problems:

3.1. Dirty Reading

Resolution: If one transaction a changes the data but has not committed yet another transaction B can read the results of an update that has not yet been committed by transaction A. Thus, when transaction A is rolled back, the data that transaction B begins to read is a dirty piece of data.

3.2. Non-repeatable reading

Parse: Non-repeatable READ: The same transaction during the transaction, the same data read operation, read the results are different. For example, transaction B reads data before the update operation of transaction A, which may be different from the data read after transaction a commits this update operation.

3.3. Phantom Reading

Parsing: The same query executes multiple times throughout the transaction, and the query results are different. For example, transaction a updates all records, and before committing, transaction B inserts a record, and when transaction a reads the database again, it finds that there is a record (that is, the newly inserted record for transaction B) that has not been updated.

In general, the isolation level is inversely proportional to system concurrency, in proportion to the consistency of the data

4. Atomicity: The atomicity of a transaction means that the program contained in the transaction acts as a logical unit of work for the system, which either executes all of the data modification operations or does not execute at all. This characteristic is called atomicity. The meaning of atomicity is the basis of database system.

After describing what is a thing, I'll tell you how to use things:

Its syntax is:

1. Starting things: BeginTransaction

2. Commit a thing:COMMIT TRANSACTION

3. Rolling back things:rollback TRANSACTION

Then I take everyone to write a thing, so that everyone has an intuitive feeling:

BEGIN Tran

DECLARE @count int

SET @count =0

--balance is constrained, its value cannot be less than 1

Update Cardinfo SET balance=0 where cardid= ' 1010 3576 1212 1004 '--Error updates

SET @[email Protected][email protected] @error--Record the error message of the statement above

--Next update the correct information

Update Cardinfo SET balance =1000 WHERE CardID = ' 1010 3576 1212 1130 '--correct update

SET @[email Protected][email protected] @error--Record the error message of the statement above

PRINT @count

IF (@count <>0)

Begin

print ' Trade failed, things will roll back '

Rollback Tran

End

Else

Begin

print ' trading success, things will be submitted, thank you for your cooperation! '

Commit Tran

End

Things I'm going to talk about here, and then we'll explain the view

Still this problem, since to speak the view, then must understand the view, ask everybody, what is the view, what is the benefit of the view ?

Answer:

What's the view?

Parsing: A view is another way to view data in one or more tables in a database, which is a virtual table, usually created as a row or column from one or more tables.

In layman's terms, a view is a virtual table that stores a bunch of SQL statements, and note that you cannot use the UPDATE statement when creating a virtual table:

What are the benefits of a view?

Analysis: The benefits are two, respectively 1. The benefit to the end user is that it makes it easier for users to understand and to get data easier. 2. The benefits to developers are that restricting data retrieval is easy, and maintaining an application is easier.

There are two ways to create a view, using T-SQL statements to create and use manual creation.

Then let me give you two ways to create the view separately, so that everyone has an intuitive benefit.

The first way:

--T-SQL statements that create views

--Check that the view exists, and when the view is present, the view of the name is deleted and the new view is created

IF exists (SELECT * from sysobjects where name= ' vw_cardinfo_userinfo ')

DROP VIEW Vw_carinfo_cuserinfo

GO

Create VIEW vw_cardinfo_userinfo--Name

As

Select CardID as bank card number, pass as password, customerName as name from Cardinfo,userinfo

WHERE Userinfo.customerid=cardinfo.customerid

GO

SELECT * from Vw_cardinfo_userinfo

The second way:

NN ~ ~ ~ I still recommend that you use the first way to create the video using T-SQL statements, because it will make you feel a bit professional ~~~~~~

Good view here we are, and we have the last point of knowledge, which is the index.

Since we are here, then the question comes, please answer, what index? , the index is divided into several major categories, what are the two main types of what two?

Parsing: The index is like the catalogue of our book, there are chapters, on the first few pages, more convenient with our search and application, without our page by page search.

Divided into 6 major categories:

1. Unique index keyword unique is a unique index name create unique index must add unique

Use Mygongzuozhandian

--Create an index

GO

IF exists (SELECT * from sysindexes where name= ' Ix_detailed_name ')

DROP INDEX Detailed.ix_detailed_name

/* Create a unique index */

CREATE Unique index Ix_detailed_name

On detailed (Name)

With FILLFACTOR = 30

GO

/* Call */

SELECT * from Admin

With (Index=ix_detailed_name)

where id>=0

The following method is similar to this one, so I will not write more ~

2. Primary KEY index

The clustered index is created automatically when you create a primary key column and set it as a self-increment column.

3. Clustered index

Use Mygongzuozhandian

--Create an index

GO

IF exists (SELECT * from sysindexes where name= ' Ix_detailed_name ')

DROP INDEX Admin.ix_detailed_name

/* Create a Clustered index index */

CREATE CLUSTERED Index Ix_detailed_name

On admin (ID)

With FILLFACTOR = 30

GO

4. Nonclustered index keyword nonclustered nonclustered index The name of a nonclustered index must be prefixed with nonclustered, index is the keyword that creates it, and FILLFACTOR is the keyword that sets the fill factor

Use Mygongzuozhandian

--Create an index

GO

IF exists (SELECT * from sysindexes where name= ' Ix_detailed_name ')

DROP INDEX Detailed.ix_detailed_name

/* Create a nonclustered index */

CREATE Nonclustered index Ix_detailed_name

On detailed (Name)

With FILLFACTOR = 30

GO

5. Composite Index

Users can build indexes on multiple columns,

This index is called a composite index ( composite index ). Composite indexes are created in exactly the same way that you create a single index. However, composite indexes require less overhead during database operations,

Can replace multiple single indexes. Using this method can significantly speed up the query speed of a table when the number of rows in the table is much larger than the number of index keys.

6. Full-Text Indexing

Full-text indexes, unlike normal indexes, are maintained by the b-tree structure, whereas full-text indexing is a special type of markup-based functional index that is created and maintained by the Microsoft SQL Server full-text engine service. With full-text indexing, you can quickly and flexibly create a keyword-based index for text data stored in a SQL Server database, unlike a like statement, where a search for a similar statement is a query that applies to character patterns, whereas a full-text index is a search for words and phrases based on the rules of a particular language. is a language-specific search.

Promotion: Http://www.cnblogs.com/fengyepiaoluo/p/4058050.html

T-SQL things, views, and indexes