May 20, 2008 at 5:44 am
table def
create table #temp
(
[Id]int IDENTITY (1, 1) NOT NULL ,
[langCode] char(6),
[projectid] int,
[status] int,
[Order] int
)
As for sample data, its the one I posted before.
Is this enough or shall i give you more data?
May 20, 2008 at 5:46 am
While loops written in T-SQL don't run in the client. They run on the server, along with everything else.
gail,
Client does not mean that it will run on the client side, for example.
If I am using the GETDATE() function, Its using Server Side Code to get the functionally of this function which is calling as delegates in .NET.
But Suppose, I am declaring a user defined function and passing parameter to it, it will only execute on the server not necessary to call the server side code or In build code to use the server DLL.
Hope you can Now get the different between Cursor and While Loop.
Cheers!
Sandy.
--
May 20, 2008 at 5:46 am
As insert statemetns would be easier, as it's gonna take time to manually enter the data into the table.
No matter. I do prefer to test before posting though.
Give this select a try, see if it returns the right data. If so, I'll turn it into an update statement
SELECT ID, langCode, projectid,
ROW_NUMBER() OVER (PARTITION BY projectID, langCode ORDER BY projectID, langCode) AS TheOrder
FROM #temp
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 5:49 am
Sandy (5/20/2008)
If I am using the GETDATE() function, Its using Server Side Code to get the functionally of this function which is calling as delegates in .NET.
Um.... Getdate is not .net code. No delegates involved, no CLR
Hope you can Now get the different between Cursor and While Loop.
Oh, I'm fully aware of the differences and similarities between cursors and while loops.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 5:49 am
Hi Johann,
There is a concept called Crosstab query Which will help you a lot for this situation.
Can you please check this with Crosstab query?
Cheers!
Sandy.
--
May 20, 2008 at 5:54 am
Hi gail
that worked!
I am getting the correct order in the select
May 20, 2008 at 5:57 am
Sweet. Then this should do the update for you
UPDATE #temp SET [Order] = TheOrder
FROM (SELECT ID, ROW_NUMBER() OVER (PARTITION BY projectID, langCode ORDER BY projectID, langCode) AS TheOrder
FROM #temp) Sub
WHERE #temp.ID = Sub.ID
Let me know how long it runs please.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 5:57 am
Gail,
Um.... Getdate is not .net code. No delegates involved, no CLR
I knew it,
I just gave one Example for you,
As for SQL, GETDATE() is a Predefined Function some where the functionally is present and when we are calling its showing us the current date and time. when its calling the function firing the function to pointer concept which is called delegates in .NET.
gail, I knows you as a SQL Guru.
This is my respect for you,
Cheers!
Sandy.
--
May 20, 2008 at 6:02 am
Hi gail
that seems to have done it
9secs!
Can you give me an explanation of the code?
Thanks
Johann
May 20, 2008 at 6:08 am
Johann Montfort
--------------------------------------------
Can you give me an explanation of the code?
gail, Its your turn Now....:hehe::cool::w00t:;):P:D:)
hehehehehehe.................lolz
Cheers!
Sandy.
--
May 20, 2008 at 6:08 am
ROW_Number is a built-in ranking function that's new in SQL 2005.
the order by clause in it specifies which column or columns define the order for the row number
The partition by clause in there specifies when the row numbering restarts. If any of the columns change value, the row number restarts at 1.
Make sense?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 6:14 am
ROW_Number is a built-in ranking function that's new in SQL 2005.
the order by clause in it specifies which column or columns define the order for the row number
The partition by clause in there specifies when the row numbering restarts. If any of the columns change value, the row number restarts at 1.
Make sense?
gail, do you have time?
I also wants to learn Sql server 2005.......:hehe:..lolz
Cheers!
Sandy.
--
May 20, 2008 at 6:19 am
It sure looks like you could just use the ROW_NUMBER function to get the incremental counts that you want instead of looping through with a cursor or a WHILE loop (which will be somewhat faster, but not even remotely as fast as a good set-based solution).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2008 at 6:24 am
Sandy (5/20/2008)
I also wants to learn Sql server 2005.......:hehe:..lolz
Cheers!
Sandy.
Books Online is a good place to start.
Johann, I could probably shave a couple more seconds off with proper indexing. In case it's not quite fast enough yet. 😀 😉
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 6:36 am
Holy cow! You guys posted all that in the time it took me to read the first couple of posts. Slow down doggone it. You're making me look more stupid than usual!
:w00t:
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply