Altering functions and CHECK constraints

Let's say I create a multi-statement function like this:

CREATE FUNCTION dbo.Test ()
RETURNS @.res TABLE (N int NOT NULL CHECK (N >= 0))
AS
BEGIN

INSERT INTO @.res
SELECT 1

RETURN
END

That works fine. Then I make a change in the function's body, replace the
CREATE FUNCTION with ALTER FUNCTION, and execute the batch. I get an error:

Server: Msg 3729, Level 16, State 3, Procedure Test, Line 9
Cannot ALTER 'dbo.Test' because it is being referenced by object
'CK__Test__N__5D2E32EB'.

Indeed, if I look at the list of dependencies for the function in QA's
object tree, I can see the check constraint referenced in the error
message.

ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
definition of the @.res table.

So it seems that the only way to modify such a function is to drop and
recreate. Is that a known behavior? Is there any particular reason for it?

Thanks.

--
(remove a 9 to reply by email)Dimitri Furman (dfurman@.cloud99.net) writes:
> ALTER FUNCTION works fine if I don't specify the CHECK constraint in the
> definition of the @.res table.
> So it seems that the only way to modify such a function is to drop and
> recreate. Is that a known behavior? Is there any particular reason for it?

I will have to admit that I was not aware of this. As for why, my guess
is that this is an artefact of the metadata structure in SQL Server, and
the SQL Server developers did not write the necessary code to avoid this.

Anyway, the restriction is not there in SQL 2005, so whatever the reason
for this in SQL 2000, it is not likely to be a compelling one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:

>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
Hugo Kornelis, SQL Server MVP

ALTER TABLE/COLUMN syntax

Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
AndrisSimply add the default with an ALTER TABLE:
ALTER TABLE firmNoliktava_test
ADD CONSTRAINT DF1_firmNoliktava_test
DEFAULT 1 FOR valstsID
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"Andris" <spameris@.gmail.com> wrote in message
news:eY4DCdHiGHA.3956@.TK2MSFTNGP02.phx.gbl...
Hi!
I want a add default value to existing column with int type with
following syntax:
ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
but got error
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Server SQL 2005 x64, in server Help Contents i see example
ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
Corporation'
What i do wrong ?
Sry my poor Eng.
Andris|||On Mon, 05 Jun 2006 11:08:02 +0300, Andris wrote:
>Hi!
>I want a add default value to existing column with int type with
>following syntax:
>ALTER TABLE firmNoliktava_test ALTER COLUMN valstsID SET DEFAULT (1)
>but got error
>Msg 156, Level 15, State 1, Line 2
>Incorrect syntax near the keyword 'SET'.
>Server SQL 2005 x64, in server Help Contents i see example
>ALTER TABLE MyCustomers ALTER COLUMN CompanyName SET DEFAULT 'A. Datum
>Corporation'
>
>What i do wrong ?
Hi Andris,
The example you have seen is not for SQL Server, but for SQL Server
Mobile edition. There are many syntax difference between "normal" SQL
Server and the mobile version. I've been tricked by this myself quite a
few times already - just remember to always check the heading of the
subject in Books Online to check if you're looking at a Mobile or a
T-SQL subject.
--
Hugo Kornelis, SQL Server MVP

Alter table with PRIMARY KEY

I have an existing table (with records) having the ff: structure:

CREATE TABLE [dbo].[TEMP2_WORKORDER] (
[WorkOrderID] [int] IDENTITY (1, 1) NOT NULL ,
[JobType] [varchar] (3) NULL ,
[JobID] [varchar] (10) NULL ,

I want to be modify the structure to add a PRIMARY KEY to the [WorkOrderID] column.

I was using ALTER TABLE but can't get the right syntax. Please help!

ThanksALTER TABLE dbo.TEMP2_WORKORDER ADD CONSTRAINT
PK_testtable PRIMARY KEY CLUSTERED
(
WorkOrderID
)|||Thank you. It did the trick!

ALTER TABLE to add NOT NULL fields

I'm using the following statement to create two new fields in a table:
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
When I run it in QA, I get this error message:
ALTER TABLE only allows columns to be added that can contain nulls or have a
DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
'tblActivity' because it does not allow nulls and does not specify a DEFAULT
definition.
How can I get those two fields to be created via the ALTER TABLE statement,
I can not use Enterprise Manager, since the statement that I'm using is bein
g
generated by another script to add those fields to all the tables in the
database...You need to specify a default value...
ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME] NOT
NULL
DEFAULT( 0 )
Make sure whatever default value you specify is meaningful to your
application; also, you could drop the DEFAULT constraint afterward...
ALTER TABLE ... DROP CONSTRAINT ...
Example...
create table t (
id int null )
insert t values( 1 )
insert t values( null )
go
alter table t add mycol int not null default( 0 )
go
sp_help t
go
alter table t drop constraint DF__t__mycol__781FBE44
Tony.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...|||To add on to Tony's response, you can specify an explicit constraint name to
make subsequent table maintenance easier:
ALTER TABLE tblActivity
ADD
LOGUserID [INT] NOT NULL
CONSTRAINT DF_tblActivity_LOGUserID DEFAULT( 0 ),
LOGDATE [DATETIME] NOT NULL
CONSTRAINT DF_tblActivity_LOGDATE DEFAULT( GETDATE() )
Hope this helps.
Dan Guzman
SQL Server MVP
"scuba79" <scuba79@.discussions.microsoft.com> wrote in message
news:EB8AF4A5-1217-4ADD-8E41-291A1E812B46@.microsoft.com...
> I'm using the following statement to create two new fields in a table:
> ALTER TABLE tblActivity ADD LOGUserID [INT] NOT NULL, LOGDATE [DATETIME]
> NOT
> NULL
> When I run it in QA, I get this error message:
> ALTER TABLE only allows columns to be added that can contain nulls or have
> a
> DEFAULT definition specified. Column 'LOGUserID' cannot be added to table
> 'tblActivity' because it does not allow nulls and does not specify a
> DEFAULT
> definition.
> How can I get those two fields to be created via the ALTER TABLE
> statement,
> I can not use Enterprise Manager, since the statement that I'm using is
> being
> generated by another script to add those fields to all the tables in the
> database...sql

ALTER Table query

Hi, I just want to know to turn this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]
into this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] IDENTITY (1, 1) NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]

using an ALTER TABLE query. I tried using:
ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
(1, 1) NOT NULL but it's not working. Anyone has any idea how I could
do it? Thanks.Heist (advertiseallyouwant@.hotmail.com) writes:
> Hi, I just want to know to turn this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> into this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] IDENTITY (1, 1) NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> using an ALTER TABLE query. I tried using:
> ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
> (1, 1) NOT NULL but it's not working. Anyone has any idea how I could
> do it? Thanks.

You cannot use ALTER TABLE to change a column into IDENTITY column
(except on SQL Server CE!). One way is to rename the table, create
a new and move over the data. You need to have SET IDENTITY_INSERT
on for the table when you move the data.

You can also do it in Enterprise Mangager - which will renamed and
move data behind the scenes.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What I ended up doing is using SQL Server Entreprise Manager to
"manually" alter the table and then I used the script generator to
create a script I could then used. Thanks.sql

alter table problem?

in a stored procedure , i've the following coding
...
create table tbl (
a int, b int
)
insert into tbl values (1, 2)
alter table tbl drop column b
select * from tbl
...
sql server returns "Column name or number of supplied values does not match
table definition."
Could anyone help me please.the table is temp table
"Win" <aaa@.aaa.com> wrote in message
news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not
match
> table definition."
> Could anyone help me please.
>|||Win
create table #tbl (
a int, b int
)
insert into #tbl values (1, 2)
GO
alter table #tbl drop column b
GO
select * from #tbl
drop table #tbl
"Win" <aaa@.aaa.com> wrote in message
news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
> the table is temp table
> "Win" <aaa@.aaa.com> wrote in message
> news:#XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...
> match
>|||Cannot alter table '#tbl' because this table does not exist in database
'abc'.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
> Win
> create table #tbl (
> a int, b int
> )
> insert into #tbl values (1, 2)
> GO
> alter table #tbl drop column b
> GO
> select * from #tbl
> drop table #tbl
>
> "Win" <aaa@.aaa.com> wrote in message
> news:Ohom9m1OGHA.3944@.tk2msftngp13.phx.gbl...
>|||Win
On my machine it works file. What version are you using?
"Win" <aaa@.aaa.com> wrote in message
news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
> Cannot alter table '#tbl' because this table does not exist in database
> 'abc'.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:ekwECK2OGHA.3728@.tk2msftngp13.phx.gbl...
>|||The procedure is parsed as a unit, before any execution has taken place. So,
when the SELECT is
parsed, the ALTER hasn't occurred yet, hence the error message. This is expe
cted. If you post the
logic behind doing this, someone might provide an alternative, or you can us
e dynamic SQL
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Win" <aaa@.aaa.com> wrote in message news:%23XRiRh1OGHA.2124@.TK2MSFTNGP14.phx.gbl...darkred">
> in a stored procedure , i've the following coding
> ...
> create table tbl (
> a int, b int
> )
> insert into tbl values (1, 2)
> alter table tbl drop column b
> select * from tbl
> ...
> sql server returns "Column name or number of supplied values does not matc
h
> table definition."
> Could anyone help me please.
>|||my coding is inside a stored procedure
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uToyZl2OGHA.428@.tk2msftngp13.phx.gbl...
> Win
> On my machine it works file. What version are you using?
>
> "Win" <aaa@.aaa.com> wrote in message
> news:OD1%23CO2OGHA.1696@.TK2MSFTNGP14.phx.gbl...
not
>

Alter table errors due to statistics

I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
GaryRun:
drop statistics hind_61_3
and then do your ALTER TABLE.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewsservers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>> Run:
>> drop statistics hind_61_3
>> and then do your ALTER TABLE.
>> --
>> Tom
>> ---
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Columnist, SQL Server Professional
>> Toronto, ON Canada
>> www.pinnaclepublishing.com
>>
>> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
>> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewsservers.com...
>> I'm attempting to change a column data type from int to nvarchar(16) on a
>> production database. When executing:
>> alter table x alter column y nvarchar(16)
>> I get the error:
>> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
>> this
>> column
>>
>> I would be forever grateful if someone could tell me how to get around
>> this
>> issue.
>> Thanks in advance,
>> Gary
>>
>

Alter table errors due to statistics

I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
Run:
drop statistics hind_61_3
and then do your ALTER TABLE.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
I'm attempting to change a column data type from int to nvarchar(16) on a
production database. When executing:
alter table x alter column y nvarchar(16)
I get the error:
ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses this
column
I would be forever grateful if someone could tell me how to get around this
issue.
Thanks in advance,
Gary
|||Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||You probably have auto-create stats and auto-update stats turned on. This
is normal. If SQL Server figures it needs stats on that column, then it
creates them. However, if you decide to alter the column, the stats are a
dependency on that column in the same wan an index or constraint is. You
have to drop those dependencies first before altering the column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
Thank you. If I could trouble you once more, how would this get in there?
We've updated hundreds of customers and have found this error on but one
site...
Again, thank you!
Gary
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
> Run:
> drop statistics hind_61_3
> and then do your ALTER TABLE.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
> news:8c236$41c72f45$44a72b52$17509@.msgid.meganewss ervers.com...
> I'm attempting to change a column data type from int to nvarchar(16) on a
> production database. When executing:
> alter table x alter column y nvarchar(16)
> I get the error:
> ALTER TABLE ALTER COLUMN y failed because STATISTICS hind_61_3 accesses
> this
> column
>
> I would be forever grateful if someone could tell me how to get around
> this
> issue.
> Thanks in advance,
> Gary
>
>
|||The hind_ statistics are really not statistics, but Hypothetical INDexes,
created by the Index Tuning Wizard, which normally are cleaned up up when
ITW finishes. There are some situations where it doesn't clean up after
itself, so you have to do it with DROP STATISTICS. Since it is a very rare
occurrence to have these left behind, it's not surprising that you don't see
this error very often.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Gary Johnson" <gary.johnson@.geoffreynyc.com> wrote in message
news:15de1$41c73bd8$44a72b52$18777@.msgid.meganewss ervers.com...
> Thank you. If I could trouble you once more, how would this get in there?
> We've updated hundreds of customers and have found this error on but one
> site...
> Again, thank you!
> Gary
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OnHXHCt5EHA.344@.TK2MSFTNGP10.phx.gbl...
>
sql

alter table - change time evaluation

Hi,

I have a table with 70 million records. I want to change the datatype of one of the columns from int to decimal(18,3). What is the best way of doing it? and how can I evaluate the time change before I start and make the change?

thank you,

Tomer

..assuming you want to know how long the operation would take....this varies massively by table definition + indexes + hardware + load

you could use SELECT INTO to create a table of one million records and add indexes identical to those on the production table.

Then drop the index on the int column on that table,

execute an ALTER TABLE ALTER COLUMN to change the datatype

and rebuild the index.

Then multiply the result by 70 though that's only an estimate as the rebuild time may not be linear.

Alter table - add default value

Helo Group,
In my database I have table :
idDoc (int) IDENTITY (1, 1),
UploadDate (datetime)
DocName (varchar).
Now I ought too add default value (getdate()) for new document.
How I can use Alter table for update structure my table.
thx
PawelRHi Pawel
The Books Online page for ALTER TABLE has a section called "Adding a default
constraint to an existing column". Books Online should always be the first
place you look for syntax help. I realize that ALTER TABLE is a long
article, but the information you need is there.
ALTER TABLE my_table
ADD CONSTRAINT col_uploadDate_def
DEFAULT getdate() FOR uploadDate
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"PawelR" <pawelratajczak;-at-;poczta;dot;onet;dot;pl> wrote in message
news:umBxwWVbGHA.4292@.TK2MSFTNGP04.phx.gbl...
> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>|||You can update default constraint using Enterprise Manager.
In table design, you can spcify default value for uploaddate column.
"PawelR"?? ??? ??:

> Helo Group,
> In my database I have table :
> idDoc (int) IDENTITY (1, 1),
> UploadDate (datetime)
> DocName (varchar).
> Now I ought too add default value (getdate()) for new document.
> How I can use Alter table for update structure my table.
> thx
> PawelR
>
>

Alter several columns using ALTER COLUMN

Hi!

I have a table with 6 columns of type int. I would like to alter this table and set these columns to float. Is it possible to do it using only one statement? I don't want to use the following query six times!

ALTER TABLE T1

ALTER COLUMN C1 FLOAT NOT NULL

Thank you!

What happens when you try to alter multiple columns in one statement?

alter indentity field

Hi Guys,
I'm using SQL server 2000.

How do I alter column/field from type int (with Identity = Yes Not For Replication) to just normail int field. No more identity. I want it to be done using SQL script( sql query analyzer).

Please help me on this, thx

Regards,
ShaffiqHi Guys,
I'm using SQL server 2000.

How do I alter column/field from type int (with Identity = Yes Not For Replication) to just normail int field. No more identity. I want it to be done using SQL script( sql query analyzer).

Please help me on this, thx

Regards,
Shaffiq

alter table table_name
alter column column_Name int not null


that should solve your problem.|||Hi Enigma,
I'd tried it before but it not work. Even query analyzer return success message "The command(s) completed successfully." but when I open the table it still the same. And the identity field still ON

Regards,
Shaffiq|||do alteration in Enterprise Manager( dont save it) and click on 'save change script'(3 rd button from second row).copy and run that script in query analyser|||or

Alter Table MyTable ADD NewColumn int
GO
UPDATE MyTAble SET NewColumn = OldColumn
GO
ALTER TABLE MyTable DROP COLUMN MyCOlumn
GO
sp_rename 'MyTable.NewColumn','OldColumn',COLUMN

Alter identity -field?

Hello.
I have a table with int identity field (X INT IDENTITY(1,1)).
I want update SEED value to 4000.
I cannot drop column because I have a foreign key to it from other table.
How I can do this (update/alter identity's SEED value to column)?
dbcc checkident
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>
|||Hi,
Execute the below command, replace the dbname and table name with actual
USE DBNAME
GO
DBCC CHECKIDENT (tablename, RESEED, 4000)
Thanks
Hari
SQL Server MVP
"Major" <lievonen@.jyu.fi.HALOOOOOOOO> wrote in message
news:OsdhknwxEHA.2540@.TK2MSFTNGP15.phx.gbl...
> Hello.
> I have a table with int identity field (X INT IDENTITY(1,1)).
> I want update SEED value to 4000.
> I cannot drop column because I have a foreign key to it from other table.
> How I can do this (update/alter identity's SEED value to column)?
>

Alter existing identity column to be non-identity column

I need to change table's identity column to just be int column. Can someone
shed a light? Thanks,
DDL:
alter table mytable
alter column IdentFld intThere is not a simple way of doing this. You have to add a new column, copy
data from identity one, drop foreign key constraints referencing the identit
y
column, drop the identity column, rename new column, add foreign key
constraints, etc.
AMB
"Sean" wrote:

> I need to change table's identity column to just be int column. Can someon
e
> shed a light? Thanks,
> DDL:
> alter table mytable
> alter column IdentFld int
>|||Thank you for your reply. It is very useful. I didn't know it's been that ha
rd.
"Alejandro Mesa" wrote:
> There is not a simple way of doing this. You have to add a new column, cop
y
> data from identity one, drop foreign key constraints referencing the ident
ity
> column, drop the identity column, rename new column, add foreign key
> constraints, etc.
>
> AMB
>
> "Sean" wrote:
>|||...or do it inside Enterprise Manager.
It does the same work as Alejandro explained though.
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:78C1A0BA-47E6-4EBF-9051-8F9E26A89FCB@.microsoft.com...
> Thank you for your reply. It is very useful. I didn't know it's been that
> hard.
> "Alejandro Mesa" wrote:
>

ALTER COLUMN?

I want to change a column in a table. The data type is int, and it allows
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

ALTER COLUMN?

I want to change a column in a table. The data type is int, and it allows
NULLs. I want to make it non-nullable and give it a DEFAULT value of 0. I've
looked up ALTER TABLE in BOL but I don't seem to see what the syntax is to
accomplish this. Is it even possible? TIA!You can do someting like this
CREATE TABLE TESTDEFAULTS (
ID INT)
INSERT INTO TESTDEFAULTS
VALUES (1)
SELECT *
FROM TESTDEFAULTS
ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
INSERT INTO TESTDEFAULTS
DEFAULT VALUES
SELECT *
FROM TESTDEFAULTS
--This will give an error now
INSERT INTO TestDefaultS VALUES (NULL)
DROP TABLE TESTDEFAULTS
Denis the SQL Menace
http://sqlservercode.blogspot.com/|||Thanks! At first I got an error but once I UPDATEd the column to 0 WHERE it
IS NULL, voila! Again, thank you!
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1147462107.839063.66230@.d71g2000cwd.googlegroups.com...
> You can do someting like this
> CREATE TABLE TESTDEFAULTS (
> ID INT)
> INSERT INTO TESTDEFAULTS
> VALUES (1)
> SELECT *
> FROM TESTDEFAULTS
> ALTER TABLE TESTDEFAULTS ALTER COLUMN ID INT NOT NULL
> ALTER TABLE TestDefaultS ADD CONSTRAINT IDNotNull DEFAULT (0) FOR [ID]
> INSERT INTO TESTDEFAULTS
> DEFAULT VALUES
>
> SELECT *
> FROM TESTDEFAULTS
> --This will give an error now
> INSERT INTO TestDefaultS VALUES (NULL)
> DROP TABLE TESTDEFAULTS
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>

Alter Column Type.

Hello,

Easy one for the SQL experts.

I have a simple table. For the example let's say it looks like this:
CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)

Now I want to alter this table and make the column column_a a float
instead of an INT.
How do I do that? I cannot DROP and ADD the column I would lose some
very precious data.

Thanks a lot"luc h" <lhovhannessian@.hotmail.com> wrote in message
news:d4175164.0402250955.2802d9e3@.posting.google.c om...
> Hello,
> Easy one for the SQL experts.
> I have a simple table. For the example let's say it looks like this:
> CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL)
> Now I want to alter this table and make the column column_a a float
> instead of an INT.
> How do I do that? I cannot DROP and ADD the column I would lose some
> very precious data.
> Thanks a lot

See ALTER TABLE in Books Online:

alter table dbo.doc_exb
alter column column_a float

Simon

Alter Column Question

Hello,
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
David Portas

Alter Column Question

Hello,
I have a table with 10million rows where I need to change the datatype on a
column from int to bigint. I'm assuming this will take a long time to
complete by just going in to Management Studio changing the datatype and
saving.
Any tips on speeding up the process?
Thanks in advance.
Any help appreciated!"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:B6BE9290-1C47-4F43-8E87-BE399792BE21@.microsoft.com...
> Hello,
> I have a table with 10million rows where I need to change the datatype on
> a
> column from int to bigint. I'm assuming this will take a long time to
> complete by just going in to Management Studio changing the datatype and
> saving.
> Any tips on speeding up the process?
> Thanks in advance.
> Any help appreciated!
Do not use the Management Studio UI to do such things. Create an ALTER
script, test it in a test environment then run it on your target system. The
Management Studio UI is not the best way because it does some things behind
the scenes that may not be obvious unless you review the commands before you
execute them.
--
David Portas