How to create an user with permission to do everything but drop databases?

I assume by everything you mean db_owner rights.  Not Sysadmin rights.
If you can't trust them not to drop a database, they should not have access to most things you can do with Sysadmin.
What you want is described in the answer here https://dba.stackexchange.com/a/163437/21924

So in effect, you can create a user that has all the rights of db_owner except say, managing users and dropping database. But I don't think there's a way to directly deny such privileges, you have to instead add everything but them.
(See https://msdn.microsoft.com/en-GB/library/ms189121.aspx)

Above is what you asked for, the solution most often used is just give the user db_owner.  Take regular backups, with the database in full recovery.  Take T-log backups as frequently as needed for your DR solution.
In the end it doesn't matter if a stupid user deleted the database or if the server crashed. Something happened to the database and you need to restore it. Your DR solution defines how much data the business can afford to loose. As it is probably a business user who has db_owner and deleted the database.  You have done your part as a DBA (assuming your backup and restores work, you should test them regularly)


Cannot execute statement: impossible to write to binary log?

It looks as if your server is misconfigured, or (though somewhat less likely) your code is improperly setting the binlog_format system variable for the current session to STATEMENT (apparently these incorrect modes of operation are, appropriately, more aggressively prevented in 5.6, so I assume you are using something earlier).

# In order for Galera to work correctly binlog format should be ROW
— http://www.percona.com/doc/percona-xtradb-cluster/5.5/manual/bootstrap.html
any binlog_format other than ROW was never supported (also well documented)
— https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1263991

This should be the configuration in all of your nodes in my.cnf.  If your cluster has been running without this, the nodes may not be consistent, though it's hard to imagine it running like this at all, though there were cirumstances in older releases that allowed the server to start with invalid configuration when it should not have done so.


join 3 tables with one to many and one to one relationship

With relationship cardinalities between tables, you should think of the outcome of INNER JOINING those tables as multiplicative per row, in a sense. In a one-to-one relationship you'll always end up with, at most, 1 x 1 record per record in the smaller table. For example if your memory_map table has 10 records and your histogram_read table has 5 records, the most records you could possibly end up with is 5 records.  Those would be whichever 5 records in memory_map that had a related record to the 5 in histogram_read.
In a one-to-many relationship, the above is similarly true except the many table dictates how many rows will be returned per row in the one table. So now the multiplicative relationship is 1 x N where N is the number of related rows in the many table to 1 particular row in the one table. For example, if your histogram_read table has 5 records, and your histogram table has 20, the most records an INNER JOIN could possibly return is 20, if all 20 records related to the 5 records in histogram_read. (Then you'd be getting every row back from histogram in that case.)
So in short, with an INNER JOIN, for a one-to-one relationship, the most records you can end up with is the total number of records in the smaller table between the join. For a one-to-many relationship, the most records would be the total number of records in the larger table.
The last thing to keep in mind is INNER JOIN only returns rows that match between both tables of the join clause, and filters out the rows that don't match. OUTER JOIN like a LEFT OUTER JOIN (LEFT JOIN for short) or RIGHT OUTER JOIN (RIGHT JOIN for short) will always return all rows from the table on that side of the join clause, and will NULL out the columns of the table on the other side of the join clause where there aren't any matches. For example, if your histogram_read table has 5 records and histogram has 20 records, and only 12 of the records in histogram related to the 5 records in histogram_read then with an INNER JOIN between the two tables you would only get 12 records. But with an OUTER JOIN that points to the histogram table, such as FROM histogram_read RIGHT JOIN histogram, will ensure you get all 20 records back from the histogram table, with NULL values in the columns from histogram_read for the 8 rows that didn't match from histogram.
All of that being said, I don't see anything inherently wrong with the query you wrote, other than the INNER JOIN in your subquery possibly filtering rows out of histogram if they don't match histogram_read. You can use all OUTER JOIN clauses to prevent that from happening, and you actually don't even need a subquery. Here's an example query that should essentially produce what you're looking for:
SELECT M.parameter, R.*, H.* -- You shouldn't use *, it's bad practice, anti-pattern, and potentially bad for performance, instead explicitly list only the columns you need
FROM histogram H
LEFT JOIN histogram_read R
    ON H.histogram_read_id = R.id
LEFT JOIN memory_map M
    ON M.memory_map_version = R.memory_map_version
    AND M.product_id_hex = R.product_id_hex

With one-to-many relationships, I personally prefer to start with the many table in my joins, but it doesn't really matter, as long as you use the correct OUTER JOIN. In this case, because the many table is on the left side of the join clause, we need to use a LEFT JOIN. The above query will return all rows from histogram, with the parameter column from memory_map, with NULL values for wherever there wasn't a match on a record between histogram and the rest of the tables.


How to work with multiple Postgres databases in psycopg2

Yes, it is possible to work with multiple databases at the same time but you're looking in the wrong place. psycopg2 is just a library that simplifies accessing and manipulating data coming out of PostgreSQL but it doesn't go far beyond what you can do with psql. What you're looking to do you can solve on the database level by using Foreign Data Wrappers.

This does become more complicated in your schema definition but brings remote tables from host some.other.server database remote_db to appear as though they live on localhost in database local_db. A simple example on how to hook up the wrappers:

CREATE EXTENSION postgres_fdw;
CREATE SERVER some_remote_server
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'some.remote.server', port '5432', dbname 'remote_db');
CREATE USER MAPPING FOR local_user
  SERVER some_remote_server
  OPTIONS (user 'remote_user', password 'remote_user_password');
CREATE FOREIGN TABLE local_table_name (id int, value int)
  SERVER some_remote_server
  OPTIONS ( schema_name 'remote_schema_name', table_name 'remote_table_name');

Now locally you can just run

SELECT * from local_table_name

and the query will get executed against the remote host. Needless to say, this requires connectivity between the two servers.

Similarly, if you really have to, you can create a "remote" server against the localhost and point to a different local database for cross database queries. Feels dirty but it's possible.

As @a_horse_with_no_name mentioned though, this is not very efficient. If you find yourself doing this too frequently, you're not getting the most optimal performance and you'd better have very good reasons for keeping your databases separate at that point.


TokuDB + MariaDB 10: should I use a clustered index

That's a tough question to answer without more schema and workload information, but the key benefit to a clustered secondary index is that it contains not just the indexed columns and all other columns in the table. "Normal" secondary indexes contain the indexed columns and the primary key (which is used to look-up the other columns for your SELECT STATEMENTS via the PK index).

The downside to a secondary clustered index is that all of the data is stored twice. Once for your PK and once for the clustered secondary index. If your data is compressible this may be less of an issue, but a clustered index certainly takes up more space on disk than an unclustered index.

Now for the benefit. If you are doing range scans, or your query needs multiple rows for the same IP address, a clustered secondary index will perform significantly better than an unclustered one. If your database is much larger than your cache then you'll save 1 IO per row returned, which can make a significant difference for your queries.

The insert performance on the table is affected by this choice, but you didn't mention if you have an insert performance issue.


Postgres - how to avoid unnecessary comparisons inside a WHERE with a JOIN?

The plan entry Recheck Cond: indicates what conditions will be used for the recheck, should a recheck be necessary.  This will always be listed, even if no recheck is actually necessary at run time.  (You can tell this because the Recheck Cond shows up even if you do a regular EXPLAIN, not an EXPLAIN ANALYZE, so this is part of the planning process, not the execution process.)

In your situation, all Heap Blocks were exact, meaning no recheck was necessary.

The real explanation for the slowness is probably that the Bitmap Heap Scan needs to read a lot of data from disk in order to find the rows it needs.  You could check this by turning track_io_timing on and running EXPLAIN (ANALYZE, BUFFERS).

If that is indeed the issue, one solution would be to CLUSTER xfiler_document ON xfiler_document_added_on so that the data for a given date range are closer together on disk.

If your data is on a RAID, then increasing effective_io_concurrency could help.


ip address record in sybase ASE auditing database?

The contents of the extrainfo column is populated in two ways:

automatically by the auditing system; the actual contents are pre-defined based on what exactly is being audited (eg, audited logins could include the ip address)
when the sp_addauditrecord proc is called (eg, by a user-defined proc, trigger, batch process)

In order to match an ip address with the various commands you'd have to use the audited login record (that contains the ip address) to obtain the associated spid/suid, then use these values to search follow-on auditing records with the same spid/suid; basically a self-join of the sysaudits table.

Keep in mind that you really don't want to be hitting the sysaudits tables very often with this type of activity as that's not what the tables are designed for.  Instead, you'd want to periodically pull the auditing data into some other database/table/repository (with appropriate indexes) where you could run your queries.


How to solve a mail issue Failed to initialize sqlcmd library with error number -2147467259?

From your example, you are trying to insert the results of your query into a table variable and then trying to use that table variable in the sp_send_mail procedure call. That won't work. sp_send_mail needs to reference a real table and not a temp table either.


SELECT
...
FROM
ORDER BY
LIMIT 5 [OFFSET 10] --Use offset for paging.

Reference: http://dev.mysql.com/doc/refman/5.1/en/select.html


Trying to run a procedure, error says result consist more than one line

I don't understand why does you want to implement this in the SP form, but if, then (a variant with minimal edition)

DELIMITER |;

CREATE PROCEDURE setRelocationFee(IN empID INT(20), OUT Rfee INT(20))
BEGIN
    DECLARE relocation varchar(50) default 0;
    SELECT city INTO relocation
    FROM employees
    LEFT JOIN offices ON employeeNumber = empID;

    SET Rfee = CASE relocation
               WHEN 'San Francisco' THEN '$10000'
               WHEN 'Boston'        THEN '$8000'
               WHEN 'London'        THEN '$20000'
               ELSE                      '$15000'
               END ;
END |;

DELIMITER ;


Migrating a database from one PostgreSQL server to another cleanly

This is a reasonable plan if a planned maintenance of a few minutes up to an hour maybe is no big thing for you.

The database is real small and should be imported within seconds on the new server.

If the downtime is an issue for you, you should consider replicating the server with Slony (http://slony.info/) and then make the replicating slave finally the new master.


It depends on the configuration you have in place.

Oracle uses log shipping (as default) to feed the transactions into the standby database. In order to make sure that ALL transactions are recovered on the standby database, we should make sure that the primary database is running in FORCE LOGGING mode. Switching FORCE LOGGING may cause a difference in performance on the primary database, where FORCE LOGGING mode would be a little slower than without force logging mode.

Just shutting down could - depending on the configuration - bring the primary database to a halt (protected standby database). When the primary and standby databases are on different hardware they should hardly influence each other, other than that the transactions need to be saved and transported to the standby site. If you run in sync mode the primary could be slowed more than when running a-sync which is the default.

The configuration that makes sure that the performance impact on the primary is the least, is the max performance mode, in which the transactions are only shipped when the redo logfile is full. It also gives the least data protection.


How to see urls of incoming SQL Server traffic?

By the time the client connects, it typically doesn't present the hostname it used.  The client resolves the hostname in DNS and connects to the IP(s) that were returned from the DNS lookup.

Kerberos connections will fail if the client uses a hostname that doesn't correspond to an SPN registered for the SQL Server service account.  And NTLM can fail if the server is not configured to accept auth at the hostname the client uses.  But there's no general way to see this.  The configuration changes required to connect with these Auth schemes through a DNS alias are outlined here.

One thing you can do is to add an additional IP address to the server and configure the two DNS entries to resolve to different IP addresses.  That you would be able to monitor either at the network level or in sys.dm_exec_connections.


You could use COALESCE(NullableColumn,'') for any column you don't want to export with NULL values.

That COALESCE will output zero-length strings instead.

Most of the time, however, it is important to know the difference between a zero-length string and a NULL value in exported data.

You're sample query would become:

SELECT getdate() NOW, COALESCE(NULL,'') DUMMY;

Although, I'd really prefer to see it as:

SELECT [Now] = getdate()
    , Dummy = COALESCE(NULL,'');

Putting the column alias at the beginning of the line makes it easier to find columns referenced in the output when doing debugging work down the road.


SQL 2016 - NU/NC Index Missing From sys.partitions


You cannot force that on the server side, because the server cannot control what a client displays.

But yan can do it on the client side: create a .psqlrc file in your home directory that contains something like:

\echo Seien Sie bitte vorsichtig.....

But I assume, given the text of the warning, that you want this bannet only for certain sensitive databases.

For that, you could have  banner table in every database, and the statement in .psqlrc would be like:

\set QUIET on
\pset format unaligned
SELECT * FROM banner;
\pset format aligned
\set QUIET off


Group concat all child ids against parent row using query in Mysql

DB Fiddle: https://www.db-fiddle.com/f/6i2dwPvg4PqTfxyCTQZoho/0
And your query could look like
with recursive myCTE (root_id, id, parent_name, parent_id) as (
    # the most basic level, we need to remember the root id and name
    select id as root_id,
           id,
           name as parent_name,
           parent_id
    from myTable
    # you can control which output you need here or at the end (modifying the `from myCTE`
    where parent_id is null
    # or with that
    # where id = 2

    union all

    # combine that in recursive way
    select mC.root_id,
           mT.id,
           mC.parent_name,
           mT.parent_id
    from myTable mT
    inner join myCTE mC on mT.parent_id = mC.id
)
select root_id, parent_name, group_concat(id)
from myCTE
where id <> root_id
group by root_id, parent_name;

It uses the recursive CTE statements (see https://dev.mysql.com/doc/refman/8.0/en/with.html#common-table-expressions-recursive) which allows to gather such hierarchical rows together. At the end, the GROUP_CONCAT is used to combine together strings by groups.


What are the first necessary actions needed to help secure a database's schema & data?

General guidelines:

Server physical security: behind a locked door, with a very short list of people able to access it (as a point of interest, I myself can't access our server room without being escorted).
Backup security: Make sure your backups are encrypted (if you use physical media for backups, make sure they're stored with a similar level of security to the server).
'sa' login: Paranoia dictates that you disable/remove it, practicality says "that'll break everything" (particularly when it comes to disaster-recovery time). I generally leave it enabled, but with a long, randomly-generated password (either stored with KeePass or a similar solution, or a post-it note inside the server room*). Create another account with the server admin role and use that for your maintenance requirements.
Permissions - in production, no-one except the DBAs and the application logins should have access to the server.

The application logins should not have any rights on the base tables, only execute rights on stored procedures used to access those tables.
Alternatively, if you want to get fancy, group all the stored procedures into schemas then grant execute on the schemas to the application logins. (This may not apply to non-SQL Server/Sybase RDBMSs, not sure if they have schemas with the same level of functionality.)
Ideally, DBAs should have two logins, one with read access and one SA. Monitor the use of the SA login to make sure they're only elevating to it when needed. In practice, your DBAs are probably going to back themselves not to be stupid and consequently will use their SA roles for everything.

Check your stored procedures to make sure they're not vulnerable to SQL injection.
Audit your stored procedures regularly, particularly the ones that issue DML commands (insert, update, delete etc).

(* I'm aware this would probably fail any security audit going, but to my mind if we've let an intruder into the server room that knows to look in the third drawer down for the unlabelled 'sa' password post-it, then we're screwed anyway.)


Identical(?) SQL Server 2005 machines; query takes 2sec on one, 15min on the other

Using a performance troubleshooting methodology like Waits and Queues identify the reason for the high CPU consumption, then appropriate action can be recommended once the bottleneck is identified.


How does this syntax work? {fn CurDate()} or {fn Now()} etc

It's ODBC escape syntax, and the engine knows what its own implementation is, and swaps it out, as you've seen in the execution plan. There are also other things, such as:

SELECT {fn curdate()},
       {ts '2016-05-24 15:19:36'}, -- not vulnerable to SET LANGUAGE!
       {guid 'D08891B4-BC25-4C7C-BAEF-3B756055AC6E'};

See the documentation here, here, here, and most importantly here. But please don't investigate and learn about this syntax; IMHO you should use the native syntax for the most part and pretend you've never heard of this stuff.

I also strongly recommend against the getdate()-1 shorthand, too, particularly if you're going back and updating old code. Be explicit and use DATEADD, since the implicit shorthand does not work with new types. For example, try:

DECLARE @d DATE = GETDATE();
SELECT @d - 1;

Result:

  Msg 206, Level 16, State 2, Line 2
  Operand type clash: date is incompatible with int

While you're in there, may as well add the semi-colons, too if you really want to protect your code 10 years into the future.


VIEW instead of FUNCTION for inserting data

Named parameters should actually be passed using => not = and not :=.

e.g.

select my_function(argument_one => 42, argument_five => 'foobar');

The => is defined by the SQL standard for named parameters, the := was used historically by Postgres, but its use is discouraged in newer versions in favor of the standard compliant =>

Using Named Notation in the manual.


Single sql query to select a record from parent table only if all it's records in child table has values

This is called an "anti-join" (or anti-semijoin). One way to write it in SQL is to use the NOT EXISTS construction:

SELECT t1.*
FROM Table1 AS t1
WHERE NOT EXISTS
      ( SELECT *
        FROM Table2 AS t2
        WHERE t2.FKcolumn = t1.PKcolumn
          AND t2.columnX IS NULL
      )

If - as your comment - you want a parent row (at Table1) not related to any child row (at Table2), to NOT be shown, add this (or join Table2 if you also want columns from the second table shown):

  AND EXISTS
      ( SELECT *
        FROM Table2 AS t2
        WHERE t2.FKcolumn = t1.PKcolumn
      )


I have a column type of jsonb named changes
[["change","is_enabled",[false,true]]]

This is a terrible schema. You're essentially storing an array of arrays. And what you're asking is how to query a jsonb-array, for any elements that are themselves an array that happen to have an array after a string named "is_enabled" which inside the array, has an element of true.
test=# SELECT jsonb_pretty('[["change","is_enabled",[false,true]]]'::jsonb);
     jsonb_pretty
-----------------------
 [                    +
     [                +
         "change",    +
         "is_enabled",+
         [            +
             false,   +
             true     +
         ]            +
     ]                +
 ]
(1 row)

That's going to be extremely ugly.  Nothing about this makes sense. Your JSON schema should just have one object that has {"is_enabled":true}, or something like this {"name":"change","is_enable":true}. As is, you need something very ugly,
You can unnest both JSON array's getting to the inside with relative ease. Then you'll have to mark the row after the is_enabled element in the array,
SELECT *,
  '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
FROM ( VALUES
  ($$[["change","is_enabled",[false,true]]]$$::jsonb)
) AS t(j)
CROSS JOIN LATERAL jsonb_array_elements(j)
  AS jae1(j1)
CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
  AS jae2(j2,o);
                     j                     |                   j1                    |      j2       | o | is_changed
-------------------------------------------+-----------------------------------------+---------------+---+------------

 [["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | "change"      | 1 |
 [["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | "is_enabled"  | 2 | f
 [["change", "is_enabled", [false, true]]] | ["change", "is_enabled", [false, true]] | [false, true] | 3 | t
(3 rows)

From this, it's simple using bool_or, and another select
SELECT j, bool_or(j2 @> 'true'::jsonb)
FROM (
  SELECT j, j2,
   '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
  FROM ( VALUES
    ($$[["change","is_enabled",[false,true]]]$$::jsonb)
  ) AS t(j)
  CROSS JOIN LATERAL jsonb_array_elements(j)
    AS jae1(j1)
  CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
    AS jae2(j2,o)
) AS t
WHERE is_changed = true
GROUP BY j;

We can add more rows to the VALUES expression to test it
SELECT j, bool_or(j2 @> 'true'::jsonb)
FROM (

  SELECT j, j2,
   '"is_enabled"'::jsonb = lag(j2) OVER (PARTITION BY j1 ORDER BY o) AS is_changed
  FROM ( VALUES
    ($$[["change","is_enabled",[false,false]]]$$::jsonb),
    ($$[["change","is_enabled",[true,false]]]$$::jsonb),
    ($$[["change","is_enabled",[true,true]]]$$::jsonb)
  ) AS t(j)
  CROSS JOIN LATERAL jsonb_array_elements(j)
    AS jae1(j1)
  CROSS JOIN LATERAL jsonb_array_elements(j1) WITH ORDINALITY
    AS jae2(j2,o)

) AS t
WHERE is_changed = true
GROUP BY j;

                     j                      | bool_or
--------------------------------------------+---------
 [["change", "is_enabled", [true, false]]]  | t
 [["change", "is_enabled", [true, true]]]   | t
 [["change", "is_enabled", [false, false]]] | f
(3 rows)

If you just want the ones where bool_or IS TRUE use HAVING..
WHERE is_changed = true
GROUP BY j
HAVING bool_or(j2 @> 'true'::jsonb) IS TRUE;


Alternative to updating every row in my table

select t.*, s.sumTSA
from __SalesDetailInfo t
join ( Select EmpName, sumTSA = SUM(TotalSaleAmount)
       FROM __Sales
       GROUP BY EmpName
     ) as s
ON RTRIM(LTRIM(s.EmpName)) = RTRIM(LTRIM(t.EmpName))

You could use that to create a view.


How to change a column type from SET to an ENUM

Technicaly YES, I just did the test :

mysql> CREATE TABLE my_t (my_c SET("one","two"));
Query OK, 0 rows affected (0.44 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("one");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO my_t VALUES ("two");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
| one  |
| one  |
| one  |
| two  |
+------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE my_t MODIFY my_c ENUM("one","two");
Query OK, 4 rows affected (1.13 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
| one  |
| one  |
| one  |
| two  |
+------+
4 rows in set (0.00 sec)

You've mentioned it but it will mess if you have multiple values (replace by an empty set in ENUM):

mysql> CREATE TABLE my_t (my_c SET("one","two"));
Query OK, 0 rows affected (0.15 sec)

mysql> INSERT INTO my_t VALUES ("two,one");
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM my_t;
+---------+
| my_c    |
+---------+
| one,two |
+---------+
1 row in set (0.00 sec)

mysql> ALTER TABLE my_t MODIFY my_c ENUM("one","two");
Query OK, 1 row affected, 1 warning (0.87 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> SELECT * FROM my_t;
+------+
| my_c |
+------+
|      |
+------+
1 row in set (0.00 sec)

Max.


Swapping is a bad symptom of over-use of RAM.
An excessive number of simultaneous connections may lead to swapping.
Very complex queries may lead to swapping (due to temp tables).  Let's see such queries if you have any.
Have you changed any things in my.cnf (or the equivalent in RDS)?  If so let's see if you raised some values too much.
As a quick fix (and if RDS lets you), decrase innodb_buffer_pool_size by a gigabyte.
Sort_merge_passes -  divide by Uptime.  1/second would be "high", but not high enough to be concerned with.
For deeper analysis, see http://mysql.rjweb.org/doc.php/mysql_analysis -- the GLOBAL STATUS and VARIABLES may find some clues; pt-query-digest will focus on the 'worst' queries.
sort_buffer_size = 2M is fine.
"ORDER BY clause not using indexes" - show us the queries, plus SHOW CREATE TABLE.


If I have a full backup that runs from 20:12 to 20:38, when I restore the backup is it restoring to the state at 20:12? 20:38? Some other point?

To start, a full backup cannot be used to do point in time restores. If the backup finished at 20:38 and you restore it, it will restore all committed data uptill 20:38 and will rollback all uncommitted data uptill 20:38. For details about SQL Server full backup read technet article Understanding SQL Server backups. of course a full backup restores complete SQL Server database and to make sure restored database is consistent it also backups up few amount of transaction log so that when restored it can bring database to consistent point.


Identity columns are not truly compressed in Columnstore Indexes in SQL Server 2012 or in SQL Server 2014. It will all truly depend on the workload that you are experiencing. If your workload will include the identity column, then you can very beautifully take advantage of Segment elimination.

From the compression point of view - Columnstore will provide you with better compression than page typically does. Typically. Please test it before advancing to production.

Your biggest problem in SQL Server 2012 will be a very weak implementation of the Batch Mode, and there is nothing you can do about it.


Go for the normalized structure. In your case, that's Alt 1.

Have your tables defined more or less like these:

CREATE TABLE visits
(
    visit_id INTEGER AUTO_INCREMENT PRIMARY KEY,
    visit_datetime DATETIME NOT NULL DEFAULT now(),
    ip VARCHAR(45),     -- This can probably be encoded more efficiently, it is actually a 32bit integer for IPv4, and 128 for IPv6
    cookie_id INTEGER   -- Do we have a cookie for this visitor? If yes, keep it!
    -- Whatever else you need
) ;

CREATE TABLE visits_clicks
(
    visit_id INTEGER NOT NULL REFERENCES visits(visit_id),
    click_datetime DATETIME NOT NULL DEFAULT now(),
    x INTEGER NOT NULL,
    y INTEGER NOT NULL,

    PRIMARY KEY (visit_id, click_datetime)
) ;

You'll end up having data like these:

INSERT INTO visits (ip, visit_datetime)
VALUES
   ('127.0.0.1',   now() - interval 90 second),
   ('192.168.1.1', now() - interval 38 second),
   ('192.168.1.2', now() - interval 37 second),
   ('192.168.1.1', now() - interval 26 second) ;

INSERT INTO visits_clicks
   (visit_id, click_datetime, x, y)
VALUES
   (1, now() - interval 88 second, 1010,  970),
   (1, now() - interval 87 second, 1201,  894),
   (1, now() - interval 86 second, 1123, 1098),
   (1, now() - interval 85 second, 1241, 1020),
   (1, now() - interval 84 second, 1234, 1034),
   (2, now() - interval 36 second, 1000, 1234)
   ;

With this structure, you will be able to perform queries like the following one, which might be very difficult with an unnormalized structure:

-- Find average number of clicks per visit, count, min and max
SELECT
   count(visit_id)  AS visit_count,
   avg(click_count) AS average_clicks,
   min(click_count) AS min_click_count,
   max(click_count) AS max_click_count
FROM
    (SELECT
        visit_id, count(*) AS click_count
    FROM
        visits_clicks
    GROUP BY
        visit_id
    ) AS q

visit_count | average_clicks | min_click_count | max_click_count
----------: | -------------: | --------------: | --------------:
          2 |         3.0000 |               1 |               5

You will find that, except for very specific questions, this structure will let you query virtually anything you can think of, in an easier fashion than with the unnormalized design. And you can do so within the database, without having to move information in and out of it, which makes the process much faster.

When in doubt: normalize. You'll be able later to aggregate or denormalize if you need to. Some very specific scenarios work better with denormalized data. I don't think that's your case

Check the setup at dbfiddle here


DBCC CHECKDB grows tempdb to a huge size - Legacy MS SQL Server 2008 R2 Instance

Actual serious although unusual suggestion: drop all the indexes in the database - or some of them - then "DBCC CHECKDB" has fewer things to check.  Of course you can put them back after...

Alternatively, rebuilding indexes may produce a simpler arrangement of data for CHECKDB to analyse...  this is actually a guess, but you may use fewer pages to store a table, at least.

https://technet.microsoft.com/en-us/library/ms176064(v=sql.105).aspx
describes several WITH options which increase or decrease the workload of CHECKDB, and another option ESTIMATEONLY which causes it do skip any checking but tell you how much space in tempdb is required for checks.  It's implied that this varies when you set other parameters with ESTIMATEONLY  i.e. TABLOCK, PHYSICAL_ONLY.  These do skip some checks.

Most but apparently not all of the checks in CHECKDB can be run as different commands, as described.

https://www.mssqltips.com/sqlservertip/2399/minimize-performance-impact-of-sql-server-dbcc-checkdb/
has  performance-related suggestions including restoring the database backup to a different server to be checked.  This too might not be a perfect check if the restore server is running a later SQL Server version and quietly "fixes" flaws of the original database during restore, so that you don't see them in the copy.


How do you drop an unnamed check constraint in a PostgresQL table?

The constraint has a name, whether you specified it or not.

From psql, \d foo will list all table constraints, along with their automatically assigned names.

You can also find these by querying the catalog tables directly:

SELECT conname
FROM pg_constraint
WHERE
  conrelid = 'foo'::regclass AND
  contype = 'c'

The table name can be schema-qualified if necessary (e.g. 'public.foo'::regclass).

contype = 'c' filters it down to CHECK constraints; the contype values for other constraint types are documented here.


This is an answer which follows the table definitions you posted:

CREATE TRIGGER update_promotion
  AFTER INSERT ON promotion
  FOR EACH ROW
  UPDATE product
      -- Here you are losing the old price!
     SET price = NEW.percentage * price / 100,
   WHERE id_product = NEW.id_produit;

However, the example you posted does not match the column names you gave later. This means you are not posting your actual code, and this makes everybody’s job harder.

Maybe this is only a toy example to learn how to use MySQL triggers. As such, it’s fine. In production, the very idea to change the price in the main product table upon insertion of a new promotion is questionable. A better design would be not to change the product table, add start_date and end_date columns to the promotion table, and read the current prices either from a view which applies the active promotions for the day, or, if you have millions of products and performance becomes an issue, from a temporary table regenerated each day (and possibly replicated to slave servers... you know your scale).


Does 'LGWR' writes to online redo log files strictly in circular fashion?

Yes it does - Online Logfiles cannot be skipped because they are still ACTIVE. This causes a wait event called checkpoint not complete, by the way.


determining user types upon authentication

For the entities and attributes you've described so far and assuming I've understood the domain, I can't see a need for anything more than the tables below. If you there are attributes specific to a Fitter or Admin, add distinct tables for those attributes.

CREATE TABLE UserType
(
    UserTypeId INT
    , UserType NVARCHAR(50)
    , CONSTRAINT PK_UserType PRIMARY KEY (UserTypeId)
)

CREATE TABLE Address
(
    AddressId INT
    , Line1 NVARCHAR(50)
    , Line2 NVARCHAR(50)
    , Line3 NVARCHAR(50)
    , Line4 NVARCHAR(50)
    , Line5 NVARCHAR(50)
    , Postcode NVARCHAR(20)
    , Country NVARCHAR(50)
    , CONSTRAINT PK_Address PRIMARY KEY (AddressId)
)

CREATE TABLE [User]
(
    UserId INT
    , UserTypeId INT
    , UserName NVARCHAR(50)
    , Password NVARCHAR(50)
    , Title NVARCHAR(50)
    , Forename NVARCHAR(50)
    , Surname NVARCHAR(50)
    , CONSTRAINT PK_User PRIMARY KEY (UserId)
    , CONSTRAINT FK_User_UserType FOREIGN KEY (UserTypeId) REFERENCES UserType(UserTypeId)
)

CREATE TABLE Client
(
    ClientId INT
    , Name NVARCHAR(50)
    , AddressId INT
    , CONSTRAINT PK_Client PRIMARY KEY (ClientId)
    , CONSTRAINT FK_Client_Address FOREIGN KEY (AddressId) REFERENCES Address(AddressId)
)

CREATE TABLE ClientUser
(
    ClientId INT
    , UserId INT
    , CONSTRAINT PK_ClientUser PRIMARY KEY (ClientId, UserId)
    , CONSTRAINT FK_ClientUser_Client FOREIGN KEY (ClientId) REFERENCES Client(ClientId)
    , CONSTRAINT FK_ClientUser_User FOREIGN KEY (UserId) REFERENCES [User](UserId)
)


Is there a reason to update statistics manually?

Sure, if your data is changing more frequently than the rate of auto stats (or, say, you are updating < 20% of the rows frequently, like updating statuses or date/time stamps). Or if your table is huge and it is not changing enough to trigger auto stats updates. Or if you have filtered indexes (since the auto stats threshold is still based on the % of change on the whole table, not the % of change of the subset of rows in the filtered index).


One thing that gets lost about sp_recompile is that it works on objects beyond stored procedures. From the docs:

The qualified or unqualified name of a stored procedure, trigger,
table, view, or user-defined function in the current database

If anyone gets a weird idea in their head, they could end up impacting more than just one procedure:

If object is the name of a table or view, all the stored procedures,
triggers, or user-defined functions that reference the table or view
will be recompiled the next time that they are run.

If developers already have the ability to use other flavors of recompile hints, it may be better to let them use those when appropriate, but also educate them on the difference in scope of what gets recompiled, and how they handle parameters:

For instances running at least SQL Server 2008 build 2746 (Service Pack 1 with Cumulative Update 5), using OPTION (RECOMPILE) has another significant advantage over WITH RECOMPILE: Only OPTION (RECOMPILE) enables the Parameter Embedding Optimization.

In general, WITH RECOMPILE should be avoided -- it's a very heavy handed approach. I'm in favor of targeting recompile hints either to temporarily solve a problem, or when the other potential solutions to a problem are unattractive.


Issue Restoring Certificate to SQL Server When Different Service Account Used

You need to fix the NTFS permissions on the certificate. By default SQL, when it creates the backup will set the NTFS permissions so that only the account running the SQL Service can read the certificate backup file.

This is why it works with a single account, and doesn't work with two accounts.


Upgrade Postgres without doing a pg_dumpall

Since 8.4, pg_upgrade is the alternate choice to dump-restore for upgrades between major versions. It is actually linked at the end of the doc page on migration you already mentioned.

As for replaying WAL files from an older version, the doc says we can't:

  In general, log shipping between servers running different major
  PostgreSQL release levels is not possible

(in Log-Shipping Standby Servers).


The 'mysql' database contains users/privileges/passwords. So take the dump of mysql database along with other databases

mysqldump [options] --all-databases > all_databases_dump.sql

mysqldump -u root -p mysql user > user_table_dump.sql

These mysql database tables contain grant information

user: User accounts, global privileges, and other non-privilege columns.

db: Database-level privileges.

tables_priv: Table-level privileges.

columns_priv: Column-level privileges.

procs_priv: Stored procedure and function privileges.

After restoring cross check with

select Host, user, password from user ;

SHOW GRANTS FOR 'user'@'localhost';


How to migrate from MySQL to MariaDB or Percona with minimum downtime?

In order to perform an upgrade with (almost) no downtime, you need to add some extra infrastructure for high availability. The most common way to perform that is with the help of MySQL built-in replication:

Setup a slave with a clone of the data of the original master. It is technically possible to setup a master-slave replication within the same node by creating a new separate instance on the same hardware. However, I would recommend -in a general case- to set it up on a separate node if possible. This is specially true if the server is very busy.
Put your slave down and perform the upgrade process offline, without stress (the master, your original mysql, is serving your application). Then you upgrade that to MySQL/Percona/MariaDB.
Restart your slave an prepare for switchover from the master to the slave (there are several ways to do that, but if it is not a stable thing, it can be done in places like the connector/driver configuration of your application). There are many things to do on this phase, like waiting for slave lag to become 0, validation testing and warming the slave's buffer pool, etc.
Set your master as read-only and wait for all changes to be applied to the slave server (if you have done enough preparations, this should be almost immediately). This is the only part that produces non-availability on the server. This is because the slave is not 100% synchronous with the master. If 1 second of unavailability is too much for you, there are alternatives for synchronous replication (like semi-sync and galera).
Set your slave as the "active server". Congratulations, you are now on production with 5.6
If you want your original slave to be the final production server, you can repeat steps 2-5 for the old master (which should be the new slave now)

There are many things to have into account here: an upgrade from 5.1 to 5.6 (even if you follow upstream MySQL, and with more reason if you change provider) does not guarantee that it will go smoothly. You will suffer configuration changes, query optimiser changes, SQL syntax changes. You may want to do both both load and validation tests. There are many tools for load testing from your own logs (mysqlslap, percona playback), but I would recommend at least pt-upgrade for compatibility testing. You may also want to watch closely the "What's new" MySQL documents to take advantage of the new features and its equivalent for MariaDB/Percona.

Even if you decide to perform the upgrade not using replication (doing a cold restart), it is still a very useful tool to test the new server version beforehand.

Regarding the upgrade itself, 5.1 -> 5.6 itself can be done in binary format (I would recommend transitioning through 5.5 for easier fix of errors), but if you do not have a lot of data I would recommend you alternatively thinking if a logical export/import is feasible to avoid later rebuilds. This is very dependent on your actual setup.

Final words: remember to perform a backup beforehand and execute mysql_upgrade afterwards.

Check a recent article of how GitHub did a datacenter migration doing a similar process.


SQL Alert: 020 - SSPI handshake failure. Tracking the cause?

Usually when you get the SSPI notification (or from the error log ), you will see the IP address from which the app\user tried to connect. The IP may be internal or external.

Just launch the command line and type:

NSlookup IP

(using the IP address from the log). This will give you the hostname from which the connection request came.


How to select unique records in PostgreSQL without using Distinct clause

A query that may get the "desired output" could look something like this:
(as @joanolo had suggested: adding the second and third column to the GROUP BY)

select
  R.house
, R.id rows_
, R.points
from jkrecords R
group by R.house, R.id, R.points
order by R.house, R.id;

See a worked example here.
Apologies to everyone, and to @a_horse_with_no_name in particular, for any confusion I may have caused with my "assumptions".


What is retrieved from disk during a query?

The physical storage for rows is described in the docs in Database Page Layout. The column contents for the same row are all stored in the same disk page, with the notable exception of TOAST'ed contents (too large to fit in a page). Contents are extracted sequentially within each row, as explained:

  To read the data you need to examine each attribute in turn. First
  check whether the field is NULL according to the null bitmap. If it
  is, go to the next. Then make sure you have the right alignment. If
  the field is a fixed width field, then all the bytes are simply
  placed.

In the simplest case (no TOAST'ed columns), postgres will fetch the entire row even if few columns are needed. So in this case, the answer is yes, having more columns may have a clear adverse impact on waster buffer cache, particularly if the column contents are large while still under the TOAST threshold.

Now the TOAST case: when an individual field exceeds ~2kB , the engine stores the field contents into a separate physical table. It also comes into play when the entire row doesn't fit into a page (8kB by default): some of the fields are moved to TOAST storage. Doc says:

  If it's a variable length field (attlen = -1) then it's a bit
  more complicated. All variable-length data types share the common
  header structure struct varlena, which includes the total length of
  the stored value and some flag bits. Depending on the flags, the data
  can be either inline or in a TOAST table; it might be compressed, too

TOAST'ed contents are not fetched when they're not explicitly needed, so their effect on the total number of pages to fetch is small (a few bytes per column). This explains the results in @dezso's answer.

As for writes, each row with all its columns is entirely rewritten on each UPDATE, no matter what columns are changed. So having more columns is obviously more costly for writes.


How to set malloc-lib in mysql 5.7 in ubuntu 18.04 LTS

I had to do a bit more to get this to work.  This worked for me.  First the problem:
Starting in Ubuntu 18.04, Percona / MySQL are configured thru systemd
This means setting malloc-lib= in my.cnf no longer works!  It also no longer works "out of the box" (at least not for me).
So how do we get this to work?

Create an override.conf file to load jemalloc.  This is done using systemd controls.
sudo systemctl edit mysql

This will pop up an editor.  Unless this has been done before, odds are this will be a blank new file. Enter this in:
[Service]
Environment="LD_PRELOAD=/usr/lib/x86_64-linux-gnu/libjemalloc.so.1"

Then sudo systemctl daemon-reload which loads the override.conf

Then sudo systemctl restart mysql which restarts the server

Resources:

The heavy lifting: https://dev.mysql.com/doc/mysql-installation-excerpt/8.0/en/using-systemd.html
Percona blog article: https://www.percona.com/blog/2017/01/03/enabling-and-disabling-jemalloc-on-percona-server/

The instructions on how to verify the jemalloc is actually being used are key.


Skipping chunk X of Y.Z because MySQL chose no index instead of the word_sid_typeindex

The message is both expected and disturbing.
WHY EXPECTED ?
I don't find this message disturbing at all. I would expect pt-table-checksum to bypass chunks since there is no defined uniqueness. Granted, InnoDB will create a row-based index called gen_clust_index when there is no PRIMARY KEY or UNIQUE INDEX. So, chunks read from the table have no rhyme or reason to its order other than order of insertion.
WHY DISTURBING ?
Since you have a replication tag on the question, now this becomes disturbing.
When you have many writes on a master, the slave may write rows in a different order than the master. If the search_index table had a PRIMARY KEY or a UNIQUE INDEX, that would stabilize checksum evaluation. Unfortunately, that cannot be said in the case of a table that has an internally generated index (gen_clust_index) as the only form of uniqueness with an InnoDB table ON BOTH MASTER AND SLAVE.
SOLUTION

Define a PRIMARY KEY for search indexon the Master.
mysqldump search_index from the Master and load it into the Slave


You are importing MSCHEM schema. Please check in source database that one of TABLE/Procedure/Package using a sequence that exist in another schema. If so, don't worry just recreate it. you can find this sequence name in import log also.


Optimal way to record and retrieve a time-dependent value

You could increase performance of your setup, as shown in your question, by changing the dbo.Routes table to:

CREATE TABLE dbo.Routes(
      RouteID int NOT NULL
    , [Type] varchar(50) NOT NULL
    , StartDate datetime NOT NULL
    , CONSTRAINT PK_Routes
        PRIMARY KEY CLUSTERED
        (RouteID, StartDate DESC)
) WITH (DATA_COMPRESSION = PAGE)
ON [PRIMARY];

The key here is we're defining the clustered index, which is the table, on the compound of RouteID and StartDate DESC.  This provides the data in exactly the way that is most efficient for the query you've written.  The caveat emptor here is inserts into the dbo.Routes for an existing route with a new date will cause page splits to occur since we'll be filling rows in descending order of date.  Having said that, with a small number of rows in the Route table, and with the occasional index maintenance, this should not be a big concern.

Instead of doing that, I would consider modifying the dbo.Routes table to include an EndDate column.  This eliminates the need to do a subquery with TOP(1) and ORDER BY ....   Something like:

CREATE TABLE dbo.Routes(
      RouteID int NOT NULL
    , [Type] varchar(50) NOT NULL
    , StartDate datetime NOT NULL
    , EndDate datetime NOT NULL
    , CONSTRAINT PK_Routes
        PRIMARY KEY CLUSTERED
        (RouteID, StartDate ASC)
);

Note the clustered index is now on (RouteID, StartDate ASC).

The query can now use an INNER JOIN, instead of the correlated subquery, and looks like:

SELECT
      t.TRANSIT_DAY
    , t.ROUTE_ID
    ,  NCTD_MODE = r.Type
FROM Transactions t
    INNER JOIN dbo.Routes r ON t.ROUTE_ID = r.RouteID
        AND t.TRANSIT_DAY >= r.StartDate
        AND t.TRANSIT_DAY < r.EndDate
ORDER BY t.TRANSIT_DAY
    , t.ROUTE_ID;

This allows SQL Server to do a simple inner loop join to obtain results.  Granted, if you are returning a large amount of rows, there will be a significant sort required, that will probably spill to tempdb.

Using the MCVE I've shown below, we can compare plans for the the two variants.  The first plan is your original query with the correlated subquery.  The second plan is with the EndDate column included.

The 2nd variant has a plan cost which is about 4 times lower than the 1st variant.  The sort operators in both plans are requesting 108MB of memory and spilling over 9,000 pages to tempdb - however it's fairly unlikely you'll be requesting the entire result set vs getting a single route, or perhaps a date range.  If you add a filter for a single route, there is no large memory grant or spill to tempdb.

What follows is a sample MCVE with 10,000 route rows, and 1,000,000 transaction rows, that can be used to run tests against various designs:

Do this in tempdb to avoid any "accidents" with real tables.

USE tempdb;

Drop the tables if they exist (this works on SQL Server 2016+):

DROP TABLE IF EXISTS dbo.Routes;
DROP TABLE IF EXISTS dbo.Transactions;

Create the dbo.Routes table, with a clustered index on RouteID, StartDate DESC:

CREATE TABLE dbo.Routes(
        RouteID int NOT NULL
    , [Type] varchar(50) NOT NULL
    , StartDate datetime NOT NULL
    , CONSTRAINT PK_Routes
        PRIMARY KEY CLUSTERED
        (RouteID, StartDate DESC)
);

Insert 10,000 route rows:

;WITH src AS (
    SELECT t.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))t(n)
)
, src2 AS (
SELECT RouteID = (s1.n * 1000) + (s2.n * 100) + (s3.n * 10)
    , Type = REPLICATE(CHAR(65 + CONVERT(int, CRYPT_GEN_RANDOM(1) % 26)), 50)
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
    CROSS JOIN src s4
)
INSERT INTO dbo.Routes (RouteID, [Type], StartDate)
SELECT s.RouteID
    , s.Type
    , StartDate = DATEADD(DAY, ROW_NUMBER() OVER (PARTITION BY RouteID ORDER BY s.RouteID) - 1, '1997-01-01T00:00:00')
FROM src2 s

Create the dbo.Transactions, with a clustered index on ROUTE_ID, TRANSIT_DAY.  Building the clustered index like that optimizes queries that filter on both route and day.

CREATE TABLE dbo.Transactions(
     TRANSIT_DAY datetime NOT NULL
    , ROUTE_ID int NOT NULL
    , CONSTRAINT PK_Transactions
        PRIMARY KEY CLUSTERED
        (ROUTE_ID, TRANSIT_DAY)
);

Insert 1,000,000 rows into the dbo.Transactions table:

;WITH src AS (
    SELECT t.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))t(n)
)
INSERT INTO dbo.Transactions (TRANSIT_DAY, ROUTE_ID)
SELECT DATEADD(DAY, CONVERT(int, CRYPT_GEN_RANDOM(1)), '1997-01-01') + DATEADD(MILLISECOND, ABS(CONVERT(int, CRYPT_GEN_RANDOM(4))), '00:00:00')
    , r.RouteID
FROM dbo.Routes r
CROSS JOIN src s1
CROSS JOIN src s2

For a Routes table with an EndDate column that can be used for comparative tests, I used this:

CREATE TABLE dbo.RoutesEndDate(
      RouteID int NOT NULL
    , [Type] varchar(50) NOT NULL
    , StartDate datetime NOT NULL
    , EndDate datetime NOT NULL
    , CONSTRAINT PK_RoutesEndDate
        PRIMARY KEY CLUSTERED
        (RouteID, StartDate ASC)
);

INSERT INTO dbo.RoutesEndDate (RouteID, [Type], StartDate, EndDate)
SELECT r.RouteID
    , R.Type
    , R.StartDate
    , EndDate = COALESCE(LEAD(r.StartDate) OVER (PARTITION BY r.RouteID ORDER BY r.StartDate), GETDATE())
FROM dbo.Routes r

Query both tables for a specific route:

SELECT
      t.TRANSIT_DAY
    , t.ROUTE_ID
    ,  NCTD_MODE = (
        SELECT TOP (1) Type
        FROM Routes r
        WHERE (r.RouteID = t.ROUTE_ID) AND (r.StartDate <= t.TRANSIT_DAY)
        ORDER BY r.StartDate DESC
        )
FROM Transactions t
WHERE t.ROUTE_ID = 750
ORDER BY t.TRANSIT_DAY
    , t.ROUTE_ID;

The plan for the above query:

I/O and time Stats:

Table 'Routes'. Scan count 1000, logical reads 2142, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Transactions'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2 ms,  elapsed time = 2 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

Query for all transactions/routes:

SELECT
      t.TRANSIT_DAY
    , t.ROUTE_ID
    ,  NCTD_MODE = (
        SELECT TOP (1) Type
        FROM Routes r
        WHERE (r.RouteID = t.ROUTE_ID) AND (r.StartDate <= t.TRANSIT_DAY)
        ORDER BY r.StartDate DESC
        )
FROM Transactions t
ORDER BY t.TRANSIT_DAY
    , t.ROUTE_ID;

The plan:

Nasty spill to tempdb for sort operator:

If we modify the clustered index on dbo.Transactions to be (TRANSIT_DAY, ROUTE_ID), and re-run the full query, we see a plan without that ugly sort and spill-to-tempdb:


sp_tables blocking my other statements. Who runs it?

You can set up an extended event session with following code. I tested this and it works (at least the way I was calling).

    CREATE EVENT SESSION [CallSP_tables] ON SERVER ADD EVENT
    sqlserver.sp_statement_completed
   (SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,
    sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,
    sqlserver.plan_handle,sqlserver.query_hash)
    WHERE ( [sqlserver].[like_i_sql_unicode_string]
            ([object_name],N'sp_tables')
        )
    ),
ADD event sqlserver.sql_statement_completed(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.client_pid,sqlserver.database_id,sqlserver.nt_username,sqlserver.plan_handle,sqlserver.query_hash))
ADD TARGET package0.event_file(SET filename=N'C:\callspTables.xel',
        max_file_size=(50),
        max_rollover_files=(100)) WITH (max_memory=4096 kb,
        event_retention_mode=allow_multiple_event_loss,
        max_dispatch_latency=120 seconds,
        max_event_size=0 kb,
        memory_partition_mode=none,
        track_causality=OFF,
        startup_state=ON)
        GO


How can I decide Rebuilding Index with DETAILED or SAMPLED on SQL Server

I would prefer to rebuild the indexes where the fragmentation percent of the indexes is greater than 30% and a Re-org of indexes where fragmentation percent is in between 9% and 30%.

Coming to update stats, I would prefer to do it with Full Scan depending on the size of the databases.

While these are resource intensive database operations, I would prefer to do these operations over the weekends or after business hours.

For Production Environments, I would usually setup jobs and allow them to run over the weekend.

Please go through the below link for maintenance plans(very useful) and customize accordingly to your environment.

https://ola.hallengren.com/


Two tables, Product and Product Notes. Link them by ProductId, primary key of your Product table.


From http://postgresql.1045698.n5.nabble.com/DELETE-and-UPDATE-triggers-on-parent-table-of-partioned-table-not-firing-td5683717.html:

  The DELETE and UPDATE triggers need to be on the child tables.  An operation on a child doesn't fire the triggers of the parent.

As such, the following code must also be executed:

CREATE TRIGGER ${name}_delete BEFORE DELETE ON $name
    FOR EACH ROW EXECUTE PROCEDURE audit_delete();

The audit_delete() function won't work as written in the question: setting the deleted date is not possible using OLD.deleted = current_timestamp. Instead, assuming that the table has a primary key field of id:

CREATE OR REPLACE FUNCTION audit_delete()
  RETURNS trigger AS
$BODY$
BEGIN
  EXECUTE
    'UPDATE ' || TG_TABLE_NAME || ' SET deleted = current_timestamp WHERE id = ' || OLD.id;
  RETURN NULL;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1;


Are there open source RDBMS that support non-backup snapshots?

Arkhipov's extension for PostgreSQL enables Temporal Tables.

https://pgxn.org/dist/temporal_tables/

Here's a decent guide for using it: http://clarkdave.net/2015/02/historical-records-with-postgresql-and-temporal-tables-and-sql-2011/

PostgreSQL is uniquely suited to this task due to the way it implements row updates.


SQL Server 2008 R2 services not coming online

Issue was resolved.. Actually improper build of sql server caused for issue. Check the registry key level for mssql server for what the default data, backup path existing in the host.. Go for same path in the explorer and check whether the database files existing in the same folder.. if not then msagent certificate will be not created in that path.

Eg. Create a sample database in the server without mentioning a physical name then you will get a operating system error saying wrong path.

So after applying the patch also one more msagent certificate will be created showing with version number..  this is the root cause of the issue


As far as I'm aware (up to 9.2), you can not.  They may have implemented that change in 9.3 or 9.4 ... but typically you would use a CTE (WITH) or just the same aggregate function..

SELECT some_agg(x) as y, some_func(some_agg(x)) from z;

I seem to recall that postgres is smart enough to know that it can reuse the some_agg(x) without having to calculate it twice.

EDIT - Adding a CTE example

WITH my_cte AS (
  SELECT
    id,
    some_agg(x) as y
  FROM tab
  GROUP BY id
) SELECT
  id, y, some_func(y)
FROM my_cte;

The easiest way to know for sure if it calculates it more than once would be to run an EXPLAIN on the plan and see.  :)

I entered the following SQL on a local DB that I have.. Below is the SQL and Explain Plan.

explain analyze
select
  product, sum(price) as x, sum(price) as y
from ticketlines
group by product
having sum(price) > 300;

You can see I have the same aggregate used 3 times ... here is the explain output:

HashAggregate  (cost=796.36..796.97 rows=49 width=16) (actual time=4.650..4.658 rows=26 loops=1)
  Filter: (sum(price) > 300::double precision)
  Rows Removed by Filter: 24
  ->  Seq Scan on ticketlines  (cost=0.00..692.68 rows=10368 width=16) (actual time=0.006..1.020 rows=10368 loops=1)
Total runtime: 4.699 ms

It only calculates the aggregate once.


SQL, category tree with multiple parents

Some reflections, a separation of tree[1] structure and content is most likely the way to go. I.e. remove name from catalogcategories2. Now nodes that don't have a parent does not have to exist in this table. I.e. you can make pid not null.

The current foreign key is invalid since the primary key which it references consists of two columns.

A sketch:

CREATE TABLE CATEGORIES
(  ID ... NOT NULL PRIMARY KEY
,  NAME ... NOT NULL
,  <additional attributes> );

CREATE TABLE CATEGORY_DAG -- [1]
(  ID ... NOT NULL
,  PID ... NOT NULL
,    PRIMARY KEY (ID, PID)
,    FOREIGN KEY (ID) REFERENCES CATEGORIES (ID)
,    FOREIGN KEY (PID) REFERENCES CATEGORIES (ID)
);

[1] What you describe is a Direct Acyclic Graph (DAG), not a tree.


Assuming you are using a recent 12.10 Informix version, the online documentation states:

  As indicated in the INSERT statement syntax diagram, not all clauses
  and options of the SELECT statement are available for you to use in a
  query within an INSERT statement. The following SELECT clauses and
  options are not supported in an INSERT statement:

  FIRST and LIMIT
  INTO TEMP, INTO RAW, and INTO STANDARD result table options
  UNION, UNION ALL, INTERSECT, MINUS, and EXCEPT set operators.

So FIRST is not supported in SELECT statements used in an INSERT context. But using FIRST in the query does not give any syntax error, so maybe something else is going on.

-- The setup
CREATE TABLE patnotes
(
     patkey    SERIAL
    , notetype CHAR(4)
    , order_no INTEGER
);

CREATE UNIQUE INDEX patnotes_udx1 ON patnotes(patkey, notetype, order_no);

INSERT INTO patnotes VALUES (5000, 'GEN', 1);
INSERT INTO patnotes VALUES (5000, 'GEN', 2);

CREATE TABLE person
(
      personkey INTEGER
    , patid     CHAR(4)
);

INSERT INTO person VALUES (4999, '4999');
INSERT INTO person VALUES (5000, '5000');
INSERT INTO person VALUES (5001, '5001');

-- The insert query
INSERT INTO patnotes (patkey, notetype, order_no)
SELECT FIRST 1
    p.personkey
    , 'GEN' AS notetype
    , CASE
        WHEN n.order_no IS NULL THEN 1
        ELSE (n.order_no + 1)
      END AS order_no
FROM
    person p
LEFT JOIN
    patnotes n
    ON p.personkey = n.patkey
    AND n.notetype = 'GEN'
WHERE
    p.patid = '5000'
ORDER BY
    n.order_no DESC;

When we run the above INSERT we get:

239: Could not insert new row - duplicate value in a UNIQUE INDEX column (Unique Index:patnotes_udx1).
100: ISAM error:  duplicate value for a record with unique key.

If we remove the UNIQUE constraint and run the INSERT multiple times we keep getting the same value for column order_no .

-- remove the UNIQUE
DROP INDEX patnotes_udx1;

-- Run the insert multiple times and we get
SELECT * FROM patnotes;
patkey notetype    order_no

  5000 GEN                1
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2
  5000 GEN                2

It keeps inserting the same calculated order_no . Seems like a bug. You should open a defect with Informix support.

As a workaround you can use MAX to calculate order_no:

INSERT INTO patnotes (patkey, notetype, order_no)
SELECT
    p.personkey
    , 'GEN' AS notetype
    , (MAX(NVL(n.order_no,0)) + 1) AS order_no
FROM
    person p
LEFT JOIN
    patnotes n
    ON p.personkey = n.patkey
    AND n.notetype = 'GEN'
WHERE
    p.patid = '5000'
GROUP BY
    p.personkey, notetype
;

Please do note that this approach will fail miserably if you have multiple sessions trying to insert new values concurrently. You should use SERIAL or create a SEQUENCE to generate the order_no values.


How to select datas for a particular user between a date range

Don't use functions on indexed columns.

Don't put dates in VARCHAR; use DATE, DATETIME, TIMESTAMP, etc.  MySQL does not understand tz info, as in 2018-07-16T09:48:30+00:00; deal with it before storing into the date column.

Do test ranges this way:

WHERE date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 7 DAY

to test for a week.  Note that you don't need to worry about leap years, etc.  For a single day, do use

WHERE date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 1 DAY

Do not use this (or CAST):

WHERE DATE(date_created) = '2018-08-23'    -- inefficient

because no index can be used.

So, if your query is

WHERE entered_by = '1' AND date_created = '2018-08-23'  -- original

change it to

WHERE entered_by = '1'
  AND date_created >= '2018-08-23'
  AND date_created  < '2018-08-23' + INTERVAL 1 DAY

Note further:  This will work regardless of whether date_created is DATE, DATATIME, or even DATETIME(6).  And it avoids the clumsy 23:59:59 (which won't be adequate for DATETIME(6)).

Also, have this "composite" index:

INDEX(entered_by, date_created)   -- in this order

A further note:  BETWEEN leads to the "midnight bug", wherein you accidentally include an extra midnight.  Note that my code has >= and <, not <=.


SSRS minimal permission to download reports

At the end, just so that I don't need to download the reports for them to edit, I have granted a very selective AD group with the contents manager permission, so that they can do their work.

any better alternative please post an answer or comment, I will review the situation accordingly.


MS Access write conflict XMIN at (2^32)/2 -1

xmin is internal postgreSQL column determines the transaction ID, When Ms Access doing update for a row it comparing the old values of the row in server with the old values of row in its memory to ensure their are no write conflicts.
psqlODBC offers xmin as long (in Access long has max value of (2^32)/2-1 or 2,147,483,647), so when xmin is the server more than (2^32)/2-1 it will be sent to MS Access as (2^32)/2-1. when you edit the row in MS Access, MS Access sent SQL string like this:
UPDATE table1 SET coln=new_valuen WHERE col1=old_value1 AND col2=old_value2 ... AND xmin= 2147483647

but xmin in the server is not 2,147,483,647 so the transaction failed and MS Access show a write conflict error.
so you have some choices to do:

Don't activate xmin column form psqlODBC driver. Uncheck the highlighted option.

Do update transaction by VBA and pure SQL statements.
Dump your database and remove the postgresql server then reinstall it, restore database, so xmin will be resetted to low numbers.


I suspect he's talking about doing the failover manually.
If, as you say, your Data Centre gets totalled, then you're not going to be able to get anything back from your old, Primary server.  It's toast.
That's probably why he leads with:

Unlike other high availability and disaster recovery technologies ...

These other technologies ship logs around much faster so that there's no "trailing edge" to worry about.


What is the minimum required compatibility level to use parallel SELECT INTO?

In SQL Server 2014 parallel DML for the SELECT INTO statement requires compatibility level 120.

110:

  A SELECT INTO statement always creates a single-threaded insert operation.

120:

  A SELECT INTO statement can create a parallel insert operation. When
  inserting a large numbers of rows, the parallel operation can improve
  performance.

Note, that other resources on the web state false values for the compatibility levels. These statements are quoted directly from Microsoft documentation and have been verified experimentally by me.


ERROR: data type text[] has no default operator class for access method "gist"

smlar Extension

I was able to create a gist index on a text[] column using the smlar extension (written by Teodor Sigaev of text search fame)

# git clone git://sigaev.ru/smlar.git
# cd smlar/
# PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1
# PATH=/usr/pgsql-9.6/bin:$PATH make USE_PGXS=1 install
# psql

postgres=# create extension smlar;
postgres=# create index on bar using gist(f _text_sml_ops);


Why is sql_text "select @@trancount" in open but sleeping transaction?

This is a SQL Server Management Studio thing. At least SSMS version 13.0.15500.91. This version of SSMS will count the number of open transactions on your behalf using the same connection as your query window.

Here's how I found out,

I connected to another server running 2012 and also saw SELECT @@TRANCOUNT
Using a 2012 version of SSMS, I connected to a variety of servers and saw BEGIN TRAN
I used profiler and saw that SSMS 2016 executes SELECT @@TRANCOUNT after every execution.
Incidentally, SSMS 2016 seems to execute SELECT SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition before every execution.
BEGIN TRAN turns out to be a bit of a red herring. SSMS 2016 executes these extra queries no matter what gets executed in the query window... even if your batch is just whitespace.
I coded an c# application that uses ado.net and profiled it. It does not execute these extra queries.


Part of the difference can be explained by:

InnoDB stores the table as B-tree inside its Primary key (primary key is clustering index for the table). And B-tree has some storage overhead (pointers on each node and internal nodes - for your row count the tree might have say 3-5 levels). + each secondary index would add some space too but you do not have any. Not sure if it accounts for the entire 1.1GB difference but it will be a big part of it.

Another thing is that the file does not automatically shrink on deletes. Only on optimize/alter which rebuilds the entire table. But it would be reported in data free column of the table status.


If you can setup a linked server to the source database server you can simply do a restore from the last backup device containing a full database backup by finding the last backup file like this:

Declare @Database sysname = 'a'
Declare @BackupFile NVARCHAR(1024)
select @BackupFile =  bf.physical_device_name from
(select MAX(ISNULL(bs.backup_finish_date, 0)) AS backup_date,
            bm.physical_device_name
            FROM SOURCESERVER.master.dbo.sysdatabases AS sd
          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupset AS bs ON sd.name = bs.database_name
          LEFT OUTER JOIN SOURCESERVER.msdb.dbo.backupmediafamily AS bm ON bm.media_set_id = bs.media_set_id
          where sd.name = @database
          GROUP BY
            bm.physical_device_name) bf
Declare @RestoreStatement NVARCHAR(1024) = 'Restore database ['+ @Database +'] from disk = N'''+@BackupFile+''' with replace,recovery'
execute SP_EXECUTESQL @RestoreStatement;

Now if you only have access to the unc path but not the linked server then the @backupfile becomes like this:.

Declare @BackupFile NVARCHAR(1024)
DECLARE @cmdline varchar(2000)
DECLARE @ReturnCode nvarchar(max)
CREATE TABLE #tempTable (cmdShellOutput VARCHAR(500))
SET @cmdline = 'start /B powershell.exe -executionpolicy bypass -NoLogo -command "Get-ChildItem -Path ''\\server\Backup'' -Filter ''*.bak'' | Sort-Object LastWriteTime -Descending | Select-Object -First 1 -ExpandProperty Name"'
INSERT #tempTable
EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline
select @BackupFile = (select top 1 cmdShellOutput from #tempTable)
DROP TABLE #tempTable


Based on the first error message, the .gz backup file seems to be truncated.

This could be confirmed with the command:

gunzip -c my_db_dump.sql.gz >/dev/null

Presumably this will lead to the same error message: unexpected end of file which is the root cause, the other error from postgres being the consequence of the abrupt end of the data stream.

If the .gz file is a copy resulting from a transfer, you want to compare its size against the size of the original, and transfer it again if it's incomplete. If that's the only copy or if the original itself is incomplete, completing the restore will not be possible.


Are you 100% sure that this is SQL Server 2008?  This behavior suggests SQL Server 2000.

The SQL Server 2000 syntax was

EXEC sp_addlogin 'recovery', 'ThePassword';
GO

Try that.  If that works, when you get into the server check the compatibility level of all your databases in addition to running select @@VERSION.


What is the best partition technique for large table with TEXT column?

You can't partition an InnoDB table with a fulltext index, which would make intuitive sense, since it's difficult to envision how fulltext searches on (what would presumably be implemented as) multiple underlying fulltext indexes (one for each partition) would be able to give meaningful relevance and query expansion results.

Full-text search (FTS) and foreign keys are not supported by InnoDB partitioned tables. For more information, see Section 12.9.5, “Full-Text Restrictions” and Section 18.6.2, “Partitioning Limitations Relating to Storage Engines”.
— http://dev.mysql.com/doc/refman/5.6/en/online-ddl-partitioning.html

The limitation applies to all storage engines.
http://dev.mysql.com/doc/refman/5.6/en/fulltext-restrictions.html
http://dev.mysql.com/doc/refman/5.6/en/partitioning-limitations-storage-engines.html


Find the number of where statements that matched in postgresQL

One way:

SELECT id
FROM   products
WHERE  details->'make' = ?
    OR details->'model' = ?
    OR details->'year' = ?
ORDER  BY CASE WHEN details->'make' = ?  THEN 0 ELSE 1 END
        + CASE WHEN details->'model' = ? THEN 0 ELSE 1 END
        + CASE WHEN details->'year' = ?  THEN 0 ELSE 1 END;

It's a bit simpler to invert the logic:
NULL and FALSE results increase the value (and decrease the ranking).


MSSQL and ALTER AUTHORIZATION on table has no affect?

I think the following quote should explain what is going on
"Ownership of schema-contained objects can be transferred to any database-level principal, but the schema owner always retains CONTROL permission on objects within the schema."
https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms189462(v=sql.105)


SQL Server Express 2016 high CPU Usage as soon as I log into Windows

as Aaron pointed above, the problem actually was related to having only 1.5 GB of RAM memory. The server was upgraded to 2 GB or RAM and the problem was solved.


Upgrade to SQL Server 2008 R2 Standard with Mirroring (1 Thread Limit)

Under normal operations, the redo queue is close to zero. What you can do is: track your current redo counters in perfmon (SQLServer:Database Mirroring> Redo Bytes/sec,Redo Queue). With this value you will know how much of your redo log performance will be affected when switching between the SQL editions.

Note that is SQL2008 R2 EE, if you have fewer that five CPUS, SQL will still use only one thread for the redo process. So if you have four or lesser CPUs, SQL will still use a single threaded redo operation.


Yes

ServerB will just replicate all databases to ServerC (ignoring replicate-ignore-db for now). There is no setting that limits ServerC to getting data that exists only on ServerA

That is,  A(master) ->B (slave) is unrelated to B(master) -> C(slave) and A and C have no knowledge or replication dependency on each other


Mimic Standard Edition Functionality on Evaluation/Developer Edition

As of today, you can't. There are things inside the engine like advanced read-ahead that you simply can't turn off.

There was a Connect request for it, and Microsoft turned it down.


Store CURSOR results into Stored Procedure or View

You should be able to wrap your code into a stored procedure that accepts your 'CommonTablePrefix' as an input parameter fairly easily.

CREATE PROCEDURE ReturnCommonTablePrefixResults (@CommonTablePrefix SYSNAME)
AS
BEGIN
    DECLARE @SQL VARCHAR(MAX);

    SET @SQL = '';

    DECLARE TBL_CURS CURSOR
    FOR
    SELECT [Name]
    FROM SYs.tables
    WHERE NAME LIKE @CommonTablePrefix + '%';

    OPEN TBL_CURS;

    DECLARE @TBL_INSTANCE SYSNAME;
    DECLARE @FIRST BIT = 1;

    FETCH TBL_CURS
    INTO @TBL_INSTANCE;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        IF @FIRST = 1
        BEGIN
            SET @FIRST = 0;
        END
        ELSE
        BEGIN
            SET @SQL = @SQL + ' UNION ALL ';
        END

        SET @SQL = @SQL + 'SELECT * FROM ' + @TBL_INSTANCE;

        FETCH TBL_CURS
        INTO @TBL_INSTANCE;
    END

    CLOSE TBL_CURS;

    DEALLOCATE TBL_CURS;

    EXEC (@SQL);
END

Then, you'd simply execute the stored procedure and supply the 'variable' @CommonTablePrefix = 'IncidentResolvedByUserFact_'

EXEC ReturnCommonTablePrefixResults 'IncidentResolvedByUserFact_'


Selecting Records From two tables with many to many relationship

SELECT u.uid, u.login, STRING_AGG(r.code, ',') code
FROM UserTable u
/* LEFT */ JOIN Rights r ON u.uid = r.uid
GROUP BY u.uid, u.login


The solution was to add slave-net-timeout lover like 10 seconds in the ini conf file of the slave and put master-repl=0 that means infinite retryes. So in this case if the connection goes down the slave will notice it after 10 seconds and it will update the db looking in the log file of the master when it comes back online.


Need some clarification of another post's method for restoring database with frm and ibd files

table_name.txt is just the name of the file you're creating to save the CREATE statements in.  By default, it should go into the same folder as the utilities, but if you'd rather it go somewhere else, you can do path_to_where\you_want_it_saved\table_name.txt and it should go there instead.


You can't access the password in the connection string. It's a security measure, regardless of your package protection level.

If you need to use an existing connection, then you need to follow the examples laid out in Todd McDermid's post Use Connections Properly in an SSIS Script Task or in Matt Masson's Accessing OLEDB Connection Managers in a Script

Personal note, I never managed to get Matt's version working but since he was the PM on the SSIS team, I'll assume it's my failed implementation and not his.

An alternative for accessing the password, since this is tagged with 2008, is to use Configuration. Whether it be XML or SQL Server, store the connection string in an external repository and assign the value to an SSIS Variable. Then, apply an Expression to the Connection Manager's ConnectionString property. Within your script task, you'll be able to access the complete connection string which contains the password.


You could run the import in small batches.

TempDB is growing large enough to accommodate running various queries necessary for the import routine.  Running smaller imports would lessen the amount of tempdb required during each run.


It's worth mentioning up front that those two queries might not return the same results.  Three separate queries by ID will always return your three rows.  In the CROSS JOIN approach, if PersonID = 1 doesn't exist, the information about the other two entities won't be returned.  So your application has to be certain that all of these IDs exist, or you might get unexpected results.

As long as your tables each have a unique clustered index (or primary key) on the ID column (or a covering nonclustered index - the point is a seek to the correct row is possible), the performance difference between the two approaches, from a SQL Server perspective, is negligible.
Both approaches will result in 3 seeks against the clustered index.  Neither should go parallel, or require sorting, and the cross join should use nested loops - so no memory grants would be needed in either case.
The three batches approach is pretty obvious:

The CROSS JOIN approach looks like this:

The way this plan operates, each seek operator executes, at most, once:

The seek on PK_House returns one row to the Nested Loops operator
This triggers execution of the seek on PK_Person, which returns one row to the same Nested Loops operator
This "flattened" row is passed to the next Nested Loops operator...
...which triggers execution of the seek on PK_Car, returning one row to that second Nested Loops operator
This single flattened row is returned to the top operator, which satisfies its request for one row, so no more rows are requested

If you want to not write cross joins all over your app (potentially confusing to other developers), you can avoid the overhead of multiple roundtrips by using one of the Entity Framework extensions that provides "future" queries.  These let you queue up multiple queries, and they will all be sent and resolved in one batch when the result of any of them is needed.
One such extension is Entity Framework Plus - query-future.


Index creation leads to large transaction log growth

Referring to MS documentation here. Also Creating a 60GB Index on SQLFool.

What I believe is happening is that when the index is being built with SORT_IN_TEMDB=OFF, the entire index build processed is logged via the DB's transaction log (DB is set to full recovery). (In addition, I expect there to be a high number of page splits due to the scattergun-nature of the GUID column that is being indexed)

When the index is built using SORT_IN_TEMPDB=ON, then most of the transaction log activity is going to be against tempdb which is in SIMPLE recovery mode.


How to delist subset strings if it is a substring of another string

If you want to delete all records except shown then try something similar to

DELETE dataset.table t1
WHERE EXISTS ( SELECT 1
               FROM dataset.table t2
               WHERE t2.word LIKE CONCAT(t1.word, '_%') )

If you want to select only shown record use

SELECT *
FROM dataset.table t1
WHERE NOT EXISTS ( SELECT 1
                   FROM dataset.table t2
                   WHERE t2.word LIKE CONCAT(t1.word, '_%') )


MS SQL 2012 - Need to split records from 4 different fields then query those

Welcome to SQL, you can replace your sample code with this:

DECLARE @ParentBaseId nvarchar(100) = '16-0833'

SELECT * FROM WORK_ORDER
WHERE BASE_ID IN
(
SELECT USER_1 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId
UNION
SELECT USER_2 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId
UNION
SELECT USER_3 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId
UNION
SELECT USER_4 FROM WORK_ORDER WHERE BASE_ID = @ParentBaseId
)

It's not the fanciest or even best way, but is the easiest to start learning with.

By the way, your code won't work if @UDF_1 is null, you need to assign '' to @ALL_UDF before working with it, or add other checks.


How to Combine Insert, Update, and Delete in the same `Instead Of` Trigger, in Oracle db?

From Coding the Trigger Body

  Detecting the DML Operation that Fired a Trigger

  If more than one type of DML operation can fire a trigger (for
  example, ON INSERT OR DELETE OR UPDATE OF emp), the trigger body can
  use the conditional predicates INSERTING, DELETING, and UPDATING to
  check which type of statement fire the trigger.

  Within the code of the trigger body, you can execute blocks of code
  depending on the kind of DML operation that fired the trigger:

IF INSERTING THEN ... END IF;
IF UPDATING THEN ... END IF;
IF DELETING THEN ... END IF;

in your case

...
if inserting then

INSERT INTO SRC_TBL (CFG_NAME, CFG_VALUE)
         VALUES(:NEW.CFG_NAME, :NEW.CFG_VALUE);

elsif updating then

 UPDATE SRC_TBL SET CFG_VALUE = :NEW.CFG_VALUE
  WHERE CFG_NAME = :NEW.CFG_NAME;

elsif deleting then

 ...

END IF;


Here it is for MySQL 5.0
http://downloads.mysql.com/docs/refman-5.0-fr.a4.pdf

It's not completely new, but it might be helpful.


How to model a relationship between Sandwiches and Toppings?

Assuming you have your toppings and sandwiches tables, I'd create a linking table consisting of sandwich_id and topping_id, so that you can JOIN from each sandwich record to its many toppings and vice versa.

Each link represents a particular sandwich having a particular topping.

Hope that makes sense.


Whats the difference between float and numeric in Postgresql?

You are falling victim of implicit type casts. When one of the operands is numeric and another one integer, the integer operand will be cast to numeric and the result will be numeric. Since numeric has a strictly defined precision, attempting to assign to it a value with infinitely many digits after the decimal point (which 100/3 produces), without rounding, will result in truncation. The same logic applies to multiplication, to which one operand now is not 33.333... (with an infinite sequence of "3"s) that you seem to expect, but rather a defined precision numeric 33.333...3 (a finite sequence of "3"s), which naturally produces 99.999...9 -- a finite sequence
When one of the operands is float and another one integer, the integer operand will be cast to float and the result will be float. Since float has an approximate precision, it is equipped to deal with 33.333... and 99.999... differently, which eventually results in an approximate value of 100.
As to

Which one is the appropriate data type for precise numeric values like currency or inventory quantities?

the manual gives you the exact answer you need:

If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

What it does not say is that you should control calculations at each step by using explicit type casting and rounding appropriate for your business rules.


Your column is of type CHAR(6). Your filter is of type VARCHAR (perhaps counter intuitive, but that what the constant literal '200706' is). The rules of Data Type Precedence dictate that the comparison occurs using the type with higher precedence, ie. the VARCHAR type. This is a different type than the partitioning function, therefore you do not get partition elimination.

Try this instead:

select count(*)
from T (nolock)
where Month = CAST('200706' as CHAR(6));

Your plan should get a partitioning elimination filter.


Relation of PAGELATCH waits with PAGEIOLATCH and WRITELOG

While I can explain increase in PAGEIOLATCH and WRITELOG (we've other
  disks - HDD instead of SSD)

I would not come to that conclusion without doing more analysis. Some of the possible cause Paul Randal mentioned in this article.

Here are some possible root causes (not an exhaustive list):

External Windows memory pressure on SQL Server causing the memory
manager to reduce the buffer pool size
Plan cache bloat causing extra memory to be borrowed from the buffer
pool
A query plan doing a table/clustered index scan (instead of an index
seek) because of:

a workload volume increase
a parameter sniffing problem
a required nonclustered index that was dropped or changed
an implicit conversion

Are they anyhow related?

No. Read this article for possible causes and solution.

Knee-Jerk Wait Statistics : PAGELATCH by Paul Randal.

Also read the answer of this question.

High PAGELATCH_* and WRITELOG waits. Are they related?

I suggest you look at your workload and identify what changed between 2008 and 2017.  Try to corollate those changes to the change in your wait-type. The New and Improved Cardinality Estimator was released in SQL Server 2014, which might not produce optimal plan for all your code written for 2008. Keeping in mind hardware change can also play a role.  Once you have all the information you will be able to identify the root cause of changes in wait-type.

The New and Improved Cardinality Estimator in SQL Server 2014


SQL Server does not support FOR EACH ROW clause in trigger.
So, I think that query in a trigger falls down with an error like this

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Maybe replacement of "=" by "IN" will help.


Is copy/rename a good way to improve temp table performance?

If the table has an index, you may want to consider the cluster command instead of re-creating it or using vacuum full. This will:

Have the same effect on dead tuples - it physically re-writes the whole table
Retains any existing indexes
Might improve performance more than just removing dead tuples, depending on whether you will benefit from the clustering too (though of course you could achieve the same with an order by clause when re-creating)


Can this query be simplified? Calculate cumulative length of segments and collapse coordinates into linestring

A combination of a function and a query/view may be another option.  The function fulfils your first requirement: " Calculate each line segment's length ... the portions of lines between vertices." (The function will need exception handling and testing!)

-- -----------------------------------------------------------------------------
--   function: calculate the segment length
-- -----------------------------------------------------------------------------
create or replace function seglength(
  x_ number
, oldx_ number
, y_ number
, oldy_ number
)
return number as
begin
  if oldx_ = 0 or oldy_ = 0 then  -- vertex_index 1, no "previous"/old values
    return 0;
  else
    return round(
        sqrt(
          power( ( x_ - oldx_ ), 2 )
        + power( ( y_ - oldy_) , 2 )
        )
      , 2
    );
  end if;
end seglength;
/

Then, we can use a modified version of your original query, like so:

select
  d.roadid
, 'LINESTRING M ( '
  || listagg( ( round(x,2) || ' '  || round(y,2) || ' '
           || seglength(x, d.old_x, y, d.old_y) ) , ', ' )
     within group ( order by d.vertexindex )
  || ')' linestring
from (
  select
    roadid
  , vertexindex
  , x
  , y
  , case
      when vertexindex = 1 then 0 -- zero instead of NULL
      else ( lag (x,1) over ( partition by roadid order by vertexindex ) )
    end old_x
  , case
      when vertexindex = 1 then 0
      else ( lag (y,1) over ( partition by roadid order by vertexindex ) )
    end old_y
  from rdvx
  ) d
group by d.roadid;

output:

500100  LINESTRING M ( 670113.32 4863724.94 0, 670122.42 4863728.94 9.94, 670259.91 4863776.23 145.39)
507200  LINESTRING M ( 670147.94 4863628.42 0, 670158.74 4863632.98 11.72, 670298.55 4863680.65 147.72)

Note: the last values in the "LINESTRING" are smaller than the ones in your question.  Can it be the case that your original query actually calculates the distance between the vertices 1 and 3?  My understanding is that the "SEGMENT LENGTHS" are supposed to be: distance v1-v1 ie 0, distance v1-v2, distance v2-v3. dbfiddle here

UPDATE

Function:

create or replace function rlength(
  x number
, prev_x number
, y number
, prev_y number
)
return number as
begin
  if prev_x is null or prev_y is null then
    return 0 ;
  else
    return round(
      sqrt(
        power( ( x - prev_x ), 2 )
      + power( ( y - prev_y ), 2 )
      )
    , 2
    );
  end if;
end rlength;
/

Query:

with roads_ as (
  select
    road_id
  , vertex_index
  , round( x, 2 ) x
  , round( y, 2 ) y
  , sum ( rlen ) over ( partition by road_id order by road_id, vertex_index )  clength
  from (
    select
      road_id
    , vertex_index
    , x
    , y
    , rlength(
        x
      , lag( x,1 ) over ( partition by road_id order by vertex_index )
      , y
      , lag( y,1 ) over ( partition by road_id order by vertex_index )
      ) rlen
    from road_vertices
    )
)
select
   road_id
, 'LINESTRING M ( '
  || listagg( x || ' ' || y || ' ' || clength , ', ' )
     within group ( order by vertex_index )
  || ' )' linestring
from roads_
group by road_id;

Test data:

create table road_vertices
(
road_id number,
vertex_index number,
x number,
y number
);

begin
  insert into road_vertices values ('100',1,0,5);
  insert into road_vertices values ('100',2,10,10);
  insert into road_vertices values ('100',3,30,0);
  insert into road_vertices values ('100',4,50,10);
  insert into road_vertices values ('100',5,60,10);
end;
/

Output:

ROAD_ID  LINESTRING
100      LINESTRING M ( 0 5 0, 10 10 11.18, 30 0 33.54, 50 10 55.9, 60 10 65.9 )


PostgreSQL, integer arrays, index for equality

Q: Does PostgreSQL uses a hash function for checking equality of integer
 arrays or does it perform a brute-force algorithm comparing one-by-one
 the elements of the array?

Not according to Array Functions and Operators in the doc:

  Array comparisons compare the array contents element-by-element, using
  the default B-tree comparison function for the element data type

No mention of hashing.

intarray provides other operators but does not replace the equality operator between int[]. The  closest function _int_same() that it exposes is semantically different (the order of elements does not matter) and is implemented as sorting+sequential comparison, not hashing.

Fortunately  implementing fast hash-based search at the SQL level is not hard, and in your case (large arrays, no updates, exact match) it may even be the most effective method.

Steps:

1) choose a hash function. I'd suggest md5 on the text representation of the array:

create function arr_hash(int[]) returns bytea as
$$ select digest($1::text, 'md5');$$
language sql immutable;

The function digest(text,text) is part of the pgcrypto extension. Compared to md5 it has the advantage of producing binary (16 bytes) instead of hexadecimal (32 bytes) for a leaner index.

2) create a functional index:

create index index_name on table_name(arr_hash(col_name));

It will be several orders of magnitude faster than a GIN index for the kind of dataset you have (actually I would worry about the GIN index creation taking a really unreasonable time, but do try it).

3) use it like this:

select 1 from table_name
 where arr_hash(col_name)=arr_hash('{10,20,30,...lot of values}'::int[])
 and   col_name='{10,20,30,...lot of values}'::int[];


automatic transactional replication failover to noce2 with AG failover

During AG failover from one server(POCSQL01) to second server(POCSQL02) , how replication agents gets re-directed.

With the introduction of AG, there were new SP's added to support replication and  replication agents like Snapshot, Log Reader and Merge supports below new SP's :

sp_redirect_publisher
sp_get_redirected_publisher
sp_validate_redirected_publisher
sp_validate_replica_hosts_as_publishers

The interesting one is sp_redirect_publisher

Specifies a redirected publisher for an existing publisher/database pair. If the publisher database belongs to an AlwaysOn Availability Group, the redirected publisher is the availability group listener name associated with the availability group.

If you go into details of how that will work - the log reader agent's default behavior is that it will not process any log records until those records have been hardened across all AG replicas.
sp_redirect_publisher will tell replication that if it is not able to connect to the original publisher, connect to the AG Listener allowing the subscribers to reconnect to the publisher after an AG failover.
Run at the distributor :
Use Distribution
Go

Exec sys.sp_redirect_publisher
       @Original_publisher = 'Originap_Pub_Name',
       @Publisher_db = 'Pub_DB_In_AG',
       @Redirected_Publisher = 'ListenerName';

-- Confirm that the new meta data table has been created

Select *
From MSRedirected_Publishers;

Go

Configure Replication for AlwaysOn Availability Groups has all detailed steps on how you will configure replication and alwaysON.
As always test your layout with all possible scenarios !!


Is there any trick to Resize LVM online that raw by UDEV rule without filesystem?

First find the disk names and paths:

select name, failgroup, path, os_mb, total_mb, free_mb from v$asm_disk;

After you found the ASM generated name for the disk you want to shrink, reduce it in ASM to the desired size (I will assume it is called DATA1_0002:

alter diskgroup data1 resize disk DATA1_0002 size 90G;

This starts a rebalance, wait until the rebalance completes, you can view its progress as:

select * from v$asm_operation;

After the rebalance, run this query again:

select name, failgroup, path, os_mb, total_mb, free_mb from v$asm_disk;

There you should see the original 184320 OS_MB, and 92160 TOTAL_MB for the disks you have just shrinked. Next use lvreduce to shrink the LV:

lvreduce -L 90G vg_db/Lv_OraD2

Make sure you do not reduce the size of LV below the ASM disk size TOTAL_MB.

Once it is done, increase the other LV:

lvextend -L 90G g_db/Lv_OraD1

Go back to ASM, run this again:

select name, failgroup, path, os_mb, total_mb, free_mb from v$asm_disk;

Here the OS_MB for the first disk should increase to 92160, but TOTAL_MB still remain 2048.

The final step is to increase the first disk size in ASM:

alter diskgroup data1 resize all;

The above will resize all ASM disks in the diskgroup up to OS_MB. This triggers another short rebalance, and when that finished, you should see 92160 for OS_MB and TOTAL_MB for both disks.


Adding where clause in LEFT OUTER JOIN giving unexpected result

That's because you violated the rule, that every column in the select clause has to be either in the group by clause or have an aggregate function applied to it.

When you don't follow this rule, a random for every group is chosen to display. The group by "collapses" the rows for each group and you have to specify which row to display with min(), max() or whatever aggregate function works for you.

MySQL is to my knowledge the only RDBMS that allows violating this rule, but you can turn this off by setting the sql mode only_full_group_by.

read more about it here


SUM rows FROM a table only if one row have some match criteria with the rest of the rows

Is this close to what you expect?

SELECT a.id_player, SUM(b.goals) as accGoals, SUM(b.fouls) as accFouls
FROM boxscore as a
JOIN boxscore as b
    USING(id_team, id_boxscore)
WHERE a.id_player = 1
GROUP BY a.id_player
;

I.e. sum all goals, fouls from boxscore, where id_team and id_boxscore is the same as player 1


Is it advantageous to scale out using multiple SQL Server instances on the same server?

No, no and again no. One instance, one server, Always. The reasons have to do with the SQL Server memory manager and CPU scheduler architecture, it really works best if it has the whole box to itself and nothing else runs on the box. 'Partitioning' of resources (max server memory, affinity mask) solves some problems and introduces more.

If you want to consolidate, consolidate in one instance.

If you must keep separate instances then a far far far better option is Hyper-V and partition VMs. VMs have a much tighter control of resources and offer many other advantages. You need to be able to expose IO paths directly to the VM (no DBs on VHDs).


Checking records consistency after mongorestore --noobjcheck

You can run a --repairDatabase for that node:

mongod --repair --repairpath <repair_path>

Keep in mind that the repair path should have at least as your data size free disk space. (if you have 100GB, the repair path should have at least 100GB free also.
That should take care of problems of that sort.

Also, in the future if anything of some sort happen again and you need to run mongodump, you can run the mongodump --repair.
This will ensure that any corrupted data will not be dumped.

Hope this helps