Is it possible to call a variable for the length of a field (varchar). Typically I always use nvarchar(max) for most things other than when I need a numeric field for math purposes. This is okay until the tables are big and performance takes a big hit.
Is it possible to create a column using varchar(max), then run a length query to create the highest number of characters (this example is just one record, so I’m not truly filtering on a max value. I understand that). Then store that number into a variable and call that variable into creating a new varchar column?
Other uses would be maybe be storing that output/result as a variable for other statements.
Is this possible?
Thanks.
CREATE TABLE Test (
MyCol nvarchar(max)
);
INSERT INTO Test (MyCol)
VALUES ('asdfasdfasdfasdfasdfasdfasdfasdfadfsasdfadfs');
SELECT LEN(MyCol) FROM Test
--44 characters. Store this as output as variable
ALTER TABLE TEST ADD NewMyCol varchar(VARIABLE_HERE?) --LEN from above
UPDATE TEST SET NewMyCol = MyCol FROM test
>Solution :
Comments are accurate, it seems quite dangerous to me to add a column based on the current length stored in the table, and I don’t really see the benefit.
However, since people will want to do it regardless of how many people pile on saying it’s a bad idea:
DECLARE @len int;
SELECT @len = MAX(LEN(MyCol)) FROM dbo.Test;
DECLARE @sql nvarchar(max) = N'
ALTER TABLE TEST ADD NewMyCol varchar($l$);';
SET @sql = REPLACE(@sql, N'$l$', RTRIM(@len));
EXEC sys.sp_executesql @sql;
Working example in this fiddle.