title: Why would a SQL query return rows un-ordered even with an ORDER BY clause?
body: <p>Everything I've read says this shouldn't happen, but it happens. My database is roughly 15GB in size, few hundred tables, tables can have anywhere from a few rows to a few million.</p>
  
  <p>Here is what happens: Let's say I have a table called <code>orders</code> and the first column is called <code>ID</code> and is the primary key for the table. There are many other columns, one of which is named <code>policy</code>. If I run the following query:</p>
  
  <pre><code>SELECT * 
  FROM orders 
  WHERE policy = 12345 
  ORDER BY ID
  </code></pre>
  
  <p>I would expect the result to look something like this:</p>
  
  <pre><code>1
  2
  3
  4
  5
  6
  </code></pre>
  
  <p>But sometimes the result will look like this:</p>
  
  <pre><code>1
  2
  4
  5
  3
  6
  </code></pre>
  
  <p>It's as if the <code>3</code> was moved to a different position. How/why would this happen?</p>
  
  <p>The actual query is</p>
  
  <pre><code>SELECT * FROM loc_info WHERE PolInfo_ID=25634 ORDER BY LocInfo_ID
  </code></pre>
  
  <p>The table definition is</p>
  
  <pre><code>CREATE TABLE [dbo].[loc_info]( 
      [LocInfo_ID] [int] IDENTITY(1,1) NOT NULL, 
      [PolInfo_ID] [int] NOT NULL, 
      [name] [varchar](100) NOT NULL, 
      [address1] [varchar](100) NULL, 
      [address2] [varchar](100) NULL, 
      [city] [varchar](100) NULL, 
      [state] [varchar](100) NOT NULL, 
      [zip] [char](10) NULL, 
      [county] [varchar](100) NULL, 
      [country] [varchar](100) NULL, 
      [loc_number] [varchar](20) NULL, 
      [occ_type_id] [int] NULL, 
      [occ_type] [varchar](100) NULL, 
      [flood_zone] [varchar](6) NOT NULL, 
      [coastal_zone] [varchar](20) NOT NULL, 
      [earthquake_zone] [varchar](20) NOT NULL, 
      [earthquake_group] [varchar](20) NULL, 
      [BuildingTIV] [numeric](18, 0) NULL, 
      [MachEquipTIV] [numeric](18, 0) NULL, 
      [StocksSuppliesTIV] [numeric](18, 0) NULL, 
      [OtherTIV] [numeric](18, 0) NULL, 
      [BusinessInterruptTIV] [numeric](18, 0) NULL, 
      [ExtraExpTIV] [numeric](18, 0) NULL, 
      [RentTIV] [numeric](18, 0) NULL, 
      [Property] [numeric](18, 0) NULL, 
      [IsUpload] [bit] NULL, 
      [IsMoved] [bit] NULL, 
   CONSTRAINT [PK_locations] PRIMARY KEY CLUSTERED  
  ( 
      [LocInfo_ID] ASC
  )WITH (PAD_INDEX  = OFF
  , STATISTICS_NORECOMPUTE  = OFF
  , IGNORE_DUP_KEY = OFF
  , ALLOW_ROW_LOCKS  = ON
  , ALLOW_PAGE_LOCKS  = ON
  , FILLFACTOR = 90) ON [PRIMARY] 
  ) 
  </code></pre>