Changing the clustered index on a table - tips?

Hi,
SQL Server 2000
I've got a table that doesn't change during the day but has inserts
overnight. currently 4 million rows in the table and I would say no
more than 5000 rows being added nightly. Db server is totally
overloaded and sometimes queries on this table take a long time.
Queries usually filter on a client_id and a input_date range. Input
date range is not normally very selective. There can be up to about
100k rows per client_id. There are normally additional group by
clauses or further filters but they are not applied every time. Also
frequently used is a bit flag called billed. billed = 0 is very
selective (0-5%), billed = 1 is not (>95%). The clustering is
currently on the identity PK so is completely wasted - only benefit is
that the fill factor can be set to 90 as allrows are currently added
to the end of the table.
I want to change the clustering index to help improve performance of
queries. My initial thought is:
(client_id, input_date). Does this seem remotely sensible? Should it
be the other way round?
Given that I would always expect client_id to be used in a query,
would putting a non-clustered index on (billed) achieve anything? Or
on (client_id, billed)?
The suggested clustered index seems to work quite well and although
not making much difference to CPU time seems to cut logical reads by
about 40x. I think it will have more effect on Live which is much
busier and much less powerful than the dev server (yes I know that's
daft!)
I think this will increase fragmentation on the table. Should I be
worried about this - I intend to rebuild all indexes with a fillfactor
of 70? My plan would be to follow the following:
DROP all existing indexes and constraints
Create new clustered index with fillfactor of 70
recreate nonclustered indexes and constraints with fillfactor of 70
Is it okay to build a clustered index on a populated table or is it
better to create a new table, add clustered index and then populate,
then rename?
Many thanks.
Cheers,
James
Jim,
I have done similar(composite nonunique CI's for ranges) with good results
but it sounds like this is a big deal so I would test as much as possible. I
would also consider your NCI's during testing. Does adding a column or two
to an NCI cover more queries? 70% on the fillfactor sounds low to me if you
are just adding 5k rows\day. How often are you reindexing?
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
> Hi,
> SQL Server 2000
> I've got a table that doesn't change during the day but has inserts
> overnight. currently 4 million rows in the table and I would say no
> more than 5000 rows being added nightly. Db server is totally
> overloaded and sometimes queries on this table take a long time.
> Queries usually filter on a client_id and a input_date range. Input
> date range is not normally very selective. There can be up to about
> 100k rows per client_id. There are normally additional group by
> clauses or further filters but they are not applied every time. Also
> frequently used is a bit flag called billed. billed = 0 is very
> selective (0-5%), billed = 1 is not (>95%). The clustering is
> currently on the identity PK so is completely wasted - only benefit is
> that the fill factor can be set to 90 as allrows are currently added
> to the end of the table.
> I want to change the clustering index to help improve performance of
> queries. My initial thought is:
> (client_id, input_date). Does this seem remotely sensible? Should it
> be the other way round?
> Given that I would always expect client_id to be used in a query,
> would putting a non-clustered index on (billed) achieve anything? Or
> on (client_id, billed)?
> The suggested clustered index seems to work quite well and although
> not making much difference to CPU time seems to cut logical reads by
> about 40x. I think it will have more effect on Live which is much
> busier and much less powerful than the dev server (yes I know that's
> daft!)
> I think this will increase fragmentation on the table. Should I be
> worried about this - I intend to rebuild all indexes with a fillfactor
> of 70? My plan would be to follow the following:
> DROP all existing indexes and constraints
> Create new clustered index with fillfactor of 70
> recreate nonclustered indexes and constraints with fillfactor of 70
> Is it okay to build a clustered index on a populated table or is it
> better to create a new table, add clustered index and then populate,
> then rename?
> Many thanks.
> Cheers,
> James
|||If the database is in Simple or Bulk Logged mode the Creation and rebuilding
of the indexes can be minimally logged and faster as a result. Just be sure
to switch back to Full and take a full backup when done.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:64479e9a-191e-4445-b52d-4a9a89ef8a13@.i29g2000prf.googlegroups.com...
On Feb 27, 2:03 am, "jason" <jason-r3m...@.statisticsio.com> wrote:
> Jim,
> I have done similar(composite nonunique CI's for ranges) with good results
> but it sounds like this is a big deal so I would test as much as possible.
> I
> would also consider your NCI's during testing. Does adding a column or two
> to an NCI cover more queries? 70% on the fillfactor sounds low to me if
> you
> are just adding 5k rows\day. How often are you reindexing?
> --
> Jason Massie
> www:http://statisticsio.com
> rss:http://feeds.feedburner.com/statisticsio
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
>
>
>
>
>
>
>
> - Show quoted text -
Hi Jason,
I checked the maintenance plans and they get rebuilt every Sunday with
a fixed free space percentage of 10% - so fillfactor is irrelevant.
Anyway following on from your comments I'll specify fillfactor as 90.
I've had a play with the indexes and the suggested clustered index
works extremely well. The nonclustered one I suggested on billed or
client_id, billed is never actually used as it always favours the
clustered index seek with a WHERE clause on the billed column.
Thanks for your help.
Just one more question - the database is fully logged. Does it make
any difference to speed/transaction log size whether you recluster
the index in situ or in a new replacement table?
James

Changing the clustered index on a table - tips?

Hi,
SQL Server 2000
I've got a table that doesn't change during the day but has inserts
overnight. currently 4 million rows in the table and I would say no
more than 5000 rows being added nightly. Db server is totally
overloaded and sometimes queries on this table take a long time.
Queries usually filter on a client_id and a input_date range. Input
date range is not normally very selective. There can be up to about
100k rows per client_id. There are normally additional group by
clauses or further filters but they are not applied every time. Also
frequently used is a bit flag called billed. billed = 0 is very
selective (0-5%), billed = 1 is not (>95%). The clustering is
currently on the identity PK so is completely wasted - only benefit is
that the fill factor can be set to 90 as allrows are currently added
to the end of the table.
I want to change the clustering index to help improve performance of
queries. My initial thought is:
(client_id, input_date). Does this seem remotely sensible? Should it
be the other way round?
Given that I would always expect client_id to be used in a query,
would putting a non-clustered index on (billed) achieve anything? Or
on (client_id, billed)?
The suggested clustered index seems to work quite well and although
not making much difference to CPU time seems to cut logical reads by
about 40x. I think it will have more effect on Live which is much
busier and much less powerful than the dev server (yes I know that's
daft!)
I think this will increase fragmentation on the table. Should I be
worried about this - I intend to rebuild all indexes with a fillfactor
of 70? My plan would be to follow the following:
DROP all existing indexes and constraints
Create new clustered index with fillfactor of 70
recreate nonclustered indexes and constraints with fillfactor of 70
Is it okay to build a clustered index on a populated table or is it
better to create a new table, add clustered index and then populate,
then rename?
Many thanks.
Cheers,
JamesJim,
I have done similar(composite nonunique CI's for ranges) with good results
but it sounds like this is a big deal so I would test as much as possible. I
would also consider your NCI's during testing. Does adding a column or two
to an NCI cover more queries? 70% on the fillfactor sounds low to me if you
are just adding 5k rows\day. How often are you reindexing?
--
Jason Massie
www: http://statisticsio.com
rss: http://feeds.feedburner.com/statisticsio
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
> Hi,
> SQL Server 2000
> I've got a table that doesn't change during the day but has inserts
> overnight. currently 4 million rows in the table and I would say no
> more than 5000 rows being added nightly. Db server is totally
> overloaded and sometimes queries on this table take a long time.
> Queries usually filter on a client_id and a input_date range. Input
> date range is not normally very selective. There can be up to about
> 100k rows per client_id. There are normally additional group by
> clauses or further filters but they are not applied every time. Also
> frequently used is a bit flag called billed. billed = 0 is very
> selective (0-5%), billed = 1 is not (>95%). The clustering is
> currently on the identity PK so is completely wasted - only benefit is
> that the fill factor can be set to 90 as allrows are currently added
> to the end of the table.
> I want to change the clustering index to help improve performance of
> queries. My initial thought is:
> (client_id, input_date). Does this seem remotely sensible? Should it
> be the other way round?
> Given that I would always expect client_id to be used in a query,
> would putting a non-clustered index on (billed) achieve anything? Or
> on (client_id, billed)?
> The suggested clustered index seems to work quite well and although
> not making much difference to CPU time seems to cut logical reads by
> about 40x. I think it will have more effect on Live which is much
> busier and much less powerful than the dev server (yes I know that's
> daft!)
> I think this will increase fragmentation on the table. Should I be
> worried about this - I intend to rebuild all indexes with a fillfactor
> of 70? My plan would be to follow the following:
> DROP all existing indexes and constraints
> Create new clustered index with fillfactor of 70
> recreate nonclustered indexes and constraints with fillfactor of 70
> Is it okay to build a clustered index on a populated table or is it
> better to create a new table, add clustered index and then populate,
> then rename?
> Many thanks.
> Cheers,
> James|||On Feb 27, 2:03=A0am, "jason" <jason-r3m...@.statisticsio.com> wrote:
> Jim,
> I have done similar(composite nonunique CI's for ranges) with good results=
> but it sounds like this is a big deal so I would test as much as possible.= I
> would also consider your NCI's during testing. Does adding a column or two=
> to an NCI cover more queries? 70% on the fillfactor sounds low to me if yo=u
> are just adding 5k rows\day. How often are you reindexing?
> --
> Jason Massie
> www:http://statisticsio.com
> rss:http://feeds.feedburner.com/statisticsio
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
>
> > Hi,
> > SQL Server 2000
> > I've got a table that doesn't change during the day but has inserts
> > overnight. currently 4 million rows in the table and I would say no
> > more than 5000 rows being added nightly. Db server is totally
> > overloaded and sometimes queries on this table take a long time.
> > Queries usually filter on a client_id and a input_date range. Input
> > date range is not normally very selective. There can be up to about
> > 100k rows per client_id. There are normally additional group by
> > clauses or further filters but they are not applied every time. Also
> > frequently used is a bit flag =A0called billed. billed =3D 0 is very
> > selective (0-5%), billed =3D 1 is not (>95%). The clustering is
> > currently on the identity PK so is completely wasted - only benefit is
> > that the fill factor can be set to 90 as allrows are currently added
> > to the end of the table.
> > I want to change the clustering index to help improve performance of
> > queries. My initial thought is:
> > (client_id, input_date). Does this seem remotely sensible? Should it
> > be the other way round?
> > Given that I would always expect client_id to be used in a query,
> > would putting a non-clustered index on (billed) achieve anything? Or
> > on (client_id, billed)?
> > The suggested clustered index seems to work quite well and although
> > not making much difference to CPU time seems to cut logical reads by
> > about 40x. I think it will have more effect on Live which is much
> > busier and much less powerful than the dev server (yes I know that's
> > daft!)
> > I think this will increase fragmentation on the table. Should I be
> > worried about this - I intend to rebuild all indexes with a fillfactor
> > of 70? My plan would be to follow the following:
> > DROP all existing indexes and constraints
> > Create new clustered index with fillfactor of 70
> > recreate nonclustered indexes and constraints with fillfactor of 70
> > Is it okay to build a clustered index on a populated table or is it
> > better to create a new table, add clustered index and then populate,
> > then rename?
> > Many thanks.
> > Cheers,
> > James- Hide quoted text -
> - Show quoted text -
Hi Jason,
I checked the maintenance plans and they get rebuilt every Sunday with
a fixed free space percentage of 10% - so fillfactor is irrelevant.
Anyway following on from your comments I'll specify fillfactor as 90.
I've had a play with the indexes and the suggested clustered index
works extremely well. The nonclustered one I suggested on billed or
client_id, billed is never actually used as it always favours the
clustered index seek with a WHERE clause on the billed column.
Thanks for your help.
Just one more question - the database is fully logged. Does it make
any difference to speed/transaction log size whether you recluster
the index in situ or in a new replacement table?
James|||If the database is in Simple or Bulk Logged mode the Creation and rebuilding
of the indexes can be minimally logged and faster as a result. Just be sure
to switch back to Full and take a full backup when done.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:64479e9a-191e-4445-b52d-4a9a89ef8a13@.i29g2000prf.googlegroups.com...
On Feb 27, 2:03 am, "jason" <jason-r3m...@.statisticsio.com> wrote:
> Jim,
> I have done similar(composite nonunique CI's for ranges) with good results
> but it sounds like this is a big deal so I would test as much as possible.
> I
> would also consider your NCI's during testing. Does adding a column or two
> to an NCI cover more queries? 70% on the fillfactor sounds low to me if
> you
> are just adding 5k rows\day. How often are you reindexing?
> --
> Jason Massie
> www:http://statisticsio.com
> rss:http://feeds.feedburner.com/statisticsio
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
>
> > Hi,
> > SQL Server 2000
> > I've got a table that doesn't change during the day but has inserts
> > overnight. currently 4 million rows in the table and I would say no
> > more than 5000 rows being added nightly. Db server is totally
> > overloaded and sometimes queries on this table take a long time.
> > Queries usually filter on a client_id and a input_date range. Input
> > date range is not normally very selective. There can be up to about
> > 100k rows per client_id. There are normally additional group by
> > clauses or further filters but they are not applied every time. Also
> > frequently used is a bit flag called billed. billed = 0 is very
> > selective (0-5%), billed = 1 is not (>95%). The clustering is
> > currently on the identity PK so is completely wasted - only benefit is
> > that the fill factor can be set to 90 as allrows are currently added
> > to the end of the table.
> > I want to change the clustering index to help improve performance of
> > queries. My initial thought is:
> > (client_id, input_date). Does this seem remotely sensible? Should it
> > be the other way round?
> > Given that I would always expect client_id to be used in a query,
> > would putting a non-clustered index on (billed) achieve anything? Or
> > on (client_id, billed)?
> > The suggested clustered index seems to work quite well and although
> > not making much difference to CPU time seems to cut logical reads by
> > about 40x. I think it will have more effect on Live which is much
> > busier and much less powerful than the dev server (yes I know that's
> > daft!)
> > I think this will increase fragmentation on the table. Should I be
> > worried about this - I intend to rebuild all indexes with a fillfactor
> > of 70? My plan would be to follow the following:
> > DROP all existing indexes and constraints
> > Create new clustered index with fillfactor of 70
> > recreate nonclustered indexes and constraints with fillfactor of 70
> > Is it okay to build a clustered index on a populated table or is it
> > better to create a new table, add clustered index and then populate,
> > then rename?
> > Many thanks.
> > Cheers,
> > James- Hide quoted text -
> - Show quoted text -
Hi Jason,
I checked the maintenance plans and they get rebuilt every Sunday with
a fixed free space percentage of 10% - so fillfactor is irrelevant.
Anyway following on from your comments I'll specify fillfactor as 90.
I've had a play with the indexes and the suggested clustered index
works extremely well. The nonclustered one I suggested on billed or
client_id, billed is never actually used as it always favours the
clustered index seek with a WHERE clause on the billed column.
Thanks for your help.
Just one more question - the database is fully logged. Does it make
any difference to speed/transaction log size whether you recluster
the index in situ or in a new replacement table?
James|||On Feb 27, 1:48=A0pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> If the database is in Simple or Bulk Logged mode the Creation and rebuildi=ng
> of the indexes can be minimally logged and faster as a result. Just be sur=e
> to switch back to Full and take a full backup when done.
> --
> Andrew J. Kelly =A0 =A0SQL MVP
> Solid Quality Mentors
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:64479e9a-191e-4445-b52d-4a9a89ef8a13@.i29g2000prf.googlegroups.com...
> On Feb 27, 2:03 am, "jason" <jason-r3m...@.statisticsio.com> wrote:
>
>
> > Jim,
> > I have done similar(composite nonunique CI's for ranges) with good resul=ts
> > but it sounds like this is a big deal so I would test as much as possibl=e.
> > I
> > would also consider your NCI's during testing. Does adding a column or t=wo
> > to an NCI cover more queries? 70% on the fillfactor sounds low to me if
> > you
> > are just adding 5k rows\day. How often are you reindexing?
> > --
> > Jason Massie
> > www:http://statisticsio.com
> > rss:http://feeds.feedburner.com/statisticsio
> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...=
> > > Hi,
> > > SQL Server 2000
> > > I've got a table that doesn't change during the day but has inserts
> > > overnight. currently 4 million rows in the table and I would say no
> > > more than 5000 rows being added nightly. Db server is totally
> > > overloaded and sometimes queries on this table take a long time.
> > > Queries usually filter on a client_id and a input_date range. Input
> > > date range is not normally very selective. There can be up to about
> > > 100k rows per client_id. There are normally additional group by
> > > clauses or further filters but they are not applied every time. Also
> > > frequently used is a bit flag called billed. billed =3D 0 is very
> > > selective (0-5%), billed =3D 1 is not (>95%). The clustering is
> > > currently on the identity PK so is completely wasted - only benefit is=
> > > that the fill factor can be set to 90 as allrows are currently added
> > > to the end of the table.
> > > I want to change the clustering index to help improve performance of
> > > queries. My initial thought is:
> > > (client_id, input_date). Does this seem remotely sensible? Should it
> > > be the other way round?
> > > Given that I would always expect client_id to be used in a query,
> > > would putting a non-clustered index on (billed) achieve anything? Or
> > > on (client_id, billed)?
> > > The suggested clustered index seems to work quite well and although
> > > not making much difference to CPU time seems to cut logical reads by
> > > about 40x. I think it will have more effect on Live which is much
> > > busier and much less powerful than the dev server (yes I know that's
> > > daft!)
> > > I think this will increase fragmentation on the table. Should I be
> > > worried about this - I intend to rebuild all indexes with a fillfactor=
> > > of 70? My plan would be to follow the following:
> > > DROP all existing indexes and constraints
> > > Create new clustered index with fillfactor of 70
> > > recreate nonclustered indexes and constraints with fillfactor of 70
> > > Is it okay to build a clustered index on a populated table or is it
> > > better to create a new table, add clustered index and then populate,
> > > then rename?
> > > Many thanks.
> > > Cheers,
> > > James- Hide quoted text -
> > - Show quoted text -
> Hi Jason,
> I checked the maintenance plans and they get rebuilt every Sunday with
> a fixed free space percentage of 10% - so fillfactor is irrelevant.
> Anyway following on from your comments I'll specify fillfactor as 90.
> I've had a play with the indexes and the suggested clustered index
> works extremely well. The nonclustered one I suggested on billed =A0or
> client_id, billed is never actually used as it always favours the
> clustered index seek with a WHERE clause on the billed column.
> Thanks for your help.
> Just one more question - the database is fully logged. Does it make
> any difference =A0to speed/transaction log size whether you recluster
> the index in situ or in a new replacement table?
> James- Hide quoted text -
> - Show quoted text -
Thanks. I'll do that.
What happens if a transaction log backup runs before the next full
backup? Will it fall over or just be corrupt? If it falls over, does
it bring the db down?
In other words, can I just leave the maintenance plans running knowing
that a couple of log backups will fail or do I need to either disable
them or do an immediate manual full backup? We're set up for half
hourly log backups. I'll probably make the change just after 6pm with
users off the system and the nightly full backup is at 9pm. Noone will
be using the db in the meantime.
James|||<<What happens if a transaction log backup runs before the next full
backup?>>
It depends on version and details. If the db is in simple mode when you execute the BACKUP LOG
command, you get an error message; regardless of version.
If db was in full and you did log backups and you then put it to simple and then backup to full and
you now do a log backup, then:
In 2000, the log backup is produced, with a lame message (not error) that it isn't usable.
In 2005, you get an error message.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"JimLad" <jamesdbirch@.yahoo.co.uk> wrote in message
news:19208e61-4e35-450e-8da2-d36208ad7cbe@.n75g2000hsh.googlegroups.com...
On Feb 27, 1:48 pm, "Andrew J. Kelly" <sqlmvpnooos...@.shadhawk.com>
wrote:
> If the database is in Simple or Bulk Logged mode the Creation and rebuilding
> of the indexes can be minimally logged and faster as a result. Just be sure
> to switch back to Full and take a full backup when done.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
> "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> news:64479e9a-191e-4445-b52d-4a9a89ef8a13@.i29g2000prf.googlegroups.com...
> On Feb 27, 2:03 am, "jason" <jason-r3m...@.statisticsio.com> wrote:
>
>
> > Jim,
> > I have done similar(composite nonunique CI's for ranges) with good results
> > but it sounds like this is a big deal so I would test as much as possible.
> > I
> > would also consider your NCI's during testing. Does adding a column or two
> > to an NCI cover more queries? 70% on the fillfactor sounds low to me if
> > you
> > are just adding 5k rows\day. How often are you reindexing?
> > --
> > Jason Massie
> > www:http://statisticsio.com
> > rss:http://feeds.feedburner.com/statisticsio
> > "JimLad" <jamesdbi...@.yahoo.co.uk> wrote in message
> >news:c894d0b7-bd3e-4ab0-a890-33b3fdf094e7@.p73g2000hsd.googlegroups.com...
> > > Hi,
> > > SQL Server 2000
> > > I've got a table that doesn't change during the day but has inserts
> > > overnight. currently 4 million rows in the table and I would say no
> > > more than 5000 rows being added nightly. Db server is totally
> > > overloaded and sometimes queries on this table take a long time.
> > > Queries usually filter on a client_id and a input_date range. Input
> > > date range is not normally very selective. There can be up to about
> > > 100k rows per client_id. There are normally additional group by
> > > clauses or further filters but they are not applied every time. Also
> > > frequently used is a bit flag called billed. billed = 0 is very
> > > selective (0-5%), billed = 1 is not (>95%). The clustering is
> > > currently on the identity PK so is completely wasted - only benefit is
> > > that the fill factor can be set to 90 as allrows are currently added
> > > to the end of the table.
> > > I want to change the clustering index to help improve performance of
> > > queries. My initial thought is:
> > > (client_id, input_date). Does this seem remotely sensible? Should it
> > > be the other way round?
> > > Given that I would always expect client_id to be used in a query,
> > > would putting a non-clustered index on (billed) achieve anything? Or
> > > on (client_id, billed)?
> > > The suggested clustered index seems to work quite well and although
> > > not making much difference to CPU time seems to cut logical reads by
> > > about 40x. I think it will have more effect on Live which is much
> > > busier and much less powerful than the dev server (yes I know that's
> > > daft!)
> > > I think this will increase fragmentation on the table. Should I be
> > > worried about this - I intend to rebuild all indexes with a fillfactor
> > > of 70? My plan would be to follow the following:
> > > DROP all existing indexes and constraints
> > > Create new clustered index with fillfactor of 70
> > > recreate nonclustered indexes and constraints with fillfactor of 70
> > > Is it okay to build a clustered index on a populated table or is it
> > > better to create a new table, add clustered index and then populate,
> > > then rename?
> > > Many thanks.
> > > Cheers,
> > > James- Hide quoted text -
> > - Show quoted text -
> Hi Jason,
> I checked the maintenance plans and they get rebuilt every Sunday with
> a fixed free space percentage of 10% - so fillfactor is irrelevant.
> Anyway following on from your comments I'll specify fillfactor as 90.
> I've had a play with the indexes and the suggested clustered index
> works extremely well. The nonclustered one I suggested on billed or
> client_id, billed is never actually used as it always favours the
> clustered index seek with a WHERE clause on the billed column.
> Thanks for your help.
> Just one more question - the database is fully logged. Does it make
> any difference to speed/transaction log size whether you recluster
> the index in situ or in a new replacement table?
> James- Hide quoted text -
> - Show quoted text -
Thanks. I'll do that.
What happens if a transaction log backup runs before the next full
backup? Will it fall over or just be corrupt? If it falls over, does
it bring the db down?
In other words, can I just leave the maintenance plans running knowing
that a couple of log backups will fail or do I need to either disable
them or do an immediate manual full backup? We're set up for half
hourly log backups. I'll probably make the change just after 6pm with
users off the system and the nightly full backup is at 9pm. Noone will
be using the db in the meantime.
James

Changing table relationships

We have a merge replication db that we have to drop a table index, add a
different index and change the relationships in one table. Will the laptops
pick up all of this or do I have to re-create the snapshot and also the
subscription? Thanks.
p.s. I assume I can't do this in EM?
David
for both SQL 2000 and SQL 2005 when you can't replicated these sorts of
schema changes.
I think you will have to drop the subscriptions and publications, make the
changes and try again.
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
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:uv$gS77xHHA.4300@.TK2MSFTNGP04.phx.gbl...
> We have a merge replication db that we have to drop a table index, add a
> different index and change the relationships in one table. Will the
> laptops pick up all of this or do I have to re-create the snapshot and
> also the subscription? Thanks.
> p.s. I assume I can't do this in EM?
> David
>
sql

Changing Non-Clustered Index to Clustered

Hi, I want to change a non-clustered index to a clustered index (on the PK).
But when I try doing this through EM, I get "Cannot convert a clustered
index to a nonclustered index using the DROP_EXISTING option". When I
remove the DROP EXISTING option, I still get an error. I should mention
that there a lot of other non clustered indexes on this table.
How can I change this to use a clustered index?
Thanks.Remove the foreign keys that refers to this table, drop the primary key,
re-create it as a non-clustered index and add back the foreign key. Sorry,
but those are the steps... :-(
Also, make sure no user is in the db as you will be without FK's for a
while.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"SQL" <nospam@.asdfadsf.com> wrote in message
news:e3TVU217DHA.1640@.TK2MSFTNGP11.phx.gbl...
> Hi, I want to change a non-clustered index to a clustered index (on the
PK).
> But when I try doing this through EM, I get "Cannot convert a clustered
> index to a nonclustered index using the DROP_EXISTING option". When I
> remove the DROP EXISTING option, I still get an error. I should mention
> that there a lot of other non clustered indexes on this table.
> How can I change this to use a clustered index?
> Thanks.
>|||Probably the simplest way is to do this in Enterprise Manager. You can
make the change and save it, or make the change and view/save the script
it produces. You can then see all the steps that are required in your
particular case.
HTH,
Gert-Jan
SQL wrote:
> Hi, I want to change a non-clustered index to a clustered index (on the PK).
> But when I try doing this through EM, I get "Cannot convert a clustered
> index to a nonclustered index using the DROP_EXISTING option". When I
> remove the DROP EXISTING option, I still get an error. I should mention
> that there a lot of other non clustered indexes on this table.
> How can I change this to use a clustered index?
> Thanks.

Changing Index Question

Hi All,
I'm trying to desgin a table that will reset the index depending on field
values. Example:
INDX Date1 NETID JunkField
-- -- -- --
1 05/05/05 USER1 1
2 05/05/05 USER1 1
3 05/05/05 USER1 1
1 05/05/05 USER2 1
1 05/06/05 USER2 1
Every time there is a new user NETID or the DATE1 values changes, the INDX
value should reset back to 1. Could someone provide me with a simple SQL
script. I'm new to this.
Thanks.Triggers may help you acheive this. Have a look at :-
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
lref.chm::/ts_create2_7eeq.htm
--
HTH
Ryan Waight, MCDBA, MCSE
"JackV" <vituja@.consumer.org> wrote in message
news:%23guTPiRgDHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I'm trying to desgin a table that will reset the index depending on field
> values. Example:
> INDX Date1 NETID JunkField
> -- -- -- --
> 1 05/05/05 USER1 1
> 2 05/05/05 USER1 1
> 3 05/05/05 USER1 1
> 1 05/05/05 USER2 1
> 1 05/06/05 USER2 1
> Every time there is a new user NETID or the DATE1 values changes, the INDX
> value should reset back to 1. Could someone provide me with a simple SQL
> script. I'm new to this.
> Thanks.
>|||Hi Ryan,
The link doesn't seem to work. Can you resend it. Thanks.
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:eO0gI9RgDHA.2292@.TK2MSFTNGP10.phx.gbl...
> Triggers may help you acheive this. Have a look at :-
>
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\tsq
> lref.chm::/ts_create2_7eeq.htm
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "JackV" <vituja@.consumer.org> wrote in message
> news:%23guTPiRgDHA.620@.TK2MSFTNGP11.phx.gbl...
> > Hi All,
> >
> > I'm trying to desgin a table that will reset the index depending on
field
> > values. Example:
> >
> > INDX Date1 NETID JunkField
> > -- -- -- --
> > 1 05/05/05 USER1 1
> > 2 05/05/05 USER1 1
> > 3 05/05/05 USER1 1
> > 1 05/05/05 USER2 1
> > 1 05/06/05 USER2 1
> >
> > Every time there is a new user NETID or the DATE1 values changes, the
INDX
> > value should reset back to 1. Could someone provide me with a simple
SQL
> > script. I'm new to this.
> >
> > Thanks.
> >
> >
>

Changing data type

Hi all,

I have to extend the length of a field, which appears in hundred of tables in a database, and maybe involved in constraints/index/primary key. It is difficult for me to drop all the constraints/index/primary key before altering each table one by one....Is there any easier way to drop all constraints and keys (instead of dropping them one by one)? Or any better (faster) way to extend the length of this field?

Thanks for any help here!OK, here's one method:

Script out your entire database. Then use search and replace to modify all instances of the field length (character, I assume?). Then use this script to create a new, corrected database, and use DTS to transfer data from the old database.

I hope you are aware, though, that you may likely render much of your SQL code (procedures, functions, triggers...) obsolete if they reference your field and attempt to assign it or concatenate it to variables that are of insufficient length. You may have quite a debugging job ahead of you. A 3rd party package such as ERWIN might be of assistance.

blindman|||Just generate ALTER TABLE statements:

SELECT 'ALTER TABLE ' + T.Name + ' ALTER COLUMN ' + C.Name + ' nVarChar(<YourNewFieldLength>);'
FROM SysColumns C INNER JOIN
SysObjects T ON T.id = C.id
WHERE T.XType = 'U' AND T.Name <> 'dtproperties' AND
C.Name = < YourColumnName >

You can consider to open a cursor, and to execute your statement dynamically. Alternatively, you can also generate a script.

I didn't check it for constraints, but indices are automatically updated by this statement.