SQL server 2008

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.

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.

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.

Ex: 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.

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 SalesHistory table. A list of available fields are available for UPDATE statement. Not only for UPDATE also other statements.

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.

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.

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

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

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.

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.

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:

DECLARE @dot as TIME

SET @dot = get date()

PRINT @dt

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.

DATETIMEOFFSET Data type:

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:

DECLARE @dt DATETIMEOFFSET(0)

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

DECLARE @dt1 DATETIMEOFFSET(0)

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

SELECT DATEDIFF(hh,@dt,@Dt1)