| Microsoft 70-433 Exam - Cheat-Test.com Free 70-433 Sample Questions: Q: 1 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. This morning you receive an e-mail from your company manager, in the e-mail, the manager asks you to create a table which is named dbo.Devices. Five rows have to be inserted into the dbo.Devices table. After this, DeviceID has to be returned for each of the rows. Of the following Transact-SQL batches, which one should be used?
A. CREATE TABLE dbo.Widgets ( WidgetID UNIQUEIDENTIFIER PRIMARY KEY, WidgetName
VARCHAR(25) );GOINSERT dbo.Widgets (WidgetName)VALUES
('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');SELECT SCOPE_IDENTITY();
B. CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName
VARCHAR(25) );GOINSERT dbo.Widgets (WidgetName)VALUES
('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');SELECT SCOPE_IDENTITY();
C. CREATE TABLE dbo.Widgets ( WidgetID UNIQUEIDENTIFIER PRIMARY KEY, WidgetName
VARCHAR(25));GOINSERT dbo.Widgets (WidgetName)OUTPUT inserted.WidgetID,
inserted.WidgetNameVALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
D. CREATE TABLE dbo.Widgets ( WidgetID INT IDENTITY PRIMARY KEY, WidgetName
VARCHAR(25));GOINSERT dbo.Widgets (WidgetName)OUTPUT inserted.WidgetID,
inserted.WidgetNameVALUES ('WidgetOne'),('WidgetTwo'),('WidgetThree'),('WidgetFour'),('WidgetFive');
Answer: D Q: 2 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. The SQL Server has identified many missing indexes. Now you have to build CREATE INDEX statements for all the missing indexes.
Which dynamic management view should be used?
A. sys.dm_db_index_usage_stats should be used
B. sys.dm_db_missing_index_group_stats should be used
C. sys.dm_db_missing_index_details should be used
D. sys.dm_db_missing_index_columns should be used
Answer: B Q: 3 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses
SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. Look at code segment below:
DECLARE @RangeStart INT = 0;
DECLARE @RangeEnd INT = 8000;
DECLARE @RangeStep INT = 1;
WITH NumberRange(ItemValue)
AS (SELECT ItemValue
FROM (SELECT @RangeStart AS ItemValue) AS t
UNION ALL
SELECT ItemValue + @RangeStep
FROM NumberRange
WHERE ItemValue < @RangeEnd)
SELECT ItemValue
FROM NumberRange
OPTION (MAXRECURSION 100)
Do you know the result of executing this code segment? Which result will be returned?
A. 101 rows will be returned with a maximum recursion error.
B. 10,001 rows will be returned with a maximum recursion error
C. 101 rows will be returned with no error
D. 10,001 rows will be returned with no error
Answer: A Q: 4 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses
SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. There is a table named dbo.Sellings in the database. The table contains the following table definition:
CREATE TABLE [dbo].[Selling](
[SellingID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
[OrderDate] [datetime] NOT NULL,
[CustomerID] [int] NOT NULL,
[SellingPersonID] [int] NULL,
[CommentDate] [date] NULL);
Since you notice that this query takes a long time to run, you start to examine the data. You find that only 2% of rows have comment dates and the SellingPersonID is null on 10% of the rows after the examination. So you have to improve the query performance. You have to create an index which must save disk space when optimize the query.
Of the following index, which one should you choose?
A. CREATE NONCLUSTERED INDEX idx2 ON dbo.Selling (CommentDate, SellingPersonID)
INCLUDE(CustomerID)WHERE CommentDate IS NOT NULL
B. CREATE NONCLUSTERED INDEX idx2ON dbo.Selling (CustomerID)INCLUDE
(CommentDate,SellingPersonID);
C. CREATE NONCLUSTERED INDEX idx2ON dbo.Selling (SellingPersonID)INCLUDE
(CommentDate,CustomerID);
D. CREATE NONCLUSTERED INDEX idx2ON dbo.Selling
(CustomerID)INCLUDE(CommentDate)WHERE SellingPersonID IS NOT NULL
Answer: A Q: 5 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database which is named DB1. There is a table named Bill in DB1. BillID is the primary key of the Bill table. By using the identity property, it is populated. The Bill table and the BillLineItem are related to each other. In order to increase load speed, all constraints are removed from the Bill table during a data load. But a row with BillId = 10 was removed from the database when you removed the constraints. Therefore you have to re-insert the row into the Bill table with the same BillId value. Of the following options, which Transact-SQL statement should be used?
A. INSERT INTO Bill(BillID, ...VALUES (10, ...
B. SET IDENTITY_INSERT BillON;INSERT INTO Bill(BillID, ...VALUES (10, ...SET IDENTITY_INSERT BillOFF;
C. ALTER TABLEBill;ALTER COLUMN BillID int;INSERT INTO Bill(BillID, ...VALUES (10, ...
D. ALTER DATABASE DB1SET SINGLE_USER;INSERT INTO Bill(BillID, ...VALUES (10, ...ALTER DATABASE DB1SET MULTI_USER;
Answer: B Q: 6 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. There are two tables in the company database. One table is named Subitems which includes subitems for shoes, hats and shirts.
Another one is named Commodities which includes commodities only from the Subitems shoes and hats.
Look at the following query:
SELECT s.Name, p.Name AS CommodityName
FROM Subitems s
OUTER APPLY
(SELECT *
FROM Commodities pr
WHERE pr.SubitemID = s.SubitemID) p
WHERE s.Name IS NOT NULL;
Now you have to foretell what results the query produces. So what is the answer?
A. Name CommodityName---------- --------------------Shoes Mountain Bike Shoes,Shoes Mountain Bike Shoes,Shoes Racing Shoes, MShoes Racing Shoes, LHats ClassicHat, SHats ClassicHat, MHats ClassicHat, LNULL Mountain Bike Shoes,NULL Mountain Bike Shoes,NULL
Racing Shoes, MNULL Racing Shoes, LNULL ClassicHat, SNULL
ClassicHat, MNULL ClassicHat, LShirts NULLNULL NULL
B. Name CommodityName---------- --------------------Shoes Mountain Bike Shoes,Shoes Mountain Bike Shoes,Shoes Racing Shoes, MShoes Racing Shoes, LHats ClassicHat, SHats ClassicHat, MHats ClassicHat, L
C. Name CommodityName---------- --------------------Shoes Mountain Bike Shoes,Shoes Mountain Bike Shoes,Shoes Racing Shoes, MShoes Racing Shoes, LHats ClassicHat, SHats ClassicHat, MHats ClassicHat, LShirts NULL
D. Name CommodityName---------- --------------------Shoes Mountain Bike Shoes,Shoes Mountain Bike Shoes,Shoes Racing Shoes, MShoes Racing Shoes, LHats ClassicHat, SHats ClassicHat, MHats ClassicHat, LShirts NULLNULL NULL
Answer: C Q: 7 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. There are two tables in the database of the company. The two tables are respectively named Sellings and SellingsHistory. Historical selling data is stored in the SellingsHistory table. On the Sellings table, you perform the configuration of Change Tracking. The minimum valid version of the Sellings table is 10. There is selling data that changed since version 10. According to the company requirement, a query has to be written to export only these data, including the primary key of deleted rows. Of the following methods, which one should be use?
A. FROM Sellings INNER JOIN CHANGETABLE (CHANGES Sellings, 10) AS C ...
B. FROM Sellings RIGHT JOIN CHANGETABLE (CHANGES Sellings, 10) AS C ...
C. FROM Sellings RIGHT JOIN CHANGETABLE (CHANGES SellingsHistory, 10) AS C ...
D. FROM Sellings INNER JOIN CHANGETABLE (CHANGES SellingsHistory, 10) AS C ...
Answer: B Q: 8 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. There are two tables in the database of the company. The two tables are respectively named Clients and Bills. Now you get an e-mail from your company manager, you've been assigned a task that you have to write a SELECT statement. The statement should output client and bill data as a valid and well-formed XML document. You have to mix attribute and element based XML within the document. But you think that it is not proper to use the FOR XML AUTO clause. You have to find the suitable FOR XML clause.
Of the following FOR XML statement, which one should be used? (choose more than one)
A. FOR XML PATH should be used
B. FOR BROWSE should be used
C. FOR XML EXPLICIT should be used
D. FOR XML RAW should be used
Answer: A, C Q: 9 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. There's a table named Clients in the database. The Clients table contains an XML column which is named ClientInfo. At present the Client table contains no indexes. Look at the WHERE clause below:
WHERE ClientInfo.exist ('/ClientDemographic/@Age[.>="21"]') = 1
You use this clause in a query for which indexes have to be created. Of the following Transact-SQL
statements, which one should be used?
A. CREATE PRIMARY XML INDEX PXML_IDX_ClientON Clients(ClientInfo);CREATE XML INDEX
SXML_IDX_Client ON Client(ClientInfo)USING XML INDEX PXML_IDX_ClientFOR VALUE;
B. CREATE PRIMARY XML INDEX PXML_IDX_ClientON Clients(ClientInfo);CREATE XML INDEX
SXML_IDX_Client ON Client(ClientInfo)USING XML INDEX PXML_IDX_ClientFOR PATH;
C. CREATE CLUSTERED INDEX CL_IDX_Client ON Clients(ClientID);CREATE PRIMARY XML
INDEX PXML_IDX_ClientON Clients(ClientInfo);CREATE XML INDEX SXML_IDX_Client_Property ON
Client(ClientInfo)USING XML INDEX PXML_IDX_ClientFOR VALUE;
D. CREATE CLUSTERED INDEX CL_IDX_Client ON Clients(ClientID);CREATE PRIMARY XML
INDEX PXML_IDX_ClientON Clients(ClientInfo);CREATE XML INDEX SXML_IDX_Client ON
Client(ClientInfo)USING XML INDEX PXML_IDX_ClientFOR PATH;
Answer: D Q: 10 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. There are two tables in the company database. The two tables are respectively named Bill and BillData. Bill information is stored in the two tables. The Bill table relates to the BillData table through the BillID column of each table. In the Bill table there is a column which is named LatestModifiedDate. If the related bill in the BillData table is modified, you must make sure that the LatestModifiedDate column must reflect the data and time of the modification. So you have to create a trigger. Of the following Transact-SQL statement, which one should be used?
A. CREATE TRIGGER [uModDate] ON [Bill]AFTER UPDATE FOR REPLICATION AS UPDATE [Bill]
SET [LatestModifiedDate] = GETDATE() FROM inserted WHERE inserted.[BillID] = [Bill].[BillID]
B. CREATE TRIGGER [uModDate] ON [BillDetails]INSTEAD OF UPDATE FOR REPLICATIONAS
UPDATE [Bill] SET [LatestModifiedDate] = GETDATE() FROM inserted WHERE inserted.[BillID] = [Bill].[BillID];
C. CREATE TRIGGER [uModDate] ON [BillDetails] AFTER UPDATE NOT FOR REPLICATION AS
UPDATE [Bill] SET [LatestModifiedDate] = GETDATE() FROM inserted WHERE inserted.[BillID] = [Bill].[BillID];
D. CREATE TRIGGER [uModDate] ON [Bill]INSTEAD OF UPDATE NOT FOR REPLICATIONAS
UPDATE [Bill] SET [LatestModifiedDate] = GETDATE() FROM inserted WHERE inserted.[BillID] = [Bill].[BillID];
Answer: C Q: 11 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. There's a table which is named Essays.
The Essays table contains two columns respectively named EssayHead and Detail. The two columns all contain a full-text index. The word "technology" may be in column EssayHead or in column Detail. You have to return row from the Essay table. Of the following code segments, which one should be used?
A. SELECT * FROM Books WHERE FREETEXT(BookTitle,'computer')
B. SELECT * FROM Books WHERE FREETEXT(*,'computer')
C. SELECT * FROM Books WHERE BookTitle LIKE '%computer%'
D. SELECT * FROM Books WHERE BookTitle = '%computer%' OR Description = '%computer%'
Answer: B Q: 12 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. Look at the following query.
SELECT AddressId,
AddressLine1,
City,
PostalCode
FROM Person.Address
WHERE City = @city_name
AND PostalCode = @postal_code
You notice that for a particular set of parameter values, sometimes this query has an unstable performance, sometimes it runs quickly while sometimes it executes slowly.
You also notice that in the Address table, 92 percent of the rows contain the same value for the city.
You have to improve the query performance. For the particular set of parameter values, you have to identify a query hint which will optimize the query.
Which query hint should be used?
A. OPTIMIZE FOR should be used
B. FAST should be used
C. PARAMETERIZATION FORCED should be used
D. MAXDOP should be used
Answer: A Q: 13 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. Now you get an order from the company manger. The company manager assigns a task to you that you have to perform the configuration on the Service Broker, making it process messages within a single database. You have completed three steps: CREATE MESSAGE TYPE; CREATE CONTRACT; CREATE QUEUE. After the above three steps, you have to complete the confifuration. So what is the next step?
A. CREATE ROUTE is the next step.
B. CREATE SERVICE is the next step
C. CREATE ENDPOINT is the next step
D. CREATE BROKER PRIORITY is the next step.
Answer: B Q: 14 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. You manage a SQL Server 2008 database of the company. Now you get an e-mail from your company manager, in the e-mail, you have been assigned a task. You have to send e-mail from a stored procedure. But when you start to perform this, you notice that that a MAPI client has not been installed. In the following options, which system stored procedure should be used?
A. sysmail_start_sp should be used.
B. xp_sendmail should be used
C. xp_startmail should be used.
D. sp_send_dbmail should be used
Answer: D Q: 15 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. Now you get an email from your company, in the email, you're assigned a task. You have to configure Full-Text Search, making it ingnore specific words. So of the following Full-Text Search components, which one should be used?
A. iFilter should be used
B. Thesaurus file should be used
C. Word breakers should be used.
D. Stoplist should be used.
Answer: D Q: 16 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. There is a table which is named Item.
Look at the following location:
PS
SQLSERVER:\SQL\CONTOSO\DEFAULT\Databases\ReportServer\Tables\dbo.Inventory\Columns>
At this location, you use the SQL Server Windows PowerShell provider to open a Microsoft Windows PowerShell session. You have to query all the columns in Item table using the e SQL Server Windows PowerShell provider. Of the following options, which cmdlet should be used?
A. Get-ChildItem should be used
B. Get-ItemProperty should be used
C. Get-Item should be used
D. Get-Location should be used
Answer: A Q: 17 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. Enterprise Edition and all the company data is stored in the SQL Server 2008 database. There's a table named Modifications. The data is the table is frequently modified. According to the company requirement, you have to maintain a history of all data modifications, the type of modification and the values modified also have to be kept. Of the following tracking methods, which one should you use?
A. C2 Audit Tracing
B. Change Data Capture
C. Database Audit
D. Change Trackin
Answer: B Q: 18 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses
SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. You insert the WorkerID of each Worker's manager in the ReportsTo column to document your company's organizational hierarchy.
You have to write a recursive query. The query should produce a list of Workers and their manager and include the Worker's level in the hierarchy.
You write the following code segment. (Line numbers are used for reference only.)
1 WITH WorkerList (WorkerID, FullName, ManagerName, Level)
2 AS (
3
4 )
5 SELECT WorkerID, FullName, ManagerName, Level
6 FROM WorkerList;
At line 3, which code segment should you insert?
A. SELECT WorkerID, FullName, '' AS [ReportsTo], 1 AS [Level] FROM Worker WHERE ReportsTo IS
NULL UNION ALL SELECT emp.WorkerID, emp.FullNName
mgr.FullName, 1 + 1 AS [Level] FROM Worker emp JOIN Worker mgr ON emp.ReportsTo = mgr.WorkerID
B. SELECT WorkerID, FullName, '' AS [ReportsTo], 1 AS [Level] FROM Worker WHERE ReportsTo IS
NULL UNION ALL SELECT emp.WorkerID, emp.FullName,
mgr.FullName, mgr.Level + 1 FROM WorkerList mgr JOIN Worker emp ON emp.ReportsTo = mgr.WorkerId
C. SELECT WorkerID, FullName, '' AS [Reports To], 1 AS [Level] FROM Worker UNION ALL SELECT
emp.WorkerID, emp.FullName, mgr.FullName, 1 + 1 AS
[Level] FROM Worker emp LEFT JOIN Worker mgr ON emp.ReportsTo = mgr.WorkerID
D. SELECT WorkerID, FullName, '' AS [ReportsTo], 1 AS [Level] FROM Worker UNION ALL SELECT
emp.WorkerID, emp.FullName, mgr.FullName, mgr.Level + 1
FROM WorkerList mgr JOIN Worker emp ON emp.ReportsTo = mgr.WorkerID
Answer: B Q: 19 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. Sales information for your company orders is stored in this database. According to the company requirement, a common table expression (CTE) has to be implemented. In the options below, which code segment should be used?
A. SELECT Year, Region, Total FROM (SELECT Year, Region, SUM(OrderTotal) AS Total FROM Orders
GROUP BY Year, Region) AS [SalesByYear];
B. SELECT DISTINCT Year, Region, (SELECT SUM(OrderTotal) FROM Orders SalesByYear WHERE
Orders.Year = SalesByYear.YEAR
AND Orders.Region = SalesByYear.Region) AS [Total] FROM Orders;
C. CREATE VIEW SalesByYear AS SELECT Year, Region, SUM(OrderTotal) FROM Orders GROUP BY
Year, Region; GO SELECT Year, Region, Total FROM
SalesByYear;
D. WITH SalesByYear(Year,Region,Total) AS (SELECT Year, Region, SUM(OrderTotal) FROM Orders
GROUP BY Year,Region) SELECT Year, Region, Total FROM
SalesByYear;
Answer: D Q: 20 You are a database developer and you have many years experience in database development. Now you are employed in a company which is named Loxgo. The company uses SQL Server 2008 and all the company data is stored in the SQL Server 2008 database. Sales information for your company orders is stored in this database. According to the requirement for marketing analysis, your company wants you to identify the orders with the highest average unit price and an order total greater than 8,000. There should be less than 30 orders in the list. Of the follow queries, which one should you use?
A. SELECT TOP (30) o.SalesOrderId, o.OrderDate, o.Total, SUM(od.Qty * od.UnitPrice) / SUM(od.Qty) AS
[AvgUnitPrice]FROM Sales.SalesOrderHeader o JOIN Sales.SalesOrderDetail
od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 8000 GROUP BY o.SalesOrderId, o.OrderDate,
o.Total ORDER BY Total DESC;
B. SELECT TOP (30) o.SalesOrderId, o.OrderDate, o.Total, (SELECT SUM(od.Qty * od.UnitPrice) /
SUM(od.Qty) FROM Sales.SalesOrderDetail od WHERE o.SalesOrderId =
od.SalesOrderId) AS [AvgUnitPrice]FROM Sales.SalesOrderHeader oWHERE o.Total > 8000 ORDER BY
o.Total DESC, AvgUnitPrice;
C. SELECT TOP (30) o.SalesOrderId, o.OrderDate, o.Total, SUM(od.QTY * od.UnitPrice) / SUM(od.Qty)
AS [AvgUnitPrice]FROM Sales.SalesOrderHeader o JOIN
SALES.SalesOrderDetail od ON o.SalesOrderId = od.SalesOrderId WHERE o.Total> 8000 GROUP BY
o.SalesOrderId, o.OrderDate, o.Total ORDER BY AvgUnitPrice;
D. SELECT TOP (30) o.SalesOrderId, o.OrderDate, o.Total, (SELECT SUM(od.Qty * od.UnitPrice) /
SUM(od.QTY) FROM Sales.SalesOrderDetail od WHERE o.SalesOrderId =
od.SalesOrderId) AS [AvgUnitPrice]FROM Sales.SalesOrderHeader o WHERE o.Total> 8000 ORDER BY
AvgUnitPrice DESC;
Answer: D |
