.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Asad Ali
Fauzul Azmi
Post New Web Links

Two statement in WHERE clause one is comparing strings one integers, should there be any order to ma

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :
 

OK. Enough complexity in the subject line, here are the details

I have a table with many columns, two of them are ManagerId INT and DesignationCode VARCHAR(30), Now the following query

SELECT EmpId, EmpName FROM Employee WHERE DesignationCode='SWENG' AND ManagerId=3

 

A friend of mine has been told by an MVP that in this case integer comparison should be the first one in order and varchar should be the second to make it perform a lot better.

I tried googling for 15 min, found no evidence. Do you know of any performance suggestion ?

My friend says that his MVP friend says that SQL Server's engine is designed to process it faster this way. So the WHERE clause should be changed to WHERE ManagerId=3 AND DesignationCode='SWENG' 

 

Do you guys know anything about it ?

 

Thanks


Fahad


View Complete Post


More Related Resource Links

Use Two Order By Clause In One Statement

  

I want to use two order by clause in one statemet or i just want to show one column in ascending order in other column in descending order without using T-SQL Statement

Please Suggest Me .

Thanks In Advance


Looking for TSQL statement that returns customers with greatest number of order in descending order

  
What would be an efficient TSQL SELECT statement that joins customer and order tables, counts the orders for each customer (group by) and returns a result for all customers with at least one order, and also in descending order from customers with the most orders to the least?

INSERT statement with OUTPUT clause, referencing outer query columns

  
I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

Can we use Select clause in Case statement

  
SELECT CASE OT.ItemTypeID WHEN 6 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 7 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 8 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 8 WHEN 9 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 9 WHEN 10 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 10 WHEN 11 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 12 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(6,11,12) WHEN 14 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 15 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID IN(7,14,15) WHEN 18 THEN SELECT SUM(ISNULL("Item Cost",0)) FROM SUBTABLE IT WHERE IT.OrderID = OT.OrderID AND IT.ItemTypeID = 18 WHEN 19 THEN SELECT SUM(ISNULL(&qu

Query help to write case statement in Where clause?

  
Hi All, I have table something like this:- SELECT UserName, IsManager FROM Employee IsManager has only two values either 1 (which means user is a Manager) or 0 (which means user is not a Manager) Now I want to write below query:- DECLARE @IsManager INT SET @IsManager = 2 SELECT UserName, IsManager FROM Employee WHERE IsManager = CASE WHEN @IsManager = 1 THEN 1 WHEN @IsManager = 0 THEN 0 WHEN @IsManager = 2 THEN 1 | 0 ----- THIS IS NOT WORKING, when I set @IsManager value to 2 then where clause should be like this "IsManager = 1 OR IsManager = 0" END Can anybody help me out in modifying about query so that it produces both the results when @IsManager variable value "2". Thanks Regards, Kumar

Pass the entire where-clause of a SQL statement through the URL?

  
I need to pass a ridiculous number of where-clause parameters to a SQLquery. I'd like to build a long where-clause on the client side andpass it to the web form through the URL. Is possible to pass the entire where-clause of a SQL statement to anasp.net SelectCommand instead of multiple individual field parameter?if yes, now? ---------- working------------------------------------------------------- http://localhost:1532/Default.aspx?myage=99 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True" ProviderName="System.Data.SqlClient" SelectCommand="SELECT * FROM [tblMain] WHERE ([age] <= @age)"> <SelectParameters> <asp:QueryStringParameter DefaultValue="0" Name="age" QueryStringField="myage" Type="String" /> </SelectParameters> </asp:SqlDataSource> ----------end working-------------------------------------------   ----------start not-working------------------------------------- http://localhost:1532/Default.aspx?para='(age<99)' <asp:SqlDataSource ID="SqlDataSource1" runat="server" Conne

Case statement in where clause

  
i trying following querySelect  FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email,  CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID  From ClientDetail with (nolock)  Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And  if(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted')) ELSE  (ClientStatus Is Null OR Lower(ClientStatus) = 'active') )  Order By ClientDetail.CreationDate DESC ;Select FirstName, LastName, ClientID, WPhone, HPhone, MPhone, Email, CreationDate, LastModified, BrokerID,  ShareBrID1, ShareBrID2, AptShowingCID From ClientDetail with (nolock) Where @Assigned_id in (BrokerID, ShareBrID1, ShareBrID2)  And case(@Type is not null and Lower(@Type) = 'inactive' )then (ClientStatus in ('active','deleted'))ELSE (ClientStatus Is Null OR Lower(ClientStatus) = 'active') ) Order By ClientDetail.CreationDate DESC ;but is gives an error plz help

Do changing clause order impress on speed?

  
Hi suppose there is a table with feilds: idMember, idHost, ip , dateofAccess. this table save all accesses to system and means:a "idMember" with iPaddress "ip" through "idHost" accessed to system at "dateofAccess". now we want to write a query like below: select Count(idMemeber) from myTable where (idMemeber = 1) and (idHost = 2) and (ip = 12.12.12.12) and (dateOfAccess < '1.1.2009') my question is:Do speed differ if change the order of up clause like bellow. select Count(idMemeber) from myTable where (dateOfAccess < '1.1.2009') and (ip = 12.12.12.12) and (idHost = 2) and (idMemeber = 1) Best Regards.Morteza  

ORDER BY clause causes 'A severe error occurred on the current command. The results, if any, shoul

  

Hello,

I'm trying to execute the following:

SELECT STATE_CODE5
  FROM qaeda.R727702b.NF29005
  WHERE STATE_CODE5 IN ('IL','NV','VA','NH','HI')
  InTERSECT
  SELECT STATE_CODE8
  FROM qaeda.r727702b.NF29008
  WHERE STATE_NAME8 = 'HAWAII'
 ORDER BY STATE_CODE5;

In both 2005 and 2008 versions of the SQL Server the query parses ok, but on execute I get the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command.  The results, if any, should be discarded.

If I remove the the 'ORDER BY' clause, I am able to execute the query with no problem. 

I've searched the forum and I know this error comes up often in various circumstances, however I didn't see anything similar to this.  Please advise, maybe I'm writing the query incorrectly?  Thanks

 

 


Order By clause in RowNumber function slowing down the performance of my query.

  

Hi,

I have a query which has a paging functionality and for which i'm using ROW_NUMBER() function. The order by clause in my rownumber function is dynamic and gets changed all the time when ever the sp is called. It works fine for few columns but for one column which is of datetime datatype, it takes lot of time. Amazing thing is with when i sort by the same column in descending order, the query runs in a sec but ascending order takes lot of time :(.

I tried creating a non clustered index(as i already i have a clustered index on that table) on that datetime column but it did not help me.

Could you please suggest what i can do to improve the performance.

Thanks,

Deepti

 

 

 


Using CASE statement in "IN" clause of query

  

I have a sql datasource with the ControlParameter called ddlDropDownList.

2 questions:

1. Is my syntax below ok, I am getting a runttime error that the syntax is incorrect.

2. I have the select statement set up the control parameter to get the selected value from the dropdownlist as shown below, is that syntax correct?  especially the "selectedItem.value" for the propertyname property. thx


select fieldOne, fieldTwo from someTable where fieldOne IN 
CASE WHEN @ddlDropDownList='valueABC' THEN ('ABC') WHEN @ddlDropDownList='valueDEF' THEN ('DEF') 
ELSE ('ABC','DEF') END 

<asp:ControlParameter ControlID="ddlDropDownList" Name="typeSlctd" PropertyName="SelectedItem.Value" Type="String" />



Column Alias in ORDER BY Clause -- not supported in SQL Server 2005/2008 -- Causes enter migration

  

Hi

We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all statements.

One of article says that SQL Server 2005/SQL Server 2008 not support "Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing. it is going to be big deal.

our client Decided to stop migration, Please some help is there any other alternative.

 


SNIVAS

Case Statement With IN Clause

  

Hi all,

I am using a query and have isssue in doing multi select with an amount field.

Actually I want to have a parameter like this:

Select ANY Amount range (from the 3 shown):-

> 8500
< -8500
BETWEEN -8500 AND 8500


The query is simple with case statement to run for either one of the range selected.
But now I want to also run by multi selection on Ranges with an OR statement.

I am able to run for either of the range selected but I am not able to run for more than one range selected.


Any help on this.

Here is my sample code.

 

create table dbo.test
(
year int,
month int,
Name varchar(12),
amount int)

INSERT INTO test ( year,month,

perfomance issue with 'order by' clause

  

hi,

  I have copied 2 queries and their execution plans below.first one taking long time to execute and the second one finishes with in a second.can u guys pls explain what is happening inside sql server engine.

 

--first part

select   se.eventdate
from
haregistration r inner join
screening_event_slot ses

on r.ScreeningSlotID=ses.SlotId
inner join screening_event se
on se.eventid=ses.eventid  order by EventDate

 |--Nested Loops(Inner Join, WHERE:([microsoft].[dbo].[HARegistration].[ScreeningSlotID] as [r].[ScreeningSlotID]=[microsoft].[dbo].[screening_event_slot].[SlotId] as [ses].[SlotId]))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([se].[EventId], [Expr1006]) WITH ORDERED PREFETCH)
       |    |--Index Scan(OBJECT:([microsoft].[dbo].[screening_event].[IX_EventDate] AS [se]), ORDERED FORWARD)
       |    |--Index Seek(OBJECT:([microsoft].[dbo].[screening_event_slot].[AK_screening_event_slot] AS [ses]), SEEK:([ses].[EventId]=[microsoft].[dbo].[screening_event].[EventId] as [se].[EventId]) ORDERED FORWARD)
       |--Clustered Index Scan(OBJECT:([microsoft].[dbo].[HARegistration].[PK_HAR

can i create a Clr ud Aggregate Function with over/ order by clause?

  

i want create a UD Aggregate like  Row_Number () for using with 'Over (Order By ...) ' (not like sum() over (part....)

is it possible?
thanks.


Delete Statement with nested select in where clause deletes all entries in a table

  

I have fallen into a trap unadvertedly causing all records in a table being deleted. Can anybody tell me, if this a the intended behaviour of SQL Server 2008 R2 or does anybody know a method how to safely discover those kinds of typos in advance?

Regards, Markus

Here follows a script reproducing the behaviour:

SET

 

ANSI_NULLS ON

GO

SET

 

QUOTED_IDENTIFIER ON

GO

Writing multiple if statement in where clause in Stored Procedure

  

Hi,

I have one Stored Procedure which returns some value. now i have some different conditions in my select query so can i write them in my where clause instead of writing the whole select query again for different conditions? 

Like 

My Current Select Statement in SP looks like :

Select

         FName,LName,UniqueRef,City

From 

       tblContact

Where 

     UniqueRef='@UniqueRef'

 

Now i have two more conditions in my Select Statement that are

@ProductList varchar(max),

@StatusList Varchar(max)

and they gets the list of id of their name from another function that is made for them respectively fun_GetProductId and fun_GetStatusId

so with this my Select Statement in SP will look like 

Select

         FName,LName,UniqueRef,City

From 

       tblContact

Where 

     (UniqueRef='@UniqueRef' or @UniqueRef='') and 

    (Productid in (Select * From fun_GetProductId(@ProductList))) and

   (StatusId in (Select * From fun_GetStatusId(@StatusList))) 


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend