How to use a select pivot query to update two different tables

I'm not 100% sure to understand your question but if you are looking at doing and UPDATE from a SELECT, then you may want to look at this.


Postgres Plus Cloud Database on AWS for write heavy social game?

For high performance systems, I think virtualization usually causes a lot more problems than it solves.  Save yourself the headache.  Build a decent db server inhouse.

It is hard to say what your current problem is, and virtualization makes troubleshooting much harder.  Is the load cpu bound?  Is it I/O bound?  Are you having concurrency issues?  Something else?

Now, if your 8k queries every 5 sec are not very complex queries, then the immediate question is why are you having performance problems.  It may just take some db tuning.  I have seen some indications that you should be able to get several times that on good hardware if things are well tuned.  Again virtualization is probably making things harder to fix rather than easier.

Finally, I would highly recommend looking into Postgres-XC.  It allows you to do sharding for write-extensibility while managing concurrency and consistency a layer above.   I would start tuning what you have and move from there though.


I reproduced this using the code below, and essentially, yes, if you wait long enough, and auto cleanup is on, it will clean itself out. It appears to be bound the a minimum - that is, the change_retention setting is more of a suggestion than a command.

Admittedly, I haven't tested with real-world table activity, etc., but the links below contain more information about what the job does - it's more of a black box, really, than anything.

Links:

MS Change Tracking

Kendra Little on Change Tracking

Song Lyrics - Courtesy of Disney

Here's what I came up with.

use master;
go

drop database cttest;
go

create database cttest;
go

use cttest;
go

create table dbo.t
(id int identity(1,1) primary key--can't track without it
, c1 int
, c2 varchar(100));
go

-- pre ct
insert t values (123, 'Yo ho, yo ho')
insert t values (234, 'We pillage, we plunder, we rifle and loot.')
insert t values (345, 'Drink up me ''earties, yo ho.')
insert t values (456, 'We kidnap and ravage and don''t give a hoot.')
insert t values (567, 'Drink up me ''earties, yo ho.')
go

-- check the table
select * from t;
go

-- enable change tracking for this DB
alter database cttest set change_tracking = on (change_retention = 1 minutes)
go

alter table dbo.t enable change_tracking with (track_columns_updated=off);
go

-- this should be a tracked change
insert t values (678, 'Yo ho, yo ho, a pirate''s life for me.');
go

-- show the change tracking record
-- so i waited 10 minutes, and then...
select * from changetable(changes dbo.t, NULL) as ct;
go

-- is this table even tracked ?
SELECT object_name(object_id),* FROM sys.change_tracking_tables

-- make another change
update t set c2 = 'Yo ho, yo ho, a pirate''s life for me.' where c1 = 123;
go

-- show the change tracking record
select * from changetable(changes dbo.t, NULL) as ct;
go

-- make more changes
insert t values (789, 'We extort and pilfer, we filch and sack.')
insert t values (8910, 'Drink up me ''earties, yo ho.')
insert t values (91011, 'Maraud and embezzle and even highjack.')
insert t values (101112, 'Drink up me ''earties, yo ho.')
go

-- show the change tracking record
select * from changetable(changes dbo.t, NULL) as ct;
go

-- make another change
update t set c2 = 'Yo ho, yo ho, a pirate''s life for me.' where c1 = 123;
go 15

-- show the tracked items
select * from changetable(changes dbo.t, NULL) as ct;
go

-- force a <ahem> flush
EXEC sp_flush_commit_table_on_demand 100000;
go

/*
Received message
(0 row(s) affected)
The Change Tracking manual cleanup procedure for database cttest has been executed
*/

-- show the tracked items
-- still there !
select * from changetable(changes dbo.t, NULL) as ct;
go

-- ok then, we'll turn it off !
alter database cttest set change_tracking (auto_cleanup=off);
go

-- force a <ahem> flush
EXEC sp_flush_commit_table_on_demand 100000;
go

-- show the tracked items
-- still there !
select * from changetable(changes dbo.t, NULL) as ct;
go

-- ok then, we'll turn it back on, and wait
alter database cttest set change_tracking (change_retention = 1 minutes, auto_cleanup = on)
go


Oracle, Is there a way to request a lock with alter table drop partition?

Just user the ddl_lock_timout feature/parameter (11g+):

alter session                set ddl_lock_timeout=1000000; -- long long time
alter table SCH.DATA_TABLE   drop partition SYS_P1026632 ;

The value for ddl_lock_timeout means seconds to wait before the ORA-00054 is raised or the command completes (which is desirable...). Te default is 0 (which means no wait).

DDL With the WAIT Option (DDL_LOCK_TIMEOUT)


MSSQL - How to log every rpc_completed event into a table?

You can still do this with SQL Server Profiler
when the trace properties window appears check the box - save to table, then supply a server & database plus define a tablename
I don't think extended events offers this functionality in a synchronous manner (you can save to a table once you have collected your data into a ring buffer or file - but you said you didn't want to do this).


If this were my project, I would run away from MS Access. A content management system or wiki would be a better choice - formatting and versioning are built in and you don't have to code it from the ground up.


permissions for yet another "ORA-06512: table or view does not exist" scenario

Well I have simulated your problem in my test environment. I found the issue with the table name(aaaa and bbbbb which belongs to z schema).
Though you have select privilege on these tables you must use schema name explicitly(z.aaaaa and z.bbbbb). The following is the test case.

SQL> create user z identified by z;

User created.

SQL> create user y identified by y;

User created.

SQL> create user x identified by x;

User created.

SQL> grant connect, resource to x,y,z;

Grant succeeded.

SQL> grant create any table to x;

Grant succeeded.

SQL> conn z/z
Connected.
SQL> create table aaaaa(id number, name varchar2(20));
Table created.

SQL> create table bbbbb(id number, address varchar2(20));
Table created.

SQL> grant select on bbbbb to y;
Grant succeeded.

SQL> grant select on aaaaa to y;
Grant succeeded.

SQL> grant select on aaaaa to x;
Grant succeeded.

SQL> grant select on bbbbb to x;
Grant succeeded.

SQL> conn / as sysdba
Connected.
SQL> grant create synonym to y;

Grant succeeded.

SQL> conn y/y
Connected.
SQL> create synonym aaaaa for z.aaaaa;

Synonym created.

SQL> create synonym bbbbb for z.bbbbb;

Synonym created.

//As you said you have renamed the synonyms.

SQL> rename aaaaa to a1;

Table renamed.

SQL> rename bbbbb to b1;

Table renamed.

SQL> conn x/x
Connected.
SQL> create or replace procedure xsql(sqlstring varchar2) as
begin
execute immediate sqlstring;
dbms_output.put_line(sqlstring);
end xsql;
/

Procedure created.

SQL> alter session set current_schema=y;

Session altered.

SQL> declare sql_code varchar2(2000):=
'create table basis as '||
'select basis.id, name, address from aaaaa basis join bbbbb basis_code on(basis.id=basis_code.id)';
begin
x.xsql(sql_code);
end;
/
declare sql_code varchar2(2000):=
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "X.XSQL", line 3
ORA-06512: at line 5

SQL> declare sql_code varchar2(2000):=
'create table basis as '||
'select basis.id, name, address from z.aaaaa basis join z.bbbbb basis_code on(basis.id=basis_code.id)';
begin
x.xsql(sql_code);
end;
/

PL/SQL procedure successfully completed.


Copying CSV file to temp table with dynamic number of columns?

Basics:

The 1st row of the CSV file has column names of defined format.
The PROGRAM clause of COPY and GET DIAGNOSTICS after COPY require Postgres 9.3+.
format() requires Postgres 9.1+
This works with pure standard Postgres - except for the head command that the shell is expected to provide. For Windows versions consider:

How to do what head, tail, more, less, sed do in Powershell?

Full automation

This function copies any table structure completely dynamically:

CREATE OR REPLACE FUNCTION f_dynamic_copy(_file    text
                                        , _tbl     text = 'tmp1'
                                        , _delim   text = E'\t'
                                        , _nodelim text = chr(127)) -- see below!
  RETURNS text AS
$func$
DECLARE
   row_ct int;
BEGIN
   -- create staging table for 1st row as  single text column
   CREATE TEMP TABLE tmp0(cols text) ON COMMIT DROP;

   -- fetch 1st row
   EXECUTE format($$COPY tmp0 FROM PROGRAM 'head -n1 %I' WITH (DELIMITER %L)$$  -- impossible delimiter
                , _file, _nodelim);

   -- create actual temp table with all columns text
   EXECUTE (
      SELECT format('CREATE TEMP TABLE %I(', _tbl)
          || string_agg(quote_ident(col) || ' text', ',')
          || ')'
      FROM  (SELECT cols FROM tmp0 LIMIT 1) t
           , unnest(string_to_array(t.cols, E'\t')) col
      );

   -- Import data
   EXECUTE format($$COPY %I FROM %L WITH (FORMAT csv, HEADER, NULL '\N', DELIMITER %L)$$
                , _tbl, _file, _delim);

   GET DIAGNOSTICS row_ct = ROW_COUNT;
   RETURN format('Created table %I with %s rows.', _tbl, row_ct);
END
$func$  LANGUAGE plpgsql;

Call variants:

SELECT f_dynamic_copy('/path/to/file.csv');
SELECT f_dynamic_copy('/path/to/file2.csv', 'tmp_file2');
SELECT f_dynamic_copy(_file  => '/path/to/file2.csv'
                    , _tbl   => 'tmp_file2');
                    , _delim => E'\t'); -- using assignment operator since pg 9.5

Answer:

Created table tmp_file2 with 123 rows.

Before the main COPY, run a preliminary COPY ... TO tmp0 to fetch the first row with column names, which are expected to be unquoted, case-sensitive strings like COPY ... TO ... (FORMAT csv, HEADER) would export them.

The structure of the actual target table is derived from it, all columns with data type text. The default name of the resulting table is tmp1 - or provide your own as 2nd function parameter.

Then COPY is executed. The default delimiter is a tab character - or provide your delimiter as 3rd function parameter.

Use any single-byte character for the non-delimiter _nodelim which does not appear in the first line of your CSV file. I am arbitrarily picking the control character "Delete" (ASCII 127). That character would be swallowed here on SO, so I generate with chr(127) instead, which is also valid. Assuming the character won't pop up - or provide your non-delimiter as 4th function parameter.

The function returns table name and number of imported rows.
Remember, a temporary table dies with the end of the session.

The manual:

  Executing a command with PROGRAM might be restricted by the operating
  system's access control mechanisms, such as SELinux.

Related answer on SO:

“ERROR: extra data after last expected column” when using PostgreSQL COPY

Postgres 8.4

That version is too old, I am not going to back-port that far.

GET DIAGNOSTICS  is an optional feature. You can just leave it away or replace it with a full count on the table
A primitive (expensive) alternative for the PROGRAM clause of COPY in pg 9.3 would be to import the complete table instead:

EXECUTE format($$COPY tmp0 FROM %L WITH (DELIMITER %L)$$, _file, _delim);

Or you prepare a second input file, or you can make it work by piping from the shell: COPY tablename FROM STDIN is available in pg 8.4.

format() can be replaced with plain string concatenation. Be wary of SQL injection though!

SQL injection in Postgres functions vs prepared queries


mongodb shard chunk migration 500GB takes 13 days - Is this slow or normal?

Update: April 2018

This answer was correct at the time of the question, but things have moved on  since then. Since version 3.4 parallelism has been introduced, and the ticket I referenced originally has been closed. For more information I cover some of the details in this more recent answer. I will leave the rest of the answer as-is because it remains a good reference for general issues/constraints as well as valid for anyone on an older version.

Original Answer

I give a full explanation of what happens with a chunk migration in the M202 Advanced course if you are interested.  In general terms, let's just say that migrations are not very fast, even for empty chunks, because of the housekeeping being performed to make sure migrations work in an active system (these still happen even if nothing but balancing is happening).

Additionally, there is only one migration happening at a time on the entire cluster - there is no parallelism.  So, despite the fact that you have two "full" nodes and two "empty" nodes, at any given time there is at most one migration happening (between the shard with the most chunks and the shard with the least).  Hence, having added 2 shards gains you nothing in terms of balancing speed and just increases the number of chunks which have to be moved.

For the migrations themselves, the chunks are likely ~30MiB in size (depends on how you populated data, but generally this will be your average with the default max chunk size).  You can run db.collection.getShardDistribution() for some of that information, and see my answer here for ways to get even more information about your chunks.

Since there is no other activity going on, for a migration to happen the target shard (one of the newly added shards) will need to read ~30MiB of data from the source shards (one of the original 2) and update the config servers to reflect the new chunk location once it is done.  Moving 30MiB of data should not be much of a bottleneck for a normal system without load.

If it is slow, there are a number of possible reasons why that is the case, but the most common for a system that is not busy are:

Source Disk I/O - if the data is not in active memory when it is read, it must be paged in from disk
Network - if there is latency, rate limiting, packet loss etc. then the read may take quite a while
Target Disk I/O - the data and indexes have to be written to disk, lots of indexes can make this worse, but usually this is not a problem on a lightly loaded system
Problems with migrations causing aborts and failed migrations (issues with config servers, issues with deletes on primaries)
Replication lag - for migrations to replica sets, write concern w:2 or w:majority is used by default and requires up to date secondaries to satisfy it.

If the system was busy then memory contention, lock contention would usually be suspects here too.

To get more information about how long migrations are taking, if they are failing etc., take a look at the entries in your config.changelog:

// connect to mongos
use config
db.changelog.find()

As you have seen, and as I generally tell people when I do training/education, if you know you will need 4 shards, then it's usually better to start with 4 rather than ramp up.  If you do, then you need to be aware that adding a shard can take a long time, and initially is a net negative on resources rather than a gain (see part II of my sharding pitfalls series for a more detailed discussion of that).

Finally, to track/upvote/comment on the feature request to improve the parallelism of chunk migrations, check out SERVER-4355


A query like
SELECT * FROM tbl1 AS OF test_restore_point;
is not possible.
Restorepoints can be used with the command FLASHBACK DATABASE. FLASHBACK DATABASE works similar like a restore.

First the whole database will be brought to a state before the time of the restore point. But this is not accomplished by copying a whole backup over each data file but it is achieved by applying flashback logs. For each data block that was modified after making the   restore point a backup was saved in the flashback logs.

Then the redo files are applied to roll forward the database to the restore point.

So when you flashback to a restore point the whole database is set to the point in time of the restore point. This is not what you want.
You can use flashback queries to queries old data but this data can only be supplied by the database if it is still found in the undo tablespace.
A reliable mechanism to hold the original data for a fixed period of time is Total recall/Flashback Data Archive. As Justin Cave pointed out this Feature is only available for Oracle 11.1 or higher versions.
The following licensing rules hold

Flashback Data Archive (formerly known as Total Recall)
For releases earlier than Oracle Database 11g Release 2 (11.2.0.4): You must license the Oracle Advanced Compression option to use Flashback Data Archive.
Beginning with Oracle Database 11g Release 2 (11.2.0.4): Flashback Data Archive—without history table optimization—is available in all editions.

A Flashback Data Archive holds the undo information of a selected table for a fixed period of time but it used a dedicated tablespace and is therefore not influenced by the cleanup meachanisms of the undo segments.
The table can be queried in the usual way with a flashback query.
You need Oracle Enterprise Edition and an additional license to use this feature.


Joining a table on an id but need to copy this data to other ids

SELECT d.id deviceid, u.id userid, dp.preferenceid
FROM Users u, Devices d, Device_Preference dp
WHERE u.id = dp.userid
  AND u.id = d.userid
ORDER BY 1,3

fiddle


Foreign key in one table have high read performance or another table with two primary key?

These two schemas represent different business rules. The first, with idFactor as a FK in Sanad, allows each Sanad to be connected to at most one Factor (if the foreign key column is nullable) or exactly one Factor (not nullable). The second, with a separate table, allows a Sanad to connect to zero, one or many Factors. Of course the second model is a superset of the first and can be limited to zero-or-one. There is no trivial way to do this in SQL Server, however. It will required additional constraints and triggers, meaning extra programing, debugging and maintenance.

You should implement the model which matches your business rules. This will force the data to be correct. It also self-documents the intent of the application. Sufficient performance can be achieved through sensible SQL and indexes.

To answer the actual question, it is likely to vary according to the ratio of reads to writes and whether Sanad field1, field2 etc. are returned. In schema 2 each row inserted in the intersection table will require two FKs to be checked i.e. two table reads. In schema 1 there will be 1 FK read. To check only the existence of a Sanad/Factor connection may be faster in schema 2 because the rows are shorter, there will be more rows per page and hence a better chance that the required page is in memory. This only applies if there is a small working set of "hot" rows. To read the data columns in scenario 2, however, will need reads of both interesction and Sanad tables; double to IO.

So, if working set size is bigger than RAM, and the vast majority of reads only check for existence of a link, and there are relatively few writes, and you're confident to do the extra programing to ensure data integrity, or you do actually have a many-to-many relationship, go with schema 2, otherwise stick with schema 1.


Protecting Virtualized SSIS and SSRS servers

We are using Veeam to backup our sql db, ssis and ssrs servers.  We are using native storage snapshot integration, which makes them happen faster.  Still we have some issues when running a snapshot when an SSIS package is running.  I would advise avoiding running snapshots during your SSIS package runs.

SSIS and SSRS encryption keys are stored on the machine and don't rely on any externals, so a restore of an entire machine will not require an additional key restore, although it is a good idea to back up your keys in case you have to rebuild and manually restore a machine for some reason.

We still run sql backups of all of our databases including ssis catalog to a network share.  We are planning to use Veaam for those 'application level' backups in the future.


Normalization: Should I do this in 2NF or 3NF?

If's a design choice, and you have to consider the trade-off:

If you use 2NF, there will be some duplication, which can potentially be detrimental to integrity - you can change a value in one place and not in the other.

So, if you decide to go with 2NF, you have to be more careful about integrity.

If you use 3NF, there will be no duplication. But you might end up with too many tables, and have hard time keeping track of them.


Alternative to SQL Server Management Studio and Azure Data Studio to initially change my password

mssql-cli is a command-line tool, available for Mac, Linux and Windows. See https://github.com/dbcli/mssql-cli/tree/master/doc/installation.

It isn't clear, however, if you can't login in the first place. If you can, then just change the password using ALTER LOGIN. If you can't, then you rely on the tool in question to (using any of the SQL Server APIs) to allow you to set a new password. SQLCMD (another command line tool, Win lony) for instance has command-line options for this. mssql-cli doesn't seem to have such options, at this point in time.


Help me understand sp_server_diagnostics

Ensure your servers follow VMware's Best Practices Guide for SQL Server.

Specifically, ensure the VMs running SQL Server have 100% of their memory and CPU reserved in VMware.  Ensure you're using the PVSCSI (para-virtualized SCSI) adapter for SQL Server RDMs or Virtual Disks.  Try to use as many PVSCSI adapaters as you can since that will increase the number of disk queues available to SQL Server.


What methods are available to create a snapshot of an oracle database for testing purposes?

If with "export/import" you mean the "traditional" exp and imp commands, than I'd say there is a better solution:

expdp and impdp are much faster and much more flexible when copying a snapshot of a database (or single schema).

If the database is not huge (i.e. Terabytes) then expdp and impdp is the easiest way to achieve this. They are easily scriptable and can be started using SQL also. So no commandline access to the servers is needed (provided the dump files are stored on a shared storage).


how to sort by timestamp field while counting duplicates

Simply apply a MAX:

SELECT user, MAX(time), COUNT(*) AS total
FROM table
GROUP BY user
ORDER BY MAX(time) DESC

Of course, this assumes that time is actually a datetime or timestampand not a VarChar.


how do you output a plsql array via ref cursor

I couldn't find an example either, so I concocted this:

CREATE TYPE t_tf_row AS OBJECT (
  id           varchar2(20),
  description  VARCHAR2(50)
);
/
CREATE TYPE t_tf_tab IS TABLE OF t_tf_row;
/

create or replace package aa2rc as
  FUNCTION get_tab_ptf
    RETURN t_tf_tab PIPELINED;
  PROCEDURE GetOrders (
    p_Orderid     IN  NUMBER,
    p_subOrderIds OUT sys_refcursor);
end aa2rc;
/

create or replace package body aa2rc as
  type allOrder_t is table of varchar2(50) index by varchar2(20);

  allOrder allOrder_t;

  FUNCTION get_tab_ptf
    RETURN t_tf_tab PIPELINED AS
    i varchar2(20);
    BEGIN
          i := allOrder.first;
          while (i is not null) loop
            PIPE ROW(t_tf_row(i, allOrder(i)));
            i := allOrder.next(i);
          END LOOP;

          RETURN;
    END;

  PROCEDURE GetOrders (
    p_Orderid     IN  NUMBER,
    p_subOrderIds OUT sys_refcursor)
    IS

      procedure relorders (p_ord in out allOrder_t) is
        i number;
        begin
          for i in 1 .. 11 loop
            p_ord('Index no. ' || (p_ord('One') + i))
              := 'Value ' || (p_ord('One') + i);
          end loop;
        end;

    BEGIN
      allOrder ('One') := p_Orderid;

      relOrders (allOrder);

      open p_suborderids for
        select * from table(aa2rc.get_tab_ptf);
    END;
end aa2rc;
/

I'm not that sharp with C#, but ran a test with sqlplus:

variable R refcursor
execute aa2rc.getorders(5,:r);
print r

which gave:

ID                   DESCRIPTION
-------------------- --------------------------------------------------
Index no. 10         Value 10
Index no. 11         Value 11
Index no. 12         Value 12
Index no. 13         Value 13
Index no. 14         Value 14
Index no. 15         Value 15
Index no. 16         Value 16
Index no. 6          Value 6
Index no. 7          Value 7
Index no. 8          Value 8
Index no. 9          Value 9
One                  5


Can you use CDC on a subscriber for transactional replicaiton

You do not mention whether you are using SQL Server 2012 or 2014, however the documentation on CDC and Change Tracking do not show much difference.

Both make the following statement: "CDC configuration is always performed on the current or intended primary replica."

http://msdn.microsoft.com/en-us/library/hh403414(v=sql.120).aspx#CDC

The above link is for SQL Server 2014 and offers many more details.


Automatically set value on child rows when deleting parent row?

create table tree_data
(
   id integer primary key,
   code text,
   name text,
   parent_id integer,
   constraint fk_parent
      foreign key (parent_id)
      references tree_data(id)
      on delete set null
);

SQLFiddle example: http://sqlfiddle.com/#!15/b9a62/1


Can I throttle individual databases instances on one SQL Server 2005 box

You could use the Windows System Resource Manager (WSRM) feature for this.
It is not present in Windows starting from 2012 R2, but since this is an old SQL 2005, I would expect the OS to be old-ish as well.

I've never used it myself, but, though a bit complicated, it might do the trick for you.


Question on best practice - keep empty partitions at both ends of your table?

Is it sufficient to ensure that there will be no data movement by ensuring that the new boundary corresponds to a future date or do I really need the empty partition ?

If your partition function is RANGE RIGHT, then yes.  A partition split moves rows in the existing partition that belong in the new partition.  The new partition is the partition that contains the new boundary value.  So in RANGE RIGHT if the new boundary value is after all the rows in the table, no rows need be moved.

Hopefully you choose RANGE RIGHT to begin with so you don't have to have boundary values like 2015-12-31 11:59:59:999.

Depending on the indexes, the head partition may need to be scanned to discover that no rows need to be moved.


LOAD DATA keeps CPU usage high after it finishes

As expected.

The updating of secondary indexes (INDEXes other than PRIMARY KEY) happens later.  Such changes are stored in the Change Buffer; they are actually written to disk later in a more efficient way than trying to do them as the rows are being inserted.  You will see both CPU and I/O activity.

The net effect should be that your LOADs and SELECTs will complete faster (elapsed time) than without the Change Buffer.


SQL order by query results in any arbitrary (but reproducible) manner

Use PK columns

Sorting by all columns of a table is potentially (very) expensive or sometimes simply impossible. Example: table with a json column or xml, or point or one of many other data types which cannot be used in ORDER BY, lacking the necessary operators. You get an exception.

If it doesn't fail, it should work for your purpose, but it still would not guarantee a true stable sort order. Without unique or primary key, completely duplicate rows are allowed, which are still sorted arbitrarily. Two concurrent sessions could both change the "first" in a set of dupes without conflict, resulting in two different rows being changed separately.

Use the primary key column(s) of each table. Unique constraint would work, too, but you really should have a pk on every table to begin with. Besides being correct and guaranteed to work, it's also typically much faster.

Function

Query the system catalogs pg_constraint and pg_attribute to get columns for a PRIMARY KEY or UNIQUE constraint.

I wrapped the query in a handy SQL function:

CREATE OR REPLACE FUNCTION f_order_cols(_tbl regclass)
  RETURNS text AS
$func$
SELECT string_agg(_tbl::text || '.' || quote_ident(attname), ',')
FROM (
   SELECT conrelid AS attrelid, conkey
   FROM   pg_catalog.pg_constraint
   WHERE  conrelid = $1
   AND    contype = ANY ('{p,u}'::"char"[]) -- primary or unique constraint
   ORDER  BY contype                        -- pk has priority ('p' < 'u')
           , array_length(conkey, 1)        -- else, fewest columns as quick tie breaker
   LIMIT  1                                 -- ... but any 1 of them is good
   )   c
JOIN   unnest(c.conkey) attnum ON TRUE      -- unnest array, possibly mult. cols
JOIN   pg_catalog.pg_attribute a USING (attrelid, attnum)

UNION  ALL                                  -- Default if first query returns no row
SELECT _tbl::text || '::text COLLATE "C"'   -- See chapter "Default" below
LIMIT  1
$func$ LANGUAGE sql;

This is very fast.
The function takes the table name as parameter, type regclass to be precise. Resolves the name according to the current search_path and raises an exception if invalid. At the same time provides a properly quoted identifier in the result automatically.
Any PRIMARY KEY or UNIQUE constraint is good. PK has priority, else the unique constraint with the fewest columns is picked.
The constraint can consist of multiple columns, attribute numbers of which are stored in the array pg_constraint.conkey. Using an implicit JOIN LATERAL to unnest() that.
Result columns are table-qualified to get syntax ready for queries on multiple tables. This optional feature may have to be modified if you are using table aliases in queries.
Also quote column names correctly to avoid SQL injection. quote_ident() does that for you. More about that:

SQL injection in Postgres functions vs prepared queries

Default

If no constraint is found, the function returns NULL. Wrap the result into COALESCE and use the text representation of the whole row as default: tbl::text.

This is not going to sort rows like original values would, but you get a consistent sort order that works for every table. Since you are going to compare text representations anyway.
Better still, add COLLATE "C" to ignore collation rules. Faster, and just as good for your purpose.

ORDER BY tbl::text COLLATE "C"

Still a potentially very inefficient measure of last resort. Use PKs if at all possible. I put the default into the function above. Everything put together, for a query:

SELECT * FROM u JOIN p USING (a) JOIN n ON n.b = p.b ORDER BY ?

This returns the complete string for ?:

SELECT concat_ws(', ', f_order_cols('u')
                     , f_order_cols('p')
                     , f_order_cols('n')) AS order_by;

SQL Fiddle demonstrating all.


Similar queries taking different times to complete

These 2 queries are not the same and don't return the same result .
You end up with following filters:

1st Query:

 WHERE
 (
 a >= To_Date('&&1','YYYYMMDD')
  AND a < To_Date('&&1','YYYYMMDD')+1
 )
 AND
(
    b LIKE ...
   AND e IS NULL
   AND adjust1>0
   AND (b NOT IN ('...','...','...'))
   OR  (b = '... AND c <> NULL)
)

Second:

WHERE
  (
  a >= To_Date('&&1','YYYYMMDD')
  AND a < To_Date('&&1','YYYYMMDD')+1
  AND b LIKE '..'
  AND e IS NULL
  AND (b NOT IN ('..','..','..'))
  )
  OR
 (b='..' AND c <> NULL)

You need to use parenthesis carefully while combining  AND and OR predicates in one condition.


Presuming you are using SQL Server, which you still haven't confirmed, I'd use an inline table-valued-function (TVF) that can be used to efficiently and safely convert character-data into an integer value, for strings of 8 characters or less.  The string must be a maximum of 8 characters in length since that is the maximum number of bytes for an integer in SQL Server.

This is the TVF:

IF OBJECT_ID('dbo.GetBigInt') IS NOT NULL
DROP FUNCTION dbo.GetBigInt;
GO
CREATE FUNCTION dbo.GetBigInt (@data VARCHAR(1000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT BigIntResult = CASE WHEN LEN(@data) > 8 THEN 1/0
    ELSE CONVERT(BIGINT, CONVERT(VARBINARY(8), @data, 0)) END;
GO

To test the function, I created the following test-bed table with some sample rows:

IF OBJECT_ID('tempdb..#t') IS NOT NULL
DROP TABLE #t;
CREATE TABLE #t
(
    SomeText VARCHAR(100)
);

INSERT INTO #t (SomeText)
VALUES ('aaaaaaaa')
    , ('AAAAAAAA')
    , ('aaaaaaab')
    , ('ZZZZZZZZ')
    , ('ÿÿÿÿÿÿÿÿ')
    , (CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1) + CHAR(1))
    , (CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127) + CHAR(127))
    , (CHAR(128) + CHAR(127) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128) + CHAR(128))
    , ('aaaaaaaba');

Run the TVF against the table like so:

SELECT *
FROM #t t
CROSS APPLY dbo.GetBigInt(t.SomeText) g;

The last row in the sample intentionally has 9 characters, which cannot be reliably converted into an 8-byte integer.  When the TVF attempts to convert that row, it will display the following error:

  Msg 8134, Level 16, State 1, Line 32
  Divide by zero error encountered.

The results for the other rows in the sample data are:

To test the speed of the TVF, I inserted 1,000,000 rows into the table, like so:

INSERT INTO #t (SomeText)
SELECT TOP(1000000) LEFT(c1.name, 8)
FROM sys.columns c1
    CROSS JOIN sys.columns c2
    CROSS JOIN sys.columns c3;

The execution plan for the query running against 1,000,000 rows:

If your "non-numeric string IDs" are longer than 8 bytes, I'd recommend simply converting them to BINARY(x) or VARBINARY(x) values, where x is then maximum length of your character data.

IF OBJECT_ID('dbo.GetBinary') IS NOT NULL
DROP FUNCTION dbo.GetBinary;
GO
CREATE FUNCTION dbo.GetBinary (@data VARCHAR(1000))
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
SELECT BinaryResult = CONVERT(VARBINARY(1000), @data, 0);
GO

SELECT *
FROM #t t
CROSS APPLY dbo.GetBinary(t.SomeText) g;


The problem with your code is that you are declaring your variable as varchar without any length. With variable declarations, varchar defaults to a length of 1 (note that in other scenarios it will have a different default length).

As a best practice, you should always specify a length on the varchar datatype. For more details, see this article on the issue.


Log all than queries taking more than X seconds

The syntax you're looking for will be very similar to this:

create event session longrunning_statements on server
add event sqlserver.sql_statement_completed
(
where (duration > 5000000)
)
add target package0.asynchronous_file_target
(Set filename='c:\capture\xe_longrunning_statement.xel',
metadatafile='c:\capture\xe_longrunning_statement.xem');

alter event sesession longrunning_statements on server state=start;

This will set up an event session which will track an sql statement that takes longer than 5 seconds to complete. The duration filter is based on microseconds btw.

If you wanted to restrict this to a specific database (in this case the db with database id=5, then you could change the filter to:

where (duration > 5000000 and database_id=5)

Lots of other filters as well to tie it down to specific sessions or users if required.

This event session will log it's results out to a file (c:\capture\xe_longrunning_statement.xel) which can be read in with the fn_xe_file_target_read_file function like so:

SELECT *
    FROM
        sys.fn_xe_file_target_read_file
        (
            'c:\capture\xe_longrunning_statement*.xel',
            'c:\capture\xe_longrunning_statement.xem',
            null,
            null
        );

(Note that I've used 2008R2 standards for tge xel/xem files and setting up the target. It's slightly different in 2012, but this syntax would still work). The * in the filename is to let the function know you want to load up all of the files if they've rolled over due to size.

You'll then need to parse the data out through your XML tools of choice.

Once you're done with the capture, you can tidy up as follows:

alter event session longrunning_statements on server state=stop;
drop event session longrunning_statements;

One of the best online resources is Jonathan Kehayais' 31 days of Exteneded Events - https://www.sqlskills.com/blogs/jonathan/an-xevent-a-day-31-days-of-extended-events/ - which walks you through the topic with plenty of examples.


Should I index an integer column which is already ordered?

Inserting in an order does not mean that is a natural order in the table.

Put a composite primary key on:

user_id, question_id

A second index on question_id is optional.
It would only be used if you are searching on question_id only.


Select rows other than first selected row in a table

You can split the debit/credit columns using a CASE syntax:

(CASE WHEN [Debit or credit]='cr' THEN Amount END) AS cr,
(CASE WHEN [Debit or credit]='dr' THEN Amount END) AS dr

As for the "sno" column, I presume that the answer you're looking for is something like

DENSE_RANK() OVER (ORDER BY TransactionType) AS sno


Open Source Business Intelligence/DWH solutions

I have been working with Pentaho for about a year now.

Pentaho is a full Open Source suite for Business Intelligence.

It's strenght is that it relies on independently managed project :

Pentaho Data Integration (Kettle) ->ETL
Pentaho Report Designer (PRD) -> Report designer
Mondrian -> R-OLAP cube
and much more..

You can use them as a whole (Pentaho BI Server) or apart.

Why would I recommend it ? The community is really active and provide plugins on a weekly basis. A group of community members provided a framework for building interactive dashboard with a click&go ui.

It is database independant. If you have some free time just take a look at it.

I did a research before using Pentaho, I did test IceCube and Palo even JasperSoft but Pentaho was from far better according to my needs.


Database Users and Server Logins and the Mapping between them

Logins or server logins exists at the SQL server level, and the database user is at the database level. They need not have the same name, but generally do. In an instance, you cannot have a database user function properly without a corresponding SQL server login.

Let us say you have an instance: instanceA with a sever login userA on your computer. You can map this server login userA to a database user: userA for a database DB_A. They are both named userA, but one is the server login and one is the database user. The database user: userA resides in DB_A. When you backup and restore DB_A from instanceA to another instanceB, the database user: userA is moved, but the server login: userA still remains on instanceA. To move the logins from instanceA to instanceB, you can use sp_help_revlogin (Click to check out KB page).

If you had previously created the SQL server login userA on instanceB, then SQL will try to map this login with database user: userA, when DB_A is restored from instanceA to instanceB. If this fails, you can use the sp_change_users_login (Click for BOL link) procedure to fix the broken mappings. Hope this clears things up.


How to recover deleted rows data table in SQL Server without backup?

It would seem that the before and after states would be recorded in the transaction log. Here is a step by step article on recovering deleted data from the SQL Server transaction log. You can create a db copy from the transaction log right before the delete and get a copy of the data from that point.
 RESTORE LOG Databasename_COPY FROM DISK = N'D:\Databasename\TranLog.trn' WITH STOPBEFOREMARK = 'lsn:0x0000001'

Recover Deleted Data From SQL Server Table by Transaction Logs
Edit: I just saw this solution on another answer.
It does not require backups and actually converts the data. It may have some issues with max length datatypes and such, so you would have to check the data.
How to recover deleted data from SQL Server
Which is referred to in an answer in the following question.
How do I get back some deleted records?


SQL Server 2008 - Cannot create index on Indexed View

Well apparently the owner is different.
You might have done that without even realizing it.

Try changing the owner:

ALTER AUTHORIZATION on CDSIM_Thk TO <whoever, dbo for instance>


What is the recommended my.cnf settings for 32 GB Server

Suggestions to consider for your my.cnf [mysqld] section

thread_cache_size=100  # to reduce threads_created count of 16K in 42d per MSTuner
table_open_cache=6000  # from 2500 to reduce opened_tables count
innodb_open_files=6000  # from 2500 to be paired with table_open_cache

Welcome to dba.stackexchange.com


Is it normal that I have to FLUSH QUERY CACHE once every 6 hours to maintain fast queries?

Flushing the query cache would be OK depending on Storage Engine

InnoDB spends time micromanaging data changes. This includes crosschecking the query cache.

I have a 2-year-old post (Sep 05, 2012 : Is the overhead of frequent query cache invalidation ever worth it?) where I recommend running FLUSH QUERY CACHE via the crontab.

I also have more recent posts

Jun 07, 2014 : Why query_cache_type is disabled by default start from MySQL 5.6?
Jun 30, 2014 : Optimize MySQL query_cache_min_res_unit

explaining how one could try to set query_cache_size and query_cache_min_res_unit if one truly knows the data, the average size of a result set, and how many entries should be in the query cache.

Other than having this foreknowledge about the data, fragmentation is unavoidable.

On the flipside, if all your data is MyISAM, you should take the time to set set query_cache_size and query_cache_min_res_unit by getting to know your data. After all, MyISAM does not cache data. It only caches indexes.

EPILOGUE

Regardless of Storage Engine, running FLUSH QUERY CACHE; bypasses the need to set query_cache_size and query_cache_min_res_unit. It quickly eliminates fragmentation issues. If your dataset is not very large, make the time to size your data and result sets. You could eliminate the crontab.

What would be an even greater advantage would be the following

tune the queries
add proper indexes to support queries
tune MyISAM

If the MyISAM tables have VARCHAR columns, change row format to FIXED.
Make sure the key_buffer_size can accommodate all MyISAM indexes (See What are the main differences between InnoDB and MyISAM?)


You need to "unnest" the elements of the JSON array and then join that to your media table:

The following query assumes that media is a column of type jsonb. If that is not the case (which it should be) you need to cast it media::jsonb.

select m.id, m.path
from media m
  join (
    select jm.id::integer as media_id
    from playlist p
      cross join jsonb_array_elements_text(p.media) as jm(id)
    where p.id = ? --<< your playlist id here
) t on m.id = t.media_id;

jsonb_array_elements_text(p.media) returns all elements of the array as rows and  as jm(id) defines a table alias for that result giving the column that is returned the name id. The expression jm.id::integer as media_id then converts the string value into a proper integer so it can be used in the join condition.

Unrelated, but: this is a case of unnecessary JSON. If you normalize your data model, queries like that will be a lot easier and more efficient. Plus: you can define proper foreign key constraints between playlist and media.


MySQL 2 Million Rows, longtext and varchar indexes

Each run of this query will scan the whole table for two reasons:

The index on name will not be used as the value starts with a wild char %
You are not using the proper functions that utilize the fulltext index on the other two fields.

Proposed solution:

add a fulltext index on name field
Use the proper functions to search. in this case, it is match : https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html#function_match

Notice: Depending on your version of MySQL, using OR to combine conditions could be a performance hit. It is not the case in 5.6 +


How do completely empty columns in a large table affect performance?

Since the columns e, k, and n can be NULL, I assume "100% empty" means NULL.
NULL storage is cheap. Each NULL "costs" one bit in the null bitmap for storage and otherwise hardly affects performance. Effective storage requirements depend on whether the null bitmap for each row already exists and has room for 3 more bits.
Tables with up to 8 user columns utilize a spare byte after the tuple header for the null bitmap in the current implementation. For any more, another MAXALIGN (typically 8) bytes are allocated providing for another 64 columns (for a total 72 now). Etc. So the null bitmap effectively costs 8 bytes per row for most tables, including yours which has 18 columns.
The null bitmap is allocated completely or not at all per row and only if there is at least one actual NULL value. If all columns are defined NOT NULL, there can never be one.
In the absence of other, more important considerations, move columns that are always (or mostly) NULL to the end of the row. Helps performance a tiny bit.
Answer to question 1.
The effective storage requirement is:

0 bytes per row where other columns are also NULL.
8 bytes (typically) per row where no other column is NULL, for allocating the null bitmap

Answer to question 2.
Dropping would not buy you much. Page cache would be the same or slightly less depending on 1. Handling queries, backups, source code and such would be simplified.
Further reading:

Does not using NULL in PostgreSQL still use a NULL bitmap in the header?
Adding nullable column to table costs more than 10 minutes
Measure the size of a PostgreSQL table row
Configuring PostgreSQL for read performance

What you did not ask for
After a round of "column tetris" I suggest this table layout - again barring other, more important considerations:
  id serial NOT NULL
, a  integer
, g  timestamp
, i  timestamp
, b  integer
, c  integer
, h  integer
, j  integer
, l  integer
, d  smallint
, f  smallint
, m  smallint
, o  varchar(36)
, p  varchar(100)
, q  varchar(100)
, k  varchar(32)      --  always NULL
, n  smallint         --  always NULL
, e  timestamp        --  always NULL

Saves at least 16 bytes per row compared to your original layout, probably around 20+, depending on your varchar columns. Instructions for "column tetris" here:

Calculating and saving space in PostgreSQL

How to reorder columns
The simplest method is to create a new table and copy data into it. Then you get a pristine new table without dead columns (and dead rows). You could sort (cluster) the physical order of rows in the process, too.
Changes in place are possible too, but there is no convenient tool (I would know of) to generally reorder the sequence of columns. Depending objects like views, functions (depends), FK constraints, indexes etc. are limiting factor here. Those can block you from deleting a column, you have to drop and recreate depending objects.
And of course, concurrent access to the table conflicts with DDL commands changing it. You need exclusive table locks in this case.
It's simple and cheap to delete completely empty columns and add them at the end of the row. Switching the sequence of populated columns in the middle of the table is not as simple or cheap. I would create a new table for that.
This related answer has recipes and links to more:

Best way to populate a new column in a large table?


TDE, or "transparent database encryption" encrypts all data contained in the encrypted database.  That is, both existing and newly inserted or updated data is encrypted.
There is no requirement to re-encrypt the database once TDE is enabled, assuming the new data is imported into the same TDE encrypted database.
Along with the document you linked to, this document on transparent database encryption contains a wealth of important information about TDE.
TDE, as suggested in its name, is transparent.  That is, running a select statement against data contained in a TDE-encrypted database will not display encrypted data - it will display the unencrypted version of the data.  The encryption protection is for data-at-rest - i.e. If someone steals a copy of the database, they won't be able see any data contained in that database without the certificate used to encrypt the database.  If you need to prevent data being visible in certain columns such as a credit card number, you should look at column encryption.


What's the difference of setting up CPU affinity between by Server's Task Manager and by SSMS?

Update based on comment from Max Vernon.

  Setting the affinity mask with Task Manager will only have effect
  until SQL Server is restarted. Using sp_configure will ensure the
  change remains effective across restarts.

Using task manager make sure you pick the right process especially if you have multiple SQL instance. With SSMS it is obvious which one you are changing.

Microsoft recommend to use:

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'affinity mask', 38;
RECONFIGURE;
GO

Details here.

Word of caution from the same article.

  Do not configure CPU affinity in the Windows operating system and also
  configure the affinity mask in SQL Server. These settings are
  attempting to achieve the same result, and if the configurations are
  inconsistent, you may have unpredictable results. SQL Server CPU
  affinity is best configured using the sp_configure option in SQL
  Server.


strange optimization of query results in phantom value conversions

As expected.

SQL is declarative and there is no implicit ordering of execution. You will see that the CAST is happening before the filter, so you get the error. That is, you have assumed that only int values will get through the CAST. Y

You should assume that all values in the column will be converted to int. The value here is "hist" which has been parsed.

There are a few ways to ensure that only CASTable values will be presented to the CAST

Materialise the JOIN so it has to run before the CAST
Use CASE to manage the CAST (see below)
Use a table variable/temp table to cache results

Example. You can't rely on the CAST happening after the derived table.

SELECT
    tbl_name,
    CASE WHEN num NOT LIKE '[^0-9]' THEN CAST(num AS int) END
FROM
    (
    SELECT
        a.tbl_name,
        CAST(REVERSE(LEFT(REVERSE(b.name),PATINDEX('%[_]%',REVERSE(b.name))-1)) AS num
    FROM
        partition_tbl a
        INNER JOIN
        sys.objects b ON
                 b.name COLLATE German_PhoneBook_BIN
                        NOT LIKE REPLACE(REPLACE(a.tbl_name,'_','[_]'),'{num}','[^0-9]')
    WHERE
        b.xtype='U'
    ) T


Is it possible to configure pgbouncer to distribute queries among master and slave?

pgBouncer cannot do that, and indeed it is a questionable feature. How can you reliably tell that a statement like

SELECT pg_create_physical_replication_slot('aname');

won't modify your database?

There is pgPool which boasts such a feature, you might investigate it.

The better technique is usually for your application to use two database connections: one for reading and one for data modifications. That doesn't put you at the mercy of a tool that tries to guess.


is there a way to improve this query by not re calculating functions? cross apply?

I beleive you want to store the results of the multiple procs in a single run.
If that's the case then

I am not sure about what are the columns returned by the procedure sp_count
I am assuming that it returns tablename,total_count

So your code will be like this

USE ORCASTG_CA18_Repl
go
DECLARE @sql VARCHAR(MAX);

CREATE TABLE #table_count (table_name varchar(128), table_count INT)

  SELECT  @sql=COALESCE(@sql+ '', '')
           + 'INSERT INTO #table exec sp_count '
           +'''' + QUOTENAME(OBJECT_SCHEMA_NAME(a.objid))
           + '.' + QUOTENAME(a.name) +';'+'''')

FROM dbo.syspublications P

INNER JOIN dbo.sysarticles A
        ON P.pubid = A.pubid

INNER JOIN dbo.syssubscriptions s
        ON a.artid = s.artid

WHERE 1=1
  AND s.dest_db <> 'virtual'

  EXEC (@sql)

  SELECT * FROM #table_count tc

Also note, I have not tested the code. If you find issues when running please feel free to edit it.

Edit 1:
You can also use  the below methods instead of COALESCE()

STRING_AGG() (2017 onwards)

XML string concatenation

GROUP_CONCAT CLR method (Github)

All these is mentioned in the Aaron bertrand's blog here


A very basic example would be to get the AVG and STDEV of the range of numbers and then exclude any that were more than 1 Standard Deviation from that average.
You then take the average of the new range.
This is quite a basic bit of code (don't forget the CAST to a DECIMAL) which you can expand upon to make it more suitable to your needs.
CREATE TABLE #nums (num INT);

INSERT INTO #nums VALUES (1), (2), (1), (2), (7);

WITH AvgStd AS (
  SELECT
    AVG(num) AS avgnum,
    STDEVP(num) AS stdnum
  FROM #nums AS n
)
SELECT
  AVG(CAST(num AS DECIMAL(5,2)))
FROM #nums AS n
CROSS JOIN AvgStd
WHERE num < (avgnum + stdnum)
  AND num > (avgnum - stdnum);

DROP TABLE #nums;


I am seeing similar results (from SQL Server 2008 R2), even though I am not using compression (so compressed_backup_size == backup_size), so I do not think this is a compressed size issue.

For example, for a .bak, backupset reports 117,648,384 while size on disk is 117,678,080, and for a .trn, backupset reports 203,776 while size on disk is 233,472.

I think the documentation's claim that backup sizes are reported as "Total Byte count of the backup stored on disk" is misleading.  I suspect that at some level it is reporting the true size of the backup as far its internals are concerned, but the actual size on disk will be larger than that.  Although the figures do not seem to divide exactly, I see the disk size as being up to 32KB larger than the reported size.

I just looked at a SQL Server 2012 backup, and it reports 7MB .bak and .trn each being ~10KB smaller than disk size.  So this is not just a 2008 R2 issue.

You see the .bak size as "very close" but the .trn size as "so far off" because your .bak is a large size while your .trn is a small size.  If it is within ~32KB, the results are not too far off when the .bak/.trn are a decent size, which is what is important.


creating a trigger based on time after one year in mysql

Triggers only occur when an action (INSERT, UPDATE or DELETE) occurs, but that's not the case here.

You have a record which has "aged out". So, basically, it just sits there and does nothing unless you actively "look for it"! The server can only react to what it can perceive as a change - not an arbitrary record age!

You'll have to use events - say (in your case), fired once a day! Think of these as "temporal programmable triggers".

From the documentation, the first thing you do is (one of):

SET GLOBAL event_scheduler = ON;
SET @@global.event_scheduler = ON;
SET GLOBAL event_scheduler = 1;
SET @@global.event_scheduler = 1;

That sets the events daemon (or service on Windows), which are like a cron job or an at) in motion (see), so basically now, events will be fired.

Then, from here do something like:

DELIMITER $$
CREATE
    EVENT `entity_update`
    ON SCHEDULE EVERY 1 DAY STARTS '2017-10-20 03:00:00'  -- do this at three in the morning!
    DO BEGIN

      -- This is where to put the SQL below - I forgot about
      -- the non-standards-compliant dialect of SQL that
      -- MySQL uses (see comparison with PostgreSQL below!).

    END */$$
DELIMITER ;

Be sure and test a couple of times before you "bed" your solution in!

The update SQL would look something like this (made a guess at table structure based on OP - see full DDL and DML below)

UPDATE status
SET status.status_plan = 'inactive'
WHERE status.status_plan_id IN
(
  SELECT del_id
  FROM
  (
    SELECT s.status_plan_id AS del_id
    FROM plan p
    JOIN status s
    ON p.plan_id = s.status_plan_id
    WHERE p.plan_issue_date < (DATE_SUB(NOW(), INTERVAL 1 YEAR)) -- plan_issue_date older than 1 year
    AND s.status_plan = 'active'  -- <-- and only change those which are not already inactive!
  ) AS things_to_delete
);

Incredibly, for reasons outlined here, with MySQL, we programmers have to put in an artificial subSELECT into our UPDATE! Check out the much simpler and more elegant PostgreSQL syntax below:

UPDATE status
SET status_plan = 'inactive'
WHERE status.status_plan_id IN
(
  SELECT s.status_plan_id
  FROM plan p
  JOIN status s
  ON p.plan_id = s.status_plan_id
  WHERE p.plan_issue_date < NOW() - INTERVAL '1 YEAR'
  AND s.status_plan = 'active'  -- <-- only change those which are not already inactive!
);

(Full MySQL solution here, PostgreSQL here)

---------- MySQL DDL ---------

CREATE TABLE plan
(
  plan_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  plan_issue_date DATE
);

CREATE TABLE status
(
  status_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  status_plan_id INTEGER,
  status_plan VARCHAR (10),  -- no CHECK CONSTRAINTs in MySQL!
  CONSTRAINT status_plan_fk FOREIGN KEY (status_plan_id) REFERENCES plan (plan_id)
);

Incredibly, MySQL does not support CHECK CONSTRAINTs on tables! You can get around this in three (clumsy IMHO) ways:

TRIGGERs - as outlined - here. Very clumsy - these should be for complex business logic and not for relatively simple criteria - Occam's Razor and all that!
A small lookup/reference table - here. Not a bad alternative, but not the most elegant - I prefer, if possible, to keep all definitions/constraints associated with a table in the CREATE TABLE statement!
VIEWs with CHECK OPTION - here. Quite why MySQL can provide VIEWs with such an option but not tables is beyond me!

<rant over...>

------------ DDL for both MySQL and PostgreSQL ------------

INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (35, 1, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (45, 2, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (67, 3, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (78, 4, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (95, 5, 'inactive');

INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (35, 1, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (45, 2, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (67, 3, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (78, 4, 'active');
INSERT INTO status (status_id, status_plan_id, status_plan) VALUES (95, 5, 'inactive');

------------------ PostgreSQL DDL ----------------------------------

CREATE TABLE plan
(
  plan_id SERIAL, CONSTRAINT plan_pk PRIMARY KEY (plan_id),
  plan_issue_date DATE -- , CONSTRAINT plan_date_ct_gt_now CHECK (plan_issue_date > NOW())
                       --   might be an idea for future inserts into plan?
);
ALTER SEQUENCE plan_plan_id_seq RENAME TO plan_seq;

INSERT INTO plan (plan_issue_date) VALUES ('2017-10-15');
INSERT INTO plan (plan_issue_date) VALUES ('2016-10-15');  -- << older than 1 year
INSERT INTO plan (plan_issue_date) VALUES ('2017-07-23');
INSERT INTO plan (plan_issue_date) VALUES ('2016-03-04');  -- << older than 1 year
INSERT INTO plan (plan_issue_date) VALUES ('2016-02-25');  -- <<= older than 1 year and inactive in status table.

CREATE TABLE status
(
  status_id SERIAL,
  CONSTRAINT status_pk PRIMARY KEY (status_id),
  status_plan_id INTEGER,
  status_plan VARCHAR(10) CHECK (status_plan IN ('active', 'inactive')),
  CONSTRAINT status_plan_fk FOREIGN KEY (status_plan_id) REFERENCES plan (plan_id)
);


Just reading the header you put in the question shows something interesting. In fact, the question shows three things:

MySQL dump 10.13  Distrib 5.1.34, for apple-darwin9.5.0 (i386) indicates you used mysqldump from apple-darwin9.5.0 (i386) binaries
Server version 5.0.51a-24+lenny2 shows the version of mysql you used mysqldump to dump from.
You wanted to load the mysqldump file into Ver 14.14 Distrib 5.1.57, for apple-darwin10.3.0 (i386) using readline 5.1

What a jumble of versions to do this with.

If you want to see if mysqldump has an issue with the line that has DATEDIFF, try dumping just the schema.

mysqldump --no-data --all-databases ... > MySQLSchema.sql

This will display ony the schema. No INSERTs will be in the output. You can then hunt down that lines. You may also want to dump the data onyl without the schema,

mysqldump --no-create-info --all-databases ... > MySQLData.sql

Splitting the dumps allows you to load the schema into an editor and see if there are any problems. If you do not see any problems, load the MySQLSchema.sql into the target server. If the error is reproduced, you can fix the schema file and reload. Once the schema is loaded, you can separate load MySQLData.sql

BTW you should use mysqldump binary whose version is 5.0.51a-24+lenny2. Use dumps from version as mysqld is usually better to port and may minimize problems like this.

Give it a Try !!!


Column name naming conventions and best practices

This has been asked before on SO.

Where you have common and very ambiguous names, then prefix with table name. That is, anything you're liable to have to alias in almost every query.

So for an Employee table I'd have

EmployeeID
EmployeeName
Comment
Salary
StartDate
EndDate
InsertedDateTime
...

And Wikipedia actually says:

  The USING construct is more than mere syntactic sugar, however, since the result set differs from the result set of the version with the explicit predicate. Specifically, any columns mentioned in the USING list will appear only once, with an unqualified name, rather than once for each table in the join.

That is one less column. You'd never use SELECT * anyway so the point is moot...


Why MySQL ignores foreign keys when importing?

By default mysqldump automatically includes a statement in the dump output to set foreign_key_checks to 0.

So If you want force MySQL to not ignore FKs when importing, Use --compact switch in your command:

For Example:

mysqldump --ignore-table=example.test --compact --no-create-info old_database > data.sql

Refrence: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_compact

Update

By defult, foreign_key_checks is set 1. this means if each of your DML queriies has FK error, the query execution will be rejected with the error.

For incresing the speed of restores,
MySQL set foreign_key_checks 0 (in Dump file) and after that the restoring was done, MySQL set foreign_key_checks 1. unless you had set foreign_key_checks 0.


UNION ALL implicit conversion for VARCHAR(MAX) column

Have a look at MS DOCS about Guidelines for Using Union:

Quoted from docs:

All select lists in the statements that are being combined with UNION must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on).
Corresponding columns in the result sets that are being combined with UNION, or any subset of columns used in individual queries, must be of the same data type, have an implicit data conversion possible between the two data types, or have an explicit conversion supplied. For example, a UNION between a column of datetime data type and one of binary data type will not work unless an explicit conversion is supplied. However, a UNION will work between a column of money data type and one of int data type, because they can be implicitly converted.

 declare @v1 varchar(1) = 'a';
 declare @v2 varchar(max) = 'a';

 set statistics profile on;
 select @v1
 union all
 select @v2;

 select cast(@v1 as varchar(10))
 union all
 select cast(@v2 as varchar(10));
 GO

Rows | Executes | StmtText                                                                      | StmtId | NodeId | Parent | PhysicalOp    | LogicalOp     | Argument                                                  | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList  | Warnings | Type     | Parallel | EstimateExecutions
:--- | :------- | :---------------------------------------------------------------------------- | -----: | -----: | -----: | :------------ | :------------ | :-------------------------------------------------------- | :------------ | :----------- | :--------- | :---------- | ---------: | :--------------- | :---------- | :------- | :------- | :------- | :-----------------
2    | 1        | select @v1
union all
select @v2                                        |      1 |      1 |      0 | null          | null          | null                                                      | null          | 2            | null       | null        |       null | 2.157E-06        | null        | null     | SELECT   | False    | null
2    | 1        |   |--Constant Scan(VALUES:((CONVERT_IMPLICIT(varchar(max),[@v1],0)),([@v2]))) |      1 |      2 |      1 | Constant Scan | Constant Scan | VALUES:((CONVERT_IMPLICIT(varchar(max),[@v1],0)),([@v2])) | null          | 2            | 0          | 2.157E-06   |       4035 | 2.157E-06        | [Union1001] | null     | PLAN_ROW | False    | 1

Rows | Executes | StmtText                                                                                | StmtId | NodeId | Parent | PhysicalOp    | LogicalOp     | Argument                                                               | DefinedValues | EstimateRows | EstimateIO | EstimateCPU | AvgRowSize | TotalSubtreeCost | OutputList  | Warnings | Type     | Parallel | EstimateExecutions
:--- | :------- | :----------------------------------------------------------------------------------------- | -----: | -----: | -----: | :------------ | :------------ | :--------------------------------------------------------------------- | :------------ | :----------- | :--------- | :---------- | ---------: | :--------------- | :---------- | :------- | :------- | :------- | :-----------------
2    | 1        | select cast(@v1 as varchar(10))
union all
select cast(@v2 as varchar(10))            |      2 |      1 |      0 | null          | null          | null                                                                   | null          | 2            | null       | null        |       null | 2.157E-06        | null        | null     | SELECT   | False    | null
2    | 1        |   |--Constant Scan(VALUES:((CONVERT(varchar(10),[@v1],0)),(CONVERT(varchar(10),[@v2],0)))) |      2 |      2 |      1 | Constant Scan | Constant Scan | VALUES:((CONVERT(varchar(10),[@v1],0)),(CONVERT(varchar(10),[@v2],0))) | null          | 2            | 0          | 2.157E-06   |         16 | 2.157E-06        | [Union1002] | null     | PLAN_ROW | False    | 1

dbfiddle here


How to write a check constraint to disallow empty varchar fields?

To allow strings containing only spaces but disallow empty strings you can use

CREATE TABLE varchar_without_empty_cols
  (
     id  INT,
     val VARCHAR(10) NOT NULL CHECK (DATALENGTH(val) > 0)
  )


Open Management Studio with configured connections

I'm not sure of a way to do this 'natively' in SSMS, but I've been using SSMS Boost for years and it has that functionality (and a whole lot more). It's a plugin for SSMS and they have a 'free' community version that just needs to be re-downloaded every few months.

Check here and scroll down until you see Download links (updated on 14-th of March 2017):

After installation, there will be a new menu bar option 'SSMSBoost'. Click on the menu bar and select 'Settings'.  Then look for 'Preferred Connections'.  There, you will have a checkbox to open a defined connection on SSMS startup.

You can 'add' preferred connections by clicking the 'plus' sign at the bottom of the screen.  Preferred connections also allow you to specify an 'Important DB Alert' that will actually be displayed on new query windows - I use mine to inform me I'm in a 'production' database.


Application unable to connect after doing a failover ,Mirroring

How is your application connecting to the SQL Server?

If it is not a direct connection and involve two hops(for example lets say you have an web tier that goes through an App tier and then to SQL Server) it might be related to kerberos authentication.

You can check if if the connection that were coming into the principle server for the application were NTLM or Kerberos.  Please check the below link

Using Kerberos Authentication with SQL Server

If it is, that means that your principle server was configured correctly to facilitate kerberos authentication and you would have to do the same thing for secondary server.

Register a Service Principal Name for Kerberos Connections

Another not so ideal situation could be that someone has added  NT Authority\anonymous as login on the primary server, you could technically make it work by adding the same thing to the secondary server, but it is not recommended.


MySQL queries are 10 to 100 times slower after OS reboot

When you reboot the OS you remove all of the disk reads that have previously put into operating system disk cache (RAM). Once you've rebooted, the operating system will have to read the MySQL data from disk, which is several orders of magnitude slower than reading from cache (RAM).

Optimise "fixes" this as it causes MySQL to read all of the table data from disk, enabling the OS to cache the data.

Linux uses most free RAM as disk cache. You can see this using the free command:

[oracle@ora12c1 ~]$ free
             total       used       free     shared    buffers     cached
Mem:       4050844     840072    3210772          0      97268     349716
-/+ buffers/cache:     393088    3657756
Swap:      4063228          0    4063228
[oracle@ora12c1 ~]$

Restarting MySQL has no affect on this cache, as it is the operating system that has cached the data, not MySQL.

To "fix" your problem you'll have to force the OS to read the data from disk so that it will cache it. You can use the Unix dd tool to do this. For example:

dd if=/var/mysql/data/mytable.idb of=/dev/null

Tip: you can verify how much of a given file is in the disk cache with the vmtouch command. That command can also purge the file out of the filesystem cache, or force loading the file into the filesystem cache. See http://hoytech.com/vmtouch/


How to stop the SQL Server Agent Job Scheduler with T-SQL?

I would recommend to start by creating a staging table that will hold some information for you. The table should be constructed to hold the data the below code outputs. I recommend to store the Schedule_ID, name, and job_id at the minimum. (If you want to cheat, you can expand the asterisks and select the columns you want, and then add the word INTO along with the database.schema.table_name_you_want_to_create and it will create the table for the first time for you. After that you can change it into an insert.)

SELECT * FROM
MSDB.dbo.sysschedules ss
INNER JOIN msdb.dbo.sysjobschedules jss
   ON jss.schedule_id = ss.schedule_id
WHERE ss.enabled = 1

That code returns the schedules for all schedules and it also performs an inner join to limit the data returned to only schedules that are paired to jobs currently that are enabled.

You can then create a loop or cursor or something similar to loop through the staging table and execute the sp_update_schedule procedure. This will disable all schedules that are enabled and paired to a job. Once your maintenance is complete, you can run the loop once more, but this time enabling the schedules you had disabled earlier.

If you would like an example of a loop, you can see an example I have created in the past on stack overflow.

I have gone back and used the suggestions to create a complete code solution. Please see below, there are two scripts in total. The first will disable your jobs and the second will re-enable them. TEST THIS IN A TEST ENVIRONMENT BEFORE PUTTING IT IN PRODUCTION. Source for completeness.

Script one:

USE MSDB;

/*************************************************************
 Checking for history table. Creating it if it doesn't exist.
*************************************************************/

IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NULL
BEGIN
CREATE TABLE [dbo].[JobsEnabledTracker](
[Id] [INT] IDENTITY(1, 1) NOT NULL,
[job_id]      [UNIQUEIDENTIFIER] NULL,
[schedule_id] [BIGINT] NULL,
[enabled]     [BIT] NULL);
END;
IF EXISTS
(
    SELECT
           1
    FROM [dbo].[JobsEnabledTracker]
    WHERE [enabled] = 1
)
   OR
(
    SELECT
           COUNT(*)
    FROM [dbo].[JobsEnabledTracker]
) = 0
    BEGIN
        PRINT 'There are jobs enabled or there are no jobs yet populated in the history table.';

/***********************
 Clear out history table
***********************/

        PRINT 'Truncating history table: dbo.JobsEnabledTracker';
        TRUNCATE TABLE [dbo].[JobsEnabledTracker];

        PRINT 'Inserting records into history table: dbo.JobsEnabledTracker';

/******************************
 Add in values to history table
******************************/

        INSERT INTO [dbo].[JobsEnabledTracker]
        (
               [job_id],
               [schedule_id],
               [enabled]
        )
        SELECT
               [jss].[job_id],
               [jss].[schedule_id],
               1 AS 'enabled'
        FROM [msdb].[dbo].[sysschedules] AS [ss]
             INNER JOIN [msdb].[dbo].[sysjobschedules] AS [jss] ON [jss].[schedule_id] = [ss].[schedule_id]
        WHERE [ss].[enabled] = 1;

/**********************************************************************************
 Table variable to hold schedules and jobs enabled. This is important for the loop.
**********************************************************************************/

        DECLARE @JobsEnabled TABLE
        ([Id]          INT
         PRIMARY KEY IDENTITY(1, 1),
         [job_id]      UNIQUEIDENTIFIER,
         [schedule_id] BIGINT,
         [enabled]     BIT
        );

/*****************************************
 Insert schedules that we need to disable.
*****************************************/

        INSERT INTO @JobsEnabled
        (
               [job_id],
               [schedule_id],
               [enabled]
        )
        SELECT
               [job_id],
               [schedule_id],
               [enabled]
        FROM [dbo].[JobsEnabledTracker];

/********************************
 Holds the job id and schedule id
********************************/

        DECLARE @jobid UNIQUEIDENTIFIER;
        DECLARE @scheduleid BIGINT;

/***********************************
 Holds the ID of the row in the loop
***********************************/

        DECLARE @ID INT= 0;

/**********************
 Check if records exist
**********************/

        IF EXISTS
        (
            SELECT
                   [Id]
            FROM @JobsEnabled
        )
            BEGIN
                PRINT 'Loop mode, jobs found enabled.';

/**********
 Begin loop
**********/

                WHILE(1 = 1)
                    BEGIN

/***************************************
 Grab jobid, scheduleid, and id of rows.
***************************************/

                        SELECT
                               @jobid =
                        (
                            SELECT TOP 1
                                   [job_id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );
                        SELECT
                               @scheduleid =
                        (
                            SELECT TOP 1
                                   [schedule_id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );
                        SELECT
                               @ID =
                        (
                            SELECT TOP 1
                                   [Id]
                            FROM @JobsEnabled
                            ORDER BY
                                     [job_id]
                        );

/************************************
 Re-enable schedule associated to job
************************************/

                        PRINT 'Disabling schedule_id: '+CAST(@scheduleid AS VARCHAR(255))+' paired to job_id: '+CAST(@jobid AS VARCHAR(255));
                        EXEC [sp_update_schedule]
                             @schedule_id = @scheduleid,
                             @enabled = 0;

/*********************
 Removes row from loop
*********************/

                        DELETE FROM @JobsEnabled
                        WHERE
                              [Id] = @ID;

                        UPDATE [dbo].[JobsEnabledTracker]
                          SET
                              [enabled] = 0
                        WHERE
                              [job_id] = @jobid
                              AND [schedule_id] = @scheduleid;

/****************************
 No more rows, stops deleting
****************************/

                        IF
                        (
                            SELECT
                                   COUNT(*)
                            FROM @JobsEnabled
                        ) <= 0
                            BEGIN
                                BREAK
                            END;

/********
 End Loop
********/
                    END;
                PRINT 'Exiting loop, disabling schedules paired to jobs complete.';

/**********
 End elseif
**********/
            END;
            ELSE
            BEGIN
                PRINT 'All done';
            END;
    END;
    ELSE
    BEGIN
        PRINT 'YOU HAVE JOBS STILL DISABLED, EXITING SCRIPT. PLEASE RUN SCRIPT TWO FIRST.';
    END;

Script two

USE MSDB;

/*******************************************************************************
 Check for history table. This physical table tells us what jobs we are going to
 enable the scheduler for.
*******************************************************************************/

IF OBJECT_ID('dbo.JobsEnabledTracker', 'U') IS NOT NULL
BEGIN
    IF EXISTS
    (
        SELECT 1
        FROM [dbo].[JobsEnabledTracker]
        WHERE [enabled] = 0
    )
    BEGIN
        PRINT 'Jobs disabled in history table: dbo.JobsEnabledTracker found.';

/**********************************************************************************
 Table variable to hold schedules and jobs enabled. This is important for the loop.
**********************************************************************************/

        DECLARE @JobsEnabled TABLE
        (
        [Id] int PRIMARY KEY IDENTITY(1, 1)
        , [job_id] uniqueidentifier
        , [schedule_id] bigint
        , [enabled] bit
        );

/*******************************************************************************
 Insert schedules that we had disabled that we need to go back in and re-enable.
*******************************************************************************/

        INSERT INTO @JobsEnabled( [job_id], [schedule_id], [enabled] )
               SELECT [job_id], [schedule_id], [enabled]
               FROM [dbo].[JobsEnabledTracker];

/********************************
 Holds the job id and schedule id
********************************/

        DECLARE @jobid uniqueidentifier;
        DECLARE @scheduleid bigint;

/***********************************
 Holds the ID of the row in the loop
***********************************/

        DECLARE @ID int= 0;

/**********************
 Check if records exist
**********************/

        IF EXISTS
        (
            SELECT [Id]
            FROM @JobsEnabled
        )
        BEGIN
            PRINT 'Loop mode, jobs found disabled.';

/**********
 Begin loop
**********/

            WHILE 1 = 1
            BEGIN

/***************************************
 Grab jobid, scheduleid, and id of rows.
***************************************/

                SELECT @jobid =
                (
                    SELECT TOP 1 [job_id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );
                SELECT @scheduleid =
                (
                    SELECT TOP 1 [schedule_id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );
                SELECT @ID =
                (
                    SELECT TOP 1 [Id]
                    FROM @JobsEnabled
                    ORDER BY [job_id]
                );

/***************************************
 Re-enable schedule associated to job
***************************************/

                PRINT 'Enabling schedule_id: '+CAST(@scheduleid AS varchar(255))+' paired to job_id: '+CAST(@jobid AS varchar(255));
                EXEC [sp_update_schedule] @schedule_id = @scheduleid, @enabled = 1;

/*********************
 Removes row from loop
*********************/

                DELETE FROM @JobsEnabled
                WHERE [Id] = @ID;

/***********************
 Set job back to enabled
***********************/

                UPDATE [dbo].[JobsEnabledTracker]
                  SET [enabled] = 1
                WHERE [job_id] = @jobid AND
                      [schedule_id] = @scheduleid;

/****************************
 No more rows, stops deleting
****************************/

                IF
                (
                    SELECT COUNT(*)
                    FROM @JobsEnabled
                ) <= 0
                BEGIN
                    BREAK;
                END;

/********
 End Loop
********/
            END;
            PRINT 'Exiting loop, enabling schedules paired to jobs complete.';

/**********
 End elseif
**********/
        END;
        ELSE
        BEGIN
            PRINT 'All done';
        END;
    END;
    ELSE
    BEGIN
        PRINT 'dbo.JobsEnabledTracker has no disabled jobs currently.';
    END;
END;
ELSE
BEGIN
    PRINT 'dbo.JobsEnabledTracker is NULL, you may need to run the first script to create and populate this table.';
END;


Why do linked servers have a limitation of 10 branches in a CASE expression?

Clearly there isn't a nested CASE expression here.

Not in the query text, no. But the parser always expands CASE expressions to the nested form:

SELECT CASE SUBSTRING(p.Name, 1, 1)
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        WHEN 'c' THEN '3'
        WHEN 'd' THEN '4'
        WHEN 'e' THEN '5'
        WHEN 'f' THEN '6'
        WHEN 'g' THEN '7'
        WHEN 'h' THEN '8'
        WHEN 'i' THEN '9'
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END
FROM AdventureWorks2012.Production.Product AS p

That query is local (no linked server) and the Compute Scalar defines the following expression:

This is fine when executed locally, because the parser does not see a nested CASE statement over 10 levels deep (though it does pass one on to the later stages of local query compilation).

However, with a linked server, the generated text may be sent to the remote server for compilation. If that is the case, the remote parser sees a nested CASE statement more than 10 levels deep and you get error 8180.

  Another oddity. This inline table-valued function produces the same
  error

The in-line function is expanded in-place into the original query text, so it's no surprise the same error results with the linked server.

  But a similar multi-statement TVF works fine

Similar, but not the same. The msTVF involves an implicit conversion to varchar(max), which happens to prevent the CASE expression being sent to the remote server. Because the CASE is evaluated locally, a parser never sees an over-nested CASE and there is no error. If you change the table definition from varchar(max) to the implicit type of the CASE result - varchar(2) - the expression is remoted with the msTVF and you will get an error.

Ultimately, the error occurs when an over-nested CASE is evaluated by the remote server. If the CASE is not evaluated in the Remote Query iterator, no error results. For example, the following includes a CONVERT that is not remoted, so no error occurs even though a linked server is used:

SELECT CASE CONVERT(varchar(max), SUBSTRING(p.Name, 1, 1))
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        WHEN 'c' THEN '3'
        WHEN 'd' THEN '4'
        WHEN 'e' THEN '5'
        WHEN 'f' THEN '6'
        WHEN 'g' THEN '7'
        WHEN 'h' THEN '8'
        WHEN 'i' THEN '9'
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END
FROM SQL2K8R2.AdventureWorks.Production.Product AS p


A form including content from two tables

The way you have the database setup seems fine.  I am not sure this is actually a DB admin question as much as it is a front end question.

MySQL or MariaDB would be a fantastic DB to use this on.  You can also use MySQL Workbench, which is free, to manage the DB if you want.  I think for something this simple, I would just use the command line MySQL client.

If this is truly a front end question about how to make a form, I would recommend using the PDO functionality in PHP.

Once you decide this for sure, just ask about the proper queries to write and how to use PDO, if you need to.  (That might be more appropriate for Stack Overflow, though)


How to measure or find cost of creating a query plan?

How to find out what the cost of creating a query plan is?

You can look at the properties of the root node in the query plan, for example:

(screenshot from the free Sentry One Plan Explorer)

This information is also available by querying the plan cache, for example using a query based on the following relationships:

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT
    CompileTime = c.value('(QueryPlan/@CompileTime)[1]', 'int'),
    CompileCPU = c.value('(QueryPlan/@CompileCPU)[1]', 'int'),
    CompileMemory = c.value('(QueryPlan/@CompileMemory)[1]', 'int'),
    ST.[text],
    QP.query_plan
FROM sys.dm_exec_cached_plans AS CP
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) AS QP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) AS ST
CROSS APPLY QP.query_plan.nodes('ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple') AS N(c);

For a full treatment of the options you have for handling these sorts of queries, see Erland Sommarskog's recently updated article.


Can sqlite dump database schema in a fixed order?

SQLiteStudio sorts the database objects by type (table, index, trigger, view), but inside each group, sorts them randomly.
(If you consider this a bug, report it.)

The sqlite3 shell dumps tables in the order in which they are stored in the sqlite_master table, which is typically the same order in which they were (re)created.


Updating multiple tables with similar structure in one query in mysql

The solution might look like:

CREATE PROCEDURE update_all_tables (IN id BIGINT, IN val VARCHAR(64))
BEGIN
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @done = 1;
    DECLARE cur CURSOR FOR SELECT table_name
                           FROM information_schema.tables
                           WHERE table_schema = 'MyDatabase'
                           AND table_name LIKE 'table%';
    OPEN cur;
    LOOP
        @done := 0;
        FETCH cur INTO @table;
        IF @done THEN LEAVE LOOP;
        SET @sql := CONCAT('UPDATE ',@table,' SET field1 = ? WHERE id = ?');
        PREPARE stmt FROM @sql;
        EXECUTE stmt USING val, id;
        DEALLOCATE PREPARE stmt;
    END LOOP;
    CLOSE cur;
END;

PS. Don't forget DELIMITER statement...


Single address table for company, company_branch, users

I would suggest something like this.

The tool used to data modeling is SQL Developer Data Modeler


I believe using a SUM + CASE expression would work, because the CASE enables you to do a 'selective count'. Or you can use the newer FILTER:

SELECT a.id,
  count(*) FILTER (WHERE d.event_time > now() - interval '1 day') AS daily,
  count(*) AS total
FROM account_test a
JOIN log_test d ON a.id=d.account
GROUP BY a.id;

With the dataset from your question, this gives the following results:

 id | daily | total
----+-------+-------
  2 |    1  |    3
  1 |    2  |    5


How can I separate some collections to another server?

Yes, it's possible. But after moving data to another server, your application must have mean to connect that server when it needs to read/write that DB.Collection.

It is possible even in sharded cluster, I have done that. In that case too, application must have mean to connect that "second" RS or Cluster.

You must give more information, if you need doable solution.

With https://github.com/mongodb-labs/mongo-connector you can easily replicate single/RS/Cluster to destination...


SQL Server 2008 database is not coming online after restore.. Pleae need help asap

I know this is an old question but will leave this here for anyone else who comes looking.
Ran into this problem today and was able to get it going.

This is difficult to believe but disabling parallelism got it online in my case.

I never would have thought to even try that, only I was watching the sysprocesses for that DB and was seeing the parallelism split / cxpacket wait behavior… the wait types would change like it was working on something.
Thinking maybe maxdop=1 would help whatever this was by not splitting up, changed it and the DB immediately came online.

exec sp_configure 'show advanced options', 1
go
reconfigure
go
exec sp_configure 'max degree of parallelism', 1
go
reconfigure
go

Other info:

Mine was also SQL 2008 database is 176GB
Also saw (at some point) a system spid (under 51) showing "Create Index"
Runs change data capture in production (not sure that had anything to do with it)
Was restoring copy to a dev environment as part of a regular data refresh from production.
Disk space was very tight on the destination server it had just enough room for the data and log files to restore, but was under 10 meg free at that point.
I think the lack of space may have helped cause the problem? I was able to free up some space before the maxdop change, but the database was still not usable until the maxdop setting was changed.
sys.databases showed the state as 'online'
the database looked usable in every way but any attempt to use it would time out


Show master status returns empty set of rows

There are many variables for MySQL that are not dynamic (cannot be changed inflight) and require a service restart. This includes some of the binary log specific variables. Consult the MySQL documentation for further info on each variables as and when you discover you need to update their values.

Restarting the process from the service manager should allow these new settings to take hold.


Moving a large MySql database(Over 15 GB) from server to server mysqldump

What you need to do is mysqldump separate tables in batches and load them.

On your new CentOS server, write the following

MYSQL_SOURCE_HOST=10.20.30.40
MYSQL_SOURCE_USER=root
MYSQL_SOURCE_PASS=rootpassword
SRC_CONN="-h${MYSQL_SOURCE_HOST} -u${MYSQL_SOURCE_USER} -p${MYSQL_SOURCE_PASS}"

MYSQL_TARGET_HOST=localhost
MYSQL_TARGET_USER=root
MYSQL_TARGET_PASS=rootpassword
TGT_CONN="-h${MYSQL_TARGET_HOST} -u${MYSQL_TARGET_USER} -p${MYSQL_TARGET_PASS}"

SQL="SELECT table_name FROM information_schema.tables"
SQL="${SQL} WHERE table_schema = 'xcart'"
SQL="${SQL} ORDER BY data_length"
mysql ${SRC_CONN} -ANe"${SQL}" > /tmp/ListOfTables.txt

COMMIT_COUNT=0
COMMIT_LIMIT=10
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers --events"
for TBL in `cat /tmp/ListOfTables.txt`
do
    mysqldump ${SRC_CONN} ${MYSQLDUMP_OPTIONS} xcart ${TBL} | mysql ${TGT_CONN} -Dxcart &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ] ; then wait ; fi

I have other ideas from my old post : How can I optimize a mysqldump of a large database?

Give it a Try !!!


How to retrieve closest value based on look-up table?

A couple of queries tested and optimized for Postgres 9.3. All return the same, all are basically standard SQL, but no RDBMS supports the standard completely.
In particular, the first one uses a LATERAL JOIN, which is missing in Oracle or MySQL. Test which performs best.
All of them use index-only scans on the lookup table in Postgres. Obviously, lookup.nominal_value needs to be indexed. I suggest to make it UNIQUE because it seems like the column should be unique, and because that also creates the all-important index automatically.
LATERAL JOIN
SELECT m.id, m.measurement, l.nominal_value
FROM   measurement m
JOIN LATERAL (
   (
   SELECT nominal_value - m.measurement AS diff, nominal_value
   FROM   lookup
   WHERE  nominal_value >= m.measurement
   ORDER  BY nominal_value
   LIMIT  1
   )
   UNION  ALL
   (
   SELECT m.measurement - nominal_value, nominal_value
   FROM   lookup
   WHERE  nominal_value <= m.measurement
   ORDER  by nominal_value DESC
   LIMIT  1
   )
   ORDER  BY 1  -- NULLS LAST is default
   LIMIT  1
   ) l ON TRUE;

All parentheses required for UNION. Related answer:
Postgres 9.2 select multiple specific rows in one query
Correlated subqueries in a subquery
SELECT id, measurement
      ,CASE WHEN hi - measurement > measurement - lo
         THEN lo
         ELSE COALESCE(hi, lo)  -- cover all possible NULL values
       END AS nominal_value
FROM (
   SELECT id, measurement
         ,(SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value >= m.measurement
           ORDER  BY nominal_value
           LIMIT  1) AS hi
         ,(SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value <= m.measurement
           ORDER  by nominal_value DESC
           LIMIT  1) AS lo   -- cover possible NULL values
   FROM   measurement m
   ) sub;

Correlated subqueries in a CTE
WITH cte AS (
   SELECT id, measurement
         ,(SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value >= m.measurement
           ORDER  BY nominal_value
           LIMIT  1) AS hi
         ,(SELECT nominal_value
           FROM   lookup
           WHERE  nominal_value <= m.measurement
           ORDER  by nominal_value DESC
           LIMIT  1) AS lo
   FROM   measurement m
   )
SELECT id, measurement
      ,CASE WHEN hi - measurement > measurement - lo
         THEN lo
         ELSE COALESCE(hi, lo)  -- cover all possible NULL values
       END AS nominal_value
FROM cte;

Nested correlated subqueries
SELECT id, measurement
      ,(SELECT nominal_value FROM (
         (
         SELECT nominal_value - m.measurement, nominal_value
         FROM   lookup
         WHERE  nominal_value >= m.measurement
         ORDER  BY nominal_value
         LIMIT  1
         )
         UNION  ALL
         (
         SELECT m.measurement - nominal_value, nominal_value
         FROM   lookup
         WHERE  nominal_value <= m.measurement
         ORDER  by nominal_value DESC
         LIMIT  1
         )
         ORDER  BY 1
         LIMIT  1
         ) sub
         ) AS nominal_value
FROM   measurement m;

SQL Fiddle.


Group data by non-unique keys by distinct time range

Below a complete solution for sql-server.

I was trying analytical functions, but didn't succeed. I'm tempted to repost the question to SO requiring a solution using analytical functions. I think there must be one.

And here is my working solution. I'm proud not to have used cursors.

set language us_english
Set Nocount ON

create table #t( PROJ_NBR char(4),  STATUS char(2), START_DT datetime);

insert into #t values ('AL20',     'AC', '1/14/2010 4:31');
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');
insert into #t values ('AL20',     'AC', '1/14/2010 4:32');
insert into #t values ('AL20',     'DE', '1/14/2010 4:34');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'DE', '1/14/2010 4:46');
insert into #t values ('AL20',     'AC', '3/4/2010 4:31');
insert into #t values ('AL20',     'AC', '3/4/2010 4:39');
insert into #t values ('AL20',     'AC', '3/21/2010 13:24');
insert into #t values ('AL20',     'AC', '2/4/2011 13:54');
insert into #t values ('AL20',     'AC', '2/4/2011 14:14');
insert into #t values ('AL20',     'AC', '2/4/2011 14:27');
insert into #t values ('AL20',     'DE', '2/11/2011 16:24');

select
    distinct PROJ_NBR, STATUS, [start], [end]
from(
select
*,
(select MIN(START_DT)
    from #t t1
    where t.STATUS = t1.STATUS
    and t.START_DT >= t1.START_DT
    and not exists ( select * from #t t2
                    where t.STATUS <> t2.STATUS
                    and  t.START_DT >= t2.START_DT
                    and t1.START_DT <= t2.START_DT)
) [start],
(select Max(START_DT)
    from #t t1
    where t.STATUS = t1.STATUS
    and t.START_DT <= t1.START_DT
    and not exists ( select * from #t t2
                    where t.STATUS <> t2.STATUS
                    and  t.START_DT <= t2.START_DT
                    and t1.START_DT >= t2.START_DT)
) [end]

from #t t
) t1
order by [start]

I think besides column and table names this can easily adapted to Oracle too.


What language and data structures does MYSQL uses as backend?

MySQL can be compiled in C/C++ and you can download the source code to do so.

At this link, Under the Select Platform ComboBox, Select Source Code and Download it from there

The .frm , .MYD, and .MYI make up a MyISAM table

For example, the user table for the mysql grants is amde up of three files

/var/lib/mysql/mysql/user.frm (Table Structure for any Storage Engine)
/var/lib/mysql/mysql/user.MYD (MyISAM Table Data)
/var/lib/mysql/mysql/user.MYI (MyISAM Table Indexes)