field is part of an index

Hello all,
What does it mean when a field is part of an index? Thank you.(rotaterx8@.yahoo.com) writes:
> Hello all,
> What does it mean when a field is part of an index? Thank you.

That it is part of an index:

CREATE TABLE somefields (a int NOT NULL,
b int NOT NULL,
c datetime NULL,
d varchar(23) NOT NULL,
CONSTRAINT pk PRIMARY KEY (a,b))
go
CREATE INDEX d_ix ON somefields(d)

Here, all columns but c are parts of an index. a and b are part of
an index, because a primary key is implemented as an index in SQL Server.

I guess there is some reason why you ask. If you are trying to change the
collation of an indexed column, you must drop the index first, change
the collation, and then recreate the index.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

Features of a normal index comapred to a Full text index

Hello everyone,
My company is in the middle of coding a complex search function which
takes over most of the features that full text indexing contains (such
as stemming and stopping).
However from reading about full text indexing it appears that for a
column containing rows of pure text documents, the way that it indexes
the text is as follows: -
word id of row where word can be found
### #################################
car 0, 10, 256, 654
bike 20, 36, 92
skates 65,. 42
If this is the case I can see this improving search speeds for a large
database.
However we don't want any of the other features that full text
catalogue has as we do it ourselves.
My question is can we turn off all the extra features of full text
indexing (so we only left with a text index), or is a normal index
sufficient to provide the above example?
Many thanks for any help or advice
Kind Regards
Philip
With the Contains/ContainsTable keywords you get a strict match which it
sounds like what you are looking for. In otherwords stemming, and
wildcarding are disabled.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408110112.15962c0@.posting.google.co m...
> Hello everyone,
> My company is in the middle of coding a complex search function which
> takes over most of the features that full text indexing contains (such
> as stemming and stopping).
> However from reading about full text indexing it appears that for a
> column containing rows of pure text documents, the way that it indexes
> the text is as follows: -
> word id of row where word can be found
> ### #################################
> car 0, 10, 256, 654
> bike 20, 36, 92
> skates 65,. 42
> If this is the case I can see this improving search speeds for a large
> database.
> However we don't want any of the other features that full text
> catalogue has as we do it ourselves.
> My question is can we turn off all the extra features of full text
> indexing (so we only left with a text index), or is a normal index
> sufficient to provide the above example?
> Many thanks for any help or advice
> Kind Regards
> Philip
|||Hello Hilary,
Thankyou for you help, just to make sure, when I enable full text
catalogs, does it index the text without stemming and stopping the
data.
By this i mean that is I had stored in the table "to be or not to be",
would the full text catalog contain indexes for to, be, or and not or
would it stem and stop the text.
Again thankyou for your help
Kind Regards
Phil
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> With the Contains/ContainsTable keywords you get a strict match which it
> sounds like what you are looking for. In otherwords stemming, and
> wildcarding are disabled.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408110112.15962c0@.posting.google.co m...
|||The answer is complex. First off to,be, or, and not are all noise words and
as such they would not be indexed.
But to answer your question that depends on the word breaker. In general
the words would be indexed as they appear in the content. For some word
breakers different language rules dicate different indexing patterns, for
instance in the French word breaker, marie-claire is indexed as two
different words, marie and claire, however Marie-Claire is indexed as one
word (MarieClaire). The hyphen and the capitalization cause it to be indexed
as one word not two.
It is at query time when the search arguements might will be stemmed (when
you are doing a FreeText query, or an Inflectional query). If you are doing
a Contains query without wildcarding or the FormsOf(Inflectional type
queries you won't get stemming (although there are word breaker specific
some exceptions).
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> Hello Hilary,
> Thankyou for you help, just to make sure, when I enable full text
> catalogs, does it index the text without stemming and stopping the
> data.
> By this i mean that is I had stored in the table "to be or not to be",
> would the full text catalog contain indexes for to, be, or and not or
> would it stem and stop the text.
> Again thankyou for your help
> Kind Regards
> Phil
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
|||Phi!
In addition to what Hilary says, there is another factor here and that is
the OS platform (Win2K vs. Win2003 or WinXP) that you have SQL Server
installed on. Specifically, the Windows 2000 Server (Win2K) wordbreaker -
infosoft.dll - indexes the "-" (dash or hyphen) in "7-UP" as one token,
i.e., as single phrase. A work around for this is to drop and re-create your
FT Catalog and use the Neutral "Language for Word Breaker" for the your
FT-enabled column. However, with the Neutral "Language for Word Breaker",
you will lose the formsof(inflectional) function as the words are "broken"
into tokens based upon the "white space" between words...
However, this is not the case with Windows Server 2003 (Win2003) or Windows
XP (WinXP) as these OS-platforms, ships with a newer (or better, i.e., more
expectant results) wordbreaker - langwrbk.dll which would correctly (or more
expectant results) break 7 and UP into separate tokens. So, in the long run
to get both the correct wordbreaking for you as well as the use of the
formsof(inflectional) function, if this is the functionality you're looking
for, you should consider upgrading to Win2003...
Regards,
John
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl...
> The answer is complex. First off to,be, or, and not are all noise words
and
> as such they would not be indexed.
> But to answer your question that depends on the word breaker. In general
> the words would be indexed as they appear in the content. For some word
> breakers different language rules dicate different indexing patterns, for
> instance in the French word breaker, marie-claire is indexed as two
> different words, marie and claire, however Marie-Claire is indexed as one
> word (MarieClaire). The hyphen and the capitalization cause it to be
indexed
> as one word not two.
> It is at query time when the search arguements might will be stemmed (when
> you are doing a FreeText query, or an Inflectional query). If you are
doing[vbcol=seagreen]
> a Contains query without wildcarding or the FormsOf(Inflectional type
> queries you won't get stemming (although there are word breaker specific
> some exceptions).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
it[vbcol=seagreen]
which[vbcol=seagreen]
(such[vbcol=seagreen]
indexes[vbcol=seagreen]
large
>
|||Hello Hillary,
AFter much testing we have decided to go as follows, we will use Full
Text catalogs and search using the contains keyword.
However to ensure SQL 2000 server does not do anything strange with
the results we have turned the lnaguage setting to neutral. We found
the results were not returned when we had the UK setting on. i.e.
"jpg" would not find "filename.jpg".
For wildcards we will use the LIKEkeyword as the contains does not do
patterm matching with wilcards but tries to find differenet words
instead.
Therefore we hope we have the best of both world, by the using the
contains in most cases but the the LIKE keyword whenever anyone needs
pattern matching (which should be rare).
Again thankyou for your hillary in helping me understanding full text
catlogs
Have a good weekend
Phil
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
> The answer is complex. First off to,be, or, and not are all noise words and
> as such they would not be indexed.
> But to answer your question that depends on the word breaker. In general
> the words would be indexed as they appear in the content. For some word
> breakers different language rules dicate different indexing patterns, for
> instance in the French word breaker, marie-claire is indexed as two
> different words, marie and claire, however Marie-Claire is indexed as one
> word (MarieClaire). The hyphen and the capitalization cause it to be indexed
> as one word not two.
> It is at query time when the search arguements might will be stemmed (when
> you are doing a FreeText query, or an Inflectional query). If you are doing
> a Contains query without wildcarding or the FormsOf(Inflectional type
> queries you won't get stemming (although there are word breaker specific
> some exceptions).
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408120050.2b0d4d81@.posting.google.c om...
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
|||Phi!,
Just FYI, if you were able to read my posting to this thread, you would of
understood why "the results were not returned when we had the UK setting on.
i.e. "jpg" would not find 'filename.jpg'" as it is a bug in the Win2K
wordbreaker dll and if in the future you decide to upgrade to Windows Server
2003 (Win2003) you would not of encountered this bug.
Regards,
John
"Phi!" <philgoogle2003@.yahoo.com> wrote in message
news:42d3fa28.0408130807.3fa93afe@.posting.google.c om...
> Hello Hillary,
> AFter much testing we have decided to go as follows, we will use Full
> Text catalogs and search using the contains keyword.
> However to ensure SQL 2000 server does not do anything strange with
> the results we have turned the lnaguage setting to neutral. We found
> the results were not returned when we had the UK setting on. i.e.
> "jpg" would not find "filename.jpg".
> For wildcards we will use the LIKEkeyword as the contains does not do
> patterm matching with wilcards but tries to find differenet words
> instead.
> Therefore we hope we have the best of both world, by the using the
> contains in most cases but the the LIKE keyword whenever anyone needs
> pattern matching (which should be rare).
> Again thankyou for your hillary in helping me understanding full text
> catlogs
> Have a good weekend
> Phil
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...[vbcol=seagreen]
and[vbcol=seagreen]
general[vbcol=seagreen]
for[vbcol=seagreen]
one[vbcol=seagreen]
indexed[vbcol=seagreen]
(when[vbcol=seagreen]
doing[vbcol=seagreen]
which it[vbcol=seagreen]
which[vbcol=seagreen]
(such[vbcol=seagreen]
a[vbcol=seagreen]
indexes[vbcol=seagreen]
large[vbcol=seagreen]
|||Hi John!
Thankyou for the tip, will bear it in mind when we upgrade.
I am having trouble getting an newsgroup reader on my desktop at work
(compnay ploicy etc) so have been limited to google at the moment so
did not know you had posted until today!
sorry for any confusion
Phil
"John Kane" <jt-kane@.comcast.net> wrote in message news:<O4zBxiagEHA.3632@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Phi!,
> Just FYI, if you were able to read my posting to this thread, you would of
> understood why "the results were not returned when we had the UK setting on.
> i.e. "jpg" would not find 'filename.jpg'" as it is a bug in the Win2K
> wordbreaker dll and if in the future you decide to upgrade to Windows Server
> 2003 (Win2003) you would not of encountered this bug.
> Regards,
> John
>
>
> "Phi!" <philgoogle2003@.yahoo.com> wrote in message
> news:42d3fa28.0408130807.3fa93afe@.posting.google.c om...
> news:<u4tYhaGgEHA.1188@.TK2MSFTNGP11.phx.gbl>...
> and
> general
> for
> one
> indexed
> (when
> doing
> news:<#XtB1t5fEHA.1972@.TK2MSFTNGP09.phx.gbl>...
> which it
> which
> (such
> a
> indexes
> large

feature requests to MS? How? Where?

Hi,
Anyone know how to request features for SQL server?
eg "reverse index" - like a phone number, if it was
reversed in the index it would be highly selective and low
density which would make it a lot faster. In Sydney
Australia most numbers have a 9 in the begining...
eg #2. "Ent. Mgr, diagrams across 2 or more databases".
Currently you can only diagram across one db at a time. I
have multiple db related.
eg #3. "programing sql trees". Support for programing sql
trees / parent child relationships.
And all sorts of other stuff developers and dba's need.
Cheers,
JohnSend email to :- sqlwish@.microsoft.com
--
HTH
Ryan Waight, MCDBA, MCSE
"John" <john.reed@.ht.com.au> wrote in message
news:0b4a01c3a455$57b94180$a301280a@.phx.gbl...
> Hi,
> Anyone know how to request features for SQL server?
> eg "reverse index" - like a phone number, if it was
> reversed in the index it would be highly selective and low
> density which would make it a lot faster. In Sydney
> Australia most numbers have a 9 in the begining...
> eg #2. "Ent. Mgr, diagrams across 2 or more databases".
> Currently you can only diagram across one db at a time. I
> have multiple db related.
> eg #3. "programing sql trees". Support for programing sql
> trees / parent child relationships.
> And all sorts of other stuff developers and dba's need.
> Cheers,
> John|||"John" <john.reed@.ht.com.au> wrote in message
news:0b4a01c3a455$57b94180$a301280a@.phx.gbl...
> eg "reverse index" - like a phone number, if it was
> reversed in the index it would be highly selective and low
> density which would make it a lot faster. In Sydney
> Australia most numbers have a 9 in the begining...
Store them backwards...
> eg #3. "programing sql trees". Support for programing sql
> trees / parent child relationships.
How is this not already supported?

Fast look up of long (n)varchar

I have a table containing URLs. I want to be able to look up an URL very
fast, so I used an nvarchar to store the URL, and put an index on it
(maybe naive).

Anyway, I bump into:
"The index entry of length 911 bytes for the index 'UQ__URL__1367E606'
exceeds the maximum length of 900 bytes."

What's the best way to handle this? I want to do the look up fast. The
only thing I could think up was adding an extra column containing a digest
for the URL, and look up all URLs with the same digest, *and* having the
same value (which could give either 1 or 0 results).

I am new to MS SQL, so I might describe a silly solution, basically I want
to look up URLs to ID the fastest way possible.

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.htmlJohn,

use an index on checksum(url), as described here:

http://www.devx.com/dbzone/Article/30786|||>Use an index on checksum(url), as described here:

oh wow. that is brilliant. I have never run into that before.

that is a GREAT idea that I am filing away for futures.

thank you!|||John Bokma <john@.castleamber.com> wrote:

> I have a table containing URLs. I want to be able to look up an URL
> very fast, so I used an nvarchar to store the URL, and put an index on
> it (maybe naive).
> Anyway, I bump into:
> "The index entry of length 911 bytes for the index 'UQ__URL__1367E606'
> exceeds the maximum length of 900 bytes."
> What's the best way to handle this? I want to do the look up fast. The
> only thing I could think up was adding an extra column containing a
> digest for the URL, and look up all URLs with the same digest, *and*
> having the same value (which could give either 1 or 0 results).
> I am new to MS SQL, so I might describe a silly solution, basically I
> want to look up URLs to ID the fastest way possible.

To answer my own question: under CHECKSUM in SQL Server Books Online:

"The checksum index can be used as a hash index, particularly to improve
indexing speed when the column to be indexed is a long character column."

Comes with an example, etc.

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

failure to publish an indexed view logbased publication

i am trying to publish an indexed view logbased publication but I'm getting "must be a schema-bound view with at least a clustered index to be published as a log-based indexed view article."

The table has clustered index and the view is schema-bound.

this is the script for schemabinding

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

ALTERVIEW [nth].[musictracks1] withschemabinding

AS

SELECT autoid,idtrack,songname,singer,writer,composer,center

FROM nth.musictracks

GO

SETANSI_NULLSOFF

GO

SETQUOTED_IDENTIFIEROFF

GO

and this is for making the publication an indexed view logbased publication

execsp_addarticle

@.publication = N'lb_publication',

@.article = N'musictracks1',

@.source_owner = N'nth',

@.source_object = N'musictracks1',

@.type = N'indexed view logbased',

@.description =null,

@.creation_script =null,

@.pre_creation_cmd = N'none',

@.schema_option = 0x0000000008000001,

/* table name doesn’t have to be the same as view name */

@.destination_table = N'musictracks1',

@.destination_owner = N'nth'

GO

Any idea what is wrong?

Thanks,

Salah

If you want to use logbased for an indexed view, then you have to create a unique clustered index on the indexed view as the error msg indicates. if you just want to replicate the view schema, then you can used indexed view schema only.|||

can you tell how can I create a unique clustered index on the indexed view ?

Thanks

|||

you create an index on it like you do with any other table, for example:

create unqiue clustered index [uc_index1] on musictracks1 (autoid)