{"id":183,"date":"2018-01-02T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/sql-data-warehouse-fast-row-counts"},"modified":"2026-03-18T21:53:07","modified_gmt":"2026-03-18T21:53:07","slug":"sql-data-warehouse-fast-row-counts","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2018\/01\/02\/sql-data-warehouse-fast-row-counts\/","title":{"rendered":"SQL Data Warehouse &#8211; Fast row counts &#8211; SQL Hammer"},"content":{"rendered":"<p>A couple of years ago I published a script to calculate <a href=\"https:\/\/sqlhammer.com\/fast-light-weight-row-counts\/\">fast, light-weight, row counts<\/a> for SQL Server on-premises. The need to view the row counts of tables has not diminished but new technologies have come to the fore-front.<\/p>\n<p><a href=\"https:\/\/azure.microsoft.com\/en-us\/services\/sql-data-warehouse\/?v=17.44\">Azure SQL Data Warehouse<\/a> is a massively parallel processing (MPP) architecture designed for large-scale data warehouses. An MPP system creates logical \/ physical slices of the data. In SQL Data Warehouse\u2019s case, the data has 60 logical slices, at all performance tiers. This means that a single table can have up to 60 different object_ids. This is why, in SQL Data Warehouse, there is the concept of physical and logical object_ids along with physical names.<\/p>\n<p>Below is a query for finding row counts of tables in SQL Data Warehouse which accounts for the differences in architecture between my earlier script, written for SQL Server, and SQL Data Warehouse.<\/p>\n<pre><code class=\"language-\">SELECT sm.name [schema] ,\ntb.name logical_table_name ,\nSUM(rg.total_rows) total_rows\nFROM\u00a0\u00a0 sys.schemas sm\nINNER JOIN sys.tables tb ON sm.schema_id = tb.schema_id\nINNER JOIN sys.pdw_table_mappings mp ON tb.object_id = mp.object_id\nINNER JOIN sys.pdw_nodes_tables nt ON nt.name = mp.physical_name\nINNER JOIN sys.dm_pdw_nodes_db_column_store_row_group_physical_stats rg\nON rg.object_id = nt.object_id\nAND rg.pdw_node_id = nt.pdw_node_id\nAND rg.distribution_id = nt.distribution_id\nWHERE 1 = 1\n\/<em>\nAND tb.name IN ( 'Posts',\n'Employee',\n'EmailAddress' )\n--<\/em>\/\n\/<em>\nAND sm.name IN ('dbo')\n--<\/em>\/\nGROUP BY sm.name, tb.name\nORDER BY SUM(rg.total_rows) DESC<\/code><\/pre>\n<p><a href=\"https:\/\/sqlhammer.com\/?attachment_id=3925\"><img decoding=\"async\" src=\"https:\/\/i0.wp.com\/www.sqlhammer.com\/wp-content\/plugins\/a3-lazy-load\/assets\/images\/lazy_placeholder.gif?resize=264%2C193&#038;ssl=1\" alt=\"\" \/><img decoding=\"async\" src=\"https:\/\/i1.wp.com\/www.sqlhammer.com\/wp-content\/uploads\/2017\/12\/sqldw-row-counts-1.jpg?resize=264%2C193&#038;ssl=1\" alt=\"\" \/><\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>A couple of years ago I published a script to calculate fast, light-weight, row counts for SQL Server on-premises. The need to view the row counts of tables has not diminished but new technologies have come to the fore-front. Azure SQL Data Warehouse is a massively parallel processing (MPP) architecture designed for large-scale data warehouses. [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":579,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-183","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/183","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=183"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/183\/revisions"}],"predecessor-version":[{"id":445,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/183\/revisions\/445"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/579"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=183"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=183"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=183"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}