What is the general guideline/rule for filtering data? I am accustomed to seeing filters in an SQL statement in the WHERE clause, although there are occasions that filters introduce complexity to the SQL, making it massive and abit hard to read on first glance for intermediate developers, but well-written ones that look complex are well-tuned and optimal. Filtering can also be done in Java, but that of course has a drawback where unfiltered data from SQL can be huge and loading it in memory only to filter it out may be wasteful. Of course there are cases where you have no choice but to filter in Java if you have several datasources as dependencies that the filter requires.
SQL filters are text strings that you use to specify a subset of the data items in an internal or SQL database data type. For SQL database and internal data types, the filter is an SQL WHERE clause that provides a set of comparisons that must be true in order for a data item to be returned.
CASE in T-SQL is an expression (not a "statement") - it evaluates to a single, atomic value - and as such, it does not do any data filtering of any kind ....
To filter data in a query, open it in Datasheet View, click the down-arrow at the top of a column, and select a filter option. Here's how it looks in an Access app: You can select multiple values from the list, but in an app, the filter list closes each time you select an option.
Equality operators When used in a query, these can be used to select individual records. For example: SELECT FirstName, LastName FROM Customer WHERE CustomerID = 13; Or we can use them to retrieve records that meet a specified criteria.
Filter on the backend (sql), whenever possible. If that makes the query too complex for a junior developer then so be it. While clarity of code is important, you shouldn't make design decisions based on how well a junior developer will understand it -- its sufficient that he be able to use it.
This is particularly the case when talking about different layers, your junior developer might not know any SQL, would you then avoid a SQL backend entirely?
Write your SQL to be as clear as possible (without sacrificing performance), but do so with the expectation that the person maintaining it will be familiar with SQL and how it should be used. When crossing layers like this, a little "easier to understand" can really kill your performance (pulling data back from the db in order to update it, can take thousands of times longer than a update on the DB, inappropriate use of a cursor can be expoentally worse than a set based solution).
If the data is already in the DB, then it makes more sense to do the filtering within it, since the RDBMS will be optimized for this kind of work. If the filtering can be confusing to novice and intermediate developers, why not hide it in a view, and only grant access to the view, to the users in question?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With