Uses of XPath language

XPath is a language defined by the W3C. It used to select nodes from XML documents. 


Microsoft SQL Server 2000 supports XPath language that helps to retrieve data from XML documents. 


Microsoft SQL server uses a mapping schema to define an XML based view of the data in one or more SQL Server tables and views. The mapping schema is then used to retrieve data.

Constraints for Designing of Files and File groups in Microsoft SQL Server

There are some constraints for designing files and file group in Microsoft SQL server. These constraints are following :


A file or file group can only be used by one database. For example, the files abcd.mdf and abcd.ndf contains data and objects for abc database, cannot be used by any other database.

A file can be a member of only one file group.


Data and transaction log information cannot be part of the same file or file group.

Transaction log files can't be part of a file group.

Types of Authentication Modes in Microsoft SQL Server

There are two types of Authentication modes in Microsoft SQL Server. These are following :

• Windows Authentication
• Mixed mode

Windows authentication mode

Windows authentication mode allows users to connect using Windows domain like NT/2000 account. The access to the Microsoft SQL server is controlled by Windows NT/2000 account or group used when logged in, means that Windows username and password are used to access the database on Microsoft SQL Server server.

Mixed mode authentication

Mixed mode authentication allows use of Windows credentials and the local Microsoft SQL server account to logged into Database.

DDL DCL and DML in Microsoft SQL Server

Data definition language

Data definition language (DDL) is used to define and manage all attributes and properties of a database in Microsoft SQL Server. DDL statements include following commands :

• CREATE
• ALTER
• DROP

Data Control Language

Data control language (DCL) is used to control permissions on database objects. Permissions are controlled by using following commands :

• GRANT
• REVOKE
• DENY

Data Manipulation Language

Data manipulation language (DML) is used to select, insert, update, and delete data in the objects defined with Data Definition Language.

Why Avoid Permitting NULL values in Microsoft SQL Server

NULL value in column in Microsoft SQL Server means that no entry has been made into the column. It states that the corresponding value is either unknown or undefined. It is also different from zero or "". 

We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Log Shipping in Microsoft SQL Server

Log shipping in Microsoft SQL Server is process used to automatically taking backup of the database and transaction files from one Microsoft SQL Server and then restoring them on a backup server. 

Log shipping keeps the two Microsoft SQL Server instances in sync with each other. If production server fails, then users simply need to be pointed to the backup server. 

Log shipping process consists of following three steps :

1. Backup transaction logs of the Production server.
2. Copy these logs on the backup server.
3. Restore the log on backup server.

Types of Temporary Table in Microsoft SQL Server

There are two types of temporary tables in Microsoft SQL Server. These are: 

1. Local temporary table
2. Global temporary table. 

Local Temporary Table

Local temporary table is available only to the current Db connection for current user and are cleared when connection is closed. Local temporary table can not be shared among multiple users.

Global Temporary Table

Global temporary table is available to any connection once created. They are cleared when the last connection is closed. Global temporary table can be shared by multiple user sessions.

Temporary tables are used to allow short term use of data in Microsoft SQL Server.

Microsoft SQL Server 2008 PowerShell

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

Below are two Windows PowerShell snap-ins provided by Microsoft 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 Microsoft 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 Microsoft SQL Server. The actions such as running sqlcmd script which contains Transact-SQL or XQuery statements, are supported by cmdlets.

Microsoft SQL Server 2008 Declarative Management Framework

Declarative Management Framework is a system for managing the instances of Microsoft 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.

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

Microsoft SQL Server 2008 Extended Events

Extended Events are the enhanced event handling system in Microsoft 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.

Microsoft SQL Server 2008 Filestream Storage

The complexity of application development 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.

There are 2 new capabilities in Microsoft SQL Server 2008 for persisting BLOB data.

1. FILESTREAM
2. Remote BLOB storage

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.

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

Compression Backup in Microsoft SQL Server 2008

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.

1. Physical disk counters
2. Device throughput bytes / second counter of SQL Server Backup Device object
3. Backup/Restore throughput / second counter of SQL Server Databases object.

Microsoft SQL Server 2008 Policy Based Administration Feature

Policy based database administration allows the DBA for managing the instances and objects of Microsoft 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. The details of facets and conditions are below.

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.

Use 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 and Filtered index.

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.

Store and Query Spatial Data in Microsoft SQL Server 2008

Store Data

Spatial data is stored by using Geometry and Geography data types that are introduced in Microsoft SQL Server 2008. Geometry data type can be created as follows :

CREATE TABLE SpatialTable
( id int IDENTITY (1,1),
GeomCol1 geometry,
GeomCol2 AS GeomCol1.STAsText() );
GO

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));

Query Data

The data in the geometry data columns is queried by using the following Commands.

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();

MERGE Statement in Microsoft SQL Server 2008

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. 

For example

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

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

Database Mirroring Enhanced in Microsoft SQL Server 2008

Data base mirroring in Microsoft SQL Server 2008 is enhanced by following ways :

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

Encryption and External Key Management in Microsoft SQL Server 2008

A comprehensive solution for encryption and key management is provided by Microsoft 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 Microsoft SQL Server 2008, by supporting third-party keys management and hardware security module products.

Plan Freezing in Microsoft SQL Server 2008

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.

Role of Resource Governor in Microsoft SQL Server 2008

Resource Governor enables the DBA for managing the work load of Microsoft 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.