Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006
    Posts
    17

    Question Unanswered: A puzzle of data type in SQL Server 2005

    Recently I began to use SQL 2005. But yesterday I found a puzzle about data type.
    I use this script to create a table:
    Code:
    CREATE TABLE [dbo].[InvoiceMaster](
    ......
    [InvoiceMemo] [varchar](max) NULL,
    ......
    )
    But when I run this cmd : exec sp_columns InvoiceMaster. I find the type_name of this column changed to "text".
    Can anybody tell me why?

    Thanks in advance!

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by yunkun_yang
    Recently I began to use SQL 2005. But yesterday I found a puzzle about data type.
    I use this script to create a table:
    Code:
    CREATE TABLE [dbo].[InvoiceMaster](
    ......
    [InvoiceMemo] [varchar](max) NULL,
    ......
    )
    But when I run this cmd : exec sp_columns InvoiceMaster. I find the type_name of this column changed to "text".
    Can anybody tell me why?

    Thanks in advance!
    Don't put the VARCHAR keyword in square brackets. It's killing my feng shui.

    Anyhow, according to this article, VARCHAR(MAX) is different from TEXT in that string manipulation functions work on it whereas they don't work on TEXT. It used to be that if you wanted to do string manipulation on text that it had to be less than 8000 characters long. Now you can do it thanks to the MAX datatypes. Since they're still VARCHAR values, all the functions will work on them just fine.

    Most importantly, to the marketing department, they have these new MAX datatypes that sound cool. We're lucky it's not VARCHAR(EXTREME) or something like that.

    If it weren't for marketing they probably would have just extended the string ops to TEXT and done the equivalent for the binary types. In fact, this is precisely what they did, and the MAX datatypes are just aliases to BLOB and CLOB types. So the reason VARCHAR(MAX) shows up as TEXT is because it *is* TEXT.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by sco08y
    Most importantly, to the marketing department, they have these new MAX datatypes that sound cool. We're lucky it's not VARCHAR(EXTREME) or something like that.
    they wanted to call it varchar(TO_THE_MAX) but it was shot down sadly.

Posting Permissions

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