Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary k
ey
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just t
o
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...[vbcol=seagreen]
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.
Showing posts with label alter. Show all posts
Showing posts with label alter. Show all posts
Tuesday, March 27, 2012
altering unique index to primary key
Is there a way to alter a unique clustered index in a table to a primary key
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary key
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just to
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>> Is there a way to alter a unique clustered index in a table to a primary
>> key
>> with some magic alter statement?
>> What I want to avoid (if possible) is to run drop/create statement, just
>> to
>> make already unique clustered index to a Primary key.
>> I appreciate your reply. I have sql server 2000 SP4.
>>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.
with some magic alter statement?
What I want to avoid (if possible) is to run drop/create statement, just to
make already unique clustered index to a Primary key.
I appreciate your reply. I have sql server 2000 SP4.Hi James
I don't think this possible with command. Why do you want to change this?
John
"James" wrote:
> Is there a way to alter a unique clustered index in a table to a primary key
> with some magic alter statement?
> What I want to avoid (if possible) is to run drop/create statement, just to
> make already unique clustered index to a Primary key.
> I appreciate your reply. I have sql server 2000 SP4.
>
>|||I wanted to replicate these tables via Transactional replication and it
requires a Primary key. Since the tables are big, I wanted to save some time
if that was possible.
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:F688407C-5A69-4B1E-B0E7-76100DE23F5E@.microsoft.com...
> Hi James
> I don't think this possible with command. Why do you want to change this?
> John
> "James" wrote:
>> Is there a way to alter a unique clustered index in a table to a primary
>> key
>> with some magic alter statement?
>> What I want to avoid (if possible) is to run drop/create statement, just
>> to
>> make already unique clustered index to a Primary key.
>> I appreciate your reply. I have sql server 2000 SP4.
>>|||Hi James,
> I wanted to replicate these tables via Transactional replication and it
> requires a Primary key.
>
Are you saying you created the tables without a primary key? Is that
something you regularly do?
Ruud de Koter.
Altering the identity seed of a table
Hi,
Im trying to alter the identity seed of a table in a script and I cant work out how to do so without doing it the way Enterprise Manager does it - ie create a tmp table with the new id, populate it with data and set constraints etc, then drop the original table and rename the tmp one.
This is pretty hard to script for arbitrary tables automatically, so I was wondering if there is some way to do it with an ALTER TABLE script?
cheers
Pete StoreyDBCC Checkident.|||Thanks!
Im trying to alter the identity seed of a table in a script and I cant work out how to do so without doing it the way Enterprise Manager does it - ie create a tmp table with the new id, populate it with data and set constraints etc, then drop the original table and rename the tmp one.
This is pretty hard to script for arbitrary tables automatically, so I was wondering if there is some way to do it with an ALTER TABLE script?
cheers
Pete StoreyDBCC Checkident.|||Thanks!
altering table with default value
Hi, How to alter a table with default value?
I am using the below statement, But, it is not working..Any pointers?
Thx..
----------
alter table action_item ALTER COLUMN STATUS default 0ALTER TABLE ACTION_ITEM ADD CONSTRAINT
DF_ACTION_ITEM_STATUS DEFAULT 0 FOR STATUS
GO
UPDATE ACTION_ITEM SET STATUS =0 where STATUS IS NULL
GO
I am using the below statement, But, it is not working..Any pointers?
Thx..
----------
alter table action_item ALTER COLUMN STATUS default 0ALTER TABLE ACTION_ITEM ADD CONSTRAINT
DF_ACTION_ITEM_STATUS DEFAULT 0 FOR STATUS
GO
UPDATE ACTION_ITEM SET STATUS =0 where STATUS IS NULL
GO
Altering stored procedure which is part of sys schema
I am trying to alter sys.sp_helpmergeconflictrows which is part fof sys schema and is in System Stored Procedures.
Reason why I need this is because Conglict Viewer in merge replication fails to show data from one of my tables, because aforementioned sp fails during execution. It fails because sql query is declared as nvarchar(4000) and it needs to be longer. So, I tired to change it to nvarchar(max), but I cannot.
I tried few things in order to gain permission to alter that sp, but I fial always.
Can it be done at all, and if can, how?
Thanks
If you have a bug in the conflict viewer (or more specifically this stored proc), you should open a ticket with MS Tech Support to have this resolved.
Bryan
sqlAltering multiple objects schema
Hi,
I need to change the schema of the stored procedures of several databases.
Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?
thank you
Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)
-Raul Garcia
SDE/T
SQL Server Engine
|||You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help
declare @.querystring nvarchar(MAX)
set @.querystring=''
select @.querystring=@.querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures
exec(@.querystring)
Either way, you will have to use dynamic sql.
Altering Identity Columns for Bidirectional TransasctionalReplication
Having 300 tables (all of them with identity columns) with
prepopulated data, I've come to a point where I have to alter all the
tables to have an identity conflict resolution system. In my case I
only have a publisher and a subscriber, hence I've opted to have the
publisher generate only even identities, and the subscriber will be
generating odd values.
I can't seem to find, besides using Enterprise Management (Design
Table Form), any other way to alter the existing identity step already
defined to the table.
Any ideas on how about to either alter the table (preferibly using
SQL), or perhaps a different approach to get the existing tables using
the new identity step (I would rather not have to export the data,
drop the tables, recreate them again, and reload the data once more)
Thank you,
James.
Here is how I ended up doing:
- Backup the original Database containing all the data:
- Create a new database - name it backup_db
- Destroy the orginal database and recreate the database only.
- Take the scripts that created the original database (if none is
available then export the scripts from the backup_db)
- Edit the script and alter all the IDENTITY(1,1) to the desired
seed and step.
- In the original db (now empty) create only the tables - don't
worry about the views, procedures and any other objects.
- Create a snapshot replication using the backup_db as the
publishing database: ensure that the published articles are marked to
only "Delete all the data in the existing table" - this will ensure
that the subscriber's tables are not destroyed (hence keeping your new
identity definition"
- Subscribe the original db to the publication and sync it.
- Once the replication has completed we now need to update all the
tables with an identity to reseed with the latest highest value - you
can do this by defining the following script (in my case, I need the
seed to be even):
BEGIN
DECLARE @.new_ident as int
SET @.new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
IF (@.new_ident % 2) <> 0
SET @.new_ident = @.new_ident + 1
DBCC CHECKIDENT('ACCOUNT', RESEED, @.new_ident)
END
|||Small errata:
> BEGIN
DBCC CHECKIDENT('your_table', RESEED)
> DECLARE @.new_ident as int
> SET @.new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
> IF (@.new_ident % 2) <> 0
> SET @.new_ident = @.new_ident + 1
> DBCC CHECKIDENT('ACCOUNT', RESEED, @.new_ident)
> END
Also, in order to generate all the scripts for each table you could
use a mail merge program with a list of your table names - this should
save time and user errors.
prepopulated data, I've come to a point where I have to alter all the
tables to have an identity conflict resolution system. In my case I
only have a publisher and a subscriber, hence I've opted to have the
publisher generate only even identities, and the subscriber will be
generating odd values.
I can't seem to find, besides using Enterprise Management (Design
Table Form), any other way to alter the existing identity step already
defined to the table.
Any ideas on how about to either alter the table (preferibly using
SQL), or perhaps a different approach to get the existing tables using
the new identity step (I would rather not have to export the data,
drop the tables, recreate them again, and reload the data once more)
Thank you,
James.
Here is how I ended up doing:
- Backup the original Database containing all the data:
- Create a new database - name it backup_db
- Destroy the orginal database and recreate the database only.
- Take the scripts that created the original database (if none is
available then export the scripts from the backup_db)
- Edit the script and alter all the IDENTITY(1,1) to the desired
seed and step.
- In the original db (now empty) create only the tables - don't
worry about the views, procedures and any other objects.
- Create a snapshot replication using the backup_db as the
publishing database: ensure that the published articles are marked to
only "Delete all the data in the existing table" - this will ensure
that the subscriber's tables are not destroyed (hence keeping your new
identity definition"
- Subscribe the original db to the publication and sync it.
- Once the replication has completed we now need to update all the
tables with an identity to reseed with the latest highest value - you
can do this by defining the following script (in my case, I need the
seed to be even):
BEGIN
DECLARE @.new_ident as int
SET @.new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
IF (@.new_ident % 2) <> 0
SET @.new_ident = @.new_ident + 1
DBCC CHECKIDENT('ACCOUNT', RESEED, @.new_ident)
END
|||Small errata:
> BEGIN
DBCC CHECKIDENT('your_table', RESEED)
> DECLARE @.new_ident as int
> SET @.new_ident=(SELECT IDENT_CURRENT('your_table')) + 1
> IF (@.new_ident % 2) <> 0
> SET @.new_ident = @.new_ident + 1
> DBCC CHECKIDENT('ACCOUNT', RESEED, @.new_ident)
> END
Also, in order to generate all the scripts for each table you could
use a mail merge program with a list of your table names - this should
save time and user errors.
altering dynamically a set data type
Hi
suppose that I have a set named 'my_set' wich contain
'aaa','bbb','ccc'
and I want to alter with an sql command the structure to get 'ddd' in
addition. How can I do that?Does "My_Set" refer to a table? If so, you add a new column to the table
using the ALTER TABLE statement. For example:
ALTER TABLE my_set ADD ddd INTEGER NULL
--
David Portas
SQL Server MVP
--|||jfbeaulieu2003@.yahoo.com (J.Beaulieu) wrote in message news:<eb1b8a74.0408101906.49a3bf1b@.posting.google.com>...
> Hi
> suppose that I have a set named 'my_set' wich contain
> 'aaa','bbb','ccc'
> and I want to alter with an sql command the structure to get 'ddd' in
> addition. How can I do that?
MSSQL doesn't have a 'set data type' - are you using MySQL, perhaps?
If so, you will get a better answer in a MySQL forum.
Simon|||No, my_set is a field, a SET data type
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<78GdnU9yacB-aoTcRVn-iA@.giganews.com>...
> Does "My_Set" refer to a table? If so, you add a new column to the table
> using the ALTER TABLE statement. For example:
> ALTER TABLE my_set ADD ddd INTEGER NULL
>> Hi
>>
>> suppose that I have a set named 'my_set' wich contain
>> 'aaa','bbb','ccc'
>> and I want to alter with an sql command the structure to get 'ddd' in
>> addition. How can I do that?|||J.Beaulieu (jfbeaulieu2003@.yahoo.com) writes:
> No, my_set is a field, a SET data type
There is no SET datatype in SQL Server (unless you it's a user-defined
type you added yourself.) Are you in the wrong newsgroup?
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||There is no base datatype called "SET" in SQL Server.
You may have a user-defined datatype of that name. There is no way to alter
the definition of an existing datatype - you have to drop it and then
recreate it - but I'm not even sure if your question relates to altering the
datatype or the data within it.
Simon suggested that maybe this is a MySQL question. If that's the case then
please try a MySQL forum. This is a SQL Server group.
--
David Portas
SQL Server MVP
--
Altering columns...getting complicated...
I have a need to write many scripts to alter a LOT the underlying database
structure of our database in the field. We have many fields out there with
a type of float, and I've been told to change those to numeric(19,5) -- easy
enough. Unless there is a constraint, in which case I have to drop the
constraint, alter the field, add the constraint back in. Easy enough again,
once you know what you are doing.
Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
easy enough again, unless they have a default -- use the same scheme as
above, and it all works. UNLESS they are part of a primary key. Uh oh --
now I hit something I don't know how to solve...
What I'm thinking is that I should dump all of the indexes and primary keys
and defaults out of all tables, and then just rebuild them all from scratch.
However, this database was "created" by using the Access upsizing wizard, so
I don't know all the primary key names, constraint names, etc.
Can anyone point me in the right direction to dump all indexes and defaults
on every column in a database? I can re-create them pretty easily...
Any advice would be appreciated, or even an alternate method to do what I
need to do.
Thanks in advance.
Matt
In message <OU#WxZ8WFHA.2420@.TK2MSFTNGP12.phx.gbl>, YYZ <none@.none.com>
writes
>I have a need to write many scripts to alter a LOT the underlying database
>structure of our database in the field. We have many fields out there with
>a type of float, and I've been told to change those to numeric(19,5) -- easy
>enough. Unless there is a constraint, in which case I have to drop the
>constraint, alter the field, add the constraint back in. Easy enough again,
>once you know what you are doing.
>Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
>easy enough again, unless they have a default -- use the same scheme as
>above, and it all works. UNLESS they are part of a primary key. Uh oh --
>now I hit something I don't know how to solve...
>What I'm thinking is that I should dump all of the indexes and primary keys
>and defaults out of all tables, and then just rebuild them all from scratch.
>However, this database was "created" by using the Access upsizing wizard, so
>I don't know all the primary key names, constraint names, etc.
>Can anyone point me in the right direction to dump all indexes and defaults
>on every column in a database? I can re-create them pretty easily...
>Any advice would be appreciated, or even an alternate method to do what I
>need to do.
>
Use a CURSOR to enumerate the SYSINDEXES system table in your database
to find all the indexes on it. Alternatively, if you tied this up with
the INFORMATION_SCHEMA.TABLES you can list the indexes on a table by
table basis.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Matt,
YYZ wrote:
> I have a need to write many scripts to alter a LOT the underlying
> database structure of our database in the field. We have many fields
> out there with a type of float, and I've been told to change those to
> numeric(19,5) -- easy enough. Unless there is a constraint, in which
> case I have to drop the constraint, alter the field, add the
> constraint back in. Easy enough again, once you know what you are
> doing.
> Now, they tell me to change all the nvarchar(XX) fields to
> varchar(XX) -- easy enough again, unless they have a default -- use
> the same scheme as above, and it all works. UNLESS they are part of
> a primary key. Uh oh -- now I hit something I don't know how to
> solve...
> What I'm thinking is that I should dump all of the indexes and
> primary keys and defaults out of all tables, and then just rebuild
> them all from scratch. However, this database was "created" by using
> the Access upsizing wizard, so I don't know all the primary key
> names, constraint names, etc.
> Can anyone point me in the right direction to dump all indexes and
> defaults on every column in a database? I can re-create them pretty
> easily...
> Any advice would be appreciated, or even an alternate method to do
> what I need to do.
you can perhaps search www.sqlservercentral.com... there's plenty of
maintenance scripts...
ie: http://www.sqlservercentral.com/scri...utions/935.asp to drop
and recreate all indexes on a db..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
structure of our database in the field. We have many fields out there with
a type of float, and I've been told to change those to numeric(19,5) -- easy
enough. Unless there is a constraint, in which case I have to drop the
constraint, alter the field, add the constraint back in. Easy enough again,
once you know what you are doing.
Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
easy enough again, unless they have a default -- use the same scheme as
above, and it all works. UNLESS they are part of a primary key. Uh oh --
now I hit something I don't know how to solve...
What I'm thinking is that I should dump all of the indexes and primary keys
and defaults out of all tables, and then just rebuild them all from scratch.
However, this database was "created" by using the Access upsizing wizard, so
I don't know all the primary key names, constraint names, etc.
Can anyone point me in the right direction to dump all indexes and defaults
on every column in a database? I can re-create them pretty easily...
Any advice would be appreciated, or even an alternate method to do what I
need to do.
Thanks in advance.
Matt
In message <OU#WxZ8WFHA.2420@.TK2MSFTNGP12.phx.gbl>, YYZ <none@.none.com>
writes
>I have a need to write many scripts to alter a LOT the underlying database
>structure of our database in the field. We have many fields out there with
>a type of float, and I've been told to change those to numeric(19,5) -- easy
>enough. Unless there is a constraint, in which case I have to drop the
>constraint, alter the field, add the constraint back in. Easy enough again,
>once you know what you are doing.
>Now, they tell me to change all the nvarchar(XX) fields to varchar(XX) --
>easy enough again, unless they have a default -- use the same scheme as
>above, and it all works. UNLESS they are part of a primary key. Uh oh --
>now I hit something I don't know how to solve...
>What I'm thinking is that I should dump all of the indexes and primary keys
>and defaults out of all tables, and then just rebuild them all from scratch.
>However, this database was "created" by using the Access upsizing wizard, so
>I don't know all the primary key names, constraint names, etc.
>Can anyone point me in the right direction to dump all indexes and defaults
>on every column in a database? I can re-create them pretty easily...
>Any advice would be appreciated, or even an alternate method to do what I
>need to do.
>
Use a CURSOR to enumerate the SYSINDEXES system table in your database
to find all the indexes on it. Alternatively, if you tied this up with
the INFORMATION_SCHEMA.TABLES you can list the indexes on a table by
table basis.
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com
|||hi Matt,
YYZ wrote:
> I have a need to write many scripts to alter a LOT the underlying
> database structure of our database in the field. We have many fields
> out there with a type of float, and I've been told to change those to
> numeric(19,5) -- easy enough. Unless there is a constraint, in which
> case I have to drop the constraint, alter the field, add the
> constraint back in. Easy enough again, once you know what you are
> doing.
> Now, they tell me to change all the nvarchar(XX) fields to
> varchar(XX) -- easy enough again, unless they have a default -- use
> the same scheme as above, and it all works. UNLESS they are part of
> a primary key. Uh oh -- now I hit something I don't know how to
> solve...
> What I'm thinking is that I should dump all of the indexes and
> primary keys and defaults out of all tables, and then just rebuild
> them all from scratch. However, this database was "created" by using
> the Access upsizing wizard, so I don't know all the primary key
> names, constraint names, etc.
> Can anyone point me in the right direction to dump all indexes and
> defaults on every column in a database? I can re-create them pretty
> easily...
> Any advice would be appreciated, or even an alternate method to do
> what I need to do.
you can perhaps search www.sqlservercentral.com... there's plenty of
maintenance scripts...
ie: http://www.sqlservercentral.com/scri...utions/935.asp to drop
and recreate all indexes on a db..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
altering columns performance issues
Good Day:
When altering column width in SQL2000SP3a we find that this is taking a long
running time on tables with many rows.
We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
the data should not need to be transformed, only the column altered.
There are some 20 million rows and some 200 columns that need this
alteration.
Any thoughts on how to speed this up?
We could create new tables and copy the data, but that requires all user
activity to be stopped, and that is not palatable.
I note that Oracle can do this sort of an alter instantly, for some reason.
THANKS
Mr. Lynn Teska
Mayo ClinicHi Lynn
I just wrote an article on ALTER TABLE for SQL Server Magazine, and why some
changes take a long time and other don't. Unfortunately, it won't appear
until January, so I'll give you a sneak preview.
You are right that the data doesn't need to be transformed, but I'm not sure
what you mean by 'only the column altered'. Yes, the column needs to be
altered, and in every single row in the table. For some changes, like
changing to a smaller datatype, only the metadata needs to be changed (but
the existing data does need to be validated to make sure it 'fits' into the
smaller type.)
If you alter a column's datatype to one that needs more storage space, SQL
Server will actually make the physical change to every row to allow the
additional bytes that are needed to store numeric(8,3).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a
long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some
reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||In addition to Kalen's response:
Watch out if you use EM for this. EM generally creates a new table, copy the data etc etc instead of
executing an ALTER TABLE tblname ALTER COLUMN colname command.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lynn Teska" <lteska@.mayo.edu> wrote in message news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||Good Day:
Well here is a hypothetical situation:
We have a 24x7x365 app with a particular table that has some 200
numeric(7,2) columns.
There are a number of other columns with constraints and indexes, but none
of the numeric (7,2) columns is indexed or constrained.
The applications store new data in the table every 1 minute, or thereabouts.
There are 25 Million rows in the table.
We need to widen all the numeric (7,2) columns to numeric (8,3).
We need to minimize or eliminate application outage.
SQL server is doing a huge amount of I/O to alter the columns.
SQL server appears to only be able to alter one column at a time.
Now when I add new columns SQL server is quick as you please.
What strategy would this group think best to accomplish the task of widening
the columns?
Thanks for any insight!
Lynn Teska
Mayo Clinic
--
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyk5vqrnDHA.688@.TK2MSFTNGP10.phx.gbl...
> In addition to Kalen's response:
> Watch out if you use EM for this. EM generally creates a new table, copy
the data etc etc instead of
> executing an ALTER TABLE tblname ALTER COLUMN colname command.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > Good Day:
> >
> > When altering column width in SQL2000SP3a we find that this is taking a
long
> > running time on tables with many rows.
> > We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> > the data should not need to be transformed, only the column altered.
> > There are some 20 million rows and some 200 columns that need this
> > alteration.
> >
> > Any thoughts on how to speed this up?
> > We could create new tables and copy the data, but that requires all user
> > activity to be stopped, and that is not palatable.
> >
> > I note that Oracle can do this sort of an alter instantly, for some
reason.
> >
> > THANKS
> > Mr. Lynn Teska
> > Mayo Clinic
> >
> >
>|||Hi Lynn,
I would like to thank Kalen and Tibor for their help. As I understand, you
want to widen all the numeric (7,2) columns to numeric (8,3) in the table
on the machine. If I have misunderstood, please feel free to let me know.
To change the schema of the table, we can perform SQL statements using
Query Analyzer or Change the table schema directly in SQL Server Enterprise
Manager. Because Enterprise Manager will create a new tale and drop the
original table for changing the schema, I think it is better to use Query
Analyzer with ALTER TABLE statement.
Example:
alter table <table name> alter column <column name> numeric(8,3)
For additional information regarding ALTER TABLE, please refer to the
following article on SQL Server Books Online.
Topic: "ALTER TABLE"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I understand how to do the task with the EM or TSQL.
The problem is the time it takes with 25 million rows is not acceptable in
at 24x7x365 system.
TSQL can only alter one column at a time, and with 200 columns to do that is
not so good.
EM makes copies as you note and that is not efficient in terms of disk i/o.
I am trying to get a strategy that will minimize or eliminate down time and
blocking of the table.
Interestingly Oracle can do this sort of alteration instantly, according to
our vendor.
Thanks
Lynn Teska
Mayo Clinic
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lynn,
> I would like to thank Kalen and Tibor for their help. As I understand, you
> want to widen all the numeric (7,2) columns to numeric (8,3) in the table
> on the machine. If I have misunderstood, please feel free to let me know.
> To change the schema of the table, we can perform SQL statements using
> Query Analyzer or Change the table schema directly in SQL Server
Enterprise
> Manager. Because Enterprise Manager will create a new tale and drop the
> original table for changing the schema, I think it is better to use Query
> Analyzer with ALTER TABLE statement.
> Example:
> alter table <table name> alter column <column name> numeric(8,3)
> For additional information regarding ALTER TABLE, please refer to the
> following article on SQL Server Books Online.
> Topic: "ALTER TABLE"
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Lynn
The vendors says Oracle can do this instantly, but you've never tried it
yourself?
I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
instantly too, that's why you come to a Technical forum to find out. Have
you asked this on an Oracle Technical forum?
As I mentioned, there are some kinds of ALTER TABLEs that can be done
instantly, and there is a lot of confusion, so someone who had not
researched this issue in technical detail could easily tell you that this
was an instantaneous operation, even on SQL Server.
You are wanting to physically change 25 million rows? How could this
possible be an instantaneous operation?
One faster way you might consider is to create a new table using select
into, which is a very fast command, and then dropping the old table, and
renaming the new one to the old name.
SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
col2 ...
INTO newtable
FROM original_table
GO
--recreate constraints, indexes etc on newtable
DROP original_table
EXEC sp_rename newtable, original_table
Good Luck!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I understand how to do the task with the EM or TSQL.
> The problem is the time it takes with 25 million rows is not acceptable in
> at 24x7x365 system.
> TSQL can only alter one column at a time, and with 200 columns to do that
is
> not so good.
> EM makes copies as you note and that is not efficient in terms of disk
i/o.
> I am trying to get a strategy that will minimize or eliminate down time
and
> blocking of the table.
> Interestingly Oracle can do this sort of alteration instantly, according
to
> our vendor.
> Thanks
> Lynn Teska
> Mayo Clinic
>
> "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > Hi Lynn,
> >
> > I would like to thank Kalen and Tibor for their help. As I understand,
you
> > want to widen all the numeric (7,2) columns to numeric (8,3) in the
table
> > on the machine. If I have misunderstood, please feel free to let me
know.
> >
> > To change the schema of the table, we can perform SQL statements using
> > Query Analyzer or Change the table schema directly in SQL Server
> Enterprise
> > Manager. Because Enterprise Manager will create a new tale and drop the
> > original table for changing the schema, I think it is better to use
Query
> > Analyzer with ALTER TABLE statement.
> >
> > Example:
> >
> > alter table <table name> alter column <column name> numeric(8,3)
> >
> > For additional information regarding ALTER TABLE, please refer to the
> > following article on SQL Server Books Online.
> > Topic: "ALTER TABLE"
> >
> > Please feel free to post in the group if this solves your problem or if
> you
> > would like further assistance.
> >
> > Regards,
> >
> > Michael Shao
> > Microsoft Online Partner Support
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> >
>|||Thanks for the info.
The vended product runs on both SQL server and Oracle, I have no reason to
doubt that the change is fast on Oracle.
They seem to be experienced in both platforms.
Thanks for your suggestion I will have a look at it.
Lynn Teska
PS:
This is an expert of a note I received from an Oracle expert:
Oracle always reserves a minimum of space in every database block for
updates, determined by the PCTFREE parameter (10% of the block size by
default, which we don't modify). Hence, Oracle does not have to reserve any
extra space, it's already there. Initially the old rows will surely fit
inside the new definition, as we have just widened the columns. If some
value needs some extra space, it will use the PCTFREE space. If this is not
enough, a phenomenon called row chaining will take place, as part of the row
will have to be stored in another block, and this is not so good for
performance, but it can be attacked later on. In our case this would never
happen as we won't go updating old values.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||All the more interesting since numeric (7,2) and numeric (8,3) both appear
to be stored in 5 bytes.
Lynn
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||Yes, there does appear to be some unexpected behavior going on. I am still
researching this issue.
OTOH, I have just learned that although you are correct, that Oracle does
make this change and all ALTER TABLE changes instantly, as only a metadata
change, the tradeoff is that actual row size adjustment must be made during
actual data use. In fact, if you change the datatype size to something very
different, or smaller, like char(5) to char(3) or char to int, Oracle will
not do any validation at the time of the alter table, but will wait until
your read the row. So you can end up getting conversion errors just by
reading data, which can be very problematic.
So, like many choices, there is a tradeoff. Normally, since altering tables
is a rare occurance, but updating and modifying data is done during
production operations, I'd rather the overhead was accrued during the alter,
when I can plan for it.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> All the more interesting since numeric (7,2) and numeric (8,3) both appear
> to be stored in 5 bytes.
> Lynn
> --
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > Lynn
> >
> > The vendors says Oracle can do this instantly, but you've never tried it
> > yourself?
> >
> > I wouldn't be surprised if a SQL Server vendor said SQL Server could do
it
> > instantly too, that's why you come to a Technical forum to find out.
Have
> > you asked this on an Oracle Technical forum?
> >
> > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > instantly, and there is a lot of confusion, so someone who had not
> > researched this issue in technical detail could easily tell you that
this
> > was an instantaneous operation, even on SQL Server.
> >
> > You are wanting to physically change 25 million rows? How could this
> > possible be an instantaneous operation?
> >
> > One faster way you might consider is to create a new table using select
> > into, which is a very fast command, and then dropping the old table, and
> > renaming the new one to the old name.
> >
> > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
as
> > col2 ...
> > INTO newtable
> > FROM original_table
> > GO
> >
> > --recreate constraints, indexes etc on newtable
> >
> > DROP original_table
> >
> > EXEC sp_rename newtable, original_table
> >
> > Good Luck!
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I understand how to do the task with the EM or TSQL.
> > > The problem is the time it takes with 25 million rows is not
acceptable
> in
> > > at 24x7x365 system.
> > >
> > > TSQL can only alter one column at a time, and with 200 columns to do
> that
> > is
> > > not so good.
> > > EM makes copies as you note and that is not efficient in terms of disk
> > i/o.
> > > I am trying to get a strategy that will minimize or eliminate down
time
> > and
> > > blocking of the table.
> > >
> > > Interestingly Oracle can do this sort of alteration instantly,
according
> > to
> > > our vendor.
> > >
> > > Thanks
> > >
> > > Lynn Teska
> > > Mayo Clinic
> > >
> > >
> > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > Hi Lynn,
> > > >
> > > > I would like to thank Kalen and Tibor for their help. As I
understand,
> > you
> > > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> > table
> > > > on the machine. If I have misunderstood, please feel free to let me
> > know.
> > > >
> > > > To change the schema of the table, we can perform SQL statements
using
> > > > Query Analyzer or Change the table schema directly in SQL Server
> > > Enterprise
> > > > Manager. Because Enterprise Manager will create a new tale and drop
> the
> > > > original table for changing the schema, I think it is better to use
> > Query
> > > > Analyzer with ALTER TABLE statement.
> > > >
> > > > Example:
> > > >
> > > > alter table <table name> alter column <column name> numeric(8,3)
> > > >
> > > > For additional information regarding ALTER TABLE, please refer to
the
> > > > following article on SQL Server Books Online.
> > > > Topic: "ALTER TABLE"
> > > >
> > > > Please feel free to post in the group if this solves your problem or
> if
> > > you
> > > > would like further assistance.
> > > >
> > > > Regards,
> > > >
> > > > Michael Shao
> > > > Microsoft Online Partner Support
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > >
> > >
> > >
> >
> >
>|||OK Thanks.
Let me know what you find.
A 4 hour outage is not a plus for us.
Thanks
Lynn Teska
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Jo3UejoDHA.3040@.TK2MSFTNGP11.phx.gbl...
> Yes, there does appear to be some unexpected behavior going on. I am still
> researching this issue.
> OTOH, I have just learned that although you are correct, that Oracle does
> make this change and all ALTER TABLE changes instantly, as only a metadata
> change, the tradeoff is that actual row size adjustment must be made
during
> actual data use. In fact, if you change the datatype size to something
very
> different, or smaller, like char(5) to char(3) or char to int, Oracle will
> not do any validation at the time of the alter table, but will wait until
> your read the row. So you can end up getting conversion errors just by
> reading data, which can be very problematic.
> So, like many choices, there is a tradeoff. Normally, since altering
tables
> is a rare occurance, but updating and modifying data is done during
> production operations, I'd rather the overhead was accrued during the
alter,
> when I can plan for it.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > All the more interesting since numeric (7,2) and numeric (8,3) both
appear
> > to be stored in 5 bytes.
> >
> > Lynn
> >
> > --
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > > Lynn
> > >
> > > The vendors says Oracle can do this instantly, but you've never tried
it
> > > yourself?
> > >
> > > I wouldn't be surprised if a SQL Server vendor said SQL Server could
do
> it
> > > instantly too, that's why you come to a Technical forum to find out.
> Have
> > > you asked this on an Oracle Technical forum?
> > >
> > > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > > instantly, and there is a lot of confusion, so someone who had not
> > > researched this issue in technical detail could easily tell you that
> this
> > > was an instantaneous operation, even on SQL Server.
> > >
> > > You are wanting to physically change 25 million rows? How could this
> > > possible be an instantaneous operation?
> > >
> > > One faster way you might consider is to create a new table using
select
> > > into, which is a very fast command, and then dropping the old table,
and
> > > renaming the new one to the old name.
> > >
> > > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
> as
> > > col2 ...
> > > INTO newtable
> > > FROM original_table
> > > GO
> > >
> > > --recreate constraints, indexes etc on newtable
> > >
> > > DROP original_table
> > >
> > > EXEC sp_rename newtable, original_table
> > >
> > > Good Luck!
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I understand how to do the task with the EM or TSQL.
> > > > The problem is the time it takes with 25 million rows is not
> acceptable
> > in
> > > > at 24x7x365 system.
> > > >
> > > > TSQL can only alter one column at a time, and with 200 columns to do
> > that
> > > is
> > > > not so good.
> > > > EM makes copies as you note and that is not efficient in terms of
disk
> > > i/o.
> > > > I am trying to get a strategy that will minimize or eliminate down
> time
> > > and
> > > > blocking of the table.
> > > >
> > > > Interestingly Oracle can do this sort of alteration instantly,
> according
> > > to
> > > > our vendor.
> > > >
> > > > Thanks
> > > >
> > > > Lynn Teska
> > > > Mayo Clinic
> > > >
> > > >
> > > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in
message
> > > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > > Hi Lynn,
> > > > >
> > > > > I would like to thank Kalen and Tibor for their help. As I
> understand,
> > > you
> > > > > want to widen all the numeric (7,2) columns to numeric (8,3) in
the
> > > table
> > > > > on the machine. If I have misunderstood, please feel free to let
me
> > > know.
> > > > >
> > > > > To change the schema of the table, we can perform SQL statements
> using
> > > > > Query Analyzer or Change the table schema directly in SQL Server
> > > > Enterprise
> > > > > Manager. Because Enterprise Manager will create a new tale and
drop
> > the
> > > > > original table for changing the schema, I think it is better to
use
> > > Query
> > > > > Analyzer with ALTER TABLE statement.
> > > > >
> > > > > Example:
> > > > >
> > > > > alter table <table name> alter column <column name> numeric(8,3)
> > > > >
> > > > > For additional information regarding ALTER TABLE, please refer to
> the
> > > > > following article on SQL Server Books Online.
> > > > > Topic: "ALTER TABLE"
> > > > >
> > > > > Please feel free to post in the group if this solves your problem
or
> > if
> > > > you
> > > > > would like further assistance.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Michael Shao
> > > > > Microsoft Online Partner Support
> > > > > Get Secure! - www.microsoft.com/security
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
When altering column width in SQL2000SP3a we find that this is taking a long
running time on tables with many rows.
We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
the data should not need to be transformed, only the column altered.
There are some 20 million rows and some 200 columns that need this
alteration.
Any thoughts on how to speed this up?
We could create new tables and copy the data, but that requires all user
activity to be stopped, and that is not palatable.
I note that Oracle can do this sort of an alter instantly, for some reason.
THANKS
Mr. Lynn Teska
Mayo ClinicHi Lynn
I just wrote an article on ALTER TABLE for SQL Server Magazine, and why some
changes take a long time and other don't. Unfortunately, it won't appear
until January, so I'll give you a sneak preview.
You are right that the data doesn't need to be transformed, but I'm not sure
what you mean by 'only the column altered'. Yes, the column needs to be
altered, and in every single row in the table. For some changes, like
changing to a smaller datatype, only the metadata needs to be changed (but
the existing data does need to be validated to make sure it 'fits' into the
smaller type.)
If you alter a column's datatype to one that needs more storage space, SQL
Server will actually make the physical change to every row to allow the
additional bytes that are needed to store numeric(8,3).
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a
long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some
reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||In addition to Kalen's response:
Watch out if you use EM for this. EM generally creates a new table, copy the data etc etc instead of
executing an ALTER TABLE tblname ALTER COLUMN colname command.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Lynn Teska" <lteska@.mayo.edu> wrote in message news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> Good Day:
> When altering column width in SQL2000SP3a we find that this is taking a long
> running time on tables with many rows.
> We are trying to alter some numeric (7,2) columns to numeric (8,3) and thus
> the data should not need to be transformed, only the column altered.
> There are some 20 million rows and some 200 columns that need this
> alteration.
> Any thoughts on how to speed this up?
> We could create new tables and copy the data, but that requires all user
> activity to be stopped, and that is not palatable.
> I note that Oracle can do this sort of an alter instantly, for some reason.
> THANKS
> Mr. Lynn Teska
> Mayo Clinic
>|||Good Day:
Well here is a hypothetical situation:
We have a 24x7x365 app with a particular table that has some 200
numeric(7,2) columns.
There are a number of other columns with constraints and indexes, but none
of the numeric (7,2) columns is indexed or constrained.
The applications store new data in the table every 1 minute, or thereabouts.
There are 25 Million rows in the table.
We need to widen all the numeric (7,2) columns to numeric (8,3).
We need to minimize or eliminate application outage.
SQL server is doing a huge amount of I/O to alter the columns.
SQL server appears to only be able to alter one column at a time.
Now when I add new columns SQL server is quick as you please.
What strategy would this group think best to accomplish the task of widening
the columns?
Thanks for any insight!
Lynn Teska
Mayo Clinic
--
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eyk5vqrnDHA.688@.TK2MSFTNGP10.phx.gbl...
> In addition to Kalen's response:
> Watch out if you use EM for this. EM generally creates a new table, copy
the data etc etc instead of
> executing an ALTER TABLE tblname ALTER COLUMN colname command.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
news:OfwNwoknDHA.2628@.TK2MSFTNGP10.phx.gbl...
> > Good Day:
> >
> > When altering column width in SQL2000SP3a we find that this is taking a
long
> > running time on tables with many rows.
> > We are trying to alter some numeric (7,2) columns to numeric (8,3) and
thus
> > the data should not need to be transformed, only the column altered.
> > There are some 20 million rows and some 200 columns that need this
> > alteration.
> >
> > Any thoughts on how to speed this up?
> > We could create new tables and copy the data, but that requires all user
> > activity to be stopped, and that is not palatable.
> >
> > I note that Oracle can do this sort of an alter instantly, for some
reason.
> >
> > THANKS
> > Mr. Lynn Teska
> > Mayo Clinic
> >
> >
>|||Hi Lynn,
I would like to thank Kalen and Tibor for their help. As I understand, you
want to widen all the numeric (7,2) columns to numeric (8,3) in the table
on the machine. If I have misunderstood, please feel free to let me know.
To change the schema of the table, we can perform SQL statements using
Query Analyzer or Change the table schema directly in SQL Server Enterprise
Manager. Because Enterprise Manager will create a new tale and drop the
original table for changing the schema, I think it is better to use Query
Analyzer with ALTER TABLE statement.
Example:
alter table <table name> alter column <column name> numeric(8,3)
For additional information regarding ALTER TABLE, please refer to the
following article on SQL Server Books Online.
Topic: "ALTER TABLE"
Please feel free to post in the group if this solves your problem or if you
would like further assistance.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||I understand how to do the task with the EM or TSQL.
The problem is the time it takes with 25 million rows is not acceptable in
at 24x7x365 system.
TSQL can only alter one column at a time, and with 200 columns to do that is
not so good.
EM makes copies as you note and that is not efficient in terms of disk i/o.
I am trying to get a strategy that will minimize or eliminate down time and
blocking of the table.
Interestingly Oracle can do this sort of alteration instantly, according to
our vendor.
Thanks
Lynn Teska
Mayo Clinic
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> Hi Lynn,
> I would like to thank Kalen and Tibor for their help. As I understand, you
> want to widen all the numeric (7,2) columns to numeric (8,3) in the table
> on the machine. If I have misunderstood, please feel free to let me know.
> To change the schema of the table, we can perform SQL statements using
> Query Analyzer or Change the table schema directly in SQL Server
Enterprise
> Manager. Because Enterprise Manager will create a new tale and drop the
> original table for changing the schema, I think it is better to use Query
> Analyzer with ALTER TABLE statement.
> Example:
> alter table <table name> alter column <column name> numeric(8,3)
> For additional information regarding ALTER TABLE, please refer to the
> following article on SQL Server Books Online.
> Topic: "ALTER TABLE"
> Please feel free to post in the group if this solves your problem or if
you
> would like further assistance.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Lynn
The vendors says Oracle can do this instantly, but you've never tried it
yourself?
I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
instantly too, that's why you come to a Technical forum to find out. Have
you asked this on an Oracle Technical forum?
As I mentioned, there are some kinds of ALTER TABLEs that can be done
instantly, and there is a lot of confusion, so someone who had not
researched this issue in technical detail could easily tell you that this
was an instantaneous operation, even on SQL Server.
You are wanting to physically change 25 million rows? How could this
possible be an instantaneous operation?
One faster way you might consider is to create a new table using select
into, which is a very fast command, and then dropping the old table, and
renaming the new one to the old name.
SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
col2 ...
INTO newtable
FROM original_table
GO
--recreate constraints, indexes etc on newtable
DROP original_table
EXEC sp_rename newtable, original_table
Good Luck!
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> I understand how to do the task with the EM or TSQL.
> The problem is the time it takes with 25 million rows is not acceptable in
> at 24x7x365 system.
> TSQL can only alter one column at a time, and with 200 columns to do that
is
> not so good.
> EM makes copies as you note and that is not efficient in terms of disk
i/o.
> I am trying to get a strategy that will minimize or eliminate down time
and
> blocking of the table.
> Interestingly Oracle can do this sort of alteration instantly, according
to
> our vendor.
> Thanks
> Lynn Teska
> Mayo Clinic
>
> "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > Hi Lynn,
> >
> > I would like to thank Kalen and Tibor for their help. As I understand,
you
> > want to widen all the numeric (7,2) columns to numeric (8,3) in the
table
> > on the machine. If I have misunderstood, please feel free to let me
know.
> >
> > To change the schema of the table, we can perform SQL statements using
> > Query Analyzer or Change the table schema directly in SQL Server
> Enterprise
> > Manager. Because Enterprise Manager will create a new tale and drop the
> > original table for changing the schema, I think it is better to use
Query
> > Analyzer with ALTER TABLE statement.
> >
> > Example:
> >
> > alter table <table name> alter column <column name> numeric(8,3)
> >
> > For additional information regarding ALTER TABLE, please refer to the
> > following article on SQL Server Books Online.
> > Topic: "ALTER TABLE"
> >
> > Please feel free to post in the group if this solves your problem or if
> you
> > would like further assistance.
> >
> > Regards,
> >
> > Michael Shao
> > Microsoft Online Partner Support
> > Get Secure! - www.microsoft.com/security
> > This posting is provided "as is" with no warranties and confers no
rights.
> >
>|||Thanks for the info.
The vended product runs on both SQL server and Oracle, I have no reason to
doubt that the change is fast on Oracle.
They seem to be experienced in both platforms.
Thanks for your suggestion I will have a look at it.
Lynn Teska
PS:
This is an expert of a note I received from an Oracle expert:
Oracle always reserves a minimum of space in every database block for
updates, determined by the PCTFREE parameter (10% of the block size by
default, which we don't modify). Hence, Oracle does not have to reserve any
extra space, it's already there. Initially the old rows will surely fit
inside the new definition, as we have just widened the columns. If some
value needs some extra space, it will use the PCTFREE space. If this is not
enough, a phenomenon called row chaining will take place, as part of the row
will have to be stored in another block, and this is not so good for
performance, but it can be attacked later on. In our case this would never
happen as we won't go updating old values.
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||All the more interesting since numeric (7,2) and numeric (8,3) both appear
to be stored in 5 bytes.
Lynn
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> Lynn
> The vendors says Oracle can do this instantly, but you've never tried it
> yourself?
> I wouldn't be surprised if a SQL Server vendor said SQL Server could do it
> instantly too, that's why you come to a Technical forum to find out. Have
> you asked this on an Oracle Technical forum?
> As I mentioned, there are some kinds of ALTER TABLEs that can be done
> instantly, and there is a lot of confusion, so someone who had not
> researched this issue in technical detail could easily tell you that this
> was an instantaneous operation, even on SQL Server.
> You are wanting to physically change 25 million rows? How could this
> possible be an instantaneous operation?
> One faster way you might consider is to create a new table using select
> into, which is a very fast command, and then dropping the old table, and
> renaming the new one to the old name.
> SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2) as
> col2 ...
> INTO newtable
> FROM original_table
> GO
> --recreate constraints, indexes etc on newtable
> DROP original_table
> EXEC sp_rename newtable, original_table
> Good Luck!
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > I understand how to do the task with the EM or TSQL.
> > The problem is the time it takes with 25 million rows is not acceptable
in
> > at 24x7x365 system.
> >
> > TSQL can only alter one column at a time, and with 200 columns to do
that
> is
> > not so good.
> > EM makes copies as you note and that is not efficient in terms of disk
> i/o.
> > I am trying to get a strategy that will minimize or eliminate down time
> and
> > blocking of the table.
> >
> > Interestingly Oracle can do this sort of alteration instantly, according
> to
> > our vendor.
> >
> > Thanks
> >
> > Lynn Teska
> > Mayo Clinic
> >
> >
> > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > Hi Lynn,
> > >
> > > I would like to thank Kalen and Tibor for their help. As I understand,
> you
> > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> table
> > > on the machine. If I have misunderstood, please feel free to let me
> know.
> > >
> > > To change the schema of the table, we can perform SQL statements using
> > > Query Analyzer or Change the table schema directly in SQL Server
> > Enterprise
> > > Manager. Because Enterprise Manager will create a new tale and drop
the
> > > original table for changing the schema, I think it is better to use
> Query
> > > Analyzer with ALTER TABLE statement.
> > >
> > > Example:
> > >
> > > alter table <table name> alter column <column name> numeric(8,3)
> > >
> > > For additional information regarding ALTER TABLE, please refer to the
> > > following article on SQL Server Books Online.
> > > Topic: "ALTER TABLE"
> > >
> > > Please feel free to post in the group if this solves your problem or
if
> > you
> > > would like further assistance.
> > >
> > > Regards,
> > >
> > > Michael Shao
> > > Microsoft Online Partner Support
> > > Get Secure! - www.microsoft.com/security
> > > This posting is provided "as is" with no warranties and confers no
> rights.
> > >
> >
> >
>|||Yes, there does appear to be some unexpected behavior going on. I am still
researching this issue.
OTOH, I have just learned that although you are correct, that Oracle does
make this change and all ALTER TABLE changes instantly, as only a metadata
change, the tradeoff is that actual row size adjustment must be made during
actual data use. In fact, if you change the datatype size to something very
different, or smaller, like char(5) to char(3) or char to int, Oracle will
not do any validation at the time of the alter table, but will wait until
your read the row. So you can end up getting conversion errors just by
reading data, which can be very problematic.
So, like many choices, there is a tradeoff. Normally, since altering tables
is a rare occurance, but updating and modifying data is done during
production operations, I'd rather the overhead was accrued during the alter,
when I can plan for it.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Lynn Teska" <lteska@.mayo.edu> wrote in message
news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> All the more interesting since numeric (7,2) and numeric (8,3) both appear
> to be stored in 5 bytes.
> Lynn
> --
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > Lynn
> >
> > The vendors says Oracle can do this instantly, but you've never tried it
> > yourself?
> >
> > I wouldn't be surprised if a SQL Server vendor said SQL Server could do
it
> > instantly too, that's why you come to a Technical forum to find out.
Have
> > you asked this on an Oracle Technical forum?
> >
> > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > instantly, and there is a lot of confusion, so someone who had not
> > researched this issue in technical detail could easily tell you that
this
> > was an instantaneous operation, even on SQL Server.
> >
> > You are wanting to physically change 25 million rows? How could this
> > possible be an instantaneous operation?
> >
> > One faster way you might consider is to create a new table using select
> > into, which is a very fast command, and then dropping the old table, and
> > renaming the new one to the old name.
> >
> > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
as
> > col2 ...
> > INTO newtable
> > FROM original_table
> > GO
> >
> > --recreate constraints, indexes etc on newtable
> >
> > DROP original_table
> >
> > EXEC sp_rename newtable, original_table
> >
> > Good Luck!
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > I understand how to do the task with the EM or TSQL.
> > > The problem is the time it takes with 25 million rows is not
acceptable
> in
> > > at 24x7x365 system.
> > >
> > > TSQL can only alter one column at a time, and with 200 columns to do
> that
> > is
> > > not so good.
> > > EM makes copies as you note and that is not efficient in terms of disk
> > i/o.
> > > I am trying to get a strategy that will minimize or eliminate down
time
> > and
> > > blocking of the table.
> > >
> > > Interestingly Oracle can do this sort of alteration instantly,
according
> > to
> > > our vendor.
> > >
> > > Thanks
> > >
> > > Lynn Teska
> > > Mayo Clinic
> > >
> > >
> > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
> > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > Hi Lynn,
> > > >
> > > > I would like to thank Kalen and Tibor for their help. As I
understand,
> > you
> > > > want to widen all the numeric (7,2) columns to numeric (8,3) in the
> > table
> > > > on the machine. If I have misunderstood, please feel free to let me
> > know.
> > > >
> > > > To change the schema of the table, we can perform SQL statements
using
> > > > Query Analyzer or Change the table schema directly in SQL Server
> > > Enterprise
> > > > Manager. Because Enterprise Manager will create a new tale and drop
> the
> > > > original table for changing the schema, I think it is better to use
> > Query
> > > > Analyzer with ALTER TABLE statement.
> > > >
> > > > Example:
> > > >
> > > > alter table <table name> alter column <column name> numeric(8,3)
> > > >
> > > > For additional information regarding ALTER TABLE, please refer to
the
> > > > following article on SQL Server Books Online.
> > > > Topic: "ALTER TABLE"
> > > >
> > > > Please feel free to post in the group if this solves your problem or
> if
> > > you
> > > > would like further assistance.
> > > >
> > > > Regards,
> > > >
> > > > Michael Shao
> > > > Microsoft Online Partner Support
> > > > Get Secure! - www.microsoft.com/security
> > > > This posting is provided "as is" with no warranties and confers no
> > rights.
> > > >
> > >
> > >
> >
> >
>|||OK Thanks.
Let me know what you find.
A 4 hour outage is not a plus for us.
Thanks
Lynn Teska
--
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23Jo3UejoDHA.3040@.TK2MSFTNGP11.phx.gbl...
> Yes, there does appear to be some unexpected behavior going on. I am still
> researching this issue.
> OTOH, I have just learned that although you are correct, that Oracle does
> make this change and all ALTER TABLE changes instantly, as only a metadata
> change, the tradeoff is that actual row size adjustment must be made
during
> actual data use. In fact, if you change the datatype size to something
very
> different, or smaller, like char(5) to char(3) or char to int, Oracle will
> not do any validation at the time of the alter table, but will wait until
> your read the row. So you can end up getting conversion errors just by
> reading data, which can be very problematic.
> So, like many choices, there is a tradeoff. Normally, since altering
tables
> is a rare occurance, but updating and modifying data is done during
> production operations, I'd rather the overhead was accrued during the
alter,
> when I can plan for it.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Lynn Teska" <lteska@.mayo.edu> wrote in message
> news:#Nl5XwioDHA.2488@.TK2MSFTNGP12.phx.gbl...
> > All the more interesting since numeric (7,2) and numeric (8,3) both
appear
> > to be stored in 5 bytes.
> >
> > Lynn
> >
> > --
> >
> >
> > "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> > news:OJjcZz9nDHA.2820@.TK2MSFTNGP10.phx.gbl...
> > > Lynn
> > >
> > > The vendors says Oracle can do this instantly, but you've never tried
it
> > > yourself?
> > >
> > > I wouldn't be surprised if a SQL Server vendor said SQL Server could
do
> it
> > > instantly too, that's why you come to a Technical forum to find out.
> Have
> > > you asked this on an Oracle Technical forum?
> > >
> > > As I mentioned, there are some kinds of ALTER TABLEs that can be done
> > > instantly, and there is a lot of confusion, so someone who had not
> > > researched this issue in technical detail could easily tell you that
> this
> > > was an instantaneous operation, even on SQL Server.
> > >
> > > You are wanting to physically change 25 million rows? How could this
> > > possible be an instantaneous operation?
> > >
> > > One faster way you might consider is to create a new table using
select
> > > into, which is a very fast command, and then dropping the old table,
and
> > > renaming the new one to the old name.
> > >
> > > SELECT convert(numeric(8,3), col1) as col1, convert(newdatatype, col2)
> as
> > > col2 ...
> > > INTO newtable
> > > FROM original_table
> > > GO
> > >
> > > --recreate constraints, indexes etc on newtable
> > >
> > > DROP original_table
> > >
> > > EXEC sp_rename newtable, original_table
> > >
> > > Good Luck!
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Lynn Teska" <lteska@.mayo.edu> wrote in message
> > > news:#HIbWl9nDHA.2064@.TK2MSFTNGP11.phx.gbl...
> > > > I understand how to do the task with the EM or TSQL.
> > > > The problem is the time it takes with 25 million rows is not
> acceptable
> > in
> > > > at 24x7x365 system.
> > > >
> > > > TSQL can only alter one column at a time, and with 200 columns to do
> > that
> > > is
> > > > not so good.
> > > > EM makes copies as you note and that is not efficient in terms of
disk
> > > i/o.
> > > > I am trying to get a strategy that will minimize or eliminate down
> time
> > > and
> > > > blocking of the table.
> > > >
> > > > Interestingly Oracle can do this sort of alteration instantly,
> according
> > > to
> > > > our vendor.
> > > >
> > > > Thanks
> > > >
> > > > Lynn Teska
> > > > Mayo Clinic
> > > >
> > > >
> > > > "Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in
message
> > > > news:71L36X9nDHA.2012@.cpmsftngxa06.phx.gbl...
> > > > > Hi Lynn,
> > > > >
> > > > > I would like to thank Kalen and Tibor for their help. As I
> understand,
> > > you
> > > > > want to widen all the numeric (7,2) columns to numeric (8,3) in
the
> > > table
> > > > > on the machine. If I have misunderstood, please feel free to let
me
> > > know.
> > > > >
> > > > > To change the schema of the table, we can perform SQL statements
> using
> > > > > Query Analyzer or Change the table schema directly in SQL Server
> > > > Enterprise
> > > > > Manager. Because Enterprise Manager will create a new tale and
drop
> > the
> > > > > original table for changing the schema, I think it is better to
use
> > > Query
> > > > > Analyzer with ALTER TABLE statement.
> > > > >
> > > > > Example:
> > > > >
> > > > > alter table <table name> alter column <column name> numeric(8,3)
> > > > >
> > > > > For additional information regarding ALTER TABLE, please refer to
> the
> > > > > following article on SQL Server Books Online.
> > > > > Topic: "ALTER TABLE"
> > > > >
> > > > > Please feel free to post in the group if this solves your problem
or
> > if
> > > > you
> > > > > would like further assistance.
> > > > >
> > > > > Regards,
> > > > >
> > > > > Michael Shao
> > > > > Microsoft Online Partner Support
> > > > > Get Secure! - www.microsoft.com/security
> > > > > This posting is provided "as is" with no warranties and confers no
> > > rights.
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
Altering column with index
Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?You'll need to drop the indexes/constraints and recreate after changing the
collation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||Drop the indexes (and any constraints) first, then alter the column and
restore the indexes (and constraints).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?|||Whell Tom
It seems to be very agly to do that.
When i do it with the enterprise Manager i don't do all this stuff
Are you sure i need to do all of that?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Drop the indexes (and any constraints) first, then alter the column and
> restore the indexes (and constraints).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||
Roy Goldhammer wrote:
>Whell Tom
>It seems to be very agly to do that.
>When i do it with the enterprise Manager i don't do all this stuff
>
Enterprise Manager probably does it for you behind the scenes. While
EM doesn't do everything in the best way, you could generate the change
script from EM and use it as a template for creating your own script. If
an index exists on a varchar column, and then the collation is changed,
the index must be rebuilt. There's no way around it, since a change in
collation may change the ordering of the values, which is what the
index is there to implement.
Steve Kass
Drew University
>Are you sure i need to do all of that?
>"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>
>
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?You'll need to drop the indexes/constraints and recreate after changing the
collation.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||Drop the indexes (and any constraints) first, then alter the column and
restore the indexes (and constraints).
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
Hello there
I need to alter the collation of collumns on my databases.
It failes on columns that connected to indexes
What should i do to alter them in this case?|||Whell Tom
It seems to be very agly to do that.
When i do it with the enterprise Manager i don't do all this stuff
Are you sure i need to do all of that?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
> Drop the indexes (and any constraints) first, then alter the column and
> restore the indexes (and constraints).
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Roy Goldhammer" <roy@.hotmail.com> wrote in message
> news:%237WdGTuOGHA.3896@.TK2MSFTNGP15.phx.gbl...
> Hello there
> I need to alter the collation of collumns on my databases.
> It failes on columns that connected to indexes
> What should i do to alter them in this case?
>
>|||
Roy Goldhammer wrote:
>Whell Tom
>It seems to be very agly to do that.
>When i do it with the enterprise Manager i don't do all this stuff
>
Enterprise Manager probably does it for you behind the scenes. While
EM doesn't do everything in the best way, you could generate the change
script from EM and use it as a template for creating your own script. If
an index exists on a varchar column, and then the collation is changed,
the index must be rebuilt. There's no way around it, since a change in
collation may change the ordering of the values, which is what the
index is there to implement.
Steve Kass
Drew University
>Are you sure i need to do all of that?
>"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>news:ev8nGguOGHA.2704@.TK2MSFTNGP15.phx.gbl...
>
>
>
Altering column in a large table
Hi,
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
ManuThis will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of row
s
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>
>|||I'm pretty certain it is a meta-data only change (provided you use ALTER TAB
LE and not the GUI
tool). I suggest you create a table with some million rows and test, just to
be certain...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@.microsoft.com...[vbcol=seagreen]
> The change is just to alter the not null property of a column in this huge
> table to NULL.
> Thanks
> Manu
> "TheSQLGuru" wrote:
>
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
ManuThis will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of row
s
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>
>|||I'm pretty certain it is a meta-data only change (provided you use ALTER TAB
LE and not the GUI
tool). I suggest you create a table with some million rows and test, just to
be certain...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@.microsoft.com...[vbcol=seagreen]
> The change is just to alter the not null property of a column in this huge
> table to NULL.
> Thanks
> Manu
> "TheSQLGuru" wrote:
>
Altering column in a large table
Hi,
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
Manu
This will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu
|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu
|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>
>
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
Manu
This will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu
|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu
|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>
>
Altering column in a large table
Hi,
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
ManuThis will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> > Hi,
> >
> > I need to alter one column which is a part of a table with billions of
> > rows
> > in it. What would be the finest and fastest approach to do it?
> >
> > Thanks in advance
> > Manu
>
>|||I'm pretty certain it is a meta-data only change (provided you use ALTER TABLE and not the GUI
tool). I suggest you create a table with some million rows and test, just to be certain...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@.microsoft.com...
> The change is just to alter the not null property of a column in this huge
> table to NULL.
> Thanks
> Manu
> "TheSQLGuru" wrote:
>> What, exactly, is the alteration? Some changes could be done more
>> efficiently by bulk exporting data, dropping all structures on table,
>> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
>> Others are nothing more than a meta-data change.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>> > Hi,
>> >
>> > I need to alter one column which is a part of a table with billions of
>> > rows
>> > in it. What would be the finest and fastest approach to do it?
>> >
>> > Thanks in advance
>> > Manu
>>
I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?
Thanks in advance
ManuThis will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.
I would use ALTER TABLE ... ALTER COLUMN.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"manu" wrote:
> Hi,
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
"manu" <manu@.discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> Hi,
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
> Thanks in advance
> Manu|||The change is just to alter the not null property of a column in this huge
table to NULL.
Thanks
Manu
"TheSQLGuru" wrote:
> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
> "manu" <manu@.discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
> > Hi,
> >
> > I need to alter one column which is a part of a table with billions of
> > rows
> > in it. What would be the finest and fastest approach to do it?
> >
> > Thanks in advance
> > Manu
>
>|||I'm pretty certain it is a meta-data only change (provided you use ALTER TABLE and not the GUI
tool). I suggest you create a table with some million rows and test, just to be certain...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"manu" <manu@.discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@.microsoft.com...
> The change is just to alter the not null property of a column in this huge
> table to NULL.
> Thanks
> Manu
> "TheSQLGuru" wrote:
>> What, exactly, is the alteration? Some changes could be done more
>> efficiently by bulk exporting data, dropping all structures on table,
>> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
>> Others are nothing more than a meta-data change.
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>> "manu" <manu@.discussions.microsoft.com> wrote in message
>> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@.microsoft.com...
>> > Hi,
>> >
>> > I need to alter one column which is a part of a table with billions of
>> > rows
>> > in it. What would be the finest and fastest approach to do it?
>> >
>> > Thanks in advance
>> > Manu
>>
Sunday, March 25, 2012
Altering a Default- unknown name...
SyetwemHi There.
I'd like to alter a DEFAULT I have set up on a database table.
Unfortunately, I don't know the name of the default as it was created when
the table was created without specifying a name.
Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
System tables)?...
Or is there a more straightforward way to alter a DEFAULT?
any help would be appreciated!
My final lines of code should look something like:
ALTER TABLE [dbo].[MyTable] DROP
DEFAULT <defaultname>FOR [Column1]
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
GOselect OBJECT_NAME(constid),scl.name
FROM sysconstraints scr
INNER JOIN syscolumns scl
ON scr.id = scl.id
AND scr.colid = scl.colid
WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
AND scr.status = 2069
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
> SyetwemHi There.
> I'd like to alter a DEFAULT I have set up on a database table.
> Unfortunately, I don't know the name of the default as it was created when
> the table was created without specifying a name.
> Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
> System tables)?...
> Or is there a more straightforward way to alter a DEFAULT?
> any help would be appreciated!
> My final lines of code should look something like:
> ALTER TABLE [dbo].[MyTable] DROP
> DEFAULT <defaultname>FOR [Column1]
> GO
> ALTER TABLE [dbo].[MyTable] ADD
> CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
> GO
>|||Thanks - I just tried that though and it seems to only return defaults that
have been named explicitly (If I remove the WHERE clause I can see a list of
all explicitly created defaults from my database)
perhaps there's a way of dropping the default on a column without knowing
the name?...
"Roji. P. Thomas" wrote:
> select OBJECT_NAME(constid),scl.name
> FROM sysconstraints scr
> INNER JOIN syscolumns scl
> ON scr.id = scl.id
> AND scr.colid = scl.colid
> WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
> AND scr.status = 2069
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "len" <len@.discussions.microsoft.com> wrote in message
> news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
>
>|||> perhaps there's a way of dropping the default on a column without knowing
> the name?...
Here you go
DECLARE @.defname VARCHAR(100), @.cmd VARCHAR(1000)
SET @.defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'YourTableName'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.YourTableName) AND
name = 'YourColumnName'))
SET @.cmd = 'ALTER TABLE YourTableName DROP CONSTRAINT '
+ @.defname
EXEC(@.cmd)
Its taken from the following article by Ron Talmage. Have a look
http://msdn.microsoft.com/library/d.../>
ql00a11.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||Thanks guys - they worked a treat!!sql
I'd like to alter a DEFAULT I have set up on a database table.
Unfortunately, I don't know the name of the default as it was created when
the table was created without specifying a name.
Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
System tables)?...
Or is there a more straightforward way to alter a DEFAULT?
any help would be appreciated!
My final lines of code should look something like:
ALTER TABLE [dbo].[MyTable] DROP
DEFAULT <defaultname>FOR [Column1]
GO
ALTER TABLE [dbo].[MyTable] ADD
CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
GOselect OBJECT_NAME(constid),scl.name
FROM sysconstraints scr
INNER JOIN syscolumns scl
ON scr.id = scl.id
AND scr.colid = scl.colid
WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
AND scr.status = 2069
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
> SyetwemHi There.
> I'd like to alter a DEFAULT I have set up on a database table.
> Unfortunately, I don't know the name of the default as it was created when
> the table was created without specifying a name.
> Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or
> System tables)?...
> Or is there a more straightforward way to alter a DEFAULT?
> any help would be appreciated!
> My final lines of code should look something like:
> ALTER TABLE [dbo].[MyTable] DROP
> DEFAULT <defaultname>FOR [Column1]
> GO
> ALTER TABLE [dbo].[MyTable] ADD
> CONSTRAINT [DF_MyTableColumn1] DEFAULT ('DefaultValue') FOR [Column1]
> GO
>|||Thanks - I just tried that though and it seems to only return defaults that
have been named explicitly (If I remove the WHERE clause I can see a list of
all explicitly created defaults from my database)
perhaps there's a way of dropping the default on a column without knowing
the name?...
"Roji. P. Thomas" wrote:
> select OBJECT_NAME(constid),scl.name
> FROM sysconstraints scr
> INNER JOIN syscolumns scl
> ON scr.id = scl.id
> AND scr.colid = scl.colid
> WHERE OBJECT_NAME(scr.id) = 'YourColumnname'
> AND scr.status = 2069
> --
> Roji. P. Thomas
> Net Asset Management
> https://www.netassetmanagement.com
>
> "len" <len@.discussions.microsoft.com> wrote in message
> news:9DFCC435-DAC9-495F-AC64-4FEC20841F04@.microsoft.com...
>
>|||> perhaps there's a way of dropping the default on a column without knowing
> the name?...
Here you go
DECLARE @.defname VARCHAR(100), @.cmd VARCHAR(1000)
SET @.defname =
(SELECT name
FROM sysobjects so JOIN sysconstraints sc
ON so.id = sc.constid
WHERE object_name(so.parent_obj) = 'YourTableName'
AND so.xtype = 'D'
AND sc.colid =
(SELECT colid FROM syscolumns
WHERE id = object_id('dbo.YourTableName) AND
name = 'YourColumnName'))
SET @.cmd = 'ALTER TABLE YourTableName DROP CONSTRAINT '
+ @.defname
EXEC(@.cmd)
Its taken from the following article by Ron Talmage. Have a look
http://msdn.microsoft.com/library/d.../>
ql00a11.asp
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||You can get rid of Defaults with automatically named constraints in a script
with the following bit of code. Just replace the <table name> and <column
names> with your table and column(s):
DECLARE @.constraint_name SYSNAME
-- remove all the defaults
WHILE 1=1
BEGIN
SET @.constraint_name = (SELECT TOP 1 c_obj.name
FROM sysobjects t_obj
INNER JOIN sysobjects c_obj
ON t_obj.id = c_obj.parent_obj
INNER JOIN syscolumns cols
ON cols.colid = c_obj.info
AND cols.id = c_obj.parent_obj
WHERE t_obj.id = OBJECT_ID('<table name>')
AND c_obj.xtype = 'D'
AND cols.[name]IN ('<column names>'))
IF @.constraint_name IS NULL BREAK
EXEC ('ALTER TABLE <table name> DROP CONSTRAINT ' + @.constraint_name)
END
Jacco Schalkwijk
SQL Server MVP
"len" <len@.discussions.microsoft.com> wrote in message
news:EA55D84B-A1D3-4BBF-B979-E85371D2F2DA@.microsoft.com...
> Thanks - I just tried that though and it seems to only return defaults
> that
> have been named explicitly (If I remove the WHERE clause I can see a list
> of
> all explicitly created defaults from my database)
> perhaps there's a way of dropping the default on a column without knowing
> the name?...
> "Roji. P. Thomas" wrote:
>|||Thanks guys - they worked a treat!!sql
ALTER'ing an XML Column
I was wondering, is it possible to ALTER and existing XML column? I need the abiltiy to be able to type the column to a different xsd if the need arose. For example, the column was created and typed to an XSD called UserClaimsXSD. Then later on, the XSD itself changes and needs to be typed to the column.
I was looking through ALTER table but couldn't find anything. I ran SQL PRofiler and saw it is handled via SSMS - it builds a new table, with that new XSD and then copies the adta from the original table into the temp table. It then drops the original table and renames the temp table...
There has to be an easier way...
Thanks!!
You can first alter the column to untyped xml:
ALTER TABLE YourTable ALTER COLUMN xmlColumn xml NOT NULL;
Then modify or create the new schema
Lastly, change the column to the new schema (the data in the table must comply with the new schema)
ALTER TABLE YourTable ALTER COLUMN xmlColumn xml(NewSchemaCollection) NOT NULL;
ALTER'ing an XML Column
I was wondering, is it possible to ALTER and existing XML column? I need the abiltiy to be able to type the column to a different xsd if the need arose. For example, the column was created and typed to an XSD called UserClaimsXSD. Then later on, the XSD itself changes and needs to be typed to the column.
I was looking through ALTER table but couldn't find anything. I ran SQL PRofiler and saw it is handled via SSMS - it builds a new table, with that new XSD and then copies the adta from the original table into the temp table. It then drops the original table and renames the temp table...
There has to be an easier way...
Thanks!!
You can first alter the column to untyped xml:
ALTER TABLE YourTable ALTER COLUMN xmlColumn xml NOT NULL;
Then modify or create the new schema
Lastly, change the column to the new schema (the data in the table must comply with the new schema)
ALTER TABLE YourTable ALTER COLUMN xmlColumn xml(NewSchemaCollection) NOT NULL;
alter view question
how do I find out when was the last time view got modified.You cannot do that in SQL Server 2000
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
>|||"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
You can't (unless SQL 2005 handles this).
You only have the date it was created.
You can:
drop the view and re-create it when you do a modification
add comments to the view about changes
keep the information in a table
use a third party code source tool|||SQL Server 2000 does not store this information. You can garner this
information from a trace, if you want to leave a lightweight one running; or
from 3rd party tools such as Lumigent's Entegra ("who did what to which data
when?" is their catch-slogan).
If you are using SQL Server 2005,
SELECT modify_date FROM sys.views WHERE name='view_name'
(And in 2005 if you want more information, such as who modified it, what app
they used, etc. then you can set up DDL triggers.)
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
>|||I was wondering if I could use trigger to audit the modified date of view,
as trigger is allowed on a view.
"Aaron Bertrand [SQL Server MVP]" wrote:
> SQL Server 2000 does not store this information. You can garner this
> information from a trace, if you want to leave a lightweight one running;
or
> from 3rd party tools such as Lumigent's Entegra ("who did what to which da
ta
> when?" is their catch-slogan).
> If you are using SQL Server 2005,
> SELECT modify_date FROM sys.views WHERE name='view_name'
> (And in 2005 if you want more information, such as who modified it, what a
pp
> they used, etc. then you can set up DDL triggers.)
>
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
>
>|||>I was wondering if I could use trigger to audit the modified date of view,
> as trigger is allowed on a view.
That will fire when the actual DATA changes, not the DEFINITION.|||i don't have sql 2005 installed, but out of curiousity, to find out other
objects' modification date, do you just go to: sys.tables, sys.triggers,
sys.functions, etc?
if I use following query,Can I find out when procedure is last altered in
sql 2005?
I know in sql 2000, last_altered is "fake" column ( data is same as created
date).
but I hope sql 2005 is not like that again.
select routine_name, last_altered from information_schema.routines
"Aaron Bertrand [SQL Server MVP]" wrote:
> That will fire when the actual DATA changes, not the DEFINITION.
>
>|||> I know in sql 2000, last_altered is "fake" column ( data is same as
> created
> date).
> but I hope sql 2005 is not like that again.
> select routine_name, last_altered from information_schema.routines
LAST_ALTERED is correct and accurate in SQL Server 2005, however I urge you
to use the catalog views instead.|||I'm not sure if I understand the original request fully, but in 2005 you can
do DDL triggers that will fire off create, alter, drop statements on all
sorts of meta-data type events. Things like CREATE_TRIGGER (triggers on
your triggers?), ALTER_TABLE, etc. Just do search at the MSDN website for
DDL triggers.
Clint
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ecLMVN2%23FHA.2040@.TK2MSFTNGP14.phx.gbl...
> That will fire when the actual DATA changes, not the DEFINITION.
>|||> I'm not sure if I understand the original request fully, but in 2005 you
> can do DDL triggers that will fire off create, alter, drop statements on
> all sorts of meta-data type events.
Yes, I suggested that, however I am not sure the user is using SQL Server
2005.sql
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
>|||"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
You can't (unless SQL 2005 handles this).
You only have the date it was created.
You can:
drop the view and re-create it when you do a modification
add comments to the view about changes
keep the information in a table
use a third party code source tool|||SQL Server 2000 does not store this information. You can garner this
information from a trace, if you want to leave a lightweight one running; or
from 3rd party tools such as Lumigent's Entegra ("who did what to which data
when?" is their catch-slogan).
If you are using SQL Server 2005,
SELECT modify_date FROM sys.views WHERE name='view_name'
(And in 2005 if you want more information, such as who modified it, what app
they used, etc. then you can set up DDL triggers.)
"Kevin" <Kevin@.discussions.microsoft.com> wrote in message
news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
> how do I find out when was the last time view got modified.
>|||I was wondering if I could use trigger to audit the modified date of view,
as trigger is allowed on a view.
"Aaron Bertrand [SQL Server MVP]" wrote:
> SQL Server 2000 does not store this information. You can garner this
> information from a trace, if you want to leave a lightweight one running;
or
> from 3rd party tools such as Lumigent's Entegra ("who did what to which da
ta
> when?" is their catch-slogan).
> If you are using SQL Server 2005,
> SELECT modify_date FROM sys.views WHERE name='view_name'
> (And in 2005 if you want more information, such as who modified it, what a
pp
> they used, etc. then you can set up DDL triggers.)
>
>
> "Kevin" <Kevin@.discussions.microsoft.com> wrote in message
> news:13E783A9-1EFD-4996-B3CC-A2C4522BE0C0@.microsoft.com...
>
>|||>I was wondering if I could use trigger to audit the modified date of view,
> as trigger is allowed on a view.
That will fire when the actual DATA changes, not the DEFINITION.|||i don't have sql 2005 installed, but out of curiousity, to find out other
objects' modification date, do you just go to: sys.tables, sys.triggers,
sys.functions, etc?
if I use following query,Can I find out when procedure is last altered in
sql 2005?
I know in sql 2000, last_altered is "fake" column ( data is same as created
date).
but I hope sql 2005 is not like that again.
select routine_name, last_altered from information_schema.routines
"Aaron Bertrand [SQL Server MVP]" wrote:
> That will fire when the actual DATA changes, not the DEFINITION.
>
>|||> I know in sql 2000, last_altered is "fake" column ( data is same as
> created
> date).
> but I hope sql 2005 is not like that again.
> select routine_name, last_altered from information_schema.routines
LAST_ALTERED is correct and accurate in SQL Server 2005, however I urge you
to use the catalog views instead.|||I'm not sure if I understand the original request fully, but in 2005 you can
do DDL triggers that will fire off create, alter, drop statements on all
sorts of meta-data type events. Things like CREATE_TRIGGER (triggers on
your triggers?), ALTER_TABLE, etc. Just do search at the MSDN website for
DDL triggers.
Clint
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ecLMVN2%23FHA.2040@.TK2MSFTNGP14.phx.gbl...
> That will fire when the actual DATA changes, not the DEFINITION.
>|||> I'm not sure if I understand the original request fully, but in 2005 you
> can do DDL triggers that will fire off create, alter, drop statements on
> all sorts of meta-data type events.
Yes, I suggested that, however I am not sure the user is using SQL Server
2005.sql
alter view permissions
Hello,
pls help, how can I remove alter view permission for
user who is member of ddladmin db role, select and update
queries shoud remain permited. The user should be able to
edit all other views, stored procedures etc, except this
one view.
thank you for helpHi
Add him to db_datawriter database role but remove him from ddladmin db role
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
> Hello,
> pls help, how can I remove alter view permission for
> user who is member of ddladmin db role, select and update
> queries shoud remain permited. The user should be able to
> edit all other views, stored procedures etc, except this
> one view.
> thank you for help|||Thanks, I have not mentioned about ability to also create
new views, stored procedures etc. This actions are not
permited with db_datawriter role. Any ideas?
>--Original Message--
>Hi
>Add him to db_datawriter database role but remove him
from ddladmin db role
>
>"Gabriel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
update[vbcol=seagreen]
to[vbcol=seagreen]
this[vbcol=seagreen]
>
>.
>|||One method is to grant CREATE permissions to the user. This will allow the
user to create/alter/drop objects that they own but not objects owned by
other users. You can then change ownership of the view in question to a
different user so that it can't be modified. Other users will need to
owner-qualify object names.
Another approach, which IMHO is better, is to employ a separate database for
those objects you don't want to user to modify. The user can then
db_ddladmin role member in your current database but not in the database
containing the sensitive objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:0bbe01c52fa6$ff2468e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks, I have not mentioned about ability to also create
> new views, stored procedures etc. This actions are not
> permited with db_datawriter role. Any ideas?
>
> from ddladmin db role
> message
> update
> to
> this
pls help, how can I remove alter view permission for
user who is member of ddladmin db role, select and update
queries shoud remain permited. The user should be able to
edit all other views, stored procedures etc, except this
one view.
thank you for helpHi
Add him to db_datawriter database role but remove him from ddladmin db role
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
> Hello,
> pls help, how can I remove alter view permission for
> user who is member of ddladmin db role, select and update
> queries shoud remain permited. The user should be able to
> edit all other views, stored procedures etc, except this
> one view.
> thank you for help|||Thanks, I have not mentioned about ability to also create
new views, stored procedures etc. This actions are not
permited with db_datawriter role. Any ideas?
>--Original Message--
>Hi
>Add him to db_datawriter database role but remove him
from ddladmin db role
>
>"Gabriel" <anonymous@.discussions.microsoft.com> wrote in
message
>news:125c01c52f94$9b7e2d10$a501280a@.phx.gbl...
update[vbcol=seagreen]
to[vbcol=seagreen]
this[vbcol=seagreen]
>
>.
>|||One method is to grant CREATE permissions to the user. This will allow the
user to create/alter/drop objects that they own but not objects owned by
other users. You can then change ownership of the view in question to a
different user so that it can't be modified. Other users will need to
owner-qualify object names.
Another approach, which IMHO is better, is to employ a separate database for
those objects you don't want to user to modify. The user can then
db_ddladmin role member in your current database but not in the database
containing the sensitive objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Gabriel" <anonymous@.discussions.microsoft.com> wrote in message
news:0bbe01c52fa6$ff2468e0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thanks, I have not mentioned about ability to also create
> new views, stored procedures etc. This actions are not
> permited with db_datawriter role. Any ideas?
>
> from ddladmin db role
> message
> update
> to
> this
Alter View permission?
MSDN says the following at
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_aa-az_2gtz.asp:
"ALTER VIEW permissions default to members of the db_owner and
db_ddladmin fixed database roles, and to the view owner. These
permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with
SELECT permission on the tables, views, and table-valued functions being
referenced in the view, ..."
The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
statement type to grant permissions on. Trying to GRANT ALTER VIEW
doesn't work. How can I give a user ALTER VIEW permission? Does "not
transferable" mean I can't?
Thanks.
David WalkerYou can add the user to the db_owner or db_ddladmin fixed database roles.
AMB
"DWalker" wrote:
> MSDN says the following at
> http://msdn.microsoft.com/library/d...rl=/library/en-
> us/tsqlref/ts_aa-az_2gtz.asp:
> "ALTER VIEW permissions default to members of the db_owner and
> db_ddladmin fixed database roles, and to the view owner. These
> permissions are not transferable.
> To alter a view, the user must have ALTER VIEW permission along with
> SELECT permission on the tables, views, and table-valued functions being
> referenced in the view, ..."
> The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
> statement type to grant permissions on. Trying to GRANT ALTER VIEW
> doesn't work. How can I give a user ALTER VIEW permission? Does "not
> transferable" mean I can't?
> Thanks.
> David Walker
>|||I ran across the DDLAdmin role, but BOL makes it sound like you can
grant ALTER VIEW permissions. Maybe I'm misreading it -- I suppose
those permissions are only given to members of db_owner and db_ddladmin,
and they can't be granted to anyone else.
The phrase "ALTER VIEW permissions default to members of the db_owner
and db_ddladmin fixed database roles" should have "default to" replaced
by "are restricted to" if that's the case.
It would be nice to be able to grant ALTER VIEW permission on one view
to one user or role.
Thanks, AlejandroMesa.
David Walker
"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:668F79B3-3335-4BC3-860F-67F467C12CEF@.microsoft.com:
> You can add the user to the db_owner or db_ddladmin fixed database
> roles.
>
> AMB
> "DWalker" wrote:
>
>|||You cannot give a user (that is not member of db_owner and db_ddladmin
fixed dabase roles) the permission to alter a specific view (that he
does not own). This is exactly what "not transferable" means (at least,
this is what it means to me).
As I see it, you have the following alternatives:
a) make the user a member of db_ddladmin role: this will enable him to
make any modification to the objects in the database, including
creating and deleting other objects (tables, views, procedures, etc)
b) grant the user the "CREATE VIEW" permission; the views that are
created by him will be will be owned by him, and so he will be able to
modify them
c) create the views in his name, by prefixing them with his user name
instead of "dbo" (without granting him the "CREATE VIEW" permission).
He will be able to modify those views (and to delete them), but he
won't be able to create new views (or other objects).
The problem with the b) and c) alternatives is that only that user will
be able to access those views by specifying only the view's name (the
other users must prefix the view's name with the user name).
Razvan|||It would be NICE if I could grant a user permission to alter a specific
view, but we'll take what we can get...
I'll probably add the user to the ddladmin role, although that's more
power than I would like to give. But the other choices aren't great
either. Thanks.
David
"Razvan Socol" <rsocol@.gmail.com> wrote in
news:1112639463.986674.297650@.f14g2000cwb.googlegroups.com:
> You cannot give a user (that is not member of db_owner and db_ddladmin
> fixed dabase roles) the permission to alter a specific view (that he
> does not own). This is exactly what "not transferable" means (at
> least, this is what it means to me).
> As I see it, you have the following alternatives:
> a) make the user a member of db_ddladmin role: this will enable him to
> make any modification to the objects in the database, including
> creating and deleting other objects (tables, views, procedures, etc)
> b) grant the user the "CREATE VIEW" permission; the views that are
> created by him will be will be owned by him, and so he will be able to
> modify them
> c) create the views in his name, by prefixing them with his user name
> instead of "dbo" (without granting him the "CREATE VIEW" permission).
> He will be able to modify those views (and to delete them), but he
> won't be able to create new views (or other objects).
> The problem with the b) and c) alternatives is that only that user
> will be able to access those views by specifying only the view's name
> (the other users must prefix the view's name with the user name).
> Razvan
>
http://msdn.microsoft.com/library/d...rl=/library/en-
us/tsqlref/ts_aa-az_2gtz.asp:
"ALTER VIEW permissions default to members of the db_owner and
db_ddladmin fixed database roles, and to the view owner. These
permissions are not transferable.
To alter a view, the user must have ALTER VIEW permission along with
SELECT permission on the tables, views, and table-valued functions being
referenced in the view, ..."
The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
statement type to grant permissions on. Trying to GRANT ALTER VIEW
doesn't work. How can I give a user ALTER VIEW permission? Does "not
transferable" mean I can't?
Thanks.
David WalkerYou can add the user to the db_owner or db_ddladmin fixed database roles.
AMB
"DWalker" wrote:
> MSDN says the following at
> http://msdn.microsoft.com/library/d...rl=/library/en-
> us/tsqlref/ts_aa-az_2gtz.asp:
> "ALTER VIEW permissions default to members of the db_owner and
> db_ddladmin fixed database roles, and to the view owner. These
> permissions are not transferable.
> To alter a view, the user must have ALTER VIEW permission along with
> SELECT permission on the tables, views, and table-valued functions being
> referenced in the view, ..."
> The BOL documentation for GRANT doesn't show ALTER VIEW as a permissible
> statement type to grant permissions on. Trying to GRANT ALTER VIEW
> doesn't work. How can I give a user ALTER VIEW permission? Does "not
> transferable" mean I can't?
> Thanks.
> David Walker
>|||I ran across the DDLAdmin role, but BOL makes it sound like you can
grant ALTER VIEW permissions. Maybe I'm misreading it -- I suppose
those permissions are only given to members of db_owner and db_ddladmin,
and they can't be granted to anyone else.
The phrase "ALTER VIEW permissions default to members of the db_owner
and db_ddladmin fixed database roles" should have "default to" replaced
by "are restricted to" if that's the case.
It would be nice to be able to grant ALTER VIEW permission on one view
to one user or role.
Thanks, AlejandroMesa.
David Walker
"examnotes"
<AlejandroMesa@.discussions.microsoft.com> wrote in
news:668F79B3-3335-4BC3-860F-67F467C12CEF@.microsoft.com:
> You can add the user to the db_owner or db_ddladmin fixed database
> roles.
>
> AMB
> "DWalker" wrote:
>
>|||You cannot give a user (that is not member of db_owner and db_ddladmin
fixed dabase roles) the permission to alter a specific view (that he
does not own). This is exactly what "not transferable" means (at least,
this is what it means to me).
As I see it, you have the following alternatives:
a) make the user a member of db_ddladmin role: this will enable him to
make any modification to the objects in the database, including
creating and deleting other objects (tables, views, procedures, etc)
b) grant the user the "CREATE VIEW" permission; the views that are
created by him will be will be owned by him, and so he will be able to
modify them
c) create the views in his name, by prefixing them with his user name
instead of "dbo" (without granting him the "CREATE VIEW" permission).
He will be able to modify those views (and to delete them), but he
won't be able to create new views (or other objects).
The problem with the b) and c) alternatives is that only that user will
be able to access those views by specifying only the view's name (the
other users must prefix the view's name with the user name).
Razvan|||It would be NICE if I could grant a user permission to alter a specific
view, but we'll take what we can get...
I'll probably add the user to the ddladmin role, although that's more
power than I would like to give. But the other choices aren't great
either. Thanks.
David
"Razvan Socol" <rsocol@.gmail.com> wrote in
news:1112639463.986674.297650@.f14g2000cwb.googlegroups.com:
> You cannot give a user (that is not member of db_owner and db_ddladmin
> fixed dabase roles) the permission to alter a specific view (that he
> does not own). This is exactly what "not transferable" means (at
> least, this is what it means to me).
> As I see it, you have the following alternatives:
> a) make the user a member of db_ddladmin role: this will enable him to
> make any modification to the objects in the database, including
> creating and deleting other objects (tables, views, procedures, etc)
> b) grant the user the "CREATE VIEW" permission; the views that are
> created by him will be will be owned by him, and so he will be able to
> modify them
> c) create the views in his name, by prefixing them with his user name
> instead of "dbo" (without granting him the "CREATE VIEW" permission).
> He will be able to modify those views (and to delete them), but he
> won't be able to create new views (or other objects).
> The problem with the b) and c) alternatives is that only that user
> will be able to access those views by specifying only the view's name
> (the other users must prefix the view's name with the user name).
> Razvan
>
Subscribe to:
Posts (Atom)