The things about SQL Server

have been busy with the data, writing a variety of corresponding SQL, in the Guide data encountered a variety of problems. Some things have not been used for a long time, most of them are almost forgotten, it is very hard to take a moment to comb.

A temporary table:

Temporary tables are created in SQL SERVER, and there are two types of temporary tables, one local temporary table: Beginning with #, the local temporary table is visible only in the current session, and the other is the global temporary table, in # #开头, and the global temporary table is visible in all sessions. the temporary table with the beginning of the with, can only be used after parentheses, generally for conditions more complex conditions, or sort, will often be used, the life cycle is the end of this sentence will be released. And the above two with # and # #开头的需要手动释放资源, the form is as follows:

1---Temp Table2 CREATE TABLE #Temp3     (4 ID INT,5Name NVARCHAR ( -) ,6 Age INT7     );8 9---Global Temp TableTen CREATE TABLE # #TempAll One     ( A ID INT, -Name NVARCHAR ( -) , - Age INT, the UID INT -     ); -  -----temporary table with start + with T as (SELECT a.id, - A.name + From #Temp as a AINNER JOIN # #TempAll as B on b.id =B.uid at              ) -SELECT * - From t; -  ----Releasing temporary tables - DROP TABLE #Temp; inDROP TABLE # #TempAll;

Second, parse, compile SQL

For the guide data, write a bunch of SQL, not easy to execute, it is best to perform before parsing, compiling, whether there is a problem. Provide SET parseonly {on | In SQL Server OFF} and set NOEXEC {on | OFF} statement.

SET parseonly means that statements are resolved but not compiled or executed. It checks the syntax of the TANSACT-SQL statement and returns any error messages. SET parseonly on to start parsing the statement, off to start compiling the execution statement.

The SET NOEXEC represents the compilation but does not execute the statement. You can have SQL Server validate the syntax and object names in your code when executing Transact-SQL code. It can also be used to debug portions of a statement that are typically large batches.

--- parsing set parseonly on; GO----- Check the statement select  * from    # #TempAll;         --- compile to execute set parseonly OFF; GO-------------------------------------------- compile set NOEXEC on; GO---- compiled statement  Select*    from-----

Third, to the weight

Data is often encountered in a variety of repetitive data, deduplication is also an important link. The commonly used method of de-weight, I only way three kinds, 1) distinct;2) groupy by; 3) row_number () over sort to go heavy. Each has its own merits and demerits.

------- to ----1. Distinctselect DISTINCT        namefrom    #Temp; ----2. Group by Go to select  namefrom    #TempGROUP by namehaving  1; ---3. Sort de-heavy    with T as (SELECT   row_number () over (PARTITION by name ORDER by name DESC) as a, 
    * from     #Temp             )    SELECT  * from    t    WHERE    1;

Temporarily first write here, met, and then tidy up ...

The things about SQL Server