Community
News and thoughts about the IT communities I'm a member of
Turns out that I'm spending an extra week in Los Angeles, but this time its for something new for me: I'm helping Brock Allen and Jason Diamond with next run of Guerrilla ASP.NET 2.0 at the Hilton South Bay.
In some ways, this is coming full circle for me. Many years ago, I took DevelopMentor's ASP.NET 1.0 class (when the product was still in Beta) and that's what got me excited about what DM does. Its going to be fun to getting back to ASP.NET after a year or so away from it on a day-to-day basis.
There's a Zen proverb that accurately describes this book: "If you understand, things are just as they are; if you do not understand, things are just as they are."
What do I mean by this? If you already understand writing OLAP queries, this book doesn't really introduce anything that likely to be new to you. If don't, Joe does a decent job of explaining the basics and demonstrating useful techniques in standard SQL and even, albeit briefly, Microsoft's MDX language. He presents things just as they are. There's a few things I like about this book and some I don't. Let's start with the negative.
- Its Joe being Joe. Okay, that's not bad or wrong per se, but here again he goes on his traditional rants about vendor-specific extensions, the differences between records and rows and the inappropriateness of identity based primary keys. If you are used to reading or listening to Joe, you expect this. If not, then you'll probably find these tangents interrupt the reading flow significantly.
- Its not T-SQL: If you're buying this book specifically to become a better T-SQL Developer, it can certainly help you do that. However, understand that Joe writes standard SQL, not Microsoft's T-SQL, so not all of his examples are directly usable. This is especially true for one of his ROW_NUMBER() examples. On page 73, he has a query that uses this function to compute the median of vector and references the computed values in the where clause. SQL Server 2005 doesn't support this. The example at the end of this post shows how to do write a similar query in SQL Server 2005.
- There might be a bug or two: Joe states on pages 51 and 52 that the MERGE INTO statement exists in SQL Server 2005. So far, I've been unable to prove this, at least as far the RTM version goes. I'm working with Niels Berglund to see if it was a feature in early versions (e.g., Alphas, Betas and CTPs).
Those negatives granted, what was good about this book -- and why do I suggest buy it?
- Its Joe being Joe. Personally, I appreciate his get to the point and show me code style. This is a book you can pick up and read in a few hours and come out with a better understanding of how to use SQL to do analytic queries. Its also a good -- but limited -- primer on OLAP concepts and terminology. If you're looking for a good place to take your first steps into this world, this book is a good choice.
- Chapter 11 on computing and using Correlations is outstanding: A while ago, there was another book on the market -- Data Mining and Statistical Analysis Using SQL by Lovett and Trueblood -- that was the book on doing statistical computing with SQL Server. I'm not sure that APRESS is still printing it, but I see that its available on EBay and Amazon Marketplace. Joe does a nice job covering this particular topic in five pages.
- There's lots of other chapters worth noting: Chapters 12 and 14 on distributions and regressions respectively are also very good. Chapter 18 on MDX is a too-brief but solid introduction to that topic. Joe doesn't try to teach you everything you should know on this complex topic, but again, its a great place to get your first foothold.
I think this book is a solid choice to make if you are interested in learning more about OLAP and Analytics from the SQL language point of view. It helps you understand these concepts "just as they are."
Median-by-RowNumber() Example for SQL Server 2005
-- Prepare table and fill with data
drop table dbo.foobar
go
create table dbo.foobar(X smallint not null)
go
set nocount on
declare @i int
set @i = 0
while(@i < 1001) begin
insert into dbo.foobar values (cast(rand() * 32767 as smallint))
set @i = @i + 1
end
go
-- Get the computed average
select avg(X) as Average from dbo.foobar
go
/* Replica of Joe's query
select avg(X),
row_number() over (order by X asc) as hi,
row_number() over (order by X desc) as lo
from dbo.foobar
where hi in (lo,lo+1,lo-1)
*/
-- Find the median values
with f as (
select X,
row_number() over (order by X asc) as hi,
row_number() over (order by X desc) as lo
from dbo.foobar)
select X as median from f
where hi in (lo,lo+1,lo-1)
go
Jacob dually notes at http://blogs.msdn.com/jacobcy/archive/2006/08/09/693810.aspx that Omaha had a larger crowd for their August MSDN event than Boston. Good for the folks back home, it is great to see the community staying strong. If you were wondering where I was...
Boston. Not at the MSDN Event either.
Next time I'm thinking about going. --just to see if we can make a it an attendance tie.
I'll be presenting to the New Jersey SQL Server user's group tommorow (http://njsql.org/blogs/user_group_news/archive/2006/05/15/30.aspx) night. Details here.
Hope to see you there!
My friend and fellow Omahan, more commonly known as "BlackCoil," posted this picture on Flickr a couple of days ago. Its worth a looksee. http://www.flickr.com/photos/blackcoil/137710609/in/set-71269/. He's a really sharp photographer not to mention a topnotch coder. I use his URL remapping tool (http://www.url123.com) frequently. He's recently released a new site thats Marginally NSFW called Goddess Models.
Good work Francesco!
Last week was BKWY's annual meeting in Omaha. BillG came to town, but I didn't go (as if I could afford a share of that...). Its always interesting to see us spit and polish up for the inbound moneybags. Here's an example from this month's issue of Hemispheres, the UAL inflight magazine.
http://www.hemispheresmagazine.com/pdf/insight/Omaha_web.pdf
"order has shipped."
My copy of Bob and Dan's "A Developer's Guide to SQL Server 2005 has finally departed from whatever cave Amazon loaded them into. Can't wait to see how different it is. Its going to be a lot of reading too, its accompanied by Johnson's ADO.NET Advanced Topics book that I'm also looking forward too.
Is it time for me to take up the writing effort again? Maybe. I've got ideas, but what would you be interested in?
Doh! Sorry to be late getting this on the wire but the Heartland Developers' Conference for 2006 is has been announced. This year its in Omaha and runs from 26 October and 27 October. Hopefully I won't find myself in China again...
In its 3rd year, HDC06 is the largest independent annual professional Microsoft development conference in the nation, organized by user groups, and focused on .NET development and other emerging Microsoft technologies. HDC provides regional developers the opportunity to experience sessions usually reserved for Tech-Ed and PDC at a much lower cost while retaining the same nationally known presenters. Over 2 days, 23 sessions, 2 tracks, and several networking opportunities, HDC06 is where you’re find top knowledge experts that can prepare you for next level development.
For only $125 Early Bird/$175 standard, each Attendee Pass includes:
1 Conference pass to 2 keynotes and all 23 ninety minute sessions
- Meals including breakfast, lunch, snacks, and coffee breaks
- Access to the “Developer Lounge” expo and activity center
- Wireless internet access throughout conference area
- Pre conference networking party including drinks and food
- Thursday night “Developer Jam” including food, drink, and a casino for prizes!
- Attendee Kit and a chance to win a monster 64 Bit Alienware computer
The speaker list is looking awesome too: Microsoft's own Jeff Brand, Don Bryner and Jacob Cynamon. MVPs Dave Donaldson, Robert Hurlbut, Rocky Lhotka, Javier Lozano and Andrew Troelsen and community rockstars Craig Utley, Phil Wolfe, Robert Boedigheimer, Tim Gifford, Matt Milner and Nick Parker. I'm planning of giving a couple of talks on SQL Server 2005 for Developers.
Register here. And remember, we're talking a mere $125 one of the best Microsoft-focused technical conferences in the Midwest.
As you might imagine, when you fly your share of miles (another 1,480 today), you really enjoy little peeks into the other side of the business. One of my favorite Podcasts from the airline industry is Betty in the Sky with Suitcase. I've been wondering why she hadn't done an episode lately. Well, she has! Enjoy!
Subscribe the feed here: http://betty.libsyn.com/rss.xml
Conor Cunnigham kicks it off with:
On behalf of the Query Optimization Team for Microsoft's SQL Server product, welcome to our humble virtual abode. We decided that we'd start a blog to help people better understand query plans, physical schema design in databases, making your application perform better, and anything else related to query optimization. Furthermore, we'll be posting tips and tricks that we learn from our work with customers so that others can benefit from those exchanges.
They are coming out of the gate strong, too. There's already an article up about usimg Computed Columns to optimize certain types of queries in SQL Server 2005.
RSS Feed Here.
As usual, some of the best questions come from the newsgroups. And yet again, there was one that piqued by attention. The question essentially boiled down to “can you put a trigger on a login?” That is, the questioner wanted to execute some arbitrary T-SQL code whenever a user logged into a given instance of SQL Server. I’m not sure how you’d go about doing this in SQL Server 2000, but in 2005, two features make this possible – if you don’t mind building a bit of your own plumbing. These features are EVENT NOTIFICATIONS and the Service Broker.
Event Notifications are essentially fixtures that your create – using T-SQL – that allows SQL Server to dispatch messages about system events like logins, data definition language statements, and some trace events to Service Broker Services and Queues. The Service Broker Service acts as an address for the messages, while the Queue holds the messages until some a process like a Stored Procedure process them. Some number of instances of that Stored Procedure will be spawned by a process called Activation when a message arrives in a Queue. So these two features work together to deliver information about system events to a stored procedure that gets executed in response to that event, much like how a trigger gets fired in response to events.
What started out as fairly short post turned into a fairly long item, so I've contued this over here, while the annotated source code (with bonus features) is over here.
Thanks to Erland Sommarskog for this gem:
So why this is dreaded? Yes, I know that Joe Celko pukes on it, but isn't that a strong indication of that it can be a sound design in the right situation?
There’s an interesting note in the SQL Server 2005 SP1 March CTP, and yes, this is really a bug fix. I’m surprised, however, that more people didn’t get caught by it. Find the topic “Performing Operations on User-defined Types” in Books-On-Line and look at the change history. You’ll see a note reading:
“Added information about the following behavior change in SQL Server 2005 Service Pack 1: Strings that are returned by user-defined type methods assume the collation of the database in which the user-defined type was created, regardless of the current database.
In the earlier version of SQL Server 2005, strings that are returned by user-defined type methods assume the collation of the current database.”
If you’re not all that familiar with collations in SQL Server, you might not be sure why this really is a fix and not a… well, something else. It is pretty easy to see, however, if you give it a try on the RTM version and the CTP SP1 version. First, start with a simple enough CLR-based User Defined Type. Here’s an example:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedType(Format.UserDefined,MaxByteSize=8000,IsByteOrdered=true)]
public class SqlStr : INullable,IBinarySerialize
{
private bool _IsNull = true;
private string _Str = string.Empty;
public void Read(BinaryReader r)
{
_Str = r.ReadString();
_IsNull = r.ReadBoolean();
}
public void Write(BinaryWriter w)
{
w.Write(_Str);
w.Write(_IsNull);
}
public override string ToString()
{
return _Str;
}
public bool IsNull
{
get
{
return _IsNull;
}
}
public static SqlStr Null
{
get
{
return new SqlStr();
}
}
public static SqlStr Parse(SqlString Source)
{
if (Source.IsNull)
return Null;
SqlStr s = new SqlStr();
s._Str = Source.Value;
s._IsNull = false;
return s;
}
}
Okay, so that’s pretty simple. We also need a bit T-SQL to demonstrate this behavior. Run this first in the RTM version of SQL Server 2005, changing the assembly path to work on your machine:
use master
drop database scratch1
drop database scratch2
go
sp_configure 'clr enabled',1
go
reconfigure
go
create database scratch1
alter database scratch1 collate Latin1_General_CI_AI
create database scratch2
alter database scratch2 collate Latin1_General_CS_AI
go
use scratch1
go
create assembly sqlstrAsm
from 'c:\etc\ktegels\SqlStr\SqlStr\bin\Debug\SqlStr.dll'
go
create type SqlStr
external name sqlstrAsm.SqlStr
go
create table dbo.v(id tinyint identity(1,1),s SqlStr)
go
insert into dbo.v values ('ABC')
insert into dbo.v values ('abc')
insert into dbo.v values ('Abc')
insert into dbo.v values ('abC')
insert into dbo.v values ('aBc')
insert into dbo.v values ('aBC')
insert into dbo.v values ('ABc')
insert into dbo.v values ('AbC')
go
select id,s.ToString() from scratch1.dbo.v order by s.ToString()
go
use scratch2
go
select id,s.ToString() from scratch1.dbo.v order by s.ToString()
go
sp_configure 'clr enabled',0
go
reconfigure
go
Your output should be two result sets, the first called from the Scratch1 database, the second, from Scratch2.
id / v
1 / ABC
2 / abc
3 / Abc
4 / abC
5 / aBc
6 / aBC
7 / ABc
8 / AbC
id / v
2 / abc
4 / abC
5 / aBc
6 / aBC
3 / Abc
8 / AbC
7 / ABc
1 / ABC
That makes sense when stop to consider that the second result is being order case sensitively, where as the first set is case insensitive. If you run that same script in SQL Server 2005 Service Pack 1 instances, you get different results. And that’s because the case insensitive ordering of Scratch1 gets used in Scratch2.
id / v
1 / ABC
2 / abc
3 / Abc
4 / abC
5 / aBc
6 / aBC
7 / ABc
8 / AbC
id / v
1 / ABC
2 / abc
3 / Abc
4 / abC
5 / aBc
6 / aBC
7 / ABc
8 / AbC
So, why isn’t this a change actually a bug? To understand that, you have know what SQL Server does with “normal” string data in cross-collation situations. And that’s easy enough. Change the script to this and run it in the RTM version:
use master
drop database scratch1
drop database scratch2
go
create database scratch1
alter database scratch1 collate Latin1_General_CI_AI
create database scratch2
alter database scratch2 collate Latin1_General_CS_AI
go
use scratch1
go
create table dbo.v(id tinyint identity(1,1),s char(3))
go
insert into dbo.v values ('ABC')
insert into dbo.v values ('abc')
insert into dbo.v values ('Abc')
insert into dbo.v values ('abC')
insert into dbo.v values ('aBc')
insert into dbo.v values ('aBC')
insert into dbo.v values ('ABc')
insert into dbo.v values ('AbC')
go
select id,s as 'v' from scratch1.dbo.v order by s
go
use scratch2
go
select id,s as 'v' from scratch1.dbo.v order by s
go
When you look at the output, you’ll get it. All this change does is make a UDT’s string-returning function behave like another string functions in a cross-collation situation.
So with that sorted out, I’m sort of back to my normal sort of self.
LAMP: Linux, Apache, MySQL and PHP. A pretty nice combination of technologies for easily building interactive and Database-backed Websites. I love the concept, its one that Microsoft didn't really captialize on with .NET 1.x. Sure, you had Windows as the OS, not free, but affordable for businesses. You also have IIS which is easy enough to configure and has improved considerable with the introduction of IIS6. And, yes, ASP.NET 1.x was most certainly a great web applicaiton platform. But where was the bit that that competed with MySQL? Well, that was MSDE. Enough said. Little wonder why there's been a lot of interest in SQL Server 2005 Express Edition.
But something about that offering just didn't make out the gate in time. Namely:
- Like the MSDE story, there wasn't a free GUI for working with yet. For better or worse, XM didn't make it, and SSMSEE wasn't ready at RTM. I'm glad they didn't hold up for it, but its still somewhat holding the ASP.NET 2.0 platform for going head-to-head with LAMP, IMHO.
- Towards the end of the development cycle of SQL Server 2005, the SQL Team decided that Express Edition really should support Reporting Services and Full-Text search. However, that decision was really made too late in the cycle to include them in the RTM. Microsoft said these features would be available with SP1.
And so they are. Mission almost accomplished. It turns out that a CTP version of SSMSEE is now available at [0], so you can start getting a feel for how tool will help you write great applications. Books On-Line has also been updated to reflect the changes in it. You can download that from [1]. You also download the first CTP for Express Edition with Advanced Services from [2].
I believe the March 2006 CTP version of SQL Server 2005 will show as 9.0.2040.0. Don't worry, it still runs with .NET FX 2.0.50727.42.
[0]: http://www.microsoft.com/downloads/details.aspx?familyid=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&displaylang=en
[1]: http://www.microsoft.com/downloads/thankyou.aspx?familyId=19db0b42-a5b2-456f-9c5c-f295cdd58d7a&displayLang=en&oRef=http%3a%2f%2fwww.microsoft.com%2fsql%2fctp_sp1.mspx
[2]: http://www.microsoft.com/downloads/details.aspx?familyid=57856cdd-da9b-4ad0-9a8a-f193ae8410ad&displaylang=en
Oh, did I mention that if you want to win yourself $10,000 in the Made In Express contest, you can? Sounds like a heck of an opportunity for somebody not only write some really cool code but also put some green in bank.
As Greg and Wally have reported, I've been thinking about bringing back Take Outs as a PodCast. I'm pleased to announce with the the hosting support offered by Steve Wynkoop of SSWUG, its now a reality. The need-to-knows:
The RSS feed: http://feeds.feedburner.com/sswugorgradio
iTunes friendly feed: http://feeds.feedburner.com/SswugorgRadio?format=pcast
Episode one show notes and download file.
Let's roll!
Like a lot of folks, I’m thinking about giving PodCasting a try. There’s really three reasons for wanting to do so:
- While I’m not sure about it yet, it looks like I could do twenty minute podcast two or three times a month without disrupting things too much.
- It would be easier to cover "short take" items like answers to questions, community events and what’s going on in my life in that format that writing blog posts about it.
- All the cool kids are doing it: I’ve always been impressed with Dr. Greg Low’s SQL DownUnder and have just flat out enjoyed Wally and Paul’s ASP.NET PodCast.
Some of the parameters I’m thinking about:
- No, this would not be a once-a-week thing. More likely than not, it’d just be whenever I’m on the road with a couple of hours to kill.
- Each episode would probably have regularly occurring parts like:
- Check this out: A quick rundown of techie-blog postings that I think you might be interested in (ala Take Outs, if you remember that.)
- Since You Asked: Questions and Answers about SQL Server, XML, .NET or my non-technical interests -- mainly food and beer. Questions would come from you, the reader/listener, my students o from the newsgroups.
- Read of the Moment: This would be an informal review of whatever book I’m reading (or audiobook) that I’ve most recently been reading, and I think is good and/or not-so-good about it and why this book might be of interest to you -- or not.
- Five Questions For: As the show gets rolling along, I’d like to do interviews with folks who share my passions and interests. If I can figure out how to bridge the audio, I probably will. If not, I’ll just read you their answers to my questions.
- Where We At?: Since I do travel a lot, I’d talk about where I’m at, my trip there and anything that happens to strike me as interesting about getting from point A to Z.
- Potpourri for $100: Most this will be just talking about non-techie stuff like, well, beer. And... Food. And maybe movies and books. But probably mostly beer. And life in Omaha, etc. etc.
Not every episode will have each part. The first three and "Where we At" will probably occur in every episode. "Five Questions For"