With the growing popularity of Power Apps, maintaining clarity and consistency in database object naming conventions has become increasingly crucial. This not only ensures better performance but also avoids unexpected errors, particularly when integrating with platforms like Power Apps. This blog delves into the significance of appropriate naming conventions in SQL Server, highlighting issues that arise from inappropriate naming practices and their impact on Power Apps.
Understanding the Importance of Naming Conventions
Naming conventions aren’t just about clarity; they’re about minimizing unexpected errors. Especially in tools like Power Apps, certain names can trigger bugs, making it vital to adhere to clear and precise conventions.
A prudent approach to circumvent such issues is to adopt a consistent naming convention. This discussion provides insights on how to name SQL Server objects effectively and highlights a specific Power Apps bug triggered when a column starts with a number.
SQL Server Database Identifiers
SQL Server offers flexibility in defining a plethora of objects including databases, tables, views, columns, indexes, stored procedures, and more. These object names are termed as identifiers and are primarily of two types:
- Regular Identifiers
- Delimited Identifiers
The key difference between them is that Delimited Identifiers can encompass spaces or special characters, necessitating the use of square brackets for delimitation.
Though Delimited Identifiers are valid, it’s generally safer to use naming conventions suited for Regular Identifiers. Some of the rules include:
- Starting the name with a letter (A-Z or a-z)
- Avoiding the use of numbers at the beginning
- Excluding spaces
- Ensuring the name doesn’t overlap with a SQL keyword
As an added precaution, avoid naming objects that coincide with function or other keywords within Power Apps. For a more comprehensive understanding of regular identifiers, consider reviewing the official Microsoft SQL Server documentation.
Delving into Delimited Identifiers
Let’s explore an example based on a SQL Server table employing various Delimited Identifiers. Notably, some column names contain spaces, resemble a data type (like datetime), or even start with a number.
CREATE TABLE [dbo].[Customer Contact](
[ID] [int] NOT NULL,
[Customer Name] [nchar](10) NULL,
[Datetime] DATETIMEOFFSET NULL,
[1st Contact] bit NULL,
CONSTRAINT [PK_Customer Contact] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
Auto-Generated App Issues
When we initiate an auto-generated app using data-driven options, certain Power Apps functions demand the passage of string column names. These encompass functions like Search, SortByColumns, among others. In these scenarios, special characters need to be escaped, making them less user-friendly than their Regular Identifier counterparts.
Beware of Numbers as Initials
A noticeable bug emerges with our example application. While data retrieval is possible, referencing a column name starting with a number causes an error. Such issues become evident when, for instance, trying to set a label in a gallery control linked to a field like “1st Contact”. This action results in a data retrieval failure, presenting an error indicating an invalid operation.
A Potential Workaround
Given this bug, data beginning with a number remains inaccessible. A feasible workaround involves creating a SQL view that renames the column, ensuring it doesn’t commence with a number. This view can then be utilized to extract the table data.
CREATE VIEW CustomerContactView
AS
SELECT [ID],
[Customer Name],
[Datetime],
[1st Contact] AS [FirstContact]
FROM
[dbo].[Customer Contact]
Conclusion
Adopting naming conventions that exclude spaces and special characters for SQL Server objects typically reduces potential complications. This practice eliminates the need to escape these characters under specific circumstances. Most crucially, refrain from naming columns with identifiers starting with numbers. Power Apps fails in fetching columns starting with numbers, consequently displaying an error.
For additional assistance or queries regarding this blog or any technical concerns, feel free to contact us. We’re here to help and ensure you have the best experience.
