How can i branch based on a columns value in oracle?

As per the comment, perhaps something like this:

CREATE OR REPLACE PROCEDURE MYDB.GET_DEAL_LIST
  ( PersonID NUMBER,
    TableName NVARCHAR2,
    BuyerOrSeller INTEGER,
    a_CURSOR OUT sys_refcursor
  )
AS
  v_stmt_str      VARCHAR2(200);
BEGIN
  if BuyerOrSeller = 1 then
    v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ' t on t.ID = TBLDEALS.id and TBLDEALS.Buyer_ID = :PersonID)';
  else
    v_stmt_str := 'SELECT TBLDEALS.*,t.* FROM TBLDEALS inner JOIN ' || tablename || ' t on t.ID = TBLDEALS.id and TBLDEALS.Seller_ID = :PersonID)';
  end if;
  OPEN a_CURSOR FOR v_stmt_str using PersonID;
END;
/


SQL Server function invocation syntax question

The ones without parentheses such as CURRENT_USER and CURRENT_TIMESTAMP are there for ANSI 92 compliance. There are only five such Niladic Functions and the list hasn't changed since SQL Server 6.0.

CURRENT_USER
CURRENT_TIMESTAMP
SESSION_USER
SYSTEM_USER
USER

Most other SQL Server system functions require parentheses.

The exception to this are those functions that used to be known as global variables that are prefixed with @@ such as @@SPID.

This last group of functions can be passed directly in a parameter list to a stored procedure. Neither of the other two variants can.


Authenticating to Oracle REST data services as admin

ok, so i found a solution, seing how apparently my default admin password got messed up

i ran a ' find /oracle/ -iname 'apxchpwd.sql' Search command

once i got the location of the file, i logged in sqlplus as sysdba, then ran @[complete_apxchpwd.sql_path] got prompted for the new password, typed it in, and voila


How to detect any changes to a database (DDL and DML)

One idea would be to make a snapshot every day and monitor the snapshot file size on the disk using a file monitor. The snapshot is increasing its size only when data is added there, so it would be a valid idea if you would find a tool to monitor the real size (reported size).
Now.. I didn't use this, so can't give you technical insights :-).
Another idea would be to verify the transaction log of each db (if you're using full recovery mode on them, of course) with some function I've seen on the forums (db_fnlog.. or something) that reads operations from the log, and see if you have any deletes/inserts/updates.
Those are no easy things to do.. but I hope you'll find them useful.
PS: found the article with the log read function (it's fndblog, by the way :-): Read the transaction log by Jens K. Suessmeyer.


Having trouble with insert query on linked server

So I assume that you already have the queries to insert the data. So the question is really "Can I also check that I m not inserting the same data over and over again?".
There are a couple of ways you can do this. One simple way is to create a constraint on the columns in your table. Let's assume that they are col1,col2 and col3. So you could create a unique constraint on the table like this:
ALTER TABLE table_name
ADD CONSTRAINT Constraint_Name unique(col1,col2,col3)

A composite primary key will also achieve this, but you may already be using the  primary key in a different column.


Try this (in an INNER JOIN you don't necessary have to reference previous table):

select p_d.price
 from b_d_s inner join b_d  on b_d_s.b_d_id = b_d.id
            inner join s_d  on b_d_s.s_d_id = s_d.id
            inner join p_d  on b_d.p_d_id   = p_d.id
 where s_d.id = 1


Percona/MySql Table does not exist after innobackupex restore on Vagrant Ubuntu 16.04

I figured out the answer if anyone else is reading this question. I think it's a bug with either vagrant or virtual box, basically, rebooting the virtual machine fixed the issue. Its like the restore data didn't get fully saved to disk and wasn't available until after the reboot.. I would be interested if anyone had a similar issue with virtual box or vagrant...


How to start mysqld with read-only data directory?

Please note the InnoDB Architecture (Pictorial Representation by Percona CTO Vadim Tkachenko)

The system tablespace file ibdata1 has moving parts requiring writes

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs

It's good you have --innodb-read-only set to InnoDB handle read-only situations.

You also need to make sure innodb_change_buffering is 0.

You also needed to make sure that crash recovery never happens. Here is how to set  it up.

First, with the database on a writeable media, login to MySQL and run this:

mysql> set global innodb_fast_shutdown = 0;

Then, go to the OS and run

service mysql stop

With innodb_fast_shutdown enabled, this cause mysqld to purge everything and its grandmother out of the InnoDB plumbing upon shutdown.

Next, move the datadir to the readonly media.

Then, add this to my.conf

[mysqld]
innodb_change_buffering = 0
innodb_read_only = 1

Finally, go start mysql with service mysql start and InnoDB should start very quickly because there will be no need to perform crash recovery since everything was purged out of the InnoDB plumbing beforehand.


You are confusing 2 things in your question. Since you state you have duplicate attribute key errors you are using the the age table as a dimension, but since you also want to find averages you are using the age table as a fact table.

When using ages as a dimension you probably don't want every single age on your axis, so what is typically done is divide the ages in buckets and have the buckets stored as strings so you get something like this

+-------+--------+
|  Age  |  sales |
+-------+--------+
| <10   |      0 |
| 10-20 |    100 |
| 21-30 |    250 |
| 31-40 |    124 |
| 41-50 |   3598 |
| > 50  |    715 |
+-------+--------+

You could either use discretization buckets in your dimension or create the buckets manually in your dimension table.

However if you want to average ages too, I suppose you want to see the average across some dimensions (such as average age of people ordering product X) so you need to create a fact table with the keys of the dimensions you want to report across and then define relationships between your fact table and dimension tables

Mixing the 2 concepts (dimensions and facts) in the same table will lead to headaches over time so you should either solve that in your ETL process or using named queries in your dmv in SSAS.


Users, roles, and permissions can get kind of confusing in PostgreSQL. To control who can try to connect to the database, Postgres uses the pg_hba.conf file. In Ubuntu, it's buried in /etc/postgres/<#>/main.

From inside the data base there are roles which control permissions each user has inside the database.

An easy, but probably insecure way to connect is to set up trust from localhost connections in pg_hba.conf.


Algorithm for finding the longest prefix

I am assuming data type text for the relevant columns.

CREATE TABLE prefix (code text, name text, price int);
CREATE TABLE num (number text, time int);

"Simple" Solution

SELECT DISTINCT ON (1)
       n.number, p.code
FROM   num n
JOIN   prefix p ON right(n.number, -1) LIKE (p.code || '%')
ORDER  BY n.number, p.code DESC;

Key elements:

DISTINCT ON is a Postgres extension of the SQL standard DISTINCT. Find a detailed explanation for the used query technique in this related answer on SO.
ORDER BY p.code DESC picks the longest match, because '1234' sorts after '123' (in ascending order).

Simple SQL Fiddle.

Without index, the query would run for a very long time (didn't wait to see it finish). To make this fast, you need index support. The trigram indexes you mentioned, supplied by the additional module pg_trgm are a good candidate. You have to choose between GIN and GiST index. The first character of the numbers is just noise and can be excluded from the index, making it a functional index in addition.
In my tests, a functional trigram GIN index won the race over a trigram GiST index (as expected):

CREATE INDEX num_trgm_gin_idx ON num USING gin (right(number, -1) gin_trgm_ops);

Advanced dbfiddle here.

All test results are from a local Postgres 9.1 test installation with a reduced setup: 17k numbers and 2k codes:

Total runtime: 1719.552 ms (trigram GiST)
Total runtime: 912.329 ms (trigram GIN)

Much faster yet

Failed attempt with text_pattern_ops

Once we ignore the distracting first noise character, it comes down to basic left anchored pattern match. Therefore I tried a functional B-tree index with the  operator class text_pattern_ops (assuming column type text).

CREATE INDEX num_text_pattern_idx ON num(right(number, -1) text_pattern_ops);

This works excellently for direct queries with a single search term and makes the trigram index look bad in comparison:

SELECT * FROM num WHERE right(number, -1) LIKE '2345%'

Total runtime: 3.816 ms (trgm_gin_idx)
Total runtime: 0.147 ms (text_pattern_idx)

However, the query planner will not consider this index for joining two tables. I have seen this limitation before. I don't have a meaningful explanation for this, yet.

Partial / functional B-tree indexes

The alternative it to use equality checks on partial strings with partial indexes. This can be used in a JOIN.

Since we typically only have a limited number of different lengths for prefixes, we can build a solution similar to the one presented here with partial indexes.

Say, we have prefixes ranging from 1 to 5 characters. Create a number of partial functional indexes, one for every distinct prefix length:

CREATE INDEX prefix_code_idx5 ON prefix(code) WHERE length(code) = 5;
CREATE INDEX prefix_code_idx4 ON prefix(code) WHERE length(code) = 4;
CREATE INDEX prefix_code_idx3 ON prefix(code) WHERE length(code) = 3;
CREATE INDEX prefix_code_idx2 ON prefix(code) WHERE length(code) = 2;
CREATE INDEX prefix_code_idx1 ON prefix(code) WHERE length(code) = 1;

Since these are partial indexes, all of them together are barely larger than a single complete index.

Add matching indexes for numbers (taking the leading noise character into account):

CREATE INDEX num_number_idx5 ON num(substring(number, 2, 5)) WHERE length(number) >= 6;
CREATE INDEX num_number_idx4 ON num(substring(number, 2, 4)) WHERE length(number) >= 5;
CREATE INDEX num_number_idx3 ON num(substring(number, 2, 3)) WHERE length(number) >= 4;
CREATE INDEX num_number_idx2 ON num(substring(number, 2, 2)) WHERE length(number) >= 3;
CREATE INDEX num_number_idx1 ON num(substring(number, 2, 1)) WHERE length(number) >= 2;

While these indexes only hold a substring each and are partial, each covers most or all of the table. So they are much larger together than a single total index - except for long numbers. And they impose more work for write operations. That's the cost for amazing speed.

If that cost is too high for you (write performance is important / too many write operations / disk space an issue), you can skip these indexes. The rest is still faster, if not quite as fast as it could be ...

If numbers are never shorter then n characters, drop redundant WHERE clauses from some or all, and also drop the corresponding WHERE clause from all following queries.

Recursive CTE

With all the setup so far I was hoping for very elegant solution with a recursive CTE:

WITH RECURSIVE cte AS (
   SELECT n.number, p.code, 4 AS len
   FROM   num n
   LEFT    JOIN prefix p
            ON  substring(number, 2, 5) = p.code
            AND length(n.number) >= 6  -- incl. noise character
            AND length(p.code) = 5

   UNION ALL
   SELECT c.number, p.code, len - 1
   FROM    cte c
   LEFT   JOIN prefix p
            ON  substring(number, 2, c.len) = p.code
            AND length(c.number) >= c.len+1  -- incl. noise character
            AND length(p.code) = c.len
   WHERE    c.len > 0
   AND    c.code IS NULL
   )
SELECT number, code
FROM   cte
WHERE  code IS NOT NULL;

Total runtime: 1045.115 ms

However, while this query isn't bad - it performs about as good as the simple version with a trigram GIN index - it doesn't deliver what I was aiming for. The recursive term is planned once only, so it can't use the best indexes. Only the non-recursive term can.

UNION ALL

Since we are dealing with a small number of recursions we can just spell them out iteratively. This allows optimized plans for each of them. (We lose the recursive exclusion of already successful numbers, though. So there is still some room for improvement, especially for a wider range of prefix lengths)):

SELECT DISTINCT ON (1) number, code
FROM  (
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 5) = p.code
            AND length(n.number) >= 6  -- incl. noise character
            AND length(p.code) = 5
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 4) = p.code
            AND length(n.number) >= 5
            AND length(p.code) = 4
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 3) = p.code
            AND length(n.number) >= 4
            AND length(p.code) = 3
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 2) = p.code
            AND length(n.number) >= 3
            AND length(p.code) = 2
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 1) = p.code
            AND length(n.number) >= 2
            AND length(p.code) = 1
   ) x
ORDER BY number, code DESC;

Total runtime: 57.578 ms (!!)

A breakthrough, finally!

SQL function

Wrapping this into an SQL function removes the query planning overhead for repeated use:

CREATE OR REPLACE FUNCTION f_longest_prefix()
  RETURNS TABLE (number text, code text) LANGUAGE sql AS
$func$
SELECT DISTINCT ON (1) number, code
FROM  (
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 5) = p.code
            AND length(n.number) >= 6  -- incl. noise character
            AND length(p.code) = 5
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 4) = p.code
            AND length(n.number) >= 5
            AND length(p.code) = 4
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 3) = p.code
            AND length(n.number) >= 4
            AND length(p.code) = 3
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 2) = p.code
            AND length(n.number) >= 3
            AND length(p.code) = 2
   UNION ALL
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(number, 2, 1) = p.code
            AND length(n.number) >= 2
            AND length(p.code) = 1
   ) x
ORDER BY number, code DESC
$func$;

Call:

SELECT * FROM f_longest_prefix_sql();

Total runtime: 17.138 ms (!!!)

PL/pgSQL function with dynamic SQL

This plpgsql function is much like the recursive CTE above, but the dynamic SQL with EXECUTE forces the query to be re-planned for every iteration. Now it makes use of all the tailored indexes.

Additionally this works for any range of prefix lengths. The function takes two parameters for the range, but I prepared it with DEFAULT values, so it works without explicit parameters, too:

CREATE OR REPLACE FUNCTION f_longest_prefix2(_min int = 1, _max int = 5)
  RETURNS TABLE (number text, code text) LANGUAGE plpgsql AS
$func$
BEGIN
FOR i IN REVERSE _max .. _min LOOP  -- longer matches first
   RETURN QUERY EXECUTE '
   SELECT n.number, p.code
   FROM   num n
   JOIN   prefix p
            ON  substring(n.number, 2, $1) = p.code
            AND length(n.number) >= $1+1  -- incl. noise character
            AND length(p.code) = $1'
   USING i;
END LOOP;
END
$func$;

The final step cannot be wrapped into the one function easily.
Either just call it like this:

SELECT DISTINCT ON (1)
       number, code
FROM   f_longest_prefix_prefix2() x
ORDER  BY number, code DESC;

Total runtime: 27.413 ms

Or use another SQL function as wrapper:

CREATE OR REPLACE FUNCTION f_longest_prefix3(_min int = 1, _max int = 5)
  RETURNS TABLE (number text, code text) LANGUAGE sql AS
$func$
SELECT DISTINCT ON (1)
       number, code
FROM   f_longest_prefix_prefix2($1, $2) x
ORDER  BY number, code DESC
$func$;

Call:

SELECT * FROM f_longest_prefix3();

Total runtime: 37.622 ms

A bit slower due to required planning overhead. But more versatile than SQL and shorter for longer prefixes.


MySQL 100% CPU Usage Reading from a 500.000 Rows table

How much RAM?  Many of the entries in my.cnf look "too big" unless you have a lot of RAM.

Please use SHOW CREATE TABLE; it is more descriptive than DESCRIBE.

InnoDB would be faster because the data is clustered with the PRIMARY KEY.

What is happening...  Each run of that query is scanning half the index, starting with IPLONG=ip_from and going to the end of the index.  Meanwhile, it is reaching over to the data (since it is separate in MyISAM) for the other columns.

Are the ip_from..ip_to pairs non-overlapping?  If so, you could check one row, then quit.  If they are overlapping, there is no hope.

The LIMIT 1 will not be sufficient without ORDER BY ip_from.

You realize that the table cannot handle IPv6?

Make sure that country_code is CHARACTER SET ascii.  Don't include country_name in the same table; build a lookup (normalization) table mapping country_code to country_name.


MySQL uses range and where on UPDATE with the primary key

So I would have to look at the query optimizer source code of why it is printing range, but my guess is that for writes it will just use a limited number of write methods for simplification/limitation of EXPLAIN. If we look at the actual low row level operations of both queries, we can see that the SELECT:

MariaDB [db]> flush status; SELECT `BuyPacker_Email`.`emailOpen` = 1567007592 FROM `BuyPacker_Email` WHERE `BuyPacker_Email`.`emailId` = 17040352; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.000 sec)
1 row in set (0.000 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     | <----
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_delete         | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
27 rows in set (0.001 sec)

... does a single row read through and index. And for the update:

MariaDB [db]> FLUSH STATUS; UPDATE `BuyPacker_Email` SET `BuyPacker_Email`.`emailOpen` = 1567007592 WHERE `BuyPacker_Email`.`emailId` = 17040352; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)
Rows matched: 1  Changed: 0  Warnings: 0

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 2     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     | <-----
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_delete         | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 1     |  <----
| Handler_write              | 0     |
+----------------------------+-------+
27 rows in set (0.000 sec)

... it uses the exact same read strategy + the required write to update the record.

So there is no overhead in terms of reading more records than necessary or any difference in terms of more expensive read plans.
Whether it is a simplification  for EXPLAIN, or a limitation due to write queries, where it cannot go too far as to tell; or it shows a real difference because how writes work, and marks a real performance impact, one would need to go into implementation details- but for most cases, as long as the rows column says "1", I don't see any problems with the query- a range of 1 row should be almost equivalent to a const access, broadly speaking.


change column type from string to numeric

I'm ignorant of postgres SQL syntax, however this appears to be an issue with either the TYPE numeric(10,0) not containing decimal granularity (numeric(10,2)) would work, or the fact that the currency is in a european format that utilizes commas instead of decimals.


What does MySQL mean by "Major Version", and is MySQL replication supported between 5.1 and 5.6?

I've confirmed this is a bug with MySQL/Oracle Support. They will update the public tickets when the internal fixes are ready.

To summarize: MySQL replication is only supported between one release level (the second digit) and the next; e.g. between MySQL 5.1 and 5.5; 5.5 and 5.6 but not between MySQL 5.1 and 5.6. The documentation is mistaken when it says "MySQL supports replication from one major version to the next higher major version."* They mean release level or release series number, not major version. That is, unless they change the terminology in the future.

Replication between 5.1 and 5.6 might work, but it's not supported and not well tested. It's best to for replication servers to stay within one release level of each other, and to use the latest version of your 5.x release.

The bug reports are:

"Section should be more clear about 5.5 and 5.6 versions". Now, the documentation is more clear:

  MySQL supports replication from one release series to the next higher
  release series. For example, you can replicate from a master running
  MySQL 5.5 to a slave running MySQL 5.6, from a master running MySQL
  5.6 to a slave running MySQL 5.7, and so on.

A second private bug which I'll add once the bug is made public.


I have five(5) aspects to discuss here

ASPECT #1 : innodb_buffer_pool_size

You need to run this query

SELECT CEILING(SUM(data_length+index_length)/power(1024,3)) BPSIZE
FROM information_schema.tables WHERE engine='InnoDB';

This will give you the ideal sized buffer pool because InnoDB caches data and index pages.

If the DB Server has 32G RAM, the biggest buffer pool would be 75% of the Machine (which would be 24G) or BPSIZE, whichever is smaller. In other words, for 32G RAM, if BPSIZE is 10G, then use 10G. If BPSIZE is30G, use 24G.

ASPECT #2 : InnoDB Log Buffer

Setting the size of the Log Buffer can be a dream come true or your worst nightmare. How?

Dream Come True

The MySQL Documentation on innodb_log_buffer_size says the following:

  The size in bytes of the buffer that InnoDB uses to write to the log files on disk. The default value is 8MB. A large log buffer enables large transactions to run without a need to write the log to disk before the transactions commit. Thus, if you have big transactions, making the log buffer larger saves disk I/O.

Your Worst Nightmare

According to the Book

Page 428 Paragraph 8 says the following:

  The InnoDB storage engine logs information about current transactions in a memory buffer. When a transaction commits or rolls back, the log buffer is flushed to disk. If the log buffer is small, it might fill up before the end of the transaction, requiring a flush to the log file before the outcome of the transaction is known. For a committed transaction, this results in multiple disk operations rather than one. For a rolled-back transaction, it results in writes that, with a larger buffer, would not need to be made at all. To set the size of the log buffer, use the innodb_log_buffer_size option. The default is 1MB. Typical values range from 1MB to 8MB. Values larger than 8MB are of no benefit.

This excerpt is for MySQL 5.0.13. InnoDB has matured a lot since then, but the principle remains the same: Setting innodb_log_buffer_size too high can have two adverse affects:

It can waste memory for low write environments or very small transactional changes
Produce much longer commits

Lesson Learned

Look at the default innodb_log_buffer_size

5.0 (Default: 1M)
5.1 (Default: 1M)
5.5 (Default: 8M)
5.6 (Default: 8M)

You will have to experiment with larger values, perhaps 16M, 32M, 64M, or 128M.

Just remember

the lower innodb_log_buffer_size is set, the more frequent larger transactions may flush
the higher innodb_log_buffer_size is set, the longer commits may take

So, don't crazy with the values. Be as conservative as possible but look for reduced disk I/O you desire.

ASPECT #3 : Dirty Pages in the Buffer Pool

90% by default on MySQL 5.0/5.1
75% by default on MySQL 5.5/5.6

You should set this to 90 regardless of the version of MySQL. The higher it is, the more delay on flushing for heavy write loads. Given the increased performance of MySQL 5.5/5.6, this may not be necessary.

ASPECT #4 : Disk Issues

Run this query:

SHOW VARIABLES LIKE 'innodb_file_per_table';

If you get OFF for innodb_file_per_table, you need to Cleanup the InnoDB Infrastructure. I wrote post back on October 29, 2010 on how to do this.

With regard to having InnoDB Data sitting on a different disks, this may reduce Disk I/O but at the risk of reduced overall InnoDB Performance. Why ? Back on Feb 06, 2012, I wrote about why spreading InnoDB on different disks is not all that performant.

What is also noteworthy is this regard is the system tablespace ibdata1. Every time you access an InnoDB tables, the data dictionary is always consulted. Thus, having spread out tables onto other disk does not negate accessing ibdata1 back in datadir. Accessing two disks would always be in order.

InnoDB has many moving parts in ibdata1:

Data Dictionary
Double Write Buffer

Safety Net to Prevent Data Corruption
Helps Bypass OS for Caching

Insert Buffer (Streamlines Changes to Secondary Indexes)
Rollback Segments
Undo Logs
Click Here to see a Pictorial Representation of ibdata1

Expect the Highest I/O around ibdata1. In light of this, whatever disk tuning you do, give weight to the location of ibdata1.

ASPECTS #5 : Transaction Flushing

I am not a big fan of setting  innodb_flush_log_at_trx_commit to 0 or 2 since it does the following:

relaxes ACID compliance
risks 1 second of data loss in the event of a crash
puts more faith and trust in the OS and hardware for flush disk changes

EPILOGUE

Please keep this foremost in mind: Your goal here should be to get InnoDB to cache transactional changes as much as possible and flush to disk as little as possible. This will certainly curtail disk I/O without expending effort on hardware too quickly. Only after the needed due diligence fails to reduce disk I/O should you explore hardware.


This DBA.StackExchange answer gives a good place to start tracing the issue.
Specifically the linked Microsoft Docs - Plan Caching in SQL Server 2008 docs (albeit a little dated, but still useful) have a section called Plan stability-related reasons of batch recompilations that details all the reasons the plan cache can be cleared out (you should also read the previous section Causes of Recompilations for some useful info).
There are too many reasons to quote from the above article but the one section I find particularly interesting and might be the easiest place to start is the following:

The following operations flush the entire plan cache, and therefore,
cause fresh compilations of batches that are submitted the first time
afterwards: Detaching a database, Upgrading a database to SQL Server
2005, Upgrading a database to SQL Server 2008, Restoring a database, DBCC
FREEPROCCACHE command, RECONFIGURE command, ALTER DATABASE ... MODIFY
FILEGROUP command, Modifying a collation, using ALTER DATABASE … COLLATE
command

In my experience, I've personally seen developers schedule commands such as DBCC FREEPROCCACHE to run routinely and cause similar issues. (Additionally I've seen overuse of the OPTION (RECOMPILE) and WITH RECOMPILE clauses all throughout a database causing similar issues as well.)
If you can eliminate the above checklist then you should follow the rest of the guide in the Plan stability-related reasons of batch recompilations docs, maybe looking next at how often you guys are running DDL queries that would cause plan cache recompilation, and then how often are you guys updating statistics across the board.
It's unfortunately going to be an elimination game most likely and won't be a trivial issue to solve, but best of luck.


hosting images on own database or 3rd party

The most common solution is, as commented by @Akina, to store images in the file system, and just store some kind of reference (file path or similar) in the database that allows you to uniquely identify the image in the file system.

However, there are also use cases where it makes sense to store the images directly in the database. Maybe you don't have permissions to store files on the file system. Maybe you have a lot of RAM and you don't expect the images to take up all that much space, and putting the files in the database means you only have to backup the database and not have to worry about a separate backup of the file system.

One of the big disadvantages of storing the images in the database is obviously that they can make your database grow to an unmaintainable size where backups take a long time and you may have to take special steps to avoid disrupting users (i.e. to avoid locking) for very long while running the backup. Maybe you can also no longer fit the whole database in RAM, which will lead to slowness. In addition, images usually require BLOB columns, and these come with their own set of disadvantages.


pg_dump fails to create a backup when running from System User in Windows Task Scheduler

I found a work around for the problem. I had to change the run as user to the current logged in user after first setting it to SYSTEM. This will prompt the user to enter the password. This change doesn't remove the radio selection on "Run whether the user is logged on or not" and pg_dump works with the current user. The nice thing is, the code runs silently. The code I added under the schedule task creator batch script is the follow.

SchTasks /Change /TN "Database Backup" /RU "%username%" /RP *


My guess is that this would fix your query:

SELECT *
FROM   location
WHERE     to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw'))
ORDER  BY to_tsvector('simple',unaccent2(city))
       @@ to_tsquery('simple',unaccent2('wroclaw')) DESC
         ,displaycount
LIMIT  20;

I repeat the WHERE condition as first element of the ORDER BY clause - which is logically redundant, but should keep the query planner from assuming it would be better off processing rows according to the index location_displaycount_index - which turns out to be much more expensive.

The underlying problem is that the query planner obviously grossly misjudges the selectivity and / or cost of your WHERE condition. I can only speculate why that is.

Do you have autovacuum running - which should also take care of running ANALYZE on your tables? Thereby, are your table-statistics up-to-date? Any effect if you run:

ANALYZE location;

And try again?

It can also be that the selectivity of the @@ operator is being misjudged. I would imagine that it is very hard to estimate for logical reasons.

If my query should not solve the problem, and generally to verify the underlying theory do one of these two things:

temporarily delete the index location_displaycount_index
temporarily disable basic index scans by running:

SET enable_indexscan = OFF;

The latter is less intrusive and only affects the current session. It leaves the methods bitmap heap scan and bitmap index scan open, which are used by the faster plan.
Then re-run the query.

BTW: If the theory is sound, your query (as you have it now) will be much faster with a less selective search term in the FTS condition - contrary to what you might expect. Try it.


Efficient way to see what pattern matches a single word

Without knowing anything about what your actual scenario looks like, I'd perhaps do something like:

USE tempdb;

IF OBJECT_ID(N'dbo.Strings', N'U') IS NOT NULL
DROP TABLE dbo.Strings;
CREATE TABLE dbo.Strings
(
    TargetString varchar(46) NOT NULL
);

IF OBJECT_ID(N'dbo.Matches', N'U') IS NOT NULL
DROP TABLE dbo.Matches;
CREATE TABLE dbo.Matches
(
    PossibleMatch varchar(46) NOT NULL
);

INSERT INTO dbo.Strings(TargetString)
VALUES ('Master');

INSERT INTO dbo.Matches(PossibleMatch)
VALUES ('m%r')
    , ('abc%')
    , ('zyx%')
    , ('a%bcd')

SELECT *
FROM dbo.Strings s
    INNER JOIN dbo.Matches m ON s.TargetString LIKE m.PossibleMatch

╔══════════════╦═══════════════╗
║ TargetString ║ PossibleMatch ║
╠══════════════╬═══════════════╣
║ Master       ║ m%r           ║
╚══════════════╩═══════════════╝

You'll get a better set of answers if you more accurately describe exactly what your scenario is by explaining how the data is designed, and what you exact design goals are.

To see if this methodology is effective enough, I've extended the MCVE above by adding a couple of indexes and a bunch of data:

The tables now have clustered indexes:

IF OBJECT_ID(N'dbo.Strings', N'U') IS NOT NULL
DROP TABLE dbo.Strings;
CREATE TABLE dbo.Strings
(
    TargetString varchar(46) NOT NULL
        PRIMARY KEY CLUSTERED
);

IF OBJECT_ID(N'dbo.Matches', N'U') IS NOT NULL
DROP TABLE dbo.Matches;
CREATE TABLE dbo.Matches
(
    PossibleMatch varchar(46) NOT NULL
        PRIMARY KEY CLUSTERED
);

This inserts 50,000 words into the Strings table:

;WITH src AS (
    SELECT v.val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(val)
)
, src26 AS
(
    SELECT TOP(26) Num = (v.val * 10) + v1.val
    FROM src v
        CROSS JOIN src v1
    ORDER BY v.val, v1.val
)
, words AS
(
    SELECT c1 = CHAR(65 + s1.num)
        , c2 = CHAR(65 + s2.num)
        , c3 = CHAR(65 + s3.num)
        , c4 = CHAR(65 + s4.num)
        , c5 = CHAR(65 + s5.num)
    FROM src26 s5
        CROSS JOIN src26 s4
        CROSS JOIN src26 s3
        CROSS JOIN src26 s2
        CROSS JOIN src26 s1
)
INSERT INTO dbo.Strings(TargetString)
SELECT TOP(50000) words.c1
    + words.c2
    + words.c3
    + words.c4
    + words.c5
FROM words
ORDER BY CRYPT_GEN_RANDOM(5);

A sample of the rows from that table:

╔══════════════╗
║ TargetString ║
╠══════════════╣
║ UKMBL        ║
║ ASOCG        ║
║ XWACG        ║
║ NHRXQ        ║
║ LFMBR        ║
║ EQELO        ║
║ PRHCM        ║
║ IIFPD        ║
║ OCLJQ        ║
║ NJBMB        ║
╚══════════════╝

Next, we'll create 1,000,000 rows in the Matches table with wildcards:

;WITH src AS (
    SELECT v.val
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9))v(val)
)
, src26 AS
(
    SELECT TOP(26) Num = (v.val * 10) + v1.val
    FROM src v
        CROSS JOIN src v1
    ORDER BY v.val, v1.val
)
, words AS
(
    SELECT c1 = CHAR(65 + s1.num)
        , c2 = CHAR(65 + s2.num)
        , c3 = CHAR(65 + s3.num)
        , c4 = CHAR(65 + s4.num)
        , c5 = CHAR(65 + s5.num)
    FROM src26 s5
        CROSS JOIN src26 s4
        CROSS JOIN src26 s3
        CROSS JOIN src26 s2
        CROSS JOIN src26 s1
)
INSERT INTO dbo.Matches(PossibleMatch)
SELECT TOP(1000000) REPLACE(words.c1, 'A', '_')
    + REPLACE(words.c2, 'E', '_')
    + REPLACE(words.c3, 'I', '_')
    + REPLACE(words.c4, 'O', '_')
    + REPLACE(words.c5, 'U', '%')
FROM words
WHERE NOT (words.c1 = 'A' AND words.c2 = 'E' AND words.c3 = 'I' AND words.c4 ='O' AND words.c5 <> 'U');

SELECT TotalMatches = (SELECT COUNT(1) FROM dbo.Matches)
    , TotalStrings = (SELECT COUNT(1) FROM dbo.Strings)

Here's a sample of those rows:

╔═══════════════╗
║ PossibleMatch ║
╠═══════════════╣
║ LDBFB         ║
║ IWLMB         ║
║ FNBUA         ║
║ _ZEVC         ║
║ BSZDB         ║
║ TTRIB         ║
║ XVLNA         ║
║ VLPDB         ║
║ UI_IA         ║
║ FNLCB         ║
╚═══════════════╝

As you can see from the sample above, some of the rows have a T-SQL single-character wildcard, _, and some rows don't have wildcards.

Now, we'll run some code to see if we can find a row from Matches that matches up to a row in Strings:

SELECT *
FROM dbo.Strings s
    LEFT JOIN dbo.Matches m ON s.TargetString LIKE m.PossibleMatch
WHERE s.TargetString = (
    SELECT TOP(1) Strings.TargetString
    FROM dbo.Strings
    ORDER BY CRYPT_GEN_RANDOM(5)
    )

The WHERE clause randomly chooses a word from the Strings table.  You may need to run this several times before you get a word with matching values in the Matches table.

Looking up any given word in the Strings table, with associated Matches:

SELECT *
FROM dbo.Strings s
    LEFT JOIN dbo.Matches m ON s.TargetString LIKE m.PossibleMatch
WHERE s.TargetString = 'JRZPC'

The plan for this query:

On my system, this query executes in ~100 milliseconds.

The results of that query:

╔══════════════╦═══════════════╗
║ TargetString ║ PossibleMatch ║
╠══════════════╬═══════════════╣
║ JRZPC        ║ ___PC         ║
║ JRZPC        ║ __Z_C         ║
║ JRZPC        ║ __ZPC         ║
║ JRZPC        ║ _R__C         ║
║ JRZPC        ║ _R_PC         ║
║ JRZPC        ║ _RZ_C         ║
║ JRZPC        ║ _RZPC         ║
╚══════════════╩═══════════════╝

If you were going to run hundreds or thousands of these queries per minute, you might want to re-think the strategy, but that's putting the horse before the cart.

If you want to implement this as a function, I'd consider this:

CREATE FUNCTION dbo.FindMatches
(
    @TargetString varchar(46)
)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
(
    SELECT m.PossibleMatch
    FROM dbo.Matches m
    WHERE @TargetString LIKE m.PossibleMatch
);
GO

That code creates a schema-bound table-valued-function that gets "inlined" with the target query, and is about as efficient as you can get for this type of thing.

This would be an example of how you'd call the function:

SELECT s.TargetString
    , fm.PossibleMatch
FROM dbo.Strings s
    CROSS APPLY dbo.FindMatches(s.TargetString) fm
WHERE s.TargetString = 'AMLAA'


Will moving the MATCH...AGAINST part in different part of the query (e.g. before evaluation of JOIN) will improve the performance?

I am glad you came here. I have always suggested using FULLTEXT indexes to collect keys first and then join it to other tables.

Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jul 18, 2012 : Why full-text-search returns less rows than LIKE
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Jan 29, 2012 : Mysql fulltext search my.cnf optimization
Aug 01, 2016 : How to make a FULLTEXT search with ORDER BY fast?

In your particular case, I would suggest rewriting your query as an all-out JOIN:

SELECT t.* FROM tracking t
INNER JOIN tracking_items ti ON t.id = ti.tracking_id
INNER JOIN (SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
ON ti.tracking_object_id = c.car_id LIMIT 5;

or maybe

SELECT t.* FROM
(SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
INNER JOIN tracking_items ti ON c.car_id = ti.tracking_object_id
INNER JOIN tracking t ON ti.tracking_id = t.id  LIMIT 5;

If these queries give you an idea, please incorporate it if you still want to use WHERE EXISTS.

Please run the explain plan on these queries and your and see the differences

EXPLAIN select `t`.*
from `tracking` `t`
where exists
(
    select `ti`.`id` from `tracking_items` `ti`
    inner join `cars` `c` on (`c`.`car_id` = `ti`.`tracking_object_id`)
    where `t`.`id` = `ti`.`tracking_id` and match(`c`.`car_text`)
    against('+bulgaria +turkey' in boolean mode)
    limit 1
) and ... limit 5;

EXPLAIN SELECT t.* FROM
(SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
INNER JOIN tracking_items ti ON c.car_id = ti.tracking_object_id
INNER JOIN tracking t ON ti.tracking_id = t.id LIMIT 5;

EXPLAIN SELECT t.* FROM tracking t
INNER JOIN tracking_items ti ON t.id = ti.tracking_id
INNER JOIN (SELECT car_id,car_text FROM cars WHERE MATCH(car_text)
AGAINST ('+bulgaria +turkey' in boolean mode)) c
ON ti.tracking_object_id = c.car_id LIMIT 5;

Give it a try, and let us know what happens !!!


Why changing the column in the ORDER BY section of window function "MAX() OVER()" affects the final result?

The explanation for the different results is given in SQL Server's documentation about window functions, the ORDER BY section:

ORDER BY
Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.
If it is specified, and a ROWS/RANGE is not specified, then default
RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for
window frame by the functions that can accept optional ROWS/RANGE
specification (for example min or max).

Note that MIN() and MAX() window aggregates accept an optional ROWS or RANGE specification.
When there is no such specification, they calculate the MIN and MAX over the whole partition. When there is, they calculate the MIN or MAX over the specified range. Since your two queries specify different orders/ranges, they yield different results.
If you wanted the MAX over the whole partition, then remove the ORDER BY range:

If it is not specified, the default order is ASC and window function will use all rows in partition.


David Spillett's answer is correct in all points, except for the "encouraging" suggestion.

Here's a way to not only encourage but (in almost all versions) force the optimizer to choose a plan that uses the wanted index to find the 50 rows - and only after that perform the join. It can't always be used but the FOREIGN KEY constraint assures that in this case the two queries will produce identical results.
I call this technique "first LIMIT, then JOIN":

SELECT     p.*, ps.*
FROM       ( SELECT     *
             FROM       productStore
             ORDER BY   storeTitle
             LIMIT 50
           ) ps
    INNER JOIN product p
        ON p.productUUID = ps.productUUID
ORDER BY   ps.storeTitle ;


Get all dates from an interval that are missing from other intervals

If a hotel can never have overlapping seasonal periods, then it is possible to obtain the result as a list of intervals by direct matching of intervals using the method below.

First you need to invert the seasonal period list, which means getting the list of gaps between the items of the list complementing it with two more intervals representing the period before the first item and the period after the last item. In other words, a list like this:

date_from    date_to
----------  ---------
date_from1   date_to1
date_from2   date_to2
.
.
.
date_fromN   date_toN

would be transformed to a list like this:

   date_from            date_to
-----------------   ------------------
   0001-01-01        date_from1 - 1 day
date_to1   + 1 day   date_from2 - 1 day
date_to2   + 1 day   date_from3 - 1 day
.
.
.
date_toN-1 + 1 day   date_fromN - 1 day
date_toN   + 1 day      9999-12-31

The date_fromi and date_toi in the second list refer to the corresponding values from the first list. The one-day adjustments are there to account for the fact that both the input intervals and the output intervals are inclusive.

This is how you could generate the list of gaps in SQL based on your schema:

SELECT
  IFNULL(
    (
      SELECT
        hs_last.date_to + INTERVAL 1 DAY
      FROM
        hotel_season AS hs_last
        INNER JOIN hotel_season_period AS hsp_last
          ON hs_last.id = hsp_last.hotel_season_id
      WHERE
        hs_last.hotel_id = hs_this.hotel_id
        AND hsp_last.date_from < hsp_this.date_from
      ORDER BY
        hsp_last.date_from DESC
      LIMIT
        0, 1
    ),
    CAST('0001-01-01' AS date)
  )                                      AS date_from,
  hs_this.date_from - INTERVAL 1 DAY     AS date_to
FROM
  hotel_season AS hs_this
  INNER JOIN hotel_season_period AS hsp_this
    ON hs_this.id = hsp_this.hotel_season_id
WHERE
  hs_this.hotel_id = @param_hotel_id

UNION ALL

SELECT
  MAX(hs_this.date_to) + INTERVAL 1 DAY  AS date_from,
  CAST('9999-12-31' AS date)             AS date_to
FROM
  hotel_season AS hs_this
  INNER JOIN hotel_season_period AS hsp_this
    ON hs_this.id = hsp_this.hotel_season_id
WHERE
  hs_this.hotel_id = @param_hotel_id
;

Note that adjacent seasonal periods will generate "gaps" for which date_from > date_to. Such ranges would be invalid, of course, and you would need to filter them out before further processing.

The further processing will involve finding the gaps intersecting with the hotel contract period. Those that do intersect with it will actually be the intervals that you want to return, except the you may need to adjust the beginning of the first matching gap as well as the end of the last one, because those might go outside the contract period bounds.

This is the matching condition, which takes into account that both the contract period and the gaps are inclusive intervals:

gap.date_to >= contract.date_from AND gap.date_from <= contract.date_to

That is, a gap is considered as intersecting the contract period when its end either comes after or exactly matches the contract beginning while at the same time the gap's beginning either comes before or exactly matches the contract's end.

As I have already said, the first and the last matching gap may go partly outside the contract period, i.e. like this:

contract:    [                 ]
first gap: [    ]
last gap:                    [    ]

For the output you will want to modify them like this:

contract:    [                 ]
first gap:   [  ]
last gap:                    [ ]

This means you will need to calculate both the beginning and the end for each gap: take the latest between gap.date_from and contract.date_from as the beginning and the earliest between gap.date_to and contract.date_to as the end:

GREATEST(gap.date_from, contract.date_from)   AS date_from,
LEAST   (gap.date_to  , contract.date_to  )   AS date_to

Putting everything together, this is what you get:

SELECT
  GREATEST(gap.date_from, contract.date_from)   AS date_from,
  LEAST   (gap.date_to  , contract.date_to  )   AS date_to
FROM
  list_of_gaps AS gap
  CROSS JOIN contract
WHERE
  contract.company_id = @param_hotel_id
  AND gap.date_to >= contract.date_from
  AND gap.date_from <= contract.date_to
;

For the list_of_gaps you can directly use the first query as a derived table:

...
FROM
  (
    SELECT
      IFNULL(
    .
    .
    .
  ) AS gap
...

Or you can store that query's results in a temporary table first:

CREATE TEMPORARY TABLE tmp_gap_list
AS
SELECT
  IFNULL(
.
.
.

and use the temporary table as the list_of_gaps instead:

SELECT
  GREATEST(gap.date_from, contract.date_from)   AS date_from,
  LEAST   (gap.date_to  , contract.date_to  )   AS date_to
FROM
  tmp_gap_list AS gap
.
.
.

The latter option – doing the job in two distinct steps – might be faster, depending on whether MySQL will find the single-query solution too complex to come up with an efficient plan, and also depending on the amount of data in your tables. Test both options for yourself to choose the one that works best for you.


Why choose a top query and temporary table instead of a cursor for a loop?

No, a loop is not necessarily faster than a cursor, though some people are more comfortable with them. I went through the loop writing phase myself at one time.  Also cursors come in several flavors, so choosing the right type of cursor is an important detail.

Your question is probably answered by Aaron Bertrand (from 2012), since he ran several comparative tests, at:

https://sqlblog.org/2012/01/26/bad-habits-to-kick-thinking-a-while-loop-isnt-a-cursor

In short, he found that the proper cursor ran significantly faster than several loop constructs.

Reading through his comparisons should help you feel comfortable with cursors.

But only when they are needed.


It turns out that the Import wizard ( because it is considered like a Bulk Insert or BCP ) is disabling replication triggers on the tables they are loading data to, which prevents Merge Replication from detecting new records.

This article: Bulk loading data into a merge replicated table explains this with more details.

One workaround is to use the procedure: sp_addtablecontents after importing data to let the merge replication detect the new rows like the following:

Exec sp_addtabletocontents @table_name = 'Customers'


May be this will help.

with topicdet as
(select t.topic_id as topic,c.conf as configuration,t.parent_topic_id as parenttopic from topic t left outer join config c on t.topic_id=c.topic_id)
select a.topic, NVL(a.configuration,CONNECT_BY_ROOT configuration) as conf from topicdet a
connect by nocycle prior a.topic=a.parenttopic;

if you dont want to use hierarchical query then

with topicdet as
(select t.topic_id as topic,c.conf as configuration,t.parent_topic_id as parenttopic from topic t left outer join config c on t.topic_id=c.topic_id)
select a.topic,case when a.configuration is null then (select b.configuration from topicdet b where b.parenttopic=a.topic) else a.configuration end as config from topicdet a


This is your original query

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM director_movie as d LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId
WHERE d.castName LIKE '%castname%'

There are three things you can do

SUGGESTION #1

You should reorganize the query so that the castName is searched first

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId;

SUGGESTION #2

Run these please

SELECT COUNT(1) WithDistinctCount FROM
(SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId) A;
SELECT COUNT(1) WithoutDistinctCount FROM
(SELECT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE castName LIKE '%castname%') d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId) A;

If WithDistinctCount and WithoutDistinctCount are the same, then remove DISTINCT. It may actually return the results faster.

SUGGESTION #3 (Optional)

If you implement my FULLTEXT suggestion (Joining two Mysql VIEWs takes a very long time), you could redo subquery d

SELECT DISTINCT d.movieName, d.castName, d.movieImdbId, f.year, f.posterLink
FROM (SELECT movieName, castName, movieImdbId
FROM director_movie WHERE MATCH(castName) AGAINST ('+castname' IN BOOLEAN MODE)) d
LEFT JOIN film_info as f ON d.movieImdbId = f.ImdbId;

This requires you create a FULLTEXT index on director_movie

ALTER TABLE director_movie ADD FULLTEXT castname_ft_ndx (castname);

GIVE IT A TRY !!!


Parsing COPY's binary format to access a tsrange

As a minor note, COPY .. (WITH BINARY) doesn't have brackets. It's the flags (which represent amongst other things the brackets).

COPY ... (WITH BINARY)

From the docs on COPY

  To determine the appropriate binary format for the actual tuple data you should consult the PostgreSQL source, in particular the *send and *recv functions for each column's data type (typically these functions are found in the src/backend/utils/adt/ directory of the source distribution).

Further the docs say binary format (currently) has

11 bytes signature
4 bytes for flags
4 byte potential variable-width field, currently not in use so we skip the size (4 byte \0\0\0\0) this technically not nice. If these four bytes had 15, we'd have to skip over not just the four, but an addition 15.

Then the tuple has

2 bytes for field count

Then the fields have

4 byte length qualifier followed by that many bytes of field data. (Which we already in the case of timestampor tsrange)

So essentially we skip 25 bytes to get to the first column

The tsrange

So it's in the format specified by range_send You can see that explained a bit below in the comments above range_recv

  Binary representation: The first byte is the flags, then the lower bound (if present), then the upper bound (if present).  Each bound is represented by a 4-byte length header and the binary representation of that bound (as returned by a call to the send function for the subtype).

The timestamp subtype

In your case, that subtype is timestamp and the send is  timestamp_send.

You can see a timestamp is stored as 8 bytes, and that's just sent with a simple pq_sendint64 (a 64 bit/8 byte int). You'll have to read how timestamp_recv works to see how you should handle a binary representation of a timestamp. Hint: it gets into a struct for in-memory representation in timestamp2tm

/* timestamp2tm()
 * Convert timestamp data type to POSIX time structure.
 * Note that year is _not_ 1900-based, but is an explicit full value.
 * Also, month is one-based, _not_ zero-based.
 * Returns:
 *   0 on success
 *  -1 on out of range

I'm not going to more-entertain this here, but maybe next go around.

Playing around

We try first DEADBEEFin it which an isolation to track the marker 8 byte marker.

psql -d test -c 'COPY ( SELECT E'\''DEADBEEF'\'' ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=25 --endian big --read-bytes=8 -c

Now we swap that out..

psql -d test -c 'COPY ( SELECT $$2010-01-01 14:30$$::timestamp without time zone ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=25 --endian big --read-bytes=8 --format=d8 -x

Result: paren-comments added.

0000031      315671400000000  (timestamp in int8)
         0001 1f19 f9a9 aa00  (hex representation)
0000041

And that's your number for the first time zone. For tsrange as per the above section we have

one byte for on the interval
upper: 4 bytes (header) + 8 bytes (timestamp)
lower: 4 bytes (header) + 8 bytes (timestamp)

So to access the first internal timestamp, we skip an addition 5 bytes, on top of already 25 bytes of skipage for 30 bytes total.

psql -d test -c 'COPY ( SELECT $$[2010-01-01 14:30, 2010-01-01 15:30)$$::tsrange ) TO STDOUT WITH ( FORMAT BINARY );' |
  od --skip-bytes=30 --endian big --read-bytes=8 --format=d8 -x

This gives us the same result as above..

0000036      315671400000000
         0001 1f19 f9a9 aa00
0000046

Simply change --skip-bytes to 42 to skip over that 8 byte time stamp, and the next 4 bytes of header for the lower and you'll get another time stamp.


SQL Server collecting SQL statements using particular index

the following query will find all the queries that are using that particular index at realtime

  select  i.object_id,
     i.index_id, i.name,
     tqp.query_plan,pl.plan_handle, pl.start_time,pl.estimated_completion_time,q.text
  from sys.indexes as i
    cross apply (select quotename(i.name) as name) as i2
    cross apply sys.dm_exec_requests as pl
    cross apply sys.dm_exec_query_plan(pl.plan_handle) AS p
    CROSS APPLY sys.dm_exec_text_query_plan(pl.plan_handle, DEFAULT, DEFAULT) tqp
    cross apply sys.dm_exec_sql_text(pl.plan_handle) q
    where i.name = 'YourIndex'


Any possible way to encrypt SQL Server 2012 database or backup?

There are plenty of ways to utilize encryption in SQL Server 2012.  Here is an article that goes into further detail:
http://sqlmag.com/database-security/sql-server-encryption-options

Note, transparent data encryption is only available in Enterprise edition.
https://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx


Use sql*loader to load the data :

http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_params.htm#sthref540

(or external tables).

1) setup a .ctl file with your file format

2) setup / point to a receiving table in your database.

3) invoke sql*loader (sqlld)


SQL Server: ODBC Linked Server to Oracle does not return all rows

Microsoft fixed this issue in Cumulative Update 4 for SQL Server 2014.

https://support.microsoft.com/help/2993262/

If you are unable to install the update, they provide a workaround:

  To work around this issue, use a nested loops join or a hash join by
  using the Option command instead of the merge join.


What are the best Database Analytics Companies for analyzing several 1000s or Mio. of products,sum them together and analyze them for statistics?

I'm not sure how you would do this with a nosql database like mongodb but in a typical data warehouse I would do this with the dimension table hierarchy.

Product_ID   Full_Product_Nm            Product_Lvl1       Product_Lvl2
----------   ---------------            --------------     ------------
1            iPad 16GByte UMTS schwarz  iPad               iPad 16gb
2            iPad 16GB 3G black         iPad               iPad 16gb
3            iPad 64GByte UMTS schwarz  iPad               iPad 64gb
4            iPad 64GB 3G black         iPad               iPad 64gb

Then I could join my fact table to the dimension and pull out the hierarchy level that I would like to report on.

So in your example I could pull out the Product_Lvl1 to get iPad but if I wanted to compare sales/inventory of 64gb compared to the 16gb iPad I could do that using the Product_Lvl2.


Why does this derived table improve performance?

I can give you a partial answer that explains why you are seeing the performance difference - though that still leaves some open questions (such as can SQL Server produce the more optimal plan without introducing an intermediate table expression that projects the expression as a column?)

The difference is that in the fast plan the work needed to parse the JSON array elements and create the Geography is done 4 times (once for each row emitted from the openjson function) - whereas it is done more than 100,000 times that in the slow plan.

In the fast plan...

geography::Point(
                convert(float,json_value(value,'$[0]'))
               ,convert(float,json_value(value,'$[1]'))
               ,4326)

Is assigned to Expr1000 in the compute scalar to the left of the openjson function. This corresponds to geo in your derived table definition.

In the fast plan the filter and stream aggregate reference Expr1000. In the slow plan they reference the full underlying expression.

Stream aggregate properties

The filter is executed 116,995 times with each execution requiring an expression evaluation. The stream aggregate has 110,520 rows flowing into it for aggregation and creates three separate aggregates using this expression. 110,520 * 3 + 116,995 = 448,555. Even if each individual evaluation takes 18 microseconds this adds up to 8 seconds additional time for the query as a whole.

You can see the effect of this in the actual time statistics in the plan XML (annotated in red below from the slow plan and blue for the fast plan - times are in ms)

The stream aggregate has an elapsed time 6.209 seconds greater than its immediate child. And the bulk of the child time was taken up by the filter. This corresponds to the extra expression evaluations.

By the way.... In general it is not a sure thing that underlying expressions with labels like Expr1000 are only calculated once and not re-evaluated but clearly in this case from the execution timing discrepancy this happens here.


Can we commit the transaction when the sql server trigger fails?

The Question mentions that the "code is failing" but there is no indication of any error message or what specifically is failing. Including at least one, if not both, of those pieces of information always helps get better answers.
For the moment, I see something that appears to be an incorrect assumption about Triggers and Transactions: you increment the @@TRANCOUNT by calling BEGIN TRAN; but only decrement @@TRANCOUNT when there is no error and the COMMIT TRAN; line executes within the TRY block. In the case of an error, the COMMIT is skipped and a ROLLBACK of the Save Point occurs. But rolling-back a Save Point does not decrement @@TRANCOUNT in which case the INSERT operation ends and the Transaction is still active.
Triggers exist within an internally started Transaction that binds it to the DML operation that fired the Trigger. This is how you are able to call ROLLBACK within a Trigger to cancel that DML operation.
Option 1 (prevent the error in the first place — PREFFERED if the error condition can be reliably tested for)
(Added 2020-10-21: Not sure how or why I didn't think of this option when I posted this answer)
If at all possible, if the condition(s) that cause the error can be tested for, then best to test for that condition prior to attempting the operation that sometimes fails. If the error never actually happens, then you don't need to alter default behavior and/or add custom transaction handling. In your case (i.e. adding the new record(s) to the archive table), you might could do one of the following:
SET NOCOUNT ON;

BEGIN TRY

    INSERT INTO dbo.Archive
        SELECT *
        FROM   inserted ins
        WHERE  NOT EXISTS (SELECT *
                           FROM   dbo.Archive arc
                           WHERE  arc.[Login] = ins.[Login])
                 -- assuming [Login] field exists and should be unique
END TRY
BEGIN CATCH
    DECLARE @DoNothing INT;
END CATCH;

There are times when this won't catch everything and there still might be the occasional violation, in which case you might still need to also use Option 2. But, even if using Option 2 and it always works, still best to attempt preventing the error because work had to be done in order to arrive at the condition causing the error, and that is just wasted time, IO, contention, etc.
Option 2A (prevent errors from cancelling the Transaction — PREFERRED if there are multiple DML statements in the Trigger)
With this in mind, you should be able to remove the BEGIN TRAN; and COMMIT TRAN; lines in order to get this working. The net-effect will be that if there is no error, the INSERT into the Archive table will commit as expected, but if there is an error, it will do the ROLLBACK to the Save Point and carry on.
HOWEVER, after removing those two pieces, you are still left with the sticky situation of getting the following error:

Msg 3931, Level 16, State 1, Procedure Customer_Insert_Trigger_Test, Line XXXXX
The current transaction cannot be committed and cannot be rolled back to a savepoint. Roll back the entire transaction.

The reason for this behavior seems to be an implicit setting of XACT_ABORT ON by the system as it calls the Trigger. The effect of XACT_ABORT ON is to cancel the Transaction (and the query batch) for most errors (except compilation errors or from RAISERROR). The remedy? Just set XACT_ABORT OFF at the beginning of the Trigger.
For example, the following works for me:
CREATE
--ALTER
TRIGGER [dbo].[Customer_Insert_Trigger_Test]
   ON  [dbo].[Inserts]
   AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT OFF;

BEGIN TRY
    PRINT '@@TRANCOUNT = ' + CONVERT(VARCHAR(10), @@TRANCOUNT); -- for debug only
    SAVE TRANSACTION InsertSaveHere;

    -- Simulating error situation
    DECLARE @Error INT = 1 / 0; -- runtime error

   -- Insert into Archive select * from Inserted;
END TRY
BEGIN CATCH
    PRINT 'Entering CATCH block...'; -- for debug only
    ROLLBACK TRANSACTION InsertSaveHere;
END CATCH;

END;

Please note that this method does not alter the expected behavior of Triggers on this table with respect to: 1) the actual COMMIT happening at the outer-most layer (either the initial DML statement or beyond that if an explicit Transaction had been started prior to that statement), 2) the ability of other potential Triggers on this table from issuing a ROLLBACK to cancel the operation, and 3) the ability of an explicit Transaction, started prior to the DML statement on this table, from issuing a ROLLBACK to cancel all changes including the DML operation on this table.
Option 2B (prevent errors from cancelling the Transaction — PREFERRED if there is a single DML statement in the Trigger)
Of course, if the only thing that could error here is the INSERT into the Archive table, then you could probably also get rid of the SAVE TRAN and ROLLBACK TRANSACTION InsertSaveHere; and just do something in the CATCH block so that it isn't empty, something like DECLARE @Test INT; might work. The reasoning here is that a single DML statement that errors never really happened, so there is nothing to roll-back ;-).

Option 3 (NOT PREFERRED)
To answer the Question as stated in the title: you should be able to COMMIT within the Trigger, but I would be eXtreeemely cautious about doing such a thing as it alters the expected behavior of when the Transaction will Commit or Rollback, which might prevent proper operation of other Triggers on this table (they won't be able to issue a ROLLBACK to cancel the operation if this Trigger runs first), and would prevent intended operation of an explicit Transaction started prior to the DML operation on this table.
To do this (NOTE: you need to have read the paragraph directly above before continuing to read this paragraph), you would issue a COMMIT TRAN; (since the Trigger already exists within a Transaction), and then execute a BEGIN TRAN;. The COMMIT TRAN; will commit the initial DML operation, and the BEGIN TRAN; will put the @@TRANCOUNT back to 1 so that when the Triggers execution ends, you don't get the error stating that the Trigger ended with a different @@TRANCOUNT than it had when it began.


How to find out what caused a schema change?

You can use a DDL trigger to log schema changes.

CREATE DATABASE AuditDB;
GO

USE AuditDB;
GO

CREATE TABLE dbo.DDLEvents
(
    EventDate    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    EventType    NVARCHAR(64),
    EventDDL     NVARCHAR(MAX),
    EventXML     XML,
    DatabaseName NVARCHAR(255),
    SchemaName   NVARCHAR(255),
    ObjectName   NVARCHAR(255),
    HostName     VARCHAR(64),
    IPAddress    VARCHAR(32),
    ProgramName  NVARCHAR(255),
    LoginName    NVARCHAR(255)
);

Then in your database, you can add events to a DDL trigger:

CREATE TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,
        CREATE_TABLE,     ALTER_TABLE,     DROP_TABLE,
        CREATE_VIEW,      ALTER_VIEW,      DROP_VIEW
        --, etc etc
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @EventData XML = EVENTDATA();
    DECLARE @ip VARCHAR(32) = (SELECT client_net_address
        FROM sys.dm_exec_connections WHERE session_id = @@SPID);

    INSERT AuditDB.dbo.DDLEvents
    (
        EventType,
        EventDDL,
        EventXML,
        DatabaseName,
        SchemaName,
        ObjectName,
        HostName,
        IPAddress,
        ProgramName,
        LoginName
    )
    SELECT
        @EventData.value('(/EVENT_INSTANCE/EventType)[1]',   'NVARCHAR(100)'),
        @EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'),
        @EventData,
        DB_NAME(),
        @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]',  'NVARCHAR(255)'),
        @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]',  'NVARCHAR(255)'),
        HOST_NAME(),
        @ip,
        PROGRAM_NAME(),
        SUSER_SNAME();
END
GO

The advantage over audit is that this does not require Enterprise Edition. The advantages over the default trace are that (a) you can control which events you capture and (b) you can keep your history as long as you want (the default trace rolls over).

This is culled from a tip I wrote here.


What causes log shipping copy error : "The process cannot access the file because it is being used by another process."?

Found a post discussing how to address this issue :


Struggling to create an efficient index for this query

If you where to go the route of plan guides as @DenisRubashkin mentioned, you could create an 'empty' plan guide to remove the existing hints: OPTION(OPTIMIZE for UNKNOWN, FAST 50).

An example of a plan guide you could use, you might have to change some datatypes.

EXEC sp_create_plan_guide
    @name =  N'Guide1',
    @stmt = N'SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID"
FROM test.dbo."Record Link" WITH(READUNCOMMITTED)
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6)))
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)',
    @type = N'SQL',
    @params = '@0 int, @1 int, @2  [nvarchar](30), @3  [nvarchar](30), @4 tinyint, @5 nvarchar(264),@6 nvarchar(264)',
    @hints = NULL;

@hints will override the existing hints, by not specifying any, OPTION(OPTIMIZE for UNKNOWN, FAST 50) is removed and no other hints are used.

In my tests, while my data is different, this index was used

CREATE INDEX IX_Notify_Company_Link_ID
ON [dbo].[Record Link](Notify,Company,[Link ID])
INCLUDE([To User ID]);

This could not be the most optimal index for your dataset! Used solely as a way to show that the plan guide works. YMMV

A seek predicate on both Notify and Company where used.
They are not very selective in my dataset, resulting in many rows read.

Part of the new plan

PasteThePlan

Comment by @PaulWhite

  You might find an addition benefit in parameter embedding using OPTION
  (RECOMPILE) in the plan guide.

You would have to change the @hints parameter in the plan guide to

    @hints = 'OPTION(RECOMPILE)';

Keep in mind that your query plan will be recompiled each time, but if it is not executed that frequently it should not be a problem.

Plan with option recompile

A better plan was used, with seek predicates on all 3 key colums.

PasteThePlan

Test Query used

SET STATISTICS IO, TIME ON;
EXEC SP_EXECUTESQL N'SELECT  TOP (@0) "timestamp","Link ID","Record ID","URL1","URL2","URL3","URL4","Description","Type","Created","User ID","Company","Notify","To User ID"
FROM test.dbo."Record Link" WITH(READUNCOMMITTED)
WHERE ("Link ID">@1 AND (("Company"=@2 OR "Company"=@3) AND "Notify"=@4 AND ("To User ID" COLLATE Latin1_General_100_CI_AI LIKE @5 OR "To User ID" COLLATE Latin1_General_100_CI_AI LIKE @6)))
ORDER BY "Link ID" ASC OPTION(OPTIMIZE for UNKNOWN, FAST 50)',N'@0 int, @1 int, @2  [nvarchar](30), @3  [nvarchar](30), @4 tinyint, @5 nvarchar(264),@6 nvarchar(264)',@0=100,@1 = 99 ,@2  = 'NNNNNNNV',@3 = 'NNNNNNN',@4=1,@5='NNNNNNN1',@6='NNNNNNN2'

Test Data used

CREATE TABLE [dbo].[Record Link](
    [timestamp] [timestamp] NOT NULL,
    [Link ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [Record ID] [varbinary](448) NOT NULL,
    [URL1] [nvarchar](250) NOT NULL,
    [URL2] [nvarchar](250) NOT NULL,
    [URL3] [nvarchar](250) NOT NULL,
    [URL4] [nvarchar](250) NOT NULL,
    [Description] [nvarchar](250) NOT NULL,
    [Type] [int] NOT NULL,
    [Note] [image] NULL,
    [Created] [datetime] NOT NULL,
    [User ID] [nvarchar](132) NOT NULL,
    [Company] [nvarchar](30) NOT NULL,
    [Notify] [tinyint] NOT NULL,
    [To User ID] [nvarchar](132) NOT NULL,
 CONSTRAINT [Record Link$0] PRIMARY KEY CLUSTERED
(
    [Link ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET NOCOUNT ON;
DECLARE @I INT = 0;
WHILE @I <= 10000
BEGIN
INSERT INTO [dbo].[Record Link]
(

    [Record ID]   ,
    [URL1]    ,
    [URL2]    ,
    [URL3]    ,
    [URL4]    ,
    [Description]    ,
    [Type]  ,
    [Note]  ,
    [Created]   ,
    [User ID]   ,
    [Company]   ,
    [Notify]   ,
    [To User ID]   )
    VALUES(convert(varbinary(448),'NNNNNNN'),'NNNNNNN','NNNNNNN','NNNNNNN','NNNNNNN','NNNNNNN',@I,'NNNNNNN',GETDATE(),'NNNNNNN'+CAST(@i as nvarchar(10)),'NNNNNNN',1,'NNNNNNN'+CAST(@i as nvarchar(10)))
    SET @I += 1
    END
    SET NOCOUNT OFF
INSERT INTO [dbo].[Record Link]
(

    [Record ID]   ,
    [URL1]    ,
    [URL2]    ,
    [URL3]    ,
    [URL4]    ,
    [Description]    ,
    [Type]  ,
    [Note]  ,
    [Created]   ,
    [User ID]   ,
    [Company]   ,
    [Notify]   ,
    [To User ID]   )

    SELECT
     [Record ID]   ,
     [URL1] ,
     [URL2]    ,
     [URL3]    ,
     [URL4]    ,
     [Description] ,
     [Type]  ,
     [Note]  ,
     [Created]   ,
     [User ID]   ,
     [Company]   ,
     [Notify]   ,
     [To User ID]
     FROM   [dbo].[Record Link]
     GO 7


Remove all instances of a character from a column within tag

Short answer - yes, it could be done

Correct and full answer depends from other information

For single (or predefined number of tags) - You can prepare UPDATE SQL for XML nodes and use REPLACE T-SQL function for updated parameters

The is will work if You need remove all ";" from TAG, if logic more complicated - solution will not work.


Performing projection first and then selection?

Not with row-oriented storage, where the column values are stored together per rows, which is not ideal for starting with projection.

Lets say Table1 has 30 columns, but you need data only from 2 (username, usertype). If username and usertype columns are not indexed, the DBMS has to read the full table with all rows (thus read all columns). In this case the only order to do this, is selection, then projection.

If username and usertype are part of a composite index, the DBMS will decide to get the data from this index instead of reading the whole table itself. The index will have most likely less columns than the table, but it is still not the projection you are looking for. Indexes are still organized for row-oriented storage, first selection happens based on usertype='Manager', then the projection.

However, there are some other DBMSs, that use column-oriented storage. Column-oriented DBMS. In these systems, whole columns are stored after each other, which makes it really easy to start with the projection. If you need only 2 columns from 30, the database can just simply read that 2 columns, and do the selection.This kind of system performs better, when running low selectivity queries, that want to access most, or all the rows from a table. Overall performance depends on the kind of workload.


There are two blogs you should look at.  The first (here) is a comparison of Table Variables and Temp Tables.  It is from 2008 and remains relevant for SQL Server 2008 R2.

The second blog entry (here) deals with some of the misconceptions (missed-conceptions) regarding Table Variables; including indexing a Table Variable.

Both of these blog entries, articles, are written by Gail Shaw.

I believe the one telling item you are looking for is a Table Variable may not be passed between stored procedures while a Temp Table is able to be passed between stored procedures.  The closest a Table Variable comes to passing between stored procedures is as a User-Defined Table Type.

Passing a Table Variable as a TVP to a stored procedure has the requirement it must be defined as a READ-ONLY parameter.  This implies there will be multiple copies of the Table Variable if you want to return a modified copy of it.

All told, passing between stored procedure may be better served by a Temp Table when you want to return the passed-in structure with updated values.


I do not understand SQL Server Transaction Logs

Whenever I make a change to a table, does it get written to both the
  database file AND the transaction log file?

It is written to both, but it is written differently to each. The changes are made to the data pages in memory and are eventually flushed to disk via the checkpoint process. the changes are sent to a log buffer and hardened to disk at some point, though before the data file is written to disk to keep with the write ahead logging protocol.

  Furthermore: When I do a backup of the transaction log file, does it
  clear ALL logs? Or does it just remove some?

A log backups allows VLFs of the log to me marked for reuse if nothing is actively depending upon them. Nothing is physically cleared, just marked to allowed to be reused.


How to Select Average from many Column with extrawhere from all Column?


Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch?

I would go "CRS / ASM / RDBMS 10.2.0.1 and then patch to 10.2.0.5 in the same order" just to be sure I follow the most typical way that others went before 10.2.0.5 became available. But it's just me, your first proposition will work as well.

Both alternatives guarantee that at all times:

CRS version >= DB version
CRS version >= ASM version


False positive error message in MS Access (concurrent users)

"Another user is currently using this record" is a misleading error message Access loves to throw out - many times it really indicates you have the table open in another window.


How to quantify or locate command timeouts in Azure SQL

Is there an method to locate query timeouts in Azure?

Here are some links:

Monitor Azure SQL Database using Azure SQL Analytics (Preview)
https://docs.microsoft.com/en-us/azure/azure-monitor/insights/azure-sql

Monitoring performance by using the Query Store
https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15

Extended Events in Azure SQL Database https://docs.microsoft.com/en-us/azure/sql-database/sql-database-xevent-db-diff-from-svr


Counterexample to demonstrate there are no functional dependencies

To find if the functional dependency AB→G is implied by F you should find the closure of the attributes AB under F, i.e. AB+.

These are the steps:

AB+ = AB
      ABC     (using AB→C)
      ABCD    (using B→D)
      ABCDE   (using CD→E)
      ABCDEGH (using CE→GH)

and since G belongs to AB+, then AB→G can be derived from F.


Run calculation not working in sub query, "does not exist"

Your error was that Postgres interpreted the column as Integer and performs a Euclidean division, which leads to the zero encountered.
Changing the car_count_last_week to a decimal, will give you the rational numbers division
 (car_count_last_week::decimal/100)

fixes the problem

CREATE TABLE IF NOT EXISTS car_store (
     car_id INT GENERATED ALWAYS AS IDENTITY,
     car_date  TIMESTAMP,
     car_time TIMESTAMP NOT NULL,
     car_name VARCHAR(255) NOT NULL,
     PRIMARY KEY(car_id)
  )

INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-03 15:28:00.116594');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-11 16:13:07.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-01 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-14 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-12 18:03:27.217903');
INSERT INTO car_store(car_name, car_time) VALUES ('awesome', '2021-05-13 18:03:27.217903');

WITH last_week AS (
  SELECT COUNT(car_time) as car_count_last_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE - INTERVAL '1 week')
  AND car_time <= CURRENT_DATE - INTERVAL '6 days'
), current_week AS (
  SELECT COUNT(car_time) AS car_count_current_week
  FROM car_store
  WHERE car_name = 'awesome'
  AND car_time >= date_trunc('week', CURRENT_DATE)
  AND car_time <= CURRENT_DATE
)
SELECT car_name,
  date_trunc('week', CURRENT_DATE - INTERVAL '1 week') AS start_of_last_week,
  CURRENT_DATE - INTERVAL '6 days' AS today_but_last_week,
  date_trunc('week', CURRENT_DATE) AS start_of_current_week,
  CURRENT_DATE AS today,
  car_count_last_week,
  car_count_current_week
  ,

 CASE WHEN car_count_last_week = 0 then 1
 ELSE ROUND(car_count_current_week/(car_count_last_week::decimal/100)) * 100
 END
   AS difference_between_weeks
  FROM car_store
  CROSS JOIN last_week, current_week
  WHERE car_name = 'awesome'
  GROUP BY car_name, car_count_last_week, car_count_current_week
  ORDER BY car_name;

car_name | start_of_last_week  | today_but_last_week | start_of_current_week  | today      | car_count_last_week | car_count_current_week | difference_between_weeks
:------- | :------------------ | :------------------ | :--------------------- | :--------- | ------------------: | ---------------------: | -----------------------:
awesome  | 2021-05-03 00:00:00 | 2021-05-09 00:00:00 | 2021-05-10 00:00:00+01 | 2021-05-15 |                   1 |                      4 |                    40000

db<>fiddle here


RANK used in 'where' returns invalid column, but exists in results set

You cannot use aliases in WHERE clauses, nor can you use Window functions.

To demonstrate, consider this example (fiddle).

CREATE TABLE payment (amount INTEGER, pay_date DATE);

INSERT INTO payment VALUES (54,  '2019-09-01'), (56,  '2019-09-01'), (154, '2019-09-02'),
(156, '2019-09-02'), (254, '2019-09-03'), (256, '2019-09-03');

Then run the two following queries:

SELECT
  *,
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
WHERE the_sums > 200; -- ERROR:  column "the_sums" does not exist

and

SELECT
  *,
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
WHERE SUM(amount) OVER (PARTITION BY pay_date) > 200;
-- ERROR:  window functions are not allowed in WHERE

You didn't post your error message or PostgreSQL version, but your problem is that timestamp_d1_rank and timestamp_d2_rank are aliases.

You have two options:

First Option: use the aliases/Window functions within a subquery (also in fiddle)

SELECT * FROM
(
SELECT
  *,
  SUM(amount) OVER (PARTITION BY pay_date) AS the_sums
FROM payment
) AS tab
WHERE the_sums < 200;

Result:

amount    pay_date  the_sums
    54  2019-09-01       110
    56  2019-09-01       110

Second Option: you can use a CTE (aka the WITH clause as outlined here or as in @ypercubeᵀᴹ 's answer above or also see the fiddle.

For a good explanation of this whole area, see here.


How to create and start a PDB for RCU in Oracle 12c

Look at:
http://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG99827

Operating system authentication is not supported in a multitenant environment.

You can't connect with "sqlplus / as sysdba" to a PDB. It only works with the CDB.

If you change environment variable ORACLE_SID to that of a PDB, you'll connect to the binaries to what he expects to be a CDB that doesn't exist, and you'll get the message "connected to an idle instance"

Fun fact: If you are logged as the oracle binaries owner (or someone in the dba group) and try to connect with:

sqlplus pdbuser/pdbpass@pdb as sysdba

Even if the pdbuser doesn't exist in the CDB, you'll log in it, as pdbuser, with SYSDBA permissions, on the CDB, because of OS Authentication.

Or at least that's what happens in my test/experiment environment...


Get data from a MDX linked server and use it on a FROM clause on SQL without OPENQUERY

If you're familiar with SSIS it can be done this way by saving the query in a file and then using it as a source query.  You'll start by creating an OLE DB connection manager that uses the Microsoft OLE DB Provider for Analysis 13.0 provider.  Set the initial catalog to your cube. Then add an Execute SQL Task with the OLE DB connection.  For the SQLSourceType, use file connection and create a connection manager for the file with the saved MDX query (or select this connection manager if you already created it).  From here, you can output the results to a table.  One option is to store the intermediate results in an SSIS object variable.  To do this you'll need to set the Result Set property to Full, and map the object variable to result name 0 on the result set pane of the Execute SQL Task.  You can then put another Execute SQL Task inside a Foreach Loop (ADO Enumerator type), map variables for each of the columns returned by the query, and then use these variables for an insert into an output table with the Execute SQL Task that's inside the Foreach Loop.


How to find an text in entire Oracle database?

See https://lalitkumarb.wordpress.com/2015/01/06/sql-to-search-for-a-value-in-all-columns-of-all-atbles-in-an-entire-schema. In short (with my slightly modified version of the code):

SELECT DISTINCT table_name, column_name
  FROM user_tab_cols,
  TABLE (xmlsequence (dbms_xmlgen.getxmltype ('select '
  || column_name
  || ' from '
  || table_name
  || ' where upper('
  || column_name
  || ') like upper(''%'
  || '&searchword'
  || '%'')' ).extract ('ROWSET/ROW/*') ) ) t
  where table_name not in (
    select distinct table_name
      from user_tab_cols where data_type like 'SDO%'
      or data_type like '%LOB')
  order by table_name, column_name;

I exclude tables that contain LOB or spatial columns as they can fail. Add other filters as you please. This will be very expensive to run in a large database!

EDIT: &searchword is the text you are searching for, of course.


SSRS - select where parameter and fixed value

Ok the problem was actually with the parameter itself.

I had to create a new one in the query called @Parameter_1 and then set it up in the query parameter tab and delete the old one.

Really confusing.


MS SQL -Date and Time Style conversion problem

The 104 format has . in it and thats why it is failing vs the 112 has not dots so you can cast that as int. See https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

DECLARE @LoopDate datetime
SET @LoopDate = '12/23/2015'
SELECT @LoopDate AS Original,
CONVERT(VARCHAR(8), @LoopDate, 104) as GermanDate_WithDot, -- see the dot
 cast(replace(CONVERT(VARCHAR(8), @LoopDate, 104),'.','') as int)  as ProperlyCastedGermanDate_WithoutDot,
CAST(CONVERT(VARCHAR(8), @LoopDate, 112) AS int) AS ISOCasted_Date_asInt  ;

Note: Casting date to int for some later math is not a good idea.


What happens if data gets inserted in the middle of a transaction?

A statement sees a consistent view of the "world" based on the data that was there when the statement started. So the select statements in your two insert statements will not see new rows while they are running.

However, if the table is changed between running the two statement (or after the first one started), each select statement could see different data e.g the first one sees 100 rows, the second one 200.

If you want to ensure that the whole transaction sees a consistent view of the data, use a higher isolation level. In your case using repeatable read would be enough.

begin transaction isolation level repeatable read;

...
commit;

Another option would be to do this in a single statement using a data modifying cte:

with to_insert as (
  SELECT id, location
  FROM sensor_log
  INNER JOIN sensor_location_to_insert USING (location)
), insert_a as (
  insert into sensor_log_a
  select *
  from to_insert
)
insert into sensor_log_b
select *
from to_insert;


During a log backup is the data backed up to the start or end of the operation?

The log backup file shall contain all the transactions (including the ones that hasn't been committed yet) up to the moment the backup finished running and you can verify it as follows:

CREATE DATABASE MyDB;
ALTER DATABASE MyDB SET RECOVERY FULL;

USE MyDB;
CREATE TABLE LogTest (numbers int);

Once created the database and the table that's gonna be used on the test, take the first full backup so that it will be possible to take a log backup later.

BACKUP DATABASE MyDB
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_FULL.bak';

Now that a full backup was taken let's insert some data on the table:

USE MyDB;

INSERT INTO LogTest(numbers) VALUES (1);

BEGIN TRAN
INSERT INTO LogTest(numbers) VALUES (2);

Observe that the second insert is under a BEGIN TRAN that hasn't been committed yet. Next we'll use the function [fn_dblog to check the content of the log file before taking a log backup.

SELECT [Current LSN], [Operation], [SPID], [Transaction Name], [Begin Time], [End Time] FROM fn_dblog(NULL, NULL);

On the picture we can see LSNs related to the first and second INSERT

BACKUP LOG MyDB
TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_LOG.trn';

A log backup was taken and we can read its content with the function fn_dump_dblog using the following query:

SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time]
FROM fn_dump_dblog (NULL, NULL, N'DISK', 1, N'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2017\MSSQL\Backup\MyDB_LOG.trn',
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT,
DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT);

As we can see those LSNs related to the unfinished transaction actually are on the transaction log backup even though they were not committed. The catch is that as Microsoft says

  The transaction log is a critical component of the database. If there
  is a system failure, you will need that log to bring your database
  back to a consistent state.

Hence after performing a restore of that log backup the data related to those unfinished transactions will be rolled back to keep the database in a consistent state.
You can consider safe (can be restored) all the transactions that were committed. It can be verified by dropping the database MyDB and restoring the backups that were generated with the steps above.


DB constraints in face of quickly changing requirements in an agile environment

As a data professional, I'm going to tell you straight up to leave the constraints in the database.

However, there's no right or wrong answer, because it depends on what risk you're prepared to deal with.

If you don't want relational integrity, use XML or JSON or some other thing that isn't an RDBMS. But if you want to use SQL Server or Oracle or PostgreSQL, go right ahead and manage it in the app.

Somewhere along the line, though, there will be data corruption and orphaned records. That risk is real, but I can't tell you how big of a risk it is.

If the effort of fixing corruption like that is less than the effort of maintaining constraints in the database, that's a manageable risk.

On the other hand, if you can't bear the thought of fixing corruption or orphaned data, then leave the constraints in the database and spend some money on the right tools to help you with database changes and migration.

P.S. "Agile" is not an excuse for bad design or lack of documentation.


How can I change the connection descriptor used by Oracle Enterprise Manager?

I'm guessing because you haven't specified what version of OEM you're using.

If you're using OEM 12C, go to the DB home then click on "Oracle Database", "Target Setup", "Monitoring Configuration".  You can change the SID there.


SQL Server Non Clustered Index Compression

I've only recently realised, that we aren't compressing any of our
  non-clustered indexes. Should we also be doing this? Are there any
  gotchas that we should be aware of?

The main question here is, do you want to save space & memory usage at the cost of more cpu cycles? This is something we will not be able to answer for you.

Since you are able to compress the clustered indexes, no other restrictions noted in Considerations for When You Use Row and Page Compression are possible limitations.

You could always run the sp_estimate_data_compression_savings procedure to calculate the gains you could get from compressing the nonclustered indexes. See here for a solution for every table in your data warehouse.

Speaking of data warehouses, you could also take a look at (clustered / nonclustered) columnstore indexes and see if they are a fit for your (fact) tables.


Select Units Sold and Total Units Sold from a Sales record table

You need a conditional aggregate using CASE:

SELECT sr.itemID, sr.itemname,
   SUM(sr.itemunits) AS "Total Units Sold",
   SUM(CASE WHEN date='2016-04-14' THEN sr.itemunits END) AS "Units Sold"
FROM salesrecord AS sr
WHERE date <= '2016-04-14'
GROUP BY sr.itemID, sr.itemname

I used a table-alias sr which improves readability.

And I added sr.itemID to GROUP BY, which is compliant with Standard SQL (and the latest version of MySQL)


How can I write a proper query to JOIN all of these tables without duplicates?

The following will work in Postgres. Test here: SQL-Fiddle, postgres-test. SQL-Server does not have NATURAL JOIN and MySQL has NATURAL but doesn't have FULL joins:

SELECT
    id,
    first_name,
    last_name,
    year,
    passing_yards,
    rushing_yards,
    receiving_yards
  FROM
      players p
    NATURAL LEFT JOIN
      ( passing_stats pas
      NATURAL FULL JOIN
        rushing_stats rus
      NATURAL FULL JOIN
        receiving_stats rec
      )


How to structure T-SQL query with many left joined "attributes"

In the original Plan you have few Merge Join and then Nested Loops.

When the things slow down, you can see that plan is changed to: Hash Match (and parallel).

You can try to enforce using old plan by replacing inner join with inner MERGE join and inner LOOP join.

The documentation may help you dive into differences between them: Hints (Transact-SQL) - Join

Take note of the warning:

  If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the position of the ON keywords.

If you do not wish to force the join order for all joined tables in the query, consider using OPTION (LOOP JOIN, MERGE JOIN) instead. This will allow the optimizer freedom to reorder joins and choose between nested loops and merge join on the basis of estimated cost.


You can do many things to prevent this.

Move DB server to somewhere else (hosting)
Buy an UPS
Change to a simpler DB software (SQLite, Files)
Make your code to always save the latest changes, so when power up, programs keep running from last saved point.


Everything is ok and x is not disappearing

Sounds like it is a syntax check complaining:

select * FROM project where name='Anna'
         ^^^^  -- missing syntax keyword!


Calculate all latitude and longitude distance in result using MySQL

Add something like this onto the query in order to get the TotalKM

GROUP BY id, gpslat, gpslon WITH ROLLUP

Don't attempt to get that exact output with SQL; do that in you application code.  The ROLLUP will give you an extra line something like

NULL NULL NULL 2.1


How to format documentation in MS_Description extendedproperties?

Why use extended properties at all here? They're second class citizens and not all that discoverable. The documentation you've described could just as easily be a block comment in the stored procedure body or, better yet, in your data dictionary and/or source control (since this stuff really should be documented in a safe location beyond the database anyway).

A block comment in a view, function, trigger or stored procedure is definitely stored in the database. Everything between the two GO batch separators is stored as the body (though I confess I don't know which visual editors might strip them, since I just use a standard query editor):

GO

/*
  this is a comment
*/

CREATE PROCEDURE dbo.whatever
  /* @A is for apple, @J is for jacks */
  @A int,
  @J int
AS
BEGIN
  SET NOCOUNT ON;
  -- do stuff;
END
GO

(I will also confess that comments stored before CREATE PROCEDURE or after the final END may be prone to inadvertent exclusion when someone modifies the procedure, so it may be safer to keep the comments inside the body.)

A little trickier to store documentation for tables and columns inside the database without using extended properties. But again, I'd argue these should be documented externally anyway. If you're going to bother writing documentation for these things, you may as well put it somewhere that will still exist if the database goes south.


You should create roles and grant access to roles rather than granting users access directly to schema.

Once you assign permissions to the role, you can just add users to the role. This way you dont have to manage permissions for individual users. The users inherit permissions granted to role.

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the database
GRANT
      SELECT, INSERT, UPDATE, DELETE, ALTER

ON SCHEMA::dbo
      TO TableSelector
GO

-- Add an existing user to the new role created
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO

-- Revoke access rights on a schema from a role
DENY ALTER  -- you can customize here ...
ON SCHEMA::dbo
      TO TableSelector

see my answer to : Setting user permissions for different SQL Server schemas


Set oracle workspace from java application

I was receiving the

java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement

because I was using the EXECUTE command rather than the CALL command.  In SQL Developer either EXECUTE or CALL works.
I was getting

java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended

because of the semicolon at the end of the SQL statement.  The following

String setWorkspace = "CALL DBMS_WM.GotoWorkspace('WORKSPACE')";
CallableStatement cs = conn.prepareCall(setWorkspace);
cs.execute();

works.