One column

[Doug Baroter]

Aside from temp table, in what circumstance one would have only one
column for a table (be it data or key)? I can't think of any case but
some of you may.
Thanks.

[Erland Sommarskog]

Doug Baroter (qw********@boxfrog.com) writes:

Aside from temp table, in what circumstance one would have only one
column for a table (be it data or key)? I can't think of any case but
some of you may.

The system I work with have a couple of one-column one-row tables. These
tables hold next available number in series which due to business rules
must be contiguous. They are in tables of their own to avoid a hot spot
in a table; this was an issue in SQL 6.5.

We also have a one-column table that holds dates from 1980-01-01 to 2149-12-
31. If you read a book like Joe Celko's "SQL for Smarties", you will find
several solutions that are based on a table of numbers. That is, a one-
column tables with all numbers from 1 up to some limit. Both our date table
and a table of numbers are auxillary tables that makes other queries
easier to write.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Trevor Best]

On 1 Aug 2003 21:56:54 -0700 in comp.databases.ms-sqlserver,
qw********@boxfrog.com (Doug Baroter) wrote:

Aside from temp table, in what circumstance one would have only one
column for a table (be it data or key)? I can't think of any case but
some of you may.
Thanks.

I have a few:

tblCityNoCounty - Cities with no county so the County field on a form
is conditionally required, e.g. enter "London" as the City and there's
no need to enter the county. Really should be 2 columns with country
as well as there's Londons around the world but then at the time it
was only for the UK.

tblCountry - Speaks for itself but then I could have other info on
that like dialling code, etc. At first it did but were dropped thru
lack of use.

tblSupplierFav - List of supplier PKs that will show in a supplier
form.

tblTitle - Mr, Mrs, Dr, Rev, etc.

zstblSQLReservedWord - used in a tool I wrote to scan all tables for
instances of SQL reserved words in table/column names in an Access
database prior to upsizing.

--
Ride Free (but you still have to pay for the petrol)

(replace sithlord with trevor for email)

[Doug Baroter]

Excellent. It seems in essence the auxillary tables serve to make
system/solution work better, they themselves are not data tables, I
use this concept as well.

I need to catch up on Joe Celko's "SQL for Smarties" after I put out
immediate fire here.

Thanks.

Erland Sommarskog <so****@algonet.se> wrote in message news:<Xn**********************@127.0.0.1>...

Doug Baroter (qw********@boxfrog.com) writes:

Aside from temp table, in what circumstance one would have only one
column for a table (be it data or key)? I can't think of any case but
some of you may.

The system I work with have a couple of one-column one-row tables. These
tables hold next available number in series which due to business rules
must be contiguous. They are in tables of their own to avoid a hot spot
in a table; this was an issue in SQL 6.5.

We also have a one-column table that holds dates from 1980-01-01 to 2149-12-
31. If you read a book like Joe Celko's "SQL for Smarties", you will find
several solutions that are based on a table of numbers. That is, a one-
column tables with all numbers from 1 up to some limit. Both our date table
and a table of numbers are auxillary tables that makes other queries
easier to write.

[Doug Baroter]

Thank you. What you described, in Erland Sommarskog's term, is auxillary tables.

Trevor Best <bouncer@localhost> wrote in message news:<k3********************************@4ax.com>. ..

On 1 Aug 2003 21:56:54 -0700 in comp.databases.ms-sqlserver,
qw********@boxfrog.com (Doug Baroter) wrote:

Aside from temp table, in what circumstance one would have only one
column for a table (be it data or key)? I can't think of any case but
some of you may.
Thanks.

I have a few:

tblCityNoCounty - Cities with no county so the County field on a form
is conditionally required, e.g. enter "London" as the City and there's
no need to enter the county. Really should be 2 columns with country
as well as there's Londons around the world but then at the time it
was only for the UK.

tblCountry - Speaks for itself but then I could have other info on
that like dialling code, etc. At first it did but were dropped thru
lack of use.

tblSupplierFav - List of supplier PKs that will show in a supplier
form.

tblTitle - Mr, Mrs, Dr, Rev, etc.

zstblSQLReservedWord - used in a tool I wrote to scan all tables for
instances of SQL reserved words in table/column names in an Access
database prior to upsizing.