SQL Server - How does Not Null Constraints Work

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.

What is Not Null constraints?

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. The basic rule for Not Null constraint is that A Not null constraint restrict the insertion of null values into a column. 

How can we create Not Null constraints?

We can create Not Null constraints at two levels -

Column Level Not Null Constraints - We can define Not Null constraints with CREATE TABLE statement or in time with table definition. 

For example, the below query creates a table Employee with the column fields EmpId, FirstName, LastName and Age as NOT NULL. 

---- Table Definition with Not Null Constraints CREATE TABLE TBL_EMPLOYEE ( EmpId int NOT NULL, FirstName varchar(25) NOT NULL, LastName varchar(25) NOT NULL, Age int NOT NULL ) ---- Insert values into table INSERT INTO TBL_EMPLOYEE (EmpId, FirstName, LastName, Age) VALUE (101,’Ryan’,’Arjun’,22)

Thus, we cannot insert the null values in these columns whenever we are going to insert/update the data into this table.

Table Level Not Null Constraints -

If you have inserted the data into your data table and want to add new column with Not Null Constraints then SQL will not allow this operation on that table because existing data does not have any values for this new column. Hence,

1. You can add new column with NOT NULL having default definition
2. Or new column with NOT NULL being added is an identity
3. Or timestamp column

For example,

---- NOT NULL being added is an identity ALTER TABLE TBL_EMPLOYEE ADD Id int NOT NULL IDENTITY(1,1) ---- NOT NULL having default definition ALTER TABLE TBL_EMPLOYEE ADD CreationDate DateTime NOT NULL Default(GetDate())

Conclusion - Once NOT NULL constraint is applied to a column, we cannot pass a null value to that column. It enforces a column to contain a proper value.

To see the demo, please visit our YouTube Channel -

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. click here to know How does Not Null constraint work?

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. click here to know How does Default constraint work?

To watch a series of constraints, please visit our YouTube channel -

Conclusion - Constraints are rules and regulations which simply apply on the data columns to meet some business standards and strictly attached within the column definitions. They give us a surety to us the data will be inserted as per their definition and any unwanted data will not be entertained by these SQL constraints. 

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 - 

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.

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.

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.