New answers tagged

3

You need dynamic SQL for this: do $$ declare myschema varchar(10) := 'SCHEMA1'; mywhere varchar(3) := 'ABC'; begin execute format(' create or replace view %I.myview as select * from SHAREDSCHEMA.sometable where MYCOL = %L', myschema, mywhere); end $$; The format() function is highly recommended when creating dynamic SQL to ...


0

You can use EXPLAIN (ANALYZE, BUFFERS) on the query, which will show what is really executed on the base tables. That allows you to understand where the slowness is and what causes it. This works for catalog tables just as well as for other tables.


1

As Akina mentioned, you need to use an OUTER JOIN, specifically LEFT OUTER JOIN or more simply written as LEFT JOIN to take all the records from the left-hand side of the join clause, join where they match the right-hand side of the clause, and just NULL out the fields where they don't match in the right-hand side. This tutorial from W3Schools gives a brief ...


1

This select first all individual player_IDs and uses them to get the numbers CREATE TABLE `game_match` ( `ID` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'The unique ID of the match,', `ACCOUNT_ONE_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 1.', `ACCOUNT_TWO_ID` INT(10) NOT NULL COMMENT 'Foreign key for Player 2.', `WINNER_ID` INT(10) NOT NULL ...


1

Look at: organization ( orgGUID PRIMARY KEY, orgName, ... ); person ( personGUID PRIMARY KEY, orgGUID REFERENCES organization (orgGUID), personName, ... ); contact ( contactGUID PRIMARY KEY, contactValue, contactType ENUM ('address', 'phone', ...), additionalAttributes ); junction ( orgGUID ...


1

The two queries are not equivalent. The first query without view runs window functions after applying the WHERE clause. The second query on the view runs window functions before applying the WHERE clause. This can lead to different results. And (obviously) to different query plans. Your second view peniot_json.all_pde_data does not use window functions (or ...


1

A "query" one or more "plans", linked by sys.query_store_plan. Each "plan" has multiple rows in sys.query_store_runtime_stats. If you turn on Profiler while running the built-in Query Store reports you can see lots of examples. EG exec sp_executesql N'SELECT TOP (@results_row_count) p.query_id query_id, q.object_id ...


Top 50 recent answers are included