rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on thi
s
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should retu
rn
> row counts in the table. There is no issue with all data manipulation on t
his
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100

rows column in sysindexes table got overflow

Hi, does anyone have the overflow issue with the column, rows in the
sysindexes table? We have table with over 3 billions records and it throws
error 8115 overflow error when I double click the table, which should return
row counts in the table. There is no issue with all data manipulation on this
table even with count_big. When I checked the sysindexes table for this
table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
always max number of integer (even after new insert).
--
hm100This is because the procedure used by EM to return this information
(sp_MStablespace) is trying to force a bigint into an int variable using the
following code
SELECT @.rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @.id
This is no longer used by management studio in SQL2005
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should
> return
> row counts in the table. There is no issue with all data manipulation on
> this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100|||"double-click the table"... Sounds like some bug in the tool you are using?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"hm100" <hm100@.discussions.microsoft.com> wrote in message
news:A4097666-D08C-449D-B01D-99C379F28EDB@.microsoft.com...
> Hi, does anyone have the overflow issue with the column, rows in the
> sysindexes table? We have table with over 3 billions records and it throws
> error 8115 overflow error when I double click the table, which should return
> row counts in the table. There is no issue with all data manipulation on this
> table even with count_big. When I checked the sysindexes table for this
> table, the rowcnt (bigint) has correct numbers of rows while rows (int) is
> always max number of integer (even after new insert).
> --
> hm100sql

ROWNUM In Oracle

Is there any way to implement ROWNUM in Oracle to select row no.
Is there any way to select the row no when retrieving records as
1 Tv
2 Fridge
3 RadioHi,
There is no concept of Rownum in sql server.
But you could write ur own query to get the serial number.
Use the below script as sample:-
create table item(item_code varchar(05))
go
insert into item values('a1')
insert into item values('a2')
insert into item values('a3')
insert into item values('a4')
go
SELECT (SELECT COUNT(i.item_code)
FROM item i
WHERE i.item_code >= o.item_code ) AS RowID,
item_code
FROM item o
ORDER BY RowID
Thanks
Hari
SQL Server MVP
"Renjith" <Renjith@.discussions.microsoft.com> wrote in message
news:3BDAC82C-4AB5-4631-ABAE-AB693B0E1312@.microsoft.com...
> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
There is not a ROWNUM function in SQLServer 2000 using an identity column is
usually the alternative. If you want to rank your values then you could use
a
construct like:
e.g
SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
col1, col2
FROM MyTable M
These links may also help.
http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
http://www.microsoft.com/resources/...r />
0761.mspx
http://www.microsoft.com/sql/evalua...pare/oracle.asp
John
"Renjith" wrote:

> Is there any way to implement ROWNUM in Oracle to select row no.
> Is there any way to select the row no when retrieving records as
> 1 Tv
> 2 Fridge
> 3 Radio|||Hi
If it is a big table then the count(*) as inner query will create
performance problem '
"John Bell" wrote:
> Hi
> There is not a ROWNUM function in SQLServer 2000 using an identity column
is
> usually the alternative. If you want to rank your values then you could us
e a
> construct like:
> e.g
> SELECT ( SELECT COUNT(*) FROM MyTable T WHERE t.id <= M.id ) AS Rank,
> col1, col2
> FROM MyTable M
> These links may also help.
> http://vyaskn.tripod.com/ oracle_sq...ent
s.htm
> http://www.microsoft.com/resources/.../>
/c0761.mspx
> http://www.microsoft.com/sql/evalua...pare/oracle.asp
> John
> "Renjith" wrote:
>|||Hi
It may, and indexing would reduce the problem.
You can also do something like:
CREATE TABLE MyTest ( id int not null identity(1,1), val char(1))
INSERT INTO MyTest ( val )
SELECT 'A'
UNION ALL SELECT 'B'
UNION ALL SELECT 'C'
UNION ALL SELECT 'D'
UNION ALL SELECT 'E'
DELETE FROM MyTest where val = 'C'
SELECT m.id, COUNT(*) as Rank, m.val
FROM MyTest m
JOIN MyTest r ON R.id <= M.id
GROUP BY m.id, M.val
ORDER BY 2
Another alternative would be do deligate the numbering to the client.
John
"Renjith" wrote:
> Hi
> If it is a big table then the count(*) as inner query will create
> performance problem '
> "John Bell" wrote:
>|||Hi
You may want to look at Itzik Ben-Gan's articles in the May 2005 SQL
Server Magazine.
http://www.windowsitpro.com/Article...5828/45828.html
http://www.windowsitpro.com/Article...2302/42302.html
http://www.windowsitpro.com/Article...2646/42646.html
John

ROWNUM function

Does SQL Server 2005 or SQL Express have the capability of the ROWNUM function found in Oracle (LIMIT in MySQL)?
please advice!
To select records from row #10 to row #20
Oracle:SELECT *FROM MyTableWHEREROWNUM>9ANDROWNUM<21
MySQL:SELECT *FROM MyTableLIMIT10,20
SQL Server:?
SELECT * FROM MyTable WHERE Row_Number() BETWEEN 10 and 20|||It is not working in SQL Express....why??|||

This one works:

SELECT

OrderID, OrderDate, RowNumberFROM(SELECT OrderID, OrderDate, ROW_NUMBER()OVER(orderby OrderID)as RowNumber

FROM

ORDERS)as tWHERE RowNumberBETWEEN 10 AND 15

Syntax in SQL Server 2005:

ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )

sql

rownum equivalent ?

Hi,
Rownum returns the serial number for the records in Oracle.
Id there an equivalent for the same in SQL Server ?
select rownum from test_table;
Please advise,
Thanks
Samsqlserver has none, the clostest match is to add an identifier-column.

rownum and sub rownum

I have a table with six records. 4 A records and 2 B records.
how do i count them by A and B. when I do a
select rownum, col from table;
i get:
1 A
2 A
3 A
4 A
5 B
6 B

How can I get the following result?
1 A 1
2 A 2
3 A 3
4 A 4
5 B 1
6 B 2

help PleaseWhat DBMS are you on? For Oracle there is the ROW_NUMBER function:

select row_number() over (order by col),
col,
row_number() over (partition by col order by 1)
from table;|||I am on Oracle. This worked. Thank you very much|||Hi, I am unable to understand what this criteria means:

ID = '"& request.querystring("oID") & "'

in the following statement:

select count(distinct hazmatclass) as hzcount
from manifestexp
where orderkey in
(
select orderkey
from manifestexp
where ID = '"& request.querystring("oID") & "'
)
group by hazmatclass;

* manfestexp is a view.
* ID is a column in that view.

Any hints please??|||That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.|||Thank you very much for your feedback.
I am not sure I understand the prepared statement part. How do I go about doing that.

Originally posted by andrewst
That is (bad) ASP syntax. Someone is building a SQL statement as a character string in ASP, and concatenating into it an ID value from a field on a form.

I say bad syntax, because what they should be doing is using bind variables via a Prepared Statement.

Rownum

Hi folks,
SELECT * FROM mytable
100 rows returned.
Can i get a rownum column for each record; i.e. if 100 records returned; rownum order 1,2,3....100 along with the each record position.
is it possible without using cursor?
Howdy!see http://www.dbforums.com/t1058224.html|||Assuming that you have atleast one primary key or at least a unique constraint:

Select Count(RowTable.UniqueField) as RowNumber, Mytable.Fields
from Mytable
inner join Mytable RowTable on Mytable.UniqueField >= RowTable.UniqueField

Even I had the Same problem. Thanks To Blindman for his help regarding the query.|||Thanx to r937 and blindman! :)|||Assuming that you have atleast one primary key or at least a unique constraint
Also: NULL values won't be counted. In the other thread the values were used as columnnames, so NULL is quite unlikely. Not sure about myTable.sql

Rowlock never escalates to Paglock?

Hi,
I have a table with an indexed column named "id". The table contains about
100,000 records.
I use "ROWLOCK" lock hint to tell SQL Server not to escalate to higher
level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
Two transactions are as following:
--Tran1
begin tran
update dbo.table1 with (rowlock) set someValue=0 where id=1000
waitfor delay '00:00:10'
commit tran
--Tran2
begin tran
select * from dbo.table1 where id=1001
commit tran
If I run Tran1 first and then Tran2, the second transaction got to wait
until the first one is done.
So it seems the page where id=1000 locates was locked, preventing id=1001 to
be read. Well... ROWLOCK means we want to lock a "row", not a whole page,
right?
Run sp_lock while the Tran1 is running, and I got the result:
66 7 141243558 1 KEY (6d0040d1d33f) X GRANT
66 7 141243558 1 PAG 1:1753 IX GRANT
66 7 141243558 0 TAB IX
GRANT
66 7 141243558 1 KEY (6c00321b0c6a) X GRANT
67 1 85575343 0 TAB IS
GRANT
As I know, PAG IX lock means only partial of an page is locked. It's what I
expected and, obviously contrary with the result I had.
Could anyone explain this to me? If this doesn't work, any other way to
make sure lock only applied to a single row of a table?
Thank you in advance
Ryan> As I know, PAG IX lock means only partial of an page is locked. It's
what I
> expected and, obviously contrary with the result I had.
> Could anyone explain this to me? If this doesn't work, any other way
to
> make sure lock only applied to a single row of a table?
An IX lock is not the result of escalation and is not a 'partial' lock.
This is an intent-exclusive lock acquired at a higher level to indicate
that a more granular lock is also held. IX locks are compatible with
other intent locks.
Your example shows that you've successfully acquired row locks on 2
different rows. Another SPID can access different rows, even if on the
same page.
See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more info.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Ryan" <ryan@.cradle.com.tw> wrote in message
news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table with an indexed column named "id". The table contains
about
> 100,000 records.
> I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
higher
> level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> Two transactions are as following:
> --Tran1
> begin tran
> update dbo.table1 with (rowlock) set someValue=0 where id=1000
> waitfor delay '00:00:10'
> commit tran
> --Tran2
> begin tran
> select * from dbo.table1 where id=1001
> commit tran
> If I run Tran1 first and then Tran2, the second transaction got to
wait
> until the first one is done.
> So it seems the page where id=1000 locates was locked, preventing
id=1001 to
> be read. Well... ROWLOCK means we want to lock a "row", not a whole
page,
> right?
> Run sp_lock while the Tran1 is running, and I got the result:
> 66 7 141243558 1 KEY (6d0040d1d33f) X GRANT
> 66 7 141243558 1 PAG 1:1753 IX
GRANT
> 66 7 141243558 0 TAB
IX
> GRANT
> 66 7 141243558 1 KEY (6c00321b0c6a) X GRANT
> 67 1 85575343 0 TAB
IS
> GRANT
> As I know, PAG IX lock means only partial of an page is locked. It's
what I
> expected and, obviously contrary with the result I had.
> Could anyone explain this to me? If this doesn't work, any other way
to
> make sure lock only applied to a single row of a table?
>
> Thank you in advance
>
> Ryan
>
>|||Thank you for your reply.
> Your example shows that you've successfully acquired row locks on 2
> different rows. Another SPID can access different rows, even if on the
> same page.
I don't get it. The second transaction must wait until the first one is
completed. If the first transaction only holds a lock on the row, id=1000,
why another SPID got to wait when id=1001 is interested?
Ryan
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > As I know, PAG IX lock means only partial of an page is locked. It's
> what I
> > expected and, obviously contrary with the result I had.
> >
> > Could anyone explain this to me? If this doesn't work, any other way
> to
> > make sure lock only applied to a single row of a table?
> An IX lock is not the result of escalation and is not a 'partial' lock.
> This is an intent-exclusive lock acquired at a higher level to indicate
> that a more granular lock is also held. IX locks are compatible with
> other intent locks.
> Your example shows that you've successfully acquired row locks on 2
> different rows. Another SPID can access different rows, even if on the
> same page.
> See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more info.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> --
> SQL FAQ links (courtesy Neil Pike):
> http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> http://www.sqlserverfaq.com
> http://www.mssqlserver.com/faq
> --
> "Ryan" <ryan@.cradle.com.tw> wrote in message
> news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > Hi,
> >
> > I have a table with an indexed column named "id". The table contains
> about
> > 100,000 records.
> >
> > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> higher
> > level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> > Two transactions are as following:
> >
> > --Tran1
> > begin tran
> > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > waitfor delay '00:00:10'
> > commit tran
> >
> > --Tran2
> > begin tran
> > select * from dbo.table1 where id=1001
> > commit tran
> >
> > If I run Tran1 first and then Tran2, the second transaction got to
> wait
> > until the first one is done.
> >
> > So it seems the page where id=1000 locates was locked, preventing
> id=1001 to
> > be read. Well... ROWLOCK means we want to lock a "row", not a whole
> page,
> > right?
> >
> > Run sp_lock while the Tran1 is running, and I got the result:
> >
> > 66 7 141243558 1 KEY (6d0040d1d33f) X GRANT
> > 66 7 141243558 1 PAG 1:1753 IX
> GRANT
> > 66 7 141243558 0 TAB
> IX
> > GRANT
> > 66 7 141243558 1 KEY (6c00321b0c6a) X GRANT
> > 67 1 85575343 0 TAB
> IS
> > GRANT
> >
> > As I know, PAG IX lock means only partial of an page is locked. It's
> what I
> > expected and, obviously contrary with the result I had.
> >
> > Could anyone explain this to me? If this doesn't work, any other way
> to
> > make sure lock only applied to a single row of a table?
> >
> >
> >
> > Thank you in advance
> >
> >
> > Ryan
> >
> >
> >
>|||Hi Ryan
Are you actually seeing another process waiting? As Dan explained, IX locks
are compatible with other IX locks. If you have a case of another
transaction blocking while attempted to access a DIFFERENT row, please post
the sp_lock output showing the process with the WAIT status.
Also, a ROWLOCK hint does not prevent true escalation. It only encourages
SQL Server to start with row (or key)locks, but if the conditions are right
and enough rows are locked, SQL Server can ALWAYS escalate to a table lock.
It will never escalate from row to page locks, escalation is only to table
locks.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Ryan" <ryan@.cradle.com.tw> wrote in message
news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> Thank you for your reply.
> > Your example shows that you've successfully acquired row locks on 2
> > different rows. Another SPID can access different rows, even if on the
> > same page.
> I don't get it. The second transaction must wait until the first one is
> completed. If the first transaction only holds a lock on the row,
id=1000,
> why another SPID got to wait when id=1001 is interested?
>
> Ryan
>
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > As I know, PAG IX lock means only partial of an page is locked. It's
> > what I
> > > expected and, obviously contrary with the result I had.
> > >
> > > Could anyone explain this to me? If this doesn't work, any other way
> > to
> > > make sure lock only applied to a single row of a table?
> >
> > An IX lock is not the result of escalation and is not a 'partial' lock.
> > This is an intent-exclusive lock acquired at a higher level to indicate
> > that a more granular lock is also held. IX locks are compatible with
> > other intent locks.
> >
> > Your example shows that you've successfully acquired row locks on 2
> > different rows. Another SPID can access different rows, even if on the
> > same page.
> >
> > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more info.
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > --
> > SQL FAQ links (courtesy Neil Pike):
> >
> > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > http://www.sqlserverfaq.com
> > http://www.mssqlserver.com/faq
> > --
> >
> > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > Hi,
> > >
> > > I have a table with an indexed column named "id". The table contains
> > about
> > > 100,000 records.
> > >
> > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> > higher
> > > level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> > > Two transactions are as following:
> > >
> > > --Tran1
> > > begin tran
> > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > waitfor delay '00:00:10'
> > > commit tran
> > >
> > > --Tran2
> > > begin tran
> > > select * from dbo.table1 where id=1001
> > > commit tran
> > >
> > > If I run Tran1 first and then Tran2, the second transaction got to
> > wait
> > > until the first one is done.
> > >
> > > So it seems the page where id=1000 locates was locked, preventing
> > id=1001 to
> > > be read. Well... ROWLOCK means we want to lock a "row", not a whole
> > page,
> > > right?
> > >
> > > Run sp_lock while the Tran1 is running, and I got the result:
> > >
> > > 66 7 141243558 1 KEY (6d0040d1d33f) X GRANT
> > > 66 7 141243558 1 PAG 1:1753 IX
> > GRANT
> > > 66 7 141243558 0 TAB
> > IX
> > > GRANT
> > > 66 7 141243558 1 KEY (6c00321b0c6a) X GRANT
> > > 67 1 85575343 0 TAB
> > IS
> > > GRANT
> > >
> > > As I know, PAG IX lock means only partial of an page is locked. It's
> > what I
> > > expected and, obviously contrary with the result I had.
> > >
> > > Could anyone explain this to me? If this doesn't work, any other way
> > to
> > > make sure lock only applied to a single row of a table?
> > >
> > >
> > >
> > > Thank you in advance
> > >
> > >
> > > Ryan
> > >
> > >
> > >
> >
> >
>|||Dear Kalen,
> Are you actually seeing another process waiting? As Dan explained, IX
locks
> are compatible with other IX locks. If you have a case of another
> transaction blocking while attempted to access a DIFFERENT row, please
post
> the sp_lock output showing the process with the WAIT status.
Could you please link to :
http://sbu.cradle.com.tw/TimeSheet/RC/ROWLOCK.gif
I captured the windows of Tran1, Tran2, and the result of running sp_lock in
the above picture.
Ryan
> Also, a ROWLOCK hint does not prevent true escalation. It only encourages
> SQL Server to start with row (or key)locks, but if the conditions are
right
> and enough rows are locked, SQL Server can ALWAYS escalate to a table
lock.
> It will never escalate from row to page locks, escalation is only to table
> locks.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Ryan" <ryan@.cradle.com.tw> wrote in message
> news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > Thank you for your reply.
> >
> > > Your example shows that you've successfully acquired row locks on 2
> > > different rows. Another SPID can access different rows, even if on
the
> > > same page.
> >
> > I don't get it. The second transaction must wait until the first one is
> > completed. If the first transaction only holds a lock on the row,
> id=1000,
> > why another SPID got to wait when id=1001 is interested?
> >
> >
> > Ryan
> >
> >
> >
> >
> > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > As I know, PAG IX lock means only partial of an page is locked.
It's
> > > what I
> > > > expected and, obviously contrary with the result I had.
> > > >
> > > > Could anyone explain this to me? If this doesn't work, any other
way
> > > to
> > > > make sure lock only applied to a single row of a table?
> > >
> > > An IX lock is not the result of escalation and is not a 'partial'
lock.
> > > This is an intent-exclusive lock acquired at a higher level to
indicate
> > > that a more granular lock is also held. IX locks are compatible with
> > > other intent locks.
> > >
> > > Your example shows that you've successfully acquired row locks on 2
> > > different rows. Another SPID can access different rows, even if on
the
> > > same page.
> > >
> > > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more
info.
> > >
> > > --
> > > Hope this helps.
> > >
> > > Dan Guzman
> > > SQL Server MVP
> > >
> > > --
> > > SQL FAQ links (courtesy Neil Pike):
> > >
> > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > http://www.sqlserverfaq.com
> > > http://www.mssqlserver.com/faq
> > > --
> > >
> > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > > Hi,
> > > >
> > > > I have a table with an indexed column named "id". The table
contains
> > > about
> > > > 100,000 records.
> > > >
> > > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> > > higher
> > > > level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> > > > Two transactions are as following:
> > > >
> > > > --Tran1
> > > > begin tran
> > > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > > waitfor delay '00:00:10'
> > > > commit tran
> > > >
> > > > --Tran2
> > > > begin tran
> > > > select * from dbo.table1 where id=1001
> > > > commit tran
> > > >
> > > > If I run Tran1 first and then Tran2, the second transaction got to
> > > wait
> > > > until the first one is done.
> > > >
> > > > So it seems the page where id=1000 locates was locked, preventing
> > > id=1001 to
> > > > be read. Well... ROWLOCK means we want to lock a "row", not a whole
> > > page,
> > > > right?
> > > >
> > > > Run sp_lock while the Tran1 is running, and I got the result:
> > > >
> > > > 66 7 141243558 1 KEY (6d0040d1d33f) X
GRANT
> > > > 66 7 141243558 1 PAG 1:1753 IX
> > > GRANT
> > > > 66 7 141243558 0 TAB
> > > IX
> > > > GRANT
> > > > 66 7 141243558 1 KEY (6c00321b0c6a) X
GRANT
> > > > 67 1 85575343 0 TAB
> > > IS
> > > > GRANT
> > > >
> > > > As I know, PAG IX lock means only partial of an page is locked.
It's
> > > what I
> > > > expected and, obviously contrary with the result I had.
> > > >
> > > > Could anyone explain this to me? If this doesn't work, any other
way
> > > to
> > > > make sure lock only applied to a single row of a table?
> > > >
> > > >
> > > >
> > > > Thank you in advance
> > > >
> > > >
> > > > Ryan
> > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Is SQL Server using an index for the query? My guess is that SQL Server has to check a number of
rows, whether the value is 1000 or not and this is causing the blocking. I wonder whether SQL Server
would benefit from a unique or PK constraint in the column in this case? "I know there can only be
one row with a certain value, no need look further...".
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Ryan" <ryan@.cradle.com.tw> wrote in message news:%23p%23A82DnDHA.2000@.TK2MSFTNGP12.phx.gbl...
> Dear Kalen,
>
> > Are you actually seeing another process waiting? As Dan explained, IX
> locks
> > are compatible with other IX locks. If you have a case of another
> > transaction blocking while attempted to access a DIFFERENT row, please
> post
> > the sp_lock output showing the process with the WAIT status.
> Could you please link to :
> http://sbu.cradle.com.tw/TimeSheet/RC/ROWLOCK.gif
> I captured the windows of Tran1, Tran2, and the result of running sp_lock in
> the above picture.
> Ryan
>
>
>
> >
> > Also, a ROWLOCK hint does not prevent true escalation. It only encourages
> > SQL Server to start with row (or key)locks, but if the conditions are
> right
> > and enough rows are locked, SQL Server can ALWAYS escalate to a table
> lock.
> > It will never escalate from row to page locks, escalation is only to table
> > locks.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > > Thank you for your reply.
> > >
> > > > Your example shows that you've successfully acquired row locks on 2
> > > > different rows. Another SPID can access different rows, even if on
> the
> > > > same page.
> > >
> > > I don't get it. The second transaction must wait until the first one is
> > > completed. If the first transaction only holds a lock on the row,
> > id=1000,
> > > why another SPID got to wait when id=1001 is interested?
> > >
> > >
> > > Ryan
> > >
> > >
> > >
> > >
> > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > > news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > > As I know, PAG IX lock means only partial of an page is locked.
> It's
> > > > what I
> > > > > expected and, obviously contrary with the result I had.
> > > > >
> > > > > Could anyone explain this to me? If this doesn't work, any other
> way
> > > > to
> > > > > make sure lock only applied to a single row of a table?
> > > >
> > > > An IX lock is not the result of escalation and is not a 'partial'
> lock.
> > > > This is an intent-exclusive lock acquired at a higher level to
> indicate
> > > > that a more granular lock is also held. IX locks are compatible with
> > > > other intent locks.
> > > >
> > > > Your example shows that you've successfully acquired row locks on 2
> > > > different rows. Another SPID can access different rows, even if on
> the
> > > > same page.
> > > >
> > > > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more
> info.
> > > >
> > > > --
> > > > Hope this helps.
> > > >
> > > > Dan Guzman
> > > > SQL Server MVP
> > > >
> > > > --
> > > > SQL FAQ links (courtesy Neil Pike):
> > > >
> > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > http://www.sqlserverfaq.com
> > > > http://www.mssqlserver.com/faq
> > > > --
> > > >
> > > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > > > Hi,
> > > > >
> > > > > I have a table with an indexed column named "id". The table
> contains
> > > > about
> > > > > 100,000 records.
> > > > >
> > > > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> > > > higher
> > > > > level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> > > > > Two transactions are as following:
> > > > >
> > > > > --Tran1
> > > > > begin tran
> > > > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > > > waitfor delay '00:00:10'
> > > > > commit tran
> > > > >
> > > > > --Tran2
> > > > > begin tran
> > > > > select * from dbo.table1 where id=1001
> > > > > commit tran
> > > > >
> > > > > If I run Tran1 first and then Tran2, the second transaction got to
> > > > wait
> > > > > until the first one is done.
> > > > >
> > > > > So it seems the page where id=1000 locates was locked, preventing
> > > > id=1001 to
> > > > > be read. Well... ROWLOCK means we want to lock a "row", not a whole
> > > > page,
> > > > > right?
> > > > >
> > > > > Run sp_lock while the Tran1 is running, and I got the result:
> > > > >
> > > > > 66 7 141243558 1 KEY (6d0040d1d33f) X
> GRANT
> > > > > 66 7 141243558 1 PAG 1:1753 IX
> > > > GRANT
> > > > > 66 7 141243558 0 TAB
> > > > IX
> > > > > GRANT
> > > > > 66 7 141243558 1 KEY (6c00321b0c6a) X
> GRANT
> > > > > 67 1 85575343 0 TAB
> > > > IS
> > > > > GRANT
> > > > >
> > > > > As I know, PAG IX lock means only partial of an page is locked.
> It's
> > > > what I
> > > > > expected and, obviously contrary with the result I had.
> > > > >
> > > > > Could anyone explain this to me? If this doesn't work, any other
> way
> > > > to
> > > > > make sure lock only applied to a single row of a table?
> > > > >
> > > > >
> > > > >
> > > > > Thank you in advance
> > > > >
> > > > >
> > > > > Ryan
> > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||That would be my guess as well. What is the DDL for that table?
--
Andrew J. Kelly
SQL Server MVP
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%23uYt%238FnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> Is SQL Server using an index for the query? My guess is that SQL Server
has to check a number of
> rows, whether the value is 1000 or not and this is causing the blocking. I
wonder whether SQL Server
> would benefit from a unique or PK constraint in the column in this case?
"I know there can only be
> one row with a certain value, no need look further...".
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Ryan" <ryan@.cradle.com.tw> wrote in message
news:%23p%23A82DnDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > Dear Kalen,
> >
> >
> > > Are you actually seeing another process waiting? As Dan explained, IX
> > locks
> > > are compatible with other IX locks. If you have a case of another
> > > transaction blocking while attempted to access a DIFFERENT row, please
> > post
> > > the sp_lock output showing the process with the WAIT status.
> >
> > Could you please link to :
> > http://sbu.cradle.com.tw/TimeSheet/RC/ROWLOCK.gif
> >
> > I captured the windows of Tran1, Tran2, and the result of running
sp_lock in
> > the above picture.
> >
> > Ryan
> >
> >
> >
> >
> >
> >
> > >
> > > Also, a ROWLOCK hint does not prevent true escalation. It only
encourages
> > > SQL Server to start with row (or key)locks, but if the conditions are
> > right
> > > and enough rows are locked, SQL Server can ALWAYS escalate to a table
> > lock.
> > > It will never escalate from row to page locks, escalation is only to
table
> > > locks.
> > >
> > > --
> > > HTH
> > > --
> > > Kalen Delaney
> > > SQL Server MVP
> > > www.SolidQualityLearning.com
> > >
> > >
> > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > > > Thank you for your reply.
> > > >
> > > > > Your example shows that you've successfully acquired row locks on
2
> > > > > different rows. Another SPID can access different rows, even if
on
> > the
> > > > > same page.
> > > >
> > > > I don't get it. The second transaction must wait until the first
one is
> > > > completed. If the first transaction only holds a lock on the row,
> > > id=1000,
> > > > why another SPID got to wait when id=1001 is interested?
> > > >
> > > >
> > > > Ryan
> > > >
> > > >
> > > >
> > > >
> > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > > > news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > > > As I know, PAG IX lock means only partial of an page is locked.
> > It's
> > > > > what I
> > > > > > expected and, obviously contrary with the result I had.
> > > > > >
> > > > > > Could anyone explain this to me? If this doesn't work, any
other
> > way
> > > > > to
> > > > > > make sure lock only applied to a single row of a table?
> > > > >
> > > > > An IX lock is not the result of escalation and is not a 'partial'
> > lock.
> > > > > This is an intent-exclusive lock acquired at a higher level to
> > indicate
> > > > > that a more granular lock is also held. IX locks are compatible
with
> > > > > other intent locks.
> > > > >
> > > > > Your example shows that you've successfully acquired row locks on
2
> > > > > different rows. Another SPID can access different rows, even if
on
> > the
> > > > > same page.
> > > > >
> > > > > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for more
> > info.
> > > > >
> > > > > --
> > > > > Hope this helps.
> > > > >
> > > > > Dan Guzman
> > > > > SQL Server MVP
> > > > >
> > > > > --
> > > > > SQL FAQ links (courtesy Neil Pike):
> > > > >
> > > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > > http://www.sqlserverfaq.com
> > > > > http://www.mssqlserver.com/faq
> > > > > --
> > > > >
> > > > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > > > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > > > > Hi,
> > > > > >
> > > > > > I have a table with an indexed column named "id". The table
> > contains
> > > > > about
> > > > > > 100,000 records.
> > > > > >
> > > > > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate to
> > > > > higher
> > > > > > level lock like PAGLOCK. But it seems SQL Server ignores my
"hint".
> > > > > > Two transactions are as following:
> > > > > >
> > > > > > --Tran1
> > > > > > begin tran
> > > > > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > > > > waitfor delay '00:00:10'
> > > > > > commit tran
> > > > > >
> > > > > > --Tran2
> > > > > > begin tran
> > > > > > select * from dbo.table1 where id=1001
> > > > > > commit tran
> > > > > >
> > > > > > If I run Tran1 first and then Tran2, the second transaction got
to
> > > > > wait
> > > > > > until the first one is done.
> > > > > >
> > > > > > So it seems the page where id=1000 locates was locked,
preventing
> > > > > id=1001 to
> > > > > > be read. Well... ROWLOCK means we want to lock a "row", not a
whole
> > > > > page,
> > > > > > right?
> > > > > >
> > > > > > Run sp_lock while the Tran1 is running, and I got the result:
> > > > > >
> > > > > > 66 7 141243558 1 KEY (6d0040d1d33f) X
> > GRANT
> > > > > > 66 7 141243558 1 PAG 1:1753 IX
> > > > > GRANT
> > > > > > 66 7 141243558 0 TAB
> > > > > IX
> > > > > > GRANT
> > > > > > 66 7 141243558 1 KEY (6c00321b0c6a) X
> > GRANT
> > > > > > 67 1 85575343 0 TAB
> > > > > IS
> > > > > > GRANT
> > > > > >
> > > > > > As I know, PAG IX lock means only partial of an page is locked.
> > It's
> > > > > what I
> > > > > > expected and, obviously contrary with the result I had.
> > > > > >
> > > > > > Could anyone explain this to me? If this doesn't work, any
other
> > way
> > > > > to
> > > > > > make sure lock only applied to a single row of a table?
> > > > > >
> > > > > >
> > > > > >
> > > > > > Thank you in advance
> > > > > >
> > > > > >
> > > > > > Ryan
> > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||Sorry, I don't follow you. To see ROWLOCK in action, we need to provide one
or more index for SQL Server to lock certain key ranges, right? In this
case, the best candidate seems to be the column "id", a non-clustered
primary key.
The DDL is as shown below. For simplicity, I removed some update triggers,
columns and associated indexes. To make sure the triggers of the table do
not affect the result, I removed them and had the same outcome.
To view the Estimated Execution Plans and all other figures, please visit
the following URL:
http://sbu.cradle.com.tw/Newsgroup/ROWLOCK.htm
CREATE TABLE [dbo].[prj_PDBA] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[job_type] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[pricingType] [bit] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[prj_PDBA] ADD
CONSTRAINT [PK_prj_PDBA] PRIMARY KEY NONCLUSTERED
(
[id]
) ON [PRIMARY]
GO
Thank you
Ryan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:u%23bVi8InDHA.2512@.TK2MSFTNGP09.phx.gbl...
> That would be my guess as well. What is the DDL for that table?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:%23uYt%238FnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > Is SQL Server using an index for the query? My guess is that SQL Server
> has to check a number of
> > rows, whether the value is 1000 or not and this is causing the blocking.
I
> wonder whether SQL Server
> > would benefit from a unique or PK constraint in the column in this case?
> "I know there can only be
> > one row with a certain value, no need look further...".
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Ryan" <ryan@.cradle.com.tw> wrote in message
> news:%23p%23A82DnDHA.2000@.TK2MSFTNGP12.phx.gbl...
> > > Dear Kalen,
> > >
> > >
> > > > Are you actually seeing another process waiting? As Dan explained,
IX
> > > locks
> > > > are compatible with other IX locks. If you have a case of another
> > > > transaction blocking while attempted to access a DIFFERENT row,
please
> > > post
> > > > the sp_lock output showing the process with the WAIT status.
> > >
> > > Could you please link to :
> > > http://sbu.cradle.com.tw/TimeSheet/RC/ROWLOCK.gif
> > >
> > > I captured the windows of Tran1, Tran2, and the result of running
> sp_lock in
> > > the above picture.
> > >
> > > Ryan
> > >
> > >
> > >
> > >
> > >
> > >
> > > >
> > > > Also, a ROWLOCK hint does not prevent true escalation. It only
> encourages
> > > > SQL Server to start with row (or key)locks, but if the conditions
are
> > > right
> > > > and enough rows are locked, SQL Server can ALWAYS escalate to a
table
> > > lock.
> > > > It will never escalate from row to page locks, escalation is only to
> table
> > > > locks.
> > > >
> > > > --
> > > > HTH
> > > > --
> > > > Kalen Delaney
> > > > SQL Server MVP
> > > > www.SolidQualityLearning.com
> > > >
> > > >
> > > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > > news:efP6YjCnDHA.3316@.TK2MSFTNGP11.phx.gbl...
> > > > > Thank you for your reply.
> > > > >
> > > > > > Your example shows that you've successfully acquired row locks
on
> 2
> > > > > > different rows. Another SPID can access different rows, even if
> on
> > > the
> > > > > > same page.
> > > > >
> > > > > I don't get it. The second transaction must wait until the first
> one is
> > > > > completed. If the first transaction only holds a lock on the row,
> > > > id=1000,
> > > > > why another SPID got to wait when id=1001 is interested?
> > > > >
> > > > >
> > > > > Ryan
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
> > > > > news:%230Sep%23imDHA.964@.TK2MSFTNGP10.phx.gbl...
> > > > > > > As I know, PAG IX lock means only partial of an page is
locked.
> > > It's
> > > > > > what I
> > > > > > > expected and, obviously contrary with the result I had.
> > > > > > >
> > > > > > > Could anyone explain this to me? If this doesn't work, any
> other
> > > way
> > > > > > to
> > > > > > > make sure lock only applied to a single row of a table?
> > > > > >
> > > > > > An IX lock is not the result of escalation and is not a
'partial'
> > > lock.
> > > > > > This is an intent-exclusive lock acquired at a higher level to
> > > indicate
> > > > > > that a more granular lock is also held. IX locks are compatible
> with
> > > > > > other intent locks.
> > > > > >
> > > > > > Your example shows that you've successfully acquired row locks
on
> 2
> > > > > > different rows. Another SPID can access different rows, even if
> on
> > > the
> > > > > > same page.
> > > > > >
> > > > > > See the Books Online <acdata.chm::/ac_8_con_7a_8um1.htm> for
more
> > > info.
> > > > > >
> > > > > > --
> > > > > > Hope this helps.
> > > > > >
> > > > > > Dan Guzman
> > > > > > SQL Server MVP
> > > > > >
> > > > > > --
> > > > > > SQL FAQ links (courtesy Neil Pike):
> > > > > >
> > > > > > http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
> > > > > > http://www.sqlserverfaq.com
> > > > > > http://www.mssqlserver.com/faq
> > > > > > --
> > > > > >
> > > > > > "Ryan" <ryan@.cradle.com.tw> wrote in message
> > > > > > news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> > > > > > > Hi,
> > > > > > >
> > > > > > > I have a table with an indexed column named "id". The table
> > > contains
> > > > > > about
> > > > > > > 100,000 records.
> > > > > > >
> > > > > > > I use "ROWLOCK" lock hint to tell SQL Server not to escalate
to
> > > > > > higher
> > > > > > > level lock like PAGLOCK. But it seems SQL Server ignores my
> "hint".
> > > > > > > Two transactions are as following:
> > > > > > >
> > > > > > > --Tran1
> > > > > > > begin tran
> > > > > > > update dbo.table1 with (rowlock) set someValue=0 where id=1000
> > > > > > > waitfor delay '00:00:10'
> > > > > > > commit tran
> > > > > > >
> > > > > > > --Tran2
> > > > > > > begin tran
> > > > > > > select * from dbo.table1 where id=1001
> > > > > > > commit tran
> > > > > > >
> > > > > > > If I run Tran1 first and then Tran2, the second transaction
got
> to
> > > > > > wait
> > > > > > > until the first one is done.
> > > > > > >
> > > > > > > So it seems the page where id=1000 locates was locked,
> preventing
> > > > > > id=1001 to
> > > > > > > be read. Well... ROWLOCK means we want to lock a "row", not a
> whole
> > > > > > page,
> > > > > > > right?
> > > > > > >
> > > > > > > Run sp_lock while the Tran1 is running, and I got the result:
> > > > > > >
> > > > > > > 66 7 141243558 1 KEY (6d0040d1d33f) X
> > > GRANT
> > > > > > > 66 7 141243558 1 PAG 1:1753
IX
> > > > > > GRANT
> > > > > > > 66 7 141243558 0 TAB
> > > > > > IX
> > > > > > > GRANT
> > > > > > > 66 7 141243558 1 KEY (6c00321b0c6a) X
> > > GRANT
> > > > > > > 67 1 85575343 0 TAB
> > > > > > IS
> > > > > > > GRANT
> > > > > > >
> > > > > > > As I know, PAG IX lock means only partial of an page is
locked.
> > > It's
> > > > > > what I
> > > > > > > expected and, obviously contrary with the result I had.
> > > > > > >
> > > > > > > Could anyone explain this to me? If this doesn't work, any
> other
> > > way
> > > > > > to
> > > > > > > make sure lock only applied to a single row of a table?
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > Thank you in advance
> > > > > > >
> > > > > > >
> > > > > > > Ryan
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>|||"Ryan" <ryan@.cradle.com.tw> wrote in message
news:ONizxlQnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> Sorry, I don't follow you. To see ROWLOCK in action, we need to provide
one
> or more index for SQL Server to lock certain key ranges, right? In this
> case, the best candidate seems to be the column "id", a non-clustered
> primary key.
> The DDL is as shown below. For simplicity, I removed some update
triggers,
> columns and associated indexes. To make sure the triggers of the table do
> not affect the result, I removed them and had the same outcome.
> To view the Estimated Execution Plans and all other figures, please visit
> the following URL:
> http://sbu.cradle.com.tw/Newsgroup/ROWLOCK.htm
> CREATE TABLE [dbo].[prj_PDBA] (
> [id] [int] IDENTITY (1, 1) NOT NULL ,
> [job_type] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
> [pricingType] [bit] NOT NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[prj_PDBA] ADD
> CONSTRAINT [PK_prj_PDBA] PRIMARY KEY NONCLUSTERED
> (
> [id]
> ) ON [PRIMARY]
> GO
>
Ok i think I've got it.
You have some other non-primary clustered index on this table. So this
update is likely moving the data row from under one clustered index key to
another, and requires locks on both keys.
Notice that Transaction2 is using a Bookmark Lookup in its query plan. A
Bookmark Lookup on a table with a clustered index resolves not to a row
locator, but to a clustered index key. Transaction1 has the has that
clustered index key exclusively locked (notice the two KEY locks).
To fix this, make PK_prj_PDBA clustered. Then the update will not require
data rows to be physically moved at all, and will require only a single KEY
lock on the key containing the updated row.
David|||You are right ! Thank you so much, and thank to all others.
Ryan
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23yB0gESnDHA.2272@.tk2msftngp13.phx.gbl...
> "Ryan" <ryan@.cradle.com.tw> wrote in message
> news:ONizxlQnDHA.2592@.TK2MSFTNGP10.phx.gbl...
> > Sorry, I don't follow you. To see ROWLOCK in action, we need to provide
> one
> > or more index for SQL Server to lock certain key ranges, right? In this
> > case, the best candidate seems to be the column "id", a non-clustered
> > primary key.
> >
> > The DDL is as shown below. For simplicity, I removed some update
> triggers,
> > columns and associated indexes. To make sure the triggers of the table
do
> > not affect the result, I removed them and had the same outcome.
> >
> > To view the Estimated Execution Plans and all other figures, please
visit
> > the following URL:
> > http://sbu.cradle.com.tw/Newsgroup/ROWLOCK.htm
> >
> > CREATE TABLE [dbo].[prj_PDBA] (
> > [id] [int] IDENTITY (1, 1) NOT NULL ,
> > [job_type] [varchar] (2) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
> > [pricingType] [bit] NOT NULL
> > ) ON [PRIMARY]
> > GO
> >
> >
> > ALTER TABLE [dbo].[prj_PDBA] ADD
> > CONSTRAINT [PK_prj_PDBA] PRIMARY KEY NONCLUSTERED
> > (
> > [id]
> > ) ON [PRIMARY]
> > GO
> >
> Ok i think I've got it.
> You have some other non-primary clustered index on this table. So this
> update is likely moving the data row from under one clustered index key to
> another, and requires locks on both keys.
> Notice that Transaction2 is using a Bookmark Lookup in its query plan. A
> Bookmark Lookup on a table with a clustered index resolves not to a row
> locator, but to a clustered index key. Transaction1 has the has that
> clustered index key exclusively locked (notice the two KEY locks).
> To fix this, make PK_prj_PDBA clustered. Then the update will not require
> data rows to be physically moved at all, and will require only a single
KEY
> lock on the key containing the updated row.
> David
>
>|||I finally found the truth, with the helpful hints from the above threads.
The table has an update trigger, which will update some other rows in the
same table with a where:() predicate using the clustered key to locate the
desired rows.
I couldn't find the fact because I simply add a command "RETURN" in the
beginng of the trigger. That's a way I "disabled" the trigger. After
pysically deleting the trigger, I found ROWLOCK was in action. But, I can't
figure it out why this way doesn't work as expected at the moment of
writing.
For your reference.
Ryan|||"Ryan" <ryan@.cradle.com.tw> wrote in message
news:uk%237%23himDHA.2676@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I have a table with an indexed column named "id". The table contains
about
> 100,000 records.
> I use "ROWLOCK" lock hint to tell SQL Server not to escalate to higher
> level lock like PAGLOCK. But it seems SQL Server ignores my "hint".
> Two transactions are as following:
> --Tran1
> begin tran
> update dbo.table1 with (rowlock) set someValue=0 where id=1000
> waitfor delay '00:00:10'
> commit tran
> --Tran2
> begin tran
> select * from dbo.table1 where id=1001
> commit tran
>
You've got key locks not row locks. table1 appears to be clustered.
Post the DDL for these tables, and a script to reproduce the effect.
David|||> You've got key locks not row locks. table1 appears to be clustered.
> Post the DDL for these tables, and a script to reproduce the effect.
>
Oops, I thought this thread looked familiar.
David

Rowid-insert a record

IN Oracle we can Use Rowid.. I need Sql Server equivalent and
IN Oracle when we insert a record and then select it is display in the last
records.
I Need same in sql server how to make same features.Selva,
Possibly IDENTITY (CREATE TABLE) or NEWID(). Not sure what the Rowid does
in Oracle.
HTH
Jerry
"Selva" <Selva@.discussions.microsoft.com> wrote in message
news:ED62696B-B651-41FC-B1CC-AF08BFEE3F35@.microsoft.com...
> IN Oracle we can Use Rowid.. I need Sql Server equivalent and
> IN Oracle when we insert a record and then select it is display in the
> last
> records.
> I Need same in sql server how to make same features.

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran
"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

RowCount using Group BY and Having

Hi,
I have a query that uses Group By and Having to retrieve records.
The query is working fine and I want to get the Row Count of that Query.
How will I do that.
Thanks
Kiran"Kiran" <Kiran@.nospam.net> wrote in message
news:eIhHM6EAFHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a query that uses Group By and Having to retrieve records.
> The query is working fine and I want to get the Row Count of that Query.
> How will I do that.
> Thanks
> Kiran
>
SELECT ... Group By Query
SELECT @.@.ROWCOUNT
You could also save the rowcount into a variable like this:
DECLARE @.MyCount int
SELECT ... Group By Query
SELECT @.MyCount = @.@.RowCount
HTH
Rick Sawtell
MCT, MCSD, MCDBA

Rowcount reporting incorrectly...

Why when I double click my table, does the "Rows"
field show 10 records, but when I do a SELECT COUNT(id)
it show me the right amount?
SQL 2000 Enterprise, Windows 2003
I've run DBCC CHECKALLOC, UPDATEUSAGE, DBREPAIR, SHOWCONTIG,
INDEXDEFRAG...etc.
I've never heard of this before.EM caches alot of information. Did you try refreshing the data? Exit
and restart EM and see if the problem still appears.|||DBCC UPDATEUSAGE may improve the accuracy of this number, but it would have
to be executed each time you want to look at the number. Read up on
statistics; how they are used and when they are updated.
"isideveloper" <isideveloper@.newsgroups.nospam> wrote in message
news:uwKyfr4RGHA.776@.TK2MSFTNGP09.phx.gbl...
> Why when I double click my table, does the "Rows"
> field show 10 records, but when I do a SELECT COUNT(id)
> it show me the right amount?
> SQL 2000 Enterprise, Windows 2003
> I've run DBCC CHECKALLOC, UPDATEUSAGE, DBREPAIR, SHOWCONTIG,
> INDEXDEFRAG...etc.
> I've never heard of this before.
>|||... and it has to be executed using the COUNT_ROWS option (which mean it wi
ll take a longer
time...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <someone@.microsoft.com> wrote in message news:e6vBNy4RGHA.5036@.TK2MSFTNGP12.phx.gbl...

> DBCC UPDATEUSAGE may improve the accuracy of this number, but it would hav
e to be executed each
> time you want to look at the number. Read up on statistics; how they are u
sed and when they are
> updated.
>
> "isideveloper" <isideveloper@.newsgroups.nospam> wrote in message
> news:uwKyfr4RGHA.776@.TK2MSFTNGP09.phx.gbl...
>sql

rowcount

Hi,

I am using ssis to import .csv files into sql server tables.
How do I get the count of the records imported?
Thanks

SOLVED by using a rowcount component.

|||

Use the RowCount transform.

I dont know what you need, but look around to eventhandlers and global variables.

regards

ROW_NUMBER is very slow on large tables

Hi all,
I thought that ROW_NUMBER is designed to handle paging on large tables.
However, when I test it on a table with 1,000,000 records, a simple select
with ROW_NUMBER hangs for more than 10 minutes.
Any idea what can be done?
My table is:
Events (ID, Date, Desc)
My query is:
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100You still need to have an index on the column ID. Also maybe you have
other issues such as blocking and the problem is not with the
row_number function.
Adi|||I think your query is wrong.
try this and let me know if it works
SELECT * FROM
(
SELECT TOP 100 *, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events order by id asc
) as SortedEvents
WHERE RowNo > 50 and RowNo < 100|||Thank you, but this query is just as slow as mine.
"Omnibuzz" wrote:

> I think your query is wrong.
> try this and let me know if it works
> SELECT * FROM
> (
> SELECT TOP 100 *, ROW_NUMBER()
> OVER (ORDER BY ID ASC) as RowNo
> FROM Events order by id asc
> ) as SortedEvents
> WHERE RowNo > 50 and RowNo < 100|||then can you tell me if this query is fast? do you have an index on ID?
SELECT * FROM
(
SELECT TOP 100 *
FROM Events order by id asc
) as SortedEvents|||Thank you.
ID has index.
Could you please elaborate on what is the blocking issue?
I'm testing on a standalone, development server.
Nobody else uses it.
"Adi" wrote:

> You still need to have an index on the column ID. Also maybe you have
> other issues such as blocking and the problem is not with the
> row_number function.
> Adi
>|||Anton,
try this:
SELECT Events .* FROM Events join
(
SELECT TOP 100 id, ROW_NUMBER()
OVER (ORDER BY ID ASC) as RowNo
FROM Events
) as SortedEvents
on events.id = SortedEvents.id
WHERE RowNo > 50 and RowNo < 100|||The nesting and the proprietary TOP 100 might be causing problems.
ROW_NUMBER () is new and probalby not well-optimized yet. Keep it
simple
SELECT event_id,
ROW_NUMBER()
OVER (ORDER BY event_id ASC) AS rn
FROM Events
WHERE rn BETWEEN 50 AND 100;|||Nonsense.Several years ago all db vendors acknowledged that all major
db problems had been solved (so they were free to add xml to the engine).
This is just another example of a user underming the operation of the db
by doing something silly and not informing the ng of exactly what it is.
'It's the user stupid' hangs on the wall of all vendors (and in the minds of
most responders:).
On a side note I still do not see any explanation from you from the mind
'set'
to windowing.So yesterdays criminial magically becomes todays most decorated
cop.
Code,code and nothing but code is STILL non-sense.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1147365856.484616.283960@.j33g2000cwa.googlegroups.com...
>.
> ROW_NUMBER () is new and probalby not well-optimized yet.
>.|||Steve Dassin wrote:
> Nonsense.Several years ago all db vendors acknowledged that all major
> db problems had been solved
Really? Please amuse me by posting an example of some vendor making
such a claim. :-)
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Row yielded no match during lookup

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

Rafael Salas

|||

Thanks for email

I m new in SSIS, please suggest such error output with example.

|||

leo1 wrote:

Thanks for email

I m new in SSIS, please suggest such error output with example.

Leo,

when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to an custom error table.

Rafael Salas

|||

thanks for reply.

I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

Can you please suggest me (Steps) or example how to do this.

thanks

|||

Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

-jamie

|||

I have got all such rows in the file using the flat file destination in data flow

Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

Kindly suggest possibly by example or steps.

|||I use a lookup often for different purposes.

For example currently i'm using it to pull "Open House" information for Properties.
Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.

Row yielded no match during lookup

In SSIS. I am having trouble exporting records
that don't match from a lookup transformation. I get the following
error:

Row yielded no match during lookup.

I would really like to have a list of all records that did not match so
that I could send an email of those missing rows

Please give me solution with example

Thanks

That is happening because your are using the default error configuration of the Lookuptask "Fail Component"; it would fail if a no match occurs. You need to change that to 'Redirect row' and then use the error output of the task to send those rows to whereever you want.

Rafael Salas

|||

Thanks for email

I m new in SSIS, please suggest such error output with example.

|||

leo1 wrote:

Thanks for email

I m new in SSIS, please suggest such error output with example.

Leo,

when you configure a lookup transformation to 'redirect error' all no-matched rows are sent to the error output instead of failing the task (the error you originally received); obviously those error rows will have null in the columns that the lookup transformation added. Then, based in your requirements, you can decide what to do with those errors. e.g. for a data warehouse your may want to replace the nulls by default values and insert them to the destination table; and/or you can decide to send them to an custom error table.

Rafael Salas

|||

thanks for reply.

I want to find out all such distinct rows or lookup id and send an email of all such non-matching items via email to the team.

Can you please suggest me (Steps) or example how to do this.

thanks

|||

Use a Flat File Destination Adapter to push that data into a file. You can then send that file using the Send mail Task.

-jamie

|||

I have got all such rows in the file using the flat file destination in data flow

Kindly let me know how to send an email.I Know email can be send using the send email task but i need to know where to place send email task and how to check whether flat file contains the error data.

Should we use the send email task on eventhandler, if yes, how to check for error and invoke send email task.

Kindly suggest possibly by example or steps.

|||I use a lookup often for different purposes.

For example currently i'm using it to pull "Open House" information for Properties.
Only a few of those have open house schedules - so what i do is i have two outs from Lookup - and they both go to Union All transform.

Row Output

Just a quick theory question. Say I periodically insert some records into a non-indexed table. I assume that records will be added to the end of the table? So, when I retrieve the rows with a simple Select query, am I guaranteed that all the records will be displayed in the same order that they were inserted? Is this rule supported by SQL server?>> "am I guaranteed that all the records will be displayed
>> in the same order that they were inserted?"

no

if you want sequence, use ORDER BY

rudy

Row Level Security

Posting again in hopes that someone has a solution..

I've set up a sales report that is by territory. Two tables one of which has

sales detail records and another table with Sales Rep info, including territory and

login.. The two tables are joined by state. What I need to be able to do is schedule

this report to run on Reporting services(Already setup) and only allow the reps

to view a snapshot, don't want anyone executing the report again. Additionally,

I need them to only see the territory that they are responsible for. Does anyone

have a solution for this.

Thx again

Hello,

If I'm understanding correctly, you want to create one master snapshot report, then have each rep only access a certain subset of the data in the snapshot. Is this correct?

Since the snapshots are static, you won't be able to limit down the results further after the snapshot is created. I think what you'll need to do is setup multiple subscriptions, one for each rep. You can specify the rep as a parameter to the report. Then you can send the snapshot of the report to the specific rep.

Hope this helps.

Jarret

|||

Thanks for the suggestion. I think that your suggestion will work, but with over 300 reps, I would rather not create that many subscriptions. There has got to be away. Cognos powerplan allows the view to be created upfront and then when A report goes into that view it further filters the results. You would think I should be able to do that. What about not using the snapshot?

Row IDs in resultset

Hi,
I would like to get Row IDs to number my resultset from 1 to whatever. For
example, if I have 10 records from the following statement :
select FirstName, LastName from employees
I would like to number the records from 1 to 10. How do I do it? No cursor
please.
TIAhttp://www.aspfaq.com/show.asp?id=2427
Note, this does not include any information about SQL Server 2005's
ROW_NUMBER function, which makes this whole process much easier...
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||> http://www.aspfaq.com/show.asp?id=2427
> Note, this does not include any information about SQL Server 2005's
> ROW_NUMBER function, which makes this whole process much easier...
Hey man, how many hands do you think I have? :-)|||Based on the amount of information on the site, I'd say somewhere between
four and six?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uXowA684FHA.632@.TK2MSFTNGP10.phx.gbl...
> Hey man, how many hands do you think I have? :-)
>|||Method 1:
If one of the columns in query is unique, the following calculates a
sequential number for each row in a resultset:
SELECT
name,
(select count(*) from TableX as x where x.name > TableX.name) as Number
FROM
TableX
ORDER BY
name
Method 2:
If you are using a stored procedure, you can insert your result into a
temporary table that has an identity column, and then select the final
result from that table. For example:
create table #myresult
(
[Seq] [int] IDENTITY (1, 1) NOT NULL ,
[Col1] [int] ,
[Col2] [int]
)
insert into #myresult select Col1, Col2 from MyTable
select Seq, Col1, Col2 from MyTable
drop table #MyResult
"John" <someone@.microsoft.com> wrote in message
news:%23kz88y84FHA.3636@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I would like to get Row IDs to number my resultset from 1 to whatever. For
> example, if I have 10 records from the following statement :
> select FirstName, LastName from employees
> I would like to number the records from 1 to 10. How do I do it? No
> cursor please.
> TIA
>|||More like 17. :)
ML