Here's a little snippet of SQL to help resolve collation conflicts. You could use it to analyze all the tables on your SQL server and it will attempt to change the collation of any text fields to the default collation.

In the current state it doesn't actually change anything, it simply returns a table of sql command. I wrote a simple app which will do the sql commands as a batchfile - you can copy and paste the data from the Query into this SqlBatchRunner.

Here's the SQL:

set nocount on



declare @defaultCollation varchar(256)
set @defaultCollation =cast(serverproperty('collation') as varchar(256))


declare @dbName varchar(256)
declare @fieldName varchar(256)
declare @tableName varchar(256)
declare @fieldType varchar(256)
declare @fieldSize varchar(10)
declare @sql nvarchar(1000)

declare c cursor for
select name
from master..sysdatabases
    where name in ('tennis', 'prods', 'media')

open c
create table #fields (tableName varchar(256),
    fieldname varchar(256),
    databaseName varchar(256),
    collation varchar(256),
    fieldSize varchar(10),
    fieldTypeName varchar(256))

fetch next from c into @dbName

while @@Fetch_status =0
begin
set @sql = 'insert into #fields select so.Name as tableName, sc.name as fieldname, ''' + @dbName + ''' as DatabaseName, sc.collation, sc.length, st.name as fieldName
            from ' + @dbName + '..syscolumns sc inner join ' + @dbName + '..sysobjects so
                on so.id = sc.id
                inner join ' + @dbName + '..systypes st on sc.xtype = st.xtype
            where sc.xtype in (175, 239, 99, 231, 35, 167)
             and so.xtype like ''U''
             and sc.collation not like ''' + @defaultCollation + '''
             and (select count(*) from ' + @dbName + '..sysindexkeys where colid = sc.colid) =0
                
                '
                

exec sp_ExecuteSql @sql

fetch next from c into @dbName

end

close c

deallocate c


create table #SqlCommands (sql nvarchar(1000), result int)


declare d cursor for

select tableName, fieldName, databaseName, fieldTypeName, str(fieldSize) from #fields

open d

fetch next from d into @tableName, @fieldName, @dbName, @fieldType, @fieldSize

while @@fetch_status = 0
begin
    insert into #SqlCommands values ('alter table ' + @dbName + '..' + @tableName + ' alter column [' + @fieldName + ']' + @fieldType + '(' + @fieldSize + ') collate ' + cast(serverproperty('collation') as varchar(256)), '')
    
    fetch next from d into @tableName, @fieldName, @dbName, @fieldType, @fieldSize
    
end

close d
deallocate d

declare @errVal int

declare e cursor for select sql, result from #SqlCommands for Update of result

open e

fetch next from e into @sql, @errVal

while @@fetch_status =0
begin
    print @sql
    --exec @errVal = sp_executesql @sql
    update #SqlCommands set result = @@Error where current of e
    fetch next from e into @sql, @errVal
end


close e
deallocate e

select * from #SqlCommands


drop table #SqlCommands
drop table #fields

And you can download the source code for the SqlBatchRunner.

I've just found this piece of sql to convert a recordset into csv I've never seen this being used anywhere before. The equivalent of Array.join(",") it never ceases to amaze me how powerful computer languages are.

It's especially useful to me as I was looking for exactly this earlier this week. I decided to go away and think about it, I'm glad I did!

I'm sure there's a better way of doing this but I found it just as quick to write this method to list all fulltext catalogs on a server.

create table #c (dbname varchar(250), catcount int)

declare @name varchar(250)

declare c cursor for select [name] from master..sysdatabases
open c

fetch next from c into @name

while @@fetch_status = 0
begin
	insert into #c exec('select ''' + @name + ''', count(*) from ' + @name + '..sysfulltextcatalogs')
	fetch next from c into @name
end

close c
deallocate c

select * from sysfulltextcatalogs



select * from #c

drop table #c

When I was younger, so much younger than today. To calculate the size of a table in a SQL Server database I used to use some horrid sql to COUNT(*) all the rows then multiply by the sizes of the sum of the lengths of the fields in that table. This took an age to run and wasn't particularly accurate as you don't really get any information about the size of any indexes held on that table.

Then at the unbelievable age of 64 (less a few decades) I suddenly found the SP_SPACEUSED stored procedure and quickly wrote the following SQL to produce the results I needed.

declare @id int
declare @name varchar(255)

create table #tmp (name varchar(255), [rows] int, reserved
varchar(30), data varchar(30), index_size varchar(30), unused
varchar(30))

declare c cursor for select id, name from sysobjects where type = 'u'

open c

fetch next from c into @id, @name

while @@Fetch_status=0
begin
    insert into #tmp exec sp_spaceused @name, true
    
    fetch next from c into @id, @name
end

deallocate c


update #tmp set reserved = left(reserved, charindex(' ', reserved)),
                data = left(data, charindex(' ', data)),
                index_size = left(index_size,
charindex(' ', index_size)),
                unused = left(unused,
charindex(' ', unused))


select count(*), sum(cast(reserved as int)) as SumOfReservered,
            sum(cast(data as int)) as SumOfData,
sum(cast(index_size as int)) as SumOfIndex_Size,
            sum(cast(unused as int)) as SumOfUnused
from #tmp

select * from #tmp order by cast(reserved as int) desc

drop table #tmp

Well I've added the extra left and right fields and created the values, and the result is that the search returns in 50% of the time.

I'm not completely satisfied by this however, and am pretty sure that I can do better. Nevertheless, I'm now in a better place to start optimising than I was previously.

I've uploaded some helper SQL queries to help with creating the left/right values.

First is a script to create and populate a basic hierarchical table

Next is the stored procedure I use to set the values of left and right

Finally the stored procedure used to initialise the correct left/right values

Having read this article I'm going to try to implement this on one of our search procedures, currently about 80% of the query time is taken finding the leaf nodes of a tree.

I think that using this process the search procedure should return in a fraction of it's current time.

According to Query Analyser returns a recordset within 5 seconds. Once I've implemented the changes I'll post the new time.