SQL_VARIANT_PROPERTY

• tilew-948340

  Hall of Fame

  Points: 3431

  More actions

  April 10, 2011 at 9:12 am

  #249269

  Why the row 10 has a value of "17" in SQLvariant TB column results?

  If you execute this script:

  CREATE TABLE tableA(colA sql_variant, colB varchar(10), colC nvarchar(10),colD int )

  INSERT INTO tableA values ( cast (1 as tinyint ),'a','a', 1)

  INSERT INTO tableA values ( cast (1 as smallint ),'aa','aa', 2)

  INSERT INTO tableA values ( cast (1 as int), 'aaa', 'aaa',3)

  INSERT INTO tableA values ( cast (1 as bigint ),'aaaa','aaaa', 4)

  INSERT INTO tableA values ( cast (1 as decimal(38,9)),'aaaaa','aaaaa', 5)

  INSERT INTO tableA values ( cast (1 as decimal(38,19)),'aaaaaa','aaaaaa', 6)

  INSERT INTO tableA values ( cast (1 as decimal(38,29)),'aaaaaaa','aaaaaaa', 7)

  INSERT INTO tableA values ( cast (0.1 as decimal(38,9)),'aaaaaaaa','aaaaaaaa', 8)

  INSERT INTO tableA values ( cast (0.1 as decimal(38,19)),'aaaaaaaaa','aaaaaaaaa', 9)

  INSERT INTO tableA values ( cast (0.1 as decimal(38,29)),NULL,NULL, 10)

  SELECT colD,

  SQL_VARIANT_PROPERTY(colA,'TotalBytes') AS 'SqlVariantTB' ,

  SQL_VARIANT_PROPERTY(colB,'TotalBytes') AS 'varcharTB',

  SQL_VARIANT_PROPERTY(colC,'TotalBytes') AS 'NvarcharTB'

  FROM tableA

  You will get this answer:

  colD, SqlVariantTB, varcharTB, NvarcharTB

  1,3,9,10

  2,4,10,12

  3,6,11,14

  4,10,12,16

  5,9,13,18

  6,13,14,20

  7,21,15,22

  8,9,16,24

  9,13,17,26

  10,17,NULL,NULL

  In the results:

  The columns of SQLvariantTB would be "Number of bytes required to hold both the metadata and data of the value"

  The columns of varcharTB would be the results of (the number of character + 8 bytes for the TotalBytes property of SQL_VARIANT_PROPERTY )

  The columns of NvarcharTB would be the results of (the number of character * 2) + 8 bytes for the TotalBytes property of SQL_VARIANT_PROPERTY )

  So, why the row 10 does not have the value of '21' (like the row 7) in SQLvariantTB column results

  a- The "NULL" value in columns B and C makes the answers unpredictable

  b- The "0" in the number makes the answers unpredictable

  c- The answer "17" is correct because the precision and scale makes it predictable

  d- None of the answers