Wednesday, August 25, 2010

Deprecated Database Engine Features in SQL Server 2008 R2

It would be better to know these features to avoid future problems. This has two tables that show features will not be supported in next version of SQL Server and features will not be supported in future versions of SQL Server.

Here is the link: http://msdn.microsoft.com/en-us/library/ms143729.aspx

Tuesday, August 24, 2010

Do you consider the “CONVERT_IMPLICIT”?

This is not just about implicit conversion. This is about implicit conversion that happens without our notice, that might hinder the performance. See the code below:

USE AdventureWorks2008
GO
DECLARE @FirstName varchar(50)
SET @FirstName = 'Gigi'
 
SELECT * FROM Person.Person
WHERE FirstName = @FirstName

No major issue. It returns data as we want. Enable Actual Execution Plan and run the code again. Do you see what I see?
iMAGE

SQL Server converts the value which is varchar into nvarchar which can be a costly operation in a certain situation. SQL Server does this because char and varchar have lower precedence than nchar and nvarchar.

This implicit can be simply overcome by making the variable as nvarchar. You can see the same when integer value is passed to a string type column that contains integer values.

Although this is a simple and not a major issue as we see, it can slow down your queries.

Monday, August 9, 2010

When GROUP BY is not used, HAVING behaves like a WHERE clause

If you have gone through BOL for HAVING keyword, you have seen above sentence at the top of the page. What does it exactly mean? Similar question raised up during my class too; Seems like this sentence makes all of us confused :).

It says that HAVING behaves like WHERE when GROUP BY is not used. Then where we can use HAVING without using GROUP BY? You just cannot replace WHERE with HAVING. But for some extent, HAVING can be used without GROUP BY. Think a about a scenario where you need to see whether number of orders placed by customers reach to certain level, let’s say 100, if so, get the number of orders. If there is a requirement like that, you can write a T-SQL like below;

USE AdventureWorks2008
GO
SELECT COUNT(*)
FROM Sales.SalesOrderHeader
HAVING Count(*) > 100

Here we have used HAVING without GROUP BY. What has happened here is, the entire row-set is considered as a group, hence HAVING works fine. I did not come across any other places this can be used, please share with me if you have.