To answer a question like this you need to carefully inspect the plans. I asked and answered a similar question here. It's probably worth reading through, but the most important parts are that compute scalars can be deferred and SQL Server 2016 gives us the sys.dm_exec_function_stats DMV so that it's easier to see how many times a UDF executes in a query.

Your example UDF is nondeterministic which can change how many times it's executed in a query. The use of getdate() and the lack of SCHEMABINDING makes it nondeterministic. You can verify this within SQL Server as well:

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.[sleep]'), 'IsDeterministic')

Let's go through your queries. For the first query the UDF call is in a startup expression predicate:

The filter operator is executed once over 9 rows. The startup expression predicate means that the UDF is only executed once instead of one time for every row processed by the filter. That's why this query only takes 2 seconds.

If I change the query to the following:

SELECT num
FROM #table t
WHERE dbo.sleep(2)=DATEADD(DAY, num, 0);

Now the query takes 18 seconds. In the plan we can see that the filter is no longer a startup expression predicate:

Now the UDF is executed once per row. If I create a completely new function that's deterministic:

CREATE FUNCTION dbo.[no_sleep] (@seconds int)
RETURNS datetime
WITH SCHEMABINDING
as
BEGIN
    RETURN DATEADD(DAY, 0, 0);
END;

Now the query plan changes again:

For this query, the UDF is only executed a single time. You can verify that through the UDF, by using extended events, or by making the function execution take a long time (but it needs to remain deterministic).

For the second query in the question:

SELECT num
   FROM #table t
   WHERE EXISTS (select TOP 1 1
                FROM  #table t1 )
   OR dbo.sleep(2)=0;

The UDF is only executed per query for a different reason. The results of the WHERE clause do not reference table t and SQL Server chooses to effectively cache them into a spool:

If you look at the actual plan you can see that the spool was executed 9 times but the filter was only executed once over a single row.

Your third query doesn't compile, but I assume that you meant this:

SELECT num
   FROM #table t
   WHERE EXISTS (select TOP 1 1
                FROM  #table t1
                WHERE t.num = t1.num) OR dbo.sleep(2)=0;

Now that you reference a column from t in the WHERE clause there's no longer a spool in the plan:

The UDF reference is a startup expression filter but it's still executed 9 times, once for each execution of the filter. If I change the query to use the deterministic no_sleep function then the UDF is only executed once, despite the query plan not changing at all.

To summarize, functions can be executed once per query or once per row depending on the function definition as well as the query definition. Sometimes it can be difficult to figure out how many times the UDF will execute just by looking at the plan. If you need to guarantee that a function will only be executed once you should save the value to a local variable and use that local variable in your query, along with a RECOMPILE hint if you need the parameter embedding optimization.


MySQL Bug required remedy : [Warning] Invalid (old?) table or database name '#sql-'

There is the presence of a bug in this case for two reasons:

111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p1'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p2'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p3'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p4'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p5'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p6'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p7'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p8'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p9'
111116 10:48:29 [Warning] Invalid (old?) table or database name '#sql-3z8g_122a#P#p10'

REASON #1 : Any table starting with #sql is a temp table. If these tables are still present in any database folder and the datetime stamp, simply delete them.

REASON #2 : Look carefully at the suffix of each table. #P#p? resembles a partition tag. This would indicate an attempt to create a temp table using partitiions. That's sounds insane. There was bug report on this back in Feb 16, 2006 for MySQL 5.1.7-beta (closed Mar 15, 2006). The bug report is based on trying to do this manually. Is mysql attempting to do this internally ?

IMHO I would upgrade mysql away from MySQL 5.1 up to MySQL 5.5


Can SQL Server avoid subqueries or joins if other where clauses return enough results?

Does query optimizer manage to prioritize the first clause, avoiding
  to do the subquery if the simpler clause is true for at least 1 record.

It can do

create table customers(id int identity primary key, firstname varchar(10)) ;
insert into customers values ('John'),  ('Johnathan'), ('George');
create table customer_emails(id_customer int, email varchar(100));
insert into customer_emails values (1, 'John@example.com'),  (2, 'Johnathan@example.com');

In the plan I got the first row matched the LIKE 'John%' predicate and the scan against customer_emails was not executed at all.

However your question is phrased as

  if the simpler clause is true for at least 1 record

That would imply that the simpler WHERE clause is evaluated in its entirety and only if that fails is the second one evaluated.

For

SELECT TOP 1 *
FROM customers
WHERE firstname LIKE 'George%'
   OR id IN (SELECT id_customer
             FROM customer_emails
             WHERE email like 'George.doe@%' );

Three rows were processed before one was found matching LIKE 'George%'and there were two ensuing scans on customer_emails

Rewriting as follows...

create index ix on customers(firstname) include(id)

SELECT TOP 1 *
FROM customers with(forceseek)
WHERE firstname LIKE 'George%'
   OR  firstname NOT LIKE 'George%' AND  id IN (SELECT id_customer
             FROM customer_emails
             WHERE email like 'George.doe@%' );

...  happens to give a plan where the operators in the shaded area for the IN part don't get executed if the simpler predicate returns a row but this plan isn't guaranteed.


How do I access a result set from a stored procedure in another stored procedure?

I am looking for a way to do this myself (in a way that satisfies me..), but a perfectly valid way to do so is described in this Stack Overflow Q & A:

Calling a Stored Procedure in a Stored Procedure in MySQL

The accepted answer by AhamedMustafaM demonstrates a technique using OUT parameters with the following code:

    CREATE PROCEDURE innerproc(OUT param1 INT)
    BEGIN
     insert into sometable;
     SELECT LAST_INSERT_ID() into param1 ;
    END
    -----------------------------------
    CREATE PROCEDURE outerproc()
    BEGIN
    CALL innerproc(@a);
    // @a gives you the result of innerproc
    SELECT @a INTO variableinouterproc FROM dual;
    END

I'm sure not always the answer for what you are trying to accomplish, but it is certainly an option. While you couldn't get the direct result of what the procedure called, this would help you get individual values, possibly using multiple OUT parameters to get what you need.


This is a tricky one. Your main condition is on next_execution_date, but the output is sorted by priority first. The conditions on status_id and is_active only play a minor part.

Better index

Your index idx_pd_order is not a big help, since filtering on non-leading columns of a multi-column index is not very efficient. Postgres is using it - still a lot better than a sequential scan. Details here:
Is a composite index also good for queries on the first field?

idx_pd_where might be a better choice, but not a good one, either. The leading column status_id is not selective at all and just bloats the index. Same goes for the trailing column is_active. And priority is not in the index and has to be fetched from the table, making an index-only scan impossible.

I suggest this partial, multi-column index to start with. (But keep reading!)

CREATE INDEX idx_pd_covering ON tb_pd (next_execution_date, priority, st_id)
WHERE  status_id = 1 AND is_active = 1

Since we are only ever interested in rows with status_id = 1and is_active = 1 exclude other rows from the index right away. Size does matter.
The remaining (crucial) condition is on next_execution_date, which must come first in the index.
priority and st_id are only appended for a possible index-only scan (Postgres 9.2+). If that doesn't take, remove the columns from the index to make is smaller.

Special difficulty

We can now use idx_pd_covering to find qualifying rows quickly, unfortunately we have to look at all qualifying rows to collect the ones with highest priority. As the query plan reveals, Postgres estimates to process 34627017 rows. Sorting 35M rows is going to cost big. That's the tricky part I mentioned at the start. To demonstrate what I am talking about, run EXPLAIN on your query with and without priority in ORDER BY:

SELECT s.st
FROM   tb_pd p
JOIN   tb_st s USING (st_id)
WHERE  p.status_id = 1
AND    p.is_active = 1
AND    p.next_execution_date < 1401402110830
ORDER  BY priority, next_execution_date
LIMIT  20000;

That's your query, formatted only slightly simplified. You should see a huge difference.

Solution

The solution depends on the number of distinct values for priority. For lack of information and for demo purposes I am going to assume only three. Priority 1, 2 and 3.

With a trivial number of distinct priority values, there is a simple solution. Create three partial indexes. All of them together are still smaller than your current indexes idx_pd_order or idx_pd_where (which you might not be needing any more).

CREATE INDEX idx_pd_covering_p1 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 1 AND status_id = 1 AND is_active = 1;

CREATE INDEX idx_pd_covering_p2 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 2 AND status_id = 1 AND is_active = 1;

CREATE INDEX idx_pd_covering_p3 ON tb_pd (next_execution_date, st_id)
WHERE  priority = 3 AND status_id = 1 AND is_active = 1;

Use this query:

SELECT s.st
FROM  (
   (
   SELECT st_id
   FROM   tb_pd
   WHERE  status_id = 1
   AND    is_active = 1
   AND    priority  = 1
   AND    next_execution_date < 1401402110830
   ORDER  BY next_execution_date
   )
   UNION ALL
   (
   SELECT st_id
   FROM   tb_pd
   WHERE  status_id = 1
   AND    is_active = 1
   AND    priority  = 2
   AND    next_execution_date < 1401402110830
   ORDER  BY next_execution_date
   )
   UNION ALL
   (
   ...
   AND    priority  = 3
   ...
   )
   LIMIT  20000
   ) p
JOIN   tb_st s USING (st_id);

This should be dynamite.

Strictly speaking, the final order is not guaranteed without an additional ORDER BY clause in the outer query. In the current implementation, the order from the inner query is preserved as long as the outer query is as simple as that. To be sure, you could join right away (which may be a bit slower):

)
SELECT s.st
FROM   tb_pd p
JOIN   tb_st s USING (st_id)
WHERE  p.status_id = 1
AND    p.is_active = 1
AND    p.priority  = 1
AND    p.next_execution_date < 1401402110830
ORDER  BY p.next_execution_date
)
UNION ALL
(
...
)
LIMIT  20000;

.. or carry along priority and next_execution_date to order once more in the outer query (to be absolutely sure), which is probably slower, yet.

All parentheses are needed! Related answer.
This query just reads tuples from the top of above partial indexes, no sort-step needed at all. All rows are pre-sorted, efficient to boot.
UNION ALL queries without a final ORDER BY can stop as soon the number of requested rows in the top-level LIMIT have been fetched. So if there are enough rows in the top priorities, subsequent legs of the UNION ALL query are never executed. This way, only the smaller partial indexes have to be touched.
JOIN to tb_st later, should be more efficient.
Again, the column st_id is only appended to the index in the hope for an index-only scan. If that works for you, the whole query does not even touch the table tb_pd at all.

General solution for any number of distinct priority values

We have solved this before. There is a complete recipe to automate the creation of partial indexes and a function .. the works:
Can spatial index help a "range - order by - limit" query

Optimize table

Since you are trying to optimize performance and your table is big, I suggest a slightly altered layout for your table tb_pd:

   Column            |  Type
---------------------+--------
 pd_id               | integer
 st_id               | integer
 next_execution_date | bigint
 priority            | integer  -- or smallint? -- or "char"?
 status_id           | smallint -- or "char"
 is_active           | boolean

This occupies 52 bytes per row on disk, while your current design needs 60 bytes. Indexes profit as well. Details:
Configuring PostgreSQL for read performance

Of course, all the basic advice for performance-optimization applies as well.

About "char":

  The type "char" (note the quotes) is different from char(1) in that it
  only uses one byte of storage. It is internally used in the system
  catalogs as a simplistic enumeration type.


This is the output of sp_server_diagnostics which is run to do health checking of either the primary replica in an availability group or an FCI. These are normal files to have and can help when investigating issues with availability groups or FCIs when dealing with failover.

Depending on the component and state, it may be an issue or may not be an issue.

Clean - No issues
Warning - There have been some key metrics above what is expected
Error - When a policy has been violated and a failure should be taken

Only System, Resource, and Query Processing are involved in the failover policies. Events will always say unknown and io_subsystem will only ever be clean or warning.
You can read more about this here for AG or FCI.

Whether or not any of the items are an issue is another matter which can only be ascertained by the frame of which the issue occurred. Obviously if any say Error, that's a problem, but warning requires a frame of reference to understand if it's something that should be further looked into or disregarded.


Replication and table locks during alter table

Would it make sense to explore a replicated PG setup (master-slave perhaps), where all writes and migrations are executed against the master, and all high volume reads are executed against the slave?

Yes, that's one option. But if you write your migrations more carefully (Rails's defaults are pretty simplistic) you can greatly reduce the locking that's required on the master anyway.

For example, instead of

ALTER TABLE tblah ADD COLUMN cblah DEFAULT dblah NOT NULL

you can write

ALTER TABLE tblah ADD COLUMN cblah;
ALTER TABLE tblah ALTER COLUMN cblah DEFAULT dblah;
UPDATE tblah SET cblah = dblah WHERE cblah IS NULL;
ALTER TABLE tblah ALTER COLUM cblah NOT NULL;

The latter will not hold a strong lock over the table rewrite, and will be much less disruptive. It does more work overall, but with lower lock levels.

If you take more care with your migration writing and testing, you'll find that this issue mostly goes away. I've helped with near-zero downtime schema changes on multi-terabyte databases and the same principles apply there.

  How does PG behave when an ALTER TABLE statement is replicated to a slave? Does the slave also acquire the same table locks?

Yes. You can always switch reads to the master while the replia applies a big schema update that's already committed on the master, though.


Select rows based on values from 1 column being the same?

for selecting values based on a specific column being the same you could have a look at NTILE

  Distributes the rows in an ordered partition into a specified number
  of groups.  The groups are numbered, starting at one.  For each row,
  NTILE returns the number of the group to which the row belongs.

there are actually many options, including common_table_expression

on your example:

--==================================================================================
use tempdb
if object_id('tempdb..radhe','U') is not null
   drop table radhe

create table  radhe(
 [Film Id] int not null
,[Scene Id] int not null
,[Value Id]int not null
)

insert into radhe ([Film Id],[Scene Id],[Value Id])
select
   0    ,     0    ,     7
union all select
   0    ,     1    ,     1
union all select
   0    ,     1    ,     0
union all select
   1    ,     2    ,     8
union all select
   1    ,     2    ,     3
union all select
   1    ,     3    ,     1
union all select
   2    ,     4    ,     3
union all select
   2    ,     4    ,     7
union all select
   2    ,     5    ,     2

--I want to select rows
--where the value id is equal to 8 or 3
--only if they are both in a row with the same scene id.

--using a cte
;with t8 as (

        SELECT r.[Film Id], r.[Scene Id], r.[Value Id]
        FROM radhe r
        WHERE r.[Value Id]IN (8)
) ,
t3 as (

        SELECT r.[Film Id], r.[Scene Id], r.[Value Id]
        FROM radhe r
        WHERE r.[Value Id]IN (3)
)

select t8.*
from t8
inner join t3
        on t8.[Scene Id] = t3.[Scene Id]
union all
select t3.*
from t8
inner join t3
        on t8.[Scene Id] = t3.[Scene Id]

--====================================================================

--using NTILE
select r.[Film Id], r.[Scene Id], r.[Value Id]
from
(
SELECT
    NTILE(2) OVER (ORDER BY [Scene Id]) [NTILE],
    *
FROM Radhe
WHERE [Value Id]IN (3,8)
) r
where r.NTILE = 1

the results are the same but what about the performance?

check this and this out.


How to pivot multiple columns in PostgreSQL 9.5?

The following query produced the results I require. However, if there is more than 2 parts (part of database column), then additional columns, AVG3, SUM3 need to be generated dynamically.

select a.ITEM, a.CD, a.AVG1, b.SUM1, a.AVG2, b.SUM2
from ( SELECT substr(row_name,1,POSITION('|' IN row_name)-1) as item
              substr(row_name,POSITION('|' IN row_name)+1,99) as cd,
              AVG1,
              AVG2
         FROM crosstab('SELECT item||''|''||cd, part::text, cnt::text
                          FROM a
                         WHERE type = ''AVG''
                        order by item, cd, part',1)
           AS ct (row_name text, AVG1 text, AVG2 text)
     ) a,
    ( SELECT substr(row_name,1,POSITION('|' IN row_name)-1) as name,
             substr(row_name,POSITION('|' IN row_name)+1,99) as cd,
             SUM1,
             SUM2
        FROM crosstab('SELECT item||''|''||cd, part::text, cnt::text
                         FROM a
                        WHERE type = ''SUM''
                       order by item, cd, part',1)
          AS ct (row_name text, SUM1 text, SUM2 text)
    ) b
where a.item = b.item
  and a.cd = b.cd;


UPDATE with join condition on matching words in columns of another table

The key here is that Column_1, represents three possible values for the JOIN. So what you want to use is string_to_array() (so long as those values are comma-separated and can not themselves include a comma).

Run this query,

SELECT id_number, string_to_array(column_1, ',') AS column_1
FROM table_a;
 id_number |       column_1
-----------+-----------------------
       123 | {foo,bar,baz}
       456 | {qux,quux,quuz}
       789 | {corge,grault,garply}
       101 | {qux,bar,grault}

Now, we can run our UPDATE using = ANY(),

UPDATE table_b
SET id_number = A.id_number
FROM (
  SELECT id_number, string_to_array(column_1, ',') AS column_1
  FROM table_a
) AS A
WHERE table_b.column_1 = ANY(A.column_1)
  AND table_b.column_2 = ANY(A.column_1);

You can alternatively use <@

WHERE ARRAY[table_b.column_1, table_b.column_2] <@ A.column_1;

That even makes it a bit more compact..

UPDATE table_b
  SET id_number = A.id_number
FROM table_a AS A
  WHERE ARRAY[table_b.column_1, table_b.column_2] <@ string_to_array(A.column_1, ',');


Service Principal as SQL Active Directory Admin, does it use Graph API?

Got an official answer from Microsoft today:

  I have confirmed that creating AAD users and groups is currently NOT supported when using Service Principal accounts. This particular limitation is only specific to database contained users.


How to insert multiple IP addresses in a pg_hba.conf file via unix shell script?

If you have a file which holds all the addresses (1 at a line) you can do:

cat ipaddresses.txt | while read line;
do
  ./reset_ip.sh $line /root/Desktop/BashScripts/pg_hba.conf;
done

or, with gawk:

gawk '{ system("./reset_ip.sh $1 /root/Desktop/BashScripts/pg_hba.conf") }' ipaddresses.txt


What you can do is to first unpivot your second table and then use conditional aggregation to pivot it again

SELECT c,
       MAX(CASE WHEN column_1 = 'xdata1' THEN value END) xdata1,
       MAX(CASE WHEN column_1 = 'xdata2' THEN value END) xdata2,
       MAX(CASE WHEN column_1 = 'xdata3' THEN value END) xdata3
  FROM
(
  SELECT t1.column_1,
         t2.c_id,
         c,
         CASE c
           WHEN 'c_1' THEN c_1
           WHEN 'c_2' THEN c_2
           WHEN 'c_3' THEN c_3
           WHEN 'c_4' THEN c_4
         END value
    FROM table1 t1 JOIN table2 t2
      ON t1.column_id = t2.c_id CROSS JOIN
  (
    SELECT 'c_1' c UNION ALL
    SELECT 'c_2'   UNION ALL
    SELECT 'c_3'   UNION ALL
    SELECT 'c_4'
  ) n
) q
 GROUP BY c

Output:

|   C |   XDATA1 |   XDATA2 |   XDATA3 |
|-----|----------|----------|----------|
| c_1 | a_data_1 | a_data_2 | a_data_3 |
| c_2 | b_data_1 | b_data_2 | b_data_3 |
| c_3 | c_data_1 | c_data_2 | c_data_3 |
| c_4 | d_data_1 | d_data_2 | d_data_3 |

Here is SQLFiddle demo


SQL Server 2008 R2 - Access Failure in a query that uses Linked server

A number of comments asked if Kerberos was configured correctly, but I do not see a direct response to that issue.  Based on the comments that you make I suspect Kerberos is not set up correctly.

Greatly simplified description:

If UserAccount is directly logged onto Server A then a linked server to Server B is one hop away and the UserAccount's authentication will be recognized on Server B.  This works with or without Kerberos delegation.
Is UserAccount is directly logged onto his desktop and accesses Server A then Server A is one hop away from the desktop and the UserAccount's authentication will be recognized on Server A. However, if from the desktop he runs a query against Server A that uses a linked server to access Server B that is two hops away from his desktop. Without Kerberos (or some similar service) the UserAccount's delegation cannot make the second hop and, despite the rights granted on Server B, will not authenticate on Server B.

This is all about how NTLM and Kerberos work. There is a blog from a few years ago that offers some details on Linked Servers and NTLM or Kerberos authentication:

http://blogs.msdn.com/b/sql_protocols/archive/2006/08/10/694657.aspx

One edited quote is: "In a single-hop setting, windows NTLM authentication, which is available if all machines are windows, is sufficient for delegation; while in a double-hop setting, Kerberos authentication is necessary for the user’s credential to cross machine boundaries from the client to the linked server."

Now, suppose that you do indeed have Kerberos configured properly and still are mystified by the behavior.  In that case you might need to check into whether there is a duplicate SPN.  If so that will need to be corrected.

The following blog discusses duplicate SPNs and other Kerberos issues:
http://blogs.technet.com/b/askds/archive/2013/07/01/interesting-findings-on-setspn-x-f.aspx

Note: Some people just fall back to using Named Pipes instead of TCP/IP, but this is not a recommended solution any more.

Bottom line: Resolve the authentication delegation problem and everything should work.


Strange PostgreSQL behaviour on service start

Ok, i understood.
It was a "problem" of selinux.
I changed the data directory, and selinux "didn't know" it, so i
chcon -R -t postgresql_db_t /data/pgdata
restorecon -RFv /data/pgdata.


Mysql And Galera cluster mariaDB , Private and Public IP Addresses multisite

Maybe you can reach what you want changing gcomm ip addresses to custom non-FQDN hostnames (db1, db2, dbN). Then you can play a little bit with DNS resolution just updating your hosts file on each db node.
This is not official nor valuable solution anyway ;)


How to optimize query for text array in PostgreSQL using `@>` operator

If the strings are from a restricted set, you can define an ENUM datatype.  This translates the strings to integers behind the scenes.

create type alph as enum ( 'a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z');

create table j as select floor(random()*100)::int, array_agg(substring('abcdefghijklmnopqrstuvwxyz',floor(random()*26)::int+1,1)) from generate_series(1,10000000) f(x) group by x%1000000;
create table j2 as select floor, array_agg::alph[] from j;

I get about a 2 fold speed improvement by doing:

select * from j2 where  array_agg @> '{a,b}';

rather than

select * from j where  array_agg @> '{a,b}';

If I include the condition and floor=7 (after creating an index on "floor"), then both queries are so fast that any difference in speed can not be reliably detected.

This seems like the essence of premature optimization to me.


You should be able to connect the msdb.mdf using:

CREATE DATABASE msdbtest ON (
    NAME = 'msdb_data'
    , FILENAME = 'c:\path_to_your_old_msdb\msdb_data.mdf'
) FOR ATTACH;

Note, depending on the operating system where you are running the above command, you may need to elevate permissions for SQL Server Management Studio (runas administrator) for the command to work.  See my question here for details.

I purposely corrupted the .ldf on my machine before attaching the file, and received the following error message about the newly attached msdbtest database:

File activation failure. The physical file name "C:\SQLServer\Logs\msdbtest_log.ldf" may be incorrect.
New log file 'c:\sqlserver\data\msdbtest_log.ldf' was created.
Msg 824, Level 24, State 2, Line 1
SQL Server detected a logical consistency-based I/O error: incorrect checksum
(expected: 0xa3ba6e3e; actual: 0x208d1401). It occurred during a read of page (2:0)
 in database ID 11 at offset 0000000000000000 in file 'C:\SQLServer
 \Logs\msdbtest_log.ldf'.  Additional messages in the SQL Server error log or system
 event log may provide more detail. This is a severe error condition that threatens
 database integrity and must be corrected immediately. Complete a full database
 consistency check (DBCC CHECKDB). This error can be caused by many factors; for more
 information, see SQL Server Books Online.

I ran DBCC CHECKDB('msdbtest') which reported no errors.  I was then able to run the following SELECT statements to see details about the jobs stored in msdb.

SELECT * FROM dbo.sysjobs
SELECT * FROM dbo.sysjobsteps
SELECT * FROM dbo.sysjobstepslogs
SELECT * FROM dbo.sysjobschedules
SELECT * FROM dbo.sysjobservers
SELECT * FROM dbo.sysjobhistory
SELECT * FROM dbo.sysjobactivity

You could use the information stored in these tables to recreate the jobs.


Once you change the master_host, you have to redo everything else

It says so in the MySQL Documentation on CHANGE MASTER TO

  If you specify the MASTER_HOST or MASTER_PORT option, the slave assumes that the master server is different from before (even if the option value is the same as its current value.) In this case, the old values for the master binary log file name and position are considered no longer applicable, so if you do not specify MASTER_LOG_FILE and MASTER_LOG_POS in the statement, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 are silently appended to it.

Just specify everything again

STOP SLAVE;
CHANGE MASTER TO
    master_host='IP_of_new_host',
    master_port=3306,
    master_user='repluser',
    master_password='repluserpasswowrd',
    master_heartbeat_period=1,
    master_auto_position=1
;
START SLAVE;

GIVE IT A TRY !!!


How do I identify tables that have a foreign key to a specific table in Sybase?

Been a while since I've worked in sybase but (from memory) the following sql should point you in the right direction:

select *
from sysobjects so inner join syscolumns sc on so.id = sc.id
where sc.name = 'field name'

you can also check out the sybase books online (system tables)


Replicating schema changes between production and development databases

If you have access to a db version control system (ala Red Gate), you can have it create a deployment script fairly easily.
If not, just create a file and script any ddl changes you make and store them in a file, then when you are ready (and have backed up prod) you can run the script against prod to make your changes.
Be aware that certain changes can require extra steps (adding a not null column without a default is always a favorite), but that is the basic procedure. Also be aware of how your data might affect your ability to promote changes (key constraints, etc.) which is why tools like Red Gate's SQL Compare and Version control are worth the money.
No I don't work for Red Gate, but I've had to do this job with and without their tools and it is a lot more work without them.


Postgres automatic failover, multiple clients to multiple masters

I can recommend using Slony for PostgreSQL replication from my experience with it.

I can't respond better then the Slony docs section on failover so I'll quote it:

  If some more serious problem occurs on the "origin" server, it may be necessary to SLONIK FAILOVER to a backup server. This is a highly undesirable circumstance, as transactions "committed" on the origin, but not applied to the subscribers, will be lost. You may have reported these transactions as "successful" to outside users. As a result, failover should be considered a last resort. If the "injured" origin server can be brought up to the point where it can limp along long enough to do a controlled switchover, that is greatly preferable.

  Slony-I does not provide any automatic detection for failed systems. Abandoning committed transactions is a business decision that cannot be made by a database system. If someone wants to put the commands below into a script executed automatically from the network monitoring system, well ... it's your data, and it's your failover policy.

To your question: does it make sense?, personally, I would say, no. I agree with the Slony docs that it's better to have a human understand why there was a failure and then manually (and quickly) failover.

Having managed PostgreSQL for more than a decade, unless there's a hardware failure, it's difficult to make it seriously fail. And I recommend involving a human to confirm hardware failure and check the state of the system before failing over.

If you are getting started with a high-availability PostgreSQL, I would first look at the hardware, tuning and RAID configuration behind your PostgreSQL database before considering automatic failover.


Msg 1833, File 'ABC.ndf' cannot be reused until after the next BACKUP LOG operation

File '%ls' cannot be reused until after the next BACKUP LOG operation.

SQL Server insists that a backup is taken before a file is reused so it can guarantee database recovery in case of disaster.

The minimum backup needed is a log backup, but the documentation recommends a full database backup after any operation that adds or removes files from a database:

By adding a file, removing it, and attempting to add it again, you run into this requirement.

If you have a genuine need to add and remove files, you will need to add a BACKUP DATABASE or BACKUP LOG statement to your script.

This backup will form part of the recovery chain, so it is important you keep it safe in case you need to recover the database.

See BACKUP (Transact-SQL) for syntax details and further information.


SQL Server with French_CI_AS Collation date conversion

This is not a collation issue. This is a language / locale / culture issue. Your DB (or your login's default language) is set to "French" (most likely) and they use Day then Month formats, not Month then Day, hence your statement is attempting to use month 13.
Try this to reproduce:
SET LANGUAGE French;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
/*
Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line XXXXX
La conversion d'un type de données varchar en type de données datetime a
    créé une valeur hors limites.
*/

GO
PRINT '-----';
GO

SET LANGUAGE English;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
GO

You have two options for changing this:

SET LANGUAGE
This controls not just the date format but also currency symbol, day and month names, etc. This might not be the best choice given that it controls more than just the date format, but it might be easier if it only requires updating the login's default language instead of updating a lot of code to do option # 2.

SET DATEFORMAT
This controls just the date format:
SET LANGUAGE French;
DECLARE @datevar DATETIME = '2020-10-13 04:42:10';
/*
Le paramètre de langue est passé à Français.
Msg 242, Level 16, State 3, Line XXXXX
La conversion d'un type de données varchar en type de données datetime a
    créé une valeur hors limites.
*/

SET DATEFORMAT ymd;
DECLARE @datevar2 DATETIME = '2020-10-13 04:42:10';

SELECT @datevar AS [@datevar], @datevar2 AS [@datevar2];
/*
@datevar    @datevar2
NULL        2020-10-13 04:42:10.000
*/

Regarding your other questions:

If the format is dmy, shouldn't both dates fail?

and

(implied) Why do DATETIME and DATETIME2 appear to parse the same date differently?

All of that is because working with dates is really messy. The documentation for DATEFORMAT states not only that:

datetime and smalldatetime interpretations may not match date, datetime2, or datetimeoffset

but also:

Some character string formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.

While the documentation doesn't tie those two statements together, testing does show that ISO 8601 formatted dates (e.g. YYYY-MM-DD) are "interpreted independently of the DATEFORMAT setting" only for DATE, DATETIME2, and DATETIMEOFFSET. That is why the date of '2020-10-13' respects the value of DATEFORMAT for your DATETIME variable (and hence receives an error for cultures where the day comes before the month, whether separating date parts with "-" or "/"), but is parsed correctly for your DATETIME2 test (and always will regardless of the current culture or value of DATEFORMAT).
P.S. I would not consider a variant / SQL_VARIANT / object, etc to be the "right" type ;-)


Do data types go into a Relational Data Model (RDM)?

In a nutshell:
Your diagram would be more complete if it showed the data types -- and a relational DB design must have data types specified -- but as a brief summary, it is OK to omit types for brevity, especially if your field names are descriptive enough.

Strictly speaking, the relational DB model includes the table and the column where a table is a tuple of columns and each column is of a particular type.

What you are drawing is a relational DB schema or design -- an application of the model for a particular business case.
Although, again, strictly speaking, what you have drawn is a hybrid between an entity-relationship diagram (ERD) and a relational schema -- even though it is a very common and useful style of diagram.

An ERD would not have any surrogate (system-generated) keys, as those are not part of the business semantics, but would have the lines -- labelled with 1 or m -- representing logical connections between entities.

On the other hand, a relational schema would not have any connecting lines as these links are not explicitly part of the relational model -- the links are implied by fields having the same data type -- but it would have surrogate keys.

As for the missing data types, yes, ideally both the DB schema and the ERD are much more complete if/when they also specify those, but that is not to say you cannot have diagrams of different levels of detail for economy of communication.


History of actions that occured on a table in SQL Server

Here's some out-of-the-box features of SQL Server that can help you accomplish your goal. You'll need to proactively enable / set one of them up before they collect the data you need.
For reference:

Triggers - Fire whenever data changes in a table, can implement logic similar to being in the context of a stored procedure.

Temporal Tables - System versioned copies of the user defined tables that track changes.

Change Tracking - Automated tracking of changes for your user defined tables.

Change Data Capture - Keeps track of DML changes to the specified user defined tables.

Audit - Automatically tracks a multitude of actions at the server level.


Oracle Exadata database machine is a hardware solution from Oracle which runs the usual Oracle Database Software. However it has an extension: the disks are accessed by special storage nodes which are smart: when the DB tells them to read blocks from a database table they can also tell them filter conditions: the result is that IO bandwidth and caches are distributed to multiple machines and table scans are parallelized (offloading from the main database machine). Besides that there can be additional Flash Cache and the interconnect use fast Infiniband network connections.

This makes it not only faster for typical OLAP queries, but also more robust against wild ad-hoc queries. The disadvantage is that you need to have a big workload so it is worth it.

Exadata uses Linux or Solaris as the Operating System.

It must not be confused with ODA, which is the database appliance without the special storage nodes.

Exadata is for the purpose of applications interacting with it invisible. This also means you can import backups from there and run the same queries (sans management queries for the distributed storage of course).


Force Sql server to use index seek in merge join

To get a clustered index seek, you'd need a clustered index that supports your filter (e.g. leading key would have to be Personal_ID, not ID).

You can't force a seek if there's no index with a leading column of Personal_ID that supports the filter.

This does not mean you should change the existing clustered index, unless this is the only query you ever run against the table.

And while you could create a non-clustered index with Personal_ID as the key column, a seek on that index might not be what you want anyway - since you're using SELECT * (are you really sure you need all columns from both tables?), it will need to go fetch the rest of the columns from the clustered index anyway, and if there are more than some number of rows returned, at some point a seek (well, what would amount to a range scan disguised as a seek) + lookups will be more expensive than a regular scan.

Why do you think you need a seek here? How many rows does the query return, how wide are they, and how long does it take? Is this just educational, or are you under the assumption that a seek will always perform better than a scan? (It won't, btw.)

Some useful reading:

Bad habits to kick : using SELECT * / omitting the column list
Assumption #2 : A seek is better than a scan, always


Try with COALESCE instead of ISNULL. With ISNULL, SQL Server doesn't seem capable of pushing a predicate against the narrower index, and therefore has to scan the clustered to find the information.

CREATE TABLE dbo.Diffs
    (
    Id int NOT NULL IDENTITY (1, 1),
    DataA int NULL,
    DataB int NULL,
    DiffPersisted  AS COALESCE(convert(bit, case when [DataA] is null
      and [DataB] is not null then 1 when [DataA] <> [DataB]
      then 1 else 0 end), 0) PERSISTED ,
    DiffComp  AS COALESCE(convert(bit, case when [DataA] is null
      and [DataB] is not null then 1 when [DataA] <> [DataB]
      then 1 else 0 end), 0),
    DiffStatic bit not null,
    Primary Key (Id)
    );

That said, if you stick with a static column, a filtered index might make more sense, and will have lower I/O costs (all depending on how many rows typically match the filter predicate) e.g.:

CREATE INDEX ix_DiffStaticFiltered
  ON dbo.Diffs(DiffStatic)
  WHERE DiffStatic = 1;


If you're testing with 50,000 rows in data_dynamic, I'd expect your inner join to return 50,000 rows. It seems to be returning about 10,000. There's a foreign key constraint—this seems unusual to me.

I loaded a bunch of random data, and ran this.

analyze data_dynamic;
analyze data_static;
explain analyze
select count(d.id) from data_static s inner join data_dynamic d on s.id = d.id;

And here's what my box returned. (PostgreSQL 9.0.2)

Aggregate  (cost=14941.95..14941.96 rows=1 width=4) (actual time=477.633..477.633 rows=1 loops=1)
  ->  Merge Join  (cost=8409.72..14816.94 rows=50000 width=4) (actual time=237.807..456.682 rows=50000 loops=1)
        Merge Cond: (s.id = d.id)
        ->  Index Scan using data_static_pkey on data_static s  (cost=0.00..91190.58 rows=3000000 width=4) (actual time=0.016..195.279 rows=350001 loops=1)
        ->  Index Scan using data_dynamic_pkey on data_dynamic d  (cost=0.00..2342.23 rows=50000 width=4) (actual time=0.010..60.933 rows=50000 loops=1)
Total runtime: 477.706 ms

So it looks like the query planner is capable of coming up with a good plan for these tables and this quantity of data.

Does this mean your problem is either with hardware or with PostgreSQL configuration? I'm not sure it's that simple.

Later . . .

I was right; it's both simpler and not that simple.

Based on these snippets from PostgreSQL mailing lists, I'm going to say that the query planner simply doesn't yet support removing inner joins.

From the pgsql-performance mailing list, 02-Mar-2011

  The planner doesn't currently make any deductions whatsoever from the
  presence of a foreign key constraint; and even if it did, I'm not sure
  that this would help it decide that a join order constraint could
  safely be dropped.

And from pgsql-hackers, 12-Dec-2010

  I'm not going to argue that careful analysis isn't needed before doing
  something like this - and, in particular, if we ever get inner-join
  removal, which I'm still hoping to do at some point, . . .


Don't do it in a loop.  Do it in a single multi-table UPDATE and/or INSERT ... SELECT ... LEFT JOIN ... IS NULL.

More details:  http://mysql.rjweb.org/doc.php/staging_table#normalization


How to secure SQL Server being accessed from the DMZ

For best practices on SQL Server security there are hundreds of pages of documentation from the DoD ("Security Installation Checklist" and "Security Checklist") and CIS ("SQL Server Security Benchmark").

This aside a more realistic approach is rooted in common sense.

Your firewall should only allow access from the specific IPs in the DMZ only directly to the IP of the database in your main domain. This is to reduce the attack surface.
The logins being used to access the new data should be specific to this purpose, and only have the permissions required; i.e. read or write (not necessarily both).
And as your server is used for other things you'd need to review its existing security setup.

What existing logins are there that may be able to access the data? For example sysadmin role users, users that can restore backups, users that are in msdb agent roles which can often be used to escalate privileges.
What administrator users are on the server itself? Anyone who has this will be able to access SQL Server and they can't be stopped, only slowed.
Is it patched (Windows and SQL)?

And what is in the data? Is it private? Could it provide insiders/competitors with financial advantage, or could an attacker use it to further penetrate a network? Those answers determine what other functionality you may need to put in place to safeguard it, and may require nothing special, or may require AlwaysEncrypted or encryption at rest for the disk files and backups.

But primarily once this is all together you put this in a document/email and get someone of appropriate authority to accept the risk in order to protect yourself. You are unlikely to be able to stop all risks, only reduce attack surface and add layers to the security onion.


Oracle: procedures+functions code base not overlapping package body code base

Your search will have to be more complicated.  Stored code can be in the package header which is not done that often.  Most often it is in PACKAGE BODY.  If you modified your query to read

SELECT DISTINCT name, type FROM all_source WHERE UPPER(text)
LIKE UPPER(‘%string%’) and type ='PACKAGE BODY';

This gives all the code that meets your criteria in package bodies.  The line number is all you can get out of all_source.  The fields available in all_source are

OWNER VARCHAR2(30)     Owner of the object
NAME  VARCHAR2(30)     Name of the object
TYPE  VARCHAR2(12)     Type of object: FUNCTION, JAVA SOURCE, PACKAGE,PACKAGE BODY, PROCEDURE, TRIGGER, TYPE, TYPE BODY
LINE  NUMBER           Line number of this line of source
TEXT  VARCHAR2(4000)   Text source of the stored object

When you search for type PROCEDURE or FUNCTION you are looking at the stand alone code blocks that are not part of a package.

If the style of coding includes local procedures or functions within procedures or functions in a package body this could get complicated fast. You could look for the mandatory syntax that precedes a code block:  PROCEDURE  xxx or FUNCTION xx is and the code that ends it END ;

So your search would be:

find all procedure or function names in a package or package body and the line number
find the line number of the text you are looking for
write some logic to find the name of the code block that contains the text

Or you could use a GUI tool like TOAD or SQL Developer which will do this for you.


Combined or separated tables: File and Message?

I would definitely go with two separate tables.

  customer wants to communicate by writing a message without having
  attachment (FilePath) or customer uploads any files without having any
  message (Content) written.

But with the way you're modeling it right now, there really isn't any relationship between file and message, as they both are dependent on Customer_ID.  If you tried to query all files and messages for each customer, you wouldn't be able to tell when the two are related (except maybe by looking at the date).

And it sounds like a customer can have a file without a message (and vice versa).  I think it makes more sense to create a "Communication" table (for lack of a better word).  Essentially it allows you to keep track of all types of communication (message and file) for your customers, but also ties message with file (attachments) when appropriate.

table: Communication
communication_id   int
customer_id        int
created            datetime

That would allow you to further normalize by taking "created" and "customer_id" out of the file and message tables.  Likewise, you'd want to add "communication_id" to both File and Message.  Then to report on them, you could do something like this:

SELECT *
FROM Customer cu
INNER JOIN Communication co ON cu.customer_id = co.customer_id
LEFT JOIN File f ON f.communication_id = co.communication_id
LEFT JOIN Message m ON m.communication_id = co.communication_id
ORDER BY cu.customer_id, co.communication_id


[Can't add comments yet so "commenting" here and a moderator can change it to a comment.]

I would recommend checking the network adapter properties on all systems involved and disabling auto-detect network speed/duplex.  If it's set to auto-detect and you don't want to change it without knowing it's causing a problem, copy some large files and see if you're getting the expected throughput.  If not, auto-detect has probably not negotiated the correct speed an duplex and this will cause poor throughput and flaky connections.  I used to do tech support for a data replication co. and we fixed so many connection stability and throughput issues with this it's not even funny.


How can I change default location for tablespaces

show parameter db_create_file_dest will point to default datafile location.

To change default location execute this command

alter system set db_create_file_dest='target_dir' scope=both;

(change is immediate no need to bounce the database)


Should we centralize databases or have a database with each application?

What you have read (even though it's oldish) is still very relevant!

Your problem is called multi-tenancy - to do it or not - the consensus appears to be "not", but there is a wide divergence of views on this issue - you can "partially multi-tenant" - i.e. keep virtually unchanging reference tables in common for example - however the same issues as outlined below apply to "partial" multi-tenancy.

My advice - use separate databases if at all possible!

If one app goes down, at least the others will still work, with a central database, if one app brings the server down, you're bunched (nothing will work).
Also, for migrations, if you need to, say, migrate one of your apps (size/speed/whatever problems - or you wish to dip your toe into the cloud testing with one app), then you will have difficulty separating out your apps, whereas if you use separate systems, you won't have any difficulties on that score!

If you'd care to explore this in more detail, I have answered questions on this issue before - check them out (1, 2 and 3 and links therein). Obviously cost and the nature of the expertise readily available to you is an issue so it's worth checking out your options before coming to a decision - you

Sometimes the wise heads are indeed on old shoulders! :-)

Another recommendation - use PostgreSQL if at all possible! It is vastly superior to MySQL in a myriad of ways - SQL compliance, JSON, Geospatial, CHECK constraints, SET operators ... long list... you won't regret it!


Write output from dynamic queries in PL/pgSQL to CSV file

There is a massively simpler solution.
SQL code
After removing much of the cruft, it boils down to this:
DO
$do$
DECLARE
   customer_schema text;
BEGIN
   FOR customer_schema IN
      SELECT cname FROM customer
   LOOP
      EXECUTE format('COPY (
         SELECT p.*, t1.*, t2.* -- etc. Or just: *
         FROM        %1$I.portal         p
         LEFT   JOIN %1$I.p_fb_config    t1 USING (pid)
         LEFT   JOIN %1$I.p_gplus_config t2 USING (pid)
         -- etc.
         ) TO $$/absolute/path/to/my/%1$I_file.csv$$;'
       , customer_schema);
   END LOOP;
 END
$do$

Simplify the format() call. Reuse the same parameter many times. Read the documentation here.

No need to stuff the cnames from your customer table into an array for processing. Just use a FOR loop.

Standard techniques like table aliases and the USING clause help to simplify SQL code.

Shell function
Adding that back into your shell function:
PG_CMD_TP="psql -d portal -U portal -t -P format=unaligned -c "
abc()
{
 $PG_CMD_TP '
DO
$do$
DECLARE
   customer_schema text;
BEGIN
   FOR customer_schema IN
      SELECT cname FROM customer
   LOOP
      EXECUTE format($f$COPY (
         SELECT *
         FROM        %1$I.portal         p
         LEFT   JOIN %1$I.p_fb_config    t1 USING (pid)
         LEFT   JOIN %1$I.p_gplus_config t2 USING (pid)
         ) TO $p$/absolute/path/to/my/%1$s_file.csv$p$;$f$
       , customer_schema);
   END LOOP;
 END
$do$';
}

abc

Voilá.

Enclose the whole query in single quotes for the scope of the shell. Unlike with double quotes the shell does not try any substitutions and the string is passed as is (special characters like $ lose their special meaning, too).

Switch to dollar-quoting in the plpgsql code to go along with the outer single quotes in the shell (or escape all single quotes properly).

For a quick solution I named the output files after customer_schema in the example. You may need to sanitize it to be a legal for filename syntax.


Transaction Log Backup Maintenance Plan and Newly Restored Databases

Check out Ola Hallengren's maintenance scripts. They have a @ChangeBackupType parameter that lets you automatically escalate to fulls if necessary.


Project a time range in multiple rows with Postgres

You can use generate_series() for this.

select t.avg_income, d.month
from the_table t
  cross join lateral (
    select g.m::date as month
    from generate_series(date_trunc('month', t.date_from), date_trunc('month', t.date_to), interval '1 month') as g (m)
  ) as d
order by t.avg_income, d.month;

Alternatively:

select t.avg_income, d.month::date
from the_table t
  cross join lateral generate_series(date_trunc('month', t.date_from), date_trunc('month', t.date_to), interval '1 month') as d (month)
order by t.avg_income, d.month;

Online example: http://rextester.com/ADG4243


Both online and offline reorganization are logged and therefore replicated by HADR. By stopping HADR you will simply allow the standby to fall behind the primary, which will cause a large spike in traffic (and potential log space issues on the standby) when you restart HADR. If you don't suffer from network congestion during normal operation, there's no reason to stop HADR during reorganization. If you do, consider dropping the the standby and rebuilding it anew after the REORGs on the primary are complete.


Modeling a database for a printing company

This part in Item doesn't look right:

F.K = JID, OID
  JID REFERENCES PURCHASE (JID)
  OID REFERENCES PURCHASE (OID)

I suppose the design is only on paper, as no DBMS would allow that, when the primary key of PURCHASE is (JID, OID). You need one (composite) Foreign Key constraint, not two:

  (JID, OID) REFERENCES PURCHASE (JID, OID)

Now this foreign key belong to Puschase_Materials (or Purchace_Items) and not to Materials (or Item). With that in mind, the corrected schema would be:

PurchaseOrders (JID, OID, ODate)
PK = (JID, OID)
FK1 = (JID) REFERENCES Jobs (JID)

Items (IID, Stock, Description, Price)
PK = IID

Purchases_Items (IID, JID, OID)
PK = (IID, JID, OID)
FK1 = (IID) REFERENCES Items (IID)
FK2 = (JID, OID) REFERENCES PurchaseOrders (JID, OID)

The image shows an additional relationship, between Purchase_Materials and Jobs/PrintingJobs, which doesn't appear in the schema - and you don't really need it.

Since there is a foreign key from Purchase_Items to PurchaseOrders (or Purchase_Orders in the image) and a foreign key from PurchaseOrders to Jobs, you don't need a direct foreign key. Consistency is enforced through this indirect relationship.

There is inconsistencies in the naming, between the schema and the images. I assumed the following are renames:

Schema         - Image              - Code in this answer
---------------------------------------------------------
Jobs           - PrintingJobs       - Jobs
PurchaseOrders - Purchase           - PurchaseOrders
Item           - Materials          - Items
  (none)       - Purchase_Materials - Purchases_Items

Some table names are plural and others singular.
Some names have underscores between name parts, some not.

It would be best if you fixed those and decide on a naming convention (say all table names are plural, there are no underscores except in case X, etc).


Query runs in serial and parallel mode in two identical databases on the same instance

The cost that is determined by the query optimizer is really, a best guess. The guess is based off of CPU/IO resources the optimizer believes that the query will use. If you want to really get some good information (including query costs) install sp_blitzcache - https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit


How can I alter the primary key of an index-organized table?

An index-organized table (IOT) is just that; an index with no "real" table.  All data besides the primary key are just "tacked on" to the primary key, so for example if you have an IOT with 6 columns, 2 of which make up the primary key, behind the scenes you just have an index with 6 columns, of which the first 2 columns make it unique.

So, sorry, only way to fix it is to recreate the table; to improve the rebuild time temporarily disable logging.  You cannot use an append hint on inserts to an  IOT table to force a direct path insert:

SQL> CREATE TABLE books_new (
    title VARCHAR2(10),
    release_date DATE NOT NULL,
    CONSTRAINT pk_title PRIMARY KEY(title,release_date))
  ORGANIZATION INDEX NOLOGGING;

SQL> INSERT INTO books_new
     (SELECT title, '01-Jan-1980' FROM books);
SQL> ALTER TABLE books_new LOGGING;
SQL> ALTER TABLE books RENAME TO books_old;
SQL> ALTER TABLE books_new RENAME TO books;


Looking up which record(s) correspond to a specific resource description in dm_tran_locks

To find lockers on a particular index you have to use seek on that index explicitly:

select *
from entities WITH (index(unique_entities))
where %%lockres%% like '%7e24236fccb8%'


Is there any point with a combined index on all columns in a table?

The index is a sorted structure - if you need only a sufficiently small portion of the data in the table, it could be fetched from the index more efficiently.

This needs a few prerequisites, though:

PostgreSQL version 9.2 or newer, as index-only scans appeared in this version
the index supports the query (the order of the columns of the index decides this)

Also worth noting that even index-only scans must visit the underlying table (to be precise, its visibility map, and in cases the heap itself) to figure out if the rows to be returned are still visible to the current transaction.  This has to be also considered when determining what 'sufficiently small' means above.


Replicating only significant records to specific database

You should be able to do this with slony. Slony is an asynchronous (but near real-time) trigger based single-master/multi-slave replication solution for PostgreSQL.

Take a look at the "Retail Store Problem" in this presentation (OpenOffice). This example actually deals with simulating a multi-master replication but the trick used can also be applied in the other direction for your use case:

On the master use table inheritance to route your data (according to the id) from the parent table to a slave specific child table. This can be done with insert triggers or rules.
Replicate the slave specific child table(s) to the slave database.
On the slave use table inheritance again to have the same table name in all databases (parent table only wrapper for slave specific child table).

Instead of table inheritance you could also use INSTEAD OF triggers on views with PostgreSQL 9.1. The little ascii art below should give you an idea of the basic structure of this solution.

db_master                          db_slave_dba23       db_slave_dba17

tbl_parent                         tbl_parent           tbl_parent
|                                  ^                    ^
v                                  |                    |
tbl_child_dba23 --- replicate ---> tbl_child_dba23      |
|                                                       |
v                                                       |
tbl_child_dba17 --- replicate ------------------------> tbl_child_dba17
|
v
...

Be aware that while (under certain pre-conditions) slony can well be used as a production grade replication system, such a complex replication solution can get very cumbersome to maintain and monitor. Especially if DDL statements are involved in your daily database use cases this solution may not be suitable at all.

CAVEATS

The replicated tables are read-only on the slaves
Replication is not transactional (but neither is dblink)
Slony can be a real PITA


pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists

The error is harmless but to get rid of it, I think you need to break this restore into two commands, as in:

dropdb -U postgres mydb && \
 pg_restore --create --dbname=postgres --username=postgres pg_backup.dump

The --clean option in pg_restore doesn't look like much but actually raises non-trivial problems.

For versions up to 9.1

The combination of --create and --clean in pg_restore options used to be an error in older PG versions (up to 9.1). There is indeed some contradiction between (quoting the 9.1 manpage):

  --clean
     Clean (drop) database objects before recreating them

and

  --create
   Create the database before restoring into it.

Because what's the point of cleaning inside a brand-new database?

Starting from version 9.2

The combination is now accepted and the doc says this (quoting the 9.3 manpage):

  --clean
  Clean (drop) database objects before recreating them. (This might generate some harmless error messages, if any objects were not present in the destination database.)

  --create
  Create the database before restoring into it. If --clean is also specified,  drop and recreate the target database before connecting to it.

Now having both together leads to this kind of sequence during your restore:

DROP DATABASE mydb;
...
CREATE DATABASE mydb WITH TEMPLATE = template0... [other options]
...
CREATE SCHEMA public;
...
CREATE TABLE...

There is no DROP for each individual object, only a DROP DATABASE at the beginning. If not using --create this would be the opposite.

Anyway this sequence raises the error of public schema  already existing because creating mydb from template0 has imported it already (which is normal, it's the point of a template database).

I'm not sure why this case is not handled automatically by pg_restore. Maybe this would cause undesirable side-effects when an admin decides to customize template0 and/or change the purpose of public, even if we're not supposed to do that.


There are a couple of ways to test it:

Use LOCK TABLE and put it in "exclusive" mode
REVOKE the privilege to INSERT or UPDATE on that table from the test user
REVOKE the privilege to connect to that database
Change or remove TCP/IP access to the database
...

There are many testing scenarios and most of them will result in a different error message.


Write a slow SQLITE query to test timeout

You should create a user-defined function that sleeps for the desired amount of time.

If you are restricted to build-in mechanisms, try using a recursive common table expression:

WITH RECURSIVE r(i) AS (
  VALUES(0)
  UNION ALL
  SELECT i FROM r
  LIMIT 1000000
)
SELECT i FROM r WHERE i = 1;

But the number of iterations needed for 5 s is strongly dependent on the CPU speed.


Table-Valued Functions from Linked Server

Just use a passthrough query. And get rid of NOLOCK (it doesn't even do anything here as the catalog is always read with locking read committed semantics).

EG

exec ('
select *
FROM [master].sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
') at [servername]

or

select *
from openquery (servername, '
select database_id, file_id,  ...
FROM [master].sys.master_files AS f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id)
')


How do I create a query which takes certain time to execute?

As @McNets suggests in his comment, this is really easy using the pg_sleep() function:
select pg_sleep(2);

will return after 2 seconds. And the resulting dataset will contain one row and one column too, so your test application can easily handle it (if it gets through the timeout).


How to most efficiently store possible combinations of categories?

How about the SET datatype.  With that, you could represent all combinations of 14 categories in 14 bits (2 bytes).


How to include values in a MySQL sum based on a conditional statement?

Conditionals can be done by means of the CASE operator/expression:

Your query will work with:

SELECT
    ts,
    (CASE WHEN column_5 > 0
     THEN
        0              /* We are ignoring column_1 */
     ELSE
        column_1       /* We are using its value */
     END) + column_2 + column_3 + column_4 AS total
FROM
    t
ORDER BY
    ts ;

You can check it at http://rextester.com/IHM39024

The CASE expression is standard SQL.

MySQL also offers an IF function that could be used in this case. In fact IF(a,b,c) = CASE WHEN a THEN b ELSE c END.


Database design for payment feature system

The second approach conforms to Third Normal Form (3NF) which is always a better place to start than something which is un-normalized (0NF) like your first option.

Converting repeating columns into rows in another table is perfectly scalable and will help keep your source code more stable because you won't be adding columns to your (option 1) table when you create new modules.

Furthermore, the second option is more powerful because it lets you easily add other information about the purchase of a module by a particular user.  For example you could add purchase_date to your user/module table.  You could also add the actual_purchase_price to that same table.  This would allow you to keep track of things like changes to the price you charge for modules or whether you sold a particular module to a particular user at a promotional price.


The 11.2 manual says you should also check plain LOG_ARCHIVE_DEST. Although it comes from Standard Edition, it is still valid in case none of numbered Enterprise Edition destinations LOG_ARCHIVE_DEST_n are defined. I haven't tested that personally.

In case all are unset, the another 11.2 manual says

  If you configure a Fast Recovery Area (by setting the
  DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE parameters) and
  do not specify any local archive destinations, the database
  automatically selects the Fast Recovery Area as a local archive
  destination and sets LOG_ARCHIVE_DEST_1 to USE_DB_RECOVERY_FILE_DEST.

I vaguely remember I've seen somewhere that 11.1 database in such situation defaults to USE_DB_RECOVERY_FILE_DEST but via LOG_ARCHIVE_DEST_10.

In all, your configuration and syntax is correct.

alter system set log_archive_min_succeed_dest = 1;

Normally all destinations are optional. But your setting is ok; it could somewhat decrease potential data loss (loss of redo log not yet archived).

Further two items to take care of:

a copy of redo logs on a second disk
a copy of control file on a second disk


That is the fastest way in terms of getting the job done. There is no way around it directly as you have to rearrange the table structure for the new column.

If you need to keep read access to the data while the change is made but can live without write access during that time you could:

Create a fresh table with the extra column
Lock the table from updates (set all users to not have the needed permissions for instance)
Copy the data from old to new
Rename the old table to something else
Rename the new table to the old name
Drop the old table

There will still be no access at all between steps 4 and 5, and the whole process will take far longer, and of course you don't have insert/update ability during the process, but the complete outage (including reads) is only steps 4 and 5 not the whole process. As noted by ypercube in the comments, if the affected table has any foreign keys directed toward it these will need modifying and that will cause locks on the affected tables, also when creating the table (or before renaming it) make sure you include any indexes and constraints. Also any triggers operating on the old table will need to be added too (after the data has been copied).

A hacky workaround would be to put the new column(s) in a new table and just join that in to any query that needs them negating the need to modify exiting structures at all, but this means you have to be careful to update both tables correctly when needed and the LEFT OUTER JOIN to pick up the extra values is both more work for you (and other developers on the project now and in future) and more work that the query planner/runner have to do in order to process your queries, so there are going to be performance differences.

It should go without saying: if you intend to try any of the above, as with any structural change (in fact any change!), do so in a testing environment first before touching production databases.

tl;dr: No, there isn't really a quicker way.


MYSQL - Error in After Insert trigger affects the actual insert on the trigger table

The entire trigger chain constitutes one atomic operation inside a transaction -- that's "A" in ACID. So yes, this is working as designed.

If atomicity is not important to you, that is, if you are fine with the fact that table_1 is updated but the insert into table_2 is lost, you need to introduce error handling in your trigger, something along these lines:

create trigger table_1_trig1 after insert on table_1
for each row
begin
 -- no-op handler that just continues;
 -- sqlstate 23000 is the foreign key violation
 declare continue handler for sqlstate '23000' begin end;
 insert into table_2 ...;
end

dbfiddle link

The condition for sqlstate '23000' is the SQL standard equivalent of the MySQL-specific for 1452. If you want to ignore all errors when inserting into table_2 just use for sqlexception instead.

So yes, there is a workaround. However, one should think twice before going that route; if one doesn't care if rows are inserted into table_2 or not, why bother inserting them in the first place? May be take another look at your business logic.

Note that such error handling may not necessarily work in other DBMSes; MySQL treats trigger code much like stored procedures invoked upon DML execution, with all associated overhead.


Determine whether mixed mode auth is enabled without logging in?

You can check it in the registry:

HKLM\Software\Microsoft\Microsoft SQL Server\ [instancename] \MSSqlServer

The current mode is recorded in the LoginMode key.

Values (DWORD) can be:

1 = Windows Authentication mode
2 = SQL Server and Windows Authentication mode

Note:

0 is equivalent to 2
You still need access to the Registry (and the server)


Unable to add db_datawriter to user defined role

JKay, you are a little mistaken about the syntax of this command:

EXEC sys.sp_addrolemember [ @rolename = ] 'role',
    [ @membername = ] 'security_account'

So in your particular case the command is:

EXEC sys.sp_addrolemember @rolename = 'db_datawriter', @membername = 'DR_User';


Log shipping large DBs - what about the log?

If I understand you correctly, your major problem is the log files during the several hours of backup. From your opening statement I understand that the 1.5 TB database normally runs in SIMPLE recovery, and thus no log backups to do.

Disclaimer: I have never done log shipping on this scale.

Of course, you should ask whether you can get more space allocated for your log files.  If you can, then great.

However, I think a small modification of your plan, providing that you already run in SIMPLE recovery model and/or the risk of SIMPLE recovery model for a few hours is worth it, would ease some of your worries.

Keep (or set) DB in SIMPLE recovery model.
Take FULL backup (5-6 hours) on primary
Restore FULL backup to secondary (leaving in NORECOVERY)
Set DB in FULL recovery model
Take DIFF backup on primary
Restore DIFF backup to secondary (still in NORECOVERY)
Initialize log shipping using 'The database is already initialized'

The apparent advantages are:

No log files to backup during the large FULL backup.
Switching to FULL before beginning the DIFF backup will give you the needed log to start with and its longest growth is probably during the DIFF backup.

Regarding when a log backup can start:

https://technet.microsoft.com/en-US/library/ms190729(v=SQL.105).aspx

This says: "A new log chain starts either with the first full database backup following database creation or after switching from the simple recovery model to the full or bulk-logged recovery model."

So, I still believe that this will work as outlined. (Not identical, but I have used a differential backup to cover a gap when log files were lost, so as to establish a new origin for the log backups.)

(Remember my disclaimer, of course.)


SQL Server replication subscriptions marked as inactive

Yes you can do that programatically using t-SQL and a sql agent job.

Basically, you have to query distribution..MSSubscriptions and check the status column value to be 0 (Inactive).

Status of the subscription:
0 = Inactive;
1 = Subscribed;
2 = Active

Below T-SQL Will help you :

-- Author: Kin Shah
-- Date: 4-1-2013
-- For dba.stackexchange.com
-- Good to find out
-- publisher_id
-- publisher_db
-- publication_id
-- subscriber_id
-- subscriber_db
select * From distribution..MSsubscriptions

--- based on the above values, run below statement
--- this can be run using SQLAgent job

if exists (select 1 from distribution..MSsubscriptions where status = 0)
begin
UPDATE distribution..MSsubscriptions
SET STATUS = 2
WHERE publisher_id = '--publisher_id -- will be integer --'
    AND publisher_db = '--publisher db name ---'
    AND publication_id = '--publication_id -- will be integer --'
    AND subscriber_id = '--subscriber_id -- will be integer ---'
    AND subscriber_db = '-- subscriber_db ---'
end
else
begin
print 'The subscription is not INACTIVE ... you are good for now .... !!'
end


Error: pq: 1 columns must reference exactly 1 columns in referenced table (found 2)

For a foreign key, you need to tell Postgres which table you want to refer to and the name(s) of the field(s) within that table that you want to use as the foreign key(s):

CREATE TABLE IF NOT EXISTS item
(
    id              SERIAL,
    restaurant_id   UUID         NOT NULL  REFERENCES restaurant ( id )
    category_id     INT          NOT NULL  REFERENCES category ( id )
    name            VARCHAR(25)  NOT NULL,
    PRIMARY KEY (id, restaurant_id)
);


Shrink a database below its initial size

As some people already mentioned you could create new database and "copy" stuff over from old database. This would be the best option for you. However I've noticed that you want to do it quite regularly. So your best option is Redgate Data Compare and Redgate Compare. Both are part of Redgate SqlToolbelt package.

So what you do:

Create an empty DB with small initial size.
Use Redgate Compare to copy db structure, functions etc from old db
Use Redgate Data Compare to copy data from old database to new one
You work on dev database and then at any time you either do just Data Compare and update the Dev DB regularly, or if you do any changes to db you can deploy those changes using Redgate Compare and then doing Redgate Data Compare.

What is good with Data Compare is that after you copy those 30gb of data (you can do it starting with some tables only) after a while it just needs to 'recompare' only some changes and not whole 30gb of data. Which means it will do a lot less impact on both databases then it would by copying it normally.


From a design perspective, what is the difference between an inventory system and an asset tracking system?

LedgerSMB, which is my main project, does both.  I don't think we will merge these and the explanation why may give you an answer.

The difference you have to understand is that if you are trying to do both, you are probably trying to do accounting on both.  The accounting issues are different and require that different things are tracked.  There is room to centralize some information, but others can't be handled as well.

Let's look at the flow items through both systems.

You buy 10 widgets for inventory for $10 each.  What you have to track depends on how you value your inventory.  If this is FIFO or LIFO, you are going to have to track the financial aspect of the transaction ($100 debit in inventory, $100 credit in payables), and for average cost, you will have to track also the average cost per item.  As an item gets sold then you have to record revenue and receivables but you also move inventory value to your cost of goods sold account.  Modelling this process really defines a usable inventory system.

An asset control system is different.  Suppose you buy 100 desks at $50 each.  You then use these for a minimum of five years and depreciate using some method (many methods are acceptable) over that time.  This example will use straight-line.  To do this properly you also have to track date in service, estimated salvage value (in this case $0 if we intend to donate on disposal), etc.  Then each year we will have a transaction which partially moves that $5000 into expenses ($1000 per year using straight-line).  After that the asset will be fully depreciated but still in service and we will have no further transactions for the part until disposal.  When we dispose even if for $0 on a fully depreciated item, we will still have another financial transaction accounting for this.

I know in LedgerSMB, our asset management side is more complex than our inventory management side.

So your design has a lot less to do with the nature of what is being stored and a lot more to do with the business processes you have to support, the accounting requirements, and so forth.  These mean you have to track different information and expect different flows of stuff through your system.

Hope this helps.  One thing I would highly recommend is that you do one of two things, however.  Either hire a consultant (me! no conflict of interest there ;-) ) or pick up a book on accounting.  If you want to support accounting workflows you want to know the basics enough you can have an intelligent conversation with the folks from finance.  Domain knowledge here really is important because this is an area that looks really weird to the uninitiated.  You don't have to know everything but you should be able to flag issues to talk about with accountants.  I got into this through a book I picked up for $2 at a garage sale.  It's a vast field though and you want to know what human problems you are solving and what business processes are accepted.


Query column A, order by B - is index needed if A and B together are the PK?

No, if you already have an index on (a, b) and the PK constraint means you do, then you don't need anything else for this query.

Separate indexes on (a) and (b) would not be useful.

The only thing that might be better than the index on (a, b) would be a covering index. Say you have a query with only SELECT b, c, d columns in the select list while the table has some 10+ columns. Then, an index on (a, b) INCLUDE (c, d) or in lack of that on (a, b, c, d) would be much narrower than the table and would suit the query perfectly. It would result in a better query plan (only an inde seek, no access to the table needed), on the expense of course that you have an additional index targeted for the specific query.


Error in partition switching in SQL Server 2014 when migrating a database from SQL Server 2005

Actually, I think this is the opposite of a bug. the 2008 document states "Source and target tables must share the same filegroup" but it looks as though they only started paying attention to their rules in 2014. This article (made for 2014 / 2016 CTE) states "The corresponding indexes, or index partitions, must also reside in the same filegroup" TechNet


How to select data in the same table on different rows that are empty?

You explain:

  I get result 1453 only when all items in column attributeY are empty for 1453.

But that's incorrect. Bold emphasis mine. The aggregate function count returns (per documentation):

  number of input rows for which the value of expression is not null

The same is true for SQLite (per documentation):

  The count(X) function returns a count of the number of times that X is
  not NULL in a group.

You obviously have one or more rows with a non-null value in attributeY in your Postgres table - probably an empty string ''.

Test with:

SELECT *
FROM   myschema.table
WHERE  attributeX = 1453
AND    attributeY IS NOT NULL;

Be sure to understand the difference between "empty" ('') and NULL:

Empty strings are character types (typically text, varchar or char) with 0 characters ('') - so basically "nothing", the equivalent of 0 for a numeric data type. NULL is possible for any data type and means "unknown". Some clients have a hard time making the difference clear in their display.

Alternative query

To find attributeX where all attributeY are either empty or NULL use this alternative query:

SELECT attributeX
FROM   myschema.table
GROUP  BY attributeX
HAVING NOT bool_or(attributeY <> '');

The expression attributeY <> '' is only true for non-null, non-empty attributeY. The aggregate function bool_or returns (per documentation):

  true if at least one input value is true, otherwise false

Those are the results we exclude (NOT ...) and return the rest. Voilá.

There are many other (less elegant) ways to achieve the same. Like:

... HAVING count(CASE WHEN attributeY <> '' THEN 1 END) = 0;
... HAVING count(attributeY <> '' OR NULL) = 0;
... HAVING count(NULLIF(attributeY, '') = 0;

More:

For absolute performance, is SUM faster or COUNT?
Replace empty strings with null values


How to read Mongodb entries in plaintext?

You are seeing "plain text" documents right now, in json format. Command db.coll.find() shows all documents (in batch of 20 documents). If you want those word wrapped (easier to read) add .pretty() to end of line

db.coll.find().pretty()

of course all these documents are in bson (binary) format in database and if you want to see that, use mongoexport.

When you insert document, if you don't specify _id, it is added automatically. Rest of document is just like you inserted it.


SQL Server 2016 Always On ReadOnly Routing List

Port 5022 is the endpoint that is used for mirroring, not for client connections.  You just need to connect to the listener on same port as usual (1433 if using the default), and the availability group will route the read-only connection to the secondary.

See Connecting to ReadOnly Secondary Replica of SQL Server via the Listener by Default for further explanation.

To test a read-only connection with SQL Server Management Studio, select Options, and on the Additional Connection Parameters tab enter "ApplicationIntent=ReadOnly").  Then use a SELECT @@SERVERNAME query to confirm the connection is routed to the secondary.


SQL 2012: Executed generated DB script in SQL 2008 R2

This is a warning message that comes up due to a missing dependency (it also means that the SQL dependency tree may not be correctly set, and so you won't have correct entries in sys.sql_expression_dependencies which can be useful down the road). This is just a warning however, and so long as the dependent object is created you will have no issues with running your code.

You can also repush the items you get warnings on, and that will fix the dependency list, but again, not critical.


mysqldump -u username -p databasename table1 table2 table3

or

mysqldump --opt -u username -p databasename table1 table2 table3


Creating trigger from one database table to another database table postgresql

Usually the full qualified name of a db table is:
catalog.schema.table

If you want to insert some values into another table you should use this syntax:
insert into catalog.schema.table
values (..., ..., ...)


separating tlog and db file when using fusionio

Let's go through some of the points.  You mentioned:

  I understand that the tlogs are written to sequentially (the reason it
  is recommended moving them).

It's also for Recoverability

Moving transaction files to a different array isn't just for performance, it's also for recover-ability.  Assume you take a transaction log file backup at 3:45pm and your Fudion IO disk goes bad at 3:55pm before your next transaction log backup.  If you have your data and log files on the same drive you are not able to go in after the crash, take a final transaction log backup thus ensuring almost no data loss (in flight transactions would still be lost of course).

Performance

  I understand that the tlogs are written to sequentially (the reason it
  is recommended moving them). But if IO difference is significant
  between the two drives as it is between traditional drives and a
  FusionIO card, will I still see a benefit from moving the tlogs to a
  different drive? Or will performance be worse?

Without proper benchmarking, you could very well lose performance by isolating your log files from a lightning fast PCI-E FusionIO card and moving them to a SAS6 RAID array with magnetic disks.  Typically moving log files to a different array also increased performance, but in your case it might decrease it.

You should measure the following perfmon counters after moving the log files over:

DISK SEC/WRITE

DISK SEC/READ

If DISK SEC/WRITE on the log file goes over 5MS it's over what MS recommends.  However, if your app doesn't have performance issues with writes, just keep an eye on that counter so you're not caught off guard.

Also,
Transaction Logs are sequential in nature but the second you RAID them or put more than 1 accessed file on the drive the physical IO is no longer sequential thus you lose performance on traditional disks.  All the FusionIO drives I benchmarked did perform better for sequential reads/writes but random IO was sufficient.

Next you mention:

  The database is a typical web-based workload with much more reading
  going on than writing

Typically most apps are much more read intensive than write intensive, which is why common SQL Server benchmarking tools use 8K IOs @ roughly 70% read / 30% write so that part of your setup is fairly common.

  For example, users can upload multiple files with 10,000 or more
  records in each of them.
  If your writes aren't slowed down by moving to the new disk then great, but if you're having scaling issues you should look into BULK LOAD.  This will minimize the log activity.

Real world benchmarking in a test system is the only way I'd go at it personally.  I'd reload a test system with a production workload and see how the drives handle by recording the metrics in SQL Server wait stats and perfmon, then make a decision.  Good luck.


Best design for adding a folder/file relationship

I would to it either like this

       Projets
-----------------------
     project_id
       [....]

       Folder
-----------------------
     folder_id
     parent_id (-1 or the matching parent folder)
     project_id
      [....]

      Media
-----------------------
     media_id
     folder_id
     [....]

This way, you would need to break the relationship for it to work. IF you want to maintain the relationship, I would screw normalisation over, and add a project_id to the Media table as well with proper constraints/trigger so that moving media from one folder to another work without writing the explicit query.

or the other way would be:

       Projets
-----------------------
     project_id
       [....]

       Folder
-----------------------
     folder_id
     parent_id (-1 or the matching parent folder)
      [....]

      Media
-----------------------
     media_id
     folder_id
     project_id
      [....]

You maintain the original relationship and are good to go.


How to use local variable in USE statement

You can't pass a variable to USE. You can do this inside the dynamic SQL, or you can build a string that prepends each object name with the database prefix.

A cleaner dynamic SQL approach than the latter approach, IMHO, since it doesn't require you to inject the database name in front of every object in the dynamic SQL (and also, in this case, allows you to pass @Country as a parameter):

DECLARE @country CHAR(3) = 'UKR';
-- assume above is incoming parameter

DECLARE
  @db SYSNAME = N'DWSource_' + @Country,
  @exec NVARCHAR(300),
  @sql NVARCHAR(MAX);

SET @sql = N'SELECT  @Country as country,
        count(*) as n_ALL,
        sum(case when LEN(EPOSTA)>0 then 1 else 0 end) as EMAIL1,
        sum(case when LEN(EPOSTA_2)>0 then 1 else 0 end) as EMAIL2
FROM    [dbo].[_data_CRM_S_PARTNER]
WHERE   VIR = ''SM''
    AND PRAVNA_OSEBA = 0
    AND PROBLEMATICEN = 0
    AND ISNULL([STATUS],0) IN (0,1)
    AND ID_DBCLUSTER IN (''112'',''122'',''132'',''212'',''222'',
      ''232'',''312'',''322'',''332'',''311'',''321'',''331'')';

SELECT @exec = QUOTENAME(@db) + '.sys.sp_executesql'
EXEC @exec @sql, N'@Country CHAR(3)', @Country;

Both of those things at least somewhat reduce your risk for SQL injection, IMHO. Not that a malicious user could do much with a CHAR(3) but if you use that pattern in other ways with bigger variables, someone could easily pass @Country = '1''; DROP TABLE dbo._data_CRM_S_PARTNER; SELECT '''... and in the other solution this would be a problem (again, if the variable were bigger).

EXEC @exec method quasi-stolen from Erland Sommarskog's great article on dynamic SQL.

That all said, I do agree with Thomas that if you have the same query you want to run in multiple databases, you are better off creating the same stored procedure in each database, and having the app just connect to the desired database in the first place. Since the app has to know which country you're after, it shouldn't be that hard to just generate a connection string based off of that, and call a clean, no-dynamic-SQL stored procedure in that database.


SQL Fastest Way to find Data Difference Between Two Tables

There is no firm answer. David Lozinski conducted a study below, and found different methods succeed depending on row count, statistics, cardinality.

Fastest way to insert new records where one doesn’t already exist

I had an opportunity to test the tablediff utility, for some reason it was performing lot slower (2-3x slower) compared to T-SQL methods below. However it is good for conducting cross-server table differences (originally used in Replication method).


Why is MySQL InnoDB table copied despite using ALGORITHM=INPLACE for ALTER TABLE

From the documentation:

INPLACE: Operations avoid copying table data but may rebuild the table in place. An exclusive metadata lock on the table may be taken briefly during preparation and execution phases of the operation. Typically, concurrent DML is supported.

The table is being rebuilt because the order of the columns is changing. As you said in your question:

[The columns] are not added at the end of table, I use the AFTER clause

If you need to avoid table rebuilds as much as possible, the new columns will need to be appended to the table definition.


SQL/Access: Iterate through a query's results to use in another's WHERE clause

You can do this with a subquery to get your max score per game with the outside query pulling the details of the game.
FYI: This was tested in MS SQL, not MS-Access (sorry don't have that installed), but the syntax should be very similar.

SELECT t.*
FROM tablename AS t
JOIN (
    SELECT GAME, MAX(SCORE) AS MAXSCORE
    FROM tablename
    GROUP BY GAME
    ) AS MAXGAMES
    ON ( t.GAME  = MAXGAMES.GAME
     AND t.SCORE = MAXGAMES.MAXSCORE )
ORDER BY t.GAME ;


"Request to run job dbxxxxx refused" error in merge replication

Try restarting SQL Server Agent service.

(per here)


You'll need to redefine your database to store that information. An example is the following:

CREATE TABLE routes (
  route_id int PRIMARY KEY,
  route_name varchar(200)
  );

CREATE TABLE stops (
  stop_id int PRIMARY KEY,
  stop_name varchar(200)
  );

CREATE TABLE route_details (
  route_id int,
  stop_number int, -- starting from 1
  stop_id int,
  leg_minutes int, -- minutes from the previous stop to this one (0 for stop_number=1)
  FOREIGN KEY (route_id) REFERENCES routes (route_id),
  FOREIGN KEY (stop_id) REFERENCES stops (stop_id),
  PRIMARY KEY (route_id, stop_number)
  );

CREATE TABLE schedules (
  route_id int,
  weekend bool,
  start_time varchar(8)
  );

-- Query which route goes from B to E
SELECT r.*
FROM routes r
JOIN route_details rs ON (rs.route_id = r.route_id)
JOIN stops ss ON (ss.stop_id = rs.stop_id)
JOIN route_details re ON (re.route_id = r.route_id)
JOIN stops se ON (se.stop_id = re.stop_id)
WHERE ss.name = 'B' AND se.name = 'E' AND rs.stop_number < re.stop_number;

When designing a database, I find it convenient sticking to a single naming convention, either plurals or singulars (i.e. routes and stops, or route and stop). Here I opted for all plurals. Also, suffixes work better than prefixes for listing tables alphabetically (it doesn't really matter in this case because there are so few tables, but it still is good practice).

This design allows you to have the same stop shared in different routes. Note I've included a weekend in schedules. This was just a suggestion, since sometimes bus tables have different schedules for weekdays and weekends.

The query would be significantly more complex for finding a route from a stop to another one using two routes, but it can be done. It also allows you easily calculate travel time, and stop times at subsequent bus stops.


Postgresql - UPDATE with different WHERE clasules

It would be helpful if you gave tables and columns, but something like this might work. Basically, move the WHERE into a case statement and set the column equal to itself if the condition is not met.

WITH sub_select(SELECT column1, column2, column3 FROM my_large_table)
UPDATE my_second_table
SET first_col = case when column3 > 1 then column1 else first_col end
, second_col = case when column 3 = 1 then column2 else second_col end
FROM sub_selection;


What are the low hanging fruits of a SQL Server 2014 migration?

In 2014 you get the new cardinality estimator which could see your queries get better plans and therefore execute more efficiently.

A good reason to upgrade from 2008R2 is that it is on extended support meaning it is unlikely to receive anymore updates and only SQL Server 2012 SP2 + versions are able to operate with TLS 1.2 see here


MariaDB replication from master to slave

A1. Use Private IPs where practical -- they are somewhat more secure.  This assumes, however, that both Master and Slave are on the same Private network or is 'tunneled' through.

A2. Create a separate "user" for the replication stream.  GRANT only REPLICATION SLAVE to that user.

Use root (or other SUPER) only for admin purposes.  Create specific user(s) for application code.  Grant them more limited access, perhaps GRANT ALL PRIVILEGES ON app_db.*


Why do we need to rebuild and reorganize Indexes in SQL Server

As you perform inserts updates and deletes, your indexes will become fragmented both internally and externally.

Internal fragmentation is you have a high percentage of free space on your index pages, meaning that SQL Server needs to read more pages when scanning the index.

External fragmentation is when the pages of the index are not in order any more, so SQL Server has to do more work, especially in IO terms to read the index.

If your indexes become too fragmented, at best, your queries will be less efficient but at worst, SQL Server will just stop using the indexes all together, meaning virtually all queries would have to perform a table scan or clustered index scan. This will hurt your performance a lot!

When you reorganise an index, then SQL Server uses the existing index pages and just shuffles data around on those ages. This will alleviate internal fragmentation and can also remove a small amount of external fragmentation. It is a lighter weight operation than rebuild and is always online.

When you rebuild an index, SQL Server actually resorts the data of the index and uses a new set of index pages. This will obviously alleviate both internal and external fragmentation but is a more heavy weight operation and by default causes the index to go offline, although it can be performed as an online operation, depending on your SQL Server version and settings.

Please do not expect to have 0 fragmentation after a Rebuild however. Unless you use a MAXDOP query hint, SQL Server will parallelise the rebuild operation and the more processors involved, the more fragmentation there is likely to be, because each processor or core, will rebuild their section or fragment of the index individually, without regard for each other. This is a trade off between best fragmentation levels and time taken to rebuild the index. For near 0 fragmentation, use MAXDOP 1 and sort the results in TempDB.