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!

Web service VS Remoting

A Web service is equivalent to a static function library. If a client creates an instance of Web service and a member variable of the object with one method call, then attempts to read the value from another method call, the value will be the default value for the variable, not the new value. That's because a Web service doesn't maintain session state without considerable hacking outside the "SOAP" standard.

Remoting feature of .Net however, works with remote objects. If you need the ability to use the remote objects rather than just remote methods, Remoting is the way to go!

Can you identify a pro for Web service when measured against .Net remoting?

Re: What are we aiming to accomplish with this code?

Usama did it guys! We indeed are removing duplicate entries from a sorted array of integers!

Well done Monsiuer :-)

Pivot Table

This seems to be a popular question and quite a few chaps have recently asked me about this.

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table. Here is an example;

USE AdventureWorks
GO
SELECT [CA], [AZ], [TX]
FROM
(
SELECT sp.StateProvinceCode
FROM Person.Address a
INNER JOIN Person.StateProvince sp
ON a.StateProvinceID = sp.StateProvinceID
) p
PIVOT
(
COUNT (StateProvinceCode)
FOR StateProvinceCode
IN ([CA], [AZ], [TX])
) AS Pvt

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())

Re: What are we aiming to accomplish with this code?

Hey Technotrons!

You still haven't been able to figure this one out yet :-)

int remove_duplicates(int * p, int size)
{
int current, insert = 1;
for (current=1; current < size; current++)
if (p[current] != p[insert-1])
{
p[insert] = p[current];
current++; insert++;
}
else current++;
return insert;
}

Re: You might need to scratch your head

Whoa Imran!

Atif is right. It seems like your query did skip out one year! But an interesting attempt, really.

Atif,

It seems like techies weren't able to crack it. Here is your solution;

SELECT A.*
FROM(SELECT ((SELECT MAX(Hire_t) FROM ABC) + 1) - ROW_NUMBER() OVER
(ORDER BY GETDATE() DESC) AS DT
FROM sysobjects WITH (NOLOCK)) A
WHERE A.DT BETWEEN
(SELECT MIN(Hire_t) FROM ABC)
AND (SELECT MAX(Hire_t) FROM ABC)
AND A.DT NOT IN (SELECT HIRE_T FROM ABC)

It ain't as simple as it sound!