January 10, 2014 at 10:58 am
We're getting close to being able to use our new application. It uses SQL Server 2012 on the backend, and SQL Server 2008 R2 Express on the user's local machine. (We duplicate the data from the backend to local, and back again.)
One thing that's been requested is that some of the fields' lengths be changed. I believe all of them are current defined as VARCHAR of some length. None of these columns are a part of any primary key or referenced in a check or unique constraint. We'll probably go to VARCHAR(MAX) for those that we change. Now, doing this in SSMS, as I've always done before, always retains the data that's in the table. Because there's so many SQL Express databases involved I don't want to bring up the tables' definitions in each case and make the change. Instead I'd like to use ALTER TABLE ALTER COLUMN, if it will retain the values that are stored in the databases.
To make it clearer, let me give an example of one of the tables. I'll restrict this example to just the primary key and the column that's involved, so the table will look kinda weird. OK, here it is:
CREATE TABLE [dbo].[ASIPsychiatric](
[ClientNumber] [int] NOT NULL,
[CaseNumber] [tinyint] NOT NULL,
[Followup] [tinyint] NOT NULL,
[SeriousLossDesc] [varchar](100) NULL
CONSTRAINT [PK_ASIPsychiatric] PRIMARY KEY NONCLUSTERED
(
[ClientNumber] ASC,
[CaseNumber] ASC,
[Followup] ASC
)WITH ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
And here's how I image the DDL command would look:
ALTER TABLE [dbo].[ASIPsychiatric] ALTER COLUMN SeriousLossDesc VARCHAR(MAX) NULL
Will that retain our data already in the table, yet change the length of the column?
Kindest Regards, Rod Connect with me on LinkedIn.
January 10, 2014 at 11:54 am
Rod, yes. Increasing to varchar(max), or to anything higher than the current VARCHAR (100), shouldn't affect your data...unlike attempting to reduce to lower than your longest string in the column which would create a truncation error.
January 10, 2014 at 12:09 pm
I second vilyana's point. To my knowledge, we will not loose data when w we are increasing varchar size.
January 10, 2014 at 12:27 pm
Yes what happens behind the scenes is an implicit conversion of all the data to the new datatype. This is incredibly simple to demonstrate.
Go on your test box and try this out. Please note this should be on your TEST box!!!
CREATE TABLE [dbo].[ASIPsychiatric](
[ClientNumber] [int] NOT NULL,
[CaseNumber] [tinyint] NOT NULL,
[Followup] [tinyint] NOT NULL,
[SeriousLossDesc] [varchar](100) NULL
CONSTRAINT [PK_ASIPsychiatric] PRIMARY KEY NONCLUSTERED
(
[ClientNumber] ASC,
[CaseNumber] ASC,
[Followup] ASC
) ON [PRIMARY]
) ON [PRIMARY]
insert ASIPsychiatric
select 10, 1, 1, 'This is only a test'
select * from ASIPsychiatric
ALTER TABLE [dbo].[ASIPsychiatric] ALTER COLUMN SeriousLossDesc VARCHAR(MAX) NULL
select * from ASIPsychiatric
drop table ASIPsychiatric
_______________________________________________________________
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/
January 10, 2014 at 1:09 pm
Thank you vilyana, pmadhavapeddi22 and Sean for your answers. I tested it using the code that you gave, Sean, on my test database server. Works like a charm. So that's what I'll do, when I've got to up those columns lengths.
Kindest Regards, Rod Connect with me on LinkedIn.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply