· 7 years ago · Jan 09, 2019, 08:28 AM
1IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('t') AND type in (N'U'))
2DROP TABLE t
3GO
4
5CREATE TABLE t
6(
7 c1 int IDENTITY(1,1) NOT NULL
8,c2 int NULL
9)
10GO
11
12insert into t
13select top 1000000 a from
14(select t1.number*2048 + t2.number a, newid() b
15from [master]..spt_values t1
16cross join [master]..spt_values t2
17where t1.[type] = 'P' and t2.[type] = 'P') a
18order by b
19GO
20
21update t set c2 = null
22where c2 < 2048 * 2048 / 10
23GO
24
25
26CREATE CLUSTERED INDEX pk ON [t] (c1)
27GO
28
29CREATE NONCLUSTERED INDEX i ON t (c2)
30GO
31
32select *
33from t
34where
35 c2 < 1048576
36 or c2 is null
37;
38
39|--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1010], [Expr1011], [Expr1012]))
40 |--Merge Interval
41 | |--Sort(TOP 2, ORDER BY:([Expr1013] DESC, [Expr1014] ASC, [Expr1010] ASC, [Expr1015] DESC))
42 | |--Compute Scalar(DEFINE:([Expr1013]=((4)&[Expr1012]) = (4) AND NULL = [Expr1010], [Expr1014]=(4)&[Expr1012], [Expr1015]=(16)&[Expr1012]))
43 | |--Concatenation
44 | |--Compute Scalar(DEFINE:([Expr1005]=NULL, [Expr1006]=NULL, [Expr1004]=(60)))
45 | | |--Constant Scan
46 | |--Compute Scalar(DEFINE:([Expr1008]=NULL, [Expr1009]=(1048576), [Expr1007]=(10)))
47 | |--Constant Scan
48 |--Index Seek(OBJECT:([t].[i]), SEEK:([t].[c2] > [Expr1010] AND [t].[c2] < [Expr1011]) ORDERED FORWARD)
49
50Expr1005 Expr1006 Expr1004
51----------- ----------- -----------
52NULL NULL 60
53
54Expr1008 Expr1009 Expr1007
55----------- ----------- -----------
56NULL 1048576 10
57
58Expr1010 Expr1011 Expr1012
59----------- ----------- -----------
60NULL NULL 60
61NULL 1048576 10
62
63Expr1010 Expr1011 Expr1012 Expr1013 Expr1014 Expr1015
64----------- ----------- ----------- ----------- ----------- -----------
65NULL NULL 60 True 4 16
66NULL 1048576 10 False 0 0
67
68[Expr1013] = Scalar Operator(((4)&[Expr1012]) = (4) AND NULL = [Expr1010]),
69[Expr1014] = Scalar Operator((4)&[Expr1012]),
70[Expr1015] = Scalar Operator((16)&[Expr1012])
71
72+----------+----------+----------+-------------+----+----+---+---+---+---+
73| Operator | Expr1010 | Expr1011 | Flags (Dec) | Flags (Bin) |
74| | | | | 32 | 16 | 8 | 4 | 2 | 1 |
75+----------+----------+----------+-------------+----+----+---+---+---+---+
76| > | 1048576 | NULL | 6 | 0 | 0 | 0 | 1 | 1 | 0 |
77| >= | 1048576 | NULL | 22 | 0 | 1 | 0 | 1 | 1 | 0 |
78| <= | NULL | 1048576 | 42 | 1 | 0 | 1 | 0 | 1 | 0 |
79| < | NULL | 1048576 | 10 | 0 | 0 | 1 | 0 | 1 | 0 |
80| = | 1048576 | 1048576 | 62 | 1 | 1 | 1 | 1 | 1 | 0 |
81| IS NULL | NULL | NULL | 60 | 1 | 1 | 1 | 1 | 0 | 0 |
82+----------+----------+----------+-------------+----+----+---+---+---+---+
83
84ORDER BY
85 HasStartOfRangeAndItIsNullFirst,
86 HasUnboundedStartOfRangeFirst,
87 StartOfRange,
88 StartOfRangeIsInclusiveFirst
89
90Expr1010 Expr1011 Expr1012
91----------- ----------- -----------
92NULL NULL 60
93NULL 1048576 10
94
95Seek Keys[1]: Start:[dbo].[t].c2 > Scalar Operator([Expr1010]),
96 End: [dbo].[t].c2 < Scalar Operator([Expr1011])
97
98select *
99from t
100where
101 c2 > 1048576
102 or c2 = 0
103;
104
105Start: c2 >= 0, End: c2 <= 0,
106Start: c2 > 1048576