Aggregation by positive and negative values I have a table called "PROD_COST" with 5 columns:

+----+----------+------+-----------+-------------+
| ID | Duration | Cost | COST_NEXT | COST_CHANGE |
+----+----------+------+-----------+-------------+

Duration = number of days the price is valid (1 day=1row).
Cost = product price in this day.
-Cost_next = lead(cost,1,0).
Cost_change = Cost_next - Cost.

I need an extra column called "Groups" for aggregation:

+----+----------+------+-----------+-------------+--------+
| ID | Duration | Cost | Next_Cost | Cost_change | Groups |
+----+----------+------+-----------+-------------+--------+
|  1 |        1 | 10   | 8,5       | -1,5        |      1 |
|  2 |        1 | 8,5  | 12,2      | 3,7         |      2 |
|  3 |        1 | 12,2 | 5,3       | -6,9        |      3 |
|  4 |        1 | 5,3  | 4,2       | 1,2         |      4 |
|  5 |        1 | 4,2  | 6,2       | 2           |      4 |
|  6 |        1 | 6,2  | 9,2       | 3           |      4 |
|  7 |        1 | 9,2  | 7,5       | -2,7        |      5 |
|  8 |        1 | 7,5  | 6,2       | -1,3        |      5 |
|  9 |        1 | 6,2  | 6,3       | 0,1         |      6 |
| 10 |        1 | 6,3  | 7,2       | 0,9         |      6 |
| 11 |        1 | 7,2  | 7,5       | 0,3         |      6 |
| 12 |        1 | 7,5  | 0         | 7,5         |      6 |
+----+----------+------+-----------+-------------+--------+

Now I need to group the "Groups" column by Cost_change. It can be positive,negative or 0 values. I was advised to use this code:

select
  id, COST_CHANGE, sum(GRP) over (order by id asc) +1
from
(
  select
    *,
    case when sign(COST_CHANGE) != sign(isnull(lag(COST_CHANGE)
      over (order by id asc),COST_CHANGE)) and Cost_change!=0 then 1 else 0 end as GRP
  from
    PROD_COST
) X

But there is a problem: If there are 0 values between two positive or negative values than it groups it separately, for example:

+-------------+--------+
| Cost_change | Groups |
+-------------+--------+
| 9.262       |   5777 |
| -9.262      |   5778 |
| 9.262       |   5779 |
| 0.000       |   5779 |
| 9.608       |   5780 |
| -11.231     |   5781 |
| 10.000      |   5782 |
+-------------+--------+

I need to have:

+-------------+--------+
| Cost_change | Groups |
+-------------+--------+
| 9.262       |   5777 |
| -9.262      |   5778 |
| 9.262       |   5779 |
| 0.000       |   5779 |
| 9.608       |   5779 | -- Here
| -11.231     |   5780 |
| 10.000      |   5781 |
+-------------+--------+

I would be very grateful for any help.

I use your code and added some checks, regarding the COST_CHANGE = 0

For Sql Server 2012, you can use the LAG (this is not available in 2008,2008R2)

--my source table
DECLARE @tPROD_COST TABLE
(ID INT /*IDENTITY(1,1)*/,
 Duration INT,
 Cost FLOAT,
 COST_NEXT FLOAT,
 COST_CHANGE FLOAT)

 INSERT INTO @tPROD_COST(ID,Duration,Cost,COST_NEXT,COST_CHANGE)
 VALUES
(  1 , 1 , 10   , 8.5  , -1.5  ),
(  2 , 1 , 8.5  , 12.2 , 3.7   ),
(  3 , 1 , 12.2 , 5.3  , -6.9  ),
(  4 , 1 , 5.3  , 4.2  , 1.2   ),
(  5 , 1 , 4.2  , 6.2  , 2     ),
(  6 , 1 , 6.2  , 9.2  , 3     ),
(  7 , 1 , 9.2  , 7.5  , -2.7  ),
(  8 , 1 , 7.5  , 6.2  , -1.3  ),
(  9 , 1 , 6.2  , 6.3  , 0.1   ),
(  10 , 1 , 6.3  , 7.2  , 0.9  ),
(  11 , 1 , 7.2  , 7.5  , 0.3  ),
(  12 , 1 , 7.5  , 0    , 7.5  )
,(13,1,10,19.262,9.262)
,(14,1,9.262,9.262,0)
,(15,1,9.262,18.870 , 9.608)
,(16,1,18.870,18.870 , 0)
,(17,1,18.870,18.870 , 0)
,(18,1,18.870, 18.000, -0.870)
,(19,1,18.000, 18.000, 0)
,(20,1,18.000, 17.000, -1.00)

--for SQL 2012

SELECT ID,
      Duration,
      Cost,
      COST_NEXT,
      COST_CHANGE,
      SUM(GRP) OVER (ORDER BY ID ASC) +1 AS Groups
FROM (
    SELECT *,
        CASE
            WHEN SIGN(COST_CHANGE)!= SIGN(ISNULL(LAG(COST_CHANGE) OVER (ORDER BY ID ASC),COST_CHANGE))
                 AND ISNULL(LAG(COST_CHANGE) OVER (ORDER BY ID ASC),COST_CHANGE) <> 0
                 AND Cost_change != 0
            THEN 1
            WHEN SIGN(COST_CHANGE)  <> 0
                AND SIGN(COST_CHANGE) <> SIGN(ISNULL(OA2.Prev_Cost_Change_NonZero,COST_CHANGE))
            THEN 1
            ELSE 0 END AS GRP
    FROM @tPROD_COST AS PC
    OUTER APPLY  -- get the previous COST CHANGE <> 0
    (
        SELECT TOP(1)
            PC3.COST_CHANGE AS Prev_Cost_Change_NonZero
         FROM
            @tPROD_COST AS PC3
         WHERE
            PC.ID>PC3.ID
            AND PC3.COST_CHANGE <> 0
        ORDER BY PC3.ID DESC
    )OA2(Prev_Cost_Change_NonZero)
    ) X

--for SQL 2008,2008R2:

;WITH cte
AS( SELECT ID,
          Duration,
          Cost,
          COST_NEXT,
          COST_CHANGE,
          GRP
    FROM
       ( SELECT ID, Duration, Cost, COST_NEXT, COST_CHANGE,
            CASE
                WHEN SIGN(COST_CHANGE)!= SIGN(ISNULL(OA.Prev_Cost_Change,COST_CHANGE))
                     AND ISNULL(OA.Prev_Cost_Change,COST_CHANGE) <> 0
                     AND Cost_change != 0
                THEN 1
                WHEN SIGN(COST_CHANGE)  <> 0
                    AND SIGN(COST_CHANGE) <> SIGN(ISNULL(OA2.Prev_Cost_Change_NonZero,COST_CHANGE))
                THEN 1
                ELSE 0 END AS GRP ,
                OA.Prev_Cost_Change,
                OA2.Prev_Cost_Change_NonZero
        FROM
            @tPROD_COST AS PC
        OUTER APPLY  -- get the previous COST CHANGE
        (
            SELECT TOP(1)
                PC2.COST_CHANGE AS Prev_Cost_Change
             FROM
                @tPROD_COST AS PC2
             WHERE
                PC.ID>PC2.ID
            ORDER BY PC2.ID DESC
        )OA(Prev_Cost_Change)
        OUTER APPLY  -- get the previous COST CHANGE <> 0
        (
            SELECT TOP(1)
                PC3.COST_CHANGE AS Prev_Cost_Change_NonZero
             FROM
                @tPROD_COST AS PC3
             WHERE
                PC.ID>PC3.ID
                AND PC3.COST_CHANGE <> 0
            ORDER BY PC3.ID DESC
        )OA2(Prev_Cost_Change_NonZero)
    )A
)

    SELECT ID,
          Duration,
          Cost,
          COST_NEXT,
          COST_CHANGE,
          --GRP,
          ISNULL(OA.Prev_Sum_Grp,0) +1 AS Groups
    FROM
        cte AS C
    OUTER APPLY  -- get the previous SUM
    (
        SELECT
            SUM(C2.GRP) AS Sum_GRP
         FROM
            cte AS C2
         WHERE
            C.ID>=C2.ID
    )OA(Prev_Sum_GRP)

The output:

ID          Duration    Cost                   COST_NEXT              COST_CHANGE            Groups
----------- ----------- ---------------------- ---------------------- ---------------------- -----------
1           1           10                     8.5                    -1.5                   1
2           1           8.5                    12.2                   3.7                    2
3           1           12.2                   5.3                    -6.9                   3
4           1           5.3                    4.2                    1.2                    4
5           1           4.2                    6.2                    2                      4
6           1           6.2                    9.2                    3                      4
7           1           9.2                    7.5                    -2.7                   5
8           1           7.5                    6.2                    -1.3                   5
9           1           6.2                    6.3                    0.1                    6
10          1           6.3                    7.2                    0.9                    6
11          1           7.2                    7.5                    0.3                    6
12          1           7.5                    0                      7.5                    6
13          1           10                     19.262                 9.262                  6
14          1           9.262                  9.262                  0                      6
15          1           9.262                  18.87                  9.608                  6
16          1           18.87                  18.87                  0                      6
17          1           18.87                  18.87                  0                      6
18          1           18.87                  18                     -0.87                  7
19          1           18                     18                     0                      7
20          1           18                     17                     -1                     7


Query to compare 2 table results and filter extra records found in a table I have two queries that I want to compare and get the extra rows found in in the first query that are not in the second plus 1. I need the DocEntryCN to perform some work.

E.g.
Query 1

SELECT DocEntry AS "DocEntryCN" , Comments, JrnlMemo, DocTotal
FROM ORIN WHERE DocDate BETWEEN '10-27-2012' AND '10-29-2012' AND Comments IS NOT NULL
ORDER BY DocTotal ASC, Comments

Query 2

SELECT DocEntry AS "DocEntryINV" , Comments, DocTotal
FROM OINV WHERE DocDate BETWEEN '10-27-2012' AND '10-29-2012' AND Comments IS NOT NULL
ORDER BY DocTotal ASC, Comments

Sample Results:
Query 1

DocEntryCN    Comments    JrnlMemo      DocTotal
67            reverse     kaka-19       500
69            reverse     kaka-19       500
70            reverse     kaka-19       500
71            reverse     kaka-19       500
74            reverse     kaka-19       500
75            reverse     Rob-23        750
77            reverse     Rob-23        750
78            reverse     Rob-23        750
79            reverse     Rob-23        750
84            reverse     Paul-17       1250
86            reverse     Paul-17       1250
87            reverse     Paul-17       1250

Sample Results: Query 2

DocEntryINV   Comments       DocTotal
1             kaka-19        500
3             kaka-19        500
5             kaka-19        500
6             kaka-19        500
7             Rob-23         750
9             Rob-23         750
10            Alex-09        1000
11            Olma-08        1150
12            Paul-17        1250
13            Paul-17        1250
16            Paul-17        1250
17            Rita-99        1300
19            Rita-99        1300

Desired Results

DocEntryCN    Comments    JrnlMemo      DocTotal
67            reverse     kaka-19       500
69            reverse     kaka-19       500
75            reverse     Rob-23        750
77            reverse     Rob-23        750
78            reverse     Rob-23        750
84            reverse     Paul-17       1250
90            reverse     Rita-99       1300

In the first query, there are 5 duplicates for "kaka-19". In the second query, there are 4 duplicates for the same "kaka-19". I would like my results be as above where (5-4)+1 = 2 for that "kaka-19" and so forth.

NB. Comments in query 2 stores Invoice numbers that should be unique. If any two comments in the OINV table are the same, it means there is a duplicate error. The JrnlMemo field in the ORIN table stores the OINV invoice numbers. For any invoice repetitions no. k, the correct number of credit notes to reverse should be k - 1.

How do I build a query for the desired results?

For my testing, I just created a couple of temporary tables (#CN for ORIN and #INV for OINV).  You should be able to replace them with your above queries without too much difficulty.

The following query should be a relatively efficient query to get the results you want:

with cn as
(
    select
        *,
        ROW_NUMBER() over (partition by JrnlMemo order by DocEntryCN desc) as rownum
    from
        #CN
),
inv as
(
    select
        Comments,
        count(*) as rowstoget
    from
        #INV
    group by
        Comments
)
select
    cn.DocEntryCN,
    cn.Comments,
    cn.JrnlMemo,
    cn.DocTotal
from
    cn
    inner join inv on cn.JrnlMemo = inv.Comments
where
    cn.rownum >= inv.rowstoget
order by
    DocEntryCN;

Sample data used:

create table #CN
(
    DocEntryCN int,
    Comments varchar(20),
    JrnlMemo varchar(20),
    DocTotal int
);

insert into #CN(DocEntryCN, Comments, JrnlMemo, DocTotal) values
(67,            'reverse',     'kaka-19',       500),
(69,            'reverse',     'kaka-19',       500),
(70,            'reverse',     'kaka-19',       500),
(71,            'reverse',     'kaka-19',       500),
(74,            'reverse',     'kaka-19',       500),
(75,            'reverse',     'Rob-23',        750),
(77,            'reverse',     'Rob-23',        750),
(78,            'reverse',     'Rob-23',        750),
(79,            'reverse',     'Rob-23',        750),
(84,            'reverse',     'Paul-17',       1250),
(86,            'reverse',     'Paul-17',       1250),
(87,            'reverse',     'Paul-17',       1250)

create table #INV
(
    DocEntryINV int,
    Comments varchar(20),
    DocTotal int
);

insert into #INV(DocEntryINV, Comments, DocTotal) values
(1,             'kaka-19',        500),
(3,             'kaka-19',        500),
(5,             'kaka-19',        500),
(6,             'kaka-19',        500),
(7,             'Rob-23',        750),
(9,             'Rob-23',         750),
(10,            'Alex-09',        1000),
(11,            'Olma-08',        1150),
(12,            'Paul-17',        1250 ),
(13,            'Paul-17',        1250),
(16,            'Paul-17',        1250),
(17,            'Rita-99',        1300),
(19,            'Rita-99',        1300);

I should point out that the results will be slightly different from what you indicated in your Desired Results section because you have a Rita there with a DocEntryCN of 90, but no Rita entries in ORIN.  Otherwise, the results are the same.


Three WHERE slow query down I have a table  adjacency, which stores information about connected lines and their angle to each other is build by

CREATE TABLE public.adjacency
(
    currentedge integer,
    currentgeom geometry(LineString,25832),
    sourcenode integer,
    targetnode integer,
    nextedge integer,
    nextgeom geometry(LineString,25832),
    degrees double precision
)

Now, I want to select only Lines which have angles in specific intervals

DROP TABLE IF EXISTS restriction;
SELECT DISTINCT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
INTO restriction
FROM adjacency a ,  adjacency b
WHERE (a.sourcenode = b.sourcenode AND (a.degrees < 45.0 OR a.degrees > 315.0))
    OR (a.targetnode = b.targetnode AND (a.degrees < 45.0 OR a.degrees > 315.0))
    OR (a.targetnode = b.sourcenode AND (a.degrees BETWEEN 46.0 AND 224.0))
AND a.nextedge=b.currentedge

The execution of the last command seems endless. Can anyone explain to me what to change here? My execution plan:

Seq Scan on adjacency a  (cost=0.00..2574.30 rows=30630 width=40)

One thing that sometimes works is to rewrite a series of OR conditions into a sequence of UNIONs:

CREATE TABLE restriction
AS
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a
 JOIN adjacency b ON a.nextedge = b.currentedge
WHERE a.sourcenode = b.sourcenode AND (a.degrees < 45.0 OR a.degrees > 315.0)
UNION
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a
 JOIN adjacency b ON a.nextedge=b.currentedge
WHERE a.targetnode = b.targetnode AND (a.degrees < 45.0 OR a.degrees > 315.0)
UNION
SELECT '100' as to_cost, a.nextedge as target_id, a.currentedge as via_path
FROM adjacency a
 JOIN adjacency b ON a.nextedge=b.currentedge
WHERE a.targetnode = b.sourcenode AND (a.degrees BETWEEN 46.0 AND 224.0)
;

You don't need an additional distinct here, as UNION will take care of that. I also replace the ancient, outdated implicit join syntax with an explicit JOIN operator.

You can additionally try indexes on:

adjacency (nextedge, sourcenode)
adjacency (currentedge, sourcenode)
adjacency (nextedge, targetnode)
adjacency (currentedge, targetnode)


Get ID which has two rows with specific values for one column I have this Table

and would like to select only those IDs that I have as type A B only
The output table should be this table:

Might you be able to share an idea on how to approach this?

WITH cte AS ( SELECT id, type, COUNT(DISTINCT type) OVER (PARTITION BY id) cnt
              FROM table
              WHERE type IN ('A', 'B')
            )
SELECT id, type
FROM cte
WHERE cnt = 2


SSIS Update (Replace) record instead of append based on Primary Key I created an SSIS Pacakge that imports a monthly extract claims file. I have a simple Data Flow task, flat file to OLEDB Destination with a Derived column component in between used to generate a primary Key (based on a combination of 3 columns in the extract file).

What I would like to do if possible is have the SSIS package only import brand new records based on the primary key so that if there is a file that is being imported that contains a (generated) primary key that already exists in the destination table, then I would like the package to delete the existing record in the table and then add the new record from the current file being imported.

I know I could do this by having a staging table where I load the file and then using some sql queries (sp) to delete and add records to the table appropriately but I am trying to accomplish the import without needing to create a staging table because the table that I am currently populating, already is a staging table...

Is what I am trying to accomplish possible, using SSIS and only 1 destination table or will I need to create an additional staging table?

As Kin mentioned in his comment above, it is possible to accomplish what I was trying to do using the SSIS Lookup transformation component (example also provided in his comment) which answers my question.

While I will surely be using this component in the future I decided to go with a more straightforward SQL scripting method to accomplish what I needed after reading mskinner's comment (and referenced blog) which points out that using an Execute SQL task is probably going to be much faster/efficient than using the Lookup Transformation component in SSIS for what I am trying to accomplish.


How can we demo Lost update, Uncommitted data, Unrepeatable data, Phantom on MySQL I'm studying Database Management System and I have a project that I need to demo Lost update, Uncommitted data, Unrepeatable data, Phantom in a DMS I selected. I selected MySQL for my project. But after a couple of times trying to demonstrate I failed. This is a case I tried.

This is transaction T1

START TRANSACTION;
UPDATE roles SET roles.isAdmin = '0';

DO sleep(5);

ROLLBACK;

This is transaction T2

START TRANSACTION;

SELECT * FROM roles WHERE 1;

COMMIT;

 Can you help me to figure out how can I demonstrate Lost update, Uncommitted data, Unrepeatable data, Phantom situation?

The different problems are prevented at different isolation levels. So you need to first set an isolation level that allows a problem to happen before you can demonstrate it.

Here's a table that explains which isolation level allows which problem to occur (taken from my post over at stackoverflow which also briefly explains the problems the isolation levels are solving).

                 | Lost updates | Dirty Read | Non-Repeatable Read | Phantom Read
---------------------------------------------------------------------------------
Read Uncommitted | possible     | possible   | possible            | possible
Read Committed   | impossible   | impossible | possible            | possible
Repeatable Read  | impossible   | impossible | impossible          | possible
Serializable     | impossible   | impossible | impossible          | impossible

So, for the first example, the lost update problem, it would work like this:

session 1> start transaction;
Query OK, 0 rows affected (0.00 sec)

session1 > select * from t;
+------+------+
| a    | b    |
+------+------+
|    1 |    1 |
|    1 |    2 |
+------+------+
2 rows in set (0.00 sec)

session 1> update t set a = 2;
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

The session is not yet committed, but in session 2 you can already see the value, when you set the isolation accordingly.

session2 > set session tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

session2 > start transaction;
Query OK, 0 rows affected (0.00 sec)

session2 > select * from t;
+------+------+
| a    | b    |
+------+------+
|    2 |    1 |
|    2 |    2 |
+------+------+
2 rows in set (0.00 sec)

session2 > update t set a=3 where a=2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

As you can see the Changed value for session 2 is 0, because it read uncommitted values from session 1. When you commit both sessions now, no matter which one first, the values in column a will be 2. The update statement of session 2 got lost.

I think you get the idea, of how to demonstrate the problems. I got some work to do now. When you still have problems, I will update this post when I find the time.


Here is what you asked for, but I think you would want to further aggregate this result set by GROUP BY customer_id to roll up the joined table. If that is the case, then simply add GROUP BY o.customer_id.

SELECT
  o.increment_id,
  o.customer_id,
  b.LastOrderMonth,
  ROUND(o.base_grand_total * o.base_to_global_rate,2) as revGBP,
  DATE_FORMAT(o.created_at, '01/%m/%Y') as 'MonthofOrder',
  b.monthCustCreated,
  b.CountOfOrders
FROM
  sales_flat_order as o
LEFT JOIN
(
    SELECT
      customer_id,
      date_format(MAX(created_at), '01-%m-%Y') as 'LastOrderMonth',
      sum(round((base_grand_total * base_to_global_rate),2)) as 'Rev GBP',
      date_format(MIN(created_at), '01-%m-%Y')  as 'monthCustCreated',
      COUNT(increment_id) as 'CountOfOrders'
    FROM
      sales_flat_order
    WHERE
      status in ('complete', 'processing')
    AND
      customer_ID IS NOT NULL
    GROUP BY
      customer_ID
) b ON o.customer_id = b.customer_id
WHERE
  o.status in ('complete', 'processing')
AND
  o.customer_email IS NOT NULL;


Select statement to retrieve xml output as below Could someone provide me some clues or solution to retrieve record sets like below?

Note: I read the msdn documentation but leads me no where but hair loss :(

Just for the assumption think i have 2 tables connected via Rid field

Table 1 columns:

 Rid, UserName, Hash

Table 2 columns:

 Rid, Phone, City, Email

Table1 and Table2 are connected via the Rid column.

I would like to have a xml output using xml auto, or xml explicit or which ever xml operations you got in SQL Server 2005 Express.

Output expected:

<UserDetails>
    <Account>
        <UserName>
        </UserName>
        <Hash>
        </Hash>
    </Account>
    <Personal>
        <Phone>
        </Phone>
        <City>
        </City>
    </Personal>
</UserDetails>

@matt Please look into the procedure I created below. When you execute the stored procedure with code at start you will know the problem I face

stack_getusers  '<Request Type="GetUsers" CRUD="R">
                            <UserDetails>
                                <Rid></Rid>
                            </UserDetails>
                        </Request>'

CREATE PROCEDURE [dbo].[stack_getusers]
@doc NTEXT
AS
DECLARE @idoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
SELECT
    t1.UserName AS "Account/UserName",
    t1.Hash AS "Account/Hash",
    (
        SELECT t2.Phone AS "Personal/Phone",
               t2.City AS "Personal/City"
        FROM table1 t2
            INNER JOIN table2 t3 ON t2.rid = t3.rid WHERE t2.rid = t1.rid AND (xml.Rid = '' OR t1.rid = xml.Rid)
        FOR XML PATH('Personals')
    )
FROM table1 t1
    INNER JOIN table2 t2 ON t1.rid = t2.rid
OPENXML (@idoc,'/Request/Users',2)
WITH (Rid int) as xml
where (xml.Rid = '' OR t1.rid = xml.Rid)
FOR XML PATH ('UserDetails');
EXEC sp_xml_removedocument @idoc

Give this a try:

SELECT
    t1.UserName AS "Account/UserName",
    t1.Hash AS "Account/Hash",
    t2.Phone AS "Personal/Phone",
    t2.City AS "Personal/City"
FROM table1 t1

    INNER JOIN table2 t2
        ON t1.rid = t2.rid

FOR XML PATH ('UserDetails');

I have added a second code sample in response to your comment.  I have no idea if this will work, or if it is even the best way to accomplish what you want it to.  Please let me know.

SELECT
    t1.UserName AS "Account/UserName",
    t1.Hash AS "Account/Hash",
    (
        SELECT
            t2.Phone AS "Personal/Phone",
            t2.City AS "Personal/City"
        FROM table1 t2

            INNER JOIN table2 t3
                ON t2.rid = t3.rid

        WHERE t2.rid = t1.rid

        FOR XML PATH('Personals')
    )
FROM table1 t1

    INNER JOIN table2 t2
        ON t1.rid = t2.rid

FOR XML PATH ('UserDetails');


content types of insertion I have a CSV file that contains a lot of integer values. Is it better to insert these individual values in bulk as in a full transaction or should I be inserting the CSV file itself into the MySQL?

Insert the individual values in bulk. In other words, each row of the csv file should become a row in the database with the same number of columns.

Using Notepad++ you can use a regex find and replace to easily convert your CSV into a very large insert statement. Replace \r\n with '),(' and  , with ','. Tidy up the first and last line of the file, then add the actual insert statement to the front. insert into table (col1, col2, col3) values .....


Why doesn't pgAgent run my job every minute? I have a job and I set the schedule to run every minute.

However, it always skips the next minute after finishing the process, even when the process ends in less than one minute.

The problem is that data is still arriving during that minute so when the job starts, it has to process two minutes of data. I'd rather have two 15 seconds runs, than one long 30 seconds run.

How can I make pgAgent run every minute?

In other cases when I set the schedule to every 3 minutes, pgAgent skips the 4th minute and starts on the 5th.

pgAgent updates the pgagent.pga_job row for the job once the scheduled job completes.  pgAgent will then only run the job a minimum of 1 minute later.  The thread for jobs that are scheduled to run "every minute" looks for new jobs to run once per minute, at the start of each minute.  So, if your job completes at 13:01:26, the next time the schedule runs will be 13:02:00, however since the job completed 34 seconds ago, it does not run the job.  The next time the scheduler runs, at 13:03:00, it sees the job ran more than one minute ago, and runs the job.

The pgAgent source code for pgagent.pga_next_schedule notes this:

-- Get the time to find the next run after. It will just be the later of
-- now() + 1m and the start date for the time being, however, we might want to
-- do more complex things using this value in the future.
IF date_trunc(''MINUTE'', jscstart) > date_trunc(''MINUTE'', (now() + ''1 Minute''::interval)) THEN
    runafter := date_trunc(''MINUTE'', jscstart);
ELSE
    runafter := date_trunc(''MINUTE'', (now() + ''1 Minute''::interval));
END IF;


Find ID columns with null value in oracle database I need a way to iterate through all tables in tablespace "T_ECOS" and find all ID with null value in oracle database

Any suggestion?

You can do something like this to dynamically generate a query against the tables with a nullable ID column to see whether they actually contain nulls or not:

declare
  l_cnt pls_integer;
begin
  for tabs in (SELECT c.owner, c.table_name FROM dba_segments s, dba_tab_cols c
               where  s.segment_name = c.table_name
               and    c.column_name = 'ID'
               and    c.nullable = 'Y'
               and    s.tablespace_name = 'T_ECOS') loop
    execute immediate 'select count(*) from ' || tabs.owner || '.' || tabs.table_name ||
      ' where id is null and rownum = 1' -- to short-circuit the count as soon as a null entry is found
      into l_cnt;

    if l_cnt > 0 then
      dbms_output.put_line(tabs.table_name);
    end if;
  end loop;
end;
/


Exporting partial table records as INSERT script I have datatable with a large amount of data in it and I'm trying to export the data from it. I saved the file locally, but I cannot open it with Notepad++. The problem is that I just want to get some of the data as an INSERT script but I don't know if this is possible.

Can anyone tell me if it's possible to get some of the data from a table as an INSERT script using SQL Server 2014?

Here is one way to do what you want (taken from this Stack Overflow answer by KT.:

  Do a select [whatever you need]INTO temp.table_namefrom [... etc ...].

  Right-click on the database in the Object Explorer => Tasks => Generate Scripts
  Select temp.table_name in the "Choose Objects" screen, click Next.
  In the "Specify how scripts should be saved" screen:
  Click Advanced, find the "Types of data to Script" property, select "Data only", close the advanced properties.
  Select "Save to new query window" (unless you have thousands of records).
  Click Next, wait for the job to complete, observe the resulting INSERT statements appear in a new query window.
  Use Find & Replace to change all [temp.table_name] to [your_table_name].
  drop table [temp.table_name].

NOTE:  The above sample uses the schema name temp. You may have to create that schema name for the script to work for you.


Efficiencies and pitfalls of a dynamically updated "Uber" View to streamline future code We have a number of tables (about 40) with approximately 2500 columns between them that all have matching primary keys, but different unique columns.  For various procedures and views, we'll draw on any number of them - sometimes hundreds of them - to build the necessary results.

I've been considering creating a view that ties all these tables and columns together primarily for the sake of simplicity in referring to the tables and columns in code for subsequent procedures and views, but I wanted to check that I'm not making a major mistake in doing so.  Unfortunately, I don't directly have the ability to change the tables, although there are circumstances where the tables will have columns added or removed, so I need to account for that.

My first question is, I would expect for any subsequent query against the view that the SQL Server optimizer would exclude any tables or columns from the actual execution that aren't actually used in the result set, correct?

I would also need to create a stored procedure to update the definition of the view so that it adjusts the columns included in the view as they change, correct?  Are there any features or possible hang-ups I'm not considering here?

To summarize discussion on the topic, creating this sort of "Uber" view with several tables that have matching primary keys and similar (if not the same) row counts is not necessarily a bad idea for the purpose of streamlining code.

Additionally, any tables or columns not used in subsequent queries on such a view are dropped from the execution plan in most cases, so performance is most likely unharmed by unnecessary joins in the view definition.

However, how the view is used down the road is an important consideration to bear in mind and should be avoided in multi-layer view within view, or view within stored procedure calls.

One interesting little note I learned while testing this out is there is a View width limit of 1,024 columns in SQL Server, so my particular case is simply not possible to accomplish.


SQL Server 2008 R2 enterprise named instance I have sql server 2008 r2 installed and I am using 3 named instances (2 express and 1 developer). For a task I need to use the CDC (change data capture) which is only in the enterprise edition. I have downloaded the trial from Microsoft website.

Given that I'm wondering if this should be done same as installing a new named instance or is it an upgrade?

Microsoft support Multiple instances on single windows machine so it is fully supported when you want to install another version of SQL Server same machine having other versions. But have you made sure that windows server/machine would perfectly support all four instances. Is the hardware resource optimum for your testing won't you face hardware crunch if all 4 instances are required and they are up and running.

Plus why are you still stuck up with SQL Server 2008 R2 edition. The link you posted in your question is pointing to SQL Server 2008 so are you installing SQL server 2008 or 2008 R2. Both does not falls under category of normal support but yes falls under category of extended support, and that too not for very long and soon will become totally unsupported. I would advise you to do your testing at least on SQL Server 2012 Evaluation edition or SQL Server 2014

  Given that I'm wondering if this should be done same as installing a new named instance or is it an upgrade?

You can do a normal installation but before moving please read carefully hardware and software requirement for installing SQL Server 2008 R2. If you are installing SQL Server 2008 R2 on windows 8 you must read this article before proceeding


POSTGRESQL 12: pg_basebackup isn't copying postgresql.conf and pg_hba.conf from master to standby I am setting a master-slave architecture and have done the following steps thus far:

on master:

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'replicator';

theowl@postgresql:/etc/postgresql/12/main$ sudo vi pg_hba.conf
host    replication     replicator      192.168.56.8/24         md5

sudo systemctl restart  postgresql

on standby:

theowl@postgresql:/etc/postgresql/12/main$ sudo systemctl stop postgresql
postgres@postgresql:/etc/postgresql/12/main$ ls -ltr
total 0

pg_basebackup -h 192.168.56.7 -U replicator -p 5432 -D /etc/postgresql/12/main/ -Fp -Xs -P -R
postgres@postgresql:/etc/postgresql/12/main$ pg_basebackup -h 192.168.56.7 -U replicator -p 5432 -D /etc/postgresql/12/main/ -Fp -Xs -P -R
Password:
48423/48423 kB (100%), 1/1 tablespace
postgres@postgresql:/etc/postgresql/12/main$ ls -ltr
total 80
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_xact
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_twophase
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_stat
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_snapshots
drwx------ 4 postgres postgres 4096 Mar 12 16:35 pg_multixact
-rw------- 1 postgres postgres  224 Mar 12 16:35 backup_label
drwx------ 3 postgres postgres 4096 Mar 12 16:35 pg_wal
drwx------ 8 postgres postgres 4096 Mar 12 16:35 base
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_subtrans
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_replslot
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_notify
-rw------- 1 postgres postgres    3 Mar 12 16:35 PG_VERSION
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_tblspc
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_serial
drwx------ 4 postgres postgres 4096 Mar 12 16:35 pg_logical
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_dynshmem
drwx------ 2 postgres postgres 4096 Mar 12 16:35 pg_commit_ts
drwx------ 2 postgres postgres 4096 Mar 12 16:35 global
-rw------- 1 postgres postgres    0 Mar 12 16:35 standby.signal
-rw------- 1 postgres postgres  268 Mar 12 16:35 postgresql.auto.conf

postgres@postgresql:/etc/postgresql/12/main$ cat postgresql.auto.conf
primary_conninfo = 'user=replicator password=replicator host=192.168.56.7 port=5432 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

postgres@postgresql:/etc/postgresql/12/main$ source ~/.pgsql_profile
postgres@postgresql:/etc/postgresql/12/main$ pg_ctl -D /etc/postgresql/12/main/ start
***waiting for server to start....postgres: could not access the server configuration file "/etc/postgresql/12/main/postgresql.conf": No such file or directory
 stopped waiting
pg_ctl: could not start server
Examine the log output.***

postgres@postgresql:/etc/postgresql/12/main$ cat ~/.pgsql_profile
export PATH=/usr/lib/postgresql/12/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/games:/usr/local/games:/snap/bin PAGER=less

If data_directory in the master's postgresql.conf is set to some non-default value, then the conf files will not be in the "real" data directory and will not be copied by pg_basebackup.

If the person in charge of the master is not you, you should ask them for a copy by some other means.  Or you can just create your own.


What is the difference between uniqe index/unique/unique key/unique constraint keywords? I am using mysql 5.5.16

What is the difference between the following DDL statements?

1)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique index query_md5_index (query_md5)
) engine=InnoDB;

2)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique query_md5_index (query_md5)
) engine=InnoDB;

3)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , constraint md5_constraint unique (query_md5)
) engine=InnoDB;

4)
create table sql_query (
    id int not null
    , query_text varchar(21826) not null
    , query_md5 binary(16) not null
    , primary key (id)
    , unique key query_md5_key (query_md5)
) engine=InnoDB;

Which one should I use? I want to ensure that the there are no rows with the same value for query_md5 and also want to create an index on that column so that searches on that column are fast.

I read the manual at https://dev.mysql.com/doc/refman/5.6/en/innodb-storage-engine.html but it is not very helpful.

There are no differences between all the SQL statements, except the third one you're using a different name for the unique index, that's all.

All of them creates a unique key/index, constraint md5_constraint unique (query_md5) has the same effect.

Just pick the name you prefer for the unique key and feel free to use any of your queries. :)


Can't create database in SQL Server Express 2005 One of our clients is running Windows Small Business Server 2011, and they have a SQL 2005 Server Express instance (and several other versions also installed).

Using Management Studio, running as administrator, I cannot seem to create any databases. The error I get is:

  Could not obtain exclusive lock on database 'model'. Retry the operation later.
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)

The first time this happened, there happened to be a backup task running which had a lock, so I tried again later, and still had the issue. Running EXEC sp_who2 shows only my own user with 2 locks on the model db, both are "sleeping", and seem to come from the Management Studio (.Net SQL Client, same last batch as when I started the app).

Unfortunately I mostly work with MySQL, so all this MS stuff is over my head.

Edit:

Here is the result of SELECT  * FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'model'

Result:

+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+
| spid |         lastwaittype         | dbid | uid | physical_io | login_time | open_tran |   status    | hostname | hostprocess |       cmd        |       loginame       |
+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+
|    1 | SLEEP_TASK                   |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | RESOURCE MONITOR | sa                   |
|    2 | LAZYWRITER_SLEEP             |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | LAZY WRITER      | sa                   |
|    3 | LOGMGR_QUEUE                 |    0 |   1 |           0 | 12:45.8    |         0 | suspended   |          |             | LOG WRITER       | sa                   |
|    4 | REQUEST_FOR_DEADLOCK_SEARCH  |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | LOCK MONITOR     | sa                   |
|   52 | MISCELLANEOUS                |    3 |   1 |           0 | 05:14.5    |         0 | sleeping    | BH-SBS   |        6976 | AWAITING COMMAND | BH\nuser             |
|   62 | MISCELLANEOUS                |    3 |   1 |           0 | 04:13.0    |         0 | sleeping    | BH-SBS   |        6976 | AWAITING COMMAND | BH\nuser             |
|   74 | MISCELLANEOUS                |    3 |   1 |           5 | 02:23.1    |         0 | runnable    | BH-SBS   |       22436 | SELECT           | BH\nuser             |
+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+

I don't really think the problem is creating the database with model as active database as Reaces mentioned in this answer.

It is possible to create a new database using

USE Model
GO
CREATE DATABASE TEST;
GO

The issue might be some process or session holding lock on the model database and the CREATE Database statement not able to acquire the exclusive lock required.

Check what processes are currently using the model database, you can use

SELECT  *
FROM    sys.sysprocesses
WHERE   DB_NAME(dbid) = 'model'

Depending on what sessions are running, you can probably issue a KILL <SPID>, where <SPID> is the session_id of the session holding a lock on model, and try creating the database again.

Update: Here is a query to find what sessions are currently running, the locks requested and additional information: (Run on model database)

SELECT  resource_type ,
    request_session_id ,
    t.TEXT AS Command,
    request_mode ,
    request_type, request_status,
    s.program_name, s.status
FROM    sys.dm_tran_locks l JOIN sys.dm_exec_sessions s
ON l.request_session_id = s.session_id
    JOIN sys.dm_exec_requests r ON r.session_id = l.request_session_id
    CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) t
WHERE   resource_database_id = DB_ID()


MySQL bug: NOT IN + subquery using GROUP BY HAVING returns nothing I'm querying on this table:

SKU
aaaa
bbbb
bbbb
NULL

Here's the query:

select *
from TEST as N
where  N.SKU NOT IN (select SKU
                 from TEST
                 group by SKU
                 having count(*)>1);

I expect the query returns 'aaaa', however, it returns nothing.

The reason I expect that is because the subquery below only returns 'bbbb':

select SKU
from TEST
group by SKU
having count(*)>1

Therefore, 'aaaa' NOT IN the subquery result.

To show the bug please copy and paste these statements in your MySQL IDE to create schema:

drop table if exists TEST;
create table TEST(
SKU varchar(255)
);
insert into TEST values('aaaa'),('bbbb'),('bbbb'),(NULL);

Even more confusing, try this new table below, re-run the query, and check result:

drop table if exists TEST;
create table TEST(
SKU varchar(255)
);
insert into TEST values('aaaa'),('cccc'),('dddd'),('bbbb'),('bbbb'),(NULL);

Expect 'aaaa', 'cccc', 'dddd' are all returned, however 'aaaa' is missing, yet 'cccc' and 'dddd' are returned.

This problem looks weird.

It reminds me of a post I wrote 7 years ago : Problem with MySQL subquery

Sometimes, values in subquery results may disappear intermittently while processing.

This problem was usually associated with non-SELECT queries involving subqueries.

This looks like something even worse. You should file a bug report on this one.


How do I find the leafs using PostgreSQL's ltree? I have a tree structure based on accounting purposes like

300
300.01
300.02
300.02.01
300.03
.....

I want to assign is_leaf_level column to true for only leaf levels. kod is the accounting number having type ltree. Here is the query:

UPDATE dim_accounting_codes d1
SET is_leaf_level = (
  SELECT count(*)=0
  FROM dim_accounting_codes d2
  WHERE d1.kurum_hesap_plani_id = d2.kurum_hesap_plani_id
    AND d2.kod ~ lquery_in(
      (ltree2text(d1.kod) || '.*{1,}')::cstring
    )
  )

The query plan is as follows:

     Update on dim_kurum_hesap_kodu d1  (cost=0.00..8529064185.14 rows=1468583 width=322)
  ->  Seq Scan on dim_kurum_hesap_kodu d1  (cost=0.00..8529064185.14 rows=1468583 width=322)
        SubPlan 1
          ->  Aggregate  (cost=5807.56..5807.57 rows=1 width=1)
                ->  Bitmap Heap Scan on dim_kurum_hesap_kodu d2  (cost=347.44..5807.53 rows=9 width=0)
                      Recheck Cond: (kod ~ lquery_in(((ltree2text(d1.kod) || '.*{1,}'::text))::cstring))
                      Filter: (d1.kurum_hesap_plani_id = kurum_hesap_plani_id)
                      ->  Bitmap Index Scan on idx_kod_gist  (cost=0.00..347.44 rows=1469 width=0)
                            Index Cond: (kod ~ lquery_in(((ltree2text(d1.kod) || '.*{1,}'::text))::cstring))

Query is running too slowly. (about 40 minutes passed and still working) There are approximately 2000000 records in the table. The index on kod is not used in the plan. Is there any advices on how to mark the leaf level records on ltree structure?

Updated:

result of \d dim_accounting_codes

                                                          Table "public.dim_accounting_codes"
            Column             |              Type              |                                       Modifiers
-------------------------------+--------------------------------+----------------------------------------------------------------------------------------
 dim_kurum_hesap_kodu_id       | integer                        | not null default nextval('dim_kurum_hesap_kodu_dim_kurum_hesap_kodu_id_seq'::regclass)
 version                       | integer                        |
 kurum_hesap_kodu_id           | integer                        |
 kurum_hesap_kodu_adi          | character varying(256)         |
 hesap_tipi                    | character varying(30)          |
 kurum_hesap_plani_id          | integer                        |
 kurum_hesap_plani_ad          | character varying(256)         |
 pasiflestirme_tarihi          | timestamp(0) without time zone |
 aktiflestirme_tarihi          | timestamp(0) without time zone |
 kod                           | ltree                          |
 hesap_kodu_durum              | character varying(8)           |
 ana_hesap_kodu                | character varying(10)          |
 yardimci_ekonomik_kod_i       | character varying(10)          |
 yardimci_ekonomik_kod_ii      | character varying(10)          |
 yardimci_ekonomik_kod_iii     | character varying(10)          |
 yardimci_ekonomik_kod_iv      | character varying(10)          |
 ana_hesap_kodu_adi            | character varying(500)         |
 yardimci_ekonomik_kod_i_adi   | character varying(500)         |
 yardimci_ekonomik_kod_ii_adi  | character varying(500)         |
 yardimci_ekonomik_kod_iii_adi | character varying(500)         |
 yardimci_ekonomik_kod_iv_adi  | character varying(500)         |
 yardimci_ekonomik_kod_level   | smallint                       |
 is_leaf_level              | boolean                        | default false
Indexes:
    "idx_dim_kurum_hesap_kodu_lookup" btree (kurum_hesap_kodu_id)
    "idx_dim_kurum_hesap_kodu_tk" btree (dim_kurum_hesap_kodu_id)
    "idx_kod_gist" gist (kod)

Using ltree2text sounds like a horrible idea, and cstring is an internal thing -- you should never be using that. If an ltree has no descendants it's a leaf, so let's test for that using @>

CREATE TABLE foo(ltree)
AS
  VALUES
    ( 'Top.Science.Astronomy'::ltree ),
    ( 'Top.Science.Astronomy.Astrophysics' ),
    ( 'Top.Science.Astronomy.Cosmology' ),
    ( 'Top.Collections.Pictures.Astronomy' ),
    ( 'Top.Collections.Pictures.Astronomy.Stars' ),
    ( 'Top.Collections.Pictures.Astronomy.Galaxies' ),
    ( 'Top.Collections.Pictures.Astronomy.Astronauts' );

CREATE INDEX ON foo USING gist(ltree);

SELECT *
FROM foo AS f1
WHERE NOT EXISTS (
  SELECT 1
  FROM foo AS f2
  WHERE f1.ltree <> f2.ltree
    AND f1.ltree @> f2.ltree
);

This uses the GIST index on the ltree. See the docs for more information on using @> with ltree.

I suggest not storing this on the table. First, it's maintenance nightmare. After every insert and update, you would have to potentially rewrite the parent nodes to update the leaf column. Moreover, if simply querying for this isn't fast enough, I would instead use a MATERIALIZED VIEW. At least then you know when the view is stale. Keeping this in sync with your data sounds like no-fun to me.


You need to first isolate the rows with 'ABIERTO' and 'CERRADO' and then do a FULL JOIN between them. Since MySQL has not implemented FULL join, there are various ways to achieve such a join. We will need the distinct values of (key, point_name) pairs, so the easiest way seems to be to get first these values and then do two LEFT joins, one to 'ABIERTO' and another to 'CERRADO' values:

SELECT
    d.point_name AS equipo,
    a.time       AS abierto,
    b.time       AS cerrado
FROM
    ( SELECT DISTINCT `key`, point_name
      FROM alarms_9_2015
    ) AS d
  LEFT JOIN
    alarms_9_2015 AS a
      ON  a.value = 'ABIERTO'
      AND a.key = d.key
      AND a.point_name = d.point_name
  LEFT JOIN
    alarms_9_2015 AS b
      ON  b.value = 'CERRADO'
      AND b.key = d.key
      AND b.point_name = d.point_name
ORDER BY
    COALESCE(a.time, b.time) ;

A composite index on (key, point_name, value) would improve efficiency.


can't connect to remote postgresql database I'm trying to connect to a remote psql database.  Before I added the pg_hba.conf entry with the client's IP address, I was getting an error message :

xdev@xdevbox:~$ psql -U postgres testdb -h 10.1.1.47
psql: FATAL:  no pg_hba.conf entry for host "10.201.50.71", user "postgres", database "testdb", SSL off

I added the client's IP with trust settings.  I also changed the listen address in postgres.conf on the server to listen to "*".
Then I restarted the database server using /etc/init.d/postgresql restart command.

Now when I try to connect, I get the following error message:

psql: could not connect to server: Connection refused
    Is the server running on host "10.1.1.47" and accepting
    TCP/IP connections on port 5432?

in postgresql.conf, the port is set to 5432.
I'm not sure what else to check.

Thanks

You have to configure the following two files

pg_hba.conf

host all all 0.0.0.0/0 md5

postgresql.conf

listen_addresses='*'

You have to check if the port 5432 is open: http://www.yougetsignal.com/tools/open-ports/

If it's not then add a rule to your iptables:

iptables -A INPUT -s 0/0 -p tcp --dport 5432 -j ACCEPT

0/0: If you want anybody to access it.
You can change it to a specific IP address or range of IP addresses.


SP_whoisactive capture on Alwayson Secondary Replica issue I am trying to capture SQL query plans using sp_whosisactive on a alwayson secondary replica db server and getting the error below in the query_plan column.
This happens when I run the code below as a sql job. However, when I have my SSMS connection set to use APPLICATIONINTENT = READONLY and execute it manually it stores the execution plan for the queries running on the server.

How do we set APPLICATIONINTENT = READONLY  for SQL Agent jobs running on secondary readonly replicas. Thanks!

EXEC dbo.sp_WhoIsActive
        @get_plans = 1,
        @destination_table = 'dbo.WIA_Output'

error message="The target database ('xyz') is in an availability group and is currently accessible for connections when the application intent is set to read only. For more information about application intent, see SQL Server Books Online."

Since you're running sp_WhoIsActive, which looks at all running queries on the server, you can run it from any database. Just choose your database context as master or tempdb in your Agent job step.


Oracle DataGuard: Find primary from standby DB I have SYS access to a data guard standby DB, but i need to find the primary one. I was searching in Oracle Docs but couldn't find anything that could help.

You can check some options like checking FAL_SERVER parameter, LOG_ARCHIVE_DEST_n parameters, Dataguard Broker, Recovery Catalog, Cloud Control, etc..  if they are used, but none of them are mandatory for a DataGuard configuration.

In fact, the standby nodes doesn't need to send any kind of information to the Primary db at all. Since the heartbeat is checked from the Primary, the best approach is to query v$session and track down sessions connected as the, non-existent, "PUBLIC" schema.


Trouble when accessing old after deleting a row in postgrsql When I delete an athlete from my database I want to take the sum he has raised and divide the sum between all the other athletes in my database.
When I run my code "addBalance" becomes Null and therefore all the sums become Null but when I run the query outside the function it runs exactly as I want it to. It looks like "old" is causing trouble. Any help is appreciated, thank you.

Here is the code and the tables are below the code.

CREATE OR REPLACE FUNCTION divideMoney()
RETURNS TRIGGER
AS $$
DECLARE athleteSum int;
DECLARE peopleCount bigint;
DECLARE addBalance int;
BEGIN
    SELECT sum::INTEGER from People P JOIN Accounts A ON P.ID = A.peopleID
    WHERE A.peopleID = old.ID INTO athleteSum;
    SELECT COUNT(*) FROM Accounts INTO peopleCount;
    RAISE NOTICE 'athleteSum --->%', athleteSum;
    RAISE NOTICE 'peopleCount --->%', peopleCount;
    addBalance := athleteSum / peopleCount::INTEGER;
    RAISE NOTICE 'addBalance --->%', addBalance;
    RAISE EXCEPTION 'STOP';
    FOR person IN 0..(peopleCount - 1) LOOP
        IF person <> old.ID THEN
            UPDATE Accounts
            SET sum = sum::INTEGER + addBalance
            WHERE peopleID = person;
        END IF;
    END LOOP;
RETURN OLD;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER divedeDeletedMoney
BEFORE DELETE ON People
FOR EACH ROW
EXECUTE PROCEDURE divideMoney();

CREATE TABLE Gender (
       gender CHAR(1),
       description VARCHAR(10),
       PRIMARY KEY (gender)
);

CREATE TABLE People (
       ID INT,
       name VARCHAR(50),
       gender CHAR(1),
       height FLOAT,
       PRIMARY KEY (ID),
       FOREIGN KEY (gender) REFERENCES Gender (gender)
);

CREATE TABLE Accounts (
    peopleID int PRIMARY KEY,
    sum varchar(255),
    last_updated date,
    FOREIGN KEY (peopleID) REFERENCES People (id)
);

To have a realistic demo case, you need to populate the tables, not just create them.
Your title says "after deleting a row", but your trigger is clearly a "before" trigger.
If you (attempt to) delete a row from people which has no corresponding entry in accounts, then athleteSum naturally becomes NULL.  What else would it do?  If you wish to avoid this, you need to take steps to avoid it.  Convert to zero?  Throw an error?  I don't know, only you can know.

addBalance := ROUND(athleteSum / peopleCount::INTEGER);

You are dividing two integers.  The result of integer division is already an integer.  No rounding is necessary.  Shouldn't it be (peopleCount-1)?  Otherwise you are distributing the value back to the row that was just (attempted to be) deleted.

FOR person IN 0..peopleCount LOOP

WHERE peopleID = person;

Why would you expect the peopleID to be densely packed between 0 and peopleCount?  Especially since you just attempted to delete one of those intervening values?

RETURN NEW;

For a delete trigger, NEW is NULL. Returning NULL prevents the delete from actually happening.  Is that what you want?


Storing variable length arrays of doubles in MySQL without loss of information I am trying to store variable length arrays of C++ doubles into a MySQL database.

I was initially storing each value in the array as a row of the form:

`pv_name` varchar(60) NOT NULL,
`time_stamp` bigint(20) UNSIGNED NOT NULL,
`array_index` bigint(20) UNSIGNED NOT NULL,
`value` double NOT NULL,
`alarm_status_id` tinyint(3) UNSIGNED NOT NULL,
`alarm_severity_id` tinyint(3) UNSIGNED NOT NULL

The pv_name and time_stamp uniquely identified each array. The array_index identified each element of the array.

The problem that I ran into is that the MySQL double type does not accept a 'nan' value. So my thought was to use a single blob type for the entire array. Would this be recommended? One advantage of doing so is that it allows me to store values of other types (tinyint, smallint, int, float, varchar) in this blob column in the same table, whereas before I needed a different table for each type. If this is appropriate, what is the best way to insert the array into the blob? I am currently casting it to a string using a format string of '%.17f' for each value and then using setString with a prepared statement (from the MySQL C++ connector (libmysqlcppconn)). Is it possible to use the '%g' format string instead without losing information? Is there an altogether different approach that may be more appropriate?

Plan A:  Use NULL to indicate NaN.

Plan B:  Use JSON now.  Build whatever structure makes sense in you app, encode it in JSON, store it in a TEXT field.  Note:  JSON is expecting utf8 string inside.  Keep in mind that you need to fetch the entire JSON field and manipulate it in your app, not in SQL.


When should I extract a field into a new table? Given a table :

CAR
---
id
model_id
condition
fuel_id
release_year

Since many cars would have the same Model and same Fuel, there are 1:N tables created.

Though, many Car objects can have the same release_year.

Should I create a Year table and then a year_id field into Car?

Design-wise, it seems accurate, but it seems weird to create a Year table containing a list of years.

Still, this is actually what I do for Fuel : a table containing a list of fuels types.

When is it accurate to create a new 1:N table instead of a field ?

It depends... as a lawyer might say
The columns in your car table might seem to be data that will not change but let's take a closer look:

condition: this is definitely a temporal fact.  Today the car is new, drive it off the lot and it is used, wait long enough and it is an antique.  You might have to allow for the possibility that the program will store data for the same vehicle more than once during it's lifetime.  Lease it as new, get it back as used, sell it....
release year: will an auto manufacturer always officially release a car on a discrete date?  Or is it possible that it will be shown at an auto show this year, released in Europe next year and released in North America a year after that?

My experience with data modelling is that the most common mistake is to assume that a fact like a year or a condition will never change when it is really slowly changing data.

For your purposes if there is any possibility that a release year could have more than one value or span over a year end or be amended by the manufacturer then you could have a release_year_id in CAR which is a foreign key to the RELEASE_YEAR table which would have:

ID NUMBER(10)
YEAR_START  DATE
YEAR_END    DATE
GEO_AREA    NUMBER(10)
CURRENT_VALUE  NUMBER(1) and constrained to value of 1 or 0

The current value field would be implemented if there is the possibility of the start of the release year or end of the release year changing and you wanted to record this.  Further information is described temporal databases.

Your final reality check is to create some sample reporting queries and find out how much it hurts.  This will give you a good indication of whether to normalize or denormalize

As Jeff Atwood wrote

  normalize until it hurts, denormalize until it works


In DB2 LUW, When should I use a 4K, 8K, or 16K tablespace rather than just creating a 32K tablespace and being done with it? We're using DB2 LUW 10.5 & 11.1 on Windows & Linux systems, in case it's relevant to he answers.

Question:
Is there a time where it would be correct to use 4K rather than 32K? If so, why? (Does it perform better when it can be used?) Or, is it just a legacy appendage from some prehistoric time when 4K was just the page size?

Background:
I have always just created a 4K, 8K, 16K, & 32K tablespaces and associated bufferpools when I create DB2 db's.

My manager is challenging me on this. (Good for him - I should know this!) He thinks we should just create a 32K tablespace and be done with it.

I'm not able to find anything that tells me, for instance, that when the row size allows it, we should use 4K rather than 32K because XYZ. It tells me I CAN do this, but not that/when I should.

It's a good question, but the answer is worthy of a whole chapter in a book that does not exist, sadly. Ember Crooks' article you're linking to is a good overview; I'll add here some random factors that one might want to take into consideration when deciding on the tablespace page sizes.

TL; DR.

Choose one page size that is most suitable for your data, considering the points below. If your performance testing shows problems that can be addressed by moving some tables to a tablespace with a different page size, do it judiciously.

Decision factors.

As you have mentioned, table row width determines the minimum page size required to accommodate them. It doesn't follow though that you always want "the smallest that will work for your data".

Firstly, the usual arguments of "avoiding unnecessary I/O" and "handling less data at a time" with smaller page sizes may be a bit misplaced. If your tablespace container sits on a ZFS file system on an LVM volume on a VMWare virtual disk on a Ceph volume on an unknown number of RAID6 devices that may use spinning disks or SSDs, do you really know how much physical I/O your 4K (or 32K) read request will cause?

Smaller page sizes certainly help if your workload creates tablespace hotspots (most I/O requests go to a limited number of pages) that cannot be resolved by other means. In such situations smaller pages can improve bufferpool efficiency and reduce page latch waits between agents competing for access to the same page. On the other hand, smaller page size means longer LRU chains and hence possibly less efficient page cleaning.

There are also arguments for larger page sizes.

Presence of LOB data.

Normally LOB data are stored outside table rows, in separate data structures that have several performance disadvantages:

You can only access them via synchronous direct reads and writes that bypass the database bufferpools; the only caching available is that of the underlying file system, if enabled for the tablespace; direct reads also don't take advantage of page prefetching.
Since LOBs are not loaded into the bufferpools, repeated access to the same data will cause repeated direct read requests.
They are not compressed, even if the table compression is enabled.

If most of your LOB values are relatively small and could fit into the row itself given larger page size (as is often the case), you could store them inline, alleviating those disadvantages.

Compression.

Larger page sizes improve efficiency of adaptive (page-level) compression. Often I/O reduction that data compression affords outweighs its CPU costs.

Don't forget temp tablespaces.

Even if each of your tables individually can be placed into a 4K tablespace, a system temporary tablespace with a larger page size (and a corresponding bufferpool) might be required. If a query joins sub-4K rows from two or more tables, the result set width might exceed the 4K limit and, if it needs to spill, an appropriately-sized tablespace will be needed.

It's worth mentioning that creating tablespaces of each possible page size "just in case" is not a good idea, because, as you said, each requires a dedicated bufferpool, and multiple bufferpools, unless necessary, are almost always worse (less efficient) than a single large one.


Error 3203 restoring SQL Server backup: The data is invalid I am trying to restore a .bak file (backup taken on SQL Server 2008 R2, compatibility level 100). I am restoring the backup on SQL Server 2012 edition. I get the below error. Unfortunately the original DB from where the DB was backed up is no longer available. I am new on this project. I was given the bak file on a thumb drive.

Any suggestions on how I can restore the DB to 2012 ?

  Restore of database 'TstDB' failed.
  System.Data.SqlClient.SqlError: Read on ***13(The data is invalid.)

This is all is the error message. When I run RESTORE VERIFYONLY, I get the below error:

  Msg 3203, Level 16, State 1, Line 2
  Read on "C:****" failed: 13(The data is invalid.)
  Msg 3013, Level 16, State 1, Line 2
  VERIFY DATABASE is terminating abnormally.

RESTORE HEADERONLY was successful. When I run restore database with stats=1 I get the below:

  10 percent processed.
  20 percent processed.
  [...]
  70 percent processed.
  80 percent processed.
  Msg 3203, Level 16, State 1, Line 2
  Read on "C:\ccc.bak" failed: 13(The data is invalid.)
  Msg 3013, Level 16, State 1, Line 2
  RESTORE DATABASE is terminating abnormally.

Try running a RESTORE FILELISTONLY or RESTORE HEADERONLY. If these run successfully, that would prove that SQL Server is successfully accessing the .bak file. (And rule out some sort of permission issue.)

If that is successful, try running your RESTORE VERIFYONLY and RESTORE DATABASE with STATS=1. If it continues to fail in the same spot each time, it would probably indicate that your backup is corrupt, and you're out of luck.

If it fails in a different spot each time, it could be an issue with reliability of the location where your .bak is saved.


DR option for SharePoint databases? ideas and experiences First let me clarify that I am not looking for thoughts in terms what is the best or the bad options for a DR for SQL Server. I am just looking for for inputs from experienced people.

We are running Virtual Hyper-V and SQL Sever 2012 with FCI. I have three options here.

AlwaysOn (DR).
It seems easy and awesome but it is a new topic for us. I cannot find too much information about it , other challenge is we have a limited support so i'm not sure to go with it unless feel comfortable. we implemented on test environment, but I believe production is a different story.
Database Mirroring (DR)
May be the easiest option to go with. and yes I know it a deprecated feature. but  still supported in SQl Server 2012, so still we have enough time.

I like mirroring because it is recommended from Microsoft before Always-on, and secondly it is good in terms of transactions as commit in primary then directory shipped to mirrored database in DR. easy to monitor and stable.
Log shipping(DR)
The more robust solution as DR. Microsoft use it in office 365, and very good option in case of slow network. It does need some work to configure and a lot of hands-on to prepare. but it is old and reliable. May be is good for non-critical databases where can I put interval like 24 hours. One cool thing is read only databases where I can switch application in read only mode till primary get fixed unlike mirroring.( Yes Always-On does that).

Again it is for DR only and I would like to hear from you. Consider this for SharePoint databases which means only content and other few databases will be replicated as Microsoft support them only.

In terms of wanting to get input from other people about their experiences, I can share about our use of Log Shipping in our Production environment.  We use native Log Shipping and perform DR tests once a quarter for our critical systems.  This involves failing over to the Secondary, pointing the application(s) at it, and testing that everything works as expected.  We then fail back to the Primary and ensure things are ready for our end users again.

By repeatedly testing our DR process we've been able to make some hard-earned improvements.  The solution we use for helping perform the failover and failback procedures is here.


Find all the SP's where a particular column is not updated I want to find stored procedures where a particular column is not updated.

As an example

SP1:

BEGIN
      UPDATE YourTable
      SET    Foo = @Foo,
             Bar = @Bar
      WHERE  Id = @Id
  END

SP2:

 BEGIN
      UPDATE YourTable
      SET    Foo = @Foo
      WHERE  Id = @Id
  END

So I want to get all the SP's where Bar is not updated in above example it should return SP2.

Note: one SP can have multiple update statements. Is there any possibility to get the table name of that update statement ?

Try this:

SELECT DISTINCT OBJECT_NAME([object_id])
FROM sys.sql_dependencies AS d
WHERE referenced_major_id = OBJECT_ID('dbo.YourTable')
AND is_updated = 1
AND NOT EXISTS
(
  SELECT 1 FROM sys.sql_dependencies AS id
   INNER JOIN sys.columns AS c
   ON id.referenced_major_id = c.[object_id]
   AND id.referenced_minor_id = c.column_id
   WHERE id.object_id = d.object_id
   AND id.referenced_major_id = d.referenced_major_id
   AND c.name = 'bar'
);

Caveats:

You didn't mention your version of SQL Server. I'm assuming you are on 2005+.
I'm not sure how reliable the is_updated column is. I've seen major inconsistencies with the is_selected column.
This does depend on your stored procedure being created after the objects and not being subject to deferred name resolution. You may want to recompile all of your stored procedures before relying on the output.
For a lot more about how dependencies can't be relied upon, see this post.

Parsing is a real pain in the rear because all of your statements could have slight variations. You may be able to search for patterns like:

SELECT name FROM sys.procedures
  WHERE LOWER(OBJECT_DEFINITION([object_id]))
        LIKE '%update%yourtable%'
  AND LOWER(OBJECT_DEFINITION([object_id]))
      NOT LIKE N'%bar%=%';

But this has all kinds of ways to go astray - some of these patterns could appear in comments, false positives could come because you have an update to yourtable2, you could miss a row because an update references the column bar2, etc. etc.

I wrote a pretty comprehensive search procedure here, but it will only be as reliable as the search pattern you define:

A handy search procedure


How to select and show all the tickets from last calendar year? It seems SQL calls varies a lot, and cannot find how to do this in Firebird, where SQL dialect is 3.

There's a table called TICKETS. This table has INSERT_TIME which is TIMESTAMP. How do I select all the tickets which has insert time year = 2016?

Depending on your needs, you can use:

Using extract:

select *
from tickets
where extract(year from insert_time) = 2016

Using between:

select *
from tickets
where insert_time between timestamp'2016-01-01 00:00:00.0' and timestamp'2016-12-31 23:59:59.99999'

Depending on the volume of data, and the presence of an index on insert_time or not, the second may be preferable over the first.


Can I modify the fill factor value while a database is in use? I'm using SQL server 2008 R2.  I asked a similar question before on this forum, now I'm asking:

First, can I change the fill factor while the database is in use?
Second, how can I determine the most suitable value for our server fill factor?  On what does this depend?

"yes", you can change the fill factor while the database is in use... If you can accept the blocking! I've found that it is seldom a good idea to have it different that 0 (or 100).

The dbreindex will not work on your heaps, so you should create a clustered index instead. (see for instance this Kimberly Tripps blog post or this https://www.brentozar.com/blitzindex/sp_blitzindex-self-loathing-indexes/ on "heaps with forwarded records"
You may also want to download sp_Bliz from https://www.brentozar.com/blitz/. This will help you investigate what may help your server run faster.


Manually specifying candidate masters in Maxscale Looking over the documentation, I haven't quite been able to find if its possible to manually set the candidate master status of a server in Maxscale.  The reason I am looking for this is because some of the slaves we have hooked up the the master/slave server architecture have inferior hardware to the others, and we don't want them to become the master.  Looking at the documentation, it is possible to set the status of a sever as master or slave, but not specify that we don't want it to become a master.

Is there a setting to do this in Maxscale?  Possibly in maxadmin or a .cnf file or something?

Thanks.

You can use priorities as described here: https://mariadb.com/kb/en/mariadb-enterprise/mariadb-maxscale/maxscale-galera-monitor/


How do I end function when X so my function looks like this:

create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$ begin return query
with
i1(id1) as select insert_here(a1,a2),
i2(id2) as select insert_there(a3,a2),
select id1,id2 from i1,i2;
end; $$

I need the function to check if a1||a2 in table_1.a12 before running the inserts and if it is in skip that insert and move onto the next.

I tried:

create or replace function insert_stuff(a1 text, a2 text, a3 int)
returns table (id_i int, id_j int) as
$$ begin return query
with
i1(id1) as select insert_here(a1,a2),
i2(id2) as select insert_there(a3,a2),
select id1,id2 from i1,i2;
exception when select (select a1||a2) in (select a12 from table_1) then
end; $$

but this just causes the query to stall out

You can simplify to:

CREATE OR REPLACE FUNCTION pg_temp.insert_stuff(_a1 text, _a2 text, _a3 int)
  RETURNS TABLE (id_i int, id_j int) AS
$func$
BEGIN
   IF EXISTS (SELECT FROM table_1 WHERE a12 = _a1||a2) THEN  -- see below
      RETURN QUERY
      SELECT -1, NULL;
      -- RAISE EXCEPTION 'Some informative msg here'  -- alternative?
   ELSE
      RETURN QUERY
      SELECT insert_here(_a1, _a2)
           , insert_there(_a3, _a2);
   END IF;
END
$func$  LANGUAGE plpgsql;

Note the clearer (and faster) IF EXISTS. See:

PL/pgSQL checking if a row exists

Either variant might fail "unexpectedly" if either input parameter or the column a12 can be NULL! You would have to define desired behavior.

Subtle difference: Since I replaced your CTE construct with a simple SELECT, the function always returns at least one now, even if either nested function (insert_here() or insert_there()) returns no row. Your version would return nothing in this case, which may be a sneaky bug if either of the nested functions can return no row.

I am assuming Postgres 10 or later (you didn't declare). Else, depending on the undeclared nature of the nested functions, you might need to consider:

What is the expected behaviour for multiple set-returning functions in select clause?


InnoDB: Error: log sequence number is in the future! Hi I have 2 node cluster where recently went offline not sure why. Node1 started fine and all the traffic is on node 1 but node 2 still down due to below error. I tried set the inodb recovery to 3 and start but i cant start and the same error as like below. I've even tried removing the "aria file, Galera cache,gwstate file" and restart MySQL but still the same error.

How can i get the node 2 started or should i do full resync.

7a0d83860019dca40019dca30019dca500000b8b15ef4dad45bf0000000000000000000000be000d3b21803500000000180615 12:13:14  InnoDB: Error: page 1209437 log sequence number 12729899042363
InnoDB: is in the future! Current system log sequence number 12692016003988.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html

I would try a full resync, i.e. SST, at this point.

Note that depending on your wsrep_sst_method (see description MariaDB knowledgebase) an SST may block the donor node (node1 in this case) while it's making the backup. The default wsrep_sst_method is rsync, which is blocking.

You should in theory be able to force an SST by removing the grastate.dat file from the datadir and then restart MariaDB, but I'm unsure if that works in your case. So if that doesn't work, then you could proceed as if you were adding a new node to the cluster. In this case, basically just wipe the datadir, run mysql_install_db, and try starting up the new node.


Insert Value based on One Column based on Value on another Column I just tried to set _timeStamp column to store value on the basis of HOUR of _date column and tried to create table as like below but encounter 1064 error. I'm new to mysql so help me in figuring out mine mistake.

create table `help` (`_ID` integer not null,
`_date` datetime not null,
`_timeStamp` integer not null
        SET  (1 if HOUR(`_date`)<10 else 2 if HOUR(`_date`)<16 else 3 if HOUR(`_date`)<24
            else -1),
PRIMARY KEY (`_ID`)
);

What you are trying to do is to create a computed column and I am pretty sure MySQL does not support them. There are a few ways around this issue:

Do the computation in the logic that inserts the data
Create a trigger on the table to do the calculation when data is inserted/updated
Create a view to represent the data how you want it.

My personal preference would be to use a view as it is easier to maintain as is a much neater solution. Your view would look something like this:

CREATE VIEW `vwHelp`
AS
SELECT `_ID`, `_date`,
CASE
   WHEN HOUR(`_date`) <10 THEN 1
   WHEN HOUR(`_date`) <16 THEN 2
   WHEN HOUR(`_date`) <24 THEN 3
   ELSE -1
END AS `_timestamp`
FROM `help`;


Can I somehow group insert returnings? I am trying to insert user and multiple of his user role + return id of created user in single row. I used following query:

WITH inserted_user as (
 insert into "user" (username, password, nickname, account_non_expired,
account_non_locked, credentials_non_expired, enabled)
  values ('username', 'password-hash', 'nickanme', true, true, true, true)
  returning id
)
insert into user_role values
  ((select id
    from inserted_user), 2),
  ((select id
    from inserted_user), 3),
  ((select id
    from inserted_user), 4)
returning user_id;

My issue is, that in my result I see 3 rows with the same ID (this is bad for my java application which expects 1 number). I am not sure how to edit this to get my result. I tried to wrap this into another select + many other more or less dumb things, but none of it was working. Could you please help me?

Use another CTE for the second INSERT and SELECT DISTINCT user_id FROM that CTE. In the new CTE do an INSERT ... SELECT FROM the old CTE.

WITH inserted_user
AS
(
INSERT INTO "user"
            (username,
             password,
             nickname,
             account_non_expired,
             account_non_locked,
             credentials_non_expired,
             enabled)
            VALUES ('username',
                    'password-hash',
                    'nickanme',
                    true,
                    true,
                    true,
                    true)
            RETURNING id
),
inserted_user_role
AS
(
INSERT INTO user_role
            SELECT iu.id,
                   gs.n
                   FROM inserted_user iu
                        CROSS JOIN generate_series(2, 4) gs(n)
            RETURNING user_id
)
SELECT DISTINCT
       user_id
       FROM inserted_user_role;


Help with "Flexible" vs Fixed Tables We are designing a database in SQL Server to handle sales commissions. A diagram of the current schema looks like this:

There are only a couple of us on the project, and our boss dropped this design on us. I'm a novice, but I was concerned about the Slot table because it seems like we're heading toward an EAV model and I don't think our team is experienced enough to navigate the pitfalls.

A Slot can be a company, location, region, route or position (based on its SlotType). We have tables for these entities, but management wants the "flexibility" to assign slots to each other (SlotHierarchy). I'm not going to get into the mess of effective dates that apply to every slot, slot type, slot level, slot level type, and employee's position assignment. All the effective dates are because the management wants control over every piece.

The plan is to have an Employee get assigned to a position slot (SlotPositionAssignment) that can then be assigned to a location, route or region slot. A position can only have one employee assigned at a given time. A position can be assigned to any number of slots, though.

So, we may have two positions (slots) of PositionType "Salesman" and SlotDesc of "Sales 1" and "Sales 2", each with an employee assigned to it. These two positions may be assigned to multiple route, region, location or company slots. The SlotHierarchy helps sort this out.

My concern:
I'm afraid we may shoot ourselves in the foot with this. I don't mind writing additional application logic, but this seems "too clever". Would it not make more sense to call a spade a spade and use tables that map to real-life instead of obscuring them as "slots"?

I wanted to do away with having positions as slots and use a Position table, or perhaps use just PositionType though we'd lose the ability to report on a specific position in the company like "Rt 1 Manager". When I brought this up, I was told that it wouldn't be "flexible" and that was the end of it.

Is there a better way to organize this, or make some compromise, or am I worried about nothing? I am the novice and don't mind being wrong, as long as I know why I'm wrong.

I'd suggest you have a think about this system's use cases.  Work out in your own mind how the queries will look for the given design and for your alternative.  Include scenarios where people move between slots.  What do updates look like?  Can business constraints be enforced in DRI?  Can the desired values still be found?  Having actual examples to talk through will highlight your concerns and allow your boss to explain the model more fully.

Writing horror queries against a ghastly schema is only one part of the problem.  Someone has to fix them, at 3am, with no documentation, when they break.  More intelligible queries at this stage will give you a better system, sooner.

If it calms your nerves any, I've had success with EAV systems.  That said, we were a team of very experienced DBAs, it really was a blue-sky, "what if" project and, no, we didn't bet the company on it.  Any dev project that will be heading to production we discourage most strongly from using an EAV approach.

If you go the 'flexible' route do not try to write generic queries just because you have a generic schema.  Each query should only deal with one of Company, Location, Region or whatever your slot types are.

My two cents' worth.


Proxy for MDW data collection on remote servers - SQL Server 2012 I am trying to setup data collection (for disk usage) from remote servers and upload it to my MDW database. I understand I will need a proxy set up for the agent to do this but which subsystem to I give it rights to?

Operating system (CmdExec). The reason for this is that the documentation at https://msdn.microsoft.com/en-us/library/bb630341.aspx  states:

  Members of the db_ssisadmin role and the dc_admin role may be able to elevate their privileges to sysadmin. This elevation of privilege can occur because these roles can modify Integration Services packages and Integration Services packages can be executed by SQL Server using the sysadmin security context of SQL Server Agent. To guard against this elevation of privilege when running maintenance plans, data collection sets, and other Integration Services packages, configure SQL Server Agent jobs that run packages to use a proxy account with limited privileges or only add sysadmin members to the db_ssisadmin and dc_admin roles.

So you need to modify anything which is running the SSIS jobs. In this case if you inspect the jobs themselves you'll see they're run using CmdExec running dcexec which runs the packages. So those are the ones you'd need a proxy on if you're interested in if you have that security issue.


Why is AT TIME ZONE nondeterministic? SQL Server 2016's AT TIME ZONE appears to be nondeterministic. However, I haven't been able to find documentation officially stating this or giving a rationale as to the reasoning behind it.

Why is AT TIME ZONE nondeterministic?

Example Showing Non-Determinism

Executing:

CREATE TABLE Test (
    LegacyTimestamp DATETIME,
    Timestamp AS LegacyTimestamp AT TIME ZONE 'Eastern Standard Time' PERSISTED
);

Returns the following error:

Msg 4936, Level 16, State 1, Line 1
Computed column 'Timestamp' in table 'Test' cannot be persisted because the column is non-deterministic.

AT TIME ZONE employs some logic to calculate Daylight Savings Time. DST offset values are not immutable (they are subject to change via windows updates) and are contained externally in the Windows registry, so therefor the AT TIME ZONE function cannot be deterministic since it is relying on external data.

Similarly, this is why sys.time_zone_info is a view and not a static reference table, it needs to be calculated depending on the registry values which have the most up to date timezone information.


mysql triggers to multiple table and insert new id to table I have 4 table:

table a (id (auto_increment), id_b , id_c, id_d(foreign_key from table b,c,d) , column)
table b (id auto_increment, column)
table c (id auto_increment, column)
table d (id auto_increment, column)

when there's event table a, b, c, d inserted, must copy to table a, b, c, d in other database. (note: in other database table a,b,c,d already has data, so auto_increment isn't identical to current database)

I want to create triggers for that, but I'm confused how can I fill id_b, id_c, id_d from table b, c, d in other database

CREATE TRIGGER tr_b
AFTER INSERT
ON db1.table_b
FOR EACH ROW
INSERT IGNORE INTO db2.table_b (column)
VALUES (NEW.column);

The same for table_c and table_d.

CREATE TRIGGER tr_a
AFTER INSERT
ON db1.table_a
FOR EACH ROW
INSERT INTO db2.table_a (id_b, id_c, id_d, column)
SELECT b.id, c.id, d.id, vals.column_a
FROM ( SELECT b.column column_b, c.column column_c, d.column column_d, NEW.column column_a
       FROM db1.table_b b
       JOIN db1.table_c c
       JOIN db1.table_d d
       WHERE b.id = NEW.id_b
         AND c.id = NEW.id_c
         AND d.id = NEW.id_d ) vals
JOIN db2.table_b b ON b.column = vals.column_b
JOIN db2.table_c c ON c.column = vals.column_c
JOIN db2.table_d d ON c.column = vals.column_d;

PS. Not tested, minor errors possible, check and debug.

PPS. The structure assumes that column in tables b, c and d is defined as unique.


SQL Data in form of Date+Time bulk insert into one column Background of problem: To insert 2 seperated fields of data (from a big flat file document of data) with bulk insert. Columns in question that i want to get data from is in its current form in date time format (in flat file document).

I want to: insert these 2 fields of data into one column.

Idea of solution: Is it possible by xml format file point the fields of data into one column?

Example code (One line of data):

2016-01-01  00:00:00            GTM         clocktic

Into column:

   [TStamp] [datetime]     NOT NULL,

With Format file:

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="20"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="\t" MAX_LEN GTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="\t" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="\n" MAX_LENGTH="#" COLLATION="Finnish_Swedish_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="TStamp" xsi:type="SQLDATETIME"/>
  <COLUMN SOURCE="2" NAME="var2" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="var3" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="var4" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="var5" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="var6" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="var7" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="8" NAME="var8" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="9" NAME="var9" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="10" NAME="var10" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="11" NAME="var11" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="12" NAME="var12" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="13" NAME="var13" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

You can process the data using regular T-SQL as part of the bulk loading process, using the OPENROWSET(BULK... function and the XML format file.

INSERT <target> WITH (TABLOCKX)
    <target_column_list>
SELECT
    <columns_and_expressions>
FROM OPENROWSET
(
    BULK <data_file>,
    FORMATFILE = <xml_format_file>,
    ... <other_options>
) AS alias;

The key part is the <columns_and_expressions> in the select clause. This provides a lot of flexibility.

A more concrete example:

INSERT dbo.zz_u_test_TEMP WITH (TABLOCKX)
(
    col1_dirty, col2_dirty, col3_dirty, col4_dirty, col5_dirty,
    col6_dirty, col7_dirty, col8_dirty, col9_dirty, col10_dirty
)
SELECT
    REPLACE(RowSource.col1_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col2_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col3_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col4_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col5_dirty, CHAR(34), SPACE(0)),
    REPLACE(RowSource.col6_dirty, CHAR(34), SPACE(0)),
    RowSource.col7_dirty,
    RowSource.col8_dirty,
    RowSource.col9_dirty,
    RowSource.col10_dirty
FROM OPENROWSET
(
    BULK 'C:\Documents and Settings\Paul\Test.data',
    FORMATFILE = 'C:\Documents and Settings\Paul\format.xml',
    CODEPAGE = 'RAW',
    FIRSTROW = 0,
    LASTROW = 0,
    MAXERRORS = 0,
    ROWS_PER_BATCH = 0
) AS RowSource;

This gives you the potential benefits of minimally-logged fast bulk load, while applying transformations in the SELECT clause.

That is a code example I wrote years ago for a different problem, but it should work for you. If I get a moment to create a table and file (not supplied in the question), I might add a more specific version.


Database structure (MySQL) I have a database called data that is going to contain some data about writers, books and images. I've created these tables so far, but I don't know how to struct the images table. A writer or book can have more than one images, so I thought I could solve this issue by creating a table called img and attach to other tables. Is that a correct solution? If it's not, how can it be improved? I use innodb as a storage engine.

Table name : Book
book_id (Primary Key)
book_name
writer_id (Foreign Key)

Table name : Writers
writer_id (Primary Key)
writer_name
writer_bio

Table name : Images
image_id (Foreign Key for all tables that need to hold an image including book,writers, etc.)
image_url

I have an additional question. I started adding data into my tables, but didn't create the relationships yet. Would it be a problem when I create the relationships? Will the indexes be created automatically?

You need a one to many relationship between Book and Writers to Images.

image_id should be a Primary Key just like writer_id and book_id providing a unique id of that specific image within your data.

Than you can have book_id and writer_id fields in Images table which will hold which book or writer this image belongs to. Or another way would be to add an image_type field as enum for book or writer, and add a parent_id field where you will hold the book_id or writer_id depending on the type.

Yes, you can keep the relationship logic as part of your application away from the database, or you can add foreign constraints and relationships after adding some data given that you don't have anything that break those constraints.


What is the best way to upload custom-parsed data into AWS Aurora PostgreSQL I have a large (5-10 GB) binary file on AWS S3 that will require custom parsing, probably in python.  It is essentially a sequential set of millions of dataframes, all having the same structure.  What is the best way for me to get this data into a severless/hosted AWS Aurora PostgreSQL instance?  So far I have thought of:
 1. I could write to a CSV file and use COPY, but the size would be astronomical
 2. I could send it over the wire in batches of rows
 3. use AWS Glue, though I'm still learning about that.

I could write to a CSV file and use COPY, but the size would be astronomical

You could write the CSV data stream to a pipe rather than a file:

geneate_csv | psql -c '\copy tablename from stdin'

or

\copy tablename from program 'generate_csv'


Is it a problem to have a large TempDB? We have a vendor that has jobs set up to execute a stored procedure that builds a table in TempDB, creates indexes on it, inserts 100s of GB of data into it, does whatever it needs to with the data, then drops the table.  It does this process repeatedly throughout the day.

The server is a VM with 16 processors and 120 GB of RAM.  TempDB has 8 data files that are 100 GB each and 1 log file that's 50 GB.  We determined these sizes after the drive ran out of space a few times in development.  I've got the user db data files, user db log files, as well as TempDB files on their own drives which are all SSDs.  This app is the only one using this server so sharing TempDB isn't a problem.

Is this a problem?  This is the first time I've seen an app that did so much work in TempDB.  What problems should I be watching for with this setup?

Important things :

Depending on your version of sql server, use TF 1117 and 1118 to equally grow all the files and alleviate tempdb contention. Those are safe trace flags to have.
Make sure your tempdb files are all equally sized and set to grow by a fix MB as opposed to Percent growth. I would suggest you preallocate so auto-growth is minimal to none.

Followups:

Run sp_whoisactive @get_plans =1, @get_transaction_info =1 and log it to a table every 1 min or so and see the tempdb usage.
Tell the vendor to have application only get the data that is needed. Have seen vendors abusing tempdb, but once you highlight the issue they will hopefully address it.
Read fully - Comprehensive tempdb blog post series - by Paul from SQLSkills


Performance of query with a range condition and order by We have the following table (in SQLite on Android) which a tree structure (Nested Set model) of words and their frequencies:

lexikon
-------
_id   integer  PRIMARY KEY
word  text
frequency integer
lset  integer  UNIQUE KEY
rset  integer  UNIQUE KEY

And the query:

SELECT word
FROM lexikon
WHERE lset BETWEEN @Low AND @High
ORDER BY frequency DESC
LIMIT @N

I suppose a covering index on (lset, frequency, word) would be useful but I feel it may not perform well if there are too many lset values in the (@High, @Low) range.

A simple index on (frequency DESC) may also be sufficient sometimes, when a search using that index yields early the @N rows that match the range condition.

But it seems that performance depends a lot on the parameter values.

Is there a way to make it perform fast, regardless of whether the range (@Low, @High) is wide or narrow and regardless of whether the top frequency words are luckily in the (narrow) selected range?

I would say give that covering index a try (lset, frequency, word), but you did not give much information. Please post how many rows does your table have, how large in bytes it is, how many maximum rows are you expecting to get back from your query, what's the cardinality of your data?