Create a column based on another column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create a column based on another column

Author

Topic

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 10:45:09

I have a table that contains a DateTime column, entitled tTimeStamp. I want to create another column in this table that is derived from this column as such:

DateAdd(day, -2, DateAdd(week, DateDiff(week, 0,Washrunchem.BOF_Date)+1, 0))

This would result in the End of Week date. I then want to place an index on this column.

All of this is in order to speed up a SQL that groups data by week ending date.

What is the best way to do this? Should I use an indexed view or is there a way I can have this "derived table column" stored in the table itself?

TIA!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-23 : 12:30:55

If you are grouping the entire table, the index may not help. If you can limit the rows prior to the grouping, that would be your best bet. However...

assuming your calculation refers to a column in the same table you can add a computed column use CREATE or ALTER table statements:


alter table Washrunchem add
  EOWDate as DateAdd(day, -2, DateAdd(week, DateDiff(week, 0,BOF_Date)+1, 0))

Then index the column as you would any other column. There are some rules to observe for indexing a computed column. See
Books Online: "creating indexes on computed columns".

You should confirm that you will be able to create AND USE the index given the limitations described in BOL. If you can't for any reason, a fair alternative could be to create a normal (indexed) column and use a trigger or job to maintain the value.

Be One with the Optimizer
TG

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 12:46:49

Exactly what I was looking for. What's the benefit of doing it this way as opposed to an indexed view?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-23 : 13:29:45

>>What's the benefit of doing it this way as opposed to an indexed view?

There are even more restrictions for creating an indexable view (see BOL: Creating an Indexed View - Using Indexes on Views). This is so much hassle I've seen several people determined to use this concept and finally give up. Not only to create the index on the view but then to use it from application calls. every connection that would want to benefit from the index needs to meet the criteria specified in BOL. It is a major pain to add this to an existing project because referenced objects may need to be dropped and recreated with specific SET options and existing connection strings and/or application code needs to be adjusted. Ughh!

Be One with the Optimizer
TG

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 13:50:48

Yeah, I was looking at that in the BOL also.

Thanks for the heads up!

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 14:55:11

OK I setup the calculated column like so...

SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF

alter table MeterData add
 EOW_Date as DateAdd(day, -2, DateAdd(week, DateDiff(week, 0,[TimeStamp])+1, 0))

CREATE Index IX_MeterData_EOW ON MeterData (EOW_Date)

It creates the column just fine, but I get an error on the Create Index...
"Cannot create index because the key column 'EOW_Date' is non-deterministic or imprecise."

How do I get this calculated column to be precise and deterministic?

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 15:08:12

Never mind. I'm going with a Trigger. Thanks.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-23 : 16:02:58

oh yeah, forgot to mention the battles of making all functions, calculated columns, and views involved with the view (you want to index) deterministic (watch out for implicit conversions to datetime).

Anyway your determimism problems was due to the implicit conversion of int to datetime (0s in your dateAdd and DateDiff functions)

You would need to create a function that returns your Date 0 ie:
(oh remember to use schemabinding even though its not bound to anything - ughh!)
(oh, and even though we're converting TO datetime we still need the conversion style - groan!)


create function dbo.Date0() 
returns datetime
with schemabinding 
as 
begin
	return convert(datetime, 0, 112)
end
go

--check to make sure it's determisistic
select objectproperty( object_id('dbo.Date0'), 'IsDeterministic')

Then use that in your computed column def:


alter table MeterData add
 EOW_Date as DateAdd(day, -2, DateAdd(week, DateDiff(week, dbo.Date0(),[TimeStamp])+1, 0))

Be One with the Optimizer
TG

Ken Blum
Constraint Violating Yak Guru

383 Posts

Posted - 2006-02-23 : 16:10:11

Thanks again TG, that's really good to know. Howeever I'm sticking with the Trigger. The inserts/updates to this table are minimal so it's no big deal to double them, and the code is alot cleaner.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources