Geek Answers Handbook

SQL server immediately returns the result

Yesterday I had this strange (never seen) situation when applying the patch to several databases using the SQL server management studio. I needed to update a large table (5 million records +) across several databases. The statement I released for each database was something like this:

set rowcount 100000
select 1
while @@rowcount > 1
  update table
  set column = newval
  where column is null

These statements were completed within a few seconds (2 to 30 seconds). On the second tab, I ran this query:

  select count(*) 
  from table 
  where column is null

Just to check how many lines have already been processed. I'm impatient, like me, I hit F5 for count (*) statements every 20 seconds or so. And the expected behavior - was that I had to wait for something in the range from 0 to 30 seconds before the counter (*) was counted. I expected this behavior since the update statement worked, so the counter (*) was next on the line.

But then after a database or 10 this strange thing happened. I opened the following database (changed the database via USE), and after pressing F5, the counter (*) immediately responded. And after pressing F5 again, a direct result, but without any progress, and again and again the score (*) has not changed. However, the update was in progress.

n- , (*) 100K . F5, .. ... ? (*) ... ....

. , .

- , , , , . ... .

+4
1

, , , count() . , , script count()

SELECT
COUNT(1)
FROM Table WITH(NOLOCK) 
WHERE Column IS NULL

count (*) . Use Count (1)

, . WITH (NOLOCK), .

0

More articles:


All Articles