how to create index for text/varchar(max) datatype

Posted by Santosh4u under Sql Server on 1/6/2014 | Points: 10 | Views : 3638 | Status : [Member] | Replies : 3

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,
i want to create a index for the text/varchar(max) datatype field becaz it's taking long tim while retrieving data from the table.

not allowing to create any index.

waiting for the quick response.

Thanks
Santosh

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: kgovindarao523-21772 on: 1/6/2014 [Member] [MVP] Bronze | Points: 25

0	Hi, In Sql server,; text, varchar(max) have restricted index participation. see following link. http://technet.microsoft.com/en-us/library/ms190197%28v=sql.105%29.aspx Thank you, Govind Santosh4u, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Bandi on: 1/6/2014 [Member] [MVP] Platinum | Points: 25

0	You can INCLUDE VARCHAR(MAX) column in the index definition.. --Sample Script IF OBJECT_ID('tempdb..#example') IS NOT NULL BEGIN DROP TABLE #example END CREATE TABLE #example (id INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(MAX) ) CREATE NONCLUSTERED INDEX IDX_NC_temp_example_name ON #example(id) INCLUDE(name) References: http://stackoverflow.com/questions/12336821/how-can-i-create-index-on-nvarcharmax-datatype-in-sql http://stackoverflow.com/questions/7141402/why-not-use-varcharmax Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Santosh4u, if this helps please login to Mark As Answer. \| Alert Moderator

You can INCLUDE VARCHAR(MAX) column in the index definition..

--Sample Script
IF OBJECT_ID('tempdb..#example') IS NOT NULL 
BEGIN
    DROP TABLE #example
END

CREATE TABLE #example (id INT PRIMARY KEY IDENTITY(1,1), name VARCHAR(MAX) )

CREATE NONCLUSTERED INDEX IDX_NC_temp_example_name ON #example(id) INCLUDE(name)

References:
http://stackoverflow.com/questions/12336821/how-can-i-create-index-on-nvarcharmax-datatype-in-sql
http://stackoverflow.com/questions/7141402/why-not-use-varcharmax

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Santosh4u, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 1/8/2014 [Member] [MVP] Platinum | Points: 25

0	"Mark as Answer " if you got clear answer; otherwise post us back Mark This Response as Answer -- Chandu http://www.dotnetfunda.com/images/dnfmvp.gif Santosh4u, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts