Performance of SQL Server indexing I want to see the performance differences between an indexed vs non-indexed column when running the same query.

The table I've created (FileCollection) is very simple and has the following 3 columns


Id (int) - Primary key which auto generates the number.
FileName (nvarchar(MAX)) - Contains a file name.
Description (nvarchar(MAX)) - Contains a description of the file.


The query I'm using is:

SELECT * FROM FileCollection WHERE FileName='readme.txt'


I've populated the database with more than 100k records. Using the SQL Server Profiler tool, I initially ran the query with no index on the FileName column and again when adding the index to FileName column. However, I cannot see any difference in performance.

Both tests average around the same stats:
CPU: 500
Reads: 13,000
Writes: 0
Duration: 200

Can anyone help me figure out why? I would assume 100k records is enough to show some difference but do I need more records?

TIA

Edit:
I've now changed the FileName column to nvarchar(255) but still the problem exists. BTW, I'm using SQL Server 2008 R2.
I'm pretty sure you're leaving something out:

(n)varchar(max) fields CANNOT BE INDEXED

From MSDN:


  Columns that are of the large object (LOB) data types ntext, text,
  varchar(max), nvarchar(max), varbinary(max), xml, or image cannot be
  specified as key columns for an index. Also, a view definition cannot
  include ntext, text, or image columns, even if they are not referenced
  in the CREATE INDEX statement.


Did you get an error when you tried to create the index?

Both your queries are the same because they are the same - since you can't index that field, they are both creating table scans.