SQL Developer Interview Questions

1. Difference between IN and Exists.
WHEN YOU USE 'IN', WHILE CHECKING FOR WHERE CONDITION SQL SERVER ENGINE DOES WHOLE TABLE SCAN.IF YOU USE 'EXISTS' AS SOON AS ENGINE FINDS THE REQUIRED ROW IT WILL STOP EXECUTING QUERY AND GOING FURTHER SCANNING TABLE. SO BASICALLY EXISTS IS FASTER AS COMPARED TO IN.

2. Locks
There are different types of lock in SQL Server 2000 and 2005. These locks are applied in different situations. Here is the list of locks and the situation for the locks.
SHARED - This lock is applied for read operation where the data is not updated. A good example would be the select statement.
UPDATE – This locked on those resources that can be updated. This lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later.
EXCLUSIVE - Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.
INTENT - Used to establish a lock hierarchy. The different types of intent locks are: intent shared, intent exclusive, and shared with intent exclusive.

SCHEMA - Used when an operation dependent on the schema of a table is executing. The different types of schema locks are: schema modification and schema stability.
BULK UPDATE – This lock is applied when there is a bulk copying of data and the TABLOCK is applied
KEY RANGE - Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.
Locking optimizer hints
SQL Server 7.0/2000 supports the following Locking optimizer hints:
NOLOCK is also known as "dirty reads". This option directs SQL Server not to issue shared locks and not to honor exclusive locks. So, if this option is specified, it is possible to read an uncommitted transaction. This results in higher concurrency and in lower consistency.

HOLDLOCK directs SQL Server to hold a shared lock until completion of the transaction in which HOLDLOCK is used. You cannot use HOLDLOCK in a SELECT statement that includes the FOR BROWSE option. HOLDLOCK is equivalent to SERIALIZABLE.

UPDLOCK instructs SQL Server to use update locks instead of shared locks while reading a table and holds them until the end of the command or transaction.

TABLOCK takes a shared lock on the table that is held until the end of the command. If you also specify HOLDLOCK, the lock is held until the end of the transaction.

PAGLOCK is used by default. Directs SQL Server to use shared page locks.

TABLOCKX takes an exclusive lock on the table that is held until the end of the command or transaction.

READCOMMITTED
Perform a scan with the same locking semantics as a transaction running at the READ COMMITTED isolation level. By default, SQL Server operates at this isolation level.

READUNCOMMITTED
Equivalent to NOLOCK.

REPEATABLEREAD
Perform a scan with the same locking semantics as a transaction running at the REPEATABLE READ isolation level.

SERIALIZABLE
Perform a scan with the same locking semantics as a transaction running at the SERIALIZABLE isolation level. Equivalent to HOLDLOCK.

READPAST
Skip locked rows. This option causes a transaction to skip over rows locked by other transactions that would ordinarily appear in the result set, rather than block the transaction waiting for the other transactions to release their locks on these rows. The READPAST lock hint applies only to transactions operating at READ COMMITTED isolation and will read only past row-level locks. Applies only to the SELECT statement.
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

ROWLOCK
Use row-level locks rather than use the coarser-grained page- and table-level locks.

SELECT au_fname FROM pubs..authors (holdlock)

Sometimes you need a reference to information about locks. Microsoft recommends using the sp_lock or sp_lock2 system stored procedure to report locks information. This very useful procedure returns the information about SQL Server process ID, which lock the data, about locked database, about locked table ID, about locked page and about type of locking (locktype column).
Deadlocks
Deadlock occurs when two users have locks on separate objects and each user wants a lock on the other's object. For example, User1 has a lock on object "A" and wants a lock on object "B" and User2 has a lock on object "B" and wants a lock on object "A". In this case, SQL Server ends a deadlock by choosing the user, who will be a deadlock victim. After that, SQL Server rolls back the breaking user's transaction, sends message number 1205 to notify the user's application about breaking, and then allows the nonbreaking user's process to continue.

You can decide which connection will be the candidate for deadlock victim by using SET DEADLOCK_PRIORITY. In other case, SQL Server selects the deadlock victim by choosing the process that completes the circular chain of locks.

So, in a multiuser situation, your application should check the error 1205 to indicate that the transaction was rolled back, and if it's so, restart the transaction.

Note. To reduce the chance of a deadlock, you should minimize the size of transactions and transaction times.

3. ISOLATION Levels and which is fast? Difference? Is there any new Isolation level introduced in SQL 2005
The isolation level determines how long a read lock is being held (in SQL Server a write lock is always being held to the end of the transaction). The default isolation level in SQL Server is Read Committed: a read operation can only read committed data. If data is being updated while you read, that data is being locked and you won't be able to view the data until the transaction that updates the data has committed (or rolled back).
From a concurrency standpoint this is very good, you are ensured that you can only read correct data. From a throughput perspective it is not that good because your read operation won't return until the write locks has been released.

In SQL Server 2005, two new isolation levels are introduced, both of which use row versioning.

READ_COMMITTED_SNAPSHOT (statement level)
ALLOW_SNAPSHOT_ISOLATION (transaction level)

4. Difference between procedure and function
1. Functions are compiled and executed at run time.
Stored procedures are stored in parsed and compiled format in the database.

2. Functions cannot affect the state of the database which means we cannot perform insert,delete,update and create operations on the database.
Stored Procedures can affect the state of the database by using insert,delete,update and create operations.

3 Functions are basically used to compute values. We passes some parameters to functions as input and then it performs some operations on the parameter and return output.
Stored procedures are basically used to process the task.

4.Function can not change server environment and our operating system environment.
Stored procedures can change server environment and our operating system environment.

5.Functions can not be invoked from SQL Statements. Execute. SELECT
operating system can be invoked from SQL Statements. Execute. SELECT

6.Functions can run an executable file from SQL SELECT or an action query.
operating system use Execute or Exec to run

5. New features in SQL 2008
LINQ (Language Integrated Query)
Enhancements in T-SQL statements
a. Merge Satatement
b. Table valued parameter
c. Data compression
A range of new Datatype
Date and Time, HierarchyId, Spatial Datatypes,,FILESTREAM Datatype,New Security features
6. New features in SQL 2005
Top keyword Enhancements
In SQL Server 2000, you were forced to use a constant value in the TOP clause. With SQL Server 2005 the TOP function now gives an option to use an expression in conjunction with the TOP clause. Now you are no longer have to hardcode the number with the TOP. The TOP clause is also supported in the INSERT, UPDATE, and DELETE statements.
PIVOT and UNPIVOT
There is a million dollar question i.e. How to convert rows into columns? We use all sorts of queries like sub-query, in-line queries with group by to convert rows into columns. PIVOT and UNPIVOT are new features are available in SQL Server 2005’s T-SQL with this you can easily do it.
These operators are mainly useful for OLAP world, where you’re dealing with tabular data rather than relational data and need to produce summary information. The PIVOT operator transforms a set of rows into columns. The UNPIVOT operator reverses the PIVOT operator i.e. transforming the pivoted columns back into rows.
DDL Triggers
In SQL Server 2000 allows triggers to be defined for data manipulation events such as inserting or updating a row. SQL Server 2005 extends this by allowing triggers to be defined on DDL events such as creating and dropping tables, views, procedures and logins. DDL triggers can be associated with CREATE, ALTER, and DROP statements.

This is very good feature to the DBA. Now if anybody issues any DROP statement then DBA has the rights to stop it by defining these kinds of triggers.
Here, you can see how the new DDL trigger can be used to restrict the use of the DROP TABLE and ALTER TABLE statements. If an ALTER TABLE or DROP TABLE statement is issued, the NO_DROP_TABLE trigger will print an error message and rollback the attempted DDL operation.

DML Output
Normally when you issue DELETE statetment it will tell you how many records got updated, but will not tell you which records. With this feature you can easily see the records that are affected with DML operations. Here the OUTPUT DELETED.* clause specifies that log all the deleted records into @temp_ORDER_INFO.
varchar(max) Data Type

This new data type provides an alternative to text/image data type. This is an extension to the varchar, nvarchar and varbinary data types. This data type supports up to 2GB of data.

Hosted CommonLanguage Runtime With SQL Server 2005 developers can create database objects using familiar languages such as Microsoft Visual C# .NET and Microsoft Visual Basic .NET. Developers can also create two new objects—user-defined types and aggregates.
Native XML Support Native XML data can be stored, queried, and indexed in a SQL Server database—allowing developers to build new classes of connected applications around Web services and across any platform or device.
ADO.NET version 2.0 From new support for SQL Types to Multiple Active Result Sets (MARS), ADO.NET in SQL Server 2005 evolves dataset access and manipulation to achieve greater scalability and flexibility.
Security Enhancements The security model in SQL Server 2005 separate users from objects, provides fine-grain access, and enables greater control of data access. Additionally, all system tables are implemented as views, providing more control over database system objects.
Transact-SQL Enhancements SQL Server 2005 provides new language capabilities for developing scalable database applications. These enhancements include error handling, recursive query capabilities, relational operator PIVOT, APPLY, ROW_NUMBER and other row ranking functions, and more.
Reliable Messaging for Asynchronous Applications Service Broker is a robust messaging infrastructure that provides reliable transactional delivery of critical messages between servers—with the scalable high-performance that is expected with asynchronous queuing.
Visual Studio Integration Tight integration with Microsoft Visual Studio and the .NET Framework streamlines development and debugging of data-driven applications. Developers can build database objects, such as stored procedures, using any .NET language and can seamlessly debug across .NET and Transact-SQL (TSQL) languages.
Web Services With SQL Server 2005 developers can develop Web services in the database tier, making SQL Server a hypertext transfer protocol (HTTP) listener and providing a new type of data access capability for Web services-centric applications.
Embedded Reports Use client-side reporting controls to embed real-time reports into an application at design time.
Full-Text Search Enhancements SQL Server 2005 supports rich, full-text search applications. Cataloging capabilities provide greater flexibility over what is cataloged. Query performance and scalability have been improved dramatically, and new management tools provide greater insight into the full-text implementation.
Notification Services and Reporting Services, Service Broker
7. Select the ways to get duplicate values along with the other values- different methods
8. Error handling
9. Index, Clusterd and nonclusterd?
10. How they are storing?Which is fast and why?
11. Is there any new Index type is implemented in SQL 2005 version?
12. Different type of Joins and How it will work
13. Architecture of SQL server
SQL based client server relational database
14. CTE
15. How to rollback all the transactions at a time
SET XACT_ABORT ON - if a Transact-SQL statement raises a run-time error, the entire transaction is terminated and rolled back
16. Explain subQuery and correlated subquey ? Difference between subQuery and correlated subquey ? How it will run (which will execute first)
correlated subquery is a SELECT statement nested inside another T-SQL statement, which contains a reference to one or more columns in the outer query. Therefore, the correlated subquery can be said to be dependent on the outer query. This is the main difference between a correlated subquery and just a plain subquery. A plain subquery is not dependent on the outer query, can be run independently of the outer query, and will return a result set. A correlated subquery, since it is dependent on the outer query will return a syntax errors if it is run by itself.

A correlated subquery will be executed many times while processing the SQL statement that contains the correlated subquery. The correlated subquery will be run once for each candidate row selected by the outer query. The outer query columns, referenced in the correlated subquery, are replaced with values from the candidate row prior to each execution. Depending on the results of the execution of the correlated subquery, it will determine if the row of the outer query is returned in the final result set.
17. DTS package? Your experience?
18. Can we update view’s columns? How to update a view column
Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view SQL Server 2000 supports more complex types of INSERT, UPDATE, and DELETE statements that reference views. INSTEAD OF triggers can be defined on a view to specify the individual updates that must be performed against the base tables to support the INSERT, UPDATE, or DELETE statement. Also, partitioned views support INSERT, UPDATE, and DELETE statements that modify multiple member tables referenced by the view
19. How many Catch we can have in a Try block?
20. How to handle error in SQL 7 and 2000?
21. What is referential integrity?
22. Can we have Primary key and Foreign Key in same table? Example
23. How to pass a tableset from .net to SQL server procedure?