Category: DataTypes

How do we identify LOB datatype columns in SQL Server

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


Thats it with this, hoping to see you with another blog post soon!

Advertisements

UNIQUEIDENTIFIER and Search behavior in SQL Server

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!!!

Unique

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.

ExePlan_Unique

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.

UniqueError

Hope you enjoyed this post, have your thoughts on the same.