Exam 70-762 topic 1 question 166 discussion

Actual exam question from Microsoft's 70-762

Question #: 166
Topic #: 1

DRAG DROP -
You are a database developer for a company that delivers produce and other refrigerated goods to grocery stores. You capture the food storage temperature for delivery vehicles by using Internet of Things (IoT) devices. You store the temperature data in a database table named VehicleTemperatures. The
ChillerSensorNumber column stores the identifier for the IoT devices.
You need to create an indexed view that meets the following requirements:
✑ Persists the data on disk to reduce the amount of I/O
✑ Provides the number of ChillerSensorNumber items
✑ Creates only a set of summary rows
How should you complete the view definition? To answer, drag the appropriate Transact-SQL segments to the correct locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bat between panes or scroll to view content.
Select and Place:

Show Suggested Answer

Suggested Answer:

Box1: WITH SCHEMABINDING -
Persists the data on disk to reduce the amount of I/O.
Box 2: COUNT_BIG(ChillerSensorNumber)
Provides the number of ChillerSensorNumber items
The function COUNT_BIG returns the number of items found in a group. COUNT_BIG operates like the COUNT function. These functions differ only in the data types of their return values. COUNT_BIG always returns a bigint data type value. COUNT always returns an int data type value.
Box 3: GROUP BY ChillerSensorNumber
Creates only a set of summary rows
References:
https://docs.microsoft.com/en-us/sql/t-sql/functions/count-big-transact-sql

by othman_ee at Jan. 26, 2020, 8:22 a.m.

Comments

Submit Cancel

othman_ee

Highly Voted 5 years, 6 months ago

Group BY recorderWhen, VehicleTemperatureId

upvoted 32 times

...

Nelly100

Highly Voted 5 years, 3 months ago

The correct Answer is GroupBY RecordedWhen, VehicleTemeparatureID. If you use groupby ChillerSensorNumber you get the following error Msg 8120, Level 16, State 1, Procedure IndexedViewVehicleTemperature, Line 5 [Batch Start Line 10] Column 'VehicleTemperatures.VehicleTemperaturesID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.I have tested this in SQL. I have created the temperature table and inserted the rows. If you want to try it see my code below: create table VehicleTemperatures ( VehicleTemperaturesID int , ChillerSensorNumber int , RecordedWhen datetime, Temperature decimal (16,4) ) create view IndexedViewVehicleTemperature WITH SCHEMABINDING as SELECT VehicleTemperaturesID, RecordedWhen, AVG(Temperature) AS VehichleTemperature, COUNT_BIG(ChillerSensorNumber) as ChillerSensorNumberCount from VehicleTemperatures group by RecordedWhen, VehicleTemperaturesID

upvoted 10 times

...

lauferr

Most Recent 5 years, 2 months ago

After reading it like 10 times i figured out we need indexed view and thats why with _ schemabinding

upvoted 5 times

...

RikimaruPR

5 years, 3 months ago

Why Count_Big and not Count, though?

upvoted 1 times

strikersree

5 years, 1 month ago

To create index on view, we need to use Schemabinding, COUNT_BIG(column_name).

upvoted 10 times

...

gog33

4 years, 8 months ago

If GROUP BY is present, the VIEW definition must contain COUNT_BIG(*) and must not contain HAVING

upvoted 2 times

...

JohnFan

5 years, 5 months ago

1. Count vs. Count_big. when using Enterprise Edition, SQL Server considers using the aggregates that are stored in your view for queries that look like the query, but doesn’t reference the view directly. Getting this to work depends on the needs being fairly limited. The limitations are pretty stiff. For example, a few common bits of coding syntax that are not allowed: COUNT(*) – Must use COUNT_BIG(*). 2. Group by. What is the Internet of Things? The Internet of Things, or IoT, refers to the billions of physical devices around the world that are now connected. The Internet of things (IoT) is a system of interrelated computing devices, mechanical and digital machines, objects, animals or people that are provided with unique identifiers (UIDs) and the ability to transfer data over a network without requiring human-to-human or human-to-computer interaction.

upvoted 3 times

...