Top 10 Developer Features in SQL Server 2008

Article Copied From: http://www.databasejournal.com/features/mssql/article.php/3897251/article.htm

Many new developer features were introduced in SQL Server2008 database to facilitate robust database development. SQL server 2008 improves developer productivity by providing seamless integration between frameworks, data connectivity technologies, programming languages, Web services, development tools and data. This article discusses the new top 10 developer features introduced in SQL server 2008.

Feature -1 Change's in the DATE and TIME DataTypes

In SQL Server 2005, there were DATETIME or SMALLDATETIME data types to store datetime values but there was no specific datatype to store date or time value only. In addition, search functionality doesn't work on DATETIME or SMALLDATETIME fields if you only specify a data value in the where clause. For example the following SQL query will not work in SQL server 2005 as you have only specified the date value in the where clause.

SELECT * FROM tblMyDate Where [MyDateTime] = '2010-12-11'

To make it work you need to specify both date and time component in the where clause.

SELECT * FROM tblMyDate Where [MyDateTime] = '2010-12-11 11:00 PM'

With introduction of DATE datatype the above problem is resolved in SQL Server 2008. See the following example.

DECLARE @mydate as DATE

SET @ mydate = getdate()

PRINT @dt

The output from the above SQL query is the present date only (2010-12-11), no time component is added with the output.

TIME datatype is also introduced in SQL server 2008. See the following query using TIME datatype.

DECLARE @mytime as TIME

SET @mytime = getdate ()

PRINT @mytime

The output of the above SQL script is a time only value. The range for the TIME datatype is 00:00:00.0000000 through 23:59:59.9999999.

SQL server 2008 also introduced a new datatype called DATETIME2. In this datatype, you will have an option to specify the number of fractions (minimum 0 and maximum 7). the following example shows how to use DATETIME2 datatype.

DECLARE @mydate7 DATETIME2 (7)

SET @mydate7 = Getdate()

PRINT @mydate7

The result of above script is 2010-12-11 22:11:19.7030000.

The new DATETIMEOFFSET datatype, which indicates what time zone that date and time belong to was also introduced in SQL Server 2008. This datatype will be required when you are keeping the date time value of different countries with different time zones in SQL Server. The following example shows the usage of the DATETIMEOFFSET datatype.

DECLARE @mydatetime DATETIMEOFFSET(0)

DECLARE @mydatetime1 DATETIMEOFFSET(0)

SET @ mydatetime = '2010-12-11 21:53:56 +5:00'

SET @ mydatetime1 = '2010-12-11 21:53:56 +10:00'

SELECT DATEDIFF(hh,@mydatetime1,@mydatetime)

Feature -2 New Date and Time functions

In SQL Server 2005 and SQL Server 2000 there are few functions to retrieve the current date and time. Adding to that In SQL Server 2008, five new functions were introduced: SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME SWITCHOFFSET and TODATETIMEOFFSET. The SYSDATETIME function returns the present system timestamp without the time zone, with an accuracy of 10 milliseconds. The SYSDATETIMEOFFSET function works like SYSDATETIME but the only difference is it includes the time zone.

SYSUTCDATETIME returns the Universal Coordinated Time that is known as Greenwich Mean Time within an accuracy of 10 milliseconds.

Select SYSUTCDATETIME () will show output '2010-12-11 21:53:05.7131792'

SWITCHOFFSET returns a datetimeoffset value that is changed from the stored time zone offset to a specified new time zone offset. See the following examples.

SELECT SYSDATETIMEOFFSET() GetCurrentOffSet;

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '-04:00') 'GetCurrentOffSet-4';

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET (), '+00:00') 'GetCurrentOffSet+0';

Feature -3 Sparse columns

Sparse column, which optimizes storage for null values, is introduced in SQL Server 2008. When a column value contains a substantial number of null values, defining the column as sparse saves a significant amount of disk space. In fact, null value in a sparse column doesn't take any space.

If you decide to implement a sparse column, it must be nullable and cannot be configured with the ROWGUIDCOL or IDENTITY properties, cannot include a default and cannot be bound to a rule. In addition, you cannot define a column as sparse if it is configured with certain datatypes, such as TEXT, IMAGE, or TIMESTAMP. T following SQL script shows how to create a table with sparse column.

Create table mysparsedtable

(

column1 int primary key,

column2 int sparse,

column3 int sparse,

column4 xml column_set for all_sparse_columns

)

Feature -4 Large UDTs in SQL server 2008 (ADO.NET)

User defined types (UDT's) were first introduced with SQL server 2005 but were restricted to a maximum size of 8 kilobytes. In SQL Server 2008, this restriction has been removed. Using Common Language Runtime (CLR), now SQL Server 2008 supports binary data that's up to 2GB in size. The following C# code snipped shows how to retrieve large UDT data from a SQL server 2008 database.

SqlConnection myconnection = new SqlConnection(myconnectionString, mycommandString); // myconnectionString and

// mycommandString must be declared

myconnection.Open();

SqlCommand mycommand = new SqlCommand(mycommandString);

SqlDataReader myreader = mycommand.ExecuteReader();

while (myreader.Read())

{

   int id = myreader.GetInt32(0);

   LargeUDT myudt = (LargeUDT)myreader[1];

   Console.WriteLine("ID={0} LargeUDT={1}", id, myudt);

}

myreader.closeO

Feature -5 Passing tables to functions or procedures using new Table-Value parameters

SQL Server 2008 introduces a new feature to pass a table datatype into stored procedures and functions. The table parameter feature greatly helps to reduce the development time because developers no longer need to worry about constructing and parsing long XML data. Using this feature, you can also allow the client-side developers (using .NET code) to pass data tables from client-side code to the database. The following example shows how to use Table-Value parameter in stored procedures.

In the first step, I have created a Student table using following script.

GO

CREATE TABLE [dbo].[TblStudent]

(

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

   [StudentName] [varchar](30) NOT NULL,

   [RollNo] [int] NOT NULL,

   [Class] [varchar](10) NOT NULL

)

GO

Next, I have created table datatype for Student table.

GO

CREATE TYPE TblStudentTableType AS TABLE

  (

    [StudentName] [varchar](30) NOT NULL,

    [RollNo] [int] NOT NULL,

    [Class] [varchar](10) NOT NULL

  )

GO

Then I have created a stored procedure with table datatype as an input parameter and to insert data in Student table.

GO

CREATE PROCEDURE sp_InsertStudent

  (

    @TableVariable TblStudentTableType READONLY

  )

AS

BEGIN

    INSERT INTO [TblStudent]

    (

       [StudentName] , [RollNo] , [Class]

    )

    SELECT

       StudentName , RollNo , Class FROM @TableVariable WHERE StudentName = 'Tapas Pal'

END

GO

In the last step, I have entered one sample student record in the table variable and executed the stored procedure to enter a sample record in the TblStudent table.

DECLARE @DataTable AS TblStudentTableType

INSERT INTO @DataTable(StudentName , RollNo , Class)

VALUES ('Tapas Pal','1', 'Xii')

EXECUTE sp_InsertStudent

@TableVariable = @DataTable

Feature -6 New MERGE command for INSERT, UPDATE and DELETE operations

SQL server 2008 provides the MERGE command that is an efficient way to perform multiple DML (Data Manipulation Language) operations at the same time. In SQL server 2000 and 2005, we had to write separate SQL statements for INSERT, UPDATE, or DELETE data based on certain conditions, but in SQL server 2008, using the MERGE statement we can include the logic of similar data modifications in one statement based on where condition match and mismatch. In the following example, I have created two tables (TblStudent and TblStudentMarks) and inserted sample data to show how MERGE command works.

GO

  CREATE TABLE [dbo].[TblStudent]

  (

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

      [StudentName] [varchar](30) NOT NULL,

      [RollNo] [int] NOT NULL,

      [Class] [varchar](10) NOT NULL

  )

GO

CREATE TABLE TblStudentMarks

(

StudentID INTEGER REFERENCES TblStudent,

StudentMarks INTEGER

)

GO

INSERT INTO TblStudent VALUES('Tapas', '1', 'Xii')

INSERT INTO TblStudent VALUES('Vinod', '2', 'Xiv')

INSERT INTO TblStudent VALUES('Tamal', '3', 'Xii')

INSERT INTO TblStudent VALUES('Tapan', '4', 'Xiii')

INSERT INTO TblStudent VALUES('Debabrata', '5', 'Xv')

INSERT INTO TblStudentMarks VALUES(1,230)

INSERT INTO TblStudentMarks VALUES(2,280)

INSERT INTO TblStudentMarks VALUES(3,270)

INSERT INTO TblStudentMarks VALUES(4,290)

INSERT INTO TblStudentMarks VALUES(5,240)

Now to perform the following operations, I have written a single SQL statement.

1.    Delete Record with student name 'Tapas'

2.    Update Marks and Set to 260 if Marks is <= 230

3.    Insert a record in TblStudentMarks table if the record doesn't exist

MERGE TblStudentMarks AS stm

USING (SELECT StudentID,StudentName FROM TblStudent) AS sd

ON stm.StudentID = sd.StudentID

WHEN MATCHED AND sd.StudentName = 'Tapas' THEN DELETE

WHEN MATCHED AND stm.StudentMarks <= 230 THEN UPDATE SET stm.StudentMarks = 260

WHEN NOT MATCHED THEN

INSERT(StudentID,StudentMarks)

VALUES(sd.StudentID,25);

GO

Feature -7 New HierarchyID datatype

SQL server 2008 provides new HierarchyID data type that allows database developers to construct relationships among data elements (columns) within a table. HierarchyID data type has a set of methods that provide tree like functionality. These methods are GetAncestor, GetDescendant, GetLevel, GetRoot, IsDescendant, Parse, Read, Reparent, ToString, Write etc. The following example shows how to create a HIERARCHYID column in a table.

CREATE TABLE dbo.ProductCategory

(

  ProductSubCategoryID IDENTITY(1,1) NOT NULL ,

  ProductCategoryID NOT NULL,

  lvl AS hid.GetLevel() PERSISTED,

  ProductSubCatName VARCHAR(25) NOT NULL,

  ProductSubCatDesc VARCHAR(250)NOT NULL

)

CREATE UNIQUE CLUSTERED INDEX idx_first ON dbo.Employees( ProductSubCategoryID);

CREATE UNIQUE INDEX idx_second ON dbo.Employees(lvl, ProductCategoryID);

Feature -8 Spatial datatypes

Spatial is the new data type introduced in SQL server 2008 that is used to represent the physical location and shape of any geometric object. Using spatial data types you can represent countries, roads etc. Spatial data type in SQL server 2008 is implemented as .NET Common Language Runtime (CLR) data type. There are two types of spatial data type's available, geometry and geography data type. Let me show you example of a geometric object.

DECLARE @point geometry;

SET @point = geometry::STGeomFromText ('POINT (4 9)', 0);

SELECT @point.STX; -- Will show output 4

SELECT @point.STY; -- Will show output 5

You can use methods STLength, STStartPoint, STEndPoint, STPointN, STNumPoints, STIsSimple, STIsClosed and STIsRing with geometric objects.

Feature -9 Manage your files and documents efficiently by implanting FILESTREAM datatype

SQL server 2000 and 2005 do not provide much for storing videos, graphic files, word documents, excelspreadsheets and other unstructured data. In SQL Server 2005 you can store unstructured data in VARBINARY (MAX) columns but the maximum limit is 2 GB. To resolve the unstructured files storing issue, SQL Server 2008 has introduced the FILESTREAM storage option. The FILESTREAM storage is implemented in SQL Server 2008 by storing VARBINARY (MAX) binary large objects (BLOBs) outside the database and in the NTFS file system. Before implementing FILESTREAM storage, you need to perform following steps.

1. Enable your SQL Server database instance to use FILESTREAM (enable it using the sp_filestream_configure stored procedure. sp_filestream_configure @enable_level = 3)

2. Enable your SQL Server database to use FILESTREAM

3. Create "VARBINARY (MAX) FILESTREAM" datatype column in your database

Feature -10 Faster queries and reporting with grouping sets

SQL Server 2008 implements grouping set, an extension to the GROUP BY clause that helps developers to define multiple groups in the same query. Grouping sets help dynamic analysis of aggregates and make querying/reporting easier and faster. The following is an example of grouping set.

SELECT StudentName, RollNo, Class , Section

FROM dbo.tbl_Student

GROUP BY GROUPING SETS ((Class), (Section))

ORDER BY StudentName

Conclusion

The above-mentioned are the 10 most significant and beneficial features provided by SQL server 2008 for developers. I hope this article will help a lot to database developers want to learn SQL server 2008.