SSIS: The service did not respond to the start or control request

Apparently there are some problems with Service Pack 1 and SSIS. Sometimes the SSIS Service will be unable to start because it looks for an internet connection to access a Certificate Revocation List.

Lucky us, there is a solution... check out this post.

SQL Server 2005 Books Online (April 2006)

Another update of the books online here to go with Service Pack 1.

On a side note, Internet Explorer 7 Beta 2 has been released too.
Also find the readiness kit here.

Deferred constraint checking?

Greg Low has a nice suggestion imho.
Basically the idea is the following (from ADO.NET):

a) start a transaction
b) send all the updates in any order
c) commit the transaction (and check the RI constraints at this point)

Read his post and vote if you think it is a good idea.

SQL Server 2005 Service Pack 1

Ladies and gentlemen... it has arrived!
Download

Internal storage of the DateTime datatype

There are many different opinions on how the DateTime value is stored internally in SQL Server. Some people say that it is converted to UTC, some think that some other magic stuff happens but none of this is true.

When you look at the books online this is the explanation:

"Values with the datetime data type are stored internally by the Microsoft
SQL Server 2005 Database Engine as two 4-byte integers. The first 4 bytes store
the number of days before or after the base date: January 1, 1900. The base date
is the system reference date. The other 4 bytes store the time of day
represented as the number of milliseconds after midnight."

It may be hard to believe but this is exactly what SQL Server does. You send him a date and time and he calculates the number of days since 1900-01-01 and the number of ticks since 00:00 for that date. SQL Server does NOT care about timezone and daylight savings time. If you really need to take these into account I suggest you save the offset between the UTC date and the local date and the UTC date. I give preference to take both the times from the client so you don't create problems because of time differences between the server and the client (unless you can guarantee in-sync clients).

Here is a little script that demonstrates the internal storage:

DECLARE @theDate datetime
SET @theDate = '2006-04-14 14:00'

SELECT CAST(SUBSTRING(CAST(@theDate AS varbinary), 1, 4) AS int)

SELECT CAST(SUBSTRING(CAST(@theDate AS varbinary), 5, 4) AS int)
SELECT CONVERT(char(10),DATEADD(d, 38819, '1900-01-01'), 120) AS 'theDate'
SELECT CONVERT(char(10), DATEADD(s, (15120000 / 300), '00:00'), 108) AS 'theTime'

SSIS Performance Whitepaper

I found this great whitepaper on SSIS performance thanks to Jamie Thomson.
This is a must read if you want high performance SSIS Packages.

Best Practices Analyzer for SQL Server 2005

Some good news on the SQL Server Relational Engine Manageability Team Blog. They have decided to start developing a Best Practices Analyzer for SQL Server 2005.

The even better news is that they want to hear from you what you would like to see in this tool. So visit their blog and post your comments!

SQL Server 2005 Service Pack 1

According to Euan Garden's blog Service Pack 1 for SQL Server 2005 will be released this month!

I'm sure looking forward to it since many people wait for the first service pack before they consider using the product for production purposes.

SQL Server 2005 Upgrade Handbook

Check out this article on Technet giving you some insights in the upgrade process to SQL Server 2005.

Blank message box when starting SQL Server Management Studio

A couple of my colleagues were having a problem when starting Management Studio. Everytime the application started it would show a blank messagebox with just an OK button. Apparently this can be caused by installing the .NET Framework 1.1 (or one of it's updates) after the .NET Framework 2.0.









The SQL Server 2005 readme has the solution (although the problem description is not exactly the same - it seems to help in this case too):

1. In Control Panel, open Add or Remove Programs.
2. Click Microsoft .NET Framework 2.0.
3. Click Change/Remove.
4. Click Repair, and then click Next.
5. When the repair is completed, restart the computer if you are prompted to do this

Random record CTE type thing

One of the developers asked me if it was possible to repeat a single query multiple times. He was trying to generate test data for stress testing and wanted to repeat a simple SELECT ID, NewID(). He told me Oracle could do it so I obviously had to prove that SQL Server was able to do this too :-)

Here is the solution I came up with, it's a little fun with CTEs and the RowNumber function.

WITH RepeatCTE (RowNumber, LogID)
AS
( SELECT ROW_NUMBER() OVER(ORDER BY newID()) as RowNumber, newID() LogID UNION ALL
SELECT RowNumber + 1 as RowNumber, newID() LogID FROM RepeatCTE
)
SELECT TOP 400 * FROM RepeatCTE OPTION (MAXRECURSION 0);

An unexpected error occurred in Report Processing. (rsUnexpectedError) - Hotfix pending

For those of you who are having trouble with this. I have received the confirmation that the hotfix request has been accepted by the developers and a hotfix is on the way. Because we are planning to migrate to SQL Server 2005 we have chosen to request the hotfix for Reporting Services 2005.

As soon as I receive information about the hotfix I will let you know.

*EDIT*
I have received the hotfix and will probably be testing it next week. The hotfix only solves the Report Manager side of the problem since the Visual Studio side of it has to be fixed there. There is no hotfix planned for Visual Studio 2005 but "it will be fixed in the future" was the answer to my question.

Jolt winner - Database Engines and data tools

And the winner is... click
Can you guess without clicking? :-D

One big lookup table vs many small lookup tables

Recently I was reading SQL Magazine when I came across an article "Designing for performance: lookup tables" (instadoc: 48811 - Michelle A Poolet). The question was whether or not one big lookup table was better than many small ones.

While I do agree that one big lookup table complicates things in terms of integrity and data management there are a couple of arguments I disagree with.

Locking and blocking increases

The nature of a lookup table is read-only and given this I would suspect that only shared locks are being taken on the lookup table. So how would you experience locking and blocking in such a scenario?

You create a hotspot on the disk

I don't see why you would have hotspots on a read-only lookup table. Being a hot table means that all the pages of that table would probably be in your data cache - meaning less disk I/O once the pages have been cached. You can even force the table to stay in memory by pinning it, but I don't think many people actually use this technique since SQL Server handles data caching quite well.

I think like any database question the only answer here is: "it all depends". We have many domain specific lookup tables but we also have a big lookup table with over 300 different types. In a database with 400 tables I wouldn't want to create 300 additional tables for simple lookups where most of them would contain 3 to 4 records.

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

DevDays 2006

So... I just attended the Belgian edition of the DevDays.
To be honest I wasn't too thrilled about the sessions, most of the content was too high level to be interesting. I think the technologies presented are already 'too old' to talk about them in such a way. Most of us freaky developers read too much about it or even worked too much with them already.

I was impressed by the visual effects in Vista though. It looks like something in a science fiction movie and I got the urge to grab the windows in a virtual reality kind of way :-) The upgrade-your-memory-by-using-a-usb-stick was really 'funky' too. Apart from that the deployment options seem quite impressive. Unfortunately I took the SQL track for the rest of the day so I didn't get a chance to see some more new features.

Another interesting thing is the creation of the Belgian SQL Server User Group (http://www.bemssug.org/). Finally some Belgian activity in the SQL Server world; the development side of SQL Server is often forgotten in Belgium so I hope these guys will have some interest in that side of the SQL Server story too.

GRANT EXECUTE

A lot of SQL Server projects are using stored procedures to control their query behavior (which I like because developers shouldn't be in control of this :p). In previous versions of SQL Server there was something that was kind of disturbing in terms of security. In order to grant execute permissions to a user you had to give permission to every single stored procedure the user needed. When you have 1000 stored procedures you wouldn't want to do this manually so there was the possibility to generate a script by using sysobjects or INFORMATION_SCHEMA.Routines but you need to keep the permissions up-to-date when new procedures were created.

SQL Server 2005 has a great solution to this: GRANT EXECUTE
You can now create a database user role that has execute permissions on all the objects in that database.

CREATE ROLE db_ICanExecute
GO

GRANT EXECUTE TO db_ICanExecute
GO