However I am presented with the following error

  "SUBSTRING": invalid identifier"

The provided query should be able to run, here is a test on SQL Server 2017

Unless your tag is wrong and you are using oracle?

Then you would get an error like this:

  ORA-00904: "SUBSTRING": invalid identifier

DB<>Fiddle

If that is the case, then you could use regexp_replace instead.

select regexp_replace(indicator, '[^0-9]', '') from table1

Resulting in

123456
401015

999999

DB<>Fiddle

Solution for SQL Server

  Below is small Subset of the data. What I would like to do is produce
  another column which extracts the numbers from indicator column.

If you want to get the numbers from the column and they are always one series of continuous numbers, you could do this

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING (Indicator, PATINDEX('%[0-9]%', Indicator), LEN(Indicator) )), PATINDEX('%[0-9]%', REVERSE(Indicator)), LEN(Indicator) ))
FROM table;

Table & Data

create table #temp
(
indicator nvarchar(80)
);

INSERT INTO #temp
VALUES
('GB-JP--123456'),
('401015 - GB-NA'),
('1'),
('999999 - LX - UK');

Result

(No column name)
123456
401015
1
999999

DB<>Fiddle

Remember that due to using the LEN() function, trailing spaces are a potential issue.

INSERT INTO #temp
('1   ')

Returns an empty string when using above query.

DATALENGTH() can resolve this (divided by 2 due to the nvarchar datatype, not that it really matters when using SUBSTRING like this)

SELECT REVERSE(SUBSTRING(REVERSE(SUBSTRING (Indicator, PATINDEX('%[0-9]%', Indicator),( DATALENGTH(Indicator)/2))), PATINDEX('%[0-9]%', REVERSE(Indicator)),( DATALENGTH(Indicator)/2) ))
from #temp;

Comparison DB<>Fiddle


Oracle: Traversing a graph of my foreign keys?

I usually use this statement.

with fk_list as (
  select parent_table.table_name parent,
         parent_cons.constraint_name as pk_constraint,
         child_table.table_name child,
         child_cons.constraint_name as fk_constraint
  from user_tables parent_table
    join user_constraints parent_cons on parent_table.table_name = parent_cons.table_name and parent_cons.constraint_type IN ('P', 'U')
    join user_constraints child_cons on child_cons.r_constraint_name = parent_cons.constraint_name and child_cons.constraint_type   = 'R'
    join user_tables child_table on child_table.table_name = child_cons.table_name and child_table.table_name <> parent_table.table_name
)
select level,
       fl.child,
       (select listagg(fk.column_name,',') within group (order by fk.position) from user_cons_columns fk where fk.constraint_name = fl.fk_constraint) as fk_columns,
       fl.parent,
       (select listagg(pk.column_name,',') within group (order by pk.position) from user_cons_columns pk where pk.constraint_name = fl.pk_constraint) as pk_columns
from fk_list fl
where fl.child <> fl.parent
start with fl.parent = 'root table'
connect by prior fl.child = fl.parent
order by level;

It is not the most efficient one, but it does work for me.

Note that it requires 11.2 due to the use of listagg()


If you are doing this from an application (VB.Net or C++ or php or whatever), then you must load the data in chunks, if the data is larger than 4000 bytes (thanks, mustaccio) OR if the data contains values that can't be sent as a string (Binary data).

The process is to take your data, split it into an array of byte "chunks" that are each somewhere less than 4000 bytes per chunk. The examples below provide ways to do this in a few languages.

Here is a PHP example from Oracle for using byte chunks.

Here is a C++ example from Stack Overflow.

Here is a C#.Net example from Code Project.

If you're doing this from scripts, check out SQL*Loader.

LOAD DATA
INFILE example13.dat
INTO TABLE EXAMPLE13
FIELDS TERMINATED BY ','
( EMPNO INTEGER EXTERNAL,
ENAME CHAR,
JOB CHAR,
MGR INTEGER EXTERNAL,
SAL DECIMAL EXTERNAL,
COMM DECIMAL EXTERNAL,
DEPTNO INTEGER EXTERNAL,
RES_FILE FILLER CHAR(60),
"IMAGE" BFILE(CONSTANT "ORDIMGDIR", RES_FILE)
)

If one of these examples isn't the right programming language or doesn't help you, let me know and I'll try to find you one for that language.


Is (and if how) it possible in Oracle to "insert into  values " if  has virtual columns

Use a view that excludes the virtual columns to do the manipulation. I've just tested this & it works:

create view v_tq84_virtual_test_with as ( select col_1, col_2, col_3, col_4 from tq84_virtual_test_with );

declare
  r v_tq84_virtual_test_with%rowtype;
begin

  select * into r from v_tq84_virtual_test_with where col_2 = 8;

  r.col_4 := r.col_4 - 2;

  insert into v_tq84_virtual_test_with values r;

end;
/

As far as I can tell, this is the only way to workaround your requirement to use %rowtype.


What is the effect of number of CPU cores on update operations in Postgres?

The additional CPUs can only be used if you use more sessions/connections.
If you increase the number of sessions, then a higher number of CPUs should show an increase in throughput. However, the throughput is not only limited by CPU, but also by the capacity of your I/O system. If the current workload is I/O bound (i.e. limited by the performance of the disks) then adding more sessions (and more CPUs) is not going to increase the throughput (it's probably making it worse).
If your disks are not saturated with the current workload, then adding CPUs and using more sessions is likely to improve the throughput. But this is typically not linear, i.e. doubling the number of CPUs and sessions might only give you an x1.5 increase in throughput. Typically this curve flattens. If you increase concurrency from 5 to 10 sessions, this will probably double your throughput (again: assuming you are not limited by I/O). But going from 100 to 200, is probably not going to double the throughput.


Update Table data using variables depending on other tables

It is a no-no to have redundant data.

Instead, use JOIN when doing the SELECT; this will let you dynamically get the faculty, faculty_head, etc.

Learn about JOIN.


Connections increasing by 10+ a second with no requests

10 Connections per second is rather high.  Still, about 1/4 of production servers I have looked at (WP and non-WP) show a higher value for Connections/Uptime.

Check the web server's log files -- you may find that a hacker is trying to break through your passwords or security flaws in WP.


How to ensure that only unique row combinations are present?

Triggers will not work on some scenarios. Let set1(A,B), set2(A,B,C) Now one cann't move to set2(A,B,D) by dropping set2-C and adding set2-D assignments because DB must go through prohibited set1(A,B), set2(A,B) state.
You should implement custom API, stored procedures or something with proper transactions which will check only final target state. Also those procs should place locks on updated set rows during the transaction. Because really what is updated is set.


Teradata supports proprietary syntax for time series expansion:
select begin(pd), column1, column2
from OTH_MBR
expand on period (from_dt, thru_dt) as pd
by anchor period month_begin
-- if you want to restrict the range of months
for period(ADD_MONTHS(current_date,- 36), current_date )

You can wrap it in a Derived Table/CTE for further processing:
with cte as
 (
   select begin(pd) as mon, column1, column2
   from OTH_MBR
   expand on period (from_dt, thru_dt) as pd
   by anchor period month_begin
   for period(ADD_MONTHS(current_date,- 36), current_date )
 )
select mon, count(*)
from cte
group by 1;


Performance issues with inherited tables and indices

"date"

Don't call your timestamp column "date", that's very misleading. Better yet, don't use the basic type name "date" as identifier at all, that's error-prone, leads to confusing error messages and it's a reserved word in standard SQL. Should be something like:

CREATE TABLE test (
  id serial PRIMARY KEY
, ts timestamp NOT NULL  -- also adding NOT NULL constraint
);
CREATE INDEX ON test(ts);

Caveats

Be aware of this caveat with constraint exclusion:

  Constraint exclusion only works when the query's WHERE clause contains
  constants (or externally supplied parameters). For example, a
  comparison against a non-immutable function such as CURRENT_TIMESTAMP
  cannot be optimized, since the planner cannot know which partition the
  function value might fall into at run time.

Bold epmhasis mine. You dodged this one, but with your confusing setup you might trip over it soon enough.

Also, since you have daily partitions:

  All constraints on all partitions of the master table are examined
  during constraint exclusion, so large numbers of partitions are likely
  to increase query planning time considerably. Partitioning using these
  techniques will work well with up to perhaps a hundred partitions;
  don't try to use many thousands of partitions.

Bold emphasis mine. If you are spanning more than a couple of months, try weekly or monthly partitions instead.

Mismatch in predicates

Your check condition:

CHECK ( date >= DATE '2015-08-12' AND date < DATE '2015-08-13' )

But your query has the condition:

where date > '2015-08-12' order by date desc;  -- should be: >=

This leaves a slight mismatch (probably incorrect!) and forces Postgres to recheck the condition. Not good, but also can't answer your question.

Use >=, and either make the column NOT NULL or append NULLS LAST to the statement:

WHERE ts >= '2015-08-12' ORDER BY ts DESC;

... and make the index match.

Unclean CHECK constraint

The CHECK constraints are saved with date constants instead of timestamp constants. Should be something like:

CHECK (ts >= timestamp '2015-08-11' AND ts < timestamp '2015-08-12');

Constraint exclusion

You write:

  constraint_exclusion is set to ON in my postgresql.conf. Therefore it should only be executed on test_20150812.

As you can see in the query plan, only test and test_20150812 are scanned, but not test_20150811. Ergo: constraint exclusion is working fine, despite all your deviations. That's just another wrong track.

Won many battles, but not the war

After cleaning all of this up, I see a bitmap index scan for the child table instead of your seq scan. Still slower than a query on the child table only. That's obviously due to the fact that the parent table itself can have matching rows, too, which must be sorted with the rest, so the result cannot just be read from the index.


Is it possible to create a database called C that is a view into all tables in database A + B?

You can create DatabaseC if you want to, but it adds no value. Just use 3 part object names:

SELECT
   A.col1, B.col2
FROM
   DatabaseA.dbo.TableA A
   JOIN
   DatabaseB.dbo.TableB B ON A.xx = B.xx


What grants should I give a user if I want the user to see all tables and views (view definitions) in every database in the server

GRANT VIEW DEFINITION TO [user]; will allow the user to see the definitions of structures in the database, including tables, views, stored procedures, etc.

You'll need to do that for every database on the instance.  There is no server-wide equivalent, out of the box, that limits permissions to just the database level.

If you need to limit it to just a single schema, then you'd do:

GRANT VIEW DEFINITION ON schema::[name_of_schema] TO [user];

If you only want them to be able to see the definitions of tables and views (and not stored procs etc), then you'll need to do a single grant for each object you want them to see the definition for.  You could generate the GRANT statements for just tables and views at the database level with the following query:

SELECT
      ObjectType = o.type_desc
    , ObjectName = o.name
    , GrantStatement = N'GRANT VIEW DEFINITION ON ' + QUOTENAME(o.name) + N' TO [user];'
FROM sys.objects o
WHERE o.type = N'U'
    OR o.type = N'V'
ORDER BY o.type
    , o.name;

See the Microsoft Docs for more info at https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-principal-permissions-transact-sql?view=sql-server-ver15

If you're comfortable assigning a lot more permissions to the login, at the server level, you could do:

GRANT VIEW ANY DEFINITION TO [name_of_login];

That will allow them to see the definition of any object in any database they have access to, and also will allow them to see definitions of things like endpoints, at the server level.


I had to run

CREATE DATABASE databasename;

to manually create the db, and then changed the restore command to:

devserver:/# psql -f prod.sql -U postgres -d databasename


Execution Plan is different when Constant Variable is Null

You're seeing an example of contradiction detection.  SQL Server's query optimizer is smart enough to perform a few quick checks at the outset of query processing to determine if it can take any shortcuts to return results without doing much (any?!) work.  One such short circuit is to check if the where clause contains an "impossible" limit, such as:
WHERE 1=0

or
WHERE NULL = NULL

SQL Server sees you are comparing something to NULL, which always returns false, and simply returns an empty resultset.  If the items in your WHERE clause were separated with OR instead of AND, then further checks would be performed and you might not see the constant-scan plan.
Comparing any value to NULL using = always returns false.  You may want to use WHERE @c IS NULL instead.  If you rewrite your query using that syntax, you'll see SQL Server does in fact "run" the query.  Consider this:
IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t
(
    i int NOT NULL
);

DECLARE @c int = 1;

The query plan for this query:
SELECT *
FROM #t t
WHERE t.i = 1
    AND @c = NULL;

Versus the query plan for this query:
SELECT *
FROM #t t
WHERE t.i = 1
    AND @c IS NULL;

The problems around comparing NULL values applies to JOIN conditions as well.  Consider this:
IF OBJECT_ID(N'tempdb..#t', N'U') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t
(
    i int NULL
);

IF OBJECT_ID(N'tempdb..#s', N'U') IS NOT NULL DROP TABLE #s;
CREATE TABLE #s
(
    i int NULL
);

I'll insert a row into each table with the value of i set to NULL:
INSERT INTO #t (i) VALUES (NULL);
INSERT INTO #s (i) VALUES (NULL);

Now, if we try to JOIN the two tables in a "simple" query, we get no results returned, since NULL cannot be compared to anything, not even another NULL value!
SELECT *
FROM #t t
    INNER JOIN #s s ON t.i = s.i;

Both tables are scanned by the query processor, as shown in the query plan:

In other words, be careful using NULL values.


How to preserve key when joining to a preference table in Oracle

I don't see the reason for having doubly nested subqueries when a simple LEFT JOIN can give the same result:

select c.customerid, c.name, p.prefval
       -- , c.rowid                        -- this errs
from customer c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;

Now, when we add c.rowid in the SELECT list, Oracle parser/optimizer is not able to identify that every row of customers is going to be joined with - at maximum - 1 row from pref. So it throws an error.

We can convert the LEFT JOIN to a subquery though - and no error is thrown:

-- query 2 --
select c.customerid, c.name,
       ( select p.prefval
         from pref p
         where c.customerid = p.customerid
           and p.preftype = 1
       ) as prefval,
       c.rowid                               -- but works here
from customer c ;

or putting the customers in a derived table and then left join to pref:

-- query 3 --
select c.customerid, c.name, p.prefval, c.rowid
from
    (select c.*, c.rowid from customer c) c
    left join pref p
    on  c.customerid = p.customerid
    and p.preftype = 1 ;

Tested at rextester.com


Use Backtick Sign  around columns names. As you have a column desc which is a keyword in MySQL, So you need to put Backtick around column the name

insert into hall_booking
(`name`,`address`,`event_type`,`hallNo`,`arrival_time`,
`arrival_date`,`dep_time`,`dep_date`,`identity`,`emailid`,
`contact`,`total_members`,`desc`,`catering`,`service`,
`decoration`,`other`)
values
('$nm','$add','$typ','$roomno','$arrv',
'$arrivaldate','$departure','$dDate','$idt','$emailid',
'$cont','$desc','200','200','200',
'200')

And one more thing in your insert you are missing value for one column, as you have 17 columns to insert but you are inserting only 16 values.

I hope you are missing value for column total_members,

Your insert should be like below

insert into hall_booking
(`name`,`address`,`event_type`,`hallNo`,`arrival_time`,
`arrival_date`,`dep_time`,`dep_date`,`identity`,`emailid`,
`contact`,`total_members`,`desc`,`catering`,`service`,
`decoration`,`other`)
values
('$nm','$add','$typ','$roomno','$arrv',
'$arrivaldate','$departure','$dDate','$idt','$emailid',
'$cont','$total_members','$desc','200','200','200', /** Added total_members **/
'200')


how to create backup for stored procedure,trigger and functions of a database in sql server 2008?

I guess you have framed your question to somewhat misleading.

You want to script out all stored procedures, triggers and functions from one server and create them on the new server.

Option 1 : USING SSMS

OPTION 2 : USING TSQL - sys.sql_modules with sys.objects


Is it bad practice to automate the updating of statistics and re-making of query plans on a relatively small SQL Azure database?

If auto update statistics is not enough for your DB, try a couple of things:

Manually update statistics - recommend Ola Hallengren's scripts that can be configured to maintain indexes and/ or statistics as required. Test different parameters (all/ indexes only/ sample rate etc) and test running daily/ weekly/ at start of proc and the like, and see what works best for you with least overhead.
Scripts are here: https://ola.hallengren.com/
Brent Ozar article about updating stats only with those scripts:https://www.brentozar.com/archive/2016/04/updating-statistics-ola-hallengrens-scripts/

Enable Trace Flag 2371 (if SQL Server version older than 2016)
For large tables, this reduces the threshold for automatically updating statistics dynamically. This algorithm is used as standard since SQL Server 2016.Good further reading:
https://support.microsoft.com/en-gb/help/2754171/controlling-autostat-auto-update-statistics-behavior-in-sql-server
https://littlekendra.com/2016/04/18/updating-statistics-in-sql-server-maintenance-answers/#trace-flag-2371


Microsoft recommends 2x data/index size as a starting point for memory allocation, but that's a generalization.

Is any of the data and/or indexes in the source table compressed?

How much memory on the server?

How much memory allocated to SQL Server?

When you created the resource group, did you bind the database to it, and then offline/online the database to make the binding effective? What percent did you allocate to the resource group?

Please post the result of:
SELECT committed_target_kb
FROM sys.dm_os_sys_info

And:
SELECT @@version

Agree with @sp_blitzErik that an atomic insert of 100 million rows to a memory-optimized table is not the best way to migrate data, but not necessarily because it will require less memory - batching will perform much better.


Formally, since the function USER() is not deterministic according to that term's definition:

  A routine is considered “deterministic” if it always produces the same result for the same input parameters, and “not deterministic” otherwise.

your function that simply returns its result is also not deterministic.

However, the manual further states that

  Assessment of the nature of a routine is based on the “honesty” of the creator

What it means is that you define the scope where it is deterministic (or not). Considering that this property is used for query optimization, and that, as you say, within a single session USER() will always return the same result, you can treat it, and the functions that call it, deterministic within the scope of a single query.

This changes, however, if the query cache is enabled, as the cache is shared among sessions and it is possible that sessions other than yours might get results from the cache that would be invalid for them.


Change the recovery model to SIMPLE and be done with it since this is a Development server and you mentioned you don't care about the history.

For more information on recovery models and transaction logs, see this excellent post from Gail Shaw.


How to make unique check without creating heavy index in postgres

There's no way to avoid creating a unique index if you want to efficiently ensure uniqueness for the data. The unique index is necessary for PostgreSQL to enforce uniqueness in the face of concurrent inserts, updates, and deletes.

A unique index is what backs a unique constraint; when you create a unique constraint, a unique index is created for you automatically.

The only other way to enforce uniqueness is to LOCK TABLE mytable IN EXCLUSIVE MODE before any change to the table that might affect the column you wish to make unique. That way you don't have to create a unique index, but in exchange you can only have one transaction changing the table at a time. You could do this from a trigger only when it detects a change in the unique column, but then you'd suffer from lock upgrades that would lead to frequent deadlocks.

Really, a unique index is the way to go here.

You can mitigate the impact of the required lock by using CREATE UNIQUE INDEX ... CONCURRENTLY then using the version of ALTER TABLE ... ADD CONSTRAINT ... that specifies an already-created index to use for the UNIQUE constraint.


Sql Server Agent notification email  with custom message

The way I've done this in the past is added a final step to the job, then I can use Database Mail to customize everything about the message (subject, body, recipients, from, etc) based on the server, the status/outcome of the job, and even the duration.

It is cumbersome if you have a lot of jobs, but I did this for a variety of other reasons as well (for example, other actions to perform in addition to sending an e-mail).

You may also consider a management tool like SQL Sentry which provides lots of flexibility over how to handle successful/failed jobs, and has comprehensive options for subsequent actions.


How to change the SSAS Multidimensional Cube partition location on disk?

I've found the solution. To change the location of the particular partition in SSAS Multidimensional Cube (MeasureGroup) you have to Open SSDT->Open the Cube->Go to Partitions tab->Click on particular partition of the Measure Group->in the Properties window scroll down to the Configurable\StorageLocation-> click "..." button->in the Partition Type Selection window select "Specified folder"->click "..." and choose the new path. After this action partition needs to be reprocessed.


Can I use row-level-security for application users?

Row security policies are general SQL expressions, so you are not required to use database users. However, the policy has to know what the current user is, and this is easier with current_user than with some other setting. (It would be possible to use current_setting('my_app.app_user'), but that requires you to SET this value in every connection, and is not protected against changes made by injected SQL code.)


How do I find out how far along my PostgreSQL query is?

As Daniel Vérité mentioned there doesn't seem to be a generic solution. When loading data into a table from a file the following technique can be used to get the progress of the load.

COPY command console progress bar

Create an empty table.

CREATE TABLE mytest (n int);

Create a data file with 10 million lines for loading into the table.

$ seq 10000000 > /tmp/data.txt

Load data from file into the table and display a progress bar.

$ pv /tmp/data.txt | psql -c "COPY mytest FROM STDIN;"

Demo

How this works

By using the copy commands STDIN option we can feed in the data for the copy operation from another process. The pv command will output a file and track it's progress displaying a progress bar, ETA, total time elapsed and the rate of data transfer.

COPY command graphical progress bar

Using the same general technique we could display a progress bar in a graphical application or a web-based application. Using python for example the psycopg2 module lets you call the copy command with a file object of your choosing. You could then track how much of your file object has been read and display a progress bar.


Include stored procedure text output in the maintenance plan log

Does it have to be part of the log? You could just create a table and modify the stored procedure to store its output there.


MySQL my.cnf: innodb_log_file_size is missing

Login to mysql and run this

SHOW GLOBAL VARIABLES LIKE 'innodb_log_file_size';

or this

SELECT variable_value FROM information_schema.global_variables
WHERE variable_name = 'innodb_log_file_size';

This will give you the size mysqld has. The default for MySQL 5.5 is 5M.

Whatever number that turns up should be placed in my.cnf. If it is not set, then place this

[mysqld]
innodb_log_file_size = (Whatever the filesize of ib_logfile0 is)

Try out xtrabackup after adding this to my.cnf.


Cannot start SQL SERVER SERVICE in Windows Server 2000

You don't mention the version of SQL Server you are running, however, are you up-to-date with updates?  There have been fixes for Access Violations in SQL Server that you may not have at this time.

Here is an MSDN post that has some relevance (though it is specific to SQL 2008):
sql server 2008 is terminating because of fatal exception c0000005

Access Violations occur because the application has attempted to access memory areas that it doesn't own.  That is a problem with code.  And quite often there are service packs or updates that address these issues.

Without knowing exactly which version of SQL Server you are running a more specific fix cannot be suggested.


Is the foreign key automatically indexed in MySQL?

Foreign keys are not automatically indexed in MySQL*.  If you should index, the answer is always: "It depends."
If you are going to primarily search based on that column, it's more than likely it should form the first part of that table's primary key and there would be no need for an additional index.
If the values of that column are not particularly selective, adding an index won't improve performance as a table scan will still be required.
So as always, test and validate.
*This depends on where you are expecting the foreign key to be created, on the table with the FK constraint or the table referenced by that constraint.  Most RDBMs follow the relational model, you can only create a FK reference to a primary or candidate key in another table (in practical terms an index would already exist).  MySQL does not have this restriction and will automatically create an index on the referenced column (or use an existing index if the column(s) referenced comprise(s) the start of that index).


What is a good solution for reliably transferring data between SQL Server and PostgreSQL?

Pentaho have an open-source tool called Kettle that we use for ETL development with Postgres. It is quite good and very comprehensive.

http://community.pentaho.com/projects/data-integration/


Which isolation levels can cause a serialization failure?

It's impossible to give a brief answer to this.
I suggest that you start reading here:

Think twice before dropping ACID and throw your CAP away
follow the links, particularly to jepsen.io

What developers don't know about transactions will hurt them!

Martin Kleppmann's take on isolation levels (quoted in the 2nd link - and his explanation)

Recommended further reading: Kleppmann's book (which I've purchased) is highly regarded! Of 1525 reviews, 94% are 4 (8%) or 5 (86%) star rated - that's the best I've ever seen!

If you can read and grasp all of that material (and the links within - the book is the cherry on the cake but not strictly required just to answer this question), you'll be well on the way to understanding transactions and isolation levels!
Edit:
In response to the OP's comments - by serialization failure, what I believe the author of the wiki (which is here - please quote references in future if possible) means is that in the first case, there will be a failure of T1 due to the isolation level being set to SERIALIZABLE - whereas, in the second case, a level of READ COMMITTED will mean that the T1 will not fail.
The important think to realise about SERIALIZABLE transactions is that (from here):

a transaction schedule is serializable if its outcome (e.g., the
resulting database state) is equal to the outcome of its transactions
executed serially, i.e. without overlapping in time.

So, T1 will fail because its entire view of the data can't be consistent over the entire time (i.e. for all of the queries) of the transaction due to the modification by T2.
I have found (and this confused me for a while until I teased it through) is that there is a BIG DIFFERENCE between a query AND a transaction - a query may or may not be a proper subset of a transaction. On most modern RDBMS's, if you don't explicitly specify a BEGIN TRANSACTION or an isolation level, the default is that:

a) a transaction is started for you automatically beneath the hood and,

b) that transaction is read-committed - which normally doesn't matter much since it's one query.

The distinction really becomes important when you start running multi-query transactions - that's when the wheat is sorted from the chaff and the developer has to understand what's going on in the server re. transaction isolations levels
There is, as with all things in IT and databases in particular there are balances, trade-offs and compromises. Why, you might ask, is SERIALIZABLE not the default and/or not always specified? The answer is simple: Performance - there's your trade-off!
I would urge you now to read the references I've provided - do some experiments with two CLI windows on your server of choice (or multiple servers if you really want to get into it!) running some of the code referenced!


Size of individual transactions in log file

Please refer following link to get answer to your question :

To get the size of each open transactions refer following link

https://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

If you want to know the transaction log of any historical date then you can take the backup of database or just a transaction log backup and use third party tools to read those logs. It will also allows you to read the the logs taken by date.(ApexSQL Log tool, SQL Log Rescue)

Hope this helps.


grant execute on utl_file to user in Oracle 11g XE

The grant worked just fine. You can't DESC a package or procedure, which is why you're getting an error.

Documentation for UTL_FILE is here. It provides sample PL/SQL that shows you how to use the package.

A short code snippet that sums up its functionality is here.


How do I fix SQL Server assembly optimization errors and memory exhaustion?

This problem is described on Connect (see msiexec.exe processes keep running after installation of SQL Server 2012 SP1) and a hotfix is available at KB2793634. There is commentary on work-arounds on Connect and this blog if you are unable to install the hotfix immediately.

I chose to setup 2 scripts.  1st script runs daily at 1am, and set to terminate after 1 hour of execution

c:\windows\Microsoft.NET\Framework\v4.0.30319\ngen.exe executequeueditems
c:\windows\Microsoft.NET\Framework\v2.0.50727\ngen.exe executequeueditems
c:\windows\Microsoft.NET\Framework64\v2.0.50727\ngen.exe executequeueditems
c:\windows\Microsoft.NET\Framework64\v4.0.30319\ngen.exe executequeueditems

2nd script runs daily at 3am (and whenever the computer restarts):

c:\windows\Microsoft.NET\Framework\v4.0.30319\ngen.exe queue pause
c:\windows\Microsoft.NET\Framework64\v4.0.30319\ngen.exe queue pause

This way I still get compile optimization jobs (only run during off-peak hours), but the mscorsvw/msiexec process is tamed.


Are cut and paste of records from a table to another a correct pratice?

As a general rule I would recommend updating/creating relationships instead of copying data around to different tables.  This is more in line with the relational model, and prevents tables-as-business logic which is what you seem to be doing.

Instead of copying data from one table to another I would think about updating a status flag.  This could be a simple boolean value or a FK relationship to a status table when there are more than two possible states.

Depending on your business logic a better solution would probably be to associate a user with their selected tasks.  This could be via a join table or with a nullable FK on the BookedTasks table.

To give a better or more complete answer I would need you to update your question to include your proposed table structures and some sample data/workflows.


How do I retrieve the loop record in the connect by clause?

You could use what your query returns to get the extra row that have CONNECT_BY_ISCYCLE=1

WITH a AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE
FROM  MYTABLE
   CONNECT BY NOCYCLE
    PRIOR MANAGER_EMP_ID = EMP_ID
    START WITH EMP_ID = '12345'
)
SELECT EMP_ID, FIRST_NAME
FROM a

UNION ALL

SELECT m.EMP_ID, m.FIRST_NAME
FROM MYTABLE m
  JOIN a
    ON a.MANAGER_EMP_ID = m.EMP_ID
WHERE a.CONNECT_BY_ISCYCLE = 1 ;

Or use a recursive CTE:

WITH a (EMP_ID, FIRST_NAME, MANAGER_EMP_ID) AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID
FROM  MYTABLE
WHERE EMP_ID = '12345'

UNION ALL

SELECT  m.EMP_ID, m.FIRST_NAME, m.MANAGER_EMP_ID
FROM  MYTABLE m
  JOIN a
    ON  a.MANAGER_EMP_ID = m.EMP_ID
    AND a.MANAGER_EMP_ID <> a.EMP_ID   -- to avoid the cycles
)
SELECT EMP_ID, FIRST_NAME
FROM a ;

Another way to avoid cycles in a recursive CTE is to use the CYCLE clause (available only in Oracle):

WITH a (EMP_ID, FIRST_NAME, MANAGER_EMP_ID) AS
(
SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID
FROM  MYTABLE
WHERE EMP_ID = 1

UNION ALL

SELECT  m.EMP_ID, m.FIRST_NAME, m.MANAGER_EMP_ID
FROM  MYTABLE m
  JOIN a
    ON  a.MANAGER_EMP_ID = m.EMP_ID
)
CYCLE MANAGER_EMP_ID
  SET IS_CYCLE TO 'Y' DEFAULT 'N'
SELECT EMP_ID, FIRST_NAME, IS_CYCLE
FROM a ;

Test at SQLfiddle

Just realized that the extra row can be retrieved with a simple change in the original query:

SELECT  EMP_ID, FIRST_NAME, MANAGER_EMP_ID, CONNECT_BY_ISCYCLE
FROM  MYTABLE
   CONNECT BY NOCYCLE
     PRIOR MANAGER_EMP_ID = EMP_ID
     AND PRIOR MANAGER_EMP_ID <> PRIOR EMP_ID
                                 -- if she's a manager
                                 -- don't try to connect her with superiors
   START WITH EMP_ID = '12345' ;


MySQL order by rand but result must exist different specific value

WITH
cte AS ( SELECT id, category, name,
                ROW_NUMBER() OVER (PARTITION BY category ORDER BY RAND()) rn
         FROM test )
SELECT id, category, name
FROM cte
ORDER BY rn > 1, RAND()
LIMIT ? ;

fiddle

For MySQL version below 8 calculate rn in subquery using user-defined variables.


how to do a count on a grouped by value in Postgres 9.4

select created_at::date,
       count(*) as total_orders,
       count(*) filter (where fulfillment_status = 'paid') as total_paid_orders
from orders
group by created_at::date
order by created_at::date;

created_at::date converts the timestamp into a date (without a time) and thus groups by day.

count(*) filter (where fulfillment_status = 'paid') only counts rows where the condition is true.


Database design for part-number generator

First, I don't see any problem with doing this in the db.  In fact we are moving this direction with LedgerSMB.  The big caution is that you want to be prepared for business rule changes.  Of course your old partnumbers will be stable.  but SKU's are stable for a long time usually unless you are Frito Lay and then it just makes trouble for everyone else (they change UPC's it seems every time a word on their packaging changes).

What we've done in LedgerSMB is to essentially store a template, and then we preprocess and increment.  That isn't quite what you are looking at doing, but it may help to see how we have looked at this in the past and where we are going.

Our templates look something like:

<?lsmb field_name ?>somechars<?lsmb another_field?>0001102

Leading zeros are preserved on increment.  Inside the db, we have a stored procedure which increments the number, keeping leading zeros and passes the rest up to the application.  I don't see how we can do full preprocessing of this sort using invoice numbers and the like but for partnumbers the goal eventually is to move all that preprocessing into the database.

Now in your case, you probably want to have the parts tied together for easier management.  I understand the arguments against this.  I would suggest putting all these fields you want to aggregate in the table representing the parts.  Then you can integrate these with a user defined function or something (that's how I'd do it in PostgreSQL) which you can use to populate a missing part number.

In this case your table structure will look very different:

product_group(id-- 0000-9999, etc)
fabric (id ....)
etc....
part (partnumber, productgroup_id, fabric_id, etc)

I don't see another sane way of doing it.  You are going to have to track each of these things you want to use separately, put them in the parts table, and then generate your partnumber based on that.


Is it possible to override a ReadCommittedSnapshot isolation level with ReadCommitted isolation level for a particular transaction?

There is no way to override the database-level setting of read committed snapshot (RCSI) for a whole transaction.

When RCSI is set, all read committed transactions will use row versions, delivering the last committed version of a row.

A session-level override option has been asked for. You can vote for it on the product feedback site at Add SET TRANSACTION ISOLATION LEVEL READ COMMITTED LOCK.

You can override the setting per-statement and per-table using the READCOMMITTEDLOCK table hint.

Alternatively, you could use snapshot isolation (SI) and turn read committed snapshot isolation (RCSI) off. Each transaction would then either SET TRANSACTION ISOLATION SNAPSHOT or SET TRANSACTION ISOLATION LEVEL READ COMMITTED.

More information in my series of articles on isolation levels.


Changing a column data type from datetime to float

With no data in the column you can just drop it and add it again.

alter table MyTable drop column MyColumn;

go

alter table MyTable add MyColumn float;

If you have data in your column and it makes sense to convert the values to a float value you can rename the column, add a new column, move the data using convert and then drop the old column.

exec sp_rename 'MyTable.MyColumn', 'Temp_MyColumn', 'COLUMN';

go

alter table MyTable add MyColumn float;

go

update MyTable
set MyColumn = convert(float, Temp_MyColumn)
where Temp_MyColumn is not null;

go

alter table MyTable drop column Temp_MyColumn;


You can fully achieve this with MERGE:

MERGE INTO big_table B
USING (SELECT key_col, col1, col2 FROM small_table) S
ON (B.key_col = S.key_col)
WHEN MATCHED THEN UPDATE SET B.col1=S.col1, B.col2=S.col2
WHEN NOT MATCHED THEN INSERT (B.col1, B.col2)
VALUES (S.col2, S.col2)
/


Well you would need a family table to begin with:

create table family
(
  family_id integer primary key,
  name text not null,
  .... other attributes of a family ...
);

And a table that links a patient to a family

create table family_member
(
  family_id integer not null references family,
  patient_id integer not null references patient
  ... maybe even a date range when this patient was part of the family ...
);

Then you can link the diagnosis to the family the same way you linked it to the patient:

create table patient_diagnosis
(
  diagnosis_id integer not null references diagnosis,
  family_id integer not null references family
);


How can I dynamically create views in Postgres 10 using variables?

You need dynamic SQL for this:
do $$
declare
    myschema varchar(10) := 'SCHEMA1';
    mywhere varchar(3) := 'ABC';
begin
    execute format('
        create or replace view %I.myview as
        select * from SHAREDSCHEMA.sometable where MYCOL = %L', myschema, mywhere);
end $$;

The format() function is highly recommended when creating dynamic SQL to properly deal with identifiers and constant values.
You can also put this into a function (or procedure in newer versions):
create function create_view(p_schema text, p_value text)
   returns void
as
$$
begin
  execute format('
      create or replace view %I.myview as
      select * from SHAREDSCHEMA.sometable where MYCOL = %L', p_schema, p_value);
end
$$
language plpgsql;

Then you can run:
select create_view('myschema', 'xyz');

If you want, you can use that to create all views in one go:
with data (schema_name, value) as
  values
     ('schema1', 'abc'),
     ('schema2', 'def'),
     ('schema3', 'ghi')
)
select create_view(schema_name, value)
from data;


How to forcibly flush the undo log/tablespace of UNDOTBS1

Undo data is used to ensure ACID of the database. It supports your transaction and cannot be removed until you committed or rolled it back. Read "What Is Undo?" in Database Administrator's Guide.

You can however commit your transaction to release some undo space, but you lose the ability to roll back your changes.

If the DML you are performing requires more undo space because there are a lot of records updated/deleted/inserted and your undo tablespace is not sized correctly, DML will fail, because Oracle cannot allocate extents to support your transaction.


Problem was solved by stopping one by one each replica and make resync it, after that was changed primary and resync previous one.


Long time, sporadic 'String or Binary data would be truncated.' Wisdom would be appreciated

I had similar error using different databases. All these errors were related to storing unicode varchar into non unicode varchar field. The problem was not displaying always, but it happened only when the text used contained multi byte characters.

So, first of all: does you database uses multi byte NVARCHAR instead of old style VARCHAR? And, second, are you really really really sure that your client cannot input text transmitted to your database server as multi byte characters?


SQL Server Database going into suspect mode after bulk insert and delete

I'm quite sure that the transaction log might be broken. Did you check the serverlogs? Another idea could be that to set the emergency mode in your database and run a dbcc checkdb and take a look at the results.

I can't find a good reason for this behaviour. Maybe a bit code would help. I don't have any of this issues, even as I'm using such operations quite often.

Maybe another idea is this way:

Call procedure
Copy tables
Run Checkpoint
Clean tables on source


Get 10th and 90th percentile by customer

It seems you are after the percentile_disc() ordered-set aggregate function.
The documentation says the following about it:

percentile_disc(fraction) WITHIN GROUP (ORDER BY sort_expression)
discrete percentile: returns the first input value whose position in the ordering equals or exceeds the specified fraction

The syntax is a bit strange for an aggregate, but using it is easy:
SELECT percentile_disc(0.9) WITHIN GROUP (ORDER BY score)
  FROM customer_score
 GROUP BY customer_id;

You define the column from which to take the percentile in the ORDER BY clause.


Is there anyway to view historical performance of SQL 2008 R2 database?

System Dynamic Management Views

You might want to dive into the Execution Related Dynamic Management Views and Functions (Transact-SQL) that reside in a SQL Server instance. These views and functions do contains some information related to performance.

The suggested views and functions are a sub-set of the System Dynamic Management Views which contain various other sub-sets of views and functions, which may or may not be what you are looking for.

Further sub-sets

Database Related Dynamic Management Views (Transact-SQL)
Index Related Dynamic Management Views and Functions (Transact-SQL)
I O Related Dynamic Management Views and Functions (Transact-SQL)
Memory-Optimized Table Dynamic Management Views (Transact-SQL)
Transaction Related Dynamic Management Views and Functions (Transact-SQL)
...

(Just to name a few)

  Note
  Most of these views  are recycled after an instance restart. You will have to dump the information to static tables in a (e.g.) Admin database if you want to retain historical data.

Built-In Reports

If you right-click on a database and move your mouse pointer into the reports, then you might find can occasional jewel of information:

Creating a Baseline for SQL Server

There is an extensive Q&A on DBA.SE with the title Creating SQL Server Performance Baseline Monitoring which show you how you could baseline a SQL Server instance. The article references other material.

And then you could start reading Erin Stellato's article 5 Reasons You Must Start Capturing Baseline Data for an interesting start into SQL Server "baselining".

Answering Your Question

  So.. I was wondering, is there a way for me to generate any report for the historical performance of a MS SQL database?

There are indeed a couple of ways with the Management Views and Functions and Standard Reports. For a deep dive you are going to either build something yourself or purchase a third-party tool.


Get SSIS to check if excel file is updated?

You could have a meta data table that stores the file name, processing date, and last modified date. Then you can have your job run daily and identify whether the file's last modified date is greater than the stored value.
If the file hasn't been modified in 32 (arbitrary number but base it on business tolerance) send a notification to you and/or the business process owner saying no new file detected.
This won't cover cases where they've opened the file, changed nothing but clicked save none-the-less (or Save As and overlaid the file). At that point, you'd get to write actual change detection. Which could be a weird kind of fun but surely there's more important priorities for the business.
I'd advise against changing the tab name. Lots of reasons for it but the easiest one to enumerate is technical/license based. Programmatically renaming an Excel worksheet is an easy technical change. But, to overcome a business process deficit, you're going to take a dependency on the Excel object library. You get to read/write to Excel via SSIS for free. Reading/Writing to Excel from a Script Task is going to use the COM interop functionality. To use that, you need to have Excel installed. Excel on a desktop, yes. Excel on a Server, no (it kills your uptime + costs you an license for the Office installation)
Yes, I am quite aware of nuget packages, the ACE library etc that are free and can provide interactivity with Excel but then that gets us into server installation, GAC requirements, 32 vs 64 bittedness discussions etc.


Sql Server (2017?) Lat/Long index and query

There is a couple of approaches that can be used here depending on the exact requirements.

First of all you will need to create a spatial index on the Landmark table

CREATE SPATIAL INDEX Landmark_GeoLocation_SDX
ON [dbo].[Landmark] ([GeoLocation])
USING GEOGRAPHY_AUTO_GRID;

Then to query for all points within a specified distance you can do a query similar to the following

DECLARE @withinDistance float = 500.0; -- This is specified in metres
DECLARE @source geography = geography::Point(40.689168, -74.044563, 4326);

SELECT *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]
WHERE @source.STBuffer(@withinDistance).STContains([GeoLocation]) = 1;

The other common query for this type of thing is to return the n closest points, otherwise known as a nearest neighbour or knn query.

DECLARE @neighbourCount int = 5; -- This is specified in metres
DECLARE @source geography = geography::Point(40.689168, -74.044563, 4326);

SELECT TOP (@neighbourCount) *, @source.STDistance([GeoLocation])
FROM [dbo].[Landmark]
WHERE @source.STDistance([GeoLocation]) IS NOT NULL
ORDER BY @source.STDistance([GeoLocation])


SQL Server 2008 R2 Restore COPY_ONLY full backup with transaction logs

A full backup in SQL Server 2008 does not break the log chain. It only resets the differential base-lsn.
You also can restore log backups after restoring from a copy only. The following script demos that:
CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13
TO DISK = 'BakTst13_Log_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP LOG BakTst13
TO DISK = 'BakTst13_Log_2' WITH INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_1' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;
GO
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE LOG BakTst13 FROM DISK='BakTst13_Log_2' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

It creates a database and a table and inserts 50 rows into that table. In between those inserts several backups are taken in this order:

Full
Log
Full
Full Copy_Only
Log

Next the database is dropped and restored like this:

1st Full
1st Log
2nd Log

The following SELECT demonstrates that the restore was successful.
This shows that neither a COP_ONLY nor a normal Full Backup break the log chain.
Then the database is dropped again and restored like this:

Copy_Only Full
2nd Log

Afterwards the SELECT demonstrates success again.
This demonstrates that you can use a COPY_ONLY full backup as the base of your Log Restore.
Differential tests
I created a DIFFERENTIAL version too:
CREATE DATABASE BakTst13;
GO
ALTER DATABASE BakTst13 SET RECOVERY FULL;
GO
USE BakTst13;
GO
CREATE TABLE dbo.tst(id INT IDENTITY(1,1));
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_1' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_1' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_2' WITH INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_2' WITH DIFFERENTIAL,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Full_C' WITH COPY_ONLY,INIT,FORMAT;
GO
INSERT INTO dbo.tst DEFAULT VALUES
GO 10
GO
BACKUP DATABASE BakTst13
TO DISK = 'BakTst13_Diff_3' WITH DIFFERENTIAL,INIT,FORMAT;
GO
USE tempdb;
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F1, D1, D2',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_1' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore FC, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_C' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH NORECOVERY;--<--Fails!
GO
DROP DATABASE BakTst13;
GO
RAISERROR('------> Starting restore F2, D2, D3',0,1)WITH NOWAIT;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Full_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_2' WITH NORECOVERY;
RESTORE DATABASE BakTst13 FROM DISK='BakTst13_Diff_3' WITH RECOVERY;
GO
SELECT * FROM BakTst13.dbo.tst;
GO
DROP DATABASE BakTst13;

This takes backups in this order:

1st Full
1st Differential
2nd Full
2nd Differential
Copy_Only Diff
3rd Differential

It then tries this restore route:

1st Full
1st Differential
2nd Differential

Step 3 fails with this error:
Msg 3136, Level 16, State 1, Line 4
This differential backup cannot be restored because the database has not been restored to the correct earlier state.

This shows that a normal full backup breaks the differential chain.
Next the database is dropped and this restore flow is attempted:

Copy_Only Full
3rd Differential

Step 2 fails with the same error as step 3 above. This shows that a copy only backup cannot be used as the base for a differential restore.
Then the database is dropped again and the following restore is executed:

2nd Full
2nd Differential
3rd Differential

The following select proves that this restore succeeded. This shows that a COPY_ONLY Full backup does not interrupt the differential chain.


Is possible to start a postgresql streaming replication using pg_dump instead of copying the database?

You cannot start streaming replication without a physical copy of the data directory; usually created with the pg_basebackup command.

The reason for this has to do with what streaming replication and WAL shipping replication do. In both cases you are applying the Write Ahead Log to the data directory using postgresql's recovery mechanism. So in order to not break database consistency you must be applying the same change set to the same starting point and you are restricted to replicating the entire database cluster. This is also why you cannot interrupt streaming or drop WAL segments, since once you have broken continuity the database server cannot guarantee that it is in a consistent state.

In postgreSQL 10 or later you could use logical replication to replicate only one database in a cluster or even a single table. And in that case you can start with a "logical copy" of the database such as are produced by pg_dump.


Can I get some rationale from the Optimzer why it chose a certain explain plan?

Yes, you can.

This is called tracing the Oracle optimizer. Doing so creates a trace file in which the optimizer dumps all reasoning done when composing a plan.

An example of generating a trace for a specific SQL can be found here how to trace optimizer for specific SQL system wide - 10053 trace event

You might need a little time to read it, given the amount of data found in the trace, it is amazing that the Oracle even finds time to do this and return in a decent time.


Maintenance plan backup and reload the same database

Carlo,

  do you have other ideas?

You jus took a backup, so when you write the execute SQL Statement part of your job reference the MSDB backup history information tables to pull out the latest full and any other diffs/logs/etc that you would like to apply. The data can be found in msdb under the dbo schema and will give you the file names, etc, so there will be no need to pass anything and you can make it work should you want to do it from the last full or from the last full with the rest of the backup chain.

http://msdn.microsoft.com/en-us/library/ms188062.aspx


How to select row and column name where field is null?

there is no need for using slow cursors ...

Insert into table_error
select 'error - ' + Cast(ID as Varchar(20)), @uploads, GetDate()
from Header
where  [name] is Null or [zip] is Null or [age] is Null or [city] is Null


Does this regex reliably remove square brackets from T-SQL?

When I first answered this question the rules for what characters would determine valid or invalid were a bit hazy (the documentation isn't entirely clear on it, and is in some ways misleading). However, since that time I have done some research into the specific rules for valid regular identifiers (i.e. those that do not need to be delimited by either "name" or [name]) related to this DBA.SE question:

How to create Unicode parameter and variable names

and have discovered the exact rules. The research into getting those exact rules lead to finding a list of Unicode Categories (not blocks) that can be used in combination to determine both the "first" / "start" character and any possible "subsequent" / "continue" characters. That research is towards the end of this post (Steps 7 and 8):

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 2

Reworking your pattern I came up with the following:

(?!\[(?i:PRIMARY|GROUP)\])\[([\p{L}\p{Nl}\u0023\u005F\uFF3F][\p{L}\p{Nl}\p{Mn}\p{Mc}\p{Nd}\p{Pc}\p{Cf}\u0023\u0024\u0040]*)\](?!\])

Some notes:

You don't need to "or" the separate items together with a | in a character class (i.e. the [stuff_here]) as it is already a list. In fact, including the pipe symbol actually means you were adding it to the list of allowed characters ;-).
You need to separate out the exclusion list (i.e. PRIMARY|GROUP...) such that they can be wrapped in [ and ]. Otherwise you allow for something like [PRIMARYa] to get skipped and keep its delimiters.
I added (?!\]) to the end to account for embedded escaped end-delimiters: ]].
This pattern is 99.5% effective. The only thing it does not catch is if you have both:

an embedded leading delimiter: [, AND
no embedded escaped end-delimiter: ]

Meaning, [Te[st] will erroneously un-delimit it to be [Test (oops), but both [Te[s]]t] and [Tes]]t] will remain delimited as expected. And really, how often are there embedded delimiters? And even then, how often is there just the beginning delimiter and not the ending delimiter? Very rare, I would think.

Of course, I said it was 99.5% effective but it only catches 2 reserved words. Easy enough to fix since we can get the list of reserved words. Adding in all 185 reserved words (well, 184 as I left out "WITHIN GROUP" since it has a space in it and is thus naturally excluded) gives us the following:

( I made it a comment instead of a code block so that it would be readable since it is a single line [that can be copied and pasted into the "Find" box] )

  (?!\[(?i:ADD|ALL|ALTER|AND|ANY|AS|ASC|AUTHORIZATION|BACKUP|BEGIN|BETWEEN|BREAK|BROWSE|BULK|BY|CASCADE|CASE|CHECK|CHECKPOINT|CLOSE|CLUSTERED|COALESCE|COLLATE|COLUMN|COMMIT|COMPUTE|CONSTRAINT|CONTAINS|CONTAINSTABLE|CONTINUE|CONVERT|CREATE|CROSS|CURRENT|CURRENT_DATE|CURRENT_TIME|CURRENT_TIMESTAMP|CURRENT_USER|CURSOR|DATABASE|DBCC|DEALLOCATE|DECLARE|DEFAULT|DELETE|DENY|DESC|DISK|DISTINCT|DISTRIBUTED|DOUBLE|DROP|DUMP|ELSE|END|ERRLVL|ESCAPE|EXCEPT|EXEC|EXECUTE|EXISTS|EXIT|EXTERNAL|FETCH|FILE|FILLFACTOR|FOR|FOREIGN|FREETEXT|FREETEXTTABLE|FROM|FULL|FUNCTION|GOTO|GRANT|GROUP|HAVING|HOLDLOCK|IDENTITY|IDENTITY_INSERT|IDENTITYCOL|IF|IN|INDEX|INNER|INSERT|INTERSECT|INTO|IS|JOIN|KEY|KILL|LEFT|LIKE|LINENO|LOAD|MERGE|NATIONAL|NOCHECK|NONCLUSTERED|NOT|NULL|NULLIF|OF|OFF|OFFSETS|ON|OPEN|OPENDATASOURCE|OPENQUERY|OPENROWSET|OPENXML|OPTION|OR|ORDER|OUTER|OVER|PERCENT|PIVOT|PLAN|PRECISION|PRIMARY|PRINT|PROC|PROCEDURE|PUBLIC|RAISERROR|READ|READTEXT|RECONFIGURE|REFERENCES|REPLICATION|RESTORE|RESTRICT|RETURN|REVERT|REVOKE|RIGHT|ROLLBACK|ROWCOUNT|ROWGUIDCOL|RULE|SAVE|SCHEMA|SECURITYAUDIT|SELECT|SEMANTICKEYPHRASETABLE|SEMANTICSIMILARITYDETAILSTABLE|SEMANTICSIMILARITYTABLE|SESSION_USER|SET|SETUSER|SHUTDOWN|SOME|STATISTICS|SYSTEM_USER|TABLE|TABLESAMPLE|TEXTSIZE|THEN|TO|TOP|TRAN|TRANSACTION|TRIGGER|TRUNCATE|TRY_CONVERT|TSEQUAL|UNION|UNIQUE|UNPIVOT|UPDATE|UPDATETEXT|USE|USER|VALUES|VARYING|VIEW|WAITFOR|WHEN|WHERE|WHILE|WITH|WRITETEXT)\])\[([\p{L}\p{Nl}\u0023\u005F\uFF3F][\p{L}\p{Nl}\p{Mn}\p{Mc}\p{Nd}\p{Pc}\p{Cf}\u0023\u0024\u0040]*)\](?!\])

Also, with the new pattern you will need to change the replacement to be $1 instead of $2.

I tested with the following:

SELECT [f], [p$o],   -- match these
       [prImary], [12y], [a b], [g%g], [j^6], [f¾], [u²], [h⁊], [s|w]
FROM   dbo.[$a],
       dbo.[#a], dbo.[@d], dbo.[_a] -- match these
WHERE  f.[&col1] = N'it will also [match] <- that :-(';
/* and [this] */

--[PRIMARY]
--[PRIMARy]
--[PRIMARYg]
--[1PRIMARYg]
--[PRIMARY g]
--[GROUP]
--[GROUPBY]
--[@Test]
--[Te]]st]
--[Te]]st]]]
--SELECT 1 AS [Te]]st]]a[b];        --OOPS
--[Te]]s[t]]ab]
--[Te]]s[t]]ab]]
--SELECT 2 AS [Te]]st]]], 2.1 AS [p];
--SELECT 3 AS [Tes[tab];            --OOPS
--[Te[st]                           --OOPS
--[Te[s]]t]
--[Tes]]t]
--[T[e[s]]t]
--[dbo].[TableName]
--[dbo].[1TableName]
--[dbo.1TableName]
--[[t]                              --OOPS
--SELECT 4 AS []]t];
--[[t]]]
-- SELECT 1 AS [d], '---' AS [1f], '$' AS [g]
-- SELECT 1 AS d, '---' AS [1f], '$' AS g
--[OPTION]
--[OPTIONt]

That all being said, I will reiterate what I originally said in a comment on the question:

Is making the script "look nicer" worth any amount of risk of potential errors? I would say "no". I used to remove square brackets for pretty much the same reason, but have gotten to the place where I now always add them because "guaranteed working" beats aesthetics. For me. I realize not for everyone. Of course, if you are more comfortable with .NET RegEx, then SQL# (which I wrote) has RegEx_Replace for free .

Also, keep in mind that this is a global search and replace, so it will match strings of this pattern even if they are in comments or string literals. If you have such strings then this might not be desirable and you will either need to find a way to exclude those (especially string literals), or give up this fight ;).


purchasing item by client - how to ensure that user has enough funds?

You've got to be really careful in cases like these. The default transaction isolation level PostgreSQL uses is READ COMMITTED, and in that mode you can easily get nasty cases where two transactions are performing checks like this:

-- You will get READ COMMITTED by default.
BEGIN;

-- Make sure user has enough coins.
-- DANGER Nasty race condition in READ COMMITTED!
SELECT coins FROM users WHERE id = 123;

-- Just to make the race condition visible...
SELECT pg_sleep(10);

-- We think we have enough coins, go ahead and perform the UPDATE
UPDATE users SET coins = ... WHERE id = 123;

-- And since we think we've deducted the coins, record the purchase
INSERT INTO purchases ... ;

COMMIT;

If you want to see the race condition in action, run that same SQL through two psql sessions at the same time. You'll see that both sessions will think that the user has enough coins to purchase an item, and will both record a purchase. (Note, the exact same problem would exist with a CHECK constraint on the users table with READ COMMITTED transactions.)

How to fix this race condition? Read up on transaction isolation levels, you probably want to be using SERIALIZABLE for important accounting code like checking account balances and recording purchases. Another option is to use  SELECT ... FOR UPDATE to obtain a row-level lock on, say, the users table while you're checking the account balance, which will ensure that two transactions can't be performing that same delicate check at the same time.


MySQL incremental backups per database (not global)

You can read binlogs with the mysqlbinlog tool, and produce a stream of logical changes, like a mysqldump backup. The mysqlbinlog has an option --database=dbname so you can extract only changes pertaining to the named database.
Read more about using this option here: http://dev.mysql.com/doc/refman/5.6/en/mysqlbinlog.html#option_mysqlbinlog_database
Two caveats:

This assumes that you have a continuous set of binary logs back either to the last full backup or to the last incremental dump.

It's not really a filter for changes to dbname. It's a filter for all binlog events applied to any database while dbname was selected as the default database. So if you have an event where someone updated dbname2.tablename while dbname was the default, then the dump will include that change to dbname2.
This makes this method unsafe to use this as a per-database incremental backup method, unless you have 100% certainty that no such transactions occurred.

P.S.: Your comments about LSN are a bit incorrect. Mysqldump knows nothing about the LSN, which is a feature of the InnoDB redo log. Mysqldump does examine the binary log position, and can output that position if you use the option --master-data.


To grant permissions for the foreign server:

GRANT USAGE ON FOREIGN SERVER hr_db TO accounting_user;

More details available in the example on official page https://www.postgresql.org/docs/9.6/static/contrib-dblink-connect.html


Disadvantages to using ON DELETE CASCADE on every foreign key?

Are there any disadvantages, gotchas, or things to bear in mind when adding ON UPDATE CASCADE ON DELETE CASCADE to every single foreign key in the database?

One reason not to : Oops!!
If you accidentally delete the wrong record, then you wipe out everything that references on that record.  If that record happens to be the one at the root of your entire company, then everything goes "Poof!", everybody starts creaming at you and you're into a major, high-profile, high-pressure Recovery exercise.
Personally, I advocate never cascading deletes.
You want to get rid of data?  You need to know what you're doing! (Or some, centrally managed, piece of code needs to know how to do so on your behalf).


When developing Dynamic SQL, I suggest you start your efforts by making heavy use of the PRINT command rather than EXEC as things can (and often do) go wrong with Dynamic SQL.  Printing out your statement instead of actually running it will help you avoid some issues if you end up having syntactically correct code that is logically wrong.

In this specific case, I'm adding a new parameter, @DSQL VARCHAR(MAX), setting that variable with the command, and printing it out, as follows:

DECLARE @dbname VARCHAR(50), @DSQL VARCHAR(MAX);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));

SET @DSQL = 'CREATE DATABASE ' + @dbname  + ' ON  PRIMARY
( NAME =' + '' + @dbname +'' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + '''' + '''.mdf'''  + ', SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )
 LOG ON
( NAME =' + '' + @dbname + '_log' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + ''''+'''.ldf''' + ', SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )'

PRINT @DSQL

Which outputs the following:

CREATE DATABASE service2019 ON  PRIMARY
( NAME =service2019, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''service2019''.mdf', SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )
 LOG ON
( NAME =service2019_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''service2019''.ldf', SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )

As you can see, you have too many quotes around service2019 in your mdf and ldf filepaths.  Upon further investigation, it also looks like you have more string concatenations than necessary.  What I suggest you do is limit concatenations (i.e. +) to only the times where you transition between variables and static strings.  I'm also a fan of formatting my DSQL, so I'm also including a Carriage Return/Line Feed (i.e. \r\n) and Tab (i.e. \t) variables so your output looks a little cleaner.  Removing the unnecessary concatenations, including the formatting, and cleaning up the quotes gets me the following:

DECLARE @dbname VARCHAR(50), @DSQL VARCHAR(MAX), @CRLF CHAR(2), @TAB CHAR(1);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
SET @CRLF = CHAR(13) + CHAR(10)
SET @TAB = CHAR(9)

SET @DSQL = 'CREATE DATABASE ' + @dbname  + ' ON  PRIMARY
( NAME = ' + @dbname + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\' + @dbname + '.mdf'' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )'
+ @CRLF + @TAB + 'LOG ON'
+ @CRLF + '( NAME = ' + @dbname + '_log' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\' + @dbname + '.ldf'' , SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )'

PRINT @DSQL
--EXEC(@DSQL)   /* <--- Uncomment out if you want to run this */

For more formatting suggestions, discussions of pitfalls that come with DSQL, and pretty much anything else, I'll point you to (what I consider the defacto guide on DSQL) The Curse and Blessings of Dynamic SQL by Erland Sommarskog.


Is there a way to restrict automatic statistics updates to a certain timeframe?

I would disable these auto statistics update and schedule a sql job to do on-demand stats update based on my defined rules, such as when the data change % is more than a threshold or when index fragmentation is more than a threshold.

Actually for big tables (100+ million rows), I always do this way because auto statistics update usually use a very small sampling rate for the big tables, and it often can cause more harm than good.


The message usually means that you're trying to restore a backup of another - unrelated - database over your existing database. Double check that the backup you're restoring is really of the database you think it is.

Edit: If I remember correctly, you can also get this message if you're trying to restore a differential backup without first restoring the correct full backup.


ops, how stupid I am...

i have used 1 time pgadmin and logged to my  external postgress server
it made in my home directory .pgpass file

if i delete it - pg_dump doesn't work without password

my error!


Microsoft exams (and preparation material) tends to be stupid in some cases. It works best with excluding answers. You have to keep records? So truncate is not an option for you (and creating a new table is typically not the right way to solve it).

Delete does free up the space. Of course not the harddisk space but (again typically) the space inside the clustered index. The background that the ghost cleanup process has to really tidy up is too complex for 98-364. This exam is about database fundamentals.


Cannot start MySQL service on linux RHEL when pointing datadir to mounted ecryptfs folder

Make sure the MySQL server owner is MySQL:MySQL using chown -R mysql:mysql /mysql and the permission level is 755 for everything except my.cnf 644. chmod 644 my.cnf then execute the following commands after changing the information inside my.cnf to the correct one.

cd /mysql/bin
./mysqld --defaults-file=/mysql/my.cnf --user=mysql &

Edit to answer the comment below
To shutdown MySQL safely without killing the SID (or PID) you can use one of the following commands:

/usr/local/mysql/bin/mysqladmin -u root -p shutdown

or

sudo mysqld stop

note: in-case you can more than SID running you need to specify the port for it.


Cannot recover Database from ibdata file

If you have access to MySQL other than thru PHPMyAdmin, you could try recreating the table. Practice this at your own risk thou.

create_tables.sql


A rough estimate of your table size is:

select sum(BYTES) / 1024 / 1024 as SIZE_MB
from   USER_SEGMENTS
where  SEGMENT_NAME = '<tablename>';

However, see the question How do I calculate tables size in Oracle for better detail as the real size of a copied table on disk can differ from the original.
You can then check how much space you have in the tablespace using:

select TS.TABLESPACE_NAME
      ,nvl(nvl(DF.BYTES, 0) / 1024 / 1024, 0) + nvl(nvl(FS.BYTES, 0) / 1024 / 1024, 0) as AVAIL_SPC_MB
from   DBA_TABLESPACES TS
       left join (select   TABLESPACE_NAME, (sum(MAXBYTES) - sum(BYTES)) BYTES
                  from     DBA_DATA_FILES
                  group by TABLESPACE_NAME) DF
         on TS.TABLESPACE_NAME = DF.TABLESPACE_NAME
       left join (select   TABLESPACE_NAME, sum(BYTES) BYTES
                  from     DBA_FREE_SPACE
                  group by TABLESPACE_NAME) FS
         on TS.TABLESPACE_NAME = FS.TABLESPACE_NAME
       left join DBA_TABLES TAB
         on TS.TABLESPACE_NAME = TAB.TABLESPACE_NAME
where  TAB.TABLE_NAME = '<tablename>';

If you don't have enough space then check your other tablespaces as you can temporarily create the table in there if you have permissions.


How to track failed logon attempts using Unified Auditing

You have enabled ORA_LOGON_FAILURES, but have you enabled Unified Auditing properly?

With the default, mixed-mode auditing, setting audit_trail to none, prevents logon failures to be audited with ORA_LOGON_FAILURES enabled.

This is how it works with everything set to default (audit_trail set to DB by DBCA):

SQL> audit policy ora_logon_failures;

Audit succeeded.

SQL> select count(*) from unified_audit_trail
     where unified_audit_policies = 'ORA_LOGON_FAILURES' and return_code = 1017;

  COUNT(*)
----------
         0

SQL> connect 1/1
ERROR:
ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.
SQL> connect / as sysdba
Connected.
SQL> select count(*) from unified_audit_trail
     where unified_audit_policies = 'ORA_LOGON_FAILURES' and return_code = 1017;

  COUNT(*)
----------
         1

Also, Unified Audit entires can be buffered in the SGA and be written to disk later and not immediately, in that case, flush manually before querying:

EXEC DBMS_AUDIT_MGMT.flush_unified_audit_trail;

Still, if you configured everything properly, you may have hit:

BUG 19383839 - UNIFIED AUDIT - NO LOGON OR FAILED LOGON ACTION CAPTURED

Edit:

Ok, so I had to test it myself, else I would not have believed it. Opposing to what the documentation states (AUDIT_TRAIL), setting the audit_trail to none does have an effect even when using pure Unified Auditing. But this is not intended, it is a bug. The fix is not included in the latest PSU (12.1.0.2.170117), but installing the above one-off patch (19383839) indeed resolved the issue. The problem is, this patch is not available to Windows platform. (I have tested this both on Linux and Windows, because Windows is always a factor you need to consider when working with Oracle.)

Setting audit_trail to DB does not reenable mixed-mode, relinking (renaming the DLL) enables pure Unified Auditing and that overrides this, you can confirm this by selecting from AUD$ or DBA_AUDIT_SESSION. So I suggest that you set audit_trail to DB.


SQL Error: ORA-14300: While partitioning and subpartitioning

I had to explicitly define all my range partitions. Interval partitioning did not work well with sub-partitioning template.

Oracle does HARD Limit of 1m partitions to my interval partitions.

I defined partitions as

PARTITION jan06 VALUES LESS THAN (TO_DATE('01-FEB-2006','DD-MON-YYYY')),
...
ALL THE WAY FOR NEXT 20 YEARS..
...
PARTITION jan26 VALUES LESS THAN (TO_DATE('01-FEB-2026','DD-MON-YYYY')),

Refer here and there


Order of your data returned is not guaranteed when you do not specify an order by.

Consider this example:

CREATE TABLE dbo.SmallTable([name] varchar(25) PRIMARY KEY NOT NULL,
                            Number int);

INSERT INTO dbo.SmallTable ([name],[Number]) VALUES ('compi',15 ), ('jack',5 ), ('malik',20 ), ('nana',10 );

If you were to just select without an order by:

SELECT [name],[Number]
FROM dbo.SmallTable;

The order this time is the same as your first example:

name    Number
compi   15
jack    5
malik   20
nana    10

But this is not guaranteed.

If you want the ordering to be guaranteed then you need to add an order by:

SELECT [name],[Number]
FROM dbo.SmallTable
ORDER BY [name] ASC;

What you are seeing when creating the nonclustered index on number is the query using this new NC index.

For example:

CREATE INDEX IX_Number
on dbo.SmallTable(Number);

Running the query again:

SELECT [name],[Number]
FROM dbo.SmallTable;

I get the same result as you:

name    Number
jack    5
nana    10
compi   15
malik   20

Because now sql server decides to use the IX_Number index to return the results:

Which is ordered by number asc.

To guarantee the ordering the query needs to be changed to:

SELECT [name],[Number]
FROM dbo.SmallTable;
ORDER BY [name] asc;

The ordering could even be different than the index order from the index that was used. This means that even if now the ordering is on the number or name column, it could be on any one of these columns or even none.

Always use an order by if the ordering is important.

More info on the subject can be found here, here, here and here


Disclaimer: This is still very broad and could be anything, so this answer is only some potential reasons for increased IO activity

My first inclination to your question But how can it increase continuously? when you mentioned that the load is 'write-heavy' is that you have autocommit enabled. If you are doing a lot of inserts, this might be your issue (emphasis mine):

  When importing data into InnoDB, turn off autocommit mode, because it performs a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET autocommit and COMMIT statements:

SET autocommit=0;
... SQL import statements ...
COMMIT;

  [src]

If that doesn't help, another major source of IO in a write-heavy environment are your indexes. Make sure you are not over-indexing (have unused indexes).

Also, InnoDB will write the primary key data to the index file for each secondary index as a way to identify them. So if you have a lot of natural or composite primary keys in tables that have secondary indexes, that could be a lot of extra data that is being written.


...I suppose it’s not just MS SQL version, Service Pack is also
associated.

Yes. You can read the lifecycle policy on service packs here.

When a new service pack is released, Microsoft provides either 12 or
24 months of support for the previous service pack, varying according
to the product family (for example, Windows, Office, Servers, or
Developer tools).

As a side note, if the product is out of support then the service pack is also out of support, regardless of this policy.

When support for a product ends, support of all the service packs for
that product also ends. The product's lifecycle supersedes the service
pack policy.

For your case, the SQL Server 2012 SP3 lifecycle policy can be found here.
The SQL Server 2012 SP3 support end date was on 10/09/2018

Note that starting from SQL Server 2017, the servicing model was changed to only CU's and GDR's when needed.


log_reuse_wait_desc - AVAILABILITY_REPLICA Unable to truncate transaction log

The reason you are seeing this behaviour is due to the fact that you have SUSPENDED the data synchronization (see @AMtwo's comment).
From BOL,

a secondary replica must be able to communicate with the primary replica and must be SYNCHRONIZED or SYNCHRONIZING.

so if you unsuspend the data synchronization, then the log can be reused since you took the log backup.


What are the practical uses for relational calculus?

I agree with  @Erik, these instruments are essentially equivalent, but I am going to add a few more ideas about this topic.
Context
In order to provide more context, it is necessary to have an authoritative definition for both terms. So, here are some excerpts from the relevant paper entitled Relational Completeness of Data Base Sublanguages, which was writen by the originator of the relational model, Dr. E. F. Codd, and published in 1972:

Relational algebra. The primary purpose of this algebra is to provide a collection of operations on relations of all degrees (not necessarily binary) suitable for selecting data from a relational data base.
Relational calculus. Having defined a relational algebra, we now consider an applied predicate calculus which may also be used in the formulation of queries on any data base consisting of a finite collection of relations in simple normal form.

In this way, one can see that they serve the same purpose, since they are two different mathematical means to express a group of operations considered valid within the relational framework.
Comparison of both approaches
In the same paper, Codd contrasts both methods in order to point out some important aspects in which each one stands out:

One advantage that might be claimed for the algebraic approach is its freedom from quantifiers. Howerver, the calculus appears to be superior to algebra in four respects.

Ease of augmentation
Scope for search optimization
Authorization capability
Closeness to Natural Language

So, these aspects should be taken into account by organizations that aim to build their own languages or database platforms.
Practical value
I do not know if there are contemporary successfully commercial RDBMS that provide query languages purely based on relational calculus, but I deem that its practical worth lies in the fact that it offers a theoretically grounded alternative for vendors who plan to design and implement a new valid relational language, or extend an existing one without departing from proven and accepted practices. In this respect, it can be useful to think of relational calculus as a blueprint that such vendors can follow in order to produce a relational language.
Since the market is largely dominated by SQL platforms, millions of users around the world are already familiar with several of its particular dialects, and billions of dollars are invested in such platforms, it would be very interesting to determine whether creating new relational lenguages would be profitable, but if so, relational calculus could always play an important role.
Of course, such projects can be much more feasible in the academic field, so there is more practical value regarding this tool.


As there is no unsigned int in SQL Server doesn't an Identity Seed of -2,147,483,648 make more sense for large tables?

There is nothing at all wrong with starting at -2,147,483,648 as far as SQL Server is concerned. Starting at 2,147,483,647 and counting backwards with IDENTITY(2147483647,-1) is perfectly valid too.
Things that would make me be wary of doing so:

It might confuse people who don't expect to see negative values in such positions. It is unusual enough that it could easily look like bug. If nothing else you might get sick of explaining it.
If you ever pass the IDs to other services for any reason they may fail input validation do to that code not expecting to see negatives (an external system shouldn't care about your internal IDs like this, but there are many things that happen which shouldn't!). Furthermore, code in other layers of your application might use an unsigned int32 making the "down from int.max" option even more dangerous as you'll not hit the problem until dropping below 0.
People sometimes use negative magic numbers to have special meaning, and this could cause collisions which lead to difficult to explain bugs. For instance shortening WHERE (x.a<>y.a OR x.a IS NULL AND y.a IS NOT NULL OR x.a IS NOT NULL AND y.a IS NULL) to WHERE ISNULL(x.a,-1)<>ISNULL(x.a,-2) is something I've seen numerous times. Not likely for your PK as that'll never be NULL but might happen in comparing FK values in other tables. I've even seen someone use somevalue>-1 in place of somevalue IS NOT NULL (apparently there is a circumstance where that is more efficient, though he never explained to my satisfaction what the circumstance might be!). There might be that and other odd shenanigans in code outside the DB too.
Most importantly: scale, particularly unexpected scale. Software and data that doesn't die early often outlives their creators' vision, and grows more over time accordingly. Unless you are very constrained by storage or RAM (perhaps in an embedded system) then plan for at least one order of magnitude higher than you expect.
If I expect hitting ~400,000,000 is ever going to be likely in the lifetime of the data, far before the difference between 2 or 4 000,000,000 is a consideration, I'm already going to go for a larger key so doubling up by using negative values isn't going to make much difference.
You don't want to be making a change to something core like a PK in four years time, but you even less want to do it in eight years time. In both cases if the design lasts that long you've long forgotten details and many other bits & bobs have started to depend on the key so the changes needing to be made grow massively, and even that single table is going to be massive work to change (unless most of the data gets deleted after a time) as it contains that many rows, then you have all those that refer to it with FKs to work on also.


Permission to select sys tables in stored procedure

My assumption of stored procedure mechanism was role/user only
  requires EXECUTE to perform the procedure. We don't need to concern
  the permission setting of each DB object like CRUD to table and so on.

This statement is true when ownership chaining applies. With an unbroken chain, users do not need permissions on indirectly referenced objects.

However ownership chaining does not apply metadata visibility when selecting from the catalog views. Users must be granted some permissions on an object in order for it to be visible when selecting from the catalog views.

Below is a script that shows this behavior for your use case. If you need to return objects users have no permissions to use, you'll need to use EXECUTE AS or certificate signing to elevate permissions for users of the BlobCheckExisting.usp_DoesStoredProcedureExist procedure.

CREATE SCHEMA BlobCheckExisting;
GO

CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist]
@schemaName varchar(128),
@procedureName varchar(128)
AS
SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id]
    WHERE s.[name] = @schemaName  AND p.[name] = @procedureName), 1, 0)
GO

CREATE PROCEDURE dbo.usp_TestProc AS RETURN;
GO

CREATE LOGIN MinimallyPrivilegedUser WITH PASSWORD = 'sdf4$$$gFx&';
GO

CREATE USER MinimallyPrivilegedUser;
GO

GRANT EXECUTE ON BlobCheckExisting.usp_DoesStoredProcedureExist TO MinimallyPrivilegedUser;
GO

--test permissions without permissions on object
EXECUTE AS USER = 'MinimallyPrivilegedUser';
GO
--no rows returned because user has no permissions on dbo.usp_TestProc
EXEC [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName = N'dbo', @procedureName = N'usp_TestProc';
GO
REVERT;
GO
--grant permissions
GRANT EXECUTE ON dbo.usp_TestProc TO MinimallyPrivilegedUser;
GO

--test permissions with permissions on object
EXECUTE AS USER = 'MinimallyPrivilegedUser';
GO
--row returned because user has execute permissions on dbo.usp_TestProc
EXEC [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName = N'dbo', @procedureName = N'usp_TestProc';
GO
REVERT;
GO


How can I keep two databases on different sites in sync?

Replication was made for small changes, so you're on the right track.
To keep the systems in sync, at the database level, you will need to setup a mysql master-master replication scenario.
I've used the tutorial from this linke (http://howtodba.com/how-to-build-a-cheap-web-cluster-with-replicated-storage-and-mysql-master-master-configuration/), to setup such configuration.
Depending on how much data is changed, you can choose between [statement] or [row-based] replication.
More info about the differences between the 2 types: http://www.databasejournal.com/features/mysql/article.php/3922266/Comparing-MySQL-Statement-Based-and-Row-Based-Replication.htm


Transfer data from database1 to database2 using task queue

You should be able to use triggers and dbilink to sync your data if you're prepared to install plperl on the postgresql end. If you are running version 9.1+ then you could instead try the foreign-data-wrapper functionality. There appear to be tds and fdw wrappers, though I've not used either myself.

Unless I'm missing something, an external queue manager sounds overkill.


The bit-like field will make it harder to find all users or articles with a certain property. For users/permissions, that's not much of a problem (unless you want a list of e.g. all administrators). For articles, I can imagine that you want to filter on certain attributes (e.g. only non-18+ articles). You also have the option to store those fields (option 3) as single bit columns; then, no extra table is needed.

Another consideration: what happens when you want to add an additional permission/attribute which is by default set to '1'/'true'? For the bitmask field (option 1), this can be done by adding the right power of 2 to all values in column permissions; for option 2, you need a mass INSERT in user_permissions_map; adding an extra bit column with ADD COLUMN and DEFAULT true (option 3) is clear and easy.


To install specific version of mongodb you need to follow certain steps documented on MongoDB site:

In my opinion it includes:

sudo apt-get install -y mongodb-org=3.0.1 mongodb-org-server=3.0.1 mongodb-org-shell=3.0.1 mongodb-org-mongos=3.0.1 mongodb-org-tools=3.0.1

You can opt for packages by omitting it from above list.

You can find detailed document here


Postgres: Trigger always uses the same value for now()

Manual:https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

statement_timestamp() / now() returns the start time of the current statement (more specifically, the time of receipt of the latest command message from the client).
clock_timestamp() returns the actual current time, and therefore its value changes even within a single SQL command.


In the slony docs they have this third sequence as an example of a structured switchover [http://slony.info/documentation/failover.html#AEN839]:

lock set (id = 1, origin = 1);
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2, wait on=1);

Anyway, I've always used the alt-perl tools with the following, probably well-known, syntax. This moves set2 origin from node 1 to node 2, ie node 2 will become provider.

/usr/local/slony/bin/slonik_move_set set2 1 2 | /usr/local/pgsql/bin/slonik

As you state the script use this sequence of commands: lock set, sync, wait for event, move set. This has worked flawlessly.

What might take some time is the locking and sync. The locking might have to wait for any long running transactions to finish. If the sync is lagging all nodes has to be synchronized when doing a slonik_movet_set.

It seems reasonable to do a sync after the lock as way to confirm the sync between nodes. You could add the sync to your slonik script, ie:

lock set (id = 1, origin = 1);
sync (id = 1);
wait for event (origin = 1, confirmed = 2, wait on=1);
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2, wait on=1);

Also notice id of the confirming node and that you can specify a timeout for the wait for event command, default is 600 sec  [http://slony.info/documentation/stmtwaitevent.html]


Restore your master database from a backup.  This is a good guide:

http://thomaslarock.com/2014/01/restore-the-master-database-in-sql-server-2012/


PostgreSQL UPSERT issue with NOT NULL columns

Your second example never makes sense, while column1 is defined NOT NULL. You must provide a value for the required column, or the command will fail before it can even check for unique violations.
Query 1
This query only executes the UPDATE part only if either column1 or column2 are NULL and at least one actually changes (anything actually changes), but it does not allow to revert either back to NULL:
INSERT INTO test AS t
       (column1, column2          , status, id)
VALUES (now()  , gen_random_uuid(), 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = COALESCE(t.column1, EXCLUDED.column1)
     , column2 = COALESCE(t.column2, EXCLUDED.column2)
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM COALESCE(t.column1, EXCLUDED.column1)
    OR t.column2 IS DISTINCT FROM COALESCE(t.column2, EXCLUDED.column2));

But COALESCE() never kicks in for this particular setup. column1, being defined NOT NULL, cannot be NULL, neither in the table nor in the input VALUES. So column2 has to be NULL or nothing happens. Hence we can never reset column2 to NULL anyway.
Query 2
This query only executes the UPDATE part only if either column1 or column2 are NULL and at least one is actually being changed -even if back to NULL:
INSERT INTO test AS t
       (column1, column2, status, id)
VALUES (now()  , NULL   , 0     , '5beb004a-63ec-4f01-8604-37296a208e5f')
ON     CONFLICT ON CONSTRAINT upsert_conflict
DO     UPDATE
SET    column1 = EXCLUDED.column1
     , column2 = EXCLUDED.column2
WHERE (t.column1 IS NULL
    OR t.column2 IS NULL)
AND   (t.column1 IS DISTINCT FROM EXCLUDED.column1
    OR t.column2 IS DISTINCT FROM EXCLUDED.column2);

This time, actual input values are used, even if NULL. No COALESCE() that defaults to the existing value if the new one is NULL
db<>fiddle here
Either way, you can be sure that a row with (status, id) = (0, '5beb004a-63ec-4f01-8604-37296a208e5f') exists after executing the command successfully - even if that row might be missing from the set returned by a RETURNING clause. See:
Related:

How to use RETURNING with ON CONFLICT in PostgreSQL?


Find a row with value zero in any column

Too many variants:

WHERE column1=0 OR column2=0 OR ... OR column64=0
WHERE column1*column2* ... *column64=0
WHERE 0 IN (column1, column2, ... column64)

and so on...


Import all database links using dbms_datapump

It is not DATABASE LINK or DATABASE LINKS, it is DB_LINK. You can find the valid values in DATABASE_EXPORT_OBJECTS, SCHEMA_EXPORT_OBJECTS and TABLE_EXPORT_OBJECTS.


Are temp tables automatically created when referencing to them?

It's not only about Temporary table it's about the INTO Statement in Sql Server. The INTO statement creates a table in the Database and this table is equal to same table we create with Create Table statement.

But this statement does not inherit any constraints.


Use Common Table Expression (CTE) to reduce locks (max_locks_per_transaction)

A CTE is no table and won't need an entry in the lock table.

It is probably a good idea not to create and drop temporary tables all the time. Apart from the problem you are experiencing, it can lead to a bloated pg_attribute catalog table (all those added and deleted columns).

Mind that a CTE is only for the duration of a single table.


Mysql: Is it possible to handle 1000 concurrent while having low cpu?

Here's how to identify which 'bad' query to fix that will give you the most bang for the buck:  The slowlog plus pt-query-digest.  More details here:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

I rarely need to go down the list more than 3 items to make a dramatic improvement.

If you are weak on knowing what indexes to add, don't simply "index ever column".  See http://mysql.rjweb.org/doc.php/index_cookbook_mysql

  Is there any measure one could take that I didn't know about to reduce the CPU usage of search queries?

No.  High CPU is fixed by using selected composite indexes and/or reformulating the queries.  Show us the queries.  "Search" is too vague a term.

Here's an extreme anecdote:  The CPU was pegged at 100%.  The slowlog pointed at one query.  It had WHERE DATE(foo) = '...'.  By making that "sargable", the CPU dropped to 2%.

As for benchmarks...  Beware.  They tend to see how many connections they can run before running out of horsepower.  That is, the results are guaranteed to say 100% CPU!  The real metric they get out is "queries/second" or "maximum connections".

Furthermore, it has repeatedly been documented that if you push mysql beyond some number of concurrent users, throughput goes down!  A decade ago, that number was about 6; now it is more like 64.  But 1000?  No way.  It is spinning its wheels because all 1000 are stumbling over each other.

How to handle 1000 users?  Replication feeding multiple Slaves.  With such, you could have 1000 slaves, each running a mere 10 connections; CPU will be very low.  Etc.