Predicate Vs Residual Predicate

hi ,
what is the definition and difference between predicate and residual predicate.
give me some examples..Basically the columns used in where clause are called as predicates. Am i right.I hope Craig Freedman's Blog may help full for you http://blogs.msdn.com/craigfr/archive/2006/07/07/652668.aspxsql

predicate using xml in sql server 2005

Hi ,
I have a doubt using xml in sql sever 2005.
I found in many places while specifying Xpath ,the predicate contains '1'
like
/Inst:root/Inst:Location[1]/Inst:step .
I tried with some other numbers in predicate like
/Inst:root/Inst:Location[2]/Inst:step .i got null value.
Can u plz explain me what is it doing.
thx.
priya.The square brackets are an abbreviation of the position() XPath function.
E.g.:
/Inst:root/Inst:Location[1]/Inst:step
equals to
/Inst:root/Inst:Location[position() = 1]/Inst:step
Return the Inst:step child-node of the first Inst:Location node in the
Inst:root node. A null value for /Inst:root/Inst:Location[2]/Inst:step means
that there is either only one Inst:Location node or there is no Inst:step
node on the second Inst:Location.
ML
http://milambda.blogspot.com/

Predicate or key word all in uppercase?

Hi,

What is the dowside of not using all uppercase for predicates and key words?

I cannot find to see a problem beside adhering to a clean coding convention. After all I already have color coding so what would be the uppercase for?

Same question for the semicolon ; at the end of a sql block. Is that real necessary not to get in trouble sometime down the road or is it a non-issue. I find like I am now a C# guys if I use these ;

Just curious, I find tedious to switch from all upper case to normal case all the time. And not forget to type the ;

Thanks,

Philippe

Case doesn't matter for keywords. It is good to establish some consistent coding style in your organization though so it is easier to read the code. As for semi-colons, it will be good to use it in new code. There are also some new constructs that require use of semi-colon in previous statement to resolve ambiguities. And in a future version of SQL Server, we may require the use of semi-colons as statement separators.|||Thank you, got it.
About GO should I have "Some query" ; GO
or is Go Gone :-)
I never saw any advantage in using GO
it looks like it is implicit.
I suspect the answer is along the same line.|||GO is not a TSQL or SQL command. It is just a batch separator token for the client. You can configure it to be something else also if you want (like / or $$ etc).|||

Umachandar Jayachandran - MS wrote:

In a future version of SQL Server, we may require the use of semi-colons as statement separators .

Well, this seems frightening, I can't think of how many organizations would be in the impossibility to upgrade to such version without going through a major review of scores of existing (distributed) code. that seems very unlikely to happen. Lot of bugs ahead with that one.
I fully agree for brand new constructs but the day you can flip the switch to make this mandatory for every constructs seems very far away.
If it is 5 years from now, I am better off starting immediately in each piece of code I ever touch.
If it is 10 years from now, I can just start doing it for every new piece of code I create.
The legacy stuff should have vanished by then. At least for my application which has a somewhat short life cycle.
Philippe|||Our deprecation policy is fairly strict right now in the sense that we make announcement first in version N, then warning in version N + 1 and then deprecate in version N + 2. So you will have really multiple releases to transition and convert your code. Please take a look at the deprecation topics in say SQL Server 2000 and SQL Server 2005 for some examples on features that we announced for deprecation or removed and so on. Backward compatibility is a huge deal due to the installed application base for SQL Server and every release we go to great lengths to maintain that. The backward compat setting for databases are also a step towards that which allows you to run most code from previous version without changes. There are exceptions but those are considered breaking changes that you will have to fix in your application after/before upgrading. Lastly if you look at most of the examples in SQL Server 2005 Books Online or modules in Adventureworks db, you can see that the code uses statement terminators to suggest good coding practice.

predicate locking

Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET

predicate locking

Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET
Depends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas

"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NET

predicate locking

Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETDepends on how you want to quantify it. What is a Range Lock? It is the
demarcation of the begining and the end of records within an Index that
satisfies a particular Where condition. That is for a simple condition that
can be satisfied by an explicit index; however, given a more complex
condition, the range locks can be placed on the Clustered Index that would
satisfy a range of conditions.
However, it is true that there is no "true" predicate locking behavior in
SQL Server. But do not be dismayed, no commercial vendor does so either.
It would have to BLOCK, not lock, upon examination of each subsequently
submitted query to "test" whether or not there may be a logical overlap
between the initiated transaction and any subsequently requested
transaction.
Mathematical Locking is nice, but it is theoretical. A lock is where the
rubber meets the road, and some form of physical lock must be acquired.
That does not mean that it can not be done, but I have failed to see any
real implementations suggested that make practical sense.
Sincerely,
Anthony Thomas
"Daniel Roth" <Daniel Roth@.discussions.microsoft.com> wrote in message
news:7E778689-BEDD-49C4-9ADA-7261222E7593@.microsoft.com...
Hi
I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).
Where predicate locking is defined as (from the postgres help):
"12.2.2.1. Serializable Isolation versus True Serializability"
It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"
Now I put it to him that that is exactly what SERIALIZABLE does in
MS-SQL.
But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."
Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?
Regards,
Daniel Roth
MCSD.NETsql

predicate locking

Hi

I am having a debate with one of the Postgres developers (Tom Lane)
according to him ms-sql does not implement predicate
locking(SERIALIZABLE).

Where predicate locking is defined as (from the postgres help):

"12.2.2.1. Serializable Isolation versus True Serializability"

It states: "To guarantee true mathematical serializability, it is
necessary for a database system to enforce predicate locking, which
means that a transaction cannot insert or modify a row that would have
matched the WHERE condition of a query in another concurrent
transaction"

Now I put it to him that that is exactly what SERIALIZABLE does is
MS-SQL.

But his response was "Only for WHERE conditions that can be expressed
as a simple range constraint."

Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
that can be expressed as a simple range constraint." ?

Regards,

Daniel Roth
MCSD.NET(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"
> Now I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?

But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.

Here is one repro that I tried:

In the first query window I ran:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION

SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)

This query returns two rows: FISSA and PARIS

In the second I ran:

insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')

This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||(dan.c.roth@.gmail.com) writes:
> I am having a debate with one of the Postgres developers (Tom Lane)
> according to him ms-sql does not implement predicate
> locking(SERIALIZABLE).
> Where predicate locking is defined as (from the postgres help):
> "12.2.2.1. Serializable Isolation versus True Serializability"
> It states: "To guarantee true mathematical serializability, it is
> necessary for a database system to enforce predicate locking, which
> means that a transaction cannot insert or modify a row that would have
> matched the WHERE condition of a query in another concurrent
> transaction"
> Now I put it to him that that is exactly what SERIALIZABLE does is
> MS-SQL.
> But his response was "Only for WHERE conditions that can be expressed
> as a simple range constraint."
> Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> that can be expressed as a simple range constraint." ?

But what cannot be expressed a range constraint? It would be interesting
to see a counter-example where Tom Lane believes that SQL Server does
not live up the definition of serializable.

Here is one repro that I tried:

In the first query window I ran:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
go
BEGIN TRANSACTION

SELECT CustomerID FROM Customers C
WHERE NOT EXISTS (SELECT *
FROM Orders O
WHERE O.CustomerID = C.CustomerID)

This query returns two rows: FISSA and PARIS

In the second I ran:

insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
values ('ALBIN', 1, '990817', '990820',
'990819', NULL, 12, NULL, 'adddd',
'London', 'UK', NULL, 'UK')

This query blocked. Note that ALBIN is not a valid CustomerID, so once
I rolled back the first transaction, the INSERT statement failed.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.

Tom had me worried for a second.

Regards,

Daniel Roth
MCSD.NET

Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "To guarantee true mathematical serializability, it is
> > necessary for a database system to enforce predicate locking, which
> > means that a transaction cannot insert or modify a row that would have
> > matched the WHERE condition of a query in another concurrent
> > transaction"
> > Now I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland.

Tom had me worried for a second.

Regards,

Daniel Roth
MCSD.NET

Erland Sommarskog wrote:
> (dan.c.roth@.gmail.com) writes:
> > I am having a debate with one of the Postgres developers (Tom Lane)
> > according to him ms-sql does not implement predicate
> > locking(SERIALIZABLE).
> > Where predicate locking is defined as (from the postgres help):
> > "12.2.2.1. Serializable Isolation versus True Serializability"
> > It states: "To guarantee true mathematical serializability, it is
> > necessary for a database system to enforce predicate locking, which
> > means that a transaction cannot insert or modify a row that would have
> > matched the WHERE condition of a query in another concurrent
> > transaction"
> > Now I put it to him that that is exactly what SERIALIZABLE does is
> > MS-SQL.
> > But his response was "Only for WHERE conditions that can be expressed
> > as a simple range constraint."
> > Now is he correct i.e SERIALIZABLE works "Only for WHERE conditions
> > that can be expressed as a simple range constraint." ?
> But what cannot be expressed a range constraint? It would be interesting
> to see a counter-example where Tom Lane believes that SQL Server does
> not live up the definition of serializable.
> Here is one repro that I tried:
> In the first query window I ran:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> go
> BEGIN TRANSACTION
> SELECT CustomerID FROM Customers C
> WHERE NOT EXISTS (SELECT *
> FROM Orders O
> WHERE O.CustomerID = C.CustomerID)
> This query returns two rows: FISSA and PARIS
> In the second I ran:
> insert Orders (CustomerID, EmployeeID, OrderDate, RequiredDate,
> ShippedDate, ShipVia, Freight, ShipName, ShipAddress,
> ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
> values ('ALBIN', 1, '990817', '990820',
> '990819', NULL, 12, NULL, 'adddd',
> 'London', 'UK', NULL, 'UK')
> This query blocked. Note that ALBIN is not a valid CustomerID, so once
> I rolled back the first transaction, the INSERT statement failed.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||As a basic transaction question, why did the second one block if the
first hadn't changed any rows and the query was done executing?|||pb648174 (google@.webpaul.net) writes:
> As a basic transaction question, why did the second one block if the
> first hadn't changed any rows and the query was done executing?

Because that transaction had isolation level serializable. This means
that if you resubmit a query with in the transaction, you should
essentially get the same results. Therefore writes must be blocked
until the reader transaction commits.

In the default isolation level "Read Committed", the same query in
the same transaction can give different results, and thus locks can
be released once data has been read.

There is also "Repeatable Read" which is between Serializable and Read
Committed. With Repeatable Read, reading the same row twice should yield
the same result. However, a for a range query that is resubmitted newly
entered rows may appear, so called "phantom inserts".

Finally there is "read uncommitted" in which no locks are taken for
reading at all, and thus you can get to read uncommitted data.

And, oh, in SQL 2005, there is a new level called snapshot. The result
is essentially the same as in serializable, but instead of locking
rows, old versions of changed rows are added to a snapshot. In this
way readers do not block writers. Also, the reader gets a wholly
consistent view of the database at a certain point in time, which you
in fact cannot achieve with Serializable.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||It seems that the serializable should just not "get" the updated rows
when it does queries, not lock the table for everyone else... It seems
like that is what the snapshot is I guess.|||pb648174 (google@.webpaul.net) writes:
> It seems that the serializable should just not "get" the updated rows
> when it does queries, not lock the table for everyone else... It seems
> like that is what the snapshot is I guess.

Without a snapshot like there is an Oracle and Rdb, it's difficult to
do serializable without blocking writers. A snapshot effectively means
that there is more than once instance of the data, and in SQL Server
there is only one single.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp