Question about locks

In short, we are sending several statements to the database from one source
(my application). When this application is running nobody else will be
accessing the database.
If I do a table lock isnâ't it still releasing that lock after each statement?
I ask because each update I send to the database will be effecting only one
row at a time so I donâ't think I would get performance gains because I lock
the table for one record update then lock it again etc etc. The manager
itself could most likely do that faster anyway.What do you mean by 'doing a table lock'? Do you mean using a lock hint?
Which hint? What is the statement that is affecting the data?
Have you started an explicit transaction with BEGIN TRAN?
We need to see exactly what you are doing to lock the table to be able to
answer your questions, but in general, it is better to let SQL Server's lock
manager handle all the necessary locking automatically.
Why are you updating only one row at a time? If you are interested in
performance, you might consider figuring out a way to process all the rows
at once. That's what a relational database is good at.
--
HTH
Kalen Delaney, SQL Server MVP
"Sean" <Sean@.discussions.microsoft.com> wrote in message
news:5C5D6009-F702-4100-BCE1-F194E3695DAC@.microsoft.com...
> In short, we are sending several statements to the database from one
> source
> (my application). When this application is running nobody else will be
> accessing the database.
> If I do a table lock isn't it still releasing that lock after each
> statement?
> I ask because each update I send to the database will be effecting only
> one
> row at a time so I don't think I would get performance gains because I
> lock
> the table for one record update then lock it again etc etc. The manager
> itself could most likely do that faster anyway.
>sql

Question about Grouping rows in Select statements with joins

Hi
I am trying to use the select statement to return rows which have a MAX
date and then associate the date with a Title field in the #c table. At
the moment when I add the Title field to the Select list I am getting
the latest date for each Title. How do I exclude the Title field form
the GROUP BY clause but display it with the date in the select list?
/* correct number of records but without the required Title field */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp)
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
DROP TABLE #h
DROP TABLE #c
GO
/* adding the Title field gives me extra rows. I only want the
Title associated with the latest date */
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID, #c.Title
DROP TABLE #h
DROP TABLE #c
GO
Cheers
j055try
CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
CREATE TABLE #c(ContentID int,Title varchar(100))
INSERT INTO #c VALUES (1,'First Item')
INSERT INTO #c VALUES (2,'Second Item')
INSERT INTO #h VALUES (10,1,'20050301')
INSERT INTO #h VALUES (10,2,'20050302')
INSERT INTO #h VALUES (11,1,'20050304')
INSERT INTO #h VALUES (11,2,'20050303')
select email, max_date, title from (
SELECT #h.EmailID email, MAX(#h.DateStamp) max_date
FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
GROUP BY #h.EmailID
) t
inner join #h on #h.EmailID=t.email and #h.DateStamp = t.max_date
inner join #c on #h.ContentID=#c.ContentID
DROP TABLE #h
DROP TABLE #c
GO
"j055" wrote:

> Hi
> I am trying to use the select statement to return rows which have a MAX
> date and then associate the date with a Title field in the #c table. At
> the moment when I add the Title field to the Select list I am getting
> the latest date for each Title. How do I exclude the Title field form
> the GROUP BY clause but display it with the date in the select list?
>
> /* correct number of records but without the required Title field */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp)
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID
> DROP TABLE #h
> DROP TABLE #c
> GO
> /* adding the Title field gives me extra rows. I only want the
> Title associated with the latest date */
> CREATE TABLE #h(EmailID int,ContentID int,DateStamp datetime)
> CREATE TABLE #c(ContentID int,Title varchar(100))
> INSERT INTO #c VALUES (1,'First Item')
> INSERT INTO #c VALUES (2,'Second Item')
> INSERT INTO #h VALUES (10,1,'20050301')
> INSERT INTO #h VALUES (10,2,'20050302')
> INSERT INTO #h VALUES (11,1,'20050304')
> INSERT INTO #h VALUES (11,2,'20050303')
> SELECT #h.EmailID, MAX(#h.DateStamp), #c.Title
> FROM #h INNER JOIN #c ON #h.ContentID = #c.ContentID
> GROUP BY #h.EmailID, #c.Title
> DROP TABLE #h
> DROP TABLE #c
> GO
> Cheers
> j055
>