find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> level
> each
>|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
>|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the leve
l
> that contains the data rows (clustered index) ? Also how many pages at eac
h
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.

> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
(Please reply only to the newsgroup)

find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > Take the authors table as an example in the pubs database...
> >
> > How can I find how many levels within the B-Tree from the root to the
> level
> > that contains the data rows (clustered index) ? Also how many pages at
> each
> > level within that B-Tree ?
> >
> > Will i have 2 B-Trees now, one for the clustered index and one for the
> > non-clustered ? Using SQL 2000
> >
> >
>|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > Have a look into DBCC SHOWCONTIG command in books online.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> > > Take the authors table as an example in the pubs database...
> > >
> > > How can I find how many levels within the B-Tree from the root to the
> > level
> > > that contains the data rows (clustered index) ? Also how many pages at
> > each
> > > level within that B-Tree ?
> > >
> > > Will i have 2 B-Trees now, one for the clustered index and one for the
> > > non-clustered ? Using SQL 2000
> > >
> > >
> >
> >
>|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the level
> that contains the data rows (clustered index) ? Also how many pages at each
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
--
(Please reply only to the newsgroup)

find no.of pages and levels deep

Take the authors table as an example in the pubs database...
How can I find how many levels within the B-Tree from the root to the level
that contains the data rows (clustered index) ? Also how many pages at each
level within that B-Tree ?
Will i have 2 B-Trees now, one for the clustered index and one for the
non-clustered ? Using SQL 2000
Hi,
Have a look into DBCC SHOWCONTIG command in books online.
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the
level
> that contains the data rows (clustered index) ? Also how many pages at
each
> level within that B-Tree ?
> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
>
|||Can your provide an example ? I did look at it but cant find any
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Have a look into DBCC SHOWCONTIG command in books online.
> Thanks
> Hari
> MCDBA
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:udoCUF7bEHA.2972@.TK2MSFTNGP12.phx.gbl...
> level
> each
>
|||Hi,
DBCC SHOWCONTIG will not give the exact infrmation you require. But it shows
information about the data and Index pages for a table.
THis gives you pages, extends for a table/index.
Infor from books online:-
Statistic Description
ObjectName Name of the table or view processed.
ObjectId ID of the object name.
IndexName Name of the index processed. IndexName is NULL for a heap.
IndexId ID of the index. IndexId is 0 for a heap.
Level Level of the index. Level 0 is the leaf (or data) level of the
index. The level number increases moving up the tree toward the index root.
Level is 0 for a heap.
Pages Number of pages comprising that level of the index or entire
heap.
Rows Number of data or index records at that level of the index. For a
heap, this is the number of data records in the entire heap.
MinimumRecordSize Minimum record size in that level of the index or
entire heap.
MaximumRecordSize Maximum record size in that level of the index or
entire heap.
AverageRecordSize Average record size in that level of the index or
entire heap.
ForwardedRecords Number of forwarded records in that level of the
index or entire heap.
Extents Number of extents in that level of the index or entire heap.
Usage :
DBCC SHOWCONTIG(tablene,[index_name])
Thanks
Hari
MCDBA
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:#2Pk8g7bEHA.2660@.tk2msftngp13.phx.gbl...
> Can your provide an example ? I did look at it but cant find any
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eiGzQM7bEHA.2544@.TK2MSFTNGP10.phx.gbl...
>
|||Hassan wrote:
> Take the authors table as an example in the pubs database...
> How can I find how many levels within the B-Tree from the root to the level
> that contains the data rows (clustered index) ? Also how many pages at each
> level within that B-Tree ?
With the INDEXPROPERTY function. The data rows are always at the leaf
level of the clustered index.

> Will i have 2 B-Trees now, one for the clustered index and one for the
> non-clustered ? Using SQL 2000
Yes, each index has its own B-Tree.
Gert-Jan
(Please reply only to the newsgroup)

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
Thanks
Hi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find avg no. of rows in a page and no. of pages in a table ?

Id like to find the avg no. of rows in a page of a table along with total
number of pages.
ThanksHi Hassan
What version?
In SQL 2005, number of rows is stored in sys.partitions and number of pages
is stored in sys.allocation_units. The whole storage picture is a bit
different in SQL 2005, because of partitioning, and because of the ways that
special data (LOB and row_overflow) is kept track of. So take a look at
those two views in BOL and look at the columns they contain. Try to join
them to get the info you need, and then post back if you get stuck
In SQL 2000, everything is in sysindexes.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Hassan" <hassan@.test.com> wrote in message
news:%23k7zHFLTIHA.280@.TK2MSFTNGP03.phx.gbl...
> Id like to find the avg no. of rows in a page of a table along with total
> number of pages.
> Thanks

find a character in string

Good morning,
In the past, with an Informix db and asp/vbscript pages, I've used the
following SQL statement:
select CustomerName, DrawNumber
from Customer
Where DrawNumber[4,4] not in ("I","M")
to find DrawNumber where the 4th character is not equal to I or M. Now that
we've switched over to a SQLServer db, I'm having some trouble getting this
statement work, or finding the equivalent. Any suggestions?
Thanks in advance!
RoseLookup how to use SUBSTRING() function in SQL Server Books Online.
Anith|||where DrawNumber not like '___[IM]%'
(3 underscores before the opening bracket)
Rose wrote:
> Good morning,
> In the past, with an Informix db and asp/vbscript pages, I've used the
> following SQL statement:
> select CustomerName, DrawNumber
> from Customer
> Where DrawNumber[4,4] not in ("I","M")
> to find DrawNumber where the 4th character is not equal to I or M. Now tha
t
> we've switched over to a SQLServer db, I'm having some trouble getting thi
s
> statement work, or finding the equivalent. Any suggestions?
> Thanks in advance!
> Rose|||Look at SUBSTRING() in BOL. It will help.
Perayu
"Rose" <Rose@.discussions.microsoft.com> wrote in message
news:BA7A20AD-3FF6-414D-96AC-8DF5C34FF75B@.microsoft.com...
> Good morning,
> In the past, with an Informix db and asp/vbscript pages, I've used the
> following SQL statement:
> select CustomerName, DrawNumber
> from Customer
> Where DrawNumber[4,4] not in ("I","M")
> to find DrawNumber where the 4th character is not equal to I or M. Now
> that
> we've switched over to a SQLServer db, I'm having some trouble getting
> this
> statement work, or finding the equivalent. Any suggestions?
> Thanks in advance!
> Rose|||Thanks Trey! It worked perfectly!
"Trey Walpole" wrote:

> where DrawNumber not like '___[IM]%'
> (3 underscores before the opening bracket)
> Rose wrote:
>