How can I write a proper query to JOIN all of these tables without duplicates? Let's say I have four tables:

TABLE:    players
COLUMNS:  id, first_name, last_name

TABLE:    passing_stats
COLUMNS:  id, year, passing_yards (several other passing columns)

TABLE:    rushing_stats
COLUMNS:  id, year, rushing_yards (several other rushing columns)

TABLE:    receiving_stats
COLUMNS:  id, year, receiving_yards (several other receiving columns)

Let's say Michael Vick has an id of 100.  I want to get his full name and all of his stats for each year (passing, rushing, and receiving).

I don't want any duplicates, meaning that rushing stats for 2011 should appear in the same row as passing stats for 2011.

What is the most elegant way to write this query?  Thanks.

The following will work in Postgres. Test here: SQL-Fiddle, postgres-test. SQL-Server does not have NATURAL JOIN and MySQL has NATURAL but doesn't have FULL joins:

SELECT
    id,
    first_name,
    last_name,
    year,
    passing_yards,
    rushing_yards,
    receiving_yards
  FROM
      players p
    NATURAL LEFT JOIN
      ( passing_stats pas
      NATURAL FULL JOIN
        rushing_stats rus
      NATURAL FULL JOIN
        receiving_stats rec
      )


Extract rows of a table, that include less than x NULLs I am working with a SQL Server database, which includes a lot of NULLs.
To analyse my data, I want to extract all rows of the database table, that include less than x NULL marks (e.g. x=2).

My database is similar to this structure:

 c1        c2      c3      c4        c5
-----------------------------------------------------
2          3       NULL    1         2
2          NULL    NULL    1         2
2          3       NULL    NULL      2
NULL       3       NULL    1         NULL
2          3       NULL    1         2

I tried the query, which doesn't return an error, but no rows are selected:

SELECT * FROM test123
WHERE ((ISNULL(c1,1) + ISNULL(c2,1) + ISNULL(c3,1) + ISNULL(c4,1) + ISNULL(c5,1)) < 2);

I expect this query to return the 1st and the fifth row, but the result contains 0 rows.

I can't test the following code, because I don't have the rights to write on the database, but here is a (pseudo-) code for creating a table like mine:

CREATE TABLE test123(
    c1 float,
    c2 float,
    c3 float,
    c4 float,
    c5 float
) GO
INSERT test123(c1,c2,c3,c4,c5)
VALUES (2,3,NULL,1,2),
       (2,NULL,NULL,1,2),
       (2,3,NULL,NULL,2),
       (NULL,3,NULL,1,NULL),
       (2,3,NULL,1,2);

Permissions to create a table in the current database shouldn't preclude you from creating one you can work with. You can just create a #temp table:

CREATE TABLE #test123(
    c1 float,
    c2 float,
    c3 float,
    c4 float,
    c5 float
);

INSERT #test123(c1,c2,c3,c4,c5);
VALUES (2,3,NULL,1,2),
       (2,NULL,NULL,1,2),
       (2,3,NULL,NULL,2),
       (NULL,3,NULL,1,NULL),
       (2,3,NULL,1,2);

To see why ISNULL isn't effective here, run this query:

SELECT ISNULL(c1,1), ISNULL(c2,1), ISNULL(c3,1), ISNULL(c4,1), ISNULL(c5,1)
  FROM #test123;

You've given every column in every row a value. So now you're evaluating the SUM of inflated values, and erroneously evaluating a property of the actual value (what happens when one of the values is negative?), instead of evaluating the COUNT of values that either are NULL or are NOT NULL.

It's more code but a simple way to address this is:

SELECT * FROM #test123
  WHERE CASE WHEN c1 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c2 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c3 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c4 IS NULL THEN 1 ELSE 0 END
      + CASE WHEN c5 IS NULL THEN 1 ELSE 0 END < 2;


Tokudb storage engine as mysql-cluster storage engine Can we use tokudb storage engine as mysql-cluster storage engine? If can, how? I search over google and found nothing

I think you are mixing concepts here: Mysql Cluster (assuming you are referring to MySQL NDB Cluster) requires the NDB engine for tables that are part of the cluster. In other words, NDB is the cluster, and MySQL Server, on the SQL layer, requires to use the NDB Cluster storage engine in order to access the data on the cluster.

TokuDB is an on-disk compressed format for MySQL optimized for large datasets. While you may be (or maybe not) able to install this engine in the MySQL NDB Cluster server distribution and use it locally to the node, it will never be able to be used as part of the distributed data, as that requires NDB.

There seems to be support of TokuDB by Galera in MariaDB Cluster 10, providing HA to TokuDB, but not the automatic sharding that NDB provides. I have not tested it, though.

Edit: Tim has told me on the comments that TokuDB is not supported by the Galera protocol in MariaDB (or at least not yet), only MyISAM (partially) and InnoDB, so not an option currently. So probably the only option for HA and TokuDB nowadays is traditional replication.


Super slow MariaDB 5.5 count(*) on large simple table I'm doing some testing on a dedicated mediocre server (Core 2 2G ram). Before I upgrade to 8G ram, I am concerned something is very wrong here.

Simple queries have normal performance:

MariaDB [testing]> select count(*) from domains where domain like 'super%';
+----------+
| count(*) |
+----------+
|    10868 |
+----------+
1 row in set (0.26 sec)

MariaDB [testing]> select count(domain) from domains where domain like 'slow%';
+---------------+
| count(domain) |
+---------------+
|           582 |
+---------------+
1 row in set (0.07 sec)

However count(*) and count(domain) are ridiculously slow:

MariaDB [testing]> select count(domain) from domains;
+---------------+
| count(domain) |
+---------------+
|      12802537 |
+---------------+
1 row in set (59 min 45.86 sec)

Explain claims it is using INDEX, yet it still shows full table scan.

MariaDB [testing]> explain select count(domain) from domains;
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
| id   | select_type | table   | type  | possible_keys | key    | key_len | ref  | rows     | Extra       |
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
|    1 | SIMPLE      | domains | index | NULL          | domain | 120     | NULL | 12762541 | Using index |
+------+-------------+---------+-------+---------------+--------+---------+------+----------+-------------+
1 row in set (0.00 sec)

Schema:

CREATE TABLE `domains` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `domain` char(120) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB AUTO_INCREMENT=14782143 DEFAULT CHARSET=latin1;

 MariaDB [testing]> desc domains;
+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| domain | char(120)        | NO   | UNI |         |                |
+--------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

my.cnf is pretty much default, except for

innodb_buffer_pool_size = 1600M

I am using deadline scheduler. No other queries are running. Nothing else is using the disk. Linux is not swapping.

Aside from ditching InnodDB, is there something else I can try?

It's reading the entire 12M rows in the index.  Yes, it is doing an index scan, not a table scan, but that is really the table (only 2 columns), then the index is as big as the data.

Do not use CHAR(...) unless the data is constant length.  I suspect the average length for a "domain" is somewhere around 20.  Changing to VARHAR(120) will shrink the data and the index by a factor of maybe 5.  This shrinkage will probably speed up the full scan by a factor of 5, and help the other queries some.

What was the setting of innodb_buffer_pool_size?  It should not have been more than about 500M.  The data is about 2GB, as is the index.  So you had to read most or all of the index from disk.  That is, the buffer_pool could not have cached it all, so you were I/O-bound.

Switching to 8GB of RAM and increasing innodb_buffer_pool_size to, say, 5500M, will perhaps let it cache the entire index, thereby running the full query in 6 seconds instead of 60.  Add to that, my suggestion about CHAR, and it might go below 2 seconds.


Database User not reported as orphaned after Windows login dropped I have dropped a SQL Server Windows Login. I then ran the below code to check for orphaned database users. However, the database user corresponding to the dropped Windows login does not appear as an orphaned user.

Why would this be?

EXEC sp_change_users_login @Action = 'Report';

Thomas has explained why that stored procedure isn't capturing orphaned Windows users, but here is how you can check:

SELECT p.name
FROM database_name.sys.database_principals AS p
WHERE [type] IN (N'U', N'G')
AND NOT EXISTS
(
  SELECT 1 FROM sys.server_principals AS sp
    WHERE sp.sid = p.sid
);

If you need to do this for all databases, you can generate this dynamically, e.g.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT ''' + db.name + ''',p.name
  FROM ' + QUOTENAME(db.name) + '.sys.database_principals AS p
  WHERE [type] IN (N''U'', N''G'')
  AND NOT EXISTS
  (
    SELECT 1 FROM sys.server_principals AS sp
    WHERE sp.sid = p.sid
  );'
FROM sys.databases AS db
WHERE [state] = 0;

EXEC sp_executesql @sql;


MySQL: Shouldn't these two DATE queries be the same? I have this query, it tells me what order has expired, immediately after it expires.
(
  SELECT
    *
  FROM
    orders as ord
  WHERE
    DATE_ADD(paid_on, INTERVAL ord.duration HOUR) < NOW()
    AND DATE(DATE_ADD(paid_on, INTERVAL ord.duration HOUR)) BETWEEN DATE(NOW() - INTERVAL 5 DAY)
    AND DATE(NOW())
)
UNION
  (
    SELECT
      *
    FROM
      orders as ord
    WHERE
      DATE_ADD(paid_on, INTERVAL ord.extended_duration HOUR) < NOW()
      AND DATE(
        DATE_ADD(paid_on, INTERVAL ord.extended_duration HOUR)
      ) BETWEEN DATE(NOW() - INTERVAL 5 DAY)
      AND DATE(NOW())
  )

I have decided to optimize it by rewriting it like this
(
  SELECT
    *
  FROM
    orders as ord
  WHERE
    DATE_ADD(ord.paid_on, INTERVAL ord.duration HOUR) < NOW()
    AND DATE_ADD(ord.paid_on, INTERVAL ord.duration HOUR) BETWEEN (CURDATE() - INTERVAL 5 DAY)
    AND CURDATE()
)
UNION
  (
    SELECT
      *
    FROM
      orders as ord
    WHERE
      DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR) < NOW()
      AND DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR) BETWEEN (CURDATE() - INTERVAL 5 DAY)
      AND CURDATE()
  )

All I did is using CURDATE() instead of DATE(NOW()), but the queries are retrieving different results, the first query is working correctly,  the second query sometimes waits 24h before showing something as expired. DBFiddle

AND  DATE_ADD(ord.paid_on, INTERVAL ord.extended_duration HOUR)

is a DATETIME, which might be in the middle of today.
BETWEEN (CURDATE() - INTERVAL 5 DAY) AND CURDATE()

excludes anything after midnight this morning.
Please provide the order that is miscategorized.


Specifying columns in LEFT JOIN through subquery beneficial performance wise? I'm trying to understand how JOINS are handled, and was wondering about the following:

Imagine a big query concerning a lot of tables, I'm really looking for small optimizations as it's a pretty important query. What would be faster in the long run. The 'normal':

SELECT
    TOP x
    A.a
    , A.xxx
    , B.a
    , B.xxx
FROM A
LEFT JOIN B ON (A.xxx = B.xxx)
ORDER BY A.a

Or specifying the columns of the joined table beforehand?

SELECT
    TOP x
    A.a
    , A.xxx
    , B.a
    , B.xxx
FROM A
LEFT JOIN
(
    SELECT
        B.A AS A
        , B.xxx AS xxx
    FROM B
 ) AS B ON (A.xxx = B.xxx)
ORDER BY A.a

I guess this boils down to having a good understanding of how SQL Server handles optimization. In my preliminary tests I haven't found any difference, but these tests aren't quite as complicated as 'The Real Thing'. Will the latter just be optimized to the former by the interpreter?

Since I have never seen the latter side being used (but admittedly, I'm really new to this), my gut feeling tells me the first one will be preferred, but a gut feeling won't (or shouldn't) hold up as real evidence.

Bare in mind the real query really has alot of tables involved with probably only 1 or 2 relevant columns per table, so I was thnking perhaps I can doe some sort of preselection to avid including the whole table in the query.

So, any ideas?

For most simple cases, like your, there will not be any difference. SQL Server is lazy and will figure out the most easiest way of completing your query and it will do a very good job at it (most of the time).

The best way of analyzing the difference yourself is to look at the query plan of both queries.

Given this example of two queries similar to yours:

SELECT
    c.CustomerId,
    o.OrderId,
    o.ArticleId,
    o.Customerid
FROM
    dbo.Customer c
INNER JOIN
    dbo.orders o
    ON o.CustomerId = c.CustomerId

SELECT
    c.CustomerId,
    o.OrderId,
    o.ArticleId,
    o.Customerid
FROM
    dbo.Customer c
INNER JOIN
(
    SELECT
        o.OrderId,
        o.ArticleId,
        o.Customerid
    from    dbo.orders o
) AS o
    ON o.CustomerId = c.CustomerId

The query plans for both will look exactly the same.

If you hover your mouse over the Index Scan operator you will see the Output List at the bottom of the popup.
The Output List will show exactly which columns that will be "extracted" from the table. Both queries produce the same Output Lists which means that there is no benefit of "pre-selecting" the columns because SQL Server is already doing this for you.

This is true in your example since you are specifically specifying columns from both tables in your select clause, if you would do SELECT * then there could be a difference.


How can we check for data integrity when we upgrade from MySQL 4.1 to MySQL 5.5? By data integrity, I mean:

Schema integrity
Data (record level) integrity

Checksum was an option, but since the storage format for some data types in MySQL 5.5 has been changed, it won't be reliable.

Another option is to parse the XML file generated by mysqldump. Since the file is too large (in GBs), is it worth it?

Any other way you have confirmed it?

You can use SQLyog-MySQL GUI (Trial) version to compare Schema as well as Data (records) to make sure consistency of schema and data as well.


Unable to connect to FTP server using "FTP Connection Manager" I'm trying to setup an SQL agent job to use my SSIS package. The package contains an FTP task that is failing. I'm getting the following error in both the job history and logging that I've setup (Log gives no other detail...):

  "Unable to connect to FTP server using "FTP Connection Manager"

I've tried setting up the SSIS package with the ProtectionLevel of EncryptSensitiveWithPassword. Going into the SQL job and entering the password. All the tasks run fine except for the FTP task.

I then tried setting up the SSIS package with the ProtectionLevel of DontSaveSensitive. I then setup a configuration file. Manually entered the password into the DTSConfig file. Chose the configuration file in the configurations tab of the step setup. But I'm still getting the same error when running the job.

The package runs fine when ran in visual studio.

Any ideas?

First make sure that you are able to connect FTP server through windows explorer, and then try three things

For Package:
1- Package Protection Level= EncryptSensitiveWithUserKey

For FTP Connection Manager:
2-Server Port= 21
3-UsePassiveMode= False


Index Seek on SQL Server Sorry folks if this sounds obvious. But does index seek always work on index pages not data pages? If so, which I think it is, it can only be used on its own if the output list are the same as index fields/covering . As a result, if a non-index field is needed for the output , it is neither not used by a query engine at all or used with Key/RId lookup which does not have a good performance. So I can conclude that index seek is only beneficial if the data is already covered by the index. is that correct?

You are correct in your description but the conclusion is not correct.  It may still be faster to use the index anyway even if it has to go off to the table for the rest of it.  The data pages contain many fewer rows than the index so there's lots of extra I/O when scanning.  Generally the index will tend to give you benefits if it selects few rows even if it's not a great fit.


How to move old data to cheap hard drive I have a quite big PostgreSQL database (with timescale plugin). Right now it is consuming about 500Gb on a SSD. Most of the data is in the form of time series. In most cases data older than a few months isn't really interesting.

My idea was to move that data to a cheap SATA hard drive instead of buying more expensive SSD's. Is that a good idea, and is there some good practice for implementing?

My naive implementation would be:
Keep two databases (or create a tablespace on the cheap HDD). Fetch every few hours data from "fast" (SSD) database to the "slow" database (HDD). Every few days, delete data from the slow database. Is this a good idea? I am happy to hear some feedback and better suggestions.

Here is a better architecture:

Create a new tablespace on the slow drive.
Set the storage parameters seq_page_cost and random_page_cost higher on that new tablespace so that the PostgreSQL optimizer knows that the disks are slower.
Partition the big time series tables by time ranges (use the same boundaries for all affected tables) so that you end up with a couple of dozen partitions for each.
Move the old partitions to the slow tablespace.

Then you still have all the data accessible.

Use PostgreSQL v11 or better for partitioning.


Problem connecting to SSRS reporting services When I try to connect to my reporting services server (SQL Server 2005) from my local machine, SQL Server 2014 I get the error message below

  Invalid Namespace (System.Management)

But when I RDP into my reporting services server and connect to reporting services there, everything is fine.

There is nothing wrong with the reporting services.

Is there a way to connect to SQL Server 2005 Reporting Services from a SSMS 2014?

I have the same problem except my Reporting Service is 2008 R2.

Anyway I have SSMS 2014 on the server where Reporting Service is installed and get the same error.

I have installed SSMS 2008 R2 and straight away it works.

So you must have same version SSMS as Reporting service.

And here is explanation:

In Reporting Services, the namespace for SSRS 2014 is root\Microsoft\SqlServer\ReportServer\RS_\v12\Admin
while the namespace for SSRS 2012 is
 root\Microsoft\SqlServer\ReportServer\RS_\v11\Admin.


How to have different server config on replication server for postgres_fdw? I have a postgres sharding based on postgres_fdw and postgres partitioning, lets say 3 machine (1 coordinator and 2 Shard machines). What I want to do is have read replica version of same? How should I do it?
What I have tried for now is, I have created read replicas of all the three machines but since server configuration also gets copied to the replica of coordinator server, there is no way for me to redirect queries from replica of coordinator to replica of shard machines? How do i do that?

If you define the foreign server using host and port connection parameters, these data are part of the PostgreSQL metadata and replicated. Streaming replication creates an identical copy of the database.
So you'd have to define the foreign server using connection parameters that refer to a file outside the database proper.
What comes to mind is the service connection parameter. With that, the actual connection parameters are taken from a file pg_service.conf that is outside the database. Then all you have to do is provide a different service file on the standby server.


SQL Server - Queries are much faster on the application when run second time When I click on a screen on my vb application, it takes approx 6/7 seconds to display the results. When I click off the screen and click back again into the screen, it returns immediately.

I ran profiler, and can see there are multiple queries running when clicking on this screen. There wasn't anything glaring obvious to fix on these queries; when run one by one in SQL Server, they ran within 1 second. The execution plan didn't recommend any missing indexes (I did add 2 indexes myself to prevent some key look ups but that was it).

My last test was to update statistics. I did this, updated all statistics on our local copy, but this still doesn't return the results on the application any faster.

Any advice on how to return these results immediately on the application? Perhaps statistics are missing from these queries? Or is there a way to auto update stats or similar for these specific queries?

SQL Server Settings:

Auto Create Stats - True

Auto Update Status - True

Auto Create Incremental Stats - False

Auto Update Stats Async - False

When I click on a screen on my vb application, it takes approx 6/7 seconds to display the results. When I click off the screen and click back again into the screen, it returns immediately.

Does the delay reappear when you restart the Application or when you restart the Database?

If it's the former, then the Application may be doing some caching of its own and, the first time you run it, it's building that cache, which takes a while.

If it's the latter, then it's probably the data being loaded into the database's Buffer Cache from disk.  Once you've loaded the data once (and assuming that nothing more important flushes it out again) then the data is simply sitting in the Buffer Cache waiting to be [very quickly] reused.


Moving data in multiple tables from old database to one table in new database I have two SQL Server 2008 databases. One is for my old application (full of data) and the other is for a complete re-write of my application.

I want to migrate the data, but I have no idea how to tackle it. Some of the tables have exactly the same structure, but some tables in the new database are composed from columns of multiple tables in the old database.

Is there a way to create such a script/project/application in which I can define the data transformation while also maintain constrains?

You can use SSIS (from sql 2005 and up)to transfer data from one database.Table to anotherDatabase.Table or all tables as well.

Note: SQL Server Import and Export Wizard will give you option to save the package at the end so that you can reuse the package for subsequent data transfer.

You need to be careful with constraints on the destination database. You can temporarily disable them and then reenable then once your data load is done. Script by Aaron can be found at Temporarily disable all foreign key constraints

If the tables from source and destination have exactly same schema, then you can use bcp as well. for script refer to my answer here.


SQL Server Management Studio generates error, can't start On my development machine, SQL server 2008 R2 was running fine previously. But after formatting OS and re-installing the OS and SQL server, it generates the following error when I want to start SQL Server Management Studio.

Error Message:

  A problem was encountered initializing commands and menus. The merged file was loaded, but it contained no valid commands. Please run setup and select Repair.

Please note that I've executed setup and repaired successfully after getting the above error.

Instead of running repair, completely uninstall the client tools using setup, and then reinstall them from scratch. Make sure you also uninstall any add-ins or plug-ins that you're currently using in SSMS. Once you've installed SSMS from the media, then apply SQL Server 2008 R2 Service Pack 1. If you have Visual Studio 2010 also installed on your machine, make sure you've installed service pack 1 there before re-installing client tools.

FWIW, I've found the new features and enhancements in SQL Server 2012's version of Management Studio to be very beneficial, and have been using it as my only SSMS version since the early betas to manage 2005, 2008, 2008 R2 and 2012 instances with no problems. RC0 is out so it may be worth trying that version too. It's a big download for just the client tools, but worth it IMHO. You can get it here:

http://www.microsoft.com/download/en/details.aspx?id=28145


what will happen if MongoDB primary host is down and secondary is out of sync. will we lose transaction? In our environment, we have one primary(A) and two secondaries(B & C nodes) running in Mongodb 4.0 version. secondary(C) server is newly added and sync is happening on that. Cluster is running only with A and B servers. Now, the Primary server(A) was abruptly down due to some host issue and it was not responding and we could also see that secondary server(B) was out of sync for 10 minutes. we were able to bring up only "B" as new primary. what will happen to the transactions that were completed in old primary (A) and that was not synced with secondary(B).? Please answer.

When you managed to get that A running, it will roll-back all those transactions what are not on B, because now B have newer transactions and it's Primary. That you get when you "force" Secondary to be primary against replica sets will.


Alternatives to User Defined Performance Counters for CPU Monitoring - SQL 2014 I want to monitor CPU usage before implementing TDE on several production SQL Servers.

My plan was to use user defined performance counters, over a period of a couple of days, counters as defined here: https://docs.microsoft.com/en-us/sql/relational-databases/performance-monitor/monitor-cpu-usage?view=sql-server-2017

I can view data on these counters when just looking at the Performance Monitor graph but the Data Collector sets just show datetime stamps and no counter data.

I'm on a time sensitive project and so I am looking for an alternative to perfmon.... and any advice.

I suppose the cause of the empty counters belongs on serverfault - but I would appreciate a solution if anyone ran into this issue and solved it.

I found a solution - the issue is I tried running perfom not locally to the server but from another server. Locally it works. (sigh)


Connect to postgres with different privileges From python program, I want to connect to postgres with various privileges.

lower_privileges = connect(db, username, password)
higher_privileges = connect(db, username, password)

How do I setup postgres 9.5 on arch linux, so I can do that?

create database dbone;
grant all privileges on dbone.* to 'writer'@'localhost' identified by 'pass';
grant select on dbone.* to 'reader'@'localhost' identified by 'pass';

The problem is I enter createdb dbone then psql then grant all privileges on database dbone to writer; and I get "db does not exist error" then I exit psql and go dropdb dbone and get "db does not exist"

From the arch guide

sudo pacman -S postgresql
su - postgres
initdb --locale $LANG -E UTF8 -D '/var/lib/postgres/data'
exit
Start postgres service sudo systemctl start postgres.service

Check admin stuff

Set postgres system user pass passwd postgres PASS
su - postgres
Optional, you could set connection host rules vim data/pg_hba.conf
psql
Set admin DB pass \password PASS

Create db create database dbone;
Add users, grant privileges

create user writer --interactive -P;
create user reader --interactive -P;
give all privileges to writer user grant all privileges on database "dbone" to writer;
Quit \q
Connect to your db psql dbone
For tables in the public schema of your db, automatically give select privileges to reader user ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO reader;
\q,   exit


constraint in oracle I'm trying modeling things like this using oracle:

An account can have 1~5 members

So I'll have a account table and a member table with a FK of account. But is that possible to check if the account have less than 5 members?

I've tried constraint and trigger but oracle doesn't allow sub queries in constraint or condition of trigger like

CHECK( SELECT ...) or IF count(*) >= 5

which make things getting hard.

One way is to add a memberNo column and restrict to 5 possible values, per account:

CREATE TABLE members
( -- ...
  accountID NUMBER REFERENCES accounts (accountID),
  memberNo NUMBER NOT NULL,
  -- ...,
  UNIQUE (accountID, memberNo),
  CHECK (memberNo IN (1,2,3,4,5))
) ;

This imposes of course the additional burden of providing and keeping values for this column when inserting into the table (with extra complications for deletions. If for example you delete the member with memberNo=2 for an account, should the 3 higher numbers be decreased or should the complications be dealt in future inserts.)


How to find the sql jobs that have not run for more than two months? I would like to get a query to ascertain which sql jobs have not run for more than 60 days?

There is no way to assure you can get data for that historical range because-

This depends upon the settings of SQL agent where you decided to keep history based on certain conditions , unless left default which again may or may not go that time back.

If you are lucky enough and msdb has the information still stored for jobs 60 days old, you can simply query from sysjobs to get required data and filter based on your need


The SELECT permission was denied on the (View) object after explicit grant select on view to user I have a database with a View Named '1098Statement'
We are doing testing for an application with user name 'appuser'.

I have tried the following solutions to granting select on the view and keep getting error:

Grant Select on View
Add user to db_datareader
Add user to a database role which has select granted on the view

Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object '1098Statement', database 'mydb', schema 'dbo'.

None of them work.

Are there any pointers anyone can provide on troubleshooting this issue.

My only remaining thoughts are to check

the user account to see if there are any explicit "deny" restrictions on either the user or any roles the account belongs to.
check the view to see if there are any explicit deny

Your response pointed me in the right direction.

After looking at the object and verifying the permissions many times, I shifted my focus to the Login and User.

Apparently the login is mapped to a different user.
So when I checked the server_principal against the database_principal there was a mismatch.

The login is called appuser and i am also trying to grant permissions inside the database to appuser. Unfortunately someone created a different database user which is actually called appwebuser.

So once i discovered that we were granting permissions to the wrong user and update the code to the correct database user the problem was resolved.

I used the following query to track down the login. I add in the servername and dbname so i can check against my different environments and report to the customer.

SELECT
@@servername  as [server_name]
,db_name()  as [database_name]
,sp.name as [server_principal_name]
,sp.type as [server_principal_type]
,sp.type_desc as [server_principal_type_desc]
,sp.create_date as [server_principal_create_date]
,sp.default_database_name as [server_principal_default_database_name]
,dp.name as [database_principal_name]
,dp.type as [database_principal_type]
,dp.type_desc as [database_principal_type_desc]
,dp.default_schema_name as [database_principal_default_schema_name]
FROM
    sys.database_principals  dp INNER JOIN
    sys.server_principals sp ON (dp.sid = sp.sid)
WHERE
    sp.name like 'appuser'
    or dp.name like  'appuser'
    or sp.name like 'appwebuser'
    or dp.name like  'appwebuser'


Restoring full and differential backups from production to a report server Running this SQL query:

   RESTORE DATABASE Demo
   FROM DISK = 'C:\DBBackup\Demo_FULL_Sunday.bak'
   WITH  NORECOVERY

   RESTORE DATABASE Demo
   FROM DISK = 'C:\DBBackup\Demo_Diff_Monday.bak'
   WITH RECOVERY

I'm getting these errors:

  Msg 3117, Level 16, State 4, Line 1
  The log or differential backup cannot be restored because no files are ready to rollforward.

  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

I have also tried adding WITH REPLACE but no luck. This is meant to restore to a report server, not to the original server that the backup was taken from.

I have tried Googling, however everything I read just says to make sure you are using WITH NORECOVERY.

Although you have WITH NORECOVERY on the 1st statement, the error is actually occurring since there is an existing database with the name Demo which cannot be overwritten without explicit instruction.

Try:

ALTER DATABASE Demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE Demo
FROM DISK = 'C:\DBBackup\Demo_FULL_Sunday.bak'
WITH REPLACE, NORECOVERY;

RESTORE DATABASE Demo
FROM DISK = 'C:\DBBackup\Demo_Diff_Monday.bak'
WITH RECOVERY;

Be aware this will totally destroy the existing database and replace it with the contents of the backup file.  Ensure you do this on the correct server, since you will have no 2nd chances.


Vulnerability in Server from SQL Server 2008 R2 We scanned our systems and got a high vulnerability reported in a server.

One of the thing that got flagged was MS12-060, which is related to  MS Office 2003 Web Components that gets installed along with Analysis Services.

Can we remove that component without breaking the SQL Server engine or do we need to uninstall SSAS first and then remove the component.

The web components are/were used for the cube browser in SSMS (That changed in SQL Server 2012 IIRC), so you could probably remove it if you are not using that functionality and only browse the cubes from other clients, but that's an untested configuration so who knows for sure. It will definitely be unsupported.

As you state:

  Can we remove that component without breaking SQL server engine

If all you care about is the SQL Server engine (and not the Analysis server) you could consider removing Analysis services altogether.

However, I don't see why you would want to do that as according to the documentation this is fixed in SQL Server 2008 R2 by simply installing updates for Office.

Also according to that page it doesn't seem like it's the office 2003 web components you have installed with your version of SQL Server but the Office 2007 web components as the link for SQL Server 2008 R2 is pointing to the update for Office 2007

If I were you I would prefer patching the system than taking the risk of removing a component that came with the installation.

And as vonPryz mentioned in a comment. It's about time to look at upgrading to a more recent version of SQL Server if you are worried about support anyway.


Do I trace an Active Directory group based database login the same way as an AD user based login on SQL Server? We are currently undergoing an PwC audit on our database logins. Some of our user accounts have sysadmin privs, which of course is a major security breach. I have successfully done traces on individual user accounts, but my question is how do I trace a database login mapped to an AD Group? The said group has over two dozen users. I don't really care what access each individual in the group requires, but I do care about what the group needs as a whole, so I can give appropriate access before taking the sysadmin priv off it.

What I do for individual users right now is to select the appropriate events in the SQL Server Profiler and filtered the LoginName column with the name of the user surrounded by a pair of %. Can the same be done for AD group based logins? I am tracing the said group right now, but there doesn't seem to be any activity going on (could be that no one from the group is currently using the database). I just want to confirm that this is the right way to do it for this type of login, or am I missing something?

Unfortunately, the group is not presented in the LoginName column => it is the individual user account (DOMAIN\user).

You can get the groups for a given member by executing:

EXEC XP_LOGININFO 'domain\groupname','members'
EXEC XP_LOGININFO 'domain\groupname','all'

This may be able to be wired up using a user-defined event.


Trigger to prevent update when Flag value is 1 I need help with a trigger to prevent update on any column except for column Flag (it can be updated) when Flag value is 1 on the below table:

CREATE TABLE Tmp (Id INT, Type INT, TranDate DATE, Flag INT)
INSERT INTO Tmp VALUES (1, 2, '2017-04-24', 0)
INSERT INTO Tmp VALUES (2, 4, '2017-04-27', 1)

I've tried with the below code but doesn't work correctly.

CREATE TRIGGER [dbo].[Prevent_Update_Trigger]
ON [dbo].[Tmp]
FOR UPDATE
AS
SET NOCOUNT ON
IF  EXISTS(SELECT NULL FROM dbo.Tmp t JOIN inserted i ON t.Id=i.Id AND t.Flag=1)
BEGIN
RAISERROR('You can not update when Flag value is 1', 16, 1)
ROLLBACK TRAN
SET NOCOUNT OFF
RETURN
END
SET NOCOUNT OFF

CREATE TRIGGER dbo_Tmp_trg_AU
ON dbo.Tmp
AFTER UPDATE
AS
BEGIN
    SET ROWCOUNT 0;
    SET NOCOUNT ON;

    IF EXISTS
    (
        SELECT NULL
        FROM Deleted AS Del
        WHERE
            -- Originally, flag was 1
            Del.Flag = 1
            AND NOT EXISTS
            (
                -- Column(s) aside from Flag have changed
                SELECT
                    Del.[Type],
                    Del.TranDate
                INTERSECT
                SELECT
                    Ins.[Type],
                    Ins.TranDate
                FROM Inserted AS Ins
                WHERE
                    -- Same Id
                    Ins.Id = Del.Id
            )
    )
    BEGIN
        RAISERROR ('You can not update when Flag value is 1', 16, 1);
        ROLLBACK TRANSACTION;
        RETURN;
    END;
END;

This assumes the Id column cannot be updated (e.g. it is an IDENTITY column).


Searching for a string in multiple fields in a table There's lots of examples of complex systems to find a string in all the columns in a table or DB. Mine has to look for a string in 10 fields out of about 100 in a particular table, so these solutions are too complex.

Right now I do the dumb thing:

SELECT * FROM sometbl WHERE FIELD0 LIKE 'zap' OR FIELD1 LIKE 'zap'...

But this results in really nasty-to-read SQL. So then I thought...

SELECT * FROM sometbl WHERE '%zap%' LIKE FIELD0+FIELD1+FEILD2...

I cannot convince myself this works. For one, is the LIKE bi-directional so that can I put the %'s on the LHS? Or do those %'s have to be on the RHS?

So then I thought...

SELECT * FROM sometbl WHERE CHARINDEX('zap',FIELD0+FIELD1+FEILD2...) > 0

Can anyone comment on these solutions, and whether I should use any of them?

If you are not using any wild cards then can use =

Starting a like with % kills the use of an index
Not good

Just stay with

SELECT *
  FROM sometbl
 WHERE FIELD0 = 'zap'
    OR FIELD1 = 'zap'

Can use variable to only set once
Can use in

declare @val varchar(200)  = 'message/rfc822'

select sID, mimeType, caseID
  from docSVsys
 where mimetype = @val
    or caseID   = @val;

select sID, mimeType, caseID
  from docSVsys
 where @val in (mimetype, caseID)


The best MySQL configuration for my server I'm little newbie in configuring mysql and I need some advices.

Few days ago I made little home server on Ubuntu Server 12.04 with samba, mysql 5.5, apache 2.2, PHP 5.4.6 etc.

I'm using it to sharing files per samba and hosting my local projects in PHP/MySQL - I'm programmer, and I'm often testing new solutions etc

It is working good, but INSERTS on InnoDB tables are slow as hell.

I think it is bad config in my.cnf, becouse a few months earlier I used the same computer on Windows WAMP and queries were faster...

Some information:

CPU: Intel(R) Pentium(R) 4 CPU 3.00GHz
RAM: 1GB
HDD: Maxtor 6L200M0 (189GiB (203GB))

Actual my.cnf:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
key_buffer      = 64M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit   = 1M
query_cache_size        = 32M
innodb_buffer_pool_size=512M
innodb_additional_mem_pool_size=10M
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=80M
expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

[mysqlhotcopy]
interactive-timeout

[myisamchk]
key_buffer=32M
sort_buffer=32M
read_buffer=16M
write_buffer=16M

You may find this surprising but MySQL 5.5 can be configured to access multiple cores for InnoDB. The features are not on by default. In fact, there are occasions when older versions of MySQL run faster "out-of-the-box" than MySQL 5.5.

I have written posts about this before:

Jun 19, 2011 : How do I properly perform a MySQL bake-off?
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Jun 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5

You must set a few options to wake up InnoDB as to its multiple core capabilities

[mysqld]
innodb_io_capacity=2000
innodb_read_io_threads=32
innodb_write_io_threads=32
innodb_log_buffer_size=32M
innodb_thread_concurrency=0

Please see my other posts on these settings

May 26, 2011 : About single threaded versus multithreaded databases performance
Sep 12, 2011 : Possible to make MySQL use more than one core?
Sep 20, 2011 : Multi cores and MySQL Performance
Apr 26, 2012 : Is the CPU performance relevant for a database server?

BTW your log buffer is a little too big. I lowered it to 32M because while a bigger log buffer saves on Disk I/O, it makes for longer COMMITs.

I also noted that you have all the InnoDB inside ibdata1 because innodb_file_per_table is disabled. Try the above setting anyway. If you want to move data and indexes out of ibdata1 and into their own tablespace files, please see my past posts on cleaning up InnoDB for data and index storage outside of ibdata1:

May 21, 2012 : How large will a MySQL database be relative to the dump file?
Apr 01, 2012 : Is innodb_file_per_table advisable?
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Feb 04, 2011 : MySQL InnoDB - innodb_file_per_table cons?
Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine?

This will control excessive growth of ibdata1 and allow you to compress data and indexes.


How do you CREATE TABLE AS SELECT (CTAS) with a composite type? PostgreSQL supports CREATE TABLE AS SELECT (CTAS). It also supports composite types that can represent whole rows.

Here is an example of a CTAS,

CREATE TABLE foo AS
SELECT * FROM ( VALUES (1), (2) );

Here is a few examples of SELECTing a row.

SELECT (1,2);
SELECT ROW(1,2);
SELECT t FROM ( VALUES (1,2), (2,100) ) AS t;
SELECT x FROM ( VALUES ((1,2)), ((2,100)) ) AS f(x);
SELECT (x,y) FROM ( VALUES (1,2), (2,100) ) AS t(x,y);

However, none of them seem to work with CTAS.

CREATE TABLE foo AS SELECT f FROM ( VALUES (1,2), (2,100) ) AS f(x,y);
ERROR:  column "f" has pseudo-type record

CREATE TABLE foo AS SELECT (x,y) FROM ( VALUES (1,2), (2,100) ) AS f(x,y);
ERROR:  column "row" has pseudo-type record

I get that this is a typing-error, but what syntax does CTAS want?

I don't think you can do it the way you are trying to... if my understanding of "A pseudo-type cannot be used as a column data type" is correct.

However, you can do something similar.

First, you define your composite type (for instance, we have a "point in two dimensions", with fields x and y):

-- Need to define the composite type
CREATE TYPE point_2d AS
(
    x real,
    y real
) ;

Then, you can write 'literals' that need to be cast to point_2d so that PostgreSQL know which exact type they have. So, this works:

CREATE TABLE
    ttt AS
SELECT
    *
FROM
(
    VALUES
    ('(1.5, 2.5)'::point_2d, '(-1.3, -2.2)'::point_2d),
    ('(2.3, 4.4)'::point_2d, '(-2.2, -3.3)'::point_2d)
) AS lines (start_point, end_point) ;

And then, you can perform queries like:

SELECT
    (start_point).x AS x_start,
    (start_point).y AS y_start,
    (end_point).x AS x_end,
    (end_point).y AS y_end
FROM
    ttt ;


Insert into matching on a WHERE clause I'm sure this is simple but can't get it work. I have two tables, A and B, where one only A has ID and they both have Number. I'd like to copy all the ID's from A into B where their Number matches (all Number values are unique).

I tried this but I'm getting invalid column name

INSERT INTO dbo.B ( [ID] )
SELECT orig_id
FROM dbo.A a, dbo.B b
WHERE b.[NUMBER] = a.number

but a whole bunch of new rows are created instead of updating the existing ones. How can I update the ID column of table B with the id's from table A matching on the Number values?

Thanks

update dbo.B
set b.[ID] = a.orig_id
FROM dbo.A a
join dbo.B b
on b.[NUMBER] = a.number
and b.[ID] <> a.orig_id

the <> is so it can avoid taking a lock if the are equal


To solve this problem, I did the following (+1 for the question BTW - it was an interesting challenge and I learnt a lot by going through it).
The code below can be found on the fiddle here (Microsoft SQL Server 2017 Linux - works on all versions on dbfiddle going back to 2014) :
Created a table:
CREATE TABLE payment (CUST_ID int, LOAN_ID int, PMNT_ID int, PMNT_AMT float,  PMNT_DT date,   LOAN_AMT float,  LOAN_FUND_DT date,  PMNT_RUN_TOT int);

Populated the table:
INSERT INTO payment VALUES (1155,      215,         100,         100.00,   '2015-02-15',           300.00,        '2015-01-15' ,       100.00);
INSERT INTO payment VALUES (1155,      215,         101,          50.00,   '2015-03-15',           300.00,        '2015-01-15' ,       150.00);
INSERT INTO payment VALUES (1155,      215,         102,          75.00,   '2015-04-15',           300.00,        '2015-01-15' ,       225.00);
INSERT INTO payment VALUES (1155,      215,         103,          75.00,   '2015-05-15',           300.00,        '2015-01-15' ,       300.00);

INSERT INTO payment VALUES (1156,      223,         104,          30.00,   '2015-02-15',           390.00,        '2015-01-15' ,        30.00);
INSERT INTO payment VALUES (1156,      223,         105,          30.00,   '2015-03-15',           390.00,        '2015-01-15' ,        60.00);
INSERT INTO payment VALUES (1156,      223,         106,         300.00,   '2015-04-15',           390.00,        '2015-01-15' ,       360.00);
INSERT INTO payment VALUES (1156,      223,         107,          30.00,   '2015-07-27',           390.00,        '2015-01-15' ,       390.00);

And then I ran the following query (don't worry about the overkill/repetition on the PARTITION BYs - I was trying to get to grips with Window functions).
WITH tab1 AS
(
  SELECT n.cust_id, n.loan_id, n.pmnt_id, pmnt_dt, pmnt_amt,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.cust_id, n.loan_id) AS loans,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.pmnt_id) AS payments,
  SUM(n.pmnt_amt) OVER () AS grand_total,
  SUM(n.pmnt_amt) OVER (partition by loan_id ORDER BY pmnt_id) AS payment_per_loan,
  SUM(n.pmnt_amt) OVER (ORDER BY loan_id) AS loans_cumulative,
  SUM(n.pmnt_amt) OVER (PARTITION BY n.loan_id) loan_amount,
  (SUM(n.pmnt_amt) OVER (PARTITION BY n.cust_id, n.loan_id)) -
  (SUM(n.pmnt_amt) OVER (partition by loan_id ORDER BY pmnt_id)) AS remainder_per_loan
  FROM payment AS n
  ORDER BY cust_id, n.loan_id, n.pmnt_id, n.pmnt_dt
)
SELECT cust_id, loan_id, loan_amount, pmnt_dt AS paid_off_date FROM tab1
WHERE remainder_per_loan = 0

And the result is (with the limited subset of data that I used):
cust_id|loan_id|loan_amount|paid_off_date
----------------------------------------------------
  1155|     215|        300|     2015-05-15
  1156|     223|        390|     2015-07-27


Slow query with sub SELECTs Unsure how to optimize this query but it and about 15 variations of it need to run for a dashboard page. Any and all suggestions are welcomed! Thanks.

Time: 0.466s

Time without sub-selects: 0.058s

SELECT
    gallery.go_id AS id,
    (SELECT meta.value FROM meta WHERE meta.model = "Gallery_other" and meta.model_id = gallery.go_id and meta.key = "cache_sort_title_text" LIMIT 1) AS `sort_cache_sort_title_text`,
    (SELECT CAST(meta.value AS SIGNED) FROM meta WHERE meta.model = "Gallery_other" and meta.model_id = gallery.go_id and meta.key = "cache_sort_title_int" LIMIT 1) AS `sort_cache_sort_title_int`
FROM
    arGalleryOther gallery
WHERE
    gallery.go_visible = 1
    AND
    gallery.go_added <= "2017-11-09 17:55:46"
    AND (
        gallery.go_maingal = 9
        OR
        FIND_IN_SET(9, gallery.go_othergal)
    )
ORDER BY
    gallery.go_added DESC,
    sort_cache_sort_title_text ASC,
    sort_cache_sort_title_int ASC
LIMIT
    4

CREATE TABLE `arGalleryOther` (
  `go_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `go_code` varchar(10) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `pr_id` varchar(150) CHARACTER SET latin1 DEFAULT NULL,
  `go_name` varchar(75) CHARACTER SET latin1 NOT NULL DEFAULT '',
  `go_name_noint` varchar(75) CHARACTER SET latin1 DEFAULT NULL,
  `go_name_int` int(5) unsigned DEFAULT NULL,
  `go_maingal` int(10) unsigned NOT NULL DEFAULT '0',
  `go_othergal` text CHARACTER SET latin1 NOT NULL,
  `go_added` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `go_visible` tinyint(1) NOT NULL DEFAULT '1',
  `go_horiz` varchar(15) CHARACTER SET latin1 NOT NULL DEFAULT 'h',
  `go_vert` varchar(15) CHARACTER SET latin1 NOT NULL DEFAULT '*',
  `go_override` tinyint(1) NOT NULL DEFAULT '0',
  `go_thumb` varchar(25) CHARACTER SET latin1 DEFAULT NULL,
  `go_keywords` text CHARACTER SET latin1 NOT NULL,
  `go_html_page` varchar(100) CHARACTER SET latin1 DEFAULT NULL,
  `go_phototype` enum('H','V') CHARACTER SET latin1 NOT NULL DEFAULT 'H',
  `go_notes` text CHARACTER SET latin1,
  `go_nudity` enum('YES','NO') CHARACTER SET latin1 NOT NULL DEFAULT 'NO',
  PRIMARY KEY (`go_id`),
  KEY `idx_visible` (`go_visible`),
  KEY `idx_added` (`go_added`),
  KEY `idx_maingal` (`go_maingal`),
  FULLTEXT KEY `idx_othergal` (`go_othergal`)
) ENGINE=MyISAM AUTO_INCREMENT=4527 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

CREATE TABLE `meta` (
  `meta_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `model` varchar(50) NOT NULL,
  `model_id` int(11) unsigned NOT NULL,
  `key` varchar(50) NOT NULL,
  `value` text,
  `serialized` enum('false','true') NOT NULL DEFAULT 'false',
  `type` varchar(20) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `updated_date` datetime NOT NULL,
  PRIMARY KEY (`meta_id`),
  KEY `idx_model` (`model_id`,`model`) USING BTREE,
  KEY `idx_key` (`key`) USING BTREE,
  KEY `idx_model_id` (`model_id`),
  KEY `idx_model_key` (`model`,`key`,`value`(255)) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=368839 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT

Critique of indexes:

go:

  KEY `idx_visible` (`go_visible`),  -- DROP; indexing flags is rarely useful
  KEY `idx_maingal` (`go_visible`, go_added),   -- for WHERE

gl:

INDEX(visible, added)  -- in this order

meta:

  PRIMARY KEY (`meta_id`),  -- see below
  KEY `idx_model` (`model_id`,`model`) USING BTREE,
  KEY `idx_model_id` (`model_id`), -- DROP since the above handles it
  KEY `idx_model_key` (`model`,`key`) USING BTREE  -- Prefix indexing useless here

For key-value, it is rarely necessary to have an AUTO_INCREMENT id (meta_id) since there is probably a combo of other columns that is unique.  See my tips here.


Can we set the fixed size for both plan cache and data cache? As far as I know, SQL Server automatically manages the size for the components of buffer pool. I wonder if it is possible to set pre-defined size for both plan cache and data cache?

No.

You can set Minimum & Maximum server memory to control the range of total memory utilization by the Instance, but there is not fine grained control for the constituent parts that make up "server memory"


SQL SERVER auditing SELECT of a single column Is this possible, or is table-level the most granular access?

So far, Google search has shown nothing, and this screenshot does not mention column, but I thought I'd ask here.

SQL Server version is 2012 SP4-OD.

Not knowing your systems or the load on them, I cannot stress enough the need to test this suggested solution, very thoroughly. However, that said, you can do this, partly, through Extended Events. Here's a sample script:

    CREATE EVENT SESSION [ColumnAudit]
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
    (WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'HamShackRadio')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%RadioName%')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%SELECT%')
           )
    ),
    ADD EVENT sqlserver.sql_statement_completed
    (WHERE (
               [sqlserver].[equal_i_sql_unicode_string]([sqlserver].[database_name], N'HamShackRadio')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%RadioName%')
               AND [sqlserver].[like_i_sql_unicode_string]([statement], N'%SELECT%')
           )
    );

A few notes on this.

First, I would strongly recommend that you put good filtering in place. I've filtered first on the database name in order to only capture this from the one database. Second, I've ensured that I can filter the statement for the columnname ahead of anything else to be sure I'm only looking at those statement. Then I filter on SELECT.

Second, lest you balk at all to use Extended Events, remember that Audit is just using them behind the scenes, so this is a normal use of the functionality offered.

Third, and most importantly, test this. I've no idea how a wild card filter like this is likely to behave on your system. If you have another way to add additional filters before the wild card, say, on object_id or something, I would.

Finally, this won't work if someone types SELECT * FROM dbo.MyTable. I'm not sure how I'd tackle attempting to capture that. I guess you could filter for SELECT, the table name, and *. That would probably do it.

In case I haven't emphasized it enough, test this thoroughly before you implement it in production.


Can multiple cores be used by PostgreSQL to run code in sections (cursors) of a table effecting parallelism? Let's say I have a 1 million record table. Each record is uniquely identifiable by id. If the table was divided into 10 cursors and 10 requests were made by a web client for each cursor to say change the last name field to upper, and 10 cores were available, would the work get done 10x faster? Or any time faster, compared to a straight for .. loop regular processing of the entire table?

I know PostgreSQl can't run parallel queries that make changes. I'm trying to get around that by using code instead. Note that these records are independent. All changes are strictly particular to the record.

Actually, PostgreSQL 9.6 can run parallel (read) queries. Parallel writes aren't supported yet.

The approach you suggest would work, but some care would be required to ensure it was consistent, since each cursor would be a different session with a different snapshot of the database.

You could instead just run 10 UPDATEs from 10 different clients. Same effect, simpler, and probably faster. Just make sure the targeted row ranges don't overlap.


Simple trigger to update two columns in one table I want to create a trigger MySQL that updates two fields at the same time.

Whenever a record is added or inserted, I want the contents to be copied to one field or the other, depending on which one is empty/edited.

due_date is a varchar and end_date is a datetime.

e.g.

IF new record with due_date filled in but end_date empty → copy due_date to end_date after insert.
IF new record with end_date filled in but due_date empty → copy end_date to due_date after insert.
IF update to due_date → copy that to end_date after update
IF update to end_date → copy that to due_date after update

Please note, the due_date format is already the correct and same format as datetime.

Hope i have understand what you are saying correctly, following would be the code you may be looking for.

DELIMITER //
CREATE TRIGGER trig_1 before insert
ON  <table_name> FOR EACH ROW
BEGIN

   IF   new.due_date is not null and new.end_date='' then
        set new.end_date=new.due_date;
   end if;
      IF   new.end_date is not null and new.due_date='' then
        set new.due_date=new.end_date;
   end if;

END;
//

DELIMITER //

CREATE TRIGGER trig_2 before update
ON  <table_name> FOR EACH ROW
BEGIN

   IF   new.due_date <>old.due_date then
        set new.end_date=new.due_date;
   end if;
      IF   new.end_date <> old.end_date then
        set new.due_date=new.end_date;
   end if;

END;
//

Hope it helps


setting a foreign key to an empty string I'm trying to set a foreign key to an empty string but i keep getting this error.

Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`msis230`.`deptchair`, CONSTRAINT `deptchair_ibfk_1` FOREIGN KEY (`PROFESSORID`) REFERENCES `professor` (`PROFID`))

this is my code:
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('SJ001', 'MSIS');
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('', 'BIO');
INSERT INTO DEPTCHAIR
VALUES ('JS001', 'PHY');
INSERT INTO DEPTCHAIR
VALUES  ('NS001', 'MKT');
INSERT INTO DEPTCHAIR
VALUES ('', 'ECO');

There are two approaches you can take
APPROACH #1 : Add to professor table
INSERT INTO professor (PROFID) VALUES ('');

Then, all INSERTs to DEPTCHAIR will work.
APPROACH #2 : Disable Foreign Key Check
SET foreign_key_checks = 0;
INSERT INTO DEPTCHAIR (PROFESSORID, DEPTID)
VALUES ('', 'BIO');
SET foreign_key_checks = 1;


Does this design satisfy 3NF, and can any improvements be made? Does this design satisfy 3NF, and can any improvements be made?

If it does not satisfy 3NF - why?

By improvements, I only care if it will seriously affect scalability, efficiency, etc - I'm not interested in personal preferences.

To add more detail, there can be many bids to a project and any employee can create a new bid. Unit price can be different for each project/bid.

The normal forms apply to individual tables. Dependencies are semantic; they depend on what the data means. Normalizing by relying on column names alone is not very reliable.

TblClient is in 5NF.
TblEmployee might have a transitive dependency and be only in 2NF, if EmployeeNm->EmployeeRole.
TblItem might have a transitive dependency and be only in 2NF, if (ItemType, ItemDesc)->Size, or if ItemDesc->Size.
TblProject might have a transitive dependency and be only in 2NF, if (ProjectSite, EstBeginDt)->EstCompleteDt, or if (ProjectSite, EstBeginDt)->anything else.

Every one of these tables almost certainly needs an additional unique constraint on one or more columns. Otherwise you're likely to end up with data that looks like this.

ItemID  ItemType  ItemDesc                Size
--
245     Flange    Engine mounting flange  13mm
246     Flange    Engine mounting flange  13mm
247     Flange    Engine mounting flange  13mm
451     Flange    Engine mounting flange  13mm
457     Flange    Engine mounting flange  13mm
683     Flange    Engine mounting flange  13mm

That particular table needs an additional unique constraint on either {ItemType, ItemDesc} or on {ItemType, ItemDesc, Size}.

Reaching 3NF for every table doesn't necessarily mean you have a good design. You might well have missed critical requirements.  If you missed a critical requirement, you're not likely to discover it by normalizing to 3NF or 5NF.

Whoever taught you to name all tables "Tbl"-something or "Lk"-something or "Xref"-something did you no favors. It's likely that person didn't understand logical data independence, and it's likely you don't understand it either. That's a big deal, because it's one of the main features of the relational model, and SQL supports it fairly well. Name tables for what they are, not for how you intend to use them today.


postgresql function index not being used by query I am trying to match a text field so I created an md5 function index as it suggested since a full index was not allowed because the text data was too large. But I can't understand why postgres is not using the index. I am using PostGIS 2.2.1 and Postgresql 9.5.0 but I have other function indexes which do use the index.

production=# set enable_seqscan = off;
SET
production=# CREATE INDEX searches_helper_query_index ON searches(md5(helper_query));
CREATE INDEX

production=# \d searches;
Column         |            Type             |                       Modifiers
helper_query   | text                        |
Indexes:
 "searches_helper_query_index" btree (md5(helper_query))

production=# EXPLAIN SELECT  id FROM "searches" WHERE md5(helper_query) = md5('abcd');
                                  QUERY PLAN
-------------------------------------------------------------------------------
 Seq Scan on searches  (cost=10000000000.00..10000017792.92 rows=1631 width=4)
   Filter: (md5(helper_query) = 'e2fc714c4727ee9395f324cd2e7f331f'::text)
(2 rows)

The table has about 7000 rows and many other columns and indexes. I only included the relevant column.

I ran vacuum verbose analyze on the table but it does not change anything either.

I also tried USING hash since I only need equality but it did not help either.

"searches_helper_query_index" hash (md5(helper_query))

As a comment this seems to only be with Postgresql 9.5.0. Either PostGIS or regular Postgres. My development machine has 9.5.1 and does not have this issue.

 dev=# EXPLAIN SELECT  id FROM "searches" WHERE md5(helper_query) = md5('abcd') ;
                                     QUERY PLAN
 Index Scan using searches_helper_query_index on searches (cost=0.27..8.29 rows=1 width=4)
 Index Cond: (md5(helper_query) = 'e2fc714c4727ee9395f324cd2e7f331f'::text) (2 rows)

Your WHERE condition is not selective enough. It returns 1631 rows which is 1/5 of the whole table, while it should be not more than 1/20 for an index to be used.


Make sure two columns are sorted in the same order I have a table like this:

CREATE TABLE T (
 ID INT NOT NULL IDENTITY PRIMARY KEY,
 CreateDateTime datetime2 NOT NULL,
 --other columns
)

How can I make sure that ID and CreateDateTime are both sorted in the same order? In other words, the following queries would return an identical order:

select * from T order by ID
select * from T order by CreateDateTime

I need this guarantee because for performance reasons I want to query a date range by filtering on the ID column. This is possible if the guarantee holds.

I want to fail DML that would violate that property. My intention is to catch bugs breaking this invariant.

Is there a way to do this without using triggers? My hope is that there is a more elegant solution.

I don't think it's easy to make such a guarantee and even if you do manage to make such an assertion actually work, it may slow down your Inserts into the table.

Since this is a performance issue for the ordering queries, here's a suggestion:

Make (CreateDateTime, ID) or (CreateDateTime) the clustered index of the table, instead of the (usual) clustered index on (ID) alone.

Then you can run queries with ORDER BY CreateDateTime and they will use the clustered index.


How to add a column in table via stored procedure? create or replace procedure SP_PARAMETER_TEST -- this is my table
(
       NAME_KEY in VARCHAR2 -- this contains a value assume NAME_KEY contains "Gypsum"
)
is
begin
  ALTER TABLE PARAMETER_TEST ADD NAME_KEY VARCHAR(2);
end SP_PARAMETER_TEST;

I want to add the column which has text value "Gypsum". But I am getting errors. How can I do this?

You can't embed DDL in a stored procedure like this.

Either use execute immediate and your DDL statement passed to it as a string, or, better, remove the stored procedure entirely an execute the DDL directly.


Is it a good idea to create an index for each column in a new table? I will short my question. (SQL-Server 2008)

Is it a good idea , when crating a new table, to create an index for each column?

Why i'm saying this?

Today I was creating a database, and my boss was behind me, and he said:

"Oh, it's good to create an index, for every column" (and then he showed me the graphic way to do this).

I'm studying indexes now and I'm not sure if this is a good idea, since every index will be updated everytime time there will be an insert\update on the table.

Some advices?

Would you find it useful to have the phone book, only having it as a list of Surnames with phone numbers, plus a list of Firstnames with phone numbers, and a list of towns with phone numbers? Because essentially, that's what you're getting if you index each column individually.


How to avoid Functions from a Performance Stand-point in Azure SQL Database? I read this article quite recently on the performance issues to do with  Functions.

I'm currently in the development phase of a new database on the Azure SQL Database platform. It won't go live for a new months yet. I'm using a variety of scalar-valued functions with one being to convert UTC to Local Date where it is specified in the Config table, e.g. AUS Eastern Standard Time

The function is called as the following:

SELECT
    dbo.fnGetLocalDate(DateColumn) as DateColumn,
FROM
    table

Does anyone have any suggestions on how to avoid functions in cases such as this? I find them useful for code reuse and I'm not sure how to avoid it here.

I'm also wondering whether with vNext fixing the performance issues with Functions in the Azure platform that I'm better off continuing to work with functions.

CREATE FUNCTION [dbo].[fnGetLocalDate]
(
    @DateToConvert datetimeoffset = NULL
)
RETURNS datetimeoffset
AS
BEGIN

    DECLARE @TimeZone varchar(50)

    RETURN
    CASE
        WHEN @DateToConvert is NULL then NULL
    ELSE
        CONVERT(datetimeoffset, @DateToConvert AT TIME ZONE (SELECT Value FROM LookUp.Config WHERE Property = 'TimeZone'))
    END

END

You can rewrite it as an inline TVF returning a single column and row and CROSS APPLY it to get the benefits of inlining now (parallelism, no overhead of switching execution contexts, holistic query costing and optimisation) without having to wait for the work done on inlining of Scalar UDFs to get released.

So your function definition would be

CREATE FUNCTION [dbo].[fnGetLocalDate] (@DateToConvert DATETIMEOFFSET = NULL)
RETURNS TABLE
AS
    RETURN
      SELECT CONVERT(DATETIMEOFFSET, @DateToConvert AT TIME ZONE
                                     (SELECT Value
                                      FROM   LookUp.Config
                                      WHERE  Property = 'TimeZone')) AS
                    LocalDate

With example usage

SELECT o.name, L.LocalDate
FROM sys.objects o
CROSS APPLY [dbo].[fnGetLocalDate](o.modify_date) AS L

You don't really need the CASE as it will return NULL on NULL input anyway and may get better plans without it


Merge 2 columns from joined table lets say I have 2 tables :

group
{
     id,
     export boolean not null default false
}

and

channel
{
     id,
     group_id null,
     export boolean null
}

Is it possible, with 1 query, to request the export boolean value for a specified channel id such as :

channel.export is not null return it as is
if channel.export is null and channel.group_id is null return false
if channel.export is null and channel.group_d is not null return group.export

I am using postgresql 10.

This seems a little bit hard.

You requirement can be translated into a CASE statement expression almost word for word.

SELECT
CASE
    WHEN channel.export is not null then channel.export
    WHEN channel.group_id is null then false
    ELSE "group".export
END as merged_bool
FROM channel LEFT JOIN "group" ON (group_id="group".id);


How to improve INSERT speed in large table that has unique constraint I have a simple MyISAM table:

explain entities;
+------------+-------------+------+-----+-------------------+----------------+
| Field      | Type        | Null | Key | Default           | Extra          |
+------------+-------------+------+-----+-------------------+----------------+
| id         | int(11)     | NO   | PRI | NULL              | auto_increment |
| name       | varchar(50) | YES  | UNI | NULL              |                |
| created_at | timestamp   | NO   |     | CURRENT_TIMESTAMP |                |
+------------+-------------+------+-----+-------------------+----------------+

that is expected to have 10s of millions of rows (or even more) eventually. I noticed that as the number of rows in the table increases, insert statements start to take longer. This is expected, as the documentation states that "the size of the table slows down the insertion of indexes by log N, assuming B-tree indexes". I read in another question that LOAD INDEX INTO CACHE can be used to improve insert speed, but I found this to have a negligible effect. Are there any other pointers on how to improve insertion speed, other than the ones listed here? For example, would partitioning the table help with insertion speed? What guidelines can I use for determining if my insertion speeds are already at a near optimum?

System info:
MySQL 5.1.67-0ubuntu0.10.04.1
Running on Linode VM with 512 MB RAM and quad-core processor

I think the problem could be your RAM, for LOAD INDEX INTO CACHE to make a difference you should have the index size < key_buffer_size < Available RAM

To see your current index size use SHOW TABLE STATUS.

Also LOAD INDEX INTO CACHE works only for MyISAM, for InnoDB you could try the blackhole method:

create table t like innodbtable;
alter table t engine = blackhole;
insert into t select * from innodbtable WHERE indexedRow<>''; -- Get creative in order to stimulate reading of data and indexes you need

source , for more information on InnoDB pre-loading see this and this.

To estimate index usage see this or this .

For example I have a table with 32 Mil records in it and the data size is 2.33 GB (2,496,806,620) and the index size is 2.08 GB (2,230,159,360). So your table should be at least 5 times smaller to be able to fit the RAM limit. Also don't forget I said Available RAM = the ram left after excluding system, mysql (other than the key_buffer_size) & other software, so that would leave you at best with 350MB.

Here is my table for reference:

CREATE TABLE `table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `a_id` int(10) unsigned DEFAULT NULL,
  `b_id` int(10) unsigned DEFAULT NULL,
  `varchar1` varchar(255) DEFAULT NULL,
  `varchar2` varchar(255) DEFAULT NULL,
  `varchar3` varchar(8) DEFAULT NULL,
  `lastModified` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `links_id_email` (`a_id`,`varchar1`),
  KEY `domain_index` (`varchar2`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


How to return duplicates if IN (...) has duplicates? I have a list containing 1, 1, 2.

I am using it in a query like this:

SELECT A.* FROM A WHERE id IN (1, 1, 2)

The output is this:

id
---
1
2

I want the results to be like this:

id
---
1
1
2

How can I achieve that?

You can do this with a JOIN:

SELECT a.*
FROM a
  JOIN
    ( VALUES (1), (1), (2)
    ) AS p (id)
  ON a.id = p.id ;


would it be possible in postgres to get a count of sql hits on a select statement Say I have the following table in Postgres 9.4:

items
id |  name             |   tag_ids
1     tire                  -1--2-
2     wheel                 -1--3-
3     transmisson           -3-

I'd like to do something like:

select id, tags from items where tag_ids ilike '%-1-%' or tags ilike '%-3-%';

but if it hit twice (like id 2), return a count of hits. Would this be possible? Or possibly have it come back
as two records and then process in application code.

edit

ok, so assuming we had a second table like this:

items_tags
item_id  tag_id
1         1
1         2
2         1
2         3
3         3

Is there a way to join them and then group by the number of "hits" - I guess just selected rows and then order in reverse? And give a represntation of the hit tag_ids

select i.name from items inner join items_tags using (id) where items_tags.tag_id in (1,3);

but how would I do a count and order. I'm assuming a group by but this is beyond me.

edit 2

so I can't get this second query to work. It seems like it should but isn't:

select tag_ids, (tag_ids ilike '%-1-%')::int + (tag_ids ilike '%-11-%')::int as hits
from items order by  hits desc;

saying ERROR:  column "hits" does not exist

select tag_ids, (tag_ids ilike '%-1-%')::int + (tag_ids ilike '%-11-%')::int
as hits from items where hits > 0 order by  hits desc;

Yes, you can use this in the select list:

 (case when tags ilike '%-1-%' then 1 else 0 end)
 + (case when tags ilike '%-3-%' then 1 else 0 end)
   as hits

or alternatively (convert the boolean values to integers (TRUE -> 1, FALSE -> 0) and then add:

 (tags ilike '%-1-%')::int + (tags ilike '%-3-%')::int
   as hits

But the query (and many others) would be much easier if you normalized the design and store the item-tag relationship in a separate, junction table.


Master_ master replication on Mariadb I have a nagios application running on 2 servers, i am planning to have have master-master replication for HA. for master-slave replication, slave should be read-only. in this manner is there any pre-req for master-master replication. while doing master-master replication i ran into a lot of duplicate as both the databases are auto-increment on their own and trying to replicate each other.

can some one please help to resolve the issue.

Thanks,

To avoid the issue with duplicate keys when using auto_increment PKs, you need to configure the system variables auto_increment_increment and auto_increment_offset.

From the MariaDB Knowledgebase: AUTO_INCREMENT:

  To make master-master or Galera safe to use AUTO_INCREMENT one should
  use the system variables auto_increment_increment and
  auto_increment_offset to generate unique values for each server.

So in other words, with two masters: On master1 set auto_increment_increment=2 and auto_increment_offset=1, and on master2 set auto_increment_increment=2 and auto_increment_offset=2.


Merge statement with two conditions in same CLAUSE alternative in Postgres I was able to convert a Merge which has one condition in both MATCHED & UNMATCHED clauses using CTE. But not sure how to do (both DELETE & UPDATE), if I have two conditions in the same clause.
Below is the sample example:

SELECT * FROM source;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5

SELECT * FROM destination;

         1         20 Description of level 1
         2         10 Description of level 2
         3         20 Description of level 3
         4         10 Description of level 4
         5         20 Description of level 5
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

I have Merge implementation in ORACLE like below:

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET d.description = 'Updated'
    DELETE WHERE d.status = 10;

5 rows merged.

SELECT * FROM destination;

        ID     STATUS DESCRIPTION
---------- ---------- -----------------------
         1         20 Updated
         3         20 Updated
         5         20 Updated
         6         10 Description of level 6
         7         20 Description of level 7
         8         10 Description of level 8
         9         20 Description of level 9
        10         10 Description of level 10

8 rows selected.

If the above code will have as below:

MERGE INTO destination d
  USING source s
    ON (s.id = d.id)
  WHEN MATCHED THEN
    UPDATE SET d.description = 'Updated'
    DELETE WHERE d.status = 10
  WHEN NOT MATCHED THEN
     INSERT (ID, STATUS, DESCRIPTION) VALUES (s.id,s.status,s.description);

Please help me how to re write the above code using WITH in Postgres.

As far as I know, Postgres (10) doesn't allow MERGE statement. You can simulate it in this way:

create table new_data (id int, status int, description text);
insert into new_data values
(1, 20, 'Description of level 1'),
(2, 10, 'Description of level 2'),
(3, 20, 'Description of level 3'),
(4, 10, 'Description of level 4'),
(5, 20, 'Description of level 5');

create table destination (id int, status int, description text);
insert into destination values
( 1, 20,  'Description of level 1'),
( 2, 10,  'Description of level 2'),
( 3, 20,  'Description of level 3'),
( 4, 10,  'Description of level 4'),
( 5, 20,  'Description of level 5'),
( 6, 10,  'Description of level 6'),
( 7, 20,  'Description of level 7'),
( 8, 10,  'Description of level 8'),
( 9, 20,  'Description of level 9'),
(10, 10, 'Description of level 10');

delete from destination d
using new_data n
where n.id = d.id
and   n.status = 10;

update destination d
set    description = 'Updated'
from   new_data n
where  d.id = n.id;

insert    into destination
select    n.id, n.status, n.description
from      new_data n
left join destination d
on        n.id = d.id
where     d.id is null
and       n.status <> 10;

2 rows affected

3 rows affected

✓

select *
from   destination
order by id;

id | status | description
-: | -----: | :----------------------
 1 |     20 | Updated
 3 |     20 | Updated
 5 |     20 | Updated
 6 |     10 | Description of level 6
 7 |     20 | Description of level 7
 8 |     10 | Description of level 8
 9 |     20 | Description of level 9
10 |     10 | Description of level 10

dbfiddle here

Using INSERT - ON CONFLICT DO

begin;

    insert into destination
    select n.id, n.status, n.description
    from   new_data n
    on conflict (id)
    do update
    set description = 'Updated';

    delete from destination
    where  id in (select id from new_data where status = 10);

commit;

dbfiddle here

Using CTE

WITH
  u AS
( update destination d
  set    description = 'Updated'
  from   new_data n
  where  d.id = n.id
    and  n.status is distinct from 10
),
  d AS
( delete from destination d
  using new_data n
  where n.id = d.id
    and n.status = 10
),
  i AS
( insert    into destination
  select    n.id, n.status, n.description
  from      new_data n
  left join destination d
  on        n.id = d.id
  where     d.id is null
)
select 1 ;

dbfiddle here


SQL Server 2008 DB size grown huge I am a .net developer and not a expert in database. I have a application running with SQL server 2008. Yesterday there was issue with a certain query getting executed in infinite loop and which caused DB size to grow from 500 MB to 380 GB. The log file itself was 300 GB and mdf file 80 GB. Today I stopped the query and truncated the log file to 12 MB. Then I figured out tables which were grown and truncated those tables as well but still my db size is 67 GB! I ran query to determine the size of all tables https://stackoverflow.com/a/7892349/287100

But the total size of all tables was less than 100 MB. I want to know why the db size is huge and how can I reduce it to normal.

Thanks.

Are you using a  simple recovery mode? if you do not need the full mode this could help.

How big is the Log file now? maby it got huge again according to a full recovery mode, try to shrink it again if you change to the simple recovery mode and check it then.


MySQL query cache with time based expiration The database we use has a read and write intensive load. Some read queries are heavy.

Unfortunately the Query Cache cannot be used as it is invalidated too quickly.

We wanted to avoid the classic Memcached/MySQL couple to simplify the architecture and decrease connections/latency.

Is there a strategy to add a "time based cache" in MySQL? (ideally per collection)

EDIT:

Each query group has a different TTL (from 5 seconds to several days).

I'll assume your database is all InnoDB when answering this question.

What you are asking for is not possible for the Query Cache. The mechanisms you have in mind do exist with the InnoDB Buffer Pool.

What you should try is the following:

Disable the Query Cache (Why query_cache_type is disabled by default start from MySQL 5.6?)
Make the InnoDB Buffer Pool Resistant to Scans

Increasing innodb_old_blocks_pct
Increasing innodb_old_blocks_time

This controls what and how long blocks of data and index are to stay inside the Buffer Pool

To release blocks after a while, you would change innodb_old_blocks_pct back to 37


Will adding more indexes speed up using RedGate SQL DATA compare? We want to use RedGate's SQL Data Compare to migrate user data from one DB to a DB on a completely different provider. This will be a time-sensitive operation as we do not want our site offline for too long and there will be a lot of data that needs to be moved. We do not want any I/O on the table during this time.

Has anyone using RedGate found that adding additional indexes on some of our comparison criteria columns (i.e. DateCreated, DateModified) will affect the time to perform this? Our initial testing is at about 25-30 min for comparison of the source -> target DBs at the moment.

The question suggests that the tool is being used to migrate data and not schema, which implies that you are referring to SQL Data Compare and not SQL Compare.

In which case, yes, indexes on the comparison key columns will speed on SQL Data Compare.

I've consulted the SQL Data Compare project manager, who provides the following insight: "Usually performance problems with SDC have more to do with the network connection speed to the server and/or the disk speed on the location of the temp files, neither of which are affected by having an index on the comparison key"

SQL Data Compare performance tips are part of the SQL Data Compare documentation.


RDS Oracle SE - Performance troubleshooting options I have an RDS Oracle SE2 instance with Amazon RDS. Our team is looking forward to move couple of applications to RDS. But we’re looking for options that are available in RDS Oracle SE to perform Performance tuning and management.

As of now I’m aware I can use Statspack. With Statspack not being supported by Oracle now, I’m looking for other options compatible with AWS RDS. Will OUTLINES work with RDS for Plan management ?

Has anyone tried any other tools that could help? Please advise.

You may want to run an EC2 instance as a dev environment. Given that you don't have access to the file system on an RDS database and that you have standard edition, you can't turn on 10046 and 10053 events and use tkprof to read the files, you can't use active session history (ASH) and Automatic Workload Repository (AWR) since you need to have enterprise to use those reports and Oracle would know if you used any of those features in an audit situation.

Your best bet is to look for blocking locks in prod and use and EC2 instance to do load testing possibly using set events 10046 and 10053. There might be another tool that you can use in RDS. I don't know what that would be. You are limited in what you can do give that you are running standard edition on an RDS instance.


How to optimize InnoDB tables in MySQL I’ve been researching how to optimize only fragmented tables in MySQL and reviewed this post on optimizing tables. It basically performs a query against the information_schema database for any table with data_free > 0 and builds a SQL statement to OPTIMIZE only those tables. I ran this query and it identified 148 tables for optimization. All of the tables identified are InnoDB tables. After executing the resultant optimization SQL script, I re-ran the original script to identify fragmented tables and it returned the exact same tables during the first pass.

I have seen conflicting posts regarding the InnoDB tables and the OPTIMIZE command. Some say that OPTIMIZE will not work with InnoDB tables and that you need to run ALTER TABLE table_name ENGINE=INNODB. Others say that OPTIMIZE actually calls the ALTER TABLE command when executing against InnoDB tables. With that in mind, I ran the ALTER TABLE command against one of the InnoDB tables identified as being fragmented (data_free > 0) and found that the data_free did not change afterwards. It’s still greater than 0. I also re-started MySQL and checked it only to find the same results.

Now, we have several servers running MySQL 5.5.29 in our organization and I ran a query against all of them to identify any InnoDB tables with DATA_FREE=0 or NULL and none were returned. They are all greater than zero.

I also ran the OPTIMIZE command against a few MyISAM tables where DATA_FREE was greater than zero and verify that it was zero afterwards.

Can anyone shed some light on this for me? What is the proper method to remove fragmentation from InnoDB tables? What is the proper method to determine fragmented InnoDB tables?

Thanks

I'll assume you are using innodb_file_per_table for this answer.

There is more than one meaning to "InnoDB fragmentation":

.ibd file is fragmented, and is very large whereas the dataset is small
Index pages are fragmented in that there are too many pages to contain little data, in which case they could be merged.

Please consider this post I wrote a while back: it shows how after purging many rows from a large table, the data file is fragmented (i.e. it is very large in the filesystem -- it's a known issue these files never reduce in size). And yet the indexes were not fragmented by the end of deletion: this is because InnoDB properly merges pages as they become empty(er).

The OPTIMIZE command indeed does not apply on InnoDB. What it does is rebuild the table (exactly like an ALTER). See this:

mysql [localhost] {msandbox} (test) > create table t(id int) engine=innodb;

mysql [localhost] {msandbox} (test) > optimize table t;
+--------+----------+----------+-------------------------------------------------------------------+
| Table  | Op       | Msg_type | Msg_text                                                          |
+--------+----------+----------+-------------------------------------------------------------------+
| test.t | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| test.t | optimize | status   | OK                                                                |
+--------+----------+----------+-------------------------------------------------------------------+

As for DATA_FREE: I suggest that you simply ignore this variable. To be honest, I've been working with InnoDB tables for 10 years, and have never found this value to be very consistent with anything.

And now it's time for the real discussion: what exactly are you trying to achieve? Unless your database is completely stale, there will always be some fragmentation. It is natural to the process of adding, removing and updating rows in your table.

Fragmentation is not that evil: free space can get reclaimed by new data. If you tables are not very large, then just forget about the whole thing. For very large tables, you might gain some disk space by optimizing the table. But ask yourself: how soon would the table reach same fragmentation? An hour? A day? A week? IMHO in all these cases it is pointless to optimize the table.

Nevertheless, if a large table is massively purged of data, which is not expected to return, I'm all for optimizing it. Say you realize you have some redundant data which consists some 30% of your table size. Sure, it would be great to have that disk space back.

Bottom line: only consider these issues with very large tables; only if you have issues with disk space.


How to aggregate results from SELECT JOIN I have two tables:

**user Table **

id   username
---  -----------
100  greg
101  john
102  patrick
103  bill

**user_subscription_plan Table **

id   user_id     amount
---  ---------   -------
1   100           50
2   100           52
3   101           10
4   103           100
5   103           110

I would like to make a query on the user table to extract username, but also to include id's from USER_SUBSCRIPTION which are related to the user_id

This is what I was trying:

SELECT
   u.id AS user_id,
   u.username AS username,
   usp.id AS subscription_id
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id;

It gets me the results, but not in the form I need. If the user has two subscription entries, then it returns two rows for each subscription.

I would need to get only ids from Subscription table. Something like this:

SELECT
   u.id AS user_id,
   u.username AS username,
   usp.id AS subscription_ids -- Array of ids for each user
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id;

Try with this

SELECT
   u.id AS user_id,
   u.username AS username,
   GROUP_CONCAT(usp.id) AS subscription_ids -- Array of ids for each user
   FROM user AS u
INNER JOIN user_subscription_plan usp on usp.id = u.id
GROUP BY u.id,u.username;

https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php#SP


Prevent MySql from mangling queries by casting string to int On one of my tables I have an auto incrementing ID field. It also has another field (richter_code) with a unique constraint but, over the seasons, this field can change - that's why I'm not using it as the primary key.

In my program's code, I have a load function. First thing it does is check the richter_code field for the search parameter. If it doesn't return anything, it then goes on to search the on the ID field for the same parameter.

The problem is that it seems to truncate the value as soon as it encounters an alpha character. So I'm getting complete garbage from my query. See screenshot for example.
Can I prevent mysql from changing the query?

Gotta love MySql ;)

I searched Stack Overflow for a MySql equivalent to some isnumeric function, and found this.

This rather convoluted WHERE clause would return the expected 0 rows:

where Id = case when concat('','3R100C'*1) = '3R100C' then '3R100C' else null end

The idea is to only match the parameter against Id when it is a valid numeric value. Hope it helps!

I'd create an actual isinteger function to abstract away this sheer madness:

CREATE FUNCTION `isinteger`(v varchar(255)) RETURNS bit(1)
BEGIN

    RETURN CASE WHEN CONCAT('', v * 1) = v THEN 1 ELSE 0 END;

END

That said I don't think it's right to use an auto-incremented Id field as a search-fallback column - this ID is your table's primary key, its meaning belongs strictly on the database side, no program should ever care about it, even less a program's user.


MariaDB reset table index value? I was wondering what to do when for example your table is reaching it's maximum index values, I was using this Query to determine the index usage per table:

SELECT
  c.TABLE_NAME,
  c.COLUMN_TYPE,
  c.MAX_VALUE,
  t.AUTO_INCREMENT,
  IF (c.MAX_VALUE > 0, ROUND(100 * t.AUTO_INCREMENT / c.MAX_VALUE, 2), -1) AS "Usage (%)"
FROM
  (SELECT
     TABLE_SCHEMA,
     TABLE_NAME,
     COLUMN_TYPE,
     CASE
        WHEN COLUMN_TYPE LIKE 'tinyint(1)' THEN 127
        WHEN COLUMN_TYPE LIKE 'tinyint(1) unsigned' THEN 255
        WHEN COLUMN_TYPE LIKE 'smallint(%)' THEN 32767
        WHEN COLUMN_TYPE LIKE 'smallint(%) unsigned' THEN 65535
        WHEN COLUMN_TYPE LIKE 'mediumint(%)' THEN 8388607
        WHEN COLUMN_TYPE LIKE 'mediumint(%) unsigned' THEN 16777215
        WHEN COLUMN_TYPE LIKE 'int(%)' THEN 2147483647
        WHEN COLUMN_TYPE LIKE 'int(%) unsigned' THEN 4294967295
        WHEN COLUMN_TYPE LIKE 'bigint(%)' THEN 9223372036854775807
        WHEN COLUMN_TYPE LIKE 'bigint(%) unsigned' THEN 0
        ELSE 0
     END AS "MAX_VALUE"
   FROM
     INFORMATION_SCHEMA.COLUMNS
     WHERE EXTRA LIKE '%auto_increment%'
   ) c

   JOIN INFORMATION_SCHEMA.TABLES t ON (t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME)

WHERE
 c.TABLE_SCHEMA = 'Database_Name'
ORDER BY
 `Usage (%)` DESC;

Which would return something like this:

+------------------------+-------------+------------+----------------+-----------+
| TABLE_NAME             | COLUMN_TYPE | MAX_VALUE  | AUTO_INCREMENT | Usage (%) |
+------------------------+-------------+------------+----------------+-----------+
| app_crontasks          | int(11)     | 2147483647 |        1536304 |      0.07 |
| app_alerts             | int(11)     | 2147483647 |              1 |      0.00 |
| app_apiclients         | int(11)     | 2147483647 |              2 |      0.00 |
| app_replicates         | int(11)     | 2147483647 |              1 |      0.00 |
| ...                    | ...         | ...        | ...            | ...       |
+------------------------+-------------+------------+----------------+-----------+

In case it fills up to, for example, 75% then we would need to do a clean up of the database? How am I able to safely do that? Would it affect the foreign keys assigned?

On a related note:  There are several ways that auto_inc ids can be "burned".  For example, INSERT IGNORE will allocate as many ids as it could need, but waste any that it fails to use.  Ditto for IODKU.  REPLACE (which is mostly obviated by IODKU) is effectively DELETE + INSERT, hence always burns id(s).

Whereas changing the datatype to a bigger INT will keep you going, it may be better to worry about the "burning" of ids.  That is, find and fix the code.

Normalization tends to burn often.  Here's an approach to avoid wasting ids: http://mysql.rjweb.org/doc.php/staging_table#normalization


The multi-part identifier > "a.surveyid" could not be bound I am getting the error :

  Msg 4104, Level 16, State 1, Line 1 The multi-part identifier
  "a.surveyid" could not be bound.

Below is the query ,what could be the issue?

 SELECT a.surveyid,*
    FROM [360HRSurvey] where sempid IN (
     SELECT  a.EmpCode
     FROM surveyusers a
     where a.surveyid = 80 and a.EmpCode NOT IN (
      SELECT p.EmpID
      FROM empsurveyselection p
      WHERE p.surveyid =80)
    and empid =   (
      SELECT empid
      from [360HRSurveyEmployee]
      where surveyid = 80)
    )

You need to use surveyid not a.surveyid. Because surveyusers as a declare in sub-query so you can not access a.surveyid in main query.