Με αφορμή για όμορφη κουβέντα που είχα με τους μαθητές μου στο σεμινάριο που κάνω αυτό το διάστημα αποφάσισα να γράψω αυτό το άρθρο ώστε να φωτίσω όσο το δυνατόν περισσότερο την ύπαρξη αλλά και την χρήση του sql_variant data type.

What is it?

Ένα field ή variable που έχει ορισθεί με το sql_variant data type μπορεί να περιέχει οποιαδήποτε τιμή οποιουδήποτε data type από αυτά που έχουμε διαθέσιμα στον SQL Server εκτός από text, ntext, timestamp και τα variable length με ΜΑΧ qualifier.

Με απλά λόγια ένα field σε ένα πίνακα που έχει ορισθεί σαν sql_variant μπορεί σε μια γραμμή να περιέχει ένα integer σε μια άλλη να έχει τιμή decimal σε μια άλλη να είναι datetime και σε κάποια άλλη κάτι άλλο.

Εξαιτίας αυτής την δυνατότητας που έχει το sql_variant το κατατάσσουμε στα variable length data types.

Internal structure

Εσωτερικά ένα sql_variant data type έχει την εξής δομή:

• Το 1ο byte δείχνει το πραγματικό data type που είναι αποθηκευμένο μέσα στο sql_variant και η τιμή που αυτό έχει είναι στην ουσία η τιμές που βρίσκουμε στο xtype field του systypes system table, πχ αν έχουμε βάλει σαν τιμή κάποιον integer η τιμή που έχει το byte 1 είναι ίση με 56.
• To 2o byte δείχνει την έκδοση του sql_variant και είναι πάντα 1 στο SQL Server 2014.
• Τα υπόλοιπα bytes περιέχουν την αποθηκευμένη τιμή και περισσότερες πληροφορίες αυτή. Οι πληροφορίες αυτές αποθηκεύονται στην αρχή των bytes αυτών. Για παράδειγμα αν στο sql_variant field είχα αποθηκεύσει ένα decimal τότε στην αρχή των bytes αποθηκεύεται το precision (1 byte) και το scale (1 byte) και μετά η τιμή. Αν βάλω στο sql_variant ένα character data types στην αρχή χρειάζονται 2 bytes για κρατηθεί το max length και 4 bytes για το collation ID. Αντίστοιχα για τα binary και τα varbinary θέλει 2 bytes στην αρχή για το max length.

To use it or not?

Σαν γενικό κανόνα όσοι είμαστε γνώστες του SQL Server λέμε ότι καλό είναι να αποφεύγεται η χρήση του εξαιτίας του ότι χρειάζεται περισσότερη μνήμη και χώρο αλλά και περισσότερη διαχείριση όπως φαντάζομαι ότι είναι κατανοητό εξαιτίας των παραπάνω.

Πέρα όμως από αυτά υπάρχουν και κάποια ακόμα που πρέπει να γνωρίζουμε για το data type αυτό όπως:

• Δεν μπορεί να γίνει primary key
• Δεν μπορεί να συμμετέχει σε computed column
• To LIKE δεν δουλεύει με sql_variant fields
• Oι OLEDB και ODBC providers αυτόματα κάνουν convert το sql_variant σε nvarchar(4000) !!!!
• Τέλος για να μην έχουμε εκπλήξεις στα αποτελέσματα θα πρέπει πάντα να κάνουμε convert στο πραγματικό data type που είναι αποθηκευμένο στο sql_variant.

Get internals metadata

Μια function που είναι απαραίτητη εφόσον χρησιμοποιήσουμε το sql_variant είναι η SQL_VARIANT_PROPERTY και η οποία μπορεί να πει τα πάντα για τι τελικά έχουμε αποθηκεύσει όπως στα παρακάτω παραδείγματα

declare @var sql_variant;

set @var = 'Antonis';
select    @var as [Value]
,        SQL_VARIANT_PROPERTY(@var,'BaseType') as [BaseType]
,        SQL_VARIANT_PROPERTY(@var,'Precision') as [Precision]
,        SQL_VARIANT_PROPERTY(@var,'Scale') as [Scale]
,        SQL_VARIANT_PROPERTY(@var,'TotalBytes') as [TotalBytes]
,        SQL_VARIANT_PROPERTY(@var,'Collation') as [Collation]
,        SQL_VARIANT_PROPERTY(@var,'MaxLength') as [MaxLength];

set @var = cast ('2016/09/15' as datetime2 );
select    @var as [Value]
,        SQL_VARIANT_PROPERTY(@var,'BaseType') as [BaseType]
,        SQL_VARIANT_PROPERTY(@var,'Precision') as [Precision]
,        SQL_VARIANT_PROPERTY(@var,'Scale') as [Scale]
,        SQL_VARIANT_PROPERTY(@var,'TotalBytes') as [TotalBytes]
,        SQL_VARIANT_PROPERTY(@var,'Collation') as [Collation]
,        SQL_VARIANT_PROPERTY(@var,'MaxLength') as [MaxLength];

set @var = cast (10.23 as decimal(10,2) );
select    @var as [Value]
,        SQL_VARIANT_PROPERTY(@var,'BaseType') as [BaseType]
,        SQL_VARIANT_PROPERTY(@var,'Precision') as [Precision]
,        SQL_VARIANT_PROPERTY(@var,'Scale') as [Scale]
,        SQL_VARIANT_PROPERTY(@var,'TotalBytes') as [TotalBytes]
,        SQL_VARIANT_PROPERTY(@var,'Collation') as [Collation]
,        SQL_VARIANT_PROPERTY(@var,'MaxLength') as [MaxLength];

Practical use of sql_variant

Όπως ανέφερα και παραπάνω καλό είναι να αποφεύγουμε την χρήση της. Παρόλα αυτά όμως προσωπικά αλλά και αρκετοί άλλοι έχουμε χρησιμοποιήσει το συγκεκριμένο data type σε μια περίπτωση και αυτή είναι όταν στην database μας έχουμε κάποιον πίνακα παραμέτρων όπου κάθε παράμετρος ορίζεται σε ξεχωριστή γραμμή και που η τιμή της κάθε παραμέτρου μπορεί να είναι οποιοδήποτε data type όπως για παράδειγμα

create table dbo.appconfig
(
    paramname nvarchar(20)
,    paramdesc nvarchar(1000)
,    paramvalue sql_variant
)

Enjoy
/*antonch*/