How can change the default authentication method in MariaDB / MySQL

default_authentication_plugin is a MySQL system server variable and not in MariaDB.
For the moment you'll need to specify the plugin explicitly when creating users.
There's no feature requests to add this functionality yet.


I would use unnest together with array_agg, like this:
SQL Fiddle

PostgreSQL 9.3.1 Schema Setup:

create table t (
A double precision[5],
B double precision[5]);

insert into t values
('{3,2,0,3,1}', '{1,0,3,2,5}');

Query 1:

with c as(
select unnest(a) a, unnest(b) b from t)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c

Results:

|         A |         B |         C |
|-----------|-----------|-----------|
| 3,2,0,3,1 | 1,0,3,2,5 | 4,2,3,5,6 |

As commented below, the query above will work well for the arrays of the same size. Otherwise it may produce an unexpected result.

If you need to support the arrays of different size, use this query:

with a as(
  select unnest(a) a from t),
b as(
  select unnest(b) b from t),
ar as(
  select a, row_number() over() r from a),
br as(
  select b, row_number() over() r from b),
c as(
  select ar.a, br.b from ar inner join br on ar.r = br.r)
select array_agg(a) a, array_agg(b) b, array_agg(a + b) c from c;


You can get autogrowth events information from the default trace if it is enabled:

select distinct
    ei.eventid,
    e.name
from sys.fn_trace_geteventinfo(1) ei
inner join sys.trace_events e
on e.trace_event_id = ei.eventid
where name like '%grow%';

You can see from this that the default trace does have the Data File Auto Grow and Log File Auto Grow events capture.  To see if you have the default trace enabled on that instance, you can do the following:

exec sp_configure 'default trace enabled';
go

Note: this is an advanced configuration option, so show advanced options would have to get set to 1 to view this configuration option through sp_configure.  Also, these two events will not be triggered if the file grew manually.

Here is a quick sample query to get these events:

select
    te.name as event_name,
    tr.DatabaseName,
    tr.FileName,
    tr.StartTime,
    tr.EndTime
from sys.fn_trace_gettable('<Trace Path>', 0) tr
inner join sys.trace_events te
on tr.EventClass = te.trace_event_id
where tr.EventClass in (92, 93)
order by EndTime;

And you can get <Trace Path> from the system function sys.fn_trace_getinfo:

select *
from sys.fn_trace_getinfo(1);


Copying a subscribed database to the publisher server

Is there anything to modify on the the copied database to make the publisher reconise it as the deleted one and continues synchronizing ?

This is not possible. The subscription is now broken and should be removed / recreated per your requirements. There are unique identifiers in play beyond the DB name which is why it didn't work as you intended.


Postgresql server is running, but service appears stopped

For me, the answer lied in more careful reading of the excellent manual. Particularly the following three points:

See section 9.26.2, Server Signalling functions
See section Notes on how to signal to postgres
See description of the kill command

The general discussion about server shutdown is here.

So what I did was to terminate all the backends that were visible through pg_stat_activity view by pg_terminate_backend and then logging onto the server and running pg_ctl kill TERM <pid> to the remaining running postgres processes.


Single table with more columns vs multiple tables with fewer columns

My inclination here would always be design option 1, or at least along those lines. Do not worry too much about trying to eliminate the need to join tables in future queries - any normalized database is going to use joins in any useful queries, that's just relational databases.

Also, why would you necessarily have to join the userPosts and userGroupPosts tables for your website? Would they not be displayed separately? The only reason you would join these tables is maybe when searching for posts, but it shouldn't be too hard to write efficient queries for that. Apart from that you may end up wanting to query the tables for analysis purposes, but that is not the primary purpose of this database.

Design 2 could at the least mean that you end up with a very busy table.

The best option though would be to prototype each and run some tests. Build a prototype of each design option, and do some performance benchmarking on different operations with some dummy data.


SSPI error on windows authenticated remote access only

We finally found how to fix the issue. Unfortunately we do not know why it fixes it as the old VMWare image have the same setting it has on the Hyper-V image. Both suddenly stopped working.

To fix it we had to change the SQL server instance that has been running on the main Domain admin account for the last 10 years to run on LocalSystem and both SPN were registered under a user account. That user was another domain admin account which was the one originally used when SQL was first installed 10 years ago. That account is still active and used. We removed the SPN from the account and put them directly under the SQL dns.

Note that i don't know that interface where SPN are listed but i understood that it list all computer DNS names and all user account of the domain and SPN can be under either. Both IT have no idea why this suddenly stopped working. According to oldest image backup of the servers those settings never changed but that's all we needed to do to make everything work again.


Migrating database from old software to new software

Create a second MySQL database. This will be used for the migration and then dropped. These are known as staging databases. Make it an exact, table-for-table copy of your source. This way it is easy to export form the source and import into staging without worrying about mapping columns. The documentation will tell you how to load flat files into tables, and what formats it supports.

Once staging is fully populated write however many INSERT .. SELECT statements you need to map staging columns to the new DB columns and copy the data.

Do all this in scripts, not through the UI. Then it will be reproducable, debugable and audited. If you find a problem in the new app you can fix the script and re-run.


I was able to achieve what I wanted by the suggestions given in the comments by @WernfriedDomscheit and @prasad_

Now my mongodump command is as below

mongodump -h 127.0.0.1:27018 -d local -c oplog.rs --query "{\"ts\":{\"\$gt\":{\"\$timestamp\":{\"t\":${TIMESTAMP_LAST_OPLOG_ENTRY},\"i\":${INC_NUMBER_LAST_OPLOG_ENTRY}}}}}" -o - > ${OUTPUT_DIRECTORY}/${TIMESTAMP_LAST_OPLOG_ENTRY}_${INC_NUMBER_LAST_OPLOG_ENTRY}_oplog.bson 2>> "$LOG_FILE"

"TIMESTAMP_LAST_OPLOG_ENTRY" and "INC_NUMBER_LAST_OPLOG_ENTRY" are being fetched from the previous '.bson' file.

Thanks,
Rahul Paryani


Your index probably only contains the lastModified column, as well as your _id value as a way of finding the other column values for each row.

So if your predicate (the WHERE clause) isn't filtering your index data down to few enough rows, the effort in looking up the other details over and over is too much and it will decide that it's easier to just scan the whole table.

If your index included all the columns you were interested in, you would see an ordinary seek.


A rollback of a BCP batch transaction occurs when SQL Server errors occur during the bulk insert (e.g. constraint violations) but not due to client side errors (e.g. data type conversion during BCP parsing). Erred records are not sent to the server and are not part of a transaction.

SQL Server errors will not only rollback the current batch but abort the entire BCP operation.


Adding an index without a lag (PostgreSQL)

See Building Indexes Concurrently in PostgreSQL documentation.
CONCURRENTLY option of CREATE INDEX solves the problem in an easy way. Just add it to CREATE INDEX.
Be aware that while CONCURRENTLY option avoids lags, it does slow down building the index and that CREATE INDEX with CONCURRENTLY cannot be used in a transaction.


How to solve Error 927 'can not open database' while configuring DB mirroring

It seems there are bugs with SSMS (2016) to configure mirroring
https://stackoverflow.com/questions/38008208/sql-server-2016-cant-start-mirror

Try to use TSQL or SSMS 2014

https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-establish-session-windows-authentication


Duplicate Oracle Database from .BKP files - getting "multitenant container database not set up properly"

65093, 00000, "multitenant container database not set up properly"

  Cause:  An attempt was made to open a multitenant container database without
              the correct parameter set for a multitenant container database in
              the initialization parameter file.

  Action: Set the 'enable_pluggable_database=true' parameter for the
              multitenant container database in the initialization parameter file
              and restart the database.

By default ENABLE_PLUGGABLE_DATABASE is set to FALSE. We need to set this parameter to TRUE before creating CDB.

RAMN>duplicate database to ECLS
    SPFILE
    set enable_pluggable_database=true
 backup location '/u01/dumpfilesgohere';


Database size as measured by

select pg_size_pretty(pg_database_size(current_database()))

or using du at the file system level
won't usually go down when you delete records. Even after VACUUM has had time to run. Instead, when you next add new records it won't go up because the free space inside the database gets re-used.

VACUUM can sometimes shrink a table and free space to the operating system, but only if there aren't any still-needed rows near the end of the table. Just one still-needed row can prevent the compaction of a huge table. There is no automatic background defragmentation performed to stop that happening.

You can VACUUM FULL to force space to be released back to the OS. This can speed up SELECTs considerably, too. However, it will make subsequent INSERTs and UPDATEs considerably slower since they need to claim new space back from the OS again. You can mitigate that by ALTERing the FILLFACTOR on your tables and indexes to be less than 100 (percent), so there's free space within the table to re-use immediately. Also, VACUUM FULL locks the table completely while it's running, which you really don't want on a running production DB.

Now, the size of a dump is different. That does go down when you delete data. If it doesn't go down, then you didn't delete anything, you added as much data as you deleted, or the data you removed is so small that it doesn't make a significant difference to the dump size.

If you delete records then do a new dump and it doesn't change size, I'd say that:

You didn't COMMIT the delete, so it hasn't taken effect;
You added about as much data as you deleted;
You deleted it from a different copy of the database to the one you dumped

You can check all of these with things like select count(*) from table_of_interest and select pg_size_pretty(pg_total_relation_size('table_of_interest')).


How to delete a procedure which name is ambigous?

This happens when you have 2 or more procedures, with the same name, but with different numbers of input parameters.

For example, you have created 2 procedures:

CREATE PROCEDURE myProc(param1)
...
CREATE PROCEDURE myProc(param1, param2)
...

To delete the second one, you have 2 options:

The easy one:

DROP PROCEDURE myProc(param1, param2);

The hard one:

dbaccess DB -
select procname, procid, numargs from sysprocedures where procname like 'myProc';
procname  myProc
procid    1
numargs   1

procname  myProc
procid    2
**numargs   2**

UPDATE sysprocedures SET procname='myProcOLD' WHERE procid=2;
DROP PROCEDURE myProcOLD;

Even if the first method is dead simple, the first time I got called in the middle of the night for this same problem, I've chosen the second. My bad ...


Convert query for count and updated date

Starting point, mostly unchanged:
SELECT count(*), min(last_updated_date), max(last_updated_date)
FROM   schema.table;

Be aware of (not) case sensitive behavior of identifiers in Postgres. If in doubt use legal, unquoted, lower-case names exclusively. See:

Are PostgreSQL column names case-sensitive?

A table can optionally be schema-qualified (schema.table), but not database-qualified. A name like from "SOURCE"."SCHEMA"."TABLE" would raise:

ERROR:  cross-database references are not implemented: "SOURCE.SCHEMA.TABLE"

Table names without schema-qualification are resolved according to the current search_path. See:

How does the search_path influence identifier resolution and the “current schema”

count(*) is a bit faster than count(1).

Min and max can be read from a matching index in next-to-no time. Only the count can be slow. If your count does not have to be exact, consider:

Fast way to discover the row count of a table in PostgreSQL


Is there a way to alias columns for reference in subqueries?

You need a table alias for both occurrences of the table and then prefix every column with the corresponding table alias to make the column references unambiguous.

select t1.*
from the_table t1
where exists (select *
              from the_table t2
              where t2.a = 7
                and t2.b = t1.b + 1);

Note that you probably want something like and t2.primary_key <> t1.primary_key in the sub-select as well.


What indexing to be used over string columns(text type) with string length around 3000 characters

Are you looking to use the existing methods already implemented in PostgreSQL, or to implement your own new indexing methods?

PostgreSQL's implementation of b-tree indexes cannot index values longer than about 2712 (although it will compress the value if it is highly compressible, before failing due to size) unless you recompile PostgreSQL with a non-standard block size.

PostgreSQL has hash indexes, but they are discouraged because they are not crash-safe.  And as you say, it would only support '=' operators.

You might be able to use the pg_trgm extension which creates a type of GIN index which can support LIKE queries.  It should be able to accommodate your long strings, but it might not be very effective with them.  But with some C programming you could perhaps customize it to suit your usage.


All pull subscriptions cannot connect to Distributor

This is a permissions issue, the Distribution Agent process account does not have rights on the distributor.

Verify the Distribution Agent process account is a member of the PAL, has read permissions on the snapshot share, and is a member of the db_owner fixed database role in the subscription database.


Watch for the high activity again; when you see it, start a server side trace or if necessary use Profiler briefly to see what's going on.

Alternately, use a packet sniffer like Wireshark to capture raw wire activity.

Check dm_exec_cached_plans to see if that gives any idea what's going on.

Watch dm_io_virtual_file_stats to see which files in particular, if any, are being hit.


Quick and dirty answer:

UPDATE RBI.RBI_OV_F_DETAIL A
 SET (BRANCH_ID, BRANCH_NUMBER) = (
       SELECT PRICE_BRANCH_ID, PRICE_BRANCH
         FROM RSE.RSE_SO_F_ORDERS B
            WHERE A.SOURCE_ID = B.FULL_ORDER_NUMBER AND A.METRIC_GROUP_ID='1'
       group by price_branch_id, price_branch);

However if you're not expecting duplicates in the RSE.RSE_SO_F_ORDERS table, the more accurate answer would be to hunt down why those duplicates are there, and work with any stakeholders to eliminate the duplicates.


Why does Oracle yield multiple levels per row in this hierarchical query?

You're getting many rows because without START WITH it outputs all possible hierarchies .  You see 1 reports 2 on level 1 (1->2), and another one on level 2 (1->2->NULL).   Adding  SYS_CONNECT_BY_PATH(reports_to, '/') "Path" to select will help in visualizing that.
Let's connect just 2 rows for illustration:

with test1 as
(
select 1 as employee_id, 2 as reports_to from dual
union all
select 2 as employee_id, NULL as reports_to from dual

)
select employee_id,reports_to ,level ,SYS_CONNECT_BY_PATH(NVL(to_char(reports_to),'NULL'), '/') "Path"

from test1
--start with employee_id=2
connect by prior employee_id  =reports_to;

will output :

Employee    ReportTo   Level Path
1              2        1     /2
2              <null>   1     /NULL
1              2        2     /NULL/2

If we uncomment "start with" , the result will be

2   <null>  1   /NULL
1      2    2   /NULL/2


Subtract column value from date in days or months

You don't need to use the interval syntax to begin with. You can simply subtract an integer from a date value, e.g. current_date - 1 is "yesterday".
select current_date - lag_days
from the_table;

If you do want to use an interval, the make_interval() function is quite useful for this:
select current_date - make_interval(days => lag_days)
from the_table;

There is however a difference between the two queries: the first one returns a date value, the second one a timestamp value.


Database Design Help: MySql/PostgreSQL Single Database/Multiple Databases

For a data warehousing/analytics type application (DW/OLAP), I would go with PostgreSQL. It has set operators, windowing functions (also known as analytic functions) and common table expressions. You will most likely have to implement some or all of these in your own code with MySQL with the attendant possibility of bugs. MySQL is more suited to read-heavy OLTP type applications, whereas PostgreSQL is better (IMHO) for OLAP work.

With respect to "multi-tenancy" - why not use the same database for all and simply identify different customers by a customer_id - or (depending on the number of customers) have separate tables for each customer and non-customer specific data in other tables? How many customers do you have and how much data do you expect to be storing/analysing  in the next 10 years? Modern RDBMSs can store large amounts of data and retrieve quickly (with suitable disk configuration), but I would favour PostgreSQL for large databases.

Sharding might be interesting if you only occasionally query across customers and most of your analysis is done per customer - you could set up a table (or even schema) per customer (on different disks). Again, knowledge of data volumes would help here.

Furthermore, with respect to multi-tenancy, see my question to jynus about querying across servers - or even different schemas within the same server.

I take jynus' point about "holy wars" - this post is just my own opinion about your particular question - were I to be asked about a different scenario, I might well recommend MySQL.

[EDIT]

What, exactly, is "my dashboard performance"?


There is any way to log all mongodb query into file in nodejs?

Enable the profiler in MongoDB Database.

https://docs.mongodb.com/manual/reference/method/db.setProfilingLevel/


Getting database usage of all the database at once in Sybase

Below is the script that I use --- master..sysdatabases&  master..sysusages

--- Source : http://benohead.com/sybase-size-of-data-and-log-segments-for-all-databases/

select db_name(d.dbid) as db_name,
ceiling(sum(case when u.segmap != 4 then u.size/1048576.*@@maxpagesize end )) as data_size,
ceiling(sum(case when u.segmap != 4 then size - curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end)/1048576.*@@maxpagesize) as data_used,
ceiling(100 * (1 - 1.0 * sum(case when u.segmap != 4 then curunreservedpgs(u.dbid, u.lstart, u.unreservedpgs) end) / sum(case when u.segmap != 4 then u.size end))) as data_used_pct,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end)) as log_size,
ceiling(sum(case when u.segmap = 4 then u.size/1048576.*@@maxpagesize end) - lct_admin("logsegment_freepages",d.dbid)/1048576.*@@maxpagesize) as log_used,
ceiling(100 * (1 - 1.0 * lct_admin("logsegment_freepages",d.dbid) / sum(case when u.segmap in (4, 7) then u.size end))) as log_used_pct
from master..sysdatabases d, master..sysusages u
where u.dbid = d.dbid  and d.status not in (256,4096)
group by d.dbid
order by db_name(d.dbid)


Would be something like:

select  p1.ref, p1.storeid, p1.price, p2.price
from    prices p1
join prices p2 on p1.ref = p2.ref
where   trunc(p1.datetime) = trunc(sysdate)
and     trunc(p2.datetime) = trunc(sysdate-1)
and     p1.price <> p2.price;

You can add Stores to get store information if you need. I do not know which DB you use so I gave a solution for Oracle. The trunc function gives you the date part of a date.


Multiple Availability groups under same IP

In theory, each Availability Group needs its own IP address (and virtual network name) for the listener. This way, you can fail the primary around between places - after all, you could have one AG that's a primary on server1, while you fail a separate one of the AGs over to server2 because it's having corruption problems.

In practice, you can use Availability Groups without a listener, and just have your apps connect to a specific SQL Server, bypassing the listener (and IP requirements) altogether. However, this means that:

Managing failover is completely up to you
Every time you have a failover, you have to touch every one of your app's connection strings because the server name will change (or you have to use a DNS CNAME)
You can't have some of the AGs be primary on one server, and some on another (in other words, all 4 of the primary writeable workloads have to be on the same server)
You can't use read-only routing - if you want to run read-only workloads on the secondaries, your apps have to connect to those other servers by name

It's a lot of drawbacks, but it IS doable. (StackOverflow was running this way for a while, not sure if they still are, because we had so many problems with the AG listeners.)


You've left out so much that your chances of recovering useful data are negligible.

pg_clog contains the commit/rollback logs. Without these, the system doesn't know which parts of the database files are valid and which are not. (gross oversimplification, but hey).
pg_xlog, the write-ahead logs. Without these, the database can't handle incomplete writes, so it'll see the database files in an incomplete state that might be damaged. Indexes may be corrupt, heap pages might be partially written, etc.
global/pg_control, the file that contains the system identifier, transaction wrap-around value, transaction log checkpoint position, and a whole lot more.

... and more.

It is vital to follow the instructions in the documentation on how to back up a database. Failure to follow the instructions will result in a useless, unrecoverable backup. Testing backups is also vital.

With in-depth analysis and a hacked version of the postgres server binary that was modified for data recovery I expect it might be possible to recover a corrupted version of your data, with duplicate entries in primary keys, broken foreign keys, multiple copies of data that was updated, reappearing deleted rows, etc. This would require some in-depth knowledge of the PostgreSQL server and take some significant time - so you'd be looking at a non-trivial cost and you'd only get back a mangled copy of your data.


Using group by on several tables with problems

I solved it that way:

  select
        SR.AN_RAP as AN, RAP.CF,
        max(col_cant.CANT_VAL) as CANT_VAL,
        max(col_cant.COD_VALORIFICARE) as COD_VALORIFICARE
    from dma
        inner join ch on ch.id=DMA.CH_ID
    inner join
            (select CANT.ID,CANT.FK_ID_MUN,CANT.CANT_VAL,CANT.COD_VALORIFICARE from cant
            union
            select  COL.FK_ID_COL_DMA,CANT2.FK_ID_MUN,COL.CANT_VAL, COL.COD_VALORIFICARE from col
                inner join cant2 on cant2.id=COL.FK_ID_COL_DMA
            ) col_cant
       on col_cant.FK_ID_MUN=ch.id
    inner join sr on SR.ID=CH.FK_ID_SR
    inner join rap on RAP.ID=CH.FK_ID_RAP
    where RAP.CF='123-123'
    group by SR.AN_RAP,RAP.CF,CANT.ID,DMA.FK_AMB_ST,
            col_cant.COD_VALORIFICARE
    order by sr.an_rap desc,rap.cf,col_cant.id


Get result of joining multiple tables as one row

You can use the GROUP_CONCAT function

SELECT a.id, a.name, CONCAT('[',GROUP_CONCAT(b.institution),']') institution
FROM table1 a INNER JOIN table2 b
ON a.id = b.profile_id
WHERE a.id = USER_ID
GROUP BY a.id, a.name;

or with your original query

select a.id, a.name, CONCAT('[',GROUP_CONCAT(b.institution),']') institution
from table1 a, table2 b where a.id = USER_ID and a.id = b.profile_id
group by a.id, a.name;

Give it a Try !!!


Two Different Sources Taking Transaction Log Backups

If a disaster occurred and I needed to recover the database from backup, would I be missing data?

As long as all of the backups are in tact, no.

The transaction log chain is not broken, and point-in-time recovery is possible.

It's just that the backups that constitute a complete transaction log chain are not all in the same location.

Having said that, I would recommend consolidating the systems so that only one of them is responsible for taking backups. While it sounds like your situation was set up that way unintentionally, if you want to create a redundant copy of a backup at an alternate location, there are many options to do so, but that's beyond the scope of the question here.

  If I were to do a restore from either backup (unless it's using only the full backup), then it would not include all of the data.

Correct, you would not be able to restore from the TDP backups alone, or the SQL Agent backups alone.

After restoring the latest full backup, you would need to restore all of the log backups between that point-in-time and the current point-in-time. This will involve combining the set of backups from both locations, and restoring them in the correct sequence as each log backup will contain a distinct portion of the backed up transaction log.


Keeping postgres connection behind load balancer alive during long-running query (vacuum)

There is no way to have activity on a database connection that is waiting for a server response.
I'd say that that is a bug in the load balancing software, and you should report it. Your use case is a valid one.


Dropped Index during Database Engine Tuning Advisor

The indexes that were created and dropped by DTA were most likely hypothetical indexes - ones that DTA creates while running to perform its analysis and should (but doesn't always) delete once done.

These hypothetical indexes can be created even if you weren't looking at tuning that specific table! I'd recommend you query some of the system DMVs directly to find out about missing indexes. This avoids the pesky behavior of DTA and provides some more granular insights into your index problems.

You can use the below query from SQL Server Central to detect any remaining hypothetical indexes from DTA that should be removed.

/*
      -----------------------------------------------------------------
      Hypothetical Indexes
      -----------------------------------------------------------------

      For more SQL resources, check out SQLServer365.blogspot.com

      -----------------------------------------------------------------

      You may alter this code for your own purposes.
      You may republish altered code as long as you give due credit.
      You must obtain prior permission before blogging this code.

      THIS CODE AND INFORMATION ARE PROVIDED "AS IS"

      -----------------------------------------------------------------
*/
-- Set database context
USE master;
GO

-- Declare variables
DECLARE@EmailProfile VARCHAR(255)
DECLARE@EmailRecipient VARCHAR(255)
DECLARE@EmailSubject VARCHAR(255)

-- Set variables
SET@EmailProfile = 'DBA'
SET@EmailRecipient = 'Chris@SQLServer365.com'
SET@EmailSubject = 'ALERT - Hypothetical Indexes found on ' + @@SERVERNAME

-- Drop temporary table if exists
IF OBJECT_ID('tempDB.dbo.#HypotheticalIndexDropScript') IS NOT NULL
    DROP TABLE #HypotheticalIndexDropScript;

-- Create Temporary Table
CREATE TABLE #HypotheticalIndexDropScript
    (
      DatabaseName VARCHAR(255) ,
      HypotheticalIndexDropScript VARCHAR(4000)
    );

INSERT  INTO#HypotheticalIndexDropScript
        EXEC sp_msforeachdb 'USE [?]; SELECT  DB_NAME(DB_ID()), ''USE '' + ''['' + DB_NAME(DB_ID()) + ''];'' + '' IF  EXISTS (SELECT 1 FROM sys.indexes  AS i WHERE i.[object_id] = '' + ''object_id('' + + '''''''' + ''['' + SCHEMA_NAME(o.[schema_id]) + ''].'' + ''['' +  OBJECT_NAME(i.[object_id]) + '']'' + '''''''' + '')'' + '' AND name = '' + '''''''' + i.NAME + '''''''' + '') ''
       + '' DROP INDEX '' + ''['' + i.name + '']'' + '' ON '' + ''['' + SCHEMA_NAME(o.[schema_id]) + ''].'' + ''['' + OBJECT_NAME(o.[object_id]) + ''];'' AS HypotheticalIndexDropScript
FROM    sys.indexes i
        INNER JOIN sys.objects o ON o.[object_id] = i.[object_id]
WHERE is_hypothetical = 1'

-- Check for hypothetical indexes
IF EXISTS ( SELECT  1
            FROM    #HypotheticalIndexDropScript )
    BEGIN
        DECLARE@tableHTML NVARCHAR(MAX);
        SET@tableHTML = N'' + N'
' + N'
'
            + N'DatabaseName	'
            + N'HypotheticalIndexDropScript	' + N'
'
            + CAST(( SELECT td =DatabaseName ,
                            '' ,
                            td = HypotheticalIndexDropScript ,
                            ''
                     FROM   #HypotheticalIndexDropScript
                   FOR
                     XMLPATH('tr') ,
                         TYPE
                   ) AS NVARCHAR(MAX)) + N'
';

            -- Email results
        EXECmsdb.dbo.sp_send_dbmail @profile_name = @EmailProfile,
            @recipients = @EmailRecipient, @subject = @EmailSubject,
            @body =@tableHTML, @body_format = 'HTML';
    END
    GO


Select items that have never been ordered using the MINUS operator

How about:

select item# from item
minus
select item# from ORDER_ITEM


How to optimize search restricted by boolean column?

The expression is_principal is the same as is_principal <> 0, so the database assumes that all values except 0 are searched for, and estimates that most rows will match.

If the values in the is_principal column are proper booleans, you can get the database to assume a higher selectivity by searching for the value you actually want to search for:

SELECT yid, xid FROM x WHERE is_principal = 1

This query is likely to read the rows from the table in index order (i.e., random order), so if there is a large number of matching rows, performance will be worse.


Postgres record locking issue - could asynchronous UPDATES be a solution?

Having thought a bit more about the problem, I realise there's actually a relatively simple solution. Since each process only updates its 'own' fields the solution is to split the data into two tables allowing each half to be updated by it's own process and any SELECTs can be done by joining on the common primary key.


Create unique index oracle with where clause

No such thing in Oracle, just some workaround with function based indexes:

CREATE UNIQUE INDEX cmt_unique_sid_yid_ct
ON COMMENTARY (
  case when comment_type_name = 'Final' then source_id end,
  case when comment_type_name = 'Final' then year_id end
);

Sample query that can use this index:

select * from commentary
where
case when comment_type_name = 'Final' then source_id end = :B1
and case when comment_type_name = 'Final' then year_id end = :B2
;


What does Oracle SQLCODE ORA-00942 signify?

From the Oracle Doc:

  SQL*Loader-00942: partition string not part of table string

  Cause: the specified partition is not part of the specified table

  Action: None


High Available PostgreSQL with Patroni and Replication

Only the master server should accept write operators

This is guaranteed by patroni for almost all cases. Essentially, you need to be malicious to provoke a "split-brain" scenario where more than one database accepts writes in a patroni cluster. There are several layers of security against this scenario built into patroni.

On the other hand i also need approximately 10 servers with an up-to-date copy of the data on the cluster. These servers are only there to read data and none of them ever can be promoted to a master server.

This is simple and straigh-forward to do in patroni:
In the configuration file for each of these nodes you can enable a special "tag", nofailover: true, so these database instances will never be considered during leader election, even thought they are in the same patroni cluster. The advantage of doing this is that patroni still takes care of all management, by creating replication slots, running pg_basebackup, keeping the configuration in sync etc.
https://patroni.readthedocs.io/en/latest/SETTINGS.html?highlight=nofailover#tags
In the end, if you prefer not having these replicas directly in your patroni cluster, you can always set up additional streaming replication independently, ontop of what patroni configures, though I think this only makes life more difficult...


Below is an example using a CTE with conditional aggregation.
WITH inventory_per_computer AS (
    SELECT Computer_id, SUM(CASE soft WHEN '7Zip' THEN 1 ELSE 0 END) AS Installed
    FROM dbo.SoftwareInventory
    GROUP BY Computer_id
    )
SELECT Computer_id, CASE Installed WHEN 0 THEN 'no' ELSE '7Zip' END AS Software
FROM inventory_per_computer
ORDER BY Computer_id;


How to isolate that network is issue of bad query performance

Waits and Queues is a good resource for this problem. Is a methodology for investigating SQL Server performance problems. It will teach you and help you find the bottleneck, which is better than to make assumptions and try to prove your assumption. Anyway, the paper has specific advice on how to identify Network as a bottleneck. A quick check is ASYNC_NETWORK_IO wait type stats, high values indicate frequent stalls waiting for Network IO sent to the client to complete (ie. marshaling the results is slow). Read the paper for more details.

Note that well designed applications seldom are affected by network bandwith. Short of special ETL scenarions, a high bandwith requirement is an indication of bad app design, not of network problems. High Network latency also affects chatty applications more, and chattiness (many round-trips) is again a smell of bad design. Both network badnwith and latency are trivial to test and measure, again read the paper linked for methodology.


Do I really need Re-Build Index Task in SQL Server all the Time?

Do I really need Re-build Index Task

The answer to that is very much "it depends". If the data in your tables doesn't change much over time the definitely no, at least not often.

  or I need it only when the Fragmentation Level is above 30% ?

How much fragmentation you allow before a rebuild is an art rather than a science and may depend on your data patterns and performance metrics+targets, but there is definitely no need to rebuild a large index if there is only a very small amount of fragmentation so definitely check each index before rebuilding to see if it is worth the IO load.

  Do I need Re-Organize Index Task when I have Re-build Index Task ?

There is no point reorganising around the same time (either before or after) rebuilding. Reorganising is a less intensive operation then a full rebuild while having some but not all of the benefits. If you rebuild after reorganising then the reorg was wasted as the whole structure is thrown away, if you reorganise after then there is nothing for the process to really do so anything it tries to do is wasted effort.

  My Major concern is, we have the Databases in Full Backup Recovery Model and the T-Log Auto-Growth setting is in '%' .

I would generally recommend against % increments as they can produce large unneeded growth at times. Set the growth to a fixed size, reasonably large so it doesn't need to happen too often but not overly large. The values of "reasonably" and "overly" will change depending on your database.

  This is eating up all my T-log drive space

If altering your plans to rebuilt/reorg/nothing less aggressively (i.e. not rebuilding everything every time irrespective of current fragmentation) then perhaps you could spread the load over the week by working on a subset of your tables each day. This will make little difference if you DB has one huge structure and the rest of the data is in very small tables (an analytics setup with one huge results table and a series of small dimension tables perhaps) but could be beneficial otherwise. Try altering the current plan to be less aggressive first though, this will be simpler to implement and maintain, and there are many example scripts out there that you can take and tweak for your database's needs.


Handling case statement subquery returning multiple rows

NOTE #1: Without knowing which RDMBS you're using I can't say if your proposed query will work or not (I'm leaning towards 'doubtful').

NOTE #2: I'm assuming the ( just before the isnull is not supposed to be there, otherwise I'm not sure what logic you're trying to implement, ie, you'll need to update your query with matching parens.

I'm thinking you'll want to pull the isnull test out to the top level and use an or to satisfy your conditional test, eg:

-- using '@id in (select ...)' construct

select *
from   temp tp
join   a
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
       or a.startdate between @startdate and @endDate)

and    (
            @id is not NULL
        or
           (@id is NULL and a.id1 in (select distinct id from #temp2) )
       )

-- or
-- using 'exists (select ...)' construct

select *
from   temp tp
join   a
on     tp.id = a.id

where  (  a.enddate   between @startdate and @endDate
       or a.startdate between @startdate and @endDate)

and    (
            @id is not NULL
        or
           (@id is NULL and exists(select 1 from #temp2 t where t.id=a.id1) )
       )


SQL Server sample Update of Statistics misses highest RANGE_HI_KEY on ascending key column

Should SQL Server not identify the highest value for that key and use that as the maximum RANGE_HI_KEY? Or is this just one of the limits of update without using FULLSCAN?

It's a limitation of the current implementation of sampled statistics. As it stands, sampled statistics collection uses TABLESAMPLE SYSTEM, which uses an allocation-order scan and chooses pages from the scan to sample. Only chosen pages contribute to the histogram.

Since the scan is allocation-ordered (rather than index-ordered), there is no way to give preference to the first and last pages in key order.

For more information see this related question:

How does sampling work when updating statistics?

and my article, Allocation Order Scans

For workarounds, see Statistics on Ascending Columns by Fabiano Amorim


Are iSeries AS/400 (DB2?), Lotus 1-2-3, and Excel date/time formats the same?

No.

Db2 for i (aka AS/400) dates are stored internally as a Scaliger number (aka Julian Day number)

The number of days since noon on Monday, January 1, 4713 BC.

select hex(current_date)
from sysibm.sysdummy1

returns 002583EC as of about 3pm MDT April 30,2019

The decimal value is 2,458,604

Which agrees which the current value on the web page linked to above


You can use FOR XML PATH() clause to generate the desired output.

I'm starting from your result set and generate the output.

DECLARE @Table1 TABLE
    ([ID] int, [Name] varchar(8), [Examiners Name] varchar(6))
;

INSERT INTO @Table1
    ([ID], [Name], [Examiners Name])
VALUES
    (1290, 'Jun', 'Aron'),
    (1290, 'Jun', 'Mark'),
    (1292, 'Takahiro', 'Didier'),
    (1292, 'Takahiro', 'Eiro'),
    (1292, 'Takahiro', 'Kurt')
;

SELECT
    C.[ID],
    C.[Name],
    STUFF(CA.[Ex_Name],1,1,'') AS [Examiners Name]
FROM
    (SELECT DISTINCT
        [ID],
        [Name]
     FROM
        @Table1) AS C
     CROSS APPLY
     (
        SELECT
            ','+E.[Examiners Name]
        FROM
            @Table1 AS E
        WHERE
            C.[ID] = E.[ID]
        ORDER BY E.[Examiners Name]
        FOR XML PATH('')
    )CA(Ex_Name)

The output for this :

ID          Name     Examiners Name
1290        Jun      Aron,Mark
1292        Takahiro Didier,Eiro,Kurt

If you provide the DDL for your table , we can post a  query base on these tables.

And base on your raw tables:(something that is not tested)

SELECT
usr.CandidateID,
usr.FirstName,
STUFF(Examiner.FirstName,1,1,'') AS [Examiners Name]
FROM
(
    SELECT DISTINCT
        usr.CandidateID,
        usr.FirstName,
        ExamCa.ExaminerID
    FROM
        dbo.tbl_User AS usr
        LEFT JOIN dbo.Tbl_ExaminerCandidates AS ExamCa
            ON usr.UserID = ExamCa.CandidateID
    WHERE
        usr.CandidateID IS NOT NULL AND
        usr.IsActive = 1 AND
        usr.UserStatus = 1
)AS USR
OUTER APPLY
(
    SELECT
        ','+ E.FirstName
    FROM
        dbo.tbl_User AS E
    WHERE
        E.UserID = USR.ExaminerID
    FOR XML PATH('')
)Examiner(FirstName)


Does the max_allowed_packet variable have any effect on LOAD LOCAL INFILE?

LOAD DATA INFILE can be thrown off if read_buffer_size is larger than max_allowed_packet. This is the case when it comes to replication.

See the following bug reports

http://bugs.mysql.com/bug.php?id=15937
http://bugs.mysql.com/bug.php?id=30435
http://lists.mysql.com/mysql/168070

Outside of MySQL Replication, I can't really tell.

One thing is for sure: Whatever mysql had as the setting for max_allowed_packet on a server that performed SELECT ... INTO OUTFILE, the server into which you import data via LOAD DATA INFILE better have the same setting for max_allowed_packet or greater.

The max value for max_allowed_packet is 1G. It couldn't hurt to just set that in my.cnf.

[mysqld]
max-allowed-packet=1G

You do not have to restart mysql. Just run this:

SET GLOBAL max_allowed_packet = 1024 * 1024 * 1024;

then you can perform the LOAD DATA INFILE.


Optimised query to select document and possible annotations

The best I've been able to come up with is to limit the size of the temporary table as much as possible by changing the inner WHERE to:

WHERE user_id=<some_user_id_here> AND document_id=<some_document_id_here>

It means the document ID has to be repeated in the statement, but at least means that the inner table is small, and pretty much guaranteed to be in-memory.


SELECT A.id
  FROM A
  LEFT JOIN B ON B.id_A = A.id AND B.c = x
 WHERE B.id_A is null;

Moving the test of B.c into the join condition and out of the where clause causes it to be used to only eliminate non-matching B-rows from consideration for the left join rather than eliminating rows from the result.


Schema check or "DATE as separate table"

It would have been great if your sqlfiddle data would have been in English...

If the activities aer bound to a single location, you could use your current approach.
But if there's a chance that one activity can be referenced to more than one location you should use a separate table to show such a reference (using foreign keys to the primary keys of your activity and location tables).

A calendar table usually is very helpful if you'll need to return any results for a consecutive date range including date values without any corresponding values (e.g. activities).


Migrate SQL Server - server name in stored procedures references old server

You could create a synonym, say:

CREATE SYNONYM dbo.remotetable1 FOR [new_server\instance1].db1.dbo.table1;

Then the server name is just in the synonym for the object, and not littered throughout the code - the code just references dbo.remotetable1.

When you need to change where it points, you just change the synonym once, and that can include consolidation (where the server prefix is no longer required).

I wrote up a little about synonyms here:

#BackToBasics : The Beauty of the Synonym


Managing failover for MySQL nodes using HA Proxy

There is a 'backup' option that you can use in HAProxy that does what you are after. e.g. if M1 fails, use the the backup (M2)

https://www.haproxy.com/blog/failover-and-worst-case-management-with-haproxy/

Then once M1 is available again, it redirects back to M1.

n.b. you do of course need to think about replication delays, as you don't want to redirect back to M1 if it is an hour behind M2 when it comes back up. We use something like this to achieve it:

http://sysbible.org/2008/12/04/having-haproxy-check-mysql-status-through-a-xinetd-script/


Indexed View - how to verify if it is worthy?

This Script contain few columns

 1. UserSeek : Number of seeks by user queries
 2. UserScans: Number of scans by user queries
 3. UserLookups: Number of bookmark lookups by user queries
 4. UserUpdates: Number of updates by user queries

on the basis these column stats you can figure out either index is useful or not.

Actually Index are inserted to, update to, or deleted on every DML statement so don’t worry if index show updates for a particular index. Typically don't add index unless it respond more than 20% \ enough fast for the query.

SELECT DB_name() DBName,
                        '[' + Sch.name + '].[' + vi.[name] + ']' AS vileName,
                        Ind.type_desc,
                        Ind.[name] AS IndexName,
                        SUBSTRING(( SELECT  ', ' + AC.name
                                    FROM    sys.[views] AS v
                                            INNER JOIN sys.[indexes] I ON v.[object_id] = I.[object_id]
                                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
                                                                                AND I.[index_id] = IC.[index_id]
                                            INNER JOIN sys.[all_columns] AC ON v.[object_id] = AC.[object_id]
                                                                               AND IC.[column_id] = AC.[column_id]
                                    WHERE   Ind.[object_id] = I.[object_id]
                                            AND Ind.index_id = I.index_id
                                            AND IC.is_included_column = 0
                                    ORDER BY IC.key_ordinal
                                  FOR
                                    XML PATH('') ), 2, 8000) AS KeyCols,
                        SUBSTRING(( SELECT  ', ' + AC.name
                                    FROM    sys.[views] AS v
                                            INNER JOIN sys.[indexes] I ON v.[object_id] = I.[object_id]
                                            INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id]
                                                                                AND I.[index_id] = IC.[index_id]
                                            INNER JOIN sys.[all_columns] AC ON v.[object_id] = AC.[object_id]
                                                                               AND IC.[column_id] = AC.[column_id]
                                    WHERE   Ind.[object_id] = I.[object_id]
                                            AND Ind.index_id = I.index_id
                                            AND IC.is_included_column = 1
                                    ORDER BY IC.key_ordinal
                                  FOR
                                    XML PATH('') ), 2, 8000) AS IncludeCols ,
                        usg_stats.user_seeks AS UserSeek,
                        usg_stats.user_scans AS UserScans,
                        usg_stats.user_lookups AS UserLookups,
                        usg_stats.user_updates AS UserUpdates,
                        getdate()  StatsTimestamp
                FROM    sys.[indexes] AS Ind
                        INNER JOIN sys.[views] AS vi ON vi.[object_id] = Ind.[object_id]
                        INNER JOIN sys.[schemas] AS Sch ON Sch.[schema_id] = vi.[schema_id]
                        LEFT OUTER JOIN sys.dm_db_index_usage_stats AS usg_stats ON  Ind.index_id = usg_stats.index_id
                                                    AND Ind.[OBJECT_ID] = usg_stats.[OBJECT_ID]  and usg_stats.database_id = DB_ID(''
                              + db_name() + '' )
                WHERE  Ind.type_desc <>'HEAP'
                and vi.[name] ='VProcessStatus'
                and Ind.[name]='IDX_VProcessStatus'

Verify script :

select * from dbo.VProcessStatus WITH (NOEXPAND) where processstatus='active'

More on How to determine if an Index is required or necessary


How to access Multiple DB2 Relational Databases In A Single Query

One way you can achieve this is via federation. In short words, if you want to access db_b from db_a, you catalog db_b in instance of db_a. You then create nicknames in db_a for the tables in db_b that you want to access. The nicknames can with some restrictions be treated as tables, so you can join a table in db_a with a table in db_b.


Which permissions are required to view Windows Event Logs in SSMS?

The two groups highlighted in the attached image (Administrators and Event Log Readers) will allow a user, or a member of a group, who is given access to either of these two local groups the ability to view the windows event log. Of course, as long as they are logged on via an AD account.


What is the best way to store X509 certificate in PostgreSQL database?

Duplication isn't ideal, but in this case is probably the best choice. Set the table permissions so that the table owner is not the operational day to day database user your app runs as, and only GRANT your app the ability to write to the certificate data column, not the "cache" columns with expiry etc. Have a SECURITY DEFINER trigger function intercept writes to the certificate field and as a privileged user update the indexed cache columns by using an X.509 library to extract the fields from the certificate after verifying it.

Alternately, you could write a PL/Python, PL/Perl, or even a C SQL function that calls an X.509 certificate parser library to extract fields and return them. So you'd say extract_x509_field(cert, 'subject'). Or perhaps even a row-returning form like SELECT subject, issuerName FROM (SELECT extract_x509_fields(cert) FROM the_table) where extract_x509_fields returns a row of all relevant cert data. With this approach you could create functional indexes like CREATE INDEX cert_issuer ON certificate_table( extract_x509_field(cert,'issuer') ); that could be used to match WHERE expressions. You wouldn't need to have table columns for the extracted data at all. The downside is that this would likely be slower as the cert would get parsed multiple times during index creation, during index re-checks, etc.

Either way, it's vital that your application operate as a PostgreSQL user that is not the database owner, not a superuser, and not the owner of the tables and indexes of concern. It should be GRANTed the minimum rights necessary and no more. If you have quite separate tasks (say, read-only vs write-and-update) consider using different database users for them so that even if the "read" part of your app is tricked into trying to write a field/update a cert/etc, it doesn't have permission to.


1 - Make sure Recovery Catalog DB is created either on a different server or a different disk (because in case of failure of target DB and lost of access to its files, if your recovery catalog db stills up and running, you'll have access to control file and probably be able to restore and recover failed instances, given all backups/ archivelogs are available.

2 - Follow the Recovery Catalog 3 step creation:
- Create the recovery catalog owner/schema, tablespace. (It's noteworthy that sys can't be the rec cat owner)
- create the recovery catalog
- register the target databases

3 - Regarding compatibility, in order to check recover catalog version, connect to the recovery catalog db through sqlplus and issue:
- SELECT * FROM rcver; (if there are multiple lines displayed, the highest will be the current catalog schema version)

4 - Configure control file autobackup to ON (this is always a best practice, with or without Reco Catalog)
- Don't use another reco catalog, or things can get really messed.
- Run reco catalog db in archivelog, because it will contain very important information about registered db, and you'll sure want recover if needed.

5 - Backup your Recovery Catalog and set proper retention policies to meet your requirements.

Hope it helps.


Update with CTE & OUTPUT blocks on concurrent calls

Many of the issues you see are being caused by an inefficient execution plan:

Not that the supplied plan and query matches the question, but even so, I'm working with what was provided.

Anyway, you should implement the Name column data type changes (from nvarchar(max)) that I mentioned in your previous question. More importantly, you need to add the indexes I recommended, so the row to update can be found without sorting:

CREATE NONCLUSTERED INDEX IX_dbo_Sizes__DiameterInches
ON dbo.Sizes (DiameterInches);

CREATE NONCLUSTERED INDEX IX_dbo_Widgets__SizeId_ColorCode_StatusId__Name
ON dbo.Widgets (SizeId, ColorCode, StatusId)
INCLUDE (Name);

You can then update the status of the selected item and return the row affected with:

DECLARE
    -- Constant values guessed, replace with the real ones
    @availableStatusId integer = 1,
    @soldOutStatusId integer= 9,
    @colorCode nvarchar(6) = N'Red';

WITH UpdateView AS
(
    SELECT TOP (1)
        W.Id,
        W.Name,
        W.StatusId,
        W.ColorCode,
        W.SizeId
    FROM dbo.Widgets AS W WITH (UPDLOCK, ROWLOCK, READPAST)
    JOIN dbo.Sizes AS S
        ON W.SizeId = S.Id
    WHERE
        W.StatusId = @availableStatusId
        AND W.ColorCode = @colorCode
    ORDER BY
        S.DiameterInches
)
UPDATE UpdateView
SET StatusId = @soldOutStatusId
OUTPUT
    Inserted.Id,
    Inserted.Name,
    Inserted.StatusId,
    Inserted.ColorCode,
    Inserted.SizeId;

Note the UPDLOCK, ROWLOCK, READPAST hints there. These are the standard hints needed for concurrent FIFO access to queue-type tables. See Using Tables as Queues by Remus Rusanu for more information.

The execution plan shape you should get (with no sorts!) is:

As an aside, unless you have very good reasons, your database should not be using SET AUTO_CLOSE ON.


Why am I getting error 35353 when enabling CDC on a table with a CCI?

I just heard back from Microsoft. The documentation on CCIs and CDC is incorrect/misleading. Change Data Capture is not supported for clustered columnstore indexes. It is supported for nonclustered columnstore indexes starting in SQL Server 2016. They are correcting the documentation.


What is the syntax for querying a spatial column?

I think you want MBRContains
I have an example from a previous post : Less restrictive query return less result due to simple removing one additional constraint
Give it a Try !!!
UPDATE 2013-05-08 17:05 EDT
You have this:
SELECT *
FROM tb_gps
WHERE MBRWITHIN(pt, MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605))

Put the MULTIPOINT in quotes
SELECT *
FROM tb_gps
WHERE MBRWITHIN(pt, 'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)')

I did this in my example
UPDATE 2013-05-08 17:39 EDT
If MULTIPOINT is not working for you, try using POLYGON
SELECT *
FROM tb_gps
WHERE
    IF(CONTAINS(GeoFromText('POLYGON(52.3641917981 4.87673850395,
    4.87673850395 4.90619949605,4.90619949605 52.3821782019,
    52.3821782019 52.3641917981)'pt),1,0)
;

You know something? It just dawned on me. You have the parameters backwards.
You have
SELECT *
FROM tb_gps
WHERE MBRWITHIN(pt,'MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)')

Try reversing the parameters:
SELECT *
FROM tb_gps
WHERE MBRWITHIN('MULTIPOINT(52.3641917981 4.87673850395, 52.3821782019 4.90619949605)',pt)

Give it a Try !!!


How to manipulate join order of PostgreSQL optimizer?

You can do that but it needs some trickery.

Imagine you got this:

select ... from a, b, c, d, e ...

...this type of query is always reordered.

But if you do:

select ... from a JOIN b ... JOIN c JOIN d JOIN c ...

...then PostgreSQL will only reorder join_collapse_limit tables. You can reduce this variable to a low value to force PostgreSQL into your order; however, I would try to avoid that.

This should only be locally using SET LOCAL, and you should first think, why you want the join order to be changed at all.

Related: Slow fulltext search due to wildly inaccurate row estimates


How to search for an ID and its descendants in this query?

How about something like this:

select id, idunit
from (select id as idunit from unit start with id=p_id connect by prior id = idparent)
     join project using(idunit)
where p_id is not null
union all
select id, idunit from project where p_id is null


From a set of FDs, you can only infer a set of candidate keys.

A "primary key" is one particular member of that set of candidate keys that you elect to be "primary".  And there are no scientific rules that tell you which one you should choose - you can pick just any key and label it "primary".

In your first example, you should end up with a set of candidate keys

{ {T G} {G X} }

In the second example, you should end up with a singleton set of candidate keys

{ {X Y} }

As for how to arrive at the set of keys, you can take a look at https://stackoverflow.com/questions/5735592/determine-keys-from-functional-dependencies/5736174#5736174


To promote, pg_ctl writes a file called $PGDATA/promote, sends SIGUSR1, then waits a default of 60 seconds for the server to acknowledge that it is now read-write. Does that file exist? What is in the server log file?


Why is it necessary to type "host" before the imp command when I'm using SQL*Plus to import a dmp file into Oracle?

imp is an operating system command, not an SQL*Plus command, so it should be run from the OS shell command line. If you insist on running it from SQL*Plus you need to use the SQL*Plus host command to send imp to the host OS for execution.


Inputs on handling large volumes of data for specific tables in SQL Server standard

Use an archive table and a live table.

Don't use a "table per year": this quickly becomes messy and you'll end up needing dynamic SQL when you UNION + view becomes a bottleneck

10 million rows isn't a lot though, and 30 columns is quite wide. So, some options:

review data types to reduce data in disk.

Do you need nvarchar?
Can you use smalldatetime?
Do you have int where tinyint will do?
(n)varchar when you have fixed length data?

do you have regular index and statistics maintenance?
do you have incorrect or missing indexes?
have you looked at most expensive queries?


Months later I stumbled across the effective solution to what I was seeking in my question.

From the docs itself there are two variables to help prevent the trashing of the buffer pool for large table scans such that would occur during a checksum or mysqldump.

The first is innodb_old_blocks_pct (default 37%).  This the sublist of pages that are eligible for eviction when a new page is inserted.  When a new page is first read it is added to the head of this list.

innodb_old_blocks_time (default 0) defines the number of milliseconds after being added must elapse before an access that cause it to move out of the old list to the head of the new list.  Since the default is 0 the first access puts it to the head of the new list.

So in short set innodb_old_blocks_time to a nonzero value, say 500, and then a table scan will basically just cause the old sublist to continually get cycled through.  Pages getting read for other purposes will remain in the new list allowing your DB to remain warmed with real application relevant data!


Deploy materialized view with database link using another user

Here is the solution I am using for now.  I don't like it, but until I find something better, it's the best I can do.

At the start of the deployment a public database link with named credentials is created that uses the same name as the private database link will use.
The materialized view is created and works because it is using the public link with embedded credentials.
A procedure is created owned by "Another User" (AU) that uses an execute immediate to create the private database link.
The procedure is run to create the private database link.
The procedure is dropped.
The public database link is dropped.

Unfortunately this requires the swapping the existence of both the public and private database links before and after any deployment that creates materialized views using links.  The temporary creation of a public database link with embedded credentials is also a security risk and is in fact a problem we are trying to solve.


Mariadb json store with unicode \u sequences

\u0395 is just 6 characters to MariaDB.  Searching for the 5-character "word" u0395 would succeed.
You need to change the JSON strings to have characters, not unicode codepoint encoding.
In PHP, I would consider write a 1-time script to bring in each line, decode it, and write it back out.
Meanwhile, the writing should be changed to
json_encode($stuff, JSON_UNESCAPED_UNICODE)

(or the equivalent in your app's language).
IntlChar::chr may be part of the solution.
Or:  $utf8string = html_entity_decode(preg_replace("/U\+([0-9A-F]{4})/", "&#x\\1;", $string), ENT_NOQUOTES, 'UTF-8');
Or:  https://stackoverflow.com/questions/6058394/unicode-character-in-php-string
Or:  https://stackoverflow.com/questions/2934563/how-to-decode-unicode-escape-sequences-like-u00ed-to-proper-utf-8-encoded-cha


ORACLE_HOME / ORACLE_SID variable - why do we need to export them ?

This is really a Unix/Linux question, but it does relate to Oracle, so I'll give an answer.

In Unix, the system makes great use of what it calls "spawning" (or "giving birth"). When you run a command, a new process is created which takes all of its information from the parent. Essentially, it's a copy of the parent - it's a new bash shell, which then sets about performing the task given to it by its parent.

When that child process completes, it returns a code to the parent (success or failure) - AFAICR, - 0 is success, failure is anything else.

So, the new bash process spawned by your shell (aka "the child") takes its variables from your original (i.e. the spawning or parent) bash shell. If you don't tell the new SQLPlus shell where to find the SQLPlus executable, then it will throw an error.

Unusually for Linux/Unix and Oracle, the error message is actually informative. This is not always the case! :-). If you want a good laugh about Unix, read this. In fairness to Linux/Unix, they've come a long way since that document was written, but it's still worth a read.

So, unless you "export" - i.e. feed the values of your environment to all of the child processes - your system will misbehave.

Take a look at Andrew Tanenbaum's book - or any good text on modern Unixen (or *nix). There's plenty of info out there - just Google Unix and processes - you'll get a truckload of stuff.

@StringerBell makes a good point. In one job I worked in, we had 4 test instances on the one machine. We'd log in and then go to our desired instance and run oraenv.blue, oraenv.red... There are pros and cons - it was easy enough to forget which instance one was on - it's maybe a good idea to change the prompt as well - to give DBAs a fighting chance :-)


PostgreSQL JSON field performance with large documents

In PostgreSQL 9.3, performance will be fairly poor for large json documents. The whole document must be parsed in order to access a field, index an array, etc. It's stored on disk as a simple text representation, the json text.

PostgreSQL 9.4 is likely to change this, with support for jsonb storage on disk. It'll still have to read the whole json object from disk to access any part of it, but it won't have the same kinds of parsing overheads you'll see in 9.3. It'll also gain many of the indexing features of hstore.

In 9.3, I strongly suspect that hstore, which is stored in a compact binary representation on disk and supports a variety of indexing features, is much more appropriate for your needs.


How to transfer Right-to-Left text from Excel to SQL Server?

There are two ways to perform this:

Right click on Database -> Task -> Import Data -> Select Excel as your data source ->     continue until the wizard is finished .
The longgest way:

In Excel add the following Columns before your data as follow:

Copy and paste your data set in a new query on SQL Server.

Use Ctrl + H to replace the blank spots like follow:

format it as you like and you're good to go!


It's hard to debug without seeing the output of your 2nd @SQL or @cols.
I'd start from there to debug, SELECT/PRINT the variable and copy-and-paste-run-it
or try different way of cols?
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

FYI this is my PIVOT query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(Date)
FROM (SELECT DISTINCT [Date] FROM #Final) AS Weeks

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery = '
select *
from
(
  select SQLInstanceName, Date, node_name
  from #Final
  WHERE 1=1 AND  Has_Changed = ''Y''
) src
pivot
(
  MIN(node_name)
  for [Date] in ('  + @ColumnName + ')
) piv
order by 1
'
--SELECT @DynamicPivotQuery --debug
EXEC sp_executesql @DynamicPivotQuery


Don't try to ask the result of dump() if there is a newline character in the string.  Ask the string directly.

Example where clause

where f1 like '%' || chr(10) || '%'

or

where instr(f1,chr(10)) > 0

or (Per John A)

where regexp_like( f1, chr(10), 'n' )


Optimizing numeric range (interval) searches in SQL Server

Columnstore is very heplful here compared to a nonclustered index which scans half the table. A nonclustered columnstore index provides most of the benefit but inserting ordered data into a clustered columnstore index is even better.

DROP TABLE IF EXISTS dbo.MyTableCCI;

CREATE TABLE dbo.MyTableCCI
(
Id        INT PRIMARY KEY,
RangeFrom INT NOT NULL,
RangeTo   INT NOT NULL,
CHECK (RangeTo > RangeFrom),
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.MyTableCCI
SELECT TOP (987654321) *
FROM dbo.MyTable
ORDER BY RangeFrom ASC
OPTION (MAXDOP 1);

By design I can get rowgroup elimination on the RangeFrom column which will eliminate half of my rowgroups. But due to the nature of the data I also get rowgroup elimination on the RangeTo column as well:

Table 'MyTableCCI'. Segment reads 1, segment skipped 9.

For larger tables with more variable data there are different ways to load the data to guarantee the best possible rowgroup elimination on both columns. For your data in particular, the query takes 1 ms.


Steps to determine why NT AUTHORITY\SYSTEM has permission on one database, but not another

Unless NT Auth\System has server level access that covers all of the databases (for example sysadmin (not recommended)) then why would you think it should have access to all of the databases?

Permissions for the most part are handled on a database by database level.  You can look at it through the GUI (security dropdown under the DB in the object explorer among other places) or there are lots of queries out there that can help you.

I've got two

sp_dbpermissions
sp_srvpermissions

that will help you list out the permissions for a given user/db/etc.  Once you have determined what permissions the ID SHOULD have for a given database it's just a matter of adding the user to the db (CREATE USER) and granting them the permission using the GRANT command.


Function access query/queries in Postgres

Sounds to me as if you are looking for:

select *
from information_schema.routine_privileges
where grantee = 'arthur';

https://www.postgresql.org/docs/current/static/infoschema-routine-privileges.html


What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

Here's a thought on archiving, with transactional integrity.

Have another table; let's call it archive.  It needs few, if any indexes.
Move 1000 rows from your table to archive at a time, transactionally.
After you have moved "enough". do whatever you want to Archive archive.

See this for suggestions on efficiently DELETEing rows from a table.  The rest is

INSERT INTO `archive` SELECT ... FROM main ...;

where the ... come from the chunking.  That can probably be done with autocommit=on and not need any BEGIN...COMMIT.


How do you restore a DB to Multi-User mode when deadlocked by a sys process

Apart from what Shawan referenced, another option is to use DAC ADMIN:ServerName
then issue below command (note that I am not using NO_WAIT)
ALTER DATABASE database SET MULTI_USER with rollback immediate

From BOL :

ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE
Specifies whether to roll back after the specified number of seconds or immediately.
NO_WAIT
Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.


Oracleasm deletedisk failed Unable to clear disk

First of all, I checked that there were no extents on DISK1.

$ . oraenv
ORACLE_SID = [+ASM1] ?

SQL> select PXN_KFFXP,  -- physical extent number
           XNUM_KFFXP, -- virtual extent number
           DISK_KFFXP, -- disk number
           AU_KFFXP    -- allocation unit number
    from X$KFFXP
    where DISK_KFFXP=1;

no rows selected

I cleared the disk header by dd:

# dd if=/dev/zero of=/dev/oracleasm/disks/DISK1 bs=1024 count=100
100+0 records in
100+0 records out
102400 bytes (102 kB) copied, 0.000156 seconds, 656 MB/s

Note: My allocation unit is 1MB that is why I am using bs=1024.

Then deleted disk by oracleasm:

# oracleasm deletedisk DISK1
# oracleasm scandisks    <--Do this step on all nodes

To tell the truth it is not a good way to solve the problem, because when I run the following command on the second node:

/sbin/blkid | grep oracleasm

/dev/sdg1: LABEL="DISK6" TYPE="oracleasm"
/dev/sdb1: LABEL="DISK1" TYPE="oracleasm"
/dev/sdh2: LABEL="DISK8" TYPE="oracleasm"
/dev/sdh3: LABEL="DISK9" TYPE="oracleasm"
/dev/sdh4: LABEL="DISK10" TYPE="oracleasm"
/dev/sdh1: LABEL="DISK7" TYPE="oracleasm"
/dev/sdi1: LABEL="DISK11" TYPE="oracleasm"
/dev/sdi2: LABEL="DISK12" TYPE="oracleasm"

It shows me the label DISK1.

So clearing the ASM disk header using dd left some junks.


Have you tried setting up the distributor transaction coordinator?

Distributed transactions are transactions that require SQL Server to get data from a source outside of the instance of SQL Server you are using (this most frequently entails the use of Linked Servers et al.

An example of a distributed transaction would be for example:

INSERT MyTable (Col1, Col2, Col3....)
SELECT Col1, Col2, Col3 ... FROM OPENQUERY(MyLinkedServer, 'SELECT Col1, Col2, Col3 ...
FROM RemoteTable
WHERE predicate1 = Condition1')
INNER JOIN Table2 ON Table2.ColX = Col1

to set up the distributed transaction coordinator follow the steps below:

Now you click apply, it will ask for restarting the service and you say yes.

this article is also worth reading:

Do I need DTC for my SQL Server?


Is it possible to force drop of mysql tables with FK?

While ON DELETE CASCADE can be attempted, it is worth noting what the output of a mysqldump looks like. At the top of every mysqldump you will see directives which disable foreign key checks and unique key checks:

-- MySQL dump 10.13  Distrib 5.5.12, for Win64 (x86)
--
-- Host: localhost    Database: sample
-- ------------------------------------------------------
-- Server version       5.5.12-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Running a reload of a mysqldump gives the DB Session the opportunity to disable those checks, load the table, then enable the checks again. In view of this, you should manually truncate all the tables in question with those two directives.

Suppose you have table test with foreign keys references in t1,t2,t3,t4,t5

Simply truncate then in order starting with child tables as follows:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
TRUNCATE TABLE t5;
TRUNCATE TABLE t4;
TRUNCATE TABLE t3;
TRUNCATE TABLE t2;
TRUNCATE TABLE t1;
TRUNCATE TABLE test;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

UPDATE 2018-07-05 09:33 EDT

I never circled back to this question to see the OP clarification, so let me complete this answer. What I should have posted 5 years ago was the following:

SET FOREIGN_KEY_CHECKS = 0;
SET UNIQUE_CHECKS = 0;
DROP TABLE t5;
DROP TABLE t4;
DROP TABLE t3;
DROP TABLE t2;
DROP TABLE t1;
DROP TABLE test;
SET FOREIGN_KEY_CHECKS = 1;
SET UNIQUE_CHECKS = 1;

Give it a Try !!!


Here's an idea you could build on to achieve this: create a simple table that contains only a list of integers from 1 to 24*60 (i.e. number of minutes in a day). Use this to generate the list of minutes in the day you're interested in. Then join that on your session data, grouping by "minute" to get a count of active sessions.

Probably better explained with an example:

Tables:

-- Holds a list of integers
create table minutes (m int) engine innodb;
-- The session data
create table sessions (id int,
                       st timestamp,
                       en timestamp) engine innodb;

Generate the list of "minutes"

delimiter ;;
create procedure fill_minutes()
begin
  declare i int default 0;
  while i < 60*24 do
    insert into minutes(m) values (i);
    set i = i + 1;
  end while;
end;;
delimiter ;

start transaction;
call fill_minutes();
commit;

Adapted from Generating a range of numbers in MySQL

To generate the minutes in a given day:

select
    date('2013-01-01 00:00:00') + interval m minute
from
    minutes;

2013-01-01 00:00:00
2013-01-01 00:01:00
2013-01-01 00:02:00
2013-01-01 00:03:00
2013-01-01 00:04:00
2013-01-01 00:05:00
....

A bit of fake data:

insert into sessions (id, st, en)
values (1, '2013-01-01 00:00:00', '2013-01-01 00:10:00')
,      (2, '2013-01-01 00:05:00', '2013-01-01 00:10:00')
,      (3, '2013-01-01 00:07:00', '2013-01-01 00:12:00');

Join the minute table with the session data

select
    dates.d, count(*)
from
    (select date('2013-01-01 00:00:00') + interval m minute d from minutes) dates
    join sessions s on ((dates.d >= s.st) and (dates.d < s.en))
group by dates.d
order by dates.d;

d                   count(*)
2013-01-01 00:00:00 1
2013-01-01 00:01:00 1
2013-01-01 00:02:00 1
2013-01-01 00:03:00 1
2013-01-01 00:04:00 1
2013-01-01 00:05:00 2
2013-01-01 00:06:00 2
2013-01-01 00:07:00 3
2013-01-01 00:08:00 3
2013-01-01 00:09:00 3
2013-01-01 00:10:00 1
2013-01-01 00:11:00 1

Note:

I don't know how to avoid that "list of minutes" table in MySQL. Doable in Oracle with a select ... from dual connect by level <= ... or similar.
You'll probably need to restrict the join on sessions more to avoid a full scan of that table (limiting the start date at least, start and end sounds better)


This is MUCH more simply done this way:

cd to mysql folders:

mkfifo mysql-general.log

in my.cnf tell it:

[mysqld]
general-log-file = /path/to/mysql/dir/mysql-general.log

Then, configure your syslog/syslog-ng to read the FIFO pipe and do with it as it will..

In my case, I pipe it across net to a centralized server with only the error logs and slow query logs however.

In situations where you want to also keep local copy, just set it to output to table and file as described above.


What is the optimal response time for file growth

First of all you want to make sure that your database is sized appropriately up front.  Usually I like to plan for 6 months to a years worth of growth.  That being said your data file(s) should be the only ones growing on a regular/irregular basis.  Your log file once it hits a certain point should not grow much if at all, assuming you are taking regular backups.  Given that, you can use a server setting called Instant file initialization that will make your database growth very very quick, regardless of how much space you are adding.

Here is a link you can read to start you on it.  http://msdn.microsoft.com/en-us/library/ms175935(v=sql.105).aspx

If you have this turned on then you can afford to set your auto growth to larger sizes so that you don't have to grow as often.


Access Oracle GTT table data from another session?

No that is not possible, only session accessing the GTT can see it. IF you wish, you can use a physical table and model it as if it s a GTT. It isn't difficult, but isn't very efficient either.


What do you mean by "sync"?  rsync?  copy?

Do you shutdown mysqld while doing the sync?

What is the value of sync_binlog?

The problem is that mysqld will be writing to the binlog at random times, and your sync has a chance of copying only half of a 'record'.

Your testing probably did not encounter the potential problem because the writes will often be 'atomic'.

Suggest you shutdown mysqld for the duration of the sync.  Even better would be to have an LVM snapshot; this would allow for a much shorter shutdown.

Plan B

Do FLUSH LOGS;.  That will 'rotate' the binlogs -- close the current binlog and start a new binlog with the next higher number.  Then copy, by any convenient means, the flushed binlog (and any previous ones as needed).  This should be totally safe.  It will capture the replication stream up to the point of the FLUSH LOGS.

The only minor tricky part is knowing how many binlogs to copy and what their names are.


The problem can also be attributed to a crashed process that left postmaster.pid file behind.
$ brew services stop postgresql
# adjust path accordingly to your install, e.g. `/opt/homebrew/var/postgres/postmaster.pid`
$ rm /usr/local/var/postgres/postmaster.pid
$ brew services start postgresql


Trick for AUTO_INCREMENT on composite keys in InnoDB

My solution is in migrating from myisam to innodb.  It goes something like:

  BEGIN;
  SELECT @id := IFNULL(MAX(id),0) + 1 FROM foo WHERE other = 123 FOR UPDATE;
  INSERT INTO foo
     (other, id, ...)
     VALUES
     (123, @id, ...);
  COMMIT;

Having a transaction is mandatory to prevent another thread from grabbing the same id.


Your interpretation matches the "How granular are locks in MongoDB?" part in MongoDB FAQ:

  How granular are locks in MongoDB?

  Beginning with version 2.2, MongoDB implements locks on a per-database
  basis for most read and write operations. Some global operations,
  typically short lived operations involving multiple databases, still
  require a global “instance” wide lock. Before 2.2, there is only one
  “global” lock per mongod instance.

  For example, if you have six databases and one takes a write lock, the
  other five are still available for read and write.

As for the way the locking works:

  What type of locking does MongoDB use?

  MongoDB uses a readers-writer
  [ed: also known as “multi-reader” or “shared exclusive”] lock that allows concurrent
  reads access to a database but gives exclusive access to a single write
  operation.

  When a read lock exists, many read operations may use this lock.
  However, when a write lock exists, a single write operation holds the
  lock exclusively, and no other read or write operations may share the
  lock.

  Locks are “writer greedy,” which means writes have preference over
  reads. When both a read and write are waiting for a lock, MongoDB
  grants the lock to the write.


Migration MySQL to Cloud Environment replication

we already done the master & master replication in locally , so can any one recommended to which cloud is best for my query like (AWS , Openstack,SoftlayerCloud ) on it.


Service Broker "Broker Enabled" option goes from true to false when performing backup/restore of DB

Can any option be set when performing either the BACKUP or RESTORE command that retains this database option?

Edit: you can restore the database with ENABLE_BROKER. Check the sample below:
RESTORE DATABASE [WideWorldImportersA]
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\WideWorldImporters-Full.bak'
WITH  FILE = 1,
MOVE N'WWI_Primary' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SB\WideWorldImporters.mdf'
,  MOVE N'WWI_UserData' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SB\WideWorldImporters_UserData.ndf'
,  MOVE N'WWI_InMemory_Data_1' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SB\WideWorldImporters_InMemory_Data_1'
,  MOVE N'WWI_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\SB\WideWorldImporters.ldf'
,  STATS = 5, ENABLE_BROKER
GO


Can we create a table in specific datafile - Oracle 11g R2?

You can create two tablespaces, put all the data files for one tablespace on one drive and all the data files for the other tablespace on the other side, and then create the tables in whichever tablespace you would like.  It seems unlikely, however, that you would really want to do this.  It is very unusual to run a database on a server without any sort of RAID configuration.  And it is generally the case that any automatic I/O load balancing that you would implement would outperform your manual load balancing.


Has the guidance on the setting for 'cost threshold for parallelism' changed with the advent of columnstore indexes?

Beyond the Cost Threshold setting, SQL Server appears to treat parallelism differently for columnstore indexes depending on your SQL Server version (2012 vs 2014) and even the datatypes in your table.

I'd start with Joe Chang's post benchmarking decimal vs float datatypes, and read the comments on that post as well. If you want to get exactly the right MAXDOP and Cost Threshold for Parallelism settings for your system, you'll need to perform the level of detailed testing that Joe does in his post, and that takes a lot of work. Because of that, I would focus on your system's primary bottleneck first - use wait stats to make sure parallelism or CPU pressure are problems for you, and then start by tuning the most CPU-intensive queries rather than making system settings changes.


Optimising a query to get rows where x is between pairs of values from another table

I'd suggest using a CTE in this instance. Something that looks like this:

with exons (genename, exonnumber, crom, exonstart, exonend, padding, direction) as
    (select genename, exonnumber, crom, exonstart, exonend, padding, direction
     from exons
     where enename = 'NM_001037501')
select *
from vcfentries vcf
join exons exo
using (chrom)
where vcf.pos between exo.exonstart and exo.exonend;


Upgrade/Transition Path from MySQL Community 5.6.31 to Enterprise

Upgrade to 5.7.  Follow all the steps there, including testing mysql_upgrade.
Test your code against 5.7.
Then embark on upgrading from 5.7 to 8.0; it's more complex but still doable.


Is it possible to know how many times a user connected to an Oracle database?

If audit is enabled, you can check DBA_AUDIT_SESSION/USER_AUDIT_SESSION (More info about auditing - Audit doc). Another option is to check dba_hist_active_sess_history - you will need to write a query that counts distinct serials/sessions to get rough number of user logins assuming user did anything during the session. Also, you won't be able to go too far in the past with dba_hist_active_sess_history - it's a view based on tables populated by AWR which automatically deletes old records (as far as I remember default retention period is 1 week ).


How to arrange rows selected in MYSQL query?

You are looking for ORDER BY FIELD(), and it looks like this:

SELECT
  PK_ONE
from
  TABLE_A
where
  PK_ONE in (2,88,3999,4,282,33399,1,394)
ORDER BY
  FIELD (PK_ONE, 2,88,3999,4,282,33399,1,394)

FIELD() is a function which returns the position of an element (first argument) in a list of values (2nd, 3rd, ... arguments).

So you need to generate/template the list of values twice for this query.


How do I import a specific line from a text file into MySQL

If you are using Unix/Linux you could try using a fifo buffer:

mkfifo /tmp/mysql.pipe
# Select rows 101 to 110
sed -n 101,110p filename > /tmp/mysql.pipe &
mysql –uusername -p -Ddatabasename -e "LOAD DATA LOCAL INFILE ' /tmp/mysql.pipe ' INTO TABLE tablename"

rm -f /tmp/mysql.pipe


Master-Slave semi circle replication not working in MySQL

I think the problem is the following:

Since B is a Slave of A, replication log position changes when something on A is executed and then records the changes to its binlogs. Server B picks up Server A binlog events and executes it.

Why won't Server B just pass it on to Server C ?

Server B need the following option enabled

[mysqld]
log_slave_updates

Whenever you want a server to be a Slave and a Master at the same time, you need log_slave_updates enabled. Here is what the MySQL Documentation says:

  Whether updates received by a slave server from a master server should be logged to the slave's own binary log. Binary logging must be enabled on the slave for this variable to have any effect.

In your case, Server B reads Server A's binlog events as recorded in Server B's relay logs, executes those events in Server B and nothing more.

When you enable log_slave_updates on Server B, then the following happens

Server B reads Server A's binlog events as recorded in Server B's relay logs
Server B executes those events in Server B
Server B records the event it executes in Server B's binlogs
Server C will receive Server B's binlogs and execute them

I mentioned using log_slave_updates a couple of times before:

Sep 09, 2014 : Is it mandatory to enable --log_slave_updates with MySQL Replication using GTID?
Nov 26, 2012 : Replicate MySQL 5.0 Master to 5.5 Slave?

Therefore, when you said

  It is because of this reason Server C doesn't know if there has been any change on Server B unless I explicitly change something (insert,update,delete) on Server B

The explicitly change something becomes implicit when you use log_slave_updates.


Non-priviledge way to restrict UPD/DEL to user knowing certificate pass

Is there a way to restrict a column to only accept updates or deletes if a specific key is open?

No, a sysadmin or someone with sufficient permissions could change the data regardless of encryption.

Should the question or investigation change to be something where it doesn't matter if the data could change but that it was encrypted or otherwise unavailable to a sysadmin or someone with sufficient permissions, then yes that is possible but would require the keys to be stored outside the database system or for secret passwords to be known (less secure).


This is a common error. Either you screwed up the 1:M relationship or you are trying to enter data into multiple tables incorrectly

From Microsoft's Developer Network MSDN :In a one-to-many relationship, you entered data on the "many" side for which there is no matching record on the "one" side. For example, this error occurs if you join a Customers table and Orders table on a CustomerID field, and then add an order using a CustomerID that does not exist in the Customers table.
From Microsoft Answers: It sounds like you are trying to enter data into multiple tables using one main form. That's not going to work. You NEED to be using subforms. Create your main form bound to the Members table and then subforms to each of the child tables. Use the Form wizard to create your forms.

My guess from your comments is #2. You added a new table but didn't recreate the form using subforms.


Connection Timeout Expired without apparent network issue

This was eventually identified as a side-effect of VMWare LRO.  Disabling host-based LRO resolved the issue.  See

Enable or Disable LRO on a VMXNET3 Adapter on a Windows Virtual Machine
Large Receive Offload
Poor network performance or high network latency on Windows virtual machines (2008925)
vmxnet3 adapter on windows server 2012 with MSSQL server bottleneck problem


This question is more about finding a "way" to figure out the waiting resources (using sp_WhoIsActive or similar) than actually fixing this current performance issue

If you're looking for what these numbers mean then, check out the sp_whoisactive Documentation.

From Adam Machanic's the page on the CXPACKET node identifier, you can see that this line in your output:

  (5x: 68/168850/635541ms)CXPACKET:15
  (3x: 3/16/32ms)CXPACKET:7
  (1x: 10207374ms)CXPACKET:1

Means that there are

5 tasks waiting on CXPACKET in node 15 of the execution plan
3 tasks waiting on CXPACKET in node 7 of the execution plan
5 tasks waiting on CXPACKET in node 1 of the execution plan

The 3 numbers separated by slashes are documented here:

  (Nx: MINms/AVGms/MAXms)[wait_type] where Nx is the number of tasks, MINms is the shortest wait duration of the tasks, AVGms is the average wait duration of the tasks, and MAXms is the longest wait duration of the tasks

The output of @get_task_info = 2 is telling you

where exactly in the execution plan your query is getting stuck
how many tasks are getting stuck there, and
how long they're stuck there (min, max, and on average)

Hopefully this provides enough information for you to be able to find which part of your query is getting stuck on these parallelism waits so you can investigate further.


Yes, CDC will work with SQL Server Always ON availability groups. Check Change Data Capture.

There is already an answer here - Does Change Data Capture (CDC) work in a SQL Server 2012 AlwaysOn Failover Cluster Instances setup?


One easy way to do this is to create a SQL Agent job with a step that runs the following code:

<Your SELECT statement>
IF (@@ROWCOUNT > 0)
    BEGIN
        RAISERROR('Long-running query detected!', 11, 1 )
    END

Configure notification on the job to send an email on failure, and if any rows are returned by the query, you'll get an email that the job failed.

Instead of setting up the job failure notification, you could set up a SQL Agent alert on the error that is raised as in the article you referenced.  But if the job notification is adequate it's less work to set up.


SQL keyword difference between MySQL and Oracle 10g?

There are big differences between the two. The datatypes are different (e.g. Oracle doesn't have the ENGINE part, int(5) is invalid for Oracle, LONGTEXT is invalid for Oracle and so on) and part of the syntax as well. Oracle does not have an "autoincrement" column. It has a different concept using sequences.

If you want to find all the differences you will have to read the manuals and compare for yourself

If MySQL Workbench emits only MySQL syntax you will need some manual search & replace to convert that to valid Oracle SQL but it is certainly doable.


How to access oracle 11g database remotely

Presuming port forwarding and network items are working as expected (can be checked with telnet host 1521).

Make sure that you restart the listener service.

Also connect to the database from your local machine and run the following commands:

ALTER SYSTEM SET LOCAL_LISTENER = "(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.5)(PORT=1521))";

ALTER SYSTEM REGISTER;

Outside of that it looks good from a listener config side.


How long does a failover for SQL Server Availability Groups take?

Personal observation from experience with both FCI and AG failovers, with reasonable high volume transactional system (40k trx/sec). For each consider 6 databases ranging in size from 500MB to 4TB in size. Times listed for failover are what it takes for the database to up and in a writeable state on the new node. Your mileage can, and will vary, but this is at least a data point for you.

Cluster failover: 47 seconds (avg)

AG Failover: 10 seconds


Explaining how to design a database to model the stocks of a firm to my Grand'ma

You might find this page interesting. Your schema seems to be reasonable - the only way you'll know is by writing your application and seeing if it works :-).

What you initially call a "bon de commande", you later refer to as a "bon de livraison" (which makes more sense in French). You also fail to provide a definition of ContenuBL (La composistion d'un bon de livraison je présume?).

Most of the people on this forum won't be able to speak/read French and will find it difficult to answer the question. If you like, I can translate it into English for you?


Insert PBKDF2 hash into PostgreSQL container

Because the variables are replaced (with nothing) in bash, the dollars signs need to be escaped:

'PBKDF2\$sha256\$901\$x8mf3JIFTUFU9C23\$ic9J6mFDSz0rETKux/Co9TzCPOX50j+V8Vky5GkY5v0='


Windows Login can't access SQL Server even with SYSADMIN role

Create a regular account that goes in a group that has sysadmin on the database. Then restart your SQL Server management studio and log on using windows authentication. You can start SSMS using the "Run-as" to log on with a different account than you used to log onto your desktop

This gives you Separation of responsibilities between domain admins and database administrators.


The idea that an application should be written with just standard SQL comes from application developers who think that changing a DBMS is a worthy design goal. I -- and many other DBAs with me -- don't agree.

While it holds true for basic features, your application is going to perform best if it uses the features that your particular DBMS provides. This almost always means using DBMS-specific versions of SQL and DBMS-specific features.

Your employer/client has paid good money for a license for their DBMS, and often gone through a selection process to do so. Not using the DBMS's features is a waste of money.

I like to say it's like buying a Ferrari and always sticking to the speed limit...

On the other hand, if you're just learning SQL, and T-SQL just happens to be the dialect you have at your disposal, then, yes, try to learn standard SQL as your knowledge will be more broadly applicable. But don't discount learning your particular DBMS's features if you are in a commercial (or non-academic) environment.


View foreign key constraints so I can delete a table

And here's how to generate the script @Shark showed for all the tables you want to drop. Let's say you have the following tables:

USE tempdb;
GO

CREATE TABLE dbo.z(z INT PRIMARY KEY); -- we won't delete this one

CREATE TABLE dbo.a
(
 a INT PRIMARY KEY FOREIGN KEY REFERENCES dbo.z(z)
);

CREATE TABLE dbo.b
(
 b INT PRIMARY KEY,
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

CREATE TABLE dbo.c
(
 c INT PRIMARY KEY,
 b INT FOREIGN KEY REFERENCES dbo.b(b),
 a INT FOREIGN KEY REFERENCES dbo.a(a)
);

-- we won't drop this table either, but we'll need to drop
-- the constraint:

CREATE TABLE dbo.d
(
 d INT,
 c INT FOREIGN KEY REFERENCES dbo.c(c)
);

But we only want to delete a, b, and c.

-- load the tables you want to delete into a table variable:

DECLARE @tables_to_delete TABLE (t NVARCHAR(512));

INSERT @tables_to_delete VALUES('dbo.a'),('dbo.b'),('dbo.c');

DECLARE @sql NVARCHAR(MAX) = N'';

-- build a list of the foreign keys you'll have to drop first:

SELECT @sql += CHAR(13) + CHAR(10) + N'ALTER TABLE '
    + QUOTENAME(OBJECT_SCHEMA_NAME(f.parent_object_id))
    + '.' + QUOTENAME(OBJECT_NAME(f.parent_object_id))
    + ' DROP CONSTRAINT ' + QUOTENAME(f.name) + ';'
FROM sys.foreign_keys AS f
INNER JOIN @tables_to_delete AS t
ON f.referenced_object_id = OBJECT_ID(t.t);

-- then the DROP TABLE commands:

SELECT @sql += CHAR(13) + CHAR(10) + N'DROP TABLE '
    + t + ';'
FROM @tables_to_delete;

PRINT @sql;
-- EXEC sp_executesql @sql;

Result (the constraint names will look different if you run this):

ALTER TABLE [dbo].[b] DROP CONSTRAINT [FK__b__a__2D27B809];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__a__30F848ED];
ALTER TABLE [dbo].[c] DROP CONSTRAINT [FK__c__b__300424B4];
ALTER TABLE [dbo].[d] DROP CONSTRAINT [FK__d__c__32E0915F];
DROP TABLE dbo.a;
DROP TABLE dbo.b;
DROP TABLE dbo.c;

When you're happy about the result, uncomment the EXEC line.

(Note, you won't be able to validate the script in its entirety when using PRINT if the script is very large. The script is truncated by Management Studio because it still has an archaic limit to how many characters it will show. The string won't be truncated like this when it gets passed to sp_executesql.)


Searching for a string in multiple fields in a table

If you are not using any wild cards then can use =

Starting a like with % kills the use of an index
Not good

Just stay with

SELECT *
  FROM sometbl
 WHERE FIELD0 = 'zap'
    OR FIELD1 = 'zap'

Can use variable to only set once
Can use in

declare @val varchar(200)  = 'message/rfc822'

select sID, mimeType, caseID
  from docSVsys
 where mimetype = @val
    or caseID   = @val;

select sID, mimeType, caseID
  from docSVsys
 where @val in (mimetype, caseID)


MySQL: Does the order of the fields in a table make a difference in performance?

I would venture to say no up front in terms of the table. However, the order of fields in an index and the number of fields in a WHERE matter a great deal.

Example : You have the following table :

CREATE TABLE testtable
(
    a INT,
    b INT,
    c INT,
    KEY (a,b,c)
);

Note this query :

SELECT * FROM testtable WHERE a=1 AND b=2 AND c=3;

Since every column in the WHERE clause is mentioned as a eq_ref, aka equa-reference (using =), the index can be used to zero in on one column.

Note this query :

SELECT * FROM testtable WHERE a=1 AND b>2 AND c=3;

Column a is in front of the index as an eq_ref, but Column b is not. This is a range query. Depending on the cardinality of Column a (cardinality visible in SHOW INDEXES FROM testtable), an index scan if cardinality of a=1 is very low and the total number of rows with Column a=1 is less than 5% of the number of rows in testtable, otherwise a full table scan is chosen by any Query Optimizer (MySQL, Oracle, PostgreSQL, SQL Server, etc).

Note this query :

SELECT * FROM testtable WHERE b=>2 AND c=3;

This WHERE clause never mentions Column a. Result? automatic full table scan.

As far as order of columns in a table goes, defragging tables and making table formats with fixed row lengths could reduce any possible issues with table column order is that is a suspected concern.

If anyone knows of issues with Oracle, PostgreSQL, SQL Server, or other RDBMS's concerning table column order, please chime in.