Wednesday, July 14, 2010

SSIS Scripting

Tim Mitchell did a great job presenting on Scripting in SQL Server Integration Services this past Monday to OKCSQL. A video of his 90 minute presentation is available here.

T-SQL Intersect and Except

During the SQL Server 2008 Database Development Boot Camp in February, several T-SQL features introduced in SQL Server 2005 and 2008 were covered. I wanted to blog about the one that most caught my eye. SQL Server now has both Intersect and Except which follow the below syntax.

{ <query_specification> | ( <query_expression> ) }
{ EXCEPT | INTERSECT }
{ <query_specification> | ( <query_expression> ) }

Examples from the AdventureWorks database for SQL Server 2008 follow:

SELECT COUNT(*) FROM Sales.Customer
(Returns count of 19185)

SELECT CustomerID FROM Sales.Customer
INTERSECT
SELECT CustomerID FROM Sales.SalesOrderHeader
(Returns 19119 distinct CustomerIDs. They are the distinct list of CustomerIDs that are found in BOTH Sales.Customer and Sales.SalesOrderHeader)

SELECT CustomerID FROM Sales.Customer
EXCEPT
SELECT CustomerID FROM Sales.SalesOrderHeader
(Returns 66 distinct CustomerIDs. They are the distinct list of CustomerIDs that are found in Sales.Customer that are not found in Sales.SalesOrderHeader)

I've heard debate as to whether or not INTERSECT and/or EXCEPT are faster than other options. Thus, I'll leave it up to you to try them out in your own systems to determine their potential value. I will mention, though, I have heard of a real-word case where an INTERSECT query similar to the one in the above example took seconds or minutes instead of hours. In that specific scenario the table on the left side of the INTERSECT had just a few thousand records and the table on the right side of the INTERSECT had millions if not billions of records.

June 2010 OKCSQL Presentation

After multiple weather postponements, I was able to give my Introduction to Visual Studio 2010 Database Functionality presentation to the Oklahoma City SQL Server Developers Group. With the meeting taking place not on our standard meeting day, I had some minor concerns that no one would show. To my delight we had a good turnout and one of my colleagues from a previous employer showed and brought some of his colleagues too.

Most important, though, is that we didn't have another serious weather event. I knew some people impacted from the first postponement, which was caused by tornadoes. The second postponement came from floods.

SQL Saturday Dallas 2010

I had a great time speaking at and attending SQL Saturday Dallas 2010. The North Texas SQL Server User Group did an excellent job organizing the event! The event was amazing with a 500 person sellout, 42 sessions, and lots of great networking. You would have never known that this was their first SQL Saturday to put on.

While there, I had the opportunity to give two presentations.

I debuted my Visual Studio 2010 Database Functionality presentation to about 40 people. I had hoped to first present this topic at OKCSQL in May, however, we had some issues due to inclement weather the day of the May meeting.

I, also, presented my Introduction to SQL Server Analysis Services 2008 OLAP presentation to 55+ people.

I look forward to attending future events put on by the North Texas SQL Server User Group.

Note: Presentation links are pointing to presentations given at the OKCSQL meetings. The OKCSQL 90 minute presentation slots provided the opportunity to include more content than at SQL Saturday Dallas.

Wednesday, June 2, 2010

Thoughts on February SQL Server 2008 Boot Camp

Wow!!! Things have been so hectic the past few months, that I'm just now getting to blog about February's free SQL Server 2008 Boot Camp taught by Mike Hotek. Over the course of 9 evenings in Oklahoma City, Mike Hotek (18th SQL Server MVP) taught a FREE 32 hour Microsoft SQL Server 2008 Certification Boot Camp 70-433 to 40 professionals. Professionals ranged from an Informatica specialist with an Oracle background all the way to very experienced SQL Server DBAs.

The course was coordinated over the course of just a few weeks by just five individuals. They were Rob Sullivan (VP of OKCSQL), Mike Hotek (our instructor), Lily Liu (amazing volunteer who coordinated with OUHSC to get us the free location), Rao Velvadapu from OUHSC, and myself. The course was planned very last minute, so we just had a few weeks to get everything together. As a result, neither myself nor Rob had the opportunity to visit the location beforehand. Thus, I remember showing up on night one only 10 minutes before the class (I had been lost on the OUHSC Campus for 30 minutes) and walking into a SUPER crowded classroom. There was almost not a place for me or any other later arrivers to sit. So lesson one learned, always visit the location of an event before the event, if you're a key coordinator. The second night we started rearranging the table layout before and after each class.

The course was to focus on SQL Server 2008 Database Development. Since, I'd been working with SQL Server for over a decade, I didn't think there would be a lot of new stuff for me to learn on the database development side. The DBA-side would have been another story, since I've been very developer/BI focused in my SQL Server experience. Boy was I WRONG about how much I would learn. I learned a ton!!! Even more, I have lots of notes related to topics to blog on. Hopefully, I'll get to blogging on them at some point.

What many of us commented about liking so much about the class was Mike's teaching style combined with his vast knowledge base. Mike would start a topic at near beginner level and would eventually bring it to an advanced level. I expect Mike is able to do this given his international speaking experience and his experience working with more than a thousand clients over 20 years.

We got a lot of great feedback on the course and even had at least one guy skip a NBA game for which he had tickets in order to not miss a specific session.

With a small classroom of 40 people dedicated to advancing their SQL Server 2008 skills over 9 evenings for a total of 32 hours, the class even started to become like a family. The experience was great and led to a second class which Mike is currently teaching about SQL Server 2008 Database Administration. Hopefully, I can blog about that class soon.

Monday, January 25, 2010

Free Microsoft SQL Server 2008 Certification Boot Camp 70-433

Throughout the past year, I've had the opportunity to work with Mike Hotek first at a client site and then later on when he spoke at the OKC SQL Server Developers Group for several meetings. He is one of the most knowledgeable SQL Server experts that I have personally met. Even more, he has been very supportive of several local SQL Server groups, including the OKC SQL Server Developers Group.

One very gracious offer that he put forward was the willingness to provide free SQL Server training in the evenings, if someone would provide the location. Back in July 2009, Integris hosted a free two evening SSRS 2008 class.

This February the OU Health Sciences Center has been very gracious to provide space for 40 people to take a FREE Microsoft SQL Server 2008 Certification Boot Camp 70-433 class taught by Mike Hotek. Mike used to teach these classes for $4,000 per student. He now teaches them for very nominal fees or for free.

Today, we opened registration for this class that begins Monday, February 8, 2010. Registration is open at http://sqlbootcamp.eventbrite.com/.

We opened registration at noon CST and I've literally been watching tickets nearly sell out before my eyes. As of this posting there are 11, wait another one just went, 10 free tickets left. If you're interested in this class, register quickly!

Update (02-Feb-2010): This class sold out in less than 20 hours! We look forward to a great class next week!