You are a database developer of a Microsoft SQL Server 2012 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

• A.CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL
  PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);
• B.CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY
  CLUSTERED,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT
  NULL);
• C.CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT
  NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY
  KEY CLUSTERED(SourceID, CustomerID));
• D.CREATE TABLE Customer(SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY,CustomerName varchar(255) NOT NULL);

Comment: *

Name: *

Email: *

Verification: *

You work as a Database Administrator (DBA) at ABC.com. The infrastructure includes servers running Microsoft SQL Server 2012. All databases are hosted on a SAN (Storage Area Network).
You need to design a database solution for a new application.
You are tasked with designing a high-availability database solution.
The solution must include a single copy of the database to save disk space and the database must remain online in the event of a SQL Server failure. What should you include in your solution?

• A.You should include two servers and log shipping.
• B.You should include two servers configured as a failover cluster.
• C.You should include two servers and database mirroring.
• D.You should include two servers configure as a SQL Server Availability Group

Comment: *

Name: *

Email: *

Verification: *

Which of the following DBCC commands can you use to check the physical and logical integrity of an SQL Server 2012 database?

• A.CVU
• B.VERIFYINTEGRITY
• C.CHECKINTEGRITY
• D.CHECKDB

Comment: *

Name: *

Email: *

Verification: *

DRAG DROP
You administer a Microsoft SQL Server instance that contains a database of confidential data.
You need to encrypt the database files at the page level. You also need to encrypt the transaction log files.
Which four actions should you perform in sequence? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)

Correct Answer:

References:
http://msdn.microsoft.com/en-us/library/bb510663.aspx
http://msdn.microsoft.com/en-us/library/bb934049.aspx

Comment: *

Name: *

Email: *

Verification: *

DRAG DROP
Your company has a data warehouse that contains all of the sales data for your company. Several business applications read data from the data warehouse.
You plan to deploy an indexing strategy for a 2-TB table named FactSales contains a clustered index.
You need to recommend a solution for index maintenance that meets the following requirements:
Maintenance must be performed only when index fragmentation reaches 50 percent.
Maintenance must minimize the impact on the database reads from the business applications.
Which four actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Correct Answer:

Step 1: Partition the table
Step 2: Create a partition function
To migrate SQL Server partition definitions to SQL Data Warehouse simply:
Eliminate the SQL Server partition scheme.
Add the partition function definition to your CREATE TABLE.
Step 3: Create a partition scheme
Partitioning requires a partition function and a partition scheme.
Step 4: Rebuild the clustered index
Rebuild the clustered index onto new partition function/scheme.
References:
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-partition
https://blog.sqlrx.com/2017/02/24/sql-2016-partitioning-an-existing-table-with-a-columnstore-index/

Comment: *

Name: *

Email: *

Verification: *