Category: DataTypes

DataTypes, SQL

Computed columns in SQL Server

What are computed columns in SQL Server

Computed columns are virtual columns that does not physically store the data in SQL Server unless it is marked as PERSISTED column. Computed columns can be defined by expression that can use other columns of the same table or static expressions.

Limitations

  1. It cannot be part of INSERT/UPDATE statement.
  2. It cannot defined as FOREIGN KEY/DEFAULT or NOT NULL constraint.

How to add a computed column while creating a table?

Create Table SampleComputedTable
(
	EmpName varchar(50),
	DOB Date,
	Age as Datediff(year,DOB,GETDATE())
)

--Today is -> '2021-07-25'; for simple explantion, the Age is calculated only year part.
Insert into SampleComputedTable Values ('Latheesh','2019-07-25')

Select * From SampleComputedTable

Drop table SampleComputedTable

How to add a computed column to an existing table? 

Alter Table SampleComputedTable Add AgeInMonths as DateDiff(month,DOB,Getdate())

Select * From SampleComputedTable

What are the best practices while creating a computed column?

There is NO way to alter computed column. You will have to drop and recreate it. 

How to find the definition of a computed column?

Select object_name(object_id) TableName, name as ColumnName, definition From sys.computed_columns 
where name='Age' and object_name(object_id) = 'SampleComputedTable'

If you enjoyed this blog post, feel free to share it with your friends!

DataTypes, SQL

CAST and CONVERT in SQL Server

CAST and CONVERT are two functions that explicitly converts an expression from one data type to another data type.

Syntax:


-- CAST Syntax:
CAST ( expression AS data_type [ ( length ) ] )

-- CONVERT Syntax:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Let us see a quick example of CAST and CONVERT as below.

Select cast(getdate() as varchar(max))
Select convert(varchar(max),getdate(),101)
Select convert(varchar(max),getdate(),102)

If we observe the difference, CAST has only a one form of way, however, CONVERT is feasible to convert to different results as we define the style of the format. There are many different style options, and you can refer those in “standard date CONVERT formats” section of ready reckoner date post.

In fact, CAST is changing as CONVERT internally while executing the query. We can simply look at the execution plan and understand this behavior as below screen shot.

SQL Server Data Type Conversion Chart:

The below chart is a beautiful way of representing the Data type conversion in SQL Server.

Credit: https://www.microsoft.com/en-us/download/confirmation.aspx?id=35834

Points to ponder:

  • CAST is an ANSI SQL Standard, however, CONVERT is a SQL Server specific.
  • CAST is single form result function, other hand, CONVERT function results as style format defined.
  • There are NO major notable difference observed in performance.

If you enjoyed this blog post, feel free to share it with your friends!

DataTypes, SCRIPT GALLERY, SQL

ISUNIQUEIDENTIFIER function in SQL Server
Today, I had a requirement to check a value can be converted as uniqueidentifier datatype or not, so quickly come up with the below function. Hope this would help you if you come across similar requirement in future.

Code: 

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

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

DataTypes, Errors and Exceptions, SQL

Curious case of varchar to uniqueidentifier in SQL Server

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.

DataTypes, SCRIPT GALLERY, SQL

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!