Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: sql_variant datatype

    I am considering using the sql_variant datatype in a datamart because it will vastly simplify the schema and SQL procedures.

    Does anyone have any caveats about using sql_variant? Will I see a significant performance hit?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...you'll incur scans because you'll be making your predicates all stage 2

    An sql_variant data type must first be [n]cast[/b] to its base data type value before participating in operations such as addition and subtraction.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, but I tested it on a copy of the database that I converted to sql_variant and....suprisingly...both inserts and select statements ran 30% to 50% faster with the sql_variant implementation. This was the case even when I had to cast it into another datatype for aggregate SUMs.

    Completely unexpected.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    It'll only be a prob when you need to reference them in a predicate and they have to be converted...then you'll incur scans because they become nonsargable...

    Have you looked at SHOWPLAN?

    Have you had to use them in a predicate yet?

    How much data are we talking about...

    and when you say 30-50% fatser I'm assuming you're comparing it to an existsing db?

    Same box or different box?

    Compared at same peak transaction times?

    Production vs Dev?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Same box. Development. No activity.

    Execution plans were identical.

    I doubt that this column will be used in a predicate. It is the datavalue for a particular point in time, so all the filtering will be on indexed non-variant columns.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I thought you where going to convert EVERYTHING to sql_variant....


    Code:
    DECLARE @x datetime, @y sql_variant
    
    SELECT @x = GetDate()
    SELECT @x
    SELECT @y = @x
    SELECT @y
    SELECT @y = GetDate()
    SELECT @y
    Just curious...is the column indexed?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No. Not indexed.

    EVERYTHING as sql_variant!? You think much more creatively than I!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Or I just misread much better....

    Why did you think it would be faster...I would have thought the opposite...everytime you need to use any date function you'll have to convert....isn't that more overhead?

    Or is outweighed because it's stored a pure binary?

    I wonder....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I didn't think it would be faster. I expected it to be slower because I have to explicitly cast the value before aggregating it.

    I'm totally floored that it is faster.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Well...ok then, so WHY did you decide to try this?

    And BOL doesn't mention anything about speed....except that it's stored in binary....

    and How much data are we talking about?

    Need to check this out...what type of operations are we talking about...INSERTS and SELECTS?

    It'a a warehouse right...no UPDATES or DELETES?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    CREATE TABLE [dbo].[Operations]
    ([PropertyID] [uniqueidentifier] NOT NULL ,
    [DataMonth] [datetime] NOT NULL ,
    [LineItemCode] [varchar] (50) NOT NULL ,
    [Category] [char] (1) NOT NULL ,
    [DataValue] [sql_variant] NULL)

    PropertyID, DataMonth, LineItemCode, and Category form a unique key. Updates and inserts are made to this master table, and a trigger detects modifications and copies records to a history table.

    So the sql_variant value is returned, but is not subject to filters or joins.

    Using the sql_variant allows me to store other property-related information in the same table, such as renovation date and property manager ID.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Out of curiosity, Blindman, what sort of data is going in the table? I am getting flumoxed by this problem:

    declare @x sql_variant
    declare @y sql_variant

    select @x = 1, @y = convert(datetime, 1)

    if (@x=@y)
    begin
    print "hello"
    end


    Should print "hello" to my mind, but it doesn't.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    @Y is stored as the datetime value '1900-01-02 00:00:00.000'
    @X is stored as the integer value 1.

    The values need to be cast as the same type for comparisons (the rules are actually more complicated than that, with different type families taking precedence for sorting and comparisons)>

    This will work:

    declare @x sql_variant
    declare @y sql_variant

    select @x = 1, @y = convert(datetime, 1)

    if (@x=cast(@y as int))
    begin
    print 'hello'
    end
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or try this:

    declare @VariantTest table
    (Variant sql_variant)

    insert into @VariantTest (Variant) values(1)
    insert into @VariantTest (Variant) values('Hello')
    insert into @VariantTest (Variant) values(getdate())
    insert into @VariantTest (Variant) values(cast(getdate() as varchar(23)))

    select *
    from @VariantTest
    order by Variant
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I went through the books online about SQL_Variants, and my head is still winding down. One important note is that sql_variants are never implicitly converted, so now the query (and the explanation) make much more sense.

Posting Permissions

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