"I'm more wondering why the query optimizer would ever use the plan it currently does."
To put it another way, the question is why the following plan looks cheapest to the optimizer, compared with the alternatives (of which there are many).
The inner side of the join is essentially running a query of the following form for each correlated value of ...
The easiest query to write is for MySQL (with not strict ANSI settings). It uses the non-standard construction:
SELECT key, value
GROUP BY key ;
In recent version (5.7 and 8.0+) where the strict settings and ONLY_FULL_GROUP_BY are the default, you can use the ANY_VALUE() function, added in 5.7:
SELECT key, ANY_VALUE(value) AS value
Boolean logic - or Three valued logic
IN is shorthand for a series of OR conditions
x NOT IN (1, 2, NULL) is the same as NOT (x = 1 OR x = 2 OR x = NULL)
... is the same as x <> 1 AND x <> 2 AND x <> NULL
... is the same as true AND true AND unknown **
... = unknown **
... which is almost the same as false in this case as it will not pass ...
When I run your script to create a statistics only database and the query in the question I get the following plan.
The Table Cardinalities shown in the plan are
tblFEStatsBrowsers : 339
So it estimates that it will need to perform the scan on tblFEStatsPaperHits 339 times. Each scan has the correlated predicate ...
In my book even one scan of 50M rows is unacceptable... My usual trick is to materialize the distinct values and delegate the engine with keeping it up to date:
create view [dbo].[vwFEStatsPaperHitsBrowserID]
select BrowserID, COUNT_BIG(*) as big_count
group by [BrowserID];
create unique clustered ...
To get a count for each of those you can try
COUNT(CASE WHEN `col1` LIKE '%something%' THEN 1 END) AS count1,
COUNT(CASE WHEN `col1` LIKE '%another%' THEN 1 END) AS count2,
COUNT(CASE WHEN `col1` LIKE '%word%' THEN 1 END) AS count3
This is relatively trivial to do with a correlated subquery. You can't use the COALESCE method highlighted in the blog post you mention unless you extract that to a user-defined function (or unless you only want to return one row at a time). Here is how I typically do this:
DECLARE @x TABLE
select dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0),
group by dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0)
datediff(minute, 0, CaptureTime) gives you the number of minutes since 1900-01-01T00:00:00.
dateadd(minute, 1+datediff(minute, 0, CaptureTime), 0) adds the number of minutes ...
Similar to Aaron's solutio, shorter syntax:
SUM(col1 LIKE '%something%') AS count1,
SUM(col1 LIKE '%another%') AS count2,
SUM(col1 LIKE '%word%') AS count3
The LIKE expression makes for a boolean result. TRUE is 1, FALSE is 0, so the CASE is redundant here.
Whether the two queries you posted are logically equivalent is irrelevant; you shouldn't use either of them. I will try to steer you away from a couple of things:
Whenever possible, try to avoid applying functions to columns. It's always as good, and mostly better, to keep those calculations against constants and not columns - this can destroy SARGability ...
A logical read is counted when a single page is retrieved from buffer cache during query execution. This is counted regardless of whether a physical or read-ahead was used to cache the page, or if the page already existed in the buffer cache. Consequentially, logical reads is a measure of how many times pages were actually touched in memory during query ...
After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:
SQL Server uses different calculations in different situations. Your example is different from the linked Q & A because your range is entirely contained within a step; it does not cross a step boundary. It is also an interval with two ends rather than one. Writing BETWEEN is the same as writing two separate predicates with >= and <=.
Interval with ...
No, you don't need to gave the columns in the same order. Not least, table order may not reflect actual on-disk order (this is 100% true for SQL Server, and I'm sure MySQL is the same)
Unless your OCD itch needs scratched
I believe you're looking for the tool tablediff which lets you do exactly that - compare two replicated tables for differences. You might find this article useful, to get started.
Here is a GUI for tablediff
The two queries are logically identical and do produce the same plan. The simplification phase of the Query Optimizer handles this.
They're identical because of the constraints that are on the tables - foreign keys, uniqueness, nullability...
To find gaps in a number range:
Test table and data:
mysql> CREATE TABLE wp_blogs
-> blog_id INTEGER
mysql> insert into wp_blogs values(1);
mysql> insert into wp_blogs values(2);
mysql> insert into wp_blogs values(4);
mysql> insert into wp_blogs values(6);
mysql> insert into wp_blogs values(7);
One way to determine the logical order of joins is to replace the first inner join in your example with a left outer join:
FROM user_branch T1
LEFT JOIN dimcustomer2 T2
ON T1.BRANCH_CODE = T2.BRANCH_CODE
INNER JOIN customer_guarantee T3
ON T3.CUSTOMER_NUM = T2.CUSTOMER_NUM
Let us assume that some rows in T1 have no matches in T2. More ...
The last time I played with Access was when 2003 was the hot new thing, so this may not be entirely accurate to every detail. However, what you need to do is go to the query designer, change the view to "SQL" (i.e. raw text entry) and then you want to UNION your two left-join queries together, e.g.
SELECT ListA.*, ListB.*
FROM ListA LEFT JOIN ListB ON ListA....
The sort spill itself can probably be addressed by enabling trace flag 7470. See FIX: Sort operator spills to tempdb when estimated number of rows and row size are correct. This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to ...
CONNECT BY is the correct way to handle data that is naturally recursive.
I don't know what your table looks like but maybe something like:
FROM some_table st
START WITH st.location = 'BLDG-01'
CONNECT BY PRIOR st.location = st.parent;
This should get nodes under "BLDG-01".
The START WITH clause is your base case.
Another explanation (aside ...
Similar to @ypercubes but to get one row without 3 separate queries
count(CASE WHEN pub_id = '1389' THEN title_id END) as algodata,
count(CASE WHEN pub_id = '0877' THEN title_id END) as binnet,
count(CASE WHEN pub_id = '0736' THEN title_id END) as newmoon
where pub_id IN ('1389', '0877', '0736')
Also, decide if your ...
For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters.
from MyTable a
join MyTable b on b.id = a.id
where a.OtherVal = 100
and b.Otherval = 200
For performance, ensure you have proper indexes. In this limited example, ideally you ...
There might be other better ways but does following work for you?
,row_number() over (partition by client_id order by service_date) rn_min
,row_number() over (partition by client_id ...
You can not select aggregates across a field if you don't include the field in the group by list.
If you want the totals per year you should write
GROUP BY year
If you want the totals per parameterno it should be
GROUP BY parameterno
From the error ...
It is not clear to me what you are asking but I believe that GROUP BY is one of the most misunderstood concepts in SQL, so I'll add this answer anyhow. It may or may not help with the understanding of the concept GROUP BY. Assume we have a table like:
CREATE TABLE T
( YEAR INT NOT NULL
, PARAMETERNO INT NOT NULL
, MARK INT NOT NULL
, PRIMARY KEY (YEAR, ...
The msdb.dbo.sp_send_dbmail procedure (or other procedures) only accept either a parameter or a literal value.
Consider changing the execution of sp_send_dbmail to this:
SET @usage_data += ' end of data'
@subject='Mail from sql server',
I'm afraid the phrase "logical execution" does not make much sense; query execution by definition is physical materialization of a result set. I think what you mean by "logical execution" is the query compilation, the phase where the query syntax and semantic meaning is analyzed and the query plan is prepared to implement said semantic meaning.
The subquery from the selected answer isn't needed. To select products with all the given tag ids the query can be simply:
products AS p
tag_ties AS tt
tt.ref_id = p.id
tt.tag_id IN (10, 11, 12)
Extending this idea, we can also query based on the tag ...
LISTEN / NOTIFY for PostgreSQL
in the database...
NOTIFY static_channel_name, 'static-message';
or in a function/trigger:
perform pg_notify('dynamic-channel-name', 'dynamic-message');
in the database client:
LISTEN some_channel_name; --note the lack of quotes
The LISTEN client will receive ...