SQL Server Mock Test, SQL Server quiz, SQL Server MCQ 2024

SQL Server 2008 introduces support for Windows PowerShell. Windows PowerShell is a powerful scripting shell that lets administrators and developers automate server administration and application deployment. SQL Server provides two Windows PowerShell snap-ins that implement:
1. SQL Server provider: A SQL Server provider, which enables a simple navigation mechanism similar to file system paths.
2. Set of cmdlets: A SQL Server provider, which enables a simple navigation mechanism similar to file system paths.

Declarative Management Framework is a system for managing the instances of SQL Server 2008. It is a policy based system. The database security can tighten with polity management, automated administration and explicit administration. A policy can be designed for prohibiting the usage of unauthorized applications and the naming conventions on the database are implied for developers.

The above statement is true. Hot Add CPU feature allows a database for scaling on demand for extending memory resources added online, The CPU resources can be supported for SQL Server 2008 on hardware platforms that supports, without the need of application downtime.

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column. You can create an indexed view on a partitioned view.

OLE DB and ODBC support application role permissions and password encryption. OLE DB and ODBC are APIs that provide access to a range of data sources. OLE DB and ODBC both provide a relatively universal layer of code that uses the same core API to access different databases.

DBCC SHOW_STATISTICS command is used to see when was the last time the index rebuild. DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan. DBCC SHOW_STATISTICS displays the header, histogram, and density vector based on data stored in the statistics object.

Yes, the default is a type of constraint, although it does not really enforce anything. Default constraints apply a value to a column when an INSERT statement does not specify the value for the column. Although default constraints do not enforce a rule like the other constraints we have seen, they do provide the proper values to keep domain integrity in tact. A default can assign a constant value, the value of a system function, or NULL to a column. You can use a default on any column except IDENTITY columns and columns of type timestamp.

The msdb database is used by SQL Server Agent for scheduling alerts and jobs, and recording operators. The system databases that you must always back up include msdb, master, and model. If any database uses replication on the server instance, there is a distribution system database that you must also back up. Backups of these system databases let you restore and recover the SQL Server system in the event of system failure, such as the loss of a hard disk.

The above statement is false. The data accessible through a view is not stored in the database as a distinct object. A view can be thought of as either a virtual table or a stored query.

The above statement is true. Indexed views work best when the underlying data are infrequently updated. The maintenance of an indexed view can be greater than the cost of maintaining a table index. If the underlying data is updated frequently, the cost of maintaining the indexed view data may outweigh the performance benefits of using the indexed view. If the underlying data is updated periodically in batches but treated primarily as read-only between updates, consider dropping any indexed views before updating, and rebuilding them afterward. Doing this may improve performance of the updates.

Read Committed is the default isolation level for all SQL Server databases. The isolation level uses shared locking or row versioning to prevent dirty reads, depending on whether the READ_COMMITTED_SNAPSHOT database option is enabled.

Dirty reads occur when a transaction reads data that has been written by another transaction that has not yet been committed. Dirty reads are only possible at the Read Uncommitted isolation level; however, the Serializable isolation level is sufficient to prevent dirty reads. At the Oracle default level of Read Committed, dirty reads are not possible.

CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

Keyset-driven cursors are controlled by a set of unique identifiers, keys, known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened.

Pivot table is very powerful, and very easy to use. PIVOT clause enables you to turn rows into columns and present your data in a cross-tabular format.

Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

– In the above question, we are given that a column contains values from 0 to 256, so the correct answer is SMALLINT, because a SMALLINT data type uses only 2 bytes of data and can store values from -32,768 to 32,767.

– A TINYINT data type cannot be used because the maximum value it can hold is 0 to 255 and it has 1 byte storage capacity.

– Although an Integer data type can be used, SMALLINT will use only 2 bytes compared to Integer’s 4 bytes, VARHCAR (3) will use 3 3 bytes while DECIMAL.

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column. You can create an indexed view on a partitioned view.