Set an IsUsedFlag - SQL Server Forums

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)

Set an IsUsedFlag

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-01 : 14:11:23

This is kinda an off-the-wall situation, but I have a case where I need to set a bit column on a table based on whether or not that column in involved in any DRI.

I thought my solution was mildly interesting, so I figured I'd share:


create table a(a char(1) not null primary key, IsUsed int)
create table b(b char(1) not null primary key, a char(1) references a(a))

insert into a(a) select 'a'
insert into a(a) select 'x'
insert into b(b,a) select 'b','a'

declare @a char(1)
while exists (select 1 from a where IsUsed is null)
begin
    select top 1 @a = a from a where IsUsed is null

    begin try
        begin transaction
        delete a where a = @a
        rollback transaction
        update a set IsUsed = 0 where a = @a
    end try
    begin catch
        rollback transaction
        update a set IsUsed = 1 where a = @a
    end catch
end

select * from a
select * from b

drop table b
drop table a

SQL2K5 solution only ...

Obviously, in this case b is the only consumer of a; however, in my situation there are many tables that consume a and the query into each of those tables is a PITA, not to mention ever time a new object is added that consumes a, I would need to modify my IsUsed updator ...

Jay White

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-01 : 15:24:09

what's a DRI?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-01 : 15:27:37

and wouldn't a left join be simpler?
or am i missing something?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-01 : 15:28:46

Declaritive Referential Integrity ...

What I mean to say is is this row actively taking part in a foreign key relationship with another table.

Jay White

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-01 : 15:29:58

Interesting, Jay.

What "runs" your updater code? How do you know at any given time that the flag is up to date? Even a second after it completes?

(not a challenge, just a question from a guy always open to new solutions

)

Be One with the Optimizer
TG

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-01 : 15:31:02

quote:
Originally posted by spirit1

what's a DRI?

Go with the flow & have fun! Else fight the flow
blog thingie: http://weblogs.sqlteam.com/mladenp

I was going to ask, "what's a PITA"...but I figured it out

Be One with the Optimizer
TG

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-12-01 : 15:44:18

quote:
Originally posted by spirit1

and wouldn't a left join be simpler?
or am i missing something?

I'm using a Dimensional model where each of my 70+ Dimension tables are referenced by up to 10 different Fact tables. Yeah, you can do the same thing with 700+ left joins ... and when the 11th Fact is added, you'll need to add the 11th left join to each of the 70+ updates. While my way isn't a great performer, it is nice for maintenance :)

quote:
Originally posted by TG
Interesting, Jay.

What "runs" your updater code? How do you know at any given time that the flag is up to date? Even a second after it completes?

(not a challenge, just a question from a guy always open to new solutions )

My updator runs at the end of my warehouse load. The DW doesn't change between loads.

Like I said, an off-the-wall situation ... I just though it was neat how a transaction nested in a try catch worked.

Jay White

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-01 : 15:55:32

That is neat, and considering it's a DW load where the data doesn't change it seems like a good solution to me...thanks! I'll keep it in mind when we move to 2K5.

Be One with the Optimizer
TG

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-01 : 16:13:46

ok i haven't been around a DW yet so i might be asking a stupid question...
but what does "actively taking part in a foreign key relationship with another table" mean exactly?

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-01 : 16:24:37

Not bad, I like it Jay. Sometimes worse performance is well worth it if your code is so much simpler, as in this case.

- Jeff

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-01 : 16:57:33

Jeff! Seems like it's been awhile since we've shared a topic...nice to see ya again :)

Be One with the Optimizer
TG

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-12-02 : 13:04:03

quote:
Originally posted by TG

Jeff! Seems like it's been awhile since we've shared a topic...nice to see ya again :)

Be One with the Optimizer
TG

Thanks, TG, it's been pretty busy for me the past few months so I haven't been able to visit SQLteam as much as I'd like ....

- Jeff

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-02 : 14:05:57

Similar kind of question was asked some days ago here:

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75607[/url]

Harsh Athalye
India.
"Nothing is Impossible"

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-02 : 17:52:09

you can also get this information from sysreferences (2000) or sys.foreign_keys (2005)

http://msdn2.microsoft.com/en-us/library/ms186900.aspx
http://msdn2.microsoft.com/en-us/library/ms189807.aspx

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

pootle_flump

1064 Posts

Posted - 2006-12-02 : 18:11:46

quote:
Originally posted by spirit1

but what does "actively taking part in a foreign key relationship with another table" mean exactly?

Based on my reading of the code - whether or not the ~~child~~ parent table (a) contains a value referenced ~~from~~ by the ~~parent~~ child (b).

quote:
Originally posted by jezemine

you can also get this information from sysreferences (2000) or sys.foreign_keys (2005)

This tells you the relationships between the tables but not whether a particular row of data contains values from the related table.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-03 : 01:04:43

ah, now I get it...

SqlSpec: a fast and comprehensive data dictionary generator for
SQL Server 2000/2005, Analysis Server 2005, Access 97/2000/XP/2003
http://www.elsasoft.org

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-12-03 : 09:46:06

~~sys.indexes maybe?~~

now i get it.
Nice one jay.

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources