Have no idea how indexing works in SQL Server.

Working with a SQL Server 2012 Intel Xeon 2.2 GHz dual core 8 GB RAM.

Will indexing a nvarchar(128) in addition to int primary key cause any performance issue for a 1000 row table which has a nvarchar(max) as one of its columns?

This will be part of website that gets average 3000 hits a day.

  • 1
    Can you show us the CREATE TABLE DDL for the table in question? – Thomas Stringer Feb 5 '15 at 16:54
  • for only 1000 rows index will not boast the performance, sure it will beneficial if table size bigger. – Pawan Singh Feb 5 '15 at 16:57
  • You should definitely brush up on SQL Server Index Design to be more familiar with indexes in general. – LowlyDBA Feb 5 '15 at 17:00
  • 3
    For 3,000 queries against 1,000 rows (unless those queries are very oddly complex, such as many levels of recursive CTE and/or sub-query), indexing is going to make very little difference either way. For larger data and load patterns, see the reference linked by JohnM and use-the-index-luke.com/sql/table-of-contents, amongst others, for useful discussion about index design and use. – David Spillett Feb 5 '15 at 17:05
  • 2
    When a table is small (and/or the index isn't very selective - not the case here, since it's a PK), the optimiser may well decide that it's not worth bothering with using an index and just performs an FTS (full table scan). Figuring out what an optimiser will or will not do is frequently more of an art than a science - there are engire books written about this subject. – Vérace Feb 5 '15 at 17:54

When considering the impact of new index, the important things to think are how often rows are added to the table, is the indexed field getting updated (often) and how many distinct values there are in that field (selectivity).

For table this size, it's probably not going improve the performance that much, but if you're getting deadlocks it might help.

If you create the index, after it you can monitor the impact from "Index Usage Statistics" -report in SSMS:


From there you can see the number of times it's being used (scans & seeks) and the number of times it was needed to update.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.