query two point values from a table then insert a new record after creating a line from the two points

You have to put the literal strings in the right spot.  You have them as part of the FROM clause, which doesn't make sense.  Put them in the select-list.

insert into locations (geom,type,label,user) select
    st_makeline(a.geom,b.geom) as line, 'Link','test','ADMIN'
from (select * from locations where id = 144) as a,
     (select * from locations where id = 145) as b;


Query for a table with paging and filtering vs. CTE (common table expression)

We have a similar issue with CTEs. From what I gather researching the question, and from actually testing on our own queries, indexes which would have been used to filter the results in the CTE when used in a WHERE clause outside of the CTE aren't used because, as mentioned here, the CTE acts as an optimization fence. This means that, for performance reasons, you will want to refactor queries using CTEs to use subqueries instead.

We had a bunch of queries that used CTEs where we gained an order or two of magnitude of performance when we refactored them to subqueries — in one case, we dropped query time from approx. 2 minutes to just under a second. So keep that in mind when building queries.

So, in your example, you would use the query inside the CTE as a subquery instead:

SELECT * FROM (
    SELECT
      customer.name,
      customer.surname,
      (SELECT aggregate_whatever(foo) FROM bar) past_purchases,
      baz.aaa bbb,
      gaz.ccc ddd,
      ...
    FROM
      customers customer
    LEFT JOIN
      baz ON ...
    LEFT JOIN
      gaz ON ...
) encapsulated
WHERE
  past_purchases = 5
  AND <other conditions>

(In our case, we had relatively abysmal performance on some of our queries due to us using XPath column expressions, combined with scanning upwards of 30k rows in a table, caused us to spend seconds on calculating those XPaths on rows which would ultimately be discarded anyway. Removing the CTEs and using subqueries sped things up considerably, as the XPath columns would be calculated only for the actual rows returned).


Why does mysql treat "<" like it was "<="?

Are you working with datetimes?

Notice that If I ran it now I'd get for instance:

August, 28 2014 13:57:36+0000

Which means that if my dates are at midnight I'd get the same with < and <=

You should probably cast this into DATE if you are to work with dates


Query to filter a table based on a comparison applied to the same table?

In a situation like this one, I recommend having two tables:

History (like what you have).  This is a list (a la audit trail) of everything that has happened (INs and OUTs).
Current -- The latest place where each person is.

History has an ever-growing number of rows, and is probably not relevant for the Question at hand.

Current contains one row per person.  A simple SELECT with a WHERE clause should pick out all the persons that are "IN" (or "OUT").

"separate tables for each building or company" -- No.  This is the third time today I have advised against having multiple identical schemas.

On the other hand, Current and History may have the same columns.  This is an exception to the previous paragraph.

Do not split by department or building.


No Primary Key in some sql server tables

To see which tables have no primary key, run the following script:

SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
EXCEPT
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY'

You will get the following list:

TABLE_NAME
----------
BuyingGroups_Archive
Cities_Archive
ColdRoomTemperatures_Archive
Colors_Archive
Countries_Archive
CustomerCategories_Archive
Customers_Archive
DeliveryMethods_Archive
PackageTypes_Archive
PaymentMethods_Archive
People_Archive
StateProvinces_Archive
StockGroups_Archive
StockItems_Archive
SupplierCategories_Archive
Suppliers_Archive
TransactionTypes_Archive

Now it's obvious that the Archive tables have no primary keys.
I guess it was a design choice, you can argue that a table without a primary key is not a relation and of course has no guarantee of being able to identify every row. But, I can see how someone would think that since the only source of data comes from the non archive table that have PKs, so there is less risk.
IMHO that is wrong, but not uncommon.

Since the project is on GitHub, you can contribute PKs to the schema.

HTH

UPDATE:
Martin correctly commented that these are in fact the temporal history tables, and therefore can't have PKs or other constraints as they are system managed.


How to log DML statements executed by a pl/pgsql function?

Question asked

There is a built-in way to log all statements inside plpgsql functions: auto-explain

LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries
SET auto_explain.log_nested_statements = ON; -- statements inside functions

Details under this closely related question:
Postgres query plan of a UDF invocation written in pgpsql

Potentially generates a lot of log output. I would only use it for debugging, not in production.
If you just need the one statement logged, go with @dezso's advice.

Code audit

Consider this rewritten function:

CREATE OR REPLACE FUNCTION fnct_clear_temp_fields()
  RETURNS void AS
$func$
DECLARE
   rec record;
   qry text;
BEGIN
   FOR rec IN
      SELECT quote_ident(c.relname) AS tbl, quote_ident(a.attname) AS col
      FROM   pg_namespace n
      JOIN   pg_class     c ON c.relnamespace = n.oid
      JOIN   pg_attribute a ON a.attrelid = c.oid
      WHERE  n.nspname = 'public'
      AND    c.relkind = 'r'
      AND    a.attname LIKE 'temp_%'  -- LIKE is faster than ~
      AND    a.attnum > 0
      AND    NOT a.attisdropped
      ORDER  BY 1,2
   LOOP
      RAISE NOTICE 'Table: %, Column: %', rec.tbl, rec.col;
      qry := format('UPDATE %1$s SET %2$s = NULL WHERE %2$s IS NOT NULL', rec.tbl, rec.col);
      RAISE LOG 'Query: %', qry;
      EXECUTE qry;
   END LOOP;
END
$func$  LANGUAGE plpgsql;

Major points

You must sanitize all identifiers that you build into dynamic SQL, else it can fail with non-standard names that require double-quoting. Worse, you are open to SQL injection.
Demonstrating quote_ident(), since you are using the sanitized identifiers multiple times. There are more options with regclass or format():
Table name as a PostgreSQL function parameter
I prefer to base such queries on the system catalog instead of the painfully slow views of the information schema. That's a matter of requirements and taste, though. Demonstrating the equivalent, which is ~ 10 times faster (not regarding UPDATE commands). More:
How to check if a table exists in a given schema
LIKE is generally faster than the more powerful regular expression matching (~). If LIKE can do the job, use it.
Some other minor simplifications.
Related answer with more details:
Update record of a cursor where the table name is a parameter


how to nest query with avg and sum group by outer in MySQL 5.7

There is no need to define inner query, try this,

added if condition in app_id count to prevent divisible by zero error

SELECT
    tenant_code,
    SUM(reg_count) AS sum_reg_count,
    IF(COUNT(app_id) = 0, 0, SUM(reg_count) / COUNT(app_id)) AS avg_reg_count,
    IF(COUNT(app_id) = 0, 0, FLOOR(SUM(reg_count) / COUNT(app_id))) AS round_reg_count
FROM h_tenant_overview
GROUP BY tenant_code

Output:
tenant_code | sum_reg_count | avg_reg_count | round_reg_count
1             1               0.5000          0
2             2               1.0000          1
3             10              5.0000          5


How can I detect the availability of sp_addextendedproperty on sql server

Use OBJECT_ID to see of the object exists.  The function will return NULL if not:

IF OBJECT_ID(N'sp_addextendedproperty', 'P') IS NOT NULL
    EXEC sp_addextendedproperty ...


Define the outer join as a separate, non-recursive, CTE:

WITH FactorColumns AS
(
  SELECT  a.FactorTitle,
          a.FactorCode,
          a.parentId,
          c.FactorColumnCode,
          c.FactorColumnTitle,
          c.FactorColumnValue,
          c.isFactorValue,
          c.FieldType
  FROM FactorSetup a
  LEFT JOIN FactorColumnSetup c ON a.FactorCode=c.FactorCode
),
...

Now just reference FactorColumns in EntityChildren – and syntactically your recursive CTE will have no outer join:

...
EntityChildren AS
(
  SELECT  f.FactorTitle,
          f.FactorCode,
          f.parentId,
          f.FactorColumnCode,
          f.FactorColumnTitle,
          f.FactorColumnValue,
          f.isFactorValue,
          f.FieldType
  FROM FactorColumns AS f

  UNION ALL

  SELECT f.FactorTitle,
         f.FactorCode,
         f.parentId,
         f.FactorColumnCode,
         f.FactorColumnTitle,
         f.FactorColumnValue,
         f.isFactorValue,
         f.FieldType
  FROM FactorColumns AS f
  INNER JOIN EntityChildren AS e2 ON f.parentId = e2.FactorCode
)
...


Will auto vacuum analyzer delay visibility of bulk insertion or updation

The reason is synchronous commit. Postgres has introduced this to increase the performance. The client gets results before actually it got committed. That’s why table did not get updated , getting time to update. I was checking before it get updated
Source: https://www.mydatahack.com/top-10-configuration-parameters-to-tune-postgresql-for-better-performance/


configure sql developer for oracle using IPC fails with network adapter error

This is not IPC, but achieves the same, and you do not even need a listener for this.

Make sure you configure SQL Developer to use an Oracle Client with the OCI/Thick Driver. You can use the installed database home as client, and you can set this in Tools / Preferences / Database / Advanced, e.g:

When creating the connection, choose Local/Bequeath as connection type, e.g:


As Denis Rubashkin said in a comment:

  If you had a look at the script text you would see that the script just creates a procedure sp_Blitz. You have to execute that proc (sample execution call with the most common parameters is in the bottom of the sp_Blitz script) to get an output you are waiting for.

You just created the procedure, but you still have to execute it. Here's some extra information if you have more questions:

There's a bunch of good resources you can check to get more information about any of the blitz procedures.

If you like to read:
This is the official documentation on the procedure, you can probably find everything you need here: sp_blitz documentation

If you rather watch videos:
Brent has made some great YouTube videos where he goes through the usage of the of the Blitz procedures.

This one is for the SP_Blitz, but there are also some vids for SP_BlitzIndex / sp_Blitzfirst. (All very useful procedures)
Brent Ozars youtube


No the subscription will not reinitialize when you make this change.  This setting simply tells the distributor how far behind the subscription can get before the subscription will be automatically expired.


Whole process is explained here

Backup is created "elsewhere" (than originating data; of course) and that backup is kept "up-to-date". Time to time snapshots (moments in time) is created (and stored) from that data. In the "side" system keeps copy of opLog too, so it is possible to "roll-forward" from snapshot to specific moment in time (between snapshots).


Short version: use a manual reference, not a DBRef.

Explanation:

There is no particular benefit to the use of a DBRef beyond giving you the collection and database that the referenced document resides in.  If you know the reference is only between categories and products, then that is not particularly useful.  A DBRef simply contains the _id of the referenced document, the collection name and optionally the database name.  You will usually have all that information in your application (i.e. you have the _id and you know that db/collection it resides in already).

If that were not the case, let's say you had references to documents in a slew of databases and collections and it was difficult or cumbersome application side to keep track of which one each document resided in, then it may be a benefit to use DBRefs.  After all, the easiest way to do a manual reference in that case would be to store the database and collection that you are referencing in the document too, and that is what DBRefs do for you in any case.

That (referencing multiple collections from a single source collection) is not a terribly common pattern though and not what you have outlined above, and so unless you have such a compelling reason to use them, manual references are recommended instead.

Some of the drivers have helper methods which may save a few lines of code versus a manual reference, but not all drivers do, and it may make the code harder to understand (without some nice comments/documentation to explain it).  As well as that, the DBREF approach is more fixed than a manual implementation - what if you want to reference a field other than _id for example.

Finally, in terms of product catalog design, I would highly recommend reading these two excellent and detailed blog posts from Antoine Girbal:

Part 1 - Schema Design
Part 2 - Product Search

There were also three presentations given at MongoDB World 2014 on this very topic, so I would recommend reviewing those also as you progress:

Presentation 1: Flexible, Searchable, Low Latency Product
Catalog
Presentation 2: Real-time, Geo-Distributed Inventory
Presentation 3: Scalable Insight Component (User History,
Recommendations, Personalization)


MongoDB 3.0 - how do I check which storage engine is being used?

You can find it through db.serverStatus() on section storageEngine -
http://docs.mongodb.org/manual/reference/command/serverStatus/#storageengine


SQL - double column used from primary key with autoincrement

You can't have more than one IDENTITY column per a table.
Use MAX(index)+1 where userid=X, when inserting a new row without auto-increment.


Is it safe to add a new node to a Cassandra cluster while a repair is running?

As per Datastax documentation,

  Warning: DataStax recommends stopping repair operations during topology changes; the Repair Service does this automatically. Repairs running during a topology change are likely to error when it involves moving ranges.

So, it is clear that topology changes (adding/removing nodes) are not recommended when there is a repair process alive in your cluster


MySQL - two tables with relation one to many - retrieve "n" fields from table1 WHERE SUM of a field on table2 is less/equal/greater than "value"

This is a rather simple GROUP BY / HAVING query in table B. You then only need to join table A to get the columns you want in the result:

SELECT a.*,                   -- pick the wanted columns from A
       bg.sum_toti             -- you can have this in the results, too,
                              -- if you want
FROM a
  JOIN
  ( SELECT id, SUM(toti) AS sum_toti
    FROM b
    GROYP BY id
    HAVING SUM(toti) >= ?              -- ? is the parameter, eg. 585
  ) AS bg
  ON bg.id = a.id ;

You already have an index on a (id). An index on b (id, toti) would help with efficiency.
I suggest you ditch MyISAM and use only InnoDB engine in your databases. It's the default engine in recent versions (5.6+) and gets far more development. MyISAM is pretty much a relic and offers no advantages (it used to have some, years ago), only disdvantages.


Postgresql Chained Slaves and Slave Promotion

You should be able to use recovery_target_timeline='latest' in your recovery.conf, as noted in the recovery target settings documentation

If you are on PostgreSQL 9.1 or 9.2 and using streaming replication, you'll need to have a WAL archive set up as well, if you want to have this continue automatically after failover, or as @amacvar mentioned, copy the necessary WAL segments over so that the replicas can continue streaming along the newer timelines.

In 9.3 and later, this happens without needing an additional WAL archive, because the timeline switch is now included in the replication stream. Michael Paquier details this behavior in his blog post timeline switch of slave node without archives


MYSQL Optimize tables on Innodb in production environment advice

You can optimize a table without interrupting service by using pt-online-schema-change:

$ pt-online-schema-change --alter="ENGINE=InnoDB" --execute D=sakila,t=actor

Also, MySQL 5.6.17 supports OPTIMIZE as an online DDL change, allowing concurrent DML.


Alright, so the actual problem was not related to the accented or special characters. The issue was with this certain field, namely "Cód. Requerimiento". That dot (.) was the problem: removed it and everything is awesome now :)


SQL Server : Transactional Replication Computed Column

You can do this, but keep in mind that your new column will be wiped out if the subscription is ever reinitialized. ie. The DBA at the publisher alters the publication to add a table, corruption, maintenance, etc. and the snapshot needs to be reapplied, any changes you have made to the replicated schema are dropped and recreated from the publication. So any changes you have made will be dropped as well.


`CHANGE_TRACKING = AUTO` fails to populate full-text index

You said that the SQL Full-text Filter Daemon Launcher - FDHost is restarting. Do you have any error in the Event Viewer that would suggest it doesn't have the appropriate rights to run?

You could check the account and password and check that the permissions are sufficient on the computer it runs.

You should check that the named pipes are enabled.

For example, the documentation states that :

  If named pipes functionality has been disabled on the local computer, or if SQL Server has been configured to use a named pipe other than the default named pipe, the SQL Full-text Filter Daemon Launcher service might not start.

It also says that you can have problems if another instance of the same named pipe is already running.

If you can provide additional error logs, maybe we could help you futher.

I read a post here that says that this error got resolved once the SQL Service, Agent Service and Full Text Service all run on the same account. Maybe it's something worth trying.

You can try to query sys.dm_fts_fdhosts or another of the DMV views related to fulltext indexes. Maybe you can find additionnal information on what's going on there.


How far apart (ping time) are the two cities?  80ms is what we experience going across the US.  It is not bad.

Writing to both heads of Master-Master is possible, but has lots of pain points.

NDB Cluster allows hot-hot, but (as you say) requires some conversion.

So, back to what I see as the only viable solution:  A single writable master, plus any number of slaves.

One thing that can make a remote master painful is if the user's "unit" of action translates into many SQL statements.  That can/should be solved by
(1) Rethink the code to use fewer statements
(2) Use a Stored Procedure to encapsulate as many of the SQL statements as possible, then deploy that on the remote Master.

Reads (other than "critical reads") can/should go to a slave, behind a load balancer.  And some mechanism should ensure that reads are usually "local".


Does the order of columns in a columnstore index matter?

No, order does not matter. Each column is considered individually.
From the SQL Server team (emphasis added):

Typically you will want to put all the columns in your table into the columnstore index.  It does not matter what order you list the columns because a columnstore index does not have a key like a B-tree index does.  Internally, the data will be re-ordered automatically to get the best compression.


Most likely a default value or trigger are not suitable solutions. The values inserted automatically are snapshots and quickly invalidated by subsequent writes to the table.
You should use a VIEW or a "generated column".
Example for "generated column"
All you need is a function that takes the table type as parameter:
CREATE OR REPLACE FUNCTION sc (person)
  RETURNS bigint AS
$func$
SELECT count(*) FROM person WHERE name = $1.name
$func$ LANGUAGE sql STABLE;

Now, you get the current count with
SELECT *, p.sc
FROM   person p
WHERE  name = 'Frank Josephson';

Details in the related answer on SO:

Store common query as column?


AlwaysON Availability group query very slow

Answering my own question that might be helpful to future visitors ..

Aaron's comment made me thinking if we dump the data into temp table would it speed up ?

Yes it indeed speed up as due to large number of Availablity groups 53 (I know thats a lot and we have plans to offload them soon), sys.availability_groups and sys.availability_replicas selects were taking considerably long time.

Below tsql runs in 1 min and 03 secs :-)

set transaction isolation level read uncommitted
set statistics time, io on

--- get data into temp tables

select * into #availability_groups from sys.availability_groups
select * into #availability_replicas from sys.availability_replicas

SELECT
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM #availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN #availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name
    set statistics time, io off


data compression does not show when scripting index on ssms

Make sure your scripting options are set correctly for Data Compression


Amazon Athena database -- is there a way to generate an Data Schema for Amazon ML in an automated way

FROM AWS Support-Team:
There are 3 ways of obtaining database schemas through AWS services that I have heard so far.  The first two are using Lake Formation, and the last is with glue:

In the Lake Formation console window select Tables, click on the desired table, and hit the properties button, this will show a json schema as part of the information ("StorageDescriptor")
If the Lake Formation was setup using database blueprints, the blueprint will contain the schema, it is available from the lake formation console under blueprints
AWS glue data catalog – once a database is crawled by glue, it’s table schemas are available under the “View Properties” after clicking on the table, similar to 1)


How to add indexing for a search based on multiple columns in MySQL table

If you need to increase performance you can do the following:

Add FULLTEXT index to the columns name, email and phone

ALTER TABLE Employee ADD FULLTEXT (name), ADD FULLTEXT (email), ADD FULLTEXT (phone)

Dont use select * ( no caching )

select name, email, phone
, (
  10 * (MATCH (name) AGAINST ("Jon*" IN BOOLEAN MODE))
+  8 * (MATCH (email) AGAINST ("Jon*" IN BOOLEAN MODE))
+  2 * (MATCH (phone) AGAINST ("Jon*" IN BOOLEAN MODE))
) as points
from Employee HAVING points > 1
order by points DESC LIMIT 10

In this solution you get an result in a better order of your results.
If your keyword match with name, it is more helpfull to show than a match with phone.

You can change your my.cnf and add: ft_min_word_len = 3to index words up to 3 chars.


Automate DBs restore through TSQL by given folders

I would suggest to use Restore Gene written by Paul Brewer.

There is a T-SQL [sp_RestoreGene] and PowerShell version [ps_RestoreGene] of it and you can use it / customize it as per your needs.

SQLServerCentral.com has an excellent article by the author on how to configure and describes the working of it as well - Restore Gene : Automating SQL Server Database Restores.


Why would a query work slower when a where clause is added?

Edit - The below is assuming that the number of rows that would be returned by the query (if the limit wasn't present) exceeds the limit. (as in ... it regularly would return 5000 rows, but the limit forces it to return 1000)

Any time you have a limit on the number of rows returned by a query, you should not expect the TIMING on that query to have any sort of relevance to performance.

For example, if you take a simple query as such:

SELECT * FROM table_with_1m_rows;

It will take a while to process because it has to fetch all the rows via a sequential scan..

If I adjust it to:

SELECT TOP 1000 * FROM table_with_1m_rows;

It will return relatively quickly because, while it still does a sequential scan, it can STOP after it gets past 1000 rows.

If I then adjust it to:

SELECT TOP 1000 * FROM table_with_1m_rows WHERE col1 > 100;

It will take LONGER than the previous query because, while it still does a sequential scan, it will most likely have to scan more than 1000 rows before it has 1000 rows to return with.

All of the above holds true whether the DB needs to use a sequential scan or an index scan..

If you truly want to troubleshoot performance of the query, you need to remove the TOP 1000 and then view your query plan and see where the performance hit is...  (in this case, most likely an index that would be useful is missing)


Virtual Column - Using it in view (Oracle)

I don't think it's the size of the column (btw: don't "presume", test).

My bet is that the function gets called over and over again for each row from the result. So for each row in the result you make two(!) selects on the person table.

Edit: I was wrong with that above statement. The function for a virtual column is only called when the column it is based on is updated.

You can slightly improve that by only doing a single SELECT in your function:

select substr( p.name, instr( p.name, ' ', -1 ) + 1 ),
       regexp_replace(p.service_no, '[^0-9]*', '')
   into lname, snumber
from person p where p.id = id_;

But I would recommend to simply put that logic into the view and construct the email string inside the view:

select  distinct
        person.SERVICE_NO as Service_No,
        person.CNIC_NO as CNIC, person.NAME as NAME ,
        card.CPLC_SERIAL_NO as Card_Number,
        child_dc.NAME as Child_DC,
        root_dc.NAME as Root_DC, person.OU as OU,
        regexp_replace(p.service_no, '[^0-9]*', '')||substr( p.name, instr( p.name, ' ', -1 ) + 1 )||'@met.af' as email
from person_card
inner join person
 on person_card.PERSON_ID = person.ID
inner join card
 on person_card.CARD_ID = card.ID
    left outer join child_dc
 on person.CHILD_DC_ID = child_dc.ID
    left outer join root_dc
 on child_dc.ID = root_dc.ID;

That way no additional select is necessary and it should run just fine.


How to set up where to store data in SQL Server

You could also control it with:

detaching the database in SQL management studio
move the *.mdf and *.ldf file to the location you desire (C:/ or D:/)
reattaching the database by selecting the new path.


How to create multiple entries on index based on the fields of one row?


Suppose we have the following tables (and data):

-- test tables and test data
create table t_1 (
  type_ varchar2(64) unique
);

create table t_2 (
  id_ number
, type_ varchar2(64) references t_1(type_)
, likelihood_ number(7,2)
);

begin
  insert into t_1 (type_) values ('fantasy') ;
  insert into t_1 (type_) values ('romance') ;
  insert into t_1 (type_) values ('horror') ;
  insert into t_1 (type_) values ('mystery') ;
  insert into t_1 (type_) values ('academic') ;
  insert into t_2 (id_, type_, likelihood_) values (1,'fantasy', 0.9) ;
  insert into t_2 (id_, type_, likelihood_) values (1,'romance', 0.5) ;
  insert into t_2 (id_, type_, likelihood_) values (2,'horror', 0.2) ;
end;
/

For getting every "userid" mapped to every single type, you could use something like:

select
 *
from
( select distinct id_ from t_2 ) users ,
( select type_ from t_1 ) types
;

ID_  TYPE_
1    fantasy
1    romance
1    horror
1    mystery
1    academic
2    fantasy
2    romance
2    horror
2    mystery
2    academic

Expanding the previous query, we can select all the columns you need (number formatting omitted for clarity):

select
  users.id_
, types.type_
, coalesce (
    ( select
        likelihood_
      from t_2
      where t_2.id_ = users.id_ and t_2.type_ = types.type_
    ) , 0
   ) likelihood
from
( select distinct id_ from t_2 ) users ,
( select type_ from t_1 ) types
;

-- output
ID_  TYPE_     LIKELIHOOD
1    fantasy   0.9
1    romance   0.5
1    horror    0
1    mystery   0
1    academic  0
2    fantasy   0
2    romance   0
2    horror    0.2
2    mystery   0
2    academic  0

Tested w/ Oracle 12c and 11g. Dbfiddle here.


How to minimize the writes needed to update a counter field multiple times

MyISAM is the worst offender for updating individual rows on concurrency. First thing you want to do is use InnoDB to be able to execute UPDATEs in parallel. InnoDB may have lower insertion rate, but that can be solved by reducing its durability setting (innodb_flush_log_at_trx_commit = 2). That, combined with a large enough buffer pool, will make most of your updates on-memory only.

If that is still too slow for a single row, memcached is certainly one solution to buffer queries, and it is even integrated into MySQL 5.6. Operations in memcached are guaranteed to be done atomically, so on that side that wouldn't be a concurrency problem. However, reading, adding plus 1 on client code, and updating the result is not guaranteed to be atomic, so you should control concurrency on application side (syncronization/locking)- Java has very good support for that, PHP doesn't, although it can be done. That can be too much overhead if the cache server is over the network.

One trick to avoid concurrency problems (works for DB tables and any other system) is to do partitioning: instead of having one counter, having several per value to prevent blocking problems on write. When calculating the final number, just add up the different rows into one.


SQL Select that inserts a new row based on a delimiter

I borrowed this split string function from this answer on StackOverflow. Due your data is enclosed using | you must select only those values where data <> '' and finally add the delimiter again using CONCAT function.

create function dbo.splitString(@input Varchar(max), @Splitter VarChar(99)) returns table as
Return
    SELECT Split.a.value('.', 'VARCHAR(max)') AS Data
    FROM (SELECT CAST ('<M>' + REPLACE(@input, @Splitter, '</M><M>') + '</M>' AS XML) AS Data) AS A
    CROSS APPLY Data.nodes ('/M') AS Split(a);
GO

Select id, concat('|', data, '|') value
from mytable x
cross apply dbo.splitString(x.value,'|') Y
where data <> '';
GO

id | value
-: | :------
 1 | |856|
 1 | |421|
 2 | |123|
 2 | |795|
 2 | |7125|
 3 | |96412|
 3 | |85251|
 3 | |2245|
 4 | |4845|
 4 | |88422|
 4 | |9155|
 4 | |15154|
 5 | |1165|
 5 | |98742|

dbfiddle here


A trigger can only ever call one tigger function, so no to item 1.

The preferable form is item 2. IMO. You can put as many SQL statements into a single plpgsql function as you want.

Item 3. is possible, too. Well, not exactly the same trigger, the name would have to be different. Triggers on the same event fire in alphabetical order, btw. But I see no gain in two separate functions. Just more code and overhead and two function invocations, which is more expensive.

2. is the undisputed victor.


Recover a MySQL InnoDB from ibdata1 that won't repair using innodb_force_recovery


Automate backup using Windows Task Scheduler

I recommend using Ola Hallengren's backup solution.

The 6th question down on Ola's FAQ page walks you through automating the stored procedures with batch files. The steps given in the FAQ can be used for any stored procedure not just his. Below is the example provided from the FAQ site. You can modify that to run your custom stored procedure.

sqlcmd -E -S .\SQLEXPRESS -d master -Q "EXECUTE dbo.DatabaseBackup @Databases = 'USER_DATABASES', @Directory = N'C:\Backup', @BackupType = 'FULL'" -b -o C:\Log\DatabaseBackup.txt


SQL show only the oldest date with same number

To sum up, there are three major issues in your query:

As noted by ypercubeᵀᴹ, the order of the clauses is wrong in one place: the WHERE clause should go after the FROM clause and before the GROUP BY clause:

SELECT
  ...
FROM
  ...
WHERE
  ...
GROUP BY
  ...

As has already been addressed by Rajesh Ranjan, the GROUP BY clause must include all non-aggregated columns of your SELECT list, so it should look like this:

GROUP BY
  DIVISION,
  DEPT_CODE,
  NO

Finally, if your statement happens to be part of a PL/SQL block, you must end it with a semicolon:

SELECT
  DIVISION,
  DEPT_CODE,
  NO,
  min(START_DATE),
  row_number() over (order by
    DIVISION,
    DEPT_CODE
  ) as "NEW_NO"
FROM INFO
WHERE (TITLE IS NULL
  OR (upper(TITLE) NOT LIKE '%RESEARCH%'
  AND upper(TITLE) NOT LIKE '%POSTDOCTORAL FELLOW%'
  AND upper(TITLE) NOT LIKE '%PROJECT%'
  )
)
GROUP BY
  DIVISION,
  DEPT_CODE,
  NO;

Note that resolving all these issues does not guarantee you will get the expected result. Addressing the above points should merely let you run your query without errors. If it shows incorrect results, that will be a separate issue requiring a separate question.


Redesign the storage of large amounts of sensor data

Since (a) the information you are working with appears to be, in a of itself, a very valuable organizational resource, and (b) the volume of data will be considerable, I would decidedly (c) build a relational database on one of the major SQL platforms.

That of course —from a very general perspective— requires three essential  factors:

A clearly defined conceptual schema, in which one has to identify and mark out with precision the prototypes of things, i.e., the entity types (including their properties and interrelationships) of relevance in the business environment you are working with (e.g., the Towers and Sensors you mention).

As you know, this point entails establishing continuous and productive communication with business experts.
A logical layout that reflects the conceptual level with accuracy, by means of tables (i.e., mathematical relations) holding well-delimited columns with appropriate column names and types (i.e., relation attributes) and all the corresponding constraints to ensure that the data complies with all the rules determined at the previous tier.

Therefore, it is here where the vast power of the relational model comes into play (though its advantages have positive repercussions at other levels of abstraction).
A physical arrangement that, e.g., boosts the execution speed of the —dynamic— logical data manipulation operations and guarantees the logical constraints.

Since the relational model offers physical data independence, a database management system (DBMS for brevity) can provide any kind of structure at this level, not exclusively indexes, to support the logical definitions. In the case of the leading SQL platforms, yes, this commonly implies, precisely, setting up an indexing strategy based on the database-specific query tendencies, and you brought up very interesting considerations with respect to some possible configurations but, without knowing the particular informational necessities with exactitude, offering specific advices in this regard would not be suitable.

Other elements that deserve evaluation are, e.g., upgrading network infrastructure to increase bandwidth, enabling proper server configurations (hardware- and software-wise), etc. And, if, and only if, a practitioner is qualified enough, he or she could even modify the source code of the DBMS of choice (more feasible in open source environments, naturally).

In this way, the following aspects that you highlight

  Manage and retrieve tower/sensor configurations to make sense of data analysis.
  Data visualization by sensor or time intervals for meteorological observations.
  Provide customers with reliable and persistent data resources/data sets to compare model and sensor performance (may require some post-processing to deliver in the format required?).

would be well-addressed, because you would easily be able to declare queries to, e.g., obtain information in very meaningful forms. For instance, you can get data associated with

the Sensor identified by SensorNumber 1750, installed at the Tower identified by TowerNumber 31, between the Date 1 June 2017 and the Date 27 June 2017.

Furthermore, since (1) the data in a relational database is logically managed in terms of sets with the aid of operations based on the relational algebra, and (2) the different SQL engines are physically optimized (some more than the others) for set processing, you can, e.g.,

compare set a with set b;
join set c with set d;
obtain subset f through a restriction on set e;
produce n subsets from n set intersections;
project n attributes from set f
retrieve information from set z that is the result of a union of set x with set y;
and so on.

The data manipulation possibilities are in fact huge —demonstrating the unmatched versatility of the relational paradigm— since you can work not only with base tables (the ones declared with CREATE TABLE … ( … ); statements) but also with derived ones (the ones expressed via SELECT …; operations, sometimes fixed as VIEWs). In other words, you can (i) express new data structures based on (ii) prior ones operating on (iii) the single underlying relational construct, i.e., the mathematical relation.

Evidently, the arrangement of the base tables and columns of a relational database can evolve, and (a) new base tables or columns may be incorporated into it when (b) keeping track of new entity types or entity type properties is deemed worthwhile in the pertinent business context. In other words, neither the initial structure nor the opening constraints of a relational database are expected to be static or immutable. Besides, a database that is appropriately organized from the very beginning tends to be much easier to modify when new informational requirements arise.

In agreement with the considerations above, the logical format of the applicable sets must be produced declaratively, at the database logical level. The graphical or presentational formatting of the sets (e.g., the colouring or font faces utilized) must in turn be processed by way of the code of one or more application programs (yes, mostly in a procedural manner, perhaps with the assistance of an object-oriented framework, HTML, etc.), in order to make the access and presentation of such sets user-friendly. Certainly, you could as well use reporting software that connects with your database.

The modeling of a database of relevance

Given that you will be working with Sensor data (which, among other features, typically involves information in the shape of time series), you may find of help multiple database design and overall administration principles contained in the two exceptional answers, by @PerformanceDBA, to the questions entitled:

“Opinions on sensor / reading / alert database design”, and
“Historical / auditable database”.

The Relational, Flat File and NoSQL approaches

The relational model by Dr. Edgar Frank Codd, although published in 1970, remains genuinely the most modern and elegant method (based on logic and set theory) to cope with the problem of data management. The distinct SQL DBMSs are, in turn, the most popular approximations (that, although not fully compliant, are nevertheless really powerful) to the systems proposed in the relational theory, and some of them have been heavily optimized (e.g., regarding their physical-level mechanisms) for even decades now. In addition, the main SQL platforms of course can (and will be able to) work with the most up-to-date storage (e.g., hard drives) and processing (e.g., CPUs) technologies quite efficiently.

When built on a powerful DBMS, a relational database that is properly designed at the conceptual, logical and physical levels would decidedly become a self-contained, self-descriptive and self-protective asset that (1) is trustworthy and (2) offers a fast response, two aspects that, as you know, are of great significance.

Flat files

Therefore, the claim that follows

  I was told that the rationale for using flat files is that they believed a relational database would take too much overhead.

is easily discarded, because the flat file approach would be:

pre-scientific;
far from optimal for large volumes of data;
too cumbersome;
application program-dependent (and you would have to implement by hand most of the features that proper DBMSs offer natively);
its performance will easily be undermined;
etc.

Whereas the —much more convenient— relational fashion, to say the least:

would offer large scalability (it is physical-level-independent, so you could enhance the underlying physical mechanisms as needed);
would bring a simple style to manipulate the data (via abstract operations) and
could work with multiple application programs simultaneously (e.g., one or more mobile apps, and/or one or more web apps, and/or one or more desktop apps, etc.).

If, however, you opt for utilizing flat files, you should evaluate the employment of a robust utility like Awk that, although not a DBMS (and was not designed as such), supplies handy resources to deal with files, records, fields, etc. See The GNU Awk User’s Guide for more information on this subject.

NoSQL

“Unstructured data” and associated terms

As per their propaganda, the initial justification for the usage of NoSQL DBMSs is that they are meant to be used in business domains that involve handling “unstructured data”, so that calls for the question:

What is the expression “unstructured data” supposed to mean?

In that respect, it must be said that data, by its very nature, is structured; if it had no structure then it would be something meaningless, consequently such a thing (i) could not be considered data and (ii) would not be worth administering. Hence, “unstructured data” is a contradictory and unfortunate expression.

Other phrase of those contexts is “semi-structured data”. That phrase suggests that there exists data that is structured “in part” or “in half” so, in accordance with the previous paragraph, only the “part” or “half” that is structured can be actual data, the remaining “part” or “half” is merely a shapeless thing because it lacks structure, and cannot be referred to as data.

Yet another, alas, typical term that is found in NoSQL marketing is “polymorphic data”. If said term signifies something like “data that has many different forms”, then it is in fact ordinary data, it comes about in many distinct forms as always. And since it has many different forms, then it presents many distinct structures, so there is nothing special about this “kind” of data.

Needless to say, data and data structures have always been susceptible to changes, then there is nothing unusual in this regard either.

Data volume growth

Evidently, the volumes of information managed by means of computerized systems have definitely grown over the years —and will keep growing exponentially as time passes, because new systems are being built every day—, but that is a fact that does not have to do with the structure of the information per se.

Lack of a rounded theoretical foundation

A critical limitation of NoSQL systems (there are of different classes, e.g., document- and graph- based) is that none of the current products —although heavily marketed and labeled as “modern”— possesses a sound theoretical basis (if at all) that supports each and everyone of the three most important elements of a proper DBMS, i.e., tools for data (a) definition, (b) manipulation, and (c) constriction. Thus, the NoSQL approach actually suggests a regression to an ancient era in which the handling of the data was performed in an ad hoc and unsound course of action, with all the needless complexity it entails.

Nowadays, graph systems are included within the “NoSQL” spectrum. These software products invite to manage data by virtue of operations on two distinct structures: nodes and relationships —which, once again, is in conflict with the term “unstructured data”—, and they stand out in the “NoSQL” group because they do have a mathematical foundation. However, graph products are rather similar to network platforms, which are deemed obsolete since tens of years ago (an obvious drawback is that, as suggested above, they need two structures for data representation, while a relational DBMS —as per the information principle— needs only one).

Even if the creation of the different NoSQL systems is chronologically newer in comparison with the origins of the majority of the SQL DBMSs, most of the concepts onto which the NoSQL products are based are, in effect, primitive.

A NoSQL program should be employed, mostly, in scenarios where, e.g.,

the IT personnel lacks the technical skills required to determine (or to determine opportunely) the structure of the data of interest —e.g., due to its complexity—; and/or
the organization cannot afford suitable education and training for the present staff, or cannot hire new staff that possesses the required education and training; and/or
when the integrity and consistency of the data is not particularly important; and/or
when blending the concerning data with that of mission critical systems that demand high precision is not expected.

But, even if the structure of the data at issue is not defined before the creation of the concerning systems, one ore more people will necessarily have to

discover the aforesaid structure,
discard all the surrounding “interference” and
collect and link the proper data

after the database and app(s) have entered the production stage in order to be able to get the most out of all the resources invested in a project, then data structure delineation is a task that cannot be bypassed, it has to be done sooner or later.

So, while going the NoSQL way is a possibility, all the factors previously mentioned should definitely be taken into account.

The most robust method

In contrast, approaching the informational requirements of a business environment in a relational manner —i.e., with a general paradigm behind— offers the possibilities of (1) managing the data in its natural structure from the beginning —which eases integration with other data sources— and also of (2) producing new trustworthy structures by dint of the manipulation of a single instrument —as explained in previous sections— that has a robust scientific basis.

According to your description of the scenario in question, you have already identified a particular structure in terms of the relevant organizational needs, so I suggest requesting that the business domain experts validate it. Successively, I recommend taking advantage of (i) the constructs —the relation, the constraints and the operations— provided by the relational model to handle said structure and the respective data, and of (ii) your SQL DBMS of choice which will very likely offer very efficient physical tools that can hold up the present demands and will supply future scalability.


How to optimize a delete query with a subselect?

Use Multiple-Table Syntax of DELETE statement.

DELETE s.*, a.*
FROM statements s, agreements a
WHERE s.agreement_id = a.id
  AND a.created < CURDATE() - INTERVAL 6 MONTH


How to find values with multiple consecutive upper case characters

This question is far more complicated than it appears to be on the surface (hence the longer-than-most-would-expect answer). If the strings being searched were codes (postal codes, ISO country codes, ISO state codes, SKUs, etc) or something where the characters used were a limited subset of all possible letters of all languages, then this would be fairly straight-forward. But when dealing with people's names, then no such luck.

                        All of the following example code and test cases can be found on Pastebin ( Searchingfor Case-Sensitive patterns in SQL Server ). The SQL posted on Pastebin includes additional test cases and additional examples related to various points mentioned below.

Very simplistically you can do the following, which does work with the sample data. I added two test cases to help determine whether or not this (or any) method works as it is data that should not match.

SET NOCOUNT ON;
DECLARE @SampleData TABLE
(
  Name NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
);
INSERT INTO @SampleData (Name) VALUES (N'Mr JOHN Smith');
INSERT INTO @SampleData (Name) VALUES (N'MR John Smith');
INSERT INTO @SampleData (Name) VALUES (N'Mr John SMITH');
INSERT INTO @SampleData (Name) VALUES (N'Mr JOhn Smith');
INSERT INTO @SampleData (Name) VALUES (N'mr john smith'); -- all lower-case
INSERT INTO @SampleData (Name) VALUES (N'Mr John Smith'); -- proper-case

SELECT *
FROM @SampleData
WHERE Name COLLATE Latin1_General_100_CS_AS
                 LIKE N'%[ABCDEFGHIJKLMNOPQRSTUVWXYZ][ABCDEFGHIJKLMNOPQRSTUVWXYZ]%';

Please note:

I am not using a collation that starts with SQL_ as those have been deprecated (or perhaps more accurately: obsoleted) since SQL Server 2000 was released.
I am using a collation in the 100 series as that is the most recent, and should be available starting in SQL Server 2008. Not all collations have a 100 series, but it is best to use the most recent one, which might be 90.
I am not using a binary collation (i.e. ending in _BIN2 or even _BIN though the _BIN collations have been deprecated since SQL Server 2005 was released, so only use _BIN2 if needing a binary collation). Binary collations are not "case sensitive"; they are "byte sensitive" (_BIN) / "code point sensitive" (_BIN2) and those are very different concepts. Binary collations only appear to be case sensitive when working with (or testing) US-English-only characters. Any characters with accents can potentially be handled incorrectly when using binary collations. For example:

PRINT N'U' + NCHAR(0x0308) +
   N' <-- U + NCHAR(0x0308) -- combining diaeresis [1 "character" from 2 code points / 4 bytes]';
   -- Ü
PRINT NCHAR(220) + N' <-- NCHAR(220) [1 "character" from 1 code point / 2 bytes]'; -- Ü
PRINT NCHAR(252) + N' <-- NCHAR(252) [1 "character" from 1 code point / 2 bytes]'; -- ü
PRINT '';

IF (NCHAR(252) = N'U'+NCHAR(0x0308) COLLATE Latin1_General_100_CI_AS)
        PRINT 'Same!' ELSE PRINT 'Nope.'; -- case INsensitive
IF (NCHAR(252) = N'U'+NCHAR(0x0308) COLLATE Latin1_General_100_CS_AS)
        PRINT 'Same!' ELSE PRINT 'Nope.'; -- case SENSITIVE
IF (NCHAR(220) = N'U'+NCHAR(0x0308) COLLATE Latin1_General_100_CS_AS)
        PRINT 'Same!' ELSE PRINT 'Nope.'; -- case SENSITIVE
IF (NCHAR(220) = N'U'+NCHAR(0x0308) COLLATE Latin1_General_100_BIN2)
        PRINT 'Same!' ELSE PRINT 'Nope.'; -- Binary

For a more in-depth look into this topic, please see my blog post: No, Binary Collations are not Case-Sensitive.
Instead of using a single character range (i.e. the [A-Z] that works in LIKE and PATINDEX) I added each of the 26 letters. This is required in order for the preferred _CS (i.e. case sensitive) collations to work properly. If you specify a range of characters via the [A-Z] syntax, then it will appear to not respect the case sensitive collation. That, however, is a false interpretation of the behavior. Case sensitive comparisons can be tricky since they, by design, work differently between equality comparisons (e.g. WHERE N'zbz' LIKE N'%B%' or WHERE N'b' = N'B') and range comparisons (e.g. WHERE N'zbz' LIKE N'%[A-C]%'). Range comparisons are more like sorting. And, case sensitive sorting does not handle all of one case before the other case. Think in terms of how dictionaries are ordered (and in fact, the Microsoft documentation even uses the term "dictionary ordering" to describe non-binary collations): they don't place all words starting with A-Z prior to words starting with a-z. They group upper and lower case of each letter together: A,a,B,b,C,c, and so on, not A,B,C,a,b,c. Hence, assuming that upper-case are first (and which one comes first depends on if you are using a SQL_ or non-SQL_ collation), a range of [A-C] equates to [AaBbC], and if lower-case are first, that same range equates to [AbBcC]. In both cases, the lower-case "b" is validly in that range.

However, this approach mainly works if the data you are searching for is guaranteed to only ever have the 26 US-English characters in it, and never ever any other characters found in most other languages, especially those with accents / diacritical marks. Given that your data contains names, you cannot guarantee that, even if working in the US since people live here who came from somewhere else (or an ancestor did), or sign up for services hosted here even though they live anywhere in the world.

For example, add the following two test cases to the example code above:

INSERT INTO @SampleData (Name) VALUES (N'Mr ÜLala Jones');
INSERT INTO @SampleData (Name) VALUES (N'Mr üLala Jones');

Running the code again will not yield new results. The second line added uses a lower-case ü so it shouldn't match anyway. But the first line uses an upper-case Ü so it should match, but can't unless we either make the collation accent-insensitive (i.e. Latin1_General_100_CS_AI) or add the Ü character to both character ranges of the LIKE clause (e.g. [ABCDEFGHIJKLMNOPQRSTUVWXYZÜ]).

Making the comparison accent-insensitive might be an option when looking for any two capital letters next to each other, but not when comparing actual words. And adding the extra letters (the ones with accents) only works when using VARCHAR (i.e. Extended ASCII) data. If that is the case then this approach could be made to work IF you added all of the additional capital letters available in the code page specified by the Collation. To find that, you can do the following:

SELECT COLLATIONPROPERTY('Latin1_General_100_CI_AS', 'CodePage');
-- 1252

According to the Wikipedia page for Code Page 1252, there are 32 additional capitals, which are (I made it a heading so that the characters would be easier to see):

Š Ž ÀÁÂÃÄÅ Æ Ç ÈÉÊË ÌÍÎÏ Ð Ñ ÒÓÔÕÖ Ø ÙÚÛÜ Ý Þ

The result would be a LIKE clause single-character specification of:

[ABCDEFGHIJKLMNOPQRSTUVWXYZŠŽÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞ]

But that particular set of characters only works with Code Page 1252. If your collation uses another Code Page then that set of extra characters would most likely be different, even if there is some overlap.

On the other hand, if you are using Unicode / NVARCHAR data, then even adding those extra characters will not work. Not only can Unicode represent all accented characters (unlike any individual Code Page), it also allows for constructing accented characters by starting with a regular, non-accented character, such as "U" and then adding one or more accents to it. These accents are called "Combining Characters" since they only work in combination with certain base characters (for a more detailed look into Combining Characters, please see my answer to How To Strip Hebrew Accent Marks ). An example of this was shown in Note #3 above (e.g. PRINT N'U' + NCHAR(0x0308); -- Ü ).

If we now add the following two test cases to the example code above:

INSERT INTO @SampleData (Name) VALUES (N'Mr U' + NCHAR(0x0308) + N'Lala Jonez');
INSERT INTO @SampleData (Name) VALUES (N'Mr U' + NCHAR(0x0308) + NCHAR(0x0309) +
                                     NCHAR(0x0308) + N'Lala Jonezzz');

and re-run the test, those new entries will not show up, at least not when using the original LIKE definition that specified just the 26 letters of the US-English alphabet. That first new entry would show up if we added the Ü character since U + that combining character equate to the same thing. So, is it feasible to add the remaining characters like I showed above in the VARCHAR example? That was 32 extra characters. How many extra characters for NVARCHAR? About 1200 ;-). Um no, not feasible.

And if you did add in all 1200 additional capital letters, would that work? Nope, because there can still be more capitals that simply don't have a "pre-composed" equivalent. For example, that second new entry won't show up since there is no equivalent character to put into the LIKE definition. The only way to make this work would be to not only add in all 1200 additional capitals, but also use an accent-insensitive collation. This still doesn't seem very feasible. And it still would only work in limited scenarios such as this one (looking for any capitals, not specific ones).

Unfortunately, T-SQL is just not equipped to deal with this.

But fortunately, there is a way of accomplishing this: Regular Expressions (commonly referred to as just RegEx). RegEx allows for specifying "classes" of characters (e.g. \s = "any white-space character"), and one class -- \p{property} -- will match characters based on certain properties of Unicode characters. The following two class specifications will allow us to get any capital letter, even if it doesn't exist today:

\p{Lu} = An upper-case letter that has a lower-case equivalent
\p{M} = A combining mark

We can use those to come up with the following RegEx pattern:

(?:\p{Lu}\p{M}*){2}

To translate this for those who aren't familiar with RegEx syntax:

The ( and ) group everything inside to be a single pattern that can be repeated or referenced later
The ?: tells the RegEx engine to not save any matches for that grouping. We won't be using whatever matches so it is more efficient to not store it in memory.
The * indicates that there should be 0 or more matches for the preceding item (i.e. the \p{M}). This will match any number of diacritical marks, or none.
\p{Lu}\p{M}* altogether says to match any single upper-case letter by itself, or that is followed by any number of diacritical marks. This will match, for example: U, Ü, U + NCHAR(0x0308), and so on.
The {2} indicates that a match will consist of exactly 2 occurrences of the preceding item (i.e. the (?:\p{Lu}\p{M}*)). Without this part it would only match a single upper-case letter, not 2. And this is why we needed the parenthesis: to group the upper-case letter and the zero-or-more combining marks together to be a sub-pattern that could be repeated.

The only part left to take care of is the fact that SQL Server does not have any built-in support for Regular Expressions. For this we turn to SQLCLR which allows us to use the RegEx functionality that exists in .NET. If you do not already have SQLCLR RegEx functions, there are many code examples around the Interwebs (just be careful since a few are pretty good, but many are pretty bad), or you can download the Free version of SQL#, a pre-compiled library of SQLCLR functions (that I created) which contains several RegEx functions, including the one used in the following example code:

SELECT *
FROM @SampleData
WHERE SQL#.RegEx_IsMatch4k(Name, N'(?:\p{Lu}\p{M}*){2}', 1, '') = 1;

Running against the test data again will bring back all of the expected entries, including the final one with the 3 diacritical marks that isn't even a character in any language (at least, not yet). Using RegEx with this pattern will just work, always, for all known languages (well, languages that have capitals since some do not).


A very basic star-schema for your situation might look like this:

The T-SQL statements to create the dimension tables for this schema look like:
CREATE TABLE dbo.problems
(
    problem_id int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT problems_pk
        PRIMARY KEY
        CLUSTERED
    , problem_description nvarchar(400) NOT NULL
);

CREATE TABLE dbo.reasons
(
    reason_id int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT reasons_pk
        PRIMARY KEY
        CLUSTERED
    , problem_id int NOT NULL
        CONSTRAINT reasons_problem_fk
        FOREIGN KEY
        REFERENCES dbo.problems(problem_id)
    , reason_text nvarchar(500) NOT NULL
);

CREATE TABLE dbo.fixes
(
    fix_id int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT fixes_pk
        PRIMARY KEY
        CLUSTERED
    , reason_id int NOT NULL
        CONSTRAINT fixes_reason_fk
        FOREIGN KEY
        REFERENCES dbo.reasons(reason_id)
    , fix_text nvarchar(525) NOT NULL
);

CREATE TABLE dbo.dates
(
    date_id int NOT NULL
        IDENTITY(1,1)
        CONSTRAINT dates_pk
        PRIMARY KEY
        CLUSTERED
    , starting_date datetime NOT NULL
    , ending_date datetime NOT NULL
    , CONSTRAINT dates_dates
        CHECK (ending_date > starting_date)
);

The fact table, which you seem to have left out of your question, and I've named incidents, looks like:
CREATE TABLE dbo.incidents
(
    incident_id int NOT NULL
        CONSTRAINT incidents_pk
        PRIMARY KEY
        CLUSTERED
    , incident_date_id int NOT NULL
        CONSTRAINT incidents_incident_date
        FOREIGN KEY
        REFERENCES dbo.dates (date_id)
    , problem_id int NOT NULL
        CONSTRAINT incidents_problem_fk
        FOREIGN KEY
        REFERENCES dbo.problems(problem_id)
    , fix_id int NULL
        CONSTRAINT incidents_fix_fk
        FOREIGN KEY
        REFERENCES dbo.fixes(fix_id)
    , fix_date_id int NOT NULL
        CONSTRAINT incidents_fix_date
        FOREIGN KEY
        REFERENCES dbo.dates (date_id)
);

incidents references the dimension tables such that you are storing the bare minimum of information in the fact table.  The fix_id column is referenced in the incidents table to indicate which of the possibly many fixes was accepted as the actual fix used in this incident.  Since each fix is related to only a single reason, you can infer the reason from the fix.  I've included a discrete column to indicate the problem in this table to enable incidents that don't yet have a fix.
You could add a reason_id foreign-key column to the incidents table if desired.  For instance, maybe you identify a reason prior to determining the fix.


Can I perform a transaction across two different MySQL databases on two different servers in different tables

This is a duplicate question. It seems like this is a possibility with InnoDB as your engine, but other than that, unfortunately, it does not seem like there is a way. This also seems dependent on the version of MySQL, like 5.6 and above, the thread I linked to points to a documentation here.

A side note: just because this seems to a possibility, it may not be the best solution. It seems that it would be best to write the transaction, and use a script to run the transaction on both servers.


how to see multiple column values in new line

select deptid, replace(ename, ',', ',\n') from table;

Try to use this query and it will gives you your expected result.


Need to create combinations of Parents/Children/Dogs, but can't get past 2nd level of CTE

First off, I think you should consider rethinking your data model a little, and look into some normalization techniques before going ahead with your project.

But, using your model ( only slightly altering it ), you could accomplish what you're after using the following method.
Simply add nulls for the child / dog levels and use a simple cross apply query to create the Cartesian product you seem to be after. There's no need for recursive queries, since your data isn't defined recursively.
But, this will work :

declare @Ticket table
(
TID int
)

declare @EntityTypes table
(
 id int primary key,
 name varchar(20)
)

declare @TicketEntities table
(ID int IDENTITY(1,1) not null,
 TicketId int,
 TypeId INT,
 EntityName varchar(40)
)

Insert into @Ticket(TID) values(1)
Insert into @Ticket(TID) values(2)

insert into @EntityTypes (id, name) values (1, 'Parent');
insert into @EntityTypes (id, name) values (2, 'Child');
insert into @EntityTypes (id, name) values (3, 'Dog');

Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,1,'Peter')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,1,'Sally')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'Sam')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'Pete')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,'John')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,2,NULL)
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Matix')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Snowy')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,'Bliksen')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(1,3,NULL)

Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,1,'Parker')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,1,'Scarlett')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Brett')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Britney')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,'Bronwyn')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,2,NULL)
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Rover')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Sam')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,'Laika')
Insert into @TicketEntities(TicketId,TypeId,EntityName) values(2,3,NULL)

Select par.ticketId, par.EntityName as Parent, child.EntityName as Child, dogs.EntityName as Dog
from @TicketEntities as par
    CROSS JOIN @TicketEntities child
    CROSS JOIN @TicketEntities dogs
where
    par.TypeId in (select id from @EntityTypes where name = 'Parent') AND par.TicketId = 1
    AND child.TypeId in (select id from @EntityTypes where name = 'Child') AND child.TicketId = 1
    AND dogs.TypeId in (select id from @EntityTypes where name = 'Dog') AND dogs.TicketId = 1


What is the best way to store optional values in table?

Per your comment:

There will be around 300 inserts per second and around 200-250 updates
on this table (sometimes update which is also updating Option column
(for example add new value).

This leaves us with two options:

Store the optional columns in their own table(s) and create a view to stick everything together.  In this the PK of the optional table(s) will be the primary key of the main table, migrated through a FK constraint.
Just deal with the 1 bit penalty per column for the other rows.  Depends on how often the optional 63 columns are populated versus the other rows and what the actual differential in storage would be.

With respect to explicit columns vs VARCHAR(MAX), it is definitely worth defining/creating the columns, if for nothing else the sanity of your end users.  You can certainly group related columns together (those that are/will be NULL together) in their own tables if the data size is relatively large, or just deal with having one (potentially) wide table with very few records in it.
Do not use EAV.  There are plenty of SO questions and articles you can find outlining why and I won't repeat them here.

Ok so the option with VARCHAR(max) is the worst option ? Why new
partitioned table with only optional columns will be better solution ?

I'd say the worst option would still be EAV, but using a VARCHAR column to store multiple values violates 1st normal form (which exists for a very good reason), but from a practical standpoint here are the drawbacks:

Can't ensure the datatypes are consistent for the data stored
Trying to make sense of the data requires someone knows how the data is encoded, then they have to try and extract the data.  Things like JSON and XML can reduce that somewhat, but they have storage overhead and are still not as easy to work with as just writing a simple SELECT statement.

Relevant example from earlier in my career:
There was a need to store derived characteristics for each customer to be calculate a price.  These varied based on what price was being determined and there were over 500 characteristics total.  Someone "solved" the problem by concatenating all of the characteristics into one string and loaded that into a VARCHAR column.
This had the following disadvantages:

There had to be a huge amount of effort put into ensuring the resulting string was valid, complicating business rules and making changes difficult (a lot of testing since changing one thing could break another)
Getting the data parsed was intensive and required operations that couldn't be performed easily in the database.  So there was a whole other program that would pull the data out, parse the 500 some odd columns, then return it.  Slow, and the typical developer/user had no clue how anything worked.  And if you only wanted "Characteristic 26B" or whatever, you had to process every row, whether or not "26B" made any sense at all for that particular row.

Currently, I deal with a lot of aggregated data that is ridiculously wide (usually 100+ columns, most NULLable).  Given the type of workload (ad hoc queries selecting anywhere between 5-20% of the columns), I will usually create a table for each NULLable column or subject area, then combine everything into a single view.  SQL Server is great at join elimination, so we've been able to cut the amount of data read for a given query substantially. This results in faster performance and we don't have to rebuild the table every time a new data element is added (just create a new table, load the data, then alter the view).
If your situation was closer to that of a DWH (a lot of inserts and relatively few updates, a lot of ad-hoc queries), then Clustered Columnstore Index would be a good option, but that doesn't seem to be the case here.


sql query to check whether multiple entries exist

WITH cte AS ( SELECT Name, ID, COUNT(ID) OVER (PARTITION BY ID) cnt
              FROM source_table )
SELECT Name, ID
FROM cte
WHERE cnt > 1;

SELECT DISTINCT t1.*
FROM source_table t1
JOIN source_table t2 ON t1.id = t2.id
                    AND t1.Name != t2.Name


Microsoft's Bob Dorr wrote that trace flag 8048 is no longer required for SQL Server 2016 and newer:

  This means that trace flag -T8048 is no longer valid or needed on SQL Server 2016 because the design is such that SQL Server dynamically adjusts to CMemThread wait activities in order to optimize performance.

However, the behavior it fixes (CMEMTHREAD waits) is still relevent for 2014 and earlier versions.


xp_cmdshell called from procedure WITH EXECUTE AS OWNER

does this example show a serious security problem, or am I misunderstanding something?

You are misunderstanding what is actually going on here.

  the database owner is changed from sa to another low privileged login, then the user stored proc is no longer able to call xp_cmdshell.

That is true in this case, but that is merely due to how you did (or more accurately: did not) configure things (more on that in a moment).

  I thought that enabling xp_cmdshell was ok if care was taken to only grant execute xp_cmdshell permission carefully,

It is.

  but my example seems to show otherwise.

I would disagree with that. This is just a matter of not understanding the security mechanism, and this does happen to be a tricky case of it, so that is quite understandable.

A few things:

EXECUTE AS OWNER is not specifically the issue. You could have done EXECUTE AS N'dbo' to get the same effect.
The database being owned by a sysadmin is not specifically the issue. You could have done EXECUTE AS N'other' where other is the name of a User that is associated with a Login that has a User in [master] that has been granted execute on xp_cmdshell to get the same effect.
No, server level permissions are not being granted. To prove this, add the following two lines to your test stored procedure:

EXEC sp_configure N'allow updates', 1; RECONFIGURE;
SELECT * FROM sys.dm_exec_sessions;

Go ahead and change the db owner back to sa and execute the proc. You will get errors on the sp_configure and the RECONFIGURE, and you will only get 1 row back from sys.dm_exec_sessions, your session's row, because you do not have the VIEW SERVER STATE server level permission, something sa definitely has.

What you are experiencing is a function of two things:

You enabled the proxy account which lets non-sysadmins accomplish this if they have execute permission (not sure why you enabled this, unless you don't want to be using the SQL Server service account, which is a good idea, but doesn't work for sysadmins)
the DB was owned by a login that was associated with a user present in master that was granted permission to exec xp_cmdshell (this associated is derived from the EXECUTE AS combined with the DB ownership)

To prove this, exec the following (at the end of your current test code in the question, with dummyuser owning the DB):

REVERT;
USE [master];
CREATE USER [dummyuser] FOR LOGIN [dummyuser];
GO
GRANT EXEC ON xp_cmdshell TO [dummyuser];
USE [testdb];

Then do:

EXECUTE AS LOGIN = 'testuser';
EXEC dbo.testproc;

and you will now, instead of getting the permission denied error, either have success with xp_cmdshell, or you will get the following error if the proxy account is not configured:

  Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line YYYYY [Batch Start Line XXXXX]
  The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.

Even better would be to do the following, which removes the permission from dummyuser, creates a new low-privileged account, and grants that account the xp_cmdshell permission:

USE [master];
CREATE LOGIN [cmdshell] WITH PASSWORD = 'password', CHECK_POLICY=OFF;
CREATE USER [cmdshell] FOR LOGIN [cmdshell];
GRANT EXEC ON xp_cmdshell TO [cmdshell];

REVOKE EXEC ON xp_cmdshell FROM [dummyuser];

USE [testdb];
CREATE USER [cmdshell] FOR LOGIN [cmdshell];

And then, change the EXECUTE AS clause in the stored procedure to be WITH EXECUTE AS 'cmdshell'. Execute the proc again and you will either have success or the proxy account error if it isn't set up. In this case:

the database owner does not have the permission
you aren't tied in any way to a sysadmin login.

This works because the EXECUTE AS user is associated with a login that has a user in master that has been granted execute on xp_cmdshell.

ALSO, this behavior is most likely tied to the object (i.e. xp_cmdshell) truly existing in the mssqlsystemresource database. You can't create an object in [master], grant a user (the same being used as the EXECUTE AS user of the stored procedure, even if that user is dbo / OWNER and the database is owned by sa) EXEC on the user proc in [master], and have it work (not without enabling TRUSTWORTHY, which you shouldn't do). Not even if the user stored procedure in [master] is marked as a system stored procedure (I've tried).

Of course, ideally you would use Module Signing to take care of the permission instead of EXECUTE AS (either associate with sysadmin server role to use SQL Server service account, or associate with user in [master] that has been granted EXECUTE on xp_cmdshell to use proxy account, assuming that it has been configured).


Delete inactive users - how to prevent jobs and reports to stop working

The company I work for writes a free script called sp_Blitz that will warn you when Agent jobs are owned by users.

It'll warn you about a whole bunch of other stuff too, and is a pretty decent way to give your SQL Servers a free health check.


Adding an ID column to a join table (many-to-many) so that it can be targeted

Join tables normally have primary key which is composite : (saleperson_id, lead_id) in your case, each of these columns is also a foreign key to appropriate table. So there is no need in surrogate ID PK in simple cases.
However, you have tasks and person information (details) which are specific to relationship itself and should have FK to saleperson_has_lead (I'd get rid of "has" in table name, but it's outside of question scope). Thus, logically your model looks good to me.
There are some options on how to implement this model :
1. Add a surrogate PK (as you did), but don't forget you still need unique constraint on (saleperson_id, lead_id)
2. Don't add it, but refer to already existing composite PK in saleperson_has_lead table -(saleperson_id, lead_id).

Personally I prefer the first approach because it seems more clear.

Side note.
From what I see many of your entities (lead, saleperson,  saleperson_has_lead) storing information about people. You may want to think of adding concept of role to your model because "lead" and "saleperson" are really just roles that a particular person can play in your system.


Is it okay to use rsync on InnoDB database if the MySQL server is shutdown?

That really depends on what the directory structure underneath /home/mysql/mydb - if that's just the data files for one database, you'll need to extend your reach.

If you're going to use rsync (or take file-based backups in general), you'll need to make sure that your backup includes, at the very least:

Your innodb logfiles (ib_logfile*)
Your main innodb data file(s) (ibdata*)
All additional innodb data files (*.ibd) (if using innodb_file_per_table = 1)
All table definition files (*.frm) for your InnoDB tables

Obviously this only covers InnoDB - presumably you'll want to grab all of any other database files (MyISAM, Archive, CSV, etc.) as well.

MySQL's documentation on the topic also recommends issuing set global innodb_fast_shutdown = 0 before shutting down the server - this shouldn't be necessary, but it makes sense. That setting will force the server to flush your log files to your data files, and will speed up the recovery time significantly if you need to restore from backup.

(Strictly speaking, doing a slow shutdown means you should be able to get away without backing up your log files, but it's still a Good Idea, just in case it turns out that one day the server doesn't shut down cleanly for some reason)

I'm a big fan of Percona Xtrabackup for backups, as it generally makes sure that it copies all of the necessary files plus it can take the backups online, but there are definitely situations where rsync might be more suitable to your requirements.

Update: If you're using MySQL 5.6, you may want to check out InnoDB Transportable Tablespaces. This functionality is probably not going to be useful to you, and would require a bit of scripting if you have a lot of tables, but it can be really helpful if you use innodb_file_per_table and you're trying to avoid copying across all of the other InnoDB baggage.


Referencing specific columns using a variable range

As far as SQL Server is concerned, column names must be specified explicitly. There is no way around. As far as you are concerned, names can be parametrised, but that means you need to generate the entire statement dynamically.

Depending on how often you need to do this, there may be no need to use dynamic SQL. If this is a one-off job, you could just generate the assignment list using something like

SELECT
  x.prev_name + ' = ' + c.name + ','
FROM
  sys.columns AS c
  CROSS APPLY (SELECT CONCAT('q', STUFF(c.name, 1, 1, '') - 1)) AS c (prev_name)
WHERE
  c.object_id = OBJECT_ID('dbo.tablename')
  AND c.name BETWEEN 'q49' AND 'q79'
;

Run that in SSMS to get an output like this:

(No column name)
----------------
q48 = q49,
q49 = q50,
q50 = q51,
...
q78 = q79,

Copy the contents of the output column and paste it into the query window where you are writing your UPDATE statement, at the appropriate position. You will only need to make minimal syntactical adjustments, like removing the comma after the final assignment.

Even if I knew I might need to do this kind of data movement again in the future, I would still consider this approach first before actually going the dynamic SQL route.

If you firmly believe you need a more convenient tool for this problem, then you can use the above statement as a starting point for your script. Browse the dynamic-sql questions on this site or on Stack Overflow to find more examples to help you with the final solution.


Merge multiple column values into one column in different table?

Try with union

insert into table2(column1)
select column1 from table1
union
select column2 from table1
union
select column3 from table1


Do NOT create a tablespace on a removable external drive, especially if you have other data in that PostgreSQL install you care about.

If the external drive is missing then PostgreSQL will fail to start, and be extremely difficult to repair.

I've submitted a patch to add more prominent warnings about this in the latest version's manual.

You had the right idea the first time - move the data directory in its entirety. You just didn't follow through with the full process. The PostgreSQL manual can't cover it all, because it's very dependent on details of the operating system, version, etc.

Since you symlinked the datadir you don't need to change your launchd configuration to point to the new datadir. I'd say your problem was probably filesystem permissions. What you should do in a situation like that is open up Console.app and check the system logs for messages relating to PostgreSQL startup. You should also check PostgreSQL's own log files. It can also be helpful to:

sudo -u postgres ls /path/to/data/directory/

to make sure PostgreSQL has the rights to "see" what's in the data directory in the new location.


Count(column_name) will give numbers of not-null records from the table "Table1".

Sequence would return a number irrespective of number of records in the table "Table1" .
Sequence can be used globally ,not only specific to a table.

if you require a digit on basic of records in table use Count function.
if you  require just a running serial number use sequence.


Best database design for payments with multiple constraints

You obviously put some thought into this design. But you would regret that you have to search for codes in two tables. And what if you want to add details for codes?

I suggest to store all codes in the same table like this:

CREATE TABLE payment (
   idpayment serial PRIMARY KEY
   -- more payment details?
);

CREATE TABLE payment_code (
   idpayment_code serial PRIMARY KEY
 , idpayment      int NOT NULL REFERENCES payment
 , idthingC       int REFERENCES thingC  -- can be NULL
 , idthingD       int REFERENCES thingD  -- can be NULL
 , c1             int NOT NULL
 , c2             int NOT NULL
 , c3             int NOT NULL
 , CONSTRAINT either_c_or_d              -- reference either to c or to d
      CHECK (idthingC IS NULL AND idthingD IS NOT NULL
          OR idthingD IS NULL AND idthingC IS NOT NULL)
 , CONSTRAINT c_distinct_per_payment     -- distinct idthingC per payment (?)
      UNIQUE (idpayment, idthingC)
 );

CREATE UNIQUE INDEX idx_one_d_per_idpayment ON payment_code (idpayment)
WHERE idthingD IS NOT NULL;              -- only one idthingD per payment

(Depending on the nature of the codes, maybe even just 1 code per row.)

The partial unique index idx_one_d_per_idpayment disallows more than one reference to thingD per payment.

This allows at most 1 thingD per payment and any number of distinct thingC.

Mutually exclusive payment types

You later clarified, those are mutually exclusive.
This allows at most 1 thingD per payment or any number of distinct thingC (but not both):

CREATE TABLE payment (
   idpayment serial PRIMARY KEY
 , type_cd   "char" NOT NULL DEFAULT 'C'  -- optional default
   -- more payment details?
, CONSTRAINT type_cd_valid CHECK (type_cd = 'C' OR type_cd = 'D')
, CONSTRAINT redundant_uni_for_fk_constraint UNIQUE (idpayment, type_cd)
);

CREATE TABLE payment_code (
   idpayment_code serial PRIMARY KEY
 , idpayment      int NOT NULL      -- multicolumn ...
 , type_cd        "char" NOT NULL   -- ... FK reference
 , idthingC       int REFERENCES thingC
 , idthingD       int REFERENCES thingD
 , c1             int NOT NULL
 , c2             int NOT NULL
 , c3             int NOT NULL
 , CONSTRAINT either_c_or_d
      CHECK (type_cd = 'C' AND idthingC IS NOT NULL
          OR type_cd = 'D' AND idthingD IS NOT NULL)
 , CONSTRAINT c_distinct_per_payment
      UNIQUE (idpayment, idthingC)
 , CONSTRAINT payment_c_or_d
      FOREIGN KEY (idpayment, type_cd) REFERENCES payment(idpayment, type_cd)
 );

CREATE UNIQUE INDEX idx_one_d_per_idpayment ON payment_code (idpayment)
WHERE type_cd = 'D';  -- slightly simpler now

To enforce your rules, add a type column in table payment (I named it type_cd since "type" is too generic, and I chose data type "char" - with double quotes - efficient for tiny enumerations). Include that column in the FK reference (redundantly). This allows a CHECK constraint (either_c_or_d) in table payment_code to enforce valid references.

The UNIQUE constraint redundant_uni_for_fk_constraint seems redundant, but is required for the multicolumn FK reference on (idpayment, type_cd). Related, with more explanation:

Enforcing constraints "two tables away"

Both FK columns (idpayment, type_cd) must be NOT NULL to enforce referential integrity. Or (if you need rows without assigned payment) declare the FK constraint as MATCH FULL. Details:

Two-column foreign key constraint only when third column is NOT NULL
MATCH FULL vs MATCH SIMPLE in foreign key constraints


MongoDB document size for collection - impact on RAM and query performance

In the future, ask to have the question migrated here instead of double posting.

From the MongoDB Documentation,

  To calculate how much RAM you need, you must calculate your working
  set size, or the portion of your data that clients use most often.
  This depends on your access patterns, what indexes you have, and the
  size of your documents. Because MongoDB uses a thread per connection
  model, each database connection also will need up to 1MB of RAM,
  whether active or idle.

If the query is not completely covered by an index then the entire document is loaded into RAM by MongoDB. This is the same regardless of the type of query.

As for whether or not you should split your collection, that's really a design decision that requires a lot more background to understand and weigh in on. You'll need to perform tests to see if the gains from it based on the query patterns are worth it compared to the loss of atomicity (a business driven decision potentially).

I probably wouldn't recommend this approach though and would assess what your true SLA's are and whether or not you're using the right amount of hardware (RAM) for the desired results. Your real question here is "Do I have enough RAM to satisfy my query performance requirements when it comes to cached data?" If the answer is no, figure out what design works best with your constraints.


How do I set the isolation level for SELECT statement?

You don't need to do anything to get statement level consistency

A query always sees a consistent state of the database regardless of the isolation level you use.

Quote from the manual:

  This means that each SQL statement sees a snapshot of data (a database version) as it was some time ago, regardless of the current state of the underlying data.

See the chapter about Multi Version Concurrency Control in the manual


SSIS Package Validation Error email task when attaching file created by previous task

Pls make sure you have tried the solutions below

Set DelayValidation property set to True for the Send Mail Task.
Close the filestream of the log file before sending the email


Multi-tenant reduce number of MongoDB connections

You can bind connections per DB at mongodb level but can limit at application end.
You can also increase max connection on mongodb with some tweaking on OS level. Still best approach is to put limit and application end with persistent properties.


There is a bug in Cassandra 3.11.x series that means nodetool flush and nodetool compact fail when using a 32 bit install of Java. Switching to 64 bit version solves the problem.

https://issues.apache.org/jira/browse/CASSANDRA-13897


How to aggregate only unique and not null values from column into a JSON array?

You can use jsonb_agg() instead:
select id,
       jsonb_agg(linked_id) as linked_ids,
       jsonb_agg(distinct another_id) filter (where another_id is not null) as other_ids
from the_table
group by id;

If you want a single JSON value for each row (=group) you can use to_jsonb()
select to_jsonb(r)
from (
  select id,
         jsonb_agg(linked_id) as linked_ids,
         jsonb_agg(distinct another_id) filter (where another_id is not null) as other_ids
  from the_table
  group by id
) r

Or using jsonb_build_object()
select jsonb_build_object(
         'id', id,
         'linked_ids', jsonb_agg(linked_id),
         'other_ids', jsonb_agg(distinct another_id) filter (where another_id is not null)
       )
from the_table
group by id


Postgresql pgAdmin, count(*), max(pk_field) giving inconsistent results

Thanks to Hannah Vernon for pointing out the obvious truncation going on, which I'm quite annoyed with myself for not noticing.
Having re run all the queries above in psql, they return the full, non-truncated numbers. Re-running them again in the pgAdmin sql window, I get truncated values. What is most strange is that there is different precision in the truncation and it is reliably so. Who am I to guess at the logic, I'm sure it's a bug and I will look to see if there is a bug tracker for the software.
Many thanks to all for your comments.
EDIT
Here is a comment about column truncation in the FAQ, however it doesn't go into any detail. I would assume this is for long text columns rather than integers over 5 digits, and I can't see where the option it refers to are, but I thought it prudent to include it anyway:

Query tool columns truncated
Some columns are truncated when running a query in the query tool.
You can increase the query option "max. chars per column" for this.
Please note that there's another limit for this imposed by the
underlying windows control, which apparently doesn't allow more than
511 characters.  In pgAdmin V1.1 and up, we provide the function
"execute to file", which has no column restrictions. [AP]

http://www.pgadmin.org/pgadmin3/faq/#ColTrunc


How can I specify the last parameters in a function call and let the others default to the DEFAULT in the declaration?

You can simply supply the fifth argument with Mixed Notation using the => to separate the parameter name from the argument,

SELECT test_func(1,2,3,p5=>5);
test_func
-----------
         5
(1 row)

From the docs on mixed notation with the one caveat (from the docs),

  Named and mixed call notations currently cannot be used when calling an aggregate function (but they do work when an aggregate function is used as a window function).


If you want the SQL in one file and the parameters in another, this is an option. Have query.sql assigning positional parameters to bind variables and then executing a query:

variable p_owner varchar2(30);
variable p_column varchar2(30);

set verify off
set feedback off

begin
    :p_owner := '&1';
    :P_column := '&2';
end;
/

set feedback on

select table_name
from all_tab_columns
where owner = :p_owner
and column_name = :p_column;

exit 0;

And put the parameter values in a text file, say parms.txt:

SYS
OSUSER

And put it together with:

sqlplus -s user/password @query `cat parms.txt`

(This is for Unix, obviously; not sure off-hand what the command-line equivalent is for Windows). Change parms.txt, or use different files, and you'll still get the same hard-parsed plan each time.

Your performance problem sounds like it might possibly be related to bind variable peeking, particularly the 'stuck with a bad plan' description. The execution plan is determined by the first query run, which might choose suitable indexes etc. for those parameters; subsequent runs then use the same plan even if different indexes might be more suitable for the new parameters. The stand-alone straight SQL will have a separate parse and thus possibly different (more suitable) plan. In the past I've had to gather stats manually to prevent histograms being used, which at least stabilises things.


Oracle 12c - Insufficient Privileges When Trying To Create A Trigger With SYS User

Even if you create the trigger as SYS, the owner must have the CREATE TRIGGER privilege:
GRANT CREATE TRIGGER TO CRE;
In addition to the above, to create an AFTER LOGON ON DATABASE trigger, the ADMINISTER DATABASE TRIGGER privilege is also required:
GRANT ADMINISTER DATABASE TRIGGER TO CRE;


Set the SQL Agent service to Automatic(Delayed). The first thing it will do is try to connect to SQL Server, and if SQL Server hasn't finished starting yet, SQL Agent can fail. Delaying the start for SQL Agent will give SQL Server the chance to finish.


Assuming that value is a numeric data type:

SELECT
    A.name
    , A.dateOfBirth

FROM
    artist A
    JOIN exhibitions.picture P
        ON A.name = P.name

WHERE
    P.Value > 50
    AND A.dayOfDeath is NULL;


Group results from two different tables on same hours

Because both your queries return the same number (and type) of fields, the can be combinied with UNION ALL. The ORDER BY is only needed once at then end of the query.

To see the difference between indoor and outdoor this could be added to your query.

SELECT 'out' as door,DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_out_temp
FROM outdoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
UNION ALL
SELECT 'in' as door,DATE_FORMAT(timestamp, '%H:00') AS time, round(avg(temp), 1) as avg_in_temp
FROM indoor
WHERE timestamp >= now() - INTERVAL 1 DAY
GROUP BY DATE_FORMAT(timestamp, '%Y-%m-%d %H')
ORDER BY timestamp ASC;


From the output the two inserts are doing gap locking on the index index_shipments_on_ci_ai_tn_sti. Without knowing the table definition, I think the index is a UNIQUE index.

Here is a decent blog explaining the reason UNIQUE constraints can cause deadlocks on concurrent inserts. Snippet here:

  Mysql innodb engine performs row locking on inserts. If column A has a unique key constraint, and we are adding the value "bbb" for column A in an insert statement, mysql needs to lock any gap in the index between the two current records where "bbb" will be inserted at.

More information on gap locking can be found in the docs: http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html


Select constant text that is the same as a column name

Values in SQLite should be wrapped in single-quotes, like:

INSERT INTO Table1 (a, b, c)
SELECT 'a', a, b
FROM Table2;

However, as @Ypercube asked in his comment, why do you want a column with the same value for every row?


Running an SSIS package owned by a domain user from SQL Server running on a local service account

My personal opinion is that option #1 is the way to go. But I see no need for you to have to grant the domain account local administrator access. It seems to me that it requires access to certain folders and files and so you could grant the domain user access to only the resources that it needs to run the package successfully. This can be done through the file/folder properties dialog box and select the security tab - there should be no need to set it for every file and folder as you could set the permissions of the parent directory and set them to override child properties.

I hope this helps you.


When you're looking to track down the root cause of RESOURCE_SEMAPHORE waits, the plan operators you want to pay attention to are most commonly

Sorts
Hash Joins
Hash Match Aggregate

That's because it's a memory wait. Specifically, queries asking for memory, and SQL Server not having it available for them to start running.

If you're on a more recent version of SQL Server (2012 SP3, 2014 SP2, 2016+), you can use a free tool like sp_BlitzCache (full disclosure, I'm a contributor) and run it to sort your plan cache by memory grant.

EXEC sp_BlitzCache @SortOrder = 'memory grant'

But fair warning, if you're hitting a bunch of that wait, there's a good chance your plan cache is wiped out from the memory pressure.


Is the key_buffer_size applicable to myisam tmp tables?

No, not at all.

Every time, I see a temp table materialize on disk, I see something like this:

-rw-rw---- 1 mysql mysql    0 May  8 17:44 #sql_22d5_2.MYD
-rw-rw---- 1 mysql mysql 1024 May  8 17:44 #sql_22d5_2.MYI

.MYI is always the default size.

On disk, they must be using the MEMORY storage engine. Again, there are no indexes created in conjunction with the temp table.

If you are concerned about many temp tables, use a RAM disk (See my post  Is it bad to create many mysql temporary tables simultaneously?)

Your goal should be avoid making temp tables, regardless of mapping tmp tables to disk or RAM.

See my other posts on this

Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk?
May 10, 2012 : Why is MySQL is creating so many temporary tables on disk?

Going back to your question: Since all your data is InnoDB, you can make key_buffer_size 8M


Patterns/Designs to help with concurrency/performance

One point would be to not have locks on reads. This is what I do generally - ReadCommited as table hint helps (or on connection level). This is not repeatable (no locks in place). Retrieval may be isolated into non-locking transactions this way.

Also then possibly go partially to stored procedures - no round trips. Merge statement etc. - the usual "keep locks super small" approach.


Mysql database slow responding to connects when having only few concurrent connections

The problem was that we have a haproxy in front of the setup. Each call to a webserver was creating a dns query on each of the webservers because the virtualhost was a "servername" with no ip supplied. Adding a local dns bind caching daemon solved this issue although its not the best sollution. (also didn't show up in the beginning since it was udp traffic)

So in the end this was not database related.


Sounds easy. You need transactional replication for read only pricing data and merge for salesHeader and salesDetails.


You should check the real database name + files it consists of by executing dbcc checkprimaryfile on your mdf file

as described here: SQL SERVER – FIX – Error: One or more files do not match the primary file of the database


Parsing DATE while copying csv file into PostgreSQL table

Set datestyle to ISO, DMY, and your dates will be parsed as you want. There is nothing wrong with setting that parameter - do it with SET right before you COPY.
There is no way to skip columns from the CSV file. Add extra columns to the table and drop them later, that is cheap.


split SQL FULL Backups on ola hallengren maintanance solution

In addition to Scott's answer, there is also the NumberOfFiles parameter which would let you create multiple files under the same directory, if desired.