Index tree

I can understand the number of table records will affect the width of the
tree. But why it also affect the depth of the tree ? Or how the key size
will afftect depth and width of the index tree ?
Alan,
The b-tree will increase its depth to improve the query time by reducing
the number of I/Os. It does this by shortening the path to the data, or
leaf node.
Let's say you have an index on surname. The more data in the table, the
greater the depth of the b-tree will result in fewer I/Os. I'm finding
this topic difficult to describe in a newsgroup posting, so hopefully
the following articles on B-Trees will help you:
B-tree algorithms
http://www.semaphorecorp.com/btp/algo.html
Binary tree
http://en.wikipedia.org/wiki/Binary_tree
binary tree
http://planetmath.org/encyclopedia/BinaryTree.html
I think B-Tree actually stands for Balanced Tree, not Binary Tree; but
the two terms are often used synonymously.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> I can understand the number of table records will affect the width of the
> tree. But why it also affect the depth of the tree ? Or how the key size
> will afftect depth and width of the index tree ?
>
|||On Tue, 7 Dec 2004 15:03:09 +1100, Alan wrote:

>I can understand the number of table records will affect the width of the
>tree. But why it also affect the depth of the tree ? Or how the key size
>will afftect depth and width of the index tree ?
Hi Alan,
I'll use an example to explain. Let's assume you have a nonclustered index
with 800 bytes in the indexed columns and another 800 bytes in the
clustered key.
The leaf pages store both the indexed values and the corresponding values
in the clustered index (as locator to the actual row). Other pages (root,
intermediate) store only the indexed values. Since each page is about 8K,
a leaf page holds values for 5 rows; other pages have pointers for 10
rows.
If the number of rows in the table is 10, we need two leaf pages (assuming
no empty space, which will not always be the case in practice) to hold all
these rows. The root page will have the indexed values of the first row on
leaf page 1 and the first row on page 2; the rest of the root page remains
empty. This B-tree has depth 2 (root is level 1; leaf at level 2).
If we add another 40 rows for a total of 50, we need 10 leaf pages. The
root page will have the indexed values of the first row on each of these
10 leaf pages and no room to spare.
One extra row, bringing the total to 51, means we now need 11 leaf pages.
Since the root page can only point to max 10 pages, we need to add a
level. The new B-tree will have one root, two intermediate (level-2) and
11 data pages. One intermediate page will have the indexed values of the
first row on the first 5 or 6 leaf pages; the other intermediate page has
the indexed values of the first row on the remaining leaf pages; the root
page will only hold the indexed values of the first row of the two
intermediate pages. Note that we now have depth 3: root at level 1,
intermediate at level 2 and leaf at level 3.
We can now continue to add rows. When there are 500 rows, there are 100
leaf pages, 10 intermediate pages (each pointing to 10 leaf pages) and 1
root page (pointing to the 10 intermediate pages). All these pages are
completely full: when the 501st row is added, another level has to be
added to the index and it is now at depth 4.
The above shows how number of rows affects the B-tree depth. To see how
key size affects B-tree depth as well, imagine what happens if the indexed
columns are not 800 but 80 bytes: now you can store the indexed values of
not 10 but 100 rows in non-leaf pages. For the leaf pages, the capacity
would increase to (8K / (80 + 800)) = 9 rows. If the size of the clustered
index would decrease as well, this number would rise even further. This of
course means that you can add more rows until all leaf, intermediate and
root pages are full and another level has to be added.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
sql

Index tree

I can understand the number of table records will affect the width of the
tree. But why it also affect the depth of the tree ? Or how the key size
will afftect depth and width of the index tree ?Alan,
The b-tree will increase its depth to improve the query time by reducing
the number of I/Os. It does this by shortening the path to the data, or
leaf node.
Let's say you have an index on surname. The more data in the table, the
greater the depth of the b-tree will result in fewer I/Os. I'm finding
this topic difficult to describe in a newsgroup posting, so hopefully
the following articles on B-Trees will help you:
B-tree algorithms
http://www.semaphorecorp.com/btp/algo.html
Binary tree
http://en.wikipedia.org/wiki/Binary_tree
binary tree
http://planetmath.org/encyclopedia/BinaryTree.html
I think B-Tree actually stands for Balanced Tree, not Binary Tree; but
the two terms are often used synonymously.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Alan wrote:
> I can understand the number of table records will affect the width of the
> tree. But why it also affect the depth of the tree ? Or how the key size
> will afftect depth and width of the index tree ?
>|||On Tue, 7 Dec 2004 15:03:09 +1100, Alan wrote:
>I can understand the number of table records will affect the width of the
>tree. But why it also affect the depth of the tree ? Or how the key size
>will afftect depth and width of the index tree ?
Hi Alan,
I'll use an example to explain. Let's assume you have a nonclustered index
with 800 bytes in the indexed columns and another 800 bytes in the
clustered key.
The leaf pages store both the indexed values and the corresponding values
in the clustered index (as locator to the actual row). Other pages (root,
intermediate) store only the indexed values. Since each page is about 8K,
a leaf page holds values for 5 rows; other pages have pointers for 10
rows.
If the number of rows in the table is 10, we need two leaf pages (assuming
no empty space, which will not always be the case in practice) to hold all
these rows. The root page will have the indexed values of the first row on
leaf page 1 and the first row on page 2; the rest of the root page remains
empty. This B-tree has depth 2 (root is level 1; leaf at level 2).
If we add another 40 rows for a total of 50, we need 10 leaf pages. The
root page will have the indexed values of the first row on each of these
10 leaf pages and no room to spare.
One extra row, bringing the total to 51, means we now need 11 leaf pages.
Since the root page can only point to max 10 pages, we need to add a
level. The new B-tree will have one root, two intermediate (level-2) and
11 data pages. One intermediate page will have the indexed values of the
first row on the first 5 or 6 leaf pages; the other intermediate page has
the indexed values of the first row on the remaining leaf pages; the root
page will only hold the indexed values of the first row of the two
intermediate pages. Note that we now have depth 3: root at level 1,
intermediate at level 2 and leaf at level 3.
We can now continue to add rows. When there are 500 rows, there are 100
leaf pages, 10 intermediate pages (each pointing to 10 leaf pages) and 1
root page (pointing to the 10 intermediate pages). All these pages are
completely full: when the 501st row is added, another level has to be
added to the index and it is now at depth 4.
The above shows how number of rows affects the B-tree depth. To see how
key size affects B-tree depth as well, imagine what happens if the indexed
columns are not 800 but 80 bytes: now you can store the indexed values of
not 10 but 100 rows in non-leaf pages. For the leaf pages, the capacity
would increase to (8K / (80 + 800)) = 9 rows. If the size of the clustered
index would decrease as well, this number would rise even further. This of
course means that you can add more rows until all leaf, intermediate and
root pages are full and another level has to be added.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Index statistics and a primary key

Hi

I have a question regarding updating statistics for a primary key.

Background: An update statistics with fullscan is sometimes taking 30 minutes - the table is 80 million rows, with only 4 columns. The table is truncated, and then 80 million rows inserted all in one go.

Now why the update stats is taking that long is another question (I have no idea - any thoughts?), but my question is; Since you can't disable the "not automatically recompute statistics" option for a primary key, and you would think it would be imperitive for the stats to be kept up to date for a PK for inserts.... does this mean the stats would be kept up to date? and an update stat with fullscan isn't required?

Hope someone can help

Thanks
James

If you have not changed anything statistics will be automatically updated by SQL Server after a number of modifications have been made to the table. SQL Server 2005 updates the counter that checks the number of modifications for BULK INSERT too. When autostats kicks in only a sample of the data is used to calculate them.

Doing a full scan requires a lot of I/O so with 80 million rows it might be slow if your disk subsystem is not fast enough..

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

sql

Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju Samuel
Hi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =
b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel
|||Thanks Uri.

Index size.

Where can I get the size of the index to add to the below query.
select
t.name [Table],
i.Name [Index],
c.name [Column],
ic.key_ordinal [Key Ordinal],
is_included_column [Included],
is_descending_key [Descending key],
i.type_desc [Index Type],
i.is_unique [Unique Index],
i.is_disabled [Index disabled],
i.fill_factor [Fill Factor],
i.is_hypothetical [hypothetical]
from
sys.indexes i
inner join sys.index_columns ic
on i.index_id = ic.index_id
and i.object_id = ic.object_id
inner join sys.columns c
on c.column_id = ic.column_id
and c.object_id = ic.object_id
inner join sys.tables t
on i.object_id = t.object_id
where t.name='Orders'
order by
t.object_id,
i.index_id,
is_included_column,
c.column_id
Thanks
Shiju SamuelHi
SELECT object_name(a.[object_id]) as TableName,a.index_id,
isnull(b.name,'HEAP') as IndexName, sum(a.page_count) as
pages,sum(a.page_count)*1.0/1024 as Mb
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL,NULL, NULL, 'DETAILED')
AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id =b.index_id
group by a.[object_id],a.index_id, b.name
order by pages desc;
"Shiju Samuel" <shiju.samuel@.gmail.com> wrote in message
news:a3495e0c-cb67-4dfd-9eca-671e8bee8e0d@.y43g2000hsy.googlegroups.com...
> Where can I get the size of the index to add to the below query.
> select
> t.name [Table],
> i.Name [Index],
> c.name [Column],
> ic.key_ordinal [Key Ordinal],
> is_included_column [Included],
> is_descending_key [Descending key],
> i.type_desc [Index Type],
> i.is_unique [Unique Index],
> i.is_disabled [Index disabled],
> i.fill_factor [Fill Factor],
> i.is_hypothetical [hypothetical]
> from
> sys.indexes i
> inner join sys.index_columns ic
> on i.index_id = ic.index_id
> and i.object_id = ic.object_id
> inner join sys.columns c
> on c.column_id = ic.column_id
> and c.object_id = ic.object_id
> inner join sys.tables t
> on i.object_id = t.object_id
> where t.name='Orders'
> order by
> t.object_id,
> i.index_id,
> is_included_column,
> c.column_id
> Thanks
> Shiju Samuel|||Thanks Uri.

Index related problems? Whats happening here?

All queries for a particular table seems to be slow. It has one
clustered index on the primary key column which of data type INT and
has identity insert ON. This table has < 10000 rows and is fast with
response in all other circumstances. The clustered index is at a fill
factor of 90% and I have toyed upto 70% fillfactor.
When it is slow I ran DBCC SHOWCONTIG and there were signs of
fragmentation which didn't look very serious. The BOL says it is not
reliable for smaller tables.
I run DBCC INDEXDEFRAG on a particular database. The results suggest
that there were 72 pages and 72 pages were moved and 0 deleted. Still
no improvement in performance.
I run DBCC DBREINDEX and viola query runs fast... I am happy but what
is happening here?
All help is welcome and appreciated...
ThanksDid you do a lot of updates/inserts/deletes and you didn't update statistics?
http://sqlservercode.blogspot.com/
"MasterNone" wrote:
> All queries for a particular table seems to be slow. It has one
> clustered index on the primary key column which of data type INT and
> has identity insert ON. This table has < 10000 rows and is fast with
> response in all other circumstances. The clustered index is at a fill
> factor of 90% and I have toyed upto 70% fillfactor.
> When it is slow I ran DBCC SHOWCONTIG and there were signs of
> fragmentation which didn't look very serious. The BOL says it is not
> reliable for smaller tables.
> I run DBCC INDEXDEFRAG on a particular database. The results suggest
> that there were 72 pages and 72 pages were moved and 0 deleted. Still
> no improvement in performance.
> I run DBCC DBREINDEX and viola query runs fast... I am happy but what
> is happening here?
>
> All help is welcome and appreciated...
> Thanks
>|||I had been monitoring the inserts they are of the order of 10-11 for a
table of 7500 rows. There were the same number of updates but not to
the primary key/indexed column. Currently the Autoupdate Statistics
option is turned on.|||MasterNone wrote:
> I had been monitoring the inserts they are of the order of 10-11 for a
> table of 7500 rows. There were the same number of updates but not to
> the primary key/indexed column. Currently the Autoupdate Statistics
> option is turned on.
Please post table DDL and your slow queries. You should also look at the
query plan with QA. A common cause for the phenomenon you seem to observe
is that the index is not used at all.
Regards
robert

index questions

there are two types of indexes in microsoft sql server

a) clustered
b) non clustered

Question
can u create a primary key which is non clustered ?
If yes what is the syntax ?

Question

can u create a compound primary key and if yes what is the syntax ?

Please excuse me from asking basic questions ?> there are two types of indexes in microsoft sql server
> a) clustered
> b) non clustered
> Question
> can u create a primary key which is non clustered ?
> If yes what is the syntax ?
> Question
> can u create a compound primary key and if yes what is the syntax ?
>
> Please excuse me from asking basic questions ?
Yes, u can!

use tempdb
go
create table test (
tID int NOT NULL
,tText varchar(100) NOT NULL
)
ALTER TABLE test ADD
CONSTRAINT [test_PK] PRIMARY KEY NONCLUSTERED (tID)
CREATE CLUSTERED INDEX [IX_test_tText] ON [test] (tText)|||
Thanks Gary

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Index Question

I have a series of many-to-many relationships, so obviously have a number of
'join' tables. These join tables store only the unique key from the other
tables.
Is there any advantage to indexing the fields in the join tables or is it
not necessary because the data in them is technically indexed in another
table or do they still need indexing in their own right?
Thanks
Keith
It is always good practice to create an index on foreign key column as the
linking column
( I assume you have pimary key with clustered index)
An index on a foreign key column can substantially boost the performance of
many joins.
"Keith" <@..> wrote in message news:O1DllPVSEHA.2408@.tk2msftngp13.phx.gbl...
> I have a series of many-to-many relationships, so obviously have a number
of
> 'join' tables. These join tables store only the unique key from the other
> tables.
> Is there any advantage to indexing the fields in the join tables or is it
> not necessary because the data in them is technically indexed in another
> table or do they still need indexing in their own right?
> Thanks
>

Index Question

Hello,
I have a 500,000 record table with the primary key being a bigint
identity column (clustered). I have another column (smallint) that it is not
unique and only has 20 possible values, this column is indexed in ascending
order.
When I do a select statement in the query analyzer filtering by the
smallint column, I notice that in the execution plan the index of this
column is not being used, it does only a clustered scan. Is it because the
smallint column is not unique? Other reason?
Thanks in advance...
Jose.
Most likely because of the low selectivity on that index. With a very low
number of unique values compared to the number of rows in the table, doing
an index seek is probably more expensive than a table (or clustered index)
scan. This is particularly true if the query is not "covered" by the index
in question. You could verify this by using an index hint in your query and
looking at the execution plan compared to the execution plan for the full
scan.
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a 500,000 record table with the primary key being a bigint
> identity column (clustered). I have another column (smallint) that it is
not
> unique and only has 20 possible values, this column is indexed in
ascending
> order.
> When I do a select statement in the query analyzer filtering by the
> smallint column, I notice that in the execution plan the index of this
> column is not being used, it does only a clustered scan. Is it because the
> smallint column is not unique? Other reason?
> Thanks in advance...
> Jose.
>
|||Yes I did it and the smallint index turned out with 0% cost.
Thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23bxFqwULEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Most likely because of the low selectivity on that index. With a very low
> number of unique values compared to the number of rows in the table, doing
> an index seek is probably more expensive than a table (or clustered index)
> scan. This is particularly true if the query is not "covered" by the
index
> in question. You could verify this by using an index hint in your query
and[vbcol=seagreen]
> looking at the execution plan compared to the execution plan for the full
> scan.
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> not
> ascending
the
>
sql

Index Question

Hello,

In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys be
disabled? If Indexes (PK's and UN Constraints) can be disabled what happens
if data is inserted while disable? Will the index be rebuilt when enabled?

Thanks,
Rob PanoshRob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> happens if data is inserted while disable? Will the index be rebuilt
> when enabled?

You can disable foreign-key constraints. When you re-enable them, SQL
Server verifies that the data comply to the constraint.

You cannot disable primary-key or unique constraints, nor indexes.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks ...

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns940E15C48E6CYazorman@.127.0.0.1...
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Unfortunately, SQL-Server does not automatically check the existing
table data after turning a constraint back on. IMO it is a Microsoft
mistake to allow a database to get corrupted this way.

Gert-Jan

Erland Sommarskog wrote:
> Rob Panosh (rob_!!!NO!!!SPAM!!!_panosh@.asdsoftadfdware.com) writes:
> > In MSS 2k can Primary Key, Unique Constraints, Indexes and Foreign Keys
> > be disabled? If Indexes (PK's and UN Constraints) can be disabled what
> > happens if data is inserted while disable? Will the index be rebuilt
> > when enabled?
> You can disable foreign-key constraints. When you re-enable them, SQL
> Server verifies that the data comply to the constraint.
> You cannot disable primary-key or unique constraints, nor indexes.
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> Unfortunately, SQL-Server does not automatically check the existing
> table data after turning a constraint back on.

You're right, Gert-Jan. Thanks for correcting my mistake.

> IMO it is a Microsoft mistake to allow a database to get corrupted this
> way.

I can see situations where you may want this, but its deceivable that
the constraint is not rechecked. Not only it makes you think that you
have a sound table. If you use the column with a CHECK constraint in a
partitioned view, you will scratch your hair, trying to find out why
SQL Server accesses all tables after this operation.

It is possible to identify this situation though. The example is
augmented script from Books Online:

SET QUOTED_IDENTIFIER OFF
go
CREATE TABLE cnst_example
(id INT NOT NULL,
name VARCHAR(10) NOT NULL,
salary MONEY NOT NULL
CONSTRAINT salary_cap CHECK (salary < 100000)
)
go
-- Valid inserts
INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
go
-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
go
-- Reenable the constraint and try another insert, will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,"Eric James",110000)
go
-- Returns 1, because constraint has been disabled.
select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')"

Interesting, I did not know this yet. Something for a standard script to
check the database...

Gert-Jan

Erland Sommarskog wrote:
> Gert-Jan Strik (sorry@.toomuchspamalready.nl) writes:
> > Unfortunately, SQL-Server does not automatically check the existing
> > table data after turning a constraint back on.
> You're right, Gert-Jan. Thanks for correcting my mistake.
> > IMO it is a Microsoft mistake to allow a database to get corrupted this
> > way.
> I can see situations where you may want this, but its deceivable that
> the constraint is not rechecked. Not only it makes you think that you
> have a sound table. If you use the column with a CHECK constraint in a
> partitioned view, you will scratch your hair, trying to find out why
> SQL Server accesses all tables after this operation.
> It is possible to identify this situation though. The example is
> augmented script from Books Online:
> SET QUOTED_IDENTIFIER OFF
> go
> CREATE TABLE cnst_example
> (id INT NOT NULL,
> name VARCHAR(10) NOT NULL,
> salary MONEY NOT NULL
> CONSTRAINT salary_cap CHECK (salary < 100000)
> )
> go
> -- Valid inserts
> INSERT INTO cnst_example VALUES (1,"Joe Brown",65000)
> INSERT INTO cnst_example VALUES (2,"Mary Smith",75000)
> go
> -- This insert violates the constraint.
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Disable the constraint and try again.
> ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (3,"Pat Jones",105000)
> go
> -- Reenable the constraint and try another insert, will fail.
> ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
> INSERT INTO cnst_example VALUES (4,"Eric James",110000)
> go
> -- Returns 1, because constraint has been disabled.
> select objectproperty(object_id('salary_cap'), 'CnstIsNotTrusted')
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

Index Question

Hello,
I have a 500,000 record table with the primary key being a bigint
identity column (clustered). I have another column (smallint) that it is not
unique and only has 20 possible values, this column is indexed in ascending
order.
When I do a select statement in the query analyzer filtering by the
smallint column, I notice that in the execution plan the index of this
column is not being used, it does only a clustered scan. Is it because the
smallint column is not unique? Other reason?
Thanks in advance...
Jose.Most likely because of the low selectivity on that index. With a very low
number of unique values compared to the number of rows in the table, doing
an index seek is probably more expensive than a table (or clustered index)
scan. This is particularly true if the query is not "covered" by the index
in question. You could verify this by using an index hint in your query and
looking at the execution plan compared to the execution plan for the full
scan.
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a 500,000 record table with the primary key being a bigint
> identity column (clustered). I have another column (smallint) that it is
not
> unique and only has 20 possible values, this column is indexed in
ascending
> order.
> When I do a select statement in the query analyzer filtering by the
> smallint column, I notice that in the execution plan the index of this
> column is not being used, it does only a clustered scan. Is it because the
> smallint column is not unique? Other reason?
> Thanks in advance...
> Jose.
>|||Yes I did it and the smallint index turned out with 0% cost.
Thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23bxFqwULEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Most likely because of the low selectivity on that index. With a very low
> number of unique values compared to the number of rows in the table, doing
> an index seek is probably more expensive than a table (or clustered index)
> scan. This is particularly true if the query is not "covered" by the
index
> in question. You could verify this by using an index hint in your query
and
> looking at the execution plan compared to the execution plan for the full
> scan.
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> not
> ascending
the[vbcol=seagreen]
>sql

Index Question

I have a series of many-to-many relationships, so obviously have a number of
'join' tables. These join tables store only the unique key from the other
tables.
Is there any advantage to indexing the fields in the join tables or is it
not necessary because the data in them is technically indexed in another
table or do they still need indexing in their own right?
ThanksKeith
It is always good practice to create an index on foreign key column as the
linking column
( I assume you have pimary key with clustered index)
An index on a foreign key column can substantially boost the performance of
many joins.
"Keith" <@..> wrote in message news:O1DllPVSEHA.2408@.tk2msftngp13.phx.gbl...
> I have a series of many-to-many relationships, so obviously have a number
of
> 'join' tables. These join tables store only the unique key from the other
> tables.
> Is there any advantage to indexing the fields in the join tables or is it
> not necessary because the data in them is technically indexed in another
> table or do they still need indexing in their own right?
> Thanks
>

index question

Hi,
I have two tables -- A & B, which have primary key and
content lots of records respectively. B table has
foreign key refer to A table. If I create a index for
this foreign key in table B, will it improve performance
when I join these two tables in my query? Any suggestion
to improve the performance during join?
Thank you.
YulingYes It may improve a performance
Also look at join hints on BOL.
"Yuling" <ytu@.creativelabs.com> wrote in message
news:044601c35ade$51d3cb20$a601280a@.phx.gbl...
> Hi,
> I have two tables -- A & B, which have primary key and
> content lots of records respectively. B table has
> foreign key refer to A table. If I create a index for
> this foreign key in table B, will it improve performance
> when I join these two tables in my query? Any suggestion
> to improve the performance during join?
> Thank you.
> Yuling|||You should generally index all primary and foreign keys when doing joins...
If there are where clauses, you may see performance improvements if you
create non-clustered index on one of the highly selective where clause
criteria.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it community
of SQL Server professionals.
www.sqlpass.org
"Yuling" <ytu@.creativelabs.com> wrote in message
news:044601c35ade$51d3cb20$a601280a@.phx.gbl...
> Hi,
> I have two tables -- A & B, which have primary key and
> content lots of records respectively. B table has
> foreign key refer to A table. If I create a index for
> this foreign key in table B, will it improve performance
> when I join these two tables in my query? Any suggestion
> to improve the performance during join?
> Thank you.
> Yuling

Index Question

I have a series of many-to-many relationships, so obviously have a number of
'join' tables. These join tables store only the unique key from the other
tables.
Is there any advantage to indexing the fields in the join tables or is it
not necessary because the data in them is technically indexed in another
table or do they still need indexing in their own right?
ThanksKeith
It is always good practice to create an index on foreign key column as the
linking column
( I assume you have pimary key with clustered index)
An index on a foreign key column can substantially boost the performance of
many joins.
"Keith" <@..> wrote in message news:O1DllPVSEHA.2408@.tk2msftngp13.phx.gbl...
> I have a series of many-to-many relationships, so obviously have a number
of
> 'join' tables. These join tables store only the unique key from the other
> tables.
> Is there any advantage to indexing the fields in the join tables or is it
> not necessary because the data in them is technically indexed in another
> table or do they still need indexing in their own right?
> Thanks
>

Index Question

Hello,
I have a 500,000 record table with the primary key being a bigint
identity column (clustered). I have another column (smallint) that it is not
unique and only has 20 possible values, this column is indexed in ascending
order.
When I do a select statement in the query analyzer filtering by the
smallint column, I notice that in the execution plan the index of this
column is not being used, it does only a clustered scan. Is it because the
smallint column is not unique? Other reason?
Thanks in advance...
Jose.Most likely because of the low selectivity on that index. With a very low
number of unique values compared to the number of rows in the table, doing
an index seek is probably more expensive than a table (or clustered index)
scan. This is particularly true if the query is not "covered" by the index
in question. You could verify this by using an index hint in your query and
looking at the execution plan compared to the execution plan for the full
scan.
"Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a 500,000 record table with the primary key being a bigint
> identity column (clustered). I have another column (smallint) that it is
not
> unique and only has 20 possible values, this column is indexed in
ascending
> order.
> When I do a select statement in the query analyzer filtering by the
> smallint column, I notice that in the execution plan the index of this
> column is not being used, it does only a clustered scan. Is it because the
> smallint column is not unique? Other reason?
> Thanks in advance...
> Jose.
>|||Yes I did it and the smallint index turned out with 0% cost.
Thanks.
"Don Peterson" <no1@.nunya.com> wrote in message
news:%23bxFqwULEHA.3472@.TK2MSFTNGP09.phx.gbl...
> Most likely because of the low selectivity on that index. With a very low
> number of unique values compared to the number of rows in the table, doing
> an index seek is probably more expensive than a table (or clustered index)
> scan. This is particularly true if the query is not "covered" by the
index
> in question. You could verify this by using an index hint in your query
and
> looking at the execution plan compared to the execution plan for the full
> scan.
> "Jose Ines Cantu Arrambide" <joseine@.nospam.com> wrote in message
> news:%23dAp9jULEHA.1156@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> > I have a 500,000 record table with the primary key being a bigint
> > identity column (clustered). I have another column (smallint) that it is
> not
> > unique and only has 20 possible values, this column is indexed in
> ascending
> > order.
> > When I do a select statement in the query analyzer filtering by the
> > smallint column, I notice that in the execution plan the index of this
> > column is not being used, it does only a clustered scan. Is it because
the
> > smallint column is not unique? Other reason?
> >
> > Thanks in advance...
> > Jose.
> >
> >
>

Index or not to index

I have a "Products" table, which currently only has the idProduct primary
key indexed. I now have a requirement to perform searches through an ASP
front end (ASP front end issuing SQL directly against SQL using SQL Server
OLE DB Provider) on
- a details text(16) field
- a description varchar(250) field
- a descriptionLong varchar(250) field
At any time, this table would have around 150-300 records, although records
do get added and deleted from time to time. I am considering Creating an
INDEX on details, description and descriptionLong field. However, I am
concerned
- whether creating an index on such a relatively small table would really
give performance gain or would it just add towards "inefficiency"
- if creating index is deemed a good idea, then what kind of index should I
use?
- Since records get added and deleted from time to time, would it be a good
idea to "re-index" the table (presumably, as part of the DB Maintenance
plan)?
Many thanks in advance!I think you shouldn't create an index because this index will be hardly used
but will decrease performance of DML commands execution
"Patrick" <patl@.reply.newsgroup.msn.com> wrote in message
news:OjTbwYXQEHA.620@.TK2MSFTNGP10.phx.gbl...
> I have a "Products" table, which currently only has the idProduct primary
> key indexed. I now have a requirement to perform searches through an ASP
> front end (ASP front end issuing SQL directly against SQL using SQL Server
> OLE DB Provider) on
> - a details text(16) field
> - a description varchar(250) field
> - a descriptionLong varchar(250) field
> At any time, this table would have around 150-300 records, although
records
> do get added and deleted from time to time. I am considering Creating an
> INDEX on details, description and descriptionLong field. However, I am
> concerned
> - whether creating an index on such a relatively small table would really
> give performance gain or would it just add towards "inefficiency"
> - if creating index is deemed a good idea, then what kind of index should
I
> use?
> - Since records get added and deleted from time to time, would it be a
good
> idea to "re-index" the table (presumably, as part of the DB Maintenance
> plan)?
> Many thanks in advance!
>|||Definitelly not to index. It is too small, index fields are too large...|||Hi Patrick,
Alex Cieszinski and Bojidar Alexandrov has give you their suggestions :) I
wanted to post a quick note to see if you would like additional assistance
or information regarding this particular issue.
We appreciate your patience and look forward to hearing from you!
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Are you Michael or Mingqing after all :)|||Hi Bojidar,
Michael is Mingqing :)
Thank you :D
Sincerely yours,
Mingqing Cheng
Microsoft Online Support
---
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

index on PK

Hello, could someone please tell me why you would index a Primary Key column. By default is it not already indexed, and isn't a primary key unique? If so why would you index a unique column? I'm sure this is a dumb question and I probably don't understand the concept of indexes in SQL. Any insight is well appreciated. Thanks.BOL:

When you specify a PRIMARY KEY constraint for a table, Microsoft SQL Server 2000 enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries.

Index on IsRowGuidCol ?

For merge replication I have only a few tables where the primary key spans
two columns. For the rest of the tables the primary key is one column and
is a uniqueidentifier.
For the fre tables where it spans two columns, I have to add an additional
uniqueidentifier column and mark it as the RowGuid Column for merge
replication.
My Question:
Will adding an index on this column speed merge replication?
--Pepto
Merge replication will add a unique index to the column that it uses for
merge replication. I believe in this case that it will create another
rowguid column to use.
Three conditions have to be met to precreate a rowguid column for merge
replication to use
1) it is called rowguid
2) its data type is uniqueidentifier ROWGUIDCOLUMN
3) it has a unique index on it.
If any of the three conditions are not met, merge replication will create
another column to use that meets these conditions although its name will be
slightly different - rowguidi where i increments.
"Pepto" <pepto@.hotmail.com> wrote in message
news:Ooh2BduPFHA.164@.TK2MSFTNGP12.phx.gbl...
> For merge replication I have only a few tables where the primary key spans
> two columns. For the rest of the tables the primary key is one column and
> is a uniqueidentifier.
> For the fre tables where it spans two columns, I have to add an additional
> uniqueidentifier column and mark it as the RowGuid Column for merge
> replication.
> My Question:
> Will adding an index on this column speed merge replication?
> --Pepto
>
>

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Julesa non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough

Index on foreign key [newbie] ?

Hi,
I need to track which modules a user is able to access. I'm designing a
table called UserPermissions which suits this purpose, where the columns
are:
ID int
UserID int
ModuleID int
AllowDeny bit
UserID is a foreign key which points to the "ID" column of another table
called Users. Now, here's the question:
I'll be writing a lot of queries that check access based on the user's
ID. For example:
"SELECT ModuleID FROM UserPermissions WHERE UserID=777"
Does this mean that I should create an index based on UserID since it is
frequently used in WHERE clauses? For example, should I create a
non-clustered unique index consisting of UserID+ModuleID since I know that
this combination will always be unique? ... or is the fact that UserID a
foreign key enough to cause SQL Server to optimize lookup based on UserID
automatically without my having to explicitly create an index?
For my trivial example, the decision of whether or not to use an index
probably won't make a difference in performance. I'm just trying to
understand the general concept of whether or not it's wise to create indexes
based on foreign keys or if SQL automatically indexes these foreign keys for
you [like it does for primary keys].
Julesa non-clust on UserID will do you fine. composite other columns if they will
be part of select
can you give an excact example of a query that you will execute ?
"Jules Winfield" <ghetto@.englewood.com> wrote in message
news:j7GdnR2cWLbEffXeRVn-tw@.giganews.com...
> Hi,
> I need to track which modules a user is able to access. I'm designing a
> table called UserPermissions which suits this purpose, where the columns
> are:
> ID int
> UserID int
> ModuleID int
> AllowDeny bit
> UserID is a foreign key which points to the "ID" column of another
> table called Users. Now, here's the question:
> I'll be writing a lot of queries that check access based on the user's
> ID. For example:
> "SELECT ModuleID FROM UserPermissions WHERE UserID=777"
> Does this mean that I should create an index based on UserID since it
> is frequently used in WHERE clauses? For example, should I create a
> non-clustered unique index consisting of UserID+ModuleID since I know that
> this combination will always be unique? ... or is the fact that UserID a
> foreign key enough to cause SQL Server to optimize lookup based on UserID
> automatically without my having to explicitly create an index?
> For my trivial example, the decision of whether or not to use an index
> probably won't make a difference in performance. I'm just trying to
> understand the general concept of whether or not it's wise to create
> indexes based on foreign keys or if SQL automatically indexes these
> foreign keys for you [like it does for primary keys].
> Jules
>|||Creating an index on the foreign key UserID will for sure boost the
performance. as for your where statement I don't recommend to make a
composite index. index on UserID will be enough