Computed columns: max from other column for the same ID

Question

Computed columns: max from other column for the same ID

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 1

HowardW (7/25/2012)[hr
Hmm, not sure I understand what you're asking. Yes, it would update if you changed a column in that row as any other deterministic function would - try it and see.

Maybe we don't understand each other because I don't understand how a function works (ya, I am that low).

For me, the @input means you have to give it "arguments" (I am guessing that I am wrong here), so how the tables know that @input1 is col1, @input2 is col2, etc for all the sixteen columns that it needs to get the max value from?

DennisPost SSCrazy Points: 2691 More actions · Answer 2

I know this is an old thread, but I want to share anyway.

The function uses schemabinding to make the computed column persisted.

USE TempDB

GO

IF OBJECT_ID('TempDB..#Test') IS NOT NULL

DROP TABLE #Test

GO

IF OBJECT_ID('dbo.ufn_GetHighestVal', 'FN') IS NOT NULL

DROP FUNCTION dbo.ufn_GetHighestVal

GO

CREATE FUNCTION ufn_GetHighestVal

(

@Val1 TinyInt,

@Val2 TinyInt = NULL,

@Val3 TinyInt = NULL,

@Val4 TinyInt = NULL,

@Val5 TinyInt = NULL,

@Val6 TinyInt = NULL,

@Val7 TinyInt = NULL,

@Val8 TinyInt = NULL

)

RETURNS TinyInt

WITH SCHEMABINDING

AS

BEGIN

DECLARE @RetVal TinyInt

SELECT @RetVal = MAX(V.Val) FROM (VALUES (@Val1),(@Val2),(@Val3),(@Val4),(@Val5),(@Val6),(@Val7),(@Val8)) V(Val)

RETURN ISNULL(@RetVal, 0)

END

GO

CREATE TABLE #Test

(

Val1 TinyInt,

Val2 TinyInt,

Val3 TinyInt,

Val4 TinyInt,

Val5 TinyInt,

Val6 TinyInt,

Val7 TinyInt,

Val8 TinyInt,

CompCol AS dbo.ufn_GetHighestVal(Val1, Val2, Val3, Val4, Val5, Val6, Val7, Val8) PERSISTED

)

INSERT #Test

SELECTTOP 10

ABS(CHECKSUM(NEWID()) % 255),

ABS(CHECKSUM(NEWID()) % 255)

FROMsys.all_columns C1, sys.all_columns C2

SELECT* FROM #Test

GO

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 3

Why not just use MAX(columname) OVER () in any queries you have?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DennisPost SSCrazy Points: 2691 More actions · Answer 4

DennisPost

SSCrazy

Points: 2691

April 23, 2015 at 12:50 am

#1792931

Could you please demonstrate.

Dwain Camps SSC Guru Points: 86948 More actions · Answer 5

Jeff Moden (4/22/2015)
Why not just use MAX(columname) OVER () in any queries you have?

I would have suggested a schema-bound iTVF instead of a sUDF, as in the end you'll be performing this over rows anyway. Just return a TABLE with one column (the max value).

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1004312 More actions · Answer 6

DennisPost (4/23/2015)
Could you please demonstrate.

Actually, I can't because I screwed up and misread. This thread is about a "GREATEST" function and I thought it was something else entirely. My most humble apologies. :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DennisPost SSCrazy Points: 2691 More actions · Answer 7

DennisPost

SSCrazy

Points: 2691

April 24, 2015 at 4:25 am

#1793216

LOL Jeff.

RBAR is good when reading posts. 😛

DonlSimpson SSCertifiable Points: 7043 More actions · Answer 8

DennisPost (4/24/2015)
LOL Jeff.
RBAR is good when reading posts. 😛

+1 :hehe:

Don Simpson

I'm not sure about Heisenberg.