SQL : sortări cu NULL

Lucrăm cu Microsoft SQL Server 2014 Enterprise (thanks, MSDNAA).

Să discutăm o problemă de sortare atunci când apare NULL.

Descărcaţi codulPentru vizualizare, folosiţi scrollbar-ul din josul blocului de cod.
/*
 PROBLEMA (Itzik Ben-Gan, "T-SQL Fundamentals 2008", Capitolul 2, Exercise 8, pag. 99):
 In mod obisnuit, in T-SQL, la sortare valorile NULL sunt prezentate primele
 (vezi op. cit., pag. 99, 61). Cum putem face ca valorile NULL sa apara ultimele?
*/

--constructia si popularea cu date a tabelului 'testare'
USE [tempdb];
DROP TABLE dbo.testing_nulls;

CREATE TABLE testing_nulls(
 number INT NOT NULL
 CONSTRAINT pk_number PRIMARY KEY
 ,extra INT NULL
 CONSTRAINT df_extra DEFAULT(0)
);
GO

INSERT INTO dbo.testing_nulls(number,extra)
VALUES(12,3)
,(23,NULL)
,(-234,654)
,(0,NULL)
,(231,DEFAULT)
,(2016,2016);
GO

--vizualizarea rezultatelor
--default: valorile NULL apar inaintea celorlalte
SELECT number,extra 
FROM dbo.testing_nulls
ORDER BY extra;


--curatam planul de executie (cache)
--vezi "https://msdn.microsoft.com/en-us/library/ms174283.aspx"
DBCC FREEPROCCACHE;
--solutie Itzik Ben-Gan (ibid.)
--(Actual) Execution plan arata "Memory Grant: 1024"
--info despre "Memory Grant":
  ----vezi "https://blogs.msdn.microsoft.com/sqlqueryprocessing/2010/02/16/understanding-sql-server-memory-grant/"
  ----vezi "http://stackoverflow.com/questions/28402317/sql-server-how-do-i-reduce-the-amount-of-memory-granted-by-stored-procedure"
SELECT number,extra
FROM dbo.testing_nulls
ORDER BY
CASE
 WHEN extra IS NULL THEN -2 --constante intregi oarecare
 ELSE -3
END
,extra;

DBCC FREEPROCCACHE;
--solutie fara "Memory Grant" in Execution plan.

WITH i_m_testing_the_nulls(zero,one,two)
AS
(
  SELECT 0
  ,number
  ,extra
  FROM dbo.testing_nulls
  WHERE extra IS NULL
)
,i_m_testing_the_not_nulls(three,four,five)
AS
(
  SELECT 1
  ,number
  ,extra
  FROM dbo.testing_nulls AS the_first_one
  WHERE NOT EXISTS (
    SELECT 2
    FROM i_m_testing_the_nulls AS the_second_one
    WHERE the_first_one.number = the_second_one.one
  )
  /*
   Pitfall: "NOT IN" versus "NOT EXISTS"
   ---vezi "http://sqlperformance.com/2012/12/t-sql-queries/left-anti-semi-join"
   Altfel (cititi despre capcana, mai intai):
      WHERE number NOT IN (SELECT one FROM i_m_testing_the_nulls)
   */
)
,the_result
AS
(
  SELECT zero AS the_counter
  ,one AS main_number
  ,two AS secondary_number
  FROM i_m_testing_the_nulls
UNION ALL
  SELECT three
  ,four
  ,five
  FROM i_m_testing_the_not_nulls
)
SELECT main_number,secondary_number
FROM the_result
ORDER BY the_counter DESC;

Planurile de execuţie au fost reconstituite cu Plan Explorer.

planuri de executie