What is MSBI?

MSBI> Microsoft Business Intelligence : It’s the Microsoft suite comprising of various tool for implementing Business intelligence solutions.

So, Converting data into information is the BI. And that information is the knowledge to make proper decisions.

Converting data into information involves the below steps –

  1. Data- It can be in any file or in any format.
  2. ETL- Extract, Transform and Load. This process called SQL SERVER INTEGRATION SERVICE (SSIS) or we can say that ETL is a process to load data into Data Warehouse. 1. Extract- read data from data source 2. Transformation – convert data to the one format which data warehouse is expecting. Example – data source may contain DateOfBirth but Data Warehouse expects Age. Converting DateOfBirth to Age is called Transformation. This is not a compulsory step. In some situation in may be possible that data is already in the desired format. In that case this step will skipped. 3.Load – Finally the data will be dumped into Data Warehouse.
  3. Data Warehouse- Place where all the data will be kept. 
  4. Analysis – SSAS –  It is kind of storage space like Data Warehouse but here data will be stored in more performance efficient manner. Data retrieval will be faster here compared to normal Data Warehouse.
  5. Display – SSRS – Let us create reports which display data in nice graphical way with the help of various charts, images etc. SSRS reports are final source of information to end user. By looking into end user will make decisions.
  6. Information- is the knowledge to make decisions.

SQL- 1st, 2nd and 3rd NORMAL FORMS

1st Normal Form- 

  1. Break data into some smaller logical pieces i.e create separate columns for each small info like first name, middle name etc…
  2. Avoid repeating data and repeating groups i.e create Master table of records like city and use their IDs instead writing city names each time.

2nd Normal Form-

  1. All column in a table should depend on the full primary key and not the partial. Ex. A student is depend on standard and not on syllabus & subject but standard belongs to subject and syllabus. This normal form implemented via using a 1 to many relationship table.

3rd Normal Form-

  1. No Column in a table should not​ depend on the other column. Ex. Student marks Average column

SQL SERVER : Transactions

A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.

Transaction modes :-

1)Autocommit transactions
Each individual statement is a transaction.
2)Explicit transactions
Each transaction is explicitly started with the BEGIN TRANSACTION statement and explicitly ended with a COMMIT or ROLLBACK statement.
3)Implicit transactions
A new transaction is implicitly started when the prior transaction completes, but each transaction is explicitly completed with a COMMIT or ROLLBACK statement.
4)Batch-scoped transactions
Applicable only to multiple active result sets (MARS), a Transact-SQL explicit or implicit transaction that starts under a MARS session becomes a batch-scoped transaction. A batch-scoped transaction that is not committed or rolled back when a batch completes is automatically rolled back by SQL Server. Continue reading

SQL SERVER : JOINS

SQL Server : JOINS are used to retrieve data from multiple tables. A SQL Server JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of SQL Server joins:

1) INNER JOIN                       -or-              SIMPLE JOIN
2) LEFT JOIN                        -or-              LEFT OUTER JOIN
3) RIGHT JOIN                       -or-              RIGHT OUTER JOIN
4) FULL JOIN      -or-              FULL OUTER JOIN
5) SELF JOIN
6) CROSS JOIN

INNER JOIN:-   INNER JOINS return all rows from multiple tables where the join condition is met.

INNER JOIN would return the records where table1 and table2 intersect.

SYNTAX :-


SELECT 
      COLUMNS
FROM Table1 
      INNER JOIN Table2
ON Table1.column = Table2.column;

EXAMPLE :-

 

LEFT JOIN :- T his type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

SYNTAX :-


SELECT 
      COLUMNS
FROM Table1 
      LEFT JOIN Table2
ON Table1.column = Table2.column;

EXAMPLE :-

 

RIGHT JOIN :- This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

SYNTAX :-


SELECT 
      COLUMNS
FROM Table1 
      RIGHT JOIN Table2
ON Table1.column = Table2.column;

EXAMPLE :-

 

FULL JOIN :- This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

FULL OUTER JOIN would return the all records from both table1 and table2.

SYNTAX :-


SELECT 
      COLUMNS
FROM Table1 
      FULL JOIN Table2
ON Table1.column = Table2.column;

EXAMPLE :-

 

SELF JOIN :- A self join is simply when you join a table with itself. There is no SELF JOIN keyword, you just write an ordinary join where both tables involved in the join are the same table. One thing to notice is that when you are self joining it is necessary to use an alias for the table otherwise the table name would be ambiguous.

It is useful when you want to correlate pairs of rows from the same table, for example a parent – child relationship.

SYNTAX :-


SELECT 
      tbl1.column_name, 
      tbl2.column_name... 
FROM table1 tbl1
JOIN table1 tbl2
ON tbl1.common_filed = tbl2.common_field;

Why we need a self join :-  Suppose we have the following table – that is called employee. The employee table has 2 columns – employee_name  and employee_location:-

Now, suppose we want to find out which employees are from the same location as the employee named A. In this example, that location would be India. Let’s assume – for the sake of our example – that we can not just directly search the table for people who live in A with a simple query like this (maybe because we don’t want to hardcode the city name) in the SQL query:


SELECT employee_name FROM employee
WHERE employee_location = "A"

So, instead of a query like that what we could do is write a nested SQL query (basically a query within another query – which more commonly called a subquery) like this:

SELECT employee_name
FROM employee
WHERE employee_location in
( SELECT employee_location
FROM employee
WHERE employee_name = "A")

Using a subquery for such a simple question is inefficient. Is there a more efficient and elegant solution to this problem?
It turns out that there is a more efficient solution – we can use something called a self join. A self join is basically when a table is joined to itself.

SELECT 
 E1.Employee_Name,
 E1.Employee_Location
FROM 
 Employees E1, Employees E2
WHERE 
 E1.Employee_Location = E2.employee_Location AND E2.Employee_Name='A';

EXAMPLE-1:-

EXAMPLE-2:-

CROSS JOIN :- The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

SYNTAX :-


SELECT 
      tbl1.column_name, 
      tbl2.column_name... 
FROM table1 tbl1
CROSS JOIN table1 tbl2 

EXAMPLE-1:-


Programming is Easy…

List of all Tables without Indexes in MS SQL

How to get the list of all tables that are not having any indexes on them :
Today, I got a question on this and decided to put the way here also.

SELECT [name] AS Tables_Without_Indexes FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’IsIndexed’) = 0
ORDER BY 1;

The above statement will give the tables that are not having any indexes..