create index - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

create index

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-12-31 : 02:28:37

How to create index on a column in a table containing ntext datatype in sql server management studio express.i want to create a normal index only

Sachin.Nand

2937 Posts

Posted - 2009-12-31 : 02:32:18

According to BOL ntext,text & image columns cannot be a part of index.

PBUH

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-12-31 : 03:41:22

then how to create index for that.any other way to do so

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-31 : 04:16:40

you probably don't want to create an index on a column with that datatype.

if you need indices on large text fields then you probably want to check out FULLTEXT search and index. However, I don't have any personal experience with them.

*Why* do you want the index? What queries do you believe it will help?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Sachin.Nand

2937 Posts

Posted - 2009-12-31 : 04:17:46

You can do this.
create table #tbl(col varchar(max))
create nonclustered index(IX_name) on #tbl(col)
insert into #tbl
select convert(varchar(max),yourcolumn which is ntext) from yourtable

PBUH

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-12-31 : 04:24:35

any script will be helpfull in doing so

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-12-31 : 04:30:08

create table #tbl(A varchar(max))
create nonclustered index IX_name on #tbl(A)
insert into #tbl
select convert(varchar(max),ROS_DATA) from EMRENCOUNTERDATA

Msg 1919, Level 16, State 1, Line 2
Column 'A' in table '#tbl' is of a type that is invalid for use as a key column in an index.

EMREncounterData_EIX nonclustered located on PRIMARY ENCOUNTER_ID
IX_EMREncounterData clustered, unique located on PRIMARY ENCOUNTER_ID

alredy 2 indexex were there

Sachin.Nand

2937 Posts

Posted - 2009-12-31 : 04:36:53

I think it is because of varchar(max).you can create a nonclustered index on some other column & put the column
ROS_DATA as a included column.

create table #tbl(id int,A varchar(max))
create nonclustered index IX_name on #tbl(id)
include(A)
insert into #tbl
select id,convert(varchar(max),ROS_DATA) from EMRENCOUNTERDATA
Or else follow what Transact Charlie suggested.

PBUH

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-12-31 : 04:49:12

Correct me if I am wrong but I think to include() the column will not help for searching...

No, you're never too old to Yak'n'Roll if you're too young to die.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-12-31 : 04:53:46

quote:
Originally posted by Transact Charlie

you probably don't want to create an index on a column with that datatype.

if you need indices on large text fields then you probably want to check out FULLTEXT search and index. However, I don't have any personal experience with them.

*Why* do you want the index? What queries do you believe it will help?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

As I said before -- what queries are you looking to improve?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-12-31 : 04:54:34

Its Creating but iam not able to find it in sp_help emrencounterdata that index and even i dont want the tempoarary table too

Sachin.Nand

2937 Posts

Posted - 2009-12-31 : 04:59:49

quote:
Originally posted by webfred

Correct me if I am wrong but I think to include() the column will not help for searching...

No, you're never too old to Yak'n'Roll if you're too young to die.

The OP didnt mention that he needs the index for searching.He said he needs it on a text column

.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources