7

I am doing this

SELECT * INTO table1 FROM Table0

I get an arithmetic overflow on a datetime column (error converting to smalldatetime), however the destinations schema that got created has a datetime column and not a smalldatetime column..

0

2 Answers 2

7

It sounds to me like you have an implicit conversion to smalldatetime, probably in a computed column. If you look at the Estimated Execution Plan, you'll probably see Compute Scalar operations in there. Hit F4 to see the properties, and you should be able to see the calculations that are being done, including any implicit conversions that are being used.

1
  • Yes there was a computed column.. Thanks all! Jan 2, 2013 at 6:02
1

This kind of problem is always indicative of corruption and can easily be debugged with

DBCC CHECKTABLE(<tablename>) WITH DATA_PURITY;

DATA_PURITY
Causes DBCC CHECKTABLE to check the table for column values that are not valid or out-of-range. For example, DBCC CHECKTABLE detects columns with date and time values that are larger than or less than the acceptable range for the datetime data type; or decimal or approximate-numeric data type columns with scale or precision values that are not valid.

5
  • Actually as was mentioned earlier, its a computed column that was causing the problem.. Jan 2, 2013 at 8:59
  • The fact that the computed column causes an overflow is technically a corruption, and can be detected by the check. Jan 2, 2013 at 9:07
  • 1
    @RemusRusanu - Is this only for persisted computed columns? It doesn't report anything for CREATE TABLE T(Id int, X AS CAST('Fish' AS INT));INSERT INTO T DEFAULT VALUES;SELECT * INTO #T FROM T;DBCC CHECKTABLE(T) WITH DATA_PURITY; Jan 2, 2013 at 9:18
  • @MartinSmith: you're right, data purity will only validate the storage format (ie persisted columns bits on disk). I was thrown aback by the very fact that you could create the computed column, obviously the DDL never evaluated the expression, but on second thought that is normal. Computed column usually depend on row data and during CREATE there are no rows, so it cannot be evaluated. During ALTER ... ADD... it could be evaluated, but there is no 'distinguished' row to try it on, so it would mean a size-of-data scan validation on all rows. Jan 11, 2013 at 9:27
  • 1
    Think of it as a feature to catch unhealthy use of SELECT * :) alter table t add [Thou Shall Not Use *] AS 1/0;. Jan 11, 2013 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.