Ok, time to start a war

Trivia perhaps, but this has bothered me for a long time. Over the years,
I've noticed that some folks are adamant that table names should be
singular; others are adamant they should be plural. Myself, I believe in the
plural school. I simply don't understand the argument that a table should be
named singular, as it holds a collection of entities. We have tables of
Customers, Contacts, Addresses, Quotes, Sales, etc. A column is AN attribute
of an entity, a row is AN instance of an entity. During development, when I
populate a list with Customers, the immediate thought is to use the
"Customers" table, not the "Customer" table. I'm interested in someone
convincing me otherwise.Earl (brikshoe@.newsgroups.nospam) writes:
> Trivia perhaps, but this has bothered me for a long time. Over the
> years, I've noticed that some folks are adamant that table names should
> be singular; others are adamant they should be plural. Myself, I believe
> in the plural school. I simply don't understand the argument that a
> table should be named singular, as it holds a collection of entities. We
> have tables of Customers, Contacts, Addresses, Quotes, Sales, etc. A
> column is AN attribute of an entity, a row is AN instance of an entity.
> During development, when I populate a list with Customers, the immediate
> thought is to use the "Customers" table, not the "Customer" table. I'm
> interested in someone convincing me otherwise.
I have a suggestion for a compromise! Let's use the Slovene for the table
names. Then the two camps can meet each other half-way and use the dual
number for table names!
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Earl wrote:

> Trivia perhaps, but this has bothered me for a long time. Over the years,
> I've noticed that some folks are adamant that table names should be
> singular; others are adamant they should be plural. Myself, I believe in t
he
> plural school. I simply don't understand the argument that a table should
be
> named singular, as it holds a collection of entities. We have tables of
> Customers, Contacts, Addresses, Quotes, Sales, etc. A column is AN attribu
te
> of an entity, a row is AN instance of an entity. During development, when
I
> populate a list with Customers, the immediate thought is to use the
> "Customers" table, not the "Customer" table. I'm interested in someone
> convincing me otherwise.
Naming conventions are important but their most important feature is
that they are applied consistently. I can live with either plural or
singular names as long as everyone sticks to the same convention. What
I don't want to see is one table called "Customer" and another in the
same project called "Contacts".
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||ROFL!
And maybe we can also introduce inflections to designate actions. :)
ML
http://milambda.blogspot.com/|||Hear Hear!
I also only like lower case and separate words with _ because you can run
into all sorts of problems if you develop on a case insensitive database and
then go to a case sensitive one. I was part of a big project that had that
exact problem and it convinced me to not use camel case and to use lower
case and underscore instead.
Once you get used to seeing upper case keywords and lower case identifiers
then its fine, bit like moving from ansi 89 to 92 on the join syntax - what
a pain that was!
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138268495.874429.75640@.f14g2000cwb.googlegroups.com...
> Earl wrote:
>
> Naming conventions are important but their most important feature is
> that they are applied consistently. I can live with either plural or
> singular names as long as everyone sticks to the same convention. What
> I don't want to see is one table called "Customer" and another in the
> same project called "Contacts".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Or perhaps use a Macromedia flash encoded object in a table name so that
when you look at it through a graphical tool it gives an animated
representation of what the table is :)
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"ML" <ML@.discussions.microsoft.com> wrote in message
news:29C18C9B-1411-48F8-A023-0111A9938EC8@.microsoft.com...
> ROFL!
> And maybe we can also introduce inflections to designate actions. :)
>
> ML
> --
> http://milambda.blogspot.com/|||Of course that's customizable - per user. And DBA's have a few extra
settings. :)
ML
http://milambda.blogspot.com/|||what!!! you don't like my naming system. LOL I am guilty of that.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1138268495.874429.75640@.f14g2000cwb.googlegroups.com...
> Earl wrote:
>
> Naming conventions are important but their most important feature is
> that they are applied consistently. I can live with either plural or
> singular names as long as everyone sticks to the same convention. What
> I don't want to see is one table called "Customer" and another in the
> same project called "Contacts".
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||There doesn't seem to be any concensus one way or another on this issue of
singular vs. plural naming conventions.
However, when I see table names prefixed with "tbl", I can't help but
cringe, becuase I know there will be more stupidity in store when I start
looking at their choice of keys, T-SQL, and indexes (or lack thereof). :-)
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:Oy2upQkIGHA.3192@.TK2MSFTNGP10.phx.gbl...
> Trivia perhaps, but this has bothered me for a long time. Over the years,
> I've noticed that some folks are adamant that table names should be
> singular; others are adamant they should be plural. Myself, I believe in
> the plural school. I simply don't understand the argument that a table
> should be named singular, as it holds a collection of entities. We have
> tables of Customers, Contacts, Addresses, Quotes, Sales, etc. A column is
> AN attribute of an entity, a row is AN instance of an entity. During
> development, when I populate a list with Customers, the immediate thought
> is to use the "Customers" table, not the "Customer" table. I'm interested
> in someone convincing me otherwise.
>|||On Thu, 26 Jan 2006 11:41:25 -0000, "Tony Rogerson"
<tonyrogerson@.sqlserverfaq.com> wrote:
in <#Qswf1mIGHA.3036@.tk2msftngp13.phx.gbl>

>Hear Hear!
>I also only like lower case and separate words with _ because you can run
>into all sorts of problems if you develop on a case insensitive database an
d
>then go to a case sensitive one. I was part of a big project that had that
>exact problem and it convinced me to not use camel case and to use lower
>case and underscore instead.
I'd have to agree with that as it simplifies moving your data in and out of
PostgreSQL. :-)
Stefan Berglund

Odd SQL Performance problem

I can't post DDL for this, but perhaps some of you may have experience
with this and will know what's happening.
I have the following problem:
INSERT TableName
SELECT * FROM ViewName
ViewName is a view that returns about 500,000 rows. TableName is a
materialized, cached representation of that data because the view is
relatively complex. I'm hesitant to use an Indexed View because I want
control over how and when my materialized copy gets updated.
I stopped the above statement after it executed for nearly 24 hours.
The following set of statements only took a few minutes to complete
successfully:
SELECT * INTO #X FROM ViewName
INSERT TableName
SELECT * FROM #X
Any ideas as to why the second set of statements would execute so much
more quickly than the first? I assume that the first statement is
trying to sort the data as it selects it to do the clustered index
inserts, thus executing multiple times on smaller sets of data, whereas
the second set of statements executes the view, stores the data and
then has a much simpler result set to sort. Are there any types of
hints that I could provide the processor to speed execution of the
first set of statements?
-AlanDid you look to see what the bottleneck was? Was there blocking, were the
disks or cpu pegged or was it just sitting there? Have you checked the
estimated plans for each to see what the differences were?
Andrew J. Kelly SQL MVP
"Alan Samet" <alansamet@.gmail.com> wrote in message
news:1145127196.691104.246480@.z34g2000cwc.googlegroups.com...
>I can't post DDL for this, but perhaps some of you may have experience
> with this and will know what's happening.
> I have the following problem:
> INSERT TableName
> SELECT * FROM ViewName
> ViewName is a view that returns about 500,000 rows. TableName is a
> materialized, cached representation of that data because the view is
> relatively complex. I'm hesitant to use an Indexed View because I want
> control over how and when my materialized copy gets updated.
> I stopped the above statement after it executed for nearly 24 hours.
> The following set of statements only took a few minutes to complete
> successfully:
> SELECT * INTO #X FROM ViewName
> INSERT TableName
> SELECT * FROM #X
> Any ideas as to why the second set of statements would execute so much
> more quickly than the first? I assume that the first statement is
> trying to sort the data as it selects it to do the clustered index
> inserts, thus executing multiple times on smaller sets of data, whereas
> the second set of statements executes the view, stores the data and
> then has a much simpler result set to sort. Are there any types of
> hints that I could provide the processor to speed execution of the
> first set of statements?
> -Alan
>|||all of the following options are untested :)
insert into tablename select * from (select * from viewname) as a
or
insert into tablename exec('select * from viewname')
P.S: Regarding the second option... I don't know if this kind of an execute
statement works.. never came across a situation where I had to use this :)|||Check the indexes on the target table.
If the data your inserting has primary key values (or other clustered
index values) that are all over the place (not in relative sequence) or
require insertion somewhere other than the end of the table, you might
run into a lot of page splits.
If that target table is large and the inserted data can't fit into the
indexes free space (fillfactor), it can take forever.
If this is the case, you might be better of dropping the clustered
index...insert the data...recreate the clustered index.
Hope this helps...John|||I think it's more related to the processor trying to batch and insert
blocks of results from the source view. One thing I've noticed is that
when running sp_who2 I seem to catch the spid shifting between SELECT,
INSERT and EXECUTE. I was kindof surprised to see EXECUTE in there.
Yes, it's exactly as you saw it INSERT x SELECT * FROM y -- No triggers
or anything.
My main reason for reporting this is here on USENET that I think it's
something that should be optimized in the query processor/optimizer
within SQL Server.
-Alan
retlaw wrote:
> Check the indexes on the target table.
> If the data your inserting has primary key values (or other clustered
> index values) that are all over the place (not in relative sequence) or
> require insertion somewhere other than the end of the table, you might
> run into a lot of page splits.
> If that target table is large and the inserted data can't fit into the
> indexes free space (fillfactor), it can take forever.
> If this is the case, you might be better of dropping the clustered
> index...insert the data...recreate the clustered index.
> Hope this helps...John|||Omnibuzz (Omnibuzz@.discussions.microsoft.com) writes:
> insert into tablename exec('select * from viewname')
> P.S: Regarding the second option... I don't know if this kind of an
> execute statement works.. never came across a situation where I had to
> use this :)
Yes, it works, but I'd be surprised if it makes any changes to performance.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx