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

Top 9 SQL Server Developer Interview Questions

The questions you may wish to ask or that will be asked during a SQL Server Developer interview will vary. Deanna Dicken shares the top 9 interview questions she asks when interviewing a potential SQL Server developer.
Introduction

In my many years in Information Technology, I have performed so many SQL Server interviews that I have long since lost count. I thought I would share with you some of the questions I find important when interviewing a potential SQL Server developer.
What is Referential Integrity?

Let's start with the basics. Regardless of the RDBMS they are targeting, I'd really like them to understand that beyond all else we need to ensure referential integrity. To ensure it, we have to understand it. I am listening for an answer along the lines of ensuring the relationships between parent and child tables and that the primary keys and foreign keys facilitate this integrity.
What is the difference between a clustered and a non-clustered index?

This is one of my favorite questions for sorting out the developers that have an understanding beyond your basic T-SQL to more of an understanding of performance. I'm looking for them to tell me that a clustered index represents the physical order of the rows as they are stored in the database. A non-clustered index is a logical ordering of the rows.

Both types of indexes facilitate faster lookups and joins.

As a follow-up question, should they get this right, ask them to tell you how many clustered indexes you can have on a table. The answer should be one. Rows can only be physically stored with one ordering.
What are DML triggers and what types of triggers can you have on a SQL Server table?

A trigger, as the name implies, is a collection of SQL statements triggered to execute by an insert, update, or delete action on a table that affects its contents. INSTEAD OF and AFTER can be specified for each trigger. When INSTEAD OF is specified, the trigger takes the place of the triggering action. So if you had an INSTEAD OF UPDATE trigger, the code inside the trigger would be executed instead of the original update. AFTER triggers are the same as the FOR used in earlier versions of SQL Server. This just means the trigger will execute following the original action. An AFTER UPDATE trigger will allow the update to complete and then the update trigger will execute. For more information, see DML Trigger Planning Guidelines.
What is a four-part name?

This question can help you get a sense for whether the candidate ever crosses database boundaries with his queries. A four-part name refers to the parts of a SQL Server object name that uniquely identifies it in the SQL environment. The first part is the instance. The second part is the database. Third is the schema and fourth is the object name. So if wanted to reference a table called employee from the HumanResources schema in the AdventureWorks database on your Prod instance, the four part name would look like this: Prod.AdventureWorks.HumanResources.Employee.
What is a Linked Server?

Another question that can tell you if the candidate has experience with distributed databases is "What is a Linked Server?" A linked server is a reference from one SQL Server server to another. If you have databases on another SQL Server server that contains data you need for your system, you could create a link server on your server to the other SQL Server server. Then, you can use the four-part name of the remote table to use it within your local queries.
How do you handle an error from within a stored procedure?

This one's a little open-ended. One, it depends on which version of SQL Server they work with. In SQL Server 2005 Microsoft introduced TRY…CATCH error handling. The code that could error is placed within a TRY block. If an error does occur, the code within the CATCH block executes. Several functions are available to assist in gleaning information about the error. See TRY…CATCH in Books Online for more information.

Prior to this, @@Error could be checked for an error code and then an error thrown using RAISERROR. Some may answer that they return the error in an output parameter or by using PRINT. RAISERROR can still be used to throw an error. As a matter of fact, a RAISERROR called from the TRY block with a severity between 11 and 19 will cause execution to jump to the CATCH block.
How do you auto number rows in SQL Server?

Sometimes I have to rephrase this one to get the answer. What I'm looking for here is the candidate's knowledge of identity columns. Identity columns are used to automatically increment a column in a table from a seed (or starting) value and by a defined increment. If no seed is specified and no increment is specified, the first entry is 1 and the subsequent entries are incremented by 1. If either the seed or the increment is specified, other must be specified.
What is a left outer join? Give an example.

The answers I get on this one blow me away. I can't believe how many candidates I've interviewed that do not grasp this concept. Assume you have two tables, TableA and TableB. You need all the rows from TableA and all matching rows from TableB. You would use a left outer join to accomplish this with TableA being the left table as in the following.

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.Col1 = TableB.Col1

What is a cursor and why would you use it?

The candidate will probably be able to tell you cursors are used to perform row-by-row processing on a result set. The candidate might tell you they use them for batch type processing. In their answer though I am secretly hoping the candidate will elaborate on performance issues with cursors and how it is preferred to use loops or set-based processing instead of cursors. If not, the follow up question here is "Is there another way to process the result set row-by-row without using a cursor?" To see more of my thoughts on cursors, see What Every DBA Ought to Know About SQL Server Cursors (and Their Alternatives).
Conclusion

The questions you may wish to ask or that will be asked of you will vary (like your mileage). Questions should be tailored to the job for which the candidate is applying. For instance, if you don't use triggers in your workplace, does it really matter if the candidate has a working knowledge of SQL Server triggers?