[thelist] [MS SQL Trickery] Is my technique good, bad, or neither?

[rudy]

well it appears that the internet swallowed my original reply to the list to
joshua's question, and barfed up dupes of a reply to a message that i now
see was probably offlist to me

if anyone's confused, sorry

blame it on sobig, blame it on my service provider, blame it on me

here's my first reply to joshua's original question:


> UDF?  not terribly portable across database systems
>
> that may of course not be a big concern to you
>
> still, i don't see your method as "trickery"
>
> dunno about UDF performance in queries, i never used one before
>
>
> here's an alternate strategy: an application "ooak" table
>
> "ooak" = "one of a kind"
>
> similar to oracle's DUAL, and used in exactly the same circumstances that
> DUAL is used --
>
>   e.g.  select current_date from ooak
>
> ooak has exactly one row, and i usually don't both assigning a pk (because
> instances where a pk is required are vanishingly rare, i.e. where ooak has
> child tables with foreign keys that need to reference ooak's pk)
>
> there is one column for every "one of" application constant
>
>    create table ooak
>      ( default_language_id  smallint not null )
>
>    insert into ooak values (11)
>
> your default language thingie fits this model perfectly!
>
>     CREATE VIEW dbo.view_items_default_language
>     AS
>     SELECT
>         item.field1
>       , item.field2
>       , ...
>       , item.fieldX
>       , item_language.name
>       , item_language.description
>     FROM       dbo.item
>     CROSS JOIN dbo.ooak            -- is this cool or what
>     INNER JOIN dbo.item_language
>     ON dbo.item_language.language_id = dbo.ooak.default_language_id
>     AND dbo.item_language.item_id = dbo.item.id


if anyone's interested in an elaboration, just holler

the cross join is a pretty rare animal and this is a great example where it
is combined with a single-row table to provide customized application data
without pain or fuss


rudy