Changes in DB column property after an Side by side upgradation

Category: sql server dbdesign

Question

Shivanq on Thu, 04 Jan 2018 10:42:08


After a Db migartion from SQL 2000 to 2014 I can see some difference when comparing column property using INFORMATION_SCHEMA.COLUMNS
What are the potentila impact of the below
Any help on this is reqally appricated


NUMERIC_PRECISION / NUMERIC_SCALE changed. All now NULL
NUMERIC_PRECISION_RADIX changed. Changed from 10 to 53
ORDINAL_POSITION has changed. Number is higher/lower than original
ORDINAL_POSITION/NUMERIC_PRECISION/NUMERIC_SCALE changed. Ordinal… Number higher/lower than original. Numeric… all now NULL
NUMERIC_PRECISION / NUMERIC_SCALE / DATETIME_PRECISION changed. All now NULL/0
New column has appeared in 2014! Perhaps due to DTS testing?
COLLATION_NAME changed from SQL_Latin1_General_CP1_CI_AS TO Latin1_General_CI_AS
DATA_TYPE has changed from text to varchar. Also CHARACTER_MAXIMUM_LENGTH / CHARACTER_OCTET_LENGTH
IS_NULLABLE changed from Yes to No
CHARACTER_MAXIMUM_LENGTH and CHARACTER_OCTET_LENGTH changed from 250 to 2000

Replies

Visakh16 on Thu, 04 Jan 2018 10:55:32


May I ask how you did the migration?

Shivanq on Thu, 04 Jan 2018 10:59:53


Using DB backups

First restored on a 2008 R2 SP3 and then on 2014 SP2

Visakh16 on Thu, 04 Jan 2018 11:02:30


Using DB backups

First restored on a 2008 R2 SP3 and then on 2014 SP2

Sorry that doesnt sound right

Simple backup restore will not cause any changes to the metadata like modifying data type, precision etc

It seems you're using a third party migration/backup tool or atleast you used some tools to do incompatibility check before the backup which caused these changes to occur. Otherwise metadata would not undergo a change.

Shivanq on Thu, 04 Jan 2018 14:41:34


I have used native backups and restore and we haven't used any no 3rd party app for data migration

Yes, It should not change the metadata But the fact is those are the changes happened after the DB restore on 2014.

So my worries are can I ignore those changes or what would be the application impact if I haven't changed those changes.

Visakh16 on Thu, 04 Jan 2018 16:10:28


I have used native backups and restore and we haven't used any no 3rd party app for data migration

Yes, It should not change the metadata But the fact is those are the changes happened after the DB restore on 2014.

So my worries are can I ignore those changes or what would be the application impact if I haven't changed those changes.

 those are the changes happened after the DB restore on 2014.

Nope my point is that such changes wont occur by a simple backup restore

Did you do some upgradation steps after the restore for the db? 

Any ways the changes that you reported can cause issue if columns are being used by other applications. Change in datatypes etc can break if application relies upon a fixed metadata for the columns (for example ETL tools like SSIS)

So you need to analyze the impact now if your metadata has changed from before and you've to do it at column level for each object affected

Tom Phillips on Thu, 04 Jan 2018 17:00:05


The INFORMATION_SCHEMA views do not exist in SQL 2000, they were introduced in SQL 2005.  What exactly is the command you ran to get the data you are comparing?

Shivanq on Fri, 05 Jan 2018 09:49:39


I have used select @@servername as server,* from INFORMATION_SCHEMA.COLUMNS

Shivanq on Fri, 05 Jan 2018 09:51:22


No haven't done any upgradations steps, it was a simple backup restore

Shivanq on Fri, 05 Jan 2018 13:19:42


My worries are as below

If I ignore those changes what would be the impact when application launch

Is there are data loss if I change those column types in the newly restored DB?

are these changes is due to the compatibility of new 2014 version


Shivanq on Fri, 05 Jan 2018 13:22:43


My worries are as below

If I ignore those changes what would be the impact when application launch

Is there are data loss if I change those column types in the newly restored DB?

are these changes is due to the compatibility of new 2014 version

Tom Phillips on Fri, 05 Jan 2018 13:23:18


There may be no issue.  We need to see the query you ran on both the SQL 2000 server to compare to the SQL 2014 server.  Your query may not be returning the same values because of the query.

Also, please post the results of SELECT @@VERSION from both servers.

Shivanq on Fri, 05 Jan 2018 13:36:04


Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Microsoft SQL Server 2014 (SP2-CU7) (KB4032541) - 12.0.5556.0 (X64)   Aug 17 2017 12:07:38   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)


Louis Davidson on Sat, 06 Jan 2018 01:10:03


Can you include some details as to what the differences are? Like others, this doesn't really sound right. Very interesting indeed. Like maybe give us the script of the tables before and after? And can you repeat the result?

Louis

Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.


TiborK on Sun, 07 Jan 2018 18:02:26


The info schema views were introduced in 7.0, when MS tried to get us stop using system tables. So in 7.0 and 2000, some of if gradually took that advice and increased the usage of info schema views over system tables. But then in 2005, MS more or less dropped info schema views since we now have the "right" way to get to meta-data: the catalog views.

Shivanq: I would check the catalog views and see if they seem OK or weird as well. IF they are weird, and not OK, then I would consider that upgrade botched. 

Tom Phillips on Mon, 08 Jan 2018 13:14:46


They were changed in SQL 2005 to be more "ISO compliant".   It is likely the values returned between versions do not match up.