CREATE FUNCTION DBO.ISUNIQUEIDENTIFIER ( @InputVal nvarchar(50) ) RETURNS SmallInt AS BEGIN RETURN CASE WHEN SERVERPROPERTY('PRODUCTMAJORVERSION') >=11 THEN CASE WHEN TRY_CONVERT(UNIQUEIDENTIFIER,@InputVal) IS NOT NULL THEN 1 ELSE 0 END ELSE ( CASE WHEN LEN(@InputVal) = 36 AND @InputVal LIKE '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' + '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' + '-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' + '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' + '[A-F0-9][A-F0-9][A-F0-9][A-F0-9]' THEN 1 ELSE 0 END ) END END
Converting a datatype to another datatype is a very common requirement in real world and we usually do it with CAST or CONVERT (eg. int to char or varchar etc).
There are two types of conversions – implicit and explicit.
Explicit conversions are the conversions done by developers using SQL server functions like cast/convert etc and Implicit conversions are mainly managed by SQL Server internally. If you want to see these types of conversions, you can check for IMPLICIT_CONVERT in the execution plan generated by SQL Server.
Today, we are going to see a curious case of an implicit conversion – varchar to uniquidentifier.
SQL Server does a implicit conversion from varchar to uniqueidentifier by default as below:
Declare @varchar nvarchar(MAX), @uniqueidentifier uniqueidentifier Set @varchar ='f124656c-136b-4beb-ab3a-b348053f898a,7f0b0dd5-22bc-421b-9416-3a7c24146a98' Set @uniqueidentifier = @varchar Select @uniqueidentifier
Now, if you look at the results, you can see the first 36 character is being converted implicitly. Since its a trivial, the operator may not be able to see in your execution plan.
The word of caution
Sometimes, if we are assigning more values/characters in the varchar variable, it would ONLY pick the first 36 characters and ignoring the rest characters by default. This default character of the conversion may end up with a wrong results, however, there would not be any sign of error! So beware of this characteristic while you writing a code.
Large OBject (LOB) data types are datatypes that can store large data. These datatypes can hold values larger than 8K row size.Prior to SQL Server 2005, we have only TEXT, NTEXT and IMAGE to hold large data. With SQL 2005, Microsoft has introduced additional three datatypes to hold large values like VARCHAR(MAX),NVARCHAR(MAX) and VARBINARY(MAX).
How to identify LOB columns in your database?
The below query is useful to identify LOB columns. Please note that the query contains a filter not to fetch CDC schema objects. Feel free to add more filters as required.
SELECT T.TABLE_CATALOG DATABASE_NAME,T.TABLE_SCHEMA AS SCHEMA_NAME, T.TABLE_NAME, C.COLUMN_NAME,C.DATA_TYPE DATA_TYPE,C.CHARACTER_MAXIMUM_LENGTH MAXIMUM_LENGTH,C.COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS C INNER JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_SCHEMA = T.TABLE_SCHEMA AND C.TABLE_NAME = T.TABLE_NAME WHERE T.TABLE_TYPE = 'BASE TABLE' AND ((C.DATA_TYPE IN ('VARCHAR', 'NVARCHAR') AND C.CHARACTER_MAXIMUM_LENGTH = -1) OR DATA_TYPE IN ('TEXT', 'NTEXT', 'IMAGE', 'VARBINARY', 'XML', 'FILESTREAM')) AND T.TABLE_SCHEMA NOT IN('CDC') -- EXCEPTION LIST ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME
How do we identify the length or size of LOB datatype in SQL Server
At times, you may need to understand the length or size of your LOB columns. One example is to set a right value for “max text repl size (B)” to avoid certain issues like this. Usually, we use a function LEN to get the length of the data, however, LEN is not compatible certain datatypes like TEXT,NTEXT and IMAGE.
To resolve, SQL Server has another function – DATALENGTH(). The function returns the number of length or size of the data in bytes. The below query will help to identify the length or size of LOB datatypes like TEXT, NTEXT, IMAGE etc.
--Table creation script Create Table tablename (columnname image) --Sample data insert (The data has been trimmed for readability) Insert into tablename values('0xFAEFCD8FFE000104A46494600010100000100010000FFDB00840009060') SELECT DATALENGTH(columnname) as inBytes, DATALENGTH(columnname) / 1024.0 as inKb, DATALENGTH(columnname) / 1024.0 / 1024.0 as inMb FROM tablename DROP Table tablename
This post explains about a different behavior with search for UNIQUEIDENTIFIER datatype column in SQL Server.
If you would have worked with UNIQUEIDENTIFIER before, you would have come across this scenario earlier. But for those not, let us see the scenario.
CREATE TABLE TEST (COLUMN1 UNIQUEIDENTIFIER)
INSERT INTO TEST VALUES(‘F7ABC3AA-0534-44F3-BDBB-0011CEFB6993′)
–Search a value in the column by adding “extra” characters
SELECT * FROM TEST WHERE COLUMN1=’F7ABC3AA-0534-44F3-BDBB-0011CEFB6993.XML’
You will expect the search will not return any data as it can not find the exact match, however, it returns results!!!
What is reason for this strange behaviour?
No, this is not a strange behaviour, its an intended and default behaviour in SQL Server for UNIQUEIDENTIFIER.
Let us look at what happens behind the scene.First step is enable the execution plan and execute the SELECT query.
As per the execution plan, SQL Server does an Implicit conversion of the value to uniqueidentifier before the search happens.As a result, only first 36 characters is being used for the search condition and returns the results.Any characters more than 36, would be ignored in the search condition.
For completeness, Please note that if we are passing a value that can not be converted as unique identifier, it will throw an exception as below.
SELECT * FROM TEST WHERE COLUMN1=’F7ABC3AA-0534-44F3-BDBB-0011CEFB699′
Msg 8169, Level 16, State 2, Line 5
Conversion failed when converting from a character string to uniqueidentifier.
Hope you enjoyed this post, have your thoughts on the same.