Query That Selects All Mis Key That Is Not In Subquery?
Jul 9, 2014
I am trying to write a query that selects all the mis_key that is not in the subquery. The left join query is working but when nested it comes out blank. Here is the query:
select *
from [TLC NEW Inv. Anal.].dbo.['Home Decor$']
WHERE NOT EXISTS
(SELECT *
FROM [TLC New].DBO.['Fabric or basket accessory$'] LEFT JOIN [TLC NEW Inv. Anal.].DBO.['Home Decor$']
ON [TLC New].DBO.['Fabric or basket accessory$'].[Item #]=[TLC NEW Inv. Anal.].DBO.['Home Decor$'].mis_key)
Hello I'm trying to figure out how to write this query...for example,
A stud attended University of Toronto in 2001 and then attended Seneca College in 2006 ..... I want to select the Education Institute that the stud attended last.
I want to get a column count several times in one query using differentfilters but can't work out how to do it - can anyone point me in the rightdirection?For example, how would combine these two selects into one query that willlist the total and filtered actions:SELECT COUNT(actions) as actioncount, locationFROM mytableGROUP BY locationSELECT COUNT(actions) as actioncount, locationFROM mytableWHERE mycondition IS NULLGROUP BY location
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar, RDR1.U_Variety, (SELECT OITM.U_Variety FROM OWOR INNER JOIN WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN OITM INNER JOIN OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity, OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet, OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode FROM OWOR AS t0 INNER JOIN ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode WHERE (t0.Status <> 'L')
I did not get any reply for my previous post. So i am just trying to make my doubt clear.
I have a subquery within a distributed query.
Eg:
SELECT T1.deptID FROM SERVER1.ACCOUNT.DBO.DEPT as T1 where deptid IN (SELECT T2.DEPTID FROM SERVER2.DEPARTMENT.DBO.DEPT as T2 WHERE T1.DIVISIONID = T2.DIVISIONID)
In the above query.. T1.DIVISIONID is not recognoized and it says T1 is not a table or alias name though I have declared it upfront.
You have to note that both the ACCOUNT and the DEPARTMENT database are on different servers.
If they are on the same servers, this should not be a problem at all. Also, all the security is working perfectly for running a distributed query for me.
do you know if I can do the following task through a single query
TableA(LocID,LocNAME) TableB(ID,LocID,Amount)
What i need to do is to add sum amount having same locID from TableB and get LocIDs name through TableA.LocName. In the query there should be one thing more, if amount is less than zero put it into credit column, while if positive, puts in debit column
Thus Result(LocId, LocName, Debit, Credit) is the requied structure. Can anyone help me out. I m not getting how to get the LocName if gets the sum by Groupby LocID also applying condition is confusing me:s
Hi,I have one table name: artcolumn:symbol_art price1 price2----------- ------- -------AG-0001 20 40AG-0001S null nullAG-0002 40 60AG-0002S null null....How paste in null price1 and price2 from oryginal symbol_art AG-0001,AG-0002 ?(duplicate symbol_art %-%'S ' it's always the same for oryginal symbol_art)thanks for any helpTom
Hi.I have an insert query which inserts record that are returned from a select subquery: INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE... col1 and col2 in tbl1 combined ,are a unique index. So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1. The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?
HiI migrate my database from Access to MS SQL Server 2005 and I have problem with one query : SELECT DISTINCT (HeadYarns_Tbl.HeadYarnsID) AS HYID, HeadYarns_Tbl.YarnType, (select max(YarnType_Tbl.Denier) from YarnType_Tbl where HeadYarns_Tbl.PrimYarnTypeID = YarnType_Tbl.YarnTypeID) AS denier1, (select max(YarnType_Tbl.Denier) from YarnType_Tbl where HeadYarns_Tbl.secYarnTypeID = YarnType_Tbl.YarnTypeID) AS denier2, HeadYarns_Tbl.YarnType & Denier1 & '/' & Denier2 AS Yarninfo2, HeadYarns_Tbl.YarnType & Denier1 AS Yarninfo FROM YarnType_Tbl, HeadYarns_Tbl INNER JOIN HeadLayout_Tbl ON HeadYarns_Tbl.HeadYarnsID = HeadLayout_Tbl.HeadYarnsID ORDER BY HeadYarns_Tbl.YarnTypeIn Access It works good, but in MS SQL Server I don't know how I can use aliases in query. (bold and underline code )please help. regards Michael
I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)
Example
table1 id - autonumeric id_user - id from user dates - date of register
table2 id - user id name - user name
query1 SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user FROM Table1 WHERE (((Table1.datess) Between [begining] And [ending])) GROUP BY Table1.id_user ORDER BY Table1.id_user;
subquery (query2) SELECT query1.id_user, query1.CuentaDeid_user, Table2.name FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;
This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?
Hi. I have an insert query which inserts record that are rturned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint). How can I solve this ?
I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query.
Here's my query:
UPDATE temp_UpdateRemainingHours SET UsedHours =
(SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN dbo.temp_UpdateRemainingHours ON dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD)
FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_UpdateRemainingHours
WHERE dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
I have a query where I need to select a bunch of rows from one table, hypothetically we'll call them ssn, first name, last name, and I need to select a subquery which coalesces a bunch of rows together (in no case will there be only one row returned from that subquery).
Anyone know how I could go about this? I'll give you an example of what I've tried, but it does not work currently.
delcare @path varchar(255) select e.ssn,
e.firstname, e.lastname, ( @path = select coalesce(@path + ', ', '') from space s1 inner join space s2
on s1.lft BETWEEN s2.lft AND s2.rgt and s1.rgt BETWEEN s2.lft AND s2.rgt where s1.spaceID = 133225 select @path) from employees e where e.id = 5
Using that spaceID is guaranteed to give me four rows, and I need them coalesced together, but I can't just use a function (too slow on the scale it would be used), any thoughts?
I am using SQL Server 2000. I have a somewhat large query and hope someone could help me with it. (Man, there needs to be a way to use colors...) The bolded parts of the query need to be replaced by the underlined part. I can't say 'b.HW' because of the scope of the inline query and that they are all on the same level. I've been told I need to change to a nested subquery, but can't for the life of me figure out how to do that. Can someone please show me? The current query: SELECT x.SSN,x.RealName,x.BudgetCode,x.TH,b.HW,x.HP,z.HL,x.NHW,x.FSLAOT,y.HHW AS AH,y.NHH,CASE WHEN x.TH < x.HP THEN 'XX' WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH WHEN x.TH <= x.HP THEN 0 END AS SOT,CASE WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH + x.FSLAOT WHEN x.TH <= x.HP THEN 0 END AS AO FROM(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS TH,CASE WHEN SUM(a.hours) > 40 THEN (SUM(a.hours) - 40) * 1.5 WHEN SUM(a.hours) <= 40 THEN 0 END AS FSLAOT,CASE WHEN SUM(a.hours) >= 40 THEN 40 WHEN SUM(a.hours) < 40 THEN SUM(a.hours) END AS NHW,32 AS HP FROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.ScheduleType = 1 AND a.EmployeeType = 2GROUP BY a.SSN, a.RealName, a.BudgetCode) x LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HHW,CASE WHEN SUM(a.Hours) >= 8 THEN 8 WHEN SUM(a.Hours) < 8 THEN SUM(a.Hours) END AS NHH FROM dbo.ActivitiesInCurrentFiscalYear a, dbo.Holidays hWHERE a.ItemDate = h.HolidayDate AND a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1GROUP BY a.SSN, a.RealName, a.BudgetCode) y ON x.SSN = y.SSN AND x.RealName = y.RealName AND x.BudgetCode = y.BudgetCode LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HLFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) z ON x.SSN = z.SSN AND x.RealName = z.RealName AND x.BudgetCode = z.BudgetCode LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HWFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND NOT (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) b ON x.SSN = b.SSN AND x.RealName = b.RealName AND x.BudgetCode = b.BudgetCode)
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1) In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break - Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment ( DeptId int identity(1,1) primary key, name varchar(50) ) create table ChildDepartment ( Id int identity(1,1) primary key,
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone) FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
I have data in a table (@Outer) that I am matching to a lookup table (@Inner) which contains multiple "matches" where nulls can match any value. By sorting the inner table and grabbing the top record, I find the "best" match. I know the sort and the null matches work but I don't understand why the correlated sub query below doesn't understand that the OJ prefix refers to the outer table.DECLARE @Outer TABLE ( OuterID int IDENTITY (1, 1) NOT NULL, MethodID int NULL, CompID int NULL, FormID int NULL, InnerID int NULL )
-- UPDATE Outer Table with best match from Inner table UPDATE @Outer SET InnerID = IJ.InnerID FROM @Outer OJ INNER JOIN ( SELECT TOP 1 I.* FROM @Inner I WHERE IsNull(I.MethodID, OJ.MethodID) = OJ.MethodID AND IsNull(I.CompID, OJ.CompID) = OJ.CompID AND IsNull(I.FormID, OJ.FormID) = OJ.FormID ORDER BY I.MethodID DESC, I.CompID DESC, I.FormID DESC ) IJ ON OJ.MethodID = IsNull(IJ.MethodID, OJ.MethodID) AND OJ.CompID = IsNull(IJ.CompID, OJ.CompID) AND OJ.FormID = IsNull(IJ.FormID, OJ.FormID) SELECT * FROM @Outer The result should be OuterID 1 matched to Inner ID 3 and OuterID 2 matched to Inner ID 5. Can anyone help me? Thanks in advance.
Table A, Table B. need to update table A balance field from table b sum of amount
UPDATE CUSTOMERS SET BALANCE=(SELECT SUM(AMOUNT) FROM PAYMENT,CUSTOMERS
WHERE CUSTOMERS.ID=PAYMENT.ID GROUP BY PAYMENT.ID)
Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I'm self-taught on T-SQL so forgive me if this is a dumb question. If I have a stored procedure containing the following...
IF [condition] SELECT ... ELSE SELECT ...
Would it be more efficient/proper/etc. to break those two SELECTs out into their own SPs and execute them from the IF statement?
IF [condition] EXEC [SP] ELSE EXEC [SP]
I've got a number of pretty lengthy SPs that I think are pretty tight but if I can make them more efficient by breaking them down into smaller tasks, I'd rather do that.
What is your opinion using Sub Selects versus Joining Tables?
SELECT (SELECT COMPANY_NAME FROM COMPANIES WHERE COMPANY_ID = c.COMPANY_ID) AS 'Company Name'
FROM COMPANY_ORDERS c
vs
SELECT co.COMPANY_NAME AS 'Company Name' FROM COMPANIES c, COMPANY co WHERE c.COMPANY_ID = co.COMPANY_ID
I'm not having any problems, just curious what everyone is practicing and is "system-actically" more resourceful. I personally like using Sub Selects (or Sub Queries) so this way I wouldn't have to deal with complex joins and where conditions, especially when your just dealing with a releational table.
I'm having problems optimizing a sql select statement that uses a LIKE statement coupled with an OR clause. For simplicity sake, I'll demonstrate this with a scaled down example:
CompanyAddressAssoc is the many-to-many associative table for Company and Address. A search query is required that, given a search string ( i.e. 'TEST' ), return all Company -> Address records where either the CompanyName or AddressName starts with the parameter:
Select c.CompanyID, c.CompanyName, a.AddressName
FROM Company c
LEFT OUTER JOIN CompanyAddressAssoc caa ON caa.CompanyID = c.CompanyID
LEFT OUTER JOIN Address a ON a.AddressID = caa.AddressID
WHERE ((c.CompanyName LIKE 'TEST%') OR (a.AddressName LIKE 'TEST%))
There are proper indexes on all tables. The execution plan creates a hash table on one LIKE query, then meshes in the other LIKE query. This takes a very long time to do, given a dataset of 500,000+ records in Company and Address.
Is there any way to optimize this query, or is it a problem with the base table implementation?
I remember when studying for exam 70-528 that I read a practice question that said it was better to return 2 select satements rather than a join on two tables as this would be more efficent. The question from MCPD Self Paced Training Kit: Designing and Developing Web Based Applications Using Microsoft .NET Framework (Certification Series): Designing and Developing ... Applications Using Microsoft .NET Framework was: You are an ASP.NET application developer. You are participating in the design of a Web retail application. The application accepts orders from the clients and stores them in a SQL Server 2005 database. Each order contains header information (order date, client information, and shipping address) and details information, which includes information about ordered products. Each detail line contains information about one ordered product and its quantity and price. All of the preceding information will be stored in two database tables: one table called Orders for the order header and another table, called OrderDetails, for order details in which each record in the orders table could have one or more related records in the OrderDetails table. When the user selects the specific order ID, the application is supposed to select all the information about the order from the database, and you need to create a stored procedure that allows selection of information about all the fields for the stored order from the database, based on a provided order ID value. Which of the following stored procedures do you recommend creating in a database to achieve the best performance on a database server side and reduce network traffic between application and database server?The answer was:CREATE PROCEDURE ORDERS_SELECT_ORDER @ORDER_ID INT AS SELECT * FROM Orders WHERE OrderID=@ORDER_IDSELECT * FROM OrderDetails WHERE OrderID=@ORDER_ID RETURNAs oppose to the traditional: CREATE PROCEDURE SP_SELECT_ORDER @ORDER_ID INT AS SELECT * FROM Orders LEFT JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderlD WHERE Orders.OrderID=@ORDER_IDThe explanation was: To achieve the best performance, avoid names of the stored procedures that start with sp_ prefix. If the name of the stored procedure starts with sp_ SQL Server always looks for the stored procedure in the master database first because all the system stored procedures start with the same prefix and are stored in a master database. It requires extra effort from the server to locate the stored procedure. This occurs even if you qualify the stored procedure with the database name. To avoid this issue, use a custom naming convention rather than the sp_ prefix. In addition, using a join inside of the stored procedure for the provided scenario will require an extra task from the server to join the data and produce a joined result. It will also lead to the redundant data for the header information that will be duplicated with each detail of the order and will increase traffic between the client and the server. Using only two separate SELECT SQL statements will allow you to avoid a join operation and reduce the size of the returned data to the client.Any one got any views on this as I have never actually seen this demostrated in a sample applcation. Is it a lot more efficent?ThanksScott
I'm about to build a batch of procedures that will be returning one row of data that is created by referencing the same table multiple times. The example below is very simplified but you'll see the differences.
Which is better:
Option A:
SELECT a.Field1, a.Field2, b.Field1, b.Field2 FROM MyTable a, MyTable b WHERE a.ID = 10 AND a.Type = 1 AND b.ID = 10 AND b.Type = 2
Or Option B:
SELECT a.Field1, a.Field2, b.Field1, b.Field2 FROM MyTable a JOIN MyTable b ON a.ID = b.ID WHERE a.ID = 10 AND a.Type = 1 AND b.Type = 2
