How to optimize MySQL query with more than 2 inner joins

I find it rather difficult to understand your query, so let's break it apart:

select id
from customer_details
where store_client_id = 2
  and id not in ( select customer_detail_id
                  from  orders
                  where store_client_id = 2
                    and total_spent > 100
                  group by customer_detail_id )
    or id in ( select tcd.id
               from property_details as pd
                  , customer_details as tcd
               where pd.store_client_id = 2
                 and pd.customer_detail_id = tcd.customer_id
                 and pd.property_key = 'Accepts Marketing'
                 and pd.property_value = 'no' )
    and id IN ( select customer_detail_id
                from orders
                where store_client_id = 2
                group by customer_detail_id
                having count(customer_detail_id) > 0 )
    or id in ( select tor.customer_detail_id
               from ordered_products as top
                  , orders as tor
               where tor.id = top.order_id
                 and tor.store_client_id = 2
               group by tor.customer_detail_id
               having sum(top.price) = 1 )

This can be translated into:

select id
from customer_details
where store_client_id = 2
  and id not in ( select customer_detail_id
                  from  orders
                  where store_client_id = 2
                    and total_spent > 100
                  group by customer_detail_id )

union

select id
from customer_details
where id in ( select tcd.id
              from property_details as pd
                 , customer_details as tcd
              where pd.store_client_id = 2
                and pd.customer_detail_id = tcd.customer_id
                and pd.property_key = 'Accepts Marketing'
                and pd.property_value = 'no' )
  and id IN ( select customer_detail_id
              from orders
              where store_client_id = 2
              group by customer_detail_id
              having count(customer_detail_id) > 0 )

union

select id
from customer_details
where id in ( select tor.customer_detail_id
              from ordered_products as top
                 , orders as tor
              where tor.id = top.order_id
                and tor.store_client_id = 2
              group by tor.customer_detail_id
              having sum(top.price) = 1 )

Now, the implicit joins should for readability be transformed into explicit ones:

select id
from customer_details
where store_client_id = 2
  and id not in ( select customer_detail_id
                  from  orders
                  where store_client_id = 2
                    and total_spent > 100
                  group by customer_detail_id )

union

select id
from customer_details
where id in ( select tcd.id
              from property_details as pd
              join  customer_details as tcd
                on pd.customer_detail_id = tcd.customer_id
              where pd.store_client_id = 2
                and pd.property_key = 'Accepts Marketing'
                and pd.property_value = 'no' )
  and id IN ( select customer_detail_id
              from orders
              where store_client_id = 2
              group by customer_detail_id
              having count(customer_detail_id) > 0 )

union

select id
from customer_details
where id in ( select tor.customer_detail_id
              from ordered_products as top
              join orders as tor
                  on tor.id = top.order_id
              where tor.store_client_id = 2
              group by tor.customer_detail_id
              having sum(top.price) = 1 )

IN is often a slow predicate, I would try replacing it with [NOT] EXISTS. PITFALL: IN and EXISTS behaves different when nulls are involved, be careful so that you don't change the semantics of the query:

select id
from customer_details as x
where store_client_id = 2
  and not exists (
         select 1
         from  orders
         where store_client_id = 2
           and total_spent > 100
           and customer_detail_id = x.id )

union

[...]


Using LIKE or Full-text when searching database

Having a wildcard in front of your search term will prevent SQL Server from being able to use an index and thus become painful very fast. 2 things you could do, besides going for full text search:

The obvisous one, do not put a wildcard at the start of the keyword requiring the user to enter a search query matching the start of col1.
Splitting col1 from both tables into keywords and put the distinct keywords in a sperate table and add two junction tables joining table1 and table2 to the keyword table.


I was having a different issue about a month ago. I came across this script that should help troubleshooting.

 USE msdb
 GO

-- Check that the service broker is enabled on MSDB.
-- Is_broker_enabled must be 1 to use database mail.
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb';
-- Check that Database mail is turned on.
-- Run_value must be 1 to use database mail.
-- If you need to change it this option does not require
-- a server restart to take effect.
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Database Mail XPs';

-- Check the Mail queues
-- This system stored procedure lists the two Database Mail queues.
-- The optional @queue_type parameter tells it to only list that queue.
-- The list contains the length of the queue (number of emails waiting),
-- the state of the queue (INACTIVE, NOTIFIED, RECEIVES_OCCURRING, the
-- last time the queue was empty and the last time the queue was active.
EXEC msdb.dbo.sysmail_help_queue_sp -- @queue_type = 'Mail' ;

-- Check the status (STARTED or STOPPED) of the sysmail database queues
-- EXEC msdb.dbo.sysmail_start_sp -- Start the queue
-- EXEC msdb.dbo.sysmail_stop_sp -- Stop the queue
EXEC msdb.dbo.sysmail_help_status_sp;

-- Check the different database mail settings.
-- These are system stored procedures that list the general
-- settings, accounts, profiles, links between the accounts
-- and profiles and the link between database principles and
-- database mail profiles.
-- These are generally controlled by the database mail wizard.

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
--  Check that your server name and server type are correct in the
--      account you are using.
--  Check that your email_address is correct in the account you are
--      using.
EXEC msdb.dbo.sysmail_help_profile_sp;
--  Check that you are using a valid profile in your dbmail command.
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
--  Check that your account and profile are joined together
--      correctly in sysmail_help_profileaccount_sp.
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

-- I’m doing a TOP 100 on these next several queries as they tend
-- to contain a great deal of data.  Obviously if you need to get
-- more than 100 rows this can be changed.
-- Check the database mail event log.
-- Particularly for the event_type of "error".  These are where you
-- will find the actual sending error.
SELECT TOP 100 *
FROM msdb.dbo.sysmail_event_log
ORDER BY last_mod_date DESC;

-- Check the actual emails queued
-- Look at sent_status to see 'failed' or 'unsent' emails.
SELECT TOP 100 *
FROM msdb.dbo.sysmail_allitems
ORDER BY last_mod_date DESC;

-- Check the emails that actually got sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'sent'
SELECT TOP 100 *
FROM msdb.dbo.sysmail_sentitems
ORDER BY last_mod_date DESC;

-- Check the emails that failed to be sent.
-- This is a view on sysmail_allitems WHERE sent_status = 'failed'
SELECT TOP 100 *
FROM msdb.dbo.sysmail_faileditems
ORDER BY last_mod_date DESC

-- Clean out unsent emails
-- Usually I do this before releasing the queue again after fixing the
problem.
-- Assuming of course that I don't want to send out potentially thousands of
-- emails that are who knows how old.
-- Obviously can be used to clean out emails of any status.
EXEC msdb.dbo.sysmail_delete_mailitems_sp
  @sent_before =  '2017-03-16',
  @sent_status = 'failed';


What network access do I need when I restoring files using RMAN?

If you are using the 11g duplicate from active database option, the only port that needs to be open is the listener port (usually 1521). If you are using any other options to copy your files to the standby host, the required ports for the protocol that you use (scp/ftp/nfs ...)

Apart from the 11g 'from active database' option, RMAN does only read backupfiles that are present on the node on which the restore/clone is to be taking place. Present in this setting means that they thould be readable. The can be on [n]fs or tape, as long as your host can read them it is ok.

I hope this helps


I recently had to do essentially the same thing, except that I wanted to migrate MySQL tables and data to SQL Server. I tried:

using phpMyAdmin to Export the table definitions with "MSSQL" compatibility: The resulting SQL script would not execute in SQL Server Management Studio (SSMS) due to syntax errors.
using HeidiSQL to export the MySQL database directly to SQL Server: HeidiSQL also generated SQL statements that SQL Server would not accept.
using MySQL Workbench: I could not find a way to migrate a database from MySQL to SQL Server.
using MySQL ODBC and the SQL Server Import and Export Wizard: The wizard crashed (i.e., the Windows "[Application name] has stopped working..." error) when trying to access the MySQL database.

The only method that didn't fail outright was to pull the tables from MySQL into a Microsoft Access .mdb file (!) via MySQL ODBC, then import that into SQL Server.

However, even that was an incomplete solution because the transfer from MySQL to Access omitted all of the primary keys, indexes, and AUTO_INCREMENT attributes. Still, it did transfer the tables and data, which was my main objective. I just had to write some T-SQL to re-create the indexes and whatnot.

Since you don't need to transfer any data you might want to consider just hacking the mysqldump SQL code (good old Find & Replace...) to the point where SQL Server will execute it.


I resolved my problem using the SQL Developer tool.

I followed the steps in this article to generate the DDL for anything in the database.  I had an 11g DB that I needed to migrate in addition to a 9i and this process was easy for 11g.

However, for 9i I used the script in this post and ran it in SQLDeveloper to pre-create my users.  Just save the output of the script to a file and then run in SQLDeveloper for ease.


How can I trace the root cause of a MySQL database table that crashed?

The cause is almost always a non-graceful shutdown -- abort, power failure, etc.  So, I suggest you already know what is causing each occurrence.

It goes something like this...

When a MyISAM table is opened, it is flagged (in the .MYI file) that it is open.
In a graceful shutdown, the index blocks from the key_buffer are flushed (to the .MYI) and the the flag is cleared.  Almost always the index file is now intact.

So, if MySQL is aborted or there is a power failure or ..., the flag is still set.  When restarting the flag(s) is noticed.  Often there is no real damage, but you still need to do at least a REPAIR...quick.  But in some cases, the only real recovery is to rebuild the index(es).

For this (and many other reasons), MyISAM is deprecated and destined for the scrap heap.  Meanwhile, little or no effort is being put into MyISAM, especially by Oracle, which has it disabled in version 8.0.  (MyISAM is likely to live a little longer in MariaDB.)

Another nasty in MyISAM is with something like

UPDATE tbl SET x = x + 1;

and that is being applied to all rows.  If there is a crash in the middle of the execution of the UPDATE, some rows will be changed, some will not.  You have no way to know how far the update got before the crash.  The table is not corrupt, but the data is effectively corrupt.  There is no flag indicates this kind of corruption.

Another nasty:

UPDATE tbl SET str = CONCAT(str, 'xyz');

That is, increase the size of some column in some row.  This is likely to require two writes to the data (.MYD) because the row is now longer and won't fit where it had been.  If only one write gets to disk before a crase, then the data file is corrupted.  REPAIR may or may not be able to fix this, and is likely not to recover at least one row.

Etc.

Note:  Everything I have said in this Answer probably applies to all versions of MyISAM even back to before version 4.0.  MyISAM is antique technology that has not had much done to it in a loooong time.


I want to ask how do I get database size in previous day (example: Today is January 31st, and I want to get database size in January 22nd). Note: I do not have regular backups.

That's impossible. You could easily script a daemon (with something like du) that recorded that. Or you could use something an lvm snapshot or XFS to get that information, if you were using a stack like that. But MySQL doesn't just record how big the database was at an arbitrary time.


Unquoting JSON strings; print JSON strings without quotes

SELECT value#>>'{}' as col FROM json_array_elements('["one", "two"]'::json);

Result:

col
---
one
two


PostgreSQL: Function to reformat a string

Use regexp_replace

SELECT regexp_replace('abcdefghiabcdefghi', '(.{3})', '\1 ', 'g');
      regexp_replace
--------------------------
 abc def ghi abc def ghi
(1 row)

This inserts a space every three characters.


10.3.9-MariaDB can not query equal operator with special character

According to the documentation about sql_mode NO_BACKSLASH_ESCAPES:

  Disables using the backslash character \ as an escape character within
  strings, making it equivalent to an ordinary character.

Your string has a double backslash in it. In the NO_BACKSLASH_ESCAPES sql_mode this will be interpreted as a double backslash whereas in the default sql_mode it's interpreted as a single backslash.

Below are some tests with MariaDB 10.3.13 using the following table:

CREATE TABLE taginfo1 (id int unsigned PRIMARY KEY, TagName varchar(200));

Default sql_mode

INSERT INTO taginfo1 (id, TagName) VALUES (1, '~!@#$%^&*()_+|}{":?><./'';[]\\=-`');
SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
+----+---------------------------------+
| id | TagName                         |
+----+---------------------------------+
|  1 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |
+----+---------------------------------+

Note that one backslash has disappeared.

sql_mode with NO_BACKSLASH_ESCAPES

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
Empty set (0.000 sec)

Let's remove one backslash from the WHERE clause:

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\=-`';
+----+---------------------------------+
| id | TagName                         |
+----+---------------------------------+
|  1 | ~!@#$%^&*()_+|}{":?><./';[]\=-` |
+----+---------------------------------+

Then let's insert a second row while we're in NO_BACKSLASH_ESCAPES mode:

INSERT INTO taginfo1 (id, TagName) VALUES (2, '~!@#$%^&*()_+|}{":?><./'';[]\\=-`');

SELECT * FROM taginfo1 WHERE TagName = '~!@#$%^&*()_+|}{":?><./'';[]\\=-`';
+----+----------------------------------+
| id | TagName                          |
+----+----------------------------------+
|  2 | ~!@#$%^&*()_+|}{":?><./';[]\\=-` |
+----+----------------------------------+

As far as I can tell, this all works as per the documentation. If you really get different results in 10.3.9, then presumably there was a bug that's been fixed.


While this only addresses a small part of your question (and you may already be aware):

When you right-click on a job and choose "Start Job at step...", the window that opens does not close automatically when the job is finished. You have to manually close the window (which can be done before or after the job completes).

Once the "Execute job" line in the window has a status (whether success or error), there should (normally) be an entry in sysjobhistory, even if the window hasn't closed (just confirmed in my system).


You have not defined any indexes, so this query would be slow even without sorting, because it needs a full table scan.

You should define a combined index on (uid, id). The first part helps in finding all rows WHERE uid = X. If these have been found, the second part can be used to sort the result set without the need to use filesort.

Use this create table statement

CREATE TABLE userstatus (
  id INT NOT NULL AUTO_INCREMENT,
  uid INT NOT NULL,
  status TEXT NOT NULL,
  datentime TIMESTAMP NOT NULL,
  visible INT (3) NOT NULL DEFAULT 0,
  statustype CHAR (16) NOT NULL DEFAULT 0,
  PRIMARY KEY (id),
  INDEX (`uid`, `id`)
);

and see this and your query in action in this sqlfiddle.

If you are interested in being capable to do similar improvements on your own, you should start by learning to read the output of EXPLAIN.


Group by function not working correctly in SQL server CTE

add cast(CallStartTime as DATE) in your GROUP BY:

;with grp (Sdate,TransactionType,tot)
as
(select cast(CallStartTime as DATE)Date, Transaction_type as [Transaction type],
(count ([Transaction_type])) as [Total Count]
from TBL_AGENT_TRANSACTIONS
where cast(CallStartTime as DATE)>='2015-04-27'
    and cast(CallEndTime as Date)<='2015-04-27'
    and Transaction_type in ('Debit_Card')
group by cast(CallStartTime as DATE), Transaction_type)

select grp.Sdate,grp.TransactionType,sum(grp.tot) as totalCount
from grp
group by TransactionType,grp.Sdate,grp.tot


I assume that this question is about how to update a bunch of rows to a value on higher without stepping on each other. If you update id=1 to id=2 in the first row while the second row (with id=2) is still there you will get a primary key violation because now you have two rows with id=2.

To prevent that type of collision you just need to start at the largest value and increase it by one. that opens up a gap for the second largest value to move into which in turn opens a gap for the third largest value to move into, and so on.

In SQL Server however, you need not worry, as the engine is taking care of that automatically:

SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.Employee(Id INT PRIMARY KEY CLUSTERED, Name NVARCHAR(MAX));

INSERT INTO dbo.Employee(Id,Name)
VALUES(1,'John'),(2,'Jane'),(3,'Max');

Query 1:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  1 | John |
|  2 | Jane |
|  3 |  Max |

Query 2:

UPDATE dbo.Employee
  SET Id = Id + 1;

Execution Plan

Query 3:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  2 | John |
|  3 | Jane |
|  4 |  Max |

Query 4:

UPDATE dbo.Employee
  SET Id = Id - 1;

Execution Plan

Query 5:

SELECT * FROM dbo.Employee;

Results:

| ID | NAME |
|----|------|
|  1 | John |
|  2 | Jane |
|  3 |  Max |

If you actually follow the SQL Fiddle link, you can also see the execution plans for the two update statements. The first one looks like this:

There you can see fairly far to the left a Table Spool operator. That operator basically creates a copy of all rows that were updated before they get written back by the actual `Clustered Index Update' operator next to it. Because of this SQL Server is able to handle "overlapping" updates without tripping over itself.

An added benefit (and the original reason why this behavior was implemented) is that this two step approach provides Halloween Protection.


Keep stored procedures / UDF in separate database

I have never seen that approach used.  If you are keeping your database schema in a source control system, then you can generate deployment scripts.  Such tools can compare previous versions of your schema and deploy the changes.  SQL Server Data Tools is available in Visual Studio.


How can I install the SQL Server PowerShell module on an offline machine?

PowerShell has a built-in mechanism for this which should be easier than the previous answer.

From an Internet-connected computer, run Save-Module sqlserver -path c:\tmp (substitute whatever path you want for the module to be saved to). This will save the module to a directory of the same name in c:\tmp (c:\tmp\SqlServer).

Then, copy that whole directory to your target computer(s). You have a couple options for the destination, depending upon how you want to make it available to users. On PowerShell 5.1 and older look at the paths in the PSModulePath environment variable. On my system, I have:

> $env:psmodulepath.split(";")
C:\Users\MYNAME\Documents\WindowsPowerShell\Modules
C:\Program Files\WindowsPowerShell\Modules
C:\WINDOWS\system32\WindowsPowerShell\v1.0\Modules
C:\Program Files\Intel\Wired Networking\
C:\Program Files (x86)\Microsoft SQL Server\150\Tools\PowerShell\Modules\

To make the module available to all users, I'd put it in C:\Program Files\WindowsPowerShell\Modules. For just myself, it'll go into C:\Users\MYNAME\Documents\WindowsPowerShell\Modules

These paths may be a bit different if you're running PowerShell Core or PowerShell 7; you'd look at one or more of the paths in $PSGetPath.

From here, you should be able to run Import-Module sqlserver successfully.

Shameless plug: If you're doing a lot of SQL Server administration via PowerShell (and I heartily encourage it!), you should be checking out the dbatools module.


Lets try and test connectivity to your server with sqlcmd

-- connect to instance with sqlcmd
sqlcmd -S MYSQLSERVER2008 -U MyUsername -P MyPassword

-- attempt to open master
USE MASTER;
GO

You should see the text : Changed database context to 'master'

Next type "quit" and then try this

Create a file called c:\test_sqlcmd.sql

Add the following code:

:setvar myserver "MYSQLSERVER2008"

:CONNECT $(myserver)
USE MASTER;
GO
SELECT @@SERVERNAME;
GO

Now execute that script sqlcmd -i c:\test_sqlcmd.sql

You should see

Changed database context to 'master'
and
MYSQLSERVER2008

Once we have that out of the way, we can continue our research


SQL Server MDW - Query for data collector retention period

Inside the database you selected for Management Data Warehouse there is a table called [core].[source_info_internal] that contains a column days_until_expiration

This query gives you the retention periods per collection set:

SELECT [collection_set_uid], [days_until_expiration]
FROM [core].[source_info_internal];

Returns:

+--------------------------------------+-----------------------+
|          collection_set_uid          | days_until_expiration |
+--------------------------------------+-----------------------+
| 2DC02BD6-E230-4C05-8516-4E8C0EF21F95 |                    14 |
| 49268954-4FD4-4EB6-AA04-CD59D9BB5714 |                    14 |
| 3006169E-D33A-4101-A1DE-9F0DABF7D84E |                    30 |
| B83A3832-37CD-4C7A-88F6-6696DA5A7E43 |                    30 |
| 7B191952-8ECF-4E12-AEB2-EF646EF79FEF |                   730 |
+--------------------------------------+-----------------------+

The collection set descriptions are unexpectedly in msdb so if you want the names of the collection set along with the retention period you could use:

SELECT msdb.[name],  mdw.[days_until_expiration]
FROM [core].[source_info_internal]  mdw
INNER JOIN [msdb].[dbo].[syscollector_collection_sets_internal] msdb
on mdw.[collection_set_uid] = msdb.[collection_set_uid];

which returns (on my system):

+---------------------------------+-----------------------+
|              name               | days_until_expiration |
+---------------------------------+-----------------------+
| Disk Usage                      |                   730 |
| Server Activity                 |                    14 |
| Query Statistics                |                    14 |
| Stored Procedure Usage Analysis |                    30 |
| Table Usage Analysis            |                    30 |
+---------------------------------+-----------------------+

Please note however that the Agent job that purges the data calls a stored procedure that accepts parameters so this query lists the default retention periods. If somebody changed it at the job step level you wouldn't get that information.

The procedure definition:

ALTER PROCEDURE [core].[sp_purge_data]
    @retention_days smallint = NULL,
    @instance_name sysname = NULL,
    @collection_set_uid uniqueidentifier = NULL,
    @duration smallint = NULL,
    @delete_batch_size int = 500
AS

So somebody could have overridden the default settings, you could look at the source code for that procedure to follow the entire logic.


It's better to extract some rarely used fields to another table. Especially if those fields are big (blob, long varchar etc). This will case smaller row size and better optimized buffer usage. Because small data (i.e 'id', 'name', 'status') will be stored in buffer and will have fast access to it. And queries with big fields (i.e. 'description TEXT') will be slower, because of need to read second table which can be unbuffered yet.

So, I think in your case there is no reason to split fields into different tables.


How to allow creation and deletion of a database or collections with user and password

No, system don't work that way. You must enable authentication and authorization of your mongodb.

After that, you log in with userAdmin credentials and you (only) have authorization to do userAdmin work, not system admin work. But remember to create super-admin user (before enabling authenticaton) or you end up to situation that you don't have superuser (root) who can give other needed rights.


The exist() Method (xml Data Type) returns a bit.
1 if at least one node is found and 0 if no nodes are found (empty result set).

To get the rows where neither ABC or XYZ exist you just have to compare the result of exist with 0.

[myxmlcolumn].exist('for $x in /Root/Row
                     where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')]))
                     return $x') = 0

Your FLWOR query can be rewritten using a predicate on the user node instead,

select Sl_no,
       myxmlcolumn
from mytable
where myxmlcolumn.exist('/Root/Row/user[fn:upper-case(text()[1]) = ("ABC", "XYZ")]') = 0

And for the IN version of the query you check if exist returns 1 instead.

select Sl_no,
       myxmlcolumn
from mytable
where myxmlcolumn.exist('/Root/Row/user[fn:upper-case(text()[1]) = ("ABC", "XYZ")]') = 1


Windows OS level scheduled disk defragment tasks and SQL data volumes

Windows-Verified Disk Defragmentation solutions won't cause corruption in your databases, however in most scenarios performing physical defragmentation of SQL Server data and log files is unnecessary.
I say "most scenarios" since most production SQL Servers will have a dedicated drive setup for SQL Server data and log files.  However, if your SQL Server data and log files exist on the system drive, or some other physically attached spinning rust disk, then you might want to consider defragmentation if the volume is heavily fragmented, and you can prove that fragmentation is having a negative effect on SQL Server performance.
If you have a locally attached SSD for storage of SQL Server data and log files, or you are using a SAN or other RAID array, then defragmentation of the physical files will have zero effect on performance.  SSDs are random access, and so are almost totally unaffected by file fragmentation.  RAID arrays spread stripes of each file across disks by design, and who knows what's happening at the SAN level depending on how advanced it is - for instance it might be doing data-de-duplication.
The short answer is, it's almost never necessary to defragment drives containing SQL Server data and log files.
This Microsoft Support page has further details about defragmenting SQL Server volumes.


Add indexes on (attribute, id2) and on (id2, attribute) and try this:

SELECT
    m.atttribute,
    COUNT(*) AS cnt
FROM
    mytable AS m
  JOIN mytable AS m1
    ON  m1.attribute = 'blue'
    AND m1.id2 = m.id2
  JOIN mytable AS m2
    ON  m2.attribute = 'red'
    AND m2.id2 = m.id2
GROUP BY
    m.attribute
HAVING
    m.attribute NOT IN ('blue', 'red')
ORDER BY
    cnt DESC ;

Test in SQL-Fiddle


creating a trigger after update exclude a column?

I believe your code is missing or is typo'ed

CREATE TRIGGER [dbo].[product_trig]
  ON dbo.product
  AFTER UPDATE
AS -- you miss this 'as'
BEGIN -- you miss this 'begin'
    SET NOCOUNT ON;
    IF NOT UPDATE (to_sync)
      BEGIN
          UPDATE dbo.product
              SET  to_sync  = 'TRUE'
          FROM dbo.product INNER JOIN INSERTED i ON dbo.product.NAME = i.NAME
      END
END


Mysql slave use more memory than master server

query_cache_size = 200M

Too big.  Don't go past 50M, else the maintenance will hurt performance.

  innodb_buffer_pool_size = 1G

If you are using InnoDB, set this to 70% of available RAM.

table_open_cache (formerly called table_cache) relates to the number of tables you might need to open, but not the sizes of the tables.  A few hundred is typically enough.  4096 is needed by only a few systems; don't go past that without a clear indication that you need it.

  All the activities are happening at master

If so, what is the Slave for?

Please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; (on each server) so I can help you dig deeper into your puzzle.


/dev/shm is shared memory. That will compete with OS for available RAM. You simply do not have enough RAM to hold that large temp table.

You need to comment out that line in /etc/my.cnf

[mysqld]
#tmpdir=/dev/shm

and restart mysql. That way, tmpdir default to the OS tmp folder, which is /tmp. That will put temp tables on disk.

If you still want /dev/shm, instead of commenting out tmpdir, just increase RAM on the server.


Two kinds of products and one order table

There are 2 options:

If your productType has same attribute:

If your ProductType has different attribute:


Count Function with Aggregate function inside the query

Put the first select in a subquery or a common table expression (shown below)

WITH cte (TicketID, CreatedMoment, StatusChangeTime)
AS (
    SELECT M.TicketID,
           M.CreatedMoment,
           MAX(L.StatusChangeMoment) AS StatusChangeTime
    FROM XX_MASTER_TICKETS AS M
    LEFT JOIN XX_DETAIL_TICKET_STATUS_LOG AS L ON M.RowID = L.TicketRowID
    WHERE M.CreatedMoment BETWEEN {d '2014-08-23'} AND {d '2014-08-26'}
    GROUP BY M.TicketID,
             M.CreatedMoment
   )

SELECT CAST(CreatedMoment AS date) AS [Date],
       SUM((CASE WHEN DATEDIFF(minute, CreatedMoment, StatusChangeTime)<60*24 THEN 1 ELSE 0 END)) AS below24Hrs,
       SUM((CASE WHEN DATEDIFF(minute, CreatedMoment, StatusChangeTime)>=60*24 THEN 1 ELSE 0 END)) AS above24Hrs
FROM cte
GROUP BY CAST(CreatedMoment AS date)
ORDER BY 1;

The common table expression returns a list of TicketID, CreatedMoment and MAX(StatusChangeMoment). Using this result set, we can apply a second aggregate in the SELECT statement below. What I'm doing there is a conditional SUM(). I'm adding 1 for each row that meets the criteria and 0 for each row that doesn't.

NB: I've simplified your criteria a bit, I hope it's equivalent to what you're looking for.


MySQL performance of applying unique constraint single column vs combination column

The duration of the creation of the index will be directly related to the size of the index. I don't think it would be wildly different for the 2 options provided. Under the hood MySQL is going to copy the table to recreate it in the new structure, so consider the amount of time MySQL will take to read the data and write the data. The process is single threaded too. If your storage is slow this will take some time.

Tips:
- Ensure that fast_index_creation is enabled
- Group multiple ALTERs
- use pt-online-schema-change tool to perform it online. This will take longer but will permit you to continue to use the table.


Convert the IP Address range to two BIGINT for faster lookup

If I am reading your requirements correctly.

From the dataset provided the start_ip_num and end_ip_num will be the same.

Why you may ask? you are not storing a range of ip addresses; take for example a /24 range: 192.168.1.0/24 this provides a range 192.168.1.0 - 192.168.1.255, whilst really only ip's 192.168.1.1 though 192.168.1.254 are usable (.255 being the broadcast).

If you are storing a 'range' the start and end values will differ

mysql> select INET_ATON('192.168.1.1') as start_ip_num, INET_ATON('192.168.1.254') as end_ip_num;
+--------------+------------+
| start_ip_num | end_ip_num |
+--------------+------------+
|   3232235777 | 3232236030 |
+--------------+------------+
1 row in set (0.00 sec)

If not they will be the same, if you want the ranges for each a whois 24.32.116.116 will reveal this information.

e.g.

24.32.116.116 has
24.32.0.0/16

and
24.32.116.0/22  "Comment:        Clarksville, Tx Customers"

The later providing the required information.

As such your 'range' is strictly speaking: 24.32.116.0 - 24.32.119.255

mysql> select INET_ATON('24.32.116.0') as start_ip_num, INET_ATON('24.32.116.255') as end_ip_num;
+--------------+------------+
| start_ip_num | end_ip_num |
+--------------+------------+
|    404780032 |  404780287 |
+--------------+------------+
1 row in set (0.00 sec)


Assuming you want all missing integer numbers between the minimum and maximum existing id in your table - in a current Postgres 9.5 installation like you commented (or at least 9.3):

@Seb3W already suggested generate_series(). It's more efficient to retrieve min and max in a single query, though. It's also preferable to use set-returning-functions like generate_series() in the FROM list instead of the SELECT list (conforming to standard SQL and less error prone).

SELECT id
FROM  (SELECT min(id) AS a, max(id) AS z FROM numbers) x, generate_series(a, z) id
LEFT   JOIN numbers n1 USING (id)
WHERE  n1.id IS NULL;

Once you have the complete set of candidate numbers, use a run-of-the-mill technique to ...

Select rows which are not present in other table

About the LATERAL join:

What is the difference between LATERAL and a subquery in PostgreSQL?

If you don't care about standard SQL and want to squeeze out the last drop of performance (or in Postgres 9.2 or older without LATERAL joins) you can use generate_series() in the SELECT list, but still make it a single SELECT:

SELECT id
FROM  (SELECT generate_series(min(id), max(id)) FROM numbers) n(id)
LEFT   JOIN numbers n1 USING (id)
WHERE  n1.id IS NULL;

If you are after performance, you should have an index on numbers.id, of course:

CREATE INDEX numbers_id_idx ON numbers (id);


Backup to URL not working when using Ola Hallengrens Database Backup

I just ran into this issue as well creating a Maintenance Plan in SQL Server. The default backup maintenance plan creates a script that includes WITH CREDENTIAL, but I was able to get it working when I removed that default Task. So I ended up with this code in an Execute T-SQL Statement Task:

declare @backuptime as varchar(250);
set @backuptime = 'https://youraccountname.blob.core.windows.net/backups/mytestdb_' + CONVERT(VARCHAR(19), GETDATE(), 120) + '.bak';

BACKUP DATABASE yourdb
    TO URL = @backuptime


Table size unaffected after deleting 400k rows

Deleting data isn't expected to change the size of the table segment.  It will create free space in blocks that are part of the table segment and it will probably create at least a few empty blocks.  That free space will be made available for subsequent insert operations on the table (assuming you're doing conventional-path inserts) as well as update operations that cause the size of rows to increase.

If you want to actually shrink the size of the table segment (generally not a good idea if you're just going to insert more data into this table in the future), depending on the Oracle version, the type of tablespace, and whether you've enabled row movement you can probably shrink the segment

ALTER TABLE step_status
  SHRINK SPACE CASCADE;

Be aware, however, that this may be a very costly operation that may involve physically moving most or all of the rows in the table.

There is a chapter in the Database Administrator's Guide on reclaiming wasted space that discusses this and related issues in much more depth.  It also walks through using the Enterprise Manager GUI and the various advisors to reclaim space either manually or automatically.


T-SQL Create Query - Compare and Calculate

Did CTE with RANK/OVER:

Rank3PM = RANK() OVER (ORDER BY [3PM per Game])

and in the select I ended with:

TotalPoints =  Rank3PM + RankREB + RankAST..

Thanks.


How to delete all the tab and enter in a table?

If you want to get rid of a \n at the start of a column you can run something like this:

UPDATE your_table SET col1 = substring(col1, -1) WHERE col1 like '\n%';

You can do something similar for other columns or changing \n for \t.

If you want to delete all \t and \n, anywhere on the string, you can use:

UPDATE your_table SET col1 = replace(replace(col1, '\t', ''), '\n', '');


How to change default port for T-SQL Debugger

I don't think it's possible to change the TCP port used by T-SQL Debugger.
You can use an application such as RelayTCP and port forwarding rules on your router to achieve what you want. It's difficult to give specific recommendations without knowing more about your network topology.

It may be easier to set up a VPN to your site and then run T-SQL Debugger over VPN (I assume that LAN-local connections to TCP 135 will not be blocked).


First of all, people often fall for this, but high log file sync waits do not necessarily mean I/O problem.

Second, this could be really troublesome, if you have many physical devices in the ASM diskgroup where your redo logs are, because the extents of your files will be evenly distributed on several disks.

Anyway, you need to find the ASM diskgroup number and file number for your redo logs. For example my redo logs:

select member from v$logfile

MEMBER
--------------------------------------------------
+O6CA_DATA/BRIX/ONLINELOG/group_1.258.915373939
+O6CA_FRA/BRIX/ONLINELOG/group_1.257.915373939
+O6CA_DATA/BRIX/ONLINELOG/group_2.259.915373939
+O6CA_FRA/BRIX/ONLINELOG/group_2.258.915373939
+O6CA_DATA/BRIX/ONLINELOG/group_3.266.915374563
+O6CA_FRA/BRIX/ONLINELOG/group_3.259.915374563
+O6CA_DATA/BRIX/ONLINELOG/group_4.267.915374563
+O6CA_FRA/BRIX/ONLINELOG/group_4.260.915374563

Since I use OMF, I can easily recognize the file number, because it is part of the name (258, 257, ...), but you can get this as:

select
  af.group_number,
  af.file_number
from
  v$asm_file af
  join v$asm_alias aa on (af.group_number = aa.group_number
                          and af.file_number = aa.file_number)
  join v$logfile lf on (substr(lf.member, instr(lf.member, '/', -1) + 1) = aa.name)
;

GROUP_NUMBER FILE_NUMBER
------------ -----------
           1         258
           2         257
           1         259
           2         258
           1         266
           2         259
           1         267
           2         260

Now I am curious about the first redo logfile (group 1, file 258). In the ASM instance (so not the database instance), I can query as:

select
  group_kffxp,
  disk_kffxp,
  size_kffxp,
  count(*)
from
  x$kffxp
where
  group_kffxp = 1
  and number_kffxp = 258
group by
  group_kffxp,
  disk_kffxp,
  size_kffxp
;

GROUP_KFFXP DISK_KFFXP SIZE_KFFXP   COUNT(*)
----------- ---------- ---------- ----------
          1          0          1         51

So this single redo log file has 51 extents in diskgroup number 1, disk number 0, each extent the size of 1 allocation unit (and the allocation unit I used is 1 MB, and I created a redo log with the size of 50 MB, but that is not relevant here). And that single disk, in my case, is:

select name, path from v$asm_disk where group_number = 1 and disk_number = 0

NAME            PATH
--------------- ------------------------------
O6CA_DATA_0000  /dev/oracleasm/asm-o6ca_data1


SQL Server database design for "archived but available" data

For convenience, assume that the live database is called LiveDb and the achive database is called ArchiveDb

Add a UNION ALL view in LiveDb pointing to the tables in ArchiveDb database via a synonym (There is no need to do a combined db with synonyms)
"Partition" on visit.date and denormalise this column to visit_payments too if it isn't there already (this improves co-located join performance)
Only archive the two large tables if possible (reduces chance of tripping up the optimiser). Keep the UNION ALL view and the other tables in LiveDb so all joins to the smaller tables are kept local
Add a CHECK constraint on the tables in both LiveDb and ArchiveDb  that describes the range of visit.date contained in the table. This helps the optimiser eliminate the archive table from both seeks and scans that contain the column visit.data. You will have to periodically update this constraint.
In the UNION ALL view, add a WHERE criteria which filters on visit.data. This is in addition to the hint you already provided in the check constraint. This maximises the chance of filters being pushed down
If you have EE, partition the table in the archive database (But NOT in the live database). If you want to get really fancy, use filegroup level backup/restore of the archive databases to save on backup times.
Consider putting AchiveDb in SIMPLE recovery mode if it isn't already. You are not likely to need transaction log backups of ArchiveDb
Use INSERT... WITH (TABLOCK) SELECT ... WITH (ROWLOCK) to force minimal logging on the destination when moving data between  LiveDb and ArchiveDb

All of the above does not guarantee that the optimiser will eliminate the archive tables from seeks and scans, but it makes it more likely.

When the elimination doesn't happen. These are the effect you may see (this list may be incomplete). For seeks, you will get an additional seek on every query (this drives up IOPS). For scans, the results could be disastrous for performance as you may end up scanning both the archive and live tables. Here are the typical ways you can trip up the optimiser:

If you join the visit% tables together and don't include the visit.data in the join criteria (this is why you want to denormalise). Because of this, you may wish to modify some of your queries
If you get a hash join between visit.data and another table (for example a date dimension), you may not get the right elimination of tables
If you try to aggregate data over the archived tables
If you filter on anything BUT the visit.data, for example a seek directly on the key of the view.

For the last scenario, you can guard yourself against the worst effects by adding another check constraint on the cid - if this is possible. You did mention that the sequence of cid not "clean" with respect to the dates and progression of rows in the table. However, could you maintain a table that contains the information: "There are no cid above this number since this visit.data" or similar? This could then drive an additional constraint.

Another thing to be careful about is that parallel queries may spawn a LOT more threads once you query the partitioned view (as both "sub-tables" will be exposed to the same parallel optimisations). For that reasons, you may want to limit MAXDOP on the server or the queries that are parallel.

By the way, if you know the queries well - you may not even need the same indexes in the two databases (this assumes you are 100% sure you will get the right elimination of tables). You could even consider using column stores for ArchiveDb.


Can a PostgreSQL Trigger be used to ignore an insert?

Yes, use a BEFORE INSERT trigger, and return null.

Live example here.

create table stuff (
  stuff_id int primary key,
  thing text
);

create or replace function stuff_inserting() returns trigger language plpgsql as $$
begin

  return null;

end $$;

insert into stuff values (1, 'asdf');

select * from stuff; /* returns 1 row */

create trigger inserting before insert on stuff for each row execute procedure stuff_inserting();

insert into stuff values (2, 'fdsa');

select * from stuff; /* still returns only 1 row */


How to backup the current code of a stored procedure and its permissions as well?

Rather than saving your procedures in user tables, why don't you just set up a SSDT database project and use version control (TFS, Git, Mercurial, SVN or whatever you find appropriate) to take care of versioning?

SSDT or other tools such as Red Gate's SQL Source Control will help you in keeping track of versions and deploying your changes to your envirnoments.


User defined table type with a check constraint

Although table types do allow unnamed CHECK constraints, it is not possible to use a user-defined function inside one.

The error message is:

  Msg 2785 Level 16 State 1 Line 3
  User-defined functions, user-defined aggregates, CLR types, and methods on CLR types are not allowed in expressions in this context.
  Msg 1750 Level 16 State 0 Line 1
  Could not create constraint. See previous errors.

See the db<>fiddle demo


The error message suggests to me you are actually running your DDL statement against a SQL Server instance rather than a MySQL one.

If your server is SQL Server and you mistakenly thought it was MySQL and wrote your DDL using the MySQL syntax, you need to fix not just the UNSIGNED part but also AUTO_INCREMENT. In SQL Server's flavour of SQL, called Transact-SQL, that line would look like this:

id tinyint NOT NULL IDENTITY primary key,

You might also want to switch to smallint, because, as SQL Server does not support unsigned types, you will have only 127 positive values using the tinyint type. Additionally, if you want to ensure the values cannot be negative or nil, you can add a check constraint:

id smallint NOT NULL IDENTITY primary key check (id > 0),

just to make sure that incorrect values cannot be inserted explicitly (when temporarily switched to the SET IDENTITY_INSERT ... ON mode).

If you did mean to run the statement in MySQL, then just check your connection settings.


Can anyone please tell me if these are correct statements about measure group partitioning in ssas?

I believe you are correct to object to this description.  The partitioning of a measure group is always a "horizontal partitioning" as the term is commonly used.  I submitted a Pull Request to change the docs to describe the options as a "multi-table partition scheme" or a "single-table partition scheme".


Efficiently query MAX over multiple ranges

Here's a solution using CROSS APPLY, which does the same TOP query for each customer_id:

SELECT MAX(b.MaxQuantity) AS quantity
  FROM
  (
    SELECT 1 AS customer_id UNION ALL
    SELECT 2
  ) a
  CROSS APPLY
  (
    SELECT TOP 1
      quantity AS MaxQuantity
      FROM orders o
      WHERE o.customer_id = a.customer_id
      ORDER BY quantity DESC
  ) b;

This does the same work as the UNION ALL-based query you wrote in the Fiddle; the difference is that the customer_id input is abstracted from the meat of the query, so it can easily be converted to use a table variable or table-valued parameter, which will result in a static query plan, which is important. This approach will work well for a small number of customer_id values, and simply removing the outer MAX will return the maximum for each customer. I don't believe there's a way to further optimize this query for a small number of customer_ids using these data structures (assuming the customer_ids are random, and not a range).

For a large number of customer_ids, it probably is cheaper to do the index scan and stream aggregate than many seeks. To get this going faster, you'd have to move to some kind of denormalized data structure. MAX isn't supported in an indexed view, so rolling your own mechanism is the only way to go, either in application logic or triggers. Depending on the read/write ratio on this table, that may or may not be faster than the above approach: you'd have to test it in your exact scenario.


I think you will have better luck pulling from inserted instead of from the base table (and of course fixing the typo that should have prevented compilation), but you should also consider not using e-mail from a trigger at all.

ALTER TRIGGER [dbo].[SendEmail_INS]
ON [dbo].[tbl_UNIT_INS]
FOR INSERT -- AFTER INSERT is the exact same thing as FOR INSERT.
AS
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
     @query_result_header = 0,
     @exclude_query_output = 1,
     @profile_name = 'Outbound',
     @recipients = 'me@email.com', -- fixed missing quote here
     @query = N'SELECT * FROM inserted;',
     @subject = 'New Item Inserted';
END
GO


Why aren't primary key / foreign key matches used for joins?

In many cases, there are more than one way to join two tables; See the other answers for lots of examples. Of course, one could say that it would be an error to use the 'automatic join' in those cases. Then only a handfull of simple cases where it can be used would be left.

However, there is a severe drawback! Queries that are correct today, might become an error tomorrow just by adding a second FK to the same table!

Let me say that again: by adding columns, queries that do not use those columns could turn from 'correct' into 'error'!

That is such a maintenance nightmare, that any sane style guide would prohibit to use this feature. Most already prohibit select * for the same reason!

All this would be acceptable, if performance would be enhanced. However, that's not the case.

Summarizing, this feature could be used in only a limited set of simple cases, does not increase performance, and most style guides would prohibit its usage anyway.

Therefor it is not supprising that most database vendors choose to spend their time on more important things.


truncating transaction log for simple recovery databases

Just because database is in simple recovery does means transaction log will not grow. Actually there is not much difference in terms of logging in full and simple recovery.

In simple recovery model transaction log is truncated when checkpoint is fired and log file grows 70% of its size subject to condition no long running transaction is holding the logs in your case it is the index rebuild which is holding things from being truncated leading to log file increase.

What you can do is use smart index rebuild technique dont rely on SSMS. There are quite few available Ola Hallengren Index rebuild, Minion Index rebuild and My Custom Script.

It may also be possible that the drive on which log file resides needs more space because your indexes are big and needs space and you have to add more space.

Please also read Why Does the Transaction Log Keep Growing or Run Out of Space?


Caching intermediary CTEs for multiple uses

No, there is no way to cache a CTE; it will typically be executed multiple times if it is referenced multiple times. If you want to avoid this, you can cache the results using a #temp table instead.

In some cases, the execution plan may be simple enough that the CTE is only materialized once (this doesn't technically mean it was "cached"), or for the results to be reused with a spool. I don't have any examples at hand and, even if I did, you couldn't rely on that to always be the case. Even something as simple as this:

CREATE TABLE dbo.OneTuple(id int PRIMARY KEY);
INSERT dbo.OneTuple(id) VALUES(1);
GO

;WITH cte AS
(
  SELECT id FROM dbo.OneTuple
)
SELECT id FROM cte
UNION ALL
SELECT id FROM cte;

Yields two identical index scans:

Also see this great Martin Smith answer on Stack Overflow and it is discussed on this feedback item.


Query to Access a specfic data in the row using python

If I understand it correctly, you are storing all data in one field of the table? If so, MySQL will not be of much use as you need to unserialize the data in python. If so, then this question is not really for DBA, but more for StackOverflow.

If you however want to restructure the table and keep various data elements in different columns instead of one column called "data", then you could SELECT only that particular column(s) that are relevant instead of querying whole string.


I think it is opportune to start pointing out that, in a relational database —as per Dr. E.F. Codd— information is represented by means of one and only one structure, the relation, which is usually portrayed as a table.

The relational model deals exclusively with aspects of the logical level of abstraction, so a table (an abstraction) is part of the logical level of a database (it is susceptible of undergoing logical operations, e.g., projections, joins, restrictions, etc.), so it is completely independent from the physical storage utlized by the database management system (DBMS for brevity) of use.

That being so, since the relational model has to do solely with logical-level aspects, it offers physical data independence, which sets DBMS (e.g., SQL platforms like IBM DB2, Microsoft SQL Server, PostgreSQL, etc. —that although quite powerful, strictly speaking, are semi-relational, i.e., they do not comply fully with the stipulations of the relational framework—) free to (a) store at the physical level of abstraction in any way their developers/vendors deem convenient (b) the data represented via logical-level tables.

It is important to note that the physical-level storage can in turn be divided in distinct sub-levels of abstraction.

Therefore, the physical arrangements depend on the particular DBMS under consideration, hence they can vary from platform to platform.

Examples of physical structures utilized by different SQL platforms are detailed

in the Storage Page Layout content from the PostgreSQL official documentation; and
in the post entitled Inside the Storage Engine: Anatomy of a record, regarding Microsoft SQL Server, by Paul Randal; and
in SQLite's Database File Format page.

In case you are interested, you may find of help this answer to a question about some of the different levels of abstraction of a computerized information system.


How to increase column size in Redshift database tables?

In AWS Redshift is now possible to alter ONLY VARCHAR column but under these conditions:

You can’t alter a column with compression encodings BYTEDICT, RUNLENGTH, TEXT255, or TEXT32K.
You can't decrease the size less than maximum size of existing data.
You can't alter columns with default values.
You can't alter columns with UNIQUE, PRIMARY KEY, or FOREIGN KEY.
You can't alter columns inside a multi-statement block (BEGIN...END).

See the documentation.


What specifically does OracleBulkCopy do, and how can I optimize its performance?

A few more days of reading and experimentation and I was able to (mostly) answer a lot of these:

I found this buried in the ODP.NET documentation (ironically not in the OracleBulkCopy docs):

  The ODP.NET Bulk Copy feature uses a direct path load approach, which is similar to,
  but not the same as Oracle SQL*Loader. Using direct path load is faster than conventional loading (using conventional SQL INSERT statements).

So it appears that it does use direct path.
This I was able to verify by doing a large bulk copy operation and getting the index properties from SQL Developer. The index did appear as UNUSABLE while the bulk copy was in progress.  However, I've also discovered that OracleBulkCopy.WriteToServer will refuse to run if the index starts in an UNUSABLE state, so clearly there's more going on here, because if it were as simple as disabling and rebuilding the index then it shouldn't care about initial state.
It does make a difference specifically if the index is also a constraint. Found this little gem in the documentation linked above:

  Enabled Constraints
  During an Oracle bulk copy, the following constraints are automatically enabled by
  default:

  NOT NULL
  UNIQUE
  PRIMARY KEY (unique-constraints on not-null columns)

  NOT NULL constraints are checked at column array build time. Any row that violates the NOT NULL constraint is rejected.

  UNIQUE constraints are verified when indexes are rebuilt at the end of the load. The index is left in an Index Unusable state if it violates a UNIQUE constraint.

The documentation is a little hazy on what happens during the load, especially with primary keys, but one thing is absolutely certain - it behaves differently with a primary key vs. without one. Since the OracleBulkCopy will happily allow you violate index constraints (and punt the index into UNUSABLE state when it's done), my hunch is that it's building the PK index during the bulk copy but simply not validating it until afterward.
I'm not sure whether the difference observed is within Oracle itself or just a quirk of the OracleBulkCopy. The jury's still out on this one.
OracleBulkCopy will throw an exception if an index is initially in the UNUSABLE state, so it's really a moot point.
If there are other factors, indexes (and especially PK indexes) are still the most important, as I found out by:
Creating a global temporary table with the same schema (using CREATE AS), then bulk copying into the temporary table, and finally doing a plain old INSERT from the temp table into the real table. Since the temp table has no index, the bulk copy happens very fast, and the final INSERT is also fast because the data is already in a table (I haven't tried the append hint yet, since a 5M row table-to-table copy already takes less than 1 minute).

I'm not yet sure of the potential ramifications of (ab)using the temporary table space this way, but so far it hasn't given me any trouble, and it's much safer than the alternative by way of preventing corruption of either the rows or indexes.

The success of this also pretty clearly demonstrates that the PK index is the problem, as that is the only practical difference between the temp table and the permanent table - both started with zero rows during the performance tests.

Conclusion: Don't bother trying to bulk copy more than around 100k rows into an indexed Oracle table using ODP.NET. Either drop the index (if you don't actually need it) or "preload" the data into a different (non-indexed) table.


How do I keep a 'running total' of purchases?

If the numbers have to be accurate, the trigger solution is the best one.
To prevent the application from messing with the summary table directly, have that table owned by a different user than the one the application uses to connect. Grant only the SELECT privilege to the application user.


Problem with LISTEN - NOTIFY mechanism in PostgreSQL

This is a psql issue.  psql is waiting for you to enter something on its command line. It only checks for notifications from the server when it has a reason to contact the server, and you haven't given it a reason.

They could have implemented it differently, so that it uses a select loop to wait on either the keyboard or the server, whichever becomes interesting first.  But that would make things much more complicated and it is not the way it was done.

This is the same reason that, if you bring down the server, psql doesn't notice until you try to do something that would try to talk to the server.

Note that the same issue will exist with clients/libraries other than psql, and different libraries solve (or fail to solve) it in different ways.


Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)?

FULL backups do not break log shipping.  FULL backups simply reset your differential base for all subsequent DIFF backups until the next FULL occurs.

Log shipping occurs in 3 phases:

1- Log backup on the primary

2- Copy log file to destination server (or network share)

3- Restore Log to secondary

If using default settings, these each occur in 15 minute intervals.  This means that it can take up to 45 minutes for a log file taken on the primary to be successfully applied to the secondary.

An easy way to verify that things are in sync is use the 'Transaction Log Shipping Status Report' on the secondary server by right clicking the server name in SSMS and navigating to the following:

EDIT: Here's where to look:


How to do a simple SELECT/UPDATE/DELETE performance test?

Simple down to 3ms accuracy:

SELECT GETDATE();
-- run query
SELECT GETDATE();

On SQL Server 2008 and above you can use SYSDATETIME() which has much greater accuracy.

You can also set a variable equal to the current time, run your query, and use DATEDIFF to determine the delta in whatever granularity makes sense (however this won't work across batches).

Other options:

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

You can also look at the execution plans to determine where a specific query might have a bottleneck. I might recommend the free SentryOne Plan Explorer for this. If you generate actual plans from within the tool, it will show you actual runtime metrics for each query (everything shown above, without the work).


Traditionally SQL Server relied on technologies like SQL Server Replication, Log Shipping or Change Data Capture/Change Tracking to replicate data across multiple locations. Unfortunately none are available in SQL Azure. Instead SQL Azure has the SQL Data Sync feature:

  SQL Data Sync (Preview) is a service of SQL Database that enables you to synchronize the data you select across multiple SQL Server and SQL Database instances. To synchronize your data you create sync groups which define the databases, tables and columns to synchronize as well as the synchronization schedule. Each sync group must have at least one SQL Database instance which serves as the sync group hub in a hub-and-spoke topology.

At this time SQL Data Sync is in preview and has some known limitations.

Also SQL Azure does offer backup/restore and export/import features, see Windows Azure SQL Database Backup and Restore, but these are all 'copy everything' solutions, not 'copy only what is changed'.

A possible solution is to switch to SQL Server on Windows Azure, which gives you a traditional SQL Server instance capable of doing all the technologies above, and you can have it replicate data to a SQL Server hosted on Amazon EC2. See Provisioning a SQL Server Virtual Machine on Windows Azure.


When a table is locked but SHOW PROCESSLIST shows nothing

The reason in my case was that one transaction is making a read-lock on a particular table leading to a "Lock wait timeout exceeded" because its not committed. So all other transactions are waiting to that particular tranx to do commit.
Now, why SHOW PROCESSLIST; show nothing ? In fact, it does.
you'll find it (the process) with a "Sleep" state and with no query shown in the info column like if it's just a regular connection to the database.
Let's check my example in the image :
In Blue, those are some very simple queries blocked (taking 120 second before a time-out) and the red one is the "ghost" or non-committed transaction.
To see those kind of transactions execute :
SELECT TRX_ID, TRX_REQUESTED_LOCK_ID, TRX_MYSQL_THREAD_ID, TRX_QUERY
FROM INNODB_TRX

And you may kill the process kill <id>; in which TRX_QUERY is null to free other transactions. In my case, KILL 33267342;
Make sure to check the non-committed transaction to see if a commit; or a Rollback clause is missing in order to ovoid such issues.


How to prevent SSDT publishing from dropping columns

There are three parts to this Question, so let's start with the "Block on Data Loss" option, dropped Tables, and then dropped Columns.

"Block on Potential Data Loss" Option

If you enable the "Block incremental deployment if data loss might occur" option:

on the "Debug" tab of "Project Properties"
using the /p:BlockOnPossibleDataLoss=True command-line option
specifying <BlockOnPossibleDataLoss>True</BlockOnPossibleDataLoss> in a publish config file

then SSDT will script out statements for each affected table, such as the following:

/*
The column [dbo].[Table1].[New2] is being dropped, data loss could occur.
*/
IF EXISTS (select top 1 1 from [dbo].[Table1])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO

/*
Table [dbo].[DontDropMe] is being dropped.  Deployment will halt if the table contains data.
*/
IF EXISTS (select top 1 1 from [dbo].[DontDropMe])
    RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127) WITH NOWAIT
GO

These statements are placed into the incremental** deployment / publish script ahead of all other changes. This causes the publish process to stop completely if any rows are found in any of those Tables. Hence, there is no such thing as a partial deployment when using the "Block on Data Loss" option.

** "Create" scripts always drop and recreate the Database, so there won't be any data to lose ;-).

Tables Dropped from the Model but still in the Target

By default, Objects (and Columns are not Objects) are not dropped from the Target. Objects that exist in the Target but are missing from the Model are only dropped if you:

enable the "DROP objects in target but not in project" option on the "Debug" tab of "Project Properties"
use the /p:DropObjectsNotInSource=True command-line option
specify <DropObjectsNotInSource>True</DropObjectsNotInSource> in a publish config file

If you are using the "Drop Objects not in Source" option but want to prevent the dropping of just Tables, you can exclude them by:

going to "Advanced Deployment Settings" (the "Advanced..." button on the "Debug" tab of "Project Properties"), then going to the "Drop" tab, and enabling the "Do not drop tables" option (in the scrollable list).
using the /p:DoNotDropObjectType=Tables command-line option
specifying <DoNotDropTables>True</DoNotDropTables> in a publish config file

Columns Dropped from the Model but still in the Target

This one is a bit trickier since Columns are not objects, and there is no specific deployment option to ignore this particular situation. Here are your options as best as I can tell:

Ignore tables entirely. You can do this by:

going to "Advanced Deployment Settings" (the "Advanced..." button on the "Debug" tab of "Project Properties"), then going to the "Ignore" tab, and enabling the "Exclude tables" option (in the "Excluded Object Types" scrollable list).
using the /p:ExcludeObjectType=Tables command-line option
specifying <ExcludeTables>True</ExcludeTables> in a publish config file

The downside here is that no new Columns or new Tables will be published. You can still manage those yourself by adding deployment scripts to the Project and setting their "Build Action" (a property of the SQL script) to "PreDeploy".
Create a "Deployment Contributor", which is a means of writing code to the SSDT API, to control how the DDL generation

Main page: Customize Database Build and Deployment by Using Build and Deployment Contributors
Example: Walkthrough: Extend Database Project Deployment to Modify the Deployment Plan

This is a very flexible and powerful option, especially if your religion practices self-flagellation ;-).

UPDATE: Even better, Ed Elliot published a blog post in which he details how to use a Deployment Contributor: Inside an SSDT Deployment Contributor. Even betterer, the example he used is to do just this: prevent the dropping of columns that are not in the Model. Even bestest, the example code has actually been incorporated into his
AgileSqlClub SqlPackage Deployment Filter project, and the feature is described in the following blog post: Deployment Contributor KeepTableColumns Filter. And, if the feature doesn't do exactly what you need, then the source code is available and you can make any changes you want and recompile.

Don't use SSDT for Schema changes. Just use it to push code (Stored Procedures, Functions, Triggers, SQLCLR Objects, etc). You can write your own schema deployment scripts and include them in the Project. You can have them included in the deployment scripts by setting their "Build Action" to "PreDeploy".
File a Connect Suggestion to have an option added for "DoNotDropColumns" or something like that :-)


Galera cluster without having to specify all hosts on wsrep_cluster_address

EDIT:

My original answer below is still correct, but the is an easier way to handle this: multicast.  I didn't mention this before because I thought galera could only use multicast for replication, which is probably something else you probably want to configure to save bandwidth with a large number of nodes.  Basically, you can use a multicast address for the cluster connection in wsrep_cluster_address.  Using multicast will allow you to configure a single address on every node.  I'm using multicast for replicating data in my galera cluster, but unicast for the cluster connection.  The page I link to doesn't say anything explicitly about it, but you might need to configure a different multicast address for the galera data replication channel (gmcast.mcast_addr) and the cluster information channel (wsrep_cluster_address).  More information on using multicast with galera is available in a Percona blog post.

Original:

It is not required to have every node in the cluster defined in wsrep_cluster_address.  When a node starts up, it tries to connect to the provided addresses to connect to an existing cluster and then connect to all of the other current nodes in the cluster.  It only really needs to connect to a single node in the cluster to join since it connects to other nodes based on the current state of the cluster.  Because of that, if you specify a few nodes that should always be in the cluster, you should be fine even if there are many other nodes that are not specified.


As of 11/4/15, there is an update to the SQL Server provider that clears up creating the data source. You may need to refresh the data source to actually list the tables.


What are we Doing when we Connect to SQL Server?

Usually, you are making a TCP connection to a remote host.

SSMS would ask your DNS server for the IP address associated with the hostname of the server, then initiate a TCP connection to that address on remote port 1433.

SSMS and MS SQL Server would then begin a conversation using the binary Tabular Data Stream protocol (TDS).

You type a query. SSMS encodes this query into TDS. SQL Server gets it, parses it, runs it, returns some data (encoded in TDS), back to SSMS, which decodes it and displays it on screen.

However, you can also connect to SQL Server installed on the same OS as SSMS, or running in a virtual machine on the same box.


How do I limit the size of my Postgres log?

The PostgreSQL Error reporting and logging documentation states:

  log_rotation_size (integer)

  When logging_collector is enabled, this parameter determines the maximum size of an individual log file. After this many kilobytes have been emitted into a log file, a new log file will be created. Set to zero to disable size-based creation of new log files. This parameter can only be set in the postgresql.conf file or on the server command line.

So, I guess you should set this parameter to the desired value. My postgresql.conf option has the default values:

#log_rotation_size = 10MB       # Automatic rotation of logfiles will
                # happen after that much log output.
                # 0 disables.


How to find out ALL the users that can connect to SSAS?

Check the server property BuiltinAdminsAreServerAdmins. Till SQL Server 2014 it defaults to true. Not sure if it will be  changed in 2016 (hopefully).

Link: https://msdn.microsoft.com/en-us/library/ms174561(v=sql.120).aspx


Mariadb always writes tmp disk to a file

Read the manual where it mentions using MEMORY for temp tables and when it will switch to MyISAM, either because of limitations such as TEXT or because of exceeding LEAST(tmp_table_size, max_hash_table_size).  Further note that VARCHARs become CHAR for calculating when to spill to disk.  (This is fixed in 8.0; I doubt if it is yet in 10.4.)  Example: VARCHAR(250) utf8mb4 counts as 1000 bytes.

See EXPLAIN SELECT.  If it says Using temporary, that is a clue.  However, that phrase is usually on the first line of the EXPLAIN not necessarily on the line that triggered the need.

Each of DISTINCT, GROUP BY, and ORDER BY may need a temp table.  If the query involves a JOIN, then it is more likely that one of those will trigger the need for a temp table.

Do not fear temp tables; some queries simply require such.  A simple example is any query with GROUP BY something ORDER BY something_else.

Granted, the above does not say whether the temp table is on disk or in memory.

If that does not suffice, please provide a specific case.  Show us the query and SHOW CREATE TABLE and EXPLAIN SELECT .... We should be able to help you change so that it does not need a disk-based temp table or explain why it must use such.

To check to see if a particular query uses disk temp table(s), do something like:

SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';
SELECT ...
SHOW SESSION STATUS LIKE 'Created_tmp_disk_tables';

That is a counter, hence the need to check before and after.

The slowlog might also give you a clue.


It was a totally unexpected underlying issue. As Hilary Cotter pointed out in this post, running the copy job from command prompt on secondary server "revealed" that .NET 3.5 framework was missing. Installing it fixed the issues. Beleive it or not, I did not do anything else other than installing .NET 3.5 framework.


SQL Server Timeout: Drop and Recreate Index Solves the Issue

There is possibility of fragmentation issue with the index.

Check possibility of index fragmentation -

Here is a simple query to check fragmentation on your existing indexes:

DECLARE @DBNAME VARCHAR(130);
SET @DBNAME = 'MYDBNAME';

DECLARE @DBID INT;
SET @DBID = DB_ID(@DBNAME);

SELECT
OBJECT_ID AS objectID
, index_id AS indexID
, avg_fragmentation_in_percent AS fragmentation
, page_count
INTO #indexDefragList
FROM
sys.dm_db_index_physical_stats
(@DBID, NULL, NULL , NULL, N'Limited')
WHERE
index_id > 0
OPTION (MaxDop 1);

SELECT
i.[name] as indexname,
d.fragmentation,
d.page_count
FROM
#indexDefragList d
INNER JOIN sys.indexes i
ON d.objectid = i.object_id
ORDER BY
d.fragmentation DESC

DROP TABLE #indexDefragList

This will return a list of all indexes in your current DB with their fragmentation in %.

Rebuilding of indexes will solve the problem.

Rebuilding of indexes is not supported automatically in any version of Microsoft SQL Server - the reason being is that rebuilding indexes can potentially be very expensive operations, and so need careful scheduling and planning.

In many environments special scripts will be written to handle this, for example:

http://weblogs.sqlteam.com/tarad/archive/2008/09/03/Defragmenting-Indexes-in-SQL-Server-2005.aspx

Note that whilst SQL can automatically update statistics for you in many cases there is a performance gain to be had by managing these more carefully as well.

You can easily build a script to automatically rebuild or reorganise them. There is a great article from SQLFool on this including a complete pre-made script.

Reference link


Create a MySQL Database using a MS SQL database dump (.CSV)

Hmm... you say that you have a .csv dump of the tables of a database? What version of SQL Server was this database extracted from? 679 tables is a largish system and this will require a lot of work.

Where are the inter-table relationships stored? You can't store FOREIGN KEY relationships in a .csv dump. Neither can you store CHECK CONSTRAINTs which MySQL doesn't support anyway (although this can be done programmatically through triggers - but can be tricky and error-prone). `

More importantly, a .csv won't tell you what the datatype is - it might give a clue, but a column with a run of INTEGERs could actually require a FLOAT/REAL type. Also, how do you know the real length of a VARCHAR field? Just because all of yours are < 30 CHARs, who's to say that a greater length won't be required?

This is to say nothing of TRIGGERs and STORED PROCEDUREs which are a completely different kettle of fish and could (depending on your database complexity) require months. In the area of these, MySQL is very much a lightweight in terms of its capabilities in this area.

MS SQL Server is a much more powerful RDBMS than MySQL - having the data is only a first step - but there'll be way more to do than just "simply" loading tables.

Your 1st step should be to get hold of the DDL and DML of this database, because without it, your task is going to be virtually impossible.
You should take a look at this article - it gives you three different methods of converting (a running) Microsoft SQL Server to MySQL.

It appears to particularly recommend MySQL Workbench - which I also recommend as an excellent GUI tool for such work. This depends on having ongoing access to a running server. You'll also need an ODBC connection to the SQL Server.

A question here gives you two options to extract DML and DDL from a SQL Server database to build a script (that won't work immediately on MySQL) - but with this, at least you have a fighting chance. You may also find this thread helpful.

Even then, you'll have a lot of testing to do - you're going to need Unix (or Windows Powershell) scripting or some programming (ideally Python or Perl) skills.

After you have basic table scripts - don't expect many of them to work first time - determine all tables which don't have any FOREIGN KEYs - usually lookup/reference tables (countries, cities, codes &c.) and make sure they load successfully. Then find the tables that have more and more FOREIGN KEYs and gradually build a cohesive system.

I would suggest a staging approach - you'll have to go back and check that your data in the MySQL database corresponds exactly to the data in your SQL Server database.

Another suggestion that I would have is to do a smaller project first to get a realistic idea of your timeframe. You could start here maybe.

You need to get a serious project plan in place and if you're doing this on your own, you need to plan for a serious amount of time - if you're not really a database person, you need to bring somebody on board. Best of luck with your project!


Relationships between subtypes of an entity

When implementing subtypes of an entity there are various solutions on Oracle:

Create one wide table with the attributes of all subentities. Entity type stored in a field and used to enforce business rules ("You may only fill column A for entity type B").
Create one master table with the shared attributes and per subentity another table. And relations back to the master table.
Create one table for each subtype and include the shared columns.

Here you are suggesting option 2. Option 2 keeps you master table rather narrow in terms of columns and might save some storage. However, it has serious drawback when you want to enforce business rules declaratively or through PL/SQL triggers or alike.

For a very large table and option 2 I would recommend against using declarative constraints, even when I am a lover of robust databases. It can get really slow, you need to appropiate indexes for locking and parallel loading high volumes can be challenging in terms of performance and parallelization.

For a very large table I would recommend either option 3 or option 1. Option 1 is rather simple to understand and given the large number of columns Oracle allows in one table, it shall not be a problem. Storage should not be an issue; trailing null columns are not stored at all in data blocks and null values within the row itself only require 1 byte per column to signal "hi, I have no value for this column". For option 1 you can write well-performing and parallellizable views that select only one or more of the subtypes. Option 1 also can be loaded fast in parallel. And you can implement many rules requiring a fk as a check constraint on a row, which does not involve locking or other problems hindering high volume use.

Unless... of course when one of the subtypes have comparetively very wide columns compared to the rest. That would make each of the data blocks contain considerably less rows. In that case I would recommend option 3, since they are such wide differences and use 'union all' constructs to run queries on the whole supertype. A mix can be option 3 and option 1: option 3 for the subtypes with optional columns with lots of data (varchar2(4000) filled nicely) and option 1 for the other subtypes.


Change a field in Database on a Particular Date

If I were starting from scratch, I might build this logic into a computed column. This may not be the best idea, but I figured I'd throw it out there as an alternative.

CREATE TABLE dbo.Grades
(
    StudentiD BIGINT,
    CourseId BIGINT,
    CourseGrade VARCHAR(2),
    FailDate DATE,
    FinalGrade AS CASE WHEN GETDATE() >= FailDate
                            AND CourseGrade = 'I' THEN 'F'
                       ELSE CourseGrade
                  END
);

When data gets inserted, you can have a default for (and a modifiable by teachers/admins) FailDate.

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 1, 'A', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 2, 'I', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 3, 'B', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 4, 'I', GETDATE() )

INSERT dbo.Grades ( StudentiD, CourseId, CourseGrade, FailDate )
VALUES ( 1, 5, 'C', GETDATE() )

This allows them to give extensions or permanently get rid of the concept by making the value NULL.

So the query

SELECT *
FROM dbo.Grades AS g

Gives us:

StudentiD   CourseId    CourseGrade FailDate    FinalGrade
1           1           A           2017-05-19  A
1           2           I           2017-05-19  F
1           3           B           2017-05-19  B
1           4           I           2017-05-19  F
1           5           C           2017-05-19  C


For a varbinary column created with ANSI padding set OFF, values containing trailing zeros, such as 0x100 will have the trailing zeros truncated prior to the value being written to the column.  This certainly does change the value contained in the column.

This might be useful for a system which displays values in a left-aligned fashion with right-padding zeros added at display-time, thereby reducing storage requirements.  For instance, if your system knows all numbers stored in a column are 8 hex digits, left aligned, then 0x1 becomes 0x01000000 at run-time.  In my opinion, the confusion this would create for future developers would far outweigh the potential space savings.

Take for example, the following:

USE tempdb;

SET ANSI_PADDING ON;
GO

IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    v varbinary(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest (v)
VALUES (0x100);

SELECT *
FROM dbo.PadTest;

Result:

╔════════╗
║   v    ║
╠════════╣
║ 0x0100 ║
║ 0x0001 ║
╚════════╝

SET ANSI_PADDING OFF;
GO

IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    v varbinary(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest (v)
VALUES (0x100)
    , (0x001);

SELECT *
FROM dbo.PadTest;

Result:

╔════════╗
║   v    ║
╠════════╣
║ 0x01   ║
║ 0x0001 ║
╚════════╝

The first table defined with ANSI padding enabled requires more storage for the first row than the second table defined with ANSI padding disabled.  As you can see, this does indeed change the values stored in the table since 0x01 <> 0x0100.

SET ANSI_PADDING OFF; for varchar columns operates in a similar way, replacing the zeros with spaces.  Take the following example:

SET ANSI_PADDING OFF;
GO
IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    [varchar(10)] varchar(10) NOT NULL
    , [char(10)] char(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest ([varchar(10)], [char(10)])
VALUES ('a', 'a')
    , ('a         ', 'a         ');

SELECT [varchar(10)] = pt.[varchar(10)] + '|'
    , [char(10)] = pt.[char(10)] + '|'
FROM dbo.PadTest pt

╔═════════════╦══════════════╗
║ varchar(10) ║ char(10)     ║
╠═════════════╬══════════════╣
║ a|          ║ a          | ║
║ a|          ║ a          | ║
╚═════════════╩══════════════╝

I've added the pipe symbol to the output to show where padding exists and doesn't exist.

SET ANSI_PADDING ON;
GO
IF OBJECT_ID(N'dbo.PadTest', N'U') IS NOT NULL
DROP TABLE dbo.PadTest;
GO
CREATE TABLE dbo.PadTest
(
    [varchar(10)] varchar(10) NOT NULL
    , [char(10)] char(10) NOT NULL
) ON [PRIMARY];
GO

INSERT INTO dbo.PadTest ([varchar(10)], [char(10)])
VALUES ('a', 'a')
    , ('a         ', 'a         ');

SELECT [varchar(10)] = pt.[varchar(10)] + '|'
    , [char(10)] = pt.[char(10)] + '|'
FROM dbo.PadTest pt

╔═════════════╦══════════════╗
║ varchar(10) ║ char(10)     ║
╠═════════════╬══════════════╣
║ a|          ║ a          | ║
║ a         | ║ a          | ║
╚═════════════╩══════════════╝

The char(10) columns operate "correctly" for both variations of the SET ANSI_PADDING statement - the varchar(10) columns are a bit problematic with SET ANSI_PADDING OFF; since it's removing the spaces from the value.  This is why in Microsoft's ANSI_PADDING documentation, it states:

  We recommend that ANSI_PADDING always be set to ON.


ORA-01196: file 1 is inconsistent due to a failed media recovery session

Looks like you need to restore archivelogs. If you have them backed up to tape, you may need to do something like:

run
{
  allocate channel ch1 type 'sbt_tape' parms
  restore archivelog from scn 16625729961 until scn 999999999;
}

Replace the until scn 999999999 with an appropriate scn from your primary database.


You can use the INSTR function of MYSQL to find the starting position of the word matching your delimiter '%#aw%'. And then use sub string to fetch the word of your interest.

for eg. SELECT SUBSTR(description,INSTR (description,'#'))
FROM photo where description like '%#aw%';

The problem here though is that if the word is followed by anything, it will also be a part of your output. You will need to find the position of the immediate next 'blank space' and use it as the LEN parameter (the 3rd param in SUBSTR function).


Get ip postgresql master from recovery.conf using non-superuser

IMHO and as I pointed out in my comments I think there is a missing parentheses in your command. Just after first SELECT regexp_split_to_array

I've tested on a postgres server (a turnkey VM) using a copy of a recovery.conf file, and this is the result:

root=# SELECT regexp_split_to_array (
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
       ' ');
                                   regexp_split_to_array
--------------------------------------------------------------------------------------------
 {primary_conninfo,=,'user=repmrg,port=5432,sslmode=prefer,sslcompression=1,host=10.0.0.1'}
(1 row)

Using unnest:

root=# SELECT unnest(regexp_split_to_array (
  (SELECT *
   FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
   WHERE a ~ '^ *primary_conninfo'),
       ' '));
      unnest
------------------
 primary_conninfo
 =
 'user=repmrg
 port=5432
 sslmode=prefer
 sslcompression=1
 host=10.0.0.1'
(7 rows)

Filtering by host:

root=# SELECT *
root-# FROM   (SELECT unnest(regexp_split_to_array (
root(#                       (SELECT *
root(#                        FROM regexp_split_to_table(f_showfile('recovery.conf.sample'),'\n') t(a)
root(#                        WHERE a ~ '^ *primary_conninfo'), ' ')) as elem) T
root-# WHERE elem LIKE '%host%'
root-# ;
      elem
----------------
 host=10.0.0.1'
(1 row)

root=#


Report based on MV: Deliberately make the report fail if MV is out of date?

Why bother at all with a new column, when you can just check LAST_REFRESH_DATE in ALL_MVIEWS before executing the actual query.
ALL_MVIEWS
And there is basic/advanced query rewrite as well.


How to compare two SQL Servers instance level objects for a migration?

If you're only concerned about Agent Jobs and logins, I think the easiest approach is to leverage a linked server and querying the relative tables.  I would do this by first creating a linked server on the old instance that points at the new instance.  Once I had that, I could run the following queries:

Logins

SELECT * FROM [SQL2005].[master].[sys].[server_principals]
WHERE name NOT IN (SELECT name FROM [SQL2008].[master].[sys].[server_principals])

Agent Jobs

SELECT * FROM [SQL2005].[msdb].[dbo].[sysjobs]
WHERE name NOT IN (SELECT name FROM [SQL2008].[msdb].[dbo].[sysjobs])

There might be some inconsistencies with some server specific accounts/logins, but these queries should show you everything that exists in the old server that doesn't exist in the new server.  You should be able to extend this logic to other facets as long as you had system tables to query for it.

As for objects that need to be synchronized, I think the best approach is to review your instance for the objects that should be migrated.  Typically this would only be logins and Agent Jobs, but could also include linked servers (sys.servers), end points (sys.endpoints), and other objects.


That is correct. Forward compatibility is guaranteed. Backwards compatibility does not exist*. Having 4 different BIDS/SSDT instances on a machine is a pain I've known all too well but there's not much you can do.

There are ways of constructing SSIS packages such that your solution can target whatever version of SQL Server will be hosting them but that's installing, purchasing and learning a separate product (Mist).


How to recursively find gaps where 90 days passed, between rows

As I read the question, the basic recursive algorithm required is:

Return the row with the earliest date in the set
Set that date as "current"
Find the row with the earliest date more than 90 days after the current date
Repeat from step 2 until no more rows are found

This is relatively easy to implement with a recursive common table expression.

For example, using the following sample data (based on the question):

DECLARE @T AS table (TheDate datetime PRIMARY KEY);

INSERT @T (TheDate)
VALUES
    ('2014-01-01 11:00'),
    ('2014-01-03 10:00'),
    ('2014-01-04 09:30'),
    ('2014-04-01 10:00'),
    ('2014-05-01 11:00'),
    ('2014-07-01 09:00'),
    ('2014-07-31 08:00');

The recursive code is:

WITH CTE AS
(
    -- Anchor:
    -- Start with the earliest date in the table
    SELECT TOP (1)
        T.TheDate
    FROM @T AS T
    ORDER BY
        T.TheDate

    UNION ALL

    -- Recursive part
    SELECT
        SQ1.TheDate
    FROM
    (
        -- Recursively find the earliest date that is
        -- more than 90 days after the "current" date
        -- and set the new date as "current".
        -- ROW_NUMBER + rn = 1 is a trick to get
        -- TOP in the recursive part of the CTE
        SELECT
            T.TheDate,
            rn = ROW_NUMBER() OVER (
                ORDER BY T.TheDate)
        FROM CTE
        JOIN @T AS T
            ON T.TheDate > DATEADD(DAY, 90, CTE.TheDate)
    ) AS SQ1
    WHERE
        SQ1.rn = 1
)
SELECT
    CTE.TheDate
FROM CTE
OPTION (MAXRECURSION 0);

The results are:

╔═════════════════════════╗
║         TheDate         ║
╠═════════════════════════╣
║ 2014-01-01 11:00:00.000 ║
║ 2014-05-01 11:00:00.000 ║
║ 2014-07-31 08:00:00.000 ║
╚═════════════════════════╝

With an index having TheDate as a leading key, the execution plan is very efficient:

You could choose to wrap this in a function and execute it directly against the view mentioned in the question, but my instincts are against it. Usually, performance is better when you select rows from a view into a temporary table, provide the appropriate index on the temporary table, then apply the logic above. The details depend on the details of the view, but this is my general experience.

For completeness (and prompted by ypercube's answer) I should mention that my other go-to solution for this type of problem (until T-SQL gets proper ordered set functions) is a SQLCLR cursor (see my answer here for an example of the technique). This performs much better than a T-SQL cursor, and is convenient for those with skills in .NET languages and the ability to run SQLCLR in their production environment. It may not offer much in this scenario over the recursive solution because the majority of the cost is the sort, but it is worth mentioning.


Allow two users permission to execute single agent job

The issue here is cross-database ownership chaining, basically once you leave the context of the AdventureWorks database, you don't have the elevated rights that dbo/sa implies and cannot actually execute the procedure in msdb.

You should recreate the stored procedure in msdb instead and grant your unprivileged user CONNECT permission to msdb and EXECUTE permission on the new procedure in msdb.

Alternatively, you can turn TRUSTWORTHY on for the AdventureWorks database but, generally, that is ill-advised. See this answer from Aaron Bertrand for additional info and a number of great links explaining the risks of TRUSTWORTHY.


What does column family-oriented datastore mean in practice

Column families in HBase are a method of "vertical partitioning" of an HTable, which separates subsets of the table columns into distinct physical structures. This improves query performance (via reduced I/O requirements) if your use cases frequently access certain groups of columns together.
Within each column family data are stored in a row-oriented manner. So, assuming that in your example "001".."004" are the record keys, and two column families, the data would be stored somewhat similar to this:
CF1                     CF2
|Key|First|Last   |     |Key|Band |Salary |
|---|-----|-------|     |---|-----|-------|
|001|Joe  |Smith  |     |001|10   |40000  |
|002|Mary |Jones  |     |002|12   |50000  |
|003|Cathy|Johnson|     |003|11   |44000  |
|004|Bob  |Jones  |     |004|22   |55000  |

Thus, if you only needed to calculate the average salary, you would read a smaller file without unnecessary data in it.
More about HBase record layout.


How do I delete rows from a table on a linked server

Why not just:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'DELETE [' + @DBName + '].dbo.tblname WHERE foo = ''bar'';';
EXEC linkedserver...sp_executesql @sql;


What are the considerations when using SERLIALIZABLE over READ COMMITTED isolation level in Oracle?

Is there any extra cost associated with, for example, having to go potentially further back in the undo data to return the records?

Example:

You have a table getting one insert every minute.

If a SERIALIZABLE transaction started at 4:00pm, and queried that table at 4:15pm, then it will have to read at least 15 undo records to return to the state of the table when your transaction started. If there had been no activity on the table, then there would be no additional workload.

Ultimately it would depend on how long the serializable transaction last, and what changes are made to the data it looks at during that time.

I'd add that it is rare to see SERIALIZABLE used. The same mechanisms support DBMS_FLASHBACK with the advantage that you can specify the exact time for which you want to view the data (eg Closing balance at 5pm).


What is the best way to query these tables? Inner Join?

This would show all shows, that run that ne song.
SELECT
    s1.date
    , s1.venue, s1.city, s1.state, s1.length, s1.source, s1.source2, s1.rating,
    s3.name
FROM
        shows s1
    INNER JOIN
        shows_songs s2
        ON s2.shows_id = s1.id
    INNER JOIN
        songs  s3
        ON s2.songs_id = s3.id
WHERE s3.name = "Walk like an Egyptian"

Foreign keys are needed when you want to guarantee, that in the shows_songs  are only shows/songs that already exist, but usually that you will do at app level, but with a foreign key restrainto you can't enter false data.
another Option is a FOREIGN KEY with ON DELETE CASCADE for example between shows and shows_songs, this would delete an entry from shows_songs when the shows would be deleted.
But in your case you don't need a foreign key.
An INNER JOIN is then need when you need to retrieve data from different shows.name and songs.name, then you need always to join the bridge table before the second table.
And take a good look at Aggregation function
Because
SELECT
    s1.venue,s3.name
FROM
        shows s1
    INNER JOIN
        shows_songs s2
        ON s2.shows_id = s1.id
    INNER JOIN
        songs  s3
        ON s2.songs_id = s3.id
WHERE s1.id = 1

Would result in
venue    name
venue1   song1
venue1   song2
venue1   song3
venue1   song4

Which could be made to a single row with GROUP_CONCAT
this is a small database, so you can test the queries and results quite fast


3 GB dump isn't huge. You can set up the MySQL locally, then import that dumpfile and then dump the base again by mysqldump with --no-data option.


As stated in the official Identity Guidelines

  In Japan, a turtle logo is used in place of the elephant due to domestic terrorist symbolism.

The elephant has negative connotations in Japan because members of a cult which uses an elephant as its logo were responsible for some terrorist attacks.

The group in question wore blue elephant masks while campaigning for Japan's parliament a few years before their murder spree, rather than it being exactly a logo. Personally, I would rather not post links to pages about their exploits, but if you want to go looking it doesn't take two minutes on a google search to find articles if you use words like: elephant deadly sarin gas tokyo

You can see the turtle iconography used quite extensively on the Japanese Language PostgreSQL site

While other Japanese companies like Zojirushi may retain use of the Blue Elephant iconography, that company in particular dates back to 1918 and its branding dates from the 60's. PostgresSQL's use of elephant iconography dates from only the late 90's which corresponds very nearly with the horrible events previously mentioned and would have no doubt been a contributing factor in the community decision to not use the imagery in Japan.


Is there a limit to how many backup files I can restore from for a single database on RDS SQL Server?

There is a limit of 10 backup files.

When attempting to restore from more than 10 backup files, the following error occurs:

  The Restore task is unable to restore from more than 10 backup
  file(s). Please reduce the number of files matched and try again.