Finding same values and updating rankings

This will display the rank you requested

SET @prev = 0;
SET @rank = 0;
SELECT name,tu_total total,rnk rank
FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
FROM (SELECT name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AA) AA;

If TOPUSER has an id field, here is the update of the rank

SET @prev = 0;
SET @rank = 0;
UPDATE
(SELECT id,name,tu_total total,rnk rank
FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
FROM (SELECT id,name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AAA) AA) A
INNER JOIN TOPUSER B USING (id)
SET B.tu_rank = A.rank;

SAMPLE DATA

mysql> DROP DATABASE IF EXISTS nate;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE nate;
Query OK, 1 row affected (0.00 sec)

mysql> USE nate
Database changed
mysql> CREATE TABLE TOPUSER
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     name VARCHAR(20),
    ->     tu_total INT NOT NULL,
    ->     tu_rank INT DEFAULT 1000,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO topuser (name,tu_total) VALUES
    -> ('Mark',3),('Bill',3),('Randy',1),('Sammy',0),('Max',0),('Jane',0);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TOPUSER;
+----+-------+----------+---------+
| id | name  | tu_total | tu_rank |
+----+-------+----------+---------+
|  1 | Mark  |        3 |    1000 |
|  2 | Bill  |        3 |    1000 |
|  3 | Randy |        1 |    1000 |
|  4 | Sammy |        0 |    1000 |
|  5 | Max   |        0 |    1000 |
|  6 | Jane  |        0 |    1000 |
+----+-------+----------+---------+
6 rows in set (0.00 sec)

mysql>

UPDATE EXECUTED

mysql> SET @prev = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @rank = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE
    -> (SELECT id,name,tu_total total,rnk rank
    -> FROM (SELECT *,(@rank:=@rank+IF(@prev=tu_total,0,1)) rnk,(@prev:=tu_total) prev
    -> FROM (SELECT id,name,tu_total FROM TOPUSER ORDER BY tu_total DESC) AAA) AA) A
    -> INNER JOIN TOPUSER B USING (id)
    -> SET B.tu_rank = A.rank;
Query OK, 6 rows affected (0.02 sec)
Rows matched: 6  Changed: 6  Warnings: 0

mysql> SELECT * FROM TOPUSER;
+----+-------+----------+---------+
| id | name  | tu_total | tu_rank |
+----+-------+----------+---------+
|  1 | Mark  |        3 |       1 |
|  2 | Bill  |        3 |       1 |
|  3 | Randy |        1 |       2 |
|  4 | Sammy |        0 |       3 |
|  5 | Max   |        0 |       3 |
|  6 | Jane  |        0 |       3 |
+----+-------+----------+---------+
6 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


How can I find duplicate key violations in a different collation?

A query like this did the trick:

select date, thing_id, email_domain collate utf8_unicode_ci
from my_table
where date = '2013-07-15'
and thing_id = 119
and email_domain collate utf8_unicode_ci = 'comcast.net';


Warning message: invalid value for parameter "default_tablespace" in postgres

It seems you're confusing schemas and tablespaces. A schema defines a namespace whereas a tablespace points to a path on disk where data files are stored.
SET default_tablespace = name refers to a tablespace that would have been created with CREATE TABLESPACE.
A default schema can be assigned to a user through search_path, see Schemas in the documentation.


How to format the output of SQL queries on SQLCMD for SQL Server 2017 on RHEL Linux 7.3?

The sqlcmd utility is handy for executing T-SQL scripts via command-line, such as in automation scenarios, but is limited in its output formatting capabilities and interactive query editor.

Consider using mssql-cli instead for interactive terminal queries. This open source tool provides pagination, handles wide results well, includes IntelliSense ,and allows one to use emacs or vi mode. mssql-cli runs on Linux, Windows, and Mac.

I'll add that Azure Data Studio is a cross-platform alternative to SSMS when a full desktop experience is desired. It does not include all features available in SSMS but does include other features and provides greater extensibility.


This question concerns PL/pgSQL exclusively, where := is the assignment operator. It has no place in pure SQL.

Using = instead of := in PL/pgSQL is an undocumented legacy feature, which should not be used.
:= is the documented assignment operator in PL/pgSQL..
More details about that in this related question on SO.

Update: Since Postgres 9.4 = is also documented as plpgsql assignement operator.

I have never seen = performing faster than :=. They should be identical. I fact, I tested your exact code in Postgres 9.1 and 9.2 and found no difference in performance whatsoever.
You may be mis-interpreting caching effects. Always run EXPLAIN ANALYZE a couple of times to populate the cache and get comparable results.

Finally, you could replace your function with this much simpler and faster one:

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no varchar)
  RETURNS varchar AS
$BODY$
BEGIN
   RETURN (
      SELECT firstwscomplete || ', ' || secondwscomplete
      FROM   myschema.mytable
      WHERE  respkgconfirmid = reservation_no
      );
END
$BODY$ LANGUAGE plpgsql;

Or even use a plain SQL function:

CREATE OR REPLACE FUNCTION myschema.fn_get_res_no(reservation_no varchar)
  RETURNS varchar AS
$BODY$
   SELECT firstwscomplete || ', ' || secondwscomplete
   FROM   myschema.mytable
   WHERE  respkgconfirmid = reservation_no);
$BODY$
  LANGUAGE sql;

Assignments in PL/pgSQL

It is comparatively expensive to make assignments in PL/pgSQL, which is a very simple Ada-like procedural language. Every assignment is executed with a (very simple) SELECT command, which is more expensive than in other fully fledged procedural languages.

PL/pgSQL excels as glue for SQL commands, when you need some procedural elements.


It seems that I missed the obvious part - adding the replica server should be done on Server A, not the other way around.

What I did was:

Start a clean MongoDB installation on server B with replSet option
On Server A I did rs.add('server-b:27017')
On Server A I controlled the addition of the replica with rs.status()

And it works.


Show data point values in the legend section

That would be really fancy, but I don't think it's possible.  You may be able to constrain the width of your bars of the chart and the width of your columns in your table as well as the placement of both to "fake it" though.


Power BI report server: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' when trying to view a report

Not familiar with virtual service accounts, but this is often related to Kerberos authentication.
Try using Microsoft® Kerberos Configuration Manager for SQL Server® to see if it can generate the SPNs for you.
It's a rather easy to use tool from Microsoft which will detect your SPN settings, and create scripts you can execute to fix your issues.


Insert multiple values in a text field using a "delimiter"?

Is this a bad idea?

Almost certainly. If code only ever treats that column as single un-parsed continuous string then it may be OK1. As soon as you want to pull one of the three values it is definitely a bad idea. Even if the parsing happens in the application it is additional complexity that need not be there.

  Should I use 3 different fields?

Yes.

  Does 3 text fields really affect database performance?

Not in any way you will ever notice. Depending on your particular DB software those "text" columns may be treated just like any other data type. Also you must consider how optimized your code to handle this will be compared to that written by a room full of PhD's who built the DBMS and do just this for a living.

1 Even then I'd say have separate columns.


Should I change my old datetime columns from SQL Server 2005 to just date datatype for SQL Server 2012

In SQL Server 2012, date takes 3 bytes while datetime takes 8 bytes.
If your table has millions of millions of rows, then yes, then the IO and logical page size should be reduced and your performance will be quicker.

First though i'd suggest to try dumping the table into a testing table, convert the column, apply your indexes and benchmark using real life workloads against this table using the original as a baseline.


Database development/admin - What exactly should I be trying to learn?

For development you should learn:

SQL including optimizations
DDL (table creations etc.)
plpgsql, PL/SQL, T-SQL (native database language to write procedures and functions)
good to know C, java, scripting languages
jdbc how it works
storage - how db store data and which solutions are faster then another
...

For administration
all above, scripting languages is a must
7. everything about operation system that you will work with
8. storage maintain
9. a lot of measure possibilities how server works (monitoring)
10. installations, upgrades - OS and db system
11. archive possibilities
12 high availability possiblities
13.....

and for both, a lot more things

About tools you have to know how to work with console very good in case that tools cannot connect at current time to db but you can connect to server itself.
Other tools are usually database system specific.


delete rows in 3 tables with on delete cascade

It seems you want these two statements inside a transaction:

DELETE FROM t2
WHERE EXISTS
      ( SELECT 1
        FROM t3
        WHERE t3.id2 = t2.id2
          AND t3.id1 = $id1
      ) ;

DELETE FROM t1
WHERE t1.id1 = $id1 ;

The cascading delete effects will take care of the rest. Not sure though what locking or isolation level will be best for this operation.

Note that if you specify $id1 = 1, the above will delete all rows from t3 that have id1 = 1 and some rows that do not. If for example, you have 3 rows in t3 with (1,5), (1,6), (1,7), the operation will delete them and also delete all rows that have id2=5 or id2=6 or id2=7. So rows with (4,5), (8,5), (28,5), (2,6), (6,6), (40,7) will also be deleted due to the cascading effects, when the three rows of t2 (with id2 5,6,7 are deleted).


You can use Extended Events to track the processing history of a Tabular model.  The ProgressReportBegin and ProgressReportEnd events report processing information, per the documentation.  SSAS extended events are somewhat different than those in SQL Server.  After creation, an extended event is active.  To stop the event it must be deleted.  An extended event can be created through an XMLA request in SSMS using the XMLA query editor.  The following creates an extended event using the ProgressReportEnd event.  Multiple events can be included in single event session.  For instance, you could create a session with the both the ProgressReportBegin and ProgressReportEnd events.
 An XMLA command to delete this event is below as well.  There are several ways to access the results which the target property controls.  A couple options are viewing the results in SSMS (event_stream) and writing these to a file (event_file).
 In this example, the results are written to a local file using the event_file.  The contents of this file (processing activity) can be viewed in SSMS.  To do this right-click the file, select Open With then choose SSMS.  To delete an event use the ID of when the event was created as the TraceID in the delete request.

Create Extended Event:

<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <ObjectDefinition>
    <Trace>
      <ID>XE_Process</ID>
      <Name>XE_Process</Name>
      <XEvent xmlns="http://schemas.microsoft.com/analysisservices/2011/engine/300/300">
        <event_session name="XE_Process" dispatchLatency="0" maxEventSize="0" maxMemory="4" memoryPartition="none" eventRetentionMode="AllowSingleEventLoss" trackCausality="true" xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
          <event package="AS" name="ProgressReportEnd" />
          <target package="package0" name="event_file">
            <parameter name="filename" value="C:\Test\XE_Process.xel" />
            <parameter name="max_file_size" value="4096" />
            <parameter name="max_rollover_files" value="1" />
            <parameter name="increment" value="1024" />
          </target>
        </event_session>
      </XEvent>
    </Trace>
  </ObjectDefinition>
</Create>

Delete Extended Event:

<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <TraceID>XE_Process</TraceID>
  </Object>
</Delete>


I have not tested it, but I would think the filestream file should be included in the size.

  For example, you can use all backup and recovery models with FILESTREAM data, and the FILESTREAM data is backed up with the structured data in the database.

Source

In addition, it sounds like you have emtpy pages.

  Note that the backup size does not include empty pages, but when you actually perform the restore, the data and log files will be over 200 GB, because it has to restore exactly what the source system had (including a 200+ GB log file, regardless of how full it was).

Your example may not be 200+GB, but it was in the case for this person when their backup was pretty small.

Source


The state of the MSSQL Options set during query after it ends

If you execute an explicit ad-hoc SET XACT_ABORT ON, the setting remains in effect for the duration of the current session. Other session settings will remain unchanged.

In the case of SET XACT_ABORT within a module (e.g. stored procedure or trigger), the setting is effective only within the scope of module execution and reverts to the original session setting afterwards.

If the application doesn't explicitly execute SET XACT_ABORT ON or SET XACT_ABORT OFF, the server user options configuration value may specify SET XACT_ABORT ON as the server-wide default by turning on bit 16384. The default configuration value is 0.

The SET Statements documentation describes general behavior of SET statements. Other settings (not including XACT_ABORT) can be turned ON (but not OFF) at the database level to determine default session settings for that session database context.


How to shrink space on table with a function-based index?

One idea is to use partition exchange. That should let you shrink the space needed one partition at a time. I can't test this out myself so please test carefully first in a dev environment. There are at least two different ways to do this.

Method 1:

Create a new table with the exact same columns and indexes as the old table. The new table should have one partition.
Copy all of the data from the partition to shrink to the new table. You can use parallel DML to speed this up. Since you're writing all of the data again there should be far less wasted space in the new copy.
If you aren't already using compression on the big table you could apply it to the new table before swapping the partitions.
Use ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... to swap the partitions.
Truncate the new table.
Repeat as necessary.

Method 2:

Create a new table with the exact same columns and indexes as the old table. The new table should have one partition.
Use ALTER TABLE ... EXCHANGE PARTITION ... WITH TABLE ... to move one partition to the new table. This should work even for partitions with function-based indexes according to Partition Exchange and Database VLDB and Partitioning Guide. Since your indexes are local it shouldn't invalidate indexes on your big table. If you specify the INCLUDING INDEXES option then indexes should be all valid on the new table as well.
Reduce the space needed by the new table. You may just want to use Oracle's MOVE functionality. You can try dropping the function-based index, issuing a SHRINK SPACE command, and recreating the index, but recreating the index could result in a lot of unnecessary free space in the new table.
If you aren't already using compression on the big table you could apply it to the new table before moving back the partition.
Partition exchange with the old table.
Repeat as necessary.

I personally like method 1 better. It gives you increased availability on the big table and you should get more predictable results with less work.

One thing to watch out for is there was some kind of issue with statistics at one point with exchanging partitions with function-based indexes. You can find more information about it here.


Postgres : Select items for an interval from the max date in store

Editing your last query to the solution:

select *
from items_prices
where date_item >= ( SELECT MAX(date_item) - interval '30 day'
                     FROM items_prices
                     where item_id = 3232 )
  and item_id = 3232;

The subquery can be moved to CTE:

WITH cte AS ( SELECT MAX(date_item) - interval '30 day' lastday
              FROM items_prices
              where item_id = 3232 )
select items_prices.*
from items_prices, cte
where items_prices.date_item >= cte.lastday
  and items_prices.item_id = 3232;

or (to select last records for more than one item_id, maybe all)

WITH cte AS ( SELECT item_id, MAX(date_item) - interval '30 day' lastday
              FROM items_prices )
select items_prices.*
from items_prices, cte
where items_prices.date_item >= cte.lastday
  and items_prices.item_id = cte.item_id
--  and items_prices.item_id = (one item_id value, for example, 3232)
--  and items_prices.item_id IN (item_id list, for example, 3232, 3233)
--  none for all item_id
;


How to estimate the storage needed for a database?

The following would be an estimate that assumes full pages and doesn't take into consideration any database overhead (page header, etc.) or compression.  It would be simple math

5,000,000kb / 1024 = 4882.8125mb

4882.8125mb / 1024 = 4.76837158203125gb

But, I agree with other comments - you should create a test database and see how close the numbers match up.


How to use a relational database to store all users who belong to a group, and all groups which a user belongs to?

You want three tables for this:

Users with a row per user and whatever details you need
Groups with a row per group and whatever details you need
UsersGroups with a unique combination of UserId, GroupId that only keeps track of relationships

This will let you have as many combinations as you like.


Should I Avoid Repeated References to Aggregate Columns in a View?

The aggregate will only be calculated once, so you're ok as you are. That said, I tend to use a subquery (as per @Aaron's comment) but purely for readability.


Mysql preventing truncate and drop table statements for a specific table

Consider putting things like that is a separate database.  Then GRANT users access to their database(s) with 'full' permissions (or whatever) and GRANT very limited access to this db:

GRANT SELECT ON Specialdb.* TO ...

If you find you need to let them do something less safe, create a Stored Procedure with security=Definer.  The definer could be root and you can do whatever you want.  However, since you control the proc, you control what things they are allowed to do.


Why does my query return inconsistent amount of rows?

Looks like I had a divide by zero error. My database tool was not reporting the error. I was able to reproduce on fiddle. http://www.sqlfiddle.com/#!18/e2ff2/1

Update:

The bug is now fixed.
https://valentina-db.com/bt/view.php?id=8516


Homebrew MariaDB Server Start Error with 'mysql.server start'

I can reproduce your issue. Don't use mysql.server start in macOS.

If you installed MariaDB with homebrew, use brew services to start and stop the database. It takes only a second and doesn't write strange error to stdout.

brew services stop mariadb
Stopping `mariadb`... (might take a while)
==> Successfully stopped `mariadb` (label: homebrew.mxcl.mariadb)

brew services start mariadb
==> Successfully started `mariadb` (label: homebrew.mxcl.mariadb)

overview of services in homebrew:

brew services list
Name     Status  User     Plist
logstash stopped
mariadb  started Kim /Users/Kim/Library/LaunchAgents/homebrew.mxcl.mariadb.plist
mysql    stopped

general help

brew services --help
brew services command:
    Integrates Homebrew formulae with macOS' launchctl manager.

    [sudo] brew services list
    List all running services for the current user (or root)

    [sudo] brew services run formula|--all
    Run the service formula without starting at login (or boot).

    [sudo] brew services start formula|--all
    Start the service formula immediately and register it to launch at login (or boot).

    [sudo] brew services stop formula|--all
    Stop the service formula immediately and unregister it from launching at login (or boot).

    [sudo] brew services restart formula|--all
    Stop (if necessary) and start the service immediately and register it to launch at login (or boot).

    [sudo] brew services cleanup
    Remove all unused services.

    If sudo is passed, operate on /Library/LaunchDaemons (started at boot).
    Otherwise, operate on ~/Library/LaunchAgents (started at login).


I got an answer after writing a question, but i want to share it to community.

Just set up Slow log and set SET GLOBAL long_query_time = 0.

You will see all sql queries with durations in slow log file.


Does software exist for transforming data from one DB schema to another?

You'll need to use a multi-database tool - I would recommend SQuirreL SQL - it's a Java based tool that connects through JDBC. If you want to migrate to Oracle, you could also consider SQL Developer (which IIRC can connect to MS SQL through JDBC also).

As @ConcernedOfTunbridgeWells said in his comment, you can't get a "push-button" migration of data - you have to explicitly tell the different systems from what column they're to take data and where to put it and join the tables back and forth. If it could be done automatically, there'd be no need for programmers or DBAs :-).

I've used the script below for PostgreSQL to solve your problem. For simplicity, I haven't put in PRIMARY KEYs, FOREIGN KEYs or INDEXes which may (probably) help with performance.

1st I created the employee table and put a small amount of sample data into it.

CREATE TABLE employee
(
  id serial,
  lname VARCHAR(25),
  city VARCHAR(25)
 );

INSERT INTO employee (lname, city) VALUES('Lino', 'Dublin');
INSERT INTO employee (lname, city) VALUES('Mary', 'Cork');
INSERT INTO employee (lname, city) VALUES('Bill', 'Galway');
INSERT INTO employee (lname, city) VALUES('Fred', 'Dublin');

Below is not strictly necessary - but it's always a good idea to "eyeball" your data, particularly when testing!

 SELECT * FROM employee; <--- result snipped for brevity.

Create the new table city.

CREATE TABLE city
(
  id serial,
  city_name VARCHAR(25)
);

Now, insert into this new city table, the DISTINCT values from the city field in your employee table.

INSERT INTO city (city_name)
SELECT DISTINCT city
FROM employee;

And again, to double-check.

SELECT * FROM city;  <---again, snipped.

Add a column city_id to the employee table.

ALTER TABLE employee ADD COLUMN city_id INTEGER; <-- syntax may vary according to RDBMS.

You can select again from the table to double-check (step skipped for brevity).

Next, you have to update the employee table with the corresponding id value that you've created in the new city table. The syntax for this step may vary according to your RDBMS - I found this post helpful.

UPDATE employee
SET city_id = city.id
FROM city
WHERE city.city_name = employee.city;

SELECT * FROM employee; <--- snipped for brevity.

Drop the city column from the employee table. Again, step may vary.

ALTER TABLE employee DROP COLUMN city;

SELECT * FROM employee;

Result - et voila!

id;      lname     city_id
 3;      Bill            1
 2;      Mary            2
 4;      Fred            3
 1;      Lino            3


You need to create a sub-query with distinct values and then join it to your main table and update, like this:

You will need a temporary numbers table:

CREATE TABLE numbers (
  n INT PRIMARY KEY);

INSERT INTO numbers VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

UPDATE users u
JOIN (
     SELECT
     ID, GROUP_CONCAT(pseudo SEPARATOR ';') pseudo
     FROM(
     SELECT DISTINCT
      users.id,
      lower(SUBSTRING_INDEX(SUBSTRING_INDEX(users.pseudo, ';', numbers.n), ';', -1)) pseudo
    FROM
      numbers INNER JOIN users
      ON CHAR_LENGTH(users.pseudo)
         -CHAR_LENGTH(REPLACE(users.pseudo, ';', ''))>numbers.n-1
    ORDER BY
      id, n) u
    GROUP BY ID
     ) du
    ON u.id = du.id
SET u.pseudo = CONCAT_WS(';', du.pseudo, 'NEWS_VALUE')
WHERE u.id = '1';

SQL Fiddle that shows how to split using numbers table


How can I filter databases by owner using psql?

For things like this, you'll want to query pg_database directly. The owner field is a reference to pg_catalog.pg_authid.oid, but this is a superuser-only accessible table. There's a view, pg_catalog.pg_roles that exposes the id as the oid attribute, so you can:

select
  datname,
  rolname AS dbowner
from pg_database d
     inner join pg_roles r on (d.datdba = r.oid);


Converting a Crystal Report Formula to a CASE expression

It looks as though "order_no" is not used a reference for the CASE expression unlike the formula. With making some minor tweaks and replacing my "+" in my sub CASE with "/" I received the correct outcome.

CASE WHEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) > 0
     THEN
        CASE WHEN (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
             TPM.dim_ext_x * TPM.dim_ext_y) / 1728))) > 0
             THEN (SUM(TCT.[weight]) + SUM(TPM.[weight])) /
             (SUM(CONVERT(DECIMAL(10,4), (TRCB.skid_height_inches *
             TPM.dim_ext_x *
             TPM.dim_ext_y) / 1728)))
             ELSE 0
        END
     ELSE 0
  END AS 'TOTAL_PCF'


How would I maintain referential integrity across a database where I use versioned records while keeping data in sync?

The three ways I can see this being handled is to either:

1) Have an audit table that tracks any changes to the questions\forms tables. This would store the records being updated before they are changed, along with a date.

2) If you foresee the questions\forms being changed often, it might be better to store the actual questions in the response table, only use the question table to generate the forms.

3) Don't allow questions to be changed at all. Any modifications to a question would generate a brand new question_id. This would ensure data integrity.


Divide, round and replace in one select statement - tranformation from bytes to GB in SQL Server

The query returns a float data type, which is a binary structure that has no comma or dot decimal separator. It is the rendering application that converts the value into a string format for display purposes, which can honor the client's regional settings if programmed to do so.
Although you could change the T-SQL to return a formatted string like the example below, be aware client sorting will use string instead of numeric rules. Note this example explicitly specifies culture en_US, which uses a dot decimal separator, to override the current session language setting. Also added ORDER BY for deterministic results with the TOP clause.
SELECT TOP (1000)
      [Sharename]
      ,[ShareSize]
      ,FORMAT(ShareSize / 1e+9, '#,##0.000000000000', 'en-US') SharesizeGB
FROM [mydb].[dbo].[myshares]
ORDER BY (ShareSize / 1e+9) DESC;


Use a condition to limit the databases a policy checks

You can certainly limit the set of databases that are checked, using a condition, and have that condition be separate from the actual condition the policy is using to check. Pull down the "Every" drop-down in the UI:

Create a new condition:

Then you should be able to use that condition:

Note that this is using the SQL Server 2012 version of Management Studio (which is now completely free, as of SP1). If the UI does not behave this way in the 2008 R2 version of the tools, upgrade your tools.


Rescue truncated data from incorrect column type

The basic answer is: No.

Do you have a record of the 18-20 digit value somewhere?  In another location in your database, on a piece of paper on you desk, etc?  If so then use the source information to correct your error.

You have not indicated which database software you are using, which can make a difference for many issues.


How far will/can the secondary replica be behind?

That depends on a number of factors.  Network speed between the 2 nodes, disk speed on the secondary, volume of data being transmitted.
Many of these KPIs are in the AG Overview dashboard (Right-lick the AG in SSMS and select 'show dashboard').  You can select a slew of different metrics including log send queue, log redo queue, estimated recovery time, estimated data loss, etc.  As stated in the comments below from scsimon, when in asynchronous, the secondary isn't every truly 'caught up'.  It will always show a 'synchronizing..' status and not 'synchronized'.

  If the secondary replica goes offline for any reason will it catch
  completely up once it comes online?

Yes.  Also worth noting that during the time where your secondary replica is unavailable/disconnected, you will not be able to back up any transactions from the log that haven't been sent to the secondary.  In in sys.databases the column log_reuse_Wait_desc will be populated with 'AVAILABILITY REPLICA' as transactions cannot be flushed out unless they are committed on the secondary.

  How long can the secondary be down before it will not catch up?

The secondary won't just 'not catch up'.  If you take the secondary down (without removing it from the AG), log backups won't flush transactions out, log files will fill up and then you'll run out of room, causing any future transactions to fail.  The secondary server's amount of time that it can be down is largely dependent on how much space you have and what your transactional volume is.  Once the secondary comes back up, it will begin applying transactions that occurred while it was down.  I've seen this take from a few minutes to several hours.

For brief outages/patching, leaving the secondary connected is fine, but if you're looking at a long duration outage, it may be easier to just remove the secondary replica and not worry about the hassle of log files filling up.  Then you just re-initialize the DBs into the AG once the outage completes.


As a DBA, how would I go about transitioning from Oracle to SQL Server?

I've swapped between working on Oracle and SQL Server over the past few years, and wrote a blurb on going the other way here.  There are a number of idiomatic and architectural differences, and various bits of terminology get used differently by the vendor and developer/DBA communities surrounding each product.

Physical architecture

SQL Server organises various things a bit differently to Oracle and has one or two key concepts that have no direct analogues in Oracle.

A 'Database' is a separate item in SQL Server, with its own user permissions, schemas/name spaces and storage.  If you're familiar with Sybase, they work much the same as databases in Sybase, due to the common origins of the product.
Filegroups are roughly equivalent to table spaces, although they are local to a database.
A schema is a distinct concept from a database user in SQL Server, although users can have a default schema.
MVCC works somewhat differently in SQL Server.  It's a relatively recent feature, maintaining different copies of a row until the locks on the old version are released.  SQL Server has no direct equivalent to a rollback segment.  It is not active by default on SQL Server databases.
Tempdb is much more heavily used in SQL Server.  The system uses it for temporary tables and intermediate join results.  More on tempdb later.
Table partitioning is somewhat clumsier than Oracle.  You need to set up a partition function that creates a partition key from whatever you are supplying it, then a partition scheme over that partition function.  The partition scheme behaves a bit like a filegroup in that you then create the table on the partition scheme.Swapping partitions in and out requires you to set up a constraint on an empty table in the right structure.  The constraint guarantees that the partition key values are appropriate to the partition you intend to swap into it.
Materialised views are called indexed views in SQL Server.  The GROUP BY clause does have a CUBE operator, and the documentation alludes to a query rewrite feature.  However, this functionality is not well documented and may not be terribly mature.  YMMV.
SQL Server does not support autonomous transactions, although it does support two-phase commit through XA or OLEDB transaction protocols.
Clustered indexes are slightly different from index-ordered tables in Oracle, as they don't require all of the columns in the table to participate in the clustered index.  They're much more widely used in SQL Server architecture than IOTs are in Oracle.
SQL Server does support covering indexes, but doesn't have join indexes.  Bitmap indexes are not supported, although it does have an index intersection/star transform operator that can calculate intersections without hitting the fact table.
Sequences are a relatively recent addition to SQL Server.  Traditionally autoincrementing keys are done through identity columns.  You can load values into an identity column through set identity_insert on.

Programming

Idiomatic T-SQL has some differences to idiomatic PL/SQL.  It works differently enough that some of the paradigmatic differences merit explaining in more depth.

T-SQL has no concept of a package.  All stored procedures and functions in a database live in a common namespace, although schemas can be used to break this up, and the namespace is local to a database.
Get a feel for how to use temporary tables, and SELECT INTO.  It's pretty rare to encounter T-SQL code that actually needs a cursor; temporary tables allow operations to be broken up into steps that can be done with set operations.  SELECT INTO in tempdb is minimally logged, and it is minimally logged in certain recovery modes on user databases as well, so it is just as fast as query operator that persists an intermediate join result.Idiomatic T-SQL will use temp tables in the sort of roles that you would see cursor variables in PL/SQL, but will make much more use of set operations.  Temp tables can, however make for fairly obtuse code, so use with care.
The system data dictionary was much more obtuse than Oracle's one on older versions, but it got much better with SQL Server 2005.  Although the tools supplied by Microsoft have quite a lot of introspection stuff built in in the SSMS explorer, it's still worth knowing your way around the data dictionary.  It doesn't differentiate between ALL, USER and DBA views of the DB objects, though.
SSMS has a query plan viewer built in.
Identifiers in T-SQL code can be quoted with [], and can contain all sorts of rubbish if quoted.  However, if we catch you calling a column 'Direct/Transfer', we will rip your intestines out.
SQL Server does have window functions (since 2005 IIRC), so you can do ordering, running sums and suchlike within groups now.
T-SQL has no direct equivalent to CONNECT BY, although recursion can be done through recursive CTEs.
If you need to write code that hops across databases (as opposed to schemas within a database), consider using public synonyms to alias the objects to something local and refer to the aliases in the code.  This avoids hard-coded dependencies on database names.
If you avoid hard coded dependencies on database names, databases make it quite easy to maintain multiple environments on the same server.
Some things, such as custom aggregate functions, can only be implemented using CLR sprocs.  Also, if you want to escape from a transaction context (e.g. to fake an autonomous transaction for rollback-proof error logging) you can use a CLR sproc, as it can create a local connection outside of the current transaction context.

Security

Logins are defined at the SQL Server instance level, but each login maps to zero or more databases as a 'database user'.  Permissions cam be assigned to both 'logins' (server) and 'users' (database) but in a database 'roles' are normally used. Users belong to roles, permissions are assigned to roles. SQL Server 2012 adds 'server roles'.

SQL Server 2012 introduces a concept called 'partially contained databases', which allows user and role information to be held local to that database.
Within a database, the concept of user and schema is separated.  A user or role can be assigned to a schema and a schema owns database objects.
Windows authentication uses login information behind the scenes to authenticate a user on a machine or domain to a SQL Server login.  IIRC support for this is an optional extra on Oracle.
A special role, 'dbo' (short for 'database owner') has a sort of super-user privilege within a specific database.  Each database has a 'dbo' role and users can be assigned to the 'dbo' role on a given database.
There is also a default 'dbo' schema.  Objects may be owned by the dbo schema - Objects created by users with the 'dbo' role (or system-wide admin permissions) will default to being owned by the 'dbo' schema unless another schema is supplied explicitly.
Security information is not kept with a backup of a single database.  Users must and roles must be configured explicitly on the server the backup is restored to.  SQL Server 2012 allows user and role data to be kept locally to a database with a new 'partially contained databases' feature.
From SQL Server 2005, stored procedures can be executed in the security context of the caller, the creator, the owning schema or a specified user.
In a view on SQL Server, permissions on underlying tables are based on the permissions of the schema that owns the view.  User permissions on the underlying tables do not participate in the security, although a view definition can include filters that get information from the session.  In Oracle, user permissions on the underlying tables may affect the view, depending on the configuration of the grants.

Monitoring and tuning

TBA - memory architecture vs. SGA etc. in oracle

Backup and Recovery

TBA

Tooling

Microsoft bundles a set of surrounding tools with SQL Server.  Some of the major items supplied are:

SQL Server Management Studio (SSMS): This does something similar to SQL Developer on Oracle - it provides an editor and code execution facility.  Some useful features include a database object browser and a query plan viewer.
SQL Server Analysis Services (SSAS): This is an OLAP server that is distinct from the database server.  It uses its own query language (MDX) and API (XML/A) for client-server communication.  It cannot be queried with SQL.  SSMS has a facility for editing MDX and raw XMLA queries and displaying the results.  A command line query tool called ASCMD.EXE is also supplied.
SQL Server Reporting Services (SSRS): This is a web-based reporting tool for publishing reports.  Reports can be built through BI Development Studio (BIDS) or Report Builder, and published to a web portal.  The SSRS server itself has a web service API for programatically managing the server.  Note that SSRS reports can consume data from a variety of sources, not just SQL Server.  A command line tool called RS.EXE is supplied for programatically managing SSRS servers.
SQL Server Integration Services (SSIS): This is an ETL tool supplied with SQL Server.  Architecturally, it is quite different to OWB or ODI in that it is not a code generation tool.  The runtime sits client-side and can be on a separate machine to the database server.  SSIS packages can be developed with BIDS and executed independently with a command line tool called DTEXEC.EXE.
B.I. Development Studio (BIDS): This is a visual studio based environnemt for developing reports, SSIS packages and SSAS cubes.  If other VS based development tooling is installed (e.g. VS Professional) the tooling can be integrated into a single environment and a common project grouping.
Bulk copy (BCP): A command line bulk insert/extract tool similar to SQL*Loader
SQLCMD: A command line query tool similar to SQL*plus
SQL Profiler: A tracing and profiling tool that can capture and evaluate trace information from SQL Server, SSAS and other tools in the suite.
SQL Server Agent: A job scheduling utility that can run periodic jobs of one sort of another.


How do I make this Postgres view that performs a join updatable?

You can't use NEW.*, because the view has columns that don't exist in the underlying table.  You have to explicitly list out the columns you want to add.  As people explained above, the reason this didn't work is that you did not provide a default.  You might want to consider using COALESCE() for this purpose.


This is very straightforward.

There is no == operator.

Please change

IF(New.LossTo == 'Vehicle') THEN

to

IF(New.LossTo = 'Vehicle') THEN

UPDATE 2018-02-26 11:04 EST

You need to change the delimiter before and after the trigger:

DELIMITER $$
DROP TRIGGER IF EXISTS TRG_CREATE_INT_SRV_ROW $$
CREATE TRIGGER TRG_CREATE_INT_SRV_ROW
 AFTER INSERT ON survey_details
 FOR EACH ROW
BEGIN
 IF(New.LossTo = 'Vehicle') THEN
    INSERT INTO survey_vehicle (RefNo) VALUES (New.RefNo);
 ELSE
    INSERT INTO survey_other (RefNo) VALUES (New.RefNo);
 END IF;
END $$
DELIMITER ;

Please see my old post Unique combination key MySQL for an example


List users permissions to tables and schemas on MSSQL 2016

Give this a shot.

SELECT ST.NAME, su.name,
CASE WHEN SYP.[ACTION] = 193 THEN 'SELECT' WHEN SYP.[ACTION] = 195 THEN 'INSERT' WHEN SYP.[ACTION] = 196 THEN 'DELETE' WHEN SYP.[ACTION] = 197 THEN 'UPDATE' ELSE CAST(SYP.[ACTION] AS CHAR(3)) END AS Permission
FROM
SYS.SYSPROTECTS SYP INNER JOIN
SYS.SYSUSERS SU
ON SU.uid = SYP.UID
INNER JOIN SYS.SYSOBJECTS SO
ON SO.id = SYP.id
INNER JOIN SYS.TABLES ST
ON ST.object_id = SO.id


Moving primary key constraint from one index to another

The following script illustrates an efficient way to convert the existing nonclustered primary key to clustered, and to rename it:

-- How the table looks now
CREATE TABLE dbo.Example
(
    pk integer NOT NULL,
    some_data integer NOT NULL,

    CONSTRAINT PK_UnusualName
        PRIMARY KEY NONCLUSTERED (pk)
);

-- Some data
INSERT dbo.Example (pk, some_data)
VALUES (1, 100), (2, 200), (3, 300);

-- Change the nonclustered PK to clustered
CREATE UNIQUE CLUSTERED INDEX PK_UnusualName
ON dbo.Example (pk)
WITH (DROP_EXISTING = ON);

-- Rename
EXECUTE sys.sp_rename
    @objname = N'dbo.Example.PK_UnusualName',
    @newname = N'PK__dbo_Example_pk',
    @objtype = 'INDEX';

-- Tidy up
DROP TABLE dbo.Example;


How can i write a stored procedure which will perform a calculation and store the result in a table?

You'll need to put a trigger on the siteArea table so that whenever it's updated or inserted, it'll call a stored procedure with an argument representing the new site area and that procedure will calculate the needed values and insert them in a table.

Your question is too ambiguous for me to describe further.


Drop Project_admin|Project_manager|Project_leader from the project table, and add a role_id column to the project_participants table.


While it depends on how many jobs and your backup methods, one of the simplest options is to have the job use retry attempts and intervals to re-run the job.

You can set the intervals at a high amount to account for a full system reboot and use these jobs specifically during reboot windows. Just go to Job Properties -> Steps -> Advanced

(Job pictured is from Ola Hallengren's scripts which contain great backup jobs)

Include a cleanup step first to remove any backups from recent history (say, 1 hour) that may no longer be valid/desirable and then perform a complete backup. I think this is preferable as it gives you a set of backups all from the same time frame and not staggered depending on when the shutdown occurred.

Really though, you should be able to reliably control and schedule your reboots and thus schedule key backup jobs around that time frame. We all update our boxes (I hope) and work around a set schedule for reboots so as to not have this issue. Stronger change control management might be the true solution if this server is important enough to stay on top of backups.


Query to get logs between Start and End Night hour


Ignore the "fragmented" alarm; it's bogus.  Ignore the max "possible" memory; it's bogus.

Focus on the slow queries; they are causing the high CPU.  What are the slow queries?

Plan A:  Glance at SHOW FULL PROCESSLIST; frequently.  You will see one or two queries there most of the time.

Plan B:  Turn on the slowlog; set long_query_time = 2; and (after a day), run pt_query_digest.  It will tell you the worst queries.

Given a bad query (or two), show us

SHOW CREATE TABLE
EXPLAIN SELECT ...

We should be able to help you dramatically shrink the CPU load.

(Contrary to what @dborba suggested, I ruled out swapping and I/O.  Those tend to show up as low CPU, but high latency for queries.  We may need to address it again after adding some INDEXes, etc.)


How to select maximum date_created value from all tables matching certain pattern?

PROPOSED QUERY

SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'MAX(date_create) MaxDateCreated FROM ',table_name) OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    AND table_name LIKE 'ap_form_%'
    GROUP BY table_name
) A;
SELECT @LongQuery\G
PREPARE qry FROM @LongQuery;
EXECUTE qry;
DEALLOCATE PREPARE qry;

What this does is:

Gathered all tables from the database you ar in that has ap_form_% as a pattern
Create SELECT query for Each ap_form_% table
Concatenate all the Queries into a Single Query
Execute all the Queries as a Single Query

EXAMPLE

This does a table count of all tables in the mysql schema

USE mysql
SET group_concat_max_len = 1024 * 1024 * 10;
SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
FROM
(
    SELECT
        CONCAT('SELECT ',QUOTE(table_name),' tblname,',
        'COUNT(1) RowCount FROM ',table_name) OneSelectQuery
    FROM information_schema.partitions
    WHERE table_schema=DATABASE()
    GROUP BY table_name
) A;
SELECT @LongQuery\G
PREPARE qry FROM @LongQuery;
EXECUTE qry;
DEALLOCATE PREPARE qry;

EXAMPLE EXECUTED

MySQL [mysql]> USE mysql
Database changed
MySQL [mysql]> SET group_concat_max_len = 1024 * 1024 * 10;
Query OK, 0 rows affected (0.00 sec)

MySQL [mysql]> SELECT GROUP_CONCAT(OneSelectQuery SEPARATOR ' UNION ') INTO @LongQuery
    -> FROM
    -> (
    ->     SELECT
    ->         CONCAT('SELECT ',QUOTE(table_name),' tblname,',
    ->         'COUNT(1) RowCount FROM ',table_name) OneSelectQuery
    ->         FROM information_schema.partitions
    ->     WHERE table_schema=DATABASE()
    ->     GROUP BY table_name
    -> ) A;
Query OK, 1 row affected (0.01 sec)

MySQL [mysql]> SELECT @LongQuery\G
*************************** 1. row ***************************
@LongQuery: SELECT 'columns_priv' tblname,COUNT(1) RowCount FROM columns_priv UNION SELECT 'db' tblname,COUNT(1) RowCount FROM db UNION SELECT 'event' tblname,COUNT(1) RowCount FROM event UNION SELECT 'func' tblname,COUNT(1) RowCount FROM func UNION SELECT 'general_log' tblname,COUNT(1) RowCount FROM general_log UNION SELECT 'general_log_backup' tblname,COUNT(1) RowCount FROM general_log_backup UNION SELECT 'help_category' tblname,COUNT(1) RowCount FROM help_category UNION SELECT 'help_keyword' tblname,COUNT(1) RowCount FROM help_keyword UNION SELECT 'help_relation' tblname,COUNT(1) RowCount FROM help_relation UNION SELECT 'help_topic' tblname,COUNT(1) RowCount FROM help_topic UNION SELECT 'host' tblname,COUNT(1) RowCount FROM host UNION SELECT 'innodb_index_stats' tblname,COUNT(1) RowCount FROM innodb_index_stats UNION SELECT 'innodb_table_stats' tblname,COUNT(1) RowCount FROM innodb_table_stats UNION SELECT 'ndb_binlog_index' tblname,COUNT(1) RowCount FROM ndb_binlog_index UNION SELECT 'plugin' tblname,COUNT(1) RowCount FROM plugin UNION SELECT 'proc' tblname,COUNT(1) RowCount FROM proc UNION SELECT 'procs_priv' tblname,COUNT(1) RowCount FROM procs_priv UNION SELECT 'proxies_priv' tblname,COUNT(1) RowCount FROM proxies_priv UNION SELECT 'rds_configuration' tblname,COUNT(1) RowCount FROM rds_configuration UNION SELECT 'rds_global_status_history' tblname,COUNT(1) RowCount FROM rds_global_status_history UNION SELECT 'rds_global_status_history_old' tblname,COUNT(1) RowCount FROM rds_global_status_history_old UNION SELECT 'rds_heartbeat2' tblname,COUNT(1) RowCount FROM rds_heartbeat2 UNION SELECT 'rds_history' tblname,COUNT(1) RowCount FROM rds_history UNION SELECT 'rds_replication_status' tblname,COUNT(1) RowCount FROM rds_replication_status UNION SELECT 'rds_sysinfo' tblname,COUNT(1) RowCount FROM rds_sysinfo UNION SELECT 'servers' tblname,COUNT(1) RowCount FROM servers UNION SELECT 'slave_master_info' tblname,COUNT(1) RowCount FROM slave_master_info UNION SELECT 'slave_relay_log_info' tblname,COUNT(1) RowCount FROM slave_relay_log_info UNION SELECT 'slave_worker_info' tblname,COUNT(1) RowCount FROM slave_worker_info UNION SELECT 'slow_log' tblname,COUNT(1) RowCount FROM slow_log UNION SELECT 'slow_log_backup' tblname,COUNT(1) RowCount FROM slow_log_backup UNION SELECT 'tables_priv' tblname,COUNT(1) RowCount FROM tables_priv UNION SELECT 'time_zone' tblname,COUNT(1) RowCount FROM time_zone UNION SELECT 'time_zone_leap_second' tblname,COUNT(1) RowCount FROM time_zone_leap_second UNION SELECT 'time_zone_name' tblname,COUNT(1) RowCount FROM time_zone_name UNION SELECT 'time_zone_transition' tblname,COUNT(1) RowCount FROM time_zone_transition UNION SELECT 'time_zone_transition_type' tblname,COUNT(1) RowCount FROM time_zone_transition_type UNION SELECT 'user' tblname,COUNT(1) RowCount FROM user
1 row in set (0.00 sec)

MySQL [mysql]> PREPARE qry FROM @LongQuery;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MySQL [mysql]> EXECUTE qry;
+-------------------------------+----------+
| tblname                       | RowCount |
+-------------------------------+----------+
| columns_priv                  |        6 |
| db                            |      207 |
| event                         |        2 |
| func                          |        0 |
| general_log                   |        0 |
| general_log_backup            |        0 |
| help_category                 |       38 |
| help_keyword                  |      453 |
| help_relation                 |      992 |
| help_topic                    |      505 |
| host                          |        0 |
| innodb_index_stats            |    32532 |
| innodb_table_stats            |     3392 |
| ndb_binlog_index              |        0 |
| plugin                        |        0 |
| proc                          |       27 |
| procs_priv                    |        0 |
| proxies_priv                  |        1 |
| rds_configuration             |        1 |
| rds_global_status_history     |        0 |
| rds_global_status_history_old |        0 |
| rds_heartbeat2                |        1 |
| rds_history                   |        1 |
| rds_replication_status        |        1 |
| rds_sysinfo                   |        0 |
| servers                       |        0 |
| slave_master_info             |        0 |
| slave_relay_log_info          |        0 |
| slave_worker_info             |        0 |
| slow_log                      |        0 |
| slow_log_backup               |        0 |
| tables_priv                   |       48 |
| time_zone                     |     1768 |
| time_zone_leap_second         |        0 |
| time_zone_name                |     1768 |
| time_zone_transition          |   118021 |
| time_zone_transition_type     |     8611 |
| user                          |      138 |
+-------------------------------+----------+
38 rows in set (0.00 sec)

MySQL [mysql]> DEALLOCATE PREPARE qry;
Query OK, 0 rows affected (0.01 sec)

MySQL [mysql]>

This should work for you.

GIVE IT A TRY !!!


Archiving the Database Audit Trail

Scheduling an Automatic Purge Job for the Audit Trail

Manually Purging the Audit Trail

Basically you copy the audit trail to another table, e.g:

INSERT INTO table SELECT ... FROM SYS.AUD$ ...;
INSERT INTO table SELECT ... FROM SYS.FGA_LOG$ ...;

You can just export and drop these tables, then import them when you need to access audit records.

Then you set the last archive timestamp, e.g:

BEGIN
  DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
   AUDIT_TRAIL_TYPE     =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   LAST_ARCHIVE_TIME    =>  '2009-05-28 06:30:00.00'
   RAC_INSTANCE_NUMBER  =>  0 );
END;
/

Finally you initialize cleanup and schedule a purge or run it manually:

BEGIN
 DBMS_AUDIT_MGMT.INIT_CLEANUP(
  AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
  DEFAULT_CLEANUP_INTERVAL    => 12 );
END;
/

BEGIN
  DBMS_AUDIT_MGMT.CREATE_PURGE_JOB (
   AUDIT_TRAIL_TYPE            => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   AUDIT_TRAIL_PURGE_INTERVAL  => 12,
   AUDIT_TRAIL_PURGE_NAME      => 'Standard_Audit_Trail_PJ',
   USE_LAST_ARCH_TIMESTAMP     => TRUE );
END;
/

Manual:

BEGIN
  DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
   AUDIT_TRAIL_TYPE           =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
   USE_LAST_ARCH_TIMESTAMP    =>  TRUE );
END;
/


Query to output column names and data types of a query, table or view

Information schema vs. system catalogs
We have had discussions about this many times. The information schema serves certain purposes. If you know your way around the system catalogs, those serve most purposes better, IMO. The system catalogs are the actual source of all information.
The information schema provides standardized views which help with portability, mostly across major Postgres versions, because portability across different RDBMS platforms typically is an illusion once your queries are sophisticated enough to need to look up system catalogs. And, notably, Oracle still doesn't support the information schema.
Views in the information schema must jump through many hoops to achieve a format complying to the standard. This makes them slow, sometimes very slow. Compare plans and performance for these basic objects:
EXPLAIN ANALYZE SELECT * from information_schema.columns;
EXPLAIN ANALYZE SELECT * from pg_catalog.pg_attribute;

The difference is remarkable. It really depends on what you are looking for.
Your example
For your example SELECT * from tbl, compare the two queries below for this simple table:
CREATE TEMP TABLE foo(
   A numeric(12,3)
 , b timestamp(0)
);

Using pg_attribute:
SELECT attname, format_type(atttypid, atttypmod) AS type
FROM   pg_attribute
WHERE  attrelid = 'foo'::regclass
AND    attnum > 0
AND    NOT attisdropped
ORDER  BY attnum;

format_type() returns the complete type with all modifiers:
attname | type
--------+-------------------------------
a       | numeric(12,3)
b       | timestamp(0) without time zone

Also note that the cast to regclass resolves the table name somewhat intelligently according to the current search_path. It also raises an exception if the name is not valid. Details:

ERROR: could not find array type for datatype information_schema.sql_identifier

Using information_schema.columns:
SELECT column_name, data_type
FROM   information_schema.columns
WHERE  table_name = 'foo'
ORDER  BY ordinal_position;

The information is standardized, but incomplete:
column_name | data_type
------------+----------------------------
a           | numeric
b           | timestamp without time zone

To get full information for the data type you need to consider all of these columns additionally:
character_maximum_length
character_octet_length
numeric_precision
numeric_precision_radix
numeric_scale
datetime_precision
interval_type
interval_precision

Related answers:

How to check if a table exists in a given schema (on SO)
How do I list all columns for a specified table

A list of pros & cons, the biggest pros (IMO) in bold:
Information schema views

often simpler (depends)
slow
preprocessed, which may or may not suit your needs
selective (users only see objects they have privileges for)
conforming to an SQL standard (that's implemented by some of the major RDBMS)
mostly portable across major Postgres versions
do not require much specific knowledge about Postgres
identifiers are descriptive, long and sometimes awkward

System catalogs

often more complex (depends), closer to the source
fast
complete (system columns like oid included)
not complying to an SQL standard
less portable across major Postgres versions (but basics aren't going to change)
require more specific knowledge about Postgres
identifiers are terse, less descriptive but conveniently short

Arbitrary query
To get the same list of column names and types from a query, you could use a simple trick: CREATE a temporary table from the query output, then use the same techniques as above.
You can append LIMIT 0, since you do not need actual data:
CREATE TEMP TABLE tmp123 AS
SELECT 1::numeric, now()
LIMIT  0;

To get the data type of individual columns, you can also use the function pg_typeof():
SELECT pg_typeof(1);


The problem with your code is probably that the logic looks for the @SplitOn character to know where the end of the SUBSTRING should be. The fix would be to simply add the @SplitOn character to the end of @List at the start of the function.

HOWEVER, I think you are going about the operation in the wrong way. First, splitting using a WHILE loop is horribly inefficient, especially since you have to recalculate @List in each iteration of the loop.

You would be far better off using an efficient string splitter and then doing a COUNT(*) combined with a WHERE val = '5'.

For example:

SELECT COUNT(*)
FROM   SQL#.String_Split4k(N'5,5,5,5,55,5,5,5,5', N',', 1) split
WHERE  split.SplitVal = N'5';
-- 8

Please note that the example uses a SQLCLR function from the SQL# library (which I am the author of, but String_Split4k is in the Free version) to do the string splitting.


below are the options we have tried in our environment and we are successfull in reducing timeouts to 99%.

1.Change remote login time out options
2.change remote query time out options
3.Disable tcp chimney,rss options

http://blogs.technet.com/b/onthewire/archive/2014/01/21/tcp-offloading-chimney-amp-rss-what-is-it-and-should-i-disable-it.aspx

Once the above settings are in place,its time out to look on queries
1.Gather the most failed step by querying ssisdb catalog by limiting predicate to event of onerror
2.Once the above steps are gathered,concentrate on most failed steps and post the query here if you need any help in fine tuning.
3.further you also may need to check some aspects like
what is the load on the server
are my queries waiting for any resources consistently
are the failed queries using tempdb,if so what is the load on tempdb

once the above changes are done with respect to settings,we concentrated totally on Tuning queries and it yielded results


MongoDB Restore / Snapshot Schedule - Point In Time vs. Oplog Timestamp

What's the differences between those restore points? I guess Snapshot is fast restore, because only copy already existing files to destination. But it takes compute and storage resources the generate every 6 hours a snapshot and it will be stored only for 3 days.

A snapshot is a full backup of your data captured at a specific interval. Restoring from a stored snapshot is the fastest option because minimal manipulation needs to be done by Ops Manager in order to provide the restore files. The snapshotIntervalHours (6 hours in your config) and snapshotRetentionDays (3 days in your config) are meant to provide more frequent restore points for recent data as compared to the archived daily/weekly/monthly snapshots. With your current configuration, restores would be possible to: stored snapshots taken on 6 hour intervals in the last 3 days, a point in time within the last 24 hours, or a daily/weekly/monthly snapshot.

  I don't understand the difference between Point In Time and Oplog Timestamp. When to choose what (advantages and disadvantages)?

These are both options for custom point-in-time snapshots, which can be time and resource intensive for Ops Manager to process. The backend processing involves restoring the nearest (older) stored snapshot and then applying the oplog changes up to the specified point in time (as marked by a date/time in seconds or an oplog timestamp). Both are limited by the available oplog history as specified in your pointInTimeWindowHours configuration (i.e. 24 hours).

The Point In Time restore point creates a custom snapshot up to the selected date and time; the Oplog Timestamp is a more precise custom snapshot which presumes you know the exact oplog timestamp you want to restore to (for example, up to and including the entry preceding an accidental collection drop).

  Any improvments for generic DBaaS provider, which doesn't know individual customers? Any issues?

It depends what level of backup/restore granularity you want to offer your customers. Increasing snapshot frequency or retention will consume more server resources, but perhaps provide more comfort for your customers. Conversely, you could reduce the snapshot frequency or retention to save on resources.

As a DBaaS provider I expect you would work standard backup & licensing costs into your service model and perhaps allow customers to vary backup options based on subscription plans or premiums (for example, as MongoDB Cloud Manager does).

Note: MongoDB Ops Manager is not currently intended to be the base for a DBaaS offering and should only be used in production as part of a MongoDB Enterprise Advanced subscription (which includes commercial support for questions such as configuration, capacity planning, tuning, ...).


Correct pg_hba.conf to allow script to run locally with a specific db-user

Since you want the backup user to connect locally from the database server. I assume the backup is running on a dedicated backup account on the OS.
Using pg_hba.conf you can specify like:
# TYPE  DATABASE USER   ADDRESS METHOD
local   all      backup_user    peer map=local_peers_backup_user

(method peer (with local/peer an address is not needed))
combine this with a pg_ident.conf like:
# MAPNAME                SYSTEM-USERNAME   PG-USERNAME
local_peers_backup_user  postgres          backup_user
local_peers_backup_user  OSBKPUSER         backup_user

This allows the os user postgres and OSBKUSER to connect to all databases in the cluster as the postgres database user named backup_user, using peer authentication. This works using sockets, on linux.
connected as your OSBKPUSER running psql suffices to get connected as backup_user, assuming that you already prepared the environment variables for the OSBKPUSER and OSBKPUSER has only 1 map. Otherwise run psql -U backup_user.
This connect method is secure, if you trust that the access to your dbserver is secured.


You have segments allocated across the tablespace with "gaps" of free space.
In order to reclaim it you should first reorg the tablespace.
See: reclaiming unused space
https://oracle-base.com/articles/misc/reclaiming-unused-space
It's a rather long process for such a large file....
Btw, if you have EM installed it's just a couple of mouse clicks

Regards
Jony


Main idea:
WITH cte AS ( SELECT UserId,
                     CheckTime,
                     LEAD(CheckTime) OVER (PARTITION BY UserId ORDER BY CheckTime) NextTime,
                     CheckType,
                     LEAD(CheckType) OVER (PARTITION BY UserId ORDER BY CheckTime) NextType )
SELECT UserId, CheckTime, NextTime
FROM cte
WHERE CheckType = 'I'
  AND NextType = 'O'

Modify as you need - extract date and/or time parts, check that date parts are equal, etc.


Repro

Open up SSMS
Type in the following into a new query window

use <YourDatabase>;
go

Go over to Object Explorer (SSMS) and right-click on <YourDatabase> -> Tasks -> Take Offline
Open up a second new query window and type the following:

use <YourDatabase>;
go

You will be prompted with the following message:

  Msg 952, Level 16, State 1, Line 1
  Database 'TestDb1' is in transition. Try the statement later.

The reason this is happening can be found from a similar diagnostic query to the one below:

select
    l.resource_type,
    l.request_mode,
    l.request_status,
    l.request_session_id,
    r.command,
    r.status,
    r.blocking_session_id,
    r.wait_type,
    r.wait_time,
    r.wait_resource,
    request_sql_text = st.text,
    s.program_name,
    most_recent_sql_text = stc.text
from sys.dm_tran_locks l
left join sys.dm_exec_requests r
on l.request_session_id = r.session_id
left join sys.dm_exec_sessions s
on l.request_session_id = s.session_id
left join sys.dm_exec_connections c
on s.session_id = c.session_id
outer apply sys.dm_exec_sql_text(r.sql_handle) st
outer apply sys.dm_exec_sql_text(c.most_recent_sql_handle) stc
where l.resource_database_id = db_id('<YourDatabase>')
order by request_session_id;

For what it's worth, you don't need Object Explorer to reproduce this error.  You just need a blocked request that is attempting the same operation (in this case, take the database offline).  See the below screenshot for the three steps in T-SQL:

What you will most likely see is your Object Explorer session being blocked by another session (shown by blocking_session_id).  That Object Explorer session will be trying to get an exclusive lock (X) on the database.  In the above repro's case, the Object Explorer session was granted an update lock (U) and attempting to convert to an exclusive lock (X).  It had a wait_type of LCK_M_X, blocked by our session that was represented by the first query window (the use <YourDatabase> grabs a shared lock (S) on the database).

And then this error came about from yet another session trying to get a lock, and this error message results in the denial of a session to get access to a database that is attempting to transition into a different state (in this case, state of online to offline transition).

What should you do next time?

First off, don't panic and don't start dropping databases.  You need to take a troubleshooting approach (with a similar diagnostic query like the one above) to find out why you're seeing what you're seeing.  With a message like that, or when something appears "hung", you should automatically assume a lack of concurrency and start digging into blocking (sys.dm_tran_locks is a good start).

As a side note, I truly do believe that you are best to find out the root of a problem before taking any random action.  Not just with this operation, but that goes for all behavior that you don't expect.  Knowing what was really causing your problem, it's obvious it really was no big deal.  You basically had a blocking chain, and the parent blocker was something you most likely could have just issued a KILL on, or if it was a session's request that you didn't want to KILL then you could have waited until it completed.  Either way, you would have had the knowledge to make the right and prudent decision given your particular scenario (rollback or wait for commit).

Another thing worth noting, this is one of the reasons why I always opt for the T-SQL alternative instead of a GUI.  You know exactly what you are executing with T-SQL and what SQL Server is doing.  After all, you issued the explicit command.  When you use a GUI, the actual T-SQL is going to be an abstraction.  In this case, I looked at the blocked Object Explorer's attempt to take the database offline and it was ALTER DATABASE <YourDatabase> SET OFFLINE.  There was no attempt to rollback, which is why it was indefinitely waiting.  In your case, if you wanted to rollback sessions that had locks on that database, your ALTER DATABASE ... SET OFFLINE WITH ROLLBACK IMMEDIATE would have most likely sufficed if you had made the initial determination that rollback was okay.


jsonb_array_elements() in the select seems to turn a left join into an inner join

Set returning functions should be put into the from clause. Putting them into the SELECT clause is allowed, but can result in strange result (as you have noticed).

The clean approach would be:

select a.vin, r.data ->'attributes'->>'name' as style
from vins a
  left join describe_vehicle b on a.vin = b.vin
  left join jsonb_array_elements(b.response->'style') as r(data) on true

Online example: https://rextester.com/RGY32895


Should I create a extra relation-table when I have two columns in one table referencing to one column in another table

I'd definitely choose option 2. A game of chess is between exactly two players.

Option 1 allows a game with 0, 1, 2, 3 or more players, while the data model in option 2 (assuming not null on the two player columns) means that you will never have these data inconsistencies. Just remember to also add the check constraint black_player != white_player.


Mariadb 10.1 / Galera - bootstrap node always doesn't lose quorum

I don't think this is a bug. Its an expected behaviour interms of normal shutdown scenario. If you are practicing split-brain testcases then it makes sense for it to loose its Primary status.

In your case the clear information about their neighbouring voters exit status is communicated. And thus software lets it to serve the requests to the node1 that is alive, since the status of other nodes are shutdown and that is communicated to node1.

Try to execute Split-Brain scenario by isolating the node1 from node2+arbitor link and then you should find node1 losing its primary status and node2 wins while arbiter voting by its side.


Is there anyway to know when was the last fail-over happen

Yes, have alerts and monitoring setup to log this when it happens.

  and exact Root cause of fail-over?

Sure, you log it manually when you figure out what the root cause is. There is nothing automatic that will magically give you a root cause for the failure - that's your job.

  Can we get history of all the previous primary nodes list and time of their fail-overs?

Unless you already have this information logged somewhere, no there is no place that this is automatically logged at.

You can attempt to slog through the engine errorlog, window event logs, and cluster logs... but there is nothing to say that they haven't rolled over or have been otherwise cleared.

  I have explored a lot in internet. I didn't get the exact steps to see that.

Because nothing that's automatic currently exists inside the product.


How to stop MySQL replicating users on slave

For statement-based replication(the default for mixed binary logging format), --replicate-ignore-db=foo_db works only when foo_db is explicitly used as in USE foo_db. In a GRANT or REVOKE statement, USE mysql is not executed, as can be seen from the binary log.

For row-based replication, if --replicate-ignore-db=foo_db, any direct change to a table in foo_db would not be replicated to the slave. But MySQL always uses statement-based logging for statements that indirectly change the tables of mysql database(GRANT, REVOKE, SET PASSWORD, ALTER, DROP, CREATE), regardless of the value of binlog_format.

So, to stop replicating any changes in mysql database to slave, you can set binlog-ignore-db=mysql in your master's configuration file to stop logging any changes in mysql database. You can also temporarily turn off sql_log_bin for your session and CREATE/ALTER the user before turning the variable back on again.


Force PostgreSQL clients to use SSL on Amazon RDS

Looks like this has finally been added. From the docs:

  You can use the rds.force_ssl parameter to force connections to your PostgreSQL database to use SSL. The default value is 0 (off). When you set this parameter to 1 (on) and restart your instance, your database will refuse any non-SSL connections. This parameter also sets the PostgreSQL parameter ssl to on as well as modifies your instance's pg_hba.conf to support this SSL configuration.


Merge Replication customize synchronization cycle and its performance

To customize the merge process, you'll need to implement a custom business logic handler (requires .NET code - cannot be implemented using TSQL). See Execute Business Logic During Merge Synchronization in the documentation. It is available in SQL Server 2012 and later.

Override methods Update, Insert and Commit to transform changed rows, and conflict resolver method to modify conflict logic. Can't use T-SQL, must be done with .NET code. The main limitation is that each overridable method is called once for every row (like SSIS row transformations), to the best of my knowledge there isn't a method called on all changed rows.

There are some workarounds to handle your case, but this the general solution.

This is the BOL C# template:

using System;
using System.Text;
using System.Data;
using System.Data.Common;
using Microsoft.SqlServer.Replication.BusinessLogicSupport;
using Microsoft.Samples.SqlServer.BusinessLogicHandler;

namespace Microsoft.Samples.SqlServer.BusinessLogicHandler
{
public class OrderEntryBusinessLogicHandler :
  Microsoft.SqlServer.Replication.BusinessLogicSupport.BusinessLogicModule
{
    // Variables to hold server names.
    private string publisherName;
    private string subscriberName;

    public OrderEntryBusinessLogicHandler()
    {
    }

    // Implement the Initialize method to get publication
    // and subscription information.
    public override void Initialize(
        string publisher,
        string subscriber,
        string distributor,
        string publisherDB,
        string subscriberDB,
        string articleName)
    {
        // Set the Publisher and Subscriber names.
        publisherName = publisher;
        subscriberName = subscriber;
    }

    // Declare what types of row changes, conflicts, or errors to handle.
    override public ChangeStates HandledChangeStates
    {
        get
        {
            // Handle Subscriber inserts, updates and deletes.
            return ChangeStates.SubscriberInserts |
              ChangeStates.SubscriberUpdates | ChangeStates.SubscriberDeletes;
        }
    }

    public override ActionOnDataChange InsertHandler(SourceIdentifier insertSource,
      DataSet insertedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
      ref string historyLogMessage)
    {
        // custom logic for updates
    }

    public override ActionOnDataChange UpdateHandler(SourceIdentifier updateSource,
      DataSet updatedDataSet, ref DataSet customDataSet, ref int historyLogLevel,
      ref string historyLogMessage)
    {
 // custom logic for updates
  }
    public override ActionOnDataDelete DeleteHandler(SourceIdentifier deleteSource,
      DataSet deletedDataSet, ref int historyLogLevel, ref string historyLogMessage)
    {
     // custom logic for deletes
}
}


Requesting help with setting up design of a few tables and the correct foreign keys

With a many-to-many relationship (each shape can be associated with many recipients, and each recipient can be associated with many shapes).

Here's the create statements for the mapping table (plus my dummy definitions for the three tables you've already got):

CREATE TABLE `user` (UserID int AUTO_INCREMENT NOT NULL,
                     userVal varchar(32),
                     CONSTRAINT pk_user
                     PRIMARY KEY (UserID)
                    );

CREATE TABLE `shape` (ShapeID int AUTO_INCREMENT NOT NULL,
                      UserID int,
                      shapeVal varchar(32),
                      CONSTRAINT pk_shape
                      PRIMARY KEY (ShapeID),
                      CONSTRAINT fk_shape__user
                      FOREIGN KEY (UserID)
                      REFERENCES `user` (UserID)
                     );

CREATE TABLE `recipient` (RecipientID int AUTO_INCREMENT NOT NULL,
                          UserID int,
                          recipientVal varchar(32),
                          CONSTRAINT pk_recipient
                          PRIMARY KEY (RecipientID),
                          CONSTRAINT fk_recipient__user
                          FOREIGN KEY (UserID)
                          REFERENCES `user` (UserID)
                         );

CREATE TABLE map_shape_recipient
       (ShapeID int,
        RecipientID int,
        CONSTRAINT pk_map_shape_recipient
        PRIMARY KEY (ShapeID, RecipientID),
        CONSTRAINT fk_map_shape_recipient__shape
        FOREIGN KEY (ShapeID)
        REFERENCES shape (ShapeID)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        CONSTRAINT fk_map_shape_recipient__recipient
        FOREIGN KEY (RecipientID)
        REFERENCES recipient (RecipientID)
        ON DELETE CASCADE
        ON UPDATE CASCADE
       );

You'd probably want the same ON DELETE and ON UPDATE for the shape and recipient foreign keys, but you didn't specify, so I left that out.

Tested via db-fiddle; that has the four CREATE TABLE statements, some sample data, and shows what happens in the mapping table when a shape or a recipient is removed.

NOTE: Presumably, a recipient and a shape should only be associated if they belong to the same user.

You might be able to make the primary keys of shape and recipient the unique ID from the table combined with the UserID. map_shape_recipient would require the UserID column too, and (if there's only one and it's part of the foreign keys to shape and to recipient) they'd have to have the same UserID.

Technically at least, this would be a denormalization (the map table wouldn't need the UserID because ShapeId and RecipientID both uniquely identify one row in their respective tables).

A more solution would be an INSERT trigger on the map table, that prevents a row from being inserted unless the UserID from the shape row and the UserID from the recipient row match.

In your case, your application should prevent associating a shape with a recipient that doesn't have the same UserID. I would still recommend some mechanism to ensure relational integrity, in case someone directly interacts with the database outside of the application.


SQL database scripts in EXE or DLL format

send the generated scripts ... to our client ... they can't see theirs content.

This is technically impossible, but may be sort-of implemented depending on what your threat model is. The problem is that the resulting distributable must be able to unpack/decode/decrypt the scripts such that it can submit them to SQL Server so it must contain all the required algorithms and keys.

If your threat model involves trying to stop a determined snooper (a competitor perhaps, or a black-hat looking to find holes that as-yet-unpatched installations are still vulnerable to) from seeing the code then that is impossible with a simple distributable. No matter how many layers you add they can just unpick them by analysing the distributable.

Secondly, if you make it enough hassle that they don't want to do that they have another inspection route: they could just intercept the communication with SQL Server and watch the code as it flows through. Yes TDS streams are usually encrypted, but they are running the package against their SQL server so that control both the client and the server so can control (and therefore break for their own purposes) that encryption. Thirdly, they can always just look in the database, and compare the current to a pre-patch backup if that would make the changes more obvious, the only way around this is for you to host the DB perhaps running your product in a SaaS-only manner.

If your concern is merely more opportunistic snooping, then there are many installation packagers[1] out there that support creating self-extracting executables which would run a program in the archive to run the scripts also in the archive and clean up as best as is possible afterwards, and support encrypting or otherwise obfuscating the content in transit.

[1] I'll not mention specific ones as there are many (free, Free, OpenSource, and commercial), I don't know enough about any specifically to authoritatively compare them, I know nothing of your dev and production environments (other than they include SQL Server), and anyway shopping/recommendation questions/answers are discouraged on StackExchange for reasons explained in detail elsewhere.

I would recommend against using this sort of tool for this purpose though: you are adding a layer of complication that is otherwise unnecessary and might make debugging installation problems that might occur more problematical for you and therefore your client (which won't look good).

If you do use such a product, do so as a convenience: making installation a single (or at least minimal) step process for your clients in order to reduce the risk of human error, rather than as a security measure.

If your concern is more "human factors" such as bad language in code comments or other sources of embarrassment, then discipline and lack of code reviews is the problem, not distribution security, and this needs to be fixed earlier in your process.

  I will need to send the generated scripts in some releases

As a side note: if by this you mean that you are generating scripts from SQL Server in order to distribute, then you may need to improve your dev process. You should be able to build everything including upgrade packages from source control rather than generating scripts from your dev databases. This may take effort to setup to run smoothly, but it could save you from a collection of failure types in future.


MySQL Workbench fails to store password in vault

The final error message shows the real problem. Your credentials are not correct or the user is not allow to connect. Hence MySQL Workbench asks again for them when you test the connection (as it failed to connect using your stored credentials).


Using a real certificate with rethinkdb cert chain bundle

Silly me. There was --driver-tls-ca option that I was missing. I found it in the rethinkdb manual (man rethinkdb).

TLS options:

  --http-tls-key key_filename                 private key to use for web
                                              administration console TLS
  --http-tls-cert cert_filename               certificate to use for web
                                              administration console TLS
  --driver-tls-key key_filename               private key to use for client driver
                                              connection TLS
  --driver-tls-cert cert_filename             certificate to use for client driver
                                              connection TLS
  --driver-tls-ca ca_filename                 CA certificate bundle used to verify
                                              client certificates; TLS client
                                              authentication disabled if omitted
  --cluster-tls-key key_filename              private key to use for intra-cluster
                                              connection TLS
  --cluster-tls-cert cert_filename            certificate to use for intra-cluster
                                              connection TLS
  --cluster-tls-ca ca_filename                CA certificate bundle used to verify
                                              cluster peer certificates

I set it in my rethinkdb instance conf file:

# TLS stuff
driver-tls-key=/etc/ssl/star.cert.key
driver-tls-cert=/etc/ssl/star.cert.crt
driver-tls-ca=/etc/ssl/star.cert.ca-bundle

And everything works as expected. openssl s_client returns the proper 0 (ok) code.

Edit note:
Although with the rethinkdb dump utility it looks like there is no ca option so I can't use a real cert anyway.


Select rows where one row meets a condition

If you can handle both products on the same row:

select t1.UserId, t1.ProductId, t1.Status,
       t2.ProductId as otherproduct, t2.Status as otherproductstatus
from MyTable t1
join MyTable t2
  on t1.UserID=t2.UserID
     AND t1.ProductID=100
     AND t2.productID!=100

However with this you'll get all products for a user:


What steps we need to perform from DB end in case of storage migration?

The impact should be minimal, if not non-existent. I've had several databases of varying sizes (both in storage and cluster) and types (MySQL, Postgres, Cassandra, to name a few) migrated between different storage environments with little impact to the end user. The VM always stays online and is usable just as it was prior to the vMotion.
If it's a:

manually triggered event I would say, at minimum, have a fresh backup available somewhere off-server in case there is a problem that requires a recovery. Otherwise, watch your applications and logs on the DB server as the Storage vMotion is occurring  and react accordingly.
automatic event because of storage failure, storage utilization, etc., you should be sure your backups are automated and saved off-server so you can recover should there be a problem. VM snapshots won't save you here should there be a problem.


Why is FORCE JOIN option slowing down query and forcing execution plan to sort prior to joining tables?

Okay, after heeding advice from the comments, I removed the top 10000 * from the select statement. After doing so, forcing the join order actually sped up the query as intended. It took 48 seconds (vs. 60 sec).

Here's the original execution plan.

And here's the forced join execution plan.


Creating tablespace on network location in Postgres

Storing database files on network storage is probably not a good idea with regard to performance; and probably security. Make sure your network is fast enough, and the connection very reliable.

Anyhow, it is feasible.

You seem to be working on Windows. Depending on version and mode of installation, the user running your PostgreSQL service might be different.

First thing to do: check which one it is. Use the "Services" administration tool, find out a service with a name similar to Postgres, and find out which user is the one that will be running PostgreSQL. In most recent versions and installation modes, it is commmon that this user is "Local System" or "NetworkService". In previous versions, it used to be a Postgres local user (i.e.: not an Active Directory domain user).

At this point, you have two options:

Change the user (and password) to another one that has permissions to access your network share. Create one if you must. This user must not have administrative privileges; and it should have privileges only to be able to handle the database (i.e.: access to the part of the filesystem that it requires, which will be the PostgreSQL data and log directories that you chose while installing and all the tablespaces you define).
Give the existing user read/write/modify permissions to access the network share. You shouldn't (and you probably just won't be able to) if the user is one of the standard Windows, that would most probably open the whole Windows to lots of security risks.


how to get complementary information from sp_readerrorlog

As other comments on your question indicated, there are probably better ways other than TSQL to get this data, but here is something I cobbled together using TSQL.

--Declare variables
--OldDelim and NewDelim are used to split on multi-char (Thanks to Aaron Bertrand for the idea)
DECLARE @olddelim NVARCHAR(10) = 'client:'
    ,@newdelim NCHAR(1) = NCHAR(9999);-- pencil (✏)
DECLARE @RowsToProcess INT
DECLARE @txt TABLE (txt VARCHAR(100))
DECLARE @cmd VARCHAR(100)
DECLARE @LogInfo TABLE (LogDate DATETIME,ProcessInfo VARCHAR(100),txt VARCHAR(max))

--Retrieve errorlog information
INSERT INTO @LogInfo
EXEC sp_readerrorlog 0,1,'Login failed'

--temp table to hold the results of parsing the errorlog info
DROP TABLE IF EXISTS #DataToProcess

--add row number to facilitate while loop processing and updates of temp table
SELECT *
    ,trim(replace(value, ']', '')) AS HostNameIP
    ,convert(VARCHAR(30), NULL) AS HostName
    ,ROW_NUMBER() OVER (
        ORDER BY newid()
        ) AS rn
INTO #DataToProcess     --insert into temp table
FROM @LogInfo li
CROSS APPLY (           --cross apply split information to get HostIP
    SELECT value
    FROM string_split(replace(li.txt, @olddelim, @newdelim), @newdelim)
    WHERE value LIKE '%]%'
    ) ca

SET @RowsToProcess = (
        SELECT count(*)
        FROM #DataToProcess
        )
DECLARE @cntr INT = 1

WHILE @cntr <= @RowsToProcess
BEGIN
    SET @cmd = 'nslookup ' + (
            SELECT HostNameIP
            FROM #DataToProcess
            WHERE rn = @cntr
            )

    DELETE FROM @txt
    INSERT INTO @txt
    EXEC xp_cmdshell @cmd

    --Update temp table with HostName from nslookup
    UPDATE #DataToProcess
    SET HostName = (
            SELECT trim(replace(txt, 'Name:', ''))
            FROM @txt
            WHERE txt LIKE '%name%'
            )
    WHERE rn = @cntr

    SET @cntr += 1
END

SELECT LogDate, ProcessInfo, txt, HostNameIP, HostName
FROM #DataToProcess

| LogDate                 | ProcessInfo | txt                                                                                | HostNameIP    | HostName                |
|-------------------------|-------------|-----------------------------------------------------------------------------------------------------------------|---------------|-------------------------|
| 2019-03-07 14:40:07.320 | Logon       | Login failed for user 'xyz'. Reason: Could not find a login matching the name provided. [CLIENT: 10.165.32.119] | 10.165.32.119 | sc-hgrckb2.anywhere.com |
| 2019-03-07 15:06:51.540 | Logon       | Login failed for user 'zzz'. Reason: Could not find a login matching the name provided. [CLIENT: 10.165.32.119] | 10.165.32.119 | sc-hgrckb2.anywhere.com |


tuned lacks oracle profile. How could I switch to it?

Looking at tuned-adm oracle's documentation, I've found the next:

  oracle   A profile optimized for Oracle databases loads based on
  throughput-performance profile. It additionaly disables
  transparent huge pages and modifies some other performance related
  kernel parameters. This profile is provided by
  tuned-profiles-oracle package. It is available in Red Hat
  Enterprise Linux 6.8 and later.

So, the installation of the tuned-profiles-oracle package solved this issue:

# yum install tuned-profiles-oracle

# tuned-adm profile oracle

# tuned-adm active

Current active profile: oracle
Service tuned: enabled, running
Service ktune: enabled, running


Extended Events session file truncated on server restart

Based on your question (and additional comments regarding your setting of max_rollover_files to 1:

  However it appears that a new event file is created when SQL Server is restarted and the history is lost. Is this expected?

Yes - that is expected.  When you restarted SQL Server all currently running extended events were terminated.  When you restarted SQL Server, a new extended event session started and your existing target file was replaced because you indicated you only wanted 1 file.  If you want to preserve extended event data stored in target files across server restarts, you will have to increase the max_rollover_files to something greater than 1.

sys.fn_xe_file_target_read_file (path, mdpath, initial_file_name, initial_offset) has the capability of reading all extended event files in a the path referenced by mdpath if you specify null for the initial_file_name, so maintaining multple files should not be a problem.

Regarding the use of the Ring Buffer, take a look at why Jonathan Kehayias (world renowned expert in SQL Server Extended Events) Hates the ring buffer.


Unable to reset the password of users in postgres

I was finally able to solve the issue thanks to this site,

http://scratching.psybermonkey.net/2009/06/postgresql-how-to-reset-user-name.html