January 12, 2010 at 7:19 am
[font="Times New Roman"]Using Management Studio, I increased the length of a column in a table using right-click/Modify of a table name. I get a list of all of tables, with a question "The following tables will be saved to your database. Do you want to continue?"
Since I am new to SQL Server, could someone please explain in detail What exactly is SQL Server going to save by clicking [Yes]? [/font]
January 12, 2010 at 7:33 am
January 12, 2010 at 7:48 am
If you were to script what a Modify column in GUI is, the you would something like below.
Alter Table MyTable
Alter Column MyColumn NewSize()
When you are done with modifying the column from GUI, it has to be saved to the table. You are getting that warning message because your SSMS has settings that do no let changes to tables be saved. You can edit that if you are certain that you want the changes to be saved from tools-->options-->designers-->table and database designers and unchecking prevent saving changes that require table recreation
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 12, 2010 at 7:53 am
When making changes with the GUI (table changes, backup, restore, jobs ...), before you click the final "OK", you can script out the change that's about to be made. That allows you to see, save, modify ... the script for the change or future changes. You can then "cancel" out of the GUI and make the change through T-SQL.
January 12, 2010 at 7:56 am
Example:
You have table TEST and one of the columns that you want to change is LASTNAME NVARCHAR(50) and you want to change it to NVARCHAR(100), you will do like this
ALTER TABLE TEST
ALTER COLUMN LASTNAME NVARCHAR(100);
And if you have to change many tables you can use the The_SQL_DBA's options as he said in the post above
...unchecking prevent saving changes that require table recreation!
January 12, 2010 at 8:00 am
homebrew01 (1/12/2010)
When making changes with the GUI (table changes, backup, restore, jobs ...), before you click the final "OK", you can script out the change that's about to be made. That allows you to see, save, modify ... the script for the change or future changes. You can then "cancel" out of the GUI and make the change through T-SQL.
Not bad for the GUI users who wants to learn more T-SQL! 😉
January 12, 2010 at 8:14 am
[font="Times New Roman"]Many thanks for all your responses, unfortunately I don't think my question has been answered.
All the replies seem to steer me toward using T-SQL scripts. So, taking a let's-see-what-happens approach, I forged ahead and clicked on [Yes] as my answer to the "Do you want to continue?" prompt. Lo and behold, the column was modified successfully!
I still have outstanding questions though:
1) Why were all those tables listed?
2) What was SQL Server saving? [/font]
Looking forward to more feedback.
January 12, 2010 at 9:03 am
turnerpe (1/12/2010)
[font="Times New Roman"]Many thanks for all your responses, unfortunately I don't think my question has been answered.All the replies seem to steer me toward using T-SQL scripts. So, taking a let's-see-what-happens approach, I forged ahead and clicked on [Yes] as my answer to the "Do you want to continue?" prompt. Lo and behold, the column was modified successfully!
I still have outstanding questions though:
1) Why were all those tables listed?
2) What was SQL Server saving?
[/font]Looking forward to more feedback.
Did you generate a script to find out what SQL was doing ?
January 12, 2010 at 9:24 am
[font="Times New Roman"]Yes, I generated the script and information is limited in that it only contains header information (date, database) and the list of tables to be "saved". What is it saving to the other tables? [/font]
January 12, 2010 at 9:39 am
I just did modify to a column, and save change script & got this:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
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
GO
CREATE TABLE dbo.Tmp_junk
(
fld1 int NOT NULL,
fld2 nchar(10) NULL,
fld3 nchar(11) NULL,
fld4 int NOT NULL IDENTITY (1, 1)
) ON [PRIMARY]
GO
SET IDENTITY_INSERT dbo.Tmp_junk ON
GO
IF EXISTS(SELECT * FROM dbo.junk)
EXEC('INSERT INTO dbo.Tmp_junk (fld1, fld2, fld3, fld4)
SELECT fld1, fld2, fld3, fld4 FROM dbo.junk WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_junk OFF
GO
DROP TABLE dbo.junk
GO
EXECUTE sp_rename N'dbo.Tmp_junk', N'junk', 'OBJECT'
GO
ALTER TABLE dbo.junk ADD CONSTRAINT
pk_fld1 PRIMARY KEY NONCLUSTERED
(
fld1
) WITH( PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
I don't get the "list of all of tables" screen
January 12, 2010 at 9:57 am
[font="Times New Roman"]Maybe I'm missing something. I'll describe step-by-step how the list of tables is produced.
1) Right click column name and select Modify
2) Change Length from 10 to 15 in the Column Properties section
3) Close the table properties tab
4) Prompt "Save changes to the following Items? Table - dbo.editorial"
Options: [Yes] [No] [Cancel]
Choose [Yes]
5) Prompt "The following tables will be saved to your database. Do you want to continue?"
(list of several tables)
[x] Warn about Tables Affected
Options: [Yes] [No] [Save Text File]
The saved text file contains only the list of tables.
Is there some sort of dependency associated with these other tables? [/font]
January 12, 2010 at 9:58 am
the list of tables is usually child tables that reference the original table via a foreign key;
if the table is going to be dropped and rebuilt completely, rather than a column being added, you'd see the child tables getting foreign keys dropped and then re-added after the new table is built.
Lowell
January 13, 2010 at 12:20 pm
[font="Times New Roman"]RECAP:
1) Why were all those tables listed?
ANSWER: I finally got my answer...it's a list of tables with FKs related to a PK of the table that was changed. By using the sp_fkeys package I learned that the list of tables are any tables having any foreign keys associated with any primary key of the table that was changed. Fortunately, none of the FKs are for the column that was updated.
2) What was SQL Server saving?
I still don't know the answer to this one. I don't know if it only saved the change to the column length for the table or some additional hidden changes. [/font]
January 13, 2010 at 2:47 pm
the why is easy...by design, when you use the GUI to change a table, it drops the table and recreates it. Not always, but since it is so easy to use the GUI,you sometimes don't know what it is doing behind the scenes.
it does the following:
1. Creates a NEW table with the new design.
2. Migrates the data from the OLD table to the NEW, using identity_insert if there is an identity column.
3. drops the Constraints from the OLD table
4. Adds the Same Constraints to the NEW table
5. Drops the old table
Renames the NEW table to the original name.
because of the original table being replaced, all the tables related to the table need their Foreign key Constraints dropped and recreated.
Lowell
January 14, 2010 at 12:15 pm
[font="Times New Roman"]Holy Toledo! That's a lot of background steps!
One more question. Given that the column in question did not have constraints (no dependencies). Would it be safe to simply use the ALTER TABLE...ALTER COLUMN command rather than the GUI?
[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply