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