Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2005
    Posts
    27

    Unanswered: Convert ntext to nvarchar for ORDER BY?

    I need to sort by an ntext field, but it won't let me do it.

    However, if I cast the field as nvarchar(100), I can use ORDER BY on that.

    Is there any reason that this is a bad idea? In my testing, ordering by a converted ntext field was actually *faster* than ordering by an nvarchar (same data in the fields).

    Josh

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ntext is for huge chunks of data (up to 2 gigabytes)

    if you can easily sort on the first 100 characters, i would suggest that you do so

    i can't see that people are going to notice the difference in sort order beyond the 100th character if in fact two 2-gigabyte fields are identical in the first 100 characters

    and why sort on 2 gigabytes if you don't have to?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    It doesn't inherently guarantee uniqueness but you might be able to live with that. You wouldn't be able make use of an index on this column.
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  4. #4
    Join Date
    Jan 2005
    Posts
    27
    100 characters is plenty for my needs - I just need to sort the recordset for display to the user. Only the first 100 characters will be displayed in the list, anyway.

    I'm just wondering if there's a performance penalty due to the conversion, or anything like that.

    Thanks!
    Josh

  5. #5
    Join Date
    Dec 2004
    Location
    California, USA
    Posts
    93
    ou will be sorting on a non-indexed column which will be a performance issue.
    Whoever imagines himself a favorite with God holds others in contempt. - Robert Green Ingersoll, lawyer and orator (1833-1899)

  6. #6
    Join Date
    Jan 2005
    Posts
    27
    Ok, thanks. These columns are user-customizable, so I don't think we will be using indexes on them. Are there any other issues that I need to worry about?

    Josh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •