Vadivel's blog

There are still people who are believing that Foreign Key does create an index automatically by SQL Server. I think since Primary key by default creates a Clustered Index people are assuming that Foreign keys would also create an Index automatically. This is a myth and SQL Server does NOT automatically  create an index on a foreign key columns. But one of the best practices for Index tuning is to Index all the columns which are part of a foreign key relationship. Check out the MSDN documentation  for the sub heading Indexing FOREIGN KEY Constraints. The first line says " Creating an index on a foreign key is often useful .... ". Microsoft wouldn't be saying this if FK is automatically indexed.

Avoid using SCOPE_IDENTITY() and @@IDENTITY functions if your system is using Parallel Plans . Extract from the above link: Posted by Microsoft on 3/18/2008 at 1:10 PM Dave, thanks to your very detailed and dilligent report I was able to find the problem.  Yes, it's a bug - whenever a parallel query plan is generated @@IDENTITY and SCOPE_IDENTITY() are not being updated consistenly and can't be relied upon.  The few workarounds I can offer you for now: Use MAX_DOP=1 as you are already using. This may hurt performance of the SELECT part of your query. Read the value from SELECT part into a set of variables (or single tabel variable) and then insert into the target table with MAX_DOP=1. Since the INSERT plan will not be parallel you will get the right semantic, yet your SELECT will be parallel to achieve performance there if you really need it. Use OUTPUT clause of INSERT to get the value you were looking for, as in the example I give further below. In fact I hig

If we have an IDENTITY column and if our insert statement is trying to exceed the maximum value of INTEGER then it would throw this error. To know the range for TinyInt, SmallInt, INT, BIGINT check out this MSDN link Lets reproduce the error for an TINYINT column CREATE TABLE dbo.tblIdentityTest ( Sno TINYINT IDENTITY(250,1) --Max limit of TinyInt is 255 ,Firstname VARCHAR(20) ) GO --These records would get inserted INSERT INTO dbo.tblIdentityTest VALUES ('250') INSERT INTO dbo.tblIdentityTest VALUES ('251') INSERT INTO dbo.tblIdentityTest VALUES ('252') INSERT INTO dbo.tblIdentityTest VALUES ('253') INSERT INTO dbo.tblIdentityTest VALUES ('254') INSERT INTO dbo.tblIdentityTest VALUES ('255') GO SELECT * FROM dbo.tblIdentityTest GO --As TINYINT has already reached it max limit any new insertion would fail INSERT INTO dbo.tblIdentityTest VALUES ('This would fail') GO Msg 8115, Level 16, State 1, Lin