Latest Entries

Find gap ranges in IDENTITY values

Copied from SET NOCOUNT ON GO — Prepare a sample table CREATE TABLE GapsTest ( ID int NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Name varchar(50) NULL ) GO — Insert 100k rows of dummy data INSERT INTO GapsTest (Name) VALUES (‘TheFirstSQL’) GO 100000 — Delete some rows to create gaps in the identity … Continue reading

SQL Server 2008 R2 / SQL Server 2012 / Uncategorized

Find fragmentation and if the table/index is partitioned

select DB_NAME(a.database_id), OBJECT_NAME(a.object_id), ,i.index_id,index_type_desc,index_level, case when p.partition_number <> 1 Then ‘1’ else ‘0’ end isPartitioned, avg_fragmentation_in_percent,is_unique,is_disabled, page_count,avg_fragment_size_in_pages,a.partition_number from sys.dm_db_index_physical_stats (DB_ID(),object_id(‘NameOfTable’),NULL,NULL,’LIMITED’) a join sys.indexes i on a.object_id=i.object_id and a.index_id=i.index_id join sys.partitions p on a.object_id=p.object_id and a.index_id=p.index_id and a.partition_number=p.partition_number where avg_fragmentation_in_percent >0 and page_count > 0 and i.is_disabled=0 order by OBJECT_NAME(a.object_id) , avg_fragmentation_in_percent desc Continue reading


Scripting Foreign Key Constraints

The Script below ¬†allows you to script Foreign Keys on the fly. This takes in to consideration if, the Constraint was created with NOCHECK OPTION. especially useful if you’re creating constraints when doing partition SWITCH. select ‘ALTER TABLE ‘ + object_name(fk.parent_object_id) + CASE WHEN fk.is_not_trusted =1 THEN ‘ WITH NOCHECK’ ELSE ” END + ‘ … Continue reading