Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


If I understand Views VS Functions correctly...I should use views to enapsulated commonly used where...


If I understand Views VS Functions correctly...I should use views to enapsulated commonly used where clauses?

Author
Message
Phillip.Putzback
Phillip.Putzback
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 227
Some background to my question. I am working with healthcare data and my datasource for Charges is one big transaction table with all the amounts.
Out of these transactions there are many other tables I need to join with specific where clauses to classify my data. For example. I need all the charges that were written off as Charity. So from my transaction table I need to join another table where a code is equal to a few values and another columns has to be a specific value.

For reuse-ability by other report writers should I create a specific view vCharityDefinition with the codes in the view's where clause that returns the IDs that will join to the transaction table to give me the rows I need? Or maybe create that view and then another view vCharityCharges that merges the data I need from Transactions with the vCharityDefinition for all the rows. How far deep of a hierarchy is typically before it gets to confusing? Do you make these hierarchies easier to manage via naming conventions and\or use of schemas?

And what if my Charity Definition view requires a join in it with a where clause that filters both tables. Would I split ti again so no view every filters more than one table? I read soem stairways and an article or two on Functions and it looks like i should avoid those if possible along with cross apply.

Thanks,
Phil
SQLBlimp
SQLBlimp
SSC Eights!
SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)SSC Eights! (820 reputation)

Group: General Forum Members
Points: 820 Visits: 495
Hi. You are asking a huge number of questions without giving us enough details to be helpful.

Specifically, we would need to know the layouts of the tables.

Speaking personally, I'm not the biggest fan of using views in the manner you propose. However, I admit this to be a personal preference and not a best practice.

Please send more details so we can get a feel for what you are trying to do.

Thanks
John
Phillip.Putzback
Phillip.Putzback
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 227
Maybe I should simplify it. Let's say you need to report on Midwest states in many reports, ETLS, or stored procedures... Would it not be easier to have a vie for Midwest states than typing out a list of states in an in list function for all those objects that would need to filter to Midwest states.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8550 Visits: 18142
Phillip.Putzback (5/2/2014)
Maybe I should simplify it. Let's say you need to report on Midwest states in many reports, ETLS, or stored procedures... Would it not be easier to have a vie for Midwest states than typing out a list of states in an in list function for all those objects that would need to filter to Midwest states.

How would you define the "Midwest states"? What happens if that definition changes? What happen when you want something more flexible?
Views might do the work now, but flexibility is something you should appreciate.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Phillip.Putzback
Phillip.Putzback
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 227
I'd define the Midwest states by whatever the company defined them as. Let's say I was a franchise and I was limited so a certain area. Say Indiana, Ohio and Kentucky. I would think it would be better to set that standard in one view so all reports didn't have to recreate it. Then if someday I needed to add Illinois I would only have to change it in my vMidwestRegion and all reports, procedures.. would pick it up and not require a search for dependencies. I'm just wondering if that is the typical practice. Because currently i have to republish Stored procedures every time a customer decides they are missing some data or have too much and they throw another code that needs to be excluded\included. So the any referenced procedure gets changed in the test environment then pushed into Prod. Now we do have some report writers that use functions like the common split-list function that takes a list of values and parses it to create a table. But from what I am reading here it is probably better to join a view than a function that returns a table.
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17597 Visits: 32267
Regardless of the exact definition of Midwest states, I'm extremely adverse to nesting views within views, or functions within views or functions within functions.

I recognize that creating a view means never having to figure out some difficult set of joins a second time, so that makes it extremely attractive to report writers. They have what they want in a single location and don't have to figure out the T-SQL to regenerate it.

But...

When you reference a view, the optimizer is forced to deal with every single object in that view. It determines which of them you are referencing, which of them it has to use to get your result, which of them are not necessary. That's all additional load on the optimizer. Throw in joining a view to a view, or calling a view from a view, and that load increases radically. What happens is that the optimizer can only try so many times to get you a good execution plan and then it gives up and goes with what it has. This is referred to as a timeout. If you're getting lots of timeouts on execution plans, you're probably also seeing really poor performance, or at least, very inconsistent performance as it finds different plans on different days.

While it is more difficult, I'd suggest writing T-SQL that's unique to each report. That will work better with the optimizer.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Phillip.Putzback
Phillip.Putzback
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 227
Thanks for the info. So the only way to manage frequently used filters is outside of SQL manually and then cut and paste into existing queries or new queries.
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7001 Visits: 13559
...customer decides they are missing some data or have too much and they throw another code that needs to be excluded\included...


Exclude/include values in a report usually is a scenario where lookup tables can be helpful. Using a stored procedure would allow the customer to add/remove/modify values as needed.



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Grant Fritchey
Grant Fritchey
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17597 Visits: 32267
Phillip.Putzback (5/4/2014)
Thanks for the info. So the only way to manage frequently used filters is outside of SQL manually and then cut and paste into existing queries or new queries.


When talking about parameters, then I'd say we're going to stored procedures, not functions or views.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
below86
below86
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 2121
Joining to a lookup table is the way we've got around a similar issue, ever changing values from users. And for the example of midwest states, we have a table of all states, just have a column in the table to house the value 'midwest' then reference that in the join.
Then as you add or remove states from the group 'midwest' you don't need to change your code.

-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search