How to Know How Many Rows Will Be Affected Before Running a Query in Microsoft SQL Server 2008

how to know how many rows will be affected before running a query in microsoft sql server 2008

Short answer is no..

You cannot get the number of rows before executing the query..atleast in SQL server.

The best way to do it is use

Select count(*) from <table> where <condtion>

then execute your actual query

 [delete]or [update] [set col='val']
 from <table> where <condtion>

How to get a count of rows qualified for clean-up in Change Tracking?

There are many good blogs on a bunch of alerts you should have set up by default. Here is one, and here is another. This will prevent you from searching the error log for certain messages pertaining to this severity. However, it will alert you for any application or code that raises this severity and 16 is very common so you may not want to add that level to an alert. I'd add 19-25 though as well as other specified error messages contained in those blogs (823, 824, 825, 829, 832, 855, 856).

To only be notified when that job fails, you can just add a notification on the job to alert you when it fails. First you will need to set up an operator so you can get emails, and then you'll just want to enable the notification on the job. If you do want to scan the error log for specific text though, I created a script that'll do just that. You can find it on GitHub here, and while it was designed to send daily emails with error log results, it can be ran adhoc.

tsql rowcount without executing update

One method would be to use transactions:

begin transaction;
    declare @rc int;
    update T set A = 'X' where B = 1;

    set @rc = @@rowcount;
    . . .
commit;  -- or rollback

Just about any other method would have race conditions, if other threads might be updating the table.

However, I am suspicious that this solves a real problem. I suspect that your real problem might have a better solution. Perhaps you should ask another question explaining what you are really trying to do.

How to get row count from EXEC() in a TSQL SPROC?

You could instead have the dynamic query return the result as a row set, which you would then insert into a table variable (could be a temporary or ordinary table as well) using the INSERT ... EXEC syntax. Afterwards you can just read the saved value into a variable using SELECT @var = ...:

DECLARE @rowcount TABLE (Value int);
INSERT INTO @rowcount
EXEC('SELECT COUNT(*) ' + @sqlBody);
SELECT @ActualNumberOfResults = Value FROM @rowcount;

Why there is an extra (1 row(s) affected)

That usually means you have the actual execution plan option turned on. The execution plan is sent as an extra rowset, resulting in an extra (1 row(s) affected) message.

To disable actual execution plan press Ctrl+M.

Need a row count after SELECT statement: what's the optimal SQL approach?

There are only two ways to be 100% certain that the COUNT(*) and the actual query will give consistent results:

• Combined the COUNT(*) with the query, as in your Approach 2. I recommend the form you show in your example, not the correlated subquery form shown in the comment from kogus.
• Use two queries, as in your Approach 1, after starting a transaction in SNAPSHOT or SERIALIZABLE isolation level.

Using one of those isolation levels is important because any other isolation level allows new rows created by other clients to become visible in your current transaction. Read the MSDN documentation on SET TRANSACTION ISOLATION for more details.

Getting info about running update query

What can you do now?

You could try running a separate query using the WITH(NOLOCK) table hint to see how many rows have been updated.
e.g. if the update statement is:

UPDATE MyTable
SET MyField = 'UPDATEDVALUE'
WHERE ID BETWEEN 1 AND 10000000

You could run this:

SELECT COUNT(*)
FROM MyTable WITH (NOLOCK)
WHERE ID BETWEEN 1 AND 10000000
    AND MyField = 'UPDATEDVALUE'

What can you do in future?

You could do the update in batches, and output progress as it goes. e.g. use a loop to update the records in chunks of say 1000 (arbitary value for point of explanation). After each update chunk completes, print out the progress (assuming you are running it from SSMS) e.g.

DECLARE @RowCount INTEGER
SET @RowCount = 1
DECLARE @Message VARCHAR(500)
DECLARE @TotalRowsUpdated INTEGER
SET @TotalRowsUpdated = 0

WHILE (@RowCount > 0)
    BEGIN
         UPDATE TOP (1000) MyTable
         SET MyField = 'UPDATEDVALUE'
         WHERE ID BETWEEN 1 AND 10000000
              AND MyField <> 'UPDATEDVALUE'

         SELECT @RowCount = @@ROWCOUNT
         SET @TotalRowsUpdated = @TotalRowsUpdated + @RowCount
         SELECT @Message = CAST(GETDATE() AS VARCHAR) + ' : ' + CAST(@TotalRowsUpdated AS VARCHAR) + ' records updated in total'
         RAISERROR (@Message, 0, 1) WITH NOWAIT
    END

Using RAISERROR like this ensures progress messages are printed out immediately. If you used PRINT instead, the messages are spooled up and not output immediately so you wouldn't get to see real-time progress.

SQL Server: the maximum number of rows in table

It's hard to give a generic answer to this. It really depends on number of factors:

• what size your row is
• what kind of data you store (strings, blobs, numbers)
• what do you do with your data (just keep it as archive, query it regularly)
• do you have indexes on your table - how many
• what's your server specs

etc.

As answered elsewhere here, 100,000 a day and thus per table is overkill - I'd suggest monthly or weekly perhaps even quarterly. The more tables you have the bigger maintenance/query nightmare it will become.

Fastest way to count exact number of rows in a very large table?

Simple answer:

• Database vendor independent solution = use the standard = COUNT(*)
• There are approximate SQL Server solutions but don't use COUNT(*) = out of scope

Notes:

COUNT(1) = COUNT(*) = COUNT(PrimaryKey) just in case

Edit:

SQL Server example (1.4 billion rows, 12 columns)

SELECT COUNT(*) FROM MyBigtable WITH (NOLOCK)
-- NOLOCK here is for me only to let me test for this answer: no more, no less

1 runs, 5:46 minutes, count = 1,401,659,700

--Note, sp_spaceused uses this DMV
SELECT
   Total_Rows= SUM(st.row_count)
FROM
   sys.dm_db_partition_stats st
WHERE
    object_name(object_id) = 'MyBigtable' AND (index_id < 2)

2 runs, both under 1 second, count = 1,401,659,670

The second one has less rows = wrong. Would be the same or more depending on writes (deletes are done out of hours here)

---

Related Topics