June 11, 2011 at 10:28 pm
I'm using sql to build an activity stream. I have a table that holds activity items - to simplify the example, let's say the table has 3 columns:
CREATE TABLE ActivityItems
(
ItemID int IDENTITY(1,1),
ItemTitle varchar (50),
ItemURL varchar(255)
)
When inserting a row into this table, I would like to insert the Identity value into both the ItemID column and into a portion of the ItemURL column. So, for example, if the ItemID value was 5, I would want the ItemURL to be 'http://mysite/default.aspx?ID=5'. But since the value of the ItemID column is auto-incremented, I don't know how to fetch that value and insert it into the ItemURL column at the time of insert. Because not every URL in my table is going to be built this way, it's important that I be able to handle it in the insert statement.
I tried using @@Identity, which returned the previous row's identity, and adding 1 to it, as in:
INSERT INTO ActivityItems (ItemTitle, ItemURL)
VALUES ('New Item', 'http://mysite/default.aspx?ID=' + CAST(@@Identity + 1 as varchar))
It worked as long as I was inserting only one item. But if insert multiple items as once, it doesn't work.
Is there a way to accomplish this?
June 12, 2011 at 2:52 am
You cant really do that, maybe try a default column value....
But this seems like a wrong design to me. Ideally you would not be storing the url directly, but doing that work on the select that pulls the data out. What if you change the site from mysite.com to yoursite.com ?
You will have to update 100's of rows. Not only that but you have to think about the storage overhead to.
The cast to varchar ? varchar(what ?) the default is (1).
In anycase @@Identity is not recommended , use Scope_indentity , though that still has issues with parallelism.
June 12, 2011 at 8:14 am
I took a look at your website, Dave. It's nice and simple yet informative.
My only suggestion comes from when I first opened the site which was, "Where's the Title of this page?". After looking through these tired ol' eyes for another minute, I finally found it. The Black letters on a Dark Blue background are really hard to see even for someone who isn't color blind. My recommendation is that you make the title White .
--Jeff Moden
Change is inevitable... Change for the better is not.
June 12, 2011 at 8:38 am
Consider a calculated column:
create table t(
i int identity (1, 1),
data char(1),
url as 'http://www.blah.com/' + cast(i as varchar)
)
insert t (data) select 'a' union select 'b' union select 'c'
select * from t
Edit: sorry, missed the not every row bit. Odd that not every row would work the same way, though....
June 12, 2011 at 9:21 am
Thanks Jeff, nice suggestion. Done.
June 12, 2011 at 12:19 pm
Thanks for the replies. I realize the design of the table is quite de-normalized, in fact, there are a number of other columns I left out to simplify the example. I'm building an activity stream, and my end goal here is performance, performance, performance. In fact, if I were building this as a stand alone application, I might even be looking at a no-SQL solution, but the application integrates with Sharepoint and some LOB systems that have SQL Server back ends. I do have some other ways of making sure that the data is consistent. For example, I am indeed pulling the ItemURL value, minus the parameter, from another table, and I've got a trigger on that table that will update the ActivityItems table in the event of a change. But I want to minimize the number of joins in my select statement, which is why I'm storing as much data as I can in this single table - the query needs to be dead simple and fast.
As for why the ItemURLs are not built the same way for every item, it's because some of the activities are pulled from other systems and the ItemURL for those items follows a different logic. So I don't think I can use a calculated column. That said, it is not 100% imperative that I make use of the ItemID column. For example, I could add a ThreadID column to the table and use the following insert statement:
Declare @ThreadID varchar(36)
Set @ThreadID = Select NewID()
INSERT INTO ActivityItems (ItemTitle, ThreadID, ItemURL)
VALUES ('New Item', @ThreadID, 'http://mysite/default.aspx?ID=' + @ThreadID)
But as with my @@Identity example, I don't think this will work if I'm inserting multiple values simultaneously as in:
INSERT INTO ActivityItems (ItemTitle, ThreadID, ItemURL)
SELECT t.Title, @ThreadID, 'http://mysite/default.aspx?ID=' + @ThreadID
FROM AnotherTable t
June 13, 2011 at 11:45 am
I apologize for this lame solution, but you could insert all of the new records first, leaving the id append off of ItemURL column, then go back and append the id. (You could use a special character as a placeholder to look for rows that don't already have an id as part of the url string, or you could look for the last character in the url string being '='). Probably too slow for your purposes, but maybe it will give the heavy duty problem solvers an idea 🙂
June 13, 2011 at 12:20 pm
why don't you do the insert within a stored procedure and you can do all the logic within the proc?
I don't believe there is any way of doing what you are proposing otherwise.
-------------------------------------------------
Will C,
MCITP 2008 Database Admin, Developer
June 13, 2011 at 1:09 pm
I agree 1000% with Dave that the database is not the place for this info. You will be kicking yourself when you either change domains or at some point add some folder structure to your site. You will most likely at some point add some folders when your site gets large because it is totally unmanageable to have hundreds of files in a single folder. Just use some basic string concatenation in your query.
select 'http:mysite/default.aspx?id=' + cast(ItemID as varchar(10)) as url
The advantages are innumerable. You change the domain/folder location at will. You don't have the entire domain stored on every single record and you only have the identity column's value in a single place.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 13, 2011 at 2:51 pm
Thanks again for all the replies. I think the idea of a stored proc that includes the insert statement followed by an update statement might work. It seems like it might be my only choice if I want the data all in this one table.
I am interested to hear more on the issue that Dave and Sean brought up. My perspective on it is that if I had a truly normalized design that I would end up with many joins in my select statement. As I said, I left out a number of columns to simplify the question. In reality, I'd have to join to a number to tables to get the url, name and photo of the author, etc. My feeling is that, in the event I change domains or otherwise change the url (something that would not happen frequently), I'd rather do an update query that takes a couple of minutes to execute - if it means improving the performance of my select query by even a second. It's all about optimizing the end user experience.
As for the issue of storage space, I'm not sure I understand the concern. It's just one weld page with a webpart on it that gets filtered by ItemID. There won't be a page for each item.
June 13, 2011 at 2:59 pm
bcampbell-1136357 (6/13/2011)
Thanks again for all the replies. I think the idea of a stored proc that includes the insert statement followed by an update statement might work. It seems like it might be my only choice if I want the data all in this one table.I am interested to hear more on the issue that Dave and Sean brought up. My perspective on it is that if I had a truly normalized design that I would end up with many joins in my select statement. As I said, I left out a number of columns to simplify the question. In reality, I'd have to join to a number to tables to get the url, name and photo of the author, etc. My feeling is that, in the event I change domains or otherwise change the url (something that would not happen frequently), I'd rather do an update query that takes a couple of minutes to execute - if it means improving the performance of my select query by even a second. It's all about optimizing the end user experience.
As for the issue of storage space, I'm not sure I understand the concern. It's just one weld page with a webpart on it that gets filtered by ItemID. There won't be a page for each item.
It appears there a lot of reasons that we can't see that make your solution the most viable to your situation. Only you can truly answer that. We just provided our opinions based on our experiences. Probably the most direct way is to either immediately run an update after your insert statement or do an instead of insert trigger. That to me seems like a lot of extra hoops for nothing more than replicating the identity column to a second field. I get your point about some of the other stuff being a bit complicated and maybe you fill that field at creation with that part and then just add the ID like in my example. Honestly storage space is not likely a huge concern unless you have millions of rows.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2011 at 2:52 am
I'd rather do an update query that takes a couple of minutes to execute - if it means improving the performance of my select query by even a second. It's all about optimizing the end user experience.
IMO , you are worrying unduly about this issue.
The overhead of pulling the bytes from the disk, and all the associated memory usage that goes with that, will probably be more than the cost of the join for a normalised design.
Denomalization has its place , but from what you have said , i wouldnt consider it here.
June 14, 2011 at 3:04 am
The instead of insert won't have the identity values, though, since the "inserted" table will return zeros for the identity column. You'd have to turn identity_insert on and generate your own ID's, but that defeats the purpose.
You'd need a regular insert trigger instead, or use an output clause and then do an update (but then why bother with the IOI trigger?)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply