Branko Dimitrijevic

My feedback

  1. 5 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  3 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Branko Dimitrijevic commented  · 

    We have a similar situation with row-level security depending on user impersonation mechanism based on SESSION_CONTEXT.

    While considering it deterministic when @readonly=1 would be useful, it would be even more useful to consider it deterministic for the duration of the query even when @readonly=0. After all, the same session can't be executing the query and calling sp_set_session_context at the same time, no?

    Being "query-level deterministic" (and not "permanently deterministic") would still preclude it from being used in persisted computed columns (and indexes), but would help with the query performance.

    We can minimize the issue by rewriting WHERE predicates as JOINs, and then carefully controlling the join order (so the SESSION_CONTEXT in the "outer" part of the JOIN and evaluated only once), but this is easy to get wrong and blow-up the performance in the process.

    Branko Dimitrijevic supported this idea  · 
  2. 7 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Branko Dimitrijevic supported this idea  · 
  3. 2 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Branko Dimitrijevic shared this idea  · 
  4. 3 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Branko Dimitrijevic shared this idea  · 
  5. 4 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 3

    <=-=Feb 7 2011 2:05PM=-=>

    Why not a filtered index?

    <=-=Feb 8 2011 10:31AM=-=>

    Hi,

    Thanks for the feedback. We’ll consider this for a future release. The columnstore index feature coming in the Denali release provides such excellent performance that many data warehouse and data mart customers won’t need indexed views any more. So that may provide an adequate solution for you. See http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf

    Best regards,
    Eric Hanson
    Program Manager
    SQL Server Query Processing

    <=-=Feb 8 2011 10:51AM=-=>

    Please don’t leave out OLTP customer… Indexed views are great for eliminating joins for mostly read data. Consider the following schema:

    create table forumthreads (id, userid null, title)
    create table users (id, name)

    and the query:
    select top 10 * from forumthreads left join users order by users.name

    No single-table index can satisfy the sort order so the entire result set must be materialized and top-n sorted. It would be much…

    Branko Dimitrijevic commented  · 

    One scenario where outer joins in indexed views would be very beneficial is related to fulltext indexing.

    Unfortunately, a fulltext index can only be created on a single table. Combining results from multiple fulltext indexes is awkward at best and extremely poorly performing at worst. Combining fulltext rank or implementing "AND NOT" logic is especially problematic.

    A "multi table" fulltext index can be emulated by creating an indexed view that joins multiple tables, and then creating a fulltext index on top of it. Limiting what can be included in an indexed view therefore limits what can be fulltext indexed.

    The alternative is manually maintaining a redundant table just so we can create a fulltext index on it, which is of course prone to errors (especially to concurrency errors).

    Branko Dimitrijevic supported this idea  · 
  6. 1 vote
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Sep 3 2010 8:55AM=-=>

    Thanks for your feedback. Do you think you could post examples of views you’d like to materialize but can’t, with an explanation of why specifically you want to materialize them?

    Are you querying the views directly with NOEXPAND or are you expecting indexed view matching to work?

    Since eventual consistency is good enough for you, consider creating user-defined snapshot tables where you select the contents of your “view” into a table and then query the table directly.

    -Eric

    <=-=Sep 7 2010 3:39AM=-=>

    Thanks for responding.
    The views I wish to materialize have ragged hierarchies such as: Charts of accounts or Product Category catalogues.
    Within these views, I need to calculate: node depth from root, sibling ranks and a materialised paths to the root, for all nodes. This is no problem for me using either a CTE OR a UDF incorporating a While loop (I…

    Branko Dimitrijevic supported this idea  · 
  7. 5 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Branko Dimitrijevic supported this idea  · 
    Branko Dimitrijevic commented  · 

    MIN on a bit column is essentially aggregate version of AND, while MAX is aggregate version of OR. This is quite useful in some scenarios where Boolean logic needs to be applied in a column over multiple rows.

    Right now, we are doing it like this:

    CAST(MAX(CAST(BIT_COLUMN AS tinyint)) AS bit)

    Wouldn't it be nicer to just write

    MAX(BIT_COLUMN)

    ?

  8. 1 vote
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 76

    <=-=Oct 4 2007 10:33AM=-=>

    If I had $1 for how many times I needed this feature, I would be rich! :D

    <=-=Oct 25 2007 4:41PM=-=>

    Thanks very much for your comments on supporting min/max aggregates for indexed views in SQL Server. We were actually considering what we called trusted indexed views for SQL Server 2008. Those are views that you could create and that would be matched automatically by the query optimizer. However, they would become invalid as a potentially relevant update comes along. You would need to maintain them separately. But, they would allow for much richer T-SQL constructs than regular indexed views. However, we did not have enough resources to put them into the upcoming 2008 release.

    However, I’d be curious to learn more about the scenarios behind your motivation to ask for this feature. Some of it I can take from your comment in…

    Branko Dimitrijevic commented  · 

    We are building what is essentially a version control system (for CAD data), and it is tremendously useful for us to have a "cached" view into the latest version of data without having to join several tables. We currently have an explicit "latest version" field, that we maintain manually, just so we can build indexed view on top of it, but it would be nicer and more robust if we could just use MAX(version) directly in the JOIN condition of the indexed view.

    Branko Dimitrijevic supported this idea  · 
  9. 172 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 127

    <=-=Jun 23 2015 8:37AM=-=>

    I’m the first to post a useful comment. This must make me special.

    Seriously though, this would be an excellent solution to having to create a new “scratchdb” to hold my interim ETL data. This would be a major plus in simplifying design of a high performance app.

    <=-=Jul 3 2015 5:04AM=-=>

    In 2014, memory optimized tables, and delayed durability can be used help mitigate these Issues. However neither of this are always completely viable solutions. Brent’s proposed solution is likely the simplest way to achieve this with the least amount of unwanted impact. It is important to note that other platforms implement similar functionality as well. Notably Oracle.

    <=-=Nov 29 2016 3:58PM=-=>

    There are so many good things about this suggestion. I am amazed that SQL does not have the capability to turn off logging for certain tables that you define as no…

    Branko Dimitrijevic supported this idea  · 
  10. 9 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Branko Dimitrijevic supported this idea  · 
  11. 67 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 289

    <=-=Aug 9 2007 9:12AM=-=>

    Benefits:
    Improved Readability, therefore Maintainablity, therefore reduced manpower $ and time expenditure.

    <=-=Aug 27 2007 6:13PM=-=>

    I definitely see the value of this. Thanks for proposing it. We’ll try to squeeze it in to SQL Server 2008 but things are really tight in terms of room for changes like this. It has to compete with many other things, including a bunch that have a larger impact on query performance, or that don’t have an easy workaround. This issue has a workaround, though it is not pretty and programmability would be enhanced a lot with the proposed enhancement. I’ll see what I can do.

    Best regards,
    Eric

    <=-=Oct 17 2007 2:06PM=-=>

    Things do not look good for this enhancement for Katmai. It probably will not make it into the release. We’ll make a final assessment in a couple of weeks. Before we can consider this,…

    Branko Dimitrijevic supported this idea  · 
  12. 56 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  2 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Branko Dimitrijevic supported this idea  · 
  13. 111 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 278

    <=-=Feb 1 2008 7:18PM=-=>

    Thanks for the valuable suggestion.

    This seems more like adding the sequence support which we’re seriously considering for the next major release.

    Meanwhile, would using identity column help?

    <=-=Feb 2 2008 2:11AM=-=>

    It does not seem that you understood the request. This definitely has nothing to do with
    IDENTITY. I am less versed about sequences, but I don’t think they will cut it either. If you think
    it does, maybe you could provide an example? Take this problem: For the Orders table in
    Northwind, write a query that lists the number of orders for all days in 1997. The result set should
    include all 365 days, and list zero for days without a number.

    This is a typical problem where you need a table of of numbers (or dates). While it’s easy to
    create such a table, I argue in this request that…

    Branko Dimitrijevic supported this idea  · 
  14. 147 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  12 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Branko Dimitrijevic supported this idea  · 
  15. 12 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 174

    <=-=Oct 12 2006 2:21PM=-=>

    Dear Adam,

    Thanks for your feedback. I think this idea has quite a bit of merit. You would like a way to tell the query processor to evaluate query subexpressions in a constrained order. There is no perfect workaround right now. You could use a multi-statement TVF, but that is hard to program and hard to read. Your workaround requires an extra sort. We’ll consider this as an improvement for a future release.

    Regards,
    Eric

    <=-=Feb 4 2010 8:40AM=-=>

    SELECT x.EmployeeID,
    (
    SELECT COUNT_BIG(*)
    FROM HumanResources.Employee AS e
    WHERE e.ManagerId = x.ManagerID
    ) AS theCount
    FROM HumanResources.Employee AS x
    WHERE x.ManagerID IS NOT NULL;

    is also 4 logical reads – no materialization needed.

    In general, I’m not so sure about the need for this. If I truly need to materialize an intermediate result (which is typically quite small) I’m happy using a…

    Branko Dimitrijevic supported this idea  · 
    Branko Dimitrijevic commented  · 

    I think this should happen automatically when the same subtree figurates multiple times in the query plan. The more expensive the subtree execution is and the smaller its result is, the more likely it should be for the query optimizer to put a spool in front it and run the subsequent executions by just reading the spool.

    I'm not sure if transaction isolation has a role here, but this optimization should at least be safe under SNAPSHOT isolation.

    A hint could be there to nudge the optimizer in or out of this behavior, but should not be required when this is a clear win.

Feedback and Knowledge Base