February 16, 2016 at 9:22 pm
Hi Folks:
For VARCHAR [varchar(MAX) & varchar(800] fields, non-clustered index or full-text index recommended. This field is intended to use in WHERE CLAUSE (using 'like').
When should we use full-text index.
Any guidance/thoughts much appreciated.
Many thanks!
February 16, 2016 at 9:37 pm
SQL!$@w$0ME (2/16/2016)
Hi Folks:For VARCHAR field, non-clustered index or full-text index recommended. When should we use full-text index.
Any guidance/thoughts much appreciated.
Many thanks!
Why do you think you need any index on the column? You have not provided enough information for an informed answer. First, VARCHAR what? Indexing a VARCHAR(MAX) column is much different than say a VARCHAR(100) column. If you are not sorting on the column, using it in a WHERE clause, grouping by that column, using DISTINCT in queries that only retrieve data from that column then you may not need an index at all.
Full text indexing is a whole different animal entirely. I would advice you start here before going down that road.
-- Itzik Ben-Gan 2001
February 17, 2016 at 2:03 am
SQL!$@w$0ME (2/16/2016)
For VARCHAR field, non-clustered index or full-text index recommended.
WRT nonclustered index: it depends. As Alan says: long varchar columns almost never. Shorter ones sometimes.
Do you often join on the varchar column? If so, perhaps redesigning to use a better datatype in the join could be an idea. (Not always better, but do consider it).
Do you oftenn filter on the varchar column? Then a nonclustered index might be useful. Try it in a QA environment that mimics production as close as possible and see.
When should we use full-text index.
When you have queries that use full-text serach functionality.
February 17, 2016 at 6:05 am
Thanks.
For VARCHAR [varchar(MAX) & varchar(800] fields on two different tables. This fields are intended to use in WHERE CLAUSE (using 'like').
February 17, 2016 at 7:04 am
SQL!$@w$0ME (2/17/2016)
Thanks.For VARCHAR [varchar(MAX) & varchar(800] fields on two different tables. This fields are intended to use in WHERE CLAUSE (using 'like').
You can't add a nonclustered index with a varchar (max) as the key column, only as an include column, but that isn't seekable and therefore not help your WHERE clause. Full text indexing might be the way to go there but you would want to weigh the benefits vs cost; adding and maintaining full text indexing is not a trivial task.
For varchar(800) I would say, maybe. An NC index on varchar(800) will improve performance provided that queries against it are SARGable and is used by the optimizer but it can be expensive with respect to storage and will certainly have an impact on inserts, updates and deletes.
EDIT: Typo
-- Itzik Ben-Gan 2001
February 17, 2016 at 7:06 am
You can't index a varchar(max) and the index on the varchar(800) will only be useful if there's no leading wildcard, and maybe not even then.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2016 at 7:22 am
Test it. This will run different for you than it will run for anyone else.
We don't know if you're using leading wildcard characters, which would cause a scan instead of a seek. We don't know the overall width of your table, which a narrower index would make for a faster scan even with possible leading wildcards. We don't know how often this table is queried this way, which justifies more extreme steps. Finally, we don't know how much of your clustered index is in cache, which would make adding an index less beneficial and possibly harmful. These are the main things I'd be looking for.
This isn't even getting into the possibility of doing full-text indexes, which I'm better off leaving to others to answer questions on.
Sorry to sit here and list the things we don't know, but I hope it gives you a better idea of what to look for.
February 17, 2016 at 9:02 am
Consider that SQL Server retains the 900-byte limit for the maximum total size of all index key columns.
https://technet.microsoft.com/en-us/library/ms191241(v=sql.105).aspx
Igor Micev,My blog: www.igormicev.com
February 17, 2016 at 9:07 am
Steve Hood. (2/17/2016)
Finally, we don't know how much of your clustered index is in cache, which would make adding an index less beneficial and possibly harmful.
???
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 17, 2016 at 9:18 am
If the clustered index is already mostly in cache it will be a lot of logical I/O, but no physical I/O. If you add a nonclustered index to that table that is narrower so it's doing less I/O then you have another index fighting for space in cache, possibly doing physical I/O every time it runs, and could actually be slower in a production environment because of that physical I/O.
It's something I keep in mind, but perhaps it was going too far in this case.
February 18, 2016 at 8:22 am
LIKE queries are not useful in FULLTEXT indexes. You'd need queries to use CONTAINS or FREETEXT in the WHERE clause.
February 18, 2016 at 1:39 pm
Thanks all!
February 18, 2016 at 1:40 pm
Thanks Gail!
February 18, 2016 at 1:40 pm
Thanks!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply