Optimizing ORDER BY for simple MySQL query

The EXPLAIN SELECT you posted definitely seems counter-intuitive.

If your query included WHERE s.id = ... then the query plan you're seeing might make a little bit more sense, but I'm assuming you're not.

It looks like the optimizer is getting distracted by the facts that supplier is a smaller table and that the supplier_id index in the po table can be used as a covering index... and with those facts in hand, it's overlooking the seemingly-obvious fact that the tables should be read in the opposite order than the one it chooses.

Here are two alternatives.

-- use the STRAIGHT_JOIN directive to insist that the optimizer process the tables in only the listed order:

SELECT STRAIGHT_JOIN * FROM `po`
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

-- use the FORCE KEY index hint to direct the optimizer to prefer the primary key of the po table:

SELECT * FROM `po` FORCE KEY (PRIMARY)
INNER JOIN po_suppliers s ON po.supplier_id = s.id
ORDER BY po.id ASC
LIMIT 10;

The first option is probably the better option, since FORCE KEY, in spite of the name, is still only a "hint" that the optimizer can choose to ignore, while STRAIGHT_JOIN genuinely does force the hand of the optimizer to join the tables in the order they're listed.


what is the difference of sequece of index in MySQL

Think of an index as an ordered data structure. Technically it's more than that, but when it comes to query optimisation, usually this is all you need to know.
To explain why this matters in your case, I'll use a common analogy: a phonebook. A phonebook is a dataset ordered by (surname, name), so it's actually an index made of paper.
Because it's ordered, looking for Tom Baker is reasonably fast. Finding all Bakers would also be reasonably fast (unless they're thousands... but that's because you're a human, not a machine). Finding all Toms would be impossible. Or better said, it's possible, but you have to scan ALL entries from the beginning to the end. It's slow, it's like not using an index at all. If the index were ordered by (name, surname), you could find all the Toms but not all the Bakers, and you'd still be able to find Tom Baker.
So now you should understand why, in general, the order of columns of an index matters. But let's see why it matters in your case.
You want to restrict your search to a certain hub_sync, and then group the rows by game_id. In this order! The opposite wouldn't work: once you grouped the rows you lose the hub_sync information.
So first you apply the WHERE, and you need to use the first row of the index for that (remember: you can quickly find the Bakers, not the Toms). Then you can group the rows by the second row of the index.
As a side note, you can use DISTINCT instead of GROUP BY. It's not faster, but it makes it more clear what you want to achieve.


Is it possible to configure PostgreSQL to automatically close idle connections?

Finally, I have a solution.
I'll use the tool PgBouncer with its parameter server_idle_timeout.

Another approach would consist in using some cron-like tool running a query periodically to find idle connections.

Once those idle connections are found, a simple call to pg_terminate_backend will close them.

See an in depth description of this approach in the anwser of the following question:

How to close idle connections in PostgreSQL automatically?


Is there a document that specifies un-installation of SQL server 2016 R server

Try this guide, maybe it will work for you: Cleanly Uninstalling Stubborn SQL Server Components by Aaron Bertrand.

The easiest way: Control Panel -> Programs & Features -> and uninstall every program that contains the words "SQL Server".

or, using PowerShell:

Get-ChildItem HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall |
select @{Name='Guid';Expression={$_.PSChildName}}, @{Name='Disp';Expression={($_.GetValue("DisplayName"))}} |
where-object {$_.Disp -ilike "*SQL*R"} |
where-object {$_.Guid -like '{*'} |
% {"rem " + $_.Disp; 'msiexec /x "' + $_.Guid + '"'; ''} > uninstallR2.bat


Cumulative Sum by distinct contact_ID by year-month

OK since no-one seemed to reply (except for Michael Green - Thanks) i did the MySQL statement the long way. If anyone has an idea how to make it smaller or how to combine them, then please let me know:

Last months inactivity:

Select b.date , count(b.contactID) FROM
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 0 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 0 MONTH)
                   ORDER BY date desc)a,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
                   ORDER BY date desc)b
WHERE a.ContactID = b.ContactID

The result was:

date    inactivity
2016-03 83108

2 Months ago inactivity:

Select c.date , count(c.contactID) FROM
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 0 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 0 MONTH)
                   ORDER BY date desc)a,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 1 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)
                   ORDER BY date desc)b,
(SELECT distinct Date_FORMAT(ss1.time, "%Y-%m") as date, c1.id as ContactID
           FROM `contacts` c1, `statengineSent` ss1
           LEFT JOIN `statengineViews` AS sv1
           ON (sv1.email = ss1.email  AND sv1.dispatchID = ss1.dispatchID AND sv1.email is NULL)
           WHERE ss1.email = c1.email
           AND YEAR(ss1.time) = YEAR(CURRENT_DATE - INTERVAL 2 MONTH)
                   AND MONTH(ss1.time) = MONTH(CURRENT_DATE - INTERVAL 2 MONTH)
                   ORDER BY date desc)c
WHERE a.ContactID = b.ContactID AND b.ContactID = c.ContactID

The result was:

date    inactivity
2016-02 82355

AS you can see right now i'm using 2 statements which already are very big and sloppy and also i need to run 12 different queries to get the whole year inactivity. Can anyone help me combine these or make it work as an interval for a whole year?
It does the trick but as i said, it's very sloppy :(


Oracle EM 13c automatically logged in as dbsnmp

Found the answer myself.

Login with the custom super administrator account

Setup -> Security -> Preferred Credential

Select "Database Instance"

Click on "Manage Preferred Credentials" button above table.

Clear dbsnmp credentials under "Target Preferred Credentials".

Next time you access the database instance you will be asked to choose the credentials to use.


SQL transaction: Delete between two selects possible?

Is it possible that the children (second query) of the parent(first
  query) are deleted (from another transaction) after the initial/first
  select?

Certainly it is possible.

When you execute your first query only the result row of the first query has S-lock (I suppose your table has an index on id).

If you want to lock the rows where this id is a parent, you should just do this select in the same query:

select *
from myTable
where parent = (
    select id
    from myTable
    where ...);

This way you lock that id and all the rows where parent = this id so they cannot be deleted.

Note that only these rows are locked because your TIL is repeatable read, this means that if during the duration of this transaction other rows with this parent are inserted they can also be deleted.

To prevent insertions/deletions of the new rows with this parent you should use serializable TIL.

Here is my repro:
The first session(56) creates the table and fills it with id 1..10000
and then adds random parents from the same range 1..10000.

Then I search for most "popular" parent with max count of corrensonding childs,
and in session 54 I try to delete one of the child, the query is blocked.

The "central" window shows you the locks held, I used it twice: first to see what my first session locks, second time to see what the second session is waiting for. First session has 1+ 6 locks 1 on parent row and 6 on child rows that you see in the output of the first query, the second session waits on conversion from update lock to exclusive lock on the row it wanted to delete.
The session is "hanging" until rollback or commit.

Here is the picture:

if object_id('myTable') is not null drop table myTable;
create table myTable(id int identity not null primary key clustered,
                     parent int,
                     col varchar(10) not null);

alter table myTable
add constraint FK_paerent_id foreign key (parent) references myTable(id);

alter table myTable add constraint UQ_myTable_col unique(col);

create index ix_parent on myTable(parent);

insert into myTable with(tablock) (col)
select top 10000 cast(row_number() over(order by getdate()) as varchar(10)) as col
from sys.columns c1 cross join sys.columns c2;

update myTable
set parent = abs(checksum(newid())) % 10000 + 1;

select parent,
       count(*) as cnt
from myTable
group by parent
having count(*) > 1
order by cnt desc;

set transaction isolation level repeatable read;

begin tran

select id
from myTable
where parent = (
    select id
    from myTable
    where col = '481');

--rollback


Cannot open database requested by the login. The login failed

sp_readerrorlog

As pointed out already by @Shanky read the error log (ERRORLOG) by setting off the sp_readerrorlog command:

sp_readerrorlog 0,1,'<your_DB>'

You could also use the official xp_readerrorlog procedure with the same parameters:

xp_readerrorlog 0,1,'<your_DB>'

xp_readerrorlog is the underlying extended stored procedure for the sp_readerrorlog stored procedure which is explained here: SQL Internals : Useful Parameters for xp_readerrorlog

Read ERRORLOG directly

If you are unable to read the ERRORLOG with the stored procedures, then manually read the ERRORLOG files (ERRORLOG.*) to find any errors.

Querying Registry to find location

If you can't find the location of the ERRORLOG file, then retrieve the path from the registry:

DECLARE @regout VARCHAR(255)
exec xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer\Parameters', 'SQLArg1', @regout OUTPUT
SELECT @regout

Don't forget to replace MSSQLSERVER with your instance name if you are using a different instance than the default instance (...\MSSQL11.MSSQLSERVER\...).

Query SQL Server directly

SQL Server itself stores some basic information about the status of the databases in sys.databases:

select name, state_desc from sys.databases

You should get an output similar to the following:

name    state_desc
master  ONLINE
tempdb  ONLINE
model   ONLINE
msdb    ONLINE
<your_DB>   ONLINE

Permissions

If your database <your_DB> is online, then you possibly don't have adequate permissions to read the database. Change your user's permissions.

Once you have found more information you will possibly be able to pinpoint the issue.


How do I control order/arrangement of certain columns in a resultset?

You could create a new ordering table with sort order and link that to your original table. Then sort it by the sort order.
Like this:

DECLARE @SortTable TABLE (LastName nvarchar(50), SortOrder int)

INSERT INTO @SortTable
VALUES        ('Carson', 1)
            , ('Vargas', 2)
            , ('Ansman-Wolfe', 3)
            , ('Pak', 4)
            , ('Reiter', 5)
            , ('Varkey Chudukatil', 6)
            , ('Blythe', 7)
            , ('Mitchell', 8)

SELECT      YT.*
FROM        YourTable     AS YT
INNER JOIN  @SortTable    AS ST
    ON      ST.LastName   = YT.LastName
ORDER BY    ST.SortOrder

I used a table variable, but of course you can also use a normal table that you add in your database. Make sure you add all the possible values for lastname, otherwise you would miss out rows.


Why Oracle 19c Database EM Express Webpage is not opening in Microsoft Edge?

I want to open the localhost:5500/em

What makes you think that the web site is being offered on port 5500?
I don't know about the Express version, but full-blown Enterprise Manager is provided by a running, Middleware process.  Is there a Window Service that you need to start (or, for whatever reason, has failed to start?)

... in the SQLPLUS command prompt I am able to login successfully ...

That's because the Database and the Web Site that you use to manage the Database are two completely separate things.


How can I load 'utf8' into plperl by default?

The solution I found involes mimicing the utf8 module's AUTOLOAD function in the plperl.on_init setting:

plperl.on_init = 'use utf8; use re; package utf8; require "utf8_heavy.pl";'

This successfully loads and initializes the utf8_heavy.pl file before dropping into Postgres's safe mode for running perl scripts.


From freelancer to team of two : best way to share/sync db?

Do not consider your database a development asset. Instead consider the code an asset. Make all changes to the database through code, have the code checked in into source control. Conflicting changes resolve as source merges. Rails migrations capture this philosophy pretty well. But it can be applied to any platform and any language, eg. Version control and your database.

This approach will solve not only your team work issues, but also deployment, QA, upgrade of live site(s), tracking history of database changes, explain the current database schema and so many more.


Export entire SQL Server Database into platform independent data files

This Powershell script should be able to solve most of your issue. I use a system table select to get my list of tables, then BCP to export all the data. The challenge is exporting your BLOBs. This script will export those as character data, which may not be useful to you in your situation.

Import-Module sqlps -DisableNameChecking
$outputdir="C:\TEMP"
$database="AdventureWorks2012"

$tables = Invoke-SqlCmd -ServerInstance localhost -Database $database -Query "SELECT s.name+'.'+t.name TableName FROM sys.schemas s JOIN sys.tables t ON s.schema_id = t.schema_id WHERE t.type = 'U'"

foreach($table in $tables.TableName){
    $outfile = Join-Path -Path $outputdir -ChildPath "$table-$(Get-Date -Format 'yyyyMMddhhmm').csv"
    $cmd = "& bcp $database.$table out '$outfile' -T -k -c -t','"
    Invoke-Expression $cmd | Out-Null
}

For an Oracle export, you'll want to use the import/export tool, but the process is more or less the same.


Storing unicode text into varchar column in SQL Server

The '?' character replaced the original value because there was no equivalent character in the collation code page and is physically stored in the column instead of the original value.

Sorry to say but the original value is lost.


How to restart MariaDB Galera cluster to change my.cnf

rsync is usually not a good SST method as it can block writes on the donor node while performing SST. Use mariabackup instead to avoid that.
Use wsrep_provider_options="gcache.recover=yes; gcache.size = xM;" where x is enough megabytes to hold all write sets for any expected downtime, if you want to be able to use IST and avoid SST after restarts. E.g. the amount of downtime expected for upgrading MariaDB on a single node. 128 is what I use, but it obviously depends on how much writing is usually happening on your cluster. gcache.recover=yes; is needed to try and use gcache-based IST when a node starts up instead of SST. See the MariaDB KB article on wsrep_provider_options for details.


PostgreSQL 9.3 - standard_confirming_strings option in postgresql.conf not affecting remote machine

I am updating a program at work that communicates with a postgreSQL 9.3 database. I need to turn off an option in the postgresql.conf file called standard_conforming_strings, so that I can store backslashes properly in the database.

These two things are completely unrelated. You may want to change the setting, but you don't need to in order to "store backslashes properly". I very strongly recommend that you do not change standard_conforming_strings - it's a backward compatibility setting that will get removed from PostgreSQL at some point in the future. Not only that, it's better to follow the standard where possible.

With standard_conforming_strings on, you can store backslashes just like anything else:

CREATE TABLE x(blah text);

SET standard_conforming_strings = on;

INSERT INTO x(blah) VALUES ('back\slash');

SELECT blah FROM x;

will produce back\slash.

If you turn standard_conforming_strings off you have to write 'back\slash' to get the same effect. The same is true for E'' strings: E'back\slash'.

  During installation, I turn off the standard_conforming_strings option in postgresql.conf automatically on the server machine. However, client installations of this program simply consist of shortcuts that point to the executable on the server machine. The client machines don't have postgres installed at all.

standard_conforming_strings is a server side setting. It can also be assigned by clients, but usually isn't, and the default is set server-side.

You don't need to mess with clients.

  If data containing a backslash is stored on the server machine, the data saves correctly, meaning the standard_conforming_strings option was successfully turned off.

Again, your first statement doesn't lead to the second at all. Your assumption is invalid.

  However, when information is saved from a client machine, backslashes are stored incorrectly (as '\134' instead of '\').

According to what client? Show the actual SQL, provide info on what the client app is, show the DDL for the table, etc.

If you're in doubt about the value of standard_conforming_strings for a session, use SHOW standard_conforming_strings to view the setting.


Need scalable database solution for very big automated data entry database

"Is MySQL suitable for distributing or cluster databases ?"

While you certainly can do manually sharding, the truth is that the most common MySQL cluster technologies are focused in high availability. NDB Cluster is based mainly on a memory database with lots of throughput, but not necessarily low-latency and large datasets. Galera does not support autosharding (only does read scaling).

  "Is there any easy to use database which support clustering and distributing systems?" (I know MySQL have a cluster version but i don't know if it is appropriate in my project).

Other people may give you better advice about other databases, but beware:

Many vendors that criticize the relational and transactional database model sometimes forget to tell you ideas like "eventually consistent" or the possibility of data loss in exchange for better performance (I look at you, NoSQL).
Can you use a non-distributed database? There are datawarehousing/highly compressed solutions for MySQL (InfiniDB, TokuDB). Even InnoDB has a memcached interface (allowing you even 1M Querys Per Second), which may help in reducing latency for the SQL (although not memory requirements and disk space).

If you tell us more about how you are going to use your logs (statistics, historic data?), we may be able to tell you if a relational database is better for you, or you have to go for other paradigm like column-based databases, or map-reduce, etc. Even then, you should try any product for yourself.


Database design for one key with multiple values

Instead of the dynamic number of columns (1 for each new language), I expect it would easier to manage this in the form of two related tables: 1. with your key and potentially one or more properties (to which new rows can be added for each new word) and 2. a table with translations for each key in table 1.

I have recently reviewed some strategies on this and found the below linked answer to be most useful, which expands on what I note above.

The answer provided a sample table:

    CREATE TABLE T_PRODUCT (pr_id int, PRICE NUMBER(18, 2))
    CREATE TABLE T_PRODUCT_tr (pr_id INT FK, languagecode varchar, pr_name text, pr_descr text)

https://stackoverflow.com/a/323365/10340388


The answer to could be really broad, but I'll try and keep it brief.

1.

MySQL as a long term technology is a really open-ended question - what do you mean by 'long' term? It's unlikely to go anywhere in the next few years, or even decade, but the market is constantly changing. Feeling too secure in anything probably isn't a good idea.

MySQL is still in the top rankings for RDMSs www.serverwatch.com , http://db-engines.com/en/ranking

So doesn't seem to be going anywhere. The fact it's free, and there's no looking like it's going to stop being supported, means that it's probably fairly stable in its position.

The questions you need to ask yourself are:
Do you have a job, and do you like it?
Do you want to do something else?
Can you learn new skills where you are, and what are the requirements there, what can you get training for?

The tricky thing with MySQL is how it's used

Lots of companies seem to expect MySQL DBA stuff as just something a really good PHP dev does. In my shop (where I'm a SQL Server DBA) the web team tends to look after the MySQL DBs, it's technically not in my remit (and I only muddle through with them if they need a hand).

Also, if a company is choosing to go open source, that's usually partly because they don't have a lot of money - making it less likely they'll have funding for a full time MySQL DBA (or at least won't be paying much).

This article talks about this more (it's a bit out of date, but what it's talking about doesn't seem to have changed).

Apart from MySQL, what DB should I learn?

Again, this is a bit of an open question: I could be flippant and ask back - what do you want to learn? It all depends on your reasons "Easily getting a job outside" is a bigger question (maybe take up being a woodsman, if working outside is your bag..!).

According to rankings Oracle and SQL Server are still right at the top with MySQL, so if it's just number of potential clients is your question - then they're the answer. Problem is, that's not so straightforward. Both of those markets already have lots of established DBAs - that's not to put you off, it's perfectly possible to start in the career, but it takes work. You need proven experience of systems in order to get jobs looking after them.

If you want to stick in the open source sphere, then investigate other open source systems people are using. Are you familiar with MariaDB? Since the split from MySQL it has been becoming increasingly more popular, and there are increasingly more reasons people are switching. Being already familiar with MySQL, this should be easier for you.

My personal opinion

Familiarising yourself with other RDMSs is always a good idea, but focus on something. Its rare a shop only has one RDMS and no others, even if they are legacy, or have other DBAs, it's always good to be able to help out and make yourself more invaluable - you can also learn more on the job.

I also think the question is not what RDMSs you should learn, but what other skills do you have, learning associated skills helps even more getting a doing a DBA job.

If you're sticking with MySQL or MariaDB, what's your PHP like?

Having a scripting language is always useful -
If you want to go into SQL Server (or use anything on Windows), consider learning PowerShell.

Choose a programming language to learn - what will depend on what area you want to go into (as a SQL Server DBA, I'm always trying to improve my .net and C#).

Having good project management skills/qualifications is another string to your bow.

Really, there's lots of things you can do, but there's no quick answers. You've got to research and find out what suits you.

Sorry... didn't keep it brief.


INFORMATION_SCHEMA.VIEW_TABLE_USAGE only shows tables from the same catalog

Yes, stop using INFORMATION_SCHEMA for various reasons, including one I didn't mention in that post (yet), which is the fact that they don't handle cross-database dependencies.

Use the catalog views and dynamic management functions instead.

To get just the views, you can use sys.sql_expression_dependencies:

SELECT
  [schema] = s.name,
  [view]   = v.name,
  d.referenced_database_name,
  d.referenced_schema_name,
  d.referenced_entity_name
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
INNER JOIN sys.sql_expression_dependencies AS d
ON v.[object_id] = d.referencing_id
WHERE d.referenced_database_name IS NOT NULL;

To get the column names too, you can use sys.dm_sql_referenced_entities:

SELECT
  [schema] = s.name,
  [view]   = v.name,
  r.referenced_database_name,
  r.referenced_schema_name,
  r.referenced_entity_name,
  [column] = r.referenced_minor_name
FROM sys.views AS v
INNER JOIN sys.schemas AS s
ON v.[schema_id] = s.[schema_id]
CROSS APPLY sys.dm_sql_referenced_entities
(
   QUOTENAME(s.name) + N'.' + QUOTENAME(v.name), N'OBJECT'
) AS r
WHERE r.referenced_database_name IS NOT NULL
AND r.referenced_minor_name IS NOT NULL;


The concept you consider is known as materialized view. Since you comment:

  rounds are rarely edited afterwards

.. I think this should be good idea for your case. Some RDBMS' have distinct objects for that. In Postgres it's just a matter of writing the results from a view or SRF (set returning function) to a table. You can find a basic code example for refreshing MVs in the manual here.

Refer to this related answer on SO for the more sophisticated regime I use for that purpose.

Your query to (re-)create a materialized view could look something like this:

SELECT c.name AS course
      ,r.user_id
      ,s.round_id
      ,COUNT(NULLIF(s.strokes = h.par, FALSE))     AS pars
      ,COUNT(NULLIF(s.strokes = h.par - 1, FALSE)) AS birdies
      -- more columns ..
FROM   score  s
JOIN   round  r ON r.id = s.round_id
JOIN   course c ON c.id = r.course_id
JOIN   hole   h ON h.id = s.hole_id
GROUP  BY 1,2,3

I simplified your test case and added the query: ->sqlfiddle.


It's because you're calling the stored procedure with empty string values rather than NULL values. The IFs and ELSEIFs in the SP body are testing for NULL, not empty string.


The default options that ssdt chooses often aren't best practices, instead you should choose what you want. For page_verify I agree checksum but it is up to you:

http://www.sqlskills.com/blogs/glenn/setting-your-page-verify-database-option-to-checksum/

Incidentally if you import from a production (or similar environment) and you choose "Import database settings" the actual settings will be imported from the database - if it is a new database I would get your dba's to create an empty one on a dev instance with their standard build etc.

Ed


mysql row returned n times based on column value n

For version 8+

WITH
cte AS ( SELECT 1 num
         UNION ALL
         SELECT num+1 FROM cte WHERE num < ( SELECT MAX(entries) FROM table)
       )
SELECT table.*
FROM table, cte
WHERE table.entries <= cte.num

For version 5.7 we must generate numbers table in the query

SELECT table.*
FROM table,
     ( SELECT 100*t1.num+10*t2.num+t3.num+1 num
       FROM (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1,
            (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2,
            (SELECT 0 num UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3
     ) cte
WHERE table.entries >= cte.num

The number of tX pseudo-tables is dependent by max possible entries value. In the code above this max value assumed to be 1000.

Or you can generate numbers table using user-defined variable based on some (the same or another) table (if such table exists) for which the records count is over max possible entries value.

Or you can create static numbers table in this or system database and use it.


How to concatenate arrays with user-defined types in pgsql?

array_append() works just fine if you use the correct syntax. Your fiddle doesn't work because you used “ instead of " and ‘ instead of '. The language for a function is an identifier and shouldn't be quoted to begin with.

String constants need to be enclosed in single quotes, not double quotes. The expression ROW("a","b")::udt would result in " column "a" does not exist" because "a" is an identifier. You need to use ROW('a','b')::udt to initialize a value of type udt

So the correct function would be:

CREATE OR REPLACE FUNCTION abc() RETURNS text AS $BODY$
DECLARE
  x udt;
  y udt[];
BEGIN
  x := ROW('a','b')::udt;
  return array_append(y,x);
END
$BODY$ LANGUAGE plpgsql
VOLATILE;

And select abc() returns:

abc
---------
{"(a,b)"}

Working fiddle


How to know if a table has a non-unique clustered index?

Create a simple table with a non-unique clustered index:

CREATE TABLE dbo.TestCluster
(
    TestClusterID INT NOT NULL
);

CREATE CLUSTERED INDEX CX_TestCluster --note there is no "UNIQUE" keyword here
ON dbo.TestCluster(TestClusterID);

This query shows if the given index is unique or not:

SELECT SchemaName = s.name
    , TableName = o.name
    , IndexName = i.name
    , IndexType = i.type_desc
    , IsUnique = i.is_unique
FROM sys.indexes i
    INNER JOIN sys.objects o ON i.object_id = o.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name = 'TestCluster'
    AND i.type_desc = 'CLUSTERED';

It's always vitally important that you specify/show the schema, as I have done above, since it very easy that objects with the same name might exist in multiple schemas.


Does inserting in order have the same effect as clustering?

Rows will be processed in the guaranteed order, but it does not mean that after being inserted they will be located next to each other. This will only be possible if records are never deleted or updated in your table. Once you update or delete some rows, after vacuuming you will likely have free space in the middle of the table, where the next inserted record(s) will go.

Some extra details in this question.


Diffrence in result from PostgreSQL (PostGIS) and SQL Server (Spatial)

A couple of things that have caught you out here.

In the SQL Server version using the Point, the order of the coordinate is Lat Lon, eg Y X.  The OGC Point construct is X Y or Lon Lat.

The next is that the PostGIS query you've posted actually returns 8.20039023523232e-005 which while appearing similar to the SQL Server result, is quite a lot smaller.

The last issue is that the PostGIS query is treating the objects as Geometries and doing a cartesian distance on them.  You need to explicitly make them Geographies.

Here's a few ways to do the PostGIS query.  I've swapped the coordinate order

SELECT ST_Distance(ST_GeomFromText('POINT(88.124582 27.185425)',4326)::geography,
                   ST_GeomFromText('POINT(88.124500 27.1854258)',4326)::geography);

SELECT ST_Distance(ST_GeogFromText('SRID=4326;POINT(88.124582 27.185425)'),
                   ST_GeogFromText('SRID=4326;POINT(88.124500 27.1854258)'));

-- Adapted from the PostGIS Manual
SELECT ST_Distance(gg1, gg2) As spheroid_dist, ST_Distance(gg1, gg2, false) As sphere_dist
FROM (SELECT
    ST_GeogFromText('SRID=4326;POINT(88.124582 27.185425)') As gg1,
    ST_GeogFromText('SRID=4326;POINT(88.124500 27.1854258)') As gg2
    ) As foo  ;


Specify cleanup by file age - PostgreSQL 9.2

First of all, if you need to retain the archived WAL files it's a good pratice to put in another directory. If you use the archive files to recovery a standby server, i may prefer make a separated copy for it.

You can adjust that in your master server with the parameter archive_command.

Now, if you want to keep just one directory for same purpose, remember that
pg_archivecleanup needs the archived wal location (seems ok with the variable $ARCHIVEDIR) and the last backup file (witch contains the first and last archived wal file for the backup, it contains a .backup extension, eg: 000000010000003700000010.00000020.backup) and that it's missing.

To solve your script, find the latest .backup file on the $ARCHIVEDIR directory and call pg_archivecleanup with the -n option to get a list of wal files to delete then use that list to find the files older than 30 days.

My idea it's something like this:

https://gist.github.com/sebastianwebber/855585ef3ef6a50c50cca7b57bc8d7d3#file-pg_cleanup_oldmorethan_30days-sh

Please, take a look at the documentation for more details:
https://www.postgresql.org/docs/current/static/pgarchivecleanup.html


list of batch aborting errors in SQL server

I believe there are a few exceptions, but from Database Engine Error Severities (MSDN):

Error messages with a severity level of 19 or higher stop the execution of the current batch.
Errors that terminate the database connection, usually with severity from 20 through 25, are not handled by the CATCH block because execution is aborted when the connection terminates.

So it seems like you could get a definitive list from the following query (of course this will not allow you to filter out which ones can be caused by user T-SQL):
SELECT message_id, severity, [text]
FROM sys.messages
WHERE language_id = 1033
AND severity >= 19
ORDER BY severity, message_id;

In SQL Server 2012, this produces 210 rows.
In SQL Server 2016, this produces 256 rows.
By the way, I don't believe the two scenarios you describe in your question work the way you think, at least not in modern versions of SQL Server. I tried this on both 2012 and 2016 (I believe Erland's article describes SQL Server 2000 behavior, which I don't remember if it was any different, but not very relevant today even if so).
USE tempdb;
GO

CREATE PROCEDURE dbo.pA -- no parameters
AS PRINT 1
GO
CREATE PROCEDURE dbo.pB -- two parameters
@x INT, @y INT
AS PRINT 1
GO

SET XACT_ABORT OFF;
GO

EXEC dbo.pA @foo = 1;
PRINT '### Calling procedure that doesn''t take parameters with a parameter';
GO

EXEC dbo.pB;
PRINT '### Calling procedure that takes 2 parameters with no parameters';
GO

EXEC dbo.pB @x = 1;
PRINT '### Calling procedure that takes 2 parameters with not enough parameters';
GO

EXEC dbo.pB @x = 1, @y = 2, @z = 3;
PRINT '### Calling procedure that takes 2 parameters with too many parameters';
GO

These all produce errors of severity level 16, and all of them proceed with the batch, as evidenced by the print output:

Msg 8146, Level 16, State 2, Procedure pA, Line 11
Procedure pA has no parameters and arguments were supplied.
### Calling procedure that doesn't take parameters with a parameter
Msg 201, Level 16, State 4, Procedure pB, Line 14
Procedure or function 'pB' expects parameter '@x', which was not supplied.
### Calling procedure that takes 2 parameters with no parameters
Msg 201, Level 16, State 4, Procedure pB, Line 18
Procedure or function 'pB' expects parameter '@y', which was not supplied.
### Calling procedure that takes 2 parameters with not enough parameters
Msg 8144, Level 16, State 2, Procedure pB, Line 22
Procedure or function pB has too many arguments specified.
### Calling procedure that takes 2 parameters with too many parameters

As I suspected, there are exceptions, of course, as noted in the comments. Conversion failure is severity 16 but aborts the batch:
SET XACT_ABORT OFF;
SELECT CONVERT (INT, 'foo');
PRINT 'Made it.'; -- no print happens

Results do not include the print output this time:

Msg 245, Level 16, State 1
Conversion failed when converting the varchar value 'foo' to data type int.


Rebuild or Reorganize indexes based on fragmentation

Is the table too small?

Fragmentation figures are meaningless for small tables that span up only a few extents

This is captured by @minPageCount in the script. The default is 8: meaning it will be skipped if one extent in size.


Exporting database Oracle 7.3 on Windows NT 3.51

Ok here's what I did:

I was in the ORANT\BIN folder.

I had to figure out what the SID was for the DB. Did that via an Oracle client which I tried to connect remotely to Oracle on the server.

set oracle_sid=a

exp73.exe System/manager file=myexp13xxxx.dmp full=y log=mylog130527.log


TSQuery To Search Number and UoM in Text

FTS is very clever in some ways, but not very clever in other ways.  It is not clever (by default) in the way you want it to be.

You can set up a thesaurus to map words to synonyms, but I don't know if there is any way to get your hands on the " symbol in order to map it. And of course it is unlikely to make sense to convert every ", or for that matter every 'in', into 'inches'.


Restore script while setting up database mirroring on secondary server

We have all the mount points and storage similar to primary.

If both primary and secondary have same file layout / structure, you will not even require to use RESTORE ... WITH MOVE. Just use restore database from disk .. with norecovery.

Alternatively, you can use

backup-dbadatabase with FileCount parameter to stripe the backups, CompressBackup. you can even use buffercount and maxtransfersize to make the backups faster. Then use
restore-dbadatabse with -UseDestinationDefaultDirectories parameter. This way it uses source folder structure when it does restore at destination. Just make sure it use NoRecovery parameter since you will set up dbs for mirroring.

  Is there a better way of doing this that i am unable to figure out or a way i can prepare restore script for above case?

Use dbatools as I mentioned above and no need to prep script. dbatools takes care of automation :-)


postgres ERROR: target lists can have at most 1664 entries SQL state: 54000

Such column count is really huge. Without the schema/query details I cannot say, what's the reason, but I might guess there are many data columns not really required to be serialized there. Instead doing some concat magic I would suggest to merge such columns into JSON/JSONB objects which would be then easily accessible by the application. This is the classic NoSQL approach in SQL RDBMSes.


What is best way to move large number of existing indexes into new file-group - DB Size: 700GB

The answer is: you cannot run heavy scripts and not slow down the DB server.

I quess all you have to do is to pick kind of light-workload-hours and REBUILD indexes one by one with your own hands monitoring blocking and overall server load.

General advice would be to use

ALTER INDEX <IndexName> REBUILD;

instead of recreating them.

Also consider ALTER INDEX options here such as:

ONLINE
RESUMABLE
MAX_DURATION
MAXDOP
WAIT_AT_LOW_PRIORITY

Have a look at these useful links:

one
two

Take care about free space when rebuilding online, as stated here.


Which TCP port to choose for SQL Server to listen to for security reasons?

This question may be better suited to https://security.stackexchange.com/ as the idea and implications are not at all specific to SQL Server, they apply to any other internal service.

Changing the SQL Server instance port generally adds very little to security - it is a "theatre" action rather than a real security benefit, something to put on a checklist to make it look like you are doing lots for a security-in-depth policy[1]. If the browser service is still open anyone getting in can ask that what port SQL is listening on. Multiple instances of SQL on different ports is not uncommon so automated attacks against SQL Server will generally scan for instances on other ports if one is not found on 1433, while an attack could take some tens of seconds longer an automated attack bot has all the time in the world to wait.

If the outside world can see port 1433 then that is the problem (your firewall/other setup is too open) and changing ports probably won't fix that because the new port will likely be open to the world too. If an attacker is inside you infrastructure so can see the SQL Server listening port for that reason then you have a significant security problem elsewhere that moving SQL's port also won't help with.

[1] NOTE: I don't mean to imply that security in depth is a bad thing, it very much isn't. Security in depth should be a core goal of all environments. But try to avoid security theatre which adds hassle but not actually any real security benefit when you could be spending your time doing something else that actually does improve security.


since you have multiple errors I recommend you to do the following:

if you have an old dump inside this directory move it to another place.
execute the following command select * from v$sgastat where pool='streams pool'; and increase STREAMS_POOL_SIZE value.
check open_cursor value and increase it (make sure scope=both when you edit it).

Note: I would recommend leaving step 3 to the end. Try to take a backup before executing it.


How to modify a Postgres tablespace location

The tablespace location is defined by a symbolic link in the pg_tblspc directory (this is why, by the way, you can only use custom tablespaces on systems that have symlinks, as stated in the documentation):

~/9.5/main/pg_tblspc$ ls -l
total 0
lrwxrwxrwx 1 postgres postgres 13 Mar  5 10:33 132568 -> /tablespace2/

As user postgres, you can change the location this symlink points to:

postgres@local:~/9.5/main/pg_tblspc$ ln -snf /tablespace/ 132568

This will fix your tablespace location.  As a precaution, I'd do this only when the database is not running.

However, you have to be sure why the location changed in the first place and that it won't change back or to anything else.


How does mysqldump take place when there are more than one database?

Let me address each of your three Questions
QUESTION #1

Are the tables of all databases in the server locked during the mysqldump?

ANSWER TO QUESTION #1
Depends on the default settings you allow and what setting you use to override. The --opt parameter is enabled by default. That enables --add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys.
If you have a database with all InnoDB tables or a mix of InnoDB tables and read-only MyISAM tables, you can just use --single-transaction. This will create a clean point-in-time snapshot of all tables involved in the mysqldump. Any INSERT/UPDATE/DELETE queries against an InnoDB table will not interfere with the point-in-time consistency of the mysqldump.
By the way, --single-transaction is mutually exclusive to --lock-all-tables. Thus, using --single-transaction will disable --lock-all-tables.
QUESTION #2

If I have to do an incremental backup using mysqlbinlog from the last full backup, should I take the time when mysqldump started or when it ended?

ANSWER TO QUESTION #2
You could take the time if you do mysqlbinlog at different times of the day. You could save yourself some off that housekeeping information by simply running FLUSH LOGS; or FLUSH BINARY LOGS; the same time every night. For example, you could script a cronjob to run FLUSH LOGS;at 11:59 PM like this
#!/bin/sh
sleep 55
mysql -uroot -p... -e"FLUSH LOGS;"

That way you can logically group together all binary logs that have the same date when formulating any needed incremental backup.
You do not have record times or positions in this respect: If you include the option --master-data=2 in the mysqldump, the log file and position at recorded as a comment on line 22 of the mysqldump output.
QUESTION #3

What happens when one is using the database when mysqldump is running? How can I take the start time for mysqlbinlog? What happens when mysqldump is executed?

ANSWER TO QUESTION #3
Here is where you have to be very careful. As I mentioned earlier, if you have a database with all InnoDB tables or a mix of InnoDB tables and read-only MyISAM tables, you can use --single-transaction and every table involved in the mysqldump will exist in the same point-in-time.
What Can Go Wrong #1
If you are performing INSERT/UPDATE/DELETE queries against a MyISAM table during the mysqldump, then the MyISAM tables will not be point-in-time consistent.
What Can Go Wrong #2
If you are performing ALTER TABLE, DROP TABLE, RENAME TABLE, or TRUNCATE TABLE against any table (MyISAM or InnoDB), consistent mysqldumps are no longer isolated. The transaction you issued with --single-transaction is released (made null and void) and all tables afterward can no longer the point-in-time consistent.
If one of these occur, recording start times or position is rendered useless.


Student table:

id | name

In this table you will have the students.

Sickness table:

id | name

In this table will have the sicknesses. It will have a row for all the sicknesses (cancer, diabetes, and so on).

Relative table:

id | name

This table will have the relations: "mom", "dad", "brother" and so on.

The trick what you need is a bridge table, or associative entity.

These are used to map many-to-many relations. And.. much more.

Because in this case, you need more than a single many-to-many relation. In fact, you need a "many-to-many-to-many relation". Essentially, it is an associative entity between 3 entities.

It is uncommon, but possible and can be handled pretty well in the DB design.

So, you need to have also a fourth table, with the following columns:

student_id | relative_id | sickness_id

If the table has a row, for example, (3, 4, 5), it would mean that the relative with id 4 of student with id 3, had the sickness with id 5.

On this way, you don't need multiple tables for the different sicknesses, it would be a very bad design.

(student_id, relative_id, sickness_id) should be a primary key of this bridge table. The induvidual columns will be also foreign keys to the corresponding student, relative or sickness tables.


How to store a date without the year component

One idea:

Use "day of year", but offset

March 1st is 1
Feb 28th is 365
Feb 29th is 366


How to determine master in mysql master-slave

@RolandoMySQLDBA has answered the question accurately ... but he also pointed out that his solution was "quick-and-dirty."

And that is a very true statement. :)

The thing that concerns me here is not with that answer, but rather is that the original question appears to make an incorrect assumption:

  I can query the two servers and ask which one is a master, then perform all queries to that one.

The problem is that in MySQL replication, the master is never truly aware that it is the master.

The concept of "promotion to master" is not really a concept in MySQL asynchronous replication.  "Promoting" a MySQL server to the master role is something that happens "external to" MySQL servers, as opposed to something that happens "internal to" MySQL servers.

"Promotion to master" not done by any kind of server provisioning, because, technically speaking, every MySQL server that has binary logging enabled is a master, even if it never has a slave.  SHOW MASTER STATUS works exactly the same way and returns exactly the same result, slaves or not, and a master with 2 slaves is no more or less a master than a master with 1 slave or 0 slaves.  Similarly, a master whose slaves are all offline is still just as much a master, because when the slaves come back online, they'll pick up replicating where they left off.

In a sense, the only "awareness" on the part of either server is not whether it is a master, but rather, whether it is a slave (or "not").

That's what Rolando's solution asks: "are you a slave?"  If the answer is no, then the assumption is that this must be the master... which he also pointed out as a flawed assumption if STOP SLAVE; is issued.  But a stopped slave is still a slave, so "not a slave" (at any moment in time) doesn't equate to "being a master."

A similar test could be done on the presumed master:

SELECT COUNT(1) FROM information_schema.processlist
 WHERE user = 'the_username_used_by_the_slave';

or

SELECT COUNT(1) FROM information_schema.processlist
 WHERE command = 'binlog dump';

If the value is zero, then the slave's IO thread is not connected.  This test has a similar defect, in that if the slave is disconnected administratively, isolated, or failed, then it won't be connected.  So this doesn't really solve anything either.

Worse still (for either of these scenarios) the information_schema.processlist "table" is a virtual table that gets materialized every time it is selected from, and this takes time and costs resources.  The busier your server, the more it costs, because each thread's activity has to be peered-into.

A more lightweight solution would be:

SELECT @@global.read_only;

On a slave, you could/should set the global variable read_only so that users without the SUPER privilege cannot unintentionally write to it (and your application should not have SUPER).  If you manually "promote" the slave to the master role, you SET GLOBAL read_only = OFF to enable writes.  (Replication can always write to the slave, no matter how this is set).

But this still, I think, misses an important point:

I would propose that the application should not be making this decision heuristically in a master/slave setup, and certainly not on a connection-by-connection basis.  The application should use either a hard configuration option, or the application should remain unaware and have the database connection destination handled by something else.

Or, at minimum, the application should never switch over until the master fails, and then it should never switch back on its own.

Here's why I say that: once the "decision" is made -- by whoever or whatever -- to make another server the master, the application cannot be allowed for any reason to switch back to the original master, even after it comes back online, without intervention.

Let's say you hit a bug and there's a software-forced crash; mysqld_safe dutifully restarts mysqld, and InnoDB crash recovery performs flawlessly.  But that takes a few minutes.

Meanwhile, the master is down so your application has switched to the slave.  Transactions have been created, orders placed, funds transferred, comments posted, blogs edited, whatever your system does.

Now, the original master comes back online.

If your application switches back to the original master, you are in an absolute world of hurt, because the very next thing that is likely to happen is that replication stops due to an inconsistency, because your application has changed data on the slave in the mean time.  You now have two database servers with inconsistent data that you will have to reconcile manually.  If there are dollars or points or credits involved, you now have mismatched balances.

So it's critical that the application not be allowed to switch back to the original master without your intervention.

Wait, did you just find the problem with this scenario as I described it?  The master has failed but your application won't be using the slave, because it thinks the slave is still the slave and not the master... the information_schema.processlist query on the slave will still return non-zero even if the master server is powered down.

So there isn't much point in the application discovering anything, since you'll have to manually STOP SLAVE for that test to be useful.

Perhaps a better approach if you want the application to be able to switch would be to  configure the servers with circular replication.

Circular replication has inherent problems of its own, but as long as your application is only always ever writing to one server at a time, most of those issues become non-issues.  In other words, both machines are always and simultaneously both master and slave, in a replication sense, but your application, via some mechanism, is always only pointing to one machine at a time as the "master" to which it can and should write.

You can't deploy HA tools on the MySQL servers due to their separation, but you could implement it with HAProxy running on the application server(s).  The application connects to "MySQL" on localhost, which isn't MySQL at all, but is actually HAProxy ... and it forwards the TCP connection to the appropriate MySQL machine.

HAProxy can test the connections to the MySQL servers and only offer traffic to a MySQL machine that is accepting connections and allowing authentication.

The combination of HAProxy running on the application server (its demand for resources won't be substantial compared to everything else the application server has to do -- it's pretty much just tying sockets together and ignoring their payload) ... and MySQL circular replication would be the approach I would probably take in this instance, based on what's known from the question.

Or, for a strictly manual setup, go with something much simpler than "discovery," like an entry in the app server's /etc/hosts file with a hostname that the application uses to connect to MySQL, which you could manually update -- assuming promotion of slave to master is intended to be a manual process.

Or, something more complex, using Percona XtraDB Cluster.  For this, though, you'd want to add a third server, because with 3 nodes in PXC, if 2 servers can see each other but get isolated from 1 server (if all three are still running) the 2 servers keep running happily but the 1 server curls up into a little ball and refuses to do anything since it realizes it must be the odd one out.  This works because the 2 realize they still constitute a majority of the nodes that were online before the network split and the 1 realizes that it isn't.  With PXC, it doesn't really matter which server your application connects to.

I say all of this is to say "don't have the application poll the servers to see which one is the master" because it will bite you sooner or later and it will nibble away at your performance up until the day it bites.


Adding the values to a new my.cnf file inside, /etc/mysql/conf.d solved the issue. Added only the value we wanted to override. For some reason, values added elsewhere were ignored by mysql.

Note:
Make sure /etc/mysql/conf.d is included in my.cnf file located at /etc/mysql/my.cnf.
It included by default.


is there a recommended way to update many postgres schemes

Why don't you set the variable search_path to be each schema in turn?

See http://www.postgresql.org/docs/current/static/runtime-config-client.html


SQL Server service broker related rights issue

ALTER DATABASE  SET trustworthy ON

This query solved my prob. Thanks for efforts.


DBCC Shrinkfile after full backup restoration but before differential restoration

Please advise if i can run the command DBCC SHRINKFILE with
  TRUNCATEONLY between full backup restored and before differential
  backup restoration.

No, you cannot.

You'll get the error that your database is in the middle of restore, and this is logic, you cannot do anything with database that is restoring.

You should analyse your db free space using sp_spaceused to understand if you can and should do anything with this database BEFORE you take the last backup, may be your db is in the full recovery model and log backups are not made regularly?


Does the SQL interpreter make a conversion between "empty set" to 0 or 1 ?

No, The query didn't return 0 because the empty result is considered equal to 0 but because the empty result set is considered NULL and null isn't equal to anything.

As you found out

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 1 THEN 1 ELSE 0 END

Returns 0

But

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) = 0 THEN 1 ELSE 0 END

Also returns 0.

So the empty result set is not 1 and not 0, it's NULL as evidenced by

SELECT
CASE WHEN (SELECT 1 WHERE (1=0)) is NULL THEN 1 ELSE 0 END

which returns 1.

See this dbfiddle with that code.

NULL is unknown so SQL Server doesn't know what it's equal to.

  In my humble opinion, it's better to use EXISTS

If you rely on the existence of a result set, I agree EXISTSis probably the way to go. That's what the construction is made for.
If you want to compare 2 values off course you'll need to use CASE but for your stated scenario I'd use EXISTS

Your comparison will break if your result set has more than one row or column.

For example

SELECT
CASE WHEN (SELECT 1,2 WHERE (1=1)) = 1 THEN 1 ELSE 0 END

returns

  Msg 116 Level 16 State 1 Line 2   Only one expression can be specified
  in the select list when the subquery is not introduced with EXISTS.

and

SELECT
CASE WHEN (SELECT 1 WHERE (1=1) UNION SELECT 2 WHERE (1=1)) = 1 THEN 1 ELSE 0 END

returns

  Msg 512 Level 16 State 1 Line 1 Subquery returned more than 1 value.
  This is not permitted when the subquery follows =, !=, <, <= , >, >=
  or when the subquery is used as an expression.


How to convert the JSON date with to_date function


Can sql server 2000 and 2012 coexist on the same server?

Yes, I have systems with 2000 and 2012 installed. I have only tried with installing 2000 first though; I haven't been brave enough to try installing 2000 after 2012 was in place.


IF the database is running in FULL recovery mode and you can somehow manage to restore the transaction log file from the time the DELETE statement ran, then there is a chance that you could recover the actual statements that were executed by reading the actual transaction log.

The way to do this requires a 3rd party tool though. It's called ApexSQL Log and it has a free trial download which you could use to test it out.

To understand how to use this tool properly, the creators of it have published an evaluation guide that walks through common scenarios of reading transaction logs.

ApexSQL Log evaluation guide for first time users – High level walkthrough with links to core topics


Is there a recommended way to update a database schema when updating and app's RPMs?

Unless the tools you're using have built-in support for database schema migrations (like ActiveRecord/Rails does, albeit rather unsafely) - you need to do it yourself in your application.

The usual technique is to have a table in your DB that contains metadata about the schema and application. You might structure it in key/value form, or as a single row with multiple cols; it doesn't matter. Either way, this table should store a schema version.

When you connect on app startup, before doing anything else with the DB, you should query the metadata table for the schema version. If it's less than the current version in the application, your application should apply schema change scripts from the old version to the new version. I used @Singleton @Startup EJB3s for that, and read the schema change scripts using the classloader from property files embedded in the application jars; you're probably using Spring or some similar framework with similar capabilities.

Because you have two application servers, you'll probably have to LOCK TABLE your_database_metadata_table before beginning to apply changes to ensure that both servers don't attempt to apply the same database migration scripts.

DDL changes are easier in PostgreSQL than some other DBs because of its support for transactional DDL. You can BEGIN a transaction, apply all the database changes in one go - DDL, metadata table updates, etc etc - and COMMIT. You won't land up with a half-applied schema change. This is only viable when the schema change won't take too long or you can afford a delay. If you're ALTERing the data type of a 100-million row table, you don't want to do that in a single big transaction unless everything else can wait.

Replication is taken care of automatically if you're using the built-in streaming replication; it doesn't differentiate between DDL and DML, they're are all just database changes as far as the replication system is concerned.

If you're using another replication engine like Slony-I or Bucardo, then it depends on how the replication engine deals with schema changes. This is engine specific, so you need to look into the docs for your replication engine. (The current mess with 3rd party replication and DDL changes should improve dramatically some time after the 9.3 event triggers, logical replication and command deparsing support is added to PostgreSQL core. Some of that looks like it'll slip to 9.4.)

Now, if you want to do your DDL changes non-disruptively without interruptions to the applications, that's a whole other level of fun.


ADD - no outage required.  Although as Sean from Microsoft pointed out, SQL will prefer to use the lower filled files.  If you are going from 1 data file and adding more, then SQL will use the new ones for a while, but your performance won't be worse than only having one file.  However, if you have 2+ already and add one more, then it will hotspot on the new one and decrease performance.

SHRINK - may require an outage, depending on load.  The tail of the file needs to be empty which may not happen in a busy server.

GROW - no outage required although you may prefer to take one to avoid the performance penalty.  Instant File Initialization will mitigate this.  As Aaron pointed out, it will only mitigate for data files, but since you aren't going to add more than one log file (not useful)

REMOVE - may require an outage (has to be shrunk/emptied first)

MOVE - requires an outage since you need to restart SQL to make it start using the files in the new location.

EDIT - Added details about HotSpotting.

When SQL Server has more than one TempDB data file, it looks at them and selects the one with the lowest amount of data to use (sort of load balancing).  So, in the scenario where you have two or more existing data files and add a new one, SQL will observe that the one you just added is lowest filled and will utilize that one almost exclusively until it reaches the same level as the others.  This will negate the advantages of having multiple tempdb files as all of the activity will concentrate on just the one.  Basically, you will see tempdb contention (Pagelatch on tempdb) for the file you just added until it reaches equilibrium.

If you have 1 file and add 1 or more to it, then this will still occur (SQL will prefer to use the one that is lowest filled) but as you were going from one file with the contention, you won't see any downtick in performance, but you won't see any uptick in performance until the files all reach equilibrium.


Common practices to determine what database technology to use

Adding a no-answer as I don't know of a formal method for choosing a database architecture (remember you buy a architecture with special backup requirement, HA feature, hardware requirements).
Basically it boils down to

You need the optimal database for the enterprise not only your project (If a  "optimal" database for your project would be DB2 on AIX is of no use to your project if all your operating team does only Windows and SQL Server)
Do you need HA? which one? Switchover? active-active?
What type of disaster recovery do you need?
Formal (e.g. customer requirements, supported OS) and legal requirements
What type of infrastructure is on the customer site (SAN? virtualization? cloud?)
Monitoring? Backup? (If your backup software does not support a RAC database or the modules for backing it up cost more than the rest of your project, it's pointless to use that database. Same is valid for monitoring
Skill of your operating teams
type of data (Geodata?) Is it business critical? (your HR has slightly different impact than a simple database the tags on your website
amount of data

There is a whole lot more to consider but in my experience these are good starting points.


When range partitioning, should I drop primary UUID-like key?

Assuming

Some queries involve a date range, usually "recent" dates,
Other queries are point-queries, which involves a unique key
You can purge data older than 18 months.

Anything else?

UUIDs are terrible for performance when the dataset size becomes too big to cache.

It would take some work, but this is what I recommend:

id INT UNSIGNED NOT NULL AUTO_INCREMENT -- or `BIGINT` if might go past 4 billion
dt DATETIME NOT NULL  -- or TIMESTAMP if you prefer
PRIMARY KEY(dt, id)
INDEX(id)

Characteristics:

The data is in datetime order, thereby making those queries very efficient,
Tacking id onto the PK assures that the it is unique,
id replaces your uuid_short in the application (so, code change),
There is a PK, which InnoDB really likes to have,
You won't need to keep adding RAM -- most of the activity is near the "end" of the table,
INSERTs are very efficient -- both the data (& PK) and the secondary key (id) are being added to at the end.

Now, I will add on the purging.

PARTITION BY RANGE(TO_DAYS(dt)) is the optimal way to do it.  Define the ranges as months.  Each month: DROP (instantly) the oldest month and ADD a new month.  See my blog for more details.

How to transition from uuid_short to id?

Add the AUTO_INCREMENT id and INDEX(id) to the existing table in a single ALTER,
Convert the app's use of uuid_short to id,
Drop the uuid_short column and change the indexes (all in a single ALTER),
Add PARTITIONing.

Steps 1,3,4 will each require copying over the entire table; the sooner you do it, the less downtime you will suffer.  Or consider pt-online-schema-change or gh-ost for doing them "online".

Another tip:  See if you can shrink any of the datatypes of other columns.


DEA saves the trace files on the target server, not on your client machine (assuming you run DEA on your machine). So the path (folder) has to exist on the target server, and the SQL Server account needs write access to that.


SQL Server 2005 Whole-Migration, which databases should I recreate?

If you really care to know which databases are in use, you have to use dmv's (log that data to a table as they get flushed out when server is restarted) or profiler (server side trace) and just filter out the logins and program names connecting to those databases.

once you have that info for your complete business cycle, you will be able to figure out what databases needs to be moved to new server.

also, backup/restore should be a preferred method unless you have a reason of not doing it.

note: we do not know your environment, nor your business so we CANNOT answer what databases you need to move.

reportserver* are eelayed to reporting server that might be installed on the current server.


COUNT() returns and integer. So both the numerator and denominator of the percentage calculation, as written in the question, are integers. As it says in the documentation

  If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.

The solution is to cast one or other (or both) to a non-integer type. The rules for precedence are a bit complicated so you may need to fiddle around to get just what you want. Or you can write a second CAST to deliver the percentage as desired.


Given that you haven't started coding the app or DB yet, but do have a sense of the scope of the data (even if that will change somewhat as time goes on): no, you are not over-thinking this. I wouldn't spend months pondering this one aspect of the system design, but to not think of it at all is a bit foolish since refactoring app code is far easier than refactoring databases (since databases include "state").

My preference for multi-tenant systems, especially if dealing with large amounts of data and/or great variation of data sizes between clients, is to go the Database-per-Customer route. There are obviously advantages and disadvantages to any approach, but here is what I see from having gone through this a few times and trying it both ways:

ADVANTAGES

One customer does not adversely affect another performance wise. This would mainly be an issue when having customers with greatly varied amounts of data since parameter sniffing will cache the plan for the first CustomerID passed into a Stored Procedure, and that CustomerID might have 10 million rows whereas the next call to the Stored Procedure is for a customer with 1,000 rows.
You don't need to have a CustomerID field in every table (except lookup tables). This complicates indexing and performance tuning.
You don't need to worry as much about cross-customer data privacy issues when forgetting to include the CustomerID field in all JOIN conditions and WHERE conditions (either Cartesian products or simply Customer A seeing Customer B's data).
When a customer leaves, you just shut off their DB and archive it. No need to worry about purging obsolete data.
If a customer needs their data, you can simply send them their DB without worrying about cross-customer data issues.
You can host customer DBs geographically closer to each particular customer, especially when using a cloud-based system like Azure SQL Database, AWS, etc.

DISADVANTAGES

App code connectivity needs to take into account the Database, not just Server
More potential for Connection Pool Fragmentation if you include the Database in the ConnectionString rather than changing the DB context after making the Connection
DB code releases get a little more complicated since they need to be applied across all DBs (though there are ways to minimize this complication)
Increased potential to have differing code/schema between customers if you don't have a solid release process (item directly above), and this would be a maintenance nightmare
Support gets minorly complicated since you now need to know which DB to go to instead of needing just a CustomerID
If the customer model changes such that in the future a "parent" company  of Customer A and Customer B can be the true customer, then those are in two separate DBs and harder to manage and report on as a single entity.
Backups get a little more complicated now that you have 200+ databases (though there are ways to minimize this complication)

Regarding the thought of having a single SystemData DB and the related concern of not being able to FK across DBs: how much system lookup data do you expect to have? Assuming it is not 1 GB of data, you can create a Schema in each customer DB called "System" or "Common" or "Lookups" or whatever, and load the data into that schema into all customer DBs. Then you can FK to those tables. And by having one DB per customer you will already need a release process that updates the code and schema of each customer DB in the exact same manner anyway (such that they are always in sync with a common structure), so updating lookup tables fits right into that process. The main downside here is that you are duplicating this data per each customer DB, so 200 times perhaps? But that is over the course of years. You aren't starting out with 200 customers, right?

Also, please keep in mind that Table Partitioning is not "sharding" (like you might be thinking of in terms of MongoDB or PostgreSQL/GreenPlum, etc). It is not meant to magically solve the problem of multi-tenancy or distributed data. It is meant to handle the problem of quickly and efficiently handling bulk data loads and unloads. And it does quite well at its intended purpose. And occasionally it even helps queries on very large tables (1 billion rows or more). But indexes and statistics are still per-table, not per-partition (though you can create filtered indexes/statistics per each partition key to help make up for this). And while you can (in more recent versions) reindex per-partition, starting with SQL Server 2012, indexes no longer get a "free" UPDATE STATISTICS WITH FULL SCAN when doing a REBUILD, if they are partitioned ! Non-partitioned indexes still do :-).

For what it's worth, I worked on a multi-tenant system that did attempt to use table partitioning in this manner (was not my idea). It caused a lot of headaches, frustration, extra work trying to fix weird execution plans, and after a few years, was mostly agreed upon that it probably shouldn't have been done.


InnoDB files created even innodb_file_per_table is OFF in mysql-5.7

Not matter what you configure innodb_file_per_table to be (0 or 1), the following still occurs

MySQL creates a database folder
MySQL creates every table's .frm in a database folder

EXAMPLE
Your database directory (datadir) is /var/lib/mysql
You run the following commands
CREATE DATABASE IF NOT EXISTS mydb;
USE mydb
CREATE TABLE tb1 (a INT NOT NULL) ENGINE=InnoDB;
CREATE TABLE tb2 LIKE tb1;

When innodb_file_per_table is 1

You get /var/lib/mysql/mydb
You get /var/lib/mysql/mydb/tb1.frm
You get /var/lib/mysql/mydb/tb1.ibd
You get /var/lib/mysql/mydb/tb2.frm
You get /var/lib/mysql/mydb/tb2.ibd

When innodb_file_per_table is 0

You get /var/lib/mysql/mydb
You get /var/lib/mysql/mydb/tb1.frm
You get /var/lib/mysql/mydb/tb2.frm

The major difference is the fact that data and index blocks for newly created tables end up inside the system tablespace file known as ibdata1 when it is 0.
Just as a pictorial reference, please note Percona's view of InnoDB

YOUR QUESTION
Changing innodb_file_per_table to 0 does not make currently created tables just migrate into ibdata1. You have to manually move those tables into ibdata1.
This will not alleviate the too many open files issues. Why ?
Regardless of storage engine, mysqld always checks for the existence of tables by first checking for the tables .frm file. Then, the mysqld process will reference the table's intergrity based on the storage engine.
In the case of InnoDB, the data dictionary of the InnoDB table is then checked.
Two file handles are opened

One file handle for the .frm
One file handle for the .ibd when accessing the table

If you move the table and index data from the .ibd into the ibdata1 file, you get this

One file handle for the .frm
One file handle for the ibdata1 when accessing the table

All the file handles will pile up inside ibdata1 instead of separate files.
STILL WANT TO MOVE TABLE INTO ibdata1 ?
While I highly discourage this, it is possible
In the example I gave you, in order to move a table into ibdata1 after setting innodb_file_per_table to 0, just run this
USE mydb
ALTER TABLE tb1 ENGINE=InnoDB;
ALTER TABLE tb2 ENGINE=InnoDB;

This recreates the table, and migrates the data and indexes into ibdata1. If you are willing to do this 240,000 times (6000 X 40), be my guest.
WHAT CAN YOU DO FROM HERE ?
You could setup MySQL Replication and perform backups on a Slave
You do the following

STOP SLAVE;
Perform mysqldump on the Slave without --single-transaction
START SLAVE;

or

STOP SLAVE;
SET GLOBAL innodb_fast_shutdown = 0;
Shutdown mysqld
Make a physical copy of the datadir
StartUp mysqld
START SLAVE;

GIVE IT A TRY !!!


SQL Server data migrations go through client machine or not?

If you run the copy database wizard then yes all the data will go through which ever machine you run the copy database wizard on.

If you are moving a large database, especially one with a complex schema then backup and restore is a much better option.


SQL Server AlwaysOn from multiple origins to a single secondary replica server

It is possible, provided that:

all servers run the same post 2012 version, preferably 2014 and all patched to the same SP/CU.
all servers are licensed to Enterprise Edition
all servers are in the same domain, or a within domains that trust each other

Perhaps you are interested in Transactional Replication instead? This would support a heterogeneous configuration (2008, 2012, 2014), crossing domain boundaries via mirrored NT accounts, does not require Enterprise licensing everywhere. You need to explicitly set up the 'published articles' (ie. what tables get replicated).


In Postgres this type of query is much more efficient when done using distinct on ()

    select distinct on (c1)
         t1.c2, t1.c3
    from MyTable t1
    where MyTable.c4='string'
    order by c1, c3 desc;

Alternatively using standard SQL and a window function:

select *
from (
  select t1.c2,
         t1.c3,
         row_number() over (partition by c1 order by c3 desc) as rn
  from MyTable t1
  where t1.c2='string'
  and t1.c4 is null
) t
where rn = 1;

If you can't change the query because this is generated by an obfuscation layer (aka "ORM"), then adding the column c3 to the index idx_multi_c2_c4 and creating an index on (c1,c3) might help.


How can I repair a large myisam database when diskspace is low?

You don't have many options here.

Since you're running MyISAM storage engine, you may try to snapshot your files, e.g. with mysqlhotcopy, repair the snapshot on another host with much more space, then copy the repaired data files back to the original host.


Select max(pk) instead of sequence.nextval

The presentation of the data should not be the determining factor in how the data is stored - especially in the case of a primary key.  That would be like saying that all dates need to be stored in the database as strings so that a particular date format could be displayed.

To make the select max(pk) + 1 work in a system with more than one user is to programmatically force only one session to get the next PK value at a time - seriously compromising your concurrency. Either that or you will have to write code to intercept the PK failure and try again.

The data displayed can be formatted in the presentation layer in any way that makes sense.  You can do a to_char to add the leading zero's in the select if needed - another string in the record could be appended - whatever would make it look more like a "code".  In our environment we just use the generated sequence as the identifier - its more important that it is generated efficiently and is unique than how it looks.


select string_agg(c,'')
from (
  select distinct regexp_split_to_table(lower(name),'') as c
  from data
) t

The inner select generates one row for each character, and the outer then aggregates that to a long string.

If you want the characters sorted, you can use an order by for the aggregate string_agg(c,'' order by c)


SQL Server stored procedure causing 100% CPU usage

If a stored procedure is maxing out your CPU, you really need to tune that query to fix the root cause rather than working around it. Analyse the query plan using native SSMS or SQL Sentry Plan Explorer.

Some things to look out for to improve the query plan from http://www.brentozar.com/ are

http://www.brentozar.com/archive/2012/07/identifying-correcting-sql-server-implicit-conversion/
http://www.brentozar.com/archive/2013/06/the-elephant-and-the-mouse-or-parameter-sniffing-in-sql-server/
http://www.brentozar.com/archive/2010/06/sargable-why-string-is-slow/

To answer your question however, yes, you can limit CPU using Resource Governor.


You will incur some expenses if you want to run ETLs (packages) from Azure. You can create Integration runtime via Azure Data factory and deploy package to the SSIS catalogue for Azure sql database.

Createing Azure SSIS Integration Runtime


Query profiling shows "Waiting for query cache lock" but query_cache_size is 0

The message means that you are trying to acquire a Query Cache Mutex even though the query_cache_size is 0 (Note that akuzminsky mentioned that the MySQL for Oracle still allows this) You will need to set query_cache_type to 0 as well because as the MySQL Documentation says:

  If the server is started with query_cache_type set to 0, it does not acquire the query cache mutex at all, which means that the query cache cannot be enabled at runtime and there is reduced overhead in query execution.

In MySQL 5.6, query_cache_type is 0 by default. It's 1 for MySQL 5.0, 5.1, and 5.5

Regardless of the MySQL version, make sure my.cnf has these

[mysqld]
query_cache_size = 0
query_cache_type = 0

You do not need to restart mysql. Just login to mysql and run the following:

mysql> SET GLOBAL query_cache_size = 0;
mysql> SET GLOBAL query_cache_type = 0;

Give it a Try !!!


Relationship between max pool, num init children, and max connections

max_pool parameter configures how many connections to cache per child. So if num_init_children is configured to 100, and max_pool is configured to 3, then pgpool can potentially open 300 (=3*100) connections to the backend database.
A child process opens a new backend connection only if the requested [user,database] pair is not already in the cache. So if the application uses only one user to connect to only one database, say [pguser1,pgdb1], then each child will continue to reuse the first connection and will never open a second connection, so in effect pgpool will open no more than 100 backend connections even though max_pool is set to 3

Your config
num_init_children = 32
max_pool = 4

That means You pgpool instance is able to open up to 128 connections (4*32). But pgbench is using only one user and is executed with concurrency 16. According to second quoted paragraph above pgpool won't open a new backend connection, because there is already user,database combination in the connection cache.
Also number of tps in pgpool test is ~16 times lower (a little bit more than that) then the other two tests.
Unfortunately I'm not so familiar with pgpool and I don't know how to improve the performance.


Select most recent row by some other column

If there are more than one AnalogValue, you can get max value for each RoomId and then JOIN with CRV_AttributeLog just to fetch all attributes.

WITH maxTime as
(
    SELECT   RoomId, MAX(LogTimeStamp) AS LogTimeStamp
    FROM     @CRV_AttributeLog
    WHERE    AttributeID LIKE N'online_status'
    GROUP BY RoomId
)
SELECT  r.RoomName,
        a.AttributeId,
        a.AnalogValue,
        m.LogTimeStamp
  FROM  @CRV_AttributeLog a
  JOIN  maxTime m
    ON  a.RoomID = m.RoomID
   AND  a.LogTimeStamp = m.LogTimeStamp
   AND  a.AttributeId LIKE N'online_status'
  JOIN  @CRV_Rooms r
    ON  r.RoomId = a.RoomId;

+-----------------+---------------+-------------+---------------------+
|     RoomName    |  AttributeId  | AnalogValue | LogTimeStamp        |
+-----------------+---------------+-------------+---------------------+
| Great Hall      | online_status |      1      | 01.01.2017 20:21:00 |
| Boardroom       | online_status |      0      | 01.01.2017 21:40:00 |
| Conference room | online_status |      1      | 01.01.2017 20:20:00 |
| Gymnasium       | online_status |      2      | 01.01.2017 18:20:00 |
+-----------------+---------------+-------------+---------------------+

Check it here: http://rextester.com/HISDOM70855


Mysql - show results from 3 tables excluding NULL or EMPTY values for certain fields

Try this:

SELECT id,
  title,
  pollid,
  COUNT(pollid) AS clean_count,
  group_concat(DISTINCT u.name) AS user
FROM
(
  SELECT DISTINCT user_id, pollid
  FROM tbl_votes
) AS tmp_tbl
JOIN tbl_polls
  ON tbl_polls.id = tmp_tbl.pollid
JOIN tbl_users AS u
ON tmp_tbl.user_id = u.user_id
WHERE not isnull(tmp_tbl.user_id)
GROUP BY pollid
ORDER BY clean_count DESC

This is the sample data that I used:

    CREATE TABLE tbl_polls (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(50)
  );

CREATE TABLE tbl_users (
  user_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
  );

CREATE TABLE tbl_votes (
  id INT AUTO_INCREMENT PRIMARY KEY,
  pollid INT,
  user_id INT,
  INDEX (pollid,user_id),
  FOREIGN KEY (pollid) REFERENCES tbl_polls(id),
  FOREIGN KEY (user_id) REFERENCES tbl_users(user_id)
  );

INSERT INTO tbl_polls (title)
VALUES ('Title 1'),('Title 2'),('Title 3');

INSERT INTO tbl_users (name)
VALUES ('Bob'),('Jack'),('Joe'),('Tom');

INSERT INTO tbl_votes (pollid,user_id)
VALUES (1,2),(1,2),(1,2),(1,3),(2,4),(2,1),(2,4),(2,4),(3,1),(2,1),(1,4);

SQL FIDDLE DEMO

I have used group concat to get the names for your list. Furthermore, to speed up your database have you tried adding index to the foreign keys?

B.T.W. I don't understand your comment that some some votes lack pollid and user_id. If that is the case have a look at this.


Does PGStrom calculate relational comparisons?

no, pgstrom cannot do that. it only able to scan relations - it is not able to join.
what you an do is pack an operator into a GPU routine but = is not a good candidate for that.


How to get Mysql Select Query? single question multiple answers

As noted by Tom V, this can easily be done in the presentation layer, instead of in the query itself. Trying to do it in the query means that the query will take longer to run, and in most cases in a web application (which I assume is what you're working with here), you can have several webservers all interacting with one database server - offloading tasks from the DB server to the web servers will frequently make sense.

However, it's also true that you'd (probably) be sending more data from the database server to the web server, and there's a cost in that bandwidth as well. And, of course, smaller websites might have just one webserver, in which case it might actually make sense to use more resources on the DB server.

So, the following query should give you what you want. We use a variable in the query to check if the current row has the same sno as the previous row; if it doesn't, we don't show the columns from qa_request. The process creates an extra column (aliased to dummy); the main query is put into a subquery simply so we can leave that dummy column out.

CREATE TABLE qa_request (
  sno       int,
  userno    varchar(20),
  name      varchar(20),
  uquestion varchar(5),
  subject   varchar(20)
  );

INSERT INTO qa_request
VALUES (1,'xxxxx','yyy','q1','s1')
      ,(2,'zzzzz','qqq','q7','s8')
;

CREATE TABLE qa_answer (
  qa_id      int,
  answer     varchar(5),
  a_subject  varchar(20)
  );

INSERT INTO qa_answer
VALUES (1,'a1','adminsubject-1')
      ,(1,'a2','adminsubject-2')
      ,(1,'a3','adminsubject-3')
      ,(2,'a37','adminsubject_37')
;

SELECT sno
      ,userno
      ,name
      ,uquestion
      ,subject
      ,answer
      ,a_subject
  FROM (SELECT CASE WHEN @last_sno <> a.sno THEN CONCAT('',a.sno) ELSE '' END as sno
              ,CASE WHEN @last_sno <> a.sno THEN a.userno ELSE '' END as userno
              ,CASE WHEN @last_sno <> a.sno THEN a.name ELSE '' END as name
              ,CASE WHEN @last_sno <> a.sno THEN a.uquestion ELSE '' END as uquestion
              ,CASE WHEN @last_sno <> a.sno THEN a.subject ELSE '' END as subject
              ,b.answer
              ,b.a_subject
              ,@last_sno := sno as dummy
          from qa_request as a
                 inner join qa_answer as b ON a.sno=b.qa_id
                 CROSS JOIN (SELECT @last_sno := -1) init
         ORDER BY a.sno, b.answer
       ) sq
;

db-fiddle here


Do indexes on primary keys default to fillfactor of 90%, and should they be set to 100%?

Why would it matter that the fillfactor is on a primary key? The index works the same way. If you UPDATE an indexed row to a point to a new version of the row -- that is an UPDATE that wasn't heap-only, or if you add a row you have to potentially split the page the index is stored on to make room for the addition.

The point at which you have to do that is when the index's page is full. The question is after the pages are properly sized for the index, how much extra-space on those pages should be left for future operations? If the fillfactor was 100, every operation that wrote to the page would result in the page split. That would be super-slow.

So what fillfactor 90 does is leave a 10% gap. That's 10% more you can grow before you have to pay that price again. Only if you know you won't have to grow the index again would you want to set it to fillfactor 100 (and then REINDEX and shrink down all the pages).


SQL Server - what isolation level for non-blocking select statements?

In an ideal world you would have two choices, SNAPSHOT and READ COMMITTED SNAPSHOT (RCSI). Make sure you understand the basics of transaction isolation levels before you decide which is appropriate for your workload. Specifically be aware of the different results you may see as a result of moving to RCSI.
This sounds like it's not an ideal world as you don't have any control over the application that is generating the select statements. In that case, your only option is to enable RCSI for the database in question such that the selects will automatically use RCSI instead of READ COMMITTED.


Postgres Transaction OOM on 100k DDL statements

A better idea entirely is to use SQL Server FDW which actually has the logic to pull in Microsoft SQL Server into PostgreSQL format (for example, Bit gets mapped to Bool). From this point

Then every three minutes,

you import the foreign schema into last_fetch_schema
if the last_fetch_schema is different from local_schema

you resync schemas

you copy all of the data over with a INSERT INTO ... SELECT ON CONFLICT DO UPDATE, and you can select only the newest data.
you drop the foreign schema last_fetch_schema

What do you gain?

On first load, you can simply use CREATE TABLE local.foo ( LIKE foreign.foo)
You can easily compare meta-data differences
CSVs loses types and leave you to infer things, FDW can read meta-data catalog.
Grabbing only the newest stuff is very simply if the rows are versioned/ you don't have to send the entire database anymore.


If you want to change some of the global configuration setting of MySQL There are two ways.

1. By Changing variable using SET GLOBAL option on running MySQL instance,this does not require server restart we can change variables like Ex : I need to set my key buffer to 128 MB

SET GLOBAL key_buffer_size = 128*1024*1024;

But using this technique we can not change or configure all variables and once we restart MySQL these GLOBAL settings are lost.

2. By Changing variables in config file (my.cnf)

This requires a server restart but changes are permanent.


In Oracle how do you move a Schema to a different Tablespace?

A schema itself can not be stored nor can have changed tablespace en bloc in any way. In fact, it is just a meta-structucre. Instead - there is DEFAULT TABLESPACE attribute of underlying USER. If you change it, then new objects are created in this tablespace by default (unless you excplicitly specify another one).

In addition, each type of structure (Table/Index/Partition...) has it's own way of moving to another tablespace. (i.e. moving a table does not move indexes built upon it - which would be rather undesirable, because you want to store indexes in another filesystem for better performance). This means, that you will have to move contents of the schema object by object.


Full text search with json field in SQLite

A full-text search table cannot be used for most 'normal' operation; you should think of it as something like an index.

The FTS documentation shows how you can use triggers to keep the actual table and the FTS table in sync.


MySQL Store Procedure - Setting AUTO_INCREMENT value

Use a prepared statement

DELIMITER $$
CREATE DEFINER=`dbcomexuser`@`%` PROCEDURE `clearance_imp`(IN CAPIfilename VARCHAR(12))
BEGIN

DECLARE `_rollback` BOOL DEFAULT 0;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
DECLARE _refID INT DEFAULT 0;

START TRANSACTION;

SELECT MAX(imp_id) + 1 INTO _refID FROM temp_imp;

INSERT INTO filenamedt (filename, filedate) VALUES (CAPIfilename, now());

INSERT INTO imp__description (imp_id, imp_description) (SELECT imp_id, TRIM(descricaoProduto) FROM temp_imp);

TRUNCATE TABLE temp_imp;

SET @sql = "ALTER TABLE temp_imp AUTO_INCREMENT =  ?";
PREPARE stmt1 FROM @sql;
SET @a = _refID;
EXECUTE stmt1 USING @a;
DEALLOCATE PREPARE stmt1;
IF `_rollback` THEN
    ROLLBACK;
ELSE
    COMMIT;
END IF;
END$$
DELIMITER ;

If you are using workbench and create Stored procedure you don't need the DELIMITER


Export/import data from Excel into a table with queries

I've used Openrowset a number of times for this task.

This code will create a table in SQL.

SELECT * INTO EXCEL_IMPORT
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0; Database=C:\Excel\Spreadsheet.xls; HDR=YES; IMEX=1',
'SELECT * FROM [Sheet1$]');

Ideally you want to create the table first and then use INSERT INTO instead of the SELECT INTO. That way you can control the column data types.


In your code, you are issueing one SQL statement for every row you get from your people_source table. This is called row-by-row (read slow-by-slow) processing. For every query you make you force a context switch from PL/SQL to SQL engine and back which will add up pretty quickly.
As a first step, you should try to implement the functionality using just SQL (in this way, you eliminate expensive SQL/PLSQL context switch altogether). Try to use the merge DML as it was meant to be used:
merge into people_destionation dest
using (
  select firstname, lastname, email, id
    from people_source
   where people_source.date > last_run_date
) src on (src.id = dest.id)
when matched then
  updated set firstname = src.firstname,
              lastname = src.lastname,
              email = src.email
when not matched then
  insert (firstname, lastname, email, id)
  values (src.firstname, src.lastname, src.email, src.id)

In case this is slow, then we need more information (for example how many rows you expect to be merged, how are source/target tables created, are there any triggers, indexes) to be able to propose a way to make it faster (adding index on Id column, parallel execution, partitioning the table, eliminating triggers, disabling/recreating indexes, ...).


Create table permission for a user in specific schema

I want to give Create , alter and drop permission to user A on a schema1 of a database

The safe way to do this is to make A owner of that Schema.

Granting a user the ability to alter another user's schema gives that user the ability to SELECT, INSERT, UPDATE and DELETE rows in any table owned by the owner of that schema.  This is called "Ownership Chaining" and it's what makes Views and Stored Procedures really simple ways to control security, as a user who has permissions on a View or Stored Procedure does not need to be granted permissions on the underlying Table, so long as the View/Proc is has the same owner as the Table.

However in this scenario you can easily create a security hole with Ownership Chains.  Generally you never want a user to be able to create objects that will be owned by a different (and especially a privileged) user.

EG, just granting CREATE TABLE, ALTER, and INSERT creates a security hole:

use master
--drop database security_test
go
create database security_test
go
use security_test
go

create schema schema1 authorization dbo
go
create user A without login
go
grant create table to A
grant alter, insert on schema::schema1 to A
go
create table dbo.secret(id int, msg varchar(200))
insert into dbo.secret(id,msg) values (1, 'secret data')
go
execute as user='A'

create table schema1.foo(id int)

go
create trigger t on schema1.foo after insert
as
begin
  select * from dbo.secret
end

go

insert into schema1.foo(id) values (1)

go

revert

outputs

id          msg
----------- -----
1           secret data


What SQL Server database version do I need?

Indeed Merge Replication seems to be the best fit for this scenario.
You can also check Transactional Replication with Updateable Subscriptions with queued updates, but it is less suited for situations where the servers are mostly disconnected, and the conflict detection is quite limited.

You are also correct about the versions you need: Standard or Enterprise for the main server, Express for the subscribers.


How can I force Teradata to create a table in memory?

The answer to your question and other information about Teradata Intelligent Memory (TIM) can be found in the Orange Book about Teradata Intelligent Memory. Also in the appendixes are some SQL samples to query Teradata to verify what the temperature of a specific table is.

Please be advised that there is a general advice to NOT load data directly as "very hot", but to "hot" and let TVS then handle the temperature characteristics. Otherwise cooling down could be a problem.

To directly answer your question, there are 3 options:

1) Wait and access the table until TVM sees that the data is "very hot". If normal query characteristics access the table often there is a high chance the table will get loaded in memory and stay there.

2) Use a queryband to force it to "very hot"

SET QUERY_BAND='TVSTEMPERATURE_PRIMARY=VERYHOT;' FOR SESSION;

However you need to have access to the macro DBC.VHCTRL(). Otherwise TD will silently set your data to "hot".

3) Use FERRET:

> force "ADW_DB.INVOICE" P TEMPERATURE=VERYHOT
force "ADW_DB.INVOICE" P TEMPERATURE=VERYHOT
FORCE command changed the temperature of table ADW_DB.INVOICE
to VERY-HOT.
The temperature of 638 cylinders have been changed to VERY-HOT.

Please also note that a DBA is not amused if you suddenly load a huge table in TIM in production, forcing other data to not reside in TIM. There is a reason why the queryband is restricted...


Mysql: Count, Group By, and limiting to first instance of an ID

It takes 2 steps:

SELECT  user_id, COUNT(DISTINCT contact_id) AS 'count'
    FROM
    (
        SELECT  i.contact_id, MIN(a.user_id) AS user_id
            FROM  activities AS a
            JOIN  invoices AS i  ON i.id = a.post_id
            GROUP BY  i.contact_id
    ) x
    GROUP BY  x.user_id;

(Possibly the DISTINCT can be removed.)


"Server not open" / "Drop tablespace" catch-22 situation

No, it is not circular, alter database open is the 3rd step.

If you have a backup of that datafile, then restore and recover it.

If you do not have a backup, then do what the post you referenced tells you. Since your database is already mounted, you can skip step 1.

  If the database is down, mount it.

  STARTUP MOUNT;

  Offline drop the datafile.

  ALTER DATABASE DATAFILE 'full_path_file_name' OFFLINE DROP;

  If the database is at mount, open it.

  ALTER DATABASE OPEN;

  ...