SQL Server interview questions on data types

 Common Interview Questions on SQL Server DataTypes

What is the difference between CHAR and VARCHAR ?

CHAR always allocates fixed data length as defined to store strings but varchar uses variable length.

So If we have CHAR(5) and VARCHAR(5) and we set it with 'AB' then CHAR will append 3 spaces after 'AB' and will be 'AB   ' but VARCHAR(5) will store 'AB'. Then why CHAR ? Reason is CHAR with fixed length can give you better indexing and compression than VARCHAR which may lead to better query performance. 

When should we use CHAR and VARCHAR ?

If we are using short code like State abbreviations which are 2-3 character long then CHAR is suggested but we have names, description then VARCHAR is recommended.

What is the difference between CHAR and NCHAR, VARCHAR and NVARCHAR ?

In the above question N represents Unicode character when we are sure that we are not going to store any unicode character like Chinese or arabic letter then we use VARCHAR but for unicode character we use it as NVARCHAR.

You can try the below SQL to see the difference 

DECLARE @UnicodeChar NVARCHAR(5) = N'٭'

DECLARE @NonUnicodeChar VARCHAR(5) = '٭'

SELECT @UnicodeChar, @NonUnicodeChar

Can we convert an int to bit ?

Yes we can convert an int to bit, any non zero value gets converted to 1 and 0 to 0.

Can we convert bigint to int ?

We can convert only those values which lies in int range else we get arithmetic overflow error.

What is the difference between DATETIME and DATETIME2 ?

DateTime2 gives better time accuracy by extending the decimal point from milliseconds to microseconds.

DECLARE @DT2 DATETIME2 = GETDATE()

DECLARE @DT DATETIME = GETDATE()

SELECT @DT2 [DATETIME2],@DT [DATETIME]