Can we actually restrict granularity of locking

Say if i want a rowlock or a paglock, will SQL Server actually grant that ?
Or are there circumstances that no SQL may override that and grab a higher
lock if it deems fit ?
ThanksYou can use Hints to ask for the granularity you want, but it is not 100%
guaranteed. If you ask for tablock for example, and someone has a rowlock on
another row, you won't get the tablock.
You can also restrict lock granularity by using sp_indexoption. Read about
it in BOL.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e514chVFFHA.2564@.tk2msftngp13.phx.gbl...
> Say if i want a rowlock or a paglock, will SQL Server actually grant that
> ?
> Or are there circumstances that no SQL may override that and grab a higher
> lock if it deems fit ?
> Thanks
>|||"If you ask for tablock for example, and someone has a rowlock on another
row, you won't get the tablock"..
Could you elaborate more on this ? What lock would i get then ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:Or72dWXFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> You can use Hints to ask for the granularity you want, but it is not 100%
> guaranteed. If you ask for tablock for example, and someone has a rowlock
on
> another row, you won't get the tablock.
> You can also restrict lock granularity by using sp_indexoption. Read about
> it in BOL.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e514chVFFHA.2564@.tk2msftngp13.phx.gbl...
that[vbcol=seagreen]
higher[vbcol=seagreen]
>|||"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OF5uF7ZFFHA.2564@.tk2msftngp13.phx.gbl...
> "If you ask for tablock for example, and someone has a rowlock on
another
> row, you won't get the tablock"..
> Could you elaborate more on this ? What lock would i get then ?
From common sense I'd assume that you'll get the most granular lock that
doesn't conflict.
robert

> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Or72dWXFFHA.1936@.TK2MSFTNGP14.phx.gbl...
100%[vbcol=seagreen]
rowlock[vbcol=seagreen]
> on
about[vbcol=seagreen]
> that
> higher
>

Can we actually restrict granularity of locking

Say if i want a rowlock or a paglock, will SQL Server actually grant that ?
Or are there circumstances that no SQL may override that and grab a higher
lock if it deems fit ?
Thanks
You can use Hints to ask for the granularity you want, but it is not 100%
guaranteed. If you ask for tablock for example, and someone has a rowlock on
another row, you won't get the tablock.
You can also restrict lock granularity by using sp_indexoption. Read about
it in BOL.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e514chVFFHA.2564@.tk2msftngp13.phx.gbl...
> Say if i want a rowlock or a paglock, will SQL Server actually grant that
> ?
> Or are there circumstances that no SQL may override that and grab a higher
> lock if it deems fit ?
> Thanks
>
|||"If you ask for tablock for example, and someone has a rowlock on another
row, you won't get the tablock"..
Could you elaborate more on this ? What lock would i get then ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:Or72dWXFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> You can use Hints to ask for the granularity you want, but it is not 100%
> guaranteed. If you ask for tablock for example, and someone has a rowlock
on[vbcol=seagreen]
> another row, you won't get the tablock.
> You can also restrict lock granularity by using sp_indexoption. Read about
> it in BOL.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e514chVFFHA.2564@.tk2msftngp13.phx.gbl...
that[vbcol=seagreen]
higher
>
|||"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OF5uF7ZFFHA.2564@.tk2msftngp13.phx.gbl...
> "If you ask for tablock for example, and someone has a rowlock on
another
> row, you won't get the tablock"..
> Could you elaborate more on this ? What lock would i get then ?
From common sense I'd assume that you'll get the most granular lock that
doesn't conflict.
robert
[vbcol=seagreen]
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Or72dWXFFHA.1936@.TK2MSFTNGP14.phx.gbl...
100%[vbcol=seagreen]
rowlock[vbcol=seagreen]
> on
about
> that
> higher
>

Can we actually restrict granularity of locking

Say if i want a rowlock or a paglock, will SQL Server actually grant that ?
Or are there circumstances that no SQL may override that and grab a higher
lock if it deems fit ?
ThanksYou can use Hints to ask for the granularity you want, but it is not 100%
guaranteed. If you ask for tablock for example, and someone has a rowlock on
another row, you won't get the tablock.
You can also restrict lock granularity by using sp_indexoption. Read about
it in BOL.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e514chVFFHA.2564@.tk2msftngp13.phx.gbl...
> Say if i want a rowlock or a paglock, will SQL Server actually grant that
> ?
> Or are there circumstances that no SQL may override that and grab a higher
> lock if it deems fit ?
> Thanks
>|||"If you ask for tablock for example, and someone has a rowlock on another
row, you won't get the tablock"..
Could you elaborate more on this ? What lock would i get then ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:Or72dWXFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> You can use Hints to ask for the granularity you want, but it is not 100%
> guaranteed. If you ask for tablock for example, and someone has a rowlock
on
> another row, you won't get the tablock.
> You can also restrict lock granularity by using sp_indexoption. Read about
> it in BOL.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:e514chVFFHA.2564@.tk2msftngp13.phx.gbl...
> > Say if i want a rowlock or a paglock, will SQL Server actually grant
that
> > ?
> > Or are there circumstances that no SQL may override that and grab a
higher
> > lock if it deems fit ?
> >
> > Thanks
> >
> >
>|||"Hassan" <fatima_ja@.hotmail.com> schrieb im Newsbeitrag
news:OF5uF7ZFFHA.2564@.tk2msftngp13.phx.gbl...
> "If you ask for tablock for example, and someone has a rowlock on
another
> row, you won't get the tablock"..
> Could you elaborate more on this ? What lock would i get then ?
From common sense I'd assume that you'll get the most granular lock that
doesn't conflict.
robert
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:Or72dWXFFHA.1936@.TK2MSFTNGP14.phx.gbl...
> > You can use Hints to ask for the granularity you want, but it is not
100%
> > guaranteed. If you ask for tablock for example, and someone has a
rowlock
> on
> > another row, you won't get the tablock.
> >
> > You can also restrict lock granularity by using sp_indexoption. Read
about
> > it in BOL.
> >
> > --
> > HTH
> > --
> > Kalen Delaney
> > SQL Server MVP
> > www.SolidQualityLearning.com
> >
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:e514chVFFHA.2564@.tk2msftngp13.phx.gbl...
> > > Say if i want a rowlock or a paglock, will SQL Server actually grant
> that
> > > ?
> > > Or are there circumstances that no SQL may override that and grab a
> higher
> > > lock if it deems fit ?
> > >
> > > Thanks
> > >
> > >
> >
> >
>

Can someone help me with this?

Hi,
I have two tables one holds data (#temp) and the other one is a reference table(#temp2). I would like to grab date from #temp table with #temp2 table column value as my data column. If you look at my example below, you should see what I mean

--my data table
create table #temp (misc1 varchar(10), misc2 varchar(10) , misc3 varchar(10))
insert into #temp values ('Manager','NA','Texas')
insert into #temp values ('VP','EMA','London')

--my reference table
create table #temp2 (Property varchar(10), Value varchar(10))
insert into #temp2 values ('misc1','Title')
insert into #temp2 values ('misc2','Region')
insert into #temp2 values ('misc3','Location')

--my pseudo query, ofcourse this does not work and this is where I need
--help
select
misc1 as (select value from #temp2 where property='misc1'),
misc2 as (select value from #temp2 where property='misc2'),
misc3 as (select value from #temp2 where property='misc3')
from #temp2

--To get the results like below
Title Region Location
Manager NA Texas
VP EMA London
Thanks to all for helping!! :)You would have to use a technique called a correlated subquery, where the subqueries in your SELECT clause reference the primary key of the outer table in their WHERE clause.
And I have to tell you, this is database design (known as an EAV, or Entity Attribute Value schema) is one of the WORST database designs possible. This is extremely inefficient, so I hope you aren't going to be loading a lot of data into it or expecting good performance.