Common knowledge point memorandum

Background The development of a project requires frequent operations on database objects, such as database operations, table view design, stored procedures, and triggers. Sometimes, we will look for similar code in the system, copy and paste it, And then modify it accordingly. The purpose of this article is to summarize and summarize the common operations of SQL Server databases, and

Background The development of a project requires frequent operations on database objects, such as table/View Design, stored procedures, triggers, and so on. Sometimes, we will look for similar code in the system, copy/paste it, And then modify it accordingly. The purpose of this article is to summarize and summarize the common operations of SQL Server databases, and

Background

The development of a project is inseparable from database-related operations, table/View Design, stored procedures, triggers, and other database object operations. Sometimes, we will look for similar code in the system, copy/paste it, And then modify it accordingly. This article aims to summarize and update common SQL Server database operations. Period to be forgotten!

Execution sequence of P1 SQL

SQL statements are a tool used to operate databases. Understanding the SQL Execution sequence will greatly help us improve the efficiency of SQL Execution. See the following code:

(8) SELECT (9) DISTINCT (11) (1) FROM [left_table](3)JOIN(2) ON(4) WHERE(5) GROUP(6)(7) HAVING(10) ORDERFROM: Perform Cartesian product (Cartesian product) on the first two tables in the FROM clause to generate a virtual table VT1.ON: Apply the ON filter to VT1. Only thoseVT2.OUTER (JOIN): If outer join (compared with cross join or (inner join) is specified, preserved table: The left outer join marks the left table as a reserved table ,, the right external join marks the right table as a reserved table, and the full external join marks both tables as reserved tables.) unmatched rows are not found and added to VT2 as external rows, generate VT3. if the FROM clause contains more than two tables, repeat steps 1 to 3 for the result table generated by the previous join and the next table until all tables are processed.WHERE: Apply the WHERE filter to VT3. Only enableIf the value is true, VT4.Group by: GROUP the rows in VT4 BY the column list in the group by clause to generate VT5.CUBE | ROLLUP: inserts Suppergroups into VT5 to generate VT6.HAVING: Apply the HAVING filter to VT6. Only enableIf this parameter is set to true, VT7.SELECT: process the SELECT list and generate VT8.DISTINCT: removes duplicate rows from VT8 to generate VT9.Order by: sorts the rows in VT9 BY column list in the order by clause to generate a cursor (VC10 ).TOP: select a specified number or proportion of rows from the beginning of VC10, generate table VT11, and return to the caller.In general, the select column is executed in the last step, while the From Table is first executed.Create a P2 with Try... Catch stored procedure templateCopy the following code and create a new query. Then you can write an SQL statement. After execution, you can create a stored procedure!USE [DB] -- set the corresponding databaseGOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ===================================================== ======-- AUTHOR:-- DESCRIBE:-- ===================================================== ======Create procedure [dbo]. [UP_InsertJHBData] -- stored PROCEDURE name(@ CustomerName VARCHAR (50) -- Parameter)ASBEGINSet nocount on -- to improve performance, you must haveDECLARE @ Now DATETIMESET @ Now = GETDATE () -- all operations ensure unified timeBEGIN TRY-- Write SQL hereEND TRYBEGIN CATCHDECLARE @ ErrorMessage NVARCHAR (4000 );DECLARE @ ErrorSeverity INT;DECLARE @ ErrorState INT;SELECT @ ErrorMessage = ERROR_MESSAGE (),@ ErrorSeverity = ERROR_SEVERITY (),@ ErrorState = ERROR_STATE ();PRINT @ ErrorMessageRAISERROR (@ ErrorMessage, -- Message text.@ ErrorSeverity, -- Severity.@ ErrorState -- State.);RETURN-1;END CATCHENDP3 creates a stored procedure template with transactionsOnly Try... The template of the Catch stored procedure is subject to transaction control.USE [DB]GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- ===================================================== ======-- AUTHOR:-- DESCRIBE:-- ===================================================== ======Create procedure [dbo]. [UP_InsertJHBData] -- stored PROCEDURE name-- Parameter(@ CustomerName VARCHAR (50))-- ParameterASBEGINSet nocount on; -- to improve performance, you must haveDECLARE @ Now DATETIME;SET @ Now = GETDATE (); -- all operations ensure unified timeBEGIN TRYBegin transaction myTrans; -- start the TRANSACTION-- Write SQL hereCommit transaction myTrans; -- transaction commit statementEND TRYBEGIN CATCHRollback transaction myTrans -- always roll back the TRANSACTION-- Throw an exceptionDECLARE @ ErrorMessage NVARCHAR (4000 );DECLARE @ ErrorSeverity INT;DECLARE @ ErrorState INT;SELECT @ ErrorMessage = ERROR_MESSAGE (),@ ErrorSeverity = ERROR_SEVERITY (),@ ErrorState = ERROR_STATE ();RAISERROR (@ ErrorMessage, -- Message text.@ ErrorSeverity, -- Severity.@ ErrorState -- State.);END CATCHENDP8 groups the dataset and returns the first n records of each group.The Row_NUMBER () function is used to generate row numbers. partition by can be used to group result sets according to specified requirements. Finally, a simple subquery can be used to obtain the first three data records of each group.SELECT *FROM (SELECT ROW_NUMBER () OVER (partition by ProductNO order by ProductNO) AS RowNum,*From im. dbo. ItemInfo) TWHERE t. RowNum IN (1, 2, 3)Use of P9 [user-defined table type]