Merge Fields into one Row with a Query I am working with Microsoft Office Professional Plus 2010.  What I am trying to figure out is how to merge all characters for each DOMAIN field into one row, for example:

Q01                         DOMAIN      Q32
-----------------------------------------------
http://redbull.example.com  example.com F
http://www.example.com      example.com B

What I would like to accomplish through a query is to take the DOMAIN field and create a view which merges the "Q32" into one row, for example:

DOMAIN      Q32
----------------
example.com B F

Is this possible through a design query or would I have to write a SQL statement?  I tried the following SQL statement to create a UNION with two different tables, though I still didn't get the merge displayed correctly.

SELECT DOMAIN, Q32
FROM tblMERGE
UNION
SELECT DOMAIN, Q32
FROM tblASSET;

If you have not more than 2 values for domain, this will help:

SELECT [DOMAIN], IIF(First([Q32])<>Last([Q32]), First([Q32])+' '+Last([Q32]),First(Q32)) AS _Q32
FROM [table]
GROUP BY [DOMAIN];


clustered index - data warehouse My source data comes from multiple OLTP databases where the primary key is an identitiy column. We anticipate adding data centers and/or splitting the databases by product. Currently i have one system incrementing from -9223372036848909236, 1 and the other is incrementing from 1134873337, 1

I pull the data into my data warehouse from each of my sources and the data is partitioned on the UTCDate which is also a source value.

Filter criteria will always include the UTCDate. May or maynot have ID as criteria

Question:
will i have less fragmentation on my clustered index UTCDate, ID or  ID, UTCDate

The Short Answer:
I misread your question a little bit. The below discussion still applies and explains my answer, but I would suggest UTCDate, ID for your index. Especially because of your partitions and the fact that UTCDate is included in every query. The question of fragmentation is a good one, and I imagine you'll see some in either of your two choices, but the performance benefit would be best with this way, I feel.

The Previous Discussion:

This is one you'll likely get a few answers on and I've asked a couple questions in the comments. That said a few quick thoughts to get started (ignoring the multiple system question and the way the IDs are being used):

1.) Presuming you are talking about SQL Server partitioning, a partition aligned clustered index is generally best. So in my mind that is a good vote for UTCDate.

2.) You indicate that filters will always (is that really always? or figuratively always?) have UTCDate. In my mind that is another good vote for UTCDate.

3.) Because you'll be having IDs coming in at both a high range and a low range, you will experience some level of index fragmentation on ID. Assuming UTCDate is always increasing and you aren't entering data from dates all over the place, you may actually experience less fragmentation going with UTCDate.

You also didn't indicate if this is a fact table or dimension. Assuming it is a fact table, that is another good argument for date in my book. If you are always querying filtering on a date or date range, then the clustered index being on that range scan value should help your queries. The fact that it would be partition aligned would also likely help.

If this is a dimension and you will sometimes join on ID to pull data out of it, then there could be a stronger argument for going with ID, though I'd argue that the unaligned nature of that index to your partition could cause you more grief in the future performance wise.

I would not bother adding ID to the index. Unless you include it enough as a filtering predicate and could actually see an improvement in performance of most queries. Yes, the date column alone wouldn't be unique, but if this table is a BIGINT, the 4 byte uniqueifier SQL Server adds to the clustered index to make it unique would actually cost less in terms of storage space than that ID column.

These are just my thoughts and they are quick thoughts. This is one of those things you should really test, try a couple ways and see how it works out. Though partitioning sometimes forces your hand a bit. So I would probably end up with the date column only as my first choice and date plus ID as my second choice. Others will likely have alternative ideas on that one, too. And more information edited back into your question will help.


Missing Utility Control on View menu I am trying to open Utility Control from View Menu, but can't find it. I am using SQL Server 2016 (SP1), Developer Edition and SSMS version 17.1. Any idea?

It seems like its not added to SSMS 17. There is a connect report regarding this:

Link to the Connect Report


Do SQL service packs effect SQL Compatibility mode? We will soon be upgrading from a SQL 2008 R2 instance to a SQL 2016 instance.

One of our databases has an application which our supplier says is compatible up to SQL 2012 SP1, but not SQL 2012 SP2, 2014 or 2016.

Once we upgrade to SQL 2016 and change the compatibility level of the database to 110 (2012), would the fact that the application is not compatible with SQL 2012 SP2 have any effect?

Most likely the application will work, however, generally when a vendor has this statement, they have not tested their application. They have not certified the application will still work once the SP or CU or version has been applied. Thusly, if you go to an "unsupported" version, they will not support you on any issues.

I would not rely on a vendor's website documentation as the end all statement for supportability. Sometimes, they have not updated their documentation yet. Give them a call to determine what the true support options are, and then get that in writing.


Write to table with transaction without locking Is it possible to write to a database table using a transaction without locking it?

I want to run an update query on single field in a single record, but just in case there is an error in my application I want to be able to roll it back. The application could take a second or two to complete the entire process, so I'd like to prevent it from locking the table, as it's a heavily read table. From the application's perspective the field is insignificant, so I don't care if there are dirty reads on it.

(I'm aware of NOLOCK and READPAST for SELECT statments, but I'm not looking for that as a solution.)

Any help is appreciated.

Is it possible to write to a database table using a transaction
  without locking it?

Depends on what you mean by "it"?  locks can be at different resource level meaning: row, page, table, non clustered index pages etc.

If you mean all of the above than the answer is no. At the same time you can avoid locks on some of the resources depending on the scope of your transaction and how many records you are updating.

I suggest you read this article :

Why do we need UPDATE Locks in SQL Server? by Klaus Aschenbrenner

Now let's look at an example using adventureworks database. To simulate what I am doing you can download this database from here.

 USE [AdventureWorks]
 GO

--Making sure we are only updating one row
SELECT COUNT(0) FROM [Production].[Product]
WHERE ProductId=318

BEGIN TRAN
UPDATE [Production].[Product]
    SET [Name]='ML Crankarm_modified'
    WHERE ProductId=318
--ROLLBACK TRAN

Since we are using BEGIN TRAN (explicit transaction) we can check what locks this session is holding.

SELECT
  resource_type,
    resource_database_id,
    resource_associated_entity_id,
    request_mode,
    request_status,
    request_session_id
FROM sys.dm_tran_locks
WHERE request_session_id=59

Looking at these locks and lock compatibility chart you should be able to figure out what select statements you can run and which one will get blocked. Keep in mind it will also be dictated by your transaction isolation level.

For example you have Intent Exclusive lock on object id 482100758 which is production.product table itself and this lock is not compatible with Shared lock. Therefore you cannot select all records from this table.  But if you want to select one or more records that does not have locks incompatible with Shared lock you should be able to select data.


What relationship type to use for this messaging system? I am trying to make a small messaging system. It should have these properties:

One user can send a message to one or many users
Messages can be sent only to registered users (i.e. to an existing 'idUser' record)
There should be tracking of sent messages (who sent the message and to whom)

Here is how the current database design looks like:

Table Private Message stores only the content of the message.

Table User Private Message is used to keep track of sent messages. It has columns "idUser from" and "idUser to" which indicate the user that sent the message and the user (can be more than one) that received the message.

I am having difficulties with choosing the right type of relationship (if the whole concept is correct in the first place) for the tables "User" and "User Private Message".
I assume that there should be two separate relationships for the tables 'User' and 'User Private Message', like this:

One-to-one relationship, that connects User.idUser and User Private Message.idUser from
One-to-many relationship, that connects User.idUser and User Private Message.idUser to

Could you please advise me on what relationship to use here. If you have any suggestions on how to approach this type of problem easier, I would appreciate that even more.

Thank you all for your time.

Assuming you're not trying to keep track of threads of messages, I.E. you send a message to N people, and they can't reply:

1) user needs no references to other tables
2) a message should have an ID (obviously) and a reference to the user who sent it originally
3) you need a table that connects a single message and its recipients (users) that looks like this: message_recipients{user_id, message_id} where the primary key is the combination of the two fields. This ensures that one message can have many recipients, and that users can be the recipients of many different messages.

  One user can send a message to one or many users

This is accomplished via the message_recipients table

  Messages can be sent only to registered users (i.e. to an existing 'idUser' record)

You'll need to handle this in your application logic as well, but if you add (as you should) a foreign key and (depending on your DBMS) a NOT NULL restriction on each of the columns in message_recipients table, it will be possible to create a record in message_recipients without the recipient existing.

  There should be tracking of sent messages (who sent the message and to whom)

This data model supports that. Example:

Find the name of all recipients of messages sent by user Mike Johnson

(edited to give better aliases to the two different uses of the user table)

SELECT recipient.firstname, recipient.lastname
FROM user recipient
JOIN message_recipient mr ON recipient.id = mr.user_id
JOIN message m ON m.id = mr.message_id
JOIN user sender ON m.user_id = sender.id
WHERE sender.firstname = 'Mike' and sender.lastname = 'Johnson'


DB2 Indexes are taking very large size on disk space I have huge table in DB with estimated size 7 GB  (I saw that using DB2CC and did the action Estimate Size for that Table).

In estimate size dialog I noticed that two of my indexes for that table have very huge size - one is 4 GB and other is 2 GB.

How is possible that index can take so much disk space and is this regular behavior?

Thank you

Indexes essentially duplicate data on disk. The idea though is the tradeoff in performance. Extra disk used, but faster response looking up data.

But since indexes do cost extra disk, one should weigh carefully whether or not the index is actually needed. If not needed, it will cost that extra disk and may even cost you in terms of performance (especially on inserts, updates, and deletes).


Passing multiple value for a parameter in my sql I have written the query below:

SET @City = 'London,Lyon,Kln';
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@City, ',',@r:=@r+1),',',-1) City
FROM (SELECT @r:=0) x,
(SELECT 'x' xx
    UNION
    SELECT 'v' xx
    UNION
    SELECT 'z' xx) z;

Which returns Cities in table format & now i want to pass it as a parameter to a query like :

SET @City = 'London,Lyon,Kln';
SELECT *
FROM Invoices
WHERE City IN (
    SELECT SUBSTRING_INDEX(
        SUBSTRING_INDEX( @City, ',',@r:=@r+1),',',-1
    ) City
    FROM (SELECT @r:=0) x,
    (SELECT 'x' xx
        UNION
        SELECT 'v' xx
        UNION
        SELECT 'z' xx) z
)

But when I execute this query it returns the records for 'Kln' city only, ie. for last city mentioned in @City variable. So please tell me how to get the records for all cities of @City variable.

If I understand your intent, I might write the query something like this:

SET @City = '"London","Lyon","Kln"';
SET @sql = concat('SELECT * FROM Invoices WHERE City IN (',@City,')');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


Should I shrink my database? I have a 1.2Tb SQL Server 2008 R2 database which is more than 50% empty, because I migrated a lot of functionality to PostgreSql. The database is still in use, and still somewhat growing, but I do not think it will grow to use all 1.2Tb in the next 3-5 years.

At the current rate of growth, we are not going to use up 1.2Tb in more than 14 years. More to the point, we keep migrating functionality to PostgreSql whenever we need to make serious changes. In a very dynamic agile environment, this means we frequently migrate data away out of this SQL Server database. So, this database is likely to grow even slower in the future: it is being phased out, eventually.

As such, we'd rather use this unused empty space for other databases and such - moist likely it is not going to be needed for growing tables/indexes ever.

I recall that the rule of thumb used to be "never shrink databases". Does it apply in this case? Currently the server is running 2008 R2 EE, but we plan to upgrade to 2012 very soon. I think because this shrink functionality exists, there should be valid use cases where it makes sense to shrink. Otherwise why does it exist at all?

We can afford a few hours of downtime during a weekend.

Edit: the problem we are solving is as I said above: "we'd rather use this unused empty space for other databases". Also we'd rather prefer a solution that does not require investing too much time in learning the technology we are migrating out of.

I recall that the rule of thumb used to be "never shrink databases". Does it apply in this case?

Yes. Dont touch that shrink button - Its a general rule and a very very sound advice from many renowned SQL Server Gurus - Paul Randal, Brent Ozar, Mike Walsh, Gail Shaw and many more.

  The database is still in use, and still somewhat growing, but I do not think it will grow to use all 1.2Tb in the next 3-5 years.

You have to be 1000% sure about your assumption that its growing but wont grow to this size in your proposed 3-5 years (its a 2 year difference as well :-)).

still somewhat growing -->You really need to find out at what pace it is growing. Are you doing database growth tracking by collecting the data growth over your complete business cycle ?

Now a days, data is what keeps the companies in business and databases are meant to grow.

Think about it - what are you going to do with the disk space that you gain from shrinking the database ?

Concentrate on more important areas of optimization as @AaronBertrand suggests.

Even if you decide to shrink , I would suggest you to

Do a reorg/rebuild and update stats after the nasty shrink operation
Take a full backup to be safe with all the indexes intact.
Adjust your auto-growth settings to not allow the auto-growth events kick off more frequently.

Edit

After OP's comments ---

Then go for it ... since you are or will be moving away data from SQL Server to PostgreSql. But do the post steps once as I mentioned in my answer - once you do the shrink operation. Also, use DBCC SHRINKFILE as you have more control of what you are shrinking as opposed to DBCC SHRINKDATABASE.


Two-sided UNIQUE INDEX for two columns Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too (col2, col1)?

For example

PRIMARY KEY (col1, col2)

OR

UNIQUE INDEX (col1, col2)

If we have col1=33 && col2=54; how we can avoid INSERT of col1=54 && col2=33?

In a standard SQL dbms, you'd enforce that kind of requirement by ordering the id numbers, and using a CHECK constraint.  Application code, a stored procedure, or a user-defined function is responsible for putting the id numbers in the right order.

create table friends (
  user_a integer not null,  -- references users, not shown
  user_b integer not null,  -- references users, not shown
  primary key (user_a, user_b),
  check (user_a < user_b)
);

But MySQL doesn't enforce CHECK constraints. Using MySQL, I'd still use a stored procedure (not application code) to put the id numbers in the right order. But I'd also run a query or a report every now and then to make sure that all the values for user_a were less than the values for user_b.

select *
from friends
where user_a >= user_b;

I probably wouldn't include an UPDATE statement to fix those values at first. I'd want to track down what process, application, or user was going around my stored procedure to insert data directly into the table. (You can revoke direct access to the table, and require all access to go through stored procedures. But this isn't practical in some legacy systems; too much code to rewrite. And there are alternatives.)

Unfortunately, some requirements have to be implemented as administrative procedures, like running reports.


Can autoshrink permanently damage a database? I've been given the task of finding out why our server is slow. I ran a stored procedure I downloaded called sp_Blitz. It let me know that our database had auto-shrink enabled, and to disable it. So, I did that. I don't know how long it's been enabled, but I'd guess at least 3-4 years. When I checked the fragmentation percent of the indexes, a lot of them were 60% - 99%. I then ran a script to rebuild the indexes and update the statistics, but the fragmentation percent didn't really change for these indexes.

Is there something else I need to do to repair what auto-shrink has done?

Any advice for me?

No auto shrink can't permanently damage a database. It may lead to highly fragmented tables and indexes which may hurt performance. The fragmentation could be difficult to remove completely, but minor fragmentation itself shouldn't be a concern. You need to assess whether this is really an issue for you worth spending time and effort to fix.


Mount ASM Disks To New Host I have oracle 11.2.0.4 installed on Red Hat Linux 6.

I have an RMAN full backup from database that size is  20TB . This backup is created on ASM disks with different diskgroup from DATA.

I want to restore this backup to a new host .
For some reasons , I should plug these disks to a new host and then restore it, I have a question :

How can I add these ASM disks to new host ?

I have drop this diskgroup from target host and then with                        " oracleasm deletedisk " , unplugged them from OS and then unplugged these disks from target host and the plugging this disk to new hosts that have oracle 11.2.0.4 and Red Hat Linux 6 .

When I run /etc/init.d/oracleasm scandisks , scandisk is ok and then when execute oracleasm listdisks , these disks are not on listed disks .

Can any one help me ?

Thanks a lot

I have drop this diskgroup from target host and then with  " oracleasm deletedisk "

This was a mistake. You removed the metadata describing the content. You should have just simply detached the disks, then attach to the new host, leaving the disks intact.


How to make a function from a concrete SELECT statement in Oracle SQL? How to replace a concrete value with a variable in Oracle SQL? I have

 select 5, min(id) from my_table where id > 5 --AND ... complex condition
 UNION ALL
 select 6, min(id) from my_table where id > 6  --AND ... complex condition
  UNION ALL
  ....

 | 5 | 6 |
 | 6 | 8 |
 ...

How to wrap it in a function executing pseudocode below?

for ( $i in ( select id from my_table)){
    UNION ALL
    select $i, min(id) from my_table where id > $i  --AND ...
 }

To make it clear, I am looking for a general method to turn a select with hard wired values into a function that accepts variables. Note the --AND part.
This is NOT a question about using lead and over but about replacing values with variables.

I am trying to replace START WITH ID=10 with a variable in this query.

WITH t AS (SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
   res AS (SELECT ID,
                  in_node,
                  out_node,
                  weight,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
                  CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                       ELSE 'out'
                  END direction_from_orig_node,
                  LEAST(in_node, out_node) node1,
                  GREATEST(in_node, out_node) node2,
                  row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   t
           START WITH ID = 10
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                              OR PRIOR in_node IN (in_node, out_node))
                              AND LEVEL <= 2)
SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
FROM   res
WHERE  rn = 1
AND    ID != orig_id
GROUP BY orig_id;

Apparently I can use this template, but do i have to use a pointer for this?

--creates a function
CREATE OR REPLACE Function dummy_function   ( name_in IN number )
   RETURN number
IS
   cnumber number;
   cursor c1 is
   SELECT weight FROM dummy_links WHERE ID = name_in;
BEGIN
   open c1;
   fetch c1 into cnumber;

   if c1%notfound then
      cnumber := 9999;
   end if;
   close c1;
RETURN cnumber+1; --to have some diversity we add 1

EXCEPTION
WHEN OTHERS THEN
   raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

--calling a function
SELECT id, weight, dummy_function(id) AS weight_plus_one FROM dummy_links;

If I undferstand correctly, all you need is a bind variable.

SQL> variable sw number
SQL> exec :sw := 10;

PL/SQL procedure successfully completed.

WITH t AS (SELECT 1 ID, 'A' in_node, 'B' out_node, 17 weight FROM dual UNION ALL
           SELECT 2 ID, 'B' in_node, 'A' out_node, 4 weight FROM dual UNION ALL
           SELECT 3 ID, 'C' in_node, 'A' out_node, 5 weight FROM dual UNION ALL
           SELECT 4 ID, 'A' in_node, 'D' out_node, 6 weight FROM dual UNION ALL
           SELECT 5 ID, 'C' in_node, 'G' out_node, 33 weight FROM dual UNION ALL
           SELECT 6 ID, 'X' in_node, 'Z' out_node, 12 weight FROM dual UNION ALL
           SELECT 7 ID, 'Z' in_node, 'Y' out_node, 15 weight FROM dual UNION ALL
           SELECT 8 ID, 'X' in_node, 'Y' out_node, 42 weight FROM dual UNION ALL
           SELECT 9 ID, 'K' in_node, 'M' out_node, 66 weight FROM dual UNION ALL
           SELECT 10 ID, 'A' in_node, 'Z' out_node, 20 weight FROM dual),
   res AS (SELECT ID,
                  in_node,
                  out_node,
                  weight,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () orig_in_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN out_node END) OVER () orig_out_node,
                  MAX(CASE WHEN ID = connect_by_root(ID) THEN ID END) OVER () orig_id,
                  CASE WHEN MAX(CASE WHEN ID = connect_by_root(ID) THEN in_node END) OVER () IN (in_node, out_node) THEN 'in'
                       ELSE 'out'
                  END direction_from_orig_node,
                  LEAST(in_node, out_node) node1,
                  GREATEST(in_node, out_node) node2,
                  row_number() OVER (PARTITION BY LEAST(in_node, out_node), GREATEST(in_node, out_node) ORDER BY weight) rn
           FROM   t
           START WITH ID = :sw
           CONNECT BY NOCYCLE (PRIOR out_node IN (in_node, out_node)
                              OR PRIOR in_node IN (in_node, out_node))
                              AND LEVEL <= 2)
SELECT orig_id,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'in' THEN node1||'~'||node2 END) in_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'in' THEN weight END), 0) in_sum,
       COUNT(DISTINCT CASE WHEN direction_from_orig_node = 'out' THEN node1||'~'||node2 END) out_count,
       nvl(SUM(CASE WHEN direction_from_orig_node = 'out' THEN weight END), 0) out_sum
FROM   res
WHERE  rn = 1
AND    ID != orig_id
GROUP BY orig_id;

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
        10          3         15          2         27

Then change the value of the variable to whatever you need:

SQL> exec :sw := 9;

PL/SQL procedure successfully completed.

SQL> /

no rows selected

SQL> exec :sw := 8;

PL/SQL procedure successfully completed.

SQL> /

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         8          1         12          1         15

SQL> exec :sw := 3;

PL/SQL procedure successfully completed.

SQL> /

   ORIG_ID   IN_COUNT     IN_SUM  OUT_COUNT    OUT_SUM
---------- ---------- ---------- ---------- ----------
         3          1         33          3         30


How to deal with id gap when your query is like from id > XX to id < XXX I have a large table of around 50M data. I am trying to get data with something like

select id,name,address,city
from table_name
where id > 50000000 and id < 50000050
order by id;

But, this strategy could work only when there is no deleted data. However, In my case, I am soft-deleting data so whenever a row is deleted, its column deleted_at will be filled with current timestamp i.e when deleted deleted_at is not null.

Now, how can I tackle this situation? Please, suggest me what would be my best bet for this.

I am ready to:

create the index with an extra column(if possible) and reorder on every deletion
create a temp table with extra table and reorder it on every deletion
Or same trick with view
or some other way.

I aaume you want to extend the range of your search so it returns 50 rows even if there are gaps. Then you can use this:

SELECT TOP (50)
    id, name, address city
FROM table_name
WHERE id > 5000000
  AND deleted_at IS NULL
ORDER BY id ;

A partial index on (id) WHERE (deleted_at IS NULL) would improve efficiency.


tsql to know when a database was last shrunk In SQL 2000, 2005 and 2008, how can  I know when databases were last shrunk on a MS SQL server?

I want to do this using TSQL.

Regards

Manjot

Because for shrinking of database one uses DBCC SHRINKDATABASE or DBCC SHRINKFILE, you can try to solve your problem with server audits from the group DBCC_GROUP (see http://technet.microsoft.com/en-us/library/cc280663.aspx and http://msdn.microsoft.com/en-us/library/cc280386.aspx)


How can I rewrite the following removing the date_format function? I have a table with a column when that is of type datetime.
I am doing the following filter which can not use and index:
AND DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month )
How can I re-write this so that I can get rid of the DATE_FORMAT that forbids index usage?

The comment from  @ChristianAmmer is the key to answering your question. Why ???
First look the the WHERE clause
DATE_FORMAT(t1.when, '%Y-%m-%d') <= LAST_DAY(now() - interval 1 month )

You are forcing mysqld to evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') for every row and comparing each result to LAST_DAY(now() - interval 1 month ). That quickly adds wear-and-tear on the query. I have discussed this before (See my answer to  What happens when we take a timediff between timestamp and datetime).
When comparing to LAST_DAY(now() - interval 1 month ), there is no need to do the DATE_FORMAT function against t1.when. Look at what actually happens when you do:

You evaluate DATE_FORMAT(t1.when, '%Y-%m-%d') and the result is a string
The string has to be converted back to a DATE
That converted DATE is then compared to LAST_DAY(now() - interval 1 month )

These steps must be done  for every row.
To remove those steps, just switch the WHERE clause to have
AND t1.when <= LAST_DAY(now() - interval 1 month)

as @ChristianAmmer specified.
That way it compares DATE to DATE without any string manipulate or function call.
To answer your last comment

@ChristianAmmer:I was not sure if it is the same

The comparison of the result is the same, but the way the comparison is executed is not.
Doing it with AND t1.when <= LAST_DAY(now() - interval 1 month) will always be faster. Leaving the t1.when on the left side of the comparison (left side of the equal sign) obligates the MySQL Query Optimizer to try using the index. Using a function call on the left side of the comparison eliminates index usage.
CONCLUSION : Go ahead an d use AND t1.when <= LAST_DAY(now() - interval 1 month)
UPDATE 2014-04-15 10:46 EDT
To further prove function calls on the left side confuses the MySQL Query Optimizer, I have  other posts going back to 2011 that discuss how searching a FULLTEXT index cause the MySQL Query Optimizer to stop using other indexes (since you have to use the MATCH function on the left side).

May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
May 23, 2011 : Optimizing mysql fulltext search (StackOverflow)


Why does my PostgreSQL query cost so much when I tack on an additional group-by? I need to extract two fields from a table. Here's the query with just one of the fields:

set schema 'data';
    explain (analyze, verbose)
     select
        count(example9),
        example9,
        example5
    from table
    group by  table.example9, table.example5
    order by count(example9) desc

Here's the query plan:

"Sort  (cost=151128.71..151149.41 rows=8280 width=79) (actual time=1602.128..1602.164 rows=1852 loops=1)"
"  Output: (count(example9)), example9, example5"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 316kB"
"  ->  HashAggregate  (cost=150507.08..150589.88 rows=8280 width=79) (actual time=1601.299..1601.705 rows=1852 loops=1)"
"        Output: count(example9), example9, example5"
"        Group Key: table.example9, table.example5"
"        ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=79) (actual time=0.013..633.542 rows=2807146 loops=1)"
"              Output: example1, example2, example3, example4, example5, example6, example7, example8, example9, example10, example11, example12, example13, example14"
"Planning time: 0.108 ms"
"Execution time: 1602.380 ms"

Here's the query with the second field I want:

set schema 'data';
    explain (analyze, verbose)
     select
        count(example9),
        example9,
        example6
    from table
    group by  table.example9, table.example6
    order by count(example9) desc

and the corresponding query plan:

"Sort  (cost=152197.99..152249.74 rows=20700 width=85) (actual time=1618.241..1618.265 rows=1794 loops=1)"
"  Output: (count(example9)), example9, example6"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 313kB"
"  ->  HashAggregate  (cost=150507.08..150714.08 rows=20700 width=85) (actual time=1617.381..1617.799 rows=1794 loops=1)"
"        Output: count(example9), example9, example6"
"        Group Key: table.example9, table.example6"
"        ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=85) (actual time=0.005..635.558 rows=2807146 loops=1)"
"              Output: example1, example2, example3, example4, example5, example6, example7, example8, example9, example10, example11, example12, example13, example14"
"Planning time: 0.067 ms"
"Execution time: 1618.559 ms"

Now (this is the problem), here are the two fields combined together in the same query:

set schema 'data';
    explain (analyze, verbose)
     select
        count(example9),
        example9,
        example6,
        example5
    from table
    group by  table.example9, table.example6, table.example5
    order by count(example9) desc

and here is the query plan for it:

"Sort  (cost=794824.44..795341.94 rows=207000 width=92) (actual time=23189.695..23189.725 rows=1872 loops=1)"
"  Output: (count(example9)), example9, example6, example5"
"  Sort Key: (count(table.example9)) DESC"
"  Sort Method: quicksort  Memory: 350kB"
"  ->  GroupAggregate  (cost=728162.97..765931.60 rows=207000 width=92) (actual time=19125.027..23189.203 rows=1872 loops=1)"
"        Output: count(example9), example9, example6, example5"
"        Group Key: table.example9, table.example6, table.example5"
"        ->  Sort  (cost=728162.97..735302.70 rows=2855890 width=92) (actual time=19110.027..22635.209 rows=2807146 loops=1)"
"              Output: example9, example6, example5"
"              Sort Key: table.example9, table.example6, table.example5"
"              Sort Method: external merge  Disk: 286688kB"
"              ->  Seq Scan on data.table  (cost=0.00..129087.90 rows=2855890 width=92) (actual time=0.009..962.086 rows=2807146 loops=1)"
"                    Output: example9, example6, example5"
"Planning time: 0.079 ms"
"Execution time: 23556.835 ms"

Why does the query suddenly become so much more loaded when I tack on that extra group-by?

EDIT: example9, example6, and example5 are all character varying

The reason why the third statement is slow is it has to group 2.8 million rows which cannot be done in memory any longer. So the sorting is done on disk which obviously is a lot slower:

You can see that in this line:

Sort Method: external merge  Disk: 286688kB"

The number of rows and the time it took can be seen in this line::

Sort  (...) (actual time=19110.027..22635.209 rows=2807146 loops=1)

You can also see that the first and second statements did the grouping using a hash function and both only had to process less than 2000 rows.

HashAggregate  (cost=...) (actual time=1617.381..1617.799 rows=1794 loops=1)

The memory that Postgres is allowed to use for sorting is controlled through the configuration parameter work_mem. If you increase that until the sorting can be done in memory, the performance should get better.


How to add duplicate records by basing on the count from another table (SQL) I wanted to add a duplicate record by basing on the count from another table
For example:
In table #1, I have the following records:
+------------------------+------------+
| first_name | last_name | Role       |
+------------------------+------------+
| John       | Smith     | Dev        |
| Jane       | Doe       | Tester     |
| Rock       | Lee       | Dev        |
+------------------------+------------+

And in table #2, I have the following records.
+------------------------+
| first_name | last_name |
+------------------------+
| John       | Smith     |
| John       | Smith     |
| Jane       | Doe       |
| Jane       | Doe       |
| Rock       | Lee       |
+------------------------+

And since there 2 records for John Smith and Jane Doe in table #2, my expected output for table #1 is like this:
+------------------------+------------+
| first_name | last_name | Role       |
+------------------------+------------+
| John       | Smith     | Dev        |
| John       | Smith     | Dev        |
| Jane       | Doe       | Tester     |
| Jane       | Doe       | Tester     |
| Rock       | Lee       | Dev        |
+------------------------+------------+

You could JOIN the tables
eg
select T1.first_name, T1.last_name, T1.role
from table#1 T1
  join table#2 T2
    on T1.first_name = T2.first_name and T1.last_name = T2.last_name
;

-- result
+----------+---------+------+
|FIRST_NAME|LAST_NAME|ROLE  |
+----------+---------+------+
|John      |Smith    |Dev   |
|John      |Smith    |Dev   |
|Jane      |Doe      |Tester|
|Jane      |Doe      |Tester|
|Rock      |Lee      |Dev   |
+----------+---------+------+

DBfiddle here.


SQL Server 2012 - Frequent SELECT and UPDATE on bit column. Index doesn't help I have a SQL Server table with about 2 million rows in, this table stores short textual documents. Here's my schema:

CREATE TABLE Documents (
    documentId bigint IDENTITY NOT NULL,
    content nvarchar(MAX) NOT NULL,
    isIndexed bit NOT NULL
)

I have a separate Lucene-based indexer process that does this:

SELECT TOP 1 documentId, content FROM Documents WHERE isIndexed = 0

It then performs the indexing operation before executing this:

UPDATE Documents SET isIndexed = 1 WHERE documentId = @documentId

Initially this worked fine. When every document in the table was not indexed (i.e. all isIndexed values were 0) then each retrieval took about 5ms.

However as more and more documents were indexed then the retrieval time slowly increased. Currently it's about 150ms - a 30 time speed reduction. I note that the UPDATE statement always seems to run in under 2ms, so I know that's not a problem.

From the start the table has always had a non-clustered index on the isIndexed column, but the Actual Execution Plan shows the SQL Server uses an Index Scan (rather than Seek).

What can I do to speed up the system?

I am aware that the "isIndexed" column existing is bad in itself, but due to how the indexer works it can't request documents by documentId directly. For this and other reasons I can't accept any answers that don't solve the immediate problem at hand.

An index on the bit column isn't going to help at all because of selectivity. You should consider a filtered index:

CREATE INDEX unindexed ON dbo.Documents(documentId) WHERE IsIndexed = 0;

You should also include an ORDER BY documentId in your SELECT.

Though you should test this in a staging environment because it may help your SELECT but may offset that with a more expensive UPDATE.


Two joins on same table in SQL I've got 2 SQL tables: NAMES and RELATIONSHIPS Currently NAMES just has 2 columns: name and name_id. The other is a list of relationships between people in the NAMES table.   It has 3 columns: primaryperson_id, relatedperson_id, and relationship_id.  The primaryperson_id and related_person_id are name_ids from the NAMES table.  Each person in NAMES can have multiple entries in either the primary or related columns of RELATIONSHIPS (is that a many-to-many relationship?).

This query works:

SELECT  people.name AS 'primary', relationships.related_person_id AS relatedto
FROM relationships
JOIN people
ON people.name_id=relationships.primary_person_id
ORDER BY people.name_id;

But I'd like to show a name (i.e. text) in the relatedto column rather than the id number.  How can I do that?

You can assign a table alias when you join tables, just like you do with the column names. Not only does it save you a lot of typing, but it makes the code a little more readable, and it solves the problem of joining the same table twice.

SELECT p1.name AS "primary",
       relationships.related_person_id AS relatedto,
       p2.name AS related_name
FROM people AS p1
INNER JOIN relationships AS r
    ON p1.name_id=r.primary_person_id
INNER JOIN people AS p2
    ON p2.name_id=r.related_person_id
ORDER BY p1.name_id;

In the example above, I've assigned p1 and p2 to the people table and r to the relationships table.

Note: some database platforms don't like the "AS" keyword. You can skip "AS" alltogether if you want, like so:

...
FROM people p1
INNER JOIN relationships r
...


Suggested SQL Server Backup Schedule using Ola Hallengren scripts We are building out a new AlwaysOn SQL Server 2016 database cluster and I am in the middle of setting up the backups using Ola Hallengren (https://ola.hallengren.com) backup scripts and I was looking for some advice when best to run the the following tasks:

DatabaseIntegrityCheck - SYSTEM_DATABASES
DatabaseIntegrityCheck - USER_DATABASES
IndexOptimize - USER_DATABASES

I am currently planning to use the following schedule for the backups:

DatabaseBackup - SYSTEM_DATABASES - FULL @ 23:30 every day
DatabaseBackup - USER_DATABASES - FULL @  23:00 every day
DatabaseBackup - USER_DATABASES - DIFF @ Every hour starting at 00:00 to 22:00
DatabaseBackup - USER_DATABASES - LOG @ Every 5 mins starting at 00:03 to 23:59:59

By reading the suggestion was to do the database integrity check before the backups so I was think maybe 30 mins before the main backup and then run the index optimize when a known time of little load. Would that make sense as a schedule for SQL Server backups?

[T]he suggestion was to do the database integrity check before the backups so I was think maybe 30 mins before the main backup and then run the index optimize when a known time of little load. Would that make sense as a schedule for SQL Server backups?

A few thoughts:

Index maintenance may not be necessary as often as you think. Usually index fragmentation has a very low impact on performance with modern hardware, especially when you take into account that all logs involved with index rebuilds have to be shipped to your Availability Group secondaries. I would suggest considering a daily (or even more frequent) statistics update, using Ola's maintenance solution, and make sure that you use this example from his site (take note of the NULLs for index fragmentation):

D. Update modified statistics on all user databases
EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y'

If you find that index fragmentation does cause performance problems, or you just want to keep them in decent shape, you can schedule that on a less frequent basis. Depending on the database and index size, maybe once a week or once a month.

This leads me into answering your "guesstimate" of 30 minutes before backups for consistency check. You won't know until you actually get the process running. If your monthly index maintenance takes three hours, you'll want to kick that off earlier than the statistics update. If CHECKDB takes five hours, then schedule it to run on an AG secondary, or restore the database to a stand-alone server and run it there (search for "offloading CHECKDB").

Differential backups every hour is overkill, in my opinion. I generally do these once a day, and possibly twice a day depending on the environment. You have your log backups, and you need to keep in mind that differentials are not incremental. They will continue growing until the next full backup, because they keep track of all changes since the last full backup. In some cases, differential backups can exceed the size of the full backup. In those cases, you'll need to evaluate whether a full backup is better than a differential.

Do you have enough disk space to keep 5-minute transaction log backups? Is that what the business needs (SLA, RTO, RPO)? Can you get away with 6 minutes? Over a three-month period, that extra minute makes a big difference in the number of files you're storing. Often I find most of my clients satisfied with a trade-off of 10- or 15-minute log backups.

Full backups could be done daily, but depending on your retention policy, as well as Recovery Time Objective and Recovery Point Objective, you might be more than happy with a weekly full backup of user databases, a daily full backup of system databases, and the differentials can fill in the gaps where necessary.

Ultimately, this all depends on what the business needs to satisfy service level agreements internally and externally. If a reporting / OLAP style database can be regenerated in two hours from an OLTP source, then you don't need to worry as much about it as the OLTP database.
I hope this helps you come up with an appropriate strategy for your business. That's what should drive these decisions.


Database design: Is there a much better way on this current relationships? I have a facultyand studenttable to store each information, respectively. I made a contacttable to store the contacts of either faculty or student. I would like to use the contact table as a generic one, means it can be used by either faculty or student.

My table diagram is something like this.

I compressed faculty and student into a USER table and have an account type in that table to know whether it is a faculty or student type of user. Then the USER table will have a one is to many relationship with the contact table.

  Student

  Faculty

  Contacts

  Student & Faculty = User table  User table -----<- Contact table

True story: I was a student at my school, then I taught there. Then one of the teachers took my class. You need to account for silliness like this.

And a faculty member or student isn't necessarily a user either.

Use table inheritance.

Faculty : Individual : Party

Student : Individual : Party

Where : means inherits.

Party hasOne User

A contact is a relationship between two parties:

Party hasMany ContactRelationships belongsTo Party

See Ready-to-Use Database models example for reference models.


How to enable/disable SQL agent jobs during fail over (AlwaysOn) Note: this is not duplicate question.

I have setup AlwaysOn. I have created SQL agent jobs manually on both the nodes. right now all the active jobs are running if node is PRIMARY.

Challenge

My DBA will disable few SQL agent jobs manually in only one node based on business requirements. Now how does it synchronizes to node 2?  Will DBA have to manually disable these related jobs in all the secondary nodes ?

I am thinking of creating a table (this table will be part of availability group) where I maintain the state of the jobs. and during fail over I am planning to run the script to read the data from this table and set the state of the jobs on other nodes. Is it right way? or do we have any recommended steps?

Kindly suggest

There are several ways to automate this and you are heading down the right path...you can rely on your DBA to manually disable and enable (not very nice ;), you can keep track of job state in a table and enable and disable using a periodic job that runs, or you can categorize your jobs and respond to a role change alert...this is what I do.

A rough outline is:

Pick a category for all the jobs that you want to only run on the current primary replica, I use the ag name
Set up a SQL Agent alert for error number 1480 (AG Role Change) that will call a stored procedure in master via a job passing in the category name
Build the sp to check to see if it is the primary, if it is enable all the jobs categorized with the parameter. If it is not the primary, disable all the jobs categorized with the parameter
Build some functions to help recognize if the AG is Primary or Secondary

There is a very detailed SQL Magazine series of articles by Michael K. Campbell that give code examples for how to structure either solution, I started with that article and only had to make minor modifications.


Providers in SQL Server linked server configuration How 'allow in process' works for providers in SQL server linked server configuration ? It looks like it's unchecked by default. What is the requirement for keeping this option checked and What are the pros and cons of using it.

What is Allow Inprocess : as the name says by selecting Allow Inprocess, SQL Server allows the provider to be instantiated or allows the provider to run as an In Process server. When the option is not set, the default behavior is to allow the provider to run outside the SQL Server process.
The question that arises is what is the benefit of running the process inside the SQL Server process or outside the SQL Server process.
Running the process outside the SQL Server Process:
Allowing the provider to run outside the SQL Server Process helps to protect the SQL Server process from errors in the provider, as when the provider runs outside the SQL Server process Updates or inserts involving column data types like  (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image) are not allowed.
Running the process inside the SQL Server Process:
By setting this option in the SQL Server providers, we will be able to handle the data types like (varchar(max), nvarchar(max), varbinary(max), text, ntext, or image) including long columns, image and text data.
In case of excel data we can see that when we import an excel data into the database tables without having predefined datatypes, by default the datatypes of some of the columns may be assigned to nvarchar(max) or ntext etc, so if we do net set the Allow Inprocess it will throw error.
If we don’t have such scenarious it is always advisable to leave the default settings of the SQL Server as it is. Only in cases where there is exception we will have to enable the option.


How to interpret undo statistics in Oracle AWR report? How can I use undo statistics in oracle AWR report for tuning Oracle? which indicators are important to identify that size of undo segments needs to be increased?

OK so the three things you need to look at to see if you need to increase your undo are:

The number of failures occuring - the value of STO/OOS
The value of Min/Max TR in comparison to your Undo Retention
The number of undo blocks per second * your block size * your retention guarantee, in comparison to the size of your undo tablespace

If there are any non-zero values in STO/OOS then you have a problem. The values of uS/uR/... will tell you what kind of problems are occurring. You will need to check the alert log for errors to figure out what's going wrong.

If Max TR is close to Undo Retention, let's say within 70%, then you should probably increase the retention to prevent ORA-1555 errors.

If Num Undo Blocks * Block Size is close to the size of your Undo Tbs then you should probably increase the size of the undo to avoid ORA-1652 errors.

But really you should be using the Undo Advisor to choose a good size for the tablespace: http://docs.oracle.com/cd/E11882_01/server.112/e25494/undo.htm#ADMIN11466

So in the example given, you are not having any active problems with undo size. Your undo retention is way above the length of the longest query so that's fine. Your undo tablespace should be a minimum of (UNDO_RETENTION * 12,333 * DB_BLOCK_SIZE ) / Length Of AWR Window In Seconds.

This article is ancient but it explains it well enough: http://www.orafaq.com/node/1888


Slow mysql query full text search tag database In the database are ~700k images. Each image has its own tag(s). I want to be able to search images by the tags with a full text search query. The query does exactly what I want but it's very slow. Can someone please help me speed it up, or create another one.

There are indexes on all the id-fields and the name field in the image_tag table.

SELECT
    image.*
FROM image
INNER JOIN (SELECT image_to_tag.image_id,
             GROUP_CONCAT(image_tag.`name`) AS tags
        FROM image_to_tag
        INNER JOIN image_tag
        ON (image_tag.id = image_to_tag.image_tag_id)
    GROUP BY image_to_tag.image_id) t ON t.image_id = image.id
WHERE MATCH (t.tags) AGAINST ('+justin* +backgrounds*' IN BOOLEAN MODE)

Table: image

id | filename
1  | image1.jpg
2  | image2.jpg
3  | image3.jpg

Table: image_to_tag

image_id | image_tag_id
1        | 1
1        | 2
2        | 3
2        | 4

Table: image_tag

id | name
1  | justin bieber
2  | backgrounds
3  | justin timberlake
4  | other backgrounds

If I search for "justin background" I want to find images 1 and 2. If I search for "justin bieber background" I want to find image 1.

Results explain query:

as I understand work of FT - it must be prepared in advance, so command

WHERE MATCH (t.tags) AGAINST ('+justin* +backgrounds*' IN BOOLEAN MODE)

against derived table:

(SELECT image_to_tag.image_id,
             GROUP_CONCAT(image_tag.`name`) AS tags
        FROM image_to_tag
        INNER JOIN image_tag
        ON (image_tag.id = image_to_tag.image_tag_id)
    GROUP BY image_to_tag.image_id) t

must not work - just because when index created no this table present

because query work (not return error from Your description) - MySQL ignore this, but need test with full table structure

also, JOIN with derived table - do not want to use index


What happens if there are too many inserts in MongoDB? How to ensure all data is stored? I use MongoDB to store periodically measured values. Every ~100 ms a bunch of values is inserted as document. It works fine, but I'm worried about performance issues. (I use safe inserts, it seems as if in PyMongo this is the default.)

What happens if there are more inserts per second than mongod is able to save to the hard disk? Will there be any warning or will it simply fail silently?

Is there any method to monitor write load? I've found only db.serverStatus().writeBacksQueued which is always set to false when I call it. How could I test how much data I have to insert to fill up the write queue?

mongostat displays locks. Is this something I should be worried about?

insert  query update delete getmore command flushes mapped  vsize    res faults  locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn repl       time
  *117     *0     *0     *0       0     2|0       0  17.4g  35.3g  3.76g      0     .:6.5%          0       0|0     0|0   124b     6k     2  SLV   09:58:10
  *111     *0     *0     *0       0     2|0       0  17.4g  35.3g  3.76g      0     .:0.8%          0       0|0     0|0   124b     6k     2  SLV   09:58:11
  *111     *0     *0     *0       0     2|0       0  17.4g  35.3g  3.76g      0     .:4.2%          0       0|0     0|0   124b     6k     2  SLV   09:58:1

Do I have to worry about write locks? What happens to an insert during a write locked time period? Is it queued and stored later on?

I am thinking about a simple replication setup using one master and one slave. Does the initial sync or a resync process lock the databases?

(I'm using version 2.4.3.)

Update:
I think have partly answered my own question. I managed to get up to 12.000 inserts per second using a simple while loop inserting a small test document. But qr|qw still shows that there are the read- and write queue is still empty:

insert  query update delete getmore command flushes mapped  vsize    res faults       locked db idx miss %     qr|qw   ar|aw  netIn netOut  conn repl       time
 11234     *0      2     *0    1563     1|0       1  21.9g  44.3g  1.22g      0    testdb:58.9%          0       1|0     1|1   797k   980k     6  PRI   10:26:32
 12768     *0      2     *0    1284     1|0       0  21.9g  44.3g  1.22g      0    testdb:58.0%          0       0|0     0|1   881k     1m     6  PRI   10:26:33
 12839     *0      2     *0    1231     1|0       0  21.9g  44.3g  1.22g      0    testdb:60.3%          0       0|0     0|1   883k     1m     6  PRI   10:26:34
 12701     *0      2     *0     910     1|0       0  21.9g  44.3g  1.22g      0    testdb:61.8%          0       0|0     0|1   858k     1m     6  PRI   10:26:35
 12241     *0      2     *0    1206     1|0       0  21.9g  44.3g  1.22g      0    testdb:56.7%          0       0|0     0|0   843k     1m     6  PRI   10:26:36
 11581     *0      2     *0    1406     1|0       0  21.9g  44.3g  1.22g      0    testdb:61.8%          0       0|0     0|1   811k     1m     6  PRI   10:26:37
  8719     *0      2     *0    1210     1|0       0  21.9g  44.3g  1.22g      0    testdb:43.8%          0       0|0     0|1   618k   762k     6  PRI   10:26:38
 11429     *0      2     *0    1469     1|0       0  21.9g  44.3g  1.22g      0    testdb:60.6%          0       0|0     0|1   804k   993k     6  PRI   10:26:39
 12779     *0      2     *0    1092     1|0       0  21.9g  44.3g  1.22g      0    testdb:60.2%          0       1|0     0|1   872k     1m     6  PRI   10:26:40
 12757     *0      2     *0     436     1|0       0  21.9g  44.3g  1.22g      0    testdb:59.7%          0       0|0     0|1   838k   432k     6  PRI   10:26:41

I suppose this means that inserts alone won't cause a lot of troubles:
"Queues will tend to spike if you’re doing a lot of write operations alongside other write heavy ops, such as large ranged removes." (found here]

My open question: What happens to my data if the write queue increases on long term?

You have answered some of your own questions here, specifically you have a decent idea about the write lock aspect of the equation - 12,000 insert/sec gets you to ~60% write lock.  That's a reasonable level to get consistent performance - you will be getting some contention, and some ops will be a little slower, but you really want to start worrying at about 80% - like a lot of things, when you start exceeding 80% available capacity you will start hitting issues a lot more often.

In terms of other bottlenecks, and specifically how quickly you can write to disk - this can cause problems, but to look at the relevant stats over time I would recommend getting MMS installed with the munin-node plugin to give you hardware and IO stats in addition to the MongoDB stats.

When you have that, the metrics you will want to keep an eye on are:

The Average Flush time (this is how long MongoDB's periodic sync to disk is taking)
The IOStats in the hardware tab (IOWait in particular)
Page Faults (if your disk is busy with writes and you need to read data, they are going to be competing for a scarce resource)

It's a bit complicated then, but here's a basic idea:

When average flush time starts to increase, be worried
If it gets into the multiple second range, you are probably on the limit (though this depends on the volume of data written and the disk speed)
If it approaches 60 seconds you will see performance degrade severely (the flush happens every 60 seconds, so they would essentially be queuing up)
High IOWait is going to hinder performance too, especially if you have to read from disk at any point
Hence looking at page fault levels will also be important

The other piece of this puzzle, which we have not mentioned yet, is the journal.  That will be persisting data to disk as well (by default every 100ms) and so it will be adding to the disk's load if it is on the same volume.  Hence if you are seeing high disk utilization, then moving the journal off to another disk would be a good idea.

There are no real "magic numbers" to stay under, in most cases it's all relative, so get a good baseline for your normal traffic, check to see if things are trending up and maybe load test to see what your limits are and when things start to degrade and you will be in good shape.

After all that pre-amble, on to some of your questions:

  What happens if there are more inserts per second than mongod is able
  to save to the hard disk? Will there be any warning or will it simply
  fail silently?

If you start to stress the disk to the levels described above, eventually everything is going to slow down and at some point (and this will depend on time outs, how beefy your hardware is, how you handle exceptions) your writes will fail - if you are using a recent version of pymongo then you will be using safe writes by default and those will then fail.  If you wish to be a little more paranoid, you can occasionally do a write concern of j:true which will wait to return OK until the write has made it to the journal (i.e. on disk).  This will, of course, be slower than a normal safe write, but it will be an immediate indication of disk capacity related issues, and you could use it to block/queue other operations and essentially act as a throttle to prevent your database from being overwhelmed.

  I am thinking about a simple replication setup using one master and
  one slave. Does the initial sync or a resync process lock the
  databases?

I think I covered locking overall at the start, but to answer this piece specifically: First, make sure you are using a replica set, not master/slave.  The master/slave implementation is deprecated and not recommended for use in general.  As for the initial sync will add some load to the primary in terms of reads, but not in terms of writes, so you should be fine in terms of locking.

  What happens to my data if the write queue increases on long term?

As you can probably tell from the explanation above, the answer is very much dependent on how you write your application, how you choose to have your writes acknowledged and how much capacity you have available.  You can, essentially, be as safe as you wish when it comes to writing to disk on MongoDB, but there is a performance trade off, as mentioned with the j:true discussion above.

Generally, you want to figure out your limiting factor - be it locking, disk speed etc. and then track the levels over time and scale out (sharding) or up (better hardware) before you hit a hard limit and see performance problems.

One last thing, db.serverStatus().writeBacksQueued is actually a metric that will only ever be non-zero in a sharded environment, and it has to do with making sure that writes to a chunk during a migration are dealt with appropriately (handled by the writeback listener).  Hence it essentially is a red herring here - nothing to do with general write volume.


What is the purpose of the NOLOGGING clause on Oracle database TABLES and INDEXES I was wandering what is the practical purpose of using the NOLOGGING clause on tables and indexes.

For what I know from the Oracle documentation, we can prevent the database from generating redo logs only when INSERTING in direct-path mode. There is no possibility to avoid redo log generation when UPDATING or DELETING.

So, we usually write

INSERT /*+ APPEND NOLOGGING */ INTO TABLE ... ;
commit;

in order to prevent redo log generation.

I totally don't get what is the practical advantage of using the NOLOGGING clause on TABLE or INDEX creation neither to use the NOLOGGING clause on TABLESPACES creation in order to set the default on the objects that will be created on that tablespace.

Can anyone describe a practical scenario where setting this clause on database objects may give advantages?

There is no such hint as NOLOGGING.

Sure, UPDATE and DELETE operations can not take advantage of NOLOGGING, but there are other operations that can:

logging_clause

  NOLOGGING is supported in only a subset of the locations that support
  LOGGING. Only the following operations support the NOLOGGING mode:

  DML:

  Direct-path INSERT (serial or parallel) resulting either from an
  INSERT or a MERGE statement. NOLOGGING is not applicable to any UPDATE
  operations resulting from the MERGE statement.

  Direct Loader (SQL*Loader)

  DDL:

  CREATE TABLE ... AS SELECT (In NOLOGGING mode, the creation of the
  table will be logged, but direct-path inserts will not be logged.)

  CREATE TABLE ... LOB_storage_clause ... LOB_parameters ... CACHE |
  NOCACHE | CACHE READS

  ALTER TABLE ... LOB_storage_clause ... LOB_parameters ... CACHE |
  NOCACHE | CACHE READS (to specify logging of newly created LOB
  columns)

  ALTER TABLE ... modify_LOB_storage_clause ... modify_LOB_parameters
  ... CACHE | NOCACHE | CACHE READS (to change logging of existing LOB
  columns)

  ALTER TABLE ... MOVE

  ALTER TABLE ... (all partition operations that involve data movement)

  ALTER TABLE ... ADD PARTITION (hash partition only)

  ALTER TABLE ... MERGE PARTITIONS

  ALTER TABLE ... SPLIT PARTITION

  ALTER TABLE ... MOVE PARTITION

  ALTER TABLE ... MODIFY PARTITION ... ADD SUBPARTITION

  ALTER TABLE ... MODIFY PARTITION ... COALESCE SUBPARTITION

  CREATE INDEX

  ALTER INDEX ... REBUILD

  ALTER INDEX ... REBUILD [SUB]PARTITION

  ALTER INDEX ... SPLIT PARTITION


Is it possible to validate more than one parameter simultaneously into a Stored Procedure in sql? On Microsoft SQL Server 2008 I realized a stored procedure which contains more than 20 parameters, in this SP I update or insert data into table which does not accept null values.

I need to evaluate if one of these parameters contain a null value before performing the insert or update into table.

Example:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8),
  @parameter2 char (20),
  @parameter3 char (20),
  @parameter4 char (20),
  @parameter5 char (20),
  @parameter6 char (20),
  @parameter7 char (20),
  @parameter8 char (40),
  @parameter9 char (15),
  @parameter10 char (15) ,
  @parameter11 char (15)...

I have a possible solution for this, which is performing evaluation parameter by parameter and validate if is null add some value, but I'm looking for a better solution for this.

There are a few options that you could use for this.  The first would be to just set default values within the procedure declaration to guarantee that there would never be NULL values passed in for these parameters:

CREATE PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8) = 'test',
  @parameter2 char (20) = 'test2'....

The second alternative to checking them one at a time is to just have a single statement that will set them based on a NULL value.  Something along the lines of

  ALTER PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8),
  @parameter2 char (20),
  @parameter3 char (20),
  @parameter4 char (20),
  @parameter5 char (20),
  @parameter6 char (20),...

  Select @parameter1 = CASE when @parameter1 is null then 'test' else @parameter1 end
  ,@parameter2 = Case when @parameter2 is null then 'test2' else @parameter2 end...

The other alternative is that if you just want to see if any of the values is NULL before performing the action, you could pretty easily do a single if statement that checks for that sort of thing

alter PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8) = NULL,
  @parameter2 char (20) = NULL....

  as
  if @parameter1 is null or @parameter2 is null.....
  begin
        print 'You passed in a NULL value for one of your parameters'
  end
  Else
  begin
        --your statement here
  end

Depending on the value of the CONCAT_NULL_YIELDS_NULL setting, you could also use:

IF (@Parameter1 + @Parameter2 + @Paramater3 ...) IS NULL
BEGIN
    RAISERROR (N'One or more parameters is NULL', 1, 1);
END
ELSE
    -- other statements that will run if all @parameters are NOT null.
BEGIN

I am sure there are other alternatives as well, but I would think one of these would get you where you need to go.


Search for rows with coordinates within a Polyline I'm stuck with the logic of a MySQL query where I need to find if a row exists where:

The column longitude and latitude are within a Polyline
The column hit is duplicated within the last month

I've been able to achieve the second point using :

select   latitude,
         longitude,
         count(*)
from     hits
WHERE datetime BETWEEN (CURRENT_DATE() - INTERVAL 1 MONTH) AND CURRENT_DATE()
group by latitude,
         longitude
having   count(*) > 1

but I can't figure out a way to only search for rows where the latitude and longitude are within a Polyline. Performance is a main factor here as the table hits can have millions of records.(can also do with a version 1 not so scalable/performant)

Any help would be much appreciated!

EDIT: I'm using MySQL 5.7 and I also have a column coordinates of type point (spatial type) also Polyline means :

  A polyline is a list of points, where line segments are drawn between consecutive points

EDIT 2: Here is the query for people using php/symfony

$query = $this->em->createQuery('
            SELECT   count(i) as counter
            FROM     HitsBundle:Hit i
            WHERE i.datetime BETWEEN :lastMonth AND :now
                  AND
                  MBRCovers(
                    ST_Buffer(
                        ST_GeomFromText(\'LineString('.$linestring.')\') ,
                        0.00001
                    ),
                    i.coordinates
                  ) = 1
            GROUP BY i.coordinates
            HAVING  counter > 1
        ')
            ->setParameter('lastMonth', $lastMonth)
            ->setParameter('now', new \DateTime())
            ->setMaxResults(1);

When Google says Polyline they're referring specifically to the Encoded Polyline Algorithm Format. That's a rendering format though. Essentially, you're rendering a point (with longitude, and latitude) as an ASCII string for easy API-transfer. That's not useful to you.

In GIS terms, what you need to do is take the Polyline from Google and construct a GIS LINESTRING from it. A GIS LINESTRING is a series of sequential points. For completeness in parlance, a line segment is a LINESTRING between just two points. A LINESTRING includes one or more line segements.

How do you get the Polyline to LINESTRING? There are many ways. For PostgreSQL with PostGIS that's easy because you have ST_LineFromEncodedPolyline which will do it. In MySQL you'll either have to do this in the app, in your case PHP, or write a very complex function (like ST_LineFromEncodedPolyline in PostGIS) to do it.

Personalized advice: in your case I would convert from PolyLine to WKB in PHP and then bring it into the database with ST_GeomFromWKB

Either way, after you have the LINESTRING you'll have to

Buffer the LINESTRING to create a POLYGON with ST_Buffer
Use MBRCoveredBy(linestring|polygon, pt)

Then in the end, it'll look something like this,

SELECT MBRCoveredBy(
  ST_Buffer( ST_GeomFromWKB( ? ) , DISTANCE_IN_METERS ),
  t.pt
)
FROM TABLE as t;

Where ? is the WKB output from PHP.


Link DAX OpenQuery Output to SQL Server Temp Table I want to record measures from a Tabular model, as KPIs, and store the values in a table in SQL Server.

I have created a linked server from my SQL Server instance to my SSAS instance.
I have written a stored procedure to execute the DAX code via OPENQUERY, with the intention of storing these in a temp table, before loading them in to the KPI table. I am using a temp table because I am querying multiple tabular models.

My problem occurs when I try to update my temp table with values from my OPENQUERY output. My OPENQUERY output is currently within a CTE, and
I was hoping to do a simple join to the temp table, but because the output from the DAX query returns each column name within [ ], when I try to join on one of the OPENQUERY columns I receive the error "Invalid column name...".

E.g.

UPDATE temp
SET temp.[Current Contract Count] = cte.[Contract Count]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[Copy of Service Zone Code] = temp.[ServiceZoneAlternateKey]

The error occurs because 'Copy of Service zone' does not exist in the OPENQEURY output; the output column name is [Copy of Service Zone].

I may well be missing a simple trick here?

How can I join an OPENQUERY output, returning tabular model data, to my T-SQL temp table?

If a column name has brackets, you need to escape them. You can see how if you use function QUOTENAME().

SELECT QUOTENAME('[awful column name]')
-- Result: [[awful column name]]]

Note the extra closing brackets. You should try adding an extra pair of enclosing brackets together with an additional closing one for your column names that include brackets.

Assuming that your problem is tabular_cte's columns:

UPDATE temp
SET temp.[Current Contract Count] = cte.[[Contract Count]]]
FROM #ServiceZoneKPIs AS temp
INNER JOIN tabular_cte AS cte
ON cte.[[Copy of Service Zone Code]]] = temp.[ServiceZoneAlternateKey]


MySQL / MariaDB SSL Connection without verify CA I need to cryptography a connection between client and server.

This is config in my server

MariaDB [(none)]> show variables like "%ssl%";
+---------------------+----------------------------+
| Variable_name       | Value                      |
+---------------------+----------------------------+
| have_openssl        | YES                        |
| have_ssl            | DISABLED                   |
| ssl_ca              |                            |
| ssl_capath          |                            |
| ssl_cert            |                            |
| ssl_cipher          |                            |
| ssl_crl             |                            |
| ssl_crlpath         |                            |
| ssl_key             |                            |
| version_ssl_library | OpenSSL 1.0.1t  3 May 2016 |
+---------------------+----------------------------+

Its possible to authenticate with SSL without verify the client certificate with server CA (ssl_ca) ?

Its possible to authenticate with SSL without verify the client certificate with server CA (ssl_ca) ?

No, you will need to create a CA, generate a Certificate Generation Request (CSR), then generate a client certificate from the CA.  Both sides of the connection will need the client cert and the CA.

You can reuse the same client certificate for all clients.  Or, when creating the user you can use REQUIRE CN= to enforce the user to connect with a specific certificate (this requires a different step when generating the client cert).

I recommend using passwords as well for authentication
Pay attention to the expiration date of your CA.  Updating the CA or client certs requires a restart of MySQL
You can have more than one CA in your ssl_ca file to allow for a transition period if you are required to rotate CAs.
The CN for your CA cannot be the same as your client cert.  You will tear your hair out trying to figure out what you did wrong if you do this.


Two If's And An Else I am trying to run this t-sql query but I keep getting an error of

  Incorrect syntax near the keyword 'END'

How should this be re-written in order to execute properly?  What I want to achieve is if my variable @d2 >= GetDate() I want to run a check to see if @d2 exists in my table farquard if it does then back-up @d1 by 1 day, if it does not then back-up @d1 by two days.

This is my with errors syntax, can someone help me?

Declare @d1 datetime, @d2 datetime
Set @d1 = '01/01/2013'
Set @d2 = '01/04/2013'

if @d2 >= CAST(GetDate() As Date)
Begin
  if Not Exists(Select TOP 1 * FROM farquard WHERE CAST(stinktoothruth As Date) = @d2)
      BEGIN
        Set @d2 = CAST(GetDate()-2 As Date)
    END
ELSE
      BEGIN
        Set @d2 = CAST(GETDATE()-1 As Date
      END
  END
End

The very last CAST is missing a parenthesis

Set @d2 = CAST(GetDate()-2 As Date

should be

Set @d2 = CAST(GetDate()-2 As Date)

That fixes the main issue. (also, once that was fixed you had and extra END at the very end which I removed--or alternately, adding an additional BEGIN.)

    Declare @d1 datetime, @d2 datetime
    Set @d1 = '01/01/2013'
    Set @d2 = '01/04/2013'

    if @d2 >= CAST(GetDate() As Date)
      Begin
        if Not Exists(Select TOP 1 * FROM farquard WHERE CAST(stinktoothruth As Date) = @d2)
          BEGIN
            Set @d2 = CAST(GetDate()-2 As Date)
          END
      END
    ELSE
          BEGIN
            Set @d2 = CAST(GETDATE()-1 As Date)
          END
--END REMOVED

OR, alternately,

Declare @d1 datetime, @d2 datetime
Set @d1 = '01/01/2013'
Set @d2 = '01/04/2013'

if @d2 >= CAST(GetDate() As Date)
Begin
  if Not Exists(Select TOP 1 * FROM farquard WHERE CAST(stinktoothruth As Date) = @d2)
      BEGIN
        Set @d2 = CAST(GetDate()-2 As Date)
    END
ELSE
--BEGIN ADDED
begin
      BEGIN
        Set @d2 = CAST(GETDATE()-1 As Date)
      END
  END
end


Query Plan showing missing index warning on trivial statements I have a Stored Procedure (ReportAdvancedUP) which runs fast (~1sec) in MSSMS but takes a long time (~1.8mins) via our Web Application.  I am aware that this usually comes down to bad choice of Execution Plan so I've had a look at both examples.

Running in MSSQS the Actual Execution plan comprises of four Clustered Index Seeks joined by nested loops.  No problems.

I then use the following query to pull out the cached execution plans used by my Stored Procedure (ReportAdvancedUP):

SELECT
    OBJECT_NAME(st.objectid,st.dbid) AS ObjectName,
    cp.usecounts AS ExecutionCount,
    st.TEXT AS QueryText,
    qp.query_plan AS QueryPlan
FROM
    sys.dm_exec_cached_plans AS cp
    CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp
    CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st
WHERE
    cp.objtype = 'Proc'
    AND OBJECT_NAME(st.objectid,st.dbid) = 'ReportAdvancedUP';

I get a QueryPlan that is not only much larger but is also reporting "Missing Index (CREATE NONCLUSTERED INDEX)" entries on queries as trivial as SET NOCOUNT ON; and IF(@ToDate IS NOT NULL)!!!

Furthermore the index it is saying is missing doesn't make sense in the context of the procedure in general... e.g.

I have a table called OrganisationLocations with primary key is ID int IDENTITY(1,1) and a foreign key OrganisationID int NOT NULL.
The table is clustered as Organisation, ID as they are always used together and it is better to group entries by Organisation than by ID
I nearly always join to the OrganisationLocations without providing BOTH the ID and OrganisationID, both from a security perspective and also for optimisation.

However, the index it keeps telling me is missing is:
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[OrganisationLocations] ([OrganisationID])

Can anyone suggest what might be going on here, regarding [a] the difference in execution plans, [b] Missing Index warnings on trivial code and [c] why it might want to add an index by OrganisationID alone.

Also, please advise what additional information might be helpful.

Thanks,
Chris

Without seeing the plan and based on the information you provided, it sounds like you don't have that column, OrganisationID, in one of your existing indexes.  Foreign keys aren't automatically indexed, so if it is part of your join or in the where clause then there could possibly be a benefit of adding the index.  The missing index feature isn't always right, so you should always review whether you really need the columns suggested indexed or if you can modify another index to include the columns instead of adding a separate index.

You can use this query from Glenn Berry to look at the missing index dmvs and see what the index advantage, unique_compiles, and user_seeks to help make  the decision:

select user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) as [index_advantage]
    ,migs.last_user_seek
    ,mid.[statement] as [Database.Schema.Table]
    ,mid.equality_columns
    ,mid.inequality_columns
    ,mid.included_columns
    ,migs.unique_compiles
    ,migs.user_seeks
    ,migs.avg_total_user_cost
    ,migs.avg_user_impact
from sys.dm_db_missing_index_group_stats as migs with (nolock)
inner join sys.dm_db_missing_index_groups as mig with (nolock) on migs.group_handle = mig.index_group_handle
inner join sys.dm_db_missing_index_details as mid with (nolock) on mig.index_handle = mid.index_handle
--where mid.[statement] = '[yourdb].[dbo].[OrganisationLocations]'

order by index_advantage desc
option (recompile);


Increase sga_max_size on Oracle 10g Windows i would like to increase the max memory size of one Oracle 10g database installed on Windows.

The v$parameter shows

  sga_max_size 452984832

and

  sga_target 452984832

Note that the ISDEFAULT column for sga_max_size is TRUE and every other IS settings are FLASE

I have found a init.ora file in the admin\MYINSTANCE\scripts and i have changed both parameters and restarted the service with no success.

There is another init.ora file in srvm\admin folder but i don't know if i can modify this one.

Where should i modify this parameter to have the memory increased ?

Thanks

Normally Oracle no longer uses the init.ora file anymore. It uses the spfileSID.ora in the $ORACLE_HOME/dbs directory. This is a binary file. Tio change the parameter(s) you should use:

alter system set sga_max_size=XXXM scope=spfile;

Now restart the database and the SGA_MAX_SIZE should be set at XXXM. Now you can alter the SGA_TARGET:

alter system set sga_target=XXXM scope=both;


Is it safe and reliable to backup MySQL databases by backing up the data folder? I have a MySQL database [server version 5.0.x, type InnoDB] which I want to backup. I don't have username/password to back it up using mysqldump. My question, is it safe to back up the database by backing up the entire MySQL's data folder (of course, after stopping the MySQL server service in Windows 7)?

Take a look here.

In a word, a resounding "yes" is the answer to your question provided the server is stopped!

I'm not sure that there are any hot backups available for Windows - Percona's XtraBackup only works on Unix (AFAIK). It's possible that MySQL have a Windows solution for hot backups, but there's nothing Open Source - (again AFAIK, and it's been a while since I've looked at this issue).

But, you are perfectly safe with a cold backup!

I'm just wondering how you're planning to make use of the cold backup in the absence of a password? Does the app that uses it have it's own table dumping facility? My advice on this is to figure out how to get your raw data back ASAP!


Dealing with co-dependent not null columns Say I have a table entities (subclassed by people and organizations tables), where each entry requires a primary_email_id. The entities table looks like this:

entities
========
id INT AUTO_INCREMENT
full_name VARCHAR(255)
primary_email_id
primary_address_id
primary_tel_id

This primary_email_id is a foreign key to a table contacts, which is structured like this:

contacts
========
id INT AUTO_INCREMENT
entity_id INT NOT NULL
type VARCHAR(255) NOT NULL -- ex: "tel", "email", "address", "fax", etc.

and has a companion table contact_attributes:

contact_attributes
==================
id INT AUTO_INCREMENT
contact_id INT NOT NULL
name VARCHAR(255) NOT NULL -- ex: "ext" or "username"
value VARCHAR(255) NOT NULL -- ex: "+25" or "coolboss27"

I think it makes sense requiring that the primary_email_id be NOT NULL and that the entity_id in the contacts table be not null, but this co-dependence creates a problem. When creating an entity for the first time, we must have a primary_email_id to put in the row. But to do this, we must create a contact (and contact_attribute of email: ...@...). However to create that contact, we need to have already created the entity we're trying to create. So we are stuck.

I guess that means that I cannot have both the entity_id in contacts and the primary_email_id in entities be NOT NULL, and presumably primary_email_id is the one that should be nullable. This could be partially solved by including the actual primary_email_address instead of an id, but then the table is no longer normalized and I definitely don't want that.

Is there something I'm missing here or is making primary_email_id nullable just a necessary tradeoff?

First, a name like 'entities' is too vague. The common name for org or individual is 'parties'.

Second, people can have zero, one, or many addresses, and can share the same address. It's a many2many relationship.

I would do it like this (using STI here, but break it out to CTI if you like):

PARTIES
id
type (org or individual)
name

ADDRESSES
id
type
address_info...

PARTY_ADDRESSES
party_id
address_id
role (ex 'sales', 'service', 'mobile', 'work', ...)
priority

Your validation rule would be that a party must have an address of type 'email' whose priority is higher than any other of their addresses. Business rules are best enforced via triggers any applicable logic and not your data model.


How to select recursively in a child parent design situation (in MySQL)? Let's take into consideration the following tables:

CREATE TABLE actions
(
    id BIGINT(20) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    user_id BIGINT(20) unsigned NOT NULL
);

CREATE TABLE recurring_actions
(
    original_action_id BIGINT(20) unsigned NOT NULL,
    recurring_action_id BIGINT(20) unsigned NOT NULL
);

The data in each table just for an example is as follows:

actions

id name user_id

1 fdfdk 3
2 43434 3
3 43334 5
4 sdkk  6
5 zz    7
6 ll    3

recurring_actions

original_action_id recurring_action_id
1                  2
4                  6
2                  3
3                  5

How can someone query and fetch all the chain of recurring action ids that lead to the last child with id 5 ?

Expected result should be [1, 2, 3, 5] (including 5 is ok)

I can solve this so far only by recursive querying by application code. Get the original action then if found, query again and so on. Recursive consecutive queries initiated by PHP/C# or whatever code used.

I want to do this instead in one recursive (or other solution) MySQL query

The answer should focus only on a query solution (if possible) and not in organizing the database in another way. I am aware of other possible database designs which are more suitable for child parent relationships (such as closure tables, nested sets etc).

Without additional information such as transitive closure or nested sets, you need some kind of iteration (well, if you know that the length of the chain cant be longer than n you could do that number of left joins). Since 5.7 does not support recursive CTE:s you can express the recursion in a stored procedure instead:

DELIMITER $$
CREATE OR REPLACE PROCEDURE materialized_path
    (IN x BIGINT, IN acc text, OUT path TEXT)
BEGIN
    DECLARE parent_ID INT DEFAULT NULL;
    DECLARE tmppath text default '';

    select original_action_id
    from recurring_actions
    where recurring_action_id = x into parent_ID;

    IF parent_ID IS NULL THEN
        SET path = rtrim(acc);
    ELSE
        CALL materialized_path(parent_ID, rtrim(acc)||parent_ID||',', path);
    END IF;

END$$

DELIMITER ;

call materialized_path(5,'',@path); select @path;
Query OK, 0 rows affected, 1 warning (0.23 sec)

+--------+
| @path  |
+--------+
| 3,2,1, |
+--------+
1 row in set (0.00 sec)

Now you need another procedural part that splits @path into its components and there ordinal numbers.


Does MySQL lock full table in case of update query on specific rows? I just wanted to know if the MySQL 5.5 with InnoDB will lock the whole table instead of some specific rows for SELECT query while UPDATE query is running on that specific table?

I can see that the SELECT queries stop responding while I was running the below query. All of the associated services were down in that particular period.

UPDATE table SET column='sample' where column is null;

Around 3.6 million(Total: 4 Million) rows were going to updated with this query.

Index on the column which is part of where clause makes any effect on this?
Is there any possible way to minimise the execution time?  I am ok with the query changes as long as the output remains the same.

Locking 3.6M rows, especially when it is 90% of the table, has multiple burdens on the system.  It may "feel like" the entire table is locked.

This sounds like a 1-time fix; perhaps you should simply wait for it to finish -- which could take many minutes.

If you need a better, but more complex, solution, do the UPDATE in chunks of 100-1000 rows.  More details:  http://mysql.rjweb.org/doc.php/deletebig#deleting_in_chunks


Index on an input value without saving it to a column? Short version:

I'm looking for a way to give Postgres a value just for an index (as opposed to saving that value to a column and then indexing it). Similar to an index on an expression, but the expression will be computed outside of Postgres. If this is possible, how can it be done?

Long version:

I've created an index on a plpgsql function that normalizes an email address, however using it is proving to be very clunky - either I have to remember to always call the function whenever I use the index, or I have to create a view and remember to use that for reads but not for writes... On top of that, the plpgsql implementation is 3x slower than the python implementation (and 30x uglier imo), and RDS doesn't support plpythonu. So I might as well go back to go back to doing it in the application, but I don't want to go back to saving a computed column and indexing on that; I still want to have the normalized email only in the index.

The way to do this I guess would be to somehow create a fake column name, and the input for this fake column would be used for the index. If this is possible, how can it be done?

Postgres supports indexes on expressions so if the value you want to index can be computed from other values in the row this will work (assuming the queries you are using a written in a way that the query planner can see the use for the index). See https://www.postgresql.org/docs/10/static/indexes-expressional.html

For more complex requirements a materialised view might be required, though these appear to need "manual" updates via REFRESH MATERIALIZED VIEW which might make them imprctical depending on your need.

From your later edit:

  or I have to create a view and remember to use that for reads but not for writes

You can use "INSTEAD OF" triggers to make views like this appear to be directly writable, though of course you need to be careful of performance issues if the data is written to often.


MySQL specify default value when data has incorrect values I have a csv file where some integer fields are blank (e.g. , ,) or have an NA value (e.g. ,NA  ,).  When I load the data (LOAD DATA LOCAL INFILE...)  into a table MySQL correctly warns that the fields have incorrect integer values, however it inserts a 0, not the DEFAULT value specified in the CREATE TABLE DDL, into the columns.  Is there any way I can specify the value to use in case of incorrect values in a data file?

When you need to 'cleanse' the incoming data, there are two techniques to consider.  Suppose the column foo needs to be -1 to represent "NA":

Using @variables...

LOAD into and @variable, then use SET to manipulate the value while storing it.  There are examples in the docs. In your case you might use something like SET foo := IF(@foo = 'NA', -1, @foo) inside the LOAD DATA statement.

Using a temp table...

Instead of LOADing directly into the target table, load into a separate table (possible TEMPORARY).  Then have a few SQL statements to manipulate the data in that table before copying to the "real" table.  In your case:  UPDATE tmp SET foo = -1 WHERE foo = 'NA';.  Then INSERT INTO real SELECT * FROM tmp;.  Note foo would be VARCHAR in tmp in spite of being INT in real.

The temp table approach can handle more complex transformations.


Order result by count of common array elements Using Postgres 9.4, I'm interested in having an array of integers like user_ids_who_like and provide an array of users (like user_ids_i_am_following) to sort that intersection.

Something like:

select *
from items
where [there is an intersection between
       user_ids_who_like with user_ids_i_am_following]
order by intersection(user_ids_who_like).count

Is grouping and ordering by an array intersection possible?

Example data:

items
name          | user_ids_who_like
'birds'       | '{1,3,5,8}'
'planes'      | '{2,3,4,11}'
'spaceships'  | '{3,4,6}'

For a given user_ids_who_i_follow = [3,4,11], can I do something like:

select * from items
where <user_ids_who_like intersects with user_ids_who_i_follow>
order by <count of that intersection>

Desired result:

name          | user_ids_who_like  | count
'planes'      |  '{2,3,4,11}'      | 3
'spaceships'  |  '{3,4,6}'         | 2
'birds'       |  '{1,3,5,8}'       | 1

One possibility seems to be something like this:

select id, user_ids_who_like, (user_ids_who_like & '{514, 515}'::int[]) as jt
from queryables
where user_ids_who_like && '{514, 515}'
order by icount(user_ids_who_like & '{514, 515}'::int[]) desc;

But I can't tell if this style (using the intarray extension rather than native array functions and operators) is outdated; any feedback from more sophisticated users here? It's not clear to me how to do the intersection of two arrays using the methods and operators.

With tools of the basic Postgres installation only, you might unnest() and count in a LATERAL subquery:

SELECT i.name, i.user_ids_who_like, x.ct
FROM   items i
     , LATERAL (
   SELECT count(*) AS ct
   FROM   unnest(i.user_ids_who_like) uid
   WHERE  uid = ANY('{3,4,11}'::int[])
   ) x
ORDER  BY x.ct DESC;  -- add PK as tiebreaker for stable sort order

We don't need a LEFT JOIN to preserve rows without match because count() always returns a row - 0 for "no match".

intarray

Assuming integer arrays without NULL values or duplicates, the intersection operator & of the intarray module would be much simpler:

SELECT name, user_ids_who_like
     , array_length(user_ids_who_like & '{3,4,11}', 1) AS ct
FROM   items
ORDER  BY 3 DESC NULLS LAST;

I added NULLS LAST to sort empty arrays last - after the reminder from your later question:

How to get 0 as array_length() result when there are no elements

Install intarray once per database for this.

Use the overlap opertaor && in the WHERE clause to rule out rows without any overlap:

SELECT ...
FROM   ...
WHERE user_ids_who_like && '{3,4,11}'
ORDER  BY ...

Why? Per documentation:

  intarray provides index support for the &&, @>, <@, and @@ operators,
  as well as regular array equality.

Applies to standard array operators in a similar fashion. Details:

Can PostgreSQL index array columns?

Alternatively and more radically, a normalized schema with a separate table instead of the array column user_ids_who_like would occupy more disk space, but offer simple solutions with plain btree indexes for these problems.


Does putting single quotation marks around numeric constants really protect from SQL injection in MySQL? The manual seems to suggest that using quotes around numbers is sufficient to protect from SQL injection.

According to section 5.3.1. General Security Guidelines of the MySQL 5.1 Reference Manual:

  If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it.

Does that mean that the user is protected if the user enters the value 234' OR 1=1 # ? (i.e. to generate the query SELECT * FROM table WHERE ID='234' OR 1=1 #')

Short answer, no.  The quoting trick is easily defeated by including your own closing quote and then a comment symbol to eliminate the final concatenated quote, precisely as in your example.

To protect yourself from SQL injection you must use bind variables.  Changing your example to SELECT * FROM table WHERE ID = :X and then binding the user's input to X solves the problem instantly and completely.  It is impossible to over-emphasize how important this practice is!


What is BMK Operator in SQLServer I was trying to answer this question stating from clause is optional..But i am stuck with an Operator in plan..below is screenshot of execution plan ..

As you can see there is BMK operator in query plan ,but no indication showing how it is calculated..

steps i have tried so far:
I started searching with BMK1000 ,but it shows a bunch of questions with the same operator.finally i found one thread which says "The BMK that you're referencing is the storage location within the heap that gets kept with the nonclustered index instead of the cluster key."..But not sure how this is related to me,since i don't have any indexes..

ASK:
what is the BMK operator and how it is calculated.Any Pointers also will be helpfull

Here is SQLFiddle to Repro the issue

It's not an operator in its own right. It is an output column from the table scan operator on the heap. It is the "bookmark" that contains the physical address of the row (this is the same bookmark as is referred to in the phrase "bookmark lookup").

This is passed along the pipeline into the update operator so it knows the row it should be updating.

In SQL Server 2016 you can see the actual values for this column by using the query_trace_column_values extended event

Plugging those into sys.fn_PhysLocFormatter shows the file:page:slot

SELECT sys.fn_PhysLocFormatter(0xB002000001),
       sys.fn_PhysLocFormatter(0xB0020000010001)

Returns

+------------------+------------------+
| (No column name) | (No column name) |
+------------------+------------------+
| (1:688:0)        | (1:688:1)        |
+------------------+------------------+