How to handle multiple hierarchies in a data model?

Jeff Moden has written two excellent articles on SQL Hierarchies here (Hierarchies on Steroids #1) and here (Hierarchies on Steroids #2) which present efficient SQL algorithms for converting hierarchies stored as an Adjacency List (ie children have a Parent pointer - easier to visualize and more efficient to create) to a temporary table organized as nested sets (more efficient for reporting).

Given Jeff's good work in describing how to efficiently convert to Nested Sets as needed, I would recommend storing and maintaining your hierarchies as a Adjacency Lists.

By making each Hierarchy an independent table you will gain the benefit of decoupling the hierarchies from the base patient data, facilitating the addition of additional hierarchies as required.

The Code (Thank you Jeff):

 CREATE PROCEDURE dbo.RebuildNestedSets AS
/****************************************************************************
 Purpose:
 Rebuilds a "Hierarchy" table that contains the original Adjacency List,
 the Nested Sets version of the same hierarchy, and several other useful
 columns of data some of which need not be included in the final table.

 Usage:
 EXEC dbo.RebuildNestedSets

 Progammer's Notes:
 1. As currently written, the code reads from a table called dbo.Employee.
 2. The Employee table must contain well indexed EmployeeID (child) and
    ManagerID (parent) columns.
 3. The Employee table must be a "well formed" Adjacency List. That is, the
    EmployeeID column must be unique and there must be a foreign key on the
    ManagerID column that points to the EmployeeID column. The table must not
    contain any "cycles" (an EmployeeID in its own upline). The Root Node
    must have a NULL for ManagerID.
 4. The final table, named dbo.Hierarchy, will be created in the same
    database as where this stored procedure is present.  IT DOES DROP THE
    TABLE CALLED DBO.HIERARCHY SO BE CAREFUL THAT IT DOESN'T DROP A TABLE
    NEAR AND DEAR TO YOUR HEART.
 5. This code currently has no ROLLBACK capabilities so make sure that you
    have met all of the requirements (and, perhaps, more) cited in #3 above.

 Dependencies:
 1. This stored procedure requires that the following special purpose HTally
    table be present in the same database from which it runs.

--===== Create the HTally table to be used for splitting SortPath
 SELECT TOP 1000 --(4 * 1000 = VARBINARY(4000) in length)
        N = ISNULL(CAST(
                (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1)*4+1
            AS INT),0)
   INTO dbo.HTally
   FROM master.sys.all_columns ac1
  CROSS JOIN master.sys.all_columns ac2
;
--===== Add the quintessential PK for performance.
  ALTER TABLE dbo.HTally
    ADD CONSTRAINT PK_HTally
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;

 Revision History:
 Rev 00 - Circa 2009  - Jeff Moden
        - Initial concept and creation.
 Rev 01 - PASS 2010   - Jeff Moden
        - Rewritten for presentation at PASS 2010.
 Rev 02 - 06 Oct 2012 - Jeff Moden
        - Code redacted to include a more efficient, higher performmance
          method of splitting the SortPath using a custom HTally Table.
****************************************************************************/
--===========================================================================
--      Presets
--===========================================================================
--===== Suppress the auto-display of rowcounts to prevent from returning
     -- false errors if called from a GUI or other application.
    SET NOCOUNT ON;

--===== Start a duration timer
DECLARE @StartTime DATETIME,
        @Duration  CHAR(12);
 SELECT @StartTime = GETDATE();

--===========================================================================
--      1.  Read ALL the nodes in a given level as indicated by the parent/
--          child relationship in the Adjacency List.
--      2.  As we read the nodes in a given level, mark each node with the
--          current level number.
--      3.  As we read the nodes in a given level, convert the EmployeeID to
--          a Binary(4) and concatenate it with the parents in the previous
--          level's binary string of EmployeeID's.  This will build the
--          SortPath.
--      4.  Number the rows according to the Sort Path.  This will number the
--          rows in the same order that the push-stack method would number
--          them.
--===========================================================================
--===== Conditionally drop the final table to make reruns easier in SSMS.
     IF OBJECT_ID('FK_Hierarchy_Hierarchy') IS NOT NULL
        ALTER TABLE dbo.Hierarchy
         DROP CONSTRAINT FK_Hierarchy_Hierarchy;

     IF OBJECT_ID('dbo.Hierarchy','U') IS NOT NULL
         DROP TABLE dbo.Hierarchy;

RAISERROR('Building the initial table and SortPath...',0,1) WITH NOWAIT;
--===== Build the new table on-the-fly including some place holders
   WITH cteBuildPath AS
( --=== This is the "anchor" part of the recursive CTE.
     -- The only thing it does is load the Root Node.
 SELECT anchor.EmployeeID,
        anchor.ManagerID,
        HLevel   = 1,
        SortPath =  CAST(
                        CAST(anchor.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000)) --Up to 1000 levels deep.
   FROM dbo.Employee AS anchor
  WHERE ManagerID IS NULL --Only the Root Node has a NULL ManagerID
  UNION ALL
 --==== This is the "recursive" part of the CTE that adds 1 for each level
     -- and concatenates each level of EmployeeID's to the SortPath column.
 SELECT recur.EmployeeID,
        recur.ManagerID,
        HLevel   =  cte.HLevel + 1,
        SortPath =  CAST( --This does the concatenation to build SortPath
                        cte.SortPath + CAST(Recur.EmployeeID AS BINARY(4))
                    AS VARBINARY(4000))
   FROM dbo.Employee      AS recur WITH (TABLOCK)
  INNER JOIN cteBuildPath AS cte
          ON cte.EmployeeID = recur.ManagerID
) --=== This final INSERT/SELECT creates the Node # in the same order as a
     -- push-stack would. It also creates the final table with some
     -- "reserved" columns on the fly. We'll leave the SortPath column in
     -- place because we're still going to need it later.
     -- The ISNULLs make NOT NULL columns
 SELECT EmployeeID = ISNULL(sorted.EmployeeID,0),
        sorted.ManagerID,
        HLevel     = ISNULL(sorted.HLevel,0),
        LeftBower  = ISNULL(CAST(0 AS INT),0), --Place holder
        RightBower = ISNULL(CAST(0 AS INT),0), --Place holder
        NodeNumber = ROW_NUMBER() OVER (ORDER BY sorted.SortPath),
        NodeCount  = ISNULL(CAST(0 AS INT),0), --Place holder
        SortPath   = ISNULL(sorted.SortPath,sorted.SortPath)
   INTO dbo.Hierarchy
   FROM cteBuildPath AS sorted
 OPTION (MAXRECURSION 100) --Change this IF necessary
;
RAISERROR('There are %u rows in dbo.Hierarchy',0,1,@@ROWCOUNT) WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Using the information created in the table above, create the
--      NodeCount column and the LeftBower and RightBower columns to create
--      the Nested Sets hierarchical structure.
--===========================================================================
RAISERROR('Building the Nested Sets...',0,1) WITH NOWAIT;

--===== Declare a working variable to hold the result of the calculation
     -- of the LeftBower so that it may be easily used to create the
     -- RightBower in a single scan of the final table.
DECLARE @LeftBower INT
;
--===== Create the Nested Sets from the information available in the table
     -- and in the following CTE. This uses the proprietary form of UPDATE
     -- available in SQL Serrver for extra performance.
   WITH cteCountDownlines AS
( --=== Count each occurance of EmployeeID in the sort path
 SELECT EmployeeID = CAST(SUBSTRING(h.SortPath,t.N,4) AS INT),
        NodeCount  = COUNT(*) --Includes current node
   FROM dbo.Hierarchy h,
        dbo.HTally t
  WHERE t.N BETWEEN 1 AND DATALENGTH(SortPath)
  GROUP BY SUBSTRING(h.SortPath,t.N,4)
) --=== Update the NodeCount and calculate both Bowers
 UPDATE h
    SET @LeftBower   = LeftBower = 2 * NodeNumber - HLevel,
        h.NodeCount  = downline.NodeCount,
        h.RightBower = (downline.NodeCount - 1) * 2 + @LeftBower + 1
   FROM dbo.Hierarchy h
   JOIN cteCountDownlines downline
     ON h.EmployeeID = downline.EmployeeID
;
RAISERROR('%u rows have been updated to Nested Sets',0,1,@@ROWCOUNT)
WITH NOWAIT;

RAISERROR('If the rowcounts don''t match, there may be orphans.'
,0,1,@@ROWCOUNT)WITH NOWAIT;

--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Prepare the table for high performance reads by adding indexes.
--===========================================================================
RAISERROR('Building the indexes...',0,1) WITH NOWAIT;

--===== Direct support for the Nested Sets
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT PK_Hierarchy
        PRIMARY KEY CLUSTERED (LeftBower, RightBower) WITH FILLFACTOR = 100
;
 CREATE UNIQUE INDEX AK_Hierarchy
     ON dbo.Hierarchy (EmployeeID) WITH FILLFACTOR = 100
;
  ALTER TABLE dbo.Hierarchy
    ADD CONSTRAINT FK_Hierarchy_Hierarchy FOREIGN KEY
        (ManagerID) REFERENCES dbo.Hierarchy (EmployeeID)
     ON UPDATE NO ACTION
     ON DELETE NO ACTION
;
--===== Display the cumulative duration
 SELECT @Duration = CONVERT(CHAR(12),GETDATE()-@StartTime,114);
RAISERROR('Cumulative Duration = %s',0,1,@Duration) WITH NOWAIT;

--===========================================================================
--      Exit
--===========================================================================
RAISERROR('===============================================',0,1) WITH NOWAIT;
RAISERROR('RUN COMPLETE',0,1) WITH NOWAIT;
RAISERROR('===============================================',0,1) WITH NOWAIT;
GO


What is the MySQL equivalent of Postgres' C collation?

C is PostgreSQL's way of saying do not reinterpret the data, order it according to binary byte comparisons. From the docs

  On all platforms, the collations named default, C, and POSIX are available. Additional collations may be available depending on operating system support. The default collation selects the LC_COLLATE and LC_CTYPE values specified at database creation time. The C and POSIX collations both specify “traditional C” behavior, in which only the ASCII letters “A” through “Z” are treated as letters, and sorting is done strictly by character code byte values.

MySQL seems to call it binary the "Binary pseudo charset". Whether or not they're optimized to take advantage of that is another question.

See also

The binary Collation Compared to _bin Collations


Update query to Reset counter when date column changes

Do it like this:

select column1,column_sec,attempt,date,
@num := if(@v1 != date OR @v2 != column_sec OR @v3 != column1, 1, @num + 1) AS counter,
@v1 := date,
@v2 := column_sec,
@v3 := column1
from table_name
cross join (select @v1 := '', @v2 :='',@v3 :='', @num := 0) var_init_subquery
where date is not null
order by column1,column_sec,attempt;

The key is, that you have to check first, then assign the value of the current row. In the query you tried, you never assigned anything to the variables. When the next row (and the therefore the select clause) is processed, the variables hold the value of the previous row in the check, then the value of the current row is assigned to the variables.


mySQL for analytics and updating same row

You may need to create the counter table with 250 slots to spread the row locks.

First, here is the table:

CREATE TABLE HourCounter
(
    hr INT,
    slot TINYINT,
    cnt INT UNSIGNED,
    PRIMARY KEY (hr,slot)
) ENGINE=InnoDB;

All you need to do is insert into the random slots like this:

SET @NumberOfSlots = 250;
SET @X = UNIX_TIMESTAMP();
SET @uthr = @X - MOD(@X,3600);
INSERT INTO HourCounter (hr,slot,cnt)
    VALUES (@uthr,RAND() * @NumberOfSlots + 1,1)
    ON DUPLICATE KEY UPDATE cnt = cnt + 1
;

If you want to collect the counts, run this:

SELECT hr,SUM(cnt) HourCount
FROM HourCounter GROUP BY hr;

If you want to collapse the slots into a single number, do this once a week:

UPDATE HourCounter c
    INNER JOIN
    (
        SELECT hr,SUM(cnt) cnt,MIN(slot) mslot
        FROM HourCounter GROUP BY hr
    ) x USING (hr)
SET c.cnt  = IF(c.slot = x.mslot, x.cnt, 0),
    c.slot = IF(c.slot = x.mslot, 0, c.slot);
DELETE FROM HourCounter WHERE slot <> 0 AND cnt = 0;

To reclaim all the disk space for this table, do this once a month:

ALTER TABLE HourCounter ENGINE=InnoDB;
ANALYZE TABLE HourCounter;

Give it a Try !!!

To give credit where is credit is due, I got the answer from page 140 of the Book

I have the book and refer to it well.


The requirements are not clear in at least three places:

What should happen if B is updated but no related row in A is updated? Should B.ts_last_update be updated with current timestamp or not?
What should happen if a row in B has 0 related rows in A (due to that it was just inserted or the related rows from A were deleted? Should B.ts_last_update hold the latest update timestamp of B/A rows or be NULL?
I can't see how a CHECK constraint would help in your case. CHECK constraints are row constraints, they can only be used for checking data from one table and each row independently of other rows, not from two tables.

If the aim is for that column to always hold the MAX(A.ts_update) WHERE A.id_b = B.id value and not to be affected by updates in table B, then you don't really need a trigger at all. You can query both tables every time you need the value or have a view:

--------------------------
CREATE TABLE B
  ( id        SERIAL PRIMARY KEY,
    -- more columns
    -- but no timestamp
  ) ;

---------------------
CREATE TABLE A
  ( id        SERIAL PRIMARY KEY,
    id_b      INTEGER NOT NULL REFERENCES B (id),
    ts_update TIMESTAMP
  ) ;

-- View: B with  ts_last_update
--------------------------
CREATE VIEW BB
    AS
    SELECT B.*,
           ( SELECT MAX(A.ts_update)
             FROM A
             WHERE A.id_b = B.id
           ) AS ts_last_update
    FROM B ;


A table with many records get slow to write and read

How can you have PRIMARY KEY(tran_id) without a column named tran_id???

Your queries need at least one index starting with user_id and 3 composite indexes, say:

INDEX(userid, created_at)
INDEX(card_id, userid)
INDEX(store_id, userid)

Surely most of the fields should be NOT NULL??

Bug:  INT(15) will not hold 15 digits; it will hold only 9 (signed 32-bit numbers).  Suggest DECIMAL(16,0) if you are storing credit card numbers.  But then, I would seriously advise hiring a security consultant -- you do not want to lose a laptop full of CC numbers; you will be in deep do-do.


Postgresql: when temporary tables are deleted in postgresql

In fact you created a regular table. You have to specify that this is a temporary table:

with cte as(
-- <a_query>
)
select *
into temporary temp_table
from cte;

or (the recommended syntax):

create temporary table temp_table as
-- <a_query>

See SELECT INTO and CREATE TABLE AS.


SQL Rebuild indexes data size percent a little smaller

SQL Server Internal Fragmentation is caused by pages that has too much free space. for example at the beginning of the day we have a table with 50 pages that is 100% full, but by the end of the day we have a table with 60 pages that is only 80% full because of various delete and insert statements throughout the day. This may lead to a decrease in performance because now when we need to read from this table we have to scan 60 pages instead of 50. Index maintenance tool and additional information here.


Easily give role in one database access to tables in another database on the same server

Database roles are security principals that are wholly contained within their respective database and are not shared or visible to other databases.  So any roles and users that are in database X have no knowledge of database Y.  To accomplish your goal, you'll need to recreate the role in database Y and add all the appropriate users to this database and role.  Fortunately, you can script out this process using the system views:

USE [Y];
CREATE ROLE foo;

--grant all perms on foo

--Use this generate sql to add all your users to the role
select 'CREATE USER '+quotename(u.name)+';' + char(10)+
       'EXEC sp_addrolemember ''foo'','''+u.name+''';'
from (select name,principal_id
        from x.sys.database_principals where type = 'R') r
    join x.sys.database_role_members rm
        on (r.principal_id = rm.role_principal_id)
    join (select name,type_desc,principal_id
        from x.sys.database_principals where type != 'R') u
        on (rm.member_principal_id = u.principal_id )
where r.name = 'foo'

You can then take this sort of script/process to a batch job to keep things synchronized, but you will need some sort of external process to keep these roles in sync.

One piece that might be confusing here is that you have logins and other server level principals that are connected to the database principals, but there is a clear distinction between these.  I provide some explanation about the differences between these principals in this answer


Insert on duplicate key update using MongoDB

As per MongoDB documentation here Since upsert:true the document is inserted based on the filter and update criteria.

As I am able to see that you are using  { upsert: true } in query statement.

For example:

Update with Upsert

The restaurant collection contains the following documents:

{ "_id" : 1, "name" : "Central Perk Cafe", "Borough" : "Manhattan", "violations" : 3 },
{ "_id" : 2, "name" : "Rock A Feller Bar and Grill", "Borough" : "Queens", "violations" : 2 },
{ "_id" : 3, "name" : "Empire State Pub", "Borough" : "Brooklyn", "violations" : "0" }

The following operation attempts to update the document with name : "Pizza Rat's Pizzaria", while upsert: true :

try {
   db.restaurant.updateOne(
      { "name" : "Pizza Rat's Pizzaria" },
      { $set: {"_id" : 4, "violations" : 7, "borough" : "Manhattan" } },
      { upsert: true }
   );
} catch (e) {
   print(e);
}

Since upsert:true the document is inserted based on the filter and update criteria. The operation returns:

{
   "acknowledged" : true,
   "matchedCount" : 0,
   "modifiedCount" : 0,
   "upsertedId" : 4
}

The collection now contains the following documents:

{ "_id" : 1, "name" : "Central Perk Cafe", "Borough" : "Manhattan", "violations" : 3 },
{ "_id" : 2, "name" : "Rock A Feller Bar and Grill", "Borough" : "Queens", "violations" : 2 },
{ "_id" : 3, "name" : "Empire State Pub", "Borough" : "Brooklyn", "violations" : 4 },
{ "_id" : 4, "name" : "Pizza Rat's Pizzaria", "Borough" : "Manhattan", "violations" : 7 }

The name field was filled in using the filter criteria, while the update operators were used to create the rest of the document.


Can you Mirror an Microsoft SQL compact Edition database

You can copy the database file to the desktop and use a tool like SQL Compact Toolbox to query the data
Or you could place a copy of the file on the SQL Server disk drive, and acces it via some SQLCLR code (this will allow you to mix with SQL server data)
Or you can use some synchronization mechanism to transfer the data to SQL server (requires programming/configuration)

Mirroring is not an option at all


Space used by databases file through linked server

@cicik, if you are looping through the databases at your local server like you mentioned, assuming you already have the list of remote databases stored somewhere in a local table, you could loop through the databases (e.g. cursor) and execute,

SELECT * FROM [YourLinkedServer].[YourRemoteDatabase].[dbo].[view];

This should put the remote database context at the database you specified, and hence it works for FILEPROPERTY function and sys.database_files dmv that return values only for the current database.

Also, you might want to consider extracting the list of databases on the remote server and run the script on each execution at the remote server (client side) so you won't have to create the view on each database, and won't miss any new databases created without your knowledge.

UPDATE
If you want to use script to dynamically extract the data from the linked server without adding the view on every database,

DECLARE @remotecmd nvarchar(max);

SET @remotecmd = N'
DECLARE @db     sysname,
        @cmd    nvarchar(max);

CREATE TABLE #dbspace
(
    DataFileName nvarchar(128),
    DataPhysicalName nvarchar(260),
    DataTotalSizeInMB decimal(17,6),
    DataSpaceUsedInMB decimal(17,6),
    DataAvailableSpaceInMB decimal(17,6)
);

DECLARE dbcur CURSOR
LOCAL FAST_FORWARD
FOR SELECT name
    FROM sys.databases
    WHERE [state] = 0;

OPEN dbcur;
FETCH NEXT FROM dbcur INTO @db;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @cmd = N''USE '' + QUOTENAME(@db) + N''; '';
SET @cmd = @cmd +
N''
INSERT INTO #dbspace
SELECT
    name AS [DataFileName],
    physical_name AS [DataPhysicalName],
    ROUND(size / 128.0, 1) AS [DataTotalSizeInMB],
    ROUND(CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataSpaceUsedInMB],
    ROUND(size / 128.0 - CAST(FILEPROPERTY(name, ''''SpaceUsed'''') AS int) / 128.0, 1) AS [DataAvailableSpaceInMB]
FROM  [sys].[database_files] WHERE type = 0'';

EXEC (@cmd);

FETCH NEXT FROM dbcur INTO @db;
END

CLOSE dbcur;
DEALLOCATE dbcur;

SELECT
    DataFileName,
    DataPhysicalName,
    DataTotalSizeInMB,
    DataSpaceUsedInMB,
    DataAvailableSpaceInMB
FROM #dbspace;

DROP TABLE #dbspace;'

EXEC (@remotecmd) AT [YourLinkedServer];

Make sure your login has the access to all the databases on the remote server.


Instead of using a trigger, you should use a Calculated Column.
This way, any change in stock_out would affect the users score.
It would be calculated automaticaly, and without duplicate rows.

An AFTER UPDATE TRIGGER could then be used to store record of previous user score, by reading data from DELETED and inserting it into an audit table.


Try something like this:

Declare @Name varchar(30) = 'Bob'
Declare @DBname sysname = 'TestDb'
Declare @Cmd varchar(4000)
set @Cmd = 'echo ' + 'alter login ' + QUOTENAME(@Name) + ' with default_database = ' + QUOTENAME(@DBname) + ' >> C:\test\test_script.txt'
print @Cmd
exec master..xp_cmdshell @cmd


Long story short: You do not have the user 'lumicrm'@'%' on the DB Server.
Login to MySQL and run
SELECT user,host FROM mysql.user WHERE user='lumicrm';

Let's say you see the user 'lumicrm'@'localhost'
You have to create the user so that the view can be recognized. There are three(3) things you can try
Try This #1
Login to MySQL as lumicrm and run this command
SHOW GRANTS FOR CURRENT_USER();

Given the output of that query:

Copy the output of that query to a Text File
Change the host portion of the userhost to '%';
Copy and Paste into a MySQL Session to create the View.

Try This #2
CREATE TABLE mysql.lumicrm LIKE mysql.user;
INSERT INTO mysql.lumicrm SELECT * FROM mysql.user WHERE user='lumicrm';
UPDATE mysql.lumicrm SET host='%';
INSERT INTO mysql.user SELECT * FROM mysql.lumicrm;
FLUSH PRIVILEGES;
DROP TABLE mysql.lumicrm;

Now you have the MySQL user 'lumicrm'@'localhost' hacked in
Try This #3
Dump the Views to a Text File. Here are my posts on how to do this:

Jun 26, 2011 : Modify DEFINER on Many Views
Jan 19, 2012 : Will mysqldump command also backup all the views that are present in DB or there is a special command for it?
May 02, 2012 : Problem with View When Restoring a MySQL Backup

Edit the user for the View with the appropriate username. Copy the View Creation Code and Paste into a MySQL Session to create the View.
Give it a Try !!!


SQL 2005 Full Recovery Mode Point in Time Restore w/out Backup

Not just using the transaction log, no. It records redo, but not undo, so you can only roll forwards from a point in time, which is the time of a backup. It may be possible to replay the entire log from the start on a blank database, but I don't know you'd "tell" SQL Server that you wanted to do that, even creating a database "for load" probably wouldn't allow it...


Custom personal monitor sql by writing the .psqlrc file

Using psql variables with quotes is a bit tricky.  As you noticed, they don't get substituted in certain positions, among other, between quotes.

My only solution to this so far has been including the quote in the variable itself.  This is also not very straightforward:

test=# \set a 'parrot'
test=# \echo :a
parrot
test=# \set a ''parrot''
test=# \echo :a
parrot
test=# \set a '''parrot'''
test=# \echo :a
'parrot'
test=# \set a ''''parrot''''
test=# \echo :a
'parrot'

and so on.  This is, to my eyes, is basically the same as the quote duplication magic elsewhere (mentioned more than once in the documentation).

The solution I've posted in the comment is basically an ugly version of the three-quote version.

Note that dollar quoting does not work here:

test=# \set a $$'parrot'$$
test=# \echo :a
$$parrot$$
test=# \set a '$$parrot$$'
test=# \echo :a
$$parrot$$


How to count the number of rows by datetime field with the division into time intervals

variable interval_minutes number;

exec :interval_minutes := 10;

select
  trunc(registered_at, 'HH24')
  + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440,
  count(*)
from
  clients
group by
  trunc(registered_at, 'HH24')
  + trunc(to_char(registered_at,'mi')/:interval_minutes)*:interval_minutes/1440
order by
  1
;

Based on: Summarizing data over time - by time interval


My first IT job was in this area - basically I was involved in a "fiddle" on the part of my employer to land a big contract which involved convincing the client that we had a functioning and "intelligent" order picking system. It involved alcholic beverages and there are many complex rules about these (tax reasons).

Anyway, I just wish that Open Source had been as prevalent then as it is now - because there appear to be a plethora of high-quality projects out there in this area. A quick Google landed me here and here - search for the word "pick" on these pages.

If I were you, what I would do is examine these (and others...) packages and see which ones correspond most closely to your needs - download and install them and then check out the underlying schemas and see which bits you can borrow for your project. Or you may just decide to implement one of the systems that's already out there?

What I do know is that the logistics behind these problems are very complex (operations research, tricky mathematics &c.), so you might be able to take some of the pain out of your project by "standing on the shoulders of giants"!

[EDIT]

You might like to check out this and its parent site- it specifically mentions bin-packing and employee rostering, so it might be of interest. I remember this from studying operations research. If you work in Java, it could be of interest - although the principles obviously apply to all languages.


From the MySQL Documentation

Aborted_clients : The number of connections that were aborted because the client died without closing the connection properly.
Aborted_connects : The number of failed attempts to connect to the MySQL server

From the look of those two status variables:

Aborted_clients : This should make you look over how your clients are disconnecting. If you connect using mysqli_connect(), you should be closing using mysqli_close(). If you are not and just let the connections die on their own, that might be one probable cause.
Aborted_connects : You should check to see what max_connections is set to. It might be too low and connections are failing simply because of reaching this max setting.
You should raise the max_connect_errors and run FLUSH HOSTS; periodically.
You should investigate the other status variables related to connection errors. You might need to get your SysAdmin involved.


How do I find a job that sends a specific email?

The problem with your first query is the use of unescaped brackets with the LIKE operator.

When using the LIKE operator, there are a few special characters such as brackets that require escaping if you want to look for them. Characters enclosed in brackets will tell the SQL engine to look for a match between any character listed inside (same as regular expressions).

DECLARE @string VARCHAR(100) = 'ABC'

SELECT 'match!' WHERE @string LIKE '%[CX]%' -- Sting must have a C or an X
-- Result: match!

In your case, using [AUPAIR] inside the like will only match 1 letter (either A, U, P, I or R), and not the whole word.

DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -> [AUPAIR] arrivalDate trigger issue%'
-- Result: (none)

This will however match with the following strings (just 1 letter!):

'Staging -> A arrivalDate trigger issue'
'Staging -> U arrivalDate trigger issue'
'Staging -> P arrivalDate trigger issue'
'Staging -> I arrivalDate trigger issue'
'Staging -> R arrivalDate trigger issue'

To correctly search for brackets, you can use 2 solutions:

Declare a custom ESCAPE character.

DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -> \[AUPAIR] arrivalDate trigger issue%' ESCAPE '\'
-- Result: match!

Enclose the opening bracket with brackets.

DECLARE @string VARCHAR(100) = 'Staging -> [AUPAIR] arrivalDate trigger issue'

SELECT 'match!' WHERE @string LIKE '%Staging -> [[]AUPAIR] arrivalDate trigger issue%'
-- Result: match!

Please note that there is no need to escape the closing bracket since this one will only be interpreted as a LIKE special character as long as there is an opening one before it (which won't be if you escaped it).


postgres 9.3. resets log_min_duration_statement after receiving SIGHUP

After reload you get have settings from config (default)
you must use false as 3 parameter - it is is_local - just for current
transaction

sqldb=# select current_setting('log_min_duration_statement');
 current_setting
-----------------
 -1
(1 row)

sqldb=# select set_config('log_min_duration_statement', '1000', false);
 set_config
------------
 1s
(1 row)

sqldb=# select current_setting('log_min_duration_statement');
 current_setting
-----------------
 1s
(1 row)


How can I change the value of min word length for specific query in fulltext search?

Short answer:  Not possible.

Long answer...

That setting is used when any MyISAM FULLTEXT index is being built.  You should change to InnoDB, in which case innodb_ft_min_token_size is the variable to set.  (It defaults to 3.)

If you change the setting after the index is built, it has no effect on existing indexes or queries using those indexes.

The only way to change it is to (1) change it in my.cnf, then (2) rebuild the index on any table for which you want the new length.


Which PowerShell technique should I use to talk to SQL Server?

Obviously, a lot of this devolves to simple personal choice. Here are my own, personal, rationalizations.

I've been using Powershell with SQL SQL since PSH v 1.0, and before SQL Server started officially integrating it. (When I started with PSH, I was administering SQL Server 2000 and 2005 servers.) So, I learned with SMO (or it's slightly older incarnation, the name of which escapes me at the moment) and .Net and I'm used to them. I'd generally lean towards SMO, since it makes some things a lot easier, like scripting out objects. My own code uses SMO some times and .Net some times. I think it's handier to use .Net to get simple result sets, for instance.

I think that Invoke-SQLCMD makes more sense if you have lots of existing TSQL scripts. If you are creating strings and executing them through -Query, that's going to be messy. If you have a good grasp of how Powershell works with .Net and SMO, using Invoke-SQLCMD occasionally, when you have a script file to run, is easy.

I've always found the PSDrive thing clunky and felt that they implemented it because they got caught up in the "everything can look like a file system" idea. I know that the *nix guys love \proc and such, but I feel that this implmentation feels sort of forced. I think that PSDrive is OK, maybe even good if you hate the UI, for exploring things but I've never written a script that uses it.

I have never seen anyone use the WMI provider. So, that would be my last choice.

So, I'd lead with SMO and fall back to .Net when it's handier to.


Should I use TINYINT for ID (PK) field in MySQL?

Yes.  You should also say UNSIGNED:  TINYINT UNSIGNED.  Range 0..255; 1 byte.

Actually, it is such a tiny optimization that I don't bother mentioning it.

Think about using the smallest datatypes when you first CREATE the table; it is messier to make the change later.

SMALLINT UNSIGNED:  0..65535, 2 bytes
MEDIUMINT UNSIGNED:  0..16M, 3 bytes
INT UNSIGNED:  0..4B, 4 bytes

Keep in mind that certain operations "burn" AUTO_INCREMENT ids.  This could lead to an unexpected overflow.  Examples:  INSERT IGNORE and REPLACE.  They may preallocate an id before realizing that it won't be needed.


It is not up to the database engine to render an image or provide any visualisations - that work is performed by the client application receiving the data returned by the database engine.

What you want to look for is an IDE or query tool that has in-built visualisation capabilities. SSMS does not do this natively for SQL Server, but there are plug-ins and alternative tools that can do this (see this question).

For Oracle, SQL Developer is apparently capable of rendering BLOBs in the IDE. For other RDBMS you will need to research.


How do I completely reset ServiceBroker including all messages, queues, triggers, etc?

Its cause due to multi-trigger on the table which created by service broker.

Delete all trigger and start broker services or then try.


What is the proper event in Extended Events to track stored procedure executions?

Include the module_start event filtered on the stored procedure name and a histogram target bucket based on the object_name field. This will capture and aggregate the execution counts every time the stored procedure is executed, whether it is called directly or indirectly (i.e. called other stored procedures and triggers).

CREATE EVENT SESSION [module_start_execution_summary] ON SERVER
ADD EVENT sqlserver.module_start(
    WHERE ([object_name]=N'YourStoredProcedure'))
ADD TARGET package0.histogram(SET filtering_event_name=N'sqlserver.module_start',source=N'object_name',source_type=(0))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON);

Note that if the same stored procedure name exists in more than one database or schema, the different procs will be counted as one with this method. An additional filter on the database can be used to limit counting to only the desired database.


Sql Server - Best Practices for Growing Database Files

You should be aiming to auto-grow as little as possible. Seven times a day is excruciating, even with instant file initialization.

Don't do a Shrink Database. Ever. Shrinkfile, maybe, but only after an extraordinary event. Shrinking it just to grow again is an exercise in futility and should actually be called auto-fragment.

If recovery model is simple, there is no way on earth you should need to grow your log file by 250 GB. The used space in the file will clean itself out automatically over time, unless you started a transaction a month ago and have no intentions of ever committing it or rolling it back.

So my advice would be:

Auto-grow the data file manually during a quiet period to a size that will accommodate several months of growth. What are you saving it for in the meantime?

Set the auto-growth increment for the data file to something relatively small (so that it doesn't interrupt users when it does happen), and alert on this event (you can catch it in the default trace, for example, or through extended events). This can tell you that you are hitting the high point you estimated and it is time to grow manually again. At this point you will want to keep this manual in case you want to add a new file / filegroup on a different drive to accommodate the space, since eventually you will fill the current drive.

Auto-grow the log file to, say, twice the largest it's ever been. It shouldn't auto-grow further unless there is some abnormal transaction holding things up. You should monitor for this event as well, so that you know about them.


Grant permission to KILL connections (sessions) for certain databases only?

Granting highly selective / fine-grained permission is rather easy via module signing:

USE [master]
Create a stored procedure to do whatever you want the low-privileged Login(s) to be able to do, with the necessary checks, etc.
Create a Certificate
Create a Login from that Certificate
Grant that Certificate-based Login the minimum level of permissions required to accomplish what the Stored Procedure is coded to do (in this case it might just be ALTER ANY CONNECTION (according to @sepupic)
Sign that Stored Procedure with that Certificate using ADD SIGNATURE
Grant the low-privileged Login(s) EXECUTE permission on that Stored Procedure.

HOWEVER, figuring out what Session / SPID is affecting which DB(s) is not easy. The database_id reported in sys.dm_exec_sessions is the "current" database: either what was connected to / their default DB if not specified in the connection string / whatever DB was changed to via the most recent USE statement. But the "current" database isn't necessarily where the problem is. Anyone can execute code and run queries in other DBs using 3-part names (a query can reference 3 tables, each in separate DBs, and none of them being in the "current" DB). So, I'm not sure how you would reliably enforce the "only certain DBs" constraint.


MongoDB replication throttled due to Mongo using all system memory

I have never seen throttling of replication speed, but I'm working with Linux mongodb machines. I guess that at windows world it can happen, windows OS is throttling when program eats all memory. I remember reading that there are "problems" at windows OS when mongod uses all free memory.

To control how much memory mongod can uses, in Linux, you can use cgroups, so if you use "containers" (docker) in windows, you could control how much resources mongod gets.


Download the Visual Studio 2010 ISO (Ultimate, in your case).

Extract it to a folder and use that folder path when prompted for vs_setup.msi.


You can get almost as good a plan with fewer indexes if you cluster tContactPhone by (ContactID,ID) instead of having a clustered index on ID and a seperate non-clustered index on ContactID.  eg
CREATE TABLE tContactPhone(
    [ContactID] [int] NOT NULL,
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Type] [nvarchar](25) NOT NULL,
    [Number] [nvarchar](12) NOT NULL,
    CONSTRAINT [pkey_tContactPhone] PRIMARY KEY CLUSTERED
    (
        [ContactID],[ID]
    )
)

This is generally a better-performing pattern for "child tables" as the clustered index also supports the foreign key.


Counting and grouping over multiple OUTER JOINs

I was able to find a solution while I was composing the question, but I've decided to post both the question and answer in case it helps someone else in the future.
Alternatively, if anyone has a simpler or better-performing solution to this problem, I would be happy to hear it.

I managed to solve my NULL-counting problem by using a CROSS JOIN prior to the OUTER JOIN:

SELECT * FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code;

 id | code | advertisement | event_code
----+------+---------------+------------
  1 | I    |             1 | I
  1 | C    |             1 | C
  2 | I    |             2 | I
  2 | I    |             2 | I
  2 | C    |             2 | C
  3 | I    |             3 | I
  3 | I    |             3 | I
  3 | C    |               |
  4 | I    |               |
  4 | C    |               |
(10 rows)

The above gave me the intermediate table I needed. Adding grouping and counting, I finally get the numbers I'm looking for:

SELECT advert.id,advert_event.code,COUNT(advert_log.advertisement) FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code GROUP BY advert.id,advert_event.code;

 id | code | count
----+------+-------
  1 | C    |     1
  1 | I    |     1
  2 | C    |     1
  2 | I    |     2
  3 | C    |     0
  3 | I    |     2
  4 | C    |     0
  4 | I    |     0
(8 rows)

Finally, using two sub-SELECTs (one for 'I', one for 'C'), I composed a query to obtain the counts by advertisement:

CREATE VIEW advertisement_dashboard AS
SELECT a.id,i.impressions,c.clicks,c.clicks::float/greatest(i.impressions, 1) AS cpi FROM advert a,
(
  SELECT advert.id,COUNT(advert_log.advertisement) AS impressions FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code
GROUP BY advert.id,advert_event.code HAVING code='I'
) i,
(
  SELECT advert.id,COUNT(advert_log.advertisement) AS clicks FROM advert CROSS JOIN advert_event LEFT OUTER JOIN advert_log ON advert_log.advertisement=advert.id AND advert_log.event_code=advert_event.code
GROUP BY advert.id,advert_event.code HAVING code='C'
) c WHERE i.id=a.id AND c.id=a.id ORDER BY a.id ASC;

SELECT * FROM advertisement_dashboard;

 id | impressions | clicks | cpi
----+-------------+--------+-----
  1 |           1 |      1 |   1
  2 |           2 |      1 | 0.5
  3 |           2 |      0 |   0
  4 |           0 |      0 |   0
(4 rows)


One way of return select data from PL/SQL is using Oracle table functions.  Below is an example where the line SELECT * FROM table_a where test_a = par1; should be replaced by your select statement + change the table name.

Create table Table_a
(test_a varchar2(1))
/

insert into table_a
values('a')
/

create or replace package test_package as
TYPE col_table_1 is table of TABLE_A%ROWTYPE;
function test_plsql_table(par1 varchar2) return col_table_1
  pipelined;
end;
/

create or replace package body test_package as
  function test_plsql_table(par1 varchar2) return col_table_1
    PIPELINED as
    cursor temp_cur is
      SELECT * FROM table_a where test_a = par1;
  begin
    for cur_rec in temp_cur loop
      pipe row(cur_rec);
    end loop;
  end;
end;

/

SELECT * from TABLE( test_package.test_plsql_table('a'))
/


"History-based" database design approach?

Your description could be of SQL Columnstore indexes

This index uses column-based data storage and query processing
stored in a column-wise data format
To reduce fragmentation of the column segments and improve performance, the columnstore index might store some data temporarily into a clustered index called a deltastore and a btree list of IDs for deleted rows. The deltastore operations are handled behind the scenes. To return the correct query results, the clustered columnstore index combines query results from both the columnstore and the deltastore.


Looks like you are trying to insert duplicate PK_user IDs into your destination table, that is set to be unique as well.

You'll need to join the 2 sets and only write the unique PK_user results or remove the unique constraint on your destination table.


Will alone bin log ignore DB mysql helps in slave read only mode

After series of testing.. this scenario only happens if we grant all privileges on every database (.)[ I think it will assume this specific user as root privileged and allows to access any database. In any other scenario it will reject with below error.

*ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement*


Delete from table where not in another table using two columns

I suspect you're after something like this:

DELETE FROM tbl1
FROM tbl1
    LEFT OUTER JOIN tbl2
        ON tbl1.PK1 = tbl2.PK1
        AND tbl1.PK2 = tbl2.PK2
        AND tbl2.DateStr >= GETDATE() - 365
WHERE tbl2.PK1 IS NULL
    AND tbl2.PK2 IS NULL

Yes, you have two FROM clauses with a multi-table DELETE statement. This is essentially just an anti-join turned into a deletion.


Grant permission to execute stored procedure without granting access to underlying table - Oracle

This is indeed possible with stored procedures running with AUTHID DEFINER. Such SPs run with the authority of whoever created them. Please note that the creator must have explicit grants to the queried table; authorities acquired via roles will have no effect in a stored procedure.


MySQL - Order by closest value on multiple columns

Simply add another column,
order by abs(connection-5), abs(level-4) is valid syntax:

select lobby.id, lobby.user_id,
   users.connection, users.level
from lobby INNER JOIN users
ON lobby.user_id = users.id
where lobby.user_id <> 1
  and users.connection between 0 and 10
  and users.level between 1 and 11
order by abs(connection-5), abs(level-4);


Is there a way to make the subscriber database read-only when using Transaction Replication?

Instead of altering the database to become read only, create a new login and add it to the db_datareader role of the subscriber database.


Moving data from one DB to another where the table/column structure is identical

If you have duplicate inserts, the first question I would have would be why would you have two rows in the same table with the exact same information?

That being said, if there are no unique keys or other such constraints on the table, the new DB server should dutifully import the duplicated data.

I would heartily recommend testing it in a non production environment first, though.


Replication - Initialize with Snapshot when using filtered rows

A lot of testing later, I have the answers.

Does the filter apply to the snapshot?

Yes.

So out of my numbered list, 1 is correct. The reason I didn't really believe this to be true is because when quickly testing this I forgot about the option in Article Properties that controls how the Snapshot is applied. This also made me ask the question after the numbered list beginning 'Further'. The answer to that paragraph is: it depend how it is set in Article Properties.
To explain this more fully:

Right click your Publication in SSMS > Properties > click Articles in the left tab > Article Properties button (pick either option that is relevant to you).
Look at the Destination Object section > Action if name is in use

This option controls (for this or ALL articles) what the Agent does when applying the Snapshot to the Destination database. As Default it Drops and recreates the object (this is why it seemed to me to be replacing all the data - essentially it was because it was dropping the object, creating the object, BCP'ing the data in (so anything previously in the table is gone)).

I needed "Delete data. If article has a row filter, delete only data that matches the filter" as this therefore kept the data already in the table that was not > 2011.

Hopefully someone in the future can save some time testing by finding this answer! I'm slightly surprised that the default option is to Drop and replace the object to be honest, but I suppose that fits most people's purposes for Snapshot.
So someone can find this answer if they are searching google I shall reproduce the question as I would google it:

When initializing replication from Snapshot on a publication with Filtered rows, does it apply the filter to the snapshot it takes?


How to change default database to all logins?

You should never try to update system tables directly, in most cases it is not going to let you as you have found.

In your case you will want to build out a dynamic statement for the ALTER LOGIN without knowing how many there are on the instance. However, you will need to be cautious in doing this to ensure you do not touch logins you shouldn't. This query should give you those logins that are "non-default" or installed via the installation process:

SELECT name
FROM sys.server_principals
WHERE type <> 'R'
    AND name NOT LIKE '##%' --Exclude policy based accounts
    AND name NOT LIKE 'NT%' --Exclude system accounts
    AND sid <> 0x01 --exclude the sa

From this you just need to build out your statement to execute via sp_executesql. You could use a simple cursor or while loop per your preference, below is a template for just a basic cursor that I keep handy:

DECLARE @v1 VARCHAR(50)

DECLARE v_cursor CURSOR FOR
/* Query of data to iterate through */

OPEN v_cursor
FETCH NEXT FROM v_cursor INTO @v1

WHILE @@FETCH_STATUS = 0
BEGIN
    /* build your dynamic statement */
    /* Use @v1 within query to perform action on each row */
    FETCH NEXT FROM v_cursor INTO @v1
END

CLOSE v_cursor
DEALLOCATE v_cursor;


Best way to store correlation data for searches

There are (at least) two ways of doing this, but only one will be shown here (see edits for old version of query):
N correlated keywords (where N is arbitrary).
I constructed a table as follows (complete DDL and DML are at the bottom of this post and also on the fiddles - PostgreSQL & MySQL):
CREATE TABLE city_lang_jp_combo
(

  c_l_combo_set INTEGER NOT NULL,

  c_id      INTEGER NOT NULL,
  l_id      INTEGER NOT NULL,

  c_l_combo_jp DOUBlE PRECISION NOT NULL,

  CONSTRAINT c_l_combo_id_pk PRIMARY KEY (c_l_combo_set, c_id, l_id),

  CONSTRAINT c_l_combo__jp_exists_fk FOREIGN KEY (c_id, l_id)
    REFERENCES city_language (c_id, l_id)

);

and it's populated as follows:
INSERT INTO city_lang_jp_combo
VALUES
(1, 101, 1111, 0.61),
(1, 101, 2222, 0.61),
(1, 101, 3333, 0.61),

(2, 102, 1111, 0.62),
(2, 102, 2222, 0.62),
(2, 102, 3333, 0.62),

...
... snipped ~ 35 rows for brevity
...

The first set of three records means that for a set 1 (1 is arbitrarily assigned, could be any INT which doesn't occur elsewhere in the table), for city 101 (Dublin) and languages 1111, 2222 & 3333 (Java, C & C++) that  the probability of the three of them occurring together is 0.61 in job postings.
I also have a 4 language combination and a 2 language combination at the bottom as follows:
(13, 101, 1111, 0.25),
(13, 101, 2222, 0.25),
(13, 101, 3333, 0.25),
(13, 101, 4444, 0.25),  <=== Python

and also a two language combination:
(14, 101, 5555, 0.05),  <=== Nim
(14, 101, 6666, 0.05);  <=== Clojure

So, first I run this SQL:
SELECT
  c.city_name AS cname,
  cljc.c_l_combo_set AS combo,
  cljc.c_id AS cid,
  l.lang_name AS ln,
  cljc.c_l_combo_jp AS prob
FROM  city_lang_jp_combo cljc
JOIN language l
  ON l.lang_id = cljc.l_id
JOIN city c
  ON c.city_id = cljc.c_id
ORDER BY cname, combo;

Result:
cname   combo   cid     ln         prob
Dublin      1   101     C++        0.61
Dublin      1   101     Java       0.61
Dublin      1   101     C          0.61
Dublin      5   101     Python     0.51
Dublin      5   101     C          0.51
Dublin      5   101     Java       0.51
Dublin      9   101     Python     0.41
Dublin      9   101     C++        0.41
Dublin      9   101     C          0.41

Dublin     13   101     Java       0.25  <<-- 4 language combo, Java,
Dublin     13   101     C          0.25  <<-- C, C++ & Python - low p-Value
Dublin     13   101     C++        0.25
Dublin     13   101     Python     0.25

Dublin     14   101     Nim        0.05  <<-- two language combo - Nim  - v. low p-Value
Dublin     14   101     Clojure    0.05  <<-- and Clojure

Madrid      4   104     C          0.64
...
... snipped
...

And, using this query, I perform a STRING_AGG (GROUP_CONCAT in MySQL) using the above as a subquery as follows:
SELECT
  t.cname AS "City name",
  STRING_AGG(t.ln, ', ' ORDER BY t.cname, t.combo, ln) AS "Languages",
  t.prob AS "Probability"
FROM
(
  SELECT
    c.city_name AS cname,
    cljc.c_l_combo_set AS combo,
    cljc.c_id AS cid,
    l.lang_name AS ln,
    cljc.c_l_combo_jp AS prob
  FROM  city_lang_jp_combo cljc
  JOIN language l
    ON l.lang_id = cljc.l_id
  JOIN city c
    ON c.city_id = cljc.c_id
  -- ORDER BY cname, combo
) AS t
GROUP BY t.cname, t.combo, t.prob
ORDER BY "City name", "Languages";

Result:
City name   Languages           Probability
Dublin  C, C++, Java                   0.61
Dublin  C, C++, Java, Python           0.25
Dublin  C, C++, Python                 0.41
Dublin  C, Java, Python                0.51
Dublin  Clojure, Nim                   0.05
Madrid  C, C++, Java                   0.64
Madrid  C, C++, Python                 0.44
Madrid  C, Java, Python                0.54
Paris   C, C++, Java                   0.62
Paris   C, C++, Python                 0.42
Paris   C, Java, Python                0.52
Rome    C, C++, Java                   0.63
Rome    C, C++, Python                 0.43
Rome    C, Java, Python                0.53

I did a performance analysis on both the PostgreSQL and the MySQL queries - they are pretty similar and the new query takes (on average) 20% - 25% as long as my first query (see the fiddle).
There's no need to use an MD5 hash or anything else to ensure uniqueness - the language names are unique by virtue of the UNIQUE index on the language table so a combination of these in lang_id or lang_name order is guaranteed to be unique - and the lang_name strings are human-readable - never underestimate the value of this factor!
Your main problem is going to be enforcing UNIQUEness - there's nothing stopping the records
Dublin     999  101     Nim        0.23  <<-- two language combo - Nim  - v. low p-Value
Dublin     999  101     Clojure    0.23  <<-- and Clojure

being inserted - and obviously only 1 of the two sets of records can be correct, but we can't enforce uniqueness over sets of records. You could create a TEMPORARY table with fields the same as the resultset above and a UNIQUE constraint on "City name" and "Languages". Then try to INSERT the query result into that table and see if it fails on the constraint... Of course, there are all sorts of ways of doing this programmatically and/or through the use of TRIGGERs.
Another problem is that there is no way of ensuring that the p-Values are realistic - i.e. if p(C & Python) is 0.6 (say), then p(C & Python & C++) can be at most 0.6 (every job posting with the first two also has C++). This will have to be enforced in code...
p.s. welcome to the forum!

Full DDL and DML...
CREATE TABLE city
(
  city_id   INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  city_name TEXT NOT NULL,
  city_pop  INTEGER NOT NULL,
  --
  --  more fields
  --
  CONSTRAINT city_pk PRIMARY KEY (city_id),
  CONSTRAINT city_name_uq UNIQUE (city_name) -- might not always be the case,
    -- just have 'Dublin (Ire.)' and ('Dublin (Cal.)') for example
);

Populate:
INSERT INTO city (city_name, city_pop) VALUES
('Dublin', 500000), ('Paris', 2100000), ('Rome', 3000000), ('Madrid', 3250000);

The population might be useful for calculating confidence intervals and the like...
CREATE TABLE language
(
  lang_id    INTEGER GENERATED BY DEFAULT AS IDENTITY,
  lang_name  TEXT NOT NULL,

  CONSTRAINT language_pk PRIMARY KEY (lang_id),
  CONSTRAINT lang_name_uq UNIQUE (lang_name)
);

Populate:
INSERT INTO language (lang_id, lang_name) VALUES
(1111, 'Java'), (2222, 'C'), (3333, 'C++'), (4444, 'Python'), (5555, 'Nim'), (6666, 'Clojure');

The language_name's UNIQUE constraint means that the result of query will be unique for any combination of languages when ordered by l.lang_name (or lang_id separated by a comma - must have the comma for numbers because 1 || 11 || 111 is identical to 11 || 11 || 11).
CREATE TABLE city_language
(
  c_id     INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
  l_id     INTEGER NOT NULL,
  c_l_prob DOUBLE PRECISION NOT NULL,

  CONSTRAINT city_language_pk PRIMARY KEY (c_id, l_id)

);

This table is a sort of "insurance policy" - the c_id, l_id combination act as a FOREIGN KEY for the table below (combinations of langauges) - how can you have combinations of languages if you don't have at least have 1  of them?
However, as discussed above, we have no way of knowing if the p-Value for 1 language (per city) - say C - is <= p (2 languages) - (say C & C++) for that same city.
Populate city_language:
INSERT INTO city_language (c_id, l_id, c_l_prob) VALUES

(101, 1111, 0.91),  (101, 2222, 0.92),  (101, 3333, 0.93),  (101, 4444, 0.94),
(102, 1111, 0.81),  (102, 2222, 0.82),  (102, 3333, 0.83),  (102, 4444, 0.84),
(103, 1111, 0.751), (103, 2222, 0.752), (103, 3333, 0.753), (103, 4444, 0.754),
(104, 1111, 0.701), (104, 2222, 0.702), (104, 3333, 0.703), (104, 4444, 0.704),
(101, 5555, 0.2), (101, 6666, 0.10);

Final table (this is the main table):
CREATE TABLE city_lang_jp_combo
(

  c_l_combo_set INTEGER NOT NULL,

  c_id      INTEGER NOT NULL,
  l_id      INTEGER NOT NULL,

  c_l_combo_jp DOUBlE PRECISION NOT NULL,

  CONSTRAINT c_l_combo_id_pk PRIMARY KEY (c_l_combo_set, c_id, l_id),

  CONSTRAINT c_l_combo__jp_exists_fk FOREIGN KEY (c_id, l_id)
    REFERENCES city_language (c_id, l_id)

    -- if this doesn't exist, then how can you have a triple combination?
);

Populate it:
INSERT INTO city_lang_jp_combo VALUES

 (1, 101, 1111, 0.61),  -- check back to city_language p !> in there...
 (1, 101, 2222, 0.61),  -- ensure that there are at least 3 records?
 (1, 101, 3333, 0.61),

 (2, 102, 1111, 0.62),
 (2, 102, 2222, 0.62),
 (2, 102, 3333, 0.62),

 (3, 103, 1111, 0.63),
 (3, 103, 2222, 0.63),
 (3, 103, 3333, 0.63),

 (4, 104, 1111, 0.64),
 (4, 104, 2222, 0.64),
 (4, 104, 3333, 0.64),

 (5, 101, 1111, 0.51),
 (5, 101, 2222, 0.51),
 (5, 101, 4444, 0.51),

 (6, 102, 1111, 0.52),
 (6, 102, 2222, 0.52),
 (6, 102, 4444, 0.52),

 (7, 103, 1111, 0.53),
 (7, 103, 2222, 0.53),
 (7, 103, 4444, 0.53),

 (8, 104, 1111, 0.54),
 (8, 104, 2222, 0.54),
 (8, 104, 4444, 0.54),

 (9, 101, 2222, 0.41),
 (9, 101, 3333, 0.41),
 (9, 101, 4444, 0.41),

(10, 102, 2222, 0.42),
(10, 102, 3333, 0.42),
(10, 102, 4444, 0.42),

(11, 103, 2222, 0.43),
(11, 103, 3333, 0.43),
(11, 103, 4444, 0.43),

(12, 104, 2222, 0.44),
(12, 104, 3333, 0.44),
(12, 104, 4444, 0.44),

(13, 101, 1111, 0.25),
(13, 101, 2222, 0.25),
(13, 101, 3333, 0.25),
(13, 101, 4444, 0.25),

(14, 101, 5555, 0.05),
(14, 101, 6666, 0.05);

Discussion (also in fiddle):
--
-- Now, consider cl_combo_set = 777 below - it's
-- essentially a duplicate of the cl_combo_set = 1
--
-- But the problem is, how does one enforce uniqueness over a set of
-- records?
--

-- (777, 101, 1111, 0.33),
-- (777, 101, 2222, 0.33),
-- (777, 101, 3333, 0.33),

--
-- BUT, if I add a 4th
-- record to the set, the UNIQUE constraint no longer applies because
-- we've added a 4th language---
--
-- say, like this:
--
-- (888, 101, 1111, 0.15),
-- (888, 101, 2222, 0.15),
-- (888, 101, 3333, 0.15),
-- (888, 101, 5555, 0.15)
--
--
-- The p-Value of a combination of languages must be **AT MOST** <= the
-- p-Value of the lowest p-Value for any of the languages - how to enforce?
--
--
-- For example, SQL (appears to me anyway) to be "tacked on" to virtually
-- every job ad - so, you might have (for Dublin - city code 101) -
-- SQL code say
--
-- Now, this is a valid set of records for the 3 languages Java, C & C++
-- (999, 101, 1111, 0.33),
-- (999, 101, 2222, 0.33),
-- (999, 101, 3333, 0.33);
--
-- and say, SQL was language code 7777, one could have:
--
-- (1000, 101, 1111, 0.33),
-- (1000, 101, 2222, 0.33),
-- (1000, 101, 3333, 0.33),
-- (1000, 101, 7777, 0.33);
--
-- this would **_ALSO_** be a valid set of records for Dublin - difficlt
-- to enforce uniqueness constraints for this and also the CHECK that the
-- p-Value is less than or equal to (at most) the p-Value for any of them.


Putting a constraints on a already made table in PostgreSQL

Tables don't have "variables" - they have columns.

But yes, you can change an existing column to NOT NULL:

alter table existing_table
   alter  the_column set not null;

Note that this will fail, if the column contains NULL values. In that case you need to first update all NULL values to something different, before you can run that statement.

See the manual for more examples.

Another option is to use a check constraint, which allows you to name the constraint:

alter table existing_table
  add constraint check_column_not_null check (the_column is not null);


case statement using characters for sum value

Test

SELECT CASE SUM(CASE WHEN Compressed = 'Yes' AND auto_compressed = 'Compressed'
                     THEN 1
                     ELSE 0
                     END)
       WHEN 0 THEN 'Disabled'
       ELSE        'Enabled'
       END AS Condition
FROM test


I switched to use cube based to take advantage of indexing:

CREATE INDEX aggregate_cube ON aggregate USING gist (ll_to_earth(latitude, longitude));

explain analyze select name from aggregate where earth_box(ll_to_earth(53.574753, -2.1), 100 * 1609.344) @> ll_to_earth(latitude, longitude);

 Bitmap Heap Scan on aggregate  (cost=9.06..259.53 rows=69 width=516) (actual time=0.218..0.476 rows=134 loops=1)
   Recheck Cond: ('(3779643.6387679, -143776.582140441, 5127079.5615671),(3789643.63851185, -133776.582396498, 5137079.56131104)'::cube @> (ll_to_earth(latitude, longitude))::cube)
   ->  Bitmap Index Scan on aggregate_cube  (cost=0.00..9.05 rows=69 width=0) (actual time=0.190..0.190 rows=134 loops=1)
         Index Cond: ('(3779643.6387679, -143776.582140441, 5127079.5615671),(3789643.63851185, -133776.582396498, 5137079.56131104)'::cube @> (ll_to_earth(latitude, longitude))::cube)
 Total runtime: 0.516 ms
(5 rows)


The first thing you should realize is that Accumulo is not a relational database. If you want to make it work like one, you need to create that functionality on top.

One way to do this would be to create rows that look like rows in a relational table, complete with foreign keys that you link together in your application's (an Accumulo client) code. Your client code must enforce the cascading updates/deletes and the other maintenance aspects of relations you may have come to expect from a relational database. Accumulo just stores and sorts Key/Value pairs for you. You decide the semantics of these Key/Value pairs.

Examples:

Note: For illustration purposes, Accumulo Key/Value pairs will be represented as:

RowID:ColumnFamily:ColumnQualifier -> Value

Employee table:

EmployeeID1:Data:Name -> Chuck
EmployeeID1:Data:Position -> Owner

EmployeeID2:Data:Name -> Barbara
EmployeeID2:Data:Position -> StoreManager
EmployeeID2:Data:Manager -> ManagerID1

EmployeeID3:Data:Name -> Tom
EmployeeID3:Data:Position -> SalesManager
EmployeeID3:Data:Manager -> ManagerID2

EmployeeID4:Data:Name -> Sally
EmployeeID4:Data:Manager -> ManagerID3

EmployeeID5:Data:Name -> Rick
EmployeeID5:Data:Manager -> ManagerID3

Manager table:

ManagerID1:Info:EmployeeID -> EmployeeID1
ManagerID1:Employees:1 -> EmployeeID2

ManagerID2:Info:EmployeeID -> EmployeeID2
ManagerID2:Employees:1 -> EmployeeID3

ManagerID3:Info:EmployeeID -> EmployeeID3
ManagerID3:Employees:1 -> EmployeeID4
ManagerID3:Employees:2 -> EmployeeID5

See how I've established foreign keys in both directions? Scanning the Manager table, you can quickly see which employees are managed by whom, but you'll have to look up each EmployeeID in the Employee table to see their information. Scanning the Employee table, you can quickly see information about a particular employee, including the ID of their manager, but you'll have to look up the manager's EmployeeID in the Manager table, and then look that up in the Employee table if you want the name of their manager.

This could be drastically simplified by combining the two tables into a single table and avoiding a separate ManagerID by using EmployeeID for both purposes. Something like:

One Employee table:

...
EmployeeID3:Data:Name -> Tom
EmployeeID3:Data:Position -> SalesManager
EmployeeID3:IsManagedBy:EmployeeID2 -> Since 2001
EmployeeID3:Manages:EmployeeID4 -> Since 2003
EmployeeID3:Manages:EmployeeID5 -> Since 2001
...

However, depending on your application, you may find that in a NoSQL database, you are better off flattening and duplicating your data, rather than normalizing with relations.

Flattened Employee table:

...
Tom:Data:Position -> SalesManager
Tom:IsManagedBy:Barbara -> Since 2001
Tom:Manages:Rick -> Since 2003
Tom:Manages:Sally -> Since 2001
...


Generating a generic INSERT/UPDATE Trigger to conform data?

No, there is no generic, column-agnostic way to join the INSERTED pseudo-table to the base table. You will need to use a conventional inner join and reference the PK column (or columns if the key is composite) explicitly in the join predicate.

Therefore, if the PK column is not the same in each of the tables you are generating the triggers for, there is no way around querying metadata to find out the column name to use. You can do that using the SQL Standard-compliant (well, more or less) INFORMATION_SCHEMA views (example) or using the native SQL Server system catalogues (example).


Looks like one cannot set up trigger on RESTORE DATABASE event

I can think of

Set up a regular SQL job to check/add permissions if needed
As this link, add a TRIGGER on [msdb].[dbo].[restorehistory]?


How do I configure MySQL to accept a custom column in mysql.users?

YOU ABSOLUTELY, POSITIVELY, CANNOT DO THAT. Why ???
Back on Apr 12, 2012, I wrote the answer to Cannot GRANT privileges as root
I carefully explained how the grant columns existed from MySQL 4.x to 5.6. I have explained this many times (See my others).
The columns in the mysql.user table need to be in the exact position they are published along with the exact column names.
On Oct 10, 2014, I answered MySQL service stops after trying to grant privileges to a user where I gave an example of how you can hack repairs into mysql.user to comply with the next version.
I highly recommend you remove your custom column today !!! Otherwise, you will NEVER get all your grants back. Please go manage your database user identification in another database.


According to above mentioned description it seems that story comments is a separate collection which will encapsulate comments related to several stories along with story Id.But that approach will cause a performance overhead since it will require multiple queries for retrieval of information.As an alternate solution comments can be stored into stories collection as embedded documents which will consume less time duration of retrieval of comments
Embedded documents are alternative to joins in MongoDB
For more detailed description regarding embedded documents please refer the documentation as mentioned in following URL

https://docs.mongodb.org/manual/tutorial/model-embedded-one-to-many-relationships-between-documents/


You don't use single quotes ' to quote identifiers in MySQL.  You use a backtick ` or you can use nothing at all, if the identifier isn't a reserved word.

'mid_initials' (255) IS NULL,

Should be

`mid_initials` VARCHAR(255) DEFAULT NULL,

...although using a VARCHAR(255) seems unnecessarily large.


Is it more secure to go through a 3rd database to connect two databases using the same login?

One thing jumps out here:

  The entire process uses the same set of login credentials

Problem

So hypothetical userX (whether some meatsack using Excel, or IIS AppPool Identity) can see some views and code. It doesn't matter what database these views and code are in because userX is setup in 3 databases anyway.

However, you lose ownership chaining like this.

Let's say WebDB.dbo.SomeProc calls PrivateDB.dbo.SomeTable. UserX requires permissions on both objects. If this was OneDB.WebGUI.SomeProc using OneDB.dbo.SomeTable then only the OneDB.WebGUI.SomeProc needs permissions. Permissions on referenced objects with the same owner are not checked.

Note: I haven't looked too deeply at cross database ownership chaining. I only know plain old "ownership chaining" well

Now, as per comments you really have 2 databases that can be combined. Not 3 which was originally implied. However, the intermediate and web can be combined.

The other "private" databases can perhaps be combined, but that'll be a separate issue. See the bottom link for a fuller discussion of "one database or many"

Solution?

If the extra databases are code containers only, then schemas are a better idea.

This sounds like you've used "Database" where you should use "Schema" (in the SQL Server sense, not MySQL sense). I'd have a WebGUI schema, a Helper or Common schema (to replace Intermediate database) and Desktop schema. This way you separate permissions based on clients and just have one database

With one database (in addition to "ownership chaining") you can also start to consider indexed views, SCHEMABINDING (I use it always) and such that can't be done with seperate databases

For more on schemas, see these questions:

Schema design - best practices?
Over use/correct use of schemas?

Finally, there appears no reason to have separate databases based on "transactional integrity not required". See this question to explain this:
 Decision criteria on when to use a non-dbo schema vs a new Database


Is there better way to 'touch' a row than performing a dummy update?

Well, if you want to change the xmin, then it needs to be ensured that other transactions can still see the old version of the row, so you need to make a copy with the new xmin, which you do with an UPDATE.  You can reduce the impact of this by updating a non-indexed column, to get the benefit of HOT.

Of course, this whole line of thinking is dubious, and perhaps you should be using some other facility altogether.


Do you know since when those segments are hidden(?), and is there a way to actually find their extents?

You can find their details from *_SEGMENTS views/synonyms.

SQL> select bytes from user_segments where segment_name='BIN$XYbYSYopQFTgUzIUqMA4vA==$0';

     BYTES
----------
     65536

  Will those extents still stop me from shrinking a data file (or tablespace)?

Yes, they may stop you to shrink the data files. Dropped tables and any associated objects such as indexes, constraints, nested tables, and other dependent objects are not moved, they are simply renamed with a prefix of BIN$.

Consider the following(it's just for demonstration purpose only) example.

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> create tablespace tbs1 datafile size 1M;

Tablespace created.

SQL> alter user jay quota unlimited on tbs1;

User altered.

SQL> create table t1(id number, col1 varchar2(100)) tablespace tbs1;

Table created.

SQL> insert into t1 select level, 'dsfldshfkjdhsfkdshfdklsjfhdkhfdks'||level
     from dual connect by level<=1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes from user_segments where segment_name='T1';

     BYTES
----------
    983040

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.

SQL> select ts#, name from v$tablespace;

       TS# NAME
---------- ------------------------------
        10 TBS1

1 rows selected.

SQL> select bytes from v$datafile where ts#=10;

     BYTES
----------
   1048576

SQL> select file# from v$datafile where ts#=10;

     FILE#
----------
        55

SQL> conn jay@orclpdb
Enter password:
Connected.
SQL> drop table t1;

Table dropped.

SQL> select object_name from recyclebin;

OBJECT_NAME
--------------------------------------------------------------------------------
BIN$XYY3afBNKwPgUzIUqMAIkA==$0
BIN$XYYmdj0XKLjgUzIUqMB2iA==$0

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> alter database datafile 55 resize 512K;
alter database datafile 55 resize 512K
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

SQL> conn jay@orclpdb
Enter password:
Connected.
SQL> purge recyclebin;

Recyclebin purged.

SQL> conn sys@orclpdb as sysdba
Enter password:
Connected.
SQL> alter database datafile 55 resize 512K;

Database altered.

  Hm, on my 12.2 system I see no BIN$ segments in *_SEGMENTS views (with SYS as SYSDBA user). And I think they never showed up in *_EXTENTS, or?

There is no recycle bin for SYSDBA privilege.

  Each user can be thought of as having his own recycle bin, since unless a user has the SYSDBA privilege, the only objects that the user has access to in the recycle bin are those that the user owns.
  What is Recycle Bin?


How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

Is that really your query and MV? tab3.col1 is not in it, but joining tab4 requires it because of tab4.col1 = tab3.col1. tab3.col1 = tab2.col2, so tab2.col2 = tab4.col1, but tab2.col2 is also not in the MV.

If I create this:

create table tab1(col1 number, col2 number, col3 number);
create table tab2(col1 number, col2 number);
create table tab3(col1 number);
create table tab4(col1 number);
alter session set query_rewrite_integrity=stale_tolerated;
CREATE MATERIALIZED VIEW tst_mv
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3
      FROM tab1, tab2, tab3
       WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;

explain plan for
SELECT  /*+ rewrite */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :B1;

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 1776227436

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     1 |    91 |     8   (0)| 00:00:01 |
|*  1 |  HASH JOIN            |      |     1 |    91 |     8   (0)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|      |     1 |    78 |     6   (0)| 00:00:01 |
|*  3 |    HASH JOIN          |      |     1 |    65 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS FULL | TAB1 |     1 |    39 |     2   (0)| 00:00:01 |
|*  5 |     TABLE ACCESS FULL | TAB2 |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |    BUFFER SORT        |      |     1 |    13 |     4   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS FULL | TAB4 |     1 |    13 |     2   (0)| 00:00:01 |
|*  8 |   TABLE ACCESS FULL   | TAB3 |     1 |    13 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - access("TAB2"."COL2"="TAB3"."COL1" AND
              "TAB4"."COL1"="TAB3"."COL1")
   3 - access("TAB1"."COL1"="TAB2"."COL1")
   5 - filter("TAB2"."COL2"=TO_NUMBER(:B1))
   7 - filter("TAB4"."COL1"=TO_NUMBER(:B1))
   8 - filter("TAB3"."COL1"=TO_NUMBER(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

TST_MV is not used.
If I create another MV with tab3.col1 included, then add use_nl, that MV is used with a nested loops join:

CREATE MATERIALIZED VIEW tst_mv2
BUILD IMMEDIATE
ENABLE QUERY REWRITE
AS
    SELECT tab1.col1, tab1.col2, tab1.col3, tab3.col1 as tab3_col1
      FROM tab1, tab2, tab3
       WHERE tab1.col1 = tab2.col1 AND tab2.col2 = tab3.col1;

explain plan for
SELECT  /*+ rewrite use_nl(tab4) */
       tab1.col1, tab1.col2, tab1.col3
  FROM tab1,
       tab2,
       tab3,
       tab4
 WHERE     tab1.col1 = tab2.col1
       AND tab2.col2 = tab3.col1
       AND tab4.col1 = tab3.col1
       AND tab4.col1 = :B1;

 select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 1384405329

-----------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |     1 |    65 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |         |     1 |    65 |     4   (0)| 00:00:01 |
|*  2 |   MAT_VIEW REWRITE ACCESS FULL| TST_MV2 |     1 |    52 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL           | TAB4    |     1 |    13 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - filter("TST_MV2"."TAB3_COL1"=TO_NUMBER(:B1))
   3 - filter("TAB4"."COL1"=TO_NUMBER(:B1))

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)


How do I make a locale available in Postgresql?

It looks like a syntax problem. This statement:

create collation fr_FR (LOCALE=‘fr_FR.utf8’)

has Unicode non-ASCII quotes LEFT SINGLE QUOTATION MARK and RIGHT SINGLE QUOTATION MARK instead of the normal ASCII double-quote. These fancy quotes are not delimiters for the SQL syntax, so they get interpreted as being part of the locale's name.

Try with:

create collation fr_FR (LOCALE="fr_FR.utf8");


Rewriting a query and eliminating subqueries

I think this will get you there... cavaet: I'm a SQL Server person...

SELECT
  ws_id
, n50
, num_singletons
, num_segments_less_100
, kmer
, num_all_reads
, number_bases_in_contigs
, num_bases_in_singletons
FROM
 stats_assembly sa
 join workflow_steps ws1
   on sa.ws_id = ws1.id
   and ws1.submodule_id = 2
 join workflows wf1
   on ws1.workflow_id = wf1.id
 join workflows wf2
   on wf1.sample_id = wf2.sample_id
 join workflow_steps ws2
   on wf2.id = ws2.workflow_id
   and ws2.id = 462


SSIS: How to check if a record DOESN'T exist in flat file but exists on the database

How do I check to see if the record is not in .CSV file but it exists in SQL database?

Have a staging table (e.g. dbo.tmp_DOCTORS or whatever naming convention that you follow) that will first truncate (everytime you load a CSV, make sure to truncate the staging table) and then import the entire CSV. Then you can update the main table by checking if a particuliar record is in the staging table or not.

This way you get better control of the process and only update the main table or add new records to the main table. You can do it using TSQL (Merge) or SSIS.

This is what we do and it incurs less overhead on the main table, since you will update or add only records that have changed.

Also, I really like the idea of doing a soft delete IsActive = TRUE or FALSE. This way you can preserve the history as well.


Not possible exactly as you intend, but here are some tools:

For your point in time, try a DB Snapshot:

http://msdn.microsoft.com/en-us/library/ms175876.aspx

To revert from DB Snapshot:

http://msdn.microsoft.com/en-us/library/ms189281.aspx

Now that would revert in total, not leaving the intended T2, but would keep you from having to deal with restoring backups.

What you could do is with the DB still live and with a DB Snapshot available, then use a tool like Redgate for DML compare (compare live DB to DB Snapshot):

http://www.red-gate.com/products/sql-development/sql-data-compare/

You'd have to then pick and choose what DML you wanted to apply, and use Redgate to generate the script. Then you could revert with the snapshot, and apply the Redgate script to include only the DML you wanted to retain.

That's as close to meeting your requirement as comes to mind. Otherwise there's no way to say "roll this one back and not that one".


Oracle 11. Updating BLOB field. Db file sequential read inappropriately slow?

Already answered at a parallel thread on serverfault:  https://serverfault.com/questions/345253/oracle-11-updating-blob-field-db-file-sequential-read-inappropriately-slow/345588#345588

  In Oracle, LOB (including BLOB) is stored as:

  in-the-table LOB - if the LOB is smaller than 3900 bytes it can be stored inside the table row; by default this is enabled, unless you
  specify DISABLE  STORAGE IN ROW
  normal LOB - stored in a separate segment, outside of table, you may even put it in another tablespace; for these:

  a minimum of CHUNK bytes are allocated and entirely redo-logged (even if LOB has only 1 byte)
  there is an internal intermediate index behind a LOB column, which gets contentious on updates and may practically serialize them
  access is multi-level and thus relatively slower
  with NOCACHE option, the waiters are "direct path read" - the default
  with CACHE option, the waiters are "db file sequential read"

  where CACHE_SIZE_THRESHOLD is not taken into account, so a large LOB can waste your cache

  Therefore, if your LOBs are larger than 4 kB they will get relatively
  slow, and this may simply be your case. I would examine the sizes.

  I would examine USER_LOBS (or DBA_LOBS) to see how the "good" and
  "slow" LOB columns differ in their definitions.

  The Metalink note ID 66431.1 describes this and may be of interest to
  you, if you have access there.

UPDATE - see link above, I've had some thoughts.


SQL Server's aggregate functions ignore nulls. How do I correct this?

You can simulate this by applying a where clause to your query:

with test_data (col) as (
   select null union all
   select 1 union all
   select 2
)
select sum(col)
from test_data
where not exists (select 1
                  from test_data
                  where col is null);

Edit

Andriy is right, this can also be written as:

with test_data (col) as (
   select null union all
   select 1 union all
   select 2
)
select case
          when count(*) = count(col) then sum(col)
          else null
       end
from test_data;


Login lacks connect endpoint permission in SQL Server - Error 18456, State: 149

Analysis

If you have a search for the Error 18456 you will eventually find the following blog post from Aaron Bertrand:

Troubleshooting Error 18456 (SQL Blog / Aaron)

Which lists the following for states 146...149:

  These states replace states 11 and 12 above, but only in SQL Server 2016 or better. The goal was to make the actual underlying issue easier for the sysadmin to diagnose between SQL auth and Windows auth logins, and between connect and endpoint permissions (all without giving any further info to the user trying to log in). For more details, see the latter part of this post.

The this post link in the above quote references the article "Why do I get the infrastructure error for login failures?" over on CSS SQL Server Engineers blog/site.

The article cites these two possible reasons as the cause for the "Login-based server access validation failed with an infrastructure error":

  Does the login have the SERVER class permission named CONNECT SQL for this server instance?
  Does the login have the ENDPOINT class permission named CONNECT for the specific endpoint on which the client application established the connection?

Because SQL Server 2016+ has new error messages for Level 146...149 errors, you have been presented with the specific error message Login lacks connect endpoint permission. (Level 149), which leads you to the second option from above.

Have a look at your current enpoint permissions with the following script:

SELECT * FROM sys.server_permissions AS sp2
    JOIN sys.server_principals AS sp
        ON sp2.grantee_principal_id = sp.principal_id
    LEFT OUTER JOIN sys.endpoints AS e
        on sp2.major_id = e.endpoint_id
WHERE sp2.permission_name = 'CONNECT'
AND sp2.class_desc = 'ENDPOINT'

You should have at least one entry for the combination of GRANT, public and TSQL Default TCP

Solution

Check the CONNECT permissions on the ENDPOINT for the SQL Server Login for TCP or simply grant the permission to the account:

GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO public;

(replace public with your <SQL Server Login> if you only want to assign that permission to a specific account)

To see a list of endpoints run:

select * from sys.endpoints

Further Reading / Reference List

Troubleshooting Error 18456 (SQL Blog / Aaron)
Why do I get the infrastructure error for login failures? (CSS SQL Server Engineers)
Endpoints Catalog Views (Transact-SQL) (Microsoft Docs)
GRANT Endpoint Permissions (Transact-SQL) (Microsoft Docs)


Since a table might only be referenced once a year (think annual reports), and since they may be referenced only in some developer's (or your CEO's) code or spreadsheet on their desktop, these approaches do not work:

tracing the database to see if any tables aren't referenced for a day / week / month
finding some reliable way to determine when the table was last used (this is not easy)
searching the codebase

The safest approach would be to rename the tables that you have already identified through normal means (like the ones above) as likely to be no longer used, and stand back to see if anyone complains. This way you can restore them quite easily if anyone should complain (and hopefully any interim breakage is in low-impact areas, otherwise these tables should have been identified better).

However, you still need to wait a full business cycle before assuming that no news is good news, as per my very first sentence.


Is the SQL syntax for InnoDB gauranteed to be the same between MySQL, MariaDB and Percona?

The InnoDB/XtraDB is a table engine, it has no say in parsing SQL. Thats another layer, which should be mostly engine independent.

MariaDB 5.5 is supposed to be 99.99% SQL compatible with MySQL 5.5 (there were some minor hiccups) and MariaDB 10.x is backward compatible with 5.5.

With MariaDB 10 and MySQL 5.6 (and now 5.7) the paths diverged somehow - some features were implemented in one but not other, some were implemented in both but with some differences (GTID, virtual/computed columns, differences in optimizer, ...).

So the answer is: There should be no difference in "standart" queries between using InnoDB/XtraDB or even MyISAM as both are "under" the SQL level of MySQL/MariaDB structure. You only have to be aware of differences in syntax for new features in 5.6+/10.0+ and those are not because of the engine differences (virtual columns for example are not handled by InnoDB afaik, but by the SQL layer - permanent computed column is just another normal column for InnoDB with the higher layer supplementing the data and the non-stored variant does not "touch" the engine at all and is always computed after the engine returns the columns needed).


SQL Server Connection property applicationIntent

Hopefully the documentation for the SQL Server Native Client clears things up for you, specifically this section on Specifying Application Intent

  The keyword ApplicationIntent can be specified in your connection string. The assignable values are ReadWrite or ReadOnly. The default is ReadWrite.

  When ApplicationIntent=ReadOnly, the client requests a read workload when connecting. The server enforces the intent at connection time, and during a USE database statement.

Since you mentioned that this is in the context of Availability Groups (AGs), specifying an intent of ReadOnly will connect you to the readable secondary in your availability group (as long as you are connect to the listener, and you're using Enterprise edition).

The enforcement, then, is due to the fact that the secondary is read-only - you can try to perform data modifications through this connect, but they will fail.

Note: for more complex setups, with multiple readable secondaries, you can Configure read-only routing as well


Round to the beginning of the month:

SELECT     trunc(EntryDate,'MM'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM');

In oracle 8i the results are sorted because of the internal algorithm used by the database to do the 'GROUP BY'. In higher Oracle version beginning with 10g a different algorithm is used and the results will not be sorted. So if you want a sorted result you should tell it the database by querying

SELECT     trunc(EntryDate,'MM'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM')
ORDER by   trunc(EntryDate,'MM');

even if the result will be  sorted by accident.

To get exactly the output of your query execute the following statement:

SELECT     to_char(trunc(EntryDate,'MM'),'MonYY'), COUNT(OrderNo)
FROM       Orders
GROUP BY   trunc(EntryDate,'MM')
ORDER by   trunc(EntryDate,'MM');


Which one for MySql Bind addresss 127.0.0.1 (in apache & ubuntu)

Many Linux distributions source the contents of /etc/mysql/conf.d after reading /etc/mysql/my.cnf, so that the package maintainer can make whatever changes they want to my.cnf and the system administrator can keep only their customizations in the conf.d directory, which makes upgrades much easier.

However, quoting straight out of my /etc/mysql/my.cnf file provided by Debian Linux (I've got MariaDB, but Debian policy handles it the same way as they would have MySQL):

  The MariaDB/MySQL tools read configuration files in the following order:

  "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
  "/etc/mysql/conf.d/*.cnf" to set global options.
  "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
  "~/.my.cnf" to set user-specific options.

On my system, the default bind-address is set in /etc/mysql/mariadb.conf.d/50-server.cnf which would be read after my customized setting in /etc/mysq/conf.d/isaacs_totally_great_customizations.cnf, and override my setting from there — I'd have to set it in the mariadb.conf.d folder. You don't say which distribution you're using, but you should look in /etc/mysq/my.cnf for some hints about the order of loading. At the end, you'll probably see a line like !includedir /etc/mysql/mysql.conf.d/ which will show you which folder is loaded last.

My opinion is that, if the conf.d directory is loaded last, that's the "best" place to put it and least likely to be affected by your next upgrade. Of course, the simplest solution would be to just make your bind-address statement the last line of my.cnf which would pretty simply solve the problem without any lingering doubts.


Query takes forever with a NOT EXISTS condition

The OR condition in your WHERE clause is killing the performance. You only need to NULL-check one of the keys you are joining with, not both.

Also, no need to do a SELECT * FROM tableUCP when just SELECT uc_id, ce_id... would be enough.


In your case restoring database is taking more time, so I suggest to use AWS database migration service.

It's very simple,less human work and fastest method. And also your real time data will sync.
Instead of replication use this.


Auto scaling MongoDB as a service in Microsoft Azure

MongoDB Atlas in a managed database as a service officially provided by MongoDB and it is readily available in all the three (aws, azure, gcp) major cloud providers.

Automated MongoDB service on Azure


What DSN file is MS Access using for ODBC Linked Tables?

IIRC, Access uses either a machine DSN or a file DSN. I only have Access 2016 installed but I don't think this has changed much. There should be an External Data tab in the ribbon, click on ODBC Database. This shows you two tabs: File Data Source and Machine Data Source.

This image shows the MS Access Database source but I generally see folks create one specific to the Access database they created.

You can also views this by opened the ODBC Data Sources from Administrator tools. There is a 32 and a 64 bit version of this and you may need to check both.

Also, with regards to Access storing the connection itself. It is possible to setup a DSN-less connection using macros. You can see this KB on how that is done to know where to check in your Access database.


MariaDB CONNECT engine BIN table - file record layout, storage of a date data type, padding

1   0   0   0  -- 1; note "little-endian"
49  50  51  52  53  54  55  56  57  48  -- ascii/utf8 for "1234567890"
32  32  32  32  32  32  32  32  32  32
32  32  32  32  32  32  32  32  32  32 -- lots of spaces for padding
48  93  116 73   -- must be the date?
97  98  99  100 101   -- ascii/utf8 for 'abcde'
32  32  32  32  32
32  32  32  32  32   -- lots of spaces for padding
184 30  133 235 1   106 248 64  -- 100000.12 --> hex: 40 f8 6a 01 eb 85 1e b8
210 4   0   0  -- 1234 = 210 + 4*2^8 + 0 * 2^16 + 0 * 2*24

On INT(4), the (4) has no meaning (unless you also use ZEROFILL).  INT is always 4 byte, but not because of (4).

DOUBLE is always 8 bytes; (m,n) should not be used.

The spaces are padding CHAR(n) CHARACTER SET utf8 out to 3 bytes per character.  This is an argument for almost always using VARCHAR, not CHAR.

With utf8mb4 there would be even more spacing.


Using bigint compared to int has, at least, these potential performance drawbacks:

the data will use more pages on disk

this can affect how long it takes to read data from disk when it's not in RAM
it will also make any maintenance operations involving those fields take longer (backups, index rebuilds, CHECKDB)

these data pages will take up more space in RAM

this means you'll either need to purchase more RAM, or incur the cost of reading from disk more often

memory grants that include these columns will be larger

this affects memory-consuming query plan operators, like sorting and hashing data
as a secondary impact, this has the effect of reducing concurrency

That being said, how much practical impact these things have for you is very dependent on your specific environment and workload.

Note: the increases in disk and RAM usage issues can be mitigated by using row compression, at the cost of increased CPU usage.  As servers often have more CPU overhead than RAM, this is generally a good tradeoff (thanks to Andy for this reminder!)


SQL Server: Failed to monitor external signals

This is because you need make the hosts /proc accessable in the chroot, in order to do that you can use a --bind mount,

sudo mount --bind /proc /opt/mschroot/proc

Where /opt/mschroot is the location of your chroot.


Query a name in large database, optimizations?

I understand the feeling that 1mil+ rows is "Very Large", however you should understand that to a lot of database people you aren't getting into a large table until you have 10s-100s of millions of rows.  And even then that is middling large.

That being said if I understand what you are asking for you are looking for "indexes".  This is a fairly large concept at times and what index(s) you need really depends on what queries you are running.

I can think of two major categories though that might match what you are looking for.  The first is simple.  You have the name in a column and you want to search the table for everyone with a given name.  For example you are looking for everyone with the name Ken and you have a query like this.

SELECT * FROM TableName WHERE NameColumn = 'Ken'

or you want to get everyone's name that starts with Ken and your query looks like this.

SELECT * FROM TableName WHERE NameColumn LIKE 'Ken%'

In this case a simple index on the NameColumn column of your table will suffice and should give you excellent performance.

If however you want to search for Bob and get everyone who has names such as Bobbie, Robert, Rob etc.  Then I believe you want full text indexing.  This is a bit more complicated and has a number of requirements to set up.  If this is what you need then I suggest either having an in house DBA do the work (someone familiar with full text indexing) or do some research yourself.  For example for SQL Server 2012 the BOL entry is Here.

I'm a SQL Server person so my answers are going to bias that way but in all of the RDBMS' that I know of indexing in one form or another is how you improve basic performance of a well written query on a well designed system.  If you need help with the design of the query or system then you will have to provide a great deal more information starting with which RDBMS you are using and your table structure and the query you are trying to optimize.


Convert hierarchy in an ordered list of rows

Probably you need tree nodes in depth-first  order

with tag as (
    select * from (values
     (1,'.Net',cast(null as int)),
     (2,'EF',1),
     (3,'NHib',1),
     (4,'CF',2),
     (5,'Java',null),
     (6,'JRE',3)) t([Id],[Name],[ParentTagId])
),
cte (ParentID, ID, Name, lev, shift)AS(
  SELECT   ParentTagID,
                ID,
                Name,
                1,
                row_number() over(order by id)
       FROM     Tag
       where ParentTagID is null
       UNION ALL
       SELECT   e.ParentTagId,
                e.ID,
                e.Name,
                lev + 1,
                shift * 100 + row_number() over(order by e.id)
       FROM     cte
       INNER JOIN Tag AS e ON e.ParentTagId = cte.ID
)
select *,
   CAST(cte.lev - LAG(cte.lev, 1, 0) OVER(ORDER BY cast(shift as varchar(50))) as int) as LevelDynamic,
   CAST(ROW_NUMBER() OVER(ORDER BY cast(shift as varchar(50))) as int) AS RowNumber
from cte
order by cast(shift as varchar(50))


SQL Server: does ddladmin rights contain datareader and datawriter rights to the database?

I don't believe so. It should only allow them to run DDL not access tables.

You could make your own role and make it be a member of the db_ddladmin, db_datareader and db_datawriter roles.


Create multiple columns from a single column filtering criteria?

I read the chapter Generating Summaries in MySQL Cookbook, this is where they explain that you can create count, sum and other methods inside your query to gain the proper values in those columns you define, this is known as statistical queries and then this is the query I ended up using:

SELECT user_id as userid, count(id) Total,
COUNT(IF(revision = 'pending', 1, NULL)) as pending,
COUNT(IF(revision = 'approved', 1, NULL)) as approved,
COUNT(IF(revision = 'rejected', 1, NULL)) as rejected
FROM table1
GROUP BY user_id

This will fill those values by count only if criteria is met.


You can use Row Count tasks in your data flow to determine how many rows pass through which part of your solution. This can be helpful if you put it immediately after a source and before a destination... assuming that you are actually performing logic that could eliminate rows.

People will often record these values to SSIS variables and then write this information to a log table of rows read and written per package per execution. Of course, I've seen very few take the time to validate or create tests on all this custom logging work which often times is merely a check on Microsoft's tools. Take the time to consider what new issues you're introducing into your solution, points of failure and dependencies, and the benefits you're creating.

There are good designs that reside in the DB that can easily answer such questions and more...

[AuditInsertDate] DATETIME NOT NULL DEFAULT GETDATE()

Covers additional use cases, provides timing information, and tracks your insert counts (in most load scenarios). It's also a lot easier to implement and maintain than custom SSIS logging.