Creating and managing records with triggers

Question

Creating and managing records with triggers

Viewing 6 posts - 1 through 7 (of 7 total)

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

Brenna Duncan-211418 SSC Eights! Points: 827 More actions · Answer 1

Hi,

Does your unique ID have to be an incremental integer type? If not, I would just concatenate the values of SetCode, SubjectCode and YearCode to generate a unique field.

To get your first results...

SELECT FirstUniqueID = CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode),

SetCode,

SubjectCode,

YearCode

FROM Class

GROUP BY CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode)

To get your second results...

SELECT SecondUniqueID = CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode)+CONVERT(varchar,InstructorID),

FirstUniqueID = CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode)

SetCode,

SubjectCode,

YearCode,

InstructorID

FROM Class

GROUP BY CONVERT(varchar,SetCode) + CONVERT(varchar,SubjectCode)+CONVERT(varchar,YearCode)+CONVERT(varchar,InstructorID)

Now you have the same FirstUniqueID in each result.

Does this work for you?

Gary Dale Old Hand Points: 375 More actions · Answer 2

I think this is a *much* better way of achieving the same result!

I'm going to implement this and see how the dev likes it!

Many Thanks

noeld SSC Guru Points: 96590 More actions · Answer 3

if the table is big you may as well populate a computed column and create an index on it. You will not be sorry you did

* Noel

Gary Dale Old Hand Points: 375 More actions · Answer 4

Again this makes sense. The table can end up with ~20,000 rows which will certainly benefit from an indexed computed column.

Thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

The benefit is only there if you have a where condition on it and that the range of values is good (didn't read the whole thread).

Just my 2 cents.

noeld SSC Guru Points: 96590 More actions · Answer 6

the index in the computed column is a way of materialize the data and not calculate it on the fly. IT will definetly speed things up even if it is not in the where clause (which it should )

* Noel