QUESTION 41
You have a table named Customer.
You need to ensure that customer data in the table meets the following requirements:
credit limit must be zero unless customer identification has been verified.
credit limit must be less than 10,000.
Which constraint should you use?
A. CHECK (CreditLimt BETWEEN 1 AND 10000)
B. CHECK (Verified = 1 AND CreditLimt BETWEEN 1 AND 10000)
C. CHECK ((CreditLimt = 0 AND Verified = 0) OR (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
D. CHECK ((CreditLimt = 0 AND Verified = 0) AND (CreditLimt BETWEEN 1 AND 10000 AND Verified = 1))
Answer: C
QUESTION 42
You have been tasked to delete 1000 rows from a table named NewWidgets. There are 2000 rows in which the column ToBeDeleted set to 1.
You need to write a Transact-SQL batch that will delete exactly 1000 rows.
Which Transact-SQL batch should you use?
A. DELETE TOP (1000) dbo.NewWidgets
WHERE ToBeDeleted = 1;
B. DECLARE @BatchSize INT = 10;
WHILE (@BatchSize = 10)
DELETE TOP (@BatchSize) dbo.NewWidgets
WHERE ToBeDeleted = 1;
C. DELETE TOP ((SELECT COUNT(*) FROM dbo.NewWidgets
WHERE ToBeDeleted = 1)) w FROM dbo.NewWidgets w WHERE w.ToBeDeleted = 1;
D. DECLARE @TotalRowCount INT = 0; WHILE (@TotalRowCount <= 1000)
BEGIN
DELETE TOP (10) dbo.NewWidgets
WHERE ToBeDeleted = 1;
SET @TotalRowCount += @@ROWCOUNT;
END
Answer: A
QUESTION 43
You work for an international charity organization. You are writing a query to list the highest 100 different amounts that were donated. You have written the following code segment (Line numbers are included for reference only):
01 SELECT *
02 FROM (SELECT Customer.CustomerID, SUM(TotalDue) AS TotalGiven,
03 ………………..
04 FROM Customer
05 JOIN SalesOrder
06 ON Customer.CustomerID = SalesOrder.CustomerID
07 GROUP BY Customer.CustomerID) AS DonationsToFilter
08 WHERE FilterCriteria <= 100
You need to insert a Transact-SQL clause in line 03 to complete the query. Which Transact-SQL clause should you insert?
A. RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
B. NTILE(100) OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
C. ROW_NUMBER() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
D. DENSE_RANK() OVER (ORDER BY SUM(TotalDue) DESC) AS FilterCriteria
Answer: D
QUESTION 44
You have an XML schema that you must use to validate XML data in your database. You need to store this XML schema. Which code segment should you use?
A. CREATE SCHEMA CustomerSchema
B. CREATE DEFAULT CustomerSchema AS ‘XML’
C. CREATE PRIMARY XML INDEX CustomerSchema
D. CREATE XML SCHEMA COLLECTION CustomerSchema
Answer: D
QUESTION 45
You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales
WHERE CommentDate IS NOT NULL AND SalesPersonID IS NOT NULL;
You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the query. The index must conserve disk space while optimizing your query. Which index should you create?
A. CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE (CommentDate,SalesPersonID);
B. CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (SalesPersonID)
INCLUDE (CommentDate,CustomerID);
C. CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE(CommentDate)
WHERE SalesPersonID IS NOT NULL;
D. CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CommentDate, SalesPersonID)
INCLUDE(CustomerID)
WHERE CommentDate IS NOT NULL;
Answer: D
QUESTION 46
You have a user named John. He has SELECT access to the Sales schema. You need to eliminate John’s SELECT access rights from the Sales.SalesOrder table without affecting his other permissions.
Which Transact-SQL statement should you use?
A. DROP USER John;
B. DENY SELECT ON Sales.SalesOrder TO John;
C. GRANT DELETE ON Sales.SalesOrder TO John;
D. REVOKE SELECT ON Sales.SalesOrder FROM John;
Answer: B
QUESTION 47
You need to create a column that allows you to create a unique constraint.
Which two column definitions should you choose? (Each correct answer presents a complete solution. Choose two.)
A. nvarchar(100) NULL
B. nvarchar(max) NOT NULL
C. nvarchar(100) NOT NULL
D. nvarchar(100) SPARSE NULL
Answer: AC
QUESTION 48
You manage a SQL Server 2008 database that is located at your company’s corporate headquarters.
The database contains a table named dbo.Sales. You need to create different views of the dbo.Sales table that will be used by each region to insert, update, and delete rows. Each regional office must only be able to insert, update, and delete rows for their respective region.
Which view should you create for Region1?
A. CREATE VIEW dbo.Region1Sales
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1;
B. CREATE VIEW dbo.Region1Sales
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1 WITH CHECK OPTION;
C. CREATE VIEW dbo.Region1Sales
WITH SCHEMABINDING
AS SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales WHERE RegionID = 1;
D. CREATE VIEW dbo.Region1Sales
WITH VIEW_METADATA
AS
SELECT SalesID,OrderQty,SalespersonID,RegionID FROM dbo.Sales
WHERE RegionID = 1;
Answer: B
QUESTION 49
You administer a SQL Server 2008 database that contains a table name dbo.Sales, which contains the following table definition:
CREATE TABLE [dbo].[Sales](
[SalesID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SalesPersonID] [int] NULL,
[CommentDate] [date] NULL);
This table contains millions of orders. You run the following query to determine when sales persons comment in the dbo.Sales table:
SELECT SalesID,CustomerID,SalesPersonID,CommentDate FROM dbo.Sales
WHERE CommentDate IS NOT NULL AND SalesPersonID IS NOT NULL;
You discover that this query runs slow. After examining the data, you find only 1% of rows have comment dates and the SalesPersonID is null on 10% of the rows. You need to create an index to optimize the query. The index must conserve disk space while optimizing your query. Which index should you create?
A. CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE (CommentDate,SalesPersonID);
B. CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (SalesPersonID)
INCLUDE (CommentDate,CustomerID);
C. CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CustomerID)
INCLUDE(CommentDate)
WHERE SalesPersonID IS NOT NULL;
D. CREATE NONCLUSTERED INDEX idx1
ON dbo.Sales (CommentDate, SalesPersonID)
INCLUDE(CustomerID)
WHERE CommentDate IS NOT NULL;
Answer: D
QUESTION 50
Your database is 5GB and contains a table named SalesHistory. Sales information is frequently inserted and updated.
You discover that excessive page splitting is occurring.
You need to reduce the occurrence of page splitting in the SalesHistory table.
Which code segment should you use?.
A. ALTER DATABASE Sales MODIFY FILE
(NAME = Salesdat3, SIZE = 10GB);
B. ALTER INDEX ALL ON Sales.SalesHistory REBUILD WITH (FILLFACTOR = 60);
C. EXEC sys.sp_configure ‘fill factor (%)’, ’60’;
D. UPDATE STATISTICS Sales.SalesHistory(Products)
WITH FULLSCAN, NORECOMPUTE;
Answer: B
If you want to pass Microsoft 70-433 successfully, donot missing to read latest lead2pass Microsoft 70-433 dumps.
If you can master all lead2pass questions you will able to pass 100% guaranteed.