My fellow MVP, Erland Sommarskog, is one of the few people I know who is frighteningly smart. He goes beyond mere SQL Server guru-hood; his knowledge of SQL Server dwarfs any mere human's. When he writes about a topic, he doesn't just create a blog entry; he writes a book length article and puts it on his Web site here.
One of the most valuable things he has done has been to compile all the information you could ever want or need to know about using array and list-type structures in SQL Server into a set of articles collectively called Arrays and Lists in SQL Server. This is a comprehensive--over the top comprehensive--description of using just about every possible technique of using data structures in Transact SQL, as well as testing how each technique performs so that you can decide the best way to do something.
I have referenced this work often, including when writing the AppDev SQL Server 2005 and 2008 courseware.
Erland just released an updated version of the articles, which brings them up to date through SQL Server 2008, including one of my favorite new features, table-valued parameters. There are now three articles and two appendices (the latter of which cover the performance data). One article is for SQL Server 2000 and before, another for SQL Server 2005, and another for SQL Server 2008. The titles are a little misleading, since you really need to read the latter two article to get the full, current information. The 2008 article only covers table-valued parameters; the other techniques are still valid as well.
Even if you've read the articles before, they are worth a re-read for SQL Server 2005 and the new one for 2008. And buy him a drink the next time you see him at a conference!
You may have heard that Oracle is attempting to buy Sun Microsystems, which now owns MySQL, the free, open source relational database system that powers a lot of the Internet.
There is significant concern that if Oracle gets control of MySQL that they will gradually decapitate it, since it competes with its own (very expensive) database systems. The European Union is holding hearings next month, and they need input.
Here is the text of an email that I received today from a former member of the core MySQL team who is still very active in MySQL development:
I am contacting you because you have in the past shown interest in MySQL and from that I assume you are interested in the future well-being of MySQL.
Now you have a unique opportunity to make a difference. By signing the petition at http://www.helpmysql.org you can help affect the future of MySQL as an Open Source database.
You can find more information of this on my latest blog post at: http://monty-says.blogspot.com/2009/12/help-keep-internet-free.html
Help us spread the world about this petition! http://www.helpmysql.org is available in 18 languages and every vote is important, independent of from where in the world it comes!
If you know people that are using MySQL, please contact them and ensure they also sign the petition!
Regards,
Monty
Creator of MySQL
PS: If you already have signed the petition or know about it, sorry for reminding you about this! Because of the importance of this issue, I am trying to contact every person that I have ever communicated with regarding MySQL.
Consider taking action if you like having an open source alternative to Oracle and SQL Server. Much as I love and use SQL Server, both Oracle and Microsoft need this kind of competition!
Thanks!
Don
I'm very pleased to announce that Robert Green and I are teaming up to teach an online version of AppDev's ASP.NET 3.5 course in February. After years of teaching various AppDev courses live, it will be interesting to present the material via Microsoft's Live Meeting, to attendees who are all over the place.
All the details and registration information are available on AppDev's onlineILT page, along with information about other upcoming classes. We'll follow pretty much the same format as the live classes, with plenty of time in each chapter for questions.
The only downside I can see is that the class starts at 6:30 AM my time (Fairbanks, Alaska). 
I'll be up for it, but will have a good supply of caffeinated tea on hand!
I hope you can join us!
Don
With all my travel for conferences and other events lately, I was a bit slow to install Beta 2 of Visual Studio 2010. But now I'm home for the winter, and getting caught up with life on the bleeding edge. And one of the priorities was the "new" beta (actually out since mid October).
Wham! Alas, I was hit by an installation issue, a rather befuddling one. During initial installation, I received this error message: "Microsoft ASP.NET MVC 2 - Visual Studio 2010 Tools: [2] WARNING! Setup Failed for optional component Microsoft ASP.NET MVC 2 - Visual Studio 2010 Tools. MSI returned error code 1603." Various Google searches on that didn't lead me to anything useful. I did a reinstall/repair, which finished successfully. So I thought that all was well.
But what was weird was that VS 2010 was installed, and worked fine. The only problem was that the MVC 2 project templates didn't appear in the New Project dialog box. Not good, since my next project is to work with the new MVC features and support in VS 2010.
After going round and round with the problem, the details of which I won't include here since none proved useful, I posted a message on the www.asp.net MVC forum, of which I'm a moderator. Fortunately, Jacques Eloff, a member of Microsoft's ASP.NET team, monitors that forum and jumped in to help. He had me use Microsoft's Collect.exe tool to gather various log and installation information, which pointed to the problem and potential solution.
Unfortunately for the forum, at that point we took the discussion offline because I had to send Jacques some log files (dd_AspNetMvc2.msiXXX.txt and dd_VS2010ToolsMvc2.msiXXX.txt generated by collect.exe), but here is my description of the resolution that I posted to the forum:
With Jacques' help offline (I had to send him some log files), the problem is now solved. I want to post the resolution in case anyone else faces the same problem.
The problem had something to do with the failure to register the MVC templates, done using devenv.exe /installvstemplates, which registers project and item templates. This was being manifested in the installation logs with these items, which Jacques dug out:
(SERVER) MSI (s) (14:08) [10:51:02:619]: Note: 1: 1722 2: VisualStudio_VSSetup 3: C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe 4: /installvstemplates
(SERVER) MSI (s) (14:08) [10:51:02:740]: Product: Microsoft ASP.NET MVC 2 - Visual Studio 2010 Tools -- Error 1722. There is a problem with this Windows Installer package. A program run as part of the setup did not finish as expected. Contact your support personnel or package vendor. Action VisualStudio_VSSetup, location: C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe, command: /installvstemplates
Apparently the repair/reinstall option just affects items that were successfully installed, so it wasn't even trying to repair this problem.
In my case, the fix was rather simple (although time consuming, given how long it takes to run through VS 2010 installation!):
1. Uninstall the Visual Web Developer component of VS 2010
2. Reinstall the Visual Web Developer component
Poof! Problem solved. For good measure, I rebooted between the steps, but I doubt that helped any.
Thanks, Jacques!
So we really don't know why the initial installation failed, since the initial 1603 message is a pretty generic error. Hopefully Microsoft will figure out the problem before release so that no one ever again has to feel my pain of being deprived of MVC. (Which, by the way, is looking to be a vast improvement over Web Forms. Stay tuned!)
One of the best projects that I've been involved with this year--other than the SQL Server 2008 AppDev courses, of course!--was the SQL Server MVP Deep Dives from Manning Publications. This is an extraordinary book for two main reasons:
- The chapters were written by 53 Microsoft MVPs, including some of the best and brightest on both the DBA and developer sides of SQL Server. It blows my mind that such a diverse herd of cats came together to produce such a book. The result is good, really really good.
- All of the authors' royalties--every last cent--is going to an amazing charity, War Child International.
Here's the official description:
This is no ordinary SQL Server book. In SQL Server MVP Deep Dives, the world's leading experts and practitioners offer a masterful collection of techniques and best practices for SQL Server development and administration. 53 MVPs each pick an area of passionate interest to them and then share their insights and practical know-how with you.
SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. In each, you'll find concise, brilliantly clear chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance.
I was privileged to contribute chapter 16, Table-Valued Parameters, one of the best of the new T-SQL features in SQL Server 2008.
The project was conceived by Paul Nielsen and managed to completion by Paul, Kalen Delaney, Greg Low, Adam Machanic, Paul S. Randal, and Kimberly L. Tripp, all of whom also contributed at least one chapter.
The book launched at the SQL PASS Summit last week in Seattle. It's been a long time since I've been involved in a book launch, but this one was amazing. As you can see in the photos below, the event started with a few words by the main participants, followed by a long line of book signings. Some 30 of the authors were on hand to sign books, and a LOT of people brought their books for a signature. On top of that, we signed copies for Microsoft executives and each other. It was a hoot!
If you have anything at all to do with SQL Server, please consider picking up a copy. If you buy it through this link, War Child gets a bit larger slice of the price. And even if you don't want the book, please consider making a donation directly to War Child! They do amazing work.
Microsoft today released preview 5 of the upcoming AJAX 4.0. This is the AJAX version that will be included with Visual Studio 2010 and ASP.NET 4.0. It has a ton of great new features that you can learn about and download on the AJAX CodePlex site.
One of the new features in preview 5 is Dynamic and Recursive Templates. That should be interesting to play with!
Happy coding!
Don
It's going to be a busy fall for me, at least for a month or two. I'm really lucky to have been invited to speak at some fabulous conferences and user groups.
September 23-24
(Ottawa is the 23rd and Montreal the 24th. I don't have location information yet though.)
I'm meeting up with some family in Montreal, so am going to visit the Ottawa and Montreal .NET user groups. At both groups I'll be spending about an hour on these two topics:
SQL Server Efficiencies: Sparse Columns and Filtered Indexes
One of the tools that Microsoft provides to help minimize data storage sizes in SQL Server 2008 is sparse columns. Sparse columns provides an efficient way to store data that is predominantly null by requiring no disk storage space for the null values. This sounds great, but there is a cost: in a field marked as sparse, the non-null data takes up a bit more storage space than if the field weren’t sparse, either two or four additional bytes depending on the data type of the field. A new feature in SQL Server 2008 that is useful with sparse columns—although usable even if a table doesn’t have sparse columns—is filtered indexes. This is an index with a WHERE clause that allows you to index only a subset of the rows in a table. The index favors a subset of the data in the table, which is very useful with sparse columns because most of the data in the field is null. You can create a filtered index on the sparse column that includes only the non-null data. Doing so optimizes the use of tables with sparse columns. This session will explore both of these great new features that can raise the efficiency of your databases to new levels, both when used together and used apart.
[This session is adapted from part of the AppDev SQL Server 2008 courseware I wrote. Seems to be a topic of interest, since I was also asked to present it at SQL PASS (see below).]
Unleash Management Studio 2.0
The SQL Server 2008 story isn’t really about productivity, but the latest version of Management Studio has definitely come of age. There are all kinds of tools and useful information throughout the environment, masking the complexity of and sometimes enhancing the underlying database and server features. During this session we’ll shine a light into the various nooks and crannies of this useful tool for both developers and administrators. You’ll learn about the new activity monitor, Intellisense (and how to keep it from driving you nuts, particularly if you are used to Visual Studio), T-SQL debugging, object explorer details enhancements, and lots more. You’ll learn various tips and tricks to make the most out of this tool. I personally guarantee you’ll have at least one WOW! moment, and come away with a list of features that you can use immediately to be more productive with this tool that you probably use so much every day.
[I've done this one a few times at conferences, with new stuff every time. It's fun to present, and attendees always seem to learn some new tricks.]
Then I'm home for a week before heading to AppDev to tape some courseware. And home a few days before off to one of my favorite places in the world: the Netherlands!
19-20 October
I'm really excited that I've been invited back to the Software Development Conference (SDN) in the Netherlands. This is a user group conference put on by the Software Development Network. I love user group conferences because people are so happy to be there and it's fun to meet and interact with such enthusiastic technologists. (That's a big reason I love to attend DevTeach in Canada.)
I'm presenting four sessions at SDC:
SQL Server 2008 Security for Developers
Have you had a run in with SQL Server or database security? Did it leave a bad taste in your mouth? Does the mumbo jumbo of database security give you the heebee jeebies? Don’t let it get you down! Come to this session and face your fears. We’ll cover the most important basics of security, why the roadblocks are there and how to get around them safely and securely. You’ll learn the terms, see how security features protect your data and server, and how to work within the system when writing code that runs within the server or in external applications. Come with your questions and be prepared to leave your fears behind.
User-Defined Types and Aggregates in SQL Server 2008
Running .NET code within SQL Server 2008 has opened up a lot of amazing possibilities, providing flexible options for working with relational data. But defining your own user-defined types and aggregates is easily the most complex and powerful thing you can do with SQLCLR code. That power and complexity puts off some developers, but you shouldn’t miss out! SQL Server 2008 expands UDTs in great new ways, making it all the more important to know how to use them. In this session we'll explore how to create custom types that perform well, write attributes correctly, and use the right serialization formats. We'll also talk about issues you should consider when designing your own types, as well as when it might be better to use other options.
AJAX 4.0: Rich Internet Applications Come of Age
The ASP.NET team at Microsoft just never sits still. They are enhancing the AJAX features of ASP.NET with great new stuff like client-side template rendering, declarative instantiation of behaviors and controls, a DataView control, markup extensions, and new bindings. Everything is in flux, but there are few parts of ASP.NET as exciting as what is coming down the road. During this session we’ll explore these new features as implemented in the current release and see how you can make your Web applications an even better experience for users. Microsoft’s plans for AJAX will blow your mind!
Revenge of the Client: AJAX 4.0 Data Binding
AJAX 4.0—scheduled to release as part of Visual Studio 2010—is introducing a lot of nice new features, but none are as revolutionary and interesting as its support for data binding. By using data binding and client templates, along with a few other new features, you can create a complete, robust data application all within an HTML page loaded in the browser. The pages can have full CRUD support—the standard Create, Retrieve, Update, and Delete database operations. This session will explore these new data binding features, and look at a complete application that works with SQL Server data, entirely within “static” HTML pages. These new client-side features open up great possibilities for rich, client-side data applications.
I'll spend a few days in the Netherlands after the conference. Not sure what I'm going to do yet. Maybe some paddling?
Then home for a whole week (wow!) and off to SQL PASS in Seattle.
2-5 November
I love SQL PASS! Good friends and amazing technical geek-out. I'm doing just one session, so most of the week I'll get to hang out and soak it all in. And this year I've gotten smarter: I'm not leaving until Friday morning so I can hang out Thursday night.
SQL Server Efficiencies: Sparse Columns and Filtered Indexes
One of the tools that Microsoft provides to help minimize data storage sizes in SQL Server 2008 is sparse columns. Sparse columns provide an efficient way to store data that is predominantly null by requiring no disk storage space for the null values. This sounds great, but there is a cost: in a field marked as sparse, the non-null data takes up a bit more storage space than if the field werent sparse, either two or four additional bytes depending on the data type of the field. A new feature in SQL Server 2008 that is useful with sparse columnsalthough usable even if a table doesnt have sparse columnsis filtered indexes. This is an index with a WHERE clause that allows you to index only a subset of the rows in a table. The index favors a subset of the data in the table, which is very useful with sparse columns because most of the data in the field is null. You can create a filtered index on the sparse column that includes only the non-null data. Doing so optimizes the use of tables with sparse columns. This session will explore both of these great new features that can raise the efficiency of your databases to new levels, both when used together and used apart.
If you're able to attend any of these events, please come up and say hi!
I love security. Not necessarily being secure in life--life itself is a risk, after all--but computer security is interesting. One bit of proof of that is that I always push for a dedicated security chapter in any courseware I develop for AppDev. (Although I have to admit that I messed up with the SQL Server 2008 Integration Services course I'm updating right now...security will still be part of the Package Management chapter, not a separate chapter. Next time I'll rectify that!)
If you're into SQL Server security, Microsoftie Laurentiu Cristofor's blog is a must for your RSS feed. Laurentiu doesn't write as much about security these days, but the archives are full of great stuff.
Laurentiu recently wrote an interesting post, Basic SQL Server Security concepts: ownership, CONTROL, TAKE OWNERSHIP. In it he talks about permissions related to object ownership. I have to admit that a few things were of interest to me, either because I hadn't encountered them yet in the wild, hadn't really thought about them, or it's always good to be reminded of them:
- The owner of an object ... most importantly, cannot be denied those permissions while he continues to be an owner.
- The CONTROL permission [is] the next best thing after ownership of the entity, but it's not quite as powerful as ownership. The main difference is that a grantee of CONTROL can still be denied some other permissions on the entity. [I love his example: I can have CONTROL of a table, but can be denied SELECT permission on it. When would I ever use that??? :-)]
- TAKE OWNERSHIP [can be] used to selectively grant someone the ability to willingly become the owner of an object. [Laurentiu's emphasis. I had never worded this as willingly, but that's a good way to think of it.]
And best of all:
- Side effect of ownership change: A potentially surprising side effect of changing ownership of an object is that all permissions granted on that object will be lost. [Again, Laurentiu's emphasis. This is something I've never explored nor, to my knowledge, ever had a problem with. I'll have to watch this for side effects though, since I suspect they could be nasty if I weren't aware of this.
Read the whole post; it isn't long.
Practice safe computing!
The Professional Association of SQL Server (PASS) is a great organization for SQL Server professionals, both DBAs and developers. Membership is free, and gets you many nice benefits for learning SQL Server and getting involved with the community. And the annual PASS Community Summits are some of the best conferences for getting deep into SQL Server with both Microsoft SQL Server team members and third party experts. (I'll be speaking again at this year's US summit the week of 2 November.)
This week, PASS is sponsoring 24 Hours of PASS, a virtual conference that will have 24 consecutive, hour long sessions. I'll be doing a session on SQL Server 2008 security at 21:00 GMT on Wednesday (2 PM PDT)--I hope I have that right! Other speakers are Adam Machanic, Andrew Kelly, Allen White, Artemakis Artemiou, Brad McGehee, Brian Knight, Dejan Sarka, Don Vilen, Erik Veerman, Gail Shaw, Grant Fritchey, Greg Low, Jacob Sebastian, Jessica Moss, John Welch, Kalen Delaney, Kevin Kline, Louis Davidson, Paul Nielsen, Peter Myers, Peter Ward, Simon Sabin, Steve Jones, Thomas Grohser. This is a truly international group!
Best of all, the sessions will be available later for viewing any time. So even if you're interested in a session that's at 2 AM where you are, you don't have to lose sleep.
I hope to see you there!
Don
If you're reading this blog or hanging out with AppDev courseware, you're a tech person of some kind. By default, that almost certainly means that you are the technical support staff for your family, friends, and probably your neighbors (unless the neighborhood has a handy 13 year old).
Now, in one easy format and location, you can save yourself a lot of time and aggravation by pointing your tech support clients to this XKCD.com panel:
P.S. I love XKCD. When you view the cartoons on the Web site or the blog, make sure you hover your mouse over it to see the tooltip, which is often even more insightful or funny than the cartoon.
P.P.S. Below is the classic XKCD cartoon for people interested in SQL Server (or any SQL-based relational database) security. A year or two later, I still bust a gut laughing at this one!
Both of these cartoons are from XKCD, a webcomic of romance, sarcasm, math, and language. Add it to your RSS feed right now!
While preparing for a SQL PASS a year or two ago when I was doing a session about common table expressions, I had endeavored to come up with an extremely simple recursive CTE. Unfortunately, I wasn’t creative enough at the time to come up with anything, so the attendees had to sit through more practical examples.
But later I revisited the problem, and came up with this:
WITH SimpleCTE(Number) AS
(
SELECT 1
UNION ALL
SELECT * FROM SimpleCTE WHERE 0=1
)
SELECT * FROM SimpleCTE
No guarantees that I couldn’t get it even simpler, but this is the simplest I've ever seen. There may be another option for the SELECT * part of the CTE, but I’m not seeing it right now.
BUT! Obviously, this is the result of a thought experiment, and is not practical for anything else I can think of. Don’t take this as any kind of recommended practice!
One of the things that Microsoft committed to back in the SQL Server 7 to 2005 time frame was to do a much better job of updating the SQL Server documentation. They originally commited to a quarterly update, but for the most part it has been two or three times a year. Far better than just shipping a static set of documentation upon release!
One of the reasons for the regular and frequent updates was to communicate current security information about threats to SQL Server and what you can do, as an admin or developer, to keep your installation of SQL Server secure.
So you should definitely update you copy of Books Online every time there's an update. A great place to get information about updates is on the SQL Server User Education team's blog (they're the people who write the documentation). They usually only post when there is some new release, so adding it to your blog reader won't result in undue amounts of traffic.
As I type this, Refresh 9 of SQL Server 2008 BOL is online. At the moment, it's only available on MSDN online. The installation files for a local copy usually lag by a month or two. Which means that soon you should be able to download it for local use.
Happy reading!
Don
Hello! It's pretty exciting that AppDev has created the Edge to give instructors, courseware authors, and students a chance to exchange ideas and get feedback and learning all the technologies coming out these days. The pace of technological change seems to be getting faster all the time, so it's hard to figure out what is worth exploring and what is maybe interesting but not worth using in a production application. And what should just be immediately discarded. I used to be a decent generalist, but there is just too much to learn to be good at everything anymore, even "just" with Microsoft technologies!
Of late, I've worked with Ken Getz on ASP.NET courseware, and Frank Tillinghast on SQL Server 2008 courseware. I've been teaching AppDev material for more than a decade, and writing courseware and doing videos for several years. Here is my semi-official biography, mostly covering my professional life:
Don Kiely, MVP, MCSD, is a senior technology consultant specializing in developing secure desktop and Web applications that integrate databases and related technologies, using tools including SQL Server, Visual Basic, C#, ASP.NET, and XML. Don has authored and co-authored several programming books, many of which you’re likely to see in the bargain bin at your local mega-bookstore. He writes regularly for many industry journals, including Visual Studio Magazine, MSDN Magazine, CoDe Magazine, and asp.netPRO. Don trains developers and speaks regularly at industry conferences, including TechEd, SQL PASS, VSLive!, DevConnections, DevTeach, and others, and is a member of the INETA and MSDN Canada speaker bureaus. He writes courseware for AppDev and records instructional videos for them. Don is a full member of the Institute of Electronics and Electrical Engineers and the American Society of Civil Engineers. He earned a BS in Civil Engineering from the University of Notre Dame and an MBA from the University of Colorado, but fortunately learned the error of his ways. In his spare time he roams the Alaska wilderness by foot, dog sled, skis, and kayak. He is President of Second Chance League, a 501(c)3 non-profit sleddog rescue organization in Interior Alaska. Contact him at donkiely@computer.org.
I often have examples in the courseware I write of dogs and dog-powered sports. That's because my partner Carol and I have 31 dogs, most of whom are working sleddogs. We also have several retired sleddogs who are living out their days roaming free in our fenced yard, as well as a few who choose not to be sleddogs. We also have the occasional non-sleddog, such as Mardy, our Irish Shedder, who has never quite forgiven me for bringing her to live with sleddogs! Harrumph! 
Thanks for visiting my new AppDev Edge blog! I hope that over the coming months that I'll provide some useful information about the technologies I cover in courseware and related topics.