Sql server interview question and answer.

RE: sql server interview questions and answers for experienced

What is the purpose of using SET ANSI NULLS ON?
The purpose of using SET ANSI_NULLS ON to follow the ISO Standard. Acording to this = and <> should not be used for null comparison. Instead we use null and is not null how ever if you will use = null it will return zero rows. If we want to use = or <> for null comparison use SET ANSI_NULLS OFF it means do not follow ISO Standard. SET ANSI_NULLS should be set to ON for executing distributed queries for maintaining compatibility of queries across Servers.

Difference between cluster and non cluster index in sql?
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. 

Can we create non primary key as cluster index?
Yes we can do this on non-primary key column but that column must be unique and the primary key column of that table must have non-clustered index because there is one cluster index in table. By default primary key column contains clustered index so its recommended to create such non-primary key clustered index column first and then should create primary key column so in such case the primary key on that column will be with non-clustered. But its highly recommended to create primary key column as a clustered indexed column.

Can we call a Trigger in store procedure?
A Trigger is also a special kind of Stored Procedure which will fire automatically on the happening of an event like before or after insert, update or delete. We cannot call a trigger explicitly from a Stored Procedure.

Difference Between GETDATE and SYSDATETIME?
When we use GETDATE the precision is till miliseconds and in case of SYSDATETIME the precision is till nanoseconds.

Get top two records without Top keyword.
[SQL]

set rowcount 2
select Column1, Column2 from TableName

Difference between Set and Select.
-Set is a ANSI standard for variable assignment. It assigns value to only one variable at a time.
-Select is a Non-ANSI standard when assigning variables. It assigns multiple variable at a time.
-When assigning from a query that returns more than one value, SET will fail with an error.
-When assigning from a query that returns more than one value, SELECT will assign the last value returned by the query and hide the fact that the query returned more than one row.

How to get number of Maximum connection can be establish to SQL?
select @@MAX_Connections

Why we use SET ROWCOUNT in Sql?
This syntax is used in SQL Server to stop processing the query after the specified number of rows are returned.

What are the Global Temporary Tables?
We can create global temporary tables but these are not using much in sql an the name of these table start with two pound signs. For example, ##globaltbl is a global temporary table. As the name suggest these table is Global temporary tables and visible to all SQL Server connections. When we create any one of these all users can see it.

What is a View in SQL?
View is just a virtual table nothing else which is based or we can say develop with SQL SELECT query.So we can say that its a real database table (it has columns and rows just like a regular table),but one difference is that real tables store data, but views can''t. View data is generated dynamically when the view is referenced. And view can also references one or more existing database tables or other views. We can say that it is filter of database.

Why Group BY and Order By clause are so expensive?
Both Group By and Order By clause requires Temporary table to process the result of query so these are expensive.

What is the use of SET NOCOUNT ON?
When we use SELECT and DML statement in SQL. SQL server return a message which specify the number of rows effected by these statements. This information helps coder when they are debugging the code other wise this is not useful we can disable this by typing SET NOCOUNT ON. It is very helpful when we are doing on store procedure contains lots of statements, loops its also increase in performance and boost network traffic.

What are the differences between SQL and PL/SQL?
We can get modify, Retrieve by single command or statement in SQL but PL/SQL process all SQL statements one at a time. With PL/SQL, an entire block of statement process in a single command line. SQL is structured query language, various queries are used to handle the database in a simplified manner. While PL/SQL is procedural language contains various types of variables, functions and procedures and other major diffrence is Sql as the name suggest it is just structured query language wheareas PLSQL is a combination of Programming language & SQL. 

What is a join? List different types of joins
Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table. 
Types of joins:
-INNER JOIN
-OUTER JOIN (OUTER JOIN is further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS)
-CROSS JOINs

What is a Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. For example, you can use cursor to include a list of all user databases and make multiple operations against each database by passing each database name as a variable.

Difference between stored procedure and user defined function
1>Procedure can return zero or n values whereas function can return one value which is mandatory.
2>Procedures can have input, output parameters for it whereas functions can have only input parameters.
3>Procedure allow select as well as DML statement in it whereas function allow only select statement in it.
4>Functions can be called from procedure whereas procedures cannot be called from function.
5>Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
6>We can go for transaction management in procedure whereas we can''t go in function.
7>Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

Posted By: Anonymous

Posted Dt: 06-Mar-2012

188   25

Total Points: 8696

RE: sql server interview questions and answers for experienced

Below are few interview questions:

What is Extent and Page?
Extent is a basic unit of storage to provide space for tables. Every extent has number of data pages. As new records are inserted new data pages are allocated. There are eight data pages in an extent. So as soon as the eight pages are consumed it allocates new extent with data pages. 
While extent is basic unit storage from database point of view, page is a unit of allocation within extent.

What are the types of JOIN used in SQL Server?
-Inner join
- Outer Join (Left outer join and right outer join)
- Self Join
- Cross Join

What’s the difference between “UNION” and “UNION ALL” ?
UNION SQL syntax is used to select information from two tables. But it selects only distinct records from both the table, while UNION ALL selects all records from both the tables.

What is a cursor?
It is used to handle a set of data. Using cursors, you can loop through all the rows and update data accordingly.

What are the steps involved in creating a cursor?
1. Declare Cursor
2. Open Cursor
3. Fetch the data''s
4. Operation
5. Close and Deallocate

How is the SUBSTR keyword used in SQL?
SUBSTR is used for string manipulation with column name, first position and string length used as arguments. 

What is a trigger? Write its syntax.
A Trigger is also a special kind of Stored Procedure which will fire automatically on the happening of an event like before or after insert, update or delete. We cannot call a trigger explicitly from a Stored Procedure.
[SQL]

CREATE TRIGGER TriggerName
ON TableName
AFTER INSERT | UPDATE | DELETE 
AS
Query to be executed...
GO

Posted By: Anonymous

Posted Dt: 18-Mar-2012

90   1

Total Points: 8696

RE: sql server interview questions and answers for experienced

Hi All,

I have given the interview for RBS, Chennai a week ago. They asked me a question in sql server. How to fetch the second highest salary from a table without using top, rowcount and rank keywords...

Could anyone know how to write the query fro fetching the second highest salary without using top, rowcount and rank keywords?

Thanks,

Posted By: Anonymous

Posted Dt: 18-Mar-2012

158   27

Total Points: 20889

RE: sql server interview questions and answers for experienced

Hi Karthik,

Below are few sql server interview questions and answers.

What are CODD rules?
There are 12 rules that every DBMS should adhere in order to be true RDBMS. These rules were laid by E.F.CODD in 1969.
1. Information Rule
2. Guaranteed access rule
3. Systematic treatment of null values
4. Dynamic on-line catalog based on the relational model
5. Comprehensive data sub-language Rule
6. View updating Rule
7. High-level insert, update and delete
8. Physical data independence
9. Logical data independence
10.Integrity independence
11.Distribution independence
12.Non-subversion Rule

How many types of relationship exists in Database designing?
1. One to One
2. One to Many
3. Many to Many

What is a DDL, DML and DCL concept?
DDL (Data definition language) defines your database structure. CREATE and ALTER are DDL statements as they affect the way your database structure is organized.
DML (Data Manipulation Language) lets you do basic functionalities like INSERT, UPDATE, DELETE and MODIFY data in database.
DCL (Data Control Language) controls you DML and DDL statements so that your data is protected and has consistency. COMITT and ROLLBACK are DCL control statements. DCL guarantees ACID fundamentals of a transaction.

Difference between DELETE and TRUNCATE.
- DELETE table can be rolled back while TRUNCATE can not be.
- DELETE TABLE syntax logs the deletes thus making the delete operations low. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table. So TRUNCATE table is faster as compared to delete table.
- DELETE table can have criteria while TRUNCATE can not.
- TRUNCATE table can not have triggers.

What is a Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. 

Thanks,
Priya

Posted By: Anonymous

Posted Dt: 24-Mar-2012

50   11

Total Points: 5781

RE: sql server interview questions and answers for experienced

Hi Vaishnavi,

I have written a query to fetch the second highest salary from the table. Please find the query below and let me know if it is not working

[SQL]

select points from tablename as tbl where (select count(*) from tablename where salary>=tbl.salary)=2

Thanks,
Hariharan T G

Posted By: Anonymous

Posted Dt: 27-Mar-2012

44   8

Total Points: 8696

RE: sql server interview questions and answers for experienced

Hi Hari,

Thanks for posting the query. It works fine. :-)

Thanks

Posted By: Anonymous

Posted Dt: 28-Mar-2012

12   3

Total Points: 20889

RE: sql server interview questions and answers for experienced

Hi All,

Below are the few sql server interview questions.

Difference between truncate and delete in sql server.
• DELETE TABLE syntax logs the deletes thus make the delete operation slow. TRUNCATE table does not log any information but it logs information about deallocation of data page of the table so TRUNCATE table is faster as compared to delete table.
• DELETE table can have criteria while TRUNCATE cannot.
• TRUNCATE table does not invoke trigger.
• TRUNCATE will reset any identity columns to the default seed value.
• You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the constraints, TRUNCATE the table, and re-apply the constraints.

Difference between where and having clause
• SQL WHERE clause condition is tested against each and every row of data, while the SQL HAVING clause condition is tested against the groups and/or aggregates specified in the SQL GROUP BY clause and/or the SQL SELECT column list.
• HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.
• A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function. 

Difference between DBMS and RDBMS
A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored. RDBMS is a Relational Data Base Management System Relational DBMS. This adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. This excludes the databases that don''t support a tabular structure or don''t enforce relationships between tables. Many DBA''s think that RDBMS is a Client Server Database system but thats not the case with RDBMS. 

Thanks,
Balaji T G

Posted By: tgbalaji

Posted Dt: 28-Mar-2012

40   2

Total Points: 9450

RE: sql server interview questions and answers for experienced

What is a index in sql server?
An index is a physical structure containing pointers to the data. Indexes 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.
They are just used to speed up queries.
Drawbacks of Index:
- Any DML operation performed in the table will be slow since it has to update the index pointers.


Difference between subquery and corelated subquery?
In a subquery first the the inner query will be executed then the outer query will be executed.
In corelated subquery the inner query refers to the outer query.


What are Clustered & non Clustered Indexes
In clustered index table are physically stored. Only one clustered index. The leaf nodes of a clustered index contain the data pages.
A non clustered index 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


What are the types of joins in SQL Server?
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL OUTER JOIN
CROSS JOIN

Difference between delete and truncate in sql server.
The deletion of each row gets logged in the transaction log, which makes it slow deletes all the rows in a table, but it won’t log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster.

Thanks,

Posted By: Anonymous

Posted Dt: 30-Mar-2012

40   1

Total Points: 8696

RE: sql server interview questions and answers for experienced

Good collection of SQL Interview questions and answers.

Please post more questions.. 
Thanks

Posted By: Rocket Raja

Posted Dt: 12-May-2012

6   8

Total Points: 522

RE: sql server interview questions and answers for experienced

What are the error functions used in SQL Try catch block?
TRY…CATCH uses the following error functions to capture error information:
- ERROR_NUMBER() returns the error number.
- ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
- ERROR_SEVERITY() returns the error severity.
- ERROR_STATE() returns the error state number.
- ERROR_LINE() returns the line number inside the routine that caused the error.
- ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.


What new indexes are introduced in SQL Server 2005 in comparison to 2000?
- Spatial
- XML


What are Cursors and their types? What type do you use most and which one is fast?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. 
Types: FORWARD-ONLY, FAST-FORWARD or READ-ONLY cursors.
Fastest to slowest: Dynamic, Static, and Keyset.


Difference between DELETE & TRUNCATE statement? Which statement can be Rollbacked?
- With DELETE we can provide conditional WHERE clause to remove/delete specific rows, which is not possible with TRUNCATE.
- TRUNCATE is faster than DELETE as Delete keeps log of each row it deletes in transaction logs, but truncate keeps log of only de-allocated pages in transaction logs.
- Both statements can be rolled backed if provided in a transaction (BEGIN TRANS). If not then none of them can be rollbacked.
- DELETE is DML just like INSERT, UPDATE, but TRANCATE is DDL, just like CREATE, ALTER, DROP


What are the tools that could be used for SQL Query performance tuning?
Query Analyzer, Profiler, Index Wizard, Performance Monitor

Thanks,

SQL Queries Interview Questions and Answers on "SQL Select" - Examples

1. Get all employee details from the employee table

 Select * from employee

2. Get First_Name,Last_Name from employee table

 Select first_name, Last_Name from employee

3. Get First_Name from employee table using alias name “Employee Name”

 Select first_name Employee Name from employee

4. Get First_Name from employee table in upper case

 Select upper(FIRST_NAME) from EMPLOYEE

5. Get First_Name from employee table in lower case

Select lower(FIRST_NAME) from EMPLOYEE

6. Get unique DEPARTMENT from employee table

select distinct DEPARTMENT from EMPLOYEE

7. Select first 3 characters of FIRST_NAME from EMPLOYEE

Oracle Equivalent of SQL Server SUBSTRING is SUBSTR, Query : select substr(FIRST_NAME,0,3) from employee

SQL Server Equivalent of Oracle SUBSTR is SUBSTRING, Query : select substring(FIRST_NAME,0,3) from employee

MySQL Server Equivalent of Oracle SUBSTR is SUBSTRING. In MySQL start position is 1, Query : select substring(FIRST_NAME,1,3) from employee

8. Get position of 'o' in name 'John' from employee table

Oracle Equivalent of SQL Server CHARINDEX is INSTR, Query : Select instr(FIRST_NAME,'o') from employee where first_name = 'John'

SQL Server Equivalent of Oracle INSTR is CHARINDEX, Query: Select CHARINDEX('o',FIRST_NAME,0) from employee where first_name = 'John'

MySQL Server Equivalent of Oracle INSTR is LOCATE, Query: Select LOCATE('o',FIRST_NAME) from employee where first_name = 'John'

9. Get FIRST_NAME from employee table after removing white spaces from right side

select RTRIM(FIRST_NAME) from employee

10. Get FIRST_NAME from employee table after removing white spaces from left side

select LTRIM(FIRST_NAME) from employee

11. Get length of FIRST_NAME from employee table

Oracle,MYSQL Equivalent of SQL Server Len is Length , Query :select length(FIRST_NAME) from employee

SQL Server Equivalent of Oracle,MYSQL Length is Len, Query :select len(FIRST_NAME) from employee

12. Get First_Name from employee table after replacing 'o' with '$'

select REPLACE(FIRST_NAME,'o','$') from employee

13. Get First_Name and Last_Name as single column from employee table separated by a '_'

Oracle Equivalent of MySQL concat is '||', Query : Select FIRST_NAME|| '_' ||LAST_NAME from EMPLOYEE

SQL Server Equivalent of MySQL concat is '+', Query : Select FIRST_NAME + '_' +LAST_NAME from EMPLOYEE

MySQL Equivalent of Oracle '||' is concat, Query : Select concat(FIRST_NAME,'_',LAST_NAME) from EMPLOYEE

14. Get FIRST_NAME ,Joining year,Joining Month and Joining Date from employee table

SQL Queries in Oracle, Select FIRST_NAME, to_char(joining_date,'YYYY') JoinYear , to_char(joining_date,'Mon'), to_char(joining_date,'dd') from EMPLOYEE

SQL Queries in SQL Server, select SUBSTRING (convert(varchar,joining_date,103),7,4) , SUBSTRING (convert(varchar,joining_date,100),1,3) , SUBSTRING (convert(varchar,joining_date,100),5,2) from EMPLOYEE

SQL Queries in MySQL, select year(joining_date),month(joining_date), DAY(joining_date) from EMPLOYEE

SQL Server 2008 Interview Questions and Answers.

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. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?
Database normalization is a data design and organization process applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships.

What are different normalization forms?

1NF: Eliminate Repeating Groups
Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data
If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key 
If attributes do not contribute to a description of the key, remove them to a separate table.All attributes must be directly dependent on the primary key
BCNF: Boyce-Codd Normal Form
If there are non-trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships
No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships 
There may be practical constrains on information that justify separating logically related many-to-many relationships.
ONF: Optimal Normal Form
A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain-Key Normal Form
A model free from all modification anomalies.

Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

What is Stored Procedure?
A stored procedure 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. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.
e.g. sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?
A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS.Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; the DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.
Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?
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. 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. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

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. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index.
Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

. When is the UPDATE_STATISTICS command used? - When the processing of large data is done, this command is used.  - Whenever large number of deletions, modification or copy takes place into the tables, the indexes need to be updated to take care of these changes. UPDATE_STATISTICS performs this job. 2. Differentiate between a HAVING CLAUSE and a WHERE CLAUSE. HAVING CLAUSE - HAVING CLAUSE is used only with the SELECT statement.  - It is generally used in a GROUP BY clause in a query.  - If GROUP BY is not used, HAVING works like a WHERE clause. WHERE Clause - It is applied to each row before they become a part of the GROUP BY function in a query. Download SQL Server interview questions and answers pdf 3. What do you understand by a view? What does the WITH CHECK OPTION clause for a view do? - A view is a virtual table that consists of fields from one or more real tables. - It is usually used to join multiple tables and get the data.  - The WITH CHECK OPTION for a view prevents any modification to the data that does not confirm to the WHERE clause of the view definition. - This allows the data belonging to the view to be updated through the view. 4. Explain query execution plan? - The optimizer available in SQL Server optimizes the code to be effectively executed.  - A query execution plan shows how this optimizer would run the query. - Query execution plan can be viewed by :  - Using the Show Execution Plan option available in Query Analyzer, - Displaying Estimated Execution Plan on the query dropdown menu,  - Use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace. 5. What is the function of SQL Server Agent Windows service? - It is a Windows service which handles the tasks scheduled within the SQL Server environment. These tasks are also called as job and are stored with in SQL server. The jobs may run through a trigger, a predefined schedule or on demand.  - This service is very useful in determining why a particular job did not run as intended. 6. Comment on Transactions. - Using transactions we can group all SQL commands into a single unit.  - The transaction begins with some task and finishes only when all tasks within it are over. - The transaction gets over successfully only when all commands in it are successfully over. Even if one command fails, the whole transaction fails.  - The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions.  - A group of tasks starts with the begin statement.  - In case of any problem, the rollback command is executed to abort the transaction.  - If all the tasks run successfully, all commands are executed through commit statement. 7. Differentiate between a primary key and a unique key. - By default, clustered index on the column are created by the primary key whereas nonclustered index are created by unique key.  - Primary key doesn't allow NULLs, but unique key allows one NULL. 8. What is recursion? Is it possible for a stored procedure to call itself or recursive stored procedure?  How many levels of SP nesting is possible? Recursion is method of problem solving where the solution is arrived at by repetitively applying the logic and solution to the subsets of the problem. Transact-SQL supports recursion. So, yes it is possible for a stored procedure to call itself. Stored procedures and managed code references can be nested up to 32 levels. 9. What are the advantages of using Stored Procedures? - They help in reducing the network traffic and latency which in turn boosts application performance.  - They help in promoting code reuse.  - They provide better security to data.  - It is possible to encapsulate the logic using stored procedures. This allows to change stored procedure code without affecting clients.  - It is possible to reuse stored procedure execution plans, which are cached in SQL Server's memory. This reduces server overhead. 10. a.) What do you mean by an execution plan? Why is it used? How would you view it? a.) An execution plan can be called as a road map that graphically or textually shows the data retrieval methods which have been chosen by the SQL  Server query optimizer, for a stored procedure or ad- hoc query.  b.) It is used because it is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure.  c.) There exists an option called "Show Execution Plan" in Query Analyzer. If this option is turned on, it will display query execution plan in separate window when the query is run again. 11. You want to implement the following relationships while designing tables. How would you do it? a.) One-to-one b.) One-to-many  c.) Many-to-many a.) One-to-One relationship - can be implemented as a single table and rarely as two tables with primary and foreign key relationships.  b.) One-to-Many relationships - by splitting the data into two tables with primary key and foreign key relationships.  c.) Many-to-Many - by using a junction table with the keys from both the tables forming the composite primary key of the junction table. 12. Differentiate between DELETE and TRUNCATE. - Truncate can not be rolled back while Delete can be.  - Truncate keeps the lock on table while Delete keeps the lock on each row.  - Truncate resets the counter of the Identity column while Delete doesn't do so.  - Trigger is not fired in Truncate while it happens in Delete. 13. What are the properties of the Relational tables? Relational tables have six properties: 1. Values are atomic.  2. Column values are of the same kind.  3. Each row is unique.  4. The sequence of columns is insignificant.  5. The sequence of rows is insignificant.  6. Each column must have a unique name. 14. Explain the following. a.) COLLATION.  Collation is a type of sort order. There are mainly three types of sort orders, namely:  i.) Dictionary case sensitive ii.)Dictionary - case insensitive  iii.)Binary. b.) Stored Procedure - It is a set of T-SQL statements combined together to perform a single task formed by combining many small tasks.  - When you actually run a Stored procedure, a set of statements is run. 15. What do you mean by ACID? - ACID (Atomicity Consistency Isolation Durability) is a quality sought after in a reliable database. Here's the relevance of each quality:  - Atomicity is an all-or-none proposition.  - Consistency - it guarantees that your database is never left by a transaction in a half-finished state.  - Isolation - it keeps transactions separated from each other until they’re finished.  - Durability - it ensures that the database keeps a track of pending changes in a way that the server can recover from an abnormal termination. 16. Explain the following: a.) Dirty pages.  These are the buffer pages that contain modifications which have not been written to disk. b.) ETL - Extraction, Transformation, and Loading.  - It is the process of copying and cleaning data from heterogeneous sources.  - It is an important part of development projects for data warehousing and business intelligence. 17. Differentiate between a Local and a Global temporary table? - A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement.  - Global temporary tables (created with a double “##”) are visible to all sessions.  - Global temporary tables are dropped when the session that created it ends, and all other sessions have stopped referencing it. 18. Explain different types of Locks in SQL Server. There are 3 kinds of locks in SQL Server i.) Shared locks - they are used for operations which do not allow any change or update of data. For e.g. SELECT.  ii.) Update locks - they are used when SQL Server wants to modify a page. The update page lock is then promoted to an exclusive page lock before actually making the changes.  iii.) Exclusive locks - they are used for the data modification operations. For e.g. UPDATE, INSERT, or DELETE. SQL Server interview online test! Sql server interview test - part 1 (40 questions)  Sql server interview test - part 2 (28 questions)  Sql Server interview test - part 3 (25 questions) Database concepts interview test (20 questions) What is SQL Injection? What is DBCC? Give few examples. What is difference between View and Materialized view? What is CTE (Common Table Expression)? What is difference between clustered and non clustered index? What is use of EXCEPT clause? How it differs from NOT IN clause. What is difference between Index Seek vs. Index Scan? What is ROW_NUMBER function? What is Trigger? What is Scheduled job and how to create it? What is OPENXML in SQL Server? What are Sparse columns? What is RANK function? What are cursors and when they are useful? What is log shipping? What is SQL Profiler? What is Similarity and Difference between Truncate and Delete in SQL? What is Normalization of database? What are its benefits? What is Fill factor? What are different types of replication in SQL Server? What is REPLACE and STUFF function in SQL Server? Give a example to search for a string in all stored procedure in SQL Server. What are Magic tables in SQL Server? What is difference between stored procedure and user defined function? What are ACID properties of Transaction? What are COMMIT and ROLLBACK in SQL? What is a Linked Server? What is a WITH(NOLOCK)? What are the basic functions for master, msdb, model, tempdb databases? List few advantages of Stored procedure Can you explain about buffer cash and log Cache in sql server? Latest answer: Buffer Cache: Buffer cache is a memory pool in which data pages are read. It performance of the buffer cache is indicated as follows:............. Read answer What is a Trace frag? Where do we use it? Latest answer: Temporary setting of specific server characteristics is done by trace tags. DBCC TRACEON is the command to set the trace flags. Once activated, trace flag will be in effect until the server is restarted............... Read answer SSIS interview questions Difference between control flow and data flow?, If you want to send some data from Access database to SQL server database. What are different component of SSIS will you use?, Explain why variables called the most powerful component of SSIS?.................. Read answer Describe how to use Linked Server. Latest answer: MS SQL Server supports the connection to different OLE DB on an ad hoc basis. This persistent connection is referred as Linked Server.............. Read answer Explain how to send email from database. Latest answer: SQL Server has a feature for sending mail. Stored procedures can also be used for sending mail on demand. With SQL Server 2005, MAPI client is not needed for sending mails................ Read answer Explain how to make remote connection in database Latest answer: The following is the process to make a remote connection in database: - Use SQL Server Surface Area Configuration Tool for enabling the remote connection in database................... Read answer Difference between cross join and Full outer join. Latest answer: Cross Join : No join conditions are specified. Results in pairs of rows. Results in Cartesian product of two tables............... Read answer Explain the purposes of OPENXML clause sql server stored procedure. Latest answer: OPENXML parses the XML data in SQL Server in an efficient manner. It’s primary ability is to insert XML data to the RDB. It is also possible to query the data by using OpenXML................ Read answer What is the order in which the SQL query is executed? Latest answer: The following is the order of executing SQL query: The query goes to the shared pool that has information like parse tree and execution plan for the corresponding statement............... Read answer Explain how to store pdf file in sql server. Latest answer: Create a column as type ‘blob’ in a table. Read the content of the file and save in ‘blob’ type column in a table............... Read answer Explain the concepts and capabilities of SQL Server. Latest answer: Microsoft SQL server is a relational database management system. It uses MS- SQL as the query language. SQL Server offers a high level of security, reliability and scalability depending on the business needs.............. Read answer SQL Server interview questions for freshers and experienced SQL Server 2008 interview questions Explain inline variable assignment in sql server 2008 with an example. What is Compound Operators in sql server 2008? Explain with an example SQL Server 2008 introduces automatic auditing. Explain its benefits............. Read answer Explain the use of keyword WITH ENCRYPTION. Create a Store Procedure with Encryption. Latest answer: WITH ENCRYPTION Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an encrypted format. Users that have no access to system................ Read answer What is a linked server in SQL Server? Latest answer: A linked server allows remote access. Using this, we can issue distributed queries, update, commands, and transactions across different data sources................  Read answer Features and concepts of Analysis Services Latest answer: Analysis service provides a combined view of the data used in OLAP or Data mining. Services here refer to OLAP, Data mining. Analysis services assists in creating, designing........... Read answer What is Analysis service repository? Latest answer: Each server running analysis service has a repository to store objects of the computer running Analysis Services an Analysis service repository stores the information about the.............  Read answer What is SQL service broker? Latest answer: SQL service broker provides asynchronous queuing functionality to SQL server. Once message is sent to the SQL server................ Read answer What is user defined datatypes and when you should go for them? Latest answer: User defined datatypes is created by using base SQL Server data type by providing a descriptive name................. Read answer What is bit datatype? Latest answer: Bit datatype is used to store boolean information................ Read answer What is lock escalation? Latest answer: Lock escalation from SQL Server 7.0 onwards, is dynamically managed by SQL Server. It is.......... Read answer What is blocking? Latest answer: Blocking happens when one connection from an application holds a lock and a second............ Read answer What is Public Role in SQL Server? Latest answer: Every database has a public role which holds all the default permissions for the users in a database................. Read answer Discuss about SQL Server Login. Latest answer: SQL server login is used to connect to SQL server. This used when login in through the windows login credentials is not existent............. Read answer Discuss about Builtin\Administrator. Latest answer: The built in Administrator Account is basically used during some setup to join some machine in the domain............ Read answer Failover clustering overview Latest answer: Failover clustering is mainly used for data availability. Typically in a failover cluster, there are two machines. One machine provides the basic services and the second is available to run.................. Read answer Describe the XML support SQL server extends. Latest answer: SQL server can return XML document using FOR XML clause................. Read answer Explain in brief how SQL server enhances scalability of the database system. Latest answer: SQL Server has efficient ways to enhance scalability of the database system............... Read answer What is SQL Server English Query? Latest answer: SQL Server English Query helps to build applications that can accept query............. Read answer What is the purpose of SQL Profiler in SQL server? Latest answer: SQL Profiler captures SQL Server events from a server. The events are saved................. Read answer What are the ways available in SQL Server to execute SQL statements? Latest answer: SQL Server uses different ways to execute SQL statements which are listed below................ Read answer Explain Full-Text Query in SQL Server. Latest answer: SQL Server supports searches on character string columns using Full-Text Query............... Read answer Explain the phases a transaction has to undergo. Latest answer: The several phases a transaction has to go through are listed here. Database.............. Read answer What is XPath? Latest answer: XPath is a language defined by the W3C, used to select nodes from XML documents.............. Read answer Define the rules for designing Files and File groups in SQL Server. Latest answer: A file or file group can only be used by one database. For example, the files abc.mdf and abc.ndf contains................. Read answer What are the Authentication Modes in SQL Server? Latest answer: SQL Server supports two security (authentication) modes................ Read answer Explain Data Definition Language, Data Control Language and Data Manipulation Language. Latest answer: Data definition language is used to define and manage all attributes and properties of a database.............. Read answer What are the steps to process a single SELECT statement? Latest answer: SQL Server uses the following steps to process a single SELECT statement............ Read answer What are the restrictions while creating batches in SQL Server? Latest answer: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, and CREATE VIEW statements.............. Read answer Explain GO Command. Latest answer: GO Command is used to signal the end of a batch............... Read answer What is the significance of NULL value and why should we avoid permitting null values? Latest answer: Null means no entry has been made. It implies that the value is either unknown or undefined............ Read answer What is the difference between UNION and UNION ALL? Latest answer: UNION command selects distinct and related information from two tables. On the other hand.............. Read answer What is use of DBCC Commands? Latest answer: Database Consistency Checker Commands give details in form of statistics about the SQL Server..............  Read answer What is Log Shipping? Latest answer: UNION command selects distinct and related information from two tables. On the other hand............. Read answer What is the difference between a Local and a Global temporary table? Latest answer: A local temporary table lives until the connection is valid or until the duration of a compound statement.........  Read answer What is the STUFF and how does it differ from the REPLACE function? Latest answer: STUFF function is used to insert a string into another string by deleting some characters specified.............  Read answer Sql Server interview - May 7,  2011 by Swati Parakh Explain various data region available in SSRS with their use. Data regions are report items used to display data from a single dataset. You can perform grouping, sorting and various aggregate functions on data in data region. In SSRS 2005, there were 4 data regions:-  1. Table  2. Matrix  3. List  4. Chart While in SSRS 2008, there are one additional data region namely Gauge.  Let’s explain each one of them: 1. Table - Table Data region has fixed tabular structure i.e. fixed number of columns. It is useful for displaying data grouped by row. You can have maximum of 1 report item per cell. The size of table depends on number of rows dataset fetches i.e., if number of rows returned by dataset is more; it can expand to multiple pages. 2. Matrix – A matrix data region display data in pivot table format, hence also popularly known as pivot table or crosstab report. It has minimum of one row group and one column group. The size of matrix data region depends on columns and rows fetched. 3. List - A list data region is free layout. It is useful for complex reporting resign. The list can be used to display multiple table and matrix. Each getting data from different dataset. 4. Chart – This data region is for displays the data graphically i.e., in form of chart. A various chart types are available in SSRS 2008 namely line, pie chart, columns etc. 5. Gauge - This can be used in a table or matrix to show the relative value of a field in a range of values in the data region. You can also add a gauge to the design surface to show a single relative value. What are various ways to enhance the SSRS report? Explain. There are various ways in which you can enhance your report: 1. Display your data in graphic format using Chart Region.  2. Use sorting.  3. If couple of reports are related, you can make them interactive using connect them using bookmark link, hyper link or drill through report link.  4. Adding sub-report. Sub-report is a stand-alone report which can be link to another report based on its content using parameter.  5. Add custom fields. Custom fields provide with same functionality as alias columns provide in SQL server query. It is the timing of the operation that differs from the alias columns. The calculation is performed on dataset by report server.  6. Using expression.  7. Using custom code. SSRS allows including custom code written in VB.Net. 8. Add document map (navigational links to report item once report is rendered) to report. Sql Server interview - July 7,  2011 by Swati Parakh What are various aggregate functions that are available? The following are various aggregate functions available:-  1. SUM  2. AVG  3. COUNT  4. COUNTDISTINCT  5. MAX  6. MIN  7. STDEV  8. STDEVP  9. VAR  10. VARP By default, SUM is the aggregate function used for numeric data type. How do you integrate the SSRS reports in your application? There are 3 ways in which you can integrate reports into your application:-  1. Navigating to URL i.e. https:\\servername\reportservername\reportname – This is simplest and most popular way. A separate login might be required since we are directly calling the report from report server. Address of report server gets expose to user.  2. Using IFrame, Browser control or Report Viewer Control – In this approach, we embed the URL of report server in our application, hence address of reportserver is not exposed. No separate window opens. A user does not come to know that he has moved to different server.  3. Programmatically sending a web request using SOAP to report server. Explain use of Expression builder. Expressions provide us with flexibility to customize our report. It is written in Visual basic and is used throughout the report to to retrieve, calculate, display, group, sort, filter, parameterize, and format the data in a report. They start with equal sign (=). S.No. Functionality Property, Context and Dialog Box Expression 1 Format data in a text box depending on value Colour for a placeholder inside of a text box in the details row for a Tablix =IIF(Fields!TotalDue.Value < 10000,"Red","Black") 2 Dynamic page header or footer content. Value for a placeholder inside of a text box that is placed in the page header or footer. ="Page " & Globals!PageNumber & " of " & Globals!TotalPages 3 Specify page breaks for every 20 rows in a Tablix with no other groups. Group expression for a group in a Tablix. =Ceiling(RowNumber(Nothing)/20) 4 Shows the user ID of the person running the report Value =User!UserID 5 To get first day of the month Value =DateSerial(Year(Today()),Month(Today()),1) 6 To get the current date Value =Today() 7 To get last day of the month Value =DateAdd("d",-1,DateSerial(Year(Today()),Month(Today())+1,1)) Sql Server interview - July 10,  2011 by Swati Parakh Difference between drill down and drill through report. Both the drill down and drill through report provide interactive functionality to the SSRS report. The differences are as follows:- Trait Drill Down Drill Through Retrieves Data Data retrieved at the same time as main report Data retrieved one click on link of drill through report Is processed and rendered when With the main report When link is clicked Performance Slower since retrieves all data with main report Faster (but does not retrieve all data with main report) Is displayed Within main report Separately either in separate window or tab What’s the use of custom fields in report? Custom fields can be defined as alias column of the report since the operation is performed on report server rather than on database server. The custom field is very useful for the data manipulation like adding some fields whose value can be calculated based on expression, text e.g. instead of CName fetched from database, I want the dataset to display Customer Name etc. We can add custom fields as right click on dataset, select add in Dataset window. The New field dialog box will open, we can add name of custom field and also mention whether it is database field or calculated one. If it is calculated, then we can mention the computation in this window. Can we use custom code in SSRS? If so, explain how we can do. Yes, we can. SSRS allows developer to add custom code in your report. You can write the code directly into embedded VB.Net and call it using property expression or you can write a custom class library and refer it in report server. The advantage of first method is that it is simple and easy to use but disadvantage is that it is available for that report only. While the second method has advantage of being available for multiple reports but it has much of configuration overhead. To write custom code, right click on Report Designer outside report body and select Properties and go to Code tab and you can write custom code here. To add custom class library, right click on Report Designer outside report body and select Properties and go to Reference tab and add the reference by browsing to the assembly of your class library. Note that you need to create class library and then compile it before referencing it in your SSRS report. Sql Server interview - July 16,  2011 by Swati Parakh Difference between report and query parameter. Why do we need different type of parameter? Query Parameter Report Parameter Defined At Database Level Report Level Created Automatically if database query or stored procedure has a parameter Automatically if report has some query parameter and is mapped to query parameter processed On Database Server On Report Server Use Filtering of Data, Security of Data Manipulate data, interconnect reports, filtering data Processing Output Number of records returned is based on query parameter Number of records presented is based on query. Note- Records processed on report parameter would be same as records returned based on query parameter. Filtering data based on them Performance is good Full set of records is retrieved then filtered. Hence, performance is low How does your SSRS maintain security? Reporting services maintain role based security. When a user logs into reporting services, a Report Manager (whose duty is to maintain security of Reporting Services) first checks the identity of user and then determine what rights he have to perform on report. Report Manager manages the security at 2 levels –  1. System-level – Administer the report server globally  2. Item-level – Security at report and dataset level System-level roles are:- 1. System Administrator – can manage report server and report manager security 2. Site User - view basic information like report properties and schedules. Item-level roles – User can use any of predefined item-level roles or create their own roles by using combination of predefined item-level roles.  Pre-defined Item-level roles are:-  1. Browser – can navigate to report and run them.  2. My Reports – these users’ rights is restricted to reports present in their MyReports folder. However, they can create, view and manage reports in their folder.  3. Publisher – As name suggest, publisher user has rights to publish reports to Reporting Server database.  4. Content Manager – has all permission at item-level. SQL Server interview questions and answers - submitted by Arpit Jain What is SQL Injection? SQL Injection is an attack in which attacker take the advantage of insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website. This attack can happen with the applications in which SQL queries are generated in the code. The attacker tries to inject their own SQL into the statement that the application will use to query the database. For example suppose the below query string is used for a search feature in a website and a user may have the inserted “Arpit” as the keyword to search. If in the code behind the keyword is directly used into the SQL statement, it will be like. String sql = “Select EmpName, City from EmployeeMaster where EmpName like ‘%” + txtSearch.Text + “%’”; But the attacker might enter the keyword like ‘ UNION SELECT name, type, id from sysobjects;-- This way attacker can get details of all the objects of the application database and using that attacker can steal further information. What is DBCC? Give few examples. DBCC stands for Database Consistency Checker. These commands are used to check the consistency of the database like validation tasks, maintenance and status checks. For example – 1. DBCC CHECKALLOC – It is used to check that all pages are correctly allocated in database. 2. DBCC CHECKDB – It is used to check that  3. DBCC SQLPERF – It generates a report for the current usage of  4. DBCC SQLFILEGROUP – It used to check all the tables file group for any design. What is difference between View and Materialized view? View result set doesn’t save anywhere on disk and executes the query definition whenever they are called, while materialized view are disk based and its result set table is updated periodically. Materialized view is similar to regular views but the output of select query has been saved to a table. View shows the latest data all the time while the materialized view only shows the fresh data after its result table is updated either by setting a schedule or based on the change in the underlying tables. The performance of the view depends on how good the selected statement the view has. If the select statement has too many joins then it the view will perform poorly. While in the case of materialized view, we are querying a table, which may also be indexed, that increase its performance. What is CTE (Common Table Expression)? When a complex SQL statement has number of joins then it can be made easier by using Common Table Expression. Consider the following SQL statement. SELECT * FROM (  SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp  left join Department dept on emp.DeptID = dept.DeptID) E WHERE E.Gender = ‘Male’ ORDER BY T.EmpName The syntax of CTE is as follow - The CTE Name (followed by WITH keyword) - The Column List (Optional) - The Query (Appears within parentheses after the AS keyword) If we write the above messy query using CTE it would be like With E(EmpName, Department, Gender) AS ( SELECT emp.EmpName,dept.Department,emp.Gender FROM Employee emp  left join Department dept on emp.DeptID = dept.DeptID ) SELECT * FROM E WHERE E.Gender = ‘Male’ ORDER BY E.EmpName This way the query can be made more readable and easy to understand. What is difference between clustered and non clustered index? A table can have only one Clustered Index at a time which is generally created on primary key and can have more than one non clustered indexes (maximum up to 999) The leaf level of clustered index is actual data pages of the table. Whereas in case of non-clustered index the leaf level is a pointer to the data. Non-clustered index is faster than clustered index because when we use DML statement on clustered index, performance issues may occurred since it has to update the index every time a DML statement is executed. Syntax of creative clustered / non clustered index is as follow CREATE [CLUSTERED | NON CLUSTERED] INDEX index_name ON <object> (column [ASC | DESC] [,…n]) What is use of EXCEPT clause? How it differs from NOT IN clause. -When we combine two queries using EXCEPT clause, it will returns distinct rows from the first SELECT statement that are not returned by the second one. -EXCEPT clause works the same way as the UNION operator of SQL and MINUS clause in Oracle. -The syntax of EXCEPT clause is as follow SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] EXCEPT SELECT column1 [, column2 ] FROM table1 [, table2 ] [WHERE condition] -The difference between EXCEPT and NOT IN clause is EXCEPT operator returns all distinct rows from the rows returned by first select statement which does not exist in the rows returned by second select statement. On the other hand “NOT IN” will return all rows from returned by first select statement which does not exist in the rows returned by second select statement. What is difference between Index Seek vs. Index Scan? Index Seek and Index Scan are operation for query tuning in execution plans. Table Scan scans every record of the table. So the cost of proportional is the number of rows of that table. The Index Scan is preferred only when the table is small. Index Seek only touches the rows which qualify and the pages that contain that qualifying rows, so the cost of proportional is the number of qualifying rows and pages instead of the number of rows in the table. Index seek is preferred for highly sensitive queries. What is ROW_NUMBER function? RANK is one of the Ranking functions which are used to give rank to each row in the result set of a SELECT statement. For using this function first specify the function name, followed by the empty parentheses. Then specify the OVER function. For this function, you have to pass an ORDER BY clause as an argument. The clause specifies the column(s) that you are going to rank. For Example  SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary In the result you will see that the highest salary got the first rand and the lowest salary got the last rank. Here the rows with equal salaries will not get same ranks. What is Trigger? -In SQL the Trigger is the procedural code that executed when you INSERT, DELETE or UPDATE data in the table.  -Triggers are useful when you want to perform any automatic actions such as cascading changes through related tables, enforcing column restrictions, comparing the results of data modifications and maintaining the referential integrity of data across a database. -For example, to prevent the user to delete the any Employee from EmpDetails table, following trigger can be created. create trigger del_emp on EmpDetails for delete as begin rollback transaction print "You cannot delete any Employee!" end -When someone will delete a row from the EmpDetails table, the del_emp trigger cancels the deletion, rolls back the transaction, and prints a message "You cannot delete any Employee!" What is Scheduled job and how to create it? -If we want to execute any procedural code automatically on specific time either once or repeatedly then we can create a Scheduled job for that code. -Following are the steps to create a Scheduled Job. 1. Connect to your database of SQL server in SQL Server Management Studio. 2. On the SQL Server Agent. There you will find a Jobs folder. Right click on jobs and choose Add New. 3. A New Job window will appear. Give a related name for the job. 4. Click next on the "Steps" in the left menu. A SQL job can have multiple steps either in the form of SQL statement or a stored procedure call. 5. Click on the "Schedules" in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which sql job will run itself. You can specify recurring schedules also. -Using scheduled job you can also create alert and notifications. What is OPENXML in SQL Server? -OPENXML provides an easy way to use an XML document as a data-source for your procedures.  -OPENXML data can be manipulated the same way we deal with database tables by treating xml tags in the form of columns and the value in the form of rows. -By using OPENXML Data can be inserted or updated very quickly without multiple trips to the database. -Example: DECLARE @count int DECLARE @xml varchar(5000) SET @xml ='<Employees> <Employee id="1"> <Name>Arpit</Name> <Employee >1234</ Employee > </Employee > <Employee id="2"> <Name>Rahul</Name> <PhoneNo>2211</PhoneNo> </Employee > </Employees>' EXEC sp_xml_preparedocument @count OUTPUT, @xml SELECT * FROM OPENXML (@count, Employees/Employee') WITH (id Varchar(10), Name varchar(100) 'Name' , PhoneNo Varchar(50) 'PhoneNo') EXEC sp_xml_removedocument @index It will give following result.  1 Arpit 1234 2 Rahul 2211 What are Sparse columns? Sparse column is a type of column which is used to optimize storage for null values. When a column there is big number of null then by defining that column as spars column we can save a large amount of disk space. The drawback of sparse column is that it requires more space for the non null values. When we define a column as sparse it requires additional 4 Byte for not null values. For example, a DATETIME column in a non-sparse column required 8 bytes of storage whereas if we define that column as a sparse column then it will require 12 bytes. It is not possible to set ROWGUIDCOL and IDENTITY properties in sparse column. What is RANK function? -RANK is one of the Ranking functions which are used to give rank to each row in the result set of a SELECT statement. -For using this function first specify the function name, followed by the empty parentheses. -Then specify the OVER function. For this function, you have to pass an ORDER BY clause as an argument. The clause specifies the column(s) that you are going to rank. For Example  SELECT RANK() OVER(ORDER BY Salary DESC) AS [RowNumber], EmpName, Salary, [Month], [Year] FROM EmpSalary -In the result you will see that the highest salary got the first rand and the lowest salary got the last rank.  Here the rows with equal salaries will get same ranks. -Remember that the rank depends on the row's position in the result set, not on the sequential number of the row. What are cursors and when they are useful? -When we execute any SQL operations, SQL Server opens a work area in memory which is called Cursor.  -When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used. -There are two of cursors 1. Implicate Cursor SQL Server automatically manages cursors for all data manipulation statements. These cursors are called implicit cursors.  2. Explicit Cursor When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name. They are managed by OPEN, FETCH and CLOSE. %FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are used in both types of cursors. What is log shipping? -SQL has introduced Log Shipping feature to synchronize the Distributed Database Server. Synchronize the database by copying Transaction logs, Backing up, Restoring data. SQL Server Job Agents is used to make these processes automatic. -In the case of failure the Log Shipping will not transfer the server. That means it will not redirect your application to other server. This has to be done manually. -Log shipping synchronizes the database only. The main functions of Log Shipping are as follows: 1. To Back up the transaction log of the primary database 2. To Copy the transaction log backup to every secondary server 3. To Restore the transaction log backup on the secondary database What is SQL Profiler? -SQL Server provides a graphical tool which helps system administrators to monitor T-SQL statements of Database Engine. -SQL Profiler can capture and store data related to every event to a file or a table. -SQL Server Profiler can be used 1. To create a trace  2. To store the trace results in a table. 3. To watch the trace results when the trace runs  4. To replay the trace results  5. To start, stop, pause, and modify the trace results What is Similarity and Difference between Truncate and Delete in SQL? Similarity -These both command will only delete data of the specified table, they cannot remove the whole table data structure. Difference -TRUNCATE is a DDL (data definition language) command whereas DELETE is a DML (data manipulation language) command.  -We can’t execute a trigger in case of TRUNCATE whereas with DELETE command, we can execute a trigger. -TRUNCATE is faster than DELETE, because when you use DELETE to delete the data, at that time it store the whole data in rollback space from where you can get the data back after deletion. In case of TRUNCATE, it will not store data in rollback space and will directly delete it. You can’t get the deleted data back when you use TRUNCATE. -We can use any condition in WHERE clause using DELETE but you can't do it with TRUNCATE. -If table is referenced by any foreign key constraints then TRUNCATE will not work. What is Normalization of database? What are its benefits? -Normalization is set of rules that are to be applied while designing the database tables which are to be connected with each other by relationships. This set of rules is called Normalization. -Benefits of normalizing the database are 1. No need to restructure existing tables for new data. 2. Reducing repetitive entries. 3. Reducing required storage space  4. Increased speed and flexibility of queries. What is Fill factor? The 'fill factor' option indicate how full SQL Server will create each index page. When the index page doesn’t have free space for inserting a new row, SQL Server will create new index page and transfer some rows from the previous index page to the new index page. This process is called page split. If we want to reduce the number of page splits then we can use Fill factor option. Using Fill factor SQL will reserve some space on each index page. The fill factor is a value from 1 through 100 that indicates the percentage of the index page to be left empty. The default value for fill factor is 0. If the table contains the data which is not changed frequently then we can set the fill factor option to 100. When the table's data is modified frequently, we can set the fill factor option to 80% or as we want. What are different types of replication in SQL Server? There are three types of replication in SQL SERVER 1. Snapshot Replication. In Snapshot Replication snapshot of one database is transferred to another database. In this replication data can be refreshed periodically and all data will be copied to another database every time the table is refreshed. 2. Transactional Replication In transactional replication data will be same as in snapshot replication, but later only the transactions are synchronized instead of replicating the whole database. We can specify the refresh of database either continuously or on periodic basis. 3. Merge Replication Merge replication replicate data from multiple sources into a single central database. The initial load will be same as in snapshot replication but later it allows change of data both on subscriber and publisher, later when they come on-line it detects and combines them and updates accordingly. What is REPLACE and STUFF function in SQL Server? STUFF: This function is used to replace the part of string with some another string. Syntax: STUFF (String1, Position, Length, String2) String1 - String to be overwritten Position - Starting Position for overwriting Length - Length of replacement string String2- String to overwrite Example: SELECT STUFF(‘Arpit',2,2,'mi') Output: Amit REPLACE: This function is used to replace all the occurrences of particular string by another string. Syntax: REPLACE(String1, String2, String3) Example: SELECT REPLACE(‘Arpit Jain’,’i’,’m’) Output: Arpmt Jamn If you want to replace the first occurrence of “I”, Replace wouldn't work, because it always replaces ALL occurrences of the string. Give a example to search for a string in all stored procedure in SQL Server. -Suppose we have a EmpDetails table in our database and there are certain number of stored procedures in database. We want to know in which stored procedure(s) table EmpDetails is used. -We can use following query SELECT sys.objects.name, sys.objects.type, sys.objects.type_desc, sys.objects.schema_id, sys.syscomments.text FROM sys.objects INNER JOIN sys.syscomments ON sys.objects.object_id = sys.syscomments.id where sys.syscomments.text like '%EmpDetails%' And type ='P' What are Magic tables in SQL Server? -In SQL Server there are two system tables “Inserted” and “Deleted” called Magic tables. -These are not the physical tables but the virtual tables generally used with the triggers to retrieve the inserted, deleted or updated rows. -When a record is inserted in the table that record will be there on INSERTED Magic table. -When a record is updated in the table that existing record will be there on DELETED Magic table and modified data will be there in INSERTED Magic table. -When a record is deleted from that table that record will be there on DELETED Magic table. What is difference between stored procedure and user defined function? It is not possible to change the table data with the use of User defined functions but you can do it by using stored procedure. The execution of User defined function will be stopped if any error occurred in it. But in the case of Stored procedure when an error occurs the execution will ignore the error and jumps to the next statement. We can use User defined function in XML FOR clause but we can use stored procedure in XML FOR clause. It is not possible to make permanent changes to server environment whereas stored procedure can change some of the server environment. User defined functions do not return output parameters while stored procedure can return output parameters. What are ACID properties of Transaction? Following are the ACID properties for Database. Atomicity – Transactions may be set of SQL statements. If any of statement fails then the entire transaction fails. The transaction follows all or nothing rule. Consistency – This property says that the transaction should be always in consistent state. If any transaction is going to effect the database’s consistent state then the transaction could be rolled back. Isolation – This property says that one transaction can not retrive the data that has been modified by any other transaction until its completed. Durability – When any transaction is committed then it must be persisted. In the case of failure only committed transaction will be recovered and uncommitted transaction will be rolled back. What are COMMIT and ROLLBACK in SQL? COMMIT statement is used to end the current transaction and once the COMMIT statement is exceucted the transaction will be permanent and undone. Syntax: COMMIT; Example:  BEGIN UPDATE EmpDetails SET EmpName = ‘Arpit’ where Dept = ‘Developer’ COMMIT; END; ROLLBACK statement is used to end the current transaction and undone the changes which was made by that transaction. Syntax: ROLLBACK [TO] Savepoint_name; Example BEGIN Statement1; SAVEPOINT mysavepoint; BEGIN Statement2; EXCEPTION WHEN OTHERS THEN ROLLBACK TO mysavepoint; Statement5; END; END; What is a Linked Server? When we want to query on remote database server along with the local database server then we can add the remote SQL server to local SQL server in a same group using the concept called Linked Server. We can query on both servers using T-SQL. We can use stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin to add new Linked Server. By using Linked Server we can SQL statement in clean and easy way to retrieve, join and combine remote data with local data. What is a WITH(NOLOCK)? WITH(NOLOCK) is used to unlock the data which is locked by the transaction that is not yet committed. This command is used before SELECT statement. When the transaction is committed or rolled back then there is no need to use NOLOCK function because the data is already released by the committed transaction. Syntax: WITH(NOLOCK) Example:  SELECT * FROM EmpDetails WITH(NOLOCK) WITH(NOLCOK) is similar as READ UNCOMMITTED What are the basic functions for master, msdb, model, tempdb databases? The Master database contains catalog and data for all databases of the SQL Server instance and it holds the engine together. Because SQL Server cannot start if the master database is not working. The msdb database contains data of database backups, SQL Agent, DTS packages, SQL Server jobs, and log shipping. The tempdb contains temporary objects like global and local temporary tables and stored procedures. The model is a template database which is used for creating a new user database. List few advantages of Stored procedure. By using stored procedures we can reuse the code. Stored procedure helps in reducing network traffic and latency. Stored procedures provide better security to your data. Stored procedure is cached in SQL Server’s memory. So it helps to reduce the server overhead. It also enhances application performance. Stored procedures help us in the encapsulation of the code. The code of the stored procedure can be changed without affecting application. Test your sql server knowledge with our multiple choice questions!  Next>> Part 1 | Part 2 | Part 3 | Part 4 | part 5 | part 6 | part 7 | part 8 | part 9 Write your comment - Share Knowledge and Experience Discussion Board SQL Server interview questions and answers  What is Lock Escalation? Lock escalation is the process of reducing the overhead of the system by converting many fine grain locks into fewer coarse grain locks. Lock escalation threshold is determined dynamically by SQL server. It doesn’t require any configuration hassles as SQL Server choose to keep lock on both row and column for the page query. What is RAID and what are different types of RAID levels? RAID stands for Redundant array of independent disks which was earlier called as Redundant array of inexpensive disks. It is a storage technology that has one logical unit consisting of multiple disk drive components. It increases the performance by replicating and dividing the data through many levels between multiple physical drives. There are 12 Raid Levels which are as follows: - Level 0: it is a 'striped' disk array (provides data stripping) without fault tolerance. - Level 1: It is used in system for “mirroring” and “duplexing” purpose. - Level 2: in this error correction takes place - Level 3: it provides byte level stripping also called as “bit-interleaved parity” - Level 4: is used as “dedicated parity drive” and it provides block level striping - Level 5: is “block interleaved distributed parity” - Level 6: is “independent data disks with double parity. - Level 0+1: is “a mirror of stripes” and used for replication and sharing of data among disks - Level 10: is “a stripe of mirrors”. Multiple mirrors are created and then stripes over it. - Level 7: It adds caching to Level 3 or 4. - Level 50: implemented as striped array with fault tolerance - RAID S: it is proprietary striped parity RAID system  Rohit Sharma 12-7-2011 07:06 AM  SQL Server interview questions and answers  What's the difference between a primary key and a unique key? - Primary key is a combination of columns which uniquely specify a row whereas a unique key is related to the superkey and can uniquely identify each row in the table. - Primary can only be one in each table as it is one of the special cases of the unique key whereas a unique key can be many.  - Primary key enforces the NOT NULL constraint whereas unique key doesn’t. Due to this values in the unique key columns may or may not be NULL. What is bit data type and what's the information that can be stored inside a bit column? - Bit data type is the smallest type used in a language. It is used to store the boolean information of the form 1 (true) or 0 (false). The former versions of SQL server doesn’t support NULL type in this but recent version such as SQL server 7.0 onwards it supports NULL state as well.  Define candidate key, alternate key, and composite key. - Candidate Key is a key which provides the uniqueness of the column(s). It identifies each row of a table as unique. It can become the primary key of the table as well. Every tabular relationship will have atleast one candidate key. - Alternate Key is a type of candidate key which is formed when there are more than one candidate key and one of them is a primary key then other keys will act as an alternate keys. Unique keys also termed as alternate keys which prevent incorrect data from entering the table.  - Composite Key is a special type of candidate key as it is formed by combining two or more columns. This gives assurance of uniqueness of data when the columns are joined together. What are ACID properties? ACID is used in database and it includes the following properties such as atomicity, consistency, isolation and durability. These properties allow easy, reliable and secure database transaction. Example: Transfer of money from one bank account to another. It is used to manage the concurrency in the database table.  What is the difference between Locking and multi-versioning? Locking is a means of not allowing any other transaction to take place when one is already in progress. In this the data is locked and there won’t be any modification taking place till the transaction either gets successful or it fails. The lock has to be put up before the processing of the data whereas  Multi-versioning is an alternate to locking to control the concurrency. It provides easy way to view and modify the data. It allows two users to view and read the data till the transaction is in progress. Rohit Sharma 12-7-2011 07:06 AM  SQL Server interview questions and answers  What's the difference between a primary key and a unique key? - Primary key is a combination of columns which uniquely specify a row whereas a unique key is related to the superkey and can uniquely identify each row in the table. - Primary can only be one in each table as it is one of the special cases of the unique key whereas a unique key can be many.  - Primary key enforces the NOT NULL constraint whereas unique key doesn’t. Due to this values in the unique key columns may or may not be NULL. What is bit data type and what's the information that can be stored inside a bit column? - Bit data type is the smallest type used in a language. It is used to store the boolean information of the form 1 (true) or 0 (false). The former versions of SQL server doesn’t support NULL type in this but recent version such as SQL server 7.0 onwards it supports NULL state as well.  Rohit Sharma 12-7-2011 07:05 AM  SQL Server interview questions and answers  What is normalization? Explain different forms of normalization? Normalization is a process of organizing the data to minimize the redundancy in the relational database management system (RDBMS). The use of normalization in database is to decompose the relations with anomalies to produce well structured and smaller relations. There are 6 forms of normalization which are as follows:- - 1NF represents a relation with no repeating groups - 2NF represents no non-prime attribute in the table  - 3NF defines that every non-prime attribute is non-transitively dependent on every candidate key - 4NF defines that every non-trival multi-valued dependency in table is dependent on superkey. - 5NF defines that every non-trival join dependency in table is implied by superkey in table. - 6NF defines that a table features no non-trival join dependency.  What is de-normalization and what are some of the examples of it? De-normalization is used to optimize the readability and performance of the database by adding redundant data. It covers the inefficiencies in the relational database software. De-normalization logical data design tend to improve the query responses by creating rules in the database which are called as constraints.  Examples include the following: - Materialized views for implementation purpose such as: - Storing the count of “many” objects in one-to-many relationship - Linking attribute of one relation with other relations - To improve the performance and scalability of web applications Rohit Sharma 12-7-2011 07:04 AM  SQL Server interview questions  What are the different index configurations a table can have? No indexes A clustered index A clustered index and many nonclustered indexes A nonclustered index Many nonclustered indexes What is BCP?  It is used to copy huge amount of data from tables and views.  It does not copy the structures same as source to destination.  Dheeraj 12-6-2011 01:38 AM  Also read  SQL Profiler  What are the tasks performed by SQL Profiler? How can you use the SQL Profiler to ensure database security? How can you reduce the amount of data collected by a trace? What is SQL Profiler? When do you use SQL Profiler?............ Sql Server DBA interview What are the steps to take to improve performance of a poor performing query? What is a deadlock and what is a live lock? How will you go about resolving deadlocks? What is blocking and how would you troubleshoot it?............. Sql Server database maintenance Explain the concepts of faster differential backups. Explain the concepts of Parallel Database consistency check (DBCC). Define Indexed view. Define Distributed partitioned views............ SQL Server backup devices and methods Database backup methods - Full Backups, Differential Backups, Transaction Log Architecture Backups, File and Filegroup Backups............... RAID (Redundant Array of Inexpensive disks) RAID is a mechanism of storing the same data in different locations. Since the same data is stored, it is termed as redundant............ SQL Server 2005 XML support Explain the concepts and capabilities of SOAP.  Explain the purpose of Native XML mode in SQL Server 2005. Native XML Access vs. SQLXML............. XML Data type implementation in SQL 2005 What is Untyped XML? Provide examples for : Create a table with a untype XML column, Insert into an untyped XML data type column. What is typed XML? The XML data type comes with five methods. Explain them Differentiate between Untyped XML and Typed XML................... Querying & modifying XML data in SQL 2005 What is XQuery language? Explain the syntax rule of XQuery language.  XQuery expression contains two parts: the Prolog and the Body. Explain them Explain PATH expression in XQuery with an example..................... Indexing XML data in SQL Server 2005 Explain the concepts of indexing XML data in SQL Server 2005. Provide basic syntax for creating index on XML data type column. What is content indexing/full text indexing? Explain the reason to index XML data type column. What are the guidelines to be adhered when creating a XML index?................... Indexing XML data in SQL Server 2005 Explain the concepts of indexing XML data in SQL Server 2005. Provide basic syntax for creating index on XML data type column. What is content indexing/full text indexing? Explain the reason to index XML data type column. What are the guidelines to be adhered when creating a XML index?............... XML Schemas in SQL Server 2005 Syntax and example for creating and dropping XML schema collection. Limitations of the XML Schema collections. Discuss about XML schema collection permission................... FOR XML in SQL Server  Discuss the TYPE directive of FOR XML. Explain the RAW mode of FOR XML. Explain the AUTO mode of FOR XML. Explain the EXPLICIT mode of FOR XML.............. CLR support for SQL Server 2005 Overview of integration of CLR with SQL Server. Advantages of CLR integration............... XSLT in SQL Server 2005 Overview of XSLT and the components that make up an XSLT style sheet. What is XSLCompiledTransform class of the .NET Framework? What is XSLTSetting class of the .NET Framework?................... SOAP support in SQL Server 2005  Syntax and example for creating and dropping XML schema collection. Limitations of the XML Schema collections. Discuss about XML schema collection permission.............. Transferring a View from Oracle 10G XE to an SQL Server 2005 Database Here you will learn how to copy a table on an Oracle 10G XE database to a database on the SQL Server 2005. You will also learn how to install an Oracle 10G XE server and work with its database objects........... SQL Server 2005 Analysis Services What is SQL Server 2005 Analysis Services (SSAS)? What are the new features with SQL Server 2005 Analysis Services (SSAS)? What are SQL Server Analysis Services cubes?  Explain the purpose of synchronization feature provided in Analysis Services 2005. Explain the new features of SQL Server 2005 Analysis Services (SSAS). [Hint - Unified Dimensional Model, Data Source View, new aggregation functions and querying tools]....................