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.