Databases
StackOverflow2013
Postsdbo
POunable to select certain records in TSQL without using a range, between, etc
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POunable to select certain records in TSQL without using a range, between, etc
    primarykey
    data
    text
    <p>On a SQL Server 2008 R2 database I have a table with a single integer as a primary key. Sometimes when I perform a select on the primary key I get no results, but if I do a search for all records on the primary key between certain values I am able to retrieve the row. This only affects a few records (less than ten out of millions of rows). The vast majority of records can be selected with a simple "equals" statement.</p> <p>Does anyone know what could be causing this or how to fix the problem? I suspect something may be wrong with the clustered index. If I use the hint "index(0)" then I can get the record without using a range.</p> <p><strong>Details:</strong></p> <p>Statement 1 produces no results:</p> <pre><code>select * from claim where claim_id = 12166769 </code></pre> <p>Statement 2 produces one result for claim_id 12166769:</p> <pre><code>select * from claim where claim_id &gt; 12166768 and claim_id &lt; 12166770 </code></pre> <p>Both statements use a Clustered Index Seek using PK_claim_claim_id. </p> <p>Statement 3 produces one result for claim_id 12166769 using a hint:</p> <pre><code>select * from claim with (index(0)) where claim_id = 12166769 </code></pre> <p>The claim table has a column named "claim_id" defined as the primary key, integer, not null. It has a clustered index on the primary key named "PK_claim_claim_id".</p> <p>I'm using SQL Sentry Plan Explorer to view the Plan Tree. Statement 1 has 1 Estimated Row and 0 Actual rows. Statement 2 has 7 Estimated rows and 1 Actual row.</p> <p><strong>Update</strong> Additionally we found that when we ran </p> <pre><code>select * from claim where claim_id &gt; 12166774 and claim_id &lt; 12166775 </code></pre> <p>we came up five rows returned, 12166770 through 12166774. Running </p> <pre><code>select * from claim with (index(0)) where claim_id &gt; 12166774 and claim_id &lt; 12166775 </code></pre> <p>with the hint returned no records as expected.</p> <p><strong>6/22/2012 Update</strong></p> <p>I spoke too soon. We found this error in a different spot. I found that I hadn't actually scanned the entire database, only a portion of it. </p> <pre><code>select * from claim where claim_id in (8223749,8223752,8223753,8223754,8223755) </code></pre> <p>returns no rows, while</p> <pre><code>select * from claim with (index(0)) where claim_id in (8223749,8223752,8223753,8223754,8223755) </code></pre> <p>returns rows. I ran </p> <pre><code>SELECT sys.fn_PhysLocFormatter(%%physloc%%),claim_id FROM claim with (index(0)) WHERE claim`_id in (8223749,8223752,8223753,8223754,8223755) </code></pre> <p>and received</p> <pre><code>(3:1394868:2) 8223749 (3:1394868:5) 8223752 (3:1394868:6) 8223753 (3:1394868:7) 8223754 (3:1394868:8) 8223755 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USNarthring
    1. USNarthring
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POunable to select certain records in TSQL without using a range, between, etc
      1. USRThomas
      1. VTFavorite
    2. VO
      singulars
      1. POunable to select certain records in TSQL without using a range, between, etc
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODBCC CHECKTABLE ( table_name | view_name [ , { NOINDEX | index_id } |, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ] )
      singulars
      1. POunable to select certain records in TSQL without using a range, between, etc
      1. USpaparazzo
    2. COWe have DBCC CHECKDB scheduled to run weekly and it reported no errors yesterday, we'll run DBCC CHECKTABLE tonight and see what it comes up with.
      singulars
      1. POunable to select certain records in TSQL without using a range, between, etc
      1. USNarthring
    3. COOnly way I can think of debugging this would be to start at the index root and use either SQL Server Internals Viewer or `DBCC IND` and `DBCC PAGE` to follow the pointers down and see where it takes you.
      singulars
      1. POunable to select certain records in TSQL without using a range, between, etc
      1. USMartin Smith
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload