SQL Server Column Attributes - Quiz

The answers you selected are indicated below, along with text that explains the correct answers.

1.	Victoria is defining a table. Her company has 75,000 employees. What datatype must she use to define the EmployeeID column to use the least amount of disk storage, but allow for the identity to be set for the column? Please select the best answer.
	A.	decimal
	B.	text
	C.	money
	D.	int
	The correct answer is D. Because the identity flag must be set, you must define the table with a datatype that allows for numeric data. The int datatype takes only 4 bytes to store. A is incorrect because it takes a minimum of 5 bytes to store. B is incorrect because it does not allow an identity flag. C is incorrect because it does not allow the identity flag and takes 8 bytes to store.

2.	Winston notices that an application using the database he designed is truncating some data the data that users type in. The users are entering numeric data. What is the most likely reason for this to happen? Please select the best answer.
	A.	The scale is set too low.
	B.	The varchar datatype does not allow for enough characters to be typed.
	C.	The datatype is a timestamp column.
	D.	The datatype is a Uniqueidentifier.
	The correct answer is A. The scale being set too low can cause this behavior. Data to the right of the decimal point will simply be truncated. B is incorrect because the users are entering numeric data, not text data. C and D are both incorrect because the uniqueidentifier and timestamp columns cannot be updated. Data cannot be truncated if it cannot be inserted or updated.

3.	You wish to create a table containing columns A, B, C, and D. You define columns B and D to allow NULL values. You define columns A and B as being the primary key. What will happen when you issue the Transact-SQL statement to create the table? Please select the best answer.
	A.	The table will be created as directed.
	B.	The table will be created as directed, but without the primary key.
	C.	The table will not be created and will result in error.
	D.	The table will not be created unless all columns make up the primary key.
	The correct answer is C. The table will not be created because the primary key cannot contain NULL values. A and B are incorrect because no table will be created. D is incorrect because even if all columns make up the primary key, the definition given indicates that column B allows null values. Null values are not allowed in the primary key.

4.	You have defined a column as a tinyint datatype. You have also set the identity flag on the column with a seed value of 1 and an increment value of 100. How many rows will you be able to insert into the table? Please select the best answer.
	A.	100
	B.	1
	C.	3
	D.	255
	The correct answer is C. A tinyint can store a value of 0 to 255. The first value is the seed value of 1. The next is the seed value plus the increment value, or 101. The final value would be 201. The forth insert would generate a value of 301, which is invalid for the datatype. Because of this reasoning, A, B, and D are all incorrect.

5.	What is significant about a GUID? Please select the best answer.
	A.	It takes the same amount of space as a char(5) datatype.
	B.	It is guaranteed to be unique across every database and every table in the world.
	C.	It is guaranteed to be unique within your database only.
	D.	It takes the same amount of space as an int datatype.
	The correct answer is B. A GUID is guaranteed not to be duplicated or regenerated. A is incorrect because a char(5) will take 5 bytes too, but the GUID always takes 16 bytes to store. C is incorrect because a GUID is not only unique within your database, but all databases in the entire world. D is incorrect because an int datatype takes 4 bytes to store and a GUID takes 16 bytes to store.

6.	Harry notices that his tables are going to contain a lot of phone number data. He does not want to specify the datatypes, flags, and attributes for each of the phone numbers. What is the best way he can speed the process of defining the phone number columns? Please select the best answer.
	A.	He can create a user-defined datatype, called “phonenum.”
	B.	He can cut and paste these attributes for columns.
	C.	There is nothing he can do. He must type in the datatypes, flags, and attributes for each column..
	D.	He can use the number datatype.
	The correct answer is A. The datatype, flags, and attributes can be specified in a user-defined datatype. B is incorrect because although this may work, it is not efficient and is not the best answer. C is incorrect because he can absolutely speed the process by creating a user-defined datatype. D is incorrect because the number datatype is not related to a phone number.

7.	What SQL Server function would you use to find the next available GUID for a Uniqueidentifier datatype? Please select the best answer.
	A.	NEWGUID
	B.	NEWID
	C.	NEWIDENT
	D.	NEWGLOBAL
	The correct answer is B. The NEWID function is used to find the next available GUID for a Uniqueidentifier datatype. Answers A, C, and D are all incorrect because these are invalid functions.