Deck 4: Creating SQL Server 2012 Databases

Question 1

What should you define if you want to grant several users the same permissions to database objects?

A)schema
B)user
C)role
D)access list

Answer 1

C

Question 2

Which of the following is true about the physical file structure of a SQL Server database?

A)it must have at least one log and one data file
B)only a data file is required
C)there are two data files for every log file
D)log files organize data in tables and indexes

Answer 2

A

Question 3

Which of the following is true about filegroups?

A)filegroups are only used when there is more than one data file
B)there can be only one data file per filegroup
C)each database must have a primary filegroup
D)multiple data files should be of different sizes for best performance

Answer 3

C

Question 4

Many tasks can be performed with SQL Server Management Studio, but to delete a database, you need to use a SQL statement in the SQLCMD command prompt.

Question 5

Which attribute include binary and numeric as categories?

A)column
B)data type
C)row
D)record labels

Question 6

What should you always do before creating a new database?

A)backup the master system database
B)install a fresh instance of SQL Server 2012
C)delete all existing temporary databases
D)stop and restart the SQL Server service

Question 7

Since it is easy to make configuration changes after a database is created, it's best to get the database created and make changes as necessary afterward.

Question 8

The widest reaching type of permission in a SQL Server is database permissions.

Question 9

Which of the following is true about database creation and configuration?

A)an overly complex database is more secure
B)other administrators should be able to understand and manage it
C)configuration changes should be made frequently
D)client applications automatically adjust to logical object model changes

Question 10

Which of the following is a logical container that groups together collections of objects within a database and allows them to be managed as a group?

A)filegroup
B)data file
C)transaction
D)schema

Question 11

Which of the following is true about log files?

A)they aid in database roll back
B)log files are optional for each database
C)there can be only one log file per database
D)they should be stored on the same physical disk as data files

Question 12

Which of the following is best described as a logical unit of work reading or writing from a database?

A)I/O performance
B)database transaction
C)log file record
D)individual filegroup

Question 13

Which of the following is true about the model database template?

A)it cannot be customized
B)changes to it will affect existing databases
C)new databases will inherit settings from it
D)modifying model database settings requires different steps than user databases

Question 14

Which of the following is true about the Autogrowth setting?

A)by default, it doubles the size of the log file when it becomes full
B)the data file is affected by it but log files are not
C)you should keep this value as small as possible
D)you should configure it so that file growth occurs infrequently

Question 15

Which of the following is a required setting that must be supplied when creating a database?

A)the SQL Server name
B)a unique database name
C)the master database configuration
D)the user credentials

Question 16

The NEW table SQL language statement is used to make a new table.

Question 17

Which of the following is true about the size of data and log files?

A)you should not change the default sizes
B)increasing the size on an existing database will degrade performance
C)you should not set the size larger than the data requires
D)best practice is to change the Maxsize setting from its default value

Question 18

What permission must be granted for a user to run procedures within a database?

A)Execute
B)Read
C)List
D)Server-level

Question 19

Which of the following is true about database permissions?

A)access to databases is controlled by Windows logon authentication
B)database users are defined for each database
C)server-level permissions provide the least access to SQL databases
D)permissions can only be set at the server or database level

Question 20

The two main physical file types in an SQL Server database are data files and log files.

Question 21

___________ performance is a term used to describe the number of read and write operations that can be processed by the physical disk in a given time frame.

Question 22

How is the Autogrowth setting used on data files and log files and what is a best practice for this setting?

Question 23

What are the two main approaches to creating a new user database?

Question 24

If you want to set the maximum number of digits a numeric data type can hold, what should you set?

A)precision
B)length
C)scale
D)size

Question 25

How are log files used for database reliability?

Question 26

Describe how to create a database using the SQL programming language and explain why it might be the preferred method.

Question 27

Describe how SQL Server 2012 supports authentication for connecting to the database server.

Question 28

The _____________ size setting defines the size of the database when it is first created.

Question 29

What happens if you need to compare two values of different data types?

A)the system is halted
B)the value with the highest precision is returned
C)an 'incompatible type' error occurs
D)a data type conversion is performed

Question 30

Describe how the Initial Size setting is used when defining a database. How can this setting affect database performance?

Question 31

Describe how filegroups are used in a database.

Question 32

If you want to set the number of places after the decimal point in a numeric data type, what should you set?

A)precision
B)length
C)scale
D)size

Question 33

Why should log files be stored on a different physical disk from data files?

Question 34

The ______________ statement is used to change an existing database object.

Question 35

Before creating a new database, make a full backup of the _____________ system database.

Question 36

Tables and ____________ are the main building blocks of a database.

Question 37

Which of the following SQL statements deletes an object?

A)ALTER
B)REMOVE
C)DROP
D)DEL

Question 38

Which of the following is true about naming objects?

A)the database management system provides strict rules
B)Camel case does not use special characters to link words
C)tables should always be named with a character prefix
D)A column name of Last Name is preferable to LastName

Question 39

How is the model system database used in database creation?

Question 40

What are roles and how are they used in database security and permissions? Provide an example of a predefine server role.