Thursday, June 27, 2019

SQL Server - What are Constraints in SQL?

Constraints are the rules and restrictions to apply on the type of data in a table. They specify the limit on the type of data that can be stored in a particular column in a table using constraints and also maintain the data integrity and accuracy in the table. They also ensure the unwanted data can't be inserted into tables.

Note: What data is valid and what is invalid can be defined using constraints.

How to specify constraints?
Constraints can be easily classified in the following two types-
n  Column Types Constraints
n  A column-level constraint references a single column and is defined along with the definition of the column at the time of creating the table using CREATE TABLE statement
n  Any constraint can be defined at the column level except for a COMPOSITE primary key constraints
n  Table Types Constraints :
n  A table-level constraint references one or more columns and is defined separately after the creation of the table using the Alter Command
n  All constraints can be defined at the table level except for the NOT NULL constraint.

Syntax: Naming of a Constraint
n  The general convention used for naming constraints is
                           <table name>_<column name>_<constraint type>
n  table name is the name of the table where the constraint is being defined,
n  column name is the name of the column to which the constraint applies,
n  constraint type is an abbreviation used to identify the constraint’s type

n  For example, a constraint name
                                          emp_deptno_fk refers to:
n  A constraint in table EMP on column DeptNo of type foreign key. 

                                          dept_deptno_pk refers to:
n  A constraint in table DEPT on column DeptNo of type primary key

Most Popular Constraint in SQL
n  NOT NULL: This constraint ensures that the column has a value and the value is not a null value. A space or a numeric zero is not a null value and can defined at Column level only

n  UNIQUE: This constraint when specified with a column or set of columns be unique, tells that all the values in the column must be unique. That is, the values in any row of a column must not be repeated

n  PRIMARY KEY: Also known as the entity integrity constraint, is a field which can uniquely identify each row in a table. It creates a primary key for the table. A table can have only one primary key constraint. If a table uses more than one column as its primary key (i.e., a composite key), the key can only be declared at the table level. 

n  FOREIGN KEY: Also known as referential integrity constraint, is a field which can uniquely identify each row in another table. This constraint is used to specify a field as foreign key and it establishes a relationship with the primary key of another table.
Foreign key and referenced primary key columns need not have the same name, but a foreign key value must match the value in the parent table’s primary key value

n  CHECK: This constraint helps to validate the values of a column to meet a particular condition. That is, it helps to ensure that the value stored in a column meets a specific condition

n  DEFAULT: This constraint specifies a default value for the column when no value is specified by the user



Friday, June 21, 2019

SSRS - How to create a Dashboard Report

In this tutorial, we will learn "How to create a Dashboard Report" in SSRS. 
A dashboard may be a combination of multiple sub-reports or can use the multiple components to create your dashboards such as various type of charts, matrix, tablix, list, gauges, indicators as per your requirements.

You will see the step by step approaches to create SSRS dashboard reports that holds a different kind of reports with an example. 
For this SSRS Dashboard example, we are going to use the Shared Data Source, and Shared Dataset.
  
Before creating a dashboard, you should determine the dashboard content and audience by working high level executives to develop your Key Performance Indicators because your dashboard is not your dashboard. It's the company's dashboard. As such, you need to find a high level business owner that will partner with you on it as you develop it. You need to show them your work at several points during the construction process, and make modifications when needed. Consider it a partnership. 
Before implementing the dashboard solution, stage a demo and get the advice of a couple of top level Executives on content, presentation and security. Let them think about it and get back to you.

"A digital dashboard, also known as an enterprise dashboard or executive dashboard, is a business management tool used to visually ascertain the status (or "health") of a business enterprise via key business indicators. Digital dashboards use visual, at-a-glance displays of data pulled from disparate business systems to provide warnings, action notices, next steps, and summaries of business conditions." -Wikipedia.com

We are using four different charts to create dashboard-
  • Column Chart - To show the sales vs taxes performance 
  • Bar Chart - To display the top sales performer 
  • Pie Charts - To show the sales contribution by product category
  • Area Chart - To display the sales vs taxes by product category

Few points to remembers -
  • Use the same font size for the header text and other text
  • Understand the steps involved in formatting labels, legends and pallets
  • Adjust the Charts in SSRS dashboard to look like a Group

To Learn more, please visit us at - 

Wednesday, June 19, 2019

SSIS Tutorials - Union All Transformation | Combine data from multiple sources

In this tutorial, we will learn " How to combine data from multiple homogeneous or heterogeneous source" by using Union All Transformation in your SSIS Package. 
The Union All transformation combines multiple inputs into one output. For example, the outputs from five different Flat File sources can be inputs to the Union All transformation and combined into one output.
The transformation inputs are added to the transformation output one after the other; no reordering of rows occurs. If the package requires a sorted output, you should use the Merge transformation instead of the Union All transformation. 
Union All Transformation in SSIS does not follow any particular order while merging the data and storing in the destination table. Supports multiple unsorted inputs and duplicate records can be possible in the final output file. It does have any error output. 

If the mapped columns contain string data and the output column is shorter in length than the input column, the output column is automatically increased in length to contain the input column. Input columns that are not mapped to output columns are set to null values in the output columns.
Used Components -
1. Data Flow Task
2. Source Assistants - Flat File, Csv file and SQL Server
3. Merge Join Transformation : Merge the sorted inputs
4. Sort Transformation : To Sort the data and remove duplicates
5. Row Sampling: To watch the final output

To Learn more, please visit us at YouTube Channel- 

Note: Combine the data from multiple sources, then there are more chances to having the duplicates in the final output. Hence, to remove duplicates,  one should combine a “UNION ALL” component with a “SORT” component. Because SORT Component provides an option to remove the duplicate rows and achieves the same thing as “UNION” query does in SQL.

Tuesday, June 18, 2019

Server 2017 Standard vs Enterprise

In-memory OLTP and in-memory Columnstore are limited to the amount of memory specified by edition in the Scale Limits section. Columnstore and Real-time Operational Analytics are also limited to two degrees of parallelism (two cores/query) in SQL Server Standard edition. 


SQL Server 2017
Enterprise
Standard
The premium offering, SQL Server Enterprise edition delivers comprehensive high-end data-center capabilities with enabling high service levels for mission-critical workloads and end user access to data insights.
SQL Server Standard edition delivers basic data management and business intelligence database for departments and small organizations to run their applications and supports common development tools for on-premise and cloud - enabling effective database management with minimal IT resources.
It offers fast performance, an unlimited amount of virtualization, advanced analytics, maximum memory utilized per instance, adaptive query processing and end-to-end business intelligence.
The Standard Edition supports common tools for development of both on-site and virtual systems with basic reporting, basic analytics, end-to-end database security and enhanced memory performance.
It can provide the solution for businesses that demand high service levels for their workloads and also require end-user access to data insights.
It allows a maximum of 24 processor cores, and some of its defining features include 128 GB memory buffer pool.

An instance as an installation of SQL Server. A single installation of SQL can run any number of databases - big, small, OLTP, OLAP, doesn’t matter. But I’m Standard edition, the instance will not exceed 128GB of RAM whereas Enterprise edition has no limit - it will consume as much as the OS supports.
It should also technically be noted, that if you have two instances of standard, each would use up to 128GB, which would actually use up to 256GB on the system. Not that multiple instances on the same server is recommended.
The buffer pool is used by all databases (i.e. it is not per database). The max on standard is 128GB. So if you're server has 200GB of member then 72GB will not be used by SQL's buffer pool. That 72GB will be used by the OS for it's stuff including any running programs etc... so it might be over sized but not by a heck of a lot.
Note that the 128GB limit is only for the buffer pool. There are other caches/pools in Standard that get added on top of this. And memory for running queries is also added on top of this as well. So it's entirely possible for a Standard to consume allot more than 128GB of memory. Depending on your workload it might be warranted to allocate 200GB memory to a Standard edition.
One other thing that stinks about standard:
a. Columnstore indexes limited to 32GB total for the entire install (multiple databases all share the same cap)
b. Worse: index and compression both share the same CPU core, and are limited to a single core. so indexes take forever, if you add row/page/columnstore it makes that "Forever" taking even more forever time. ROW has minimal impact from my experience but page/columnstore add 3-5x to the run time.
Creating an index on standard with a few hundred million records often took 60-90 minutes. adding compression (row) added only a few minutes, but page/columnstore several hours.

Conclusion

Despite many similarities between the two editions for management tools such as RDBMS manageability, Development Tools, and Programmability but they provide a clear picture to meet in your business requirements that Enterprise delivers more functionality than the other editions of SQL Server, including the Standard edition.  In the one hand, a company that has a need for all the additional features and scalability of Microsoft SQL Enterprise, should opt for this edition but other hand whereas, smaller organizations may be able to utilize the foundation offered by SQL Server Standard edition.

Thursday, June 13, 2019

SSIS Tutorials - Merge Join || Combine data based on conditions

In this tutorial, we will learn " How to merge data from two homogeneous or heterogeneous source" by using Merge Join Transformation in your SSIS Package. 

SSIS merge join also combines data from different sources (Source of same type or different type (heterogeneous)) into single output or merged output. It uses merge concept based on (Inner, Left and full). We can use Merge Join based on specific condition like combining data on matching keys with that Inner, Left and full. Merge Join component accepts only 2 sorted (compulsory) inputs and one output and one error output.

Unlike Merge, Merge Join combines data depending on matching keys or string name.
Used Components -
1. Data Flow Task
2. Source Assistants - Flat File and SQL Server
3. Sort Transformation : To Sort the data
4. Merge Join Transformation : Merge the sorted inputs
5. Row Sampling: To watch the final output


What is the difference between merge and merge join in SSIS?
Merge transformation in a data flow, we can perform the following tasks:
  1. Merge data from two data sources, such as tables and files.
  2. Create complex data-sets by nesting Merge transformations.
  3. Re-merge rows after correcting errors in the data.

Merge Join transformation in a data flow, we can perform the following tasks:
  1. Specify the join is a FULL, LEFT, or INNER join.
  2. Specify the columns the join uses.
  3. Specify whether the transformation handles null values as equal to other nulls.

SSIS Tutorials - Merge Transformation || Merge data from two sources

In this tutorial, we will learn "How to merge data from two homogeneous or heterogeneous source" by using Merge Transformation in your SSIS Package. 

Ideal Problem: If you have 2 separate csv files/text files/excel files, one with multiple headings and one with just 2 headings, both have one heading with the same name and data underneath. Here, you want to create just one file from both files before processing it further.
Solution: Use Merge Transformation to create a just one file

What is Merge Transformation?
SSIS merge works similar way to a SQL join it merges the two or more different sources (Sources can be of same type or different type / heterogeneous) into a single output. SSIS is all about collecting data from different sources, If you want to merge the collected data from different sources then we can use merge component. Merge component accepts only 2 sorted (compulsory) inputs. if there is more than 2 inputs then it best to use Union All transformation component. Also Merge transformation have only 1 output and does not have any Error output.

Before using merge transformation, you should take care of the following things-

  1. Data should be sorted in the both sources
  2. Field data type must be in the same type in both sorted outputs
  3. Merge transformation have only 1 output 
  4. Does not have any error output
Used Components -
1. Data Flow Task
2. Source Assistants - Flat File and SQL Server
3. Sort Transformation : To Sort the data 
4. Merge Transformation : Merge the sorted inputs
5. Row Sampling: To watch the final output.

To see the demo, please visit us YouTube channel -


What is the difference between merge and merge join in SSIS?
Merge transformation in a data flow, we can perform the following tasks:
  1. Merge data from two data sources, such as tables and files.
  2. Create complex data-sets by nesting Merge transformations.
  3. Re-merge rows after correcting errors in the data.

Merge Join transformation in a data flow, we can perform the following tasks:


  1. Specify the join is a FULL, LEFT, or INNER join.
  2. Specify the columns the join uses.
  3. Specify whether the transformation handles null values as equal to other nulls.

Wednesday, May 22, 2019

Talend ETL - How to remove special characters in the string

Talend allows us to define each data lake as a data source and then develop processes to combine and filter data from those sources to produce new products (reports, new data, spreadsheets, etc.) All this is done using a diagrammatic interface based on Eclipse.

tMap Component Transformation 
The tMap component is part of the Processing family of components. tMap is one of the core components and is primarily used for mapping input data to output data, that is, mapping one Schema to another.
The Map Editor allows us to enter a Mapping Expression for each of the columns in each output Schema.

As well as performing mapping functions, tMap may also be used to Join multiple inputs, and to write multiple outputs. Additionally, we can Filter data within the tMap component. We'll cover these features in a later article.

If you want to remove any special characters from the input string/text then you can achieve this by using a tMap component. tMap Component Transformation comes with two replace() and replaceAll() function where replaceAll() works with regular expressions, replace() works with CharSequence. You have to take care of replace function in tMap components. To use tMap with the following expression to get the expected result:
To remove all non-digit characters from a string
row1.inputField.replaceAll("\\D", "")

To remove all digits characters from a string
row1.inputField.replaceAll("[0-9]", "")

To remove all digits characters from a string
row1.inputField.replaceAll("*,&;!", "")



To remove all digits characters from a string
row1.inputField.replace("??", "")
This will replace requested characters by nothing. If you want to learn the whole process then you can watch our demo at YouTube Channel also -



Tuesday, May 7, 2019

SQL Server - How to get difference between two dates

If you are working as a SQL Professional then there is a lot of activities which are related to dates. You have to write SQL queries to find out the difference between two dates or need to get the next day, next month, next week, next quarter or next year to set some business logic. 
Business Scenario 
Suppose that you need to send an email alert to your customers to remind them to pay the invoice bill after 30 days of invoice generation as per their contract. Hence, you have to write an SQL based program to check all the unpaid invoices which comes under this conditions. 
Here, In your SQL based program (it may be a stored procedure which may be schedule to run on the daily basis on the defined schedule), you have to get the difference between the invoice date and current running date. 
If the difference result is matched with the contract with the customer then system will pull that invoice and send it to the customer as the email attachment. 
To full-fill the above business needs, you have to use some defined SQL Date related functions such as -
DateDIFF(interval, date1, date2) is used to get the differences between two dates and return numeric values such as difference in years, months, quarters, days, weeks, hours, minutes, seconds, milliseconds and nanoseconds etc. The interval of time is used to calculate the difference between date1 and date2. It can be one of the following values:
Value (any one of)
Explanation
year, yyyy, yy
Year interval
quarter, qq, q
Quarter interval
month, mm, m
Month interval
dayofyear
Day of year interval
day, dy, y
Day interval
week, ww, wk
Week interval
weekday, dw, w
Weekday interval
hour, hh
Hour interval
minute, mi, n
Minute interval
second, ss, s
Second interval
millisecond, ms
Millisecond interval

Examples
---- Difference in years
SELECT DATEDIFF(year, '2012/04/28', '2014/04/28');
Result: 2
---- Difference in months
SELECT DATEDIFF(month, '2014/01/01', '2014/04/28');
Result: 3
---- Difference in days
SELECT DATEDIFF(day, '2014/01/01', '2014/04/28');
Result: 117
---- Difference in hours
SELECT DATEDIFF(hour, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 2
---- Difference in minutes
SELECT DATEDIFF(minute, '2014/04/28 08:00', '2014/04/28 10:45');
Result: 165

To watch a demo video, please visit our YouTube channel -

Monday, May 6, 2019

SQL Server - Treat Null Values in Count() function

As we know that the COUNT function can tell us the total number of rows returned in a result set (both NULL and non-NULL together but it is also depending on how it’s used). 


For example, 

  • Using SELECT COUNT(*) or SELECT COUNT(1)  (which is what I prefer to use) will return the total of all records returned in the result set regardless of NULL values. 
This is because, we are not specifying any expression in the count function. So, this function is returning all the rows including null values also.
  • Using COUNT (<Specific Column Name Here>will count the number of non-NULL items in the specified column (NULL fields will be ignored).
  • COUNT (<expression>) counts rows where the <expression> is not null.
Thus, we could find the number of NULL fields in the result set by subtracting the non-NULL fields from the Total fields. 

Note: In SQL Server, since the COUNT (and other aggregate functions) will ignore NULL values, we use the CASE to turn NULLs into values and values into NULLs


In the below example, we are creating a data-set with the help of common table expression and using the count function -
--This will return count of 2
;WITH CTE
    AS (
    SELECT NULL AS Id
    UNION ALL
    SELECT 1 AS Id
    )
    SELECT COUNT(*) FROM CTE

--This will return count of 1
;WITH CTE
    AS (
    SELECT NULL AS Id
    UNION ALL
    SELECT 1 AS Id
    )
    SELECT COUNT (Id)  FROM  CTE

 So, it depends on you, how can you use count function in your SQL scripts. 



Popular Posts

Get Sponsored by Big Brands