How to generate an SQL file with DBMS_DATAPUMP

Ok, I didn't realise that creating such an export file consists of two steps. First, an ordinary dump file must be created, then this dump file is used to create an sql file.

Since I didn't have such a dump file, the script as posted would not work.

Here are the two steps, so that an sql file can be created

declare

  datapump_job number;
  job_state    varchar2(20);

begin

  datapump_job := dbms_datapump.open(
    operation    => 'EXPORT',
    job_mode     => 'SCHEMA',
    remote_link  =>  null,
    job_name     => 'Export dump file',
    version      => 'LATEST'
  );

  dbms_output.put_line('datapump_job: ' || datapump_job);

  dbms_datapump.add_file(
    handle    =>  datapump_job,
    filename  => 'export.dmp',
    directory => 'DATAPUMP_DIR',
    filetype  =>  dbms_datapump.ku$_file_type_dump_file);

  dbms_datapump.start_job(
    handle       => datapump_job,
    skip_current => 0,
    abort_step   => 0);

  dbms_datapump.wait_for_job(datapump_job, job_state);

  dbms_output.put_line('Job state: ' || job_state);

  dbms_datapump.detach(datapump_job);

end;
/

and the script that creates the sql file:

declare

  datapump_job number;
  job_state    varchar2(20);

begin

  datapump_job := dbms_datapump.open(
    operation    => 'SQL_FILE',
    job_mode     => 'SCHEMA',
    remote_link  =>  null,
    job_name     => 'Export SQL file',
    version      => 'LATEST'
  );

  dbms_output.put_line('datapump_job: ' || datapump_job);

  dbms_datapump.add_file(
    handle    =>  datapump_job,
    filename  => 'export.dmp',
    directory => 'DATAPUMP_DIR',
    filetype  =>  dbms_datapump.ku$_file_type_dump_file);

  dbms_datapump.add_file(
    handle    =>  datapump_job,
    filename  => 'schema.sql',
    directory => 'DATAPUMP_DIR',
    filetype  =>  dbms_datapump.ku$_file_type_sql_file);

  dbms_datapump.start_job(
    handle       => datapump_job,
    skip_current => 0,
    abort_step   => 0);

  dbms_datapump.wait_for_job(datapump_job, job_state);

  dbms_output.put_line('Job state: ' || job_state);

  dbms_datapump.detach(datapump_job);

end;


PostgreSQL sort by max date and get the difference

If there is exactly one maximum balance per month, the first query can be simplified to

select balance
from balances
where user_id = 123
  and date_trunc('month', datecreated) = date '2018-08-01'
order by datecreated desc
limit 1;

The difference between a specific month and the previous one, can be calculated with a window function:

select b.datecreated,
       b.balance,
       coalesce(prev.balance, 0) as prev_balance
from balances b
  left join lateral (
    select balance
    from balances b2
    where b2.user_id = b.user_id
      and b2.datecreated < date_trunc('month', b.datecreated)
    order by datecreated desc
    limit 1
  ) as prev on true
where user_id = 123
  and date_trunc('month', datecreated) = date '2018-08-01'
order by datecreated desc
limit 1;

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


Clustered index in SQL Server vs index organized tables in Oracle

If you're transitioning from SQL Server to Oracle, I would advise to try heap tables at first because they are the standard form of storing data in Oracle. For most workloads, heap tables with regular indexes in Oracle are the most balanced forms of storage regarding DML and query performance.

If later you find that you have performance problems or bottleneck, you should look into specialized advanced storage methods such as IOT, partitioning, clusters, reversed-key indexes, etc.

Regarding IOT in particular, I would advise against their generalized use because there are lots of "gotchas" that you may not want to get into as a beginner:

IOT don't have real rowid (because there's no table per se).
consequently, secondary indexes on IOT don't have true pointers to the rows but only mere guesses which can lead to inefficient index scans.
Some features are disabled on IOTs such as virtual columns, table compression, composite partitioning.
You have to decide at creation where to store the non-index columns (inline or in an overflow segment), potentially leading to disastrous performance for some queries.


Unexpected behavior of the “Money” field in PostgreSQL

Quoting the documentation on monetary types on PostgreSQL:

  Since the output of this data type is locale-sensitive, it might not work to load money data into a database that has a different setting of lc_monetary. To avoid problems, before restoring a dump into a new database make sure lc_monetary has the same or equivalent value as in the database that was dumped.

(emphasis mine)

The interpretation of the local for lc_monetary is system-dependent, and doesn't work the same way in Ubuntu as in Windows.

Check Best data type to store money values in MySQL, a good part of the answers are not MySQL specific, and they will give you a good understanding.

The natural tendency is to not deal with money types within databases, but use numeric types instead (and normally, with 4 decimals during calculations, not just 2), and handle the formatting at the app level. I should add also that, having a money type that doesn't store a currency doesn't make much sense to me.

With regard to locale information:

In Windows, you can actually set with very much detail how currency should be shown. The following picture shows the Windows 10 settings of my box:

They're quite different that those of my Ubuntu 16 box:

And slightly different from the Mac that hosts them all:


Advanced search engine or server for relational database

Stack Overflow search is now 81% less crappy, thanks to Lucene. Whether this, or any of the other document oriented search servers, would be a good fit is difficult to tell from the detail you've posted.

Lucene/Solr and their ilk are built to search textual data. It sounds like you should be considering a graph database. High Scalability has an interesting article on Neo4j which discusses some of the concepts well.


How to use a MySQL error handler in a stored procedure with prepare/execute

Here is a sample of updated procedure with 1064 and 1065 errors caught

refer to https://gist.github.com/onare/c8d3042ba4d18408aeee for a longer list of errors

drop procedure if exists drop_tables_like;
delimiter $$
create procedure drop_tables_like(myint int, pattern varchar(255), db      varchar(255) )
begin
DECLARE E INT DEFAULT 0;
DECLARE M TEXT DEFAULT NULL;

DECLARE CONTINUE HANDLER FOR 1064 SET E='1064', M="%s near '%s' at line %d";
DECLARE CONTINUE HANDLER FOR 1065 SET E='1065', M="Query was empty";

select now() - interval  myint day into @owa;
set group_concat_max_len = 65535;
select concat('drop table ', group_concat(table_name)) into @str_sql
from information_schema.tables
where
table_schema=db and table_name like pattern and
create_time <= @owa;

prepare stmt from @str_sql;
IF E> 0 THEN
        SELECT CONCAT("MySQL Error #", E,  ": ", M);;
        set E=0;
    else
        execute stmt;
        drop prepare stmt;
    end if;
end$$


MySQL: Granting permission for CREATE VIEW

This is detailed in When Privilege Changes Take Effect in the MySQL documentation.

  A grant table reload affects privileges for each existing client
  connection as follows:

  Table and column privilege changes take effect with the client's next
  request.

  Database privilege changes take effect the next time the client
  executes a USE db_name statement.

  Note Client applications may cache the database name; thus, this
  effect may not be visible to them without actually changing to a
  different database or flushing the privileges.

  Global privileges and passwords are unaffected for a connected client.
  These changes take effect only for subsequent connections.


How does SSIS Balanced Data Distributor decide to split records?

You don't really have much control over how the SSIS Balanced data distributer control distributes data, but you could influence the order in which the data by the BDD is received.

From the docs (emphasis mine):

  This transform takes a single input and distributes the incoming rows
  to one or more outputs uniformly via multithreading. The transform
  takes one pipeline buffer worth of rows at a time and moves it to the
  next output in a round robin fashion.

The pipeline buffer size can be influenced by properties such as the DefaultBufferMaxRows and DefaultBufferSize or AutoAdjustBufferSize. Check the docs on data flow performance features for details on how it's all calculated.

So in short, You'll have a hard time figuring out exactly how many records are sent out each time the BDD switches to a new output.

Knowing how the BDD distributes data by just taking whatever is in the incoming buffer and sending it to an output destination you could maybe figure out ways to make sure the incoming data is sorted in a way that you can avoid locks later on, but remember that each flow after the BDD will probably have some processing in it too, and there is no way you can tell if at the end the data will be processed in the same amount of time by each path the data takes, so sorting it before the BDD doesn't guarantee it's still in the same order later on.


What is the correct implementation of handling concurrent SQL inserts while ensuring a non-primary key column (i.e. wid) unique and auto-incremental?

All three options you are looking at are bound to perform poorly in a concurrent environment. Wrapping SELECT (MAX(wid)) ... in a transaction won't prevent race conditions; you'd need to write-lock the entire table for that.

If you don't need the sequence numbers to start from 1 for every branch, as long as the values are increasing and unique, you might consider creating a separate table with an auto-incerement column to generate the wid values, something like this:

create table gen (
  branch_id int not null,
  seq int not null auto_increment,
    index (seq),
    unique (branch_id, seq)
);

You can then simply insert the base branch_id into that table and retrieve the generated sequence value with last_insert_id().


SSIS Package fails to "Transform File and create output file"

This ended up being a Kerberos problem.  We were able to identify through some of the security logs and some network monitoring, that the request was coming in as anonymous.  Once we applied the relevant SPNs for MSSQLSVC for the account that was being used to kick off the process, it worked.


Create one table to house all of the writing types, and use a foreign key relationship to a type table.

The most dominant reason to do this is because what happens when you want to add another writing type (say, "dictionary")?  You'd have to add another table, and most likely alter application code.

Not fun, and a bad design.  Go with one table and a type field.


MariaDb: right syntax of PREPARE statement for CONNECT type tables when passing string variable

This looks like a bug as you can run this code just fine against an InnoDB table. It should work the same way against a Connect table, but apparently it doesn't. So I'd suggest you file a bug report in the MariaDB Server Jira project.


It's a totally pointless function that executes arbitrary SQL. It isn't SECURITY DEFINER so the only risk I think it can pose is if you allow users to run arbitrary SQL predicates or call arbitrary functions (in which case you're probably already stuffed) but try to block them from running any command they want.

As you guessed, it just executes the SQL input using PL/PgSQL's dynamic SQL facilities.

Oh, it'd also hide the statement text from simple log_statement analysis, auto_explain etc, as they only look at top level statements, not statements run via PL/pgSQL.

Personally I'd drop/rename it, and see what complains.


Why not just collate that column as case-sensitive? I don't think you need to resort to binary. This seems to work fine, just note that it may affect sorting, comparisons, unions etc:

CREATE TABLE [dbo].[Unit]
(
    [id]                SMALLINT       NOT NULL IDENTITY(1,1),
    [name]              VARCHAR(100)   COLLATE Latin1_General_CS_AS NOT NULL ,

    CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [UQ_Unit_Name] UNIQUE ([name])
);
GO

INSERT INTO dbo.Unit (Name) VALUES('e³m³/day')
INSERT INTO dbo.Unit (Name) VALUES('e3m3/day')

Results:

1 row(s) affected.
1 row(s) affected.

Now, this works for the example in your question, but whether it works for your entire set of requirements, is hard to say. You may have characters that are different to you but aren't treated differently in terms of case-sensitivity, such as:

INSERT INTO dbo.Unit (Name) VALUES('ß')
INSERT INTO dbo.Unit (Name) VALUES('ss')

Results:

1 row(s) affected.

And then:

  Msg 2627, Level 14, State 1, Line 2
  Violation of UNIQUE KEY constraint 'UQ_Unit_Name'. Cannot insert duplicate key in object 'dbo.Unit'. The duplicate key value is (ss).
  The statement has been terminated.

In that case you may need to use binary after all.


Join 2 tables by closest time, PostgreSQL 9.6

Using btree_gist and <->

You really want <-> which will actually work on GiST indexes through the internal tstz_dist. This works because you don't really care about the interval. First you need to add the extension,

CREATE EXTENSION btree_gist;

then

SELECT DISTINCT ON (time_1) time_1, time_2
FROM tbl1
CROSS JOIN tbl2
ORDER BY time_1, time_1 <-> time_2;

If there are too many rows to do a cross-join, you may have better luck with the lateral solution.

SELECT time_1, time_2
FROM tbl1
CROSS JOIN LATERAL (
  SELECT time_2
  FROM tbl2
  ORDER BY time_1<->time_2
  LIMIT 1
) AS tbl2;

Would be interested in seeing a report back on the benchmarks. =) Especially if you have a GIST index on time_1 and time_2.


Should indexes automatically be placed into their own bufferpool?

Lets run two hypothetical databases (H1 and H2) in parallel, with the same total amount of RAM for bufferpools (R).

Let H1 have a single bufferpool of size R.
Let H2 have two bufferpools: one of size I for index pages, the other of size D for data pages. (And D+I==R of course.)

The question is:

How do you determine the right values for I and D to make H2 perform better than H1?

My answer is that you can't in general. The database engine for H1 has more room for optimizing its cache than H2. If there are times of day where more index pages would lead to better performance, it can discard unused data pages and have a "mostly index pages" cache. If at a later time, data pages become hotter, it can evict more index pages and have a "mostly data pages" cache.

H2 can't do that. Once it has I pages-worth of index pages cached, it can't cache more even if that's what would have been best right now. It's stuck there with a sub-optimal use of RAM.

The only way for H2 to perform just as well as H1 is if the D/I split initially chosen is ideal, and the workload is very stable. That sure can happen, but I'd wager that it is not a very usual database scenario. If that's not your case, think of H1 as being the same as H2 but with dynamic partitioning of the cache between data and index pages managed directly by the thing that knows the most about how it needs to optimizing I/O (i.e. the database engine).

This is not to say that maintaining different bufferpools is never a good idea.
One scenario for isolating specific pages in a specific cache I've encountered is having a critical "report" that needed to run fast (obviously) at all times, and happened to use a few tables that were pretty much never used elsewhere. So those pages kept getting evicted, and that "report" had runtimes that varied a lot from run to run. Moving a specific set of tables (and their indexes) to a specific pool removed much of the "non-deterministic" performance part.
But that's sub-optimal for the database as a whole, more of a kludge and nearer Voodoo optimization IMO. (And that wasn't on DB2 but I believe that's irrelevant here.)

So my advice is: if you have X Gb of RAM available for cache, use a single buffer, put everything in it, and let the database engine do its magic in there.
If you run into a corner case that would appear to benefit from cache segregation, try it out, benchmark it, think about the overhead of having to maintain the magic numbers for each cache size, and go tune the queries, schema or disk layout instead :)


There really is an abundance of documentation and tools to do MS SQL Server to MySQL migrations - likely there would be no need to re-invent the wheel. Exporting your database tables' data to CSV just to re-import it to MySQL seems like a rather bad idea since you are going to lose all metadata (data types, constraints, indexes, views, ...).

Look at this question over at DBA.SE for a number of references to migration tools. Also, the MySQL website publishes a white paper on MS SQL migration which has some useful information about how data types and functions could be substituted and describes how MySQL Workbench could be used for migration.

While DTS could be used to perform a one-time migration, it probably is not worth the effort - it has been created with repeatable procedures in mind (i.e. database synchronization, repeated export tasks etc.). The amount of time for creating a transformation package is going to be far higher than what you are aiming for.


Please explain why deadlock is detected in my example

Simplified version

drop table TabA;
create table TabA ( a int
,                   b int );
insert into TabA
values ( 1, 11 ),
    ( 2, 22 ),
    ( 3, 333 );
select * from TabA;

--T1. Run T1 first.
begin tran t1
insert into TabA values (5, 55 );

waitfor delay '00:00:10';
--Run T2 in the other session within 10 sec.

-- After delay expires deadlock is detected
select * from TabA where a = 2;

commit tran t1;

Second script

--T2.
begin tran t2
update TabA set b = 111 where a=1;
commit tran t2;

Looking at deadlock graph (MS SQL 2008 Profiler), you'll see

1) T1 has inserted (5,555) row and holds X lock on this RID untill commit.

2) T2 tries to get U lock on all rows, check predicate a=1 and update the rows where the predicate evaluates to true.
2.1. It successfully updates one a=1 row and holds X lock on this RID untill commit.
2.2. When T2 tries to get U lock on (5,555) row it can't do that due to T1's lock. So T2 waits for T1 to commit.

3) After delay T1 tries to get S lock on all rows when executing SELECT * FROM TabA ... and naturally it can't obtain the lock on the row held by T2 (see 2.1). T1 must wait T2 which waits T1. Deadlock.

Now add PK to TabA

create table TabA ( a int PRIMARY KEY,
                   b int );

Sql sever is now deadly sure it needn't check any more rows for a=1 at step(2.1). T2 commits immediately and no dead lock detected.


You can see this information in the SQL Server Log.  This link has a query text that shows an example of searching for that specific item.

https://www.sqlrx.com/find-out-who-changed-the-database-recovery-model/

For your convenience, I have included the script in the above link here.  This is not my script, but the one from the site above, with some formatting fixes.

DECLARE @tracefile VARCHAR(500);
DECLARE @ProcessInfoSPID VARCHAR(20);

CREATE TABLE [dbo].[#SQLerrorlog]
    (
        [LogDate] DATETIME NULL
        , [ProcessInfo] VARCHAR(10) NULL
        , [Text] VARCHAR(MAX) NULL
    );

/*

Valid parameters for sp_readerrorlog

1 – Error log: 0 = current, 1 = Archive #1, 2 = Archive #2, etc…

2 – Log file type: 1 or NULL = error log, 2 = SQL Agent log

3 – Search string 1

4 – Search string 2

Change parameters to meet your needs

*/

--Read error log looking for the words RECOVERY

--and either FULL, SIMPLE or BULK_LOGGED indicating a change from prior state
INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'FULL';

INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'SIMPLE';

INSERT INTO #SQLerrorlog
EXEC sp_readerrorlog 0, 1, 'RECOVERY', 'BULK_LOGGED';

UPDATE #SQLerrorlog
SET ProcessInfo = SUBSTRING(ProcessInfo, 5, 20)
FROM #SQLerrorlog
WHERE ProcessInfo LIKE 'spid%';

-- Get path of default trace file
SELECT @tracefile = CAST(value AS VARCHAR(500))
FROM sys.fn_trace_getinfo(DEFAULT)
WHERE traceid = 1
    AND property = 2;

-- Get objects altered from the default trace
SELECT IDENTITY(INT, 1, 1) AS RowNumber
       , *
INTO #temp_trc
FROM sys.fn_trace_gettable(@tracefile, DEFAULT) g -- default = read all trace files
WHERE g.EventClass = 164;

SELECT t.DatabaseID
       , t.DatabaseName
       , t.NTUserName
       , t.NTDomainName
       , t.HostName
       , t.ApplicationName
       , t.LoginName
       , t.SPID
       , t.StartTime
       , l.Text
FROM #temp_trc t
    JOIN #SQLerrorlog l ON t.SPID = l.ProcessInfo
WHERE t.StartTime > GETDATE() - 1 -- filter by time within the last 24 hours
ORDER BY t.StartTime DESC;

DROP TABLE #temp_trc;
DROP TABLE #SQLerrorlog;
GO


Moving tables to another SQL2008 database (including indexes, triggers, etc.)

Scripting out the tables, then using SSIS to transfer the data would be the most reliable and effective way of moving the data to the new database.


Need Help Normalizing a Database for Media Website

You should read up on Star Schema and Database Normalization. While it's not always the solution for all database designs by any means, I believe following a loose implementation of it is generally good practice in a lot of cases, including yours. (It's even a derivative of what the whole Snowflake database management system is based off of.)
Here's a pretty good document on Star (and Snowflake) schema design: Understand star schema
(While this is targeted towards PowerBI, I think Microsoft does a good job explaining the concepts of the schema designs, so just focus on those sections.)
This is a good straightforward article on Normalization by Microsoft: Description of the database normalization basics
Database Normalization is even more relevant to your design question and more important between the two to understand.
From looking at your first design with the Media table that has 40+ columns, it might make sense to break it into different tables for the things that begin with the same prefix in your column names.
E.g. TrailerAttributes and SocialMediaAttributes could be their own tables. Then things in your Media table that don't have a trailer for example, won't have an empty and unneeded column. You can further normalize things away into their own tables this way too, say for example some common property belonged to both some Trailers and some Social Media rows. You'd make a further decoupled table that stored those attributes shared among the other tables. (This is another lever of normalization, which will make sense after you read my link above.)
It seems like your updated schema is a step in the right direction, especially with aiming to implement a more generic solution.


Postgres `log_disconnections = on` does not log disconnects

To debug non-working configuration settings, you can verify at runtime what the value is, and where it came from by querying the view pg_settings
select setting, source, sourcefile, sourceline
from pg_settings
where name = 'log_disconnections'

The columns sourcefile and sourceline will tell you where the currently active value was taken from.
If you find a value from postgresql.auto.conf the value was applied through ALTER SYSTEM and the correct way to remove it, is to use ALTER SYSTEM again.


How do I adopt the PostgreSQL naming convention in legacy database?

Moving to PostgreSQL convention

In PostgreSQL by convention and with good reason, we neither use spaces nor capital letters in our identifiers (columns, tables, schemas, etc). The use of _ is purely style though. In this example, we

Transliterate all ' ' to '_'
Migrate from camelCase to snake_case

This code changes nothing, but outputs the respective ALTER commands to update the schema (including the schema, table, and column names)

-- Columns first.
SELECT FORMAT(
  'ALTER TABLE %I.%I.%I RENAME COLUMN %I TO %I;',
  table_catalog,
  table_schema,
  table_name,
  column_name,
  lower(
    -- replace all spaces with _, xX and Xx becomes x_x
    regexp_replace(
      -- First, replace spaces with an _
      replace(column_name, ' ', '_'),
      '([[:lower:]])([[:upper:]])',
      '\1_\2',
      'g'
    )
  )
)
FROM information_schema.columns
WHERE column_name ~ ' |[[:lower:]][[:upper:]]'

-- Tables
UNION ALL
  SELECT FORMAT (
    'ALTER TABLE %I.%I.%I RENAME TO %I;',
    table_catalog,
    table_schema,
    table_name,
    lower(
      regexp_replace(
        replace(table_name, ' ', '_'),
        '([[:lower:]])([[:upper:]])',
        '\1_\2',
        'g'
      )
    )
  )
  FROM information_schema.tables
  WHERE table_name ~ ' |[[:lower:]][[:upper:]]'

-- Schemas
UNION ALL
  SELECT FORMAT (
    'ALTER SCHEMA %I RENAME TO %I;',
    schema_name,
    lower(
      regexp_replace(
        replace(schema_name, ' ', '_'),
        '([[:lower:]])([[:upper:]])',
        '\1_\2',
        'g'
      )
    )
  )
  FROM information_schema.schemata
  WHERE schema_name ~ ' |[[:lower:]][[:upper:]]';

From there you can edit the commands to be run, delete the ones you don't want, or simply run \gexec if you're using psql and they will execute.

Testing

If you wish you can test the above

CREATE SCHEMA "myFoo bar";
CREATE TABLE "myFoo bar"."foo bar myBaz" ( "my foo" int, "myBar" int, "MyBaz" text, "myFooBarBaz" int, "MyFoo Bar Baz" int, "myTestQ" uuid );

                                            format
-----------------------------------------------------------------------------------------------
 ALTER TABLE test.my_foo_bar.foo_bar_my_baz RENAME COLUMN "MyFoo Bar Baz" TO my_foo_bar_baz;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "my foo" TO my_foo;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "myBar" TO my_bar;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "MyBaz" TO my_baz;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "myFooBarBaz" TO my_foo_bar_baz;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "MyFoo Bar Baz" TO my_foo_bar_baz;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME COLUMN "myTestQ" TO my_test_q;
 ALTER TABLE test."myFoo bar"."foo bar myBaz" RENAME TO foo_bar_my_baz;
 ALTER SCHEMA "myFoo bar" RENAME TO my_foo_bar;
(9 rows)

Note because there is a collision in the above to my_foo_bar_baz, you'll see

  ERROR:  column "my_foo_bar_baz" of relation "foo bar myBaz" already exists

That just confirms nothing catastrophic happens if you happen to run into that. highly unlikely


The problems with shrinking database files are primarily:

The shrinking process can cause fragmentation in tables and indexes.
Barring unusual circumstances, the files will need to be grown again in the immediate future.

Both of these can cause performance issues. Fragmentation of your tables and indexes can make queries take longer, and growing your log file (or your data file, if you don't have instant file instantiation turned on) takes a certain amount of time, where users may be waiting for it to complete.

"Unusual circumstances" would mean some change was made to the database such that space in the data file is not longer needed (for example, the permanent removal of a large table, or the deletion of millions of rows that won't be replaced shortly), or an abnormally large amount of space was required for the log file (for instance, a once-a-year data load cause your log file to grow from its norm 1 GB size to 35 GB; or, your database was using the FULL recovery model but you weren't taking transaction log backups and you've now resolved this (either by starting the backups, or by switching to the SIMPLE recovery model)).

In those unusual circumstances, shrinking your data or log files to a more reasonable size makes sense.

Note: Having a nightly job that shrinks these files doesn't make sense. If you're shrinking either file that often, then it must be growing larger than your job's target daily. If that's happening, you need to rethink that target, and think about adding space to your drives.

A further note: If you have heap tables (tables with no clustered index), SQL Server isn't good about releasing space once columns in those tables are no longer in use. Solution to this include copying the data to a new table, or adding a clustered index.

You need to asses your own system, and determine why you're running low on space. If it's due to natural growth of your data, then additional disk space will be needed at some point. If you got a lot of data that's no longer needed, devising some sort of archival process and then removing the data from production may be your solution. Or, if you've had one of those "unusual circumstances", shrinking files may be all you need to do.


Register XML schema using dbms_xmlschema in Oracle

You firstly need to create and Oracle directory.

As a user with the relevant permissions:

CREATE OR REPLACE DIRECTORY HOMEJOE AS '/home/joe';

Grant permission to the user that needs to use DBMS_XMLSCHEMA.registerSchema:

GRANT READ, WRITE ON DIRECTORY HOMEJOE TO YOURUSER;

Then register the schema, using the newly created directory:

DECLARE
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://[...]/spin-catalog-to-bpm.xsd',
    SCHEMADOC => bfilename ('HOMEJOE','spin-catalog-to-bpm.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
END;
/


Does MySQL or PostgreSQL lock table on deletion?

I don't know about MySQL but Postgres never locks the whole table when running DML statements - regardless of the number of affected rows.

Additionally writers never block readers, so even if you update all rows in a table, other transactions will still be able to SELECT from that table - obviously seeing the old values until your DML statement is committed.

Deleting rows from the table will also not block concurrent inserts (as long as there are no primary key conflicts). And inserting rows won't block deleting existing rows.

Again: the above is true for Postgres. I don't use MySQL, so I can't comment on that.


How to make psql restore of a database ignore the owner information?

That is easy. Use the --no-owner and --no-privileges options of pg_dump.
You might be more happy if you used the custom format of pg_dump (-F c). Then you'd restore with pg_restore and could specify the options at restore time.


Replication with two side update but one side insert

If I understand correctly, you want an insert on A to propagate to B but an insert on B not to propagate to A. If a record that exists on both gets updated on either side, you want that update to propagate. If a record that only exists on B gets updated it stays only on B.

The simplest way to get bi-directional replication going is to use merge replication. It can handle conflicts automatically in is therefore a good choice. See my stairway series for an introduction to merge replication: http://www.sqlservercentral.com/stairway/72401/

The additional requirement of hiding inserts on one node is easy if you can find a common property of those records that all other records don't have. If you think of the typical sale data distribution problem, you have a main database with the complete data set. The PA sales person is only interested in PA data, but if someone updates PA data in the main database she wants to know about it.

Replication allows you to specify a horizontal filter that would include only 'PA' data in the table article. The article about the merge publisher in my series has a brief introduction to horizontal filters.

If you do not have such an indicator yet, you could add a surrogate indicator, something like Repl_Ind BIT column and default it to 0 on the publisher, but change the default to 1 on the subscriber. (Or always specifically set it to 1 on the subscriber.) That way you could use the horizontal filter again.


looking for a cleaner way to select from multiple tables

First, I would remove the Order by weekrange desc as it is unnecessary (since you aren't also using TOP() in any of the SELECT queries.

Also, why use UNION ALL in the first place? Why not just have these be 3 - 12 separate INSERT INTO table SELECT .... queries?

And, given that the only thing changing in each query is the name of the table, and that name follows a pattern, the dynamic query can be templated and then just loop through the number of months you need.

The following should do what you want, with each INSERT...SELECT from each separate SMD_ table being a separate query / transaction:

DECLARE @Search NVARCHAR(10) = N'_search_', -- just to get the test code working
       @Store NVARCHAR(10) = N'_store_'; -- just to get the test code working

DECLARE @Now DATETIME2;
SET @Now = SYSDATETIME();

DECLARE @QueryTemplate AS NVARCHAR(MAX)
SET @QueryTemplate = N'
INSERT INTO SMDTemp (QDATE, UPC2, AWEEK, WEEKRANGE, MOVEMENT, COST, RTL, LBS, GP)
  SELECT @Now_tmp AS [QDATE], UPC2, MAX(RTG_WKD.AWEEK), RTG_WKD.WEEKRANGE,
         SUM(SMVNUM) AS [MOVEMENT], ROUND(AVG(SMVCOST), 2) AS [COST],
         ROUND(AVG(SMVSUGS / SMVSUGNS), 2) AS [RTL],
         CASE WHEN SUM(smvwgt) = 0 THEN SUM(smvnum) ELSE SUM(smvwgt) END AS [LBS],
         (ROUND(AVG(SMVSUGS / SMVSUGNS),2) - ROUND(AVG(SMVCOST), 2)) / CASE
             WHEN round(AVG(smvsugs / smvsugns), 2) = 0 THEN NULL
              ELSE round(AVG(smvsugs / smvsugns), 2) END AS [GP]
  FROM SMD_{{TableDate}}
  INNER JOIN RTG_WKD
          ON SMVDATE = RTG_WKD.WKDDATE6
  WHERE UPC2 = @Search_tmp
  AND   SMVStore = @Store_tmp
  GROUP BY RTG_WKD.WEEKRANGE, UPC2;
';

DECLARE @SQLQuery NVARCHAR(MAX);
SET @SQLQuery = N'';

DECLARE @MonthsBack INT;
SET @MonthsBack = 0;

WHILE (@MonthsBack > -12)
BEGIN
  SET @SQLQuery = @SQLQuery + REPLACE(@QueryTemplate,
                                      N'{{TableDate}}',
                                      REPLACE(CONVERT(NVARCHAR(15),
                                                      DATEADD(MONTH,
                                                              @MonthsBack,
                                                              GETDATE()),
                                                      102),
                                              N'.',
                                              N'_')
                                              )
                            + NCHAR(0x0D) + NCHAR(0x0A);

  SET @MonthsBack = @MonthsBack - 1;
END;

PRINT @SQLQuery; -- debug

EXEC sp_executesql
  @SQLQuery, -- define the query
  N'@Now_tmp DATETIME2, @Search_tmp VARCHAR(100), @Store_tmp VARCHAR(100)', --param list
  @Now_tmp = @Now, -- pass in parameter: param_name = local_variable_name
  @Search_tmp = @Search, -- pass in parameter: param_name = local_variable_name
  @Store_tmp = @Store; -- pass in parameter: param_name = local_variable_name

This query was originally using EXEC(@SQLQuery) but has been parameterized to use sp_executesql (as mentioned by @Jean in a comment on the Question).


You may want to recognize directional relationships (e.g. "is the mother of", "is the son of") or you may need to acknowledge that relationships are not bilateral insofar as the users don't grant each other exactly the same rights.  In that case you need to keep two records in users_relations for each relationship.  Otherwise, I would just capture each user linkage once.

Union queries take about twice as long as single queries, but when these queries are built on fast operations like index seeks this isn't so bad.  In any case you'd be trading this off against twice as much storage, which mitigates the impact slightly.

I'd be much more concerned about the potential headaches of managing the redundant data (if it is indeed redundant).

You could pick a convention like lowest id_user first to make certain kinds of operations more predictable - such as updates and deletes.


Can rebuilding indexes cause worse performance after the rebuild is finished?

Yes, rebuilding indexes (especially on SSD) can cause worse performance. Most high speed SSD prefer many, smaller block requests instead of fewer, larger requests. This is exactly the opposite pattern preferred by traditional, spinning rust.

Assume you have a highly fragmented B-tree. Because nothing is ordered on the disk, you will typically issue a lot of 8KB I/O requests to scan the tree. If you were to defragment the tree, then you can get up to 512KB in a single request. Those large requests will have higher latency on the SSD, because the SSD internally breaks it down to 8KB chunks (unlike a hard drive, which will issue a sequential I/O). For a great many cases: Higher disk latency = slower queries

All that being said, please do make sure you check that you are actually getting the same query plans that you were getting before the rebuild.

An finally: Unless you are low on space, why are you wasting your precious DBA time with index rebuilds when you run on SSD?


This is longer than a comment .. so putting this as answer ...

  So, I have rebuild the index and everything was fine.

Rebuilding an index will update stats on the columns associated with that index.

There can be a lot of variables which might be different between 2 servers (2014 vs 2016)

sys.configurations settings
compatiblity level of database
Any custom trace flag enabled
max memory setting
cost threshold of parallelism setting
max dop setting

You can run Best practice analyzer script from MSSQLTiger team or just run sp_Blitz from Brent Ozar's team.

As a side note: Use Ola's Index maintenance solution ... much flexible and well tested by a lot of organizations.

From your screenshot, it seems that you are setting the page fullness (FILLFACTOR) to 80% i.e. leave 20% free. You should monitor fragmentation (in your case 12% is nothing + you need to consider how many pages are in the index. I would suggest you to start looking into indexes with 1000+ pages). I would doubt that this will cause a major performance problem.


Here is a sketch for an Incremental Evaluation System (IES):

CREATE TABLE stops_min_max
( JournalPkey ... not null primary key
, MINStopPkey ... not null
, MAXStopPkey ... not null
);

CREATE TRIGGER ...
AFTER INSERT ON stops
BEGIN
        MERGE INTO stops_mean_max x
        USING (
            VALUES (__new.JournalPkey, __new.StopPkey, __new.StopPkey)
        ) y (JournalPkey, MINStopPkey, MAXStopPkey)
                ON x.JournalPkey = y.JournalPkey
        WHEN MATCHED THEN
            UPDATE SET (MINStopPkey, MAXStopPkey)
                     = (LEAST(x.MINStopPkey, y.MINStopPkey)
                       ,GREATEST(x.MAXStopPkey, y.MAXStopPkey))
        WHEN NOT MATCHED THEN
            INSERT (JournalPkey, MINStopPkey, MAXStopPkey)
            VALUES (y.JournalPkey, y.MINStopPkey, y.MAXStopPkey);

Triggers for update and delete should be added

Instead of looking for min, max in stops, they can be found in stops_min_max

Since I have no idea what your tables look like, and I don't have access to the DBMS you are using, a sketch is the best I can do.


Disabling SET Statements / Forcing SET Options in SQL Server 2008R2/2012

There is no way to enforce a global setting or prevent people running

SET ANSI_NULLS OFF
SET ANSI_PADDING OFF

You can get a quick idea if your application is using any deprecated features by running

SELECT *
FROM   sys.dm_os_performance_counters
WHERE  object_name LIKE '%Deprecated%'
       AND cntr_value > 0
ORDER  BY cntr_value DESC

If you do find areas of concern then you could set up a Server Side trace capturing the deprecation events.

This also returns the session id and SQL Handle of the offending batch to allow you to investigate further if you do see any events.

A slightly more sophisticated approach would be to set up a notification procedure to retrieve the text associated with the SQL Handle more quickly to reduce the probability that the offending SQL is no longer in the cache when you come to analyse the results.

Or you could probably use extended events to retrieve the TSQL along with the event.


TSQL command to delete backup files older than N hours

Remember that T-SQL should not be used to do file maintenance. Instead you should use Powershell to delete /move / copy etc files.

Tsql way:

use xp_cmdshell command along with the FORFILES command

e.g Delete all .sql files in the C:\Backup directory and its subfolders where the file modified date is more than 30 days old.

EXEC xp_cmdshell 'FORFILES /p c:\BACKUP /s /m *.sql /d -30 /c "CMD /C del /Q /F @FILE"'

Disclaimer: Enabling xp_cmdshell is a security risk and not many companies allow it.

Powershell Way --> This should be the right approach !!

A full script can be found at technet.

## Delete files older than 90 days

$a = Get-ChildItem C:\Scripts
foreach($x in $a)
    {
        $y = ((Get-Date) - $x.CreationTime).Days
        if ($y -gt 90 -and $x.PsISContainer -ne $True)
            {$x.Delete()}
    }


The optimal sequence (the query plan) is determined by the query executor, based on the SQL code (operators you use), data statistics (statistical distribution of values in columns, data volume, etc.) and database structure (availability of indexes, datatypes, etc.).
So, there are rules, but first not all of them are under your total control and second the result is detemined by a combination of many factors.

To evaluate performance, you should always take a look at the estimated query plan and compare the changes to the plan caused by the changes in your SQL code, to undertand why a query run faster or slower than another.


Calculate those counts on the fly.

For most data sets of this nature, I would imagine calculating the count of tardiness events would be relatively cheap.  How many rows do you expect there to be in the long run in your students or attendance tables?  Probably on the order of tens of thousands at most.  Contrast that to calculating an account balance from its transaction history in database for a large bank with billions of transactions. In this case I would consider persisting a summary of these aggregations somewhere.

If you wanted to pre-calculate this data anyway, I would summarize the count of tardiness events per student per year in an indexed view as suggested in the top answer here.

For example:

CREATE VIEW dbo.tardiness_summary
WITH SCHEMABINDING
AS
SELECT
     student_id
   , year
   , COUNT_BIG(*) AS tardy_count
FROM dbo.attendance
GROUP BY
     student_id
   , year
;

CREATE UNIQUE CLUSTERED INDEX IX_tardiness_summary
ON dbo.tardiness_summary (
     student_id
   , year
;

This is not a flexible approach, however, as this view is now schema-bound to the base tables and thus any modifications to either the view or the table will require rebuilding the view. Indexed views also have many restrictions on how they may be created or queried. Their advantage is that they guarantee the summary table will stay in sync with its sources because the database engine is now doing this work for you.


How to represent class table inheritance (current DBMS-specific way please)?

Ultimately, I implemented ypercube's suggestion from comments:

  The "type" column can be defined as a computed (but constant) column in SQL-Server. I think it has to be PERSISTED though so it can participate in the Foreign Key constraint.

This worked well both for performance and compatibility with with my tools (Entity Framework <= 6.0):

CREATE TABLE [dbo].[Account](
    [Id] [int] IDENTITY(1000,1) NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole] [int] NOT NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
CONSTRAINT [UX_Derived_Relation] UNIQUE ([Id], [CommunityRole]))

CREATE TABLE [dbo].[Recruiter](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((1)) PERSISTED NOT NULL,
    [RecruiterSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

CREATE TABLE [dbo].[Candidate](
    [Id] [int] NOT NULL PRIMARY KEY CLUSTERED,
    [CommunityRole]  AS ((2)) PERSISTED NOT NULL,
    [CandidateSpecificValue]  [int] NOT NULL,
FOREIGN KEY ([Id], [CommunityRole]) REFERENCES Account([Id], [CommunityRole]))

This mapped well to my implementation of multiple discrete account types, Recruiter and Candidate, on my Job Board.


how to group data against more than one conditions

The reason this isn't working for you when you have multiple rows in the employee table is that it is not correlated back to the main query.  By this I mean that SQL has no way of knowing which employee to count in your subqueries for a given row in your main query.  You can avoid this buy adding a reference back to your main query like this:
SELECT
    Employees.name as EmpName,
    (SELECT COUNT(LateDays.LateDate)*200
        FROM Employees E
        INNER JOIN LateDays LD
            ON E.EmpId = LD.EmployeeEmpId
        WHERE E.EmpId = Employees.EmpId)
    as TotalFine,

Notice how I aliased the tables in the subquery so they had a different name from those in the outer query.  Then in the WHERE clause I referenced the table name from the outer query.
However this is not how I would write your query.  It will probably be less than well performing.  Instead you can use a pattern where you use a CASE statement to define your condition returning a 1 or a 0 then SUM the values.
The final query looks like this:
SELECT
    Employees.name AS EmpName,
    COUNT(LateDays.LateDate)*200 AS TotalFine,
    SUM(CASE WHEN LateDays.FinePaid = 1 AND LateDays.LateDate IS NOT NULL THEN 1 ELSE 0 END) AS PaidFine,
    SUM(CASE WHEN LateDays.Informed = 1 THEN 1 ELSE 0 END) AS InformedDays
FROM Employees
INNER JOIN LateDays
    ON Employees.EmpId = LateDays.EmployeeEmpId
GROUP BY Employees.EmpId, Employees.Name

A couple of things of note.  I am checking that LateDate is not NULL in the first SUM to match your COUNT.  COUNT doesn't actually count a value if it is NULL.  Second is that I changed the GROUP by to Employees.EmpId.  You generally do not want to GROUP BY a NAME column.  What happens when two employees have the same name?  It's rare but it does happen.  You are better off grouping on the ID column.
EDIT:

Based on having test data I made a few corrections to the query and have a question.
Change1: I had missed a comma in the SELECT list.
Change2: I had forgotten to add Employees.Name to the GROUP BY.  You should still have Employes.EmpId to ensure grouping by distinct employees but because you are pulling Employees.Name you do have to include it.
Question: In your test data rows 6 & 7 have the same date but the same EmployeeEmpId.  Should this be counted as two for the TotalFine calculation?
Note: Rows 6 & 7 of your test data have an EmployeeEmpId of 6 but that Id doesn't exist in your Employees table test data.  I changed it to a 3 for my testing.


If you are using Windows Authentication to connect you sql server, use following command.

bcp "por.dbo.audit" out "D:\tbackup\audit.dat" -S "SQLServerName/IP" -T -t -n

If using SQL Server authentication, use this one

bcp "por.dbo.audit" out "D:\tbackup\audit.dat" -S "SQLServerName/IP" -T -U Login -P password -t -n

Edit:1

The error appears because, giving variable as an input to 'bcp` accepted as a value. So if I use

bcp $Database+'.'+$Schema+'.'+$Table out $BCP_To+$Table+$ExporttoFile -S $Server -T -t -n

here $Database+'.'+$Schema+'.'+$Table assumed as single value Database.dbo.TableName (a table) and $BCP_To+$Table+$ExporttoFile is another value. (not taking as path).

So, instead of using above command in powershell. I prepared command to a variable and executed it using Invoke-Expression. You can say it dynamic powershell command execution.

Here is complete power-shell command to export you all tables of a database into .txt file.

[reflection.assembly]::loadwithpartialname('Microsoft.SQLServer.SMO') | out-null

$Server = 'SQLServer'
$Database = "Database"
$BCP_To = "D:\tbackup\"
$ExporttoFile =".txt"

[string]$Table
[string]$Schema

$Server = New-Object('Microsoft.SQLServer.Management.SMO.Server') $Server

foreach ($Tables in $Server.Databases[$Database].Tables | SELECT Schema,Name)
{
$Schema = $Tables.Schema
$Table = $Tables.Name

$BCP = 'bcp "'+$Database+'.'+$Schema+'.'+$Table+'" out "'+$BCP_To+$Table+$ExporttoFile+'" -S '+$Server+' -T -t -n'

$BCP = $BCP.Replace('[','')

$BCP = $BCP.Replace(']','')

Invoke-Expression $BCP
}

Hope it'll help you.

Thanks!


Calculating difference between consecutive timestamps. Error when i reach the hour mark


Yes, it should be working how you think.  Below is an example proving this:

-- create the dummy login
create login TestLogin1
with password = 'p@$$w0rd';
go

use TestDB;
go

-- create the test user
create user TestUser1
for login TestLogin1;
go

-- create the proc to create a table
create procedure dbo.CreateTableProc
as

    create table dbo.SomeTestTable(id int);

go

-- give TestUser1 perms to execute CreateTableProc
grant execute
on dbo.CreateTableProc
to TestUser1;
go

-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- unsuccessful, permission denied

  Msg 262, Level 14, State 1, Procedure CreateTableProc, Line 4
  CREATE TABLE permission denied in database 'TestDB'.

-- alter the proc to execute under my security context (with CREATE TABLE perms)
alter procedure dbo.CreateTableProc
    with execute as owner
as

    create table dbo.SomeTestTable(id int);

go

-- execute CreateTableProc under the security context of TestUser1
execute as user = 'TestUser1';
go

exec dbo.CreateTableProc;
go

revert;
go
-- successful, table created

  Command(s) completed successfully.

In this example, TestUser1 does not have the permissions to create the table.  We see that when the original version of CreateTableProc is called, as the CREATE TABLE DDL executes under the security context of TestUser1.  But then by modifying the CreateTableProc stored procedure definition and including the WITH EXECUTE AS OWNER clause, now TestUser1 can successfully call the proc and create a table because now the CREATE TABLE DDL executes under the security context of the owner (my database user, in the db_owner role).


SQL server Like pattern match with CONCAT() always fails

You are only matching on Author, not B_Name.  Change code to:

CREATE PROCEDURE [dbo].[Search_By_Author_And_Name]
(
    @Author_name AS [varchar](50)
)
AS
BEGIN
    Select B.*, R.R_number From Book AS B , Rack As R
    where (B.Author LIKE CONCAT('%', @Author_name, '%') OR
    B.B_Name LIKE CONCAT('%', @Author_name, '%'))
    AND B.B_Id=R.B_Id
END
GO


ScaleArc does not do Data Sharding

I actually wrote an earlier post where I evaluated it for my former employer : Experience using ScaleArc in test or production?

It does provide excellent read/write splitting using a single IP address and proprietary caching (ScaleArc's secret sauce).
You configure the ScaleArc server to have one or more Masters and One or More Slaves. It works great with Percona XtraDB Cluster as well as with Master with 1+ Slaves using MySQL Replication.
Each DB Server must have the complete dataset.

I doubt that you have the budget for multiple servers using 10TB of disk each.

What you might be looking for is ScaleBase not ScaleArc.


SQL Server Backup Job failed multiple times

Well based on Reason:15105 I think that SQL Server doesn't have permission to access to the path you check the permissions of the account that is assigned to the SQL service account? or other reason would be that there is not enough free space to create backup.


If PostgreSQL starts after copying data, is the data ok?

It is not safe to assume that the data is OK just because PostgreSQL starts. Pg does not do any kind of comprehensive verification run on the DB contents at startup. If it did it could take hours (or days or weeks for bigger DBs) to start.

It doesn't have any verification tools. The argument is that these should not be needed if the data is managed correctly. I'm coming to question that argument in the wake of the 9.3.x (x < 4) multixact corruption bugs, but building verification tools isn't a small job.

If the 64-bit server is running 32-bit PostgreSQL binaries, and they're compiled with the same options, it'll be fine.

If the 64-bit server is running 64-bit PostgreSQL binaries then you didn't copy the datadir in place, because 32-bit and 64-bit PostgreSQL data directories are not compatible and PostgreSQL will refuse to start.

My concern is about how you copied the data dir. You must stop the origin and destination PostgreSQL servers before copying if you just do a simple file system level copy. To live copy you must use one of the techniques documented in the manual - pg_basebackup, pg_start_backup() + rsync + pg_stop_backup() + WAL archiving, etc.

If you copied the datadir without stopping the server it might appear to be copied correctly, but it's not safe to use and may be subtly or not so subtly corrupt.


Is it possible to have passwords configured per database or per host in .my.cnf

As I answered here, you can add a section for each user/host/db you connect to using the syntax in your ~/.my.cnf:

[clienthost1]   # Note: client + host1
user=myuser
password=mypass
database=dbname
host=server.location.com

Once this is in your user's .my.cnf, you can utilize it by doing this on a command line:

$ mysql --defaults-group-suffix=host1


What queries are supported by MySQL but not PostgreSQL?

re 1) very often they can be replaced with other features (e.g. I have seen query variables being used to simulate window functions or pass the result from one query to another)

re 2) Postgres has insert ... on conflict()

re 3) Postgres has logical replication

re 4) Postgres has GROUP BY ROLLUP

re 5) That's true. Other alternatives are pg_cron or pg_timetable

re 6) I have worked more (and longer) with Oracle than I have worked with Postgres. And I can count the number of times I needed a table with a clustered index (called "index organized table" in Oracle) on my two hands in the last 10 years. So I don't really miss them in Postgres (although I do agree it would come in handy sometimes).

  What other queries or features would one need to change?

In general, queries that are invalid (standard) SQL.

You already noticed that Postgres complies with the SQL standard and uses  || as the string concatenation operator, not for OR).

Integer division is different. 1/10 yields 0 in Postgres (because both arguments are integers) whereas MySQL returns a decimal value.

Then there is the invalid use of GROUP BY, invalid use of MySQL's interpretation of "boolean" expressions, invalid DATE values (2020-02-31or 0000-00-00), Postgres also does not allow to divide by zero.

Postgres is much stricter when it comes to matching datatypes (varchar_column = 1 will fail), while MySQL usually lets you get away with comparing apples to oranges.

Non-standard quoting using those dreaded backticks ` (in general do not quote identifiers at all).

Accept that a "database" in MySQL is really a schema (as seen by Postgres and the SQL standard). If you have multiple databases in MySQL you most probably want a single database with multiple schemas in Postgres.

There are other syntax differences, e.g. date arithmetic works differently in MySQL and Postgres, including interval literals. Many functions have different names (string_agg() vs. group_concat()).

Obviously several data types are different as well (e.g. the infamous int(11))

P.S.: I maintain a more up-to-date feature comparison in this answer


Performing group by, Order by on a table starting from particular date to recent last date

You just need to add a WHERE clause in the statement, like: where Date >= '2016-03-05':

select v.Date, count(v.id) as co
from volunter as v
where v.Date >= '2016-03-05'
group by v.Date
order by v.Date ;

Details may differ depending on the DBMS you use. For SQL Server for example, the safe format for the date and datetime types is 'YYYYMMDD', so it would be better to write the where as where v.Date >= '20160305'. In Oracle and Postgres you could use where v.Date >= DATE '2016-03-05'.
Date is a reserved keyword in some DBMS, so it would be better to choose another name for that column. Or always prefix it with the table name/alias.


Granting SA privileges for Developers on the development box

If it's not too late, one compromise option that I've seen work well is rather than upgrade the permissions or replacing the developers' existing accounts, create a separate account that is only used when they need the elevated permissions.

So normally they work under individual "restricted" accounts (which I use loosely because these restricted accounts still need some hefty permissions — ie create, drop, alter for tables).  But for that rare occasion when they think they need sa, they can log in using this account.  Then you can flag the account in your logs and do extra monitoring on it.  You've given the developers the access they asked for, but in a way that's a little more controllable.

Eventually, if there's abuse, the logs on this account can be used as evidence to take it away.


The error isn't coming from the trigger.   You could confirm this by dropping the trigger and you will see the same error.

ERROR 1416 (22003): Cannot get geometry object from data you send to the GEOMETRY field

You didn't mention it in the question, but it looks like your user_geometry column is defined with a GEOMETRY data type.  To use a GEOMETRY data type, you have to insert a GEOMETRY object.  You can't coerce it later with a trigger because it's validated very early, before the BEFORE UPDATE trigger.

Remove the quotes and add a comma and you have a query that would work and not need the trigger:

INSERT INTO `users` (`id`, `name`, `email`, `password`, `user_geometry`)
VALUES (NULL, 'arash', 'arash@host.com', 'sssssss', POINT(-0.23,169.93));

The POINT() function takes 2 arguments and returns a geometry object.


Apply settings to multiple output columns in SSIS flat file source

In the advanced tab of the connection manager of the flat file data source. You can mark multiple columns by holding CTRL and then set the properties they have in common.


MySQL can't connect using private IP address

I have reinstalled MySQL and now everything works.


The log for database 'tempdb' is not available

That's likely a memory problem, or possible database corruption somehwere in your system. Run CHECKDB on all of your databases, and ensure that you do not have any hardware problems.
As for tempdb getting recreated, well yes, in a sense that is true, all objects are cleared, but the files themselves are not recreated (except in the circumstance that you manually delete the files from the OS). I Would recommend reading this post on the zeroing out of log files in tempdb.


Invalid use of a side-effecting operator 'change_tracking_min_valid_version' within a function

As Aaron mentions, this logic could be done in a view rather than a function. If you do need to use a function, however, an alternative might be to use sys.change_tracking_tables rather than CHANGE_TRACKING_MIN_VALID_VERSION. It has a min_valid_version column, and as far as I can tell from the documentation it provides the same information you are looking for.

CREATE FUNCTION MY_CHANGE_TRACKING_MIN_VALID_VERSION(@synonym VARCHAR(100))
RETURNS BIGINT
BEGIN
    RETURN (SELECT c.min_valid_version
    FROM sys.synonyms s
    JOIN sys.change_tracking_tables c
        ON c.object_id = OBJECT_ID(s.base_object_name)
    WHERE s.name = @synonym);
END
GO

What are the side-effects of CHANGE_TRACKING_MIN_VALID_VERSION?

As far as I can tell from the documentation, there aren't any "side-effects" that will meaningfully impact you. As Aaron mentions, you'll get the same error if you try to use NEWID in a function. In both cases, I would suspect (but am not sure) that NEWID and CHANGE_TRACKING_MIN_VALID_VERSION are accessing and/or modifying an internal data structure (e.g., a random generator in the case of NEWID) and SQL Server is biasing towards caution in rejecting their usage in a function.

-- This generates the same error
CREATE FUNCTION testNewIdInFunction()
RETURNS UNIQUEIDENTIFIER
BEGIN
    RETURN (SELECT NEWID());
END


How to achive a many to one merge join in Azure Data Warehouse

Are you getting this info from an EXPLAIN plan or the new SSMS plan functionality for Azure SQL Data Warehouse?  Please provide.

In the "box product" engine (SQL Server 2017 14.0.3008.27), I can reproduce converting a many-to-many MERGE join to a one-to-many using the DISTINCT keyword against a columnstore table.  I can't guarantee this would behave the same in Azure SQL Data Warehouse however and it won't apply to all queries:

SELECT *
FROM dbo.Numbers a
    INNER JOIN
        (
        SELECT TOP 10 Number
        FROM dbo.Numbers
        ) b ON a.Number = b.Number
OPTION ( MERGE JOIN );
GO

SELECT *
FROM dbo.Numbers a
    INNER JOIN
        (
        SELECT DISTINCT TOP 10 Number
        FROM dbo.Numbers
        ) b ON a.Number = b.Number
OPTION ( MERGE JOIN );

Full script available here.  My results:


Using PostgreSQL to hold an array of data

Create a view on the table that joins with the enum table and shows an array of text.
Then create an INSTEAD OF INSERT OR UPDATE trigger on the view that translates the strings to numbers and stores the number array in the underlying table.


Invoice days and amount with Distinct Value

You'll want to check out the datediff function, see below example.

select branch,
       userid,
       customer,
       billtype,
       case when datediff(d,billdate,getdate()) >= 0 and datediff(d,billdate,getdate()) <= 30 then billingamount else 0 end [0 - 30 days],
       case when datediff(d,billdate,getdate()) >= 31 and datediff(d,billdate,getdate()) <= 60 then billingamount else 0 end [31 - 60 days],
       case when datediff(d,billdate,getdate()) >= 61 and datediff(d,billdate,getdate()) <= 90 then billingamount else 0 end [61 - 90 days],
       case when datediff(d,billdate,getdate()) >= 91 and datediff(d,billdate,getdate()) <= 120 then billingamount else 0 end [91 - 120 days],
       case when datediff(d,billdate,getdate()) >= 121 then billingamount else 0 end [121+ days],
       billingamount as total
  from #test


Two substantially different but similar cases: Are they the same in terms of table cadinality?

It's common to confuse a Thing (a physical book) with the Specification of that Thing (the idea of the book)

Say you have a Book Specification:

Title: Moby Dick
Format: Hard Cover
ISBN: 0840769008

Then you also have a Book Asset:

ISBN: 0840769008 (refers to Specification)
Serial Number: 1234
Condition: poor

People can borrow a Book Asset (I borrowed a particular copy).

People can reserve a Book Specification (I want to borrow that book but I don't care which copy I get)

So in your case, for one book, you'd have a specification for it, and one asset. For multiple, you'd have one specification and multiple assets

You'd design the same schema for both scenarios, by using Specification has many Assets, Asset belongs to Specification


In PostgreSQL, can you read from an index during a ALTER INDEX ... SET TABLESPACE operation?

Try to recreate the index concurrently, so the original one is still used while creating the new.

CREATE INDEX CONCURRENTLY idx_name
ON table_name
USING BTREE(col_name)
TABLESPACE new_tblspace


How to group by week even when the count is 0

Could the solution be as simple as using a recursive CTE to generate a numbers table of the weeks in the year and then left joining to your main query?  Using coalesce to output either the real amount or zeros when no match on week number is found?

--demo setup
Declare @ResultsTable table (
  [week] INTEGER,
  amount INTEGER
);

INSERT INTO @ResultsTable
  ([week], amount)
VALUES
  ('4', '354'),
  ('6', '222'),
  ('7', '144'),
  ('8', '354'),
  ('9', '45'),
  ('10', '55'),
  ('11', '76'),
  ('12', '98'),
  ('13', '45'),
  ('14', '344');

--recursive cte to generates week numbers
;WITH WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber < (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN @ResultsTable rt
    ON rt.[week] = wn.WeekNumber

| week | amount |
|------|--------|
| 1    | 0      |
| 2    | 0      |
| 3    | 0      |
| 4    | 354    |
| 5    | 0      |
| 6    | 222    |
| 7    | 144    |
| 8    | 354    |
| 9    | 45     |
| 10   | 55     |
| 11   | 76     |
| 12   | 98     |
| 13   | 45     |
| 14   | 344    |
| 15   | 0      |
| 16   | 0      |
| 17   | 0      |
| 18   | 0      |
| 19   | 0      |
| 20   | 0      |
| 21   | 0      |
| 22   | 0      |
| 23   | 0      |
| 24   | 0      |
| 25   | 0      |
| 26   | 0      |
| 27   | 0      |
| 28   | 0      |
| 29   | 0      |
| 30   | 0      |
| 31   | 0      |
| 32   | 0      |
| 33   | 0      |
| 34   | 0      |
| 35   | 0      |
| 36   | 0      |
| 37   | 0      |
| 38   | 0      |
| 39   | 0      |
| 40   | 0      |
| 41   | 0      |
| 42   | 0      |
| 43   | 0      |
| 44   | 0      |
| 45   | 0      |
| 46   | 0      |
| 47   | 0      |
| 48   | 0      |
| 49   | 0      |
| 50   | 0      |
| 51   | 0      |
| 52   | 0      |
| 53   | 0      |

So, using your original query incorporated into a Common Table Expression, the final solution might look something like this:

;with ResultsTable as
(
SELECT
DATENAME (WK, DATE) AS WEEK,
COUNT (DISTINCT COMPANY_ID) AS AMOUNT
FROM
(
SELECT COMPANY, DATE = MIN(DATE)
FROM TABLE1 A INNER JOIN TABLE2 B
ON A.ID = B.ID
WHERE YEAR(A.DATE) = '2019' AND COMPANY_ID NOT IN(SELECT COMPANY_ID  FROM
TABLE1 A INNER JOIN TABLE2 B  ON A.ID = B.ID AND DATE < '2019-01-01') GROUP
BY COMPANY_ID) d

GROUP BY dateadd(wk, datediff(wk, 0, DATE), 0),  DATENAME(WK, DATE)
)
,WeekNumbers (WeekNumber)
AS (
    SELECT 1 AS Number

    UNION ALL

    SELECT WeekNumber + 1
    FROM WeekNumbers
    WHERE WeekNumber < (
            SELECT datename(week, '2019-12-31')
            )
    )
--join cte to original results
--when no match on week number, set amount to 0
SELECT wn.WeekNumber AS [week]
    ,COALESCE(rt.amount, 0) AS amount
FROM WeekNumbers wn
LEFT JOIN ResultsTable rt
    ON rt.[week] = wn.WeekNumber


fragmented index not defragmenting after an index rebuild

Yes I see that fragmentation all the time with GUID columns, due to the random inserts and page splits. In your case, this could be a result of a non-standard fill factor to account for GUID fields.

A clustering key should be on an ever-increasing, narrow data type, like INT or BIGINT, and the fill factor set as close to 100 (or 0) as possible.

That all said, rebuilding a clustered index due to fragmentation is a time-consuming process, and it generates a lot of transaction log, so it's generally more accepted to simply update the statistics fairly frequently.

You can worry about fragmentation less frequently, say once a month.


mongodb crashed with the error or permission denied

It's not mongodb fault. Your system went to "read only" mode.
You can see errors on that log file. Positive is that system managed to write that information to log file because it makes easier to debug this problem.

/var/lib/mongo/journal: opendir: Permission denied
couldn't open [/proc/1281/stat] errno:13 Permission denied

Check /var/log/syslog (or /messages) for more info, something fatal happened on that node and it's operating system!


Not able to update MySQL table using Oracle: Unknown column in 'where clause' {42S22,NativeErr = 1054}

by experience sometimes creating oracle tables by using ODBC produce errors.

so please try to do the following:

create the table manually on oracle db by using sql plus.
try to use capital letters for table and column names without double quotes (example: use TABLE_NAME not table_name or Table_Name)
try to run your application to check if any errors appears.

also in where clause you have to check it.
try:

WHERE SRC."INT_ID" = REC.MYSQL_INT_ID;

does it works?


How to Set the Transaction Isolation level in a Table Value Multi Function

You can't set the transaction isolation level inside a function.  You can use lock hints on the queries used in the functions instead.

Although you should really avoid using either READ UNCOMMITTED or the nolock lock hint.


SQL Server database has corruption, but displays no errors

This might help someone with a similar problem.  There was nothing wrong with the database, we needed to install an update.  A service pack I believe.  Then everything was fine.


SQL Server: Log Shipping in "restoring" state: Can I temporarily take it out of restoring to check it?

I would suggest configuring the log shipping to STANDBY mode instead of NORECOVERY.  This will allow you to query the secondary db for any reporting, etc.

When you use NORECOVERY mode, the secondary database will not allow any users to access it, so the database does not have to worry about uncommitted transactions. The log can just be restored as it currently sits without worrying about any users accessing/changing things.

When you use STANDBY mode, the database restores in NORECOVERY mode, then looks at and rolls any open transactions. Once that is complete, users can access the database and it will be in read-only mode. When the next log is restored, the database disconnects all users and repeats the rollback/forward process and then, again, places the database in read-only mode. Lather, rinse, repeat.

Think of it as a transaction log restore.  When you restore each transaction log, you leave the DB in NORECOVERY so that you can continue to append any additional log files until you have restored to the point you require.  Then, you set it to RECOVERY and the database is able to be read. Make sense?

@DenisT brought up a good point that the 'disconnect all users' option must be checked in order for all users to get booted.  Otherwise, the log restore will fail.

Here's a great article on it
http://askmesql.blogspot.se/2011/01/log-shipping-norecovery-vs-standby-mode.html


Perl - MySQL/MariaDB - slow with no identifiable bottleneck

Conclusion and a workaround

After exhausting all options on Windows, I decided to switch to Linux, mostly because I was frustrated with inability to profile and debug in detail.

I have moved the whole setup to Ubuntu 14.04. I first tried XAMPP but gave up because of conflicts between XAMPP and MySQL and MySQL Workbench.  Then I moved to vanilla MySQL (5.5, I think) and vanilla Apache 2.

However, I was still left with the same problem – no visible bottleneck and resources still underutilized.   I suspected throttling in TCP sockets (used between Perl code and MySQL), but further profiling proved this not to be the case.

Then, I turned my attantion to Perl DBI module DBD::SQL, thinking that it may be doing some throttlinig.  I did some tests where I replaced DBI calls in Perl with system calls (system("mysql -e'INSERT INTO blah blah …').  I have determined that the performance did not change, therefore absolving DBI as a culprit.

I need to add one important detail now: I was in fact always running a number of my Perl scripts in parallel.  Given that the CPU has 8 cores, this is necessary to utilize all of them, of course.
Further debugging showed that almost all my perl processes which were supposed to work furiously
were sleeping most of the time. Ubunty System Monitor shawed them as waiting on Waiting Channels  wait_answer_interruptible or  unix_stream_recvmsg.  CPU History graph in System Monitor showed all perl processes jumping to 100% CPU utilization and then dropping to ~0% in unison.  I suspected that MySQL server is not configured for multi threading, but htop showed 17 mysqld threads activated, confirming that all should be ok.
I suspected that all MySQL threads were waiting on the same semaphore and were locked out for most of the time.  I dreaded delving into the dark bowels of MySQL trying to figure out what goes on inside. Instead, I decided to replace MySQL with MariaDB, even though MariaDB seems to have had the same issue originally when I was running it on Windows.

Lo and behold – this finally worked.  My perl scripts were screaming.

One last problem remained: I had a very rudimentary method of parallelising the perl scripts: I would just run 10 or 20 with their respective loads and hope that they would utilize all the resources.

This has obvious drawbacks: if too many processes are spawned, the OS may spend too much time swapping them (although not a serious issue with only 20 processes, it becomes an issue with e.g. 1000).   If not enough processes are spawned (e.g. less than 8, for each core) the CPU will not be utilized fully for sure.   If too many processes exhaust RAM, Linux will turn to disk and will start swapping.  As soon as this starts happening, everything grinds to a halt.

I searched but could not find a perl library/script/code which would spawn new processes only when CPU, memory and disk are under utilized.  Hence I created my own: raspawn.pl (resource aware spawn) which I placed on github.  Raspawn.pl spawns a number of processes while trying to keep resources utilization just below the maximum.  It constantly checks the CPU, memory and disk utilization and only if all are less than ~90% utilized, starts a new process.

Finally, this worked.  I can now process my whole load in around 7 days, instead of many months...


Is it possible to represent a ‘mutual IS-A’ relationship in an entity-relationship diagram?

In databases, subtyping means that one domain is a subset of another. If A is a subset of B and B is a subset of A, then it means A = B. The same logic applies whether A and B are entity sets or relationship sets. There would be no point distinguishing A and B in an ER model.


A lock will be taken by whichever process accesses the row (or table) first, and the second process will have to wait until the lock is released. This happens automatically.

Occasionally, there will be a deadlock where two processes attempt to update the same record, and one of these will be killed by SQL Server. The resolution there is to retry the process again.

In your example, if two users try to update a row at the same time, the first user there will change it, and then the second user will change it again once the lock is released. That's literally how it was designed to work.

If you want to prevent the second user from changing the record because the first user already changed it, that's application logic, well outside of the scope of SQL Server.


How to drop table with all reference to this table

You can use the following:

  The following query will generate all the keys which you have to drop auto, then simply run:

SELECT CONCAT ('alter table ',
               TABLE_SCHEMA,
               '.',
               TABLE_NAME,
               ' DROP FOREIGN KEY ',
               CONSTRAINT_NAME,
               ';')
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
   AND TABLE_NAME = 'YourTableName';


Best way to remap database login to new database user and keep previous database user

What is the best way to go about this? If database user dbuser02 is
  created and mapped to aduser01, what are the possible states dbuser01
  could be left in?

You can't create different database users mapped to the same login because a database user can be mapped to no more than one login.  The CREATE USER statement for dbuser02 failed so dbuser01 is still mapped to aduser01 and dbuser02 was not created.

I'm not exactly sure what you are trying to accomplish.  If your intent is to selectively grant database access to some, but not all, members of the mydomain\group01 and specify an individual default schema for each user, try the script below.

--add all members of mydomain\adgroup01 as role members
EXEC sp_addrolemember N'db_mydbrole', N'mydomain\adgroup01';
--create a default schema and user for desired mydomain\adgroup01 members
CREATE SCHEMA [aduser01] AUTHORIZATION [aduser01];
CREATE USER [mydomain\aduser01] WITH DEFAULT_SCHEMA=[aduser01];
CREATE SCHEMA [aduser02] AUTHORIZATION [aduser02];
CREATE USER [mydomain\aduser02] WITH DEFAULT_SCHEMA=[aduser02];


You will need to put the port number in there.  When using the default instance the SQL Browser is not contacted in order to get the port number to use to connect.  Because of this you have to specify the port number when connecting otherwise the OLE/ODBC driver doesn't know what TCP port number to connect to.

Please clarify how you took a standalone server and made it a cluster.  That isn't supported unless you uninstalled SQL and reinstalled it.


Tighten query caching scope, and periodical recaching

There are two types of "caching" in MySQL

the basic caching of data and index blocks
the "Query cache".

Both have configurable limits.

"Cache" implies that a finite amount of space is reserved.  When you "run out" of the space, something is bumped out of cache to make room for the new item.  The one bumped out is usually the "least recently used", which, without other info, is the least likely to be needed again soon.

So, I don't see what the problem is.

On the other hand, maybe you are asking a different question...  With the "Query cache", SELECTs are saved in it.  This cache keeps the queries and their result sets.  (Again, when it fills up, old entries are bumped out to make room for new ones.)  A major drawback of the QC is that when any write occurs, all entries in the QC for that table are purged.  So, for tables that are frequently written to, the QC is virtually useless.

For the few systems that can effectively use the QC, here are my recommendations:

query_cache_type = DEMAND
query_cache_size = 50M  -- no more than this, else purging is too costly
SELECT SQL_CACHE ...    -- on queries that can benefit from the QC
SELECT SQL_NO_CACHE ... -- for the rest


What would a schema look like for setting up tables for RPG character creation restrictions?

There's not a specific schema name for what you're trying to achieve but there are two ways you can go about achieving it depending on which database management system you end up using.

You can create one linking table for each combination of values. Every legal combination will get it's own row, and have a foreign key field of the id of each base table that it's linking together. This is generally known as a dimension combination table.

You can alternatively look into having a more denormalized table that stores each instance of the character that's been selected, with foreign key refencing fields to each id of the base tables, with a composite constraint on what value combinations can be entered in each field. (The availability of this kind of complex constraint feature will be dependent on the database system you use.)


How to find which SQL server instance is consumimg more CPU on 4 node / 20 instance Windows cluster?

Glenn Berry has a set of DMV scripts, in each of them there is a CPU utilization for the last 256 minutes for the instance (query number 40 for SQL 2016 script). Run this on each instance. And if you have CMS setup correctly, you can run the script against a group of instances at the same time.
the link to Glenn Berry's page:
http://www.sqlskills.com/blogs/glenn/sql-server-diagnostic-information-queries-for-september-2016/


Does CommVault use native SQL Server backup COM objects or the VSS?

I used CommVault at a previous job. Version 9.4+ did both native and VSS snaps.


Add column to a table for every shema in an oracle db 11g

begin
  for t in (select * from dba_tables where table_name = 'EMER_COMP')
  loop
    execute immediate 'alter table "' || t.owner || '"."' || t.table_name || '" add TELEFONE varchar2(20) NULL';
  end loop;
end;
/


Return a specific value based on the min between 2 dates in SQL Server

Given the sample data you provided (in a more regional-friendly format):

CREATE TABLE #Table1(name char(2),[date] date,type char(6));

INSERT #Table1 VALUES
('aa','20180910','Client'),('bb','20181012','Client'),('cc','20181016','Client'),
('dd','20181112','Client'),('ee','20181015','Client'),('ff','20181117','Client'),
('gg','20181212','Client'),('tt','20181111','Server'),('kk','20181215','Server');

CREATE TABLE #Table2(ID int,type char(6),[Date Created] date);

INSERT #Table2 VALUES
(1,'Client','20181125'),(2,'Client','20181025'),(3,'Client','20181117'),
(4,'Client','20180925'),(5,'Client','20181110'),(6,'Client','20181205'),
(7,'Client','20181102'),(8,'Server','20181119'),(9,'Server','20181210');
GO

The following query:

;WITH x AS
(
  SELECT t2.ID, t2.type, t2.[Date Created], t1.name,
    rn = ROW_NUMBER() OVER (PARTITION BY t2.ID ORDER BY t1.date DESC)
    FROM #Table1 AS t1
    INNER JOIN #Table2 AS t2
    ON t1.type = t2.type AND t2.[Date Created] >= t1.date
)
SELECT ID, type, [Date Created], name
  FROM x
  WHERE rn = 1
  ORDER BY ID;

Produces these results:

ID  type    Date Created    name
--  ------  ------------    ----
1   Client  2018-11-25      ff
2   Client  2018-10-25      cc
3   Client  2018-11-17      dd
4   Client  2018-09-25      aa
5   Client  2018-11-10      cc
6   Client  2018-12-05      ff
7   Client  2018-11-02      cc
8   Server  2018-11-19      tt
9   Server  2018-12-10      tt

Which differ from your expected results on row 3 and row 5, but I think that's a mistake in the expected results.


You probably have lots of large transactions or one really big transaction.
Take a look at Vadim Tkachenko's Pictorial Representation of InnoDB

If you look inside the system table (ibdata1), you see rollback segments and undo logs.
When a rollback segment rolls back a transaction, it has to use the MVCC information it stockpiled in the undo space. That's where most of the growth occurs. Percona mentioned this back on Jun 10, 2010 (Reasons for run-away main Innodb Tablespace)
I have discussed this before

Apr 23, 2013 : How can Innodb ibdata1 file grows by 5X even with innodb_file_per_table set?
Mar 31, 2014 : mysql directory grow to 246G after one query, which failed due to table is full
Jun 16, 2014 : MySQL Index creation failing on table is full

UPDATE 2015-03-22 15:00 EDT
If you are convinced the data dictionary is the cause, there is only one sure way you can verify this.

STEP 01) Get another DB Server (Dev or Test)
STEP 02) Install MySQL on it
STEP 03) mysqldump the schema-only (using --no-data)
STEP 04) load the schema-only dump into the empty database
STEP 05) run ls -l /var/lib/mysql/ibdata1 | awk '{print $5}'

The number that comes out will be the size will be the size of ibdata1 with just data dictionary entries for the current list of tables, but with no data in them.
In your last comment, you said

So open transaction doesnot seem to be right reason. We use to create some 200-300 table on daily basis (by our ETL tool) and drop them after some processing periodically ( drop some 2k-5k tables once in 2-3weeks), could this attribute to the big file ?

You could be right for same reason: Just like rollback segments and the undo space have no provision for reclaiming unused diskspace for the OS, dropping tables and creating new ones would likewise have no provision for reclaiming unused diskspace for the OS.
To prove that, you would have to use that new server with all tables but no data. Then, perform the same dropping and creating of tables every week. If the ibdata1 grows just for doing that, then you have proven undeniably that dropping and creating tables alone can grow ibdata1 with innodb_file_per_table enabled.
If you want to cleanup ibdata1, please see my posts

Sep 26, 2012 : How do I shrink the innodb file ibdata1 without dumping all databases?
(on why you have to dump all data, remove ibdatat1, and create a new ibdata1)
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (on how to actually do this)

GIVE IT A TRY!!!


Copy DBF files from production to development environment

Do you have an existing database on the development env. and you want to refresh it or is it going to be a new database?  And if this is a new database, do you have the Oracle software installed on the server?

If the database already exists, then the easiest way to refresh is to export the required schema(s) from the production database and import into the dev. database.

I'm providing a very crude example using datapump, but you should follow and read the links @a_horse_with_no_name provided in the comments above:

Export:

expdp system DIRECTORY=your_db_dir DUMPFILE=your_out_file.dmp LOGFILE=your_log_file.log SCHEMAS=schema_a,schema_b CONSISTENT=Y COMPRESSION=ALL

Import (you might need to drop the schemas first or you might need to add the TABLE_EXISTS_ACTION parameter):

impdp system DIRECTORY=your_db_dir DUMPFILE=your_out_file.dmp LOGFILE=your_log_file.log FULL=y

If however you want to refresh the database by copying the DBF files, you have a lot more things to take into consideration.  The very high level steps would be:

Create pfile from spifile from Prod database
Shutdown Production and Development database
Copy DBFs, control files and pfile from primary to development
Do any necessary changes in the pfile to reflect dev. env.
Mount the dev. database using pfile
Change DBF directory names if different from Prod using 'alter database rename file'
Recover database if needed
Open the database and create spfile

As you can see, this also involves downtime on your production env. and is much more complex.  I would recommend using this approach only if you know what you are doing or with an experienced DBA.  But it would be a good learning experience if you could try this yourself on a separate dev. env. (and if you have enough time and patience to experiment).


How to export oracle database version 12.1.0.2.0 with expdp Release 12.2.0.1.0

Are you sure about the target and source version?
I did here and worked right.
My source DB version was 12.1.0.2.0 and the target was 12.2.0.1.0.
Please, see my example below:
$ cat myfile.par
directory=DUMP
dumpfile=MYFILE.DMP
logfile=MYFILE.LOG
content=ALL
schemas="DIEGO","HR","USER"

$ expdp user@mypdb parfile=myfile.par

Exporting and Importing Between Different Database Releases

In an upgrade situation, when the target release of a Data Pump-based migration is higher than the source, the VERSION parameter typically does not have to be specified because all objects in the source database will be compatible with the higher target release. An exception is when an entire Oracle Database 11g (release 11.2.0.3 or higher) is exported in preparation for importing into Oracle Database 12c Release 1 (12.1.0.1) or later. In this case, explicitly specify VERSION=12 in conjunction with FULL=YES in order to include a complete set of Oracle internal component metadata.
In a downgrade situation, when the target release of a Data Pump-based migration is lower than the source, the VERSION parameter should be explicitly specified to be the same version as the target. An exception is when the target release version is the same as the value of the COMPATIBLE initialization parameter on the source system; then VERSION does not need to be specified. In general however, Data Pump import cannot read dump file sets created by an Oracle release that is newer than the current release unless the VERSION parameter is explicitly specified.


Table size reduce with help changing collation charset [MySQL]

MySQL's documentation for utf8 shows that it will use 1 byte for Latin characters, and only use more if the situation requires them.

Therefore, if you're only using normal latin characters, both utf8_general_ci and latin1_general_ci will use between 1 and 4 bytes: one byte to store the length (0-3 characters), and then up to three bytes for the actual text.

If the value of this field will always be exactly three characters, you could save one byte by changing the column from a varchar(3) to a char(3) (as long as you also use latin1_general_ci).

If it's not exactly three characters, it's likely to be more efficient to leave it as a varchar - there's not much more you can do to reduce the size of this field. However, with a 40GB table you can hopefully find something else to optimise!


You have a couple options. The basic thing is that dead tuples in the middle of the table, as you have guessed are not reclaimed for the filesystem with autovacuum.  Rather they are marked as free so that future inserts in the same table can use that space.  The space isn't bloated permanently.  It is available for re-use within the same table regarding future inserts or updates.

If you want to reclaim that space for the filesystem you have two options:

1)  vacuum full.  This locks things.  It is usually not ideal but sometimes it is the best option.  Also it takes a while.

2)  cluster on an index.  This rewrites the table into a new file.  It requires a lot more disk space, but is also a lot faster.  It does fully return free space back to the filesystem at the end however.

Both these require table locks.  They are not concurrent safe.  You don't want autovacuum deciding that now is a good time to lock tables for you so this is just the way it is.

Edit:  This wasn't obvious from the question initially but from the comment reply it looks like the problem here is that the free space map settings may be insufficient.  These settings were removed in 9.1 iirc but basically set the size of the free space map per table.  What happens in this case is that the free space map is insufficient to hold all dead tuples.  In this case tuples get deleted but the space can't be re-used.

When this happens bloat is the result and vacuum full or cluster can be necessary to recover space.  As an intermediate solution barring these, you can significantly increase your free space map settings and re-run vacuum (regular, not full) to update the fsm.  This won't recover space, but it will make more of the space available for re-use.


DECLARE @docNo nvarchar(255)
DECLARE @message_body xml = N'<inserted DocNo="12345"/>'

SELECT
    @docNo = @message_body.value('/inserted[1]/@DocNo', 'nvarchar(255)')

SELECT @docNo


Is there any way to get the optimization time in PostgreSQL?

Yes, starting with Postgres 9.4 this information is included in the execution plan when using the analyze option:

psql (9.4.1)
Type "help" for help.

postgres=> explain (analyze,verbose)
postgres-> select count(*)
postgres-> from product;
                                                                             QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=8431.92..8431.92 rows=1 width=0) (actual time=132.491..132.491 rows=1 loops=1)
   Output: count(*)
   ->  Index Only Scan using product_pkey on public.product  (cost=0.42..7181.92 rows=500000 width=0) (actual time=0.622..91.129 rows=500000 loops=1)
         Output: product_id
         Heap Fetches: 8
 Planning time: 2.756 ms
 Execution time: 132.614 ms
(7 rows)