Changing the size of a Varchar Field

We have a small table of about 13 million rows that needs altered. A column in the table needs to be changed from a varchar(20) to a varchar(500). When we ran the alter table script, 3 hrs later and it wasn't done running. Any suggestions on what we can do to speed up the process?

Thanks ahead of time
DMW

Edit:
We are running SQL Server 2000 and the db at the time was running in simple moodBulk copy it out, redefine the table without indexes (except clustered or primary key) or triggers, bulk copy back in with a batchsize set to keep the log from growing too large, reapply the indexes and repost the triggers.sql

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

Changing the identity seed & increment programatically

Hi,
I want to change the identity values of an identity column of a table
that already has rows in it, such that, it begins at zero and goes up
in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
usual 1, 2, 3, 4, etc...
The current values of the identity seed & identity increment are the
default values of "1" respectively.
I'm going to use the following commands. Let me know if you think
these commands will do the trick will you please?. The table name is
"A" for the sake of simplicity:-
Firstly change the identity increment value to 2
ALTER TABLE A
ALTER COLUMN identityColumn
IDENTITY (1, 2)
Then reseed all values so that odd become
DBCC CHECKIDENT('A', RESEED, 0)
Comments/corrections/suggestions much appreciated.
Al.--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||I think that the best option for you is to create a new table with the
IDENTITY seed value you desire, and copy the current table (without the
IDENTITY column) to the new table, then drop the old table and rename the
new table to the old name.
I don't think that DBCC CHECKIDENT() will renumber the existing data.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157033831.447625.273890@.b28g2000cwb.googlegroups.com...
> Hi,
>
> I want to change the identity values of an identity column of a table
> that already has rows in it, such that, it begins at zero and goes up
> in even numbers - that is, like 0, 2, 4, 6, 8, etc... instead of the
> usual 1, 2, 3, 4, etc...
> The current values of the identity seed & identity increment are the
> default values of "1" respectively.
> I'm going to use the following commands. Let me know if you think
> these commands will do the trick will you please?. The table name is
> "A" for the sake of simplicity:-
> Firstly change the identity increment value to 2
> ALTER TABLE A
> ALTER COLUMN identityColumn
> IDENTITY (1, 2)
> Then reseed all values so that odd become
> DBCC CHECKIDENT('A', RESEED, 0)
>
> Comments/corrections/suggestions much appreciated.
> Al.
>|||Hi Arnie,
Thanks for that but do you know how I would renumber the values
from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
Puzzled,
Al.|||On the new table, set the IDENTITY seed and increment values as you wish
before you copy the old table values.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<almurph@.altavista.com> wrote in message
news:1157046915.027819.46040@.b28g2000cwb.googlegroups.com...
> Hi Arnie,
> Thanks for that but do you know how I would renumber the values
> from 1, 2, 3, 4, to the 1, 3, 5, 7 sequence?
> Puzzled,
> Al.
>

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 records in tables

Hi,

I was just wondering... Is there any built-in function in MS SQL, which will
allow me to do rows' "capitalisation"?
Lets say that in database.table.name I've got:
"FOO BAR LTD.", which I want to change to "Foo Bar Ltd."
"Foo bar LTD.", which I want to be "Foo Bar Ltd.".
Is there any way of doing this or do I have to read it from database, change
in some script, and then insert it back into the table?

Hope it's all clear ;-)

Thank you,
MartinMartin Lukasik (marcin@.milea.pl.i.hate.this.spam) writes:

Quote:

Originally Posted by

I was just wondering... Is there any built-in function in MS SQL, which
will allow me to do rows' "capitalisation"?
Lets say that in database.table.name I've got:
"FOO BAR LTD.", which I want to change to "Foo Bar Ltd."
"Foo bar LTD.", which I want to be "Foo Bar Ltd.".
Is there any way of doing this or do I have to read it from database,
change in some script, and then insert it back into the table?

No, there is no built in for this. You could write your own scalar
UDF for this.

--
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|||Hi There,

Search for "Camel Case" in Google

With Warm Regards
Jatinder Singh
http://jatindersingh.blogspot.com

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegroups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>

changing nullability- any other options besides alter

I have a table with over 200 million rows. In this table is a column that
is currently defined as " DATETIME NULL". There are no NULL values for any
row in the table and I need to convert this column from NULL to NOT NULL.
I am executing the following command
Alter MyTable Alter Column MyColumn datetime NOT NULL
The query runs for over 3.5 hours before it finally exhausts all the disk
space on the drive containing the transaction log. The t-log grows to a
size of 140GB.
Just want to make sure that I am not missing anything here... is there an
easier way to do this?
I am considering running a "select into" a new table, and then dropping the
old table if I need to. I am just hoping that there is some simpler
solution that I am overlooking.
Any help would be greatly appreciated. Thanks.
TJT ,
use the WITH NOCHECK option in your alter statement. This way
existing records won't be checked until you do an update of the record.
Mark
|||Hi Mark
NOCHECK cannot be used when altering a column like this. It can only be
used when adding new constraints.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"MarkusB" <m.bohse@.quest-consultants.com> wrote in message
news:1159197893.908319.144320@.i3g2000cwc.googlegro ups.com...
> TJT ,
> use the WITH NOCHECK option in your alter statement. This way
> existing records won't be checked until you do an update of the record.
> Mark
>