Nonclustered UNIQUE INDEX

Hi
I have read about "Nonclustered UNIQUE INDEX" on BOL and
it am not clear what exactly is Nonclustered Unique Index!
Plese note i do understand the difference between the
Clustered and Nonclustered Index!
What i don't understand is the "Nonclustered UNIQUE
INDEX"? What does it mean when you create a "Nonclustered
UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
CONSRAINT and it allows duplicate values.
I appreciate if you could also tell me which columns are
usually good candidates for a Nonclustered UNIQUE INDEX.
Thank you,
Mitra
> What i don't understand is the "Nonclustered UNIQUE
> INDEX"? What does it mean when you create a "Nonclustered
> UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
> CONSRAINT and it allows duplicate values.
You've made some assumptions here. If you create a UNIQUE INDEX then it
will not allow duplicate values. Just because you didn't explicitly create
a constraint doesn't mean one isn't implicitly created for you. Try it!
CREATE TABLE blat(foo INT)
CREATE UNIQUE INDEX splunge ON blat(foo)
GO
INSERT blat SELECT 1
INSERT blat SELECT 2
INSERT blat SELECT 3
SELECT foo FROM blat
GO
-- you will see 1, 2, 3 in the resultset.
-- however, when you try this:
INSERT blat SELECT 1
-- you will get:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'blat' with unique index
'splunge'.
The statement has been terminated.
So, essentially, a UNIQUE INDEX creates an index *and* it is enforced like a
unique constraint (though no constraint will show up using sp_help or
sp_helpconstraint).
Note that unless you include the word CLUSTERED the index will be
non-clustered.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
|||Aaron,thank you for the clarification!
I guess I was confused because when I open the Design
Table window for a table in SQL Server 2000, in the
Properties dialog box there are two options for Create
UNIQUE setting: Constraint, and Index. Why is that?
Thanks,
Mitra
[vbcol=seagreen]
>--Original Message--
a "Nonclustered[vbcol=seagreen]
UNIQUE
>You've made some assumptions here. If you create a
UNIQUE INDEX then it
>will not allow duplicate values. Just because you
didn't explicitly create
>a constraint doesn't mean one isn't implicitly created
for you. Try it!
>
>CREATE TABLE blat(foo INT)
>CREATE UNIQUE INDEX splunge ON blat(foo)
>GO
>INSERT blat SELECT 1
>INSERT blat SELECT 2
>INSERT blat SELECT 3
>SELECT foo FROM blat
>GO
>-- you will see 1, 2, 3 in the resultset.
>-- however, when you try this:
>INSERT blat SELECT 1
>-- you will get:
>Server: Msg 2601, Level 14, State 3, Line 1
>Cannot insert duplicate key row in object 'blat' with
unique index
>'splunge'.
>The statement has been terminated.
>
>So, essentially, a UNIQUE INDEX creates an index *and*
it is enforced like a
>unique constraint (though no constraint will show up
using sp_help or
>sp_helpconstraint).
>Note that unless you include the word CLUSTERED the
index will be
>non-clustered.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>
|||A constraint doesn't add an index, it just enforces uniqueness. You might
want to have 30 constraints on a table but you will be very unlikely to have
30 indexes that are helpful.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:c47401c43884$9dbfd590$a101280a@.phx.gbl...[vbcol=seagreen]
> Aaron,thank you for the clarification!
> I guess I was confused because when I open the Design
> Table window for a table in SQL Server 2000, in the
> Properties dialog box there are two options for Create
> UNIQUE setting: Constraint, and Index. Why is that?
> Thanks,
> Mitra
> a "Nonclustered
> UNIQUE
> UNIQUE INDEX then it
> didn't explicitly create
> for you. Try it!
> unique index
> it is enforced like a
> using sp_help or
> index will be
|||Uhm, Aaron?
A UNIQUE constraint always automatically adds an index, that's the only way
in SQL server you can implement it.
Mitra,
A constraint is part of your logical database design, an index is physical
construct. The effect of them is the same. It is best practice however to
enforce uniqueness via constraints, as it is an element of your logical
design, just like foreign keys for example. The only good reason to
implement a unique index without a unique constraint is if a subset of the
columns in the unique index is already covered by a unique constraint. One
example is when you have two columns with a unique constraint on it and for
performance reasons you also want an index with the columns in the opposite
order.
Jacco Schalkwijk
SQL Server MVP
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%233kkbVIOEHA.128@.TK2MSFTNGP12.phx.gbl...
> A constraint doesn't add an index, it just enforces uniqueness. You might
> want to have 30 constraints on a table but you will be very unlikely to
have
> 30 indexes that are helpful.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
>
|||Yes, much better explanation, sorry...
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OqBXTyMOEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Uhm, Aaron?
> A UNIQUE constraint always automatically adds an index, that's the only
way
> in SQL server you can implement it.
> Mitra,
> A constraint is part of your logical database design, an index is physical
> construct. The effect of them is the same. It is best practice however to
> enforce uniqueness via constraints, as it is an element of your logical
> design, just like foreign keys for example. The only good reason to
> implement a unique index without a unique constraint is if a subset of the
> columns in the unique index is already covered by a unique constraint. One
> example is when you have two columns with a unique constraint on it and
for
> performance reasons you also want an index with the columns in the
opposite
> order.

Nonclustered UNIQUE INDEX

Hi
I have read about "Nonclustered UNIQUE INDEX" on BOL and
it am not clear what exactly is Nonclustered Unique Index!
Plese note i do understand the difference between the
Clustered and Nonclustered Index!
What i don't understand is the "Nonclustered UNIQUE
INDEX"? What does it mean when you create a "Nonclustered
UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
CONSRAINT and it allows duplicate values.
I appreciate if you could also tell me which columns are
usually good candidates for a Nonclustered UNIQUE INDEX.
Thank you,
Mitra> What i don't understand is the "Nonclustered UNIQUE
> INDEX"? What does it mean when you create a "Nonclustered
> UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
> CONSRAINT and it allows duplicate values.
You've made some assumptions here. If you create a UNIQUE INDEX then it
will not allow duplicate values. Just because you didn't explicitly create
a constraint doesn't mean one isn't implicitly created for you. Try it!
CREATE TABLE blat(foo INT)
CREATE UNIQUE INDEX splunge ON blat(foo)
GO
INSERT blat SELECT 1
INSERT blat SELECT 2
INSERT blat SELECT 3
SELECT foo FROM blat
GO
-- you will see 1, 2, 3 in the resultset.
-- however, when you try this:
INSERT blat SELECT 1
-- you will get:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'blat' with unique index
'splunge'.
The statement has been terminated.
So, essentially, a UNIQUE INDEX creates an index *and* it is enforced like a
unique constraint (though no constraint will show up using sp_help or
sp_helpconstraint).
Note that unless you include the word CLUSTERED the index will be
non-clustered.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Aaron,thank you for the clarification!
I guess I was confused because when I open the Design
Table window for a table in SQL Server 2000, in the
Properties dialog box there are two options for Create
UNIQUE setting: Constraint, and Index. Why is that?
Thanks,
Mitra

>--Original Message--
a "Nonclustered[vbcol=seagreen]
UNIQUE[vbcol=seagreen]
>You've made some assumptions here. If you create a
UNIQUE INDEX then it
>will not allow duplicate values. Just because you
didn't explicitly create
>a constraint doesn't mean one isn't implicitly created
for you. Try it!
>
>CREATE TABLE blat(foo INT)
>CREATE UNIQUE INDEX splunge ON blat(foo)
>GO
>INSERT blat SELECT 1
>INSERT blat SELECT 2
>INSERT blat SELECT 3
>SELECT foo FROM blat
>GO
>-- you will see 1, 2, 3 in the resultset.
>-- however, when you try this:
>INSERT blat SELECT 1
>-- you will get:
>Server: Msg 2601, Level 14, State 3, Line 1
>Cannot insert duplicate key row in object 'blat' with
unique index
>'splunge'.
>The statement has been terminated.
>
>So, essentially, a UNIQUE INDEX creates an index *and*
it is enforced like a
>unique constraint (though no constraint will show up
using sp_help or
>sp_helpconstraint).
>Note that unless you include the word CLUSTERED the
index will be
>non-clustered.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||A constraint doesn't add an index, it just enforces uniqueness. You might
want to have 30 constraints on a table but you will be very unlikely to have
30 indexes that are helpful.
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:c47401c43884$9dbfd590$a101280a@.phx.gbl...[vbcol=seagreen]
> Aaron,thank you for the clarification!
> I guess I was confused because when I open the Design
> Table window for a table in SQL Server 2000, in the
> Properties dialog box there are two options for Create
> UNIQUE setting: Constraint, and Index. Why is that?
> Thanks,
> Mitra
>
> a "Nonclustered
> UNIQUE
> UNIQUE INDEX then it
> didn't explicitly create
> for you. Try it!
> unique index
> it is enforced like a
> using sp_help or
> index will be|||Uhm, Aaron?
A UNIQUE constraint always automatically adds an index, that's the only way
in SQL server you can implement it.
Mitra,
A constraint is part of your logical database design, an index is physical
construct. The effect of them is the same. It is best practice however to
enforce uniqueness via constraints, as it is an element of your logical
design, just like foreign keys for example. The only good reason to
implement a unique index without a unique constraint is if a subset of the
columns in the unique index is already covered by a unique constraint. One
example is when you have two columns with a unique constraint on it and for
performance reasons you also want an index with the columns in the opposite
order.
Jacco Schalkwijk
SQL Server MVP
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%233kkbVIOEHA.128@.TK2MSFTNGP12.phx.gbl...
> A constraint doesn't add an index, it just enforces uniqueness. You might
> want to have 30 constraints on a table but you will be very unlikely to
have
> 30 indexes that are helpful.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
>|||Yes, much better explanation, sorry...
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OqBXTyMOEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Uhm, Aaron?
> A UNIQUE constraint always automatically adds an index, that's the only
way
> in SQL server you can implement it.
> Mitra,
> A constraint is part of your logical database design, an index is physical
> construct. The effect of them is the same. It is best practice however to
> enforce uniqueness via constraints, as it is an element of your logical
> design, just like foreign keys for example. The only good reason to
> implement a unique index without a unique constraint is if a subset of the
> columns in the unique index is already covered by a unique constraint. One
> example is when you have two columns with a unique constraint on it and
for
> performance reasons you also want an index with the columns in the
opposite
> order.

Nonclustered UNIQUE INDEX

Hi
I have read about "Nonclustered UNIQUE INDEX" on BOL and
it am not clear what exactly is Nonclustered Unique Index!
Plese note i do understand the difference between the
Clustered and Nonclustered Index!
What i don't understand is the "Nonclustered UNIQUE
INDEX"? What does it mean when you create a "Nonclustered
UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
CONSRAINT and it allows duplicate values.
I appreciate if you could also tell me which columns are
usually good candidates for a Nonclustered UNIQUE INDEX.
Thank you,
Mitra> What i don't understand is the "Nonclustered UNIQUE
> INDEX"? What does it mean when you create a "Nonclustered
> UNIQUE INDEX" on a column that it's NOT defined as UNIQUE
> CONSRAINT and it allows duplicate values.
You've made some assumptions here. If you create a UNIQUE INDEX then it
will not allow duplicate values. Just because you didn't explicitly create
a constraint doesn't mean one isn't implicitly created for you. Try it!
CREATE TABLE blat(foo INT)
CREATE UNIQUE INDEX splunge ON blat(foo)
GO
INSERT blat SELECT 1
INSERT blat SELECT 2
INSERT blat SELECT 3
SELECT foo FROM blat
GO
-- you will see 1, 2, 3 in the resultset.
-- however, when you try this:
INSERT blat SELECT 1
-- you will get:
Server: Msg 2601, Level 14, State 3, Line 1
Cannot insert duplicate key row in object 'blat' with unique index
'splunge'.
The statement has been terminated.
So, essentially, a UNIQUE INDEX creates an index *and* it is enforced like a
unique constraint (though no constraint will show up using sp_help or
sp_helpconstraint).
Note that unless you include the word CLUSTERED the index will be
non-clustered.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/|||Aaron,thank you for the clarification!
I guess I was confused because when I open the Design
Table window for a table in SQL Server 2000, in the
Properties dialog box there are two options for Create
UNIQUE setting: Constraint, and Index. Why is that?
Thanks,
Mitra
>--Original Message--
>> What i don't understand is the "Nonclustered UNIQUE
>> INDEX"? What does it mean when you create
a "Nonclustered
>> UNIQUE INDEX" on a column that it's NOT defined as
UNIQUE
>> CONSRAINT and it allows duplicate values.
>You've made some assumptions here. If you create a
UNIQUE INDEX then it
>will not allow duplicate values. Just because you
didn't explicitly create
>a constraint doesn't mean one isn't implicitly created
for you. Try it!
>
>CREATE TABLE blat(foo INT)
>CREATE UNIQUE INDEX splunge ON blat(foo)
>GO
>INSERT blat SELECT 1
>INSERT blat SELECT 2
>INSERT blat SELECT 3
>SELECT foo FROM blat
>GO
>-- you will see 1, 2, 3 in the resultset.
>-- however, when you try this:
>INSERT blat SELECT 1
>-- you will get:
>Server: Msg 2601, Level 14, State 3, Line 1
>Cannot insert duplicate key row in object 'blat' with
unique index
>'splunge'.
>The statement has been terminated.
>
>So, essentially, a UNIQUE INDEX creates an index *and*
it is enforced like a
>unique constraint (though no constraint will show up
using sp_help or
>sp_helpconstraint).
>Note that unless you include the word CLUSTERED the
index will be
>non-clustered.
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>.
>|||A constraint doesn't add an index, it just enforces uniqueness. You might
want to have 30 constraints on a table but you will be very unlikely to have
30 indexes that are helpful.
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
> Aaron,thank you for the clarification!
> I guess I was confused because when I open the Design
> Table window for a table in SQL Server 2000, in the
> Properties dialog box there are two options for Create
> UNIQUE setting: Constraint, and Index. Why is that?
> Thanks,
> Mitra
>>--Original Message--
>> What i don't understand is the "Nonclustered UNIQUE
>> INDEX"? What does it mean when you create
> a "Nonclustered
>> UNIQUE INDEX" on a column that it's NOT defined as
> UNIQUE
>> CONSRAINT and it allows duplicate values.
>>You've made some assumptions here. If you create a
> UNIQUE INDEX then it
>>will not allow duplicate values. Just because you
> didn't explicitly create
>>a constraint doesn't mean one isn't implicitly created
> for you. Try it!
>>
>>CREATE TABLE blat(foo INT)
>>CREATE UNIQUE INDEX splunge ON blat(foo)
>>GO
>>INSERT blat SELECT 1
>>INSERT blat SELECT 2
>>INSERT blat SELECT 3
>>SELECT foo FROM blat
>>GO
>>-- you will see 1, 2, 3 in the resultset.
>>-- however, when you try this:
>>INSERT blat SELECT 1
>>-- you will get:
>>Server: Msg 2601, Level 14, State 3, Line 1
>>Cannot insert duplicate key row in object 'blat' with
> unique index
>>'splunge'.
>>The statement has been terminated.
>>
>>So, essentially, a UNIQUE INDEX creates an index *and*
> it is enforced like a
>>unique constraint (though no constraint will show up
> using sp_help or
>>sp_helpconstraint).
>>Note that unless you include the word CLUSTERED the
> index will be
>>non-clustered.
>>--
>>Aaron Bertrand
>>SQL Server MVP
>>http://www.aspfaq.com/
>>
>>.|||Uhm, Aaron?
A UNIQUE constraint always automatically adds an index, that's the only way
in SQL server you can implement it.
Mitra,
A constraint is part of your logical database design, an index is physical
construct. The effect of them is the same. It is best practice however to
enforce uniqueness via constraints, as it is an element of your logical
design, just like foreign keys for example. The only good reason to
implement a unique index without a unique constraint is if a subset of the
columns in the unique index is already covered by a unique constraint. One
example is when you have two columns with a unique constraint on it and for
performance reasons you also want an index with the columns in the opposite
order.
--
Jacco Schalkwijk
SQL Server MVP
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:%233kkbVIOEHA.128@.TK2MSFTNGP12.phx.gbl...
> A constraint doesn't add an index, it just enforces uniqueness. You might
> want to have 30 constraints on a table but you will be very unlikely to
have
> 30 indexes that are helpful.
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Mitra Fathollahi" <mitra928@.hotmail.com> wrote in message
> news:c47401c43884$9dbfd590$a101280a@.phx.gbl...
> > Aaron,thank you for the clarification!
> >
> > I guess I was confused because when I open the Design
> > Table window for a table in SQL Server 2000, in the
> > Properties dialog box there are two options for Create
> > UNIQUE setting: Constraint, and Index. Why is that?
> >
> > Thanks,
> >
> > Mitra
> >
> >>--Original Message--
> >> What i don't understand is the "Nonclustered UNIQUE
> >> INDEX"? What does it mean when you create
> > a "Nonclustered
> >> UNIQUE INDEX" on a column that it's NOT defined as
> > UNIQUE
> >> CONSRAINT and it allows duplicate values.
> >>
> >>You've made some assumptions here. If you create a
> > UNIQUE INDEX then it
> >>will not allow duplicate values. Just because you
> > didn't explicitly create
> >>a constraint doesn't mean one isn't implicitly created
> > for you. Try it!
> >>
> >>
> >>CREATE TABLE blat(foo INT)
> >>CREATE UNIQUE INDEX splunge ON blat(foo)
> >>GO
> >>
> >>INSERT blat SELECT 1
> >>INSERT blat SELECT 2
> >>INSERT blat SELECT 3
> >>SELECT foo FROM blat
> >>GO
> >>
> >>-- you will see 1, 2, 3 in the resultset.
> >>-- however, when you try this:
> >>
> >>INSERT blat SELECT 1
> >>
> >>-- you will get:
> >>
> >>Server: Msg 2601, Level 14, State 3, Line 1
> >>Cannot insert duplicate key row in object 'blat' with
> > unique index
> >>'splunge'.
> >>The statement has been terminated.
> >>
> >>
> >>So, essentially, a UNIQUE INDEX creates an index *and*
> > it is enforced like a
> >>unique constraint (though no constraint will show up
> > using sp_help or
> >>sp_helpconstraint).
> >>
> >>Note that unless you include the word CLUSTERED the
> > index will be
> >>non-clustered.
> >>
> >>--
> >>Aaron Bertrand
> >>SQL Server MVP
> >>http://www.aspfaq.com/
> >>
> >>
> >>.
> >>
>|||Yes, much better explanation, sorry...
"Jacco Schalkwijk" <NOSPAMjaccos@.eurostop.co.uk> wrote in message
news:OqBXTyMOEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Uhm, Aaron?
> A UNIQUE constraint always automatically adds an index, that's the only
way
> in SQL server you can implement it.
> Mitra,
> A constraint is part of your logical database design, an index is physical
> construct. The effect of them is the same. It is best practice however to
> enforce uniqueness via constraints, as it is an element of your logical
> design, just like foreign keys for example. The only good reason to
> implement a unique index without a unique constraint is if a subset of the
> columns in the unique index is already covered by a unique constraint. One
> example is when you have two columns with a unique constraint on it and
for
> performance reasons you also want an index with the columns in the
opposite
> order.

Noise Words

http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how t
o
get SQL Server to ignore noise words in the query, BOL's discussion on noise
words is all nice except things don't seem as they appear.
Right from BOL:
Important:
If you edit a noise-word file, you must repopulate the full-text catalogs
before the changes will take effect.
Yes, the DBA has a choice in the matter. No, wait...I made changes,
repopulated the index, and I'm still not getting the results I want.
There are words in the $SQL_Server_Install_Path\Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
words. When I remove them and repopulate the index, I still get tagged for
"Informational: The full-text search condition contained noise word(s)."
Is there a literal difference from words of BOL "repopulate the full-text
catalogs" and the context menu item "Start full Population"? I know I must
be overlooking something. Can I seriously edit the noise words, or is it a
tease?
-DanPlease forgive me, I did discover that rebuilding the Catelog is much
different than repopulating the indexes. Maybe the BOL could differentiate
this better since Index manuveres can be achieved thru context menus and
catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
real clear in the docs
-Dan
"Dan Davis" wrote:

> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how
to
> get SQL Server to ignore noise words in the query, BOL's discussion on noi
se
> words is all nice except things don't seem as they appear.
> Right from BOL:
> Important:
> If you edit a noise-word file, you must repopulate the full-text catalogs
> before the changes will take effect.
> Yes, the DBA has a choice in the matter. No, wait...I made changes,
> repopulated the index, and I'm still not getting the results I want.
> There are words in the $SQL_Server_Install_Path\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
> words. When I remove them and repopulate the index, I still get tagged fo
r
> "Informational: The full-text search condition contained noise word(s)."
>
> Is there a literal difference from words of BOL "repopulate the full-text
> catalogs" and the context menu item "Start full Population"? I know I mus
t
> be overlooking something. Can I seriously edit the noise words, or is it
a
> tease?
> -Dan|||Hi Dan
There is a .fulltext newsgroup. You might have better luck posting your
questions there.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dan Davis" <In_Finite@.news.postalias> wrote in message
news:9F9E5E9A-1A7F-484A-98B3-E3E8F5C95F27@.microsoft.com...[vbcol=seagreen]
> Please forgive me, I did discover that rebuilding the Catelog is much
> different than repopulating the indexes. Maybe the BOL could
> differentiate
> this better since Index manuveres can be achieved thru context menus and
> catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
> real clear in the docs
> -Dan
> "Dan Davis" wrote:
>

Noise Words

http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how to
get SQL Server to ignore noise words in the query, BOL's discussion on noise
words is all nice except things don't seem as they appear.
Right from BOL:
Important:
If you edit a noise-word file, you must repopulate the full-text catalogs
before the changes will take effect.
Yes, the DBA has a choice in the matter. No, wait...I made changes,
repopulated the index, and I'm still not getting the results I want.
There are words in the $SQL_Server_Install_Path\Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
words. When I remove them and repopulate the index, I still get tagged for
"Informational: The full-text search condition contained noise word(s)."
Is there a literal difference from words of BOL "repopulate the full-text
catalogs" and the context menu item "Start full Population"? I know I must
be overlooking something. Can I seriously edit the noise words, or is it a
tease?
-DanPlease forgive me, I did discover that rebuilding the Catelog is much
different than repopulating the indexes. Maybe the BOL could differentiate
this better since Index manuveres can be achieved thru context menus and
catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
real clear in the docs
-Dan
"Dan Davis" wrote:
> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how to
> get SQL Server to ignore noise words in the query, BOL's discussion on noise
> words is all nice except things don't seem as they appear.
> Right from BOL:
> Important:
> If you edit a noise-word file, you must repopulate the full-text catalogs
> before the changes will take effect.
> Yes, the DBA has a choice in the matter. No, wait...I made changes,
> repopulated the index, and I'm still not getting the results I want.
> There are words in the $SQL_Server_Install_Path\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
> words. When I remove them and repopulate the index, I still get tagged for
> "Informational: The full-text search condition contained noise word(s)."
>
> Is there a literal difference from words of BOL "repopulate the full-text
> catalogs" and the context menu item "Start full Population"? I know I must
> be overlooking something. Can I seriously edit the noise words, or is it a
> tease?
> -Dan|||Hi Dan
There is a .fulltext newsgroup. You might have better luck posting your
questions there.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dan Davis" <In_Finite@.news.postalias> wrote in message
news:9F9E5E9A-1A7F-484A-98B3-E3E8F5C95F27@.microsoft.com...
> Please forgive me, I did discover that rebuilding the Catelog is much
> different than repopulating the indexes. Maybe the BOL could
> differentiate
> this better since Index manuveres can be achieved thru context menus and
> catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
> real clear in the docs
> -Dan
> "Dan Davis" wrote:
>> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely
>> how to
>> get SQL Server to ignore noise words in the query, BOL's discussion on
>> noise
>> words is all nice except things don't seem as they appear.
>> Right from BOL:
>> Important:
>> If you edit a noise-word file, you must repopulate the full-text catalogs
>> before the changes will take effect.
>> Yes, the DBA has a choice in the matter. No, wait...I made changes,
>> repopulated the index, and I'm still not getting the results I want.
>> There are words in the $SQL_Server_Install_Path\Microsoft SQL
>> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
>> words. When I remove them and repopulate the index, I still get tagged
>> for
>> "Informational: The full-text search condition contained noise word(s)."
>>
>> Is there a literal difference from words of BOL "repopulate the full-text
>> catalogs" and the context menu item "Start full Population"? I know I
>> must
>> be overlooking something. Can I seriously edit the noise words, or is it
>> a
>> tease?
>> -Dan