How to store temp tables in memory during table "optimize"?

Please keep in mind OPTIMIZE TABLE is DDL not DML.

In terms of mechanism, this is what OPTIMIZE TABLE mydb.mytable; does under the hood

ALTER TABLE mydb.mytable ENGINE=MyISAM;
ANALYZE TABLE mydb.mytable;

or

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new DISABLE KEYS;
INSERT INTO mydb.mytable_new SELECT * FROM mydb.mytable;
ALTER TABLE mydb.mytable_new ENABLE KEYS;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Since it's DDL, mysqld has no plausible reason to copy the table to memory.

BTW for InnoDB, you could do it manually if you want to skip ANALYZE TABLE mydb.mytable;

ALTER TABLE mydb.mytable ENGINE=InnoDB;

Even if you do ANALYZE TABLE mydb.mytable;, InnoDB will do deep dives into the index anyway to do cardinality approximations (See my post From where does the MySQL Query Optimizer read index statistics?).


How to pivot a table for a set number of combinations?

Using Jonathan Fite's answer from this DBA.SE question and adapting it to your table & data

The CTE ranks the data prior to pivoting twice. You could change the order if you would like to order by something else.

    ;WITH CTE_Rank AS
    (
    SELECT ID_RECIPE,COMPONENT,QTY
        , sComponentName = 'COMP_' + CAST(DENSE_RANK() OVER (PARTITION BY ID_RECIPE ORDER BY COMPONENT) AS VARCHAR(10))
        , sQTY = 'QTY_' + CAST(DENSE_RANK() OVER (PARTITION BY ID_RECIPE ORDER BY COMPONENT) AS VARCHAR(10))
    FROM T
    )

    SELECT ID_RECIPE
    ,  MAX([COMP_1]) as Comp_1
    ,  MAX([COMP_2]) as Comp_2
    ,  MAX([COMP_3]) as Comp_3
    ,  MAX([COMP_4]) as Comp_4
    ,  MAX([COMP_5]) as Comp_5
    ,  MAX([QTY_1])  as QTY_1
    ,  MAX([QTY_2])  as QTY_2
    ,  MAX([QTY_3])  as QTY_3
    ,  MAX([QTY_4])  as QTY_4
    ,  MAX([QTY_5])  as QTY_5
FROM CTE_Rank AS R
    PIVOT(MAX(COMPONENT) FOR sComponentName IN ([COMP_1], [COMP_2], [COMP_3], [COMP_4], [COMP_5])) AS COMPONENTNAME
    PIVOT(MAX(QTY) FOR sQTY IN ([QTY_1], [QTY_2], [QTY_3], [QTY_4],[QTY_5])) AS QTYName
GROUP BY ID_RECIPE;

Result

ID_RECIPE   Comp_1  Comp_2  Comp_3  Comp_4  Comp_5  QTY_1   QTY_2   QTY_3   QTY_4   QTY_5
1           CPA      CPC    NULL    NULL    NULL    1.00    0.50    NULL    NULL    NULL
2           CPA      CPF    CPH     CPJ     CPP     10.00   7.50    3.00    2.25    0.25

Table + Data

CREATE TABLE T
(ID_RECIPE int,  COMPONENT char(3),    QTY numeric(10,2));
INSERT INTO T(ID_RECIPE,COMPONENT,QTY)
VALUES
( 1         ,'CPA',       1.00),
( 1         ,'CPC',       0.50),
( 2         ,'CPA',       10.0),
( 2         ,'CPF',       7.50),
( 2         ,'CPH',       3.00),
( 2         ,'CPJ',       2.25),
( 2         ,'CPP',       0.25);

You could remove the warning of the NULL values by checking prior to applying the MAX() (MAX() is there due to the grouping).

DB<>Fiddle


I was able to fix my own problem. Below is the script I used to complete my report:
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))/3600 AS varchar) + ' hours, '
  + CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))/60 AS varchar)+ ' minutes, '
  + CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) AS int))%60 AS varchar)+ ' seconds'
  AS [Duration],
CASE msdb..backupset.type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN 'Log'
END AS backup_type,
msdb.dbo.backupset.backup_size,
msdb.dbo.backupmediafamily.physical_device_name as 'Physical Location'
FROM msdb.dbo.backupmediafamily
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 1)
ORDER BY
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_finish_date ```


Are TCP loadbalancers an alternative to Postgres 9.X read slaves load balancers like pgPool II or pgBouncer?

There's nothing wrong with it in principle, though I haven't used it myself yet.

Most client applications maintain long sessions - so the load balancing is effectively always "sticky". This can easily lead to one node having a lot more load than other nodes. A workaround can be to intentionally limit connection duration, forcing apps to reconnect occasionally and potentially be assigned a different node. That'll also let you shrink the system when load is light by collapsing work onto fewer nodes, rather than being stuck with lots of nodes, each mostly idle with only one or two active connections.

Your applications must be written carefully to check all return codes / handle exceptions. They must retry transactions that fail due to transient errors like connection loss, which means they have to remember everything they did until they commit each transaction. This is good application design anyway, it's just easier to get away with being lazy about it if disconnects and transaction aborts aren't a normal part of day to day operations.

In theory PgPool can allow "transparent" load balancing to read replicas while still permitting write queries to the master. In practice I find it's usually fiddly and hard to get right, so I usually want to have the app aware of separate "read-only" and "write/master" connections anyway.

The main advantage PgBouncer offers over TCP load balancing is that (in transaction pooling mode) it lets applications maintain idle connections without imposing overhead on the backing PostgreSQL server(s). So you can do your expensive handshake, SSL negotiation, authentication, etc once against PgBouncer, and let it bind the connection to an actual PostgreSQL backend from the pool only when it's doing something. This allows optimal utilization of Pg backends and reduces the number of idle backends sitting around doing nothing except using up resources and waiting for work. This is only valuable if your application can cope with transaction pooling (or statement pooling) mode, though; there's no benefit if you can only use session pooling mode due to use of session variables, listen/notify, advisory locks, or whatever.

If you TCP load balance you should be able to do SSL offloading just like with PgPool or PgBouncer, at least with a smart enough load balancer. You won't be able to do things like client certificate authentication that depends on pg_hba.conf, though.

I tend to scale up rather than out most of the time, so as yet this isn't something I've needed to explore. I've got some work in the pipeline that'll make it very interesting to scale out with Pg, though, so I'll have to play with this.


With an average of 5 relationship rows per task per update if you have 1000 tasks created or updated each hour, you will have enough space in an unsigned integer for 49 years.

You could use a BITINT instead (a 64-bit numeric rather than a 32-bit one), though if you are passing the value into any other code that treats it as a numeric type rather than a string or similar you need to ensure that your target language(s) can handle larger numbers natively.

Or as Melvin suggests, use a UUID type (128 bit keyspace, and generally (if you always generate them properly) portable between systems without keyspace conflict).

As a further option: do you actually need a surrogate key on that table? You could not use task+member as a composite key instead if a person will not be linked to the same task twice?

Edit: As for the delete-before-insert (instead of check-and-insert-or-delete-as-needed): there is no significant problem with this if it makes your code simpler and therefore easier to maintain.

It is less efficient as you are removing some rows that will then immediately get put back as they were, so there will be more I/O activity generally and more actions against rows being logged (relevant if you are using differential backups, log shipping, or so forth) and potentially greater fragmentation within the data files (though this latter effect should be small enough to be safely ignored unless there are many updates per second), and you are using more of your keyspace in that INT PK, but often having simpler code is valuable enough to more than be worth these side effects.

Do make sure that your actions are wrapped in a transaction so that if something goes wrong any changes that were made before the problem are all rolled out together instead of leaving your data in an inconsistent state - though you should do this for the other methods of updating multiple rows in different tables anyway.


The problem as shown is transforming relational calculus, of which SQL is a variant, into relational algebra, which consists of the original operators Codd defined on relations.  I will assume that the EMP, ASG, and PROJ represent employees, projects, and the assignment of employees to projects.  The query, as stated in relational calculus, is asking for the names of employees who aren't J. Doe and who are assigned to the CAD/CAM project for a duration of 12 or 24 months.  The operator in question, natural join, is associative - which means the ability to group the operands in any way without changing the result.  So it does not matter the order in which the EMP, ASG, and PROG are joined.  This is because natural join is really just the product of EMP, ASG, and PROG, then restricted by EMP not J. Doe, ASG DUR 12 or 24, PROG CAD/CAM, EMP.ENO = ASG.ENO, and ASG.PNO = PROJ.PNO, then only the ENAME column projected out.  When you think of it this way, you realize that to first get the product of the rows in EMP, ASG, and PROJ it makes no difference the order you multiply them.  This corresponds to integer algebra, where it makes no difference in what order you multiple 3, 6, and 2.  Thinking of it this way also shows why you can't do the join "in parallel."  You can't take the product of ASG and PROJ, and the product of ASG and EMP, and then... what?  No more than you could take the product of 3 and 6, which is 18, and 6 and 2, which is 12, and then... what? to get what is the right answer - 36.

This is a great question as it shows the job of the optimizer when parsing relational calculus is to first build a corresponding tree of equivalent operators in relational algebra.  There are many ways to build the tree that yield the same result, and as Marco pointed out the optimizer's job is to figure out which of the ways will cost the least.  Part of that cost estimation is determining which access methods (ie indexes) are available to execute each relational operator and the cost of each.  In this way, access methods can be added and removed without impacting the formulation of the query.  This was a huge benefit over the navigation systems which pre-date the SQL DBMS'.  A great reference on this is Fabian Pascal's book SQL and Relational Basics which, while written over 20 years ago still provides the best explanation I have found of these concepts.  It is unfortunately out of print now but you may be able to purchase an old copy.  You can however purchase his Practical Database Foundation Series on his website.  I would also highly recommend any of the writings of Chris Date, Hugh Darwen, and/or David McGoveran on the topic.


Default and temporary tablespaces for installing sample Oracle database schemas?

You can make them any existing temp and permenant TS you want.  I'll bet TEMP and USER already exists. You can answer your own question of 'to they already exist' by simply querying DBA_TABLESPACES.


What is non-page data structure in SQL Server

From the same source that you mentioned on LATCH_EX

  A latch is a lightweight synchronization mechanism that provides
  synchronization between threads trying to read or change a data
  structure in SQL Server. There are three types of latches:

  Latches on pages being read from disk (these are covered by the    PAGEIOLATCH_XX wait types – see the PAGEIOLATCH_SH wait type for
  more details)
  Latches on pages already in memory (these are covered by the    PAGELATCH_XX wait types – see the PAGELATCH_EX wait type for more
  details)
  Latches on non-page data structures (i.e. everything else)

You are interested in the last part:

  Latches on non-page data structures (i.e. everything else)

Which are latches that do not correspond to data pages in memory or on disk.

Further below in the same source is more information on these types of latches:

  The LATCH_SH and LATCH_EX wait types occur when a thread requires
  access to a non-page data structure (e.g., page buffers in the buffer
  pool (latch type = BUFFER), or the data structure that represents a
  database’s data and log files (latch type = FGCB_ADD_REMOVE)).

With two more examples, waiting on access to page buffers in the buffer pool or data and log file data structures.

An example of accessing the data and log file data structure is shrinking / growing the data or log file of your database.

Your latch type, ACCESS_METHODS_DATASET_PARENT is related to parallellism and latch on the threads needed to acquire scan ranges. More on that here and here.

I would start with looking at the query itself and asking a new question with as much information if the LATCH_EX waits are troublesome for you.


Append to Media Vs Overwrite an existing media

WITH NOINIT allows you to retain multiple backups in a single file.  WITH INIT makes SQL Server overwrite the contents of the backup file, if it contains an existing backup.  See the Microsoft Docs for more details about the various WITH options for BACKUP DATABASE.

Personally, I prefer to keep multiple files with unique names if I'm required to keep multiple backups.  Typically, I'd append the date to the name of each file so I can easily determine which backup files are now considered "out of date".  Whether you do that should be dictated by your recovery point objectives and recovery time objectives.

So my backup command would look like:

DECLARE @date date;
DECLARE @filename varchar(260);
SET @date = GETDATE();
SET @filename = 'F:\Backups\xyz\xyz-' + CONVERT(varchar(30), @date, 112) + '.bak';

BACKUP DATABASE [xyz]
TO DISK = @filename
WITH INIT;

Each day, a uniquely named backup file will be created in F:\Backups\xyz similar to:

  F:\Backups\xyz\xyz-20170411.bak

Appending to media can make restoring multiple log backups easier since you won't need to programmatically determine the file names during the restore process.


Is it good to avoid Linked Server by utilizing JOIN task under SSIS package?

You'll have to test to find which works best for your scenario.

By "join task" I assume you mean a Merge Join transformation which requires the input to be sorted. Sorting can be expensive especially if using a Sort transformation. Even sorting at source will take time.

You could try a Lookup transformation which works best using full cache, but if the reference data is large the cache will obviously require a lot of memory.

Another option is to use SSIS to localise the table and then do the join as you would do normally.

Rarely would I use linked servers for anything other than ad-hoc queries.


This depends most likely on how your updates will be accomplished, because

  "
  A trigger is fired for any affected row once. A rule manipulates the query or generates an additional query. So if many rows are affected in one statement, a rule issuing one extra command is likely to be faster than a trigger that is called for every single row and must execute its operations many times. However, the trigger approach is conceptually far simpler than the rule approach, and is easier for novices to get right."

- Postgres Documentation

So it seems that you find  rule simpler is quite convenient in the scenario where you make a large amount of changes to the data. Either should work, though.


Subquerying results in no row returned although records exist

One of the subqueries (derived tables) returns 0 rows and thus the whole query - which is a cross join of 5 tables - returns 0 rows as well.

You could rewrite with proper, explicit joins. If you make them outer (LEFT) joins, then you'll get results if one of the subqueries has 0 rows:

  SELECT EMP.EMPID ID,
         COALESCE(PAID_TOTAL, 0) AS PAID_TOTAL,
         COALESCE(PAID_CURRENT, 0) AS PAID_CURRENT,
         COALESCE(PAID_PAST, 0) AS PAID_PAST,
         COALESCE(PAID_OT, 0) AS PAID_OT
  FROM EMPLOYEES EMP
    LEFT JOIN (
      SELECT EMPID,SUM(AMOUNT)PAID_TOTAL
      FROM PAYMENTS
      WHERE PERIOD<='201207' AND ST=1
      GROUP BY EMPID
      ) PY_TOTAL ON EMP.EMPID=PY_TOTAL.EMPID
    ---
    --- more LEFT joins
    ---
    LEFT JOIN (
      SELECT EMPID,SUM(AMOUNT)PAID_OT
      FROM PAYMENTS
      WHERE PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1
      GROUP BY EMPID
    ) PY_OT ON EMP.EMPID=PY_OT.EMPID ;

But it could be written even simpler, with only one join to Payments table:

  SELECT
      EMP.EMPID AS ID,
      COALESCE(SUM(CASE WHEN PERIOD<='201207' AND ST=1 THEN AMOUNT END), 0)
          AS PAID_TOTAL,
      COALESCE(SUM(CASE WHEN PERIOD='201207' AND PAYMENTTYPE=1 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_CURRENT,
      COALESCE(SUM(CASE WHEN PERIOD<'201207' AND PAYMENTTYPE=1 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_PAST,
      COALESCE(SUM(CASE WHEN PERIOD='201207' AND PAYMENTTYPE=2 AND ST=1 THEN AMOUNT END), 0)
          AS PAID_OT
  FROM EMPLOYEES  EMP
    LEFT JOIN PAYMENTS  P
      ON EMP.EMPID = P.EMPID
  GROUP BY EMP.EMPID ;

This may not be as efficient or need different indexes but it can easily be changed to include in the results only employees with at least one payment:

  FROM EMPLOYEES  EMP
    INNER JOIN PAYMENTS  P
      ON EMP.EMPID = P.EMPID

or employees with at least one payment in the examined period/restrictions:

  FROM EMPLOYEES  EMP
    INNER JOIN PAYMENTS  P
      ON  EMP.EMPID = P.EMPID
      AND (P.PERIOD<='201207' AND P.ST=1)

The (P.PERIOD<='201207' AND P.ST=1) condition is the OR of all the 4 conditions, simplified.


SQL logins Audit for SQL Server 2005/2008

I had thought you would be able to use policy based management but couldn't find a way to get the PasswordLastSetTime property.  It didn't exist in the Login facet.

However what you an do is run the following query to get the PasswordLastSetTime for all SQL Logins on the instance, then use a tool such as Idera's Multi Query or a CMS (Central Management Server) to run it across all of your instances.

SELECT name, LOGINPROPERTY(name,'PasswordLastSetTime')
FROM sys.server_principals
WHERE TYPE = 'S'

Also as stated in the comments by @Reaces if you are a sysadmin or have control server then you can change the password without the old_password.

EDIT:
In response to your comment you can add an additional column to generate the ALTER LOGIN command dynamically.  This does of course assume that you are sysadmin and don't need the OLDPASSWORD keyword added.

SELECT name, LOGINPROPERTY(name,'PasswordLastSetTime'),
    'ALTER LOGIN ' + quotename(name) + ' WITH PASSWORD = ''TemporaryPassword'' ' +
    'MUST_CHANGE, CHECK_EXPIRATION = ON '
FROM sys.server_principals
WHERE TYPE = 'S'

I've also added the MUST_CHANGE clause to force the user to change their password from your generic password the next time they log in.


Negative amount of remaining transactions before wraparound

That just means that your query is bogus.

age(xid) compares the age to the current transaction ID, so it is useless to call it with a multixact ID.

Moreover, running that query will return nonsense for anything except for regular tables (relkind = 'r').

You could try something like

SELECT cc.next_multixact_id::text::bigint - t.relminmxid::text::bigint
FROM pg_class AS t
   CROSS JOIN pg_control_checkpoint() AS cc
WHERE t.relkind = 'r';

to see how old the oldest multixact is.


Treating (A, B) and (B, A) as same pair and getting max row per pair

First, you can do a lateral cross join to

(VALUES (sender_id), (receiver_id)) AS u (user_id)

That will duplicate each source row but from each duplicate pair you will select only the entry where user_id is not the one specified as the argument:

WHERE
  ...
  AND u.user_id <> 2

Now each row will have a group identifier, which is User 2's counterpart in each row. For your example the row set will look like this:

id | sender_id | receiver_id | message | date_created | user_id
1  | 1         | 2           | hi      | 2016-05-21   | 1
2  | 1         | 2           | hello   | 2016-05-22   | 1
3  | 2         | 1           | :)      | 2016-05-23   | 1
4  | 1         | 2           | :D      | 2016-05-24   | 1
5  | 2         | 1           | hehehe  | 2016-05-25   | 1
8  | 3         | 2           | :D:D    | 2016-05-28   | 3

At this point you could use a MAX(uconv.date_created) OVER (PARTITION BY u.user_id) to get each conversation's last date_created. Once you have obtained it, you can get just the row corresponding to that date.

Here is the full query:

SELECT
  id,
  sender_id,
  receiver_id,
  message,
  date_created
FROM
  (
    SELECT
      *,
      MAX(c.date_created) OVER (PARTITION BY u.user_id) AS last_date
    FROM
      uconv AS c
      CROSS JOIN LATERAL (VALUES (sender_id), (receiver_id)) AS u (user_id)
    WHERE
      2 IN (sender_id, receiver_id)
      AND user_id <> 2
  ) AS derived
WHERE
  date_created = last_date
;

As you can see, this method allows you to include the actual message in the results as well.

Alternatively, you could replace the MAX() OVER () with a ROW_NUMBER() OVER () (and filter on the row number, of course):

SELECT
  id,
  sender_id,
  receiver_id,
  message,
  date_created
FROM
  (
    SELECT
      *,
      ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY c.date_created DESC) AS rn
    FROM
      uconv AS c
      CROSS JOIN LATERAL (VALUES (sender_id), (receiver_id)) AS u (user_id)
    WHERE
      2 IN (sender_id, receiver_id)
      AND user_id <> 2
  ) AS derived
WHERE
  rn = 1
;

This is a typical greatest-n-per-group problem. The tricky part was just to determine what to group by.


Just remove all three of the correlated table data files from the file system and that should ensure all traces are removed as per my understanding of 15.2 The MyISAM Storage Engine

Each MyISAM table is stored on disk in three files:

.frm
.MYD
.MYI

  Each MyISAM table is stored on disk in three files. The files have
  names that begin with the table name and have an extension to indicate
  the file type. An .frm file stores the table format. The data file has
  an .MYD (MYData) extension. The index file has an .MYI (MYIndex)
  extension.

  Source

Further Resources

1.6.3 How to Repair MyISAM Tables


Help writing a query to transform rows with json data into 1 download file

Don't store JSON as bytea. Store it as jsonb or json.

Maybe I don't understand your question

ALTER TABLE table
  ALTER COLUMN data
  SET DATA TYPE jsonb USING encode(data, 'escape')::jsonb;

Then just do.. SELECT * FROM table WHERE job_id = 1

  My concern is if the bytea size is too large, I need to get one-row at a time, and extract the data from it.

Why? What does too large mean there? The way this works, you'll only encode one row at a time and send it to the client...


The minimum permission in database to make a user "see" all the users is VIEW DEFINITION.

This permission does not permit the user to alter anything, but it opens to user every definition of database objects: tables, procedures, etc.


How can I set a default now() timestamp in a timestamp array?

You need to use the array[] syntax:

create table messages(
    id serial primary key,
    val text not null,
    testarray timestamp[] not null default array[now()]
);

insert into messages(val) values ('blah');

select * from messages;

http://sqlfiddle.com/#!17/c3a31/4


As was pointed out, you got the CASE syntax wrong.
You also don't need sub-blocks, parameters, multiple distinct UPDATE statements or even the (broken) PL/pgSQL CASE construct. Just an SQL CASE expression in the WHERE clause of the UPDATE:
CREATE OR REPLACE FUNCTION upd_sys_table()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE sys_table
   SET    last_modified = EXTRACT(epoch FROM timezone('UTC', now())) * 1000
        , modified_by   = NEW.modified_by
   WHERE  sys_table_name = CASE WHEN TG_TABLE_NAME LIKE 'candy\_%' THEN 'xvw_candy'
                                WHEN TG_TABLE_NAME LIKE 'fruit\_%' THEN 'xvw_fruit'
                           END;

   IF NOT FOUND THEN
      RAISE NOTICE 'No match';
   END IF;

   RETURN NEW;
END
$func$;

Subtle difference: this assumes that rows for 'xvw_candy' and 'xvw_fruit' always exist in sys_table. Else, there is an additional path to NOT FOUND.
Don't confuse SQL CASE with PL/pgSQL CASE. Same keyword, different language, subtly distinct syntax. Among other things, SQL CASE does not require an ELSE branch and defaults to NULL.
If the use case is as simple as your example suggests, you can even just look at the leading 5 characters and use the simpler "searched" CASE like you had in mind:
WHERE  sys_table_name = CASE left(TG_TABLE_NAME, 5)
                           WHEN 'candy' THEN 'xvw_candy'
                           WHEN 'fruit' THEN 'xvw_fruit'
                        END;

Or even just:
WHERE  sys_table_name = 'xvw_' || left(TG_TABLE_NAME, 5)

See:

Issue with composite type in an UPSERT function

Aside: it's typically better to store an actual timestamptz instead of an epoch as bigint. See:

How to best store a timestamp in PostgreSQL?


Beginner SQL: Unexpected results from GROUP BY

This answer is a bit unlike the others, but I understood your question to be more related to "what is the logic behind adding/removing columns to/from the SELECT and GROUP BY".  If it's out of place, please let me know and I'll delete.

It's not really a quirk, it's just a basic rule of aggregation.
If I'm counting people that live at my house (3 total), I count people that live at my address.  So I can say

SELECT Address
      ,COUNT(PersonID) AS PeopleCount
FROM Household
GROUP BY Address

That will give me the number of people at my address.

If I try to do it by name:

SELECT Address
       Name
      ,COUNT(PersonID) AS PeopleCount
FROM Household
GROUP BY Address
        ,Name

Then it will give me a "1" value in People count as there is only 1 person per address per name in my household.

If I had 2 people with the same name living at my house, then the "PeopleCount" value for those two people would be "2" until I found something else to add to make the records unique.

So the reason you add items to the SELECT/GROUP BY is to perform aggregates based on UNIQUE records.  The more columns you add to the SELECT statement, the more unique the records are.


Database full backup,differential backup and log backup plan

I'm going to try to save you a lot of time messing with outdated and mostly unnecessary backup options. Just use Ola Hallengren's free scripts.

Next up, deciding backup frequency is a business decision as it relates to RPO and RTO. No one here can tell you how much data you're allowed to lose. One thing though: if you're thinking about taking log backups once a day, you shouldn't be in full recovery model.

That also relates to: DON'T SHRINK YOUR DATABASE. Your log file grows because you don't back it up either at all, or often enough. If you shrink it, it will grow again, and that's just not a smart way to administer a SQL Server. Either back it up more often, or switch to SIMPLE recovery.

That decision is between you and management, though.


How to test whether unique index can be created in MongoDB?

You can use an aggregation query to check for duplicates before trying to create a unique index.

For example, if you want to create a unique index on fields a and b:

Insert some test data:

db.mycollection.insert([
    {a: 123, b: 456},
    {a: 123},
    {a: 123, b: 456},
    {c: 789 },
    {d: 321 }
])

Find documents with duplicate values:

db.mycollection.aggregate([

    // Group by the field combination you plan to add a unique index on
    { $group: {
        _id: { a: "$a", b: "$b"},
        count: { $sum: 1 }
    }},

    // Find any groups which are not unique (more than 1 matching document)
    { $match: {
        count: { "$gt": 1}
    }}
])

Aggregated output for the sample data above would indicate that there are two documents missing the expected fields a and b, and two documents with duplicate values of a and b:

{ "_id" : {  }, "count" : 2 }
{ "_id" : { "a" : 123, "b" : 456 }, "count" : 2 }

In this example I intentionally included some documents with expected fields missing, since those will be indexed as null values if you create a unique index. By default the unique requirement still applies even if one or more fields in the unique index is missing for a given document, because all documents in the collection are added to the index.

If you only want the field combination to be unique if both fields are present (or using some other criteria) you can create a Partial Index with Unique Constraint.


DB2 - question regarding RESTORE OFFLINE backup and Archive Logs

Yes, you can issue ROLLFORWARD after restoring an offline backup. The ROLLFORWARD command syntax remains the same, regardless of the type of restored backup image.


mongodb conf node won't start - compatibility error

Went with the nuclear option:

stopped the orphaned server
moved the data dir and recreated new dir
removed orphaned server from primary
started orphaned server with null data dir
added orphaned server to pool from primary

data started sync'ing immediately and rs.status() for the cluster config servers reporting all is well.


How can I get dates that wrap around new year from non-datetime columns?


How can I rename a table, but also all FK etc. references to that table?

You should be able to generate the appropriate rename scripts using this, and adjust it to whatever naming convention you choose;  I've used this to ensure that the names use the current table and column names, so the where clause currently finds names that don't follow that convention.

SELECT
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    FK_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME,
    'FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME,
    'sp_rename ''' + C.CONSTRAINT_NAME + ''', ' + '''FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME + '''' + char(13) + char(10) + 'GO' + CHAR(13) + CHAR(10)
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
    INNER JOIN (
        SELECT i1.TABLE_NAME, i2.COLUMN_NAME
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
        ) PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE
    C.CONSTRAINT_NAME != 'FK_' + FK.TABLE_NAME + '_' + PK.TABLE_NAME + '_' + CU.COLUMN_NAME
ORDER BY
    PK.TABLE_NAME, FK.TABLE_NAME


Free...

I'm not 100% sure that there's a good, free, open source alternative, if that's what you're looking for. The closest you are likely to find is Orange, a data mining and visualization tool.  For a little bit of instruction, just do a little Googling, or consult helpful Youtube videos such as the one linked.

or Paid?

If you're willing to pay for it, I would highly recommend using Tableau.  They have a free version, called Tableau Public, which only works with flat files, but will allow you to do some basic testing. The licensed version allows native connections to many databases, including Postgres. Lastly, if you are a larger enterprise client who has a business model which permits cloud storage, Chartio may also be a good alternative.

Full disclosure:  I have no business connections to any of the companies mentioned, and my post is meant to be purely informative and non-promotional for the companies listed.


Just like the error says, you can't have aggregates in the WHERE clause.  WHERE is logically before GROUP BY, so it filters rows before they are grouped, not after.

For filtering the groups, use HAVING (or push the GROUP BY into a subquery).  eg

Declare @Hamburger Table (empName varchar(200), e float, a float, saleid varchar(10))

Insert Into @Hamburger (empName, e,a, saleid) Values
    ('Jake', 362, 233, 'E111'), ('Jake', 1431, 2702, 'E112'), ('Blue', 849, 280, 'R222'), ('Blue', 1418, 299, 'R390')

Select empName, e, a, saleid, AmtGained = Sum(Coalesce(e,0))-Sum(Coalesce(a,0)),
DiscountPercent = Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0)
From @Hamburger
Group By empName, saleid, e, a
having ((Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) >= .70)
OR (Sum(Coalesce(e,0)-Coalesce(a,0))/NullIf(Sum(Coalesce(e,0)),0) <= -.70))
Order By empName ASC


Question about encrypting multiple MariaDB schemas on the same server

In the documentation it says each table can have a manually specified encryption key number.

ALTER TABLE my_db.my_encrypted_table
   ENCRYPTED=YES
   ENCRYPTION_KEY_ID=3;

If each database1 has all its tables having a different encryption key number from database2 tables' then they are using different keys.


UFT to SQL Server conecction which is located at different domain

The uid & password attributes are used for SQL Server Authentication. You cannot use those connection string values with Windows Authentication. I suspect you are getting login failures with error 18456, state of either 2 or 6. The state portion of the failure message will give you the detailed reason for failure. This post by Aaron Bertand gives a detailed rundown of most/all possible failure reasons.

If you want to use Windows Authentication to connect to SQL Server from a different domain, you would need to have a trust established between the two domains. With the trust established, you would replace the uid & password attributes with Trusted_Connection=True;

If your second domain is not trusted, and you cannot establish a trust, then you would need to use SQL Server Authentication instead. You would need to create the SQL Server login, and then your existing connection string format would work.


Memory issue in Transactional Replication

The SQL Server Snapshot Agent will use BCP to create an initial snapshot of the data and objects to be replicated.

These are stored on disk in the default installation folder. You can specify a different location if you are concerned about space issues in the default location.

Once the snapshot has been distributed to the subscribers, subsequent changes will be stored in the distribution database and sent to the subscriber at the frequency you choose.

If you don't need to create and send a snapshot every day you might save time and bandwidth only sending the differences for a while before refreshing the snapshot.


selecting and Inserting data from two different servers

If the duration is related to data volume, you should consider syncing only the modified data:

put an IsDirty flag on each student record
sync (delete + insert or simple updates) data only for records where IsDirty = 1

This should work decently if a fairly small amount of records change between the sync.

Another option is to try to sync data directly between MySql and PSql as indicated here. Also, take a look upon dblink.


SQl server 2008R2 Performance issue after SP3 update

Any one has idea that SP update causing any issue?

The security KB (KB4057113) is for the spectre / meltdown group of CPU security attacks.  They have been known to cause some performance slowdowns, but nothing that drastic.  Still, I thought it would be worth mentioning in an answer.
You may want to install the latest operating system patches to make sure you have the latest and greatest changes on that side of things (the initial patches addressed the security flaw, while some subsequent ones addressed performance).
See this Q&A on the site for more information on what you need to do:
As a SQL Server DBA, what do I need to know about the meltdown/spectre vulnerabilities?
Other than that, you should get execution plans for the slow procs from before and after the update, and compare them.  The more likely culprit is a plan change, which could be based on a number of factors (often unrelated to the update, like parameter sniffing).  Feel free to share them here to get specific help with them.


Windows Server 2016 Active Directory-Detached Cluster - Cannot add a Client Access Point

How a client access point can be added to the cluster?

You'll have to use PowerShell. No AD means no AD and you lose all of the great functionality and brevity it offers.

  I need to add a client access point to use it later in SQL Server Availability Group Listener, to use it for auto-failover between mirrors.

Between replicas, you mean? Again, you'll need to use PowerShell.

There is an upcoming blog post [(placeholder)] blog post I wrote detailing this. When it is officially published, I'll update the answer with a link. In the interim, here is the basics of what you'll need to do this.

You may want to re-think using clusters like this (workgroup) as this is just the tip of the administrative iceberg you'll be dealing with.

Add-ClusterResource -Name "IPAddress1" -ResourceType "IP Address" -Group "WGAG"
Get-ClusterResource -Name IPAddress1 | Set-ClusterParameter -Multiple @{"Network" = "Cluster Network 1";"Address" = "20.250.250.9";"SubnetMask" = "255.0.0.0";"EnableDHCP" = 0}
Add-ClusterResource -Name "IPAddress2" -ResourceType "IP Address" -Group "WGAG"
Get-ClusterResource -Name IPAddress2 | Set-ClusterParameter -Multiple @{"Network" = "Cluster Network 2";"Address" = "30.250.250.9";"SubnetMask" = "255.0.0.0";"EnableDHCP" = 0}
Add-ClusterResource -Name "TestName" -Group "WGAG" -ResourceType "Network Name"
Get-ClusterResource -Name "TestName" | Set-ClusterParameter -Multiple @{"DnsName" = "TestName";"RegisterAllProvidersIP" = 1}
Set-ClusterResourceDependency -Resource TestName -Dependency "[IPAddress1] or [IPAddress2]"
Start-ClusterResource -Name TestName -Verbose

Updated with comments:

  ... now we are trying to setup cluster on SQL Server 2016 and Windows Server 2016 without AD (just to make everything simplier).

This does not make things simple. It actually creates much more manual of an environment. It is possible to automate things, but most of the common automation tools or powershell scripts aren't going to work as most assume either a standard login on each server (security issue) or implicitly use Windows Authentication which you now, won't have.

Additionally there is the aspect of SQL Server where you'll lose all the support of AD. No Kerberos, all SQL logins (or local windows logins - which are widely not useful in these scenarios) and no groups, etc. Setting up the AG will need to be completed through certificate endpoints if you want to keep things secure and you won't be able to use any GUIs in order to set it up end to end.

To reiterate, it doesn't make anything simple - only more complex. These types of configurations should only be used where they are expressly needed.

If you want to continue going this way, that's your prerogative... I can only give advice and answers.


Control SQL Server pace of db shrink to split into multiple files

You don't need to shrink to migrate data to multiple files.
Simply add the new files tot the filegroup, then rebuild each table one-by-one to move the data around.
The proportional fill algorithm will take care of equally balancing the data between the files, assuming you size the files in an optimal way.  See this Microsoft Docs page for details.
Use the ALTER TABLE ... REBUILD; command to migrate the data across all available files in a filegroup.  You can even use that command to migrate a table to another filegroup.  Microsoft Docs for that command are here


Known vulnerabilities for when a guest executes a stored procedure on SQL Server

I would first question why they need direct access to the database. You might ask your manager or legal department if the security policy for the company allows granting this type of access. Is it really needed if they are just going to execute a stored procedure on a regular basis.

As you stated this is going to be on a regular basis I would setup an SSIS package to export the information to a file. Then either have the package email the file (if not to large) or put it on a UNC directory for them to come and download themselves. If they want to determine when the data is pulled down setup a script for them to execute outside of SQL Server that simply runs the package.


ibdata1

The file ibdata1 is the system tablespace for the InnoDB infrastructure.

It contains several classes for information vital for InnoDB

Table Data Pages
Table Index Pages
Data Dictionary
MVCC Control Data

Undo Space
Rollback Segments

Double Write Buffer (Pages Written in the Background to avoid OS caching)
Insert Buffer (Changes to Secondary Indexes)

Click Here to see a Pictorial Representation

You can divorce Data and Index Pages from ibdata1 by enabling innodb_file_per_table. This will cause any newly created InnoDB table to store data and index pages in an external .ibd file.

Example

datadir is /var/lib/mysql
CREATE TABLE mydb.mytable (...) ENGINE=InnoDB;, creates /var/lib/mysql/mydb/mytable.frm

innodb_file_per_table enabled, Data/Index Pages Stored in /var/lib/mysql/mydb/mytable.ibd
innodb_file_per_table disabled, Data/Index Pages Stored in ibdata1

No matter where the InnoDB table is stored, InnoDB's functionality requires looking for table metadata and storing and retrieving MVCC info to support ACID compliance and Transaction Isolation.

Here are my past articles on separating table data and indexes from ibdata1

Oct 29, 2010 : My Original Post in StackOverflow
Nov 26, 2011 : ERROR 1114 (HY000) at line 6308 in file & The table user_analysis is full
Feb 03, 2012 : Scheduled optimization of tables in MySQL InnoDB
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Apr 01, 2012 : Is innodb_file_per_table advisable?

iblog files (a.k.a. ib_logfile0, ib_logfile1)

If you want to know what the ib_logfile0 and ib_logfile1 are for, they are the InnoDB Redo Logs. They should never be erased or resized until a full normal shutdown of mysqld has taken place. If mysqld ever crashes, just start up mysqld. It will read across ib_logfile0 and ib_logfile1 to check for any data changes that were not posted to the the double write buffer in ibdata1. It will replay (redo) those changes. Once they are replayed and stored, mysqld becomes ready for new DB Connections.


Migrating Sql Cluster Installation To New Drive

Your steps to do this are pretty much right, I've added some steps and changed the wording on a few items.

Some SAN vendors provide migration software so you only have to turn off the cluster nodes for a few minutes and they will migrate the data behind the scenes.  You take a breif outage and a performance hit until the migration is complete, but you don't have to manually copy the data over or have to change the drive letters.  It's worth looking into for having 13 instances.

Create LUNs on new SAN.
Present new LUNs to the cluster.
Copy everything from old clustered disk resource to new clustered
disk resource.
add new disk to the cluster group
change old disk letter to an unused disk letter or remove disk letter
entirely
change disk letter for new drive to be the same as old disk letter
change dependencies in the cluster resources to the new disk
remove old disk from cluster group
bring SQL resource online


Why does FREEPROCCACHE let you shrink tempdb?

This answer is based on comments under the question by @MrTCS pointing to this link and by @KrisGruttemeyer with this summary

  TL;DR - "What is important is that the cached objects are tied to a query plan and that by freeing the procedure cache you can make those objects go away, allowing you to shrink your files."

As I understand it:

What is happening when you can’t shrink tempdb, as there is a pointer (or something) physically located on the last page(s) of the datafile.  It points to the query plan that caused tempdb to fill.  That page will not be released as long as that query plan exists.  The nuclear option is DBCC FREEPROCCACHE, which clears all the query plans, and the page is released.


How to create several builds of PostgreSQL?

If you use the --prefix=PREFIX option while building from source, won't that help? You would also need to look at other options such as --with-pgport=NUMBER to ensure that you don't end up forgetting to change the port in postgresql.conf. Please have a look at http://www.postgresql.org/docs/current/static/install-procedure.html for other options you might want to change.


Postgres Restore Not Restoring Bytea Data

We finally were able to restore the backup. We tried so many things that I am not sure what the fix was, but our conclusion is that the problem was the bytea_output setting in postgresql.conf.
We think Heroku was using value of 'escape' while our database server was using 'hex' and this caused the problem.


insert in Mysql innodb table extremely slow

Running insert/update queries on an InnoDB table is significantly slower when no transaction is started. I've run the procedures you listed and the result was that load_innodb_table was actually faster than load_myisam_table.
Running load_innodb_table with 10m inserts actually means that you're running 10m transactions, as every insert implicitly starts a transaction.


SQL Server 2008, SOAP Endpoint, http.sys, timeout

For anyone who might stumble into this, too.
It turned out to be not a matter of timeouts, but sizes of the POSTed payload. The transfer would always get stuck if the size of the posted parameter would be over a certain threshold (somewhere between 8 and 9 Mb).
What fixed it was setting the SSL version to 3 as one of the CURL options (we have a custom SOAP client library).

A big complication in debugging it in our case was that certain servers were behind a load balancer built on Nginx proxy. For those, the call worked fine! But not for the ones that we connected to directly. My guess (I am no specialist in neither Nginx nor SSL) is that Nginx has enough compatible interfaces on "both ends" and was able to receive the call and forward it to the server properly.

What lead to the solution was trying communicating from different clients. When it worked fine from SoapUI but not from our library it was only a matter of prying on every detail of the connection parameters until something made a difference.


What is the best type of account to use for SQL services?

I generally start with AD accounts because someday, someone's going to want to join this server into an Availability Group. Then, they're going to wonder why they have problems with Kerberos authentication. Books Online states:

  All server instances that host an availability replica for the availability group must use the same SQL Server service account.

So I just head that off at the pass by using an AD account for any SQL Server, and then if I end up needing to add it to an AG later, all I have to do is set up the other replica with the same service account.


Does TokuDB need any regular cleanup/maintenance?

Percona has a github page on the following

OPTIMIZE TABLE (which references another Percona Blog)
ANALYZE TABLE

There is lots of info explaining why.

For example, there are messages stored in non-leaf nodes of Fractal Trees. All those messages are pushed way down to leaf nodes. This simplifies retrieval of stats needed by the Query Optimizer.

Please read those links and learn way more.


How to solve ORA-011033: ORACLE initialization or shutdown in progress

ORA-00600 is Oracle's generic message for un-handled internal exceptions i.e. bugs.  Usually they require us to raise an SR with Oracle Support.    This can be a problem for sites who are using Oracle without a support contract.  In that situation we are thrown on the resources of the internet.

The problem you are experiencing is due to a corrupted block.  The top result in Google offers a simple workaround: find out more.

SQL> shutdown immediate;
SQL> startup mount;
SQL> recover database;
SQL> alter database open;

(Of course, this is only a solution if you are running in archivelog mode, and so can recover the lost block).


Why DB2 cannot allow creation of database with keyword "BACKUP" in its name?

There's no restriction on the specific name, other than it must be 8 characters or less.  So BACKUP works just fine as a database name:

$ db2level
DB21085I  This instance or install (instance name, where applicable:
"db2inst1") uses "64" bits and DB2 code release "SQL10055" with level
identifier "0606010E".
Informational tokens are "DB2 v10.5.0.5", "s141128", "IP23633", and Fix Pack
"5".
Product is installed at "/opt/ibm/db2/V10.5.5".

$ db2 "create database backup"
DB20000I  The CREATE DATABASE command completed successfully.


If I understand your question correctly, you basically want to join your [Table1] to [Lookup Table] whenever [Table1].[Description] is like [Lookup Table].[SearchString].

You can try this:

select * from Table1
join [Lookup Table]
on Table1.Description like ('%' + [Lookup Table].SearchString + '%');

This will only return records where there is a match on the like condition. You can adapt this to an outer join if you need all rows from one of the tables, as required.

I won't vouch for the performance of this query. Also, it is an adaptation of another question on Stack Exchange.


Is there a way to view the body / contents of a system function in sql server 2008

I don't think Sum is stored as a T-SQL function in your system that you could "peek" into. That's most likely code deep inside the SQL Server core.

As a proof - you can e.g. get the body of other system stored functions using sp_helptext:

sp_helptext 'sys.fn_isrolemember'

That works just fine - so that leads me to believe that Sum isn't a stored function as such.


Group_by and having vs Joins when table is self joined 5-6 times

With 7 books, my guess would be that 7 joins is faster than GROUP BY / HAVING.

But it depends on the DBMS, the version, the optimizer's settings, the database settings, the RAM you have, the hard disks' performance, the indexes fragmentation, the overall pressure on the server and possibly several other parameters. Even more, even all the previous are set still, it depends on your data (and its distribution) and the specific parameters of the query. For example, if the 7 books are the 7 books of Harry Potter and all your users are Harry Potter's fans, then the GROUP BY/HAVING may be faster.

Plus, you shouldn't trust others, however experts they may (look to) be, when you can test. Why don't you test - with your data in your server and your settings - the performance of both ways, using variable number of books (and titles)?

Check also this question (with a similar query) where several other (more than 10) ways are shown (and benchmarked in PostgrSQL): How to filter SQL results in a has-many-through relation

UPDATE

explanation of the "guess" that 7 Joins are usually better than GROUP BY / HAVING:

Imagine you have a million users and about a million books. Now, on average, a user has read 100 books (in your database, totally fictional data and distribution). So, the table has about 100M rows.

Now, the GROUP BY query would have something like WHERE book_id IN (1,2,3,4,5,6,7). Lets assume that book_id=1 is the most popular (the Bible) and has about 100K readers and the other 6 are not so popular, having between 100 to 1000 readers each. This would limit the rows to be grouped to something between 100K and 106K. That translates (roughly) to the SQL engine reading 106K data from the proper index and then doing the GROUP BY user_id. So, (it will probably choose to use the (user_id, book_id) index), and it will do about 100K calculations of the COUNT(book_id) - and reject anyone that is not 7.

In the 7 JOIN query, it has more options. The optimizer may choose to use the other index, the (book_id, user_id) one. Imagine "taking out" 7 smaller parts of this big index, the (1, user_id) part (remember: 100K data (user_ids) in it), the (2, user_id) part (less than 1000 data in here), ..., up to the (7, user_id) part (less than 1000 data in here, too). So now, it has to somehow combine those 7 index-parts (which is just 7 lists of userids) and find which userids are in ALL of the 7 lists. There are some clever algorithms that do just that, without having to do a whole reading (full scan) of the 7 lists. Just notice that even a dumb algorithm that combines first the 6 smaller lists, may end up with only a handful of userids (lets say just 1). To find if this 1 user_id is in the big (first) list, only a binary search is needed (remember it's not really a list, it's an index and that's what is good about indexes, you can search fast in them). So, even if there are only 100 user_ids, 100 searches in the big 100K list/index will only need less than 100*17 operation ( log(100K) ~= 17 ). Which is 1700 operations, much less than the GROUP BY 100K operations. And no COUNT(*) is needed.

Therefore, with the Joins, if most books are not very popular (or just one book and we get lucky), the query will be quite efficient because it will have to look at the index on a very small number of places.
(Another thought is that with the Group By method, the query has calculated - before rejecting them - how many books have read all those users that have read 1 or 2 or ... or 6 books. But we don't care if they read 1 or 6. We just need to know if they have read all 7 !)

The situation is different off course if all the 7 chosen books are very popular. Now, the 7 index parts are all big and combining them may be less efficient than using the GROUP BY method which uses only one pass at one index.
(And the other thought says that the Group By is efficient now because almost all Count calculations will be a 7, so a very small number of calculations is wasted)


Are non 'copy-only' full backups safe to recover from?

However, there is no need to do point in time restore on any of them at this point.

If this is the case you MUST put database in simple recovery mode and this will take care of the transaction logs. When database is in simple recovery mode you can take two types of backup
1. Full
2. Differential

Depending on how much data you can loose you can take daily full backup and then may be differential backup twice or 4 times a day just to make sure you loose as less data as possible. When you restore from backup you would have to restore the latest full backup and the latest differential backup taken after the latest full backup. You cannot take transaction log backup in simple recovery model SQL Server will itself manage your transaction log truncation.

Please read Understanding Logging and Recovery in SQL Server

  Am I right in thinking that a database can be restored from a full-backup at the time that full-backup was taken

Yes you are absolutely correct. Please read Understanding Backups in SQL Server


Make sure the directory where you want to write the data files is accessable for MySQL. I put it the security settings on Everyone.

You might need to copy the data also from old data directory to the new one.


You should be able to do this using the either glogin.sql or login.sql. The former is run whenever anyone logs into SQL*Plus and the latter is specific to a user. Im not currently in a position to install it myself to try out but this link to the Oracle docs should help.
Configuring SQL*Plus


MS SQL Server 2012: once a day full replication (automatic backup/restore) for reporting

We want to handle schema changes

Transactional Replication would be hard to maintain. If you do frequent schema changes, then you have to drop replication and recreate it, which is time consuming and on the top of it, you have to maintain distribution database as well. Network latency also plays a big role if you are replicating your entire database.

For you scenario, I would recommend :

If not on enterprise edition, you can use any of below :

Backup and restore with COMPRESSION. SQL Server native compression is very efficient which will reduce the backup size.
Since data can be stale up-to 24 hrs, another option would be to use logshipping. You just have to delay the restore of the log backups on the secondary server. So when you want to update the data on the reporting server, you can kickoff the job manually or it will get kicked off with the schedule. This way, you just have to restore the T-logs on the secondary server, when you want to refresh the data in the reporting (secondary) server.

So when setting up Logshipping, configure the secondary database in STANDBY mode and make sure you select Disconnect users in the database when restoring backups.

If on Enterprise edition, then AlwaysON with readable secondary. This will offload the reporting work from your primary server to the replica.

Note: For completeness of this answer, even though Database Mirroring is announced as Deprecated, it is still supported in SQL Server 2012. You have to be careful, as the database snapshot name will be different from the original database name, so if your app relies on the database name, it will need modification. Also, as snapshot works on "COPY ON WRITE" method, it can be significant overhead if there are multiple snapshots. Also, it will randomizes IO during query processing.


SQL Server - Restore only primary Filegroup but bring the database online

I know you mentioned it's a little bit out of your control, but if you don't need the data at all and just the schema itself, doing a backup and restore on a 2 TB database is going to be the least efficient way to accomplish your goal, by far, among probably 10 different options. (You should familiarize yourself with the alternatives and bring this to the attention of whoever's control it is in.)
Some of those alternative options are Replication (as you mentioned), SSIS, SQL Agent Job that keeps the other instances up to date, scripting out the database, or using a DACPAC to deploy just the schema, which would be my choice for you as it's the simplest / makes the most sense. (Also if your limitation in using a different features is because you're not allowed to setup anything on the server, DACPAC is an already enabled feature, and the process is simpler than backups since you just use SSMS to generate the file for you, similar to if you were generating scripts. No need to install or setup anything.)
If your schema doesn't change often, you can even dedicate one empty copy of the database as your "model" which always remains data-less and can easily be used to generate new databases off of (even if you still stick to the backup and restore methodology, since it'll be a tiny database). Or even if it does change often, you can still do the backup and restore to just this one model database, and then truncate the data or use a DACPAC there to generate the other instances so that way you don't need to wait on a 2 TB restore more than once.


Alternate to use LIMIT in subquery in mysql?

If 2 is a fixed value and you'll never need to compare against 3, 4 or more items in the future, then you could convert the subquery to a derived table and find the 2 values with aggregation:

select r.res_id, r.res_name
from Resource as r
     join
     ( select min(lim.ran_resid) as ran_resid_1,
              max(lim.ran_resid) as ran_resid_2
       from
         ( select v.ran_resid
           from views as v
           order by v.viewsid
           limit 2
         ) as lim
     ) as x
     on r.ran_resid NOT IN (x.ran_resid_1, x.ran_resid_2)
 ;

A more general solution (where you can use any LIMIT and not just 2), is to write the antijoin with the LEFT JOIN / IS NULL method:

select r.res_id, r.res_name
from Resource as r
     left join
     ( select v.ran_resid
       from views as v
       order by v.viewsid
       limit 5
     ) as x
     on r.ran_resid = x.ran_resid
where x.ran_resid is null
 ;

An NOT EXISTS version should work as well but I'm not sure that it would be very efficient, due to the double nesting:

select r.res_id, r.res_name
from Resource as r
where not exists
  ( select 1
    from
      ( select v.ran_resid
        from views as v
        order by v.viewsid
        limit 5
      ) as x
    where r.ran_resid = x.ran_resid
  ) ;

Also note that I removed the last group by r.ran_resId from your query.

It makes no sense to group by a and select b, c in a query. And while MySQL allows it, it is a guaranteed way to get garbage results.


Mysql Query Take Longer To Prcoess On My Dedicated Server Than My Local Server

There are other things that can come into play when dealing with a production vs a development environment such as fragmentation/load/locks/amount of data/etc. I would first start by comparing the plans in mySQL using EXPLAIN. This will give you more information as to what the problem is.


Convert columns into rows for use with indexed/materalized view

Based on your description, I think it's fairly unlikely that an indexed view is going to be your best solution. The indexed view will add overhead each time you write to UserAuditTbl, and a CROSS APPLY that unpivots each row into two rows should be quite efficient if you are careful to first seek and/or filter to only the necessary rows (e.g., only those rows for a (UserID, UserAttribute) tuple you are interested in).

If you post a full example of your exact table (including indexes), a script to fill your table to the expected number of rows with fake data, and the exact T-SQL you need to optimize, we might be able to provide more specific suggestions.

However, if you decide to try the indexed view approach, one fairly easy way to implement your CROSS APPLY as a join is to create a table that contains one row per value in your CROSS APPLY, then join to that table. For example:

Create the necessary tables

/* Your sample data */
CREATE TABLE dbo.UserAuditTbl (
    RowID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_UserAuditTbl PRIMARY KEY,
    UserID VARCHAR(10) NOT NULL,
    UserAttribute VARCHAR(100) NOT NULL,
    OldValue VARCHAR(100) NOT NULL,
    NewValue VARCHAR(100) NOT NULL
)
INSERT INTO dbo.UserAuditTbl (UserID,UserAttribute,OldValue,NewValue)
VALUES  ('ID00184','FirstName','John','Jon'),
        ('ID00184','LastName','NULL','Albert'),
        ('ID00185','HomePhone','555-555-1122','555-555-1212'),
        ('ID00188','MiddleName','Jesse','James'),
        ('ID00188','CellPhone','555-555-1234','555-555-1555'),
        ('ID19594','ZipCode','00000','90210')
GO

/* Create a two-row table to implement the CROSS APPLY as a join */
CREATE TABLE dbo.OldAndNewValues (
    OldOrNewValue CHAR(8) NOT NULL
        CONSTRAINT PK_OldAndNewValues PRIMARY KEY
)
INSERT INTO dbo.OldAndNewValues (OldOrNewValue)
VALUES  ('OldValue'),
        ('NewValue')
GO

Create the indexed view

/* Create a view that implements the cross apply as a join */
CREATE VIEW dbo.UserAuditIndexedView WITH SCHEMABINDING AS
SELECT u.RowID,
    u.UserID,
    u.UserAttribute,
    v.OldOrNewValue AS Col,
    IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) AS Val
FROM dbo.UserAuditTbl u
CROSS JOIN dbo.OldAndNewValues v
WHERE IIF(v.OldOrNewValue = 'OldValue', u.OldValue, u.NewValue) IS NOT NULL
GO

/* Create the view index.
    You can adjust the column order as necessary for your audit queries.
    Note that the first index on a view must be a unique index,
    so the RowID and Col columns must be used. */
CREATE UNIQUE CLUSTERED INDEX UQ_UserAuditIndexedView
ON dbo.UserAuditIndexedView (UserID, UserAttribute, RowID, Col)
GO

Confirm that the results match

/* Use the view index */
SELECT *
FROM dbo.UserAuditIndexedView WITH (NOEXPAND)
ORDER BY RowID, Col

/* An adapted version of your original query (the original does not compile) */
SELECT RowID, UserID, UserAttribute, Col, Val
FROM dbo.UserAuditTbl
CROSS APPLY (
  VALUES ('OldValue', OldValue), ('NewValue', NewValue)
) x (Col, Val)
WHERE Val IS NOT NULL
ORDER BY RowID, Col
GO


Merge Table Row Values to Header of Another Table

Check this where I answered a similar question on Stack Overflow.

You can do it with dynamic sql:

DECLARE @fieldName varchar(32), @title varchar(32)
DECLARE @sql_cmd varchar(512)
SET @sql_cmd='SELECT id, '

DECLARE sql_cursor CURSOR FOR
SELECT Field, Values FROM table1 ORDER BY id

OPEN sql_cursor

FETCH NEXT FROM sql_cursor INTO @fieldName, @Title

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql_cmd = @sql_cmd + @fieldName + ' AS ' + @Title + ', '

    FETCH NEXT FROM sql_cursor
    INTO @fieldName, @Title
END;

CLOSE sql_cursor
DEALLOCATE sql_cursor

SET @sql_cmd = SUBSTRING(@sql_cmd,0,LEN(@sql_cmd)-2) + ' FROM table2'
EXEC(@sql_cmd)


Is there a way to show a string instead if numeric column has specific value?

As you want to mix numeric and string values in the same column you need to convert the numbers to strings as well.

select object_id,
       case
         when weight < 0 then 'unweighted'
         else weight::text
       end as weight
from the_table;

As that column is no longer a number, you can not sort or compare it properly any more. If you want to put that into a view, I suggest you create an additional column for this purpose:

select object_id,
       weight,
       case
         when weight < 0 then 'unweighted'
         else weight::text
       end as display_weight
from the_table;

However it would be cleaner if you don't use "magic numbers" to mark the absence of data. Precisely for this reason the NULL value was created in SQL (although using NULL won't solve the problem of mixing numeric and string values in the same column).


SQL Server - Copy data from restored version to live database table

Note: While I believe these steps to be accurate I take no responsibility for any lost data. Use at your own risk.

Right click on the databases folder in object explorer (it should be listed beneath your server name)
Choose device and click on the ellipsis button. Find the backup file you wish to restore
Change the destination database to another database name (you don't want to overwrite your existing database)
Choose files on the LHS. Make sure to change the Restore As field so that you do not overwrite your existing mdf or ldf files
Write queries to insert into  select * from


MySQL database getting connected with any username and password

As per suggested by Mr. Barmar it was causing due to --skip-grant-tables in MySQL.

To disable this option just run FLUSH PRIVILEGES; command in your MySQL CLI and this will solve the issue.


How to add 1 milliseconds to a datetime string?

Datetime is not precise to the level of 1 millisecond. What you are asking for is not possible unless you change to a different datatype (i.e. datetime2).

Documentation

Important quote:

  Accuracy  Rounded to increments of .000, .003, or .007 seconds


Is there any MongoDB handler for find event?

I want to know is there any handler or trigger kind of functionality exist in MongoDB which can help me to run my mongo command on find query?

  Basically, I want to run a specific set of Mongo queries on find query. You can say a listener on find query to run other mongo commands.

As at MongoDB 3.4, there is no server-side functionality for query listeners or triggers. There are some open feature suggestions for triggers in the MongoDB Jira issue tracker, but these all appear to be associated with operations that update data (insert, update, delete) rather than find queries.

  Other than that is there anything like create a view from a remote database, I know MongoDB provides creating a view from the same database but I want from the remote database?

As at MongoDB 3.4, there is no in-built feature for creating views from remote databases or deployments.

  I do not want any application level solution like any third party module or watcher script, I want something which is within the MongoDB itself like inbuilt function or any event handling.

Although you've excluded third party solutions, there are currently no built-in features or feature requests matching your description. You could certainly raise one or more feature requests with a description of the use cases, but I think both of these approaches sound more suitable for implementing in an application layer with your business logic rather than embedding that into a distributed database.

As a more general solution to enable the use cases you've described, I suggest putting an API layer between your applications and your MongoDB deployment. The API layer could intercept queries and perform the desired actions based on your use case, and is an option available to you without any dependencies on MongoDB server features.

  I have tried auditing but, it only records the operation and all, does not provide any handler through which I can run my mongo commands like Oracle providing in Fine-grained auditing.

The auditing feature (which is part of MongoDB Enterprise) is only intended to record configured operations of interest. If you want to do special processing based on queries you will have to implement this in your own application code.


Postgres JSON field for many to many relationships

would it be better to use Postgres' JSON functionality to store my data something like this:

No.

  Are there any downsides to an approach like this?

GIN / GiST indexes on jsonb are bigger and slower than b-tree indexes. Additionally, whenever you update a field it must be rewritten, you can't just insert an extra element inside the json document in the row.

  My concern is that since every move is creating a new entry into the junction table -- and there are potentially hundreds of stages a lead can go through -- it may lead to hundreds of thousands of rows for very few leads and that could probably impact the lookup time when I'm trying to fetch a specific set.

This is what RDBMSes are good at.

Use the conventional relational modelling for this unless you have a really good reason not to.


I had to do this some years ago for a SQL Server 2005 DB.  It was awful.  I've not had to do it on 2008, thankfully, but as far as I know it still works the same way.

Unless specified explicitly, the collation is copied from the next higher object when an object is created.  So creating a table will copy the database's collation.  Collations are not inherited dynamically at runtime i.e. changing the DB's collation will not change any table's collation.

So you are on the right track.  You have to change the collation at every level from column up.  Moreover you will almost certainly want to change the collation for the system databases (master, model, msdb and tempdb) otherwise you may get collation conflicts when a query plan uses tempdb and you will have such plans.

Furthermore I remember that the new column collations did not take effect until the data had been written.  I ended up performing

update <table>
set <string_column1> = <string_column1>,
    <string_column2> = <string_column2>;

for every table in the database.

If I ever had to do it again I'd install a new instance from scratch with the proper collation, then migrate the tables, then the data, then the constraints.  Hopefully you have good source control in place.


Why can you only drop a SQL Server view in the current database?

The documentation is skimpy on this, merely stating (and providing a syntax diagram) that only current database is relevant.

  Removes one or more views from the current database.

DROP VIEW [ IF EXISTS ] [ schema_name . ] view_name [ ...,n ] [ ; ]

Someone fluent in the debugger could determine if this is because of a code path that is only hit when dropping certain types of objects, or you could ask someone at Microsoft. Regardless of the reason, and whether or not you actually know the reason, you still need the same workarounds... you can do

USE your_database;
GO
DROP VIEW dbo.viewname;

Or:

EXEC your_database.sys.sp_executesql N'DROP VIEW dbo.viewname;';


i am trying to get total amount from this query

Error occurred due to a column which is not part of the equation is inside the parenthesis.

Try this:

SELECT
    s.emp_id,
      (s.[basic] + s.da + s.allowance) -
        ((s.deduction - p.pf_own_value) +
          s.adjustment + s.fine + s.tax +
           p.pf_own_value) AS solution
FROM salary_log s
INNER JOIN provident_fund_info p ON p.emp_id = s.emp_id
WHERE s.generate_date = '2017-12-01'
    AND s.statement_type = 'salary';


Can one make MySQL cluster on a unique index rather than a primary key?

PRIMARY KEY

InnoDB has exactly one PRIMARY KEY on each table.
The PRIMARY KEY is always clustered; there cannot be any other "clustered" index.
That PK is either an explicitly stated PK (this is preferred), the first UNIQUE key (with certain limitations), or a hidden sequence number (not quite the same as `AUTO_INCREMENT; 6 bytes).
The PK is UNIQUE and is and index.
The PK (and other indexes) may contain multiple columns ("composite" index.
The table is structured as a BTree (actually a B+Tree), with all columns present, and BTree is ordered according to the PK.

Secondary Keys

All other indexes are called "secondary indexes".  ("key" == "index")
The structure is a B+Tree, ordered according to the key
The "rows" of this BTree contain copies of the column(s) of the PRIMARY KEY (that is, the 'clustered index').  This is how the secondary key can get to the row.
Note that looking up a row via a secondary key requires drilling down two BTrees.  (Unless it is a "covering" index).
A UNIQUE KEY is two things: a secondary key (unless promoted to PK), and a "uniqueness constraint".

Other notes

InnoDB has not concept of a "ROWNUM" or "row locator", unlike some other vendors.  The PK serves as the row locator.
MySQL has no "Heap" or "Bit" indexes on InnoDB.
InnoDB has only BTree, SPATIAL, and FULLTEXT; I am not discussion the latter two in this Answer.
For most purposes, it does not matter whether you create the indexes inside the CREATE TABLE statement, or do CREATE INDEX later.  Ditto for defining FOREIGN KEYs.
Structurally, the PK+data is a B+Tree; each secondary index is a B+Tree.  These B+Trees look, feel, smell, etc, identical.  The only difference is what you find in the 'rows'.

AUTO_INCREMENT

There are many caveats.  It is not necessarily consecutive numbers; there are many ways that gaps can occur.  Only trust that they will be 'unique'.
AUTO_INCREMENT does not have to be declared PRIMARY KEY, or even UNIQUE.

A trick.  It is possible to do the following.  Is it what you are fishing for??

CREATE TABLE ... (
  id INT AUTO_INCREMENT,
  foo ...,
  ...
  PRIMARY KEY (foo, id),  -- to get clustering on `foo` and uniqueness (via `id`)
  INDEX(id)               -- sufficient to keep AUTO_INCREMENT happy.
) ENGINE=InnoDB;


I'm not entirely clear on whether I understand you problem or not, but  assuming there is a customer_id attribute you can partition by that:

select ... from (
    select ..., row_number() over (partition by customer_id) as rn
    from ...
)
where rn = 1

This will give you one row per customer, in an undefined order. If you want a true random account per customer you can add order by some random function after partition by:

    select ..., row_number() over (partition by customer_id
                                   order by ...) as rn


mysql Quiz leaderboard filter by points, time taken

Solution to the query:

select *
from (
  select *
  from ProQzQuizReport
  order by points desc, time_spend asc) a
group by user_id;

http://sqlfiddle.com/#!2/65fbf0/9


MySQL query optimization for legacy application

SUGGESTION #1

You have no meaningful indexes. That can have a detrimental effect on execution since the MySQL Query Optimizer has no help in searching.

You should all the following indexes

ALTER TABLE customers ADD INDEX bigindex (status,deadlead,purchased,last_updated);
ALTER TABLE notes ADD INDEX timestamp_id_index (timestamp,id);

This may help with some of the searching

SUGGESTION #2

You have duplicate indexes. That can slow down INSERTs and bloat the MYI file.

Please run

ALTER TABLE customers DROP INDEX id,DROP INDEX id3;

SUGGESTION #3

Periodically, perhaps once a week, you should update index statistics

ANALYZE TABLE customers;
ANALYZE TABLE notes;

CAVEAT

There could be other things I could suggest. However, if the amount of data is relatively small, indexing is probably all you need.

Give it a Try !!!


SQL Server 2008 R2 : Unexpected timeout with SQL Agent Job/powershell

Here is what I use in my PowerShell scripts that I experience issues for timeout.

Invoke-SQLcmd $SQL -QueryTimeout 65535 -ConnectionTimeout 0

Reference:
https://docs.microsoft.com/en-us/powershell/module/sqlserver/invoke-sqlcmd?view=sqlserver-ps


storing a mirror of IMAP/pop3 folder/inbox in a local DB, what choices are there?

You could try DBMail as local IMAP storage, and either use any of various IMAP syncing solutions or regular SMTP forwarding to get the mail in there.


I would not rely on Object Explorer (or the AG dashboard) in SSMS to determine the status. Use the DMVs directly (or create your own dashboards against them). Something like this:
SELECT
  d.name,
  AGName = COALESCE(ag.name, 'N/A'),
  drs.synchronization_state_desc,
  ar.replica_server_name
FROM sys.databases AS d
LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS drs
  ON d.database_id = drs.database_id
LEFT OUTER JOIN sys.availability_groups AS ag
  ON drs.group_id = ag.group_id
LEFT OUTER JOIN sys.availability_replicas AS ar
  ON drs.replica_id = ar.replica_id;

I suspect this specific limitation is just that they didn't add the display sync status logic into the code path that renders the System Databases node. The fact that is it missing is documented here, but without an explanation why:

SSMS does not show Distribution Database as synchronizing/synchronized, when distribution database is added to an availability group.


MySQL - 450 columns per table good or wrong practice

The technical limit on the number of columns depends on the engine.  InnoDB allows 1017 in MySQL5.7
Those 1017 columns can cover a maximum of 65535 bytes.
Records are stored on "pages".  InnoDB allows the page size to be configured to 4, 8, 16, 32, 64Kb.  Your record must fit on a page so you can't stick a 5K record on a 4K page.

The problems with having wide records is that when the DB engine retrieves records it does so in pages. You can get few wide records on a page so retrieval performance decreases. DBs pull the results through memory so subsequent retrievals will see if the data remains in memory before falling back to storage.  Having many records on a page means that the first physical retrieval of a page is more likely to load into memory records which can be logically (and much faster) read from memory.

From a design perspective it depends on what your use case is.  In an OLTP system then I would feel uncomfortable with 450+ columns.  A database is not a dumb store.  It can be used to enforce rules on the structure of information and the relationships between different data entities. This is an incredibly powerful weapon to have in your arsenal.

In a data warehouse supporting certain analytical systems 450+ sounds like a lot however I have seen some wide denormalised tables used to feed OLAP cube technologies.

If I saw a 450+ column table I should also ask questions about security.  When I grant access to that table do I want everyone with access to have access to all 450+ columns?  In addition to storage efficiency/performance normalisation can also factor in a security design.

Consider performance.  Of those 450+ columns which ones get retrieved the majority of the time?  Do you really want to have the expense of retrieving 450+ columns if only 32 are used on a regular basis?

The answer I have given assumes that InnoDB (the default) is used.


NOLOCK or Read Uncommitted locking / latching behaviours

Reads under READ UNCOMMITTED isolation only take Sch-S. No shared locks are acquired (there are a couple of rare exceptions).

Reads under locking READ COMMITTED take S locks that are usually released as soon as the query processor moves on to the next row (again, there are some exceptions).

Pages are always latched while being read or written to ensure physical consistency, generally a shared latch on read, and an exclusive latch on write. For details see Inside SQL Server Latches by Bob Ward via PASStv.

Locks are an implementation detail used by SQL Server to deliver the guarantees provided by the isolation level.


How should I log changes to an object when the datatype of the changed property can vary?

There are restrictions and difficulties with sql_variant type.
See MSDN for details: SQL Variant

As you already "feel" you might also trap into problems with casting and converting lateron. For samples see 10 reasons to explicitly convert SQL Server data types.

I would try to avoid variant in this and any other case if possible.

But your basic problem is not the variant type but more your table design. Even if you had only integer values in your log tables, I would still think about changing it to some more normalization.

However after thinking about your problem I came to another approach that maybe worth a try: Why don't design your log table the same as the original table.

For example:

company_log
- id int
- name nvarchar
- description
- log_stamp datetime
- Log_type

That way you could add a new row each time the original row was changed and add the previous row into the log table. This will give you a complete history of the rows without any datatype issues.

You could also limit the logging to changes of specific columns. I think you will need triggers to realize it.

It will give you multiple log tables but if you keep them similar regarding log_type and log_stamp you could still query the entire history of any changes (of tracked tables) by UNION them.

I have found another question where this and other possible ways to solve it are explained pretty detailed: How to store historical records in a history table in SQL Server


Unable to create a Filtered Index on a Computed Column

Unfortunately as of SQL Server 2014, there is no ability to create a Filtered Index where the Filter is on a Computed Column (regardless of whether or not it is persisted).

There has been a Connect Item open since 2009, so please go ahead and vote for it. Maybe Microsoft will fix this one day.

Aaron Bertrand has an article that covers a number of other issues with Filtered Indexes.


INSERT using results of CTE INSERT to provide unique id values

I don't understand why you need 2 tables if they have only 1-1 relationship. But here it is (pk is the primary key of t3):

WITH ins AS (
  INSERT INTO t1 (col1)
    SELECT NULL FROM t3
  RETURNING t1_id
)
, r AS
( SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
)
, t AS
( SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
)
INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t
  JOIN r USING (rn) ;

If your t3 is the results of a SELECT instead of a preexisting table, you can implement it as such so that you don't have to repeat the t3 query twice:

WITH t3 AS (
  SELECT ...
), ins AS (
  INSERT INTO t1 (col1)
    SELECT NULL FROM t3
  RETURNING t1_id
), r AS (
  SELECT t1_id, ROW_NUMBER() OVER () AS rn
  FROM ins
), t AS (
  SELECT *, ROW_NUMBER() OVER () AS rn
  FROM t3
) INSERT INTO t2
  (col1, t1_id)
SELECT
  t.val1, r.t1_id
FROM t
  JOIN r USING (rn);


You can place your query inside a subquery and do a WHERE against it

PROPOSED QUERY

SET @rank=0;
SELECT rank FROM
(SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
WHERE fruit='apple';

SAMPLE DATA

DROP DATABASE IF EXISTS Alw7SHxD;
CREATE DATABASE Alw7SHxD;
USE Alw7SHxD
CREATE TABLE sales
(
    id INT NOT NULL AUTO_INCREMENT,
    fruit VARCHAR(20),
    amount INT DEFAULT 0,
    PRIMARY KEY (id)
);
INSERT INTO sales (fruit,amount) VALUES
('cherry',124), ('plum'  , 23), ('pear'  , 19),
('apple' , 13), ('banana',  4), ('orange',  2);
SELECT * FROM sales;
SET @rank=0;
SELECT rank FROM
(SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
WHERE fruit='apple';

SAMPLE CODE EXECUTED

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

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

mysql> USE Alw7SHxD
Database changed
mysql> CREATE TABLE sales
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     fruit VARCHAR(20),
    ->     amount INT DEFAULT 0,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO sales (fruit,amount) VALUES
    -> ('cherry',124), ('plum'  , 23), ('pear'  , 19),
    -> ('apple' , 13), ('banana',  4), ('orange',  2);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM sales;
+----+--------+--------+
| id | fruit  | amount |
+----+--------+--------+
|  1 | cherry |    124 |
|  2 | plum   |     23 |
|  3 | pear   |     19 |
|  4 | apple  |     13 |
|  5 | banana |      4 |
|  6 | orange |      2 |
+----+--------+--------+
6 rows in set (0.00 sec)

mysql>

PROPOSED QUERY EXECUTED

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

mysql> SELECT rank FROM
    -> (SELECT @rank:=@rank+1 AS rank, fruit, amount FROM sales ORDER BY amount DESC) A
    -> WHERE fruit='apple';
+------+
| rank |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


MariaDB 10.2 custom install using mysql_install_db.exe

I call mysql_install_db.exe with the correct --datadir argument and root password and have all files generated. I call mysqld --remove to delete the windows service. I call mysqld --install-manual [ServiceName] --defaults-extra-file=my.ini to finally set up the service using my very own my.ini file.
When this service is started up for the first time using net start the innodb-files are created freshly in the folders defined in the my.ini file. The system database mysql uses myisam table format and had not touched the innodb files.


Start with "database design" on Wikipedia.

I'd also recommend using Object Role Modelling to capture your conceptual model

Otherwise, your question is far too broad.

What you don't do is start knocking out some CREATE TABLE statements, or design as go along writing client side code


I'm a MS SQL DB Developer looking to get more permissions on the server

I am wondering if Grant Control on Schema will cover everything I
need
Will Grant Control give me everything I need as a Database developer.

But you didn't write what exactly you need as database developer.
If you update your question with your tasks to accomplish, I'll answer on that. For now I can give you only general answer.
Having CONTROL on schema you have access to this schema objects only, i.e. you can

SELECT INSERT UPDATE DELETE and ALTER tables (but you cannot CREATE
new table because this requires CREATE TABLE permission)
ALTER and EXECUTE modules (but not CREATE new ones because you don't
have CREATE PROCEDURE/VIEW/FUNCTION permission)
GRANT permissions on your schema objects to user but not CREATE USER

You are still lacking SHOWPLAN permission having only CONTROL ON SCHEMA
and of course you continue to see only your own sessions using sp_who2
To see all the sessions on the server you need VIEW SERVER STATE server level permission.
To be able to see plans you need SHOWPLAN permission in all databases involved in your query (so if you use temp tables you cannot see the plan if you have no SHOWPLAN in tempdb)
To be able to create jobs you must be a member of SQLAgentOperatorRole
To be able to kill any session you need ALTER ANY CONNECTION server level permission.
To use stop button in SSMS you don't need any special permission

I'd like to know if I create an index and it is ever hit and how often

To be able to select from sys.dm_db_index_usage_stats you need VIEW SERVER STATE permission.
.............
It seems to me I touched all the actions mentioned in your question, if I miss smth please ask in a comment or update your question


Execution Plan is NOT using INDEX, It uses Table Scan

Index seek might not be the best choice if you return many rows and/or the rows are very wide. Lookups can be expensive if your index is not covering. See #2 here.

In your scenario, the query optimizer estimates that performing 50,000 individual lookups will be more expensive than a single scan. The optimizer's choice between scan and seek (with RID lookups for the columns needed by the query, but not present in the nonclustered index) is based on the estimated cost of each alternative.

The optimizer always chooses the lowest cost alternative it considers. If you look at the Estimated Subtree Cost property in the root node of the two execution plans, you will see that the scan plan has a lower estimated cost than the seek plan. As a result, the optimizer chose the scan. That is essentially the answer to your question.

Now, the cost model used by the optimizer is based on assumptions and "magic numbers" that are quite unlikely to match your system's performance characteristics. In particular, one assumption made in the model is that the query starts executing with none of the required data or index pages already in memory. Another is that sequential I/O (expected for a scan) is cheaper than the random I/O pattern assumed for RID Lookups. There are many other such assumptions and caveats, far too many to go into in detail here.

Nevertheless, the cost model as a whole has been shown to produce generally "good enough" plans for most queries, on most database schemas, on most hardware configurations, most of the time, everywhere. That is quite an achievement, if you think about it.

Model limitations and other factors will sometimes mean the optimizer chooses a plan that is not, in fact, "good enough" at all. You report that "performance is good", so that does not seem to be the case here.


special setup to create views in Oracle in default schema

DBA role alone is not enough.

CREATE VIEW

  The owner of the schema containing the view must have the privileges
  necessary to either select (READ or SELECT privilege), insert, update,
  or delete rows from all the tables or views on which the view is
  based. The owner must be granted these privileges directly, rather
  than through a role.


Snapshot are a common practice for backups. See http://www.mysqlperformanceblog.com/2009/02/05/disaster-lvm-performance-in-snapshot-mode/

For this to work safely though you'll want to ensure innodb_flush_log_at_trx_commit is set to 1.

That being said, the general practice with backups is test restoring them.  You want to be sure your backup method is working functionally and you want to be sure you know the steps to restore them.  If you're having to restore a backup it likely means your in a situation you're loosing money while it's down.  You should be able to restore a backup while being groggy after getting a 3am wake up call.

If you have a dev environment you might setup a weekly script to restore the backup into your dev environment.

You now have a push button script to restore in a pinch; just point it to production instead of dev
Your dev env. now has a production style dataset to work against. (You may want to have a secondary scrub script to anonymize customer, financial or other sensitive information).
You get automatic weekly tests of your restore process.  If there's a problem you can be alerted of it and fix it before it's needed for real.


Mongo db sharded "the collection's metadata lock is taken" issue coming very frequently

There are two things which take a meta data lock on a collection - the balancer (to move a chunk) and a mongos (to split a chunk).  Now, sometimes you will get this warning (it is just that, a warning) if, for example, a mongos attempts to split while another mongos is already performing a split.  Or, it might be that the balancer is moving chunks and so the split cannot get a lock for that reason.

In either case, the chunk will usually get split eventually and things will settle down, but you have indicated that is not happening.  If the balancer is running constantly then you may want to re-evaluate your shard key choice and whether you need to pre-split etc. to alleviate the imbalances you are creating.

In any case, one thing that you can do to give the mongos a better chance to be able to split without an error is to temporarily disable the balancer to see if stopping it allows the splits to happen.  This is covered as part of the more detailed and complicated procedure I am about to cover for the more extreme case.

That extreme case would involve some sort of issue with stale locks.  As mentioned in the comments, this is intended as a break-fix type of intervention to get that particular collection split.  It is a lot of work, and it is not really for beginners.  The lock that is blocking the splits should expire eventually on its own eventually, but you may not have the time to wait for that to happen.  To force the issue, we need to basically do the following:

Reduce contention for the meta data lock (balancer, and splits)
Connect to a mongos, split the chunk on the collection manually
Manually move some chunks for the collection onto other shards (manual balancing)
Restore normal balancing and splitting

Note: this will require you to restart mongos processes if you want to be absolutely sure.  That will usually mean down time for your application and some errors getting thrown back to your app when you do the restarts. For this reason, and for general safety I would recommend doing this at a low traffic or scheduled maintenance time. For reference in terms of how splitting happens, see this answer.

First, turn off the balancer:

sh.stopBalancer()

The command will wait for balancing to complete before returning, so this may take some time if the balancer is currently actively balancing chunks.  To verify the state of the balancer you can use sh.getBalancerState() and sh.isBalancerRunning(). This is also the piece you should try initially as mentioned at the start of the answer.

Next, restart all of your current mongos processes with the --noAutoSplit option. By doing these two things you have essentially guaranteed that:

Nothing else will be trying to make meta data changes on this cluster (which will allow you to do so manually without error)
If there are any remaining locks blocking you from taking action in the config.locks collection, they are now definitely stale and can be removed if necessary.

Next, you need to manually split the chunk on the collection that has been failing.  The easiest command to do so is splitFind().  It will basically work with any valid query for you since you only have one (Min to Max) chunk:

sh.splitFind( "ibeat20150105.dgrpCount", { articleId: $SOMEVALUE, host: $SOMEVALUE } )

This will split the chunk in two, and you can repeat for the resulting chunks (with different values to target different chunks) to get to a decent number - I would aim for at least the same number of chunks as you have shards so that you can move data to them, but basically the only real limit is that the new chunks must be less than you max chunk size (64MB by default) in order to be moved successfully.  If you know your current approximate data size (from db.collection.stats()) for example, then it should be easy to figure out how many chunks you are going to need.

If you encounter a similar error regarding locks being taken and you are sure you have disabled the balancer and splitting on all mongos processes then you can remove the entries from the config.locks collection (because they must be stale) manually and retry.  As long as you run the remove command from a mongos and not directly on the config servers, this should be safe enough.

Once you have your split and appropriately sized chunks you can move them from shard0000 with the moveChunk() command, something like this (to move to the theoretical shard0001):

sh.moveChunk("ibeat20150105.dgrpCount", { articleId: $SOMEVALUE, host: $SOMEVALUE }, "shard0001")

Once your chunks have been moved to your satisfaction, you can restart your mongos processes without the --noAutoSplit option, returning them to normal.  When that is complete, re-enable normal balancing with sh.startBalancer().


Is there any way to delete from this postgres 9.6 view?

Firstly, afaik, any view can be "updatable" but only views that are “simple enough” can be automatically updatable. You can make any VIEW updatable with

INSTEAD OF trigger trigger
PostgreSQL RULE SYSTEM I prefer to never use the rule system.

And of course you can make a function that works on the underlying table and updates it.


Timezone offset displayed though it shouldn't be stored

From the PostgreSQL documentation you link to, about halfway down the page:

  When a timestamp with time zone value is output, it is always converted from UTC to the current timezone zone, and displayed as local time in that zone. To see the time in another time zone, either change timezone or use the AT TIME ZONE construct.

So (as Evan Carroll hinted in his comment), if your system has 'Europe/Berlin' set as timezone, you would expect to see the UTC date/times output using that timezone.

To confirm, try inserting a date/time from another timezone; that should also be output showing the 'Europe/Berlin' time instead. Or, try changing timezone, and confirm that the dates that were showing the time from Europe/Berlin now show the time from the new time zone instead.