You need to add a new column named Confirmed to the Employees table. The solution must meet the following requirements:
– Have a default value of TRUE.
– Minimize the amount of disk space used.
Which code segment should you use?
A. ALTER TABLE Employees
ADD Confirmed bit DEFAULT 0;
B. ALTER TABLE Employees
ADD Confirmed char(l) DEFAULT ‘1’;
C. ALTER TABLE Employees
ADD Confirmed bit DEFAULT 1;
D. ALTER TABLE Employees
ADD Confirmed char(l) DEFAULT ‘0’;
Case Study 3: Scenario 3 (Question 61 ~ Question 68)
You are a database administrator for a manufacturing company. You have an application that stores product data. The data will be converted to technical diagrams for the manufacturing process.
The product details are stored in XML format. Each XML must contain only one product that has a root element named Product. A schema named Production.ProductSchema has been created for the products xml.
You develop a Microsoft .NET Framework assembly named ProcessProducts.dll that will be used to convert the XML files to diagrams. The diagrams will be stored in the database as images. ProcessProducts.dll contains one class named ProcessProduct that has a method name of Convert(). ProcessProducts.dll was created by using a source code file named ProcessProduct.es. All of the files are located in C:\Products\. The application has several performance and security issues. You will create a new database named ProductsDB on a new server that has SQL Server 2012 installed. ProductsDB will support the application. The following graphic shows the planned tables for ProductsDB:
You will also add a sequence named Production.ProductID_Seq. You plan to create two certificates named DBCert and ProductsCert. You will create ProductsCert in master. You will create DBCert in ProductsDB. You have an application that executes dynamic T-SQL statements against ProductsDB. A sample of the queries generated by the application appears in Dynamic.sql.
You need to add a new column named Confirmed to the Attendees table. The solution must meet the following requirements:
– Have a default value of false.
– Minimize the amount of disk space used.
Which code block should you use?
A. Option A
B. Option B
C. Option C
D. Option D
You have a SQL Server 2012 database named DB1 that is accessed by 650 concurrent users.
You need to log all of the queries to DB1 that become deadlocked. The solution must minimize the impact on the server.
What should you create?
A. A SQL Server Agent job that retrieves information from the sys.dm_tran_active_transactions dynamic
B. A script that enables trace flags
C. A SQL Server Agent job that retrieves information from the sys.dm_tran_session_transactions dynamic
D. A SQL Server Profiler trace
You administer a Microsoft SQL Server 2012 database named Contoso on a server named Server01. You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server. What should you create?
A. an Extended Event session
B. a Policy
C. a Database Audit Specification
D. an Alert
E. a Server Audit Specification
F. a SQL Profiler Trace
G. a Resource Pool
You execute the following code.
You need to reduce the amount of time it takes to execute the query. What should you do?
A. change SUBSTRING (JobTitle, l, l) = ‘c’ to LEFT(JobTitle,1) = ‘c’.
B. Change SUBSTRING(JobTitle, l, l) = ‘c’ to JobTitle LIKE ‘c%\
C. Partition the table and use the JobTitle column for the partition scheme.
D. Replace IX_Employees with a clustered index.
You have a text file that contains an XML Schema Definition (XSD). You have a table named Schemal.Table1. You have a stored procedure named Schemal.Proc1 that accepts an XML parameter named Param1. You need to store validated XML data in Schemal.Table1. The solution must ensure that only valid XML data is accepted by Param1. What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
A. Define an XML column in Table1 by using an XML schema collection.
B. Create an XML schema collection in the database from the text file.
C. Declare Param1 var1 as type XML and associate the variable to the XML schema collection.
D. use the modify method to insert the XML schema into each row of the XML column in Table1.
http://msdn.microsoft.com/en-us/library/ms176009.aspx http://msdn.microsoft.com/en-us/library/hh403385.aspx http://msdn.microsoft.com/en-us/library/ms184277.aspx
Your company has a SQL Azure subscription. You implement a database named Database1. Database1 has two tables named Table1 and Table2. You create a stored procedure named sp1.Sp1 reads data from Table1 and inserts data into Table2. A user named User1 informs you that he is unable to run sp1. You verify that User1 has the SELECT permission on Table1 and Table2. You need to ensure that User1 can run sp1. The solution must minimize the number of permissions assigned to User1. What should you do?
A. Grant User1 the INSERT permission on Table2.
B. Add User1 to the db_datawriter role.
C. Change sp1 to run as the sa user.
D. Grant User1 the EXECUTE permission on sp1.