SQL

1) What is SQL ? 
A) SQL stands for Structured Query Language. SQL is used to communicate with a database.  it is the standard language for relational database management systems. SQL statements are used to perform tasks such as update data on a database, or retrieve data from a database.
Example  relational database management systems that use SQL are:

• Oracle
• Sybase
• Microsoft SQL Serve
• Access

2)What is OLTP and OLAP?
Online transaction processing and online analytical process
Oltp is transactional
Olap is an analatic

Oltp provides source data to datawarehouse.
Olap helps to analyse it

Oltp is characterized by large no. of short online transactions(insert,update,delete)
Olap is charecterised by relatively low volumes of transations

In oltp fast query processing
In olap queries are complex and involved aggregation

In oltp it maintained data integrity in multi access environments.

In oltp effectiveness is measured by transactions per second
In olap response time is an effectiveness measure

Detailed and current data, schema used to store transactional DB
Aggregated, historical data,

3)What is RDBMS? 
Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables.
An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.
Relational tables have the following five properties:
Values are atomic.
Column values are of the same kind.
The sequence of columns is insignificant.
The sequence of rows is insignificant.
Each column must have a unique name

4) What is normalization? 
In relational database design, the process of organizing data to minimize redundancy is called normalization.
Normalization usually involves dividing database data into different tables and defining relationships between the tables. Database normalization is a data design and organizational process applied to data structures based on rules that help build relational databases.
The key traits for Normalization are eliminating redundant data and ensuring data dependencies.

5) What are the different normalization forms? 
1NF: Eliminate repeating groups
2NF: Eliminate redundant data
3NF: Eliminate columns not dependent on the Pkey

6) What is de-normalization?
De-normalization is the process of attempting to optimize the performance of a database by adding redundant data.
De-normalizing the database design allows for fewer joins with tables and foreign key requirements.
This method is commonly used for Reporting and OLAP workloads

7) SQL Commands:
SQL commands are instructions used to communicate with the database to perform specific task that work with data. SQL commands can be used not only for searching the database but also to perform various other functions like, for example, you can create tables, add data to tables, or modify data, drop the table, set permissions for users. SQL commands are grouped into four major categories depending on their

functionality:
Data Definition Language (DDL) - These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.
Data Manipulation Language (DML) - These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
Transaction Control Language (TCL) - These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
Data Control Language (DCL) - These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.

The CREATE TABLE Statement is used to create tables to store data.
CREATE TABLE table_name
(column_name1 datatype,
column_name2 datatype,
… column_nameN datatype
);

The INSERT Statement is used to add new rows of data to a table.
INSERT INTO TABLE_NAME
[ (col1, col2, col3,...colN)]
VALUES (value1, value2, value3,…valueN);

The UPDATE Statement is used to modify the existing rows in a table.
UPDATE table_name
SET column_name1 = value1,
column_name2 = value2, ..
[WHERE condition]

NOTE:In the Update statement, WHERE clause identifies the rows that get affected. If you do not include the WHERE clause, column values for all the rows get affected.

The DELETE Statement is used to delete rows from a table.
DELETE FROM table_name [WHERE condition];

TRUNCATE statement: This command is used to delete all the rows from the table and free the space containing the table.

8) Difference between delete and truncate?
Data deleted with truncate command cannot be roll back once the transaction is committed. While data deletion with delete command can be roll back even if the transaction is committed.
Delete Performs row by row with given condition while truncate command deletes the complete data in a table but the structure of the table remains.
TRUNCATE is faster and uses fewer system and transaction log resources while transaction logs are maintained by delete which slows the operation .
DELETE does not reset the identity column of the table while TRUNCATE resets the identity field of the table.

NOTE:: If you want to remove table definition and its data, use the DROP TABLE statement.

9) Difference between DROP and TRUNCATE Statement:
If a table is dropped, all the relationships with other tables will no longer be valid, the integrity constraints will be dropped, grant or access privileges on the table will also be dropped, if we want use the table again it has to be recreated with the integrity constraints, access privileges and the relationships with other tables should be established again. But, if a table is truncated, the table structure remains the same , therefore relations created on columns between the tables will exist.

10) What are primary keys and foreign keys?
A PRIMARY KEY constraint is a unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row and only one primary key constraint can be created for each table and PK cannot be null. The primary key constraints are used to enforce entity integrity.
NOTE:: It is not possible to change the length of a column defined with a PRIMARY KEY constraint. If you need to change the length then you must first delete the existing PRIMARY KEY constraint and then re-create it with the new definition.
A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

11) What is a UNIQUE KEY Constraint?
A UNIQUE constraint enforces the uniqueness of the values in a set of columns; so no duplicate values are entered. The unique key constraints are used to enforce entity integrity as the primary key constraints.

12) What’s the difference between a primary key and a unique key?
Both primary key and unique key enforces uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a non-clustered index by default. Another major difference is that, primary key doesn’t allow NULLs, but unique key allows one NULL only
NOTE:: Primary key is also a unique key internally, but cannot allow NULLs. Unique keys on the other hand allow a single NULL but not multiple NULLs over the columns.

13) What is a CHECK constraint?
A CHECK constraint is used to limit the values that can be placed in a column. CHECK constraints are most often used to enforce domain integrity. (Read more here http://bit.ly/sqlinterview17)

14) What is a NOT NULL constraint?
A not null constraint enforces that the column will not accept null values. Not null constraints are used to enforce domain integrity.

Define candidate key, alternate key, composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

15) What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A start and increment value can be set, but most DBA leave these at 1. A GUID column also generates numbers; the value of this cannot be controlled. Identity/GUID columns do not need to be indexed.
Note:: TRUNCATE TABLE resets the IDENTITY column to its base value. The DELETE command doesn’t do this.

16) What are the different types of data types in SQL Server?
There are different types of data types used in SQL Server
Int
Small int
Big int
Tiny int
Float
Double
Decimal
Money
Char
Varchar
Nvarchar
Nchar
Varbinary
Varbinary max
Small date time
Date time
Ntext
Numeric
Sql variant
Blob.
Glob
Unique identifier
Nvarchar max

17) SQL Operators
There are two type of Operators, namely Comparison Operators and Logical Operators. These operators are used mainly in the WHERE clause, HAVING clause to filter the data to be selected.
Comparison operators are used to compare the column data with specific values in a condition. ‘=, != or , , =’

18) Logical operators:
There are three Logical Operators namely AND, OR and NOT. These operators compare two conditions at a time to determine whether a row can be selected for the output. When retrieving data using a SELECT statement, you can use logical operators in the WHERE clause, which allows you to combine more than one condition.
OR operator:: If you want to select rows that satisfy at least one of the given conditions, you can use the logical operator, OR.
For example: if you want to find the names of students who are studying either Maths or Science, the query would be like,
SELECTfirst_name,last_name,subject FROMstudent_details
WHERE subject = ‘Maths’ OR subject = ‘Science’ AND operator:: If you want to select rows that must satisfy all the given conditions, you can use the logical operator, AND.
For Example: To find the names of the students between the age 10 to 15 years, the query would be like:
SELECTfirst_name,last_name,age FROMstudent_details
WHERE age >= 10 AND age =10ANDage 25000

19) Difference between where clause and having clause?
Aggregated functions cannot be used in WHERE Clause but can be used in HAVING clause.
Where clause can be used in select/update/delete clause while having can be used only in select clause.
Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

20) What is Group by function?
The SQL GROUP BY Clause is used along with the group functions to retrieve data grouped according to one or more columns
The group by clause should contain all the columns in the select list expect those used along with the aggregated functions.

21) What is order by function?
The ORDER BY clause is used in a SELECT statement to sort results either in ascending or descending order.
NOTE: The columns specified in ORDER BY clause should be one of the columns selected in the SELECT column list.

22) What is a join and explain different types of joins?
In order to join two or more tables we use Joins.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

23) Inner Join
A join that displays only the rows that have a match in both joined tables is known as inner Join.
This is the default type of join in the Query and View Designer.

24) Outer Join
A join that includes rows even if they do not have related rows in the joined table is an Outer Join.  You can further create three different outer join to specify the unmatched rows to be included:
Left Outer Join: It pulls all the records from left table which appears left most in the JOIN clause and substitute null vales in the right table for no matched records.
Right Outer Join It pulls all the records from right table which appears rightmost in the JOIN clause and substitute null vales in the left table for no matched records
Full Outer Join: In Full Outer Join all rows in all joined tables are included, whether they are matched or not.

25) Cross join
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table

26) Self Join
This is a particular case when one table joins to itself, with one or two aliases to avoid confusion. A self join can be of any type, as long as the joined tables are the same.The common example is when company has a hierarchal reporting structure whereby one member of staff reports to another. Self Join can be Outer Join or Inner Join. (Read More Here)

27) What is Sub-query?
Subquery or Inner query or Nested query is a SELECT statement that is nested within another T-SQL statement.
A subquery SELECT statement can return any number of values, and can be found in, the column list of a SELECT statement, a FROM, GROUP BY, HAVING, and/or ORDER BY clauses of a T-SQL statement
A query is called correlated subquery when both the inner query and the outer query are interdependent.
SELECT p.product_name FROM product p
WHERE p.product_id = (SELECT o.product_id FROM order_items o
WHERE o.product_id = p.product_id);

28) Properties of sub-query:
•A sub-query must be enclosed in the parenthesis.
•A sub-query must be put in the right hand of the comparison operator, and
•A sub-query cannot contain an ORDER-BY clause.
•A query can contain more than one sub-query.
A Subquery can also be used as a parameter to a function call

29) Difference between join and subquery?
A join will join two or more tables together by a field related to both tables (ie, relationship of primary and foreign keys). It is typically easy to understand.

A sub-query statement involves a SELECT statement that selects particular values from a table. The values that the select query selects is dependent upon the sub-query. The sub-query itself is another SELECT statement.

30) What is Union and Union all? What is difference between them?
UNION If you need to combine two or more result set into single result set we use union . The no.of columns and their datatypes also should match
If you have any duplicate data in your result set those records will be ignored with UNION while union all will not eliminate the duplicate rows from the result set.

Difference between join and Union?
31) What is Variable?
variable is an object that can hold a single data value of a specific type.
When a variable is first declared, its value is set to NULL. To assign a value to a variable, use the SET statement.
A variable can also have a value assigned by being referenced in a select list.

Syntax:
Declare @variable_name datatype
Set variable_name = value
EX: Declare @st_id int
Set @st_id = 5
Select *
From student where st_id=@st_id

32) What are local variables and global variable?
A local variable must be declared (using DECLARE or as a formal parameter) before use. A local variable can be set using either the SET command or the SELECT command.

Global variables are system-supplied, predefined variables. They are distinguished from local variables by the two @ signs preceding their names–for example, @@error. The two @ signs are considered part of the identifier used to define the global variable.

Users cannot create global variables and cannot update the value of global variables directly in a select statement. If a user declares a local variable that has the same name as a global variable, that variable is treated as a local variable.

The difference is global variable is a system defined or predefined variable while local variable is a user defined and must be declared before using.Local variable declared with a single @ preceding their name while global variable by the two @ signs preceding their names.

Some of the global variable are:: @@error, @@rowcount

33) What are string functions?
These are system defined functions.
Ltrim – If we need to extract the first four characters from a given column we use ltrim
EX : select customer_name,LEFT(customer_name,4) from customer

Rtrim – If we need to extract the last four characters from a given column we use rtrim
EX: select customer_name,right(customer_name,4) from customer

Length – It will count the function of given string.
Ex: select customer_name,len(customer_name) from customer

Datalength – It will count the function of given string includes empty spaces.
Ex: select customer_name,datalength(customer_name),len(customer_name) from customer

34) Difference between length and data length?
Length function will not count the empty space and control keys like f1,f2 while data length counts.
Ex: select customer_name,datalength(customer_name),len(customer_name) from customer
where datalength(customer_name) len(customer_name)

Sub-string: To extract a string from a sub-string we use sub-string.
Ex: select customer_name,SUBSTRING(customer_name,5,4) from customer

Char-index : It is going to return index value of first apperence of character.
Ex: select CHARINDEX(‘-’,'raja-testing’)

35) What are user defined function?
User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

Scalar value function: If any function returns a single value as a result set.
In scalar we pass parameters an inputs.
It is used to call in select clause.
create function fun_name (@a int,@b int,@op char)
returns int
as begin
declare @output int
select stmnt
end
Return @output
end

Tabular value function: If any function returns result set as a table.
It is used to call in from clause.
create function tab_months()
returns table
return
select stmnts
)

The diff b/w scalar valued and tabular valued function is in scalar we return single value as a result set where as in tabular valued functions it returns a table as a result set and scalar we pass parameters an inputs and scalar used to call in select clause while tabular used to call in from clause.

36) What is Difference between Function and Stored Procedure?
UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be. UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables. Inline UDF’s can be thought of as views that take parameters and can be used in JOINs and other Rowset operations.

37) What is Views?
A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. The results of using a view are not permanently stored in the database. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table.

38) What is a stored procedure?
A stored procedure (SP) is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. Stored procedures reduce network traffic and improve performance.
reuse and improved security from SQL injection are some of the advantages of using SPs

39) What is a Trigger?
A trigger is a SQL procedure that initiates an action when an event (like INSERT, DELETE or UPDATE) occurs on an object.
Triggers are stored in and managed by the DBMS.
Triggers can be used to maintain the referential integrity of data by changing the data in a systematic way.
A trigger cannot be called or executed directly;
DBMS automatically fires the trigger as a result of a data modification to the associated table.
A trigger is called a nested trigger when it is fired off from another trigger.
Triggers are similar to stored procedures in that both consist of procedural logic that is stored at the database level.

40) Difference between stored procedures and triggers?
Stored procedures, however, are not event-driven and are not attached to a specific table as most triggers are.
Stored procedures are explicitly executed by invoking a call to the procedure while triggers are implicitly executed by events.
In addition, triggers can also execute stored procedures.

41) What are the different types of triggers?
There are three types of triggers.
1) DML trigger
There are two kinds of DML triggers
a. Instead of Trigger
Instead of Triggers are fired in place of the triggering action such as an insert, update, or delete.
b. After Trigger
After triggers execute following the triggering action, such as an insert, update, or delete.

2) DDL trigger
This type of trigger is fired against DDL statements like Drop Table, Create Table, or Alter Table. DDL Triggers are always after Triggers.

3) Logon trigger
This type of trigger is fired against a LOGON event before a user session is established to the SQL Server.

42) What is a linked server?
A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. With a linked server, you can create very clean, easy–to-follow SQL statements that allow remote data to be retrieved, joined, and combined with local data. The ability to issue distributed queries and perform commands with transactions on heterogeneous sources is one of the benefits of using linked servers.
The system supplied stored procedures sp_addlinkedserver and sp_addlinkedsrvlogin are used to add new linked server(s). The stored procedure sp_linkedservers is used to list all the linked servers defined on the server.

43) What is a cursor?
Cursors are used to hold the data temporarily for processing for some logic. It stores the data from table and process one by one each record

A cursor is a database object used by applications in the procedural logic to manipulate data in a row-by-row basis, instead of the typical SQL commands that operate on all or parts of rows as sets of data.

Cursor is required when there is any operation required on data by traversing one by one.

In order to work with a cursor, we need to perform these steps in the following order:
Declare a cursor
Open the cursor

Fetch a row from the cursor
Process the fetched row
Close cursor
Deallocate the cursor

44) What is Index?
An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries.

What are the difference between clustered and a non-clustered index? (Read More Here)
A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

45) How to Enable/Disable Indexes?
–DisableIndex
ALTER INDEX [IndexName] ON TableName DISABLE GO
–EnableIndex
ALTER INDEX [IndexName] ON TableNameREBUILDGO
(Read more here)

46) Data model:
It is a type of data abstraction .It is a set of concepts that can be used to describe the structure of a database.

47) What is isolation level in sql server?
Isolation Levels are used to handle transations in sql server i.e to implement concurrency across the transactions.
-Read committed
-Phantom reads
-Dirty read

What is default isolation for sql server
Read committed

@@error?
Stores error details used in stored procedures

@@Raise error?
To print error messege.

@@Row count?
To check how many no. of rows updated in the last transaction.

32 levels of nesting can be done for SPs,functions,triggers.

How many columns can inserted in table?
4096
But at a time only 1000rows can be inserted

48) Table sample in sql server?
Is used for getting the sample of rows from a result set. The sample is in the form of percentage.
Syntax::
Select table sample(%) from table_name.

49) What is CTE?
It is a virtual view which exists at run time. it is introduced in sql server 2008.
Is is mainly used for handing for hierarchal and recursive data.

50) What is difference between CTE and View?
CTE is a not a physical object and View is a physical object.
Write a query to get the no rows of a table without using count or any other clause.
There is the view sys.partitions is the view which contains the information about the rows in the table. By providing the object id of table you can get rows for table.

51) What are dynamic queries?
Dynamic Queries are the query in which logic can be change at run time. These queries are created by using the variables.

52) What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time.

53) What is B-Tree?
The database server uses a B-tree structure to organize index information. B-Tree generally has following types of index pages or nodes:
Root node: A root node contains node pointers to only one branch node.
Branch nodes: A branch node contains pointers to leaf nodes or other branch nodes, which can be two or more.
Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes, which can be many.

54) Can SQL Servers linked to other servers like Oracle?
SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group

55) What is BCP? When does it used?
Bulk Copy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user-specified format.

Explain Few of the New Features of SQL Server 2008 Management Studio
SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities.

A few of the important new features are as follows:
A)IntelliSense for Query Editing: we will not have to remember all the syntax or browse online references. IntelliSense offers a few additional features besides just completing the keyword.

b)Multi Server Query: SSMS 2008 has a feature to run a query on different servers from one query editor window. First of all, make sure that you registered all the servers under your registered server. Once they are registered, right click on server group name and click New Query.

c)Query Editor Regions: When the T-SQL code is more than hundreds of lines, after a while, it becomes more and more confusing.
The regions are defined by the following hierarchy:
From first GO command to the next GO command.

d)Object Explorer Enhancements: In Object Explorer Detail, the new feature is Object Search. Enter any object name in the object search box and the searched result will be displayed in the same window as Object Explorer Detail.
Additionally, there are new wizards which help you perform several tasks, from policy management to disk monitoring. One cool thing is that everything displayed in the object explorer details screen can be right away copied and pasted to Excel without any formatting issue

e)Activity Monitors: There are four graphs
percent; Processor Time,
Waiting Tasks,
Database I/O,
Batch Requests/Sec
All the four tabs provide very important information; however, the one which I refer most is “Recent Expensive Queries.” Whenever I find my server running slow or having any performance-related issues, my first reaction is to open this tab and see which query is running slow.