Saturday, February 04, 2012

Foreign key doesn't create an Index automatically

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.

Friday, February 03, 2012

Avoid using SCOPE_IDENTITY and @@IDENTITY

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:
  1. Use MAX_DOP=1 as you are already using. This may hurt performance of the SELECT part of your query.
  2. 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.
  3. Use OUTPUT clause of INSERT to get the value you were looking for, as in the example I give further below. In fact I highly recomend using OUTPUT instead of @@IDENTITY in all cases. It's just the best way there is to read identity and timestamp.
  4. Changing autostas is NOT a good workaround. It may hide the problem for a while but a prallel plan will get produced eventually.
  5. Force serial plans for entire server via sp_configure 'max degree of parallelism' option.

Check out this KB Article for more information on this. As of SQL Server 2008 R2 this issue is not yet resolved and am hopeful that this would be fixed in SQL Server 2012.

Wednesday, February 01, 2012

Arithmetic overflow error converting IDENTITY to data type int

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, Line 1
Arithmetic overflow error converting IDENTITY to data type tinyint.
Arithmetic overflow occurred.

--Cleanup
DROP TABLE dbo.tblIdentityTest
GO

Lets reproduce the error for INTEGER column:

CREATE TABLE dbo.tblIdentityTest2
(
Sno INT IDENTITY(2147483645,1) --Max limit of INT is 2,147,483,647
,Firstname VARCHAR(20)
)
GO

--These records would get inserted
INSERT INTO dbo.tblIdentityTest2 VALUES ('Alpha')
INSERT INTO dbo.tblIdentityTest2 VALUES ('Beta')
INSERT INTO dbo.tblIdentityTest2 VALUES ('Gamma')
GO


SELECT * FROM dbo.tblIdentityTest2
GO


--As INT has already reached it max limit any new insertion would fail
INSERT INTO dbo.tblIdentityTest2 VALUES ('This would fail')
GO

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

--Cleanup
DROP TABLE dbo.tblIdentityTest2

In both of the above cases for demonstration purpose we had seeded the value nearest to its maximum limit of that data type. Though we know that we have very less rows in the table still SQL Server would throw this Arithmetic overflow error. This would show us that SQL Server doesn't automatically try to fill the existing gaps in the Identity sequence!

Normally based on the requirements we have to choose an appropriate data type to store our data. For the above issues one of the easiest way to solve this issue is to change the data type as BIGINT.


ALTER TABLE dbo.tblIdentityTest2 ALTER COLUMN Sno BIGINT
GO

This way now the table can store upto maximum of  2^63-1 (9,223,372,036,854,775,807) records in it. Actually, if we need, we can start the sequence from the minimum value of BIGINT (-9223372036854775808).

CREATE TABLE dbo.tblIdentityTest3
(
Sno INT IDENTITY(-9223372036854775808,1) 
,Firstname VARCHAR(20)
)
GO

So this table can now store data from -9223372036854775808 to 0 and then from 0 to 9223372036854775807. It can store huge amount of rows before it can run out of numbers.

IDENTITY column can also be of data type NUMERIC or DECIMAL. The only restriction there is its scale can ONLY be 0. As decimal data type cannot have more than 38 precision the value it can store is -99999999999999999999999999999999999999 to 99999999999999999999999999999999999999

CREATE TABLE dbo.tblIdentityTest3
(
Sno DECIMAL(38,0) IDENTITY(-99999999999999999999999999999999999999,1)
,Firstname VARCHAR(20)
)
GO


Related posts: $IDENTITY in SQL Server