1. Rank function with partition by
Copy Code code as follows:
--All data
SELECT * from Dbo.student as a INNER JOIN dbo. Scoretb as B on a.id = B.stuid
WHERE scorename = ' language '
-------------------------------------------
--row_number () Use build columns are incremented 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 = ' language '
--You can also add an order by, which means that all the columns that were previously generated are rearranged with the last column (the Rank column value is 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 = ' language ' ORDER by a.id
--To use the Rank function after grouping statistics, first group, use CTE or nested query table to complete the result set, then use the Row_number function to deal with
With B as
(
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
----------------------------------------------------------------------------
The--rank () usage is the same as the Row_numer function, except that the rank is set to be the same when the order by sibling appears, and the next one generates an ordinal based on the number of previous records
--for example, the first three are the same, then all are 1, the next is 4, and the example is slightly
----------------------------------------------------------------------------
----------------------------------------------------------------------------
The--dense_rank () dense rank usage is the same as the Row_numer, rank function, but is continuous when the ordinal is generated, and the ordinal number generated by the rank function may be discontinuous
--for example, the first three are the same, then all are 1, the next is 2, and the example is slightly
----------------------------------------------------------------------------
----------------------------------------------------------------------------
The--ntile function can group ordinal processing. This is equivalent to putting the query's recordset into an array of specified lengths, each containing a certain number of records.
-The ordinal number generated for each record is the index of all of the array elements in this record (starting from 1). You can also call the array element of each assigned record "bucket."
--it has a parameter that specifies the number of buckets, such as
----------------------------------------------------------------------------
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 = ' language '
--------------------------------------------------------------------------
--partition by is similar to applying a group by to a rank function, grouping each group individually
--------------------------------------------------------------------------
The ranking of the statistics of each subject is as follows:
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. Top New usage
Copy Code code as follows:
DECLARE @num INT = 101
SELECT Top (@num) * from Student ORDER by Id--must be enclosed in parentheses
SELECT Top (@num) percent * The Student order by Id-accepts only the number between float and 1-100, and if passed in, the error occurs
3. Group BY all field/Group By field
The former is a bit like a left join, the feeling of right join, the main difference between the two is reflected in the where conditions are filtered aggregate functions, will be crawled out into the query datasheet, but the aggregate function will be based on the type of return value of the default value of 0 or NULL to replace aggregate function return value
Of course, in terms of efficiency, the latter is better than the former, just as inner join is better than a left join.
4. Count (*)/count (0) with Count (field)
If there are no null values in the queried field, the two query criteria are no different, and if there is null, the latter is 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 for null values on a line-by-row basis, and Null does not count, reducing the overhead of logical reading, which improves performance
5. Use of top n with ties
Refer to Http://www.cnblogs.com/skynet/archive/2010/03/29/1700055.html
As an example
Select top 1 with ties * Student ORDER BY score Desc
Equivalent to
SELECT * FROM student where score= (select top 1 score from student ORDER by score Desc)
6. Apply operator
Copy Code code as follows:
View Code
--Preparing data
CREATE TABLE [dbo]. [Student] (
[ID] [int] NULL,
[Name] [varchar] (m) NULL
)
Go
INSERT into dbo. Student VALUES (1, ' John ')
INSERT into dbo. Student VALUES (2, ' Reese ')
INSERT into dbo. Student VALUES (3, ' Harry ')
INSERT into dbo. Student VALUES (4, ' Man of God ')
Go
CREATE TABLE [dbo]. [SCORETB] (
[Stuid] [INT] Null
[Scorename] [varchar] (m) NULL,
[Score] INT NULL
)
Go
INSERT into [Scoretb] VALUES (1, ' language ', 22)
INSERT into [Scoretb] VALUES (1, ' math ', 32)
INSERT into [Scoretb] VALUES (1, ' foreign language ', 42)
INSERT into [Scoretb] VALUES (2, ' language ', 52)
INSERT into [Scoretb] VALUES (2, ' math ', 62)
INSERT into [Scoretb] VALUES (2, ' foreign language ', 72)
INSERT into [Scoretb] VALUES (3, ' language ', 82)
INSERT into [Scoretb] VALUES (3, ' Math ', 92)
INSERT into [Scoretb] VALUES (3, ' foreign language ', 72)
--Creating table-valued functions
CREATE FUNCTION [dbo]. [Fgetscore] (@stuid int)
RETURNS @score TABLE
(
[Stuid] [INT] Null
[Scorename] [varchar] (m) NULL,
[Score] INT NULL
)
As
BEGIN
INSERT into @score
SELECT Stuid,scorename,score from Dbo.scoretb WHERE stuid = @stuid
return;
End
Go
--Start using
SELECT a.id,a.name,b.scorename,b.score from [Student] A
CROSS APPLY [dbo]. [Fgetscore] (a.id) B--equivalent to inner join effect
SELECT a.id,a.name,b.scorename,b.score from [Student] A
OUTER APPLY [dbo]. [Fgetscore] (a.id) B--equivalent to left join effect
--and cannot be used like this
--select a.id,a.name,b.scorename,b.score from [Student] A
--INNER JOIN [dbo]. [Fgetscore] (a.id) B on a.id = B.stuid
--SELECT A.id,a.name,b.scorename,b.score from [Student] A
--INNER JOIN (SELECT * FROM [dbo].[ Fgetscore] (a.id)) B on a.id = B.stuid
7. Intersect and except operators
EXCEPT contains only those rows where the Excpet keyword is left and the result set on the right does not exist INTERSECT contains only two rows that exist in the result set
Often exists keywords can replace the above keywords, and can be seen from performance better than them, but except/intersect easier to read and intuitive. It is also recommended to start with better performance.
8. Index to improve query efficiency principle
Indexes are similar to the exists operators in that they can exit a query immediately after finding a match, which improves query performance
9. Table variables and temporary tables
Main difference: 1 table variables do not write log, no statistics, frequent changes do not cause stored procedures to recompile, cannot build indexes and statistics, but can establish a primary key, to adapt to the index lookup, table variables not only in memory operation, the large amount of data will also write tempdb, that is, physical disk IO operation. 2. Transaction rollback is not valid for table variables (reason no statistics)
In general, a large amount of data, the temporary result set needs and other table two times association with the small amount of temporary table data, separate operation of temporary result set with table variables
10. Scripting and Batch processing
Go is not a T-SQL command, he can only be compiled tools management Studio, sqlcmd recognition, if using third-party tools, does not necessarily support the GO command. such as Ado.net,ado.
the use of sqlcmd
Sqlcmd-usa-psa-q "SELECT * from TESTDB.dbo.mytable"
Sqlcmd-usa-psa-i testsql.sql The SQL statement in the run file
EXEC Usage Instructions
After exec, you can use a variable such as @ @ROWCOUNT to view the number of affected rows; You cannot run functions on the exec string, such as cast (XX as VARCHAR), for exec strings, in exec parameters, but only strings, or a whole string.
the meaning of WAITFOR
WAITFOR time < ' time ' > timed execution; WAITFOR DELAY < ' time ' > deferred execution
14. Summary of stored procedures
1 with try/catch substitution @ @ERROR This more scientific, one of the @ @ERROR no tra/catch intuitive, the second encountered error level in 11-19 error, the error will cause a direct interruption of the operation, resulting in the @ @ERROR judge error or not invalid.
2) using RAISERROR to throw the wrong
With LOG, this option is required when the severity level is greater than or equal to 19 o'clock
With SetError so that it overrides the @ @ERROR value to facilitate external invocation
With nowait immediately notifies the client of the error
15. Review of Cursors
Cursor main part includes: 1) Declaration 2 Open 3) use or navigation 4 to close 5) release
Examples of nested use-tour markers
Copy Code code as follows:
DECLARE Billmsgcursor CURSOR for
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 for
SELECT name from syscolumns WHERE id = object_id (@DetailName)
OPEN ColumnName
FETCH NEXT from ColumnName into @DataFieldName
PRINT ' Document number: ' + @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 Value: 0 FETCH statement succeeded; -1 FETCH statement failed or the row is not in the result set;-2 The fetched row does not exist
FETCH [Next/prior/first/last] from ... into a scroll type for a cursor.
16. Classification of cursors
1 static cursor (static): the equivalent of a temporary table, which is saved in a private table in tempdb, as if the snapshot table copies a
A. Once a cursor is created, it is separated from the actual record and no more locks are maintained
B. Cursors are independent and are no longer associated with the original data in any way
2 Keyset-driven Cursors (keyset): Need to be aware of the changes to the data to some extent, but do not need to know all the latest occurrences of the insert
A. A table must have a unique index
B. Only keyset in tempdb, not the entire dataset, has a beneficial impact on the overall server performance
C. The ability to perceive changes made to a line that is part of a keyset (delete) and cannot perceive new
3 Dynamic Cursors
A. Full dynamic, very sensitive, all things done to the underlying data will be affected, and the performance is of course the worst
B. They create additional concurrency problems
C. To rebuild a cursor for every fetch you send
D. Can be allowed to run a table without a unique index, but the disadvantage of SQL Server can not trace it in the location of the cursor to cause a dead loop, you should avoid this use
4) Fast forward cursor (FAST_FORWARD)
In many cases, an fast_forward cursor is implicitly converted to another cursor type
