SQL server calculated field value

Question

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.

Answer 1

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....! |
+----+--------------------------+--------------------------+-----------------+

Answer 2

In my experience is better a calculated field than a trigger because a trigger could generate performance issues in the future. Try this piece of code that uses a calculated field:

CREATE TABLE [dbo].[Table_1](
    [id] [int] NOT NULL,
    [name] [varchar](100) NOT NULL,
    [label]  AS (lower(replace([name],' ','_'))),
    [description] [varchar](max) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

INSERT INTO [dbo].[Table_1]
([id]
,[name]
,[description])
VALUES
(1
,'Sample Product'
,'Sample Product Description')

GO

Answer 3

An AFTER INSERT TRIGGER should solve your problem

IF OBJECT_ID('dbo.TestTable', 'U') IS NOT NULL 
  DROP TABLE dbo.TestTable; 
go

create table TestTable (ID int, Name varchar(100), Label varchar(100), Description varchar(max))
go
create TRIGGER dbo.UpdateLabel ON dbo.TestTable
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE tt
    SET tt.label = replace(lower(i.NAME), ' ', '_')
    FROM dbo.TestTable tt
    JOIN inserted i ON i.id = tt.id
END
GO

INSERT INTO TestTable (ID, NAME, Label, Description)
VALUES (1,'JOHN SMITH',' ','TestDescription')

SELECT *
FROM testtable