How can I improve this nested MySQL query to avoid redundant WHERE statement? I am trying to improve on a nested MySQL query, using the following table structure (omitting a few dozen not relevant to my question):

tbl_users:
ID | email  (all subscribers to our email list)

tbl_mailings:
ID | mail_title (each marketing email)

tbl_sends:
ID | user_id | mailing_id (each user who was sent each mailing)

tbl_opens:
ID | user_id | mailing_id (each user who opened each mailing)

tbl_actions:
ID | user_id | mailing_id (each user who took the target action on each mailing)

I inherited this query that I have been using to get the open rate and action rate for each mailing:

SELECT subq_opens.numopens/subq_sends.numsends as Open_Rate,
       subq_actions.numactions/subq_sends.numsends as Action_Rate,
FROM
(SELECT COUNT(DISTINCT sends.userID) AS numsends
    FROM tbl_sends WHERE mailing_id = 5694) AS subq_sends,

(SELECT COUNT(DISTINCT opens.userID) AS numopens
    FROM tbl_opens WHERE mailing_id = 5694) AS subq_opens,

(SELECT COUNT(DISTINCT actions.userID) AS numactions
    FROM tbl_actions WHERE mailing_id = 5694) AS subq_actions

Not too elegant, but functional, even though I have to change the mailing ID in multiple places every time.
However, now I need to change that WHERE statement to something a great deal more complicated, and I don't want to have to repeat the WHERE statement so many times.

Is there a way I can reformulate this without the redundant WHERE?
I have tried various nesting schemes, but can't seem to find a way to get it right. I can't change anything about the table structure; that is set in stone. Thank you in advance for your help.

Reformulating your query:

SELECT
    numopens / numsends AS Open_Rate,
    numactions / numsends AS Action_Rate
FROM
    ( SELECT
          ( SELECT COUNT(DISTINCT s.userID)
            FROM tbl_sends AS s
            WHERE s.mailing_id = param.mailing_id
          )
        AS numsends,
          ( SELECT COUNT(DISTINCT o.userID)
            FROM tbl_opens AS o
            WHERE o.mailing_id = param.mailing_id
          )
        AS numopens
          ( SELECT COUNT(DISTINCT a.userID)
            FROM tbl_actions AS a
            WHERE a.mailing_id = param.mailing_id
          )
        AS numactions
      FROM
          ( SELECT
                5694     AS mailing_id,
                417      AS another_parameter,
                'funny'  AS one_more_parameter
          ) AS param
    ) AS cte ;


Report transition from Access to Crystal (multiple queries) I have just started a new job where I am responsible for building and improving reports.

There is one report I am trying to move from Access to Crystal.  The data is stored in an application called Acctivate.  The previous person who held my job built the report with a series of queries that built multiple tables, and then a final query which aggregated the information from the tables built by the first five queries.

The PTB want the report in Crystal so the management for each client can call up their info and run the report, rather than have to call me and have me do it.

What is the best approach to this multi-level query (currently make-table) that will let me move the report out of Access?

Given that you have a SQL Server backend you have a few options depending how much freedom you have to add objects to the application db or on the server.

If you are not permitted to add objects to the native schema i would request a small report db on the same server where you can create views or stored procs that reference the source database schema using 3 part names.

If you can re-write the query and deploy it as a view that might be a good option.  If the query is too complex to be rewritten as a single query then you could reproduce the behavior of "make tables" with temorary #tables and deploy it as a stored procedure.

If you arent permitted to create objects on the server at all i would rewrite the query using #tables and embed the whole thing in the report query.

Views and SP's get better performance as the number of users/hits increase compared to embedded queries.


Storage problem with MYSQL read replica I have one master RDS instance of 50 GB storage and created a read replica of the master DB with the same configuration.

I use this read replica only for SELECT operations nothing else. But suddenly I got the storage full problem with the read replica DB. Master Db is working properly, how is it possible if the size is same but replica DB's size is full?

Getting below error when executing the complex query(inner, join, group).
ERROR 3 (HY000): Error writing file '/rdsdbdata/tmp/MY7U2XRf' (Errcode: 28 - No space left on device)

In AWS Console, The slave DB status is "Storage-full" and event log message is :
The free storage capacity for DB Instance: example-slave is low at 1% of the provisioned storage [Provisioned Storage: 49.07 GB, Free Storage: 527.80 MB]. You may want to increase the provisioned storage to address this issue.

But, I checked the free size with below command of both Master and replica Db instances, it's almost the same.

SELECT table_schema,
       ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB"
FROM information_schema.tables
GROUP BY 1
ORDER BY 2 DESC;

Master DB's space is used 23.86 GB out of 50 GB and Slave DB's space is used 24 GB out of 50 GB.

Please help me.

Possibly what happened was that a complex SELECT was run.  It was so complex that it built one or more giant temporary tables that overran the disk tmpdir filesystem.


How to treat a row as a table I have two tables with the following structures:

CREATE TABLE USERS
(
  USERID VARCHAR2,
  USERNAME VARCHAR2
);

CREATE TABLE CONFIRMATIONS
(
 PERSONELID NUMBER,
 ACCOUNTING VARCHAR2,
 FINANCE VARCHAR2,
 HR VARCHAR2
);

ACCOUNTING,FINANCE,HR columns of the the CONFIRMATIONS table correspond to the USERID column in the USERS table. I need a query that will put USERNAME corresponding the USERID if in any of the ACCOUNTING,FINANCE,HR columns have a value in it. So suppose we have the following data in the tables:

USERID|USERNAME           PERSONELID | ACCOUNTING| FINANCE |  HR
---------------           ----------------------------------------
   1    Jack                  113          2          1      NULL
   2    Rony                  120          3         NULL      2
   3    Michel

I need something looking like this:

PERSONELID | ACCOUNTING | FINANCE |  HR
----------------------------------------
    113         Rony        Jack    NULL
    120        Michel       NULL    Rony

You need to use multiple self joins to get that information:

select c.personelid,
       a.username as accounting,
       f.username as finance,
       h.username as hr
from confirmations c
  left join users a on c.accounting = a.userid
  left join users f on c.finance = f.userid
  left join users h on c.hr = f.userid;


Linking identifiers across tables in MySQL (Foreign Keys) Given a simplified example of two tables

table Contact(ID int primary key, Name text not null)
table ContactPhone(ContactID int not null, PhoneNumber text not null)

Is there a way to tell MySQL that the ContactPhone.ContactID field is referencing the Primary Key of Contact?

Can there be automatic handling? If the Contact is deleted, then relevant ContactPhone.ContactID should be set to zero.

Ideally this flag/code/operation should be attached to the ContactPhone table. This way existing software that interacts with the Contact table can keep on the way it is, and the ContactPhone table and related software can be added or removed as one unit, without the necessity of altering the Contact table.

Specifically I want to be able to be able to empty out the Contact table, reset the AUTO_INCREMENT to =1, and bring in new Contacts AND I don't want to have to tell this program anything about ContactPhone. I want it to be automatic.

Is there a way to tell MySQL that the ContactPhone.ContactID field is referencing the Primary Key of Contact?

Yes, use a foreign key.

  Can there be automatic handling? If the Contact is deleted, then relevant ContactPhone.ContactID should be set to zero.

Yes, add in an action after ON DELETE in the below code.

Here's an example:

create table ContactPhone(
  ContactID int not null,
  PhoneNumber text not null,
  CONSTRAINT `fk_contactid`      -- this is the foreign key
    FOREIGN KEY (`ContactID` )
    REFERENCES `Contact`(`id` )
    ON DELETE <some action>      -- the on delete action goes here
) engine = InnoDB;               -- make sure to use InnoDB if you want FKs to be enforced

Make sure that either InnoDB is specified as the storage engine, or that your version of MySQL defaults to InnoDB.  Other storage engines will parse and silently ignore foreign key constraints.

Here's the grammar for a MySQL foreign key:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION


Count specific query occurred in Redshift I wanted to count the number queries executed over one day. I have this working version :

SELECT DATE_TRUNC('day', starttime) AS date,
       COUNT(*) AS COUNT
FROM stl_query
WHERE querytxt LIKE 'COPY sensor_%_temp FROM%'
  AND starttime BETWEEN '2017-01-24 00:00:00' AND '2017-01-25 00:00:00'
GROUP BY 1;

Is there a better way of doing it?

IMO your query looks good. I have one idea.

Instead of:

    AND starttime BETWEEN '2017-01-24 00:00:00' AND '2017-01-25 00:00:00'

You can write:

    AND starttime::date = '2017-01-24'

The double column operator (::) converts DateTime type to Date, resulting shorter query.


Compare data between 2 different schemas We refactored our old database. Some tables were dropped, some tables were split into 2 new tables etc. So now we have an absolutely new DB schema which has many differences from the old one. Someone has written an application which migrates data from the old database to the new one.

I'm looking for methods/techniques to compare correctness of data migration. Is there any standard or often-used ways to accomplish such compare?

After some research, I found one technique how to do it - create a VIEW of each table of the old database i.e. I will try to represent in VIEW data in format of old table (old schema), and then compare data in old table with data in VIEW.

I'm interested in other ways.

Although the representation of the data differs between the old and new schema the data itself should be the same.  This means a series of reconciliation queries will provide certainty that the migration was without error.

Say you have an Orders table.  The total number of Orders per year, month or day will be the same in the old and new databases.  Similarly the total value of those Orders for each time period will match.  You can expand this idea for orders-by-product, orders-by-customer etc.

If any data was changed in the migration the corresponding reconciliation queries will not return the same value from old and new DBs.  You will know immediately that the problem was with an order from customer "X" on day "Y" for product "Z" and can focus your debugging on that area.

The same approach can be used for any other table.  Take customers as and example.  You can count customers by country, business area, first letter of name or any other differentiator you choose.


MySQL tables/databases into different disks I am aware that there is a possibility to have a single database server with many tables/databases, and you can separate some of those tables/databases into separate disks.
Lets say you have 5 tables/databases, and 5 drives. So you attach each table/database for a different drive. Thus increasing IO and everything else.

What is that technique called ?

I think if you try separating databases, you just mount different drive to different folder for different database, but what if you want to separate tables into separate drives (each table in different disk)

I'm not aware of a specific term for this practice but you can specify different data and/or index dirs using the create table syntax.

This only works for MyISAM tables and your OS must support symlinks.

In my practice though, we generally just segment sets databases into their own LV. These are generally a segment per functional application.  Having a single logical volume with all the tables actually in there it's easier to keep track of an manage disk space for each DB.

Those LVs though, are generally in a RAID 10 config to help balance IO.


Running sum total over 14 days for each date In a database of transactions spanning about 50 customers over a period of 8 months, I need to find which customers have 0 total transactions over a consecutive 14-day period. The customer table (customer_tbl) has the following general structure:
cust_id, date, new_transactions, total_transactions

So for every row, I want a column where it shows the total number of transactions that the customer had, for the past 14 days. I have tried to create a view to display customers who had 0 total transactions using the code below.
create view sumoftransactions as SELECT
cust_id
, date
, REVERSE_RUNNING_SUM
FROM
(
    SELECT
      cust_id
    , date
    , SUM(new_transactions) OVER (PARTITION BY cust_id ORDER BY date DESC, VALID_FLAG ASC) REVERSE_RUNNING_SUM
    , VALID_FLAG
    FROM
    (
        SELECT cust_id
        , date
        , new_transactions
        , 1 VALID_FLAG
        FROM customer_tbl

        UNION ALL

        SELECT cust_id
        , DATE_ADD(date, interval -14 day) date
        , -1 * new_transactions
        , -1 VALID_FLAG
        FROM customer_tbl
    ) t
) tt
WHERE tt.VALID_FLAG = 1
and tt.reverse_running_sum = 0;

However the output is skewed. I believe it has something to do with the cut-off dates. Each customer has a different date for their first record. The running sum formula includes the record of their first transaction date into the output because there are no dates before this, so the running sum is calculated to be 0, even though it is not.
How do I rectify this code? The following is some sample data. To simplify it, the following table only has 3 customers and covers a period of one week. I want to generate the customer_id and dates whereby there have been at least 3 consecutive days of 0 transactions. The desired output is written below.
CREATE TABLE `customer_tbl` (
  `cust_id` text,
  `date` date default null,
  `new_transactions` text,
  `total_transactions` text
)

INSERT INTO `customer_tbl` VALUES
(‘10001’, ‘2020-02-01’, ‘1’, ‘1’),
(‘10001’, 2020-02-02’, ‘1’, ‘2’),
(‘10001’, ‘2020-02-03’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-03’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-04’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-05’, ‘0’, ‘2’),
(‘10001’, ‘2020-02-06’, ‘1’, ‘3’),
(‘10002’, ‘2020-02-01’, ‘1’, ‘1’),
(‘10002’, 2020-02-02’, ‘1’, ‘2’),
(‘10002’, ‘2020-02-03’, ‘4’, ‘6’),
(‘10002’, ‘2020-02-03’, ‘0’, ‘6’),
(‘10002’, ‘2020-02-04’, ‘1’, ‘7’),
(‘10002’, ‘2020-02-05’, ‘0’, ‘7’),
(‘10002’, ‘2020-02-06’, ‘1’, ‘8’),
(‘10003’, ‘2020-02-01’, ‘0’, ‘0’),
(‘10003’, 2020-02-02’, ‘0’, ‘0’),
(‘10003’, ‘2020-02-03’, ‘1’, ‘1’),
(‘10003’, ‘2020-02-03’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-04’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-05’, ‘0’, ‘1’),
(‘10003’, ‘2020-02-06’, ‘1’, ‘2’);

This is a bit tricky but very fast query that counts how many days customer spent idle up to the date. You can use it as subquery to refine by additional conditions. I hope it is selfexplanatory enough.
Unfortunately dbfiddle works pretty bad with UDVs so I can't provide a working sample.
SELECT cust_id
     , date
     , new_transactions
     , total_transactions
     , IF( @curr_cust_id <=> @curr_cust_id := cust_id,
       /*THEN*/ IF( new_transactions = 0,
                /*THEN*/ @idle4 := @idle4 + 1,
                /*ELSE*/ @idle4 := 0
                /*FI*/ ),
       /*ELSE*/ @idle4 := 0
       /*FI*/ ) AS idle_for
  FROM customer_tbl
 WHERE ( TRUE OR @idle4 := 0 )       -- UDV initialization
 ORDER BY cust_id ASC
        , date ASC
;

+---------+------------+------------------+--------------------+----------+
| cust_id |       date | new_transactions | total_transactions | idle_for |
+---------+------------+------------------+--------------------+----------+
|   10001 | 2020-02-01 |                1 |                  1 |        0 |
|   10001 | 2020-02-02 |                1 |                  2 |        0 |
|   10001 | 2020-02-03 |                0 |                  2 |        1 |
|   10001 | 2020-02-03 |                0 |                  2 |        2 |
|   10001 | 2020-02-04 |                0 |                  2 |        3 |
|   10001 | 2020-02-05 |                0 |                  2 |        4 |
|   10001 | 2020-02-06 |                1 |                  3 |        0 |


Resume log shipping from a specific transaction Is it possible to force SQL Log Shipping to run from a given .trn file onward?

I have two databases, where one ships to another this was working fine. However I had to restore the primary database and in the interim another .trn file was created. (This would be rubbish given the DB was restored and so couldn't be applied anywhere.) For the next transaction I manually backed up the whole database, and could manually restore it to the logship destination. (13.trn below)

The next transaction file that was created could also be manually restored. However the jobs are stuck on the transaction before the full backup, (12.trn below) and of course the job is failing as it is behind the destination db.

If I can set it to try 15.trn, it should (probably) work! I've tried changing msdb..log_shipping_secondary_databases to no avail.

So, is it possible to set which file sqllogship.exe uses next?

Basic example below!

10.trn
11.trn
<DB Was Restored>
12.trn
13.trn (contains full backup)
14.trn
15.trn

I have manually reset the log shipping by updating the last_restored_file column in the log_shipping_monitor_secondary table on the standby server to the last transaction log that was successfully restored.

Something like this:

UPDATE lsms
SET lsms.last_restored_file = '{transaction log file}'
FROM dbo.log_shipping_monitor_secondary lsms
WHERE lsms.secondary_database = '{DB Name}';


Primary Key Question Building a database on Access 2007 and having trouble selecting the primary key. The tables consists of a student table and another table where the students are checked in. I would have liked to make the Student ID the primary key, but the check in staff doesn't request that on their paper-based forms. I also thought about the making the email the key, This would mean that they would only have to use their school provided emails

Please respond, perhaps a concatenated key would work?

NOTE: The information requested from students on the form when they sign in is there Name, Email, Date, Phone

If the school provided email is always going to be available and everyone is required to have one then you could certainly use that as the primary key.  If not then it sounds like you don't have a good natural key.  You might consider going with a surrogate key.  Create an additional column that is auto incrementing and put your primary key on it.  Then create indexes on the email and student id.

Lot's of people will argue that you should always use a natural key, and other's will argue that you should always use a surrogate key.  In my personal opinion you should use a natural key where it makes sense and a surrogate when it doesn't.  A natural key makes sense when it is reasonably short, consistent and always available.

I'm not a big fan of large concatenated primary keys.  If nothing else when you link the table to another one you may have to include all of those columns to create a good join and that makes your system heavily de-normalized and your queries slower.  At that point a simple integer surrogate key will let you create simple fast joins.


Is there a way to alter a table definition (add columns) in Oracle and have it replicate to materialized views on remote databases? I have a master table, and several remote read-only materialized views on pre-built tables that are being fast refreshed to keep in sync with the master table, using a dblink.

The table definitions (master and remotes) are currently identical.  I want to add a column to the master table, and eventually have any changes propagate to the remote tables...without breaking anything :)

I don't have much experience with this, so I'm not 100% sure how to proceed.

My current plan is:

Add column to master table
Add column to remote tables
Drop the materialized views on the remote tables
Recreate the materialized views on the remote tables, but with the added column.

What I want advice on is whether this should work, or do I need to do something with the materialized view log on the master as well?

As long as you don't add data between the last refresh before the column add and after the column add, you should be fine.  If you must allow for this then the easiest thing would be to do a complete refresh at the end of your plan.  You shouldn't need to re-create the materialized view log.

Setup

--Create master table.
drop table t1;
create table t1 (id number(10) primary key, datetime date);
insert into t1 values (1, sysdate-1);
create materialized view log on t1 with primary key;

--Create materialized view table.
drop materialized view t2;
drop table t2;
create table t2 as (select * from t1);

--Create materialized view.
create materialized view t2 on prebuilt table with reduced precision
refresh fast on demand
as select id, datetime from t1;

--Show an empty materialized view.
select * from t2;

--Add data to t1.
insert into t1 values (2, sysdate);
commit;

--Refresh
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t2;

Planned Column Add

--Add Column.
alter table t1 add n varchar2(1);
alter table t2 add n varchar2(1);

--Re-create the materialized view.
drop materialized view t2 preserve table;
--***T1 updates between last refresh and the following statement will be lost.
create materialized view t2 on prebuilt table with reduced precision
   refresh fast on demand
   as select id, datetime, n from t1;

--Add data.
insert into t1 values (3,sysdate+1,'a');
commit;

--Refresh.
execute dbms_snapshot.refresh('T2','f');

--Show changed data.
select * from t1;
select * from t2;


Prevent T-SQL code that is not compatible to break the script Here is an interesting challenge that I have not been able to crack... There is one nice T-SQL query that I Jonathan Kehayias created some time ago to find Implicit Conversions issues on the queries that I love. The thing is, this query doesn't work on databases with old compatibility levels (80 and before). I assume this is because Table Valued Functions (TVFs) where introduced in SQL Server 2005.

The problem is, if I use this script to validate implicit conversions on all my databases like this:

declare @sql    nvarchar(4000)
set @sql =
'IF EXISTS (SELECT * FROM sys.databases WHERE name = ''?'' AND compatibility_level >= 90)
BEGIN
    USE ['+'?'+'] ;
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
    SET QUOTED_IDENTIFIER ON
    DECLARE @dbname SYSNAME
    SET @dbname = QUOTENAME(DB_NAME())

    BEGIN TRY
        RAISERROR(''?'', 0, 42) WITH NOWAIT;
        WITH XMLNAMESPACES
            (DEFAULT ''http://schemas.microsoft.com/sqlserver/2004/07/showplan'')
        INSERT INTO DMTAdmin.dbo.BestPractices_ImplicitConversions
        SELECT
            GETDATE(),
            @dbname,
            stmt.value(''(@StatementText)[1]'', ''varchar(max)''),
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)''),
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)''),
            t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)''),
            ic.DATA_TYPE AS ConvertFrom,
            ic.CHARACTER_MAXIMUM_LENGTH AS ConvertFromLength,
            t.value(''(@DataType)[1]'', ''varchar(128)'') AS ConvertTo,
            t.value(''(@Length)[1]'', ''int'') AS ConvertToLength,
            query_plan
        FROM sys.dm_exec_cached_plans AS cp
        CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp
        CROSS APPLY query_plan.nodes(''/ShowPlanXML/BatchSequence/Batch/Statements/StmtSimple'') AS batch(stmt)
        CROSS APPLY stmt.nodes(''.//Convert[@Implicit="1"]'') AS n(t)
        JOIN INFORMATION_SCHEMA.COLUMNS AS ic
            ON QUOTENAME(ic.TABLE_SCHEMA) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Schema)[1]'', ''varchar(128)'')
            AND QUOTENAME(ic.TABLE_NAME) = t.value(''(ScalarOperator/Identifier/ColumnReference/@Table)[1]'', ''varchar(128)'')
            AND ic.COLUMN_NAME = t.value(''(ScalarOperator/Identifier/ColumnReference/@Column)[1]'', ''varchar(128)'')
        WHERE t.exist(''ScalarOperator/Identifier/ColumnReference[@Database=sql:variable("@dbname")][@Schema!="[sys]"]'') = 1
    END TRY
    BEGIN CATCH
    END CATCH;
END
'

exec sp_msforeachdb @sql

... the script will bomb because 9 out of my bizzilion databases are in compat level 80! You can easily try this by creating one database in a box with an old compat level and run this script, you will see that it will fail.

As you can see my script is using a TRY/CATCH, but that has not helped because the problem is actually a compilation error (and not a run time error).

Also I tried with an IF right at the beginning of that dynamic T-SQL (as you can see) but again, decision points will not prevent the code from being compailed for those databases and failing.

Nothing that I have tried so far helps me to avoid that error and as a result, the job I have reports as "failure" even when I'm OK with these specific databases being "skipped" if I could.

Do any of you guys have an idea of how could I implement this so that I could search on all but those old compat level databases?

Instead of doing the sysdatabases.comptlevel check inside of your dynamic SQL,  pull the sysdatabases.cmptlevel check out to the top-level batch and use it to determine which databases to build/run dynamic queries against.  Alternatively, use the cmptlevel to customize the main query as needed.

Whether you build one big SQL query to hit all desired databases, or use a cursor/loop to build/run a dynamic query for each desired database, is up to you.

A quick, pseudo-code outline of a cursor/loop solution:

declare @dbname varchar(30), @cmptlevel int, @sql varchar(max)
declare dbcur cursor for select name, cmptlevel from sysdatabases
open dbcur
fetch dbcur into @dbname, @cmptlevel
while @@sqlstatus = 0
begin
    select @sql='... common query code ... add '@dbname..' prefix to tables as needed ... '+
                case when @cmptlevel <  90 then '... cmptlevel< 90 specific code ... add '@dbname..' prefix to tables as needed ...'
                     when @cmptlevel >= 90 then '... cmptlevel>=90 specific code ... add '@dbname..' prefix to tables as needed ...'
                end +
                '... common query code ... add '@dbname..' prefix to tables as needed ... '
    exec sp_excecutesql @sql
    fetch dbcur into @dbname, @cmptlevel
end
close dbcur


How to find if a column is referenced in a computed column? I'm trying to mass retype columns. That means first dropping and recreating any constraints they are part of.

I found columns referenced by these constraints

Foreign Keys,
Primary Keys,
Indexes,
Check constraints,
Rules,
Default constraints.

But I cannot find Computed columns.

I've looked into INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE, but it doesn't include Computed Columns.

There is also sys.computed_columns which shows definition, but doesn't list columns in searchable manner.

Is there anywhere else I can look? If SQL Server can figure out the dependence, I thought I would be able to as well.

Thanks to Scott Hodgin I found it in sys.sql_expression_dependencies

SELECT
    OBJECT_NAME(sed.referencing_id)     AS referencingTable
    , pc.[name] AS computedColumn
    , pc.is_computed
    , cc.[name] AS referencedcolumn
    , cc.is_computed
FROM sys.sql_expression_dependencies sed
JOIN sys.[columns] pc ON sed.referencing_minor_id = pc.column_id AND sed.referencing_id = pc.[object_id]
JOIN sys.[columns] cc ON sed.referenced_minor_id = cc.column_id AND sed.referenced_id = cc.[object_id]
WHERE sed.referencing_minor_id > 0      -- referencing object is Column
AND sed.referenced_minor_id > 0         -- referenced object is Column
AND sed.referencing_id = sed.referenced_id  -- references the same table


Auto incrementing a column based on another I have an application which generates invoice_id's. These id's need to be unique. The issue is that a client can have more than one invoice depending on what plan they are on. It could be 2, 3 or 4. So I made a column with extra invoice_id's which I called invoice_idA and invoice_idB.

Now I do not know how to handle the incrementing of the invoice_id. It would be easier if it is one invoice so I can just use an auto_increment but I will have an issue because if there is more than one invoice_id in a column for one client, there could be duplicates.

Looking at the picture,

I need a way to make invoice_id increment to 1008 based on the value in invoice_idA of 1007.
If I insert a value in invoice_idB like 1008, how will I increment invoice_id to read from it because it could be 2,3 or 4 extra invoice numbers. I believe even if there was a way I may need to know the exact column to increment from but in this case, it could be invoice_idA or invoice_idB.

romeo, before I craft a reply, I want to ask: Is there any chance you have the opportunity to edit the schema in this case?

If you can, you definitely should, because the problems you're running into are because of denormalization.

Normalization of the schema

From your example table above, it appears this should be split into at least two separate tables. From the information you've given you should have an invoice table and a client table, with (pseudocode) definitions as

CREATE TABLE invoice (invoice_id, client_id, invoice_date);

and

CREATE TABLE customer (client_id,service_id, phone_number);

Using these tables, you can establish a foreign key relationship from invoice (client_id) to customer (client_id), allowing a many-to-one relationship between invoice and client.

Subsequently, you can obtain all invoices pertaining to a particular client via

SELECT * FROM invoice JOIN client ON invoice.client_id = clinet.client_id
WHERE client = ...

Hope this helps!


In InnoDB, does a Transaction imply any implicit locking of a table? The Isolation level is REPEATABLE_READ.

The logic is as under:

Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction and exit
If Table A has No Data, Proceed further
Delete a record in Table B
Transaction ends

Now, my question is about the following scenario:

The current execution reaches at point #4.
I someone inserts data (Note, the current execution is at point # 4) in the Table A that would have returned some result in #2.
Now, The current execution will execute #5. It will delete a record that should not be deleted.

Is there any implicit locking in transaction or do I need to lock Table A Explicitly so no one can insert any data in Table A before I commit changes?

In repeatable reads, there is always row-level locking imposed via the gen_clust_index (aka the Clustered Index). This is the beauty of Transactions. What is even more interesting is that InnoDB has four transaction isolation levels, not just one:

There are four values for tx_isolation you can set:

READ-UNCOMMITTED
READ-COMMITTED
REPEATABLE-READ (default)
SERIALIZABLE

In your particular case, inserting data into TableA actually does not get written to disk. The necessary changes are recorded in three(3) distinct places:

log buffer in memory
in ibdata1

undo tablespace
rollback segments
double write buffer

redo log info in either ib_logfile0 or ib_logfile1

The same applies with the delete in step 5.

Executing a rollback will undo the delete and then undo the inserts.

You must remember something very important: If you want to rollback multiple SQL commands, you must begin like this:

SET autocommit = 0;
START TRANSACTION;

Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction (via ROLLBACK) and exit
If Table A has No Data, Proceed further
Delete a record in Table B
Transaction ends

COMMIT;

Give it a Try !!!

When everyone is using repeatable reads

your INSERTs are only seen by you
someone else's DELETEs are only seen by the other person

CAVEAT : Table level locking is never implicit for InnoDB. If you want to lock a table, you must issue, LOCK TABLE explicitly.


Tracking of deleted records who delete the transaction in SQL server 2008r2 how can we check who deleted the transaction even after few days in sql server 2008 r2
is there any command instead of logs

You'd have to do forensics on the transaction logs using 3rd party tools

SQL Log Rescue
ApexSQL Log
?

Of course, the log entries may not be there due to backups or simple recovery.

If you want auditing, build it in. And restrict permissions of course.


SQL Trigger not respecting where clause I have a trigger:

CREATE TRIGGER [dbo].[UpdateTbl]
   ON  [dbo].[TableB]
   AFTER INSERT

AS
BEGIN

       DECLARE @role varchar(20)
       select @role = u.[role] from inserted i join users u on u.id = i.creator_id

       if @role = 'client'
              update TableA
              set updated_date = i.created_date from inserted i where TableA.id = i.trans_id

       if @role = 'tech'
              update TableA
              set updated_date = i.created_date from inserted i where TableA.id = i.trans_id and i.[public] = 'true'

END
GO

But it doesn't work as expected. The updated_date is still updated with the value of i.created_date even if i.[public]='false'. Any idea?

When you DECLARE and SELECT @role= at the beginning of your code, you assume that the trigger will only be applied to single-row inserts. Effectively, the value of @role is not guaranteed, as it is assigned multiple times, and you cannot control the order.

To fix this, you could instead join the users table into your update:

UPDATE a
SET a.updated_date=i.created_date

FROM TableA AS a
INNER JOIN inserted AS i ON a.id=i.trans_id
INNER JOIN users AS u ON u.id=i.creator_id

WHERE u.[role]='client' OR
      u.[role]='tech' AND i.public='true';

In this example, the update is performed if a) the role is "client", or b) the role is "tech" and public is "true".


Slow index scans in large table Update 2020-08-04:
Since this answer is apparently still being viewed regularly I wanted to provide an update on the situation. We're currently using PG 11 with table partitioning on timestamp and are easily handling a few billion rows in the table(s). The index-only scans are a life saver and it wouldn't be possible without.

Using PostgreSQL 9.2, I have troubles with slow queries on a relatively large table (200+ million rows). I'm not trying anything crazy, just adding historic values. Below is the query and the query plan output.
My table layout:
                                   Table "public.energy_energyentry"
  Column   |           Type           |                            Modifiers
-----------+--------------------------+-----------------------------------------------------------------
 id        | integer                  | not null default nextval('energy_energyentry_id_seq'::regclass)
 prop_id   | integer                  | not null
 timestamp | timestamp with time zone | not null
 value     | double precision         | not null
Indexes:
    "energy_energyentry_pkey" PRIMARY KEY, btree (id)
    "energy_energyentry_prop_id" btree (prop_id)
    "energy_energyentry_prop_id_timestamp_idx" btree (prop_id, "timestamp")
Foreign-key constraints:
    "energy_energyentry_prop_id_fkey" FOREIGN KEY (prop_id) REFERENCES gateway_peripheralproperty(id) DEFERRABLE INITIALLY DEFERRED

The data ranges from 2012-01-01 till now, with new data constantly being added. There are about 2.2k distinct values in the prop_id foreign key, distributed evenly.
I notice that the row estimates aren't far off, but the cost estimates seem larger by factor 4x. This probably isn't an issue, but is there anything I could do about it?
I expect that disk access might be the issue, since the table isn't in memory all the time.
EXPLAIN ANALYZE
SELECT SUM("value")
FROM "energy_energyentry"
WHERE
  "prop_id"=82411
  AND "timestamp">'2014-06-11'
  AND "timestamp"<'2014-11-11'
;

 Aggregate  (cost=214481.45..214481.46 rows=1 width=8) (actual time=51504.814..51504.814 rows=1 loops=1)
   ->  Index Scan using energy_energyentry_prop_id_timestamp_idx on  energy_energyentry (cost=0.00..214434.08 rows=18947 width=8) (actual time=136.030..51488.321 rows=13578 loops=1)
         Index Cond: ((prop_id = 82411) AND ("timestamp" > '2014-06-11 00:00:00+00'::timestamp with time zone) AND ("timestamp" < '2014-11-11 00:00:00+00'::timestamp with time zone))
 Total runtime: 51504.841 ms

Any suggestions how to make this faster?
I'm also fine with just hearing I didn't do anything weird.

Your table is big, and so is any index spanning the whole table. Assuming that:

only new data (with timestamp = now()) is entered
existing rows are neither changed nor deleted.
you have data since 2012-01-01 but queries are predominantly on the current year (?)

I would suggest a partial, multi-column (covering!) index:

CREATE INDEX ON energy_energyentry (prop_id, "timestamp", value)
WHERE "timestamp" >= '2014-01-01 0:0';  -- adapt to your needs

Only include the time range that is queried regularly. Effectiveness deteriorates over time with new entries. Recreate the index from time to time. (You may need to adapt your queries.) See linked answer below.

The last column value is only included to get index-only scans out of this. Aggressive autovacuum setting may help by keeping the visibility map up to date, like @jjanes already mentioned.

The partial index should fit into RAM more easily and stay there longer.

You may need to include this WHERE condition in queries to make the planner understand the index is applicable to the query, like:

SELECT sum(value) AS sum_value
FROM   energy_energyentry
WHERE  prop_id = 82411
AND   "timestamp" > '2014-06-11 0:0'
AND   "timestamp" < '2014-11-11 0:0'
AND   "timestamp" >= '2014-01-01 0:0'; -- seems redundant, but may be needed

Since your query is summing a lot of rows (rows=13578), this is going to take some time, even with an index-only scan. It shouldn't be anywhere near 50 seconds, though. Less than a second on any halfway decent hardware.

Related (but ignore CLUSTER and FILLFACTOR, both are irrelevant if you can get index-only scans out of this):

Index optimization with dates

Aside:
Since you currently have an index on (prop_id, "timestamp"), the additional index on just (prop_id) may cost more than it's worth:

Is a composite index also good for queries on the first field?


Constant Scan spooling I have a table with a few dozens of rows. Simplified setup is following

CREATE TABLE #data ([Id] int, [Status] int);

INSERT INTO #data
VALUES (100, 1), (101, 2), (102, 3), (103, 2);

And I have a query that joins this table to a set of table value constructed rows (made of variables and constants), like

DECLARE @id1 int = 101, @id2 int = 105;

SELECT
    COALESCE(p.[Code], 'X') AS [Code],
    COALESCE(d.[Status], 0) AS [Status]
FROM (VALUES
        (@id1, 'A'),
        (@id2, 'B')
    ) p([Id], [Code])
    FULL JOIN #data d ON d.[Id] = p.[Id];

Query execution plan is showing that optimizer's decision is to use FULL LOOP JOIN strategy, which seems appropriate, since both inputs have very few rows. One thing I noticed (and cannot agree), though, is that TVC rows are being spooled (see area of the execution plan in the red box).

Why optimizer introduces spool here, what is the reason to do it? There is nothing complex beyond the spool. Looks like it is not necessary. How to get rid of it in this case, what are the possible ways?

The above plan was obtained on

  Microsoft SQL Server 2014 (SP2-CU11) (KB4077063) - 12.0.5579.0 (X64)

Why optimizer introduces spool here, what is the reason to do it? There is nothing complex beyond the spool.

The thing beyond the spool is not a simple table reference, which could simply be duplicated when the left join / anti semi join alternative is generated.

It may look a little like a table (Constant Scan) but to the optimizer* it is a UNION ALL of the separate rows in the VALUES clause.

The additional complexity is enough for the optimizer to choose to spool and replay the source rows, and not replace the spool with a simple "table get" later on. For example, the initial transformation from full join looks like this:

Notice the extra spools introduced by the general transform. Spools above a simple table get are cleaned up later by the rule SpoolGetToGet.

If the optimizer had a corresponding SpoolConstGetToConstGet rule, it could work as you wish, in principle.

  How to get rid of it in this case, what are the possible ways?

Use a real table (temporary or variable), or write the transformation from full join manually, for example:

WITH
    p([Id], [Code]) AS
    (
        SELECT @id1, 'A'
        UNION ALL
        SELECT @id2, 'B'
    ),
    FullJoin AS
    (
        SELECT
            p.Code,
            d.[Status]
        FROM p
        LEFT JOIN #data d
            ON d.[Id] = p.[Id]
        UNION ALL
        SELECT
            NULL,
            D.[Status]
        FROM #data AS D
        WHERE NOT EXISTS
        (
            SELECT *
            FROM p
            WHERE p.Id = D.Id
        )
    )
SELECT
    COALESCE(FullJoin.Code, 'X') AS Code,
    COALESCE(FullJoin.Status, 0) AS [Status]
FROM FullJoin;

Plan for manual rewrite:

This has an estimated cost of 0.0067201 units, compared with 0.0203412 units for the original.

* It can be observed as a LogOp_UnionAll in the Converted Tree (TF 8605). In the Input Tree (TF 8606) it is a LogOp_ConstTableGet. The Converted Tree shows the tree of optimizer expression elements after parsing, normalization, algebrization, binding, and some other preparatory work. The Input Tree shows the elements after conversion to Negation Normal Form (NNF convert), runtime constant collapsing, and a few other bits and bobs. NNF convert includes logic to collapse logical unions and common table gets, among other things.


Postgres Foreign Data Wrappers for Data Migration. Do transactions hold across databases? I want to migrate some data from Postgres database A to database B. I found Foreign Data Wrappers that allow you to connect to a table in B while in database A. I have a table in A that I want to move to B and clear out the records in A.
insert into b_table select * from a_table;
delete from a_table;

Will a postgres transaction provide me with all expected error recovery in the event of a crash or disconnect between the two SQL lines even though I'm working with 2 different databases?

I tried an experiment where I used the Foreign Data Wrapper and executed SQL like I had above. In between the insert and delete lines I put a sleep and then killed the process while it was sleeping. No data was committed as a consequence of the insert until it actually hit the connection.commit() line.
So, to answer my own question, it should be safe.


Is conditional execution possible for SQL Server Agent job steps? Is conditional execution possible for SQL Server Agent job steps?

That is if Step 1 returns 1 I want to go to Step 2.

If Step 1 returns 2 I want to go to Step 3.

If Step 1 returns 3 I want to go to Step 4.

It looks like it is not possible because the only distinction available for step completion is Success or Failure but I figured I would check with the experts here.

I know I can achieve this logic in SSIS package but was wondering if it can be done within SQL Server Agent.

You can't configure the agent to act as you have described, but you might be able to achieve the desired result by building the logic into the code executed in steps 2, 3, and 4.  For instance you could use an IF statement at the start of step 2 to check the result of step 1, and only continue if the result was 1.  If the result is not 1, then it would move on to step 3, and so on.


drop table causes ORA-08103 with another one In my process I have 2 tables.

  target table, load table.

The target table holds the current data, and a lot of queries are fired against it.

When new data coming we use a FULL LOAD and the goal is to switch the current data that in the target table with the new data.

The new data exists in the load table.

In goal to do that we are using

  Exchange partition

Those are the steps:

create new load table with only 1 partition -all_rows (values less than maxvalue).
insert all the data from source to the load table by insert statement.
exchange partition of the all_rows (=all the new data) with the target table.

That way, we have the most new data, and there is no space that data is not available.
The problem start when I want to drop my load table after the exchange.
If someone send a query to the target table he can get exception:

ORA-08103: Object no longer exists.

Even the drop was only on the load_table. Same when load table truncate.

What does that mean?
Those two tables will be together forever?
Every truncate or drop I need to be afraid on the second one?
Why they are connected at all?

I'm using oracle 11g.

My actions:

CREATE TABLE tbl_ld
partition by range (col1) (partition all_rows values less than (maxvalue)
AS  SELECT * FROM tbl_tgt WHERE 1=2 ;

insert into tbl_ld
select * from tbl_temp;

alter table tbl_ld exchange partition all_rows with table tbl_tgt;

-- drop or truncate
drop table tbl_ld;
truncate table tbl_ld;

AskTom (answered by Connor McDonald) : ORA - 08103:Object No Longer Exists and an even more detailed explanation by Connor McDonald: A cool thing with EXCHANGE PARTITION (recommended by AskTom here).

TRUNCATE TABLE is a DDL statement, not a DML statement like delete. But what happens is a phenomena that is similar to a phenomena that can be caused by delete or update  statements.

If   delete or update statements are involved then we have

ORA-01555: snapshot too old: rollback segment number string with name "string" too small

There is also an AskTom article for ORA-01555

What happens:

Assume in your transaction you start a SELECT statement on a table. During this  select statement another transaction manipulates the data:

Either it deletes or updates  the table data (DML) and commits
or it truncates a partitions or the table or  exchanges a partition (DDL)

In this case your session that executes the select statement cannot find the data on its original place because the table has already changed. But it  is necessary to retrieve the table data in a consistent way, this means not need the data from the table from the point in time when the query started.
YOur session  it may be able to find the data in a rollback segment (in case of a DML  statement) or in the table whose segment you have exchanged with the partition (in case of exchange partition) or it is a segment that is not assigned to a table anymore but is still available and unaltered on disk (DDL statement).
If Oracle finds all the data it can continue with your select statement. If it does not find the data, either because the rollback segment where  already overwritten (DML  case)  or the segment was already modified or deleted (DDL case) then Oracle will throw an error.
in the DML case Oracle will throw an ORA-01555, in the DDL case it will throw a ORA-08103.

Connor McDonald writes in the first article referenced:

  Partitioned tables are the most common scenario to see this error, but by no means the only way.

  Anything that physically alters an underlying segment that is being queried can create the issue. Examples:

  truncate table
  alter table move
  alter index rebuild

Both articles are very instructive and I recommend you to read both (the second one is a video)

So how can you handle this problem?

simply repeat the action in case of this error (or rollback the transaction and repeat it)
maybe it helps if you wait some time before you drop the exchanged partition. Maybe this will decrease the frequency of this error.


Join Comma Separated Same Value create table dbo.CourseMaster
(
  CourseId char(2),
  CourseName char(3)
);

create table dbo.StudentMaster
(
  ROLLNO char(5),
  NAME varchar(10),
  ADDRESS varchar(20),
  Course varchar(100)
);

insert into dbo.CourseMaster values
('01', 'ABC'),
('02', 'DEF'),
('03', 'GHI'),
('04', 'JKL'),
('05', 'MNO'),
('06', 'PQR'),
('07', 'STU');

insert into dbo.StudentMaster values
('12345', 'RAM',   'RAM ADDRESS',   '01,02,02'),
('25695', 'HARI',  'HARI ADDRESS',  '02,06'),
('89685', 'JEFF',  'JEFF ADDRESS',  '03,05,06,07'),
('47896', 'DAISY', 'DAISY ADDRESS', '03');

Query 1:

select SM.ROLLNO,
       SM.NAME,
       SM.ADDRESS,
       (
       select ','+CM.CourseName
       from dbo.CourseMaster as CM
       where ','+SM.Course+',' like '%,'+CM.CourseId+',%'
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;

Actual Ouput:

| ROLLNO |  NAME |       ADDRESS |          Course |
|--------|-------|---------------|-----------------|
|  12345 |   RAM |   RAM ADDRESS |         ABC,DEF |
|  25695 |  HARI |  HARI ADDRESS |         DEF,PQR |
|  89685 |  JEFF |  JEFF ADDRESS | GHI,MNO,PQR,STU |
|  47896 | DAISY | DAISY ADDRESS |             GHI |

When I add course like 01,02,02 in student master it give below output:
ABC,DEF and it should give: ABC,DEF,DEF

Normalising StudentMaster.Course will help preserve duplicates. Since you've tagged sql-server-2012 sadly string_split() is unavailable. Happily, the internet is rife with pre-2016 workarounds. I'll choose this one arbitrarily since it was the top google hit for me.

Given creation of dbo.fn_split_string_to_column() from the linked SO post, the following should get you what you seek.

with normal_course as (
    select SM.ROLLNO,
           ss.column_id,
           ss.value as course_id
    from dbo.StudentMaster as SM
    cross apply dbo.fn_split_string_to_column(SM.Course,',') as ss
)
select SM.ROLLNO,
       SM.NAME,
       SM.ADDRESS,
       (
       select ','+CM.CourseName
       from normal_course as nc
       join dbo.CourseMaster as CM on CM.CourseId = nc.course_id
       where nc.ROLLNO = SM.ROLLNO
       for xml path(''), type
       ).value('substring(text()[1], 2)', 'varchar(max)') as Course
from dbo.StudentMaster as SM;

Try it on dbfiddle

Of course, my preference would also be to move the subselect down to a dedicated apply, but that's not strictly required.

Explanation

In your original query, the WHERE clause in your sub-select is fuzzy-matching the CourseId to the comma-aggregated string Course. This "mostly works" as you can see; but one of the (many) downsides of this approach is what you have seen - that each CourseId matches only once and in-line "duplicates" will not be aggregated multiple times.

Splitting out StudentMaster.Course into normalised values occurs in the CTE (with block) at the top of the query. This preserves the duplicates.

    select SM.ROLLNO,
           ss.column_id,
           ss.value as course_id
    from dbo.StudentMaster as SM
    cross apply dbo.fn_split_string_to_column(SM.Course,',') as ss

For the example duplicate row, this shows us...

+--------+-----------+-----------+
| ROLLNO | column_id | course_id |
+--------+-----------+-----------+
| 12345  | 1         | 01        |
| 12345  | 2         | 02        |
| 12345  | 3         | 02        |
+--------+-----------+-----------+

Now that the duplicate course_id is exposed as a separate data record, when you re-aggregate the corresponding CourseNames, the duplicates will be preserved unless you take steps to explicitly remove them.


Postgres occasionally slow to query I'm running a database with a few tables with large row counts (millions) but which are fairly small in data size. All the tables are append-only, so no deletes or updates ever happening, around 250 rows are added per minute. 99% of the time queries take less than 5ms. However, occasionally queries get terminated by Postgres for taking more than 200ms (my configured statement timeout).

I'm using default settings apart from autovacuum_analyze_scale_factor=0.05, autovacuum_vacuum_scale_factor=0.1 and autovacuum_naptime=30.

I'm running PostgreSQL 9.6.3 on AWS RDS on a t2.medium machine, with multi-az and connecting to it from AWS Lambda (Node.js 6.10) from within the same private VPC.

CPU stays under 7% at all times, disk read latency is less than 2 ms, number of concurrent connections rarely goes above 1. Average queries per second is quite consistent at around 20.

We've also tried turning synchronous_commit to off, but the same timeouts continued to happen.

Could this be an issue with the auto-vacuum process slowing queries even though I've got no updates or deletes?

Update: Having removed all writes to the database, we still see the same issue. So, no inserts, no updates, no deletes, just ~30 selects per second causes timeouts.

I unfortunately don't know much about postgres on AWS, my company is just starting to move that way, but when a vacuum runs here it does create disk io that could potentially slow down a query.  It could be that the locks are on resource contention more as opposed to Postgres Locks.


How to exclude rows containing null values within columns? This is probably not the ideal query to retrieve rows containing specified values within a column, but the solution below effectively returns the values specified in a new column-alias but includes NULL values.  How would the below query be constructed to exclude rows containing NULL values within the column alias?

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS "NewSalutation"
FROM student.student

I welcome alternative approaches-thanks!

SELECT * FROM (
SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
)
WHERE NewSalutation IS NOT NULL;

If that's your only section in the CASE statement, a logically equivalent query would be:

SELECT student_id,
       salutation,
       CASE
           WHEN salutation IN('Dr.') THEN 'Doctor'
       END AS NewSalutation
FROM student.student
WHERE salutation = 'Dr.';

... as anything other than Dr. will produce a NULL NewSalutation.


Am I doing a security breach if I am connecting a MS-Access front end (multiple computers) to a SQL Server back-end? I have a MS Access frontend that I would like to install on 5-7 computers, so they can access a SQL Server stored on a shared network drive:

If possible, is it safe and can my data be corrupted? Otherwise, how I have 5-7 users access the SQL Server simultaneously by using an interface?

Thank you very much for your time!

Yes you can use MS Access as a front end to SQL Server. Its quite common. I have done this myself in the past.

No, Access itself won't corrupt data.  However your Access application needs to be designed so that it respects SQL Server constraints and rules. Also how will your Application stop users from making simple mistakes, like deleting records if they're not permitted to.

The most common problem I had was dealing with concurrency.  When 2 clients are doing things at the same time such as inserting a record. Do you let Access generate the primary key? or do you let SQL Server manage it? In either case after you've inserted rows how do you ensure that each client is still dealing with the same row?

This is a consideration for anyone developing a multi user system. It just takes some time and thought.

Some advice: Make sure that you define the primary key in SQL Server. If you don't do this Access won't allow you to update records as it can't guarantee a lock on a single record. Primary keys and indexes also have a huge impact on performance.

Edit: I've just been thinking about your Question title a bit more. You mention "security breach". If security is a concern, You need to consider how users will connect to the database. Domain authentication? or SQL authentication, and how you log actions taken by users.


Postgres incomplete startup packet error I am attempting to install Postgres 9.3 on Ubuntu 14.04 and I am getting an irksome error on startup. Here's what I'm seeing:

$ sudo service postgresql restart
* Restarting PostgreSQL 9.3 database server
* Error: could not exec /usr/lib/postgresql/9.3/bin/pg_ctl /usr/lib/postgresql/9.3/bin/pg_ctl start -D /var/lib/postgresql/9.3/main -l /var/log/postgresql/postgresql-9.3-main.log -s -o  -c config_file="/etc/postgresql/9.3/main/postgresql.conf" : [fail]

So I checked the log file with this output:

 2015-01-05 21:50:05 EST LOG:  database system was shut down at 2015-01-05 21:50:03 EST
 2015-01-05 21:50:05 EST LOG:  database system is ready to accept connections
 2015-01-05 21:50:05 EST LOG:  autovacuum launcher started
 2015-01-05 21:50:06 EST LOG:  incomplete startup packet
 2015-01-05 21:51:22 EST ERROR:  syntax error at or near "exit" at character 1
 2015-01-05 21:51:22 EST STATEMENT:  exit;

"Incomplete startup packet" seems to be the culprit but  I am having trouble finding info out there on what might be going on, so I thought I would ask. Thanks in advance.

There are 3 different items in this question:

Incomplete startup packet occuring at server start is inconsequential, you may ignore it. Read Incomplete startup packet help needed (in pgsql-general mailing-list) for more.
syntax error at or near "exit" at character 1 means that a client issued exit as if it was an SQL statement.
The could not exec error when issuing service postgresql restart looks like a serious installation problem but it's contradicted by the log entry database system is ready to accept connections meaning that the server started up just fine.


Any advantage of creating table in temporary tablespace - Oracle My on-site DBA told me to create the particular table on temporary tablespace.Is there any advantage of such action?

I believe your DBA told you to use temporary tablespace for temporary data? If so using temporary tables have the following advantages:

DML on temporary tables does not generate redo. If one uses direct path INSERT (INSERT /*+ append */, CTAS) then undo is also not generated. On 12c it is possible to store UNDO in temporary tablespace as well!
after session crash Oracle will take care to remove your data
after instance crash temporary tables do not require recover

Of course I forgot something. :) People will add more advantages in comments.

While listing advantages I am not considering scenario when you have to preserve data between restarts.

Database Concepts about Temporary Tables


Determine Oracle session client character set? I know how the database characterset (NLS_CHARACTERSET in select * from v$nls_parameters;) and the client character set (the client environment setting NLS_LANG) interact.

What I can't find out however, is how or if I can determine, for an established session, what Oracle thinks the current client characterset is.

Is this possible at all?

Note: SELECT * FROM NLS_SESSION_PARAMETERS; does not include the character set (on 10g2).

To make absolutely clear what I'd like to accomplish:

NLS_LANG is set in client environment to an arbitrary value (for example GERMAN_GERMANY.WE8MSWIN1252)
Database application[*] starts and establishes a connection/session to the Oracle database.
Database application[*] wants to "ask" Oracle (not its OS environment) what the client character set is Oracle will assume.

[*]: If the db application is sqlplus, the example would look as follows:

...
sqlplus /nolog
connect user/pass@example
*magic command*;
   CLIENT CHARACTERSET = ...

Jack's note in his answer raises two important points:

With Oracle, who does the characterset translation. Is it the client-library code or is it done on the server side?
As it appears it is the client, the client would need expose this setting -- what the client lib/tool assumes this setting is. Is there any of the Oracle client libs/tools (sqlplus, OCI/OCCI, Pro*C, ...) that can be queried for what it thinks this setting is?

I am a little doubtful that this is exactly what you are looking for, but
host echo %nls_lang%;

ENGLISH_UNITED KINGDOM.WE8ISO8859P1

shows the client nls_lang environment variable on the client.
I don't think there will be a SQL query you can run to give the 'current' setting because AFAIK the server is not aware of what translation is done client-side, so any command to show the current setting will have to be native to the client - I used SQL Developer for the above command, but I assume it will work the same in SQL*Plus
--edit
from AskTom:

only the client knows their character set as well -- it is not available "in the
database"

and

the character set describes what is stored in database.
the client makes their desired translated to character know [sic] to the database via the NLS_LANG
settting.

If you were on 11.1+, you might have some joy with v$session_connect_info, because:

This information is pushed by OCI to the server ats login time.

But I discovered it would still depend on how you are connecting, eg from the JDBC Thin Driver you aren't using OCI and so the information isn't pushed


Rearranging Data in an MS Access 2007 table into a new table format/Result Output I have Access tables that have the following format:

--------------------------------------
|Name |Math|Science|Reading|Civics|
--------------------------------------
|Mike | A  |  C    |  A    | B    |
|John | B  |  A    |  A    | B    |
|Randy| A  |  C    |  A    | A    |
|Sasha| B  |  C    |  D    | A    |
|Max  | A  |  C    |  A    | A    |
|Jenn | A  |  A    |  A    | C    |
-----------------------------------

Is there any Access Sql code or Access VBA script that I can use so that I can convert (or output in a query/resultset) the above table in the below format?

 --------------------
|   Name      |Grade|
---------------------
|Mike Math    | A   |
|Mike Science | c   |
|Mike Reading | A   |
|Mike Civics  | B   |
|John Math    | B   |
|John Science | A   |
|John Reading | A   |
|John Civics  | B   |
|Randy Math   | A   |
 etc...

What I have found so far are the below 2 threads which shows how to bring in all the field names into an array or just basic output using VBA in Access. It's a start for what I am trying to accomplish (since in essence I will need to bring in the field names into the first column). I'll post back if I'm able to figure it out.

https://stackoverflow.com/questions/11548697/how-to-list-fields-name-in-table-in-access-using-sql

http://forums.asp.net/t/1143922.aspx

I'm wondering If I can do something like...

For each fieldname in table (where fieldname != Name)
Begin
select
Name + ' ' + Subject as Name,
(select Grade from table where Name = Name and Subject = Subject) as Grade
End

Yes, it can be done, using UNION ALL:

SELECT [Name] & " Math" AS col1, tab1.Math FROM tab1
UNION ALL
SELECT [Name] & " Science" AS col1, tab1.Science FROM tab1
UNION ALL
SELECT [Name] & " Reading" AS col1, tab1.Reading FROM tab1
UNION ALL
SELECT [Name] & " Civics" AS col1, tab1.Civics FROM tab1
Order BY 1;


update value in row based on value in another row I have a table with timestamped data.

t_stamp | col1 | col2 | col3

I want to update the value in say col3 based on values in col1 and col2 from the previous row in the table. By previous row I mean the row with the next lowest timestamp value. I also want to do this for every row in the table.

For example:

col3 = col1.prev + col2

I was able to use a window function to create a SELECT query to give me the desired values for col3

SELECT lag(col1) OVER (ORDER BY t_stamp ASC) + col2 AS col3
FROM table1

but this does not update the values in the table. Can I somehow apply this to the original table? Or is there a way to format an update query in the same way?

You just need to use the FROM clause along with the query you already have:

UPDATE test set col3 = prev_col1 + prev_col2
FROM (
  SELECT t_stamp,
      lag(col1) OVER (ORDER BY t_stamp ASC) prev_col1,
      lag(col2) OVER (ORDER BY t_stamp ASC) prev_col2
  FROM test) prev
  WHERE prev.t_stamp = test.t_stamp;


Bulk Insert Through Network Can someone help me with these?

BULK INSERT DATABESE01.dbo.TABLE01
FROM '\\COMPUTER01\FOLDER01\TextFile.txt'
WITH
(
    FIELDTERMINATOR = ' ',
    rowterminator = '\n',
    tablock
)

The error shows, couldn't open:

  Could not bulk insert because file '\SERVERNAME\FOLDERNAME\textFile.txt' could not be opened. Operating system error code 5(Access is denied.)

The path is on another computer on the network.

I'm going to make a leap of faith and assume you connect to SQL Server using Windows Authentication. In such a case you have a Kerberos  double hop: one hop is your client application connecting to the SQL Server, a second hop is the SQL Server connecting to the remote \\COMPUTER01. Such a double hop falls under the restrictions of Constrained Delegation and you end up accessing the share as Anonymous Login and hence the Access Denied.

To fix the problem you need to enable constrained delegation for the SQL Server service account. This blog How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied) has an example of how to do it, and I really do hope that the step on how to 'enable unconstrained delegation' is just a typo as unconstrained delegation is just plain evil.

In the case I jumped the gun and you are logging into SQL Server using SQL Authentication you will need to create a credential for your SQL login and use that to access network resources. See CREATE CREDENTIAL.


When to use identity increment? Many times I face these three choices to work with IDs:

Using an auto-increment identity column?
Suggesting a new codification to use (eg, for students: first 4 position is the year when the student joins the university, 5th position is 0 for females 1 for males, the last 4 position is a sequential number)
Don't add any extra column and use only a unique information that already exists (eg: name (of course must be sure that's unique (there are no two possible entries with the same name)))

Question is: which choice do I make, and according to what criteria?

This is an ongoing debate that many face, and experts will give good arguments for either of these.  What your question boils down to is a surrogate vs. natural key (and option two seems to be a half-surrogate half-natural key).

As for your third option, oftentimes I see people do whatever they can to find a unique candidate for a primary key constraint.  Take you for instance:  name is your example key.  That is a notoriously bad candidate for a key, as there is no uniqueness guaranteed with that.  Think "John Smith".  Not only that, but having that as a key will bloat your foreign key relationship, as when you go to reference that primary key in a relationship table then you will have that same data type and column width.  Think nvarchar() as opposed to a narrow data type (i.e. int).

As for your second option, it sounds like you have the ability to create a unique value (by your description, able to fit in an int column).  Where would you plan on doing this?  This would either be logic in the database or in the application.  In my opinion, I like to keep entities like keys isolated to the data tier. This "id" would most likely be used for normalized data.  That should be an abstraction to the application (in my opinion).  So now my reasoning brings me to either a database-generated unique value (your option two) or an identity value (your option one).  Those both have the same result (as far as I can see it), therefore why go through the trouble and the development to generate your own when you can just have an IDENTITY column?

Again, others may and probably will have different and equally objective answers for going with another alternative, but my vote would be for the IDENTITY column for a unique, surrogate key.


Costly HASH MATCH Aggregate I have a view,which is slow when i query a simple select statement with a where clause.The where clause column is indexed(Non-clustered index)
Here is the view and the plan.
execution plan

CREATE VIEW [dbo].[CurrentIncidentStatus]
AS
SELECT [incidentDetails].[IncidentStatus], [incidentDetails].[IncidentStatus_FieldValue], [incidentDetails].[IncidentStatus_Description], Report.Id ReportId, Form.Id FormId
FROM
[dbo].[IncidentDetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[IncidentDetailsPages] detailsPage WITH (NOLOCK)
ON incidentDetails.PageId = detailsPage.Id
INNER JOIN Form WITH (NOLOCK)
ON detailsPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
ON Form.ReportId = Report.Id
LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
ON detailsPage.Id = supplement.PageId
INNER JOIN
(
    SELECT ReportId, Max(FormNumber) RecentFormNumber FROM
    (
        SELECT Report.Id ReportId, FormId, COALESCE(SupplementNumber, '0000') formNumber
        FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
        INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
        INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id
        INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId
        LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId) FormNumbers
        GROUP By ReportId) RecentForm
ON Report.Id = RecentForm.ReportId AND
RecentForm.RecentFormNumber = COALESCE(supplement.SupplementNumber, '0000')
GO

I analyzed the plan in Sentry Plan explorer and learned that the HASH JOIN Aggregate is the costly operation in the plan.
I am trying to avoid/remove that HASH JOIN someway.?
If any of you have experienced similar situation please give some suggestion.

Additional Info:

The plan says Hash key is built on Table Report and column Id.
In-fact the Idcolumn is the primary key in the Report table ,so a clustered index is present also a Non-clustered index is created for Id column.
Still why Hashing is required for Report.Id?

Here is the screenshot of the HASH MATCH Aggregate operation.

Info:
When i select TOP 100 or 1000 it doesn't show HASH MATCH (Aggregate).

Thanks

This is not a full answer, but I need more room than a comment allows, so...

First, you should consolidate your query - you have an unnecessary step in it:

INNER JOIN (
    SELECT Report.Id ReportId, Max(COALESCE(SupplementNumber, '0000')) RecentFormNumber
    FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
    INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
    INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id
    INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId
    LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId
    GROUP By Report.Id
    ) RecentForm

Second, you should get rid of the redundant use of the Report table, ending up with a query like this:

CREATE VIEW [dbo].[CurrentIncidentStatus]
AS
SELECT [incidentDetails].[IncidentStatus],
    [incidentDetails].[IncidentStatus_FieldValue],
    [incidentDetails].[IncidentStatus_Description],
    Report.Id ReportId,
    Form.Id FormId
FROM [dbo].[IncidentDetailsPage_Incident] incidentDetails WITH (NOLOCK)
INNER JOIN [dbo].[IncidentDetailsPages] detailsPage WITH (NOLOCK)
    ON incidentDetails.PageId = detailsPage.Id
INNER JOIN Form WITH (NOLOCK)
    ON detailsPage.FormId = Form.Id
INNER JOIN Report WITH (NOLOCK)
    ON Form.ReportId = Report.Id
LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
    ON detailsPage.Id = supplement.PageId
INNER JOIN (
    SELECT Report.Id ReportId, Max(COALESCE(SupplementNumber, '0000')) RecentFormNumber
    FROM dbo.IncidentDetailsPages detailPage WITH (NOLOCK)
    INNER JOIN Form WITH (NOLOCK)
        ON detailPage.FormId = Form.Id
    INNER JOIN Report WITH (NOLOCK)
        ON Form.ReportId = Report.Id
    INNER JOIN dbo.IncidentDetailsPage_Incident incident WITH (NOLOCK)
        ON detailPage.Id = incident.PageId
    LEFT OUTER JOIN dbo.IncidentDetailsPage_Supplements supplement WITH (NOLOCK)
        ON detailPage.Id = supplement.PageId
    GROUP By Report.Id
    ) RecentForm
    ON Form.ReportId = RecentForm.ReportId
    AND RecentForm.RecentFormNumber = COALESCE(supplement.SupplementNumber, '0000')
GO

With these simplifications, please check the query plan again.


Creating calculated column I want to create a rating weight and I need do calculate AVG, then find COUNT and multiply both to get weight. I have something like that:

SELECT post_id, COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END) AS voters,
AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END) AS average FROM wp_imdb_rating
GROUP BY post_id
ORDER BY voters * average DESC
LIMIT 0, 100

But it says:

  #1247 - Reference 'voters' not supported (reference to group function)

I can order by average and voters separately, but I can't combine them with any formula. How can I use a formula in order clause that includes values from AVG and COUNT?

Thanks.

It seems to be some old limitation of the optimizer maybe, aliases to aggregating expressions are not allowed in more complex expressions in ORDER BY.

You can get around it by using the full expression instead of the alias:

ORDER BY
    COUNT(CASE WHEN vote > 0 THEN vote ELSE NULL END)
  *
    AVG(CASE WHEN vote > 0 THEN vote ELSE NULL END)


MySQL Logging Options I want to know what logging options are available to me in MySQL; I find a lot more results about binary logging than I do about general/error logging and I'm not sure what options are available to me for such logs.

Could anyone tell me a list of the my.cnf variables I can set to get different types of logs?

Cheers!

Chris

Well, each log is for a different purpose.

Binary logs - Required for acting as a replication master and useful for auditing or point in time restores from backups
Relay logs - Required for acting as a replication slave. They are only stored until applied to the slave and then removed automatically
General query log - All the queries executed by the server
Slow query log - Queries that are 'slow'. Based on executing time and/or index usage
Innodb logs - These are internal to the innodb engine and not a classic log for you to look at. It stores the data post the current checkpoint and so it can be merged into sequential writes rather then random writes all over the table.
Error logs - Just stderr from the mysqld process

What are you after? Queries run or slow queries or?


Convert CreateLSN of DBCC LOGINFO into time If I run the undocumented  DBCC LOGINFO against my database one of the fields returned is CreateLSN.  This is the LSN in the format 1629000000070500011 at the time the VLF was created.  Now it is a long time later and the auto grow values of Disk Usage report are long gone.  I want to convert the LSN to a date/time

For a one off solution I tried this

select sys.fn_cdc_map_lsn_to_time ( convert(binary,1629000000070500011) )

But I get the error

  Invalid object name 'cdc.lsn_time_mapping'

I feel like I am missing something simple, but it is alluding me.

sys.fn_cdc_map_lsn_to_time (Transact-SQL)

The LSN is incremented each time a modification is made to the database, irrespective of when the modification was made.

sys.fn_cdc_map_lsn_to_time relies on the current database having Change Data Capture enabled, which automatically creates several objects in the cdc schema.  The definition of fn_cdc_map_lsn_to_time shows this:

create function [sys].[fn_cdc_map_lsn_to_time]
(
    @lsn        binary(10)
)
returns datetime
with returns null on null input
as
begin
    declare @lsn_end_time datetime

    select @lsn_end_time = tran_end_time
    from [cdc].[lsn_time_mapping]
    where start_lsn = @lsn

    return @lsn_end_time
end

So, unless the current database has the cdc.lsn_time_mapping object (I'm unsure if this is a view or a table) present, the function will not work.

The notes in the docs page you linked says:

  This function can be used to determine the time that a change was committed based upon the __$start_lsn value returned in the row of change data.

I'm extrapolating that the function actually returns pre-recorded LSN-to-timestamp tuples, instead of actually converting an LSN into a timestamp.

@RemusRusanu has an excellent writeup about LSNs on his blog


Create event trigger as non-superuser in Postgres Is it possible to give a role or a set of attributes to a non-superuser in postgres that would allow the user to create an EVENT TRIGGER?  Simple example of what (obviously) doesn't work in psql:

=> CREATE OR REPLACE FUNCTION do_nothing()
->   RETURNS event_trigger
->  LANGUAGE plpgsql
->   AS $$
$> BEGIN
$>   SELECT 1;
$> END;
$> $$;
CREATE FUNCTION

=> CREATE EVENT TRIGGER do_nothing_on_ddl_change ON ddl_command_start
->    EXECUTE PROCEDURE do_nothing();
ERROR:  permission denied to create event trigger "do_nothing_on_ddl_change"
HINT:  Must be superuser to create an event trigger.

The root reason for needing this is that I'm using an RDS-backed Postgres instance, and attempting to setup streaming of that database (data and schema changes) into a Redshift instance.  Using Amazons DMS services, setting this up is up is fairly straightforward - however, there's a catch: Starting up the streaming process creates a DDL audit table in the database, as well as an event trigger that captures any DDL changes.  The trigger writes the changes to the audit table, and those changes are streamed into redshift.

Here's where the problems lie:

A non-superuser (lets call it app_production, since this is part of a rails app) is responsible for making DDL changes to the db.
If we use the non-superuser to create the data streaming task in AWS, that user is unable to create the required event triggers to stream schema changes.
If we use the superuser to create the streaming task, then future schema migrations made by app_production fail, as they attempt to write to an audit table created by the superuser.

Any ideas?

I was just looking into this topic, found this question, and another one with the answer. I figured I'd link back to the original answer, and the RDS docs.

The short answer is:

Yes you can, using the RDS master account.
You have to delete the event triggers before upgrading between major versions.

Details:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.FeatureSupport.EventTriggers


Do not replicate Delete operations AWS DMS I am using AWS DMS to replicate ongoing changes with SQL Server as a source and target endpoint. The tasks are running and replicating data with low latency. However, I need the tasks configured not to replicate DELETE operations from the source database.

I did read the whole documentation of the tool and I wasn't able to find such a configuration option to exclude DELETE statements for replicating. There is only one place in the whole user guide where it states that if one is performing a Full Load plus CDC or a CDC-only task, it is recommended the migration to be paused and secondary indexes to be created that will support filtering for update and delete statements. But no more, no further explanation.

Any help or experience is highly appreciated, please.

I've recently encountered a similar problem (my source endpoint is SQL Server and target endpoint is Redshift) and after a futile search through AWS documentation, I've contacted AWS support. They have confirmed that there is no direct way to filter out replication of DELETE statements while using a single DMS replication task.

However, they have suggested a workaround, which I think might also solve your issue.

Basically, you need to set up a migration task from SQL Server to S3, which serves as an intermediary step, and then from S3 to the final destination (which in your case is again the SQL Server).

Reason for this is that when you are setting up the target endpoint, you can specify an Extra Connection Attribute (ECA) to modify its behaviour. One of these ECAs can actually filter out all UPDATE and DELETE statements, but strangely, it is available only when creating an S3 target endpoint.

Set up a migration task from SQL Server to an S3 bucket, which will serve as an intermediary. As a target endpoint, choose an S3 bucket and at the bottom of the endpoint setup screen at the 'Endpoint-specific settings', you specify an ECA cdcInsertsOnly=true; This way, only INSERT statements are written to the output file (either .csv or .parquet) during the CDC load. [1]
Set up a second DMS task to migrate the now filtered data from S3 to the target endpoint of your choice. You will need to create a source endpoint for the S3 bucket from previous steps. You will also need to provide a JSON with external table definition so that DMS is able to replicate the data correctly. [2]

Of course, it is possible that this inability to filter out DELETE statements only applies to the Redshift target endpoint and there is some easier way to solve your problem. However, the evidence suggests that there is indeed no direct way since the ECA cdcInsertsOnly is available only in S3 target endpoint and not in SQL Server nor Redshift.


Adding columns from joined table slows down query incredibly I have a fairly complex query that joins several tables and views. It executes pretty quickly (~9 seconds for 2200 rows), except when I add a column from a view that is already joined in the query the query seems like it will never finish - I left it running for 33 minutes once and it had not completed.

SELECT
    table1.col1,
    table2.col2,
    view1.col3,
    view2.col4

FROM
    table1
    INNER JOIN table2 ON table1.id = table2.id
    LEFT OUTER JOIN view1 ON view1.id = table1.id
    LEFT OUTER JOIN view2 ON view2.id = view1.id
    LEFT OUTER JOIN view3 ON CONVERT(CHAR(8),view1.DateRequired,112) + LEFT(view2.ItemNumber, 4) = view3.UID

The above will run fine, but when I add any column from view3 into the SELECT list the query slows down to a crawl. I'm unable to get the full result set but if I limit the result to 50 rows it takes around 1 minute to run to completion compared to roughly 1 second with the original query.

Now, I know that the join for view3 is pretty complicated; it converts one value from a date to a string and then concatenates this to a value from another table and uses this resulting concatenated blob of info to join against the same style code in view3. However, I would expect that if this were a problem with the join itself then it would slow down the query when the view itself is joined, not just when columns from that view are added into the SELECT list.

I managed to run an execution plan when the extra column is added and it is absolutely colossal. I did manage to find one task that has a cost of 51%: RID Lookup (Heap). I'm not au fait with execution plans so not sure what this means or how to resolve it.

UPDATE

Using Scott's answer I created a non-clustered index on the row that was causing the expensive RID lookup. It improved performance quite a lot - down from over a minute to around 30 seconds to return the 50 row limit.

I think Nic is right in his suggestion that the datatype conversion from date to char(8) in the join condition is causing the bulk of the cost when querying. Going to work on another way to join view3.

A RID Lookup is a lookup into a heap table using a Row ID. The Row ID is included in a non-clustered index in order to find the rest of a table's data in the heap table. Since a heap table is a table without a clustered index and is sorted unordered, a Row ID is required for the correlation.

My guess is that view3.UID is found easily via a non-clustered index with that column.  When you actually start asking for other columns (by specifying them in the SELECT list that are not part of that non-clustered index, Sql Server has to go find the other data items in the unordered heap - that's the RID Lookup - which can be quite expensive depending on the amount of data you're dealing with.

You 'might' see improved performance if you can identify the 'source' tables referenced in view3 and possibly 'including' your SELECT columns in supporting indexes on those source tables - this is called a 'covering index' in that the index is able to 'cover' or retrieve all of the information you're asking for without having to go elsewhere (clustered-index or heap)

This link has some good information on RID Lookups.

Here is a brief summary of the main points of the link:

  When you find an RID Lookup in a query plan, it’s a symptom. It
  indicates a database schema that breaks several rules of thumb. Those
  rules-of-thumb are:

  Each table should have a clustered index (of course there are    exceptions but we’re dealing with rules-of-thumb here).
  A non-clustered index has been created indicating that someone somewhere identified an ordering on one or more columns that made
  sense for that data.
  There is at least one query (i.e. the one that generated the RID Lookup) that needs columns that are not covered by the non-clustered
  index.

Something else to 'try' - use a Common Table Expression (CTE) in an attempt to force Sql Server to 'materialize' view3.  Before your main SELECT, add the CTE - this 'assumes' that no more than 2147483648 (max int value) could be returned - adjust as needed.

with View3Materialzed as
(
select top 2147483648 UID from view3 order by UID
)

Then, use the CTE View3Materialzed in the regular joins.  I have used this technique with some success.  It may or may not help you.


Mysql GROUP BY is really slow on a simple view I have these tables.

CREATE TABLE `movements` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `movementType` tinyint(3) UNSIGNED NOT NULL,
  `deleted` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements`
  ADD PRIMARY KEY (`movementId`),
  ADD KEY `movementType` (`movementType`) USING BTREE,
  ADD KEY `deleted` (`deleted`),
  ADD KEY `movementId` (`movementId`,`deleted`);

CREATE TABLE `movements_items` (
  `movementId` mediumint(8) UNSIGNED NOT NULL,
  `itemId` mediumint(8) UNSIGNED NOT NULL,
  `qty` decimal(10,3) UNSIGNED NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `movements_items`
  ADD KEY `movementId` (`movementId`),
  ADD KEY `itemId` (`itemId`),
  ADD KEY `movementId_2` (`movementId`,`itemId`);

and this view called "movements_items_view".

SELECT
movements_items.itemId, movements_items.qty,
movements.movementId, movements.movementType
FROM movements_items
JOIN movements ON (movements.movementId=movements_items.movementId AND movements.deleted=0)

The first table has 5913 rows, the second one has 144992.

The view is very fast, it loads 20 result in PhpMyAdmin in 0.0011s but as soon as I ask for a GROUP BY on it (I need it to do statistics with SUM()) es:

SELECT * FROM movements_items_view GROUP BY itemId LIMIT 0,20

time jumps to 0.2s or more and it causes "Using where; Using temporary; Using filesort" on movements join.

Edit: Bypassing the view.

I also run via phpMyAdmin this query to try to not use the view:

SELECT movements.movementId, movements.movementType, movements_items.qty FROM movements_items JOIN movements ON movements.movementId=movements_items.movementId GROUP BY itemId LIMIT 0,20

And the performance is basically the same.

Edit. Here is the EXPLAIN

id  select_type     table               type    possible_keys                    key            key_len     ref                        rows     Extra
1   SIMPLE          movements           index   PRIMARY,movementid               movement_type  1           NULL                       5913     Using index; Using temporary; Using filesort
1   SIMPLE          movements_items     ref     movementId,itemId,movementId_2   movementId_2   3           movements.movementId       12       Using index

Any help appreciated, thanks.

First things first: Does the query continue to perform slowly if you bypass the view and run the underlying query with the GROUP BY? In my experience, MySQL is notoriously bad with views that are used with anything except SELECT statements.

If that fixes the problem, then just create a second view with the actual information you want to query (that is, with the GROUP BY included in the view).

If the problem persists, then there's a few things that can be improved.

Add primary key

You have no primary key on the movement_items table. Add one. If each combination of movementId and itemId can only have on entry, then make the PK a composite key out of those columns. Otherwise, you can add a surrogate autoincrement primary key.

Add index

Try the following index:

movement_items: (itemId,movementId,qty)

Provide the actual query you want optimized

Query optimization is not a piecemeal process. You cannot improve one query, and then expect that improvement to necessarily carry through to related queries.

You say you want to do a SUM, well, show us that SUM query. What exactly you are SUMming will require different optimizations than a SELECT * will.

Also, provide your EXPLAIN plans. You mention that you've run them, but don't provide that extremely useful information to us.


Create result set with all records from LEFT table, and from the RIGHT table only bring records that do no exist in left already I'm having a bit of trouble wrapping my head around a way to make this JOIN work.

Postgres 11.5

I have two tables:

Table1:
customer_id
date
(various other fields)

Table2:
customer_id
date
(various other fields)

The tables are not the same, but the customer_id's and dates are in the same format so will be able to be joined.

I am trying to create a result set that contains both of the tables combined together (all columns) but with the following logic applied:

All data from Table1
Only records from Table 2 that DO NOT exist in Table 1
Joined on customer_id and date

My end goal is that I am trying to create a stacked area chart with this result set, but there are situations where a customer could be both in Table 1 and Table 2, so for a single given day, if someone exists who is like that, I don't want to double count that individual.

Appreciate the help!

Assuming all the columns are compatible:

SELECT * from table1
UNION ALL
SELECT table2.* from table2 left join table1 using (customer_id, date)
    where table1.customer_id is null

If not all the columns are compatible, you will have to do a bit of tedious manipulation of the select lists, rather than using *.


Database design for surveys in MySQL I'm trying to design an app where users take a new survey everyday (that's not the main part of app but it's an important part). I've never designed a database before so what I'm trying to do might not make any sense.

I'm using MySQL and I have a table for user information (user_id, name, password, etc). What I plan on doing is making a new table for each new survey and linking it with the user_id. So basically, I would have one table having a relationship with hundreds of other tables.

Is that how it would work? I thought about adding a column to the user table for every survey but I read that it's bad design to add columns often.

Make one table for all surveys. Don't make a new table for each survey.

Also, make sure you hash and salt your passwords with bcrypt.


How does mysql transaction works? I already know how to use transactions, what I want to know is how MySQL transaction handles data being processed...

For example, consider the following:

    1. I have 2 databases (db1 and db2) and both databases are geographically far from each other, more like cities away.
    2. db1 has a table namely, tbl_orig
    3. db2 has a table namely, tbl_backup
    4. I want to use MySQL transaction when copying the table from db1 (tbl_orig) into db2 (tbl_backup) so that in case of a power outage or anything that would interrupt the process, it would prevent my database to be in an unstable state.

Say I use the following commands below:

    mysql> start transaction;
    mysql> insert into db2.tbl_backup select * from db1.tbl_orig;
    mysql> commit;

In the above sql statements, in between the "start transaction" and the "commit" lines... what exaclty is happening to the rows indicated in the insert statement?

Does all of the rows affected by the insert statement from db1 gets transfered into db2 immediately and are waiting for the commit line to execute in order to make the insert official?

Or, are the rows affected by the insert statement are still inside db1 and are just waiting to be transfered into db2 once the "commit" command executes?

I wanted to know this facts because I will be copying data from a database through the internet and I'm worried that I might lose some data in the process... Any help would be greaty appreciated.

Does all of the rows affected by the insert statement from db1 gets
  transfered into db2 immediately and are waiting for the commit line to
  execute in order to make the insert official?

Transafer is made immediatly, but other sessions will not see inserted data until commit (depends on transaction isolation level)

  Or, are the rows affected by the insert statement are still inside db1
  and are just waiting to be transfered into db2 once the "commit"
  command executes?

Data are not waiting for commit, just storage engine wait for commit to make changes persisted.

  I wanted to know this facts because I will be copying data from a
  database through the internet and I'm worried that I might lose some
  data in the process... Any help would be greaty appreciated.

Every TCP based connection guarantee data integrity.

You should use replication instead of this approach


Table/Column Character Encoding and Indexes Does table/column character encoding have any impact on how the MySQL optimizer chooses join indexes?

I have a table that have the same columns and indexes on two servers, one server does a eq_ref under type and the other does ALL

Only difference I see is the character encoding. Could that be the cause query not using an index? In the database that isn't performing, one table is latin1 and the joining table is utf8.

1st Server

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, tx, , ALL, , , , , 234587263, 100.00, Using where; Using join buffer (Block Nested Loop)

2nd Server

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
1, PRIMARY, tx, , eq_ref, PRIMARY, PRIMARY, 198, db1.log.hash, 1, 100.00,

I found my answer here.  Quick answer is - they need to be the same collation/encoding to use an index.
https://stackoverflow.com/questions/2266241/utf-8-vs-latin1-mysql-indexes-not-used-on-utf-8


JSON array format in MySQL not working? What is the proper way to do a JSON data array in MySQL?

Following the JSON.org example http://json.org/example.html does not work:

 insert into zz_TEST_ObsJSON (jsonData) values ('{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}');

  ERROR 3140 (22032): Invalid JSON text: "The document root must not follow by other values." at position 36 in value for column 'zz_TEST_ObsJSON.jsonData'.

For giggles, tried putting it all in another { }:

insert into zz_TEST_ObsJSON (jsonData) values ('{{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}}');

  ERROR 3140 (22032): Invalid JSON text: "Missing a name for object member." at position 1 in value for column 'zz_TEST_ObsJSON.jsonData'.

This other method that kind of looks like geoJSON does not either:

insert into zz_TEST_ObsJSON (jsonData) values ('[["Test1":{"Val1":"37", "Val2":"25"}],["Test2":{"Val1":"25", "Val2":"27"}]]');

  ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 10 in value for column 'zz_TEST_ObsJSON.jsonData'.

insert into zz_TEST_ObsJSON (jsonData) values ('["Test1":{"Val1":"37", "Val2":"25"}],["Test2":{"Val1":"25", "Val2":"27"}]');

  ERROR 3140 (22032): Invalid JSON text: "Missing a comma or ']' after an array element." at position 9 in value for column 'zz_TEST_ObsJSON.jsonData'.

Are you attempting to insert a JSON array with two objects, Test1 and Test2?

If so, then you need to insert the data as an array:

JSON array:

[{
  "Test1": {
    "Val1": "37",
    "Val2": "25"
  }
}, {
  "Test2": {
    "Val1": "25",
    "Val2": "27"
  }
}]

Your INSERT statement would look similar to the following (note the wrapping brackets around the JSON objects):

INSERT INTO zz_TEST_ObsJSON (jsonData) values ('[{"Test1":{"Val1":"37", "Val2":"25"}},{"Test2":{"Val1":"25", "Val2":"27"}}]');