Post reply

create index on nvarchar value

  • November 28, 2012 at 7:46 am

    #393685

    Hi.

    i have a news table.

    My table fields are like this:

    NewsId int PK

    Header nvarchar(max)

    Content nvarchar(max)

    TimeCreated smalldatetime

    .....

    .....

    PageSlug nvarchar(max)

    NewsId is Primary key. i use PageSlug filed for url.

    For example; http://www.---.com/news/%5Bb%5Dnew-record-added%5B/b%5D. In this link new-record-added is my page slug.

    Can i create an index on PageSlug field for becoming my searching process is fast?

    Thanks in advance.

  • November 28, 2012 at 7:55 am

    #1563167

    Yes, but no.

    Assuming that all the length of the strings are less than 450 characters it will work.

    An indexes key length is 900 bytes, now as NVARCHAR(MAX) can go above 450 characters, anything larger than this will fail on insert or update

  • November 28, 2012 at 8:05 am

    #1563178

    thanks it worked. i also want to ask one question. pwhen pageslug is set as index, searching time increase. i know right, do i?

    Thanks.

  • November 28, 2012 at 8:29 am

    #1563195

    You need to look at the execution plan of the query to understand which indexes it accessed. If query time increased, then adding that index did not help.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • November 28, 2012 at 8:31 am

    #1563199

    I would strongly recommend changing your column to NVARCHAR(450) to prevent any insert/update failures.

    But if query time has increased after adding the index, then the index doesnt help.

  • November 28, 2012 at 10:20 am

    #1563309

    i already set pageslug nvarchar(450).

    and i couldset pageslug field as index.

    As i know, indexed fields increase searching time. For example primary key. But i don't know exactly, this is true or not.

  • November 28, 2012 at 3:59 pm

    #1563496

    sa.ordekci (11/28/2012)

    ...As i know, indexed fields increase searching time. For example primary key.

    Indexes usually make queries faster. Primary keys usually make queries faster because an index is created when you create a PK (clustered unless otherwise specified.) The caveat is that an index does no good unless it's used and, for tables that are updated frequently, an un-used index is a liability because it does not help performance and slows down updates/inserts/deletes.

    ...But i don't know exactly, [if] this is true or not.

    The sure-fire way to find out is to test for yourself. SSC is a great place to learn about SQL. Your PC or Server is the place to see if what you learned was true.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • November 28, 2012 at 4:44 pm

    #1563515

    I'll also add that you pretty much blow yourself out of the water when using any blob-type including things like NVARCHAR(MAX) and XML when it comes to being able to reindex tables in an "online" fashion in the Enterprise Edition.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply