define Hierarchy: index

Showing posts with label index. Show all posts

Friday, March 23, 2012

How to determine columns that are part of an index.

Hello,
How can I use SQL-DMO to get list of all the columns that are part of
an index?
I am able to determine if the column is primay key.
ThanksSELECT sysindexes.name, syscolumns.name, *
FROM dbo.sysindexes
JOIN dbo.sysindexkeys ON (sysindexes.id = sysindexkeys.id AND
sysindexes.indid = sysindexkeys.indid)
JOIN dbo.syscolumns ON (sysindexes.id = syscolumns.id AND
sysindexkeys.colid = syscolumns.colid)
-- all indecies of table 'cus_address'
WHERE OBJECT_NAME(sysindexes.id) = 'cus_address'
-- indicies on column 'PKadr_id'
WHERE syscolumns.name = 'PKadr_id'
-- indicies with name 'ix_adr'
WHERE sysindexes.name = 'ix_adr'
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> schrieb im Newsbeitrag
news:<76C61B54-40C9-48E4-A89E-F6FF9A76DF0C@.microsoft.com>...
> Hello,
> How can I use SQL-DMO to get list of all the columns that are part of
> an index?
> I am able to determine if the column is primay key.
> Thanks

How to determine ASC/DESC of a column in an index?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to programmatically determine the ASC/DESC nature of a column in an
index?
For example, if I declare an index to be:
create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
Thanks for any help you can provide!
John PetersonAfter a bit of fiddling around, the data *appears* to be in the [dbo].[sysindexes].[keys]
column. However, I'm not sure how to interpret this data. :-(
From this link:
http://sybooks.sybase.com/onlinebooks/group-asarc/asg1192e/asefun92/@.Generic__BookTextView/22111
I wish that SQL Server had a INDEX_COLORDER() built-in function. ;-)
Any additional thoughts?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ei2YX6LAEHA.1036@.TK2MSFTNGP10.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to programmatically determine the ASC/DESC nature of a column in an
> index?
> For example, if I declare an index to be:
> create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
> I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
> Thanks for any help you can provide!
> John Peterson
>|||sp_helpindex will return this information. The index_keys
column returns the index keys - descending has a minus sign
following the column name.
The INDEXKEY_PROPERTY function can be used to determine if
an index key is descending or ascending. Sysindexkeys system
table can help you in determining the key_id.
In SQL-DMO, you can use the GetIndexedColumnDESC method of
the Index2 object to determine if a column in an index is
descending.
-Sue
On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to programmatically determine the ASC/DESC nature of a column in an
>index?
>For example, if I declare an index to be:
>create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
>I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
>Thanks for any help you can provide!
>John Peterson
>|||Thanks, Sue! That's *exactly* what I was looking for! I cobbled something based on the
31st byte of the 32 byte chunks in the sysindexes.keys, but it was rife with disaster.
(But seemed to work!)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:r3ja40lp2nde0h8pbf1fjc92oqfkc252de@.4ax.com...
> sp_helpindex will return this information. The index_keys
> column returns the index keys - descending has a minus sign
> following the column name.
> The INDEXKEY_PROPERTY function can be used to determine if
> an index key is descending or ascending. Sysindexkeys system
> table can help you in determining the key_id.
> In SQL-DMO, you can use the GetIndexedColumnDESC method of
> the Index2 object to determine if a column in an index is
> descending.
> -Sue
> On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3a)
> >
> >Hello all!
> >
> >Is there any way to programmatically determine the ASC/DESC nature of a column in an
> >index?
> >
> >For example, if I declare an index to be:
> >
> >create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
> >
> >I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
> >
> >Thanks for any help you can provide!
> >
> >John Peterson
> >
>

How to determine ASC/DESC of a column in an index?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to programmatically determine the ASC/DESC nature of a colu
mn in an
index?
For example, if I declare an index to be:
create unique clustered index [Test] on [MyTable](Field1 desc, Field
2)
I'd like to determine, somehow, that Field1 is declared with the DESC keywor
d.
Thanks for any help you can provide!
John PetersonAfter a bit of fiddling around, the data *appears* to be in the [dbo].&#
91;sysindexes].[keys]
column. However, I'm not sure how to interpret this data. :-(
From this link:
http://sybooks.sybase.com/onlineboo...
TextView/22111
I wish that SQL Server had a INDEX_COLORDER() built-in function. ;-)
Any additional thoughts?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ei2YX6LAEHA.1036@.TK2MSFTNGP10.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to programmatically determine the ASC/DESC nature of a co
lumn in an
> index?
> For example, if I declare an index to be:
> create unique clustered index [Test] on [MyTable](Field1 desc, Fie
ld2)
> I'd like to determine, somehow, that Field1 is declared with the DESC keyw
ord.
> Thanks for any help you can provide!
> John Peterson
>|||sp_helpindex will return this information. The index_keys
column returns the index keys - descending has a minus sign
following the column name.
The INDEXKEY_PROPERTY function can be used to determine if
an index key is descending or ascending. Sysindexkeys system
table can help you in determining the key_id.
In SQL-DMO, you can use the GetIndexedColumnDESC method of
the Index2 object to determine if a column in an index is
descending.
-Sue
On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:

>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to programmatically determine the ASC/DESC nature of a col
umn in an
>index?
>For example, if I declare an index to be:
>create unique clustered index [Test] on [MyTable](Field1 desc, Fiel
d2)
>I'd like to determine, somehow, that Field1 is declared with the DESC keywo
rd.
>Thanks for any help you can provide!
>John Peterson
>|||Thanks, Sue! That's *exactly* what I was looking for! I cobbled something
based on the
31st byte of the 32 byte chunks in the sysindexes.keys, but it was rife with
disaster.
(But seemed to work!)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:r3ja40lp2nde0h8pbf1fjc92oqfkc252de@.
4ax.com...
> sp_helpindex will return this information. The index_keys
> column returns the index keys - descending has a minus sign
> following the column name.
> The INDEXKEY_PROPERTY function can be used to determine if
> an index key is descending or ascending. Sysindexkeys system
> table can help you in determining the key_id.
> In SQL-DMO, you can use the GetIndexedColumnDESC method of
> the Index2 object to determine if a column in an index is
> descending.
> -Sue
> On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
>
>sql

How to determine as to when an index was last rebuilt.

Hi,
Is it possible to determine the date/time as to when an index was last creat
ed / rebuilt.
ThanxHi,
SQL server will not store the index creation or modification dates.
Date and time the statistics were last updated can be viewd using the
command.
DBCC SHOW_STATISTICS ( table , index_name)
Thanks
Hari
MCDBA
"Ramesh" <Ramesh@.discussions.microsoft.com> wrote in message
news:6CBA7E49-EB22-4EA2-A185-134B94A726B9@.microsoft.com...
> Hi,
> Is it possible to determine the date/time as to when an index was last
created / rebuilt.
> Thanx

How to determine as to when an index was last rebuilt.

Hi,
Is it possible to determine the date/time as to when an index was last created / rebuilt.
Thanx
Hi,
SQL server will not store the index creation or modification dates.
Date and time the statistics were last updated can be viewd using the
command.
DBCC SHOW_STATISTICS ( table , index_name)
Thanks
Hari
MCDBA
"Ramesh" <Ramesh@.discussions.microsoft.com> wrote in message
news:6CBA7E49-EB22-4EA2-A185-134B94A726B9@.microsoft.com...
> Hi,
> Is it possible to determine the date/time as to when an index was last
created / rebuilt.
> Thanx

Sunday, February 19, 2012

How to defragment a table which does not have clustered index

Hello,
DBCC SHOWCONTIG shows that some table is fragmented.
This table does not have a clustered index.
AFAIK, DBCC INDEXDEFRAG or DBCC DBREINDEX will re-organize a table when
this table have a clustered index only.
Does anyone know how to defragment a table
which does not have a clustered index?
Thank you for your help,
OlegWhat kind of defragmentation do you expect to achieve for the data pages for
a table which doesn't have a clustered index? Such pages are *not* connected
in a linked list (as they were in the old architecture).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Oleg" <oleg@.nospam.com> wrote in message
news:O0xrFgw9DHA.632@.TK2MSFTNGP12.phx.gbl...
> Hello,
> DBCC SHOWCONTIG shows that some table is fragmented.
> This table does not have a clustered index.
> AFAIK, DBCC INDEXDEFRAG or DBCC DBREINDEX will re-organize a table when
> this table have a clustered index only.
> Does anyone know how to defragment a table
> which does not have a clustered index?
> Thank you for your help,
> Oleg
>|||Hello Tibor,
I am not a big expert in how SQL stores its tables.
DBCC SHOWCONTIG shows me that
table has Logical Fragmentation 75%
and its indices have 0%
I expected that if some value is not applicable
to some object it will have value NULL.
So I expected that if fragmentation is not applicable to heap tables
it will be showed as NULL by DBCC.
It is not so.
So what does that fragmentation value mean for heap tables
and is it a problem?
Thank you for your help,
Oleg
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:u2$Tglw9DHA.1268@.TK2MSFTNGP12.phx.gbl...
> What kind of defragmentation do you expect to achieve for the data pages
for
> a table which doesn't have a clustered index? Such pages are *not*
connected
> in a linked list (as they were in the old architecture).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Oleg" <oleg@.nospam.com> wrote in message
> news:O0xrFgw9DHA.632@.TK2MSFTNGP12.phx.gbl...
> > Hello,
> >
> > DBCC SHOWCONTIG shows that some table is fragmented.
> > This table does not have a clustered index.
> > AFAIK, DBCC INDEXDEFRAG or DBCC DBREINDEX will re-organize a table when
> > this table have a clustered index only.
> > Does anyone know how to defragment a table
> > which does not have a clustered index?
> >
> > Thank you for your help,
> >
> > Oleg
> >
> >
>|||Hi Oleg!
BOL states that Logical Scan Fragmentation "is not relevant to heap and text
indexes".
You could argue that it should be NULL and I would agree. Consider sending a
request to sqlwish@.microsoft.com.
What can happen with heap pages is that they aren't 100% full or that there
are "air" between the extents that they use (or even that not all pages on
the extents are used). This is generally not considered a big issue, and
there is no way to sort this out except to export/import data.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Oleg" <oleg@.nospam.com> wrote in message
news:OAOmktw9DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Hello Tibor,
> I am not a big expert in how SQL stores its tables.
> DBCC SHOWCONTIG shows me that
> table has Logical Fragmentation 75%
> and its indices have 0%
> I expected that if some value is not applicable
> to some object it will have value NULL.
> So I expected that if fragmentation is not applicable to heap tables
> it will be showed as NULL by DBCC.
> It is not so.
> So what does that fragmentation value mean for heap tables
> and is it a problem?
> Thank you for your help,
> Oleg
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u2$Tglw9DHA.1268@.TK2MSFTNGP12.phx.gbl...
> > What kind of defragmentation do you expect to achieve for the data pages
> for
> > a table which doesn't have a clustered index? Such pages are *not*
> connected
> > in a linked list (as they were in the old architecture).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Oleg" <oleg@.nospam.com> wrote in message
> > news:O0xrFgw9DHA.632@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > DBCC SHOWCONTIG shows that some table is fragmented.
> > > This table does not have a clustered index.
> > > AFAIK, DBCC INDEXDEFRAG or DBCC DBREINDEX will re-organize a table
when
> > > this table have a clustered index only.
> > > Does anyone know how to defragment a table
> > > which does not have a clustered index?
> > >
> > > Thank you for your help,
> > >
> > > Oleg
> > >
> > >
> >
> >
>|||However, IIRC, DBCC REINDEX will do enough reorganization to eliminate the
forwarding pointers for a heap table which, if it is big enough, would
result in faster table scans. (Seems that I attended a session by Kimberley
Tripp that described this behavior.)
Russell Fields
"Oleg" <oleg@.nospam.com> wrote in message
news:OAOmktw9DHA.2392@.TK2MSFTNGP11.phx.gbl...
> Hello Tibor,
> I am not a big expert in how SQL stores its tables.
> DBCC SHOWCONTIG shows me that
> table has Logical Fragmentation 75%
> and its indices have 0%
> I expected that if some value is not applicable
> to some object it will have value NULL.
> So I expected that if fragmentation is not applicable to heap tables
> it will be showed as NULL by DBCC.
> It is not so.
> So what does that fragmentation value mean for heap tables
> and is it a problem?
> Thank you for your help,
> Oleg
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:u2$Tglw9DHA.1268@.TK2MSFTNGP12.phx.gbl...
> > What kind of defragmentation do you expect to achieve for the data pages
> for
> > a table which doesn't have a clustered index? Such pages are *not*
> connected
> > in a linked list (as they were in the old architecture).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Oleg" <oleg@.nospam.com> wrote in message
> > news:O0xrFgw9DHA.632@.TK2MSFTNGP12.phx.gbl...
> > > Hello,
> > >
> > > DBCC SHOWCONTIG shows that some table is fragmented.
> > > This table does not have a clustered index.
> > > AFAIK, DBCC INDEXDEFRAG or DBCC DBREINDEX will re-organize a table
when
> > > this table have a clustered index only.
> > > Does anyone know how to defragment a table
> > > which does not have a clustered index?
> > >
> > > Thank you for your help,
> > >
> > > Oleg
> > >
> > >
> >
> >
>|||Ahh, I always seem to forget those forwarding pointers. So DBREINDEX will
re-write the index row, pointing it to the proper datapage and remove the
forwarding pointer from the old data page, as I understand it.
Thanks Russell, yet another thing I didn't know! :-)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:uYTDbfy9DHA.1052@.TK2MSFTNGP12.phx.gbl...
> However, IIRC, DBCC REINDEX will do enough reorganization to eliminate the
> forwarding pointers for a heap table which, if it is big enough, would
> result in faster table scans. (Seems that I attended a session by
Kimberley
> Tripp that described this behavior.)
> Russell Fields
> "Oleg" <oleg@.nospam.com> wrote in message
> news:OAOmktw9DHA.2392@.TK2MSFTNGP11.phx.gbl...
> > Hello Tibor,
> >
> > I am not a big expert in how SQL stores its tables.
> > DBCC SHOWCONTIG shows me that
> > table has Logical Fragmentation 75%
> > and its indices have 0%
> > I expected that if some value is not applicable
> > to some object it will have value NULL.
> > So I expected that if fragmentation is not applicable to heap tables
> > it will be showed as NULL by DBCC.
> > It is not so.
> > So what does that fragmentation value mean for heap tables
> > and is it a problem?
> >
> > Thank you for your help,
> >
> > Oleg
> >
> > "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> > message news:u2$Tglw9DHA.1268@.TK2MSFTNGP12.phx.gbl...
> > > What kind of defragmentation do you expect to achieve for the data
pages
> > for
> > > a table which doesn't have a clustered index? Such pages are *not*
> > connected
> > > in a linked list (as they were in the old architecture).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Oleg" <oleg@.nospam.com> wrote in message
> > > news:O0xrFgw9DHA.632@.TK2MSFTNGP12.phx.gbl...
> > > > Hello,
> > > >
> > > > DBCC SHOWCONTIG shows that some table is fragmented.
> > > > This table does not have a clustered index.
> > > > AFAIK, DBCC INDEXDEFRAG or DBCC DBREINDEX will re-organize a table
> when
> > > > this table have a clustered index only.
> > > > Does anyone know how to defragment a table
> > > > which does not have a clustered index?
> > > >
> > > > Thank you for your help,
> > > >
> > > > Oleg
> > > >
> > > >
> > >
> > >
> >
> >
>

Friday, March 23, 2012

How to determine columns that are part of an index.

How to determine ASC/DESC of a column in an index?

How to determine ASC/DESC of a column in an index?

How to determine as to when an index was last rebuilt.

How to determine as to when an index was last rebuilt.

Sunday, February 19, 2012

How to defragment a table which does not have clustered index

How to defragment a table which does not have clustered index

define Hierarchy

Blog Archive

About Me