Exam 70-761 All Questions

View all questions & answers for the 70-761 exam

Exam 70-761 topic 1 question 7 discussion

Actual exam question from Microsoft's 70-761

Question #: 7
Topic #: 1

Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section. You will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a database that tracks orders and deliveries for customers in North America. The database contains the following tables:

Sales.Customers -

Application.Cities -

Sales.CustomerCategories -

The company's development team is designing a customer directory application. The application must list customers by the area code of their phone number. The area code is defined as the first three characters of the phone number.
The main page of the application will be based on an indexed view that contains the area and phone number for all customers.
You need to return the area code from the PhoneNumber field.
Solution: You run the following Transact-SQL statement:

Does the solution meet the goal?

A. Yes
B. No

Show Suggested Answer

Suggested Answer: B 🗳️
The function should return nvarchar(10) and not a TABLE.
References:
https://sqlstudies.com/2014/08/06/schemabinding-what-why/

by avramov at Oct. 30, 2019, 6:47 a.m.

Comments

Submit Cancel

avramov

Highly Voted 5 years, 7 months ago

even if the function is defined to return nvarchar, the select top 1 col1, col2 still returns 2 columns and 1 row

upvoted 6 times

kiri2020

4 years, 8 months ago

why do we need a new function to display an area code in the view, should just add left(PhoneNumber,3) as AreaCode in that indexed view

upvoted 2 times

...

Barbedx

Highly Voted 5 years, 3 months ago

I think answer s A because there are no requirements for only one value.

upvoted 6 times

Aghie

4 years, 9 months ago

i agree. the requirement states "need to return the area code" not "only the area code". The area code will be included in the return table.

upvoted 1 times

...

Anirudh_net

Most Recent 3 years, 3 months ago

I think answer s A

upvoted 1 times

...

Billybob0604

4 years, 4 months ago

Vermonster is right. The answer to this question is A. Tested it. Additional info : CREATE FUNCTION AreaCode(@phoneNumber NVARCHAR(20)) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT TOP 1 @phoneNumber as PhoneNumber, VALUE as AreaCode FROM string_split(@phoneNumber,'-') ) GO drop table customer create table customer ( id INT primary key identity, phonenumber nvarchar(20) ) insert into customer values('123-256955'), ('511-956266') drop view dbo.PhoneWithAreaCode CREATE VIEW dbo.PhoneWithAreaCode with schemabinding AS SELECT AreaCode FROM dbo.Customer CROSS APPLY dbo.AreaCode(PhoneNumber) as AreaCode GO

upvoted 1 times

...

Vermonster

4 years, 5 months ago

The in-line Table-Valued Function works. This doesn't show how the function is being used but it does return a single row, 2 column table. This was tested to make sure: CREATE FUNCTION AreaCode(@phoneNumber NVARCHAR(20)) RETURNS TABLE WITH SCHEMABINDING AS RETURN (SELECT TOP 1 @phoneNumber as PhoneNumber, VALUE as AreaCode FROM string_split(@phoneNumber,'-') ) GO SELECT AreaCode FROM dbo.AreaCode(N'888-888-0000') Returns 888

upvoted 1 times

Vermonster

4 years, 5 months ago

And here is a view that uses it that meets the original question: CREATE VIEW dbo.PhoneWithAreaCode AS SELECT FirstName, LastName, PhoneNumber, AreaCode FROM dbo.Customer CROSS APPLY dbo.AreaCode(PhoneNumber) as AreaCode GO

upvoted 1 times

...

SimSql

4 years, 5 months ago

we have to think of nested table valued functions and figure out how to insert the table returned by split_string into the table that functions AreaCode is supposed to return.

upvoted 1 times

...

SimSql

4 years, 5 months ago

ANSWER IS B. Remember split_string function is a table valued function and so it returns a table.

upvoted 1 times

...

vramky

4 years, 5 months ago

The query should be : CREATE Function areacode (@PhoneNumber NVARCHAR(20)) RETURNS NVARCHAR(10) AS BEGIN DECLARE @areacode NVARCHAR(MAX) SELECT TOP 1 @areacode = VALUE FROM STRING_SPLIT(@phonenumer, '-') RETURN@areacode END

upvoted 1 times

...

cianissam

4 years, 8 months ago

Answer is B because string_split() function doesn't guarantee ordering of values returned

upvoted 3 times

...

TheDUdeu

4 years, 9 months ago

In this case the answer is B you should not return a table because it will return more than just the zip code and the questions wants you to return just the zip code.

upvoted 2 times

...

Ilray

4 years, 10 months ago

B, because you can not to return table in view, only scalar value.

upvoted 2 times

Aghie

4 years, 9 months ago

CREATE FUNCTION dbo.SAMPLE_FUNC1() RETURNS TABLE AS RETURN ( SELECT 1 AS ID, 'FNAME' AS FNAME, 'LNAME' AS LNAME, 30 AS AGE ) GO CREATE VIEW SAMPLE_VIEW AS SELECT * FROM SAMPLE_FUNC1() AS MYPROFILE GO SELECT * FROM SAMPLE_VIEW

upvoted 1 times

Aghie

4 years, 9 months ago

we can use table function inside view

upvoted 2 times

...

Joeyboats

4 years, 11 months ago

It says you need to return the area code. The table will return the area code and the next 3 digits then the last 4 in 3 different columns so the answer is no this soultion is not correct.

upvoted 1 times

...