Thursday, July 31, 2008

[Tip] SELECT INTO with an IDENTITY column

Really basic, but I always forget the syntax.

select identity(int, 1,1) as ID [...] into [nonexistent table] from [other tables]

Wednesday, July 9, 2008

[Indexes] Making sure you CAN reorg/rebuild/defrag indexes

In SSMS, prior to SP2, the default when creating indexes was to create them with page locking disabled. Which means you can't defragment or rebuild them.

Here's a script, courtesy of "TheSQLGuru", which will script out the corrections.
http://www.eggheadcafe.com/software/aspnet/29953067/finding-all-the-indexes-o.aspx

set quoted_identifier off
go
SELECT "alter index [" + i.name + "] ON [" + s.name + "].[" + t.name + "]
SET (ALLOW_PAGE_LOCKS = ON)
go"
-- s.name, t.name, i.name
FROM sys.schemas s
JOIN sys.tables t ON
t.schema_id = s.schema_id
JOIN sys.indexes i ON
i.object_id = t.object_id
WHERE
i.index_id > 0
AND INDEXPROPERTY(i.object_id, i.name, 'IsPageLockDisallowed') = 1
AND INDEXPROPERTY(t.object_id, i.name, 'IsStatistics') = 0
AND NOT EXISTS
(
SELECT *
FROM sys.objects keys
WHERE
keys.parent_object_id = i.object_id AND
keys.name = i.name AND
keys.type IN('PK', 'UQ')
)