SQL Server 2012 – Calculated Field Value

sql serversql-server-2012

Not sure how to start describing this so I get straight to it.

I have a SQL Server 2012 database with a sample table Table_1 with the below (simplified) design:

id: int (PK, ID, NOT NULL)
name: varchar(100) (NOT NULL)
label: varchar(100) (NOT NULL)
description: varchar(max) (NOT NULL)

on my front end form user is asked for a name and description only.

What I want to achieve from the database is to, at the point of creating new record, get the value of the name field, convert it to lowercase and replace any spaces with underscores, and then insert the resulting string into the label column.

For example if user provided name "Sample Product", I want the label to be "sample_product".

How can I achieve this and what is the best approach: triggers or the calculated field property can do this?

Thanks in advance for any help or pointers.

Best Answer

Although you can do this with a trigger, a computed column would probably suit your requirements better (although this will depend on your application).

This is the syntax you'd need for the table with a computed column (the [label] column):

CREATE TABLE [Table_1] (
[id] int PRIMARY KEY IDENTITY NOT NULL,
[name] varchar(100) NOT NULL,
[label] AS REPLACE(LOWER([name]), ' ','_'),
[description] varchar(max) NOT NULL
);

Why a computed column?

  • Inserts/updates are not allowed on computed columns, so you don't have to worry about anyone overriding this formatting, so this can be enforced without complicated permissions etc.

  • Inserts will be quick - no work is actually done for a computed column during an insert, and in the vast majority of OLTP applications, reducing insert work is beneficial (this is highly subjective though). A trigger will use another insert statement, slowing down inserts, it would be up to you to decide whether this is significant for your application. Even if your application is read-intensive on that column (and/or requires an index), you can make it a persisted computed column, which would still probably offer better overall performance than trigger. This SO post and this MSDN post discuss the benefits of a computed column over a trigger.

  • Ongoing maintenance is easier. A fairly minor point, but one less trigger is one less object in your database you have to maintain.

Demo of table

INSERT INTO Table_1 (name, description) VALUES 
('Roasted Turnips', 'Very yummy'), 
('Lettuce plus half a Slug' , 'Urgh, yuck....!');

SELECT * FROM Table_1;

Results:

+----+--------------------------+--------------------------+-----------------+
| id |           name           |          label           |   description   |
+----+--------------------------+--------------------------+-----------------+
|  1 | Roasted Turnips          | roasted_turnips          | Very yummy      |
|  2 | Lettuce plus half a Slug | lettuce_plus_half_a_slug | Urgh, yuck....! |
+----+--------------------------+--------------------------+-----------------+
Related Question