The W Blog

OT: Another reason to love Red Gate

WhitneyW — Fri, 21 Nov 2008 20:15:22 GMT

After Thursday’s sessions ended I was invited to a dinner thrown by Red Gate software. I’m in their “Friends of Red Gate” program, which basically means I show their tremendous software to customers or at user groups. It’s been an easy relationship for me as I am quite a fan of their products.

So the event is packed at a local restaurant, we all sit down and on either side of me I’ve got Peter deBetta and Adam Machanic. Also at the table is Roman Rehak and Heidi Sweeney. Not bad company at all, right? So the conversation runs in every possible direction and a lot of red wine and good food follows. I begged off the party at 3 as I’m a sissy (or at least that’s what I was told).

Red Gate put on a really great evening and their people are even better than the products they build (if you can imagine).

UPDATE: I added a couple iPhone photos from the evening.

Note the serious, foreboding face of one Peter DeBetta…

And the rest of the gang…Rowan, Heidi, and Adam.

SQL PASS: Thursday

WhitneyW — Fri, 21 Nov 2008 19:37:28 GMT

Another fun filled day at PASS started with a session from Kalen Delaney. The room was packed and she was discussing query plans. I had heard a good bit of the content before but it was still interesting to finally see her present live. Very much like Itzik, her asides can be as informative as the content in her slides or demos.

The second session was a lot of fun as Paul Nielson and Louis Davidson had everyone join in their personal debate over what exactly is “proper design”. It was a 300 level session so nothing really earth-shattering came from it but it was really interesting to see the wide range of opinions across the room. There were even defenders of the dreaded “Muck” table, or the single table housing all look-up data (the horror!). I tend to fall on Louis’ side of most issues, but Paul made some strong arguments as well. At the end of the session Paul demoed a project called NORDIC which is an object-oriented database design. I’ll need to give this more attention before commenting but it certainly left me with questions swirling in my head.

The third session I attended was the most fun for me personally. Erik Kang, Program Manager from the product team, discussed intellisense and the debugger in 2008. We talked for a while after the session and I have a much better understanding of where he was coming from back in our early conversations about down-level support for intellisense. I think there are other pieces of the product that will be much better for the attention devoted to them than the time for implementing down-level support. I missed two opportunities to meet his team this week which really bummed me out. Just too many pieces of my schedule have been in play to make it happen.

The last session was a question and answer time with three general managers of the SQL team. They basically let attendees line up and fire questions at them for over an hour and a half. Now that’s brave. Some really interesting questions (some polite others not so much) were asked ranging from dedication to new product features (we hardly knew you Notification Services) to future support for jdbc driver. The coolest part to me was when a question was trending towards too technical a direction the GMs were able to point into the crowd and say “oh, here’s XXX he/she is the program manager for the area you’re discussing, they would be more than happy to talk more about it…”. Another benefit of having the event so near Redmond.

All in all, a tremendous day of SQL geekiness. I have to give some props again to Magenic for okaying this adventure out to the northwest. It’s been a great time.

SQL PASS: Wednesday

WhitneyW — Wed, 19 Nov 2008 23:59:50 GMT

A quick post about today’s sessions so far…Ted Kummert’s keynote was interesting in how sparse his details were. Aaron has a good overview of his talking points – I won’t bother retyping. I guess I was expecting at least one big “woo hoo” announcement out of him. Oh well.

The most interesting thing I saw was a demo from Dan Jones of a new feature called DAC. The basic premise is that you have a “fabric” of instances defined and you can then create a deployment package (Dan used the analogy of an .msi for the database) for use against any of those instances. The tool in SSMS will allow you to apply policy to the deployment and check for successful passage of said policies before implementing on the target instance. Very cool.

Another great piece of the keynote was a demo of some of the DATAllegro features that will be rolled into the next version of the product. A demo took place showing data being spread across multiple instances then coming together as it was accessed via an SSRS report.

I went to Mark Sousa’s session on lessons learned by the SQLCAT team around SQL 2008 installations. The session focused on places where the product team expecting certain behaviors and were surprised by results on the ground (both positive and negative).

I’m about to head to a session with Paul Randall and Kimberly Tripp called “Corruption Survival Techniques”. I suspect my head will be close to explosion after listening to those two for an hour and a half. :)

More fun to come…

Headed to PASS

WhitneyW — Tue, 18 Nov 2008 01:29:18 GMT

I’m a couple thousand miles over Colorado as I’m typing this, headed to Seattle and the PASS conference. Should be a tremendous week of content and networking. It’s also a bonus to be so close to the Microsoft campus. I will finally get to put a face with a couple names that have been very helpful to me over the years.

If you are going to be attending the conference drop me a line through the Contact section of this blog and we can meet up during the week.

Where have you been?…I blame Windows 7

WhitneyW — Thu, 13 Nov 2008 04:02:29 GMT

I recently broke my own blog admonition about Windows 7 and virtual environments. In my defense, I came across a laptop drive in my home office that worked in my Dell D620. That’s a sign right? You’re just meant to install it… :)

So I gave it a whirl fully expecting to either blue screen my laptop (at best) or end up with scorch marks in the carpet and a bad limp. I was thinking back to the beta of Vista that had all sorts of little maddening quirks.

I think I was struck most by just how uneventful the whole installation process was. The look and feel was pretty Vista like with some nice differences from my current 64-bit Vista install. I really liked the paired down feel with the removal of duplicated items like Mail and Photo Gallery (which can be replaced by installing Windows Live versions).

The largest effort in setup was getting the “superbar” or taskbar to show up like the demos seen at PDC. If you are interested Rafael has the easy steps to make that happen for you.

Knowing I was dealing with a spare drive I got a bit of a daredevil streak going. I tried installing the drivers for my broadband modem. I installed Office, I set up connection to my company’s Exchange server and started dragging email over. I then kicked off the Windows Live installer and fired up IE. With all this activity going on I still never saw higher than 1.3 gigs of memory utilization. Sweet. I continued to beat on the OS just waiting for the “well there you go…” moment where the beta rolled over. Never happened.

The only things that really never worked were installing Flash (I never pay attention to how many sites use flash until I’m on a machine without it) and using Live Mesh. While I can’t truly say Live Mesh didn’t work it did decide to change my color scheme at random intervals. Perhaps this is just a user interface “feature” I’m just not getting. I am a data guy after all. ;)

I even went so far as to use this OS for a week at my current client. There were no crashes, no problems. I think Microsoft really has a winner with Windows 7.

So hopefully this will explain a bit of where I’ve been in regards to this blog. Focus will be returning to SQL Server after this R&D boondoggle, just need to make a trip to Seattle first.

SQLSaturday content – DMVs

WhitneyW — Sun, 12 Oct 2008 16:35:00 GMT

As promised here are the code samples and slide deck from yesterday’s presentation. Thanks to everyone who came to hear me speak, you were a great audience.

I would also like to give a virtual high five to all the folks involved in putting on SQLSaturday in Greenville. You guys did a super job.

Split a string in TSQL (without looping)

WhitneyW — Wed, 08 Oct 2008 14:43:00 GMT

During the course of two days in the MSDN TSQL forums I must have seen 10 requests that were basically the same issue. Some string of values is passed from your application and you need to split them into the individual items. Here is a quick implementation that will get you a string splitting inline function that requires no recursion.

First, we need to create a numbers table that we will later utilize for string parsing. I’m using a version Itzik Ben-Gan’s number table that he posts frequently in SQL Mag articles.

-- Create and populate an auxiliary table of numbers (1,000,000 entries)
IF OBJECT_ID('dbo.Numbers') IS NOT NULL 
DROP TABLE dbo.Numbers;
GO
CREATE TABLE dbo.Numbers
(
number INT NOT NULL CONSTRAINT PK_Numbers PRIMARY KEY CLUSTERED
);

SET NOCOUNT ON;

DECLARE
  @max AS INT,
  @rc AS INT;

SET @max = 1000000;
SET @rc = 1;

BEGIN TRAN;
  INSERT INTO dbo.Numbers(number) VALUES(1);

  WHILE @rc * 2 <= @max
  BEGIN
    INSERT INTO dbo.Numbers(number)
      SELECT number + @rc 
      FROM dbo.Numbers;

    SET @rc = @rc * 2;
  END

  INSERT INTO dbo.Numbers(number)
    SELECT number + @rc 
    FROM dbo.Numbers
    WHERE number + @rc <= @max;
COMMIT TRAN; 
GO

Next, we create the inline function that will do the heavy lifting for us. I’ve added an input for separator as hard coding the comma almost always guarantees some other character will later be needed.

IF OBJECT_ID('dbo.SplitString', 'IF') IS NOT NULL
DROP FUNCTION dbo.SplitString;
GO
CREATE FUNCTION dbo.SplitString(@arr AS VARCHAR(8000), @sep AS CHAR(1))
  RETURNS TABLE
AS
RETURN
  SELECT
    (number - 1) - LEN(REPLACE(LEFT(@arr, number - 1), @sep, '')) + 1 AS pos,
    SUBSTRING(@arr, number, CHARINDEX(@sep, @arr + @sep, number) - number) AS element
  FROM dbo.Numbers
  WHERE number <= LEN(@arr) + 1
    AND SUBSTRING(@sep + @arr, number, 1) = @sep;
GO

Finally, we call our function with a string of values. If we needed to then utilize this for filtering we could use the CROSS APPLY operator introduced in SQL 2005.

DECLARE @arr varchar(8000);
DECLARE @sep char(1);

SET @arr = '1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C';
SET @sep = ',';

SELECT *
FROM dbo.SplitString(@arr, @sep);

This will give us a two row result set containing a position and value. Simple, easy, and set based to boot! And now I can just reference this post instead of cutting and pasting code. :)

Windows 7 and PDC

WhitneyW — Thu, 25 Sep 2008 02:17:02 GMT

Are you looking for a super early beta of Windows 7 for your virtual machine of choice (and dear Lord please tell me you would only do this on a virtual)? The Windows Vista blog is reporting that attendees of next month's PDC will be getting the first public beta of said OS.

There were other posts today regarding the giveaways that might be the best I've heard yet. Apparently all software swag will be handed over on a 160GB USB drive. Now that's a cool way to handle business.

Who knows, maybe the fine folks at Microsoft have saved you from yourself and put 7 on a virtual drive. ;)

SQLSaturday, Greenville SC (Oct 11)

WhitneyW — Thu, 04 Sep 2008 12:57:00 GMT

Next month there will be a free one day SQL event in Greenville, SC. It is part of the national SQLSaturday events and looks to be a great time. There are tracks for BI and data management. I will be presenting a session on 'Dynamic Management Views' in the data management track.

A list of speakers can be found here. The schedule is still being fleshed out so make sure to check the site for a while to get the full list.

If you will be in the area, or can get there, come check out what should be a day of geeky goodness!

Google Chrome - another round of browser war headed our way?

WhitneyW — Tue, 02 Sep 2008 13:33:41 GMT

On Google's blog site they are announcing the release of their own browser, Google Chrome. The initial launch will be tomorrow, supporting Windows only.

So what do you think about a new entrant into the browser space? Normally, I would say more competition is always better. Here, I have some reservations as Google already has such a strong position within the browser.

I suppose this will give some fresh fodder for the flame wars that occur on various sites. Good times... ;)

IE 8 Beta 2 now available

WhitneyW — Thu, 28 Aug 2008 12:18:23 GMT

The new beta is available for download. Head on over to the IE blog for details and a download link.

SQL Server vNext wish-list

WhitneyW — Wed, 20 Aug 2008 20:13:10 GMT

Aaron Bertrand has posted his wish list for the next version of SQL Server. As usual, I'm in solid agreement with Aaron's post.

What's that you say? A version just shipped? I know, I know...but it's never too early to start asking for the functionality you want to see. While I'm happy he included my Connect item for SQL Intellisense down-level support I really have hopes of it making a service pack for 2008.

So if you have the next killer feature floating around in your head don't wait until you run across a SQL product team person somewhere, go to Connect and make it a reality!

SQL Server 2008 RTM!

WhitneyW — Thu, 07 Aug 2008 01:55:44 GMT

Hooray, it's finally here!

Head on over to MSDN or TechNet and get your download on..

OT: It's iPhone love!

WhitneyW — Tue, 05 Aug 2008 17:31:44 GMT

A non SQL related post today. Last week I gave in to my curiosity and bought an iPhone. I was already with AT&T and a little intrigued by the ActiveSync integration. After peppering Anthony Handley (I owe him several beers) with all sorts of questions about his iPhone experience I pulled the trigger.

This might be the only time I can remember that I went into a cellular company's store and didn't leave with a device. Nothing like spending three hundred dollars and only having a receipt to show for it...The device arrived three days later and the folks at the AT&T store ~~tortured me~~ assisted me with activation.

I was coming from an HTC S620 (T-Mobile Dash) and honestly didn't know what to expect. I was preparing myself to be disappointed and need to roll back to the old device. My experience could not have been further from that thought. The touch screen is far easier to deal with than I imagined. It took a day to get used to the input direction versus the thumb typing of true keys.

After a week of use you couldn't pry this little guy from my hands. Having my music, video, email, and phone all in one device has been fantastic. Not to mention the app store and other assorted items like GPS in the 2.0 firmware. Syncing with Exchange has been the same experience as my prior WM6 device, but the look and feel are worlds better. Rendering of email is spot on to Outlook. The browsing experience can't even be compared, the ability to manipulate the page to a given size is a great feature.

I haven't experienced any of the nagging issues that seem to be reported online. But then again I've been running Vista for over a year without the first driver issue. Maybe I just lead a charmed life?

I have to say this really changes my perception of Apple's place in the enterprise. I am curious what the laptop experience would be like for me since I spend most all my time in Microsoft applications and server products. Maybe I should email Anthony... :)

How to quickly get stored procedure text

WhitneyW — Tue, 22 Jul 2008 21:00:00 GMT

I've answered this question twice today so I thought it made good sense to blog it. I am going to give three examples that will quickly get you to the text of a stored procedure or other object type. I am assuming you are looking to skip the [Database Name] --> Programmability --> Stored Procedures path of Object Browser, although the new "DROP and CREATE to" option in SQL 2008 is nice. We will be using the following methods today:

OBJECT_DEFINITION()
sys.sql_modules
sp_helptext

First up is OBJECT_DEFINITION(). This system function takes an object_id and returns a varchar(max) output of the object source text. There are multiple types that can be used with OBJECT_DEFINITION, check the BOL reference for a complete list. The syntax for this function is:

SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.GetCustomer', 'P')) AS objectText;

Next up is the object catalog view sys.sql_modules. This view returns a row for each object that is a SQL language-defined module. Again, check the BOL reference for your complete list of types. This view returns many useful pieces of information but today we are only after the definition column, which contains the SQL text of the module. Here's the simple query:

SELECT [definition] AS objectText
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID(N'dbo.GetCustomer', 'P');

Last is the old standby sp_helptext. This system stored procedure takes and object name (and potentially a column name) and returns the definition of multiple object types with a 255 character row output. The source for this stored procedure is sys.sql_modules, referenced above. Here is a sample call to this stored procedure:

EXECUTE sp_helptext @objName = N'dbo.GetCustomer';

The one additional option provided by sp_helptext is the ability to return computed column definitions. To get that detail we simply add the columnname parameter:

EXECUTE sp_helptext @objName = N'dbo.Customer', @columnname = 'CustomerComputedColumn';

So the next time you have that "what does this object do?" moment give one of these options a try.