Simple introduction and operation of SQL Server Index and Views (view)

--Index and Views (view)--

--indexing (Index)--
--Overview :
　　 the index in the database is similar to the book's Directory, which contains a new order of a column or combination of columns in the table as a pointer, implementing the logical ordering of the database in the table。index is created on a field in a datasheet or view, the index page is generated and stored in the database.
--category: In SQL Server, depending on the index of thefunctionAndStorage Mode, the index is divided intoClustered IndexAndnonclustered indexes two classes。
1) clustered index : Refers toPhysical order of the databaseAndthe order of the fields created by the indexexactly the same。 You can create at most one clustered index on the same data table. By default, the primary key is automatically created as a clustered index. Clustered indexes are often created on fields that are frequently queried to improve query efficiency.
2) nonclustered indexes : Nonclustered Indexes anddo not change the physical order of the data tablesButgenerates an index file that holds the pointer address for the index order。 The system displays the query results by associating the address found in the index file with the data in the data table. A data table allows up to 254 nonclustered indexes to be created, and multiple columns of composite indexes are allowed to be created on up to 16 columns.

-- role :

　　The primary purpose of the index is to improve query efficiency . In the database lookup process, the system will gradually find the last row from the first row of the table, and find the record information that satisfies the condition, and for the indexed data table, the system will first use the index to find the storage location of the data. When you find the details of the corresponding data record in the datasheet, but the records that meet the search criteria appear after the record that does not meet the criteria, the system will no longer continue to look for the whole table, thus improving query efficiency .

-- Create an index --
NOTE: Due to constraints on constraint properties, when a field in a datasheet is set with a PRIMARY KEY constraint and a unique constraint , a unique clustered index is set for the PRIMARY KEY constraint and a unique nonclustered index is set for the unique constraint . To create an index, you can use either the manager or the T-SQL language.
Create [unique] [clustered| nonclustered] Index index name --create represents the creation, index represents the indexes. The index name must conform to the naming convention and cannot have the same name as an existing index. The optional unique represents a unique property, clustered represents a clustered property, and nonclustered represents a nonclustered attribute. Without these two keywords, the default is nonclustered, non-unique indexes.
On table name (field name 1, field Name 2 ...)

Example: (Create a nonclustered unique index named "Ix_ Commodity Type Table _ commodity type name" for the "Product type name" field in the Product Management database "Commodity Information sheet")
Use Commodity Management database
Go
Create unique nonclustered
Index Ix_ Commodity Type Table _ product type name
On commodity type table (product type name)

-- View index --
EXEC sp_helpindex data table name

Example: (View the index of the commodity type table in the Product Management database)
Use Commodity Management database
Go
EXEC sp_helpindex Commodity Type table

-- Modify the index --
1) Modify the index name :
EXEC sp_rename ' data table name. Old index name ', ' New index name '

Cases:
Use Commodity Management database
exec sp_rename ' commodity type table. Ix_ Commodity Type Table _ Commodity type name ', ' ix_ commodity type name '

2) Modify the indexed properties that are not dependent on the constraint :
"Object Explorer"--"server"--"database"--the database to be modified--"table"--the data table to be modified--"index"--right-click on the index to be modified--"indexed properties"--the option to remove the "unique" attribute
3) Modify the indexed properties that are dependent on the constraint :
"Object Explorer"--"server"--"database"--the database to be modified--"table"--the data table to be modified--right-click Design to open the Table Designer--Right-click Column Name Select Index/Key--Select the index to modify (primary KEY constraint), in the Right property window " Table Designer "--Create as clustered" Property--Modify to "no"

-- Delete index --
Drop Index Table name. Index Name

Example: (delete the index named "Ix_ Commodity Type Table" in the Product type table of the product Management database)
Use commodity management database
Go
Drop Index commodity type table. Ix_ Commodity type name

----
-- overview :
Span style= "text-decoration:underline;" The view is based on the is generated . Its . The data in the view can originate from one or more base tables, or from the query mix of the base tables and views. Because the when the view is viewed. In general, you cannot modify the data information in a table through a view, but by modifying the base table implementation. Simply put, later.
-- effect : 1) simplifies operations. 2) results are intuitive. 3) Improve safety. 4) Export Save.
-- CREATE VIEW --
CREATE view name [with encryption] as Select The query statement --create View represents the creation of views. The WITH encryption command is used to create a view of code or procedure encryption. The AS keyword follows the query statement that satisfies the syntax format of the SELECT statement.

Example: (In the "Commodity Management database" to find out the November 2, 2012 incoming goods information, required to display the product number, product name, quantity and Arrival date field information, the result is created into a view named "V_ goods _ Purchase")
Use Commodity Management database
Go
Create VIEW Vs_ Goods _ Purchase Success
As
Select commodity Information Sheet. Product number, product name, quantity purchased, arrival date, ' arrival Success ' memo ' from commodity information table, incoming information table where commodity information table. Product Number = Incoming Information table. Product Code
and purchase date = ' 2012-11-02 '

Example: (in the Commodity Management database, the product name with "Wine" in the detailed sales information (display product number, product name, sales price, sales quantity, sales amount, customer name and sales date))
Use Commodity Management database
Go
Create VIEW v_ Sales information
As select commodity Information sheet. Product number, product name, sales price, sales quantity, sales amount, customer name, sales date
From Sales Information table
Join commodity information Sheet on the sales information sheet. Item Number = commodity Information table. Product Code
Join Customer Information table on the Customer Information table. Customer Number = Sales Information table. Customer number
Where commodity information table. Product name like '% wine% '

-- Query view --
1) query View results
SELECT * | [List of field names] from view name [where condition expression]

Example: (Query "v_ goods _ Purchase" view of the goods purchase information)
SELECT * FROM v_ goods _ Purchase Success

Example: (Query "v_ goods _ Purchase" view in the purchase quantity is greater than or equal to 300 of the goods purchase information)
Use Commodity Management database
Go
SELECT * FROM v_ goods _ Purchase success where purchase quantity >=300

2) query view creation Information
EXEC sp_helptext View name

Example: (View the creation of a view named "V_ goods" in the Product Management database)
Use Commodity Management database
Go
EXEC sp_helptext V_ Goods _ Purchase Success

-- Modify View --
1) Modify the View name
EXEC sp_rename old view name, new view name

Cases:
Use Commodity Management database
Go
EXEC sp_rename V_ Goods _ Purchase success, V_ product Information _ Purchase Success

2) Modify the contents of the View
ALTER VIEW name [WITH encryption] AS SELECT query Statement --alter View represents the modification of views, "view name" must be a view name that already exists. The other commands are the same as the CREATE view.

Example: (Change the contents of the "Product Management database" named "V_ Goods _ Purchase Success" To view the November 1, 2012 arrival record information)
Use Commodity Management database
Go
Alter VIEW V_ goods _ Purchase Success
As
Select commodity Information Sheet. Product number, product name, quantity purchased, date of purchase
From commodity information table, incoming information form
Where commodity information table. Item Number = Incoming Information table. Product Code
and purchase date = ' 2012-11-01 '

-- Delete view --
Drop View Name

Example: (Delete the "Product Management database" named "V_ Goods _ Purchase Success" view)
Use Commodity Management database
Go
Drop View V_ Goods _ Purchase Success

Note: "--" for comment text or description

Simple introduction and operation of SQL Server Index and Views (view)