1. What is the best data type to store the birthdays of the US Presidents, starting with George Washington's birthday of February 22, 1732?

A. DATETIME
B. SMALLDATETIME
C. INT
D. VARCHAR

2. Which of the following is NOT true about the TRUNCATE TABLE command?

A. Removes all records from the table.
B. Resets the identity column to the seed of the column.
C. Delete triggers are executed.
D. It cannot be used on tables referenced by foreign tables.

3. What will be the value of @TodaysDate in the following script?

DECLARE @TodaysDate DATETIME
SET @TodaysDate = '12/25/2005'
SET @TodaysDate = @TodaysDate + 5

A. 12/25/2010
B. 12/30/2005
C. 04/24/2006
D. An error is generated. You cannot add together a DATETIME value and an INT value.

4. What will be the value of @TodaysDate in the following script?

DECLARE @TodaysDate Varchar(10)
SET @TodaysDate = '12/25/2005'
SET @TodaysDate = @TodaysDate + 5

A. 12/25/2010
B. 12/30/2005
C. 04/24/2006
D. An error is generated. You cannot add together a DATETIME value and an INT value.

5. Which of the following statements is the correct syntax for adding a new column called Gender into a table called dbo.Employees that already contains data?

A. ALTER TABLE dbo.Employees ADD COLUMN Gender CHAR(1) NOT NULL
B. ALTER TABLE dbo.Employees ADD COLUMN GENDER CHAR(1) NULL
C. ALTER TABLE dbo.Employees ADD Gender CHAR(1) NOT NULL
D. ALTER TABLE dbo.Employees ADD Gender CHAR(1) NULL

6. What will be the output of the following statement?

SELECT CHARINDEX('is','Missississippi', -1)

A. 0
B. 2
C. 8
D. -1
E. NULL
F. Statement will generate an error. Only positive integer values are allowed.

7. What will be the output of the following statement?

SELECT CHARINDEX('Missississippi', 'is', -1)

A. 0
B. 2
C. 8
D. -1
E. NULL
F. Statement will generate an error. Only positive integer values are allowed.

8. What's the maximum length that can be specified in an NVARCHAR data type in SQL Server 2000?

A. 2000
B. 4000
C. 8000
D. No Limit

9. What will be the result of the following statement?

SELECT ROUND(123.89, -1)

A. 120.00
B. 123.00
C. 123.90
D. 124.00

10. What's the maximum number of parameters can a SQL Server 2000 stored procedure have?

A. 128
B. 256
C. 512
D. 1,024

Answers :

1. C

The earliest date that can be stored in a DATETIME data type is January 1, 1753 while the earliest date that can be stored in a SMALLDATETIME data type is January 1, 1900 so neither of these data types can be used. Both an INT data type and a VARCHAR data type can be used but between these two data types, the best option is an INT data type where the date value can be stored in the YYYYMMDD format. An INT data type will only use 4 bytes while a VARCHAR data type storing the same date value will need 8 bytes.

2. C

When the contents of a table are removed using the TRUNCATE TABLE command, delete triggers are not executed. Delete triggers are only triggered when the DELETE command is used.

3. B

Adding an INT value to a DATETIME value is like adding that number of days to the DATETIME value. This is similar to using the DATEADD function and passing DD as the first parameter (the datepart parameter).

4. D

Conversion failed when converting the varchar value '12/25/2005' to data type int.

5. D

To add a column to an existing table, the ALTER TABLE command is used with the ADD option (without the word COLUMN). Since there's data on the table already and since no default value has been specified, the column should be created with the NULL option. Otherwise an error will be encountered.

6. B

The CHARINDEX function returns the starting position of the specified expression in a character string. The third parameter of the CHARINDEX functions is the character position to start searching for the first parameter in the second parameter. If the starting location is a negative number, the search starts at the beginning of the second parameter

7. A

8. B

NVARCHAR data types store variable-length Unicode data with a maximum length of 4,000 characters. On the other hand, VARCHAR data types store variable-length non-Unicode data with a maximum of 8,000 characters.

9. A

The ROUND function returns a numeric expression rounded to the specified length or precision. The second parameter of the ROUND function is the precision to which the input numeric expression is to be rounded. When the second parameter is a negative number, the numeric expression is rounded on the left side of the decimal point, as specified in the length.

10. D

The maximum number of parameters a stored procedure in SQL Server can have is 1,024, which is also the maximum number of columns a table can have.