Update number of characters in a field -- sql server new users

sql server new users : Update number of characters in a field

SQL
9/28/2005 8:41:06 AM

Easiest way is to use Enterprise Manager

http://sqlservercode.blogspot.com/

E Ortega
9/28/2005 11:36:33 AM

Hi All,

I have a very large DB in SQL 2000. I would like to change the properties of
a field (increase the number of characters).The field is currently nvarchar
50, I would like to make it 100. What should I do? do I need to run any util
to update the structure of the DB?

Thanks in advance.
EO

E Ortega
9/28/2005 12:09:38 PM

Thanks. After using the Enterprise Manger to change the field format do I
need to run anything else to update the structure of the old/current
records?
Thanks.

[quoted text, click to view]

E Ortega
9/28/2005 12:36:56 PM

Thank you.

[quoted text, click to view]

Mike Epprecht (SQL MVP)
9/28/2005 6:25:44 PM

Hi

Any Triggers, Stored Procedures and Views that use the Table need to be
updated.
Any code that accesses the table (client side code) need to be updated too.

Nothing more needs to be done to the specific column to update the rows as
it is done automatically.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mike@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

[quoted text, click to view]

Hugo Kornelis
9/28/2005 9:23:58 PM

[quoted text, click to view]

Hi SQL,

I disagree. Enterprise Manager will generate a script that creates a new
table, copies all data, drops and recreates all triggers and constraints
that refer to the table, then drops the old table and renames the new
one. This effectively locks the complete table AND may lock other tables
as well, for an extended time (especially if the table is large).

Only 5 seconds more work to type, but much, much, much faster to execute
is

ALTER TABLE <tablename>
ALTER COLUMN <columnname> <new datatype>

Best, Hugo
--

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server new users

home · blog · groups

Questions? Comments? Contact the dn