title: Does SQL Server creates Non clustered index by default
body: <p>Ya, it is <strong>a duplicate</strong> of <a href="https://stackoverflow.com/questions/7781088/does-sql-server-creates-non-clustered-index-on-all-columns-of-a-table-by-default">this</a>. But I just needs a clarification on <a href="http://blog.sqlauthority.com/2013/02/10/sql-server-primary-key-and-nonclustered-index-in-simple-words/" rel="noreferrer">this article</a> by <strong>Pinal Dave</strong>, which says the following:</p>
  
  <blockquote>
    <p>Scenario 4: Primary Key defaults to Clustered Index with other index
    defaults to Non-clustered index</p>
    
    <p>In this case we will create two indexes on the both the tables but we
    will not specify the type of the index on the columns. When we check
    the results we will notice that Primary Key is automatically defaulted
    to Clustered Index and another column as a Non-clustered index.</p>
  
  <pre><code>-- Case 4 Primary Key and Defaults
  USE TempDB
  GO
  -- Create table
  CREATE TABLE TestTable
  (ID INT NOT NULL PRIMARY KEY,
  Col1 INT NOT NULL UNIQUE)
  GO
  -- Check Indexes
  SELECT OBJECT_NAME(OBJECT_ID) TableObject,
  [name] IndexName,
  [Type_Desc]
  FROM sys.indexes
  WHERE OBJECT_NAME(OBJECT_ID) = 'TestTable'
  GO
  -- Clean up
  DROP TABLE TestTable
  GO
  </code></pre>
  </blockquote>