Some way to create a deterministic computed column that only has to count once?

Question

Some way to create a deterministic computed column that only has to count once?

Viewing 7 posts - 16 through 21 (of 21 total)

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

shane94 (5/7/2010)
From what I've read giving SQL as little as possible to "figure out" is always the preferred approach...

Throw away whatever you read that said that. That is incorrect and will lead to horrendous design. There are times (such as reporting applications, etc) that require a degree of de-normalization in order to perform adequately. That, however, is not the standard. And there are times in which a calculation is complex enough and the results are queried often enough that it is wise to store the results of the calculation in the table. This is just not one of those cases. ROW_NUMBER is extremely fast and the calculation isn't internal to the record ... it's exactly the sort of set-based logic that SQL Server is designed to do.

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Shane N Ten Centuries Points: 1323 More actions · Answer 2

bteraberry (5/7/2010)
shane94 (5/7/2010)
From what I've read giving SQL as little as possible to "figure out" is always the preferred approach...
Throw away whatever you read that said that. That is incorrect and will lead to horrendous design. There are times (such as reporting applications, etc) that require a degree of de-normalization in order to perform adequately. That, however, is not the standard. And there are times in which a calculation is complex enough and the results are queried often enough that it is wise to store the results of the calculation in the table. This is just not one of those cases. ROW_NUMBER is extremely fast and the calculation isn't internal to the record ... it's exactly the sort of set-based logic that SQL Server is designed to do.

hmmmmmmmmmmm interesting 🙂

I'll have to follow that path a little and see what I can do..

We're using an ORM so I'm not totally positive how well that will play in with this concept and all so we'll see..

Appreciate all the good info..

Your Name Here SSCrazy Points: 2344 More actions · Answer 3

I'm not sure if anybody is still following this thread but I came across something recently that might apply:

DECLARE @x TABLE (

[RecID] INT IDENTITY(1,1)

,[Name] VARCHAR(11)

,[Flag] TINYINT DEFAULT(0)

,[ProcessRecID] INT NULL

)

DECLARE @recid INT

INSERT INTO @x ([Name])

VALUES

('Apple')

,('Banana')

,('Cherry')

,('Date')

,('Elderberry')

,('Fig')

,('Grape')

,('Huckleberry')

UPDATE @x

SET [Flag] = 1

WHERE [Name] IN ('Apple', 'Banana', 'Fig')

--SELECT * FROM @x

SELECT @recid = 0

UPDATE @x

SET @recid = [ProcessRecID] = @recid + 1

WHERE [Flag] = 1

SELECT * FROM @x

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

You're describing something that's known as Quirky Update. To get it right, you need to follow some rules or the results can't be guaranteed. Check the following article describing it.

http://www.sqlservercentral.com/articles/T-SQL/68467/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

OK, if you need it, try

create table #client

(

SiteID int not null,

clientid int not null,

clientnum int not null

);

-- insert into empty #client

with inp as( -- test data

select top 100

SiteID = row_number() over(order by object_id) % 20

,ClientID =row_number() over(order by object_id) % 14

from sys.all_objects x

)

insert into #client (SiteID, Clientid, clientnum)

select SiteID

, Clientid

,(select isnull(MAX(clientnum),0) from #client c where inp.Clientid = c.Clientid)

+ row_number() over (partition by Clientid order by SiteID)

from inp;

-- and run above query once again when #client isn't empty

select * from #client

order by Clientid, clientnum;

drop table #client;

VSP SSC-Addicted Points: 467 More actions · Answer 6

-- Here is SQL SELECT query to use when inserting a new row into the table.

-- But ClientNum will remain accurate as long as the rows are not deleted from the table.

-- If the rows are deleted from the table, use the view/query given below that provides accurate ClientNum.

-- this may not be an optimal solution, but should work

-- ?site_id and ?client_id are the values for SiteID and ClientID columns in the new row you are adding

select ?site_id

,?client_id

,case when total is null then 1 else total + 1 end as ClientNum

from (select ?site_id as SiteID

,?client_id as ClientID

) NEW_RECORD

left outer join (select ClientID

,max(ClientNum) as total

from your_db_table -- this is table that has previously loaded data

where ClientID = ?client_id

group by ClientID

) TMP on TMP.Client_ID = NEW_RECORD.Client_ID

-- If the rows are deleted from the table, the following view will provide the accurate ClientNum values

select SiteID

,ClientID

,row_number() over(partition by NEW_RECORD.client_id order by ClientNum) as revised_ClientNum

from your_db_table

Hope this helps.

VSP SSC-Addicted Points: 467 More actions · Answer 7

Here is another alternative

Add a date column (REC_ADD_DATE) to the table, instead of ClientNum. Default the date column to current_date. This date will then be used to determine the order in which the rows are added.

Build a view on the table with the following SELECT query..to get the derived ClientNum column

select SiteID

,ClientID

,row_number() over(partition by ClientID order by REC_ADD_DATE) as ClientNum

from your_db_table -- this is the table the data is loaded into