SQL Server 2008 – Annoyingly Helpful

One of the features that has often been asked for is intellisense for T-SQL and this is now implemented in SQL Server 2008.  But one of the reasons it has taken so long is that SQL isn’t a language that readily lends itself to this sort of assistance. For example in a select statement we state what we want before we say where we get it from.  So to get the most from the feature you need to start with the from clause and word backwards to fill in the select and forwards to add where and so on:

My top tips here would be to get into the habit fully declaring what you are selecting from  i.e. myschema.mytable and never to use select * from  as both of these are slower to run and with intellisense no slower to enter.  I prefer to alias my tables, but you may wish to fully declare each column as again you won’t need to type all this any more and it could help with ease of maintainability i.e.

Select
Production.Product.ProductID,
Production.Product.Name as ProductName,
Production.ProductSubcategory.Name as ProductSubcategoryName,
Production.ProductCategory.Name as ProductCategoryName
From
Production.Product
inner join
Production.ProductSubCategory
on Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID
inner join
Production.ProductCategory
on Production.ProductCategory.ProductCategoryID = Production.ProductSubcategory.ProductCategoryID

rather than the more traditional

Select
Prod.ProductID,
Prod.Name as ProductName ,
Sub.Name as ProductSubcategoryName,
Cat.Name as ProductCategoryName
From
Production.Product Prod
inner join
Production.ProductSubCategory Sub
on Prod.ProductSubcategoryID = Sub.ProductSubcategoryID
inner join
Production.ProductCategory Cat
on Cat.ProductCategoryID = Sub.ProductCategoryID

Another slightly confusing thing I cam across was if you try and modify the data type of a column, you get an error message in CTP6:

This is caused by the default options in Management Studio:

So do watch out for these subtle changes in the latest CTP, they might seem like a pain but they are trying to help.

Technorati Tags: SQL Server 2008