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
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.
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)
I'm trying to execute the following:
WHERE STATE_CODE5 IN ('IL','NV','VA','NH','HI')
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
How do i get around this???
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.
Notes: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.
anybody help me, i wanna create function using table name parameter.
this is my function code
ALTER function [dbo].[CreateRecID]( @TableName varchar(100))
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)
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
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
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.
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):
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: