Category Archives: SQL Server

What I’ve Read and Recommend to Others – Joe Celko Edition

I’ve known Joe for a number of years and have a lot of respect for his experience and knowledge around relational database design and the SQL language. Joe is a prolific writer and has been writing about technology since I was in grade school with articles going back into the early 80’s. Not to mention his ten years serving on the ANSI board for SQL standards. I would put Joe’s books between the purely academic text on relational and set based theory and the more popular books out today that don’t always cover some of the dryer materials that newcomers to SQL may find to hard to digest at the start of their career. If you have been working with SQL for a while and want to take the next step Joe’s books are generally the way to go. Joe’s style is humorous at times and completely unflinching at others. When it comes to things that he thinks is the right way to model and develop using the SQL language, and that is against what popular or easy methods call for, he makes his feelings known on the subject.


Joe Celko’s SQL for Smarties: Advanced SQL Programming Third Edition (The Morgan Kaufmann Series in Data Management Systems)

Now in its third edition this book covers, in detail and depth, what many other texts leave behind. This isn’t a beginners book. This isn’t a text for the dabbler in SQL.

Joe suggest at least a years worth of experience and I would qualify that as a years worth of solid 40 hour a week kind of experience. If you have mastered third normal form and want to take it to the next level this will be one of the text’s I’ll always point you to.

For those who are squeamish about a little math, get over it. I hear people say relational database work isn’t math and they are flat wrong. Just because you may not understand the math doesn’t mean it doesn’t govern every aspect of your relational world.

If you want to take a real peak behind the curtain of relational theory and cover some advanced data modeling this is as good as it gets.


Joe Celko’s SQL Puzzles and Answers, Second Edition, Second Edition (The Morgan Kaufmann Series in Data Management Systems)

I liked this book because it allows you to see how many different ways there are to solve the same problem using SQL. Some of them are very interesting in the approach. It will show you how to think in other ways through the eyes of others. Any time you can get a look into how others solve issues you only build up your own problem solving skills. Plus, for a SQL geek like me I like taking the Pepsi challenge and see how I stack up.


Joe Celko’s Trees and Hierarchies in SQL for Smarties, (The Morgan Kaufmann Series in Data Management Systems)

This is one of my favorite books that Joe has ever written. It covers one of the more complicated modeling issues you will come across, and come across regularly. It explains clearly trees and hierarchies, how they are the same and how they are different. Now that SQL Server 2008 has a hierarchy function don’t think it does away with this book! Joe covers lots of different methods and what works best in different situations.


Joe Celko’s Thinking in Sets: Auxiliary, Temporal, and Virtual Tables in SQL (The Morgan Kaufmann Series in Data Management Systems)

Joe takes one of the common issues new, and some intermediate, users of SQL have a hard time overcoming, sets. I would say most of us that have a background in SQL probably came from a traditional programming back ground and that means iterative thinking. Loops, lots and lots of loops. Having come from that kind of background I still consider the day I finally got functional over procedural was the day I really started down the road of being a SQL developer. 

This book can be a heard read if you are locked into procedural mindset, but if you are trying to break out and really get past the same old mistakes we all make this will help you along your path.


Joe Celko’s SQL Programming Style (The Morgan Kaufmann Series in Data Management Systems)

Here we are again, Joe tackles a subject that gets glossed over in many other texts if it gets touched on at all. One of the keys of this book is helping you develop a consistent and predictable way to get the data in and out of your system. Even though this book is on style part of that is understanding set based architecture.  Even if you understand the math behind normalization that doesn’t mean you understand the data that goes into it. Even if you don’t agree with everything in this book it will help you focus on the data, consistency and improve you as a SQL developer.


Joe Celko’s Analytics and OLAP in SQL (The Morgan Kaufmann Series in Data Management Systems)

The concept of this particular work is to help transition the online high transaction, high volume database developer over to the analytical side of data aggregation and warehousing. Some folks think because they understand databases in an OLTP environment they can move easily in to OLAP. I’m here to testify that isn’t as easy as it sounds. Joe covers the concepts and some of the newer SQL syntax available in the ANSI-99 standard. Not all of it is available in SQL Server but it is a solid introduction to data warehousing and how to put your general SQL skills to use in the OLAP world.
This book is based on materials in SQL for Smarties. It expands on some of the topics covered there. 


I hope you enjoy these books as much as I have, over the years I have relied on Joe and others like him to build my own knowledge base and skills. I also have tried to share that knowledge like Joe has for so many years to others who want it.

Cannot generate SSPI context…

Out of all the problems you can have with SQL Server troubleshooting connectivity issues can be the most challenging. When you factor in the complexities of Active Directory and SQL Server’s interaction with it fixing SSPI errors can be down right baffling.

At my company we are moving onto new hardware and along the way standardizing on SQL Server 2005 x64.Since this is all happening on new hardware I have the luxury of doing most of the work before we flip the switch. We had one migration under our belt and the second one was looking good when the SSPI came and decided to make sure I spent all my Saturday working.

I ran down my list of things to check:

Date out of sync with domain more than 30 minutes – Nope.

Bad DNS entry – Nope.

Miss configured service account – Nope.

Log on locally with Windows account – Yep.

Now I was stating to get frustrated and kind of worried that a roll back may be called for. I did what all good panicked DBA’s do, I searched the Internet. Most of it I had already tried and some of it just didn’t apply. It’s not often a solid web search is such a complete strike out. When all else fails, step back look at the problem as a whole and start from the beginning.

What do we know?

We can’t establish a trusted login between SQL Server and the domain.

The OS and domain are just fine. You can log in locally with a domain account and you can remotely access other server resources with a domain account.

SQL Server services start up just fine under a domain account.

You can log in locally to SQL Server but not remotely.

SQL authentication works just fine.

That pretty much leaves a configuration issue somewhere. Since we had set this server up it initially had a different name and IP address that would be changed to the old server name and IP address. There are alot of known problems with other parts of SQL Server like reporting services when you do this kind of rename but generally SQL Server is just fine.

You drop the old SQL Server name

sp_dropserver old_name
sp_addserver new_name, local


sp_dropserver [old_nameinstancename] 
sp_addserver [new_nameinstancename], local 

Verify everything with these two queries

SELECT @@Servername 
SELECT * FROM [servername].msdb.dbo.sysjobs

If you have a problem with the @@Servername you get back nothing or the wrong server name. The four part select should return data if not you usually get this linked server is not configured for blah blah blah. Which means you may have forgotten the ,local part. After checking all of these things off again, we still had the issue! Now I really was stumped. I didn’t have a lot of other choices and time was running out. I just started going through EVERYTHING that was network related in the SQL Server configuration. Eventually, I saw the issue. The server IP was correct but the IP in the SQL Server Network Configuration was wrong! I’ve done a ton of renames like this and hadn’t encountered this particular setting not changing when the server IP changed. I reset it to the new server IP and just like magic SQL Server could authenticate to the domain. There was much cheering from my peers, ok not really but I was cheering. The server was up and everyone was able to get back to doing other things as the database faded into the background once again. I just keep telling myself that SQL Server one of the easiest RDMS’s on the market.

Migrating A SQL Server 2005 Reporting Services Named Instance, The Missing Manual

Microsoft has always been pretty good at one thing, ease on install.

One of the things I always says is the greatest thing about SQL Server is any idiot can install it. The other thing I also say is the worst thing about SQL Server is any idiot can install it.

This has carried over to the other fine products that ship with SQL Server. The one thing I’ve had pretty good luck using as a novice since it was introduced is SQL Server Reporting Services. As someone who hasn’t had the best experience over the years with crystal reports SSRS was a breath of fresh air.

One of the things I’ve never had to do though is move SSRS to a new server. To start with I did what I always do; I went to MSDN and read what I could find on moving or migrating to a new server. I was feeling pretty confident that I could pull this off.

I had backed up my databases and my key so I could import it on reinstall.

My first mistake was hubris that I could do what I thought was correct and second guess the documentation.

I installed SQL Server plus all the other services, including SSRS just like I was doing a clean install. The server didn’t start out with the same name and that was part of the problem. I copied all the files I needed from the old server and shut it down. Next we renamed the server and I renamed the SQL Server instance to its new name. Once that was done I restored the old SSRS databases and the encryption key. I fired up SSRS when to look at a report and got an odd error.

This version doesn’t support multiple instances.

My second mistake was assuming the documentation on MSDN was complete. After staring over and following the instructions there I ended up variations on the previous error, or the reports wouldn’t generate period.

What should have been a two hour migration was stretching into two days. I had to pull the trigger and bring the old hardware back on line.

Having a fall back plan saved us from having any impact on the business but it did set me back a week until the next available maintenance window was available.

I now had a whole week to research the issue and make sure I had a successful migration, so I got cracking.

I went to my buddy Mr. Google and asked him where the hell I messed up and if anyone else had come up against this. As I thought I wasn’t the first, but there was a twist it wasn’t just one thing it was a few things that I was bumping up against. Luckily between MSDN and some of the fine folks on the SQLServerCentral forums I was able to piece together a successful migration strategy.

The list of obstacles I had to overcome.

This was a named instance and the $ played havoc with the Reporting Services Configuration Manager.
This was a migration of existing data plus the security key to the server.
The server name was also being changed after install to reflect the old server name.

The instructions from MSDN tell you not to choose SSRS to be configured during the install with the default values.
This causes a key part not to be configured, IIS. This is a problem because without IIS configured you can’t view the reports!
This by its self isn’t an issue if you are working with a default instance of SQL Server. But, as I indicated, I’m dealing with a named instance.
After you have installed SQL Server and SSRS you are suppose to use the Reporting Services Configuration Manager to setup IIS. But, since the named instance has the $ in it, the Reporting Services Configuration Manager kicks back the virtual name with:

ReportServicesConfigUI.WMIProvider.WMIProviderException: The virtual directory specified is not valid. Make sure the specified name is not too long and doesn’t contain illegal characters. (example: 😉
at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.CreateVirtualDirectory(String virtualDirectory, String path)

Very nice, if you let the installer configure the site at install time it gladly puts the $ in the name for you like http://<servername>/ReportServer$<instancename>.

I had puzzled out the IIS part and gleaned the rest from Tim Benninghoff and his post on SSC.
Also, the bits about restoring the encryption key came from MSDN, none of the other articles talk about a named instance at all and assume it is a default instance you are moving.

So, I followed these steps to restore the IIS folder structure and move my instance of SSRS to the new server.
Remember, please validate any changes you make to your server, and always your success isn’t guaranteed just because you followed these instructions.

On the original source server:

  1. Back up the ReportServer database, the ReportServerTempDB database.
  2. Back up the encryption key by using the Reporting Services configuration Manager. It will ask you for a password to backup the key DON’T FORGET IT!
  3. Using the Internet Information Services Manger expand the Application Pools folder, right click on the ReportServer$<instancename> go to all tasks and Save Configuration File.
    I recommend giving it a .xml extension to make things easier on the restore side.
  4. Go to the Web Sites folder and usually under the Default Web Site there will be two entries with gears next to them one named Reports$<instancename> and the other ReportServer$<instancename>.
    Right click on them and save the configuration file like you did with the application pool.

On your new destination server:

  1. Stop IIS service by running the iisreset /stop command at the command prompt.
  2. Stop Reporting Services using Reporting Services Configuration Manager click Server Status, and then click Stop on the Report Server Status page. If Stop is unavailable, the service has already been stopped.
  3. Restore the backup databases from the source server.
  4. Import the IIS application pool configuration to IIS on the new server. From Internet Information Manager, right click on your Application Pools folder and select New>Application Pool (from file)… Use the Browse button to locate your .xml file you should see the new pool in the list.
  5. Import the IIS virtual directory configuration to IIS on the new server. From Internet Information Manager, right click on your Default Website and select New>Virtual Directory (from file)…
    Use the Browse button to locate your .xml files, and then click Read File Select the Virtual Directory name that appears in the white box and click the OK button.
    You should see your new Virtual Directory appear with the name Reports$<InstanceName> and ReportServer$<InstanceName>
  6. Modify the following registry settings under HKLMSoftwareMicrosoftMicrosoft SQL ServerMSSQL.(instance#)Setup:
    RSVirtualRootApplication – Reports$<InstanceName>
    RSVirtualRootApplicationPath – IIS://(ServerName)/W3SVC/1/Root/Reports$<InstanceName>
    RSVirtualRootServer – ReportServer$<InstanceName>
    RSVirtualRootServerPath – IIS://(ServerName)/W3SVC/1/Root/ReportServer$<InstanceName>
    Also, you may need to add the $<InstanceName> to the following keys if they don’t already exist:
  7. Modify the following .config files for Reporting Services:
    In C:Program FilesMicrosoft SQL ServerMSSQL.(instance#)Reporting ServicesReportManager, modify RSWebApplication.config so that the ReportServerVirtualDirectory node contains ReportServer$<InstanceName>
    In C:Program FilesMicrosoft SQL ServerMSSQL.(instance #)Reporting ServicesReportServer, modify rsreportserver.config so that the UrlRoot node contains http://(ServerName)/reportserver$<InstanceName>
  8. In the Reporting Services Configuration Manager confirm the new paths.
  9. Start Reporting Services from the Reporting Service Configuration Manager.
  10. Start IIS service by running the iisreset /start command at the command prompt.
  11. Remove the encryption key from the source server. I used the rskeymgmt utility to do this.
  12. Examine the encryption keys that exist. Based on the type of the instance of SQL Server 2005 installed on the destination server, you type the following in the command prompt window.
    Default instance
    Type rskeymgmt -l, and then press Enter
    Named instance
    Type rskeymgmt -l -i InstName, and then press Enter
    You will see two encryption keys listed. One is from the source server, and the other is from the destination server. The format of the encryption keys listed is as follows:
    Note ComputerName is the name of the destination server.InstName is the placeholder for the name of the instance of SQL Server 2005 installed on the destination server. EncryptionKeyID is the placeholder for the ID of the encryption key.
  13. Delete the encryption key from the source server. Based on the type of the instance of SQL Server 2005 installed on the computer, you type the following in the command prompt window.
    Default instance
    Type rskeymgmt -r SourceEncryptionKeyID, and then press Enter
    Named instance
    Type rskeymgmt -i InstName -r SourceEncryptionKeyID, and then press Enter
    Note SourceEncryptionKeyID is the placeholder for the ID of the encryption key from the source server that is obtained in step 2.
  14. Repeat step 2. You will see that only the encryption key of the destination server exists now.
  15. Restore the backup encryption key from the source server.
    In the Reporting Services Configuration Manager, click Encryption Keys, and then click Restore in the Encryption Key page.
    In the Encryption Key Information window, type the password you use to back up the encryption key from the source server, locate the backup encryption key from the source server, and then click OK.
  16. Make sure that the same user account as the ASP.NET service account is set on the Rsreportserver.config file. To do this, follow these steps:
    In the Reporting Services Configuration Manager, click Web Service Identity. Notice the ASP.NET Service Account information.
    Open the SQLInstall:Program FilesMicrosoft SQL ServerMSSQL.XReporting ServicesReportServer folder. Right-click the Rsreportserver.config file, and then click Properties.
    Note SQLInstall is the placeholder for the drive where SQL Server 2005 is installed. MSSQL.X is the placeholder for the Instance ID of the instance of SQL Server 2005 Report Services that is running on the destination server. To obtain the Instance ID, click Server Status in the Reporting Services Configuration Manager, and then notice the Instance ID information. Usually it’s ID 3 if you are running multiple instances or ID 2 if not.
    In the Rsreportserver properties dialog box, click the Security tab.
    if the ASP.NET service account is not listed, add it. Make sure that the ASP.NET service account has Read and Read & Execute permissions.
    Click OK to close the Rsreportserver properties window.
  17. If you did not have an instance of SSRS already configured then you will need to execute the following script to add the RSExecRole and the appropriate permissions.
    --create RSExecRole role in master and assign permissions
    USE [master]
    GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole 
    GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole 
    GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole 
    --create RSExecRole role in msdb and assign permissions
    USE [msdb]
    -- Permissions for SQL Agent SP's 
    GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole 
    GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole 
    GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole 
    GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole 
    -- SQL Server 2005 requires that the user is in the SQLAgentOperatorRole 
    IF EXISTS (SELECT * FROM sysusers WHERE issqlrole = 1 AND name = N'SQLAgentOperatorRole'
    msdb.dbo.sp_addrolemember N'SQLAgentOperatorRole', N'RSExecRole' 

Once all that is done, if you had an instance already configured you may need to remove the virtual folders from the default website.

I also restarted everything again and tested it all out.

Good luck and I hope you find this useful; trying to glean all of this from different resources and separate what was really going on by the cryptic messages SSRS pumps out can be difficult in the best of times!

What I’ve Read and Recommend to Others – SQL Server 2008 Part 1

I read, a lot. I’ve been a prolific reader all my adult life.

I use to split my reading between tech books and my regular relaxing reading but since I got into audio books several years ago I just pretty much read tech books now. Some times I’ll listen to a book and read a manual at the same time breaking from one or the other if I need to really focus on a particular passage.

This allows me to really chew through a large amount of text in a pretty short amount of time.

I also have a method of digesting the information as well.

When I read a large technical volume I usually do it in three passes.

First pass is a scan of the entire book marking things of immediate need or interest for detail re-read later.

Second pass is the detail look at my notes from the first pass.

Finally, I re-read the whole thing and take notes on stuff I may have skipped the first time through because I “thought” I knew about that already, or didn’t apply to my core skill sets.

I recently posted a short list of authors I always read and now I’m going to follow that up with specific texts with some notes.

Eventually, I’ll expand some of these into single book reviews, ones I’ve come back to over and over or that I found extremely interesting.


Microsoft® SQL Server® 2008 Internals (Pro – Developer)

Kalen took over Inside SQL Server full time for the 2000 release and hasn’t looked back. Inside SQL Server 2000 was a must have on any book shelf. 2005 saw a shift by breaking the book into several volumes and inviting the industries best to write about the fields they were established experts in.

This time around, that theme has been carried forward and several noted experts lend a hand in this volume. SQL Server as a product is impossible for a single person to be an expert in from end to end. Even the core engine is beginning to grow to a point that just being an expert in a single aspect may be enough to establish a career.

With the inclusion of Paul Randal, Kimberly Tripp, Conor Cunningham and Adam Machanic this book is an exceptional read. The access to other experts and team members on the SQL Server team at Microsoft lend this book a level of authority that any other book like it in this space just simply cannot match.

Again, Kalen gives us a solid walk through the core engine, what has changed and how it works. I was personally happy to see Conor do his chapter over the optimizer.


Inside Microsoft® SQL Server® 2008: T-SQL Querying

Itzik is a giant in the query world. He has a grasp of the T-SQL language that few can match let alone surpass. Time and again I reach for his work to solve a problem or just to learn something new.

With this volume he has added some other note worthy people have joined in. Lubor I have known for quite a while. He is a staple figure at Microsoft and is also a big thinker.

Steve Kass long time SQL Server MVP and scary smart fellow himself adds his wealth of knowledge and his ability to communicate to this book.

I have a feeling this will become a “must have” and a classic in it’s own right.


Microsoft® SQL Server® 2008 T-SQL Fundamentals (PRO-Developer)

Another Itzik book teaches you the foundations of T-SQL including previous versions of SQL Server and raps that up with the new stuff in 2008.

Never think you are too smart to read a foundation book no matter what point you are in your career. This is how you can catch new ideas, theories and techniques that “what’s new in…” books can miss.

Plus Itzik’s style and clarity may actually re-learn something you thought you already understood.


Pro SQL Server 2008 Relational Database Design and Implementation


I’ve known Louis for a number of years and have read every one that I know of.

He has a down to earth conversational style that is easy to read. Also, having Kevin Kline work on this book is just icing on the cake. I have been a fan of Kevin’s work since the 7.0 days.

This book is good for someone who has been doing some design and development work and really wants to start digging in deeper. He makes no assumptions about what you do or don’t know he starts at an introductory level and walks you through what it takes to do design work and turn that into a usable physical model.

I will say he has progressed to cover more material in every new volume and this latest outing is no exception. Make no bones about it Louis works for a living. There are a lot of text that will make you feel stupid or completely inadequate to do your job but Louis makes some of the more complex problems easy to digest by putting them in real world examples.

I’ve got a lot more books in the pipeline and will be talking about them soon.

I will also be covering 2005 and even 2000 in the coming posts. I know there are some folks still on 2000 and even 7.0 but I don’t have any books save one that old on my shelf at this point.
Since I do more than just SQL Server or database work in general I’ll be covering those volumes as well and why I like them.
Lastly but not least I will also cover books I’ve read and didn’t find that helped me move my knowledge forward, not that these books were bad, they just didn’t fit me, or there was another author who covered the material that made it easier for me to understand.

Found a Bug in SQL Server 2005

It doesn’t happen often but every once in a while you may be the lucky person to find a previously unknown bug in SQL Server.

It was a normal morning for me, checking the status of our servers going over any failure messages waiting for the day to ramp up. That’s when one of our lead developers came around the corner and told me he had an error when he had tried to create an index on a table he was working on. The more he tried to explain the error the more I started to worry. I had him send me the code and the error statement.

Location:     BtreeMgr.cpp:5372

Expression:   bufferLen > currOffset + ACCESSSOR_OVERHEAD

SPID:         116

Process ID:   5016

Msg 3624, Level 20, State 1, Line 2

A system assertion check has failed. Check the SQL Server error log for details. Typically, an assertion failure is caused by a software bug or data corruption. To check for database corruption, consider running DBCC CHECKDB. If you agreed to send dumps to Microsoft during setup, a mini dump will be sent to Microsoft. An update might be available from Microsoft in the latest Service Pack or in a QFE from Technical Support.

Msg 0, Level 20, State 0, Line 0

A severe error occurred on the current command.  The results, if any, should be discarded.

I had what we like to call in the high availability space a “pucker moment”. This wasn’t your normal, I typed something wrong and got an error, kind of problem. This was a real SEV 20 with an assert, the core engine had just puked on something it shouldn’t have.

Like all good DBA’s the first thing I did was run a DBCC on the database this error was generated from.

While that was going on I asked my very good friend, Google, if he had seen this particular assert before. For the first time in a very long time Google failed me! In the last few years if I hit this kind of hard error someone else has too and it is ether being fixed in a hot fix or addressed in the next version of SQL Server, but not this time.

So, we have this same schema on another server and the developer tried the exact same code there and had the exact same error.

I had him document the steps he took to get to this point and to his credit the steps were clear, concise and easily reproducible.

The DBCC finished with zero problems detected, which let me calm down a bit. That coupled with the fact it looked like I had a repeatable test case When the second database had cleared the DBCC I set about my task of reproducing the error and trying to find a work around. Lucky for us it was a simple matter of column organization in the index and we were able to apply it successfully and carry on with life.

I bundled up the documentation I had accumulated, ran the test case confirmed the bug and sent it off to the powers that be at Microsoft. Since we had a work around and it wasn’t a show stopper I didn’t raise it as a critical server down issue but Microsoft still worked it in a timely fashion.

So, what was the problem you say? It was an interesting edge condition.

We have a table that contains a composite primary key and the rest is made up of bit fields, a flag table.

We had to add a new column, another bit flag, to the table.

The non-clustered covering index was dropped the column was added to the end of the table.

The index was updated with the new column at the end of the column list and then *POOF* it blew up.

I think it has to do with two specific things.

First, bit fields are stored in a compact manor where multiple bits share a byte and aren’t truly separate from every other but field. It would be a huge waste of space to store each bit in it’s own byte but it would make things like this index issue less likely to happen.

Secondly we did a column add but didn’t drop and recreate the table repopulating it in the process so things at the page level weren’t nice and neat. The underlying clustered index wasn’t effected but when we tried to add an index back with the new field it couldn’t do it. The fix was simple, change the column order in the non-clustered index moving the new column up one. We verified the data without the index was correct and with the index was correct.

I haven’t tried it yet, but I am betting included columns won’t suffer the assert ether since the items don’t have to be sorted in the index.

So there you go! Having been on the software side of things a couple of times I always find it interesting when I find bugs in others products and work the issue to conclusion.

What is your take away from all of this? Never be afraid to submit a bug report to Microsoft. I have heard people say to the effect someone else will or has hit the bug and they will submit it. DON’T RELY ON THE ACTIONS OF OTHERS! Reporting bugs helps the community as a whole and makes the product better. When you assume someone else is worked it you are putting YOUR production servers in the hands of strangers. If someone has submitted it and it didn’t turn up in a search they will let you know, and be very kind about it to boot. You will get piece of mind that it is being worked on and it is a bug, or that you may keep someone else from stumbling onto this and not having the knowledge to fix it or work around it.