SQL Server 2008 Interview Preparation Guide
Download PDF

SQL Server 2008 frequently Asked Questions in various MS SQL Server 2008 job Interviews by interviewer. The set of SQL Server 2008 interview questions here ensures that you offer a perfect answer to the interview questions posed to you. Get preparation of SQL Server 2008 job interview

27 MS SQL Server 2008 Questions and Answers:

1 :: Explain External Key Management in sql server 2008

A comprehensive solution for encryption and key management is provided by SQL Server 2008. The growing need for greater information security within the data centers of enterprises is managed by security keys. This could be done by excellent support given by SQL Server 2008, by supporting third-party keys management and hardware security module products.

2 :: Explain Declarative Management Framework (DMF) in SQL Server 2008?

Declarative Management Framework is a system for managing the instances of SQL Server 2008. It is a policy based system. The database security can be tighten with polity management, automated administration and explicit administration. A policy can be designed for prohibiting the usage of unauthorized applications and the naming conventions on the database are implied for developers.

Various tasks are planned by many DBAs and backing up databases, reviewing events logs, scanning for improper / unauthorized object creations, long running query SPIDs killing are being performed. Lot of tactical and reactionary items on the list of tasks can keep the conscientious DBA busy.

SQL Server will enforce to define the policies by using Management Studio and select certain servers for enforcing the policy. These can be monitored from SSMS, which acts as a central console.

3 :: Explain Encryption of entire databases without the need for application changes in sql server 2008?

The demands of regulatory compliance and overall data privacy concern are enabled by encryption in organizations. Searching for encrypted data by using fuzzy searches or range includes secure data from unauthorized users, and data encryption. The existing applications need not be changed by using this concept.

4 :: Do you know hot Add CPU in sql server 2008?

CPUs can dynamically be added to a running system, by using Hot Add CPU feature. New hardware can be added physically and online hardware partitioning logically. A virtualization layer can be used to add this feature virtually.

Hot Add CPU feature allows a database for scaling on demand for extending memory resources added online, The CPU resources can be supported for SQL Server 2008 on hardware platforms that supports, without the need of application downtime

The following are the requirements for Hot Add CPU:

1. Hardware which supports Hot Add CPU
2. Windows Server 2008 Datacenter server of 64-bit or Windows Server 2008 Enterprise Edition for the Itanium-Based System operating system.
3. SQL Server Enterprise.

5 :: What is Enhanced database mirroring in sql server 2008?

Data base mirroring in SQL Server 2008 is enhanced by:

Automatic Page Repair: The principal is enabled and mirror computers for recovering transparently from 823 and 824 errors on data pages, with a request for a fresh copy of the page that is corrupted from the mirroring partner.
Improved Performance: The outgoing log stream is compressed by SQL Server 2008 for minimizing the network bandwidth that is required by database mirroring.
Enhanced Supportability: Additional performance counters for enabling more granular accounting of the time, which is spent across the different stages of DBMS log processing. Dynamic Management Views and extensions of the existing views are included, for exposing additional information that is concerned to mirroring sessions.

6 :: Can you explain PowerShell included in SQL Server 2008?

Windows PowerShell is a new and powerful scripting shell which can be used by developers and administrators for automating server administration and deployment of the application. The PowerShell supports complex logic than the Transact-SQL scripts, which provides the SQL Server administrators the power to build robust administration scripts. Other Microsoft products can be administered by PowerShell. In a nut shell, PowerShell is a common scripting language across different servers.

There are two Windows PowerShell snap-ins provided by SQL Server provides:

SQL Server provider: It enables the navigation mechanism similar to the file system paths. Paths that are similar to file system paths can be built. The process is associated with SQL Server management object model and the other nodes are object model classes based.
Set of cmdlets: These are the commands which are utilized in PowerShell scripts for specifying an action of SQL Server. The actions such as running sqlcmd script which contains Transact-SQL or XQuery statements, are supported by cmdlets.

7 :: Tell me Extended events in sql server 2008?

Extended Events are the enhanced event handling system in SQL Server. It is an architecture that is highly scalable, highly configurable. It allows the DBAs for collecting the required information, could it be little or much, for problem identification or problem trouble shooting.

Data collection which can output to an Event Tracing for Windows target is one of the key features of Extended Events. This allows the correlating data with the data collected from the corresponding operating system with the help of Event Tracing for Windows. Even the wait events could be correlated with the kernel EWT data. This process is done within a single view for isolating the waits for a specific reason.

The events are handled by an engine which is a services and objects collection which allows defining, process and managing event sessions, event data and event sessions respectively.

8 :: Do you know spatial data types - geometry and geography in sql server 2008?

Location based data can be seamlessly consumed and extended by the enterprises with the comprehensive support of spatial data types.

Geometry data type: This data type supports a flat 2D surface with points of XY coordinates. The points could be on line string, on lines and also mark the polygon edges. There are certain methods like STintersects, STarea, STDistance and STTouch which uses geometry data types.

Geography data type: The similar methods of geometry data type are used in this data type. But the type reflects on curved 2D surfaces. The ST* functions are utilized but the results in the curvature.

DECLARE @gmtry geometry;
SET @gmtry = geometry::STGeomFromText('POINT (3 4)', 0);
DECLARE @grphy geography;
SET @grphy = geography::STGeomFromText('POINT (3 4)', 4326);
Certain observations need to be considered. They are:

- A polygon is tried which is larger than a hemisphere, an ArgumentException is thrown.
- If the returned result is larger than a hemisphere, then a NULL value is returned.

9 :: Do you know sql server 2008 backup compression?

A compression backup is smaller than uncompressed backup. The backup speed significantly increases because it needs less disk space and I/O operations. The CPU usage is increased and the increased and the additional CPU that is consumed by the process of compression would impact the operations that are running concurrently.

The following processes can be isolated for obtaining a good picture

- Physical disk counters

- Device throughput bytes / second counter of SQL Server Backup Device object

- Backup/Restore throughput / second counter of SQL Server Databases object.

10 :: What is sparse Columns of sql server 2008?

A column with an optimized storage for null values is known as sparse column. Sparse columns reduce the storage space needs for null values. In a scenario of saving 20 percent to 40 percent of storage space, sparse columns can be considered. They are created using CREATE TABLE or ALTER TABLE statements. Sparse columns can be used with

- Column sets: The statements INSERT, DELETE, UPDATE could be referred the sparse columns by name. The same an also be combined into a single XML Column. This is a column set.

- Filtered index: As there are several null value rows in sparse columns, they are appropriate for filtered indexes. The filtered index on a sparse column can accommodate only the rows / tuples which populate values. The index created with filtered index is small and more efficient index.

A large number of user defined properties can be accessed by using sparse columns and filtered indexes enabled applications such as Share Point Services of windows are efficiently store and access.

11 :: Explain filestream storage of sql server 2008?

The complexity of application development and reduces the managing unstructured data cost. The manageability is increased by extending various capabilities which are available only to relational data to non-relational data.

SQL Server 2008 came with 2 new capabilities for persisting BLOB data:

1. FILESTREAM: It is an attribute that can be set on a ‘varbinary’ column for data persistence on the file streams. This enables the benefits from fast streaming capabilities and storage capabilities.

2. Remote BLOB storage: It is a client-side API which reduces the application building complexity and relies on an external persistence for BLOBs and a database for relational data.

SQL Server 2008 will also continue the support for standard BLOB type through the data type ‘varbinary’.

12 :: Tell me TIME data type, datetime2, datetimeoffset data type in sql server 2008?

TIME Data type:

TIME data type of SQL Server 2008 allows to exclusively storing the time.

The following is an example of using TIME data type:

SET @dot = get date()
The above script displays the output as HH:MM:SS.SSSSSSS format. The TIME has the data range from 00:00:00.0000000 through 23:59:59.9999999.

DATETIME2 Data Type:

DATETIME2 is a data type which returns date/time. It provides larger factional seconds and the year compared to DATETIME data type. There are options to specify the number fractions as per the need. The maximum fraction is 7 and the minimum fraction is 0.

The following is an example of using DATETIME2 data type:

DECLARE @dt7 datetime2(7)
SET @dt7 = Getdate()
PRINT @dt7
The above script displays the date as YYYY-MM-DD HH:MM:SS.SSSSSSS format.


To store date, time along with time zone, the DATETIMEOFFSET is used. This is important when dealing with date of several countries with various time zones. The clock is based on 24-hour clock.

The following is an example of using DATETIMEOFFSET data type:

SET @dt = '2007-10-29 22:50:55 -1:00'
SET @dt1 = '2007-10-29 22:50:55 +5:00'

13 :: Do you know the policy based administration feature of SQL Server 2008?

Policy based database administration allows the DBA for managing the instances and objects of SQL Server 2008 across the enterprise by a defined policies that are set. These policies are the rules and regulations which determine the possible ways of what to do and what not to do and the way the violations of policies are enforced and reported. The policies are well defined by using facets and conditions.

Facet: An object which contains the properties which well describes a component.

Condition: A logical expression that is defined on a facet. It is used for identification of acceptable property values of the facet.

14 :: What is Change Data Capture (CDC) feature?

Change Data Capture is a feature that is used for tracking the changes on a table. The process involves in steps.

Step 1 – Creation of a database
The database name is MyDataBase
USE [master]

/*** Object: Database [MyDataBase] ***/

IF EXISTS (SELECT name FROM sys.databases WHERE name = 'MyDataBase')
USE [master]

/*** Object: Database [MyDataBase] ***/
Step 2 - Creation of a table in MyDataBase database
USE [MyDataBase]

/*** Object: Table [dbo].[MyTable] ***/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]')
AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
USE [MyDataBase]

CREATE TABLE [dbo].[MyTable](
[ID] [int] NOT NULL,
[Name] [varchar](100) NULL,

Step 3 - Enabling Change Data Capture feature.
The Transact SQL command enables the Change Data Capture feature.
After enabling the Change Data Capture, a schema along with objects is created.
USE [MyDataBase]
EXEC sys.sp_cdc_enable_db_change_data_capture

Using the cdc the columns, tables, history of ddl commands, index columns and time mappings are created as follows:


15 :: Do you know how to store and query Spatial Data?

Spatial data is stored by using Geometry and Geography data types that are introduced in SQL Server 2008.

Geometry data type is created as follows:
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );

The data into the geometry data column is persisted by using the following INSERT command

INSERT INTO SpatialTable (GeomCol1)
VALUES (geometry::STGeomFromText('LINESTRING (100 100, 20 180, 180 180)', 0));

The data in the geometry data columns is queried by using the following DECLARE and SELECT statements:

DECLARE @geom1 geometry;
DECLARE @geom2 geometry;
DECLARE @result geometry;

SELECT @geom1 = GeomCol1 FROM SpatialTable WHERE id = 1;
SELECT @geom2 = GeomCol1 FROM SpatialTable WHERE id = 2;
SELECT @result = @geom1.STIntersection(@geom2);
SELECT @result.STAsText();

16 :: What is inline variable assignment?

Inline variable assignment in SQL Server 2008 enables to – declare, and assign the variables in a ‘single line’.

The traditional Ex:
DECLARE @myVar int
SET @myVar = 5
need to declare the variable and assigning it to the variable to split into 2 lines and use 2 statements - DECLARE and SET.

In SQL Server 2008, as the name suggests ‘inline’, both declaration and assignment can be given in a single line:

DECLARE @myVar int = 5

17 :: What is Entity Data Services?

SQL Server 2008 allows objects to be created for high level business like Customers, Parts, Inventory. Instead of returning individual rows and tables, these entities could be used. The E-R model will now match with the SQL model.

18 :: What is Resource governor?

Resource Governor enables the DBA for managing the work load of SQL Server and critical system resource consumption. The limits of CPU and memory which are the incoming sessions to the SQL Server will be controlled by Resource Governor.

The various scenarios that occur when sudden spike in CPU and memory utilization that result in slow responses for querying requests. The Resource Governor enables the DBA’s to differentiate the workloads and allocates the shared resources which allow the available CPU and memory resources.

19 :: What is MERGE?

Merge statement allows a single statement for INSERT, DELETE and UPDATE a row that depends on a condition. The target table for certain operations is based on the results of join with a source table. The following example illustrates the use of MERGE.

MERGE InventoryMaster AS invmstr
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON invmstr. InventoryID = src. InventoryID
UPDATE SET invmstr.Descr = src.Descr

INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);.

20 :: Explain filtered indexes?

Filtered index in SQL Server 2008 is an index WHERE clause. A filtered index is an optimized non-clustered index. It allows for defining the filter predicate with WHERE clause at the time of creating index. The rows from the filtered index are available in B-Tree will contain the rows which only satisfy the criteria of the filter while creating the index.