Locking

Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der EeckenI don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
--
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking levels were across different
>> versions of SQL, i.e. row vs. page vs. table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the other? Guess versions 4.2 and
>> 6.x were page-locking no matter what if I remember correctly, but want to make sure before I tell
>> my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||They called it INSERT-ROW locks in SQL 6.5, but it was not row level
locking. It was just a new type of page lock that allowed multiple inserts
on the same page.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||Thanks for the answers all of you ppl! Got the answer to my colleague
correct after all. But I really appreciate your help, and I really feel
honoured since I only got replies from MVP's..Obviously my question was kind
of obscure.
Nite,
Jan Van der Eecken
Cape Town
South Africa
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23spoQjlWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> They called it INSERT-ROW locks in SQL 6.5, but it was not row level
> locking. It was just a new type of page lock that allowed multiple inserts
> on the same page.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
>> 6.5 added some possibility to get some type of row locks for INSERTs.
>> Sorry to be vague, but it was a long time ago. As I recall, you had to
>> satisfy a number of conditions (probably what indexes you had) in order
>> to get some concurrency on the page for INSERT operations.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the
>> other? Guess versions 4.2 and 6.x were page-locking no matter what if I
>> remember correctly, but want to make sure before I tell my colleagues
>> rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>>
>

Locking

Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der Eecken
I don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>
|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>
|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>

Localization.Brain Storming.

Hello,

I am working on a Blog and a Documents systems.
What I need is:
1. Each blog can have various language versions.
2. Each document can have various language versions.

I have been thinking about this and I end up with two approaches:
1. Use a structure where all tables depend on a localized table:
BLOGS
|-- BlogsLocalized
|-- BlogsPosts
|-- BlogsRatings
|-- BlogsComments

2. Use a structure where each table has a localized version
BLOGS
|-- BlogsLocalized
|-- BlogsPosts
|-- BlogsPostsLocalized
|-- BlogsComments
|-- BlogsCommentsLocalized

3. Create a simpler, without localization, in SQL and in my web sites have different versions for each language.

The same approach is under thinking for DocumentsTables.

Could someone give me some advice?

I have been looking in internet but until no I couldn't find anything really useful.

Thanks,
Miguel

Hi Miguel,

I would suggest the 3rd way.

In the database, you only store a simple version of the blog contents. But in your website, you can have serveral resource files that contains all the languages for the web site.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

Localization in SQL Server 2000

Hi.

I have distributed databases in different language versions, I am creating a stored procedure, and this problably in future is going to be migrated to other SQL Server database. the query that I have inside the stored procedure is this:

Insert Into Prueba Values(14,'31/07/1999') ---> format date of this query is in Hispanic version (this works fine)

But, If this query is migrated to other SQL Server and it's version would be in English, that query wouldn't work, the principal reason is the format date.

Solution that I have on mind is creating a stored procedure that receives three parameters the month,day and year. I want to identify the localization of that SQL Server database and use "IF" conditions and inside of these concatenate month,day and year obviously depending of the date format identified through "IF" conditions.

If somebody has an idea to solve this or somebody knows how to identify the locatization in an SQL Server database I would be thankful.

Thank you again and best regards.

Christian

Hello my friend,

You do not need to do this. Instead, change the way string dates are interpreted in your stored procedure by using the SET DATEFORMAT command in the procedure before parsing the date. Run the following to see what I mean: -

DECLARE @.datevar varchar(50)
SET @.datevar = '2007/08/04'

SET DATEFORMAT ymd
SELECT MONTH(@.datevar)

SET DATEFORMAT ydm
SELECT MONTH(@.datevar)

Kind regards

Scotty

|||

Thank you so much...

I know it's going to work fine.

Thank you so much again !!