Post reply

LOB data types

  • August 24, 2016 at 2:14 pm

    #1897322

    GilaMonster (8/24/2016)

    Filestream isn't a datatype. It's an attribute of a varbinary data type column. Bigint is definitely not considered as large object, it's a grand total of 16-bytes in size.

    Your LOB data types are those that can go over 8kb in size.

    TEXT, NTEXT, IMAGE (deprecated SQL 2000 data types)

    VARCHAR(MAX), NVARCHAR(MAX), VARBINARY(MAX), XML

    I thought a bigint was 8 bytes (rather than 16).

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • August 24, 2016 at 2:35 pm

    #1897330

    ScottPletcher (8/24/2016)

    I thought a bigint was 8 bytes (rather than 16).

    You are correct. For some reason I was thinking int = 8 when I wrote that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 25, 2016 at 8:17 am

    #1897485

    Got to the bottom of it.

    There were some included columns in the index which were of type varchar(max) which was what was being picked up by SQL Server. Although the table columns being used by the indexes were fine.

    Now I just need to figure out how to bring back index included column data types in my query.

    Thanks

  • August 25, 2016 at 9:41 am

    #1897511

    Yup, included columns are part of the index, just like key columns.

    Any query that returns the key columns should return the include columns as well, unless they're getting explicitly filtered out. Include columns are listed in sys.index_columns, just like key columns.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 25, 2016 at 9:58 am

    #1897524

    Ok thanks to everyone for your help.

  • August 25, 2016 at 10:12 am

    #1897535

    When I run John's query against my test table (which contains a VARCHAR(MAX) data type column in it), it returns the data type as 'varchar'.

    So it seems to miss out the (MAX) from the end of the data types. Same for NVARCHAR(MAX) columns in my table which are displayed simply as NVARCHAR in the query output.

  • August 25, 2016 at 10:16 am

    #1897541

    navtec (8/25/2016)

    When I run John's query against my test table (which contains a VARCHAR(MAX) data type column in it), it returns the data type as 'varchar'.

    So it seems to miss out the (MAX) from the end of the data types. Same for NVARCHAR(MAX) columns in my table which are displayed simply as NVARCHAR in the query output.

    Check max_length. If it is -1 that is the MAX data type.

  • August 25, 2016 at 10:18 am

    #1897542

    max length for the varchar(max) and nvarchar(max) columns are 8000.

    I was hoping they would be -1.....

  • August 25, 2016 at 10:20 am

    #1897546

    The documentation is incorrect about that for sys.types.

    Pull max_length from sys.columns instead, and it should correctly show -1.

    Cheers!

  • August 25, 2016 at 10:21 am

    #1897547

    FYI: https://msdn.microsoft.com/query/dev10.query?appId=Dev10IDEF1&l=EN-US&k=k(SYS.TYPES_TSQL);k(SQL12.SWB.TSQLRESULTS.F1);k(SQL12.SWB.TSQLQUERY.F1);k(MISCELLANEOUSFILESPROJECT);k(DevLang-TSQL)&rd=true

  • August 25, 2016 at 10:22 am

    #1897548

    That's worked for sys.columns max length, which is correctly showing -1 for those columns.

    Thanks.

  • August 25, 2016 at 10:24 am

    #1897550

    navtec (8/25/2016)

    max length for the varchar(max) and nvarchar(max) columns are 8000.

    I was hoping they would be -1.....

    Not any where I have seen. I am pulling this info on a regular basis doing schema checks.

  • August 25, 2016 at 10:29 am

    #1897554

    The values in sys.types show max length. VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) aren't different data types from VARCHAR(n), NVARCHAR(n), or VARBINARY(n) which is why you don't see the max_length of -1 in sys.types. If you look at the documentation for the sys.tables it tells you the a max_length of -1 indicates that those types are defined as a MAX type.

  • August 26, 2016 at 2:05 am

    #1897721

    Ah yes. My mistake. I've edited the offending post.

    John

Viewing 14 posts - 16 through 28 (of 28 total)

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