Altering a column which has an index defined on it

Hello,

I'm trying the following test (which works like a charm on Oracle)

create table x1(c1 numeric(10), c2 numeric(5,1))
create index x1_c2_idx on x1(c2)
alter table x1 alter column c2 numeric(9,1)

I get the following error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'x1_c2_idx' is dependent on column 'c2'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN c2 failed because one or more objects access this column.

Is there a way to alter the column WITHOUT dropping the index ?



Regards,

Tal Olier
otal@.mercury.co.ilNo, you must drop the index first.|||Originally posted by Paul Young
No, you must drop the index first.

Thanks.

Alter UserDefined Datatype

How can I alter a userdefined datatype.

eg: A user defined datatype of Dt_Quantity numeric(16,0)

Want to change it to numeric(16,3).

Is it possible?

If it requires a system catelog updation please help.

This requirment is came after the implementation of the DataBase.

Please help me fast|||which version of sql server are you using ?|||Using the SQL Server 2005|||

you have to drop it and recreate...

From BOL :

Note: User-defined types cannot be modified after they are created, because changes could invalidate data in the tables or indexes. To modify a type, you must either drop the type and then re-create it, or issue an ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause. For more information, see ALTER ASSEMBLY (Transact-SQL).

Madhu

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?
The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>

alter user defined data type length

I have one UDD, which is used in some tables.
Now i want to change its length. How can i do that ?The only way i know of is to drop it and re-create it...
sp_droptype
sp_addtype
but you can't drop a type if it's being referenced by any columns
The way to go would be this:
EXEC sp_rename UDName, old_UDName
EXEC sp_addtype UDName, 'varchar(20)'
ALTER TABLE tbl1 ALTER COLUMN col1 UDName
ALTER TABLE tbl2 ALTER COLUMN col2 UDName
HTH. Ryan
"Vikram" <aa@.aa> wrote in message
news:e7qkGn9KGHA.2012@.TK2MSFTNGP14.phx.gbl...
>I have one UDD, which is used in some tables.
> Now i want to change its length. How can i do that ?
>sql

Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!
Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

Alter to specify a fillfactor?

For SQL2000 - I would like to be able to alter indexes on a table to specify
a fillfactor where previously a fillfactor was not defined.
I first tried this on one table in Enterprise Manager where I specified a
fillfactor of 80 for the clustered index. I ran profiler to see how it was
done. It looks like it dropped the Clustered Index and then rebuilt it.
I was hoping that there was an ALTER statement that I could run that would
effectively update the fillfactor definition so that the next time I ran
DBREINDEX it would take effect. Is this possible.
Thanks in advance!Fillfactor is not maintained during regular DML operation; it only matters
when an index is built. Therefore, there is little or no need to introduce
ALTER INDEX statement to specify a fillfactor that will not be used. When
you are ready to build/re-build index, you can specify fillfactor in DBCC
DBREINDEX/CREATE INDEX statement; after the index is built, the fillfactor
number is stored in system table for future index build to use.
--
Stephen Jiang
Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJT" <TJT@.nospam.com> wrote in message
news:%23k0I981cFHA.3040@.TK2MSFTNGP14.phx.gbl...
> For SQL2000 - I would like to be able to alter indexes on a table to
> specify
> a fillfactor where previously a fillfactor was not defined.
> I first tried this on one table in Enterprise Manager where I specified a
> fillfactor of 80 for the clustered index. I ran profiler to see how it
> was
> done. It looks like it dropped the Clustered Index and then rebuilt it.
> I was hoping that there was an ALTER statement that I could run that would
> effectively update the fillfactor definition so that the next time I ran
> DBREINDEX it would take effect. Is this possible.
> Thanks in advance!
>

ALTER TABLE question

Hi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I want to change the nullability of some columns and add a
primary key constraint to a table. When I monitor what the
ALTER TABLE really does (using Profiler), I see strange
inserts and updates like "insert [dbo].[table] select *
from [dbo].[table]" or "update [dbo].[table] set [column] =
[column]". What are they for, and will these inserts and
updates activate any triggers/defaults/constraints defined
for a table?
I use SQL Server 2000 SP3a.
Many thanks,
Osk
Are you doing this in Enterprise Manager or Query Analyzer.
You really want to be using Query Analyzer to make these
kind of changes.
-Sue
On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>defaults/observe any constraints defined on a table?
>I want to change the nullability of some columns and add a
>primary key constraint to a table. When I monitor what the
>ALTER TABLE really does (using Profiler), I see strange
>inserts and updates like "insert [dbo].[table] select *
>from [dbo].[table]" or "update [dbo].[table] set [column] =
>[column]". What are they for, and will these inserts and
>updates activate any triggers/defaults/constraints defined
>for a table?
>I use SQL Server 2000 SP3a.
|||I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
ALTER TABLE command as far as I know. But what about
triggers/constraint - will they bey fired/observed?
Thanks,
Osk

>--Original Message--
>Are you doing this in Enterprise Manager or Query Analyzer.
>You really want to be using Query Analyzer to make these
>kind of changes.
>-Sue
>On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>
|||On Thu, 20 Jan 2005 22:39:58 -0800, Osk wrote:

>I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
>ALTER TABLE command as far as I know. But what about
>triggers/constraint - will they bey fired/observed?
Hi Osk,
Easy to test, isn't it?
CREATE TABLE Test (Col1 int NULL,
Col2 int NULL)
go
CREATE TRIGGER TestTrig ON Test
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'I''m fired!'
go
ALTER TABLE Test
ALTER COLUMN Col1 int NOT NULL
go
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY (Col1)
go
DROP TABLE Test
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

ALTER TABLE question

Hi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I want to change the nullability of some columns and add a
primary key constraint to a table. When I monitor what the
ALTER TABLE really does (using Profiler), I see strange
inserts and updates like "insert [dbo].[table] select *
from [dbo].[table]" or "update [dbo].[table] set [column
] =
[column]". What are they for, and will these inserts and
updates activate any triggers/defaults/constraints defined
for a table?
I use SQL Server 2000 SP3a.
Many thanks,
OskAre you doing this in Enterprise Manager or Query Analyzer.
You really want to be using Query Analyzer to make these
kind of changes.
-Sue
On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>defaults/observe any constraints defined on a table?
>I want to change the nullability of some columns and add a
>primary key constraint to a table. When I monitor what the
>ALTER TABLE really does (using Profiler), I see strange
>inserts and updates like "insert [dbo].[table] select *
>from [dbo].[table]" or "update [dbo].[table] set [colum
n] =
>[column]". What are they for, and will these inserts and
>updates activate any triggers/defaults/constraints defined
>for a table?
>I use SQL Server 2000 SP3a.|||I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
ALTER TABLE command as far as I know. But what about
triggers/constraint - will they bey fired/observed?
Thanks,
Osk

>--Original Message--
>Are you doing this in Enterprise Manager or Query Analyzer.
>You really want to be using Query Analyzer to make these
>kind of changes.
>-Sue
>On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
><anonymous@.discussions.microsoft.com> wrote:
>
>.
>|||On Thu, 20 Jan 2005 22:39:58 -0800, Osk wrote:

>I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
>ALTER TABLE command as far as I know. But what about
>triggers/constraint - will they bey fired/observed?
Hi Osk,
Easy to test, isn't it?
CREATE TABLE Test (Col1 int NULL,
Col2 int NULL)
go
CREATE TRIGGER TestTrig ON Test
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'I''m fired!'
go
ALTER TABLE Test
ALTER COLUMN Col1 int NOT NULL
go
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY (Col1)
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

ALTER TABLE question

Hi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I use SQL Server 2000 SP3a.
--
Many thanks,
OskHi,
Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
defaults/observe any constraints defined on a table?
I want to change the nullability of some columns and add a
primary key constraint to a table. When I monitor what the
ALTER TABLE really does (using Profiler), I see strange
inserts and updates like "insert [dbo].[table] select *
from [dbo].[table]" or "update [dbo].[table] set [column] =[column]". What are they for, and will these inserts and
updates activate any triggers/defaults/constraints defined
for a table?
I use SQL Server 2000 SP3a.
--
Many thanks,
Osk|||Are you doing this in Enterprise Manager or Query Analyzer.
You really want to be using Query Analyzer to make these
kind of changes.
-Sue
On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
<anonymous@.discussions.microsoft.com> wrote:
>Hi,
>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>defaults/observe any constraints defined on a table?
>I want to change the nullability of some columns and add a
>primary key constraint to a table. When I monitor what the
>ALTER TABLE really does (using Profiler), I see strange
>inserts and updates like "insert [dbo].[table] select *
>from [dbo].[table]" or "update [dbo].[table] set [column] =>[column]". What are they for, and will these inserts and
>updates activate any triggers/defaults/constraints defined
>for a table?
>I use SQL Server 2000 SP3a.|||I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
ALTER TABLE command as far as I know. But what about
triggers/constraint - will they bey fired/observed?
--
Thanks,
Osk
>--Original Message--
>Are you doing this in Enterprise Manager or Query Analyzer.
>You really want to be using Query Analyzer to make these
>kind of changes.
>-Sue
>On Thu, 20 Jan 2005 04:14:02 -0800, "Osk"
><anonymous@.discussions.microsoft.com> wrote:
>>Hi,
>>Will ALTER TABLE/ALTER COLUMN fire any triggers/fill any
>>defaults/observe any constraints defined on a table?
>>I want to change the nullability of some columns and add a
>>primary key constraint to a table. When I monitor what the
>>ALTER TABLE really does (using Profiler), I see strange
>>inserts and updates like "insert [dbo].[table] select *
>>from [dbo].[table]" or "update [dbo].[table] set [column] =>>[column]". What are they for, and will these inserts and
>>updates activate any triggers/defaults/constraints defined
>>for a table?
>>I use SQL Server 2000 SP3a.
>.
>|||On Thu, 20 Jan 2005 22:39:58 -0800, Osk wrote:
>I'm using Query Analyzer, yes. Ent. Man. doesn't issue the
>ALTER TABLE command as far as I know. But what about
>triggers/constraint - will they bey fired/observed?
Hi Osk,
Easy to test, isn't it?
CREATE TABLE Test (Col1 int NULL,
Col2 int NULL)
go
CREATE TRIGGER TestTrig ON Test
AFTER INSERT, UPDATE, DELETE
AS
PRINT 'I''m fired!'
go
ALTER TABLE Test
ALTER COLUMN Col1 int NOT NULL
go
ALTER TABLE Test
ADD CONSTRAINT pk_Test PRIMARY KEY (Col1)
go
DROP TABLE Test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now for some reason instead of using the index for it is using a sequential scan. I have updated the statistics, rebuilt the indexes and about everthing else I can think of. It simply refuses to use the index it did prior to the alter
Anyone have a clue as to what is going on?Is the comparison done against a variable or another column which is of the
float datatype? Float has higher datatype precedence, so the decimal need to
first be converted to float before that comparison can be performed which
prohibits the usage of index. If you code the code, or preferable a
simplified example that displays the behavior we might be able to comment...
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
--
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...
> I altered a table that had sever columns defined as float to decimal. Now
for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.
> Anyone have a clue as to what is going on?|||The columns that were altered are NOT part of the index nor are they used in the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
--
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...
> The columns that were altered are NOT part of the index nor are they used
in the criteria of the query.
>

Alter table changes optimizer query plan

I altered a table that had sever columns defined as float to decimal. Now f
or some reason instead of using the index for it is using a sequential scan.
I have updated the statistics, rebuilt the indexes and about everthing els
e I can think of. It simply
refuses to use the index it did prior to the alter.
Anyone have a clue as to what is going on?Is the comparison done against a variable or another column which is of the
float datatype? Float has higher datatype precedence, so the decimal need to
first be converted to float before that comparison can be performed which
prohibits the usage of index. If you code the code, or preferable a
simplified example that displays the behavior we might be able to comment...
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...

quote:

---

> I altered a table that had sever columns defined as float to decimal. Now

---

for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.

quote:

---

> Anyone have a clue as to what is going on?

---

|||Hi Redmund,
Are you comparing the column with a variable of data type float?
In that case, due to the rules of data type precedence, the decimal will be
implicitly converted into a float, and the implicit convert prevents the use
of an index on the column.
Change the variable to decimal as well.
Jacco Schalkwijk
SQL Server MVP
"Redmud" <anonymous@.discussions.microsoft.com> wrote in message
news:5022BD01-645D-48B2-B7C7-D8D73FA1C2A7@.microsoft.com...

quote:

---

> I altered a table that had sever columns defined as float to decimal. Now

---

for some reason instead of using the index for it is using a sequential
scan. I have updated the statistics, rebuilt the indexes and about
everthing else I can think of. It simply refuses to use the index it did
prior to the alter.

quote:

---

> Anyone have a clue as to what is going on?

---

|||The columns that were altered are NOT part of the index nor are they used in
the criteria of the query.|||Hi,
Can you posts your table(s), indexes and query, so that we can study that?
Jacco Schalkwijk
SQL Server MVP
"RedMud" <anonymous@.discussions.microsoft.com> wrote in message
news:810E5CE2-29CE-490B-BFFD-5A58EA99048B@.microsoft.com...

quote:

---

> The columns that were altered are NOT part of the index nor are they used

---

in the criteria of the query.

quote:

---

>

---

Allowing multi-element user defined custom data

We are creating a phonebook application which allows users to add custom
data to each entry, in the form of a (name):(value) pair. The users should
be able to add names of custom data types to a look-up table, and then be
able to add data values of that "type" to any entry in the phonebook. The
data will be saved as nvarchar.
However, we now realize some of this custom data will have to be made up
of several data elements in itself. For example: If the users want to add
the data type "address at in-law's", this custom data will be more than just
(name):(long string value), it will have to be (name):((street)(city)(state)(zipcode)).
We have several ideas on how to do this but they all seem cumbersome, and
they complicate the design a lot. Has anyone created a system like this before?
Is there a tried and true way of doing this?Hi
Have you thought of using XML for this?
John
"Ido Kalir" wrote:
> We are creating a phonebook application which allows users to add custom
> data to each entry, in the form of a (name):(value) pair. The users should
> be able to add names of custom data types to a look-up table, and then be
> able to add data values of that "type" to any entry in the phonebook. The
> data will be saved as nvarchar.
> However, we now realize some of this custom data will have to be made up
> of several data elements in itself. For example: If the users want to add
> the data type "address at in-law's", this custom data will be more than just
> (name):(long string value), it will have to be (name):((street)(city)(state)(zipcode)).
> We have several ideas on how to do this but they all seem cumbersome, and
> they complicate the design a lot. Has anyone created a system like this before?
> Is there a tried and true way of doing this?
>