Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.
Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegr oups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
Showing posts with label environment. Show all posts
Showing posts with label environment. Show all posts
Sunday, March 25, 2012
Alter table with Merge replication
Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
Alter table with Merge replication
Hi,
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
I have SQL server 2000 merge replication environment.
How can i propagate Add default constraint command on an existing
column withuot runnning the command on every subscriber.
I know i can use sp_repladdcolumn to add a column in the publisher and
let it propaget to all the subscribers, i want the same behaviour but
this time i am only adding a default constraint.
Thanks in Advance.Check out sp_addscriptexec in the SQL BOL.
HTH
Jerry
<bimalfernando@.gmail.com> wrote in message
news:1127788017.704066.291960@.o13g2000cwo.googlegroups.com...
> Hi,
> I have SQL server 2000 merge replication environment.
> How can i propagate Add default constraint command on an existing
> column withuot runnning the command on every subscriber.
> I know i can use sp_repladdcolumn to add a column in the publisher and
> let it propaget to all the subscribers, i want the same behaviour but
> this time i am only adding a default constraint.
> Thanks in Advance.
>
Thursday, March 22, 2012
ALTER TABLE to add a column between other columns
I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanks
This is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanks
This is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>
ALTER TABLE to add a column between other columns
I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanksThis is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanksThis is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>
ALTER TABLE to add a column between other columns
I need to use ALTER TABLE in order to add a column between the columns of a
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanksThis is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>
specific table. The table is in an production environment.
I think to use a unique Transact-SQL statement that allows to alter the
previous colums and add my column in the right position inside structure
table.
I have used this statement:
ALTER TABLE mytable
ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
ADD COLUMN mycolumn typecolumn(precision, scale)
but I have generated a syntax error.
How can I solve this issue?
Many thanksThis is not possible with ALTER TABLE.
It really shouldn't be necessary, anyway. The order that the columns are
returned when you SELECT * is not necessarily the order they are physically
stored on the data pages. If you want to return columns in a particular
order, you can SELECT with a column list, or create a view of the table with
the columns in the order you want them.
The graphical tools make you think you can add a column in a particular
position, but they do this by completely recreating a new table. That can
take a long time on a big table.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Pasquale" <Pasquale@.discussions.microsoft.com> wrote in message
news:246FA243-3EAA-40C5-8AC7-9462DD64B8AB@.microsoft.com...
>I need to use ALTER TABLE in order to add a column between the columns of a
> specific table. The table is in an production environment.
> I think to use a unique Transact-SQL statement that allows to alter the
> previous colums and add my column in the right position inside structure
> table.
> I have used this statement:
> ALTER TABLE mytable
> ALTER COLUMN mypreviouscolumn typecolumn(precision, scale)
> ADD COLUMN mycolumn typecolumn(precision, scale)
> but I have generated a syntax error.
> How can I solve this issue?
> Many thanks
>
Sunday, March 11, 2012
Alter SP
If I issue an "Alter SP", What happens to calls from apps using the
exisiting SP in a production environment?
For example, will my alter statement put the actual SP offline, current
appel in a web app will error with a specific error number one, recompilation
happpens automatically......
Thanks in advanceThe app will be blocked from using it until the proc is recompiled.
Depending on how long it takes to recompile the proc the application might
time out.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SalamElias" <eliassal@.online.nospam> wrote in message
news:8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com...
> If I issue an "Alter SP", What happens to calls from apps using the
> exisiting SP in a production environment?
> For example, will my alter statement put the actual SP offline, current
> appel in a web app will error with a specific error number one,
> recompilation
> happpens automatically......
> Thanks in advance|||Hi Salam,
I agree with Hilary's comment.
If you want to store procudure recompile as soon as possible, you could run
sp_recompile.
Running sp_recompile on a stored procedure or a trigger causes them to be
recompiled the next time they are executed. When sp_recompile is run on a
table or a view, all of the stored procedures that reference that table or
view will be recompiled the next time they are run. sp_recompile
accomplishes recompilations by incrementing the on-disk schema version of
the object in question.
Or use the procedure_option RECOMPILE :
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @.parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS
{ <sql_statement> [ ...n ] | <method_specifier> }
<procedure_option> ::= [ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]
<sql_statement> ::={ [ BEGIN ] statements [ END ] }
<method_specifier> ::=EXTERNAL NAME
assembly_name.class_name.method_name
Note : RECOMPILE
Indicates that the SQL Server 2005 Database Engine does not cache a plan
for this procedure and the procedure is recompiled at run time.
In SQL Server 2000, whenever a statement within a batch causes
recompilation, the whole batch, whether submitted through a stored
procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. In
SQL Server 2005, only the statement inside the batch that causes
recompilation is recompiled. Because of this difference, recompilation
counts in SQL Server 2000 and SQL Server 2005 are not comparable. Also,
there are more types of recompilations in SQL Server 2005 because of its
expanded feature set.
Statement-level recompilation benefits performance because, in most cases,
a small number of statements causes recompilations and their associated
penalties, in terms of CPU time and locks. These penalties are therefore
avoided for the other statements in the batch that do not have to be
recompiled.
The SQL Server Profiler SP:Recompile trace event reports statement-level
recompilations in SQL Server 2005. This trace event reports only batch
recompilations in SQL Server 2000. Further, in SQL Server 2005, the
TextData column of this event is populated. Therefore, the SQL Server 2000
practice of having to trace SP:StmtStarting or SP:StmtCompleted to obtain
the Transact-SQL text that caused recompilation is no longer required.
SQL Server 2005 also adds a new trace event called SQL:StmtRecompile that
reports statement-level recompilations. This trace event can be used to
track and debug recompilations. Whereas SP:Recompile generates only for
stored procedures and triggers, SQL:StmtRecompile generates for stored
procedures, triggers, ad-hoc batches, batches that are executed by using
sp_executesql, prepared queries, and dynamic SQL.
Reference:
SQL Server 2005 Books Online - Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/ms181055.aspx
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server
2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
SQL Server 2005 Books Online - ALTER PROCEDURE (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms189762.aspx
Sincerely,
Ray Yen
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Hilary Cotter" <hilary.cotter@.gmail.com>
| References: <8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com>
| Subject: Re: Alter SP
| Date: Mon, 2 Oct 2006 13:11:49 -0400
| Lines: 32
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
| Message-ID: <ucipbWk5GHA.512@.TK2MSFTNGP06.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: ool-44c103e1.dyn.optonline.net 68.193.3.225
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP06.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:446875
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| The app will be blocked from using it until the proc is recompiled.
| Depending on how long it takes to recompile the proc the application
might
| time out.
|
| --
| Hilary Cotter
| Director of Text Mining and Database Strategy
| RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
|
| This posting is my own and doesn't necessarily represent RelevantNoise's
| positions, strategies or opinions.
|
| Looking for a SQL Server replication book?
| http://www.nwsu.com/0974973602.html
|
| Looking for a FAQ on Indexing Services/SQL FTS
| http://www.indexserverfaq.com
|
|
|
| "SalamElias" <eliassal@.online.nospam> wrote in message
| news:8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com...
| > If I issue an "Alter SP", What happens to calls from apps using the
| > exisiting SP in a production environment?
| > For example, will my alter statement put the actual SP offline, current
| > appel in a web app will error with a specific error number one,
| > recompilation
| > happpens automatically......
| >
| > Thanks in advance
|
|
|
exisiting SP in a production environment?
For example, will my alter statement put the actual SP offline, current
appel in a web app will error with a specific error number one, recompilation
happpens automatically......
Thanks in advanceThe app will be blocked from using it until the proc is recompiled.
Depending on how long it takes to recompile the proc the application might
time out.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SalamElias" <eliassal@.online.nospam> wrote in message
news:8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com...
> If I issue an "Alter SP", What happens to calls from apps using the
> exisiting SP in a production environment?
> For example, will my alter statement put the actual SP offline, current
> appel in a web app will error with a specific error number one,
> recompilation
> happpens automatically......
> Thanks in advance|||Hi Salam,
I agree with Hilary's comment.
If you want to store procudure recompile as soon as possible, you could run
sp_recompile.
Running sp_recompile on a stored procedure or a trigger causes them to be
recompiled the next time they are executed. When sp_recompile is run on a
table or a view, all of the stored procedures that reference that table or
view will be recompiled the next time they are run. sp_recompile
accomplishes recompilations by incrementing the on-disk schema version of
the object in question.
Or use the procedure_option RECOMPILE :
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ]
[ { @.parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ [ OUT [ PUT ]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS
{ <sql_statement> [ ...n ] | <method_specifier> }
<procedure_option> ::= [ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE_AS_Clause ]
<sql_statement> ::={ [ BEGIN ] statements [ END ] }
<method_specifier> ::=EXTERNAL NAME
assembly_name.class_name.method_name
Note : RECOMPILE
Indicates that the SQL Server 2005 Database Engine does not cache a plan
for this procedure and the procedure is recompiled at run time.
In SQL Server 2000, whenever a statement within a batch causes
recompilation, the whole batch, whether submitted through a stored
procedure, trigger, ad-hoc batch, or prepared statement, is recompiled. In
SQL Server 2005, only the statement inside the batch that causes
recompilation is recompiled. Because of this difference, recompilation
counts in SQL Server 2000 and SQL Server 2005 are not comparable. Also,
there are more types of recompilations in SQL Server 2005 because of its
expanded feature set.
Statement-level recompilation benefits performance because, in most cases,
a small number of statements causes recompilations and their associated
penalties, in terms of CPU time and locks. These penalties are therefore
avoided for the other statements in the batch that do not have to be
recompiled.
The SQL Server Profiler SP:Recompile trace event reports statement-level
recompilations in SQL Server 2005. This trace event reports only batch
recompilations in SQL Server 2000. Further, in SQL Server 2005, the
TextData column of this event is populated. Therefore, the SQL Server 2000
practice of having to trace SP:StmtStarting or SP:StmtCompleted to obtain
the Transact-SQL text that caused recompilation is no longer required.
SQL Server 2005 also adds a new trace event called SQL:StmtRecompile that
reports statement-level recompilations. This trace event can be used to
track and debug recompilations. Whereas SP:Recompile generates only for
stored procedures and triggers, SQL:StmtRecompile generates for stored
procedures, triggers, ad-hoc batches, batches that are executed by using
sp_executesql, prepared queries, and dynamic SQL.
Reference:
SQL Server 2005 Books Online - Execution Plan Caching and Reuse
http://msdn2.microsoft.com/en-us/library/ms181055.aspx
Batch Compilation, Recompilation, and Plan Caching Issues in SQL Server
2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
SQL Server 2005 Books Online - ALTER PROCEDURE (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms189762.aspx
Sincerely,
Ray Yen
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Hilary Cotter" <hilary.cotter@.gmail.com>
| References: <8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com>
| Subject: Re: Alter SP
| Date: Mon, 2 Oct 2006 13:11:49 -0400
| Lines: 32
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
| X-RFC2646: Format=Flowed; Original
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
| Message-ID: <ucipbWk5GHA.512@.TK2MSFTNGP06.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: ool-44c103e1.dyn.optonline.net 68.193.3.225
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP06.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:446875
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| The app will be blocked from using it until the proc is recompiled.
| Depending on how long it takes to recompile the proc the application
might
| time out.
|
| --
| Hilary Cotter
| Director of Text Mining and Database Strategy
| RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
|
| This posting is my own and doesn't necessarily represent RelevantNoise's
| positions, strategies or opinions.
|
| Looking for a SQL Server replication book?
| http://www.nwsu.com/0974973602.html
|
| Looking for a FAQ on Indexing Services/SQL FTS
| http://www.indexserverfaq.com
|
|
|
| "SalamElias" <eliassal@.online.nospam> wrote in message
| news:8C14C710-D83B-400E-85CC-9D546ECEAF62@.microsoft.com...
| > If I issue an "Alter SP", What happens to calls from apps using the
| > exisiting SP in a production environment?
| > For example, will my alter statement put the actual SP offline, current
| > appel in a web app will error with a specific error number one,
| > recompilation
| > happpens automatically......
| >
| > Thanks in advance
|
|
|
Thursday, March 8, 2012
ALTER how long should it take?
The following ALTER takes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.PERSON ADD
FL_CNSL_NTFY char(1) NOT NULL CONSTRAINT DF_PERSON_FL_CNSL_NTFY
DEFAULT '',
CD_INTRP_NEED smallint NOT NULL CONSTRAINT DF_PERSON_CD_INTRP_NEED
DEFAULT 0
GO
COMMIT
When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that does take some time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.
>
When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that doestakesome time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.
>
I don't have much experience ofALTERTABLE myself, because I almost
alwaystakethelongway in my update scripts. That is, I rename the
existing table, create the table with the new definition, copy the
data, recreate indexes, triggers, and foreign keys, move referencing
foreign keys to the new table and finally drop the old definition.
When I copy data, I have a loop, so that I copy some 50000 rows at
a time.
>
This way of altering a table gives more flexibility to place columns
where you want, or make changes like replacing a bit column with
a char(1) column. But it also requires more care, since there are
so many steps. I have a tool that generates this for me. If you do it
by hand, you have to be very careful.
>
But there is certainly one thing youshouldcheck for: blocking. Maybe
some other process is blockingALTERTABLE from running at all. Check this
with sp_who.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools. Can you also tell me how long typically takes for you
to administer this type of change.
I appreciate your help. Thanks again.|||(cuneyt.barutcu@.illinois.gov) writes:
It's an inhouse tool that I developed myself.
There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the production databases
there is an unknown number of test databases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.
>
It's an inhouse tool that I developed myself.
>
As for commercial tools on the market, I don't have a very good overview
what is available. But Microsoft offers "DataDude", that is Visual Studio
Team Suite for Database Professionals. I believe the price tag is hefty.
>
Many people use Red Gate'sSQLCompareto generate their change scripts.
>
There is something called SQLFarms, which looks interesting, but I have
looked very very little on it.
>
>
There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the productiondatabases
there is an unknown number of testdatabases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.
>
--
Erland Sommarskog,SQLServerMVP, esq...@.sommarskog.se
>
Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Hi there,
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
ALTER TABLE dbo.PERSON ADD
FL_CNSL_NTFY char(1) NOT NULL CONSTRAINT DF_PERSON_FL_CNSL_NTFY
DEFAULT '',
CD_INTRP_NEED smallint NOT NULL CONSTRAINT DF_PERSON_CD_INTRP_NEED
DEFAULT 0
GO
COMMIT
Thanks for any tips on this issue...(cuneyt.barutcu@.illinois.gov) writes:
Quote:
Originally Posted by
The following ALTER takes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.
When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that does take some time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.
I don't have much experience of ALTER TABLE myself, because I almost
always take the long way in my update scripts. That is, I rename the
existing table, create the table with the new definition, copy the
data, recreate indexes, triggers, and foreign keys, move referencing
foreign keys to the new table and finally drop the old definition.
When I copy data, I have a loop, so that I copy some 50000 rows at
a time.
This way of altering a table gives more flexibility to place columns
where you want, or make changes like replacing a bit column with
a char(1) column. But it also requires more care, since there are
so many steps. I have a tool that generates this for me. If you do it
by hand, you have to be very careful.
But there is certainly one thing you should check for: blocking. Maybe
some other process is blocking ALTER TABLE from running at all. Check this
with sp_who.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jun 8, 4:17 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
(cuneyt.baru...@.illinois.gov) writes:
Quote:
Originally Posted by
The followingALTERtakes about 2 hours in my environment. total
number of records is about 2.8 million. IS this typical? Is there a
way to speed up this process.
>
When you add non-nullable columns, SQL Server needs to rebuild the entire
table to make room for the columns, and that doestakesome time. But
I two hours for 2.8 million rows is more than I execpt. Then again,
it depends not only on the number of the rows, but also how wide they
are.
>
I don't have much experience ofALTERTABLE myself, because I almost
alwaystakethelongway in my update scripts. That is, I rename the
existing table, create the table with the new definition, copy the
data, recreate indexes, triggers, and foreign keys, move referencing
foreign keys to the new table and finally drop the old definition.
When I copy data, I have a loop, so that I copy some 50000 rows at
a time.
>
This way of altering a table gives more flexibility to place columns
where you want, or make changes like replacing a bit column with
a char(1) column. But it also requires more care, since there are
so many steps. I have a tool that generates this for me. If you do it
by hand, you have to be very careful.
>
But there is certainly one thing youshouldcheck for: blocking. Maybe
some other process is blockingALTERTABLE from running at all. Check this
with sp_who.
>
--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools. Can you also tell me how long typically takes for you
to administer this type of change.
I appreciate your help. Thanks again.|||(cuneyt.barutcu@.illinois.gov) writes:
Quote:
Originally Posted by
Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools.
It's an inhouse tool that I developed myself.
As for commercial tools on the market, I don't have a very good overview
what is available. But Microsoft offers "DataDude", that is Visual Studio
Team Suite for Database Professionals. I believe the price tag is hefty.
Many people use Red Gate's SQL Compare to generate their change scripts.
There is something called SQLFarms, which looks interesting, but I have
looked very very little on it.
Quote:
Originally Posted by
Can you also tell me how long typically takes for you
to administer this type of change.
There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the production databases
there is an unknown number of test databases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||On Jun 11, 5:28 pm, Erland Sommarskog <esq...@.sommarskog.sewrote:
Quote:
Originally Posted by
(cuneyt.baru...@.illinois.gov) writes:
Quote:
Originally Posted by
Thanks a lot for your answer Erland,
I was wondering about the tool you were using to accomplish the tasks
you mentioned. Can you tell me what it is called. and the names of
similar tools.
>
It's an inhouse tool that I developed myself.
>
As for commercial tools on the market, I don't have a very good overview
what is available. But Microsoft offers "DataDude", that is Visual Studio
Team Suite for Database Professionals. I believe the price tag is hefty.
>
Many people use Red Gate'sSQLCompareto generate their change scripts.
>
There is something called SQLFarms, which looks interesting, but I have
looked very very little on it.
>
Quote:
Originally Posted by
Can you also tell me how long typically takes for you
to administer this type of change.
>
There are two steps: 1) Implement the change script. 2) Running it.
Implementing the change script takes quite some time. But I usually
implement a whole bunch of changes at a time. Our system is a product,
which runs at some 20 customer sites, and beside the productiondatabases
there is an unknown number of testdatabases. How long time it takes
running the change script depends on the size of the data base. We are
lucky in that our customers are not 24/7 shops, but if a script needs
to run for 24 hours, this is permissible. Again, keep in mind that a
script includes several table changes. Typically I would not accept two
hours to reload 2.8 million rows.
>
--
Erland Sommarskog,SQLServerMVP, esq...@.sommarskog.se
>
Books Online forSQLServer2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Hi there,
you may want to check out our xSQL Object (http://
www.xsqlsoftware.com) for generating those change scripts - we have a
free lite edition available also.
Thanks,
JC
xSQL Software
http://www.xsqlsoftware.com
Subscribe to:
Posts (Atom)