database indexes, stored procedures, views, transactions

One, index
An index is a separate, physical database structure that can quickly find information about a table or view
You typically create an index on a table only if you need to query the data in an indexed column frequently
Basic syntax:
CREATE INDEX Stockindex
On appschema.stockevents (StockSymbol);
To create a unique index:
Create Unique index Index name
On table name (Field 1, Field 2)
Clustered index: Clustered only one clustered index per key value
Nonclustered index noclustered: The key value of the index contains a pointer to the record storage location in the table, and does not sort the data in the table
The creation of a clustered index:
Create unique clustered index index name
On table name (field name DESC)
Non-clustered functions:
Create unique nonclustered index index name
On table name (field name)
Two nonclustered indexes: built on heaps and built on clustered indexes
Query index: EXEC sp_helpindex table name
Delete index: The name of the Drop Index table. Index name
Two, view:
A view is a logical object that is a table from one or several basic tables and is a virtual table.
The contents of the view can be: 1, a subset of the columns in the base table or a subset of the rows 2, Union 3 of two or more base tables, statistical summary of the base table 4, a subset of the other views 5, a mix of views and base tables
Limit defined by the view: 1, the CREATE VIEW statement cannot include the COMPUTE or comprte by clause or into keyword 2, and the CREATE VIEW statement can include the ORDER BY clause only if the TOP keyword is used
3, the view cannot reference a temporary table 4, the view cannot reference more than 1024 columns 5, the CREATE VIEW statement cannot be combined with other T-SQL statements in a single batch
To create a view:
CREATE VIEW V_USERVIP
As
SELECT [User].userid,[user].username,vipid,vipdate from [user] join VIP
On [User].userid=vip.userid
To change the view:
Alter VIEW V_USERVIP
As
SELECT [User].userid,sex,[user].username,vipid,vipdate from [user] join VIP
On [User].userid=vip.userid
Delete a view
Drop View DBO.V_USERVIP
Working with indexed views
1, the first index created on the view must be a unique clustered index 2, the view must be created using the Schemabinging option 3, the view can reference the base table, but cannot refer to other view 4, when referencing tables and user-defined functions, you must use two-part names 5, subsequent connections using the index graph must have the same option settings.
2, limits for creating indexed views
The first index created on a view must be a unique clustered index
You must use the SCHEMABINDING option when creating a view
Views can reference base tables, but cannot reference other views
When referencing tables and user-defined functions, two-part names must be used
Subsequent connections using the indexed view must have the same option settings
Three, business
Transactions can be automatically committed or rolled back, and other statements in the transaction cannot be executed when one statement in the transaction cannot be executed
Each individual statement is a transaction
Begin TRANSACTION The starting point of the transaction
Begin try
Insert into Stu values (' Jack1 ', 20, 1)
Insert into Stu values (' Jack2 ', 2, 1)
Commit TRANSACTION Transaction Commit
End Try
Begin Catch
print ' ERROR '
ROLLBACK TRANSACTION Transaction Rollback
End Catch
SELECT * FROM Stu
Four, stored procedures
A stored procedure is a set of SQL statements that are compiled to complete a particular function and stored in a database, which can contain program flow, logic, and queries to the database. They can accept parameters, output parameters, return single or multiple result sets, and return values
If......else statements
Use pubs

Declare @pic float
Select @pic =sum (price) from  titles
if @pic >500
begin
print ' more '
End
Else
begin
print ' less '
End
Case Condition:
use northwind
Go
Select top Orderid,orderid% Ten as ' last Digit ', position=
case OrderID%10
 when 1 Then ' first '
 when 2 Then ' Second '
 when 3 Then ' third '
 when 4 and ' fourth '
Else ' Something Else '
end 
from Orders
Whil loop:
DECLARE @i int
set @i=1 
while @i<10
begin 
Print @i
Set @[email protected]+1
End
Waitfor statement:
Waitfor
delay  ' 01:00 '  --wait an hour to execute
Print ' SQL '

Waitfor
Time ' 08:30 '--wait until 8:30 to perform the operation
Pint ' SQL '
To create a stored procedure with parameters:
Use pubs
Go
CREATE PROCEDURE Mypro
@id varchar (11),
@contract bit,
@phone char (Output)
As
Select @phone =phone from authors where [email protected] and [contract][email protected]

--Call the stored procedure
Decla @phone Char (12)
exec mypro ' 267-41-2394 ', true, @phone output
Print @phone

The method for obtaining a random number in SQL is: NEWID ()

An understanding of the compute and compute by examples seen online:
SQL Server can use SUM () and group by to group the fields of the query and get the sum, but only returns the sum but not the details, compute can get the details of all the data and get the sum in the end
Use pubs
SELECT type, price, advance
From titles
ORDER by Type
COMPUTE sum (price), sum (advance)
Compute by can only appear with order by, and the individual data is listed by group and calculated for each group and
Use pubs
SELECT type, price, advance
From titles
ORDER by Type
COMPUTE sum (price), sum (advance) by type

database indexes, stored procedures, views, transactions