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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

need to fix a where clause and an order by

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :

Sorry, The Content you are looking for is no longer available.

View Complete Post

More Related Resource Links

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 = 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 = 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



I'm trying to execute the following:

  FROM qaeda.R727702b.NF29005
  FROM qaeda.r727702b.NF29008

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.



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.






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



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.



perfomance issue with 'order by' clause



  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
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?

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

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common ta


Any ideas how I can order inside the subquery? I get the following error. I have tried ORDER BY MAX([Count]) but it doesn't like this much either... Also, I don't want to use TOP in the subquery as I need to use all the records!

			 COUNT(*) AS [Count]
		GROUP BY System_User_ID
		ORDER BY [Count]) l
	INNER JOIN aspnet_Membership m
	ON CAST(m.UserId AS VARCHAR(36)) = l.System_User_ID
Msg 1033, Level 15, State 1, Line 11
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

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


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 ?




Power of using Clause in SharePoint

The Using Clause will help to avoid memory leaks in SharePoint by automatically Disposing the MOSS objects.

For example when we create SPSite and SPWeb objects, if we don't dispose it explicitly it might create memory leaks,



Is it possible with SQL sorted in descending order according to the word?
In Table I shrajeno Mastercard, Visa, Diners etc ...

All Visa are the first in the order, another Mastercard, etc. ...

Something does not work: ORDER BY PaymentType = 'Visa';

I would not want to work in one field PaymentId (111,222,33, etc. ..) and then use the ORDER BY.

Is this possible in such a way?

Linq qry with order by basic questions


I have never tried a group by with linq, now I do and I'm stuck.

I try to convert this sql statement to its linq counterpart :

SELECT COUNT(tb_Mailer_Smses.Id) AS [cnt], tb_Mailer_mailing.Id As [Id]
,SUM( CASE WHEN tb_Mailer_smses.DLRStatus = 200 THEN 1 ELSE 0 END) As [Delivered]
FROM tb_Mailer_mailing INNER JOIN
tb_Mailer_Smses ON tb_Mailer_mailing.Id = tb_Mailer_Smses.MailingId
WHERE tb_Mailer_mailing.ApplicationId = 1
GROUP BY tb_Mailer_mailing.Id

Is there some one who can me show me the convertion process ?

ASP.NET: Collect Customer Order Information on an Internet Site Using XML and Web Forms


XML has quickly become the new data structure standard for everything from database access to cross-platform computing. XML is typically considered to be a vehicle for data exchange, dynamic data presentation, and data storage. However, the potential of XML far surpasses those limited applications. This article examines one new use: the gathering of data across a number of forms in an ASP.NET Beta 1 framework application. The sample program is a Web app used for ordering pizza. It uses ASP and C# to gather order information and then stores it in XML. To build the application, several concepts are explained, including data collection, order persistence using cookies, grouping input forms, and formatting the data for display.

Jeff Jorczak

MSDN Magazine September 2001

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