Tag:SQL
Category:SQL
Article From:https://www.cnblogs.com/yanzi-meng/p/9122436.html

How the relational database quickly queries the number of records of a table

2017-05-09 15:58 by Xiaoxiang hermit,2423 Read,0 Comments, collections, editors

SQL SERVER data base

 

 

SELECT OBJECT_NAME(object_id)    AS Object_Name, 
AS Index_Name,
rows                    AS Table_Rows
LEFT JOIN sys.sysindexes i ON p.object_id = i.id AND  p.index_id = i.indid  
     object_id = OBJECT_ID('TableName') 

 

 

   1:Can a table without index also use the script above?

   2:Is only the non clustered index heap table able to use the script above?

   3:If there are multiple index tables, will there be any inconsistencies in the number of records?

   4:If rows is not accurate, will it be inaccurate?

   5: What is the situation of the partition table?

   6:Is the table record function obtained by object directory view sys.partitions and sp_spaceused accurate?

 

EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TAB_ROW')
    DROP TABLE TEST_TAB_ROW;
 
NOT EXISTS(SELECT 1 FROM sys.objects WHERE type='U' AND name='TEST_TAB_ROW')
        CREATE TABLE TEST_TAB_ROW
            ID        INT,
CHAR(200)
END
 
BEGIN TRAN
INT =1;
WHILE @Index <= 100000
 
INTO TEST_TAB_ROW
VALUES(@Index, NEWID());
    SET @Index+=1;
    IF (@Index % 5000)  = 0 
BEGIN
IF @@TRANCOUNT > 0 
BEGIN 
COMMIT;
BEGIN TRAN
END
END
 
BEGIN 
COMMIT;
GO

 

AS Object_Name, 
AS Index_Name,
rows                    AS Table_Rows
LEFT JOIN sys.sysindexes i ON p.object_id = i.id AND  p.index_id = i.indid  
     object_id = OBJECT_ID('dbo.TEST_TAB_ROW') 

 

INDEX IX_TEST_TAB_ROW ON TEST_TAB_ROW(ID);