Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tandreasen
Starting Member
5 Posts |
 Posted - 2011-07-11 : 13:54:36
|
| I have a view that basically queries against several tables, it happens to have a nested SELECT statement:select ..., CAST(COALESCE ((SELECT COLUMN FROM SOMETABLE WHERE (SOMEOTHERCOLUMN = 'SomeValue')), 4) AS bigint) AS MyColumnVariable, ... other columns..Unfortunately, I do not think this is optimal as every row will execute this, regardless of the fact that it will always be the same value for each execution (it is a static variable from a settings table). How can I optimize this view to basically perform the query only one time on my SOMETABLE that will never change across multiple rows? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tandreasen
Starting Member
5 Posts |
Posted - 2011-07-11 : 14:21:26
|
| This is very helpful and should work perfectly, but how can I do all of this inside of a view? It doesn't like the declaration of variables, it seems..EDIT: Guess I could just create a function.EDIT2: But, if I use a function, then how can I prevent it from executing that same query per each row (back to the original problem) |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tandreasen
Starting Member
5 Posts |
Posted - 2011-07-11 : 14:36:33
|
| Got it, thanks for the help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
|
|
|
|
sql view optimization help