RSS 2.0
Sign In
# Friday, November 9, 2012

Two monthes ago we have started a process of changing column type from smallint to int in a big database.

This was splitted in two phases:

  1. Change tables and internal stored procedures and functions.
  2. Change interface API and update all clients.

The first part took almost two monthes to complete. Please read earlier post about the technique we have selected for the implementation. In total we have transferred about 15 billion rows. During this time database was online.

The second part was short but the problem was that we did not control all clients, so could not arbitrary change types of parameters and of result columns.

All our clients use Entity Framework 4 to access the database. All access is done though stored procedures. So suppose there was a procedure:

create procedure Data.GetReports(@type smallint) as
begin
  select Type, ... from Data.Report where Type = @type;
end;

where column "Type" was of type smallint. Now we were going to change it to:

create procedure Data.GetReports(@type int) as
begin
  select Type, ... from Data.Report where Type = @type;
end;

where "Type" column became of type int.

Our tests have shown that EF bears with change of types of input parameters, but throws exceptions when column type has been changed, even when a value fits the range.  The reason is that EF uses method SqlDataReader.GetInt16 to access the column value. This method has a remark: "No conversions are performed; therefore, the data retrieved must already be a 16-bit signed integer."

Fortunately, we have found that EF allows additional columns in the result set. This helped us to formulate the solution. We have updated the procedure definition like this:

create procedure Data.GetReports(@type int) as
begin
  select
    cast(Type as smallint) Type, -- deprecated
    Type TypeEx, ...
  from
    Data.Report
  where
    Type = @type;
end;

This way:

  • result column "Type" is declared as deprecated;
  • old clients still work;
  • all clients should be updated to use "TypeEx" column;
  • after all clients will be updated we shall remove "Type" column from the result set.

So there is a clear migration process.

P.S. we don't understand why SqlDataReader doesn't support value conversion.

Friday, November 9, 2012 4:41:27 PM UTC  #    Comments [0] -
.NET | SQL Server puzzle | Tips and tricks
All comments require the approval of the site owner before being displayed.
Name
E-mail
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview
Archive
<September 2019>
SunMonTueWedThuFriSat
25262728293031
1234567
891011121314
15161718192021
22232425262728
293012345
Statistics
Total Posts: 366
This Year: 2
This Month: 0
This Week: 0
Comments: 226
Locations of visitors to this page
Disclaimer
The opinions expressed herein are our own personal opinions and do not represent our employer's view in anyway.

© 2019, Nesterovsky bros
All Content © 2019, Nesterovsky bros
DasBlog theme 'Business' created by Christoph De Baene (delarou)