Clustered DB Server: index

Showing posts with label index. Show all posts

Thursday, February 16, 2012

clustered-index issue

hi,
yesterday i realized (by those helpfull explenations of a
few guys of this forum) that clustered index contains all
of the fields of the table (it looks like the index is
actually the table itself...?), and also that updateing
varing field types like varchar may change the the length
of a row, and therefor may cause a page-split in the index.
in the first place i described a case of a clustered index
on an IDENTITY column (IDENTITY_INSERT is never ON so it
is actually always auto-incremented), so the page split
may happen only because of an Update, and never because of
an Insert.
well, my table is acutally has only fields of
int,float,and datetime types, and only 1 vharchar(40). the
maximum length of a raw is exaclty 200 (so the minimum is
160 when the varchar is actually empty), so 40 is 20% of
200.
finally, for my questions:
1. if i want to avoid any chance for page-split, should i
set the fill-factor of the clustered index to 80?
2. what would be considered as better performance for
reading & updating (amount of storage place is not an
issue): A. chaging the type of the varchar(40) to char
(40), and setting fill-factor to 100. B. leaving the
varchar(40) as it is, and setting fill-factor to 80.
Thanks in advance.
edo.
edo
Setting fillfactor=100 is sutiable for 'read-only' tables because the page
is 100 percent full and I/O is lower as well
By having clustrered index on the table, you eliminate page splitting
entirely because all new records will be added to the end of the table. But
only do this if you know that a clustered index on the primary key is the
best option for you when it comes to query performance. But just thinking if
you have datapage with 1,5,7 index structure and a new row (let me say 3) is
added to index page. Then 5 and 7 to be moved on a new datapage (created by
SQL Server and allocated anywhere) in order to make room for 3. Now, your
data is not in logical order (external fragmentation).
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>
|||> clustered index contains all of the fields of the table (it looks like the
index is actually the table itself...?),
It is the table itself, and the keys in the clustered index determine how
the rows are ordered. And you only avoid page splits during inserts if the
primary key of the table is also the clustered index keys of the table i.e.
a clustered primary key, as you could have a non-clustered primary key.
I'm not too sure of the various options you are contemplating, though they
appear sound. I'll probably go the char(40) route since space is not an
issue and I believe there are (slight) overheads when dealing with varchar
columns.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>
|||> it probably save place for at least 1 WHOLE row, so it will be able to
place it as a whole without page-split.
Why would it do that if the clustered index is also your primary key?
Say a page is 8020 bytes. If all new rows reached their max size during
inserts, you could've inserted 32 rows and wasted 1604 bytes. If all new
rows were created with no value for the varchar column, you could've
inserted approx. 40 rows. Now if all 40 rows were then updated to fill the
varchar column to 40 chars, you would need 1600 bytes, which still fits on
your page. So the worst case scenario is to waste 1604 bytes per page, and
the best case is to fill the page entirely. NOTE: above calculations
ignored page overheads.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d3bd01c45367$8b11c130$a001280a@.phx.gbl...
> hi,
> i guess i made a basic mistake while trying to "calculate"
> the fill-factor percentage needed:
> i guess the server probably does not gain sagnificant
> improvment by saveing only 20% space of the possible row
> size. it probably save place for at least 1 WHOLE row, so
> it will be able to place it as a whole without page-split.
> actually, i believe 20 percent means that there is enough
> space for much more than 1 row.
> so, as to my questions:
> 1. may be i can even set fill-factor to 99% and still be
> sure that there is no chance for a split-page at all?
> 2. same question with no principle changes.
>
> thanks again.
> edo.
|||hi,
you wrote:
But just thinking if you have datapage with 1,5,7 index
structure and a new row (let me say 3) is added to index
page....
i'm not sure i understand this line.
i ensured that 3 can not come after 7, since the clustered
index in on an IDENTITY column, and i never intend to set
IDENTITY_INSERT to ON.
so, maybe you are talking about a kind of a temporary
result-set built by the server as SELECT SQL query? or
what?
anyway, you mentioned "query performance". as for that,
one of the most common tasks my application is doing is to
find raws accordig to this field, like for example:
select * from my_table where recid=X
thanks again,
edo.
|||edo
Yep, I was assuming that you don't have an Identity property on the table.
Does recid column has a clustered index?
If your output is large set I suggets you to create a clustered index on
this column otherwise I would create a non clustered.
Also run SET STATISTICS IO ON to see what is going on when SQL Server is
perfoming the query.
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1ccff01c4536a$2327dbb0$a301280a@.phx.gbl...
> hi,
> you wrote:
> But just thinking if you have datapage with 1,5,7 index
> structure and a new row (let me say 3) is added to index
> page....
> i'm not sure i understand this line.
> i ensured that 3 can not come after 7, since the clustered
> index in on an IDENTITY column, and i never intend to set
> IDENTITY_INSERT to ON.
> so, maybe you are talking about a kind of a temporary
> result-set built by the server as SELECT SQL query? or
> what?
> anyway, you mentioned "query performance". as for that,
> one of the most common tasks my application is doing is to
> find raws accordig to this field, like for example:
> select * from my_table where recid=X
> thanks again,
> edo.
>
>
>
|||hi,
well, it's much more clear for me now.
indeed, it seems that the bottom line is that in order to
avoid page-split at all, the fill-factor should be
(minimum_row_size/maximum_row_size)*100.
another question, if i may:
thinking of the search issue ALONE, would it be faster for
the SQL-Server to find a row if the index has larger fill-
factor, or it's only a metter of a wasted space?
for example, given two identical tables, both filled
exactly with the same data, but the fill-factor of the
first is 50% and the fill-factor of the second is 100%.
dose a search in the second table would be faster
(significantly or at all)?
another issue, just to make sure: do a fixed-size type
(like Integer) may influence the actual row size if it
alows NULL values? dose a NULL value take the same amount
of space as "real" Value? if not, in order to ensure a
minimum_row_size of 160, i must set all the fields to NOT
NULL, right?
thanks a lot.
edo.
|||I guess it all boils down to what Uri mentioned in the other post i.e. I/O.
The less I/O SQL Server has to read, the faster your operations can
complete. Thus, the more data that can fit on a page, the faster your
search will execute. Whether it is significant would depend on the
difference in the number of pages between the 2 tables, taking into account
SQL Server reads 8 pages at a time (extents), the OS probably reads it in
bigger chunks, and your disk controller probably has its own caching system.
Not forgetting that the pages may be cached in SQL Server buffers where size
permits, further diminishing the difference if the entire table can fit into
the cache.
Re your second question, based on a simple (unscientific) test I did, a NULL
value does not take up more space than a real value. If you're interested,
look up the DBCC PAGE command to view the contents of a data page.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0fd01c45374$6ad1c2a0$a501280a@.phx.gbl...
> hi,
> well, it's much more clear for me now.
> indeed, it seems that the bottom line is that in order to
> avoid page-split at all, the fill-factor should be
> (minimum_row_size/maximum_row_size)*100.
> another question, if i may:
> thinking of the search issue ALONE, would it be faster for
> the SQL-Server to find a row if the index has larger fill-
> factor, or it's only a metter of a wasted space?
> for example, given two identical tables, both filled
> exactly with the same data, but the fill-factor of the
> first is 50% and the fill-factor of the second is 100%.
> dose a search in the second table would be faster
> (significantly or at all)?
> another issue, just to make sure: do a fixed-size type
> (like Integer) may influence the actual row size if it
> alows NULL values? dose a NULL value take the same amount
> of space as "real" Value? if not, in order to ensure a
> minimum_row_size of 160, i must set all the fields to NOT
> NULL, right?
> thanks a lot.
> edo.
>
|||
>If you're interested,
>look up the DBCC PAGE command to view the contents of a
Data page.
i'll check this out.
thanks (again),
edo.
|||There is a bit for each field which allows null, and if the field is null
the flag is turned on... storage for nulls is very very small.
One thing you should consider before changing the varchar into char is
whether or not you intend to index the column... The index on the char
column would be larger than the varchar, and may slow performance..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>

clustered-index issue

hi,
yesterday i realized (by those helpfull explenations of a
few guys of this forum) that clustered index contains all
of the fields of the table (it looks like the index is
actually the table itself...?), and also that updateing
varing field types like varchar may change the the length
of a row, and therefor may cause a page-split in the index.
in the first place i described a case of a clustered index
on an IDENTITY column (IDENTITY_INSERT is never ON so it
is actually always auto-incremented), so the page split
may happen only because of an Update, and never because of
an Insert.
well, my table is acutally has only fields of
int,float,and datetime types, and only 1 vharchar(40). the
maximum length of a raw is exaclty 200 (so the minimum is
160 when the varchar is actually empty), so 40 is 20% of
200.
finally, for my questions:
1. if i want to avoid any chance for page-split, should i
set the fill-factor of the clustered index to 80?
2. what would be considered as better performance for
reading & updating (amount of storage place is not an
issue): A. chaging the type of the varchar(40) to char
(40), and setting fill-factor to 100. B. leaving the
varchar(40) as it is, and setting fill-factor to 80.
Thanks in advance.
edo.edo
Setting fillfactor=100 is sutiable for 'read-only' tables because the page
is 100 percent full and I/O is lower as well
By having clustrered index on the table, you eliminate page splitting
entirely because all new records will be added to the end of the table. But
only do this if you know that a clustered index on the primary key is the
best option for you when it comes to query performance. But just thinking if
you have datapage with 1,5,7 index structure and a new row (let me say 3) is
added to index page. Then 5 and 7 to be moved on a new datapage (created by
SQL Server and allocated anywhere) in order to make room for 3. Now, your
data is not in logical order (external fragmentation).
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>|||hi,
i guess i made a basic mistake while trying to "calculate"
the fill-factor percentage needed:
i guess the server probably does not gain sagnificant
improvment by saveing only 20% space of the possible row
size. it probably save place for at least 1 WHOLE row, so
it will be able to place it as a whole without page-split.
actually, i believe 20 percent means that there is enough
space for much more than 1 row.
so, as to my questions:
1. may be i can even set fill-factor to 99% and still be
sure that there is no chance for a split-page at all?
2. same question with no principle changes.
thanks again.
edo.|||> clustered index contains all of the fields of the table (it looks like the
index is actually the table itself...?),
It is the table itself, and the keys in the clustered index determine how
the rows are ordered. And you only avoid page splits during inserts if the
primary key of the table is also the clustered index keys of the table i.e.
a clustered primary key, as you could have a non-clustered primary key.
I'm not too sure of the various options you are contemplating, though they
appear sound. I'll probably go the char(40) route since space is not an
issue and I believe there are (slight) overheads when dealing with varchar
columns.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>|||> it probably save place for at least 1 WHOLE row, so it will be able to
place it as a whole without page-split.
Why would it do that if the clustered index is also your primary key?
Say a page is 8020 bytes. If all new rows reached their max size during
inserts, you could've inserted 32 rows and wasted 1604 bytes. If all new
rows were created with no value for the varchar column, you could've
inserted approx. 40 rows. Now if all 40 rows were then updated to fill the
varchar column to 40 chars, you would need 1600 bytes, which still fits on
your page. So the worst case scenario is to waste 1604 bytes per page, and
the best case is to fill the page entirely. NOTE: above calculations
ignored page overheads.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d3bd01c45367$8b11c130$a001280a@.phx.gbl...
> hi,
> i guess i made a basic mistake while trying to "calculate"
> the fill-factor percentage needed:
> i guess the server probably does not gain sagnificant
> improvment by saveing only 20% space of the possible row
> size. it probably save place for at least 1 WHOLE row, so
> it will be able to place it as a whole without page-split.
> actually, i believe 20 percent means that there is enough
> space for much more than 1 row.
> so, as to my questions:
> 1. may be i can even set fill-factor to 99% and still be
> sure that there is no chance for a split-page at all?
> 2. same question with no principle changes.
>
> thanks again.
> edo.|||hi,
you wrote:
But just thinking if you have datapage with 1,5,7 index
structure and a new row (let me say 3) is added to index
page....
i'm not sure i understand this line.
i ensured that 3 can not come after 7, since the clustered
index in on an IDENTITY column, and i never intend to set
IDENTITY_INSERT to ON.
so, maybe you are talking about a kind of a temporary
result-set built by the server as SELECT SQL query? or
what?
anyway, you mentioned "query performance". as for that,
one of the most common tasks my application is doing is to
find raws accordig to this field, like for example:
select * from my_table where recid=X
thanks again,
edo.|||edo
Yep, I was assuming that you don't have an Identity property on the table.
Does recid column has a clustered index?
If your output is large set I suggets you to create a clustered index on
this column otherwise I would create a non clustered.
Also run SET STATISTICS IO ON to see what is going on when SQL Server is
perfoming the query.
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1ccff01c4536a$2327dbb0$a301280a@.phx.gbl...
> hi,
> you wrote:
> But just thinking if you have datapage with 1,5,7 index
> structure and a new row (let me say 3) is added to index
> page....
> i'm not sure i understand this line.
> i ensured that 3 can not come after 7, since the clustered
> index in on an IDENTITY column, and i never intend to set
> IDENTITY_INSERT to ON.
> so, maybe you are talking about a kind of a temporary
> result-set built by the server as SELECT SQL query? or
> what?
> anyway, you mentioned "query performance". as for that,
> one of the most common tasks my application is doing is to
> find raws accordig to this field, like for example:
> select * from my_table where recid=X
> thanks again,
> edo.
>
>
>|||hi,
well, it's much more clear for me now.
indeed, it seems that the bottom line is that in order to
avoid page-split at all, the fill-factor should be
(minimum_row_size/maximum_row_size)*100.
another question, if i may:
thinking of the search issue ALONE, would it be faster for
the SQL-Server to find a row if the index has larger fill-
factor, or it's only a metter of a wasted space?
for example, given two identical tables, both filled
exactly with the same data, but the fill-factor of the
first is 50% and the fill-factor of the second is 100%.
dose a search in the second table would be faster
(significantly or at all)?
another issue, just to make sure: do a fixed-size type
(like Integer) may influence the actual row size if it
alows NULL values? dose a NULL value take the same amount
of space as "real" Value? if not, in order to ensure a
minimum_row_size of 160, i must set all the fields to NOT
NULL, right?
thanks a lot.
edo.|||I guess it all boils down to what Uri mentioned in the other post i.e. I/O.
The less I/O SQL Server has to read, the faster your operations can
complete. Thus, the more data that can fit on a page, the faster your
search will execute. Whether it is significant would depend on the
difference in the number of pages between the 2 tables, taking into account
SQL Server reads 8 pages at a time (extents), the OS probably reads it in
bigger chunks, and your disk controller probably has its own caching system.
Not forgetting that the pages may be cached in SQL Server buffers where size
permits, further diminishing the difference if the entire table can fit into
the cache.
Re your second question, based on a simple (unscientific) test I did, a NULL
value does not take up more space than a real value. If you're interested,
look up the DBCC PAGE command to view the contents of a data page.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0fd01c45374$6ad1c2a0$a501280a@.phx.gbl...
> hi,
> well, it's much more clear for me now.
> indeed, it seems that the bottom line is that in order to
> avoid page-split at all, the fill-factor should be
> (minimum_row_size/maximum_row_size)*100.
> another question, if i may:
> thinking of the search issue ALONE, would it be faster for
> the SQL-Server to find a row if the index has larger fill-
> factor, or it's only a metter of a wasted space?
> for example, given two identical tables, both filled
> exactly with the same data, but the fill-factor of the
> first is 50% and the fill-factor of the second is 100%.
> dose a search in the second table would be faster
> (significantly or at all)?
> another issue, just to make sure: do a fixed-size type
> (like Integer) may influence the actual row size if it
> alows NULL values? dose a NULL value take the same amount
> of space as "real" Value? if not, in order to ensure a
> minimum_row_size of 160, i must set all the fields to NOT
> NULL, right?
> thanks a lot.
> edo.
>|||>If you're interested,
>look up the DBCC PAGE command to view the contents of a
Data page.
i'll check this out.
thanks (again),
edo.|||There is a bit for each field which allows null, and if the field is null
the flag is turned on... storage for nulls is very very small.
One thing you should consider before changing the varchar into char is
whether or not you intend to index the column... The index on the char
column would be larger than the varchar, and may slow performance..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>

clustered-index issue

hi,
yesterday i realized (by those helpfull explenations of a
few guys of this forum) that clustered index contains all
of the fields of the table (it looks like the index is
actually the table itself...?), and also that updateing
varing field types like varchar may change the the length
of a row, and therefor may cause a page-split in the index.
in the first place i described a case of a clustered index
on an IDENTITY column (IDENTITY_INSERT is never ON so it
is actually always auto-incremented), so the page split
may happen only because of an Update, and never because of
an Insert.
well, my table is acutally has only fields of
int,float,and datetime types, and only 1 vharchar(40). the
maximum length of a raw is exaclty 200 (so the minimum is
160 when the varchar is actually empty), so 40 is 20% of
200.
finally, for my questions:
1. if i want to avoid any chance for page-split, should i
set the fill-factor of the clustered index to 80?
2. what would be considered as better performance for
reading & updating (amount of storage place is not an
issue): A. chaging the type of the varchar(40) to char
(40), and setting fill-factor to 100. B. leaving the
varchar(40) as it is, and setting fill-factor to 80.
Thanks in advance.
edo.edo
Setting fillfactor=100 is sutiable for 'read-only' tables because the page
is 100 percent full and I/O is lower as well
By having clustrered index on the table, you eliminate page splitting
entirely because all new records will be added to the end of the table. But
only do this if you know that a clustered index on the primary key is the
best option for you when it comes to query performance. But just thinking if
you have datapage with 1,5,7 index structure and a new row (let me say 3) is
added to index page. Then 5 and 7 to be moved on a new datapage (created by
SQL Server and allocated anywhere) in order to make room for 3. Now, your
data is not in logical order (external fragmentation).
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx
.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>|||> clustered index contains all of the fields of the table (it looks like the
index is actually the table itself...?),
It is the table itself, and the keys in the clustered index determine how
the rows are ordered. And you only avoid page splits during inserts if the
primary key of the table is also the clustered index keys of the table i.e.
a clustered primary key, as you could have a non-clustered primary key.
I'm not too sure of the various options you are contemplating, though they
appear sound. I'll probably go the char(40) route since space is not an
issue and I believe there are (slight) overheads when dealing with varchar
columns.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx
.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>|||> it probably save place for at least 1 WHOLE row, so it will be able to
place it as a whole without page-split.
Why would it do that if the clustered index is also your primary key?
Say a page is 8020 bytes. If all new rows reached their max size during
inserts, you could've inserted 32 rows and wasted 1604 bytes. If all new
rows were created with no value for the varchar column, you could've
inserted approx. 40 rows. Now if all 40 rows were then updated to fill the
varchar column to 40 chars, you would need 1600 bytes, which still fits on
your page. So the worst case scenario is to waste 1604 bytes per page, and
the best case is to fill the page entirely. NOTE: above calculations
ignored page overheads.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d3bd01c45367$8b11c130$a001280a@.phx
.gbl...
> hi,
> i guess i made a basic mistake while trying to "calculate"
> the fill-factor percentage needed:
> i guess the server probably does not gain sagnificant
> improvment by saveing only 20% space of the possible row
> size. it probably save place for at least 1 WHOLE row, so
> it will be able to place it as a whole without page-split.
> actually, i believe 20 percent means that there is enough
> space for much more than 1 row.
> so, as to my questions:
> 1. may be i can even set fill-factor to 99% and still be
> sure that there is no chance for a split-page at all?
> 2. same question with no principle changes.
>
> thanks again.
> edo.|||hi,
you wrote:
But just thinking if you have datapage with 1,5,7 index
structure and a new row (let me say 3) is added to index
page....
i'm not sure i understand this line.
i ensured that 3 can not come after 7, since the clustered
index in on an IDENTITY column, and i never intend to set
IDENTITY_INSERT to ON.
so, maybe you are talking about a kind of a temporary
result-set built by the server as SELECT SQL query? or
what?
anyway, you mentioned "query performance". as for that,
one of the most common tasks my application is doing is to
find raws accordig to this field, like for example:
select * from my_table where recid=X
thanks again,
edo.|||edo
Yep, I was assuming that you don't have an Identity property on the table.
Does recid column has a clustered index?
If your output is large set I suggets you to create a clustered index on
this column otherwise I would create a non clustered.
Also run SET STATISTICS IO ON to see what is going on when SQL Server is
perfoming the query.
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1ccff01c4536a$2327dbb0$a301280a@.phx
.gbl...
> hi,
> you wrote:
> But just thinking if you have datapage with 1,5,7 index
> structure and a new row (let me say 3) is added to index
> page....
> i'm not sure i understand this line.
> i ensured that 3 can not come after 7, since the clustered
> index in on an IDENTITY column, and i never intend to set
> IDENTITY_INSERT to ON.
> so, maybe you are talking about a kind of a temporary
> result-set built by the server as SELECT SQL query? or
> what?
> anyway, you mentioned "query performance". as for that,
> one of the most common tasks my application is doing is to
> find raws accordig to this field, like for example:
> select * from my_table where recid=X
> thanks again,
> edo.
>
>
>|||hi,
well, it's much more clear for me now.
indeed, it seems that the bottom line is that in order to
avoid page-split at all, the fill-factor should be
(minimum_row_size/maximum_row_size)*100.
another question, if i may:
thinking of the search issue ALONE, would it be faster for
the SQL-Server to find a row if the index has larger fill-
factor, or it's only a metter of a wasted space?
for example, given two identical tables, both filled
exactly with the same data, but the fill-factor of the
first is 50% and the fill-factor of the second is 100%.
dose a search in the second table would be faster
(significantly or at all)?
another issue, just to make sure: do a fixed-size type
(like Integer) may influence the actual row size if it
alows NULL values? dose a NULL value take the same amount
of space as "real" Value? if not, in order to ensure a
minimum_row_size of 160, i must set all the fields to NOT
NULL, right?
thanks a lot.
edo.|||I guess it all boils down to what Uri mentioned in the other post i.e. I/O.
The less I/O SQL Server has to read, the faster your operations can
complete. Thus, the more data that can fit on a page, the faster your
search will execute. Whether it is significant would depend on the
difference in the number of pages between the 2 tables, taking into account
SQL Server reads 8 pages at a time (extents), the OS probably reads it in
bigger chunks, and your disk controller probably has its own caching system.
Not forgetting that the pages may be cached in SQL Server buffers where size
permits, further diminishing the difference if the entire table can fit into
the cache.
Re your second question, based on a simple (unscientific) test I did, a NULL
value does not take up more space than a real value. If you're interested,
look up the DBCC PAGE command to view the contents of a data page.
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backup files? Try MiniSQLBackup
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d0fd01c45374$6ad1c2a0$a501280a@.phx
.gbl...
> hi,
> well, it's much more clear for me now.
> indeed, it seems that the bottom line is that in order to
> avoid page-split at all, the fill-factor should be
> (minimum_row_size/maximum_row_size)*100.
> another question, if i may:
> thinking of the search issue ALONE, would it be faster for
> the SQL-Server to find a row if the index has larger fill-
> factor, or it's only a metter of a wasted space?
> for example, given two identical tables, both filled
> exactly with the same data, but the fill-factor of the
> first is 50% and the fill-factor of the second is 100%.
> dose a search in the second table would be faster
> (significantly or at all)?
> another issue, just to make sure: do a fixed-size type
> (like Integer) may influence the actual row size if it
> alows NULL values? dose a NULL value take the same amount
> of space as "real" Value? if not, in order to ensure a
> minimum_row_size of 160, i must set all the fields to NOT
> NULL, right?
> thanks a lot.
> edo.
>|||
>If you're interested,
>look up the DBCC PAGE command to view the contents of a
Data page.
i'll check this out.
thanks (again),
edo.|||There is a bit for each field which allows null, and if the field is null
the flag is turned on... storage for nulls is very very small.
One thing you should consider before changing the varchar into char is
whether or not you intend to index the column... The index on the char
column would be larger than the varchar, and may slow performance..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"edo" <anonymous@.discussions.microsoft.com> wrote in message
news:1d37601c4535b$957e53b0$a001280a@.phx
.gbl...
> hi,
> yesterday i realized (by those helpfull explenations of a
> few guys of this forum) that clustered index contains all
> of the fields of the table (it looks like the index is
> actually the table itself...?), and also that updateing
> varing field types like varchar may change the the length
> of a row, and therefor may cause a page-split in the index.
> in the first place i described a case of a clustered index
> on an IDENTITY column (IDENTITY_INSERT is never ON so it
> is actually always auto-incremented), so the page split
> may happen only because of an Update, and never because of
> an Insert.
> well, my table is acutally has only fields of
> int,float,and datetime types, and only 1 vharchar(40). the
> maximum length of a raw is exaclty 200 (so the minimum is
> 160 when the varchar is actually empty), so 40 is 20% of
> 200.
> finally, for my questions:
> 1. if i want to avoid any chance for page-split, should i
> set the fill-factor of the clustered index to 80?
> 2. what would be considered as better performance for
> reading & updating (amount of storage place is not an
> issue): A. chaging the type of the varchar(40) to char
> (40), and setting fill-factor to 100. B. leaving the
> varchar(40) as it is, and setting fill-factor to 80.
> Thanks in advance.
> edo.
>
>
>

Clustered/Non-clustered indexes column selection

From what I've read, clustered index is particularly good for returning a set
of data, expecially if the result set requires a sort by on those columns. My
company is running education services. Let's say our enrollment table
contains the fields year, session (term), course, student_id (fk to student
table), and marks.
We do a lot of queries based on year, session, and course. I'm planning to
put year, session, and course on clustered index. The question is we
definitely will query a particular enrollment for a student using student id.
I read from many articles that non-clustered index always include the
location of the clustered index. If so, does it mean an index on student_id
(which is already there because of the FK) is the same as building one using
year, session, course, and student_id which would cover many of our queries?> If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?
Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
Just be careful, having a wide cl ix makes you nc indexes wide.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
> From what I've read, clustered index is particularly good for returning a set
> of data, expecially if the result set requires a sort by on those columns. My
> company is running education services. Let's say our enrollment table
> contains the fields year, session (term), course, student_id (fk to student
> table), and marks.
> We do a lot of queries based on year, session, and course. I'm planning to
> put year, session, and course on clustered index. The question is we
> definitely will query a particular enrollment for a student using student id.
> I read from many articles that non-clustered index always include the
> location of the clustered index. If so, does it mean an index on student_id
> (which is already there because of the FK) is the same as building one using
> year, session, course, and student_id which would cover many of our queries?|||Thanks Tibor. I'm just curious if I do create an index on all four columns.
Is SQL Server smart enough to see the first 3 are in the clustered index and
not duplicate them in the nc index?
Thanks also for the advice on the wide cl. We were debating whether it is
better to have year + session (5 digits) or year + session + course code (8
digits) as our cl ix. If we have time (yeah, like that will ever happens),
we'll go through the exec plan of our heavy queries with different set up and
see what happens.
"Tibor Karaszi" wrote:
> > If so, does it mean an index on student_id
> > (which is already there because of the FK) is the same as building one using
> > year, session, course, and student_id which would cover many of our queries?
> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the index.
> Just be careful, having a wide cl ix makes you nc indexes wide.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
> > From what I've read, clustered index is particularly good for returning a set
> > of data, expecially if the result set requires a sort by on those columns. My
> > company is running education services. Let's say our enrollment table
> > contains the fields year, session (term), course, student_id (fk to student
> > table), and marks.
> >
> > We do a lot of queries based on year, session, and course. I'm planning to
> > put year, session, and course on clustered index. The question is we
> > definitely will query a particular enrollment for a student using student id.
> > I read from many articles that non-clustered index always include the
> > location of the clustered index. If so, does it mean an index on student_id
> > (which is already there because of the FK) is the same as building one using
> > year, session, course, and student_id which would cover many of our queries?
>
>|||> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
You're welcome. :-)
Yes, SQL Server is "smart" and will not duplicate the columns. Check sysindexes.keycnt, good source
of information for these things.
Heh, sometimes you just need to get on with your work. But an understanding of index architecture
along with how SQL Server can use indexes (like covering a query with non-clustered indexes) will
take you a long way. A big thing is to ask the right questions, and of course later to verify your
thinking.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
news:8025E15E-8E25-4369-8033-40A586AD7205@.microsoft.com...
> Thanks Tibor. I'm just curious if I do create an index on all four columns.
> Is SQL Server smart enough to see the first 3 are in the clustered index and
> not duplicate them in the nc index?
> Thanks also for the advice on the wide cl. We were debating whether it is
> better to have year + session (5 digits) or year + session + course code (8
> digits) as our cl ix. If we have time (yeah, like that will ever happens),
> we'll go through the exec plan of our heavy queries with different set up and
> see what happens.
> "Tibor Karaszi" wrote:
>> > If so, does it mean an index on student_id
>> > (which is already there because of the FK) is the same as building one using
>> > year, session, course, and student_id which would cover many of our queries?
>> Yes. By explicitly naming the cl ix columns, you can control the order of the columns in the
>> index.
>> Just be careful, having a wide cl ix makes you nc indexes wide.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Terence Leung" <TerenceLeung@.discussions.microsoft.com> wrote in message
>> news:C30D63E9-EF56-4D6E-B683-55844C520EC8@.microsoft.com...
>> > From what I've read, clustered index is particularly good for returning a set
>> > of data, expecially if the result set requires a sort by on those columns. My
>> > company is running education services. Let's say our enrollment table
>> > contains the fields year, session (term), course, student_id (fk to student
>> > table), and marks.
>> >
>> > We do a lot of queries based on year, session, and course. I'm planning to
>> > put year, session, and course on clustered index. The question is we
>> > definitely will query a particular enrollment for a student using student id.
>> > I read from many articles that non-clustered index always include the
>> > location of the clustered index. If so, does it mean an index on student_id
>> > (which is already there because of the FK) is the same as building one using
>> > year, session, course, and student_id which would cover many of our queries?
>>

Clustered/Non-clustered Indexes and B-Trees

I would like to find information on Clustered and Non-clustered indexes and how B-trees are used. I know a clustered index is placed into a b-tree which makes sense for fast ordered searching. What data structure does a non-clustered index use and how?

I tried to find info. on the web but couldn't get much detail...I believe all indexes use B-Trees. The difference between a clustered and a non-clustered index is that the clustered index represents the way the records are actually stored in the database, and that is why a table can have only one clustered index.

Non-clustered indexes are stored separately from the table and reference the table's Primary Key.|||Why is it I always find the answer right after I post? :o

Correct me if I'm wrong but a non-clustered index stores the keys in a B-tree with the leaf level nodes containing pointers to the non-contiguous data pages.

A clustered index, however, also stores the keys in a B-tree but the leaves are the actual data pages ordered contiguously. The table data is physically ordered around the key.|||You said it, brother.

Clustered/non-Clustered Index

What does an index add to the performance?

Why do we use Clustered Index and Non-clustered Index?

thanks

Indexes are used to improve the performance of queries

A clustered index will have more performance while executing select query

but take more overhead for insert,delete statements where as a non clustured index will not have any overhead on insert and delete but does not give as much performance hit as of a clustred index for more information on indexed look the following link

An Introduction to Clustered and Non-Clustered Index Data Structures

|||

It's a long story.

Shortly, indexes shorten the time to look for data (when established properly). The 10 000 feet explanation is that clustered index orders rows physically, and data is stored on leaf node of a index, while non-clustered index only points to data location (bookmark: heap or underlying clustered index). Indexes are stored as B-tree (balanced tree) where node levels of each type contain pointers to pages at the next level, while the leaf level contains the key values

Without index, SQL Server has to do table scan and other tricks to locate data, e.g look through the entire table.

You'll find docs here:

Table And Index Architecture
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_8sit.asp

Clustered Indexes
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_1tbn.asp

Non-clustered indexes
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_da2_75mb.asp

|||

In SQL Server you can create one clustered index and 249 none clustered indexes per table, all none clustered indexes include your clustered index. I don't think you can create all 249 none clustered indexes because it is better to let the index tuning wizard which is part of the SQL Server profiler to guide you in your index creation. To improve performance look up index covering in the BOL(books online). Hope this helps.

clustered vs. non clustered

Any existing scripts out there that might do this? I found something
similar and modified it, the sql is included below. This gives me the
list of all the columns I need, I just need to get the foreign keys for
each from here before each one and generate all the create/drop
scripts.

All the columns I am looking to do this for are called "Id" making this
somewhat simpler. I'm just looking to incrementally make the SQL side
better and don't want to rewrite a bunch of application level code to
make the column names ISO compliant, etc.

/*
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
@.object_id INT,
@.index_id TINYINT,
@.column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @.r NVARCHAR(5)
SELECT @.r = CASE INDEXKEY_PROPERTY
(
@.object_id,
@.index_id,
@.column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @.r
END

-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
@.table_name SYSNAME,
@.object_id INT,
@.index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@.colnames NVARCHAR(4000),
@.thisColID INT,
@.thisColName SYSNAME

SET @.colnames = INDEX_COL(@.table_name, @.index_id, 1)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id, 1)

SET @.thisColID = 2
SET @.thisColName = INDEX_COL(@.table_name, @.index_id, @.thisColID)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id, @.thisColID)

WHILE (@.thisColName IS NOT NULL)
BEGIN
SET @.thisColID = @.thisColID + 1
SET @.colnames = @.colnames + ', ' + @.thisColName

SET @.thisColName = INDEX_COL(@.table_name, @.index_id,
@.thisColID)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id,
@.thisColID)
END
RETURN @.colNames
END

CREATE VIEW dbo.vAllIndexes
AS
begin
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id,
i.indid),
IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS:
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
end
*/

SELECT
v.*
FROM
dbo.vAllIndexes v
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND v.COLUMN_LIST = 'Id'
AND v.IS_CLUSTERED = 0
ORDER BY v.TABLE_NAMEIt's OK to have a clustered index that is seperate from your
nonclustered primary key, even if the two indexes cover the same
columns. In fact, I usually build my indexes in this way in case I
ever have to move the clustered index to a different column and I don't
want to mess with my established foreign key constraints.

That being said, I would simply add the clustered index to each table
and not worry about dropping the pre-existing primary key constraint.
It'll take a while, but it will work.

Stu

pb648174 wrote:

Quote:

Originally Posted by

I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes. So my
questions is a) good idea? and b) how? There are foreign key references
to most of them so those would need to be dropped first and then
re-created after the clustered one was created and that could cascade
(I think?)
>
Any existing scripts out there that might do this? I found something
similar and modified it, the sql is included below. This gives me the
list of all the columns I need, I just need to get the foreign keys for
each from here before each one and generate all the create/drop
scripts.
>
All the columns I am looking to do this for are called "Id" making this
somewhat simpler. I'm just looking to incrementally make the SQL side
better and don't want to rewrite a bunch of application level code to
make the column names ISO compliant, etc.
>
/*
-- Returns whether the column is ASC or DESC
CREATE FUNCTION dbo.GetIndexColumnOrder
(
@.object_id INT,
@.index_id TINYINT,
@.column_id TINYINT
)
RETURNS NVARCHAR(5)
AS
BEGIN
DECLARE @.r NVARCHAR(5)
SELECT @.r = CASE INDEXKEY_PROPERTY
(
@.object_id,
@.index_id,
@.column_id,
'IsDescending'
)
WHEN 1 THEN N' DESC'
ELSE N''
END
RETURN @.r
END
>
-- Returns the list of columns in the index
CREATE FUNCTION dbo.GetIndexColumns
(
@.table_name SYSNAME,
@.object_id INT,
@.index_id TINYINT
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE
@.colnames NVARCHAR(4000),
@.thisColID INT,
@.thisColName SYSNAME
>
SET @.colnames = INDEX_COL(@.table_name, @.index_id, 1)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id, 1)
>
SET @.thisColID = 2
SET @.thisColName = INDEX_COL(@.table_name, @.index_id, @.thisColID)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id, @.thisColID)
>
WHILE (@.thisColName IS NOT NULL)
BEGIN
SET @.thisColID = @.thisColID + 1
SET @.colnames = @.colnames + ', ' + @.thisColName
>
SET @.thisColName = INDEX_COL(@.table_name, @.index_id,
@.thisColID)
+ dbo.GetIndexColumnOrder(@.object_id, @.index_id,
@.thisColID)
END
RETURN @.colNames
END
>
CREATE VIEW dbo.vAllIndexes
AS
begin
SELECT
TABLE_NAME = OBJECT_NAME(i.id),
INDEX_NAME = i.name,
COLUMN_LIST = dbo.GetIndexColumns(OBJECT_NAME(i.id), i.id,
i.indid),
IS_CLUSTERED = INDEXPROPERTY(i.id, i.name, 'IsClustered'),
IS_UNIQUE = INDEXPROPERTY(i.id, i.name, 'IsUnique'),
FILE_GROUP = g.GroupName
FROM
sysindexes i
INNER JOIN
sysfilegroups g
ON
i.groupid = g.groupid
WHERE
(i.indid BETWEEN 1 AND 254)
-- leave out AUTO_STATISTICS:
AND (i.Status & 64)=0
-- leave out system tables:
AND OBJECTPROPERTY(i.id, 'IsMsShipped') = 0
end
*/
>
SELECT
v.*
FROM
dbo.vAllIndexes v
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS T
ON
T.CONSTRAINT_NAME = v.INDEX_NAME
AND T.TABLE_NAME = v.TABLE_NAME
AND T.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND v.COLUMN_LIST = 'Id'
AND v.IS_CLUSTERED = 0
ORDER BY v.TABLE_NAME

|||pb648174 (google@.webpaul.net) writes:

Quote:

Originally Posted by

Yes, having clustered indexes on all tables is a good idea, but the
IDENTITY column is not always the best choice. It's a good choice if
you have a high transaction rate, and you want to avoid fragmentation
and page splits.

But for SELECT queries it is likely that in most tables that there
are better candidates for the clustered index, as you don't do
range queries on ids that often. So I would suggest that you review
your tables and look for better columns to cluster on.

Here I had single-column PKs in mind. Clustering on a multi-column PK,
or part of it is another matter. Take an OrderDetails table for instance.
"SELECT ... FROM OrderDetails WHERE OrderID = @.id" is a very likely
query and a clustred index may be great here.

Stu's suggestion of keeping the PK non-clustered, and adding a clustered
index as well is not that bad. If you have a multi-column key that is 4
30 bytes long, but the first key column is four bytes, the clustering on
the first columns means that the key size for the clustered index is
only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
in non-clustered index, this matters quite a bit.

As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Well that makes things simpler then.. I'll try adding the clustered
columns to one area of the app and see if it makes a positive or
negative performance impact. Thanks for the info guys.

Erland Sommarskog wrote:

Quote:

Originally Posted by

pb648174 (google@.webpaul.net) writes:

Quote:

Originally Posted by

>
Yes, having clustered indexes on all tables is a good idea, but the
IDENTITY column is not always the best choice. It's a good choice if
you have a high transaction rate, and you want to avoid fragmentation
and page splits.
>
But for SELECT queries it is likely that in most tables that there
are better candidates for the clustered index, as you don't do
range queries on ids that often. So I would suggest that you review
your tables and look for better columns to cluster on.
>
Here I had single-column PKs in mind. Clustering on a multi-column PK,
or part of it is another matter. Take an OrderDetails table for instance.
"SELECT ... FROM OrderDetails WHERE OrderID = @.id" is a very likely
query and a clustred index may be great here.
>
Stu's suggestion of keeping the PK non-clustered, and adding a clustered
index as well is not that bad. If you have a multi-column key that is 4
30 bytes long, but the first key column is four bytes, the clustering on
the first columns means that the key size for the clustered index is
only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
in non-clustered index, this matters quite a bit.
>
As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||Performance was actually worse once I added the clustered index. A
query that takes 4 seconds took 5 seconds after adding clustered
indexes to all the tables for a particular module. I turned the actual
execution plan display on and saw that it was using the clustered index
instead of the non clustered. So without the clustered index the
largest time used is an "index seek" and a "table spool/lazy spool" and
with the clustered index the index seek just becomes a clustered index
seek... No big difference except it takes longer!

pb648174 wrote:

Quote:

Originally Posted by

Well that makes things simpler then.. I'll try adding the clustered
columns to one area of the app and see if it makes a positive or
negative performance impact. Thanks for the info guys.
>
Erland Sommarskog wrote:

Quote:

Originally Posted by

pb648174 (google@.webpaul.net) writes:

Quote:

Originally Posted by

As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.

|||Index tuning is not black-and-white, especially when it comes to the
clustered index decision. It is likely than some queries will benefit by
the PK clustered index while others will not. You'll need run a mix of
queries that is representative of the actual workload mix to ascertain
overall performance impact. IMHO, an all-or-nothing clustered index
decision is naive.

It is also possible that some tables will benefit with the clustered PK and
others will not. I know that this adds a wrinkle to automated schema
generation but this is reality. You might consider using the Index Tuning
Wizard (SQL 2000) or Database Engine Tuning Advisor (SQL 2005) for index
recommendations based on workload.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"pb648174" <google@.webpaul.netwrote in message
news:1155727057.245342.208480@.74g2000cwt.googlegro ups.com...

Quote:

Originally Posted by

Performance was actually worse once I added the clustered index. A
query that takes 4 seconds took 5 seconds after adding clustered
indexes to all the tables for a particular module. I turned the actual
execution plan display on and saw that it was using the clustered index
instead of the non clustered. So without the clustered index the
largest time used is an "index seek" and a "table spool/lazy spool" and
with the clustered index the index seek just becomes a clustered index
seek... No big difference except it takes longer!
>
pb648174 wrote:

Quote:

Originally Posted by

>Well that makes things simpler then.. I'll try adding the clustered
>columns to one area of the app and see if it makes a positive or
>negative performance impact. Thanks for the info guys.
>>
>Erland Sommarskog wrote:

Quote:

Originally Posted by

pb648174 (google@.webpaul.net) writes:
I've been doing a bit of reading and have read in quite a few places
that an identity column is a good clustered index and that all or at
least most tables should have a clustered index. The tool I used to
generate tables made them all with non clustered indexes so I would
like to drop all of them and generate clustered indexes.
>
Yes, having clustered indexes on all tables is a good idea, but the
IDENTITY column is not always the best choice. It's a good choice if
you have a high transaction rate, and you want to avoid fragmentation
and page splits.
>
But for SELECT queries it is likely that in most tables that there
are better candidates for the clustered index, as you don't do
range queries on ids that often. So I would suggest that you review
your tables and look for better columns to cluster on.
>
Here I had single-column PKs in mind. Clustering on a multi-column PK,
or part of it is another matter. Take an OrderDetails table for
instance.
"SELECT ... FROM OrderDetails WHERE OrderID = @.id" is a very likely
query and a clustred index may be great here.
>
Stu's suggestion of keeping the PK non-clustered, and adding a
clustered
index as well is not that bad. If you have a multi-column key that is 4
30 bytes long, but the first key column is four bytes, the clustering
on
the first columns means that the key size for the clustered index is
only 8 bytes. (key col + uniquifier). Since cluster-key colunms appear
in non-clustered index, this matters quite a bit.
>
As for looking up the foreign keys, the tables are sysreferences in
SQL 2000 and sys.forein_keys in SQL 2005.
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

clustered Vs NonClustered indexes

I was looking for some info on the major differences between a clustered and
a non-clustered index, but I couldn't find anything in the MS knowledgebase.
Which is more efficient? I am creating reports on an audit table that has
approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
queries take for ever to run. If I changed the indexes to clustered will
that speed things up or will it make no difference?
TIA,
JoeClustered Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms190639.aspx
Nonclustered Index Design Guidelines
http://msdn.microsoft.com/library/e...ar_da2_1tbn.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@.microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>|||Efficient for reporting queries? I have found it is particularly beneficial
to have a clustered index that compliments the same sort order of the query.
It also helps if the first column of the clustered index is included in the
where clause.
select
state, product category, dateofsale, price
from
SALES
where
state = 'FL'
order by
state, product category, dateofsale
Try creating a clustered index on: state, product category, dateofsale
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@.microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>|||On Fri, 16 Dec 2005 12:38:02 -0800, jaylou
<jaylou@.discussions.microsoft.com> wrote:
>I was looking for some info on the major differences between a clustered an
d
>a non-clustered index, but I couldn't find anything in the MS knowledgebase
.
>Which is more efficient? I am creating reports on an audit table that has
>approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
>queries take for ever to run. If I changed the indexes to clustered will
>that speed things up or will it make no difference?
Indexed separately or together?
How many rows is returned by each query?
If you want any informed answers here, posting at least the select
statements (and what fields you've indexed) is going to be helpful.
In general, it's unlikely that clustered/unclustered by itself is
going to affect your runtimes much.
J.
ps - how long is "for ever"?
pps - are other people updating the table at the same time you're
running reports?|||Clustered indexes are best used when you need to return data ordered
according to a criteria, in other words a range of data. Non-clustered
indexes are best used for singleton fetches, or data which is not
necessarily contiguous.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"jaylou" <jaylou@.discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@.microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>

Clustered Vrs Non Clustered Indexes

Dear All
I recently posted a message where I stated that a
clustered index is faster than a non clustered index, is
this correct or do I have my facts totally wrong ?
Thanks
Peter
Some definitions first:
An index doesn't have any speed to talk about; it isn't fast or slow. The optimizer generates
execution plans where those plans might use indexes in different ways. And when the execution engine
uses the plans, using an index or different index types influences performance.
A blanket statement like "clustered index are fasten than non-clustered indexes" is too much of a
generalization. There are cases where using a non-clustered index will result in fewer I/O
operations and shorter execution time compared to using a clustered index. One such example is where
a non-clustered index covers the query.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
> Dear All
> I recently posted a message where I stated that a
> clustered index is faster than a non clustered index, is
> this correct or do I have my facts totally wrong ?
> Thanks
> Peter
|||Thanks Tibor,
Ah well, wrong again ;)
Peter

>--Original Message--
>Some definitions first:
>An index doesn't have any speed to talk about; it isn't
fast or slow. The optimizer generates
>execution plans where those plans might use indexes in
different ways. And when the execution engine
>uses the plans, using an index or different index types
influences performance.
>
>A blanket statement like "clustered index are fasten than
non-clustered indexes" is too much of a
>generalization. There are cases where using a non-
clustered index will result in fewer I/O
>operations and shorter execution time compared to using a
clustered index. One such example is where
>a non-clustered index covers the query.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>
>.
>
|||I wouldn't say "wrong", Pete. But perhaps an over-generalization... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks Tibor,
> Ah well, wrong again ;)
> Peter
>
> fast or slow. The optimizer generates
> different ways. And when the execution engine
> influences performance.
> non-clustered indexes" is too much of a
> clustered index will result in fewer I/O
> clustered index. One such example is where
> wrote in message
|||So far over its caused a capsize :-D

>--Original Message--
>I wouldn't say "wrong", Pete. But perhaps an over-
generalization... :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
than[vbcol=seagreen]
a[vbcol=seagreen]
is
>
>.
>
|||Peter, Peter, Peter...
Don't be so hard on yourself.
Don't you remember what Einstein said?
He would rather have a watch that had stopped completely, than one that lost
a second a day.
A watch that had stopped would still show the correct time twice every day.
If you just keep saying that clustered indexes are faster than nonclustered,
you will occasionally be right.
:-)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0efe01c51509$798a6890$a401280a@.phx.gbl...[vbcol=seagreen]
> So far over its caused a capsize :-D
>
> generalization... :-)
> wrote in message
> than
> a
> is
|||Mark Wilden wrote:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23r$pIsQFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> I hope this is apocryphal or metaphorical or just a joke.

> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.

Thursday, February 16, 2012

clustered-index issue

clustered-index issue

clustered-index issue

Clustered/Non-clustered indexes column selection

Clustered/Non-clustered indexes column selection

Clustered/Non-clustered indexes column selection

Clustered/Non-clustered Indexes and B-Trees

Clustered/non-Clustered Index

clustered vs. non clustered

clustered Vs NonClustered indexes

Clustered Vrs Non Clustered Indexes

Clustered DB Server

Blog Archive

About Me