How can we improve Windows Azure Data Management: SQL Database?

← Data Management: SQL Database

Support Rebuilding Indexes Online for Tables with Large Objects

Following the guidance from

http://blogs.msdn.com/b/cbiyikoglu/archive/2010/04/03/handling-error-40522-while-creating-dropping-and-rebuilding-large-indexes-in-sql-azure.aspx

I attempted to rebuild a heavily fragmented index using Online=ON:

ALTER INDEX ALL ON [Backups] REBUILD WITH(ONLINE = ON)

And received the following error:

'ONLINE INDEX DDL WITH LARGE OBJECT' is not supported in this version of SQL Server.

My table contains a varbinary(max) field. This is a serious flaw. Please fix.

139 votes
Vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Forgot password?
    Create a password
    I agree to the terms of service
    Signed in as (Sign out)
    Close
    Close
    You have left! (?) (thinking…)
    DPedenDPeden shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    anonymousanonymous shared a merged idea: Enable sys.dm_db_index_physical_stats() so we can index fragmentation levels.  ·   · 

    13 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Forgot password?
      Create a password
      I agree to the terms of service
      Signed in as (Sign out)
      Close
      Close
      Submitting...
      • Anonymous commented  ·   ·  Flag as inappropriate

        Hello, i would like to ask that what is the benefits of sql training, what all topics should be covered and it is kinda bothering me … and has anyone studies from this course http://www.wiziq.com/course/125-comprehensive-introduction-to-sql of SQL tutorial online?? or tell me any other guidance...
        would really appreciate help… and Also i would like to thank for all the information you are providing on sql training.

      • MikeMike commented  ·   ·  Flag as inappropriate

        Throws this error with nvarchar(4000) also. This is very important because it negatively impacts both performance and billing.

      • Shane MiltonShane Milton commented  ·   ·  Flag as inappropriate

        I agree with the original poster. This is a SERIOUS flaw. I have a table w/ 250M records in it and it has nvarchar(max) as it contains up to ~32k unicode characters (although usually much smaller). There is no known work-around to this as far as I can tell. :-/

      • Saravanan SubburayalSaravanan Subburayal commented  ·   ·  Flag as inappropriate

        currently the following data columns are not supported for REBUILD ONLINE.
        * XML index
        * Spatial index
        * Large object data type columns: image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml

        For the time being, get to know the max size of binary info that will accomodate in the varbinary column and provide, for eg., if you are sure that it will not have data more than 1024 b, then change varbinary(max) -> varbinary(1024).

      • SaketSaket commented  ·   ·  Flag as inappropriate

        Please enable this DMV to view index fragmentation details.

      • AnonymousAnonymous commented  ·   ·  Flag as inappropriate

        With the product we're developing it is difficult to gauge per-tenant and overall database usage. This is required so that we can effectively split tenants across databases.

      Data Management: SQL Database

      Feedback and Knowledge Base

      (thinking…)