Best Sql Server Interview Questions 2000/2005/2008 : Part 3

Part 1
Part 2

1.      Why can there be only one Clustered Index and not more than one?

Cluster Index physically stores data, or arranges data in one order (depends on which column(s) you have defined Clustered index and in which order).

As a fact, we all know that a set of data can be only stored in only one order; that is why only one clustered index is possible

2.      How to Create Primary Key with Specific Name while Creating a Table?

CREATE TABLE [dbo].[TestTable](

[ID] [int] IDENTITY(1,1)NOT NULL,

[FirstName] [varchar](100)NULL,

CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED

([ID] ASC))

GO

3.      How to Enable/Disable Indexes?

--Disable Index
ALTER INDEX [IndexName] ON TableName DISABLE
GO
--Enable Index
ALTER INDEX [IndexName] ON TableName REBUILD
GO

4.      What is T-SQL Script to Take Database Offline – Take Database Online?

-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO

5.      Can we Insert Data if Clustered Index is Disabled?

No, we cannot insert data if Clustered Index is disabled because Clustered Indexes are in fact original tables which are physically ordered according to one or more keys (Columns).

6.      How to Recompile Stored Procedure at Run Time?

We can Recompile Stored Procedure in two ways.

Option 1:

CREATE PROCEDURE dbo.PersonAge(@MinAge INT, @MaxAge INT)
WITH RECOMPILE
AS
SELECT*
FROM dbo.tblPerson
WHERE Age <= @MinAge AND Age >= @MaxAge
GO

Option 2:

EXEC dbo.PersonAge65, 70 WITH RECOMPILE

We can use RECOMPILE hint with a query and recompile only that particular query. However, if the parameters are used in many statements in the stored procedure and we want to recompile all the statements, then instead of using the RECOMPILE option with all the queries, we have one better option that uses WITH RECOMPILE during stored procedure creation or execution.

This method is not recommended for large stored procedures because the recompilation of so many statements may outweigh the benefit of a better execution plan. 

7.      What is the Maximum Number of Index per Table?

For SQL Server 2005:

1 Clustered Index + 249 Nonclustered Index = 250 Index.

For SQL Server 2008:

1 Clustered Index + 999 Nonclustered Index = 1000 Index.

8.      Where are SQL server Usernames and Passwords Stored in the SQL server?

They get stored in System Catalog Views, sys.server_principals and sys.sql_logins. However, you will not find password stored in plain text.

9.      What does TOP Operator Do?

The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETES statements.

10.  What is CTE?

CTE is the abbreviation for Common Table Expression. A CTE is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

11.  What are the Advantages of Using CTE?

• Using CTE improves the readability and enables easy maintenance of complex queries.
• The query can be divided into separate, simple, and logical building blocks, which can be then used to build more complex CTEs until the final result set is generated.
• CTE can be defined in functions, stored procedures, triggers or even views.
• After a CTE is defined, it can be used as a Table or a View and can SELECT, INSERT, UPDATE or DELETE Data.

12.  What is MERGE Statement?

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement, we can include the logic of such data modifications in one statement that even checks when the data is matched, then just update it, and when unmatched, insert it.

One of the most important advantages of MERGE statement is all the data is read and processed only once.

In previous versions three different statement has to be written to process three different activity (INSERT, UPDATE or DELETE), however using MERGE statement all update activity can be done in one pass of database table. This is quite an improvement in performance of database query.

Syntax of MERGE statement is as following:
MERGE
[ TOP ( expression ) [ PERCENT ] ]
[ INTO ] target_table [ WITH ( <merge_hint> ) ] [ [ AS ] table_alias]
USING <table_source>
ON <merge_search_condition>
[ WHEN MATCHED [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ WHEN NOT MATCHED [ BY TARGET ] [ AND <clause_search_condition> ]
THEN <merge_not_matched> ]
[ WHEN NOT MATCHED BY SOURCE [ AND <clause_search_condition> ]
THEN <merge_matched> ]
[ <output_clause> ]
[ OPTION ( <query_hint> [ ,...n ] ) ]
;

13.  What is Filtered Index?

Filtered Index is used to index a portion of rows in a table that means it applies filter on INDEX which improves query performance, reduces index maintenance costs, and reduces index storage costs when compared with full-table indexes. When we see an Index created with a WHERE clause, then that is actually a FILTERED INDEX.

14.  How can we Rewrite Sub-Queries into Simple Select Statements or with Joins?

Yes. We can rewrite sub-queries using the Common Table Expression (CTE). A Common Table Expression (CTE) is an expression that can be thought of as a temporary result set which is defined within the execution of a single SQL statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query.

e.g.
USE AdventureWorks
GO
WITH EmployeeDepartment_CTE AS (
SELECT EmployeeID,DepartmentID,ShiftID
FROM HumanResources.EmployeeDepartmentHistory
)
SELECT ecte.EmployeeId,ed.DepartmentID, ed.Name,ecte.ShiftID
FROM HumanResources.Department ed
INNER JOIN EmployeeDepartment_CTE ecte ON ecte.DepartmentID = ed.DepartmentID
GO

15.  What is CLR?

In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers. You can develop such CLR add-ons to SQL Server using Visual Studio 2008. 

16.  What are Synonyms?

Synonyms give you the ability to provide alternate names for database objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with three and four part names; for example, shortening server.database.owner.object to object. 

17.  What is LINQ?

Language Integrated Query (LINQ) adds the ability to query objects using .NET languages. The LINQ to SQL object/relational mapping (O/RM) framework provides the following basic features:

Tools to create classes (usually called entities) mapped to database tables

Compatibility with LINQ’s standard query operations

The DataContext class with features such as entity record monitoring, automatic SQL statement generation, record concurrency detection, and much more

18.  What is Use of EXCEPT Clause?

EXCEPT clause is similar to MINUS operation in Oracle. The EXCEPT query and MINUS query return all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.

19.  What is XPath?

XPath uses a set of expressions to select nodes to be processed. The most common expression that you’ll use is the location path expression, which returns back a set of nodes called a node set. XPath can use both an unabbreviated and abbreviated syntax. The following is the unabbreviated syntax for a location path:

/axisName::nodeTest[predicate]/axisName::nodeTest[predicate]

20.  What is NOLOCK?

Using the NOLOCK query optimizer hint is generally considered a good practice in order to improve concurrency on a busy system. When the NOLOCK hint is included in a SELECT statement, no locks are taken on data when data is read. The result is a Dirty Read, which means that another process could be updating the data at the exact time you are reading it. There are no guarantees that your query will retrieve the most recent data. The advantage to performance is that your reading of data will not block updates from taking place, and updates will not block your reading of data. SELECT statements take Shared (Read) locks. This means that multiple SELECT statements are allowed simultaneous access, but other processes are blocked from modifying the data. The updates will queue until all the reads have completed, and reads requested after the update will wait for the updates to complete. The result to your system is delay (blocking).

21.  What is the Difference between Update Lock and Exclusive Lock?

When Exclusive Lock is on any process, no other lock can be placed on that row or table. Every other process has to wait till Exclusive Lock completes its tasks.

Update Lock is a type of Exclusive Lock, except that it can be placed on the row which already has Shared Lock on it. Update Lock reads the data of the row which has the Shared Lock as soon as the Update Lock is ready to change the data it converts itself to the Exclusive Lock.

22.  How will you Handle Error in SQL SERVER 2008?

SQL Server now supports the use of TRY…CATCH constructs for providing rich error handling. TRY…CATCH lets us build error handling at the level we need, in the way we need to by setting a region where if any error occurs, it will break out of the region and head to an error handler. The basic structure is as follows:

BEGIN TRY

<code>

END TRY

BEGIN CATCH

<code>

END CATCH

So if any error occurs in the TRY block, then execution is diverted to the CATCH block, and the error can be resolved.

23.  What is the XML Datatype?

The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that has a missing single top-level element. You can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of SQL Server.

24.  How can I Track the Changes or Identify the Latest Insert-Update-Delete from a Table?

In SQL Server 2005 and earlier versions, there is no inbuilt functionality to know which row was recently changed and what the changes were. However, in SQL Server 2008, a new feature known as Change Data Capture (CDC) has been introduced to capture the changed data.

25.  How to Find Tables without Indexes?

Run the following query in the Query Editor.

USE <database_name>;
GO
SELECT SCHEMA_NAME(schema_id) AS schema_name
,name AS table_name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'IsIndexed') = 0
ORDER BY schema_name, table_name;
GO

1.      What is Difference between GETDATE and SYSDATETIME in SQL Server 2008?

In case of GETDATE, the precision is till milliseconds, and in case of SYSDATETIME, the precision is till nanoseconds

2.      What is the ‘FILLFACTOR’?

A “FILLFACTOR” is one of the important arguments that can be used while creating an index.

According to MSDN, FILLFACTOR specifies a percentage that indicates how much the Database Engine should fill each index page during index creation or rebuild. Fill-factor is always an integer valued from 1 to 100. The fill-factor option is designed for improving index performance and data storage. By setting the fill-factor value, you specify the percentage of space on each page to be filled with data, reserving free space on each page for future table growth.

Specifying a fill-factor value of 70 would imply that 30 percent of each page will be left empty, providing space for index expansion as data is added to the underlying table. The fill-factor setting applies only when the index is created or rebuilt.

3.      What are Various Limitations of the Views?

• ORDER BY clause does not work in View. (works with Top keyword)

• Regular queries or Stored Procedures give us flexibility when we need another column; we can add a column to regular queries right away. If we want to do the same with Views, then we will have to modify them first.

• Index created on view not used often.

• Once the view is created and if the basic table has any column added or removed, it is not usually reflected in the view till it is refreshed.

• One of the most prominent limitations of the View it is that it does not support COUNT (*); however, it can support COUNT_BIG (*).

• UNION Operation is now allowed in Indexed View.

• We cannot create an Index on a nested View situation means we cannot create index on a view which is built from another view.

• Outer Join, SELF JOIN Not Allowed in Indexed View.

• Cross Database Queries Not Allowed in Indexed View.

4.      What is a Covered index?

It is an index that can satisfy a query just by its index keys without having needed to touch the data pages.

It means that when a query is fired, SQL Server doesn’t need to go to the table to retrieve the rows, but can produce the results directly from the index as the index covers all the columns used in query.

5.      When I delete any Data from a Table, does the SQL Server reduce the size of that table?

When data are deleted from any table, the SQL Server does not reduce the size of the table right away; however, it marks those pages as free pages, showing that they belong to the table. When new data are inserted, they are put into those pages first. Once those pages are filled up, SQL Server will allocate new pages. If you wait for sometime, the background process de-allocates the pages, finally reducing the page size.

6.      What are different transaction levels in SQL SERVER?

Transaction Isolation level decides how is one process isolated from other process.

Using transaction levels, you can implement locking in SQL SERVER.

There are four transaction levels in SQL SERVER:-

READ COMMITTED:

The shared lock is held for the duration of the transaction, meaning that no other transactions can change the data at the same time. Other transactions can insert and modify data in the same table, however, as long as it is not locked by the first transaction.

READ UNCOMMITTED:

No shared locks and no exclusive locks are honored. This is the least restrictive isolation level resulting in the best concurrency but the least data integrity.

REPEATABLE READ:

This setting disallows dirty and non-repeatable reads. However, even though the locks are held on read data, new rows can still be inserted in the table, and will subsequently be interpreted by the transaction.

SERIALIZABLE:

This is the most restrictive setting holding shared locks on the range of data. This setting does not allow the insertion of new rows in the range that is locked; therefore, no phantoms are allowed.

Following is the syntax for setting transaction level in SQL SERVER.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

7.      What is LOCK escalation?

Lock escalation is the process of converting of low-level locks (like rowlocks, page locks) into higher-level locks (like table locks). Every lock is a memory structure too many locks would mean, more memory being occupied by locks. To prevent this from happening, SQL Server escalates the many fine-grain locks to fewer coarse-grain locks. Lock escalation threshold was definable in SQL Server 6.5, but from SQL Server 7.0 onwards SQL Server dynamically manages it.

8.      What are the different ways of moving data between databases in SQL Server?

There are lots of options available; you have to choose your option depending upon your requirements. Some of the options you have are BACKUP/RESTORE, detaching and attaching databases, replication, DTS, BCP, logshipping, INSERT...SELECT, SELECT...INTO, creating INSERT scripts to generate data.

9.      What is the purpose of Replication?

Replication is way of keeping data synchronized in multiple databases. SQL server replication has two important aspects publisher and subscriber.

Publisher

Database server that makes data available for replication is known as Publisher.

Subscriber

Database Servers that get data from the publishers is called as Subscribers.

10.  What are the different types of replication supported by SQL SERVER?

There are three types of replication supported by SQL SERVER:-

Snapshot Replication:

Snapshot Replication takes snapshot of one database and moves it to the other database. After initial load data can be refreshed periodically. The only disadvantage of this type of replication is that all data has to be copied each time the table is refreshed.

Transactional Replication:

In transactional replication, data is copied first time as in snapshot replication, but later only the transactions are synchronized rather than replicating the whole database. You can either specify to run continuously or on periodic basis.

Merge Replication:

Merge replication combines data from multiple sources into a single central database. Again as usual, the initial load is like snapshot 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.

11.  What are Ranking Functions?

Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic. The different Ranking functions are as follows:

ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.

RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.

DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.