Starting the week of December 6th, Microsoft will launch a series of webcasts focused on SQL Server 2005 Development. During the week of December 6th, Microsoft/MSDN will host 15 webcasts introducing all the new features of SQL Server 2005 for Developers. Then, starting in January, there will be at least one webcast per week focused on specific developer topics for SQL Server 2005.

There are a bunch of benefits to watching this series (in addition to the wealth of information provided ;):

• Everyone who attends a webcast will receive a copy of the Beta 2 Resource Kit and the Beta 3 Resource Kit when it ships. 
• The first 1500 people that watch 5 or more webcasts will also receive a special, limited edition SQL Server 2005 Webcast T-shirt.
• All webcast viewers will be entered into a competition to win an XBOX, one for each day of the week. The official rules are here.

The official site with session information, dates, times and abstracts is: http://msdn.microsoft.com/SQL/2005Webcasts and if I hear of any updates I will also keep you posted on further details, etc...

The title is a common question I've received in the past and I thought I'd take a few minutes to explain a bit about keys and indexes...

This is by no means a lot of detail regarding relational theory, etc. but there are a few things that we should quickly review to make sure that the basis for indexes being created makes sense. First, from a relational theory perspective every table must have a primary key. From SQL Server's perspective it's not a requirement but it's generally a good idea. A primary/unique key are entity identifiers. Each are a unique way of identifying a row. There are subtle differences between the two - in implementation:

Primary Key

• In SQL Server the Primary Key is enforced through a Primary Key Constraint.
• None of the columns that make up the primary key allow nulls.
• The values in the Primary Key must be unique - to enforce uniqueness (as well as make it efficient), SQL Server creates a unique clustered [composite] index on the column(s) that make up the key.

Unique Key

• In SQL Server the Primary Key is enforced through a Unique Key Constraint.
• The columns that make up the unique key CAN allow nulls but not for more than one complete key. Meaning that allowing Nulls on a single column unique key really only allows NULL (only one NULL value). Overall, allowing Nulls in a column (like Social Security Number) doesn't really make much sense but if you need to then you can't go with a unique constraint - instead consider a unique index. At that point, I typically get the question of what's the difference between a unique key and a unique index...
• Allowing Nulls values in the columns that make up a composite unique key makes more sense as long as the complete key is not null for more than one row.
• The values in the unique Key must be unique and to enforce uniqueness (as well as make it efficient), SQL Server creates a unique NON-clustered [composite] index on the column(s) that make up the key.

What's the difference between a unique index and a unique constraint?

• A unique key CAN be referenced by a foreign key constraint and a column which has only a unique index cannot be referenced.
• Constraints are checked before indexes and this can lead to a large multi-row insert/select or update to fail before modification. However, indexes might (for a large modification) be validated at the end (instead of row by row) so a large modification that has a failure will need to rollback at the end of the modification rather than before. This is a good point - and one I hadn't really thought of until I was poking around some of the Q&A on SQLMag's website. Here's where I found it.

So, all of this leads me up to the original question (yes, you knew I get here someday :) and that's “When did SQL Server stop putting indexes on Foreign Key columns?”

First, SQL Server has NEVER put an index on a foreign key column... Indexes are used (as described above) to make the lookup (in a primary or unique key) for a duplicate value FAST. If the keys are ordered then checking to see if one already exists is trivial (i.e. fast). There is NO reason for SQL Server to put an index on a foreign key column as the column does not (and probably would never be) unique (if it is then it's likely to have a primary or unique key on it as well - as in a 1-1 relationship). So, that leads me to another key point...

Are there any benefits to indexing foreign key columns? YES

• Better performance on maintaining the relationship on a delete of a primary/unique key. When you delete a key row, SQL Server must check to see if there are any rows which reference the row being deleted.
  • If the foreign key relationship is defined with NO ACTION (on update/delete) then a referenced row CANNOT be deleted as it would leave the referencing rows “orphaned.” To find the rows efficiently an index on the foreign key column helps!
  • If the foreign key relationship is defined with CASCADE (on update/delete) then when a referenced row is modified all of the referencing rows must be modified as well (either updated to reflect the new value or on cascade delete). To find the rows to modify efficiently, an index on the foreign key column helps!
• Better join performance - for many of the reasons above, SQL Server can more effectively find the rows to join to when tables are joined on primary/foreign key relationships. However, this is NOT always the “best” indexing choice for joins but it is a good start. 

Finally, if you want a few titles related to relational theory check out these links:

An Introduction to Database Systems, Eighth Edition
     by C.J. Date
     E.F. Codd

Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design
     Mike Hernandez

Have fun!
kt

During the week of October 11, I delivered a four day “immersion” event where we focused on nothing but performance tuning - from design to indexing to optimizing procedural code. There were many states represented (CA, CT, FL, GA, IL, IN, KY, MA, MI, MS, NE, NY, OH, OR, TX, UT, WA, and WI) and it led to some great conversations - even outside of SQL! For the attendees, we had long days with lots of content (the comprehensive agenda is here) and evenings in a great city (yes, Chicago is my home town). And - to end the four-day event and kick off Bill's Friday event, I just couldn't let a group of so many visitors leave Chicago without having Chicago Pizza. On Thursday night we hit Pizzeria Due:

Not everyone was able to make it for dinner but we did happen to get a nice group shot here:

Finally, a few of the attendees were able to play “tourist” for a few minutes (I don't know how they had the energy!) and here are a couple of pictures I want to share with everyone. These next two Chicago pictures were taken by Ken (he's a New Yorker who was in town for the event - but we won't hold that against him :) :) :). In the first image, Ken took nine images facing North from the John Hancock building and in the second photograph there are five images facing South from the Hancock. Both of these are the result of “stitching” the images back together. Very cool Ken - THANKS!

Facing North from the John Hancock Building:

Facing Sorth from the John Hancock Building:

A fun event, a great group and a wonderful way to kickoff of our SQL Immersion event. Subscribe on SQLskills if you'd like to hear more about our upcoming SQL Immersion events planned for 2005 as well as those planned on SQL Server 2005.

Well, it's a small country - but with a huge population of SQL Server users - especially as far as ratios go! This week I've been in Reykjavik, Iceland delivering my second SQL Server Immersion Event on SQL Server Performance Tuning and Optimization which was hosted by my friends at Miracle Iceland. fyi - I still have to post a few pics from my Chicago Immersion event and I hope to do that tonight as well!!!

Class is over and I just thought I'd say a final thanks to all the great people I met this week. I'd also like to mention how impressed I am wrt to the major load reduction against the Miracle Mainframe (jpg 185.28 KB).

Thanks!

In a few past posts, a couple of other friends/RDs have given me geeky sql titles... such as sql hera (which Forte started here, referenced here and resulted in this) and the original culprit (from Clemens) started it all here....... major harrassment ensued and my irritation was subdued solely by being worn down.

Anyway, after months of avoiding the titles, I have decided to give up the fight and take advantage of a couple of birthday presents. Yes, I now own a couple of new domains. If you can guess what they are then you can get to this blog faster... they solely redirect you to here.

I hope all is well!

Wow, seems like forever since I last blogged... and yes, it was! Many exciting things have happened over the last few weeks and that's part of the reason for my silence... Thought I'd do a quick list of highlights and hopefully I'll expand on a few of these things over the next few days.

At the end of Sept - went to SQL PASS in Orlando which started a day late due to yet another hurricane but despite the lost day, once PASS got started it went really well. I did a lecture on Partitioning in addition to an all day workshop on “Designing for Performance.” One of the highlights of the conference for me (since I was a judge :) was the opening reception where we played a game show like game of trivia... It was quite a bit of fun deciding if the answer was “correct.” Turns out there are a few different things we all call SPIDs. The answer for the question was “server process ID“ but I allowed system process ID as that's what many of us call it... In fact, search through the books online for a couple of minutes and you'll see it called both. Well, I feel better now :). OK, enough of that. The “final question” was a good one though - what is the correct way to reduce the size of the transaction log. No one had the syntax right (but no one really tried either :) and the correct answer is...

(1) Clear space from the transaction log using BACKUP LOG.
The key thing that I want to stress here though is that “clearing” space from the log does NOT have to be done with the TRUNCATE_ONLY or NO_LOG operations... you can “clear” space from the transaction log by

(2) DBCC SHRINKFILE can then shrink the unused space from the transaction log - allowing you to shrink it down to a defined size (which is an option of shrinkfile) or you can shrink the transaction log down to the smallest possible size by using the TRUNCATEONLY option. One of the benefits of shrinking the log down to the smallest possible size would be to then alter the transaction log size by using ALTER DATABASE modify file to set the transaction log to the appropriate size. By shrinking and then altering in one step (altering to the necessary size based on transaction log activity and maintenancec requirements) you can reduce internal fragmentation in the transaction log.

OK, fragmentation in the transaction log is another story but as for the answer - BACKUP LOG and DBCC SHRINKFILE were accepted as the correct answer. If someone supplied only one part we didn't give any points (mostly because you wouldn't have really been able to shrink the log without both)!

After PASS I was back to Seattle to attend a beta course delivery of a new SQL Server 2005 course which is part of the SQL Ascend program. I've teamed up with Scalability Experts to deliver some of these workshops in 2005 and we're really looking forward to the events. This 5-day course is intense and covers a wide range of IT-related new features of SQL Server 2005. I'm looking forward to starting these deliveries in January!

After the course I was off to Chicago for our first ever SQLskills SQL Immersion event. It was GREAT! We had a blast!! The event hours were long but we got A LOT accomplished. Each day the room opened at 8am and a light breakfast was setup. Class officially started at 9 and we had lunch (and snacks) catered during the course. Each night a number of attendees stuck around for questions/playing/analyzing queries, etc. from 5-7pm and a few nights I didn't get out of there until almost 8pm. Yep, we work 1/2 days at SQLskills - any 12 hours you want! :) Each night we tried to attack as many real world problems as we could! As the week progressed, we solved quite a few interesting issues and the result for some was a gain in disk space (and quite a bit of it :) :), a gain in performance or for some - both! It was really fun to watch the - real time - practical application of many of our tips as the week progressed. I'm anxious to hear more from everyone as applications improve.

As for the content - it was all about Performance Tuning and Optimization from Design to Indexing to Optimizing Procedural Code. I delivered the first four days and then William R. Vaughn came in to deliver our client side lecture on Friday covering ADO.Net Best Practices in Data Access. Most of us went out for pizza on Thursday night (I couldn't possibly have allowed a Chicago event to occur without everyone having world famous Chicago pizza - would have gone to Uno's if they could have supported the group size but we ended up at Due's... pizza was excellent). As for why people weren't already familar with Chicago pizza... we had attendees from around the country - CA, CT, FL, GA, IL, IN, KY, MA, MI, MS, NE, NY, OH, OR, TX, UT, WA, and WI (a nice SELECT distinct state ORDER BY from my registration db ;).  Everyone had great questions, excellent “problems/issues” to tackle and together it was a fantastic mix of backgrounds and personalities. THANKS FOR A GREAT WEEK EVERYONE!

As for this week - I'm in Iceland and it's been colder than I had expected (no, I'm not a complete idiot - thank you)... They've had UNSEASONABLY cold weather and a crazy 3 day wind storm that's hit wind speeds of 134 mph. Most of the time they've just been 60-80mph but WOW it's been windy....especially at night. Today was quite nice and the weather is supposed to get better for the weekend (at least that's what the local weather folks have said... I'm definitely not going to believe weather.com right now ;). And - what am I doing here? I'm delivering another Immersion Event partnered with the local Miracle folks. It's my second time here and once again - the warmth of the people (not to mention the plentiful beer :) makes up for the colder temperatures! As for a local highlight, today I continued to aid in further load reduction of the Miracle mainframe... Huh? The Miracle mainframe currently holds 37 types of “Miracle dollars.” There's a bit of a story behind this but it relates to a previous form of payment that was made with beers... So yes, the Miracle mainframe has 37 types of beer and each night we partake in some load reduction to help out the mainframe! Still a few days to go...

From Iceland I head to Denmark to deliver a two hour lecture for PASS-Denmark and then next Thursday a few of us will be driving to Lalandia for the final DBForum event.

I head back to Redmond soon after DBForum ends and then there are only four more events this year:

7-10 November - Las Vegas, NV

15-19 November - Copenhagen, Denmark

22 November - Special 1-Day Indexing Event in Oslo, Norway
23-25 November - EastMed Developers Conference (EDC 2004) in Amman, Jordan

No links for the last two... *I* probably couldn't read them regardless. But - I will keep you posted.

After all the travel, I'll be enjoying as many consecutive weeks at home as I possibly can. Hope to see you soon!!!