Defragment all indexes in the current database

Use this script to defragment every index on every table in the current database.

declare @workFlow table (
    WorkID  int identity (1, 1)
    ,TableName sysname
    ,IndexName sysname
    ,unique(TableName, IndexName)
    )
declare @startID int
declare @endID  int
declare @curID  int
declare @curTableName sysname
declare @curIndexName sysname

set nocount on

insert @workFlow
 (
 TableName
 ,IndexName
 )
 select dbo.sysobjects.[name] as TableName
  ,dbo.sysindexes.[name] as IndexName
  from dbo.sysobjects
   inner join dbo.sysindexes
     on dbo.sysindexes.[id] = dbo.sysobjects.[id]
  where (dbo.sysobjects.xtype = 'U')
   and (dbo.sysindexes.impid = 0)
  order by dbo.sysobjects.[name]

select @startID
  = min(WorkID)
 ,@endID
  = max(WorkID)
 from @workFlow workFlow

set @curID = @startID

while (@curID <= @endID)
 begin
  select @curTableName
    = workFlow.TableName
   ,@curIndexName
    = workFlow.IndexName
   from @workFlow workFlow
   where (workFlow.WorkID = @curID)

  dbcc indexdefrag (0, @curTableName, @curIndexName)
   with no_infomsgs

  set @curID = @curID + 1
 end

If you need to rebuild the indexes, replace the 'dbcc indexdefrag ...' statement with this one:

dbcc dbreindex (@curTableName, @curIndexName)
 with no_infomsgs

Also make sure you read this must-read white paper on index defragmentation by Mike Ruthruff: Microsoft SQL Server 2000 Index Defragmentation Best Practices.

ML

OPENXML custom UDF (inline table function)

Ever had the need to manipulate XML programmatically in SQL? How about this generic function? Of course it needs some alterations to suit your specific needs more appropriately... It recieves one parameter - the document handle of an internal representation of an XML document (see example below).

create function dbo.fnReturn_XML_asUniversalTable
 (
 @xmlObjectHandler int
 )
returns table
as
return (
 select OX.[id] as [id]
  ,OX.parentid as parentid
  ,OX.nodetype as nodetype
  ,OX.localname as localname
  ,OX.prefix as prefix
  ,OX.namespaceuri as namespaceuri
  ,OX.datatype as datatype
  ,OX.prev as prev
  ,OX.[text] as [text]
  from openxml(@xmlObjectHandler, '/') OX
 )
go

An example of use:

declare @xml   nvarchar(4000)
declare @xmlObjectHandler int

set @xml = 'FirstNameMatijaLastNameLah'

-- Prepare the internal representation of your XML
exec dbo.sp_xml_preparedocument
  @xmlObjectHandler output
  ,@xml

select *
 from dbo.fnReturn_XML_asUniversalTable(@xmlObjectHandler)

-- Never forget to remove the document and release the handle!
exec dbo.sp_xml_removedocument
  @xmlObjectHandler

Now go and customize! ML

Climbing trees is for monkeys

So, here's a monkey. A 'monkey' function, that is. The tree we'll be climbing is a self-referenced table:

create table dbo.TreeOfValues
 (
 NodeID   int  primary key
 ,Value   nvarchar(64)
 ,ChildOfNodeID  int 
  foreign key references dbo.TreeOfValues (NodeID)
 )
go

insert dbo.TreeOfValues
 (
 NodeID
 ,Value
 ,ChildOfNodeID
 )
 select 1 as NodeID
  ,'father1' as Value
  ,null as ChildOfNodeID
 union all
 select 2
  ,'son11'
  ,1
 union all
 select 3
  ,'son12'
  ,1
 union all
 select 4
  ,'son13'
  ,1
 union all
 select 5
  ,'father2'
  ,null
 union all
 select 6
  ,'son21'
  ,5
 union all
 select 7
  ,'son22'
  ,5
 union all
 select 8
  ,'grandson211'
  ,6
 union all
 select 9
  ,'grandson212'
  ,6
go

And this is the function:

create function dbo.Monkey
 (
 @NodeID  int
 ,@upToNodeID int
 )
returns int
as
begin
 declare @loopDone bit
 declare @currentNodeID int

 set @loopDone = 0

 while (@loopDone = 0)
  begin
   select @NodeID
     = dbo.TreeOfValues.ChildOfNodeID
    from dbo.TreeOfValues
    where (dbo.TreeOfValues.NodeID = @NodeID)

   select @currentNodeID
     = dbo.TreeOfValues.NodeID
    from dbo.TreeOfValues
    where (dbo.TreeOfValues.NodeID = @NodeID)

   if (@NodeID = @upToNodeID
       or @NodeID is null
       or @currentNodeID is null)
    begin
     set @loopDone = 1
    end
  end

 return @NodeID
end
go

And here is an example to find all the descendants of 'father2':

select *
 from dbo.TreeOfValues
 where (dbo.Monkey(dbo.TreeOfValues.NodeID , 5) = 5)

Beware! This function can get really slow on large tables. Indexes on NodeID and ChildOfNodeID are a necessity. ML

Return related values as array

Silly it may be but sometimes de-normalized relationships make more sense to the end-user. The following function presents a way to list related records (in the Northwind database) in a flat table, where in this case the names of products in an order can be listed in a single column as comma-delimited values. Also known as: aggregate concatenation.

create function dbo.fnGet_ProductName_asArray
 (
 @OrderID int
 ,@Separator nvarchar(16) = null
 )
returns nvarchar(4000)
as
begin
 declare @productNameArray nvarchar(4000)

 if (@Separator is null)
  begin
   set @Separator = N', '
  end

 set @productNameArray = N''

 select @productNameArray
   = @productNameArray
   + @Separator
   + convert(nvarchar(4000), isnull(dbo.Products.ProductName, N''))
  from dbo.[Order Details]
   inner join dbo.Products
     on dbo.Products.ProductID = dbo.[Order Details].ProductID
  where (dbo.[Order Details].OrderID = @OrderID)
  order by dbo.Products.ProductName

 set @productNameArray = ltrim(rtrim(substring(@productNameArray,
                       len(@Separator) + 1, len(@productNameArray))))

 return @productNameArray
end
go

Please, use this function for presentation purposes only. Better still - use appropriate presentation components to do this on the client-side.

WARNING!
There are known issues with aggregate concatenation in T-SQL:

• PRB: Execution Plan and Results of Aggregate Concatenation Queries Depend Upon Expression Location;
• Bug Details: Expression in ORDER BY clause causes self-referencing variables in the SELECT clause to evaluate only once rather than once per row.

Thanks to Erland Sommarskog for pointing them out to me.

Still, here's an example of use:

select dbo.[Order Details].OrderID
 ,dbo.fnGet_ProductName_asArray
  (dbo.[Order Details].OrderID, N' | ') as ProductNames
 from dbo.[Order Details]
  inner join dbo.Products
    on dbo.Products.ProductID = dbo.[Order Details].ProductID
 group by dbo.[Order Details].OrderID

ML

p.s. In SQL Server 2005 there are alternatives...