Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview Prep - SQL > Data Types > Flashcards

Data Types Flashcards

1
Q

Name 6 high level categories of data types.

A
  1. Exact Numeric (INT, NUMERIC)
  2. Approximate numeric (FLOAT, REAL)
  3. Character strings (CHAR, VARCHAR)
  4. Unicode character strings (NCHAR, NVARCHAR)
  5. Binary Strings (BINARY, VARBINARY)
  6. Date and Time (DATE, TIME, DATETIME2, SMALLDATETIME,DATETIME,DATETIMEOFFSET)

There are more than this, but these are the 6 mentioned in the book.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is important to consider when choosing a data type?

A
  1. Data types are constraints. Use the appropriate data type to invoke appropriate data constraints.
  2. The operators and functions you will be able to use.
  3. The size of the data type.
  4. Whether the data type should be fixed or variable.
  5. Whether to use a Regular or Unicode character type.
  6. Length of the data type.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Where should formatting of a value take place?

A

Formatting of a value should be the responsibility of the application when data is presented.

For example, you shouldn’t use a data type of string because you want a date to be displayed in a specific format. You should use a date data type and then depend on the application to display it in the appropriate format.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

When should you use the FLOAT or REAL data types?

A

When you are certain that imprecise numbers will be acceptable.

REAL or FLOAT data types are approximate; therefore, not all values in the data type range can be represented exactly. If you need your value in the data type to be exact, then do not use one of these data types.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Why use FLOAT or REAL data types when there are more precise data types available to use?

A

The benefit in these types is that they can represent very large and very small numbers beyond what any other numeric type that SQL Server supports can represent.

So, for example, if you need torepresent very large or very small numbers for scientific purposes and don’t need completeaccuracy, you may find these types useful.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How is a data type a constraint?

A

The data type you choose will limit the type of data that can be entered.

For example, if you choose a date data type then no value other than a valid date will be allowed, if you choose a numeric data type then only numbers will be allowed, etc.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Why should you consider what operators and functions you will be able to use when selecting a data type?

A

Choosing the right data type ensures you will be able to use the operators and functions best suited to the values entered.

For example, if you use a numeric data type then + would add values. If you use a character data type then + will concatenate values.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is a general rule when choosing the size of the data type?

A

Always use the smallest type that serves your needs for the long run.

For example, use TINYINT for an attribute that will have a value no larger than 100. TINYINT uses only 1 byte, if you used INT then 4 bytes would be used. However, say you are using an ID field that will grow to over a billion, INT will serve short term needs but you must use BIGINT in this scenario to prepare for the future.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

When should you use a fixed data type and when should you use a variable data type?

A

Consider using a fixed data type if update performance is a priority since the row will not have to physically expand and therefore data shifting isn’t necessary.

A variable type may be preferable if read performance is a priority. The less storage used, the less there is for a query to read, and the faster the query can perform.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

If read performance is a priority but the best data type to use is a fixed data type, what can you do to improve performance?

A

You can use compression.

If you use compression, especially row compression, a fixed data type will be stored like a variable data type, but with lower overhead.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

When should you use a Regular character data type?

A

When your data will only be in English and one other language.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

When should you use a Unicode character data type?

A

If your data is international (many languages) or your application natively works with Unicode.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

What are the different storage requirements for Regular vs Unicode character types?

A

Regular character types use 1 byte of storage per character.

Unicode character types use 2 bytes of storage per character, unless compressed.

Surrogate pairs use 4 bytes of storage.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

How can the storage requirements of Unicode data types be mitigated?

A

By using Unicode compression.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What happens if you don’t specify length for a data type that allows a length designation?

A

SQL Server will assign a default length. It is therefore best practice to always choose a length for those data types that allow a length specification.

SQL Server 70-461 02-02a

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What happens if attributes that are going to be used as join columns (such as primary key and foreign key) are given different data types?

A

SQL Server has to apply implicit conversion of one attribute’s type to the other. This could have negative performance implications, like preventing efficientuse of indexes.

SQL Server 70-461 02-02a

17
Q

What happens if an expression uses operands of different types? For example, 1 + ‘1’.

A

SQL Server usually converts the one that has the lower data type precedence to the one with the higher. For the example of 1 + ‘1’, the VARCHAR value ‘1’ is converted to the INT value 1. This gives a result of 2 instead of ‘11’. You can always take control by using explicit conversion.

SQL Server 70-461 02-02a

18
Q

What happens if all of the operands in the expression are of the same type?

A

The result is of that type as well. In some cases this may not be desirable. For example, 5 / 2. Both 5 and 2 are of type INT so the result is the INT value 2 not the NUMERIC value 2.5. If you wish the result to be 2.5, both the column that the 5 comes from and the column that the 2 comes from will have to be explicitly converted to NUMERIC so the result is NUMERIC.

CAST(col1 AS NUMERIC(12, 2)) / CAST(col2 AS NUMERIC(12, 2))

SQL Server 70-461 02-02a

19
Q

What does scale mean, with regard to numeric data types?

A

Scale is the number of digits to the right of the decimal point.

SQL Server 70-461 02-02a

20
Q

What are the 5 date/time data types?

A
  1. DATE
  2. DATETIME
  3. DATETIME2
  4. DATETIMEOFFSET
  5. SMALLDATETIME

SQL Server 70-461 03-01

21
Q

What is the best practice overall for choosing a data type?

A
  1. Choose one that will take the least amount of disk space but still capture your data adequately
  2. Choose one that won’t need to be changed when your table fills with data

SQL Server 70-461 08-01

22
Q

What is the best practice for choosing date, time or date and time data type?

A

Use
DATE
TIME
DATETIME2

They store data more efficiently and with better precision than
DATETIME
SMALLDATETIME

SQL Server 70-461 08-01

23
Q

What is the best practice for large text data type and image data type?

A

Use
VARCHAR(max)
NVARCHAR(max)
VARBINARY(max)

Instead of
TEXT
NTEXT
IMAGE

SQL Server 70-461 08-01

24
Q

What is the best practice when using a number data type?

A
  1. DECIMAL and NUMERIC are the same data type but use DECIMAL, its more descriptive
  2. ONLY use FLOAT or REAL if you really need floating-point precision and make sure to be familiar with possible rounding issues

SQL Server 70-461 08-01

25
Q

What are best practices for chooseing a character data type?

A
  1. If length will vary use NVARCHAR or VARCHAR
  2. If length is fixed use NCHAR or CHAR

SQL Server 70-461 08-01

26
Q

How do data types affect FK?

A

The FK must have the same data types as the column(s) it is referencing

SQL Server 70-461 08-02

Interview Prep - SQL (26 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions