How Do You Change Data Type from uniqueidentifier
-
In the ss below you can see that I have a table (dbo.clan.spouses) with a column (spousesID) that has a datatype of uniqueidentifier.
I assigned that value not knowing the consequences. The column (spouseID) is supposed to be the primary key with the IDENTITY attribute so that each record will have this value incremented. In ignorance I confused IDENTITY with uniqueidentifier. (Lot's to learn.)
Now I cannot change the data type. I want it to be smallint. First I tried to change datatype in Design mode like I've done for other columns. There is something about uniqueidentifier that prevents me doing that. I searched and found little information about removing uniqueidentifier once it has been assigned. After failing with SSMS, I tried the query you see in the ss with the error. Searching on that error code turned up nothing that I understood or could make work. I also tried to delete the column, but that, too, was prevented (probably due to uniqueidentifier?).
Can someone please tell me how to fix this? I'm too much of a noob to try to change settings for SQL which was suggested in one result I found.
- This topic was modified 1 month, 1 week ago by Ahr Aitch. Reason: correct typos
Be kind. Be calm. Be generous. Behave.
-
dbo.clan.spouses is a 3 part name where dbo is the database, clan is the schema and spouses is the table.
Looking at your screen shot you probably want dbo.[clan.spouses], where dbo is the schema and [clan.spouses] is the table, although the naming convention is awful. You would be better having table names like clanSpouses or clan_spouses. If fact, unless you enjoy typing, what is wrong with spouses, people etc.
ps Why are you prefixing every column name with spouse in the [clan.spouses] table?
-
Your best bet is to recreate the tables(s) using the preferred data types.
The primary reason you can't alter the column is that there is not a way for SQL Server to convert the data. Even if the table is empty you will see an error like this.
Msg 206, Level 16, State 2, Line 18
Operand type clash: uniqueidentifier is incompatible with intYou also have a primary key constraint on that column so the first error is likely to look like this.
Msg 5074, Level 16, State 1, Line 18
The object 'PKspouses' is dependent on column 'spouseID'.
Msg 4922, Level 16, State 9, Line 18
ALTER TABLE ALTER COLUMN spouseID failed because one or more objects access this column.You could add a new column and define it as an identity, then drop the primary key constraint, drop the old column, rename the new column and then create a primary key constraint on the new column, but the id column will be at the end of the column list and it will bug you for ever, so it's worth the effort to recreate the tables.
--DROP TABLE IF EXISTS DBO.spouses
GO
CREATE TABLE DBO.spouses
(spouseID uniqueidentifier,
spouseCounter smallint,
spouseNumber int,
spouseLastName nvarchar(50),
spouseFirstName nvarchar(50)
CONSTRAINT PKspouses PRIMARY KEY CLUSTERED (spouseID)
)
INSERT DBO.spouses
SELECT NEWID(), 0,1,'xyz','abc'
ALTER TABLE DBO.spouses ADD spouseIDint INT IDENTITY(1,1)
ALTER TABLE DBO.spouses DROP CONSTRAINT PKspouses
ALTER TABLE DBO.spouses DROP COLUMN spouseID
EXEC sp_RENAME 'DBO.spouses.spouseIDint' , 'spouseID', 'COLUMN'
ALTER TABLE DBO.spouses ADD CONSTRAINT PKspouses PRIMARY KEY CLUSTERED (spouseID)
-- id column is at the end which shouldnt matter but...
SELECT *
FROM DBO.spouses - Ken McKelvey wrote:
dbo.clan.spouses is a 3 part name where dbo is the database, clan is the schema and spouses is the table.
Looking at your screen shot you probably want dbo.[clan.spouses], where dbo is the schema and [clan.spouses] is the table, although the naming convention is awful. You would be better having table names like clanSpouses or clan_spouses. If fact, unless you enjoy typing, what is wrong with spouses, people etc.
ps Why are you prefixing every column name with spouse in the [clan.spouses] table?
'awful' is an opinion. Thanks for sharing yours.
I chose this naming convention because I'm old (83) and benefit from mnemonics. While prefacing all the data element names with the table name is, admittedly, redundant, the practice helps me when I use the datanames in HTML and javascript and php. I don't mind the typing. I'm retired. I'm doing this as a hobby.
I have recently changed the naming convention eliminating capital letters by inserting periods in front of the portions that were capitalized. Perhaps in the future I'll make other changes.
Be kind. Be calm. Be generous. Behave.
-
After dropping PK as an attribute, I was able to delete the column. I did so and added a column with that name, set the type to smallint, and gave it the IDENTITY property. I will make it the PK for the table.
Be kind. Be calm. Be generous. Behave.
- Ahr Aitch wrote:
In the ss below you can see that I have a table (dbo.clan.spouses) with a column (spousesID) that has a datatype of uniqueidentifier.
I assigned that value not knowing the consequences. The column (spouseID) is supposed to be the primary key with the IDENTITY attribute so that each record will have this value incremented. In ignorance I confused IDENTITY with uniqueidentifier. (Lot's to learn.)
Now I cannot change the data type. I want it to be smallint. First I tried to change datatype in Design mode like I've done for other columns. There is something about uniqueidentifier that prevents me doing that. I searched and found little information about removing uniqueidentifier once it has been assigned. After failing with SSMS, I tried the query you see in the ss with the error. Searching on that error code turned up nothing that I understood or could make work. I also tried to delete the column, but that, too, was prevented (probably due to uniqueidentifier?).
Can someone please tell me how to fix this? I'm too much of a noob to try to change settings for SQL which was suggested in one result I found.
My recommendation is to NOT use periods in table names. Don't use dashes either because, like periods, they require the use of square brackets when you go to reference them in code.
Ahr Aitch wrote:Ken McKelvey wrote:dbo.clan.spouses is a 3 part name where dbo is the database, clan is the schema and spouses is the table.
Looking at your screen shot you probably want dbo.[clan.spouses], where dbo is the schema and [clan.spouses] is the table, although the naming convention is awful. You would be better having table names like clanSpouses or clan_spouses. If fact, unless you enjoy typing, what is wrong with spouses, people etc.
ps Why are you prefixing every column name with spouse in the [clan.spouses] table?
'awful' is an opinion. Thanks for sharing yours.
No... it's not just an opinion. Using anything that requires the use of square brackets is an "awful" idea because it violates the rules for the "4 part naming convention" that SQL follows. Using periods as a part of a table name is a bit like adding sugar to your gas tank to try to make your exhaust smell better.
Ahr Aitch wrote:I chose this naming convention because I'm old (83) and benefit from mnemonics.
TOTALLY UNDERSTOOD and much appreciated. Instead of violating some major and time honored best practices to do that, either use table names like dbo.clan_spouses or... even better... as you said, you're here to learn something new. So, instead of using the default "dbo" schema, learn how to make a "clan" schema so that the correct and proper 2 part naming would be clan.spouses instead of dbo.[clan.spouses].
If SQL Server was correctly installed, you should be able to type CREATE SCHEMA, then highlight those two words, then press the F1 key, and it should take you to the online documentation for the CREATE SCHEMA command with explanations and syntax examples in a very "Alice's Restaurant" fashion minus the circles and arrows.
If not, fire up your favorite web search engine and search for CREATE SCHEMA IN SQL SERVER and it should take you to the same place.
And, yeah... I totally agree with what you're doing. Like Red-Green used to say, "I'm pullin' for ya... We're all in this together".
--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) -
Thanks, Jeff.
I learned a good deal from your post. I did not know about the square brackets syntax requirement. I also learned more about using schema.
Thanks, again.
Be kind. Be calm. Be generous. Behave.
-
Realizing that I have no clue, I wondered if there are any best practices for choosing data types for such commonly used variables as firstNames, middleNames, lastNames, suffixes, cityNames, countyNames, countryNames, dates, etc.
My guess is that names should be varchar(#) because shorter values don't add padded spaces, but what (#) values. What do good practices recommend for length for first/middle/last names and suffixes? What about for city/county/state/country? I'm using drop down lists for choosing state names; so I think 2 will work but wondered if there are gotchas I don't know about. I've been thinking about creating a table of USPS country names. From a past life, I recall that there is a standard list that converts all of them to 3 characters. Forcing choice on users will go a long way to prevent errors in spelling, etc. I want to be practical here, but for the anticipated usage my db is likely to have it might not make any practical difference.
For dates, my guess is date; are there any cautions here? My understanding is the stored format is yyyy-mm-dd.
For IDENTITY keys, my guess is smallint. I doubt my database will ever realistically exceed 32k entries.
I will have text data for things like biographies and educational achievements. They will be free form. I can take a stab at lengths but would appreciate recommendations or pointers on how to set lengths.
Finally, I will have images stored in the db. I need guidance here. I know very little about image sizes except that they depend on a lot of things, like size of image in pixels, which (I think) is influenced by dimensions, color and other factors. From searching, I learned that image as a datatype has been deprecated and that varbinary(max) is now recommended. I have no clue what max should be. Are there best practice recommendations? My images are most likely to be portraits, shapshots, landscapes and cityscapes with dimensions typical for households.
Be kind. Be calm. Be generous. Behave.
-
You don't specify the value for max in varchar and varbinary columns; it has a specific value already. Also, the text datatype is deprecated - you should use varchar(max) or nvarchar(max) instead.
-
Good to know about deprecations. That information doesn't show up in all search results.
Thanks, again, Chris.
Be kind. Be calm. Be generous. Behave.
- Ahr Aitch wrote:
Thanks, Jeff.
I learned a good deal from your post. I did not know about the square brackets syntax requirement. I also learned more about using schema.
Thanks, again.
My pleasure and thank you for the feedback.
--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)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply