I have the following 2 sql statements. One uses a fixed string, while the other used a variable. The first one takes 1 second, and the second takes approx 30-40 seconds. Why is this. Please not that when I use dynamic SQL it is back to 1 second, which is what I used in the end.
Here are the sql statements....HELP!!!
Also not that these tables contain 5 million records and all fields used in the query are indexed.
declare @code varchar(50)
select @code = 'V'
Selectb.pa_id
frommytable b
whereb.code3 = 'V'
and b.pa_id <> 0
group by b.pa_id
--Takes 1 second
Selectb.pa_id
frommytableb
whereb.code3 = @code
and b.pa_id <> 0
group by b.pa_id
--Takes 30 seconds
I'm trying to use this WHERE Clause in a stored procedure, it is not working. I'm hoping someone can look at it and tell me if there is a better way or what might be causing it to fail.(CONVERT(varchar, tblTasks.ReminderDate, 101) BETWEEN CONVERT(varchar, GETDATE() - 7, 101) AND CONVERT(varchar, GETDATE() + 7, 101))
I have a form that has many checkboxes (more than 40) that provide information about companies.
For example. Company ABC Checkbox 1 (Windows XP) Checkbox 2 (Windows Vista) Checkbox 3 (Windows NT4) etc.
I save these in a SQL table like the following Row 1 - Col 1 (CompanyID), col2 (checkbox value) Row 2 - Col 1 (CompanyID), col2 (checkbox value) etc.. so a comapany can have multiple checkbox selected..
I am currently developing a report where the end user can select one or many of the checkbox to see if the company exist.
This is where I get stuck - I only want to return the results of the companies that meet the report selections. So if I want to see all the companies that current have Windows NT and Windows Vista...how would I build that querry based on the data model above.
I have tried using the IN clause - however that use the OR connector and doesnt show ONLY the compaines that meet. I have tried using a UNION and INTERSECT, and running the select statement many times.. (SELECT CompanyID from xx WHERE CheckboxVal = xx) INTERSECT (SELECT CompanyID from xx WHERE CheckboxVal = xx) etc.. howevert this creates a very large quesry that is unable to be handled by SQL 2005. I get a error message asking to minimize the query.
Any suggestions on this please...this is my 4th day working on this..
Dear Friends, I'm preety new to the concept of globalization & all. Basically my requirments is very straight-forward. I have a table structure is as follwos:
the second & the third column has data in Simplified chinese and their collation is Chinese_PRC_90 The data is getting properly inserted and retrived.
But i'm not able to use "where" clause in the query where the value specified is Chinese. I tried LIKE as well as in IN, but everytime empty dataset is returned.
Is there anything special i have to take care of to enable this feature.
I am having a problem with this stored procedure. I'm using SQL Server 2005 Developer's edition and if I execute the procedure in a query window, I get no errors. Also, when the script runs from a website call there are no errors. The problem is that it doesn't return the information that is in the database. It is supposed to return the orders from Washington state between such and such dates. The orders are there in the database, so I think the where clause must be wrong.
Thanks for the help.
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CommerceLibOrdersGetWashingtonState]') AND type in (N'P', N'PC'))
I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?
I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....
I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.
Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?
Hi, can anyone shed some light on this issue?SELECT Status from lupStatuswith a normal query it returns the correct recordcountSELECT Status from lupStatus GROUP BY Statusbut with a GROUP By clause or DISTINCT clause it return the recordcount= -1
ON dbo.Track_ID.SM_ID = dbo.transactions.sm_session_id
GROUP BY dbo.Track_ID.TrackID
)
) Once moved to SQL Server 2005 the statement would not return and showed SOS_SCHEDULER_YIELD to be the waittype when executed. This machine is SP1 and needs to be upgraded to SP2, something that is not going to happen near time.
I changed the SQL to the following, SQL Server now runs it in under a second, but now the app is not functioning correctly. Are the above and the following semantically the same?
UPDATE dbo.Track_ID
SET dbo.Track_ID.Processed = 4 --Regular 1 leg call thats been completed
I had a view in which I did something like this isnull(fld,val) as 'alias'
when I assign a value to this in the client (vb 6.0) it works ok in sql2000 but fails in 2005. When I change the query to fld as 'alias' then it works ok in sql 2005 . why ?? I still have sql 2000 (8.0) compatability.
Also some queries which are pretty badly written run on sql 2000 but dont run at all in sql 2005 ???
any clues or answers ?? it is some configuration issue ?
1) Rows ordered using textual id rather than numeric id
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v order by v.id
Result set is ordered as: 1, 11, 2 I expect: 1,2,11
if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
2) SQL server reject query below with next message
Server: Msg 169, Level 15, State 3, Line 16 A column has been specified more than once in the order by list. Columns in the order by list must be unique.
Code Snippet select cast(v.id as nvarchar(2)) id from ( select 1 id union select 2 id union select 11 id ) v cross join ( select 1 id union select 2 id union select 11 id ) u order by v.id ,u.id
Again, if renamed or removed alias for "cast(v.id as nvarchar(2))" expression then all works fine.
It reproducible on
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
and
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In both cases database collation is SQL_Latin1_General_CP1251_CS_AS
If I check quieries above on database with SQL_Latin1_General_CP1_CI_AS collation then it works fine again.
Could someone clarify - is it bug or expected behaviour?
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
I am using web developer 2008, while connecting to I wanted to fetch data from Lotus notes database file, for this i used notesql connector, while connectiong to notes database i am fetting error
ERROR [42000] [Lotus][ODBC Lotus Notes]Table reference has to be a table name or an outer join escape clause in a FROM clause
I have already checked that database & table name are correct, please help me out How i can fetch the lotus notes data in my asp.net pages.
what is differance between conditions put in ON clause and in WHERE clause in JOINS????
see conditions that shown in brown color
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) )
select d1.SourceID, d1.PID, d1.SummaryID, d1.EffectiveDate, d1.Audit, d1.ExpirationDate, d1.Indicator from[DB1].[dbo].[Implicit] d1 inner join [DB2].[dbo].[Implicit] d2 on d1.SummaryID=d2.SummaryID AND d1.ImplicitID = d1.ImplicitIDAND d1.ListType = d2.ListType AND (d1.EffectiveDate <= d2.ExpirationDate or d2.ExpirationDate is null) AND (d1.ExpirationDate >= d2.EffectiveDate or d1.ExpirationDate is null) whered1.ImplicitID >= d2.ImplicitID AND (d1.SourceID<>d2.SourceID OR (d1.SourceID IS NULL AND d2.SourceID IS NOT NULL) OR (d1.SourceID IS NOT NULL AND d2.SourceID IS NULL) )
another thing...
if we put AND d1.ImplicitID = d1.ImplicitID condition in second query then shall we remove d1.ImplicitID >= d2.ImplicitID from WHERE clause????
Hi everyone, I saw some queries where SQL inner join clause and the where clause is used at the same time. I knew that "on" is used instead of the "where" clause. Would anyone please exaplin me why both "where" and "on" clause is used in some sql Select queries ?
Example, suppose you have these 2 tables(NOTE: My example is totally different, but I'm simply trying to setupthe a simpler version, so excuse the bad design; not the point here)CarsSold {CarsSoldID int (primary key)MonthID intDealershipID intNumberCarsSold int}Dealership {DealershipID int, (primary key)SalesTax decimal}so you may have many delearships selling cars the same month, and youwanted a report to sum up totals of all dealerships per month.select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',sum(cs.NumberCarsSold) * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDMy question is, is there a way to achieve something like this:select cs.MonthID,sum(cs.NumberCarsSold) as 'TotalCarsSoldInMonth',TotalCarsSoldInMonth * d.SalesTax as 'TotalRevenue'from CarsSold csjoin Dealership d on d.DealershipID = cs.DealershipIDgroup by cs.MonthIDNotice the only difference is the 3rd column in the select. Myparticular query is performing some crazy math and the only way I knowof how to get it to work is to copy and past the logic which isgetting out way out of hand...Thanks,Dave
Hey guys, I'm a bit weak when it comes to doing ands and or's. I know what i want, but when I put it into statement, i dont get the results that i want. I have 3 fields in my where clause. ID, LW, and LWU. The code is as follows:WHERE (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID NOT LIKE '%6%') AND (ID NOT LIKE '%7%') AND (ID NOT LIKE '%8%') AND (LW <> 1) AND (LWU <> 'test') OR (LASTVISIT BETWEEN '1 / 1 / 95 12 : 00 : 00 AM' AND '1 / 1 / 06 12 : 00 : 00 AM') AND (ID IS NULL) AND (LW <> 1) AND (LWU <> 'test') I have a range of dates that I want to grab, in there I do not want any records where ID has 6,7,8 and I only want records where LW does not equal 1. UP to this point, it works fine. I get all the records that only return these values. However, the moment I add where LWU does not equal 'test'. it does not return the values I want. Furthermore, why can I not put this whole string into one and clause? I never understood why I had to create a second line following OR. the longer this query gets the more I get confused. Any help?
I have an insert statement that reads: SELECT AppointmentID, PatientNo, PatientSurname, PatientForename, ConsultantName, HospitalName, Date, CONVERT (varchar, Time, 8), AppointmentStatus FROM [Appointment] WHERE ([AppointmentId] = @AppointmentId) I also need to add another WHERE clause. This clause will mean that if the date is within 14 days of the actual date it will not ba able to be selected need help writing this not sure how to write it Thanks in advance Mike.
I'm having a heck of time with this where clause. I have a table that contains client addresses, a client can have more than one address. So some of the addresses may be seasonal. I need to return only the current address based on a flag MailTo (bit) and a date range, just the month and day, the start and end are datetime datatypes.
Here is what i have tried:
I would really would like it to work on a range of month and day based on the startdate and enddate fields and the MailTo flag. The table looks like this;
I am working on a project involving text searching. I created a fulltext catalog on the database and scheduled it for every one minute. I created a fulltext index on a table and added some columns. I scheduled it as the database catalog. I ran a simple query like this in the query analyzer but got an error message that the catalog does not exist!
SELECT * FROM tbl_extra_skills WHERE CONTAINS(ITSkills, 'Word')
hi alli need to create a sql statement that receives some values - my doubt is only about how to build that sql statementi've heard something about IN clause but could not apply it - could someone give any sample?First page: I have a textbox with some emails e.g. a@a.com, b@b.com, c@c.com etcSecond page: SELECT * FROM Table1 WHERE Field = ... IN ???thanks in advance
I would like to create a "where in" clause on the fly. For example, if a user types into a text box three email address separated by a comma, I can not do a select on them because they are not strings - (adam@homebusiness.to, wanshark1@yahoo.com, test@test.com) <--- They should be ('adam@homebusiness.to', 'wanshark1@yahoo.com', 'test@test.com') Is there a sql function that tells the server that the stuff in parenthesis is a string? Thanks
we trying to use a UDF in Where Clause. This is taking too much of time. when we replaced the UDF with a subquery the query is fast.
Eg:
select Name, Designation, Address From Employee Where dbo.GetAge(EmpId) > 25
This is taking very long to fetch 12 records from 22,00,000 records.
When the same query has been converted to
select E.Name, E.Designation, E.Address From Employee E Where (select datediff("YY", EP.DOB, GETDATE()) from EmpPersonaldetail EP where EP.Empid= E.EmpId)
I'm desperatly trying to make a query (inside a stored procedure which handles diferent tables and columns for each time) on which I have a variable as the table name. The problem is that I cant make it with the variable. What I need to do is something like this:
select @max_value=MAX(COL_NAME(@tableID,@columnID)) from (and here is my problem) @table(or @tableId or something :confused: )
I already tried with OBJECT_NAME but i can't do it, and there's no way i can know the table's name 'cause the idea is to perform the procedure to several tables.
I have a 3rd party app which generates the following in it's where clause. COnsidering all the ROJ's on this table, would it not be better to put the definitive part "AND pe.enc_id = '8E487A58-E53A-42B3-8D0E-07AA1BE2E5E5'" before the joins, or does it really make a difference?
WHERE physical_exam_.enc_id =* pe.enc_id AND immunization_mast_.enc_id =* pe.enc_id AND intk_trnf_master_.enc_id =* pe.enc_id AND vital_signs_.enc_id =* pe.enc_id AND med_sys_rev_.enc_id =* pe.enc_id AND tb_isolation_.enc_id =* pe.enc_id AND zinmate_encounter_.enc_id =* pe.enc_id AND intk_trnf_master8_.enc_id =* pe.enc_id AND pe_head_neck_.enc_id =* pe.enc_id AND pe_eye_exam_.enc_id =* pe.enc_id AND pe_ears_.enc_id =* pe.enc_id AND pe_throat_.enc_id =* pe.enc_id AND pe_skin_.enc_id =* pe.enc_id AND pe_cardio_.enc_id =* pe.enc_id AND pe_musculoskeletl_.enc_id =* pe.enc_id AND pe_neurological_.enc_id =* pe.enc_id AND pe_breast_.enc_id =* pe.enc_id AND pe_lymph_new_.enc_id =* pe.enc_id AND pe_abdomnl_.enc_id =* pe.enc_id AND pe_male_.enc_id =* pe.enc_id AND pe_female_.enc_id =* pe.enc_id AND pe_proctological_.enc_id =* pe.enc_id AND pe.enc_id = '8E487A58-E53A-42B3-8D0E-07AA1BE2E5E5'
I just learn how to create a trigger. I had question: what is the different of For, after, instead of clause when we create a trigger? Why we need these? When to use each of these. Thanks in advance.
