Individual queries run on 10ms, with UNION ALL they are taking 290ms+ (7.7M records MySQL DB). How to optimise? I have a table that stores available appointments for teachers, allowing two kinds of insertions:

Hourly based: with total freedom to add unlimited slots per day per teacher (as long as slots don't overlap): on 15/Apr a teacher may have slots at 10:00, 11:00, 12:00 and 16:00. A person is served after choosing a specific teacher time/slot.
Time period/range: on 15/Apr another teacher may work from 10:00 to 12:00 and then from 14:00 to 18:00. A person is served by order of arrival, so if a teacher works from 10:00 to 12:00, all persons that arrive in this period will be attended by order of arrival (local queue).

Since I have to return all available teachers in a search, I need all slots to be saved in the same table as the order of arrival ranges. This way I can order by date_from ASC, showing the first available slots first on the search results.

Current table structure

CREATE TABLE `teacher_slots` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `teacher_id` mediumint(8) unsigned NOT NULL,
  `city_id` smallint(5) unsigned NOT NULL,
  `subject_id` smallint(5) unsigned NOT NULL,
  `date_from` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_to` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `status` tinyint(4) NOT NULL DEFAULT '0',
  `order_of_arrival` tinyint(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `by_hour_idx` (`teacher_id`,`order_of_arrival`,`status`,`city_id`,`subject_id`,`date_from`),
  KEY `order_arrival_idx` (`order_of_arrival`,`status`,`city_id`,`subject_id`,`date_from`,`date_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

Search query

I need to filter by: actual datetime, city_id, subject_id and if a slot is available (status = 0).

For hourly based I have to show all available slots for the first closest available day for every teacher (show all time slots of a given day and can't show more than one day for the same teacher). (I got the query with the help from mattedgod).

For range based (order_of_arrival = 1), I have to show the closest available range, just one time per teacher.

First query runs individually in around 0.10 ms, second query 0.08 ms and the UNION ALL an average of 300ms.

(
    SELECT id, teacher_slots.teacher_id, date_from, date_to, order_of_arrival
    FROM teacher_slots
    JOIN (
        SELECT DATE(MIN(date_from)) as closestDay, teacher_id
        FROM teacher_slots
        WHERE   date_from >= '2014-04-10 08:00:00' AND order_of_arrival = 0
                AND status = 0 AND city_id = 6015 AND subject_id = 1
        GROUP BY teacher_id
    ) a ON a.teacher_id = teacher_slots.teacher_id
    AND DATE(teacher_slots.date_from) = closestDay
    WHERE teacher_slots.date_from >= '2014-04-10 08:00:00'
        AND teacher_slots.order_of_arrival = 0
        AND teacher_slots.status = 0
        AND teacher_slots.city_id = 6015
        AND teacher_slots.subject_id = 1
)

UNION ALL

(
    SELECT id, teacher_id, date_from, date_to, order_of_arrival
    FROM teacher_slots
    WHERE order_of_arrival = 1 AND status = 0 AND city_id = 6015 AND subject_id = 1
        AND (
            (date_from <= '2014-04-10 08:00:00' AND  date_to >= '2014-04-10 08:00:00')
            OR (date_from >= '2014-04-10 08:00:00')
        )
    GROUP BY teacher_id
)

ORDER BY date_from ASC;

Question

Is there a way to optimise the UNION, so I can get a reasonable response of a maximum ~20ms or even return range based + hourly based in just one query (with an IF, etc)?

SQL Fiddle: http://www.sqlfiddle.com/#!2/59420/1/0

EDIT:

I tried some denormalization by creating a field "only_date_from" where I stored only the date, so I could change this...

DATE(MIN(date_from)) as closestDay / DATE(teacher_slots.date_from) = closestDay

... to this

MIN(only_date_from) as closestDay / teacher_slots.only_date_from = closestDay

It already saved me 100ms! Still 200ms on average.

Try this query:

(
select * from (SELECT id, teacher_slots.teacher_id, date_from, date_to,  order_of_arrival
FROM teacher_slots  WHERE teacher_slots.date_from >= '2014-04-10 08:00:00'
    AND teacher_slots.order_of_arrival = 0
    AND teacher_slots.status = 0
    AND teacher_slots.city_id = 6015
    AND teacher_slots.subject_id = 1)
 teacher_slots
JOIN (
    SELECT DATE(MIN(date_from)) as closestDay, teacher_id
    FROM teacher_slots
    WHERE   date_from >= '2014-04-10 08:00:00' AND order_of_arrival = 0
            AND status = 0 AND city_id = 6015 AND subject_id = 1
    GROUP BY teacher_id
) a ON a.teacher_id = teacher_slots.teacher_id
AND DATE(teacher_slots.date_from) = closestDay

)

UNION ALL

(
SELECT id, teacher_id, date_from, date_to, order_of_arrival
FROM teacher_slots
WHERE order_of_arrival = 1 AND status = 0 AND city_id = 6015 AND subject_id = 1
    AND (
        (date_from <= '2014-04-10 08:00:00' AND  date_to >= '2014-04-10 08:00:00')
        OR (date_from >= '2014-04-10 08:00:00')
    )
GROUP BY teacher_id
)

ORDER BY date_from ASC;


DBA privileges information My colleague having DBA ROLE has created a user for me with the following role. CONNECT,DBA,RESOURCE from his own user.

Then I login and check the detail by using the below query.

select * from DBA_ROLE_PRIVS where grantee = 'MURAHMAN';

It's fine and retrieve all the above three roles, but when I query the
table dba_tab_privs.

select distinct grantee as "User_Name" from dba_tab_privs;

This does not retrieve my user.

Then I run the below query.

SELECT distinct username, account_status,privilege,owner
 FROM dba_users
   left outer join dba_tab_privs
   on (dba_users.username = dba_tab_privs.grantee)
where dba_users.account_status = 'OPEN';

I saw the privilege column against my user is null? So, could you please help to find the privilege information against each user? Also please explain why the privilege column is null against my user? All other user have the privilege information.

The DBA role includes system privileges (like SELECT ANY TABLE), not object privileges.


Overcome Runtime error when exporting large data table from PostgreSQL I am very new to using PostgreSQL. I have created a query that compiles a table with more than 100,000 rows and 20 columns of data. The query works and loads the table fine in the PostgreSQL preview window. However, when I click to export the table to CSV in PostgreSQL I get a Runtime application error that says PostgreSQL must automatically close (and I lose everything). I do not get this error when I query and CSV export much smaller tables.

I would appreciate any advice on how to overcome this Runtime application error for this large table.

I do not want to divide my large table into many small tables since this will take too much time and creates rooms for merging multiple tables errors.

Thank you for any help you can provide!

CODE:

SELECT
si.symbolvalue as GVKEY
,e.mostImportantDateUTC
,t.transcriptcreationdateutc
,tc.componentOrder
,replace(replace(tc.componenttext, chr(10), ''), chr(13), '') as ComponentText

,p.proId

FROM ciqTranscript t
JOIN ciqEvent e  ON e.keyDevId = t.keyDevId
JOIN ciqEventToObjectToEventType eo ON eo.keyDevId = e.keyDevId
JOIN ciqEventType et ON et.keyDevEventTypeId = eo.keyDevEventTypeId
JOIN ciqCompany c ON c.companyId = eo.objectId
JOIN ciqsymbol si on si.objectid = c.companyid and si.symboltypeid = 69 -- gvkey
JOIN ciqTranscriptCollectionType tct  ON tct.transcriptCollectionTypeId = t.transcriptCollectionTypeId
JOIN ciqTranscriptComponent tc  ON tc.transcriptId = t.transcriptId

WHERE et.keyDevEventTypeId='48'
AND t.transcriptPresentationTypeId='5'
AND tct.transcriptCollectionTypeId='1'
AND e.announceddateutc between '2008-01-01' and '2017-12-31'

ORDER BY t.transcriptid, c.companyid, tc.componentOrder

This problem is not with postgresql but with the IDE that you are using to manage it - PostgreSQL has nothing to do about that.

You can try to do this with COPY command  (different from \copy - because is a psql command)

COPY (
SELECT
si.symbolvalue as GVKEY
,e.mostImportantDateUTC
,t.transcriptcreationdateutc
,tc.componentOrder
,replace(replace(tc.componenttext, chr(10), ''), chr(13), '') as ComponentText

,p.proId

FROM ciqTranscript t
JOIN ciqEvent e  ON e.keyDevId = t.keyDevId
JOIN ciqEventToObjectToEventType eo ON eo.keyDevId = e.keyDevId
JOIN ciqEventType et ON et.keyDevEventTypeId = eo.keyDevEventTypeId
JOIN ciqCompany c ON c.companyId = eo.objectId
JOIN ciqsymbol si on si.objectid = c.companyid and si.symboltypeid = 69 -- gvkey
JOIN ciqTranscriptCollectionType tct  ON tct.transcriptCollectionTypeId = t.transcriptCollectionTypeId
JOIN ciqTranscriptComponent tc  ON tc.transcriptId = t.transcriptId

WHERE et.keyDevEventTypeId='48'
AND t.transcriptPresentationTypeId='5'
AND tct.transcriptCollectionTypeId='1'
AND e.announceddateutc between '2008-01-01' and '2017-12-31'

ORDER BY t.transcriptid, c.companyid, tc.componentOrder )
TO '/tmp/yourfile.txt' delimiter ';';

If you are using Windows as a server, then you just need to use a Windows path instead of /tmp.

This is good if you have postgresql in your local machine or have some access in the server that postgresql is stored, then you can also use a path in the file name to put the file in some directory that is shared (via samba, Windows Share or NFS for example) with you or with your PHP/Java system. Guarantee postgresql user has access to the directory -- tmp is world writable because that i used it as a example.

Good lucky!


(MySQL) SET column with values from a foreign key constrant I am making a junction table that tracks relationships between users. Among other things it is formatted with a group leader, and with a variable amount of members. All users have a user id and so this relationship between leader and members must constrain to the defined uuids in the users table.

Currently, the only way I know how to accomplish this is to make a bunch of columns named member1, member2 and so on, and then make foreign key constraints on each column, but this is inefficient as the number of members in a group varies from entry to entry. So I thought to use a SET type for the members column. However, the SET column type forces you to define constants for it, and this simply isn't possible with a wide amount of user ids.

So in essence, my question is, how can I make a set whose values are constrained to the values in another table? I'm trying my best to follow the standard practice of using junction tables, but if you have a better organizational solution which allows me to sidestep this issue entirely while still accomplishing the same or similar goal that would also be most appreciated.

Edit: Group id is synonymous with leader id because groups are identified by their leaders.

Schematically.

CREATE TABLE users ( id PRIMARY KEY, ... );
CREATE TABLE groups ( leader_id REFERENCES users (id),
                      member_id REFERENCES users (id) );

The keys (including primary) and constraints in groups are dependent by:

does the user may be a member of one group only, or of a lot of groups;
does the group leader may be a member of another group (rings possible);
does a leader must (or may) be a member of his own group.


Unpivot avoid cartesian product on empty columns I have an issue with UNPIVOT.

The initial situation is that I receive some files with a fixed number of columns and I have to import them in my Database like that. That means my table has for example 10 departement columns : departement_1, department_2, departement_3, ...

Based on that data, I need to create some views where I only have 1 column because I am limited by the software where I'll be importing the data.

This is a view I made:

SELECT reference, libelle, date_naissance, pays_naissance, nationalite,
    pays_fiscal, nif, us_tin, us_person, type_activite, etat_civil, rac,
    pep, departement, etat_individu, relation
FROM S2i_individu
LEFT JOIN relation_individu
    ON S2i_individu.reference = relation_individu.individu
UNPIVOT (
    pays_fiscal FOR t1 IN (pays_fiscal_1, pays_fiscal_2, pays_fiscal_3)
) UNPIV
UNPIVOT (
    nif FOR t2 IN (nif_1, nif_2, nif_3)
) UNPIV
UNPIVOT (
departement for t3 in (departement_1, departement_2, departement_3, departement_4, departement_5, departement_6, departement_7, departement_8, departement_9, departement_10)
) UNPIV
WHERE import_id = (SELECT TOP(1) id FROM import ORDER BY import_date DESC)

My issue is that by the nature of UNPIVOT, the result is the cartesian product of departement, nif and pays_fiscal. Which means that I will always have 90 rows for each entry in S2i_individu.

Does anyone know a way to limit to 1 row if there's no data ? Like if I don't have any data for pays_fiscal, nif and departement, that I still have a single row in my result?

This is what I came up with the help of reddit :

SELECT DISTINCT reference, libelle, date_naissance, pays_naissance, nationalite, pays_fiscal, nif, us_tin, us_person, type_activite, etat_civil, rac, pep, departement, etat_individu, relation
FROM S2i_individu
LEFT JOIN relation_individu ON S2i_individu.reference = relation_individu.individu
CROSS APPLY (
VALUES
    (pays_fiscal_1,nif_1,departement_1,'t1'),
    (pays_fiscal_2,nif_2,departement_2,'t2'),
    (pays_fiscal_3,nif_3,departement_3,'t3'),
    ('','',departement_4,'t4'),
    ('','',departement_5,'t5'),
    ('','',departement_6,'t6'),
    ('','',departement_7,'t7'),
    ('','',departement_8,'t8'),
    ('','',departement_9,'t9'),
    ('','',departement_10,'t10')
) AS v(pays_fiscal, nif, departement, suffix)
WHERE import_id = (SELECT TOP(1) id FROM import ORDER BY import_date DESC


Update large number of row in MySQL table I'm using relational database(MySQL 5.7). On this database i have a table called customer_transaction. On this table i have 4 columns: id, customer_id, type, amount

|id|customer_id |type     |amount|
|--|------------|---------|------|
|1 |44          |Credit   |50    |
|2 |44          |Credit   |20    |
|3 |44          |Debit    |30    |
|4 |10          |Credit   |30    |

now i am introduce a new balance column(current balance) on this table like below.

|id|customer_id |type     |amount|balance|
|--|------------|---------|------|-------|
|1 |44          |Credit   |50    |50     |
|2 |44          |Credit   |20    |70     |
|3 |44          |Debit    |30    |40     |
|4 |10          |Debit    |30    |-30    |

The problem is, on the customer transaction table, their was nearly millions of row and all balance column was 0.00.

So i want to re-sync all balance data. But i'm confused how to recalculate and update all those row. Can i update this by MySQL query or calculate and update from my application (Laravel-PHP).

Solution 1: suggested by @GMB

In MySQL 5.x, where window functions are not available, an option uses a correlated subquery to compute the balance:

update customer_transaction ct
inner join (
    select
        id,
        (
            select sum(case type when 'Credit' then amount when 'Debit' then -amount end)
            from customer_transaction ct2
            where ct2.customer_id = ct1.customer_id and ct2.id <= ct1.id
        ) balance
    from customer_transaction ct1
) ctx on ctx.id = ct.id
set ct.balance = ctx.balance

But their is a problem. We are doing those process on a live server and it take's some times to execute. i think, during that execution time, my transaction table remains locked. Is their any way to preventing locking.

Look at this:

-- this trigger needed for initial balance calculation
-- or emergency re-calculation (generally for one customer)
CREATE TRIGGER tr_bu_customer_transaction
BEFORE UPDATE
ON customer_transaction
FOR EACH ROW
SET NEW.balance = COALESCE( ( SELECT balance
                              FROM customer_transaction
                              WHERE customer_id = NEW.customer_id
                                AND id < NEW.id
                              ORDER BY id DESC LIMIT 1 ), 0) + NEW.amount;

-- this trigger needed for balance calculation for newly inserted records
CREATE TRIGGER tr_bi_customer_transaction
BEFORE INSERT
ON customer_transaction
FOR EACH ROW
SET NEW.balance = COALESCE( ( SELECT balance
                              FROM customer_transaction
                              WHERE customer_id = NEW.customer_id
                              ORDER BY id DESC LIMIT 1 ), 0) + NEW.amount;

Usage sample: fiddle.

PS. Of course the whole table will be locked during initial calculation. But I think this will take less time then the technique with correlated query usage. And this allows to perform initial calculation for separate user or users list per chunk easily.

PPS. I assume that the transaction records (more precisely - their base data) are updated NEVER.


Why is Row Overflow not working on SQL Server 2005 I have a SQL Server 2005 database with a table that has around 860 columns. Aside from the ID column all other columns are varchar(N) where N is either 100 or 500. Upon inserting a new row (via an InfoPath form as the front end) with more than 8000 characters the SQL server returns the following error:

Cannot create a row of size 11024 which is greater than the allowable maximum of 8060.

From what I understand this should not be happening due to Row Overflow.

Can anyone please provide some insight as to why this is happening?

This is just a warning message.  You can verify that rows are overflowing to the ROW_OVERFLOW_DATA pages by using the undocumented command DBCC IND.

Personally this sounds like a table which could stand some normalization.


AWS Aurora MySQL Serverless CPU utilisation is above 10 even it is not processing any requests We have Aurora MySQL serverless DB with 2 capacity units. I didn't understand why the CPU utilisation is always above 10 even when it not processing any queries. As a result we are paying more bills without any use. Can someone help me to understand how the CPU utilisation is calculated and why serveless is charging even when it is in ideal state(not serving any requests).

Run show processlist or an analogous command. 10% doesn't seem unreasonable but the database is obviously doing something at this time.

It seems as though this product puts a large emphasis on replication and durability. From the product description, "Amazon Aurora is designed to offer greater than 99.99% availability, replicating 6 copies of your data across 3 Availability Zones and backing up your data continuously to Amazon S3" https://aws.amazon.com/rds/aurora/

What I'm getting at is that 10% cpu usage could likely be the result of synchronization/replication thread(s).


How to grant rights for a user in DB2 Express? I have installed DB2 Express on a PC. My user is not an administrator user, however as far as i know, it is possible to grant a user rights, so he can create/delete/modify a database.

I have executed: db2set DB2_GRP_LOOKUP=local
after that i created a Local group: DB2Admins, and added myself to it. After that i executed `db2 update dbm cfg using sysadm_group db2admins.

After that i tried to create database test, but i get the error SQL1092N The requested command or operation failed because the user ID does not have authority to perform the requested command or operation. User ID: "myUserID".

I created a local user, and added it to the same group. If i log in with this local user, then it is ok. The user can create a a database.
My user however is a domain user, and with that it is not working.

Does anyone has any idee how to solve this?

Thanks in advance!

DB2 will by default look for the group information in the same directory where the user is defined. For a domain user that would be the domain itself. If a domain user is a member of a local group, after setting DB2_GRP_LOOKUP=local make sure to restart the DB2 instance.

If you have enabled extended security (which is the default installation option), you may want to try adding your ID to the DB2USERS group.

Since the DB2 instance will need to perform the domain directory lookup when authenticating domain users, the account that runs the DB2 instance service must have sufficient permissions to perform the AD lookup. For example, Local Service authority would not be able to do that.

Each DB2 instance service needs such authority. There are other DB2-related services (DB2 DAS, DB2 Remote Command Server, etc. -- described in http://pic.dhe.ibm.com/infocenter/db2luw/v10r5/topic/com.ibm.db2.luw.qb.server.doc/doc/r0023249.html) -- they do not perform authentication and therefore do not require domain rights.


On a typical COW filesystem, is the database WAL redundant (in theory)? EDIT: I was incorrectly assuming that all COW filesystems have a WAL. IT seems that BTRFS doesn't, so this question essentially applies only to ZFS.

Typical COW filesystems, like ZFS, have their own form of WAL (ZIL, in the case of ZFS).

On a non-COW filesystem, the DB WAL makes sense for both performance (because it's sequential) and integrity reasons (because of the lack of write integrity guarantees); on a COW filesystem, those roles are fulfilled by the FS (WAL).

Because of this, doesn't storing DB data on a COW filesystem make the DB WAL redundant?

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


Transaction log backup file larger than expected? I've recently taken over a SQL Server database that didn't have any transaction log backup routine running for at least 2 years. The first log backup was very large (3.8 gb on a 1.14 gb database) as you would predict, however subsequent log backups are still larger than I would have expected (somewhere in the region of 3 mb).

I also restored the database to a QA and ran several sequential log backups without there being any activity on the database and the log file backups were still ~3 mb.

What would account for the size and consistency of the transaction log backups with nominal activity on the database itself?

EDIT:

Here is the script we're using to run the TLOG backups, this is being executed every 10 minutes:

BACKUP LOG [mydatabase]
TO  DISK = @fullpath
WITH  RETAINDAYS = 2
    , NOFORMAT
    , NOINIT
    , NAME = @bckName
    , SKIP
    , REWIND
    , NOUNLOAD
    , STATS = 10;

Having spent some time looking at this in more detail it would appear that the unusually large transaction log backups are due to excessive internal fragmentation of the transaction log itself. This is due to unrestricted autogrowth of the transaction log over a long period of time.

The following excerpt from a SQLSkills.com article more precisely defines the problem

  Often, when transaction logs are not pre-allocated and/or when there's
  been a lot of autogrowths, the transaction log can become internally
  fragmented. Internally your transaction logs are broken down into
  smaller more granular chunks called VLFs (Virtual Log Files). The size
  and number of VLFs you'll have depends largely on the size that the
  chunk is when it's added to you transaction log. If you add a new
  chunk to the transaction log which is 20MB (through autogrowth or
  through manual growth) then the number of VLFs that are added is 4. If
  you add a chunk which is greater than 64MB but less than or equal to
  1GB, you'll add 8 VLFs. If you add more than 1GB then you'll add
  16VLFs. In general, most transaction logs will only have 20 or 30 VLFs
  – even 50 could be reasonable depending on the total size of the
  transaction log. However, in many cases what happens is that excessive
  autogrowths can cause an excessive number of VLFs to be added –
  sometimes resulting in hundreds of VLFs. Having an excessive number of
  VLFs can negatively impact all transaction log related activities and
  you may even see degradation in performance when transaction log
  backups occur. To see how many VLFs you have solely look at the number
  of rows returned by DBCC LOGINFO. The number of rows returned equals
  the number of VLFs your transaction log file has. If you have more
  than 50, I would recommend fixing it and adjusting your autogrowth so
  that it doesn't occur as fequently. To get rid of all of the
  execessive VLFs, follow these easy steps to shrink off the fragmented
  chunk and add a new, clean chunk to your transaction log.

On this particular database it turns out we had 3011 VLFs, some degree over the recommended limit of 50 :)

We are now working through the steps in the above guide to reduce the number of internal VLFs. The QA database has been shrunk and the start size set to 2.5 gb, we now have 23 internal VLFs. So the resulting TLOG backup is in the region of 128 kb.


UPDATE SET values ignoring set ordering I'm trying to update 2 rows at once, I want to:

1) Set coupon.start (datetime) to the original account.vipExpires (datetime)

2) Add coupon.days (int) to account.vipExpires (datetime)

3) Set coupon.end (datetime) to the new value of account.vipExpires after coupon.days have been added

UPDATE accounts a, coupons c
SET c.start = a.vipExpires,
    a.vipExpires =  DATE_ADD(a.vipExpires, INTERVAL c.days DAY),
    c.end = a.vipExpires
WHERE a.id = 1 AND c.id = 1;

The result I'm getting is:

c.start, a.vipExpires, c.end ALL end up as the new datetime with the added days.

How do I make c.start be equal to the original a.vipExpires value?

I originally thought the SET operations would execute in the order specified, it seems I was wrong.

Test

UPDATE coupons c, accounts a
SET c.start = a.vipExpires,
    c.end = DATE_ADD(a.vipExpires, INTERVAL c.days DAY),
    a.vipExpires = c.end
WHERE a.id = 1 AND c.id = 1;


Loop inside of query in mysql procedure. Is it possible? I have the following query:

INSERT INTO major (key_1, key_2, ... , key_N)
VALUES (v_1_1, v_1_2, v_1_3, ... , c_1_N)
       (v_2_1, v_2_2, v_2_3, ... , c_2_N)
       ...
       (v_K_1, v_K_2, v_K_3, ... , c_K_N)

where key_i is name of column, and v_i_j is random value from 1 to L. N,L and K are positive integers (our input).

I am going to write mysql procedure that create random (with values from 1 to L) matrices* (KxN) and place it to database using query presented above.

Now It is realised by code in php and doctrine but I want to achieve the same result in pure SQL procedure that as input gets (N,L,K). Theres is php code:

$conn = DriverManager::getConnection($connectionParams);
$conn->beginTransaction();
try {
    for ($i = 1; $i <= $k; $i++) {     // row in table major
        $content=[];
        for ($j = 1; $j <= $n; $j++) {   // foreign key of row
            $content['key_' . $j] = rand(1, $l);
        }
        $conn->insert('major', $content);
    }
    $conn->commit();
} catch(\Exception $e) {
    $conn->rollBack();
    throw $e;
}

*I wrote matrices but I know that without order of rows it is not matrix in strict sense.

So I do not want to make query inside loop, but loop inside query. I know it is bad practice, but I am thinking about concatenating and evaluating query inside of procedure? It is possible? Or is these better solution?

I solved this writhing the following code:

drop procedure if exists populate;
delimiter $$
CREATE PROCEDURE populate (IN n INT, IN l INT, IN k INT)
BEGIN
  declare _k int unsigned DEFAULT 0;
  declare _n int unsigned DEFAULT 0;
  declare _l int unsigned DEFAULT 0;
  DECLARE _stmt TEXT DEFAULT 'INSERT INTO major_1 (';

  WHILE _n < n DO set _n=_n+1;
    set _stmt = CONCAT(_stmt,'minor_',_n,'_id');
    IF _n<n THEN set _stmt = CONCAT(_stmt,','); END IF;
  END WHILE;
  set _stmt = CONCAT(_stmt,') VALUES ');

  while _k < k do set _k=_k+1;
    set _stmt = CONCAT(_stmt,'(');
    set _n=0; WHILE _n < n do set _n=_n+1;
      SET _stmt = CONCAT(_stmt,CEIL(RAND()*l));
      IF _n<n THEN set _stmt = CONCAT(_stmt,','); END IF;
    END WHILE;
    IF _k<k THEN set _stmt = CONCAT(_stmt,'),');
    ELSE set _stmt = CONCAT(_stmt,');'); END IF;
  end while;

# code to debug:
# SELECT n,l,k,_stmt;

  SET @STMT = _stmt;
  PREPARE stmt FROM @stmt;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
delimiter ;

And this can be used (for N=20,L=25,K=3) by command:

CALL populate(20,25,3);

Sources:

Loops in MySQL

  https://stackoverflow.com/questions/1485668/how-to-set-initial-value-and-auto-increment-in-mysql

Execution of text variable as query

  https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure


Importing large SQL dump with millions of INSERT statements I need to import a big .sql file (8.1GB when unpacked) into PostgreSQL. I tried to use \i /path/to/file.sql but it is way too slow.

How does one speed up the import? I need to import this data weekly.

The first 2000 lines can be found here, while the compressed 1 GB dump can be found here

--
-- PostgreSQL database dump
--

-- Dumped from database version 9.5.3
-- Dumped by pg_dump version 9.5.2

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

--
-- Name: rpo; Type: SCHEMA; Schema: -; Owner: -
--

That's the only way I can get the data. The full file has approx. 38,000,000 lines. How can I speed up the import?

This dump was dumped as individual statements (with pg_dump --inserts)

INSERT INTO esa2010_codes VALUES (11002, 'Národn
INSERT INTO esa2010_codes VALUES (11003, 'Nefina
INSERT INTO esa2010_codes VALUES (12502, 'Národn
INSERT INTO esa2010_codes VALUES (11001, 'Verejn
INSERT INTO esa2010_codes VALUES (12602, 'Národn
INSERT INTO esa2010_codes VALUES (12603, 'Finanč
INSERT INTO esa2010_codes VALUES (12503, 'Ostatn

This is documented as being slow (from man pg_dump)

  --inserts Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.

That's why it's so slow. What you're going to want to do is to turn off some of the durability settings, specifically synchronous_commit, though fsync will help too

You can do this very simply by running the following command before you run your \i file.sql.

SET synchronous_commit TO off;

That will do a lot to speed it up. Don't forget to turn back on the durability options after you're done. I bet it'll finish in a few hours, after you're set that. If you need more speed though don't hesitate to turn off fsync and full_page_writes on the cluster until you get the data up -- though I won't do it if the DB had data you needed in it, or if it was production. As a last note, if you need the speed and this is a production DB you can go all out on your own copy and dump it with the default options by pg_dump, which you'll be able to load much faster.


How to optimize search restricted by boolean column? I want to optimize a query1 of the form

SELECT yid, xid FROM x WHERE is_principal;

Here, is_principal is a non-null boolean column of x.

If I run EXPLAIN QUERY PLAN on this query, the output I get is

0|0|0|SCAN TABLE x

I'd like to turn this SCAN into a SEARCH, so created the following index:

CREATE INDEX x_is_principal_idx ON x (is_principal);

...but it made no difference: the output of EXPLAIN QUERY PLAN remains unchanged.

Is there some other way to optimize this query?

1 FWIW, this query is in fact a sub-query of a much larger query that I'm trying to optimize.

The expression is_principal is the same as is_principal <> 0, so the database assumes that all values except 0 are searched for, and estimates that most rows will match.

If the values in the is_principal column are proper booleans, you can get the database to assume a higher selectivity by searching for the value you actually want to search for:

SELECT yid, xid FROM x WHERE is_principal = 1

This query is likely to read the rows from the table in index order (i.e., random order), so if there is a large number of matching rows, performance will be worse.


Inserting data into a table with one value as clob and blob My table has one clob column and one blob column. I need to insert data into it through insert statements.

The length of data to be inserted is more than 4000 chars.

When I do export of insert statements is sql navigator I get empty values.

INSERT INTO mytable VALUES('REQUEST',EMPTY_CLOB(),EMPTY_BLOB());

I also tried to do insert it data like this.I have added only example data to be inserted.The actual data to be inserted is large.

INSERT INTO mytable VALUES('REQUEST2','{"customer"asxcbasjbab....:}',NULL);

Can some one tell me best way to insert data into table in these scenario.

Or anyway to do export and import this data will also help.

In SQL, the limit is 4,000 characters.  Using straight SQL like that, without a bind
variable, you'll be limited to 4,000 characters.
Steps to insert lob values :

1) Create a table and name it TBL_CLOB with 2 fields:

   id_int = integer;
    clob_txt =clob;

2) Create a stored procedure and name it P_CLOB  with the following
code:

    (P_ID_INT in int,P_CLOB_TXT in varchar2)as

    begin
    insert into TBL_CLOB values(P_ID_INT,P_CLOB_TXT);
    end;

3) Test inserting up to 32000. Use SQL Plus and enter some starts in
the CLOB field:

    SQL>  exec p_clob(1,rpad('*',32000,'*'));

    SQL> commit;

    SQL>  exec p_clob(2,rpad('*',19872,'*'));

    SQL> commit;

4) Retrieve the 2 records you just inserted and count the number of
characters in the CLOB fields:

    SQL> select id_int,dbms_lob.getlength(clob_txt) from tbl_clob;

5) You should get something like this:

        ID_INT DBMS_LOB.GETLENGTH(CLOB_TXT)
    ---------- ----------------------------
             1                        32000
             2                        19872

Makes sense ?


Should a TeamMembers table have a relationship with the Student Table or the Enrolment Table? I'll keep it nice and short and simply link to the ERD:

Users can be assigned a role, one of those is student.
So for the sake of this, Students are the same as Users!

A Student can be enrolled in many OfferedUnits. A Student can also be in Many Teams and a Team can have Many Students.

The issue I am having is this: Should the Many-to-Many relationship that allows a Student to be in many teams and a Team to have many students be between the Users Table and the Teams Table or the Enrollment Table and the Teams table?

The argument for the Enrollment option is as such: You will only be able to assign a Team to Enrolled students. That's the only reason I can think of doing it that way.

Otherwise, it logically doesn't make sense to connect Enrollment to Teams, because it's the STUDENT who is in a Team, not their Enrollment.

What do you think?

I would have assumed that one team can have many users and one user can be part of many teams thus creating a new junction table, Users_Team. From there I would have related users to Enrollment or user_team to enrollments depending on business logic. Am I right in assuming that enrollment will contain recrods such as Maths, Science, Art etc...?

Does it make sense for a user to be enrolled in maths as a individual, and also as a team?

From my understanding, I would drop that TeamMembers table.


Importing a single large table in Azure I have an existing DB called MyDb in Azure SQL Server.
I have a bacpac of another DB with several tables in it. I'm interested in importing one single table (that table has no FK, it makes things easier) into a dedicated table MyDb.dbo.ImportedTable. The final goal is to be able to do some data reconstruction using that table.

Problems are:

MyDb.dbo.ImportedTable is ~60 Gb large
The main column in that table is a NVARCHAR(MAX). That forbids me to use Elastic queries in Azure. It times out since Elastic queries hates anything larger than NVARCHAR(4000) (I tried)

I guess a good approach is:
 1. Use BCP but I only have the binary *.bcp files (15'000 of them) that are inside the bacpac archive (opened as a zip, in its data folder)

But I'm unable to make it work, especially because I find no documentation about the *.bcp file format used in the bacpac.

tl;dr What is the good approach to import a single ~60Gb table fro ma bacpac in an existing database in azure SQL Server?

It seems there is no way to simply use the .bac files fomr inside the .bacpac archive.
I had then to revert to bcp to  create a text file that will be the source of another bcp command to create import the dat in the new DB I want.

To export the data and create the file:

bcp "SELECT * FROM $table WHERE someColumn = someValue" queryout "${table}.csv" -U $user -P $password -S $server -d $dbSource -N -k

And then to reimport it as a new table

bcp [$dbTarget].[dbo].[$table] IN "Fresh${table}.csv" -U $user -P $password -S $server -E -N -k


DB2 LOAD data into table - Interpreting the error messages I encountered some constraint issues when loading data from file into table

**
SQL3109N  The utility is beginning to load data from file
"xag".
SQL3500W  The utility is beginning the "LOAD" phase at time "07/30/2012
23:33:53.199615".
SQL3519W  Begin Load Consistency Point. Input record count = "0".
SQL3520W  Load Consistency Point was successful.
SQL3116W  The field value in row "F12-33856" and column "9" is missing, but
the target column is not nullable.
SQL3185W  The previous error occurred while processing data from row
"F12-33856" of the input file.
SQL3227W  Record token "F12-33856" refers to user record number "440439".**

db2 LOAD client from data  of del MESSAGES msg replace INTO TABLE_L ;

I thought this message is telling me that we have an constraint issue on row 440439, but seems it is not the case. anyone know how to read the messages ?

According to IBM SQL3227W states:

  "Record token token1 refers to user record number token2.
  Explanation

  An error or warning was encountered during LOAD, IMPORT or EXPORT of a
  table. CPU parallelism was greater than 1 at the time the problem was
  encountered, and an SQL message was written which identified the user
  record with a special unique token. This message serves to map the
  unique record token to the record number of the source user data.
  User response

  Refer to the original SQL message returned for appropriate action."

So this refers you back to the previous error. And the next one SQL3185W says:

  "The previous error occurred while processing data from row row-number
  of the input file.
  Explanation

  This message provides the identification of the row where the error
  occurred for the previous message listed in the message file (for
  example, SQL3306).
  User response

  No action is required."

So...Phil nailed it on the head, we really need to see what comes before these messages. Do you have a log of the output that you could post to your question? We could better help you with interpreting results then.

That being said, I would highly encourage you to poke around Information Center like I did as it can best help you find what those pesky "SQL" messages mean. The links I found were for 9.5. Just switch to the version of DB2 that you are using to make sure you have the most accurate information. I use Information Center for just about everything.

  EDIT: Your updated helps a lot. Here is the error:

SQL3116W  The field value in row "F12-33856" and column "9" is missing, but
the target column is not nullable.
SQL3185W  The previous error occurred while processing data from row
"F12-33856" of the input file.
SQL3227W  Record token "F12-33856" refers to user record number "440439".**

  In short, it looks like a null was written to "column 9" of our table
  and that you have a not null constraint on that column. Now the trick
  is locating which record had the issue. It is marked with two tokens
  it looks like. (F12-33856 and 440439). I guess I'd start with 440439
  first. That seems to me like it could be the primary key. Just a
  guess. But at least you know your issue is a null value for a non-null
  field.


Running identical queries in sysbench When benchmarking using sysbench and utilizing concurrency (multiple threads) for example on a table with 1,000,000 records, does sysbench run "identical" queries in a loop on the table?

You can create several test types with sysbench, but if you look at the typical OLTP tests for SELECTS and INSERTS you will see that the queries executed are similar in pattern, but with random values for each query (not identical every time).

If they were identical (something that you could test if you wanted), structures like the query cache and buffer pool would be very effective, invalidating the reason for the stress test.


Fastest key-value store for random disk reads? I want a key-value store, with records stored on disk. (There are too many for memory.) I want to use a single machine.

What key-value store will provide the fastest random reads (off disk)?

Without context, this is a poor question.

Bound to a single machine your requirement is a function of IO performance, not platform. An Access mdb file on a FusionIO card could outperform Trinity on a 5400rpm drive in a narrow band of tests.

You'll have to be more specific if you want answers of any value.

Edit: following comment.

Context would be a description of what you're building. As I indicated, whichever k-v system you choose you will be IO bound when constrained to a single machine. On EC2 block storage the choice of k-v becomes even more irrelevant.

If you're building on EC2 look at the native products they already provide e.g. SimpleDB or Elasticache.


mySQL efficiency with inner join queries I have two tables, both with about 1,000,000 rows.

Here is the question I am trying to get mySQL to solve:

return all record_ids from table_a where that id matches the cat_id in table_b, but only for customer_id 654 (customer_id exists in both tables).

First off I tried this with the following statement:

SELECT record_id from `table_a` where customer_id="654" and record_id in
(SELECT cat_id from `table_b` where cat_id="654");

This is the most logical looking one to me, as it's more at my coding level.  But this query took 2 minutes to run.  So I tried to re-write it using:

SELECT record_id from `table_a` where
customer_id = "654" and exists (SELECT cat_id FROM `table_b` where
table_b.customer_id = "654" and
table_a.record_id= table_b.cat_id
group by record_id having count(*) > 1);

This took 22 seconds to run.

I tried another way as well:

SELECT table_a.record_id from table_a
INNER JOIN table_b ON (table_a.record_id = table_b.cat_id)
WHERE table_a.customer_id = "654" and table_b.customer_id= "654"
GROUP BY table_a.record_id
HAVING COUNT(*) > 1;

This one takes 19.5 seconds to run.

I'm wondering if my third attempt is the most efficient way to ask this question, or as I'm hoping, there is a more efficient way to run these.

Here is mySQL's execution path for the last two queries:

On table_a, I have the following indices:

KEY `rc` (`record_id`,`customer_id`)
KEY `test` (`record_id`,`customer_id`),
KEY `test2` (`record_id`,`customer_id`),
KEY `cust` (`customer_id`)

On table_b, I have the following indices:

KEY `rc` (`cat_id, `customer_id`)

There are duplicates that I made on accident and I haven't gotten around to deleting them, (not sure if that impacts performance):

Based (only) on your query i think the better query could be :

 SELECT table_a.record_id from table_a
 INNER JOIN table_b ON (table_a.record_id = table_b.cat_id)
 WHERE table_a.customer_id = "654" and table_b.customer_id= "654"
 GROUP BY table_a.record_id
 HAVING COUNT(*) > 1;

the correct index could be in   table a  only this:

KEY cust_and_rec (customer_id, record_id)

in table b only

KEY cust (custmer_id)

In the index you show in your question seem rc, test and test2 are the same and not  so selective like the inverse (customer_id is the most selective value in your query )

index rc on table b seem not useful for your purpose.


Storing math,physics equations in mysql Database I am working on a project where different unicode characters has to be stored in table.
math equations, physics formulae , subscript  X2 , superscript X2.

greek characters like Link

I have made all tables collation and charset as utf16 , but problem I am facing is in data entry.

I have to manually enter data into tables because of some reasons, I am using sequel pro tool for data entry, many characters coming fine but subscript and superscript not coming properly, they will be treated as normal character. Also I am not sure whether sequel pro supports any kind of data or not, later I may face problem with some other characters. So is there any proper way to deal with this?

Also let me know whether utf16 is ok for collation and charset ?

Kindly let me know if any such tool which support all kind of data.

Do you have some way of entering utf16?  I don't know of such, except internally in Java.  The world is moving toward utf8, not utf16.

I recommend using utf8mb4 throughout.  The collation may not matter; the default is utf8mb4_general_ci, but _bin or _unicode_ci are also possibilities.

A little history lesson on why I suggest utf8mb4 instead of utf8.  More than a decade ago, MySQL added utf8, but coded it only to the extent of the 3-byte encodings.  Recently, a number of 'new' Chinese characters have come out that need a 4-byte encoding.  And there are other sets that need 4 bytes.  MySQL realized that expanding its utf8 to 4 bytes would be far too incompatible, so it added utf8mb4 (think "MultiByte4").  A lot more info on utf8, etc:  http://mysql.rjweb.org/doc.php/charcoll


How To Prevent Replication Failure If I become a MySQL DBA, will I had to deal all day with those kind of issues or do you have tips to prevent from breaking the whole replication?

I received this message because I removed manually the database, and after that the php script remove it.

Last_SQL_Errno: 1133`
Error 'Can't find any matching row in the user table' on query.`


IDENTITY_INSERT seems stuck I might be forgetting something, but essentially, I'm struggling with IDENTITY_INSERT.

I'm trying to move data between tables, both of which have an identity column (similar schema).

SET IDENTITY_INSERT DestinationTable ON;

returns that IDENTITY_INSERT is already ON for that table. (Error Msg 8107) Fine, it's already ON, I can work with that, right?

Then:

INSERT INTO DestinationTable (Id, ColA) SELECT Id, ColA FROM SourceTable;

returns that I can't insert an explicit value .. when IDENTITY_INSERT is OFF (Error Msg 544). But it just said it was ON.

So, what's going on?

EDIT:
Actual script (save for table names and structure):

SET IDENTITY_INSERT User2.DestinationTable ON;
INSERT INTO User2.DestinationTable (Id, ColA) SELECT Id, ColA FROM User1.SourceTable;
SET IDENTITY_INSERT User2.DestinationTable OFF;

Is it possible you have two tables with the same name in different schemas? Some random doc I found online states that message 8107 appears when you already have IDENTITY_INSERT set to ON for another table in the db...

Another debugging step to check: You state that the issue keep occurring even after you drop and recreate the table, but what if you drop and don't recreate the table, and try to enable IDENTITY_INSERT on another table? What happens then?


How about this (untested)?

select *
from log_table x
where task_id = 7
and not exists (
  select 1
  from log_table
  where case_number = x.case_number
  and task_id = x.task_id
  and action_id = 2
);


Deleting duplicate subscribers using max uuid postgresql I am trying to eliminate duplicate subscribers from the subscribers table for a api_customer_id, this is the query that I use to get those duplicate subscribers :

select subscribers.email, count(*) from subscribers
inner join subscriptions on subscriptions.subscriber_id = subscribers.id
inner join subscription_lists on subscription_lists.id = subscriptions.subscription_list_id
where 793 in (subscribers.api_customer_id, subscription_lists.api_customer_id)
group by subscribers.email
having count(*) > 1

I tried to delete everything that is not max(subscribers.id), but I could not because the id column type is uuid. Here is that error :

ERROR:  function max(uuid) does not exist
LINE 1: select max(subscribers.id), subscribers.email, count(*) from...
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
SQL state: 42883
Character: 8

What can I do in the meantime to get rid of the duplicates?
There is created column on the subscribers, but the times are very close together that I'm fearful to use that to max/min on. Any other suggestions?

Update 1:

Per question in the comments I do have another column updated at too, they look like this :

But they're all in the same second, and it's still risky. I don't have the flexibility to change this design now, maybe I can going forward.

Here is a generic method that works as long as there is a lower-than operator on the column used to discriminate  (which is the case of uuid).

WITH dups as (
  select email, id FROM...< complete this with the duplicates detection>
)
DELETE FROM subscribers USING dups
WHERE dups.email = subcribers.email
  AND dups.id < subscribers.id;

You may replace id by a creation timestamp if these timestamps are guaranteed to be different. Even if they're not different, meaning there are (email,timestamp) duplicates, they will just be not touched by this DELETE. You can then do a second pass with another DELETE, this time with the uuid as the differentiator as in the query above. If the timestamps were all different this 2nd pass will delete nothing, otherwise it will finish the job.

Also you may do this in a transaction and check if there are still duplicates and the number of deleted rows seem good before doing a COMMIT or ROLLBACK.


Clustered columnstore index on small tables Clustered column store indexed tables in general are useful for large tables. Ideally with million of rows.
And also useful with queries, which selects only the subset of available columns in such tables.
What happens if we break these two "rules"/best practices?

Like having a clustered column store indexed table which will only store few thousand, or hundreds of thousands of rows max.
And running queries against those clustered column store table where all the columns are needed.

My tests don't reveal any performance degradation comparing to row stored clustered index table.
Which is great in our case.
Is there any "long term" effects breaking these two rules? Or any hidden pitfalls which haven't showed up just yet?
Context why is it needed: I designed a database model which will be used for many instances of different vendor databases. The schema remains the same in every database, but different vendors have different amount of data. Hence few small vendors may end up with small amount of data (<1 000 000) in their tables. I can't allow myself to keep up two different database for row-store and column-store model.

Columnstore index has a big advantage on compress data size. The general aim of the Columnstore index is quickly read a bunch of data due to its compression.

CCI is Columnstore Clustered Index, Clustered is Clustered Index
Columnstore Index compresses data size from 4MB to 2MB.
We can look at the performance in two tables and three parts.
The first one is the minimal SELECT operation:
SELECT * FROM Users_CCI WITH(INDEX=CCI_Users) WHERE Id=12333

SELECT * FROM Users_Clustered WHERE Id=12333

Result: There is a Columnstore Scan here and wrong estimated. Further, logical read difference. You can say it doesn't matter for you, but, If you use a minimal SELECT query, probably use thousands of times. And it will affect total performance.
The second one is the minimal UPDATE operation:
UPDATE Users_CCI SET Age=10 WHERE  Id=2

UPDATE Users_Clustered SET Age=10 WHERE  Id=2

Result: There are read, CPU, and time differences as we can see.
The third one is the REBUILD operation:
USE [StackOverflow2013]
GO
ALTER INDEX [CCI_Users] ON [dbo].[Users_CCI] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
USE [StackOverflow2013]
GO
ALTER INDEX [PK_Users_Clustered_Id] ON [dbo].[Users_Clustered] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

UPDATE Users_CCI SET Age=10
UPDATE Users_Clustered SET Age=10

If I rebuild and update all rows for index fragmentation, I will see a more fragmented Clustered Columnstore Index than the Clustered Index. And I didn't show, but the Clustered Columnstore Index Rebuild process produces transaction log more than Clustered Index.

Like the document says

More than 10% of the operations on the table are updates and deletes. Large numbers of updates and deletes cause fragmentation.The fragmentation affects compression rates and query performance until you run an operation called reorganize that forces all data into the columnstore and removes fragmentation. For more information, see Minimizing index fragmentation in columnstore index.

If you have a small table you don't need to Columnstore index.


Should snapshot agent continue to run in transactional replication? We have transactional replication running for a large number of publications on sql server 2008 R2 2 node active/active cluster.  I have noticed that the snapshot agent job runs hourly and it looks like it does a refresh of the publications (literally, a new snapshot?).  During this time we experience locking issues with the blocker being this job.  Should this be doing a refresh this often if no new articles are being added or changed?

I have noticed that the snapshot agent job runs hourly and it looks like it does a refresh of the publications (literally, a new snapshot?) Should this be doing a refresh this often if no new articles are being added or changed?

NO. Snapshot should not be run frequently, unless you are adding/dropping articles.

Also, Transactional replication uses Concurrent Snapshot processing; meaning it does not hold share locks in place during the entire snapshot generation, thereby allowing users to continue working uninterrupted while replication creates initial snapshot files.

The sync_method parameter governs which mode is used - native, character, concurrent or concurrent_c.


What does exchangevent indicate in SQL deadlock graph? Have a deadlock XDL file with several exchange events listed.  Not sure if these are good or bad or what causes them.  What causes exchange events in SQL deadlock graph?
 <exchangeEvent id="Pipe12dfb4bc900" WaitType="e_waitPipeGetRow" nodeId="28">
   <owner-list>
    <owner id="process4a9ee4eca8" />
   </owner-list>
   <waiter-list>
    <waiter id="process2612ca8" />
 </waiter-list>
  </exchangeEvent>

Exchange Events provide details and context on the deadlock itself. Specifically the WaitType e_waitPipeGetRow is a resource that one SELECT statement owns and another SELECT statement is waiting on, ergo being blocked by the first SELECT (for example due to resource contention). This StackOverflow answer provides more context on it and deadlocks specifically.
I'm not an expert on reading deadlock graph data, but my research seems to indicate that kind of WaitType is potentially related to intra-query parallelism deadlocks (as mentioned in the previously linked StackOverflow answer).
You may find SentryOne's higher level read on deadlocks helpful in understanding what's going on.


Create Copy of all rows where foreign key matches specified and then replace foreign key I'm new to database administration (this is my first time) and have been tasked with creating a database with functionality that allows users to select a "Model" as a current state which can then be used as a default/baseline for a new Model when editing.

My question would be, how does one copy every single row from multiple tables where the foreign key matches the Model_ID of the selected Model to then be used in a new Model?

Would this require selecting the rows from the tables into a tempdb and then re-inserting into the originaldb, this seems quite resource heavy for such a task. Is there a better solution?

I've attached an img of the database design I have created just in case it is of use. help and advice would be greatly appreciated, Thank You.

Update

After taking the suggestion of Akina, this is my plan on how the rows will be duplicated for user usage (Pseudocode). If there are any glaring errors feel free to correct.

@X int -- the selected existing Model taken as a paremeter
@new_model_Name -- fields to be entered into the model table
@new_model_completionUser -- fields to be entered into the model table
@new_model_dateCreated -- fields to be entered into the model table

insert into model values (new_model_Name, new_model_completionUser, new_model_dateCreated)

insert into table1 (old_data1, old_data2,..., New_Model_ID)
select * from table1 where Model_ID = X

insert into table2 (old_data1, old_data2,..., New_Model_ID)
select * from table2 where Model_ID = X

--etc

Modelling algorythm.

Assume we have Main table with the fields ID and Name. Assume we have table Details referenced to Main table via ID_Main field (FK) and having Detail field.

Assume we selected the record with some ID from table Main with some name. We altered this name, and now we want to create new records pack - with new Name and with the copy of Detail value from source record.

First, we must ensure that new name value is not present in Main table (we do not like full duplicates, is it?). So we query

SELECT COUNT(*) FROM Main WHERE Name = 'New Name Value'

and check that its result is zero ('0'). If not, this value is already present, and we tell it to operator.

If the value returned is zero, we can store data. We insert it with the query

INSERT INTO Main (Name) VALUES ('New Name Value')

The record is inserted, and some ID is assumed to it. We may:

1) obtain it by simple query

SELECT id FROM Main WHERE Name = 'New Name Value'

and then use this value in the query which adds data to the table Details referenced to this record

INSERT INTO Details (Main_ID, Detail) VALUES ('obtained ID value', 'New Detail value')

2) do not obtain ID separately, doing this in a INSERT query

INSERT INTO Details (Main_ID, Detail)
SELECT id, 'New Detail value' FROM Main WHERE Name = 'New Name Value'

PS. For SQL Server you can obtain ID of last record using select scope_identity(), select @@identity or select ident_curent(‘tablename’) dependent by what you need.


Enumerate drives available to SQL Server I'm trying to work out a way of listing the drives that a particular instance of SQL Server is "aware" of, and understand the process.

I can run EXEC master..xp_fixeddrives which gives me a list of drives, but when I look in the GUI (Eg. Databases | Restore Database | From device | Add ) the list of available drives is far fewer.

By what process does SQL server become "aware" of a drive and is there any way to problematically determine the drives it is aware of?

Thanks in advance!

I'm guessing you're actually running on a clustered instance.

EXEC master..xp_fixeddrives is returning all the drives that the underlying OS is aware of, so a lot aren't available to your cluster instance

use:

SELECT * FROM sys.dm_io_cluster_shared_drives

instead, which will just list the ones available to your instance


MySQL: update field with “x hours xx minutes”using int from another field We have two fields that hold time data

totalTime (int): number of minutes (eg 243)
totalTimeDesc (string): normal english representation (eg '4 Hours 3 Minutes')

We have a lot of different formats in totalTimeDesc ('5 min', '5m', '5 minutes', etc) and want to calculate a uniform string using the totalTime field and save that in totalTimeDesc.

Can I do this inside mysql, or do I need to write a script to make those changes?

I haven't written stored procedures before, but this might be a good use case for them.  Here is a snippet of JS that we used to do the calculation/formatting in a similar case:

// get value & calculate time/format
var mins = $(this).val();
var calcMin = mins % 60;
var calcMinText = (calcMin > 1) ? "Minutes" : "Minute";
var calcHrs = Math.floor(mins / 60);
var calcHrsText = (calcHrs > 1) ? "Hours" : "Hour";

// set time/format as calcDesc
var calcDesc = "";
if(calcHrs > 0) { calcDesc = calcHrs+" "+calcHrsText+" "; }
if(calcMin > 0) { calcDesc += calcMin+" "+calcMinText; }

I have a single MySQL query that do all the heavy lifting

SELECT TimeDisplay FROM
(
    SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT 243 sec_a) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) AAAAAA;

When you cut and paste it, this is what it looks like

mysql> SELECT TimeDisplay FROM
    -> (
    ->     SELECT TRIM(REPLACE(CONCAT(IF(dy=0,'',
    ->     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    ->     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    ->     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    ->     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    ->     FROM
    ->     (
    ->         SELECT dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    ->         (
    ->             SELECT dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    ->             FROM
    ->             (
    ->                 SELECT dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    ->                 (
    ->                     SELECT FLOOR(sec_a/86400) dy,sec_a sec_aa
    ->                     FROM (SELECT 243 sec_a) A
    ->                 ) AA
    ->             ) AAA
    ->         ) AAAA
    ->     ) AAAAA
    -> ) AAAAAA;
+---------------------+
| TimeDisplay         |
+---------------------+
| 4 minutes 3 seconds |
+---------------------+
1 row in set (0.00 sec)

mysql>

The key to using this query is this section

FROM (SELECT 243 sec_a) A

Simply replace the 243 with totalTime and you are off and running.

Your Original Question

Would you like to see a very wicked UPDATE JOIN to fix all totalTimeDesc fields?

Sample Data

use test
drop table if exists mytimes;
create table mytimes
(
    id int not null auto_increment primary key,
    totalTime int not null,
    totalTimeDesc varchar(128)
);
insert into mytimes (totalTime) values (243),(2430),(24300),(243000);
SELECT * FROM mytimes;

Proposed UPDATE JOIN

UPDATE mytimes J INNER JOIN
(
    SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT id,totalTime sec_a FROM mytimes) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) K USING (id)
SET J.totalTimeDesc = K.TimeDisplay;

If you look carefully, I slipped in

FROM (SELECT id,totalTime sec_a FROM mytimes) A

in place of

FROM (SELECT 243 sec_a) A

and propagated the id column through subqueries

Sample Data Loaded

mysql> use test
Database changed
mysql> drop table if exists mytimes;
Query OK, 0 rows affected (0.01 sec)

mysql> create table mytimes
    -> (
    ->     id int not null auto_increment primary key,
    ->     totalTime int not null,
    ->     totalTimeDesc varchar(128)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytimes (totalTime) values (243),(2430),(24300),(243000);
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM mytimes;
+----+-----------+---------------+
| id | totalTime | totalTimeDesc |
+----+-----------+---------------+
|  1 |       243 | NULL          |
|  2 |      2430 | NULL          |
|  3 |     24300 | NULL          |
|  4 |    243000 | NULL          |
+----+-----------+---------------+
4 rows in set (0.00 sec)

mysql>

Proposed UPDATE JOIN Executed

mysql> UPDATE mytimes J INNER JOIN
    -> (
    ->     SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    ->     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    ->     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    ->     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    ->     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    ->     FROM
    ->     (
    ->         SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    ->         (
    ->             SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    ->             FROM
    ->             (
    ->                 SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    ->                 (
    ->                     SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
    ->                     FROM (SELECT id,totalTime sec_a FROM mytimes) A
    ->                 ) AA
    ->             ) AAA
    ->         ) AAAA
    ->     ) AAAAA
    -> ) K USING (id)
    -> SET J.totalTimeDesc = K.TimeDisplay;
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM mytimes;
+----+-----------+----------------------------+
| id | totalTime | totalTimeDesc              |
+----+-----------+----------------------------+
|  1 |       243 | 4 minutes 3 seconds        |
|  2 |      2430 | 40 minutes 30 seconds      |
|  3 |     24300 | 6 hours 45 minutes         |
|  4 |    243000 | 2 days 19 hours 30 minutes |
+----+-----------+----------------------------+
4 rows in set (0.00 sec)

mysql>

UPDATE 2014-07-23 16:20 EDT

I just realized, your units are in minutes not seconds

Here is the quick fix

UPDATE mytimes J INNER JOIN
(
    SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    FROM
    (
        SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
        (
            SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
            FROM
            (
                SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
                (
                    SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
                    FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A
                ) AA
            ) AAA
        ) AAAA
    ) AAAAA
) K USING (id)
SET J.totalTimeDesc = K.TimeDisplay;
SELECT * FROM mytimes;

I changed

FROM (SELECT id,totalTime sec_a FROM mytimes) A

to

FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A

If you rerun the UPDATE JOIN, you get this

mysql> UPDATE mytimes J INNER JOIN
    -> (
    ->     SELECT id,TRIM(REPLACE(CONCAT(IF(dy=0,'',
    ->     IF(dy=1,'1 day ',CONCAT(dy,' days '))),
    ->     IF(hr=0,'',IF(hr=1,'1 hour ', CONCAT(hr,' hours  '))),
    ->     IF(mn=0,'',IF(mn=1,'1 minute ',CONCAT(mn,' minutes '))),
    ->     IF(sc=0,'',IF(sc=1,'1 second ',CONCAT(sc,' seconds ')))),'  ',' ')) TimeDisplay
    ->     FROM
    ->     (
    ->         SELECT id,dy,hr,mn,MOD(sec_aaaa,60) sc FROM
    ->         (
    ->             SELECT id,dy,hr,FLOOR((sec_aaa-dy*86400-hr*3600)/60) mn,sec_aaa sec_aaaa
    ->             FROM
    ->             (
    ->                 SELECT id,dy,FLOOR((sec_aa-(dy*86400))/3600) hr,sec_aa sec_aaa FROM
    ->                 (
    ->                     SELECT id,FLOOR(sec_a/86400) dy,sec_a sec_aa
    ->                     FROM (SELECT id,totalTime*60 sec_a FROM mytimes) A
    ->                 ) AA
    ->             ) AAA
    ->         ) AAAA
    ->     ) AAAAA
    -> ) K USING (id)
    -> SET J.totalTimeDesc = K.TimeDisplay;
Query OK, 0 rows affected (0.01 sec)
Rows matched: 4  Changed: 0  Warnings: 0

mysql> SELECT * FROM mytimes;
+----+-----------+---------------------------+
| id | totalTime | totalTimeDesc             |
+----+-----------+---------------------------+
|  1 |       243 | 4 hours 3 minutes         |
|  2 |      2430 | 1 day 16 hours 30 minutes |
|  3 |     24300 | 16 days 21 hours          |
|  4 |    243000 | 168 days 18 hours         |
+----+-----------+---------------------------+
4 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


"Skipping the default startup of database" SQL notification for AlwaysOn Cluster So I set up our SQL servers to notify us of alerts at severity 17 or higher (as advised by various SQL Server articles). I've done this to each node of a SQL Server Always On Availability Cluster, with three multi-subnet nodes.

Over the weekend we've received almost a hundred notifications about a single "informational only" message from one of the secondary nodes. The other nodes aren't doing this and the alert doesn't seem to be causing any actual problems. The alert is:

DESCRIPTION:    Skipping the default startup of database '<database>' because the database belongs to an availability group (Group ID: <...>).
The database will be started by the availability group.
This is an informational message only. No user action is required.

Possible culprit: Our network has a few issues, the node could be disconnecting and reconnecting or timing out.

I suppose my questions are, is there any action I should take to resolve this alert? Why is it only affecting one of my nodes? If it really is informational and no action needs to be taken, can I configure my alerts to ignore these alerts?

Upon a comment, I looked at the logs and it appears that the server has been running all that time but that it disconnected from the always on server around that time:

AlwaysOn Availability Groups: Local Windows Server Failover Cluster node is no longer online.
The Availability replica is going offline because the local ...
The Waiting for local Windows Server Failover Clustering service to start ...

(Then it goes through the informational messages regarding starting up each database)
Local Windows Server Failover Clustering service started.

Same kind of message appeared in our environment as well.

What I understand is, as the DB was in AAG, and when the node (secondary AAG group) goes under restart, the DB start-up will be taken care of by AlwaysOn instead of the regular SQL Server 3-phase process!

If the message appears without any secondary node restart, we may have to consider troubleshooting, but in our condition we can ignore that as the message states it is just informational.


Use previous query result in a query I'm building a football database. I store data about the position, points, etc of different teams in the past years.

I'm trying to write a query which gets all the teams which have ascended and then descended again. In order to do that, I've the following query.

SELECT DISTINCT team
    FROM Result
    WHERE number=42 AND
        situation='ASCENT'
INTERSECT SELECT DISTINCT team
    FROM Result
    WHERE number=38
        AND situation='DESCENT'
        AND league='Premier league';

The key is that I need to express "temporality"; the year of the ascend has to be previous than the the year of the descend. In my Result table I've a year field, which I could use in order to compare the temporal order, but I don't know how to do it.

You can join the Result table to itself like this:

SELECT
  team
FROM
  Result A
  JOIN Result D ON D.team = A.team AND A.year < D.year
WHERE
  A.number=42
  AND
  A.situation='ASCENT'
  AND
  D.number=38
  AND
  D.situation='DESCENT'
  AND
  D.league='Premier league'


Cannot add database to SQL Always ON I have problems adding a databases to Always ON.
When I try to connect to named instance I get an error:

Names are correct. Also when I try to connect from server a to server b with server\instance is giving same error, but just server is working? But here server name is greyed out so I cannot change it?
On server itself when I use server\instance is working.

I did not set DNS Aliases, maybe I need to do that?

Any ideas?

Thanks

When named instances are having issues being connected to, there is generally an issue with one of the following:

SQL Browser isn't running
SQL Browser ports are blocked (1434 UDP)
Incorrect DNS entries
SQL TCP Port is blocked (custom port number for named instance)
SQL TCP Port is blocked (Dynamic ports)
TCP is not an enabled protocol

To workaround it, either start the SQL Browser service or unblock the port(s).

Additionally, with the SQL Server Management Studio connection dialog it is possible to press the "options" button in the lower right hand corner and override any of the preset options. In this instance, using SERVER=MyServer,Port to specify the server name and port number overrides the SQL Browser port lookup and allows for a direct connection without name to port resolution.


Issues with a Table having 120 fields in mysql I am creating a table with 120 fields in MySQL. It will have 50 varchar(255) fields, other are maximum fields with char(1) , 1 date field and 5 double fields and others are int(11) fields.

My question is - will it cause any problem in future?

You need to stop and think about the design of the table. If you have that many columns, then chances are you may need to do some normalization of the table.

Although denormalized data can help you bypass the need to do JOIN queries to retrieve many columns of data, try to imagine what is happening to MySQL internally during data retrieval.

If a 120 column table undergoes SELECT * FROM ... WHERE, you would potentially cause the following:

generate large temp tables
fetch columns that are unnecessary
handle many variable length fields

This would easily cause communication packets (sized by max_allowed_packet) to be pushed to the brink on every query.

In my earlier days as a developer, I worked at a company back in 1995 where DB2 was the main RDBMS. The company had a single table that had 270 columns, dozens of indexes, and had performance issues retrieving data. They contacted IBM and had consultants look over the architecture of their system, including this one monolithic table. The company was told "If you do not normalize this table in the next 2 years, DB2 will fail on queries doing Stage2 Processing (any queries requiring sorting on non-indexed columns)." This was told to a multi-trillion dollar company, to normalize a 270 column table.

Please see my other posts around this subject

Jul 30, 2011 : More than 20 columns in database table MySQL
Jul 20, 2011 : Too many columns in MySQL


How do I handle a single `reference` to multiple different types of data? I host a file sharing site.  The idea is to take a short string (e.g. abcd) and do one of the following:

serve an image
display some text
redirect to another url

Historically, I've hosted this site using non-relational databases, where I'd store it as something like:
{
  short: "abcd",
  type: "url", // or image or text
  // type specific data, e.g. for a url
  url: "https://google.com"
  // or for a file
  store: "/cool_store",
  filesize: 10,
  etc.
}

I've redesigned the platform and decided to use SQLite / Postgres instead.  I'm not that used to schema design and my first design of (not actual code, heavily simplified):
CREATE TABLE Items (
    short varchar(64),
    store varchar(255),
    filesize int
);

Which worked great for the image hosting side of things.  Now I want to expand to the other types and can't decide the best approach.  My two main ideas are:

Replace the singular table with one 'reference' table and separate 'item' tables for each different type, e.g. References { id: 'abcd', type: 'url', ref: 1 } (where the ref represents the primary key of a different table).  Items { id: 1, store: "/cool_store", filesize: 10 }.  Urls { id: 2, url: "https://google.com" }, etc.
Store all the properties required on a single table with a large proportion of the columns being null.  In total we require ~50 columns, with ~30 being null at any point in time.

The database is exceptionally read-heavy, maybe 99.99% reads.

You should group the fields closest related to each other in the same Table and create additional Tables for the remaining fields as they relate to each other as well. This is called database normalization and is effective in reducing data redundancy, improving maintainability, and helps optimize performance. Your additional Tables (kind of reference Tables such as your first option describes) will store the primary key(s) of the Table(s) they relate to. This is known as a foreign key relationship. Your second design option is called denormalization and is not recommended in most cases (there are some specific cases where some denormalization is helpful).
I don't know your data so I'll use an example for a Car Dealer to convey the idea of normalization. Image a Car Dealer business who sells different brands of Cars across multiple Locations (notice how my objects are already taking shape of the Tables I need). So I'd expect their to be a Locations table, with columns regarding each Location, such as its Address, PhoneNumber and maybe ManagerName, in addition to its primary key column LocationId. I'd also expect a Cars table with columns like Manufacturer, Model, ManufacturedYear, with a primary key column of CarId.
Cool, so far so good. But what if we wanted to know more information about the Managers of each Location and more information about the Manufacturers of each Car. To store that in the database, if we added those fields to the Locations and Cars tables respectively would be bad design because that's denormalized. Information about a Manager object doesn't make a lot of sense in a Locations table, same for information about a Manufacturer in the Car Dealership's Cars table. So we should create two more tables, Managers and Manufacturers.
Managers would now store the ManagerName field instead of the Locations table. (Realistically you'd like store ManagerName in two fields, one for FirstName and one for LastName but this is just a basic example, so bear with me :). Managers would also store things like the Manager's Address and PhoneNunber, and maybe even their YearlySalary, all things that wouldn't make sense in a Locations table. Finally, the primary key would be ManagerId which is what the Locations table would store as a foreign key reference to the Managers table, and this is how you relate the two tables.
Same design as above for the Manufacturers table. It would have columns like ManufacturerName, Address, PhoneNumber, maybe a field called NumberOfCarModels etc, and finally a primary key of ManufacturerId which would be a foreign key in the Cars table.
This is loosely a first level of normalization and hopefully conveys how to structure things. Essentially every main object gets it's own table. You can actually further normalize this (though not always necessary) by having more generic tables for your objects. For example a Contacts table could store all the Addresses and PhoneNumbers of the Locations, Managers, and Manufacturers tables in one place (with a column to denote the ContactType) instead of on each individual table. And the Managers table would likely be an Employees table with a field IsManager to denote which ones are Managers. This next level of normalization reduces redundancy and lightens the other tables even more so (but again it isn't a necessity and just depends on your situation).
Ideally, the performance benefits of normalization come from the fact that when you query a table, you're not getting back 30 extra columns of data you don't need and take longer to load off disk, etc. By designing your tables centralized around your primary objects, and storing only what's truly relevant to those objects in those tables, breaking out the additional fields into their own object tables, you will maximize the performance of querying those tables.


How to show multiple record if has top 1 Hi currently having difficulty to query the result sorry a newbie here. i am trying to find the result of these two tables. i want to get all the records on table 2 with an effective date < 2019-12-05.

Table 1

Key 2          Name

78     Test 1
222    Test 2
824    Test 3
1980   Test 4
3747   Test 5

Please see image for table 2

select top 1 (md2.table2) from table2 md2
where md2.EFFECTIVEDATE < '2019-12-05'
and md2.key2 in (select key2 from table1)
order by EFFECTIVEDATE desc

my query only getting one record i want to get all the record on key 2

Top is only giving you one record because you're asking for the top '1' row.  If you want all, just change it to:

select (md2.table2) from table2 md2
where md2.EFFECTIVEDATE < '2019-12-05'
and md2.key2 in (select key2 from table1)
order by EFFECTIVEDATE desc


Joining two tables and query them I want to select the name and date of birth of all living artists who have value more than 50. My tables are artist and picture and I wrote this SQL statement but it is not giving me the desired result. Please advise.

SELECT name, dateOfBirth
FROM artist
    JOIN exhibitions.picture
        ON exhibitions.picture.name
WHERE exhibitions.picture.value = '50'
    AND artist.dayOfDeath is NULL;

Assuming that value is a numeric data type:

SELECT
    A.name
    , A.dateOfBirth

FROM
    artist A
    JOIN exhibitions.picture P
        ON A.name = P.name

WHERE
    P.Value > 50
    AND A.dayOfDeath is NULL;


Script to merge-replicate two MDF database files? I have a program on a computer and on a tablet. The program uses SQL Server Express to interact with a local database (the same information on both) from a .MDF file. So if I plug the tablet into the PC, I have two .MDF files that I want to synchronize.

I want both users to be able to make changes to the version on their device, then I want to get both .MDF files and run a script that merges them.

The database has a GUID primary key, and a DateTime of when it was last changed, so if there are conflicts, I would just take the most recent version.

How should I do this?

Or alternatively, if I'm going about this the wrong way, could you recommend an alternative way to keep the databases of these two machines synchronized without an internet connection?

Thanks in advance.

Without an internet connection, you would be looking at merge or peer-to-peer replication. Neither of which can be implemented easily (google I seem to remember seeing a workaround somewhere) how you want with Express edition. If you had standard or better edition I would personally go with Peer-To-Peer as it negates the need for an additional column per table and like merge includes conflict resolution. Be advised though that in my opinion replication is a fairly finicky technology and requires intermediate DBA skills to implement and troubleshoot efficiently. Also be advised that once replication is implimented that no transaction can be cleared by a transaction log backup or checkpoint (depending on your recovery model) until it has been distributed to its subscriber. Depending on the duration and number of commands between syncs this will cause your transaction log to grow and potentially fill the disk. Obviously if the avilable disk space becomes full no new transactions can be written / updated in the DB.

With an internet connection I would definitely look at Azure to give you a single database which both devices can write to.

Obviously you can also look to pull and merge the data via powershell, SSIS, C# scripts or get a trial licence of RedGate data compare and kick off via a windows scheduled task (SQL Server agent is not available in express) and do the job which replication would do out of the box.

On a side note depending on your usage you might be able to get hold of developer edition SQL Server licence which is equal to enterprise.

Edit 1: peer to peer is an enterprise only feature in 2008r2 and 2012 at least, unlike merge which is available in standard


Stored procedure returns -4 after catching exception In a stored procedure, I am finding that if it successfully inserts a record, it returns 0. If it fails on a primary key violation (which is caught and ignored), it returns -4. Where is the -4 coming from?

Here is the stored procedure:

ALTER PROCEDURE [dbo].[pSetUserItemLike]
        @UserID int
        ,@ItemID int
AS
BEGIN
    SET NOCOUNT ON;

    -- for exception handling
    DECLARE @ErrorMessage nvarchar(max)
    DECLARE @ErrorSeverity int
    DECLARE @ErrorState int

    BEGIN TRY
        INSERT dbo.UserItemLikes
        (
            UserID
            ,ItemID
        )
        VALUES
        (
            @UserID
            ,@ItemID
        )
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() <> 2627 -- IGNORE PRIMARY KEY VIOLATION
            SELECT
                @ErrorMessage = ERROR_MESSAGE() + ' Line ' + cast(ERROR_LINE() as nvarchar(5))
                , @ErrorSeverity = ERROR_SEVERITY()
                , @ErrorState = ERROR_STATE();
            RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH
END

Note: I am not actually using the return value, I just noticed it as SSMS' "Execute Procedure" automatically captures the return value and puts it in a variable, and couldn't figure out why this was happening.

While I agree that you're raising an error even if you have a PK violation (as the other answer suggests, you need BEGIN/END, because your IF conditional currently assumes that everything indented after it will be part of the IF logic), that doesn't really answer why you're getting -4 specifically.
You don't have an explicit return value in the event that you catch an error. I'll borrow from Erland Sommarskog's answer here, which states:

But if there is no RETURN statement, but an error occurs during execution, the return value is 10 minus the severity level of the error. Division by zero is level 16, thus the return value is -6. Permissions errors are typical level 14, thus the return value is -4.
As you may guess this is not terribly useful, but this: 0 is success, and everything else is an error.

So, raise a PK error, you get:

Msg 2627, Level 14, State 1

According to Erland's formula:
10 - 14 = -4

You don't get the actual level in @ErrorLevel when there's a PK violation because those local variables aren't populated when the error message is 2627 (your IF condition prevents that for that specific scenario, but doesn't prevent the RAISERROR from happening). If you manage to say RAISERROR(NULL,NULL,NULL); then Erland's rule applies; this is because, if an actual error has happened (I fake this in a demo below), SQL Server will replace that middle param with the actual ERROR_LEVEL() from the exception. I can show that this is true for other error levels other than 14; for example, the following will print out Hi/-6 after I fail to assign the variable for a divide by zero error, which is severity level 16:
CREATE PROCEDURE dbo.Test
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @ErrorLevel INT;

  BEGIN TRY
    SELECT 1/0; -- level 16!
  END TRY
  BEGIN CATCH
    IF ERROR_NUMBER() <> 8134 -- divide by 0
      SET @ErrorLevel = ERROR_SEVERITY();
    RAISERROR('Hi', @ErrorLevel, 1);
  END CATCH
END
GO

DECLARE @rc INT;
EXEC @rc = dbo.Test;
PRINT @rc;

(In fact, inside a CATCH block, I couldn't find a way to override the actual exception's true severity level, even by hard-coding @ErrorLevel to sensible and non-sensible values.)
Also from John Saunders' answer on the same question:

My "research" is due, with thanks, to SQL Server MVP Tibor Karaszi. His source is Books Online for SQL Server 6.5. Under ""Control-Of-Flow Language", RETURN", he found
"SQL Server reserves 0 to indicate a successful return and reserves negative values from - 1 through - 99 to indicate different reasons for failure. If no user-defined return value is provided, the SQL Server value is used. User-defined return status values should not conflict with those reserved by SQL Server. The values 0 through -14 are currently in use.

(Current versions of this same topic no longer have this language, but if you read the late Ken Henderson's book, The Guru's Guide to SQL Server Stored Procedures, XML, and HTML, on page 39 he indicates that -1 through -14 indicate different levels of failure (and to go read the then-current version of Books Online to see individual descriptions, a lot of good that does now), and -15 through -99 are reserved for future use.)
In the end, Erland's right; whether you get -4 or -6 or -23 doesn't really matter. If it's not 0 (and you didn't set an explicit non-zero return value to indicate success), you had an error. It sounds like you just want to ignore the PK violation error, but if you want control over what value gets returned in the event of other non-PK errors, you'll have to add your own explicit RETURN(x); command to that same IF block.
And depending on how many PK violations you expect, it might be worth considering checking for the violation manually instead of just blindly throwing everything and letting SQL Server deal with it. There can be substantial overhead with raising exceptions:

Checking for potential constraint violations before entering SQL Server TRY and CATCH logic
Performance impact of different error handling techniques

Further demonstration of where -4 (and other values) come from. Create this procedure:
CREATE PROCEDURE dbo.DemoErrors
  @ErrorLevel INT
AS
BEGIN
  RAISERROR(N'foo', @ErrorLevel, 1);
END
GO

Now if you call it with these values for @ErrorLevel (level < 11 will end up with return value of 0, and > 19 require log etc., so we'll go 11 -> 18):
SET NOCOUNT ON;
DECLARE @rc INT, @i INT = 11;
WHILE @i <= 18
BEGIN
  EXEC @rc = dbo.DemoErrors @ErrorLevel = @i;
  PRINT @rc;
  SET @i += 1;
END

Output shows that the return value is always 10 - @ErrorLevel:

Msg 50000, Level 11, State 1, Procedure DemoErrors, Line 25
foo
-1
Msg 50000, Level 12, State 1, Procedure DemoErrors, Line 25
foo
-2
Msg 50000, Level 13, State 1, Procedure DemoErrors, Line 25
foo
-3
Msg 50000, Level 14, State 1, Procedure DemoErrors, Line 25
foo
-4
Msg 50000, Level 15, State 1, Procedure DemoErrors, Line 25
foo
-5
Msg 50000, Level 16, State 1, Procedure DemoErrors, Line 25
foo
-6
Msg 50000, Level 17, State 1, Procedure DemoErrors, Line 25
foo
-7
Msg 50000, Level 18, State 1, Procedure DemoErrors, Line 25
foo
-8

(If you run as a sysadmin and add WITH LOG to the RAISERROR call, you'll see 19 produces -9, and so on, but up above that you'll start having connections severed etc. because those are serious errors.)


Can independent applications cause roll back to each other's transactions? I have a database built on SQL Server

Application-1 [Legacy App]:

Written in C#, and runs transactions on the above database.

Application-2 [New application being developed]:

Written in Python, and runs transactions on the above database.

Considerations:

These apps access the same set of tables and perform similar actions.
We are trying to transition from older app to the newer one over time.
I am finding several deadlocks and rollbacks happening in the system.

Questions

I want to understand if there is a possibility that transaction being run via one application can affect the ones from an independent other application.
Are these scenarios handled by the DBMS itself internally or have to be handled at the application level ?

I want to understand if there is a possibility that transaction being
  run via one application can affect the ones from an independent other
  application.

Directly, no. Each transaction is by definition is a "single unit of work" and is local to itself. That is, my transaction cannot become part of your transaction. I can't explicitly tell yours to roll back or commit.

However, if two transactions are working on the same data, they can influence each other. If a deadlock occurs between two transactions, one is a victim (killed) and the other succeeds. In this scenario, my transaction causes the database engine to issue a rollback on your transaction, but nothing in my transaction is directly impacting yours. While deadlocks can never be 100% avoided, there are ways to mitigate the risk and impact of them.

As El.Ham mentioned, separate transactions can also cause blocking, leading to a suspended query. This could lead to application slowness or even timeouts depending on how things are configured for your connections.

  Are these scenarios handled by the DBMS itself internally or have to
  be handled at the application level ?

It depends. If your concern is deadlocks (which I think it is), then you probably want the applications to have retry logic so that a deadlocked transaction will be re-tried automatically. This can be done with T-SQL or at the application level.


Disable multiple logins in one step Is there a way to disable multiple sql logins based on a set of users in a table? For example:

ALTER LOGIN (Select userid from tbl_user) DISABLE;

No, you can't feed a query to an ALTER LOGIN command. But you can build the command pretty easily using dynamic SQL:

DECLARE @sql nvarchar(max) = N'',
        @cmd nvarchar(550) = N'ALTER LOGIN $$$ DISABLE;'
                           + CHAR(13) + CHAR(10);

SELECT @sql += REPLACE(@cmd, N'$$$', QUOTENAME(userid))
  FROM dbo.tbl_user;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

When you're happy with the output, uncomment the EXEC.

Note that if you have a lot of users in that table, you won't be able to see the whole command using PRINT, since there are limitations in output length. See this tip for workarounds.

Also, there is no error handling here, so you should probably actually either add TRY/CATCH or join to sys.server_principals to make sure you are disabling logins that actually exist:

SELECT @sql += REPLACE(@cmd, N'$$$', QUOTENAME(p.name))
  FROM dbo.tbl_user AS u
  INNER JOIN master.sys.server_principals AS p
  ON p.name = u.userid;


How do I list all columns for a specified table that begin with a specific string How to select all the columns whose names begins with "img" from a PostgreSQL table?

I wrote that, but doesn't work

select 'img%' from mytable

You can use INFORMATION_SCHEMA views for this purpose:

create table tbl
(
    id serial,
    img1 text,
    img2 text,
    foo int,
    bar int,
    img14 text
);

select table_catalog, table_name, column_name
from   information_schema.columns
where  table_catalog = 'postgres'
and    table_name = 'tbl'
and    column_name like 'img%';

table_catalog | table_name | column_name
:------------ | :--------- | :----------
postgres      | tbl        | img1
postgres      | tbl        | img2
postgres      | tbl        | img14

db<>fiddle here


data translating issues I have these two table here in ssms:

one is called client table, which have a column called city, and it is really city name, like new york, etc.
Another table is called city table, it has city's ID and city name. such as CityID:1 City: New york.

So in my Client table, I know how to create another column called CityID, but how can I populate the CityID column in the client table, entering them by searching the CityID in the City table?

You have to use the following statement:

UPDATE A
SET A.CityID = B.CityID
FROM dbo.client AS A
INNER JOIN dbo.city AS B
       ON A.city = B.City;

If you don't want a [case/blank spaces] sensitive update (' New York   ' = 'NEW YORK') use this statement:

UPDATE A
SET A.CityID = B.CityID
FROM dbo.client AS A
INNER JOIN dbo.city AS B
       ON rtrim(ltrim(upper(A.city))) = rtrim(ltrim(upper(B.City)));


Get the max value from a slice of a JSON Array? I would like to get the max value within a slice of a Json object (typicaly [1,2,3,5,6,7,9,10]) which is contained in a field named Data of the table raw.

The limits Start & End of the slice are contained in an other Json object named Features contained in a table named features

Here is the input:

CREATE TABLE raw (
    id     int PRIMARY KEY
      GENERATED BY DEFAULT AS IDENTITY

    data   json
);

INSERT INTO raw (data) VALUES
  ('[1,2,3,5,6,7,9,10]');

CREATE TABLE features (
    id         int,
    features   json
);

INSERT INTO features (id, features) VALUES
  (1, '{"Start" : 1, "End": 5}');

The output I would like is 7, i.e. the max value of the slice [2,3,5,6,7]

Here is what I came up with looking at other posts, but it does not work...

SELECT
    R."ID",
    F."Features"->>'Start' AS Start,
    F."Features"->>'End' AS End,
    sort_desc((array(select json_array_elements(R."Data")))[F."Features"->>'Start':F."Features"->>'End'])[1] as maxData
FROM
    raw AS R
INNER JOIN
    features AS F ON R."ID" = F."ID"

The approximate error message I get is concerning sort_desc :

  No function corresponding to this name or this type of arguments. You
  should convert the type of data

You can unnest json array:

Postgres WITH ORDINALITY:

  When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().

Have a look at this answer of Erwin Brandstetter:

PostgreSQL unnest() with element number

  SELECT
     r."ID",
     MAX(t.elem::int) MaxElem
  FROM
      raw r
  JOIN
      features f
      ON f."ID" = r."ID"
  JOIN LATERAL
      json_array_elements_text(r."Data")
      WITH ORDINALITY AS t(elem, n) ON TRUE
  WHERE
      n >= (f."Features"->>'Start')::int + 1
      AND
      n <= (f."Features"->>'End')::int + 1
  GROUP BY
      r."ID";

ID | maxelem
-: | ------:
 1 |       7

db<>fiddle here

Or if you prefer to use intarray module:

SELECT
    r."ID",
    (sort_desc(((ARRAY(SELECT json_array_elements_text(r."Data")))::int[])[(f."Features"->>'Start')::int + 1:(f."Features"->>'End')::int + 1]))[1]
FROM
    raw r
JOIN
    features f
    ON f."ID" = r."ID";

rextester here