0

I have many user-defined-scalar-functions which return varchar(max) as a result. As a consequence the whole column is varchar(max) even if it consists of only few character strings. I need to convert those varchar(max) into varchar(N) to use them as index. I have to do this because indexes are not allowed on varchar(max).

Is there an easy and fast way to convert all varchar(max) columns in a table to varchar(N) where N is the actual used max length of the variable? Can you please advice how to loop through all the varchar(max) columns in a table with the code below. The most desirably would be a procedure with the name of a table as a parameter.

declare @SQL varchar(max);
declare @N varchar(max); 
set @N = (SELECT max(len([MyColumn])) FROM [dbo].[MyTable])
set @SQL='ALTER TABLE [dbo].[MyTable] ALTER COLUMN [MyColumn] VARCHAR (' + @N +')'

--select @SQL
exec (@SQL)

Update. Alternatively, is there a way to force scalar UDF to return varchar(N) where N would be dynamically adjusted according to the length of the returned string?

Przemyslaw Remin
  • 6,276
  • 25
  • 113
  • 191
  • 3
    Why do you want to do this? It makes very little difference to the size of the data or performance. – Gordon Linoff Dec 22 '15 at 15:25
  • 1
    You say UDF but you also say something about variables in a table. There is likely a language barrier there but I am a bit confused. Are you trying to modify all your tables that have a datatype of varchar(max)? Do you really think that is a good idea? Do all the applications and procedures not allow for a longer value than the current longest value? This just seems to be fraught with serious challenges. – Sean Lange Dec 22 '15 at 15:33
  • What if you suceeeded, and then tried to add a record with a longer value? – Dan Bracuk Dec 22 '15 at 15:34
  • @DanBracuk then I will be informed that something is wrong with the results because the length of my variables is fixed. That is why I want it. Imagine that you know that you should get a variable with length of 6, and you get prompt about truncation. – Przemyslaw Remin Dec 22 '15 at 15:38
  • @GordonLinoff Another good reason for doing it is that you cannot have indexes on variables varchar(max) http://stackoverflow.com/a/2864109/1903793 – Przemyslaw Remin Dec 23 '15 at 12:34
  • @PrzemyslawRemin . . . That is a good point, but it argues for setting all the columns to a maximum length of 900 or so, not to a custom length. – Gordon Linoff Dec 24 '15 at 04:15
  • I have a task to do the exact same thing. Previous developers set all varchar columns to varchar(max) in a particular db. My task is to create a script, or scripts, to alter the varchar(max) columns to a reasonable length for given column, based upon maximum length of data per column (I may add 10%). Of course, corresponding maxlen's will need to be added to textboxes and such in the application, so that we can avoid truncation upon update / insert. I will post whatever I discover / create / google that I think is a workable solution. – Kershaw Feb 06 '18 at 23:58

0 Answers0