Compare 2 columns in 2 different tables with non uniques values

Maybe this will help:  use analytics to count (the duplicate) values in each table, and use the results of these operations in a full outer join.  Example (see dbfiddle):

Tables

create table A ( columnA )
as
select 'a' from dual union all
select 'b' from dual union all
select 'b' from dual union all
select 'c' from dual union all
select 'c' from dual union all
select 'c' from dual union all
select 'e' from dual -- one 'e' in each table
;

-- one 'a', 'b', 'c' (respectively) missing here
-- 4 x 'd' (does not exist in the other table)
create table B ( columnA)
as
select 'b' from dual union all
select 'c' from dual union all
select 'c' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'd' from dual union all
select 'e' from dual -- one 'e' in each table
;

Write to 2 queries, one for each table, that look something like ...

-- count the instances of each value
select unique
  'table A ->' as tbl, columnA
, count(*) over ( partition by columnA ) as instances_a
from A

-- result
TBL         COLUMNA  INSTANCES_A
table A ->  a        1
table A ->  c        3
table A ->  b        2

If this works, then use the initial queries as "inline views", and write a full outer join, which will allow you to compare the value counts, eg

select
  *
from (
  select unique
    'table A ->' as tbl, columnA
  , count(*) over ( partition by columnA ) as instances_a
  from A
) A_
full join
(
  select unique
    'table B ->' as tbl, columnA
  , count(*) over ( partition by columnA ) as instances_b
  from B
) B_
on A_.columnA = B_.columnA
order by A_.columnA, B_.columnA
;

-- result
TBL         COLUMNA  INSTANCES_A  TBL         COLUMNA  INSTANCES_B
table A ->  a        1            NULL        NULL     NULL
table A ->  b        2            table B ->  b        1
table A ->  c        3            table B ->  c        2
NULL        NULL     NULL         table B ->  d        4

You can then modify this query, in order to find out whether values are missing from one of the tables etc.  Eg

select
  A_.tbl, A_.columnA, count_a
, B_.tbl, B_.columnA, count_b
, case
    when count_a = count_b then 'yes'
    else 'no'
  end as "column contents equal?"
from (
  select unique
    'table A ->' as tbl, columnA
  , count(*) over ( partition by columnA ) as count_a
  from A
) A_
full join
(
  select unique
    'table B ->' as tbl, columnA
  , count(*) over ( partition by columnA ) as count_b
  from B
) B_
on A_.columnA = B_.columnA
order by A_.columnA, B_.columnA
;

TBL         COLUMNA  COUNT_A  TBL         COLUMNA  COUNT_B  column contents equal?
table A ->  a        1        NULL        NULL     NULL     no
table A ->  b        2        table B ->  b        1        no
table A ->  c        3        table B ->  c        2        no
table A ->  e        1        table B ->  e        1        yes
NULL        NULL     NULL     table B ->  d        4        no

(Tested w/ Oracle 12c and 18c)


What is a good way to verify that a restore completed successfully in SQL Server?

It is considered good practice where I work to run DBCC CHECKDB after a restore, especially if the backup is of an unknown quality.

CHECKDB will at least tell you if your DB has any consistency errors, and checks the logical and physical integrity of all the objects in the specified DB.

A similar thread is on ServerFault: https://serverfault.com/questions/187869/should-i-run-dbcc-checkdb-before-full-backups-or-after


Cannot Create Backup Device - SQL Server 2008 R2, Error 1326 (permissions look ok)

Figured it out -- Windows credential manager didn't have the credentials stored for the share. After adding the proper creds to the credential manager it worked ok.


Fetch nested results as JSON PostgreSQL 9.2

Alright, so you seem to want to create a graph from a join tree. That's a pretty natural thing to want to do.

It's not as easy as it should be in Pg, mainly because of the lack of support for column-aliases in the record pseudo-type.

Here's what I came up with:

SELECT row_to_json(r, true)
FROM (
    SELECT
        d.id,
        d.name,
        json_agg(c_row) AS components
    FROM devices d
    INNER JOIN device_components dc ON (dc.deviceid = d.id)
    INNER JOIN (
        SELECT
            c.id,
            c.name,
            json_agg(m) AS manufacturers
        FROM components c
        INNER JOIN component_manufacturers cm ON (cm.componentid = c.id)
        INNER JOIN manufacturers m ON (cm.manufacturerid = m.id)
        GROUP BY c.id
    ) c_row ON (c_row.id = dc.componentid)
    GROUP BY d.id
) r(id, name, component);

The general idea here is, at every level of object nesting where you're producing an array of objects, use json_agg in a group_by. The json_agg function implicitly calls row_to_json to transform a rowtype into json. In the SELECT clause specify aliases for the synthetic columns so the json key names are correct when the row is fed into the outer level.

As the outer level isn't aggregated, use row_to_json over a subquery instead of using json_agg. If you want a single json result instead of a set of json rows, you can change row_to_json to json_agg in the outer level.

I've only tested on 9.3, as that's what I have installed and sqlfiddle seems to be having some issues.

Update: Unfortunately json_agg doesn't exist on 9.2, it was added in 9.3. However, this is a simple enough case that you can just use array_agg instead, so this should work in 9.2:

SELECT row_to_json(r, true)
FROM (
    SELECT
        d.id,
        d.name,
        array_agg(c_row) AS components
    FROM devices d
    INNER JOIN device_components dc ON (dc.deviceid = d.id)
    INNER JOIN (
        SELECT
            c.id,
            c.name,
            array_agg(m) AS manufacturers
        FROM components c
        INNER JOIN component_manufacturers cm ON (cm.componentid = c.id)
        INNER JOIN manufacturers m ON (cm.manufacturerid = m.id)
        GROUP BY c.id
    ) c_row ON (c_row.id = dc.componentid)
    GROUP BY d.id
) r(id, name, component);

Ideally PostgreSQL would support this sort of query more naturally, without the subqueries, but I think we'd need a new join type, or at least some functions to use with lateral queries. It all plans out into a civilised query plan anyway (though it'd be better with suitable indexes):

                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan on r  (cost=1050.26..1282.94 rows=720 width=106)
   ->  GroupAggregate  (cost=1050.26..1273.94 rows=720 width=188)
         ->  Merge Join  (cost=1050.26..1226.42 rows=7704 width=188)
               Merge Cond: (d.id = dc.deviceid)
               ->  Index Scan using devices_pkey on devices d  (cost=0.15..58.95 rows=720 width=82)
               ->  Sort  (cost=1050.11..1069.37 rows=7704 width=110)
                     Sort Key: dc.deviceid
                     ->  Merge Join  (cost=403.16..552.77 rows=7704 width=110)
                           Merge Cond: (c_row.id = dc.componentid)
                           ->  Subquery Scan on c_row  (cost=253.38..285.63 rows=720 width=110)
                                 ->  GroupAggregate  (cost=253.38..278.43 rows=720 width=286)
                                       ->  Sort  (cost=253.38..258.73 rows=2140 width=286)
                                             Sort Key: c.id
                                             ->  Hash Join  (cost=44.75..135.00 rows=2140 width=286)
                                                   Hash Cond: (cm.manufacturerid = m.id)
                                                   ->  Hash Join  (cost=26.20..87.03 rows=2140 width=86)
                                                         Hash Cond: (cm.componentid = c.id)
                                                         ->  Seq Scan on component_manufacturers cm  (cost=0.00..31.40 rows=2140 width=8)
                                                         ->  Hash  (cost=17.20..17.20 rows=720 width=82)
                                                               ->  Seq Scan on components c  (cost=0.00..17.20 rows=720 width=82)
                                                   ->  Hash  (cost=13.80..13.80 rows=380 width=208)
                                                         ->  Seq Scan on manufacturers m  (cost=0.00..13.80 rows=380 width=208)
                           ->  Sort  (cost=149.78..155.13 rows=2140 width=8)
                                 Sort Key: dc.componentid
                                 ->  Seq Scan on device_components dc  (cost=0.00..31.40 rows=2140 width=8)
(25 rows)


Query performance of a NULL vs an empty ('') varchar in SQL Server 2012

Using your info about the distribution of values (and some guessing), I would say that your problem is that MSSQL is doing an table scan when receiving an WHERE clause for empty string.

Why? Suposing that your index on varchar column does not cover the query (in other words, include all the columns referenced in the query); MSSQL have to do an seek to get the PK's columns (if it is clustered) - or record-id (if not) - and using that in a bookmark lookup to get all the columns it need.

Where comes the table (or clustered index) scan? Simple: based on index statistics, if the number of pages touched to get that data is over than 25%-33% of the total pages of the table, MSSQL considers that is too much expensive to use the index and goes the table (or clustered index) scan way.

This is discussed in deeper details on this dba.SE question.

As to solve the problem, you can:

create (or modify an existing index to be) an covering index to your query
Reduce, if possible, the columns on the query - so it become covered by an existing index


I have never heard of something like this and I have never attempted before

Here is my best guess (using Dynamic SQL)

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                        data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

    DECLARE rv VARCHAR(1000);

    @sql = CONCAT('SET rv = ',function_name,'(',data_to_process,')');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    RETURN rv ;
END

or

CREATE FUNCTION `string_processing_chain` (function_name    VARCHAR(1000),
                                        data_to_process VARCHAR(1000))
RETURNS VARCHAR(1000)
BEGIN

    SET @rv = '';

    @sql = CONCAT('SET @rv = ',function_name,'(',data_to_process,')');
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

    RETURN @rv ;
END

Give it a Try and tell us all if it worked, please !!!

From OP I used this :

CREATE PROCEDURE `string_processing_chain`(
    function_name   VARCHAR(1000),
    data_to_process VARCHAR(1000) )
BEGIN
    SET @sql2 = CONCAT('CALL ',function_name,'(?)');
    SELECT @sql2;
    PREPARE s FROM @sql2;
    SET @a = data_to_process;
    EXECUTE s USING @a;
    DEALLOCATE PREPARE s;
END


Postgresql: How to take incremental backup with Barman

With Barman, the only way to do it is to use rsync backup method and to enable links. See example :

backup_method = rsync
reuse_backup = link

If you need advanced configuration about incr and diff backups, maybe you should have a look at pgbackrest tool.


Retrieve parent and child data using child id

I was able to figure out what I wanted to achieve.

I just added an OR on right join. (http://sqlfiddle.com/#!9/f9f1c6/12)

Select parent.child_id, parent.parent_id, parent.child_name from Child c
RIGHT JOIN (
  Select NULL as child_id, parent.id as parent_id, parent_name as child_name
  from parent
  Union
  Select child.id as child_id, NULL as parent_id, child_name
  from Child
) parent on parent.parent_id = c.parent_id OR parent.child_id = c.id
WHERE c.id = '2';


LOAD DATA INFILE blocks on concurrent use against MyISAM tables

Concurrent LOAD DATA INFILE on MyISAM tables are limited by mutex congestion due a global Key Cache. To alleviate lock congestion, MyISAM tables can be set to have their own Key Cache.

SET GLOBAL isam_cache.key_buffer_size=8*1024;
SET GLOBAL isam_cache_two.key_buffer_size=8*1024;
CACHE INDEX log_isam IN isam_cache;
CACHE INDEX log_isam2 IN isam_cache_two;


Checking if entities match an id in on relation where it could show up in either of two columns

Use set operations. If the user is 42, and the group is 1001, that would be
(SELECT user_id_a FROM userfriend
 WHERE user_id_b = 42
    UNION
 SELECT user_id_b FROM userfriend
 WHERE user_id_a = 42
)
   EXCEPT
SELECT user_id FROM usergroup
WHERE group_id = 1001;

UNION creates a result set that is the union of two query results and removes duplicate rows. EXCEPT returns all result rows from the left side that do not occur on the right side.


Can a Z/OS mainframe application or DB2 read a SQL Server database/table directly?

Define read...

You can certainly run a Java application on z/OS that uses JDBC to connect and transfer data from MS SQL Server into DB2 for z/OS.

Otherwise, IBM's distributed database protocol is known as Distributed Relational Database Architecture (DRDA).

Both IBM and Microsoft offer products that allow MS SQL server to be seen as a remote DB2 server.
Microsoft Service for DRDA
IBM InfoSphere Federation Server

With the IBM product, you'd need a DB2 LUW server in the middle.

On IBM's midrange platform, IBM i (aka AS/400), there exists an open-source DRDA-JDBC gateway, ArdGate.  I don't know if something similar exists for z/OS or if you could port the project or build one yourself.


Configuring log shipping with one primary and two secondary instances

Below are the steps that you need to follow :

Take full backup on primary (server 1 ).
Restore full backup on server 2 and 3 using no_recovery option.
Take log backup on server 1
Restore log backup on server 2 and 3 using no_recovery option.
Using GUI wizard, add secondary servers (server 2 and 3).

This will help you set up tertiary logshipping. I have done it and it just works fine.

Note: When you failover, you need to be careful as to what server you are failing over and have to manually restore the tail log backup on the other secondary server.


This is not a problem, but expected, documented behaviour:

An AUTO_INCREMENT column normally has missing values. This happens because if a row is deleted, or an AUTO_INCREMENT value is explicitly updated, old values are never re-used. [...] With InnoDB, values can be reserved by a transaction; but if the transaction fails (for example, because of a ROLLBACK) the reserved value will be lost.
Thus AUTO_INCREMENT values can be used to sort results in a chronological order, but not to create a numeric sequence.

Most RDBMSes work this way. The identity/sequence/autoincrement value is not incremented by a random number. What happens is each session reserves and caches a certain, often configurable, number of identity/sequence/autoincrement values, and unused values from the cache are discarded if the session ends before they are all exhausted.
For example, session A reserves values 1 through 5 for itself, then session B reserves 6 to 10. Session A uses values 1, 2, and 3 and terminates, so 4 and 5 are never used.
If for some reason you require a gapless sequence (which in reality is very rarely needed), you'll have to implement it yourself, but note that this will be a performance bottleneck under any nontrivial workload.


Unable to connect SQL Server 2008 from SQL Server 2005 DB

This is a known issue.  Please see the following KB article for a resolution: FIX: You may experience problems when you use SQL Server Management Studio in SQL Server 2005 to connect to an instance of SQL Server 2008


what are the SplitPage as Transaction Name?

I'd say that just reading the transaction log won't give you a good idea if the fillfactor is too high. Keep in mind that Page Splits are not always a bad thing, and they are necessary.

Your fill factor is set when the index is created and rebuilt, so I'd look at how often your indexes need to be rebuilt if you use Ola's maintenance solution it will track that for you. Use tools like Ola's Command Log table and judgement based on the size of the index, size of the table, type of key values to decide on your fill factor. You can usually set your fill factor to 100 and then re-evaluate if there are a lot of index rebuild commands in Command Log.

New pages created during Page Splits like in your example will not allocate empty space based on your fill factor, they will simply fill the pages as needed for the new data.

In general, a good clustered index key will always be incrementing, so every page split will be simply to allocate a new page at the end of the index. This changes a bit with varchar columns, since they can cause page splits during an update.


Archive Relational OLTP Database by Year

These do not have order dates. How would I archive this? We have 100+
  million rows? I thought partition switching would not work unless
  every table has year column.

To use partition switching to facilitate archivial by year, order-related tables don't necessarily need a new year column. Instead, you could add OrderDate to related tables and partition on OrderDate with yearly boundaries using a sliding window. All the tables would need the same partitioning OrderDate column and be aligned (indexes also partitioned).

This does introduce redundancy and would have significant implications on both the data model and OLTP query performance. The paritioning column needs to be part of the primary key, unique constraints, and indexes. Queries that don't specify the partitioning column (e.g. WHERE OrderID = @OrderID) would need to hit both partitions of the active tables.

My recommendation here would be to use a traditional ETL process for the archive rather than introduce significant changes that would only benefit the annual archive. If your business requirements allow, consider a daily or monthly archive to keep the process more manageable.


How does the query processor execute COMPUTE BY?

It is a hard-coded sequence of operations at the top of the execution tree. There is a simple stream accumulation (hence the ordering requirement), some logic to detect the start of a new group, a little computation and data value copying, and direct calls to construct the alternate TDS result sets per group. Some example call stacks from a query using COMPUTE BY are shown below:

  and how can I obtain an accurate execution plan?

You can't, because COMPUTE BY is not implemented in the same way as other plan operations. The replacement ROLLUP support does use the regular execution framework, automatically providing showplan information. An additional benefit of ROLLUP is that it does not use alternate TDS result sets, providing better client compatibility. In short, no one will miss COMPUTE.


Looking for an efficient way to fix "Could not parse relay log event entry..." error

There is more stable approach you can try

Here is something to remember

Whenever you run CHANGE MASTER TO, it will erase every relay log you have. You do not want to keep relay logs of commands you have not executed any SQL on as of yet

The following is an excerpt taken from a post I made back on Feb 03, 2012 : How to resolve the master server shut down/unavailability in mysql with master - slave replication :

  Please notice that there are two sets of replication coordinates from
  the Master

  (Master_Log_File,Read_Master_Log_Pos)
  (Relay_Master_Log_File,Exec_Master_Log_Pos)

  There is a major difference between them

  (Master_Log_File,Read_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the Slave
  read from the Master and placed in its Relay Logs.
  (Relay_Master_Log_File,Exec_Master_Log_Pos) tells you the last binlog statement from the Master's log file and log position that the
  Slave read from the Master and placed in its Relay Logs THAT IS NEXT
  TO BE EXECUTED ON THE SLAVE.

What you want are two things:

Erase Every Binary Log You Have
Start Collecting Binary Log Entries From the Last SQL You Successfully Executed.

In your case, you must use the second set of Replication Coordinates

Relay_Master_Log_File
Exec_Master_Log_Pos

It is easy to distrust a corrupt relay log as shown in the error message. The one that hurts the most is a corrupt Master Log. You will have to jump through hoops if that is the case. On the other hand, if one of the other situations was the reason for the corrupt relay log, the simplest and most concise approach is what I stated.

To make sure, whatever is reported for Relay_Master_Log_File, if that particular binary log still exists on the Master, perform a mysqlbinlog on it. If it dumps in its entirety without corrupt characters, go ahead and use the second set of replication coordinates.

From my same earlier post

mysql> show slave status\G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 10.48.20.253
                Master_User: replicant
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysql-bin.000254
        Read_Master_Log_Pos: 858190247
             Relay_Log_File: relay-bin.066069
              Relay_Log_Pos: 873918
      Relay_Master_Log_File: mysql-bin.000254
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 858190247
            Relay_Log_Space: 873772
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

notice that the Replication Coordinates from SHOW SLAVE STATUS\G for what was last executed are (mysql-bin.000254,858190247). The CHANGE MASTER TO command in this case would be:

CHANGE MASTER TO master_log_file='mysql-bin.000254',master_log_pos=858190247;

Give it a Try !!!

UPDATE 2012-09-14 16:38 EDT

If you worried about the stockpiling relay logs, just throttle the relay logs. In SHOW SLAVE STATUS\G, there is a field called Relay_Log_Space. That gives you the sum of all relay sizes in bytes. Did you know you could put a cap on that number ?

The option is called relay_log_space_limit.

For example, if you want to cap the total number of bytes to 10G, do the following

STEP 01) Add this to /etc/my.cnf on the Slave

[mysqld]
relay_log_space_limit = 10G

STEP 02) Run service mysql restart on the Slave

and that's it !!!

When the oldest relay has all its entries processed, it is deleted and a new relay log is created. That gets filled until all relay logs add up to 10G. That's the only way to control runaway relay log space issues.

UPDATE 2012-09-14 18:10 EDT

SUGGESTION : If you make mysqldump backups of the data on the Slave every midnight, you could set up the following to restrict having 1TB of binary logs:

STEP 01) Add this to /etc/my.cnf on the Master

[mysqld]
expire_logs_days = 14

STEP 02) Run this query on the Master

mysql> PURGE BINARY LOGS BEFORE DATE(NOW()) - INTERVAL 14 DAY;

STEP 03) service mysql restart on the Master

STEP 04) Add a mysqldump backup script to a crontab on the Slave

This will make the Slave more useful and would control having excess binary logs to worry about


Insert a group of consistent item with foreign key but colliding with existing items

In reference to your comment, CTEs are scalable that way. You should do everything in a single transaction anyway. The related question @ypercube suggested should fit your case exactly:
How do I insert a row which contains a foreign key?

Your other option would be to employ lastval() and curval() to identify  values most recently obtained from sequences. Not providing code, your question is too vague. These related questions on SO may be helpful:
Reference value of serial column in another column during same INSERT
PostgreSQL next value of the sequences?


Will removing a 2nd log file break log shipping?

Removing the extra file won't give you any problems.


Updating Statistics: Estimated Number of Rows not equal to Actual for Index Scan. Why?

Row count appears to only hold 6 significant digits of information (apparently called a mantissa for a float).

Consider the below example, which has a table with 11,111,111 rows. However, estimated rows is only displayed as 11,111,100.

USE TestDB
GO

DROP TABLE IF EXISTS dbo.stattest
GO
CREATE TABLE dbo.stattest (ID int primary key, junk char(1))

INSERT dbo.stattest
SELECT TOP 11111111 ROW_NUMBER() OVER(ORDER BY 1/0), 'a'
FROM master..spt_values a
CROSS JOIN master..spt_values b
CROSS JOIN master..spt_values c

SELECT COUNT(*)
FROM dbo.stattest

Interestingly, the stats object shows all 11,111,111 rows.

UPDATE STATISTICS dbo.stattest WITH FULLSCAN
GO
SELECT *
FROM sys.dm_db_stats_properties(OBJECT_ID('dbo.stattest'),1)

By adding TF 2363, you can see that the rounding occurs during the cardinality estimation process.

SELECT COUNT(*)
FROM dbo.stattest
OPTION(
QUERYTRACEON 3604,
QUERYTRACEON 2363
)

  CStCollBaseTable(ID=1, CARD=1.11111e+007 TBL: dbo.stattest)


SQL Server 2008 R2 Ent trial expired move to express?

So someone let our SQL server 2008 R2 enterprise trial expire on a production web server

Violation of the EULA.

  Is express or compact capable of running a small website?

Both are capable of running a small website. Express is also capable of running medium or large websites. Personally I would go for Express if possible, Express handles concurrent access much better than Compact.

  Is it possible downgrade to express or compact and how?

Uninstall Enterprise, install Express and migrate the database to the new instance. Compact is much harder - Microsoft has no tools for downgrading to Compact. This tool, amongst others, can help you.

  How can I backup the existing databases now that we have been effectively locked out of them?

You might try starting the database in single user mode, or stopping the SQL Server service and getting the MDF and LDF files out of the DATA directory and attaching them to the Express instance.


EdStevens wrote:

"message not found" invariably means that ORACLE_HOME is not set correctly.  The message file (the file with the text of all of the error messages) is located in relation to ORACLE_HOME. If ORACLE_HOME is not correct, then it can't find the message file, and all errors will cascade to "message not found".

The question author responded:

I think I solved the problem; the Oracle service TNS Listener was not started. I changed the ORACLE_HOME environment variable to: C:\app\Owner\product\18.0.0\dbhomeXE -  I dropped the "\bin" directory.


Listener pointing to incorrect AlwaysOn Availability Group

An Availability Group Listener simply connects the user to a database instance. Once they are connected to the instance they can access all the databases on the instance. It doesn't matter of the databases are part of the availability group or not. This is by design.

In other words everything is working exactly as it should be.


Rule of thumb: fewer tables and more columns.
In your case you should probably have one tier table, and make the level another column. Then have a constraint that level can only be between 1 and 4.
If the tiers are always in order and have those values you don’t need any more data — the absence of a record with level 3 would imply tiers 1 -> 2 -> 4.
You might need two columns for keys: one ID for each individual record and a second column which has the same value across each group of tiers that belong together. It’s that column that you would refer to in a foreign key from (an)other table(s).
But without knowing more about your exact use-case I can only keep this advice generic.


DB2 - How to estimate proper BUFFERPOOL size?

You can't "estimate" the size of a buffer pool, because the answer is, "it depends".

Try using the AUTOCONFIGURE command to get started, and enable STMM.  Together these will go a long way towards getting your database running more efficiently.

I would also recommend you spend some time reading about DB2 Performance Tuning.  There is a ton of information available on the web, starting from the entire Performance Tuning section of the manual.


"Transaction T can read or write data item X only if X had not been accessed by a "later" transaction.  Otherwise, the operation of T is rejected; T is aborted and restarted with new TS".

In other words we have:

ST1 and then ST2 any data that is read or write by T2, could not be access by T1 or it will rollback that is because T2 start after T1.

I found it here in the slide 28

I hope it helped you


https://www.veritas.com/support/en_US.html

you'll have to find the SQL Agent Guide for that version of Netbackup and see what it supports. Technically you need 7.5 to backup SQL 2012 but I think that version supports only the bare features. I think they only introduced some of the advanced features in version 8, but I can't say with 100% certainty. You will need to find the doc for your version and check to see what it supports


One alternative is to trade the correlated subqueries and, potentially, joins for using variables as well as a lot of nesting and sorting.

The starting point of the approach I am suggesting will be this query:

SELECT
  MONTH(rdate)  AS month_2015,
  SUM(ramaount) AS this_amount
FROM
  my_table
WHERE
  YEAR(rdate) = 2015
GROUP BY
  MONTH(rdate)

which is essentially your second query. According to your example, the output would be this:

month_2015  this_amount
----------  -----------
1           375
3           950
5           1730
6           1200
7           6000
8           300
9           200
10          180
12          1200

The following query uses that as a derived table to calculate the running total from the first to the last month with the help of a variable:

SELECT
  month_2015,
  this_amount,
  @past_amount := @past_amount + this_amount AS past_amount
FROM
  (SELECT @past_amount := 0) AS x,
  (
    SELECT
      MONTH(rdate)  AS month_2015,
      SUM(ramaount) AS this_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
    GROUP BY
      MONTH(rdate)
  ) AS sub
ORDER BY
  month_2015 ASC

In your case the returned rows would look like this:

month_2015  this_amount  past_amount
----------  -----------  -----------
1           375          375
3           950          1325
5           1730         3055
6           1200         4255
7           6000         10255
8           300          10555
9           200          10755
10          180          10935
12          1200         12135

Never mind that the past_amount value includes the current month whereas you seem to require otherwise – that will be rectified later.

The next step would be to use the above as a derived table again and use the same method to calculate future_amount while sorting the rows in the opposite order:

SELECT
  month_2015,
  this_amount,
  past_amount,
  @future_amount := @future_amount + this_amount AS future_amount
FROM
  (SELECT @future_amount := 0) AS x,
  (
    SELECT
      month_2015,
      this_amount,
      @past_amount := @past_amount + this_amount AS past_amount
    FROM
      (SELECT @past_amount := 0) AS x,
      (
        SELECT
          MONTH(rdate)  AS month_2015,
          SUM(ramaount) AS this_amount
        FROM
          my_table
        WHERE
          YEAR(rdate) = 2015
        GROUP BY
          MONTH(rdate)
      ) AS sub
    ORDER BY
      month_2015 ASC
  ) AS sub
ORDER BY
  month_2015 DESC

This would be the result:

month_2015  this_amount  past_amount  future_amount
----------  -----------  -----------  -------------
12          1200         12135        1200
10          180          10935        1380
9           200          10755        1580
8           300          10555        1880
7           6000         10255        7880
6           1200         4255         9080
5           1730         3055         10810
3           950          1325         11760
1           375          375          12135

Again, do not worry at this point about the future_amount results including the current month's amount.

Your next, and last, step will be to nest the previous query one level more in order to sort the results back in the ascending order of months as well as to adjust past_amount and future_amount as past_amount - this_amount and future_amount - this_amount, respectively:

SELECT
  month_2015,
  NULLIF(past_amount   - this_amount, 0) AS past_amount,
  this_amount,
  NULLIF(future_amount - this_amount, 0) AS future_amount
FROM
  (
    SELECT
      month_2015,
      this_amount,
      past_amount,
      @future_amount := @future_amount + this_amount AS future_amount
    FROM
      (SELECT @future_amount := 0) AS x,
      (
        SELECT
          month_2015,
          this_amount,
          @past_amount := @past_amount + this_amount AS past_amount
        FROM
          (SELECT @past_amount := 0) AS x,
          (
            SELECT
              MONTH(rdate)  AS month_2015,
              SUM(ramaount) AS this_amount
            FROM
              my_table
            WHERE
              YEAR(rdate) = 2015
            GROUP BY
              MONTH(rdate)
          ) AS sub
        ORDER BY
          month_2015 ASC
      ) AS sub
    ORDER BY
      month_2015 DESC
  ) AS sub
ORDER BY
  month_2015 ASC
;

And this is would you should get as the result:

month_2015  past_amount  this_amount  future_amount
----------  -----------  -----------  -------------
1           NULL         375          11760
3           375          950          10810
5           1325         1730         9080
6           3055         1200         7880
7           4255         6000         1880
8           10255        300          1580
9           10555        200          1380
10          10755        180          1200
12          10935        1200         NULL

I additionally used the NULLIF function on the subtractions to substitute NULLs for what otherwise would be 0s in the first row's past_amount and the last row's future_amount, because that was what your results seemed to suggest to me you wanted. If a 0 in both cases would do just fine, then do not use NULLIF.

A demo of this approach is available at Rextester and SQL Fiddle.

We could also simplify by calculating the total first, so we don't have to do running sums, only one forward:

SELECT
  month_2015,
  @past_amount := @past_amount + @this_amount AS past_amount,
  @this_amount := this_amount                 AS this_amount,
  total_amount - @past_amount - @this_amount  AS future_amount
FROM
  (SELECT @past_amount := 0, @this_amount := 0) AS x,

  (
    SELECT
      SUM(ramaount) AS total_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
  ) AS t,

  (
    SELECT
      MONTH(rdate)  AS month_2015,
      SUM(ramaount) AS this_amount
    FROM
      my_table
    WHERE
      YEAR(rdate) = 2015
    GROUP BY
      MONTH(rdate)
  ) AS sub
;

An extra logical table scan in the form of the total amount across the year has been added in exchange for extra sorts of the result set.

Both solutions at Rextester.


the code erro ORA-04091 is relative the transaction. In a trigger you dont can execute select, unless you declare a new transacation with clause PRAGMA AUTONOMOUS_TRANSACTION.

remember to run a commit or rollback to finalize transaction on the trigger.


Measure compilation time of stored procedure

Yes, you can get this information. It is buried inside properties of the select statement in an actual execution plan. To do that:

Set Actual Execution plan to on
Execute your SP with recompile option
Go to Actual Execution plan, choose 'select' part
Go to properties (Press F4) and there you can see CompileTime

However, I'm not sure how accurate or what measures this information displays.

I am assuming, you want to discover how much it does affect your performance. You can search this article for 'compile time' https://technet.microsoft.com/en-us/library/cc966540.aspx


PgBouncer on separate server than PostgreSQL

The top command gives the CPU usage per core. Since you have 12 I would not create another Linode for that. Check the line with Cpu(s). There you will find the %id which is a better indication weather or not your server is using it's full CPU capacity.


How, when and why does implicit changing of transaction isolation level happen?

How does the output illustrate implicit elevation of isolation level?

Sunil is technically correct, but it does sound a little confusing, I agree.
The output shows the session is blocked waiting to acquire a U lock. The definition of the READ COMMITTED isolation level is that the session will only encounter committed data. SQL Server honours this logical requirement under the default pessimistic (locking) implementation of read committed by holding shared locks just long enough to avoid seeing uncommitted data. These shared locks are normally quickly released (usually just before reading the next row).
Under optimistic (row-versioning) read committed (RCSI) SQL Server avoids reading uncommitted data by reading the last-committed version of the row at the time the statement started instead.
The sense Sunil is trying to convey is that taking U locks (instead of brief shared locks or reading versions) represents a (technical) escalation of isolation level (though not to any explicitly named level).
The effective isolation level in this case is not quite REPEATABLE READ because any U locks taken (and not converted to X locks) are released at the end of the statement. This is different from the behaviour of the UPDLOCK hint, which acquires and holds U locks (at least) until the end of the transaction. In addition, REPEATABLE READ generally acquires S locks (though this is strictly just an implementation detail).
Confusingly, the engine also takes U locks on the access method when identifying rows to update under default (locking) read-committed. This is a convenience to avoid a common deadlocking scenario without having to specify UPDLOCK explicitly. I apologise that this is so complicated, but there we are.

How to check for real isolation level "jumpings" in context of some statements?

There is nothing explicitly exposed in query plans to identify cases where the engine temporarily increases the effective isolation level. This might change in a future version of SQL Server. There may be indirect evidence in terms of locks taken, but this is rarely a convenient approach.

When to expect them and why do they occur?

Some of the occasions when internal escalation occurs are (somewhat) documented in Books Online. For example, Understanding Row Versioning-Based Isolation Levels says (among other things worth noting):

In a read-committed transaction using row versioning, the selection of rows to update is done using a blocking scan where an update (U) lock is taken on the data row as data values are read.

The general reason for temporary changes in effective isolation level changes is to avoid data corruption. A list of posts identifying some common cases follows:
Blocking Operators
Large Objects
Lookup with Prefetching
Cascading Referential Integrity
Other common cases (not a complete list):

Shared locks taken when the query processor verifies foreign key relationships.
Range locks taken when maintaining an indexed view referencing more than one table.
Range locks taken when maintaining an index with IGNORE_DUP_KEY.

Some of these behaviours may be documented in Books Online, somewhere, but there's no convenient single list that I am aware of.


Coalesce on right hand table field with left join, or a better way?

no one has bothered with an answer

an index on [HomePages] [Key]

an index on [HomePageTranslations] [CultureName], [HomePageId]
and make it a unique constraint if appropriate

the query itself looks fine to me

you could probably just use a view and an index on [HomePageTranslations] [HomePageId] and have fine response
10s of thousands is not that much


ORA-01017 and ORA-12545 when trying to connect to oracle 12c with sqlplus

You can't connect from a 9.2.0 SQL*Plus to a 12.2.0.1 version or Oracle Database.

It was never a supported option as displayed in the Interoperability Support Situation matrix chart in an Oracle Support Note.1 (Oracle Support Login required to read the Oracle Document.)

A SQL*Plus 9.2.0 client was only able to connect to 11.2.0 server or lower.

The lowest supported version of SQL*Plus that could connect to an 12.2.0.1 version of Oracle Database would be an 11.2.0.x client.

1 Oracle: Note 207303.1

Reference: oracle 9i client connecting to oracle 12c server
 (Stackoverflow.com)


Securing an Internet Database Connection

You DB should probably not be publicly accessible, unless it really has to be. You can make DB accessible only to localhost, which would allow your web server to talk to it and you can use it over SSH or Remote Desktop or what have you.
You'll want to watch out for brute-force attempts on your logins. I'm not sure what the Windows equivalent of Fail2Ban is, but you'll want something like that.
Shut off any unneeded db services, see what ports are open, use a firewall to block any ports other than the ones you definitely need.
Have your access logs recorded to another computer with a write-only connection, so that if someone does break in, you still have logs.


My problem is solved. I have changed the FRA to new location.

alter system set db_recovery_file_dest='/flasharch01';
alter system set db_recovery_file_dest_size=750G;

Copy all archive log file which is generated in old and copy to new location.
using OS level copy commond.

RMAN> catalog db_recovery_file_dest;

Or

RMAN> catalog recovery area;

Or

RMAN> catalog start with 'New location Path';
RMAN> catalog start with 'New location Path';

create PFILE='/flasharch/file_raiddb.ora' from spfile;

RMAN> crosscheck copy;
RMAN> crosscheck backup;
RMAN> delete expired copy;
RMAN> delete expired backup;

alter system set log_archive_dest_1='location=/flasharch01';
create spfile from pfile=''/flasharch/file_raiddb.ora''

CREATE SPFILE='/flasharch/file_raiddb.ora'
FROM PFILE='/flasharch/file_raiddb.ora';


High SOS_SCHEDULER_YIELD and Signal Waits

Recent expensive queries in the Activity Monitor in SSMS should be your first stop.

For more information grab SP_Whoisactive and find Glenn Berry's diagnostic queries which will help you a lot. The exact query depends on the version of SQLServer but this should be a start.

SELECT TOP(50)
  creation_time
 ,last_execution_time
 ,(total_worker_time+0.0)/1000 AS total_worker_time
 ,(total_worker_time+0.0)/(execution_count*1000) AS [AvgCPUTime]
 ,execution_count
 ,st.text
FROM sys.dm_exec_query_stats  qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
 WHERE total_worker_time > 0
ORDER BY total_worker_time DESC


MYSQL: How to optimize a date sorted table with a triple primary key index

Your query is not sargeable. Using Hour() function on the first column (pk1) of the composite primary key is inhibiting the usage of the Primary Key for index lookup; and thus (in absence of any other proper index), it is most likely doing full table scan. The general rule of thumb to follow while defining a working index is:

First priority should be given to all the columns, which are inside the WHERE clause, and are connected by AND clause, and are compared to a constant value using either =, IS NULL, or <=>. So, in your query, there are two columns following this: Data_Type and Status.
Second priority can be given to following three scenarios:

Column having Range condition.
Columns in a specific order in the GROUP BY clause (if existing).
Columns in a specific order in the ORDER BY clause (if existing)

Now, coming to your questions:

  Would changing the DATETIME to a DATE (and thus remove HOUR(0), no
  longer needed) resolve the problem in a satisfying enough way ? I
  don't need it anymore, especially if it causes problems.

Yes, you should do that; if the time part is irrelevant to you. In MySQL 8+, the size of datetime is 5 bytes, while the size of date is 3 bytes. So you are effectively going to save considerable amount of space as well.

With this change, your new query would simply be:

SELECT * FROM table
WHERE pkey2 = ...
  AND pkey3= ...
  -- removed AND HOUR(pk1) = 0
ORDER BY pkey1 DESC

In this scenario, if you refer the thumbrule discussed above, you need to define a new Composite Index (pkey2, pkey3, pkey1) (the ordering is changed; you will need to keep this index besides the already defined Primary Key).

ALTER TABLE table_name ADD INDEX (pkey2, pkey3, pkey1);

This should be able to help you considerably.


Custom aggregation for non-leaf level nodes to avoid double counting

The right approach for the problem is to use many to many relationship in SSAS.

More details here https://stackoverflow.com/questions/10971593/custom-aggregation-for-non-leaf-level-nodes-to-avoid-double-counting and here http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf.

Then no MDX is required.


No sp_helpdb wont show info about filestream data. You have to use a custom solution modified as per your needs.

Remember that sys.database_files with file type 2 = FILESTREAM shows data about filestream.


CREATE LOGFILE GROUP and CREATE TABLESPACE accept only bytes as unit.

http://bugs.mysql.com/bug.php?id=62858

With MB converted to bytes logfile group was created successfully for me.


Restore Postgres 9.0 backup into Postgres 9.3

So...

Does any of the data structure exist?

I could be wrong but it may be that your backup format. It depends on how you backed up, using pg_dump etc.

try using pg_restore filename -d database

pg_restore /root/mydb.sql -d database

It could also be the database encoding.

The database you are dumping into, what encoding is it?

UTF-8, SQL_ASCII ?

try
createdb database -E utf-8

then
psql -f /root/mydb.sql -d databse


The easiest optimization would probably be to let the planner use a hash anti join, by rewriting the query as:

 delete from taskflag where not exists
  (select 1 from idtemp where documentid=taskflag.documentid);

also you may need to ANALYZE the temp table immediately after populating it.


Schema unusable after deleting another database

It looks like you'll need to change the user's default database, as it appears this is currently set to DB1, which no longer exists.

exec sp_defaultdb @loginame='user1', @defdb='DB2'

OR

ALTER LOGIN <login_name> WITH DEFAULT_DATABASE = <default_database>


How can I only load one function from an extension?

In PostgreSQL 13, the gen_random_uuid() function is now available globally, with no need to use the pgcrypto or uuid-ossp extensions.
It is still not possible to only load one function from an extension, but for this common case, it's possible to generate random UUIDs without having all those other UUID-related functions in scope.


Percona MySQL 5.6 databases ignoring key length at constrait creation

By default, an index key for a single-column index can be up to 767
  bytes

–MySQL Reference Manual

The size of your index is:

191 (characters) * 4 (utf8mb4 max size per character) + 4 (length) + 1 (NULL) = 769

The calculation may not be 100% exact, I am not fully sure about the length requirements, but I will assume it is not a bug.

The reason why it may be working initially could be because you may be using utf8, not utf8mb4 on the original system (which is 3 bytes in size per character instead of 4) or you had enabled the variable innodb_large_prefix.

In any case, I would recommend you to fix it enabling that very same variable, which will allow you prefixes of up to 3072 bytes (it will be the default in newer versions).

Edit: ALTER TABLEs on a production PXC are usually done in RSU mode.


System account locked, insufficient privileges to log in as sysdba

The windows user account belongs to Administrator and Ora_DBA Windows
  groups.

Double check this, because you get the above error, when you are not a member of the ora_dba group.

Check %ORACLE_HOME%/network/admin/sqlnet.ora for the following line:

SQLNET.AUTHENTICATION_SERVICES=(NTS)

If it is set to NONE, that disables logging in with / as sysdba, but would result in a ORA-01017: invalid username/password; logon denied error.

Finally, SYS password is stored outside the database, in a file, on Windows it is %ORACLE_HOME%/database/PWD%ORACLE_SID%.ora. You can recreate it (give new SYS password) as:

orapwd file=%ORACLE_HOME%/database/PWD%ORACLE_SID%.ora force=y

After that, you can should be able to log in with the given password as:

connect sys/password as sysdba


Should I capture all the relationships in a Database design?

Second what @bbaird says. I usually start with the objects I'll be dealing with. (user, category, blog, comment).
Then I'll start thinking about how they are connected.

A user is interested in zero to many categories
A user can follow another user
A user can create a blog
A user can create a comment
A comment is applied to a blog
A blog belongs to a category

To cover the links that are to one to many or many to many, I'll use joining tables that look like this;
UserToCategory (PK int, UserID int, CategoryID int)
UserToBlog  (PK int, UserID int, BlogID int)
Obviously there's a bit more to fully flesh out the plan, but this gets things started. If your foreign keys are built right, you can run a query to get all comments where the blog category is sports, joining across a couple of tables at once.


Why do nonclustered Indexes use clustered indexes instead of the table data pages directly?

The clustered index IS the table. In the classic phone book analogy where everything is ordered by Lastname, Firstname there's not some other book that the phone book refers to.  The Phone book IS the book, and it's in that order.

When you have a clustered index, it contains all the data for all the columns in the table, ordered by the key you pick.

In the absence of a CI, the non-clustered indexes need to refer to something so they refer to the RID (row identifier) which is a surrogate key the engine assigns.


How can I be specific about which CDB I want to connect to wrapping a PDB?

Quite easily.

The full syntax for Easy Connect is:

Understanding the Easy Connect Naming Method

CONNECT username@[//]host[:port][/service_name][:server][/instance_name]

You just need to specify the service_name and the instance_name as well.

$ lsnrctl services
...
Service "pdb1" has 2 instance(s).
  Instance "RAIN", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
  Instance "WIND", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER

You will have more than 1 instance serving the same service. The below may connect to any of the instances, but the listener decides which instance it forwards to your request, not you:

sqlplus user/password@localhost:1521/pdb1

The below commands always connect to just one of the instances. If that instance is down, the connection attempt fails, even though there is another instance providing the same service:

sqlplus user/password@localhost:1521/pdb1/rain
sqlplus user/password@localhost:1521/pdb1/wind


Changing the grid listener port of an Oracle Database Appliance X8-2M

The command is definitely (user grid):
srvctl modify listener listener -p "TCP:1522,4444,nnnn"

Our issue was that the port we wanted to use, was used by another service on the ODA (e.g. ONS). The command completes but the listener ignores the port than. This explains the log above maybe.
We also needed to enter the local_listener on the ODA CDBs/ASM:
alter system set local_listener='(ADDRESS = (PROTOCOL = TCP)(HOST = hostname)(PORT = 1522))';

We opened a SR at Oracle support and got a feedback:

Right now the dcs agent on ODA does not handle multiple listener port.
We can configure listener to use multiple ports on ODA. But we face
issues during create-database, register-database. Enhancement has been
logged for such issues which will be fixed in future release Enh
30095060 - ODACLI CANNOT REGISTER DATABASE At present I would not
recommend to add multiple ports to listener.


Streaming Replication on Two Postgres Systems?

Streaming Replication will only replicate your entire cluster, and not a single database. It will also make the slave database read only, so you can't use it as your "Training Database".

If you need to do that, you need to look at something like Slony, Bucardo or Londiste.

However, those are much more complex to set up and maintain. Another option is to run two completely different clusters ("instances") on the slave - one that is the slave and one that is the training. They'll then run on different ports, with different memory pools, etc, etc. It's not great for performance to do that, but if it's just a training database that may not matter, and it's a much easier replication system to work with.


My understanding is that each shard is a physical machine. holding below two sets of data(primary and secondary) and host two separate mongod server/instance.

No, each replica-set member is a physical machine. In the above graphic, each shard consists of two physical machines. While you can technically run multiple members of a replica-set on the same machine, there is nothing to gain from that.

  . One will hold primary data and second will hold secondary data which is back up of primary data on another shard

No, the secondary will back up the data from the other member(s) of the same shard. But if you want to build the server-level equivalent of a mirroring RAID, you could of course put two members of different shards on the same physical machine.

  i believe i need to start only 1 mongos instance

You only need one, but to avoid a single point of failure, you might want to have more than one.

  Similarly I need to have single mongo arbiter for all 3 shards instead of 3 on each shard?

An arbiter can only be member of a single replica-set. So you need 3 arbiters. However, the arbiters are very lightweight. They don't hold any data and don't do anything unless there is a primary election. So you do not need a dedicated server for the arbiter processes.


How to add / create table in the current database in mysql?

Is this Magento?  I had a similar issue where the plugin I was using wasn't compatible with the version of Magento that was installed.

Check your plugin compatibility then if you're running this on a hosted platform, use phpMyAdmin to verify that the table actually exists.

Also, have you contacted the Magento forums to see if they can help?


Database replication and consistency check

Your options are rather limited because of your requirements to "constantly check ... sync" and "can't make any change in A".  Things such as materialized view logs, dbms_alert, streams, and a standby database are all off the table.

If the tables in A are constantly having all of their rows updated then (as Jack Douglas said) a materialized view would be the easiest to setup.  In the more likely event that most of the records don't change in A from moment to moment, you will probably want to setup a package (or packages) on B that select from A to merge and delete as necessary on B.  This will only be as up to date as the frequency in which it is run, but given your requirements it may be the best you can do.

Specifically, your package should do the following:

Delete from B rows that do not exist in A.
Merge A into B updating when matched and inserting when not matched.

If you want to avoid hitting the table in A multiple times you could insert the entirety of the table into a global temporary table on B and then do your Delete/Merge from there.

Concerning Minus:
Minus can tell you all the rows from a query of A that are not in B.  By union-ing this with B minus query of A you can get all rows that are different, but this would probably take longer to process even before adding the insert/update part.  If A doesn't get updates or deletes then you could insert the results of the first minus, but an insert into B...where not exists A... would still be faster and simpler.


How to identify what is causing unusual high CPU load in MySQL?

The slowlog.  Granted, this picks up on both high CPU and high I/O, but still it will give you what you are looking for.

Suggestions on digesting, etc: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

Often the 'cure' is an appropriate composite index.


Unattended install of SQL Server 2008 R2 cluster fails with error - "Illegal characters in path."

Nicholas Cain (@SirSQL on Twitter) published three blogs recently regarding the installation of SQL on a WIndows Failover Cluster using PowerShell to script the activity.

The first of the three blog entries may be found at http://sirsql.net/blog/2011/7/11/creating-a-clustered-sql-install-ini-file-using-powershellpa.html

The second of the three blog entries may be found at http://sirsql.net/blog/2011/7/13/creating-a-clustered-sql-install-ini-file-using-powershellpa.html

Finally, the third blog entry may be found at http://sirsql.net/blog/2011/7/18/creating-a-clustered-sql-install-ini-file-using-powershellpa.html

His approach uses PowerShell and it might give you some ideas.


What should I set max_connections to for MySQL?

There's no formula here. You should limit your database according to what you think is reasonable according to your application needs.

Typically, servers with applications using a connection pool shouldn't need more than a few hundred concurrent connections. Small-Medium sized websites may suffice with 100 - 200.

I usually setup a new server with some 500 - 800 value of max_connections and see how it goes. You can always change dynamically via

set global max_connections := 567;

Make sure, though, you set up a proper open_files_limit. On linux, your process is limited to 1024 files, by default. This is very low, since every thread, connection, and, of course, file -- make for a file handle in linux. So set open_files_limit to some generous number (say 8192) to clear up your many connections with the operating system.

I should note I have worked with MySQL servers with thousands of open connections - it's cool. But, most of the time, the vast majority of these connections would sit and do nothing (be idle).

To sum up, I would use what appears to be normal application needs + some threshold for spike events.


Because SQL Server released the memory back to the OS, this means the memory pressure was external, so you aren't going to find the reason within SQL Server.  Your answer is going to come from outside SQL Server and I know of one tool that can track the memory allocations of the OS.

Rammap  https://technet.microsoft.com/en-us/sysinternals/rammap.aspx


SQL 2012 insert data into a table from another table and custom columns

The obvious answer is that those columns aren't in Table1 either.  Otherwise I don't see an obvious reason why that would fail.  It looks like you're adding data to the table that could easily be captured by a default value.  See part B for an example of using SYSTEM_USER as a default value or just see the code shamelessly copied below:

CREATE TABLE Sales.Sales_Tracking
(
    Territory_id int IDENTITY(2000, 1) NOT NULL,
    Rep_id  int NOT NULL,
    Last_sale datetime NOT NULL DEFAULT GETDATE(), -- Your date info
    SRep_tracking_user varchar(30) NOT NULL DEFAULT SYSTEM_USER -- System user
);

By setting these as a default value on Table2 you might just be able to add those four columns to Table2 without having to bother with the insert.  Alternatively you can set the date and user columns to default to GETDATE() and SYSTEM_USER respectively on Table1.  That would eliminate the need to specify those values in your insert statement, and give you good defaults moving forward from the looks of things.


How to check how the table structure effects the performance of my query

Procedure Analyse will be helpful command: http://dev.mysql.com/doc/refman/5.0/en/procedure-analyse.html

SELECT *
FROM `table_name`
PROCEDURE ANALYSE ( )


How to handle update or insert situations

A slightly more efficient way (which will do at worst one seek/scan instead of two against the existing data):

UPDATE dbo.whatever SET ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.whatever ...
END

MERGE may be tempting, however there are a few reasons I shy away from it.

the syntax is daunting and hard to memorize
you don't get any more concurrency than the above approach unless you intentionally add specific locking hints
there are many unresolved bugs with MERGE and probably many more that have yet to be uncovered


How does unique key help to improve SQL query performance?

Since the Stack Overflow post you linked to explains how the unique key constraint is implemented using a unique index, so the two are the same for practical purposes, I'll skip that part and try to explain how a unique index improves query performance.

Let's stick to your example where table named myTable has a column named id which happens to be filled with unique values, but no unique index on that column. Let's also suppose that the table has another column, anotherCol.

Now someone comes and issues a query like

select * from myTable where id = 15

The optimizer must decide how to find all the rows satisfying the condition id = 15. It may know, from the statistics, that values in the column are unique (num distinct = num rows in the table), however, that doesn't provide any information on where the row with id = 15 is physically located on the disk. So, the database has no choice but to scan the entire table to find the matching row. It can't even stop once it finds the first row with id = 15, since there's no guarantee that there aren't more such rows.

Now, we create a unique index on column id, and repeat the same query.

Things are radically different now. The optimizer now knows that there are only two possibilities: either there is exactly one row that satisfies the condition, or there are no such rows. So, a cheap index seek is all that is needed to find the required row if it exists; the same index seek will return no results if there are no such rows. So, the query will run fast.

Suppose there is also a (normal, non-unique) index on anotherCol, and you issue a query like

select * from myTable where id = 15 and anotherCol = 100

The optimizer can now choose between using the index on id, index on anotherCol, and a table scan. It will choose the most selective method, because it requires the least work to get to the results - so it will use the unique index, again.

In general, whenever your query has where clause predicates that are joined with AND (a common case) and any of the columns in the where clause has a unique index on it, the optimizer will chose that index as the access method and the query will run fast.

The only case when the unique index will not be  used is when it cannot be used, for example when a query like

select * from myTable where id = 15 or val = 100

is issued. Since the condition between the predicates is OR, the fact that we can find the row where id = 15 fast doesn't mean much since we still have to find other rows where val = 100. If val were not indexed, a table scan would be the only solution (rendering unique index seek useless, as the row where id = 15 would be picked up by the table scan anyway); since val is indexed, the optimizer might opt to use both indexes to find rows satisfying each condition individually and then concatenate the results.

Hopefully this makes things a bit clearer to you.


How to identify database users and their owned schemas before dropping server logins?

The query below will return the schema owner information.  You can change the JOIN to sys.database_principals to a LEFT JOIN to also return schema owners that are not logins, such as database roles and users without a login.

SELECT
      sp.name AS OwningLogin
    , dp.name AS OwningUser
    , s.name AS SchemaName
FROM sys.schemas AS s
JOIN sys.database_principals AS dp ON
    dp.principal_id = s.principal_id
JOIN sys.server_principals AS sp ON
    sp.sid = dp.sid;


You can only add references to those assemblies which have been registered with Sql Server. If they are not registered, they will no show up in the Add References dialog.

There are a number of steps you'll need to do register a DLL, firstly you'll need to reconfigure your database:

ALTER DATABASE [MyDatabase] SET TRUSTWORTHY ON;
sp_configure 'clr enabled', 1;
RECONFIGURE;

Once this is done, Sql Server is CLR enabled. Next, you'll need to register your assembly:

CREATE ASSEMBLY [MyAssembly] AUTHORIZATION [MyUser]
FROM 'C:\CLR\MyAssembly.dll'
WITH PERMISSION_SET = SAFE

If this last script runs correctly, the assembly is now registered, and will appear in the Add References dialog.

What you will need to consider though, is the application security of your Sql Server CLR configuration:

Prefer to register an assembly as SAFE, only in exceptional circumstances should you use EXTERNAL_ACCESS or UNSAFE.
Don't expect to be able to do everything you can on Full-trust CLR (i.e., not the CLR hosted by Sql Server) - the SQLCLR is a sandboxed runtime.
Don't try and load assemblies dynamically, as Assembly.Load() is purposefully restricted.
You may need to ensure the 3rd party library is signed with a public key if you plan on using UNSAFE.
Code executing runs in the context of the identity of the service running Sql Server (I think!)
Database access made from a hosted assembly (e.g. via context connection = true;) runs in the context of the connected user, so you need to make sure you are aware what access that library has to your data.


Does SQL Server Express 2016 come with SQL Server Configuration Manager?

If you already installed SQL Server 2016 Express Edition, you can find configuration manager here:

Computer Management > Services and Applications > SQL Server Configuration Manager


The login already has an account under a different user name

This means that the login [R2Server\AAOUser] is already mapped to a user in that database. Or, in other words, another database user is using this login. You can see what database user is using your login with the following query:

use YourDB
go
SELECT su.name as DatabaseUser
FROM sys.sysusers su
join sys.syslogins sl on sl.sid = su.sid
where sl.name = 'test' -- login

PS: a version of the script that doesn't use the compatibility views:

Select sp.name as LoginName, sp.type_desc as LoginType,
    dp.name as DBUser, dp.type_desc as UserType
from sys.server_principals sp
join sys.database_principals dp on dp.sid = sp.sid
where sp.name = 'test' -- your login


Single node SQL Server 2014 cluster at DR environment

Overall, there is no need for a cluster IMHO. If you need HA at the DR site, then do it - but if you don't, I don't see a purpose for it as the stand alone will work just fine. The reason I say this, is that they will be two distinct clusters which makes it a little hard to justify.

  Tech person from this cloud company said that single node cluster is
  complicated solution and is prone to all kinds of problems like "split
  brain", that it can stuck all of a sudden and they have seen so many
  issues with single cluster nodes.

So I don't know if the tech person didn't understand, or what, but you can't split brain with a single node... so, I don't know why (s)he thought that, but that's incorrect. There really aren't any issues with a single node cluster other than it's a single node cluster which defeats the point of having a cluster TBH. It's a different story if it'll eventually be built out, but if you're going to wait for a disaster to do so... you're already behind the ball.

Keeping just a single instance, not in a cluster would be faster to replicate as well should something happen.

In the end it's up to you, unless I had a much more compelling reason I'd go with just a single server without clustering. Also, don't listen to their techs anymore ;)


MySQL privileges for roles aren't applying to users?

The problem (it seems) was that MySQL didn't like the capital letters in my procedure names.  I went though the entire script and renamed all of my procedures with lower case letters.  I updated the roles accordingly, and then it worked properly.


what are the different ways to specify the values in PostgreSQL?

If the values are obtained from a user input, 2) and 3) are a bad idea.

You shouldn't put user supplied values directly into the SQL string, but use a prepared statement with bind variables. Not doing that, makes your application vulnerable to SQL Injection

How you use a prepared statement with bind variables depends on the programming language you are using.


How to store multiple data types for same column (for many columns)

I'm the author of the book you referenced. Thanks for reading my book!
I suggest you read chapter 14, "Fear of the Unknown."
For your case:

I am entering the quantity of elements detected in a sample and, if the quantity of an element is not detected, then I must be able to identify that an element was tested, but not detected, such as "ND".

I would not store the string value "ND". I would store NULL instead of a number when the value is unknown or inapplicable.
Then when you query it, you can use:
SELECT COALESCE(myNumericColumn1, 'ND') AS myNumericColumn1
FROM Mytable
...;

The COALESCE() function in SQL returns its first argument that is not NULL. In this example, if the numeric is NULL on a given row, the result will use the default value in the second argument, 'ND'.
You can also create a VIEW with this expression for each column, so you don't have to repeat it in every query.

Re your comment:
This is starting to sound like you need to store the results in rows, not _columns. One numeric result per row. Just two three columns:

Sample ID
Element ID
Numeric value

Then you can make a distinction between three states:

Presence of a row with a non-NULL numeric value.
Presence of a row, but NULL for the numeric value, indicating the test was done but the result was Not Detected.
Absence of a row, indicating the element test was not done.

When you want your client to display the results for a sample, you would fetch 30 rows (or fewer if not all elements were tested in a given sample), then write code in your client to loop over the 30 rows, and present the data however you want.


pg_dump and ERROR: missing chunk number 0 for toast value

Given that what you did was a reindex, what probably happened was it used an index scan to try to locate the toasted values in the table and couldnt find one.  This sounds like a corrupted index.  Vacuum analyse does alter the table but reindex does not and the changes are very minor.

The way to think about this is that TOASTed attributes are actually broken into chunks of about 4k in size and these are stored in rows.    They are looked up and sorted/reconnected with the main row at query time.  It sounds like an index used here was corrupted and so the reindex solved the problem.

I have found corrupted indexes are usually a sign that something is not well with the server.  It is good to check and make sure memory, CPU's and hard drives are all happy and not reporting problems.  I have found overheating servers to be particularly prone to index corruption and if indexes can get corrupt one has to worry about data becoming corrupt too.


Yes.

select *
from pg_stat_activity
where pid = pg_backend_pid();

client_hostname And client_addr are relevant.

log_hostname needs enabling in order for the name to be resolved correctly. See this link:

  By default, connection log messages only show the IP address of the
  connecting host. Turning this parameter on causes logging of the host
  name as well. Note that depending on your host name resolution setup
  this might impose a non-negligible performance penalty. This parameter
  can only be set in the postgresql.conf file or on the server command
  line.


Merging two selects then sort by and remove duplicates

UNION is definitely the way to "stack" rowsets on top of each other, while removing duplicates. (Use UNION ALL to keep duplicates.)

Your queries appear to have some cleanup possible, so we'll do that as well.

Observations:

In the first query, the extra join to UM AS MUM seems unnecessary because no columns are used from that table, and due to the likely uniqueness of UMKEY it won't have multiple matching rows, so there is no effect at all on the final query. I'm removing this join.
The second query's two parts are almost the same and could be combined by using the single join condition ON DF.DFKEY IN (ST.FAMILY, ST.FAMB). Depending on your indexes and other factors, simplifying this way could be okay performance or could be worse. Or, querying twice as you're doing could be best. Instead, I've chosen a way that has at times beaten the performance of both of those for me. (And for what it's worth, my guess is that these two columns represent denormalized data, and refactoring a bit to put the family information into a separate table could be a sensible way to go forward—what if you had FAMC, FAMD, FAME, and so on?)
UM.INTERNATIONAL != 'Y' effectively makes the join to UM an INNER JOIN. So we'll use INNER JOIN to that table.
For what it's worth, the extra and unneeded parentheses add clutter, not clarity (to me, anyway), so I'll be removing them in my answer.
Some of the duplication in each query can be made common, such as the join to UM being the same.
I would encourage you to, where possible, use equality instead of inequality, as it's easier for the mind to comprehend (especially don't use double negatives). If your Y/N columns do not have NULLs and only contain the values Y and N, I recommend switching from OS.DECEASED != 'Y' to OS.DECEASED = 'N' and from UM.INTERNATIONAL != 'Y' TO UM.INTERNATIONAL = 'N'. This is probably merely a matter of style as it likely makes no difference to the query engine (depending on its cardinality estimates and other factors), though there is the possibility that an equality predicate could in some situations perform better than an inequality one.

I believe this query will return exactly the same rowset as you get with the other answers, but it has the potential for running in 1/2 to 1/3rd the time.

SELECT
   UM.UMKEY,
   M.MAILTITLE,
   UM.ADDRESS01,
   UM.ADDRESS02,
   UM.ADDRESS03
FROM
   (
      SELECT
         OS.MAILTITLE,
         OS.HOMEKEY
      FROM
         OS
      WHERE
         OS.OS_EMAIL != ''
         AND OS.DECEASED != 'Y'
         AND OS.GENDER  = 'F'
         AND OS.MAIL_LIST = 'Y'
      UNION
      SELECT
         DF.MAILTITLE,
         DF.HOMEKEY
      FROM
         DF
         INNER JOIN (
            SELECT
               FAMILYKEY = CASE X.WHICH WHEN 0 THEN ST.FAMILY ELSE ST.FAMB END
            FROM
               ST
               CROSS JOIN (SELECT 0 UNION ALL SELECT 1) X (WHICH)
            WHERE
               ST.STATUS = 'FULL'
         ) ST
            ON DF.DFKEY = ST.FAMILYKEY
   ) M
   INNER JOIN UM
      ON M.HOMEKEY = UM.UMKEY
WHERE
   UM.INTERNATIONAL != 'Y'
ORDER BY
   UM.UMKEY
;

If you are using a DBMS that supports syntax such as CROSS APPLY (SQL Server) or UNPIVOT, the rows from the ST table can be handled in possibly a slightly more efficient manner. For example, the innermost INNER JOIN could look like this in SQL Server:

INNER JOIN (
   SELECT
      F.FAMILYKEY
   FROM
      ST
      CROSS APPLY (VALUES
         (ST.FAMILY),
         (ST.FAMB)
      ) F (FAMILYKEY)
   WHERE
      ST.STATUS = 'FULL'
) ST
   ON DF.DFKEY = ST.FAMILYKEY


Why the seek is not chosen by the optimizer

TL:DR The expanded computed column definition interferes with the optimizer's ability to reorder joins initially. With a different starting point, cost-based optimization takes a different path through the optimizer, and ends up with a different final plan choice.

Details
For all but the very simplest of queries, the optimizer does not attempt to explore anything like the whole space of possible plans. Instead, it picks a reasonable-looking starting point, then spends a budgeted amount of effort exploring logical and physical variations, in one or more search phases, until it finds a reasonable plan.
The main reason you get different plans (with different final cost estimates) for the two cases is that there are different starting points. Starting from a different place, optimization ends up at a different place (after its limited number of exploration and implementation iterations). I hope this is reasonably intuitive.
The starting point I mentioned, is somewhat based on the textual representation of the query, but changes are made to the internal tree representation as it passes through the parsing, binding, normalization, and simplification stages of query compilation.
Importantly, the exact starting point depends heavily on the initial join order selected by the optimizer. This choice is made before statistics are loaded, and before any cardinality estimations have been derived. The total cardinality (number of rows) in each table is however known, having been obtained from system metadata.
The initial join ordering is therefore based on heuristics. For example, the optimizer tries to rewrite the tree such that smaller tables are joined before larger ones, and inner joins come before outer joins (and cross joins).
The presence of the computed column interferes with this process, most specifically with the optimizer's ability to push outer joins down the query tree. This is because the computed column is expanded into its underlying expression before join reordering occurs, and moving a join past a complex expression is much more difficult than moving it past a simple column reference.
The trees involved are quite large, but to illustrate, the non-computed column initial query tree begins with: (note the two outer joins at the top)

LogOp_Select
    LogOp_Apply (x_jtLeftOuter)
        LogOp_LeftOuterJoin
            LogOp_NAryJoin
                LogOp_LeftAntiSemiJoin
                    LogOp_NAryJoin
                        LogOp_Get TBL: dbo.table1(alias TBL: a4)
                        LogOp_Select
                            LogOp_Get TBL: dbo.table6(alias TBL: a3)
                            ScaOp_Comp x_cmpEq
                                ScaOp_Identifier QCOL: [a3].col18
                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                        LogOp_Select
                            LogOp_Get TBL: dbo.table1(alias TBL: a1)
                            ScaOp_Comp x_cmpEq
                                ScaOp_Identifier QCOL: [a1].col2
                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                        LogOp_Select
                            LogOp_Get TBL: dbo.table5(alias TBL: a2)
                            ScaOp_Comp x_cmpEq
                                ScaOp_Identifier QCOL: [a2].col2
                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a4].col2
                            ScaOp_Identifier QCOL: [a3].col19
                    LogOp_Select
                        LogOp_Get TBL: dbo.table7(alias TBL: a7)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a7].col22
                            ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a4].col2
                        ScaOp_Identifier QCOL: [a7].col23
                LogOp_Select
                    LogOp_Get TBL: table1(alias TBL: cdc)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [cdc].col6
                        ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=4)
                LogOp_Get TBL: dbo.table5(alias TBL: a5)
                LogOp_Get TBL: table2(alias TBL: cdt)
                ScaOp_Logical x_lopAnd
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a5].col2
                        ScaOp_Identifier QCOL: [cdc].col2
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a4].col2
                        ScaOp_Identifier QCOL: [cdc].col2
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [cdt].col1
                        ScaOp_Identifier QCOL: [cdc].col1
            LogOp_Get TBL: table3(alias TBL: ahcr)
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [ahcr].col9
                ScaOp_Identifier QCOL: [cdt].col1

The same fragment of the computed column query is: (note the outer join much lower down, the expanded computed column definition, and some other subtle differences in (inner) join ordering)

LogOp_Select
    LogOp_Apply (x_jtLeftOuter)
        LogOp_NAryJoin
            LogOp_LeftAntiSemiJoin
                LogOp_NAryJoin
                    LogOp_Get TBL: dbo.table1(alias TBL: a4)
                    LogOp_Select
                        LogOp_Get TBL: dbo.table6(alias TBL: a3)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a3].col18
                            ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                    LogOp_Select
                        LogOp_Get TBL: dbo.table1(alias TBL: a1
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a1].col2
                            ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                    LogOp_Select
                        LogOp_Get TBL: dbo.table5(alias TBL: a2)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [a2].col2
                            ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a4].col2
                        ScaOp_Identifier QCOL: [a3].col19
                LogOp_Select
                    LogOp_Get TBL: dbo.table7(alias TBL: a7)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [a7].col22
                        ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=16)
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [a4].col2
                    ScaOp_Identifier QCOL: [a7].col23
            LogOp_Project
                LogOp_LeftOuterJoin
                    LogOp_Join
                        LogOp_Select
                            LogOp_Get TBL: table1(alias TBL: cdc)
                            ScaOp_Comp x_cmpEq
                                ScaOp_Identifier QCOL: [cdc].col6
                                ScaOp_Const TI(smallint,ML=2) XVAR(smallint,Not Owned,Value=4)
                        LogOp_Get TBL: table2(alias TBL: cdt)
                        ScaOp_Comp x_cmpEq
                            ScaOp_Identifier QCOL: [cdc].col1
                            ScaOp_Identifier QCOL: [cdt].col1
                    LogOp_Get TBL: table3(alias TBL: ahcr)
                    ScaOp_Comp x_cmpEq
                        ScaOp_Identifier QCOL: [ahcr].col9
                        ScaOp_Identifier QCOL: [cdt].col1
                AncOp_PrjList
                    AncOp_PrjEl QCOL: [cdc].col7
                        ScaOp_Convert char collate 53256,Null,Trim,ML=6
                            ScaOp_IIF varchar collate 53256,Null,Var,Trim,ML=6
                                ScaOp_Comp x_cmpEq
                                    ScaOp_Intrinsic isnumeric
                                        ScaOp_Intrinsic right
                                            ScaOp_Identifier QCOL: [cdc].col4
                                            ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=4)
                                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=0)
                                ScaOp_Const TI(varchar collate 53256,Var,Trim,ML=1) XVAR(varchar,Owned,Value=Len,Data = (0,))
                                ScaOp_Intrinsic substring
                                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=6)
                                    ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=1)
                                    ScaOp_Identifier QCOL: [cdc].col4
            LogOp_Get TBL: dbo.table5(alias TBL: a5)
            ScaOp_Logical x_lopAnd
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [a5].col2
                    ScaOp_Identifier QCOL: [cdc].col2
                ScaOp_Comp x_cmpEq
                    ScaOp_Identifier QCOL: [a4].col2
                    ScaOp_Identifier QCOL: [cdc].col2

Statistics are loaded and an initial cardinality estimation is performed on the tree just after the initial join order is set. Having the joins in different orders also affects these estimates, and so has a knock-on effect during later cost-based optimization.
Finally for this section, having an outer join stuck in the middle of the tree can prevent some further join reordering rules matching during cost-based optimization.

Using a plan guide (or, equivalently a USE PLAN hint - example for your query) changes the search strategy to a more goal-oriented approach, guided by the general shape and features of the supplied template. This explains why the optimizer can find the same table1 seek plan against both computed and non-computed column schemas, when a plan guide or hint is used.
Whether we can do something differently to make the seek happen
This is something you only need to worry about if the optimizer does not find a plan with acceptable performance characteristics on its own.
All the normal tuning tools are potentially applicable. You can, for example, break the query up into simpler parts, review and improve the available indexing, update or create new statistics...and so on.
All these things can affect cardinality estimates, the code path taken through the optimizer, and influence cost-based decisions in subtle ways.
You may ultimately resort to using hints (or a plan guide), but that's not usually the ideal solution.

Additional questions from comments

I agree that it's best to simplify the query etc., but is there a way (trace flag) to make the optimizer continue with the optimization and reach the same result?

No, there's no trace flag to perform an exhaustive search, and you don't want one. The possible search space is vast, and compilation times that exceed the age of the universe would not be well-received. Also, the optimizer doesn't know every possible logical transform (no one does).

Also, why is the complex expansion needed, as the column is persisted? Why can't the optimizer avoid expanding it, treat it like a regular column, and reach the same starting point?

Computed columns are expanded (like views are) to enable additional optimization opportunities. The expansion may be matched back to e.g. a persisted column or index later in the process, but this happens after the initial join order is fixed.


How would I build a data model for parking lot hours and respective parking fees?

Depending on how many parking lots you're operating, I wouldn't be too worried about how many records you have to manage.  It's not like this data would be fast changing, since you'd probably have to get signs printed up with the prices marked and so on.  Still, assuming that you have many lots (many of which have the same pricing) and that you want to minimize data maintenance, you could build a price schedule and map lots to the schedule.

Consider something like this:

ParkingLot -
    ParkingLotId
    Name
    Address

PriceSchedule -
    PriceScheduleId
    Description

PriceScheduleDetail -
    PriceScheduleDetailId
    PriceScheduleId
    DayOfTheWeek
    StartTime
    EndTime
    DollarsPerUnit
    MinutesPerUnit
    MinutesMinimumStay

ParkingLotPrice -
    ParkingLotId
    PriceScheduleId
    EffectiveDate
    ExpiryDate

Note that the PriceScheduleDetail table allows you to keep two or more versions of a price for a given time of day range.

Note also that the ParkingLotPrice table uses effective and expiry dates to link parking lots to a price table by date range.  This allows you to set up a price change in advance so you don't need to load new data in the middle of the night when a price change takes effect.

You're queries for pricing are going to have to find the version of the price that applies based on the minimum stay length, which is much more complicated than just "What is the price right now?" since it will depend on how long a stay is and which is the best price that the customer qualifies for.  Also, the price a customer pays will depend on how long their stay is and it may cross one or more price point boundaries.


Locale is incompatible error in Postgresql 10 when replicating from Ubunu to Win 10

You can't copy a data directory (which is what pg_basebackup does) between two different operating systems. The missing locale is just one symptom. If you need to replicate between Linux and Windows use logical replication – a_horse_with_no_name


Does missing logout event in SQL Server trace mean connection leak?

Great question! To find out if the connection is still open right now, look in sys.sysprocesses:

SELECT * FROM sys.sysprocesses;

Generally, as long as they're not doing anything, open connections aren't a problem for SQL Server - it can handle tens of thousands. Connection pooling - keeping a connection open and ready for reuse - is fine, and doesn't consume that many resources on a SQL Server.

I'm generally more worried about sleeping sessions - someone who started a request minutes ago and didn't finish - and you can find those with Adam Machanic's free sp_WhoIsActive:

sp_WhoIsActive @ShowSleepingSpids = 1


running many CREATE VIEW and GRANT SELECT statements in one execution

Multiple CREATE VIEW statements can be executed together if you separate them with keyword 'GO'

CREATE VIEW vw_test1 AS SELECT name FROM dbo.test1;
GO
CREATE VIEW vw)test2 AS SELECT name FROM dbo.test2;
GO
GRANT SELECT ON vw_test....


How to track overhead of Change Tracking in SQL Server

http://technet.microsoft.com/en-us/library/hh710064.aspx talks about monitoring Change Tracking, however it neglects to mention anything about performance other than "Change Tracking is lightweight".

It seems you're out of luck...


What does CREATE ASSEMBLY do with the registered dll?

The bits are copied into the database where you call CREATE ASSEMBLY or ALTER ASSEMBLY.

The path you select in Management Studio is only a convenience for it to read the file and dump out the bits into a varbinary(MAX) value (hit the Script button in that dialog to see what I mean).

This also means that the assembly is "carried" with the database if you do something like backup/restore on another server.


Here is what you can do:

Old DB Server : 10.1.2.30
New DB Server : 10.1.2.40

STEP 01) On the Old Server, service mysql stop

STEP 02) On the Old Server, rsync -av --progress /var/lib/mysql root@10.1.2.40:/var/lib/mysql

STEP 03) On the New Server, chown -R mysql:mysql /var/lib/mysql

STEP 04) On the New Server, service mysql start

Make sure /var/lib/mysql on the New Server is on a much bigger disk mount


Executing a table type parameter stored procedure or function within a select clause

The way you're doing it is about the fastest, easiest and most comfortable way you're going to find to pass a TVP to a stored procedure, sorry. You can't pass expressions to stored procedure parameters, even simple things like:

EXEC sp_who2 N'act'+'ive';

Fails with:

  Msg 102, Level 15, State 1, Line 1
  Incorrect syntax near '+'.

In order to use an expression, it needs to be:

DECLARE @expression SYSNAME = N'act'+'ive';
EXEC sp_who2 @expression;


I/O is frozen on Database & also I/O was resumed on Database on daily basis in log with complete Backup

I believe its just a message and not an error unless and until the task is getting impacted when this message is logged in the error log:

We also encountered the same for one of our production server, where windows admin team enabled the SQL Server backup through VSS (Volume Shadow Copy Services). And when this process runs it freezes I/O temporarily to take shadow copy and release it back once process is complete.

Major cause being "I/O on the database is frozen in order for any backup service using Virtual Device Interface (VDI)  and commiting a sql  snapshot backup."
as mentioned here

For a better understanding how this process works please read How It Works: SQL Server - VDI (VSS) Backup Resources2

Therefore i believe you should start looking for process like Volume Shadow Copy (VSS) OR Data Protection Manager (DPM) running on the server and discuss with system admin team for what is enabled and to ensure nothing is impacted.


Counting rows in a table based on subtable result

select count(customer) from
(
    select customer from table
    group by customer
    having sum(value)<>0
) X


Disabling Indexes and Auditing for large data import

Disabling indexes is a popular method when inserting a bulk amount of data.  So if you are looking to get this done in the shortest amount of time (your window isn't long enough for the with-index import??), simply do this:

alter index YourNonClusteredIndex
on YourDestinationTable
disable
go

Do this for your nonclustered indexes.

Granted, when you disable your indexes (provided you want to continue to use them) to enable then you'll need to rebuild or recreate them.  So there is that time-consuming side effect.

Another consideration pointed out by @JNK:

  Just disabling indexes on the table may be an issue if he uses any of those indexes to find the row to update or check for a previously inserted row.

In other words, whatever you choose you should be testing to ensure you're getting the gains you are looking for.

As for auditing, you need to be more specific on what type of auditing you are using.  If you have triggers on your table, and that is your auditing design then you can also disable all of the triggers on your table by doing the following:

disable trigger all
on YourDestinationTable
go

Disabling the triggers on a table could potentially save you a lot of time on your insert (or whatever action fires the trigger[s]).


Selected column in a query with GROUP BY can either be one of the "grouping" columns, functionally dependent on those or an aggregate. I covered the functional dependency just yesterday in this answer. question_id does not fulfill any of these so putting it in there leads to "undefined behavior".

To show what really happens I prepared sql fiddle where you can see that the first query does not return anything and second query shows the details of what actually happens - by removing the HAVING and adding some columns to the SELECT I show what values are assigned to which columns and expressions:

+---------+-------------+
| test_id | question_id |
+---------+-------------+
|       6 |          54 |
|       6 |         300 |
+---------+-------------+

SELECT test_id,
    question_id,
    max(question_id),
    group_concat(question_id)
FROM test
WHERE test_id = 6
GROUP BY test_id;

+---------+-------------+------------------+---------------------------+
| test_id | question_id | max(question_id) | group_concat(question_id) |
+---------+-------------+------------------+---------------------------+
|       6 |          54 |              300 | 54,300                    |
+---------+-------------+------------------+---------------------------+

For test_id = 6 we have two rows with question_id of 54 and 300, because of the GROUP BY we can only select one value per column for a given test_id

max() is trivial, it selects 300
group_concat() is aggregating function which gets all possible values for given column in a group and makes a comma separated list of them
question_id is the interesting case - it can show only one value but two exist for the given test_id - so one is picked "randomly" - as we can see, mysql picks the first one it gets - group_concat shows the internal "ordering", but it depends on many things, one of which is index used - thats why removing the WHERE changes the results such way, different index is used and so different number is picked for this column

The same values are very probably used when you write the same columns and expressions to the HAVING clause and as you can see question_id != max(question_id) because 54 != 300.

To solve this task properly some "groupwise-max" solution is needed. It is even covered in the MySQL manual which shows 3 possible solutions - correlated subquery, derived table and left join. Which one is the best depends on specific case - the use of subquery can be faster than the left join for some data and slower for others. From my experience if you have few groups with lot of items then subquery is generally better whereas the left join performs better with many smaller groups, but you can test them all and see for yourself.

Edit: As other answers show, if you select only test_id and question_id, then the max(question_id) and GROUP BY test_id are enough. I added the previous paragraph because you mentioned in some version of the question that your friend has some "problem" with subqueries etc, so I supposed the actual problem is not as simple as this.


How can I dump the schema of a SQL server database on Linux?

Check out mssql-scripter. It's cross-platform and created by Microsoft. It has a bunch of options to control how the scripting happens.