NOLOCK and COUNT(*)

_{Tuesday, March 29, 2011 12:29 PM}

Hi ,

  SQL Server 2008 , standard edition::

    I am Inserting 1 million records into a table by selecting from other table. It is taking some minutes for the insertion. Menwhile, if i open other session and wants to see the progressed data , I can write the query like this ,which will return 100+ records:

            SELECT TOP 100 * FROM BigTable(NOLOCK);

In the same way , during the insertion time, if I want to see the number of records inserted, from the other session ,

I can write query like:

        SELECT COUNT(*) FROM BigTable(NOLOCK);

To my surprise, it is showing zero as the count , but the first query is returning the records. Means, Can I say COUNT(*) won't return records from UNCOMMITTED transactions?? Or is there any other concept related to this??

-- Reddy Balaji C. ##Mark as answer if it solves the issue

_{Tuesday, March 29, 2011 2:03 PM ✅Answered | 1 vote}

Hi

You can use the following code to obtain inserted rows estimates :

select OBJECT_NAME(object_id),row_count from sys.dm_db_partition_stats
where OBJECT_NAME(object_id)='BigTable'

_{Wednesday, March 30, 2011 7:39 AM ✅Answered}

Hi,

SELECT TOP 100 * FROM BigTable(NOLOCK);

The above T-SQL Statement is returning the records its due to the default TRANSACTION ISOLATION is Read UNCOMMITTED.

SELECT COUNT(*) FROM BigTable(NOLOCK);

The above T-SQL Statement is not returning the counts due to if we are trying to INSERT in a single transaction. If we implement batch wise INSERT then the above statement will shows the counts.

One more tips: Even if you want to see the COUNTs use SP_SpaceUsed 'TableName'

Thanks,

Guru

_{Tuesday, March 29, 2011 12:39 PM | 1 vote}

See Doron's article for the subject

http://dfarber.com/computer-consulting-blog.aspx?filterby=Insert records into ms sql table

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

_{Tuesday, March 29, 2011 12:54 PM}

Very good article and very helpful, Thanks Uri.If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer. DVR

_{Wednesday, March 30, 2011 5:57 AM}

Hi All,

        Thank you all for the replies ... Still I am not clear, whether COUNT will fetch the UNCOMMITTED data in the scenario that I have explained.

-- Reddy Balaji C. ##Mark as answer if it solves the issue

_{Wednesday, March 30, 2011 6:44 AM}

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspxBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

_{Wednesday, March 30, 2011 6:44 AM}

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/11/10/1280.aspxBest Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

_{Wednesday, March 30, 2011 7:46 AM}

One more method

SELECT OBJECT_NAME(OBJECT_ID)AS OBJECT, ROW_COUNT

FROM

SYS.DM_DB_PARTITION_STATS

WHERE

INDEX_ID < 2



/R.