MSDN users to get early access to Visual Studio 2005 tools

According to this article in Infoworld we will have access to a release candidate of SQL Server 2005 and Visual Studio 2005 in September! The final versions would be available mid to end October.

Very good news!

Using Sp_configure To Change a Value Will Issue DBCC FREEPROCCACHE

Another little known fact I guess but Brian Moran pointed out that issueing an sp_configure triggers the DBCC FREEPROCCACHE which removes all cached procedure plans.

SQL Server Magazine's Reader's Choice Awards

An the award goes to....... A First Look at SQL Server 2005 for Developers.

Congratulations to the authors!

Buy

Adding a non nullable column without a default

When trying to add a column with a default that is not nullable to an existing table you get an error message (even when there are no records in the table)

ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'NewColumn' cannot be added to table 'tbl_test' because it does not allow nulls and does not specify a DEFAULT definition.

There is however an easy workaround.

ALTER TABLE dbo.tbl_test
ADD NewColumn smalldatetime NOT NULL
CONSTRAINT DF_ToDrop DEFAULT ('2000-01-01')

ALTER TABLE dbo.tbl_test
DROP CONSTRAINT DF_ToDrop

Google Talk

Google released an IM GoogleTalk.
Looks like MSN could get a decent competitor

Ken Henderson

For those of you that don't have it already... buy the book!

sql_variant

sql_variant is one of the datatypes that I don't often see being used although it has it's advantages. It's kind of like a varchar where you would be able to store numeric characters, alphanumeric characters, dates, ... with the difference that on a sql_variant field you would be able to determine of which datatype the value is.

One tip I would give you is to always explicitly cast the value you insert (or update) to the datatype you want it to be. Apparently SQL Server uses 2 extra bytes to determine the extra information like the datatype etc. (eg. int would be 6 bytes).

The SQL_VARIANT_PROPERTY function gives you information about the variant itself like datatype, total bytes, precision ...

For more information visit:
sql_variant
SQL_VARIANT_PROPERTY

An interesting Q&A with Kimberly L. Tripp

Read it here

Back and already having fun

Aaah back from my 2 week holiday. I can't say I'm thrilled to be back but I can live with it ;-)

Especially when I find things like this :-)

Here's a nice article about fragmentation