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?
Edit: I've now changed the FileName column to nvarchar(255) but still the problem exists. BTW, I'm using SQL Server 2008 R2.