Computed columns 1


computed columns

use test

We used computed columns many times , but we can get most benefit of computed columns when we can mark them
as persisted.
For this columns must be deterministic.
Lets see how can we get it



CREATE TABLE test
  (
     first_name VARCHAR(50),
     last_name  VARCHAR(50),
     full_name AS first_name + ' ' + last_name,
     entry_date AS CONVERT(DATE, Getdate()),
     hour_id AS Datepart(HH, Getdate()),
     minute_id AS Datepart(MI, Getdate())
  )




INSERT INTO test
            (first_name,
             last_name)
VALUES      ('amish',
             'shah')

INSERT INTO test
            (first_name,
             last_name)
VALUES      ('anil',
             'shah')

SELECT *
FROM   test






Now here we can see that full_name is combination of first_name and last_name but what if table has million of rows?

Querying full_name will get benefit of indexing?
Is it good if we get this values at runtime?
Getting date and minute and hour at runtime efficient?

So ultimate solution is we need this values to be stored in table

We need to check either this value is deterministic at first?

SELECT Columnproperty(Object_id('test'), 'full_name', 'isCOMPUTED'),
       Columnproperty(Object_id('test'), 'full_name', 'isDETERMINISTIC'),
       Columnproperty(Object_id('test'), 'full_name', 'isINDEXABLE'),
       Columnproperty(Object_id('test'), 'full_name', 'isPRECISE')

SELECT Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isCOMPUTED'),
       Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isDETERMINISTIC'),
       Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isINDEXABLE'),
       Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isPRECISE')

SELECT Columnproperty(Object_id('test'), 'hour_id', 'isCOMPUTED'),
       Columnproperty(Object_id('test'), 'hour_id', 'isDETERMINISTIC'),
       Columnproperty(Object_id('test'), 'hour_id', 'isINDEXABLE'),
       Columnproperty(Object_id('test'), 'hour_id', 'isPRECISE')





Here we can see values derived from getdate() is not deterministic, so we can not create index over it

So first we will mark full_name as persisted

alter table test
alter column full_name
add persisted

Now this full_name is marked persisted and its value will be stored in table.

Now we will go for entry_date column

alter table test
alter column entry_date
add persisted



we are getting error

Msg 4936, Level 16, State 1, Line 58
Computed column 'ENTRY_DATE' in table 'test' cannot be persisted because the column is non-deterministic.


So whats solution for this?

We need to redesign table here
DROP TABLE test

CREATE TABLE test
  (
     first_name     VARCHAR(50),
     last_name      VARCHAR(50),
     full_name AS first_name + ' ' + last_name,
     entry_datetime DATETIME DEFAULT Getdate(),
     entry_date AS CONVERT(DATE, entry_datetime),
     hour_id AS Datepart(HH, entry_datetime),
     minute_id AS Datepart(MI, entry_datetime)
  )
  INSERT INTO test
            (first_name,
             last_name)
VALUES      ('amish',
             'shah')

INSERT INTO test
            (first_name,
             last_name)
VALUES      ('anil',
             'shah')


SELECT *
FROM   test



SELECT Columnproperty(Object_id('test'), 'full_name', 'isCOMPUTED'),
       Columnproperty(Object_id('test'), 'full_name', 'isDETERMINISTIC'),
       Columnproperty(Object_id('test'), 'full_name', 'isINDEXABLE'),
       Columnproperty(Object_id('test'), 'full_name', 'isPRECISE')

SELECT Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isCOMPUTED'),
       Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isDETERMINISTIC'),
       Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isINDEXABLE'),
       Columnproperty(Object_id('test'), 'ENTRY_DATE', 'isPRECISE')

SELECT Columnproperty(Object_id('test'), 'hour_id', 'isCOMPUTED'),
       Columnproperty(Object_id('test'), 'hour_id', 'isDETERMINISTIC'),
       Columnproperty(Object_id('test'), 'hour_id', 'isINDEXABLE'),
       Columnproperty(Object_id('test'), 'hour_id', 'isPRECISE')





Now we can see we are getting 1 for all properties.
All this columns are deterministic.
We can mark them persisted

ALTER TABLE test
ALTER COLUMN entry_date
ADD persisted



ALTER TABLE test
ALTER COLUMN minute_id
ADD persisted

ALTER TABLE test
ALTER COLUMN hour_id
ADD persisted



No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts