LA Code Camp Files

Hi There!

Thanks for coming to my LA Code Camp session on SQL Server Performance Optimization. I hope you had a great time, learned a lot, and left with action items that can immediately help you at work.

Here's a link to the download files:

LA Code Camp.zip

See you soon!

Ike

ID Columns With No Indexes

This is a really simple script that will tell you what columns with "ID" in the name don't have indexes. Often, foreign key constraints are not added to the database. I'm sure the referrential integrity is being enforced in the business logic of the application. At least I hope so. In any case, the optimizer still needs those columns to be indexed.

select * from sys.columns c 
    where c.name like '%id%'
        and c.object_id not in 
            (
            select object_id from sys.index_columns
            )

Then I created this script to just go ahead and create a basic nonclustered index on those columns. Just copy & paste the results into a SQL Server Management Studio query window and run it.

select 'create nonclustered index  IX_' + t.name + '_' + c.name + '_001'
    + ' ON ' + schema_name(t.schema_id) + '.' + t.name + '(' + c.Name + ');'
    from sys.columns c 
    join sys.tables t 
        on c.object_id = t.object_id
    where c.name like '%id%'
        and c.object_id not in 
            (
            select object_id from sys.index_columns
            ) 

You'll want to include other columns in these indexes to make sure your indexes are covering the queries, but this should get you started. Make sure the results are return in text, not grid.

T-SQL Random Date

I wrote this code to generate a random date between two dates. I wanted a random date for each row in a table. Use @beginDate and @endDate to specify the lower and upper bounds of the random date. The only tricky part is the checksum(newid()). You can try using the rand function without it, but you'll just get the same random date for every row in the table.

declare @beginDate datetime, @endDate datetime
set @beginDate = '1/1/2000'
set @endDate = getdate()

select dateadd(d, cast(-1 * datediff(d, @beginDate, @endDate)
* rand(checksum(newid())) as int), @endDate)
from sys.objects


If you want to update a date column with a random date, you can just do this:

update Orders
set orderdate = dateadd(d, cast(-1 * datediff(d, @beginDate, @endDate)
* rand(checksum(newid())) as int), @endDate)