Most applications require a unique id to be assigned to the record. While a unique id can be implemented in several ways, SQL Server offers the option of using a Computed Column to generate and maintain these values.
Computed Columns enable the implementation of some complex logic via a User Defined Function (UDF) in generating the value of the corresponding row. For example: Consider an Employee table that has Employee_Location as a unique field. The Employee_Location changes every time, the employee's department is changed. This field can have values such as E001,Y001 and T009. While there are several ways of implementing this scenario, the Computed Column feature offered by SQL Server is an effective option. Values stored in a Computed Column can be persisted or kept virtual. Thus, it gives you a performance boost as you don't need to store data on the disk that is changing frequently but is required. On the other hand, if the data does need to be stored, the Computed Column can be persisted. This article explains the creation of indexes on Computed Columns.
If your Computed Column is being refrenced from within the application via an ORM such as Hibernate. The corresponding column mapping in the Hibernate POJO has to be annotated using the following set of annotations on the getter of the associated column attribute.
@Column(name = "Employee_Location", length = 25, insertable = false, updatable = false)
@Generated(GenerationTime.INSERT)
What the annotations do is tell Hibernate, that the Computed Column is not to be updated by the application AND that the value in the Computed Column is created when the record is inserted. That's it. Hibernate will not attempt to insert any value into the Employee_Location column but will retrieve the value stored in the column and associate it with any related mapping.
Computed Columns enable the implementation of some complex logic via a User Defined Function (UDF) in generating the value of the corresponding row. For example: Consider an Employee table that has Employee_Location as a unique field. The Employee_Location changes every time, the employee's department is changed. This field can have values such as E001,Y001 and T009. While there are several ways of implementing this scenario, the Computed Column feature offered by SQL Server is an effective option. Values stored in a Computed Column can be persisted or kept virtual. Thus, it gives you a performance boost as you don't need to store data on the disk that is changing frequently but is required. On the other hand, if the data does need to be stored, the Computed Column can be persisted. This article explains the creation of indexes on Computed Columns.
If your Computed Column is being refrenced from within the application via an ORM such as Hibernate. The corresponding column mapping in the Hibernate POJO has to be annotated using the following set of annotations on the getter of the associated column attribute.
@Column(name = "Employee_Location", length = 25, insertable = false, updatable = false)
@Generated(GenerationTime.INSERT)
What the annotations do is tell Hibernate, that the Computed Column is not to be updated by the application AND that the value in the Computed Column is created when the record is inserted. That's it. Hibernate will not attempt to insert any value into the Employee_Location column but will retrieve the value stored in the column and associate it with any related mapping.
1 comment:
Looks like you are an expert in this field, you really got some great points there, thanks.
- Robson
Post a Comment