index one column (varchar(1743))

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)

index one column (varchar(1743))

Author

Topic

srg_skinner
Starting Member

4 Posts

Posted - 2009-05-06 : 06:35:42

SQL Server 2005
Hi all,
i have stucked ....
i would like to create index on one! column which is defined as varchar(1743). But Sql server allows only 900 bytes maximum length column.
How can i solve this?
I have read about included columns, bud according the guide, it seems
it is usable only when more columns are supposed to be indexed.
Does any get around solution exist?

Any help would be appreciated.
Thanks

srg_skinner
Starting Member

4 Posts

Posted - 2009-05-06 : 07:04:22

any idea?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-06 : 07:27:42

What kind of queries are you running against this field?

An index will not help you if you are doing


LIKE '%z%'

IF you post your table structure and a sample query someone will probably have a good idea for making it quicker / more efficient

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

srg_skinner
Starting Member

4 Posts

Posted - 2009-05-06 : 07:50:13

Table serves for storing names of items that are stored in other table. Column cnaz stores names.
And often are queries done by this column. (by various search conditions (beginning with,ending with, and also whole name etc))
And size is important to fulfill specification by standardization agency :(
It is pity because i have never seen so long name :)

DDL is:

CREATE
TABLE KCCH6
(
ID_KCCH6 INT NOT NULL,
KSTNAZ CHAR(1) COLLATE Cyrillic_General_CI_AS,
INC CHAR(5) COLLATE Cyrillic_General_CI_AS,
CNAZ VARCHAR(1743) COLLATE Cyrillic_General_CI_AS,
TNAZ CHAR(1) COLLATE Cyrillic_General_CI_AS NOT NULL,
CN CHAR(1) COLLATE Cyrillic_General_CI_AS,
DZCNAZ DATETIME NOT NULL,
ui_kcch6_cnaz VARCHAR(1743) COLLATE Cyrillic_General_CI_AS,
CONSTRAINT PK_KCCH6 PRIMARY KEY (ID_KCCH6),
CONSTRAINT F_KCCH6_MASTKCSTNA FOREIGN KEY (KSTNAZ) REFERENCES KCSTNAZ (KSTNAZ),
CONSTRAINT CKCCH6_INC________ CHECK ([INC] like '[0-9A-Z][0-9][0-9][0-9][0-9]'),
CONSTRAINT CKCCH6_KSTNAZ_____ CHECK ([KSTNAZ] like '[A-Z]'),
CONSTRAINT CKCCH6_TNAZ_______ CHECK ([TNAZ] like '[SNXMPZ]'),
CONSTRAINT ckcch6_cnaz_______ CHECK ([cnaz]<> '')
)

thanks for your effort...

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-05-06 : 08:05:59

Maybe you should look into FULLTEXT?

I've never used it but I think it would be perfect for your needs.

try:
http://msdn.microsoft.com/en-us/library/ms142571.aspx
http://msdn.microsoft.com/en-us/library/aa224740.aspx

or:
http://www.developer.com/db/article.php/3446891

to get you started.

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

srg_skinner
Starting Member

4 Posts

Posted - 2009-05-06 : 08:20:32

ok thank you very much,
i will analyze it, try this propose
and till tommorow i will get here an info how it works to close properly this topic.

but i think it is most suitable solution.
once again thanks t. charlie,
bye.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources