Preventing concurrent sends of emails from my application

This should be your main two queries

STEP01 : Run this query

SELECT nome,email FROM clientes
WHERE lastsend < (DATE(NOW()) + INTERVAL 0 SECOND);

STEP02  : Run your process to send out emails

STEP03 : Run this query

UPDATE clientes SET lastsend = NOW()
WHERE lastsend < (DATE(NOW()) + INTERVAL 0 SECOND);

The expression (DATE(NOW()) + INTERVAL 0 SECOND); is always midnight today.

Look it over

STEP01 will check for lastsend to make sure it is before midnight today
STEP03 changes the lastsend to the current date and time for the emails that have not been sent out today
You can run STEP01 - STEP03 multiple times in the same and only new emails will go out

You should also make sure that lastsend is indexed. If it is not, then run this

ALTER TABLE clientes ADD INDEX (lastsend);

This will help speed up the query in STEP01

As for locking the table during this process, I would need to see the table structure. I have an idea concerning using the DB Connection ID. It goes something like this:

Add a column called sender to clientes table and index it

ALTER TABLE clientes ADD COLUMN sender TINYINT DEFAULT 0;

Once you create that column and index it as I specified, you can perform the following:

STEP01 : Mark everything before midnight in your DB Connection as midnight

UPDATE clientes SET
    sender   = CONNECTION_ID(),
    lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)
WHERE
    lastsend < (DATE(NOW()) + INTERVAL 0 SECOND)
;

STEP02 : Run your process to send out emails where sender=CONNECTION_ID() and lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)

STEP03 : Mark your stuff as sent

UPDATE clientes SET
    lastsend = NOW()
WHERE
    sender   = CONNECTION_ID() AND
    lastsend = (DATE(NOW()) + INTERVAL 0 SECOND)
;

This will restrict which DB Connection send which batch of email

Give it a Try !!!


Use EMCLI to update OEM Oracle Home Path

Assuming your DB target name as seen in EM is ORCL11.WORLD and you want to set it's Monitoring Configuration's Oracle Home to /oracle/product/11.2.0.4, the syntax would be the following:

emcli modify_target -type='oracle_database' -name='ORCL11.WORLD' -properties='OracleHome:/oracle/product/11.2.0.4'

Probably would be similar approach for Listeners, ASM, etc.

Version note: This worked for me on EM 12cR4.


I have recently installed postgresql 9.4 from source

[pol@localhost inst]$ ./bin/psql --version
psql (PostgreSQL) 9.4.0
[pol@localhost inst]$

My install directory structure is as follows

/home/pol/Downloads/software/postgres/inst
[pol@localhost inst]$ ll
total 128
drwxrwxr-x.  2 pol pol  4096 Jan 29 05:42 bin
drwx------. 18 pol pol  4096 May  1 18:03 data
drwxrwxr-x.  6 pol pol  4096 Jan 29 05:42 include
-rw-r--r--.  1 pol pol 75392 Jan 29 05:44 INSTALL
-rw-rw-r--.  1 pol pol   158 Jan 29 05:45 install.txt
drwxrwxr-x.  4 pol pol  4096 Jan 29 05:42 lib
-rw-------.  1 pol pol 14718 May  2 17:47 logfile
-rw-rw-r--.  1 pol pol  2981 Jan 30 07:49 logfile.txt
-rw-rw-r--.  1 pol pol  5996 Apr  2 22:32 mytestdb.sql
drwxrwxr-x.  6 pol pol  4096 Jan 29 05:42 share

Then I searched for files name xxxx.opts

[pol@localhost inst]$ find . -name *.opts
./data/postmaster.opts

So, it exists in 9.4 - it appears from Googling that it generally lives in the data directory. I have never edited it manually.

[pol@localhost inst]$ more ./data/postmaster.opts
/home/pol/Downloads/software/postgres/inst/bin/postgres "-D" "./data"
[pol@localhost inst]$

So, it appears that it still has a function in the system.


Availability Group backup preference not changing to primary

I tried to set the backup preference to Primary but it doesn't change, it stays on Prefer Secondary

I'm not sure if you mean the user interface, or if the actual effect of the change didn't occur as you expected.  My answer assumes the latter.

To be clear about something that I find a little confusing: this setting is not something that automatically affects how backups are run.  It's simply a place where you, as a DBA, can store "metadata" about how you prefer backups to be taken.

This is documented, but it's not obvious at all from the user interface.  Here's the quote from the docs:

  ❕ Important

  There is no enforcement of the AUTOMATED_BACKUP_PREFERENCE setting. The interpretation of this preference depends on the logic, if any, that you script into back jobs for the databases in a given availability group. The automated backup preference setting has no impact on ad hoc backups.

After setting this, you need to update your backup jobs with code like this (also lifted from the docs):

IF (NOT sys.fn_hadr_backup_is_preferred_replica(@DBNAME))
BEGIN
      Select 'This is not the preferred replica, exiting with success';
      RETURN 0 - This is a normal, expected condition, so the script returns success
END
BACKUP DATABASE @DBNAME TO DISK=<disk>
   WITH COPY_ONLY;

Using sp_helptext to view the source of sys.fn_hadr_backup_is_preferred_replica, it can be seen that this takes into account both the AG-level AUTOMATED_BACKUP_PREFERENCE setting, and the replica-level BACKUP_PRIORITY setting.

Note: since you're using Ola's scripts, this is already taken into account

If you just didn't see the value change in the user interface, I would suggest using the "script" button to show the T-SQL that's being run by the UI, and then running that script manually to see if the change is properly made.


Are utf8_bin and utf8_general_cs the same?

The notion that binary collations are the same as case-sensitive collations is, unfortunately, an extremely common one.

However, they are very much not functionally equivalent. There are four areas where behavioral differences can be seen (at least four that I am aware of):

Combining Characters

Consider having a lower-case ü ("u" with diaeresis) and an upper-case Ü ("U" with diaeresis). Both types of collations will be able to distinguish them.

Now, consider having an upper-case U and a ̈ (combining diaeresis). When the combining character is placed after the non-accented U, you get Ü. Visually it is the same as the single Ü ("U" with diaeresis). And a case-sensitive (and even accent-sensitive) collation will seem them as being the same, even though one is a single code point and the other is a combination of two code points. But a binary collation cannot compare them as being equal since they are not the same code points (or even the same number of code points).
Fullwidth Characters

A collation that is case-sensitive but width-insensitive would be able to equate =ｏ= and =o=. However, a binary collation cannot seem them as equal since they are different code points.
Accent Insensitivity

A collation that is case-sensitive but accent-insensitive would be able to equate o and ô. However, a binary collation cannot seem them as equal since they are different code points.
Sorting

A case-sensitive collation will sort ~ before a and A after it. But, a binary collation will sort those same characters as: A then a then ~.

All of this is documented in the following post of mine:

No, Binary Collations are not Case-Sensitive

It is presented in terms of Microsoft SQL Server, but the behavior is based on rules defined by the Unicode standard, which should be the same across RDBMSs, languages, OSs, etc (with minor variation due to which version of the Unicode Standard is being used, and who did the implementation since Unicode is only a standard and has been implemented with slight variation across vendors).

  I have checked that utf8_general_cs is not available in default MySQL. May I know why?

My guess is that the "general" collations are obsolete and have been superseded by newer "unicode" and culture-specific collations. The documentation (half-way down the page, paragraph starting with "For any Unicode character set,") even states:

  utf8_general_ci is a legacy collation that does not support expansions, contractions, or ignorable characters. It can make only one-to-one comparisons between characters.

The "unicode" collations are probably the default sort weights and collation rules. The culture-specific collations tailor the weights and rules to be correct for that culture (when the default is incorrect). For more info on why there are different collations, please see my answer to the following S.O. question:

What is the point of COLLATIONS for nvarchar (Unicode) columns?


Query taking non-consistent time to execute

One possible explanation:

The first time you run your query it's probably not in cache, which means it has to be read from disk and stored in memory.  If your query remains the same and execution plan doesn't change, the time it takes to execute the same query will be less.

If the SQL Server you are using is on your own computer and/or others will not be affected, you can clear the plan cache with the DBCC FREEPROCCACHE to see if the execution times remain constant.

You can check to see which queries are in cache by running the following command

SELECT text,objtype, refcounts, usecounts, size_in_bytes,
    disk_ios_count, context_switches_count, pages_allocated_count, original_cost,
    current_cost
FROM sys.dm_exec_cached_plans p
    CROSS APPLY sys.dm_exec_sql_text(plan_handle)
    JOIN sys.dm_os_memory_cache_entries e
    ON p.memory_object_address = e.memory_object_address
WHERE cacheobjtype = 'Compiled Plan'
ORDER BY objtype desc, usecounts DESC

If you see the query

SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]
FROM [company].dbo.[customer]
WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 )

Then you know its in cache.

If clearing out the cache doesn't make the execution times more consistent, please include the actual execution plan, by selecting CTRL+M before executing your query.

Another possible explanation:
Since you are testing from different computers, there could be additional network latency involved when SQL Server sends the data back to the computer you are testing from. You can verify this by using task manager and opening up the Networking Tab when running your query from another server.

Update 15:26

If bandwith was an issue , one solution would be to find out:
1. How much bandwith exists between your clients and server
2. How much bandwith you are actually using

Your network administrator or sysadmin can tell you how much bandwith exists between you and other machines within your domaine. Using Process Explorer or perfmon, you can easily determine how much of that bandwith is being used by SQL Server and other processes. Just keep in mind that when you are executing a query on the server, the results don't have to travel across a network cable as they would with a remote client, which would change execution times.

Your inital question though is why you are seeing inconsistent execution times for the same query. So a discussion about networks and bandwith would be off topic.

Update 09:27

Here are two screenshots of Process Explorer that will help you find the graphs that will allow you to analyze, CPU,IO,Memory and Network traffic.

Main Screen

Graphs


Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec?

There are some options you will need to consider
InnoDB Buffer Pool
The reason 26G was picked is that you have 32GB of RAM and 80% of that is 25.6 G. Since you mentioned that you will have 100 databases and 100 applications making this a multitenant DB Server, you are going to have to get the InnoDB Buffer Pool just right.
Please run this query:
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

This will tell you how much space is currently occupied by your MySQL instance. Whatever the total of InnoDB Data Size and Index Size is, that is what you use. If that total is over the 80% limit then you must you the 80% (leaving innodb_buffer_pool_size at 26G).
Since you have a quad-core server, set innodb_buffer_pool_instances to 4.
InnoDB Transaction Log Files
Since 26G was selected as innodb_buffer_pool_size, you are going to need the biggest possible transaction logs. The value 512M was probably picked for innodb_log_file_size because there is nothing to suggest the amount of transaction data (in bytes) that will actually be processed.
To resize your transaction logs
mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Next edit my.cnf, replacing
innodb_log_file_size           = 512M

with this
innodb_log_file_size           = 2047M

Then, replace the transaction logs like this
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
service mysql start

After a few months of peak activity, you could then run this query during a peak:
SET @TimeInterval = 300;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL,@MB_WL,@GB_WL;

Based on what comes back, you should resize the transaction logs again.
Please see my earlier posts on doing this:

Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?

Multicore Engagement
When the InnoDB Plugin was introduced in MySQL 5.1.38, it set the world of MySQL on fire. Why? Because InnoDB was single threaded. You had to install the plugin to have new setting that allowed InnoDB to use multiple cores.
Rather than writing something lengthy, please read my earlier posts of tweaking MySQL 5.5 to have InnoDB utilitze multiple cores:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance


Try using the following query (Considering you are using mysql):

SELECT rt.id, rt.type,
    rt.total_no_of_rooms,
    if(SUM(r.no_of_rooms) IS NULL,0,SUM(r.no_of_rooms)) as "reserved rooms",
    (rt.total_no_of_rooms - if(SUM(r.no_of_rooms) IS NULL,0,SUM(r.no_of_rooms))) as "rooms available"
FROM Roomtypes rt LEFT OUTER JOIN Reservations r
    ON r.roomtype_id = rt.id
    AND '2018-04-11 10:00:00' >= r.check_in
    AND '2018-04-12 09:59:59' <= r.check_out
GROUP BY rt.id, rt.type, rt.total_no_of_rooms

I have just added condition to check NULL value. If you are using PostgreSQL, use COALESCE function.


Is there any implications on updating Azure SQL server from V11 to V12?

This Azure documentation page lists the limitations during and after the upgrade:

Plan and prepare to upgrade to SQL Database V12


My solution using SQL Server is as below. I could not test it in mysql as my ubuntu server is down. Also, I am not sure if mysql implements cte and window functions yet. In such case you may have to re-write the query as a stored procedure.

--Table definition

create table test
(f_date date,
trd int,
code varchar(10),
t_date date);

--Populate table

insert into test
values
 ('1-Jan-14',   1,          'B',                  '1-Nov-13'),
 ('1-Jan-14',   2,          'B',                  '3-Oct-13'),
 ('1-Jan-14',   2,          'B',                  '3-Oct-13'),
 ('2-Jan-14',   1,          'A',                  '1-Nov-13'),
 ('2-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('2-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('2-Jan-14',   3,          'A',                  '2-Jan-14'),
 ('3-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('3-Jan-14',   2,          'A',                  '3-Oct-13'),
 ('3-Jan-14',   3,          'A',                  '2-Jan-14'),
 ('3-Jan-14',   4,          'C',                  '3-Jan-14');

--Quey code

 ; with cte as
 (select RANK()over(partition by trd order by f_date) as rank_no, trd, code, f_date
 from dbo.test)
 select distinct test.f_date as 'Date', cte.trd as 'Trd', cte.code as 'First showed up as code',
 cte.f_date as 'In Date', test.t_date as 'T-Date'
 from test
 join
 cte on  test.trd = cte.trd
 where test.f_date = '20140103'
 and cte.rank_no = 1;

--Results

Date    Trd First showed up as code In Date T-Date
2014-01-03  2   B   2014-01-01  2013-10-03
2014-01-03  3   A   2014-01-02  2014-01-02
2014-01-03  4   C   2014-01-03  2014-01-03


Count business days between 2 dates in postgreSQL

You're not using (or showing us) a unique key, if you have more than one row with the same (start_date, end_date) you will get wrong results.

select start_date, end_date,
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select start_date, end_date,
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by start_date, end_date;

start_date          | end_date            | thediff
:------------------ | :------------------ | ------:
2017-05-29 00:00:00 | 2017-06-02 00:00:00 |       5
2017-06-01 00:00:00 | 2017-06-01 00:00:00 |       1

select id, start_date, end_date,
       sum(case when extract (dow from dt) in (1,2,3,4,5) then 1 else 0 end) as thediff
from (
       select id, start_date, end_date,
              generate_series(start_date, end_date, '1 day'::interval) as dt
       from   tbl
     ) t
group by id, start_date, end_date;

id | start_date          | end_date            | thediff
-: | :------------------ | :------------------ | ------:
 1 | 2017-06-01 00:00:00 | 2017-06-01 00:00:00 |       1
 2 | 2017-05-29 00:00:00 | 2017-06-02 00:00:00 |       5

db<>fiddle here


Allow multiple users to see procedures and functions

Try granting EXECUTE permissions on user devel.

GRANT EXECUTE ON PROCEDURE test1.yourprocedure TO 'devel'@'%'


Does AE certificate include both master and encryption keys together?

After setting up Always Encrypted, I can see a certificate in my Current User

Correct, you've stated that the Column Master Key (CMK) will be created/stored there. Most likely this was done via the GUI to generate a new key and save it there. There are other options as well for the CMK.

  [...] under the key_path I see this location, but there is no location specified for encryption keys.

Correct, that was the location used when executing the metadata update to SQL Server for where the CMK exists. Since SQL Server doesn't go out and touch that Certificate (which shouldn't be in the server) it only knows what to send back to the client driver as metadata. The certificate holds an asymmetric key pair that is used to encrypt and decrypt the Column Encryption Key (CEK) which is stored inside of SQL Server in the same database.

  So is just this certificate export enough?

Assuming AE is setup correctly, the client only needs that certificate, yes. There are obviously database level permissions that are required to get said metadata but overall the process is moot if you don't have a copy of the CMK.

  Does it contain both master and encryption keys together in the same certificate?

No, as I stated, there is the CMK which is the certificate you've created and then there is the CEK which does the actual encryption and decryption of the data.

You'll want to go back and read a bit more about Always Encrypted and how it works as this is all explained in the documentation.


What if all the non clustered indexes on my table were filtered indexes?

Significant gains? No, I shouldn't think so.
SQL Server's indexes are B-Trees. These have a hierarchical nature. To read a row the server starts at the root node of the index then steps through one or more intermediate nodes before reading the row's values via the leaf node. (Details vary between clustered & non-clustered indexes, and heaps.) The speed-limiting factor is often the number of levels there are in the index and, hence, the number of pages (each index node is its own page) that have to be read. This, in turn is controlled by the fan-out, the number of lower-level pages a higher-level page can reference, which is itself determined by the total size of the key. Let's do some hand-wavy maths.
Index IDX_SeasonItemID_Tier1_Tier2_ImageSizeBodenImageURL_INC_BodenDomainURL is defined as
Column          Definition     Effective bytes  Note
------------    -------------  ---------------  -----------------------------------
SeasonItemID    VARCHAR(5)                   5  Use the full size
Tier1           VARCHAR(10)                  5  Assume 50% of defined size
Tier2           VARCHAR(10)                  5  Assume 50% of defined size
ImageSize       VARCHAR(10)                  7  Minimum from given data
ImageFormat     VARCHAR(20)                 10  Assume 50% of defined size
BodenImageURL   NVARCHAR(210)              210  Assume 50% of defined size, 2 bytes per character.
                                          ====
Total                                      242 bytes

I'll ignore the included column for fan-out since it's only present in the leaf nodes. Since there's about 8060 usable bytes per page that gives roughly 8060 / 242 = 33 rows per page. So a one-level index can reference up to 33 rows, a two-level index can reference 33 * 33 = 1,089 rows, a three-level 35,937 rows, and a four-level 1,185,921 rows. Given your counts of 74,000 rows per picture size and 370,000 rows in total you'll need a four-level index1 for both filtered and non-filtered indexes. There will be no reduction in the number of pages referenced and so no improvement in run-time.
These numbers are a lower limit as the included columns and the column of the clustered index will take further space in the leaf pages of the non-clustered index.
Adding these indexes will introduce risks, however. One is that the optimizer can be finicky about when it uses them. Another is that bringing on a new size (a data change) will require five more indexes to be defined (a DDL change). This is unwelcome maintenance overhead but without performing it the new-sized images will, in effect, be un-indexed. Schema changes will be complicated by the additional objects. Index maintenance will be slightly more complicated, if scripted manually. The overhead of the upper level of the indexes will increase the DB size fractionally, and with it backup and restore times.

1For the value used by an actual index see sys.dm_db_index_physical_stats.index_depth (docs).


Often indicates nothing more than that certain queries are executing with parallelism; CXPACKET waits in the server are not an immediate sign of problems, although they may be the symptom of another problem.

If the server hosts a data warehouse or reporting type of database that receives a low volume of queries but processes large amounts of data, parallelism can substantially reduce the time it takes to execute those queries. In contrast, however, if the server hosts an OLTP database that has a lot of small queries and transactions, then parallelism can kill throughput and negatively impact performance.

Whenever possible, it is best to isolate and troubleshoot the underlying wait type, since this will lead to overall system throughput improvements. Again, the CXPACKET waits are simply a symptom of a problem in most cases, not the actual problem

The sys.dm_os_latch_stats DMV contains information about the specific latch waits that have occurred in the instance, and if one of the top latch waits is ACCESS_METHODS_DATASET_PARENT, in conjunction with CXPACKET, LATCH_*, and SOS_SCHEDULER_YIELD wait types as the top waits, the level of parallelism on the system is the cause of bottlenecking during query execution, and reducing the 'max degree of parallelism' sp_configure option may be required to resolve the problems.

This TechNet Magazine article is old but it does say to try turning off hyperthreading if you are exceeding the 5000 per sec per processor:

Optimizing SQL Server CPU Performance by Zach Nichter


Restore a WordPress database from .frm and .idb only

In general, if you're using MyISAM tables, you might be okay. See https://stackoverflow.com/a/879268/2385479 for instance.

If your tables are InnoDB, then it's a bit more difficult. According to https://serverfault.com/a/487534/67211, it seems the ibdata1 file is rather important.

Based on the error message, what I believe is happening here is that your database structure is being read correctly from the files you were able to recover, but the underlying table structure and associated data itself is in the missing file.

If it's a MyISAM table type, you can attempt to recover it (in phpMyAdmin click the Operations tab and look for the Repair text; from the command line or SQL tab just type REPAIR TABLE wp_commentmeta;.

Sorry for the bad news, but it doesn't look good.


Enabling IPAII protocol disable remote conections. Why?

After you modify any of these settings it's common to lose connections until you restart the instance (and sometimes Browser too if you're using it). We have a policy not to do it without an outage window.


Database SQL Language Reference - CREATE INDEX

  "Oracle does not index table rows in which all key columns are null
  except in the case of bitmap indexes."

Demo

create table t as select level as field3 from dual connect by level <= 1000000;
alter table t add field3_ind as (case when field3 = 101 then field3 end);
create index t_ix_field3_ind on t (field3_ind);

select * from t where field3_ind = 101;

select    segment_name
         ,segment_type
         ,bytes

from      dba_segments

where     owner        =  'DMARKOVITZ'
      and segment_name in ('T','T_IX_FIELD3_IND')

order by  segment_name
;

+-----------------+--------------+----------+
| SEGMENT_NAME    | SEGMENT_TYPE | BYTES    |
+-----------------+--------------+----------+
| T               | TABLE        | 13631488 |
+-----------------+--------------+----------+
| T_IX_FIELD3_IND | INDEX        | 65536    |
+-----------------+--------------+----------+

P.s.
The use of virtual column makes the querying more user friendly.

If the index would have been defined as -

create index t_ix_field3 on t (case when field3 = 101 then field3 end);

then in order to use the index, the query should have looked like -

select * from t where case when field3 = 101 then field3 end = 101;


Role to grant a DBA who should not access confidential Database

I suggest that you are careful with securityadmin.
As a security admin, you can create a new login and give this new login CONTROL permissions on the server. Now you can login as this new login and do almost anything. CONTROL on server is very close to sysadmin. Among other things, you can use any database and access all information, unless there are explicit DENY
Checking DENY is one of the big differences between sysadmin and CONTROL server. SQL Server doesn't even check for DENY for a sysadmin. But it does that for CONTROL server.
So, initially I thought that you could grant CONTROL server to this person, create a user in the confidential database, and then DENY CONNECT to this user. However, since this now can create a a login this can create a new login and grant CONTROL server to this new login. So, you would have to DENY ALTER ANY LOGIN to avoid this part.
In short: create a login, grant CONTROL SERVER (or securityadmin, or something else except sysadmin), deny ALTER ANY LOGIN, create a user in your confidential database, and deny connect for this login to the user in the confidential database.
This is just my own reasoning, so I can't say that there aren't any holes in above!


Is possible to use ORDER BY with IF ELSE condition?

I want to show the records which have fav=1 at fast, then I want to show the data where the date is greater than the current date in ascending order(date) and at last the remaining records in descending order(date).

Try:

SELECT *
FROM `test`
ORDER BY fav=1 desc,
         fav=0 AND `date`>=DATE(NOW()) DESC,
         CASE WHEN fav=0 AND `date`>=DATE(NOW())
                  THEN UNIX_TIMESTAMP(`date`)
                  ELSE -UNIX_TIMESTAMP(`date`)
              END ASC;


SQL Server Replication Stored Procedures sp_MSdel_db, sp_MSupd_db, sp_MSins_db

Jeff - Have a look at the parameters for sp_addarticle.  Notice the parameters @ins_cmd, @upd_cmd, and @del_cmd can be configured to replicate inserts, updates, and deletes as either stored procedure calls or straight SQL.

DBA number 1 likely setup the production publication to replicate inserts, updates, and deletes as SQL.  DBA number 2 likely setup the development publication to replicate inserts, updates, and deletes through called stored procs.


SQL Server 2008 on Hyper-V Virtual Machine with Multiple instances

For your hardware setup you should go with a single instance of SQL Server.  Configure resource governer to identify dev and prod logins and set limits accordingly.


ROLLBACK doesn't work after INSERT INTO newly created destination table

The reason is that that some statements, like CREATE TABLE cause an implicit commit. You can read about them in the documentation: Statements That Cause an Implicit Commit.

So the original sequence of statements:

START TRANSACTION
SHOW TABLES LIKE customers
CREATE TABLE `customers__20150119_14_08_20` LIKE `customers`
INSERT INTO `customers__20150119_14_08_20` SELECT * FROM `customers`
ROLLBACK

will expand into:

START TRANSACTION ;   -- transaction context created
SHOW TABLES LIKE customers ;

COMMIT ;              -- CREATE TABLE forces commit before itself
                      --     (at this point the previous transaction is done.)
START TRANSACTION ;   -- and a new transaction
CREATE TABLE `customers__20150119_14_08_20`
    LIKE `customers` ;
COMMIT ;              -- CREATE TABLE forces commit after itself.
                      -- At this point there's no transaction context

START TRANSACTION ;   --  starts a new transaction
INSERT INTO `customers__20150119_14_08_20`
    SELECT * FROM `customers` ;
COMMIT ;              -- caused by "autocommit on" setting (guess).

ROLLBACK ;            -- this rollback HAS NOTHING to undo

The solution would be to start the transaction (or a new one) after the CREATE TABLE statement or use a temporary table.


SQL SERVER Verifying mirror status from 2 weeks ago

Depending on how long your SQL Server logs are retained, you can filter the SQL Server Logs with "mirror" to get an overview (not every detail) of the mirroring status on your Mirror server.

You will see messages like:

Database mirroring is active with database 'Name' as the mirror copy.
Database mirroring is inactive with database 'Name' as the mirror copy.

And some other messages as well that may reveal other issues on the mirror server such as:

The mirroring connection to "serverconnection" has timed out ...


You can create a column for update and/or delete status for the records. Do update instead of delete and assign a value that specifies this process. Assign another value for update process so that you can track the record's status.

When fetching you should have a where condition that returns records which have neither of these assigned values. After fetching these 200 records, you can do your delete or update process. I wouldn't use paging for this requirement, but if you want, just add your condition to the below paging

Sample paging:

SELECT * FROM (
    SELECT COL1,COL2,COL3,ROWNUM RNUM
    FROM SAMPLE_TABLE
    WHERE UPD_OR_DELETE NOT IN (1,2) AND ROWNUM < ( (p_page_number * p_page_size) + 1)) a
WHERE rnum >= ( ( (p_page_number - 1) * p_page_size) + 1);


How to create an index used by a WHERE extractValue(…) LIKE '…'?

A generic XMLIndex should do the trick:

CREATE INDEX t_xml_x ON t_xml(xml) INDEXTYPE IS XDB.XMLIndex;

Your explain plan will be:

SELECT STATEMENT
 FILTER
  TABLE ACCESS FULL T_XML
  TABLE ACCESS BY INDEX ROWID SYS15471_T_XML_X_PATH_TABLE
   INDEX RANGE SCAN SYS15471_T_XML_X_PIKEY_IX

You should be good with this, as long as your XPath expression doesn't use any of the constructs not indexed by XMLIndex.


Should I disable "auto update statistics" in a data warehousing scenario?

Here is a whitepaper on when auto_update of stats occurs. Here are the salient points vis-a-vis automatic updates to statistics:

  The table size has gone from 0 to >0 rows (test 1).
  The number of rows in the table when the statistics were gathered was 500 or less, and the colmodctr of the leading column of the statistics object has changed by more than 500 since then (test 2).
  The table had more than 500 rows when the statistics were gathered, and the colmodctr of the leading column of the statistics object has changed by more than 500 + 20% of the number of rows in the table when the statistics were gathered (test 3).

So @JNK made the point in a comment that if you have 1 billion rows in a table, you'd need to have 20,000,5000 writes to the first column in the statistic to trigger an update.

Let's take the following structure:

CREATE TABLE dbo.test_table (
    test_table_id INTEGER IDENTITY(1,1) NOT NULL,
    test_table_value VARCHAR(50),
    test_table_value2 BIGINT,
    test_table_value3 NUMERIC(10,2)
);

CREATE CLUSTERED INDEX cix_test_table ON dbo.test_table (test_table_id, test_table_value);

Now we can check to see what happened in statistics land.

select *
    from sys.stats
        where object_id = OBJECT_ID('dbo.test_table')

However, to see if this is a meaningful statistic object we need to:

dbcc show_statistics('dbo.test_table',cix_test_table)

So this statistic hasn't been updated. That's because it looks like the statistic isn't updated until a SELECT occurs and even then the SELECT has to fall outside of what SQL Server has within its histogram. Here's a test script that I ran to test this:

    CREATE TABLE test_table (
        test_table_id INTEGER IDENTITY(1,1) NOT NULL,
        test_table_value VARCHAR(50),
        test_table_value2 BIGINT,
        test_table_value3 NUMERIC(10,2)
    );

    CREATE CLUSTERED INDEX cix_test_table ON test_table (test_table_id, test_table_value);

    ALTER TABLE test_table ADD CONSTRAINT pk_test_table PRIMARY KEY  (test_table_id)

    SELECT *
        FROM sys.stats
            WHERE object_id = OBJECT_ID('dbo.test_table')

    --DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table)
    DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

declare @test int = 0

WHILE @test < 1
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT 'one row|select < 1', * FROM test_table WHERE test_table_id < 1;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

SET @test = 1

WHILE @test < 500
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '100 rows(add 99)|select < 100',* FROM test_table WHERE test_table_id < 100;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--get the table up to 500 rows/changes
WHILE @test < 500
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END
SELECT '500 rows(add 400)|select < 100',* FROM test_table WHERE test_table_id < 100;
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
SELECT '500 rows(add 400)|select < 500',* FROM test_table WHERE test_table_id < 500;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--bump it to 501
SET @test = 500;
WHILE @test < 501
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '501 rows(add 1)|select < 501',* FROM test_table WHERE test_table_id < 501;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 600
SET @test = 501;
WHILE @test < 600
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '600 rows (add 100)|select < 600',* FROM test_table WHERE test_table_id < 600;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 700
SET @test = 600;
WHILE @test < 700
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '700 rows (add 100)|select < 700', * FROM test_table WHERE test_table_id < 700;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;

--bump it to 1200
SET @test = 700;
WHILE @test < 1200
    BEGIN
        INSERT INTO test_table (test_table_value,test_table_value2,test_table_value3) VALUES
            ('stats test' + CAST(@test AS VARCHAR(10)),@test, @test)
        SET @test = @test + 1;
    END

SELECT '1200 rows (add 500)|select < 1200',* FROM test_table WHERE test_table_id < 1200;
--DBCC SHOW_STATISTICS('dbo.test_table',pk_test_table);
DBCC SHOW_STATISTICS('dbo.test_table',cix_test_table) WITH STAT_HEADER;
--DROP TABLE test_table

Instead of blindly disabling auto_update statistics, I would try to examine your data set for skew. If your data exhibits significant skew, then you need to consider creating filtered statistics and then decide if managing statistics updates manually is the correct course of action.

To analyze for skew you need to run DBCC SHOW_STATISTICS(<stat_object>, <index_name>); (in the above script without the WITH STAT_HEADER) on the particular stat/index combination that you'd like to examine. A quick way to eyeball your skew would be to look at the histogram (third result set) and check out the variance in your EQ_ROWS. If it's fairly consistent then your skew is minimal. To step it up, you look at the RANGE_ROWS column and look at the variance there since this measures how many rows exist between each step. Finally, you can take the [All density] result from the DENSITY_VECTOR (second result set) and multiply that by the [Rows Sampled] value in the STAT_HEADER (first result set) and see what the average expectation would be for a query on that column would be. You compare that average to your EQ_ROWS and if there are many places where it varies significantly, then you've got skew.

If you find that you do have skew, then you need to consider creating some filtered statistics on the ranges that have high very high RANGE_ROWS so that you can give an additional steps for better estimates on those values.

Once you have these filtered statistics in place, then you can look at the possibility of manually updating statistics.


It's what is known in programming as a Ternary Operator. It's common in C/C++, JavaScript and PHP (among other languages).

As tombom states, the idea is that it is a short-hand if statement:

<condition> ? <true-case-code> : <false-case-code>;

SQL Server 2012 and above has it's own Ternary Operator in the form of the IIF statement.

IIF(@type = 2, 1, 0)


Composite index design where only a subset will be queried

Consider N partial indexes:

CREATE INDEX tbl_a_b1_idx ON tbl (A) WHERE B1;

All together only occupying slightly more space than a plain btree index on (A). You can have hundreds of indexes like that without problem. (I had a case with ~ 250.) The number is certainly not exhausted by the maximum of possible columns (250 - 1600 depending on column types).

Since each partial index is comparatively small, this will typically benefit performance as long as query patterns match.

Additional indexes may be useful and adding more index expressions may make sense to get index-only scans ....

Using a current version of Postgres is advisable in any case, but for this in particular, since there have been a number of optimizations for partial indexes lately.

To create all DDL statements:

SELECT format ('CREATE INDEX tbl_a_b%1$s_idx ON tbl(a) WHERE b%1$s;', g)
FROM generate_series(1, 24) g;

Returns:

CREATE INDEX tbl_a_b1_idx ON tbl(a) WHERE b1;
CREATE INDEX tbl_a_b2_idx ON tbl(a) WHERE b2;
...

Related:

PostgreSQL multi-column unique constraint and NULL values


How to change Zeros ( 0 ) to NULL in SSIS?

Use Derived Column Transformation with expression [test] == 0 ? NULL(DT_I8) : [test]


mysql show processlist hanging processes;

If the users were migrated by adding records to the mysql.user table, rather than by issuing GRANT statements, then you need to...

mysql> FLUSH PRIVILEGES;

...so that these new records will be read and used by the server.

(Yes, "flush" sounds like "delete" but it's not -- in MySQL, it's more like "close and re-open.")

The two threads you posted are not hung, they're idle.  Your "backup server" appears to be (or have been) a replica/slave server.  The first thread one is the replication IO_thread that reads events from the master, and the second one is the replication SQL_thread, that executes those events.  The second one looks normal; the first one is attempting to re-connect to the master server, which, I gather from your question, may now be offline.

They aren't affecting the inability to log in, but if you really want them to go away... this should do it...

mysql> STOP SLAVE;


How to avoid intermittent "duplicate entry" error on slave when replicating mysql.time_zone_transition?

I am not aware of any automatic method inserting data to those tables? Maybe you are trying to load data to a table that already contains them? Which OS are you? Maybe the operating system is doing it without telling you. Maybe someone setup a cron doing on the master:

shell>mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

https://mariadb.com/kb/en/mariadb/mysql_tzinfo_to_sql/

You can put a replication filter for now to avoid the issues, but then research the master's binaty log to understand who and where that is being inserted, and why. If those are legitimate, or it is automatized, make sure it is executed without writing to the binary log (SET SESSION sql_Log_bin=0)


If someone has a better answer or suggestion please let me know. Otherwise, I believe I found the answer in the pgsql-general mailing list;
https://www.postgresql.org/message-id/936117.1605644139%40sss.pgh.pa.us
Hi,
On Tue, 2020-11-17 at 21:00 +0100, Marcin Giedz wrote:

Hi all, trying to performe upgrade from 12 to 13 installed from
Centos8 repo gives such error:
cat loadable_libraries.txt
could not load library "$libdir/plpython2": ERROR: could not access
file "$libdir/plpython2": No such file or directory

I dropped PL/Python support along with PostgreSQL 13 RPMs.
Regards,
Devrim Gündüz
Open Source Solution Architect, Red Hat Certified Engineer
Twitter: @DevrimGunduz , @DevrimGunduzTR


Decomposition of a relation to 2NF then to 3NF

2NF: Remove Partial Dependencies

R = {A, B, C, D, E, F, G, H, I, J} includes partial dependencies.

D and E depend only on A, F depends only on B, G, H, I and J don't depend on the key (directly) at all.

  R0 = {A, B, C}

  R1 = {A, D, E, I, J}

  R2 = {B, F, G, H}

R0, R1, and R2 contain no partial dependencies (or repeating groups) so they are 2NF.  However R1 and R2 are still an issue, because they contain transitive dependencies.

3NF: Remove Transitive Dependencies

I and J depend on D, not on the key of R1.  Therefore you need to further normalize R1 as follows:

  R1 = {A, D, E, I, J}

  R1a = {A, D, E}

  R1b = {D, I, J}

Similarly, G and H depend only on F so R2 must be decomposed as follows:

  R2 = {B, F, G, H}

  R2a = {B, F}

  R2b = {F, G, H}

Now all of your remaining relations (R0, R1a, R1b, R2a, R2b) are devoid of repeating groups, partial dependencies and transitive dependencies.  That means your relations are in 3NF.

When you are looking at an relation that hasn't been normalized and a series of dependencies, you can often normalize by inspection just by recognizing what your primary keys are going to be.  Any attribute or combination of attributes that functionally determine other attributes are going to end up as primary keys.  Once you've got your primary keys defined, you just need to figure out which non-key attributes go with each key.  This is obvious from the statement of what your functional dependencies are.


Postgres 9.3 - Problem to initialize folder as a data folder

Creating the data directory on Linux includes creating hard links, which is not supported on CIFS shares. This is why the creation is failing.

It's usually not a good idea to create data stores / clusters on remote shares, it might lead to issues if the share disappears in the middle of using it.


View still depends on column even after I have removed the explicit (as well as transitive) dependencies?

TL;DR: The view definition statement is only evaluated once, not each time the view is queried.

It appears that I have misunderstood how DDL statements like CREATE OR REPLACE VIEW are evaluated.

I had assumed that the definition of the view (or at least, its SELECT clause) was reevaluated verbatim upon querying the view. From that point of view, the expanded form of

CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

would be

CREATE OR REPLACE VIEW my_view AS SELECT a, b, c FROM my_table;

before the column b was dropped, but

CREATE OR REPLACE VIEW my_view AS SELECT a, c FROM my_table;

after column b was dropped.

However, after my attempt to drop column b failed, I decided to inspect the interpreted definition of my_view.

postgres=# \d+ my_view
                 View "public.my_view"
 Column |  Type   | Modifiers | Storage  | Description
--------+---------+-----------+----------+-------------
 a      | integer |           | plain    |
 b      | text    |           | extended |
 c      | text    |           | extended |
View definition:
 SELECT my_table.a,
    my_table.b,
    my_table.c
   FROM my_table;

As it turns out, it seems the view definition is evaluated only once, and that the * wildcard from the SELECT clause was expanded to column list a, b, c upon evaluation. It seems this * wildcard is not re-evaluated as the underlying table has changed, contrary to my initial beliefs.

I assume this means I must redefine the view after the table's column list has changed.


SSAS How to calculate Standard Deviation

The StDev function does operate at a rollup grain per the first parameter you pass in. If you always want to calculate the StDev at the lowest level then you can write some mathematically equivalent measures and avoid the StDev MDX function. This approach is described here. Let me know if that doesn't make sense. The MDX would be:

(([Measures].[Sum of Square] - (([Measures].[Sum of Values]^2)/[Measures].[Count of Values]))/[Measures].[Count of Values])^0.5

See Peter's answer for a full explanation of the steps he took.

Also, if you do stick with the MDX approach you are currently using you will want to fix the first parameter as follows. Your code is factoring the grand total into the calculation. This MDX does not:

STDDEVP( {[Data Source].[Data Source Name].[Data Source Name].MEMBERS},[Measures].[Total])


There will be no difference - the query engine in mssql (and in most database engines) will build the same query plan for both.  You can verify this by viewing the estimated and actual query plan in SQL Workbench.


Primary key should be chosen so as attributes never change?

Keys are identifiers. They permit database users to identify the specific things about which information is being recorded in a database. If the values of key attributes are subject to change then there may be a risk of misidentification, particularly if the identifying information is also recorded elsewhere outside the database (e.g. in other databases, documents or in the mind of the users).

Sensible criteria to consider when choosing what keys to use are: Stability, Simplicity and Familiarity. These are good criteria for choosing any key, not just "primary" keys (the designation of a "primary" key is essentially arbitrary and a matter of convention or convenience - it doesn't mean those keys are fundamentally different to any other).

Stability, simplicity and familiarity are of course all subjective and relative criteria. Any attribute, whether key or not, might need to change and sometimes the ability to change a key attribute is a necessary part of maintaining accurate data and the correct operation of the systems that depend on the data. Very often there is no way to foresee a future requirement that may require such a change. It isn't necessary or even achievable to expect all keys never to change.

Uniqueness isn't really the issue because uniqueness needs to be enforced whether key values change or not. The uniqueness is implicit in the fact that it is a key and is maintained as such by the DBMS.


This could be an issue with a missing group in the Windows Local Groups folder.

Please ensure you have the following groups in your Computer Management (compmgmt.msc). Open up the relevant branches to reach the Groups branch:

Computer Management
    System
        Local Users and Groups
            Groups

Not all groups are required. The groups marked with an asterisk *) should be available in English even though your OS is French (or a different language) as it can cause issues. The groups marked with a +) should be there in any case.

Administrators *)
ORA_ADMADMIN
ORA_ASMDBA
ORA_ASMOPER
ORA_CLIENT_LISTENERS
ora_dba +)
ORA_DBSVCACCTS
ORA_GRID_LISTENERS
ORA_INSTALL
ORA_OPER
ORA_OraDB12Home1_DBA
ORA_OraDB12Home1_OPER
ORA_OraDB12Home1_SYSBACKUP
ORA_OraDB12Home1_SYSDG
ORA_OraDB12Home1_SYSKM
USERS +)

If you can't manually create the groups marked with *) and +) yourself, then you don't have enough privileges and should run the installation with a privileged or escalated ("RunAs Administrator...") account.

Depending on what you wish to achieve, you would also possibly need to add certain Window accounts to the individual Window groups. E.g. you might need to add your_account as a member of the ora_admin group.

Reference Material

Bug 17846528 CONNECTIONS THROUGH LISTENER HANG AFTER 2049 ATTEMPTS ON NON-ENGLISH SERVERS (Oracle Support)
Solution: Create a local "Administrators" group
(Oracle Support Account required to read bug article)


SQL Agent Jobs Automatically Disabled After Modifying Maintenance Plan

You haven't done anything wrong or gone crazy - it's a known bug and we've experienced the same thing.

Our process was changed that we would script out job status and reapply after any maintenance plan work - until we could move off of maintenance plans entirely (which we did).

Continuing on from the comments on the main question, there is an owner associated with the package itself which then gets copied over to the jobs and there's no UI to change it (you can modify it directly in the MSDB package tables though!)

We remediated that later on but I am unaware if there's a link between that referenced login disappearing and the jobs being disabled. It's possible.


Does the SQL Standard mention trigger events on DDL (ALTER, CREATE, DROP)?

The SQL Spec currently only says this about Triggers,

  4.6.5.5 Triggers

  A trigger is an object associated with a single base table or a single viewed table. A trigger specifies a subject table, trigger event, a trigger action time, and one or more triggered actions.

  A trigger event specifies what action on the subject table shall cause the triggered actions. A trigger event is either INSERT, DELETE, or UPDATE.
  A trigger action time specifies whether the triggered action is to taken BEFORE, INSTEAD OF, or AFTER the trigger event.
  A triggered action is either an <SQL procedure statement> or BEGIN ATOMIC, followed by one or more <SQL procedure statement>s terminated with <semicolon>s, followed by END.

Without any further mention, I believe the spec does not currently address this functionality at all.

Formatting slightly modified from the spec. I turned it into a list. Spec used "Information technology — Database languages — SQL —
Part 1: Framework (SQL/Framework)"


Why is MariaDB 10 refusing to use my index on a date field?

The optimizer seems to think that your index is not selective enough. I think that you can do one or more of the following things:

ANALYZE TABLE bets;
ALTER TABLE bets ADD INDEX bets_time_amount (time, amount); /* this is a covering index */
add a FORCE INDEX to your SELECT


Yes, Access on the client machine will connect directly to the linked tables located on SQL Server (or whatever ODBC back-end you're using). If you're dealing with forms and reports bound directly to these linked tables, it shouldn't cause any major performance problems. If you've written more complex queries within Access, then performance can depend on how well those have been optimized.

We've got a number of small Access "apps" running against SQL Server. They're great for when you just need to bang out a simple CRUD tool for a few people in an hour or two. Haven't had any real performance problems with them, though occasionally we run into a lock being held longer than it should.

My advice would be to take all the tables stored in that Access back-end and migrate them to SQL Server as well. Shared Access data files tend to be awful for performance and reliability.


Does timestamp protocol following thomas's write rule allow non-view-serializable schedules in some cases?

The given schedule S is:

T1    T2
----  -----
R(X)
      W(X)
W(X)

To be view serializable W2(X) must move before R1(X) or after W1(X). However, the first would violate the "initial read" rule and the second would violate the "last write" rule.

T2 starts after T1 so has a higher timestamp. When T1 comes to write X it sees that T2's timestamp on it. By Thomas' write rule the write by T1 is void and can be removed. The given schedule then becomes

T1    T2
----  -----
R(X)
      W(X)

Now all of T2's actions occur after all of T1's - the schedule is serialized.

(You have this the other way around in your question, removing W2(X). I think that is an error.)

To show that a schedule is serializable it is only necessary to show that there is at least one equivalent schedule that is serial. It is not necessary to show that every possible serial schedule is achievable. I agree that T2,T1 is not view serializable on the original schedule because of the read/ write dependency. However, T1,T2 is serializable under the Thomas write rule.


How can I clear output of a query in pgAdmin III?

I found this to be an interesting "lapse", if not bug, in the (normally excellent) pgAdmin3 software.

I double checked and it is is not possible to do what you want on Windows. Update, nor is it possible on Linux. Maybe a feature request? Though, at least the behaviour is consistent: ;-)

With respect to a feature request - it appears that the list is indeed the recommended method of submission. If you provide a link here, I can update with either the Linux or Windows behaviour - whichever you may not have.


How to do data migration between different schemas?

Did I understand this correctly that your customer has MSSQL Server?

Source Database MSSQL or other

You have two options:

Script your own replication
Use Oracle Golden Gate to stream the data to your Oracle Database. Golden Gate supports "zero downtime" migration.

Source Database Oracle

Additionally to the options mentioned in "Source Database MSSQL or other" you can do:

Use Streams, Basic or Advanced Replication Features (requires Enterprise Edition, Basic Replication is included in SE, SE1 and SE2)
Export/Import DataPump (this does not support continuous replication)
Import DataPump via database link
Transportable Tablespace (requires Enterprise Edition)

There are several other posibilites but I need more details to recommend you one method:

How big is the customer's database? Please use the view dba_tables to get this information. The size of the datafiles does not reflect the actual size of a database.
How fast can you copy 1GB from the customer's side to your side?
Do you want to convert and cleanup the data while transfering it to your side?
Which method did you use when it took 6 days?


Is it possible to attach Oracle database files after a clean install

ORADIM is what you need: Creating an Oracle service on Windows


How can I recover all the Stored procedures, Functions, Events from previous server version

Install the previous version of MySQL you have migrated from.
Import the dumpfile you have made.
Dump again in the proper way.
Install the newer MySQL version.
Import your proper backup.


After much floundering around with the information_schema.tables table, I finally discovered the answer to your problem - update time doesn't work for InnoDB. See the points here by a.out and ajacian81.

The only way you can do this is to implement logging tables (as suggested by Bill Karwin, a bit of a MySQL/database guru).

The only other thing that I can think of is to use a backup and use a diff programme to compare the text output of queries from your current and backup database. Horrible I know, but there are tools which can do this if it's critical. There's MySQL's own (MySQLdiff - in utilities), you could try Liquibase or Flyaway (which I believe have this functionality) or Google "database diff programmes mysql".

I also found this post helpful in realising that the information_schema tables are dynamic.

Maybe I'm a bit OCD too (can't let this go! :-) ) but I also found this thread - check out the answer by Jacket - Percona may be the way to go if you are in real need of this functionality. However, it's obviously too late for your particular situation. I think it's backups for you :-(.


deploy data from sql 2016 backup to azure database

You need a live DB to export to Azure, which would require you to restore it locally. Once that is done, you can create a .BACPAC file on Azure storage and restore it, or set up transactional replication, and there are a few other methods as well.

The .BACPAC migration process looks to be the simplest, even if it isn't that simple. Honestly, this page explains it better than I can. Bruno Terklay is at least a minor demigod over at Microsoft, and this is really well written. All credit to him, not me.


SQL Server 2012 Unable to Utilize Defined Amount of Locks

Is there any trace flag enabled or any tweaking into startup parameters of SQL server can you please check that.Have a look at below Microsoft support article

http://support.microsoft.com/kb/2199576/en-us
Keep max lock value to default

EXEC sp_configure 'locks', 0 ( I guess you tried this option already)

Now if you face this issue again best possible workaround will be to decrease the amount of rows being transferred. Every lock has memory associated with it so if there is lock contention I believe its somehow related to SQL Server buffer pool memory. Did you run DBCC MEMORYSTATUS and check value for

OBJECTSTORE_LOCK_MANAGER (Total)       KB

please do tell us what is value of single page and multi page allocator for above clerk


List all the MS SQL Server Objects ->LinkedServers->Providers using SQL

Executing the stored procedure sp_MSset_oledb_prop without parameters will return a table of the available providers and their options:

EXEC sp_MSset_oledb_prop


Okay so here's what I think you need to do. I'm assuming since you are already using integration services you have both of your data sources set so I'm not going into detail on that. If you have any questions as to how to properly set those up I can help you to. So the first thing you need to do is create a new package in your Integration services solution. Then you need to create a dataflow task. You will find that in your You toolbox like so .

Once you have done that open the DataFlowTask and Create a new OLEDB Connection. Once again found in the toolbox.

Open that Up and set up your SQL command this will run locally on the machine and will therefore suffer no linked query costs.

Next Set up your OLEDB command the portion that will execute a SQL command with the result Set. First Select OLEDB Command from the toolbox.

Connect your OLE Connection to You OLE Command.

Next you create a stored procedure to handle this update. Its a fairly simple procedure to make it will ultimately look like this.

CREATE PROCEDURE UPD_CRM_TableName(
    @ID int,
    @Field int)

UPDATE TableName SET
@Field = Field

WHERE @ID = ID

Then open you OLEDB command and select the appropriate connection in the connection tab.

Go to the Component properties tab click on SQL Command and the appropriate values to execute your stored procedure.

Then go to column mappings and make your parameters match you columns and you will be good to execute the package. Note that the update stored procedure will be sent to the server with the appropriate parameters required to update the table eliminating the need for your costly linked query.

Setting this up for any update you command will be incredibly easy to set up and will basically run as fast as your hardware and network can keep up. Make sure your integration services box has plenty of memory as that is where all of it's operations are executed. If you have any questions feel free to let me know.


You cannot.  These are different operations, and only one of them is supported by the index.  If you were to swap the order of the arguments as well as the operator itself, then it would be the same operation and the index would still support it.

Although it might be possible that you could modify the source code of pg_trgm to support this other operation.  I doubt it would be very efficient, though.


@a_horse_with_no_name is right in the comments, from the docs

  SPI_execute_with_args executes a command that might include references to externally supplied parameters. The command text refers to a parameter as $n, and the call specifies data types and values for each such symbol. read_only and count have the same interpretation as in SPI_execute.

So you can do

SELECT * FROM partition_master WHERE foo = 5;

or using prepared statements,

SELECT * FROM partition_master WHERE foo = $1;

But you can't do...

SELECT * FROM whatever AS w
JOIN partition_master AS p
WHERE p.foo = w.bar;


Restore encrypted database to another server

I wrote a blog entry about mirroring and TDE.

It took me a while to figure out that to get the db on the secondary server, I needed the code line:

 OPEN MASTER KEY DECRYPTION BY PASSWORD = 'SomePassword'
 ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
 GO

The password is the same one that I used to encrypt the master key file. After issuing the command on the 2nd SQL server, everything played nicely. Here is the db.stackexchange post I made also.


Adding Time Limit parameter to Ola hallengren Index Maintenance

If a command is already running and the time limit reach it: will continue and will not run any command after.
To answer your question I saw stored procedure definition. Find keyword @TimeLimit


Subquery can't find column from superquery's join

The cause of the problem was identified by @Phil in the comments:

  Probably because it's nested too deep

You have 2 layers of nesting and the reference of table e cannot "see" through these 2 layers in MySQL.

Correlated inline subquery can usually be converted to derived tables and then LEFT joined to the other tables in the FROM clause but they have to be turned into uncorrelated (in MySQL. In other DBMS, you could use a LATERAL join or the similar OUTER APPLY.

A first rewrite to get the job done:

SELECT
    CONCAT_WS(
        ', ', count(DISTINCT CONCAT(q.emailaddress, '_', e.id)),
        dv.OneTimeItems
    ) as AllItems,
    e.id,
    LEFT(e.firstname, 1) as voorletter,
    e.lastname
FROM question q
LEFT JOIN employee e ON q.employee_id = e.id
LEFT JOIN
    (
        SELECT company_id,
               GROUP_CONCAT(items SEPARATOR '; ') AS OneTimeItems
        FROM (
            SELECT oi.company_id,
                   CONCAT_WS(
                ': ', oi.item_name, SUM(oi.item_amount)
            ) items
            FROM onetime_item oi
            WHERE oi.date BETWEEN '2015-12-01'
                              AND LAST_DAY('2015-12-01')
            GROUP BY oi.company_id, oi.item_name
        ) resulta
        GROUP BY company_id
    ) AS dv
    ON dv.company_id = e.company_id
WHERE 1=1
AND YEAR(q.created_at) = '2015'
AND MONTH(q.created_at) = '12'
GROUP BY e.company_id ;

Test in SQLfiddle.

Unrelated to the issue comments:

There is GROUP BY e.company_id while the select list has e.id,  LEFT(e.firstname, 1), e.lastname. All these will give arbitrary result from a (more or less random) employee for each company - or even in extremely rare cases arbitrary results from 2 or 3 different employees! MySQL allowed (before 5.7) such bad use of group by that could cause erroneous results. It has been fixed in 5.7 and the default settings would reject this query.
The condition:

YEAR(created_at) = '2015' AND MONTH(created_at) = '12'

cannot make use of indexes. It's better to rewrite with either BETWEEN if the column is of DATE type of with an inclusive-exclusive range condition, which works flawlessly with any datetime type (DATE, DATETIME, TIMESTAMP) of any precision:

-- use only when the type is DATE only
date BETWEEN '2015-12-01' AND LAST_DAY('2015-12-01')

or:

-- use when the type is DATE, DATETIME or TIMESTAMP
created_at >= '2015-12-01' AND created_at < '2016-01-01'


Dynamic script to Drop Indices of a specific column

Since you are planning to DROP a column and the indices that support it, I'll assume that doing so is a product of well-thought plan.  Also, I'll assume that you want to drop ANY indice that use the to-be-deleted column, regardless of how it is used.   If so, you can build your script based upon something like below.  You can put this in a stored procedure and pass the parameters, TableName, ColumnName, and SchemaName in this example.

Now, before you run this or any modified version of it, please be clear that once this script starts, all associated indices plus the column will be dropped, and such decision shouldn't be taken lightly.

I'm assuming you have already done your homework, but please allow me.  One situation that might become a problem is when the column you want to drop (C) is a part of a multi-column index that supports a popular query.  For example, let's consider the following structure.

CREATE NONCLUSTERED INDEX nci_tableA_ABC ON dbo.A(A,B,C) INCLUDE (D,E);

SELECT D,E FROM dbo.A WHERE A=@? AND B=@? AND C=@?

If this query is very frequently used hence the index is frequently used, dropping this index will severely impact the performance, and the index needs to be manually recreated albeit without the column C.  I'm sure there are other risks to consider.

Also please note that this script is completely void of error trapping or constraint checking.  If the column you are interested has constraint(s), that need to be dropped before the column can be dropped. You can use sys.objects table to construct dynamic SQL.  (and this might help https://stackoverflow.com/questions/13818837/how-to-check-if-default-value-constraint-exist-not-exist)

SET NOCOUNT ON;

IF OBJECT_ID(N'Tempdb..#IndexList', 'U') IS NOT NULL
    DROP TABLE #IndexList;
GO
DECLARE
    @SchemaName SYSNAME,
    @DropThisIndex SYSNAME,
    @DropThisColumn SYSNAME,
    @CheckThisTable SYSNAME,
    @LoopCounter INT,
    @IndexCount INT,
    @DropColumnSQL NVARCHAR(1000);

SET @SchemaName = N'SchemaName'
SET @CheckThisTable = @SchemaName + '.' + N'TableName';
SET @DropThisColumn = N'ColumnName';
SET @LoopCounter = 1;
SET @DropColumnSQL = 'ALTER TABLE ' + @CheckThisTable + ' DROP COLUMN ' + @DropThisColumn

--First check if the column even exists--

IF EXISTS(SELECT * FROM sys.columns
            WHERE Name = @DropThisColumn AND Object_ID = Object_ID(@CheckThisTable))

    BEGIN --the column exists in the specified table Construct a temporary table that stores name of index to be dropped--
        SELECT
            I.name AS IndexName
        INTO #IndexList
        FROM
            sys.index_columns IC
            INNER JOIN
            sys.objects O
        ON  IC.[object_id]= O.[Object_ID]
            INNER JOIN
            sys.indexes I
        ON  IC.[object_id] = I.[object_id] AND
            IC.index_id = I.index_id
            INNER JOIN
            sys.columns C
        ON  IC.[object_id] = C.[object_id] AND
            IC.column_id = C.column_id
        WHERE O.[type] NOT IN ('S','IT') AND --we don't want anything to do with System or Internal Table
            IC.[INDEX_ID] <> 1 AND --neither Clustered Index
            IC.[object_id] = OBJECT_id(@CheckThisTable,'U') AND
            C.name = @DropThisColumn;

        --Check how many times to loop through--
        SET @IndexCount = @@ROWCOUNT;
        PRINT CAST(@IndexCount AS NVARCHAR) + N' index(es) on the table [' + @CheckThisTable + N'] for the column [' +  @DropThisColumn + '] found'

        IF @IndexCount > 0 --there is at least one index(es) that are associated with the table and the column in question.  Begin loop--
            BEGIN
                WHILE @LoopCounter < @IndexCount+1
                    BEGIN
                        SELECT TOP(@LoopCounter)
                            @DropThisIndex = IndexName
                        FROM #IndexList;
                        --Execute Drop Index statement--
                        PRINT N'Loop ' + CONVERT(NVARCHAR,@LoopCounter) + N': Dropping [' + @DropThisIndex + ']'
                        EXEC('DROP INDEX ' + @DropthisIndex + ' ON ' + @CheckThisTable);
                        SET @LoopCounter +=1;
                    END;
                --Dropping Index is done. Let's drop the column now
                PRINT N'Dropping the column [' + @DropThiscolumn + ']'
                EXEC(@DropColumnSQL);
            END;
        ELSE --There was no index.  let's just drop the column.
            BEGIN
                PRINT N'Dropping the column [' + @DropThiscolumn + ']'
                EXEC(@DropColumnSQL);
            END
    END
ELSE --no column found in the specified table.  Are the input corrects?
    PRINT 'The column [' + @DropThisColumn + '] in the table [' + @CheckThisTable + '] wasn''t found'
;

Now, if your goal is to drop ONLY the indices that has the to-be-dropped column as the key (left most key that is), then you can modify #IndexList  table like below and filter unwanted row, like Clustered Index, before your get the loop count.

CREATE TABLE #IndexList
    (IndexName SYSNAME,
    IndexType NVARCHAR(255),
    Index_Key SYSNAME)

INSERT INTO #indexlist
EXEC sys.sp_helpindex N'TableName';


On Apri 18, 2012, I addressed a similar question : MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?
I mentioned the following

Bytes_sent : IO Thread requesting binlog entries from the Master
Bytes_received : IO Thread reading binlogs entries from its Master
Bytes_received : SQL thread reading its own relay logs

You can find out what these are for all connections past and present by doing
SELECT * FROM information_schema.global_status
WHERE variable_name IN ('Bytes_received','Bytes_sent');

You can do so an individual session by doing
SELECT * FROM information_schema.session_status
WHERE variable_name IN ('Bytes_received','Bytes_sent');

I have good news and bad news. First ...
BAD NEWS
You cannot get a SQL thread to query information_schema.session_status. That's a violation of the first and fifth amendments (if applied against Replication's SQL thread).
GOOD NEWS (Sort of)
The only way you can track any kind of byte traffic is to do the following

Enable Binary Logging on the Slave

Dump all binlogs on the Slave using
mysqlbinlog --server-id=(server_id of the Master) ... > Binlogs_From_Master.txt

Take the filesize of Binlogs_From_Master.txt and that's your best guess.

Subtract that filesize from whatever Bytes_received is, and that's the internal byte traffic on the Slave.
CAVEAT
You will have to set up replication to find that number out.
As an alternative, you could just use one the the following from the Master

Bytes_sent
Bytes_received
Bytes_sent + Bytes_received

as an educated guess.
Give it a Try !!!


I base my idea on generating the records for each interval.For this I use master..spt_values.( you can use anything else, that generate a list of numbers 0,1,2...).

So , for interval 60-46, generate 3(frequency) records.Those records have the starting point = the event date - 60 days. (This is the date when you start to send emails)

DECLARE @dtCurrentDT AS DATETIME ;
SET @dtCurrentDT = '20160402';
--SET @dtCurrentDT = '20160403';

WITH EventsCTE AS
( SELECT '20160601' AS EventPromotion)
,RemindersCTE AS
(SELECT 60  aS DayFrom,46 AS Dayto, 3 AS Frequent UNION ALL
 SELECT 45,37,3 UNION ALL
 SELECT 36,31,6)

SELECT
    R.DayFrom
    ,R.Dayto
    ,R.Frequent
    ,DATEADD(DAY,
            ((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number ,
            DATEADD(DAY,-R.DayFrom,E.EventPromotion)) AS StartDateEvent_ToSendMail
    --,CA.number
    --,DATEADD(DAY,((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number , @dtCurrentDT) AS RemainderDay
    --,DATEADD(DAY,-R.DayFrom,E.EventPromotion) AS Event_StartDate
FROM
    RemindersCTE AS R
    CROSS APPLY
    (SELECT TOP(R.Frequent)
        V.number
     FROM
        master..spt_values AS V
     WHERE
        V.type ='P'
     ORDER BY
        V.number ASC
     )CA
     CROSS JOIN EventsCTE AS E
WHERE
    DATEADD(DAY,
            ((R.DayFrom-R.DayTo + 1)/ R.Frequent) * CA.number ,
            DATEADD(DAY,-R.DayFrom,E.EventPromotion))
    >= @dtCurrentDT;

My output is :

DayFrom     Dayto       Frequent    StartDateEvent_ToSendMail
60          46          3           2016-04-02 00:00:00.000
60          46          3           2016-04-07 00:00:00.000
60          46          3           2016-04-12 00:00:00.000
45          37          3           2016-04-17 00:00:00.000
45          37          3           2016-04-20 00:00:00.000
45          37          3           2016-04-23 00:00:00.000
36          31          6           2016-04-26 00:00:00.000
36          31          6           2016-04-27 00:00:00.000
36          31          6           2016-04-28 00:00:00.000
36          31          6           2016-04-29 00:00:00.000
36          31          6           2016-04-30 00:00:00.000
36          31          6           2016-05-01 00:00:00.000


In my opinion, your best bet would be to upgrade to Enterprise Edition and implement AlwaysOn Availability Groups. Depending on how much reporting you do and how high the availability needs to be, you might even want to set it up with two secondary replicas (for a total of three nodes).
Alternatively if you do stick with Standard Edition, aside from Log Shipping and regular FCI, you can also use Replication to synchronize your data across multiple servers in near real-time, especially since your OLTP is not that much, actually. SSIS is another way you can synchronize data between servers too, though it's a bit more clunky and might not be as real-time as Replication.
Basic Availability Groups are also an option in Standard Edition. It's not really a "group" since each AG can only contain 1 DB, and only span 2 nodes, among some other limitations--but essentially provide the same functionality previously supported via mirroring.


parent child records - cascade deletes or transactions?

This depends on the needs of the data and is also a design preference. Having CASCADE ON DELETE can be a cleaner implementation and better describe the relationship of the data.

The research that this article presents shows that CASCADE ON DELETE actually has a slight performance decrease over a straight delete in SQLite. But unless you have a consistently high load, it shouldn't be an issue.

Database    Speedup    CPU decrease
MySQL         45%        25%
Oracle        28%        11%
PostgreSQL    56%        55%
SQL Server    17%        22%
SQLite        -11%      -75%

http://www.codesynthesis.com/~boris/blog/2012/04/12/explicit-sql-delete-vs-on-delete-cascade/


SQL Server Bulk Insert properly interprets some Unicode characters but not others?

This is not a bug in SQL Server (or even in Windows), nor is it a situation that requires the additional step of converting the file into another encoding (i.e. into "Unicode", which in Windows-world means "UTF-16 Little Endian"). It is just a simple miscommunication.

The source of the communication breakdown (it's always the same, right ;-) is merely not agreeing on the nature of the source data. When moving character data from one place to another, it is important to specify the encoding on both sides. Yes, the SQL_Latin1_General_CP1_* Collations use Code Page 1252. However, if you don't tell BULK INSERT or BCP.exe what the Code Page of the source file is, then they will assume that the Code Page is the system default.

The documentation for BULK INSERT even states (for the CODEPAGE = argument):

  'OEM' (default) = Columns of char, varchar, or text data type are converted from the system OEM code page to the SQL Server code page.

The documentation for BCP.exe states (for the -C switch):

  OEM = Default code page used by the client. This is the default code page used if -C is not specified.

And the default Code Page for Windows is (at least for US English systems): 437. You can see this if you execute the following in a Command Prompt:

C:\> CHCP

It will return:

Active code page: 437

But your source file wasn't encoded using Code Page 437. It was encoded using Code Page 1252.

So here is what is happening:

Bytes are bytes. And bytes representing character data can only be interpreted via an encoding. Anything reading a file doesn't read characters from the file, it reads the bytes of the file and displays the characters based on the encoding that is specified.
BULK INSERT / BCP reads in byte 0xC9. 0xC9 displays as É when using Code Page 1252.
BULK INSERT / BCP is not given a source Code Page, so it checks the current Code Page for the process and is told: 437.
BULK INSERT / BCP now has a byte of 0xC9 for Code Page 437 (which displays as ╔, but BULK INSERT / BCP isn't displaying it so you won't see this)
BULK INSERT / BCP inserts this data into a column using a Collation that specifies Code Page 1252.
SQL Server sees that the incoming data is using a different Code Page than the destination uses and so has to convert the incoming data such that the characters appear to be the same (as much as possible), even if the underlying values change.
The Code Page 437 to Code Page 1252 mapping indicates that byte 0xC9 maps to byte 0x2B. Similarly, byte 0xAE (which is ® on Code Page 1252) on Code Page 437 (which displays as «) maps to byte 0xAB on Code Page 1252 (because it also displays as «).

The following example shows this conversion for all of the characters noted in the question:

DECLARE @CodePageConversion TABLE
(
   [ActualSource_CP1252] AS CONVERT(VARCHAR(10), CONVERT(BINARY(1),
                    [PerceivedSource_CP437])) COLLATE SQL_Latin1_General_CP1_CI_AS,

   [PerceivedSource_CP437] VARCHAR(10) COLLATE SQL_Latin1_General_CP437_CI_AS,

   [Source_Value] AS (CONVERT(BINARY(1), [PerceivedSource_CP437])),

   [Destination_CP1252] AS (CONVERT(VARCHAR(10), [PerceivedSource_CP437]
                  COLLATE SQL_Latin1_General_CP1_CI_AS)),

   [CP1252_Value] AS (CONVERT(BINARY(1), CONVERT(VARCHAR(10),
                  [PerceivedSource_CP437] COLLATE SQL_Latin1_General_CP1_CI_AS)))
);

INSERT INTO @CodePageConversion
VALUES      (0xC9), (0xA1), (0xA0), (0xAE), (0xCB), (0xD1), (0x92), (0x96);

SELECT * FROM @CodePageConversion;

which returns:

ActualSource_CP1252  PerceivedSource_CP437  Source_Value  Destination_CP1252  CP1252_Value
É                    ╔                      0xC9          +                   0x2B
¡                    í                      0xA1          í                   0xED
                     á                      0xA0          á                   0xE1
®                    «                      0xAE          «                   0xAB
Ë                    ╦                      0xCB          -                   0x2D
Ñ                    ╤                      0xD1          -                   0x2D
’                    Æ                      0x92          Æ                   0xC6
–                    û                      0x96          û                   0xFB

The characters for 0xC9, 0XCB, and 0xD1 do not exist in Code Page 1252, so the "best fit" mappings are used, which is why you end up with the + and - characters after the conversion.

Also, even if the destination column is using NVARCHAR, all of these mappings are the same, so you would be seeing the exact same behavior.

So, your choices are:

If using the T-SQL BULK INSERT command, specify the WITH CODEPAGE = option with one of the following values:

'ACP' (this is the same as '1252')
'RAW' (this uses the Code Page of the column's Collation if inserting into VARCHAR, or is the same as 'OEM' / Code Page 437 when inserting into  NVARCHAR)
'1252' (this is the same as 'ACP')

Or, if using BCP.exe, indicate that the incoming file uses Code Page 1252 via the -C command-line switch along with one of the following values (see notes in option #1):

ACP
RAW
1252

Please note that:

I tested with BULK INSERT, inserting into a VARCHAR column, using the set of characters noted in the question, and the ACP (which I believe stands for ANSI Code Page), RAW, and 1252 values all produced the correct results.
Not specifying WITH CODEPAGE = produced the same results that the O.P. reported in the question. This was the same as specifying WITH CODEPAGE = 'OEM'.
When inserting into an NVARCHAR column, both ACP and 1252 worked as desired, but RAW produced the same result as OEM (i.e. using Code Page 437 instead of Code Page 1252 which is specified by the Collation of the column).
I tested using BCP.exe, and not specifying the -C switch did not use the Code Page of the process. Meaning, using CHCP to change the command prompt's Code Page had no effect: Code Page 437 was still used as the source Code Page.

P.S. Since the data here is all 8-bit encoded, there are no "Unicode characters" since there is no Unicode being used.


What is the SQL Server equivalent of MySQL's utf8mb4 character set?

First: the particular "edition" of SQL Server (i.e. Express, Standard, Enterprise, etc) does not matter. All editions for a particular version will behave the same.

  When I switch to "Standard" it chooses the Latin1_General_CI_AS collation.

Well, that is better than a collation starting with SQL_, but still not ideal. If you are using SQL Server 2008 or newer, then you should be using a version 100 collation (or a  version 140 collation IF using SQL Server 2017 or newer and specifying a Japanese collation). AND, if using SQL Server 2012 or newer, then you should be using a collation that supports supplementary characters, which means your choices are:

version 100 collations with names ending in _SC, or
version 140 collations (only Japanese collations have a version 140 set, but none of these end in _SC since supplementary character support is built into all version 140 collations)

In your case, most likely you want: Latin1_General_100_CI_AI_SC

Technically speaking, the closest equivalent to utf8mb4_general_ci (there is no utf8mb4_Standard, and your screen shot even shows utf8mb4_general_ci) is Latin1_General_CI_AI. Reason being:

The utf8mb4 character set allows you to store supplementary characters (which NVARCHAR also does, regardless of collation),
the general part of the MySQL collation means that supplementary characters all have the same weight. This pre-level 100 SQL Server collation is similar in that all supplementary characters have the same weight, it's just that they have no weight at all.
the ci in the MySQL collation implies ai since as isn't specified.

Still, you want to stick with: Latin1_General_100_CI_AI_SC.

  I do not know which character encoding this is and how it would affect the scenario if we would like to take over some data from utf7mb8 MySQL tables into SQL Server.

The encoding is determined by a combination of the datatype and the collation:

NVARCHAR (and NCHAR / NTEXT) is always UTF-16 LE (Little Endian).
VARCHAR (and CHAR / TEXT) is always an 8-bit encoding, the specific encoding determined by the code page associated with the collation being used.

That said, it shouldn't matter what the source encoding is as long as the destination encoding can handle all of the incoming characters (and behave in a similar manner, of course, which is where the culture and sensitivities come in). Assuming you will be storing everything in NVARCHAR (maybe occasionally NCHAR, but never NTEXT as that has been deprecated since SQL Server 2005), then the data transfer tool will handle any necessary conversions.

  what does the collation Latin1_General_CI_AS mean?

It means:

Because the name does not start with SQL_, this is a Windows collation, not a SQL Server collation (and this is a good thing as the SQL Server collations — those starting with SQL_ — are mainly for pre-SQL Server 2000 compatibility, although quite unfortunately SQL_Latin1_General_CP1_CI_AS is very common due to it being the default when installing on an OS using US English as its language)
Latin1_General is the culture / locale.

For NVARCHAR data this determines the linguistic rules used for sorting and comparison.
For VARCHAR data this determines the:

linguistic rules used for sorting and comparison.
code page used to encode the characters. For example, Latin1_General collations use code page 1252, Hebrew collations use code page 1255, and so on.

{version}, while not present in this collation name, refers to the SQL Server version in which the collation was introduced (for the most part). Windows collations with no version number in the name are version 80 (meaning SQL Server 2000 as that is version 8.0). Not all versions of SQL Server come with new collations, so there are gaps in the version numbers. There are some that are 90 (for SQL Server 2005, which is version 9.0), most are 100 (for SQL Server 2008, version 10.0), and a small set has 140 (for SQL Server 2017, version 14.0).

I said "for the most part" because the collations ending in _SC were introduced in SQL Server 2012 (version 11.0), but the underlying data wasn't new, they merely added support for supplementary characters for the built-in functions. So, those endings exist for version 90 and 100 collations, but only starting in SQL Server 2012.
Next you have the sensitivities, that can be in any combination of the following, but always specified in this order:

CS = case-sensitive or CI = case-insensitive
AS = accent-sensitive or AI = accent-insensitive
KS = Kana type-sensitive or missing = Kana type-insensitive
WS = width-sensitive or missing = width insensitive
VSS = variation selector sensitive (only available in the version 140 collations) or missing = variation selector insensitive

Optional last piece:

_SC at the end means "Supplementary Character support". The "support" only affects how the built-in functions interpret surrogate pairs (which are how supplementary characters are encoded in UTF-16). Without _SC at the end (or _140_ in the middle), built-in functions don't see a single supplementary character, but instead see two meaningless code points that make up the surrogate pair. This ending can be added to any non-binary, version 90 or 100 collation.
_BIN or _BIN2 at the end means "binary" sorting and comparison. Data is still stored the same, but there are no linguistic rules. This ending is never combined with any of the 5 sensitivities or _SC. _BIN is the older style, and _BIN2 is the newer, more accurate style. If using SQL Server 2005 or newer, use _BIN2. For details on the differences between _BIN and _BIN2, please see: Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2).
_UTF8 is a new option as of SQL Server 2019. It's an 8-bit encoding that allows for Unicode data to be stored in VARCHAR and CHAR datatypes (but not the deprecated TEXT datatype). This option can only be used on collations that support supplementary characters (i.e. version 90 or 100 collations with _SC in their name, and version 140 collations). There is also a single binary _UTF8 collation (_BIN2, not _BIN).

PLEASE NOTE: UTF-8 was designed / created for compatibility with environments / code that are set up for 8-bit encodings yet want to support Unicode. Even though there are a few scenarios where UTF-8 can provide up to 50% space savings as compared to NVARCHAR, that is a side-effect and has a cost of a slight hit to performance in many / most operations. If you need this for compatibility, then the cost is acceptable. If you want this for space-savings, you had better test, and TEST AGAIN. Testing includes all functionality, and more than just a few rows of data. Be warned that UTF-8 collations work best when ALL columns, and the database itself, are using VARCHAR data (columns, variables, string literals) with a _UTF8 collation. This is the natural state for anyone using this for compatibility, but not for those hoping to use it for space-savings. Be careful when mixing VARCHAR data using a _UTF8 collation with either VARCHAR data using non-_UTF8 collations or NVARCHAR data, as you might experience odd behavior / data loss. For more details on the new UTF-8 collations, please see: Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?

  Especially how would this behave if you had Chinese characters, for example?

If you are storing those characters in a VARCHAR column or variable, you would most likely lose those characters as they get converted to ?. There are a few Chinese locale collations that use a double-byte character set (DBCS) for VARCHAR that can store more than 256 different characters, but it's still not even close to what is available in Unicode.
If you are storing those characters in an NVARCHAR column or variable, there would be no data loss. However, with the Latin1_General culture / locale (Western European / US English), you won't get any Chinese-specific linguistic rules, so sorting and comparisons of the Chinese characters (anything different than what is in the default definition) will likely not behave as would be appropriate for that language. In which case you simply use a Chinese collation, version 100, and with _SC in the name.


You need to change the delimiter to accept semicolons properly while creating the trigger

DELIMITER $$
CREATE DEFINER = CURRENT_USER TRIGGER `movimentacao_before_del_tr` BEFORE DELETE ON `movimentacao`
  FOR EACH ROW
 IF (OLD.stMov IN ("E", "C", "R")) THEN
    INSERT INTO `controle` (
      `tabela`,
      `data`,
      `hora`,
      `registro`
    ) VALUES (
      MD5("movimentacao"),
      OLD.dtEmissao,
      CURTIME(),
      CONCAT_WS("|",
        OLD.idMovimentacao,
        OLD.idEcf,
        OLD.numFabricacao,
        OLD.CCF,
        OLD.stMov,
        OLD.tpNota,
        OLD.modoMov,
        OLD.dtMovimentacao,
        OLD.dtEmissao,
        OLD.hrEmissao,
        OLD.numNF,
        OLD.idPessoa,
        OLD.cpfCnpj,
        OLD.rgIe,
        OLD.nomePessoa,
        OLD.codcidade,
        OLD.uf,
        OLD.cidade,
        OLD.bairro,
        OLD.logradouro,
        OLD.numero,
        OLD.complemento,
        OLD.cep,
        OLD.totalBruto,
        OLD.valAcrecimo,
        OLD.valDesconto,
        OLD.valOutrasDespesas,
        OLD.valFrete,
        OLD.valSeguro,
        OLD.bcICMS,
        OLD.valICMS,
        OLD.bcICMSST,
        OLD.valICMSST,
        OLD.valPIS,
        OLD.valCOFINS,
        OLD.totalLiquido,
        OLD.idNatOp,
        OLD.idAidf,
        OLD.especie,
        OLD.serie,
        OLD.docProprio,
        OLD.chaveNFe,
        OLD.motivoCancelamento,
        OLD.hrMovimentacao,
        OLD.IdTransportador,
        OLD.modFrete,
        OLD.qtdeVolumes,
        OLD.especieVolumes,
        OLD.marcaVolumes,
        OLD.pesoL,
        OLD.pesoB,
        OLD.infAdFisco,
        OLD.infCpl,
        OLD.enderecoTransp,
        OLD.cidadeTransp,
        OLD.UfTransp,
        OLD.finNFe,
        OLD.placaVeicTransp,
        OLD.ufVeicTrans,
        OLD.RNTC,
        OLD.idOperador,
        OLD.chk,
        OLD.hash,
        OLD.hash2
      )
    );
  END IF $$
DELIMITER ;

Give it a Try !!!


Incorrect query filter according to the requirement

The problem is with the below bind variable substitution:

AND ( sv.project_id                 = DECODE(:'  1368','0', project_id, :'1368')
AND TO_CHAR (date_in, 'dd-MM-yyyy') = DECODE(:'  ', '0', date_in, :'  ')
AND vehicle_no                      = DECODE(:'  GJ-06-0Z-9202', '0', vehicle_no, :'  GJ-06-0Z-9202') ))

Your application should pass the value to the bind variable correctly. The query you need to use is:

AND sv.project_id = DECODE(:project_id, 0, project_id, :project_id)
AND date_in       = DECODE(:date_in, 0, date_in, :date_in)
AND vehicle_no    = DECODE(:vehicle_no, 0, vehicle_no, :vehicle_no)

The above DECODE is just like an IF-ELSE construct. It means, if the passed values are 0, then use the column value from the table, else, use the value that is passed.

Your application need to assign values to the following bind variables:

:project_id
:date_in
:vehicle_no

For example, in SQL*Plus:

SQL> var num number
SQL> exec :num := 10

PL/SQL procedure successfully completed.

SQL> print num

       NUM
----------
        10

SQL> var text varchar2(10)
SQL> exec :text := 'coder'

PL/SQL procedure successfully completed.

SQL> print text

TEXT
--------------------------------
coder

Also, no need to use TO_CHAR for the DATE. Pass the value as DATE. Else, use TO_DATE on the literal on the right hand side. Leave the date as it is, since you should compare the dates and not strings. TO_CHAR converts the date into string in your desired format. You need TO_CHAR only to display the date, not while comparing or date arithmetic.

UPDATE :

Your application should pass the value to the bind variable properly. After passing the value, your query still has the bind variable symbol :.

  :'  1368'

If you capture the bind variable value, then it should just be:

  '1368'

Also, there is a leading space in the value. Although, it won't throw any error, it would give you incorrect output.


There is absolutely nothing about this on the Internet - even the Oracle docs regarding the %_ASSEMBLIES views don't actually state what they show.

However, if you go digging around in $ORACLE_HOME/rdbms/admin you'll see the following reference to the data dictionary views (which sit on assembly$:

remark
remark  FAMILY "ASSEMBLIES"
remark
remark  Views for showing information about PL/SQL Assemblies:
remark  USER_ASSEMBLIES, ALL_ASSEMBLIES and DBA_ASSEMBLIES
remark
create or replace view USER_ASSEMBLIES
(ASSEMBLY_NAME, FILE_SPEC, SECURITY_LEVEL, IDENTITY, STATUS)
.......

This definitely says to me that these views, and the associated CREATE/DROP/ALTER ASSEMBLY commands, are for .NET stored procedure support.


Moved site to new, similar server - same query is taking 10x longer?

The fast query avoids sorting the result by reading in date_recorded index order matching your ORDER BY. It filters the hard way, comparing your conditions in the WHERE clause to rows one by one, but once it gets enough matching rows to satisfy the LIMIT, it stops scanning the table.
Whereas the slow query tries to use indexes on a couple of columns, thinking this will help. But it ends up having to scan hundreds of thousands of rows to find those that match, then finds the set of rows that matched both index searches (that's the Using intersect note), and finally sorts the large set of rows the hard way, using a filesort, just to return 16 rows and discard the rest.
This is a good counter-example case that shows that searching by index isn't always the best strategy. But the MySQL cost-based optimzer is biased to think that searching by index is usually better.
In this case, you might want to use an index hint to make it choose the date_recorded index.