optimized indexing

Hi,
I would like to consider optimized indexing on the table which really does
not exist in my database, but will serve as an example for creating indexes.
Suppose that there is a table with following fields:
- family name,
- first name,
- dateofbirth,
- street,
- home number,
- city
Suppose also that there is an searching engine that is querying our example
table with statistics by these parameters:
- family name and first name - 50%,
- family name only - 20%,
- first name only - 15%,
- family name and street - 15%
Is it Ok (because of the best querying) to create following indexes:
- IX1 (family name, first name),
- IX2 (first name)
- IX3 (family name, street)
or there are other options?
Best Regards from SinclairSinclair (Sinclair@.discussions.microsoft.com) writes:
> I would like to consider optimized indexing on the table which really
> does not exist in my database, but will serve as an example for creating
> indexes.
> Suppose that there is a table with following fields:
> - family name,
> - first name,
> - dateofbirth,
> - street,
> - home number,
> - city
> Suppose also that there is an searching engine that is querying our
> example table with statistics by these parameters:
> - family name and first name - 50%,
> - family name only - 20%,
> - first name only - 15%,
> - family name and street - 15%
> Is it Ok (because of the best querying) to create following indexes:
> - IX1 (family name, first name),
> - IX2 (first name)
> - IX3 (family name, street)
> or there are other options?
Well, the above is not bad, but it fails to take in account the question is
which index to make the clustered index.
Since family name is popular, that is my pick for the clustered index. This
results in:
ClusterIX - (family naee)
NC_IX1 - (first name)
NC_IX2 - (street)
You may be missing family name from the non-clustered indexes, but it is
not. The clustrered index works as row locator in the non-clustered indexes,
so all columns in the clustered index appears there as well. (It would not
be wrong to explicitly include family name in these indexes.)

> I apologize to ask again. Related to the example in previous question
> about optimized indexing, should there be changes in proposed indexes,
> if the field [family name] takes part in INNER JOIN with some other
> table (Foreign key)?
With knowing only this, it is difficult to say whether it would matter
at all. But if it does, it is likely to enfoce the idea that family
name should be the clustered index.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Opportunity to provide feedback on performance improvements in SQL Server...

Please consider giving your feedback or suggestions on the top 5 OLTP and data warehouse performance improvments you would like to see in the next version of SQL Server. Use the links below to provide your comments.

http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_OLTP_PERF_IMP_NEXT_SQL.aspx

http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_DW_PERF_IMP_NEXT_SQL.aspx

Thanks

Any thought on
1. Reverse Key, Functional based and Bit map indexes?
2. UPSERT Statement (Update and insert)
3. Regular expressions in SQL Server
4. Row level trigger (due since first version)

Thanks
Bushan.|||there should something similar to XML documentation for stored procedure as in C# for T-SQL.
That will help document stored Procedures. Please correct me if there already is similar feature.|||

I would like to see SQL Server have something like the MINUS set-operator I have used in ORACLE. It provides a very easy way to see if anything is different between two tables. It is not necessary to specify the columns or any sort of JOIN condition -- merely

SELECT * FROM A MINUS SELECT * FROM B

This simple query returns all rows from A that do not match all values in some row of B. (I seem to recall that duplicate rows are not easily identified by a MINUS query.)

|||

hi Uma,

I've got the silliest idea of them all !

how about an ETL diagraming tools. Something like a flowchart

or the ERD which allows me to analyze the flow

of the etl when i have it printed. The diagram alone should be sufficient to explain the entire flow which minimize my needs to scrutine the properties of the transform.

i've got an initial idea of how it may look like hope you be interested.

thanks,

joey

Opportunity to provide feedback on performance improvements in SQL Server...

Please consider giving your feedback or suggestions on the top 5 OLTP and data warehouse performance improvments you would like to see in the next version of SQL Server. Use the links below to provide your comments.

http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_OLTP_PERF_IMP_NEXT_SQL.aspx

http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_DW_PERF_IMP_NEXT_SQL.aspx

Thanks

Any thought on
1. Reverse Key, Functional based and Bit map indexes?
2. UPSERT Statement (Update and insert)
3. Regular expressions in SQL Server
4. Row level trigger (due since first version)

Thanks
Bushan.|||there should something similar to XML documentation for stored procedure as in C# for T-SQL.
That will help document stored Procedures. Please correct me if there already is similar feature.|||

I would like to see SQL Server have something like the MINUS set-operator I have used in ORACLE. It provides a very easy way to see if anything is different between two tables. It is not necessary to specify the columns or any sort of JOIN condition -- merely

SELECT * FROM A MINUS SELECT * FROM B

This simple query returns all rows from A that do not match all values in some row of B. (I seem to recall that duplicate rows are not easily identified by a MINUS query.)

|||

hi Uma,

I've got the silliest idea of them all !

how about an ETL diagraming tools. Something like a flowchart

or the ERD which allows me to analyze the flow

of the etl when i have it printed. The diagram alone should be sufficient to explain the entire flow which minimize my needs to scrutine the properties of the transform.

i've got an initial idea of how it may look like hope you be interested.

thanks,

joey

Opportunity to provide feedback on performance improvements in SQL Server...

Please consider giving your feedback or suggestions on the top 5 OLTP and data warehouse performance improvments you would like to see in the next version of SQL Server. Use the links below to provide your comments.

http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_OLTP_PERF_IMP_NEXT_SQL.aspx

http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_DW_PERF_IMP_NEXT_SQL.aspx

Thanks

Any thought on
1. Reverse Key, Functional based and Bit map indexes?
2. UPSERT Statement (Update and insert)
3. Regular expressions in SQL Server
4. Row level trigger (due since first version)

Thanks
Bushan.|||there should something similar to XML documentation for stored procedure as in C# for T-SQL.
That will help document stored Procedures. Please correct me if there already is similar feature.|||

I would like to see SQL Server have something like the MINUS set-operator I have used in ORACLE. It provides a very easy way to see if anything is different between two tables. It is not necessary to specify the columns or any sort of JOIN condition -- merely

SELECT * FROM A MINUS SELECT * FROM B

This simple query returns all rows from A that do not match all values in some row of B. (I seem to recall that duplicate rows are not easily identified by a MINUS query.)

|||

hi Uma,

I've got the silliest idea of them all !

how about an ETL diagraming tools. Something like a flowchart

or the ERD which allows me to analyze the flow

of the etl when i have it printed. The diagram alone should be sufficient to explain the entire flow which minimize my needs to scrutine the properties of the transform.

i've got an initial idea of how it may look like hope you be interested.

thanks,

joey

Opportunity to provide feedback on performance improvements in SQL Server...

Please consider giving your feedback or suggestions on the top 5 OLTP and
data warehouse performance improvments you would like to see in the next
version of SQL Server. Please use the links below to provide your comments.
http://blogs.msdn.com/sqltips/archiv..._NEXT_SQL.aspx
http://blogs.msdn.com/sqltips/archiv..._NEXT_SQL.aspx
Thanks
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Dude, Microsoft doesn't listen to customers... don't kid yourself
Umachandar Jayachandran [MS] wrote:
> Please consider giving your feedback or suggestions on the top 5 OLTP and
> data warehouse performance improvments you would like to see in the next
> version of SQL Server. Please use the links below to provide your comments.
> http://blogs.msdn.com/sqltips/archiv..._NEXT_SQL.aspx
> http://blogs.msdn.com/sqltips/archiv..._NEXT_SQL.aspx
> Thanks
> Umachandar Jayachandran
> Microsoft SQL Server Performance Team
> SQL Server Engine Team Tips Blog at
> http://blogs.msdn.com/sqltips/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello susiedba@.hotmail.com,
s> Dude, Microsoft doesn't listen to customers... don't kid yourself
Ha! I had a nickle for everytime they listened to me and the customers I
worked with, I wouldn't need to work. You've either got an axe to grind or
don't have a clue.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||On Fri, 13 Oct 2006 02:03:09 +0000 (UTC), Kent Tegels
<ktegels@.develop.com> wrote:
in <b87ad74a0568c8bc58f4dcddd0@.news.microsoft.com>

>Hello susiedba@.hotmail.com,
>s> Dude, Microsoft doesn't listen to customers... don't kid yourself
>Ha! I had a nickle for everytime they listened to me and the customers I
>worked with, I wouldn't need to work. You've either got an axe to grind or
>don't have a clue.
>Thanks,
>Kent Tegels
>http://staff.develop.com/ktegels/
>
And you clearly don't have any code assets in VB6 :: no forward
migration path other than a complete rewrite. And SQL Server 2000 won't
be supported on Vista. It seems yours is a rather myopic view.
Stefan Berglund
|||Hello Stefan,
SB> And you clearly don't have any code assets in VB6 :: no forward
SB> migration path other than a complete rewrite.
Yep, I woke and smelled the coffee early. All 40+ of my VB4/VB5/VB6 applications
are now running faster and more reliably in .NET, most of them since late
2003. I'm sorry if *you* are stuck in the stone ages, but frankly, *you*
made that decision.
SB> And SQL Server 2000
SB> won't be supported on Vista. It seems yours is a rather myopic
SB> view.
Yawn. I hear very few of my customers or users even talking about adopting
Vista anytime soon -- there's nothing in it that's an immediate driver for
them. I am hearing chatter about IIS7, so *maybe* Longhorn looks to be painful
for them. Of course, they still have the option of running on Server 2003
R2 until they are migrated to SQL 2005.
So I'm not so sure that I'm the only one that's suffering from myopia, Stefan.

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Opportunity to provide feedback on performance improvements in SQL Server...

Please consider giving your feedback or suggestions on the top 5 OLTP and
data warehouse performance improvments you would like to see in the next
version of SQL Server. Please use the links below to provide your comments.
http://blogs.msdn.com/sqltips/archiv..._NEXT_SQL.aspx
http://blogs.msdn.com/sqltips/archiv..._NEXT_SQL.aspx
Thanks
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.
Dude, Microsoft doesn't listen to customers... don't kid yourself
Umachandar Jayachandran [MS] wrote:
> Please consider giving your feedback or suggestions on the top 5 OLTP and
> data warehouse performance improvments you would like to see in the next
> version of SQL Server. Please use the links below to provide your comments.
> http://blogs.msdn.com/sqltips/archiv..._NEXT_SQL.aspx
> http://blogs.msdn.com/sqltips/archiv..._NEXT_SQL.aspx
> Thanks
> Umachandar Jayachandran
> Microsoft SQL Server Performance Team
> SQL Server Engine Team Tips Blog at
> http://blogs.msdn.com/sqltips/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.
|||Hello susiedba@.hotmail.com,
s> Dude, Microsoft doesn't listen to customers... don't kid yourself
Ha! I had a nickle for everytime they listened to me and the customers I
worked with, I wouldn't need to work. You've either got an axe to grind or
don't have a clue.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/
|||On Fri, 13 Oct 2006 02:03:09 +0000 (UTC), Kent Tegels
<ktegels@.develop.com> wrote:
in <b87ad74a0568c8bc58f4dcddd0@.news.microsoft.com>

>Hello susiedba@.hotmail.com,
>s> Dude, Microsoft doesn't listen to customers... don't kid yourself
>Ha! I had a nickle for everytime they listened to me and the customers I
>worked with, I wouldn't need to work. You've either got an axe to grind or
>don't have a clue.
>Thanks,
>Kent Tegels
>http://staff.develop.com/ktegels/
>
And you clearly don't have any code assets in VB6 :: no forward
migration path other than a complete rewrite. And SQL Server 2000 won't
be supported on Vista. It seems yours is a rather myopic view.
Stefan Berglund
|||Hello Stefan,
SB> And you clearly don't have any code assets in VB6 :: no forward
SB> migration path other than a complete rewrite.
Yep, I woke and smelled the coffee early. All 40+ of my VB4/VB5/VB6 applications
are now running faster and more reliably in .NET, most of them since late
2003. I'm sorry if *you* are stuck in the stone ages, but frankly, *you*
made that decision.
SB> And SQL Server 2000
SB> won't be supported on Vista. It seems yours is a rather myopic
SB> view.
Yawn. I hear very few of my customers or users even talking about adopting
Vista anytime soon -- there's nothing in it that's an immediate driver for
them. I am hearing chatter about IIS7, so *maybe* Longhorn looks to be painful
for them. Of course, they still have the option of running on Server 2003
R2 until they are migrated to SQL 2005.
So I'm not so sure that I'm the only one that's suffering from myopia, Stefan.

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Opportunity to provide feedback on performance improvements in SQL Server...

Please consider giving your feedback or suggestions on the top 5 OLTP and
data warehouse performance improvments you would like to see in the next
version of SQL Server. Please use the links below to provide your comments.
http://blogs.msdn.com/sqltips/archi...QL.aspx

http://blogs.msdn.com/sqltips/archi...ps/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.Dude, Microsoft doesn't listen to customers... don't kid yourself
Umachandar Jayachandran [MS] wrote:[vbcol=seagreen]
> Please consider giving your feedback or suggestions on the top 5 OLTP and
> data warehouse performance improvments you would like to see in the next
> version of SQL Server. Please use the links below to provide your comments
.
> http://blogs.msdn.com/sqltips/archi...QL.as
px
> http://blogs.msdn.com/sqltips/archi...QL.aspx

> Thanks
> Umachandar Jayachandran
> Microsoft SQL Server Performance Team
> SQL Server Engine Team Tips Blog at
> http://blogs.msdn.com/sqltips/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.[/vbcol
]|||Hello susiedba@.hotmail.com,
s> Dude, Microsoft doesn't listen to customers... don't kid yourself
Ha! I had a nickle for everytime they listened to me and the customers I
worked with, I wouldn't need to work. You've either got an axe to grind or
don't have a clue.
Thanks,
Kent Tegels
http://staff.develop.com/ktegels/|||On Fri, 13 Oct 2006 02:03:09 +0000 (UTC), Kent Tegels
<ktegels@.develop.com> wrote:
in <b87ad74a0568c8bc58f4dcddd0@.news.microsoft.com>

>Hello susiedba@.hotmail.com,
>s> Dude, Microsoft doesn't listen to customers... don't kid yourself
>Ha! I had a nickle for everytime they listened to me and the customers I
>worked with, I wouldn't need to work. You've either got an axe to grind or
>don't have a clue.
>Thanks,
>Kent Tegels
>http://staff.develop.com/ktegels/
>
And you clearly don't have any code assets in VB6 :: no forward
migration path other than a complete rewrite. And SQL Server 2000 won't
be supported on Vista. It seems yours is a rather myopic view.
Stefan Berglund|||Hello Stefan,
SB> And you clearly don't have any code assets in VB6 :: no forward
SB> migration path other than a complete rewrite.
Yep, I woke and smelled the coffee early. All 40+ of my VB4/VB5/VB6 applicat
ions
are now running faster and more reliably in .NET, most of them since late
2003. I'm sorry if *you* are stuck in the stone ages, but frankly, *you*
made that decision.
SB> And SQL Server 2000
SB> won't be supported on Vista. It seems yours is a rather myopic
SB> view.
Yawn. I hear very few of my customers or users even talking about adopting
Vista anytime soon -- there's nothing in it that's an immediate driver for
them. I am hearing chatter about IIS7, so *maybe* Longhorn looks to be painf
ul
for them. Of course, they still have the option of running on Server 2003
R2 until they are migrated to SQL 2005.
So I'm not so sure that I'm the only one that's suffering from myopia, Stefa
n.

Thanks,
Kent Tegels
http://staff.develop.com/ktegels/

Opportunity to provide feedback on performance improvements in SQL Server...

Please consider giving your feedback or suggestions on the top 5 OLTP and
data warehouse performance improvments you would like to see in the next
version of SQL Server. Please use the links below to provide your comments.
http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_OLTP_PERF_IMP_NEXT_SQL.aspx
http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_DW_PERF_IMP_NEXT_SQL.aspx
Thanks
Umachandar Jayachandran
Microsoft SQL Server Performance Team
SQL Server Engine Team Tips Blog at
http://blogs.msdn.com/sqltips/default.aspx
This posting is provided "AS IS" with no warranties, and confers no rights.Dude, Microsoft doesn't listen to customers... don't kid yourself
Umachandar Jayachandran [MS] wrote:
> Please consider giving your feedback or suggestions on the top 5 OLTP and
> data warehouse performance improvments you would like to see in the next
> version of SQL Server. Please use the links below to provide your comments.
> http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_OLTP_PERF_IMP_NEXT_SQL.aspx
> http://blogs.msdn.com/sqltips/archive/2006/09/20/TOP_5_DW_PERF_IMP_NEXT_SQL.aspx
> Thanks
> Umachandar Jayachandran
> Microsoft SQL Server Performance Team
> SQL Server Engine Team Tips Blog at
> http://blogs.msdn.com/sqltips/default.aspx
> This posting is provided "AS IS" with no warranties, and confers no rights.

Operator precedence bug in SQL Server 2000?

Consider the following expression:
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.
<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegr oups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
.. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David
|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegr oups.com...
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan
|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David
|||http://msdn.microsoft.com/library/de...oa-oz_3qpf.asp
[vbcol=seagreen]
[vbcol=seagreen]
is evaluated before the addition operator.
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2>).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.

Operator precedence bug in SQL Server 2000?

Consider the following expression:
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> > Consider the following expression:
> > SELECT 41 % 17 % 5
> > Returns: 2
> >
> > This simply follows the rules of precedence:
> > 1) Unary operators
> > 2) Left to right per ranl
> > As all modulo operators are equal rank, it will be simply evaluated
> > left to right
> >
> > --However, if we DO introduce unary operators, it doesn't work that way
> > SELECT 41 % +17 % 5
> > Returns: 1
> > As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> > evaluated the modulo right to left.
> > As the + unary operator is basically void, it is a total mystery to me
> > why this happens.
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
>> <martijn.vels@.gmail.com> wrote in message
>> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
>> > Consider the following expression:
>> > SELECT 41 % 17 % 5
>> > Returns: 2
>> >
>> > This simply follows the rules of precedence:
>> > 1) Unary operators
>> > 2) Left to right per ranl
>> > As all modulo operators are equal rank, it will be simply evaluated
>> > left to right
>> >
>> > --However, if we DO introduce unary operators, it doesn't work that way
>> > SELECT 41 % +17 % 5
>> > Returns: 1
>> > As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
>> > evaluated the modulo right to left.
>> > As the + unary operator is basically void, it is a total mystery to me
>> > why this happens.
>> Unary + is still a valid operator and it has a lower precedence than %.
>> From BOL:
>> Operators have the precedence levels shown in the following table. An
>> operator on higher levels is evaluated before an operator on a lower
>> level.
>> Level Operators
>> 1 ~ (Bitwise NOT)
>> 2 * (Multiply), / (Division), % (Modulo)
>> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
>> (Bitwise AND)
>> . . .
>> So the expression
>> 41 % +17 % 5
>> is equivalent to as
>> 41 % +(17 % 5)
>> David
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David|||http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_3qpf.asp
>> Operators have these precedence levels. An operator on higher levels is evaluated before an operator on a lower level:
>> + (Positive), - (Negative), ~ (Bitwise NOT)
>> * (Multiply), / (Division), % (Modulo)
>> + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
>> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
>> ^ (Bitwise Exlusive OR), | (Bitwise OR)
>> NOT
>> AND
>> ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
>> = (Assignment)
>> When two operators in an expression have the same operator precedence level, they are evaluated left to right based on their position in the expression. For example, in the expression used in the SET statement of this example, the subtraction operator is evaluated before the addition operator.
--
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2>).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.

Operator precedence bug in SQL Server 2000?

Consider the following expression:
SELECT 41 % 17 % 5
Returns: 2
This simply follows the rules of precedence:
1) Unary operators
2) Left to right per ranl
As all modulo operators are equal rank, it will be simply evaluated
left to right
--However, if we DO introduce unary operators, it doesn't work that way
SELECT 41 % +17 % 5
Returns: 1
As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
evaluated the modulo right to left.
As the + unary operator is basically void, it is a total mystery to me
why this happens.
-- Adding unary operators to the let or right elements as in SELECT +41
% 17 % +5 doesn't cause any trouble.
The return valus is still ok.
Adding a unary operator to the middle element changes the left-to-right
evaluation to right to left.
I found this under SQL Server 2000 Service Pack 3
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
I upgraded to Service Pack 4:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Still, the same thing happens.
After trying this under other operators, I found the same effect for
the divide (/) operator.
Consider:
SELECT 41 / 17 / 5
Will evaluate (correctly) as (4 / 17) / 5
However,
SELECT 41 /-+17 / 5
SELECT 41 / +17 / 5
Will both evaluate as 41 / (17 / 5) and 41 / (-17 / 5) respectively.
Anyone any idea? Bug? Documented ~feature~? What's up as I see no way
how this conforms to any operator precedence.
Cheers.<martijn.vels@.gmail.com> wrote in message
news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Consider the following expression:
> SELECT 41 % 17 % 5
> Returns: 2
> This simply follows the rules of precedence:
> 1) Unary operators
> 2) Left to right per ranl
> As all modulo operators are equal rank, it will be simply evaluated
> left to right
> --However, if we DO introduce unary operators, it doesn't work that way
> SELECT 41 % +17 % 5
> Returns: 1
> As 41 % (17 % 5) = 1 it is bloody obvious that SQL server suddenly
> evaluated the modulo right to left.
> As the + unary operator is basically void, it is a total mystery to me
> why this happens.
Unary + is still a valid operator and it has a lower precedence than %.
From BOL:
Operators have the precedence levels shown in the following table. An
operator on higher levels is evaluated before an operator on a lower level.
Level Operators
1 ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
(Bitwise AND)
. . .
So the expression
41 % +17 % 5
is equivalent to as
41 % +(17 % 5)
David|||David Browne wrote:
> <martijn.vels@.gmail.com> wrote in message
> news:1131753937.040613.203430@.g44g2000cwa.googlegroups.com...
> Unary + is still a valid operator and it has a lower precedence than %.
> From BOL:
> Operators have the precedence levels shown in the following table. An
> operator on higher levels is evaluated before an operator on a lower level
.
> Level Operators
> 1 ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Positive), - (Negative), + (Add), (+ Concatenate), - (Subtract), &
> (Bitwise AND)
> . . .
> So the expression
> 41 % +17 % 5
> is equivalent to as
> 41 % +(17 % 5)
> David
I don't know if there is an easy way to see the version of BOL, but my
BOL says this:
Operators have these precedence levels. An operator on higher levels is
evaluated before an operator on a lower level:
+ (Positive), - (Negative), ~ (Bitwise NOT)
* (Multiply), / (Division), % (Modulo)
+ (Add), (+ Concatenate), - (Subtract)
=, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
etc.
In the BOL of SQL2K5 (CTP) it still has the same text:
Level Operators
1 + (Positive), - (Negative), ~ (Bitwise NOT)
2 * (Multiply), / (Division), % (Modulo)
3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
6 NOT
7 AND
8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
9 = (Assignment)
I usually use parentheses to make sure the desired evaluation order is
used. Having said that, it does seem to be a bug in either the
documentation or the implementation. One that has been around since at
least SQL7.0.
HTH,
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:43764A1B.E0EC13DE@.toomuchspamalready.nl...
> David Browne wrote:
> I don't know if there is an easy way to see the version of BOL, but my
> BOL says this:
> Operators have these precedence levels. An operator on higher levels is
> evaluated before an operator on a lower level:
> + (Positive), - (Negative), ~ (Bitwise NOT)
> * (Multiply), / (Division), % (Modulo)
> + (Add), (+ Concatenate), - (Subtract)
> =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> etc.
> In the BOL of SQL2K5 (CTP) it still has the same text:
> Level Operators
> 1 + (Positive), - (Negative), ~ (Bitwise NOT)
> 2 * (Multiply), / (Division), % (Modulo)
> 3 + (Add), (+ Concatenate), - (Subtract), & (Bitwise AND)
> 4 =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)
> 5 ^ (Bitwise Exlusive OR), | (Bitwise OR)
> 6 NOT
> 7 AND
> 8 ALL, ANY, BETWEEN, IN, LIKE, OR, SOME
> 9 = (Assignment)
>
> I usually use parentheses to make sure the desired evaluation order is
> used. Having said that, it does seem to be a bug in either the
> documentation or the implementation. One that has been around since at
> least SQL7.0.
>
The text was from the 2005 RTM BOL. I didn't bother to check SQL 2000 or
think that it changed. I guess this is a doc bug on SQL 2000 since it
clearly binds % before unary + in both SQL 2000 and 2005.
David|||http://msdn.microsoft.com/library/d...br />
3qpf.asp
[vbcol=seagreen]
[vbcol=seagreen]
is evaluated before the addition operator.
So yeah, clearly a bug.
My problem lies therein that I found this while working on my SQL
Server -> Oracle parser which automatically parses SQL expression to
Oracle format, parsing <Expr1> % <Expr2> in SQL as MOD(<Expr1>,
<Expr2> ).
Oh well, guess I'll follow the BOL and leave any resulting chaos to
blame on crappy MS developers. (They can purchase my parser if they
want, heheheh)
Thanks for the input.