Top 10 SQL Server 2008 Development Features

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 covers the top 10 developer features introduced in SQL server 2008.

Introduction:

Many new developer features were introduced in SQL Server 2008

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, excel spreadsheets 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.

Additional Resources

TechNet Magazine: SQL Server 2008 - What's New

SQL Server Books Online