title: SQL performance advice
body: <p>I know that it can be a tricky question and that it highly depends on context.</p>
  
  <p>I have a database with a table containing around 10 millions lines (each line contains 4 varchar).
  There is an index non clustered on the field (a varchar) used for the where.</p>
  
  <p>I'm a bit confused because when selecting a line using a where on the indexed columns, it takes around a second to end.</p>
  
  <p>Any advices to improve this response time ? 
  Would an indexed clustered be a good solution here?</p>
  
  <p>Here is the table definition :</p>
  
  <pre><code>CREATE TABLE [dbo].[MYTABLE](
  [ID] [uniqueidentifier] NOT NULL DEFAULT (newid()),
  [CreationDate] [datetime] NOT NULL DEFAULT (getdate()),
  [UpdateDate] [datetime] NULL,
  [FIELD1] [varchar](9) NOT NULL,
  [FIELD2] [varchar](max) NOT NULL,
  [FIELD3] [varchar](1) NULL,
  [FIELD4] [varchar](4) NOT NULL,
  CONSTRAINT [PK_MYTABLE] PRIMARY KEY CLUSTERED 
  (
  [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
  </code></pre>
  
  <p>Here is the index definition :</p>
  
  <pre><code>CREATE UNIQUE NONCLUSTERED INDEX [IX_FIELD1] ON [dbo].[MYTABLE]
  (
  [FIELD1] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
  </code></pre>
  
  <p>And here is the query I use (very basic) :</p>
  
  <pre><code>SELECT * FROM MYTABLE WHERE FIELD1 = 'DATA'
  </code></pre>