Blog

SQL - How to show distinct values in the most efficient way

A very common requirement is to show distinct values from a SQL Server table. The most efficient way to accomplish this is to build a view. This post walks through how to carry out this task.

The ability to show distinct values is a very common requirement. A big benefit of SQL server is that we can utilise views to perform this task. However, I still see cases where app builders don't take advantage of this valuable performance feature. To provide some guidance, this post walks through the steps to build and to use view from Power Apps.

Example use case scenario

To provide a practical use case scenario, let's suppose we capture property details into the following table.


'City' is a free text field and users can enter whatever they choose into this field.

From within our app, we want to display a list of distinct cities. The reason for this is to populate a search drop down, or to provide a list on which to build additional navigation functionality.

How NOT to do show distinct values

Before we go further, let's look at how not to do this. The pattern that I occasionally see involves formula that looks like this:


This technique pre-loads entire tables into local collections during the OnStart of an app. I've seen cases where app builders have loaded entire tables with 80k-100k records.

This highly inefficient technqiue can completely lock up SQL Server for the duration of the data retrieval process. Whilst pre-loading records is a valid pattern for data sources such as SharePoint, it is unnecessary with SQL Server.

Creating a SQL Server View

The most efficient way to return distinct values is to create a view. From Management Studio, we choose the option to add a new view, and we construct SQL to return the distinct values. For this example, the SQL would look like this:

CREATE VIEW [dbo].[vw_DistinctCities]
AS
SELECT DISTINCT City
FROM
[dbo].[Property]

Using the view from an app

From Power Apps, we can now add the view through the data panel and use it from our app.

To demonstrate our use case scenario, we can add a combo box control and set the Items property to our view.


To allow users to search for cities within the combobox, we specify a SearchFields value that includes 'City'. The benefit of using views and SQL server is that the combo box search is delegable. This means that if the source view returns more than 2,000 records, the results will be accurate.

To build the search feature, we can add a gallery control and display the records from the property table that match the selected item in the combo box. We set the Items property of the gallery control to formula that looks like this::

Filter('[dbo].[Property]', City=cboCities.Selected.City)


The user can now use the combobox to search for unique cities, and to show matching records in the gallery.

Conclusion

With SQL Server, the most efficient way to return distinct values is to create a view. In this post we walked through the process of how to build a view, and to use it from within an app.
Previous
Next
Related posts