Watch out when using @@IDENTITY and SCOPE_IDENTITY() in your applications. Below is a quote from a Microsoft SQL Server engineer.
… whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistently and can’t be relied upon.
You can find more about the bug and workarounds at the SQL Server bug report.
UPDATE 28MAR2009: Pinal Dave has a more detailed blog post about the bug with workaround.
Run the below query to see which databases that uses read committed snapshot isolation:
SELECT name, is_read_committed_snapshot_on FROM sys.databases
To enable it on a specific database run the below query:
ALTER DATABASE [<databasename>] SET READ_COMMITTED_SNAPSHOT ON
As a developer I am in love with read committed snapshot isolation feature for SQL Server 2005+. Transactions can read and write simultaneously the same piece of data – no read/write contention due to row versioning.
Normal conflicting scenarios:
Simultaneously write and read of the same data.
Simultaneously writes of the same data.
In effect this is optimistic concurrency control without coding anything. Read more about read committed snapshot isolation and isolation levels in the Isolation Levels in SQL Server 2005 article.
Microsoft has released a poster for download, where you can see all the system views for SQL Server 2008 and the relationships between them.
Microsoft also released the system views poster for SQL Server 2005 a couple of years ago.
You need a huge printer/plotter to print these…
Below is my real life story telling how easy it can be to find index candidates if you use your tools.
This weekend we deployed a new version of a medium/large solution to the production environment for one of our customers. Normally this is not a big issue, as we are always prepared to roll back to the previous version – the one currently in production. The deployment and configuration processes are well-prepared and highly automated to minimize faults and late-night-bug-hunting-frenzy.
After the deployment is complete we smoke test the setup to make sure that it is configured correctly and all is sound.
This weekend after the successful deployment the smoke test failed miserably. We received TimeOutExceptions all over. It was coming from the user repository service.
We decided to investigate a little before the decision of aborting the deployment of the new version and therefore rolling back to the previous version.
The steps to resolve the issue is what I want to share with you.
A quick look in the source repository showed that almost no changes to the user repository service – none that could cause TimeOutExceptions.
With the SQL Server Profiler it was easy to spot the cause of the exception.
This simple select statement was isolated and copied into the Management Studio to view the Actual Execution Plan. The execution plan did show much, but by execution the below statement which instructs the SQL Server not to execute the Transact-SQL statements. Instead, SQL Server returns detailed information about how the statements are going to be executed in the form of a well-defined XML document.
SET SHOWPLAN_XML ON
The SQL Server will include loads in information and one of these are suggested indexes which you will not see with the graphical execution plan in Management Studio. Look for the MissingIndex elements:
<missingindexes>
<missingindexgroup Impact="96.6222">
<missingindex Database="[DOIPEI]" Schema="[dbo]" Table="[OrganisationalUnit_Stack]">
<columngroup Usage="EQUALITY">
<column Name="[org_pkid]" ColumnId="2" />
</columngroup>
</missingindex>
</missingindexgroup>
</missingindexes>
By creating this single index on [DOIPEI].[dbo]. [OrganisationalUnit_Stack]. [org_pkid] the TimeOutExceptions disappeared, as the query no longer took minutes but milliseconds.
We were able to continue and successfully deploy the new version of the solution with only minimal disruption and a delay of only half an hour. Why the index where missing is still a mystery.
It makes all the difference in the world if you know the capabilities of your tools at hand.
This week I hosted two sessions at two different events for Microsoft Denmark. One at Radisson SAS Scandinavia Hotel in Århus and one in Microsoft Denmark’s headquarter in Hellerup.
It was fun at the feedback was very positive. The best part was during the breaks with great edifying technical discussions. All the slides are available at Miracle’s homepage.
We (my colleagues at Miracle and I) are currently in the process of upgrading a customer’s solutions. One of task is migrating 100 gigs of data into an existing database schema. This can easily become a tedious task and can take considerable calendar time. Therefore we decided to disable all constraints on the database, move the data and then enable them again, as we know the data integrity is sound.
We could have written a script that gets all the constraints in the database and generates a SQL script, but there is an easier way…
-- Disable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
--Enable all Constraints
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
That’s easy… 
I found this on the Internet somewhere – the sp_MSforeachtable procedure is nowhere to be found in books online.
You can check if foreign keys and check constrains are disabled via the sys.foreign_keys and sys.check_constraints views.
Another obvious triviality in our situation is disabling and enabling triggers – that is easy too:
-- Disable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
-- Enable all Triggers
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
All the presentations from the product launch are recorded as webcasts. They are available here (it’s in Danish). Later this year the webcast will be moved to TechNet.
I spoke about long overdue features in SQL Server for developers.
Some of the webcasts has poor sound quality (not mine 
), which is a shame.
The webcasts where recorded at Microsoft. They used Camtasia Studio which I highly recommend as it is easy, intuitive and have some great features.
I guess the poor sound quality is due to lack of proper microphones and the recording was done in a regular meeting room. They used the build-in microphone on my laptop. Microsoft Denmark should have better sound equipment and a recording studio if they are serious about webcasts.
I am speaking at the launch of Windows Server 2008, Visual Studio 2008 and SQL Server 2008 in Denmark February 28th. It will be an online launch – meaning that you can watch in comfort of your personal development sphere 
As it is a Danish event and we Danes speak Danish – the event will be in Danish 
You can sign-up here.
I just found this list of free SQL Server tools that I want to share with you.
I use some of these already, but will check out the others too.
I’ll attend the Miracle SQL Server Open World conference from March 8-10, 2007 where I will speak about Windows Communication Foundation (WCF) and .Net Garbage Collection on the business intelligence & .Net development track.
Smart guys like Mark Souza, Lubor Kollar, Poul Randall from Microsoft and Kimberly Tripp will be there – will you?