I have a query with many (approximately, 30) conditions, such as:
select ....... from table1 join table2 on ( (table1.field1 = table2.field1 OR table1.filed1 IS NULL) AND (table1.field2 = table2.field2 OR table1.filed2 IS NULL) )
My question is:
In C++ or C#, when I write a condition like this, say, in an IF or WHILE, I know that I would be better off specifying the IS NULL (well, == null, to be precise) first, and use | instead of ||. In that case, the first condition (equality to null) is checked first, it's fast, and if it's not satisfied, the control flow goes to the next statement.
The question is, is there the same rule in T-SQL?
I mean, if I put the "... IS NULL" first, and then "OR ... = ...", will the query run faster than if I write it the other way around (that is, "... = ... OR ... IS NULL")?
This is very important to me, because most of those fields are VARCHAR, and due to some business rules, I can't change them to numerics etc, which would be compared much faster than text. So, even if I use full text search, I still need to find a way to optimize the query for performance...
By the way, I know that I can put those conditions in the WHERE clause, but as far as I know it won't make much of a difference for performance. So, my question is primarily about the order of conditions, in which SQL Server constructs its query plan.
[Edited:] In other words, what runs faster: comparing varchar to null or comparing varchars? And does it make a difference if I switch their places in my sql script?
We are using SQL Server 2000 SP4, Standard Edition. [Dev edition on the dev machine.]
what is order in which conditions are processed for sql query i.e for select * from table1, table2 where cond1 and cond2 and cond3 which condition will be processed first (i.e. for optimination purpose condition cutting down max no. of row shud be placed first or last?)
My question is fairly simple. When I join between two tables, I always use the ON syntax. For example:
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId)
No problems there. However, if I then decide to further filter the selection based on some trait of the UserRole, I have two options: I can add the condition as a WHERE statement, or I can add the condition within the ON block.
--Version 1:
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId) WHERE
UserRoles.Active = 'TRUE'
-- Version 2
SELECT
* FROM
Users
JOIN UserRoles
ON (Users.UserRoleId = UserRoles.UserRoleId
AND UserRoles.Active = 'TRUE')
So, the question is, which is faster/better, if either? The Query Analyzer shows the two queries have the exact same execution plan, which makes sense, since they're both joining the same tables. However, I'm wondering if adding the condition in the ON statement results in fewer rows the JOIN statement initially needs to join up, thus reducing the overall initial size of the results table before the WHERE conditions are applied.
So is there a difference, performance wise? I imagine that if Users had a thousand records, and UserRoles had 10 records, then the JOIN would create a cartesian product of the two tables, resulting in 10,000 records in the table before the WHERE conditions are applied. However, if only three of the UserRoles is set to Active, would that mean that the resulting table, before applying WHERE conditions, would only contain 3000 records?
if Region==Asia then compute percent of Amount if Region==US then compute percent of Amount if Region==Europe then compute percent of Amount if Region==Africa then compute percent of Amount
I've a table with a field named 'TypeOfProd' that has an ID for the various types of products: ex.: 1 - product A 2 - product B 3- product C 4 - product D .... 10 - product J and so on
I need to create a stored procedure that querys only the product types selected by the user.
The user can select 1, 3, 5, 10 or 1, 3 or 3 or 0 for all or some other combination.
For the first user selection a have something like this SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3 or TypeOfProd = 5 OR TypeOfProd = 10 For the second, SELECT Prod FROM tableProd WHERE TypeOfProd = 1 OR TypeOfProd = 3
Is it possible to have a stored procedures that runs a query with this random scenario?
On a webpage, there are filters to choose from. Like date, amount, SSN (multiple filters can be choosen).I have a single query so far. SqlCommand cmd = new SqlCommand("SELECT [column1], [column2], [column3], [column4], [column5] FROM [table] WHERE [column4] = 'condition4' AND [column5] = @total_bill AND [last_change] >= @txtStartDate AND [last_change] <= @txtEndDate ", Conn) ; cmd.Parameters.Add(new SqlParameter("@total_bill", total_bill1.Text)); cmd.Parameters.Add(new SqlParameter("@txtStartDate", txtStartDate.Text)); cmd.Parameters.Add(new SqlParameter("@txtEndDate", txtEndDate.Text)); I want to break the query so that it executes on the basis of different sets of conditions (filters). If I dont select date filter, then the above query will not execute properly.Please help.
I have following query. Now I want to change this in such a way that if Condition 1 satisfies then it should not go to next condition ,if not then should go to next. Similarly the same format till condition 4.
select * from table where IND = 'Y' and ( (STATE = @STATE and LOB =@LOB and COMPANY=@COMPANY)--Condition 1 OR (STATE = @STATE and LOB =@LOB)--Condition 2 OR (STATE = @STATE and COMPANY=@COMPANY)--Condition 3 OR (STATE = @STATE)--Condition 4 )
This is an exact matching record and straight forward
Is it possible to identify the record using T SQL query based on the following scenarios
1) return the record - If all the three where conditions match 2) if record not found check and return the record where 2 columns values in the where condition match
-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values
SELECT Productname FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1
Hi, I am using below query: SELECT tbh_Articles.ArticleID, tbh_Articles.AddedDate, tbh_Articles.AddedBy, tbh_Articles.CategoryID, tbh_Articles.Title, tbh_Articles.Abstract, tbh_Articles.Body, tbh_Articles.Country, tbh_Articles.State, tbh_Articles.City, tbh_Articles.ReleaseDate, tbh_Articles.ExpireDate, tbh_Articles.Approved, tbh_Articles.Listed, tbh_Articles.CommentsEnabled, tbh_Articles.OnlyForMembers, tbh_Articles.ViewCount, tbh_Articles.Votes, tbh_Articles.TotalRating, tbh_Articles.ImageURL, tbh_Articles.specialFROM tbh_Lang CROSS JOIN tbh_ArticlesWHERE (tbh_Lang.LangID = @LanguageID) AND (tbh_Articles.ArticleID = tbh_Lang.ArticleMain OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond1 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond2 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond3 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond4 OR tbh_Articles.ArticleID = tbh_Lang.ArticleSecond5) Problem is that I want to sort in a manner which the results returns as ArticleMain as the first column, ArticleSecond1 as the second and so on... Tables structure: tbh_Articles(id, title, body...) ; tbh_Lang(id,ArticleMain,ArticleSecond1 ,ArticleSecond2.... ) Any suggestions?
Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!
Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.
I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.
So far I have this:
Code:
SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold FROM Orders INNER JOIN OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN Products ON OrderItems.ProductID = Products.ProductID WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND (Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND (Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid' GROUP BY Products.ProductCode, Products.ProductTitle
Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again
I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!
Thanks in advance to anyone that looks at this for me.
Also, any recommended books/sites to learn this kind of query?
Have the query below which is taking delimited address information from _Venue column. This works well apart from the order it is returned, for example, the output below has the address tittle displayed in a different column for each row
Queen Elizabeth's Hunting Lodge is in Address1 All Saints' Church is in Address2 Audley End House is in Address3
As I need to reference from the query the correct part of the address from the same location each time, is there anyway to get around this?
Address1 Address2Address3Address4 ---------------------------------------------------------------------------------------- Queen Elizabeth's Hunting LodgeRangers RoadChingfordLondon E4 7 QH - All Saints' ChurchShrub End RoadColchester ---Audley End House
I have table called 'UserDetails'. If I execute below select query it should display in order of uno= 7,13,5 but i get in order of
uno=5,7,13.
How to get in order of uno= 7,13,5
Select EmailAddress,EmployeeName,UNo, MobileNumber from UserDetails where (UNo=7 or UNo=13 or UNo=5 ) group by uno,emailaddress,employeename,uno,mobilenumber
Result I am getting as
EmailAddress EmployeeName UNo MobileNumber ----------------------------------------------------------- aaa@xxx.com ravi 5 8989898989 bbb@xxx.comramesh 79898989898 ariv@gmail.com arivu 13 8989898989
Hi every body. Can u tell me how to get the order values of the SQL query Example. My sqlstring ="Select * from tbl_Products" And it returns 6 rows And I want to get order values like this 1,2,3,4,5,6 I am a beginner. Thanks a lots
SELECT Column1,Column2,Column3 .... FROM vwMyView ORDER BY CreatedDT
View has about 10000 rows, If I remove order by query runs faster but adding an order by cause query to timeout..
All tables have clustered index based on the primary key of the table..
(a) Should I create an index view with CreatedDT as non clustered index? (b) Or create a non clustered index on CreatedDT column on the underlying table?
I can provide DDL but if something obvious I am missing
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
Table:ColumnsUsersList:UserID, UserName, Country I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order So the query first return all the users from Pakistan and the the users from rest of the world in ascending order. Forexample, 1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan then the query returns. 3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA
I have some data that I'd like to order by a certain attribute.. but if there is a tie, then it should order by a secondary attribute.. and if there's still a tie.. then a 3rd attribute.
Currently the query looks like this:
SELECT * FROM Players WHERE ORDER BY Points
But I want it to look something like this (I know this doesn't work.. but it's just to give an idea):
SELECT * FROM Players WHERE ORDER BY (Points desc AND Games asc AND Goals desc)
Does anyone know the proper syntax for a multiple ORDER BY query like I described above?
If we use: select * from ..... , normally, it will return an ordered result ( may be order by ID ), but how can we make an random order select statement. It mean every time we run the query, the result will be different from the other ?
I have a VB.NET function that returns an array of Integers. Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54] One characteristic of the array is that no two numbers repeat - it reflects the IDs of my Users table. And, that it is not ascending or descending.
What I would like to know is how do I sort my query in the order of the integers in this array? Ideally, I would like to use ORDER BY for this query.
I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.
Code: SELECT * FROM DMTM SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223' WHERE temp_pk in
I am trying to combine 2 queries, each with their own 'order by' and I am having trouble.
This is just an example, not what I'm trying to do, my query is more elaberate but looks simular to this.
SELECT TOP 10 *, 'FieldA' AS SortedBy FROM TableA Order By FieldA Desc UNION ALL SELECT TOP 10 Precent *, 'FieldB' AS SortedBy FROM TableA Order By FieldB Asc
Anyway not to get the following error? Server: Msg 156, Level 15, State 1, Line 34 Incorrect syntax near the keyword 'UNION'.
Hello, I'm trying to find the most optimal way to perform a trickyquery. I'm hoping this is some sort of standard problem that has beensolved before, but I'm not finding anything too useful so far. I havea solution that works (using subqueries), but is pretty slow.Assume I have two tables:[Item]ItemID int (Primary Key)ItemSourceID intItemUniversalKey uniqueidentifierPrice int[Source]ItemSourceIDPriorityI'm looking for a set of ItemIDs that match a query to the Price(something like Price < 30), with a unique ItemUniversalKey, taking thefirst item with each key according to Source.Priority.So, given Item rows like this:1 2 [key_one] 152 2 [key_two] 253 1 [key_one] 15and Source rows like this:1 12 2I want results like this:2 2 [key_two] 253 1 [key_one] 15Row 1 in Item would be eliminated because it shares an ItemUniversalKeywith row 3, and row 3's Source.Priority is lower than row 1.Help!?
Hi all,Just wondering if anyone can tell me if an order by clause on a selectquery would have any impact on the time it takes to retrieve results?Essentially I'm selecting Top 1 out of a table via various criteriaand currently getting it back without an order by clause. The order bywould only include the column that has the clustered primary index onit.Can anyone tell me if in theory this will slow the query down?Many thanks in advance!Much warmth,Murrau
I have a table of transaction that includes student ids and dates. I need to select all records from the table and include a new value that is the sequential transaction numbered for each student with the oldest transaction for each student being numbered one, the next oldest numbered two and so on. So the result should look like student1, 10/1/2000, 1, student1, 10/15/2000, 2, student1, 2/12/2001, 3, student2, 9/1/1999, 1, student2 10/2/2000, 2, student2 , 12/15/2000, 3, student2, 11/4/2001, 4 and so on.
i use microaccess create table, there is a filed call"Complete_PO", value"yes/no" i wrote following statement to select it, but at runtime, there is warning message"...constraint...one or more row violating non-unique and so so..." how to solve it SqlSelectCommand2.CommandText = "SELECT Complete_PO FROM [PURCHASE ORDER] WHERE [PO_No] Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No,4) desc" PoNum_SqlDataAdapter.Fill(PO_DataSet1) TextBox1.Text = PO_DataSet1.Tables("PURCHASE ORDER").Rows(0).Item("Complete_PO").ToString()
i query a purchase order table, there is one column called PO_No, format: LP-0245111-0004 i make following statement to query: the middle code act as my id, using it search my records, the last 4 digit used to find the last purchase order number SqlSelectCommand2.CommandText = "SELECT PO_No FROM [PURCHASE ORDER] WHERE PO_No Like '%" & GetYearCode() & "%' ORDER BY Right(PO_No, 4) DESC" i checked my database, last record is LP-0545381-0300 in my debuging process, surprisingly found that selected record is LP-0545381-301 ! any one hav any suggestion? ^_^
In my BM_Maps subform, there is a combo box called called Borders. I want the borders to be filtered based on product series, so if the user picks "City Guide", they only get city guide borders, so I have added a table called Product_Series_X_Border which has the product series and border id.
I have a query which joins two tables, and I've ordered it by the border name, which works fine, until I add a where clause for the product series from the other table. I have attached a screengrab which I hope works. I have tried joining other tables but it still doesn't work. Is there any other way I can order by border name with the where clause? I've tested with individual product series as the where clause with the same result.
I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?
Thanks so much
select date3, date2, date1, who, what from ( select date3, date2, date1, who, what from shows union select date3, date2, date1, who, what from shares union select date3, date2, date1, who, what from soiree ) a order by date3, date2, date1
I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.
