I wrote about dealing with data loss recently, ansking what your plans are for a situation. Some of you might rely on digital systems, some rely on humans, and some just accept some data loss. All of those are valid responses, depending on your environment. Today I wanted to take another step on this journey.

Imagine that one of your systems has data corruption. Any system you have, but pick one that might cause you some anxiety. Maybe you discover it from a failed query, maybe from an entry in the error log, maybe from a DBCC execution (I hope you use these). You have no idea how long the corruption has been there. Therefore, you don't know how many backups are valid.

What do you do? What are your plans? Certainly there could be data loss potential here. There will likely be some questions about why this wasn't known immediately, and maybe you'll experience some embarrassment in the moment. This will be a stressful moment in your career, and one with which you will want to be able to cope.

The question today is to get you to prepare a bit and game the possibilities. Some of you might never experience corruption, but you never know, so it does make some sense to think in advance and anticipate the reaction you want to have in the moment.

We always want to be prepared, but we won't have actual preparation plans (scripts, documents, etc) for all situations. It's much easier to think about the possibilities, rather that actually build plans for every possibility. Maybe you want to even discuss and debate them among your peers. Today's question is good practice for the real situation, and great mental preparation for a real event. Plus it can be a bit fun to brainstorm, have someone shoot holes in your ideas, you do the same for them, and then come up with another solution.

Join the debate, and respond to today's editorial on the forums

SET NOCOUNT only affects messages sent to the calling process. It has no effect on the value of @@ROWCOUNT: click here

While twice the number of rows are manipulated, @@ROWCOUNT only represents the results of the parent statement. The documentation for the OUTPUT clause contains this caveat:

This apparently also applies to outer UPDATE and DELETE statements as well, but this is not explicitly stated.

There are a number or restrictions on the table you can output to, (e.g. the table can not have triggers, check constraints, or participate in foreign key constraints as a parent or a child, nor can you output to a view), but a temporary table is valid. You can even return the results of the OUTPUT clause as a recordset to the calling process.

click here

» Discuss this question and answer on the forums