Databases
StackOverflow2013
Postsdbo
POWhat is the most efficient way to index in SQL Server on three nchar columns in this case?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POWhat is the most efficient way to index in SQL Server on three nchar columns in this case?
    text
    copied!<p><strong>Abstract:</strong><br> I'm creating a new table in SQL Server 2012 as part of a business system design. The table has 3 columns (among others) with type of <code>nvarchar</code> of various sizes, my web app needs to query those 3 columns using a single string search term. This table could contain records no more than 100K. </p> <p>I'd like to index these columns in SQL Server 2012 in a way so that it would be most efficient to get the results. </p> <p><strong>I'd like to emphasize that the questions that I'm about to ask pertains to my particular case rather than generic SQL index questions. Though the answers to them might be applied to generic questions as well.</strong></p> <p><strong>Context</strong></p> <ul> <li>SQL Server 2012</li> <li>Windows Server 2008 </li> </ul> <p>Table column definitions:</p> <ul> <li>ItemNumber :: nvarchar(10)</li> <li>Manufacturer :: nvarchar(20)</li> <li>Description :: nvarchar(40)</li> </ul> <p>Possible record count: up to 100K</p> <p><strong>Use Case:</strong></p> <p>An end user (one of 1000 or so) will pass a single string to search these three columns and the query needs to return all rows where any of these 3 columns contains the value of the string that's being searched (case insensitive).</p> <p><strong>The Questions:</strong></p> <ol> <li>What is the best way in to create index(s) so that the query would return the data in a most efficient manner (fast while minimizing SQL Server resource usage)? </li> <li>Create index for each column? </li> <li>Create one index with all 3 columns included? </li> <li>Enable full text search on the index(s)?</li> <li>What method would exploit the full potential of what SQL Server 2012 could offer?</li> </ol>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload