[dba-SQLServer] Update to null the efficient way

jwcolby jwcolby at colbyconsulting.com
Wed Oct 6 06:49:58 CDT 2010


Stuart,

We created a dynamic SQL statement to try and do it all in one shot and it timed out after an hour 
(no big surprise there) but I decided to just break it down into 100 field chunks and do it that 
way.  Each chunk took under an hour.

Interestingly, SQL Server threw an error each time, it appears to be related to the length of the 
string though not certain about that.  The SQL statement looks as follows and every time SQL threw 
an error about the very first case (IBS_IRRITABLE_BOWEL_SYNDROME_IN_HH in this case) however it 
appears that SQL Server did in fact process the SQL statement, if I go look for spaces in that set 
of fields I don't find any.

UPDATE _DataHSID_UpdSpaces.dbo.tblHSID SET

IBS_IRRITABLE_BOWEL_SYNDROME_IN_HH = case IBS_IRRITABLE_BOWEL_SYNDROME_IN_HH when ' ' then NULL else 
IBS_IRRITABLE_BOWEL_SYNDROME_IN_HH end,

IMPOTENCE_ED_IN_HH = case IMPOTENCE_ED_IN_HH when ' ' then NULL else IMPOTENCE_ED_IN_HH end,

MENOPAUSE_IN_HH = case MENOPAUSE_IN_HH when ' ' then NULL else MENOPAUSE_IN_HH end,

etc.

Because of the error being thrown (and apparently unhandled in my code) I am just now processing the 
last set of fields, however I should be finished in another hour.  MUCH better than 10 days.  ;)

We use SMO everywhere in our programming these days, to get database, table and field names from SQL 
Server.  We use it to back up and restore databases, attach and detach databases etc.

Quite a powerful library in C# / .NET.

John W. Colby
www.ColbyConsulting.com

On 10/5/2010 8:14 AM, Stuart McLachlan wrote:
> Don't know, but my gut feeling is that a single pass has got to be way more efficient that 540
> of them.
>
> Why not try it on a subset of the records and see how long it takes.
>
>



More information about the dba-SQLServer mailing list