Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

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.

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

rather than the more traditional

    Prod.Name as ProductName  ,
    Sub.Name as ProductSubcategoryName,
    Cat.Name as ProductCategoryName
    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: