What's new in Sql Server 2008 (A string of articles by Irfan AQ)

Declaring and Initializing Multiple Variables

Initializing a variable at declaration time was not possible in SQL 2k5. But this functionality is now available in Sql Server 2k8. We can declare and initialize variables with any constant value or with any expression;

DECLARE
@a int = 777,
@b bit = 0,
@c VARCHAR (10) = 'trg tech',
@d CHAR (3) = Substring('trg tech', 1, 3)

PRINT @a
PRINT @b
PRINT @c
PRINT @d

They indeed are making our lives easy!

What's new in Sql Server 2008? (A string of articles by Irfan AQ)

Filtered Indexes (My Favourite!)

As the name shows, filtered indexes are criteria-based indexes. We specify the criteria as a WHERE clause similar to the WHERE clause in a simple SELECT statement. In fact a filtered index is an optimized non-clustered index. Also, clustered indexes cannot have a filter. If properly designed, filtered indexes enhance query performance to great extent. Randy Dyess (Introducing Filtered Indexes) has some interesting demonstration on how filtered indexes can make database developer’s life easy. I was reading another article on filtered indexes in which author was saying that with filtered indexes, days of over-indexing will be over.

Here is a simple example on creating filtered index:

USE AdventureWorks

CREATE NONCLUSTERED INDEX MyFilteredIndex
ON Sales.SalesOrderDetail
(
SalesOrderID ASC,
ProductID ASC
)
WHERE (ModifiedDate < '03/01/2004')

What's new in Sql Server 2008? (A string of articles by Irfan AQ)

Trusted, Productive, and Intelligent; this is what Microsoft claims about SQL Server 2008. The latest release of SQL Server 2008 was in February 2008, which is still a CTP (Community Technology Preview) and it has most certainly caught the attention of database developers. There are a lot of promising features in 2008 release of SQL Server and in this string of articles, I would discuss them briefly to introduce you to this fascinating upgrade!

Inserting Multiple Rows in Single Insert Statement!

With SQL Server 2005, you could not insert multiple rows in a table in a single insert statement (very annoying!). If there are 5 rows to be inserted, for example, you had to write 5 insert statements. This is not a restriction with 2008. We can insert multiple rows in a single statement. The multiple rows are enclosed in parenthesis and separated by commas. This is the same syntax which is available in MySQL. Below is the example:

INSERT INTO
AdventureWorks.HumanResources.Department
(Name
,GroupName
,ModifiedDate)
VALUES
('Software Development', 'Research and Development', GETDATE()),
('trg tech', 'Research and Development', GETDATE()),
('Software Marketing', 'Sales and Marketing', GETDATE())