September 15, 2015 at 10:55 am
Question: Table definition uses user defined types for EVERY field (yeah, I know, but it's legacy). These aren't really "user defined types" in the useful sense, but just placeholders, so to speak, as each type declaration just specifies an existing data type. Thus there are tables with a number of fields defined as text data type based on a single user defined type "DescriptionHuge", which does nothing more than:
CREATE TYPE [dbo].[DescriptionHuge] FROM [text] NULL
Just wondering what would happen if you did nothing more than change the user defined data type to varchar(max)? Or are types created this way just aliases?
If they are just aliases, what happens if you just drop the user defined data type? I'm guessing that these types are just aliases, but as I have to remediate some of these columns, I'm looking for any "quick tricks" that might exist. Colleagues have suggested adding a new column with the correct data type, then moving the data, and then renaming columns, and I can't see any obvious reason to do otherwise, but was wondering if there are any viable alternatives, or not so obvious alternate methods.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 15, 2015 at 11:07 am
sgmunson (9/15/2015)
Question: Table definition uses user defined types for EVERY field (yeah, I know, but it's legacy). These aren't really "user defined types" in the useful sense, but just placeholders, so to speak, as each type declaration just specifies an existing data type. Thus there are tables with a number of fields defined as text data type based on a single user defined type "DescriptionHuge", which does nothing more than:
CREATE TYPE [dbo].[DescriptionHuge] FROM [text] NULL
Just wondering what would happen if you did nothing more than change the user defined data type to varchar(max)? Or are types created this way just aliases?
If they are just aliases, what happens if you just drop the user defined data type? I'm guessing that these types are just aliases, but as I have to remediate some of these columns, I'm looking for any "quick tricks" that might exist. Colleagues have suggested adding a new column with the correct data type, then moving the data, and then renaming columns, and I can't see any obvious reason to do otherwise, but was wondering if there are any viable alternatives, or not so obvious alternate methods.
You could probably just change the column type from DescriptionHuge to varchar(max).
alter table MyTable
alter column MyDescription varchar(max) NULL
I have never had or heard of anybody having an issue changing a column from text to varchar(max).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 11:18 am
Sean Lange (9/15/2015)
sgmunson (9/15/2015)
Question: Table definition uses user defined types for EVERY field (yeah, I know, but it's legacy). These aren't really "user defined types" in the useful sense, but just placeholders, so to speak, as each type declaration just specifies an existing data type. Thus there are tables with a number of fields defined as text data type based on a single user defined type "DescriptionHuge", which does nothing more than:
CREATE TYPE [dbo].[DescriptionHuge] FROM [text] NULL
Just wondering what would happen if you did nothing more than change the user defined data type to varchar(max)? Or are types created this way just aliases?
If they are just aliases, what happens if you just drop the user defined data type? I'm guessing that these types are just aliases, but as I have to remediate some of these columns, I'm looking for any "quick tricks" that might exist. Colleagues have suggested adding a new column with the correct data type, then moving the data, and then renaming columns, and I can't see any obvious reason to do otherwise, but was wondering if there are any viable alternatives, or not so obvious alternate methods.
You could probably just change the column type from DescriptionHuge to varchar(max).
alter table MyTable
alter column MyDescription varchar(max) NULL
I have never had or heard of anybody having an issue changing a column from text to varchar(max).
As in, it just works? Just alter the column and no need for anything else? I'll also have to change any columns from ntext to nvarchar(max)....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 15, 2015 at 11:21 am
As a remember it, there is no valid syntax for ALTER TYPE. only drop and create.
i know that once a user type is in use, to change it, it cannot be modified. only dropped and recreated....
but you can't drop it if it's in use....you have to change EVERYTHING it touches.
you have to recreate, and then drop, every column,index,default, constraint, trigger,etc that might be involved with the type, and replace it with the new type.
I think after you drop teh old type you can finally rename the new type to be the old name, if desired.
i've ended up creating the replacement type, and adding new columns along side the existing columns that use that type.
it depends on how deep your rabbit hole goes, as to it's impact.
Lowell
September 15, 2015 at 12:19 pm
Lowell (9/15/2015)
As a remember it, there is no valid syntax for ALTER TYPE. only drop and create.i know that once a user type is in use, to change it, it cannot be modified. only dropped and recreated....
but you can't drop it if it's in use....you have to change EVERYTHING it touches.
you have to recreate, and then drop, every column,index,default, constraint, trigger,etc that might be involved with the type, and replace it with the new type.
I think after you drop teh old type you can finally rename the new type to be the old name, if desired.
i've ended up creating the replacement type, and adding new columns along side the existing columns that use that type.
it depends on how deep your rabbit hole goes, as to it's impact.
Yeah, I discovered that... and it's kind of a good thing. The rabbit hole here is already deep, and no point in trying to make it any deeper than it already is. As it turns out, there's more to my rabbit hole to worry about. Replication is in play, so I have more investigation to do for the tables involved.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 15, 2015 at 12:38 pm
sgmunson (9/15/2015)
Lowell (9/15/2015)
As a remember it, there is no valid syntax for ALTER TYPE. only drop and create.i know that once a user type is in use, to change it, it cannot be modified. only dropped and recreated....
but you can't drop it if it's in use....you have to change EVERYTHING it touches.
you have to recreate, and then drop, every column,index,default, constraint, trigger,etc that might be involved with the type, and replace it with the new type.
I think after you drop teh old type you can finally rename the new type to be the old name, if desired.
i've ended up creating the replacement type, and adding new columns along side the existing columns that use that type.
it depends on how deep your rabbit hole goes, as to it's impact.
Yeah, I discovered that... and it's kind of a good thing. The rabbit hole here is already deep, and no point in trying to make it any deeper than it already is. As it turns out, there's more to my rabbit hole to worry about. Replication is in play, so I have more investigation to do for the tables involved.
That could either strengthen the idea of not using the existing type or make the whole process a lot more complicated. Will be interesting to see what path you take and what you run in to.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 12:39 pm
sgmunson (9/15/2015)
Sean Lange (9/15/2015)
sgmunson (9/15/2015)
Question: Table definition uses user defined types for EVERY field (yeah, I know, but it's legacy). These aren't really "user defined types" in the useful sense, but just placeholders, so to speak, as each type declaration just specifies an existing data type. Thus there are tables with a number of fields defined as text data type based on a single user defined type "DescriptionHuge", which does nothing more than:
CREATE TYPE [dbo].[DescriptionHuge] FROM [text] NULL
Just wondering what would happen if you did nothing more than change the user defined data type to varchar(max)? Or are types created this way just aliases?
If they are just aliases, what happens if you just drop the user defined data type? I'm guessing that these types are just aliases, but as I have to remediate some of these columns, I'm looking for any "quick tricks" that might exist. Colleagues have suggested adding a new column with the correct data type, then moving the data, and then renaming columns, and I can't see any obvious reason to do otherwise, but was wondering if there are any viable alternatives, or not so obvious alternate methods.
You could probably just change the column type from DescriptionHuge to varchar(max).
alter table MyTable
alter column MyDescription varchar(max) NULL
I have never had or heard of anybody having an issue changing a column from text to varchar(max).
As in, it just works? Just alter the column and no need for anything else? I'll also have to change any columns from ntext to nvarchar(max)....
Yes. In this case I am suggesting changing the datatype of the columns and not bothering with the user defined type. Once you change the columns you can delete the existing user defined type.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 3:09 pm
Sean Lange (9/15/2015)
That could either strengthen the idea of not using the existing type or make the whole process a lot more complicated. Will be interesting to see what path you take and what you run in to.
Changing the user defined type is not going to work, as Lowell pointed out, and as I independently discovered. I was, at that point, just glad it wasn't going to work, as the rabbit hole would have gotten a lot deeper if it did. Anyway, it appears that the complications associated with replication are going to cause my team to need a new column that gets updated from the old, and that will keep the replication stuff to a minimum. If anyone has any expertise surrounding changing replication to accommodate a data type change for a number of columns from the user defined type that represents either text or ntext to varchar(max) or nvarchar(max), respectively, let me know. These user defined types are in use for a number of other fields that are not going to change (due to the fields not being in use), so they won't go away for awhile yet.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 15, 2015 at 7:44 pm
sgmunson (9/15/2015)
Sean Lange (9/15/2015)
That could either strengthen the idea of not using the existing type or make the whole process a lot more complicated. Will be interesting to see what path you take and what you run in to.Changing the user defined type is not going to work, as Lowell pointed out, and as I independently discovered. I was, at that point, just glad it wasn't going to work, as the rabbit hole would have gotten a lot deeper if it did. Anyway, it appears that the complications associated with replication are going to cause my team to need a new column that gets updated from the old, and that will keep the replication stuff to a minimum. If anyone has any expertise surrounding changing replication to accommodate a data type change for a number of columns from the user defined type that represents either text or ntext to varchar(max) or nvarchar(max), respectively, let me know. These user defined types are in use for a number of other fields that are not going to change (due to the fields not being in use), so they won't go away for awhile yet.
I did NOT suggest changing the user defined datatype. I suggested changing the datatype of the column from a user defined type to varchar(max).
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 15, 2015 at 9:54 pm
Sean Lange (9/15/2015)
sgmunson (9/15/2015)
Sean Lange (9/15/2015)
That could either strengthen the idea of not using the existing type or make the whole process a lot more complicated. Will be interesting to see what path you take and what you run in to.Changing the user defined type is not going to work, as Lowell pointed out, and as I independently discovered. I was, at that point, just glad it wasn't going to work, as the rabbit hole would have gotten a lot deeper if it did. Anyway, it appears that the complications associated with replication are going to cause my team to need a new column that gets updated from the old, and that will keep the replication stuff to a minimum. If anyone has any expertise surrounding changing replication to accommodate a data type change for a number of columns from the user defined type that represents either text or ntext to varchar(max) or nvarchar(max), respectively, let me know. These user defined types are in use for a number of other fields that are not going to change (due to the fields not being in use), so they won't go away for awhile yet.
I did NOT suggest changing the user defined datatype. I suggested changing the datatype of the column from a user defined type to varchar(max).
Oh dear... that's not what I was trying to say. Words just aren't good enough sometimes. My bad... I knew what you suggested, but I was just trying to communicate that because it wasn't going to work, that such was a good thing.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 16, 2015 at 7:14 am
sgmunson (9/15/2015)
Sean Lange (9/15/2015)
sgmunson (9/15/2015)
Sean Lange (9/15/2015)
That could either strengthen the idea of not using the existing type or make the whole process a lot more complicated. Will be interesting to see what path you take and what you run in to.Changing the user defined type is not going to work, as Lowell pointed out, and as I independently discovered. I was, at that point, just glad it wasn't going to work, as the rabbit hole would have gotten a lot deeper if it did. Anyway, it appears that the complications associated with replication are going to cause my team to need a new column that gets updated from the old, and that will keep the replication stuff to a minimum. If anyone has any expertise surrounding changing replication to accommodate a data type change for a number of columns from the user defined type that represents either text or ntext to varchar(max) or nvarchar(max), respectively, let me know. These user defined types are in use for a number of other fields that are not going to change (due to the fields not being in use), so they won't go away for awhile yet.
I did NOT suggest changing the user defined datatype. I suggested changing the datatype of the column from a user defined type to varchar(max).
Oh dear... that's not what I was trying to say. Words just aren't good enough sometimes. My bad... I knew what you suggested, but I was just trying to communicate that because it wasn't going to work, that such was a good thing.
No problem at all. It is sometimes quite easy to misinterpret the meaning when you can't hear the person. :-D:-D:-D
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 16, 2015 at 7:28 am
Sean Lange (9/16/2015)
sgmunson (9/15/2015)
Sean Lange (9/15/2015)
sgmunson (9/15/2015)
Sean Lange (9/15/2015)
That could either strengthen the idea of not using the existing type or make the whole process a lot more complicated. Will be interesting to see what path you take and what you run in to.Changing the user defined type is not going to work, as Lowell pointed out, and as I independently discovered. I was, at that point, just glad it wasn't going to work, as the rabbit hole would have gotten a lot deeper if it did. Anyway, it appears that the complications associated with replication are going to cause my team to need a new column that gets updated from the old, and that will keep the replication stuff to a minimum. If anyone has any expertise surrounding changing replication to accommodate a data type change for a number of columns from the user defined type that represents either text or ntext to varchar(max) or nvarchar(max), respectively, let me know. These user defined types are in use for a number of other fields that are not going to change (due to the fields not being in use), so they won't go away for awhile yet.
I did NOT suggest changing the user defined datatype. I suggested changing the datatype of the column from a user defined type to varchar(max).
Oh dear... that's not what I was trying to say. Words just aren't good enough sometimes. My bad... I knew what you suggested, but I was just trying to communicate that because it wasn't going to work, that such was a good thing.
No problem at all. It is sometimes quite easy to misinterpret the meaning when you can't hear the person. :-D:-D:-D
Tell me about it !!! Happens all the time... and even more often when a poster's primary language is something other than English. It's amazing how carefully you have to choose your words in order to remain crystal clear as to meaning. So much of verbal communication is the tone of voice instead of just the words themselves, and as we all tend to think the way we speak....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 16, 2015 at 8:30 am
Don't envy you this task, sounds like changing a database collation but less fun!
I'd be fighting hard to change columns to built in datatypes rather than new/modified user defined types.
For replication, check Subscription Options on your Publication properties - if Replicate Schema changes is 'True' you should be able to issue the ALTER TABLE... ALTER COLUMN commands on the Publisher just fine. In theory.
This does need your subscribers to be SQL too though.
September 23, 2015 at 2:38 pm
Gazareth (9/16/2015)
Don't envy you this task, sounds like changing a database collation but less fun!I'd be fighting hard to change columns to built in datatypes rather than new/modified user defined types.
For replication, check Subscription Options on your Publication properties - if Replicate Schema changes is 'True' you should be able to issue the ALTER TABLE... ALTER COLUMN commands on the Publisher just fine. In theory.
This does need your subscribers to be SQL too though.
As details have become more clear, I am no longer needing to worry about replication, but I do need to be able to see how the change from text or ntext to varchar(max) or nvarchar(max) will affect both space usage and whether or not the data is "in-row" or not. Our method is to run the ALTER TABLE with ALTER COLUMN command, followed by running an UPDATE to set the value of the affected column to a CAST of itself to the proper data type, and have a before and after picture of whether we are moving this data in-row or not, and in what quantities. I've been looking into a number of options:
1.) Using a system catalog view: sys.dm_db_index_physical_stats
2.) Using DBCC IND and/or DBCC PAGE to get more detailed information
If anyone has any general guidance on a good method for tracking where this data ends up, I'd appreciate it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 23, 2015 at 2:44 pm
sgmunson (9/23/2015)
Gazareth (9/16/2015)
Don't envy you this task, sounds like changing a database collation but less fun!I'd be fighting hard to change columns to built in datatypes rather than new/modified user defined types.
For replication, check Subscription Options on your Publication properties - if Replicate Schema changes is 'True' you should be able to issue the ALTER TABLE... ALTER COLUMN commands on the Publisher just fine. In theory.
This does need your subscribers to be SQL too though.
As details have become more clear, I am no longer needing to worry about replication, but I do need to be able to see how the change from text or ntext to varchar(max) or nvarchar(max) will affect both space usage and whether or not the data is "in-row" or not. Our method is to run the ALTER TABLE with ALTER COLUMN command, followed by running an UPDATE to set the value of the affected column to a CAST of itself to the proper data type, and have a before and after picture of whether we are moving this data in-row or not, and in what quantities. I've been looking into a number of options:
1.) Using a system catalog view: sys.dm_db_index_physical_stats
2.) Using DBCC IND and/or DBCC PAGE to get more detailed information
If anyone has any general guidance on a good method for tracking where this data ends up, I'd appreciate it.
It is not going to consume less space and the data will still be off row when relevant. This doesn't change. What does change is the ability to search for this data with normal where predicates. You no longer have to do things like "where cast(MyTextColumn as varchar(max)) = 'MyValueToSearchFor'".
If you are changing the datatype of the column there is no point in then running an update on the column to "change" the datatype. That happens as an implicit conversion when you change the datatype of the column.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply