| Expert 100+
P: 1,035
| - SELECT id, int1
-
FROM testInt
-
WHERE int1 IN (1,2,346,596,597)
-
ORDER BY int1, id
-
-
SELECT id, int1
-
FROM testInt
-
WHERE int1 IN (1,597)
-
ORDER BY int1, id
-
-
SELECT id, int1
-
FROM testInt
-
WHERE id in (5152426,6301653,1308712,5518275,6121956,7410050)
-
ORDER BY int1, id
-
this gives next results - id int1
-
----------- -----------
-
5152426 2
-
6301653 2
-
1308712 346
-
5518275 346
-
6121956 596
-
7410050 596
-
-
(6 rows affected)
-
-
-
-
(1 row affected)
-
id int1
-
----------- -----------
-
-
(0 rows affected)
-
-
-
-
(1 row affected)
-
id int1
-
----------- -----------
-
5152426 2
-
6301653 2
-
1308712 346
-
5518275 346
-
6121956 596
-
7410050 596
As you can see
- There is no record for int1 with value =1
- Why does the first query return two records with value=2?
- There is no record for int1 with value =597
- Why does the first query return two records with value=596?
The create script for this table is - CREATE TABLE [dbo].[testInt](
-
[id] [int] NOT NULL,
-
[description] [nvarchar](50) NULL,
-
[int1] [int] NULL,
-
[int2] [int] NULL,
-
CONSTRAINT [PK_testInt] PRIMARY KEY CLUSTERED
-
(
-
[id] ASC
-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-
) ON [PRIMARY]
-
GO
-
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190309-125532] ON [dbo].[testInt]
-
(
-
[int1] ASC
-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
-
GO
-
-
| |
