pg_dump --exclude-table not working on one specific table Wanting to exclude a few very large table in a pg_dump, I use the -T option multiple times, like this:
pg_dump --clean clientportal -T article_plain -T callstats -T cdrviewer_cdr -T calls -T cdrviewer_datasource -T django_sesssion -T django_admin_log

article_plain however still gets included in the dump.  Using --schema-only here for demonstration purposes:
postgres@server:~/bin$ pg_dump -s --clean clientportal -T article_plain -T callstats -T cdrviewer_cdr -T calls -T cdrviewer_datasource -T django_sesssion -T django_admin_log | grep article_plain
ALTER TABLE ONLY otrs.article_plain DROP CONSTRAINT fk_article_plain_create_by_id;
ALTER TABLE ONLY otrs.article_plain DROP CONSTRAINT fk_article_plain_change_by_id;
ALTER TABLE ONLY otrs.article_plain DROP CONSTRAINT fk_article_plain_article_id_id;
DROP INDEX otrs.article_plain_article_id;
ALTER TABLE ONLY otrs.article_plain DROP CONSTRAINT article_plain_pkey;
ALTER TABLE otrs.article_plain ALTER COLUMN id DROP DEFAULT;
DROP SEQUENCE otrs.article_plain_id_seq;
DROP TABLE otrs.article_plain;
-- Name: article_plain; Type: TABLE; Schema: otrs; Owner: sg1admin
CREATE TABLE article_plain (                                                 <----- there is article_plain
ALTER TABLE article_plain OWNER TO sg1admin;
...

Why is that?

Including the schema fixed it. The other tables were in the public schema but article_plain was in a schema of it's own:
postgres@server:~/bin$ pg_dump -s --clean clientportal -T otrs.article_plain -T callstats -T cdrviewer_cdr -T calls -T cdrviewer_datasource -T django_sesssion -T django_admin_log | grep article_plain
DROP SEQUENCE otrs.article_plain_id_seq;
-- Name: article_plain_id_seq; Type: SEQUENCE; Schema: otrs; Owner: sg1admin
CREATE SEQUENCE article_plain_id_seq
ALTER TABLE article_plain_id_seq OWNER TO sg1admin;
-- Name: article_plain_id_seq; Type: ACL; Schema: otrs; Owner: sg1admin
GRANT ALL ON SEQUENCE article_plain_id_seq TO sg1;


Count rows where value differs I have the following dataset in SQL Server:

Name     Pattern
-------  -------
Peter    DRF
Peter    DRF
Peter    BBB
Peter    DRF
Peter    CCC
Peter    CCC
Sue      DRF
Sue      CCC
Sue      DRF
Sue      VVV
Sue      SSS

I need to count the number of times a pattern changes per Name (not only distinct changes). So my end result will be:

Peter 3
Sue 4

In other words, only counting the distinct changes. Peter changed from DRF to BBB to CCC and Sue changed from DRF to CCC to DRF to VVV to SSS.

Is this possible without using a cursor?

I have a start date and end date per row, but no unique id. Patterns gets logged by the system per person per day and sometimes multiple times per day per person.

Try this:

Declare @T table (name varchar(10), Pattern varchar(10))
insert into @t (name, Pattern) values
('Peter', 'DRF'),
('Peter', 'DRF'),
('Peter', 'BBB'),
('Peter', 'DRF'),
('Peter', 'CCC'),
('Peter', 'CCC'),
('Sue', 'DRF'),
('Sue', 'CCC'),
('Sue', 'DRF'),
('Sue', 'VVV'),
('Sue', 'SSS')

SELECT NAME
    ,count(DISTINCT pattern) AS DistinctChanges
FROM @t
GROUP BY NAME

| name  | DistinctChanges |
|-------|----------------|
| Peter | 3              |
| Sue   | 4              |

Note: There is a difference between distinct values and the number of times a value changed assuming some kind of timeline.

For example:
AAA, BBB, AAA, BBB, AAA, BBB, AAA would be 2 distinct changes, but 5 total times the value changed assuming some sort of order.

Which variation are you looking for?


Why is log reserve negative in the SQL Server compensation log when using fn_dblog command I was using the fn_dblog command and was not able to get why the log reserve was negative.

The function is technically undocumented, so I can't find any official word on this.  However, my understanding is that those "compensation" rows are related to a rollback.  A rollback is essentially erasing some previous log records, so it frees up some space in the transaction log.  The negative values are saying "give this much free space back to the log file."
You can observe this by performing a demo like this:
USE [master]
GO
DROP DATABASE IF EXISTS LogUsageTest;
GO
CREATE DATABASE LogUsageTest;
GO
ALTER DATABASE [LogUsageTest]
SET RECOVERY SIMPLE
WITH NO_WAIT;
GO
USE LogUsageTest;

CREATE TABLE dbo.A
(
    Id int IDENTITY(1,1) NOT NULL,
    Something int NOT NULL

    CONSTRAINT PK_A PRIMARY KEY (Id)
);
GO
CHECKPOINT;

That sets up a database in the SIMPLE recovery model with one table in it, then clears the log (with a checkpoint).  Here's the baseline transaction log usage:

Now I'll run an insert inside a transaction:
BEGIN TRANSACTION;
INSERT INTO dbo.A
    (Something)
SELECT
    v1.number
FROM master.dbo.spt_values v1;

We've gone from 5.5% usage to 13%:

And now we'll roll that insert back:
ROLLBACK;

And we've gone from 13% down to 8.9%.
You might notice that the compensations don't free up all the log space used by the insert.  In my test, the inserts reserve 178 bytes, while the rollbacks free up -74 bytes.
I think this is because the log reservations are extra big in case of page splits, which don't get rolled back.  But that's just an educated guess.


Normalization to 2NF and 3NF I solved this example to determine 2NF and 3NF. I would like to check if my answer is correct. Can somebody check the 2NF and 3NF of this example, please?

R( A B C D E F G H I J)

with functional dependencies:

AB -> C
A -> DE
B -> F
F -> GH
D -> IJ

My result:

2NF:
R1 (AB -> C , A -> E)
R2 (B -> F , F -> GH)
R3 (D -> I)

3NF:
R1 (AB -> C , A -> E)
R2 (B -> F)
R4 (F -> GH)
R3 (D -> I)

Firstly, i suggest you the steps to quickly find a correct normalization of the table, using the example that you brought, showing you the correct normalization.

Define the key

The key is usually defined by its usefulness, or its role in operations like joins (if we need a join lot of times we could choose a key instead of another).

I choose A U B as a key. I usually underline the key, but, because in this board we can't underline text, I will write the key as AB

Define the functional dependencies

You already found the FD, so we can go to the next step.

2NF Normalization

Looking at the FD the correct normalization is:

R1 (the previous big table) (**A**:R3,**B**:R2, C)
  AB -> C

(:R2 indicates a foreign key)

In this table we can find the AB -> C functional dependency.

R2 (**B**,F, G, H)
  B -> F,
  F -> GH

R3 (**A**, D, E, I, J)
  A -> DE,
  D -> IJ

3NF Normalization

R1 (**A**:R3, **B**:R2, C)
  AB -> C

R2 (**B**, F:R4)
  B -> F

R3 (**A**, D:R5, E)
  A -> DE

R4 (**F**, G, H)
  F -> GH

R5 (**D**, I, J)
  D -> IJ


MySQL: Why is DATEDIFF and TIMEDIFF/60.0/60.0/24.0 results different? I have a database with two DATETIME values, and then I generate some new values from them:

tdiff = timediff(d1,d2)+0
day1 = truncate(tdiff/60.0/60.0/24.0,0)
day2 = datediff(d1,d2)+0

All three of those "variables" are FLOAT fields in the (temporary) database. I would expect that day1 and day2 would never differ by more than 1 or 2. However, there are certain values that seem to generate a wild answer - and they tend to be for certain values of tdiff. Calculating the value from tdiff shows that the calculations are right.

Conceptually, day2 should be difference between the dates from the DATETIME values, and day1 should be the number of 24-hour periods in the same time frame.

The biggest differences are at the maximum value for timediff: using calculations, the number of 24-hour periods is just over 97. However, using the DATEDIFF function shows a time span of as low as 60 days. This makes no sense.

There are also big differences when TIMEDIFF results in 26 24-hour periods: DATEDIFF reports 10 days instead - or when TIMEDIFF results in 30 24-hour periods, and DATEDIFF reports 11 days. There are also differences when TIMEDIFF reports 79 days, but DATEDIFF gives 29 days.

Here is example output - from a Ruby script (and in Ruby array format) - with columns d1,d2,TIMEDIFF,24-hr periods,DATEDIFF:

["2012-04-11 06:15:21", "2012-02-09 22:11:21", "8.38596e+06", "97", "62"]
["2012-04-11 06:15:22", "2012-02-09 22:13:24", "8.38596e+06", "97", "62"]
["2012-04-11 06:15:25", "2012-02-09 22:15:29", "8.38596e+06", "97", "62"]
["2012-04-11 05:47:25", "2012-03-31 05:07:01", "2.64402e+06", "30", "11"]
["2012-04-11 05:47:26", "2012-03-31 05:09:03", "2.64382e+06", "30", "11"]
["2012-04-11 05:47:27", "2012-03-31 05:27:21", "2.64201e+06", "30", "11"]
["2012-04-11 05:48:43", "2012-01-27 16:39:32", "8.38596e+06", "97", "75"]
["2012-04-11 05:49:00", "2012-04-01 19:58:00", "2.2551e+06", "26", "10"]
["2012-04-11 05:49:00", "2012-04-01 20:00:02", "2.25486e+06", "26", "10"]
["2012-04-11 05:49:00", "2012-04-01 20:02:04", "2.25466e+06", "26", "10"]
["2012-04-11 05:49:05", "2012-04-01 20:04:04", "2.2545e+06", "26", "10"]
["2012-04-11 05:49:06", "2012-04-01 20:06:06", "2.2543e+06", "26", "10"]
["2012-04-11 05:49:06", "2012-01-27 17:36:07", "8.38596e+06", "97", "75"]
["2012-04-11 05:49:07", "2012-04-01 20:08:10", "2.25406e+06", "26", "10"]
["2012-04-11 06:15:06", "2012-03-13 12:23:24", "6.89514e+06", "79", "29"]
["2012-04-11 06:15:07", "2012-03-13 12:25:25", "6.89494e+06", "79", "29"]

Why are these calculations so different? I am assuming that it is a lack of understanding of TIMEDIFF, but what am I missing?

The answer lies in fully understanding the TIMEDIFF function and what it returns as an integer.

First, let's consider TIMEDIFF in its native element and what it returns:

mysql> select timediff('0:0:0','1:0:0') as timediff;
+-----------+
| timediff  |
+-----------+
| -01:00:00 |
+-----------+
1 row in set (0.00 sec)

Second, what does TIMEDIFF return in an integer context - done by adding zero:

mysql> select timediff('0:0:0','1:0:0')+0 as timediff2;
+--------------+
| timediff2    |
+--------------+
| 10000.000000 |
+--------------+
1 row in set (0.01 sec)

Now, we use the TIME_TO_SEC function to return what we actually want:

mysql> select time_to_sec(timediff('0:0:0','1:0:0')+0)/60 as timediff3;
+-----------+
| timediff3 |
+-----------+
|   60.0000 |
+-----------+
1 row in set (0.00 sec)

The integer value returned by TIMEDIFF is not minutes or seconds, but rather a base-10 representation of the actual time - so 01:00:00 becomes 10000, and 05:05:01 becomes 50501.

Instead of using TIME_TO_SEC one can also use TIMESTAMPDIFF instead of TIMEDIFF and set the units to whatever is desired:

mysql> select timestampdiff(MINUTE,'2012-04-13 0:0:0','2012-04-13 1:0:0') as timediff4;
+-----------+
| timediff4 |
+-----------+
|        60 |
+-----------+
1 row in set (0.00 sec)

Instead of MINUTE, you can also use FRAC_SECOND (microseconds), SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

The time functions in MySQL (in this case, version 5.1) are all described in the manual. The online manual also links to other versions (such as MySQL 5.5) as well.


Role problem of PostgreSQL I want to create a role named cp with some defined privileges, then we will create some other roles which will be granted with cp role. I know Oracle can do this job. For examle grant resources to user_name; which means grant resources role to a user. I do the follwing test in PostgreSQL, but it does not work. Any body know this?

--create role cp and grant privilege
postgres=# create role cp login nosuperuser nocreatedb nocreaterole
           noinherit encrypted password 'cp';
CREATE ROLE

postgres=# grant connect on database skytf to cp;
GRANT

postgres=# \c skytf skytf;
You are now connected to database "skytf" as user "skytf".

skytf=> grant usage on schema skytf to cp;
GRANT

skytf=> grant  select on skytf.test_1 to cp;
GRANT

--create role cp_1, and grant cp role privilege to cp_1
skytf=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".

postgres=# create role cp_1 login nosuperuser nocreatedb nocreaterole
           noinherit encrypted password 'cp_1';
CREATE ROLE

skytf=# grant cp to cp_1;
GRANT ROLE

--test cp_1
skytf=# \c skytf cp_1;
You are now connected to database "skytf" as user "cp_1".

skytf=> select * from skytf.test_1 limit 1;
ERROR:  permission denied for schema skytf
LINE 1: select * from skytf.test_1 limit 1;

You are explicitly setting the role to noinherit so you will need to use set rolecp before your select to use the permissions from role cp (but I'm guessing you probably just want to inherit)

From the docs:

  INHERIT
  NOINHERIT

  These clauses determine whether a role "inherits"
  the privileges of roles it is a member of. A role with the INHERIT
  attribute can automatically use whatever database privileges have been
  granted to all roles it is directly or indirectly a member of. Without
  INHERIT, membership in another role only grants the ability to SET
  ROLE to that other role; the privileges of the other role are only
  available after having done so. If not specified, INHERIT is the
  default.


Sorting by two columns conditionally Column a has category ids, column b has set orders:

+-------------+-----------+
| category_id | set_order |
+-------------+-----------+
|          80 | null      |
|          81 | null      |
|          82 | null      |
|          83 | 2         |
|          84 | null      |
|          85 | 4         |
|          86 | null      |
|          87 | null      |
|          88 | null      |
|          89 | null.     |
|          90 | null      |
+-------------+-----------+

Desired output is:

+----+
| 90 |
| 83 |
| 89 |
| 85 |
| 88 |
| 87 |
| 86 |
| 84 |
| 82 |
| 81 |
| 80 |
+----+

Please help how to write the order by clause. 83 has value 2 in column b, hence is to appear 2nd in ordering. 85 has value 4 in column b, hence is to appear 4th in ordering.

This is a job better done on application layer. Databases are not suited for this. Just do

SELECT category_id, set_order FROM your_table ORDER BY category_id DESC;

Then write a function that rearranges the array returned by above query.

For fun I did it in MySQL, just to let you see, how complicated this can be. So you see it's doable, note though, that this can be horrible performance-wise.

Sample data:

CREATE TABLE t
    (`category_id` int, `set_order` int)
;

INSERT INTO t
    (`category_id`, `set_order`)
VALUES
    (80, NULL),
    (81, NULL),
    (82, NULL),
    (83, '2'),
    (84, NULL),
    (85, '4'),
    (86, NULL),
    (87, NULL),
    (88, NULL),
    (89, NULL),
    (90, NULL)
;

Query:

SELECT category_id FROM (
    SELECT
    category_id, set_order
    , IF(set_order IS NULL, @newOrder := IF(@newOrder + 1 IN (SELECT set_order FROM t WHERE set_order IS NOT NULL), @newOrder + 2, @newOrder + 1), NULL) new_order
    FROM t,
    (SELECT @newOrder := 0) var_init_subquery_alias
    ORDER BY category_id DESC
) sq ORDER BY COALESCE(new_order, set_order);

Result:

+-------------+
| category_id |
+-------------+
|          90 |
|          83 |
|          89 |
|          85 |
|          88 |
|          87 |
|          86 |
|          84 |
|          82 |
|          81 |
|          80 |
+-------------+


Server Integrity Check CheckCommands question I am trying to figure out what is the best CheckCommands option for ServerIntegrity check.

Should I leave the job defaults as is?

EXECUTE [dbo].[DatabaseIntegrityCheck] @Databases = 'USER_DATABASES', @LogToTable = 'Y'

Should i run the CHECKALLOC,CHECKTABLE,CHECKCATALOG option separately or is it included in the above? Same for the CHECKFILEGROUP option.

If you run the base CHECKDB command, you don't need to run the others. The other commands make up what is run by the full command.

See:

CHECKDB From Every Angle: Complete description of all CHECKDB stages
CHECKDB From Every Angle: Consistency Checking Options for a VLDB

Breaking the full check into its constituent parts is sometimes done on very large databases where a full check in total is too time consuming or gobbles up too many resources.

The only CHECKDB option that results in a different full check (PHYSICAL_ONLY) runs fewer overall checks. It skips logical checks against the data and only checks the physical file structure.


How to select first row from a join that returns multple rows on the primary key This is related to this question:  Joining multiple tables results in duplicate rows

I have two tables that I am joining.  They share a key. The person table has one name per primary key but the email table has multiple emails per personId.  I want to only show the first email per person.  Presently I get multiple rows per person because they have multiple emails. I am running SQL-Server 2005.

EDIT: This is T-SQL.
First email is literally the first email row per person.

Edit 2:
First email as I see it would be the first email row that shows up in the join as SQL works through the query.  I does not matter which email shows up.  Only that no more than one email shows up.  I hope that makes it clearer.

Table1: Person
Table2: Email

Select Person.PersonName, Email.Email
From person
left join on Person.ID=Email.PersonId;

SELECT
    A.PersonName, A.Email
FROM
        (
        Select Person.PersonName, Email.Email
            ,ROW_NUMBER() OVER(PARTITION BY Person.ID ORDER BY Email.Email) AS RN
        From person
        left join Email on Person.ID=Email.PersonId
        ) A
WHERE A.RN = 1


How to make a column mandatory in every Insert / Update (not meaning default null) Following table:

CREATE TABLE kontrolle.negativtext
(
  id serial NOT NULL,
  ausschluss character varying(255) NOT NULL,
  nur_domains character varying(255)[] NOT NULL DEFAULT '{}'::character varying[],
  nicht_domains character varying(255)[] NOT NULL DEFAULT '{}'::character varying[],
  bemerkung character varying(255),
  last_change_user character varying(3) NOT NULL,
  last_change_timestamp timestamp without time zone,
  hits integer NOT NULL DEFAULT 0,
  is_regex boolean NOT NULL DEFAULT false,
  check_doc negative_doc_check[] DEFAULT '{CONTENT_TXT_DIRTY, CONTENT_TXT_CLEAN}',
  CONSTRAINT negativtext_pkey PRIMARY KEY (id)
);

Values of datarecords in this table should be changeable by multiple users. I want to force everyone updating or inserting values in this table to provide a username (we don't need to discuss why this is no foreign key).
Setting the column to NOT NULL DEFAULT NULL will prevent the value to ever be null, which is fine. But when user abc inserted a row and user xyz updates it later, xyz should need to provide a value for column last_change_user, meaning this update statement should throw an error:

Update kontrolle.negativtext set ausschluss = :a, regex : rgx where id = :id

even if there is already a username set, but this should work:

Update kontrolle.negativtext
set ausschluss = :a, regex : rgx, last_change_user = :user
where id = :id

I think a constraint wouldn't work, a trigger before update on should work but I don't have success:

create trigger mandatory_username before update on kontrolle.negativtext
for each row
execute procedure check_mandatory_username();

create or replace function check_mandatory_username()
returns trigger as $bla$
begin
    if NEW.last_change_user is null then
        raise exception 'no last_change_user provided'
        USING HINT = 'please add your name';
    else return NEW;
    end if;
end;
$bla$ language plpgsql;

This never raises an exception. If I remove the condition-parts:

create or replace function check_mandatory_username()
returns trigger as $bla$
begin
    raise exception 'no last_change_user provided'
    USING HINT = 'please add your name';
end;
$bla$ language plpgsql;

then I get the message every time, so in general this could be the way to go. But I think NEW.last_change_user does not check the provided name, but only the new value (which is the old name).

Somebody have an idea?

Your check NEW.last_change_user is null in the trigger will only be true if the UPDATE statement explicitly contains a set last_change_user = NULL, not if the value did not change (then new and old are simply identical).

You can check if the value changed using if new.last_change_user is distinct from old.last_change_user, but you can not check if the value was specified at all, possibly supplying the same value that is already in the column.

Something like Oracle's updating(column_name) function would be needed here, but Postgres does not have that.

So I think the answer to your question is: no, that's not possible.


Error when using DBCA to create database I am using Oracle Database XE (18c). The install completed without any error however, when I try to create a database using dbca it shows error: java.lang.NullPointerException.

The log is:
https://ghostbin.com/paste/G0SEb
I am still very new to Oracle. How can I solve this?

This is in your log file:
------------------------------------------------------------------------------
Global Settings
------------------------------------------------------------------------------

- Global database name : course_management
- Configuration type : Oracle Single Instance database
- SID : coursemanage

Global Database Name is meant to be a fully qualified name, like "sales.us.example.com". The first part of that name becomes the "SID" value, and must not exceed 8 characters in length (e.g. "sales.us"). In other words, pick a much shorter name.
See here: https://docs.oracle.com/en/database/oracle/oracle-database/18/ssdbi/about-assigning-global-database-names-during-installation.html#GUID-1AC9E18F-0AE4-4263-B3CC-A83B859A178F


Rounding problem with my result I have a rounding problem with the result of a query as shown below, where it feels that somewhere along the calculation the result was truncated. The result is

The SQL query is:

SELECT
    "wos"."ISOVERDUE" AS "Broken SLA",
    (COUNT("wo"."WORKORDERID")) * 100 /(
       SELECT COUNT(*)
       FROM   "WorkOrder" "wo"
       WHERE  wo.ISPARENT = '1'
              AND wo.CREATEDTIME >= < from_thismonth >
              AND wo.CREATEDTIME <= < to_thismonth >
     ) AS "%"

FROM "WorkOrder" "wo"
LEFT JOIN "ModeDefinition" "mdd" ON "wo"."MODEID"="mdd"."MODEID"
LEFT JOIN "SDUser" "sdu" ON "wo"."REQUESTERID"="sdu"."USERID"
LEFT JOIN "AaaUser" "aau" ON "sdu"."USERID"="aau"."USER_ID"
LEFT JOIN "DepartmentDefinition" "dpt" ON "wo"."DEPTID"="dpt"."DEPTID"
LEFT JOIN "WorkOrderStates" "wos" ON "wo"."WORKORDERID"="wos"."WORKORDERID"
LEFT JOIN "CategoryDefinition" "cd" ON "wos"."CATEGORYID"="cd"."CATEGORYID"
LEFT JOIN "WorkOrder_Queue" "woq" ON "wo"."WORKORDERID"="woq"."WORKORDERID"
LEFT JOIN "QueueDefinition" "qd" ON "woq"."QUEUEID"="qd"."QUEUEID"

WHERE   wo.ISPARENT='1' AND wo.CREATEDTIME >= <from_thismonth> AND wo.CREATEDTIME <= <to_thismonth>

GROUP BY "wos"."ISOVERDUE"

COUNT() returns and integer. So both the numerator and denominator of the percentage calculation, as written in the question, are integers. As it says in the documentation

  If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

The solution is to cast one or other (or both) to a non-integer type. The rules for precedence are a bit complicated so you may need to fiddle around to get just what you want. Or you can write a second CAST to deliver the percentage as desired.


How to improve this query (using table variables or not?) I'm having a bit of problems with these tables...

TableCost
(
TableCostId int,
Cost numeric,
MonthsId int
)

and

Taxes
(
Percentt numeric,
MonthsId int
)

I have to calculate a new value by getting the biggest Taxes.MonthsId that do not exceed the  TableCost.MonthsId (for each row).

For example, if I have the following registers:

TableCost:

(1,10,1),
(2,5,3),
(3,12,7),
(4,17,8),
(5,22,11)

Taxes:

(55,1),
(60,2),
(65,6),
(70,10),
(75,12)

To calculate the TaxedCost of the

first row of the TableCost -> 10*55
Second row -> 5*60
Third row ->12*65
Fourth row -> 17*65
Fifth row -> 22*70

The select must return the following fields

(Cost,TaxedCost)

I've tought of declaring a table variable... storage the Taxes values on it, runs a normal select on the TableCost table and looping each of its rows calculating the TaxedCost after the select.

Any ideas of improve this query?

You can do this with a correlated sub query.

SELECT Cost,
       Cost * (SELECT TOP 1 Percentt
               FROM   Taxes t
               WHERE  t.MonthsId <= tc.MonthsId
               ORDER  BY t.MonthsId DESC) AS TaxedCost
FROM   TableCost tc

You may well get a more efficient plan though if you simply fill in the gaps in your Taxes table so you can do a straight forward join on MonthsId. At least that should allow the optimiser to consider something other than nested loops.


Aliases with correlated subqueries I am looking at an example of correlated subquery with aliases from a PostgreSQL book:

bpsimple=# SELECT * FROM orderinfo o,
bpsimple=# (SELECT * FROM customer c WHERE town = 'Bingham') c
bpsimple=# WHERE c.customer_id = o.customer_id;

Why is the c alias used twice here? Isn't the one inside the parenthesis enough?

As @ypercube already explained, the subquery has no reference to columns in the outer query, it can be processed independently. So it is not a "correlated subquery". Some call that a "derived table", or just "subquery".

SELECT *
FROM   orderinfo o
    , (SELECT * FROM customer c1 WHERE town = 'Bingham') c2
WHERE  c2.customer_id = o.customer_id;

As to your questions:

c1 is a table alias for customer in the subquery, short for customer AS c1. The key word AS has been omitted which is fine since it would be just noise for a table alias. I quote the manual on "Omitting the AS Key Word":

  In FROM items, both the standard and PostgreSQL allow AS to be omitted
  before an alias that is an unreserved keyword. But this is impractical
  for output column names, because of syntactic ambiguities.

The example probably goes to demonstrate visibility: only the outer table alias is visible in the outer WHERE clause, so there is no naming conflict with c used twice.
Otherwise, c1 is useless here since nothing refers to it. You can just drop it.
c2 is another table alias for the "derived table". This one is mandatory since Postgres requires a name for every used table, and a subquery has none until you name it.

The example is not very good overall. Such a query should rather use an explicit JOIN clause and the subquery is just useless. This would be better, shorter and faster:

SELECT *
FROM   orderinfo
JOIN   customer  c USING (customer_id)
WHERE  c.town = 'Bingham';

The only difference: customer_id is listed once instead of twice in the result (due to the USING clause), which would be preferable since it is completely redundant in this case.

Details in the manual about SELECT.


How to use variables with RESTORE FILELISTONLY Storing the result of RESTORE FILELISTONLY in a temporary table called #filepaths.

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN
    RESTORE FILELISTONLY FROM DISK = 'Z:\BACKUPS\my_database_backup.bak'
END
GO

insert into #filepaths
exec sp_restore

How can I get 'Z:\BACKUPS\my_database_backup.bak' into a variable? So that the script looks similar to this.

DECLARE @BACKUP_PATH as nvarchar(max) = 'Z:\BACKUPS\my_database_backup.bak'

IF object_id('sp_restore') IS NOT NULL
    drop procedure sp_restore
    go

CREATE PROCEDURE sp_restore AS
BEGIN
    RESTORE FILELISTONLY FROM DISK = @BACKUP_PATH
END
GO

insert into #filepaths
exec sp_restore

Thanks,
Craig

I think you have things just a little messed up. How about:

CREATE PROCEDURE dbo.usp_restore
  @backup_path NVARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    RESTORE FILELISTONLY FROM DISK = @backup_path;
END
GO

Creating the procedure should be completely separate from any individual invocation. So in a different window, or two weeks later, you should be able to do this (again, completely separately from the creation of the stored procedure):

DECLARE @bp NVARCHAR(MAX) = N'Z:\BACKUPS\my_database_backup.bak';

INSERT #filepaths EXEC dbo.usp_restore @backup_path = @bp;

Some other commentary you should look into:

Why you shouldn't use the sp_ prefix
Why you should always use the schema prefix

If you don't want a stored procedure to help with this (why not?), sure you can do this in a much messier way. Assuming you already have a #temp table, #t, created with the right columns and data types:

DECLARE @path NVARCHAR(MAX) = N'Z:\BACKUPS\my_database_backup.bak';

DECLARE @sql NVARCHAR(MAX) = N'RESTORE FILELISTONLY FROM DISK = @path;';

INSERT #t EXEC master.sys.sp_executesql @sql, N'@path NVARCHAR(MAX)', @path;

But still, why not just have a stored procedure ready when you need it, instead of porting around all this bulky code?


Select parent with a specific child with ordering by a column with no grouping by this column I have got a parent table, let's call it Post, with two columns - PostId, Title. I have got another one table, let's call it Comment - CommentId, PostId (FK), CreatedDate, Type, Message

I need to get a flat view of parent and one of it's childs which will look like below:

PostId, Title, CommentId, CreatedDate, Type, Message

I need the oldest one comment in this view for each post, but I also need Type and message though I don't want to group by CreatedDate, Type and Message.

Without grouping by them I can't use ORDER BY

I have got two options here - using PARTITION OVER and CROSS/OUTER APPLY. First is not an option cause of performance gain, second one is ok but I can't for example filter by Type (WHERE Type = 3) since it's a view.

What I've tried:

Option one:

SELECT DISTINCT
        P.PostId,
        P.Title
FROM dbo.Post AS P
OUTER APPLY
(
    SELECT TOP 1
        C.CommentId,
        C.CreatedDate,
        C.Type,
        C.Message
    FROM dbo.Comment C
    WHERE
        C.PostId = P.PostId
    ORDER BY C.CreatedDate ASC
)

Option two:

SELECT
        P.PostId,
        P.Title,
        C.CommentId,
        C.CreatedDate,
        C.Type,
        C.Message
        ROW_NUMBER() OVER (PARTITION BY p.PostId
                           ORDER BY C.CreatedDate ASC) AS Generation
FROM dbo.Post P
LEFT JOIN dbo.Comment C
ON C.PostId = P.PostId

and then use WHERE Generation = 1 from application server

To be clear, the issue is that OUTER APPLY performs great with SQL side paging (skip 100 take 100 for example), but when I need to take a count - it's a bottleneck since of each row processing

Not sure if it helps, but I need something like this:

You concern yourself for nothing. "it's a bottleneck since of each row processing" is an assumption you make, and not necessarily what the optimizer will choose to do, even if it looks to you that you write it that way.

SQL is a declarative language, and as such you tell the engine what you want done, and not how you want it done, like you do in imperative languages.

The query optimizer, especially in SQL Server, is a mind blowing wizard in moving things around, considering alternatives, and understanding what it is that you want, regardless of how you write it. It doesn't get it 100% right, but it is damn good.

Write your query, use several syntax like you did is never a bad idea, and test it against a representative data set. If you see performance challenges, examine the execution plan, or post it here so people can help you.

HTH


NoSQL, CouchDB vs. CouchBase, What should i do? I started to encounter some issues on my production site, when having a web page that needs to load a very big ResultSet (currently coming from relational database, MySQL) it takes forever, and problem these result sets are only getting bigger and bigger.

I started to seek after a better solution, and what I came across with is the idea of saving the data in a NoSQL database. (I'm already using Mongo, but Mongo is inefficient due to high amount of DML in my environment.) So when searching the web I thought about the following 2 options:

CouchDB
CouchBase

When looking at both of the above, I can tell say that both are JSON document based (ok, that's a good start), but when getting into some technical background I do look for better caching (I don't wan't to kill my server's I/O) then MongoDB's master-master replication ability (I saw that CouchDB can replicate easily based on source->destination / destination->source).

Can someone provide me some of your input, and if you had tried the above solutions I will be happy to hear about your experience.

IMO you are making what is probably a pretty common mistake when it comes to web pages which is to assume that the answer to performance problems due to initial result size on MySQL is to jump to NoSQL solutions often with little understanding of what the tradeoffs are or how to use them appropriately and effectively.

I would be surprised if a well-tuned db was actually the problem here for a web app, if the size of the result set is the issue.  The simple fact is that a result set can only be retrieved from disk so fast (assuming you aren't using a main memory db where everything is forced in RAM), and then you actually have to spend time processing the result set to get your web page.  You need to start by fully profiling everything before assuming it's the db.

Your most basic tradeoff in NoSQL is data entry flexibility and easy scaling vs integrity guarantees and data processing on output.   The only way to do data processing in NoSQL over a result set of any size is to essentially do it on input and this has significant impacts over the life of a product if a NoSQL solution is used at the expense of a traditional RDBMS.  On the other hand, were appropriate these offer adjuncts to the RDBMS which can be helpful for both pre- and post-processing.  In short there are reasons to choose NoSQL but size really isn't one of them.

Now, you mention here that this is a "web page" that is loading a "very big result set."  Now, I do crazy things with web apps sometimes, and I suspect that if you are really loading a very large result set directly into a web page you have plenty of issues other than db performance.

In LedgerSMB for example, I know of cases where we pull over a thousand invoice rows to generate a single web page for some users (we use PostgreSQL).  For us PostgreSQL hums along quite well, even when aggregating across tends of thousands of records pulled out of multi-million-record tables.  Our time spent (profiled) per page load at that level is approx 15 sec of db time to up to 5 min of web app time to generate the web page.  (This is acceptable for the reason that it does globally optimize the workflow for this customer, keep in mind the web page may have up to 20k input elements, and the data has to be significantly transformed between where the db server sends it and where the web page is built).   This may not match your use case specifically but it may give you an idea of the fact that the database doesn't have to be the bottleneck there, and probably isn't if you are doing a lot with a web page.

Here are some aspects of troubleshooting and options you have if the db is in fact the problem.

Profile your whole application.  How much time is actually spent on db stuff?  How much is spent processing the page for display?
Profile your db queries.  What can be done to make them more efficient?

Do this before concluding that a different db will fix your problem.

Now if it turns out that you really have pushed this to the max, then you need to look at your choices.  These include:

PostgreSQL (yes, a relational db).  One thing this has going for it are much more generally optimized table/index structures (InnoDB specializes in pkey lookups, meaning that other searches are slower).
VoltDB (another Relational DB, but this one is main memory for high-speed oltp and very fast)
You can build a caching layer with a NoSQL db which works alongside your rdbms.  This is where you might be able to use MongoDB or CouchDB.


Postgres 9.1.16 Hot Standby I am using PostgreSQL 9.1.16 (and I cannot upgrade) with Hot Standby.

We have a few batches that perform some heavy writes, resulting in a lot of WAL bein produced in a short period of time.

The standby desynchronizes because the pg_xlog becomes full.

I could just increase the FS but I would like to understand.

I found out that with this version of PostgreSQL, the restartpoints are triggered only because of the checkpoint_timeout (5 min) parameter on the standby.

The primary checkpoints a lot (every 5 sec or so)  because of the WAL activity (triggered by checkpoint_segments (10) ). The result is that my standby doesn't recycle its WAL quickly enough and the FS becomes full before the next checkpoint.

Since checkpoints every 5s is "a bit too much", I tried to set checkpoint_segments to 50 on the primary and checkpoint_timeout to 30s in the standby. It's ugly but somehow the checkpoint timing are in synch and the standby doesn't "desync".

My questions are:

In 9.1.16 and before. How did you handle restart point when it was triggered only by checkpoint_timeout?
Can I do something else PostgreSQL wise than this ugly workaround ?
I don't have much experience with pg in real world. my pg_xlog FS is 5G. Is it a normal size or is it way too small ?

PS: Feel free to ask me more info, it's my first post.

PS2: the article I found about checkpoint_timeout on standby http://www.postgresql.org/message-id/AANLkTimpudRqHf8kFfQZxyMHsULYo79qwK5PCXxP21gt@mail.gmail.com

To answer your questions:

Set the checkpoint_timeout lower, as you did, to 30 seconds or so. I made sure to enable log_checkpoints in my postgresql.conf and used check_postgres.pl to monitor the number of segments and replication lag.
You can also set checkpoint_completion_target=0 to adjust the checkpointing behavior as well. But really, on a replica, checkpoint_timeout is really the only way to keep the pg_xlog directory down in size on a replica.
Additional reference here: pgsql bugs #7801
Adjusting checkpoint_timeout is unfortunately the best option. There are some tips on dealing with pg_xlog filling up in this blog post: Solving pg_xlog out of disk space problems, but these mostly apply to a primary that's run out of space, and the panic related to that.
PostgreSQL WAL segments take up approximately this much room, according to the documentation for 9.1 WAL Configuration:

(2 + checkpoint_completion_target) * checkpoint_segments + 1 or checkpoint_segments + wal_keep_segments + 1 files

So if you do the math like this for example, with a checkpoint_completion_target=0.9 and checkpoint_segments = 50:

(2 + 0.9) * 50 + 1 = 146 * 16MB = 2336 MB

Which is only about 45% of your 5 GB of your pg_xlog filesystem. So long as you aren't getting checkpoint warnings in your logs, and the space isn't in danger of being completely used up, 5 GB should be fine. Using these formulas, you should be able to appropriately size your pg_xlog filesystem for your usage.

Greg Smith has a lovely chart in PostgreSQL 9.0 High Performance, referenced here: Server Configuration Tuning Practices. I would also recommend the book as a reference, even though it's a bit old, because a lot of the information in it is still quite applicable.

Hope that helps. =)


Where are user defined functions saved in PostgreSQL? To clarify the issue I am having, I am writing pgtap unit tests on DB and one of my functions returns a table. However, the pgtap function function_returns does not have a definition for table check and their columns (names and data types).
These are all the overloads of the function:
SELECT function_returns( :schema, :function, :args, :type, :description );
SELECT function_returns( :schema, :function, :args, :type );
SELECT function_returns( :schema, :function, :type, :description );
SELECT function_returns( :schema, :function, :type );
SELECT function_returns( :function, :args, :type, :description );
SELECT function_returns( :function, :args, :type );
SELECT function_returns( :function, :type, :description );
SELECT function_returns( :function, :type );

You may read the full definition and what each parameter stands for in the official documentation
Basically the issue is that :type does not work for table.
So I am wondering where these user defined functions are saved in Postgres (trying to write my own version and hopefully contribute to the pgtap project).
After digging in the source code I found the function used in the background to check for this, it's called _func_compare
CREATE OR REPLACE FUNCTION _func_compare( NAME, NAME, NAME[], anyelement, anyelement, TEXT)
RETURNS TEXT AS $$
    SELECT CASE WHEN $4 IS NULL
      THEN ok( FALSE, $6 ) || _nosuch($1, $2, $3)
      ELSE is( $4, $5, $6 )
      END;
$$ LANGUAGE SQL;

And I can't find out how the author was checking for valid return definition of the function.

You can get the definition of a function from the pg_proc system catalog.
If prorettype is contains the special record type, the actual return types are the elements in proallargtypes where the corresponding proargmodes entry is t, o or b.
This query should give you all functions maned test together with their object ID, the input types and the result types:
SELECT p.oid,
       p.proname,
       p.proargtypes::regtype[] AS input_types,
       /* only list output arguments */
       array_agg(a.type::regtype ORDER BY a.n)
          FILTER (WHERE a.mode IN ('t', 'o', 'b'))
          AS result_types
FROM pg_proc AS p
   CROSS JOIN LATERAL (/* get all arguments for table functions */
                       SELECT *
                       FROM unnest(p.proallargtypes, p.proargmodes) WITH ORDINALITY
                       UNION
                       /* get the return type for other functions */
                       SELECT p.prorettype, 'o', 1
                       WHERE p.proallargtypes IS NULL
                      ) AS a(type,mode,n)
WHERE p.proname = 'test'
GROUP BY p.oid, p.proname, p.proargtypes;


Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared I recently installed an Oracle 12c in Oracle Linux 7.6, it was previously in Windows.  In the first database restored, some functions are with compilation errors.

The query below informs you that there is a syntax error on line 2, but I can not figure out where the problem is.

Error: Error (2.17): PLS-00201: The identifier 'DAYS_INTERVAL_TABLE' must be declared

Query:

create or replace FUNCTION funRetornaDias (p_inicio date, p_termino date)
         RETURN DAYS_INTERVAL_TABLE PIPELINED IS

    v_date date;
    out_rec DAYS_INTERVAL := DAYS_INTERVAL(NULL,NULL);
    v_ref date;

BEGIN

    if p_inicio > p_termino then
        v_ref := p_inicio;
        v_date := p_termino;
    else
        v_ref := p_termino;
        v_date := p_inicio;
    end if;

    while cast(to_char(v_date, 'YYYYMMDD') as number) <= cast(to_char(v_ref, 'YYYYMMDD') as number) loop
    begin

        out_rec.date_ := v_date;
        out_rec.day_of_week := datepart('DW', v_date);

        PIPE ROW(out_rec);
        v_date := v_date + interval '1' day;

    end;
    end loop;

    RETURN;

END;

I checked the impdb log and saw that this object was not imported due to an OID conflict.

At the beginning of the log I found this message.

ORA-39083: Object type TYPE:"VERZANI"."DAYS_INTERVAL" failed to create with error:
ORA-02304: invalid object identifier literal

    Failing sql is:
    CREATE EDITIONABLE TYPE "VERZANI"."DAYS_INTERVAL"   OID 'C01480BB3D223837E0430100007FED58' AS OBJECT
           (DATE_           DATE,
            DAY_OF_WEEK     number(1,0));
    ORA-39083: Object type TYPE:"VERZANI"."DAYS_INTERVAL_TABLE" failed to create with error:
    ORA-02304: invalid object identifier literal

    Failing sql is:
    CREATE EDITIONABLE TYPE "VERZANI"."DAYS_INTERVAL_TABLE"   OID 'C01480BB3D273837E0430100007FED58' AS TABLE OF DAYS_INTERVAL;

So I dropped the schema and rerun the impdp again with the parameter TRANSFORM=oid:n. Now the database went up without any problems.


Hierarchical data using closure table, how to ensure that a column is unique within the parent node? I store hierarchical data in a Node-table, combined with a closure table (NodeClosure). Each node has a column "name" that should be unique among the sibling nodes (link to parent-Node + name should be unique).
This is definition of the tables:

Table "Node"

Column "nodeID" (int not null, autoincrement, primary key)
Column "name" (text not null)

Table "NodeClosure"

Column "ancestorID" (int not null, primary key, foreign key to Node)
Column "descendantID" (int not null, primary key, foreign key to Node)
Column "depth" (int not null)

For each parent node, I want the names of its direct child nodes to be unique for that parent node.
Do I have to add a parentID-column to Node (and duplicate the parent/child information) to be able to add a unique index on parentID + name, or is there a different solution?

I'm assuming (from the fact you considered adding a parentId column) that each Node has a single parent.
Two things to do:

Add parent column to Node, with a self-referencing foreign key to the nodeID column.  This ensures that each Node links to a single parent Node.  This should be null for the root node.
Get rid of NodeClosure table.  Seriously, you don't need it.

So your design becomes simply:
Node

nodeID (int not null, autoincrement, primary key)
parentID (int null, foreign key to Node(nodeID))
name (text not null)

Now you can indeed enforce unique names among siblings by adding a unique constraint on parent + name.
You can derive the ancestor/descendent relationship and level that was previously in NodeClosure using queries against the Node table.  Syntax for this varies depending on database.


Can I disable journalling to improve performance We have a 16 shard MongoDB 2.4 installation running in AWS that is eating money.

The data is volatile, it only gets used within 15 minutes of creation
If the cluster goes down we wipe it out and restart ~ 30 seconds downtime

I note that the majority of disk stress appears to be journalling

Is there any reason I cannot disable journalling?

It should be noted that on any MongoDB system with significant write volume, most of the continuous/ongoing disk usage will come from the journal - after all it syncs to disk every 100ms - so this observation is not unusual.

If you truly can wipe out 16 shards and restore in 30 seconds (which is impressive), then you can eliminate the journal with minimal risk, yes, but you could also be less drastic.  But, before you do, consider that getting rid of the journal means that a non-clean restart of a mongod leaves your data in an unknown state (it might be OK, it might not).  You will want to be sure you have ways to detect any discrepancies (that might not present as an outage) if you run in this mode.

It's also worth noting that this would essentially be an unsupported mode of operation for production, so getting any help with a problem will likely mean having to turn journaling back on.

Finally, before you turn off the journal, consider these somewhat less drastic steps first:

Move the journal to its own disk/volume - it's a standard write ahead journal meaning sequential writes - very different to the usual data writes, and so having it on its own disk/volume can be significantly more efficient, especially if your other IO is more randomly distributed (common with MongoDB).  Finally, as of writing this, you should never have the journal on an NFS mount - it does not play well (may not apply here, but good advice in general).
Change the commit interval of the journal - the default is 100ms, but you can increase to 300ms (see the docs here) - this is a minor tweak but may help
Although it is not directly related to journaling, the other competing writes will come from the fsync process for data (every 60 seconds by default).  If you alter this you may improve that performance also and remove stress from the disk also.  Again, see the docs on syncdelay for details.
Take a look at how you are writing data - sometimes large array updates, large or un-needed indexes etc. can translate into more writes than they should.


Calculate percentage of records that have an association in another table Table A                                           Table B
Columns                                           Columns
Performance Count                                  Key
Source                                             Record
Date
Key

I need the %age of Total Performance counts for a Source and Date that have a value in the record column of Table B

select sum(a.Performance Count) As TotalPerfCount, a.Source, a.Date,
(100 * b.Record / a.sum(a.PerformanceCount)) AS Percentage
from Table A a
JOIN Table B b ON a.key = b.key
where b.Record is not null
Group By a.TotalPerfCount, a.Source, a.Date

This isn't pulling all records that have a value in b.Record.  what am I doing wrong?

In order to calculate a percentage like that you need a count of rows in table b divided by a count of rows in table a.

Something like this:

USE tempdb;

DROP TABLE IF EXISTS dbo.b;
DROP TABLE IF EXISTS dbo.a;
GO

CREATE TABLE dbo.a
(
    a_id int NOT NULL
        CONSTRAINT a_pk
        PRIMARY KEY CLUSTERED
    , someData varchar(10) NOT NULL
    , rowDate datetime NOT NULL
);

CREATE TABLE dbo.b
(
    b_id int NOT NULL
        CONSTRAINT b_pk
        PRIMARY KEY CLUSTERED
    , a_id int NOT NULL
        CONSTRAINT b_a_id_fk
        FOREIGN KEY
        REFERENCES a(a_id)
    , someOtherData varchar(10) NOT NULL
);

INSERT INTO dbo.a (a_id, someData, rowDate)
VALUES (1, 'abcdefghij', GETDATE())
    , (2, 'abcdefghik', GETDATE());

INSERT INTO dbo.b (b_id, a_id, someOtherData)
VALUES (1, 1, 'blah');

;WITH a AS
(
    SELECT cntA = COUNT(1)
    FROM dbo.a
)
, b AS
(
    SELECT cntB = COUNT(1)
    FROM dbo.b
        INNER JOIN dbo.a ON b.a_id = a.a_id
)
SELECT a.cntA
    , b.cntB
    , [percentage] = (CONVERT(decimal(10, 2), b.cntB) / a.cntA) * 100
FROM a, b;

The output looks like:

╔══════╦══════╦══════════════════╗
║ cntA ║ cntB ║    percentage    ║
╠══════╬══════╬══════════════════╣
║    2 ║    1 ║ 50.0000000000000 ║
╚══════╩══════╩══════════════════╝

When you JOIN the rows, as in your example query, you're only going to see rows that match in both tables, since JOIN is really INNER JOIN.  If you use a LEFT JOIN instead, you'll get all rows from the table on the left side of the join, and only matching rows from the right side of the join.  Consider:

SELECT *
FROM dbo.a
    INNER JOIN dbo.b ON a.a_id = b.a_id;

Results:

╔══════╦════════════╦═════════════════════════╦══════╦══════╦═══════════════╗
║ a_id ║  someData  ║         rowDate         ║ b_id ║ a_id ║ someOtherData ║
╠══════╬════════════╬═════════════════════════╬══════╬══════╬═══════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:07:33.340 ║    1 ║    1 ║ blah          ║
╚══════╩════════════╩═════════════════════════╩══════╩══════╩═══════════════╝

Note, we're only seeing a single row of results; rows that exist in both tables are returned by the query.

The left join version looks like:

SELECT *
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id;

Results:

╔══════╦════════════╦═════════════════════════╦══════╦══════╦═══════════════╗
║ a_id ║  someData  ║         rowDate         ║ b_id ║ a_id ║ someOtherData ║
╠══════╬════════════╬═════════════════════════╬══════╬══════╬═══════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:07:33.340 ║ 1    ║ 1    ║ blah          ║
║    2 ║ abcdefghik ║ 2019-12-06 07:07:33.340 ║ NULL ║ NULL ║ NULL          ║
╚══════╩════════════╩═════════════════════════╩══════╩══════╩═══════════════╝

As you can see, this is return rows from both tables; where there is no row in b, we see NULL data.

You can capitalize on the left join to get you the results you want:

SELECT [Count_of_a] = COUNT(a.a_id)
    , [Count_of_b] = COUNT(b.a_id)
    , [Percentage] = CONVERT(decimal(10,2), COUNT(b.a_id)) / COUNT(a.a_id) * 100
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id;

Results:

╔════════════╦════════════╦═════════════════╗
║ Count_of_a ║ Count_of_b ║   Percentage    ║
╠════════════╬════════════╬═════════════════╣
║          2 ║          1 ║ 50.000000000000 ║
╚════════════╩════════════╩═════════════════╝

You can extend that to include a group-by on the date column.  But, first lets insert a couple of more rows in our sample tables:

INSERT INTO dbo.a (a_id, someData, rowDate)
VALUES (3, 'abcdefghil', GETDATE())
    , (4, 'abcdefghim', GETDATE());

INSERT INTO dbo.b (b_id, a_id, someOtherData)
VALUES (2, 1, 'blah')
    , (3, 4, 'blah');

SELECT *
FROM dbo.a;

SELECT *
FROM dbo.b;

Contents of both tables:

╔══════╦════════════╦═════════════════════════╗
║ a_id ║  someData  ║         rowDate         ║
╠══════╬════════════╬═════════════════════════╣
║    1 ║ abcdefghij ║ 2019-12-06 07:27:24.643 ║
║    2 ║ abcdefghik ║ 2019-12-06 07:27:24.643 ║
║    3 ║ abcdefghil ║ 2019-12-06 07:27:24.643 ║
║    4 ║ abcdefghim ║ 2019-12-06 07:27:24.643 ║
╚══════╩════════════╩═════════════════════════╝

╔══════╦══════╦═══════════════╗
║ b_id ║ a_id ║ someOtherData ║
╠══════╬══════╬═══════════════╣
║    1 ║    1 ║ blah          ║
║    2 ║    1 ║ blah          ║
║    3 ║    4 ║ blah          ║
╚══════╩══════╩═══════════════╝

The following query counts the distinct key values from each table, showing the percentage of rows that exist in table b compared to table a:

SELECT a.rowDate
    , a.someData
    , [Count_of_a] = COUNT(DISTINCT a.a_id)
    , [Count_of_b] = COUNT(DISTINCT b.b_id)
    , [Percentage] = CONVERT(decimal(10,2), COUNT(DISTINCT b.b_id)) / COUNT(DISTINCT a.a_id) * 100
FROM dbo.a
    LEFT JOIN dbo.b ON a.a_id = b.a_id
GROUP BY a.rowDate, a.someData;

Results:

╔═════════════════════════╦════════════╦════════════╦════════════╦═══════════════════╗
║         rowDate         ║  someData  ║ Count_of_a ║ Count_of_b ║    Percentage     ║
╠═════════════════════════╬════════════╬════════════╬════════════╬═══════════════════╣
║ 2019-12-06 07:27:24.643 ║ abcdefghij ║          1 ║          2 ║ 200.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghik ║          1 ║          0 ║   0.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghil ║          1 ║          0 ║   0.0000000000000 ║
║ 2019-12-06 07:27:24.643 ║ abcdefghim ║          1 ║          1 ║ 100.0000000000000 ║
╚═════════════════════════╩════════════╩════════════╩════════════╩═══════════════════╝

As you can see in the first row, there is a count of 2 rows in b, but only 1 row matching in a; hence we get a percentage value of 200%.

The DISTINCT keywords inside each COUNT(...) aggregate ensure we only count unique values for the keys in a and b.  Without that keyword, SQL Server will count duplicates from a where more rows exist in b for the given a.a_id key value.


Dynamic Count of items +/- 15% per row value I have a table with a column of building sizes named "SQFT".  I trying to find the number of buildings that are within 15% +/- of each rows "SQFT" value.
I have tried case, count with filter and window functions but none would give me the results I'm looking for, at least not in the way I was using them.

Below is what I'd like as a final result.

ID   -15%    SQFT    +15%    COUNT
1    85      100     115     2
2    94      110     127     3
3    102     120     138     3
4    111     130     150     4
5    119     140     161     5
6    128     150     173     5
7    136     160     184     5
8    145     170     196     5
9    153     180     207     5
10   162     190     219     5
11   170     200     230     7
12   179     210     242     7
13   187     220     253     7
14   196     230     265     6
15   204     240     276     5
16   213     250     288     4

This is the code so far:

WITH base AS (
   SELECT "Unique ID" as id, "Sqft Total" AS "SQFT", "Sqft Total" * (1-.15) as low, "Sqft Total" * (1+.15) as high
   FROM   "_KEY_TABLE"
   )
SELECT
    id,
    round(low::numeric,0) as "-15%",
    sqft,
    round(high::numeric,0) as "+15%",
    count(*) over(partition by "SQFT" between low and high) as "COUNT"
    -- case when "SQFT" between low and high is true then 1 end,
    -- count(*) filter (where "SQFT" between low and high) as counter_filtered,
FROM   base b
group by b."SQFT", id, low, high

Not sure exactly what you want, but I imagine it's something like this. Here we use a INNER JOIN and check the right table for the range.

-- sample data
WITH t(x) AS (SELECT generate_series(1,10))

SELECT t1.x,
  t1.x*0.85 AS lower, -- just so you can see the lower
  t1.x*1.15 AS upper, -- just so you can see the upper
  count(*)
FROM t AS t1
INNER JOIN t AS t2
  ON t2.x BETWEEN t1.x*0.85 AND t1.x*1.15  -- JOY
GROUP BY t1.x
ORDER BY t1.x;

Output,

 x  | lower | upper | count
----+-------+-------+-------
  1 |  0.85 |  1.15 |     1
  2 |  1.70 |  2.30 |     1
  3 |  2.55 |  3.45 |     1
  4 |  3.40 |  4.60 |     1
  5 |  4.25 |  5.75 |     1
  6 |  5.10 |  6.90 |     1
  7 |  5.95 |  8.05 |     3
  8 |  6.80 |  9.20 |     3
  9 |  7.65 | 10.35 |     3
 10 |  8.50 | 11.50 |     2
(10 rows)


SSIS package fails intermittently with: Unsafe assembly 'microsoft.sqlserver.integrationservices.server, version=11.0.0.0 I have an SSIS package deployed to the SSIS catalog on my Data warehouse which has run successfully for months. The package is executed by a SQL agent job.

Now it fails intermittently with the following error:

SQL agent history says says:

  Date      6/12/2015 6:00:00 a.m. Log      Job History (PBI) Step ID       1
  Server        DEV-DW Job Name     PBI Step Name       Customer DETL
  Duration      00:00:12 Sql Severity   16 Sql Message ID   27195 Operator
  Emailed    Operator Net sent   Operator Paged  Retries Attempted  0
  Message Executed as user: \. The operation failed
  because the execution timed out. [SQLSTATE 42000] (Error 27195).  The
  step failed.

In the SQL logs it says:

  Unsafe assembly 'microsoft.sqlserver.integrationservices.server,
  version=11.0.0.0, culture=neutral, publickeytoken=xxxxxxxxxxxx,
  processorarchitecture=msil' loaded into appdomain 73
  (SSISDB.dbo[runtime].103).

Why would it only fail sometimes?
Why is it failing now?
What is the root cause?
How can I fix this?

Running SQL 2012 SP2 (11.0.5343) on Win 2008 R2 Standard SP1 on VMWare.

Edit: Updated to SQL 2012 SP3 (11.0.6020).

Edit: have added more context and clarified.

Following suggestion from @TheGameisWar, I queried ssisdb

SELECT *
FROM [SSISDB].[catalog].[event_messages]
WHERE event_name = 'OnError'

Returns no error messages for that date.

This has now run successfully for ~1.5 weeks. The more I think about it I believe that the ssis error (posted in title) was a side effect, and was the result of the timeout caused by the dataload connection.

I can't prove or disprove if installing SP3 had any effect at all.

In my opinion the comment from @TheGameiswar is the best indicator pointing to root cause.


Handling a chat room in a database I have been searching all day to find a better answer to my question but those I found was useful
but do not critically analyse my question.

I am building a chat room using php, mysql, jquery and ajax.
The target group members are 3000 people who will frequently
chat every day, so I am expecting like one million messages a day.
The chat room is open to all the members, that means
every member has the same privilege to send and view
sent messages.

In this case, every member has the permission to delete messages whether sent by him or different members,
however deleted messages should not affect other users. I wouldn't also keep messages for more than two days,
meaning every sent message should be deleted after two days.

Below are the sample tables that represents the logics above

users table

|  u_id |
|  name |

messages table

| msgID            |
| msgBODY          |
| msgTime          |
| senderID--FK     |
| deleted_by_sender|

recipient table

| recID               |
| msgID--FK           |
| recipientID ---FK   |
| deleted_by_recipient|

Now, if I implement the schema above, it means that every single sent message has to do 3000 inserts into the
recipient table and one insert into the messages table. This also means that if there are 50 sent messages within 1 minute there would be more
inserts within the one minute. At the same time 3000 people are viewing the messages. Awwsh! more work load on the database within that minute.
hmm!

Is there a way to handle this, scalability and performance wise?

The following is kind of a shot in the dark in lack of clarification of the question, but as I had built it already earlier I'll post it in absense of any other answers.

How about implementing the message-deletion-per-recipient so that you only keep track of those messages that have been tagged as deleted-by-recipient? That would translate into getting rid of the recipient table as you had it and instead having a recipient table plus a cross-ref table for deleted messages:

recipient table

| recID              |
| recipientID --FK   |

deletedmessages table

| msgID --FK         |
| userID --FK   |

When fetching messages to be displayed to a given recipient, it would be enough to either join the two tables on msgID or to do something like

... WHERE NOT EXISTS (SELECT 1 FROM deletedmessages dm WHERE dm.recipientID = <current_user_ID> AND dm.msgID = m.msgID)

to filter out the deleted ones. Having deletedmessages indexed by both columns (probably mandatory to get the foreign keys references properly set up) would allow the DB to execute this very efficiently.

This way deletedmessages would only grow when a user actually deletes a message and not grow that much.

There's a SQLfiddle with the above solution.


Oracle ASM vs RAID 10 I have oracle ASM 11gR2 on oracle linux 5.8 and i want to install oracle database.
I have some disks and i want to use the redundancy strategies of oracle ASM or RAID 10 .
I do not know which one is better ? Oracle ASM redundancy or RAID 10 ?

If you can afford RAID, then go for it.. RAID or no RAID depends.. if your hardware provides redundancy then go with ASM and mirror it as you wish.
Tom Kyte has brilliant insight towards this..try visiting his site as well.


Schedule replication of SQL Server views to MySQL remote database I have a Microsoft SQL Server 2005 Database inhouse I have to publish some information on the internet and I can't (for security policy) query this database from the web server.

So I came up with the solution of replicating the information (contained in 3 views) from my SQL Server to a MySQL server on the web server.

I have to schedule this synchronization (one way SQL Server always overwrites MySQL) every night. Ideally SQL Server should push updates into MySQL server (so security ninjas are satisfied).

I read some article about but I have no clue where to begin.

I'm familiar with PHP, Python. Preferably I'd like to realize this with the internal schedule of SQL Server and a stored procedure or a maintenance task

Thank you in advance

You'll want to look at SQL Server Replication.  If you just want a one time nightly feed then you'll probably want Snapshot Replication.  If you want a more real time feed then you'll want to look at Transactional Replication.

Now replication doesn't support replicating the views, so you'll need to replicate the base tables, removing any columns which aren't needed by the website then create the views on the MySQL database.


Optimizing multi-criteria sort query I want to efficiently sort a table in PostgreSQL based off of two criteria. Here is my table structure:
CREATE TABLE shapes
(
    shape_id character varying,
    shape_pt_lat double precision,
    shape_pt_lon double precision,
    shape_pt_sequence integer
)

And there is an index on shape_id and shape_pt_sequence:
CREATE INDEX shapes_idx ON shapes (shape_id, shape_pt_sequence)

Here is the query I tried at first:
SELECT shape_pt_lat, shape_pt_lon
FROM shapes
ORDER BY shape_id, shape_pt_sequence

However, then I broke it up into two queries, because I thought that would prevent it from being sorted all at once. The first query gets the distinct shapes and then my program will make a second query for each shape_id to sort just the shape records from a single shape_id:
SELECT DISTINCT shape_id FROM shapes

then
SELECT shape_pt_lat, shape_pt_lon
FROM shapes
WHERE shape_id = ?
ORDER BY shape_pt_sequence

It turns out for smaller datasets (shape table records ~ 750k), this does cut the calculation time in half, but for larger datasets (shape table records ~ 4m) the performance was actually slower. I'm guessing maybe it was because for the larger dataset all the extra individual SQL requests may have slowed things down.
Here is the results of EXPLAIN on some queries:
Query: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shapes ORDER BY shape_id, shape_pt_sequence
Result:
Index Scan using ielz_tahfnniqhzylvuhipcjjnt_shapes_idx on shapes  (cost=0.42..29796.64 rows=473805 width=34) (actual time=0.039..214.900 rows=473805 loops=1)"
  Buffers: shared hit=31022
Planning Time: 0.361 ms
Execution Time: 259.751 ms

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shapes WHERE shape_id = '117112' ORDER BY shape_pt_sequence
Result:
Sort  (cost=534.92..535.31 rows=156 width=34) (actual time=0.163..0.173 rows=134 loops=1)
  Sort Key: shape_pt_sequence
  Sort Method: quicksort  Memory: 35kB
  Buffers: shared hit=5
  ->  Bitmap Heap Scan on shapes  (cost=5.63..529.23 rows=156 width=34) (actual time=0.062..0.094 rows=134 loops=1)
        Recheck Cond: ((shape_id)::text = '117112'::text)
        Heap Blocks: exact=2
        Buffers: shared hit=5
        ->  Bitmap Index Scan on ielz_tahfnniqhzylvuhipcjjnt_shapes_idx  (cost=0.00..5.59 rows=156 width=0) (actual time=0.050..0.050 rows=134 loops=1)
              Index Cond: ((shape_id)::text = '117112'::text)
              Buffers: shared hit=3
Planning Time: 0.098 ms
Execution Time: 0.216 ms

Anyways, I'm wondering if I'm missing something here and if there is a better way to help instruct PostgreSQL to sort this more efficiently in a single query for example.

If I divide the time for query planning and execution by the number of rows returned, I find that the "big" query took 0.00054 milliseconds per result row, while the "small" one took 0.00234 milliseconds, more than four times as long.
If you add to that the (unknown) time it took to get the shape_ids in the first place, I cannot see how splitting up the query can lead to a performance advantage.
Besides, you later have to combine all the small result sets and sort them by shape_pt_sequence, which will also take some time.
The "big" query doesn't sort at all, it uses an index scan, and that is probably the most efficient way to go about it.


ghost cleanup, delete and reindex I know when you delete data, that data is only "marked" as "deleted' for faster rollback.  The only way to really delete this data is to run something that reads the datapage, such as a table scan or to wait for the "ghost cleanup task".

I've deleted a LOT of data and I can see my database has a lot of free space, which is great. I'm not going to shrink the database since I don't need space right now; this question is just about how the delete process works.

Let's say, after this delete, I could run a script that shows me 20 indexes that need to be rebuilt, and some more to reorganize.  Well, inside them I'm sure there's a lot of ghost data.  If I run a ALTER INDEX ... REBUILD ( organize or rebuild ) will the table consume less space in the database?   I mean, can I gain space by running a reindex operation after a delete?  Or when data is marked as ghost data, is that data already not consuming space in the table?

If I run a REINDEX ( organize or rebuild ) is my database going to be
  smaller?

No, after reindex or rebuild database size may only increase. This is because when you do a rebuild(offline), a new copy of index will be built, and only then the old index will be dropped. This means that at a certain moment your db will accommodate 2 copies of the same index.

I.e., if your database is 1Tb with 10Gb free, and inside this db you have an index of 500Gb, after offline index rebuild the size of your database will be nearly 1,5Tb with 500Gb free.

This is without log growth. Log file will grow in any case, it can grow less if you rebuild offline and database recovery model is simple or bulk logged. The log will grow proportionally of your index size if you rebuild it offline in full recovery model (because every index page will be fully logged). Finally, if you rebuild online or reorganize, the log will grow even more because these operations are fully logged. (Note: if index is not fragmented, reorg will do nothing so the log will not grow).

Rebuilt index will not preserve ghost rows, but this does not mean that database size decrease.

This miens that unused space of your database will increase. But these free pages will still reserved to database.

The only mode to decrease database size is to shrink it. I still did not understand if you want to shrink or not. Shrinking means to release free database pages to OS. It is resource intensive process that is not recommended unless you really need to release some space to OS.


What are the risks of the parameter PRESERVE_COMMIT_ORDER of Oracle's DBMS_LOGSTDBY? We have the following setting, with Oracle 10g:

One primary database
One logical standby database with setup with data guard. The logical standby is only used for generating reports and alike.

Currently the parameter PRESERVE_COMMIT_ORDER of DBMS_LOGSTDBY is set to true. In order to enhance the speed of data replication, we are thinking of setting it to false. But what are the risks, if any, of doing so? (Oracle says: "See the Usage Notes for details and recommendations." ... but I couldn't find these recommendations.)

I found the following white paper: SQL Apply Best Practices: Oracle Data Guard 11g Release 1 that brings a lot of enlightement.
On page 12 is says

If you are using the SQL Apply database only for disaster-recovery purposes or if the reporting application using the standby database can accommodate transactions being applied out of sequence, then set the PRESERVE_COMMIT_ORDER parameter to FALSE. When SQL Apply is running in this model, transactions that are unrelated to each other may be applied out of sequence.

there are some examples that illustrates the effects (logical and performance related) of setting this parameter to false.

Most reporting application are reporting about data that was processed some time ago (yesterday, the last month) it seems to be no problem to et this parameter to true

The most performant way to run a standby is to run it as a physical standby with the limitation that the database is a read only 1:1 copy of the primary database.


SSIS Condition that include two connections/data bases In a SSIS package , I need some sort of condition that include two connections.

The simplified problem is:
A simple CSV import to SQL Server, DB1, table 1( col1, col2 ).
I need to populate table 1 > col2 = '1'   if col1     is found in    another database , DB2 > table 2 ;      db2 is specified as a different connection in package.

and table 1 > col2 = '2'   if col1 is not found in the lookup database.

any guidance is appreciated,
ta

Put the CSV import in one data flow, then in a dependent data flow use an OLE DB Source that contains the results you need as the output of a SQL query.
Feed that into an OLEDB destination.

Your query would look similar to:

SELECT t1.col1,
CASE WHEN EXISTS (SELECT null FROM db2.dbo.table2 t2 WHERE t2.col1 = t1.col1) THEN '2'
ELSE '1' END as [col2]
FROM db1.dbo.table1 t1


Full-text Search XML Properties I'm trying to determine whether I can retrieve a property (attribute) value from an xml blob stored in a FILESTREAM-enabled column using Full-text search.  Here is a sample where message is a varbinary(max) field that stores xml messages:

SELECT message FROM AuditTable
WHERE CONTAINS ( PROPERTY ( message, 'AccountNo' ), '123456');

On the MSDN page Search Document Properties with Search Property Lists, the summary at the top of the page includes this statement: "On the other hand, the XML IFilter does not emit properties."

I'm confused because it appears that this should work for an XML document. I'm starting to prep an environment to test, but I wanted to ask first just in case someone already knew.

If you have say a Word or Excel document, then these have documents well-known properties such as Author, Title, Keywords etc which you can associate with a search property list and then a full-text index.  Properties available depending on their IFilter.  You can use the PROPERTY keyword to query these properties.

However with an .xml file, it's unlikely the document has these custom properties, and what you really want to do is search the xml content.  AccountNo sounds like just an attribute in the xml rather than a document property.  You can search your xml using ordinary full-text search methods, however xml markup and attribute names are ignored in full-text indexes.  You can query attribute and element content.  You could also cast the file content to xml and use the built-in xml methods eg .query, .nodes, .value and .exist.  Work through this demo and see if it makes sense:

USE master
GO

IF EXISTS ( SELECT * FROM sys.databases WHERE name = 'filestreamDemo' )
BEGIN
    ALTER DATABASE filestreamDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE filestreamDemo
END
GO

CREATE DATABASE filestreamDemo
ON PRIMARY (name = filestreamDemo_File, filename = N'c:\temp\filestreamDemo.mdf' ),
FILEGROUP FG_filestreamDemo CONTAINS FILESTREAM ( NAME = filestreamDemo_FS_File, FILENAME = N'c:\temp\filestreamDemo_FS' )
WITH FILESTREAM (
    non_transacted_access = FULL,
    directory_name = N'filestreamDemo' )
GO

USE filestreamDemo
GO

-- DROP TABLE dbo.AuditTable
IF OBJECT_ID('dbo.AuditTable') IS NULL
CREATE TABLE dbo.AuditTable
(
    rowId           INT IDENTITY CONSTRAINT PK_AuditTable PRIMARY KEY,
    Id              UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE,
    [message]       VARBINARY(MAX) FILESTREAM NULL,
    fileExt         VARCHAR(5) NOT NULL
)
GO

IF NOT EXISTS ( SELECT * FROM sys.fulltext_catalogs WHERE name = N'ft' )
CREATE FULLTEXT CATALOG ft AS DEFAULT
GO

CREATE FULLTEXT INDEX ON dbo.AuditTable
    ( [message] TYPE COLUMN fileExt )
    KEY INDEX PK_AuditTable ON ft
    WITH ( CHANGE_TRACKING = MANUAL, STOPLIST = SYSTEM )
GO

INSERT INTO dbo.AuditTable ( Id, [message], fileExt )
SELECT
    NEWID(),
    CAST( '<root AccountNo="123456"/>' AS VARBINARY(MAX) ),
    'xml'

UNION ALL

SELECT
    NEWID(),
    CAST( '<root><child AccountNo="123456"/>elementValue</root>' AS VARBINARY(MAX) ),
    'xml'

UNION ALL

SELECT
    NEWID(),
    CAST( '<dummy/>' AS VARBINARY(MAX) ),
    'xml'
GO

ALTER FULLTEXT INDEX ON dbo.AuditTable START FULL POPULATION;
GO

SELECT 'before' ft, * FROM sys.fulltext_indexes
GO

DECLARE @i INT = 0

WHILE EXISTS ( SELECT * FROM sys.fulltext_indexes WHERE has_crawl_completed = 0 )
BEGIN
    WAITFOR DELAY '00:00:05'

    SET @i += 1
    IF @i > 60 BEGIN THROW 50001, 'Too many loops!', 1 BREAK END

END
GO

SELECT 'after' ft, * FROM sys.fulltext_indexes
GO

-- Have a look in the full-text index
SELECT *
FROM sys.dm_fts_index_keywords_by_document(DB_ID(), OBJECT_ID('dbo.AuditTable'))
ORDER BY document_id

-- Search using full-text; WON'T WORK
SELECT [message], CAST( [message] AS VARCHAR(MAX) )
FROM AuditTable
WHERE CONTAINS ( [message], 'AccountNo');

-- will work
SELECT [message], CAST( [message] AS VARCHAR(MAX) )
FROM AuditTable
WHERE CONTAINS ( [message], '123456');

-- will work: numbers are prefixed with nn in full-text indexes
SELECT [message], CAST( [message] AS VARCHAR(MAX) )
FROM AuditTable
WHERE CONTAINS ( [message], 'nn123456');

-- Cast to xml and search using normal xml methods
SELECT *
FROM ( SELECT rowId, ID, CAST( [message] AS XML ) [message] FROM dbo.AuditTable ) a
WHERE a.message.exist('//@AccountNo[.="123456"]') = 1
GO

I also did a test and could not get an xml doc to emit any properties, as suggested by the documentation.  I could however get this to work with a standard Word document.


Why this statement invalid: `VALUES ( CURRENT PACKAGESET )`? I'm on db2 luw 10.5.

db2 special registers can be set with:

SET CURRENT ...

and retrieved with:

VALUES CURRENT ...

with the exception of CURRENT PACKAGESET. For some reason the following statement fails:

VALUES CURRENT PACKAGESET
SQL0104N  An unexpected token "PACKAGESET" was found following "VALUES CURRENT
".  Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

Why so?

I dont think CURRENT PACKAGESET is a special register in LUW 10.5. The special registers can be found at:

https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008404.html?cp=SSEPGG_10.5.0%2F2-12-2-5

SET CURRENT PACKAGESET is a statement that can only be embedded in an application program:

https://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0001005.html?cp=SSEPGG_10.5.0%2F2-12-7-219&lang=en


Does 2 in the DBCC LOGINFO column "Status" mean it cannot be truncated? When I run the command DBCC LOGINFO, a few of the Status rows contain 2.

Now I want to check why my transaction log cannot be truncated. How do I see this? Does the 2 mean in the Status that they are not reusable or an action is waiting (backup, restore)? Is it saying that it cannot be truncated?

According the the information in this MSDN forums answer by Sankar Reddy (and other posts when searching for DBCC loginfo status 2):

  Status = 2 means that VLF can't be reused (overwritten) at this time
  and it doesn't necessarily mean that VLF is still active and writing
  transactions to that VLF. It means that the VLF is waiting for
  backup/REPL/Mirroring etc...

Database Checkpoints (SQL Server)

  CHECKPOINT only truncates the transaction log (marks the VLF for
  reuse) only in simple recovery model. In Full recovery, you have to
  take log backup.

Understanding Logging and Recovery in SQL Server by Paul S. Randal


Large memory grant requests I have one query with multiple execution plans, the memory granted to one plan is huge comparing to the 2nd one

based on this article https://blogs.msdn.microsoft.com/sql_server_team/addressing-large-memory-grant-requests-from-optimized-nested-loops/

  The issue occurs when the outer table of the Nested Loop join has a
  predicate that filters the result to a small input, but the batch sort
  appears to be using an estimate for cardinality that is equivalent to
  the entire outer table. This can result in a perceived excessive
  memory grant which in a very concurrent server can have several
  side-effects, like OOM conditions, memory pressure for plan cache
  eviction, or unexpected RESOURCE_SEMAPHORE waits. We have seen how a
  single query that match this pattern can actually get several GB’s of
  granted memory on high-end machines (1TB+ RAM).

  One option until now would be to disable this feature globally using
  Trace Flag 2340, as described in KB 2801413. However, in SQL Server
  2016 RC0 we have changed the behavior to maintain the advantage of the
  optimization, but now the max grant limit is based on the available
  memory grant space. This improvement also translates into better
  scalability, in the sense more queries can be executed with a smaller
  memory footprint. We are looking at back porting this behavior to an
  upcoming have ported this behavior to SQL Server 2014 Service Pack 2,
  and as usual deliver added value to in-market versions.

This is exactly what I am seeing however I am using SQL Server 2016 Enterprise.

These are the execution plans

https://www.brentozar.com/pastetheplan/?id=SJ0mYAy0b

https://www.brentozar.com/pastetheplan/?id=BJzutC1R-

My questions are

What is the reason for 2 execution plans?
The optimizer is using the top execution plan, I forced it to use the lower plan but after sometime it goes again to the top one, any reason for that?
How to fix this problem? This issue causes the application to crash (there were many RESOURCE_SEMAPHORE waits and the application become unresponsive)? Should I use the hint : DISABLE_OPTIMIZED_NESTED_LOOP or  Trace Flag 2340?

NOTE : I checked the XML and both plans have  NestedLoops Optimized="false"

1. What is the reason for 2 execution plans?

From the sys.query_context_settings (Transact-SQL) documentation:

    There are a number of context settings available in SQL Server that influence the query semantics (defining the correct result of the query). The same query text compiled under different settings may produce different results (depending on the underlying data).

It seems most likely the same query text was submitted from sessions with different context settings. See also Slow in the Application, Fast in SSMS? by Erland Sommarskog.

  2. The optimizer is using the top execution plan, I forced it to use the lower plan but after sometime it goes again to the top one, any reason for that?

Plans will still recompile from time to time based on the normal rules around changes to e.g. underlying objects, statistics, schema. Forcing a query store plan helps ensure the plan used will be at least fundamentally similar to the source plan, as sys.query_store_plan (Transact-SQL) notes:

    Forcing mechanism does not guarantee that exactly this plan will be used for the query referenced by query_id. Plan forcing causes query to be compiled again and typically produces exactly the same or similar plan to the plan referenced by plan_id.

Each (re-)compilation may produce plans with different estimates (including memory grant) depending on any values in the query that are visible to the optimizer at the time of compilation. A specific memory grant cannot be forced using query store or plan guides.

  3. How to fix this problem?

As Dan Guzman's answer suggests, if the plan is very sensitive to specific parameter values, compiling a fresh plan on every execution using an OPTION (RECOMPILE) hint may be the best practical solution.

That said, there are very few tools available to affect update-side execution plans, which can be complex where things like cascading foreign keys and indexed views are involved. If all else fails, you may find that an execution plan without sorts (to order rows in index key order) produces the best result. There is no documented way that I'm aware of to generate plans without these sorts, but you could try forcing the plan obtained with undocumented trace flag 8795 on.

You might also try a suitable MAX_GRANT_PERCENT query hint or simply install (or make available to SQL Server) enough memory to handle your workload.


DB2 error - SQL Statement too long or complex Getting this error in a DB2 production system:

ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0101 - SQL statement too long or complex.

The statement has a large number of OR statements in the WHERE clause, as in

...<sql statements and joins>
WHERE (a=x1 and b=y1)
  OR (a=x2 and b=y2)
  OR (a=x3 and b=y3)
... <4000 times>

I am thinking that I need to scale back the batch size to something less that 4000 OR statements.  Any suggestions?

This should probably be a comment rather than an answer since I'm not sure if my reasoning is correct, but then I wouldn't be able to have any formatting.

Could you create a temporary table and JOIN instead?

...<sql statements and joins>
JOIN tmp_table ON my_table.a = tmp_table AND my_table.b = tmp_table.b

Edit: if the values you're checking against don't change much, it might even make sense to persist them in a table to increase performance and facilitate querying.


SSIS Package when executed via agent job or manually on SQL Server does nothing, but shows it completed successfully. What gives? I recently upgraded a SQL Server from 2008 R2 to 2016 and a SSIS package on the SQL server (in msdb) contained ActiveX Script Tasks. So I exported the package and got the code out of the ActiveX Script Tasks and updated it in regular Script Tasks, pretty simple package. I imported the SSIS package back onto SQL Server (in msdb) and thought all was well. As the job showed it executed and completed successfully. I am later notified that nothing happened that the package should of done. I even executed the package manually in Integration Services and yet still nothing other than it running in about .15 seconds (which this job should take about 5 seconds) and showing it completed successfully. The package actually does its what it is supposed to do when ran from a VS 2015 (with SSDT) project, but does nothing when it is on SQL Server. Has anyone experienced this!?! I recall something about 32bit runtime vs 64bit runtime but I wanted to check here first. Any solutions? I've even started from scratch with a NEW project too.

I was completely unaware that Deployment Target Version defaults to SQL Server 2017 in Visual Studio 2015 SSDT ... It ran fine once I had set it to SQL 2016... I feel like a fool.

[https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s][1]


SQL query does not recognise derived table So I've made a simple SQL query:

SELECT companyname
FROM works w1, (
                SELECT companyname,sum(salary) AS sumsal
                FROM works
                GROUP BY companyname
               ) w2
WHERE
w1.companyname=w2.companyname

it works fine so far but then I add the following line in the where clause:

AND w2.sumsal=(SELECT MIN(w2.sumsal)
               FROM w2);

I get:

ERROR 1146 (42S02): Table 'companydb.w2' doesn't exist

I'm using MySQL 8.0.

Before answering the question, you are using WHERE instead of JOIN for joining the tables, and join the tables using the company name instead of the row ID / primary key. It's better use JOIN with row ID when joining tables (if your are using the company name as the primary key you really have to add to add a row ID and use it instead as the key).

Regarding your question, the problem is that the subquery w2 can't be used in the right part of the SELECT because is out of scope, but you can use a Common Table Expression to create a "temporary view" that can be used in any part of the query. Then you can use two methods for getting the result:

Using MIN():

with sums as (select companyname, sum(salary) AS sumsal
  from works
  group by companyname
)
select *
from sums
where sumsal=(select min(sumsal) from sums)

Using EXISTS:

with sums as (select companyname, sum(salary) AS sumsal
  from works
  group by companyname
)
select *
from sums
where not exists (select * from sums s2 where s2.sumsal<sums.sumsal)

I have created this db<>fiddle for testing it online, there I have used JOIN with the company Id instead of the name as an example of how you can use that.


Querying XML Nodes with SQL I think I'm nearly there with this but have reached an impasse!

I've a relatively straightforward XML structure that I need to parse out as a SQL resultset but can't seem to get it to work.

Here is the code:

DECLARE @xml XML;
SET @xml = '
<values>
    <Nationality>
        <item>
            <key>UK</key>
            <value>def</value>
        </item>
        <item>
            <key>IE</key>
            <value>def</value>
        </item>
    </Nationality>
    <Currency>
        <item>
            <key>GBP</key>
            <value>123</value>
        </item>
        <item>
            <key>EUR</key>
            <value>345</value>
        </item>
    </Currency>
</values>';

select I.X.value('(/key/text())[1]', 'int') as ItemID,
       I.X.value('(/value/text())[1]', 'int') as Store
from @XML.nodes('/values/Nationality') as O(X)
cross apply O.X.nodes('/item') as I(X);

Essentially, I want to pull out the values from Nationality into:

Key  | Value
-------------
UK   | Def
IE   | Def

I may want to do Currency later but I think if I get the above cracked then the rest will be simple enough.

Can you help please?

Base on your input data, I will do something like this:

select I.X.value('(key/text())[1]', 'varchar(50)') as ItemID,
       I.X.value('(value/text())[1]', 'varchar(50)') as Store
from @XML.nodes('values/Nationality') as O(X)
  cross apply O.X.nodes('item') as I(X);

ouptput

ItemID  Store
UK  def
IE  def


Altering Tables using Tablenames from other Table I have a database with several tables and i have a table which contains all tablenames which I want to alter.

I want to perform a query like this:

ALTER TABLE (SELECT "TABLE_NAME" FROM "RESOURCE_TABLES)  DROP COLUMN ID;

My Table RESOURCE_TABLE with Table Names:

TABLE_NAME     ID

Table1          1
Table2          2
Table3          3
  ...          ...

How can I alter all tables named in the table in the column TABLE_NAME with a loop or a query?

So i want Table1,Table2,Table3 to drop a column id without executing a single alter table query on each table because maybe the tables are 100 Tables.

You can loop using an EXECUTE statement.  I believe this post has the exact answer you're looking for, you just need to change the ALTER statement.


How to Rename Table or Field With Merge Replication I am using merge replication with SQL 2012. I am trying to rename a published table, and rename a published field.

These operations are not permitted in merge replication.

However I can think of an action plan if these things are needed. For instance renaming a field would include,

Adding the new field
Migrating the data from the old field to the new field
Wait for a while, perhaps a month
Delete the old field

The problem is after step 2. We can force a software upgrade to the clients so that data is written into the new field.

But some data may have been modified in the old field at one of the subscribers before they do the sync which makes the schema change. This would also be before they were forced to do the update.

The migration could be done more than once, but by the time you get to step 4 its hard to know which modifications have been made to the old field, and which have been made to the new field.

You could do it this way:

Add the new field.
Force a software upgrade that includes the logic to insert new data into the new field and to read the data from the new field if it is not null, otherwise to read that field from the old field. ( in case of update: you read the value from the old field then do an update to the new field )
Wait for a while, perhaps a month till you are sure that all clients have upgraded your software.
Migrate data from the old field to the new field if the new field is null.
Delete the old field.


Calculating time spent on query cache lock How can I calculate the total time my MySQL database is spending  Waiting for query cache lock    ?

I've found two specific very simple tables that adding a SQL_NO_CACHE drastically improved the SELECT queries on them, and I want to analyze other tables in a more methodological approach.

My MySQL version is 5.5.15 and the InnoDB engine is 1.1.8
Query cache size is 536870912

Relevant Variables:

Half a gigabyte is too large for the query cache.  This write-up is several years old but still very applicable:

http://www.mysqlperformanceblog.com/2007/03/23/beware-large-query_cache-sizes/

In MySQL 5.5., you can benchmark this using profiling but the real problem isn't how long any particular query waits for the lock, because the answers will generally be all across the board and largely unrelated to the query itself or the tables, because the query cache is accessed by a global mutex that every query contends for.

There is apparently supposed to be a timeout to prevent long waits, but which does not work correctly, according to Bug #68573.

Your fix would be a more reasonable query cache.

I dissected and described a little bit of the query cache's inner workings here.


Syntax error on declaring a variable inside a transaction I want to create a variable inside this transaction but I'm getting

  ERROR: syntax error at or near "DECLARE" LINE 4: DECLARE new_id INTEGER;.

The variable needs to hold the value of the id of the newly created message.
This seems to be the cleanest and race condition resistant way of doing so, but I don't know how to correctly declare the variable.

BEGIN TRANSACTION;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE new_id INTEGER;
INSERT INTO messages (author) VALUES ($author) RETURNING id INTO new_id;
-- Other statements..

COMMIT;

Try using a declare block before the main block:

DECLARE
  -- declarations go here
BEGIN
  -- commands go here
END;

If you need this as an anonymous block (not in any function, etc.) also read about DO.


Is there a rule of thumb regarding the size and number of a buffer pool instances? I know the consensus seems to be approximately 75% of ram should be dedicated to the buffer pool. But, I'm more interested in the optimal number of buffer pool instances. What are some things to consider when determining the setting?

For example, let's say I have a 75GB buffer pool. Should I just assign 75 buffer pool instances at 1GB each? 15 at 5GB each? Or, 10 at 7.5GB each?

Should I start out with a single buffer pool and increase based on whether or not I see locking?

What should the max number of instances be based on - available cores?

Thanks - Your response is greatly appreciated.

BUFFER POOL INSTANCES
The rule of thumb I usually use is based on a special program in in the Linux environment
numactl --hardware

When I run this, I get the following output
sh-4.1# numactl --hardware
available: 4 nodes (0-3)
node 0 cpus: 0 1 2 3
node 0 size: 49151 MB
node 0 free: 241 MB
node 1 cpus: 4 5 6 7
node 1 size: 32768 MB
node 1 free: 39 MB
node 2 cpus: 8 9 10 11
node 2 size: 49152 MB
node 2 free: 49 MB
node 3 cpus: 12 13 14 15
node 3 size: 32752 MB
node 3 free: 32 MB
node distances:
node   0   1   2   3
  0:  10  16  16  16
  1:  16  10  16  16
  2:  16  16  10  16
  3:  16  16  16  10

This quickly tells me how many CPUs and cores I have on my DB Server.
In general, I set the innodb_buffer_pool_instances to the number of physical CPUs or the number of cores. In your particular, I set the innodb_buffer_pool_instances to 4 or 16.
BUFFER POOL SIZE
If you make the InnoDB Buffer Pool bigger than 50% of the installed RAM, mysqld causes the OS to start swapping ... BADLY !!!! If you need a Buffer Pool that big, then tuning innodb_buffer_pool_instances becomes even more critical.
EPILOGUE
As soon as innodb_buffer_pool_instances was first introduced, I immediately experimented with it. I had a client that had 192GB DB Server, dual hexacore with a 162GB Buffer Pool. I simply set it to 2 and everything worked out just fine. Please see my old post from Feb 12, 2011 on this : How do you tune MySQL for a heavy InnoDB workload?
GIVE IT A TRY !!!


Query about MySQL Semaphores in Engine Status During a recent problem / slow down with one of our slave databases I noticed that when I ran SHOW ENGINE INNODB STATUS. I am getting the following:

----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 822986039, signal count 17045541908
--Thread 140562108442368 has waited at row0sel.c line 2930 for 0.00 seconds the semaphore:
S-lock on RW-latch at 0x7fd752e55c40 created in file buf0buf.c line 938
a writer (thread id 140562088974080) has reserved it in mode  exclusive
number of readers 0, waiters flag 1, lock_word: 0
Last time read locked in file row0sel.c line 2930
Last time write locked in file /tmp/buildd/mysql-5.5-5.5.44/storage/innobase/buf/buf0buf.c line 3168

In fact, there are about 15 instances of this.

Looking at the transactions there are apx 200 transactions listed, of which there are apx half showing as waiting in InnoDB queue, and the other half are fetching data.

All the transactions are SELECT queries, and none of them are showing any locks.

Can anyone tell me what the SEMAPHORES (above) relate to ?

Semaphores have to do with waiting for internal thread locks in the OS

You can find a nice discussion in Peter Zaitsev's SHOW INNODB STATUS walk through

The way I see it, this is related to two things

innodb_concurrency_tickets
innodb_thread_concurrency

My guess is that you set innodb_thread_concurrency to a nonzero number. This causes the InnoDB storage engine to throttle its use of internal threads. Consequently, this will cause transactions to go sit in the queue.

Way back in 2011, I wrote posts about this

Sep 12, 2011 : Possible to make MySQL use more than one core?
Aug 16, 2011 : Why do MySQL threads often show "freeing items" status when the query cache is disabled?
May 26, 2011 : About single threaded versus multithreaded databases performance

I went to Percona Live NYC in 2011. Ronald Bradford told me during a workshop to leave innodb_thread_concurrency at zero(0). Why ?

As I mentioned in the Aug 16, 2011 post:

  He plainly told me that I should never set values against innodb_thread_concurrency. Let it always be the default value, which is now zero(0). By doing so, you let the InnoDB storage decide how many innodb_concurrency_tickets to generate on its own. This is what infinite concurrency does.

If you have innodb_thread_concurrency as a nonzero value, please set it to 0 or remove it from my.cnf. Please note that MySQL 5.6 allows for 5000 tickets while MySQL 5.5 only allows 500.

If you want to change these values, you can do so dynamically with

SET GLOBAL innodb_thread_concurrency = 0;

I would recommend not setting innodb_concurrency_tickets at all.

I would also recommend reading Pythian's ONCE AGAIN ABOUT INNODB-CONCURRENCY-TICKETS