SQL Server

   What are different types of  SQL Commands

• There are four types of SQL Commands listed below
• Data Definition Language:These commands allows us to create and modify the structure of database objects.Examples of DDL are Create,Alter,Drop and Rename.
• Data Manipulation Command:These commands allows us to store,modify,retrieve and delete the data in database.Examples of DML Commands are Select,Insert,Update and delete.
• Transaction control language:These commands are used for managing changes affecting the data.Examples of TCL are Commit,Rollback and Savepoint.
• Data control language:These commands are used for providing security to database objects.Examples of  DCL are Grant and Revoke.

  What is Database Transaction

• A Transaction is a group of commands which changes the state of database.It make sure that all  of the commands get succeeded or none of them Succeeded. If any one of the command in the transaction gets failed then all the commands get failed and any data which was modified in the database is Roll backed.

   What is Normalization and its types

• Normalization:It is the process of organising data in database means itis used for elimination data redundancy(duplication) from database.
• There are different types of Normalization listed below

1. First Normal Form:Elimination of duplicate columns from the same table(No repeating groups)

1. Second Normal Form:It should meet the requirements of First Normal form.
   Need to remove columns from the table which creates duplicate,move the columns to a separate table and relate both the tables by Primary-Foreign key Relation.

1. Third Normal Form:
   It should meet the requirements of First and Second Normal Form.
   Need to remove columns which are not dependant on Primary key.

   What is a Primary Key

• .A Primary key is a field where we can uniquely identify a record.
• It does not contains any NULL Values and a table can have only one Primary Key.
  
  Example:
  CREATE TABLE TBLPRIMARY
  (
  SNO  INT NOT NULL PRIMARY KEY,
  NAME NVARCHAR(250)
  )

   What is a Foreign Key

• A Foreign key is a field which is used to establish a link between Two tables.
• A foreign key in one table is mapped to Primary key in another table.
  
  Example:
  CREATE TABLE TBLPRIMARY
  (
  EMPID  INT,
  FOREIGN KEY(SNO) REFERENCE TBLPRIMARY(SNO)
  )

  What is a Composite Key

• It is a Combination of two or more columns in a table where we can uniquely identify a record.
• If we create a primary key on two or more columns then it is said to be a Composite Key.
  
  Example:
  CREATE TABLE TBLCOMPOSITE
  (
  EMPID    INT,
  EMPNAME   NVARCHAR(250),
  EMPLASTNAME    NVARCHAR(250),
  CONSTRAINT  CNS_PRIMARY PRIMARY KEY(EMPID,EMPNAME)
  )

   What is a unique Key

• It is a combination of two or more fields where we can uniquely identify a record.
• It accepts only one NULL Value and it cannot have any duplicate records.

     Define Join and Explain different types of Joins

• Join:It allows us to join two or more tables based on the common field between them is Join.
• Different types of Joins are listed below

      . Inner Join: It is used to display rows only if there is a matching condition between both
          the  tables is Inner Join.

        Outer Join:If is used to return all the rows from both the tables if the joining condition
         is  matched or not is Outer Join.

      Note:Outer join consists of three types

        Left Outer Join:It returns all the rows from the left table and matching rows from the
         Right table unmatched rows from the Right table will returns NULL.

        Right Outer Join:It returns all the rows from the Right table and matching rows from the 

        left table,Unmatched rows from the left table will returns NULL.

        Full outer join:It returns ll the rows from both the tables if the join condition is matched or 

        not  is Full Outer Join.

     What is a Cartesian Join in SQL Server

• Cartesian Join:When each row in a first table is mapped with each row in a second table is called as Cartesian or Cross Join.

    What is a View and different types of View

• View:A View is a virtual table which is created on the result set of an SQL Statement.
• A View can contains Rows and columns just like a Real table.
• Creation of View is shown below
  Example:
  CREATE VIEW VIEWNAME AS SELECT * FROM TABLENAME
• There are two different types of Views

1. User defined View   2.System defined Views

         System defined Views: These are predefined views which already exists in
         Master database.It consists three different views.

         Information Schema View:In a SQL  Server database it consists of 20 different views 

         which  are used to get the physical information of database like Tables,Columns and 

         Views..

         Catalog View:These views are used to display the database Self describing 

         information.These Views gives the total information of what are the database objects 

         that are  available in  database like Views,Tables.

         Dynamic Management View:These views gives  the information to the Administrator

         about  the current state of SQL server Machine.These values helps the administrator to 

         diagnose  the  problem and tune the server for Optimal performance.

        User defined Views:It mainly consists of two types

       .Simple View:If we create a view on Single table then it is called as Simple View.

        Complex View:If we crate a view on more than one table then it is said to be a Complex 

        View,

What is Grant and Revoke commands

• Grant:It is a command which allows us to provide an access or privileges on database objects to the user.
• Revoke:It is a command which removes user access rights to the database objects.

   What is a Stored procedure and Advantages

• A Store Procedure can be defined as set of logical group of SQL statements which are grouped together to perform a specific task is a Store procedure.
• A store procedure can take input,process them and sends back the output.

     Advantages: Stored procedures are precompiled and stores in database which makes database

     to execute faster.

    2.As many queries are included in Stored procedure Round trip to server to execute 

       multiple  queries is avoided.

What are Magic tables

• Inserted and deleted are called as Magic tables which are used in Triggers and are automatically created and managed by the SQL server internally to hold a recently Inserted,Updated and deleted values during DML Operations on a table.

What is a Trigger and types

• A Trigger gets executed automatically based on the action performed on the table like Insertion,deletion or Updation of the data.
• Triggers are automatically invoked we cannot explicitly invoke the Trigger.
• Triggers are classified into Two types
• After Trigger:These Triggers gets executed after we perform an action on a table like Insertion,deletion or updation.

• Instead of Trigger:If we want to perform an logic present inside the trigger instead of inserting data in the data then we make use of Instead of Trigger.

• Triggers are not supported for Views.
• After Triggers can be classified into Three types
• After Insert Trigger:These triggers are automatically invoked after performing an Insert command on a table.

• After delete Trigger:These triggers are automatically invoked after performing an delete command on a table.

• After Update Trigger:These triggers are automatically invoked after we perform an Update command on a table.

• Instead of Triggers can also be classified into three types
• Instead of Insert:If we want to perform an logic present inside the trigger instead of inserting the data in the table then we make use of Instead of Insert Trigger.

• Instead of delete:If we want to perform a logic present inside the trigger instead of deleting the data in the table then we make use of Instead of delete trigger.

• Instead of update:If we want to perform a logic present inside the trigger instead of updating the data in the table then we make use of instead of update trigger.

      Example for Trigger:

• CREATE TRIGGER TRIGGERNAME ON TABLENAME
  FOR INSERT
  AS
  BEGIN
         DECLARE @ID INT;       SELECT @ID=I.EMPID FROM INSERTED I;
         INSERT INTO TABLENAME1 VAKUES(@ID);
  END

   Difference between Delete and Truncate Commands

• Delete:

• It is a DML Command
• We can specify filters in Where clause.
• It deletes the specific data.
• It fires an Triggers
• Delete is slower when compare with truncate because it removes data on row by row basis and records an entry in Transaction logs.
• If we perform delete operation then the Identity column will not reset as it affects the rows but not on the data pages.

• Truncate:

• It is a DDL Command.
• We cannot make use of Where clause.
• It deletes all the data which exists in the table.
• It will not fire trigger.
• If we perform truncate on Identity column then it gets reset as it affects on data pages but not on the rows.

What is difference between Clustered and Non clustered index

Clustered Index	Non clustered Index
It is unique for a given table and we can have only one clustered index on a table.	We can create as many as clustered index as possible
Clustered index isautomatically created when we create a Primary key if no clustered index exists on a table	Non clustered index is automatically created when we create a unique key.
clustered iindex does not requires a separate storage for index than the tahle storage	Non clustered index requires a separate storage for a index information than the table storage
Clustered index is faster when compare with non clustered index	Non clustered index is slower because it refer back to table data as index information is stored separately with table data.

What is Union,Except and Intersect Commands

• Union:
• It is used to combine the result set of two or more select statements and gets the distinct values.
• In both the select statements datatype should be compatible to each other and both the select statements should have same number of columns.

• Except:
• It is used to return rows from the first select statement but not from the second select statement.

• Intersect:
• It is used to return common records from both the table.

What is difference between primary key and  Unique key

Primary Key	Unique Key
It does not allow NULL values	It allows only one NULL Value
By default it adds a Clustered index when we create a primary key	By default it adds a Non clustered index when we create a  Unique key
A Table can have only one primary key	A Table can have more than one Unique key

What is difference between Having and Where clause

Where Clause	Having Clause
It can be applied on Non Aggregate columns	it can be applied on  Aggregate columns
It filters the data before grouping has been performed	It filters the data after grouping has been performed.

What is difference between Local and Global temporary tables.

• Temporary tables are very similar to permanent tables.Permanent tables are created in a specific database and exists until database exists where as temporary table is created in a TempDB and are automatically deleted when temporary tables are no longer in use.
• There are two types of Temporary tables
• A.Local Temporary table   B.Global Temporary table

Local Temporary Table	Global Temporary Table
It is created with single # as prefix	It is created with double ## as prefix
It is visible to connection which creates it and are automatically deleted when connection is closed	These are visible to all the connections and is automatcally deleted when the last connection referring to the table is closed
If temporary table is created with in the stored procedure then table is automatically dropped once after the execution of Stored procedure is completed

What is an Index and its types

• Indexes: Indexes are database object which are created on one or more columns.
• Indexes can be created on columns upto 16(columns max)
• It allows us to improve the performance of  data retrieval and fetches the records from the database quickly.

• Different types of Indexes are Clustered and Non clustered index.
• Clustered Index:
• It determines the physical order of data in a table.
• Clustered indexes are very similar to telephone directory as all the data is arranged in Alphabetical order.
• Primary key constrain automatically creates a clustered index if no clustered index exists on a table.

• Non Clustered index:
• Non Clustered index is very similar to textbook as the index is stored at different location and table data is stored at different location.
• Unique key constraint will automatically creates a Non clustered index and we can create as many as Non clustered indexes on a table.

What is a Subquery and different types of Subquery

• A Subquery is a query within another query.The outer query is called as Main query and inner query is called as Sub query.
• Subquery is always executed first and the result of sub query is passed to the main query.
• Different types of query are  A.Correlated Subquery  B.Non Correlated Subquery
• Correlated Subquery: It depends upon the Outer query and it does not executes by itself.

• Non Correlated Subquery: Both outer and inner query are independent to each other.

What are local and Global variables

• Local Variables:These variables are declared with in the store procedure or body of a batch and can assign a value either by SET or SELECT statements.
• Local variables starts with single  as prefix and we creates it explicitly.

• Global Variables:These are special type of variables which already exists in the database and server always maintain the value for these variables.
• Global variables starts with prefix @@ and we cannot create it explicitly.

What are constraints and different types of Constraints

• SQL Constraints are used to specify the rules for data in a table.
• If there is any violation between the constraint  and the data then the action is aborted by the constraint.
• There are Six different types of constraints listed below
• Not NULL: It indicates that a column cannot store Null value.
• Unique Constraint: It make sure that each row for a column must have a unique value.
• Primary key:A Primary key is a field where we can uniquely identify a record. and it does not allows Null values.
• Foreign Key: It is used to establish a link between two tables and foreign key in one table is mapped with the primary key in another table.
• Check:It is used to check the value entered in a table against the condition specified for a column with in the table.If the condition is not matched then it aborts the actions.
• Default:It allows us to specify a default value when value is not specified for a column.

What is User defined functions

• User defined functions accepts the input parameters,performs the action and returns the result.
• We cannot perform any DML Operations like Insert,Update and delete.
• Different types of functions are
• System defined functions:It consists of two types A.Scalar function  B.Aggregate Function
• User defined functions:It also consists of two types A.Inline table valued function B. Multistatement table valued function.

What are  Aggregate and Scalar functions

• Scalar Function:It operates on a Single value and returns a Single value.
• Example:UPPER,LOWER,LTRIM and RTRIM
• Aggregate Function:It operates on  a collection of values and returns a Single value.
• Example:MAX,MIN,COUNT

What are User defined functions in SQL

• Inline Tabled value function:It returns a table as a result of action performed on the function.
• The value of a table must be derived from Select statement.
• MultiStatement tabled value function: It returns a table variable as a result of action performed by the function.
• In this Table variable must be explicitly declared and defined whose values can be deived from Multiple SQL Statements.

What is difference between Inner Join and Outer Join

• Inner Join:It returns records from both the tables if the condition specified in the Where clause matches.
• Left Outer Join:.If returns all the rows from the left table and matching records from the right table.Unmatched records will returns Null
• Right Outer Join:.If returns all the rows from the Right table and matching records from the left table.Unmatched records will returns Null.

What is difference between Join and Union

Join	Union
It returns the result set from two or more tables based on the logical relationship between two tables.	It combines result of two or more Select statements into a Single result set
It can fetch the duplicate records if a table contains duplicate records.	It does not fetch duplicate records.

What are TOP,RANK,DENSE RANK  and NTILE functions.

• TOP: It is used to retrieve records from one or more tables and limit the Number of records based on a fixed value.
• RANK: It returns RANK for each and every row in a Result set.Rank function skips the sequence if there is a tie.
• DENSE RANK: It is also same as Rank but the difference is that Dense_Rank will not skips the Sequence if there is a tie.
• NTILE: It is used to distribute the rows into specified number of groups.

What is COALESCE function in SQL Server

• It returns the first Not Null value from the expression in the list.

What is difference between Union VS Union ALL

UNION	UNION ALL
UNION removes the duplicate from the select statement	UNION ALL does not removes the duplicate rows from the select statement.
Performance is slow because it performs DISTINCT to remove duplicate rows.	Performance is fast.

What are Left,Right,CharIndex and Substringfunctions

• Left: It starts from the LEFT most character of the string and moves to RIGHT.
• Right: It starts from RIGHT most character of the string and moves to LEFT.
• Charindex: It accepts two Arguments.First arguement is the character we are searching for and second is the string.
• Substring: It accepts three arguments.First parameter is the string,Second parameter is the starting position of the string and third parameter is the End position of the string.

What are Replicate,Space,Patindex and Stuff Function

• Replicate: It is used to repeat a string with specified number of times.
• Space: It returns a string with specified number of spaces.
• PatIndex: It returns the location of pattern of a string.Search is not a case sensitive.
• Stuff: It deletes the sequence of character from a source string and inserts another sequence of character to the sequence string.

Difference between Stored Procedure and Function

Stored Procedure	Function
It contains input and Output parameters	It contains only input parameters.
We can perform DML Operations like Insert,Update and delete.	We cannot perform any DML operations like Insert,Update and Delete
It supports an exception handling	It cannot supports Exception handling
We can call functions with in the Stored procedures	We cannot call Stored procedures with in the functions.

What are Pivot and Unpivot operator in SQL Server

• Pivot: It is used to turn an unique values from one column into multiple columns thereby rotating a Table.Basically it is used to turn Rows into Columns.
• UnPivot: It is used to turn columns into Rows is Unpivot

What is a SQL Profiler

• SQL Profiler is a tool which helps us to capture the activities like execution of SQL Statements and we can save all the activities in a trace file which is useful for Tuning advisor.
• When we select the trace file and run it with the help of tuning advisor then it will give us how much estimation improvement can be achieved and it also gives us the recommendation to create an indexes on a table.

Difference between Scope identity and @@Identity and Ideal_Current

• To get the last generated identity columns we make use of Scope identity and @@Identity and Ideal_Current.
• Scope_Identity: It returns the last generated identity value with in the same scope and a same session.
• @@Identity: It returns the last generated identity value with in the same session and across any scope.
• Ideal_Current: It returns the last generated identity value across any session and across any scope.

What is Merge in SQL Server

• Merge:It allows us to perform Insert,Update and delete in a single statement instead of performing with multiple statements.
• It requires two tables
• Source Table: It contains the changes that need to be applied to the Target table.
• Target Table: The table that requires changes

How many system databases are available in SQL Server

• There are four types of System databases which are listed below
• Master Database: It is a System database which contains the information of Server's Configuration.
• MSDB: It is used by SQL Server agents for Scheduling Jobs and Alerts.
• TempDB: It stores the temporary database objects like Temporary tables.
• Model DB: It is a Template database which is used in the creation of New database.

What is difference between Temp table and Table Variable

Temp Table	Table Variable
Temp Table is available in Temp Database	It is available with in the memory
We can perform DDL Operations	We cannot perform any DDL operations
We can create multiple tables with same name with in the Stored procedure	We cannot create Multiple tables with same name
We cannot use it with in the function	We can use it with in the function
We can create indexes on a Temp table	We cannot create indexes on a table Variable

What is Integrated security in Connection string

• If integrated security="false" then we need to provide User name and Password in the Connection string.
  Example:Data Source=localhost;user id=username;password=pwd;integrated security="false"
• If integrated security="true" then current window credentials are used for Authentication.
• No need to provide Username and Password in the Connection string.

What are different isolation levels in SQL Server

• Read Uncommitted:It allows us to read the data that has been currently being modified in another transaction.But it has not yet committed the data.
• Example:Let say we have to transactions T1 and T1.Transaction T1 has updated the data but not yet committed the data.Where as transaction T2 will not wait for transaction T1 to complete but transaction T2 will return the data.If transaction T1 fails and rollback the modified data then the data present in the Transaction T2 will not be available in database which is a dirty read.
• Read Committed:It will return the committed data of a table.
• Example:Let say we have two transactions T1 and T2.Let say Transaction T1 will perform update operation on a table then other transaction T2 will wait until transaction T1 gets completed and committed the data.