> Lets say i have a table that simply stores how many times someone has
> logged into a webpage. Is it better to store each login as a new record
> then count the records or is it better to simply have one record that
> updates the total value by incrementing that one field. I have read all
> manner of articles and some say one way is better vs. the other but what
> I can't find is why? If I knew why one way was better than another then
> I could make and educated decision and choose the best way that is right
> for me. Is updating more or less expensive then inserting? Does it
> matter and is a relevant question?

> CREATE TABLE USER_METRICS
> (
> -- here i can select all the records and count them up.
> ID INT IDENTITY(1,1) PRIMARY KEY,
> Email VARCHAR(250)
> );

> And before anyone comments on my use of all uppercase letters for my
> table name I do this so that my table names stand out within the sql
> server enterprise manager. In other words system tables are lowercase
> and my tables are uppercase. People always seem to give me crap for
> this but never back it up with a good explanation so far as I can tell
> its personal preference? Am I wrong?

"Erland Sommarskog" <sommar@algonet.se> wrote in message
news:Xns946FEE945363DYazorman@127.0.0.1...
> Muhd (muhd@binarydemon.com) writes:
> > Lets say i have a table that simply stores how many times someone has
> > logged into a webpage. Is it better to store each login as a new record
> > then count the records or is it better to simply have one record that
> > updates the total value by incrementing that one field. I have read all
> > manner of articles and some say one way is better vs. the other but what
> > I can't find is why? If I knew why one way was better than another then
> > I could make and educated decision and choose the best way that is right
> > for me. Is updating more or less expensive then inserting? Does it
> > matter and is a relevant question?
>
> There is no clear-cut answer to this question, because it depends on
> what you want. But that said, this table:
>
> > CREATE TABLE USER_METRICS
> > (
> > -- here i can select all the records and count them up.
> > ID INT IDENTITY(1,1) PRIMARY KEY,
> > Email VARCHAR(250)
> > );
>
> does not really make any sense. If this is all you want, then the other
> table is much better. It will be faster to use, and take up less disk
> space.
>
> But let's say one day, you starting thinking "Ì wonder how often people
> log in? Do they make many logins in cluster, or do they log in once an
> hour?" Well, this when you want a table like the above, but you add a
> login_time column. But even then you may want to keep the other table,
> because since if there are many logins, you may prefer to delete old
> rows, but still keep the total number of logins since the start of time.
> Yet then again, if you only want to know the number of logins the last
> two mobths, a table with a row for each login and a timestamp can easily
> be emptied at the end of the month, whereas one with only number cannot.
>
> So the bottom line is that to get what you want, you have to know what
> you want.
>
> > And before anyone comments on my use of all uppercase letters for my
> > table name I do this so that my table names stand out within the sql
> > server enterprise manager. In other words system tables are lowercase
> > and my tables are uppercase. People always seem to give me crap for
> > this but never back it up with a good explanation so far as I can tell
> > its personal preference? Am I wrong?
>
> That is of course personal preference. But you may be interested to know
> that if you right-click a server in EM, and select Edit SQL Server
> Registration Properties, there is a checkbox with which you can hide
> system objects.
>
> Personally, I never or very rarely use EM to look at objects, so this
> is not an issue for me. My preference is all lowercase for all
identifiers,
> since I use all uppercase for keywords.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@algonet.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

>Ok one question in response to your message. If your not using EM (and
> i don't mean to imply that you should, its certainly helpful for me as a
> relative newb) what do you use?

> Hence i believe this is the best solution for.
>
> CREATE TABLE USER_METRICS
> (
> ID INT IDENTITY(1,1) PRIMARY KEY,
> Email VARCHAR(250) NOT NULL
> Login_Time DATETIME NOT NULL
> Logout_Time DATETIME NOT NULL
> )
>