Database

3-Jan-11 23:05

Dear All,

1st, happy new year for all of you.

I have 2 tables, X and Z.
In real life, X contains one numerical field/coloumn that is the sum of 900 numerical inputs/records from table Z.

My question is, when designing the table X, should i include a feild/coloumn that contains the sum of the inputs/records in Y, or not, little bit confused Confused | :confused:

Regards...

0 will always beats the 1.

Hiren solanki3-Jan-11 23:31

Hiren solanki

3-Jan-11 23:31

scorp_scorp wrote:
My question is, when designing the table X, should i include a feild/coloumn that contains the sum of the inputs/records in Y, or not, little bit confused

There's one option available of computed column in SQL Server, That's just giving you column value by applying formula you've provided while creating that column rather then storing physical data of SUM.

Regards,
Hiren.

My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Building Hierarchy using Recursive CTE

scorp_scorp3-Jan-11 23:36

3-Jan-11 23:36

Thanks for the quick reply hiren, but, what i mean, is, if it is a good practice from a design point of view. Since some told me that i shoudnt, since i can get the sum in a report, query, or in a view, any time and then, i shouldnt save it in a table.

What do u think??

regards,

0 will always beats the 1.

Hiren solanki3-Jan-11 23:40

Hiren solanki

3-Jan-11 23:40

Storing it physically though same data you can get that data anytime by querying isn't a good practice at all.

But What I mean is Computed column is invented for that purpose only it just computes value provided and gives you final answer in a column looks like it's physically storing data, but it isn't it's just a logical.

Regards,
Hiren.

My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Building Hierarchy using Recursive CTE

scorp_scorp3-Jan-11 23:42

3-Jan-11 23:42

Hiren Solanki wrote:
a column looks like it's physically storing data, but it isn't it's just a logical.

Got the point, thanks a lot, very helpfull.

Regards,

0 will always beats the 1.

jschell4-Jan-11 8:25

jschell

4-Jan-11 8:25

Hiren Solanki wrote:
Storing it physically though same data you can get that data anytime by querying isn't a good practice at all.

That isn't true - it depends on explicit and implicit business needs.

One consideration is volume and the load on the database. If summaries are common and significant in volume while being exactly the same summary (such as daily totals) then summing them once and then reserving that value can reduce load.

Another consideration is historical retention. It might not be possible or might be too complex to attempt to retain complex calculations which have inputs that vary over time. So the calculation is done at a point in time that is valid and then retained for later use to insure that reproducing reports (receipts or whatever) will still produce the same value.

Eddy Vluggen4-Jan-11 0:31

Eddy Vluggen

4-Jan-11 0:31

As Hiren noted, a computed column would be a good solution. That would be re-evaluated every time you fetch the records, adding a performance-penalty. I'd go for a column in table X that would get updated with a trigger on table Z. That way the server only has to recount the new records once. The downside is that this would add more complexity than a computed column.

I are Troll Suspicious | :suss:

PIEBALDconsult4-Jan-11 2:23

PIEBALDconsult

4-Jan-11 2:23

A summary table is a good solution when the calculations are very complex or you delete/archive the data. Is that what you're doing?

jschell4-Jan-11 8:27

jschell

4-Jan-11 8:27

scorp_scorp wrote:
In real life, X contains one numerical field/coloumn that is the sum of 900 numerical inputs/records from table Z.

Does table Z have 900 rows or 900 billion?
Is table Z used in other queries that run once a day or 1000 times a second?

scorp_scorp9-Jan-11 21:06

Index with two columns of table

9-Jan-11 21:06

for each record in X, the amount of numerical feild correspond to sum of records "not more than 900 record" in Z. ie: table Z has more than 900 records (unlimited), but the sum in X is limited to a max number of 900 in Z.
Table Z is subjected to quireis, that accour not more than once a day.

Regards,

0 will always beats the 1.

error in sql

mandarapu3-Jan-11 21:14

mandarapu

3-Jan-11 21:14

hai,

i had wrote one storedprocedure for deleting the current leaves.but when i am debugging i am getting the error as invalid object name.i am posting the code.

USE [HDCHRMSDB]
GO
/****** Object: StoredProcedure [dbo].[apDeleteCurrentYearLeaveSummery] Script Date: 01/04/2011 12:23:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[apDeleteCurrentYearLeaveSummery]
As
BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;
DELETE FROM CurrentYearLeaveSummery Where Year=2011

END

please suggest any answer to avoid that error.urgent for me.

Re: error in sql

Bernhard Hiller3-Jan-11 23:14

Bernhard Hiller

3-Jan-11 23:14

"Year" could be a reserved word.
If you have a column with that name, use [Year] instead.
Otherwise, it is a function, i.e. Year([My Column]).

Re: error in sql

Hiren solanki3-Jan-11 23:28

Hiren solanki

3-Jan-11 23:28

First of all it's a Repost[^].

I think answer you've got of adding dbo.[name of table] may solve your problem.

Rather then posting code in different forum Discuss with people answered you over there.

Regards,
Hiren.

My Recent Article: - Way to know which control have raised a postback
My Recent Tip/Trick: - Building Hierarchy using Recursive CTE

vanikanc3-Jan-11 4:05

vanikanc

3-Jan-11 4:05

Hello,

An index is created with two not null columns of table. The index created is a unique non-clustered index.

When trying to re-index this field, it takes a couple of hours. Also, do indexes work if there are two columns defined in a index?

Thank you and Happy New Year!

Re: Index with two columns of table

Corporal Agarn3-Jan-11 5:13

Corporal Agarn

3-Jan-11 5:13

How big is your table?

Yes an index on two columns do work.

lack of index in mysql document

followait31-Dec-10 16:50

followait

31-Dec-10 16:50

With out index, it become not that efficient when finding help.
The TOC (or bookmarks in pdf) is not so efficient.
What worse is html, I have to return to TOC to relocate.

BTW, index is removed by default in visual studio 2010.

Why index is gone?

Join and Update statement using junction table and sql

jboyd11130-Dec-10 10:19

Re: Join and Update statement using junction table and sql

30-Dec-10 10:19

Hi, I've searched and haven't found a relevant answer to this 'simple' problem.

I have 3 tables, one of which is a junction table between the other two:

Users
-----
UserId
UserName

Roles
-----
RoleId
RoleName

UserRoles
---------
UserRoleId
UserId
RoleId

There are FK relationships between UserRoles and the other 2 tables.

Problem statement:
What I want to do is update the user's RoleId in the UserRoles table if I only have the person's RoleName and UserName.

Here's what I'm starting with code-wise (MSSQL):

<br />
				update a<br />
				set a.RoleId  = ?? Set to what?<br />
				FROM UserRoles a<br />
				inner join Users b on a.UserId = b.UserId<br />
				inner join Roles c on a.RoleId=c.RoleId<br />
where c.RoleName= 'Administrator' AND b.UserName='someuser'<br />

Further information:
I'm trying to do this within an update method call within an ASP.NET GridView control. This information may be a bit more than what's needed but I thought I would mention it just in case it may offer a different solution.
Thanks for any assistance in advance.

---
Stan

Luc Pattyn30-Dec-10 10:34

Luc Pattyn

30-Dec-10 10:34

Sorry, I'm missing the logic in your question.

This is what I read:
we know the UserName (and hence the UserId)
we know the RoleName (and hence the RoleId)

I see two possibilities:
1. the UserRoles table does not contain a matching UserId,RoleId record; you have to insert one.
2. the UserRoles table does contain a matching UserId,RoleId record. Now what needs to be updated???

Or is it you want the existing UserId,RoleId record to get another value of UserRoleId for some mysterious reason?

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Season's Greetings to all CPians.

Re: Join and Update statement using junction table and sql

jboyd11130-Dec-10 16:06

Re: Join and Update statement using junction table and sql

30-Dec-10 16:06

Your last point is what I'd like to find an answer for: I want to UPDATE the RoleId for an EXISTING RoleId,UserId combo.
In short, I want to update the junction table.

Luc Pattyn30-Dec-10 23:24

Luc Pattyn

30-Dec-10 23:24

You failed to make it any clearer. This is my only gamble at what it is you might possibly want (not tested!):

update a
set a.RoleId = d.RoleId
FROM UserRoles a
inner join Users b on a.UserId = b.UserId
inner join Roles c on a.RoleId = c.RoleId
inner join Roles d
where c.RoleName= 'Administrator' AND b.UserName='someuser' AND d.RoleName='AnotherRole'

Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum

Season's Greetings to all CPians.

modified on Friday, December 31, 2010 5:39 AM

Re: Join and Update statement using junction table and sql

jboyd1113-Jan-11 8:50

Re: Join and Update statement using junction table and sql

3-Jan-11 8:50

Thanks for your help. I apologize I hadn't been able to properly characterize my problem. In short: I want to update a junction table (RoleId and UserId) when I only know the UserName and RoleName.
I'll play around with variations of what you provided and search a bit further online. I'll post whatever resolution(s) I come up with.

Jörgen Andersson3-Jan-11 23:36

Jörgen Andersson

3-Jan-11 23:36

I think you're thinking a bit wrong here (Alternatively have I understood you wrong).
There's no real point in updating the UserRoles table. You want to add or remove roles a user have. There's nothing to update really.

So you should rather think along the line of Insert and Delete instead.
For example something similar to:

Insert Into UserRoles (UserID,RoleID)
Values ((Select Userid from Users Where UserName = 'someuser'),(Select RoleID from Roles Where Rolename = 'Administrator'))

Or:

Delete From UserRoles
Where Userid = (Select Userid from Users Where UserName = 'someuser')
And Roleid = (Select RoleID from Roles Where Rolename = 'Administrator')

"When did ignorance become a point of view" - Dilbert

Re: Join and Update statement using junction table and sql

jboyd1115-Jan-11 11:13