Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-December
This is a transactional replication (immediate updating).
|||This is a transactional replication (immediate updating).
|||december wrote:

> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com
|||I'm using SQL Server 2000
amish wrote:
> december wrote:
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com
|||december wrote:
[vbcol=seagreen]
> I'm using SQL Server 2000
> amish wrote:
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.com

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-DecemberThis is a transactional replication (immediate updating).|||This is a transactional replication (immediate updating).|||december wrote:

> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com|||I'm using SQL Server 2000
amish wrote:
> december wrote:
>
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com|||december wrote:
[vbcol=seagreen]
> I'm using SQL Server 2000
> amish wrote:
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.comsql

Changing the table schema in Subscriber (only)?

Hi,
I have to alter a column on a replicated table and because I cannot
have any downtime, I'm thinking about just alter a column in Subscriber
only. I tested out in the test environment if I could just alter a
column in subscriber only and it worked. Now i'm wondering if this way
is ok or not.
Thanks in advance
-DecemberThis is a transactional replication (immediate updating).|||This is a transactional replication (immediate updating).|||december wrote:
> This is a transactional replication (immediate updating).
which version of SQL Server you are using ?
SQL Server 2000 or 2005?
If you have SQL Server 2005 then alter table will work for most
changes.
Regards
Amish Shah
http://shahamishm.tripod.com|||I'm using SQL Server 2000
amish wrote:
> december wrote:
> > This is a transactional replication (immediate updating).
> which version of SQL Server you are using ?
> SQL Server 2000 or 2005?
> If you have SQL Server 2005 then alter table will work for most
> changes.
> Regards
> Amish Shah
> http://shahamishm.tripod.com|||december wrote:
> I'm using SQL Server 2000
> amish wrote:
> > december wrote:
> >
> > > This is a transactional replication (immediate updating).
> > which version of SQL Server you are using ?
> > SQL Server 2000 or 2005?
> >
> > If you have SQL Server 2005 then alter table will work for most
> > changes.
> >
> > Regards
> > Amish Shah
> > http://shahamishm.tripod.com
You can use sp_repladdcolumn or sp_repldropcolumn in SQL Server 2000.
If you are changing only at subscriber you can not get its effect on
your publisher.
Regards
Amish Shah
http://shahamishm.tripod.com

Changing table column size

I'm new to mssql.

I need to change the size of a column of a table from char(255) to char(500). I used the line:

alter table table_name alter column column_name char(500)

When I run that command, I get a message that it was sucessful. However, when I try to enter data into the changed column, the number of characters I can enter is still 255. I check the information schema for the column and the 'character_maximum_length' field is 500.

What is the problem here? Is the maximum allowable length for char 255? How can I get a column to have 500 characters?Really?

USE Northwind
GO

CREATE TABLE myTable99(Col1 char(250))
GO

INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',250) UNION ALL
SELECT REPLICATE('x',25) UNION ALL
SELECT REPLICATE('x',2)
GO

SELECT LEN(Col1) FROM myTable99
GO

-- Will Fail
INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',500)
GO

ALTER TABLE myTable99 ALTER COLUMN Col1 char(500)
GO

INSERT INTO myTable99(Col1)
SELECT REPLICATE('x',500)
GO

SELECT LEN(Col1) FROM myTable99
GO

DROP TABLE myTable99
GO|||xiphias, you're probably testing the number of characters inserted by doing SELECT col_name FROM table. At this point you see only 255 characters, right? Check your QA settings (Tools/Options/Results/Maximum characters per column)|||Haha. That was the problem. I changed the settings and now I can see all the characters.

Now I try to do queries with the database with PHP and the number of characters return from the query is 255 again. Is there some setting file that I have to change in PHP?|||Your's is an interface problem...has nothing to do with sql server...

What's PHP?|||Sorry, I didn't know what was wrong..

PHP is a programming language for dynamic web pages. I guess I'll have to ask that in a different forum.

Thanks for the help.|||True, it seems you need to control from PHP point of view also.sql

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guy
Using ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy
|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:

> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guyUsing ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:

> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing SQL Server Collation

Hi
Has anyone successfully changed the Collation type for a single user
database without having to use Rebuildm.exe and/or the ALTER DATABASE
statement as I`ve tried the latter and if there are CK`s you cannot.
Thanks in advance.
Sad guyUsing ALTER DATABASE only changes the default collation for that database.
To fully change the collation you'd have to alter every column, every
character-based user-defined type, and possibly other objects (I can't
remember offhand). The easiest method I've come up with for changing the
collation is:
A) Script the entire database using Enterprise Manager
B) Edit the script and change all of the collation designators to whatever
you want
C) Create a new database using the script
D) Use DTS to transfer the data out of the old database and into the new
database
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"ANON" <ANON@.discussions.microsoft.com> wrote in message
news:EFB1A3D2-6E98-4C81-82D1-2FF10EAC7BBF@.microsoft.com...
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy|||Hi
Once a tables are created in a DB, ALTER DATABASE does not change those
column's collations.
What are you trying to do?
Regards
Mike
"ANON" wrote:
> Hi
> Has anyone successfully changed the Collation type for a single user
> database without having to use Rebuildm.exe and/or the ALTER DATABASE
> statement as I`ve tried the latter and if there are CK`s you cannot.
> Thanks in advance.
> Sad guy

Changing schemas already in an xml schema collection

One more question for the day.
I tried to alter the schema collection but only see the "ADD" keyword, which
then gives error about duplicate of same namespace. Seems like their needs
to be a "REPLACE" keyword instead of the ADD?
I also noticed that when you want to change a schema in the collection, you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection
Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?
I guess it would be obvious that you could not remove an XML element if that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.
Thanks in advance,
Neal Walters
http://Biztalk-Training.comHi Neal,
Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.
Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> One more question for the day.
> I tried to alter the schema collection but only see the "ADD" keyword,
> which
> then gives error about duplicate of same namespace. Seems like their
> needs
> to be a "REPLACE" keyword instead of the ADD?
> I also noticed that when you want to change a schema in the collection,
> you
> have apparently have to do the following:
> 1) alter table/alter column to remove schema collection from column
> 2) drop schema collection
> 3) create schema collection
> 4) alter table/atler column to re-add schema collection
> Any dangers in this procedure? Do you mess up the indexes or any
> performance problems when you want to change a schema in a collection?
> I guess it would be obvious that you could not remove an XML element if
> that
> element is actually stored in the database, but i could see quite often
> adding new elements to the schema.
> Thanks in advance,
> Neal Walters
> http://Biztalk-Training.com
>|||Bob, Thanks for all your responses!
Neal

Changing schemas already in an xml schema collection

One more question for the day.
I tried to alter the schema collection but only see the "ADD" keyword, which
then gives error about duplicate of same namespace. Seems like their needs
to be a "REPLACE" keyword instead of the ADD?
I also noticed that when you want to change a schema in the collection, you
have apparently have to do the following:
1) alter table/alter column to remove schema collection from column
2) drop schema collection
3) create schema collection
4) alter table/atler column to re-add schema collection
Any dangers in this procedure? Do you mess up the indexes or any
performance problems when you want to change a schema in a collection?
I guess it would be obvious that you could not remove an XML element if that
element is actually stored in the database, but i could see quite often
adding new elements to the schema.
Thanks in advance,
Neal Walters
http://Biztalk-Training.com
Hi Neal,
Yes, you're right. In SQL Server 2005 there is no replace or delete a schema
from a schema collection. It's popular as a "sought-after for next release"
feature. You may be able to add definitions to an existing schema in a
collection (using ALTER...ADD) as long as they don't redefine existing
types.
Your process seems right, and I'd drop/rebuild the indexes when I dropped
the schema collection from the column/and added the schema collection back
to the column. The documents in the XML column are re-validated when you
alter the table to add the schema collection back, this may take some time,
depending on how many rows you have.
Cheers,
Bob Beauchemin
http://www.SQLskills.com/blogs/bobb
"Neal Walters" <NealWalters@.discussions.microsoft.com> wrote in message
news:ACA61EC4-D430-4E81-B4ED-021CD2A8ABB5@.microsoft.com...
> One more question for the day.
> I tried to alter the schema collection but only see the "ADD" keyword,
> which
> then gives error about duplicate of same namespace. Seems like their
> needs
> to be a "REPLACE" keyword instead of the ADD?
> I also noticed that when you want to change a schema in the collection,
> you
> have apparently have to do the following:
> 1) alter table/alter column to remove schema collection from column
> 2) drop schema collection
> 3) create schema collection
> 4) alter table/atler column to re-add schema collection
> Any dangers in this procedure? Do you mess up the indexes or any
> performance problems when you want to change a schema in a collection?
> I guess it would be obvious that you could not remove an XML element if
> that
> element is actually stored in the database, but i could see quite often
> adding new elements to the schema.
> Thanks in advance,
> Neal Walters
> http://Biztalk-Training.com
>
|||Bob, Thanks for all your responses!
Neal

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
>

Changing NULL, to NOT NULL

SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.You drop statistics in the same way that you drop indexes. Typically, you
don't have both statistics and indexes on the same column - except, say, if
you have an index on a single column and statistics on a pair of columns,
and vice-versa. In your case, it looks like the Database Tuning Advisor
recommended the stats and the DBA running the DTA allowed them to be built.
You'll have to run:
DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
SQL Server 2005
When I run the command:
alter table FileRepository alter column parentID int not null
I get the message:
Msg 5074, Level 16, State 1, Line 1
The index 'IX_FileRepository' is dependent on column 'parentID'.
Msg 5074, Level 16, State 1, Line 1
The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
'parentID'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
this column.
No problem on the index as that is easy to drop and recreate. But what is
this stuff about statistics and how do I deal with it?
FWIW, I know just enough about 2000 to do my job, but diddle about 2005.|||Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say, if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>|||This is a multi-part message in MIME format.
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
Well, that's the issue. If you have a lot of data, it will take a long
time. Your log may fill up during that time. Ultimately, you may need to
create a new table, with the intended DDL. Then, you move the data in
chunks to the other table. Meanwhile, you create a partitioned view (with
the same name as the old table) that includes both tables. However, you
will need to add INSTEAD OF triggers for any INSERT/UPDATE/DELETE activity
that will occur during that time. Once all data have been moved, you can
drop the original table and view, renaming the new table.
The attached code has an example.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"JayKon" <JayKon@.discussions.microsoft.com> wrote in message
news:3E756320-3BA2-46CA-A2E6-9DBD404D83B4@.microsoft.com...
Thank you sir.
After having a further conversation, it seems they have tried the alter
already and it ran for several hours before timing out ( a very large
table).
This implies to me that it is doing a table rebuild, even though all data in
that column is already NOT NULL and only the DDL needs to be changed.
Am I probably right here? Can we make this type of change without it taking
more than a few seconds to do the alter?
"Tom Moreau" wrote:
> You drop statistics in the same way that you drop indexes. Typically, you
> don't have both statistics and indexes on the same column - except, say,
> if
> you have an index on a single column and statistics on a pair of columns,
> and vice-versa. In your case, it looks like the Database Tuning Advisor
> recommended the stats and the DBA running the DTA allowed them to be
> built.
> You'll have to run:
> DROP STATISTICS FileRepository ._dta_stat_191339746_1_12_11_2
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "JayKon" <JayKon@.discussions.microsoft.com> wrote in message
> news:C172210B-9AF9-438D-AB31-58B55DFC5367@.microsoft.com...
> SQL Server 2005
> When I run the command:
> alter table FileRepository alter column parentID int not null
> I get the message:
> Msg 5074, Level 16, State 1, Line 1
> The index 'IX_FileRepository' is dependent on column 'parentID'.
> Msg 5074, Level 16, State 1, Line 1
> The statistics '_dta_stat_191339746_1_12_11_2' is dependent on column
> 'parentID'.
> Msg 4922, Level 16, State 9, Line 1
> ALTER TABLE ALTER COLUMN parentID failed because one or more objects
> access
> this column.
>
> No problem on the index as that is easy to drop and recreate. But what is
> this stuff about statistics and how do I deal with it?
> FWIW, I know just enough about 2000 to do my job, but diddle about 2005.
>
--=_NextPart_000_0A6F_01C77871.851AEB80
Content-Type: text/plain;
name="Alter.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
filename="Alter.sql"
-- Listing 1
create table dbo.t1
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
)
create clustered index C_t1 on dbo.t1 (dt)
create table dbo.t2
(
id int primary key nonclustered
, dt datetime not null
, junk varchar (20) not null
, other1 bit not null
default (0)
, other2 varchar (30) null
)
create clustered index C_t2 on dbo.t2 (dt)
create view dbo.t
as
select
id
, dt
, junk
from
dbo.t2
union all
select
id
, dt
, junk
from
dbo.t1
go
-- Listing 2
create table dbo.threshold
(
threshold datetime not null
)
insert dbo.threshold values ('1995-01-01')
go
-- Listing 3
create trigger tri_t on dbo.t instead of insert
as
insert dbo.t1 (id, dt, junk)
select id, dt, junk
from inserted
where dt >= (select threshold from dbo.threshold)
insert dbo.t2 (id, dt, junk)
select id, dt, junk
from inserted
where dt < (select threshold from dbo.threshold)
go
-- Listing 4
create trigger tru_t on dbo.t instead of update
as
if (update (dt))
begin
-- no crossing of threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt < z.threshold
update x
set
dt = i.dt
, junk = i.junk
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt >= z.threshold
-- threshold crossed
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t2 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
delete x
from
inserted i
join deleted d on d.id = i.id
join dbo.t1 x on x.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
insert dbo.t1 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt >= z.threshold
and d.dt < z.threshold
insert dbo.t2 (id, dt, junk)
select i.id, i.dt, i.junk
from
inserted i
join deleted d on d.id = i.id
cross join
dbo.threshold z
where
i.dt < z.threshold
and d.dt >= z.threshold
end
else
begin
update x
set
junk = i.junk
from
inserted i
join dbo.t1 x on x.id = i.id
where
i.dt >= (select threshold from dbo.threshold)
update x
set
junk = i.junk
from
inserted i
join dbo.t2 x on x.id = i.id
where
i.dt < (select threshold from dbo.threshold)
end
go
-- Listing 5
create trigger tru_t on dbo.t instead of delete
as
if @.@.ROWCOUNT = 0
return
delete x
from
deleted d
join dbo.t1 x on x.id = d.id
delete x
from
deleted d
join dbo.t2 x on x.id = d.id
go
-- populate the table
declare
@.year smallint
, @.month tinyint
, @.day tinyint
, @.id int
, @.date datetime
, @.icount int
, @.max int
select
@.year = 1995
, @.id = 1
while @.year < 2004
begin
set @.month = 1
while @.month <= 12
begin
set @.day = 1
while @.day <= case when @.month in (1, 3, 5, 7, 8, 10, 12) then 31
when @.month in (4, 6, 9, 11) then 30
when @.year % 4 = 0 then 29
else 28 end
begin
set @.date = cast (@.year as char (4))
+ replace (str (@.month, 2), ' ', '0')
+ replace (str (@.day, 2), ' ', '0')
select
@.max = rand () * 1000
, @.icount = 1
while @.icount <= @.max
begin
insert t1 (id, dt, junk)
values (@.id, @.date, replicate ('X', 20))
select
@.icount = @.icount + 1
, @.id = @.id + 1
end
set @.day = @.day + 1
end
set @.month = @.month + 1
end
set @.year = @.year + 1
end
go
-- Listing 6
-- migrate the data
declare
@.date datetime
, @.str varchar (8000)
select
@.date = min (dt)
from
t1
while @.date is not null and not datepart (hh, getdate()) between 8 and 18
begin
begin tran
update threshold
set
threshold = dateadd (dd, 1, @.date)
insert t2 (id, dt, junk)
select id, dt, junk
from t1
where dt = @.date
delete t1
where dt = @.date
commit tran
select
@.date = min (dt)
from
t1
end
/*
drop view t
drop table t1
drop table t2
drop table threshold
*/
--=_NextPart_000_0A6F_01C77871.851AEB80--|||> After having a further conversation, it seems they have tried the alter
> already and it ran for several hours before timing out ( a very large
> table).
> This implies to me that it is doing a table rebuild, even though all data
> in
> that column is already NOT NULL and only the DDL needs to be changed.
> Am I probably right here?
That depends, did they open Enterprise Manager and click around in the GUI
to make the change? By default, in most cases, when you click OK Enterprise
Manager will do exactly that behind your back: create a new table, move the
data over, drop the old table, rename the new table to the old name, etc.
No exactly pretty.
You should probably wait to attempt this during a maintenance window (e.g.
on a Sunday at midnight), and even enforce it with setting the database to
SINGLE_USER, and then you won't have to worry about blocking users, or being
blocked by users, or being slowed down by other activity in the database.
If it still takes too long, you may have to use Tom's solution, which is a
bit more complex but is certainly better than what Enterprise Manager tries
to do.|||> You should probably wait to attempt this
And by "this" I mean an ALTER statement in Query Analyzer, not clicking OK
to the change in Enterprise Manager.|||Yes, they tried to do it through the GUI.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>|||At present, I'm on a dev server (our only 2005 box).
As it happens, the ALTER statement does see that it's a simple DDL change
and took a couple of seconds.
I'm releived that SQL Server is smart enough to do that.
Thank you very much.
"Aaron Bertrand [SQL Server MVP]" wrote:
> > After having a further conversation, it seems they have tried the alter
> > already and it ran for several hours before timing out ( a very large
> > table).
> > This implies to me that it is doing a table rebuild, even though all data
> > in
> > that column is already NOT NULL and only the DDL needs to be changed.
> >
> > Am I probably right here?
> That depends, did they open Enterprise Manager and click around in the GUI
> to make the change? By default, in most cases, when you click OK Enterprise
> Manager will do exactly that behind your back: create a new table, move the
> data over, drop the old table, rename the new table to the old name, etc.
> No exactly pretty.
> You should probably wait to attempt this during a maintenance window (e.g.
> on a Sunday at midnight), and even enforce it with setting the database to
> SINGLE_USER, and then you won't have to worry about blocking users, or being
> blocked by users, or being slowed down by other activity in the database.
> If it still takes too long, you may have to use Tom's solution, which is a
> bit more complex but is certainly better than what Enterprise Manager tries
> to do.
>
>

Changing Field name of an existing Table

Hi,
Is it possible to change any fieldname of an existing table?I mean to say
by TSQL statement.We know that we can alter the data type and width etc.
But I haven't got any info about filedname change.So if it is possible
Please help...
And Is there any TSQL command to alter multiple columns in a single statement?

Please help...
Thanks!!
Joydeepif it is not a primary or a foriegn key you can do something like so...

ALTER TABLE Mytable
ADD INQUIRYID2 BIGINT
GO
UPDATE MyTable SET INQUIRYID2 = INQUIRYID
GO
ALTER TABLE MyTable
DROP COLUMN INQUIRYID

But this a bad idea. What about the existing database object that reference the existing column. Oh well too late. A thousand application buga have just been created. Update your resume. You want biggie size|||if you can connect the db thru EM, just type the new name....|||if you can connect the db thru EM, just type the new name....

The Holy Book says ...

sp_rename

Changes the name of a user-created object (for example, table, column, or user-defineddata type) in the current database.
Syntax

sp_rename [ @.objname = ] 'object_name' ,
[ @.newname = ] 'new_name'
[ , [ @.objtype = ] 'object_type' ]
Arguments

[@.objname =] 'object_name'
Is the current name of the user object (table, view, column, stored procedure, trigger, default, database, object, or rule) or data type. If the object to be renamed is a column in a table, object_name must be in the form table.column. If the object to be renamed is an index, object_name must be in the form table.index. object_name is nvarchar(776), with no default.
[@.newname =] 'new_name'
Is the new name for the specified object. new_name must be a one-part name and must follow the rules for identifiers. newname is sysname, with no default.
[@.objtype =] 'object_type'
Is the type of object being renamed. object_type is varchar(13), with a default of NULL, and can be one of these values.
ValueDescriptionCOLUMNA column to be renamed.DATABASEA user-defined database. This option is required when renaming a database.INDEXA user-defined index.OBJECTAn item of a type tracked in sysobjects. For example, OBJECT could be used to rename objects including constraints (CHECK, FOREIGN KEY, PRIMARY/UNIQUE KEY), user tables, views, stored procedures, triggers, and rules.USERDATATYPEA user-defined data type added by executing sp_addtype.


I don't know why you are providing a round about way of doing the same thing.|||You can try out this one.

EXECUTE sp_rename N'MyTable.MyCurrFName', N'MyNewFName', 'COLUMN'

MyTable has a Field MyCurrFName. It will be renamed to MyNewFName|||You are better off either a). Create a view that has the name you want, or b). unloading, dropping, create, and load

There was a very good article that discusses why do the alter causes sql server to waste a lot of space on the data pages...Now if I can find on Nigel's web site I'll post a link|||Here's the link

http://www.mindsdoor.net/SQLAdmin/AlterTableProblems.html|||thanks Brett, thats really a good link. never thought of it.

changing default values

How can i change the default value of a column? I already have a column
named DateOfRental but I want to alter it so that it has default value
getdate()

Thanks
David

--
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious"David Wright" <David@.prodigiousuk.com> wrote in message
news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> How can i change the default value of a column? I already have a column
> named DateOfRental but I want to alter it so that it has default value
> getdate()
> Thanks
> David
> --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious

1. Use sp_help to get the current name of the constraint

exec sp_help MyTable

2. Drop the constraint

alter table MyTable drop constraint <Constraint name goes here
3. Add a new constraint

alter table t1 add constraint DFT_DateOfRental default getdate() for
DateOfRental

Simon|||Will try that, thanks Simon

David

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3ffda2ef$1_1@.news.bluewin.ch...
> "David Wright" <David@.prodigiousuk.com> wrote in message
> news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> > How can i change the default value of a column? I already have a column
> > named DateOfRental but I want to alter it so that it has default value
> > getdate()
> > Thanks
> > David
> > --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
> 1. Use sp_help to get the current name of the constraint
> exec sp_help MyTable
> 2. Drop the constraint
> alter table MyTable drop constraint <Constraint name goes here>
> 3. Add a new constraint
> alter table t1 add constraint DFT_DateOfRental default getdate() for
> DateOfRental
> Simon|||How can i drop a constraint i never made though? its not in the list when i
try the SP_help :(

"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3ffda2ef$1_1@.news.bluewin.ch...
> "David Wright" <David@.prodigiousuk.com> wrote in message
> news:btjtsg$dbi$1@.news8.svr.pol.co.uk...
> > How can i change the default value of a column? I already have a column
> > named DateOfRental but I want to alter it so that it has default value
> > getdate()
> > Thanks
> > David
> > --
http://www.nintendo-europe.com/NOE/...=l&a=Prodigious
> 1. Use sp_help to get the current name of the constraint
> exec sp_help MyTable
> 2. Drop the constraint
> alter table MyTable drop constraint <Constraint name goes here>
> 3. Add a new constraint
> alter table t1 add constraint DFT_DateOfRental default getdate() for
> DateOfRental
> Simon|||David Wright (David@.prodigiousuk.com) writes:
> How can i drop a constraint i never made though? its not in the list
> when i try the SP_help :(

If there is no constraint, you should not have to drop it.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp