SQl server 2008 Features

T-SQL Data Types
SQL Server 2008's new date and time types.
The new DATE type is just a date, in the range of 0001-01-01 through 9999-12-31, which equates to Jan. 1, A.D. 1 through Dec. 31, A.D. 9999.Iit provides a simple date type usable for most business applications. SQL Server stores this information in three bytes, a vast improvement over the DATETime’s eight bytes, which is a nice savings when you don't need the overhead of the time data.
The equivalent TIME type has a range of 00:00:00.0000000 through 23:59:59.9999999, stored in five bytes at the default 100 nanosecond precision. The new DATETIME2 type doesn't show much imagination in how it was named, but it's useful, providing a larger date range, a larger default fractional precision, and optional user-specified precision stored in six to eight bytes. A new DATETIMEOFFSET type includes time zone information embedded in the type. The date and time enhancements in SQL Server 2008 make it much easier to work with temporal data, and the database stores the data much more efficiently.
SQL Server 2008 spatial data types.
SQL Server 2008 has built-in support for two kinds of spatial-data systems. The geometry types support planar or "flat-earth" coordinate data. The geography types store ellipsoidal data that stores locations on the earth's surface, a flattened sphere. Whether you're storing GPS data scattered around the globe or need to store the coordinates that define complex shapes on a rectangular surface, you'll find a lot of features in these data types, along with dozens of useful methods.
T-SQL Improvements:

The new version includes many features that make code simpler and more efficient. There are a few syntax enhancements that developers will like, including a couple that make T-SQL seem more like a "real" programming language. You can now declare and initialize variables in a single statement. Now this statement works: DECLARE @id int = 5
Another nice new programming feature is compound operators. There's no rocket science here; this code prints "6": DECLARE @id int = 5SET @id += 1PRINT @id
T-SQL supports compound operators for addition, subtraction, multiplication, division, and modulus, as well as the bitwise operators.
Sparse columns provide an efficient storage mechanism for data that consists of many nulls. When defined using the SPARSE keyword on a column definition, any nulls in the column require no storage space at all. There are a few restrictions; for example, you can't use sparse columns with a few complex data types such as the spatial types, image and text fields, or user-defined types. But using sparse types requires substantially less disk storage. You can also use sparse columns with column sets, which is untyped XML data that combines all the sparse columns in the table. Column sets are useful when the table contains many sparse columns and when working with these columns individually could prove tedious.
table-valued parameters (TVPs).
This one feature single-handedly will save you from a lot of ugly T-SQL code. Have you ever had to pass several pieces of data as a parameter to a stored procedure? Maybe it was a comma-delimited list or some other array-like structure. You'd have to write some nasty parsing code to split up the values, then probably use a loop to process the data. SQL Server 2005 introduced a table data type, but you couldn't pass it to a procedure.
TVPs solve these kinds of problems elegantly by letting you pass -- as the name suggests -- a table-valued parameter to the procedure or function. Then, in the body of the procedure, you can use the set-based features of SQL to process the data, such as by inserting it into a persistent table.

Here's a simple example of using a TVP. There are four simple steps. First, create a persistent TABLE type with the schema to hold the data: CREATE TYPE MyTbl AS TABLE (ID INT, String NVARCHAR(100))GO
Next, create a stored procedure that accepts a parameter of the TABLE type you just created. As with any stored procedures, you can have as many parameters as you like, and any or all of them can be TVPs: CREATE PROCEDURE dkSelectFromTVP(@TVParam MyTbl READONLY)AS SET NOCOUNT ON SELECT * FROM @TVParamGO
Now declare a variable of your TABLE type and insert some data: DECLARE @TVP AS MyTblINSERT INTO @TVP(ID, String) VALUES (1, ‘Fairbanks')INSERT INTO @TVP(ID, String) VALUES (2, ‘Juneau')INSERT INTO @TVP(ID, String) VALUES (3, ‘Anchorage')INSERT INTO @TVP(ID, String) VALUES (4, ‘Denali')
Finally, run the stored procedure and pass in the TVP: EXEC dkSelectFromTVP @TVP
TVPs have a few restrictions; the worst of these is that the parameter is read-only. If you leave off the READONLY keyword in the stored-procedure definition, you'll get a syntax error. The SQL community is exerting some pressure on Microsoft to ease this restriction, so perhaps a future version will make TVPs even more flexible. But now, whenever you need to pass multiple pieces of data in a single parameter, you won't have to write messy parsing code to break it up.
A database scenario that developers often need to write code for involves taking a set of source data, updating a table based on that source data, and then applying changes as necessary. You might have to update some existing rows, insert new rows, or delete rows that are no longer needed. This can be tortuous T-SQL code to write, often requiring a dreaded cursor, which is a guaranteed drain on performance.
MERGE statement:
MERGE lets you define the source and target data, as well as the operations performed on the target data based on whether the source data matches the target data (using match definitions you specify). Assume you have a table with data about a set of working dogs, with fields such as Name, BirthDate, and HarnessSize. You gather a set of changes from a user and need to apply the changes to the Dogs table. You might do this by creating a TABLE type named typDogUpdates to store the source update data that the code will apply to the dbo.Dogs table (see Listing 1). You can then use the MERGE statement to create a TABLE variable of that type and populate it with data. In this case, the primary key dogID will be the basis for matching source and target data, but you can use any field or combination of fields you want. If SQL Server finds a matching dogID, it updates the row; otherwise, it inserts the record.
The MERGE statement consists of five main clauses. The MERGE clause specifies the target for the changes -- in this case, the persistent table Dogs. The USING clause specifies the source data that contains the changes you want to apply to the target. The ON clause specifies the join condition, how the source data is matched to the target data. One or more WHEN clauses let you specify what to do when the database finds matches and what to do when source data doesn't match anything in the target. In Listing 1, the code executes an UPDATE statement when there is a match. When the source data doesn't match a row in the target, it executes an INSERT statement. You can also delete data and apply various conditions to each action. Finally, the OUTPUT clause can return a result set that contains records for each row changed in the target data.

Other great enhancements to T-SQL in SQL Server 2008: Grouping Sets are an extension to the GROUP BY clause and provide functionality similar to ROLLUP and CUBE to define multiple groupings in a single query. You now have far more options for working with unstructured data, including enhancements to the XML data type and FILESTREAM, which lets you store blobs in the file system while letting SQL Server manage the files for you.
Full-text search:
SQL Server 2008, full-text search is completely integrated into the database instead of being stored externally. Portions of full-text indexing and querying are now integrated into the query optimizer, so performance is much better, and there are more tools to extract useful data from the database. You might want to consider dumping all that gnarly T-SQL code you wrote over the last decade to give users flexible searches into their data and implement full-text searches instead.
SQL Server 2008 supports Windows PowerShell,:
an enhanced, extensible scripting shell interface for developers and administrators who love the command line. SQL Server includes two PowerShell snap-ins that expose the hierarchy of database and server objects as paths (similar to file-system paths). On the surface, this sounds a bit like an abomination, but it can simplify getting around the database object model. Another snap-in implements a set of PowerShell cmdlets for performing a variety of actions such as running sqlcmd scripts. PowerShell is a powerful tool, but if you love your mice and GUIs, you can opt not to use it.