go backarticles

Articles of SQLschool.gr Team

The sql_variant Data type

Antonios Chatzipavlis

Με αφορμή για όμορφη κουβέντα που είχα με τους μαθητές μου στο σεμινάριο που κάνω αυτό το διάστημα αποφάσισα να γράψω αυτό το άρθρο ώστε να φωτίσω όσο το δυνατόν περισσότερο την ύπαρξη αλλά και την χρήση του 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*/


Antonios Chatzipavlis

Antonios Chatzipavlis

Antonios is a Data Solutions Consultant and Trainer. He has been working in IT since 1988. In his career, he has worked as senior developer, IT Manager, Solutions Architect and IT Consultant. Since 1995 he has been devoted on new technologies and software development tools, mainly by Microsoft, either by training company staff and colleagues or assisting them in design, development and implementation as a consultant or chief developer. He has focused in Databases and Data Science since 1995. He specialized in Microsoft SQL Server since version 6.0 in areas like SQL Server Internals, Database Design and Development, Business Intelligence and in 2010 he has started working with Azure Data Platform, NoSQL databases, Big Data Technologies and Machine Learning. He is an active member of many IT communities in Greece, answering colleagues' questions and writing articles in his web site. He is the owner of SQLschool.gr which is a community portal with a lot of information about Microsoft SQL Server. He has been a Microsoft Certified Trainer (MCT) since 2000. Microsoft honored him as MVP on Data Platform due to his activities in SQL Server since 2010. He holds a large number of Microsoft Certifications and Microsoft SQL Server Certifications since version 6.5.


Relative Articles

Leave your comment

COMMENT

FULL NAME

EMAIL ADDRESS

We use Gravatar

WEB SITE



captcha


 

Newsletter

If you want to receive updates from us subscribe below with your email.
Follow us in
PASS chapter logo

The Official PASS Local Group for Greece

About us Contact us Terms of Use Privacy Sing in Register
sql school greece logo
© 2010-2020 All rights reserved

This site uses cookies for operational and analytics purposes only. By continuing to browse this site, you agree to their use.