|
what is merge and hash join ?
if anyone know and use it then please share the knowledge.
when one should go for merge and hash join.
if possible discuss it with a good example the significance of merge and hash join.
also tell me how merge and hash join is different from normal join
thanks
tbhattacharjee
|
|
|
|
|
|
CHill60 wrote: Google is a wonderful resource
OP is pathologically averse to using Google. Maybe it's a rude word in his language.
|
|
|
|
|
I see what you mean 
|
|
|
|
|
searching google for merge join in sql server brings lots of result but i like to know when it is good or should i do it when joining one key is pk or other key is fk or regular key.
in this kind of scenario merge join in sql server will be good.
share your idea if you know. thanks
tbhattacharjee
|
|
|
|
|
This article (from the first page of the Google search I posted) sums it up pretty well
Understanding SQL Server Physical Joins[^]
I draw your attention to the statement at the end of the article
Quote: SQL Server does a pretty good job in deciding which join operator to use in each condition. Understanding these condition helps you to understand what can be done in performance tuning. It's not recommended to use join hints (using OPTION clause) to force SQL Server to use a specific join operator (unless you have no other way out), but rather you can use other means like updating statistics, creating indexes or re-writing your query.
Given the way you have worded your question I would just stick to using normal join syntax and allow SQL Server to do the decisions on how they are physically implemented.
These resources may be helpful:
SQL Server Join Example[^]
Visual Representation of SQL Joins[^]
|
|
|
|
|
Tridip Bhattacharjee wrote: searching google for merge join in sql server brings lots of result but i like to know when it is good or should i do it when joining one key is pk or other key is fk or regular key.
Try reading the first pages that come back as a result 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi All,
I have an application which reads all the Jobs and its related packages which failed, but its reading only those Packages which are executed as part of the Jobs. Is there any way that I can check all the Packages that are failed like the ones that are executed externally like from an Application or Manually etc.
The code to get the failed packages that are part of the SQL Agent Jobs:
DECLARE @PreviousDate datetime<br />
DECLARE <a href="https://www.codeproject.com/Members/Year">@Year</a> VARCHAR(4)<br />
DECLARE <a href="https://www.codeproject.com/Members/month">@Month</a> VARCHAR(2)<br />
DECLARE @MonthPre VARCHAR(2)<br />
DECLARE <a href="https://www.codeproject.com/Members/Day">@Day</a> VARCHAR(2)<br />
DECLARE @DayPre VARCHAR(2)<br />
DECLARE @FinalDate INT
-- Initialize Variables<br />
SET @PreviousDate = DATEADD(dd, -1, GETDATE()) -- Last 1 day<br />
SET <a href="https://www.codeproject.com/Members/Year">@Year</a> = DATEPART(yyyy, @PreviousDate)<br />
SELECT @MonthPre = CONVERT(VARCHAR(2), DATEPART(mm, @PreviousDate))<br />
SELECT <a href="https://www.codeproject.com/Members/month">@Month</a> = RIGHT(CONVERT(VARCHAR, (@MonthPre + 1000000000)),2)<br />
SELECT @DayPre = CONVERT(VARCHAR(2), DATEPART(dd, @PreviousDate))<br />
SELECT <a href="https://www.codeproject.com/Members/Day">@Day</a> = RIGHT(CONVERT(VARCHAR, (@DayPre + 1000000000)),2)<br />
SET @FinalDate = CAST(@Year + <a href="https://www.codeproject.com/Members/month">@Month</a> + <a href="https://www.codeproject.com/Members/Day">@Day</a> AS INT)
-- Final Logic
SELECT h.instance_id, j.[name],
s.step_name,
h.step_id,
h.step_name,
h.run_date,
h.run_time,
h.sql_severity,
h.message,
h.server
FROM msdb.dbo.sysjobhistory h
INNER JOIN msdb.dbo.sysjobs j
ON h.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
AND h.step_id = s.step_id
WHERE h.run_status = 0 -- Failure
AND h.run_date > @FinalDate
ORDER BY h.instance_id DESC
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
-----------------------------------DATABASE-------------------------------------------------------------
CREATE TABLE ACCOUNTS(ACCNO INT PRIMARY KEY,BAL PRIMARY KEY);
insert into accounts values(77,10000);
insert into accounts values(88,10000);
---------------------------------HERE IS JDBC CODE----------------------------------------------------
import A.JDBCUtil; // ****JDBCUtil is the class having connection code
import java.sql.*;
public class Test12 {
public static void main(String[] args) {
Account acc=new Account();
acc.transfer(77,88,5000); //******LINE 8******
}
}
class InvalidAccountNumberException extends Exception{
int accno;
public InvalidAccountNumberException() {
}
public InvalidAccountNumberException(int accno){
this.accno=accno;
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "Accno"+accno+"not found";
}
}
class InsufficientFundsException extends Exception{
public InsufficientFundsException() {
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "sufficent funds are not available";
}
}
class Account{
int bal;
int sab,dab,danb,sanb;
public void transfer(int sa,int da,int amt){
Connection con=null;
PreparedStatement ps1=null;
PreparedStatement ps2=null;
try{
con=JDBCUtil.getOracleConnection();
con.setAutoCommit(false);
ps1=con.prepareStatement("select bal from accounts where accno='?'");
ps1.setInt(1,da); ******//LINE 12******
ResultSet rs1=ps1.executeQuery();
if(rs1.next())
dab=rs1.getInt(1);
else
throw new InvalidAccountNumberException(da);
danb=dab+amt;
//updating destination account balance
ps2=con.prepareStatement("update accounts set bal='?' where accno='?' ");
ps2.setInt(1,danb);
ps2.setInt(2,da);
ps2.executeUpdate();
System.out.println("**"+da+"*updated*");
//checking source account
ps1.setInt(1,sa);
rs1=ps1.executeQuery();
if(rs1.next()){
sanb=rs1.getInt(1);
}else{
throw new InvalidAccountNumberException();
}
if(sab>=amt){
sanb=sab-amt;
}else{
throw new InsufficientFundsException();
}
//updating Source account
ps2.setInt(1,sanb);
ps2.setInt(2,sa);
ps2.executeUpdate();
con.commit();
System.out.println("**"+sa+"updated");
System.out.println("funds transferred");
}catch(Exception e){
e.printStackTrace();
try{
con.rollback();
}catch(Exception e1){
}
}finally{
JDBCUtil.cleanup(ps1, con);
JDBCUtil.cleanup(ps2, con);
}
}//transfer
}//account
|
|
|
|
|
Where is line 8, where is line 12?
|
|
|
|
|
CHECK THE CODE I HAVE WRITTEN THE LINE NO ON THE CODE
|
|
|
|
|
-----------------------------------DATABASE-------------------------------------------------------------
CREATE TABLE ACCOUNTS(ACCNO INT PRIMARY KEY,BAL PRIMARY KEY);
insert into accounts values(77,10000);
insert into accounts values(88,10000);
---------------------------------HERE IS JDBC CODE----------------------------------------------------
import A.JDBCUtil; // ****JDBCUtil is the class having connection code
import java.sql.*;
public class Test12 {
public static void main(String[] args) {
Account acc=new Account();
acc.transfer(77,88,5000); //******LINE 8******
}
}
class InvalidAccountNumberException extends Exception{
int accno;
public InvalidAccountNumberException() {
}
public InvalidAccountNumberException(int accno){
this.accno=accno;
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "Accno"+accno+"not found";
}
}
class InsufficientFundsException extends Exception{
public InsufficientFundsException() {
}
<a href="https://www.codeproject.com/Members/OverRide">@Override</a>
public String toString() {
return "sufficent funds are not available";
}
}
class Account{
int bal;
int sab,dab,danb,sanb;
public void transfer(int sa,int da,int amt){
Connection con=null;
PreparedStatement ps1=null;
PreparedStatement ps2=null;
try{
con=JDBCUtil.getOracleConnection();
con.setAutoCommit(false);
ps1=con.prepareStatement("select bal from accounts where accno='?'");
ps1.setInt(1,da); //**************//LINE 12******************//
ResultSet rs1=ps1.executeQuery();
if(rs1.next())
dab=rs1.getInt(1);
else
throw new InvalidAccountNumberException(da);
danb=dab+amt;
//updating destination account balance
ps2=con.prepareStatement("update accounts set bal='?' where accno='?' ");
ps2.setInt(1,danb);
ps2.setInt(2,da);
ps2.executeUpdate();
System.out.println("**"+da+"*updated*");
//checking source account
ps1.setInt(1,sa);
rs1=ps1.executeQuery();
if(rs1.next()){
sanb=rs1.getInt(1);
}else{
throw new InvalidAccountNumberException();
}
if(sab>=amt){
sanb=sab-amt;
}else{
throw new InsufficientFundsException();
}
//updating Source account
ps2.setInt(1,sanb);
ps2.setInt(2,sa);
ps2.executeUpdate();
con.commit();
System.out.println("**"+sa+"updated");
System.out.println("funds transferred");
}catch(Exception e){
e.printStackTrace();
try{
con.rollback();
}catch(Exception e1){
}
}finally{
JDBCUtil.cleanup(ps1, con);
JDBCUtil.cleanup(ps2, con);
}
}//transfer
}//account
|
|
|
|
|
I cannot see anything wrong with your parameter indices, unless something else is corrupting those values. You need to use your debugger to step through the code and see exactly what is happening.
|
|
|
|
|
I am looking to create a tool for my office to simplify the storing of certain logs. Right now it is done via excel and during weekly calls the staff needs to organize this and distribute to recipients, update daily, etc.
I am thinking (thinking) if I went to a Database application I could write a better means to store the information and more importantly add some code to have a button (for example) to email all of the reports to anyone in the database flagged as a recipient.
This is just one small first step of functionality as I aim to overtime add more and more slightly to it.
I am thinking that I could put a template of the database in each project folder and have people just manage it that way so I dont have to worry about too many concurrent users. It also simplifies the location of the storage vs a central place everyone gets to it (which means they forget since they are in the project folders all the time).
Alternative, is I just start the endeavor of writing a true .net application on top of the database. Although I am thinking this is more future proof as I honestly dont know where this would feature wise I am thinking it would be way more complex and lengthy on development time.
So how do you weigh where to build the application? I can probably knock out basic functionality in access relatively quickly, but am I limiting myself?
|
|
|
|
|
If you NEVER expect to consolidate the data then Access on each client is a viable option.
However I would start with SQL Server express and a Winforms/WPF client application.
Access will give you a quick and dirty solution with a minimal learning curve. You will run into limitations fairly quickly, especially when you decide you need to consolidate the data for whole of company reporting.
SQL/Client will have quite a steep learning curve, however it is a professional environment and you can plug in a professional developer when you reach you limitations.
This is an ideal project for a junior developer just starting out, they get to learn the skills of analyst and application design and you get to manage and learn the development skills.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the response. The combination of data is not a big deal as I could always recreate a unified solution later. It never would really hurt me. I am just torn on how useful a standalone DB will be. If not useful, it won't get buy in.
I am sure this may not be well received in these forums but I have been researching Mendix a lot. Model based development takes the technicalities out of it. Any thoughts on that? Seems like it could be a good route to go.
|
|
|
|
|
Sorry I know nothing about Mendix but it looks like a web development suite. I would look into how mature it is and peruse their support forum to see how responsive they are to problems.
[edit]
As a caveat to using custom IDEs look at this question then look a the last release date for an update of the product he is using.
How to set up QuickSharp to run program in separate window? - C# Discussion Boards
Never underestimate the power of human stupidity
RAH
modified 12-Mar-17 20:39pm.
|
|
|
|
|
Hi All,
I want some help in getting all the steps which are either disabled or failed along with their job information of SQL Server, any help is going to be very helpful.
Thanks in advance.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
indian143 wrote: getting all the steps which are either disabled or failed along with their job information of SQL Server,
Get how? You can go look at them through the UI. Do you want sql to query for them? C# to run a report? Not sure what you want.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
Hi,
I need to get all the Packages that are run either by using Job or without using the Jobs, I need the statuses of those Packages, whether they are failed or success.
This is because I have a console application that's executing all the Packages one after the other, but if the Packages failed internally we are not informed of that, so I want to go ahead and check status of the Package after execution using query and find out what has happened.
Any help would be greatly helpful. Thanks in advance. I want to get all the packages that are executed either by Job or without using the Job on a database.
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
create or replace procedure updateStudentResult(id int,
m1 in int,
m2 in int,
m3 in int,
tot out int,
avg out float,
grd out char)
as
begin
select marks1,marks2,marks3 into m1,m2,m3 from students where sid=id;
tot:=m1+m2+m3;
avg:=(m1+m2+m3)/3;
if avg >= 85 then
grd:='A';
elsif (avg >= 65) then
grd:='B';
elsif (avg >= 50) then
grd:='C';
else
grd:='D';
end if;
UPDATE students SET
total=tot,
average=avg,
grade=grd
where sid=id;
end;
/
|
|
|
|
|
Do you have a question or are you just trying to showcase your code?
|
|
|
|
|
|
Fix the compilation error that it tells you about.
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
It looks like you downvoted my post and that's fine; however, the reason I said to fix the compilation error is because you haven't even shared with us what the exact error is, so how can we possibly help you fix it?
There are two kinds of people in the world: those who can extrapolate from incomplete data.
There are only 10 types of people in the world, those who understand binary and those who don't.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
