SQL Server MCQ, SQL Server Online Test Series 2, SQL Server quiz Series 2

Only one primary key constraints can be included or defined in a table definition.

TRUNCATE TABLE removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

The above statement is true. 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 the domain integrity intact. 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.

A stored procedure is used in the SQL Server with the help of the “Execute” or “Exec” Keyword. For example, if we want to execute the stored procedure “Getstudentname”, then we will use the following statement.

Execute Getstudentname 1
Exec Getstudentname 1

The members of setupadmin server role can manage linked servers (add or remove linked servers), manage replication, manage extended stored
procedures, and execute some system stored procedures, such as sp_serveroption. The setupadmin role lets users manage linked servers and alter server startup procedures. Members of this role can also configure replication and install extended stored procedures.

Data Transformation Services (DTS) is a set of graphical tools that allows you to transfer data between disparate sources into one or more destination. It is used to export the data from table to text file. It is a set of tools that allows you extract, transform, and consolidate data from disparate sources into single or multiple destinations. You can create custom data movement solution using DTS object model. DTS package also notifies if package steps succeed or failed by sending mails to source and destination.

The dtsrun utility is used to execute a package as an operating system command. The dtsrun tool is graphical interface used to run and schedule DTS package.

The above statement is true. The SQL Server Agent is a service that lets you configure scheduled tasks and system alerts. SQL Server Agent runs continuously in the background as a Windows Service.

from wikipedia:
A database schema is a way to logically group objects such as tables, views, stored procedures etc. Think of a schema as a container of objects. You can assign a user login permissions to a single schema so that the user can only access the objects they are authorized to access

Linked servers are configured to enable the Database Engine to execute a Transact-SQL statement that includes tables in another instance of SQL Server, or another database product such as Oracle.

SQL Server Integration Services (SSIS) is a component of the Microsoft SQL Server database software that can be used to perform a broad range of data migration tasks. SSIS is a platform for data integration and workflow applications. It features a fast and flexible data warehousing tool used for data extraction, transformation, and loading (ETL). The tool may also be used to automate maintenance of SQL Server databases and updates to multidimensional cube data.

SQL Server Profiler is an interface to create and manage traces and analyze and replay trace results. It enables you to monitor events
and checks if rows are being inserted properly.

Trace file name allows you to provide the full path to a template. TRACEFILE_IDENTIFIER specifies a custom identifier that becomes part of the Oracle Trace file name. Trace file continuity information is automatically added to both the old and new trace files to indicate that these trace files belong to the same process.

Compound assignment operator means an operator combined with another operator. The compound assignment operators that are supported in SQL Server 2008 are:
+=: Add and assign
-=: Subtract and assign
*=: Multiply and assign
/=: Divide and assign
%=: Modulus and assign
&=: Bitwise AND and assign
|=: Bitwise OR and assign
^=: Bitwise XOR and assign

There are two types of compression:
1. Row Level Compression
2. Page Level Compression

1. Row Level Compression: Row level compression is one of the type where there is no compression algorithm is used. The main aim of row level compression is to make fixed data types to a variable data type thereby reducing the size. This type of compression will remove any extra bytes in the fixed data type. One main consideration in this type of compression is that, data schema and data distribution directly plays an important role with respect to compression ratio.

2. Page Level Compression: Page level compression type can give more compression ratio compared to Row level. Page compression uses dictionary based compression algorithm to attain more compression ratio. The main aim of page level compression is to reduce column redundancy in one or more rows in a data page.

The above statement is true. A filtered index is an optimized non-clustered index especially suited to cover queries that select from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table. A well-designed filtered index can improve query performance as well as reduce index maintenance and storage costs compared with full-table indexes.

The SQL Server Resource Governor is a feature than you can use to manage SQL Server workload and system resource consumption. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use. Resource Governor enables you to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests.

Plan freezing refers to forcing a plan for a specific query. There are different reasons to freeze a plan, one is to provide plan stability for solving parameter sniffing issues. It allows the DBAs to persist plan guides which helps when a query fails. It monitors the features for checking when the query is succeeded or failed.

Plan freezing refers to forcing a plan for a specific query. There are different reasons to freeze a plan, one is to provide plan stability for solving parameter sniffing issues. It allows the DBAs to persist plan guides which helps when a query fails. It monitors the features for checking when the query is succeeded or failed. IntelliSense of SQL Server 2008 is ON by default. DML statements can also be used with IntelliSense.