Estimate number of rows in query results

The approach would be to separate the predicate (the WHERE clause) into its parts and work out the fractions.

The fraction of all rows returned for a given column value is called the selectivity of that column. Since each value of party will return, on average, 700 rows the selectivity of party is 700 / 21000. Similarly for type the selectivity is (number of rows per value)/(total number of rows) = 420 / 21000. If it helps you can think of selectivity as being the probability that a row taken randomly from the table will have the given value.

To combine the two parts of the predicate we multiply the selectivities. We multiply because of the AND clause. Again you can think of probability - if you roll a dice what's the chance you get two sixes in a row? It's (chance of a 6) AND (chance of a six) = 1/6 * 1/6 = 1/36. This is the "chance" per row so to get the number of rows returned we multiply by the table cardinality:

   700     420
  ----- * ----- * 2100
  21000   21000

Which works out at 14.

A similar, but different, calculation can be performed for an OR clause. Then we must account for double-counting of rows that match both conditions. The answer is 700 + 420 - 14 = 1106.

Now, there are several assumptions built into this calculation. First is that the data is evenly distributed over values i.e. that there are 420 values for "shoe" and 420 for "sock" and 420 for "shirt" etc. This is rarely so. More likely is that some values occur infrequently and others often e.g. 800 rows for "shoe" and 2 rows for "golf club". This problem is know as skew. A second assumption is that the two predicates are independent. This assumes if we change "sky" to "earth" there will still be 420 rows for "shoe". Again, this is unlikely to be wholly true. Perhaps sky people only ever donate wings and earth people only ever donate shoes? This problem is know as correlation.  If your data has skew or correlation the maths above is no longer correct.

Commercial DBMS collect statistics about the distribution of values within a column. This helps them with skew. Correlation is difficult and not well handled even in commercial products.

Here's a link which discusses selectivity and cardinality and here's one which discusses multi-column predicates. They're specifically for SQL Server but the maths applies elsewhere.

  I can tell that the upper limit is 200 as that is the number of total candidates.

This is not so. From what you've shown in the question (and I'm sure that's not your full database schema) there is nothing to prevent all rows in the table from having the same value of party, type and candidate. So for a 21,000 row table the maximum number of rows that can be returned is 21,000 or N(donate) in your nomenclature. If there are 200 separate values in the candidate column and you require that each value is returned at most once you need to specify that requirement in the query. In SQL this is achieved with the DISTINCT clause:

SELECT DISTINCT candidate
FROM donate
WHERE party="sky" AND type "shoe"


I'm not sure that there is a problem.

A session in V$SESSION has a status of "INACTIVE" if it is not executing a SQL statement at that particular instant in time.  If a user opens a dedicated session in a client-server application and is using the application intensively, it would not be at all unusual for the session to be "INACTIVE" 99% of the time because the vast majority of the time is spent waiting for the human to read and process the data on the screen.  The fact that a session is "INACTIVE" doesn't imply that it wasn't "ACTIVE" a few seconds ago or that it won't be "ACTIVE" again a few seconds from now.  The LAST_CALL_ET column in V$SESSION shows you the number of seconds since a session was last in an "ACTIVE" status if the session is currently in an "INACTIVE" status.  That may give you some idea whether you are dealing with sessions that have been active for a while or not.

If you have a three-tier application, your middle tier servers will generally maintain a pool of database connections.  The middle tier opens a number of connections and keeps them open more or less indefinitely.  When users need to interact with the database, they get a connection from the pool, run a query, and return the connection to the pool.  At peak workloads, connection pooling can concentrate a large number of application users into a relatively smaller number of database connections which will mean that the sessions spend a larger fraction of their time in an "ACTIVE" status.  But during periods of low usage, the middle tier probably maintains a bunch of "INACTIVE" sessions.

In either of these cases, having a number of "INACTIVE" sessions is not a bad thing.  And "cleaning up" those inactive sessions will cause errors in the applications that have opened them.

If you have a large number of inactive sessions because the client processes have failed (i.e. the client application crashed and the database is unaware of that fact), you could use the sqlnet.expire_time parameter on the server's sqlnet.ora file to periodically send a probe packet to the client machine.  That may negatively impact performance but it will allow the database to more quickly discover that the client process has failed.

If you have a large number of inactive sessions because your middle tier servers have created too many connections to the database in their connection pools, you would need to talk with the middle tier admins to ask them to reduce the number of connections they open in their connection pools.  If they have recently added more servers to the middle tier farm, they may need to reduce the number of connections each server maintains in its connection pool.

If your problem is that too much RAM is being allocated to all the dedicated server connections that need to be open (particularly common if you've got a lot of client server applications), you could configure Oracle to support shared server connections since that reduces the amount of memory that each connection requires.


Second Instance Installation of PostgreSQL?

You don't need the binaries two times, you only need to create a second data directory (aka "cluster").

The following creates a new data directory under c:\ProgramData\pgdata2. I highly recommend to not put the data directory under c:\Program Files (that's one of the reasons I stopped using the EntpriseDB installer because it always suggested that)

"C:\Program Files\PostgreSQL\12\bin\initdb" -D "c:\ProgramData\pgdata2"

Then edit c:\ProgramData\pgdata2\postgresql.conf and change the value for port, e.g.

port = 5335

Then you can register the new Windows service and provide the data directory during registration:

pg_ctl register -N other-postgres -D "c:\ProgramData\pgdata2"

Once the service is registered, you can start it using net start

net start other-postgres


Poor temporal table performance on older values

In a comment from Zane on your question, he stated:

  ...It seems like part of your problem is you're reading 50 million rows in order to return 20K in the plan.

This is, indeed, the problem.  There's no index available to push some, or all, of the predicates down to the storage engine.  Microsoft recommends this baseline indexing strategy for temporal tables in the Docs article Temporal Table Considerations and Limitations:

  An optimal indexing strategy will include a clustered columns store index and / or a B-tree rowstore index on the current table and a clustered columnstore index on the history table for optimal storage size and performance. If you create / use your own history table, we strongly recommend that you create this type of index consisting of period columns starting with the end of period column to speed up temporal querying as well as speeding up the queries that are part of the data consistency check. The default history table has a clustered rowstore index created for you based on the period columns (end, start). At a minimum, a non-clustered rowstore index is recommended

The phrasing of that is a little confusing (to me, anyway).  But the takeaway is that you could create these indexes to improve performance some, if not quite a lot:

NC index on the current table, leading with SysEndTime:

CREATE NONCLUSTERED INDEX IX_SysEndTime_SysStartTime
ON dbo.Benefits (SysEndTime, SysStartTime)
/*INCLUDE (ideally, include your other important fields here)*/;

This will allow you to avoid reading some of the rows in the current table by seeking to the appropriate end time.

CCI on the history table

CREATE CLUSTERED COLUMNSTORE INDEX ix_BenefitsHistory
ON dbo.BenefitsHistory
WITH (DROP_EXISTING = ON);

This will let you get batch mode on the history table, which should make the scans much faster.

NC index on the current table, leading with SysStartTime:

See Paul's answer to the question Most Efficient Way to Retrieve Date Ranges for more details on why indexing for date range queries is hard.  Based on the logic there, it makes sense to add another NC index on the current table that leads with SysStartTime, so that the optimizer can choose which one to use based on statistics and the specific parameters of your query:

CREATE NONCLUSTERED INDEX IX_SysStartTime_SysEndTime
ON dbo.Benefits (SysStartTime, SysEndTime)
/*INCLUDE (ideally, include your other important fields here)*/;

Creating the 3 indexes outlined above made a significant difference in resource usage in my test cases.  I set up a test case which runs two queries that return 1.5 million total rows.  Both the history and current tables have 50 million rows).

Note: To reduce SSMS overhead, I ran the test with "Discard results after execution" option enabled.

Execution Plan - Default Indexes

Logical reads: 1,330,612
CPU time: 00:00:14.718
Elapsed time: 00:00:06.198

Execution Plan - With Indexes Described Above

Logical reads: 27,656 (8,111 row store + 19,545 columnstore)
CPU time: 00:00:01.828
Elapsed time: 00:00:01.150

As you can see, all 3 measures dropped significantly - including total elapsed time, from 6 seconds to 1 second.

The other option presented by the Docs article is to forgo the two NC indexes on the current table in favor of a clustered columnstore index.  In my test, performance was very similar to the indexing solution described above.


Set sync_binlog = 1
When sync_binlog is set to 0 (default), the OS caches all writes to disk and writes in 4K batches. This is for speed. If set to 1, every database update will be immediately written to the binary log. This may cause a higher iops consumption.


There is a huge difference.

If you process a cube then it processes the measure groups and partitions inside it.

If you process a database then it also processes the dimensions


SQL Server Backups - A couple of questions

(1) Yes, I usually have the backup process by itself. I would not be doing much of anything during my backup time if I can. You might have it take the backup and then do the update on stats. As it sounds it seems you are running two jobs (1 for backup, 1 for update stats) at the same time?

(2) Do you copy the backup to tape or other disk storage? If so then I usually clean up the files before I create new backups locally. If not, then if I am scraping for storage space I would consider zipping the backup file prior to the new one being created. (That is if you cannot enable compression on the backups, as @Simon suggested, this would save some space as well.)


I can't test it right now but I think you could do this with open/close scheduler window:

first define a window with DBMS_SCHEDULER.create_window. This window could have a 24 hour duration and repeat every day so that it is always open.
modify your job submission so that they run under this window (parameter schedule_name of the DBMS_SCHEDULER.create_job procedure).
when you need to enter maintenance and/or release resource, use DBMS_SCHEDULER.close_window. When your maintenance is done, use DBMS_SCHEDULER.open_window.

The jobs submitted while the window is closed should be queued and run later when the window is opened.


This code works by passing a date value, such as "getdate()", to a function.

Works great.  Not my work.

Source:  http://mikedefehr.com/2011/07/25/finding-the-nth-weekday-of-a-given-month/

CREATE FUNCTION nth_weekday_of_month_fn (
@dt DATETIME,
@dow VARCHAR(10),
@num INT
)
RETURNS DATETIME
AS
BEGIN

DECLARE @RetDate DATETIME;

WITH MonthDays AS
(
SELECT DATEADD(DAY,number-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@dt),0)) AS
MonthDate
FROM MASTER..spt_values
WHERE number > 0
   AND TYPE = 'P'
)
, WeekDays AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY MonthDate) DayIndex
FROM MonthDays
WHERE YEAR(@dt) = YEAR(MonthDate)
   AND MONTH(@dt) = MONTH(MonthDate)
   AND DATENAME(dw,MonthDate) LIKE @dow + '%'
)
SELECT @RetDate = MonthDate
FROM WeekDays
WHERE DayIndex = @num RETURN (@RetDate) END
GO

For 1st and 3rd Tuesdays of current month:

SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Tuesday',1)
SELECT dbo.nth_weekday_of_month_fn(GETDATE(),'Tuesday',3)


There isn't much you can do about this with RDS. It means the OS decided that whatever got swapped out was less valuable than more page cache. You have no control over this with huge pages or vm.swappiness controls like you would on an EC2 instance.


Handling data encoding issues while loading data to SQL from Script (Notepad++)

One option might be to have them run the script via sqlcmd with the codepage options:

sqlcmd -f <codepage> | i:<codepage>,o:<codepage>

Alternately, save the source files with UTF-8 encoding (code page 65001), with a byte-order mark.


Joining 2 queries to match Months side by side

use a union all

the same way you have used in your other question, combine 2 queries

the idea is exactly the same.

All you have to do is

select * from
(
select [set of columns]
from query1
union all
select [same set of columns as before]
from query2

) as Q
PIVOT
(
SUM(Amount)
FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS query

I think,
if I understood it right, I got it modified for you, see the picture below.

It is a UNION ALL plus a CASE.

creating the tables to get the exercise done was a bit troublesome.

    SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @JDT1 table(

    ContraAct char(6) primary key clustered,
    Account int,
    RefDate datetime,
    credit money,
    debit money

    )

    declare @OACT table(
    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int
    )

    declare @OBGT table(
    AcctCode int primary key clustered,
    AbsId int,
    CredLTotal int,
    DebLTotal int,
    Line_ID int
    )

    declare @BGT1 table(

    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int,
    CredLTotal int,
    DebLTotal int,
    Line_ID int

    )

    insert into @BGT1 values  (1,4,'London','412100',5,1,100,70,1)
    insert into @BGT1 values  (2,4,'London','412100',5,1,100,70,2)
    insert into @BGT1 values  (3,4,'London','412100',5,1,100,70,3)
    insert into @BGT1 values  (4,4,'London','412100',5,1,100,70,4)
    insert into @BGT1 values  (5,4,'London','412100',5,1,100,70,5)
    insert into @BGT1 values  (6,4,'London','412100',5,1,100,70,6)
    insert into @BGT1 values  (7,4,'London','412100',5,1,100,70,7)
    insert into @BGT1 values  (8,4,'London','412100',5,1,100,70,8)
    insert into @BGT1 values  (9,4,'London','412100',5,1,100,70,9)
    insert into @BGT1 values  (10,4,'London','412100',5,1,100,70,10)
    insert into @BGT1 values  (11,4,'London','412100',5,1,100,70,11)
    insert into @BGT1 values  (12,4,'London','412100',5,1,100,70,0)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311202',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311203',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311204',2,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311205',2,'20151231',10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311206',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311207',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311208',2,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311209',2,'20151031',10,7)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (2,4,'London','412100',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (3,4,'New York','412101',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (4,4,'Rome','412102',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (5,4,'Paris','412103',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (6,4,'Moscow','412103',5,1)

    update @BGT1
    set GroupMask  = 4

    update @OACT
    set GroupMask  = 4

    insert into @OBGT
    select
    AcctCode,
    AbsId,
    CredLTotal,
    DebLTotal,
    Line_ID
    from @BGT1

    --select * from @OBGT

    --select * from @OACT

    --select * from @BGT1

    SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
    MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
    FROM @OACT T0
    LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account]
    --LEFT JOIN @OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] -- this is not being used here
    WHERE T0.GroupMask = 4 AND T0.Levels = 5
    AND (T1.RefDate BETWEEN '20150101' AND '20151231')
    GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate

    SELECT T0.AcctCode AS 'SAP Code',
           T3.AcctName AS 'Description',
           T3.FatherNum, 1 AS 'Group',
           'Turnover' [Definition],

            T1.Line_ID+1 AS 'Month',

           CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0)
THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

          FROM @OBGT T0
    INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
    INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode]
    WHERE T3.GroupMask = 4

    SELECT * FROM
    (

    SELECT T0.AcctCode AS 'SAP Code', T0.AcctName AS 'Description', T0.FatherNum, 1 AS 'Group', 'Turnover' [Definition],
    MONTH(T1.RefDate) AS Month, SUM(T1.Debit - T1.Credit) AS 'Amount'
    FROM @OACT T0
    LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account]
    --LEFT JOIN @OBGT T2 ON T0.[AcctCode] = T2.[AcctCode] -- this is not being used here
    WHERE T0.GroupMask = 4 AND T0.Levels = 5
    AND (T1.RefDate BETWEEN '20150101' AND '20151231')
    GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate

    UNION ALL

    SELECT T0.AcctCode AS 'SAP Code',
           T3.AcctName AS 'Description',
           T3.FatherNum, 1 AS 'Group',
           'Turnover' [Definition],

            T1.Line_ID+1 AS 'Month',

           CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0)
           THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

          FROM @OBGT T0
    INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
    INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode]
    WHERE T3.GroupMask = 4

    ) AS q
    PIVOT
    (
        SUM(Amount)
        FOR [Month] IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS query

I believe, if I understood well, you want something like the picture above.

if that is not right, then, please create a picture of what you need, even if you do it in excel, but try to use the values according to the tables in the script below. Or post your table definitions with some scripts inserting data into them, then we would be definitely on the same page.

Also, I have not included all of your columns, if they are really important you can add them to the script below.

 SET NOCOUNT ON
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    declare @JDT1 table(

    ContraAct char(6) primary key clustered,
    Account int,
    RefDate datetime,
    credit money,
    debit money

    )

    declare @OACT table(
    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int
    )

    declare @OBGT table(
    AcctCode int primary key clustered,
    AbsId int,
    CredLTotal int,
    DebLTotal int,
    Line_ID int
    )

    declare @BGT1 table(

    AcctCode int primary key clustered,
    AcctName varchar(108),
    FatherNum char(6),
    GroupMask int,
    Levels int,
    AbsId int,
    CredLTotal int,
    DebLTotal int,
    Line_ID int

    )

    insert into @BGT1 values  (1,4,'London','412100',5,1,100,70,1)
    insert into @BGT1 values  (2,4,'London','412100',5,1,100,70,2)
    insert into @BGT1 values  (3,4,'London','412100',5,1,100,70,3)
    insert into @BGT1 values  (4,4,'London','412100',5,1,100,70,4)
    insert into @BGT1 values  (5,4,'London','412100',5,1,100,70,5)
    insert into @BGT1 values  (6,4,'London','412100',5,1,100,70,6)
    insert into @BGT1 values  (7,4,'London','412100',5,1,100,70,7)
    insert into @BGT1 values  (8,4,'London','412100',5,1,100,70,8)
    insert into @BGT1 values  (9,4,'London','412100',5,1,100,70,9)
    insert into @BGT1 values  (10,4,'London','412100',5,1,100,70,10)
    insert into @BGT1 values  (11,4,'London','412100',5,1,100,70,11)
    insert into @BGT1 values  (12,4,'London','412100',5,1,100,70,0)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311202',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311203',1,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311204',2,'20151231', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311215',4,'20151231',5,2)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311216',4,'20151231',5,3)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311219',4,'20151030',5,3)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311217',5,'20151231',8,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311218',6,'20151231',4,6)

        insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311205',2,'20151231',10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311206',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311207',1,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311208',2,'20151031', 10,7)

    insert into  @JDT1 (ContraAct,Account,RefDate,credit,debit)
    values ('311209',2,'20151031',10,7)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (2,4,'London','412100',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum,Levels )
    values (3,4,'New York','412101',5)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (4,4,'Rome','412102',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (5,4,'Paris','412103',5,1)

    insert into @OACT(AcctCode,GroupMask,AcctName,FatherNum ,Levels,AbsId)
    values (6,4,'Moscow','412103',5,1)

    update @BGT1
    set GroupMask  = 4

    update @OACT
    set GroupMask  = 4

    insert into @OBGT
    select
    AcctCode,
    AbsId,
    CredLTotal,
    DebLTotal,
    Line_ID
    from @BGT1

    --select * from @OBGT

    --select * from @OACT

    --select * from @BGT1

    declare @months table(
     i smallint primary key clustered,
     name varchar(25))

     insert into @months
     select 1,'jan'
        union all select
            2, 'feb'
        union all select
            3, 'mar'
        union all select
            4, 'apr'
        union all select
            5, 'may'
        union all select
            6, 'jun'
        union all select
            7, 'jul'
        union all select
            8, 'aug'
        union all select
            9, 'sep'
        union all select
            10, 'out'
        union all select
            11, 'nov'
        union all select
            12, 'dec'

SELECT
 K.[SAP Code]
,K.Description,
[jan_Budget],
[jan_Amount],[feb_Budget],
[feb_Amount],[mar_Budget],
[mar_Amount],[apr_Budget],
[apr_Amount],[may_Budget],
[may_Amount],[jun_Budget],
[jun_Amount],[jul_Budget],
[jul_Amount],[aug_Budget],
[aug_Amount],[sep_Budget],
[sep_Amount],[out_Budget],
[out_Amount],[nov_Budget],
[nov_Amount],[dec_Budget],
[dec_Amount]

FROM

(
    SELECT A.*
      FROM (

                SELECT K.[SAP Code]
                      ,K.[Description]
                      ,AMOUNT = COALESCE(K.Amount,0)
                      ,PIVOT_KEY = M.NAME + '_Amount'
                FROM @months M
                     LEFT OUTER JOIN

                        (   SELECT T0.AcctCode AS 'SAP Code',
                                   T0.AcctName AS 'Description',
                                   T0.FatherNum, 1 AS 'Group',
                                   'Turnover' AS [Definition],
                            MONTH(T1.RefDate) AS 'Month',
                            SUM(T1.Debit - T1.Credit) AS 'Amount'
                            FROM @OACT T0
                            LEFT JOIN @JDT1 T1 ON T0.[AcctCode] = T1.[Account]
                            WHERE T0.GroupMask = 4 AND T0.Levels = 5
                            AND (T1.RefDate BETWEEN '20150101' AND '20151231')
                            GROUP BY T0.AcctCode, T0.AcctName, T0.FatherNum, T1.RefDate
                        ) K
                        ON K.Month = M.i

           ) RADHE
    PIVOT (SUM([AMOUNT]) FOR PIVOT_KEY IN ([jan_Amount],[feb_Amount],[mar_Amount],
                                         [apr_Amount],[may_Amount],[jun_Amount],
                                         [jul_Amount],[aug_Amount],[sep_Amount],
                                         [out_Amount],[nov_Amount],[dec_Amount])) A
    where [SAP Code] is not null

) K

INNER JOIN

(

SELECT *
  FROM (

              SELECT K.[SAP Code]
                  ,K.[Description]
                  ,AMOUNT = COALESCE(K.Amount,0)
                  ,PIVOT_KEY = M.NAME + '_Budget'
            FROM @months M
                 LEFT OUTER JOIN

                    (

                        SELECT T0.AcctCode AS 'SAP Code',
                               T3.AcctName AS 'Description',
                               T3.FatherNum, 1 AS 'Group',
                                T1.Line_ID+1 AS 'Month',

                               CASE WHEN (T1.CredLTotal IS NULL OR T1.CredLTotal = 0)
                    THEN T1.DebLTotal ELSE T1.CredLTotal END AS Amount

                              FROM @OBGT T0
                        INNER JOIN @BGT1 T1 ON T0.[AbsId] = T1.[AbsId]
                        INNER JOIN @OACT T3 ON T0.[AcctCode] = T3.[AcctCode]
                        WHERE T3.GroupMask = 4

                    ) K
                    ON K.Month = M.i

       ) RADHE
PIVOT (SUM([AMOUNT]) FOR PIVOT_KEY IN ([jan_Budget],[feb_Budget],[mar_Budget],
                                     [apr_Budget],[may_Budget],[jun_Budget],
                                     [jul_Budget],[aug_Budget],[sep_Budget],
                                     [out_Budget],[nov_Budget],[dec_Budget])) B

) R
ON 1=1
AND  K.[SAP Code] = R.[SAP Code]


Is it wrong to consider SCAN DENSITY as a performance metric in higher versions of SQL Server apart from SQL Server 2000?

Scan Density is how many times you jump from one extent to another (dividing the "optimal" to the current state). Thing is that this is a side effect of logical fragmentation (jump back and forth between pages, and you are likely to jump back and forth between extents). When the DBCC was redone into a DMV, Paul cleaned up lots of stuff in there.
I suggest you work with logical frag instead (avg frag in percent), if you care about how contiguous things are in the first place. Perhaps more interesting is how full pages are, since random vs seqential I/O is probably more or less irrelevant with modern hardware (read-ahead set aside).
Paul vocals pretty strongly how we should stop using the old DBCC and use the DMV instead.  Do some searches and you will find lots of good nuggets out there. Here is one thread that can give you some insights, for instance: https://social.microsoft.com/Forums/partner/en-US/8d01a374-1493-43c3-8cea-5bf5de723b4d/dmdbindexphysicalstats-and-dbcc-showcontig-disparity.


This may be a typo in your question, but just to be safe I'll post this as a possible answer - you shouldn't have those single quotes around @siteId, it should be like this:

siteGUIDInAnotherEntity=@siteId

The text string '@siteId' can't be converted into a uniqueidentifier for that join, and will result in the error you mentioned:

SELECT CAST('@siteId' AS uniqueidentifier);

  Msg 8169, Level 16, State 2, Line 1
  Conversion failed when converting from a character string to uniqueidentifier.


SELECT users, COUNT(*) FROM (
    SELECT
    `user1` AS users
    FROM usuarios
    WHERE (`user1` is not null)
    UNION ALL
    SELECT
    `user2`
    FROM usuarios
    WHERE (`user2` is not null)
) sq
GROUP BY users;

Do the grouping outside. The trick is to use UNION ALL instead of UNION. The latter one uses a distinct on the result. You don't have to include the id column in the subqueries, so you can later count it, because it's the primary key and doesn't allow null values anyway.


tablediff is not designed to be used as a real-time synchronization tool (it seems this is your goal). If you really need real-time sync, there's database mirroring. The primary server sends its transaction logs to the secondary, which then replays the transaction logs - so the databases are synchronized
Check out MSDN for more info Database Mirroring (SQL Server)


Mongodb migration and appending data to existing database

In mongoshell you can connect to several databases and use them at the same time. A plain simple copy would be this one:
var db_A = Mongo("mongodb://user:password@host-A").getDB("data")
var db_B = Mongo("mongodb://user:password@host-B").getDB("data")

var docs = db_A.getCollection("collection_name").find().toArray();
if (docs.length > 0) {
    db_B.getCollection("collection_name").insertMany(docs, {ordered: false});
}

Your migration script will look more complex of course, but that's the way you can do it.


SQL Select where value = to another select

You can use a CTE for this work:

WITH ctSum AS
(
    SELECT   customerId, SUM(PartQty) as TotalQty
    FROM     OrdersParts
    GROUP BY customerID
)
SELECT a.customerID, a.Total_number_parts
FROM   Orders a
JOIN   ctSum b
ON     b.customerID = a.customerID
WHERE  a.Total_number_parts = b.TotalQty;

Or if you prefer another syntax:

SELECT a.customerID, a.Total_number_parts
FROM   Orders a
JOIN   (SELECT   customerId, SUM(PartQty) as TotalQty
        FROM     OrdersParts
        GROUP BY customerID) b
ON     b.customerID = a.customerID
WHERE  a.Total_number_parts = b.TotalQty;


Analyze, Vacuum equivalent for SQL Server?

As far as I understand, rough equivalent of ANALYZE would be UPDATE STATISTICS.

  Updates query optimization statistics on a table or indexed view. By
  default, the query optimizer already updates statistics as necessary
  to improve the query plan; in some cases you can improve query
  performance by using UPDATE STATISTICS or the stored procedure
  sp_updatestats to update statistics more frequently than the default
  updates.

Quite often automatic updates of statistics is enough, but I came across few cases when I had to do it manually to get a good query plan. One common case is when rows are added to a large table and the range of values for new rows lies outside the existing statistics histogram, the so called Ascending Key Problem.

Rough equivalent of VACUUM FULL would be commands that shrink database.

  Shrinking data files recovers space by moving pages of data from the
  end of the file to unoccupied space closer to the front of the file.
  When enough free space is created at the end of the file, data pages
  at end of the file can be deallocated and returned to the file system.

In many cases shrinking a database is a bad idea.

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/

When is it OK to shrink a Database?

The feature is there, but it should be used to address some very specific problems.

As @dezso pointed out in the comments, simple VACUUM command only marks pages as free for reuse in the same table, it doesn't compact the file. SQL Server does it automatically via the Ghost cleanup background process. Actually, it is possible to turn it off. So, it looks like this Ghost cleanup process is a rough equivalent to a simple VACUUM.


The task of an optimizer is to create several execution plans and compare them (typically by costs). Each execution plan can be seen as logical transformation.

For example: a simple join would always be the cartesian product of two tables. Each row from the first is read and combined with each row in the other table. Than the join condition is checked and the resulting row is used or discarded.

That would be our live without optimizer. The optimizer is using set theory and boolean algebra to transform it. Each transformation leads to the same result. For instance tt doesn't matter if you first sort two sets independent and join them afterwards or first join them and sort them as one.

Other optimizations are the way rows are fetched or compared. There are for example many join algorithms like nested loop join, hash join or sort-merge join. Each algorithm has different advantages and disadvantages. But they operate on the same data. The only (hypothetical) way a execution plan could lead to other results is a difference in rows stored and the index. For that reason the indices have to be always updated as a row is inserted/updated/deleted.


SQL Server imports data in wrong charset with SSIS

The columns in the connection manager that your datasource is using need to be defined as "Unicode String (DT_WSTR)".


3 way junction table and redundant value

You need to normalize your schema.  Your Employee_Owns_Asset table is trying to do too many things.  Consider this schema:

Note that your individual assets need to be normalized out into their own table.  This lets you separate the two semantically independent pieces of information:

What are the properties (brand, model, etc.) of each asset?
Who (if anyone!) owns this asset at a particular point in time?

This is a case of a requirement for fourth normal form (4NF), where it is important to isolate independent many-to-many relationships.

Note that if you don't want to track an asset's owner over time, then you could simplify the illustrated model and make ASSET.owning_employee_key a foreign key on the ASSET table and eliminate the EMPLOYEE_OWNS_ASSET table from the model.


copydb, shutdown commands are not supported in ApsaraDB for MongoDB. Any Alternate Available

The commands you've listed are administrative commands that you should expect to be limited when using a managed Database-as-a-Service provider. I would anticipate further restriction for commands that are either subsumed by the managed platform functionality (for example, replica set configuration) or likely to introduce performance/stability issues. For comparison, MongoDB Atlas also has a list of unsupported commands for shared or dedicated clusters.

In the event your deployment gets in state where admin help is needed, the provider's support staff can presumably escalate as required.

For context on the specific commands you've listed:

clone and copydb are officially deprecated since MongoDB 4.0 but also discouraged in older server versions. These commands do not produce point-in-time snapshots of the source database and will introduce performance and concurrency problems (such as blocking foreground index builds). Use mongodump and mongorestore instead.
clean is an internal command with no end user utility.
shutdown doesn't make sense for a managed service where you don't have direct access to restart a MongoDB process. It is possible the provider's UI or API provides an option to restart or suspend a member of your deployment but reasonable not to have direct access.
repairDatabase and  repairCursor should only be used as a last resort to salvage data if you don't have a viable backup for your deployment. This is definitely a scenario where you'd be working with the provider's support team to get your deployment back online with the most reasonable approach.


I think you're one the right track. Make your database model the process. Try to think of People, Security Roles, and Courses as separate entities that have relationships - not always directly.

In your scenario, you're describing a many to many relationship between roles, courses, and people.

In one table you have people - all the demographical data like name, address, and phone number. A second table that has different roles a person may have with in the system - instructor, student, auditor, teaching assistant, etc. The third table has courses - all the demographical data course name, department, cost, etc. The final table has Security permissions - by web pages, functions, or whatever makes sense in the context of your application.

In between you're going to have tables that break these relationships into 1 to many relationships. A person with the Role of Instructor has multiple courses assigned to them, a table with a surrogate key for a specific instructor and the surrogate key for their course load.

I would like at building something like this - https://en.wikipedia.org/wiki/Swim_lane before building your schema. When you understand who does what, and in what order, you get a better understanding of what your data model should look like.


On a typical COW filesystem, is the database WAL redundant (in theory)?

No, because the WAL is also used for restoring from backups -- for example, in case of user error. In combination with a prior full backup, it allows restoring to any point in time -- presumably prior to when the user did something bad.


mysql replication missed quite a lot SQL statement

Two possibilities come to mind:

*SQL_LOG_BIN* was temporary turned off.
Your master server crashed without synchronizing the binary log
(and most probably *SYNC_BINLOG* was set to a value different than 1 ...).


Migrate replication from SQL Server 2008 R2 to SQL Server 2016 - Old to new hardware

I am going to that with my same type migration this past February, I had to do all systems in one day. I had all my servers prepped before had with the exact same folder structure and included the folder name MSSQLSERVER10.  I shut down all services and disabled them.  I then copied all the system databases to the exact same folder location on the new system.  I started SQL Services only to ensure systems came up.  Once that was confirmed I brought over 1 of the replicated systems to ensure they come up.  Once that was successful, I brought over everything else, turned the systems on and waited a bit to start the agent as I wanted to check and ensure every db came back online.  I then turned on agent service on Primary first and then started all of the secondaries.  Forcing replication, or rebuilding it was not an option for me.  There are other ways to do it but this was the one I decided to use for the environment we have.  I was luckily enough to have an outage window to complete this.


Couple of points of clarification. Nodes and replicas are not the same thing. Nodes refer to the Windows Failover cluster and replicas to AvailabilityGroup instances. So assuming you are running a 2 node WSFC, each hosting a SQL instance in an AvailabilityGroup, then yes, you will need another server to achieve quorum and avoid split brain behavior. The easiest way to achieve this is simply set up a quorum file on another server outside of the cluster, known as Node and File Share majority. Read more on WSFC Quorum Modes.

You don't necessarily need to commission a new server for this.  Any server outside of the server will work.


If you really want to have that specific space and line break, you should not rely on enviroment settings, but explicitly specify it:

insert into emp values(1,'
Mithrivanam, Ameerpet, ' || chr(10) ||
'Hyderabad, Telangana, 500018
');


mysql UNIQUE KEY multiple columns with one column as NULL

Possibilities, you can:

use the BDB-Storage engine in MySQL prior 5.1. which treats nulls as non-distinct to each other. Later versions don't support BDB
introduce a special value for "no kid_id", so you won't have nulls
add a not-null pseudo column, which is filled by triggers and has to be in the constraint instead of the original kid_id
use a before-insert trigger instead of the constraint, which checks if there is a row containing all the 'unqiue' fields (use <=> for also compare nulls) and changes the PK of the to-be-inserted-row to the one of the existing one so it results in a "Duplicate key" error.


Nullable smallint column usage in a CASE Statement nested in a STUFF function

Another way to create your StatusList is, using STRING_AGG (From SQL Server 2017):

SELECT STRING_AGG(x.status,',') FROM (
   SELECT
      ROW_NUMBER() OVER (PARTITION BY StatusId) as RowNum,
      CASE WHEN Status.Rescriced = 1 THEN ItemStatus + ' (Restricted)'
           ELSE ItemStatus END AS Status
   FROM Statuses) x

Above query is untested, and should be adapted to your situation.


sys.dm_exec_query_stats - what causes total_worker_time to be reset? How to work around that?

I'm pretty terrible at these DMVs however:

When you're joining on the history you can have one plan_handle with multiple sql_handle (where the plan executes with different options), so possibly this is skewing your stats.
The plan handle is for an entire batch, with individual statements inside it, which can be recompiled separately, and this might (I haven't confirmed) overwrite the stats for the batch. You might be able to confirm by capturing with traces/events for plan recompilations and seeing if the times correlate with your counter resets.


How to configure database mail in sql server 2012

You don't have the required permission to access the database mail settings. Ask your DBA to configure it for you. If you don't have a DBA then you will need to logon to the server with an account that has sysadmin server role.


PL-SQL query to find the most recent record the "Operation_date" of which is lower than a specified date

Analytic Functions
select
  col_id, amount
from
 (
   select
     d.col_id, d.amount,
     rank() over (partition by d.col_id order by d.operation_date desc) as rk
   from doc_amount_detail d
   where.d.operation_date <= to_date('11/15/2020', 'mm/dd/yyyy')
 )
where rk = 1;


Combining count queries into one table row

Define Query objects (call them QRented and QInventory) with SQL definitions as you have provided:

(SELECT Machine.MNAME, Count(Rental.Mid) AS CountOfMID1
FROM Machine JOIN Rental ON Machine.[MID] = Rental.[MID]
GROUP BY Machine.MNAME;)

and this:

(SELECT Machine.MName, Count(Machine.Mid) AS CountOfMID
FROM Machine
GROUP BY Machine.MName
ORDER BY Machine.MName;)

Now create a combined query from your Query objects like this:

SELECT QInventory.MName, QInventory.CountOfMID as Inventory,
    QInventory.CountOfMID  - QRented.MIDI as Available,
    QRented.MIDI  as Rented
FROM QQInventory
LEFT JOIN QRented on QQInventory.MName = QRented.MName
ORDER BY Machine.MName;


MySQL 5.5.57 No longer supports myisamck *.MYI

Still happening in MySQL 8.0.11

Walkarround solution: NOT USE THE MYI EXTENSION

myisamchk mytable


Restore a SQL Server 2012 backup file in SQL Server 2016/2014

Yes this will work.  You can always go forward, you can't go backwards, at least not with backup and restore.  With older version eg SQL 2000, SQL 2005 sometimes you have to do a stepped upgrade, ie go from 2000 to 2008 to 2014, but that shouldn't be a problem for you.

Be aware when you restore your databases they will have the older database compatibility level.


[sys].[dm_db_index_usage_stats] returns more entries in some databases

From taking a quick look, it's quite possible that you're actually looking at indexes on different databases.

Alternatively, you could have a partitioned table as the docs state that sys.dm_db_partition_stats:

  Returns page and row-count information for every partition in the
  current database.

Take it back to basics, don't use DB_ID() and actually use the database_id field within dm_db_index_usage_stats to join to sys.databases.

For now, it would also be best to remove dm_db_index_physical_stats (the INNER JOIN is not a good way to join to it anyway).

SELECT DB.[name] AS [database]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]
      ,IDX.[name] AS [Index]
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = US.database_id
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0

Check the database names. Are they all definitely the same database?

Add PRS.partition_id to your SELECT. Are they all the same partition?

If that looks to be correct, then perhaps APPLY the dm_db_index_physical_stats table.

SELECT DB.[name]
      ,OBJECT_NAME(IDX.[object_id]) AS [Table]
      ,IDX.[name] AS [Index]
      ,US.[user_seeks]
      ,US.[user_scans]
      ,US.[user_lookups]
      ,US.[user_updates]
      ,US.[last_user_seek]
      ,US.[last_user_scan]
      ,US.[last_user_lookup]
      ,US.[last_user_update]
FROM [sys].[dm_db_index_usage_stats] US
INNER JOIN [sys].[indexes] IDX
    ON IDX.[index_id] = US.[index_id]
    AND IDX.[object_id] = US.[object_id]
INNER JOIN [sys].[dm_db_partition_stats] PRS
    ON PRS.[index_id] = US.[index_id]
    AND PRS.[object_id] = US.[object_id]
INNER JOIN [sys].[databases] DB
    ON DB.[database_id] = US.database_id
CROSS APPLY sys.dm_db_index_physical_stats(US.DATABASE_id, US.OBJECT_ID, US.index_id, NULL, NULL)  AS ddius
WHERE [is_primary_key] = 0
    AND US.[user_seeks] = 0
    AND US.[user_scans] = 0
    AND US.[user_lookups] = 0
ORDER BY ddius.page_count DESC


~ POSIX Regex Operator

The easy way to do this is to use the ~ operator.

SELECT *
FROM ( VALUES
  ( '/{s3bucket}/filesuser/client/27801123/attachment/4510/file/1113/small/photo.jpg' ),
  ( '/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/small/photo.jpg' ),
  ( '/{s3bucket}/filesuser/client/27801124/attachment/4511/main/111/main' ),
  ( '/{s3bucket}/filesuser/client/27801125/attachment/4512/file' ),
  ( '/{s3bucket}/filesuser/client/27801126/attachment/4513/main' )
) AS t(path_name)
WHERE path_name ~ '/main$';

You can always be more specific with your regex..

WHERE path_name ~ '/filesuser/client/\d+/attachment/\d+/main$';


Connection pooling surely has that type of behavior, but the providers I'm familiar with wouldn't typically keep those connections active to reuse for days, or even hours.  In fact, ADO.NET will eliminate the connection from the pool in about 4 to 8 minutes or inactivity.

Yes, it sounds like you have clients not properly closing and disposing of connections, which could be leading to more severe problems than just connection placeholders.  But at the same time, you should not only be verifying login_time, but you also have to consider last_request_start_time to get an idea of activity of the session (or the lack thereof).

  Is this standard?

It depends.  For that duration, no that's not typical and surely warrants the investigation of how clients are managing connections to the data source.

  should those conenctions that are sleeping be closed?

You would first need to determine that they shouldn't be currently active, and then you would need to confirm with the business and end users whether or not you can clean that up.  But that would most likely be a very temporary fix, as if this is programmatic connections (think client applications) then it will probably come back once applications and/or end users reconnect.

Take a look at this blog post I wrote about connection pooling.  It is relatively thorough and should give you a good idea on distinguishing between connection pooling, as well as poorly managed connections.


Actually, this plan is kind of dangerous for the mysql schema. Why ?

You are upgrading two versions up. mysql_upgrade will not respond well to this. I have a post where someone upgraded without addressing mysql.user : Any known issues upgrading from MySQL 5.1.73 to 5.6.21?

I have old posts on how to upgrade two versions in one shot

Apr 11, 2013 : MySQL upgrade 5.0.88 to latest (Best Reference)
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?
Jul 26, 2011 : Restoring an old backup to latest MySQL release

Here is an additional post on how to move the grants with pure SQL : Upgrading mysql myisam 5.1 to mysql 5.6: force innodb on restore?. That post is based on Linux.

To do it in Windows, here are the steps

STEP 01 : Run This

set MYSQL_USER=root
set MYSQL_PASS=rootpassword
set MYSQL_CONN=-u%MYSQL% -p%MYSQL_PASS%
set SQLSTMT="SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
set SQLSTMT=%SQLSTMT% FROM mysql.user WHERE user<>''"
mysql %MYSQL_CONN% -ANe%SQLSTMT% > GetGrants.sql
echo "SET sql_log_bin = 0;" > MySQLUserGrants.sql
mysql %MYSQL_CONN% -AN < GetGrants.sql >> MySQLUserGrants.sql

STEP 02 : Append a semicolon to every line in MySQLUserGrants.sql

STEP 03 : Load MySQLUserGrants.sql into MySQL 5.6 setup


looks like this an issue with mysql workbench 8 . Try this workaround.
1. locate or create your my.cnf file. E.g. if you're on a unix platform, look for ~/.my.cnf and create it if it doesn't exist.
2. update my.cnf to contain

[client]
port        = 3306
[mysqld]
port        = 3306
secure_file_priv=''
local-infile = 1

restart the MySQL server.


Try dropping and recreating any indexes on items.id or items_attribute.attribute_id.


Post mortem on tempdb transaction log after instance restart?

Short answer is - Not Really as Kin got at.

That said the default server side trace, if enabled as it is by default, may be of help to you here. Look in the "LOGS" directory in SQL Server and look in the SQL Server installation directory for your instance. It doesn't show you everything that happened, unfortunately. But it could show you the times of the autogrowths to compare against (that is included in the trace). It can also show you hash warnings and sort warnings which could be a cause of TempDB growth. You can also see object creations and deletes for any objects for certain explictly created objects. You can see more about what is included and how to view/query this here.

Kin's answer is also good about looking at what is going on before you restart. Now one note here is you may not need to use the DAC to connect (This link describes what the DAC is). If you are connected or can connect, next time you can run some queries to at least get a sense for what is going on at the time of the event:

SP_Whoisactive is a great script to show you what is going on at that moment, you can also see who is consuming TempDB at that time using the TempDB_Writes/TempDB_Current columns to see the allocation in TempDB.
The scripts in this article are excellent at showing what is consuming space in TempDB. Also this question discusses it with some other queries.

Trick is seeing how TempDB is being used and addressing it over time in your processes and ensure TempDB is appropriately sized and the drives are appropriate for your needs.


Queries that do "full table scan" are the ones that don't use indexes. Try to log them in the slow query log using this option log_queries_not_using_indexes

Be careful though that small tables that have frequent queries running against will fill your slow query log files. You may want to enable this option for limited amount of time. To reduce this chance, you may set min_examined_row_limit variable to a reasonable value, depending on your small tables.


How to Substitute Variables in PostgreSQL

These variables are a feature of SQL*Plus.

psql, the equivalent program in the PostgreSQL world, also has variables.

Use

\set variable 'value'

and

insert into mytable(mycolumn)
values (:variable);


In creating view use SQL Security definer or invoker?

Quotes taken from the documentation.

  If you have the SUPER privilege, you can specify any syntactically legal account name. If the account does not actually exist, a warning is generated.

Being able to specify security accounts other than the view's creator is a key piece of using DEFINER, which you seem to have missed.

  When a view has been referenced, privileges for objects accessed by the view are checked against the privileges held by the view DEFINER account...

This means that you can set the DEFINER to use a security account which has access to the underlying objects, but deny access to those objects to users at large, thus maintaining tighter security.

For example, say you have some table PrivateData, which users should not be able to generally access, but you want to create an internal report which tracks metadata on this table. You deny permissions on PrivateData to users, but create an account PrivateDataReader which has read-only access to the table. You can then create a view which presents the metadata information, specifying PrivateDataReader as the DEFINER.

If you always want to use the security of the user accessing the view, then you should specify INVOKER.


Transaction Replication Without Snapshot

What version of SQL Server are you using ?

I just setup a publication and subscription in the method you describe on SQL Server 2012 (11.0.2218) and it appears to be working fine.

I tested inserting, updating, and deleting records from the source - and all transactions made it successfully to the destination.

It is true that the timestamp column is of interest but I didn't have to use the option you mentioned in your update.  From this SQL 2000 TechNet article note that

  "The literal values for a timestamp column are replicated, but the data type for the replicated values is changed to binary (8) on the Subscriber."

Since this was for SQL 2000 I can only imagine things may have changed... as my subscriber table, just like the publication, has a timestamp column not a binary(8).

Therefore the values that are there when you backup and restore will match.  Future inserts will have different timestamp values in the source and destination.

I scripted the entire thing out into one file.  The order of events:

create the publication (with @allow_initialize_from_backup = N'true');
backup the source database (with format, init);
restore the database to the destination;
create the subscription (with @sync_type = N'initialize with backup', @backupdevicetype = 'disk', @backupdevicename = '\myUNCPath\Backup\Backup.bak').

I ran into Msg 21397 during my first try - and an interesting read, explanation, solution are in this blog post.


how to Update Value in local tabel when job Finished

Because there are several apparently semi-independent jobs running, I would suggest implementing a status table and creating a trigger for that table.  It depends on how complex you want to be, but let's keep it to a very simple example.  Perhaps a table:

CREATE TABLE dbo.JobGroupStatus
(Job1 INT,
 Job2 INT,
 Job3 INT,
 Job4 INT,
 Job5 INT
 Job6 INT);

When the first job starts it could set all columns to 0.  Then as job one finishes it would "UPDATE dbo.JobGroupStatus SET Job1 = 1", then Job2 when it finishes would update Job2, etc.  Create a trigger on that table that discerns when all jobs have finished.

 CREATE TRIGGER UPD_JobGroupStatus ON dbo.JobGroupStatus
 FOR UPDATE
 AS
 IF (SELECT Job1+Job2+Job3+Job4+Job5+Job6
     FROM dbo.JobGroupStatus) = 6
   Update dbo.FinishedJobGroup Set AllDone = 1;
  GO

Of  course, you would need to have a periodic check that would determine that apparently all 6 jobs will not finish and report failure.

This little snippet of code is just a toy, not a serious production program. But perhaps it offers you an idea or two about how you might approach your problem.


Does the pgpool-II extension allow for parallel execution of any query?

To my knowledge it does not allow that level of parallelism. If that's what you are trying to do the best approach is to go with Postgres-XC or GridSQL, but Postgres-XC while more complex is also more flexible.

http://postgres-xc.sourceforge.net/ is the project page.


Meaning of 'SET' in error message 'Null value is eliminated by an aggregate or other SET operation'

I was just looking through the SQL-92 Specification and saw a passage that reminded me of this question.

There is in fact a prescribed warning for this situation as indicated below

  b) Otherwise, let TX be the single-column table that is the
                result of applying the <value expression> to each row of T
                and eliminating null values. If one or more null values are
                eliminated, then a completion condition is raised: warning-
                null value eliminated in set function.

I assume that the SET in the SQL Server Error Message is a reference to the set function of that error message although I'm not sure why it would make a distinction between aggregates and other set functions, as far as I can see they are synonymous. The relevant bit of the grammar is below.

6.5  <set function specification>

         Function

         Specify a value derived by the application of a function to an
         argument.

         Format

         <set function specification> ::=
                COUNT <left paren> <asterisk> <right paren>
              | <general set function>

         <general set function> ::=
                <set function type>
                    <left paren> [ <set quantifier> ] <value expression> <right paren>

         <set function type> ::=
              AVG | MAX | MIN | SUM | COUNT

         <set quantifier> ::= DISTINCT | ALL


You can use a correlated sub-query.

SELECT *
FROM topics
INNER JOIN
(SELECT DISTINCT(topic) as topic FROM answers LIMIT 500) x on x.topic = topic_id


The documentation to dbcc shrinkfile says:

  Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used. The size of the virtual log file is chosen dynamically by the Database Engine when log files are created or extended.

So it isn't possible to shrink the logfile to an arbitrary small size. To check your virtual log files, you can use

  DBCC LOGINFO

There you can see the virtual log files and their size. Probably you hit the virtual log file size.


SQL Server does not internally support Regular Expressions. LIKE and PATINDEX both support very limited wildcards, including a single-character range match [...] or exclude [^...] that are of similar syntax to RegEx, and are to a degree functionally similar, but are certainly not RegEx.

If you want / need Regular Expressions in SQL Server, you need to use SQLCLR. You can either code this yourself, or use pre-built functions such as the ones available in SQL# (which I wrote). Most of the RegEx functions are available in the Free version. I think you could use RegEx_Matches to return a result set of words that are not in your exclude list, and then combine that with the String_ToTitleCase4k function (also available in the Free version) to do the InitCap.

For example:

DECLARE @Input NVARCHAR(MAX) =
               N'santacroce sull''arno o''sullivan suLL sUlLiVan gsantacroce',
        @Expression NVARCHAR(4000) =
               N'[\s](?!di\s|in\s|sull\s|on\s|upon\s|von\s|uber\s|ueber\s)[\w]';

-- show matches for debugging
SELECT word.[StartPos],
       word.[EndPos],
       word.[Value] AS [Original],
       SQL#.String_ToTitleCase4k(word.[Value], N'') AS [TitleCased]
FROM   SQL#.RegEx_Matches(@Input, @Expression, 1, N'ignorecase') word;

SELECT @Input = STUFF(@Input,
                      word.[StartPos],
                      ((word.[EndPos] - word.[StartPos]) + 1),
                      SQL#.String_ToTitleCase4k(word.[Value], N'')
                                                COLLATE Latin1_General_100_BIN2)
FROM SQL#.RegEx_Matches(@Input, @Expression, 1, N'ignorecase') word;

SELECT @Input AS [Fixed];

Returns:

StartPos    EndPos    Original    TitleCased
--------    ------    --------    ----------
11          12        s           S
21          22        o           O
32          33        s           S
37          38        s           S
46          47        g           G

Fixed
-------------------------
santacroce Sull'arno O'sullivan SuLL SUlLiVan Gsantacroce

The reason that it doesn't work entirely correctly is due to your regular expression being incorrect:

It only matches a single letter.
It won't exclude any of the fragments if they are at the end of the string, but that might be ok if that never occurs in actual use.
It won't include the first word of the string (due to the required white-space to the left), but that might be ok if that never occurs in actual use.

UPDATE:
I was able to fix your regular expression by changing it to be as follows:

\b(?!(?:di|in|sull|on|upon|von|uber|ueber)\b)\w+

Main differences from the original:

I am using \b (word boundary) instead of \s (white-space) as it handles beginning and ending of line/string. It also doesn't capture the white-space, which \s does, and if it isn't visually obvious, each of the matched strings above is prefixed with the space that matched. While that space wouldn't affect the replacement as it would still be a space, it did prevent the first word in the group from matching, unless the entire string was preceded by some white-space. In the case of using this with addresses, if they always start with a number then perhaps there will always be that preceding space, but best not to include it in the match.
I added the + (one or more) quantifier to the \w so that it will pick up more than just the first character
I simplified the exclusion list by moving the common \b at the end of each fragment to just outside of a new, inner non-capturing group. This is a non-functional difference. It just makes it easier to read and deal with.

New output:

StartPos    EndPos    Original      TitleCased
--------    ------    --------      ----------
1           10        santacroce    Santacroce
17          20        arno          Arno
22          22        o             O
24          31        sullivan      Sullivan
38          45        sUlLiVan      Sullivan
47          57        gsantacroce   Gsantacroce

Fixed
-------------------------
Santacroce sull'Arno O'Sullivan suLL Sullivan Gsantacroce

UPDATE 2:

If the desire is to have a list of exclude words that is updateable without needing to update the function containing the regular expression, that is fairly easy to accomplish by doing the following:

Create a table to hold the exclusion words:

CREATE TABLE #ExcludeWords ([Word] NVARCHAR(50) NOT NULL);

Populate / manage-over-time the words in that table:

INSERT INTO #ExcludeWords ([Word]) VALUES
   (N'di'), (N'in'), (N'sull'), (N'on'), (N'upon'), (N'von'), (N'uber'), (N'ueber');

In whatever code you have that does this data-cleansing, dynamically build the regular expression from the table of words to exclude:

DECLARE @Expression NVARCHAR(4000) = N'\b(?!(?:';

SELECT @Expression += ex.[Word] + N'|'
FROM   @ExcludeWords ex

SET @Expression = LEFT(@Expression, LEN(@Expression) - 1) + N')\b)\w+';

SELECT @Expression; -- JUST FOR DEBUG
--\b(?!(?:di|in|sull|on|upon|von|uber|ueber)\b)\w+

UPDATE 3:

Originally no sample data was provided, so both answers tested with a simple list of words, some of which were the exclusion words. But now some test cases have been provided and in testing with one of those, I found a few issues with my implementation:

I had forgotten to specify the "case-insensitive" option for the RegEx function
I was replacing all occurrences of the entire match within the original string. This works fine when no matches are substrings of other strings within the entire string. But when including "O'Sullivan", the "O" is a substring of a few items and hence produced erroneous results.

So, I have adjusted the code and test case and results above to account for these issues. The main differences are:

Added the RegEx option of N'ignorecase'
Switched the REPLACE function with STUFF which allows me to use the starting and ending positions of each match to replace just that one item

PLEASE NOTE:

There is a minor discrepancy between the exclusion list in the question and the sample data provided in a comment on the question: question uses sul while the comment uses sull. I have adjusted my answer to work with the sull (two "L"s) shown in the test case provided in the comment.
This is reason number 5,235,948,567 for why it is critically important to have and/or provide actual test data ;-).


Partitioned mariadb table files(the .ibd ones) placed outside of data directory return back to data directory itself after altering the table in use

That's a problem with ALTER.  File a bug report if you like.

I consider RAID striping to be better than manually placing pieces of the database across physical volumes.

What kind of ALTER are you doing?  Adding an index is different than adding a column is different than fiddling with the partition structure.  There may be a workaround.


Your ORDER BY clause is on:

engagement(social) DESC NULLS LAST, "users"."created_at" ASC

But I suspect your index is just on:

engagement(social) DESC NULLS LAST

So the index is not capable of fully supporting the ORDER BY.

You can reproduce the same issue without using either JSONB or expression indexes.  You might be able to salvage the situation by creating a composite expression index over both columns in your ORDER BY.

If the PostgreSQL planner were infinitely wise, it might be able to use the existing index efficiently.  It would have to march up engagement(social) DESC NULLS LAST until it collected 12 tuples which met all the rest of the filter requirements.  Then it would have continue marching up that index until it collected all the rest of the tuples which were tied on engagement(social) with the 12th tuple (and which met the other criteria).  Then it would have to re-sort all of the collected tuples on the full ORDER BY, and apply the LIMIT 12 to that expanded and re-sorted set.  But the PostgreSQL planner is not infinitely wise.


Can mongoDB split read and write, with primary on HDD, replica sets on SSD?

You can send rs.stepDown(300) to the replicas on the SSD disks that you want as Secondary. Then they won't get chosen for the next 5 minutes. The replica on the HDD should be Primary when all the others step down.

Also make sure that you can read from your secondaries with rs.slaveOK().

In case that your primary has problems, one of your secondaries will take over. (Unless if you really don't want that, you can use {priority:0} in the member options.) Be aware that your script with these tasks, should only execute when your 'previous primary' is synced correctly again, before you return to your preferred environment. This could be automated as a cronjob or sometimes better manually if you need to figure out what happened with the Primary.

To optimize read/write:

Howto Benchmark and test limits
Split different database files by task : journal/log/data


Resume job 'A' at some step till job 'B' is completed

You are using jobs incorrectly, the steps from job B should simply be in job A. Use stored procedures to group and reuse common code not jobs.

However it is possible to do this, in which case this is a duplicate of Good way to call multiple SQL Server Agent jobs sequentially from one main job?


Questionable duplicate index suggested by optimization

The index suggested by the system is a much better fit for the query you have shown. You should aim to have columns with equality predicates as the leading columns.

Consider a phone book ordered by lastname, firstname. If your rquirement is to find all people with surnames between "Brown" and "Yates" and a first name of "John" then you need to read most of the phone book. If the phonebook was instead ordered by firstname, lastname you could easily find the "John" section and the first "Brown" in the section then all you need to do is read all the names until the lastname is after "Yates" or a new firstname is encountered.

It might not be the ideal index. Potentially you should just change the key columns in the clustered index to this order rather than creating a new one though. You need to evaluate this based on knowledge of your workload.


Okay, so here is the query modified to work the way you want:

DECLARE @players table
(
    PlayerID uniqueidentifier NOT NULL PRIMARY KEY,
    PlayerName nvarchar(64) NOT NULL
);

DECLARE @playerScores table
(
    ID bigint NOT NULL IDENTITY PRIMARY KEY,
    PlayerID uniqueidentifier NOT NULL,
    DateCreated datetime NOT NULL,
    Score int NOT NULL,
    TimeTaken bigint NOT NULL,
    PuzzleID int NOT NULL
);

DECLARE @puzzleId int = 0;

SELECT TOP 50
    a.PlayerID,
    p.PlayerName,
    a.Score,
    a.TimeTaken,
    a.PlayedDate
    FROM
    (
        SELECT
            ps.PlayerID,
            ps.Score,
            ps.TimeTaken,
            ps.DateCreated AS PlayedDate,
            ROW_NUMBER()
                OVER
                (
                    PARTITION BY ps.PlayerID
                    ORDER BY ps.Score DESC, ps.TimeTaken, ps.DateCreated
                ) AS RN
            FROM @playerScores ps
            WHERE ps.PuzzleID = @puzzleId
    ) a
    INNER JOIN @players p ON p.PlayerID = a.PlayerID
    WHERE a.RN = 1
    ORDER BY
        a.Score DESC,
        a.TimeTaken,
        a.PlayedDate;

Having written this (note: indexes are not optimized), and looking at the other queries you're going to need to write, what I would actually recommend is to abandon this type of query entirely, and create a denormalized high-score table (rows are unique on the combination of PlayerID, PuzzleID), on which to run aggregates instead.

The reason why is because the GameResult table is going to grow huge in the database, and so it will be less and less efficient to run aggregates on it directly as time passes, and the requirements are incompatible with doing something like creating an indexed view to summarize the information.

Also, if you aren't doing this already, it's highly likely you'll want to use an asynchronous process to compute the "leaderboards" periodically and cache the results, instead of computing them just-in-time. (You could do something like merge the current player's score with the cached leaderboards so the player can see themself on the leaderboards immediately if they got a high score.) See my answer here for some ideas to consider when implementing a caching mechanism.


When to use NULL and when to use an empty string?

Let's say that the record comes from a form to gather name and address information. Line 2 of the address will typically be blank if the user doesn't live in apartment. An empty string in this case is perfectly valid. I tend to prefer to use NULL to mean that the value is unknown or not given.

I don't believe the physical storage difference is worth worrying about in practice. As database administrators, we have much bigger fish to fry!


WHERE statement returning out of bounds returns

First and foremost:
Change your where clause and put all OR's inside a single parenthesis.
SELECT
    lead_id, status, owner
FROM
    leads
WHERE
    owner = 6585993
    AND ((`status` = 1) OR (`status` = 21) OR (`status` = 3))
ORDER BY
    CASE `status`
        WHEN 1 THEN 0
        WHEN 21 THEN 1
        WHEN 3 THEN 2
    END
LIMIT 1
;

Then, if you prefer and order, set and ORDER BY clause using a case to assign the new values, 1 = First (0), 21 = Second (1) and 3 = Last (2).

lead_id | status |   owner
------: | -----: | ------:
  20329 |      1 | 6585993

db<>fiddle here


INSERT performance degradation in SQL Server after a certain number of columns

The difference between the repro that you have posted with 10 columns and 100 rows and 11 columns and 100 rows is that the execution plan for the first one uses Simple Parameterization.

The actual execution plan for the 10 columns lists parameters from @1 to @1000.

11 * 100 is 1100. But one thousand seems to be the maximum number of parameters an auto parameterised query can reach.

You are doing 10 inserts for each. In the 10 column case the plan can be compiled once and reused for the other 9 inserts. In the 11 column case each insert statement needs to be compiled individually.

Moreover the process of compilation takes longer when SQL Server has the literal values to look at as it spends time working out properties of the group (or at least this used to be the case I'm not sure if this has changed in more recent versions).


Best option to back up mysql innodb partition

The Percona xtrabackup solution that you linked in your question is a good solution. It is a hot backup and doesn't lock innodb tables. I would prefer that to mysqldump in most cases.

Did you try xtrabackup? Have questions about using it?


It's not possible to run aggregate function over an expression involving a window function in the same query level, because aggregate functions are evaluated before window functions. You'd need a subquery like:

SELECT avg(delta_previous) AS avg_delta_previous
FROM (
   SELECT "RecognitionTimestamp"
        - lag("RecognitionTimestamp") OVER(ORDER BY "RecognitionTimestamp") AS delta_previous,
   FROM   "Detection"
   WHERE  "RecognitionTimestamp" >= '2018-10-03 09:01:00'
   AND    "RecognitionTimestamp" <  '2018-10-04 09:00:00'  -- typically, you'd want "<" here, not "<="!
   ) sub;

Consider the sequence of events in a SELECT query:

Best way to get result count before LIMIT was applied

But this simpler query should give you the same result, only much cheaper:

SELECT (max("RecognitionTimestamp")
      - min("RecognitionTimestamp")) / (count(*) - 1) AS avg_delta_previous
FROM   "Detection"
WHERE  "RecognitionTimestamp" >= '2018-10-03 09:01:00'
AND    "RecognitionTimestamp" <  '2018-10-04 09:00:00';

Subtract the minimum from the maximum timestamp to get the total interval and divide it by the number or rows - 1. Voilá.


You may use the pageinspect module to get at the raw pages. The column contents are not blanked by the DROP COLUMN, they're still there.

For each data page, select * from heap_page_items(get_raw_page('tablename', pageno)) will give the control information for each row, especially the start offset of the row.

To understand the structure of each row, you want to study the Database Page Layout in the doc. Offsets of columns are not found on disk, you get at a specific column by skipping over the columns before it. If there is only one column with a fixed size like in your case, it's not too complicated to retrieve contents page by page, row by row.

Now if the table had UPDATEs or DELETEs, the pages will contain a mix of live and old contents, also called dead rows.

And if some of the text contents were large enough to be TOASTed (about 2000 bytes), which means stored indirectly out of the main page in a compressed form, they're going to be visually unreadable and quite harder to extract. That part seems unfeasible to me without writing some C code interfaced with PostgreSQL.


How to create a Job for Merge Replication?

When you create your replication, it will automatically create a job with all of the steps needed for replication if you use the GUI.

Step 1: Merge Agent Startup Message. Type T-SQL ran on the distribution database

Step 2: Run Agent. Type Replication Merge. The command will be something like:
-Publisher [] -PublisherDB [] -Publication [] -Subscriber [] -SubscriberDB []   -Distributor [] -DistributorSecurityMode 1

Step 3: Detect nonlogged agent shutdown. Type T-SQL sp_MSDetect_nonlogged_shutdown @ subsystem='Merge', @agentid=


Could a busy database affect others on the same server?

Will a busy database affect others? In general, yes. Within a single instance all databases share buffer pool, worker threads, plan cache and other system resource. There is a single TempDB for the instance to which all queries write (or spill) when needed.

Installing multiple instances on one machine may not prevent this. Likely they will still share CPU cores, network cards and (perhaps) disk spindles. A very busy DB on one instance could adversely affect a DB on another instance that shares these types of resources. Putting the instances in VMs does not avoid this conflict.

It is possible to configure each instances to have dedicated CPU, memory, disk, network cards etc. Then there will be little or no impact of one on the other. By then you have, I would argue, two independent machines residing inside a single case.

So your question about a blocking query comes down to system configuration. If a query in one DB blocks, and it does so while holding lots of resource - say an exceedingly large temp table or very many rows in an open transaction - and that resource is shared with another DB - then another query in a separate DB may not be able to acquire the resource (disk, memory etc) it requires and will fail.

The shared and exclusive locks taken in the normal course of DML, that are active in one DB will not affect what reads and writes can occur in the other DB (in the absence of cross-database queries).


SQL server changes XML structure when inserted

You can use xml:space = "preserve" on the nodes where you want to keep the space. Using xml:space is "only a signal of intent" but SQL server is kind to us here.

For one node

declare @X xml =
'<root>
  <element xml:space = "preserve"> </element>
  <element> </element>
</root>'

select @X;

Result:

<root>
  <element xml:space="preserve"> </element>
  <element />
</root>

Entire document:

declare @X xml =
'<root xml:space = "preserve">
  <element> </element>
  <element> </element>
</root>'

select @X;

Result:

<root xml:space="preserve">
  <element> </element>
  <element> </element>
</root>

Another options for the entire document is to use convert with style 1.

  Preserve insignificant white space. This style setting sets the
  default xml:space handling to match the behavior of
  xml:space="preserve".

declare @X xml = convert(xml,
'<root>
  <element> </element>
  <element> </element>
</root>', 1)

select @X;


Search and Replace in multiple MySQL databases

Not really.

Instead, you could do something like

SELECT CONCAT("UPDATE ", table_schema, ".", table_name,
                " SET foo = replace(foo, 'this', 'that');")
    FROM information_schema.tables
    WHERE ...

to generate the desired 50+ UPDATE statements.  Then copy and paste them into the mysql commandline tool.

(Or you could construct a shell script using that.  Or...)


How do I determine if a SSIS transform is non-blocking, partially blocking, or fully blocking?

An operation which is blocking must wait until all rows have been seen and handled before it can start populating buffers.

An operations which is partially-blocking writes data onto new buffers, which only get handled by the next operation once each buffer (typically just under 10,000 rows) is populated.

An operation which is non-blocking can have the subsequent operation working on the same buffer, as each row is handled.

But many texts will refer to 'partially-blocking' as 'non-blocking', as the behaviour is much closer to 'non-blocking' than 'blocking'.

You should be able to tell what's going on by watching the data flow run, and seeing when the numbers are increasing at each point.


There's no need for both relationships.

Generally speaking, your category table should have one row per category, not per fact table row.

It's OK if multiple EReport rows refer to the same category record, but having a category record per EReport record defeats the purpose of normalizing in the first place.


Unable to create stored procedure to access excel files in SQL Server?

The good news is it CAN be done.  The bad news is you are opening a security hole.

Create a new database for this SP.
Make the owner of the database a sysadmin.
Set the database to trustworthy
Create your SP in the database using EXECUTE AS OWNER
For the people you want to be able to run this grant ONLY the following permissions.

CONNECT (create them a user)
EXECUTE on the stored procedure.

The reason for moving it off to it's own database & dramatically restricting permissions there I hope is fairly obvious.  By setting things up this way anyone who can run the stored procedure is in effect a sysadmin.  Now it's restricted to the code in the SP.  But what happens if someone gets db_owner access?  They can now write SPs that can grant them sysadmin.  Note: db_owner is a simple example.  There are lower permissions that could cause problems also.


You can use a CTE to create a dataset for SchoolBranch that includes the contactId value for the parent record then simply INNER JOIN to the main dataset to get the data you're after.

WITH CTE AS (
  SELECT id, contactId, parentBranchId, NULL AS parentContactId
  FROM SchoolBranch
  WHERE parentBranchId IS NULL
  UNION ALL
  SELECT sb.id, sb.contactId, sb.parentBranchId, p.contactId AS parentContactId
  FROM SchoolBranch sb
  INNER JOIN CTE p ON p.id = sb.parentBranchId
)

SELECT sb.id AS branchId
  , c.id AS contactId
  , sb.parentBranchId
  , c.NonImportantColumn
FROM Teachers t
INNER JOIN Department d ON d.id = t.deptId
INNER JOIN Contact c ON c.id = d.contactId
INNER JOIN CTE sb ON (sb.contactId = c.id) OR (sb.parentContactId = c.id)
WHERE t.id = 2

You can see a working example on db<>fiddle.


how much free space do I need for db2 backup file?

The backup image contains only used extents, so it might be substantially smaller than the sum of tablespace sizes, depending on the tablespaces' types and their physical properties.

On the other hand, in DB2 10.5 the backup image will by default include log files that are modified during the backup, so depending on how active the database is during the backup you may need to include the log sizes into your calculation.

Finally, the backup image can be compressed. Compression ratio is impossible to estimate in advance.

As a result, you can only estimate the upper limit for free space required to store the backup image, which would be the sum total of the current tablespace sizes plus the size of included log files plus the backup image header.

See also this answer on Stackoverflow.


Depending on the version of Report Builder, you can only build from a single data source.  I'm pretty sure this is still the case with SQL Server 2008R2, although Report Builder 3 can consume data directly from a database without needing a report model, although you need to build data sets using the individual connections and I don't think you can group items from different data sets by a common value.

You can do this gouping with SSRS (see this article for an overview), although I can't see an obvious way to do it in RB3.  This article indicates that you can't nest items based on different data sets, so it's looking like common rollups aren't possible in RB3.


Patching FileShareWitness for AlwaysOn AG

Normally, rebooting the File Share Witness should be totally fine in this setup as long as both AG replicas are up and healthy when you patch the FSW server.  So you shouldn't need to remove anything from the WSFC settings to accomplish your plan.

I had a situation a while back where

the file share witness was down for maintenance
the PRIMARY and SECONDARY both switched to the RESOLVING state briefly (about 15 seconds)
then they both returned to their original roles

This was due to the File Share Witness resource running in the same Resource Hosting Subsystem (RHS) process as the AG node resources - when the FSW failed it's "are you up" checks, it caused the whole RHS process to fail, which resulted in the "blip" in the AG.

You can avoid this problem by setting the FSW resource to run in a separate RHS process in WSFC settings:

I blogged in detail about that here: Troubleshooting an AG Failure

If there is something else causing this problem in your case, you can still troubleshoot it using the steps outlined in my blog post:

look at the SQL Server error log
look at the WSFC cluster log
review the AlwaysOn_health extended event session

If you don't want to dig through every single one of these places, you can also use the Failover Detection Utility provided by Microsoft Support.


ERROR: insufficient columns in PRIMARY KEY constraint definition

Yes, this is required.  There is no concept of an index over all
partitions in PostgreSQL.  The requirement of having the partition key
a subset of the primary key allows us to implement primary keys by
just having individual unique indexes on each partition.
There's mention in section 5.10.2.3. "Unique constraints on
partitioned tables must include all the partition key columns. This
limitation exists because PostgreSQL can only enforce uniqueness in
each partition individually.
CREATE TABLE c_account_p
    (
        billing_account_guid character varying(40)  NOT NULL,
        ingestion_process_id bigint NOT NULL DEFAULT '-1'::integer,
        load_dttm timestamp(6) without time zone NOT NULL,
        ban integer NOT NULL,
        CONSTRAINT billing_account_pkey  PRIMARY KEY (billing_account_guid,ban,load_dttm)
    ) PARTITION by RANGE(load_dttm);


What does Error: 1204, Severity: 19, State: 4 mean?

Explanation:

This error occurs when there are not enough system locks to complete the current command. SQL Server then attempts to obtain a LOCK block to represent and control the desired lock. When dynamically configured, the lock limit is determined by the available memory. When statically configured, the lock limit is determined by the sp_configure setting.

Action:

you have two options earthier wait for sometime and re-execute the same command(s), or reconfigure locks value by executing the following commands

sp_configure locks, XXXXXX     where XXXXXX value higher than the original one
GO
RECONFIGURE WITH OVERRIDE
GO

Regarding troulbeshooting this error please refer to the log and execute the following command:

SELECT @@SPID AS 'ID', SYSTEM_USER AS 'Login Name', USER AS 'User Name';

This will show you the needed info, and you can check Activity Monitor too

some useful links:
http://www.sqlservercentral.com/Forums/Topic141154-92-1.aspx
https://technet.microsoft.com/en-us/library/aa258769(v=sql.80).aspx


Well the reason for the above behaviour is due to the rocksdb_block_size set to 4kb. Due to smaller data blacks the compressor finds lesser amount of data to compress. Setting it to 16kb solved the issue. Now I get the similar data size as of cassandra.


Your indexes won't be used because they won't narrow down the table scan.
As a matter of fact, you want the max time for each type, so even if type index were used as a first intention, you'd still have to fully scan the segment at this index to get the max time for a given type. As a result, MySQL would have scanned the whole table, as a sum of type-indexed segment scans.

Using (type, time) index would in theory give a direct access to the last (max) time, for each type sub-tree. But as far as I know, some older MySQL versions have trouble regarding index selection for this kind of queries.


yes this is normal, mysql can so handle multiple requests, like apache webserver or firfox which do the same.
actually 2 is quite low, , if the number of requests/connections increases so will the number of proce4sses(till max Connections is reached). Two means that you and a free process are at the moment  connected


How can I measure the number of IOPs for a certain query

This is trickier than it looks. I/O per second is a difficult thing to pin down. Just because the file system sees so many I/Os per second, doesn't mean that MySQL sees the same thing - there is such a thing as I/O buffering. I would look into MySQL 5.6 - in particular the performance schema (P_S)(Marc Alff wrote the P_S (*)). One SQL SELECT could read from multiple tables. One transaction could read and write to many tables. Are you using MyISAM? If you are using InnoDB, are you using one file per table (advisable IMHO) - otherwise iostat will just give a database wide figure. I would suggest that you read around this topic in some detail and be clear about what, exactly, it is you're measuring.

(*) this comment obviously depends on whether you have the choice to upgrade, but the server version is always important on this forum - the more those who are trying to help know about your system, the more help you can receive.


Unable to fully drop a database snapshot

We ended up renaming the .ss file while the service was down. When it came back up again we could create a new snapshot. It's not ideal that services had to be down for us to resolve this, but it's better than nothing.


How to add a column with computed columns with values of row on Sybase IQ

Page Considerations When Using the CREATE TABLE Statement from the Sybase IQ 16 online manual:

Computed Columns
Computed column support differs as follows:

SAP SQL Anywhere supports computed columns that can be indexed.

Adaptive Server Enterprise and SAP Sybase IQ do not.

It seems to support views though, so you could define a view that contains the columns of the table plus the function, as another column (the column values will not be materialized this way, only computed when needed.)


How to grant permission to new user created in mysql?

It would be something like this :

GRANT SELECT, INSERT, DELETE ON database TO username@'localhost' IDENTIFIED BY 'password';

To see a list of the privileges that have been granted to a specific user:

select * from mysql.user where User='username';

OR

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';


How to use explode mysql function in where condition with cakephp

by Unique constraint on your payment_method field, you can make sure that payment_method is unique. If the peyment_method is already exists, it will throw a SQL Error.

  Duplicate entry 'x' for key 'peyment_method'

The SQL for that would be:

ALTER TABLE YourTableName ADD UNIQUE (payment_method)

But if you mean you need to make sure this payment_method exists and the payment_method_id is in another table, you need foreign key.

ALTER TABLE YourTable ADD FOREIGN KEY `methodidCheck`(payment_method)
REFERENCES payment_method_table(payment_method_id);

Hope I got your question right


Best practices for providing PHI/PII data to users in organization

My first choice would be to use the traditional security mechanisms, which has been in place for decades. I.e., only grant permissions on what they should see.
If they are about to write their own queries, they might be annoyed if you grant some columns but not all (1), since SELECT * will choke on that. And, yes, some will do SELECT *, whatever we say. So, (3) gets my vote.
You might run into the Dynamic Data Masking track, and this might be useful if they aren't writing their own queries. Since you say that security is #1 priority, then you probably aren't OK with them being able to deduce info. Basically, the data "is there" through the WHERE clause but not SELECT list. I recently read an article where CROSS APPLY was used as a type of brute force data attack to get the SSN even though it was masked.


ENABLE/DISABLE KEYS lag: how to optimize?

There are some rare occasions when mysqld processes ALTER TABLE ... ENABLE KEYS; and you get Repair by keycache. The real question to ask is Under what circumstances does mysqld perform Repair by keycache ?

According to MySQL Documentation on General Thread States

  Repair with keycache

  The repair code is using creating keys one by one through the key cache. This is much slower than Repair by sorting.

There are one or more server variables in the session that could potentially affect this behavior

sort_buffer_size

Independent of Storage Engine
The default is 256K
If this is set to 2M and mysqld needs more that 2M to process a sort operation, the session dumps the 2M it has in the sort buffer to a temporary MyISAM table, then continue processing from there.

innodb_sort_buffer_size

Default: 1M, MinValue: 64K, MaxValue: 64M
Documentation says three buffers of this size are allocated for an ALTER TABLE or CREATE TABLE statement that creates an index.

myisam_sort_buffer_size

Default value is 8M.
Definition : The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
If the sort operation needs more than 8M, there is where mysqld will make the session stop doing Repair by sorting and switch to Repair by keycache.

SUGGESTION

Based on the MySQL Documentation on sort_buffer_size

  As of MySQL 5.6.4, the optimizer tries to work out how much space is needed but can allocate more, up to the limit. Before MySQL 5.6.4, the optimizer allocates the entire buffer even if it is not all needed. In either case, setting it larger than required globally will slow down most queries that sort. It is best to increase it as a session setting, and only for the sessions that need a larger size. On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values. Experiment to find the best value for your workload. See Section B.5.4.4, “Where MySQL Stores Temporary Files”.

Only sort_buffer_size and myisam_sort_buffer_size can be changed for the session.

If you don't have a lot of memory, change myisam_sort_buffer_size so that a larger temp table can be formed. To set it to 1G in your session, do one of the following before executing a large reindexing:

SET myisam_sort_buffer_size = 1024 * 1024 * 1024;

or

SET SESSION myisam_sort_buffer_size = 1024 * 1024 * 1024;

That way, the temp table on disk will be allowed to grow to 1G. Make sure the folder mapped to tmpdir can hold large temp tables.

If you want 1G as the permanent setting for myisam_sort_buffer_size going forward, do this

STEP #1 : Add this to /etc/my.cnf

[mysqld]
myisam_sort_buffer_size=1G

This will allow connections to have this setting after mysqld is restarted.

STEP #2 : Login to mysql as root@localhost and run

SET GLOBAL myisam_sort_buffer_size = 1024 * 1024 * 1024;

This will enabled new connections to have this setting. Current connections would have to be closed and reopened (perhaps restarting apache/tomcat/Hibernate or whatever web server you are using).

MY OTHER POSTS ON Repair by keycache

May 09, 2012 : ENABLE KEYS on a large table is crashing mysql
May 17, 2013 : MySQL Hanging Completely when `ALTER TABLE... ENABLE KEYS`
Aug 12, 2013 : MySQL MyISAM index causes query to match no rows; indexes disabled, rows match


MySQL: Indexing by column which is also a part of a compound primary keys and

visits for a page for a period of time:  INDEX(page_id, hour)

visits for a period of time:  INDEX(hour)

Your primary key, plus these, are not redundant; have them all.

Recommend you keep sum_time, not avg_time, then compute the avg as SUM(sum_time) / SUM(visits).  (The average of averages is not mathematically correct.)


I would recommend you read the expression you have written very carefully because your logic is flawed and that is why your SWITCH statement is not working as you believe it should.

=SWITCH
    (Fields!Matl_.Value > 89.9, "RED",
        Fields!IHDAR_AMT1.Value < 75 and Fields!Matl_.Value > 20, "ORANGE",
        Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150 and Fields!Matl_.Value > 30, "ORANGE",
        Fields!IHDAR_AMT1.Value < 151 and Fields!IHDAR_AMT1.Value > 250 and Fields!Matl_.Value > 40, "ORANGE",
        Fields!IHDAR_AMT1.Value < 226 and Fields!IHDAR_AMT1.Value > 375 and Fields!Matl_.Value > 50, "ORANGE",
        Fields!IHDAR_AMT1.Value < 376 and Fields!IHDAR_AMT1.Value > 625 and Fields!Matl_.Value > 60, "ORANGE",
        Fields!IHDAR_AMT1.Value < 626 and Fields!IHDAR_AMT1.Value > 850 and Fields!Matl_.Value > 67.5, "ORANGE",
        Fields!IHDAR_AMT1.Value < 851 and Fields!IHDAR_AMT1.Value > 1500 and Fields!Matl_.Value > 7500, "ORANGE",
        Fields!IHDAR_AMT1.Value < 1501 and Fields!IHDAR_AMT1.Value > 2500 and Fields!Matl_.Value > 80, "ORANGE",
        Fields!IHDAR_AMT1.Value < 3501 and Fields!IHDAR_AMT1.Value > 4500 and Fields!Matl_.Value > 84, "ORANGE",
        Fields!IHDAR_AMT1.Value < 4501 and Fields!IHDAR_AMT1.Value > 5000 and Fields!Matl_.Value > 85, "ORANGE"
    )

When you format the code, it's easy to see you have written clauses like:

Fields!IHDAR_AMT1.Value < 76 and Fields!IHDAR_AMT1.Value > 150

The value of IHDAR_AMT1.Value cannot be both less than 76 and greater than 150, so it immediately evaluates to FALSE.


How to filter out the records with the latest date?

It worked I found 2 bugs:

Line 5:

  $wpdb->task_actions.datetime = $wpdb->mostrecent.datetime");
  Had to be: task_actions.created_taskaction instead of task_actions.datetime

Also the $wpdb-> sometimes doesn't work properly, so I changed all of them with wp_


sliding window partitioning on date field

Starting from the fact that I wouldn't partition on a column which is not used in a where clause, yes, your clustered index will have to change to include the log_date into its key columns, and yes, you can still query it filtering on ID only. I'd still suggest to include the partitioning column into your where clause to leverage partition elimination.

It's explained to much detail here: https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-2017


Adding as friend is not a commutative operation. When A add B as friend that doesn't means that B do the same for A. That is why the mutuality emerge.

You definitely need both rows:

id   user1_id  user2_idz
1    123       456
2    456       123

If you want to get mutual relationships from that table, you have to JOIN it with itself:

SELECT w.*
  FROM friends AS w
  JOIN friends AS z ON z.user2_id = w.user1_id
                   AND z.user1_id = w.user2_id
                   AND w.user1_id < w.user2_id