What does a SQL Server DBA need to know about Windows Server?

You definitely need to know your way around...

Performance Monitor
Disk Management (mount points, 64k clusters, RAID levels etc)
Group Policy + Active Directory + Security
Shares + NTFS permissions
Cluster Service + resource groups
Powershell
Physical/Logical sockets/cores + affinity
NUMA architecture

Without these (and others) you're ineffective outside of raw SQL Server work (eg setting up logins). As soon as you want backups, one or more of the above starts to apply...


I think you may be missing synonym for dbms_alert for adminuser schema. A object grant and synonym both are normally required (unless you own the object).

Try following as sys account, perhaps it will fix the problem.

create or replace synonym adminuser.dbms_alert for sys.dbms_alert;

If you don't want to create a synonym, you should use sys.dbms_alert, if your create a synonym as shown above, using just dbms_alert is sufficient in your code.


Do all users need access to the postgres database in pg_hba.conf?

What you see is pgAdmin connecting to the configured  Maintenance DB - which is postgres by default. Select a server in the object browser pane and choose "Properties" from the context menu (right-click). The drop-down menu only offers the typical choices, but you can just type in any database name.

If a user's access is limited to one database in pg_hba.conf (which is the most efficient place to do that), you can make it work with pgAdmin by configuring this one database as maintenance db. Nothing bad will come of it.

The other option is to grant access to the maintenance DB (postgres or whatever) in pg_hba.conf additionally.

You can also open all gates in pg_hba.conf and regulate access with database permissions, like @a_horse explained in his answer. This is easier to manage, but restricting access in pg_hba.conf is safer and faster - more robust against DoS attacks and heavy load. Won't make a big difference in most cases, though.

Quoting the documentation of pgAdmin 1.22 (final release of pgAdmin III):

  The maintenance DB field is used to specify the initial database that
  pgAdmin connects to, and that will be expected to have the pgAgent
  schema and adminpack objects installed (both optional). On PostgreSQL
  8.1 and above, the maintenance DB is normally called 'postgres',
  and on earlier versions 'template1' is often used, though it is
  preferable to create a 'postgres' database for this purpose to avoid
  cluttering the template database.


How to get last inserted rows where a condition is satisfied

If you want the 'last' A:

SELECT * FROM table2
    where esp = 'A'
    ORDER BY id DESC
    LIMIT 1;

If you need something more complicated, see "groupwise-max".


Replicating tables from LAN database to web server?

There are a variety of ways you can do this.

If you only need a portion of the database, you can have copies of the tables which you refresh on the destinations periodically. The idea is that you have your users reading one copy of the data, then you have an identical table in a different schema, and you reload that copy while the users are reading. Then in a very quick transaction, you switch the copies of the table between schemas, and it's essentially a metadata operation. I describe this in detail in the following two posts:

Schema Switch-A-Roo
Schema Switch-A-Roo : Part 2

If you need most or all of the database, then it is going to be much simpler to use log shipping to facilitate readable secondaries. It just so happens that today I published a very thorough walk-through of creating readable secondaries with Express:

Readable Secondaries on a Budget

Essentially, you play round-robin with which instance gets read from at any one time; since you want multiple active simultaneously, it would only be a little bit more complex. You might have two or four instances of Express on each web server, and every 15 minutes you switch the standby on all of the servers to have the most recent data.

Since you're on Express for both source and destination, in either case, you will need an agent of some kind to schedule the daily (or more frequent) pushes.


Limiting the number of CPUs used in SQL Server 2012 Enterprise

The question is a little hard to understand.

  We are in the process of migrating a SQL Server environment to VM but
  highly feel the server was over spec'd to start.

  We want the customer to feel good about going to a VM with less
  resources as they don't appear to be needed.

  To start we want limit the number of CPUs that are currently being
  allocated. Is setting the Processor affinity a safe approach? Any
  gotchas?

Is the current server a physical and you want to limit the CPU resources it has access to in order to highlight to the customer that it will run fine on a VM with fewer CPU resources?

Assuming yes, processor affinity may work for this, however, it can cause SQL Server to adopt a 1:1 ratio of Schedulers on SQLOS to Cores on the server, which can cause poor utilisation of even the now reduced CPU resources. See this blog post for more information.

A better solution might be to use Resource Governor to limit the amount of CPU for the default pool to control the amount of CPU resources it can use without introducing the 1:1 affinity issue. See here for more information.

Basically, if you have, say, 24 cores at the moment and want to reduce to 12, then you could set the default pool MAX_CPU_PERCENT to 50. It is not an exact analog for reducing the available cores, but it will give you some confirmation that your workload will be fine with reduced CPU resources.

NOTE: If you enable trace flag 8002 and use processor affinity you can avoid the 1:1 scheduler to core issue. See here for more info. Enabling this trace flag requires a restart.

NOTE 2: If your current server is not physical, then just lower the vCPUs for that server to validate (if you're confident the workload can be sustained on fewer cores).


Connection Reset by Peer: Socket Write Error

Doesn't look like an issue with SQL Server. Here's a link which talks about connection being forcibly closed by sql server :

https://technet.microsoft.com/en-us/library/ms187005(v=sql.105).aspx

Also, the following post might help you analyze the error further :

https://stackoverflow.com/questions/25611741/java-net-socketexception-connection-reset-by-peer-socket-write-error-when-serv


SQL Service failed to start master.mdf is in use

I have found the issue, it was something called a ghost process.

If you want then use the extra tool to kill the process SQL Server will start.

more information on http://amorvita.github.io/2013/08/finding-a-zombie-process/


This should work,

pg_dump --section=pre-data -d myDb | grep -e '^\(GRANT\|REVOKE\)'


Setting up simple relational database structure

Unless you embed a list of pos_div_name in a column with type TEXT, you need to create table POS_STRUCT_DIV (POS_div in your diagram) as it has one-to-many relationship with POS_STRUCT.

You could use pos_div_name as the foreign key in the MEMBER as the name is never change. It saves you from looking up pos_div_name when loading MEMBER but the row size could be larger. So it is up to you.


Have a look a tablespaces. You can keep the main and small parts of the database in the SSD drives and migrate the bigger tables to the tablespaces in your other drive.

http://www.postgresql.org/docs/9.4/static/manage-ag-tablespaces.html

In alternative the whole database can be put into the tablespace by setting the default tablespace parameter.


Tool to generate large datasets of test data

The best tool (if you can find it) is DataFactory. (Sadly out of print). I've generated absolutely delightful (and quite authentic-looking) datasets from it.

Generatedata.com is... acceptable, but doesn't scale very well.

DataGenerator is something to keep an eye on.

And while DTM Data Generator is clunky and a poor substitute for DataFactory, it exists and is being sold, and I've used it to generate mildly acceptable data.


Blocked Process Report SQL Server 31 hour duration

Not a huge fan of reporting profiler for checking blocking, but to get proper analysis would suggest you to either:

1) Use Adam Mechanic's SP_WHOisActive which is indeed very simple way of analyzing what and where could the blocking be a scenario with detailing on database name, app name along with its process.

2) If possible you can use SqlDiag utility to analyse same.

Ultimately, you need to find out who is doing the blocking first – why is their transaction taking so long? If it’s due to inefficiencies in the query – can you rewrite it? If it’s due to inefficiencies in the plan – can you add an index? If it’s modifying a large amount of data – can you break it down into smaller chunks so that each set is locked for a shorter period of time? These are ALWAYS the thing to try first.

However, if you still would like to go with reports to analyse blocking i would suggest to read this article from Jonathan Kehayias on how to use the block process report in sql server 2005/8


emergency-mode repair failed, nothing to do?

The answer is - as many of you replied in the comment's - that there is no way around a backup is the only way to go. and I did find a good backup and it was restored. that you all for the help.


In Postgres, set log_rotation_size but my logs are still not rotating

In Debian/Ubuntu, per policy it's the logrotate package that is in charge of handling log rotation and purge for all services, PostgreSQL included.

From  https://www.debian.org/doc/debian-policy/ch-files.html :

  Log files must be rotated occasionally so that they don't grow
  indefinitely. The best way to do this is to install a log rotation
  configuration file in the directory /etc/logrotate.d, normally named
  /etc/logrotate.d/package, and use the facilities provided by
  logrotate.

So there is  a /etc/logrotate.d/postgresql-common configuration file that can be tuned to rotate log files when they grow over a size given by the size keyword , among a lot of other options.

On the other hand, should you want to use the PostgreSQL feature to achieve this, as opposed to the pre-configured method of the OS, you need to consider all logging parameters in postgresql.conf, not just log_rotation_size. First a Debian/Ubuntu packaged PostgreSQL will turn off logging_collector to not interfere with logrotate, but most of log_* options depend on it to be effective, as mentioned in the doc, for instance:

https://www.postgresql.org/docs/current/static/runtime-config-logging.html

  log_rotation_size (integer)

  When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have
  been emitted into a log file, a new log file will be created. Set to
  zero to disable size-based creation of new log files. This parameter
  can only be set in the postgresql.conf file or on the server command
  line.


Failing to insert into table getting error: ORA-00600: internal error code, arguments: [ktssinseg2]

You could try to copy the table into a new object:

CREATE TABLE newtab AS SELECT * FROM MV_IN_SMS_TOTAL

Try to do your inserts into the new table. If that solves the problem and the problem is isolated to that specific object (table), and you use that as a workaround.

RENAME TABLE MV_IN_SMS_TOTAL TO MV_IN_SMS_TOTAL_OLD;
CREATE TABLE MV_IN_SMS_TOTAL AS SELECT * FROM MV_IN_SMS_TOTAL_OLD;

Note that all auxiliary objects must be created. Indexes, constraints, triggers, you name it - and privileges.

Regards,

Daniel


Methodology & Architecture to Create ETL Process

I would recommend reading The Data Warehouse Toolkit by Ralph Kimball. Goes through the theory,  with some good examples, and will leave you with a solid understanding of the theory. Then you can look at the tools themselves.

If you're looking for something to build your ETL process, Pentaho have something called the "Star Modeller" which lets you model your dwh, and it then builds the ETL/cubes etc.  But it's pretty new and far from complete - worth keeping an eye on though.


This works fine:

postgres=# create table hosts ( type varchar(20), name varchar(20));
CREATE TABLE
postgres=# \q
postgres@ironforge:~$ psql -c "insert into hosts (type,name) values ('test','10.100.133.1')"
INSERT 0 1
postgres@ironforge:~$


How to edit `postgresql.conf` with pgAdmin 3?

In pgAdmin…

To edit the postgresql.conf file:Choose Tools > Server Configuration > postgresql.conf
To edit the pg_hba.conf file:Choose Tools > Server Configuration > pg_hba.conf

Avoid the red-herring File menu:

File > Open postgresql.conf
File > Open pg_hba.conf


From the documentation on sys.dm_exec_procedure_stats:

  Returns aggregate performance statistics for cached stored procedures. The view returns one row for each cached stored procedure plan, and the lifetime of the row is as long as the stored procedure remains cached. When a stored procedure is removed from the cache, the corresponding row is eliminated from this view.

Therefore, it is likely that some time on September 1st, the server had an event that caused the procedure cache to be flushed, or at the very least, caused the plans for the procedures you care about to be pushed out of cache. We've already ruled out the most obvious culprit - a failover or service restart - but it could have been anything, really:

a unit test that executed a whole slew of procedures, filling up the cache
an sp_configure change, which - for certain options - has a side effect of clearing the procedure cache
a single query with a huge memory grant
a manual DBCC FREEPROCCACHE;
explicit recompiles or drop/re-create of procedures
...


Physical or logical backup takes more space when backing up same MySQL database?

If you backup the files by copying the directory tree, the backup is the same size as the original.
If you use mysqldump (or similar tool) to backup, that will contain CREATE and INSERT statements to recreate the database from scratch.  This can be larger or smaller, depending on the data involved.
An example of "smaller": a BIGINT takes 8 bytes in a table, but if the value is 1, it will take about 2 bytes in a logical dump (1 and a comma).


Is there a more concise way to convert a UTC datetime into a local date only?

If you're not averse to having a function do the dirty work, this helps make the statement cleaner:

CREATE FUNCTION LocalDateFromUTCTime
(
    @UTCDateTime datetime
)
RETURNS datetime
BEGIN
    DECLARE @diff int;
    SET @diff = datediff(hh,GetUTCDate(), GetDate());
    RETURN DATEADD(day, DATEDIFF(day, 0, DATEADD(hh, @diff, @UTCDateTime)),0);
END

You could then do something like:

SELECT dbo.LocalDateFromUTCTime(MyUTCDate), COUNT(*)
FROM MyTable
GROUP BY dbo.LocalDateFromUTCTime(MyUTCDate);

This does, of course, make the statement non-SARGable.

If sargability is of primary concern due to the large number of records you may have, you could create a materialized view with an index on the calculated field.

EDIT:

Per @RichardThekiwi the SARGability of this particular statement is not affected by use of the function since the function is not part of either a JOIN or a WHERE clause.  Where the function is used, it is ran after any indexes would have been used.

ALSO, not the above code will truncate any time portion of the date input to the function (this is by design).  Therefore, any time zones that implement minutes such as the Prince Edward Island in Canada (UTC -4:30), India (UTC -4:30), and Kathmandu (UTC +5:45)


Accessing the OLD/NEW table value in a trigger function in plain SQL

Firstly, to create a trigger on the order_items table, the update_totals() must be a trigger function which RETURNS TRIGGER (bold is mine).

  A data change trigger is declared as a function with no arguments and
  a return type of trigger. Note that the function must be declared with
  no arguments even if it expects to receive some arguments specified in
  CREATE TRIGGER

If not, It causes an error ERROR: function func_order_items must return type trigger.

Secondly, please remember that you cannot have OLD when AFTER INSERT on the trigger. If not, It raises an error ERROR:  record "old" is not assigned yet.

Finally, please take a look at the example as below:

CREATE TABLE IF NOT EXISTS orders(ID INT NOT NULL PRIMARY KEY, X INT);
CREATE TABLE IF NOT EXISTS order_items (ID INT NOT NULL PRIMARY KEY, ORDER_ID INT, Y INT);

INSERT INTO orders VALUES(1, 0);
INSERT INTO order_items VALUES(10, 1, 20);
INSERT INTO order_items VALUES(11, 1, 30);

CREATE OR REPLACE FUNCTION func_order_items() RETURNS trigger AS
$$
BEGIN
  IF (TG_OP = 'UPDATE') THEN
    UPDATE orders
    SET X = (SELECT SUM(Y) FROM order_items WHERE order_id = OLD.order_id)
    WHERE ID = OLD.order_id;
  ELSIF (TG_OP = 'INSERT') THEN
    UPDATE orders
    SET X = (SELECT SUM(Y) FROM order_items WHERE order_id = NEW.order_id)
    WHERE ID = NEW.order_id;
  END IF;
  RETURN NULL;
END
$$
LANGUAGE PLPGSQL;

CREATE TRIGGER trigger_order_items
AFTER INSERT OR UPDATE
ON order_items
FOR EACH ROW EXECUTE PROCEDURE func_order_items();

UPDATE order_items SET Y = 200 WHERE ID = 10;
INSERT INTO order_items VALUES (12, 1, 200);


how to design a database's table to store mysql spatial data in a parent child relation?

Your location table should have a self-reference. Use the Polygon1 type for your geography.

eg:

LocationId (Primary key, not null)
LocationSequence (int, not null)
LocationName (string, not null)
parentLocationId (int, can be null)
Geographical data (POLYGON , not null)

So, your table might have:

1001, 1, World, null, ?
1002, 1, US, 1001, ?
1003, 2, China, 1001, ?
1004, 3, Japan, 1001, ?
1005, 4, South Africa, 1001, ?
1006, 1, Pretoria, 1005, ?
1007, 2, Cape Town, 1005, ?
1008, 5, India, 1001, ?
1009, 1, New Delhi, 1008, ?
1010, 2, Chennai, 1008, ?


Finding distinct rows across two tables: Full Outer Join more efficient than Union?

The semantics of the two queries are not the same - UNION removes duplicates, whereas the FULL OUTER JOIN will not:

DECLARE @T1 AS table (id bigint NULL, val integer NULL);
DECLARE @T2 AS table (id bigint NULL, val integer NULL);

INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T1 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);
INSERT @T2 (id, val) VALUES (1, 1);

SELECT COALESCE(t1.id, t2.id) AS id, COALESCE(t1.val, t2.val) AS val
FROM @t1 t1
FULL OUTER JOIN @t2 t2
    ON t2.id = t1.id
    AND t2.val = t1.val;

SELECT t1.id, t1.val
FROM @t1 t1
UNION
SELECT t2.id, t2.val
FROM @t2 t2;

Output:

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
║  1 ║   1 ║
╚════╩═════╝

╔════╦═════╗
║ id ║ val ║
╠════╬═════╣
║  1 ║   1 ║
╚════╩═════╝

That said, the optimizer does not know many FOJN tricks, so it is always possible that there is a better way to express the query than the natural UNION. Only commonly-useful and always-correct transformations are implemented.

Note that with a unique constraint only on the larger table, the optimizer chooses a hash union, without expensive duplicate-removal on the probe input, that makes it choose Concat Union All in the question example:

ALTER TABLE #t2
ADD CONSTRAINT UQ2
UNIQUE CLUSTERED (id);

SELECT COUNT(*), AVG(x.id), AVG(x.val)
FROM (
    SELECT t1.id, t1.val
    FROM #t1 t1
    UNION
    SELECT t2.id, t2.val
    FROM #t2 t2
) AS x;

The FOJN rewrite may well be a useful one in cases where you know there cannot be duplicates within each input set, but this condition is not enforced with a unique constraint or index (particularly on the large input).

If such a uniqueness guarantee does exist, and yet the optimizer does not select a Hash Union, you might try an OPTION (HASH UNION) hint, to see how it compares.


Disk size and log file growth during upgrades

(yeah, I added a new column to a multi-billion row table.)

Adding a column to a very large table can have implications but there is a clever way of adding column as well.
From : Adding NOT NULL Columns as an Online Operation

Starting with SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table.
Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.

Enterprise edition (works on 2008 R2 standard edition as per comment) has ALTER TABLE source_table SWITCH TO new_destination_table

The databases are on Simple Recovery model.

In simple recovery model, only a CHECKPOINT will truncate the log.
If you are recording autogrowth metrics during your typical upgrade, then an average of those collected metrics would give you a good starting figure. This script will help you get started (you would need default trace enabled and running on your server).

I think the log growth is set to 10%, but even if it were set to 1 or 2 Gig increments, I think I'd still be cutting it close.

I would suggest you to change the autogrowth setting from Percentage to Fixed MB. As @AaronBertrand says in his comment :

The problem is that it takes longer and longer over time, since 10% of a growing file is also constantly growing itself - it's like compound interest, but you're paying, not receiving.

For completeness, make sure you have Instant file initialization enabled, so the data file autogrowth can take advantage of it.


Look at the differences between the two EXPLAINs...

ALL  NULL       NULL   1144
REF  new_index  const     1

The first looked at ALL 1144 (approximate) rows of the table

The second used new_index, checking against a "constant", finding 1 (approximate) row.  Clearly faster.

The 503 may be 2+3*167, Perhaps the column is VARCHAR(167) CHARACTER SET utf8 NOT NULL?  The "2" is overhead for "VAR"; the "3" is the max byte length of a utf8 character.  About the only use for "503" is exactly what I just did -- deduce something about the index in question.

Or maybe it is a VARCHAR(125) CHARACTER SET utf8mb4 NULL?  2+4*125+1; the "1" is for a NULL flag.

The size of the "key_len" does not matter, except that there are limits -- 767, 3072, and possibly others, depending on version, etc.

Index "prefixing" (as danblack mentioned) is problematic.  It is often not used when you think it should.  I recommend avoiding it in most cases.  This is a case where you might need it, assuming you can't shrink the TEXT column:

CREATE TABLE ...
    foo TEXT NOT NULL, ...
    INDEX(foo(167)) ...


Find IDs from a list that don't exist in a table

You can use an outer join against a values list (similar to Martin's answer mentioned above):

select t.id
from (
  values (4),(5),(6)
) as t(id)
  left join images i on i.id = t.id
where i.id is null;

or a not exists together with the row constructor:

select *
from (
   values (4),(5),(6)
) as v(id)
where not exists (select *
                  from images i
                  where i.id = v.id);

If you like you can also put the values clause into a CTE to make the final query easier to read:

with v (id) as (
 values (4),(5),(6)
)
select v.id
from v
  left join images i on i.id = v.id
where i.id is null;


How to migrate SQL Server Stored Procedures using temporary tables or table variables to Oracle?

One way to do this would be Object Types, in this case the type would be analagous to your #t1. So it would need to be defined somewhere but it would not need to be global, it could be per-schema or per-procedure even. First, we can create a type:

SQL> create or replace type t1_type as object (x int, y int, z int)
  2  /

Type created.

SQL> create or replace type t1 as table of t1_type
  2  /

Type created.

Now set up some sample data:

SQL> create table xy (x int, y int)
  2  /

Table created.

SQL> insert into xy values (1, 2)
  2  /

1 row created.

SQL> insert into xy values (3, 4)
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

And create a function over this data returning our "temporary" type:

SQL> create or replace function fn_t1 return t1 as
  2  v_t1 t1 := t1();       -- empty temporary table (really an array)
  3  v_ix number default 0; -- array index
  4  begin
  5  for r in (select * from xy) loop
  6  v_ix := v_ix + 1;
  7  v_t1.extend;
  8  v_t1(v_ix) := t1_type(r.x, r.y, (r.x + r.y));
  9  end loop;
 10  return v_t1;
 11  end;
 12  /

Function created.

And finally:

SQL> select * from the (select cast (fn_t1 as t1) from dual)
  2  /

         X          Y          Z
---------- ---------- ----------
         1          2          3
         3          4          7

As you can see this is pretty clunky (and uses collection pseudo-functions, which is an obscure feature at the best of times!), as I always say, porting from DB to DB is not merely about syntax and keywords in their SQL dialects, the real difficulty comes in different underlying assumptions (in the case of SQL Server, that cursors are expensive and their use avoided/worked around at all costs).


What is the meaning of Order preserving Conflict Serializable schedules?

In the schedule, T3 occurs completely before T1 and T2, but T2 and T1 are interleaved (so there is no "one occurs completely before the other" relationship between them).
So, it can be in OCSR as long as there exists a serial history that T3 occurs completely before T1 and T2.
Actually there is a serial history T3->T1->T2 that meets the above condition.
Then, it is in OCSR.


You can eliminate the sort if you like, although it's hard to say if that will necessary improve query performance. The key is how you constructed your ORDER BY clause along with the search conditions. If there's any row that matches x.sedolcode = b.breakdowncode then you want to take that row, otherwise you go to the next condition. With the right indexes we can avoid the sort by splitting up the APPLY. The SQL Server query optimizer actually gives a nice hint because it transforms your OR conditions into UNIONs.

I'm going to create a limited example in order to show the general query plan shape that I'm after. I will also assume that the PfPortfolioHoldingClassificationFtid table has a primary key and a clustering key on a PK column. Here is my test data:

CREATE TABLE #BreakdownSet (
    BreakdownClassificationId BIGINT NOT NULL,
    breakdowncode VARCHAR(10) NULL,
    sedolcode VARCHAR(10) NULL,
    isincode VARCHAR(10) NULL
);

INSERT INTO #BreakdownSet
SELECT
  t.RN
, CASE WHEN RN % 10 = 1 THEN t.RN ELSE NULL END
, CASE WHEN RN % 10 = 4 THEN t.RN ELSE NULL END
, CASE WHEN RN % 10 = 7 THEN t.RN ELSE NULL END
FROM
(
    SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

CREATE TABLE dbo.PfPortfolioHoldingClassificationFtid (
    PK BIGINT NOT NULL,
    isincode VARCHAR(10) NOT NULL,
    sedolcode VARCHAR(10) NOT NULL,
    ClassificationDate DATE NOT NULL,
    OTHER_COLUMN VARCHAR(200) NOT NULL,
    PRIMARY KEY (PK)
);

INSERT INTO dbo.PfPortfolioHoldingClassificationFtid WITH (TABLOCK)
SELECT
  t.RN
, t.RN
, t.RN
, DATEADD(DAY, t.rn / 100, '20170101')
, REPLICATE('OTHER', 40)
FROM
(
    SELECT TOP (1500000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) t;

CREATE INDEX isin_date ON dbo.PfPortfolioHoldingClassificationFtid (isincode, ClassificationDate);

CREATE INDEX sedol_date ON dbo.PfPortfolioHoldingClassificationFtid (sedolcode, ClassificationDate);

Here is your query with my table definitions:

SELECT b.breakdownclassificationid,
       k.isincode,
       k.sedolcode,
       classificationdate,
       other_column
FROM   #breakdownset b
       OUTER apply (SELECT TOP 1 isincode,
                                 sedolcode,
                                 classificationdate,
                                 other_column
                    FROM   dbo.pfportfolioholdingclassificationftid x WITH (
                           nolock)
                    WHERE  ( x.isincode > ''
                             AND x.isincode = b.breakdowncode )
                            OR ( x.sedolcode > ''
                                 AND x.sedolcode = b.breakdowncode )
                            OR ( x.sedolcode > ''
                                 AND x.sedolcode = b.sedolcode )
                            OR ( x.isincode > ''
                                 AND x.isincode = b.isincode )
                    ORDER  BY CASE
                                WHEN x.sedolcode = b.breakdowncode THEN 1
                                WHEN x.isincode = b.breakdowncode THEN 2
                                WHEN x.sedolcode = b.sedolcode THEN 3
                                WHEN x.isincode = b.isincode THEN 4
                                ELSE 5
                              END,
                              classificationdate DESC) k;

Not surprisingly I get a different plan than you, but the sort still has a high estimated cost. What if we split the APPLY into four parts and have each APPLY only return the primary key of the table? If we have a covering index for each APPLY then we can find the primary key of the matching row with at most four index seeks. No sorting is required. We can also skip doing the seeks that we don't need by adding filters into the APPLYs but that isn't guaranteed behavior. Here's one way to write it:

SELECT b.breakdownclassificationid,
       k.isincode,
       k.sedolcode,
       classificationdate,
       other_column
FROM   #breakdownset b
       OUTER apply (SELECT TOP 1 pk
                    FROM   dbo.pfportfolioholdingclassificationftid x
                    WHERE  x.sedolcode = b.breakdowncode
                    ORDER  BY classificationdate DESC) a1
       OUTER apply (SELECT TOP 1 pk
                    FROM   dbo.pfportfolioholdingclassificationftid x
                    WHERE  x.isincode = b.breakdowncode
                           AND a1.pk IS NOT NULL
                    ORDER  BY classificationdate DESC) a2
       OUTER apply (SELECT TOP 1 pk
                    FROM   dbo.pfportfolioholdingclassificationftid x
                    WHERE  x.sedolcode = b.sedolcode
                           AND a2.pk IS NOT NULL
                    ORDER  BY classificationdate DESC) a3
       OUTER apply (SELECT TOP 1 pk
                    FROM   dbo.pfportfolioholdingclassificationftid x
                    WHERE  x.isincode = b.isincode
                           AND a3.pk IS NOT NULL
                    ORDER  BY classificationdate DESC) a4
       LEFT OUTER JOIN dbo.pfportfolioholdingclassificationftid k
                    ON k.pk = COALESCE(a1.pk, a2.pk, a3.pk, a4.pk);

With 1.5 M rows in the temp table, the worst possible case should be 6 million nonclustered index seeks and 1.5 million clustered index seeks.

The query runs in two seconds on my machine. The runtime doesn't matter because I have nonsense data. However, there isn't a sort in the plan. I uploaded the query plan to Paste The Plan. You should consider doing that as well for future questions. Here's also a screenshot of the actual plan:

The query that you have does a nested loop join and doesn't go parallel. With LOOP JOIN and MAXDOP 1 hints the query finishes in seven seconds on my machine. Here's the plan and here's a screenshot:


MySQL: Do killed query results get cached?

Yes and no.

The Query cache will not see, much less keep, the partial result.  The page you referenced is talking only about the Query cache.  The QC is populated only after the complete result is built.  This gives a significant boost for a repeat of the identical query.  (Similar queries are not helped any by the QC.)

InnoDB's buffer_pool (and the equivalent in MyISAM) caches whatever blocks are read from disk.  Blocks that were read from disk before the killed was process will probably still be in RAM.  This will provide some performance boost for repeated executions of the same or similar queries, whether killed or not.


How do I prove that the act of removing foreign keys doesn't corrupt existing data?

Implementing this stuff at an app level is a nightmare. You and your team will have to test, double check and retest code which does EXACTLY the same thing that's been done by MySQL (for InnoDB) for MILLIONS of users over a period of YEARS.

Follow the discussion (one of the best threads I've seen on stackoverflow) here. With all due respect to you and your team, does your boss REALLY think that you can write bug-free RI (Referential Integrity) code that has significant functionality in less than 5 years? I certainly don't!

I can't tell you the number of times I've read on other forums (primarily Oracle) where some poor schmuck is crying his eyes out over an app he's inherited where RI was enforced at the app layer. Orphaned records, childless parents, inconsistent data...  the list goes on and on... data might as well be Swiss cheese, it's got that many holes! Tell your boss to have a read of this book by database professionals (Oracle experts, some of the best, most readable technical writing I've ever seen). From a review here - an outline of what Jonathan Lewis (a man who wrote a 530 page book on only (get this) the FUNDAMENTALS of the Oracle optimiser.

  Chapter 10: Design Disasters, by Jonathan Lewis

  More war stories, for fans of Chapter 8! "Now prepare yourself to read
  all about 'The World's Worst Oracle Project.'" - Jonathan Lewis.

  This chapter describes some of the most common mistakes in development
  Oracle database applications. You'll certainly recognise some of them,
  because so many people stubbornly cling to certain beliefs. I know I
  like to bring up several of his points when I get into common
  arguments like these:
  1. We want our application to be "Database Independent."
  2. We will check data integrity at the application level instead of taking advantage of Oracle's constraint checking abilities.
  3. We want to use sequences for our primary keys.

Take CAREFUL note of point 2! If your boss persists with this madness, then my advice to you is to run fast and run far! You will spend your days in a miserable hellhole of constant firefighting, and never be able to fulfill your potential as a developer or a DBA and you'll learn very little! Just a few thoughts!


Get all possible parent ids only if has parent bigger than 0

I have a way to do it in one query, though it may not be faster than your current solution.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_parent_id`(`id` INT) RETURNS varchar(1024) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE return_string VARCHAR(1024);
    DECLARE return_string_separator CHAR(1);
    DECLARE parent_id INT;

    SET return_string = '';
    SET return_string_separator = '';
    SET parent_id = id;

    WHILE parent_id > 0 DO

        SET parent_id = (
            SELECT
                forum_parent_id
            FROM
                forums
            WHERE
                forum_id = parent_id and forum_parent_id not in (Select forum_id from forums where forum_parent_id=0)
        );

        IF (parent_id > 0) THEN
            SET return_string = CONCAT(return_string, return_string_separator, parent_id);
            SET return_string_separator = ',';
        END IF;

    END WHILE;

    RETURN return_string;
END$$


Calling Sproc From Another Server - runs as SA

If it runs as sa on ServerB via a linked server when the user is logged into ServerA as a user login, then the sa account is configured in the linked server properties. If that's not what you want, then fix the linked server so that it uses the current security context instead.


Is it better to shrink a database or defragment to improve performance?

To improve performance would I be better off defragmenting the database or shrinking it?

Shrinking will not improve performance de-fragmenting the indexes should increase the performance, if that is really the reson behind the slowness. Shrinking is going to cause massive logical fragmentation so always bear in mind to avoid it. ONLY shrink when you have freed up large amount of space in SQL Server and you need that space at all cost. For all other reasons avoid shrinking

PS: Before going to shrinking at all I would first suggest you to find the cause of problem and Analyzing SQL Server Performance should get you started.


Adding a group of rows in postgresql based on column id

There is no natural order in a table. You need some indication which rows should go together. As discussed, a group_id per row could do the job.

Then the query becomes simple:

SELECT group_id, SUM(qty) AS qty, string_agg(equip_id, ', ') AS id
FROM   tbl
GROUP  BY 1;

If you already have your table and imported all the data, there is a trick. As long as you have not done anything to the table, yet, especially not updated or deleted any rows, chances are, the physical order of rows is still in the sequence like they were imported.

You could (ab)use the system column ctid as a poor man's id to indicate this sort order and build on this. Assuming there are exactly 3 rows per group:

SELECT (rn + 2)/3, SUM(qty) AS qty, string_agg(equip_id, ', ') AS id
FROM   (SELECT *, row_number() OVER (ORDER BY ctid) AS rn FROM tbl) t
GROUP  BY 1


Note that this only applies to Standard Edition.

Assume you have four sockets in the motherboard. If each of those sockets is populated with a CPU with more than six cores, SQL Server Standard Edition will not use the additional cores, but take care. It does something really ugly:

CPU 1: 8 cores (all used) - running total 8
CPU 2: 8 cores (all used) - running total 16
CPU 3: 8 cores (all used) - running total 24
CPU 4: 8 cores (none used) - you will still have to license these and the load will be unbalanced.

Another scenario, with two sockets:

CPU 1: 16 cores (all used) - running total 16
CPU 2: 16 cores (8 used) - running total 24

In this second scenario the cores are also unbalanced (meaning it won't spread the load evenly across NUMA nodes), and you'll still have to pay for the 8 remaining cores even though you can't use them.

If you have no say in the purchasing of hardware, you can mitigate this balancing problem by running Standard Edition in a virtual machine, and assigning the right number of vCPUs accordingly.


How to list all grants per user/role on PostgreSQL

While the following is not a complete solution (column privs aren't included, it doesn't have the function signatures) you should hopefully be able to get most of what you're asking for using:
SELECT rug.grantor,
        rug.grantee,
        rug.object_catalog,
        rug.object_schema,
        rug.object_name,
        rug.object_type,
        rug.privilege_type,
        rug.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_usage_grants rug
    WHERE rug.object_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rtg.grantor,
        rtg.grantee,
        rtg.table_catalog,
        rtg.table_schema,
        rtg.table_name,
        tab.table_type,
        rtg.privilege_type,
        rtg.is_grantable,
        rtg.with_hierarchy
    FROM information_schema.role_table_grants rtg
    LEFT JOIN information_schema.tables tab
        ON ( tab.table_catalog = rtg.table_catalog
            AND tab.table_schema = rtg.table_schema
            AND tab.table_name = rtg.table_name )
    WHERE rtg.table_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rrg.grantor,
        rrg.grantee,
        rrg.routine_catalog,
        rrg.routine_schema,
        rrg.routine_name,
        fcn.routine_type,
        rrg.privilege_type,
        rrg.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_routine_grants rrg
    LEFT JOIN information_schema.routines fcn
        ON ( fcn.routine_catalog = rrg.routine_catalog
            AND fcn.routine_schema = rrg.routine_schema
            AND fcn.routine_name = rrg.routine_name )
    WHERE rrg.specific_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND grantor <> grantee
UNION
SELECT rug.grantor,
        rug.grantee,
        rug.udt_catalog,
        rug.udt_schema,
        rug.udt_name,
        ''::text AS udt_type,
        rug.privilege_type,
        rug.is_grantable,
        null::text AS with_hierarchy
    FROM information_schema.role_udt_grants rug
    WHERE rug.udt_schema NOT IN ( 'pg_catalog', 'information_schema' )
        AND substr ( rug.udt_schema, 1, 3 ) <> 'pg_'
        AND grantor <> grantee ;


If a transaction is "committed" , is it then saved for sure?

If a client application has issued a COMMIT and got back a success code then the transaction is guaranteed by the engine to be durable. It is guaranteed that all changes done in a transaction are going to be visible, even after a crash. Furthermore the recovery also guarantees that any transaction not committed will be rolled back in case of a crash.

For more details I recommend reading the ARIES paper.

Write-ahead logging and recovery cannot make guarantees if the underlying storage hardware has faults (corruption). And any engineering product may contain defects.

Your application, like all other applications, needs to be carefully written to behave correctly in presence of crashes (errors). There is no magic.


[Note: I got to the end of writing this and noticed that sometimes you refer to the data field as image and sometimes as attach_data.  You'll need to take that into account as you read this.]

You might try breaking up the update statement into multiple batches with TOP:

UPDATE TOP (5) messages SET attach_data = 0x
WHERE ins_date < DATEADD(DAY, -120, GETDATE())
AND DATALENGTH(image) > 0;

Then you'll need to modify your maintenance window to execute this query repeatedly until no records are returned.  You also might want to use a static date parameter specified by the application since the dates will create a moving target as you re-run this query.

Note that this method will cause the maintenance process to take longer to process overall, but each batch should complete more quickly and therefore you shouldn't be blocking reads as long.  You can make the whole process faster by increasing the size of the TOP, but each transaction will lock longer.  It's a tradeoff you'll have to play with.

Note also that you can't easily specify an order the records will be updated here since UPDATE doesn't support ORDER BY; you'll just have to keep repeating it until it finishes.

If that doesn't work for you, then there are some optimizations you could make.  I would guess that part of the issue is that DATALENGTH() is not a very fast function since it has to read the data.  Do you really need to test data length here?  Is what you're doing significantly different than this:

UPDATE messages SET attach_data = 0x
WHERE ins_date < DATEADD(DAY, -120, GETDATE())
AND attach_data <> 0x;

If you must use DATALENGTH() or the above doesn't work, its worth noticing that it is deterministic.  That means you can index it.  It may be worthwhile to create an indexed view, or a computed column in the table which has DATALENGTH() that you can then index.

Something like this:

CREATE VIEW messages_maintenance
WITH SCHEMABINDING
AS
SELECT id,
    ins_date,
    DATALENGTH(image) AS "image_datalength"
FROM messages;

Followed by a unique clustered index on id, and then a nonclustered index on image_datalength, ins_date.  Then you can run:

UPDATE messages SET attach_data = 0x
WHERE EXISTS (SELECT 1 FROM messages_maintenance
        WHERE messages_maintenance.id = messages.id
        AND messages_maintenance.image_datalength > 0
        AND messages_maintenance.ins_date < DATEADD(DAY, -120, GETDATE()));

Adding a computed column and an index on that would be similar.

Note that this method does add a number of indexes that now have to be maintained by the DB, so it isn't free.


how long can CU and SP be different across a SQL Server Always on Cluster nodes

Does Microsoft support running SQL Always On clusters for a short period of time on different CU and/or SP levels. for a week or a couple of days?

This is not an official statement but I got this from my MS friend.

It would be supported subject to condition both CU and SP are supported. But the risk and burden lies on you. I have worked with such environment for very short period, to be precise for 4 days and after that I made sure all the replicas are on same SP/CU level. If you really need it, I would suggest to try to limit this to couple of days to be on the safer side.

The correct approach would be to first test the SP/CU on UAT and then if you are happy proceed on production. But sometimes due to constraints this may not be possible. If replicas are on different SP/CU failover may cause primary replica to be on node which is not at latest SP/CU and you may see issues which was fixed in recently released SP/CU so this is very risky. The solution here is to keep such environment as short as possible.


Where does SQL Server physically store the IDENTITY VALUE for a table?

If you can access the DAC (Dedicated Administrator Console), you can inspect the value of the identity column, for INT columns, by looking at the idtval column in sys.syscolpars.

Thanks to Martin Smith for directing me to that table via this very useful answer by Roi Gavish on a related question here.

Take, for instance, the following temporary table:

USE tempdb;

CREATE TABLE #d
(
    ID INT NOT NULL IDENTITY(1,1)
);

TRUNCATE TABLE #d;

DBCC CHECKIDENT ('#d',RESEED, 2147483635);

INSERT INTO #d DEFAULT VALUES;

Let's see what the table contains:

SELECT *
FROM #d;

+------------+
| ID         |
+------------+
| 2147483635 |
+------------+

The identity value can be inspected by this code:

DECLARE @idtval VARBINARY(64);

SELECT @idtval = scp.idtval
FROM sys.syscolpars scp
    INNER JOIN sys.objects o ON scp.id = o.object_id
WHERE o.name LIKE '#d____%'

DECLARE @LittleEndian NVARCHAR(10);
SET @LittleEndian = LEFT(sys.fn_varbintohexstr(@idtval), 10);
SELECT @LittleEndian;
DECLARE @BigEndian NVARCHAR(10) = '0x';
DECLARE @Loop INT = 0;
WHILE @Loop < 4
BEGIN
  SET @BigEndian = @BigEndian + SUBSTRING(@LittleEndian, ((4 - @Loop) * 2) + 1, 2);
  SET @Loop += 1;
END
SELECT CurrentIdentityValue = CONVERT(INT,
    CONVERT(VARBINARY(32), @BigEndian, 1), 2);

+----------------------+
| CurrentIdentityValue |
+----------------------+
|                      |
| 2147483635           |
+----------------------+

For BIGINT identity columns, we need to expand the size of some variables used in the code, such as:

CREATE TABLE #dBig
(
    ID BIGINT NOT NULL IDENTITY(1,1)
);

TRUNCATE TABLE #dBig;

DBCC CHECKIDENT ('#dBig',RESEED, 9223372036854775704);

INSERT INTO #dBig DEFAULT VALUES;

SELECT *
FROM #dBig;

DECLARE @idtval VARBINARY(64);

SELECT @idtval = scp.idtval
FROM sys.syscolpars scp
    INNER JOIN sys.objects o ON scp.id = o.object_id
WHERE o.name LIKE '#dBig____%'

DECLARE @LittleEndian NVARCHAR(18);
SET @LittleEndian = LEFT(sys.fn_varbintohexstr(@idtval), 18);
DECLARE @BigEndian NVARCHAR(18) = '0x';
DECLARE @Loop INT = 0;
WHILE @Loop < 8
BEGIN
  SET @BigEndian = @BigEndian + SUBSTRING(@LittleEndian, ((8 - @Loop) * 2) + 1, 2);
  SET @Loop += 1;
END
SELECT CurrentIdentityValue = CONVERT(BIGINT,
    CONVERT(VARBINARY(32), @BigEndian, 1), 2);

Results for the BIGINT:

+----------------------+
| CurrentIdentityValue |
+----------------------+
|                      |
| 9223372036854775704  |
+----------------------+


How to insert rows for each of the type column?

You absolutely do not want to change the id for any row when inserting new data.
Your Primary Key (id) value should be set when each record is created, remain the same throughout the entire lifetime of that record, right up to the point where that record is finally destroyed.
Imagine if banks "renumbered" people's bank accounts every time someone [else] closed their account.  There would be chaos.
If the combination of "choice" and "type" is unique, then that could form a Natural, Composite, Primary Key for this table, removing the need for the id column entirely.
YMMV.
Alternatively, just let your database allocate ids to the new records - their actual values do not matter, just that they are unique.
I do not believe you need the order column either.  From the data you show ...
select
. . .
order by type, choice

... would achieve exactly the same thing, without the need to update anything as data is added or removed.


Data Corruption on base backups created with wal-e?

I've experienced this problem with wal-e up to 0.9.2. Manually creating the pg_subtrans directory seems to have got replication working again.


Product Attribute System While Avoiding EAV

Your question looks like an instance of a very widespread pattern.  This pattern goes by the name "generalization and specialization" in Extended ER modeling.  It also goes by the name "classes and subclasses" (or "types and subtypes") in object modeling.

When designing an object oriented data structure, the concept of inheritance plays a key role in the design.  When designing a relational data model, the designer is faced with the reality that the relational data model does not include the concept of inheritance.

So the designer is forced to implement inheritance through a workaraound, by designing tables that work more or less the way inheritance works.  This situation has been studied before, and some fairly good designs have been used with fairly good results.

Two of these designs go by the names "Single Table Inheritance" and "Class Table Inheritance".  These two designs are well presented by Martin Fowler in one of his books.  You can see a summary at these two web pages:

http://www.martinfowler.com/eaaCatalog/singleTableInheritance.html
http://martinfowler.com/eaaCatalog/classTableInheritance.html

You can choose which one depending on the specifics of your case.

In conjunction with class table inheritance, there is a technique known as Shared primary key. Shared primary key involves defining a key to the subclass tables that is both a primary key and also a foreign key that references a row in the class table.  This does two things:  it enforces the one-to-one nature of these relationships and it makes the relevant joins simple, easy, and fast.

You can see this technique illustrated over in the StackOverflow area:

https://stackoverflow.com/questions/tagged/shared-primary-key

These three designs or techniques are not perfect, and they require a little work on the part of the application programmer.  But they go a long way to meeting a very commonplace need.


Set Maximum Server Memory Higher Than What is Available to OS

Presumably you have the "Standard" edition of Windows Server 2008 R2.

You'll need to move to the Enterprise Edition (or DataCenter) of Window Server 2008 R2 to utilize more than 32GB, regardless of the "max server memory" setting in SQL Server.

Windows memory limitations can be seen on MSDN here.

Keep in mind, SQL Server also has memory limitations by edition.  SQL Server 2008 R2 Standard Edition limits memory to a maximum of 64GB.


Multi-column uniqueness constraint, checking a field has one of two values

You need to remove the status column from your index:

CREATE UNIQUE INDEX builders_unique_house
   ON houses (builder_id)
WHERE status IN ('PLANNING', 'CONSTRUCTING');

If you keep the status column, the index will contain e.g. ('PLANNING', 1) and ('CONSTRUCTING', 1) - that combination is unique.

If you remove the status, only the builder_id will be indexed, but only if one of the status values are set. And then trying to index "the other" status will lead to the desired unique index violation.


SSIS flat file import - Issue with datetime format

Try adjusting your input column type to DT_DBTIMESTAMP2.  I've seen this fix similarly annoying Date conversion issues.  Here's a full list of what those input types mean.


Deciding which MySQL execution plan is better

Multiplying the rows is invalid for several reasons:

Many times, the rows examined are an approximation (based on statistics, not accurate), good for query plan selection, but not for performance calculation
The total number of rows examined on a nested loop join (A, B) is not rows_examined_on_table_A * rows_examined_on_table_B, but rows_examined_on_table_A + rows_returned_from_table_A * rows_examined_on_table_B. Where clauses can make a huge difference on that, although it is true that the mentioned calculations is many times used as a broad approximation, assuming the indexes are being created properly and the main causes of filtering out results.
Modern MySQL versions do not use always a nested loop join approach for executing joins and subqueries. Check 5.6 subquery optimizations and other optimization documents on the same manual. Additionally, some of the new optimization techniques do not modify the predicted examined rows, which at some times can be way lower than the one printed, even if it has been calculated exactly.

In particular, on your first query, you are hitting a well know MySQL bug? limitation?  in which an IN subquery is identified as a DEPENDENT SUBQUERY, even if it really isn't, forcing the outmost query to be executed without an index (full table scan) in order to test all possible values of the first table. That is usually an indicator that it is a bad query. It seems not to bee too bad in this case, as the table is small, but it is usually an indication of bad performance.

The other thing that should bring your attention is the Using temporary; Using filesort. Filtering is not the only thing where you should focus, as these extra pieces of information are telling you that a large sorting has to be done using a temporary table (that may or may not end up on disk, but at least has to be materialized). That is another indicator of potential bad performance, that in some cases can be avoided with the right indexes.

I will not tell you which is the right query to use (partially, because I do not know all the variables: indexes, tables structure, etc., and in most cases it will depend on the particular hardware/resources available), but I will tell you the tools to decide:

Profile the query- obtain the post execution times and how much of it it is being invested in what. You can use SHOW PROFILES up to 5.5, and the performance_schema starting with 5.6.
As time can be sometimes variable (for example, depending on other queries being executed at the same time, depending on the buffer pool contents) Obtain post-execution statistics with SHOW SESSION STATUS. In particular:

FLUSH SESSION STATUS;
SELECT ... ;
SHOW STATUS like 'Hand%';

will give you the exact number of handler calls done (approximately, the number of rows read and written for that particular query- although that is not 100% accurate, as it depends on the particular engine implementation).

You may also want to monitor other status variables, like the created temporary tables, created temporary tables on disk and sort passes/sorted rows.

All of these will give you post-execution, exact, time-independent parameters to evaluate the performance of a query. Percona even has a patch for the slow log to output that information on the logs instead of using performance_schema.

With those extra pieces of information you will be able to evaluate more objectively which query is better, and not relying exclusively on EXPLAIN, which only provides limited pre-execution information.


need to use PLSQL to insert distinct rows

If the duplicates are being introduced by the inserts from Table A, then it seems a MERGE would be in order.  A MERGE can INSERT data when the record does not exist in Table B and UPDATE data if the record already exists thus preventing the introduction of duplicates.

FrustratedWithFormsDesign is correct that you probably should have a constraint preventing the introduction of duplicates.  He is also correct in pointing out that this code would probably benefit from Bulk processing.

Phil is correct that if you only need to INSERT data, then a subquery pruning rows from the insert would be preferable.


Two instances sharing same system database?

This happened to me once when I restore system database of SQL 2014. When I investigate further, the 2 different name was pointing to same physical file and from @@version I can see both having similar naming.
The database which I restored was pointing to another database and it got fixed when I revert back from single user mode to multi user.


Single Domain Controller for SQL Server Always On Availability Group

It depends on what your goal is.

Technically, SQL Server will work just fine in a Availability Group or FCI installation with a single domain controller. I have this set up in my lab and it works without issue. In this situation, you will be fine.

Generally, the design diagrams you see are splitting things between sites. So you have a DC and a SQL node in each site. If Site 1 fails, you can fail over to Site 2 and still have authentication still work. If you have only one DC, and the site where the DC is offline, then authentication will fail causing issues for your users.


RESTORE DATABASE dbname
FROM DISK = 'dbname .bak'
WITH REPLACE, RECOVERY --force restore

or just

RESTORE DATABASE dbname WITH RECOVERY

the REPLACE Overwrite the existing database, do it only if you are sure you want to override your existing database as you mentioned you dont care to delete it

RESTORE WITH RECOVERY is the default behavior which leaves the database ready for use by rolling back the uncommitted transactions. Additional transaction logs cannot be restored.

That should bring the database online. Then you can delete it & try again.


What is a Storage Definition Language (SDL)?

You may want to ask your teacher to define "SDL".  I don't think the term appears in the SQL standard.  I found this reference (see page 3), and I disagree: SQL does not define the physical structure, the physical bytes per field, the physical field order, the physical sorting, or "mixed records", whatever that might mean.  SQL doesn't define fields at all, or records, or say anything about how the data are stored on the disk if they're stored on the disk.

That said, every vendor does provide some means for the administrator to define where the data will be kept, and many offer varying levels of control over physical storage aspects (e.g. ISAM, clustered indexes).  AFAIK all that stuff lies outside the SQL standard.  I've used DBMSs that understand SQL DML, but completely separate mechanisms for defining tables and storage.


Database creation on Linux with dbca: "command not found"

Regarding your first error ('dbca: command not found'):

ensure ORACLE_HOME is set correctly
ensure $ORACLE_HOME/bin is in your path
run the command as oracle user

If the error persists, check the contents and permissions of $ORACLE_HOME/bin.


First of all, here is a Pictorial Representation of InnoDB (courtesy of Percona CTO Vadim Tkachenko)

Strictly in terms of InnoDB, there is only lose data on power failure if certain settings as disabled.
There are two parts of the InnoDB Storage that, if disabled, can cause data loss on power failure.
Double Write Buffer
A record of all data and index pages that are supposed to be written back to the physical files of InnoDB table are written in the Double Write Buffer beforehand. The Double Write Buffer is contained with the system tablespace, better know as ibdata1. That way, in the event of a crash, the InnoDB Storage Engine has something to refer to during crash recovery. To ensure that the Double Write Buffer's pages are there for you after a MySQL crash, always leave innodb_doublewrite set to 1 (default) and do not start mysqld with --skip-innodb_doublewrite.
Transaction Log Flushing
The Log Buffer is used to collect changes to be written to the Transaciton Log Files (ib_logfile0, ib_logfile1). The default flushing behavior (innodb_flush_log_at_trx_commit = 1) is to  flush the contents of the InnoDB log buffer to the log file at each transaction commit and the log file flushed to disk. Some change innodb_flush_log_at_trx_commit to 0 or 2 to increase InnoDB write performance. However, changing innodb_flush_log_at_trx_commit to anything other than 1 makes it possible to lose 1 second's worth of transaction upon an OS crash or power outage.
Your Comment
The only way to see data that is not committed would be to start your transactions with READ UNCOMMITTED isolation and never run COMMIT. You should always commit your transactions. Days of rollback info sounds like an abuse of InnoDB, your data, and your application.
SUMMARY
Although these are the default settings, you should make sure these settings are in place
[mysqld]
innodb_flush_log_at_trx_commit = 1
innodb_doublewrite = 1
auto_commit = 1


Select only those records that have different/multiple values for a particular column

You could do something like:

select distinct x.id, x.lastname, x.firstname, x.email
from t as x
join (
    select id
    from t
    group by id
    having count(distinct email) > 1
) as y
    on x.id = y.Id


Fastest MySQL pagination and ORDER from a huge UNION ALL VIEW

Rethink the need for getting the total count; it is probably the most costly part.
Time each of the two queries separately.  Optimize them separately.  Then re-time.
Do you really need all (*) columns?  TEXT and BLOB columns extract an extra toll.
There is no advantage in having a VIEW; instead there is a big disadvantage in that the UNION will be run twice!  (In MySQL 8.0, WITH might avoid this 'twice'.)
For the query with the LIMIT, you can and should do the limit on each subquery.  More details here .
Each table needs INDEX(column1) .

This should significantly speed up (more than 2X) the big count:

SELECT  ( SELECT COUNT(*) FROM A.month )
      + ( SELECT COUNT(*) FROM A.year )
      + ( SELECT COUNT(*) FROM B.month )
      ... ;

This is because COUNT(*) is faster than either the gathering of all the rows into a temp table or the counting of rows in that table.

I predict that the result of all my suggestions will get it under 5 seconds.  If not, please provide SHOW CREATE TABLE for each table.  And what MySQL version you are using.

Paginate through 10K rows??

Let approach your problem from the other side.  If I calculate correctly, there are about 10,000 pages in your output?  What user can page through that many pages before dying if old age??

If you have more than a few dozen pages, then, for the sake of the users sanity, come up with another way to show the data to them.  Pagination is not practical.

Let me provide a simple example -- Paginating through all a list of all cities in the world...

Plan A, like pagination...  First have the user pick what country.  (Even that is clumsy since there are about 250 countries.)  Then pick a province/state/canton/etc.  Then pick A..Z, then page through the cities.

Plan B, moving farther afield...  Provide some "search" mechanism(s) -- for searching by part of the name, by country/state/province/etc, by population range, by part of the world, etc.


It's not possible to modify table via trigger.

So I have 2 options:

Add partition via cron.
Add partition via code (check default partition).


What are the default settings for accessing MySQL remotely?

You will have to resort to removing remote access from the MySQL users themselves.
To find out who has remote access, run this
SELECT user,host from mysql.user
WHERE host NOT IN ('localhost','127.0.0.1');

This will show you everyone that can login to MySQL from specific access points as well as broader network specifications.
You could run the utility mysql_secure_installation which do the following:

You can set a password for root accounts.
You can remove root accounts that are accessible from outside the local host.
You can remove anonymous-user accounts.
You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_. (I posted a question and answer about this weird aspect).

UPDATE 2013-07-02 13:11 EDT
I see you have skip-networking in the my.cnf and you removed bind-address=127.0.0.1. I don't think the two options belong together. Why?
The option skip-networking disables TCP/IP. Using 127.0.0.1 as a bind-address implies TCP/IP. The two can never really mix. They are meant to be mutually exclusive. Use one or the other, not both at the same time. This makes sense because when you read the MySQL Documentation for skip-networking and bind-address, one does not mention the other.


Can I tell postgres to use a different "collation" for an integer column?

You can use a CASE expression for the ORDER BY:

order by case the_column
            when 0 then 0
            when 3 then 1
            when 1 then 2
            when 4 then 3
            when 2 then 4
            else 5 --- everything else
         end


Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode

This may produce some false positives (e.g. you may have *= in a comment), but should be a good start:

SELECT
  obj = QUOTENAME(SCHEMA_NAME(o.[object_id]))
      + '.' + QUOTENAME(o.name),
  o.type_desc
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.[object_id] = o.[object_id]
WHERE m.definition LIKE '%=*%'
OR m.definition LIKE '%*=%';


How to add function to particular column in while creating table in Postgres?

There is serial and bigserial numeric type to autogenerate numbers i.e. autoincrement.
If you want to have custom function then you need to create trigger functions first and assign it to the trigger on insert.

More in the postgresql docs:
http://www.postgresql.org/docs/9.4/static/datatype-numeric.html


Use variables in filename with INTO OUTFILE (timestamped filename)

Has said in this post, you can use:

SET @sql_text =
   CONCAT (
       "SELECT * FROM `project ` into outfile 'C:\desktop\"
       , DATE_FORMAT( NOW(), '%d/%m/%Y')
       , ".csv'"
    );

PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;

Thanks Haim Evgi for the original answer.

Max.


how to find oracle calculated/computed column list using data dictionary views

The column USER_TAB_COLS.virtual_column = 'YES' indicates if it's a virtual column.

You can get the expressions for each column with:

SELECT *
FROM user_tab_columns
WHERE data_default IS NOT NULL;


What does "Deleting unrecoverable checkpoint table row" mean?

This is my assumption based on the log entry -

If you are using Memory optimized tables, there are Checkpoint File Pair (CFPs).

  CPFs transition through several states before they can be deallocated. Database checkpoints and log backups need to happen to transition the files through the phases, and ultimately clean up files that are no longer needed. For a description of these phases, see sys.dm_db_xtp_checkpoint_files

The log entry refers to deallocation by garbage collection process.


What is the best permissions to set for only creating and managing own databases?

If you grant the dbcreator server role to a login, that login can create databases.  Databases created by a login also (unless changed as part of the process) will be owned by that login, meaning that the login will be a member of the db_owner role for that database and have full rights to that database and all objects within it.

The primary issue here is that by granting the role, you will have no control over when and how the login creates databases, nor do you have any control on how many databases that login creates.  A login with dbcreator can create as many databases as there is space for.  My recommendation is that you create the database for the login and then grant that login db_owner rights in their database.  You can then grant the login db_datareader in the databases that it doesn't/shouldn't own.  This gives you better manageability along with accomplishing your stated goal.


Replication from Enterprise to Standard edition

Yes, you can replicate from Enterprise edition to Standard edition.

The only caveat is Peer-to-Peer Replication requires all nodes to be Enterprise Edition, and Web and Express editions are Subscriber only.

Features Supported by the Editions of SQL Server - Replication


How to return "NaN" using SQRT function in Oracle SQL?

Cast the number, as you said:

SELECT SQRT(4) AS "SQRT of 4",
   SQRT(0) AS "SQRT of 0",
   SQRT(TO_BINARY_DOUBLE(-1)) AS "SQRT of -1"
FROM DUAL;


Can I query a copy of a database without impacting performance of the primary?

You can do something like log shipping. Assuming you are already taking full and log backups on the primary server then using those backups to maintain a mostly-fresh copy of the database elsewhere will not impact the primary server at all (unless the storage for the backups is the same - which is a no-no anyway). You cannot access the database while logs are restored, but assuming you know when you'll be running your query bursts, you can schedule restore/standby operations outside of those windows (just know that the data won't always be in sync with the production copy, depending on the last log restore).

Your reporting copy can also serve as a warm standby to recover data in the event that the vendor or someone else makes a mistake in production - depending on the log shipping delay you can recover that data from before that mistake without recovering the entire database. I've seen this methodology used in several places, especially where a lot of cretins had ad hoc access to the database. :-)


Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?

According to the MySQL Documentation

When a client uses INSERT DELAYED, it gets an okay from the server at once, and the row is queued to be inserted when the table is not in use by any other thread.
Inserts from many clients are bundled together and written in one block (This consumes memory). This is much faster than performing many separate inserts.
The queued rows are held only in memory until they are inserted into the table. This means that if you terminate mysqld forcibly (for example, with kill -9) or if mysqld dies unexpectedly, any queued rows that have not been written to disk are lost.
INSERT DELAYED is slower than a normal INSERT if the table is not otherwise in use. There is also the additional overhead for the server to handle a separate thread for each table for which there are delayed rows (This consumes memory).
For MyISAM tables, if there are no free blocks in the middle of the data file, concurrent SELECT and INSERT statements are supported. Under these circumstances, you very seldom need to use INSERT DELAYED with MyISAM. This is definitely the case for a MyISAM table that experiences no DELETEs and UPDATEs.

Based on these excerpts from the MySQL Documentation, I would like to suggest the following options:
OPTION 1
Change your target table from MyISAM to InnoDB. This will eliminate the need to use INSERT DELAYED altogether. With InnoDB using MVCC for ACID compliance, the InnoDB log files will better protect data in the events of a crash via MySQL calling for InnoDB to do crash recovery. You will need to tune InnoDB accordingly.
OPTION 2
If you cannot switch the table to InnoDB, you must go with extended INSERTs. However, you must adjust bulk_insert_buffer_size (its default is 8M) to a much larger to compensate using extended INSERT.
Either way, you need to stop using INSERT DELAYED like yesterday.


The ?'s are Parameter markers. Read abut them here https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.apdv.sqlpl.doc/doc/c0020295.html

I would suggest you add an index on column AB

Also consider using IBM Data Server Manager to monitor you database. It includes the ability to see explain plans and also invoke the design advisor


When I change the content of VARCHAR(32) from 32 chars to 0, will it save some space?

You are leaving the datatype at VARCHAR(32), correct?  But updating the value to be the empty string, correct?

A 32-char hash takes 33 bytes -- 1 byte length plus 32 hex characters (1 byte per character).

The empty string will take 1 byte -- 1 byte length, plus no space for any characters.

However...  You are deleting stuff out of the middle of blocks in the middle of the table.  So don't expect that zapping a million hashes will necessarily shrink the table by 32MB.  There are too many things going on with blocking, free space, reuse of free space, etc, to say anything more than "it will free up some space for future usage".

I don't know where you got the 1KB info in that link -- the length is 1 byte, not 1 kilobyte.


PostgreSQL not starting correctly, is it repairing itself first?

I had problems and many postgresql processes were stuck so I killed them with a -KILL...

Don't do this. It won't cause data corruption, but as you've discovered it forces the whole database system to restart and do crash recovery.

If you hard-kill any database back-end with SIGKILL (kill -9) the postmaster has to assume that shared memory might be corrupted and it has to kill and restart all workers, doing crash recovery as if the server its self had crashed and restarted.

You should not need to SIGKILL a backend. Use regular SIGTERM to ask it to stop what it's doing and exit - or better, use pg_terminate_backend(...) from within SQL. If the back-end doesn't respond, SIGQUIT should force it to terminate.

If you do SIGKILL, crash recovery usually takes a few seconds; minutes for a really big and busy databases. However, if you have a long checkpoint_timeout and huge checkpoint_segments you can accumulate a lot of work that must be done before the DB can become available again. If you have quite slow disk I/O this will be even worse.

PostgreSQL does produce logs while it's in recovery, and they're at a log level that makes it unlikely that they'd be suppressed. So it's hard to say what could be going on. Maybe you were looking at syslog, but your PostgreSQL install is configured to log directly to files in /var/log/pgsql or in the pg_log directory in the datadir?

(For anyone else reading this, never SIGKILL the postmaster then delete the postmaster pid file and restart PostgreSQL while there are still old postgres back-ends running. That can cause data corruption, since you've disabled every safety measure PostgreSQL puts in place to stop you from starting a new postmaster while old backends might still be running.)


Migrating Production SQL Servers (Availability Group) from one data center to another - questions

(you have lot of sub questions as part of main question .. will get straight to answering your questions ... )

  if deploy server 4 and server 5 at dc2 as SQL Servers 2017, and add them to existing AG as secondaries, will they be kept synchronized with primary (server 1) without any problem, despite difference in SQL Server and Windows versions ?

Yes, they will ... BUT you will have limit on number of replicas - synchronous with automatic failover :

SQL Server 2012 AG Limits: 1 primary replica, 4 secondary replicas, 2 of the replicas can be synchronous.

So practically, if you were to add additional replica in your current topology, you would be able to add only 1 server (2 you have in DC1, 1 cloud and 1 in DC2 (either server 4 or 5) - this will make 4 replicas which is max).

You could then pre-stage server 5 in DC2 to be logshipped. SO when you cutover to server 4, you can easily join server 5 dbs to AOAG without taking full & log backup of your databases.

  will adding server 4 and server 5 to AG and then manual failover to server 4 to be new primary (when we are ready to switch applications to use server 4 / listener new IP as production) work fine ? And then server 4 and server 5 will be synchronizing, and server 1,2,and 3 will break and stop syncing ? Is my understanding correct ?

See my answer 1. Once you failover to 2017, you wont be able to switch back to 2012. Remember that it will have the same old IP if listener. You can technically add 2nd listener (as client access point) with an OR dependency of your AG on both the listeners. This way once you decom you old listener, your AG will not go offline.

  are there any better options of migrating production from dc1 to dc2 with short downtime?

You should explore setting mirroring between DC1 and DC2 leaving the AlwaysON bit separate. This way, you just cutover from DC1 to DC2 and point your application to the new listener in DC2. This will be more staright forward and easy to rollback since both DC1 and DC2 are decoupled and failover and failback (point to old DC1) will be more manageable.

This is an upgrade as well (2012 --> 2017). I have written very detailed steps in my answer here.

  is it OK to deploy server 4 and server 5 as virtual machines in dc2 ? Or it is better to buy dedicated physical machines ? We want a potential automatic failover from server 4 to server 5 to work perfectly in future. Can having SQL Servers virtualized introduce potential problems with automatic failover function?

I would highly suggest to be a 1-1 match. If old servers are physical, go with physical with same or more resources (CPU, RAM and Disk (match mountpoints, folder layout, etc)). Physical vs Virtual is a whole different debate with many facets and it would be unfit in the current scope of the question.

Note: If you still have questions, I would highly suggest hire a professional as you might run into issues if you do not do a dry-run  end-to-end migration test.


Query that only uses index takes long time to execute

It may just be taking a while to return to the client.  How long does it take if you run as SELECT INTO, eg

SELECT ID, FirstName, LastName
INTO #tmp
FROM Customers


When inserting the varchar value into the datetime field, it throws conversion error

Since you are not shared the sample data of the Gradd19 value, I suspect the date value is in the yyyy-dd-mm format, so when trying to convert (month value more than 12) it cause the out-of-range value error.

For sample, if the date format in yyyy-mm-dd, it will execute properly:

DECLARE @FromDate DATETIME;
DECLARE @Gradd19 AS VARCHAR (20) = '2016-11-15';
SELECT @FromDate = CONVERT(VARCHAR, @Gradd19, 105);
SELECT @FromDate;

Incase if it is in yyyy-dd-mm it will throw out-of-range value error

DECLARE @FromDate1 DATETIME;
DECLARE @Gradd191 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate1 = CONVERT(VARCHAR, @Gradd191, 105);
SELECT @FromDate1;

So in that case you need do one more conversion to skip the issue, like the below:

DECLARE @FromDate2 DATETIME;
DECLARE @Gradd192 AS VARCHAR (20) = '2016-15-11';
SELECT @FromDate2 = CONVERT(VARCHAR(19), CONVERT(DATETIME, @Gradd192, 105), 120)
SELECT @FromDate2;

So instead of

CONVERT(VARCHAR, Gradd19, 105) AS ExpiryDate

you need to use

CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120) AS ExpiryDate

Update: As you mentioned in the comments the Gradd19 having the values of 20 and 60, it is difficult to filter those non-valid data. But based on the LEN() function you may avoid the non-valid data, then using the CONVERT you can get the VARCAHR field as DATETIME value.

Based on your sample data the valid date's minimum length is 10 characters, so you can apply the condition in the WHERE clause.

Sample execution:

DECLARE @GradedDETAILS  TABLE (Gradd19 VARCHAR (30));

INSERT INTO @GradedDETAILS (Gradd19) VALUES
('Sep 16 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('Sep 19 2016 12:00AM'),
('Sep 19 2016 12:00AM'),
('Sep 16 2016 12:00AM'),
('20'),
('60'),
('20-09-2016'),
('20-09-2016');

SELECT CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)
FROM @GradedDETAILS
WHERE LEN(Gradd19) >= 10

So in your actual query also you need to add the LEN(Gradd19) >= 10 condition and in the SELECT use this to convert as DATETIME by CONVERT(VARCHAR(19), CONVERT(DATETIME, Gradd19, 105), 120)


Does Oracle Partition by Reference optimizes join queries

I think the answer to your question is "Yes".

The Oracle optimizer understands reference partitioning. See the Oracle documentation at http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_avail.htm#BJECHCAG

Also see http://www.orafaq.com/node/2591


PostgreSQL - how to rename CamelCase column?

Turns out that using double quotes works:

ALTER TABLE mytable RENAME COLUMN "camelCaseColumn" TO camel_case_column;


How does Transparent Data Encryption actually work?

OK, In case anyone wants to know, I got to the bottom of this.
The chain is pretty easy to work out:

Create Service Master Key (automatically done upon install)
Create Database Master Key (user creates their own key)
SymetricEncrypt(Service Key,Database Master Key) -> Store Encrypted Master Key into Master DB
Create Certificate
SymetricEncrypt(Database Master Key, Certificate Private Key) -> Store Encrypted private key in Master DB
Create Database Encryption Key (system creates a random key, but must be manually added to database)
ASymetricEncrypt(Certificate, Database Encryption Key) -> Store Encrypted DEK into database.

So, to decrypt the database data, you'll need to:

Use the SMK to decrypt the DMK
Use the DMK to decrypt the cert private key
Use the certificate to decrypt the DEK
Use the DEK to decrypt the database

The starting point is the Service Master Key... how is this stored (if you encrypt it, you can't retrieve data, and if left in plaintext your system is exposed).
The answer is, the SMK is encrypted with Data Protection API, which is a service built into windows.
Basically, data is encrypted with "secrets" from your user profile. So the same user profile must be used to decrypt the SMK.
Now that I know this, I can use DPAPI in my applications to encrypt my encryption keys :)


Create table on a Master - Master config avoiding replication

The safest approach is to stay far, far away from trying to filter replication... and tweaking sql_log_bin on and off is a recipe for replication problems, too.  In a replicated environment, the general idea is that both servers have identical data sets, and to whatever extent they don't, your setup becomes exponentially more complex.

There is an easier and safer approach.

First, create the table, with the storage engine you want, on the server where you want it to store data.

Then, on the other server (where you don't want the table to store data), verify that the definition has replicated and that the table exists, and then:

mysql> SET sql_log_bin = 0;
mysql> ALTER TABLE t1 ENGINE=BLACKHOLE;
mysql> SET sql_log_bin = 1;

This will change the table to a Blackhole table locally, without replicating that change back to the other master server.  This is something you only need to do once, when you set up the table.  After that, us it normally.

The Blackhole Storage Engine...

  acts as a “black hole” that accepts data but throws it away and does not store it.

The server with the blackhole table will still churn through the replicated queries involving that table, but with substantially less overhead (writes to the table will be discarded), and SELECT * FROM table will always return... nothing.

You can write to the real table on one server without taking up space or having the data in the table be selectable on the other server.

caveat: if you are manipulating data in other tables based on what you have in this table (INSERT ... SELECT ... UPDATE ... JOIN ...etc.) then you need to be using binlog_format = ROW or abandon this idea altogether, because those queries will not work without this table's data on both masters, if your binlog_format is STATEMENT, and will not necessarily work reliably with MIXED mode either.


Dealing with NULL values and EMPTY strings in UNION of two tables

Use ISNULL or COALESCE

SELECT COALESCE(A.NULLABLEFIELD,'') FROM A
UNION
SELECT COALESCE(B.NULLABLEFIELD,'') FROM B


SQL query to get the leaf record in a parent-child relationship

This was written while exploring various ways, so if you just want the best solution, see at bottom, otherwise read on to follow the train of thoughts and the progressive refinements.

If you can guarantee order in our IDs (that is new_id > old_id for any renewal, if that is not true see below for enhanced method) then you can do:

WITH RECURSIVE renewals AS (
    SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 1
    UNION ALL
    SELECT pr.old_id, pr.new_id FROM ProductRenewals pr
        INNER JOIN renewals r ON pr.old_id=r.new_id)
SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1

Replace the 1 in old_id = 1 by the value you want to query for.
This will return either nothing (cases 3 and 4) or the id (cases 1 and 2) as such:

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 1 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 2 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
(0 rows)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 3 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT old_id, new_id FROM ProductRenewals WHERE old_id = 4 UNION ALL SELECT pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY new_id DESC LIMIT 1;
 new_id
--------
(0 rows)

If you replace the latest SELECT by SELECT MAX(new_id) FROM renewals you will get either an id or NULL. You could also do something like SELECT COALESCE(MAX(new_id), 0)  FROM renewals, or reuse the id you used as input instead of 0 to match your first design.

Postgresql documentation on recursive CTE is at https://www.postgresql.org/docs/8.4/static/queries-with.html and you can find a canonical example with employees and managers at http://www.postgresqltutorial.com/postgresql-recursive-query/

With as slight change of the above, you do not need to hold any condition on how the id are ordered, just store a counter at each "iteration" step of the recursion, like that:

test=# WITH RECURSIVE renewals AS (SELECT 1 iter, old_id, new_id FROM ProductRenewals WHERE old_id = 1 UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;

(do not change the first 1, it is the starting counter completely not related to your id values, even if any value would fit here in fact)

In fact by slightly elaborating on this you can use this final form that both allows you to get exactly the result as expected (specially in case of non renewals) and also do not care about ordering at all. See:

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 1) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 2) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      2
(1 row)

rest=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 3) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)

test=# WITH RECURSIVE renewals AS (SELECT * FROM input UNION ALL SELECT r.iter + 1, pr.old_id, pr.new_id FROM ProductRenewals pr INNER JOIN renewals r ON pr.old_id=r.new_id), input AS (SELECT 1 iter, id old_id, id new_id FROM Products WHERE id = 4) SELECT new_id FROM renewals ORDER BY iter DESC LIMIT 1;
 new_id
--------
      4
(1 row)


This might seem too obvious but:

rm -rf /var/backup/mysql/ *

Space between / and * means there's two seperate deletes. So you're deleting the contents of /var/back/mysql/ and the contents of *. i.e. Everything in the current location. Could that be doing it?


Is there an easy way to replicate MySQL data?

I have two suggestions
Commercial Product
There is a product called ScaleBase that can handle Geographic replication topologies.
Handcrafted Solution
If you want to set up Geographic Replication using MySQL Replication, you will need to make the MySQL Instance in each Data Center resilient. Here is what thinking:
IDEA #1 : Local Data Redundancy
In each city, setup DRBD with MySQL running on the DRBD Primary
IDEA #2 : Use Distribution Masters
Having a MySQL Replication Master handle the traffic of SQL statement between Data Center could possibly add some intermittent behavior in terms of Replication Lag. If you run another MySQL Instance within the same Data Center whose sole purpose is to ship binary logs to another Data Center, that can be setup in what some call a BLACKHOLE slave. That MySQL Instance contains no actual MySQL. It simply has binary logs and relay logs sued for shipping to multiple slaves. I have discussed using this type of Slave in StackOverflow.
CAVEAT
The two ideas can be combined. The only drawback for using IDEA #2 is doing ALTER TABLE. If any major changes are to be done to table layouts, make sure the Distribution Masters do not change from the BLACKHOLE Storage Engine because this could cascade to the other Data Center and possibly destroy data.
EPILOGUE
I wrote about setting up such topologies before

Aug 20, 2012 : Best solution for cross-datacenter MySQL master-slave replication
Aug 20, 2012 : How to use MySQL Server 5.6 for GeoIP replication?
Jun 25, 2012 : Mysql database replication on different vlan/subnet/another site
Dec 19, 2011 : Best way to setup master to multi master replication
Dec 17, 2011 : Mysql Master-Master Replication Topologies on >2 machines
Aug 18, 2011 : With MySQL Replication, what level of resilience is possible?
Jun 01, 2011 : What can we do in MySQL 5.0 Replication to address bandwidth concerns?
Mar 29, 2011 : MySQL high availability, failover and replication with Latency

Give it a Try !!!


Optimising a sql correlated subquery statement

Neither.

Index ColB INCLUDE (bKey)

Since you aren't filtering on bKey you don't need it at all the nodes of the index, just the leaf level.

If you only index on ColB then you will still have to pay for a key lookup to get the value of bKey from the clustered index.

Also is there a reason this isn't using a JOIN instead of a correlated subquery?

UPDATE a
SET a.bKey = b.bKey
FROM TableA A
INNER JOIN TableB B
ON A.ColB = B.ColB


does DBCC ShrinkDatabase do a Reorganize of files?

No, actually it is notoriously bad for fragmenting the data. You should do a re-org after the shrink is complete.

FROM MSDN:

  A shrink operation does not preserve
  the fragmentation state of indexes in
  the database, and generally increases
  fragmentation to a degree. This is
  another reason not to repeatedly
  shrink the database.


Get all of the rows involved between tables relationships

According to your explanation I think you could use a recursive solution. I've set up a minimal example:

INSERT INTO dbo.Code VALUES
(1,  'Code1',  1, 1),
(2,  'Code2',  2, 1),
(3,  'Code3',  2, 1),
(4,  'Code4',  3, 1),
(5,  'Code5',  3, 1),
(6,  'Code6',  3, 1),
(7,  'Code7',  3, 1),
(8,  'Code8',  3, 1),
(9,  'Code9',  1, 3),
(10, 'Code10', 2, 3),
(11, 'Code11', 2, 3);
GO

INSERT INTO dbo.Aggregation VALUES (1),(2),(3),(9);
GO

INSERT INTO dbo.AggregationChildren VALUES
(2,  1, 1),
(3,  1, 2),
(4,  2, 1),
(5,  2, 2),
(6,  3, 1),
(7,  3, 2),
(8,  3, 3),
(10, 9, 1),
(11, 9, 2);
GO

DECLARE @Serial VARCHAR(20) = 'Code1';

SELECT CodeId FROM dbo.Code WHERE Serial = @Serial;

SELECT    AggregationId Id
FROM      dbo.Aggregation
WHERE     AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial);
GO

| CodeId |
| -----: |
|      1 |

| Id |
| -: |
|  1 |

The anchor selects first row from dbo.Aggregation, and the recursive query rows from dbo.AggregationChildren where AggregationId match AggregationId of dbo.Aggregation.

Then simply update Code table using the list of ID's returned by the recursive query.

DECLARE @Serial VARCHAR(20) = 'Code1';

;WITH rc AS
(
    SELECT AggregationId Id
    FROM   dbo.Aggregation
    WHERE  AggregationId = (SELECT CodeId FROM dbo.Code WHERE Serial = @Serial)
    UNION ALL
    SELECT ac.AggregationChildrenId Id
    FROM   dbo.AggregationChildren ac
    JOIN   rc
    ON     ac.AggregationId = rc.Id
)
UPDATE c
SET    CommissioningFlag = 5
FROM   dbo.Code c
JOIN   rc
ON     c.CodeId = rc.Id;
GO

8 rows affected

SELECT * FROM dbo.Code;
GO

CodeId | Serial | AggregationLevelId | CommissioningFlag
-----: | :----- | :----------------- | :----------------
     1 | Code1  | 1                  | 5
     2 | Code2  | 2                  | 5
     3 | Code3  | 2                  | 5
     4 | Code4  | 3                  | 5
     5 | Code5  | 3                  | 5
     6 | Code6  | 3                  | 5
     7 | Code7  | 3                  | 5
     8 | Code8  | 3                  | 5
     9 | Code9  | 1                  | 3
    10 | Code10 | 2                  | 3
    11 | Code11 | 2                  | 3

db<>fiddle here


Actually - it depends on many things like

hardware you use
wideness of index
current load
...

If index is relatively wide - it can take from minutes to hours.

but

if you have your DB on Enterprise Edition of Sql Server, than you can choose to create an index WITH ONLINE=ON - it will take a little bit more time and space to complete, but will not stop other processing with the table involved


MongoDB deployment across cloud and On Premises

1) Should we need to keep same OS in AWS as our On-premise server or we can keep Amazon Linux also ? In our case it is RHEL 7.2 in On premise servers

There is no strict requirement to have the same O/S for all members of a replica set, but in general it is a good idea to have consistent O/S so you have similar configuration and performance tuning across replica set members.

However, since your DC3 (cloud) instance appears to be an arbiter (which only participates in voting) any O/S differences should be irrelevant to performance.

  2) If No, will there be a performance impact if we use different OS between replica nodes ?

Amazon Linux evolved from RHEL, so isn't an entirely different O/S (for example, like Linux vs Windows). However, there may be different configuration or tuning between Linux distributions. I wouldn't expect dramatically different performance between Linux distros, but this is something you'd have to test with your own use case and workload.

  3) If yes, Do we need to do patch upgrades as frequent as we do in On Prem servers ?

This is up to your own security policy, but I would expect patch upgrades to be applied similarly for On-Premise versus cloud servers.

  4) How the failover works here in case of write concern as Majority ?

Assuming you have an equal number of instances in DC1 vs DC2, an arbiter can be useful to ensure a primary can be elected in the event either DC is unreachable. An arbiter cannot acknowledge writes (since it is a voting-only node), so if you have a Primary-Secondary-Arbiter (PSA) configuration you will not be able to acknowledge majority writes if one of your data bearing nodes is unavailable.

I would strongly recommend using PSS (i.e. no arbiter) to support consistent failover with both elections and majority write concern.

  5) What are the other trade-offs in this approach if any ?

As noted above, arbiters cannot acknowledge write so are not recommendable if you want to support fault tolerance with majority write concern. With a PSA configuration degraded to PsA (one secondary down), you have write availability (since a primary can still be maintained) but no longer have replication or data redundancy (since there is only one data-bearing node writing data).


SQL Server 2014 DBCC CHECKDB notification

Create a Job Step next to the DBCC CHECKDB Step, and set the Checkdb Step field 'On success action' to 'Go to Next Step'
In the Checkdb Step create an output file.
Now, in second Step use the log file to be sent as an email attachment using T-SQL

exec msdb.dbo.sp_send_dbmail @file_attachments = 'Path\Filename.txt'

This should work fine. I am not aware of any inbuilt solution for this.


How do I deal with FK constraints when importing data using DTS Import/Export Wizard?

I was given this solution over at SQLTeam.com:

  Use:

 EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'

  Then import your data

EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'

Using that method I was able to import all the data in with no issues.


Other way to disconnect user/client in mySql 5.6?

Enforcing limits: ALTER USER and set MAX_USER_CONNECTIONS.

If you where using MariaDB you'd have KILL USER

Otherwise, for MySQL and non-MariaDB, for killing off connections try the following SQL:

select @kill := GROUP_CONCAT(CONCAT('KILL ',  id, ';')) FROM information_schema.processlist WHERE user = $id;;
PREPARE stmt FROM @kill;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


How to grant permission for am administrator account in SQL Server 2012?

I found out that I mis-typed the path name in this case.
The correct folder name is MSSQL11.SQLEXPRESS instead of MSSQL11.MSSQLEXPRESS.

After correcting the folder path, I was able to create the database. Thanks!


Batch Delete with checkpoint still causes log growth

First step is to ask SQL Server why the log grows. You should find the answer in the log_reuse_wait_desc column in the sys.databases catalog view. Being in simple mode, it shouldn't be log backup. Then take it from there.
And don't do it all in one transaction, since SQL Server can't truncate past the oldest open transaction.