Thanks for your input everybody!

I should probably write my initial question something like this:

I have a totaly theoretical question regarding merge statement. It can be used for 'upsert' functionality where...

Wow, such negative comments... As I wrote before, it was purely curiosity that made me ask the question how to convert my if exists update ... else insert ... into...

@SeanLange: in reality it is a normal table, I just used temp table in the example script, sorry about that.

My way of doing it now is like this:

create...

@luis Cazares:

No no, thank you 🙂

@Eirikur Eiriksson: I learned a lot from your solution (never saw 'LAG' and 'ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'...

Ughhhh... An explanation, yes please 🙂

@scottpletcher - I tried it now and it seems that both clustered and nonclustered indexes are running for about the same time.

Thanks!

I wrote a CLR function instead:

public static class FingerPrint

{

[SqlFunction(DataAccess = DataAccessKind.Read,SystemDataAccess=SystemDataAccessKind.Read)]

...

Well the point is, that my application is under frequend audits by our client and the random number generator is an important part of it (i am creating lottery games)...

Thanks everybody for reply!

I'm pretty sure my c# method is ok (code taken from here: http://msdn.microsoft.com/en-us/magazine/cc163367.aspx), so I just deployed it to Sql:

[SqlFunction]

public static int RndBetween(int minValue, int maxValue)

{

if (minValue...

Jeff, your solution works better, thanks.

I am using Microsoft SQL Server Management Studio 10.50.2500.0, @@version:

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)

Jun 17 2011 00:54:03

Copyright (c) Microsoft...

You should probably use pivot operation (I hope somebody will post an example), but this should also work:

use tempdb

declare @t table (

Skill varchar(10) not null,

BU1 int not null,

BU2 int not...

Can you describe what you are trying to do here?

A little side note - to drop all tables you can also use sp_msforeachtable procedure, like:

sp_msforeachtable 'drop table ?'

If your tables are connected with foreign keys, then you would have...

I would suggest that you never test for such an inequality in such a manner. Always test for the equality and if that test "fails", then it must be...