Tuesday, March 20, 2012

How to disable constraints on a table

Sometimes it's useful to disable one or more constraints on a table, do something significant like a BULK INSERT / DELETE, and then re-enable the constraint(s) once you're done.

You can disable / enable the constraint using the ALTER TABLE … NOCHECK/CHECK CONSTRAINT  …

Here is an example that disables all the constraints in a table

 

--disable all the constraints for Customer table 
ALTER TABLE Customer NOCHECK CONSTRAINT ALL
--do something 
--disable all the constraints for Customer table 
ALTER TABLE Customer CHECK CONSTRAINT ALL


Here is the one which disable /enable  a particular constraint



--disable the foreign key constraint FK_customer_countryId constraint 
ALTER TABLE Customer NOCHECK CONSTRAINT FK_customer_countryId
--do something
--enable the FK_customer_countryId constraint 
ALTER TABLE Customer CHECK CONSTRAINT FK_customer_countryId


 



Once you disable/enable a constraint, make sure to enable it after you are done with your BULK operation

Wednesday, March 14, 2012

Script to find the size of an Index

Simple query to find the size of  the indexes

 

SELECT
	OBJECT_NAME(i.OBJECT_ID) AS TableName,
	i.name AS IndexName,
	i.index_id AS IndexID,
	8 * SUM(a.used_pages)/1024 AS 'IndexSize in KB'
FROM sys.indexes AS i
JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE i.object_id = object_id ('yourTableName')--- put the table name here
GROUP BY i.OBJECT_ID,i.index_id,i.name

Tuesday, March 6, 2012

SQL Server 2012 Virtual launch event

Finally.. the most anticipated version of sql server is ready for launch. Just few more hours..want to register for the virtual launch, click the below link

Experience the excitement of the SQL Server 2012 Virtual Launch Event