T-SQL Interview Questions On Data Types

When working with a unit of information, T-SQL provides different data types. It is very important to understand that every data type is a constraint, and you should think twice before defining them.

In this article, I collected interview questions on data types. I participated in many interview calls and I was asked (though sometimes I asked the interviewee) many times about most of the below interview questions.

The purpose of this article is not to teach you data types from scratch. You can find related information here.

Question 1: What type of information piece can be converted to BIT data type automatically?

Answer

Any number and TRUE/FALSE data can be converted to BIT.

SELECT CAST('TRUE' as bit) as '_1_',
CAST('FALSE' as bit) as '_0_',
CAST('5667' as bit) as '_1_',
CAST(874 as bit) as '_1_',
CAST('-456' as bit) as '_1_',
CAST('0' as bit) as '_0_'

Below conversions will fail,

Question 2: What are the differences between money and decimals?

We use them for different purposes.

1. Money is a “static” type. This means that if you have money-typed information, this will not be a good choice for arithmetical operations/calculations. The reason for that is that you lose precision. Decimals are the best choice for this type of operation:

 2. Money automatically adds scaling to your digits. Decimals are configurable. If you want, you CAN “bypass” scaling.

3. Money uses eight bytes in memory, but decimals' use depends on your configuration. (Maximum storage sizes vary, based on the precision.)

4. Money rounds information to four scaled data points:

Question 3: What is the difference between numeric and decimal?

Answer: There are no differences between them. You can easily use them interchangeably.

Question 4: What is the difference between Float and decimal?

Answer: Decimal is an exact numerical type. It doesn’t allow rounding, but float rounds data. Use float when you don't need to store exact scaling. The float will allow you to store a large amount of data without “details.”

Question 5: What is the difference between Float and real?

  1. Real = float(24)
  2. Real takes four bytes of memory, while float uses (depending on configuration) four or eight bytes.

Question 6: What are the differences between datetime and datetime2?

Answer

  • Datetime uses eight bytes of memory; datetime2 is configurable and can take 5-8 bytes
  • Datetime starts from 1750-01-01; datetime2 starts from 0001-01-01 year
  • Datetime is not accurate. You can easily lose nanoseconds, and in the worst case, this can affect seconds and minutes as well. Unfortunately, it can ROUND your date information.

  • Datetime is not ANSI Compotable.
  • Datetime2  is a better wrapper over datetime.

Question 7: What is the difference between datetime and datetime2 when working with formatted date-time information?

1. When working with the “yyyymmdd” format, both datetime and datetime2 behave the same. This is a universal, recommended format.

2. For “mdy” + “yyyy-mm-dd” formats, they behave the same to one another.

3. Setting “dmy”/”ydm” with incorrect formatting will fail for datetime, but works for datetime2.

4. Setting “ydm” with incorrect formatting will fail for datetime2.