Answer Q no 2

Answer Q no 2

Correct Answer:

INSERT dbo.mytable (col1, col2) VALUES (‘Test’, ‘Test2’), (‘Test3’, ‘Test4’), (‘test5’, ‘test6’);

You can use the Transact-SQL row constructor (also called a table value constructor) to specify multiple rows in a single INSERT statement. The row constructor consists of a single VALUES clause with multiple value lists enclosed in parentheses and separated by a comma. For more information, see Table Value Constructor (Transact-SQL).

Back to Question

Advertisements

Question no 2

Question no 2

In SQL Server 2008 R2, if I want to insert multiple rows in one statement, which format should I use?

1. INSERT dbo.mytable (col1, col2) VALUES (‘Test’, ‘Test2’), (‘Test3’, ‘Test4’), (‘test5’, ‘test6’);

2. INSERT dbo.mytable (col1, col2) SELECT (‘Test’, ‘Test2’), (‘Test3’, ‘Test4’), (‘test5’, ‘test6’);

3. INSERT dbo.mytable (col1, col2) VALUES ((‘Test’, ‘Test2’), (‘Test3’, ‘Test4’), (‘test5’, ‘test6’));

4. INSERT dbo.mytable (col1, col2) VALUES [(‘Test’, ‘Test2’), (‘Test3’, ‘Test4’), (‘test5’, ‘test6’)];

Go To The Answer

Answer Q no 1

Answer Q no 1

Correct Answer:
Enable backup compression, Create a user that is classified into a limited CPU workload group in Resource Governor.

Enable backup compression –> To reduce the disk space used for backup operations.

Create a user that is classified into a limited CPU workload group in Resource Governor –> minimize the impact to your production systems’ performance when performing backups

By default, backing up using compression significantly increases CPU usage, and the additional CPU consumed by the compression process can adversely impact concurrent operations.

Therefore, you might want to create a low-priority compressed backup in a session whose CPU usage is limited by Resource Governor when CPU contention occurs.

Back to Question

Question no 1

Question no 1

You would like to reduce the disk space used for backup operations on your SQL Server 2008 R2 databases. You also want to minimize the impact to your production systems’ performance when performing backups. Which two of the following items should you perform to meet these requirements? (select 2)

1. Enable backup compression

2. Add the WITH LOW_PRIORITY option to your backup command

3. Create a user that is classified into a limited CPU workload group in Resource Governor

4. Create a user that is classified into a limited I/O workload group in Resource Governor

Go To The Answer

SYSTEM DATABASES

Master DB

Records all the system-level information for an instance of SQL Server.
This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.
Therefore, SQL Server cannot start if the master database is unavailable.

msdb DB

It is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

model DB

Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

ResourceDB

Is a read-only database that contains system objects that are included with SQL Server. SQL Server system objects, such as sys.objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Tempdb DB

It is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Distribution

Database responsible for the replication metadata between the publisher and subscriber(s)

ReportServer

Primary database for Reporting Services to store the metadata and object definitions

  • Reports security
  • Job schedules and running jobs
  • Report notifications
  • Report execution history

ReportServer TempDB

Temporary storage for Reporting Services

  • Session information
  • Cache

SUBSCRIPTION MODEL

push subscription::

With a push subscription, the Publisher propagates changes to a Subscriber without a request from the Subscriber.

Changes can be pushed to Subscribers on demand, continuously, or on a scheduled basis.

The Distribution Agent or Merge Agent runs at the Distributor.

pull subscription::

With a pull subscription, the Subscriber requests changes made at the Publisher.

Pull subscriptions allow the user at the Subscriber to determine when the data changes are synchronized.

The Distribution Agent or the Merge Agent runs at the Subscriber.

TYPES OF REPLICATION

Snapshot publication

The Publisher sends a snapshot of the published data to Subscribers at scheduled intervals.

Transactional publication

The Publisher streams transactions to the Subscribers after they receive an initial snapshot of the published data.

Transactional publication with updatable subscriptions

The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.

Merge publication

The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications.