Microsoft Developing Microsoft SQL Server Databases - 070-464무료 덤프문제 풀어보기
You plan to modify a procedure that contains hundreds of lines of code.
The modification must support the following guidelines:
* Use only tables that are not persistent in the database.
* Minimize the amount of time required to execute and recompile procedures.
You need to identify which type of table must be used to support the planned modification.
Which type of table should you identify?
The modification must support the following guidelines:
* Use only tables that are not persistent in the database.
* Minimize the amount of time required to execute and recompile procedures.
You need to identify which type of table must be used to support the planned modification.
Which type of table should you identify?
정답: D
An administrator provides a digital certificate named ServerCert.
You need to implement Transparent Data Encryption (TDE) on ProductsDB.
Which code segment should you use?
You need to implement Transparent Data Encryption (TDE) on ProductsDB.
Which code segment should you use?
정답: D
설명: (Fast2test 회원만 볼 수 있음)
You are testing disaster recovery procedures.
You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111.
Level 16, State 3, Line 1
Cannot find server certificate with thumbprint
,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9\
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore DB1 to a different server.
Which code segment should you execute?
You attempt to restore DB1 to a different server and you receive the following error message: "Msg 33111.
Level 16, State 3, Line 1
Cannot find server certificate with thumbprint
,0xA694FBEA88C9354E5E2567C30A2A69E8FB4C44A9\
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally."
You need to ensure that you can restore DB1 to a different server.
Which code segment should you execute?
정답: D
You have a SQL Azure database.
You execute the following script:
You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?
You execute the following script:
You add 1 million rows to Table1. Approximately 85 percent of all the rows have a null value for Column2.
You plan to deploy an application that will search Column2.
You need to create an index on Table1 to support the planned deployment. The solution must minimize the storage requirements.
Which code segment should you execute?
정답: A
설명: (Fast2test 회원만 볼 수 있음)
You need to identify which nonclustered indexes are unused by queries.
How should you complete the statement? To answer, drag the appropriate values to the correct locations. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
How should you complete the statement? To answer, drag the appropriate values to the correct locations. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
정답:
Explanation:
Box 1: sys.dm_db_index_usage_stats
sys.dm_db_index_usage_stats shows you how many times the index was used for user queries. It returns counts of different types of index operations and the time each type of operation was last performed in SQL Server.
Box 2: sys.indexes
sys.indexes contains a row per index or heap of a tabular object, such as a table, view, or table-valued function.
References: https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-index-usage-stats-transact-sql
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-indexes-transact-sql
You have a table named Table1 that contains 1 million rows. Table1 contains a column named Column1 that stores sensitive information. Column1 uses the nvarchar(16) data type.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column that uses two-way encryption.
Which code segment should you execute before you remove Column1?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
You have a certificate named Cert1.
You need to replace Column1 with a new encrypted column that uses two-way encryption.
Which code segment should you execute before you remove Column1?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
정답:
Explanation:
Note:
* Use AES_256 for two-way encryption.
* Use varbinary to store key.
* CLOSE SYMMETRIC KEY (Transact-SQL)
Closes a symmetric key, or closes all symmetric keys open in the current session.
* Example:
CREATE SYMMETRIC KEY CreditCards_Key11
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE Sales09;
GO
-- Create a column in which to store the encrypted data.
ALTER TABLE Sales.CreditCard
ADD CardNumber_Encrypted varbinary(128);
GO
-- Open the symmetric key with which to encrypt the data.
OPEN SYMMETRIC KEY CreditCards_Key11
DECRYPTION BY CERTIFICATE Sales09;
-- Encrypt the value in column CardNumber using the
-- symmetric key CreditCards_Key11.
-- Save the result in column CardNumber_Encrypted.
UPDATE Sales.CreditCard
SET CardNumber_Encrypted = EncryptByKey(Key_GUID('CreditCards_Key11')
, CardNumber, 1, HashBytes('SHA1', CONVERT( varbinary
, CreditCardID)));
GO
You have a SQL Azure database.
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?
You need to identify which keyword must be used to create a view that will be indexed.
Which keyword should you identify?
정답: C
설명: (Fast2test 회원만 볼 수 있음)
You have a SQL Server 2012 environment that contains two servers. The servers are configured as shown in the following table.
After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:
"Msg 916, Level 14, State 1, Line 1
The server principal "Account1" is not able to access the database "DB1" under the current security context." You verify that there is a server login for Account1 on Server2.
You need to ensure that Account1 can connect to DB1.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
After the failover is complete, a user receives the following error message when connecting to DB1 on Server2:
"Msg 916, Level 14, State 1, Line 1
The server principal "Account1" is not able to access the database "DB1" under the current security context." You verify that there is a server login for Account1 on Server2.
You need to ensure that Account1 can connect to DB1.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.
정답: C
You need to create a function that filters invoices by CustomerID. The SELECT statement for the function is contained in InvoicesByCustomer.sql.
Which code segment should you use to complete the function?
Which code segment should you use to complete the function?
정답: C
Your network contains a server named Server1 that runs SQL Server 2012. Server1 contains an instance named Instance1. Instance1 contains a database named ContentDatabase.
ContentDatabase uses transaction log backups.
The recovery model of ContentDatabase is set to FULL.
You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log.
Which three code segments should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
ContentDatabase uses transaction log backups.
The recovery model of ContentDatabase is set to FULL.
You need to shrink the ContentDatabase_Log log file to 10 MB. The solution must ensure that you can continue to back up the transaction log.
Which three code segments should you execute?
To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
정답:
Explanation:
Note:
* Shrinking a log file to a specified target size
The following example shrinks the log file in the AdventureWorks database to 1 MB. To allow the DBCC SHRINKFILE command to shrink the file, the file is first truncated by setting the database recovery model to SIMPLE.
Transact-SQL
USE AdventureWorks2012;
GO
-- Truncate the log by changing the database recovery model to SIMPLE.
ALTER DATABASE AdventureWorks2012
SET RECOVERY SIMPLE;
GO
-- Shrink the truncated log file to 1 MB.
DBCC SHRINKFILE (AdventureWorks2012_Log, 1);
GO
-- Reset the database recovery model.
ALTER DATABASE AdventureWorks2012
SET RECOVERY FULL;
GO
* If the log file does not shrink (after dbcc shrinkfile)
Typically it is the log file that appears not to shrink. This is usually the result of a log file that has notbeen truncated. You can truncate the log by setting the database recovery model to SIMPLE, or bybacking up the log and then running the DBCC SHRINKFILE operation again.
* DBCC SHRINKFILEShrinks the size of the specified data or log file for the current database, or empties a file by moving thedata from the specified file to other files in the same filegroup, allowing the file to be removed from thedatabase.
Arguments include:
target_size
Is the size for the file in megabytes, expressed as an integer.
You attempt to process an invoice by using usp_InsertInvoice.sql and you receive the following error message: "Msg 515, Level 16, State 2, Procedure usp_InsertInvoice, Line 10 Cannot insert the value NULL into column 'InvoiceDate', table 'DB1.Accounting.Invoices'; column does not allow nulls. INSERT fails." You need to modify usp_InsertInvoice.sql to resolve the error.
How should you modify the INSERT statement?
How should you modify the INSERT statement?
정답: A