Click here to Skip to main content
15,441,434 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello,

I have a column in table that have nvarchar(200) datatype

and then I add index on another column include my nvarchar(200) column

but the performance is the same when I do a select query with where condition in the nvarchar(200) column

I need to know why the index on nvarchar column doesn't affect the performance?

What I have tried:

CREATE TABLE dbo.POS_JSON_Transaction_2000
(
  Id INT IDENTITY PRIMARY KEY,
  [Name] Nvarchar(50),
  [Transaction] nvarchar(2000) 
);

CREATE INDEX POS_JSON_Transaction_2000_Transaction_Index ON 
POS_JSON_Transaction_2000(Name) include ([Transaction]);

select * from POS_JSON_Transaction_2000 where [Transaction] like '%"NAME__STRING":"testKA"%'
Posted
Updated 23-Oct-18 1:56am
v2
Comments
#realJSOP 23-Oct-18 7:22am    
You should ask this question in one of the forums. It's more of a discussion that a specific question.
Richard Deeming 23-Oct-18 7:34am     CRLF
We can't see your table; we can't see your index; we can't see your query; and we can't see your data. Any one of those could affect the query plan. Click the green "Improve question" link and update your question to describe the relevant parts of your table structure, the index definition, and the query you're running.
Heba Kamel 23-Oct-18 7:44am     CRLF
ok, I have updated the question Thanks

1 solution

There are a couple of problems here.

Firstly, the index is on the Name column. It can be used to satisfy queries on the Name column, but will be of no use for queries on the Transaction column.

Think of it an address book which is sorted by last name. You have created a secondary index based on the first name, and included the town in that index. You now want to find all entries in a specific town. The secondary index wouldn't help, since you'd still have to go through every entry to find the matching ones.

The other problem is that you're using a "contains" query - WHERE column LIKE '% ... %'. There is no sensible way for an index to help with that query. Again, given a list of towns in alphabetical order, if you want to find any town which contains the letter "p", you still have to go through every entry to find it.

If you were performing a "starts with" query - WHERE column LIKE '... %' - then an index could help.

Even for an "ends with" query - WHERE column LIKE '% ...' - it would be possible to use an index.

But for a "contains" query, there's not much you can do, short of creating a separate table with every substring from your column.

Sargability: Why %string% Is Slow - Brent Ozar Unlimited®[^]
One way to get an index seek for a leading %wildcard in SQL Server[^]
 
Share this answer
 
Comments
Heba Kamel 23-Oct-18 8:15am     CRLF
" the index is on the Name column. It can be used to satisfy queries on the Name column, but will be of no use for queries on the Transaction column. " I did the index on Name column with include Transaction column because of I cannot create the index directly on Transaction column because it's nvarchar(2000)
Richard Deeming 23-Oct-18 8:18am     CRLF
And as I explained, an index on the Name column won't help you find the records with a particular value in the Transaction column. It will only help you find records with a particular Name. Including the Transaction column just means that SQL doesn't have to go back to the original record if it needs to read the Transaction column for a record it's found using that index. And even if you had created an index on the Transaction column, it wouldn't help you with a "contains" query.
Heba Kamel 23-Oct-18 8:23am     CRLF
Okany suggestions to do this ? for me the whole string must put in one column.
Richard Deeming 23-Oct-18 8:28am     CRLF
Have a look at the second link in my answer. It will require a lot more space, and make adding or updating records a lot slower; but it's about the only way to get an index working on a "contains" query. Based on the text you're searching for, I'm guessing you're storing multiple values in a single column? If so, you should really consider splitting them out into a separate table, and searching on that. Alternatively, if you're using SQL Server 2016, you could try storing the data as JSON and creating an index on that[^].
Heba Kamel 23-Oct-18 8:27am    
because it's a JSON object, and the properties is dynamic

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900