How do I SELECT INTO new columns? I'm trying to write a query that will put values in three different columns. I have the query pulling the information I need, I just don't know how to split the values out into different columns.

The first column value should display the values where financialinst.name = National Financial Services

The second column value should display the values where financialinst.name is not equal to National Financial Services (DirectValue)

The last column value should be the total of these two columns (National Financial Services + Direct Values)

Here is what I have so far:

SELECT     Household.Name, Account.AccountNumber, Account.Registration, FinancialInst.Name AS [Financial Institution], SUM(Asset.Value) AS Value,
                      FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode,
NFSvsDirect=CASE WHEN FinancialInst.Name ='National Financial Services' THEN 'NFS'
ELSE 'Direct Business' END
FROM         PlanFirmSpecCode INNER JOIN
                      FinPlanSpecCode ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID RIGHT OUTER JOIN
                      Household INNER JOIN
                      Account ON Household.HouseholdID = Account.HouseholdID INNER JOIN
                      Asset ON Account.AccountID = Asset.AccountID INNER JOIN
                      FinancialPlanner ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID LEFT OUTER JOIN
                      FinancialInst ON Account.FinancialInstID = FinancialInst.FinancialInstID ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE     (Account.Closed <> 'Y') AND (Asset.Value > '0')
GROUP BY Household.Name, Account.AccountNumber,  FinancialInst.Name, FinancialPlanner.LastName, PlanFirmSpecCode.SpecialCode, Account.Registration,
                      Account.Description

This would be a good application of an aggregate function and a case statement:

    ;WITH a AS -- CTE for your query
    (SELECT Household.Name,
            Account.AccountNumber,
            Account.Registration,
            FinancialInst.Name AS [Financial Institution],
            SUM(Asset.Value) AS Value,
            FinancialPlanner.LastName,
            PlanFirmSpecCode.SpecialCode,
            NFSvsDirect=CASE WHEN FinancialInst.Name ='National Financial Services' THEN 'NFS'
            ELSE 'Direct Business' END
FROM PlanFirmSpecCode
INNER JOIN FinPlanSpecCode
   ON PlanFirmSpecCode.PlanFirmSpecCodeID = FinPlanSpecCode.PlanFirmSpecCodeID
RIGHT OUTER JOIN Household
INNER JOIN Account
   ON Household.HouseholdID = Account.HouseholdID
INNER JOIN Asset
   ON Account.AccountID = Asset.AccountID
INNER JOIN FinancialPlanner
   ON Household.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
LEFT OUTER JOIN FinancialInst
   ON Account.FinancialInstID = FinancialInst.FinancialInstID
INNER JOIN /*inner join not specified in question, assumed*/ FinPlanSpecCode
   ON FinPlanSpecCode.FinancialPlannerID = FinancialPlanner.FinancialPlannerID
WHERE (Account.Closed <> 'Y')
  AND (Asset.Value > '0')
GROUP BY Household.Name,
            Account.AccountNumber,
            Account.Registration,
            FinancialInst.Name ,
            FinancialPlanner.LastName,
            PlanFirmSpecCode.SpecialCode,
            CASE WHEN FinancialInst.Name ='National Financial Services'THEN 'NFS' ELSE 'Direct Business' END

)

        SELECT SUM(CASE WHEN [Financial Institution] = 'National Financial Services' THEN Value ELSE 0 END) as [FirstColumn],
                   SUM(CASE WHEN [Financial Institution] <> 'National Financial Services' THEN Value ELSE 0 END) as [SecondColumn],
                   SUM(Value) as [ThirdColumn]
            From a

Please let me know if there's any need to clarify further.


Sql Server: A seemingly strange behaviour of BEGIN TRAN - COMMIT First create two tables:

CREATE TABLE xyz (Id INT)
CREATE TABLE abc (Id INT)

Please, observe the following SQL code:

DELETE FROM abc
BEGIN TRAN
EXEC('
INSERT INTO xyz VALUES (1),(2)
DECLARE @x INT = (SELECT Id FROM xyz)
')
INSERT INTO abc VALUES (1)
COMMIT

SELECT * FROM abc

Running it outputs the following error:

Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

And there is no result. All as expected.

Now let us change the error to a syntax error by replacing xyz) with xyz. Here is the error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'xyz'.

But this time there is a result:

Why? What is going on?

The behaviour depends on SET XACT_ABORT and is explained in the following quote from the linked article.

When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
OFF is the default setting.


SQL Server 2012/2014 insufficient memory available in the buffer pool We have bunch of SQL Server 2012 Standard and SQL Server 2014 Standard database instances running on Windows 2012, each have different storage and memory. We are getting:

  Error Code: 802; There is insufficient memory available in the buffer
  pool.

from 3 database servers when we build large SQL Server indexes, and it's happening intermittently, once in 3 or 4 months. The same indexes run fine on servers with same version of SQL Server and only 1/3 of memory and CPU.

SQL Server 2012 Standard Servers

Memory 110GB allocated out of 128 GB. Version 11.0.6 - We get reported the following error when we run heavy index:

  Error Code: 802; There is insufficient memory available in the buffer pool.

Memory 78GB allocated to SQL out of 96GB. Version 11.0.6 - We get reported the following error when we run heavy index:

  Error Code: 802; There is insufficient memory available in the buffer pool.

Memory allocated 18GB out of 24GB. VM. Version 11.0.5

No indexing error

Memory 24GB out of 32GB. Physical. Version 11.0.5

No indexing error

SQL Server 2014 Standard Servers

Memory 56GB out of 64GB physical memory. Version 12.0.5 - We get reported the following error when we run heavy index:

  Error Code: 802; There is insufficient memory available in the buffer pool.

Memory 18GB out 24GB - VM. Version 12.0.5.

No indexing error

I don't think there's much mystery to the the errors occurring with SQL 2012 Standard as that edition only supports 64GB of memory.  Set your max server memory to 64GB and this error should disappear for your SQL 2012 instances.

As for SQL 2014 Standard Edition, the max server memory limit increased to 128GB, so I'll echo Sean Gallardy's question in the comments and ask what else you're running on that VM or if you've over-provisioned the VM host by accident?


which way is better to design shopping Cart Table - SQL Server I want to create a shopping cart in an online store. I have two ways to design shopping cart, but I don't know which one is better.

Solution 1:
create a table like below :

in this way for each product for a user add new row.

note UserId and ProductId are uniqe

Solution 2:

use two tables: shopping cart, cartItem

In this way for each user just create a record in Shoopingcart Table .

What is your opinion?

Option 2 is the cannonical solution for the Order / OrderItem pattern, which a shopping cart is, in essence. It allows the cart to be treated as a whole and distinct from another cart that may be submitted by the same user an arbitrarily short time later. Fulfilment can be recorded against a cart rather than each item individually, if that is what is required.


Will DROP TABLE of an empty table with a large tablespace file cause a long lock? Let's say I have a reasonably large (100GB) INNODB table on a 3-node Percona Xtradb Cluster that uses innodb_file_per_table=1 and has about 80GB buffer_pool per node and uses ext4. I want to drop this large table without causing any global lock. I use this approach:

I delete all the rows from the table with pt-archiver. At this point the table is empty, but the innodb file for that table is still ~100GB.
I issue a DROP TABLE tablename

Will I get any global lock due to either ws-rep or the fact that Innodb has to go through the LRU list and discard the pages which belong to this tablespace?

What version of MySQL are you using?

I'd do a RENAME table then delete rows in chunks (something like DELETE FROM TABLE LIMIT 5000; in a loop) once it's empty you should be able to drop it.

Adjust the LIMIT 5000 up or down depending on how long that operation takes, slave replication falls behind, or it's affecting other queries performance.

If it's an empty table then no, it shouldn't cause a long lock.


How do I get the MySQL binary log coordinates from the binary files? I'm attempting to set up replication for a large (120 GB) MySQL database and I messed up.

I shut down the master, copied the data files, and restarted the master. Now I've set up the slave & started it up, and I realize that I neglected to get the MASTER_LOG_FILE and MASTER_LOG_POS values needed to setup replication.

I still have an untouched copy of the data files. Is there any way to determine MASTER_LOG_FILE and MASTER_LOG_POS from those files?

Related question:

what happens if you set the MASTER_LOG_POS to an earlier point in time?
Am I correct in assuming duplicate queries will be executed, possibly resulting in duplicate data?

Edit

I've been investigating the mysqlbinlog command and discovered what looks like the correct info by executing:

mysqlbinlog --to-last-log --start-datetime='2015-06-21 20:05:00'
    mysql-bin.000006 | grep end_log_pos

The output looks something like this:

... a whole bunch more lines ...

#150621 20:10:21 server id 1  end_log_pos 720159843     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:21 server id 1  end_log_pos 720161877     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:21 server id 1  end_log_pos 720162762     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:22 server id 1  end_log_pos 720164796     Query   thread_id=901489    exec_time=0 error_code=0
#150621 20:10:22 server id 1  end_log_pos 720164815     Stop

I'm guessing I should use one of those last two positions, but I'm not sure which.

Hopefully, you did not start mysqld on the slave yet.

It just so happens that a binlog position is also the binlog size at the end of every binlog event. In your case, you can use the last binlog the slave sees, which I can assume is mysql-bin.000006. Get the filesize and use that as the position.

Therefore, on the Slave you would run

STOP SLAVE;
CHANGE MASTER TO
    MASTER_HOST='...',
    MASTER_PORT=3306,
    MASTER_USER='repluser',
    MASTER_PASSWORD='replpassword',
    MASTER_LOG_FILE='mysql-bin.00006',
    MASTER_LOG_POS=720164815;
START SLAVE;

and that should do it !!!

I have discussed the binlog position being the filesize before

Nov 30, 2012 : How can you monitor if MySQL binlog files get corrupted?
Oct 20, 2011 : How to handle the My SQL DB Master to Master replication when there was a disk space outage

You also asked

  what happens if you set the MASTER_LOG_POS to an earlier point in time?

Yes, it will replay those binlog event and cause issues.


conditional/processing on the dummy data column to create new dummy column Following is my query.

Select [A],[C]
      ,CASE WHEN [A]>19999 and [A]<30000 THEN '12' ELSE '21' END AS 'dummy1'
    ,Substring(CAST(C AS varchar(9)),1,4) as YEARDummy
    ,Substring(CAST(C AS varchar(9)),5,2) as MONTHDummy
    ,Substring(CAST(C AS varchar(9)),7,2) as DAYDummy
    ,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4
From TableYearlyData

I want to do conditional/processing on the dummy data column to create new dummy column. How can I use a dummy column to create a new dummy column?

with the help of CTE

;WITH CTE AS (Select [A],[C]
      ,CASE WHEN [A]>19999 and [A]<30000 THEN '12' ELSE '21' END AS 'dummy1'
    ,Substring(CAST(C AS varchar(9)),1,4) as YEARDummy
    ,Substring(CAST(C AS varchar(9)),5,2) as MONTHDummy
    ,Substring(CAST(C AS varchar(9)),7,2) as DAYDummy
    --,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4
From TableYearlyData)

-- here you can apply your CASE with YEARDummy,MONTHDummy,DAYDummy column
SELECT *,CASE WHEN MONTHDummy>0 and DAYDummy<31 THEN '30dayas' ELSE 'xyz' END AS Dummy4
FROM CTE


How to transpose rows into columns in SQL? Sorry for misleading question but I don't know hot to name my issue.

My db<>fiidle is here.

I have a table with defined as follows:

CREATE TABLE shift (
     worker_name VARCHAR(25),
     log_time DATETIME,
     in_out VARCHAR(3)
);

It contains the following data:

INSERT INTO shift VALUES ('User 1', '2018-05-14 07:24:40.000', 'IN');
INSERT INTO shift VALUES ('User 2', '2018-05-14 11:55:29.000', 'IN');
INSERT INTO shift VALUES ('User 3', '2018-05-14 14:03:26.000', 'IN');
INSERT INTO shift VALUES ('User 1', '2018-05-14 14:05:08.000', 'Out');
INSERT INTO shift VALUES ('User 3', '2018-05-14 20:08:30.000', 'Out');
INSERT INTO shift VALUES ('User 2', '2018-05-14 20:08:36.000', 'Out');
INSERT INTO shift VALUES ('User 4', '2018-05-14 20:09:14.000', 'Out');

And as a result I would like to get:

worker_name | IN                  | OUT
:---------- | :------------------ | :------------------
User 1      | 14/05/2018 07:24:40 | 14/05/2018 14:05:08
User 2      | 14/05/2018 11:55:29 | 14/05/2018 20:08:36
User 3      | 14/05/2018 14:03:26 | 14/05/2018 20:08:30
User 4      |      ----------     | 14/05/2018 20:09:14

How can I achieve that?

This is an example of pivoting:

select worker_name, max(_in) as _in, max(_out) as _out
from (
    select worker_name
         , case when in_out = 'IN' then log_time end as _in
         , case when in_out = 'OUT' then log_time end as _out
    from shift
) as t
group by worker_name;

The idea is to map log_time to _in when in_out = 'IN', ditto for _out. In the outer select an aggregate function is used to filter out nulls. Try running the inner select first to get an idea of what is happening.

In general, the presentation of data is better handled by the application layer. So I would suggest reading the table as is, and let the presentation of the data be handled by the application.


How to return when inserting into multiple tables as part of a views on inert rule I am trying to create rules for my views to insert into multiple tables at once but I need to return the new row and I cannot work how to return from multiple insert statements.

This rule works fine but obviously returns nothing:

create rule template_field_insert as on insert to template_field
do instead (
    insert into public.base (
        id,
        organisation_id,
        last_modified_by,
        armicus_data
    )
    values (
        new.id,
        new.organisation_id,
        new.last_modified_by,
        new.armicus_data
    )
    insert into public.template_field_data (
        id,
        template_id,
        format_id,
        armicus_name,
        description,
        armicus_configuration,
        required
    )
    values (
        new.id,
        new.template_id,
        new.format_id,
        new.armicus_name,
        new.description,
        new.armicus_configuration,
        new.required
    )
)

I tried this but it just throws a syntax error on the first insert:

create rule template_field_insert as on insert to template_field
do instead (
    (
        insert into public.base (
            id,
            organisation_id,
            last_modified_by,
            armicus_data
        )
        values (
            new.id,
            new.organisation_id,
            new.last_modified_by,
            new.armicus_data
        )
        returning *
    ) b, (
        insert into public.template_field_data (
            id,
            template_id,
            format_id,
            armicus_name,
            description,
            armicus_configuration,
            required
        )
        values (
            new.id,
            new.template_id,
            new.format_id,
            new.armicus_name,
            new.description,
            new.armicus_configuration,
            new.required
        )
        returning *
    ) f
    select b.id, f.armicus_name
    from b
    join f on b.id = f.id
)

I have achieved this with CTEs outside of the view but you cannot use NEW inside a CTE so cannot use this approach inside the rule. I also tried using subqueries but I couldn't work out how to structure it because I wasn't sure what to enter in for the FROM clause.

How do I convert this query to be returning?

Model.

Insert data into two tables via view with returning some field values.

Test tables:

create table test1 (id int, val int);
create table test2 (id int, val int);

View:

create view total
as
select t1.id, t1.val val1, t2.val val2
from test1 t1
join test2 t2 on t1.id = t2.id;

Trigger function and trigger which inserts data into tables:

create function fninsert()
returns trigger as $fninsert$
begin
insert into test1 values (new.id, new.val1);
insert into test2 values (new.id, new.val2);
return new;
end;
$fninsert$ LANGUAGE plpgsql;

create trigger trinsert
instead of insert on total
for each row
execute procedure fninsert();

Rule which returns inserted values:

create rule r_insert as
on insert to total
do also
select new.val1, new.val2;

Demo fiddle.


psql: Could not connnect to the server: No such file or directory I know that this is a question that has been posted repeatedly, but I have not been able to find an answer that suits me.

PostgreSQL 9.5 was running fine, someone decided we needed to edit the pg_hba.conf file and restart the service, so we did. From then on, this ominous error message comes up when trying to connect to the databases (we tried to revert the changes and restart the service, but this did not solve it). It looks like the process is running since

service --status-all

Shows that postgres is running, as in [ + ]   postgresql

But, if I run

service postgres status

The output looks like this (the black dot at the begining is actually green):

● postgresql.service - PostgreSQL RDBMS
Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
Active: active (exited) since Fri 2016-11-04 11:49:18 UTC; 27min ago
Main PID: 1079 (code=exited, status=0/SUCCESS)
Tasks: 0
Memory: 0B
  CPU: 0
CGroup: /system.slice/postgresql.service

Nov 04 11:49:18 kissdb systemd[1]: Starting PostgreSQL RDBMS...
Nov 04 11:49:18 kissdb systemd[1]: Started PostgreSQL RDBMS.

However, when I try to look for the PID of the process in ps output... it is not to be found, so my best guess is that something is telling the service is running while not actually being running and I don't know how to get my hands around this.

Also, I don't seem to have access to the pg_ctl, postgres commands, and there is no /var/ directory in /usr/local/, which I guess that should be worrying since almost every given answer I have looked into mentioned it.

Please, somebody help me :(
I can give whatever more information you may need, I don't really know what is going anymore so I don't know for sure that I have given the necessary information for someone to know what the problem is.

Alright, I found what was wrong with all of this.

First, by running /usr/bin/pg_lsclusters I could see that the cluster was indeed not online. Then I checked the /var/log/postgresql/postgresql-9.5-main.log file to look up what was the specific error that had caused the cluster to not be brought up and there I could see that my ph_hba.conf file was, somehow, impossible to read.

I don't know how or when the permissions changed but they did. I chmod 644them and now everything is awesome.


MySql - Remove duplicate record if another column is empty I have a table with many duplicate records . My table looks like this:

ID |First_Name     | Last_Name     |City
1  |    Alan       | Smith     |Los Angeles
2  |    Alan       | Smith     |
3  |    Alan       | Smith     |New York
4  |    Alan       | Smith     |
5  |    Alan       | Smith     |Houston

I can find duplicate records with this query:

SELECT First_Name, Last_Name, COUNT(*)
FROM tab_Name
GROUP BY First_Name, Last_Name
HAVING COUNT(*) > 1

I'd like to remove duplicate records only under following 2 conditions:

The First_Name and Last_Name are the same
the city column is empty(null).

What's the correct way to achieve this? Please let me know if more explanation is needed. Thank you!

I think this is what you want...

I assume your table is called t:

 CREATE TABLE t
 (
     `ID` int PRIMARY KEY,
     `First_Name` varchar(50),
     `Last_Name` varchar(50),
     `City` varchar(50)
 ) ;

 INSERT INTO t
     (`ID`, `First_Name`, `Last_Name`, `City`)
 VALUES
     (1, 'Alan', 'Smith', 'Los Angeles'),
     (2, 'Alan', 'Smith', NULL),
     (3, 'Alan', 'Smith', 'New York'),
     (4, 'Alan', 'Smith', NULL),
     (5, 'Alan', 'Smith', 'Houston')
 ;

This query DELETEs the rows you don't want...

 DELETE
 FROM
     t
 WHERE
     t.id IN
     (
     SELECT
         *
     FROM
         (
         SELECT
             id
         FROM
             t AS t1
         WHERE
             City IS NULL
             AND
             EXISTS
             (
                  SELECT 1
                    FROM t AS t2
                   WHERE     t2.`First_Name` = t1.`First_Name`
                         AND t2.`Last_Name`  = t1.`Last_Name`
                         AND t2.`City` IS NOT NULL
                         AND t2.`ID` <> t1.`ID`
             )
         ) AS s0
     ) ;

 SELECT * FROM t

 ID | First_Name | Last_Name | City
 -: | :--------- | :-------- | :----------
  1 | Alan       | Smith     | Los Angeles
  3 | Alan       | Smith     | New York
  5 | Alan       | Smith     | Houston

dbfiddle here

--

NOTEs:

The need of a subquery within of a subquery is a limitation of MySQL (most SQL databases don't need it). See You can't specify target table 'NAME' for update in FROM clause
I've assumed that you wouldn't want to delete any row if there is not one with a City. I.e.: if you have (15, 'John', 'Doe', NULL) and (16, 'John', 'Doe', NULL) you leave both on the table.


Switching out of one scheme into another I have a table named dbo.messages.  We are expecting a lot of data in this table.  To keep things to a reasonable level we are looking at a weekly partitioning on that table.  What I would like to do is take the oldest weekly partition, once a week, and switch it out.  I have it set up that a each week will be on its own file/filegroup.  This in itself isn't a big deal.

What I'd then like to do is take that switched out table, and switch it into an Archive copy of the table.  Do I need a separate partition function and scheme for the archive table?  Are there any gotchas with this?

I was able to figure it out on my own.  If you switch out from a partitioned table to a file group, you can then switch that table into a partitioned table with another partition scheme.

Here's my proof of concept:

USE [master]
GO

    CREATE DATABASE [test]
     CONTAINMENT = NONE
     ON  PRIMARY
    ( NAME = N'test', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\test.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [a_fg]
    ( NAME = N'a_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\a_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [archive_l_null_fg]
    ( NAME = N'archive_l_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\archive_l_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [archive_r_null_fg]
    ( NAME = N'archive_r_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\archive_r_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [b_fg]
    ( NAME = N'b_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\b_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB ),
     FILEGROUP [main_l_null_fg]
    ( NAME = N'main_l_null_f', FILENAME = N'S:\SQLData01\MSSQLSERVER\Data\main_l_null_f.ndf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 102400KB )
     LOG ON
    ( NAME = N'test_log', FILENAME = N'T:\SQLLog01\MSSQLSERVER\LOG\test_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 102400KB )
    GO

    USE [test]
    GO

    CREATE PARTITION FUNCTION main (datetime)
        AS RANGE RIGHT FOR VALUES
        (
            '2018-1-1',
            '2018-2-1'
        )

    CREATE PARTITION FUNCTION archive (DATETIME)
        AS RANGE RIGHT FOR VALUES
        (
            '2099-01-01'
        )

    CREATE PARTITION SCHEME main_ps
        AS PARTITION main TO
        (
            'main_l_null_fg',
            'a_fg',
            'b_fg'
        )

    CREATE PARTITION SCHEME archive_ps
        AS PARTITION archive TO
        (
            'archive_l_null_fg',
            'archive_r_null_fg'
        )

    CREATE TABLE dbo.main_table
    (
        PK_ID INT not null IDENTITY(1, 1),
        CreatedDate datetime not null
    ) ON main_ps (CreatedDate)

    CREATE TABLE dbo.archive_table
    (
        PK_ID int not null,
        CreatedDate datetime not null
    ) ON archive_ps (CreatedDate)

    INSERT INTO dbo.main_table ( CreatedDate )
    VALUES ( '2018-01-01' ),
        ( '2018-01-03' ),
        ( '2018-01-07' )

    CREATE TABLE dbo.switchout_table
    (
        PK_ID INT not null,
        CreatedDate DATETIME NOT null
    ) ON a_fg

    ALTER TABLE dbo.switchout_table
    ADD CONSTRAINT CK_SOT CHECK (CreatedDate >= '2018-01-01' AND CreatedDate < '2099-01-01')

    ALTER TABLE dbo.main_table
    SWITCH PARTITION 2 TO dbo.switchout_table

    ALTER PARTITION FUNCTION main()
        MERGE RANGE ('2018-01-01')

    ALTER PARTITION SCHEME archive_ps
    NEXT USED a_fg

    ALTER PARTITION FUNCTION archive()
        SPLIT RANGE ('2018-01-01')

    ALTER TABLE dbo.switchout_table
    SWITCH TO dbo.archive_table PARTITION 2

    DROP TABLE dbo.switchout_table


Modeling a three-way association with optional relation Business rules

I have three tables (Parties, Categories and Products) which representing the following relationships:

A product is classified by zero-one-to-many categories
A category classifies zero-one-or-many products

Then, I have the party relationships:

A product is classified by one-to-one party
A party classifies one-to-many products

In other words, a product doesn't have to be assigned a category, but they have to be assigned a party respectively.

The party_id for a Category must match the party_id for a Product in order to relate.

EDIT

Following is a correction to the business rules above, based on @damir-sudarevic's solution proposal:

Category is defined by a party.

Each category is defined by exactly one party.
Each party may define more than one category.

Product is classified by a party.

Each product is classified by exactly one party.
Each party may classify more than one product.

Product is classified in a category by a party.

Each product may be classified in more than one category.
More than one product may be classified in the same category.
A product is classified by a party in a category, then that category is defined by that party.

Design proposals

I have based my first design on the proposal found here, but it's not entirely applicable since I want to enforce party_id for both Products and Categories respectively and in the relation.

I have made a second proposal that simplifies the design somewhat, but I'm not sure how to enforce the party_id to the Product-Category relation.

SQL based on latest design

Based on the comments and solution proposals, I have added a simplified SQL to create the tables and their relations.

CREATE TABLE IF NOT EXISTS parties (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (id));

CREATE TABLE IF NOT EXISTS categories (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  name_key VARCHAR(255) NOT NULL,
  party_id INT(10) UNSIGNED NOT NULL,
  parent_id INT(10) UNSIGNED NOT NULL DEFAULT 0,
  PRIMARY KEY (id, party_id),
  INDEX fk_categories_parent_category_idx (parent_id ASC),
  UNIQUE INDEX name_key_UNIQUE (name_key ASC, party_id ASC),
  INDEX fk_categories_party_idx (party_id ASC),
  CONSTRAINT fk_categories_parent_category
    FOREIGN KEY (parent_id)
    REFERENCES categories (id)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT fk_categories_party
    FOREIGN KEY (party_id)
    REFERENCES parties (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS products (
  id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  party_id INT(10) UNSIGNED NOT NULL,
  product_code VARCHAR(50) NOT NULL,
  PRIMARY KEY (id, party_id),
  UNIQUE INDEX product_code_UNIQUE (product_code ASC, party_id ASC),
  INDEX fk_products_party_idx (party_id ASC),
  CONSTRAINT fk_products_party
    FOREIGN KEY (party_id)
    REFERENCES parties (id)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

CREATE TABLE IF NOT EXISTS product_category (
  product_id INT(10) UNSIGNED NOT NULL,
  category_id INT(10) UNSIGNED NOT NULL,
  party_id INT(10) UNSIGNED NOT NULL,
  PRIMARY KEY (product_id, category_id),
  INDEX fk_product_category_product_idx (product_id ASC, party_id ASC),
  INDEX fk_product_category_category_idx (category_id ASC, party_id ASC),
  CONSTRAINT fk_product_category_product
    FOREIGN KEY (product_id , party_id)
    REFERENCES products (id , party_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT fk_product_category_category
    FOREIGN KEY (category_id , party_id)
    REFERENCES categories (id , party_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE);

Question

How can I setup the three-way association table correctly to avoid the risk of having an application layer assigning a product to a category without enforcing the party_id?

Using your Model #1, I believe the database itself can be made to enforce the stated constraints (i.e., a category and a product may only be related when they are both related to the same party) using the following constraints.

The primary key for category_party consists of category_id and party_id. This creates an optional many-to-many relationship between categories and parties. This implies the following.

A category is classified by zero-one-to-many parties.
A party classifies zero-one-to-many categories.

The primary key for product_party consists of product_id and party_id. This creates an optional many-to-many relationship between categories and parties. This implies the following.

A product is classified by zero-one-to-many parties. This will be overridden below to meet your stated requirements.
A party is classified by zero-one-to-many products.

There is unique constraint on product_id in the product_party. This makes the relationship between party and product a one-to-many instead of many-to-many. This implies the following.

A product is classified by one-to-one party, overriding the previously-stated cardinality of this relationship.

The primary key of product_category_assignment consists of category_id, product_id and party_id.
The foreign key from product_category_assignment to category_party consists of category_id and party_id
The foreign key from product_category_assignment to product_party consists of product_id and party_id
The constraint that a category and a product can only be related when they are related to a common party is enforced because product_category_assignment only contains one party_id which is used in both foreign key relationships.

I believe that the only requirement not covered here is that a product is required to have a related party (i.e., party is not optional).

Using Model #2 would require much more code (i.e., an ugly stored procedure?) to enforce the constraint because the structure of the database will allow the following.

The category can be have one party_id (e.g., 1).
The product can have a different party_id (e.g., 2).
The product_category can have yet a third party_id (e.g., 3).

I do want to point out that there appear to be some unstated requirements here that could have some minor bearing on my answer above. For example...

What is the correct cardinality of the relationship between category and party? The two models you presented are different in this respect.

Model #1 allows a category to be related to zero, one or many parties.
Model #2 only allows for zero or one party for each category.

Is a category required to have (at least) one party or is that relationship optional? This is not clear from your description or either of your models.

I have always said that there are three things that are important in a data model: details, DeTaiLs and DETAILS. Sometimes nailing down the details of every relationship around the complexity can help you discover the best way to design the data model. Sometimes, complex relationships like this may point to missing entities (tables). Sometimes, complex requirements defy enforcement using only database structural constraints and require a bit of code (e.g., a stored procedure?) to finish the job.

Hope that helps.


MS SQL Server CPU load goes up dramatically when turning on 2014 features by setting compatibility level I'm running some Microsoft SQL Server 2014 load testing on a decently spec'ed machine. I'm certainly not an expert on the SQL Server tuning specifics, but with the bits an pieces I've gathered from various places I think the setup is quite OK.

One element in the performance does puzzle, hence I'm reaching out here. The database that I'm testing against was built up historically and was originally created against 2008r2. The compatibility setting was carried over from that version. I've raised that setting last week in two stages, but when I raised it from 2012 to 2014 compatibility, all of sudden the CPU load went through the roof.

When changing this setting back an forth it seems to be systemic. I can run X simulated users (web app in front of the DB) on an 8 core database with the 2012 setting. If I raise it to 2014 I only get away with 60% of X simulated users. And the other way around for X users the batch requests number is 40% lower for the 2014 setting.

As I understand, switching compatibility level back doesn't revert the DB but only turns off features. This situation seems to point to a feature that eats CPU with no apparent gain, so I was wondering which feature that could be and if there's a way to turn of that specific feature separately?

Other posts on serverfault have pointed towards rebuilding the indexes after an upgrade, I've done so, but that doesn't really seem to make a difference.

It's probably important to note that the application at hand generates a lot of queries that are not prepared statements. This is probably leading to the high number of compilations I see in perfmon (compilations/batch requests : 0.25). I can't do too much about that, so telling me to do something about that first won't really help me forward.

I would suspect the new cardinality estimator as a responsible for this behaviour, you can read up on it in the msdn article

It is clearly stated that the new cardinality estimator takes effect when you put the database in the new compatibility mode.

  Upgrade all existing databases to use the new cardinality estimator.
  To do this, use ALTER DATABASE Compatibility Level (Transact-SQL) to
  set the database compatibility level to 120 or higher.

There are trace flags that control this behavior.
If you want to use the new cardinality estimator when the database is in the old compatibility mode you can enable trace flag 2312
If you want to use the old cardinality estimator when the database is in the new compatibility mode you can enable trace flag 9481

If you can isolate the problematic query you could try to tune it so it performs well with the new cardinality estimator or if that doesn't work you can use the QUERYTRACEON keyword on that query only. That is also documented in the msdn link.

Also note that when, setting a trace flag you might need to use DBCC TRACEON (<traceflag>,-1) where the -1 sets it globally.


Is rowversion unique across the database? Or per table? If I have a rowversion on two different tables in a database, are they guaranteed to be unique across both tables? If not, is there any way to set this behaviour?

I have a view that joins two tables together, I'm wondering if this behaviour is guaranteed, so that I could take the largest row number from either table, to form a rowversion for the view.

According to this link, it 'should' be unique across all tables in a database.

Here is the 'should' part - there is a caveat in the documentation:

  Duplicate rowversion values can be generated by using the SELECT INTO
  statement in which a rowversion column is in the SELECT list. We do
  not recommend using rowversion in this manner.

And here is a summary of the information in the link:

  Each database has a counter that is incremented for each insert or
  update operation that is performed on a table that contains a
  rowversion column within the database. This counter is the database
  rowversion. This tracks a relative time within a database, not an
  actual time that can be associated with a clock. A table can have only
  one rowversion column. Every time that a row with a rowversion column
  is modified or inserted, the incremented database rowversion value is
  inserted in the rowversion column.


set -x equivalent for the Postgres cli/console/prompt/repl Is there a "set -x" for the Postgres prompt/repl ?

In bash when you want to execute a set of commands, and come back to check it after lunch you can do

set -x
command1
command2
command3

and bash prints out the command to STDOUT just before it executes it.
this is helpful to see, what's taking a long time, which did line did it last execute, etc.

Similarly, inside the psql console, I also do sets of long running commands:

VACUUM FULL VERBOSE ANALYZE events;
REINDEX VERBOSE TABLE events;

Can I do

set -x
VACUUM FULL VERBOSE ANALYZE events;
REINDEX VERBOSE TABLE events;

so that when I come back from lunch I can see which one of my commands was the last one it did.


Calculate stock value with PostgreSQL I'm working on inventory system web app. It is based on latest Ruby on Rails and PostgreSQL. I need to track products stock values base on purchases and sales quantities. You can check out my simplified db schema (also shown below).
Table products has a stock field. This field must be calculated from products' total purchases and sales quantity.
SUM(purchases.quantity) - SUM(sales.quantity)
I'm looking for simple, performant and most important race conditions safe solution. My current (but not final) idea is to use db triggers here. But I have no mind how to implement them the right way.
CREATE TABLE products (
    id INT GENERATED ALWAYS AS IDENTITY,
    name varchar,
    stock INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    PRIMARY KEY (id)
);

CREATE TABLE purchases (
    id INT GENERATED ALWAYS AS IDENTITY,
    quantity INT NOT NULL DEFAULT 0,
    product_id INT NOT NULL,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id)
);

CREATE TABLE sales (
    id INT GENERATED ALWAYS AS IDENTITY,
    quantity INT NOT NULL DEFAULT 0,
    product_id INT NOT NULL,
    CONSTRAINT fk_product FOREIGN KEY (product_id) REFERENCES products (id)
);

INSERT INTO products (name)
        VALUES('Super Power');

INSERT INTO purchases (product_id, quantity)
        VALUES(1, 2), (1, 3), (1, 2);

INSERT INTO sales (product_id, quantity)
        VALUES(1, 1), (1, 2), (1, 2);

SELECT
    *
FROM
    products

I'm not really sure if you have much of an issue, as long as you update the products.stock field every time you either INSERT INTO the purchases or sales tables. No need for a Trigger in this scenario. Also you should ensure your update on the products.stock happens in the same Transaction as your INSERT INTO the purchases and sales tables.
Alternatively you could also design your schema to not store the stock field on the products table. Rather create a View that joins products, purchases, and sales together, grouped on the product and aggregates the difference in purchases and sales per product as its stock, that way it's instantly always up to date whenever the purchases or sales change.


How to fetch records from table which has csv Please guide me about following case. I need to fetch records from a table which has a column like shown in the picture. I need to fetch records which has 065 and AMT campaigns only.

I tried:

SELECT * FROM table WHERE Campaigns LIKE '%AMT%' OR '%065%'

Unfortunately, it is not working. Data should not be duplicated.

Easiest way to achive this could be:

SELECT * FROM table WHERE Campaigns LIKE '%AMT%' OR Campaigns LIKE '%065%'

You can also look at this answer:

https://dba.stackexchange.com/a/170603/74024


Alternatives for SSMS solutions & projects? According to the official SQL Server Documentation, the use of "Solutions, Projects, and Items" is deprecated.  A conspicuous (though somewhat cryptic) warning banner at the top of the MSDN page states:

  "This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature."

Unfortunately, no suggestions for alternative storage/container strategies are offered. So what is the recommended way for storing and packaging the various scripts, queries, and files that make up your database application? I would also be very interested to know if any of you are acually using the Solutions or Projects framework in your current work, and what you use them for.

[Note: I realize that I could use VS2010 for this functionality, but I am interested in SSMS-based approaches (for the reasons expressed in the answer to this question).]

Reading between the lines, I guess the expectation/plan was for the SQL Server Data Tools (SSDT) project type to be extended to SSMS for the release of SQL 2012. Evidently that didn't happen, so we're stuck with the (frankly dreadful) SSMS interpretation of a project.

If you want something to manage your database source, you should be diving deep into SSDT. I'm currently using it for one greenfield project and also moving a large brownfield database to it. Minor niggles and pain points but no show stoppers and we're getting payback on the time invested already.

Jamie Thomson, who blogged extensively on VS2010 Database Projects (the predecessor), has started to publish articles on SSDT and appears to be as big a fan as I am. Bob Beauchemin is another early adopter worth following.

If you want to organise a collection of random scripts (rather than a full database build) you can but support isn't quite "first-class". To do so, create a standard SSDT project type and change your scripts to compilation type "None" in the file properties.


Oracle 11G - Index Rebuild VS GATHER_TABLE_STATS Quick question, after populating my table, I noticed through my explain plan that indexes, in most cases, only seems to take effect after I perform GATHER_TABLE_STATS:

Exec DBMS_STATS.GATHER_TABLE_STATS(ownname => 'USER/SCHEMA', tabname => 'MYTABLE', cascade=> true, degree=> 8);

The "CASCADE" attribute was defined as: Gather statistics on the indexes for this table. Index statistics gathering is not parallelized. Using this option is equivalent to running the GATHER_INDEX_STATS Procedure on each of the table's indexes.

So my question is, should I run the REBUILD of my indexes prior to GATHER_TABLE_STATS, as I'm assuming gathering the index stats and rebuilding them IS NOT the same thing, or is it?

At the moment, I run dbms_index_utl.build_table_indexes to rebuild my indexes followed by the GATHER_TABLE_STATS,  I just don't know if it's necessary, mind you my explain plan seems to indicate it is, otherwise, unless I add a hint to my query, the index won't get used.

Gathering statistics and rebuilding indexes are two completely separate things.

It is exceedingly rare that an index in Oracle needs to be rebuilt so any process that is regularly rebuilding an index is highly suspect.  I strongly suspect that rebuilding the indexes is unnecessary.  And I would strongly suggest spending some quality time reading through Richard Foote's Index Internals - Rebuilding the Truth to understand why rebuilding indexes is so rarely needed and those few cases where it is necessary.
You do need to gather statistics on tables and indexes when there are substantial changes in data volumes or distributions in order to give the optimizer the information it needs in order to determine the appropriate query plan.  If you create an index on an empty table and subsequently insert millions of rows into the table, you'll need to gather statistics to tell the optimizer that the table is no longer 1 empty extent.  Unless you disabled the automatic job, Oracle will also automatically gather statistics on any objects that have changed substantially since the last time statistics were gathered during the early morning hours every day.


Are older versions of MS SQL Server DEVELOPER edition now free? Microsoft SQL Server Developer Edition is now free  https://blogs.technet.microsoft.com/dataplatforminsider/2016/03/31/microsoft-sql-server-developer-edition-is-now-free/

I see that I can download 2016 and 2014, but are there downgrade rights to use 2012 and earlier versions?


Change naming conventions in SQL Server In SQL Server 2014 we need to change naming convention for indexes and  keys. The old naming convention is not following any standards. My questions are:

How can we change the naming convention for 200 tables in a production server?
Can the naming be changed at once using some scripts, or do we have to change them one by one?

Edit:
Keys:
Primary or Foreign Key

You can use sp_rename to change the name of procedures, columns, tables, indexes, constraints. You'll have to do each object individually (although you can create a single script that renames the objects), and I would highly recommend running through a test environment first to ensure that you do not run into any problems.


Is it better to separate a big query into multiple smaller queries? There are situations which require having really big query joining several tables together with sub select statements in them to produce the desired results.

My question is, should we consider using multiple smaller queries and bring the logical operations into the application layer by querying the DB in more than one calls or it's better to have them all in one go?
For example consider the following query:

SELECT *
FROM   `users`
WHERE  `user_id` IN (SELECT f2.`friend_user_id`
                     FROM   `friends` AS f1
                            INNER JOIN `friends` AS f2
                              ON f1.`friend_user_id` = f2.`user_id`
                     WHERE  f2.`is_page` = 0
                            AND f1.`user_id` = "%1$d"
                            AND f2.`friend_user_id` != "%1$d"
                            AND f2.`friend_user_id` NOT IN (SELECT `friend_user_id`
                                                            FROM   `friends`
                                                            WHERE  `user_id` = "%1$d"))
       AND `user_id` NOT IN (SELECT `user_id`
                             FROM   `friend_requests`
                             WHERE  `friend_user_id` = "%1$d")
       AND `user_image` IS NOT NULL
ORDER  BY RAND()
LIMIT %2$d

What's the best way of doing it?

I am going to disagree on large and complicated queries with datagod here.  I see these only as problems if they are disorganized.  Performance-wise, these are almost always better because the planner has much more freedom in how to go about retrieving the information.  However, large queries do need to be written with maintainability in mind.  In general, I have found that simple, well-structured SQL to be easy to debug even when a single query goes on for 200+ lines.  This is because usually you have a pretty good idea of what kind of problem you are dealing with so there are only a few areas in the query that you have to check.

The maintenance problems, IME, come in when the structure of SQL breaks down.  Long, complex queries in subselects impairs readability and troubleshooting, as do inline views, and both of these should be avoided in long queries.  Instead, use VIEWs if you can (note if you are on MySQL, views do not perform all that well, but on most other db's they do), and use common table expressions where those don't work (MySQL doesn't support these btw).

Long complex queries work pretty well both from a maintainability and performance case where you keep your where clauses simple, and where you do as much as you can with joins instead of subselects.  The goal is to make it so that "records aren't showing up" gives you a few very specific places in the query to check (is it getting dropped in a join or filtered out in a where clause?) and so the maintenance team can actually maintain things.

Regarding scalability, keep in mind that the more flexibility the planner has, that's a good thing too....

Edit:  You mention this is MySQL, so views are unlikely to perform that well and CTE's are out of the question.   Additionally the example given is not particularly long or complex so that's no problem.


postgresql double count query I have a table with 3 columns: key, status and rank.
status contains numbers and rank contains t or f.
key is auto numbered and irrelevant here.

my goal is to count how many in each status are t and how many false
for example:

if my data is (key, status, rank)

1 10 f
2 10 t
3 10 t
4 11 t

then result will be : (status, rank, count value)

10 f 1
10 t 2
11 t 1

how do i do that?

This is a simple group by query.

select status,
       rank,
       count(*) as cnt
from the_table
group by status, rank
order by status, rank;


Performance of inner join query for certain condition I have one question about inner join query in SQL Server 2008. Is there any situation where NOT to use inner joins.
I have one scenario please tell me that here inner join is fruitful or not.

I have 2 tables

Table1 Columns: T1Id, Code and Name1, (Here Code is unique column)
Table2 Columns: T2Id, T1id(reference from Table 1), Name2, Type

Now there can be millions of records in both table. Now user pass the Code and I have to search the Name2 3 times in same procedure for diff condition.
I can do it with 2 scenario.

Scenario 1

I can store T1Id in temp variable

Declare @Id Int
Select @Id=Top 1 T1Id from Table1 Where Code='ABC'

Step 2: I will write all my queries

Select Name2 From Table2 Where T1id=@Id and Type='A'
Select Name2 From Table2 Where T1id=@Id and Type='B'
Select Name2 From Table2 Where T1id=@Id and Type='C'

Scenario 2:

Select Name2
from Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='A' and Table1.Code='ABC'

Select Name2
From Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='B' and Table1.Code='ABC'

Select Name2
From Table2 Inner join Table1 on Table1.T1Id = Table2.T1Id
Where Table2.Type='C' and Table1.Code='ABC'

Please tell me which one is good to use. Both tables can have more than millions records in future.

Run one query, make sure you have good indexes

Select Name2
From Table2 Inner join Table1 on Table1.T1ITable2.T1Id
Where Table2.Type IN ('A', 'B', 'C') and Table1.Code='ABC'

A graphical query plan will show where you need indexes

A million rows isn't much and well within capability of SQL Server


DBCA silent database creation rolls back prompt when asked for sys password I'm experiencing a strange behavior when trying to perform a silent database creation with 12.1 DBCA.

My command:

dbca -silent -createDatabase -templateName pbw4.dbt -responseFile NO_VALUE -gdbname pbwdup -sid pbwdup -emConfiguration NONE -totalMemory 2048

After running this command, I get a prompt asking me for the SYS password, as expected. However, I'm unable to actually enter in the password because my command prompt continuously rolls back, it looks like it is just automatically deleting every text on my screen. I have to ctl+c out of it to regain control.

Below is an attempt to illustrate the behavior:

Does anyone have any idea why this is happening?

Bug 21163674 : DBCA SILENT MODE DELETES LINES FROM SCREEN AND DISPLAYS NO PROMPT

  This happens when
  XTERM is run with both "Enable Reverse Wraparound" & "Enable Auto
  Wraparound" enabled. As soon as one of these is disabled, then dbca
  functions correctly.

  ...

  With putty , settings -> terminal, uncheck 'Auto wrap mode initially on'.

  Another workaround is to use silent installation with a response file.


Have a separate database for data warehousing? I have built a typical web application using PHP and MySQL for enterprise usage.

The MySQL database is 3NF as much as possible.

However, as time goes by, the requests for the data appear to be more for a data warehouse so that they can slice and dice the data in whatever way they want.

Hence I picked up a book the 3rd Edition Data Warehouse Toolkit of the Kimball Group.

Inside the authors described a Kimball DW/BI Architecture and using star schema for dimensional and fact tables for DW/BI purposes. See below.

My question is, since I am most familiar with PHP and MySQL, should I have the data warehouse star schema in a separate database from the original 3NF database?

No commercial BI tool will be used. Anything that looks like an OLAP or BI would likely be an open source software that I may augment myself.

Please advise.

EDIT

My entire 3NF MySQL database is currently about 20mb so far growing about 1 mb every month or so.

In response to your reply to my comment, I mentioned some of the areas in which PostgreSQL shines for DW work - notably CTEs (Common Table Expressions - AKA the WITH clause) and Window functions (AKA the OVER clause).

Without these in MySQL, you'll end up writing your own hacks to emulate these (and, no disrespect, but it'll take a while for your code to be bug free), whereas with PostgreSQL you get them out of the box. PostgreSQL also has superior GIS support (should you require it) and better JSON - only experimental in MySQL.

Take a look here

  PostgreSQL has traditionally focused on reliability, data-integrity
  and integrated developer-focused features. It has an extremely
  sophisticated query planner, which is capable of joining relatively
  large numbers of tables efficiently.

  MySQL, on the other hand, has traditionally focused on read-mostly web
  apps, usually written in PHP, where the principal concern is with
  optimising simple queries.

DW type apps need far more of the former than the latter.

Also, check this out - a very recent comparion of the two systems. My take on this is that PostgreSQL is the better of the two, esp. for DW work.

You could peruse these pages for PostgreSQL and MySQL features. There is a certain "religious war" element here, and I'm mindful of the fact that you already have MySQL experience which may be a clincher for you. I wish you all the best with your DW project.

[EDIT in response to OP's comment]

I would certainly urge you to have separate databases for your OLTP and your OLAP work - if that is within your budget. OLTP and OLAP are fundamentally different and if you mix them you'll have many conflicts - I know the pain caused by "managers" insisting on running reports against a live system during the day - so if I were you, I would have MySQL as my OLTP system and PostgreSQL as my OLAP.

But whatever configuration you choose, I strongly suggest separate servers.

If I were forced to choose one database and one only, it would be PostgreSQL without hesitation. MySQL was essentially lucky and in the right place at the right time with the right software during the first internet boom. IMHO, PostgreSQL is undoubtedly technically superior.

I bear MySQL no ill-will - I have used it a lot (clients!) and find that if you can live with or workaround its quirks, it can be quite performant and has some interesting features - notably the storage engine choices one can make (which one can't with PostgreSQL).

In the DW sphere for MySQL there are two interesting developments which may bode well for it in this area in the future - these are columnar store engines - Infinidb and Infobright. The problem at the moment is that Infinidb appears to have died - even though the code is still available, the installation guide has disappeared and ICE (Infobright Community Edition) is essentially crippleware. You may wish to evaluate the Entreprise edition.

I urge you to set up a few test cases - imagine what sort of reports you will be running and try them on both systems. Think especially about the complex reports required for OLAP systems - I believe that you will find the far richer PostgreSQL SQL language to be a persuasive argument in its favour.


Is my.cnf for mysql 5.5 is suitable and well tuned for below hardware spec? My Hardware Spec

Technology: Ivy Bridge
CPU: Intel Xeon E3 1245v2
Intel Smart Cache: 8MB
Cores / Threads: 4 / 8
Frequency: 3.4GHz+ / 3.8GHz Turbo Boost
RAM: 32 GB DDR3
Hard disk: 2x 2TB SATA3
Raid 1 by software
Bandwidth: 100 Mbps guaranteed

OS

ubuntu 12.04.2 LTS 64 bit server edition

Mysql

version 5.5.29 , 64 bit

My Application Architecture

https://stackoverflow.com/questions/11272776/using-phusion-passenger-nginx-running-same-rails-app-with-multiple-instance

So my server contains 100 rails apps with 100 mysql databases, each database having around 100 tables

My Traffic To The Server

10 to 200 (max) request per second

3000 to 10,000 (max) request per day

Connectivity Between Mysql and Rails

I use sockets  socket = /var/lib/mysql/data/mysql.sock

my.cnf generated by https://tools.percona.com/wizard for the above hardware spec

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/lib/mysql/data/mysql.sock

[mysqld]

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/lib/mysql/data/mysql.sock
pid_file                       = /var/lib/mysql/data/mysql.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
innodb                         = FORCE
innodb_strict_mode             = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/data/

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/data/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 10240

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 26G

# LOGGING #
log_error                      = /var/lib/mysql/data/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/data/mysql-slow.log

I have seen that innodb_buffer_pool_size = 26G, so in 32 GB RAM at least 27 to 28 GB will used by mysql, so there is only less space for running 100 rails apps, so please somebody will edit and optimize my mysql config my.cnf posted in pastebin, to get enough space and speed for running both mysql and rails application.

I planned to use 100 databases connecting with 100 rails application in a single machine, so i use mysql sockets for connectivity, whether will have performance improvement compared to connecting using mysql with ports.

Update

More info about application

My application is not an mission critical application, it's just an student information system for schools. Mysql backup to remote ftp scheduled through webmin at 2 times per day , so i planned to put 100 schools in single server, so 100 rails apps + corresponding 100 mysql databases

Currently the application is not running , now i am at production set-up stage, so i need to have some suggestions  for my.cnf considering equal resource handling for rails and mysql.

There are some options you will need to consider
InnoDB Buffer Pool
The reason 26G was picked is that you have 32GB of RAM and 80% of that is 25.6 G. Since you mentioned that you will have 100 databases and 100 applications making this a multitenant DB Server, you are going to have to get the InnoDB Buffer Pool just right.
Please run this query:
SELECT IFNULL(B.engine,'Total') "Storage Engine",
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(
FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(
FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') "Table Size" FROM
(SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM
information_schema.tables WHERE table_schema NOT IN
('mysql','information_schema','performance_schema') AND
engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,
(SELECT 3 pw) A ORDER BY TSize;

This will tell you how much space is currently occupied by your MySQL instance. Whatever the total of InnoDB Data Size and Index Size is, that is what you use. If that total is over the 80% limit then you must you the 80% (leaving innodb_buffer_pool_size at 26G).
Since you have a quad-core server, set innodb_buffer_pool_instances to 4.
InnoDB Transaction Log Files
Since 26G was selected as innodb_buffer_pool_size, you are going to need the biggest possible transaction logs. The value 512M was probably picked for innodb_log_file_size because there is nothing to suggest the amount of transaction data (in bytes) that will actually be processed.
To resize your transaction logs
mysql -u... -p... -e"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Next edit my.cnf, replacing
innodb_log_file_size           = 512M

with this
innodb_log_file_size           = 2047M

Then, replace the transaction logs like this
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0.bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1.bak
service mysql start

After a few months of peak activity, you could then run this query during a peak:
SET @TimeInterval = 300;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL,@MB_WL,@GB_WL;

Based on what comes back, you should resize the transaction logs again.
Please see my earlier posts on doing this:

Jul 21, 2012 : InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
Feb 16, 2011 : How to safely change MySQL innodb variable 'innodb_log_file_size'?

Multicore Engagement
When the InnoDB Plugin was introduced in MySQL 5.1.38, it set the world of MySQL on fire. Why? Because InnoDB was single threaded. You had to install the plugin to have new setting that allowed InnoDB to use multiple cores.
Rather than writing something lengthy, please read my earlier posts of tweaking MySQL 5.5 to have InnoDB utilitze multiple cores:

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance


What steps we need to perform from DB end in case of storage migration? What are the impacts / steps from DB2 DB side before VMware SvMotion Storage migration ?

The impact should be minimal, if not non-existent. I've had several databases of varying sizes (both in storage and cluster) and types (MySQL, Postgres, Cassandra, to name a few) migrated between different storage environments with little impact to the end user. The VM always stays online and is usable just as it was prior to the vMotion.
If it's a:

manually triggered event I would say, at minimum, have a fresh backup available somewhere off-server in case there is a problem that requires a recovery. Otherwise, watch your applications and logs on the DB server as the Storage vMotion is occurring  and react accordingly.
automatic event because of storage failure, storage utilization, etc., you should be sure your backups are automated and saved off-server so you can recover should there be a problem. VM snapshots won't save you here should there be a problem.


Internal query on CDEF$ and CON$ very CPU bound I am checking an application (Infor ERP LN) that loads a lot of data into an oracle 10.2 database. While checking one of the user sessions with SQL_TRACE and its output with tkprof, I found that the most time consuming query is one internal to oracle. (I assume it is an internal one since the application does not execute that query.) Here it is:

select c.name, u.name
from
 con$ c, cdef$ cd, user$ u  where c.con# = cd.con# and cd.enabled = :1 and
  c.owner# = u.user#

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     5901      0.09       0.09          0          0          0           0
Execute   5901      0.26       0.24          0          0          0           0
Fetch     5901    978.68     969.36          0   65955477          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    17703    979.04     969.71          0   65955477          0           0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  HASH JOIN  (cr=11177 pr=0 pw=0 time=161966 us)
     86   TABLE ACCESS FULL USER$ (cr=7 pr=0 pw=0 time=897 us)
      0   HASH JOIN  (cr=11170 pr=0 pw=0 time=161253 us)
      0    TABLE ACCESS FULL CDEF$ (cr=11170 pr=0 pw=0 time=159825 us)
      0    TABLE ACCESS FULL CON$ (cr=0 pr=0 pw=0 time=0 us)

From what I see this seems related to cdef$ table that have 915386 records. The same number or records is in con$.

So, is this a normal behaviour? May I add an index on cdef$(con#,enabled)? Would that make any change?

Thank you very much.

Browse My Oracle Support for the possible causes. For example quick search revealed a bug about your Oracle internal SQL. Workaround for it is:

Set optimizer_mode=rule for the affected DMLs

The bug is registered against version 11g but of course it is possible that it exists also in 10g.


Advice for my DB design and how to handle null / measuring errors I am seeking advice for my own DB design. I need to make a big step and start a huge load of data processing. What I decide now will have big consequences for my project.

I have this table. It holds data that was acquired online from an external source.

app_data
+--------------+-------+------------+--------+---------+-------+
| surrogateKey | appid |  created   | owners | viewers | price |
+--------------+-------+------------+--------+---------+-------+
|            1 |     2 | 1472428100 | 10     |      25 | 10,00 |
|            2 |     2 | 1472428200 | 11     |      50 | 10,00 |
|            3 |     2 | 1472428300 | 15     |      50 | 10,00 |
|            4 |     2 | 1472428400 | 22     |      51 |  8,00 |
|            5 |     2 | 1472428500 | null   |      50 |  8,00 |
|            6 |     2 | 1472428600 | 20     |      49 |  8,00 |
|            7 |     2 | 1472428700 | 25     |      50 | 10,00 |
|          ... |       |            |        |         |       |
+--------------+-------+------------+--------+---------+-------+

CREATE TABLE app_data(
    surrogateKey BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
    appID INT UNSIGNED NOT NULL,
    created TIMESTAMP NOT NULL,
    owners INT UNSIGNED,
    owners_variance MEDIUMINT UNSIGNED,
    viewers MEDIUMINT UNSIGNED, -- TINYINT would be better but application layer cannot handle tinyint
    viewers_variance MEDIUMINT UNSIGNED,
    price MEDIUMINT UNSIGNED,
    CONSTRAINT fk_appData_app_appID
        FOREIGN KEY (appID)
        REFERENCES app(appID)
        ON UPDATE CASCADE
) ENGINE=INNODB;

Does it makes sense to create this surrogateKey? There is no value
added by it. I will not be used for joins. appid and created would
make a fine composite key and I will use these keys for joining
tables.
appID and created will be used for joining, comparisons, range
queries, and so on. How should I create indexes? One index on each?
    (appID, created)? (created, appID)?
This table will be HUGE and take 95% of the storage space. How can I optimize it further performance wise?
How should I handle random null values as in the owners column? These mean basically: The thermometer was broken that day... I was
    contemplating in just turning them to zero or leaving them as null.
    null does mean that there were no measurements that day whereas zero
    would be ambiguous but easier for analysis.
How should I handle values that don't make sense? For example
viewers upper limit is 50. There is one value with 51 that means:
    This value is WRONG. Should I turn it to null? To 50?
Should I normalize price into something like this? Normalization
would make it more difficult to analyse and result in more
complicated queries.

.

CREATE TABLE(
  appID INT NOT NULL PRIMARY KEY,
  created TIMESTAMP,
  start_date TIMESTAMP,
  end_date TIMESTAMP,
  price INT
);

Thanks!

(Others may disagree, but here goes...)

If you have a good "natural" PRIMARY KEY, use it.  In my own tables, I find that is the case about 2/3 of the time.

What makes it good?

Must be UNIQUE (since the PK is, by definition, unique)
Must never be NULL (again, a PK requirement)
'Composite' is OK, as with your example:  (appID, created)
If there are no (or few) secondary keys, then it should be "small".  This is because every secondary index implicitly includes the PK columns.
A 'natural' key is more efficient for scanning.
Since your queries might be WHERE appID = constant AND created BETWEEN..., this order is much better:  PRIMARY KEY(assId, created).

Don't use INT (4 bytes) when MEDIUMINT (3 bytes) will suffice; etc.  If the value is 0..50, then TINYINT UNSIGNED (1 byte) is better.

How did you store 10,00 in INT?  Perhaps you meant DECIMAL(8,2)?

Do not normalize anything you might want to do a range test on, such as price or created.  In general, don't normalize 'continuous' values.  Nor small values.  For example, it is hardly worth normalizing a 5-digit zip code to replace it with a 3-byte MEDIUMINT.


Insert record only if a combination of column values doesn't exist I have a simple MYSQL insert query:

INSERT INTO table('col1','col2','col3') VALUES('123','456','some text')

I want to insert only if the above combination of columns does not already exist and this case is only when col1 has value of 123 and col3 has values of some text

I will try to explain with some examples of what is allowed and what not:

('123','456','some text') allowed ONLY if not exists

('123','847,'some text') allowed ONLY if not exists

Both results above can coexists together because col2 is different. However, each of the above results can exist only once

The following ones can be duplicate because they don't have the combination of col1 and col3 of the values 123 and some text respectively

('412','847','some text') allowed no matter what even if exists

('763','372','any text') allowed no matter what even if exists

To summarize, a combination of col1,col2,col3 should be unique ONLY when col1 has value 123 and col3 has value some text

The primary key of the table is an auto_increment id.

Can this be achieved by setting certain indexes on the table or only through subqueries or what are the solutions available here to achieve this?

MYSQL doesn't support such types of constraints.
You should use stored procedure for inserting data. So you can do some checking and validation data.


SQL Server 2012 Vs PDW We are migrating to SQL server 2012 to PDW.
Any SQL script for Comparing the data between SQL server 2012 and Parallel dataware house or any tools to compare.

This paper turned in to be very handy for me, but you will still have to do some changes in your script

http://download.microsoft.com/download/4/2/6/42616D71-3488-46E2-89F0-E516C10F6576/SQL_Server_to_SQL_Server_PDW_Migration_Guide.pdf


Should I redesign my "events" table My system needs to store an append only log of events.  Currently I have a database table that stores all the relevant data in one table:

CREATE TABLE `events` (
        `event_id` VARCHAR(255) NOT NULL PRIMARY KEY,
        `event_type` VARCHAR(255) NOT NULL,
        `event_timestamp` DATETIME,
        `group_id` VARCHAR(255),
        `person_id` VARCHAR(255),
        `client_id` VARCHAR(255),
        `name` VARCHAR(768),
        `result` VARCHAR(255),
        `status` VARCHAR(255),
        `logged_at` DATETIME,
        `severity` VARCHAR(255),
        `message` LONGTEXT,
        INDEX `event_type_index` (`event_type`),
        INDEX `event_timestamp_index` (`event_timestamp`),
        INDEX `group_id_index` (`group_id`),
        INDEX `person_id_index` (`person_id`),
        INDEX `client_id_index` (`client_id`),
        INDEX `name_index` (`name`),
        INDEX `result_index` (`result`),
        INDEX `status_index` (`status`),
        INDEX `logged_at_index` (`logged_at`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

But I've noticed that queries with multiple attributes in the WHERE clause are still slow.  For example:

SELECT
  count(e.event_id) as total
FROM events e
WHERE
  e.result='Success' AND
  e.event_type='some_silly_event' AND
  e.event_timestamp > '2019-01-01 00:00:00'

One solution would be to create an index like the following:

CREATE INDEX successful_silly_events
ON events (result,event_type,event_timestamp);

The downsides of this approach seem to be that creating the index would take a long time, and would only speed up this query.  If I create a different query on this table with different columns, I'm back to square one.

Would I have been better served by splitting the events table into multiple tables from the start?  For example:

CREATE TABLE `events` (
        `event_id` VARCHAR(255) NOT NULL,
        `logged_at` DATETIME,
        `severity` VARCHAR(255),
        `message` LONGTEXT,
        PRIMARY KEY (event_id),
        INDEX `logged_at_index` (`logged_at`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

CREATE TABLE `event_types` (
        `event_id` VARCHAR(255) NOT NULL,
        `event_type` VARCHAR(255) NOT NULL,
        PRIMARY KEY event_id REFERENCES events(event_id)
        INDEX `event_type_index` (`event_type`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

CREATE TABLE `event_timestamps` (
        `event_id` VARCHAR(255) NOT NULL,
        `event_timestamp` VARCHAR(255) NOT NULL,
        PRIMARY KEY event_id REFERENCES events(event_id)
        INDEX `event_timestamp_index` (`event_timestamp`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

CREATE TABLE `event_groups` (
        `event_id` VARCHAR(255) NOT NULL,
        `group_id` VARCHAR(255) NOT NULL,
        PRIMARY KEY event_id REFERENCES events(event_id)
        INDEX `group_id_index` (`group_id`),
      ) ENGINE=InnoDB DEFAULT CHARACTER SET=utf8mb4 COLLATE=utf8_general_ci

And so on for all the other event attributes which I would have normally indexed on the events table.  This way, I could construct a similar query:

SELECT
  count(e.event_id) as total
FROM events e
  LEFT JOIN event_results er ON e.event_id=er.event_id
  LEFT JOIN event_types ety ON e.event_id=et.event_id
  LEFT JOIN event_timestamps eti ON e.event_id=et.event_id
WHERE
  er.result='Success' AND
  ety.event_type='some_silly_event' AND
  eti.event_timestamp > '2019-01-01 00:00:00'

Would the resulting query be fast and not require full table scan?  If so, this seems like a better setup.

INDEX(result, event_type, event_timestamp) obviates the need for INDEX(result) and INDEX(result, event_type).

Blindly using (255) hurts indexes and queries.  Trim back to realistic limits.

Splitting up the table the way you suggested helps nothing and hurts most queries.  In particular, then you would not be able to effectively use the multiple indexes, nor would you be able to use a 'composite' index (since it would involve more than one table).  On the other hand, if your 'log' gets to be too huge, then such 'normalization' will drastically shrink the disk footprint.  This, itself, has some positive impact on performance.

Do not normalize 'continuous' values such as timestamp.  This severely hurts performance because it makes it impractical to have an index on a "range" value.

Don't use LEFT unless you need the 'right' table to be optional.  LEFT sometimes implies that the 'left' table must be scanned first.  In your example, that would lead to a full scan of events.

If you change LEFT JOIN to JOIN (in the last example), then the Optimizer would pick among the tables to decide which one to start with.  This would be equivalent (but slower) than the original case of single-column indexes on the relevant columns.

"Low cardinality" columns (status and result) are virtually useless to index by themselves.  They can be effective when the first column of a 'composite' index.

Most tables have a limited number of queries that will realistically be applied.  If you are saying that your table begs for lots of different queries, then my advice is as follows:

Monitor what queries people want.  Keep track of the typical combinations of columns.
Implement a few 2- and 3-column indexes.
Be sure to have the first column(s) in the index be column(s) that are tested (in WHERE) with =.  If there is also a "range" (as in your example with timestamp), then put it last.  (Punt on multiple range tests.)
Remember that the order of ANDing things in WHERE does not matter, but the order of columns in an INDEX does.
More on creating optimal indexes: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

MySQL does not implement "bitmap" indexes; they are rarely worth the effort.  MySQL does implement "index merge intersect" (for ANDs) which is a clumsy way to simulate a composite index.  "index merge union" (for ORs) is sometimes handy for OR; but UNION is likely to be as good.

Yours seems like a "Data Warehouse" application.  The best speedup for such is to build and maintain Summary tables.  For your one example, a summary of daily counts broken down by result and event_type would be much smaller and much faster to query.  (10x speedup is quite possible.)  Furthermore, it is practical to have different indexes on the Summary table, thereby somewhat breaking the log jam you currently have.  (You would SUM the subtotals to get the total COUNT.)


compare 2 tables with minus with a SDO Geometry shape column if I have a column like SHAPE:

CREATE TABLE SOL_PUBLIC.PUNCTE_PERIMETRU_SIT
    (
      ID                NUMBER(10)                  NOT NULL,
      NUMAR             NUMBER(19),
      X                 NUMBER(19,2),
      Y                 NUMBER(19,2),
      FK_ID_CHESTIONAR  NUMBER(19),
      SHAPE
    )
    COLUMN SHAPE NOT SUBSTITUTABLE AT ALL LEVELS
    VARRAY "SHAPE"."SDO_ELEM_INFO" STORE AS LOB (
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      CACHE
      LOGGING
          STORAGE    (
                      INITIAL          64K
                      NEXT             1M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))
    VARRAY "SHAPE"."SDO_ORDINATES" STORE AS LOB (
      ENABLE      STORAGE IN ROW
      CHUNK       8192
      RETENTION
      CACHE
      LOGGING
          STORAGE    (
                      INITIAL          64K
                      NEXT             1M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                      FLASH_CACHE      DEFAULT
                      CELL_FLASH_CACHE DEFAULT
                     ))

when I try:

select * from (
select pub.nume_sit,cpu.* from
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
    inner join SOL_PUBLIC.SOL_CHESTIONARE@RACDB PUB
        on cpu.FK_ID_CHESTIONAR=PUB.ID
minus
select prod.nume_sit,cpr.numar,cpr.x,cpr.y from
    SOL.PUNCTE_PERIMETRU_SIT cpr
    inner join SOL.SOL_CHESTIONARE PROD
        on cpr.FK_ID_CHESTIONAR=PROD.ID
) tmp_minus
ORDER BY nume_sit,numar;

it gives me an:

ORA-22804: remote operations not permitted on object tables or user-defined type columns

and if I make a:

select * from
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
minus
select * from
    SOL.PUNCTE_PERIMETRU_SIT cpr

it gives me an:

ORA-22901: cannot compare VARRAY or LOB attributes of an object type

I need to insert the missing columns in the table where they are missing (so I need the SHAPE too).

I think I have to convert iti tu text to compare it and when I insert it into the table have to convert it back. How can I do it?

MANY THANKS!

I found that the only way to compare with RACDB + SDO_GEOMETRY row is to compose the SDO like this:

select tm.id,tm.nume_sit,tm.numar,tm.x,tm.y,
        MDSYS.SDO_GEOMETRY(2001,NULL,
                           MDSYS.SDO_POINT_TYPE(tm.x,tm.y,NULL),
                           NULL, NULL) as shape
from (
select pub.id,pub.nume_sit,cpu.numar,cpu.x,cpu.y from
    SOL_PUBLIC.PUNCTE_PERIMETRU_SIT@RACDB cpu
    inner join SOL_PUBLIC.SOL_CHESTIONARE@RACDB PUB
        on cpu.FK_ID_CHESTIONAR=PUB.ID
minus
select prod.id,prod.nume_sit,cpr.numar,cpr.x,cpr.y from
    SOL.PUNCTE_PERIMETRU_SIT cpr
    inner join SOL.SOL_CHESTIONARE PROD
        on cpr.FK_ID_CHESTIONAR=PROD.ID
) tm
ORDER BY nume_sit,numar;

as for converting the SDO_GEOMETRY to text, if anyone need this, you have to do something like this:

to_char(substr(cpu.shape.get_wkt(),1,4000)) as shape


Oracle 11 IMPDP Table Exists but gets Does Not Exist Error I'm running in to a weird problem with IMPDP and Oracle DB version 11.2.0.4.0 where I have a table in the destination schema that does indeed exist, but IMPDP thinks it doesn't. See this entry from the import log file:

  Starting "EXPORT_USER"."SYS_IMPORT_TABLE_01":  export_user/********
  directory=EXPORT_DIRECTORY
  dumpfile=SMARTCLOUDCONSOLE_OWNER_20170830.dmp
  TABLE_EXISTS_ACTION=APPEND DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
  REMAP_SCHEMA=SMARTCLOUDCONSOLE_OWNER:MIKETESTCONSOLE_OWNER
  TABLES=SMARTCLOUDCONSOLE_OWNER.ACCOUNTDEPLOYMENTLINKS QUERY="WHERE
  account_id = (select account_id from accounts where lower(guid) =
  '#####')"

  Processing object type SCHEMA_EXPORT/TABLE/TABLE

  Table "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" exists. Data
  will be appended to existing table but all dependent metadata will be
  skipped due to table_exists_action of append

  Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

  ORA-31693: Table data object
  "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" failed to load/unload
  and is being skipped due to error: ORA-00942: table or view does not
  exist

We are using VPDs such that the _owner user owns all of the objects, so I don't think it's a problem of ownership. And it seems like it figures out that the table exists from the second line there Table "MIKETESTCONSOLE_OWNER"."ACCOUNTDEPLOYMENTLINKS" exists. but then when it proceeds it fails with ORA-00942: table or view does not exist

What might be wrong with my use of IMPDP?

It looks like a permission issue. As Raj pointed out make sure that export_user has select privilege for the accounts table. Which it will have if it owns the table. Also make sure that it has select, insert, update and delete for MIKETESTCONSOLE_OWNER.ACCOUNTDEPLOYMENTLINKS and all of the tables in that schema. If tables are added over time, you probably need to add grants.


Minimum rowcount for filtered index? Creating an index always has trade-offs.  Whenever you insert, update or delete, it has to perform those operations on the index as well.  This is all pretty commonly known.

But with the advent of filtered indexes, I'm wondering if there is a minimum (or maximum) row count that you should consider too much of a performance hit.

For example, if you have 100,000 rows in a table that gets, say, 75% read operations, would it be wise to add a filtered index that only covers 500 rows?  Or 100 rows?  What if the filter covers 85,000 of those rows?

I think the performance implications of adding a filtered index to a read-heavy table are pretty clear.  I'm just wondering if there's a natural limit to this performance.

Another way of looking at this question:  If 90% of your updates aren't fitting within the filter, is the performance degraded less than it would be for a non-filtered index?

For example, if you have 100,000 rows in a table that gets, say, 75%
  read operations, would it be wise to add a filtered index that only
  covers 500 rows? Or 100 rows? What if the filter covers 85,000 of
  those rows?

That would depend on the queries against the table. If query patterns are such that they target a subset of the 500 or 100 rows that your filtered index covers, perfect. On the flip side, it's unlikely that the optimiser is going to choose a filtered index that includes 85% unless it is a covering index for a particular query.

I haven't tested this but I would expect filtered index utilisation to exhibit the same tipping point behaviour as non-filtered indexes.


Mongo replication lag slowly increasing I am running a replica-set in production with slaveOk = false using mongo 2.0.7 in AWS The replication lag on one of the servers is close to 58 hours. This replication lag is sometimes decreasing ( at very slow rate ) and sometimes increasing, but overall its lag is increasing 1-2 hours per day.

I restarted the server, but seeing no benefit
The read rate is 10 times of the read rate, compared to the other secondaries.
I checked the logs, but nothing weird
The lock % is very high ( close to 100% ) only on that server( and there is no such issues with the capped collection ). Other secondaries hardly have 10-20 lock%

One more weird thing that I noted about this server ( secondary ) is that mms is showing the version to be 2.2.1 and type as 'standalone' server, which is not the case ( cross checked using db.version() and rs.status() command )

Firtsly, I hope you have set the indexes. At the other hand, If you don't use db.repairDatabase() try it when you're in maintanance mode. That command will re-indexing and doing many performance procedures.

If you want to more details; repairDatabase()


How do I get full details of each person when joining tables I know this has to have a simple solution and I am not seeing it.

I have a table, person, which contains all information about each person such as first name, last name, age, address.

I have a second table called consumer_client which contains a row containing person id for each consumer and client like this:

id | consumer_person_id | client_person_id

What I want is to fetch both full names when I join person to client_consumer like this:

id | Joe Mama | Mike Smith

I cannot seem to get that with a simple JOIN syntax like

SELECT * FROM person JOIN client_consumer WHERE person.person_id = cc.person_id

as it always returns just the consumer_person_id and client_person_id along with other information in person. I want it replaced with the full name instead of person_id.

I think you are looking at this the wrong way around. You should be selecting from the client_consumer table and joining to the person table. This way the query is easily read and understood.

SELECT
    cc.id,
    consumer.full_name as consumer_full_name,
    client.full_name as client_full_name
FROM client_consumer cc
JOIN person consumer ON
    consumer.person_id = cc.consumer_person_id
JOIN person client ON
    client.person_id = cc.client_person_id


Recreate MSDB for SQL Server Express 2008 My SQL Server Express database's msdb file has become 'suspect'. All the other mdf files seem to be OK.

I now need to recreate the msdb file, but I do not have a backup for it (guilty as charged!). I have backups for my other DBs. This is what I found from this blog:

  If your msdb goes suspect then you have two choices, if you have
  backup of database then restore it else you have to recreate it

  If you want to recreate it here are the steps to do it

  Start the server with trace flag 3608. Detach the damaged msdb
  Navigate to the directory C:\Program Files\Microsoft SQL
  Server\MSSQL.1\MSSQL\Binn and start sqlservr.exe -c -T3608
  Move or rename the damaged msdb files
  Go to the folder C:\Program Files\Microsoft SQL
  Server\MSSQL.1\MSSQL\Install and run the instmsdb file.
  Restart SQL Server without the 3608 trace flag

Has someone done this before? I would like to get opinions or better suggestions before I break things more.

Thanks!

EDIT:
My advice to anyone is to treat SQL Express like any production DB.  Have rigorous back-up policy and make sure you back-up your system databases.

You either recreate as above or restore it.

The info above is similar to MSDN, see "Creating a New msdb Database".

Note: if this came from Paul Randal's blog or the SQL Server Storage Engine then you can trust it


MySQL Table rename and Grants We are in the process of spring-cleaning our production MySQL 5.0 database, and this includes the renaming a number of tables.

We are using the following syntax for the rename

ALTER TABLE `mydb`.`table_a`
RENAME TO `mydb`.`table_b`

The expected behaviour would be that the GRANTS for table_a would be shifted across to table_b.  This does not seem to be the case.

Assuming that what we see is correct is there a simple solution we can add to our scripting?

It looks like we should be able to update the TABLE_PRIVILEGES.TABLE_NAME column in the information schema, but we are assuming that whilst reading the schema is OK, we shouldn't be manually updating it.

Which leads to reading TABLE_PRIVILEGES and Granting / Revoking privileges line by line...

Any other options?

Have ended up Querying information_schema.Table_Privileges and scripting Grants & Revokes based on the result.


What is object ID -710746692? I am using SentryOne, and have what I believe to be a deadlocking process.  It shows it with a database id of 32767 and an object id of -710746692. I looked in sys.databases and sys.objects and didn't find anything for these. I did a search for the Database ID and believe it to be the hidden Resource Database that sql server has.  However, a google search didn't return anything meaningful for the object id.

What is Object ID -710746692?

As Erik mentioned, 32767 is the resource database, and it is hidden from the sys.databases view, but that doesn't mean the object you're after lives only there - in fact most system objects are in all databases.

To determine the object, we just need to pass your object_id to a query against sys.all_objects (rather than sys.objects):

SELECT name FROM sys.all_objects
  WHERE object_id = -710746692;

And the result:

sp_MSreplupdateschema

So, sounds to me like something is messed up with your replication configuration, or you're just throwing too much concurrent changes at it. If this is only happening during deployments or specific activities I would try to eliminate those as causes.

Otherwise, like Erik said, there's not much you can do about it - it's not like you can edit that stored procedure, even if you do understand why it's deadlocking, so you'll have to troubleshoot it from the feature side. You could upload the .xdl graph and someone could take a look and maybe figure out if it's deadlocking because of something else you can control.


SQL Server 2008 R2 reindex job reorganise getting stuck I am currently experiencing this job getting stuck in my nightly maintenance solution -

ALTER INDEX [PK_ThirdPartySettleCalled] ON [evo_archive].[dbo].[ThirdPartySettleCalled]
 REORGANIZE WITH (LOB_COMPACTION = ON)

a full reindex of the table works.

but I'm using Ola Hallengren's awesome solution as I do on a number of sites that need maintenance solutions. http://ola.hallengren.com/Documentation.html and the job gets stuck in the middle of the night.

I've tested it as a single statement from within ssms with the same result, without an error I'm a little short of ideas.

Anyone experienced this issue before ?

No blocking,

the table is circa 300 million rows,

basically I say 'stuck' because no other processes are running in SQL server, and its been reorganising for hours with no error.

It is in the same state with 25% fragmentation or virutually 0% (tested after a full rebuild of the index)

Resolved

The problem was we had a rogue cron job (unix job) related to our web servers that was using an incorrect login,

eventually it took up all 8 CPU’s so the reindex job was simply waiting for some CPU to become available before it could continue (this is why the job failed to timeout)


Database engine that share a row among multiple tables I'm wondering whether exists any database engine that can share cells or rows amongst many tables. For example:

TABLE Customer (
   CID: int
   Fullname: nvarchar(50)
   Age: int
)

TABLE Group (
   GID: int
   Name: nvarchar(50)
)

TABLE Customer_Group (
   CusName: nvarchar(50)
   GrpName: nvarchar(50)
)

In which, Customer_Group is designated for a certain query, like a column family in Cassandra. Now I want to insert to Customer_Group table a row whose CusName cell references to Fullname cell of a row in table Customer. Similar to GrpName.

As a result, when I update Fullname in Customer table, I don't have to update Customer_Group table, because it references to same cell address. And Customer_Group table itself doesn't have to store actual data for CusName but in fact is a pointer.

Does anyone know what database engine supports this idea? And is it a good idea though?

Isn't this what CASCADE option on foreign key is supposed to do? You can find that in many DB engines.
It is not ok to perform the join on Fullname column as long as you have a CID column, which is supposed to be the identity. It is just a bad practice, and can have undesired effects - like the one you encountered.

In conclusion, either you change the joining column, or try to active the cascading on update capabilities of your DBMS.


List all databases not attached to SQL server I am moving databases from one instance to another (by detaching databases from the first instance, move MDF and log files to another location, and attach them to the new instance), unfortunately could not attach some of them, and miss calculate number of databases that have issues, how to check all MDF files that is not attached to SQL server from specific directory.

I am moving databases from one instance to another

You should use powershell for automation and backup restore for a guranteed way of migrating your databases.

For powershell, use dbatools --> COPY-SQLDATABASE or for migrating entire server with logins, jobs etc use Start-SqlMigration

  by detaching databases from the first instance, move MDF and log files to another location, and attach them to the new instance

Why not backup restore ? detaching and attaching database files for moving from one server to another is not a recommended approach !

  List all databases not attached to SQL server

You can use Find-DbaOrphanedFile to find out orphaned database files .mdf, .ldf and .ndf files.


Set a default value for a jsonb property in PostgreSQL Let's say that I have a jsonb column with documents like:

{
    "lead": "The lead",
    "video": "An URL to the video"
}

In some cases I also need to store the name of a picture that's uploaded to the server by the user in the JSON. Obviously I don't want clashes on the filenames so I wonder if it's possible to use uuid_generate_v4() as default value:

insert into document (title, content) values (
    "The title of the document",
    '{
        "lead": "The lead",
        "video": "An URL to the video",
        "picture": ":?" -- Here I need the value returned by uuid_generate_v4()
    }'
);

Is it possible or should I get the uuid in the application before inserting the data in the database?

Thanks beforehand.

You can resort to row_to_json for things like this.

insert into document (title, content)
select 'The title of the document', row_to_json(n)
from (
  select 'The lead' as lead,
         'An URL to the video' as video,
         uuid_generate_v4() as picture
  ) n


What are the support windows for SQL Server 2012 SP3 and SP4 respectively? I am hearing two different rumors. One is that SQL Server 2012 SP3 loses support June 2017. I am also hearing SQL Server 2012 SP4 is due out in June 2017. Is SP4 just a rumor? Or is it true? If true, when does SP4 lose support?

SP4 for 2012 was announced recently and is due out next month.

See Microsoft's Support Lifecycle Policy documentation for end-of-support dates.  According to that document, SQL Server 2012 Service Pack 3 "mainstream support" ends on July 11, 2017.  Extended support for SQL Server 2012, including service pack 4, ends July 12, 2022.