May 5, 2010 at 4:53 pm
I have a table that has a non unique column called ClientId
What I need is that every time a new record is inserted a column gets incremented by one so each entry keeps track of which number client it is wthin the table
the first client inserted has a 1
the next client with the same client id has a 2
a different clientid gets inserted it has a 1
etc.....
if I did this with a trigger it'd basically be something like this
select @ClientNumber = MAX(isnull(ClientNumber, 0)) + 1 FROM Site where ClientId = @ClientId
update SITE SET ClientNumber = @ClientNumber where SiteId = @SiteId
which is fine and all but in order to do that I have to make clientnumber nullable or have it insert a dummy value
the other things I thought of were
-Use a Before Insert Trigger (haven't really used these before not sure the risk involved)
-use a computed column
the only issue with a computed column is that I can't figure out for the life of me how to make it persist because I have to use an aggregate that accesses the table data so it just won't fly. Ideally I'd like a computed column cause it's pretty quick and easy and I mean the value in the field will NEVER change so it'd be cool if there was a way to say HEY compute this ONCE on insert than don't ever compute it again...
Curious if anyone had any other thoughts?
May 5, 2010 at 6:54 pm
May 5, 2010 at 6:59 pm
Yea I figure someone was going to answer that I tried to explain it well but I guess I didn't or maybe there's some other identity I need
To quote myself again
What I need is that every time a new record is inserted a column gets incremented by one so each entry keeps track of which number client it is wthin the table
the first client inserted has a 1
the next client with the same client id has a 2
a different clientid gets inserted it has a 1
etc.....
So the table has an incrementing ID column called SITEID it has another ID called CLIENT ID
so lets say I insert 4 records with Client ID 2 and 2 records with ClientID 3
the table would look like this
SITEID ClientID ClientNum
1 2 1
2 2 2
3 2 3
4 2 4
5 3 1
6 3 2
what I'm trying to achieve is that ClientNum incrementing that's based off the count of clientids at the given time of the insert
so the first siteid inserted for client 2 is clientnum 1
the second siteid inserted for client 2 is clientnum 2
the first siteid inserted for client 3 is client num 1
it's not a straight unique identity increment
May 7, 2010 at 9:19 am
What you're describing is bad design ... it's not normalized and it's going to be confusing and have performance issues for the life of the database. You should have one table that defines site ID's, another table that defines client ID's and another table that establishes the relationship between the two.
If any given site ID can only possibly be related to a single client ID then you can do away with the third table. In this case the site table would simply have an additional column that is a foreign key for the client ID and that identity you're looking for isn't actually created in that table, but in the client table that's being referenced. If there is a possibility that a site would be related to more than one client it would be necessary to maintain that third table to define the relationships between sites and clients.
May 7, 2010 at 9:54 am
Since ClientID is not unqiue, then I assume the purpose of this ClientNum is to create a composite primary key. You can persist a calculated column using the PERSISTED clause, and from what I've read, you can even include a persisted calculted column as part of of the primary key. However, that could be problematic and is not necessay.
Instead, you can just insert your Client table using a select with an inline sub-query that returns the next ClientNum for the ClientID you're inserting.
It's unclear what purpose SiteID would serve, but if needed, it would just be an additional identity column.
create table #client
(
primary key (clientid, clientnum),
clientid int not null,
clientnum int not null,
SiteID int not null identity(1,1)
);
declare @clientid int;
select @clientid = 2;
insert into #client (clientid,clientnum)
select @clientid, (select isnull(max(clientnum),0)+1 from #client where clientid = @clientid);
select @clientid = 2;
insert into #client (clientid,clientnum)
select @clientid, (select isnull(max(clientnum),0)+1 from #client where clientid = @clientid);
select @clientid = 2;
insert into #client (clientid,clientnum)
select @clientid, (select isnull(max(clientnum),0)+1 from #client where clientid = @clientid);
select @clientid = 2;
insert into #client (clientid,clientnum)
select @clientid, (select isnull(max(clientnum),0)+1 from #client where clientid = @clientid);
select @clientid = 3;
insert into #client (clientid,clientnum)
select @clientid, (select isnull(max(clientnum),0)+1 from #client where clientid = @clientid);
select @clientid = 3;
insert into #client (clientid,clientnum)
select @clientid, (select isnull(max(clientnum),0)+1 from #client where clientid = @clientid);
select SiteID, ClientID, ClientNum from #client;
drop table #client;
SiteID ClientID ClientNum
----------- ----------- -----------
1 2 1
2 2 2
3 2 3
4 2 4
5 3 1
6 3 2
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 7, 2010 at 10:01 am
Yea I'm sorry I did such a bad job of setting up the data structures and explaining the scenario
There's a Client table that has a unique incrementing clientID
There's a SITE table that has a unique incrementing SiteId
sites can only belong to one client so it's all one to many
Client Num is basically just what number of site has been inserted grouped by clientid
We're using an ORM so I'll have to see if there's a way to inject Max(ClientNum) + 1 into the generated SQL but I'm thinking there isn't
I'll probably just use an after insert trigger to update the Num
that seems like the easiest
I tried to use persisted but there's no way to use MAX or Count or any of those in a column you are indicating as computed and persisted
May 7, 2010 at 10:31 am
shane94 (5/7/2010)
so I'll have to see if there's a way to inject Max(ClientNum) + 1 into the generated SQL but I'm thinking there isn'tI'll probably just use an after insert trigger to update the Num
that seems like the easiest
Why not just use an IDENTITY in the client table? I think you're making it much harder than it is.
May 7, 2010 at 11:18 am
this is a logical mistake; you should never try to keep a counter in a table; the counter should be derived in a view, instead; go with bteraberry's idea and pull everything out into two tables with identity fields, then you can use a view like this to get exactly what you are after, without triggers or wierd code to try and update the clientnum column:
results:
SiteStuff clientid SiteID clientstuff ClientNum
---------- --------- ------- ------------ --------------------
one.com 1 1 Manny 1
one.com 2 1 Moe 2
one.com 3 1 Jack 3
one.com 10 1 Larry 4
one.com 11 1 Shemp 5
one.com 12 1 Curley-Joe 6
two.com 4 2 Larry 1
two.com 5 2 Moe 2
two.com 6 2 Curley 3
three.com 7 3 Larry 1
three.com 8 3 Moe 2
three.com 9 3 Joe 3
code:
Create table Sites(
SiteID int not null identity(1,1) primary key,
SiteStuff Varchar(30) )
create table client (
clientid int not null identity(1,1) primary key,
SiteID int references Sites(SiteID),
clientstuff varchar(30));
GO
CREATE VIEW ClientSites
AS
Select
Sites.SiteStuff,
Client.*,
Row_Number() OVER (PARTITION BY Sites.SiteID ORDER BY Sites.SiteID,Client.clientid ) as ClientNum
FROM Sites
LEFT OUTER JOIN client ON Sites.SiteId = client.SiteId
GO
insert into sites (SiteStuff) select 'one.com' union all select 'two.com' union all select 'three.com'
insert into client(SiteID,clientstuff)
select 1,'Manny' union all select 1,'Moe' union all select 1,'Jack' union all
select 2,'Larry' union all select 2,'Moe' union all select 2,'Curley' union all
select 3,'Larry' union all select 3,'Moe' union all select 3,'Joe' union all
select 1,'Larry' union all select 1,'Shemp' union all select 1,'Curley-Joe'
select * from ClientSites
Lowell
May 7, 2010 at 11:20 am
I don't think you're understanding the issue
Lets say I have some blocks these blocks come in 3 different colors
Each time I acquire a block that block has it's OWN unique ID but I also want to keep track of what number of block that color is that I received
SO
someone gives me a red block
that block is now
BLOCK 1 (DB ID only important to the DB)
RED BLOCK 1 (concept important to the client)
someone gives me a second red block
BLOCK 2
RED BLOCK 2
someone gives me a blue block
BLOCK 3
BLUE BLOCK 1
someone gives me a green block
BLOCK 4
GREEN BLOCK 1
someone gives me a blue block
BLOCK 5
BLUE BLOCK 2
etc...
so now when the client is looking at all the blue blocks they will say HEY we want blue block 1
in the DB this is BLOCK 3 but relative to the count of blue blocks this is blue block 1
when they are looking at green blocks they will just see
Green block 1
Greek Block 2
etc...
they won't see that ID of the block in the DB
but just what number green block has been inserted
it's a purely for client/user friendly purposes for referring to things
To do this with an identity column I'd have to create a new table every time I acquired a new color of block which isn't really a reasonable solution
May 7, 2010 at 11:25 am
Lowell (5/7/2010)
this is a logical mistake; you should never try to keep a counter in a table; the counter should be derived in a view, isntead; go with bteraberry's idea and pull everything out into two tables with identity fields, then you can use a view like this to get exactly what you are after, without triggers or wierd code to try and update the clientnum column:results:
SiteStuff clientid SiteID clientstuff ClientNum
---------- --------- ------- ------------ --------------------
one.com 1 1 Manny 1
one.com 2 1 Moe 2
one.com 3 1 Jack 3
one.com 10 1 Larry 4
one.com 11 1 Shemp 5
one.com 12 1 Curley-Joe 6
two.com 4 2 Larry 1
two.com 5 2 Moe 2
two.com 6 2 Curley 3
three.com 7 3 Larry 1
three.com 8 3 Moe 2
three.com 9 3 Joe 3
code:
Create table Sites(
SiteID int not null identity(1,1) primary key,
SiteStuff Varchar(30) )
create table client (
clientid int not null identity(1,1) primary key,
SiteID int references Sites(SiteID),
clientstuff varchar(30));
GO
CREATE VIEW ClientSites
AS
Select
Sites.SiteStuff,
Client.*,
Row_Number() OVER (PARTITION BY Sites.SiteID ORDER BY Sites.SiteID) as ClientNum
FROM Sites
LEFT OUTER JOIN client ON Sites.SiteId = client.SiteId
insert into sites (SiteStuff) select 'one.com' union all select 'two.com' union all select 'three.com'
insert into client(SiteID,clientstuff)
select 1,'Manny' union all select 1,'Moe' union all select 1,'Jack' union all
select 2,'Larry' union all select 2,'Moe' union all select 2,'Curley' union all
select 3,'Larry' union all select 3,'Moe' union all select 3,'Joe' union all
select 1,'Larry' union all select 1,'Shemp' union all select 1,'Curley-Joe'
select * from ClientSites
But the solidarity of this seems to be lacking..
If anything gets deleted than the row numbers all shift also with a trigger I store the number 4 in the DB and it's always the number 4 it's there it's solid it's quick to access there's no joins or counting or views or row_numbers it's just straight up
select ClientNum FROM Sites where SiteID = 5
BAM
And it's an insert trigger that runs once the after math of that is non existent the value just gets created and there's nothing more to worry about ever
May 7, 2010 at 11:35 am
Again, you're making this way harder than it needs to be. Using triggers to hard code the table when you can simply use the capabilities of T-SQL to query is a terrible idea. For example, here I show how you can get the data you want using your colored block example:
declare @t_table table
(BlockINT IDENTITY,
BlockColorVARCHAR(10),
BlockIDINT
)
insert into @t_table select 'RED', 1
insert into @t_table select 'RED', 2
insert into @t_table select 'BLUE', 1
insert into @t_table select 'GREEN', 1
insert into @t_table select 'BLUE', 2
select Block,
BlockColor,
BlockID,
TheValueYouAreTryingToGet = ROW_NUMBER() OVER(PARTITION BY BlockColor ORDER BY Block)
from @t_table
order by Block
May 7, 2010 at 11:51 am
bteraberry (5/7/2010)
Again, you're making this way harder than it needs to be. Using triggers to hard code the table when you can simply use the capabilities of T-SQL to query is a terrible idea. For example, here I show how you can get the data you want using your colored block example:
declare @t_table table(BlockINT IDENTITY,
BlockColorVARCHAR(10),
BlockIDINT
)
insert into @t_table select 'RED', 1
insert into @t_table select 'RED', 2
insert into @t_table select 'BLUE', 1
insert into @t_table select 'GREEN', 1
insert into @t_table select 'BLUE', 2
select Block,
BlockColor,
BlockID,
TheValueYouAreTryingToGet = ROW_NUMBER() OVER(PARTITION BY BlockColor ORDER BY Block)
from @t_table
order by Block
Yea but for
insert into @t_table select 'RED', 2
you're operating off the premise that you know this is red block number 2 each time a block is inserted it doesn't know what number of that color it is unless it checks for the max block number
I realize that you're using
TheValueYouAreTryingToGet = ROW_NUMBER() OVER(PARTITION BY BlockColor ORDER BY Block)
to get the block number but that just seems excessive
I mean each block always maintains the exact same colored block number it never changes ever so deriving that value every single time I query that table seems like unnecessary cycles on the DB
red block 2 is always red block 2 so why not just store that in a column as a discrete value than call it good
I mean if I get to the point where the table has 5 million rows with 1.3 million blocks than in order to get color number i'll have to row number over that whole table instead of just a straight query against a discrete value in a column
Maybe I'm overstating the performance implications but it just seems like if my options are
1) a trigger that runs once and inserts a value and now I can forever just query straight against the column
2) use a view that calculates this exact same unchanging value every time it queries
I'd rather go with number 1 since it's a done once work forever
and lets say for SOME reason one of the middle blocks gets deleted that means every single block number after that will get decremented by one
Blocks shouldn't be deleted but still basing it off a row_number calculations binds me to never being able to delete any rows ever cause it'll screw everything up
May 7, 2010 at 12:06 pm
It's just bad design. (I would suggest doing some reading on normalization.) Basically, unless there is a compelling performance reason to do so, never store data in the table that can be figured out through a query. There are situations where it is necessary for performance reasons, but judging by your questions I am highly doubtful that you are working on such a large and/or complex system to require this. More likely you'll save yourself about 0.005 seconds on your query, but the question is at what cost?
I work in an extremely large environment and we don't use triggers unless absolutely necessary. Now this is a matter of preference ... other shops use them regularly. One of the issues is that they tend to be harder for other people to support and they can be easily forgotten. Keeping logic in a stored procedure makes it easy for anyone with experience to look and see and figure things out. Triggers, especially when created by people with less experience, can be difficult to troubleshoot and they may not fire exactly when you think they will.
So when I look at what you're asking, I see a very easy straightforward solution that adheres to solid design principals on one hand and a sort of concocted solution that is dicey at best on the other hand. My impression is that you've presumed the trigger approach from the beginning without really listening to what has been told to you by people with years of experience. You're certainly free to pursue whatever avenue you wish, but do realize that some of us went through trial and error learning on issues such as these in previous decades. We're just trying to help save you some frustration later.
Good luck to you whatever path you choose.
May 7, 2010 at 12:11 pm
shane94 (5/7/2010)
bteraberry (5/7/2010)
I'd rather go with number 1 since it's a done once work foreverand lets say for SOME reason one of the middle blocks gets deleted that means every single block number after that will get decremented by one
Blocks shouldn't be deleted but still basing it off a row_number calculations binds me to never being able to delete any rows ever cause it'll screw everything up
You would be relating entries based on the block ID and not the calculated ROW_NUMBER() column so this would be irrelevant. This would be the case whether you were deleting records (which I don't really like) or using an IsActive bit field. Your block ID would be an IDENTITY and would refer to that record for all time and would never change. It's also the most likely candidate to establish the clustered index, so for all reasons that is the column you would want to join on.
May 7, 2010 at 12:17 pm
I have been listening this is all in the pursuit of knowledge
And no a colleague was suggesting the triggers and I was trying to find a solution that didn't involve triggers of any sort...
I mean typically I try to stay away from triggers and even sprocs as much as I possibly can because they create a bad mixing of layers... Which is a completely different discussion 🙂
Which is where I was trying to get computed columns to work for me...
I guess I'm just still not totally sold on the idea of
"Basically, unless there is a compelling performance reason to do so, never store data in the table that can be figured out through a query. "
From what I've read giving SQL as little as possible to "figure out" is always the preferred approach...
I mean yea this one tiny detail of figuring out might be asinine but layer after layer after layer of "figuring" out adds up
It seems odd to make SQL figure out the exact same thing over and over again
it seems like having a column that I want the number 6 in but instead of just storing a 6 I have SQL pull 2*3 every single time I query that table
even though I could run a single task that says hey this will always be 6 forever and ever so you don't ever have to figure that out ever again it's just always a 6
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply