Common Questions related data types

Q1: What are different Character data types?

Common used character data types;

Data Type	Length (Max Character)	Storage Size	Unicode
char	Fixed (8,000)	Always n bytes	No; each character requires 1 byte
varchar	Variable (8,000)	Actual length of entry in bytes	No; each character requires 1 byte
nchar	Fixed (4,000)	Twice n bytes	Yes; each character requires 2 bytes
nvarchar	Variable (4,000)	Twice actual length of entry in bytes	Yes; each character requires 2 bytes

Note:

1.      Don’t use nchar or nvarchar unless you truly need it. (Unicode provides a unique number for up to 65,536 characters. ANSI, the one most of us are most familiar with, has only 256.) Unless you’re working with an international application, you probably don’t need a Unicode data type.

2.      Use the smallest data type necessary, but make sure it can accommodate the largest possible value.

3.      Use a fixed-length data type when the values are mostly about the same size.

4.      Use a variable length when the values vary a lot in size.

Q2: What are different Integer data types?

Use integer data types to store numeric data that the application evaluates as numbers.

Common used integer data types;

Data type	Min value	Max value	Storage
tinyint	0	255	1 byte
smallint	-32,768	32,767	2 bytes
int	-2,147,483,648	2,147,483,674	4 bytes
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807	8 bytes

Q3: What is the difference between numeric and decimal?

There’s no difference between the numeric and decimal data types. Use them interchangeably or use one or the other to store integer and floating-point numbers scaled from 1 to 38 places, inclusive of both sides of a decimal. Use this data type when you need to control the accuracy of your calculations in terms of the number of decimal digits.

Total characters (precision)	Storage size
1 - 9	5 bytes
10 - 19	9 bytes
20 - 28	13 bytes
29 - 38	17 bytes

Q4: What is the difference between float and real?

The only differences between float and real are their minimum and maximum values and their required storage.

Data type	n	Min Value	Max value	Precision	Storage
float(n)	1 - 24	-1.79E + 308	1.79 + 308	7 digits	4 bytes
	25 - 53	-1.79E + 308	1.79E + 308	15 digits	8 bytes
real	n/a	-3.40E + 38	3.40E + 38	7 digits	4 bytes

Q5: What is the difference between smalldatetime and datetime?

Both smalldatetime and datetime store a combination date and time value, but the minimum and maximum values, accuracy, and storage size are different.

Data type	Min value	Max value	Accuracy	Storage size
smalldatetime	January 1, 1900	June 6, 2079	Up to a minute	4 bytes (the first 2 bytes store the date; the second 2 bytes store the time)
datetime	January 1, 1753	December 31, 9999	One three-hundredth of a second	8 bytes (the first 4 bytes store the date; the second 4 bytes store the time)

Q6: What’s the difference between smallmoney and money?

Use both smallmoney and money to store currency values. However, the minimum and maximum values for both differ.

Data type	Minimum value	Maximum value	Storage size
smallmoney	-214,748.3648	214,748,3647	4 bytes
money	-922,337,203,685,477.5808	922,337,203.685,477.5807	8 bytes

Q7: Where’s the Boolean data type?

SQL Server doesn’t have a Boolean data type, at least not by that name. To store True/False, Yes/No, and On/Off values, use the bit data type. It accepts only three values: 0, 1, and NULL. (NULL is supported by SQL Server 7.0 and later.)

Q8: What happened to text, ntext, and image?

SQL Server is phasing out text, ntext, and image. There’s no way to know how long SQL Server will support the older data types. Upgrade legacy applications to varchar, nvarchar, and varbinary.

Q9: What is a user-defined data type?

user can create his own data type for handling data that does not fit in one of the existing data types.

Create a user-defined data type when you specify the same limitations often.

To create a user-defined data type

1. In Object Explorer, expand Databases, expand a database, expand Programmability, expand Types, right-click User-defined Data Types, and then click New User-defined Data Type.
2. In the New User-defined Data Type dialog box, in the Schema box, type the schema to own this data type, or use the browse button to select the schema.
3. In the Name box, type a name for the new data type.
4. In the Data type box, select the data type that the new data type will be based on.
5. Complete the Length, Precision, and Scale boxes if appropriate for that data type.
6. Check Allow NULLs if the new data type can permit NULL values.
7. In the Binding area, complete the Default or Rule boxes if you want to bind a default or rule to the new data type. Defaults and rules cannot be created in SQL Server Management Studio. Use Transact-SQL. Example code for creating defaults and rules are available in Template Explorer.

Thanks