.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

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

Posted By:      Posted Date: October 21, 2010    Points: 0   Category :Sql Server

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

is it possible?

View Complete Post

More Related Resource Links

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.






Function and Variable declaration with out an 'As' clause


I get a message - "Function without an 'As' clause" for HMAC_MD5 and "Variable declaration without an 'As' clause" for Key and Value.  But the code works.  But the messages still exists.  Is there a way to fix this to avoid the messages?

Function HMAC_MD5(ByVal Key, ByVal Value)


About the Aggregate Function MIN

Hi all, I am using the select statement with MAX(id), MIN(id), COUNT(*) of a very big table and it is returning me the value in less than a second. But surprisingly if i am using aggregate function MIN(id) in a seperate select statement it is taking upto 90 Seconds. Here are the 2 statements I am running SELECT MAX(ID),MIN(ID),COUNT(*) FROM A TABLE  -- Time consuming for this:  less than a Second. SELECT MIN(ID)FROM A TABLE  -- Time consuming for this:  Upto 90 Seconds. Please clarify how it work internally when we call the select statement with Aggregate functions?  chinna

How to create a "search function" to find files and folders using C++ windows forms?

As mentioned above, how can I make a windows forms application that searches for files and folders specified by the user and display results in a ListBox? Thanks in advance another question: How can I make a textbox transparent? I would be really grateful if anybody told me what books are good to learn windows forms using C++? thanks again

how to create a List order and unourder a simple Sql Query

A Simple Query Pass to connection String 1 My Query is SELECT a.HD, a.HEAD, a.SH, a.SUBHEAD, a.TRK, a.TRACK, a.TGT, a.TARGET, a.PDC, a.DT_RELEASE, a.STATUS, a.COFROM V_HIERARCHY1 a   2,My Data is HD    HEAD    SH    SUBHEAD    TRK    TRACK    TGT    TARGET    PDC    DT_RELEASE    STATUS    COHR    HR    Rec    Records    Leave    Leave Records    HRL.0002    Quarterly Review    01.07.2010    04.07.2010    Planned    EdlinkHR    HR1    DB    Database    SAL    Salary    DDS.0001    Calculation of Monthly salary    08.08.2010    09.08.2010    WIP    EdlinkHR    HR    Rec    Records    Ind    Induction Records    HRI.0001    Completion of dossiers.    08.07.2010    10.07.2010    WIP    EdlinkHR    HR  

how to create a list order and unorder using database

how to create a list order and unorder using database

Use of PATINDEX function in select clause With Linq

hello       i have an problem to use of PATINDEX function in select clause with Linq.      if anyone have any clue about it then please update me its urgent.Thanks In Advance.

a function in the where clause?

I have this where clause that I use in so many different places, I wanted to see if it is possible to have it as a function or something so that in the case I have to ever change it, I won't have to go through 50+ queries to change it.   Select * From Table1 Where IsTableAvailable = 1 AND (IsTableBroken = 0 OR IsTableFixed = 1)    

Tab order in create user wizard

I have as 2 step create user wizard.  The second step collects shipping information.  I use a custom control for gathering the shipping information.  I have tabindexes set up, but the wizard always sets focus to the button instead of the next text box.  How can I change the tabindex of the button?

How to create nonclustered index which uses function on column?

Hi, Is there any way I can perform following. The ID column is of varchar type.   CREATE NONCLUSTERED INDEX idxID ON Table_1(CAST(ID AS BIGINT) );   Regards, Jigs

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



Cannot perform an aggregate function on an expression containing an aggregate or a subquery


How do i get around this???



sum(PODet.UnitCost*ReceiverDet.Qty2Receive) as TotalCost


Create SQL CLR function error



I am using VS 2008 - SQL Server Project to create User Defined Functions.

Build successfully and when I try to run it (F5), it prompts the error below:

Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131522. You need to restart SQL server to use CLR integration features.

a) The database is version 9.0 compatible.
b) .Net framework 3.5 SP1 installed.
c) Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) (Build 2600: Service Pack 3)

Kindly advise what I need to do to fix the error.

Thank you in advance.

using table name variable in create function


anybody help me, i wanna create function using table name parameter.

this is my function code

ALTER function [dbo].[CreateRecID]( @TableName varchar(100))
returns bigInt
declare @RecID bigint
declare @Sql varchar(2000)
set @Sql = (select case when max(RecID) is null then 0 else max(RecID) + 1 end as RecID from @TableName)

return @Sql

when i run this code

i got error "Must declare the table variable "@TableName".

I have already declare a @TableName Parameter.

can you show me the way to solve my problem?

Thanks so much

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.



SQL MAX() function selecting ALL columns and not only those in the GROUP BY clause


A school has classes. Each class has students. Each student may take up one or several courses.

One only table with thousands of rows contains the following columns (and some others):

  1. Student´s class
  2. Student id-number
  3. Student's course
  4. Student's grade (in the course)
  5. Some other rows. This table has about 25 rows

I need an only SELECT that brings ALL ROWS for each student's highest grade (grades are numbers, not letters).

In the example bellow, I have listed a fragment of the table and I have emphasized with bold and italic characters the rows that are supposed to be selected:

          Class       ID-number     Course     Grade     Row5   Row6   Row7... 

  1. Class-A         100001     Math&n
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