Friday, January 27, 2012

Prevent duplicate rows using unique index

create table test(id int identity(1,1), name varchar(10))create
insert
unique index ix_test on test(name) with IGNORE_DUP_KEY into test(name)values('ppv')select * from test

Friday, January 6, 2012

Reenable sql server index

SELECT
'ALTER INDEX ' + I.name + ' ON ' + T.name + ' REBUILD ' FROM SYS.indexes I INNER JOIN SYS.tables T ON I.object_id = T.object_idWHERE I.name LIKE 'IX%'

Wednesday, January 4, 2012

Disable all indexes in sql server

For a data marting application being done @ SyneITY, we needed to disable all indexes in our Sql Server 2008 database. We used the following query to get this done

SELECT 'ALTER INDEX ' + I.name + ' ON ' + T.name + ' DISABLE ' FROM SYS.indexes I INNER JOIN SYS.tables T ON I.object_id = T.object_id


WHERE I.name LIKE 'IX%'