Creating correlated fields in a sql table

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

General SQL Server Forums

New to SQL Server Administration

Creating correlated fields in a sql table

Author

Topic

HaniaGueiros
Starting Member

3 Posts

Posted - 2009-06-08 : 06:51:59

Hello all,

My question is about SQL Server 2005.
I was wondering if it was at all possible to creat a field in a table which "gets" a value for other field from the same table.

For example:
Table Student
field BDT (birthdate - mm/dd/yyyy) field BYM (birthyearMonth - yyyymm)

I want to insert the birthdate field (BDT) and the field birthYearMonth (BYM)automatically will receive the Value from BDT.

I hope this isn't confusing. Just wondering if this is possible.

Thank you!

Hania Christina Gueiros

Hania Christina Gueiros

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-08 : 06:54:56

This is possible but useless.
If you have BDT you can retrieve always year(BDT) and month(BDT) as well.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 07:44:27

Yes. The technique is called "Calculated Column" and can be persisted or nonpersisted in conjunction with index or not.

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-08 : 07:49:31

Why is this useless? De-normalizing like this can be perfectly justifiable depending on business needs. Grouping for example would be a lot faster than to group on YEAR(BDT), MONTH(BDT), given that the columns is marked as PERSISTED.

What you are referring to is called a computed column and can be created like this:

create table testing (
  bdt datetime not null,
  bym as left(convert(varchar(8), bdt, 112), 6) persisted
)

- Lumbago

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-08 : 07:54:03

quote:
Originally posted by Lumbago

Why is this useless? De-normalizing like this can be perfectly justifiable depending on business needs. Grouping for example would be a lot faster than to group on YEAR(BDT), MONTH(BDT), given that the columns is marked as PERSISTED.

What you are referring to is called a computed column and can be created like this:
create table testing (
bdt datetime not null,
bym as left(convert(varchar(8), bdt, 112), 6) persisted
)

- Lumbago

Ok sorry for my snap judgment.

No, you're never too old to Yak'n'Roll if you're too young to die.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-08 : 07:59:16

You are halfway right though, since the data of computed columns without the persisted-keyword are not actually stored but calculated each time. From BOL:

quote:
Unless otherwise specified, computed columns are virtual columns that are not physically stored in the table. Their values are recalculated every time they are referenced in a query. The SQL Server 2005 Database Engine uses the PERSISTED keyword in the CREATE TABLE and ALTER TABLE statements to physically store computed columns in the table. Their values are updated when any columns that are part of their calculation change.

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 08:06:50

Persisted calculated columns can be indexed.

E 12°55'05.63"
N 56°04'39.26"

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2009-06-08 : 08:12:21

Persisted ~~calculated~~ computed columns can be indexed.

- Lumbago

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 08:17:38

Yes, you're right about the terminology!
http://msdn.microsoft.com/en-us/library/ms189292.aspx

E 12°55'05.63"
N 56°04'39.26"

HaniaGueiros
Starting Member

3 Posts

Posted - 2009-06-08 : 10:14:13

Thanks! It really helpt me!

But my aplication needs another thing, for example:

field --> type
BDT --> mm/dd/yyyy
COD --> int - identity increment
BYM --> BDT(YYMM) & format(COD,"00000")
Is that possible??

I need this BYM field to be an index. Please tell me how can I do it!

Hania Christina Gueiros

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-08 : 11:09:56

Yes.
ALTER TABLE Table1
ADD BYM AS REPLACE(STR(COD, 5), ' ', '0') PERSISTED

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources