Non-noise words are incorrectly recognised as noise words.

Hi.
I have an Sql server 2005 beta2 problem with the full-text index
functionality.
I have a table with a full text index. In the definition of the full-text
index, I have specified the neutral language. The problem domain specifies
that texts can be in arbitrary languages.
create fulltext index on mytable
(
myfield language 0x0
)
..
Then I insert a record containing the word "and" (which in Danish means
duck). When searching for that record
select * from mytable where contains(myfield, 'and')
nothing is returned, and I get the message,"Informational: The full-text
search condition contained noise word(s)."
Apperantly, it uses the noise-word list for english, when I insert records
and search on that field.
I thought that when I specify the language as 0x0, the full-text index
should be language neutral and that would mean that it doesn't filter out
noise words(because those are language specific). Am I mistaken, or is it an
Sql Server 2005 beta2 bug?
Thanks in advance,
Peter Striman> I have an Sql server 2005 beta2 problem with the full-text index
> functionality.
Please post to the beta newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1|||Hello,
For questions of SQL server 2005, please post at the following newsgroup:
Welcome to the Microsoft SQL Server 2005 Community Technology (CTP)
Newsgroups
<http://communities.microsoft.com/ne...sqlserver2005&s
lcid=us>
Thanks for cooperation.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql

NON-DETERMINISTIC?

I am trying to create a unique constraint on a computed column. I've tried
unique index, too, but they both fail w/this error:
Server: Msg 1933, Level 16, State 1, Line 2
Cannot create index because the key column 'msgID' is non-deterministic or
imprecise.
ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had to
use REPLACE in order to get rid of the date characters like MM/DD/YY. the
formula for MsgID is: (rtrim([endpoint]) +
replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
BOL says 1) all functions referenced by the expression are deterministic and
precise. 2) all columns referenced in the expression come from the table
containing the computed column and 3) no column reference pulls data from
multiple rows.
All of which I believe I'm good on. Each of these are SET ON:
ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
and NUMERIC_ROUNDABORT is SET OFF.
can somebody help me find what I'm missing?
-- LynnCan you give your table structure and some sample data?
http://www.aspfaq.com/5006
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Also, if you create a primary key or unique constraint on the three base
columns, do you really need the computed column to be explicitly unique
(since it should be unique by definition anyway)? I am often amazed at this
desire to store computed values when you don't have to; views and queries
could easily construct this value on select instead of storing redundant
data...
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Lynn,
The dateformat "1" uses a cutoff year which makes it imprecise.
The following example shows the problem and the solution.
create table #t(mydate datetime not null
,displaydate as convert(varchar(8),mydate,1)
)
create unique index someindex on #t(displaydate)
create table #t2(mydate datetime not null
,displaydate as
substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(10)
,mydate,101),9,2)
)
create unique index someindex2 on #t2(displaydate)
drop table #t
drop table #t2
Gert-Jan
Lynn wrote:
> I am trying to create a unique constraint on a computed column. I've trie
d
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had t
o
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic a
nd
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Gert-Jan, doing that, my value becomes this: 08/26/05
I need this MMDDYY, w/out the forward slashes in there.
is there no way to do that w/out becoming imprecise?
-- Lynn
"Gert-Jan Strik" wrote:

> Lynn,
> The dateformat "1" uses a cutoff year which makes it imprecise.
> The following example shows the problem and the solution.
> create table #t(mydate datetime not null
> ,displaydate as convert(varchar(8),mydate,1)
> )
> create unique index someindex on #t(displaydate)
> create table #t2(mydate datetime not null
> ,displaydate as
> substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(1
0),mydate,101),9,2)
> )
> create unique index someindex2 on #t2(displaydate)
> drop table #t
> drop table #t2
>
> Gert-Jan
>
> Lynn wrote:
>|||Yes, Aaron, unfortunately I do need it, as the composite PK of the three
columns invites duplicates. Meaning, it's endpoint+YYYY-MM-DD
HH:MM:MS:000+orderno. As weird as it may sound, the presence of the time
along w/the date is not desirable because like i said, it invites dupes.
Yes, I know the date w/out the time would seem as though it would do the sam
e
-- but it's something a little native to us, I suppose. So anyway, the
composite pk/constraint won't do, unless there's some way that I am unaware
of that will allow me to strip the time from the datestamp in the
constraint/pk.
can I do that? create a uniqe constraint and/or pk and/or unique index
(ideally, the constraint) on the three columns, but strip the time from the
exectime column?
exectime+endpoint+orderno
-- Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also, if you create a primary key or unique constraint on the three base
> columns, do you really need the computed column to be explicitly unique
> (since it should be unique by definition anyway)? I am often amazed at th
is
> desire to store computed values when you don't have to; views and queries
> could easily construct this value on select instead of storing redundant
> data...
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>
>|||What do you hope to gain from this particular format that cannot also be
accomplished using one of the standard ones. For constraint purposes, the
format of the date is not important. Does the computed column need to be in
this format for visual purposes? If so, why not create a 2nd column for
constraint purposes only.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
> Gert-Jan, doing that, my value becomes this: 08/26/05
> I need this MMDDYY, w/out the forward slashes in there.
> is there no way to do that w/out becoming imprecise?
>
> -- Lynn
>
> "Gert-Jan Strik" wrote:
>|||This particular format is our uniqueID. W/the time, however, it is invalid
for business/application reasons. I started down this path hoping to do a
PK, but learned I could not do a PK on a computed column. Hence, I am tryin
g
both the unique constraint or the unique index, both of which fail w/the
non-deterministic problem. So, possibly for constraint purposes the format
of the date is not important. But it is for our purposes. I am hopeful
that I am in error or possibly missing something quite obvious, but I need
the time stripped from the datetime stamp in the value, whether constraint,
computed or otherwise.
-- Lynn
"Scott Morris" wrote:

> What do you hope to gain from this particular format that cannot also be
> accomplished using one of the standard ones. For constraint purposes, the
> format of the date is not important. Does the computed column need to be
in
> this format for visual purposes? If so, why not create a 2nd column for
> constraint purposes only.
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
>
>|||On Fri, 26 Aug 2005 12:49:04 -0400, "Scott Morris" <bogus@.bogus.com>
wrote:
>What do you hope to gain from this particular format that cannot also be
>accomplished using one of the standard ones. For constraint purposes, the
>format of the date is not important. Does the computed column need to be i
n
>this format for visual purposes? If so, why not create a 2nd column for
>constraint purposes only.
What he said.
J.

Non-deterministic Clustered Index for Indexed View

Hi all,
I am trying to create an indexed view to do aggregation on a table of
Payments. The table has columns as follows:
CREATE TABLE [Payments] (
[PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
[PmtAmt] [smallmoney] NOT NULL ,
[PmtDate] [smalldatetime] NOT NULL ,
[Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
(
[PmtKey]
) ON [PRIMARY]
) ON [PRIMARY]
I am trying to create an indexed view as follows:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
I am doing the cast to try to get all payments grouped by the same date,
ignoring the time portion.
When I go to add a unique clustered index to this view,
CREATE UNIQUE CLUSTERED
INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
WITH
FILLFACTOR = 85
I am told that the column PmtDate is non-detereministic or too imprecise.
I understand the requirement that the indices in an indexed view cannot be
nullable and must be deterministic. Is there another way to cast PmtDate
that will allow me to do the grouping and aggregation I am trying to achieve
?
Thanks.
--
John> Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
CAST is non-deterministic when used with smalldatetime. Try CONVERT with a
style parameter instead:
CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
> Hi all,
> I am trying to create an indexed view to do aggregation on a table of
> Payments. The table has columns as follows:
> CREATE TABLE [Payments] (
> [PmtKey] [int] IDENTITY (1, 1) NOT NULL ,
> [PmtAmt] [smallmoney] NOT NULL ,
> [PmtDate] [smalldatetime] NOT NULL ,
> [Voided] [bit] NOT NULL CONSTRAINT [DF_Payments_Voided] DEFAULT (0),
> CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED
> (
> [PmtKey]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
> I am doing the cast to try to get all payments grouped by the same date,
> ignoring the time portion.
> When I go to add a unique clustered index to this view,
> CREATE UNIQUE CLUSTERED
> INDEX [PK_vwPmts_ByDate] ON [dbo].[vwPayments_ByDate] ([PmtDate])
> WITH
> FILLFACTOR = 85
> I am told that the column PmtDate is non-detereministic or too imprecise.
> I understand the requirement that the indices in an indexed view cannot be
> nullable and must be deterministic. Is there another way to cast PmtDate
> that will allow me to do the grouping and aggregation I am trying to
> achieve?
> Thanks.
> --
> John|||JT (Jthayer@.online.nospam) writes:
> I am trying to create an indexed view as follows:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT
> CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CAST(DATEDIFF(DAY,0,PmtDate) AS SMALLDATETIME)
Dan posted a solution, but it works only on SQL 2005. (I've tested).
On SQL 2000 you may have to let it suffice with:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(char(8), PmtDate, 112)
Thus, you get PmtDate as a char(8) column instead.
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.mspx|||Thanks Dan,
From BOL:
CONVERT: Deterministic unless used with datetime, smalldatetime, or
sql_variant. The datetime and smalldatetime data types are deterministic if
the style parameter is also specified.
In theory, it would sure seem that your solution should work. In practice,
I get the same error. Any other thoughts?
--
John
"Dan Guzman" wrote:

> CAST is non-deterministic when used with smalldatetime. Try CONVERT with
a
> style parameter instead:
> CONVERT(smalldatetime, DATEDIFF(DAY, 0, PmtDate), 112)
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:2DA1D643-75AC-4B61-AE2C-E08C3085F5A7@.microsoft.com...
>
>|||It looks like you are using SQL 2000.
Erland posted one method that will work in SQL 2000. Here's an extention of
that technique that will return a smalldatetime:
CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
Hope this helps.
Dan Guzman
SQL Server MVP
"JT" <Jthayer@.online.nospam> wrote in message
news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
> Thanks Dan,
> From BOL:
> CONVERT: Deterministic unless used with datetime, smalldatetime, or
> sql_variant. The datetime and smalldatetime data types are deterministic
> if
> the style parameter is also specified.
> In theory, it would sure seem that your solution should work. In
> practice,
> I get the same error. Any other thoughts?
> --
> John
>
> "Dan Guzman" wrote:
>|||Dan and Erland,
Thank you both for your posts and wisdom. I independently got it working by
getting rid of the 0 and explicitly setting 1/1/1900 as the index date for
SQL Server's Datediff function. For example:
CREATE VIEW dbo.vwPayments_ByDate
WITH SCHEMABINDING
AS
SELECT
CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME, '1900-01-01
00:00:00', 102), PmtDate), 101) AS PmtDate,
SUM(PmtAmt) AS DaysPmts,
COUNT_BIG (*) AS Expr1
FROM dbo.Payments
WHERE (Voided = 0)
GROUP BY CONVERT(smalldatetime, DATEDIFF([DAY], CONVERT(DATETIME,
'1900-01-01 00:00:00', 102), PmtDate), 101)
Pretty darn ugly, if I may say so. I like your method better.
Thanks again.
John
"Dan Guzman" wrote:

> It looks like you are using SQL 2000.
> Erland posted one method that will work in SQL 2000. Here's an extention
of
> that technique that will return a smalldatetime:
> CONVERT(smalldatetime, CONVERT(char(8), PmtDate, 112), 112) AS PmtDate
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "JT" <Jthayer@.online.nospam> wrote in message
> news:11FDCEC3-F22E-4572-B211-AF822FCCC5AC@.microsoft.com...
>
>|||On second thought, I think I will stick with my convoluted method as it
preserves the column as smalldatetime datatype, which will be helpful in
sorting records for reporting. Sorting dates cast as Char(8) gives you all
of the January's, then the February's when converted to style 101, which is
what I use in my reports.
Thanks again.
--
John
"Erland Sommarskog" wrote:

> JT (Jthayer@.online.nospam) writes:
> Dan posted a solution, but it works only on SQL 2005. (I've tested).
> On SQL 2000 you may have to let it suffice with:
> CREATE VIEW dbo.vwPayments_ByDate
> WITH SCHEMABINDING
> AS
> SELECT CONVERT(char(8), PmtDate, 112) AS PmtDate,
> SUM(PmtAmt) AS DaysPmts,
> COUNT_BIG (*) AS Expr1
> FROM dbo.Payments
> WHERE (Voided = 0)
> GROUP BY CONVERT(char(8), PmtDate, 112)
> Thus, you get PmtDate as a char(8) column instead.
>
> --
> 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.mspx
>|||Let's start with everything is wrong. You have an IDENTITY column, no
key, and use assembly language style BIT flags and proprietary MONEY
data types in spite of the math errors in it. The payments are not
posted to an account or an invoice? Wild guess at a valid design
CREATE TABLE Payments
(invoice_nbr INTEGER NOT NULL,
payment_nbr INTEGER NOT NULL,
PRIMARY KEY (invoice_nbr, payment_nbr),
pmt_amt DECIMAL (12,4) NOT NULL,
pmt_date DATETIME NOT NULL
CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
DATETIME)), -- other ways to do this, too
pmt_status INTEGER NOT NULL);
What if you had a relational approach and not allow bad data that has
to be clean out later? Good DDL will save you from complex kludges.|||Thanks for the enlightenment! You forgot the Volkswagen lecture, as the vie
w
is named vw... The actual table is considerably different than the
simplified example I posted. Seriously, though, thanks for your input,
especially about the datetime column. Something to consider...
--
John
"--CELKO--" wrote:

> Let's start with everything is wrong. You have an IDENTITY column, no
> key, and use assembly language style BIT flags and proprietary MONEY
> data types in spite of the math errors in it. The payments are not
> posted to an account or an invoice? Wild guess at a valid design
> CREATE TABLE Payments
> (invoice_nbr INTEGER NOT NULL,
> payment_nbr INTEGER NOT NULL,
> PRIMARY KEY (invoice_nbr, payment_nbr),
> pmt_amt DECIMAL (12,4) NOT NULL,
> pmt_date DATETIME NOT NULL
> CHECK (pmt_date = CAST (FLOOR(CAST(pmt_date AS FLOAT)) AS
> DATETIME)), -- other ways to do this, too
> pmt_status INTEGER NOT NULL);
>
> What if you had a relational approach and not allow bad data that has
> to be clean out later? Good DDL will save you from complex kludges.
>|||I pikced up a slogan from Graeme Simsion, who is a data quality and
design guru -- "Mop the floor, but then fix the leak!" . I am getting
a presentation on advanced DDL ready for PASS this year. DML gets all
the glory, but good DDL does the real work.
And one day, I will figure out DCL trick.
s

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.

Nonclustered Indexes

Is this true or false based on reading this:
http://msdn.microsoft.com/library/en...asp?frame=true
If a table has a nonclustered index AND a clustered index the
NONCLUSTERED index will use the clustered index key as a row locater?
Meaning SQL has to
1. Decide based on the execution plan whether or not it should use the
non clustered index
2. Scan the nonclustered index to get to the leaf node which contains
the clustered index key
3. Scan the clustered index by that key to get to its leaf node ( which
will be the data page )
4. Find the row within that page
Am I correct?
The clustered index IS the table order. Therefore it becomes the lookup key
for any non-clustered index operation. Steps 2. and 3. should be Search,
not Scan operations on the nonclustered and clustered indexes. Searching
for an item in a modified B-tree structure is a very fast operation. FYI,
the operation in step 3 is called a bookmark lookup.
One of the optimizer's challenges is deciding when to skip the index use and
just scan the table. If the nonclustered index is poorly selective or the
filter criteria returns a very large result set or returns a large
proportion of the underlying table, SQL may decide that the "extra hop" to
do the bookmark lookups is slower than a single pass table scan.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141933932.804305.235590@.u72g2000cwu.googlegr oups.com...
> Is this true or false based on reading this:
> http://msdn.microsoft.com/library/en...asp?frame=true
> If a table has a nonclustered index AND a clustered index the
> NONCLUSTERED index will use the clustered index key as a row locater?
> Meaning SQL has to
> 1. Decide based on the execution plan whether or not it should use the
> non clustered index
> 2. Scan the nonclustered index to get to the leaf node which contains
> the clustered index key
> 3. Scan the clustered index by that key to get to its leaf node ( which
> will be the data page )
> 4. Find the row within that page
> Am I correct?
>
|||Thanks Geoff for your reply.
Now a nonclustered index on a heap table uses the combination of
File ID, page number, and number of row on the page as the ROW ID in
its leaf node.
The docs state that the row locator when a clustered is available "is
the clustered index key for the row"
Does the above mean that the clustered index key takes you DIRECTLY to
the row or it takes you directly to the DATA PAGE that contains the
row?
Because from my understanding the leaf node of a clustered index IS the
data page.
|||For resolving data lookups, Row and Data Page containing the row are
identical concepts. SQL loads and saves data in pages. Going from a page
to a row within a page is a trivial exercise and the two are sometimes used
interchangably when talking about lookup operations. And you are correct,
the leaf level IS the data row for a clustered index.
Note that a clustered index doesn't have to be unique. Earlier versions of
SQL (6.5 and before) worked better when the clustered index was not unique.
That changed with SQL 7.0 and true row-level locking. Now, the default for
SQL is to create a clustered index out of the Primary Key. This is not a
requirement and can be overridden at design time. When the clustered index
is not unique, a uniquifier is added to each row so the index lookup
functions work correctly.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"g3000" <carlton_gregory@.yahoo.com> wrote in message
news:1141938175.459830.100580@.i39g2000cwa.googlegr oups.com...
> Thanks Geoff for your reply.
> Now a nonclustered index on a heap table uses the combination of
> File ID, page number, and number of row on the page as the ROW ID in
> its leaf node.
> The docs state that the row locator when a clustered is available "is
> the clustered index key for the row"
> Does the above mean that the clustered index key takes you DIRECTLY to
> the row or it takes you directly to the DATA PAGE that contains the
> row?
> Because from my understanding the leaf node of a clustered index IS the
> data page.
>
|||One issue not touched upon. Clustered indexes in general, are not a
great idea for most tables.
Non-clustered indexes have relatively low overhead, super fast
addition, update, and delete capabilities, and are extremely fast to
traverse.
Clustered indexes take HUGE hits when you modifiy a column in the
index, potentially when you insert a lot into the middle of the table,
but are equivalent for deletes.
An interesting side note. The non-clustered index finds itself very
quickly into cache for even the largest of tables. Not true of a
clustered index.
And pay attention to the size of the columns in the clustered index.
Pick large columns, and your performance will be seriously downgraded.
In general, if you will ALWAYS be pulling multiple rows of sequential
data, with sequential ALWAYS being defined exactly teh same, then
clustered can make sense. As an example, time stamped data, whree you
need 1000 rows at a time.
Pulling a single row, or reporting data based upon several variables,
and the non-clustered index will typically outperform the clustered
index in all manners.
regards,
doug
|||Perhaps an example.
Public library, the fiction section. The clustered index is the
placement on the shelves due to the author's last name. Really nice if
ALL you EVER did was check out one author's books. You can walk right
to the correct shelf, and grab your books.
OTOH, the card catalogue is the non-clustered index. You know the
subject, or title, you don't start scanning book shelves.
it is MUCH quicker to go to teh card catalogue, get the author's name
and book name, and go to the shelf to get teh right book.
IMO, an EFFICIENT library would just throw the books on any old shelf,
noting where you stuffed it, and updating the card catalogue. Then, on
lookups, book losses, or additions, you just go to teh catalogue, find
the shelf/position, adn get your book.
regards,
doug
|||So basically the clustered index is best when the selectivity is HIGH?
and the queries against the tables are generally the same ( like a
reference table )
non-clustered indexes are good when you are doing something like a
search on a catalog of parts ( or a library )
So seems to me more often then not non clustered will be the best
choice.
Why is the overhead so high for clustered?
|||A poorly selected Clustered index can cause performance degradation. A
well-chosen clustered index will improve performance.
Your example illustrates a poorly chosen Clustered order. Here is a good
one:
Books are assigned a sequential number as they are purchased. They are
stocked on the shelves in that numbered order. Card catalog is used to find
books according to pre-determined criteria. Note that the physical ordering
of the books has nothing to do with most catalog requests. Only when
checking books by purchase date would the indexes align, and then only by
coincidence. This improves insert performance since all the insertion work
happens in the same part of the library. In most libraries, the more recent
a book or periodical, the more frequently it is accessed. A smart librarian
would put those high traffic books near each other and in an easily accesed
place (think cache).
Just because SQL creates a clustered index on the Primary Key by default,
doesn't mean those two entities are irrevocably tied together. A clustered
index is a physical construct. A Primary Key is a logical database design
component. They can be implemented differently.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Doug" <drmiller100@.hotmail.com> wrote in message
news:1142203270.733531.260280@.e56g2000cwe.googlegr oups.com...
> Perhaps an example.
> Public library, the fiction section. The clustered index is the
> placement on the shelves due to the author's last name. Really nice if
> ALL you EVER did was check out one author's books. You can walk right
> to the correct shelf, and grab your books.
> OTOH, the card catalogue is the non-clustered index. You know the
> subject, or title, you don't start scanning book shelves.
> it is MUCH quicker to go to teh card catalogue, get the author's name
> and book name, and go to the shelf to get teh right book.
> IMO, an EFFICIENT library would just throw the books on any old shelf,
> noting where you stuffed it, and updating the card catalogue. Then, on
> lookups, book losses, or additions, you just go to teh catalogue, find
> the shelf/position, adn get your book.
> regards,
> doug
>
|||so in your example, you'd have a clustered index on the identity key,
and this woudl be more efficient then have a non-clustered index?
I disagree. I would suggest having no clustered index would be faster.
the key would be "shorter." Inserts faster.
I am curious as to the logic that a clustered index would make this
scenario faster.
|||Yes Im curious also.
I looking for an aswer as to why the overhead is so high just because
its physical construct.
If I load physical in the same data page then most likely if its high
transaction on that table the same data pages should be found in the
cache.
Obviously the process for searching for an data page with free space
within an extent is slow for a clustered index?
Should clustered index be used when rows will be selected "together"
most of the time? Meaning a low cardinality column?
So in a employee database all sex columns with a "Male" value can have
a clustred index because those records will be selected together?

Nonclustered index question

I have a table that has around 100,000 records in it, growing at a rate
of 200 records/day:
CREATE TABLE [Main] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Code] [char] (17) NOT NULL ,
[AgentID] [int] NULL ,
[DueDate] [smalldatetime] NOT NULL ,
[Status] [varchar] (18) NOT NULL
)
(This table also has about 30 more fields, but I have left them out for
brevity.)
I am looking to speed up a few specific search queries. I already have
a clustered index on my primary key field [ID], and a non-clustered
index on field [Code]. These indexes are fine performance wise.
I am looking to speed up 2 searches:
1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
AND DueDate BETWEEN (...)
2) [Status]: WHERE [Status]=xxx. Actually, this search is already
fast, and [Status] will really only have about 7 distinct values, so I
didn't index this field, as I figured a table scan would be faster in
this case. IS THIS A CORRECT ASSUMPTION. The result set for this
query is almost aways under 25 rows
My main question is: Should I put nonclustered indexes on [DueDate]
and [AgentID]. AgentID will have maybe 25 distinct values throughout
the table. The result set will usually be under 50 rows.
I just want to make sure I am not overindexing the table, and that if I
index these fields it will be beneficial.
Any help would be appreciated.for query 1, an index on ([AgentID], [DueDate]) could be an option, but
it looks like AgentID is not too sleective:

> AgentID will have maybe 25 distinct values throughout
so I'd also consider indexes on ([DueDate], [AgentID]) and just on
([DueDate])|||> 2) [Status]: WHERE [Status]=xxx. Actually, this search is already
> fast, and [Status] will really only have about 7 distinct values, so I
> didn't index this field, as I figured a table scan would be faster in
> this case. IS THIS A CORRECT ASSUMPTION. The result set for this
> query is almost aways under 25 rows
How exactly are you using this column? (i.e. how many rows per each value)
Searching for individual status values might be improved through the use of
indexed views - one per each status value. But may have a negative impact on
inserts/updates.
ML
http://milambda.blogspot.com/|||With 7 distinct values for Status, 100,000 rows in the table, and
WHERE [Status]=xxx returning under 25 rows, I have to assume that the
values for Status are severely skewed, and the status being tested is
one of the low frequency ones. Even with so few values, a query on a
specific low frequency value may in fact benefit from an index. I
would add an index on Status and see if it was used and if it helped.
With 100,000 rows, 25 distinct values for AgentID, and a result set of
around 50 rows, I have to assume that the DueDate BETWEEN test is
quite restrictive. I would try an index on (AgentID,DueDate).
Roy
On 23 Feb 2006 08:29:24 -0800, kaczmar2@.hotmail.com wrote:

>I have a table that has around 100,000 records in it, growing at a rate
>of 200 records/day:
>CREATE TABLE [Main] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Code] [char] (17) NOT NULL ,
> [AgentID] [int] NULL ,
> [DueDate] [smalldatetime] NOT NULL ,
> [Status] [varchar] (18) NOT NULL
> )
>(This table also has about 30 more fields, but I have left them out for
>brevity.)
>I am looking to speed up a few specific search queries. I already have
>a clustered index on my primary key field [ID], and a non-clustered
>index on field [Code]. These indexes are fine performance wise.
>I am looking to speed up 2 searches:
>1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
>AND DueDate BETWEEN (...)
>2) [Status]: WHERE [Status]=xxx. Actually, this search is already
>fast, and [Status] will really only have about 7 distinct values, so I
>didn't index this field, as I figured a table scan would be faster in
>this case. IS THIS A CORRECT ASSUMPTION. The result set for this
>query is almost aways under 25 rows
>My main question is: Should I put nonclustered indexes on [DueDate]
>and [AgentID]. AgentID will have maybe 25 distinct values throughout
>the table. The result set will usually be under 50 rows.
>I just want to make sure I am not overindexing the table, and that if I
>index these fields it will be beneficial.
>Any help would be appreciated.|||kaczmar2@.hotmail.com wrote:
> I have a table that has around 100,000 records in it, growing at a
> rate of 200 records/day:
> CREATE TABLE [Main] (
> [ID] [int] IDENTITY (1, 1) NOT NULL ,
> [Code] [char] (17) NOT NULL ,
> [AgentID] [int] NULL ,
> [DueDate] [smalldatetime] NOT NULL ,
> [Status] [varchar] (18) NOT NULL
> )
> (This table also has about 30 more fields, but I have left them out
> for brevity.)
> I am looking to speed up a few specific search queries. I already
> have a clustered index on my primary key field [ID], and a
> non-clustered index on field [Code]. These indexes are fine
> performance wise.
> I am looking to speed up 2 searches:
> 1) A combination of [AgentID] and [DueDate]: "... WHERE AgentID = n
> AND DueDate BETWEEN (...)
Depending on the selectiveness of your query criteria and distribuition of
values it might in fact be better to change the PK to non clustered and
have a clustered index on (DueDate, AgentID). If inserted DueDates are
increasing then inserts may also benefit from this index layout. On the
other hand, ID will be increasing, too. But for a range query a clustered
index is usually favourable.

> 2) [Status]: WHERE [Status]=xxx. Actually, this search is already
> fast, and [Status] will really only have about 7 distinct values, so I
> didn't index this field, as I figured a table scan would be faster in
> this case. IS THIS A CORRECT ASSUMPTION. The result set for this
> query is almost aways under 25 rows
Assuming equal distribution of values you'll get only 14% of the rows with
this criterion. I would assume that an index pays off here as this seems
pretty selective. Since you seem to be mostly searching for low freq
values it's highly likely that and index would help, especially as the
table grows. The more often you search for low freq values the more the
index pays off.

> My main question is: Should I put nonclustered indexes on [DueDate]
> and [AgentID]. AgentID will have maybe 25 distinct values throughout
> the table. The result set will usually be under 50 rows.
> I just want to make sure I am not overindexing the table, and that if
> I index these fields it will be beneficial.
You'll have to do some testing to get definitive answers. And you
probably should verify your findings from time to time as the table grows.
You might as well create a test table with the same layout and much more
data (but with typical distribution) if you want to see the behavior for
large tables beforehand.
Kind regards
robert

Non-clustered index on a field and a "%" sign

Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.
Not necessarily. It does mean that a scan will be done, not a seek. Here's
a case where it will scan the NC index:
1) You have a NC index on ColA.
2) You run: SELECT ColA FROM MyTable WHERE ColA LIKE '%XYZ'
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<almurph@.altavista.com> wrote in message
news:a8035a0d-7d2f-4ea4-9394-fc3b9b3c6247@.w34g2000hsg.googlegroups.com...
Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.

Non-clustered index on a field and a "%" sign

Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.Not necessarily. It does mean that a scan will be done, not a seek. Here's
a case where it will scan the NC index:
1) You have a NC index on ColA.
2) You run: SELECT ColA FROM MyTable WHERE ColA LIKE '%XYZ'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<almurph@.altavista.com> wrote in message
news:a8035a0d-7d2f-4ea4-9394-fc3b9b3c6247@.w34g2000hsg.googlegroups.com...
Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.|||"almurph@.altavista.com" wrote:
> Folks,
> I'm not sure, but if you have a non-clustered index on a field and
> you have "%" character as the first character of the search parameter
> does this bypass the index? In other words, do you need a certain
> amount of characters before the % sign in SQL Server 2000?
> Any comments/knowledge much appreciated,
> Thanks,
> Al.
It disqualifies the seeking or partial scanning of the index. But as
mentioned by Tom, a full nonclustered index scan could still be used.
If you know that you will always have the % sign preceding the text, and
never after the text, then you could consider storing the data in
REVERSE order, or create a computed column on the REVERSE value, index
that, and use that column in your query.
For example: SELECT * FROM my_table WHERE reversed_domain LIKE
REVERSE('%.altavista.com')
Gert-Jan

Non-clustered index on a field and a "%" sign

Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.Not necessarily. It does mean that a scan will be done, not a seek. Here's
a case where it will scan the NC index:
1) You have a NC index on ColA.
2) You run: SELECT ColA FROM MyTable WHERE ColA LIKE '%XYZ'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
<almurph@.altavista.com> wrote in message
news:a8035a0d-7d2f-4ea4-9394-fc3b9b3c6247@.w34g2000hsg.googlegroups.com...
Folks,
I'm not sure, but if you have a non-clustered index on a field and
you have "%" character as the first character of the search parameter
does this bypass the index? In other words, do you need a certain
amount of characters before the % sign in SQL Server 2000?
Any comments/knowledge much appreciated,
Thanks,
Al.|||"almurph@.altavista.com" wrote:
> Folks,
> I'm not sure, but if you have a non-clustered index on a field and
> you have "%" character as the first character of the search parameter
> does this bypass the index? In other words, do you need a certain
> amount of characters before the % sign in SQL Server 2000?
> Any comments/knowledge much appreciated,
> Thanks,
> Al.
It disqualifies the seeking or partial scanning of the index. But as
mentioned by Tom, a full nonclustered index scan could still be used.
If you know that you will always have the % sign preceding the text, and
never after the text, then you could consider storing the data in
REVERSE order, or create a computed column on the REVERSE value, index
that, and use that column in your query.
For example: SELECT * FROM my_table WHERE reversed_domain LIKE
REVERSE('%.altavista.com')
--
Gert-Jan

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
AminSQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
--
David G.

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
AminSQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
David G.

Nonclustered index helps ORDER BY?

Hi,
Does nonclustered index help ORDER BY clause in the query? If yes, how?
Thanks in advance,
Amin
SQL Server can use a non-clustered index to provide result ordering by
scanning the index in ORDER BY sequence. However, the optimizer might
choose other methods to provide the ordering (e.g. SORT) due to other
factors, such as using another index to satisfy WHERE or JOIN predicates.
Hope this helps.
Dan Guzman
SQL Server MVP
"Amin Sobati" <amins@.morva.net> wrote in message
news:%23sWEpkhgEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes, how?
> Thanks in advance,
> Amin
>
|||Amin Sobati wrote:
> Hi,
> Does nonclustered index help ORDER BY clause in the query? If yes,
> how? Thanks in advance,
> Amin
It will also help if your non-clustered index is a covering index.
Remember, all clustered index keys are a part of non-clustered indexes,
which makes designign covering indexes a little easier... in some cases.
David G.

nonclustered index fields

Hi folks,
I was wondering if it should be taken as a rule of thumb to allways avoid
the us of the fields that conform the primary key when we are designing any
nonclustered index, as in reality they allready have this key in "their
inside".
For example, say we have a pk composed of the fields: date, id
and then we create an index1 with fields : field1, date
and yet another index2 with just field: field1
Then, whenever I search in this simplistic example for field 1 between a
range of dates I allways get better performance with just the simple index.
The question is, will it happen allways or will it depend on the particular
queries and or situations?
Thanks in advance,
Tristan."Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
>
It depends. Often, for instance, you will have a non-clustered index on the
trailing column of a two-column clustered primary key. in your example a
non-clustered index on id would probably be appropriate to enable lookups by
ID. Even though the ID is replicated in the index leaf data, since it is
not the leading column in the clustered primary key, the clustered primary
key does not provide an efficient access path for lookups or sorting by ID.
David|||Tristan
You are confusing PK with clustered index. They are not the same thing.
Clustered index keys are contained in every NC index, but the clustered
index might not be on the primary key column(s).
However, if we assume you meant clustered index when you said pk, then you
answered your own questions. It completely depends on your queries and your
data distributions. A very general rule of thumb is that if you do a lot of
modifications, you want to keep your indexes to a minimum, so you wouldn't
have indexes with overlapping keys. But if you do mainly SELECTs, more
indexes can be useful, and having different leading columns can be a BIG
help. However, in your specific example, your index1 and index2 will be
identical in every way, so there is little reason to have both of them.
I wrote an article for SQL Server Magazine about a year ago on the reasons
why you might want to explicitly list one of your clustered index columns in
your nc index definitions.
http://www.sqlmag.com/Article/Artic...rver_44807.html
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tristan" <Tristan@.discussions.microsoft.com> wrote in message
news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
> Hi folks,
> I was wondering if it should be taken as a rule of thumb to allways avoid
> the us of the fields that conform the primary key when we are designing
> any
> nonclustered index, as in reality they allready have this key in "their
> inside".
> For example, say we have a pk composed of the fields: date, id
> and then we create an index1 with fields : field1, date
> and yet another index2 with just field: field1
> Then, whenever I search in this simplistic example for field 1 between a
> range of dates I allways get better performance with just the simple
> index.
> The question is, will it happen allways or will it depend on the
> particular
> queries and or situations?
> Thanks in advance,
> Tristan.|||Thanks lot Kalen & David for your replies. Indeed I pretendet to say
clustered when I used pk, thanks por pointing that out. Your answers have
being most clarifiying, thanks again,
Tristan.
"Kalen Delaney" wrote:

> Tristan
> You are confusing PK with clustered index. They are not the same thing.
> Clustered index keys are contained in every NC index, but the clustered
> index might not be on the primary key column(s).
> However, if we assume you meant clustered index when you said pk, then you
> answered your own questions. It completely depends on your queries and you
r
> data distributions. A very general rule of thumb is that if you do a lot o
f
> modifications, you want to keep your indexes to a minimum, so you wouldn't
> have indexes with overlapping keys. But if you do mainly SELECTs, more
> indexes can be useful, and having different leading columns can be a BIG
> help. However, in your specific example, your index1 and index2 will be
> identical in every way, so there is little reason to have both of them.
> I wrote an article for SQL Server Magazine about a year ago on the reasons
> why you might want to explicitly list one of your clustered index columns
in
> your nc index definitions.
> http://www.sqlmag.com/Article/Artic...rver_44807.html
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tristan" <Tristan@.discussions.microsoft.com> wrote in message
> news:68D78CF8-E360-4365-9AE6-7CEA2D1C6C68@.microsoft.com...
>
>

NonClustered Index Corruption

Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
Jerry
First off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>
|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>
|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>
|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>
|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>
|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>
|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>
|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>

NonClustered Index Corruption

Hi,
Yesterday I had some corruption in a nonclustered index on a table that
contains over 150 million records. After determining that the NC index was
the cause, I removed the NC index and reran the DBCC CHECKDB statement. The
results were no errors after 20 minutes. Today I recreated the NC index
(single column). I reran both the DBCC CHECKTABLE and DBCC CHECKDB (doing
both to make sure something else isn't causing the issue) - both hung again
(over 5 hours yesterday). Dropping the NC index again and the checks work
fine. Any ideas?
Thanks
JerryFirst off there is no need to run Checktable if you run CheckDB since that
includes it. You say they were hung. What exactly does that mean? Were
they being blocked? Was the spid doing anything? Is the NCI on a single
column, computed etc? In any case if you have hardware corruption going on
it will usually manifest itself in the most active process. Creating an
index on a 150M row table gives a lot of opportunity for corruption if the
hardware is suspect. If you have bad disk sectors then creating the index
again might very well use the same space on the drive.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
> Hi,
> Yesterday I had some corruption in a nonclustered index on a table that
> contains over 150 million records. After determining that the NC index
> was the cause, I removed the NC index and reran the DBCC CHECKDB
> statement. The results were no errors after 20 minutes. Today I
> recreated the NC index (single column). I reran both the DBCC CHECKTABLE
> and DBCC CHECKDB (doing both to make sure something else isn't causing the
> issue) - both hung again (over 5 hours yesterday). Dropping the NC index
> again and the checks work fine. Any ideas?
> Thanks
> Jerry
>|||Andrew,
As I said in the original post I was running both to ensure that some other
table/index etc... was not causing the issue...yes I knowthat DBCC CHECKDB
includes DBCC CHECKTABLE. Again as I stated in the original post the NC
index is on a single column - no it is not a computed column. Hung? A 20
minute operations (DBCC CHECKDB) takes more than 6 hours and never completes
and has to be stopped manually. It could be a hardware issue (RAID 5).
What operations would you suggest to determine if this could be a HW issue?
Thanks
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
> First off there is no need to run Checktable if you run CheckDB since that
> includes it. You say they were hung. What exactly does that mean? Were
> they being blocked? Was the spid doing anything? Is the NCI on a single
> column, computed etc? In any case if you have hardware corruption going
> on it will usually manifest itself in the most active process. Creating
> an index on a 150M row table gives a lot of opportunity for corruption if
> the hardware is suspect. If you have bad disk sectors then creating the
> index again might very well use the same space on the drive.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23hQPzegvFHA.3548@.tk2msftngp13.phx.gbl...
>|||The Disk Management tool shows the logical drive as "Healthy". The system
log in the Event Viewer shows no HW releated disk errors. I checked the
RAID tool on the server and no issues/errors were reported. The network
admin says that we will need to wait til afterhours to run chkdsk. Other
suggestions?
Thanks.
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
> Andrew,
> As I said in the original post I was running both to ensure that some
> other table/index etc... was not causing the issue...yes I knowthat DBCC
> CHECKDB includes DBCC CHECKTABLE. Again as I stated in the original post
> the NC index is on a single column - no it is not a computed column.
> Hung? A 20 minute operations (DBCC CHECKDB) takes more than 6 hours and
> never completes and has to be stopped manually. It could be a hardware
> issue (RAID 5). What operations would you suggest to determine if this
> could be a HW issue?
> Thanks
> Jerry
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OncOn5gvFHA.3588@.tk2msftngp13.phx.gbl...
>|||Jerry,
Sorry I didn't catch the single column. What level of DBCC did you run?
Try with the Physical_Only option first just to see if it goes through. In
any case unless it is blocked it should not just sit there doing nothing for
6 hours. Are you certain it was not just blocked? Hmmm, I would have
expected some errors if it were disk related. I would run chkdisk to be
sure anyway when you can. Next time you try it take a look at the SPID in
sysprocesses to see how much I/O CPU etc. it used before it "hangs".
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
> The Disk Management tool shows the logical drive as "Healthy". The system
> log in the Event Viewer shows no HW releated disk errors. I checked the
> RAID tool on the server and no issues/errors were reported. The network
> admin says that we will need to wait til afterhours to run chkdsk. Other
> suggestions?
> Thanks.
> Jerry
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OdZtGBhvFHA.2880@.TK2MSFTNGP12.phx.gbl...
>|||Andrew,
No worries. I was running DBCC CHECKDB (with the default options). I
dropped and recreated the nonclustered index and reran the DBCC CHECKDB
statment. This time the statement returned in a timely manner with no
errors reported. I'm thinking the drive(s). Does the SQL Server service
deed to be stopped before I have the network guy run the chkdsk command?
Thanks again.
Jerry
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>|||I don't know for sure it it is required to be stopped but I would if I
could<g>. Make sure you have a good backup first.
Andrew J. Kelly SQL MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Andrew,
> No worries. I was running DBCC CHECKDB (with the default options). I
> dropped and recreated the nonclustered index and reran the DBCC CHECKDB
> statment. This time the statement returned in a timely manner with no
> errors reported. I'm thinking the drive(s). Does the SQL Server service
> deed to be stopped before I have the network guy run the chkdsk command?
> Thanks again.
> Jerry
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
>|||Got it. Thanks Andrew.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23lGa7ujvFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I don't know for sure it it is required to be stopped but I would if I
>could<g>. Make sure you have a good backup first.
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:%23wwZTXjvFHA.3860@.TK2MSFTNGP09.phx.gbl...
>|||It wasn't hung - it most likely found some non-clustered index errors and
returned to do a 'deep-dive' where it manually looks up the matching base
table row for each non-clustered index row, and vice-versa to determine
which rows are in error. The normal non-clustered index check mechanism uses
a sophisticated hashing mechanism to avoid such an n-squared algorithm (and
so is very fast) but if an error is discovered, the very-slow deep-dive is
necessary to determine which rows are in error.
Thanks
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uRiqlrhvFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Jerry,
> Sorry I didn't catch the single column. What level of DBCC did you run?
> Try with the Physical_Only option first just to see if it goes through.
> In any case unless it is blocked it should not just sit there doing
> nothing for 6 hours. Are you certain it was not just blocked? Hmmm, I
> would have expected some errors if it were disk related. I would run
> chkdisk to be sure anyway when you can. Next time you try it take a look
> at the SPID in sysprocesses to see how much I/O CPU etc. it used before it
> "hangs".
> --
> Andrew J. Kelly SQL MVP
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:e$JgyOhvFHA.3080@.tk2msftngp13.phx.gbl...
>