Perhaps a little known feature of SQL Server 2005 but it might come in handy when you are slowly changing your database to use schema's. Synonyms allow you to define an alias for your objects and this is very helpful when you are migrating to schema's.
By using synonyms you can change your object names while retaining your 'interface' to the application and this allows for a smoother and more phased migration.
A little example with a table but remember that this also works for stored procedures, functions, etc. and it is also possible to create synonyms for remote objects (linked server).
--Create a schema
CREATE
SCHEMA SchemasRock
GO
--Create a table in the default schema
CREATE
TABLE dbo.myTable
(ID int)
GO
--Insert a row
INSERT
INTO dbo.myTable VALUES (1)
--Move the table to the new schema
ALTER
SCHEMA SchemasRock TRANSFER dbo.myTable
--Insert another row (will fail!)
INSERT
INTO dbo.myTable VALUES (2)
/*
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.myTable'.
*/
--Create a synonym to support 'old' table name
CREATE
SYNONYM dbo.myTable FOR SchemasRock.myTable
--Insert another row
INSERT
INTO dbo.myTable VALUES (2)
--Select the rows
SELECT
* FROM dbo.myTable
SELECT * FROM SchemasRock.myTable
--Clean
DROP
SYNONYM [dbo].[myTable]
DROP TABLE [SchemasRock].[myTable]
DROP SCHEMA [SchemasRock]
We're at build 3175 with KB936305, the hotfix is not publicly available though.
For an overview of the hotfixes released after SP2 check out KB937137, it contains the list of fixes per build.
Back to the current release of SQL Server ;-)
Get the updated Books Online here.
I found some time to play with CTP3 and checked out the MERGE statement. What you achieve with it may not be rocket science and was perfectly possible with a couple of IF statements but using MERGE gives you much cleaner code. It looks like Microsoft is investing a lot in 'cleaner' coding in SQL Server 2008, which I can only welcome with great enthusiasm.
A simple example will show you exactly what the MERGE statement does.
CREATE TABLE StagedData
(ID int, UserName varchar(20))
GO
CREATE TABLE RealData
(ID int, UserName varchar(20))
GO
INSERT INTO StagedData VALUES (1, 'Slava'), (2, 'Paul'), (3, 'Wesley')
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO
UPDATE StagedData SET UserName = 'Kimberley' WHERE ID = 3
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO
DELETE FROM StagedData WHERE ID = 3
GO
MERGE RealData r
USING
(SELECT ID, UserName FROM StagedData) staging
ON (r.ID = staging.ID)
WHEN MATCHED THEN UPDATE SET r.UserName = staging.UserName
WHEN TARGET NOT MATCHED THEN INSERT VALUES (ID, UserName)
WHEN SOURCE NOT MATCHED THEN DELETE;
GO
SELECT * FROM RealData
GO
DROP TABLE StagedData
GO
DROP TABLE RealData
GO
I took some time to look at the ''nifty" features in SQL Server 2008.
There is a long list of new features shown in this webcast, but I doesn't look like all of them are already implemented in CTP3.
Here are some of the new features that are in this build and are quite nifty:
- MERGE, a very powerful statement to combine insert/updates and even deletes in a single statement
- Multiple values INSERT (eg. INSERT INTO myTable VALUES (1, 'SQL Server 2008 Rocks'), (2, 'It really does') - the example in the BOL seems to be incorrect)
- FORCESEEK query hint
- Table-valued parameters enables you to pass table variables to stored procedures or functions
- ALTER DATABASE SET COMPATIBILITY_LEVEL isof sp_dbcmptlevel
- Policy based management, this is an awesome feature that allows you to set policies on many configuration options and even naming conventions for your objects in a very flexible way (Declarative Management Framework)
- Change Data Capture, a built-in auditing mechanism
- Some new dynamic management views (sys.dm_os_memory_brokers, sys.dm_os_memory_nodes, sys.dm_os_nodes, sys.dm_os_process_memory and sys.dm_os_sys_memory)
There are probably many cool features and I'll keep you informed when I stumble upon them.
*EDIT*
A great list of new features can be found here.
Check it out here, the first public CTP of SQL Server 2008.
Go go go :-)
Francois Ajenstat announced in a podcast interview - at the very first Microsoft Business Intelligence Conference - that the first CTP will probably be released in the coming month!
Get your Virtual Servers ready ;-)
By the way, you can find webcasts of this conference on the resources page.