Stored procedure functions in SQL Server things index and view

The specific differences between stored procedures and functions are:

Core Tip : no difference in nature. Only the function has a limit to return only one scalar, and the stored procedure can return multiple. and functions can be embedded in SQL and can be called in SQL statements such as SELECT, and stored procedures do not work. The essence of execution is the same.

There are many function limitations, such as the inability to use temporary tables, only table variables, and so on, while stored procedures are relatively less restrictive.

1. In general, the function of the stored procedure implementation is a bit more complex, and the function implementation of the function is relatively strong.

2. Parameters can be returned for stored procedures, and functions can only return values or table objects.

3. A stored procedure is typically performed as a separate part, and the function can be called as part of a query statement because the function

You can return a Table object, so it can be located after the FROM keyword in a query statement .

4. When the stored procedure and function are executed, SQL Manager will go to the procedure cache to fetch the corresponding query statement, if

Procedure cache does not have a corresponding query statement, SQL Manager compiles stored procedures and functions.

Procedure Cache: The execution plan is saved, and when it is compiled, the execution plan in the Procedure cache is executed, and then SQL Server is based on each execution Plan's actual situation to consider whether or not to save the plan in the cache, the criteria for judging is the frequency at which the execution plan might be used, and secondly, the cost of generating the plan, which is the time it takes to compile. The plan saved in the cache will not be compiled the next time it executes.

Stored procedures: It makes it much easier to manage and display information about its users. A stored procedure is a precompiled collection of SQL statements and optional control-flow statements, stored as a single name and processed as a unit. Stored procedures are stored in the database and can be executed by the application through a call, and allow the user to declare variables, conditional execution, and other powerful programming features. Stored procedures can contain program flow, logic, and queries against the database. They can accept parameters, output parameters, return single or multiple result sets, and return values.

You can use stored procedures for any purpose that uses SQL statements , and it has the following advantages :

(1) powerful, less restrictive.

(2) A series of SQL statements can be executed in a single stored procedure.

(3) Other stored procedures can be referenced from within their own stored procedures, which simplifies a series of complex statements.

(4) The stored procedure is compiled at the time of creation, so it executes faster than a single SQL statement.

(5) You can have multiple return values, that is, multiple output parameters, and you can use Select to return a result set.

Function: A subroutine consisting of one or more SQL statements that can be used to encapsulate code for reuse. Many of the limitations of custom functions, many of which cannot be used, are not implemented. A function can directly reference the return value and return the recordset with a table variable. However, user-defined functions cannot be used to perform a set of actions that modify the state of the global database .

Turn from (http://blog.sina.com.cn/s/blog_48a45b950100ip4o.html)

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

3. Turn from (http://blog.csdn.net/flanet/article/details/7595907)

Stored procedure functions in SQL Server things index and view