http://www.sql-server-helper.com/free-test/sql-question.aspx

 

What is the maximum date value that can be stored in a SMALLDATETIME data type?

SQL Server stores SMALLDATETIME values as two 2-byte integers.  The first 2 bytes store the number of days after January 1, 1990.  The other 2 bytes store the number of minutes after midnight.  Date ranges from January 1, 1900 through June 6, 2079, with accuracy to the minute.

Which of the following is NOT a valid description of the public role?

A. The public role captures all default permissions for users in a database.

B. The public role cannot be dropped.

C. The public role is contained in every database, including msdb, tempdb, model and all user databases except in the master database for security purposes.

D. The public role cannot have users, groups or roles assigned to it.

E. None of the Above

 

(Answer) C

 

What will be the result of the following statement?

SELECT ROUND(123.89, -1)

(Answer) 120.00

What will be the output of the following statement?

SELECT LEN(CAST(LEFT('026-100', 3) AS INT))

(Answer) 2

When getting the length of an integer value, any leading 0's are not included in the length.  The output of the LEFT function is '026', which is then converted to an integer value giving it a value of 26.

What will be the result of the following SELECT statement?

SELECT 7/3

(Answer) 2

Dividing an INT value with another INT value will result to an INT value.

You have a column that will only contain values from 0 to 256.  What is the most economical data type to use for the column?

A. TINYINT

B. SMALLINT

C. INT

D. DECIMAL(3)

E. VARCHAR(3)

F. None of the Above

 

(Answer) SMALLINT

A SMALLINT data type uses only 2 bytes of data and can store value from -32,768 to 32,767.  A TINYINT data type cannot be used because the maximum value it can hold is 255.  Although an INT data type can be used, SMALLINT will use only 2 bytes compared to INT's 4 bytes.  VARCHAR(3) will use 3 bytes while DECIMAL(3) will use 5 bytes.

 

Which of the following is the correct way of changing the owner of a user database?

A. ALTER DATABASE MODIFY OWNER=''

B. ALTER DATABASE SET OWNER=''

C. ALTER DATABASE ALTER OWNER=''

D. USE
EXECUTE [dbo].[sp_changeowner] ''

E. USE
EXECUTE [dbo].[sp_changedbowner] ''

F. None of the above.

(Answer) E

To change the owner of a user database, the [dbo].[sp_changedbowner] system stored procedure is used.  There is no option in the ALTER DATABASE statement that will allow the changing of the owner of a user database.

 

Which of the following is NOT a valid description of the db_securityadmin fixed database role?

A. Manages roles and members of database roles.

B. Manages statement permissions in the database.

C. Adds or removes Windows groups and users, and SQL Server users in the database.

D. Manages object permissions in the database.

E. None of the Above

 

(Answer) C

All of these are valid descriptions of the db_securityadmin fixed database role except for adding or removing of Windows groups and users and SQL Server users in the database, which is the role of the db_accessadmin fixed database role.