Transaction isolation level/design for write-only ledger

Be sure to have FOR UPDATE on the end of the SELECTs.  Else another connection could sneak in and change the results of the SELECTs.

Also, steps 1,2,3 need to be inside a single TRANSACTION.

Once you do all that, you can probably leave the isolation level at the default.


Oracle XE 18c connects with sqlplus, but not DB GUI apps

The listener is not running.

As the Oracle user (which may be called oracle or oraclexe) run:

lsnrctl start

... to start the Oracle listener.


Recover raw Databases from corrupted MySQL 8 + Windows Server Implementation

Well, after three days of fighting with this, I have found a means of recovering at least some of the data:

With a lot of searching, I was able to locate an old virtual machine in the archives that still had a working copy of MySQL (5.x) that contained a version of one of the databases that I need to recover. From that resource I was able to generate all of the appropriate CREATE statements for all of the associated Schemas and Tables. So then, armed with my fresh server, and a new installation of MySQL, and all of the CREATE statements, I was finally able to regenerate all of the schemas and (empty) tables that this customer uses. So, upon shutting down the MySQL server service, and copying only the specific directories for that customer's databases into the new Data folder, then re-starting the service, the databases were, miraculously, fully restored!

IDK if it matters or not, but all of the schemas this particular customer had were MyISAM tables; I haven't yet tried this technique with any InnoDB tables, but this, at least, is progress, of a sort! Also, I don't know that I'll be able to find legacy functioning VM's for all of the schemas that I need to recover (and that are up to date/accurate!) so there's still a lot of work ahead...

I find it stunning that there's no simple tool for analysing a backed-up raw MySQL Data folder and then integrating the contents thereof onto an active server. You'd think that such a tool would have tremendous value for innumerable people in situations like this!?

Anyway, back into the archives!


If I add an additional column to handle special values, how will this impact my table size?

Change the datatype of Detail to VARCHAR(2000) or TEXT*.  This will be enough to handle your 1900 length value plus a little padding for future exceptions.  This will add no additional overhead beyond the additional storage needed for that particular row.
If you add another column you will add 1 byte to all of the rows (which isn't a big deal) but now you have to contend with two columns with different names but the same purpose.
Postgres documentation explains storage requirements in more detail:
https://www.postgresql.org/docs/current/datatype-character.html
*The TEXT datatype is not ANSI compliant, if you do use it understand the implications.


Advisable to restart a SQL Server running Windows when disk space is low?

The short answer is that we eventually literally pulled the plug on the SQL server and got away with it, (although I wouldn't necessarily recommend the approach). If you're in a situation like we were with this though, here's the full scenario:

We couldn't log onto the box at all, and all services dependant on the database were running very slowly. The OS seemed to be partially shutting down.
Eventually got lucky and managed to allocate an extra 6Gb to the server (from a low of 4Mb free), by connecting to it using computer management (after several attempts).
After this, all attempts to detach the database we wanted to move failed (we tried setting the database into single user mode with immediate rollback, also setting it into simple recovery mode, manually killing off SPIDs, nothing worked).
We were seeing pre-emptive database locks placed by the OS (presumably for the OS to ensure it could keep running), which seems to have been what was preventing us detaching the database in order to move it to a different drive.
We tried to stop the entire database instance, however this didn't work and eventually we literally pulled the plug on the server.
When it restarted we were able to immediately detach the database as the restart straightened out the OS. Moved the database, re-attached it and we're back in business :)
The database we moved is (initially) stuck in 'read only' state, however we do only read from it.


ORA-39244:Event to disable dropping null bit image header during relational select

For me this line seems most telling:

PL/SQL: could not find program unit being called: "SYS.DBMS_AQADM_SYS"

You should check to see if the user that you exported had a job scheduled using a program owned by someone else. If so you are trying to schedule a job for a program that does not exist. You can create the program, then do the import as long as the user who will own the program exists before you create the program as that user.


Group by unknown ID, select all related rows where Column X = Y and Column X = Z

Maybe you need

SELECT session_id,
       MAX(CASE WHEN question = 'Q1' then responce END) q1,
       MAX(CASE WHEN question = 'Q1' then DateCreated END) q1_datetime,
       MAX(CASE WHEN question = 'Q2' then responce END) q2,
       MAX(CASE WHEN question = 'Q2' then DateCreated END) q2_datetime,
       MAX(CASE WHEN question = 'Q3' then responce END) q3,
       MAX(CASE WHEN question = 'Q3' then DateCreated END) q3_datetime,
       terminal
FROM quiz
WHERE terminal IN ('A1','A2','B1')
GROUP BY session_id, terminal
-- HAVING MAX(CASE WHEN Question = 'Q1' then Response END) = 'A'
--    AND MAX(CASE WHEN Question = 'Q2' then Response END) = 'B';


Querying MySQL and SQL Server from same IDE

there is a lot of options but choosing the right one is up to you, this is some of the options :

1) Adminer: http://www.adminer.org/

2) DBeaver: http://dbeaver.jkiss.org/about/

3)DBVisualizer Free: http://www.dbvis.com/

4) sql developer (but you need to install the plugins first) : http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html


Stored Procedure is taking very long to complete

Here's one suggestion:

CREATE TEMPORARY TABLE csv
( i int not null
, c1 ... not null
, c2 ... not null
, c3 ... not null
, c4 ... not null )

Load the csv file into the temporary table, and use a join to get the result.

SELECT csv.i
   table1.c1,
   table1.c2,
   csv.st_dist_spere,
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
                      point(table1.c3, table1.c4)) * 0.2 AS length,
   table1.c5
FROM   table1
CROSS JOIN csv
WHERE  table1.c1 IN (
                 SELECT DISTINCT table2.c1
                 FROM      table2
                 LEFT JOIN table2.c1=table3.c1
                 WHERE table2.c2=1
                 AND   table2.c3 BETWEEN 1000 AND 2000
HAVING length < csv.c4
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1

I would then look at the IN predicate (I'll assume this is what it is supposed to be):

SELECT DISTINCT table2.c1
FROM      table2
LEFT JOIN table3
    ON table2.c1=table3.c1
WHERE table2.c2=1
  AND table2.c3 BETWEEN 1000 AND 2000

The outer join against table3 can be eliminated (a smart optimizer should realize this, but I don't know if this is implemented in MySQL):

SELECT DISTINCT table2.c1
FROM      table2
WHERE table2.c2=1
  AND table2.c3 BETWEEN 1000 AND 2000

This leaves us with:

SELECT csv.i
   table1.c1,
   table1.c2,
   csv.st_dist_spere,
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
                      point(table1.c3, table1.c4)) * 0.2 AS length,
   table1.c5
FROM   table1
CROSS JOIN csv
WHERE  table1.c1 IN (
                 SELECT DISTINCT table2.c1
                 FROM      table2
                 WHERE table2.c2=1
                 AND   table2.c3 BETWEEN 1000 AND 2000
       )
HAVING length < csv.c4
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1

Next step, is trying to replace the IN PREDICATE with a JOIN:

SELECT csv.i
   table1.c1,
   table1.c2,
   csv.st_dist_spere,
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
                      point(table1.c3, table1.c4)) * 0.2 AS length,
   table1.c5
FROM   table1
CROSS JOIN csv
JOIN ( SELECT DISTINCT table2.c1
       FROM      table2
       WHERE table2.c2=1
       AND   table2.c3 BETWEEN 1000 AND 2000
     ) as X
   ON X.c1 = table1.c1
HAVING length < csv.c4
   AND    table1.c5 BETWEEN date(start_date) AND date(end_date)
ORDER BY csv.i, table.c1

It may be advantageous to push the range predicate into table1 before doing the cross join:

SELECT csv.i
   table1.c1,
   table1.c2,
   csv.st_dist_spere,
   ST_Distance_Sphere(point(csv.c2[i], csv.c3[i]),
                      point(table1.c3, table1.c4)) * 0.2 AS length,
   table1.c5
FROM (
    select ... from table1
    where table1.c5 BETWEEN date(start_date) AND date(end_date)
) as table1
CROSS JOIN csv
[....]


Firebird query performance with text search, also some numbers

Nothing suspicious in the plan itself, though I do wonder what your actual query looks like. If KONTRAHENCI.kt_nazwa has an index and there also is an index for the field in OFERTY that links to the primary key field of KONTRAHENCI, then a better plan might be something like:
PLAN JOIN (JOIN (KL INDEX (KT_NAZWA_INDEX), OFERTY INDEX (LINK_FIELD_KONTRAHENCI...

Are you by any chance not using INNER JOINs, but LEFT or RIGHT [OUTER] JOINs? That would prevent Firebird from choosing the optimal index.


Cannot figure out how to remove this Using Temporary

There is no chance to remove Using temporary; Using filesort on that query.

If m.id is unique (or even primary key), DISTINCT is excessive. If not, the ORDER BY is random.- Akina


What's the approach to figure out reason for mysql crash?

This appears to be a regression bug that exists in Percona Server 5.6.14 and possibly some previous versions of 5.6.
https://bugs.launchpad.net/percona-server/+bug/1247021
It appears it was fixed in Percona Server 5.6.16-64.0:

Due to a regression in the buffer pool mutex split, a combination of InnoDB compression, write workload, and multiple active purge threads could lead to a server crash. Bug fixed #1247021.
— http://www.percona.com/doc/percona-server/5.6/release-notes/Percona-Server-5.6.16-64.0.html


One way is

UPDATE a
SET [Role]= 'Admin'
FROM UserRoles a
WHERE a.[Role]= 'Administrator'
AND NOT EXISTS
  (
       SELECT NULL
       FROM dbo.UserRoles b
       WHERE b.[User] = a.[User] AND b.[Role] = 'Admin'
  );


Why should I use InnoDB and MySql instead of XtraDB and MariaDB?

Performance enhancement may be possible without major upgrade to the next version of MySQL or a Migration to MariaDB.

MySQL is the default database by which everyone else creates forks or introduces new things

Different Storage Engines

TokuDB
PBXT
ScaleDB
and Others

Improved Storage Engines

XtraDB over InnoDB
TokuDB over InnoDB

New Features not available in Oracle's MySQL

Multisource Replication
Progress Meter on Commands : Is there a progress indicator for OPTIMIZE TABLE progress?

To be honest, some people upgrade to MariaDB for nontechnical reasons

It's the latest buzzword in the MySQL Universe in years
Redhat want to include MariaDB instead of MySQL in future releases
Monty Widenus really hates MySQL in the hands of Oracle
Hoping for Performance Improvements across the board just doing the upgrade (no due diligence)

Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Oct 05, 2011 : Query runs a long time in some newer MySQL versions

EPILOGUE

Before deciding on moving over to MariaDB (or any other MySQL fork), you need to be sure you understand the features, improvements, and the configuration settings, and you actually follow through on the settings to make those improvements come to life. See my post How do I properly perform a MySQL bake-off?

In light of all this

If you can enhance performance without an upgrade, you save yourself needless work
If you really need AND UNDERSTAND features, then upgrade
If you benchmark MySQL and MariaDB on the same level playing field (same configurations), decide on which product based on the significant performance improvements (or lack of improvements).


SQL Server: ensure primary key uniqueness over two tables

For performance in this scenario, you should consider Filtered Index.

It looks like it is ideal solution for your case. Just filter only events with appropiate status and index will be small and fast.

Other pros will be minimal effort to implement this solution (add new index).


What makes an OLTP database an OLTP database?

I would say that most production databases are OLTP with a little OLAP flavor. In a lot of shops the OLAP side of the world is getting more specialized and they really are performing the analytical functions outside of the normal application database. Are there shops that still plunk everything into one database? Of course. But as the capabilities improve (and Denali is no exception), I think you'll start to see a much more obvious divide.


Please use this code to create a table value Parameter.

--create a new database
USE [master]
GO
CREATE DATABASE DemoTVP
GO

--Change context to the new databse
USE [DemoTVP]
GO

--creating table-Valued Parameters
CREATE TYPE LocationTableType AS TABLE
( LocationName VARCHAR(50)
, CostRate INT );
GO

Then you see the above created table value parameter in SSMS.

Using TSQL you can see the same.

USE [DemoTVP]
GO
SELECT * FROM [sys].[objects]
WHERE [type] ='TT'
ORDER BY create_date DESC


Check if all record columns of 2 Tables/Query are equal

I think this will work:

SELECT CASE WHEN NOT EXISTS
         ( SELECT * FROM table1
           EXCEPT
           SELECT * FROM table2
         )
             AND NOT EXISTS
         ( SELECT * FROM table2
           EXCEPT
           SELECT * FROM table1
         )
         THEN 'True' ELSE 'False'
       END AS result ;


How to enforce that a parent must have at least one child when the PK is composite?

The CTEs (WITH and RETURNING) are there to avoid the use of deferred constraints.

It is simpler conceptually to use deferred constraints and for a homework assignment, I suggest you start with using them. Example, with comments where it differs from yours:

CREATE SEQUENCE planes_seq;

CREATE TABLE airlines (
    aid INTEGER PRIMARY KEY DEFAULT nextval('planes_seq')
);

CREATE TABLE planes (
    aid INTEGER NOT NULL REFERENCES airlines (aid)
,   pid INTEGER NOT NULL
,   sid INTEGER NOT NULL                -- we add a default seat
        DEFAULT 1 CHECK (sid = 1)       -- in every plane
,   PRIMARY KEY (aid, pid)
);

CREATE TABLE seats (
    aid INTEGER NOT NULL
,   pid INTEGER NOT NULL
,   sid INTEGER NOT NULL
,   PRIMARY KEY (aid, pid, sid)
,   FOREIGN KEY (aid, pid) REFERENCES planes (aid, pid)
);

ALTER TABLE planes ADD CONSTRAINT fk_seats
    FOREIGN KEY (aid, pid, sid)
    REFERENCES seats (aid, pid, sid)
    DEFERRABLE INITIALLY DEFERRED ;      -- the DEFERRABLE is important

Now we can add some data:

INSERT INTO airlines (aid)
VALUES (1) ;                      -- our 1st airline

BEGIN ;
    INSERT INTO  planes      -- its 1st plane
        (aid, pid)           -- notice: sid is added by default as 1
    VALUES
        (1, 1) ;

    INSERT INTO seats
        (aid, pid, sid)
    VALUES                    -- 3 seats in the 1st plane
        (1, 1, 1),
        (1, 1, 2),
        (1, 1, 3) ;
COMMIT ;

We need to insert new planes (and their seats) inside a transaction, otherwise it would fail.

DEFERRABLE means that we can "defer" the check of the FOREIGN KEY constraint until the end of the transaction (not deferred constraints are checked immediately after insert/update/delete statements.) If you try to insert only in planes, it will fail.

INITIALLY DEFERRED means that we want to be "deferring" the foreign key constraint now, as soon as it is created. This could have been done later (you can set and unset constraints from deferred, if they have been defined as DEFERRABLE).


SQL case statement not executing else part

This returns the wanted result, but i don't think that you wnat only the course id.

But this should help further

DELIMITER $$
CREATE FUNCTION search(roll_num VARCHAR(50))
RETURNS VARCHAR(150)
DETERMINISTIC
BEGIN
    IF (EXISTS(SELECT 1 FROM `student` WHERE `student`.`roll_no` = roll_num)) THEN
        RETURN (SELECT CONCAT (`student`.`student_name`,`student`.`course_id`) FROM `student`WHERE `student`.`roll_no` = roll_num);
    ELSE
        RETURN 'Roll No not exist ';
    END IF;
END$$
DELIMITER ;


Filter Stored Procedure Result (Informix)

Here is a illustration how works..
This was executed with dbaccess at Informix 11.50 FC9.
You can name your return parameters, but they are valid only when executed the procedure as procedure by self... not into select statement. There you need to name each column manually... check below.

References at IBM Informix 11.50 manual:

select from syntax...
procedure named return parameters

create procedure dummy ( param integer )
returning int as id , char(10) as name, date as created
define vid int;;
define vname char(10);;
define vcreate date;;

foreach c1 for
  select first 10 tabid, tabname, created
     into vid, vname, vcreate
     from systables where tabid >= param

  return vid , vname, vcreate with resume ;;
end foreach ;;

end procedure
;
Routine created.

;

execute procedure dummy(10);

         id name       created

         10 syssyntabl 20/02/2014
         11 sysconstra 20/02/2014
         12 sysreferen 20/02/2014
         13 syschecks  20/02/2014
         14 sysdefault 20/02/2014
         15 syscoldepe 20/02/2014
         16 sysprocedu 20/02/2014
         17 sysprocbod 20/02/2014
         18 sysprocpla 20/02/2014
         19 sysprocaut 20/02/2014

10 row(s) retrieved.

select  * from table(dummy(10));

unnamed_col_1 unnamed_col_2 unnamed_col_3

           10 syssyntabl    20/02/2014
           11 sysconstra    20/02/2014
           12 sysreferen    20/02/2014
           13 syschecks     20/02/2014
           14 sysdefault    20/02/2014
           15 syscoldepe    20/02/2014
           16 sysprocedu    20/02/2014
           17 sysprocbod    20/02/2014
           18 sysprocpla    20/02/2014
           19 sysprocaut    20/02/2014

10 row(s) retrieved.

select  * from table(dummy(10)) as x(id,name,created);

         id name       created

         10 syssyntabl 20/02/2014
         11 sysconstra 20/02/2014
         12 sysreferen 20/02/2014
         13 syschecks  20/02/2014
         14 sysdefault 20/02/2014
         15 syscoldepe 20/02/2014
         16 sysprocedu 20/02/2014
         17 sysprocbod 20/02/2014
         18 sysprocpla 20/02/2014
         19 sysprocaut 20/02/2014

10 row(s) retrieved.

select  x.id,name from table(dummy(10)) x(id,name,created);

         id name

         10 syssyntabl
         11 sysconstra
         12 sysreferen
         13 syschecks
         14 sysdefault
         15 syscoldepe
         16 sysprocedu
         17 sysprocbod
         18 sysprocpla
         19 sysprocaut

10 row(s) retrieved.

Database closed.


After many successive and successful queries SQL Server starts refusing connections

What node.js version are you on?
Apparently node version: v.12.6.0 required me to use packetSize: 8192 to avoid the Connection lost - read ECONNRESET error
If that is no the problem try to change the Network Packet size in the SSMS Properties

If you said that the connections mostly works but fails for different data is because of the size of the stream of data.


If you have a separate table Clients with the list of all 1000 client IDs, it may be faster to seek 500 times into 1M rows table instead of reading all 1M rows. This is another common variant to write a top-n-per-group query. The following question discussed it in great details: Retrieving n rows per group.

Also, use filtered index to match your query:

CREATE NONCLUSTERED INDEX [IX] ON [dbo].[entries]
(
    [client_id] ASC,
    [creation_date] ASC
)
INCLUDE
(
    [status],
    [uuid]
)
WHERE (status <> 'DONE')

The query (assuming you have Clients table):

SELECT TOP(500)
    CA.*
FROM
    dbo.Clients
    CROSS APPLY
    (
        SELECT TOP(1)
            dbo.entries.*
        FROM
            dbo.entries
        WHERE
            dbo.entries.client_id = dbo.Clients.client_id
            AND dbo.entries.status <> 'DONE'
        ORDER BY dbo.entries.creation_date
    ) AS CA
;


Should I opt for a new database or adding more tables?

I would definitely err on the side of keeping the tables in the same database. It makes it easier to maintain referential integrity (unless you hard delete records in which case foreign keys won't work for you anyway) and create consistent backups (simply backup one database and you know it is in a consistent state, instead of taking two+ backups that my be slightly out of sync).

If you concern is wanting to spread the data between different storage pools (for instance keeping history and audit data on something big & cheap but potentially slow, while keeping active data on something faster but smaller & more expensive), or just keeping the different data on different spindle-sets to reduce potential IO contention, then you can achieve this within a single database by distributing your tables between files/filegroups instead of just having one filegroup containing everything. See http://technet.microsoft.com/en-us/library/ms189126(v=sql.105).aspx (amongst other reference locations in MS's sites) for more details on this.


How can I Get Value of Max(col) in a greatest n per grouped by into Declared variable. MS SQL sever

how to set the value of @MaxItemSeqNum to the returned result

Either

SET @MaxItemSeqNum = ( SELECT MAX([Table1].[Item_Seq_number])
                       FROM Table1
                       WHERE Batch_Id = 1
                       Group By Batch_Id );

or

SELECT @MaxItemSeqNum = MAX([Table1].[Item_Seq_number])
                        FROM Table1
                        WHERE Batch_Id = 1
                        Group By Batch_Id;

fiddle

The last variant is more useful - it allows to assign the values to more than one variable in one statement.


How many disk seeks does it take to write one row on SQLite?

This question was answered on Stackoverflow.

  By default its one transaction per
  disk rotation (roughly),

  http://www.sqlite.org/faq.html#q19

  The way to speedier writes is to wrap
  multiple ones inside a transaction.


How to use an integer pivot in SQL Server?

First I'd suggest to verify that you source query returns expected data:

SELECT     Principal.Fixed, Code.Alpha, Code.Position
FROM       Principal
INNER JOIN Code ON Principal.Id;

I've set up a single test and it should works. (dbfiddle here)

Second, you can build @col variable dynamically by using Position values returned by your main query, avoiding to build it using variables (@one, @two, ...)

DECLARE @col AS nvarchar(MAX),
        @cmd AS nvarchar(MAX);

SET @col = STUFF((SELECT distinct ',' + QUOTENAME(Code.Position)
                  FROM Principal
                  INNER JOIN Code ON Principal.Id = Code.PrincipalId
            FOR XML PATH(''), TYPE
            ).value('.', 'nvarchar(MAX)')
           ,1,1,'')

SET @cmd =   'SELECT Fixed, ' + @col
           + ' FROM'
           + ' (SELECT Principal.Fixed, Code.Alpha, Code.Position '
           + '  FROM       Principal '
           + '  INNER JOIN Code ON Principal.Id = Code.PrincipalId) src'
           + ' PIVOT (MAX(Alpha) FOR Position IN (' + @col + ')) pvt';


Excessive RAM consumption in MySQL Cluster

The 429496 transaction records consume around 400 MBytes
(around 900 bytes per record).

The operation records consume also around 400 MBytes,
around 300 bytes in transaction coordinator and
500 bytes in ldm thread.

In addition it uses memory for a lot of other buffers such
as REDO buffers, SEND buffers, job buffers. These normally
consume a few hundred MBytes.

In addition you can decrease memory usage by setting
BatchSizePerLocalScan to a smaller value (e.g. 16).
This should save a few hundred MBytes of memory space.


Is there a simple, safe and low cost way to implement a (limited) database to be outside (close) IT control?

Here's my view of the situation:

The data you are working with already have a source; they are extracted from some data mart or "system of record" and given to you, probably as a CSV file or something, which you load into Excel and build your models. Subsequently, there is already a database that has the data you need. Further, there is probably a bunch of development and QA (that is, non-production) versions of this database, to which it is much easier to get access.

If that is so, your best chance would be to discover where your data are coming from (if you don't already know) and ask your IT to give you access to a development copy of that database. Since they already maintain it, there won't be much extra effort required on their side and they might be less reluctant to help you.


SQL Server 2016 - Disparity Between View Definition and View Design

The view will run according to it's metadata, which is no longer correct.

If you run sp_refreshview, it should work correctly.

sp_refreshview  @viewname =  'TestView' ;

select * from TestView ;

+------+------+------+------+
| col1 | col2 | col5 | col4 |
+------+------+------+------+
|    1 | abc  | NULL | ABC  |
|    2 | def  | NULL | DEF  |
|    3 | ghi  | NULL | GHI  |
|    4 | jkl  | NULL | JKL  |
+------+------+------+------+


Mysql Command line Parameter passing to sql file

Try something like

echo set @tmp='abc'; > %temp%\temp.sql
copy /b %temp%\temp.sql+d:/v_test1.sql
mysql -u root -p xyz < %temp%\temp.sql
del %temp%\temp.sql


bring back results from Table1 that don't exist in Table2

You can use NOT EXISTS

SELECT
    T1.*
FROM
    TABLE1 T1
WHERE NOT EXISTS(SELECT 1
                 FROM   TABLE2 T2
                 WHERE  T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER
                        AND T2.APPL_ID = T1.APPL_ID );

Update

It doesn't matter how many JOIN's you use before the left anti semi join, it returns all rows from the left side that does not exists on the right side, according the predicate: WHERE T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER AND  T2.APPL_ID = T1.APPL_ID

SELECT
    T1.*
FROM
    TABLE1 T1
RIGHT JOIN
    TABLE99 T99
    ON T99.CARD_ID = T99.CARD_ID
    AND T99.DATE_OPEN = T1.DATE_OPEN
WHERE NOT EXISTS(SELECT 1
                 FROM   TABLE2 T2
                 WHERE  T2.ACCOUNT_NUMBER = T1.ACCOUNT_NUMBER
                        AND  T2.APPL_ID = T1.APPL_ID
                );

Let's imagine 3 tables with next ID's:

 T1    T2    T9
----  ----  ----
 01    01    01
 02          02
 03    03
 04    04    04
 05          05

Give me all ID's that exists in T1 and T9.

SELECT ID FROM T1 JOIN T9 ON T1.ID = T2.ID;

01,02,04,05

and now, give me all ID's of this group that does not exists in T2:

pseudo-code:

SELECT ID FROM (01,02,04,05) WHERE NOT EXISTS IN (01,03,04);

02,05

NOTE: I've replaced table names by their values to make the final result more clear


SSIS Packages with SQL Server Express 2008 R2

I think you need to be clearer on your exact intended setup. That will allow for a better reading of those two linked questions. I would guess that those two links are actually irrelevant. SQL Server Express comes with a stripped down version of SSIS (or can be installed via the Tools or Advanced install), according to Link #2. But yet your question regards "creat[ing] packages for use between the Standard edition of SQL Server and the Express instance of SQL Server" which implies that you have Standard Edition. If that is the case, then you can create and run packages from the BIDS and SSIS that came with Standard Edition, and connect to Express Edition instances to transfer data back and forth. Documentation that says "SQL Server Express 2008 R2 doesn't support SSIS" is not saying that Express cannot be connected to (unless, of course, your testing indicates otherwise).

EDIT:
Testing indicated that this did indeed work; creating an SSIS package from BIDS that came with Standard Edition, and running it from Standard Edition, was able to connect to Express Edition. The only caveat is that, as noted by @Gareth in a comment below, "certain functionality won't work, for example History Cleanup, as it's not supported by Express Edition".


Backup/restore should normally be your method of choice. It will be quicker in most situations.

You can use it consistently, also for production to test too.

See this related question as well, where the backup/restore vs detach/attach is mentioned:

SQL Server Migration restore backup vs copy data and log files

Make sure you add the WITH COPY_ONLY option to the backup so it does not break the existing maintenance plan backup chain.


SELECT
'Süre Bitiş Tarihi','Tc Kimlik No', 'Kullanıcı Soyadı, Adı','E-posta',
'Telefon','Diğer Telefon','Adres','Şehir','Posta Kodu','Ev Adresi',
'Ev Şehir','Ev Posta Kodu','Barkod', 'Eser Adı', 'Yazar', 'Yeri',
'Kütüphane Adı','Materyal türü','Yerel Yer Numarası','Basım Tarihi',
'Hedef kitle','Dil kodu'
UNION ALL (
SELECT *

from(

SELECT
i.date_due, bo.cardnumber,CONCAT(bo.surname,',',bo.firstname),bo.email,
bo.phone, bo.mobile, CONCAT(bo.address,' ',bo.streetnumber,' ',bo.address2),
bo.city, bo.zipcode, CONCAT(bo.B_address,' ',bo.B_streetnumber,' ',bo.B_address2) ,bo.B_city,bo.B_zipcode , it.barcode,
b.title,b.author,it.location,br.branchname,itype.description,
it.itemcallnumber,b.copyrightdate,bitems.agerestriction,
EXTRACTVALUE(bitems.marcxml,'/record/datafield [\@tag=\"041\"] /subfield [\@code=\"a\"]')

FROM issues i
LEFT JOIN items it on i.itemnumber=it.itemnumber
LEFT JOIN biblioitems bitems on bitems.biblioitemnumber=it.biblioitemnumber
LEFT JOIN biblio b on b.biblionumber=it.biblionumber
LEFT JOIN itemtypes itype on it.itype=itype.itemtype
LEFT JOIN borrowers bo on bo.borrowernumber=i.borrowernumber
LEFT JOIN branches br on br.branchcode=bo.branchcode
where it.holdingbranch=110

UNION ALL (

SELECT
i.date_due, bo.cardnumber,CONCAT(bo.surname,', ',bo.firstname),
bo.email, bo.phone, bo.mobile, CONCAT(bo.address,' ',bo.streetnumber,
' ',bo.address2),bo.city, bo.zipcode,
CONCAT(bo.B_address,' ',bo.B_streetnumber,' ',bo.B_address2),
bo.B_city,bo.B_zipcode , it.barcode, b.title,b.author,it.location,
br.branchname,itype.description,it.itemcallnumber,b.copyrightdate,
bitems.agerestriction,
EXTRACTVALUE(bitems.marcxml,'/record/datafield [\@tag=\"041\"] /subfield [\@code=\"a\"]')
FROM old_issues i
LEFT JOIN items it on i.itemnumber=it.itemnumber
LEFT JOIN biblioitems bitems on bitems.biblioitemnumber=it.biblioitemnumber
LEFT JOIN biblio b on b.biblionumber=it.biblionumber
LEFT JOIN itemtypes itype on it.itype=itype.itemtype
LEFT JOIN borrowers bo on bo.borrowernumber=i.borrowernumber
LEFT JOIN branches br on br.branchcode=bo.branchcode
where it.holdingbranch=110
))temp);

You are missing from clause in line No 8.  After using Union all you just combine with any temp variable.


How to (re)start Postgres on Windows command line in background?

As it turns out, it was running in the console because I didn't tell it where to stream the log. The following sent the process in the background:

pg_ctl restart -l %PGDATA%\server.log


How to access the data of collections from the .ns and other core storage files in mongodb?

Check this guide: http://docs.mongodb.org/manual/tutorial/backup-with-mongodump/
Create Backups Without a Running mongod Instance

If your MongoDB instance is not running, you can use the --dbpath option to specify the location to your MongoDB instance’s database files. mongodump reads from the data files directly with this operation. This locks the data directory to prevent conflicting writes. The mongod process must not be running or attached to these data files when you run mongodump in this configuration.

mongodump --dbpath /old_data -o dataout

Then restore to a new data-directory and access the databases.


Execution Plan Basics -- Hash Match Confusion

As SQLRockstar's answer quotes

  best for large, unsorted inputs.

Now,

from the Users.DisplayName index scan (assumed nonclustered) you get Users.Id (assuming clustered) = unsorted
You are also scanning Posts for OwnerUserId = unsorted

This is 2 unordered inputs.

I'd consider an index on the Posts table on OwnerUserId, including Title. This will add some order on one side of the input to the JOIN + it will be covering index

CREATE INDEX IX_OwnerUserId ON Posts (OwnerUserId) INCLUDE (Title)

You may then find that the Users.DisplayName index won't be used and it will scan the PK instead.


MariaDB ERROR 1048 GeomFromText returning as null


What is MySQL Cluster's Practical Memory Limit?

As I read it, there is a 64GB limit per node.  48 nodes, without redundancy, would allow 3TB total data size, 1.5TB with redundancy.

That sounds like less than a day's worth of data at the rate you are ingesting data.

Do you need all the raw data?  Or can you summarize the data, then throw away (or archive to text files) the raw data?  This would probably let you collect data for 10 times as long.


Master - Master replication Duplicate entry error

The tutorial you linked to is, I'm afraid, very optimistic. It forgets to note that it only covers the case of an auto generated AUTO_INCREMENT PRIMARY KEY.

The methods it suggests do not cover the general case for a UNIQUE KEY, including, of course, a general PRIMARY KEY.

The article does mention that "The most common problem with replication is primary key collision". It then completely fails to mention all the other problems...

So, anyway, any UNIQUE KEY collision will render your replication broken, possibly in both directions.

Which table was it that failed your replication? Was that a table with an AUTO_INCREMENT PRIMARY KEY or a "general" PRIMARY KEY (ie on non-AUTO_ICNREMENT columns)?

To solve the general case of unique keys, your application must be very aware of its location (Netherlands or Washington DC), and must refrain from writing data that may cause collision. Alternatively it might want to use some synchronizing/locking mechanism; that would of course induce latency as data must cross the Atlantic.

Might I also suggest that the use of binlog_do_db is also naive and dangerous. Are you sure you don't want to replicate the mysql schema? What happens when you add new schemas? The general correct solution is to have full replication without filtering, or otherwise have a very good explanation why not to do so.

Finally, I highly discourage the use of active-active master-master replication. I had customers using it in the past. The amount of trouble they went into because of that setup, and in spite of my counter-recommendations, led to many ruined weekends and holidays.

You might want to look at Galera replication. It's a synchronized master replication solution, and I hear it works over WAN. This will allow your application to remain ignorant. Watch out for network failures, as one part of your cluster will turn immutable.


Storing different countries' administrative divisions

One way to store different levels of hierarchy is by creating a hierarchical table:

ID    Type
---   ---------------------------
1     Country
2     Province/State
3     County
4     City
5     Regions
6     Communes
7     Municipality

Then you can create your divisions and list them appropriately. This allows you to flexibly assign divisions and the parent divisions.

ID    Division    Parent    Type
----- ----------- --------- -----
1     Chile                 1
2     Region-A    1         5
3     Province-A  2         2
4     Commune-A   3         6
5     Angola                1
6     Province-A  5         2
7     Municip-A   6         7
8     Commune-A   7         6

The benefits is that different levels of hierarchy can be assigned in this design for different top level divisions. People can be associated with any level of hierarchy.

ID    Person      Division
----- ----------- ---------
1     Tom         4 (Commune-A of Province-A of Region-A of Chile)
2     Dick        7 (Municip-A of Province-A of Angola)
3     Harry       1 (Chile)

The challenge is to create a user defined function or stored procedure or utilize hierarchy data structures (e.g. SQL Server's Hierarchy ID) to extract different pieces of information for the statisticians. For example:

  Show sales by highest level of hierarchy of each salesperson and list the level and the name of that level (e.g. Harry sold $45M of widget A in Chile (level 1 of 1) in comparison to Dick who sold $40M of the same widget in Municip-A (level 3 of 4)).

Note that Angola's hierarchy is 4 levels deep and Dick is functioning on 1 level above the bottom-most level. Stats can be generated by different depths.

  Show sales for level 1 of each salesperson (e.g. sales for Chile, Angola etc.). Show sales for level 2 (e.g. sales for Province-A if Chile, or Municip-A if Angola etc.). Show all sales for a level named Province, or even more complex - show all sales for Provinces of countries who have it, or Municipalities of countries who have it.

The denormalized data warehouse structure can also be used, like you mentioned in the question. In a dimensional table, add sale person's name and assign level1, level1type, level2, level2type etc. fields. Tom's level 1 would be Chile and type would be Country. Level 2 would be Region-A and type would be Region etc. This kind of structure can be easy for data extraction if statisticians are looking to hone down on level 1 or 2 or such. We have just added to complexity of adding more columns as we discover hierarchy of different countries but the benefit is that the data is easy to visualize. A simple select statement can help us understand how Tom is functioning.

Sit down with statisticians and find out what they like to see and how they would like to use the data. Based on their ideas you might be able to build a better, perhaps a hybrid system.


check format of every value of column in sql server

You can use TRY_PARSE for this. This function will attempt to parse a supplied NVARCHAR value as a given data type, and if it fails it returns NULL.

For example, you can try the following:

select TRY_PARSE('11-23-09' AS DATETIME2)

and it returns

2009-11-23 00:00:00.0000000

But this

select TRY_PARSE('abc' AS DATETIME2)

returns NULL because it cannot parse 'abc' as a datetime value. There are other options, such as using TRY_CONVERT or TRY_CAST, however, TRY_PARSE as the advantage of allowing you to use a culture setting to parse values for a specific region/language.

For example, TRY_PARSE('11-23-09' AS DATETIME2) will parse if the default region is en-US on your SQL Server, but TRY_PARSE('23-11-09' AS DATETIME2) will fail as the NVARCHAR value is not using the US date format.

Specifying the region in the TRY_PARSE function - TRY_PARSE('23-11-09' AS DATETIME2 USING 'en-AU') - means that the string value will PARSE correctly.

You can see this behaviour in this db<>fiddle.

For your specific requirement, use TRY_PARSE in the WHERE clause to identify rows in the first table that can be successfully converted to a datetime value for inserting into your second table.


Restrict access to my PostgresSQL/ Post GIS database used through QGIS

That is best done through the PostgreSQL permission system.

Create database users for the people to access the database. Then grant them SELECT on the tables that contain the layer data and USAGE on the schemas containing these tables. For example:

GRANT USAGE ON SCHEMA myschema TO auser;
GRANT SELECT ON myschema.mytable TO auser;


Generally you can open standby databases as read only. Specifically you can open data guard standby databases as read only. Did you check to see if all of the data files exist? That a database is mounted, means that the control files are where they are supposed to be, not corrupted and your init/spfile is valid.

Did you patch the Oracle Home for the standby database with the same patch as the parent? Can you force database recovery on the child to get the databases in sync.


There is one serious effort to do this - it can be found here. Check out the warning though

  WARNING

  It is extremely important for you to fully understand each change you
  make to a MySQL database server.... &c...

It is virtually impossible to factor in all variables - at the end of the day, it comes down to MySQL expertise. The author (Major Hayden) is a principal architect with RackSpace, so one can assume that he has some expertise.


If I've understood you correctly, you want something like this (see fiddle here):

SELECT
  p.id AS p_pid, p.product_name AS p_name,
  p.product_category_id AS p_cat_id,
  pt.product_id AS pt_pid, pt.tag_id AS pt_tag_id, -- other product_tag fields...
  t.tag_name AS t_name,
  t.slug AS t_slug_name                            -- other tag fields...
FROM products p
INNER JOIN product_tags pt
  ON p.id = pt.product_id
INNER JOIN tags t
  ON t.id = pt.tag_id
WHERE pt.tag_id = 1 OR pt.tag_id = 6
ORDER BY product_id, tag_id;

Result:

p_pid     p_name p_cat_id pt_pid pt_tag_id        t_name    t_slug_name
    1   product1        4      1         1  Mobile Sales    mobile-sales
    1   product1        4      1         1  Mobile Sales    mobile-sales
    1   product1        4      1         6            Mi              mi
    1   product1        4      1         6            Mi              mi
    2   product2        4      2         1  Mobile Sales    mobile-sales
    2   product2        4      2         1  Mobile Sales    mobile-sales
    2   product2        4      2         6            Mi              mi
    2   product2        4      2         6            Mi              mi

Notice in the fiddle how I've arranged the SQL. I've used aliases and meaningful names for my fields, prefixing them with the table initials. You mightn't want to do that for the final query, but it sure helps when constructing your query and  you have different, say, product_ids coming in from different tables through the JOINs. The aliases also help with legibility along with liberal use of spaces.

I think because of this lack of clarity, you missed this piece of code:

WHERE product_tags.tag_id=1 AND product_tags.tag_id=6

which, when turned into this, becomes much more legible:

WHERE pt.tag_id = 1 AND pt.tag_id = 6

Now, it's glaringly obvious where the error is - you can't have a tag_id = 1 AND = 6 at the same time, hence your empty result set from your original query. So, just changing that line to:

WHERE pt.tag_id = 1 AND pt.tag_id = 6

gives the result which is I believe what you were looking for. If not, let me know and I will endeavour to correct any problems/misunderstandings!

Oh, yes, a couple of final words. Please always include your version of MySQL - it's particularly important with this RDBMS as they've introduced many important features recently! Also, see the importance of providing good sample data - good questions get good answers here and yours has now become a good one with the addition of the thorough fiddle (+1)!


Missing commands in MySQL command line history

There is an option to prevent some commands that contain specific words from being logged. One common practice is not to log commands that has the word password.

This option is called histignore

More info:

https://dev.mysql.com/doc/refman/5.7/en/mysql-command-options.html#option_mysql_histignore
https://dev.mysql.com/doc/refman/5.7/en/mysql-logging.html


Access denied for user 'root'@'localhost'

You can reinit the password :

1.stop mysql

/etc/init.d/mysql stop

2.start mysql safe :

mysqld_safe --skip-grant-tables &

3.connect to mysql

mysql -u root

4.change the password

mysql> use mysql;
mysql> update user set password=PASSWORD(”YOUR_PASSWORD”) where user=’root’;
mysql> flush privileges;
mysql> quit

5.stop mysql

/etc/init.d/mysql stop

6.start mysql

/etc/init.d/mysql start

Then you can connect to mysql with login root and the new password.


MongoDB 4.2 using change streams with read concern majority disabled in a PSA deployment

This is not the expected behavior. Since you have the majority members of the replica set online, it should work

Just wanted to confirm:

Are you using version 4.2?
After installing/upgrading the replica set, have you set the feature compatibility version to 4.2 by executing the below command?

db.adminCommand( { setFeatureCompatibilityVersion: 4.2 } )

Thank you!


Optimizing syncing of large dataset between 2 servers with SSIS

You want to add a "lookup" operator in your SSIS pacakage.
this package will "check" for some columns (your PK for example) and you will then be able to take action on only the rows that are missing.


Using totals on aggregates to improve performance

I can't understand why you can't aggregate on the fly and why it's so slow. Is the "Valid" idea a workaround to deal with the lag of OrderTotals or some business process

Both of these ideas discard the InvalidOrder table which is a workaround for poor indexing.

Suggestion 1:

Create a computed column

ALTER TABLE dbo.Order ADD PriceXQuantity AS Price * Quantity PERSISTED

Add an index

CREATE INDEX IX_Totals ON dbo.Order (OrderID) INCLUDE Quantity, PriceXQuantity)

See what happens

Suggestion 2:

Use an Indexed view

CREATE VIEW OrderTotals
WITH SCHEMABINDING
AS
select
    OrderId             =   r.OrderId
  , TotalQty            =   SUM(r.Quantity)
  , TotalGrossConsid    =   SUM(r.Price * r.Quantity)
  , COUNT_BIG(*) AS Dummy
from dbo.Order r
group by r.OrderId
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_OrderTotals ON OrderTotals (OrderId9
GO

You can use the computed column here too


How to keep history of a mysql table that has autonum as key?

In my opinion your best bet is adding a trigger to the prices table after the insert so that it adds the desired information in the history table. You will need another trigger that is called after update to deal with insert collitions.

After that you go on simply inserting in prices as you have been doing all along and let MySQL do the rest.

Example for insert trigger:

DELIMITER $$
CREATE TRIGGER do_history
AFTER INSERT ON prices
FOR EACH ROW
BEGIN
    /* Example insert */
    INSERT INTO history (a, validon, price) values (NEW.fkey, NOW(), NEW.price)
    /* Example insert end */
END $$
DELIMITER ;

Example for update trigger:

DELIMITER $$
CREATE TRIGGER do_history
AFTER UPDATE ON prices
FOR EACH ROW
BEGIN
    /* Example insert */
    INSERT INTO history (a, validon, price) values (NEW.fkey, NOW(), NEW.price - OLD.price)
    /* Example insert end */
END $$
DELIMITER ;

Please change the triggers' insert queries since I do not understand your history table and these examples will fail on duplicate ids.


Deciding the best way to partition (Postgresql)

It isn't even clear if you should be partitioning at all.

PostgreSQL's table partitioning is a bit primitive and comes with some limitations in enforcing referential integrity, etc. If your query pattern doesn't overwhelmingly favour filters on a particular field where you can benefit from constraint exclusion it might not help you much.

Partitioning can ease certain maintenance tasks, in particular bulk drops of data that rotates through by age. Dropping the whole of last month's data can be easier than doing a big slow DELETE with follow-up VACUUMing. This alone is rarely worth partitioning for, though.

You really do need to test for your workload and query pattern. Use the data in pg_stat_user_indexes and pg_stat_user_tables on your system as it's currently running to guide you about query patterns. I also recommend installing the pg_stat_statements extension, which will collect more info about query patterns.


What index strategy to use to improve multi-condition SELECT?

Privet Sergiy,

Index design and cost based query optimizers are complicated subjects, and this isn't the place to cover all aspects, so I will address your specific question.

To satisfy the WHERE clause, the 'optimal' index for your query example might be a composite index on (foo, baz). Why?

Think about a phone book directory, and foo and baz as last name and first name respectively. Let's say you are looking for all records in the phone book where the last name is 'doh', and the first name > 'John'. The composite index on last name, first name the phone directory uses allows you to quickly navigate to the 'doh' section, find the first 'John', and from there just follow the list one by one until you exhaust all 'Doh' last names. Could you do it with an index just on Last name? yes.. but you would need to scan all 'Doh' records. An index on First name only, means you have to scan all 'John's, and all following first names to find your 'Doh's.

If your table consists of just foo and baz, it's a no-brainer.
That said, the fact that your SELECT list uses a *, means that if the index doesn't contain all the data to satisfy the query, an additional 'lookup' will be needed for each row to get the rest of the columns.
This would be similar to an index at the end of the book, that has a pointer to the relevant page. You could potentially include all columns of the table in the index, but that may cause challenges for data modifications and bloat database size.

The optimizer now needs to decide between using the index, and performing potentially many 'lookups', and if it assumes there will be many of those, it may decide to simply scan the whole table and avoid the lookups.
This is why cost based optimizers require statistics on the columns to make the right decisions, some engines such as SQL Server also store complete histograms of value distribution for better accuracy.

Different RDBMS engines will handle this differently, so look for resources specific to the engine you are using.

HTH


Downgrading SQL Server database and moving it to another computer

There's no such thing as downgradning the physical database. You would have to (use a tool that) script out your objects (CREATE commands) and the data (as INSERTs or some BCP files). And hope that you don't use anything on your 2019 that isn't available on 2014.

Such tools can be BACPAC, SSDT, "Generate Scripts" in SSMS.


Migrate sharepoint database from SQL Server 2012 express to SQL Server 2012 Standard

Would it be possible to migrate my database to SQL Server Std?

Yes, a backup and restore approach would work for this situation. For a detailed migration steps (ignoring the ones that you dont need - as it is for migrating from lower to higher version), refer to this answer.

  Can I install my SQL Server 2012 Std on the same server I have of my SQL Server Express?

Yes, Express and standard editions can co-exists together. Make sure that your server have enough memory, CPU and disk space. Also, make sure to cap the Max memory setting on the instances appropriately.

  Is there any risk doing that in terms of sharepoint compatibility?

We have SharePoint running in our environment on SQL server 2012 standard edition, so I dont see any issues. What risks are you referring to ?

  NOTE: I have installed SQL Server 2012 Std on same server but the db size limit still persist, is this normal?

No that is not normal.  See Database size Maximum Capacity Specifications for SQL Server 2012.

Below script will give you more detailed info about the version you are running :

SELECT
   SERVERPROPERTY ('MachineName') AS PhysicalMachineName,
   SERVERPROPERTY ('ServerName') AS SQLServerName,
   SERVERPROPERTY ('Edition') AS ServerEdition,
   CAST(SUBSTRING(@@Version,charindex('SQL',@@version, 1),15) AS VARCHAR(255)) +  ' + '
       + CAST (SERVERPROPERTY ('productlevel')AS VARCHAR (50)) + ' + (Build'
       + CAST (SERVERPROPERTY ('ProductVersion') AS VARCHAR (50)) +')' AS ProductVersion,
    RIGHT(@@version, (Len(@@Version)-charindex('Windows',@@version, 1))+1) AS [O.S.],
 SERVERPROPERTY ('Collation') AS Collation


You need to restrict your sub-query to look for the specific tags of the outer query.

I have aliased your outer table t1 and inner table t2.

 CREATE TABLE #testing
    (
      Id INT ,
      Tag NVARCHAR(250) ,
      OptionText NVARCHAR(250)
    );

 INSERT INTO #testing
 VALUES ( 1, 'IsMobile', 'Yes' );
 INSERT INTO #testing
 VALUES ( 1, 'Mission', 'I am using this' );
 INSERT INTO #testing
 VALUES ( 1, 'Mission', 'I am buying this' );

 SELECT
    Id ,
    Tag ,
    STUFF(CAST(( SELECT
                    [text()] = ', ' + [OptionText]
                 FROM
                    #testing t2
                 WHERE
                    t2.Tag = t1.Tag -- Restrict rows returned in subquery
               FOR
                 XML PATH('') ,
                     TYPE
               ) AS VARCHAR(MAX)), 1, 2, '') OptionText
 FROM
    #testing t1
 GROUP BY
    Id ,
    Tag;


This is a situation that might benefit from use of a date table. https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

Then you can join on the date table for all dates between the DepartureDate and the ModelEndDate, something like this:

Select y.BlockID,
    DaysFreeJan = Sum(CASE WHEN d.MonthName = 'January' THEN 1 END),
    DaysFreeFeb = Sum(CASE WHEN d.MonthName = 'February'THEN 1 END),
    ...
  From dbo.YourTable As y
  Join dbo.DateTable On d
    On y.DepartureDate < d.DateKey
    And y.ModelEndDate >= d.DateKey
  Group By y.BlockID;

So each row of your table will join to multiple rows in your date table, then this query counts how many of those rows there are in each month.


Is it possible to detach some specific database from MongoDB Server?

I tried to search the answer with google, but without success.

I'm pretty sure MongoDB does not offer "offlining" or "detaching" databases like traditional RDBMS products do, at this time (hence you not being able to find anything on it).

Have you set storage.wiredTiger.engineConfig.cacheSizeGB
 and hostInfo.system.memLimitMB to limit the RAM being used by MongoDB? If not set it will default to 50% of (RAM - 1 GB), which might be your actual problem.

AFAIK Mongo shouldn't be loading unused database data into RAM, and the RAM to keep databases online should be negligible.


Issue identified.  The network admin didn't have the separate Azure domain environments trusted, although he said that he did.  I tried this and failed in our SQL DEV environment/domain in Azure, which isn't trusted.  I then, for sake of troubleshooting and communicating to the powers to be, tried it from our SQL QA environment/domain in Azure and it worked flawlessly!  The network admin compared the two environments and found the issue. Network issue after all.


A Script to insert dummy Data in all tables of Database

There are lots of cases that @AmmarR's solution doesn't handle - ROWVERSION/TIMESTAMP, computed columns, columns with check constraints, foreign keys, UNIQUEIDENTIFIER columns that default to NEWSEQUENTIALID(), date/time columns that default to GETDATE(), sparse columns, etc. etc.

Why reinvent the wheel? There are tools out there that can generate data for you and they've probably already thought about all of these restrictions. For example DTM Data Generator (not free, have not tested it) and Red-Gate SQL Data Generator (not free, have tested it and recommend it - also reviewed here).


Connecting to a Remote SQL Server 2008 Instance

The way you do this really depends on your network setup, corporate security policies, and what kind of infrastructure you have available to use.

What's worked really well for me is creating a totally separate management server on which SSMS, BIDS, etc., is installed, and all management of servers is done from there.

There are many advantages to doing this:

Management tools are installed and configured centrally, which itself does a couple of important things:

Alleviates the installation of management tools on the actual production servers (SQL Server 2012 can be installed on Server Core, which makes external management a necessity). This saves your production CPU cycles, memory, and (probably) expensive storage.
If you retire a server, the management tools don't go down with the ship, too.

If you need to manage multiple servers, it's the perfect place to install a CMS, and do things like centralized monitoring and logging.
It gives you an area you can test with and not affect production. (If you blow something up, you haven't blown up production!)
Eliminates the need to directly connect to the SQL servers from the outside (simply RDP to the management box).
You can land the O/S on less expensive or performant storage than the production boxes, thus saving $$.

This kind of thing is super easy to set up if you have a virtualized infrastructure available to use.


How to move snapshot Azure SQL db from prod to dev on a recurring basis

You can use CREATE DATABASE AS COPY to create a snapshot of production database, and then use the same CREATE DATABASE AS COPY option to create databases for development environments using the snapshot database. All this can be automated using an Azure Runbook. The runbook can run with a SQL login with elevated privileges to accomplish all copy activities.

CREATE DATABASE db_copy
    AS COPY OF ozabzw7545.db_original ( SERVICE_OBJECTIVE = 'P2' );

Adopting Azure Web Apps may help you, because of their ability to override application settings and connection strings from within the Azure Portal. This way you could allow your developers or infrastructure admins to deploy new code to the Azure Web App without letting them even see the appSettings and Connection Strings that particular environment uses.

Visual Studio Team Services resource groups and Microsoft Release Management Agents may be very useful for you.


Is there a way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases?

As long as you don't add data between the last refresh before the column add and after the column add, you should be fine.  If you must allow for this then the easiest thing would be to do a complete refresh at the end of your plan.  You shouldn't need to re-create the materialized view log.

Setup

--Create master table.
drop table t1;
create table t1 (id number(10) primary key, datetime date);
insert into t1 values (1, sysdate-1);
create materialized view log on t1 with primary key;

--Create materialized view table.
drop materialized view t2;
drop table t2;
create table t2 as (select * from t1);

--Create materialized view.
create materialized view t2 on prebuilt table with reduced precision
refresh fast on demand
as select id, datetime from t1;

--Show an empty materialized view.
select * from t2;

--Add data to t1.
insert into t1 values (2, sysdate);
commit;

--Refresh
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t2;

Planned Column Add

--Add Column.
alter table t1 add n varchar2(1);
alter table t2 add n varchar2(1);

--Re-create the materialized view.
drop materialized view t2 preserve table;
--***T1 updates between last refresh and the following statement will be lost.
create materialized view t2 on prebuilt table with reduced precision
   refresh fast on demand
   as select id, datetime, n from t1;

--Add data.
insert into t1 values (3,sysdate+1,'a');
commit;

--Refresh.
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t1;
select * from t2;


What are the major differences between mysql5.6 and mysql 5.7

The release notes for MySQL 5.7 are here.

"What's new in MySQL 5.7" can be found here.

Changes of note:

ONLY_FULL_GROUP_BY is now turned on by default, meaning MySQL
will no longer return random data when a user has incorrectly
specified columns in the where clause, but missed them out of the
group by clause.
Improvements to online DDL - more ALTER operations can be done
online without the storage engine having to make a copy of the object
in question.
InnoDB now supports MySQL-supported spatial data types
InnoDB supports tablespaces
Native JSON support
A new sys schema, with summarized performance data
Multi-source replication


MySQL takes his configuration from the my.cnf/ini file where you can activate or disable the modes or options.

There is also the possibility to put options to the program when it starts.

Please read up on the sql Modes or configuration that you want to change, but remember  the defaults are there for a cause, but the Mysql homepage has usual a  lot to tell about see, but read the warnings carefully. Everything could end in disaster.

I alos din't know where you see t taht the sql is empty, i am guessing that you mean https://dev.mysql.com/doc/workbench/en/wb-preferences-general-editors.html

Bujt that is as you can see something different


In the query you posted:

select * from <table_name>;

There's no such thing as the 100th-200th rows, because you don't specify an ORDER BY. Order isn't guaranteed unless you include the ORDER BY for a whole lot of interesting reasons, but that's not really the point here.

So to illustrate your point, let's use a table - I'm going to use the Users table from the Stack Overflow data dump, and run this query:

SELECT * FROM dbo.Users ORDER BY DisplayName;

By default, there's no index on the DisplayName field, so SQL Server has to scan the entire table, then sort it by DisplayName. Here's the execution plan:

It's not pretty - that's a lot of work, with an estimated subtree cost of around 30k. (You can see it by hovering your mouse over the select operator over at PasteThePlan.) So what happens if we only want rows 100-200? We can use this syntax in SQL Server 2012+:

SELECT * FROM dbo.Users ORDER BY DisplayName OFFSET 100 ROWS FETCH NEXT 100 ROWS ONLY;

The execution plan on that is pretty ugly too:

SQL Server's still scanning the whole table to build the sorted list just to give you your rows 100-200, and the cost is still around 30k. Even worse, this whole list will be rebuilt every time your query runs (because after all, someone might have changed their DisplayName.)

To make it go faster, we can create an nonclustered index on DisplayName, which is a copy of our table, sorted by that specific field:

CREATE INDEX IX_DisplayName ON dbo.Users(DisplayName);

With that index, our query's execution plan now does an index seek:

The query finishes instantly and has an estimated subtree cost of just 0.66 (as opposed to 30k).

In summary, if you organize the data in a way that supports the queries you frequently run, then yes, SQL Server can take shortcuts to make your queries go faster. If, on the other hand, all you have is heaps or clustered indexes, you're screwed.


This is an old verified bug in MySQL for InnoDB on larger tables.
As this has been patched quite some time ago, perhaps it's not the same situation, but it sure seems similar.

The workarounds for the older versions are:

Defragment in the filesystem. When you extend many tables the chunks for each end up very fragmented, so the defragmenting can be of benefit for table scans as well as dropping speed. If you don't have a defragmenting program, copying the files somewhere and copying them back will do the job.
When dropping, create a filesystem link to the IBD file. Drop the table in MySQL and the OS won't drop the file in the filesystem because the link exists, so the drop will be fast within MySQL. Remove the link and the OS will finish the deleting work in the background.

If that doesnt work... The answer in this question will allow you to delete ABOUT half of your data.
The idea being that you can remove enough data to stop InnoDB from hanging when you drop the table.


How do I list all available encoding types available in postgreSQL?

Or are the only encoding types ever available listed in Chapter 23.3 Character Set Support?

Yes. You can see the full list in the source code, if you insist

That said, it's highly abnormal to use any of these encoding except UTF-8 as an internal storage format. Unicode should support all of the useful characters in all of the other encodings. If you find UTF-8 acceptable, the only purpose these other encoding serve is to eliminate the need to create an ETL script for the COPY statement.


What is the default column name given to a TABLE() type with a single column

column_value Pseudocolumn

  When you refer to an XMLTable construct without the COLUMNS clause, or
  when you use the TABLE function to refer to a scalar nested table
  type, the database returns a virtual table with a single column. This
  name of this pseudocolumn is COLUMN_VALUE.

Example:

CREATE OR REPLACE TYPE MISSING_KEYS AS TABLE OF VARCHAR2(256);

SELECT column_value AS missing_key
FROM TABLE (MISSING_KEYS('A','B','C','D'))


When I try to update/set a datetime I get an invalid mysql syntax 1064

9.1.3 Date and Time Literals

Enclosing that datetime string in single quotes, 'YYYY-MM-DD HH:MM:SS' should work:

UPDATE xxxxx_eb_events
Set event_date = '2019-04-01 00:00:00';


Wait on the Database Engine recovery handle failed. Check the SQL Server error log for potential causes

Following can be reason for this error

The account which user selected on Server Configuration page window ( during installation)  is somehow not able to bring SQL Server database engine services online. Either is lacks privilege or it is corrupted. During installation of database engine services SQL Server tries to bring online database services as a internal process but due to startup account either corrupt or not having appropriate privileges it fails to do so and ultimately installation fails.

Other reason is when installation fails first time due to some reason and user uninstall failed installation from add remove program, the un-installation leaves account in broken state so any further attempt to install flashes this error message.

Solution:

Uninstall SQL Server completely from Add remove program and start new installation by right click on Setup and selecting run as admin. Now when you reach Server Configuration page please select NT Authority\System as SQL Server Database Engine service startup account.

http://social.technet.microsoft.com/wiki/contents/articles/24364.sql-server-troubleshooting-could-not-find-database-engine-startup-handle-error-during-installation.aspx

PS: If this does not helps please revert. Please follow steps mentioned in link carefully


How many B-tree indexes do I need to support lookups by any subset of n columns?

How many B-tree indexes do I need to support lookups by any subset of n columns

At least for Oracle (12.x) and Postgres the answer is: n

That is: one index for each column. Both DBMS are able to combine multiple indexes for a single query. In older Oracle versions you would need to use a single bitmap index on all columns. Oracle 12.1 can do a bitmap "scan" on the fly (don't know if that was already possible in 11.x - I don't have an 11.x installation to test).

Test setup:

create table idx_test (a int, b int, c int, d int, e int);
create index idx_a on idx_test(a);
create index idx_b on idx_test(b);
create index idx_c on idx_test(c);
create index idx_d on idx_test(d);

Now populate the table with million rows and random values for each column. I did this in Postgres using:

insert into idx_test (a,b,c,d,e)
select (random() * 10000 + 1)::int,
       (random() * 100000 + 1)::int,
       (random() * 1000000 + 1)::int,
       (random() * 100000 + 1)::int,
       (random() * 10000 + 1)::int
from generate_series(1,1000000);

Then copied the rows over to the Oracle server.

The following query

select *
from idx_test
where b = 42 or a = 24 or c = 100;

shows the following execution plan in Postgres:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on idx_test  (cost=6.25..166.36 rows=111 width=20) (actual time=0.032..0.244 rows=113 loops=1)
  Recheck Cond: ((b = 42) OR (a = 24) OR (c = 100))
  Heap Blocks: exact=113
  ->  BitmapOr  (cost=6.25..6.25 rows=111 width=0) (actual time=0.020..0.020 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_test_b_idx  (cost=0.00..1.96 rows=11 width=0) (actual time=0.009..0.009 rows=11 loops=1)
              Index Cond: (b = 42)
        ->  Bitmap Index Scan on idx_test_a_idx  (cost=0.00..2.27 rows=98 width=0) (actual time=0.007..0.007 rows=100 loops=1)
              Index Cond: (a = 24)
        ->  Bitmap Index Scan on idx_test_c_idx  (cost=0.00..1.93 rows=2 width=0) (actual time=0.003..0.003 rows=2 loops=1)
              Index Cond: (c = 100)
Planning time: 0.077 ms
Execution time: 0.270 ms

You can see that the corresponding index for each column was used. The execution plan for Oracle 12.1 is nearly identical:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2332290563

--------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     43 |  2795 |   740   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| IDX_TEST |     43 |  2795 |   740   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS       |          |        |       |            |
|   3 |    BITMAP OR                        |          |        |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  5 |      INDEX RANGE SCAN               | IDX_C    |        |       |     3   (0)|
|   6 |     BITMAP AND                      |          |        |       |            |
|   7 |      BITMAP CONVERSION FROM ROWIDS  |          |        |       |            |
|*  8 |       INDEX RANGE SCAN              | IDX_A    |        |       |     3   (0)|
|   9 |      BITMAP CONVERSION FROM ROWIDS  |          |        |       |            |
|* 10 |       INDEX RANGE SCAN              | IDX_B    |        |       |     3   (0)|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("C"=100)
   8 - access("A"=24)
  10 - access("B"=42)

A query with only two columns will only use two indexes:

select *
from idx_test
where a = 1001 and b = 45877;

Postgres:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on idx_test  (cost=4.48..5.99 rows=1 width=20) (actual time=0.118..0.118 rows=0 loops=1)
  Recheck Cond: ((b = 45877) AND (a = 1001))
  ->  BitmapAnd  (cost=4.48..4.48 rows=1 width=0) (actual time=0.117..0.117 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_test_b_idx  (cost=0.00..1.96 rows=11 width=0) (actual time=0.075..0.075 rows=11 loops=1)
              Index Cond: (b = 45877)
        ->  Bitmap Index Scan on idx_test_a_idx  (cost=0.00..2.27 rows=98 width=0) (actual time=0.038..0.038 rows=93 loops=1)
              Index Cond: (a = 1001)
Planning time: 0.150 ms
Execution time: 0.157 ms

And Oracle:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 3618970768

--------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     78 |  5070 |    22   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| IDX_TEST |     78 |  5070 |    22   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS       |          |        |       |            |
|   3 |    BITMAP AND                       |          |        |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  5 |      INDEX RANGE SCAN               | IDX_A    |     43 |       |     3   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  7 |      INDEX RANGE SCAN               | IDX_B    |     43 |       |     3   (0)|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"=1001)
   7 - access("B"=45877)

The indexes are also used for an OR condition:

select *
from idx_test
where a = 1001 or b = 45877;

Postgres:

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on idx_test  (cost=4.29..161.31 rows=109 width=20) (actual time=0.053..0.240 rows=104 loops=1)
  Recheck Cond: ((a = 1001) OR (b = 45877))
  Heap Blocks: exact=104
  ->  BitmapOr  (cost=4.29..4.29 rows=109 width=0) (actual time=0.038..0.038 rows=0 loops=1)
        ->  Bitmap Index Scan on idx_test_a_idx  (cost=0.00..2.27 rows=98 width=0) (actual time=0.030..0.030 rows=93 loops=1)
              Index Cond: (a = 1001)
        ->  Bitmap Index Scan on idx_test_b_idx  (cost=0.00..1.96 rows=11 width=0) (actual time=0.007..0.007 rows=11 loops=1)
              Index Cond: (b = 45877)
Planning time: 0.111 ms
Execution time: 0.273 ms

Oracle:

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------
Plan hash value: 2853963857

--------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | E-Rows |E-Bytes| Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     62 |  4030 |   879   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| IDX_TEST |     62 |  4030 |   879   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS       |          |        |       |            |
|   3 |    BITMAP OR                        |          |        |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  5 |      INDEX RANGE SCAN               | IDX_A    |        |       |     3   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS   |          |        |       |            |
|*  7 |      INDEX RANGE SCAN               | IDX_B    |        |       |     3   (0)|
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"=1001)
   7 - access("B"=45877)

A quick test with SQL Server 2016 shows that it does essentially the same thing:

StmtText                                                                                | StmtId | NodeId | Parent | PhysicalOp   | LogicalOp  | Argument
-------------------------------------------------------------------------------------------------------------------------------------+--------+--------+--------+--------------+------------+------------------------------------------------------------------------------------------------------
select * from idx_test where b = 42 and a = 24                                                                                |      1 |      1 |      0 |              |            | 1
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))                                                                          |      1 |      2 |      1 | Nested Loops | Inner Join | OUTER REFERENCES:([Bmk1000])
       |--Hash Match(Inner Join, HASH:([Bmk1000])=([Bmk1000]), RESIDUAL:([Bmk1000] = [Bmk1000]))                                     |      1 |      4 |      2 | Hash Match   | Inner Join | HASH:([Bmk1000])=([Bmk1000]), RESIDUAL:([Bmk1000] = [Bmk1000])
       |    |--Index Seek(OBJECT:([TestDB].[dbo].[idx_test].[idx_b]), SEEK:([TestDB].[dbo].[idx_test].[b]=(42)) ORDERED FORWARD)     |      1 |      5 |      4 | Index Seek   | Index Seek | OBJECT:([TestDB].[dbo].[idx_test].[idx_b]), SEEK:([TestDB].[dbo].[idx_test].[b]=(42)) ORDERED FORWARD
       |    |--Index Seek(OBJECT:([TestDB].[dbo].[idx_test].[idx_a]), SEEK:([TestDB].[dbo].[idx_test].[a]=(24)) ORDERED FORWARD)     |      1 |      6 |      4 | Index Seek   | Index Seek | OBJECT:([TestDB].[dbo].[idx_test].[idx_a]), SEEK:([TestDB].[dbo].[idx_test].[a]=(24)) ORDERED FORWARD
       |--RID Lookup(OBJECT:([TestDB].[dbo].[idx_test]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)                          |      1 |     16 |      2 | RID Lookup   | RID Lookup | OBJECT:([TestDB].[dbo].[idx_test]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD

Is this the most efficient index access possible? Probably not, but it is most probably the best compromise between indexing overhead and query flexibility.


What's the anatomy of a columnstore index?

Columnstore Structure

Columnstore data is physically stored in one or more segments (regular LOB allocation units) per column, and may also be partitioned in the usual way.  Each segment contains roughly one million rows of highly-compressed values or value references (several compression techniques are available).  A value reference links to an entry in one of up to two hash dictionaries.

Dictionaries are pinned in memory during query execution, with data value IDs from the segment being looked in the dictionary whenever execution requires the actual data value (this lookup is deferred for as long as possible for performance reasons).

Segments also have a header record containing metadata such as the minimum and maximum values stored in the segment.  Information from the header can often be used to eliminate complete partitions from processing at execution time.  Header record information is stored in the usual LOB data root structure, so eliminating a segment means the Storage Engine can skip reading the LOB data pages from physical storage entirely.  Maximizing the potential for elimination can require careful design, including a dependency on the clustered index order at the time the Columnstore index is built.

Specific Plan Operators

SQL Server 2012 introduces a new execution mode called Batch Mode.  In this mode, packets of roughly 1000 rows are passed between operators, significantly improving processor utilization efficiency.  Within each packet, columnar data is represented as a vector.  Not all plan operators support batch mode operation, but examples of those that do include Columnstore Index Scan, Hash Inner Join, Batch Hash Table Build, Bitmap Filter, Hash Aggregate (not scalar aggregates), Filter, and Compute Scalar (for projection and expression evaluation).  Query execution plans have been enhanced to show estimated and actual execution mode.

Anti-Patterns

There are a large number of restrictions in the first release, including constraints on allowable data types.  Most common types are supported; unsupported data types include DECIMAL with a precision greater than 18 digits, (N)VARCHAR(MAX), UNIQUEIDENTIFIER, CLR types, and (VAR)BINARY.

Use of string types, OUTER JOIN, IN, EXISTS, NOT IN, OR, UNION ALL may result in significantly reduced performance (Row Mode execution), unless workarounds are employed that typically involve unusual syntax rewrites as shown in the linked articles in this section.

More Information

Remus Rusanu has blogged a great overview here.


MySQL query to get sum of count distinct column value

I would start with using the same expression in both select and group by:

SELECT COUNT(DISTINCT l.userid) AS Logins
     , DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS Month
FROM databasename.tablename l
WHERE l.action = 'loggedin'
  AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018'
GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M');

If you want to count the total number of distinct logins you can add ROLLUP as ypercubeᵀᴹ suggests. You can use COALESCE to get a label for the total:

SELECT COUNT(DISTINCT l.userid) AS Logins
     , COALESCE(DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M'), 'TOTAL') AS Month
FROM databasename.tablename l
WHERE l.action = 'loggedin'
  AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018'
GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') WITH ROLLUP;

In general, the total is less than the sum of the parts. It will be the same only if the logins from Jan is disjoint with the logins from Feb.

If you instead want to sum the number of distinct logins you can do it like:

SELECT SUM(logins), COALESCE(Month, 'Summary') as Month
FROM (
    SELECT COUNT(DISTINCT l.userid) AS Logins
         , DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M') AS Month
    FROM databasename.tablename l
    WHERE l.action = 'loggedin'
      AND YEAR(FROM_UNIXTIME(l.timecreated)) = '2018'
    GROUP BY DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%M')
) AS T
GROUP BY Month WITH ROLLUP;

This will result in the sum of the number of distinct logins for each month, which will be 49 in your case.


With LocalAdmin become sysadmin SQL Server

For such a situation we have made use of PSExec utility to get regain access on the SQL server.

You can download the utility from here , extract and copy the exe file to the drive location from where you can run below command.

Once downloaded you need to open the cmd with admin and run below:

PsExec -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"

The path above may vary, like for one of our SQL 2k8 , we found the path to be:

"C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

This will make you connect to the current via sever Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM.

YOu can create the required login from here:


Here is a functional (albeit basic) example for you.  Triggers in SQL Server can be either AFTER or INSTEAD and can apply to any DML (INSERT, UPDATE or DELETE), even at the same time.  Inside the scope of each trigger is a virtual table called:

INSERTED - what is intended to be.
DELETED - what the old values were.

These virtual tables contain every column from the base table and make modification to either the base table or other tables.

CAVEATS (or basic reminders):

MAKE SURE that the statement inside your trigger can handle multiple rows properly.  A common mistake that I see is people making the logic inside the trigger only work for one row at a time.
These can kill performance, use them sparingly and keep the logic as simple as possible.
Bulk Inserts do not typically fire triggers, but can be configured to do so.
AVOID cursors inside triggers.
You cannot try/catch inside a trigger.  Any exception that happens in there WILL bubble out to the caller.

FINALLY

Take care storing the passwords using a proper cryptographic library.  I used VARCHAR in my example below to better illustrate the trigger logic.

DROP TABLE IF EXISTS dbo.FormerPasswords
DROP TABLE IF EXISTS dbo.Users

GO

CREATE TABLE dbo.Users
    (
    UsersID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , UserName NVARCHAR(200) NOT NULL
    , CurrentPassword VARCHAR(64) NOT NULL
    )

CREATE TABLE dbo.FormerPasswords
    (
    FormerPasswords INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , UsersID INT NOT NULL REFERENCES dbo.Users (UsersID)
    , PriorPasswords VARCHAR(64) NOT NULL
    , DateLastUsed_UTC DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME())
    )

GO

CREATE OR ALTER TRIGGER trgUsers
    ON dbo.Users
    AFTER UPDATE
AS
BEGIN

    INSERT INTO dbo.FormerPasswords
    (UsersID, PriorPasswords)
    SELECT D.UsersID
        , D.CurrentPassword
    FROM DELETED AS D

END

GO

INSERT INTO dbo.Users
(UserName, CurrentPassword)
VALUES ('jsmith', 'original 1')
    , ('jdoe', 'original 2')

UPDATE dbo.Users
SET CurrentPassword = 'updated 1'
WHERE UserName = 'jsmith'

SELECT * FROM dbo.Users
SELECT * FROM dbo.FormerPasswords


The basic misunderstanding: "localhost" is not a local connection. Your "trust" line in pg_hba.conf is not applicable for your connection:

local   all   all   trust

That only applies for connections via Unix-domain socket. Best choice for you is to connect via this route. Per pgAdmin documentation:

  The host is the IP address of the machine to contact, or the fully
  qualified domain name. On Unix based systems, the address field may be
  left blank to use the default PostgreSQL Unix Domain Socket on the
  local machine, or be set to an alternate path containing a PostgreSQL
  socket. If a path is entered, it must begin with a “/”. The port
  number may also be specified.

Bold emphasis mine.

More about connecting without password in this related answer on SO:
Run batch file with psql command without password


You should be able to do it with a dateadd and getdate functions:

SELECT TOP ( 100 ) PERCENT
        STOK_KODU ,
        COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                          ELSE 0
                     END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                  ELSE 0
                                             END), 0) AS StokToplam1
FROM    s_hareket
WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
        AND ( tarih BETWEEN dateadd(month,-6,GETDATE()) AND GETDATE() )
GROUP BY STOK_KODU
ORDER BY STOK_KODU

If I understand your question correctly, you want to union the results, then pivot it. That leads to the following query:

SELECT * FROM
    (SELECT TOP ( 100 ) PERCENT
            'last' AS t,
            STOK_KODU ,
            COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                              ELSE 0
                         END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                      ELSE 0
                                                 END), 0) AS StokToplam1
    FROM    s_hareket
    WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
            AND ( tarih BETWEEN dateadd(month,-6,GETDATE()) AND GETDATE() )
    GROUP BY STOK_KODU
    ORDER BY STOK_KODU

    UNION ALL
    SELECT TOP ( 100 ) PERCENT
            'first' AS t,
            STOK_KODU ,
            COALESCE(SUM(CASE WHEN kod1 = 'G' THEN kod2
                              ELSE 0
                         END), 0) - COALESCE(SUM(CASE WHEN kod1 = 'C' THEN kod2
                                                      ELSE 0
                                                 END), 0) AS StokToplam1
    FROM    s_hareket
    WHERE   ( STOK_KODU BETWEEN '01001' AND '75000' )
            AND ( tarih BETWEEN '2011-01-01' and '2011-06-30' )
    GROUP BY STOK_KODU
    ORDER BY STOK_KODU) AS sourceTable
PIVOT (
    SUM(StokToplam1)
    FOR T IN ([first], [last])
) AS p


MySQL 5.7 foreign keys constraint invalid

I have solution to some of your problems

Q) Why does this happen?

A) because invalid or missing values in data-change statements such as INSERT or UPDATE, ...etc

Note disabling FOREIGN_KEY_CHECKS one of the main causes of this violation.

Q) Preventing or workaround
 There is some restriction   you can add to your database, I'm not sure if this practical (DBA should be able to decide)

SET sql_mode = 'STRICT_TRANS_TABLES';
SET sql_mode = 'STRICT_ALL_TABLES';

you can also use the following script https://github.com/michaelirey/mysql-foreign-key-checker to check foreign key problems and fix them.

Regarding rebuild / optimize table this may cause problem, it happened to me once on SQL server. not sure about MySQL.

my bad since the answer is not organized enough, I will try to re-organize it soon.


How do I delete only related records in a multi-key MERGE in SQL Server?

You can filter out the rows you need to consider from the target table in a CTE and use the CTE as the target in the merge.

WITH T AS
(
  SELECT M.LeftId,
         M.RightId,
         M.CustomValue
  FROM @Mappings AS M
  WHERE EXISTS (SELECT *
                FROM @Values AS V
                WHERE M.LeftId = V.LeftId)
)
MERGE T
USING @Values AS S
ON T.LeftId = S.LeftId and
   T.RightId = S.RightId
WHEN NOT MATCHED BY TARGET THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (S.LeftId, S.RightId, S.CustomValue)
WHEN MATCHED THEN
  UPDATE SET CustomValue = S.CustomValue
WHEN NOT MATCHED BY SOURCE THEN
  DELETE
;


How do I import a dump for a deleted tablespace?

There is a good chance that the default (and/or temporary) tablespace for MYSCHEMA was dropped. If that is the case, give MYSCHEMA a new tablespace, with quota and re-run.
This can be checked by viewing the entries in dba_users.
This is a bug and should normally not occur.


ERROR 2026 (HY000): SSL connection error: Unable to get private key

You are trying to connect to the remote host using the server file of the master.

The reason it is working with only the CA is presumably the SSL configuration on the slave has it's own certificate signed by the same CA.

What you seem to be trying to do is use client certificate authentication.  If that is the case you need an additional client certificate that is recognised on the slave to be used as the certificate you use when trying to do a client connection.

IN simple terms, the server-cert is used to provide the SSL negotiation and is provided by the server to clients to allow the clients to encrypt their traffic in a way that the server can decrypt.

Each server would have its own server-cert that identifies its hostname and allows clients to connect and verify they are connecting to the expected server.

A client certificate is normally a separate certificate and is unrelated to the server certificate on either the master or the slave.


I wrote a blog post showing some sample BCP commands, including how to create the format file, at https://www.sqlserverscience.com/tools/example-bcp-export-import-commands/

This command should create the format file that is essential for import via bcp:

bcp "dbo.Oranges" format nul -S "FRUIT\PEARS" -T -d "Fruit" -n -f "C:\some\path\Oranges.fmt" -x

dbo.Oranges is the table name.  FRUIT\PEARS is the SQL Server instance name.  Fruit is the database name.

If you're having trouble with the XML file, I would recommend using the native binary format generated by BCP  It ensures the export and import will work without any data loss, and is fully supported across various versions of SQL Server.


How do I backup a database from MySQL 32-bit and restore it to MySQL 64-bit?

mysqldump is a separate binary and not a command within the MySQL console. Furthermore it is spelled with a p in the end (you had a b there!).

So just use your command on the ordinary command line and it should work.

> mysqldump -u root -p "Isys-TMS(EEI1)" > isystms.sql

MySQL reference manual


Get multiple column and row values in a sigle row

This process is called pivot.
This is mostly done with GROUP BY and using MAX or SUM.

Create table/insert data.

CREATE TABLE t
    (`KEY` VARCHAR(6), `language_id` INT, `language_value` VARCHAR(13))
;

INSERT INTO t
    (`KEY`, `language_id`, `language_value`)
VALUES
    ('SELECT', 1, 'Select Lang 1'),
    ('SELECT', 2, 'Select lang 2'),
    ('GET', 2, 'get lang 2')
;

Query

SELECT
   t.key
 , MAX(CASE WHEN t.language_id = 1 THEN t.language_value END) AS language_val_1
 , MAX(CASE WHEN t.language_id = 2 THEN t.language_value END) AS language_val_2
FROM
 t
GROUP BY
 t.key
ORDER BY
 t.key ASC

Result

key     language_val_1  language_val_2
------  --------------  ----------------
GET     (NULL)          get lang 2
SELECT  Select Lang 1   Select lang 2

Searching for KEY "GET".

You need to put the pivot query as a delivered table so you can filter with a WHERE statement.

Query

SELECT
    t_pivot.key
  , t_pivot.language_val_1
  , t_pivot.language_val_2
FROM (

  SELECT
     t.key
   , MAX(CASE WHEN t.language_id = 1 THEN t.language_value END) AS language_val_1
   , MAX(CASE WHEN t.language_id = 2 THEN t.language_value END) AS language_val_2
  FROM
   t
  GROUP BY
   t.key
  ORDER BY
   t.key ASC
)
 AS
   t_pivot
WHERE
 t_pivot.key = 'GET'

Result

I got a different result as your example.
But it looks like your example result is wrong.

key     language_val_1  language_val_2
------  --------------  ----------------
GET     (NULL)          get lang 2


AWS Aurora MySQL Serverless CPU utilization is growing for unknown reason

What solved it for me was setting the min and max instance count higher than before (forcing aurora to scale up, for example from min 2 ACU to min 4 ACU), and after scaling restoring the scaling settings.

I used 'Force scaling the capacity to the specified values when the timeout is reached' and 'Apply immediately'.

It also solved another issue we had, the 'Serverless database capacity' indicated 4 ACU while it was actually 2 ACU (as seen in the picture)


Necessary to include clustered index columns in non-clustered indexes?

In SQL Server the clustered index key column(s) are always added in to the non clustered index to act as a row locator (Ref: More About Nonclustered Index Keys).

For an NCI declared as unique they are added as an included column otherwise they are added to the end of the key.

You might want to add the columns in explicitly if the default placement is not optimum for your queries. For example if you want to control the ASC/DESC direction or you want to control the position of the key columns in the index.

CREATE TABLE T
(
A INT,
B INT,
C INT ,
PRIMARY KEY CLUSTERED (B DESC, C DESC)
)

/*Implicitly adds B DESC, C DESC to end of key*/
CREATE NONCLUSTERED INDEX ix1 ON T(A ASC)

/*No sort operation*/
SELECT  *
FROM T
ORDER BY A ASC,B DESC, C DESC

/*
But the avove index won't be able to seek into A,C
and will need a residual predicate after seeking into A.

For the following query
*/

SELECT  *
FROM T
WHERE A=1 AND C > 4
ORDER BY C ASC, B DESC

/*This index explicitly controlling the key column position
  and direction would be better*/
CREATE NONCLUSTERED INDEX ix2 ON T(A ASC, C ASC, B DESC)


How is data of multi valued attribute say phone number of person relation is stored in table?

When you say a multi-valued attribute, for a phone number for example, you mean because the phone number has multiple parts to it - area code, suffix, and prefix? If so, how you store it just depends on your use case for it.
If your use case is to just store it so it can be later recalled and displayed in the application, then storing the whole phone number as is, in a string-based field (e.g. VARCHAR in SQL Server) is sufficient.
If you need to be able to query about different parts of the phone number, then storing it as multiple separate INT fields for each part would be more appropriate and easier to work with, e.g. AreaCode, PhonePrefix, and PhoneSuffix. That way you can index each part of the phone number for efficient querying, and it's easy to query for things like, "who are all the people in area code 631?" by just doing a query of the kind SELECT * FROM People WHERE AreaCode = 631, etc.
It truly just depends on your use cases.


Generate the statistics of a table after truncating it and then repopulate it?

As a general guidelines, it is usually recommended to do a gather stats on table whenever more than 10% of the rows are modified. So in your case, you will need to do a stats gather post data load.
The index stats gather are taken care of if we do table stats gather using cascade option.


How to have different server config on replication server for postgres_fdw?

If you define the foreign server using host and port connection parameters, these data are part of the PostgreSQL metadata and replicated. Streaming replication creates an identical copy of the database.
So you'd have to define the foreign server using connection parameters that refer to a file outside the database proper.
What comes to mind is the service connection parameter. With that, the actual connection parameters are taken from a file pg_service.conf that is outside the database. Then all you have to do is provide a different service file on the standby server.


SP or query to dump "password policy" checkbox for all logins

Here's the start of a report:

SELECT
    sp.name AS LoginName,
    sl.is_policy_checked AS IsPasswordPolicyEnforced
    FROM sys.server_principals sp
    LEFT OUTER JOIN sys.sql_logins sl ON sl.principal_id = sp.principal_id
    WHERE sp.type IN ('S', 'G', 'U');

Note that this property only applies to SQL logins.


Have you considered using a trace to audit logins and/or triggers.

in case you find a trigger using this particular login the following post - Login failed due to trigger execution underlines the security context (see Microsoft answers) in which logon triggers are executed.

Hope this helps.


mysql showing two select statements as one output

You can combine them like this:

select
    count(CASE WHEN sub like '%bar%' or comment like '%bar%' THEN postid ELSE NULL END) AS count_bar,
    count(CASE WHEN sub like '%foo%' or comment like '%foo%' THEN postid ELSE NULL END) AS count_foo
from
    myTable
where
    (sub like '%bar%' or comment like '%bar%'
     or sub like '%foo%' or comment like '%foo%')
    and replies IS NOT NULL
group
    by time div 3600
order by
    time div 3600 asc;

Note that I also change and replies IS NOT NULL, assuming you have rows that are actually NULL. With the quotes you used, you treated it as a string, but NULL is really nothing. You can't compare it like other datatypes, you have to use the special IS NULL or IS NOT NULL comparison.


How to join sys.columns and sys.synonyms table using object_id column

object_id() does not work for remote databases.

The documentation states:

  For objects that are not found in the sys.objects catalog view, obtain
  the object identification numbers by querying the appropriate catalog
  view

SQL that provides an answer to your query requirement is here.


Is it possible to enter a missing sequence number into another table, on exception in a Trigger

EXCEPTION
  WHEN OTHERS THEN
        SEQ_AUDIT_PKG.Inse...

is a bad idea, because 'when others' could be anything. eg, you might have a datafile corruption, or an out of memory error.  You dont want to be doing a sequence audit in that respect.  Similarly, when that primary trigger fails, you want to be sending that back to the calling program, not telling them that "Hey, all is well" (which is when WHEN-OTHERS typically will do).

You would need your SEQ_AUDIT_PKG.InsertMissingSequence to be an autonomous transaction so that you could log the information outside the transaction scope of the trigger.

But having said all that ..... just dont do it :-)

Sequences have gaps...period.  You can

nocache them
pin them in the shared pool
carefully police shutdown abort commands
capture unused ones with an autonomous transaction

and you'll still eventually get some missing and any promises you've made about no gaps become moot.  And the complexity cost toward meeting this goal is just not worth it.


Getting @@ROWCOUNT when there is an insert query in SQL-SERVER?

@@ROWCOUNT is set after each statement so the value printed is for the SET statement instead of the INSERT statement.

Save the @@ROWCOUNT in a local variable in order to use it later in code:

EXEC (@SQL);

DECLARE @RowCount int = @@ROWCOUNT;

IF @RowCount > 0
BEGIN
    SET @RowFlag = 1;
END;

PRINT @RowCount;