question

WesleyButler-0037 avatar image
0 Votes"
WesleyButler-0037 asked ErlandSommarskog commented

T-SQL in SSMS - SQL Server - Keep rowcount for previous days in a table for reporting purposes.

I am currently able to get the rowcount for each table in a schema. On this particular database history is not stored, so each day when I run this script, the counts are likely to differ. What I need to do is somehow keep a record of what the counts for each table were on previous dates. Is there a way I can do this using T-SQL - I can't seem to figure out how to approach this. Also CDC is disabled - so I am limited to having to script a solution using T-SQL. The table I get daily simply lists schema, tablename and rowcount - rowcount is realtime. I would like to keep previous counts associated with a date so result will produce a table with - schema, tablename RowCount_19May, RowCount_18May, RowCount_17May, ... , RowCount_1May.

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Are you storing that data somewhere? How are you receiving a 'table' every day - that doesn't really make sense. With that said - if you need to maintain history then you probably should look at a system versioned (temporal) table. Once you have that set up then perform an insert/update every day into this table from the 'table' you receive and you will get the history built for you.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

Create this table:

CREATE TABLE rowcounts (date date NOT NULL,
                       schemaname sysname NOT NULL,
                       tablename  sysname NOT NULL,
                       rowcnt     bigint NOT NULL,
                       CONSTRAINT pk_rowcounts PRIMARY KEY (date, schemaname, tablename))

Then schedule from an Agent job:

INSERT rowcounts (date, schemaname, tablename, rowcnt)
   SELECT convert(date, sysdatetime()), s.name, t.name, SUM(p.rows)
   FROM   sys.tables t
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   JOIN   sys.partitions p ON t.object_id = p.object_id
   WHERE  p.index_id IN (0, 1)
   GROUP  BY s.name, t.name

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Erland - that's a great suggestion, this DB (i should have mentioned) is an Azure instance so the Agent isn't available. Is there an equivalent in Azure.

0 Votes 0 ·

Azure has a feature called Elastic Jobs which is sort of "Agent for Azure". But I am not sure that it is worth the effort and the cost to set this up only for this thing.

Then again, you could schedule this to run from Task Scheduler on a local machine. You would have a .BAT file that runs SQLCMD to run the query.

0 Votes 0 ·
ElanChezhian-2384 avatar image
0 Votes"
ElanChezhian-2384 answered

I don't think there is a way to get the previous count of the table without having any reference such as created/updated date time or storing them somewhere. I would approach this in the following ways.

May be run the existing script along with a getdate() field and store this result into a separate table on a daily basis through an SQL Agent Job?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi anonymous user,

Welcome to Microsoft Q&A!

As mentioned by Erland, you could create one table rowcounts and insert the result into this table every day.

Suppose we have below table:

 CREATE TABLE rowcounts (date date NOT NULL,
             schemaname sysname NOT NULL,
             tablename  sysname NOT NULL,
             rowcnt     bigint NOT NULL,
             CONSTRAINT pk_rowcounts PRIMARY KEY (date, schemaname, tablename))
    
 insert into rowcounts values
 ('2021-05-20','dbo','A',10),
 ('2021-05-20','dbo','B',11),
 ('2021-05-19','dbo','A',11),
 ('2021-05-19','dbo','B',12),
 ('2021-05-18','dbo','A',10),
 ('2021-05-18','dbo','B',12)

Then we could use a PIVOT query to output the expected output:

 select * from 
 (select 'RowCount_'+REPLACE(LEFT(CONVERT(char(11), date, 113),6),' ','') DATE,schemaname,tablename,rowcnt from rowcounts) s
 pivot 
 (max(rowcnt) for date in ([RowCount_20May],[RowCount_19May],[RowCount_18May])) P

Output:

 schemaname    tablename    RowCount_18May    RowCount_19May    RowCount_20May
 dbo    A    10    11    10
 dbo    B    12    12    11

You could also use a dynamic query as below in case you would not like to list the dates manually.

  declare @sql nvarchar(max)
        
  select @sql=STUFF(( SELECT DISTINCT ',[RowCount_'+REPLACE(LEFT(CONVERT(char(11), date, 113),6),' ','')+']'  FROM rowcounts FOR XML PATH('') ), 1, 1, '')
    
  set @sql=N' select * from 
 (select ''RowCount_''+REPLACE(LEFT(CONVERT(char(11), date, 113),6),'' '','''') DATE,schemaname,tablename,rowcnt from rowcounts) s
 pivot 
 (max(rowcnt) for date in ('+@sql+')) p'
        
  EXECUTE sp_executesql  @sql

Best regards,
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.