Showing posts with label Row_Number. Show all posts
Showing posts with label Row_Number. Show all posts

Thursday, November 24, 2016

Is your Pre-Production database environment aligned with Production?

As database developer or BI developer you may get below questions many times:

1) Is our Pre-Production database has exact match with production database?
2) How do we ensure Test environment has good data quality?
3) Is our Test environment has big deviation than production system? and so on.

To answer those questions you  have different ways of investigating the database tables. However, one most obvious and first step could be counting rows of two different databse environemnts. You may think it's going to take a lot hell of time to count all the tables's row count in two different environments and list them manually.

well, you don't need to do this task manually. There is a way where you can get row counts of all the tables in a database with small piece of code. 

Please run the below code to find number of rows for each table in the chosen database.

USE AdventureWorks2012
GO 
SELECT  @@SERVERNAME AS Server_Name,
DB_NAME() AS Current_Database,
SCHEMA_NAME(A.schema_id) + '.' + A.Name as Table_Name, 
SUM(B.rows) AS Row_Count
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id

GROUP BY    A.schema_id, A.Name



I have used AdventureWorks2012 databse and result set looks like below:

Fig 1: result set of the query

At the same way you can find out  number of rows of the tables across  different database environments. Now you can place the result side by side in excel and easily compare the row numbers and number of tables between two different database environments. If you wish to make this automated then you can put this SQL code in a SSIS package and load the data after comparison.



Saturday, December 27, 2014

Analytical function: ROW_NUMBER () with Qualify in Teradata

This post will help to understand QUALIFY  ROW_NUMBER OVER (PARTITION BY ...) phrase with simple example.

We take an example like below:

INDUST_CODE
INDUSTRY_NAME
ACTIVATE_DATE
1011
GOOGLE
13-12-2009
1013
YOUTUBE
01-09-2008
1011
GOOGLE
15-12-2009
1012
MICROSOFT
14-10-1999
1011
GOOGLE
11-12-2009

The data listed above have more than one row with Industry named ‘Google’.  Business needs the industry name without duplication, they don’t want one industry name more than once. They like to see the data like below:

INDUST_CODE
 INDUSTRY_NAME

1011
GOOGLE

1012
MICROSOFT

1013
YOUTUBE


How do you do that?
You are expert in SQL, so wrote the script like below which works fine:
select INDUST_CODE,INDUSTRY_NAME
from INDUSTRY
QUALIFY
row_number() Over (partition by INDUST_CODE order by ACTIVATE_DATE desc)=1

If you want to know how the code worked? Then please find the explanation step by step:
1) Group by (Partition by)with Industry Code (INDUST_CODE)
2) Order by with ACTIVATE_DATE which means latest date will be used to find the first row
3) Then uses ‘QUALIFY’ to select the row.

Lets explain the syntax :





















Make the translation easy:
qualify row_number() over (Partition and order by brace) = 1
    which means,
    Selecting first row from the result set after applying group by and order by function.