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
Showing posts with label column. Show all posts
Showing posts with label column. Show all posts
Friday, March 30, 2012
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
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
rows as columns
is it possible to write a query so that we can have all rows of one column in a single column
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable
TIA
Yes. You could concatenate all the rows in the query as:
SELECT
column1 + column2 + column3
FROM
yourtable
One thing to note here is, since the columns would have different datatypes if you try to concatenate varchar column with int column SQL Server might throw an error. So it is adviced to use CONVERT function to convert all the values into varchar, something like:
SELECT
( CONVERT(varchar(5),intcolumn1) + CONVERT(varchar(10),decimalcolumn2) + regularvarcharcolumn3 )
FROM
yourtable
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
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
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
Wednesday, March 28, 2012
rowid is there?
Hi experts,
I'm in need to use the rowid of a column.
is there any concept like rowid?
select top 1 * from tablename|||Add a column of int datatype to the existing table. Define identity on that column. Define Identity seed and identity increment as 1,1.
The following is the syntax.
I'm in need to use the rowid of a column.
is there any concept like rowid?
for example, i need the first row of a table.or 5th row of a table.how can i write the query?
thank you verymuch in advance.
Quote:
Originally Posted by vinod
Hi experts,
I'm in need to use the rowid of a column.
is there any concept like rowid?
for example, i need the first row of a table.or 5th row of a table.how can i write the query?
thank you verymuch in advance.
select top 1 * from tablename|||Add a column of int datatype to the existing table. Define identity on that column. Define Identity seed and identity increment as 1,1.
The following is the syntax.
a int identity(1,1). Here a is the column. SQL Server automatically increments the number. Based on that number you can fetch the desired record.|||Using Identity function is really great friend.
I'm trying for my purpose now, if i need some more assistance, i'l come back to you
ROWGUIDCOL column
hi
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
Kalyan
Kalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
David Gugick
Imceda Software
www.imceda.com
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
Kalyan
Kalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
David Gugick
Imceda Software
www.imceda.com
ROWGUIDCOL column
hi
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
KalyanKalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
David Gugick
Imceda Software
www.imceda.com
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
KalyanKalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
David Gugick
Imceda Software
www.imceda.com
ROWGUIDCOL column
hi
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
KalyanKalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
--
David Gugick
Imceda Software
www.imceda.com
Can any one explain impact of tunning or query optimization that uses
ROWGUIDCOL?
Thanks
KalyanKalyan wrote:
> hi
> Can any one explain impact of tunning or query optimization that uses
> ROWGUIDCOL?
>
> Thanks
> Kalyan
Can you explain in more detail what you are looking for. Using a GUID as
opposed to a INT IDENTITY, for example, doesn't really hurt performance,
except for the fact that a GUID takes up more space than an INT (16 vs 4
bytes). In that respect, the INT is a better choice.
--
David Gugick
Imceda Software
www.imceda.com
Rowguid Column Position
Hi,
We are merge replication more than 20 databases . Currently we added
one new column to a table so it will be added only in last, and now only in
this table the rowguid is not the last column.
I want to know because of this is there is any problem in replication.
Please give me a solution as soon as possible
thanks,
Soura
Soura,
the rowguid column doesn't need t be the last column, and sp_repladdcolumn
is all you need to use in this circumstance.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
We are merge replication more than 20 databases . Currently we added
one new column to a table so it will be added only in last, and now only in
this table the rowguid is not the last column.
I want to know because of this is there is any problem in replication.
Please give me a solution as soon as possible
thanks,
Soura
Soura,
the rowguid column doesn't need t be the last column, and sp_repladdcolumn
is all you need to use in this circumstance.
HTH,
Paul Ibison SQL Server MVP,
www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Rowguid Column at the end of my Tables
Hello,
There is a "rowguid" column that was added to the end of my tables, and I
have no clue when and why it was added. Actually i had a Merge Replication on
this database and i removed it. I don't know if it was generated by SQL
Server for Merge Replication or something else.
Ant idea ???
Thanks so much
Mathew
Matthew,
this column is essential (actually a column with the rowguid property) to
merge replication. Removing replication will not remove these columns -
you'll need to do it by hand or craft a script. Be sure however that the
developers haven't coded to include this column.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks so much for your helps. I know how to remove this column either on
the Enterprise Manager or by scripts using sysobject to get the table name
and remove this column, but is there any easier ways or scripts that you know?
I appreciate so much.
Mathew
"Paul Ibison" wrote:
> Matthew,
> this column is essential (actually a column with the rowguid property) to
> merge replication. Removing replication will not remove these columns -
> you'll need to do it by hand or craft a script. Be sure however that the
> developers haven't coded to include this column.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
There is a "rowguid" column that was added to the end of my tables, and I
have no clue when and why it was added. Actually i had a Merge Replication on
this database and i removed it. I don't know if it was generated by SQL
Server for Merge Replication or something else.
Ant idea ???
Thanks so much
Mathew
Matthew,
this column is essential (actually a column with the rowguid property) to
merge replication. Removing replication will not remove these columns -
you'll need to do it by hand or craft a script. Be sure however that the
developers haven't coded to include this column.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Dear Paul,
Thanks so much for your helps. I know how to remove this column either on
the Enterprise Manager or by scripts using sysobject to get the table name
and remove this column, but is there any easier ways or scripts that you know?
I appreciate so much.
Mathew
"Paul Ibison" wrote:
> Matthew,
> this column is essential (actually a column with the rowguid property) to
> merge replication. Removing replication will not remove these columns -
> you'll need to do it by hand or craft a script. Be sure however that the
> developers haven't coded to include this column.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
rowguid
Hello,
When I use the Replication and the Publication mechanism - why is created
for every table which I want to synchronize an additional column with the
name rowguid? I have already set a primary key for all tables which I want to
synchronize.
Regards
mat
the rowguid is used to track which row has been modified in a database. By
examining the guid the merge agent knows what table it belongs too, and can
track its change history.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"mathon" <mathon@.discussions.microsoft.com> wrote in message
news:2D7CC7C9-8925-4BED-8DCB-9DF24CE88512@.microsoft.com...
> Hello,
> When I use the Replication and the Publication mechanism - why is created
> for every table which I want to synchronize an additional column with the
> name rowguid? I have already set a primary key for all tables which I want
> to
> synchronize.
> Regards
> mat
When I use the Replication and the Publication mechanism - why is created
for every table which I want to synchronize an additional column with the
name rowguid? I have already set a primary key for all tables which I want to
synchronize.
Regards
mat
the rowguid is used to track which row has been modified in a database. By
examining the guid the merge agent knows what table it belongs too, and can
track its change history.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"mathon" <mathon@.discussions.microsoft.com> wrote in message
news:2D7CC7C9-8925-4BED-8DCB-9DF24CE88512@.microsoft.com...
> Hello,
> When I use the Replication and the Publication mechanism - why is created
> for every table which I want to synchronize an additional column with the
> name rowguid? I have already set a primary key for all tables which I want
> to
> synchronize.
> Regards
> mat
Rowguid
Rowguid column ( globally unique identifier) was added to all tables in the
database. What may cause that?
Thanks for help in advance."Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve|||I inabled replication and it hosed a production db. We are still having tro
uble updating. I disabled and dropped the distribution DB. We then found a
new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_C
ommon_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls"
. I ran a script and the rowguid is no longer associated with the CONTACT2
table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:
> I inabled replication and it hosed a production db. We are still having t
rouble updating. I disabled and dropped the distribution DB. We then found
a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine
_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow null
s". I ran a script and the rowguid is no longer associated with the CONTACT
2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine s
upports Post Alerts, Ratings, and Searching.
>
database. What may cause that?
Thanks for help in advance."Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve|||I inabled replication and it hosed a production db. We are still having tro
uble updating. I disabled and dropped the distribution DB. We then found a
new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_C
ommon_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls"
. I ran a script and the rowguid is no longer associated with the CONTACT2
table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine sup
ports Post Alerts, Ratings, and Searching.|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:
> I inabled replication and it hosed a production db. We are still having t
rouble updating. I disabled and dropped the distribution DB. We then found
a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine
_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow null
s". I ran a script and the rowguid is no longer associated with the CONTACT
2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine s
upports Post Alerts, Ratings, and Searching.
>
Rowguid
Rowguid column ( globally unique identifier) was added to all tables in the
database. What may cause that?
Thanks for help in advance.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve
|||I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:
> I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>
database. What may cause that?
Thanks for help in advance.
"Helen Stein" <hstein@.nojunklarimor.net> wrote in message
news:%237NUmMVVEHA.2908@.TK2MSFTNGP10.phx.gbl...
> Rowguid column ( globally unique identifier) was added to all tables in
the
> database. What may cause that?
>
Did you enable SQL Server replication?
Steve
|||I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
Please HELP>
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
|||Hi
You now have one more column than your inserts are expecting. If you have
removed replication, you can remove the column "rowguid".
Thne your code will work again
Regards
Mike
"SqlJunkies User" wrote:
> I inabled replication and it hosed a production db. We are still having trouble updating. I disabled and dropped the distribution DB. We then found a new column created "rowguid" that hosed my db. The latest message is...
> Server message:Cannot insert the value NULL into column ", table 'Goldmine_Common_DB.dbo.CONTACT2; column does not allow nul.
> I have checked and all tables in our Common DB are correct for "allow nulls". I ran a script and the rowguid is no longer associated with the CONTACT2 table or syscolumns.
> Please HELP>
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.
>
Monday, March 26, 2012
row_id in sql server....
Hi all,
i have a task that whenever i will write a select query then when the result comes then i should be able to write a column that should write the ROW-ID of these rows. I mean for example i write a select query that gives me the following table:(the query can be like select bill_id,name from myTable order by name)
Bill_id name
25 abc
18 def
26 ghi
23 adfd
but my result should look like:
Row_Id Bill_id name
1 25 abc
2 18 def
3 26 ghi
4 23 adfd
n this Row_Id should be generated automatically...even if when i will write order by name in reverse order then also it should write the row_id as 1,2,3,4 same increasing order...n this automatic function i have to make...i cant understand..if there exist some procedure or function that already does this...or how can i just find the simple ROW_ID in general???
any help will be greatly appreciated.
Regards.create table t(bill_id int, name varchar(10))
insert into t values(25, 'abc')
insert into t values(18, 'def')
insert into t values(26, 'ghi')
insert into t values(23, 'adfg')
select Row_ID=count(*), a1.bill_id, a1.name
from t a1, t a2
where a1.bill_id >= a2.bill_id
group by a1.bill_id, a1.name
order by 1
Row_ID bill_id name
-------
1 18 def
2 23 adfg
3 25 abc
4 26 ghi|||Thanks a lot...it really WORKED...
regards.sql
i have a task that whenever i will write a select query then when the result comes then i should be able to write a column that should write the ROW-ID of these rows. I mean for example i write a select query that gives me the following table:(the query can be like select bill_id,name from myTable order by name)
Bill_id name
25 abc
18 def
26 ghi
23 adfd
but my result should look like:
Row_Id Bill_id name
1 25 abc
2 18 def
3 26 ghi
4 23 adfd
n this Row_Id should be generated automatically...even if when i will write order by name in reverse order then also it should write the row_id as 1,2,3,4 same increasing order...n this automatic function i have to make...i cant understand..if there exist some procedure or function that already does this...or how can i just find the simple ROW_ID in general???
any help will be greatly appreciated.
Regards.create table t(bill_id int, name varchar(10))
insert into t values(25, 'abc')
insert into t values(18, 'def')
insert into t values(26, 'ghi')
insert into t values(23, 'adfg')
select Row_ID=count(*), a1.bill_id, a1.name
from t a1, t a2
where a1.bill_id >= a2.bill_id
group by a1.bill_id, a1.name
order by 1
Row_ID bill_id name
-------
1 18 def
2 23 adfg
3 25 abc
4 26 ghi|||Thanks a lot...it really WORKED...
regards.sql
row with highest column A/B/C/D (was "SQL: is this possible?")
Hi i am wondering if this is possible with SQL:
Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/50/300/250/500
3/50/100/1000/500
Is there any possibility with SQL to select all data from this table where the following criteria are used:
Field A highest ? Yes: take this record
No: Field B highest ? Yes: take this record
No: Field C highest ? Yes: take this record
No: Field D highest ? Yes: take this record
This will result in record 1 to be taken (Field A is highest)
Other data:
Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/100/200/750/500
3/50/100/1000/500
This will result in record 2 to be taken (Field C is higest)
Any help is appreciated, Thanks :)If I understand you correctly, this SQL will return all the rows with the one you want at the top:
select * from mytable order by a desc,b desc,c desc,d desc;
All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.|||if a row (not a record) has 4 columns (not fields), then one of them is going to have the highest value in the row, and so you want that row (not record) to be returned?
sounds like you want all rows returned, because each row will have a highest column value amongst its 4 column values
or do you want values compared amongst all rows at the same time? in that case column C in row 2 does not have the highest value
confused|||If I understand you correctly, this SQL will return all the rows with the one you want at the top:
select * from mytable order by a desc,b desc,c desc,d desc;
All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.
I just started to experiment with this :) This MUST be the solution i was looking for. Thanks :)
Edit: I had to select the LAST record to get the correct record (MS Access). Strange, but it works.
Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/50/300/250/500
3/50/100/1000/500
Is there any possibility with SQL to select all data from this table where the following criteria are used:
Field A highest ? Yes: take this record
No: Field B highest ? Yes: take this record
No: Field C highest ? Yes: take this record
No: Field D highest ? Yes: take this record
This will result in record 1 to be taken (Field A is highest)
Other data:
Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/100/200/750/500
3/50/100/1000/500
This will result in record 2 to be taken (Field C is higest)
Any help is appreciated, Thanks :)If I understand you correctly, this SQL will return all the rows with the one you want at the top:
select * from mytable order by a desc,b desc,c desc,d desc;
All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.|||if a row (not a record) has 4 columns (not fields), then one of them is going to have the highest value in the row, and so you want that row (not record) to be returned?
sounds like you want all rows returned, because each row will have a highest column value amongst its 4 column values
or do you want values compared amongst all rows at the same time? in that case column C in row 2 does not have the highest value
confused|||If I understand you correctly, this SQL will return all the rows with the one you want at the top:
select * from mytable order by a desc,b desc,c desc,d desc;
All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.
I just started to experiment with this :) This MUST be the solution i was looking for. Thanks :)
Edit: I had to select the LAST record to get the correct record (MS Access). Strange, but it works.
Friday, March 23, 2012
Row Total and Column Total in MS Visual Studio report designer 2005
Hi All,
I am using Visual Studio 2005 report desingner and SQL 2005. I have created one matrix report, but i want to insert the column total and row total.
or atleast column total
How do i do that ?
COLUMN1 COLUMN2 COLUMN3
qty amt qty amt qty amt
XXXX 2 11.00 3 22.00 34 23.50
YYYYY 3 22.00 5 27.00 35 33.50
How to do the colunm total.
Regards
Saleem
Assuming you have dynamic column and row groups, you can just right-click on the group and select "Subtotal" from the context menu.
See also: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_structure_objects_v1_7vi0.asp (scroll to the bottom section of "Adding Subtotals")
-- Robert
Row to Column?
All:
Is there a function in MS SQL so that I can archieve the following in
SQL statement? Or do I need to loop through the record set and doing
some array element movement on client side?
Table
Item Color
1 red
1 blue
2 red
2 yellow
3 red
I want the result looks like:
Item Color_red Color_blue Color_yellow
1 red blue null
2 red null yellow
3 red null null
thanks a lotCheck out RAC @.
www.rac4sql.net
A very easy and powerful pivoting/xtab utility.
No sql coding required.|||here's a couple ways, e.g.
declare @.x table (item int, color varchar(6))
insert @.x
select 1, 'red' union all
select 1, 'blue' union all
select 2, 'red' union all
select 2, 'yellow' union all
select 3, 'red'
-- either sql 2000/2005
select item,
max(case when color='red' then 'red' end) as color_red,
max(case when color='blue' then 'blue' end) as color_blue,
max(case when color='yellow' then 'yellow' end) as color_yellow
from @.x
group by item
-- sql2005 only [new PIVOT clause]
-- note in the pivot clause, those are columns, not values (strings)
select item, [red] as color_red, [blue] as color_blue, [yellow] as
color_yellow
from
(select item, color from @.x) x
pivot
(
max(color)
for color in ([red],[blue],[yellow])) as pvt
order by item
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||If this has to be done in SQL, you could try outer joining to the table
multiple times, once for each column on your output. If you have the option
of using a tool to process the data outside of SQL, thaqt may be easier.
if tblColor is the name of your table...
select item, rcolor, bcolor, ycolor
from
(Select distinct item from tblColor) as Main
left outer join (select distinct item as ritem, color as rcolor from
tblColor where color = 'red') as red
on item = ritem
left outer join (select distinct item as bitem, color as bcolor from
tblColor where color = 'blue') as blue
on item = bitem
left outer join (select distinct item as yitem, color as ycolor from
tblColor where color = 'yellow') as yellow
on item = yitem
OR, if you dont like inline queries, this is slightly more readable:
select Main.item, red.color, blue.color, yellow.color
from
(Select distinct item from tblColor) as Main
left outer join tblColor as red
on Main.item = red.item and red.color = 'red'
left outer join tblColor as blue
on Main.item = blue.item and blue.color = 'blue'
left outer join tblColor as yellow
on Main.item = yellow.item and yellow.color = 'yellow'
I think you are stuck with the inline query to select the distinct items
regardless. I can't think of a way to avoid this, but you should be able to
make the rest work. The performance on something like this is surprisingly
good, even when you have thousands of rows in your table and 20 collumns.
As you add more columns and more filters on the data it can get a bit out of
hand.
Hope this helps.
<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>
Ugly.
Of course, you need to know what possible colors can exist in advance.
set nocount on
create table #col (ident int, col varchar(10))
insert #col select 1, 'red'
insert #col select 1, 'blue'
insert #col select 2, 'red'
insert #col select 2, 'yellow'
insert #col select 3, 'red'
select ident,
case when exists (select C.col from #col C where C.col = 'red' and C.ident =
#col.ident) then 'red' end as color_red,
case when exists (select C.col from #col C where C.col = 'blue' and C.ident
= #col.ident) then 'blue' end as color_blue,
case when exists (select C.col from #col C where C.col = 'yellow' and
C.ident = #col.ident) then 'yellow' end as color_yellow
from #col
group by ident
drop table #col|||If you are using SQL2K5, you can use this:
CREATE TABLE Colors
(Item int not null
,Color varchar(50) not null
)
INSERT INTO COLORS VALUES (1,'red')
INSERT INTO COLORS VALUES (1,'blue')
INSERT INTO COLORS VALUES (2,'red')
INSERT INTO COLORS VALUES (2,'yellow')
INSERT INTO COLORS VALUES (3,'red')
GO
SELECT Item, "red" AS Color_red, "blue" AS Color_blue, "yellow" AS
Color_Yellow
FROM (
SELECT Item, Color
FROM Colors
) p PIVOT (
MIN(Color)
FOR Color IN ("red","blue","yellow")
) pvt
ORDER BY Item
GO
DROP TABLE Colors
GO
If you are using SQL2K or below, then google for SQL Server and PIVOT.
HTH,
Gert-Jan
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%239zKmuVFGHA.516@.TK2MSFTNGP15.phx.gbl...
>.
> -- sql2005 only [new PIVOT clause]
> -- note in the pivot clause, those are columns, not values (strings)
> select item, [red] as color_red, [blue] as color_blue, [yellow] as
> color_yellow
> from
> (select item, color from @.x) x
> pivot
> (
> max(color)
> for color in ([red],[blue],[yellow])) as pvt
> order by item
And to think you only had to wait 5 years for this!
Are we both being factious? :)
MS is doing its best to keep RAC around.
If you can't top it......:)
www.rac4sql.net|||Some people are dragged to the funny farm,
others JOIN it :)
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23Y%23HV3VFGHA.984@.tk2msftngp13.phx.gbl...
> If this has to be done in SQL, you could try outer joining to the table
> multiple times, once for each column on your output. If you have the
option
> of using a tool to process the data outside of SQL, thaqt may be easier.
> if tblColor is the name of your table...
> select item, rcolor, bcolor, ycolor
> from
> (Select distinct item from tblColor) as Main
> left outer join (select distinct item as ritem, color as rcolor from
> tblColor where color = 'red') as red
> on item = ritem
> left outer join (select distinct item as bitem, color as bcolor from
> tblColor where color = 'blue') as blue
> on item = bitem
> left outer join (select distinct item as yitem, color as ycolor from
> tblColor where color = 'yellow') as yellow
> on item = yitem
> OR, if you dont like inline queries, this is slightly more readable:
> select Main.item, red.color, blue.color, yellow.color
> from
> (Select distinct item from tblColor) as Main
> left outer join tblColor as red
> on Main.item = red.item and red.color = 'red'
> left outer join tblColor as blue
> on Main.item = blue.item and blue.color = 'blue'
> left outer join tblColor as yellow
> on Main.item = yellow.item and yellow.color = 'yellow'
> I think you are stuck with the inline query to select the distinct items
> regardless. I can't think of a way to avoid this, but you should be able
to
> make the rest work. The performance on something like this is
surprisingly
> good, even when you have thousands of rows in your table and 20 collumns.
> As you add more columns and more filters on the data it can get a bit out
of
> hand.
> Hope this helps.
>
> <rockdale.green@.gmail.com> wrote in message
> news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
>|||Guys, Thanks for all your reply. The pivot table is interesting. I
didnot know that SQL2k5 has this functionality.
But I decided to do this convertion in client side. Because how many
colour we have is stored in another table. I can not hard code say
color_red.. etc. I know that I can dynamic generate the sql statement
in store procedure. But that is kind of overkill.
Anyway, thanks a lot.|||Hi, all
I am back to this problem since now I have more time to test it out.
I guess Raymond's solution is a neat one but it does not solve a more
complex problem like following,
based on cid column then show content in col column.
Notice that I have to add col in my group by clause, but that cause the
problem. THe result is
1 red red NULL
1 blue blue NULL
2 red NULL red
2 yellow NULL yellow
3 red NULL NULL
Which not what I want. Any Idea?
---
set nocount on
create table #col (ident int,cid int, col varchar(10))
insert #col select 1,1, 'red'
insert #col select 1,2, 'blue'
insert #col select 2,1, 'red'
insert #col select 2,3, 'yellow'
insert #col select 3,1, 'red'
select ident,
case when exists (select C.col from #col C where C.cid = 1 and C.ident
=
#col.ident) then col end as color_red,
case when exists (select C.col from #col C where C.cid = 2 and C.ident
= #col.ident) then col end as color_blue,
case when exists (select C.col from #col C where C.cid = 3 and
C.ident = #col.ident) then col end as color_yellow
from #col
group by ident,cid, col
----
drop table #col
Is there a function in MS SQL so that I can archieve the following in
SQL statement? Or do I need to loop through the record set and doing
some array element movement on client side?
Table
Item Color
1 red
1 blue
2 red
2 yellow
3 red
I want the result looks like:
Item Color_red Color_blue Color_yellow
1 red blue null
2 red null yellow
3 red null null
thanks a lotCheck out RAC @.
www.rac4sql.net
A very easy and powerful pivoting/xtab utility.
No sql coding required.|||here's a couple ways, e.g.
declare @.x table (item int, color varchar(6))
insert @.x
select 1, 'red' union all
select 1, 'blue' union all
select 2, 'red' union all
select 2, 'yellow' union all
select 3, 'red'
-- either sql 2000/2005
select item,
max(case when color='red' then 'red' end) as color_red,
max(case when color='blue' then 'blue' end) as color_blue,
max(case when color='yellow' then 'yellow' end) as color_yellow
from @.x
group by item
-- sql2005 only [new PIVOT clause]
-- note in the pivot clause, those are columns, not values (strings)
select item, [red] as color_red, [blue] as color_blue, [yellow] as
color_yellow
from
(select item, color from @.x) x
pivot
(
max(color)
for color in ([red],[blue],[yellow])) as pvt
order by item
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||If this has to be done in SQL, you could try outer joining to the table
multiple times, once for each column on your output. If you have the option
of using a tool to process the data outside of SQL, thaqt may be easier.
if tblColor is the name of your table...
select item, rcolor, bcolor, ycolor
from
(Select distinct item from tblColor) as Main
left outer join (select distinct item as ritem, color as rcolor from
tblColor where color = 'red') as red
on item = ritem
left outer join (select distinct item as bitem, color as bcolor from
tblColor where color = 'blue') as blue
on item = bitem
left outer join (select distinct item as yitem, color as ycolor from
tblColor where color = 'yellow') as yellow
on item = yitem
OR, if you dont like inline queries, this is slightly more readable:
select Main.item, red.color, blue.color, yellow.color
from
(Select distinct item from tblColor) as Main
left outer join tblColor as red
on Main.item = red.item and red.color = 'red'
left outer join tblColor as blue
on Main.item = blue.item and blue.color = 'blue'
left outer join tblColor as yellow
on Main.item = yellow.item and yellow.color = 'yellow'
I think you are stuck with the inline query to select the distinct items
regardless. I can't think of a way to avoid this, but you should be able to
make the rest work. The performance on something like this is surprisingly
good, even when you have thousands of rows in your table and 20 collumns.
As you add more columns and more filters on the data it can get a bit out of
hand.
Hope this helps.
<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>|||<rockdale.green@.gmail.com> wrote in message
news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot
>
Ugly.
Of course, you need to know what possible colors can exist in advance.
set nocount on
create table #col (ident int, col varchar(10))
insert #col select 1, 'red'
insert #col select 1, 'blue'
insert #col select 2, 'red'
insert #col select 2, 'yellow'
insert #col select 3, 'red'
select ident,
case when exists (select C.col from #col C where C.col = 'red' and C.ident =
#col.ident) then 'red' end as color_red,
case when exists (select C.col from #col C where C.col = 'blue' and C.ident
= #col.ident) then 'blue' end as color_blue,
case when exists (select C.col from #col C where C.col = 'yellow' and
C.ident = #col.ident) then 'yellow' end as color_yellow
from #col
group by ident
drop table #col|||If you are using SQL2K5, you can use this:
CREATE TABLE Colors
(Item int not null
,Color varchar(50) not null
)
INSERT INTO COLORS VALUES (1,'red')
INSERT INTO COLORS VALUES (1,'blue')
INSERT INTO COLORS VALUES (2,'red')
INSERT INTO COLORS VALUES (2,'yellow')
INSERT INTO COLORS VALUES (3,'red')
GO
SELECT Item, "red" AS Color_red, "blue" AS Color_blue, "yellow" AS
Color_Yellow
FROM (
SELECT Item, Color
FROM Colors
) p PIVOT (
MIN(Color)
FOR Color IN ("red","blue","yellow")
) pvt
ORDER BY Item
GO
DROP TABLE Colors
GO
If you are using SQL2K or below, then google for SQL Server and PIVOT.
HTH,
Gert-Jan
rockdale.green@.gmail.com wrote:
> All:
> Is there a function in MS SQL so that I can archieve the following in
> SQL statement? Or do I need to loop through the record set and doing
> some array element movement on client side?
> Table
> Item Color
> 1 red
> 1 blue
> 2 red
> 2 yellow
> 3 red
> I want the result looks like:
> Item Color_red Color_blue Color_yellow
> 1 red blue null
> 2 red null yellow
> 3 red null null
> thanks a lot|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:%239zKmuVFGHA.516@.TK2MSFTNGP15.phx.gbl...
>.
> -- sql2005 only [new PIVOT clause]
> -- note in the pivot clause, those are columns, not values (strings)
> select item, [red] as color_red, [blue] as color_blue, [yellow] as
> color_yellow
> from
> (select item, color from @.x) x
> pivot
> (
> max(color)
> for color in ([red],[blue],[yellow])) as pvt
> order by item
And to think you only had to wait 5 years for this!
Are we both being factious? :)
MS is doing its best to keep RAC around.
If you can't top it......:)
www.rac4sql.net|||Some people are dragged to the funny farm,
others JOIN it :)
"Jim Underwood" <james.underwood@.fallonclinic.com> wrote in message
news:%23Y%23HV3VFGHA.984@.tk2msftngp13.phx.gbl...
> If this has to be done in SQL, you could try outer joining to the table
> multiple times, once for each column on your output. If you have the
option
> of using a tool to process the data outside of SQL, thaqt may be easier.
> if tblColor is the name of your table...
> select item, rcolor, bcolor, ycolor
> from
> (Select distinct item from tblColor) as Main
> left outer join (select distinct item as ritem, color as rcolor from
> tblColor where color = 'red') as red
> on item = ritem
> left outer join (select distinct item as bitem, color as bcolor from
> tblColor where color = 'blue') as blue
> on item = bitem
> left outer join (select distinct item as yitem, color as ycolor from
> tblColor where color = 'yellow') as yellow
> on item = yitem
> OR, if you dont like inline queries, this is slightly more readable:
> select Main.item, red.color, blue.color, yellow.color
> from
> (Select distinct item from tblColor) as Main
> left outer join tblColor as red
> on Main.item = red.item and red.color = 'red'
> left outer join tblColor as blue
> on Main.item = blue.item and blue.color = 'blue'
> left outer join tblColor as yellow
> on Main.item = yellow.item and yellow.color = 'yellow'
> I think you are stuck with the inline query to select the distinct items
> regardless. I can't think of a way to avoid this, but you should be able
to
> make the rest work. The performance on something like this is
surprisingly
> good, even when you have thousands of rows in your table and 20 collumns.
> As you add more columns and more filters on the data it can get a bit out
of
> hand.
> Hope this helps.
>
> <rockdale.green@.gmail.com> wrote in message
> news:1136837520.587742.131180@.g49g2000cwa.googlegroups.com...
>|||Guys, Thanks for all your reply. The pivot table is interesting. I
didnot know that SQL2k5 has this functionality.
But I decided to do this convertion in client side. Because how many
colour we have is stored in another table. I can not hard code say
color_red.. etc. I know that I can dynamic generate the sql statement
in store procedure. But that is kind of overkill.
Anyway, thanks a lot.|||Hi, all
I am back to this problem since now I have more time to test it out.
I guess Raymond's solution is a neat one but it does not solve a more
complex problem like following,
based on cid column then show content in col column.
Notice that I have to add col in my group by clause, but that cause the
problem. THe result is
1 red red NULL
1 blue blue NULL
2 red NULL red
2 yellow NULL yellow
3 red NULL NULL
Which not what I want. Any Idea?
---
set nocount on
create table #col (ident int,cid int, col varchar(10))
insert #col select 1,1, 'red'
insert #col select 1,2, 'blue'
insert #col select 2,1, 'red'
insert #col select 2,3, 'yellow'
insert #col select 3,1, 'red'
select ident,
case when exists (select C.col from #col C where C.cid = 1 and C.ident
=
#col.ident) then col end as color_red,
case when exists (select C.col from #col C where C.cid = 2 and C.ident
= #col.ident) then col end as color_blue,
case when exists (select C.col from #col C where C.cid = 3 and
C.ident = #col.ident) then col end as color_yellow
from #col
group by ident,cid, col
----
drop table #col
Row to column
Is there a row to column function?
I need to convert some rows into columns in a stored proc.
An y ideas.
Thanks,
Gene
If you're using SQL2005 there is the PIVOT/UNPIVOT functions which are explained nicely in Books Online...|||
if there is multiple columns need to be pivoted, I recommand to use the legacy approach rather new PIVOT operator,
Code Snippet
Create Table #UnPivot
(
Year int,
Product int,
Sales int,
Qty int
)
Insert Into #UnPivot Values(2004,1,28,67);
Insert Into #UnPivot Values(2005,1,15,20);
Insert Into #UnPivot Values(2006,1,50,30);
Insert Into #UnPivot Values(2004,2,5,67);
Insert Into #UnPivot Values(2005,2,6,20);
Insert Into #UnPivot Values(2006,2,10,30);
Select
Product
,Max(Case When Year=2004 then Sales End) [2004-Sales]
,Max(Case When Year=2004 then Qty End) [2004-Qty]
,Max(Case When Year=2005 then Sales End) [2005-Sales]
,Max(Case When Year=2005 then Qty End) [2005-Qty]
,Max(Case When Year=2006 then Sales End) [2006-Sales]
,Max(Case When Year=2006 then Qty End) [2006-Qty]
From
#UnPivot
Group By
product
Drop Table #UnPivot
sqlrow to column
hello
TableT1 has
T1: MyID, MyDate1, MyNote1, MyCharge1, MyDate2,MyNote2, MyCharge2
How can I write my view to report these in
MyID, MyDate1, MyNote1, MyCharge1
MyID, MyDate2,MyNote2, MyCharge2
format?
You could use the following SQL Statement to make this happen:
select myid,mydate1,mynote1,mychange1
from MyInfo
union
select myid,mydate2,mynote2,mychange2
from MyInfo
order by myid
It would return appear something like the following:
MyID MyDate MyNote MyChange
1 2006-08-07 00:00:00.000 Called Dealer For Customer Service Changed his mail cost for 15 to 20
1 2006-08-08 00:00:00.000 Faxed Information to Dealer Changed Status to Enrolled
2 2006-08-04 00:00:00.000 Enrolling of Dealer Enrolled Dealer in Oil Change Mail Piece
2 2006-08-09 00:00:00.000 Dealer Called Changed to Tune-Up Mail Piece
Let me know if this works for you.
crusso
|||You should actually normalize your table so that it is easier to work with. You can do one of the following with your existing table structure:
select t.MyID
, case r.n when 1 then MyDate1 when 2 then MyDate2 end as MyDate
, case r.n when 1 then MyNote1 when 2 then MyNote2 end as MyNote
, case r.n when 1 then MyCharge1 when 2 then MyCharge2 end as MyCharge
from T1 as t
cross join (select 1 union all select 2) as r(n)
-- or
select t.MyID, t.MyDate1 as MyDate, t.MyNote1 as MyNote, t.MyCharge1 as MyCharge
from T1 as t
union all
select t.MyID, t.MyDate2 as MyDate, t.MyNote2 as MyNote, t.MyCharge2 as MyCharge
from T1 as t
Subscribe to:
Posts (Atom)