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
>

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
>

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
>

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 Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, Julia
The net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>
|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
David Gugick
Imceda Software
www.imceda.com

Alter Stored Procedure and Trigger

Hi All,
I need to modify some Stored Procedures and Triggers in production database
(SQL 2000). Can I use Edit object which means execute "Alter procedure..."
or "Alter Trigger..." directly? or I have to drop the existing SP or TR and
recreate them? Any difference between these 2 methods?
Thank you, JuliaThe net effect will be the same, the only difference will the the create
date will change for the drop/add whereas it will not change for the alter.
So dropping the sp/tr and recreating it will set the new create date for
tracking purposes, if you want to know when it was last changed. But about
the only difference.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Sorry, forgot, along with the drop/add, object ownership could change,
depending on who you are logged in as when you recreate the sp.
"Julia" <Julia@.discussions.microsoft.com> wrote in message
news:F2D56079-CC8F-4F4A-A7ED-5417AF745EB7@.microsoft.com...
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
database
> (SQL 2000). Can I use Edit object which means execute "Alter
procedure..."
> or "Alter Trigger..." directly? or I have to drop the existing SP or TR
and
> recreate them? Any difference between these 2 methods?
> Thank you, Julia
>|||Julia wrote:
> Hi All,
> I need to modify some Stored Procedures and Triggers in production
> database (SQL 2000). Can I use Edit object which means execute
> "Alter procedure..." or "Alter Trigger..." directly? or I have to
> drop the existing SP or TR and recreate them? Any difference between
> these 2 methods?
> Thank you, Julia
Object IDs change on a drop/create. Use alter whenever possible.
--
David Gugick
Imceda Software
www.imceda.com

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 advance
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
|||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/pro...05/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/subscripti...ult.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/subscripti.../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!TK2MSFT NGP06.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
|
|
|

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
|
|
|

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, recompilatio
n
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 [ ; nu
mber ]
[ { @.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/pr...005/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/subscript...ault.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/subscript...t/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
|
|
|

alter column

hi,
I have a production table with 2.6 million records, I need
to alter one column from numeric to varchar, does it will
affect the users? blocking the table? ThanksHi,
Obviously Yes....Dont do it when users are accessing the table.
Thanks
Hari
MCDBA
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:088d01c3ced5$34b021e0$a601280a@.phx.gbl...
> hi,
> I have a production table with 2.6 million records, I need
> to alter one column from numeric to varchar, does it will
> affect the users? blocking the table? Thanks|||Changing the column data type (ALTER TABLE ... ALTER COLUMN) will acquire a
schema modification lock for the duration of the operation. In this case,
all data pages be updated so you probably want to do this during a
maintenance window.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jen" <anonymous@.discussions.microsoft.com> wrote in message
news:088d01c3ced5$34b021e0$a601280a@.phx.gbl...
> hi,
> I have a production table with 2.6 million records, I need
> to alter one column from numeric to varchar, does it will
> affect the users? blocking the table? Thanks

All zero Reads in profiler trace

I'm looking at a trace of 100k rows over two hours for a
production server. The database isn't huge, it might all
be in cache for all I know, but I didn't expect all Read
values to come back as zero!
Profiler seems to suggest that these are *logical* reads,
so even if it were all in cache, would that explain the
numbers, or is there something else wedged?
(Just to be clear, the column is present, but all values
are zero).
Thanks for any wisdom or advice.
J.Which events are you trapping in the profiler trace output? Not all profiler
events trap all data columns.
For example, the TSQL\SQL:StmtCompleted event captures the reads data
column, but the TSQL\SQL:StmtStarting does not.
You need to check the event class / data column matrix in the documentation
to see which event classes capture which data columns.
The TSQL class is documented (with the other classes) here:
http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_7zll.asp
HTH
Regards,
Greg Linwood
SQL Server MVP
"jxstern" <jxstern@.wherever.iam> wrote in message
news:0b3001c38233$cc5c9b70$a301280a@.phx.gbl...
> I'm looking at a trace of 100k rows over two hours for a
> production server. The database isn't huge, it might all
> be in cache for all I know, but I didn't expect all Read
> values to come back as zero!
> Profiler seems to suggest that these are *logical* reads,
> so even if it were all in cache, would that explain the
> numbers, or is there something else wedged?
> (Just to be clear, the column is present, but all values
> are zero).
> Thanks for any wisdom or advice.
> J.
>|||On Wed, 24 Sep 2003 11:00:17 +1000, "Greg Linwood"
<g_linwood@.hotmail.com> wrote:
>Which events are you trapping in the profiler trace output? Not all profiler
>events trap all data columns.
>For example, the TSQL\SQL:StmtCompleted event captures the reads data
>column, but the TSQL\SQL:StmtStarting does not.
It's all the eventclass 12's that usually capture reads reliably, as I
recall that's BatchCompleted.
J.
>You need to check the event class / data column matrix in the documentation
>to see which event classes capture which data columns.
>The TSQL class is documented (with the other classes) here:
>http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_7zll.asp
>HTH
>Regards,
>Greg Linwood
>SQL Server MVP
>"jxstern" <jxstern@.wherever.iam> wrote in message
>news:0b3001c38233$cc5c9b70$a301280a@.phx.gbl...
>> I'm looking at a trace of 100k rows over two hours for a
>> production server. The database isn't huge, it might all
>> be in cache for all I know, but I didn't expect all Read
>> values to come back as zero!
>> Profiler seems to suggest that these are *logical* reads,
>> so even if it were all in cache, would that explain the
>> numbers, or is there something else wedged?
>> (Just to be clear, the column is present, but all values
>> are zero).
>> Thanks for any wisdom or advice.
>> J.
>|||When you say:
<snip> It's all the eventclass 12's that usually capture reads reliably, as
I recall that's BatchCompleted.</snip>
Are you saying that BatchCompleted is the event that is returning read
values reliably (non zero), or is it some other event that's the returning
zeros?
Regards,
Greg Linwood
SQL Server MVP
"JXStern" <JXSternChangeX2R@.gte.net> wrote in message
news:c272nvc8c461ibc0mrgvh9l3vmg08p69sa@.4ax.com...
> On Wed, 24 Sep 2003 11:00:17 +1000, "Greg Linwood"
> <g_linwood@.hotmail.com> wrote:
> >Which events are you trapping in the profiler trace output? Not all
profiler
> >events trap all data columns.
> >
> >For example, the TSQL\SQL:StmtCompleted event captures the reads data
> >column, but the TSQL\SQL:StmtStarting does not.
> It's all the eventclass 12's that usually capture reads reliably, as I
> recall that's BatchCompleted.
> J.
>
> >
> >You need to check the event class / data column matrix in the
documentation
> >to see which event classes capture which data columns.
> >
> >The TSQL class is documented (with the other classes) here:
> >http://msdn.microsoft.com/library/en-us/adminsql/ad_mon_perf_7zll.asp
> >
> >HTH
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"jxstern" <jxstern@.wherever.iam> wrote in message
> >news:0b3001c38233$cc5c9b70$a301280a@.phx.gbl...
> >> I'm looking at a trace of 100k rows over two hours for a
> >> production server. The database isn't huge, it might all
> >> be in cache for all I know, but I didn't expect all Read
> >> values to come back as zero!
> >>
> >> Profiler seems to suggest that these are *logical* reads,
> >> so even if it were all in cache, would that explain the
> >> numbers, or is there something else wedged?
> >>
> >> (Just to be clear, the column is present, but all values
> >> are zero).
> >>
> >> Thanks for any wisdom or advice.
> >>
> >> J.
> >>
> >
>|||On Wed, 24 Sep 2003 14:54:16 +1000, "Greg Linwood"
<g_linwood@.hotmail.com> wrote:
>When you say:
><snip> It's all the eventclass 12's that usually capture reads reliably, as
>I recall that's BatchCompleted.</snip>
>Are you saying that BatchCompleted is the event that is returning read
>values reliably (non zero), or is it some other event that's the returning
>zeros?
I generally look only at the 12's, and all BatchCompleted events are
coming up zero in this trace.
In fact, *all* standard events are coming up straight zeros.
J.