title: Why do I have to include the primary key column in this insert statement?
body: <p>Given a SQL Server Express Compact Edition database with a table called "Clients" with the following columns:</p>
  
  <pre><code>ID, FirstName, MiddleName, Nickname, LastName, Suffix, IsMale
  </code></pre>
  
  <p>where ID is an auto-incrementing primary key / identity value, when I try to use this command on the table:</p>
  
  <pre><code>INSERT INTO Clients (Title, FirstName, MiddleName, Nickname, LastName, Suffix,
  IsMale) VALUES ('', 'John', '', '', 'Smith', '', 'True')
  </code></pre>
  
  <p>everything works.  But when I try to use this command:</p>
  
  <pre><code>INSERT INTO Clients VALUES ('', 'John', '', '', 'Smith', '', 'True')
  </code></pre>
  
  <p>this error shows up:</p>
  
  <pre><code>The number of columns in the query and the table must match. [ Number of columns
  in query = 7, Number of columns in table = 8 ]
  </code></pre>
  
  <p>I've been using insert statements with SQL Server for quite a while now, and this has never been a problem; the validation against the number of columns has always ignored auto-incrementing primary keys / identitity types.  The only major difference in this case is that I'm now using a very low-end version of SQL Server, whereas I'm used to using much more professional versions.  In case it might possibly matter, I am using this in a C# program.</p>
  
  <p>What would be causing this?   Thanks.</p>
  
  <p>EDIT: A lot of the focus of this question is the fact that I never have run into this problem when using the non-free, non-compact versions of SQL Server.  The SQL Servers I've used before have ALWAYS been smart enough to ignore the identity / primary key column in the column count, and to just figure out the auto-incremented value without me having to spell out specific column names.</p>
  
  <p>Although another difference here is that I've always used SQL Management Studio to mess with table definitions before, whereas I'm going through VS 2012 and SQL Server Compact Edition in this case.  The user interfaces involved are obviously pretty different, so I guess maybe there's something being specified for the table or something when you do it in Management Studio that keeps this problem from happening?</p>