Duplicating database on the same server without data loss

a "lot" of rows are missing

Your interests would be best served if you'd investigate and come up with an explanation of why this is happening.  It's not acceptable for a tool or utility to behave mysteriously, so either the tool has a defect, or isn't intended to be used as you are using it, or there is a quirk in your database structure that is interacting with something in the utility that you're using to cause these problems.

As a DBA, I like to be as "operationally close" to the server as possible, without tools making decisions and being "helpful."  I use a graphical query browser for only three things:  Running queries where I want to scroll through the results, editing stored programs, and adding comments to large numbers of undocumented table columns.  Everything else, I do by hand because I like to understand exactly what the server is being asked to do, and I like the server to understand me, as well.  So, here's how I do what I think you are describing.

I assume you're talking about cloning a schema on a single server, to a different schema on the same server.

To state it procedurally, "for each table (or other object) in `prod_db`, create an identically named object in `dev_db` including all of the indexes, foreign key constraints referencing the appropriate table in the new schema, and, optionally, copy all the data."

Here's how I do that.

$ mysqldump [options*] --databases prod_db | perl -pe 's/`prod_db`/`dev_db`/g' > dump.sql

This generates a human-readable dumpfile, with every instance of `prod_db` (including the backticks) replaced with `dev_db`.  Perl handles that filtering as the dumpfile passes through the | pipeline.  Then I review the file's contents for sanity and pipe it right back in the mysql:

$ mysql < dump.sql

Done.

Now for the details.

If I take the first few lines of the file generated by this command:

$ mysqldump [options] --databases sakila

I see this near the beginning of the file:

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `sakila` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `sakila`;

Now this:

$ mysqldump --databases sakila | perl -pe 's/`sakila`/`dolphin`/g'

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `dolphin` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;
USE `dolphin`;

Magic.  This process carries one major caveat:  you can't have a table with the same name as the original database, because the table name `sakila` will be changed to `dolphin` just like the database name was changed.

*options for mysqldump are very important here, because if you make a mistake, you run the risk of trashing your live database.  This option is particularly useful.

--skip-add-drop-table

If you do a normal mysqldump, before each CREATE TABLE in the resulting file, there's a DROP TABLE IF EXISTS statement.

DROP TABLE IF EXISTS `actor`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `actor` ( ...

When you add the --skip-add-drop-table to mysqldump it does write the DROP TABLE IF EXISTS statement into the dumpfile, so there is no danger of dropping the wrong table.  The worst case is that it will try to create a table that already exists, and if that happens, the script will halt.  Then you can figure out what the problem was.

You should also add the --verbose option to mysqldump because it shows you progress as the dump is proceeding.

--events --triggers --routines

If you have MySQL Event Scheduler events, or triggers, or stored functions/procedures ("routines") these flags will ensure that mysqldump copies those, as well.  You need to review them, however, to be sure that any references to the schema are correct, because the schema name may not be properly backticked in these.  That shouldn't be a major problem, because you don't need to reference the schema when defining these -- they run in the context of the schema in which they are defined.

--no-data

Use this option if you only want to clone the structures, not the rows in the tables.  You can also use this option for a dry run to examine the resulting "dump.sql" file for sanity.  Remember, these files contain nothing more or less than the SQL statements needed to recreate a database, and are therefore, human-readable.

--single-transaction

You want to use this when the tables you're dumping are InnoDB because it won't lock the tables or block other operations, but it will ensure that the data you get is consistent from table to table, because it issues START TRANSACTION WITH CONSISTENT SNAPSHOT against the original server before extracting the dump file.

What's Perl doing here?

perl -pe 's/`prod_db`/`dev_db`/g'

What we're essentially saying is perl, -e execute the following mini-script (in quotes) and -p pretend that I've wrapped this instruction in a loop that says "read from standard in", "apply changes to data", "print resulting data to standard out".  s/ substitute, when you see `prod_db` replace / it with `dev_db` and / do this g globally (instead of only replacing the first occurrence on any given line, replace every occurrence on every given line).  Perl does this replacement very efficiently and is on virtually every system, and freely available for systems where it isn't installed, including Windows.  You could do the same thing with sed, and the command would be similar, though not identical.

The steps above are essentially how I perform this operation when my developers need an entire schema cloned to the same server but with a different schema (database) name.


Why do I get an incorrect error "ORA-01775: looping chain of synonyms", when the base table does not existing?

This happens when you have a "dangling" synonym, because it points to itself.

For example:

SQL> create table blah ( a number );

Table created.

SQL> create public synonym blah for blah;

Synonym created.

SQL> drop table blah;

Table dropped.

SQL> select * from blah;
select * from blah
              *
ERROR at line 1:
ORA-01775: looping chain of synonyms

SQL>


Is it possible to create an empty or dummy index in postgres?

upgrade script expects an index to exist only so that it can drop that index [...] Instead of taking the time to recreate the index is it possible to create a "dummy" or empty index with the same name so that the upgrade script can run?

Just delete that line from the update script. Or even better, use the IF EXISTS clause so it throws a notice not an error.

Command:     DROP INDEX
Description: remove an index
Syntax:
DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ]

But yes, the DROP INDEX doesn't even know if the index is on a specified table if you're dropping by index name and not by table(column) location.

Here is an example,

CREATE TABLE foo ( x int );
CREATE INDEX ON foo(x);
CREATE TABLE bar ( LIKE foo );

\d foo;
      Table "public.foo"
 Column |  Type   | Modifiers
--------+---------+-----------
 x      | integer |
Indexes:
    "foo_x_idx" btree (x)

--
-- HERE WE DROP FOR THE FIRST TIME
--
DROP INDEX foo_x_idx ;
DROP INDEX

--
-- RECREATE
--
CREATE INDEX foo_x_idx ON bar(x);

--
-- DROP AGAIN
--
DROP INDEX foo_x_idx;
DROP INDEX


only last row is inserting in table when having "instead of insert" trigger on that table

The issue is your comparison is working against a single record but the INSERTED pseudo table contains multiple rows (all of the rows being inserted in the transaction). You can get around this using something like the following:

CREATE TRIGGER inTrigger ON Data
INSTEAD OF INSERT
AS
BEGIN

  IF EXISTS(
    SELECT *
    FROM INSERTED i
    INNER JOIN Faculty f ON f.Id = i.Fid
    WHERE f.fopd > i.Date
  )
  BEGIN
    RAISERROR('Cannot insert ',16,1);
  END

  INSERT INTO test (id, fid, date)
  SELECT i.*
  FROM INSERTED i
  INNER JOIN Faculty f ON f.Id = i.Fid
  WHERE f.fopd <= i.Date

END

This trigger identifies any records in the INSERTED table that match the failing criteria and if encountered it raises an error. It then proceeds with the insertion of the records that do not violate the condition. You could easily amend this to rollback the whole transaction if that is preferred.


Subtract multiple quantities in table2 from table1

Here is an example of how that could be done

--Demo setup
Declare @Table1 table (P_ID int, [name] varchar(1), quantity int)
INSERT INTO @Table1
    (P_ID, [name], quantity)
VALUES
    (1, 'a', 100),
    (2, 'b', 100),
    (3, 'c', 100),
    (4, 'd', 100),
    (5, 'e', 100)
;

Declare @Table2 TABLE
    (P_ID int, [name] varchar(8), quantity int)
;

INSERT INTO @Table2
    (P_ID, [name], quantity)
VALUES
    (1, 'carrot', 3),
    (2, 'eggplant', 12),
    (5, 'peppers', 7)
;

--The solution
UPDATE t1
SET t1.quantity = t1.quantity - t2.quantity
FROM @Table1 t1
JOIN @Table2 t2
    ON t2.P_id = t1.P_ID

SELECT *
FROM @Table1

| P_ID | name | quantity |
|------|------|----------|
| 1    | a    | 97       |
| 2    | b    | 88       |
| 3    | c    | 100      |
| 4    | d    | 100      |
| 5    | e    | 93       |

Update based on OP's comment about grouping table2

Declare @Table1 table (P_ID int, [name] varchar(1), quantity int)
INSERT INTO @Table1
    (P_ID, [name], quantity)
VALUES
    (1, 'a', 100),
    (2, 'b', 100),
    (3, 'c', 100),
    (4, 'd', 100),
    (5, 'e', 100)
;
Declare @Table2 table
    ([P_id] int, [name] varchar(20), [quantity] int)
;

INSERT INTO @Table2
    ([P_ID], [name], [quantity])
VALUES
    (1, 'carrot', 3),
    (2, 'eggplant', 12),
    (5, 'peppers', 7),
    (2, 'eggplant', 8),
    (1, 'carrot', 25),
    (5, 'peppers', 13)
;

;WITH Table2Grouped
AS (
    SELECT P_ID
        ,SUM(cast(quantity AS INT)) AS quantity
    FROM @Table2
    GROUP BY P_ID
    )
UPDATE t1
SET t1.quantity = t1.quantity - t2.quantity
FROM @Table1 t1
JOIN Table2Grouped t2
    ON t2.P_id = t1.P_ID

SELECT *
FROM @Table1


Postgres handle case to continue Insert other row if some row doesn't exists

Build a SELECT expression that does what you want, and then use INSERT INTO ... SELECT ... to insert it into the table.
To get a SELECT that does what you want, wrap the VALUES expression in parentheses, add an alias with field names for your sub-selects, then add a WHERE for the project and user IDs being NOT NULL. The final query becomes:
insert into projects_users select * from
(VALUES (
            (Select id from users where username='user_id1'),
            (Select id from projects where name='project_id2'),
            True
        ),(
            (Select id from users where username='user_id3'),
            (Select id from projects where name='project_id4'),
            True
        )
) as r(user_id, project_id, is_admin)
WHERE user_id is not null and project_id is not null;


Can one of Windows failover cluster nodes run on Linux?

As SQL Server is available for Linux can I configure Clustering on Linux nodes?

You can configure Corosync and pacemaker for Linux. Good luck.

  I mean mixed with Windows - some nodes are on Linux, others on Windows in the same cluster?

In the same cluster, no. Someone will argue with me and say, "Well, Sean, you can do read-scale availability groups like this!" and that may be true, however it isn't a cluster.

The only way to do this would be to have two different clusters, spanned by a distributed availability group. If you want to make life complicated then this would be the setup to do it.


A good place to start would be looking at the SQL CAT team's whitepaper on data loading performance for SQL Server 2008.

The procedural loops in SQL Server are very slow, which would be a large part of the difference in timings.  It would be best to generate files to be bulk loaded in the databases to measure the performance differences.  Also the size of the database will very quickly require the use of multiple database files to spread the load which I'm not sure that FoxPro has any builtin support.


How to add a new column with auto increment ,increment factor, min-value and max value in psql?

Here, you can use sequence to increase app_id value.
Please noted if the sequence reach to its maxvalue, you will face this error ERROR:  nextval: reached maximum value of sequence .

For example

create table t1 (a int, b int);

create sequence seq_t1
increment by 4
minvalue 100
maxvalue 2000;

alter table t1 add column app_id int default nextval('seq_t1');
insert into t1(a) select generate_series(1, 100) ;

  a  | b | app_id
-----+---+--------
   1 |   |    100
   2 |   |    104
   3 |   |    108
   4 |   |    112
   5 |   |    116
   6 |   |    120
   7 |   |    124


Update value equal to the count appearance on the last X records

I find the construction rather error prune, what if a row is deleted, or value is updated, should last_100_repetitions also be updated then? That said, a trigger like:

delimiter //
create trigger trg
before insert on t
for each row
    set new.last_100_repetitions = (select count(1)
                                    from (
                                        select value
                                        from t
                                        order by num desc
                                        limit 100
                                    ) as x
                                    where value = new.value )
//
delimiter ;

may work. If you are only interested in the last last_100_repetitions for each value you may concider moving this attribute to a separate table.

Test:

MariaDB [test3]> insert into t (value) values ('A'),('A'),('B'),('C');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test3]> select * from t;
+----+-------+----------------------+
| num| value | last_100_repetitions |
+----+-------+----------------------+
| 12 | A     |                    0 |
| 13 | A     |                    1 |
| 14 | B     |                    0 |
| 15 | C     |                    0 |
+----+-------+----------------------+
4 rows in set (0.01 sec)

MariaDB [test3]> insert into t (value) values ('A'),('A'),('B'),('C');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test3]> select * from t;
+----+-------+----------------------+
| num| value | last_100_repetitions |
+----+-------+----------------------+
| 12 | A     |                    0 |
| 13 | A     |                    1 |
| 14 | B     |                    0 |
| 15 | C     |                    0 |
| 16 | A     |                    2 |
| 17 | A     |                    3 |
| 18 | B     |                    1 |
| 19 | C     |                    1 |
+----+-------+----------------------+
8 rows in set (0.00 sec)


How to prevent #Error values derived from Nulls in an outer join

You need to apply the function (Nz) that replaces the null with another value before the week function call. Use:

week( Nz(Tab2.y, Value_for_y) , Nz(Tab2.w, Value_for_w) )

You probably also need tests against the special Value_for_y or Value_for_w inside week().


g2server wrote in answer to the question 64 bit Microsoft SQL Server Data Tools on Stack Overflow:

As the others have said, there is only a 32 bit version.
The important thing is, if you’re running an x64-based SQL instance (64-bit), make sure to select “New Instance” on the Installation Type page, and NOT “Add features to an existing instance”. Not doing so will cause it to error out.

Community Wiki answer to document the solution on Stack Overflow found by Dave


How to get non-overlapping distinct intervals from a PostgreSQL table?

To solve this problem I did the following:
"Easy" explanation:
For this part I slightly added to the table definition provided by the OP. I'm firmly of the belief that DDL should be used to the maximum extent possible to "guide" the whole database programming process and could be much more powerful - an example of this would be SQL in CHECK constraints - so far only provided by Firebird (example here) and H2 (see reference here).
However, that's all very well and good, but we have to deal with PostgreSQL's 9.6 capabilities - the OP's version. My adjusted DDL for the "simple" explanation (see the entire fiddle here):
CREATE TABLE sessions
(
        id serial NOT NULL PRIMARY KEY,
        ctn INT NOT NULL,
        f_day DATE NOT NULL,
        f_time TIME(0) NOT NULL,
        l_time TIME(0) NOT  NULL,
        CONSTRAINT ft_less_than_lt_ck CHECK (f_time < l_time),
        CONSTRAINT ctn_f_day_f_time_uq UNIQUE (ctn, f_day, f_time),
        CONSTRAINT ctn_f_day_l_time_uq UNIQUE (ctn, f_day, l_time)
        -- could put in a DISTINCT somewhere if you don't have these constraints
        -- maybe has TIME(2) - but see complex solution
);

Indexes:
CREATE INDEX ctn_ix ON sessions USING BTREE (ctn ASC);
CREATE INDEX f_day_ix ON sessions USING BTREE (f_day ASC);
CREATE INDEX f_time_ix ON sessions USING BTREE (f_time ASC);

Just a point to note: do not use SQL keywords as table or column names - day is such a keyword! It can be confusing for debugging &c - it's simply not good practice. I've changed your original fieldname of day to f_day - note all lower and python case! Whatever you do, have a standard method of naming variables and stick to it - there are many coding standards documents out there.
The change to 'f_day' has no effect on the rest of the SQL as we don't take account of sessions which span midnight. Taking account of these can be done relatively easily by doing to the following (see fiddle).
SELECT (f_day + f_time)::TIMESTAMP FROM sessions;

Now the advent of GENERATED columns, you don't even have to worry about this - just have a GENERATED field as above!
If a constraint to the second is unworkable - logins at the same time, you could possibly use TIME(2) (or 3..6) for ensuring uniqueness. If [you don't want | can't have] UNIQUE constraints, you could put in DISTINCT in your SQL for identical login and logout times - though this is unlikely.
The fact remains that some simple DDL like this enormously simplifies your subsequent SQL (see discussion at the end of the "complex" explanation below).
You might also want to put ctn and/or day in your DDL UNIQUE constraints as shown? I've also added what I think may be good indexes! You might also want to investigate the OVERLAPS operator?
As for the sample data, I also added a few records for testing my solution as follows:
INSERT INTO sessions (id, ctn, day, f_time, l_time)
VALUES
( 1, 707, '2019-06-18', '10:48:25', '10:56:17'),
( 2, 707, '2019-06-18', '10:53:17', '11:00:49'),
( 3, 707, '2019-06-18', '10:54:31', '10:59:43'),  -- record 3 is completely covered
                                                  -- by record 2

( 4, 707, '2019-06-18', '11:03:59', '11:10:39'),
( 5, 707, '2019-06-18', '11:04:41', '11:08:02'), -- GROUP 2 record 6 completely
                                                 -- covers record 7

( 6, 707, '2019-06-18', '11:11:04', '11:19:39'), -- GROUP 3

( 7, 707, '2019-06-18', '12:15:15', '13:13:13'),
( 8, 707, '2019-06-18', '13:04:41', '13:20:02'),
( 9, 707, '2019-06-18', '13:17:17', '13:22:22'), -- GROUP 4

(10, 707, '2019-06-18', '14:05:17', '14:14:14'); -- GROUP 5

I'll go through my logic step by step - good for you perhaps, but also for me as it helps me to clarify my thinking and will ensure that the lessons which I have learnt from this exercise will stay with me - "I hear and I forget. I see and I remember. I do and I understand." - Confucius.
All of the following is included in the fiddle.
/**

So, the desired result is:

Interval 1 - start: 10:48:25 - end 11:00:49
Interval 2 - start: 11:03:59 - end 11:10:39
Interval 3 - start: 11:11:04 - end 11:19:39
Interval 4 - start: 12:15:15 - end 13:22:22
Interval 5 - start: 14:05:17 - end 14:14:14

**/

The first step is to use the LAG function (documentation) as follows:
SELECT
  s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
  CASE
    WHEN LAG(s.l_time) OVER () > f_time THEN 0
    ELSE 1
  END AS ovl
FROM sessions s

Result:
id  ctn     ft  lt  ovl
1   707     10:48:25    10:56:17    1
2   707     10:53:17    11:00:49    0
3   707     10:54:31    10:59:43    0
4   707     11:03:59    11:10:39    1
5   707     11:04:41    11:08:02    0
6   707     11:11:04    11:19:39    1
7   707     12:15:15    13:13:13    1
8   707     13:04:41    13:20:02    0
9   707     13:17:17    13:22:22    0
10  707     14:05:17    14:14:14    1

So, whenever there's a new interval, there's a 1 in the ovl (overlap) column.
Next, we take the cumulative SUM of those 1's as follows:
SELECT
  t1.id, t1.ctn, t1.ft, t1.lt, t1.ovl,
  SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                          AND CURRENT ROW) AS s
FROM
(
  SELECT
    s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
    CASE
      WHEN LAG(s.l_time) OVER () > f_time THEN 0
      ELSE 1
    END AS ovl
  FROM sessions s
) AS t1
ORDER BY lt, id

Result:
id  ctn     ft  lt  ovl     s
1   707     10:48:25    10:56:17    1   1
3   707     10:54:31    10:59:43    0   1
2   707     10:53:17    11:00:49    0   1
5   707     11:04:41    11:08:02    0   2
4   707     11:03:59    11:10:39    1   2
6   707     11:11:04    11:19:39    1   3
7   707     12:15:15    13:13:13    1   4
8   707     13:04:41    13:20:02    0   4
9   707     13:17:17    13:22:22    0   4
10  707     14:05:17    14:14:14    1   5

So, we have now "split out", and have a way of distinguishing between, our intervals - each interval has a different value of s - 1..5.
So, now we want to get the lowest value of f_time and the highest value of l_time for these intervals. My first attempt using MAX() and MIN() went as follows:
SELECT
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt) AS min_f,
  MAX(lt) OVER (PARTITION BY s ORDER BY ft, lt) AS max_l,
  s
FROM
(
  SELECT
    t1.id, t1.ctn, t1.ft, t1.lt, t1.ovl,
    SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW) AS s
  FROM
  (
    SELECT
      s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
      CASE
        WHEN LAG(s.l_time) OVER () > f_time THEN 0
        ELSE 1
      END AS ovl
    FROM sessions s
  ) AS t1;
  ORDER BY id, lt
)AS t2
ORDER BY s, rn ASC, min_f;

Result:
rn  min_f   max_l   s
1   10:48:25    10:56:17    1
2   10:48:25    11:00:49    1
3   10:48:25    11:00:49    1
1   11:03:59    11:10:39    2
2   11:03:59    11:10:39    2
1   11:11:04    11:19:39    3
1   12:15:15    13:13:13    4
2   12:15:15    13:20:02    4
3   12:15:15    13:22:22    4
1   14:05:17    14:14:14    5

Note how we have to get rn = 3 for the first interval, rn = 3 for the fourth and different values of rn for different intervals - if there were 7 sub-intervals making up one interval, then we would have to retrieve rn = 7 - this stumped me for a while!
Then the power of Window functions came to the rescue - if you sort the MAX() and MIN() differently, the correct result drops into our lap:
SELECT
  ROW_NUMBER() OVER (PARTITION BY s) AS rn,
  MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f,
  MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,
  s
FROM
(
  SELECT
    t1.id, t1.ctn, t1.ft, t1.lt, t1.ovl,
    SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW) AS s
  FROM
  (
    SELECT
      s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
      CASE
        WHEN LAG(s.l_time) OVER () > f_time THEN 0
        ELSE 1
      END AS ovl
    FROM sessions s
  ) AS t1
  ORDER BY id, lt
)AS t2
ORDER BY s, rn ASC, min_f;

Result:
rn  min_f   max_l   s
1   10:48:25    11:00:49    1
2   10:48:25    11:00:49    1
3   10:48:25    10:59:43    1
1   11:03:59    11:10:39    2
2   11:03:59    11:08:02    2
1   11:11:04    11:19:39    3
1   12:15:15    13:22:22    4
2   12:15:15    13:22:22    4
3   12:15:15    13:22:22    4
1   14:05:17    14:14:14    5

Note that now, rn = 1 is always our desired record - this is the result of:
  MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f,
  MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,

Note that for MIN(), the sort is by lt DESC and for the MAX() (partitioned by interval - i.e. s) it is by ft DESC. This matches up the smallest ft with the largest lt which is what we want.
This is essentially our desired result - just add a bit of tidying up and formatting as per the OP's requirements and we're good to go. This part also demonstrates the use of another very useful Window function - ROW_NUMBER().
SELECT
  ROW_NUMBER() OVER () AS "Interval No.",
  ' Start time: ' AS " ",
  t3.min_f AS "Interval start" ,
  ' End time: ' AS " ",
  t3.max_l AS "Interval stop",
  ' Duration: ' AS " ",
  (t3.max_l - t3.min_f) AS "Duration"
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY s) AS rn,
    MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f,
    MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,
    s
  FROM
  (
    SELECT
      t1.id, t1.ctn, t1.ft, t1.lt, t1.ovl,
      SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                             AND CURRENT ROW) AS s
    FROM
    (
      SELECT
        s.id AS id, s.ctn AS ctn, s.f_time AS ft, s.l_time AS lt,
        CASE
          WHEN LAG(s.l_time) OVER () > f_time THEN 0
          ELSE 1
        END AS ovl
      FROM sessions s
    ) AS t1
    ORDER BY id, lt
  )AS t2
  ORDER BY s, rn ASC, min_f
) AS t3
WHERE t3.rn = 1;

Final result:
Interval No.        Interval start      Interval stop       Duration
1    Start time:    10:48:25     End time:  11:00:49     Duration:  00:12:24
2    Start time:    11:03:59     End time:  11:10:39     Duration:  00:06:40
3    Start time:    11:11:04     End time:  11:19:39     Duration:  00:08:35
4    Start time:    12:15:15     End time:  13:22:22     Duration:  01:07:07
5    Start time:    14:05:17     End time:  14:14:14     Duration:  00:08:57

I can make no guarantees about the performance of this query if there is a large number of records, see the result of EXPLAIN (ANALYZE, BUFFERS) at the end of the fiddle. However, I'm assuming that since it's in a report style format, it might be for a given value of ctn and/or day - i.e. not too many records?
"Complex" explanation:
I won't show every step - after eliminating the duplicate f_times and l_times, the steps are identical.
Here, the table definition and data are slightly different (fiddle available here):
CREATE TABLE sessions
(
        id serial NOT NULL PRIMARY KEY,
        ctn INT NOT NULL,
        f_day DATE NOT NULL,
        f_time TIME(0) NOT NULL,
        l_time TIME(0) NOT  NULL,
        CONSTRAINT ft_lt_lt CHECK (f_time < l_time),
        -- CONSTRAINT ft_uq UNIQUE (f_time),
        -- CONSTRAINT lt_uq UNIQUE (l_time)
        CONSTRAINT ft_lt_uq UNIQUE(f_time, l_time)
        -- could put in a DISTINCT somewhere to counter this possibility or
        -- maybe have TIME(2) to ensure no duplicates?
);

The only constraints that I've kept are CHECK (f_time < l_time) (couldn't be any other way) and UNIQUE f_time, l_time (maybe add day and/or ctn to that - the advice about TIME(2) or (3...6) above also applies.
I'll leave it up to the reader to apply UNIQUE to combinations of ctn and f_day as applicable!
INSERT INTO sessions (id, ctn, day, f_time, l_time)
VALUES
( 1, 707, '2019-06-18', '10:48:25', '10:56:17'), -- note - same l_times
( 2, 707, '2019-06-18', '10:48:33', '10:56:17'), -- need one with lowest f_time
( 3, 707, '2019-06-18', '10:53:17', '11:00:49'),
( 4, 707, '2019-06-18', '10:54:31', '10:59:43'), -- note - same f_times
                                                 -- need one with greatest l_time
( 5, 707, '2019-06-18', '10:54:31', '10:57:37'), -- GROUP 1

( 6, 707, '2019-06-18', '11:03:59', '11:10:39'),
( 7, 707, '2019-06-18', '11:04:41', '11:08:02'), -- GROUP 2, record 6 completely
                                                 -- covers record 7
( 8, 707, '2019-06-18', '11:11:04', '11:19:39'), -- GROUP 3

( 9, 707, '2019-06-18', '12:15:15', '13:13:13'),
(10, 707, '2019-06-18', '13:04:41', '13:20:02'),
(11, 707, '2019-06-18', '13:17:17', '13:22:22'), -- GROUP 4

(12, 707, '2019-06-18', '14:05:17', '14:14:14'); -- GROUP 5

I've added a couple of potentially "troublesome" records (2 & 4) with the same f_time and l_time within the same interval. So, in the event of an identical f_time, we want the sub-interval with the greatest l_time and vice-versa for the case of an identical l_time (i.e. smallest f_time).
So, what I did in this case was to eliminate duplicates by chaining CTE's (aka the WITH clause) as follows:
WITH cte1 AS
(
  SELECT s.*, t.mt, t.lt
  FROM sessions s
  JOIN
  (
    SELECT
      DISTINCT
      ctn,
      MIN(f_time) AS mt,
      l_time AS lt
    FROM sessions
    GROUP BY ctn, l_time
    ORDER BY l_time
  ) AS t
  ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
  ORDER BY s.l_time
),
cte2 AS
(
  SELECT
    DISTINCT
    ctn,
    f_time AS ft,
    MAX(lt) AS lt
  FROM cte1
  GROUP BY ctn, f_time
  ORDER BY f_time
)
SELECT * FROM cte2
ORDER BY ft;

Result:
ctn     ft  lt
707     10:48:25    10:56:17
707     10:53:17    11:00:49
707     10:54:31    10:59:43
707     11:03:59    11:10:39
707     11:04:41    11:08:02
707     11:11:04    11:19:39
707     12:15:15    13:13:13
707     13:04:41    13:20:02
707     13:17:17    13:22:22
707     14:05:17    14:14:14

And then I treat cte2 as the starting point for the process in the "easy" explanation.
The final SQL is as follows:
WITH cte1 AS
(
  SELECT s.*, t.mt, t.lt
  FROM sessions s
  JOIN
  (
    SELECT
      DISTINCT
      ctn,
      MIN(f_time) AS mt,
      l_time AS lt
    FROM sessions
    GROUP BY ctn, l_time
    ORDER BY l_time
  ) AS t
  ON (s.ctn, s.f_time, s.l_time) = (t.ctn, t.mt, t.lt)
  ORDER BY s.l_time
),
cte2 AS
(
  SELECT
    DISTINCT
    ctn,
    f_time AS ft,
    MAX(lt) AS lt
  FROM cte1
  GROUP BY ctn, f_time
  ORDER BY f_time
)
SELECT
  ROW_NUMBER() OVER () AS "Interval No.",
  ' Start time: ' AS " ",
  t3.min_f AS "Interval start" ,
  ' End time: ' AS " ",
  t3.max_l AS "Interval stop",
  ' Duration: ' AS " ",
  (t3.max_l - t3.min_f) AS "Duration"
FROM
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY s) AS rn,
    MIN(ft) OVER (PARTITION BY s ORDER BY ft, lt DESC) AS min_f,
    MAX(lt) OVER (PARTITION BY s ORDER BY ft DESC, lt) AS max_l,
    s
  FROM
  (
    SELECT
    t1.ctn, t1.ft, t1.lt, t1.ovl,
    SUM(ovl) OVER (ORDER BY t1.ft ASC ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW) AS s
    FROM
    (
      SELECT
        c.ctn AS ctn, c.ft AS ft, c.lt AS lt,
        CASE
          WHEN LAG(c.lt) OVER () > ft THEN 0
          ELSE 1
        END AS ovl
      FROM cte2 c
    ) AS t1
    ORDER BY t1.lt
  ) AS t2
  ORDER BY s, rn ASC, min_f
) AS t3
WHERE t3.rn = 1
ORDER BY t3.rn;

Result:
Interval No.        Interval start      Interval stop       Duration
1    Start time:    10:48:25     End time:  11:00:49     Duration:  00:12:24
2    Start time:    11:03:59     End time:  11:08:02     Duration:  00:04:03
3    Start time:    11:11:04     End time:  11:19:39     Duration:  00:08:35
4    Start time:    12:15:15     End time:  13:22:22     Duration:  01:07:07
5    Start time:    14:05:17     End time:  14:14:14     Duration:  00:08:57

As you can see, it's pretty hairy stuff - not having the UNIQUE constraints in the DDL has doubled the length of the SQL and the time taken for the planning and executions stages, and made it pretty horrible into the bargain.
See the end of the fiddle for the plans for both queries! Lessons to be learnt there! As a rule of thumb, the longer the plan, the slower the query!
I'm not sure that the indexes can play any role here since we're selecting from the entire table and it's very small! If we were filtering a large table by ctn and/or f_day and/or f_time, I'm pretty sure that we would start to see differences in the plans (and timings!) if there were no indexes!


I realize this has been answered before on Stack Overflow. It got me going in the right direction.

I now realize I should add the actual time stamp of the leave to my Start and End Dates and do a simple compare. I wish I had figured this out months ago; it would have saved me much pain and suffering.

For anyone in the future who has a similar issue:

My new StartDate will be recorded as 08/22/2015 09:00.000 instead of 08/22/2015 00:00.000
The EndDate will be 8/23/2015 13:00:00.000

I can then compare as per the linked answer:

(
    (@Start > StartDate and @Start < EndDate)
    OR (@End > StartDate and @End < EndDate)
    OR (@Start < StartDate and @End >EndDate)
)


German umlaute represented by questionmarks when latin1 is used

See "Question Marks" in here .  In particular,

The bytes to be stored are not encoded as utf8/utf8mb4. Fix this.
The column in the database is CHARACTER SET utf8 (or utf8mb4). Fix this.
Also, check that the connection during reading is UTF-8.

Since you seem to be aiming for latin1, not utf8, see "Test the data" in that link.  äüöß will be hex E4 FC F6 DF in latin1, or C3A4 C3BC C3B6 C39F in utf8/utf8mb4.

SET NAMES establishes the encoding in the client, not the server.  The table's encoding is based on SHOW CREATE TABLE; let's see that.

OR...

It is OK if you want latin1 used in the columns/tables, but your client wants utf8.  Then the connection setting (to say utf8 / UTF-8) or the use of SET NAMES utf8 (after connecting) will alert MySQL to convert as data is transferred between the client and the server and database.

(With latin1, you are limited to Western European characters.)


I had the same problem and solved it by adding domain admin user into the local Administrators group on local computer (where I installed DB2 DB).


SQL Server and TFS - How to rename stored procedures

This question isn't so much about how to RENAME a stored procedure, as it is asking about how to MERGE the RENAME to other branches in TFS.

As pointed out by Dan Guzman in the comments, the Refactor > Rename feature while editing a SQL file should be used:

(note that this is not an option if you right-click the file; you have to right click the entity name in the file (or have the caret on the name and use the shortcut chord))

The hard part is now merging the rename between branches.

Using either the MERGE command from Source Control Explorer or the unshelve /migrate command to TFS Power Tools (tfpt.exe), the pending changes are generated as a DELETE and an ADD which deletes the link to the TFS history!

Furthermore, your .sqlproj file will be a giant headache to merge conflicts this way.

Instead, the renamed files should be manually renamed in the target branch, then the TFS Power Tools unshelve /migrate command will just require merging the file contents as normal, with the .sqlproj file already having the changes made to it (so you can choose the Keep target branch version option on it, presuming you don't have other ADD or DELETE changes additional to the RENAME changes).

There seems to be another method using labels, but the gated check-in process is a contraindication to it.

Note that if you start getting this error message renaming files, immediately restart Visual Studio and manually fix the .sqlproj file in a text editor as the file was renamed on disk but not in the project file:

To recap, this is the process I have found to work the best:

Manually perform all the renames in the target branch
Ensure changes in the source branch are in a shelfset
Use the TFS Power Tools unshelve /migrate command
Verify. Verify. Verify. And also verify by using this advanced publish option:

I hope this helps somebody, as this process has been a serious headache for me because I have to merge to multiple branches, and my changeset is very large.


Design rules to make database easier to maintain

It really is terribly inconvenient.

Some RDBMSes make it a bit easier with things like synonyms, more flexible changes to views, etc. But it's still a massive pain to do the sort of thing you're describing.

PostgreSQL doesn't let you do much to the underlying tables without dropping and re-creating the views and every object that depends on them, which makes them very inconvenient.

Your model of nested views will also quickly get a bit costly in query planner time. Plus you'll land up with a big search_path and lots of schemas quite quickly if you use a versioned schema approach. Or if you create name-versioned wrapper views for every DB object with every schema change you'll land up with huge numbers of database objects.

Not only that, but dealing with multiple values, upserts, concurrency and locking correctly when you're going via updatable views with rules can get hairy. Take your email address example: you have a row with an email address already set. A v1 client tries to clear it (set it to null); meanwhile a v2 client tries INSERT a second email address into the separate addresses table. What do you do? There are related problems with upserts and similar. It gets ugly fast.

Not to mention that you'll land up with kludges a lot, like having to aggregate lists of email addrs into a comma separated field, then de-aggregate them when UPDATEd, compare to the aggregated current table contents, and do a merge operation to add/delete email addresses. All while ensuring no concurrent operations occur on the same data. Doing this with triggers and rules is fun at all and very prone to deadlocks.

So personally ... I wouldn't bother. The consumer of the database is your API service. You can handle the change abstraction and versioning there to a large extent, and that's just what I suggest you do. You probably have a data access layer in the application that can help abstract some of it, but to a large extent it's about updating things so that the new code exposes the old APIs remapped onto the new backend, so you're not maintaining multiple versions of everything.

People who do try to maintain a consistent database "API" usually do so via stored procedures (or in PostgreSQL, functions). This has some ugly performance consequences, especially since in PostgreSQL non-inlined function results are fully materialised before being returned. It's basically the same approach as a web API anyway; expressing operations on the data as the interface rather than manipulating the data structure directly. If you have a web API there's no need to duplicate this at the DBMS level as well.


How can I add back Identity to a column?

Dropping the primary key constraint from a table does not alter the IDENTITY property of the primary key column.

USE tempdb;
DROP TABLE dbo.Question;
GO
CREATE TABLE dbo.Question
(
    QuestionID INT NOT NULL CONSTRAINT PK_Question PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , SomeData VARCHAR(255) NULL
);

INSERT INTO dbo.Question (SomeData) VALUES ('test');

SELECT *
FROM dbo.Question;

ALTER TABLE dbo.Question DROP CONSTRAINT PK_Question;

INSERT INTO dbo.Question (SomeData) VALUES ('test2');

SELECT *
FROM dbo.Question;

The code above creates a table with a primary key, then drops the primary key.  It then adds a row to the table; and as you can see in the results below, the new row contains an auto-generated IDENTITY value for the QuestionID column.

Results:

Be aware that you could use the SQL Server Management GUI to modify the IDENTITY property of the column in question, however behind the scenes, SSMS will actually drop and recreate the table, which might be problematic to say the least.


Data driven subscription query parameters in SSRS

When creating the dataset for the data-driven subscription, you can use those results to populate several email delivery options.  For each row returned by the data-driven subscription dataset, it will send one email.  Each of those rows should contain the necessary columns to feed the required parameters for each report to be unique to its recipients (i.e. Department), along with the email address(es) to which you want to send each report.  The data-driven subscription will only send out reports for the rows its dataset returns, so you will need to make sure the data-driven subscription dataset returns rows that filter the report parameters correctly.  If your report dataset will return more than one row, you will need to ensure data-driven subscription dataset results are grouped appropriately to ensure you send the reports in the desired granularity.

ID   Dept   Amount   Name     Email

1    Sales  $70      Shelly   shelly@email.com; sales@email.com


Permissions for executing subscription in SSRS

If you set up a report subscription with a One-time Schedule and gather its Schedule ID, you can execute it by using sp_start_job.  SSRS manages subscriptions with SQL Server Agent, which makes this possible without needing to use Reporting Server Events.  The executing user, I imagine a service account, will then only need to be a part of the SQLAgentOperatorRole.  See: sp_start_job#permissions.
You allude to this in an edit in you question, but I'd like to outline the process in case anyone else needs a specific solution.

Setting up the schedule is easy.  The default time is 2am on the day you set up the subscription schedule and has likely already passed, so you can leave it.

Then you can gather your Schedule ID by querying the Report Server database tables:

select C.Name ReportName,
    S.Name ScheduleName,
    SU.Description SubscriptionName,
    S.ScheduleID
from ssrs.Catalog C
join ssrs.ReportSchedule RS
    on C.itemID = RS.ReportID
join ssrs.Schedule S
    on RS.scheduleID = S.ScheduleID
join ssrs.Subscriptions SU
    on RS.SubscriptionID = SU.SubscriptionID
where C.name = 'Test Report'

(1 row(s) affected)

You can kick off the subscription by referencing the job name matching your Schedule ID:

/*Run Subscription*/
exec msdb.dbo.sp_start_job '8037A345-9F7C-45B0-99FB-B110996BD521'

(1 row(s) affected)
Job '8037A345-9F7C-45B0-99FB-B110996BD521' started successfully.

I have not yet had an issue with the a Schedule ID (GUID) changing and breaking the execution, but as a fail-safe you can create a data-driven subscription and explicitly state a unique description.

You can then reference the description to gather your Schedule ID dynamically.

declare @ScheduleID uniqueidentifier

select @ScheduleID = S.ScheduleID
from ssrs.Catalog C
join ssrs.ReportSchedule RS
    on C.itemID = RS.ReportID
join ssrs.Schedule S
    on RS.scheduleid = S.ScheduleID
join ssrs.Subscriptions SU
    on RS.SubscriptionID = SU.SubscriptionID
where C.name = 'Test Report'
    and SU.Description = 'My Test Report Subscription'

exec msdb.dbo.sp_start_job @ScheduleID

(1 row(s) affected)
Job '8037A345-9F7C-45B0-99FB-B110996BD521' started successfully.


You mean that you can see that the database exist, even if you don't have access to it? Yes, that is the way it is, quite simply.

From a technical standpoint, the reason is (likely) that whether or not you have access to a database is stored inside the database. So, SQL server would have to open all databases in tools such as SSMS/Object Explorer in order to determine whether or not to show each database. I.e., the info isn't stored in master.

From a practical standpoint, the reason is it is because MS did it that way. I'm not trying to be a smart-Alec here, but some things are just the way they are...

Two ways with which you can possibly handle this:

Use contained databases. If you turn on partial containment for a database, then you can login directly to the database without a login. The session will be sandboxed to that database.

Deny the VIEW ANY DATABASE server permission for the logins in question. They will now only see the databases that they own. I.e., this is only usable for persons who own the databases they will use. A non-owner will still be able to use the database it has access to, but it won't show up in sys.databases.


Can you run 11g and 18c on the same machine for SE2/EE editions?

yes.

I'd place them in different ORACLE_HOMEs.  BUT, you only need to run 1 Listener.

Can you run 11g and 18c on the same OS if both are XE?

no. (But - 18c XE can run in a VM)

11g XE License limits you to one XE instance per physical machine.

  Any use of the Oracle Database Express Edition is subject to the
  following limitations;

  Express Edition is limited to a single instance on any server;
  Express Edition may be installed on a multiple CPU server, but may only be executed on one processor in any server;
  Express Edition may only be used to support up to 11GB of user data (not including Express Edition system data);
  Express Edition may use up to 1 GB RAM of available memory.

18c XE License does not have Condition 1 from 11g XE.  As I understand, this was done so that you can run 18c XE inside a VM:

  Oracle Database 18c Express Edition automatically constrains itself to
  the following resource restrictions;

  2 CPU threads;
  2 GB of RAM; and
  12 GB of user data.

The code also prevents you from running multiple XE instances on the same host.  ( source )

  4.2 Oracle Database XE Installation and Execution Restrictions

  Oracle Database XE restricts itself to only one installation per logical environment. The logical environment can either be a virtual
  host such as a VM or container, or a physical host. If more than one
  Oracle Database XE installation is attempted to be started in such a
  logical environment, an "ORA-00442: Oracle Database Express Edition
  (XE) single instance violation error" is raised and the database will
  not start.

But, certain VM technologies are supported.  That is:  You can run multiple instance of XE as long as each one is in a different Virtual Host.

  Oracle certifies the following virtualization technologies with Oracle
  Database on Windows:

  Oracle VM Server
  Microsoft Hyper-V


If you us a shell to execute a command in backticks (`), it is executed in a subshell, so all the environment variables it sets are only set in the subshell and are gone when the subshell exits.

You have to "source" the script with

. oraenv

without the backticks.


Managed backups in Azure SQL Managed Instance

I doubt that the "compress" setting affects the managed backups either way.  But there is no way to check as you don't have direct access to it.  I have caught the system taking backups before, the commands show using sp_whoisactive, but can't say that I've ever looked at the full command string before.  I will try and catch one of mine at it and update my answer if I can.

As for how managed backups work.  The management system periodically takes full and log backups according to it's own schedule.  There are some events that seem to spark a full backup to be taken that we have observed.  Creating/Restoring a database, failing between failover group nodes and when the service restarts (probably Azure balancing it's internal workload).

These backups are stored in a hidden storage account and can only be accessed through powershell.  Cross-subscription restores are not possible through powershell using managed backups.

You can additionally take COPY_ONLY backups to URL and then restore from there.  But the backups can only be restored onto another managed instance (so no restore to on-prem of any version).  However, if you have enabled TDE with Azure managed key rotation, this option is locked out for you.  Enabling TDE is the default for databases created through CREATE DATABASE, although databases restored (as in restored from on-prem) carry their TDE setting with them.


MongoDB Replica Set & Data Sync behavior

If you have modified X nodes priority at rs.conf() higher number than Y node, switch over should automatically happen WHEN X has replicated all information from Y node. Not before that. If switch doesn't happen, check rs.printSlaveReplicationInfo() that your X is really 0 second behind. It sounds like that your Y is so slow that even X cannot read opLog fast enough and keeps lagging that why.


You can use the CleanupTime  parameter for each backup job (log, diff, full)

  Specify the time, in hours, after which the backup files are deleted. If no time is specified, then no backup files are deleted.

So for example:

@Cleanuptime = 336  --2 weeks for transaction logs
@Cleanuptime = 744  --1 month for diff backups
@Cleanuptime = 8760 --1 year for full backups

Example A from Ola

  A. Back up all user databases, using checksums and compression; verify
  the backup; and delete old backup files

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@Compress = 'Y',
@CheckSum = 'Y',
@CleanupTime = 24


Given the same input, is the HASHBYTES function guaranteed to return the same output?

In terms of different instances, the HASHBYTES should return the same values if the same script is run, regardless of the server version, processor or architecture:

I've just run the following on both SQL Server 2005 and SQL Server 2012, on completely different hardware configuration. Both have produced the output 0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(100)));

However, if the collation is different, the output will be different.

In terms of column definition, the output should be the same regardless of the length of the field.

Taking your example of VARCHAR(50) and VARCHAR(100), we can test the output like so:

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(50)));
SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(100)));

Both SELECT statements return the same value:

0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA

However, be aware that VARCHAR and NVARCHAR will not produce the same HASHBYTES value, even with the same string:

SELECT  HASHBYTES('SHA1', CAST('Test' AS VARCHAR(50))); -- Returns 0x640AB2BAE07BEDC4C163F679A746F7AB7FB5D1FA
SELECT  HASHBYTES('SHA1', CAST('Test' AS NVARCHAR(100))); -- Returns 0x9AB696A37604D665DC97134DBEE44CFE70451B1A


Grant access on Secondary but not on primary in SQL Server logshipping

On Primary

Create a new Login [testLogin] and a new User [testUser] with the requiered rights and roles:

USE [master]
GO
CREATE LOGIN [testLogin] WITH PASSWORD=N'test123'
    , DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
USE [Test]
GO
CREATE USER [testUser] FOR LOGIN [testLogin];
GO
USE [Test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [testUser];
GO

These login and user must not exist and be different from your current login and user.

Once [testUser] is available on Secondary, remove [testLogin] from Primary:

USE [master]
GO
DROP LOGIN [testLogin];

  Other option on Primary

Remove the orignal Login

You must plan your update if you have to remove login for a short time and make sure you disconnect them first.

Create a new Login with the same name

CREATE LOGIN...

Link Orphan users to the newly created Login for all DBs except the log shipped DB:

USE [DB1]
GO
sp_change_users_login @Action='update_one'
    , @UserNamePattern='testUser'
    , @LoginName='testLogin';

  On Secondary [testLogin]:

Create a new login [testLogin]:

USE [master]
GO
CREATE LOGIN [testLogin] WITH PASSWORD=N'test123'
     , DEFAULT_DATABASE=[Test], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO

Link the orphan user [testUser] (shipped from Primary) to the new login [testLogin]:

USE [Test]
GO
sp_change_users_login @Action='update_one'
    , @UserNamePattern='testUser'
    , @LoginName='testLogin';


Proper table design for sparse primary key

I'm thinking clustered pk (rule_id, index)

Do that.  This isn't really a hard case.  (rule_id, index) uniquely identifies a row, so that should be your clustered PK, unless there's some compelling reason to use a different design.  and because

  I will often query the table by rule id only

rule_id should be the leading column in the index.  This supports efficient lookup by rule_id and ensures locality for inserting multiple rows for the same rule_id.


Can we allocate 10 gb RAM to postgres out of 30gb

Yes one can allocate the Shared Memory with a Kernel Flag this is documented in Postgresql Documentation

https://www.postgresql.org/docs/current/kernel-resources.html

Also may want to look into Huge Page same link further down

note this is Shared Memory other settings eat up memory independently,  see the postgres.conf  for more details


when do i usually optimize an innodb table?

In almost all situations, OPTIMIZE TABLE is a waste of time and effort.  So, in my opinion, "substantial" is really, really, big.

10-20 changes per minute -- not very busy.  Not substantial.


Modeling a Car Dealership context in an Entity-Relationship diagram

Service, inspection,  and repair are all instances of some work. If you really do need service, inspection,  and repair as separate entities, create a super-entity Work, which would have a 1..* relationship to a ticket, and make service, inspection,  and repair inherit from it (1..1). In many cases you will probably just have Work with the Type being one of service, inspection, or repair.


Creating a table with Rank ordering in Oracle

The way you put it, it is not that ORDER BY causes problems, but the fact that UID is a reserved word and can't be used as a column name:
SQL> create table test (uid number);
create table test (uid number)
                   *
ERROR at line 1:
ORA-00904: : invalid identifier

But, if you enclosed it into double quotes while creating the table, then yes - it'll "work" (but - do you really want to do that? I wouldn't):
SQL> create table test ("uid" number);

Table created.

SQL>

So: presuming that this is the case, then enclose it into double quotes while running your query. Pay attention to letter case! You MUST match it every time you use that column. Presuming it is in upper case, then you'd
SQL> create table t_distribution as
  2    select "UID", contact_email, product_id,
  3           rank() over (partition by "UID" order by product_id)
  4             priority_order
  5    from staging_test;

Table created.


For searching by prefix, you just do it.  The hard part (at least from the command line) is getting the binary data to be queried into query in a properly escaped way.

To query a table of file-blobs for files with the ELF header:

select....where data like (E'\\x7f')::bytea||'ELF%'

For indexing it, the main problem you will have is that PostgreSQL btree indexes can't index values longer than 1/3 of the page size (2712 bytes, usually) and a column of binary data is likely to have some records that exceed that.  You can build a functional index on only the first so many bytes of the data, and query that.

create index on thing (substring(data,0,100));

And you can then query it like this:

 select....where substring(data,0,100) like (E'\\x7f')::bytea||'ELF%';

PostgreSQL is not smart enough to realize that for a prefix to match the full column, it most also match a longer-than-the-query prefix of the column.  So that means you must write the match against the expression used to build the index, not against the column itself, or the index won't be used.


SQL Drive getting full delete millions of rows

Batch deletion is your best bet.

If possible, I would do something like:

<create lookup table with a list of PKs or keys of deletable rows>

WHILE 1=1
BEGIN
    DELETE TOP (100000)
    FROM MyBigTable T
    INNER JOIN MyLookupTable L
          ON T.PK = L.PK
    IF @@ROWCOUNT < 100000 BREAK;
END

Batching will keep your log use low, and using the INNER JOIN will reduce tempdb usage since you did the work of filtering in creating the first table.


Remote to a remote oracle server and get running instances?

Agree with first responder.  The only way to do what you want, and not 100% sure it is possible, would be to have very high level permissions on all servers, or have special permission settings.  Any system admin would be justified in asking for any order to set something like this up in writing.  The same goes for the DBAs for the DBs you want to run "sql script in each instance", without user/password signon.  My advice is you need to find a way that works with the standard server/DB security, and stop finding a way around them.


Changing Column type to avoid Arithmetic overflow error

If you need to change the data type of a column, you'll have to change every data type referencing that column, too.

At the database level, you'll have to change any column referencing this column. Since you need to change a PK column, this will affect all other tables where this column is used as a foreign key reference. In all of those tables you'll need to change the data type of the related columns.

Furthermore, you'll have to perform a code review to catch each and every place where all the columns identified in the step above are referenced. If there are code sections, where a value from such a column is assigned to a variable, you'll need to change the data type of that variable. If the column is used in joins to other tables or in the WHERE section of a query, you might need to change the referenced columns as well.

As a side effect after the change you might see a performance drop caused by implicit conversions, if you didn't change all references.

Once the database changes are identified, you'll have to check the application if the columns and variables that had to be changed will have any side effect to the appl. For instance, if there are columns returned as bigint that used to be integer, the app should be able to handle it.

After all changes are identified, they should be implemented during a maintenance window to avoid data inconsistency due to dropped references.

Long story short: changing a data type needs to be well-prepared and tested. I strongly vote against an "on-the-fly" change!


How to troubleshoot "Encryption not supported on the client OLE DB provider "SQLNCLI11" for linked server" error

It seems you are using always encrypted for some columns in the database. This is not supported or functional with linked servers (or other means of distributed queries), as documented here . Quote from that page:
"The following features don't work on encrypted columns:
Transactional or merge replication
Distributed queries (linked servers, OPENROWSET(T-SQL), OPENDATASOURCE(T-SQL))"
For Always Encryped (AE), the encryption/decryption of data is done by the client, the API on the client side. APIs that supports AE include ADO.NET, JDBC and ODBC. Linked Server uses OLEDB (the calling server is a client to the remote server), and OLEDB do not have the functionality to encrypt/decrypt for AE.


Send Multiple Attachments With Send DBMail

The semi-colon is a statement terminator, and you haven't correctly escaped your attachments. In other words, you've done this:

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email',
  @body = @body1,
  @file_attachments = @filename1;

@filename2; -- Syntax Error

According to the MSDN article on sp_send_dbmail:

  [ @file_attachments= ] 'file_attachments'

  Is a semicolon-delimited
  list of file names to attach to the e-mail message. Files in the list
  must be specified as absolute paths. The attachments list is of type
  nvarchar(max). By default, Database Mail limits file attachments to 1
  MB per file.

You need to do this:

Declare @filenames varchar(max)
Set @filenames = 'C:\Testfiles\Test1.csv;C:\Testfiles\Test2.csv'

EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email',
  @body = @body1,
  @file_attachments = @filenames;

If you want to programmatically add attachments, you would do something like this (notice how the second and third files begin with a semi-colon):

DECLARE @filenames varchar(max)
DECLARE @file1 VARCHAR(MAX) = 'C:\Testfiles\Test1.csv'
SELECT @filenames = @file1

-- Optional new attachments
DECLARE @file2 VARCHAR(MAX) = ';C:\Testfiles\Test2.csv'
DECLARE @file3 VARCHAR(MAX) = ';C:\Testfiles\Test3.csv'

-- Create list from optional files
SELECT @filenames = @file1 + @file2 + @file3

-- Send the email
EXEC msdb.dbo.sp_send_dbmail
  @profile_name = 'Mod',
  @from_address = 'modis@modisglobal.com',
  @recipients= 'rsmith@gmail.com',
  @subject= 'Test Email',
  @body = @body1,
  @file_attachments = @filenames;


Cannot login to SQL Server as a member of AD group

This is an old thread, but it was the first hit on Google when I encountered the same issue.

My two cents:
In my case it turned out that the AD group was a Distribution group, instead of a Security group, hence it could not be used to grant access to SQL Server.


What does enable mean in Oracle DDL constraint?

not null is a constraint. The enable that follows it is optional if you want that constraint to be active (which is the default). So

NOT NULL
NOT NULL ENABLE
NOT NULL ENABLE VALIDATE

all mean the same thing (validate is also the default, novalidate being the other option), so there really isn't an "advantage" in specifying it fully (except perhaps for documentary purposes).

If you wanted that not null constraint to be disabled, you could say:

NOT NULL DISABLE -- equivalent to NOT NULL DISABLE NOVALIDATE

or

NOT NULL DISABLE VALIDATE

See the Specifying Constraint State docs for the details and use cases on this topic.


Slow Query With Multiple Left Outer Joins

Are there ever two billing addresses for one account?  If not, toss the LEFT JOIN for that address; it has no affect on the COUNT(*).  Ditto for all the other LEFT JOINs.

Even if one of the LEFT JOINs leads to extra rows, get rid of them because of the DISTINCT.

Are there multiple profiles for each user?  If not, then much of the other baggage is wasted.

Then get rid of the nested ("derived") SELECT.

SELECT COUNT(*)   -- or maybe COUNT(DISTINCT profile_id)
    FROM profile AS p
    WHERE P.ORGANIZATION_ID = 1
      AND P.ISANONYMOUS = false

That should give you the same count as your original query.  If not, please explain where there are 1:many mappings that are not obvious.

If you aren't really asking about COUNT(*), but rather fetching multiple columns, then show us the real query; it may need other optimizations.  Also, provide SHOW CREATE TABLE so we don't waste time recommending indexes you may already have.


Move Primary Key to Filegroup (SQL Server 2012)

CREATE UNIQUE CLUSTERED INDEX Your_PK_Name
    ON YourTable(YourColumnList)
WITH (DROP_EXISTING =  ON )
ON [YourOtherFileGroup]

This preserves the logical PK property despite it not being mentioned in the syntax.


Backing up a SQL 2008 DB, then restoring, gives a SQL 2005 (compatibility level 90) DB

DISCLAIMER
  This information is being supplied as is. It might assist you in finding the root issue.

While I don't have a solution why your database is converting back to a certain level during a restore, I might be able to shed some light on why the restore might think it has to reset the compatibility level.

DBCC DBINFO

You might want to have a look at the DBCC DBINFO command.

DBCC DBINFO('<Your_DB_Name>') WITH TABLERESULTS

Have a look at the following lines:

ParentObject      | Object                     | Field             | VALUE
------------------+----------------------------+-------------------+--------
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_version       |  782
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_createVersion |  705
DBINFO STRUCTURE: | DBINFO @0x000000003609D5E0 | dbi_cmptlevel     |  110

While your database might look like it is at a certain level, internally it might be still fostering a lower level, which might be the cause of the restore setting a lower compatibility level.

DBCC PAGE

Instead of using DBCC DBINFO you can use the equally undocumented DBCC PAGE command:

DBCC TRACEON(3604)
DBCC PAGE('<Your_DB_Name>',1,9,3)

Where the syntax is

DBCC PAGE('DATABASE_NAME' | DB_ID, <FILE_ID>, <PAGE>, <LEVEL_OF_DETAILS>)

Scroll down a bit and you will find:

...
dbi_version = 782                   dbi_createVersion = 705             dbi_SEVersion = 0
dbi_dvSplitPoint = 0:0:0 (0x00000000:00000000:0000)
dbi_dbbackupLSN = 96:512:1 (0x00000060:00000200:0001)
dbi_LastLogBackupTime = 2018-11-22 09:15:01.473
dbi_nextseqnum = 1900-01-01 00:00:00.000                                 dbi_status = 0x00010000
dbi_crdate = 2012-03-14 13:14:18.510dbi_dbname = AdventureWorks2012     dbi_dbid = 9
dbi_cmptlevel = 110                 dbi_masterfixups = 0                dbi_maxDbTimestamp = 10000

Verifying The Database Backup Version

Now for some reason your system might be creating a lower level backup. You can verify your backup with the following command:

RESTORE HEADERONLY FROM DISK='B:\BACKUP\DATABASE_BACKUP.bak'

Which will return the Database Version of your backup file:

DatabaseVersion | CompatibilityLevel | SoftwareVersionMajor | SoftwareVersionMinor | SoftwareVersionBuild
----------------+--------------------+----------------------+----------------------+----------------------
782             | 110                | 12                   | 0                    | 5590

Information Output has been shortened

Is the database backup the same level as the SQL Server version running?

Questions to Ask Yourself

Are we using a third-party backup tool?
Are we using a different server to create the backups?
Are we using some other tool which might interfere with the backups?

Conclusion

You might have just hit a bug with the version of SQL Server you are using and an database internal issue with the dbi_version and dbi_createVersion information, which causes the compatibility level to set lower than you expect.

-OR-

You might be having issues with the database backup files being created with a down-level compatibility level, because...

of some 3-rd party issue
of the dbi_version and/or dbi_createVersion being different in the backup, because of, ....

a bug
a 3-rd party issue


DEFAULT PRIVILEGES do not change permissions for existing objects. They are the default privileges for newly created objects and only for the particular role they belong to. If you do not specify the role when running ALTER DEFAULT PRIVILEGES, it defaults to the current role (when executing the ALTER DEFAULT PRIVILEGES statement.
Also, since you are using a serial column, which creates a SEQUENCE, you'll want to set default privileges for sequences as well.
Run this on the user you create objects with, before you run the CREATE command:
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON TABLES TO bspu;
ALTER DEFAULT PRIVILEGES [ FOR ROLE my_create_role] GRANT ALL ON SEQUENCES TO bspu;

A word of caution for pgAdmin users. There is a bug in all versions of pgAdmin III and pgAdmin4 (including v5.3). The reverse engineered SQL script for the database or schema nodes displays DEFAULT PRIVILEGES ignoring  the owning user and is therefore incorrect in certain situations. I reported the bug (repeatedly), but the project encountered difficulties fixing it. pgAdmin III was discontinued in favor of pgAdmin4. The bug is still there in pgAdmin4.
For existing objects you may also be interested in this "batch" form of the GRANT command:
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO bspu;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO bspu;

More under this related question on SO:

Grant all on a specific schema in the db to a group role in PostgreSQL


I think you could run a query from SQL Server Management Studio from the machine that you can connect from and run the query with client statistics. That won't be exactly the same as a ping but you can see the server execution time (time from request sent from client to time server starts to send response back) and the client processing time (time from when the server starts to send to when the client receives it). That is why it isn't a ping because client execution time includes that network time and the time on the client to process.

I'd run a fairly simple query with a handful or so of rows returned. And maybe a slightly more complex query with more rows. You should also run each of these a few times in a row to eliminate any other slowdowns with the query.

Again, this won't really be a "ping" but it can at least show you some information and you can use this to trend information over time as well by running the same queries and looking at the info.

To look at client statistics - from within the query window of SSMS, go to Query and then select Include Client Statistics. You'll see the details in the Client Statistics tab of the results.

If you could also run SSMS queries on the actual server itself and run the same queries you can look at the difference in these times for the same queries and that should give a rough approximation of the impact of network latency perhaps.


Extract MySQL information from a compressed tar.gz file

Actually it seems a backup by copy files (as tables are myisam tables) not a mysqldump. So you just need to untar this compressed file by below command on linux-

$ untar -zxvf your_backup.tar.gz .

Note: Please run this command from the directory where your backup file exist and it will provide you normal backup also in same directory.

Now you can simply copy these files (*.frm, *.MYI, *.MYD) to your database directory in which Database you want to restore them and it should work.

Note: if there is any permission related issue then use below command to make sure mysql can use these files/tables.

$ chown -R mysql:mysql /path_till_your_db_directory/*


How can I redirect database requests in my postgres server

As @horse_with_no_name already comment: you can use pgbouncer to do that.

Basically you need to create a pool configuration, and during your maintenance you ajust the pool config to a new server or database.

Take a look at the session [databases] on /etc/pgbouncer.ini file:

[databases]
; no connection info enforces unix socket
foodb =
; bardb config
bardb = host=127.0.0.1 dbname=bardb

So, if you want to redirect the connections for the database bardb for another database, what you need to do is edit the dbname and reload de pgbouncer service. E.G.:

; bardb config
bardb = host=127.0.0.1 dbname=maintenancedb

Please, take a look at the documentation[1] for more details.

You can do the same with the pgpool[2].

Hope it helps.

References:

https://pgbouncer.github.io/config.html
http://www.pgpool.net


MySQL - SQL statement to test minimum length of the word 'ft_min_word_len'

To retrieve ft_min_word_len as a query, run this:
SELECT variable_value FROM information_schema.global_variables
WHERE variable_name='ft_min_word_len';

But wait, you are asking

Why the third row is returned if there is no match for '(database|data)'

Did you know there are 543 words that are excluded from FULLTEXT indexes ?
Note the following from your query and row data...

The words will and this are in the stopword list.
The word show is not in the stopword list, so searching for show should produce a tangible result.
The word data is not in the body column as its own word. Try searching for data* or +data*

I wrote about this before and how to disable the default stopword list:

Jan 26, 2012 : Mysql fulltext search my.cnf optimization
Dec 29, 2011 : MySQL Full Text index issue

UPDATE 2013-04-25 12:26 EDT
@Michael-sqlbot has pointed out in his comment to my answer that QUERY EXPANSION does a double lookup to include "near-misses" due to relevance. If @Michael-sqlbot adds his comment as an answer, I will retract my answer, upvote his answer, and ask that @Michael-sqlbot's answer be accepted.


How to reconstruct functional dependencies from 3NF decomposition?

If AD is the only candidate key of R, then we know that in the original relation the dependency AD → BC holds.

But, since the relation is not in BCNF, we know that there are also other dependencies for which the determinant is not a superkey. Given the fact that the decomposition preserves the dependencies, we know that the other dependencies are constituted by attributes that are in the decomposed relations.

The simplest possible case is that there are dependencies A→B and B→C. So the original relation can be:

R<(ABCD), {AD→BC, A→B, B→C}>

In fact, if we apply both the classical BCNF decomposition algorithm as well as the 3NF synthesis algorithm we obtain the specified decomposition.


Write performance of Postgresql 9.1 with read-only slave

Truncate should be a logical operation that should not be too bad disk I/O-wise.  I would not expect a lot of problems there.


SQL Stored Procedure with one parameter and several variables that will change over time

You are missing spaces in your concatenated string and you also need to quote your filename.
I also don't see the point of mixing concatenation and calling sp_executesql with parameters.

Try changing this part:

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename' + @p1 + 'FROM' + @var5 + 'WITH
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = Finnish_Swedish_CI_AS,
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = @var3,
   FORMATFILE = @formatFile,
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable, N'@p1 nvarchar, @var5 nvarchar, @var3 nvarchar, @formatFile nvarchar', @p1, @var5, @var3, @formatFile
   end

Into this, mind the spaces where you concatenate @p1 and @var5 and single quotes:

set @insertvariable =  N'
BULK INSERT dbname.dbo.tablename ' + @p1 + ' FROM ''' + @var5 + ''' WITH
    (
   FIRSTROW = 1,
   ORDER (Id),
   CODEPAGE = ''Finnish_Swedish_CI_AS'',
   FIELDTERMINATOR = \t,
   ROWTERMINATOR = 0x0a,
   TABLOCK,
   ERRORFILE = ''' + @var3 + ''',
   FORMATFILE = ''' + @formatFile + ''',
   KEEPNULLS
   );'
   exec sp_executesql @insertvariable
   end


Is it bad practice to calculate within the insert query?

INSERT INTO hr (hr_sign, hr_job, hr_code, hr_subject, hr_qty, hr_ppu, hr_sum)
SELECT 'JD',
       '20-0001',
       code_id,
       'testing',
       CASE code_id WHEN 'PM' THEN 1.25
                    WHEN 'COPY' THEN 2.25
                    END,
       code_ppu,
       code_ppu * CASE code_id WHEN 'PM' THEN 1.25
                               WHEN 'COPY' THEN 2.25
                               END
FROM code
WHERE code_id IN ('PM','COPY');

PS. It is safe to store the relation between code_id and according coefficient (set into hr_qty and used during hr_sum calculation) in separate table and use this table in data source instead of CASEs.

Is it smart or bad practice to do this calculation in the SQL query?

Calculations is a norma. But shown subqueries usage - hardly...

How do I transfer them as parameters?

For example, something like (not tested)
INSERT INTO hr (hr_sign, hr_job, hr_code, hr_subject, hr_qty, hr_ppu, hr_sum)
WITH
cte1 AS ( SELECT one_row
          FROM REGEXP_SPLIT_TO_TABLE(@packed_csv, ',') AS parameters(one_row) ),
cte2 AS ( SELECT SPLIT_PART(one_row, ':', 1) AS code_id,
                 SPLIT_PART(one_row, ':', 2)::DECIMAL AS multiplier
          FROM cte1 )
SELECT 'JD',
       '20-0001',
       code.code_id,
       'testing',
       cte2.multiplier,
       code.code_ppu,
       code.code_ppu * cte2.multiplier
FROM code
JOIN cte2 ON code.code_id = cte2.code_id;

where @packed_csv is a string like 'PM:1.25,COPY:2.25',


If the number of categories is fixed an known in advance, this is just some conditional summing over your existing solution... and add whatever logic is needed for the visits via a subquery:

SELECT
    date,
    sum(CASE WHEN `categoryId`=1 THEN `ticketCount` END) AS `Category A`,
    sum(CASE WHEN `categoryId`=2 THEN `ticketCount` END) AS `Category B`,
    sum(CASE WHEN `categoryId`=3 THEN `ticketCount` END) AS `Category C`,
    (SELECT  count(id) FROM event_page_visits
      WHERE event_id = 1 and date(created_at) = date) AS `Visits`
FROM
(
    select
      `list_of_dates`.`date` as `date`, `ticket_categories`.`id` as `categoryId`, `ticket_categories`.`name` as `categoryName`, count(tickets.id) as ticketCount
    from (
      SELECT DATE_ADD('2017-03-10', INTERVAL seq.seq DAY) AS DATE FROM seq_0_to_999 AS seq WHERE DATE_ADD('2017-03-10', INTERVAL seq.seq DAY) <= '2017-03-14'
    ) as list_of_dates
    cross join
      `ticket_categories`
    left join
      `tickets` on
        `tickets`.`ticket_category_id` = `ticket_categories`.`id` and
        `tickets`.`created_at` >= `list_of_dates`.`date` and
        `tickets`.`created_at` < list_of_dates.date + INTERVAL 1 DAY
    where
      `ticket_categories`.`event_id` = 1
    group by
      `date`, `ticket_categories`.`id`
) AS q
GROUP BY
   `date`
order by
  `date` asc ;

And you just get:

|       date | Category A | Category B | Category C | Visits |
|------------|------------|------------|------------|--------|
| 2017-03-10 |          4 |          3 |          2 |     10 |
| 2017-03-11 |          0 |          0 |          0 |      2 |
| 2017-03-12 |          1 |          1 |          0 |      3 |
| 2017-03-13 |          0 |          0 |          3 |      6 |
| 2017-03-14 |          0 |          0 |          0 |      0 |

Fiddle.


How do you design schema for tracking change history in NoSQL?

Add three attributes to the project and asset documents - WrittenWhen, WrittenBy and IsActive. Populate these in the "real" data.

When a record changes copy it but with IsActive flag flipped. Retain the original values for WrittenWhen and WrittenBy. The original record is updated with the amending user's id and the time stamp. I do it this way around so any other record holding this record's magic id retains that pointer.

When reading only look for records with the right value of IsActive.

IsActive can be omitted if the archive records are written to a separate collection / namespace / database / whatever your technology supports.

  Thank you Michael, Couple concerns I have with this implementation; Size - this may have a lot of redundant data in it, possibly 100 or more times data than needed would be stored, with many deployments using mongodb this may not be an issue, but with the BaaS the like of firebase, this can make a big difference. My other concern is that it doesn't know which data point was edited unless you diff with previous, and with possibility of multiple users editing multiple data points, to show a "last updated by * on *" it may take a lookup of many older records – Daniel Mar 10 at 16:16

The points you raise are valid. An alternative design would be to store only the changed values in the history store. A record there would then be (primary key field(s), name of changed field, old value, new value, written by, written when). Storing only the parts that changed leads directly to two new problems.

One, you have a mandatory overhead of separating the changed parts for every single write, as opposed to deferring the diff costs until needed. Typically we want OLTP writes to be fast and audit reporting is not performance critical. So it would seem reasonable to me to defer that work to when the report is required. Of course the cost of writing the whole record will be more than of writing the shorter diff-only record. You can benchmark your infrastructure to see if that is significant.

Two, by storing only deltas you loose the data context in which a change occured. By this I mean by only storing deltas we can read a single audit record to see that, say, field 53 changed from "A" to "B". What values did all the other fields have when that change occurred? To find out you have to read backwards through history until you find the most recent value for the other fields in which you're interested, potentially all the way to the "live" record. Again, I don't know if this is a valid use case for your scenario, or if change frequency and audit read frequency would make it a problem, but it is something worth considering.

To show "last updated by .. on .. " with either scheme is a single record lookup of the last history record written because both ways store this information on every history record. To find what changed with the full record stored will require a second read and a diff, which is not needed with a delta store. To reconstruct a historical record will be light work with full records and processing-heavy with deltas.

I don't know your requirements or constraints well enough to fully evaluate one design over the other. My preference would be for the one I documented due to ease of implementation and (likely) performance. I will note, however, that there are no free lunches. The work has to be done somewhere, sometime.


What are the bad design points in the following (simple) Entity-Relationship Diagram?

I am not sure if this is the right place to pose such questions. But here some remarks and questions anyway. They are too long for a comment.

I don't understand the word representant. I did not  find it at http://dict.leo.org/. Dou you mean representative?
With telephones you mean phone numbers and with emails you mean email adresses?
Why is telephone a weak entity? What is its identifying entity? For me it is not a weak entity. A phone  extension may be a weak entity. The same holds for email and address.
can a representant have more then one address? This is not excluded by the text but it is excluded by the ER diagtram.
Why do you name some entities as singular and some as plural? I think a consisten notation will be more clear.
Why do one need a reading direction? What is the difference if one reads the diagram from bottom to top? I see no difference.
"if a representant is removed it must be saved in a list" That does not describe any relationships between a removed representant and the remaining entities. "it must be saved" is a term of computer programming and not of the business domain. It is not clear to me what you want but using an attribute to a relation may be not the appropriate solution. From your drawing I conclude a telephone is assigned to a representant. If the representant is removed the removed representant has a telephone too? If yes then this telephone cannot be used by then new representant for this address because a telephone has only one representant assigned.


How to get the size of the result of a SQL query?

There is no way.

EXPLAIN gives you an estimation; do not count on it being exact.
The number of bytes in a resultset is not directly available.  And attempts to estimate will be fraught with errors.
The structure of the QC is another variable -- there is overhead to take into account.
Since every row for a table in the QC is purged when any write to the table occurs, the QC is rarely useful at all.


Wrong error in SSIS - [Execute SQL Task] Error: There is an invalid number of result bindings returned for the ResultSetType: “ResultSetType_Rowset”

In the Execute SQL Task, when the Result Set property is set to any value except None, the result set(s) must be mapped on the Result Set page, as shown below.

The "Result Name" column is a bit misleading; what's actually required is a zero-based number.

Unfortunately, none of these requirements are validated at design-time, so the editor will happily let you click OK without entering all the necessary information.


Good explanation of cascade (ON DELETE/UPDATE) behavior

If you like the Parent and Child terms and you feel they are easy to be remembered, you may like the translation of ON DELETE CASCADE to Leave No Orphans!

Which means that when a Parent row is deleted (killed), no orphan row should stay alive in the Child table. All children of the parent row are killed (deleted), too. If any of these children has grandchildren (in another table through another foreign key) and there is ON DELETE CASCADE defined, these should be killed, too (and all descendants, as long as there is a cascade effect defined.)

The FOREIGN KEY constraint itself could also be described as Allow No Orphans! (in the first place). No Child should ever be allowed (written) in the child table if it hasn't a Parent (a row in the parent table).

For consistency, the ON DELETE RESTRICT can be translated to the (less aggresive) You Can't Kill Parents! Only childless rows can be killed (deleted.)


What is meant by Account is disabled in dba_users password column for account status as locked?

I want to understand what is meant by account disabled? Is it same as expired and locked account or it has some other meaning?

As per Oracle BOL , Yes, A locked account is an account for which login is disabled.

For Example : Oracle Database provides sample schema account, SCOTT. The SCOTT schema contains the tables EMP, DEPT, SALGRADE, and BONUS. The SCOTT account is used in examples throughout the Oracle Database documentation set. When you install Oracle Database, the SCOTT account is locked and expired.

  Locking an account preserves the user password, as well as other
  account information, but makes the account unavailable to anyone who
  tries to log in to the database using that account. Unlocking it makes
  the account available again.

For your Ref:-  Here and Here


Set up read-only privileges on the dbo schema, but allow creation of entities in another schema?

You shouldn't think about permissions in the sense that you have to own something to make changes to it. All you need to create an object in a schema is the ability to create an object in the database, and authorization on the schema (there are other ways but they are less secure). You also should think about applying the same permissions to n number of users via a role as opposed to applying those permissions individually to each user.

Here's a working example that demonstrates the syntax required for all of the things you want to do, I believe.

First, create logins from Windows, if they don't already exist:

USE [master];
GO
CREATE LOGIN [Domain\Username1] FROM WINDOWS;
CREATE LOGIN [Domain\Username2] FROM WINDOWS;

Add those users to the database (create this database as a test, perhaps):

USE a_database;
GO
CREATE USER [Domain\Username1] FROM LOGIN [Domain\Username1];
CREATE USER [Domain\Username2] FROM LOGIN [Domain\Username2];

Create a schema for them:

CREATE SCHEMA Pyramid;

Create a role, so that you don't have to maintain these permissions on every individual user, and add both users to that role:

CREATE ROLE PyramidRole;
GO
-- on modern versions:
ALTER ROLE PyramidRole ADD MEMBER [Domain\Username1];
ALTER ROLE PyramidRole ADD MEMBER [Domain\Username2];
-- on dinosaurs:
EXEC sys.sp_addrolemember @rolename = N'PyramidRole', @membername = N'[Domain\Username1]';
EXEC sys.sp_addrolemember @rolename = N'PyramidRole', @membername = N'[Domain\Username2]';

Set the permissions on the role:

-- need specific create / alter permissions:
GRANT CREATE TABLE, CREATE VIEW, CREATE FUNCTION, CREATE PROCEDURE
  TO PyramidRole;

-- to be explicit, you want them to /read/ from dbo, so:
GRANT SELECT, VIEW DEFINITION ON SCHEMA::dbo TO PyramidRole;

-- finally, this will allow them to create/alter objects in their schema:
ALTER AUTHORIZATION ON SCHEMA::Pyramid TO PyramidRole;
-- you may need other permissions depending on what else you need them to do

Now, try it out:

EXECUTE AS USER = N'[Domain\Username1]';
GO
CREATE TABLE dbo.what(id int);     -- Fails with Msg 2760
GO
CREATE TABLE Pyramid.what(id int); -- Succeeds
GO
REVERT;

Now, as you add more users and you want them to inherit this same set of permissions, you just add them to the database, and then add them to the role using ALTER ROLE ... ADD MEMBER ....

More info and more syntax examples:

Roles / ALTER ROLE
Schemas
Permissions
GRANT Database Permissions
DENY Database Permissions
EXECUTE AS


It sounds like you just need to change your outer query to also GROUP BY the employee name:

...
SELECT
(RTRIM(LTRIM(Employee))) As "employeeName"
,COUNT(1) AS "Count"
FROM employees cm
JOIN (
        Select
        RTRIM(LTRIM(employee)) As employee
        ,SUM(DollaAmt) As "TotalDollaAmt"
        ,saleDate
        ,saleID
        FROM DollaAmt
        GROUP BY RTRIM(LTRIM(employee)),saleDate,saleID
) As ia
ON RTRIM(LTRIM(ia.employee)) = RTRIM(LTRIM(cm.Employee))
WHERE CAST(saleDate As Date) BETWEEN '2017-01-01' AND '2017-01-31'
AND "TotalDollaAmt" >= '1000.00'
GROUP BY (RTRIM(LTRIM(Employee)))


Always verify if the join is returing correct number of rows from each join.
Try this,
;With CTE as
(
 select l.*, city.*
from  listings l
cross apply (
     select  top (1)  c.UnicodeName, c.name,c.regionid
     --, r.code as region, cn.code as country
     from cities c
     --inner join regions r on r.regionid = c.regionid
     --inner join Countries cn on cn.CountryId = r.countryid
     where c.location.STDistance(l.location) is not null
     order by c.Location.STDistance(l.location)
 ) as city
 )

 select c.*
  ,r.code as region, cn.code as country
 from CTE C
 inner join regions r on r.regionid = c.regionid
 inner join Countries cn on cn.CountryId = r.countryid
 OPTION (MAXDOP 1, RECOMPILE);

Has Estimated number of rows decrease.
Is difference between Actual number of rows and Estimated number of rows decrease.


MySQL Explain has different row count than slow query log

In order to answer this question, you must understand what the rows column on explain means, and the difference between calculations based on statistics and post-execution statistics.

When you run explain, the rows column will tell you, for each table access, how many rows will be examined by using the intended filter. There are two ways to calculate that: either an index dive (that usually should give you exact results) or by using approximate statistics that each engine stores independently -up to 5.6- for each table. While the first method is preferred when it can be used (simple filters on a single indexed column), in many cases, only an approximation could be used -otherwise, the query optimiser would take as much time as the query execution itself.

In any case, the rows are the calculated rows to be read (not to be returned) per table access. Even if it were exact (and many times has differences with several order of magnitudes, but still is good enough for the optimiser), it does not predict the real number of rows accessed throughout a join. For example, if you join table A (reading exactly X rows) and table B (reading exactly Y rows), in the order A -> B, the real number of rows read will be: X + # of rows returned by A (<=X) multiplied by Y, as standard mysql only supports nested loop joins.

The slow log, like the handler statistics, or other profiling mechanisms tell you the real number of rows processed and sent, because those statistics are gathered after the execution, thus exact.

Regarding your particular case, EXPLAIN is to blame because it shows that only 3 rows would be scanned for the first access, when in reality it may be doing a full index scan (as it is using the key only for sorting), which later gets multiplied for each join performed. Do not trust explain. You can use:

FLUSH STATUS;
-- Execute your query here
SHOW STATUS like 'Hand%';

To check the actual number and kind (PK access, ref, index scan, table scan) of row operations. I would use it to test each table access individually.

For more specific help, we would need the table structure of each table and the approximate selectivity of each filter condition.


Table scan instead of index seeks happening when where clause filters across multiple tables in join using OR

It looks to me like it’s doing a Scan because it may well need rows from T553 if the condition in T1011 holds. On the other hand, if any of the conditions on T553 hold, it’ll need rows from T1011.
So indexes would have to be able to handle finding rows in T553 and then pulling in the relevant rows from T1011, and also finding rows in T1011 and pulling in the relevant rows from T553. And the Query Optimizer figures that it’s probably best to get a good join happening and to filter the results.
To make it better, use a UNION of the two ways through the query. This should help it decide to do it the preferred way and you can optimise each side of the Union as you need.
Something like:
SELECT *, ROW_NUMBER....
FROM
(
SELECT *
FROM T1617
WHERE
  C1402001100 LIKE @P0
  OR C200000001 LIKE @P2
  OR C200000020 LIKE @P3
UNION
SELECT *
FROM T1617
WHERE C260100004 LIKE @P1
) t

Use SELECT CAST(ntextCol as nvarchar(max)), ... for something which can be UNIONed.
One thing to keep in mind is the UNION operator will remove dupes, not only between the two datasets, but within them as well. There are PKs in place here though, based on the objects involved in the seeks. If it did apply here, the answer would be to include something that’s unique in the view on each side of the UNION and then leave it out in the outer query.


error 2013 (hy000) lost connection to mysql server during query while load of mysqldump

If all the other solutions here fail - check your syslog (/var/log/syslog or similar) to see if your server is running out of memory during the query.

Had this issue when innodb_buffer_pool_size was set too close to physical memory without a swapfile configured. MySQL recommends for a database specific server setting innodb_buffer_pool_size at a max of around 80% of physical memory, I had it set to around 90%, the kernel was killing the mysql process. Moved innodb_buffer_pool_size back down to around 80% and that fixed the issue.


why does create index with SET FOREIGN_KEY_CHECKS=0; and SET UNIQUE_CHECKS = 0; take a lot of time?

SET FOREIGN_KEY_CHECKS=0; and SET UNIQUE_CHECKS = 0; are meant for speeding up imports (typically INSERT statements) into tables where foreign keys and unique indexes already exist. They are not meant to speed up the creation of indexes.


Mysql database replication with geo-distributed servers

One solution that could work is a Galera cluster with one node in read-only mode. (See e.g. here for info about geo-distributed Galera cluster.) Galera cluster is a (virtually) synchronous multi-master solution. MariaDB and Percona provide this technology, as does Galera themselves. There is one problem: Since you would be writing to more than one node, there is a higher likelihood of experiencing deadlocks / certification failures, including on commit. (See this article for an explanation.) However, this is something your application could handle if you take care when developing it.

Be sure to review the Galera cluster limitations before you decide on this technology. Some of the most notable requirements are: A Linux OS, it only supports the InnoDB storage engine, and all tables must have an explicit primary key.

MySQL Group Replication is a similar technology, but this wouldn't be suitable for geo-distributed database nodes as in your use-case.


MySQL - query to get length of service in Years, months, days

A possible (not the best) solution to this:

SELECT firstname
     , startdate
     , CASE
           WHEN YEAR(CURDATE()) < YEAR(startdate) THEN 0 #or some such validation
           ELSE YEAR(CURDATE()) - YEAR(startdate)
       END years
     , CASE
           WHEN MONTH(CURDATE()) < MONTH(startdate) THEN MONTH(startdate) - MONTH(CURDATE())
           ELSE MONTH(CURDATE()) - MONTH(startdate)
       END months
     , CASE
           WHEN DAY(CURDATE()) < DAY(startdate) THEN DAY(startdate) - DAY(CURDATE())
           ELSE DAY(CURDATE()) - DAY(startdate)
       END days
FROM users;


Oracle live data updates for frequently updated table: poll or CQN?

Polling isn't uncommon; it will depend on what your application and data latency requirements are. Depending on your infrastructure, it may not be possible to poll more than a few times per minute.
If you must have real-time, or as near as possible, you might be able to use Oracle Advanced Queues to stream the updates.

https://www.oracle.com/technetwork/database/features/data-integration/oracle-aq-tech-wp11-2-191324.pdf
https://docs.oracle.com/database/121/LNCPP/queuing.htm#LNCPP010.

It would be an extra cost, but one other alternative is Oracle GoldenGate:

https://www.oracle.com/middleware/technologies/goldengate.html


ORA-00205 error during the startup of physical standby database

This problem occurs when your auxiliary (the clone target) database instance is started using a static init file. Smarter is to use the more dynamic spfile so the control_files parameter can be changed by the cloning procedure to where it is restored on the target.

I guess the question was: why this happened? ;-)


Make another column in a table equal to the primary key column

I explained the error message in an earlier post 4.5 years ago (Problem with MySQL subquery)
SUGGESITON : Do it as two queries
SELECT id INTO @maxid FROM notes ORDER BY id DESC LIMIT 1;
UPDATE notes SET originalid = @maxid where id = @maxid;

or
SELECT MAX(id) INTO @maxid FROM notes;
UPDATE notes SET originalid = @maxid where id = @maxid;


best way to run sql server management studio on linux

You can't run Sql Server Management Studio on Linux. But there is an alternative that is provided by Microsoft called Azure Data Studio. Not all features are available in Azure Data Studio that are available in Sql Server Management Studio - the main difference is that Azure Data Studio is missing a lot of the wizards that are available in SSMS.

Use Azure Data Studio if you:

Need to run on macOS or Linux
Are connecting to a SQL Server 2019 big data cluster
Spend most of your time editing or executing queries
Need the ability to quickly chart and visualize result sets
Can execute most administrative tasks via the integrated terminal using sqlcmd or Powershell
Have minimal need for wizard experiences
Do not need to do deep administrative configuration

Use SQL Server Management Studio if you:

Spend most of your time on database administration tasks
Are doing deep administrative configuration
Are doing security management, including user management, vulnerability assessment, and configuration of security features
Make use of the Reports for SQL Server Query Store
Need to make use of performance tuning advisors and dashboards
Are doing import/export of DACPACs
Need access to Registered Servers and want to control SQL Server services on Windows

The snippet above is taken from Sql Server Blog and provides a comparison of features.
If you are happy to pay for a tool, then take a look at Aqua Data Studio and SQL Maestro which have quite a lot of features and a wide range of support for other database platforms.