SQL Server summary and Review (2)

1. ranking function and PARTITION
Copy codeThe Code is as follows: -- all data
SELECT * FROM dbo. student AS a inner join dbo. ScoreTB AS B ON a. Id = B. stuid
WHERE scorename = 'China'
-------------------------------------------
-- The usage of ROW_NUMBER () increases the number of generated columns from 1.
-------------------------------------------
SELECT ROW_NUMBER () OVER (order by B. score desc) as rownumber, A. NAME, B. SCORE, a. Id
FROM dbo. student AS a inner join dbo. ScoreTB AS B ON a. Id = B. stuid
WHERE scorename = 'China'
-- You can also add an order by clause to indicate that all the columns generated earlier are rearranged with the last column (the rank column value remains unchanged)
SELECT ROW_NUMBER () OVER (order by B. score desc) as rownumber, A. NAME, B. SCORE, a. Id
FROM dbo. student AS a inner join dbo. ScoreTB AS B ON a. Id = B. stuid
WHERE scorename = 'China' order by a. Id
-- To use the ranking Function After grouping statistics, grouping is performed first, and the result set is queried by cte or nested query, and then processed by the row_number function.
WITH B
(
SELECT stuid, SUM (score) AS score FROM ScoreTB group by stuid
)
SELECT *, ROW_NUMBER () OVER (order by B. score desc) AS rownumer
FROM dbo. student AS a inner join B ON a. id = B. stuid
----------------------------------------------------------------------------
-- RANK () is used in the same way as the ROW_NUMER function. However, when order by has the same level, the ranking is set to the same, and the next one generates the sequence number based on the number of previous records.
-- For example, if the first three are the same, all are 1 and the next is 4.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
-- DENSE_RANK (): the dense RANK is used in the same way as ROW_NUMER and rank functions, but it is continuous when the sequence number is generated, and the sequence number generated by the RANK function may be discontinuous.
-- For example, if the first three are the same, all are 1 and the next is 2.
----------------------------------------------------------------------------
----------------------------------------------------------------------------
The -- ntile function can group sequence numbers. This is equivalent to placing the queried record set in an array of the specified length. Each array element stores a certain number of records.
-- The sequence number generated for each record is the index of all array elements of the record (starting from 1 ). You can also call the array element of each allocated record as a "Bucket ".
-- It has a parameter to specify the number of buckets, for example
----------------------------------------------------------------------------
SELECT ntile (2) OVER (order by B. score desc) as groupid, A. NAME, ISNULL (B. SCORE, 0) SCORE, a. Id
FROM dbo. student AS a left join dbo. ScoreTB AS B ON a. Id = B. stuid AND scorename = 'China'
--------------------------------------------------------------------------
-- Partition by is similar to applying a group by to the ranking function. After grouping, the group is ranked separately for each group.
--------------------------------------------------------------------------
-- The ranking of each discipline is:
Select rank () OVER (partition by B. scorename order by B. score desc) as rownumber, B. scorename,
B. score, A. NAME, a. Id FROM dbo. student AS a inner join dbo. ScoreTB AS B ON a. Id = B. stuid ORDER BY SCORENAME

2. New TOP usage Copy codeThe Code is as follows: DECLARE @ num INT = 101
Select top (@ num) * FROM Student order by Id -- must be enclosed in parentheses
Select top (@ num) percent * FROM Student order by Id -- only accept the number of float values between 1 and. If it is input, an error is returned.

3. group by all field/group by field
The former is a bit like left join and right join. The main difference between the two is that aggregation functions filtered by the where condition will be crawled and placed into the queried data table again, the aggregate function uses the default value 0 or NULL to replace the return value of the aggregate function based on the type of the returned value.
Of course, in terms of efficiency, the latter is better than the former, just as inner join is better than left join.
4. count (*)/count (0) and count (field)
If the queried field does not have a NULL value, there is no difference between the two query conditions. If there is NULL, the records recorded by the latter are the total number of records-the number of NULL records.
In terms of performance, the former is higher than the latter, because the latter scans the field row by row for NULL values, and does not count for NULL values, reducing the overhead of logical read, to improve performance
5. Usage of top n With ties
See http://www.cnblogs.com/skynet/archive/2010/03/29/1700055.html
For example
Select top 1 with ties * from student order by score desc
Equivalent
Select * from student where score = (select top 1 score from student order by score desc)
6. Apply Operator
Copy codeThe Code is as follows: View Code
-- Prepare data
Create table [dbo]. [Student] (
[Id] [int] NULL,
[Name] [varchar] (50) NULL
)
Go
Insert into dbo. Student VALUES (1, 'zhang san ')
Insert into dbo. Student VALUES (2, 'riss ')
Insert into dbo. Student VALUES (3, 'wang wu ')
Insert into dbo. Student VALUES (4, 'shenren ')
Go
Create table [dbo]. [scoretb] (
[StuId] [int] NULL,
[Scorename] [varchar] (50) NULL,
[Score] INT NULL
)
Go
Insert into [scoretb] VALUES (1, 'China', 22)
Insert into [scoretb] VALUES (1, 'mat', 32)
Insert into [scoretb] VALUES (1, '', 42)
Insert into [scoretb] VALUES (2, 'China', 52)
Insert into [scoretb] VALUES (2, 'mat', 62)
Insert into [scoretb] VALUES (2, '', 72)
Insert into [scoretb] VALUES (3, 'China', 82)
Insert into [scoretb] VALUES (3, 'mat', 92)
Insert into [scoretb] VALUES (3, 'foreign local', 72)
-- Create a table Value Function
Create function [dbo]. [fGetScore] (@ stuid int)
RETURNS @ score TABLE
(
[StuId] [int] NULL,
[Scorename] [varchar] (50) NULL,
[Score] INT NULL
)
As
BEGIN
Insert into @ score
SELECT stuid, scorename, score FROM dbo. scoretb WHERE stuId = @ stuid
RETURN;
END
GO
-- Start to use
Select a. id, A. name, B. scorename, B. score FROM [Student]
Cross apply [dbo]. [fGetScore] (A. Id) B -- equivalent to inner join
Select a. id, A. name, B. scorename, B. score FROM [Student]
Outer apply [dbo]. [fGetScore] (A. Id) B -- equivalent to left join
-- But cannot be used like this
-- Select a. id, A. name, B. scorename, B. score FROM [Student]
-- Inner join [dbo]. [fGetScore] (A. Id) B on a. Id = B. stuid
-- Select a. id, A. name, B. scorename, B. score FROM [Student]
-- Inner join (SELECT * FROM [dbo]. [fGetScore] (A. Id) B on a. Id = B. stuid

7. INTERSECT and except t operators
EXCEPT t only contains the rows on the left of the excpet keyword and the result set on the right does not exist. INTERSECT only contains those rows in both result sets.
Usually EXISTS keywords can replace the above keywords, and the performance can be seen better than them, but EXCEPT/INTERSECT is easier to read and understand. We recommend that you start with better performance.
8. How indexes increase query efficiency
The indexing and EXISTS operators are similar in the processing method. They can all exit the query immediately after finding the matching value, thus improving the query performance.
9. Table variables and temporary tables
Major differences: 1. Table variables do not write logs, and there is no statistical information. Frequent changes do not cause re-Compilation of stored procedures. Indexes and statistical information cannot be created. However, you can create a primary key to flexibly implement index search, table variables are not only operated in the memory, but also in tempdb when the data volume is large, that is, the I/O operation of the physical disk. 2. Transaction rollback is invalid for table variables (no statistical information for the reason)
Generally, the temporary result set needs to be associated with other tables with a large data volume. The temporary table has a small amount of data, and the temporary result set uses table variables separately.
10. Script and Batch Processing
Go is not a T-SQL command, it can only be identified by the compilation tool Management Studio, SQLCMD, if you use a third-party tool, not necessarily support GO commands. Such as ADO. NET and ADO.
11. Use of SQLCMD
SQLCMD-Usa-Psa-Q "SELECT * from testdb. dbo. mytable"
SQLCMD-Usa-Psa-I testsql. SQL SQL statement in the running File
12. EXEC instructions
After EXEC is executed, you can use a variable such AS @ ROWCOUNT to view the number of affected rows. You cannot run the function on the EXEC string in the EXEC parameter, such AS cast (xx as varchar ), for EXEC parameters, only strings can be added or the overall string can be used.
13. WAITFOR
Waitfor time <'time'> scheduled execution; waitfor delay <'time'> delayed execution
14. Summary of Stored Procedures
1) it is more scientific to replace @ ERROR with TRY/CATCH. One @ ERROR is not intuitive with TRA/CATCH, and the other is to encounter an ERROR of 11-19, which causes the operation to be interrupted directly, cause @ ERROR to determine whether the ERROR is invalid.
2) use RAISERROR to throw an error
With log. This option is required when the severity level is greater than or equal to 19.
With seterror to rewrite the @ ERROR value to facilitate external calls
With nowait immediately sends an error notification to the client
15. cursor Review
The cursor mainly includes: 1) Declaration 2) Open 3) use or navigation 4) Close 5) Release
Nested Use cursor exampleCopy codeThe Code is as follows: DECLARE BillMsgCursor CURSOR
SELECT TypeNo, TabDetailName FROM dbo. BillType
OPEN BillMsgCursor
DECLARE @ TypeNo CHAR (5)
DECLARE @ DetailName VARCHAR (50)
Fetch next from BillMsgCursor INTO @ TypeNo, @ DetailName
WHILE @ FETCH_STATUS = 0
BEGIN
DECLARE @ DataFieldName VARCHAR (50)
DECLARE ColumnName CURSOR
SELECT name FROM syscolumns WHERE id = OBJECT_ID (@ DetailName)
OPEN ColumnName
Fetch next from ColumnName INTO @ DataFieldName
PRINT 'document No.: '+ @ TypeNo
WHILE @ FETCH_STATUS = 0
BEGIN
PRINT 'listdetaildatafiled. Add (''' + @ DataFieldName + ''');'
Fetch next from ColumnName INTO @ DataFieldName
END
CLOSE ColumnName
DEALLOCATE ColumnName
Fetch next from BillMsgCursor INTO @ TypeNo, @ DetailName
END
CLOSE BillMsgCursor
DEALLOCATE BillMsgCursor

@ Fetch_status: 0 indicates that the FETCH statement is successful.-1 indicates that the FETCH statement fails or this row is not in the result set.-2 indicates that the extracted row does not exist.
FETCH [NEXT/PRIOR/FIRST/LAST] FROM...
16. cursor Classification
1) static cursor (static): it is equivalent to a temporary table and will be saved in a private table in tempdb, just like copying a snapshot table.
A. Once a cursor is created, it is separated from the actual record and no locks are maintained.
B. The cursor is independent and no longer associated with the original data in any way.
2) keyset-driven cursor (keyset): You must be aware of the changes to the data to a certain extent, but you do not need to understand all the latest inserts.
A. the table must have a unique index.
B. Only the key set is in tempdb, not the entire dataset, which has a favorable impact on the performance of the entire server.
C. You are aware of the modifications (changes and deletions) made to the rows that are part of the key set. You are not aware of the addition.
3) DYNAMIC Cursor (DYNAMIC)
A. Completely dynamic and very sensitive. All the operations on the underlying data will affect the performance, of course, the worst.
B. They will cause extra concurrency Problems
C. Every time a FETCH is sent, the cursor must be rebuilt.
D. You can run a table without a unique index. However, SQL Server cannot track its position in the cursor, leading to an endless loop.
4) Fast Forward cursor (FAST_FORWARD)
In many cases, the FAST_FORWARD cursor is implicitly converted to another cursor type.