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)

Blind Spot - T-SQL $Identity

I've been working with SQL Server for 12 years, but this short cut escaped me.

In Transact-SQL, the $IDENTITY keyword allows us to refer to the identity column without knowing its name. Since identity columns are often primary keys, we could use this as a short cut for always referring to the PK. This would be awesome to use in a code generator or an ORM.

This is not to be confused with @@IDENTITY or SCOPE_IDENTITY(), which tells us the actual number auto generated by the IDENTITY column (though each behaves in different ways.)

Test it out with this script:

use tempdb
go

create table test
(
id int identity(1,1)
, descr varchar(100)
)
go

insert into test
(descr)
values
('first record')
, ('second record')
, ('third record')
go

select $identity, descr from test

TIG Notes

Yesterday at TIG I mentioned that the Visual Studio tips from Sara Ford had helped me to be a faster developer and worker. Many of you asked where you could find her blog, so here it is:

Sara Ford's BLOG

San Diego TIG - 2nd Meeting

The Tech Immersion Group met last night, and we had a great time. Next month, we'll be reading Head First C#, Chapters 6 - 8, and Lab 2. We'll be meeting on October 27th.

We also setup a new Google Group. DM me for an invitation.

San Diego Tech Immersion Group

TIG - Tech Immersion Group

WHAT

The Tech Immersion Group is founded on the belief that learning new technology can be fun. Our first topic will be C# and .NET. Future topics include Silverlight, SharePoint, WCF, Entity Framework, LINQ, and Azure. The idea is as follows:

1) We will meet monthly to discuss about 200 – 300 pages of a book we’ve chosen.

2) We will stay with the same topic for several months (probably until we’re finished with the book.)

3) We will study for certification exams together.

4) We will write sample code and software projects.

5) This will be a safe place where any question can be asked (Ugh, I hope you get a good answer).



WHO

All are welcome to join the group. We do need to know who they are ahead of time so we can ensure a book is provided. We’ve been able to get our first book donated by O’Reilly Press. Hopefully, we can keep this sweet deal going.

I’m Ike Ellis, and I’ve been writing software for 12 years. Software I have written is currently in use by several thousand people. I love to teach and I love to learn. I can’t wait to meet you and get started on this together!

THE COMMITMENT

You will get a free book, free time from a mentor, and really valuable knowledge. In return, we ask:

1) That you show up on time and ready to learn.

2) That you commit to being there every month.

3) That you consider becoming a member of the San Diego .NET User Group ($50 annually.)

4) That you write a review on Amazon regarding the book you got for free (This will keep the free books coming.)

That’s it! Let’s get ready to learn a ton!

WHERE

Robert Half Technology
4365 Executive Drive
Suite 450
San Diego, CA 92121

WHEN

The 4th Wednesday of every month. Please be there at 5:45pm, as the doors to our building will lock by 6pm. Don’t be late!

Ike Ellis Contact Info
Twitter: @EllisTeam1
FaceBook: http://www.facebook.com/ellisike
Linked In
http://ellisteam.blogspot.com
http://www.ellisteam.net



A BIG THANK YOU TO O'RIELLY FOR DONATING OUR BOOKS!!!
Visit them at http://www.oreilly.com.