070-461 試験問題を無料オンラインアクセス

You administer a database that includes a table named Customers that contains more than 750 rows. You create a new column named PartitionNumber of the int type in the table.
You need to assign a PartitionNumber for each record in the Customers table. You also need to ensure that the PartitionNumber satisfies the following conditions:
* Always starts with 1.
* Starts again from 1 after it reaches 100.
Which Transact-SQL statement should you use?

• A.CREATE SEQUENCE CustomerSequence AS int
  START WITH 0
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence
• B.CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence + 1 DROP SEQUENCE CustomerSequence
• C.CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 1
  MAXVALUE 100
  CYCLE
  UPDATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence
• D.CREATE SEQUENCE CustomerSequence AS int
  START WITH 1
  INCREMENT BY 1
  MINVALUE 0
  MAXVALUE 100
  CYCLE
  UPTATE Customers SET PartitionNumber = NEXT VALUE FOR CustomerSequence
  DROP SEQUENCE CustomerSequence

You use Microsoft SQL Server 2012 to develop a database that has two tables named Div1Cust and Div2Cust.
Each table has columns named DivisionID and CustomerId. None of the rows in Div1Cust exist in Div2Cust.
You need to write a query that meets the following requirements:
The rows in Div1Cust must be combined with the rows in Div2Cust.
The result set must have columns named Division and Customer.
Duplicates must be retained.
Which three Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

正解：

1 - SELECT DivisionID AS Division, CustomerlD AS Customer FROM Div1Cust
2 - UNION All
3 - SELECT DivisionID, CustomerlD FROM Div2Cust

You need to create a view named uv_CustomerFullNames. The view must prevent the underlying structure of the customer table from being changed.
Part of the correct T-SQL statement has been provided in the answer area. Provide the complete code.

• A.CREATE VIEW sales.uv_CustomerFullNames
  WITH SCHEMABINDING
  AS SELECT
  FirstName,
  LastName
  FROM Sales.Customers
• B.CREATE VIEW sales.uv_CustomerFullNames
  WITH SCHEMABINDING
  AS SELECT
  FirstName,
  FROM Sales.Customers

You are writing a set of queries against a FILESTREAM-enabled database.
You create a stored procedure that will update multiple tables within a transaction.
You need to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back.
Which Transact-SQL statement should you include at the beginning of the stored procedure?

• A.SET IMPLICIT_TRANSACTIONS OFF
• B.SET XACT_ABORT ON
• C.SET IMPLICIT_TRANSACTIONS ON
• D.SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• E.SET XACT_ABORT OFF
• F.SET TRANSACTION ISOLATION LEVEL SNAPSHOT

You are a database developer of a Microsoft SQL Server server that hosts a business-critical high-activity OLTP system.
The database contains a table named Customer that has more than one million records. The table has the following defintion:

Customer data is updates in an external system. These changes are copied into a staging table on a weekly basis. This table has the following definition.

You need to write a Transact-SQL statement that will update the records in the Customer table by using the values in the Customer_Staging table that has matching CustomerID values. You must ensure that only one record is updated at a time.
Which Transact-SQL query should you run?

• A.Option
  
• B.Option
  
• C.Option
  
• D.Option