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 size question

I have a SQL 2000 table with 16 million rows. I made a copy of it. The
two tables have the same number of rows, 16,152,139 to be exact.
The old table had a clustered, composite primary key across the first four
columns -- ssn (char(9)), Acct Number (varchar 20), sequence number
(varchar(20)), and transaction date (smalldatetime). The database size was
1,708,032 KB and the index was 8,520 KB.
To the new table, I added an ID field of type Int, and made it the primary
key nonclustered, also an identity field. The only other index is a
different date field in the table that's a clustered index (smalldatetime).
I also set the index fill factor to 80% from 90% in the old one.
The new table takes 2,406,592 KB; it's bigger because of the extra field.
BUT the index (as shown in the Task Pad summary) is 163,656 KB. *How could
two single-column indexes take 19 times the storage space as one 4-column
composite index?* I have run dbcc dbreindex on the table.
I also ran dbcc updateusage on the new table and got trivial differences.
Here is what SHOWCONTIG gives, if that helps. Anything else I can look at?
DBCC SHOWCONTIG scanning 'Transactions-Old' table...
Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 212443
- Extents Scanned.......................: 26688
- Extent Switches.......................: 26687
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.50%
- Avg. Bytes Free per Page................: 766.4
- Avg. Page Density (full)................: 90.53%
DBCC SHOWCONTIG scanning 'Transactions' table...
Table: 'Transactions' (711673583); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 280366
- Extents Scanned.......................: 35103
- Extent Switches.......................: 35102
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.11%
- Avg. Bytes Free per Page................: 1562.7
- Avg. Page Density (full)................: 80.69%
DBCC SHOWCONTIG scanning 'Transactions' table...
Table: 'Transactions' (711673583); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 19966
- Extents Scanned.......................: 2500
- Extent Switches.......................: 2499
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 0.60%
- Avg. Bytes Free per Page................: 6.2
- Avg. Page Density (full)................: 99.92%
I would post the DDL but I can't find the link to get the format...
Thanks.
David WalkerThis should be the DDL:
CREATE TABLE [dbo].[Transactions] (
[ID] [int] IDENTITY (1000, 1) NOT NULL ,
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] [int] NULL ,
[Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Transactions-Old] (
[SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[Settle_Date] [smalldatetime] NOT NULL ,
[SEQ_NUM] [int] NOT NULL ,
[FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL ,
[INT_TYPE] [smallint] NOT NULL ,
[Trade_Date] [smalldatetime] NULL ,
[QUANTITY] [decimal](16, 6) NOT NULL ,
[PRICE] [decimal](21, 8) NOT NULL ,
[PROCEEDS] [money] NULL ,
[FIN_INST_ID] [smallint] NOT NULL ,
[FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
(month,[Trade_Date])) ,
[Acct_Pre] AS (left([Acct_Number],3))
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
(
[SSN_TIN],
[ACCT_NUMBER],
[Settle_Date],
[SEQ_NUM]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
> I have a SQL 2000 table with 16 million rows. I made a copy of it.
> The two tables have the same number of rows, 16,152,139 to be exact.
> The old table had a clustered, composite primary key across the first
> four columns -- ssn (char(9)), Acct Number (varchar 20), sequence
> number (varchar(20)), and transaction date (smalldatetime). The
> database size was 1,708,032 KB and the index was 8,520 KB.
> To the new table, I added an ID field of type Int, and made it the
> primary key nonclustered, also an identity field. The only other
> index is a different date field in the table that's a clustered index
> (smalldatetime). I also set the index fill factor to 80% from 90% in
> the old one.
> The new table takes 2,406,592 KB; it's bigger because of the extra
> field. BUT the index (as shown in the Task Pad summary) is 163,656
> KB. *How could two single-column indexes take 19 times the storage
> space as one 4-column composite index?* I have run dbcc dbreindex on
> the table.
> I also ran dbcc updateusage on the new table and got trivial
> differences.
> Here is what SHOWCONTIG gives, if that helps. Anything else I can
> look at?
> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 212443
> - Extents Scanned.......................: 26688
> - Extent Switches.......................: 26687
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.50%
> - Avg. Bytes Free per Page................: 766.4
> - Avg. Page Density (full)................: 90.53%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 280366
> - Extents Scanned.......................: 35103
> - Extent Switches.......................: 35102
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.11%
> - Avg. Bytes Free per Page................: 1562.7
> - Avg. Page Density (full)................: 80.69%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 19966
> - Extents Scanned.......................: 2500
> - Extent Switches.......................: 2499
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.60%
> - Avg. Bytes Free per Page................: 6.2
> - Avg. Page Density (full)................: 99.92%
> I would post the DDL but I can't find the link to get the format...
> Thanks.
> David Walker
>|||Oops -- the new Transactions table actually has the ID (int) field
indexed twice, both clustered and non-clustered, with 2 different
indexes. Now how did that happen? :-)
I'll fix the indexes and check again.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:
> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>> I have a SQL 2000 table with 16 million rows. I made a copy of it.
>> The two tables have the same number of rows, 16,152,139 to be exact.
>> The old table had a clustered, composite primary key across the first
>> four columns -- ssn (char(9)), Acct Number (varchar 20), sequence
>> number (varchar(20)), and transaction date (smalldatetime). The
>> database size was 1,708,032 KB and the index was 8,520 KB.
>> To the new table, I added an ID field of type Int, and made it the
>> primary key nonclustered, also an identity field. The only other
>> index is a different date field in the table that's a clustered index
>> (smalldatetime). I also set the index fill factor to 80% from 90% in
>> the old one.
>> The new table takes 2,406,592 KB; it's bigger because of the extra
>> field. BUT the index (as shown in the Task Pad summary) is 163,656
>> KB. *How could two single-column indexes take 19 times the storage
>> space as one 4-column composite index?* I have run dbcc dbreindex on
>> the table.
>> I also ran dbcc updateusage on the new table and got trivial
>> differences.
>> Here is what SHOWCONTIG gives, if that helps. Anything else I can
>> look at?
>> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
>> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
>> TABLE level scan performed.
>> - Pages Scanned........................: 212443
>> - Extents Scanned.......................: 26688
>> - Extent Switches.......................: 26687
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.50%
>> - Avg. Bytes Free per Page................: 766.4
>> - Avg. Page Density (full)................: 90.53%
>> DBCC SHOWCONTIG scanning 'Transactions' table...
>> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
>> TABLE level scan performed.
>> - Pages Scanned........................: 280366
>> - Extents Scanned.......................: 35103
>> - Extent Switches.......................: 35102
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.11%
>> - Avg. Bytes Free per Page................: 1562.7
>> - Avg. Page Density (full)................: 80.69%
>> DBCC SHOWCONTIG scanning 'Transactions' table...
>> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
>> LEAF level scan performed.
>> - Pages Scanned........................: 19966
>> - Extents Scanned.......................: 2500
>> - Extent Switches.......................: 2499
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
>> - Logical Scan Fragmentation ..............: 0.01%
>> - Extent Scan Fragmentation ...............: 0.60%
>> - Avg. Bytes Free per Page................: 6.2
>> - Avg. Page Density (full)................: 99.92%
>> I would post the DDL but I can't find the link to get the format...
>> Thanks.
>> David Walker
>|||Um, I fixed the incorrect index to be a clustered index on the
Trade_Date column like it should have been, and the results are
essentially the same. I'm still confused.
Thanks for any insights.
David Walker
DW <None> wrote in news:ebCGMOjAEHA.3828@.TK2MSFTNGP10.phx.gbl:
> This should be the DDL:
> CREATE TABLE [dbo].[Transactions] (
> [ID] [int] IDENTITY (1000, 1) NOT NULL ,
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] [int] NULL ,
> [Acct_Pre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[Transactions-Old] (
> [SSN_TIN] [char] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL
> ,
> [ACCT_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [Settle_Date] [smalldatetime] NOT NULL ,
> [SEQ_NUM] [int] NOT NULL ,
> [FUND_ID] [varchar] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [TRANS_CODE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS
> NOT NULL ,
> [INT_TYPE] [smallint] NOT NULL ,
> [Trade_Date] [smalldatetime] NULL ,
> [QUANTITY] [decimal](16, 6) NOT NULL ,
> [PRICE] [decimal](21, 8) NOT NULL ,
> [PROCEEDS] [money] NULL ,
> [FIN_INST_ID] [smallint] NOT NULL ,
> [FUND_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL ,
> [TradeMonth] AS (datepart(year,[Trade_Date]) * 100 + datepart
> (month,[Trade_Date])) ,
> [Acct_Pre] AS (left([Acct_Number],3))
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions-Old] WITH NOCHECK ADD
> CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED
> (
> [SSN_TIN],
> [ACCT_NUMBER],
> [Settle_Date],
> [SEQ_NUM]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
> CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
> [Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[Transactions] ADD
> CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
> (
> [ID]
> ) ON [PRIMARY]
> GO
>
> DW <None> wrote in news:OupT2CjAEHA.3348@.TK2MSFTNGP11.phx.gbl:
>> I have a SQL 2000 table with 16 million rows. I made a copy of it.
>> The two tables have the same number of rows, 16,152,139 to be exact.
>> The old table had a clustered, composite primary key across the first
>> four columns -- ssn (char(9)), Acct Number (varchar 20), sequence
>> number (varchar(20)), and transaction date (smalldatetime). The
>> database size was 1,708,032 KB and the index was 8,520 KB.
>> To the new table, I added an ID field of type Int, and made it the
>> primary key nonclustered, also an identity field. The only other
>> index is a different date field in the table that's a clustered index
>> (smalldatetime). I also set the index fill factor to 80% from 90% in
>> the old one.
>> The new table takes 2,406,592 KB; it's bigger because of the extra
>> field. BUT the index (as shown in the Task Pad summary) is 163,656
>> KB. *How could two single-column indexes take 19 times the storage
>> space as one 4-column composite index?* I have run dbcc dbreindex on
>> the table.
>> I also ran dbcc updateusage on the new table and got trivial
>> differences.
>> Here is what SHOWCONTIG gives, if that helps. Anything else I can
>> look at?
>> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
>> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
>> TABLE level scan performed.
>> - Pages Scanned........................: 212443
>> - Extents Scanned.......................: 26688
>> - Extent Switches.......................: 26687
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.50%
>> - Avg. Bytes Free per Page................: 766.4
>> - Avg. Page Density (full)................: 90.53%
>> DBCC SHOWCONTIG scanning 'Transactions' table...
>> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
>> TABLE level scan performed.
>> - Pages Scanned........................: 280366
>> - Extents Scanned.......................: 35103
>> - Extent Switches.......................: 35102
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
>> - Logical Scan Fragmentation ..............: 0.00%
>> - Extent Scan Fragmentation ...............: 0.11%
>> - Avg. Bytes Free per Page................: 1562.7
>> - Avg. Page Density (full)................: 80.69%
>> DBCC SHOWCONTIG scanning 'Transactions' table...
>> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
>> LEAF level scan performed.
>> - Pages Scanned........................: 19966
>> - Extents Scanned.......................: 2500
>> - Extent Switches.......................: 2499
>> - Avg. Pages per Extent..................: 8.0
>> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
>> - Logical Scan Fragmentation ..............: 0.01%
>> - Extent Scan Fragmentation ...............: 0.60%
>> - Avg. Bytes Free per Page................: 6.2
>> - Avg. Page Density (full)................: 99.92%
>> I would post the DDL but I can't find the link to get the format...
>> Thanks.
>> David Walker
>|||David,
The table holds the leaf level pages of the clustered index. This means
that adding a clustered index to a heap (a table without clustered
index) will add only a few percent to the total size. A nonclustered
index is stored entirely separate from the table.
Maybe you hadn't noticed, but it is impossible to index 16 million rows
in 8 million bytes. (16,152,139 rows in 8,520 KB).
When you create a nonclustered index, it needs space for the indexed
column(s) plus space for the clustered key of each row. So the wider the
clustered index, the bigger any nonclustered index will be. When adding
a nonclustered index, all of a sudden the size of the clustered index
shows...
Hope this helps,
Gert-Jan
DW wrote:
> I have a SQL 2000 table with 16 million rows. I made a copy of it. The
> two tables have the same number of rows, 16,152,139 to be exact.
> The old table had a clustered, composite primary key across the first four
> columns -- ssn (char(9)), Acct Number (varchar 20), sequence number
> (varchar(20)), and transaction date (smalldatetime). The database size was
> 1,708,032 KB and the index was 8,520 KB.
> To the new table, I added an ID field of type Int, and made it the primary
> key nonclustered, also an identity field. The only other index is a
> different date field in the table that's a clustered index (smalldatetime).
> I also set the index fill factor to 80% from 90% in the old one.
> The new table takes 2,406,592 KB; it's bigger because of the extra field.
> BUT the index (as shown in the Task Pad summary) is 163,656 KB. *How could
> two single-column indexes take 19 times the storage space as one 4-column
> composite index?* I have run dbcc dbreindex on the table.
> I also ran dbcc updateusage on the new table and got trivial differences.
> Here is what SHOWCONTIG gives, if that helps. Anything else I can look at?
> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 212443
> - Extents Scanned.......................: 26688
> - Extent Switches.......................: 26687
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.50%
> - Avg. Bytes Free per Page................: 766.4
> - Avg. Page Density (full)................: 90.53%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 280366
> - Extents Scanned.......................: 35103
> - Extent Switches.......................: 35102
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.11%
> - Avg. Bytes Free per Page................: 1562.7
> - Avg. Page Density (full)................: 80.69%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 19966
> - Extents Scanned.......................: 2500
> - Extent Switches.......................: 2499
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.60%
> - Avg. Bytes Free per Page................: 6.2
> - Avg. Page Density (full)................: 99.92%
> I would post the DDL but I can't find the link to get the format...
> Thanks.
> David Walker
--
(Please reply only to the newsgroup)|||Hi David,
Thank you for using the newsgroup and it is my pleasure to help you with
you issue.
From you informaiton provided, you generate the SQL script from one table.
You noticed that the ID column in the following part:
CREATE CLUSTERED INDEX [IX_CL_Transactions_TradeDate] ON [dbo].
[Transactions]([ID]) WITH FILLFACTOR = 80 ON [PRIMARY]
GO
ALTER TABLE [dbo].[Transactions] ADD
CONSTRAINT [PK_Transactions_ID] PRIMARY KEY NONCLUSTERED
(
[ID]
) ON [PRIMARY]
GO
There is an clustered index and nonclustered index bulid on the table. You
wonder how it come, right?
To get the information on the table, you could run this statement in you
Query Analyzer:
exec sp_help transactions
OR
sp_helpindex transactions
From my experience, in the Enterprise Manger you have first create a
clustered index 'IX_CL_Transactions_TradeDate' on the column ID, then you
create a PRIMARY KEY constraint on this same column. So, finaly, you will
found that the 'sp_help transactions' or 'sp_helpindex transactions' will
show that the index on the column ID is non-clustered.
For maintenance purpose, you could run the DBCC INDEXDEFRAG
Hope this helps and if you still have questions, please feel free to post
your message here and I am glad to help.
Thanks.
Sincerely Yours
Baisong Wei
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. Thanks.sql

Index size question

I have a SQL 2000 table with 16 million rows. I made a copy of it. The
two tables have the same number of rows, 16,152,139 to be exact.
The old table had a clustered, composite primary key across the first four
columns -- ssn (char(9)), Acct Number (varchar 20), sequence number
(varchar(20)), and transaction date (smalldatetime). The database size was
1,708,032 KB and the index was 8,520 KB.
To the new table, I added an ID field of type Int, and made it the primary
key nonclustered, also an identity field. The only other index is a
different date field in the table that's a clustered index (smalldatetime).
I also set the index fill factor to 80% from 90% in the old one.
The new table takes 2,406,592 KB; it's bigger because of the extra field.
BUT the index (as shown in the Task Pad summary) is 163,656 KB. *How could
two single-column indexes take 19 times the storage space as one 4-column
composite index?* I have run dbcc dbreindex on the table.
I also ran dbcc updateusage on the new table and got trivial differences.
Here is what SHOWCONTIG gives, if that helps. Anything else I can look at?
DBCC SHOWCONTIG scanning 'Transactions-Old' table...
Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 212443
- Extents Scanned.......................: 26688
- Extent Switches.......................: 26687
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.51% [26556:26688
]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.50%
- Avg. Bytes Free per Page................: 766.4
- Avg. Page Density (full)................: 90.53%
DBCC SHOWCONTIG scanning 'Transactions' table...
Table: 'Transactions' (711673583); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned........................: 280366
- Extents Scanned.......................: 35103
- Extent Switches.......................: 35102
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.84% [35046:35103
]
- Logical Scan Fragmentation ..............: 0.00%
- Extent Scan Fragmentation ...............: 0.11%
- Avg. Bytes Free per Page................: 1562.7
- Avg. Page Density (full)................: 80.69%
DBCC SHOWCONTIG scanning 'Transactions' table...
Table: 'Transactions' (711673583); index ID: 2, database ID: 7
LEAF level scan performed.
- Pages Scanned........................: 19966
- Extents Scanned.......................: 2500
- Extent Switches.......................: 2499
- Avg. Pages per Extent..................: 8.0
- Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500]
- Logical Scan Fragmentation ..............: 0.01%
- Extent Scan Fragmentation ...............: 0.60%
- Avg. Bytes Free per Page................: 6.2
- Avg. Page Density (full)................: 99.92%
I would post the DDL but I can't find the link to get the format...
Thanks.
David WalkerDavid,
The table holds the leaf level pages of the clustered index. This means
that adding a clustered index to a heap (a table without clustered
index) will add only a few percent to the total size. A nonclustered
index is stored entirely separate from the table.
Maybe you hadn't noticed, but it is impossible to index 16 million rows
in 8 million bytes. (16,152,139 rows in 8,520 KB).
When you create a nonclustered index, it needs space for the indexed
column(s) plus space for the clustered key of each row. So the wider the
clustered index, the bigger any nonclustered index will be. When adding
a nonclustered index, all of a sudden the size of the clustered index
shows...
Hope this helps,
Gert-Jan
DW wrote:
> I have a SQL 2000 table with 16 million rows. I made a copy of it. The
> two tables have the same number of rows, 16,152,139 to be exact.
> The old table had a clustered, composite primary key across the first four
> columns -- ssn (char(9)), Acct Number (varchar 20), sequence number
> (varchar(20)), and transaction date (smalldatetime). The database size wa
s
> 1,708,032 KB and the index was 8,520 KB.
> To the new table, I added an ID field of type Int, and made it the primary
> key nonclustered, also an identity field. The only other index is a
> different date field in the table that's a clustered index (smalldatetime)
.
> I also set the index fill factor to 80% from 90% in the old one.
> The new table takes 2,406,592 KB; it's bigger because of the extra field.
> BUT the index (as shown in the Task Pad summary) is 163,656 KB. *How coul
d
> two single-column indexes take 19 times the storage space as one 4-column
> composite index?* I have run dbcc dbreindex on the table.
> I also ran dbcc updateusage on the new table and got trivial differences.
> Here is what SHOWCONTIG gives, if that helps. Anything else I can look at
?
> DBCC SHOWCONTIG scanning 'Transactions-Old' table...
> Table: 'Transactions-Old' (87671360); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 212443
> - Extents Scanned.......................: 26688
> - Extent Switches.......................: 26687
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.51% [26556:266
88]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.50%
> - Avg. Bytes Free per Page................: 766.4
> - Avg. Page Density (full)................: 90.53%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 1, database ID: 7
> TABLE level scan performed.
> - Pages Scanned........................: 280366
> - Extents Scanned.......................: 35103
> - Extent Switches.......................: 35102
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [35046:351
03]
> - Logical Scan Fragmentation ..............: 0.00%
> - Extent Scan Fragmentation ...............: 0.11%
> - Avg. Bytes Free per Page................: 1562.7
> - Avg. Page Density (full)................: 80.69%
> DBCC SHOWCONTIG scanning 'Transactions' table...
> Table: 'Transactions' (711673583); index ID: 2, database ID: 7
> LEAF level scan performed.
> - Pages Scanned........................: 19966
> - Extents Scanned.......................: 2500
> - Extent Switches.......................: 2499
> - Avg. Pages per Extent..................: 8.0
> - Scan Density [Best Count:Actual Count]......: 99.84% [2496:2500
]
> - Logical Scan Fragmentation ..............: 0.01%
> - Extent Scan Fragmentation ...............: 0.60%
> - Avg. Bytes Free per Page................: 6.2
> - Avg. Page Density (full)................: 99.92%
> I would post the DDL but I can't find the link to get the format...
> Thanks.
> David Walker
(Please reply only to the newsgroup)

Index question

Hello,
I have a telephone log table that tracks the following: (phone number, time
of day, duration of call). This table that is populated with over 1/2 a
million telephone numbers per day. My question is what is the appropriate
way of indexing a table with this volume of inserts?
Thanks in advance,
sck10
Something like that usually does well with a clustered index on the datetime
but it depends on how you need to access the data. What are your typical
queries like?
Andrew J. Kelly SQL MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uTER80kzEHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a telephone log table that tracks the following: (phone number,
> time
> of day, duration of call). This table that is populated with over 1/2 a
> million telephone numbers per day. My question is what is the appropriate
> way of indexing a table with this volume of inserts?
> --
> Thanks in advance,
> sck10
>
|||Hi sck10,
Addtionally to MVP Andrew J. Kelly's suggestions, you would also make the
decision based on Index Tuning Wizard in Query Analyzer. Here is some
guidelines for you
Index Tuning Wizard for Microsoft SQL Server 2000
http://msdn.microsoft.com/library/de...us/dnsql2k/htm
l/itwforsql.asp
INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/kb/311826
Support WebCast: Effective Indexing and Statistics with SQL 2000
http://support.microsoft.com/kb/325024
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||It depends on whether this data is being inserted in real time
or if the data is loaded from provider files, and also on whether
the data is constantly being queried, or if it is only accessed for
occasional reports and bill generation. If it's bulk loaded
from provider files, or if it's processed only for reports and
bills, you have more flexibility, and can index it in a way
that's useful to what you do with the data. If it's loaded
in real time, but not queried in real time, it could even be
appropriate to load it into an unindexed table, or into
a table with an artificial clustered key like a column with
the identity property. If the hardware cannot spit
out the same call twice, you don't need a natural key while
the data is loading.
Can you say more about where this data comes from,
what kinds of queries you run against it, and whether
there are typically few or many connections querying
the data at once?
Steve Kass
Drew University
sck10 wrote:

>Hello,
>I have a telephone log table that tracks the following: (phone number, time
>of day, duration of call). This table that is populated with over 1/2 a
>million telephone numbers per day. My question is what is the appropriate
>way of indexing a table with this volume of inserts?
>
>

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 telephone log table that tracks the following: (phone number, time
of day, duration of call). This table that is populated with over 1/2 a
million telephone numbers per day. My question is what is the appropriate
way of indexing a table with this volume of inserts?
Thanks in advance,
sck10Something like that usually does well with a clustered index on the datetime
but it depends on how you need to access the data. What are your typical
queries like?
Andrew J. Kelly SQL MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uTER80kzEHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a telephone log table that tracks the following: (phone number,
> time
> of day, duration of call). This table that is populated with over 1/2 a
> million telephone numbers per day. My question is what is the appropriate
> way of indexing a table with this volume of inserts?
> --
> Thanks in advance,
> sck10
>|||Hi sck10,
Addtionally to MVP Andrew J. Kelly's suggestions, you would also make the
decision based on Index Tuning Wizard in Query Analyzer. Here is some
guidelines for you
Index Tuning Wizard for Microsoft SQL Server 2000
http://msdn.microsoft.com/library/d...-us/dnsql2k/htm
l/itwforsql.asp
INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/kb/311826
Support WebCast: Effective Indexing and Statistics with SQL 2000
http://support.microsoft.com/kb/325024
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It depends on whether this data is being inserted in real time
or if the data is loaded from provider files, and also on whether
the data is constantly being queried, or if it is only accessed for
occasional reports and bill generation. If it's bulk loaded
from provider files, or if it's processed only for reports and
bills, you have more flexibility, and can index it in a way
that's useful to what you do with the data. If it's loaded
in real time, but not queried in real time, it could even be
appropriate to load it into an unindexed table, or into
a table with an artificial clustered key like a column with
the identity property. If the hardware cannot spit
out the same call twice, you don't need a natural key while
the data is loading.
Can you say more about where this data comes from,
what kinds of queries you run against it, and whether
there are typically few or many connections querying
the data at once?
Steve Kass
Drew University
sck10 wrote:

>Hello,
>I have a telephone log table that tracks the following: (phone number, time
>of day, duration of call). This table that is populated with over 1/2 a
>million telephone numbers per day. My question is what is the appropriate
>way of indexing a table with this volume of inserts?
>
>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

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 telephone log table that tracks the following: (phone number, time
of day, duration of call). This table that is populated with over 1/2 a
million telephone numbers per day. My question is what is the appropriate
way of indexing a table with this volume of inserts?
--
Thanks in advance,
sck10Something like that usually does well with a clustered index on the datetime
but it depends on how you need to access the data. What are your typical
queries like?
--
Andrew J. Kelly SQL MVP
"sck10" <sck10@.online.nospam> wrote in message
news:uTER80kzEHA.3656@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a telephone log table that tracks the following: (phone number,
> time
> of day, duration of call). This table that is populated with over 1/2 a
> million telephone numbers per day. My question is what is the appropriate
> way of indexing a table with this volume of inserts?
> --
> Thanks in advance,
> sck10
>|||Hi sck10,
Addtionally to MVP Andrew J. Kelly's suggestions, you would also make the
decision based on Index Tuning Wizard in Query Analyzer. Here is some
guidelines for you
Index Tuning Wizard for Microsoft SQL Server 2000
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/htm
l/itwforsql.asp
INF: Index Tuning Wizard Best Practices
http://support.microsoft.com/kb/311826
Support WebCast: Effective Indexing and Statistics with SQL 2000
http://support.microsoft.com/kb/325024
Hope this helps and if you have any questions or concerns, don't hesitate
to let me know. We are always here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||It depends on whether this data is being inserted in real time
or if the data is loaded from provider files, and also on whether
the data is constantly being queried, or if it is only accessed for
occasional reports and bill generation. If it's bulk loaded
from provider files, or if it's processed only for reports and
bills, you have more flexibility, and can index it in a way
that's useful to what you do with the data. If it's loaded
in real time, but not queried in real time, it could even be
appropriate to load it into an unindexed table, or into
a table with an artificial clustered key like a column with
the identity property. If the hardware cannot spit
out the same call twice, you don't need a natural key while
the data is loading.
Can you say more about where this data comes from,
what kinds of queries you run against it, and whether
there are typically few or many connections querying
the data at once?
Steve Kass
Drew University
sck10 wrote:
>Hello,
>I have a telephone log table that tracks the following: (phone number, time
>of day, duration of call). This table that is populated with over 1/2 a
>million telephone numbers per day. My question is what is the appropriate
>way of indexing a table with this volume of inserts?
>
>

Index problem

I have a table, which contains address information,
have non-clustered index on phone no, search on phone
number was very quick, now users requested that search on
address be possible too, I created non-clustered index on
address which speeds up search by address but search by
phone number is extremely slow, query plan shows that
optimizer is using this new index instead of index on
phone number even during phone search, I updated stats
with full scan and still same result.
Dbcc show_statistics shows better selectivity for index on
address than phone number.
It is a very complicated dynamic query which I cannot
force optimizer to use specific index.
Any help will be appreciatedI would play with "set statistics IO on" to see your logical reads.
How are they asking for the address and phone number? (SQL syntax)
When they only ask for phone number how do they ask this? For example, if I
said
select phonenumber from table where street = 'main'
SQL would not use your nonclustered index on phone(unless you had a
clustered index on street)
You might consider covering non-clustered indexes.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sam" <sam.moayedi@.moneymanagement.org> wrote in message
news:044b01c39350$a27e2a70$a401280a@.phx.gbl...
> I have a table, which contains address information,
> have non-clustered index on phone no, search on phone
> number was very quick, now users requested that search on
> address be possible too, I created non-clustered index on
> address which speeds up search by address but search by
> phone number is extremely slow, query plan shows that
> optimizer is using this new index instead of index on
> phone number even during phone search, I updated stats
> with full scan and still same result.
> Dbcc show_statistics shows better selectivity for index on
> address than phone number.
> It is a very complicated dynamic query which I cannot
> force optimizer to use specific index.
> Any help will be appreciated
>|||The sp goes something like this
select * from table where phoneno='xxxxxxxxx'
It is more complicated than|||Then your NC index on phone number definitely won't help, it would make no
sense to go out and find phone numbers only to redirect back to the table,
did you say you had a clustered index? Again, I would play with my indexes
using "set statistics IO on"
If you were saying
select phone_no from table where phone_no = 'blah'
Then your NCI would act as a covering index and SQL would naturally go to
the index and not touch the table.
HTH
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Sam" <sam.moayedi@.moneymanagement.org> wrote in message
news:05f701c3935f$d57f1ec0$a401280a@.phx.gbl...
> The sp goes something like this
> select * from table where phoneno='xxxxxxxxx'
> It is more complicated than|||Sam
Have you checked for fragmentation on the phone number
index? Although it mostly tends to affect clustered
indexes, I have seen very similar problems with fragmented
non-clustered indexes.
Regards
Johnsql

Index Lookups

I have queried sys.dm_db_index_usage_stats and found an index that has a high
number of IndexLookups vs. IndexSeeks. This index is on a table that is
refernced in over 1200 stored procedures. The index is comprised of a single
column.
I could examine each of these 1200 stored procedures individually to see what
additional columns might be added or included in the index to reduce the
lookups, or run Profiler and include Event Performance > ShowPlan Text and
search for the table or index in ShowPlan Text, but neither of these options
thrill me.
Any other ideas on how one might go about determining the additional columns
needed to cover these lookups?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1cbrichards via SQLMonster.com,
That index could be the clustered index for that table. I do not think you
want to add more columns to the key of the clustered index. The look_up is
from a nonclustered to a clustered, so you will have to identify first the
nonclustered indexes participating in the look_up and see if you can add more
columns, either to the key or to the leaf nodes using the new feature
"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
AMB
"cbrichards via SQLMonster.com" wrote:
> I have queried sys.dm_db_index_usage_stats and found an index that has a high
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a single
> column.
> I could examine each of these 1200 stored procedures individually to see what
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these options
> thrill me.
> Any other ideas on how one might go about determining the additional columns
> needed to cover these lookups?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>|||Perhaps there is anything in the missing_indexes DMV's?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message news:6ed5f925db6ae@.uwe...
>I have queried sys.dm_db_index_usage_stats and found an index that has a high
> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> refernced in over 1200 stored procedures. The index is comprised of a single
> column.
> I could examine each of these 1200 stored procedures individually to see what
> additional columns might be added or included in the index to reduce the
> lookups, or run Profiler and include Event Performance > ShowPlan Text and
> search for the table or index in ShowPlan Text, but neither of these options
> thrill me.
> Any other ideas on how one might go about determining the additional columns
> needed to cover these lookups?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>|||Thanks Alejandro. You are correct...this is a clustered index.
So I believe you have clarified a misunderstanding on my part. I thought the
User_Lookups value returned from sys.dm_db_index_usage_stats was the number
of times the index itself caused a lookup, but from your statement, it
appears the User_Lookups value is the number of times the index is used by
other indexes to lookup information. Is that a correct understanding?
Alejandro Mesa wrote:
>cbrichards via SQLMonster.com,
>That index could be the clustered index for that table. I do not think you
>want to add more columns to the key of the clustered index. The look_up is
>from a nonclustered to a clustered, so you will have to identify first the
>nonclustered indexes participating in the look_up and see if you can add more
>columns, either to the key or to the leaf nodes using the new feature
>"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
>AMB
>> I have queried sys.dm_db_index_usage_stats and found an index that has a high
>> number of IndexLookups vs. IndexSeeks. This index is on a table that is
>[quoted text clipped - 9 lines]
>> Any other ideas on how one might go about determining the additional columns
>> needed to cover these lookups?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||It may just be a misunderstanding in the way indexes are organized. Remember
that the clustered index IS the data. So anytime you are going to the actual
data pages, you are using the clustered index.
It's not clear what you mean by 'the index caused a lookup'.
The clustered index keys are used as the bookmark values in nonclustered
indexes. So anytime a nonclustered index is used to find the data you need,
SQL Server will find the corresponding clustered key in the nonclustered
index,and then use it to find the row in the clustered index (the data).
So user_lookups is the total number of times the index was used for a
lookup. And for a clustered index, the number will probably be bigger than
for any other index, because you'll have all the queries that use the
clustered index key in the user query, plus all the queries that use a
nonclustered index to get to the data.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:6ed74f8c473ce@.uwe...
> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought
> the
> User_Lookups value returned from sys.dm_db_index_usage_stats was the
> number
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
>>cbrichards via SQLMonster.com,
>>That index could be the clustered index for that table. I do not think you
>>want to add more columns to the key of the clustered index. The look_up is
>>from a nonclustered to a clustered, so you will have to identify first the
>>nonclustered indexes participating in the look_up and see if you can add
>>more
>>columns, either to the key or to the leaf nodes using the new feature
>>"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
>>AMB
>> I have queried sys.dm_db_index_usage_stats and found an index that has a
>> high
>> number of IndexLookups vs. IndexSeeks. This index is on a table that is
>>[quoted text clipped - 9 lines]
>> Any other ideas on how one might go about determining the additional
>> columns
>> needed to cover these lookups?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>|||cbrichards via SQLMonster.com,
That is my understanding also, but I have not found anything in BOL to
support that. You can do some test in your local computer (personal - NOT
PRODUCTION). Restart the db engine, and execute this:
use northwind
go
-- index seek on PK_Orders (clustered)
select *
from dbo.orders
where orderid = 10250
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where orderdate = '19980101'
select *
from sys.dm_db_index_usage_stats
select *
from dbo.orders
where employeeid = 5 and customerid= cast(N'WARTH' as nchar(5))
select *
from sys.dm_db_index_usage_stats
go
AMB
"cbrichards via SQLMonster.com" wrote:
> Thanks Alejandro. You are correct...this is a clustered index.
> So I believe you have clarified a misunderstanding on my part. I thought the
> User_Lookups value returned from sys.dm_db_index_usage_stats was the number
> of times the index itself caused a lookup, but from your statement, it
> appears the User_Lookups value is the number of times the index is used by
> other indexes to lookup information. Is that a correct understanding?
> Alejandro Mesa wrote:
> >cbrichards via SQLMonster.com,
> >
> >That index could be the clustered index for that table. I do not think you
> >want to add more columns to the key of the clustered index. The look_up is
> >from a nonclustered to a clustered, so you will have to identify first the
> >nonclustered indexes participating in the look_up and see if you can add more
> >columns, either to the key or to the leaf nodes using the new feature
> >"INCLUDE (COLUMN_NAME,...)" to make the nonclustered index a covering one.
> >
> >AMB
> >
> >> I have queried sys.dm_db_index_usage_stats and found an index that has a high
> >> number of IndexLookups vs. IndexSeeks. This index is on a table that is
> >[quoted text clipped - 9 lines]
> >> Any other ideas on how one might go about determining the additional columns
> >> needed to cover these lookups?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1
>

Index Defragmentation

None of the system tables in our databases have a significant number of
pages, and I can't see there would be many scans happening, but in the
interests of completeness should system tables be defragmented if they show
high fragmentation?Hi Ben
I would say yes, they are no different to any other table in that respect
and should be optimized in a similar way. Unfortunately you can't run DBCC
INDEXDEFRAG or DBCC DBREINDEX on system tables, so you will have to hope that
the system has it's own way of keeping these tables in tune.
John
"BenUK" wrote:
> None of the system tables in our databases have a significant number of
> pages, and I can't see there would be many scans happening, but in the
> interests of completeness should system tables be defragmented if they show
> high fragmentation?
>

Index Defragmentation

None of the system tables in our databases have a significant number of
pages, and I can't see there would be many scans happening, but in the
interests of completeness should system tables be defragmented if they show
high fragmentation?Hi Ben
I would say yes, they are no different to any other table in that respect
and should be optimized in a similar way. Unfortunately you can't run DBCC
INDEXDEFRAG or DBCC DBREINDEX on system tables, so you will have to hope tha
t
the system has it's own way of keeping these tables in tune.
John
"BenUK" wrote:

> None of the system tables in our databases have a significant number of
> pages, and I can't see there would be many scans happening, but in the
> interests of completeness should system tables be defragmented if they sho
w
> high fragmentation?
>

Index count

How can I find the number of indexes created on a user database (Only
clustered and Non-clustered indexes in user tables - not system).
Thanks."DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
>
Select count(*) from sysindexes
where id in (select id from sysobjects where type = 'U')
and indid <> 255
255 is Text column.|||Set objSqlServer to your sql connection
Sub ListIndexes(strDBName)
WSCript.Echo "Database:" & Trim(strDBName)
Set oDatabase = objSqlServer.Databases(Trim(strDBName))
For Each Table In oDatabase.Tables
If NOT Table.SystemObject Then
WSCript.Echo Table.Name & " (" & Table.Indexes.Count & " indexes)"
<--here is the actual count property
For Each Index in Table.Indexes
If NOT Index.StatisticsIndex Then
WSCript.Echo vbTab & Index.Name & " (Stat: " & Index.StatisticsIndex &
")"
For Each Column in Index.ListIndexedColumns( )
WSCript.Echo vbTab & vbTab & "[" & Column.Name & "]"
Next
WSCript.Echo vbSpace
End If
Next
End IF
Next
End Sub
Or... more to the point
Set objSqlServer = YourSQLServerConnection
Set oDatabase = objSqlServer.Databases(TheDatabase)
For Each Table In oDatabase.Tables
If NOT Table.SystemObject Then
WSCript.StdOout.WriteLine Table.Indexes.Count
End If
Next
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> How can I find the number of indexes created on a user database (Only
> clustered and Non-clustered indexes in user tables - not system).
> Thanks.|||Not quite, in SQL 2000 statistics also take up an indid value in
sysindexes, so you would need to eliminate them also.
I think you would need to use INDEXPROPERTY to weed them out:
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
HTH
Kalen Delaney
www.solidqualitylearning.com
"rkusenet" <rkusenet@.yahoo.com> wrote in message
news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> "DXC" <DXC@.discussions.microsoft.com> wrote in message
> news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
>> How can I find the number of indexes created on a user database (Only
>> clustered and Non-clustered indexes in user tables - not system).
> Select count(*) from sysindexes
> where id in (select id from sysobjects where type = 'U')
> and indid <> 255
> 255 is Text column.
>|||So, is following the correct syntax '
SELECT COUNT(*) FROM sysindexes
WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
name, 'IsHypothetical') = 0
Thanks.
"Kalen Delaney" wrote:
> Not quite, in SQL 2000 statistics also take up an indid value in
> sysindexes, so you would need to eliminate them also.
> I think you would need to use INDEXPROPERTY to weed them out:
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY (ID,
> name, 'IsHypothetical') = 0
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "rkusenet" <rkusenet@.yahoo.com> wrote in message
> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
> >
> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
> > news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
> >> How can I find the number of indexes created on a user database (Only
> >> clustered and Non-clustered indexes in user tables - not system).
> >>
> >
> > Select count(*) from sysindexes
> > where id in (select id from sysobjects where type = 'U')
> > and indid <> 255
> >
> > 255 is Text column.
> >
> >
>
>|||what does 'IsHypothetical' mean?|||Hypothetical indexes hold column level statistics. They are created by SQL
Server and used internally. They cannot be used directly as a data access
path.
--
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>|||Did you try it? If you have a syntax error you will be told so.
You will also need the other conditions in the WHERE clause, not just these
two.
HTH
Kalen Delaney
"DXC" <DXC@.discussions.microsoft.com> wrote in message
news:700341BA-4C8D-4BE5-9970-DFC1151E6DBB@.microsoft.com...
> So, is following the correct syntax '
> SELECT COUNT(*) FROM sysindexes
> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
> (ID,
> name, 'IsHypothetical') = 0
> Thanks.
> "Kalen Delaney" wrote:
>> Not quite, in SQL 2000 statistics also take up an indid value in
>> sysindexes, so you would need to eliminate them also.
>> I think you would need to use INDEXPROPERTY to weed them out:
>> WHERE INDEXPROPERTY ( ID, name, 'IsStatistics') = 0 and INDEXPROPERTY
>> (ID,
>> name, 'IsHypothetical') = 0
>> HTH
>> Kalen Delaney
>> www.solidqualitylearning.com
>>
>> "rkusenet" <rkusenet@.yahoo.com> wrote in message
>> news:4321d67f$0$91790$892e7fe2@.authen.white.readfreenews.net...
>> >
>> > "DXC" <DXC@.discussions.microsoft.com> wrote in message
>> > news:5F002D25-DB29-49A3-9F35-36E782D1882B@.microsoft.com...
>> >> How can I find the number of indexes created on a user database (Only
>> >> clustered and Non-clustered indexes in user tables - not system).
>> >>
>> >
>> > Select count(*) from sysindexes
>> > where id in (select id from sysobjects where type = 'U')
>> > and indid <> 255
>> >
>> > 255 is Text column.
>> >
>> >
>>
>>
>|||An index created by the Index Tuning Wizard; these are usually removed when
the IDW is done, but sometimes they manage to stick around.
HTH
Kalen Delaney
<ford_desperado@.yahoo.com> wrote in message
news:1126296886.681664.90670@.g47g2000cwa.googlegroups.com...
> what does 'IsHypothetical' mean?
>

index count

Is there a way to find out the total number of indexes
(clustered and non-clustered) on a given database, either
through the use of QA or EM? I'm currently running
sp_helpindex against all tables in my database and then
manually counting each index.
Thanks.SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
Indexes with indid 0 are heaps (tables without a clustered index), indid 1
are clustered indexes, indid 2-254 are non clustered indexes and indid 255
are used for text/ntext/images columns
--
Jacco Schalkwijk
SQL Server MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.|||Also not that this will include statistics and hypothetical indexes. These can be filtered out using
INDEXPEROPERTY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote in message
news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> SELECT COUNT(*) FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> Indexes with indid 0 are heaps (tables without a clustered index), indid 1 are clustered indexes,
> indid 2-254 are non clustered indexes and indid 255 are used for text/ntext/images columns
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database, either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and then
>> manually counting each index.
>> Thanks.
>|||select count(*) from (SELECT sysobjects.name, sysobjects.id,
sysindexkeys.indid
FROM sysindexes INNER JOIN
sysobjects ON sysindexes.id = sysobjects.id INNER JOIN
sysindexkeys ON sysindexes.id = sysindexkeys.id
GROUP BY sysobjects.name, sysobjects.id, sysindexkeys.indid) drv
ll give the current databases index count
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.|||Oops, forgot about those.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
--
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Also not that this will include statistics and hypothetical indexes. These
> can be filtered out using INDEXPEROPERTY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database, either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and then
>> manually counting each index.
>> Thanks.
>>
>|||Thanks guys, that was really helpful. Any ideas on how to
apply the same filter in SQL6.5, since the INDEXPROPERTY
function isn't available in 6.5?
Thanks again.
>--Original Message--
>Oops, forgot about those.
>SELECT COUNT(*) FROM sysindexes
>WHERE indid BETWEEN 1 AND 254
>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> Also not that this will include statistics and
hypothetical indexes. These
>> can be filtered out using INDEXPEROPERTY.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jacco Schalkwijk"
<jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:%
23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a
clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered
indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in
message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database,
either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and
then
>> manually counting each index.
>> Thanks.
>>
>>
>
>.
>|||I forget how a text page is represented in 6.5 sysindexes. But... you won't
have hypothetical indexes and I don't think stats show up as a row in
sysindexes. It's been sooo long, I just don't remember.
But I think you'll be safe simply looking at user tables with an indid
between 1-255 (or 254 if a row is there for text/image)
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> >--Original Message--
> >Oops, forgot about those.
> >
> >SELECT COUNT(*) FROM sysindexes
> >WHERE indid BETWEEN 1 AND 254
> >AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
> >AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
> >
> >--
> >Jacco Schalkwijk
> >SQL Server MVP
> >
> >
> >"Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> >message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> >> Also not that this will include statistics and
> hypothetical indexes. These
> >> can be filtered out using INDEXPEROPERTY.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >>
> >>
> >> "Jacco Schalkwijk"
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> >> wrote in message news:%
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> >> SELECT COUNT(*) FROM sysindexes
> >> WHERE indid BETWEEN 1 AND 254
> >>
> >> Indexes with indid 0 are heaps (tables without a
> clustered index), indid
> >> 1 are clustered indexes, indid 2-254 are non clustered
> indexes and indid
> >> 255 are used for text/ntext/images columns
> >>
> >> --
> >> Jacco Schalkwijk
> >> SQL Server MVP
> >>
> >>
> >> "Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
> >> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> >> Is there a way to find out the total number of indexes
> >> (clustered and non-clustered) on a given database,
> either
> >> through the use of QA or EM? I'm currently running
> >> sp_helpindex against all tables in my database and
> then
> >> manually counting each index.
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >
> >
> >.
> >|||SQL 6.5 didn't have column stats (only those associated with indexes) and it
didn't have hypothetical indexes, so you don't need to check for either of
these conditions.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
>>--Original Message--
>>Oops, forgot about those.
>>SELECT COUNT(*) FROM sysindexes
>>WHERE indid BETWEEN 1 AND 254
>>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>>--
>>Jacco Schalkwijk
>>SQL Server MVP
>>
>>"Tibor Karaszi"
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
>>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
>> Also not that this will include statistics and
> hypothetical indexes. These
>> can be filtered out using INDEXPEROPERTY.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Jacco Schalkwijk"
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:%
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>> SELECT COUNT(*) FROM sysindexes
>> WHERE indid BETWEEN 1 AND 254
>> Indexes with indid 0 are heaps (tables without a
> clustered index), indid
>> 1 are clustered indexes, indid 2-254 are non clustered
> indexes and indid
>> 255 are used for text/ntext/images columns
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "Rob" <anonymous@.discussions.microsoft.com> wrote in
> message
>> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>> Is there a way to find out the total number of indexes
>> (clustered and non-clustered) on a given database,
> either
>> through the use of QA or EM? I'm currently running
>> sp_helpindex against all tables in my database and
> then
>> manually counting each index.
>> Thanks.
>>
>>
>>
>>.

index count

Is there a way to find out the total number of indexes
(clustered and non-clustered) on a given database, either
through the use of QA or EM? I'm currently running
sp_helpindex against all tables in my database and then
manually counting each index.
Thanks.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
Indexes with indid 0 are heaps (tables without a clustered index), indid 1
are clustered indexes, indid 2-254 are non clustered indexes and indid 255
are used for text/ntext/images columns
Jacco Schalkwijk
SQL Server MVP
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.
|||Also not that this will include statistics and hypothetical indexes. These can be filtered out using
INDEXPEROPERTY.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid > wrote in message
news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> SELECT COUNT(*) FROM sysindexes
> WHERE indid BETWEEN 1 AND 254
> Indexes with indid 0 are heaps (tables without a clustered index), indid 1 are clustered indexes,
> indid 2-254 are non clustered indexes and indid 255 are used for text/ntext/images columns
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Rob" <anonymous@.discussions.microsoft.com> wrote in message
> news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
>
|||select count(*) from (SELECT sysobjects.name, sysobjects.id,
sysindexkeys.indid
FROM sysindexes INNER JOIN
sysobjects ON sysindexes.id = sysobjects.id INNER JOIN
sysindexkeys ON sysindexes.id = sysindexkeys.id
GROUP BY sysobjects.name, sysobjects.id, sysindexkeys.indid) drv
ll give the current databases index count
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:07f501c4ad40$fe7d1920$a501280a@.phx.gbl...
> Is there a way to find out the total number of indexes
> (clustered and non-clustered) on a given database, either
> through the use of QA or EM? I'm currently running
> sp_helpindex against all tables in my database and then
> manually counting each index.
> Thanks.
|||Oops, forgot about those.
SELECT COUNT(*) FROM sysindexes
WHERE indid BETWEEN 1 AND 254
AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
Jacco Schalkwijk
SQL Server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Also not that this will include statistics and hypothetical indexes. These
> can be filtered out using INDEXPEROPERTY.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> wrote in message news:%23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
>
|||Thanks guys, that was really helpful. Any ideas on how to
apply the same filter in SQL6.5, since the INDEXPROPERTY
function isn't available in 6.5?
Thanks again.

>--Original Message--
>Oops, forgot about those.
>SELECT COUNT(*) FROM sysindexes
>WHERE indid BETWEEN 1 AND 254
>AND INDEXPROPERTY (ID, name, 'IsHypothetical') = 0
>AND INDEXPROPERTY (ID, name, 'IsStatistics') = 0
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in[vbcol=seagreen]
>message news:OcFKtLUrEHA.3748@.TK2MSFTNGP09.phx.gbl...
hypothetical indexes. These[vbcol=seagreen]
<jacco.please.reply@.to.newsgroups.mvps.org.invalid >[vbcol=seagreen]
23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
clustered index), indid[vbcol=seagreen]
indexes and indid[vbcol=seagreen]
message[vbcol=seagreen]
either[vbcol=seagreen]
then
>
>.
>
|||I forget how a text page is represented in 6.5 sysindexes. But... you won't
have hypothetical indexes and I don't think stats show up as a row in
sysindexes. It's been sooo long, I just don't remember.
But I think you'll be safe simply looking at user tables with an indid
between 1-255 (or 254 if a row is there for text/image)
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> hypothetical indexes. These
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> clustered index), indid
> indexes and indid
> message
> either
> then
|||SQL 6.5 didn't have column stats (only those associated with indexes) and it
didn't have hypothetical indexes, so you don't need to check for either of
these conditions.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rob" <anonymous@.discussions.microsoft.com> wrote in message
news:171201c4ad4c$1569c6f0$a301280a@.phx.gbl...[vbcol=seagreen]
> Thanks guys, that was really helpful. Any ideas on how to
> apply the same filter in SQL6.5, since the INDEXPROPERTY
> function isn't available in 6.5?
> Thanks again.
> <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> hypothetical indexes. These
> <jacco.please.reply@.to.newsgroups.mvps.org.invalid >
> 23heAEIUrEHA.1272@.TK2MSFTNGP09.phx.gbl...
> clustered index), indid
> indexes and indid
> message
> either
> then