Kalen Delaney : books

Geek City: Preparing for the SQL Server Master Exam

Kalen Delaney — Thu, 03 Mar 2011 14:40:00 GMT

I was amazed at the results when I just did a search of SQLBlog, and realized no one had really blogged here about the changes to the Microsoft Certified Master (MCM) program. Greg Low described the MCM program when he decided to pursue the MCM at the...(read more)

Did You Know? MVP Deep Dives is Deal of the Day at Manning!

Kalen Delaney — Mon, 26 Jul 2010 14:45:00 GMT

If, for who knows what reason, you haven't yet got your copy of the SQL MVP Deep Dives book , you can't pass it up today. It is the "Deal of the Day" at Manning , and you can get it for only $25! Check it out at at: http://www.manning.com/nielsen/ (the...(read more)

Did You Know? More online seminars!

Kalen Delaney — Thu, 22 Apr 2010 23:20:00 GMT

I am in Tucson again, having just recorded two more online workshops to be broadcast by SSWUG. We haven't set the dates yet, but we are thinking about offering a special package deal for the two of them. The topics really are related and I think they...(read more)

Did You Know? 53 SQL Server MVPs Give Away Their Royalties!

Kalen Delaney — Wed, 30 Sep 2009 16:47:00 GMT

You've probably already read about the SQL Server MVP Deep Dives book, which will be making its debut at PASS in November. Many of my friends and colleagues have blogged about it already. But just in case… SQL Server MVP Deep Dives is an all-volunteer...(read more)

Did You Know: A New Blogger

Kalen Delaney — Mon, 27 Jul 2009 22:36:00 GMT

I invite you to check out the posts by one of SQLBlog’s newest bloggers: Ben Nevarez . I first met Ben online when he started commenting on my Inside SQL Server 2005: The Storage Engine book, with the most insightful comments on my oversights as well...(read more)

Did You Know? My book is at the printers!

Kalen Delaney — Wed, 18 Feb 2009 20:43:00 GMT

I know it's been a while since my last post, and there is a specific reason for that, which I am going to tell you about. On February 5th, I emailed to my editor the LAST chapter of my new book, after the author reviews and all comments from the editors were answered. I was technically done, except for a bit of work on the Intro, etc. Less than an hour after I sent off that last chapter, I got a call from the Washington State Highway Patrol that my husband had been found at the side of the road; he was semi-conscious after having a major stroke. I have spent most of the last two weeks by his side at Harborview Hospital in Seattle. Although there is WiFi in the rooms and I was able to check email and keep family and close friends informed, there was little inclination or energy for blogging.

Today, after all the final polishing by Microsoft Press, the pages were sent to the printer. The expected 'in print' date is March 11. It will be a week or so after that until the bookstores get it. I don't know how soon Amazon will be able to start shipping the pre-orders.

I'm not going to post my husband's whole story here, but suffice it to say my life will be changing. I will need to cut back on traveling, but I do need to continue working. I may not be offering nearly as many public classes, so if you've ever wanted to take a class from me, I suggest you think about doing it sooner rather than later, because there might not be a later. I will start delivering the 2008 version of my SQL Server Internals course next month. (Probably 90-95% of the course is still relevant to SQL Server 2005.)

Take care .... and in the words of one of my favorite singers/songwriters, James Taylor,

Shower the people you love with love....

~Kalen

Geek City: Too Many Indexes!

Kalen Delaney — Sun, 18 Jan 2009 20:37:00 GMT

I wrote about "Too Many Columns" last September, and along with changes in SQL Server 2008 that allow lots of columns, there is also the ability to create more than the old maximum of 249 nonclustered indexes on a table. I knew this fact, but somehow I overlooked it when updating the chapter on table structures in my new book. My omission also got past all the editors, and I just realized my mistake when reviewing the Indexes chapter, which was being updated for SQL 2008 by Kimberly Tripp.

I found that Books Online has not been completely updated (and I have filed a doc bug on this already). This page still says the limit is 249 nonclustered indexes per table:

http://msdn.microsoft.com/en-us/library/ms190197.aspx

However, the page for CREATE TABLE at http://msdn.microsoft.com/en-us/library/ms174979.aspx does say:

Each table can contain a maximum of 999 nonclustered indexes, and 1 clustered index. These include the indexes generated to support any PRIMARY KEY and UNIQUE constraints defined for the table.

You can take my script to create a table with lots of columns, and edit it to create a UNIQUE constraint on every column.

DECLARE @create varchar(max);
DECLARE @tabname sysname;
DECLARE @numcols int;
DECLARE @col int;
SELECT @numcols = 900;
SELECT @tabname = 'wide' + CONVERT(varchar, @numcols);

SELECT @create = 'CREATE TABLE ' + @tabname +
' (ID int IDENTITY, ';

SELECT @col = 1;
WHILE @col < @numcols BEGIN
    IF (@col % 3) = 0
       SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int UNIQUE,';
    IF (@col % 3) = 1
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' char(5) UNIQUE,';
     IF (@col % 3) = 2
            SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
             ' varchar(25) UNIQUE,';
SELECT @col = @col + 1;
END;
SELECT @create = @create + 'col' + CONVERT(varchar, @col) +
           ' int default 0);'
PRINT @create
EXECUTE (@create)

After the table is created, you can examine sys.indexes:

SELECT name, index_id, type_desc
FROM sys.indexes
WHERE object_id = object_id('wide900');

You'll definitely see more than 249 indexes! In addition, you might note that there is a gap in the index_id values. The values 251 - 255 are not used, and this is for backward compatibility. In much older versions, index_id (or indid) 255 was reserved for the LOB data belonging to a table, and the values from 251 - 254 were reserved. So that range is still not used. But you can see that I have index_id values from 256 - 905 in this table.

So, thanks Kim~

One more place that Books Online could use a little tweaking is in the section on Programmability Enhancements:

http://msdn.microsoft.com/en-us/library/cc645577.aspx

The section on "Wide Tables" seems to imply that you need to have an XML column set in order to get 1000 indexes. It doesn't actually say that, so it's not really a bug, but it does seem to imply it:

Wide tables are tables that contain one or more column sets. A wide table can contain up to 30000 columns, 1000 indexes, and 30000 statistics.

Because you can only reach the 30000 column limit with a wide table, it seems to imply that the 1000 index limit is only for wide tables. But as my script shows, it is not. You can have up to 1024 columns in a non-wide table, and you can now have indexes on every one of those columns.

But do you want that many? That's for another post, another time...

Have fun!

~Kalen

Did You Know? There is no such thing as A SQL Server

Kalen Delaney — Tue, 16 Dec 2008 00:08:00 GMT

I've finished my last chapter of SQL Server 2008 Internals, and now I am doing author reviews. The editors are complaining about the way I use the term "SQL Server". Sometimes I say "Your SQL Server should be configured ...." or refer to SQL Server using this or that resource. The copyeditors keep trying to change my wording and rewrite it as "Your computer running SQL Server..." or they want me say that the SQL Server computer is using this or that resource. I wrote to my main editor that SQL Server and Computer running SQL Server are not synonymous. She said that Microsoft's legal department has very strict requirements. Here is part of a passage she sent me:

SQL Server
SQL Server is the name of the Microsoft software product. At first mention and occasionally thereafter within a document, use Microsoft SQL Server. When referring to a computer running Microsoft SQL Server, use a computer running SQL Server (note the capital S). Never use the SQL Server, a SQL Server, or SQL Servers.

This didn't completely answer my question, so after another round of email, she said I could use "server running SQL Server". But I thought that in addition to being very awkward, most people will still interpret that to mean the computer. Was I correct?

And on this topic of things my editors insist upon changing... every acronym has to be spelled out. I agree that spelling out can sometimes help, but sometimes it provides no additional benefit, or even adds additional confusion. Plus, since my books are for advanced users, I expect the readers to have some background. For example, do I need to spell out "Redundant Array of Inexpensive Disks" instead of writing RAID? (or maybe it should be "independent disks"?) Will writing "Small Computer System Interface" really help you understand if you don't know what SCSI is? Will writing "Object Linking and Embedding DataBase" help you understand what OLEDB is, or will it just confuse you? I may be fighting a losing battle here.

Oh well... back to my reviewing.

~Kalen

Did You Know? I just submitted my last chapter!

Kalen Delaney — Fri, 05 Dec 2008 18:38:00 GMT

Less than an hour ago, I sent my last chapter of my next book "SQL Server 2008 Internals" to my editor!

Note that I said "my" last chapter, not "the" next chapter, as two of my co-authors still need to complete their chapters. But I've finished my chapters! Now this is still more to do, in addition to the waiting for the other two chapters to arrive. There are author reviews, and some rewrites, and Introduction, etc, to write, but after all these years, I know that stuff is minor.

So it's really going to happen... one more book!

Although Amazon indicates it will be available February 18, it probably will be out in mid-March.

Thanks for all your support.

~Kalen

Did You Know? You Can Win a Copy of My Next Book!

Kalen Delaney — Sat, 25 Oct 2008 03:09:00 GMT

November is conference month for me, and I'll be giving away vouchers for signed copies of my new book, to be mailed to the winners as soon as I receive my author copies.

I'll be giving away one each day of my seminars in London and Seattle!

The London seminars, for SQLKnowHow, are taking place the week of November 3-7, so I'll actually be out of the country the day of the US national elections. I haven't figured out if that is good news or bad news yet; I guess it depends on who wins.

Monday, November 3 is SQL Server Data Storage Formats: Internals, Performance and Best Practices. This will be the first delivery of the seminar I'll be presenting for my preconference session at PASS.

This seminar will describe all the different formats available for storing data, including the new filestream data, compressed data and sparse data columns in SQL Server 2008. We’ll look at the metadata to determine exactly what is being stored and where, and discuss best practices for choosing the particular type of storage format.

Tuesday, November 4 is SQL Server Concurrency Control: Locking, Blocking and Row Versioning

In this seminar I'll look at the concurrency enhancements made in SQL Server 2005 based on row-level versioning which implements optimistic concurrency. We’ll compare concurrency management with row versioning to concurrency management with locking and blocking. We'll look at the metadata that stores information about your transactions, and look at tools for troubleshooting concurrency problems. We will also look at the locking enhancements added to SQL Server 2008.

Wednesday - Friday, November5-7 is SQL Server Data Internals and Tuning

This seminar is a 3-day version of my 5-day SQL Server Internals and Tuning class that I have delivered all over the world. The seminar includes extensive demonstrations that illustrate the details of SQL Server internals as well as tuning techniques. This course will be presented on SQL Server 2008 and cover features specific to that version, but most of the information is relevant to SQL Server 2005 and some is relevant to SQL Server 2000. Topics to be covered include the following:

SQL Server Storage Metadata
Tools for exploring storage structures
Physical Storage Structures
Index Design and Tuning
Query Processing and Query Plans
Optimization and Recompilation
Query Tuning Tips and Techniques

So for each of these 5 days, I'll be giving away a book a day, plus other giveaways as well.

Then later in November, on 18 November, I'll be giving a preconference session at PASS, in Seattle. In addition to vouchers for my new book, I'll have a couple of copies of my SQL Server 2005 books to give away.

So hopefully I'll see many of you soon!

~Kalen

Geek City: SQL Server 2008 Internals is Orderable!

Kalen Delaney — Thu, 23 Oct 2008 22:08:00 GMT

Less than a week ago, I told you about making some major progress on my new book, and that it would "soon" be pre-orderable.

It turns out it already was orderable, as my helpful readers let me know. So here is the link to Amazon, for all of you who want to get the book as soon as it hits the streets!

I just searched through my old blog posts, and realized I've never talked about the new name! The "Inside" name was getting a bit diluted, with all kinds of other Inside books on the market, including some "Inside SQL Server" books from other authors through other publishers. Since I wanted the title to be really clear about what the book was covering, we decided to change the name.

This name change is not without precedent. Some of you may recall that there used to be a book called "Inside Windows" which morphed into "Windows Internals".

I have a terrific group of co-authors, including two people who are (or were very recently) on the SQL Server development team at Microsoft. My co-authors are: Paul Randal, Kimberly Tripp, Conor Cunningham, and Adam Machanic (who wrote the Tracing Chapter for Inside SQL Server 2005: Query Tuning and Optimization)

Thanks!

~Kalen

Did You Know? There is a light at the end!

Kalen Delaney — Fri, 17 Oct 2008 19:40:00 GMT

I realize it's been a long time since I've posted, but I have been traveling quite a bit, and when not traveling I am writing. I have a firm deadline for the next book,and it means every available hour that I am not teaching will have to be spent writing.

I just finished a major milestone, so I thought I would share that here....

I was dreading working on Chapter 7, called "Special Storage", because half of it was brand new stuff and it was really gory technical detail stuff. The old stuff is row-overflow, LOBs and partitioning, and the new stuff is filestream, sparse columns and compression. I was calling this my "Monster Chapter", but now it is done! I just sent the chapter in to my editor this morning... so I can take a short break, maybe even an hour or so. :-)

None of the rest of the chapters have nearly as much new content, and nothing is so deeply technical (bit and bytes oriented) as explaining the storage of sparse columns and compressed data. Even though, each chapter will take a least a week and maybe two.

The problem is that I am scheduled to be teaching quite a bit between now and end of the year, so there are few weeks to write. My all-in due date is middle of December, after that there are chapter reviews plus introduction, etc to write. We are looking at mid-March for an availability date.

There should be a pre-release item up on Amazon soon. I'll let you know when that appears.

~Kalen

Did You Know? I'm not just a SQL Server expert!

Kalen Delaney — Thu, 09 Oct 2008 20:47:00 GMT

School started for my two university-attending sons, and one of them was ordering his books on Amazon from the Marketplace, which is resellers and Amazon partners, selling both new and used. He ordered the books before classes started, expecting to receive them by the end of the first week of school. On Thursday of the first week, one of the vendors sent him email, saying that they didn't have the book after all, and were refunding his money. He was very frustrated because he had an assignment due the following Monday, for which he needed to use that book. I was really shocked that they would have said they had the book, when they really didn't.

But today, I realized it is not all that uncommon. I was checking on Amazon to see if they had put up any pre-order information for my new book. Well, I didn't see any news about my next SQL Server book, but I did see this:

http://www.amazon.com/Oracle-10g-Kalen-Delaney/dp/8448149394

I wrote a book on Oracle 10g and I didn't even know it! I even followed the link to buy new or used copies from resellers, the same kind of link my son followed, and saw this:

So the book is apparently available... a used copy from one reseller and a NEW one from another. I'm tempted to order it and see what I said. Because they say it is IN STOCK....

Or if one of you decides to buy it, let me know how you like it!

Thanks

~Kalen

Did You Know? It's Almost My 20th Anniversary!

Kalen Delaney — Thu, 04 Oct 2007 19:47:00 GMT

Maybe this post should have been a Geek City post, because 20 years is the length of time I've been working with SQL Server! Some people might think it's very geeky to spend 20 years working with the same software product.

I started working with the Sybase Corporation on October 27th, 1987. They gave a certificate to everyone after their first year, and I had that certificate on my wall for years and years, proudly proclaiming "I survived a year at Sybase!" with the dates clearly indicated. I actually survived over 5 years at Sybase before moving to the Beautiful Pacific Northwest and starting to work independently.

In the Beautiful Pacific Northwest, some of the native peoples have a tradition of "potlatch" when celebrating special occasions. Although there are many aspects of true potlatch, both spiritual and practical, one of the features is that the host of the celebration gives gifts instead of receiving them. With the release of my latest book occurring close to this momentous anniversary, I decided to follow that tradition and give gifts to thank my readers for all their support and encouragement over the last many years that I have been writing about SQL Server.

Fabulous Prizes:

First Prize:
      A set of all four Inside SQL Server 2005 books
                     The Storage Engine
                     Query Tuning and Optimization
                     T-SQL Querying
                     T-SQL Programming

Second Prizes: (2 sets will be given away)
                   Two of the Inside SQL Server 2005 books:
                        The Storage Engine
                        Query Tuning and Optimization

Third Prizes: (7 will be given away):
This one is hot off the presses (Microsoft Press, that is)
Inside SQL Server 2005: Query Tuning and Optimization

Additional Prizes:
Various SQL Server branded items that I have in my possession

First prize will be shipped anywhere in the world.
Other prizes will be shipped to US, UK or Canada addresses only.

To enter the giveaway, please follow these rules:

1. Create an email message to this newly created email address

2. Include "Happy Anniversary" in the subject line. The mail will be filed as junk if you don't.

3. The body of the email should consist of a single T-SQL INSERT statement (shown below). A value for country is required. If you include a city or region, I will add you to my mailing list so that when I speak at user groups in various parts of the world, I can let you know if I will be in your area. Examples of city or region would be "Beautiful Pacific Northwest", "San Francisco Bay Area", "Western Australia", "Central Europe" or "London". If you do not want to include city or region, please explicitly include NULL in the INSERT.

4. Doublecheck the syntax! Make sure you have open and close quotes for all strings, and that the quotes are STRAIGHT quotes, as in the INSERT statement below. If you use Word to add a quote of your own or replace one that's there, it's usually a "smart" quote, which in this case is NOT smart. SQL Server will reject the INSERT if the quotes are not STRAIGHT. I suggest you use SSMS to write the insert.

5. Send the email.

6. Wait until October 27th to find out the winners. I will send the winners email, asking for a mailing address.

Here is the INSERT statement. Replace the strings in red with your specific information, and include it in your email:

INSERT INTO Anniversary.Raffle(emailAddress, firstName, lastName, country, city_region)
VALUES('Your email address', 'First Name', 'Last Name', 'Country', 'City or Region' )

Thank you!

~Kalen

Inside SQL Server... the next book

Kalen Delaney — Tue, 14 Aug 2007 18:28:00 GMT

The last volume of Inside SQL Server 2005 (Query Tuning and Optimization) is now in the hands of MS Press. This is the first time I have worked on a Inside book as a group project, and for some strange reason I thought that meant that I could get the book done more quickly. We were desperately trying to get it done in time to be available by the PASS conference, but as the conference is in September this year, instead of the usual November, it looks like we won't make it.

I would like to extend my heartfelt thanks to my co-authors:

Sunil Agarwal (Chapter 1: A Performance Troubleshooting Methodology)

Adam Machanic (Chapter 2: Tracing and Profiling)

Craig Freedman (Chapter 3: Query Execution and Chapter 4: Troubleshooting Query Performance)

Ron Talmage (Chapter 6: Concurrency Problems)

(I co-wrote Chapter 4, and wrote Chapter 5: Plan Caching and Recompilation)

And of course, I also wish to thank all of my readers, without whom there would be no reason to write!

~Kalen