By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,491 Members | 1,169 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,491 IT Pros & Developers. It's quick & easy.

strange query result, (i must be doing something wrong)

Expert 100+
P: 997
Expand|Select|Wrap|Line Numbers
  1. SELECT id, int1 
  2. FROM testInt 
  3. WHERE int1 IN (1,2,346,596,597)
  4. ORDER BY int1, id
  5.  
  6. SELECT id, int1 
  7. FROM testInt 
  8. WHERE int1 IN (1,597)
  9. ORDER BY int1, id
  10.  
  11. SELECT id, int1 
  12. FROM testInt 
  13. WHERE id in (5152426,6301653,1308712,5518275,6121956,7410050)
  14. ORDER BY int1, id
  15.  
this gives next results
Expand|Select|Wrap|Line Numbers
  1. id          int1
  2. ----------- -----------
  3. 5152426     2
  4. 6301653     2
  5. 1308712     346
  6. 5518275     346
  7. 6121956     596
  8. 7410050     596
  9.  
  10. (6 rows affected)
  11.  
  12.  
  13.  
  14. (1 row affected)
  15. id          int1
  16. ----------- -----------
  17.  
  18. (0 rows affected)
  19.  
  20.  
  21.  
  22. (1 row affected)
  23. id          int1
  24. ----------- -----------
  25. 5152426     2
  26. 6301653     2
  27. 1308712     346
  28. 5518275     346
  29. 6121956     596
  30. 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
Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE [dbo].[testInt](
  2.     [id] [int] NOT NULL,
  3.     [description] [nvarchar](50) NULL,
  4.     [int1] [int] NULL,
  5.     [int2] [int] NULL,
  6.  CONSTRAINT [PK_testInt] PRIMARY KEY CLUSTERED 
  7. (
  8.     [id] ASC
  9. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  10. ) ON [PRIMARY]
  11. GO
  12. CREATE NONCLUSTERED INDEX [NonClusteredIndex-20190309-125532] ON [dbo].[testInt]
  13. (
  14.     [int1] ASC
  15. )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]
  16. GO
  17.  
  18.  
1 Week Ago #1

✓ answered by Rabbit

It seems to me that the int1 values were inserted multiple times with the same value. Since the additional index created on int1 doesn't specify unique, nothing prevents someone from doing that.

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,315
It seems to me that the int1 values were inserted multiple times with the same value. Since the additional index created on int1 doesn't specify unique, nothing prevents someone from doing that.
1 Week Ago #2

Expert 100+
P: 997
Indeed
Expand|Select|Wrap|Line Numbers
  1. with tmp as (
  2.     SELECT 1 as tmp
  3.     union
  4.     select 2
  5.     union 
  6.     select 346
  7.     union
  8.     select 596
  9.     union 
  10.     select 597
  11. )
  12. SELECT tmp.tmp, id, int1 
  13. FROM tmp
  14. LEFT OUTER JOIN testInt on int1 = tmp.tmp
  15. ORDER BY int1, id
  16.  
shows:
Expand|Select|Wrap|Line Numbers
  1. tmp    id    int1
  2. 1    NULL    NULL
  3. 597    NULL    NULL
  4. 2    5152426    2
  5. 2    6301653    2
  6. 346    1308712    346
  7. 346    5518275    346
  8. 596    6121956    596
  9. 596    7410050    596
1 Week Ago #3

Post your reply

Sign in to post your reply or Sign up for a free account.