SQL Server DB Objects MCQs

Question 1: Which is the smallest numeric data type in SQL Server?

Answer: D

A bit is the smallest numeric type that has 0, 1, and NULL values and a size of one byte.

Visit SQL Server Data Types for more information.

Question 2: What modes of authentications are supported in SQL Server?

Answer: A, B

SQL Server supports Windows authentication and SQL Server authentication modes.

Windows authentication mode enables local Windows authentication with SQL Server, where you can log in with your local Windows credentials.

In SQL Server Authentication, logins are created in SQL Server that are not based on Windows user accounts. Both the user name and the password are created by using SQL Server and stored in SQL Server. 

Learn Windows Authentication and SQL Server Authentication in detail.

Question 3: Which statement in the following is a valid check constraint?

Answer: C, D

Check constraints are used to specify limitations on the values being inserted in a column. It specifies the accepted values. The syntax for check constraint is:

CONSTRAINT CHECK()

Visit Check Constraint in SQL Server for information.

Question 4: What are the different types of table relationships in SQL Server?

Answer: D

Table relationship helps in enforcing referential integrity which in turn contributes to a more efficient database.

In a One-to-One relationship, one record of the first table will be linked to zero or one record of another table.

One-to-Many is the most commonly used relationship among tables. A single record from one table can be linked to zero or more rows in another table.

Many-to-Many relationship lets you relate each row in one table to many rows in another table.

Visit DB Tables Relationships for more information.

Question 5: What are the types of stored procedures?

Answer: A, B

User-defined procedures: A User-defined stored procedure is created by a database user in a user-defined database or any System database except the resource database.

System procedures: System procedures are included with SQL Server and are physically stored in the internal, hidden Resource database and logically appear in the sys schema of all the databases. The system stored-procedures start with the sp_ prefix.

Visit Stored-procedures in SQL Server for more information.