Should arrays of IDs stored in Postgres use varchar, int, or another type? What is proper convention for creating a field that will hold an array of foreign keys? In this particular case, I'm creating a field called "owners" that will hold an array of user IDs. I want to make sure I choose the right type to maximize speed and follow convention.

Thanks.

The "one true way" is to create a table and use foreign keys:

CREATE TABLE owners(
    user_id int references users
  , account_id int references accounts
  , primary key(user_id, account_id)
);

That way you can verify membership using a single and simple query:

SELECT user_id, account_id FROM owners WHERE user_id = X AND account_id = Y

And you can get a list of owners for an account using:

SELECT user_id FROM owners WHERE account_id = X

Alternatively, you can find all accounts a user owns using the reverse query:

SELECT account_id FROM owners WHERE user_id = X


Stored procedure for adding cascade I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add their corresponding fk's with its on delete cascade?

As simple answer to the question, yes, it is possible to do this with a function.

PostgreSQL has several very powerful function languages that you can use for this purpose. Being powerful, you can do some pretty neat things with them. You can also shoot yourself in the foot very easily.

The solution is very brute-force and inelegant, and I wouldn't recommend using it on a production database at all.

The function that answers this doesn't give you any output in the logs, and it's basically an all-or-nothing proposition in your database, so running it in a transaction is essential.

I would highly recommend figuring out exactly what it's doing and why it is doing it before running it.

It would be better to explicitly create an sql script to add ON DELETE CASCADE for each key that you want to update. This would be the most flexible, introspect-able, and modify-able solution to your problem.

The code below worked on the pgbench tables and their foreign keys for me in a test database I created. I don't guarantee it will work in other cases.

It looks something like this:

BEGIN;

DO $$DECLARE r record;
   BEGIN
      FOR r IN SELECT c.relname, conname, cr.oid AS croid FROM pg_catalog.pg_constraint cr, pg_catalog.pg_class c WHERE c.oid = cr.conrelid AND cr.contype = 'f' ORDER BY 1
   LOOP
      EXECUTE 'ALTER TABLE '||quote_ident(r.relname)||' DROP CONSTRAINT '||quote_ident(r.conname)||',ADD CONSTRAINT '||quote_ident(r.conname)||' '||pg_catalog.pg_get_constraintdef(r.croid, true)||' '||' ON DELETE CASCADE';
   END LOOP;
END$$;

It dynamically creates

ALTER TABLE foo DROP CONSTRAINT bar_fk, ADD CONSTRAINT bar_fk FOREIGN KEY (baz) REFERENCES bar(baz) ON DELETE CASCADE;

and executes it in a loop in the anonymous function for every referenced constraint in pg_catalog.pg_constraint of type f (foreign key), joined to the pg_catalog.pg_class table to get the relation name. If you needed it to be smarter than that, you'd want to alter the statement in the loop accordingly to fit your needs.

Once you've checked the results for your tables with \d or some other method, and are satisfied with the results, then you execute the COMMIT at the end.


Database recovery procedure I recovered a database with last full backup, all differentials after the full, and then all log backups after the last differential.

Can I trust the database is right or do I have to do it again the right way: with full, then last differential, then all transaction backups after that?

No, you dont have to do it again.

The differential backups are just that so the only effect your mistake made was to make the restore time longer than neccessary. If your last transaction log backup was restored successfully then you are ok.


postgresql v8.4 function runs but doesn't work I have a postgresql v8.4 database. I need to update several hundred tables with about 3 million rows of data each.
The customer wants a new column added (which I added) and now I'm trying to update the new column with data.
 I created some SQL statements to do the update. This works if I run it manually in pgAdmin (just the sql statements); so I tried putting this into a function to automate the UPDATE, like so:

CREATE or REPLACE function update_ee() RETURN void as
$$
DECLARE
  row RECORD;
BEGIN
   FOR row IN SELECT tablename FROM pg_tables WHERE schemaname = 'public' and tablename like '%_2015060%' order by tablename asc;
   LOOP
   RAISE LOG 'removing index for %', row.tablename;
   -- remove indices
   EXECUTE 'DROP INDEX ' || row.tablename || '_start_idx;';

   RAISE LOG 'starting update on %', row.tablename;
   -- update table
   EXECUTE 'UPDATE ' || row.tablename || ' set ee = array_to_string(regexp_matches(xml_data, ''<EE_ID>(.+?)</EE_ID>''), '';'') where elnot is NULL and xml_data is not null;';

   RAISE LOG 'restoring index on %', row.tablename;
   -- put indices back
   EXECUTE 'CREATE INDEX ' || row.tablename || '_start_idx on ' || row.tablename || ' USING btree(start);';
   END LOOP;
  RETURN;
 END;
$$
LANGUAGE plpgsql;

 So the function runs; the updates tend to slow down a bit with each successive table; the log statements all appear in the log file. It appears to be working; but upon finish not one table has been updated.
 What did I do wrong?
 Is it not possible to update tables from a function? Is the language wrong? Should I use SQL instead of plsql?
 Thanks in advance...

When trying the original function code under Postgres 9.3, it would not compile.  I had to change 2 minor things, which I have commented and labelled 1 and 2.  Testing the function with several small tables worked like a charm - I have used Postgres 9.3 and 8.4 (see dbfiddle).
Example code:

create table a_2017 (id int primary key, start int);
create index a_2017_start_idx on a_2017 using btree(start) ;
create table b_2017 (id int primary key, start int);
create index b_2017_start_idx on b_2017 using btree(start) ;
create table c_2017 (id int primary key, start int);
create index c_2017_start_idx on c_2017 using btree(start) ;
create table d_2017 (id int primary key, start int);
create index d_2017_start_idx on d_2017 using btree(start) ;
create table e_2017 (id int primary key, start int);
create index e_2017_start_idx on e_2017 using btree(start) ;

insert into a_2017 values (1,1),(2,2),(3,3),(4,4),(5,5);
insert into b_2017 values (11,11),(22,22),(33,33),(44,44),(55,55);
insert into c_2017 values (111,111),(222,222),(333,333),(444,444),(555,555);
insert into d_2017 values (1111,1111),(2222,2222),(3333,3333),(4444,4444),(5555,5555);
insert into e_2017 values (11111,11111),(22222,22222),(33333,33333),(44444,44444),(55555,55555);

Function: 1 returns (instead of: return), 2 no semicolon.  select in round brackets.

create or replace function update_2017()
returns void -- <--_1_
as $$
DECLARE
  row RECORD;
BEGIN
   FOR row IN (
     SELECT tablename
     FROM pg_tables
     WHERE schemaname = 'public' and tablename like '%_2017%'
     order by tablename asc ) -- <--_2_
   LOOP
      RAISE LOG '-> removing index for %', row.tablename;
      -- remove indices
      EXECUTE 'DROP INDEX ' || row.tablename || '_start_idx;';

      RAISE LOG '--> starting update on %', row.tablename;
      -- update table
      EXECUTE 'UPDATE ' || row.tablename || ' set start = start*1000;';

      RAISE LOG '---> restoring index on %', row.tablename;
      -- put indices back
      EXECUTE 'CREATE INDEX ' || row.tablename || '_start_idx on ' || row.tablename || ' USING btree(start);';
   END LOOP;
   RETURN;
END; $$
LANGUAGE plpgsql;

Then, for testing the function:

select update_2017();

select * from a_2017
union
select * from b_2017
union
select * from c_2017
union
select * from d_2017
union
select * from e_2017
;

-- output: see last section of the dbfiddle.

With bigger tables (one million rows), the execution of the function was slightly slower, but worked nonetheless.  Maybe you should have another shot at this ...


Is there any way to determine how many rows a transaction has modified (before the transaction ends)? I have a ~320 million row table on which I run the following query:

UPDATE my_table SET state = TRIM(state)

Needless to say this has been running for 90+ hours (since thursday afternoon) and has not finished yet.

I'd like to know, more or less, how many rows it has modified until now.
Is there any way to obtain this information from the running transaction?

Trying to search I have found how to determine a rough estimate of how many new rows are being added to a table using the pgstattuple function:

select dead_tuple_count from pgstattuple('my_table');

But this does not seem to show any interesting information for how many rows have been modified.

Following @Nick Barnes suggestion to use pg_table_size I was able to obtain a rough estimate of progress. Note however that this can only work when only one transaction is modifying the table. If you have multiple transactions modifying the table you will not be able to tell how much a specific transaction has completed.

Moreover in order for you to be able to get a progress estimate you should know how many rows are affected by the query.

So using the command:

SELECT pg_table_size('my_table');

You can obtain the size of the table (this contains both the commited and uncommitted data).

If you know the initial size of the table initial_size, the initial number of rows N, and the number K of rows affected by the query you can get an estimated of the data that your query will write:

delta_size = (initial_size/N)*K

Now if the current table size is current_size your estimate for progress will be:

progress_perc = 100*(current_size - initial_size)/delta_size

If you do not know the initial size of the table you can estimate it by checking the growth of the table.

Using pg_table_size check how much the table grows in a certain interval of time delta_t, say 1 hour. Then you can estimate the initial size from the equation:

initial_size = current_size - growth*num_delta_t_passed

You can obtain the time since when the transaction started using the query:

SELECT pid, age(clock_timestamp(), query_start), usename, query
FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

(taken from this gist page)

For Postgresql 9.6 it does not seem possible to obtain an accurate response. I don't know whether a better option exists for Postgresql 10+.


SQL Server 2016 SQL Agent Token Not Working I have this in scores of SQL Agent Job steps throughout my enterprise and it works as expected:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

But on my new SQL Server 2016 instance, it simply produces a Named Pipes connection error (which is a complete red herring).

This, on the other hand, works just fine on my new server:

sqlcmd -E -S MyExplicitServerName -d master -Q "EXECUTE MyStoredProc etc..."

Why does the SRVR token not work?
If I fire up sqlcmd in a command prompt and tell it to print $(ESCAPE_SQUOTE(SRVR)) it says:

'SRVR' scripting vaiable not defined.

This is a pretty basic, no-frills install with just the default instance.

Okay, I'm now thinking this is something with Windows Server 2016 as this is the first SQL Server 2016 install I've done on Windows Server 2016.

On my Windows Server 2012 R2 (Standard) machine with SQL Server 2016, this works in a SQL Agent CmdExec job step:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

But on my new Windows Server 2016 (Standard) with SQL Server 2016, the above does not work.  I have to do either:

C:\Windows\System32\cmd.exe -c sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."

Or this works as well:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE MyStoredProc etc..."
GO

The cmd.exe -c argument (I was unfamiliar with) is a batch terminator; from Microsoft docs:

  -c batch_terminator

  Specifies the batch terminator. By default, commands are terminated
  and sent to SQL Server by typing the word "GO" on a line by itself.
  When you reset the batch terminator, do not use Transact-SQL reserved
  keywords or characters that have special meaning to the operating
  system, even if they are preceded by a backslash.

I don't know why the batch terminator is necessary in this situation...

  The GO Command and the Semicolon Terminator

  The GO Command

  “GO” is a batch terminator. Technically speaking, the GO command is
  not even a part of the Transact-SQL language. It is really a command
  used by the SQLCMD, OSQL and ISQL utilities that can also be used
  within Query Analyzer and the Query Editor window.

  NOTE: A batch should not be confused with a script. A batch is a set of T-SQL statements that are submitted for execution as a group. A
  script is simply a file containing set of T-SQL statements. One script
  can contain many batches.


Filter Stored Procedure Result (Informix) IBM Informix Dynamic Server Version 11.50.UC5XA.
I have a Informix database. This database contain a stored procedure with name of 'sp_agent_details' that get two DateTime Parameter. This stored procedure results about 27 columns and I want only few columns of it for reporting. I try this syntax:

select AGENT_NAME, AGENT_LOGIN_ID from
       TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))

This Result such Error:

java.sql.SQLException: Illegal SQL statement in SPL routine.

Next I try this syntax:

select AGENT_NAME, AGENT_LOGIN_ID from
       TABLE(MULTISET{sp_agent_call_summary
    ('2014-02-04 04:00:00', '2014-02-04 23:00:00')})

Result:

 java.sql.SQLException: Function (informix.sp_agent_call_summary)
returns too many values.

then I tried this:

select AGENT_NAME, AGENT_LOGIN_ID from
   TABLE(sp_agent_detail('2014-02-04 04:00:00', '2014-02-04 23:00:00'))
   (AGENT_NAME, AGENT_LOGIN_ID, other columns that are returned by SP)

Result:

java.sql.SQLException: Illegal SQL statement in SPL routine.

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

References at IBM Informix 11.50 manual:

select from syntax...
procedure named return parameters

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

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

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

end procedure
;
Routine created.

;

execute procedure dummy(10);

         id name       created

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

10 row(s) retrieved.

select  * from table(dummy(10));

unnamed_col_1 unnamed_col_2 unnamed_col_3

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

10 row(s) retrieved.

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

         id name       created

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

10 row(s) retrieved.

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

         id name

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

10 row(s) retrieved.

Database closed.


Are there any in-memory hybrid databases available? I am looking for a database that is -

Relational (Not NoSQL).
Has an in-memory store.
Has disk-based backing storage.

I want to set up a system where entire contents of the disk are replicated in memory. So writes are made to both the in-memory store and disk, but reads are done only against memory.

Most of the databases I have seen such as H2  or HSQLDB offer in memory grid or disk based tables. I am looking for an in-memory grid WITH disk based backing storage.

Is there such a database system available or is it possible to configure MySQL (or some other) database to work in such a manner?

H2 and HSQLDB will cache the tables in memory, even if you use "persistent" tables.

In HSQLDB you can control the amount of memory that is used to hold table data, using the SET FILES CACHE SIZE statement. I think H2 has something similar.

So if you create cached tables and make the data cache large enough you will wind up with tables stored on disk but held in memory.


How to delete multiple values? I have 3 identical values:

2814|Venerdì 13  |1980   |Orrore      |1:30    |Dvd      |Italiano
2815|Venerdì 13  |1980   |Orrore      |1:30    |Dvd      |Italiano
2816|Venerdì 13  |1980   |Orrore      |1:30    |Dvd      |Italiano

I have tried to delete mutiple with this query

delete from titolo where idtitolo > '2814'  and idtitolo < '2815';

But this deleted 0 lines.
How delete two values using one command?

The less than and greater than are not inclusive, there is no number in the list you gave that is more than 2814 AND less than 2815.

Try this instead:

DELETE
FROM titolo
WHERE idtitolo BETWEEN 2814 AND 2815


storing query results in a variable in mysql and using it in another query I have a following query which runs perfectly fine:

SELECT count(*) as total,l.state as state
            FROM
                 db1.table1   cd
                 inner join db2.table2 l ON
                LEFT(cd.threestring_vch, 3) = l.NPA
            AND
                MID(cd.threestring_vch, 4,3) = l.NXX
            WHERE
               starttime_dt >= '2014-12-18 00:00:00'
            AND
               starttime_dt <= '2014-12-18 23:59:59'
            AND
                Result_int = 76
            GROUP BY
                l.state

However, when I try to do the following and run the query, I get and error:

          Error Code: 1241. Operand should contain 1 column(s)  0.016 sec

Not sure, what is wrong here as I am referring to one related post which says to do what I am doing right now.

SET @v1 := (SELECT count(*) as total,l.state as state
            FROM
                 db1.table1   cd
                 inner join db2.table2 l ON
                LEFT(cd.threestring_vch, 3) = l.NPA
            AND
                MID(cd.threestring_vch, 4,3) = l.NXX
            WHERE
               starttime_dt >= '2014-12-18 00:00:00'
            AND
               starttime_dt <= '2014-12-18 23:59:59'
            AND
                Result_int = 76
            GROUP BY
                l.state);

Here is my ultimate goal:

I want to use the value returned by above query in above variable v1 like the following:

SELECT state , SUM(total) as TOTAL

FROM  v1
GROUP BY state
ORDER BY state ASC

So basically  I will be doing UNION of above two queries.

Please let me know what I am doing wrong.

Thanks

The post you link to only discusses capturing a single scalar value into the variable, not an entire result set.

You are trying to capture more than one column and presumably more than one row.  MySQL variables can only hold one scalar value.

It seems like what you are looking for is a temporary table, but on the other hand, your explanation is difficult to understand... why would you group by state when the source data is grouped by state?  And what do you mean by the union of the above "two" qieries?  The result of the last query should be identical to the one before it, if such  construct were possible.


how to set Mongod config set multiple bindIp addresses I have the following config file:

net:
  bindIp: 127.0.0.1
  port: 27017

storage:
  dbPath: c:\mongoDATA\db
  journal:
    enabled: true

##systemLog:
 ## destination: file
 ## path: C:\mongoDATA\log\mongodb.log
 ## logAppend: true

this works, but when I try to add multiple ports like:

bindIp: 127.0.0.1, 192.168.10.115
or
bindIp: "127.0.0.1, 192.168.10.115"
or
bindIp: [127.0.0.1, 192.168.10.115]

with or without the space after the comma it doesn't.  Could someone provide the write syntax?  Thanks  (using 3.6 community)

without a space after the comma, AND the ip address has to present


A query to find patients with any 4 visits within a 365 day period I need to find our high utilizers. I have this query:

with Visitlist as(
select v.PatientID, a.VisitID,  a.AdmitDateTime,
rank() over (partition by PatientID order by AdmitDateTime desc) as VisitNumber
from AdmittingData a
inner join MriPatientVisitEvents v on a.VisitID = v.VisitID
where
a.AdmitDateTime >= '2016'
and v.PatientID is not null)

select distinct v1.PatientID,
v1.AdmitDateTime, v1.VisitNumber, v2.AdmitDateTime, v2.VisitNumber,
v3.AdmitDateTime, v3.VisitNumber, v4.AdmitDateTime, v4.VisitNumber
from Visitlist v1
inner join Visitlist v2 with (nolock) on v1.VisitNumber = v2.VisitNumber - 1 and v1.PatientID = v2.PatientID
inner join Visitlist v3 with (nolock) on v1.VisitNumber = v3.VisitNumber - 2 and v1.PatientID = v3.PatientID
inner join Visitlist v4 with (nolock) on v1.VisitNumber = v4.VisitNumber - 3 and v1.PatientID = v4.PatientID
where
v1.VisitNumber = 1
and datediff(day, v4.AdmitDateTime, v1.AdmitDateTime) <= 365
and v1.AdmitDateTime >= '2017'

The Problem: This query will only return patients whose last 4 visits happened within 1 year.

Question: How would I find all the patients with any 4 visits that happened within 1 year of each other?

Credit to Jonathan Fite, I used his suggestion to rewrite the query like so:

with VisitEvents as(select distinct PatientID, VisitID from MriPatientVisitEvents),

VisitList2 as (
select v.PatientID, a.VisitID, a.AdmitDateTime, rank() over (partition by v.PatientID order by a.AdmitDateTime desc) as VisitNumber
from AdmittingData a
inner join VisitEvents v on a.VisitID = v.VisitID
where a.AdmitDateTime is not null),

VisitList as(
select v.*,
lag(AdmitDateTime,1) over (partition by PatientID order by AdmitDateTime) as Delta1AdmitDateTime,
lag(VisitNumber,1) over (partition by PatientID order by AdmitDateTime) as D1VisitNum,
lag(AdmitDateTime,2) over (partition by PatientID order by AdmitDateTime) as Delta2AdmitDateTime,
lag(VisitNumber,2) over (partition by PatientID order by AdmitDateTime) as D2VisitNum,
lag(AdmitDateTime,3) over (partition by PatientID order by AdmitDateTime) as Delta3AdmitDateTime,
lag(VisitNumber,3) over (partition by PatientID order by AdmitDateTime) as D3VisitNum,
lag(AdmitDateTime,4) over (partition by PatientID order by AdmitDateTime) as Delta4AdmitDateTime,
lag(VisitNumber,4) over (partition by PatientID order by AdmitDateTime) as D4VisitNum

from VisitList2 v
)

select * from VisitList
where datediff(day,Delta3AdmitDateTime, AdmitDateTime) <= 365
and AdmitDateTime >= '2017'
order by PatientID, VisitNumber

Here is a possibility for you to look at.  It uses the Lag function with the offset option set.  It doesn't show what all the visits were or when they occurred, but it does only show the most recent visit date for each patient and then when the fourth prior visit occurred within the year.

Test Data

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL
BEGIN
    DROP TABLE #TestData;
END

CREATE TABLE #TestData
    (
    TestDataID INT IDENTITY(1,1) PRIMARY KEY NOT NULL
    , PatientName VARCHAR(40) NOT NULL
    , VisitDate DATETIME NOT NULL
    );

INSERT INTO #TestData
(PatientName, VisitDate)
VALUES ('Smith', '11/30/2017')
    , ('Smith', '1/1/2018')
    , ('Smith', '12/5/2017')
    , ('Smith', '2/1/2015')
    , ('Smith', '9/5/2017')
    , ('Doe', '9/8/2017')
    , ('Smith', '9/12/2017');

Query

/** CTE_Visitor
    Limits data to only where all visits happened in 2017
    Determines if most recent (MostRecentDesc) will be 1 for most recent.
    Determines the fourth prior visit ID by using LAG with an offset.
    **/
;WITH CTE_Visitor AS
    (
    SELECT S.TestDataID
        , S.PatientName
        , S.VisitDate
        , FourVisitsAgo = LAG(S.TestDataID, 3) OVER (PARTITION BY S.PatientName ORDER BY S.VisitDate)
        , MostRecentDesc = ROW_NUMBER() OVER (PARTITION BY S.PatientName ORDER BY S.VisitDate DESC)
    FROM #TestData AS S
    WHERE YEAR(S.VisitDate) >= 2017
    )
/** Joins test data back on to the FourVistsAgo to get the date.
    - Only displays for the most recent visit and the fourth prior visit.
    - Must have had at least four visits in the past
    - Fourth prior visit must be within a year of most recent visit
    **/
SELECT S.TestDataID
    , S.PatientName
    , S.VisitDate
    , Prev.VisitDate AS DateFourVisitsAgo
    , DiffDays = DATEDIFF(DAY, Prev.VisitDate, S.VisitDate)
FROM CTE_Visitor AS S
    INNER JOIN #TestData AS Prev ON Prev.TestDataID = S.FourVisitsAgo
WHERE MostRecentDesc = 1
    AND FourVisitsAgo IS NOT NULL
    AND DATEDIFF(DAY, Prev.VisitDate, S.VisitDate) <= 365;


Call a stored procedure for every select result I have an stored procedure to delete from a long series of tables. It uses one main ID as parameter. I have to delete a bunch of rows based on a view that show that lot of tables. I want to apply the deleting procedure to every filtered result from that view. Some thing like:

SELECT id FROM view WHERE another_id=100;

And then apply the stored procedure to those results like:

CALL deleteProcedure(id);

Create another Stored Procedure.  In it, DECLARE a CURSOR for SELECT id FROM view WHERE another_id=100;.  Create a loop; in the loop FETCH one id, then CALL deleteProcedure(id);.  (See the manual and online examples for the details.)


Designing a table for large amount of rows. No relationships I am looking to design a database that'll allow me to store references to files. The table is pretty simple:

Id (GUID)(PK)
Path (nvarchar(250))
IsPublic (bit)
FileType (int)

The files can be accessed via url by putting the GUID in the url, such as files/{GUID}, so if request comes in, I can query the table by the GUID as Primary Key.

Do you think this is a valid approach? What insert/query performance am I looking at with hundreds of millions of records?

The database engine is Microsoft SQL Server.

Some queries I am predicting:

SELECT * FROM Files where ID = {GUID}

INSERT INTO Files () VALUES ()

UPDATE Files SET IsPublic = true WHERE ID = {GUID}

UPDATE Files SET Label = {NewLabel} WHERE ID = {GUID}

DELETE FROM Files WHERE ID = {GUID}

Most work will happen in select and insert. There will be very few updates and deletes. Maybe 10 inserts per second at its peak.

Each service stores the FileIds next to the tables that use it. Items service has ItemFiles which will then retrieve the Files from the service/table.

ID - I don't like GUIDs where GUIDs can be avoided.  They're 16 bytes and thrown randomly into your tables causing fragmentation and page splits.  A 4-byte INT or 8-byte BIGINT as a sequence or identity column would provide the same functionality, be smaller, and insert into the table sequentially.

Path - Does this really need to be NVARCHAR?  This will take twice the amount of storage as VARCHAR unless you use row-level compression.  Going from 500 bytes to 250 bytes doesn't seem like a lot for one row, but for a million rows you're at 500,000,000 to 250,000,000, which is 250 MB according to your friendly hard drive marketing department.

FileType - This is really a small nitpick.  Do you really need to have a 4-byte INT here?  This allows you to have billions of file types.  Chances are a 1-byte TINYINT limited to 255 choices would be more than enough.

The biggest thing from your concerns is getting the GUID out of there.  On an insert-intensive table you don't want SQL Server writing all over the place doing page splits.  I mention the size of the columns which is a nice-to-have, but its the sequential writes that are going to make you the happiest with your performance.

However, sequential numbers are a bit of a security risk if someone is allowed to view some files in the system but not others and you rely on them not being able to guess a GUID to avoid them finding other files.  If they bring up file 1000 then they can be pretty sure that files 999 and 1001 probably exist and view those as well.  I don't know if this is a concern based on what you wrote.


Does SQL Server 2016 still contain sqlmaint.exe? According to the documentation (Deprecated Database Engine Features in SQL Server 2016), sqlmaint.exe should still be available (emphasis mine):

  This topic describes the deprecated SQL Server Database Engine features that are still available in SQL Server 2016. These features are scheduled to be removed in a future release of SQL Server.

  ...

  Tools: sqlmaint Utility ...

It is definitely not listed on the list of discontinued features.

However, I fail to find sqlmaint.exe on my recently installed SQL Server 2016 Express systems. In previous versions, it was in the same folder as sqlserver.exe.

Is the documentation wrong? Or did the file location move? Or did I miss something during the install process?

(Note: Encouraged by the comments reproducing this issue, I have created a Microsoft Connect entry for this, which was migrated to UserVoice.)

Microsoft documentation mentions they're removing it, it says "Next Version" but i assume this was written for a previous version. They advise to use maintenance plans in the future.

Microsoft Documentation for sqlmaint

Edit: Last update on that article was 03/14/2017. But given all the examples point to it being originally written with SQL Server 2008 in some and edited with later versions as examples (it's all over the place to be honest), it's probably safe to say the note at the top is wrong and it's actually removed already.


Multiple m:m relationships LEFT JOIN giving duplicate records DB noob here.

I have a table named invoice with a m:m relationship with a few other tables via junction tables:

invoice_customer
invoice_creator
invoice_deposit
invoice_withdrawal
invoice_payment_method

I'm trying to execute a query that will LEFT JOIN all of the data of these m:m relationships, however, seemingly at random, I'm getting duplicate results. Sometimes it gives the right amount, then when some state is modified it will give duplicates, and I can't seem to figure out why.

For example, this query gives me a result of "1" given I have one record matching the query:

SELECT COUNT(*)
FROM invoice
JOIN account_invoice
    ON account_invoice.account_id = ?
    AND account_invoice.invoice_id = invoice.id
WHERE invoice.deleted_at IS NULL

But this query gives me 4 duplicate records instead of 1:

SELECT invoice.*, account.*, customer.*, payment_method.*, deposit.*, withdrawal.*
FROM invoice
LEFT JOIN account_invoice
    ON account_invoice.invoice_id = invoice.id
LEFT JOIN account
    ON account.id = account_invoice.account_id
LEFT JOIN invoice_customer
    ON invoice_customer.invoice_id = invoice.id
LEFT JOIN customer
    ON customer.id = invoice_customer.customer_id
LEFT JOIN invoice_payment_method
    ON invoice_payment_method.invoice_id = invoice.id
LEFT JOIN payment_method
    ON payment_method.id = invoice_payment_method.payment_method_id
LEFT JOIN invoice_deposit
    ON invoice.id = invoice_deposit.invoice_id
LEFT JOIN transaction AS deposit
    ON deposit.id = invoice_deposit.transaction_id
LEFT JOIN invoice_withdrawal
    ON invoice.id = invoice_withdrawal.invoice_id
LEFT JOIN transaction AS withdrawal
    ON withdrawal.id = invoice_withdrawal.transaction_id
WHERE account.id = ?
    AND invoice.deleted_at IS NULL
ORDER BY invoice.id DESC
LIMIT ?,?

If you want only one row of output for each Invoice, you need to figure out what to do with multiple customers tied to that one invoice.  (Etc.)  Here's one approach:

SELECT i.*,
       GROUP_CONCAT(
           ( SELECT c.name FROM invoice_customers AS ic
                           JOIN customers AS c  USING(customer_id)
                           WHERE ic.invoice_id = i.invoice_id
           ) SEPARATOR ", "
                   ) AS customers
    FROM invoices AS i
    ...


Error while creating procedure This is my procedure:

     DELIMITER $$
     CREATE PROCEDURE sp_test_final6()
     BEGIN
    INSERT INTO `chef_ratings`(`chef_id`,`avg_total_no_votes`,`avg_total_rating`,`no_of_votes`,`avg_rating`)
    SELECT `chef_id`,(SELECT count(chef_id) FROM rates)/( SELECT count(DISTINCT chef_id) FROM rates),
    (( SELECT sum((`question_1` + `question_2` +`question_3` )/3) FROM rates ) / ( SELECT count(chef_id) FROM rates)),
    ( SELECT count(chef_id) FROM rates ),avg((`question_1`+`question_2`+`question_3`)/3)
    FROM rates GROUP BY `chef_id`;
    END $$
    DELIMITER ;

I'm getting error :

 #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'PROCEDURE sp_test_final6()
BEGIN
INSERT INTO `chef_ratings`( `chef_id`, `avg_t' at line 1

Any idea why it is failing?

Edited: It works fine in mysql version 5.5 but in Server version: 4.1.22 it is giving this problem.  How we can fix without upgrading?

I got the answer. Stored procedure supported in MYSQL since 5.0 .Older version doesn't support stored procedure .

reference link


How to force order of WHERE clauses in PostgreSQL? I have 6 columns in Postgres table:

A1 character varying(5)[]
A2 character varying(5)[]
A3 int REFERENCES ... -- FK
B1 character varying(5)[]
B2 character varying(5)[]
B3 int REFERENCES ... -- FK

I need a SELECT where the first row matched is the winner (limit 1)
with matching to A group and B group.

I know Postgres doesn't care about the order of WHERE clauses and I must prepare an ORDER clause or find a different approach.

I want to prepare lookup with priority of matching, my importance of WHERE clauses is as follow:

highest priority: (A1 and B1) OR
.                 (A1 and B2 OR A2 and B1) OR
.                 (A1 and B3 OR A3 and B1) OR
.                 (A2 and B3 OR A3 and B2) OR
lowest priority:  (A3 and B3)

The query is matching with 6 values, like:
a1 to A1, a2 to A2, a3 to A3, b1 to B1, b2 to B2, b3 to B3

So A1 and B1 means a1 matched with A1 and b1 matched with B1,

in SQL this part is written as:

  ("A1" @> ARRAY['19956']::varchar(5)[] AND "B1" @>
  ARRAY['27407']::varchar(5)[])

with a1='19956', b1='27407'.

Is it possible to prepare it as single query, despite declarative aspect of SQL? I am considering 5 joins on the same table, but maybe there is an easier way.

This information is crucial:

  the first row matched is the winner (limit 1)

You do not actually need any sort order for a single result. ORDER BY is just one idea how to solve the task. I suggest a completely different approach, probably (much) faster:

SELECT * FROM tbl WHERE A1 AND B1
UNION ALL
SELECT * FROM tbl WHERE A1 AND B2 OR A2 and B1
UNION ALL
SELECT * FROM tbl WHERE A1 AND B3 OR A3 and B1
UNION ALL
SELECT * FROM tbl WHERE A2 AND B3 OR A3 and B2
UNION ALL
SELECT * FROM tbl WHERE A3 AND B3
LIMIT 1;

This is assuming each SELECT can only return a single row. Else you get an arbitrary pick or you need to add ORDER BY to individual SELECTs enclosed in parentheses for a deterministic pick. (See linked answers below.)

The beauty of it: Postgres stops evaluating as soon as the first row is found. Test with EXPLAIN ANALYZE to see "never executed" for trailing SELECTs. So you can use indexes, which might mean orders of magnitude in performance.

And you can conveniently return a default row if nothing matches. Just append one more SELECT before LIMIT 1.

BTW, this is a single query.

Related:

Optimize a query on two big tables
Select first record if none match


Difference of sets with different amount of columns? Problem

When there exists two sets, R and S, and we take the difference R - S, we remove the elements from R that exist in S.

For cases where the number of columns are the same on both sides, such as the example below, this works fine:

Question

However, what happens when R and S have a different amount of columns?

If I recall, the difference operation requires the two relations involved to be compatible, implying that they should have the same number of columns.

Quoting from Wikipedia about relational algebra

  For set union and set difference, the two relations involved must be
  union-compatible—that is, the two relations must have the same set of
  attributes.


MySQL replication on slave server Broken/Stopped without error (Hi; sorry for my English)
My question is: how can I restart MySQL replication on a slave server.
At first sight everything seems to be normal but when I check with

SHOW SLAVE STATUS\G

besides Relay_Log_Space, Read_Master_Log_Pos and Seconds_Behind_Master which
continue to increase. Other data remain fixed for several days.
I tried STOP/START SLAVE; but nothing has changed.

root@localhost [mysql]> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: x.x.x.x
                  Master_User: slave_replicator
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000103
          Read_Master_Log_Pos: 197827259
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 149672940
        Relay_Master_Log_File: mysql-bin.000029
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 305160334
              Relay_Log_Space: 65277287216
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 1724670
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 3464562
                  Master_UUID: 727b7e74-325f-11e6-9b54-faa1cd739fa9
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Reading event from the relay log
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0

Slave_SQL_Running_State: Reading event...

Suggests that it's working on a large event. If you have MIXED or Row based binary logging and have changed a large amount of data on the master you might end up with a large event or series of large events. Use the mysqlbinlog tool to see what's going on with your relaylog. The master might show a concentration of binary logs over a small period of time (on the filesystem)


SQL access query to count and delete rows > n Can someone help me with a SQL (Access) query to delete all rows greater than n entries ordered by ID?

I.e. say I have 1200 rows of data with ID incremented . I need delete the first amount leaving only  200.

If I have only 199 rows of data, then I need to preserve them all.

I don't have access to a working copy of MS Access to test this but what you might be looking for is this:

delete * from table_name where ID not in(
select top 200 ID from table_name order by id desc
);


Adding articles to sql server I am trying to add a few new articles to an existing sql server replication publication.  The publication is for one way transactional replication.  I have done this multiple times on multiple servers, and in fact have done it several times on the server I am working on now. This time I am running into problems.

I am using the ssms gui to add the articles, by going to the publication properties, selecting the new articles to include, and clicking ok.  Once that is done, I rerun the snapshot agent, which shows the new articles and it finishes successfully.

The problem is that the new tables are never created on the subscriber.  The tables are missing, as well as the associated stored procedures (sp_MSins*/sp_MSdel*/etc).  I don't see any indication of errors that are occuring during the process though.

If I leave the new tables in the publication, eventually all replication grinds to a halt, as it tries to push data changes to the affected table, and the replication agent jobs are unable to find the required tables/stored procedures.  I have removed the affected articles from the publication and now transactions are being replicated again as expected.

At this point I really don't know whats going on, I don't see errors in the logs on either side, and I am not sure what my next step is, besides opening a case with microsoft, any advice for a solution, or even just someplace else to look for errors in the process would be appreciated.

I reached out to another DBA, and they said they had seen similar issues in the past, without a clear cause, but that adding the articles 1 at a time had resolved the issue, that seemed to work in this case as well.  Adding 1 table at a time vs adding 10 tables at once allowed the tables to be created, and they are now replicating successfully.


SSIS Excel source not mapping all columns I have a data that I need to import on a xlsx file.
The flow is simple, a person complete the information on this file, then put the file on a shared folder. Finally the ETL move the file to other location and load the data and put it on a SQL Table.

But for some strange reason now, the file load correct, but then fails. The error says

  SSIS.Pipeline] Error: Error in the validation of "component" Excel Origin "(150)". The validation status "VS_NEEDSNEWMETADATA" was returned.

I realized that the dataource is not loading some columns that exists in the file.

I've tried creating a new datasource and new connection, but the problem persits. I think the problem is on the file, but how can I change the metadata to map the columns tha are missing.

Try removing then adding the Excel Source component in the DataFlow Task, Also try to set the DataFlow Task Delay Validation property to True

If the Error persists, i think that the issue is not with the columns name, it can be generated by the column data type, try adding ;IMEX=1 to the excel connection string so the data type are taken from the first row


How many rows can a SQL Server 2012 table hold, and when should I partition it? Say a table stores granular data about some event. It has the date of the event, a type dimension with around 30K types, and a category dimension with around 100 categories, plus some numeric facts.

On average, there are 15 million transactions per day. More than 5 billion per year, over 60G per decade. That's not big data, but it's a lot.

How many rows can a SQL Server 2012 table hold?

Of course, older data is used less often and is candidate for being partitioned in multiple tables on the same DB. But when should this partitioning start happening? 1 table per year? 5 years?

Additional information collected from comments:

Consider: I have enough storage to hold 30 billion records of that event. If each event record requires 1KB, I have 30TB in that table, and enough storage for that (and for log of it). Its PK is bigint.

What do you think of having one table with historical data and another table with latest data? Instead of a transactional event, the table has a catalog, clients, for example. Every day the OLTP's catalog is copied into DW. So I'd have a table holding the history data and another table with the latest records.

In the design I use, ETL feeds the history table, then I use row_number() to grab the latest record of each entity by its NK. It's very expensive to run, but this way I keep entities that existed in the past and aren't on the OLTP anymore.

How many records can a MSSQL2012 table hold?

As stated on the MSDN page for Maximum Capacity Specifications for SQL Server (for SQL Server 2012):

"Rows per table = Limited by available storage" (same for both 32-bit and 64-bit platforms)

  But when should this partitioning start happening? 1 table per year? 5 years?

This all depends on the needs of the system. There is no inherent need to partition, ever really, solely based on the issue of performance. Partitioning is mainly intended as a means of more easily managing getting mass amounts of data into, or out of, a table, as quickly as possible and causing as little contention as possible. If the desire was purely to assist query performance, maybe start TESTING it around 1 billion rows, but even then, if you have a good data model and good indexing, you probably won't need to even bother with this. Also, filtered indexes and even filtered statistics probably would do well enough for many cases where people choose to implement table partitioning (if their intention is purely performance-related).

But if your need is to quickly remove a large block of rows, perhaps for aging out older data, then table partitioning will help as you can SWITCH the "old" data out. And on this level it is not a matter of rows but a matter of what amount of time you want to manage. If you want to switch out data monthly, then do monthly partitions. If you want to age-off data yearly, then try yearly partitions.

UPDATE

Not sure why I didn't mention this earlier, but you should take a look at Partitioned Views. That is when you have multiple tables of identical schema and a View that does a UNION ALL between them, and each table has a CHECK CONSTRAINT enforcing a particular range of data within that table (and so the Query Optimizer knows where to get the data from). By doing this, you can have two tables -- current and historical -- and then have queries that hit either one or the other (if the time-frame is known ahead of time, such as a query that only hits the most recent 90 days), or uses the View if the data could be in either one. Please see the following for more info:

Partitioned Tables v. Partitioned Views–Why are they even still around?
MSDN page for CREATE VIEW (go to the Partitioned Views subsection of the Remarks section)
Using Partitioned Views

I believe you can even do a combination where the "current" table is partitioned (so that you can quickly switch in the incoming data and switch out the data that is becoming "old"), a non-partitioned table for historical, and a Partitioned View to join the two of them. Then you just need a way to get the data from the newly switched-out partition into the "historical" table.

Also, with regards to performance, there are other features offered, depending on what edition you are using (some only come with Enterprise Edition). But you should look into ColumnStore Indexes, Data Compression, and maybe a few other things.


How to flatten and pivot a normalised hierarchy table I have an OrgChart table that looks something like this:

PositionCode   PositionName   ParentCode  Level
AA0001         CEO                        0
AA0002         CFO            AA0001      1
AA0003         CIO            AA0002      2
AA0004         SnrMgr         AA0002      2
AA0005         JnrMgr         AA0004      3
AA0006         ItMgr          AA0003      3

etc
So, the ItMgr reports to the CIO, who reports to the CFO, who reports to the CEO.

I need to convert this to a flattened structure like this (for a BI solution): (CurCode is the position we're focusing on for that row, followed by the complete org hierarchy above that position).

CurCode  Lvl0Code Lvl0Name Lvl1Code Lvl1Name Lvl2Code Lvl2Name Lvl3Code Lvl3Name
AA0001   AA0001   CEO      na       na       na       na       na       na
AA0002   AA0001   CEO      AA0002   CFO      na       na       na       na
AA0003   AA0001   CEO      AA0002   CFO      AA0003   CIO      na       na
AA0004   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   na       na
AA0005   AA0001   CEO      AA0002   CFO      AA0004   SnrMgr   AA0005   JnrMgr
AA0006   AA0001   CEO      AA0002   CFO      AA0003   CIO      AA0006   ItMgr

How can I achieve that in T-SQL?
The table goes 5 levels deep, so I don't need an undefined number of columns.
I was looking at a PIVOT, but couldn't see how to make it work properly.
Any help will be very appreciated.

As far as you're using a hierarchical structure you can apply a recursive solution. But for a large number of rows you must deal with MAXRECURSION, have a look at MS Docs about it.

Quoted from docs:

  An incorrectly composed recursive CTE may cause an infinite loop. For
  example, if the recursive member query definition returns the same
  values for both the parent and child columns, an infinite loop is
  created. To prevent an infinite loop, you can limit the number of
  recursion levels allowed for a particular statement by using the
  MAXRECURSION hint and a value between 0 and 32,767 in the OPTION
  clause of the INSERT, UPDATE, DELETE, or SELECT statement. This lets
  you control the execution of the statement until you resolve the code
  problem that is creating the loop. The server-wide default is 100.
  When 0 is specified, no limit is applied. Only one MAXRECURSION value
  can be specified per statement.

WITH tree AS
(
    SELECT
        PositionCode as CurrCode,
        PositionCode,
        PositionCode as Lvl0Code,
        PositionName as Lvl0Name,
        cast('na' as nvarchar(10)) as Lvl1Code,
        cast('na' as nvarchar(10)) as Lvl1Name,
        cast('na' as nvarchar(10)) as Lvl2Code,
        cast('na' as nvarchar(10)) as Lvl2Name,
        cast('na' as nvarchar(10)) as Lvl3Code,
        cast('na' as nvarchar(10)) as Lvl3Name,
        ParentCode,
        Level
    FROM
        tbl
    WHERE
        Level = 0
    UNION ALL
    SELECT
        tree.CurrCode,
        tbl.PositionCode,
        tree.Lvl0Code,
        tree.Lvl0Name,
        case when tbl.Level = 1 then tbl.PositionCode else tree.Lvl1Code end as Lvl1Code,
        case when tbl.Level = 1 then tbl.PositionName else tree.Lvl1Name end as Lvl1Name,
        case when tbl.Level = 2 then tbl.PositionCode else tree.Lvl2Code end as Lvl2Code,
        case when tbl.Level = 2 then tbl.PositionName else tree.Lvl2Name end as Lvl2Name,
        case when tbl.Level = 3 then tbl.PositionCode else tree.Lvl3Code end as Lvl3Code,
        case when tbl.Level = 3 then tbl.PositionName else tree.Lvl3Name end as Lvl3Name,
        tbl.ParentCode,
        tbl.Level
    FROM
        tbl
    JOIN
        tree
        ON tree.PositionCode = tbl.ParentCode
)
SELECT
    CurrCode,
    Lvl0Code,
    Lvl0Name,
    Lvl1Code,
    Lvl1Name,
    Lvl2Code,
    Lvl2Name,
    Lvl3Code,
    Lvl3Name
FROM
       tree
OPTION (MAXRECURSION 0);

CurrCode | Lvl0Code | Lvl0Name | Lvl1Code | Lvl1Name | Lvl2Code | Lvl2Name | Lvl3Code | Lvl3Name
:------- | :------- | :------- | :------- | :------- | :------- | :------- | :------- | :-------
AA0001   | AA0001   | CEO      | na       | na       | na       | na       | na       | na
AA0001   | AA0001   | CEO      | AA0002   | CFO      | na       | na       | na       | na
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0003   | CIO      | na       | na
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0004   | SnrMgr   | na       | na
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0004   | SnrMgr   | AA0005   | JnrMgr
AA0001   | AA0001   | CEO      | AA0002   | CFO      | AA0003   | CIO      | AA0006   | ItMgr

db<>fiddle here

If you don't like or don't want or simply you cannot use a recursive solution, you can use a series of nested CTE's (one for each level), and finally combine the results using a UNION operation:

WITH l0 AS
(
    SELECT PositionCode as CurrCode, PositionCode as Lvl0Code, PositionName as Lvl0Name,
           'na' as Lvl1Code,
           'na' as Lvl1Name,
           'na' as Lvl2Code,
           'na' as Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           level
    FROM   tbl
    WHERE  level = 0
),
l1 AS
(
    SELECT l0.CurrCode, l0.Lvl0Code, l0.Lvl0Name,
           t1.PositionCode Lvl1Code,
           t1.PositionName Lvl1Name,
           'na' as Lvl2Code,
           'na' as Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           t1.level
    FROM   tbl t1
    JOIN   l0
           ON t1.ParentCode = l0.Lvl0Code
    WHERE  t1.level = 1
),
l2 AS
(
    SELECT l1.CurrCode, l1.Lvl0Code, l1.Lvl0Name,
           l1.Lvl1Code,
           l1.Lvl1Name,
           t2.PositionCode Lvl2Code,
           t2.PositionName Lvl2Name,
           'na' as Lvl3Code,
           'na' as Lvl3Name,
           t2.level
    FROM   tbl t2
    JOIN   l1
           ON t2.ParentCode = l1.Lvl1Code
    WHERE  t2.level = 2
),
l3 AS
(
    SELECT l2.CurrCode, l2.Lvl0Code, l2.Lvl0Name,
           l2.Lvl1Code,
           l2.Lvl1Name,
           l2.Lvl2Code,
           l2.Lvl2Name,
           t3.PositionCode Lvl3Code,
           t3.PositionName Lvl3Name,
           t3.level
    FROM   tbl t3
    JOIN   l2
           ON t3.ParentCode = l2.Lvl2Code
    WHERE  t3.level = 3
)
SELECT * FROM l0
UNION
SELECT * FROM l1
UNION
SELECT * FROM l2
UNION
SELECT * FROM l3
ORDER BY CurrCode, level;

db<>fiddle here


How to specify “partition (partx)” in “mysqldump” command? I have to export some data from a table which is partitioned, and with some extra condition
So I use mysqldump command,and use --where"xxxxxx" options as below

mysqldump -hxxx -Pxxx -uxxx xxx rtm --tables xxx --where="xxx"> xxx

But how can I specify partition (partx) keyword in mysqldump command，since I just need the data from the specific partition.

Plan A:  Provide a WHERE clause that exactly covers that partition.

Plan B:  Let's discuss (1) why you are using partitioning, and (2) why you need that kind of dump.

Plan C:  See "transportable tablespaces" as a way to break off a partition, turning it into an independent table.


Mysql 5.7 - select where json I have some JSON data stored in a filters column. An example from one row:

name     | filters
---------+----------------------------------------------------------
John Doe | [{"Year":2004, "SportID":3}, {"Year":2005, "SportID":27}]

Other rows may have more than 2 objects, this is just an example.

I am trying to select data using a where clause which would iterate the JSON objects and act upon the value. For example, find me records where Year > 2004 or SportID = 24.

It seems like this should work:

SELECT name, filters from my_table
where json_extract(`filters`, "$[*].Year") = 2004;

However I get an empty result set. ♂

Of course I could just return the entire JSON object and parse it in app code, but I'd rather not do that if I don't have to.

UPDATE:
As requested, my show create table:

CREATE TABLE `my_table` (
  `userId` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `filters` json DEFAULT NULL,
  PRIMARY KEY (`userId`),
  UNIQUE KEY `my_table_userId_uindex` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


Dropping multiple foreign keys of unknown name I have a DB schema generated by Hibernate. Hibernate generates FKs by assigning them a generated name like FK0123456789ABCDEF.

As part of the upgrade process of an application installed at a customer's site, I need to drop some FKs created by Hibernate long ago. I cannot be sure that their names remain the same from my local installation and the customer's site. Worse, their DBA won't allow me to trickle with their UAT and PROD Dbs with a Management Studio client: they want one script to run both on UAT and PROD without any error (violators will be rolled back).

I know how to indentify these FKs. FKs from tables labeled TA_xxx to tables labeled XTB_xx.

So I ran the following in local

SELECT
    name AS FkName,
    OBJECT_NAME(parent_object_id) AS ReferencingTable,
    OBJECT_NAME(referenced_object_id) AS ReferencedTable
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) LIKE 'TA_%'
AND OBJECT_NAME(referenced_object_id) LIKE 'XTB_%'

Result:

FK2A3B4D9E4EF09A0A  TA_BONDS    XTB_IMPORTS
FK2AC809114EF09A0A  TA_LOANS    XTB_IMPORTS
FKFCE5B2C695243BE8  TA_DEAL_SUBMISSIONS XTB_EXPORTS
FKFCE5B2C64757176B  TA_DEAL_SUBMISSIONS XTB_EXPORTS
FK2A52C11573F2E7FF  TA_DEALS    XTB_ORGANIZATIONS
FK2A52C11595243BE8  TA_DEALS    XTB_EXPORTS
FK2A52C1154757176B  TA_DEALS    XTB_EXPORTS

They are 7 both on my installation and on customer's site. I wanted to adapt a script like the following

DECLARE @fkConstraint SYSNAME
DECLARE @tbName SYSNAME

SELECT
    @fkConstraint= name,
    @tbName = OBJECT_NAME(parent_object_id)
FROM sys.foreign_keys
WHERE OBJECT_NAME(parent_object_id) LIKE 'TA_%' AND OBJECT_NAME(referenced_object_id) LIKE 'XTB_%'

DECLARE @DropStmt NVARCHAR(500)

SET @DropStmt = 'ALTER TABLE [dbo].'+@tbName+' DROP CONSTRAINT ' + @fkConstraint

EXEC (@DropStmt)
GO

I think I could repeat the above block 7 times to get rid of all FKs, but I'd ask if there is a more automated way, like "DROP CONSTRAINT ... WHERE IN (SELECT ...)"

Any ideas?

One way is dump the result of below query into a Temp Table and execute resulted rows one by one.

CREATE TABLE #foregnkeys
    (
      ROWID INT IDENTITY(1, 1),
      SQLStmt VARCHAR(3000)
    )

DECLARE @count INT= 0,
    @SQLStmt VARCHAR(3000)

INSERT  INTO #foregnkeys
        SELECT  'alter table [' + sc.name + '].['
                + OBJECT_NAME(fk.parent_object_id, DB_ID('DBNAME'))
                + '] drop constraint [' + fk.name + ']'
        FROM    DBNAME.sys.foreign_keys fk
                INNER JOIN DBNAME.sys.tables tbl ON tbl.[object_id] = fk.parent_object_id
                INNER JOIN DBNAME.sys.schemas sc ON sc.[schema_id] = tbl.[schema_id]
        WHERE   tbl.name LIKE 'TA_%'
                OR tbl.name LIKE 'XTB_%'    --- AND of your query is converted to OR

SELECT  @count = ( SELECT MAX(rowid) FROM #foregnkeys )

WHILE ( @count > 0 )
    BEGIN

        SELECT  @SQLStmt = ( SELECT SQLStmt
                             FROM   #foregnkeys
                             WHERE  ROWID = @count
                           )

        PRINT ( Cast(@count as varchar)+'_ '+ @SQLStmt )
        EXEC ( @SQLStmt)

        SELECT  @count = @count - 1

    END

DROP TABLE #foregnkeys


Maintenance plan in AlwaysOn environment I need to create below three jobs in AOAGs SQL Server.

CheckDB integrity,
Update stats,
Re-organize index

My question is ,Do I need to create in only Primary Replica or all the secondaries also ?

My question is ,Do I need to create in only Primary Replica or all the secondaries also ?

You MUST run dbcc checkdb on all the replicas at least weekly. If you can afford it daily or once in two days that would be even better. Yes you can run checkdb on all replicas because checkdb internally creates snapshot and runs integrity check on that snapshot without affecting database. For more details read Checkdb from every angle
You cannot run index maintenance on read only replicas. You have to do it on primary replica. And since the logs generated from primary replica is replayed on secondary the index maintenance changes would eventually be done on secondary replicas.Please read Recommendations for Index Maintenance with AlwaysOn Availability Groups.

For statistic,s if you are running query on secondary replica which is different from primary replica and the query on secondary requires column statistics which is not there on primary you have option to create temporary statistics. Please read

AlwaysOn: Challenges with statistics on ReadOnly database, Database Snapshot and Secondary Replica
AlwaysOn: Making latest statistics available on Readable Secondary, Read-Only database and Database Snapshot

I am sure you are aware about Ola Hallengren script for index maintenance, I suggest you download it and use it. It takes care of various things on AOAG database.


Database mirroring stuck in suspended mode We have an issue with database mirroring on one of the databases on our sql instance where mirroring is suspended for almost 15 hours now.

DB is 200 GB on SQL server 2008R2 Sp3+cu with latest patched done for meltdown.

so far we have done below

Rebuild the endpoints--> no luck
Resume the mirroring with partner resume --> no luck
DBCC CHECKDB shows 0 errors found on either side.

Nothing found from error logs on Principal. However on secondary when we resume error logs seems to have stack dump logged every time.

Any help on how to resume the suspended mirror apart from rebuilding mirroring from scratch?

First thing to try:
ALTER DATABASE Gandalf SET PARTNER RESUME;

Might work!  If you prefer you can do it with the GUI - Tasks / Mirror / Resume
Check logs for an Error 3624.
I get this occasionally - reading up on this, it looks very much like "an error has occurred."
My solution is to stop mirroring and recreate.
I would be interested if anyone has better information.


Deleting rows from table where values less than an aggregate value I'm attempting to remove duplicates from a table.

CREATE TABLE media_views (
  `companyid` INT (11) NOT NULL,
  `channel` INT(11) NOT NULL,
  `date` DATE NOT NULL,
  `views` INT(11) NOT NULL DEFAULT 0
  PRIMARY KEY (`companyid`,`channel`,`date`,`views`)
) ENGINE=InnoDB;

INSERT INTO media_views (companyid, channel, `date`, views)
     VALUES (1, 0,  '2015-10-01', 15),
            (1, 0,  '2015-10-01', 25),
            (1, 0,  '2015-10-01', 541),
            (1, 5,  '2015-10-01', 2),
            (1, 8,  '2015-10-01', 53),
            (1, 91, '2015-10-01', 8571),
            (3, 12, '2015-10-01', 11),
            (1, 0,  '2015-10-02', 1),
            (1, 0,  '2015-10-02', 13),
            (1, 5,  '2015-10-02', 17),
            (1, 91, '2015-10-03', 37),
            (3, 0,  '2015-10-03', 73);

Now the problem is that I have "duplicate" errors, I have records that match the dates but views are different. Only the maximum values of the views are actually useful. So I can solve this problem by doing the following.

SELECT companyid, channel, date, MAX(views)
FROM media_views
GROUP BY companyid, channel, date
HAVING COUNT(1) >= 1;

This clearly provides me with the following:

+-----------+---------+------------+------------+
| companyid | channel | date       | MAX(views) |
+-----------+---------+------------+------------+
| 1         | 0       | 2015-10-01 | 541        |
| 1         | 0       | 2015-10-02 | 13         |
| 1         | 5       | 2015-10-01 | 2          |
| 1         | 5       | 2015-10-02 | 17         |
| 1         | 8       | 2015-10-01 | 53         |
| 1         | 91      | 2015-10-01 | 8571       |
| 1         | 91      | 2015-10-03 | 37         |
| 3         | 0       | 2015-10-03 | 73         |
| 3         | 12      | 2015-10-01 | 11         |
+-----------+---------+------------+------------+
9 rows in set (0.11 sec)

Which is great but how do I go back and delete the rows that are duplicates and less than the max(views) value?

You can select the rows to keep using a left join - http://sqlfiddle.com/#!9/3a677/3

select *
from media_views m1
left join media_views m2
  on (m1.companyid = m2.companyid
      and m1.channel = m2.channel
      and m1.date = m2.date
      and m1.views < m2.views)
where m2.companyid is null;

And you can delete the other ones using similar a inner join - http://sqlfiddle.com/#!9/d5d36/2

delete m1 from media_views m1
join media_views m2
  on (m1.companyid = m2.companyid
      and m1.channel = m2.channel
      and m1.date = m2.date
      and m1.views < m2.views);

And do not forget to modify the primary key after deleting them so it is not possible anymore.


Installing oracle RAC 10.2.0.1 install and patch to 10.2.0.5 directly or install CRS ASM and RDBMS then patch? I'm installing my first RAC and I was wondering if I should install CRS 10.2.0.1 (we are still at 10g for compatibility reasons) then patch to 10.2.0.5 before I install ASM (then patch) and finally rdbms (then patch)

or if I should install CRS / ASM / RDBMS 10.2.0.1 and then patch to 10.2.0.5 in the same order.

I would go "CRS / ASM / RDBMS 10.2.0.1 and then patch to 10.2.0.5 in the same order" just to be sure I follow the most typical way that others went before 10.2.0.5 became available. But it's just me, your first proposition will work as well.

Both alternatives guarantee that at all times:

CRS version >= DB version
CRS version >= ASM version


Version control tool for postgreSQL Can anyone suggest a windows based version control tool for PostgreSQL.

Thanks and Regards,
GP

There are two tools which regularly get "honourable mentions" in this category. They are:

http://www.liquibase.org/

and

https://flywaydb.org

They are specifically for database version control - Open Source, Apache licence.
I believe that it's good to go to the source (pardon the pun...) in these matters. A Google of "database version control" leads one to this post by Jeff Atwood (one of the founders of StackExchange). Be sure to follow the links to K. Scott Allen's series of articles. This is an excellent thread which pretty much sums matters up (i.e. version control your database (or its metadata!)).
You could also look at this by Martin Fowler which is a thought provoking article on database version control and software development generally.


Get @@SERVERNAME from linked server This seems like a basic question but I can't find any answers out there - I need to be able to get the server name/instance etc. from a linked server. I've tried a couple of things:

select <linked server>.@@SERVERNAME;
select <linked server>.SERVERPROPERTY('ServerName');

... but no joy. Any ideas?

This is SQL 2008 R2 & 2014 (2008R2 is the linked server)

EDIT: Errors are:

  Msg 102, Level 15, State 1, Line 2 Incorrect syntax near
  '@@SERVERNAME'.

Some slightly shorter (and more natural, IMHO) approaches:

EXEC LinkedServer.[master].sys.sp_executesql N'SELECT @@VERSION;';

Or:

EXEC('SELECT @@VERSION;') AT LinkedServer;

I prefer the sp_executesql route because:

It will still allow for strongly-typed parameters, instead of dealing with all kinds of string concatenation, escaping of single quotes, etc.
It is easy to specify a particular database in that path, instead of having to put database prefixes on all the objects referenced in the query. And yes, you can define the database dynamically:

DECLARE @db SYSNAME, @exec NVARCHAR(1024);
SET @db = N'tempdb';
SET @exec = N'LinkedServer.' + QUOTENAME(@db) + N'.sys.sp_executesql';
EXEC @exec N'SELECT @@VERSION, DB_NAME();';

And the linked server name, too, if need be:

DECLARE @srv SYSNAME, @db SYSNAME, @exec NVARCHAR(1024);
SET @srv = N'LinkedServer';
SET @db = N'tempdb';
SET @exec = QUOTENAME(@srv) + N'.' + QUOTENAME(@db) + N'.sys.sp_executesql';
EXEC @exec N'SELECT @@VERSION, DB_NAME();';