“Table of Constants” – is this common practice

best practices

College SQL class, using the book "SQL Fundamentals" by John J. Patrick. In the third chapter, he talks about using a "table of constants" to add columns to a select statement, where all rows have the same value.

For example, if you have table "characters", as so:

first_name  last_name  dept_code
----------- ---------- -------------------
Fred        Flintstone ROCKS
Barney      Rubble     ROCKS
Wilma       Flintstone FACEPALMING_AT_FRED

and you want a SELECT that adds a column "hometown" with value "BEDROCK" to all rows, he recommends making a second table in the database, "temp", with

hometown
--------
BEDROCK

and then doing

SELECT first_name, last_name, dept_code, hometown FROM characters, temp

The idea is that this avoids putting string constants in the SELECT statement, and that if you've got a lot of SELECTs that need the same constants, it's easier to update one table than fifty queries.

The thing is, I've been working with SQL databases for the last fifteen years, and I have never seen this construction. Is it something totally common that I've just missed, or is it something that I can erase from my memory after this assignment is over?

Best Answer

Joe Celko mentions tables of constants in a couple of his books.

He suggests if using a table that a check constraint is added that ensures the table can contain no more than one row.

CREATE TABLE Constants
(
lock CHAR(1) DEFAULT 'X' NOT NULL PRIMARY KEY CHECK (lock = 'X'),
pi FLOAT DEFAULT 3.142592653 NOT NULL,
e FLOAT DEFAULT 2.71828182 NOT NULL,
phi FLOAT DEFAULT 1.6180339887 NOT NULL
);

Or alternatively a view can be used for a similar purpose.

CREATE VIEW Constants
AS
SELECT *
FROM 
(VALUES(3.142592653,
        2.71828182,
        1.6180339887)) V(pi,e,phi)

Not something I've used much, if at all, myself but always worth having additional possible techniques to consider.

The specific use case in your question isn't something I would consider a table of constants for though. TBH the hardcoded same "hometown" for everyone just seems a nonsensical requirement.

Related Solutions

Sql-server – Best Practice for Folder strucure

Never on C:\ if it can be avoided, though with smallish SSDs and VMs on laptops it's hard to have anything but C:.

So where else? Depends on the config. I've done like you for simple workstation installs: data, logs, tempdb all on D:. But then you get into more advanced implementations where you have a SAN and many spindles to choose from - there I've had groups of data files on M:\ and N:\, log files on L:\, tempdb on T:\, etc. Making deployment easier isn't the only goal you should consider... it's also reducing the need for re-deployment and/or moving of data/log files later. :-)

SQL Server – Is Table Aliasing a Bad Practice?

Table aliasing is a common and helpful practice.

It saves you keystrokes when referencing columns anywhere in your query.
It improves the readability of your SQL when you are referencing many tables. Aliases let you give those tables a short name plus a little meaning to how they are being used.
It is even required when you join a table to itself or when you join to the same table multiple times. This is so the query optimizer knows which table you are referencing when you mention a column.

The following reporting extract illustrates all of the above points nicely:

INSERT INTO reporting.txns_extract
SELECT 
    -- 30+ columns snipped
    -- 
    -- Would you want to type out full table names for each 
    -- column here?
FROM 
    -- ... and in the JOIN conditions here?
                billing.financial_transactions  ft_cdi   -- alias required here
    INNER JOIN  
                billing.cash_application_links  cal
            ON  ft_cdi.key_num = cal.applied_ft_key_num
    INNER JOIN  
                billing.financial_transactions  ft_pmt   -- alias required here
            ON  cal.owner_key_num = ft_pmt.key_num
    LEFT OUTER JOIN
                billing.invoice_lines           invl
            ON  ft_cdi.key_num = invl.invoice_key_num
    LEFT OUTER JOIN
                billing.charges                 chrg
            ON  invl.creator_key_num = chrg.key_num
    LEFT OUTER JOIN
                billing.customer_services       cs
            ON  chrg.cs_key_num = cs.key_num
    INNER JOIN
                billing.billers                 bil
            ON  ft_cdi.biller_account_key_num = bil.biller_account_key_num
    INNER JOIN
                billing.formal_entities         fe
            ON  bil.frml_key_num = fe.key_num
WHERE
    -- ... and in the WHERE conditions here?
        ft_cdi.transaction_type <> 'Payment'   -- alias tells me this table is not for payments
    AND ft_cdi.status = 'Approved'
    AND ft_pmt.transaction_type =  'Payment'   -- alias tells me this table is for payments
    AND ft_pmt.status = 'Approved'
    AND ft_cdi.last_user_date >   ft_last_user_date_begin
    AND ft_cdi.last_user_date <=  ft_last_user_date_end
;

Best Answer

Related Solutions

Sql-server – Best Practice for Folder strucure

SQL Server – Is Table Aliasing a Bad Practice?

Related Question