Sql-server – Changing Column type to avoid Arithmetic overflow error

sql-server-2008

I have a table that saves user hits. It has these columns:

ID , UserID , HitDate , Userlocation , UserUniqueID

Today I'm getting this error "Arithmetic overflow error". After I checked it out I find my Last hit ID was "2147483647" and also my Column type is

Int type , Primary Key with AutoNumber enabled

So the only solution is to Alter the column to bigint type (I guess). I've tried this code :

Alter HitTable alter column id bigint

but it returns this error :

Msg 5074 level 6 stat 1 line 1

The Object 'PK_HitTable' is dependent on colun 'id'

Alter Table Alter Column id failed because one or more objects access this column

So how can I change this huge table without losing any data? Will droping the current constraint cause data loss? Is below code safe and will it give me the result (also with autonumber)?

ALTER TABLE HitTable
DROP CONSTRAINT PK_HitTable
GO
Alter HitTable alter column id bigint
GO
ALTER TABLE HitTable
ADD CONSTRAINT HitTable PRIMARY KEY CLUSTERED (ID);

Or should I use this?

ALTER TABLE HitTable NOCHECK CONSTRAINT ALL  
GO
Alter HitTable alter column id bigint
GO
ALTER TABLE HitTable NOCHECK CONSTRAINT ALL

Also I should mention this table is used heavily.

Best Answer

If you need to change the data type of a column, you'll have to change every data type referencing that column, too.

At the database level, you'll have to change any column referencing this column. Since you need to change a PK column, this will affect all other tables where this column is used as a foreign key reference. In all of those tables you'll need to change the data type of the related columns.

Furthermore, you'll have to perform a code review to catch each and every place where all the columns identified in the step above are referenced. If there are code sections, where a value from such a column is assigned to a variable, you'll need to change the data type of that variable. If the column is used in joins to other tables or in the WHERE section of a query, you might need to change the referenced columns as well.

As a side effect after the change you might see a performance drop caused by implicit conversions, if you didn't change all references.

Once the database changes are identified, you'll have to check the application if the columns and variables that had to be changed will have any side effect to the appl. For instance, if there are columns returned as bigint that used to be integer, the app should be able to handle it.

After all changes are identified, they should be implemented during a maintenance window to avoid data inconsistency due to dropped references.

Long story short: changing a data type needs to be well-prepared and tested. I strongly vote against an "on-the-fly" change!

Related Solutions

Sql-server – SQL Server 2008 Containstable generate negative rank with weighted_term

Qualifier: Since I can't recreate this, I'm unable to know for sure if this will fix the problem. However, these are some things that I'm seeing.

First off, the ampersand, pound sign, and semicolon are word-break characters. That means, that instead of searching for the string "pétoncle", what you're actually searching for is "p", "233", and "toncle". Clearly, that's not your intent.

I have to presume that you have the text "pétoncle" somewhere in your dataset. That means you need that entire string to be complete.

There are a few things you can do.

1) Turn off Stopwords all together. You can do that by altering the full text index to turn it off.

Note that you have to have your database set to SQL Server 2008 compatability for this to not generate a syntax error:

ALTER FULLTEXT INDEX ON Table1 SET STOPLIST OFF;

2) Create a new stoplist

If you create an empty StopList, you might be able to add the stopwords that you want or copy the system stoplist and remove the stopwords that you don't want. (I would advise the second approach).

Having said that, I wasn't able to find the & or # in the system stoplist, so they may be hard coded. You may have to simply turn the stoplist off.

3) Change your search to ignore the "pétoncle" case.

If you drop the "pétoncle" from the ISABOUT and change them to "p toncle", it might work:

'ISABOUT("pétoncle" weight (.8), "p toncle" weight (.8))'

Those are just some ideas. Like I said, without being able to access the system or recreate the scenario, we won't be able to help much.

Some more information for your researching pleasure:

SQL Server – SELECT POWER(10.0, 38.0) Arithmetic Overflow Error

From the POWER documentation:

Syntax

POWER ( float_expression , y )

Arguments

float_expression
Is an expression of type float or of a type that can be implicitly converted to float.

y
Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).

The first input is implicitly cast to float if necessary.

The internal calculation is performed using float arithmetic by the standard C Runtime Library (CRT) function pow.

The float output from pow is then cast back to the type of the left hand operand (implied to be numeric(3,1) when you use the literal value 10.0).

Using an explicit float works fine in your case:

SELECT POWER(1e1, 38);
SELECT POWER(CAST(10 as float), 38.0);

An exact result for 10³⁸ cannot be stored in a SQL Server decimal/numeric because it would require 39 digits of precision (1 followed by 38 zeros). The maximum precision is 38.

Best Answer

Related Solutions

Sql-server – SQL Server 2008 Containstable generate negative rank with weighted_term

SQL Server – SELECT POWER(10.0, 38.0) Arithmetic Overflow Error

Syntax

Arguments

Return Types

Related Question