SQL Server T-SQL Query Learning Notes (1) _mssql2005

The SELECT clause is logically the last step in the final processing of the SQL statement, so the following query will have an error:

SELECT year
 (OrderDate asorderyearCOUNT(DISTINCT CustomerID fromdboGROUP by OrderYear;        

Because group by was preceded by a SELECT, the OrderYear column was not formed at that time.

If you want to query for success, you can modify it as follows:

SELECT orderyearCOUNT(DISTINCT CustomerID(OrderDate asorderyearfrom dbo. OrdersGROUP by OrderYear;          

There is also a very special way of writing:

SELECT orderyearCOUNT(DISTINCT CustomerID(OrderDate from dbo). OrdersD(orderyear, CustomerID)GROUP by OrderYear;           

In the author's eyes, he is very fond of this writing, because clearer, clearer, easier to maintain.

Using parameter orientation in a query produces a batch of results, this technique has nothing to say.

Nested queries, which are logically executed from the inside out.

Multiple references, it is possible that your SQL statement contains multiple queries from one table after the combination of the connection. For example, you have to compare the number of customers with the number of years before the year before the changes, so your query must join 2 of the same table instance, which is also unavoidable.

Common Table Expressions (CTE)

A CTE is a representation type of a table that is newly added to the SQL2005.

It is defined as follows:

With Cte_name

As

(

Cte_query

)

Outer_query_refferring To_cte_name;

Note: Because the WITH keyword is already contained in the standard T-SQL language, the CTE adds the word ";" at the end of the statement in order to differentiate. As a stop character.

CTE instance One (result set alias)

Year(OrderDate asorderyear fromdboSELECT orderyearCOUNT(DISTINCT CustomerID  GROUP by OrderYear;        

Of course, the author himself has a more recommended wording:

With C(orderyear, CustomerID-OrderDate fromdboSELECT orderyearCOUNT(  DISTINCT CustomerIDGROUP by OrderYear;          

CTE Instance II (multiple CTEs)

Year(OrderDate asorderyear fromdboSELECT orderyearCOUNT(DISTINCT CustomerID  SELECT orderyear>;         

CTE instance three (multiple references)

Year(OrderDate asorderyearCOUNT(DISTINCT CustomerID fromdbo year(OrderDate SELECT Cur. OrderYear, Cur ascurnumcusts, Prv asprvnumcusts, Cur-Prv onCur= Prv+ 1 ; 

CTE instance four (modify data)

1. The results from the Customer table query, dynamic assembly into the new table customersdups:

  IF  object_id  ( ' dbo. Customersdups '  is not NULL  DROP TABLE dbo . Customersdups ; Go  with Crosscustomers  as  ( SELECT 1  as C , C1 . *  from dbo . Customers  as C1 , dbo . Customers  as C2 )  SELECT  row_number  ()  over  ( order by C )  as KeyCol , CustomerID , CompanyName , ContactName , ContactTitle ,  address , city , Region , PostalCode , Country , Phone , Fax  into dbo . Customersdups  from Crosscustomers ;             

2. Use the CTE to remove the data, leaving only the keycol of the same CustomerID in the Customerdups table as the largest record.

From the dbo<MAX(keycol fromdboWHERE C2= C1. CustomerIDDELETE from justdups;        

CTE instance five (object container)

That is, it provides the ability of encapsulation, which facilitates the programming of component. The author added that the CTE cannot be embedded directly, but it can be embedded by encapsulating the CTE into an object container and querying the container's data from an external CTE.

The author also explains that using CTEs in view and UDFs is of little value.

There is an example, as follows:

CREATE VIEW dbo Year(OrderDate asorderyearCOUNT(DISTINCT CustomerID From the dbo year(OrderDate fromyearcnt;          

CTE Instance VI (recursion of ctes)

The author gives an example to describe this new content in SQL2005, the CTEs recursion.

According to EmployeeID, the information for this employee is returned and contains information for all subordinate employees. (The hierarchical relationship is based on Empolyeeid and ReportsTo properties) The result returned contains the following fields, Employeeid,reportsto,firstname,lastname.

Here, the author gives an optimal indexing method:

CREATE UNIQUE INDEX idx_mgr_emp_ifname_ilname on
 dbo. Employees(ReportsTo, EmployeeIDINCLUDE(FirstName, LastName);       

Author's explanation: This index will be a separate query (local scan) to obtain the direct subordinate of each manager. Include (Fristname,lastname) is added here, that is, overwrite columns.

Little knowledge: What include index?

The include index is a new feature of the SQL2005. The columns of the include index do not affect the physical storage order of the indexed rows, and they are ' hanging on ' the index line as a pendant. The purpose of hanging these ' pendants ' is to get these additional data only by scanning an index.

Back to the example of the author, here is the recursive code:

Select EmployeeID, ReportsTo, FirstName from thedboUNION all SELECT EMP. EmployeeID, EMP. ReportsTo, EMP. FirstName, EMPJOIN dbo onemp= MGR fromempscte;       

Understanding: A recursive CTE contains at least 2 queries, and the first query resembles a grid anchor in the body of a CTE. This anchor only returns a valid table and serves as a recursive anchor. As you can see from the example above, the anchor point simply returns a row of EmployeeID = 5. Then the 2nd query is as a recursive member. This recursion ends when the result of the query to the subordinate member is empty.

If you are concerned that recursion can cause a permanent loop, you can use the following expression:

With Cte_name as (cte_body) outer_query OPTION (maxrecursion N);

The default n is 100, unrestricted when n=0.