title: SQL Server 2008 Performance: No Indexes vs Bad Indexes?
body: <p>i'm running into a strange problem in Microsoft SQL Server 2008.
  I have a large database (20 GB) with about 10 tables and i'm attempting to make a point regarding how to correctly create indexes. </p>
  
  <p>Here's my problem: on some nested queries i'm getting faster results <strong>without</strong> using indexes! It's close (one or two seconds), but in some cases using no indexes at all seems to make these queries run faster... I'm running a Checkpoiunt and a DBCC dropcleanbuffers to reset the caches before running the scripts, so I'm kinda lost.</p>
  
  <p>What could be causing this?
  I know for a fact that the indexes are poorly constructed (think one index per relevant field), the whole point is to prove the importance of constructing them correctly, but it should <strong>never</strong> be slower than having no indexes at all, right?</p>
  
  <p>EDIT: here's one of the guilty queries:</p>
  
  <pre><code>SET STATISTICS TIME ON
  SET STATISTICS IO ON
  
  USE DBX;
  GO
  CHECKPOINT;
  GO
  DBCC DROPCLEANBUFFERS;
  GO
  DBCC FREEPROCCACHE;
  GO
  
  SELECT * FROM Identifier where CarId in (SELECT CarID from Car where ManufactId = 14) and DataTypeId = 1
  </code></pre>
  
  <p>Identifier table:
  - IdentifierId int not null
  - CarId int not null
  - DataTypeId int not null
  - Alias nvarchar(300)</p>
  
  <p>Car table:
  - CarId int not null
  - ManufactId int not null
  - (several fields followed, all nvarchar(100)</p>
  
  <p>Each of these bullet points has an index, along with some indexes that simultaneously store two of them at a time (e.g. CarId and DataTypeId). </p>
  
  <p>Finally, The identifier table has over million entries, while the Car table has two or three million</p>