Basic differences between SQL and NoSQL.

Around three decades Oracle, IBM, Microsoft relational databases were consistent leaders for Operational Database Management Systems. However, currently all of these vendors offer NoSQL platform parallel to their traditional database platform e.g. Microsoft got Azure Cosmos DB as NoSQL database and so on.

Journey of NoSQL:

 

The world of NoSQL emerged in 1998 and it's gained popularity in 2010s. In 2015, the popular NoSQL database provider MongoDB appeared in Gartners magic Quardent as a leader for operational database management system.

Fig 1:Gartners Magic Quadrant in 2015

Though recent trends shows that NoSQL is getting more popular in Big data landscape than relational database management system.

What is NoSql and Why?

NoSQL stands for 'Not only SQL'. NoSQL database is for storing both structure and semi structure data.

NoSQL stores data in one of four categories:

1. Key-Value storage
2. Document storage
3. Wide Column storage
4. Graph database

The most popular NoSQL  database MongoDB is document storage, however, it can be used as a key/value store, which is just a subset of features.

Today data become more broader in terms of shape and size. Data is spread around documents, social media, complex web application, IoT sources. The traditional SQL database can't handle these data due to the continuous changing behavior. Traditional database need to know shape of the data (schema) beforehand to store those, hence it failed to capture continuous evolving data. And thus, NoSQL emerge and conquer the world!!!

Fixed schema vs. No Schema

For structure database (traditional SQL database) you need to have schema ready before you insert data to a table but for NoSQL you don't need to have schema created at first rather you can directly insert the data. Though, it's better to say 'Schema agnostice' than 'Schemaless' e.g. Microsoft's NoSQL database Azure Cosmos DB known as schema agnostic database, which is not bound by schemas but are aware of the schemas. 


Relationship vs. No Relationship

Traditional SQL require to maintain relationship to perform, that's why normalization is there. Whereas, NoSQL doesn't require to maintain the relationship. You can embed a few tables into one table in NoSQL database.

Below table (fig:2) shows basic differences between SQL and NoSQL database:

Fig 2: Comparison between SQL and NoSQL

Conclusion: NoSQL denotes 'Not only SQL', it means NoSQL database can perform what traditional relational database can do as well as do more. But these two types of Database have different expertise,  as per business requirements you can choose from them. A way to find out which fit best for you could be asking the question. Do you know the shape of data well advance? If answer is 'Yes' which means you can define schema earlier and can build the relationship before data arrive then it's good to choose traditional SQL. On the other hand, when you don't know the shape of data and behavior changes continuously then go with NoSQL.  Nevertheless, some complex enterprise solution can be built by using both SQL and NoSQL database to leverage best of each.

SQL Server: Easy way to add Columns to Multiple tables

When you work in Data Warehouse/BI Project or any project where database is required and you are in a situation where need to add one or more same columns to the tables you already built then this post will help you to achieve the goal quicker and error free.

For example a BI project you may working with require to add load timestamp to all the target tables which at the design phase somehow missed or you find out at the later part of the project that you require one or two particular columns to all the tables.

Boring task!!! right?? Going through all the tables and changing it one by one is time consumable and error prone.  For example if you have 50 tables and you need to add one column say column called 'UpdatedAt'. How do we do it by using SQL script?

You need to list all the table name and run the below query by replacing the table name e.g. 'testTable1', 'testTable2' etc.
 
             Declare @tbl_name as varchar(100)
              Declare @tsql_code Varchar(500)

              Declare cur_table cursor local for
                           select name
                           from sys.tables
                           Where name in ('testTable1','testTable2')
                           order by 1
                           for read only

              open cur_table
              fetch cur_table into @tbl_name
     
                           while @@fetch_status = 0
                           begin
                               
                           Select @tsql_code='Alter Table ' + @tbl_name  +' Add Col1 Varchar(10), Col2 Varchar(10)'
                           Exec (@tsql_code)
                           fetch cur_table into @tbl_name
                           end
                     close cur_table
                     deallocate cur_table


However,  the above code may not work since schema name is missing there, so you need to add the schema name along with the table name like below example:

   Declare @tbl_name as varchar(100)
              Declare @tsql_code Varchar(500)

     Declare cur_table cursor local for
                           select '['+SCHEMA_NAME(schema_id)+'].['+name+']'
                           from sys.tables
                           Where SCHEMA_NAME(schema_id)+'.'+name in ('dbo.Currency','dbo.Sales')
                           order by 1
                           for read only

              open cur_table
              fetch cur_table into @tbl_name
     
                           while @@fetch_status = 0
                           begin
                               
                           Select @tsql_code='Alter Table ' + @tbl_name  +' Add UpdatedAt [DATETIME]'
                           Exec (@tsql_code)
                           fetch cur_table into @tbl_name
                           end
                     close cur_table
                     deallocate cur_table


Hope this will make your life easier.

Unions in OLEDB Source can kill performance in SSIS, How to handle this?

If you write SQL with many UNION in OLEDB source transformation then you will find the SSIS package is running slow.

For example, you may require many SQL unions to populate the data set as like below:
SELECT x,y,z,z1,z2
FROM tblA

UNION
SELECT x,y,z,z1,z2
FROM tblB

UNION
SELECT x,y,z,z1,z2
FROM tblC

UNION
SELECT x,y,z,z1,z2
FROM tblD

UNION
SELECT x,y,z,z1,z2
FROM tblN

Fig 1: OLEDB source 

Instead of using the above SQL in OLEDB source, you can use Union All transformation which is much faster in compare with using the SQL code in OLEDB source.

Fig 2: Union All Transformation

SQL Analytical Function: Teradata Vs. MS SQL syntax

A few years back, I posted about Teradata analytical function where I described how this works and why we need to use analytical function :  https://pauldhip.blogspot.com/2014/12/analytical-function-rownumber-with.html

However, this post includes SQL syntax for MS SQL and how it's different from Teradata.

The SQL Code I have written in Teradata in my earlier post to find latest version of the data by using below:

select INDUST_CODE,INDUSTRY_NAME

from INDUSTRY

QUALIFY

row_number() Over (partition by INDUST_CODE order by ACTIVATE_DATE desc)=1

Do you think, the above syntax will work in MS SQL? Not really!! MS SQL doesn't have QUALIFY function. You need to write the syntax a little different way and  a few extra lines required to achieve same results.

Let's translate the above syntax for MS SQL:

SELECT INDUST_CODE, INDUSTRY_NAME, RANKRESULT

FROM(

SELECT  INDUST_CODE, INDUSTRY_NAME ,

RANK() OVER (PARTITION BY INDUST_CODE ORDER BY ACTIVATE_DATE DESC) AS RANKRESULT  

 FROM INDUSTRY

) TT

WHERE TT.RANKRESULT=1 

As you see, for MS SQL we had to use sub query and RANK function to achieve the same result we got in Teradata.

SQL: Pay attention when using Group by and CASE WHEN statement together

When you are working with SQL and having case when statement together with 

group by then please have a close look at the what are you putting in the case

when statement. 

For example, Cases like below is pretty much straight forward:

 
Select cust_key,
       Cust_site_key,
       Max(rating_key) as rating_key
From Customer_rating
Where year=2018
Group by 1,2
 
However, Now let's look at the below code and see if you find any issue with

this code.

Select
event_key,
Event_code,
CASE Event_code
   When ‘start’ then min(f.event_date_key)
   When ‘Closed’ then max(f.event_date_key)
ELSE
   f.event_date_key
END as c 
FROM
Event_fact f
Join
event_dim ed
On
f.event_key=ed.event_key
Group
by event_key,Event_code

After running above code you will get error with "Failed
3504: Selected non-aggregate values must be part of the associated group."

What can go wrong? Well, you may think; event_date_key is using aggregate

function (min and max) so you don't need to use group by with 

event_date_key. However, if you look closely then will find ELSE part of the CASE

statement also using the event_date_key where no aggregation applied. It means 

we need to use group by with event_date_key.

Hence the correct code will be:

Select

event_key,
Event_code,
CASE Event_code
   When ‘start’ then min(f.event_date_key)
   When ‘Closed’ then max(f.event_date_key)
ELSE
   f.event_date_key
END as c 
FROM
Event_fact f
Join
event_dim ed
On
f.event_key=ed.event_key
Group
by event_key,Event_code,event_date_key

How to handle trailing space while you use LEN function in SQL

LEN function in MS SQL returns the number of characters of the specified string expression, excluding trailing blanks. [1]

It means if your data has trailing blank /space in the string then LEN function will ignore that.

e.g. 

SELECT LEN('DK123456789 ') as LengthofString

Which give you as length 11, though with space it’s 12.

Well, you can say we know it will be 11 why you should have 12? Or what is problem if it returns length as 11?

Let’s discuss the above scenario, consider above string 'DK123456789’ is a valid company VAT registration number. And your customer asked to get only the number part, so output should be ‘123456789’

So you use RIGHT function as like below:

Fig 1: RIGHT function to find number part

And you get the right result.

However, if your data has issue like trailing blank then can you expect the result`? Let’s see:

Fig 2: When you have data issue

So you lost the first digit since RIGHT function consider space/blank as character so you get 9 character from the right side of the value which include one space but discard first character ‘1’.

The above particular example we already know the length, however; when we query SQL table then we don’t know the length so we must calculate length as well.

so let’s create a table and insert some data:

CREATE Table #Table1 (

 [VATRegistration] nvarchar(50))

 insert into #Table1 values('DK123456789 ')

 insert into #Table1 values('DE7891012879')

 insert into #Table1 values('BD989741258')

 select * from #Table1

Fig 3: VAT registration data

We make the SQL query to find out only number part from the above dataset, the query is look like:

SELECT [VATRegistration],RIGHT([VATRegistration],LEN([VATRegistration])-2)  as [VATRegistrationNo]

FROM #Table1   /*Since we know first two characters need to exclude from the VAT number*/

 

Fig 4: Output with error data

Does our result correct? Look closer and will find first row has discarded first digit. Why this happened? Because the data has issue, If you look at the above insert statement you will find it has trailing space. So how do we solve this?

The main problem here is to calculate the LEN, since data LEN will not count space as length. Well can’t we try RTRIM then? Sure let’s do it.

 SELECT [VATRegistration],RIGHT([VATRegistration],LEN(RTRIM([VATRegistration]))-2)  as [VATRegistrationNo]

 FROM #Table1   /*Since we know first two characters need to exclude from the VAT number*/

Unfortunately, result did not change, well; it’s not unfortunate; rather this how it’s built, TRIM function will not have any effect under LEN function.

What can we do then? Yes…, REPLACE must be a good option here.

SELECT [VATRegistration],RIGHT([VATRegistration],LEN(REPLACE([VATRegistration],' ','_'))-2)  as [VATRegistrationNo]

 FROM #Table1   /*Since we know first two characters need to exclude from the VAT number*/

Result look fine now:

 

Fig 5: Correct output by using REPLACE

Since REPLACE make removed the space and put ‘_’ which is count as character. And RIGHT function return 10 character from right to the left which include space as well.

Fig 6: Where is the difference??

 After you get the result you can RTRIM the final value before you present to the report or sending to the business.

[1] https://docs.microsoft.com/en-us/sql/t-sql/functions/len-transact-sql

How to Insert data into a table with Identity column

In general, you don't insert data manually in a table which has an identity column. However, in obvious cases if you need to insert data into the table manually then writing general inset Query will failed with error:

For example, you have a table (DDL) like below:

  Create table PurchaseOrder

   (orderId int identity(1,1),

    item nvarchar(100))

If you would like to insert data into the table, standard syntax for inserting data is like below:

    Insert into PurchaseOrder(orderId,item)

     values (101,'strawberry')

However, above query will failed with below error message:

Fig 1: Eror Inserting data into the table with Identiy column

To insert the data into a table with identity column you should not put any value for the identity column. e.g. 

Insert into PurchaseOrder (orderId,item)
values ('strawberry')  

And then you will see the result set like below:

Fig 2. Output of the above insert command

However, if you would like to explicity put value for the identity column. e.g. you would like to add a negative number as orderid to make an item invalid. (sometimes you may need for the data warehouse dimension and fact tables). SQL for inserting data with your own choice:

SET IDENTITY_INSERT PurchaseOrder ON
insert into PurchaseOrder(orderId,item)
values (-1,'An Island')
SET IDENTITY_INSERT PurchaseOrder OFF

Now the result will look like below:

Fig 3: Output after  the above SQL command

Excel PowerPivot.: How to execute SQL query inside PowerPivot

Power Pivot is one of the best faeture in excel. Though PowerPivot can be used to serve different purposes, however; this post only include: how you can run your SQL code into Powerpivot and manipulte the data into the excel?

Why?

If you use Excel Power Pivot as front end tool then by implementing this process you can avoid asking developer a new excel sheet each time the data is updated.

How?

Before you start you must need to have Powerpivot enable in your excel, if so then your excel Tabs are like below:

Fig 1: Different tabs in the excel

If you don't find the tab 'POWERPIVOT' in your excel then you need to work a little more to add that. Please follow the below link to make it work: 

Step 1 :Make connection with your data source 

Fig 2: Manage option under POWERPIVOT

After you click the manage window you will find a popup window which like below, from there you will have option to connect with data source.

Fig 3. Connect with the data source

You need to follow the wizard to complete the connection. 

Step 2: PowerPivot Design

After you establish connection with the data source, then click on Design tab as like below

                         Fig 4: Design Tab from powerpivot manager

Under the design window you wil find following Tabs like picture and you need to choose 'Table properties'

Fig 5: Design- table properties

And then you need to click Table properites where you will write/paste the SQL code to generate the data directely from the data source.

Fig 6: Add SQl to the table property window

After putting the SQL code in the above window,  Please click 'validate' button before you click the 'Save' button; it will validate your SQL code. Now press 'Save' button then SQL query will run and get the data from the database and populate the data in your excel.

Basic of PIVOT:Why and When to Use PIVOT function in SQL?

 One of the most powerful feature in Excel is Pivot, the folk who work with BI, heavily uses PIVOT table in Excel. However, MS SQL and Oracle has function called PIVOT that can transpose your rows into column. Though, Teradata doesn’t have the PIVOT functionality but you can achieve the by using many lines of SQL code.

Let’s start with an example, say my below table holds information about my family expenses:

Shop	Prod_Type	Price
Fotex	Grocery	560.00
Fotex	Medicine	300.00
Fotex	Electronics	2000.00
El-giganten	Electronics	5600.00
Netto	Grocery	420.00
Netto	Medicine	250.00

     Fig 1: Family expenses data

If you look at the data above you will find there are three different types of product e.g. Grocery, Electronics and Medicine.

However, I would like to see the data in different way which can give much clear view in terms of product type I regularly buy e.g.

Shop	Grocery	Electronics	Medicine
El-giganten	NULL	5600.00	NULL
Fotex	560.00	2000.00	300.00
Netto	420.00	NULL	250.00

     Fig 2: Transposed view (rows turned into column)

By looking at the above data, I can easily see different sector of my family expenses.  To get the above data view I had to transpose rows into the columns. And this can be accomplished by using PIVOT function in MS SQL.

Now, the fun part, SQL Coding:

Step1: Create a temporary table

CREATE TABLE #tempFamilyExpense

(

Shop varchar(15),

Prod_Type varchar(20),

Price decimal(10,2)

)

Step 2. Insert some dummy data

INSERT INTO #tempFamilyExpense VALUES ('Fotex','Grocery',560.00)

INSERT INTO #tempFamilyExpense VALUES ('Fotex','Medicine',300.00)

INSERT INTO #tempFamilyExpense VALUES ('Fotex','Electronics',2000.00)

INSERT INTO #tempFamilyExpense VALUES ('El-giganten','Electronics',5600.00)

INSERT INTO #tempFamilyExpense VALUES ('Netto','Grocery',420.00)

INSERT INTO #tempFamilyExpense VALUES ('Netto','Medicine',250.00)

Step 3. Look at the current data and then make the view as figure 2 by using PIVOT

/** Show original table **/

SELECT * FROM  #tempFamilyExpense

** Create transpose using PIVOT **/

SELECT *

FROM #tempFamilyExpense

PIVOT

 (

 SUM(Price)

 FOR Prod_Type

 IN (Grocery,Electronics,Medicine)

 )

 AS pv

You will find many cases where you have to make columns by using the row data (columns value) then PIVOT is your friend to help with that.

MS SQL: How to insert data into a table from Excel

One of my previous post I have written how to insert data from excel/csv file to Teradata table (URL: http://pauldhip.blogspot.dk/2015/02/insert-data-from-excel-to-terdata-table.html).This time, I am going to explain how to do that in MS SQL.

Lets say, we have a table in the database named Customer_dim which have following columns:

CREATE TABLE [dbo].[Customer_dim](
[Customer_id] [nchar](10) NULL,
[Customer_First_Name] [nchar](10) NULL,
[Customer_Last_Name] [nchar](10) NULL,
[Martial_Status] [nchar](10) NULL
) ON [PRIMARY]

The table need to filled by data from excel. To do so, we need to follow the following steps:


Step 1: Save MS excel file into CSV
The excel file should have same number of columns as in the table.

Fig 1: Data in the excel

Now save the excel data as .csv file.

Fig 2: Save as .csv

Step 2: Write code to insert data from CSV

BULK INSERT [dbo].[Customer_dim] FROM 'C:\BI Projects\Blog\Dim_Customer_CSV.csv'
WITH (
    CHECK_CONSTRAINTS,
    DATAFILETYPE='widechar',
    FIELDTERMINATOR='|',
    ROWTERMINATOR='\n',
    KEEPIDENTITY,
    TABLOCK
);

Since, my csv file is saved as pipe delimiter so that above SQL code has FIELDTERMINATOR='|'. You need to change that as per your csv format. As well as,vyou need to change your csv file loacation accordingly.

After running the above SQL code, you should able to see below records are inserted to the table.

Fig 3: Result set after inserting the data

Teradata DBC : Easy way to get DDL of views, tables and macros!!

 As a database developer you may need to take back up of DDL of views, macros and tables for a particular database that you are working on. Or when your development work is done then you need to move table/macro structure (DDL) from development environment to test and test to production.

And those cases, how do you move DDL from Development environment to Test environment and test to Production?

You can copy DDL from database one by one and then paste it to a document to save it and then forward the document to the DBA to execute your DDL in Test or Production environment.

 If you would like to copy DDL of the table then you do like below:

Fig 1: Manually copy DDL one by one

If you have twenty(20) tables, 10 macros or 5 views then you have to do the above action 35 times which is time consumable, error prone and boring tasks.

Then, what could be the better way to do it?

You can use DBC to get DDL of views, macros and tables for one particular database by using below SQL:

select * from dbc.tables where databasename='Customer_Service'

order by TableKind desc

Result look like below:

Fig 2: DBC result set

From the above result set (Fig 2),  you will find a column named RequestText that holds DDL for all elements exist in the database.

To find DDL for table; SQL will look like below:

Select RequestText from dbc.tables where tablekind='T' and databasename='Customer_Service'

tablekind=T means table

tablekind=V means views

tablekind=M means macro

By changing tablekind column's value you can get DDL for different object in the database.