Archive for the ‘MS BI’ Category

SQL Server Interview Question -Part 2

September 14th, 2010 by admin | No Comments | Filed in MS BI

What is joins ?

Joins a way to get data from multiple tables . By join, you can retrieve the data from two or more tables based on logical relationship between tables .

Different types of joins :-

  1. Inner join :-Inner join get the matches record from both the tables .
  2. Outer Join
    1. Left Outer Join :- The Result sets of left outer join get all the records/rows from the left tables . When the rows from left table has no matching record from right table , then associated rows show null values for all columns.
    2. Right Outer join :- The result sets of the right outer join contain all the records / rows from the right tables .
    3. Full Join or Full outer  Join :- A full outer join returns all rows in both the left and right tables.
  3. Cross Join :- Cross joins return all rows from the left table. Each row from the left table is combined with all rows from the right table. Cross joins are also called Cartesian products.
  4. Self Join :- if we want to use two instance of the same table is calld self join .

What is Constraints in SQL Server ?

A constraints is a property assigned to a column or a set of column in a table that prevent certain types of inconsistency data. SQL Server constraints allows you to enforce rules in your database .

How many types of constraints in SQL Server ?

Basically there are six types of constraints supported by SQL Server .

  1. UNIQUE Constraints :-A Unique constraints assigned to a column or a set of column in a table that a column may not contain duplicate values .Unique Key constraints uniquely identify the each row .Unique Key Constarints support NULL value also .
  2. Primary Key Constraints :- A Primary Key Constraints assigned to a column or a set of column to uniquely identify the each row from table . You can create only one primary key constraints in table . Primary Key not support NULL values .
  3. Foreign Key Constraints :- Foreign Key are fields in table that match the primary key to other table .Foreign Key describe the referential integrity concepts .Apart of these constraints there are check constraints , not null constraints ,default constraints  etc.

What is Sub query ?

A Sub Query is T SQL Statement that is nested inside other sql query .

Tags: , , , , , , , , , ,

SQL Server Interview Question

September 13th, 2010 by admin | No Comments | Filed in MS BI

What is DBMS ?

A Database Management System (DBMS) is a set of programs that controls the creation, maintenance, and the use of a database. A DBMS provide a systematic way to organize and retrieving the data from database.

What is RDBMS ?

A RDBMS is a database management system that is based on the relational model .RDBMS store the data into multiple table and the table are related to each other . RDBMS provides the concepts of relationship integrity . RDBMS describe how the table are related to each other .

What is collation in SQL Server ?

Collation are a set of rules that determine how the data is stored and compared .

Each SQL Server collation specify three properties :-

What is authentication mode ?

SQL Srever have two authentication mode . These are :-

  1. Windows Authentication Mode :-

When a user connects through a Windows NT 4.0 or Windows 2000 user account, SQL Server revalidates the account name and password by calling back to Windows NT 4.0 or Windows 2000 for the information. Windows Authentication provide following benefits such as secure validation and encryption of passwords, auditing, password expiration, minimum password length, and account lockout after multiple invalid login requests. Windows Authentication utilizes Kerberos security protocol

2.Mixed Mode (Windows Authentication and Sql Server Authentication Mode ) :- When a user connects with a specified login name and password from a nontrusted connection, SQL Server performs the authentication itself by checking to see if a SQL Server login account has been set up and if the specified password matches the one previously recorded. If SQL Server does not have a login account set, authentication fails and the user receives an error message.

What is Normalization ?

Normalization is a way to organize the data into table in good manner. Database normalization is the process of removing redundant data from your tables to improve the database efficiency. Normalization provides the data integrity and scalability.

First Normal form (1NF): A relation is said to be in 1NF if it has only single valued attributes, neither repeating nor arrays are permitted.

Second Normal Form (2NF): A relation is said to be in 2NF if it is in 1NF and every non key attribute is fully functional dependent on the primary key.

Third Normal Form (3NF): We say that a relation is in 3NF if it is in 2NF and has no transitive dependencies.

Boyce-Codd Normal Form (BCNF): A relation is said to be in BCNF if and only if every determinant in the relation is a candidate key.

Fourth Normal Form (4NF): A relation is said to be in 4NF if it is in BCNF and contains no multi valued attributes.

Fifth Normal Form (5NF): A relation is said to be in 5NF if and only if every join dependency in relation is implied by the candidate keys of relation.

Domain-Key Normal Form (DKNF):  We say that a relation is in DKNF if it is free of all modification anomalies. Insertion, Deletion, and update anomalies come under modification anomalies.

SQL Server 2005 default database

By default there are four database created in sql server .

  1. Master Database :- The Master database hold all the information of database located into sql server instance or created .the database includes information such as system logins , system configuration , linked server and general information regarding the other system and user database .
  2. Model Database :- model is essentially a template database used in creation of new user database . All the information related to stored procedure , views , users etc stored into template database .
  3. Tempdb Database :- Tempdb holds temporary objects such as global and local temporary tables and stored procedure . This database is recreated every time SQL Server starts, and the objects contained in it will be based upon the objects defined in the model database. When you are executing any queries or stored procedure, every times the temporary table or views is created and stored into tempdb table.
  4. MSDB Database :- The MSDB Database stores information regarding of backup , Sql agent information , DTS package ,Sql Server jobs and some replication information such as for log shipping.

What is DBCC commands ?

DBCC stands for Database Consistency checker . We use these commands to check the consistence of the database. i.e maintenance , status checks , validation task .

Database consistency checker statements are grouped into the following categories :-

  1. Maintenance: - Maintenance task on database , file group ,index .
  2. Validation :- Validation operation on database , file group , index ,catalog etc.
  3. Informational :- Task that gather and display various types of information .
  4. Miscellaneous :- Miscellaneous tasks such as enabling trace flags or removing a DLL from memory.

How do you read transaction logs?

To Read transaction log , we use DBCC command and pass database name as a parameter

DBCC LOG (Your_Database_Name, 2)

What Command do you use to rename db ?

Sp_renamedb “oldname” ,”newname “

Tags: , , , , , , , , , , , , , , ,

Execute Process Task

May 20th, 2010 by admin | 1 Comment | Filed in MS BI-SSIS

The Execute process task execute a windows or console application .

Process to Create Package :-

a)      Open Business intelligence Development studio (BIDS) –> select File –>New Project  —> choose Integration service Project

Execute-Process.1

b)      Put the project name and press ok button .The Project will be created and screen will be look like this and you will find default package.dtsx package file in solution explorer.Rename the package name whatever you would like to change .

Execute-Process-package.2

c)      Drag and drop Execute Process Task  to execute console or window application .The following screen will be appear :-

Execute-Process.3

d)      Now double click on execute process task .The Execute Process Task editor will be appear . Now go to Process tab and Executable properties enter the name of console or windows application path , whatever you would like to process. Here I want to execute calc.exe That’s why I have entered calc.exe name in executable Properties .Which is shown in picture bellow :

Execute-Process.4.0

e)      Now Click Ok Button and run the package . The calculator will be appear when the package will be execute .

Execute-Process.5

Bulk Insert Task

May 10th, 2010 by admin | 2 Comments | Filed in MS BI-SSIS
  1. 1.       Bulk Insert Task: – The bulk insert task allows to insert bulk data from a flat file into Sql Server . This task hasn’t any data flow.

Process to Create Package :-

a)      Open Business intelligence Development studio (BIDS) à select Fileà New Project àchoose Integration service Project

Main

a)      Put the project name and press ok button .The Project will be created and screen will be look like this and you will find default package.dtsx package file in solution explorer.Rename the package name whatever you would like to change .

Package

a)      Drag and drop Bulk Insert   Task  to transfer bulk data from Flat file to Sql Server ..The following screen will be appear :-

Bulk_Insert

a)      Press double click on Bulk insert task , the properties page will be appear .In Bulk Insert task you have to choose source connection and destination connection . Bulk Insert task basically transfer the flat file data to sql server so obvisally source connection will be your flat file and desitination will be database.Now click source connection and browse button to select a flat file to do further process

Bulk_Insert_Browse

a)      Choose file and press open button for process the file

Bulk_Insert_Browse2

a)      Now choose Destination connection à GO To Connection Properties à select New Connection à Click New Button for establish to coneection to databaseà after selection of database -à Press OK Button to establish the connection

Bulk_Insert_Task_Properties

a)      Now select Destination Table  and press ok button .You have completed all process to move data from flat file to sql server . Press F5 to execute .

Bulk_Insert_Task_Properties1

Execute SQL Task

April 27th, 2010 by admin | 1 Comment | Filed in MS BI-SSIS

SSIS Tasks :-

Execute SQL Task:

  1. 1. Execute SQL Task: – Execute sql task execute a stored procedure or sql query.

Process to create a package:-

a)      Open Business intelligence Development studio (BIDS) à select Fileà New Project àchoose Integration service Project

BIDS

a)      Put the project name and press ok button .The Project will be created and screen will be look like this and you will find default package.dtsx package file in solution explorer.Rename the package name whatever you would like to change .

Execute_SQL_task

a)      Drag and drop Execute Sql Task control to perform some operation into database and double click on Execute SQL Task  .The following screen will be appear :-

Execute_SQL_task.1

d)Select Connection Type , Click connection to establish connection with database.

Execute_SQL_task.4

e)      Enter the sql Query or procedure in SQLStatement Properties and click OK button.

Execute_SQL_task.5

e)      You have done all process ,Now your package is ready to execute . Press F5 to run the package .When you will press the F5 , the Package will be execute and data will be inserted into database and screen will be look like this .

Execute_SQL_task.6

Tags: , ,

The SSIS Package Designer

April 27th, 2010 by admin | No Comments | Filed in MS BI-SSIS

The SSIS Package designer contain design pane where we create or design our DTS package .The SSIS package designer tool provide a way to create a workflow which represent how the data flow .The Designer include four  tabs

1. Control folw

2.  Data Flow

3. Event Handlers

4. Package Explorer

1

Control Flow :- It contain workflow parts of the package .Control flow give a pictorial representation how the data flow from heterogeneous source. Control flow also include Task and constraints (Success , failure)

Control Flow include :-

  1. Tasks: – Task basically unit of work.
  2. Constraints :-Evaluate success , failure and completion

Data Flow :- It contain the data flow task ,it define how the data flow from source to destination .Data Flow task includes Data Flow Source, Data Flow Transformation, Data Flow Destination .

  1. Data Flow Source :-The Data Flow Source is the source from where you want to transfer the data .such as DataReader source,Excel Source,OLEDB Source etc .
  2. Data Flow Transformation :-Data Flow transformation provide a way to cleaning the data .Transformation can perform task such as updating ,cleaning , mearging and distributing data.Such as Fuzzy Lookup,Data conversion,Conditional split etc .
  3. Data Flow Destination :- A destination is component that store or move the data to a specific data stores .such as OLE DB Destination etc

3. Event Handlers :- The Event handlers allow you to handle errors or changes in events .

4. Package Explorer: – The Package explorer tab all the package designiner view to a single view . Package Explorer show the connection manager which you have already configured during in data flow or control flow .

Executing Package :- The final step of SSIS package is executing a package by clicking F5.

Tags: , , , ,

SSIS

April 9th, 2010 by admin | 1 Comment | Filed in MS BI-SSIS

SSIS: – SSIS is most important part of Sql Server 2005 . SSIS is the way to transfer the data from one source to another source .SSIS is robust way to load data or transfer the data from different source and perform tasks in a workflow.

Architecture of SSIS

The SSIS Architecture consists of four main components

  1. The SSIS Services
  2. The SSIS runtime engine and the runtime executables
  3. The SSIS data Flow engine and the data flow components
  4. The SSIS clients

SSIS Package:

When we are creating SSIS package .You will get following object in Solution Explorer. The solution explorer window consist SSIS package, connection or Data Sources and data source views. The Screen is given below

SSIS1

  1. Data Source :- Data source basically is connection that can be used for source or destination to transfer the data . Process to create Data Source
  2. Data Source Views (DSV):- Data Source views allow you to create a logical views of your data. Basically Data source views are collection of tables , views , stored procedure and queries that are shared across our projects .DSVs are deployed as a connection manager.
  3. SSIS Packages :-A Package is a collection of tasks that execute in orderly. SSIS Packages basically provide the user interface to transfer the data from one source to another source They can be used to merge data from heterogeneous source or transfer data from heterogeneous data source .The primary use for SSIS package  is flexible tool for data extraction , transformation and loading (ETL).This is an important part of data warehousing .

The tool also used to update multidimensional cube data

Tags: , , , , ,