Shulou(Shulou.com)06/01 Report--

SQL Prompt automatically retrieves according to the object name, syntax and code snippet of the database to provide users with appropriate code choices. Automatic script settings make the code easy to read-especially useful when developers are not familiar with scripts. SQL Prompt can be installed and used, which can greatly improve the coding efficiency. In addition, users can customize it as needed to make it work in the desired way.

This tutorial explains the "quirks" of the SQL_VARIANT data type and why it's best to investigate when SQL Prompt reminds you to use it. If you explicitly convert it to a real type before using it, it is only safe to store the data as SQL_VARIANT.

The sql_variant data type comes from values of several different data types and is used internally by SQL Server. It is not part of the SQL standard and has limited use in relational databases. It needs to be handled with care, as its misuse can lead to untraceable performance problems and bug. Sql_variant cannot be passed directly to certain SQL operators and functions, such as LIKE, SUM (), or AVG (), and can produce misleading results when used in comparisons or expressions. Except for binary data, it cannot be returned to the application through ODBC.

SQL Server is a strongly typed language that ensures data integrity, efficient storage, and efficient retrieval. For this reason, using sql_variant is a bit strange, so it's not surprising that it can inadvertently cause problems by using it unwisely. For these reasons, SQL Prompt enforces the Best practices Code Analysis Rule (BP024), which reminds you to use the sql_variant data type.

Like many "best practice" rules, these suggestions sometimes sound like telling people not to run with scissors. In this case, the data can be stored as secure only if it is explicitly converted to a real type before using the sql_variant.

Why is there sql_variant?

The sql_variant data type was first introduced when Microsoft developed SQL Server from Sybase. They need Microsoft Access, which can enter the database market for the first time from Microsoft, to import the database into SQL Server, which supports variant data types. It is still used within SQL Server for data such as parameters and extended properties of system stored procedures.

Sql_variant tends to be the catch-all data type of a column, variable, parameter, or value returned as a user-defined function. It can hold up to 8000 bytes and can store basic data types such as integers, decimals, strings, and dates. It cannot store other data types, such as the (MAX) data type, the CLR data type, or XML.

Sometimes sql_variant can be a useful tool, such as when dealing with inconsistent or unspecified data types, usually because the database supports applications that allow users to define data.

It stores the basic data types of the values it contains, so when it is used as a mediation, all conversion rules between data types are enforced. You can use the data type function to retrieve this basic data type sql_variant_property ():

DECLARE @ MyVariant SQL_VARIANT = '2.3657' SELECT SQL_VARIANT_PROPERTY (@ MyVariant,'BaseType')

Return varchar in this case. There are also some other useful properties: Precision, Scale, TotalBytes, Collation, and MaxLength. If you are generating a primary key from sql_variant, the TotalBytes parameter makes the function available as a preliminary check because the total size of the primary key (or index) is limited to 900 bytes.

By the way, you can use this function on any data type. For example:

SELECT SQL_VARIANT_PROPERTY (N'B é o á ed mac OCL á in', 'collation')

Polymerization

Let's see what happens if we try to aggregate sql_variant columns. For simplicity, we will do this from the derived table.

SELECT Sum (ValueAsVariant) FROM (VALUES (Convert (SQL_VARIANT, 'one'), 1, Convert (SQL_VARIANT, 1)), (' two', 2,2), ('three', 3,3), (' four', 4,4), ('five', 5,5)) AS f (ValueAsString, ValueAsInt, ValueAsVariant)

We see an error:

Msg 8117 Magi level 16 Magi State 1 Magi Line 3 Operand data type sql_variant is not valid for sum operators.

And if we explicitly convert it to numbers (int, numeric, and so on), it works fine.

SELECT Sum (Convert (NUMERIC (9jue 4), ValueAsVariant)-- try sum, avg, stdev, stdevp, var, varp, or string_agg FROM (VALUES (Convert (SQL_VARIANT, 'one'), 1, Convert (SQL_VARIANT, 1)), (' two', 2,2), ('three', 3,3), (' four', 4,4), ('five') 5, 5) AS f (ValueAsString, ValueAsInt, ValueAsVariant)

Sql_variant data types that seem to work well with max () and min () aggregate functions, so there can be no technical problems preventing other functions from working.

Compare

You cannot filter sql_variant columns with LIKE because LIKE does not support the sql_variant parameter.

SELECT f.ValueAsVariant, f.ValueAsInt, f.ValueAsString FROM (VALUES (Convert (SQL_VARIANT,'one'), 1, Convert (VARCHAR (5), 1)), ('two', 2,2), (' three', 3,3), ('four', 4,4), (' five', 5,5)) AS f (ValueAsVariant, ValueAsInt) ValueAsString) WHERE ValueAsVariant like t%'

When something goes wrong, it goes like this:

Msg 8116,Level 16,State 1,Line 4

The parameter data type sql_variant is not valid for argument 1 of the LIKE function.

In fact, no string function accepts sql_variant and does not attempt to implicitly convert strings. Instead, they just reject the parameters. On the contrary, if we declare what type of data type it is, it works as follows:

SELECT f.ValueAsVariant, f.ValueAsInt, f.ValueAsString FROM (VALUES (Convert (SQL_VARIANT,'one'), 1, Convert (NVARCHAR (5), 1)), ('two', 2,2), (' three', 3,3), ('four', 4,4), (' five', 5,5)) AS f (ValueAsVariant, ValueAsInt) ValueAsString) WHERE Convert (VARCHAR (20), ValueAsVariant) like't%'

Unless you sql_variant explicitly converts data types in the WHERE clause, you may get incorrect results when hiding in a long and tortuous process, and the reason is hard to detect. For example, this returns only rows 4 and 5, which is what you expect:

DECLARE @ ParameterAsINT INT SELECT @ ParameterAsINT = 3 SELECT f.ValueAsString, f.ValueAsInt, f.ValueAsVariant FROM (VALUES ('one', 1, Convert (SQL_VARIANT, 1)), (' two', 2,2), ('three', 3,3), (' four', 4,4), ('five', 5,5) AS f (ValueAsString, ValueAsInt) ValueAsVariant) WHERE ValueAsVariant > @ ParameterAsInt

But what happens if we change the parameter to a sql_variant and provide it with a string value?

DECLARE @ ParameterAsVariant sql_variant SELECT @ ParameterAsVariant ='3' SELECT f.ValueAsString, f.ValueAsInt, f.ValueAsVariant FROM (VALUES ('one', 1, Convert (SQL_VARIANT, 1)), (' two', 2,2), ('three', 3,3), (' four', 4,4), ('five', 5,5)) AS f (ValueAsString) ValueAsInt, ValueAsVariant) WHERE ValueAsVariant > @ ParameterAsVariant

Now it returns all the lines you might not have thought of. The problem here is that to evaluate an expression, SQL Server examines its base type or type family and compares it with our variable type. The base type family of sql_variant can be Unicode, exact numbers, approximate numbers, date and time, binary, or unique identifiers, and our ValueAsVariant column contains exact numbers.

In the first example, which returns only rows 4 and 5, our parameter type belongs to the same family as the type of the ValueAsVariant column. SQL Server performs implicit conversion and the code works properly. However, in the second example, we use the parameter sql_variant with a string value, where @ ParameterAsVariant contains Unicode. Instead of implicitly converting the Unicode type to an exact number (that is, the "advanced" data type), SQL Server determines that the advanced data type is "larger", so our search criteria evaluate to true for each row.

This is obviously a quirk of sql_variant. If we compare two sql_ variant values of exactly the same basic data type, it will "work". If we compare sql_variant to another data type in the same series, implicit conversion will allow it to work. Other than that, nothing is possible.

ODBC support

ODBC does not fully support sql_variant. You will notice this when using joins with tables of type sql_variant, because sql_variant when you use Microsoft OLE DB Provider for ODBC (MSDASQL), the data in the column is returned as binary data (for example, 0x32303931).

Restrict the use of sql_variant in indexes

Sql_variant can include columns in an index only if the total length of the index is less than the maximum of 900 bytes. This means that if the length of the value exceeds 900 bytes, the insert operation on the index sql_variant column will fail. If we create a table or table variable:

DECLARE @ MyTableVariable TABLE (MyProperty sql_Variant PRIMARY KEY)

We got a warning:

Warning! The maximum key length of a clustered index is 900 bytes. The maximum length of the index "contention _ # B2961DC__8E45D1198BEEA325" is 8016 bytes. For some large value combinations, the insert or update operation will fail.

If we ignore the warning.

DECLARE @ MyTableVariable TABLE (MyProperty sql_Variant PRIMARY KEY) INSERT INTO @ MyTableVariable (MyProperty) VALUES (N'Abb á n moccu Corbmaic'), (N'Abel of Reims'), (N'BU í te [Boetius] mac Br ó naig'), (Numbu Buriana'), (Replicate (N'Caill í n [Caill é n] mac Niataig Crom mac Feradaig, Comgall mac S é tnai, Comg á n mac D á Cherda, Comma á n mac F á elchon, Mo Chomm ó cCr ó n á n of Balla, see Mo Chua mac B é c á in', 3))

We get mistakes.

Ms 1946,Level 16,State 3,Line 45

The operation failed. An index entry with a length of 980 bytes for index "contention _ # B72883F__8E45D1191C112AAE" exceeds the maximum length of 900 bytes for a clustered index.

Conclusion

Sql_variant 's use of data types in user tables is a smell of code because it introduces untyped data types into strongly typed languages and requires investigation, just as you smell burning at home. It may just be a barbecue, but it may be more worrying.

Sql_variant has legitimate uses, but there is always a risk that while you may know exactly how to use them, others who must maintain or debug your code may not, and if you do anything other than simply using them, it is most likely to cause problems for storage.

You should never rely on the implicit conversion of sql_variant, because it often fails, either because it is not implemented or because it is strange. Instead, explicitly convert them to the SQL data type before making comparisons, expressions, or aggregations. If you're not entirely sure you understand the last sentence, it's best never to use sql_variant.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.