Today (Jan 25th, 2011), Microsoft released SQL Server 2005 Service Pack 4 (SP4) to Microsoft Update. SP4 was made available on the download center back in December 2010.
The SP4 update for non-Express SQL Server editions (Developer, Enterprise, Standard, and so on) will be made available as an optional update. As an optional update, you need to specifically scan and select the update to be installed.
For Express editions, the update will be pushed down to the system automatically, if you have changed your Windows Update settings to automatically download updates.
See here for guidance on obtaining SQL Server updates through Microsoft Update: http://blogs.msdn.com/b/petersad/archive/2009/08/14/microsoft-update-guidance-for-sql-server.aspx