SQL Server 2008 from getting started to mastering--20180716

1. Lock

Concurrency problems occur when multiple users modify the same data at the same time, and a transaction can be used to solve the problem. However, in order to prevent other users from modifying the data in another transaction that is not yet completed, a lock is required in the transaction.
SQL Server 2008 provides a variety of lock modes: exclusive, shared, update, intent, key range, schema, and bulk update locks.
Querying the sys.dm_tran_locks View provides a quick insight into locking conditions within SQL Server 2008.

SELECT * FROM sys.dm_tran_locks;

Note: The knowledge of the lock is not detailed in the book, will be added in the future blog.

2. Cursors

A cursor is a structure similar to a C language pointer, and is a data access mechanism that allows users to access separate rows of data. Cursors consist primarily of the cursor result set and the cursor position. A cursor result set is a collection of rows returned by a SELECT statement that defines a cursor, which is a pointer to a row in the result set.
Example 1: Retrieving each row of records in a student table with a cursor
Student Table Records

Execute the following statement

USE testDECLARE stu_cursor CURSOR FORSELECT * FROM student--声明student表的游标stuOPEN stu_cursor--打开游标FETCH NEXT FROM stu_cursor--移动该记录指针WHILE @@[email protected]@FETCH_STATUS用于保存FETCH操作的结束信息，=0表示有记录检索成功BEGINFETCH NEXT FROM stu_cursor--游标指针移动到下一条记录ENDCLOSE stu_cursor--关闭游标DEALLOCATE stu_cursor--释放游标资源

Results

2.1. Parameters defined by the cursor local and global

The cursor definition parameter local indicates that the cursor can only be used for this batch or function or stored procedure. The cursor definition parameter global indicates that the cursor can be used globally.
Execute the following statement

DECLARE stu_cursor CURSOR LOCALFOR SELECT * FROM studentGOOPEN stu_cursorGO

Execution results are as follows

statement, a cursor stu_cursor is declared for a student table, and the cursor is not present when the cursor is opened. Because the cursor parameter is local, it can only be used in the current batch statement, and opening a tour banner and declaration statement is not in a batch. If you remove the first go, so that two statements in the same batch, you can successfully execute without error.
Execute the following statement

DECLARE stu_cursor1 CURSOR GLOBALFOR SELECT * FROM studentGOOPEN stu_cursor1GO

Execution Result: Command completed successfully
In contrast to the local parameter, the Golbal parameter sets the cursor for global use, so the open and DECLARE statements are not executed successfully in the same batch.

2.2. Cursors are divided into cursor variables and cursor types

such as the following statement

--语句1DECLARE stu_cursor CURSORFOR SELECT * FROM student--声明student表的游标名称为stu_cursor并赋值GO--语句2DECLARE @stu_cursor CURSOR--声明游标类型的变量@stu_cursorSET @stu_cursor=CURSOR FOR--给该变量赋值SELECT * FROM student

A cursor is declared directly in statement 1 and assigned a value, while statement 2 declares the variable @stu_cursor of the cursor type and assigns a value to the variable. The two are different.

2.3. Cursor parameters forward_only and scroll

The forward_only parameter sets the cursor to be read only from the beginning of the result set to the end direction, using the FETCH statement only with Next, while the scroll parameter sets the cursor to move from any position in any direction in the result set. such as the following statement

--语句1，默认FORWARD_ONLYDECLARE stu_cursor CURSOR LOCALFOR SELECT * FROM studentOPEN stu_cursorFETCH NEXT FROM stu_cursorGO--语句2，FORWARD_ONLY参数，FETCH时只能从开始往结束方向DECLARE stu_cursor CURSOR FORWARD_ONLY LOCALFOR SELECT * FROM studentOPEN stu_cursor FETCH NEXT FROM stu_cursorGO--语句3，SCROLL参数，FETCH时可以从任意位置往任意方向DECLARE stu_cursor CURSOR SCROLL LOCALFOR SELECT * FROM studentOPEN stu_cursorFETCH LAST FROM stu_cursorGO

2.4. Simple application of Cursors

Example 2: Subtract 100 points from a student who stu_enter_score more than 600 points in the student table
Student data in a table

Execute the following statement

--游标的简单应用DECLARE stu_cursor CURSOR FORWARD_ONLY LOCALFOR SELECT stu_no,stu_enter_score FROM studentOPEN stu_cursorDECLARE @score INTDECLARE @stu_no VARCHAR(8)FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@scoreWHILE @@FETCH_STATUS=0 BEGINIF @score>=600BEGINUPDATE studentSET [email protected] WHERE [email protected]_no ENDFETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@scoreENDCLOSE stu_cursorDEALLOCATE stu_cursorGO

Results

3. Stored Procedures

A stored procedure is a set of statements that are used to complete a specific function and are stored in the database after compilation. In SQL Server 2008, you can either write a stored procedure in T-SQL or write a stored procedure with the CLR.

3.1. User-defined stored procedures

This stored procedure refers to a module or process that encapsulates reusable code, with 2 types: T-SQL stored procedures and CLR stored procedures.
A T-SQL stored procedure refers to a collection of saved T-SQL statements
CLR stored procedures refer to references to Microsoft. NET Framework common Language Runtime (CLR) methods

3.2. Extended stored Procedures

Extended stored procedures refer to DLLs that can be dynamically loaded and run, allowing you to create your own external routines using programming languages such as C. Extended stored procedures run directly in the address space of an instance of SQL Server 2008 and can be programmed using the SQL Server extended stored procedure API.

3.3. System stored Procedures

A system stored procedure is a stored procedure stored in the source database that begins with an SP and appears in the SYS schema of each system-defined database and user-defined database.

3.3.1. Creating a Stored procedure rule

When designing and creating stored procedures, you should meet certain constraints and rules.

• The CREATE procedure definition itself can include any number and type of SQL statements, except for the statements in the following table. These statements cannot be used anywhere in the stored procedure.
• You can reference an object that was created in a unified stored procedure, as long as the object was created when it was referenced
• Temporary tables can be referenced within a stored procedure
• If a local temporary table is created in a stored procedure that exists only for that stored procedure, the temporary table disappears after exiting the stored procedure
• If the stored procedure being executed calls another stored procedure, the called stored procedure can access all objects of the first stored procedure, including the temporary table
• If you perform remote stored procedures that make changes to a remote SQL Server 2008 instance, those changes will not be rolled back. Remote stored procedures do not participate in transaction processing
• The maximum number of parameters in a stored procedure is 2100
• The maximum number of local variables in a stored procedure is limited only by available memory
• Stored procedures up to 128MB depending on available memory

Statement	Statement	Statement
CREATE AGGREGATE	CREATE RULE	CREATE DEFAULT
CREATE SCHEMA	CREATE (ALTER) FUNCTION	CREATE (ALTER) TRIGGER
CREATE (ALTER) PROCEDURE	CREATE (ALTER) VIEW	SET parseonly
SET Showplan_all	SET Showplan_text	SET Showplan_xml
Use database_name

3.3.2. Qualifying names within a stored procedure

Within a stored procedure, if the object used for the statement does not have a qualified schema, the schema defaults to the schema of the stored procedure. If the user who created the stored procedure does not qualify the table name or attempted name referenced in the insert,select,update or DELETE statement, access through the stored procedure by default is limited by the creator permission for the procedure. If there are other users who want to use stored procedures, the object names for all statements (such as CREATE,ALTER,EXECUTE,DROP,DBCC or dynamic SQL statements) that are used for data definition language (DDL) should be qualified with the name of the object schema.

3.3.3. Definition of an encrypted stored procedure

If you want to create a stored procedure and make sure that other users cannot see the definition of the stored procedure, you can use with encryption so that the procedure definition is stored in an unreadable form.

3.3.4.SET Statement Options

When a T-SQL stored procedure is created or changed, the database engine saves the settings of SET QUOTED_IDENTIFIER and set ANSI_NULLS, which will be used when the stored procedure is executed and ignores any client session's ET Quoted_ Identifier and set ANSI_NULLS settings. Other set options are not saved after the stored procedure is created or changed.

3.4. Using stored procedure 3.4.1. Creating a Stored procedure

Example 3: implementing Example 2 with a stored procedure
Data for student tables

Execute the following statement

CREATE PROCEDURE alter_data@a int--参数ASBEGINDECLARE stu_cursor CURSOR FORWARD_ONLY LOCALFOR SELECT stu_no,stu_enter_score FROM studentOPEN stu_cursorDECLARE @score INTDECLARE @stu_no VARCHAR(8)FETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@scoreWHILE @@FETCH_STATUS=0 BEGINIF @score>[email protected]BEGINUPDATE studentSET [email protected] WHERE [email protected]_no ENDFETCH NEXT FROM stu_cursor INTO @stu_no,@score--把游标值赋值给变量@scoreENDCLOSE stu_cursorDEALLOCATE stu_cursorENDGOEXEC dbo.alter_data ‘600‘

Results

3.4.2. Viewing stored procedures

You can view the definition of a stored procedure by using a system stored procedure or a catalog view

3.4.2.1. Graphical interface

Such as

3.4.2.2. System stored procedures sp_helptext viewing stored procedure definitions

Execute the following statement

EXEC sp_helptext ‘alter_data‘

Results

3.4.2.3. System stored Procedures sp_depends View information about stored procedures

Execute the following statement

EXEC sp_depends ‘alter_data‘

Results

3.4.2.4. catalog view View stored Procedures

Execute the following statement

SELECT * FROM sys.procedures

Results

3.4.3. Modifying a stored procedure

Modify the stored procedure with the alter PROCEDURE statement, just modify the create in the example above to run as alter.

3.4.4. Deleting a stored procedure

Execute the following statement to delete the stored procedure

DROP PROCEDURE alter_data

SQL Server 2008 from getting started to mastering--20180716