Things indexed and views in SQL Server

1.                                              Transaction 1, what is a transactional transaction is an inseparable unit of work, it contains a set of database operations commands, and all commands as a whole to the system to commit or withdraw Pin operation request, that is, either execute, or none of the 2, 4 properties of the transaction (1), atomicity (atomicity): The transaction is a complete operation, the transaction element is not divided (2), the consistency (consistency): When the transaction completes, the data must be in the Consistent state (3), Isolation (isolation): All concurrent transactions that modify data are isolated from each other, indicating that the transaction must be independent, and that it should not in any way depend on or affect other transactions (4), Persistence (durability): After the transaction completes, it The effect on the system is permanent 3, the syntax for creating the transaction:--Start transaction--BEGIN TRANSACTION--Define variables, accumulate errors during transaction execution--DECLARE @erro rsum int Set @errorSum = 0--initialized to 0, that is, no error--adding and deleting operations---set @errorSum = @errorSum [Email protect ED] @error--Cumulative error ...--determine if the transaction was committed or revoked based on an error--if (@errorSum =0) Commit Transactio N--COMMIT TRANSACTION else ROLLBACK TRANSACTION--undo Transaction Note: Variable @errorsum only to delete and change the operation, the query does not need 4, what When a transaction is made up of multiple additions and deletions, the transaction index 1 is applied, and what is the index is the number of SQL Server orchestrations Provides a way for SQL Server to provide an internal method forTo orchestrate the path of the query data, equivalent to the dictionary Directory 2, Index classification (1), unique index: Unique index does not allow two rows with the same index value (2), primary key index: Define a primary key for the table in the database diagram will automatically create a primary key index, the primary key index is a unique index Special type (3), clustered index: the physical order of the rows in the table is the same as the logical (indexed) Order of the key values, and each table can only be specified in the logical order of the table by one (4), nonclustered index: Nonclustered index. The data is stored in one place, the index is stored in a different location, and the index contains a pointer to the location of the data store. Can be multiple, less than 249 3, creating an index syntax: create [unique] [clustered | nonclustered] Index index_name on               TABLE_NAME (Column_name[,column_name] ...)         [Fillfactor=x] unique specifies a unique index, optional clustered, nonclustered specifies whether a clustered or nonclustered index, optional FILLFACTOR represents a fill factor that specifies a 0~100 value that indicates the percentage of space that the index page fills up as an example: Use STUDB go/*--detect if The index exists (the index is stored in system table sysindexes)--*/if exists (select name from sysindexes where Name= ' Ix_stuma Rks_writtenexam ') DROP index stumarks.ix_stumarks_writtenexam--delete index/*--written test column create nonclustered index: Fill factor is 30%-- */create nonclustered index ix_stumarks_writtenexam on Stumarks (Writtenexam) with fillfactor=30 go 3, indexes should be created and indexes should not be created: This column is often used to            Queries that are often sorted by this column should not be indexed: The amount of data in the table should not be created (the index page will be checked first, then the table) when the value of a column changes frequently (modifying the data affects the index page) When the value of the column compares to a single case (such as the value of the column for gender male and female) View 1, what is view view is another way to view one or more tables in a database The method of the data in the view is a virtual table 2, syntax create VIEW view_name as <select statement > Note: Views can be added to and removed from the table, but not recommended Its use and additions and changes, more for the query

Things in SQL Server index and views