Compartilhar via

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

  • Artigo

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

  • Anonymous
    November 30, 2004
    The comment has been removed
  • Anonymous
    November 30, 2004
    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.
  • Anonymous
    November 30, 2004
    Raymond can you get in contact with me maireado@microsoft.com
  • Anonymous
    November 30, 2004
    More details and examples about DDEX would be fine.
  • Anonymous
    November 30, 2004
    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,
    [etc]
    )
    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.
  • Anonymous
    November 30, 2004
    Mairead, I sent you an email.
  • Anonymous
    December 01, 2004
    The comment has been removed
  • Anonymous
    December 01, 2004
    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.
  • Anonymous
    December 01, 2004
    Thomy

    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

    mairead
  • Anonymous
    December 01, 2004
    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?
  • Anonymous
    December 01, 2004
    Stuart,

    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.
  • Anonymous
    December 01, 2004
    Stuart,

    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.

    Raymond
  • Anonymous
    December 01, 2004
    The comment has been removed
  • Anonymous
    December 01, 2004
    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 :) )
  • Anonymous
    December 01, 2004
    Stuart,

    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.
  • Anonymous
    December 01, 2004
    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).
  • Anonymous
    December 01, 2004
    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.
  • Anonymous
    December 01, 2004
    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

    mairead
  • Anonymous
    June 01, 2009
    PingBack from http://paidsurveyshub.info/story.php?id=75759