October 2, 2007 at 5:47 pm
Just a little warning... you are using an undocumented behaviour and a SP could belly up your project.
Ummm... what undocumented behaviour?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2007 at 6:56 am
Ninja's_RGR'us (10/2/2007)
What is it for?
Stocks and such. We have about 50 performance markers for a stock, bond etc. Rarely are we looking at more than 1 or two, but this code is for that exceptional scenario where I might want to look at any number of them. It not only allows the user to look at what might be considered dissimilar, but also look at comparisons between day, week, month etc averages.
Lets just say, no missles, but more money that I will make in my life will be on the line 🙁
October 3, 2007 at 7:11 am
I still don't know what "undocumented behaviour" Noel is talking about...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2007 at 7:16 am
So, Bob, should we buy or should we sell?
October 3, 2007 at 7:43 am
I'm with Jeff, what's undocumented? The (#index)? That should be ok.
October 3, 2007 at 8:41 am
FROM BOL
SET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
Not sure what isn't documented.
October 3, 2007 at 12:52 pm
Bob Fazio (10/3/2007)
FROM BOLSET @variable = column = expression sets the variable to the same value as the column. This differs from SET @variable = column, column = expression, which sets the variable to the pre-update value of the column.
Not sure what isn't documented.
In what part is the UPDATE statement there ? 😉
* Noel
October 3, 2007 at 1:25 pm
In what part is the UPDATE statement there ? ;)[/quote]
Sorry here is the whole thing.
[ WITH [...n] ]
UPDATE
[ TOP ( expression ) [ PERCENT ] ]
{ | rowset_function_limited
[ WITH ( [ ...n ] ) ]
}
SET
{ column_name = { expression | DEFAULT | NULL }
| { udt_column_name.{ { property_name = expression
| field_name = expression }
| method_name ( argument [ ,...n ] )
}
}
| column_name { .WRITE ( expression , @Offset , @Length ) }
| @variable = expression
| @variable = column = expression [ ,...n ]
} [ ,...n ]
[ ]
[ FROM{ } [ ,...n ] ]
[ WHERE {
| { [ CURRENT OF
{ { [ GLOBAL ] cursor_name }
| cursor_variable_name
}
]
}
}
]
[ OPTION ( [ ,...n ] ) ]
[ ; ]
::=
{
[ server_name . database_name . schema_name .
| database_name .[ schema_name ] .
| schema_name .
]
table_or_view_name}
October 3, 2007 at 2:11 pm
Ok that's the statement. Where does it says that it will update the varibles following a certain order ?
We know that, so far, we have been able to force the use of an index on a table but the variable updating order is *not* documented 😀
Hints are just that, "hints", the optimizer may or may NOT use them 😉
* Noel
October 3, 2007 at 11:10 pm
Nothing undocumented... you need to look in BOL for the UPDATE... I've bolded the part of the update I used....
UPDATE
{
table_name WITH ( [ ...n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,...n ]
{ { [ FROM { } [ ,...n ] ]
[ WHERE
] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( [ ,...n ] ) ]
--Jeff Moden
Change is inevitable... Change for the better is not.
October 3, 2007 at 11:23 pm
noeld (10/3/2007)
Ok that's the statement. Where does it says that it will update the varibles following a certain order ?We know that, so far, we have been able to force the use of an index on a table but the variable updating order is *not* documented 😀
Hints are just that, "hints", the optimizer may or may NOT use them 😉
Columns will be returned in the order listed... variables will be returned (populated) in the order listed. Items in the SELECT list will be done in the order listed. From BOL...
Using the Select List
The select list defines the columns in the result set of a SELECT statement. The select list is a series of expressions separated by commas. Each expression defines a column in the result set. The columns in the result set are in the same order as the sequence of expressions in the select list.
Index or "Table" hints are only ignored if the table is not actually accessed... from BOL...
Table Hints
A table hint specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this SELECT. Although this is an option, the query optimizer can usually pick the best optimization method without hints being specified.
Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that only be used as a last resort by experienced developers and database administrators.
The table hints are ignored if the table is not accessed by the query plan. This may be a result of the optimizer's choice not to access the table at all, or because an indexed view is accessed instead. In the latter case, the use of an indexed view may be prevented by using the OPTION (EXPAND VIEWS) query hint.
Translation... INDEX hints are always used unless the table they are attached to is not accessed by the query...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 4, 2007 at 3:52 pm
Jeff Moden (10/3/2007)
Columns will be returned in the order listed... variables will be returned (populated) in the order listed. Items in the SELECT list will be done in the order listed. From BOL...
Using the Select List
The select list defines the columns in the result set of a SELECT statement. The select list is a series of expressions separated by commas. Each expression defines a column in the result set. The columns in the result set are in the same order as the sequence of expressions in the select list.
Index or "Table" hints are only ignored if the table is not actually accessed... from BOL...
Table Hints
A table hint specifies a table scan, one or more indexes to be used by the query optimizer, or a locking method to be used by the query optimizer with this table and for this SELECT. Although this is an option, the query optimizer can usually pick the best optimization method without hints being specified.
Caution Because the query optimizer of SQL Server usually selects the best execution plan for a query, it is recommended that only be used as a last resort by experienced developers and database administrators.
The table hints are ignored if the table is not accessed by the query plan. This may be a result of the optimizer's choice not to access the table at all, or because an indexed view is accessed instead. In the latter case, the use of an indexed view may be prevented by using the OPTION (EXPAND VIEWS) query hint.
Translation... INDEX hints are always used unless the table they are attached to is not accessed by the query...
The UPDATE Statement does not specify any ORDER on the updated ROWS so what your columns are being updated with are the previous-value which is NOT guarantee to be the prevoius ROW. in TSQL there is no ORDER BY in the update statement.
The Query Optimization Team on all occassions that I have been questioning this "feaure" DO say that HINTS are nothing but that HINTS and you should never rely on that (specially in an update).
If the optimizer code changes tomorrow your query "could" break.
* Noel
October 4, 2007 at 5:18 pm
Heh... and if they change the way Order By works, we'd all be screwed, too!...
...Wait, they did that 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
October 5, 2007 at 7:43 am
You know exactly that I am talking about standards but hey if you want to play that game with M$ the real answer is ... you never know not "if" but "when" is going to happen 😀
* Noel
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply