What would you like to hear about from the VS DATA Team blog? (by Mairead)

The VS DATA team has been blogging on articles such as our extensibility component DDEX, the new and innovating technology of SQLCLR, SQL Server Express, Microsoft and .Net etc.


We had some very great postings from one of our developers Ming on the Results pane and a great article by Sam from the QA team on how to use Whidbey to debug TSQL/CLR procedures on Yukon server.


But what I really would like to know is what do you guys/gals like to see us blog (more) about in regards to data or even SQL Server Express. Is there a particular topic that you find interesting that you would like us to blog about? Please do let us know as we love to hear from you and we will try to get that information to you as much as possible. Please do bear in mind that we cannot discuss any non disclosed release dates or internal confidential information.

Comments (20)

  1. I may not be the typical reader of this blog, but my interests from SQL2005 aren’t so much in the "cool new features" department, but in whether 2005 fixes some of the (IMO) stupid and arbitrary limitations in SQL2000.

    As far as object-orientation, custom types, .NET integration, managed stored procs etc, I already have an O-R mapping library which does what I need and doesn’t use stored procs. Not that the new features aren’t extremely cool and I’m glad you’re working on them, but they won’t impact me personally any time soon.

    On the other hand, I routinely get bitten by the fact that SQL2000 simply can’t handle multiple paths of cascading-delete foreign keys between the same two tables. There’s no logical reason for this limitation and other database systems can handle it just fine. It’s a situation that comes up *frequently* in my small group and it’s always for good reason. We end up having to resort to ugly, kludgy hacks (putting code to delete records in an area of code that should have no knowledge of that table) or have situations where our application simply fails with a foreign key violation error.

    Another requirement I often have is to create a unique constraint that only limits non-null values. Based on the semantics of NULL in SQL, this should be the default behavior – the unique constraint says that you can’t have two values that are equal, but if you have two nulls, they’re *not* equal to each other because in SQL, null does not equal null. I often want to have a field that’s optional, but must be unique if it’s specified. If there’s a way to do this in SQL2000, I haven’t found it.

    While it would be nice to have answers to these two specific questions, I realize that that what you’re really asking is what kind of general categories of information I’d like to see. I think the underlying category here is information about what improvements, if any, are being made to the core of SQL Server’s functionality – the SQL engine itself. Information about the architecture and why particular design decisions were made, why particular limitations exist, what might be done in unspecified future versions to improve this, etc.

    Basically, I want to learn about SQL Server in the context of being "just" an RDBMS, as well as being a CLR-hosting object-oriented integrated-debugginged GUI-managed RDBMS. There’s a lot of information about the fancy stuff out there – whitepapers all over the place – but not a lot about the basic functionality. It’s not as sexy, perhaps, but from my position in the trenches actually *working* with SQL Server, it’s much more valuable.

  2. Raymond says:

    Stuart, your first issue with the cascading foreign keys, it sounds like your database is not correctly architectured, otherwise this wouldn’t be a problem, but I could be wrong.

    Your second issue regarding NULLs… your asking to take away a very imporant piece of functionality. That’s the way most people WANT it to work. You have to understand that NULL means nothingness. There is nothing to compare, so NULL can’t be used as part of the constraint, because there isn’t anything there. Null doesn’t mean null, null really means nonexistant. If it didn’t work that way, or was changed, it would crash so many systems out there its not even funny, because that is a core piece of functionallity most systems require. You have a misconception of what NULL really is, and you want it to be part of a comparison operation when nothing exists to compare.

  3. Mairead says:

    Raymond can you get in contact with me maireado@microsoft.com

  4. Thomy Kay says:

    More details and examples about DDEX would be fine.

  5. Raymond,

    I’ll answer the second issue first because it’s easier. I think that what I’m asking for is what you’re saying already exists. To clarify: I’m asking that if you have a table with a field x, that is nullable, and a unique constraint on it, you SHOULD be able to have multiple records where x is null. If this is already the behavior, then I must have mis-remembered what happened when I tested this, or perhaps have been testing on a different system. I’ll retest.

    The first issue is harder because you don’t specify in what ways my database might be incorrectly architected. I’m always very conscious about trying to keep a clean architecture, and my beef with SQL Server in this case is that it frequently forbids me from doing so for arbitrary reasons.

    Here’s a theoretical example (I can’t remember the details of any of the actual times this has come up, unfortunately, but I think this example is reasonable). Suppose you have an eCommerce system which is designed for hosting multiple sites, so you have a table Stores with Id and Name fields. Each Store can contain categories for classifying products, and a product can be in more than one category (within the same store). For simplicity I’m ignoring hierarchical categories – don’t even get me started on SQL server’s support for hierarchies.

    So I envision the following tables:

    Stores (

    StoreId int Identity,

    Name nvarchar


    Products (

    ProductId int Identity,

    StoreId int,

    Name nvarchar

    Price … [etc]


    Categories (

    CategoryId int Identity,

    StoreId int,

    Name nvarchar,



    ProductCategories (

    CategoryId int,

    ProductId int


    Now naturally if I delete a product I want it to disappear out of any categories it’s in, and it’s not unreasonable to imagine that if I delete a category I want all products to be removed from it (I can think of plenty of ways to deal with any potentially orphaned products). So both fields in ProductCategories are cascading foreign keys. But if I delete a Store I want everything related to it to disappear too, so the StoreId fields on Product and Category should also both be cascading foreign keys.

    SQL Server can’t do this. I can’t think of a cleaner DB architecture for this scenario, so I’m curious to know how you’d suggest doing it.

  6. Raymond says:

    Mairead, I sent you an email.

  7. Raymond says:


    Looks like I was wrong on that unique constraint. By default, you are correct, you cannot insert duplicate NULLs into a field with a unique constraint on it. However, I know I have overcome this many, many times and know of many systems that use this same behavior (having a unique constraint but with multiple NULLs in the field). I could be thinking of a different database, but I know I’ve accomplished this in Sql Server somehow.

    For you first scenario, this is functionallity that supports data integrity. The incorrect architecture in your database lies within the constraints. With Sql Server 2000, there is an ON UPDATE CASCADE and ON DELETE CASCADE. Let’s say you wanted to delete a store from the store table. You would need to alter your table first in order to delete the foreign keys.


    FK_Store_Products FOREIGN KEY



    ) REFERENCES dbo.Products




    You would have to propegate that code down the line and alter the tables down the heirarchy.

    The only other way I know to accomplish this manually is to use triggers like:

    CREATE TRIGGER triggerStore ON dbo.stores FOR DELETE AS

    DELETE products FROM deleted, products WHERE deleted.storeid = products.storeid

    Again, sorry about this misinformation on the NULLs. I’m going to look into that and see how I accomplished that and post it back here.

  8. Yes, I’m aware of cascading delete foreign keys, that’s what this is all about.

    Try actually creating my tables in a test database and putting the appropriate foreign keys in place, all with ON DELETE CASCADE turned on. It works in general but it will fail for my scenario with a "multiple cascade paths" error due to the fact that there are two separate cascade paths from Stores to ProductCategories (one via Products, one via Categories). It will work if you change any one of the foreign keys to be non-cascading, but as I explained, there are good reasons for all of them to cascade.

    This is the limitation that I’m complaining about.

  9. Mairead says:


    I have passed on your request about DDEX to the PM owner (Milind) and the developer(Stephen). So expect to see some more postings shortly


  10. Mairead,

    How do you feel about my request for general information on the basic SQL and RDBMS functionality of SQL Server? Am I just weird for being interested in this or do you think other readers might feel the same?

  11. Raymond says:


    I apologize, I must have been thinking back to Oracle when I was talking about the NULLs. I can’t seem to find how I was handling that type of behavior in Sql Server, although I thought I was.

    I am most likely doing it somewhere using some UPDATE and INSERT triggers to create an index in some sort of "index table" for the primary table and checking against that instead of having the unique constraint on the primary table. Also, you can create a view with schemabinding that selects the columns that define your uniqueness constraint and return only the non NULL values in the view. Then create a unique clustered index on the view. If you have more than one ANSI SQL-92 compliant uniqueness constraint to implement you can create multiple views, each with their own unique clustered index.

    I am correct in saying that NULL doesn’t equal NULL, but for the purpose of enforcing unique constraints, Sql Server treats them as equal. The SQL standard consistently defines all the nullable constraints (CHECK, UNIQUE, FOREIGN KEY) in the negative. In other words, the constraint doesn’t permit violations (logical FALSE) but nor does it require that the constraint be satisfied (logical TRUE). The UNKNOWN case is not considered a violation of a constraint, which is why NULLs are permitted in Oracle. Unfortunately Microsoft disagrees in versions 7.0 and 2000 at least. I have a copy of Yukon, but haven’t done much work with it as of yet.

    I was completly incorrect when making that statement about NULLs and unique constraints in Sql Server. That behavior does, however, exist in Oracle. You’ll have to forgive me, as I’m not too long removed from Oracle into Sql Server.

  12. Raymond says:


    I see what your talking about now with your foreign keys. I’ve dealt with these type of situation in the past with triggers. I went out and looked and looked and looked to see if I could find an article to help you out on how to solve that and came across this one: <a href="http://www.windowsitpro.com/SQLServer/Article/ArticleID/25520/SQLServer_25520.html">Cascading Alternatives</a>. I hope it can help you out with your problem.


  13. Ok, I’m glad to find I wasn’t going nuts when I remembered this behavior, and I stand by my original characterization of this behavior as "stupid and arbitrary" 🙂

    If you look back at my original post you’ll see that I know that NULL doesn’t equal NULL and this was my rationale in asking for the behavior in the first place 🙂

    Oracle is able to handle my store scenario just fine, but Oracle has its own quirks – whoever had the bright idea of making the empty string equivalent to NULL so that [where ” = ”] is false (UNKNOWN technically, but that’s treated as false in a where clause) should be shot.

  14. Raymond,

    Thanks for following along with my examples 🙂

    I know that there are various ways of working around the limitations (I hadn’t thought of using triggers, but it’s a possibility I’ll bear in mind for next time I face the situation).

    The existence of workarounds doesn’t change the fact that the limitations are real, and that it would be good if they could be fixed in Yukon. And more generally, I’m interested to know how much focus, if any, the Yukon team is giving to this kind of basic database functionality, compared to the flashy new features. That’s why I’m interested in seeing blog entries on this topic (to bring the subject back to the original question 🙂 )

  15. Raymond says:


    What you have to remember about these things is that not everyone views them as limitations. Goes back to the old "its not a bug, its a feature request" type thing. Many developers prefer to have NULL used in their unique constraint comparisons, whereas others, obviously you and I, would like NULLs to be ignored when doing unique constraints without using workarounds. Same things applies to how the foreign keys are handled. Not everyone views this as a limitation. Many people view this as a feature.

    Fortunately, through blogs like this, we get to voice our vote on whether we think these are limitations or features and how they apply to us, and Microsoft adjusts the product accordingly.

    One persons limitations are another persons features, which is why there is such a strong competition in the land of enterprise software and enterprise databases. Not all software operates the same, so we get our choice.

  16. Mairead says:

    Hi Stuart

    First and foremost thank you so much for taking the time to providing us with your feedback. We really do appreciate it and no I don’t think you are being weird to ask for this information. I do think there will definitely be users out there who will also be interested in this. I’ll start getting some blogs postings or blog links to get that information to you. I will also pass this information onto the SQL Server folks themselves so that they can also potentially blogging about this (if they have not already).

  17. Raymond says:

    Bill Ramos is the Lead Program Manager with the Sql Server 2005 Tools team. His blog can be found here: http://blogs.msdn.com/billramo/ He has just started blogging, so the content is sparse, but I expect we will see a lot of information start stemming from his blog shortly. The will also be another good place to voice our ideas for the Yukon release of Sql Server, although I image most of what is going to be in Yukon is already developed and only minor changes are being implemented at this time.

  18. Mairead says:

    Stuart, Raymond

    These are great suggestions and great feedback for the SQL Server team. I have contacted Bill Ramos and his team about your feedback