How to query for rows containing in a column?

Question

I have a SQL table in which some columns, when viewed in SQL Server Manager, contain ``. Does anyone know how to query for ``? I can individually modify the data in this column with `update table set column = NULL where key = 'value'`, but how can I find whether additional rows exist with this bad data?

Accepted Answer

I would recommend against replacing the data. There is nothing wrong with it, is just that SSMs cannot display it properly in the Edit panel. The data in the database itself is perfectly fine, from your description.

This script shows the problem:

```
create table test (id int not null identity(1,1) primary key, 
    large_value numeric(38,0));
go

insert into test (large_value) values (1);
insert into test (large_value) values (12345678901234567890123456789012345678);
insert into test (large_value) values (1234567890123456789012345678901234567);
insert into test (large_value) values (123456789012345678901234567890123456);
insert into test (large_value) values (12345678901234567890123456789012345);
insert into test (large_value) values (1234567890123456789012345678901234);
insert into test (large_value) values (123456789012345678901234567890123);
insert into test (large_value) values (12345678901234567890123456789012);
insert into test (large_value) values (1234567890123456789012345678901);
insert into test (large_value) values (123456789012345678901234567890);
insert into test (large_value) values (12345678901234567890123456789);
insert into test (large_value) values (NULL);
go

select * from test;
go
```

The SELECT will work fine, but showing the Edit Top 200 Rows in object explorer will not:

There is a Connect Item for this issue. SSMS 2012 still exhibits the same problem.

If we look at the Numeric and Decimal details we'll see that the problem occurs at a weird boundary, at precision 29 which is actually not a SQL Server boundary (precision 28 is):

```
Precision   Storage bytes
1 - 9   5
10-19   9
20-28   13
29-38   17
```

If we check the .Net (SSMS is a managed application) decimal precision table we can see quickly where the crux of the issue is: Precision is 28-29 significant digits. So the .Net `decimal` type cannot map high precision (>29) SQL Server `numeric`/`decimal` types.

This will affect not only SSMS display, but your applications as well. Specialized applications like SSIS will use high precisions representation like `DT_NUMERIC`:

`DT_NUMERIC` An exact numeric value with a fixed precision and scale. This data type is a 16-byte unsigned integer with a separate sign, a scale of 0 - 38, and a maximum precision of 38.

Now back to your problem: you can discover invalid entries by simply looking at the value. Knowing that the C# representation range can accommodate values between approximate (-7.9 x 1028 to 7.9 x 1028) / (100 to 28)` (the range depends on the scale) you can search for values outside the range on each column (the actual values to search between will depend on the column scale). But that begs the question 'what to replace the data with?'.

I would recommend instead using dedicated tools for import export, tools that are capable of handling high precision numeric values. SSIS is the obvious candidate. But even the modest bcp.exe would also fit the bill.

BTW if your values are actually incorrect (ie. true corruption) then I would recommend running `DBCC CHECKTABLE (...) WITH DATA_PURITY`:

DATA_PURITY

Causes DBCC CHECKDB to check the database for column values that are not valid or out-of-range. For example, DBCC CHECKDB 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.

For databases created in SQL Server 2005 and later, column-value integrity checks are enabled by default and do not require the DATA_PURITY option. For databases upgraded from earlier versions of SQL Server, column-value checks are not enabled by default until DBCC CHECKDB WITH DATA_PURITY has been run error free on the database. After this, DBCC CHECKDB checks column-value integrity by default.

Q: How can this issue arise for a `datetime` column?

```
use tempdb;
go

create table test(d datetime)

insert into test (d) values (getdate())

select %%physloc%%, * from test;

-- Row is on page  0x9100000001000000

dbcc traceon(3604,-1);

dbcc page(2,1,145,3);

Memory Dump @0x000000003FA1A060
0000000000000000:   10000c00 75f9ff00 6aa00000 010000             ....uùÿ.j .....
Slot 0 Column 1 Offset 0x4 Length 8 Length (physical) 8

dbcc writepage(2,1,145, 100, 8, 0xFFFFFFFFFFFFFFFF)
```

```
dbcc checktable('test') with data_purity;
```

Msg 2570, Level 16, State 3, Line 2 Page (1:145), slot 0 in object ID 837578022, index ID 0, partition ID 2882303763115671552, alloc unit ID 2882303763120062464 (type "In-row data"). Column "d" value is out of range for data type "datetime". Update column to a legal value.

How to query for rows containing <Unable to read data> in a column?

Solution

Problem

Related problems