SQL Server 2008 Interview Questions and Answers

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

Compound operators feature is enhanced in SQL Server 2008. They are like compound operators in languages like C, C++ etc.

Compound operators are a combination of operator with another operator.

The compound assignment operators are – arithmetic operators – +=,-=,*=, /=, %=, bitwise operators -&=, ^=,|=

Declare @number int

Set @number = 10

–using Compound assignment operator

Set @number+=200

Select @number as MyResult

Go

Automatic auditing is a true auditing solution for enterprise customers. STL Trace can be used for satisfying several auditing needs. SQL Server Auditing feature offers a number of advantages that attracts and help DBAs with much more easily achievable goals. These include meeting regulatory compliance requirements. These include,

• Provision for centralized storage of audit logs and integration with system center
• Better performance that is noticeable
• Permits free-grained auditing in which an audit can be targeted for some specific actions by a principle against a particular object.
• Allows the objects of audit to be treated as first class database objects, which mean Transact-SQL DDL statements can create these objects.
• The database object is controlled by database engine’s permission model and enforcement control.
• A new level permission is featured in SQL Audit – ALTER ANY SERVER AUDIT- which allows a principle to CREATE, ALTER and DROP an Audit Specification object.
• A database level permission – ALTER ANY DATABASE AUDIT – is introduced to allow a principle to CREATE, ALTER and DROP a Database specification object.

Data compression is a feature which is used to reduce disk storage space and increase the performance of the query by reducing the I/O operations.

SQL Server 2008 supports two types of compression – Row-level compression and Page-level compression.

A row-level and page-level compression takes place on the metadata.

Page level compression results in persisting certain common data that affects rows in a single location.

The compression takes place into number of bits. For example, the length of ‘varchar’ will be stored in 3 bits.

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.

The benefits of Filtered indexes are:

• Has only that row which satisfies the filter criteria defined. This results in reducing the storage space need for the index.
• The filtered index statistics are accurate and more compact. The reason is they consider only the tuples / rows in the filtered index and it reduces the cost of overhead of updating the statistics.
• The data modification impact is less by using filtered index. Because it is updated only at the time where the data of the index is impacted.
• The cost of maintenance will be reduced, because only the subset of rows will be considered which rebuilding the index.

Example:

CREATE NONCLUSTERED INDEX FI_Employee_DOJ ON Employee(DOJ)

WHERE DOJ IS NOT NULL

In the above example the NOT NULL is the filtered criteria for the index. Employee is the table and DOJ is the column name.

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.

Plan freezing is a new concept that allows the DBAs to persist plan guides. These guides could be reverted to when the queries either fail or drain the resources after the upgrade. The stability to queries is achieved by Plan Freezing. Several monitoring features for checking when the query is succeeded or failed are included with Plan Freezing.

A user defined tables are allowed between queries using the Table Value Parameters feature of SQL Server 2008. It also supports defining the tables between a client and a server. Querying, joining, inserting values, updating values etc., can be done as is being done with a normal table. Instead of a query taking a long list of parameters, they simple take TVP as a parameter.

For creating TVP, one need to define a user defined type and the columns which the TVP would hold. The following example creates a customer type which holds an id and name.

CREATE TYPE Customer AS TABLE (id int, CustomerName nvarchar(50))

A dummy table is created for persisting the information.

CREATE TABLE Customers (id int, CustomerName nvarchar(50)) GO

A procedure can also be created which takes a single parameter as a Table Value Parameter. Data can be inserted into two different tables, however from the outside it is a single object and only a single stored procedure is being called.

CREATE Procedure AddCustomers(@customer Customer READONLY) AS

INSERT INTO Customers SELECT id, CustomerName FROM @customer

GO

The TVP as parameter must have the READONLY attribute and TVPs are basically temporary tables persisted on the server in tempdb.

Prior to SQL Server 2008, IntelliSense was available from third-party products. Red-Gate has a pretty good IntelliSense product.

IntelliSense of SQL Server 2008 is ON by default, and can be turn it OFF by using Tools->Options from Management Studio.

The process involves in creating a table object like the following:

IF OBJECT_ID(‘SalesHistory’)>0

DROP TABLE SalesHistory;

GO

CREATE TABLE [dbo].[SalesHistory]

(

[SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,

[Product] [char](150) NULL,

[SaleDate] [date] NULL,

[SalePrice] [money] NULL

)

GO

Only the table exists. Use DML statements like SELECT, INSERT against SalesHistory table. A dropdown list of the fields available would be chosen from the table. This helps in knowing the information about columns easier.

DML statements can also be used with IntelliSense. For example, type UPDATE statement against the Sales History table. A list of available fields is available for UPDATE statement. Not only for UPDATE also other statements.

Windows Power Shell 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 Power Shell 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 Power Shell. In a nut shell, Power Shell is a common scripting language across different servers.

There are two Windows Power Shell 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 camlets: These are the commands which are utilized in Power Shell 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.

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 tightening 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.

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.

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.

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

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

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.