Views, indexes, stored procedure pros and cons

1. View
(1). What is a view?
A view is a database object that provides users with a way to retrieve data from a data table. The user browses some or all of the data that is of interest to the data table through the view, and the physical storage location of the data is still in the table.
A view is a virtual table and does not represent any physical data, just a window that is used to view data. Views are not stored in a database as a set of data, only the definition of the view is stored in the database, and the data for the view is not stored, which is still stored in the base table of the exported view. When the data in the base table changes, the data that is queried from the view changes as well.
The data rows and columns in the view are derived from the base table and are generated dynamically when the view is referenced. Views can be used to centralize, simplify, and develop a user's database display, which allows users to access the data without having to go directly to the base table of the view.
A view consists of two parts, the view name and the view definition. A view is a table from one or more tables, which is actually a query result, and the query that corresponds to the name and view of the view is stored in the data dictionary.

(2). What are the pros and cons of views?
<1>. Advantages of views
A. Data security.
Define different views for different users so that users can only see data about themselves. The database Authorization command enables each user to limit the retrieval of a database to a specific database object, but not to a specific row or column on a database. With views, users can be limited to different subsets of the data.
B. Query simplification.
To create a view of complex queries, users do not have to enter complex query statements, just make simple queries against this view. Those queries that are used frequently can be defined as views, so that users do not have to specify all the conditions for subsequent operations each time.
C. Logical data independence.
Views enable applications and database tables to be somewhat independent. If there is no view, the application must be built on the table. With a view, the program can be built on top of the view so that the program is separated from the database table by the view.
For views, for example, a query depends only on the definition of the view, and when the base table that makes up the view needs to be modified, only the subquery portion of the view definition needs to be modified, and the view-based query does not change.

<2>. Disadvantages of the View
A. Performance.
SQL Server must turn the query of the view into a query for the base table, and if the view is defined by a complex multi-table query, then even a simple query of the view, SQL Server will turn it into a complex combination that takes some time.
B. Modify the restrictions.
When a user attempts to modify some rows of a view, SQL Server must convert it to some row of the base table. In fact, this is the case when inserting or deleting from a view. This is convenient for simple views, however, for more complex views, they may not be modifiable, and these views have the following characteristics:
A. There is a view of the unique set operator.
B. A view with a GROUP BY clause.
C. There is a view of aggregate functions such as Avg\sum\max.
D. Use the view of the DISTINCT keyword.
E. View of the join table (with some exceptions)
(3). Limits for creating views:
When creating a view, also be aware of the following limitations that you must try to meet:
<1>: The rule or Default definition cannot be associated with the view.
<2> a query that defines a view cannot contain an order By\compurer\computer by clause and into keyword
<3> If a column in the view is an arithmetic expression, constructor, or constant, and two or more different columns in the view have the same name (usually because there is a connection in the definition of the view, and the two or more columns from different tables have the same name), , the user needs to specify the name of the column for each column of the view.

Reference blog:
Advantages and disadvantages of using SQL Server views

2. Index
(1). What is an index?
The index is a database object based on the table column, which holds the indexed columns sorted in the table, and records the physical storage location of the index columns in the data table, realizes the logical ordering of the data in the table, its main purpose is to improve the performance of SQL Server system, speed up the query of data and reduce the response time of the system. The index points to the records in the table by key values in the record table, so that the database engine does not have to scan the entire table to locate the related records. Conversely, if there is no index, it causes all records in the SQL Server Search table to get the matching results.
In addition to increasing the speed of querying data within a table, indexes can make connections between tables and tables faster. For example, when you implement data referential integrity, you can make the foreign key of the table an index, which accelerates the connection between the table and the table.

(2). Classification of indexes
There are 3 types of indexes: clustered, nonclustered, and unique. If a clustered index exists in the table, the nonclustered index uses a clustered index to speed up the data query.
<1>. Clustered index
Clustered indexes physically sort tables and views, so they are very effective for queries and only have one clustered index in tables and views. When a PRIMARY key constraint is established, if there is no clustered index in the table, SQL Server uses the primary key column as the clustered index key. You can index on a combination of any column or column of a table, and a column that is typically defined as a PRIMARY key constraint in the actual application builds a clustered index.
<2> nonclustered indexes
Nonclustered indexes do not physically sort tables and views. If a clustered index does not exist in the table, it represents an unordered. You can create up to 250 nonclustered indexes, or 249 nonclustered indexes and one clustered index, in a table or view.
<3> unique index
A unique index does not allow two rows to have the same index value. As long as the data in the column is unique, you can create a unique clustered index on the same table. If uniqueness must be enforced to ensure data integrity, you should create a unique or PRIMARY KEY constraint on the column instead of creating a unique index.

(3). The cost of using the index
Although indexes have many advantages, the existence of indexes also makes the system pay a certain price. Creating indexes and maintaining indexes consumes time, and when the data in the table is incremented, deleted, and modified, the index is maintained, or the index is reduced; In addition, each index consumes a certain amount of physical space, which can affect the performance of the entire SQL Server system if it consumes too much physical space.

(4). Principles of Index Building
Creating an index can improve query speed, but it is sacrificing certain system performance. Therefore, at the time of creation, which columns are suitable for creating indexes, which are not suitable for creating indexes, and need to be judged, the following principles are specific:
<1> The data column with the primary key to be indexed. Because the primary key can be accelerated to locate a row in the table.
<2> A data column with a foreign key to be indexed. Foreign key columns are typically used for connections between tables, and creating indexes on them can speed up connections between tables.
<3> For data columns that are frequently queried, it is best to index them.
A. For data columns that need to be queried quickly or frequently within a specified range, because the index is sorted and its specified range is contiguous, the query can take advantage of the sorting of the index to speed up the query time.
B. The data columns that are often used in the WHERE clause, which is built during the collection of the WHERE clause, allow the data columns that frequently participate in the query to be queried in the order of the index for the data columns that need to be accelerated or frequently retrieved, speeding up the query time.
<4> Do not index columns that are rarely involved in those queries, and that have more duplicate values.
For example, a column that is seldom used in a query, with or without an index, does not increase the speed of the query, but instead increases the system maintenance time and consumes the system space.
<5> Do not index columns that are defined as text, image, and bit data types. Because data columns of these data types have a large or small amount of data, they are not conducive to the use of indexes.

Reference blog:
"SQL Server Index Tuning Practice"
SQL Server Index maintenance Guide (1)
Detailed explanation of performance issues with SQL Server indexes

3. Stored Procedures
(1). What is a stored procedure?
When developing an application, in order to be easy to modify and augment, the statements that are responsible for different functions are often lumped together and placed separately, so that they can be called repeatedly, and those languages that are individually placed and have different functions are "processes" (Procedure). A
Stored procedure (Stored producedures) is a set of SQL statements that is a complete set of features that are stored in the database after compilation. The user executes a parameter by specifying the name of the stored procedure (if the stored procedure has parameters).
It can contain statements that perform various database operations, and can call other stored procedures, be able to accept input parameters, and return multiple data values to the Invoker as output parameters (calling Procedure) or batch processing (batch) ; Returns a status value to the calling program or batch to indicate success or failure (and the reason for the failure).

(2). Advantages of stored Procedures
<1> Stored Procedure Advantages
A. Fast execution speed.
Stored procedures are compiled only at creation time, have been checked for syntax and performance optimizations, and each subsequent execution of the stored procedure does not need to be recompiled, and the SQL statements we typically use are compiled once per execution, so using stored procedures can improve database execution speed.
B. Allow component-type programming.
Often encountering complex business logic and operations on the database, this time the SP is used to encapsulate the database operations. When complex operations are performed on a database, such as when multiple tables are update,insert,query,delete, this complex operation can be encapsulated with stored procedures and used in conjunction with the transactional processing provided by the database. You simply create a stored procedure once and store it in a database, and you can call the procedure any time in a program. In code, the separation of SQL statements and program code statements can improve the readability of program code.
Stored procedures can set parameters to reuse the same stored procedure depending on the parameters passed in, effectively improving the optimization rate and readability of the code.
C. Reduce network traffic.
An operation that requires hundreds of lines of Transact-SQL code is implemented by a separate statement that executes the procedure code without the need to send hundreds of lines of code across the network.
For the same operation on a database object, if the T-SQL statement involved in this operation is organized into a stored procedure, then when the stored procedure is called on the client, the call statement is passed in the network, otherwise it will be multiple SQL statements. This reduces network traffic and lowers Network load.
D. Improve system security.
A stored procedure can be used as a conduit for user access to data. Can restrict the user access to the data table, establish a specific stored procedure for the user to use, to avoid unauthorized user access to data, to ensure the security of data.

<2> stored Procedure disadvantages:
A. Poor portability. Rely on database vendors, difficult to transplant (when a small system developed to large systems, the requirements of the database will also change);
B. Difficult to debug, maintenance. Business logic is large, the encapsulation is not enough, difficult to debug difficult to maintain;
C. The server is not load balanced. Complex applications are implemented with stored procedures that load the burden of business processing on the database server. There is no way to flexibly share loads and pressures through the middle tier. Balanced load, etc.

(3). Stored Procedure Classification
&LT;1&GT; system stored procedures
System Stored procedures are primarily stored in the master database and are prefixed with sp_, and system stored procedures are primarily obtained from system tables, providing support for system administrators to administer SQL Server.
<2> local stored procedures
Local Stored procedures is a stored procedure that users create themselves in the user database. In fact, the stored procedure that is generally referred to is worth the local stored procedure. A stored procedure created by a user is a stored procedure created by a user and capable of completing a specific function, such as querying the data information required by a user.
&LT;3&GT; temporary stored procedures
Temporary stored procedures (temporary Stored procedures) can be divided into the following two types:
A. Local temporary stored procedures
If you create a stored procedure with a pound sign (#) as the first character of its name, the stored procedure becomes a local temporary stored procedure that resides in the tempdb database (for example, CREATE PROCEDURE #book_proc ...). The local temporary stored procedure can only be executed by the user who created it, and once the user disconnects from SQL Server, the local temporary stored procedure is automatically deleted and, of course, the user can delete the multi-created local temporary stored procedure with the drop procedure command during the connection.
B. Global temporary stored procedures
If the stored procedure name you create begins with a two pound sign (# #), the stored procedure becomes a global temporary stored procedure stored in the tempdb database and, if not, deletes the global temporary stored procedure immediately, and if so, SQL Server lets those operations continue. However, no user is allowed to perform the global temporary stored procedure, and the global temporary stored procedure is automatically deleted when all outstanding operations are completed.
Because global temporary stored procedures can be used by all connected users, it is important to note that their names cannot be the same as those used by other connections.
&LT;4&GT; remote stored procedures
Remote Stored Procedures is a stored procedure located on a remote server, and you can typically execute a remote stored procedure using the distributed query and execute commands.
&LT;5&GT; extended stored procedures
Extended stored Procedures (Extended Stored procedures) are stored procedures that users can write in an external program language. Extended stored procedures are identical to normal stored procedures in terms of usage and execution. Parameters can be passed to extended stored procedures, and extended stored procedures can also return results and status values.
To differentiate, the name of an extended stored procedure usually begins with xp_. Extended stored procedures exist in the form of a dynamic-link library (DLLS) that allows SQL Server to load and execute dynamically. The extended stored procedure must be stored in the system database master.

Reference blog:
SQL Server Stored Procedures

4. Transaction processing
(1). What is a transaction? A
transaction is a single unit of work. If a transaction succeeds, all data changes made in the transaction are committed and become a permanent part of the database. If the transaction encounters an error and must be canceled or rolled back, all data changes are cleared. A transaction has 4 properties as a logical unit of work, called acid (atomicity, consistency, isolation, and persistence) properties.
<1> atomicity: A transaction must be an atomic unit of work, either all executed for its data modification, or none executed.
<2>. Consistency: When a transaction is complete, it must keep all data in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. When things end, all the content data results must be correct.
<3>. Isolation: Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction, guaranteeing that the data is in a state when the transaction is viewing the data, only if another concurrent transaction modifies its state before it is modified, or the state after which another transaction modifies it, rather than the data in the middle state.
<4> persistence: The effect on the system after the transaction is complete is permanent.
(2). Transaction classification
<1>. Explicit transactions: The start of a transaction is explicitly specified with BEGIN TRANSACTION.
<2>. Recessive transaction: Open implicit transaction: Set implicit_transactions on, when operating in implicit transaction mode, SQL Servler automatically starts a new transaction after committing or rolling back the transaction. Cannot describe the beginning of a transaction, only the transaction must be committed or rolled back.
<3>. autocommit transaction: The default mode of SQL Server, which treats each individual T-SQL statement as a transaction. If executed successfully, it is automatically committed or rolled back.

See blog:
SQL Server Transactions

5.SQL server intrinsics
Internal functions are used to help users obtain information about the system, perform calculations, implement data transformations, and statistical functions. The internal functions provided by SQL are divided into system functions, date functions, String functions, mathematical functions, aggregate functions and so on.
(1). System functions
System functions Help to obtain information from a SQL Server system table without directly accessing the system tables. The system functions operate on SQL Server servers and database objects, and return information such as server configuration and database object values. System functions can be used to select a list, a WHERE clause, and any place where an expression is allowed to be used.

system Functions	function
App_name ()	Returns the application name of the current session (if the application is set)
Case expression	Evaluates a list of conditions and returns one of several possible results of an expression.
CAST (expression as data_type)	Converts an expression display to another data type.
CONVERT (Data_type[length],expression[,style])	Converts an expression display to another data type. Cast and convert provide similar functionality.
Col_length	Returns the length of a column rather than any single string stored in the column.
Current_timestamp	Returns the current date and time. This function is equivalent to GETDATE ().
Current_User	Returns the current user, this function is equivalent to USER_NAME ().
DATALENGTH (expression)	Returns the number of bytes consumed by the expression.
Getansinull ([' Database '])	The default value for the database that returns the session is null. Getansinull returns 1 when a null value is allowed when a given database is null and the column or data type is null without a display definition.
HOST_ID ()	Returns the host identity.
HOST_NAME ()	Returns the host name.
Ident_current (' table_name ')	The last identity value generated for the specified table in any session and in any scope.
IDENT_INCR (' Table_or_view ')	Returns the identity increment for the identity column of the table.
Ident_seed (' Table_or_view ')	Returns a seed value that is the value specified when an identity column is created in a table or view with an identity column.
IDENTITY (Data_type[,seed,increment]) as Col_name	The identity column in the new table is generated only in select Iinto.
ISDATE (expression)	Returns 1 if the expression is a valid date format, otherwise 0 is returned.
ISNULL (Check_expression,replacement_value)	Expression value is null and replaced with the specified replacement value
IsNumeric (expression)	Returns 1 if the expression is a numeric type, otherwise 0 is returned.
NEWID ()	Generates a globally unique identifier.
Nullif (expression,expression)	If two of the specified expressions are equal, a null value is returned.
ParseName (' object_name ', ' Object_part ')	Returns the specified part of the object name.
PERMISSIONS ([objectid[, ' column ']])	Returns a value that contains a bitmap indicating the current user's statement, object, or column permissions.
Rowcount_big ()	Returns the number of rows affected by the execution of the last statement.
Scope_identity ()	Inserts the last identity value in the identity column of the current scope.
SERVERPROPERTY (PropertyName)	Returns information about the server properties.
Sessionproperty (option)	Set options for the session.
Stats_date (table_id,index_id)	The date that the page was allocated for table_id and index_id updates.
USER_NAME ([id])	Returns the user name of the user database to the specified identification number.

(2). Date function
The date function is used to display information about the date and time. They handle the values of Datatime and smalldatetime and perform arithmetic operations on them.

Date Function	function
GETDATE ()	Returns the current system date and time of the server.
Datename (date element, date)	Returns the name of the specified date and returns a string.
Daterart (date element, date)	Returns a portion of the specified date, returning an integer.
DATEDIFF (date element, date 1, date 2)	Returns the difference between two days and converts to the specified date element form
DATEADD (date element, date)	Adds a date element plus a date to create a new day.
Year (date)	Return year (integer)
MONTH (date)	Return month (integer)
Day (date)	Returns the integer value of a month's date
getUTCDate ()	Returns the value of the date representing the current UTC time (World time coordinate and Greenwich newspaper time)

Date element References:

Date Element	Abbreviations	Take value
Year	Yy	1753-9999
Month	Mm	1-12
Day	Dd	1-31
Day of the Year	Dy	1-366
Week	Wk	0-52
Weekday	Dw	1-7
Hour	hh	0-23
Minute	Mi	0-59
Quarter	Qq	1-4
Second	Ss	0-59
Millisecond	Ms	0-999

(3). String functions
String functions are used to concatenate, intercept, and manipulate strings.

String Functions	function
ASCII (character-expression)	Returns the ASCII code for the leftmost character of a character expression.
CHAR (int-expression)	To convert an ASCII code into a character, the ASCII code should be between 0-255.
SPACE (int-expression)	Returns a string consisting of n spaces, the value of an n integer expression.
LEA (character expression)	Returns the number of characters (not bytes) of the character expression, trailing spaces are not counted
Right (character-expression, integer-expression)	The rightmost n characters are returned from the character expression, and n is an integer expression.
Left (character expression, integer expression)	Returns the leftmost n characters from a character expression, and n is an integer expression.
SUBSTRING (character expression, starting point, N)	Returns the n characters from the start point in a string expression.
STR (floating-point expression [, length [, decimal]])	Converts a floating-point expression to a string of the given length, and the number of digits after the decimal point is determined by the "decimal" given.
LTRIM (character-expression)	Removes leading spaces for character expressions.
Rteim (character-expression)	Remove trailing spaces from the character expression.
LOWER (character-expression)	Converts the letter of a character expression to a lowercase letter.
UPPER (character-expression)	Converts the letter of a character expression to uppercase.
REVERSE (character-expression)	Returns the inverse of a character expression.
DIFFERENCES (character-expression 1, character-expression 2)	Returns the similarity (0-4) of the two-character expression pronunciation, and 4 is the most similar pronunciation.
PATINDEX ("% mode%", expression)	Returns the starting position of the specified pattern in an expression, which is 0 when it is not found.
Peplicate (character-expression, integer-expression)	Repeats the character expression multiple times, and the integer gives the number of repetitions.
SOUNDEX (character-expression)	Returns the 4-character code that corresponds to a character expression.
NCHAR (integer expression)	Returns the Unicode character.
UNICODE (character-expression)	Returns the Unicode code for the leftmost character of a character expression.
STUFF (character expression, start,length, character expression 2)	The length character in character expression 1, starting with start, is replaced by a character expression of 2.
CHARINDEX (character expression 1, character expression 2,[start position])	Returns the character expression 1 at the beginning of the character expression 2, which can be found from the given start position, If you do not specify a start position, or if you specify a negative number or 0, the default is to find the starting position of the character expression 2.

(4). Mathematical functions

Mathematical Functions	function
ABS (numeric expression)	Returns the absolute value (positive value) of an expression.
ACOS (floating-point expression)	Returns the floating-point expression inverse cosine value in radians.
ASIN (floating-point expression)	Returns the inverse sine of a floating-point expression in radians.
ATAN (floating-point expression)	Returns the inverse tangent value (in radians) of a floating-point expression.
ATAN2 (floating-point expression 1, floating-point expression 2)	Returns the angle value in radians, where the inverse tangent of the value is between the given floating-point expression 1 and the floating-point expression 2.
COS (floating-point expression)	Returns the trigonometric cosine value of a floating-point expression.
COT (floating-point expression)	Returns a floating-point expression triangle cotangent value.
Ceilign (numeric expression)	Returns the smallest integer greater than or equal to a numeric expression.
DEGREES (numeric expression)	Converts radians to degrees.
EXP (floating-point expression)	Returns the exponential form of a numeric value.
Floor (numeric expression)	Returns the largest integer greater than or equal to the numeric expression, Ceilign's inverse function
LOG (floating-point expression)	Returns the natural pair of numeric values.
LOG10 (floating-point expression)	Returns the logarithm of a floating-point number with a base of 10.
PI ()	Returns a value of 3.1415962653.
Power (numeric expression, power)	Returns the value of the specified power of a numeric expression.
RADIANS (numeric expression)	Converts degrees to radians, degrees inverse functions.
RAND (integer-expression)	Returns a random decimal number between 0-1.
ROUND (numeric-expression, integer-expression)	Rounds the set expression to the precision given by the integer expression.
sign (numeric expression)	Symbolic function, positive return 1, negative return -1,0 returns 0.
SQUARE (floating-point expression)	Returns the square of a floating-point expression.
SIN (floating-point expression)	Returns the trigonometric sine of a floating-point expression (in radians).
SQRT (floating-point expression)	Returns the square root of a floating-point expression.
TAN (floating-point expression)	Returns the value of the floating-point expression tangent (in radians).

(5). Aggregate functions
A collection function is also called a statistical function, which evaluates a group and returns a numeric value. Aggregate functions are often used with the clauses of a SELECT statement.

Aggregation Functions	function
SUM ([all| Distinct]expression)	Calculates the and of a set of data
MIN ([all| Distinct]expression)	Give the minimum value of a set of data
MAX ([all| Distinct]expression)	Give the maximum value of a set of data
COUNT ([all| distinct]expression|*)	Calculates the total number of rows. COUNT (*) returns the number of rows, including rows that contain null values and cannot be used with distinct
CHECKSUM (*|expression[,... n])	The calibration of a set of values and the detection of changes to the table
Binary_checksum (*|expression[,... n])	Binary and checksum detection of table changes
AVG ([all| Distinct]expression)	Calculates the average of a set of values

Views, indexes, stored procedure pros and cons