Question: 1You are a database developer and you have many years experience in database development.Now you are employed in a company which is named Logo. The company uses SQL Server 2008and all the company data is stored in the SQL Server 2008 database. This morning you receivean e-mail from your company manager, in the e-mail, the manager asks you to create a tablewhich is named dbo.Devices. Five rows have to be inserted into the dbo.Devices table. After this,Devised has to be returned for each of the rows. Of the following Transact-SQL batches, whichone should be used?

A. CREATE TABLE dbo.Widgets ( Fidgeted UNIQUEIDENTIFIER PRIMARY KEY, Widget Name VARCHAR(25) );GOINSERT dbo.Widgets (Widget Name)VALUE (‘Widget One’),(‘Widget Two’),(‘Widget Three’),(‘Widget Four’),(‘Widget Five’);SELECT SCOPE_IDENTITY();B. CREATE TABLE dbo.Widgets ( Fidgeted INT IDENTITY PRIMARY KEY, Widget Name VARCHAR(25) );GOINSERT dbo.Widgets (Widget Name)VALUES (‘Widget One’),(‘Widget Two’),(‘Widget Three’),(‘Widget Four’),(‘Widget Five’);SELECT SCOPE_IDENTITY();C. CREATE TABLE dbo.Widgets ( Widget UNIQUEIDENTIFIER PRIMARY KEY, Widget Name VARCHAR(25));GOINSERT dbo.Widgets (Widget Name)OUTPUT inserted.WidgetID, inserted.WidgetNameVALUES (‘Widget One’),(‘Widget Two’),(‘Widget Three’),(‘Widget Four’),(‘Widget Five’);D. CREATE TABLE dbo.Widgets ( Widget INT IDENTITY PRIMARY KEY, Widget Name VARCHAR(25));GOINSERT dbo.Widgets (Widget Name)OUTPUT inserted.WidgetID, inserted.WidgetNameVALUES (‘Widget One’),(‘Widget Two’),(‘Widget Three’),(‘Widget Four’),(‘Widget Five’);

Answer: D

Question: 2You 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 Server2008 and all the company data is stored in the SQL Server 2008 database. The SQL Server hasidentified many missing indexes. Now you have to build CREATE INDEX statements for all themissing indexes. Which dynamic management view should be used?

A. sys.dm_db_index_usage_stats should be usedB. sys.dm_db_missing_index_group_stats should be usedC. sys.dm_db_missing_index_details should be usedD. sys.dm_db_missing_index_columns should be used

Answer: B

Question: 3You 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 Server2008 and all the company data is stored in the SQL Server 2008 database. Look at codesegment below:

DECLARE @Range Start INT = 0;DECLARE @Range End INT = 8000;DECLARE @Range Step INT = 1;WITH Number Range(Item Value)AS (SELECT Item ValueFROM (SELECT @Range Start AS Item Value) AS tUNION ALLSELECT Item Value + @Range StepFROM Number Range

Page 1 of 91Exam Name: TS: Microsoft SQL Server 2008, Database DevelopmentExam Type: MicrosoftExam Code: 70-433 Total Questions 199

WHERE Item Value < @Range End)SELECT Item ValueFROM Number RangeOPTION (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 errorC. 101 rows will be returned with no errorD. 10,001 rows will be returned with no error

Answer: A

Question: 4You 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 Server2008 and all the company data is stored in the SQL Server 2008 database. There is a tablenamed dbo.Sellings in the database. The table contains the following table definition:

CREATE TABLE [dbo].[Selling]([Selling ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,[Order Date] [date time] NOT NULL,[Customer] [int] NOT NULL,[SellingPersonID] [int] NULL,[Comment Date] [date] NULL);

Since you notice that this query takes a long time to run, you start to examine the data. You findthat only 2% of rows have comment dates and the SellingPersonID is null on 10% of the rowsafter the examination. So you have to improve the query performance. You have to create anindex which must save disk space when optimize the query. Of the following index, which oneshould you choose?

A. CREATE NONCLUSTERED INDEX idx2 ON dbo.Selling (Comment Date, SellingPersonID) INCLUDE(Customer)WHERE Comment Date IS NOT NULLB. 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 (Customer)INCLUDE(Comment Date)WHERE SellingPersonID IS NOT NULL

Answer: A

Question: 5You are a database developer and you have many years experience in database development.Now you are employed in a company which is named Lox go. The company uses SQL Server2008 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 identityproperty, it is populated. The Bill table and the BillLineItem are related to each other. In order toincrease load speed, all constraints are removed from the Bill table during a data load. But a rowwith BillId = 10 was removed from the database when you removed the constraints. Thereforeyou 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?

Page 2 of 91Exam Name: TS: Microsoft SQL Server 2008, Database DevelopmentExam Type: MicrosoftExam Code: 70-433 Total Questions 199

A. INSERT INTO Bill(BillID, …VALUES (10, …B. SET IDENTITY_INSERT Billon; INSERT INTO Bill(BillID, …VALUES (10, …SET IDENTITY_INSERT Bill OFF;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

Question: 6You are a database developer and you have many years experience in database development.Now you are employed in a company which is named Lox go. The company uses SQL Server2008 and all the company data is stored in the SQL Server 2008 database. There are two tablesin the company database. One table is named Sub items which includes sub items for shoes,hats and shirts. Another one is named Commodities which includes commodities only from theSub items shoes and hats.Look at the following query:

SELECT s.Name, p.Name AS Commodity NameFROM Sub items sOUTER APPLY(SELECT *FROM Commodities prWHERE pr.SubitemID = s.SubitemID) pWHERE s.Name IS NOT NULL;

Now you have to foretell what results the query produces. So what is the answer?

A. Name Commodity Name———- ——————–Shoes Mountain Bike Shoes,Shoes Mountain Bike Shoes, Shoes Racing Shoes, MShoes Racing Shoes, LHats Classic Hat, SHats Classic Hat, MHats Classic Hat, LNULL Mountain Bike Shoes, NULL Mountain Bike Shoes, NULL Racing Shoes, MNULL Racing Shoes, LNULL Classic Hat, SNULL Classic Hat, MNULL Classic Hat, LShirts NULLNULL NULLB. Name Commodity Name———- ——————–Shoes Mountain Bike Shoes,Shoes Mountain Bike Shoes,Shoes Racing Shoes, MShoes Racing Shoes, LHats Classic Hat, SHats Classic Hat, MHats Classic Hat, LC. Name Commodity Name———- ——————–Shoes Mountain Bike Shoes,Shoes Mountain Bike Shoes,Shoes Racing Shoes, MShoes Racing Shoes, LHats Classic Hat, SHats Classic Hat, MHats Classic Hat, LShirts NULLD. Name Commodity Name———- ——————–Shoes Mountain Bike Shoes,Shoes Mountain Bike Shoes,Shoes Racing Shoes, MShoes Racing Shoes, LHats Classic Hat, SHats ClassicHat, MHats Classic Hat, Shirts NULLNULL NULL

Answer: C

Question: 7You 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 Server2008 and all the company data is stored in the SQL Server 2008 database. There are two tablesin the database of the company. The two tables are respectively named Selling• s and SellingHistory. Historical selling data is stored in the Selling History table. On the Selling• s table, youperform the configuration of Change Tracking. The minimum valid version of the Selling• s table is10. There is selling data that changed since version 10. According to the company requirement, aquery has to be written to export only these data, including the primary key of deleted rows. Ofthe following methods, which one should be use?

Page 3 of 91Exam Name: TS: Microsoft SQL Server 2008, Database DevelopmentExam Type: MicrosoftExam Code: 70-433 Total Questions 199

A. FROM Selling• s INNER JOIN CHANGETABLE (CHANGES Selling• s, 10) AS C …B. FROM Selling• s RIGHT JOIN CHANGETABLE (CHANGES Selling• s, 10) AS C …C. FROM Selling• s RIGHT JOIN CHANGETABLE (CHANGES Selling History, 10) AS C …D. FROM Selling• s INNER JOIN CHANGETABLE (CHANGES Selling History, 10) AS C …

Answer: B

Question: 8You 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 Server2008 and all the company data is stored in the SQL Server 2008 database. There are two tablesin the database of the company. The two tables are respectively named Clients and Bills. Nowyou get an e-mail from your company manager, you’ve been assigned a task that you have towrite 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 suitableFOR XML clause. Of the following FOR XML statement, which one should be used? (choosemore than one)

A. FOR XML PATH should be usedB. FOR BROWSE should be usedC. FOR XML EXPLICIT should be usedD. FOR XML RAW should be used

Answer: A, C

Question: 9You are a database developer and you have many years experience in database development.Now you are employed in a company which is named Lox go. The company uses SQL Server2008 and all the company data is stored in the SQL Server 2008 database. There’s a tablenamed 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: WHEREClientInfo.exist (‘/Client Demographic/@Age[.>="21"]‘) = 1 You use this clause in a query forwhich indexes have to be created. Of the following Transact-SQL statements, which one shouldbe used?

A. CREATE PRIMARY XML INDEX PXML_IDX_ClientON Clients(Client Info);CREATE XML INDEX SXML_IDX_Client ON Client(Client Info)USING XML INDEX PXML_IDX_ClientFOR VALUE;B. CREATE PRIMARY XML INDEX PXML_IDX_ClientON Clients(Client Info);CREATE XML INDEX SXML_IDX_Client ON Client(Client Info)USING XML INDEX PXML_IDX_ClientFOR PATH;C. CREATE CLUSTERED INDEX CL_IDX_Client ON Clients(Cliental);CREATE PRIMARY XML INDEX PXML_IDX_ClientON Clients(Client Info);CREATE XML INDEX SXML_IDX_Client_Property ON Client(Client Info)USING XML INDEX PXML_IDX_ClientFOR VALUE;D. CREATE CLUSTERED INDEX CL_IDX_Client ON Clients(Client);CREATE PRIMARY XML INDEX PXML_IDX_ClientON Clients(Client Info);CREATE XML INDEX SXML_IDX_Client ON Client(Client Info)USING XML INDEX PXML_IDX_ClientFOR PATH;

Answer: D

Page 4 of 91Exam Name: TS: Microsoft SQL Server 2008, Database DevelopmentExam Type: MicrosoftExam Code: 70-433 Total Questions 199

Question: 10You are a database developer and you have many years experience in database development.Now you are employed in a company which is named Lox go. The company uses SQL Server2008 and all the company data is stored in the SQL Server 2008 database. There are two tablesin the company database. The two tables are respectively named Bill and Bill Data. Billinformation is stored in the two tables. The Bill table relates to the Bill Data table through theBillID column of each table. In the Bill table there is a column which is named LatestModifiedDate.If the related bill in the Bill Data table is modified, you must make sure that theLatestModifiedDate column must reflect the data and time of the modification. So you have tocreate a trigger. Of the following Transact-SQL statement, which one should be used?

A. CREATE TRIGGER [emendate] ON [Bill]AFTER UPDATE FOR REPLICATION AS UPDATE [Bill] SET [LatestModifiedDate] = GETDATE() FROM inserted WHERE inserted.[BillID] = [Bill].[BillID]B. CREATE TRIGGER [uModDate] ON [Bill Details]INSTEAD OF UPDATE FOR REPLICATIONAS UPDATE [Bill] SET [LatestModifiedDate] = GETDATE() FROM inserted WHERE inserted.[BillID] = [Bill].[BillID];C. CREATE TRIGGER [uModDate] ON [Bill Details] 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

Question: 11You are a database developer and you have many years experience in database development.Now you are employed in a company which is named Lox go. The company uses SQL Server2008 and all the company data is stored in the SQL Server 2008 database. There’s a table whichis named Essays. The Essays table contains two columns respectively named Essay Head andDetail. The two columns all contain a full-text index. The word “technology” may be in columnEssay Head or in column Detail. You have to return row from the Essay table. Of the followingcode 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 Book Title LIKE ‘%computer%’D. SELECT * FROM Books WHERE Book Title = ‘%computer%’ OR Description = ‘%computer%’

Answer: B

Question: 12You are a database developer and you have many years experience in database development.Now you are employed in a company which is named Lox go. The company uses SQL Server2008 and all the company data is stored in the SQL Server 2008 database. Look at the followingquery.

SELECT Addressed,AddressLine1,City,Postal CodeFROM Person. AddressWHERE City = @city nameAND Postal Code = @postal code

Page 5 of 91