Blog

SQL - How NOT to name database tables, columns, and objects

Tip - unexpected errors are less likely to occur when we adhere to simple naming conventions - eg, those without spaces and special characters. In this post, we'll examine a Power Apps bug that occurs when we reference columns that begin with a number.

A great way to minimise potential problems is to adopt a simple naming convention. In this post, I suggest some recomendations on how to name SQL Server objects, and we'll examine a bug in Power Apps that occurs when a column starts with a number.

SQL Server database identifiers

With SQL Server, we can define a wide range of objects. The common ones include databases, tables, views, columns, indexes, stored procedures, and more. The name that we give an object is technically known as an identifier, and these are grouped into two types:

  • Regular Identifiers
  • Delimited Identifiers
The difference between these two types is that a Delimited Identifiers can include spaces or special characters. This then requires us to delimit the name with square brackets.

Although it's valid to use Delimited Identifies, fewer unexpected errors usually occur when we adhere to naming rules that apply to Regular Identifiers. These include:

  • The name should begin with a letter (upper case A-Z, case a-z)
  • The name should NOT begin with a number
  • The name should NOT contain spaces
  • The name should NOT share the same name as a SQL keyword

As an extra measure, I also tend to avoid using object names to match function or other keywords in Power Apps.

For additional information on regular identifies, we can refer to the Microsoft SQL Server documentation here:

Walkthrough of using a table with Delimited Identifiers

As an example, let's walkthrough an example app that's based on a SQL Server table that uses a range of Delimited Identifiers.

Here's the definition of the table. As we can see, we've used a range of Delimited Identifiers. These include column names with spaces, a name that matches a data type (datetime), and a name that begins 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

Creating an auto-generated app
Next, we'll create an auto generated app using the "start with data option".

When we open this app, the first thing we notice is that certain functions in Power Apps require us to pass a string column name. These functions include Search, and SortByColumns, and a few more. In these cases, it's necessary to escape the special characters.

In this example, the string identifier that we use looks like this - "Customer_x0020_Name" (as shown in the screenshot beneath).This is far less friendly to work with, compared to the alternative of defining the object without a space to begin with.


On a more positive note, with other functions such as Patch, we can build the record that we want to patch using column names that are enclosed in single quotes. This provides formula that is much more readable.

The formula beneath works, and will successfully create a record in the table.

Patch('[dbo].[Customer Contact]', 
      Defaults('[dbo].[Customer Contact]'), 
      {'Customer Name':"Tim Leung",
       '1st Contact':true, 
       Datetime:Now(),
       ID:3}
)

Warning! - Don't use Column Names that begin with a number

With our example app, we soon notice a very apparant bug. Although we can retrieve rows from our table, as soon as we reference a column name that begin with a number, we receive an error.

In the example beneath, we attempt to set a label in a gallery control to the "1st Contact" field. When we do this, the data retrieval operation fails, and we receive an error against the Items property that reads - "Requested operation is invalid. Server response: [TableName] failed. An identifier was expected at position X."


Workaround for Bug - Use a SQL View

Due to this bug, we cannot retrieve data that begins with a number. One workaround for this bug is to create a view that renames the column so that it doesn't begin with a number. We then use this view to retrieve the data from our table.

CREATE VIEW CustomerContactView 
AS
SELECT  [ID],
	[Customer Name],
	[Datetime],
	[1st Contact] AS [FirstContact] 
FROM
[dbo].[Customer Contact]

Conclusion

In general, fewer problems occur when we name SQL Server objects without spaces and special characters. This removes the need to escape these characters in certain situations. Most importantly, we should not name columns with an identifier that starts with a number. Power Apps fails to retrieve columns that begins with a number, and it will instead show an error.

Previous
Next
Related posts