Making Additions and Changes; Getting Error Preventing Them
-
MS SQL Server/SSMS are my tools.
As my project evolves I find I need to add data elements (Columns) to my existing tables. I'm confident I will need to add additional tables to the database at some point. The first time I added a column and tried to close/save the table, I got an error message:
I did some searching and found a KB that explains why the error is generated and suggesting a workaround. While I haven't yet figured out how to use the workaround (due to lack of familiarity with SSMS ui and how to invoke the direct editing functions, no doubt), this post is to ask about additions and changes.
At present, I do not have any data in my tables. I'm still working on normalizing and identifying PKs and FKs and making notes for how the tables and data will be related and used. Running afoul of this error at the design stage and finding such a seemingly kludgy workaround gives me pause for the future when I am certain to make additions and changes.
It seems to me that additions, changes and deletions are inherent to managing any database. How should I plan to deal with the process?
Are there any cautions you can give me about changes and additions both during design and subequently in use? Why can't the process be simplified by allowing the 'close/save' operation even if requiring confirmation after receiving cautions? Is the process truly overly and unnecessarily complicated for a reason or does it just seem so because I'm a noob? What do I need to read and absorb to overcome this obstacle?
- This topic was modified 1 month, 1 week ago by Ahr Aitch.
Be kind. Be calm. Be generous. Behave.
-
Generally - changes to objects in SQL will be performed using code. All the GUI does is generate the code for you and then execute that code.
What I would recommend is that anything you do in the GUI - you look to the top of that dialog, find the 'Script' button and script out the changes. Once scripted out, you can see how the GUI generated the code to make the changes - you can then save those changes in a file so you can reference them later and you can start to learn how to create those statements without using the GUI.
The issue you have run into is a default option in SSMS that is set to prevent you from making a mistake. Let's assume you decide to make a change to a table that has 500,000,000 rows in it. The change you are trying to make can only be done by creating a new table, copying the data from the old table to the new table - then dropping the old table and renaming the new table to the old table.
That is not only going to take some time - but it will very intrusive on a working production system and not something you would want to do normally.
Using the GUI to mock up a first pass on a table design is okay. But once you have that, you should script out the table as a drop/create (right-click on the object in object explorer). Now, save that script and any time you need to make changes while developing - modify the script and run it. That will drop the table and recreate it with your changes - but note that any data in that table would be lost.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Thank you, Jeffrey.
I can see the value in that sort of fail-safe for populated objects, but it just seemed that an empty data set should be open to modifications through the ui.
I DO see the value in learning to write scripts. I even found and tried to write a short script to add the column, but I ran into a perplexing issue. Searching revealed that using the ALTER command and the ADD qualifier should work, but when I entered the command using syntax I found on the web, I got an error message and the script wasn't executed. I did this using the Command Window in SSMS. Should I have used a different tool? At the point I wrote the code the table I wanted to affect was in Design mode and active in SSMS. The Command Window rejected every command I tried to use.
Here's the script I used: >ALTER table clanPEOPLE ADD column peopleSpouseID nchar(10);
and the error message returned: Command "ALTER" is not valid.
Obviously I still have a lot to learn. Tutorials and searching are helpful, but there is nothing as helpful as someone willing to answer questions on a forum. Thank you for that.
Be kind. Be calm. Be generous. Behave.
- Ahr Aitch wrote:
Thank you, Jeffrey.
Here's the script I used: >ALTER table clanPEOPLE ADD column peopleSpouseID nchar(10); and the error message returned: Command "ALTER" is not valid.
.
ALTER TABLE dbo.clanPEOPLE ADD peopleSpouseID nchar(10);
No need for the "column" keyword. You can use the gui to add columns, but if you change the order of the columns or add and identity column the gui duplicates the data and creates a new table which is not feasible on large tables. I also recommend learning to create scripts so that once tested they can be used for deployments.
-
Once again, thank you, Jeffrey. Your are kind and generous.
I realize that the dbo prefix exists for the table names in Object Explorer. I thought it was an SSMS convention and not actually part of the table filename. I don't know why SSMS or SQL Server added it or what it denotes. My supposition was that it stands for 'database object'. I thought I had learned that such prefixes were used to identify schema and inferred that it was a user's choice.
Can you enlighten me or point me to a good article?
I tried using the command as you suggested and received results in the ss. Am I wrong in trying to use the SSMS Command Window? If not that interface, what then?
Be kind. Be calm. Be generous. Behave.
-
You need to put SQL in a Query Window. This can be opened on the top left of SSMS.
dbo is the default schema. You can create your own schemas to group objects (tables, views, stored procedures etc). Schemas are mainly used to simplify security as permissions can be granted at schema level as well as object level. When writing queries it is usually best to specify the schema. For a simple database, I would just keep everything in dbo.
-
Thank you, Ken!
I had a feeling I had the wrong interface.
Be kind. Be calm. Be generous. Behave.
- Ken McKelvey wrote:
You need to put SQL in a Query Window. This can be opened on the top left of SSMS.
dbo is the default schema. You can create your own schemas to group objects (tables, views, stored procedures etc). Schemas are mainly used to simplify security as permissions can be granted at schema level as well as object level. When writing queries it is usually best to specify the schema. For a simple database, I would just keep everything in dbo.
Thanks again, Ken.
I was able to add the column to my table. I can see the column name in the Object Explorer view; but when I choose the Design option for the table, the new column does not appear in the design window. I tried refreshing the table with no change. I then tried refreshing the database with no change. Tried refreshing the table again; still no change. Searching turned up nothing that I understood. None of the tutorials I've done spent much time on DDL functions therefore no mention of this error in them.
I want to rearrange the order of columns in the table, specifically to move the new column to a different spot. I think that the order makes no difference, but I want datalements that have so affinity to be near each other in the list of columns. I enter descriptions for columns but having related items in proximity helps me remember how I use them.
What do I need to know?
Be kind. Be calm. Be generous. Behave.
- Ahr Aitch wrote:
when I choose the Design option for the table, the new column does not appear in the design window.
I cannot help you with this as I do not use the design window. I suspect most people just type CREATE TABLE, ALTER TABLE etc in .sql files. Typing is quicker and allows the file to be put in version control. Microsoft Learn will have the syntax for the DDL statements. Also right clicking on the table in object explorer will give the option to generate the script.
Ahr Aitch wrote:I want to rearrange the order of columns in the table, specifically to move the new column to a different spot. I think that the order makes no difference, but I want datalements that have so affinity to be near each other in the list of columns. I enter descriptions for columns but having related items in proximity helps me remember how I use them.
In theory the order makes no difference as a relation (table) is an unordered set. Occasionally the order of the columns can affect performance and, as you point out, be used as a form of pseudo documentation. If you want to change the order of the columns:
- Copy out the data.
- DROP the table
- CREATE the table with the columns in the order you want.
- Copy the data back.
If you have no data then:
- Select the table, right click, generate script as CREATE.
- Save the script.
- DROP the table.
- Re-arrange the order of the columns in the script and save.
- Run the script (F5)
- Select the database in the object explorer. Right click and refresh.
-
Thanks, Ken.
I haven't tried that yet but think I understand it.
Big thanks to you and Jeffrey . . .
I've been able to alter table structures (adding columns), alter data types (change datatype fixing wrong selection when created), add a few records to one of my tables, and select and display records to confirm my changes. Without help from you and a lot of searching for error messages, I would not have progressed this far. Now I at least have a few records to use for coding scripts to display the data on web pages I created and styled. This is far more progress than I expected to make. It could not have been done without your help.
I didn't even know how to spell SQL a few days ago. ':>)
Mega thanks!
Be kind. Be calm. Be generous. Behave.
-
This is NOT a problem with code. It IS a setting in SSMS to prevent casual users from making changes using the Object Explorer. Unfortunately, it's an instance wide setting instead of a setting as a part of an assignable role.
The reason why you're getting the error message is because you're trying to use the GUI (Design Window) to insert a column into a position in the table other than as the last column. SQL Server has to run a bunch of code to rebuild the table and copy the data to do that, just like you would if you were trying to do it in code.
To get to the setting in SSMS, select <tools><options> and then the <Designer> in the window that pops up. See the setting that I put a Red box around and make sure to uncheck the related box.
Then, send me some money for knowing such things off the top of my head instead of you Googling for the error message that came up which would have taken you to multiple articles that tell you the same thing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As for not being able to see the column you added in the design view, it sounds like you did all the proper refreshes and so I have to ask, did you actually add the column to the correct table in the correct database? When you do a SELECT * from the table, does the column actually appear and are you doing the SELECT in the correct database?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you, Jeff.
If I weren't so ignorant about SQL generally - and SSMS particularly - the searching I did might well have told me something useful. In fact the results probably did, but I was too much a noob to understand them. I didn't mean to say or even imply that there was a problem with the code. I'm quite aware that the problem exists in my lack of skills. I make frequent use of searching (but not google) especially for error codes. Search results for error codes often refer to their occurrence in circumstances different from the ones I experience them in. I don't yet know how to extrapolate and interpolate relevance.
I'll spend some quality time trying to learn what you taught me. I am absolutely positive that I added the columns to the the intended table. In fact I successfully added 2 of them (so far). Still, when I select the table in Object Explorer, right click and choose Design, information in what I call the active window doesn't include the new columns as the ss shows.
Searching turned up a reference to sp_refreshsqlmodule. I'm still trying to understand and use that to see if it fixes the problem.
To answer your question, since adding the columns I have been able to add a few records to the table and use SELECT to see records including one record with information in the peopleNickname data element. I'm satisfied that I added the column and that the column exists as the Object Explorer shows. I have no idea (yet) why the columns fail to appear in the Design window. From what I've read so far, it has to do with meta data maintained by SQL and the possibility that the meta data has not yet been updated. How that might work is another mystery for me, but that's what I gleaned from information in the link above to sp_refreshsqlmodule.
Can you tell me how to navigate to the Options window you shared? I spent about 10 minutes searching through SSMS menus without finding it. I found Tools > Options in the menus but did not get the screen you show.
Thanks again for the help.
Be kind. Be calm. Be generous. Behave.
-
Continuing this thread . . .
It's my understanding that the sp_refreshqlmodule command will update metadata, metadata that must be updated for SSMS to display all the column names in a table in the active window, as illustrated in my earlier post.
The kb give this syntax for the command:
sp_refreshsqlmodule
[ @name = ] N'name'
[ , [ @namespace = ] { OBJECT | DATABASE_DDL_TRIGGER | SERVER_DDL_TRIGGER } ]
[ ; ]
I don't understand the explanation give for the @name and N'name' and @namespace variables in that command. Searching wider didn't reveal anything to explain what they are. In my case, there is a database name (hutchinsNAME) and a table name (dbo.clanPEOPLE). Two columns (peopleSpouseID and peopleNickname) were added. Are any of those names relevant to @name or @namespace or N'name'?
To complicate matters further, I just discovered that the column peopleNickname has disappeared from the dbo.clanPEOPLE table since I posted the earlier ss. I have not run a Delete command. Any explanation for this? I was able to add it again. The Object Explorer shows that the column was re-added.
Be kind. Be calm. Be generous. Behave.
-
. . . and yet another question
What, if any, significance do the square brackets around elements in the example have in this ss? Are they literal or placeholder markers?
Be kind. Be calm. Be generous. Behave.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply