Is it a bird, is it an airplane...?

No it's the SQL Server 2005 Service Pack 1 CTP
Check it out here

GO GO GO

Foreign keys and indexes

A very common misunderstanding is that indexes are automatically created for foreign keys. While most of the times you need them they are NOT automatically created by SQL Server and before you ask... SQL Server NEVER created them automatically. When your database grows in size this may become a very big performance hit.

Your first task is to detect the foreign keys that have missing indexes. I've created the following query for SQL Server 2005. I have to test it some more to be sure it covers everything but on my little test database it seems to work fine. This is obviously a starting point and you can extend the query quite easily. I'm not checking if the index is too wide to be considered yet, but obviously none of you guys have such wide indexes right?

I'll give it a try on a bigger database tomorrow since it's getting kind of late now ;-)


SELECT fk.Name as 'ForeignKey', OBJECT_NAME(fk.parent_object_id) as 'ChildTable',
OBJECT_NAME(fk.referenced_object_id) as 'ParentTable', c.Name as 'Column', i.Name as 'ValidIndex'
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.columns c ON c.object_id = fk.parent_object_id AND c.column_id = fkc.parent_column_id
LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = fk.parent_object_id AND c.column_id = ic.column_id AND ic.index_column_id = 1
LEFT OUTER JOIN sys.indexes i ON i.object_id = ic.object_id AND i.index_id = ic.index_id
ORDER BY ChildTable, ParentTable

DevDays 2006 - Clarification

I noticed on Tom Mertens' blog that my words may have been a little too harsh so I felt I should give a more nuanced response. I do not mean any disrespect and I know everyone works very hard to organize such an event. I was just expressing my own personal feeling at that time.I took the SQL Server track and maybe my comment on Tom's blog explains why I personally didn't like it too much. It could be the difference between IT Pro's and Developers which I am... a Database Developer :-)

Maybe this is the real problem in Belgium, Database Developers are kind of an unknown species and we don't see many projects that actually have people that do just that. I hope more and more companies will start to understand the importance of Database Developers so the DevDays can have a separate Database Developers track :-D

4GB of VAS under WOW, does it really worth it?

Another great post by the SQL Server Memory Guru: here

Also check out this suggestion coming from Greg Low (MVP). Although it is a feature in Oracle I still think it is a great idea :-) Being able to define your variable type as the column type gets rid of the sometimes painful type mismatches between parameters/variables and column data types. Having the wrong datatype can cause very odd behavior from the Query Optimizer. It also saves you a lot of work when you change the datatype of a specific column. Go VOTE :-D

From the suggestion: DECLARE @TradingName dbo.Customers.TradingName