Database

Re: two phase commit in sql

24-Mar-13 0:47

If I'm not mistaken you have the fifth normal form here. I see nothing wrong in that.

"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull

Re: DB Desing Check

Mycroft Holmes24-Mar-13 2:19

Mycroft Holmes

24-Mar-13 2:19

I'm hunting around for a negative comment - nope can't find one, oh wait underscores, hate underscores. Design is fine.

Never underestimate the power of human stupidity
RAH

Re: DB Desing Check

Richard MacCutchan24-Mar-13 2:29

Richard MacCutchan

24-Mar-13 2:29

Yes, very true.

Use the best guess

SSRS

engine123-Mar-13 1:35

engine1

23-Mar-13 1:35

I'm studying SSRS and due to situations have limited time to study. Please suggest what all topics i should be focusing on so as to crack an interview & also utilize it in new job.
Thanks.

PS: If my question seems too generic, still answer as per u're understanding.

Re: SSRS

Richard MacCutchan23-Mar-13 1:37

Richard MacCutchan

23-Mar-13 1:37

See here[^]; although you will be lucky to pass an interview without some practical experience.

Use the best guess

two phase commit in sql

Soiraian22-Mar-13 23:22

Soiraian

22-Mar-13 23:22

how two phase commit is handled in sql server ?
and what is the difference between two phase commit in sql and Oracle??

please help me

Mycroft Holmes23-Mar-13 13:36

Mycroft Holmes

23-Mar-13 13:36

You need some google foo[^]. I will be interested to see if you get anyone experienced with this solution responding!

Never underestimate the power of human stupidity
RAH

OLAP cube question (a) Edit Dimension and (b) Delete Partitiion

devvvy21-Mar-13 16:07

devvvy

21-Mar-13 16:07

hello two newbie questions for OLAP. I'm looking at my cube from Visual Studio ...

(a) Edit Dimension
I want to basically add a new Dimension level - under Design view of "Dimension Structure" ...
I can see Data Source View tabs containing two existing tables which make up my original dimension. Trouble is, how can I add the third? Can I add a table for this dimension?

(b) Delete Partitiion
I can find from under Cube's Design view there's one tab "Partitions" - and I can delete partition manually one-by-one right click + select "Delete" from Context Menu. Is there a quicker way of doing this?

Thanks

dev

Want a query for for finding total number of non defected items.

VishwaKL19-Mar-13 23:07

VishwaKL

19-Mar-13 23:07

Hi guys,

I have 2 tables Table1,Table2

Table1 having products and serial number information, and Table2 having set of 32 check points to say that product having defect or not, i want to get only total number of non defected products for each month,

say example i have 3 products in march month and only 2 products satisfy set of 32 check points and having non defect, so for this problem result will be Total Number of non defected product will be 2. i want this in query.

I tried it but it giving each product defect count, i want only total number of non defect product

here is my query

SQL

WITH months (num) as(
		SELECT 1
		UNION ALL 
		SELECT num+1
		FROM months
		WHERE num<12)SELECT m.num as Month,
							--isnull(t.count,0) as Total,
							isnull(t.resolved,0) as Totalresolved,  
							t.YearValue as YearData FROM months m
					left join (SELECT month(ProgramStartedDate) as Month,YEAR(ProgramStartedDate) as YearValue,
								--count(*) as count,
								
								
								
								sum(case when (ProgrammerStatus = 1 OR ReviewerStatus = 1)  THEN 1 ELSE 0 END)as resolved  
								FROM tb_ProgramCheckList 
								LEFT JOIN dbo.tb_ProgramDetails on 
								tb_ProgramDetails.ProgramID =tb_ProgramCheckList.ProgramID AND tb_ProgramDetails.Version=tb_ProgramCheckList.VersionID
							   WHERE ProgramStartedDate >='10/1/2012' 
								and ProgramStartedDate <'10/01/2013' --AND month(ProgramStartedDate)=m.num  
								GROUP BY month(ProgramStartedDate),YEAR(ProgramStartedDate)

If any body help me to solve this issue will be appreciated,

Thanks and regards

VISHWA

Re: Want a query for for finding total number of non defected items.

Corporal Agarn20-Mar-13 1:03

Corporal Agarn

20-Mar-13 1:03

Does table 2 have 32 bit columns or two columns with one record per check?

Re: Want a query for for finding total number of non defected items.

VishwaKL20-Mar-13 18:05

VishwaKL

20-Mar-13 18:05

each program having 32 bit checks to prove the item is defect or not

Re: Want a query for for finding total number of non defected items.

Mycroft Holmes20-Mar-13 18:58

Mycroft Holmes

20-Mar-13 18:58

Great design - what happens when you want to add a new check!

You need to add the values of the 32 checks (presumably booleans where 1= positive) if it = 32 then the program passes andything els the program fails.

SQL

Select *
From (Select C1 + C2+C3+... as checktotal, Program from table) as SubQuery
where checktotal = 32

Never underestimate the power of human stupidity
RAH

MS Sql normalization

Per Söderlund19-Mar-13 21:40

19-Mar-13 21:40

We have a production table with a column called sheets.
When we are making the final product in our production, sheets will be stored in another table called control_measures.

This means we will only store sheets in production when we are making the final product.

I want to know what you guys think.
Should I normalize and move sheets to a new table related to production or should i make it nullable as it is?

I know the recommended way of doing it in relational databases is normalization.
But still, I´m curious to know what you guys have to say about this.

Jörgen Andersson20-Mar-13 0:14

20-Mar-13 0:14

If you were creating the tables at the moment I would certainly normalize it, that makes it easier to make changes in the future.
If this is the current design and it works fine, I wouldn't touch it.

"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull

Per Söderlund20-Mar-13 0:20

20-Mar-13 0:20

So you prefer to normalize even if it is just one column?
Whats wrong with a nullable column?

And yes, this is the current design, i´m expanding.

Eddy Vluggen20-Mar-13 1:38

Eddy Vluggen

20-Mar-13 1:38

Söderlund wrote:
So you prefer to normalize even if it is just one column?

Whats wrong with a nullable column?

A nullable column is not dependent on the key it's linked to, while every atomic fact in the record should depend on the key. One can split the field of to it's own table with it's own identifying key. That's theoretically beautiful.

If you were to implement the "beautiful" method, you'd end up with an extra table and an extra join.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Jörgen Andersson20-Mar-13 2:34

20-Mar-13 2:34

The proper answer you got from Eddy.

Mine is that it depends, I'd rather normalize once to many than once to few.

As I don't know anything about your domain, I also don't know if there are changes to your database to expect.
But it's also about performance, most of the time (not always) normalization boosts performance in contrary to popular belief. The most obvious exception is OLAP.
Here's an excellent article[^] on that subject.

Whether or not nulls is a performance hit or not also depends on what database you're using, Oracle for example isn't ISO compliant in this matter and doesn't store NULL values at all, the lack of a value is the NULL value.
SQL Server on the other hand stores a NULL token that takes two bytes for variable length data and the full space for fixed length data. So if you have a column with a high percentage of nulls you get a hit on memory compared to a separate table. If there's a low percentage of nulls you can keep it in the original table.
And then again, SQL Server nowadays have SPARSE Columns. Can't say for sure how well it works as I have never used them. But at least in theory they should have fixed the problem, but give you an extra join for the null bitmap.

"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull

Per Söderlund20-Mar-13 3:01

20-Mar-13 3:01

I´m kind of doing science here.
No one has done this kind of system before and the company i´m working for have been turned down by programming firms doing production systems.

To prepare database against future changes i would have to normalize every column since no one knows what or how things works until we have tested it.

neither memory nor performance is an issue since we are talking about 6-10k records a year.

Jörgen Andersson20-Mar-13 4:07

20-Mar-13 4:07

Söderlund wrote:
To prepare database against future changes i would have to normalize every
column

Within reason, If you know your domain you can make a qualified guess as to were there will be changes.

Remember that there is a disadvantage with normalizing all the way. Your CRUD operations will become complicated.

There's a saying: Normalize 'til it hurts, denormalize 'til it works.

"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull

Per Söderlund20-Mar-13 4:38

20-Mar-13 4:38

Indeed, I can guess and i have.
However, I could not foresee this change.

I´m split because on one hand i have "normalization is the way to go"
and on the other hand i have "code that works".

At the moment i will keep a nullable column and when it works i will look at normalization and how that will affect current code.
I also believe it will be easier for future coders if i follow the standard guidelines.

Jörgen Andersson20-Mar-13 4:45

20-Mar-13 4:45

"Code that works" always trumps change "because it's the correct way of doing it".

Söderlund wrote:
if i follow the standard guidelines

Whos guidelines are those?

Make your own guidelines instead, they're easier to follow

"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull

Per Söderlund20-Mar-13 11:51

20-Mar-13 11:51

Jörgen Andersson wrote:
Whos guidelines are those?

That´s what a friend was fed from school (I´m not schooled).
So I Assumed it was standard, mostly because it makes sense.

Not that i trust the school since they had a web developer program with a C# winform ball game exam and didnt touch php at all.

Jörgen Andersson20-Mar-13 21:24

20-Mar-13 21:24

I would trust the school a lot less if they taught PHP.

Schools shouldn't teach languages, they should teach programming.
C# in contrary to PHP enforces a lot of good habits.
Not that you can't program properly in PHP, you certainly can.

But this is a subject that others are much better at answering then I am.
The best place to ask about this subject is probably the Lounge, but make damn sure it's not formed as a programming question but rather a discussion subject, or you might be well fried.

"The ones who care enough to do it right care too much to compromise."
Matthew Faithfull

Per Söderlund22-Mar-13 10:48