One column

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.Doug Baroter (qwert12345@.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, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 1 Aug 2003 21:56:54 -0700 in comp.databases.ms-sqlserver,
qwert12345@.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)|||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 <sommar@.algonet.se> wrote in message news:<Xns93CB67FF99FEBYazorman@.127.0.0.1>...
> Doug Baroter (qwert12345@.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.|||Thank you. What you described, in Erland Sommarskog's term, is auxillary tables.

Trevor Best <bouncer@.localhost> wrote in message news:<k39niv42a0s86v1htc4kdl8mbvm9v4ecuk@.4ax.com>...
> On 1 Aug 2003 21:56:54 -0700 in comp.databases.ms-sqlserver,
> qwert12345@.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.