Using Service Broker internal to a single SQL Express instance? I understand that SQL Express cannot send Service Broker messages to other instances without a Standard (or better) instance involved in the chain.  But is it possible to use Service Broker to send and receive messages within a single Express instance?  In other words, the queues and services are all owned by (and never leave) the initiating SQL instance.

From what I understand the limitation only applies when you attempt to send messages outside of the instance. Sending messages within the same instance is not limited by using Express. See the last reply in the following link:

http://social.msdn.microsoft.com/Forums/en-US/sqlservicebroker/thread/3b03a610-8159-4be8-b2d8-e2abb4ba1225

I hope this helps you.


Problem to apply replacement with regexp that include look ahead in Postgres I have this kind of string 12s2-3a abc def and I am trying to apply the regexp ^(?=.*\d) to remove the first part and having as left abc def.

I have tried with:

select substring ('12s2-3 abc def' from '^(?=.*\d)');

and

select regexp_replace ('12s2-3 abc def', '^(?=.*\d)', '');

but none of those has worked. The only one that looks working is the match verification (~).

Any idea?

Look-ahead are zero width.  They don't match anything themselves.  So your replacement operations is just replacing the empty string with the empty string, under certain conditions.

Simply removing the look-ahead designation makes it give the answer you want for your one example:

select regexp_replace ('12s2-3 abc def', '^.*\d', '');


Best practice for securely backing up RDS Postgres "offsite"? We're running Postgres on RDS, which is largely great.  The big issue with it, however, is to do with AWS' security model, which allows anyone with various permissions to delete everything - your DBs, your backups, the whole lot.

In particular, you can't prevent someone with access to create IAM users and groups from being able to then give a new user more permissions than they themselves have, so either compromised credentials or a disgruntled employee could destroy everything if you rely on RDS' own backups.

EDIT:

Just in case you're wondering what the issue might be, have a quick read of http://www.infoworld.com/article/2608076/data-center/murder-in-the-amazon-cloud.html

So, the "sensible" thing to do seems to be to have a separate AWS account on which you have basically no one having any access, and have a key which can write stuff up to S3 (and possibly read it back if you fancy, though this is probably optional).

This way, you can back things up to an account from which your main AWS admins can't, by accident or design, delete stuff, and then use lifecycle rules to manage it.

Sorry for the long build up - I am literally amazed that people don't seem to have asked/answered this before, as it seems such an obvious thing for almost anyone using RDS (or indeed just AWS) to need to do, but...

How do I backup Postgres in a sensible fashion for this?

Some things to consider:

Storage space and so on aren't infinite, so ideally don't want to be doing a full pg_dump/gzip/encrypt/upload to S3, which is the obvious solution, as it would mean hundreds of Gb a day going up there which is probably overkill.
We don't have access to the "core" servers, so can't do differential type backups as would be more "normal"

Would it work to do a pg_dump, then use some sort of diff program to only upload/store the diffs?  Since I don't think pg_dump produces things in a specific order, I'm not sure this would work (in the way intended)?

Any other ideas?

I'm aware by the way of the use of manual snapshot sharing - https://aws.amazon.com/blogs/aws/amazon-rds-update-cross-account-snapshot-sharing/ - which works great if you're using unencrypted RDS - but we aren't, for reasons I can't fully explain except that "it sounds good".

I think your way of thinking with a separate machine in AWS which has access to the db is generally the right way to do it, and would just add that the "normal" way of making incremental backups (with any "real" database) is named PITR, for Point-in-time-recovery. If you search for this term and PostgreSQL, you will find tutorials on how to do generic incremental database backups, and it's up to you to implement them using AWS.


TSQL issues with view and data operations I can't get my head around this sooo - help! ;)

Working with SQL Server here.

I have a few tables that hold customer sales, products, customer and other data imported from SAP.

I have create a view that combined all this data into a single easy to read "table". The only caveat being the data coming from SAP for each sale is group by sale period of month.year (09.2018, 10.2018, 11.2018, etc).

My import script break this data into two columns, month & year.
Now, because I will have to run queries with date function on this view I needed a datetime field, so in the view definition I essentially did this:

CONVERT(datetime, CONCAT(a.period_year,'-',a.period_month,'-01')) AS period_date

first create a string value of the date adding day (01) and then convert this to datetime type.

This view will not hold any future data, but for the sake of argument & testing I run this query:

SELECT * FROM my_sales_view WHERE period_date <= getdate();

I get all records returned to me, no problem.
But, let say I need all data from past 12 months, from entire month, meaning if I run the query on 15th of May 2019 I need records from 1st of June 2018, not 16 of June.

This will give me that date:

SELECT DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

so, I though by running this I would get what I want:

SELECT * FROM my_sales_view WHERE period_date > DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

But I don't. Instead the query returns the following error:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

However when I run this:

SELECT * FROM my_sales_view WHERE period_date < DATEADD(month, DATEDIFF(month, -12, getdate()), 0)

by just inverting the operator and getting the date older then 1 year ago it works fine!

So, what gives?! This makes no sense to me, what am I missing here.

Sounds like you have at least one row where the combination of year/month is not valid (and only with certain plans is the conversion attempted before that row is filtered away). To be certain, we should see the full definition of the view, the actual plan for the query that works, and the estimated plan for the query that fails (if you can get it).

First, I suggest that instead of breaking the string from SAP apart into separate components, you import the data into the correct data type from the start. But that's a different story. Given what you have, I would identify the bad rows this way:

SELECT [key?], period_year, period_month
  FROM dbo.[table shown as "a" in my_sales_view]
  WHERE TRY_CONVERT(datetime,CONCAT(period_year,
    RIGHT('0'+RTRIM(period_month),2),'01')) IS NULL;

And when you find the bad rows and fix them, your current view will work, but if you don't make any other changes, this will just be temporary and bad data will happen again. So your view can say the following instead of all this CONCAT nonsense:

TRY_CONVERT(datetime,CONCAT(a.period_year,
  RIGHT('0'+RTRIM(a.period_month),2),'01'))) AS period_date

Of course this will mean that no matter what predicate you apply to period_date, those bad rows won't appear in the output. Hopefully you wouldn't expect them to, but without the error, it won't be obvious those rows are missing.


Pgpool - semanage config files - which filetype should I choose? Reffering to problem with pgpool installation :
Problem with pgpoolAdmin installation
I had a problem with non running pgpooladmin(installation files permission was wrong).
I must change file label of files: "pgpool.conf" and "pcp.conf". I checked those files with ls -Z and saw this:

[root@cvc etc]# ls -Z
-rw-rw-rw-. apache root unconfined_u:object_r:etc_t:s0   pcp.conf
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0       pcp.conf.sample
-rw-rw-rw-. apache root unconfined_u:object_r:etc_t:s0   pgpool.conf
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0   pgpool.conf.sample
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0 pgpool.conf.sample-master-slave
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0  pgpool.conf.sample-replication
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0   pgpool.conf.sample-stream
-rw-rw-rw-. root   root system_u:object_r:etc_t:s0   pool_hba.conf.sample

Communicate from SELinux tolds:

You should change label of pgpool.conf with
semanage fcontext -a -t FILE_TYPE 'pgpool.conf',
which FILE_TYPE is one of below:
squirrelmail_spool_t,
dirsrvadmin_config_t,
afs_cache_t, httpd_t,
user_cron_spool_t,
httpd_lock_t,
dirsrv_config_t,
httpd_tmp_t,
dirsrvadmin_tmp_t,
httpd_cache_t,
httpd_tmpfs_t,
httpd_squirrelmail_t,
puppet_tmp_t,
dirsrv_var_log_t,
zarafa_var_lib_t,
dirsrv_var_run_t,
httpd_var_lib_t,
httpd_var_run_t,
httpd_nagios_rw_content_t,
passenger_tmp_t,
httpd_nutups_cgi_rw_content_t,
httpd_apcupsd_cgi_rw_content_t,
httpd_dspam_rw_content_t,
httpd_mediawiki_rw_content_t,
httpd_squid_rw_content_t,
httpd_prewikka_rw_content_t,
httpd_smokeping_cgi_rw_content_t,
passenger_var_run_t,
httpd_openshift_rw_content_t,
httpd_dirsrvadmin_rw_content_t,
httpd_w3c_validator_rw_content_t,
cluster_var_lib_t,
cluster_var_run_t,
httpd_user_rw_content_t,
httpd_awstats_rw_content_t,
root_t,
httpdcontent,
httpd_cobbler_rw_content_t,
httpd_munin_rw_content_t,
cluster_conf_t,
cluster_tmp_t,
httpd_bugzilla_rw_content_t,
httpd_cvs_rw_content_t,
httpd_git_rw_content_t,
httpd_sys_rw_content_t,
httpd_sys_rw_content_t
Next you have to execute:
restorecon -v 'pgpool.conf'

When I try to change filetype it tolds me that choosen one is wrong... which filetype should I choose?

Solution founded:

To repair this issue I must execute this comand:

  chcon -t httpd_user_rw_content_t pgpool.conf

and

  chcon -t httpd_user_rw_content_t pcp.conf

then pgpooladmin will show that those files are writable and will allow to click "Next" to install pgpoolAdmin.
Yay Cheers :D


Is it possible to join MySQL 8.0 to a cluster of MySQL 5.7 I have a MySQL 5.7 (5.7.19-17-57-log Percona XtraDB Cluster) cluster using Galera, but I am in the middle of upgrading to 8.0 -- 8.0.23-14 Percona Server (GPL), Release '14'.  I wasn't sure if it was possible to join the 8.0 box to the clustered 5.7.

It's not possible to make a Percona MySQL server 8.0 join a Percona XtraDB Cluster, regardless of version. The 8.0 server has to be Percona XtraDB, otherwise it won't have the necessary Galera libraries and bindings.


Can't insert arabic text into mysql database using mysql prompt I had a local table with arabic values.when exported i got the following query.

DROP TABLE IF EXISTS `type`;
 CREATE TABLE IF NOT EXISTS `type` (
 `bs_id` int(11) NOT NULL AUTO_INCREMENT,
 `types` varchar(250) NOT NULL,
  PRIMARY KEY (`bs_id`)
 ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

--
-- Data for table `business_type`
--

INSERT INTO `type` (`bs_id`, `types`) VALUES
 ('1', 'صناعة'),
 ('2', 'معارض'),
 ('3', 'أسواق'),
 ('4', 'إستيراد وتوزيع'),
 ('5', 'خدمات'),
 ('6', 'إستشارات'),
 ('7', 'أخرى');

I want to create this table on server so i just copied the above query and run it on server terminal.The query works fine but the values in table is showing like ".....".

tried this

 SET NAMES utf8;

before inserting the values,but failed.I don't have a cpanel installed on server.Command prompt is the only option.

Can anyone help!!

Thanks

Since you said you can only use the Command prompt, my suggestion is to shift the character set to one that supports Arabic within the mysql client:

I just ran these commands on my laptop at home (Windows 8.1)

mysql> select * from information_schema.character_sets where description like '%arabic%';
+--------------------+----------------------+----------------+--------+
| CHARACTER_SET_NAME | DEFAULT_COLLATE_NAME | DESCRIPTION    | MAXLEN |
+--------------------+----------------------+----------------+--------+
| cp1256             | cp1256_general_ci    | Windows Arabic |      1 |
+--------------------+----------------------+----------------+--------+
1 row in set (0.00 sec)

mysql> select * from information_schema.global_variables where variable_name like 'version%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION                 | 5.6.21                       |
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION_COMPILE_MACHINE | x86_64                       |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
4 rows in set (0.00 sec)

mysql>

Start up the mysql client using --default-character-set like this

mysql -uroot -p --default-character-set=cp1256

In the event you are running on Linux, make sure you run

select * from information_schema.character_sets where description like '%arabic%';

and make sure you have a character set that support Arabic

Give it a Try !!!

There is a nice post in StackOverflow Save Data in Arabic in MySQL database. It has three nice queries to help inspect your database to make sure data can be saved with the right character set.


UPDATE adds a new row when I merely want to edit an old one Here's my code:

$sql = "UPDATE `news` SET (headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link) ";
$sql .="VALUES('$headline','$text','$startDate','$endDate','$imageLocation','$imagePlacement','$imgStyles','$link') ";
$sql .= "WHERE id = $id";

I've tried REPLACE INTO, INSERT INTO, and all kinds of variations on single quotes, etc., to no avail. I even hardcoded the ID (22) in case that wasn't getting picked up correctly, but every time I try something like this I get a new row, which means I have two entries with slightly different data in each.

The table's primary key is ID and it's set to AUTO_INCREMENT.

Any thoughts on this?

EDIT

Here's the entire code block on the PHP page:

$id = $_GET["newschoice"];

if(empty($headline)) $headline = "none";
if(empty($text)) $text = "none";
if(empty($imageLocation)) $imageLocation = "";
if(empty($imagePlacement)) $imagePlacement = "0";
if(empty($imgStyles)) $imgStyles= "none";
if(empty($link)) $link = "none";

opendatabase();

$sql = "UPDATE `news` SET (headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link) ";
$sql .="VALUES('$headline','$text','$startDate','$endDate','$imageLocation','$imagePlacement','$imgStyles','$link') ";
$sql .= "WHERE id = $id";

if($result = mysqli_query($sql))
  print "
Record successfully added to table!
";
else {
  print "
Problem adding record: ".mysql_error()."
";
}

Then elsewhere there is more to fill the form with the correct table row:

$id = $_GET["newschoice"];

if (isset($id) && $id != "") {

    opendatabase();
    $sql = "select headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link FROM news WHERE id = $id";
    $result = mysql_query($sql);
    $row = mysql_fetch_object($result);
    $left = "";
    $right = "";
    $dbImg = "";
    if ($row->imagePlacement == 0)
        $left = "checked";
    if ($row->imagePlacement == 1)
        $right= "checked";
    if (isset($row->imageLocation)) {
      $dbImg = "imageLocation\">";
    }
    $showCameo = "style=\"display:block\"";
}

  $headline = $_REQUEST["headline"];
  $text= $_REQUEST["text"];
  $startDate= $_REQUEST["startDate"];
  $endDate= $_REQUEST["endDate"];
  $imageLocation= $_REQUEST["imageLocation"];
  $imagePlacement= $_REQUEST["imagePlacement"];
  $imgStyles = $_REQUEST["imgStyles"];
  $link = $_REQUEST["link"];

Apologies if this is hopelessly clunky. I'm not a dba but have had this task thrust upon me.

Check the database and do a select first using the same criteria and see what it returns. As mentioned above, UPDATE will not insert a new row.
I'm positive your select will return more than one row and then you can work back from there to find the issue in your data or  your code.

Also, no need to use INSERT INTO - that will not update but try to insert new records and any records that satisfy your where clause.

You could try to debug and see what value is being passed into the $id variable once you worked out the sql.


How to get MySQL to iterate a subquery in an aggregate select? Because the vocabulary describing what I'd like to do can be so disparate, I haven't had any luck figuring out why MySQL 5.6 is doing what it does.

Consider the following two simple queries:

#1
select sum(amount) as amt,
    (sum(amount) * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50))) as cost
from gas_log gl
where date_format(gl.stamp,'%X')=2013;

#2
select sum(amount) as amt,
    (sum(amount) * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50))) as cost
from gas_log gl
where date_format(gl.stamp,'%X')=2013 group by yearweek(gl.stamp,0);

The second query is identical to the first, with a simple group_by to get the weekly totals instead of the yearly totals. Both queries have near-identical explain outputs:

+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                        |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+
|  1 | PRIMARY            | gl    | ALL  | NULL          | NULL | NULL    | NULL | 7428 | Using where; Using temporary; Using filesort |
|  2 | DEPENDENT SUBQUERY | gp    | ALL  | yw            | NULL | NULL    | NULL |   52 | Using where                                  |
+----+--------------------+-------+------+---------------+------+---------+------+------+----------------------------------------------+

+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type        | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | PRIMARY            | gl    | ALL  | NULL          | NULL | NULL    | NULL | 7428 | Using where |
|  2 | DEPENDENT SUBQUERY | gp    | ALL  | yw            | NULL | NULL    | NULL |   52 | Using where |
+----+--------------------+-------+------+---------------+------+---------+------+------+-------------+

gas_log contains a timestamp of when gas was pumped and how much was pumped. gas_prices looks like this:

10:11:09> select * from gas_prices limit 2;
+------------+--------+-------+--------+
| date       | yw     | gas   | diesel |
+------------+--------+-------+--------+
| 2013-01-07 | 201301 | 3.235 |  3.870 |
| 2013-01-14 | 201302 | 3.265 |  3.834 |
+------------+--------+-------+--------+

For the first query, MySQL only executes the subquery once and uses that value (found by matching the first row retrieved from gas_log against its corresponding week in gas_prices) to multiply against the sum of all gallons logged in the gas log, whereas in the second query it does what I'm actually after: execute the subquery for each of the 52 grouped weeks in 2013, matching each week's gas price accordingly.

Using with rollup on the group by provides an interesting result; instead of using the first gas price in the timerange, it uses the last one! Thus, it still provides an incorrect cost total for any timerange spanning more than week.

Is there a way to rewrite the query to get MySQL to present one total for a given timerange, yet still match each gas_log row to its corresponding price in gas_prices?

(sum(amount * (0.000+coalesce((select gas from gas_prices gp where gp.yw=yearweek(gl.stamp,0)),3.50)))) as cost
Is the answer, as provided by ypercube.

I was victim to order of operations--MySQL was taking the sum of gallons pumped, then multiplying that against the first/last result found from the gas prices sub query. As Michael quoted and noted:

  "The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate."

So to get what I was after, the sum of each entry multiplied against the price of gas during that week, you simply wrap that exact expression in the comfortable embrace of a sum().


Is "SQL Server Auditing" what I'm looking for? Man... this feels like a broad question, so apologies in advance. The word "auditing" seems to have some depth in the DBA world, and I may not be asking the right question when I google.

I'm trying to get my head around how to do some simple "auditing" in SQL Server. I have a subset of user tables for which I want to track changes (INSERTS, UPDATES, and DELETES). I want to know who made the change, when, and what the before and after results were (in the case of UPDATES).

I've done this in the past with triggers that would write to an "audit" table, and this provided a basic but intuitive way for me to quickly zero in on any concerning changes in the database.

For some licensing and support reasons, building triggers to capture this information won't work in my current case. I'm investigating other options (SQL Server features and commercial applications). I'm running SQL Server on AWS RDS, and it looks like SQL Server Auditing is supported. Does the output of SQL Server Audit lend itself to quick and easy consumption? Like, can I easily query for Table ABC, when/who/what was the last time a certain ROW was changed? Or alternatively is what I'm describing more in line with a commercial product (like Apex SQL)?

Since you are using SQL Server 2016, you are having advantage of using temporal tables. It seems like you need to have combination of things for addressing your objective. As far as tracking changes in terms of insert/updates and deletes are concerned, this can be very much addressed by using temporal table. This will also serve your second question i.e. what was the value before. Current value is anyway stored in the subject table.

As far as your first question is concerned, I think you need to have auditing enabled on subject table to track who did mentioned operation i.e. insert/delete/update.

Lets understand temporal table a bit here:

  Temporal tables - not to be mistaken with temporary tables - were
  introduced as a new feature in SQL Server 2016. Temporal tables, also
  named system-versioned tables, allow SQL Server to automatically keep
  history of the data in the table.

A system-versioned table allows you to query updated and deleted data, while a normal table can only return the current data. For example, if you update a column value from 5 to 10, you can only retrieve the value 10 in a normal table. A temporal table also allows you to retrieve the old value 5. This is accomplished by keeping a history table. This history table stores the old data together with a start and end data to indicate when the record was active.

These are the most common use cases for temporal tables:

Audit --> With temporal tables you can find out what values a specific entity has had over its entire lifetime.
Slowly changing dimensions --> A system-versioned table exactly behaves like a dimension with type 2 changing behavior for all of its columns.
Repair record-level corruptions --> Think of it as a sort of back-up mechanism on a single table. Accidentally deleted a record? Retrieve it from the history table and insert it back into the main table.

You can read more about temporal tables at below links with examples:

Koen Verbeeck -  https://www.mssqltips.com/sqlservertip/3680/introduction-to-sql-server-temporal-tables/
Alex Grinberg - https://www.red-gate.com/simple-talk/sql/sql-training/sql-server-temporal-tables-recipes/
Prashanth Jayaram - https://www.sqlshack.com/temporal-tables-in-sql-server/

You can enable auditing at database level to capture insert/update and delete operation. This would capture who performed the mentioned operation with some other basic details like at what time this was done.

There are few disadvantage to this auditing:

SQL Server Audit uses the resources of the audited SQL Server itself, which can degrade performance
It is difficult to comprehensively manage multiple instances and consolidate the audit data.
There is a lot of wet-work involved in managing, analyzing and archiving audit data, whether in a file or log, and necessitates manual effort for importing, archiving and reporting.
This feature isn’t available in the standard version of SQL Server until SQL Server version 2016.

In your case, if you combine both these concepts, this should serve your purpose.

Hope above helps.


MaxScale not correctly recognizing new topology when we failover to a new master So I currently have an architecture where we have one master and one slave and read/write splitting in Maxscale. When we fail the slave, Maxscale correctly recognizes it and displays the information accordingly (and does not bring our website down), as well as allow both read and writes on the master. However, when we fail the master, mysqlfailover promotes the slave to the master, but Maxscale does not recognize that the slave is now the master. It does not seem to be granting it write permissions (which causes our website to go down).

When we fail either of the two databases, MySQL recognizes that there has been a change in our topology, but Maxscale does not.

What Maxscale configuration would allow us to correctly have Maxscale recognize this new topology (Slave is now the new master)? Is there an option that we have to select in order to have Maxscale correctly give the proper read/write permissions to the new master?

Since our topology is quite simple, I feel that Maxscale should be smart enough to realize that the new slave is now the master, and adjust accordingly

Turns out the issue was that mysqlfailover was causing a conflict with maxscale.  When the master would fail, mysqlfailover would automatically promote a new slave to master.  However, when we did this it would cause a conflict with Maxscale (since it usually does it on its own), so it would lose track of which servers were the master and which were the slaves.


How to set rank per group with a SQL UPDATE statement? Please first refer to the table structure:

Now you could see there is one unique constraint including manager_code, archive_year and archive_day_of_year.

I need to rank the managers for every group which members having the same branch_code, year and day of year. Here branch_code stands for the department code.

I tried this:

I could get the correct rank number using RANK() on SQL Server but I don't know how to set the correct_rank back into the rank_in_department column using an UPDATE statement on table open_account_by_manager_per_day.

Do you have any idea on how this can be done?

CTE:

You can embed you SELECT with RANK() into a CTE and then UPDATE the CTE.

WITH cte AS
(
    SELECT *, r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
    FROM @data
)
UPDATE c
SET rank_in_department = r
FROM cte c;

Don't forget the ; terminator at the end of the line preceding the CTE statement.

Sub Query:

You can also self JOIN your table on a sub query with the expected RANK.

UPDATE d SET rank_in_department = r.r
FROM @data d
INNER JOIN (
    SELECT id
        , r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
    FROM @data
) r ON d.id = r.id

This query expects an Id or a group of column in both the sub query and the JOIN. It is used to uniquely identify each row and JOIN it to the table. From your data in your sample picture, this seems to be manager_code+, archive_year, archive_day_of_year

Sample Data used:

This gives your 2 correct syntaxes using this sample data. Queries must be adapted to your real table(s).

DECLARE @data TABLE(id int identity(0, 1), archive_year int, archive_day int, branch_code nvarchar(5), rank_in_department int, open_count int)
INSERT INTO @data(archive_day, archive_year, branch_code, open_count) VALUES
    (2016, 1, 'X', 5)
    , (2016, 1, 'X', 15)
    , (2016, 1, 'X', 52)
    , (2016, 1, 'X', 36)
    , (2016, 1, 'X', 55)
    , (2016, 1, 'Y', 65)
    , (2016, 1, 'Y', 85)
    , (2016, 1, 'Y', 42)
    , (2016, 1, 'Y', 96)
    , (2016, 1, 'Y', 15);

SELECT *
    , r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count)
FROM @data;


Test if any columns are NULL I'm trying to figure out an easy query I can do to test if a large table has a list of entries that has at least ONE blank (NULL / empty) value in ANY column.

I need something like

SELECT * FROM table AS t WHERE ANY(t.* IS NULL)

I don't want to have to do

SELECT * FROM table AS t WHERE t.c1 = NULL OR t.c2 = NULL OR t.c3 = NULL

This would be a HUGE query.

An extension to @db2's answer with less (read:zero) hand-wrangling:

DECLARE @tb nvarchar(512) = N'dbo.[table]';

DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
    + ' WHERE 1 = 0';

SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
    FROM sys.columns
    WHERE [object_id] = OBJECT_ID(@tb);

EXEC sys.sp_executesql @sql;


What's the fastest way to migrate an Oracle database from Unix to Linux? We have an Oracle database installed on AIX 7.1, and now we want to migrate our database instance to RedHat 6.2.

The RedHat server has Oracle 11.2 installed already.

What's the fastest way to migrate my database to our new Linux server?

I tried create a template (structure and data) on the AIX server, and then upload those template files to RedHat server, but dbca failed to create new database with this template, the error message says "bad file header".

Is hard to say which method is faster, but I would say from my experience using source backup is faster than import.

It seems the issue you see with the file header when you use the source files comes from the file format of AIX which is big endian, while on Linux is small endian.

The files need to be converted. This can be done using RMAN:

RMAN> convert datafile 'bla'
    TO PLATFORM="Linux 64-bit for AMD (or your platform)"
    FROM PLATFORM="AIX-Based Systems (64-bit) - or your platform"

    (note: line breaks added for readability)


After page compressing a huge table, the disk space didn't change. Do I need to shrink in any kind? MSSQL: There is a daily partitioned table which was 2 GB and we needed to compress this table by page compression. After compression, the table size got smaller if I check from the table properties - storage. But the disk space didn't change a bit after this compression. I need to gain some space on the disc and wonder if shrinking any kind (file, database) would help.

I would like to hear your advises.
Thanks

In order to reclaim disk space, you would have to use SHRINKFILE.  The usual caveats about incurred data fragmentation and the need to rebuild indexes to resolve that fragmentation apply.

Here is a sample query to determine which partitions are on which filegroups.
(Borrowed from Determining Filegroup for a Table)

SELECT OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
    ,t.NAME AS table_name
    ,i.index_id
    ,i.NAME AS index_name
    ,p.partition_number
    ,fg.NAME AS filegroup_name
    ,FORMAT(p.rows, '#,###') AS rows
FROM sys.tables t
INNER JOIN sys.indexes i
    ON t.object_id = i.object_id
INNER JOIN sys.partitions p
    ON i.object_id = p.object_id
        AND i.index_id = p.index_id
LEFT OUTER JOIN sys.partition_schemes ps
    ON i.data_space_id = ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds
    ON ps.data_space_id = dds.partition_scheme_id
        AND p.partition_number = dds.destination_id
INNER JOIN sys.filegroups fg
    ON COALESCE(dds.data_space_id, i.data_space_id) = fg.data_space_id
ORDER BY p.partition_number


Entity Relationship Diagram - Entity depending on attribute I have an ER-Diagram containing the entity employees. When employees have high skills they may supervise lower skilled employees. How can I model that in an ER-Diagram?

My initial approach of was to have a disjunction of high skilled and low-skilled employees. But that appears to be rather inconvenient.

Here's one idea:

CREATE TABLE Employees
( empno ... not null primary key
, skilllevel ... not null
);

CREATE TABLE Supervising
( supervisor_empno ... not null references Employees (empno)
, empno ... not null references Employees (empno)
, primary key (supervisor_empno, empno)
);

If you want to guarantee the skilllevel in supervising:

CREATE TABLE Employees
( empno ... not null primary key
, skilllevel ... not null
, unique (skilllevel, empno)
, check (skillevel in ('High', 'Low'))
);

CREATE TABLE Supervising
( supervisor_empno ... not null
, supervisor_skillevel ... not null
,    foreign key (supervisor_skillevel, supervisor_empno)
     references Employees (skillevel, empno)
, empno ... not null references Employees (empno)
, empno_skillevel ... not null
,    foreign key (skillevel, empno)
     references Employees (skillevel, empno)
, check (supervisor_skillevel = 'High')
, check (skillevel = 'Low')
, primary key (supervisor_empno, empno)
);


How to pattern match against Unicode characters by PostgreSQL `SIMILAR TO` expressions? In a PostgreSQL database, I have a column named metaid of type text whose value contains digits as specified in the third edition of XML 1.0 standard:
Digit ::= [#x0030-#x0039] | [#x0660-#x0669] | [#x06F0-#x06F9] | [#x0966-#x096F] | [#x09E6-#x09EF] | [#x0A66-#x0A6F] | [#x0AE6-#x0AEF] | [#x0B66-#x0B6F] | [#x0BE7-#x0BEF] | [#x0C66-#x0C6F] | [#x0CE6-#x0CEF] | [#x0D66-#x0D6F] | [#x0E50-#x0E59] | [#x0ED0-#x0ED9] | [#x0F20-#x0F29]

In the documentation of PostgreSQL for SIMILAR TO expressions, matching against such Unicode characters by their range is not mentioned.
I need to put a CHECK constraint on the column for ensuring the pattern of the entered value conforms to the required format.
How can I accomplish this?

Here is a check constraint:
CHECK (col ~ '^[0-9\u0660-\u0669\u06F0-\u06F9\u0966-\u096F]+$')

The pattern starts with "beginning of string" (^) and ends with "end of string" ($)and allows one or more "digits" in between.
^ and $ are necessary, otherwise the pattern would match abc123efg.


What will happen if master goes down in peer to peer replication? In case of Peer to peer replication, if Master goes down, will the other two nodes continue synchronizing or not?

In a Peer-To-Peer replication topology all the nodes are master. So, there is no concept of just ONE master.

The image in your question seems to be made up and wrong. See original doc.

Read up more on - Peer-to-Peer - Transactional Replication


Dedicated tables VS Multipurpose Tables I am developing a forum like app using a MySQL database. I have posts, comments and replies just any other forum.

At the moment I have a table for posts, a table for comments/replies and a table for user/object relationships.

Lately, I have been studying WordPress CMS database structure and I've noticed that all the above are under a single table and there is a field 'post_type' that determines the object's type (eg post, comment or reply).

I have come to discover that this is very flexible for this site's purposes but is it wise in terms of speed?

Additionally, I was thinking of using an additional database such as ElasticSearch just for quering and MySQL only for storing.

What are your thoughts on this approach? Advantages/Disadvantages?

Thank you in advance :)

When the data pattern is the same and the data is possibly related, such a setup is completely fine and even desirable as it makes certain tasks (such as supporting a search function) a lot easier. With proper indexing and solid queries speed should not be a concern.

This type of table should only be avoided if you find yourself trying to force data to fit in the pattern for the sake of less tables (such as deciding to save your user data as a string representation to fit in that table with a new type) or using types to indicate different key references for other tables sharing a single column, which would inhibit using Foreign Keys and break your relational model.


Postgres index size calculation Is there any way to determine a rough estimate of the size (on disk) of an index before creating it? The size of the table and each column are known. I am particularly interested in GIN indexes. Also any information of how the sizes of different index types relate to each other is appreciated. Is there a general rule of thumb like a GIN index is always bigger than a B-TREE index? Or is it too dependent on the data size and distribution?

To clarify: i am not looking for a tool. I am happy to do it by hand.

There is no general way to answer this (other than try it on your test server and see).  GIN supports many different operators, like tsvector or trigrams, and they have different characteristics.  In newer versions, GIN indexes use compression which can be pretty impressive when the same key value shows up over and over again.  But that level of compression depends on the ordering of the rows.

For example, if I index a single text column with a lot of duplicate values (~50 million rows, with ~1.5 million distinct values, using the btree_gin extension for the GIN), I get 2010 MB for the b-tree index and 435 MB for the GIN index.  So no, the GIN is not always bigger.  But in general (i.e. other than with btree_gin) you don't index the same types of data with GIN as you do with b-tree, so a direct comparison of sizes does not make much sense.


Optimal data types for UUID and ENUM AWS Aurora for MySQL table DDL Hi i have a table in MySQL 5.7 Aurora and current data types for the table is
CREATE TABLE `table` (
`ID` varchar(255) NOT NULL,
`USER_ID` varchar(255) NOT NULL,
`PATTERN_TYPE` varchar(255) NOT NULL,
`USES_TYPE` varchar(255) NOT NULL,
`OBJECT_TYPE` varchar(255) NOT NULL

PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

NOTE :I need to  have Index on USR_ID column
To use Aurora parallel query for this table i need to change varchar(255) to varchar(192)
I can do that for ID columns but before doing just wanted to know if i can optimize further ?
I have ID which is UUID generated from application and it is of two kind
6tgbcrq9pfhtyizsdo82mcrzz
0a3687fr-612f-2345-984c-65c100076267

so for above two kind of UUID we can use varchar(192) or even we shrink more ?
Query 2 : We have fixed types for PATTERN_TYPE (88 distinct types) ,USES_TYPE(5 distinct types) and OBJECT_TYPE (10 distinct) .
what would be the best data types for this two columns ? Can we use ENUM for this ?
Why need to convert to varchar(192 ) because of below limitation from Audora MySQL

TEXT, BLOB, JSON, and GEOMETRY data types aren't supported with
parallel query. A query that refers to any columns of these types
can't use parallel query.
Variable-length columns (VARCHAR and CHAR data types) are compatible
with parallel query up to a maximum declared length of 768 bytes. A
query that refers to any columns of the types declared with a longer
maximum length can't use parallel query. For columns that use
multibyte character sets, the byte limit takes into account the
maximum number of bytes in the character set. For example, for the
character set utf8mb4 (which has a maximum character length of 4
bytes), a VARCHAR(192) column is compatible with parallel query but a
VARCHAR(193) column isn't.

The string 0a3687fr-612f-2345-984c-65c100076267 is only 36 characters long. Why would you need varchar(255) or varchar(192) for that?
If you need it to be even more compact, you can use VARBINARY(16). Then for your UUID, remove the - characters and convert the string to bytes using UNHEX().
For your other columns pattern_type, uses_type, and object_type, I don't recommend using ENUM for columns that are likely to require new values from time to time. I'd use ENUM only when the column is certain to be unchanging.
It would be better to define a lookup table for those types, with a shorter ID. For example, either an integer or a short code. Then you can make reference to it in your table shown above, using a smaller data type.


Does running pg_dump on live db produce consistent backups? I have a 3GB database that is constantly modified and I need to make backups without stopping the server (Postgres 8.3).

My pg_dump runs for 5 minutes. What if the data is modified during the process? Do I get consistent backups? I don't want to find out when disaster strikes.

Postgres documentation http://www.postgresql.org/docs/8.3/static/app-pgdump.html doesn't say anything about this.

From the manual:

  It makes consistent backups even if the database is being used
  concurrently.

So yes, you can trust the backup. Of course, it's PostgreSQL, you can trust your data in PostgreSQL.


Catch event of auto_shrink in SQL Server Log? I am aware of the disadvantages of AUTO_SHRINK being configured on SQL Server databases. Recently, in a new environment, I discovered several production servers with the Auto Shrink option turned on.

Now, before I change this configuration, I wonder: Since the auto_shrink event starts at 25% unused space I wonder if and/or when this is actually happening on the SQL Servers. I  tried to find the relevant SQL-Event-Logfile entries. I started manual shrinking on test databases but there is no Event-Log entry showing up.

Does anyone know how to find out when shrinking has happened within the latest days or weeks on a SQL Server?

Pls see this link :

To summarize (and this worked for me) :

Find out the location of your default trace file using :

SELECT * FROM fn_trace_getinfo(default);

Then use that to query your default trace file :

SELECT top 10
    TextData,
    HostName,
    ApplicationName,
    LoginName,
    StartTime
 FROM
 [fn_trace_gettable]('C:\MSSQL\Log\log_1110.trc', DEFAULT)
 WHERE TextData LIKE '%SHRINK%';

You can see entries there for operations like DBCC SHRINKDATABASE etc.


MySQL : Extract SQL statements from binlog I am trying the following command to extract SQL statements from binlog for using it in another database (Amazon RDS)

mysqlbinlog /var/lib/mysql/binlog.000027 --start-position=54375264 --base64-output=NEVER > /home/ec2-user/output2.sql

But I got the following error:

ERROR: --base64-output=never specified, but binlog contains a Table_map event which must be printed in base64.

I read in the documentation that mysqlbinlog exits with an error if a row event is found that must be displayed using BINLOG.
Is there any work around to extract SQL statements from binlog?

Update 1 :
I run the command again with option --verbose but I did not get any return in the command line

[root@server ec2-user]# mysqlbinlog /var/lib/mysql/binlog.000027 --start-position=54375264 --base64-output=DECODE-ROWS --verbose  > /home/ec2-user/output4.sql
[root@server ec2-user]#
[root@server ec2-user]#

When I browse output4.sql, I can see some valide SQL + some commented SQL like this:

#130906 14:54:55 server id 1  end_log_pos 269447893     Query   thread_id=1221718733    exec_time=0 error_code=0
SET TIMESTAMP=1378479295/*!*/;
BEGIN
/*!*/;
# at 269447893
# at 269447950
#130906 14:54:55 server id 1  end_log_pos 269447950     Table_map: `dbname`.`tablename` mapped to number 102
#130906 14:54:55 server id 1  end_log_pos 269448102     Update_rows: table id 102 flags: STMT_END_F
### UPDATE `dbname`.`tablename`
### WHERE
###   @1='edited by me'
###   @2=0
###   @3=1373178370
### SET
###   @1='edited by me'
###   @2=0
###   @3=1378479295
# at 269448102
#130906 14:54:55 server id 1  end_log_pos 269448162     Query   thread_id=1221718733    exec_time=0 error_code=0

Update 2 :
I am following the tutorial here https://engineering.gosquared.com/migrating-mysql-to-amazon-rds

I want to update Amazon RDS from the output of binlog. Currently  Amazon RDS do not allow writing from binlog. I am trying to extract the SQL statments from binlog so I can update Amazon RDS. Is there any way to update Amazon RDS from binlog?

--base64-output=DECODE-ROWS --verbose

This is the format you need to be using if you are reviewing the binlog manually... it extracts the maximum amount of useful data from the log... but it will help if you understand how binary logging works in MySQL, first.
RDS (at least for MySQL 5.5 and 5.6) uses binlog_format = MIXED.  This means the server may choose, on a query-by-query basis, to log either the actual SQL statement that was executed ("statement" log), or the actual rows changed by the query ("row" log) but not both.
The "commented SQL" that you are seeing is, as explained in the documentation, a human-readable pseudo-sql reconstruction of the actual data that was changed on the master by your query, for events logged using the ROW format.  The body of the query that made the change is not preserved, because it is not needed for replication -- slave servers can replicate the exact same changes to their tables as occurred on the master, based on these before ("where") and after ("set") row images.
You can't "get" the original SQL from these events because it's not being saved to the binary log when events are written like this.  In the example you posted, it's telling you that the value of the 3rd column changed.
###   @3=1373178370 # old value

###   @3=1378479295 # new value

This "pseudo-SQL" is commented out because it isn't actual valid SQL that a server could execute.  It's eyeball-compatible-only.  (For a server to replay these row events, it needs to see the BINLOG blocks, which are a base64-encoded version of the information that mysqlbinlog is decoding for you, here).
The column names are also not preserved in the binary log for row-based events, because since they should be the same on master and slave, that information is not needed.
(Edit: you mentioned RDS but re-reading your question, it seems that you may be trying to apply these logs to RDS rather than having retrieved them from RDS; retrieving logs from RDS is only possible with RDS/MySQL 5.6.  The conclusion, then, is that the server that originated these logs is either logging in MIXED or ROW mode.)

Update: I see what you are doing now.  To summarize the link you posted, you're trying to (1) take a point-in-time snapshot of an existing server with mysqldump, (2) restore that snapshot to an RDS instance, then (3) use mysqlbinlog to play the transactions forward from the binlog to sync the RDS server to your existing production server, and finally (4) migrate your application to RDS.
Bottom line: You are overthinking the nature of the problem and are concerned without cause.  MySQL Server knows how to interpret the output of mysqlbinlog without either the --base64-output or --verbose flags and apply the changes it sees, whether or not the original query SQL is logged because when the query isn't logged, the actual row changes are logged.
When you run mysqlbinlog you see what looks almost like noise.  Example:
#080828 15:03:08 server id 1  end_log_pos 442   Delete_rows: table id 17 flags: STMT_END_F
...
BINLOG '
fAS3SBMBAAAALAAAANoAAAAAABEAAAAAAAAABHRlc3QAAXQAAwMPCgIUAAQ=
fAS3SBcBAAAAKAAAAAIBAAAQABEAAAAAAAEAA//8AQAAAAVhcHBsZQ==
'/*!*/;

This is a base-64-encoded version of very tightly-packed binary "row images" containing the actual change that needs to be made to the database.  A MySQL server knows how to decode and apply this change.   Conversely, when you decode-rows and verbose, you're asking mysqlbinlog to decode the binary log for human eyeballs.
Leave your server's binlog_format setting as it is.  The binary logs should work without a problem.

But now, forget everything you were planning because literally in the last 24 hours, Amazon has announced a much better way: they have opened up the ability to temporarily connect an RDS instance, as a slave, to a non-RDS master, to allow the RDS instance to be brought into real-time automatically prior to being promoted to master.
This is exactly the solution to what you are trying to accomplish and is much easier and less error prone.

To import data from a MySQL database that is not in Amazon RDS, you can configure replication from that database to an instance of MySQL running in Amazon RDS. The MySQL database you are migrating from can be running either on-premise in your data center, or on an Amazon EC2 instance. The MySQL instance in Amazon RDS must be running either version 5.5.33 or 5.6.13. Using replication to migrate the data reduces downtime.
— http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.NonRDSRepl.html

No kidding.
As a DBA, I've been very reluctant to use RDS in my own production networks, preferring to retain full control over all aspects of the server... and by "reluctant," I mean "we didn't."  I finally relented when Amazon made it possible in RDS for MySQL 5.6 to use the RDS instance as master to a non-RDS slave ... and, now, using an RDS master as slave to a legacy server to simplify migration seems like another positive sign for the viability of RDS.


Nvarchar column getting truncated to 256 characters I have a SQL Server Express 2012 database. There is a table which previously had a column of type nvarchar(255). Now my requirements have changed and I am required to store long strings in the same column, so I modified the columns to have size 512.

alter Table [dbo].[mytable] ALTER COLUMN [mycol] nvarchar(512) NULL;

Now when I try updating data in the column whose size was increased, the update goes through fine. But when I query the data, the column in question only returns 256 chars of data.

I have tried updates from application (jdbc) and manual update SQL queries in SSMS. But in both cases the results are the same. The data some how seems to be truncated at 256 chars.

update [dbo].[mytable]
set mycol1 = 'Lot+hrJBVaD2hX1PjF34M2K8nLxyC3csgjZEnXOgzhyQ4iVNU8DYsPIePaMuHB4nC4cJghRSvbwQGw4me3mpynGxWeTIiCaxMFMJofF7u+yhEpa5S4hmeKaADCMHv/Mr+J/Ao3wbtJPO5VQcp6q7nUkQbVUTTAZjJ0GKLq3trpTxqNT52WrXFP+ZL1rN6Ho8rMMO74BvGxzojHo7ly9aO9iqfoi+LxJKvHK54QKpQLc/2AT5bjdUnupafURzmJvCII4DvL8R7ROSu3PbXDEEN/BmBZSY7Gune64ADh0q6R4RmLMBJFxviFMxxcjIgkKngma/uSVzYTdNLQJqD9nW2w==',
mycol2 ='a0rfSnMgCSun8J6+LmElAY4Kx1ZmCEuCkCHeVW7wi6Jo7apAZ/qiGj0kRD+ZMlK5r4icgoBufv7Lzs1zlWZkoMMdWuula5keNo8X4TH3Y8cyEL3Ujio/Co6UVL7xngdXYY6woFxPQQtW0FB+/miGlY6KrByRV9vMFsPTGc77+dc0wgFQxE3CsPU97nRVe8m/LsT97qoolSvtyB0xHtDeQ70q2VRNB2oAyeVpsqoVN7/MQb7xW9glqYk0w/uHhZ6kx5QkSEbn1Ssq77KN/96yQluzcDm9E9eiMBdDMXFsu3FjqPy5BSyyvLi13NKaJcpaGo+do8jz0Rl6iXuorfSNyw=='
where ... conditions;

select max(len(mycol1)), max(len(mycol2)) from [dbo].[mytable];

...returns 255. No errors were shown when running the queries.

I checked that elsewhere in the same database I have longer columns (nvarchar(998)) and there the updates go through fine - I can store longer values and get that data successfully.

I also tried rebooting the database just in case the alter did not go through properly but still data is truncated at the same size. I have already confirmed that the table shows the correct column size when I check the properties.

I have this database sitting on AWS RDS. Incidentally same column level alter works fine on the database on my local machine.

I do not have any views in the database and I have tried querying the exact records I update. This alter script works fine on my own local instance of SQL Server; the problems show up only on the SQL Server instance on my test env (AWS RDS).

I can't share the table definition due to its proprietary nature. I am not using a stored procedure. I tested by running a plain SQL update query using SSMS.
The application has parameter set as String, and does not do any datatype truncation. It leaves that to the database.

Any ideas on what could be wrong appreciated.

My guess (which turned out to be correct) was that a trigger was modifying / truncating the value somehow.

I usually first check to make sure I am really observing the data correctly, since SSMS truncates display output by default (and it's different in results to text/grid). So checking the value, LEN, MAX(LEN()), and ensuring you are looking at the right column, the right table, and that your definition has been updated correctly, are useful things to check.

Once you've ruled that out, if you were updating from application, I would check to make sure the app doesn't have the wrong data type / size for its local variables or command parameters.

If you were calling a stored procedure, I would check the parameter, since silent truncation can occur here:

CREATE PROCEDURE dbo.foo @x nvarchar(255)
AS PRINT @x;
GO

DECLARE @string nvarchar(512) = REPLICATE(N'x', 254)
  + REPLICATE(N'y', 258);

PRINT @string;
EXEC dbo.foo @x = @string;

Results:

xxx...xxxyyy...
xxx...xxxy

The second value is obviously truncated to the parameter definition (but no warning or error is ever logged).

But if you are getting truncation from a direct update in SSMS, and your parameters / variables / constants look right, dig for a trigger. It's likely the 255 definition is still hard-coded there somewhere.


How to set isolation to SERIALIZABLE DEFERRABLE for a whole Postgresql database? I think read somewhere that it was possible to configure a Postgresql database to force all transactions to be SERIALIZABLE, but I can't find that document anymore.

In fact, I would like to force the isolation level of my database to SERIALIZABLE DEFERRABLE. Is this possible at all? If yes, how?

You can set SERIALIZABLE as the default isolation level in postgresql.conf:

default_transaction_isolation = 'serializable'

This may confuse clients that assume READ COMMITTED isolation, so IMO it's better to set it explicitly in your sessions.

As for the rest, per the manual:

  The DEFERRABLE transaction property has no effect unless the transaction is also SERIALIZABLE and READ ONLY

... and given the properties of such transactions, I find it hard to imagine you wanting to make it the default. Are you perhaps confusing it with SET CONSTRAINTS DEFERRED?

You can make transactions read-only and deferrable by default with:

transaction_read_only = on
transaction_deferrable = on

in postgresql.conf but it's really a rather bad idea. Instead, just use an appropriate BEGIN, or SET TRANSACTION in your session before your first "real" SQL statement.


get max date by comparing total amount with amount in another table I have two tables tableA(S.no.,amount) and tableB(Id,paid_amt,trxn_dt)

   tableA                tableB
S.no.  amount     Id    paid_amt    trxn_dt
1       500       1       200      2015-01-01
2       500       2       300      2015-02-10
3       500       3       500      2015-04-10
4       500       4       1000     2015-10-10

Now i need a query to get the output as follows:

S.no.    amount          trxn_dt
 1        500           2015-02-10
 2        500           2015-04-10
 3        500           2015-10-10
 4        500           2015-10-10

I need to compare the sum of paid_amt from tableB to match each amount of tableA and get the max trxn_dt according to it. i.e (200+300)=500  so get max(trxn_dt) i.e 2015-02-10. Similarly for s.no.2 get get max(trxn_dt) as 2015-04-10 and for last one 1000 has been paid on 2015-10-10 so get max(trxn_dt) as 2015-10-10 for both s.no. 3 and 4. i.e i have to get the trxn_dt of the exact paid amount according to s.no. I am able to do this using resultset in java but it is time consuming for table containing lots of data.I would appreciate any kind of help. Thank you.

The requirements seem complex but if you calculate the running totals first, it's a simple comparison.

Find the first running_total from b that has covered the running_total from a:

WITH
  rta AS
    ( SELECT [S.no], amount,
             running_total = SUM(amount) OVER
                                 (ORDER BY [S.no]
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
      FROM tableA
    ),
  rtb AS
    ( SELECT trxn_dt, id,
             running_total = SUM(paid_amt) OVER
                                 (ORDER BY trxn_dt, id
                                  ROWS BETWEEN UNBOUNDED PRECEDING
                                           AND CURRENT ROW)
      FROM tableB
    )
SELECT
    a.[S.no], a.amount, b.trxn_dt
FROM rta AS a
  OUTER APPLY
    ( SELECT TOP (1) b.trxn_dt
      FROM rtb AS b
      WHERE a.running_total <= b.running_total
      ORDER BY b.running_total, b.trxn_dt, b.id
    ) AS b ;

Tested at rextester.com.


Database Size Troubleshooting I have a SQL Server database and the .MDF file is almost 9GB in size. However, the data and indexes only seem to be taking up a little over 1GB. I've shrunk the database (with and without reorganization) and I've shrunk the database files.

What's next? How do I find what is taking up so much space?

This is a bit long for a comment so I'm putting it here.

First try running this:

exec sp_msforeachtable 'sp_spaceused [?]'

Yes I realize it uses sp_msforeachtable and there are a number of better methods, this is just quick and hopefully "good enough".

At a guess you have a heap (no clustered index) that has a large amount of "unused" space.  I wrote about how to duplicate it here.  If in the list of tables you find one that has a large amount of space shown in the unused column then try running this:

ALTER TABLE tablename REBUILD

Fair warning it may take up some space to run the operation but it should free up the excess space in the table.


Is conditionally SQL Server log backup possible? I have a SQL Server database and right now we have weekly full backup, daily differential backup and 5 minutes log backup. Most of the time there are reads and writes happening in the database. Sometimes, for example there was continuously eight hours period yesterday, there was only read operations happened in the database and no writes. In this period I don't want to do the 5 minutes log backup as there isn't any modifications happened. Is there a way for me to test whether some modifications occurred in the database so that I can actually take the log backup, otherwise the log backup session will just skip it. Is this idea reasonable and doable? I simply don't want to have too many log backup files which makes it a little bit harder to manage.

Edit:

I considered the function fn_dblog. My idea is to count(*) the result of this function and if it increased then I'll take a log backup. But I'm not sure whether read will cause log record increase.

My idea is to count(*) the result of this function and if it increased then I'll take a log backup. But I'm not sure whether read will cause log record increase.

A read action, including reading from the system management views/functions should not cause any log activity, so this would work as you intend.

But why do you need to skip the log backups in this case? They will be minimally small as there has been no new activity since the last log backup so won't consume significant resources to take, store, transfer, and (if needed later) restore, and you would be adding an extra complication (so a potential point of failure) into your process.


Select duplicates by PrimaryKey I have to select all duplicates by PrimaryKey

Here is my DB

ID   UserID   GroupID
1    1        1
2    1        1
3    1        2
4    2        2

If I pass ID=1 need to select row 1 and 2

Here is my searched id:

DECLARE @id INT = 1;

Here is my SQL Script 1:

SELECT *
FROM table as t1
WHERE EXISTS  (
      SELECT 1
      FROM table as t2
      WHERE
           t2.ID=@id AND
           t2.UserID = t1.UserID AND
           t2.GroupID = t1.GroupID
)

Here is my SQL Script 2:

SELECT t1.*
FROM table as t1 INNER JOIN
(
    SELECT *
    FROM table as t3
    WHERE t3.ID = @id
) as t2 ON t2.UserID = t1.UserID AND t2.GroupID = t2.GroupID

With this Selects the result is ok, but I'm not sure is the correct way and who is the best for the performance

Please give me some advice. Thanks.

Both your queries are fine, assuming that (ID) is the primary key of the table (or it has a UNIQUE constraint).

The 2nd query can be simplified, removing the derived table:

SELECT t1.*
FROM table as t1 INNER JOIN
     table as t2
     ON t2.UserID = t1.UserID AND t2.GroupID = t2.GroupID
WHERE t2.ID = @id ;

For performance, you may need an index on (UserID, GroupID) - with any of the queries.


Why does using Format vs Right to apply padding cause estimated number of rows to dramatically change? I was working on a query at work, that had a left join like

cast(cola as varchar) + '-' + right('000' + cast(colb as varchar), 3) = x

The actual execution plan for this query was fairly close, est 269 vs actually 475.

Changing the right +padding to use format(colb, '000') results in a a huge mis-estimate of the number of rows, off by at least 4 million, which causes the query to take 10-15 times as long.

I understand why a mis-estimate would cause a problem, but I don't understand why using Format would cause a less accurate estimate.

FORMAT returns nvarchar which has a higher data type precedence than the compared varchar column. In addition the imprecise row count estimate, the implicit conversion of the compared varchar column to nvarchar will prevent indexes on that column from be used efficiently

Try casting the FORMAT result to varchar.


This is a common trick of counting conditionally into several columns, depending on a condition using the IF() expression:

mysql> SELECT DATE_FORMAT(date,'%m-%d %H:00') AS Date,
              SUM(IF(type = 'client', 1, 0)) AS `Client Count`,
              SUM(IF(type = 'staff', 1, 0)) AS `Staff Count`,
FROM ticket_data
WHERE department = 'support'
GROUP BY DAY(`Date`), HOUR(`Date`)
ORDER BY `Date` ASC
LIMIT 10;


Can I restore a differential backup to the 2nd newest full backup? At 12:30 pm we pulled a full backup and shipped it, and set all users in read-only mode. At 11pm that night another full backup was triggered.

Assuming no changes occurred during that time span, at least none that I care about, can I apply a differential I pull now to the [restored] 12:30pm backup I created...or will it not work/error out because another full backup occurred since it?

No, that's not possible. You will not be able to start the backup operation.

What you are trying to do there could leave the database physically inconsistent if SQL Server allowed it. You would get a Frankenstein database with pages mixed from different points in time.


Query involving TVF very slow on SQL Server 2008 I have a query that runs in under 1 second on SQL Server 2014. A similar query takes over 12 minutes when run against a table on a linked server running SQL Server 2008. I don't know if it's because it's a linked server or because it's 2008. I found a blog post about TVFs confusing the query optimizer on 2008, but I'm not sure how to apply that info to my situation.

Here's the query on the local table:

select 'E10', InvoiceNum, MiscCode, Description, DocMiscAmt, Type
from Live.Erp.InvcMisc
where InvoiceNum in (
    select distinct ARInvoiceNum from dbo.UnifiedSalesJournal(2018, 5, default, default, default)
    where DB = 'E10'
)
and MiscCode not like '%SHI'
and MiscCode not like '%HAN'

plan

Here's the query on the linked table. The difference in the plan is obvious:

select 'E9', InvoiceNum, MiscCode, Description, DocMiscAmt, Type
from tmiedb.Epicor905.Dbo.InvcMisc
where InvoiceNum in (
    select distinct ARInvoiceNum from dbo.UnifiedSalesJournal(2018, 3, default, default, default)
    where DB = 'E9'
)
and MiscCode not like 'SH%'
and MiscCode not like 'HD%'
and MiscCode <> 'THDL'

plan

I got rid of the loop by rewriting it like this. This runs in about 1 second:

select 'E9', InvoiceNum, MiscCode, m.Description, DocMiscAmt, Type
from dbo.UnifiedSalesJournal(2018, 3, default, default, default) i
left join tmiedb.Epicor905.Dbo.InvcMisc m
on i.ARInvoiceNum = m.InvoiceNum

plan

But if I add a where clause, this takes 29 minutes.

The data is small. The TVF returns a few thousand rows. The local table contains 10K rows, and the linked table contains 55K rows.

The linked server contains historical data that will never change, so I'm considering importing the whole database into SQL Server 2014. But I'm hoping there's a less drastic solution.

Possibly related: Linked Server behavior. I'm seeing the behavior described in Case 3a. It's calling sp_execute thousands of times, once for each row returned by the TVF.

Before SQL Server 2012 SP1, the permissions needed by the linked server login to read remote statistics were:

  In order to view the statistics object, the user must own the table or the user must be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

From SQL Server 2012 SP1 onwards, the login only needs SELECT permission:

  Users must have permissions on all columns in the statistics object
  Users must have permission on all columns in a filter condition (if one exists)
  The table cannot have a row-level security policy.

More information in Distributed Queries – Remote Login Permissions and Execution Plans

Without access to statistical information, the optimizer makes a guess at the number of rows returned by the TVF (one row) and chooses nested loops on that basis.

If you cannot make the linked server login a member of one of the groups mentioned, the best workaround for the specific query in the question might be an OPTION (HASH JOIN) hint, or to read all the data from the remote server into a #temporary table.


App domain is marked for unload due to memory pressure In my sql server log (2005 SP4) I have this message every couple of seconds :

"AppDomain XXX [DataBase.dbo[runtime], YYY] is marked for unload due to memory pressure"

Our server :

Windows Server 2003 R2 Enterprise Edition SP2
32 Gb RAM
Physical Address Extension
Intel Xeon CPU X5675 x 2 (6 core) 3.07Ghz
Awe enabled
We have CLR code in SQL Server
-g2048 on startup (I'm no DBA, and this value was set by a former emplyee)

I read many article about memtoleave or vas, but I don't get how to diagnose my problem.

Any idea ?

Microsoft has a KB article about this behavior. You got a 32-bit OS with 32-bit application, so the process' address space is 4 GiB. AWE can be used to access more memory, but it is limited only for buffer pool usage. As per the documentation:

  The SQL Server buffer pool can fully utilize AWE mapped memory;
  however, only database pages can be dynamically mapped to and unmapped
  from SQL Server's virtual address space and take full advantage of
  memory allocated through AWE. AWE does not directly help supporting
  additional users, threads, databases, queries, and other objects that
  permanently reside in the virtual address space.

Short-term solution: review that your CLR code minimizes memory usage.

For a long-term solution, upgrade to 64-bit.

Addendum

Kimberly Tripp has written a white paper about CLR stuff from DBA point of view. She's got a few nice queries and Perfmon counters that are of use for tracking CLR resource use.


Add a ROW_NUMBER(), and select all records where row_number = 1.

with dupli as
(
    select id, col1, col2, row_number() over (partition by id order by col1) rn
    from @dup
)
select     d1.*
from       @dup d1
inner join dupli d2
on         d1.id = d2.id
and        d1.col1 = d2.col1
and        d2.rn = 1;

|id    |col1|col2         |
|:-----|---:|:------------|
|111111|   1|Green        |
|222222|   9|Not Specified|
|333333|   1|Blue         |
|444444|   2|Undisclosed  |

dbfiddle here


Insert dependent records in multiple tables, MSSERVER 2012 I'm trying to migrate Data from a Excel file to a newly build Database.

I already inserted all data from the Excel file to the Database and now need to insert it in three newly dependable tables.

The old database contains all data in a single table, in the new Database I would like to split the Data in three different tables (Address, ContactData and Company).

I tried doing it like this:

INSERT INTO CRM_Beta.dbo.Adress(Street, City, ZIP, State, Country)
    (SELECT Adress, City, ZIP, State, Country
     FROM ACT_Data.dbo.ActData)

INSERT INTO CRM_Beta.dbo.Contactdata(Phonenumber, Mobilenumber, Email, Adress)
    (SELECT PhoneNumber, MobileNumber, [Privat-Email], IDENT_CURRENT('Adress')
     FROM ACT_Data.dbo.ActData)

INSERT INTO CRM_Beta.dbo.Company(Name, Website, Rating, Contactdata, Protocol, Documents)
    (SELECT Company, [Web-Site], '', IDENT_CURRENT('Contactdata'), @@ROWCOUNT, @@ROWCOUNT
     FROM ACT_Data.dbo.ActData WHERE Company != NULL)

But it will only inserts the last inserted ID from the Query before.

Lets say the first table contains two entries in the format:

| Address | City | ZIP | State | Country | PhoneNumber | MobileNumber | private_mail | Company

Now i need to split this into three tables:

All Address Data (Street, State ...)
All Contact Data (Phone, Email ...)
All Company Data (Name, ...)

while linking each ID to the corresponding Foreign Key ID.

The three new tables have the format:

Company PK: ID, Name, Website, Rating,  FK ContactData, FK Protocol, FK Documents
ContactData PK: ID, Phonenumber, Mobilenumber,Email, FK Address
Address PK: ID, Street, City, ZIP, State, Country

I can change the Database if necassary or redesign if you think im doing something stupid ;)

Thanks for the help.

Edit1: I have tried using OUTPUT but i didn't figure out how to work with it yet and therefore have problems applying it to my case.

The Source file is a simple txt file i imported with the SQL-Server Assistant, so i could easily add keys or indexes, or use custom imports.

Edit2: I managed to do what i tried to accomplish in a similiar way that
 @Jonathan Fite showed, it definitely lead me into the right direction.

I did not understand how this would have worked with the Identity_Insert mentioned by @David Spillett and therefore will give the right answer to @Jonathan Fite

But it will only inserts the last inserted ID from the Query before.

This is current as documented in https://docs.microsoft.com/en-us/sql/t-sql/functions/ident-current-transact-sql:

  Returns the last identity value generated for a specified table or view.
  The last identity value generated can be for any session and any scope.

You can get all the identities generated in an insert using the OUTPUT clause as documented at https://docs.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

  The Source file is a simple txt file i imported with the SQL-Server Assistant, so i could easily add keys or indexes, or use custom imports.

Assuming that you don't need to deduplicate anything at this point (i.e. you won't have two companies with the same address), and you do not need to keep ID values unique between the tables, you could:

Add an ID column to the source table of the right type (INT, UNIQUEIDENTIFIER, ...) which is populated by default (i.e. IDENTITY(1,1) or DEFAULT NEWID())
Use that for the IDs of all the inserts coming from it (if you are using IDENTITY columns you will need to temporarily set IDENTITY_INSERT off for them as this is done, see https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql

No need for OUTPUT or extra lookups.

  I can change the Database if necassary or redesign if you think im doing something stupid ;)

You'll need to more thoroughly describe what you are trying to model before we can help at all with that. It does seem strange to me that a company record would have a reference to contact details rather than the other way around: if is more likely that a company would have several contact methods than that one contact method could be relevant to more then one company.


How to import data from a dump file to a specific schema in postgres? Let us assume there's a database name myDataBase. There are four schema in this database named

1: schema_1

2: schema_2

3: schema_3

4: schema_4

I have created a dump of schema_1 from this command pg_dump  myDataBase --schema=schema_1 > schema_1_dump

How to import schema_1_dump to only schema_3 but not to any other schema

probably the easiest is to first rename the existing schema_1

restore the dump

rename the new schema_1 to schema_3

restore the original schema_1 to its original name

eg psql:

 alter schema schema_1 rename to schema_1_backup;
 create schema schema_1;
 \i schema_1_dump
 drop schema_3 cascade;
 alter schema schema_1 rename to schema_3;
 alter schema schema_1_backup rename to schema_1;

This is going to annoy other users.

The other way is to change all the SQL commands to say  'schema3' instead of 'schema1'


Postgresql multi-column GIN index Suppose a postgresql table items like this. (keywords column is of type text[])

name   account_id      keywords
------------------------------
foo1   1               ['k1', 'k2']
foo2   1               ['k1', 'k3']
foo3   2               ['k4', 'k1']
foo4   2               ['k1', 'k6']

Each row in items is relate to a Account (the table is virtually splitted by account_id). We want make queries like: "items for account 1 with keyword k1". This query requires a composite GIN index on account_id and keywords column.

Actually, we need an inverted index, with rows like this: (the key for each row should be composite)

(account_id, keyword) --> [item1, item2, ...]

What's the right way to create this index in postgresql?

The answer is same as the answer for this question: Inner join using an array column

The main point is installing the btree_gin extension (this may require additional package, like postgresql-contrib)

Then, the extension can be enabled easily with: (requires postgresql >= 9.1)

CREATE EXTENSION btree_gin;

And index can be created with:

CREATE INDEX index_name ON items USING GIN (account_id, keywords);


Microsoft SQL Server says my login is disabled, even in single user/DAC mode I'm locked out of a local database, and the single-user mode option that has worked for me in the past isn't working for me here. I could use some options to try.

It's saying the account is disabled, which I didn't think was even possible for single-user mode.

What I've tried already:

Restarting in single-user mode:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>net stop MSSQL$DBNAME
The SQL Server (DBNAME) service is stopping..
The SQL Server (DBNAME) service was stopped successfully.

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>.\sqlservr -sDBNAME -m -c

[seems to come up]

The login attempt looks like this:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>sqlcmd -S .\DBNAME
Msg 18470, Level 14, State 1, Server MY-PC\DBNAME, Line 1
Login failed for user 'My-PC\sarahg'. Reason: The account is disabled.

Server output:

2012-05-29 15:55:25.11 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 15:55:25.11 Logon       Login failed for user 'My-PC\sarahg'. Reason: The account is disabled. [CLIENT: <local machine>]

Restarting in DAC mode:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>.\sqlservr -sDBNAME -m -c -t 7806
... blah blah ...
2012-05-29 15:59:05.59 spid6s      Server name is 'MY-PC\DBNAME'. This is an informational message only. No user action is required.
2012-05-29 15:59:05.60 spid9s      Starting up database 'model'.
2012-05-29 15:59:05.60 spid6s      Informational: No full-text supported languages found.
2012-05-29 15:59:05.60 spid6s      Starting up database 'msdb'.
2012-05-29 15:59:05.72 Server      A self-generated certificate was successfully loaded for encryption.
2012-05-29 15:59:05.72 Server      Server is listening on [ 'any' <ipv6> 49201].
2012-05-29 15:59:05.74 Server      Server is listening on [ 'any' <ipv4> 49201].
2012-05-29 15:59:05.74 Server      Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\DBNAME ].
2012-05-29 15:59:05.74 Server      Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$DBNAME\sql\query ].

The login attempt looks like this:

C:\Program Files\Microsoft SQL Server\MSSQL10_50.DBNAME\MSSQL\Binn>sqlcmd -A -S .\DBNAME
Msg 18470, Level 14, State 1, Server MY-PC\DBNAME, Line 1
Login failed for user 'Mys-PC\sarahg'. Reason: The account is disabled.

With server log:

2012-05-29 16:00:24.86 Logon       Error: 18470, Severity: 14, State: 1.
2012-05-29 16:00:24.86 Logon       Login failed for user 'Mys-PC\sarahg'. Reason: The account is disabled. [CLIENT: 127.0.0.1]

Any help is very welcome,

-- Sarah

I have had the exact same problem and tried this solution and it works: Using PSExec (a free download from Microsoft) to connect to an instance using the NT AUTHORITY\SYSTEM account. Sample syntax:

PsExec -s -i "C:\...\Tools\Binn\ManagementStudio\Ssms.exe"

This will launch Management Studio as a Windows account that has native sysadmin permissions, and this will allow you to switch to mixed mode, enable the sa account, reset the sa account, add your own Windows account as an administrator, etc.

A lot more information can be found in the following article:

http://www.mssqltips.com/sqlservertip/2682/recover-access-to-a-sql-server-instance/

However bear in mind that you may be breaking some contract, just wanted to note it. :)


MySqlDump's DISABLE KEYS have no effect on import I have a follow up to my previous question regarding import speed with Inno-Tables (surprise!).

Scenario
I try to import some big* database dump on my local dev machine in reasonable time. We have a lot of KEYs attached to the tables which have turned out to be a bottleneck but are still important for our live system.

My approach after asking question above was to delete KEY ... statements from dump, import and re-add keys.

However I often find myself editing a current dump to import it locally and I stumbled across these funny "comments" (The disable/enable keys-lines)

--
-- Dumping data for table `monster`
--

LOCK TABLES `monster` WRITE;
/*!40000 ALTER TABLE `monster` DISABLE KEYS */;
INSERT … INSERT … INSERT
/*!40000 ALTER TABLE `monster` ENABLE KEYS */;
UNLOCK TABLES;

But in fact these "comments" are conditional MySql-Statements

That was news for me but ok, given the output form mysql --version everything looks fine to me:
mysql  Ver 14.14 Distrib 5.5.38, for debian-linux-gnu (x86_64) using readline 6.3

What I assume
The table is locked (fine, it's just me on the dev mashine). Then the keys as defined in table schema are disabled, data is imported, keys are enabled.
So during "data insertion"-phase there should be no time wasted on keys but rather examined after all data is inserted.

I would be thinking this is the same behaviour as if I delete all the KEY 'foo' (foo)'-lines from the dump, import the dump and run a script with ADD KEY 'foo' ... afterwards.

What I observe
It is way faster to manually delete the keys, import and re-add keys then relying on conditional DISABLE KEYS statements created my mysqldump

Manual editing of dump + mysql import + adding keys = 15+8+8 ≈ 30min
Plain mysql import: given up, (I'm just getting paid for 8 hours/day >:) )

I can't help but thinking I'm missing something very fundamental here (or database is trolling me).

You cannot rely on DISABLE KEYS; and ENABLE KEYS; for InnoDB because it is not implemented in the InnoDB Storage Engine. Running ALTER TABLE ... DISABLE KEYS; and ALTER TABLE ... ENABLE KEYS; were designed for MyISAM. As it says in the MySQL Documentation for ALTER TABLE:

  If you use ALTER TABLE on a MyISAM table, all nonunique indexes are created in a separate batch (as for REPAIR TABLE). This should make ALTER TABLE much faster when you have many indexes.

  For MyISAM tables, key updating can be controlled explicitly. Use ALTER TABLE ... DISABLE KEYS to tell MySQL to stop updating nonunique indexes. Then use ALTER TABLE ... ENABLE KEYS to re-create missing indexes. MyISAM does this with a special algorithm that is much faster than inserting keys one by one, so disabling keys before performing bulk insert operations should give a considerable speedup. Using ALTER TABLE ... DISABLE KEYS requires the INDEX privilege in addition to the privileges mentioned earlier.

  While the nonunique indexes are disabled, they are ignored for statements such as SELECT and EXPLAIN that otherwise would use them.

No mention is ever made of InnoDB in context with ALTER TABLE ... DISABLE/ENABLE KEYS;

Even if you run ALTER TABLE ... DISABLE KEYS; against an InnoDB table, it generates a warning:

mysql> show create table mytimes\G
*************************** 1. row ***************************
       Table: mytimes
Create Table: CREATE TABLE `mytimes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `totalTime` int(11) NOT NULL,
  `totalTimeDesc` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> alter table mytimes disable keys;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+-------+------+-------------------------------------------------------------+
| Level | Code | Message                                                     |
+-------+------+-------------------------------------------------------------+
| Note  | 1031 | Table storage engine for 'mytimes' doesn't have this option |
+-------+------+-------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

That's why there is no affect. Please recall that @jynus mentioned the same thing in his answer in bulletpoint 7.

Keep in mind also that MyISAM keeps data and indexes in two separate files (.MYD for data, .MYI for indexes), so it would be trivial to disable and enable indexes. InnoDB keeps the PRIMARY KEY and row data in the same InnoDB pages (via the Clustered Index). Secondary indexes will carry the PRIMARY KEY as an attachment to every secondary index leaf entry. Since the data and indexes are intertwined via the Clustered Index, no one has, as of yet, attempted to implement DISABLE KEYS and ENABLE KEYS in InnoDB.


Here are a couple of approaches you can try.

DECLARE @lng INT;

SET @lng = 3;

/*Approach 1*/
SELECT y.*
FROM   YourTable AS y
WHERE  y.lng IN (1,@lng)
       AND y.lng = (SELECT MAX(y2.lng)
                    FROM   YourTable AS y2
                    WHERE  y2.vi_id = y.vi_id
                           AND y2.lng IN (1,@lng))
ORDER BY id;

/*Approach 2 (assumes id is PK and all vi_id have a lng=1 row) */
SELECT
    CASE WHEN y2.id IS NULL THEN y1.id ELSE y2.id END AS id,
    CASE WHEN y2.id IS NULL THEN y1.title ELSE y2.title END AS title,
    CASE WHEN y2.id IS NULL THEN y1.lng ELSE y2.lng END AS lng,
    CASE WHEN y2.id IS NULL THEN y1.vi_id ELSE y2.vi_id END AS vi_id
FROM   YourTable y1
       LEFT JOIN YourTable y2
         ON y1.vi_id = y2.vi_id
            AND y2.lng = @lng
WHERE  y1.lng = 1
ORDER BY id;


Is it correct way having status or Summary columns in parent table or should choose another precedure? My Parent Table is "Jobs" table, and there is child table that records Job's actions named "JobActions" as below:

Jobs (JobId, JobType, CurrentStage, AssignedTo)

JobActions (JobActionId, JobId, CreateDate, ActionType, FromUserId, ToUserId, Message, Detail)

in my grid in web page I list grid columns as below:

JobId, CreateDate, CurrentStage, AssignedTo, LastActionType, LastActionDate,...

In grid for calculating below columns I use joins to child table (very slow when there is many users and many data)

CreateDate  ==> First(or min) JobActions' CreateDate,

LastActionType ==> Last JobActions' ActionType,

LastActionDate ==> Last(or Max) JobActions' CreateDate

I have about five columns like this that needs many join to JobActions or Another tables, So I Decided to change Job table like below:

Jobs (JobId, JobType, CurrentStage, AssignedTo, CreateDate, LastActionType, LastActionDate)

the last three columns always updated whenever any jobaction added to JobActions.

Question 1: Is this a correct version, (am I denormalized my tables?)
Question 2: Should I use trigger for this type of jobs or should update from inside my code?
Question 3: Is this best practice? is there any better solution for this type of problems.

Question 3: Is this best practice? is there any better solution for this type of problems.

"Best Practice" might be dependent on the environment.  The recommendation would be to try and figure out why a VIEW is "slow".  If you can't make it faster, then your solution(s) seem to be fine (after considering the other Business Requirements).

  Question 1: Is this a correct version, (am I denormalized my tables?)

In your original design, the JobActions table would be a Slowly Changing Dimension (SCD) Type 2 type of data.

In your other design, the additional columns (in the Jobs table) would be akin to SCD Type 4 type of data.

Your model looks fine (to me).

  Question 2: Should I use trigger for this type of jobs or should update from inside my code?

Let's start with "should the update be placed inside your Application Code"? IMO - no.  All applications should follow the same process when working with the same set of data.  The only way to ensure that is to have the update logic within the database.

Should it be in a trigger? This answer may be highly opinionated.

On one hand, it could be easy to implement the required update logic in the trigger. On the other hand, implementing the delete logic might be difficult/impossible.

Alternative

Place the code in a Stored Procedure.

That is: Follow the Model-View-Control (MVC) philosophy

hide your data Model from the application
the application fetches the data from a View
all DML manipulation is Controlled via Stored Procedures

In your example, you don't really need to create a VIEW on the tables (thus hiding your data Model).  You just need to prevent (through GRANT/REVOKE) the application from performing DML outside of the Stored Procedures.


Database Design for updatable sequential records I am working on creating a data model for storing data related to production tracking.  I work for an engineering firm that models and analyzes data for our clients.  There are several steps in the process and the process is constantly updating.

I am trying to model the processes and include parent processes and sequential order of processes.

For example:

Process Table
---------------------
ProcessID - uniqueidentifier
ProcessName - varchar
ProcessDescription - varchar
...

ProcessOrder Table
---------------------
ProcessID - uniqueidentifier FK - Process
ParentProcessID - uniqueidentifier FK - Process
ProcessOrder - int
...

The ProcessOrder column in the ProcessOrder Table would simply store a number representing which sequential step in the parent process it represents.

For example, a modeling procedure has the following steps: create new empty model, name model, enter model parameters.  The Process Table would look like:

ProcessID | ProcessName | ProcessDescription
-------------------------------------------------
UUID1     | Modeling    | Create Model of Data
UUID2     | New Model   | create new empty model
UUID3     | Name Model  | name model
UUID4     | Parameters  | enter model parameters

The ProcessOrder Table would look like:

ProcessID | ParentProcessID | ProcessOrder
--------------------------------------------------
UUID2     | UUID1           | 1
UUID3     | UUID1           | 2
UUID4     | UUID1           | 3

The issue with this design is that when the workflow gets updated, the process order will change and I will need to update the ProcessOrder record for the process that changed and for all subsequent records with the same ParentProcessID.

Is there a better way to store this kind of data and maintain normalization?

If all you need is to store which step of your process comes after which previous step, then all you need is the following:

ProcessID | ParentProcessID | PreviousProcessID

Of course, you will need a FK constraint to make sure that (ParentProcessID | PreviousProcessID) points to a valid (ParentProcessID | ProcessID)

If I understood your requirements and this design is valid, then it is easy to insert/remove/move around steps in your process - you do not have to propagate any changes to your child tables, because they refer to your primary key on (ParentProcessID | ProcessID).

HIH


Join select queries on an id in SQL Server I have three tables that SELECT from and I'd need to join the results.

It feels like this should be a rather easy task but I struggle with the syntax.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

What I came up with so far is..

TableA has id (int), name (varchar), comment (varchar) and extra (varchar).
TableB has id (int) and char (varchar)
TableC has eid (int) and info (varchar).

SQL query:

SELECT
    b.count, a.id, a.name, a.comment
FROM
    (SELECT *
     FROM TableA a
     WHERE id + 100 IN (SELECT distinct(eid)
                        FROM TableC
                        WHERE NOT eid IS NULL)
    )
JOIN
   (SELECT
        char, count(*) AS count
    FROM TableB b sel
    GROUP BY char) on a.id + 100 = b.char

What I'd like to have is the output of the top select (TableA) with the count of TableB where a.id+100 is b.char

Excuse my amateur-ish sql..

If I understand correctly, I think this should work...

We're checking that TableA.id+100 is in TableC.eid and then counting the number of times TableA.id+100 appears as TableB.char.

It's always good to create a SQLFiddle though so that others can easily pick up your schema and code to debug.

SELECT
  a.id,
  a.name,
  a.comment,
  COUNT(b.char) AS count
FROM TableA a
INNER JOIN TableB b
  ON a.id+100 = b.char
INNER JOIN TableC
  ON a.id+100 = c.eid
GROUP BY a.id, a.name, a.comment;

As Aaron Bertrand pointed out below, using EXISTS would help to eliminate double counting on where duplicate eids appear in  TableC.

SELECT
      a.id,
      a.name,
      a.comment,
      COUNT(b.char) AS count
FROM TableA a
INNER JOIN TableB b
  ON a.id+100 = b.char
WHERE EXISTS (SELECT * FROM TableC where eid = a.id+100)
GROUP BY a.id, a.name, a.comment;


Oracle ASM Failgroup The Oracle 11gR2 Automatic Storage Managament Administrator's Guide says:

  When Oracle ASM allocates an extent for a mirrored file, Oracle ASM allocates a
  primary copy and a mirror copy. Oracle ASM chooses the disk on which to store the
  mirror copy in a different failure group than the primary copy.

It also says:

  If you omit the failure group specification, then Oracle ASM automatically
  places each disk into its own failure group, except for disk groups containing disks on
  Oracle Exadata cells.

My question is for the following scenario how ASM will create failure groups:

In my server when there is one disk controller and one physical hard disk having many logical volumes and created disk groups.
When server is having two controller card and two or more hard disks with logical volumes created and used for diskgroups.

My main question is, I want to understand how this ASM decides failgroups? Please some one can guide

As the official documentation says, unless you specify failure groups explicitly,
ASM will place each disk into its own failure group
(it doesn't have any knowledge about your HW infrastructure).

Note that Oracle doesn't recommend using an OS LVM with ASM.


Blocking on readable secondary replica We recently migrated from LogShipping standby/read-only setup to Multi Subnet AG setup with readable secondaries.
Generally on old setup we have select queries running for longer duration as the database in question is over 20 TB and has mix of read write workload on primary.
After moving to new setup of AG we have started seeing blocking which i am not able to understand. Why select queries on secondary are blocking other select queries in my readable secondary replica instance, even when the database being queried has RCSI enabled?
Below is what i have captured

Lead blocker is some long running SELECT query does not show any
specific waittype as particular, lets say SPID 129

SPID 129 blocks a session ID 45 ( i am sure this is not a user
id) for almost 6 hours which is dependent on spid129 and wait type is
LCK_M_SCH_M

Here comes the problem when this SPID 45 just blocks all other select
queries now in that 6 hour duration.

I am not able to understand what is happening. Can someone help me troubleshoot or look in correct direction?

Queries against read-only secondary replicas implicitly run in snapshot isolation regardless of the session isolation level or RCSI setting. This avoids blocking due to DML changes. Read-only queries still acquire schema stability locks, which will block DDL operations by redo threads and visa-versa.

SPID 129 blocks a session ID 45 ( i am sure this is not a user id) for
almost 6 hours which is dependent on spid129 and wait type is
LCK_M_SCH_M

In your case, it seems a redo thread is waiting for a schema modification lock but is blocked by a very long-running query/transaction schema stability lock. Other queries are in turn blocked by the redo thread.
Take a look at the SELECT query execution plan and duration of the transaction for remediation.


SQL Server: Query Efficiency Having vs Subquery I have the following query where I used a sub query since I can't use aggregates in the where clause.

SSMS suggested I use the HAVING clause but then I would need to group by all the other columns (right?) which I did not want to have to do so I ended up using the sub query.

Is using the sub query the most efficient way or should I use the Having clause so I don't have to read from the same table twice?

Is the difference negligible?

select *
from [DW_STAGE].[ETL].[Claims]
where pd_dt > (
        select dateadd(m, - 12, cast(max(pd_dt) as date))
        from [DW_STAGE].[ETL].[Claims]
        )

If your table's clustered index has pd_dt as the first column, this may be the most optimal query. You could simplify it by breaking it into two parts, like so:

DECLARE @dt date;
SELECT @dt=DATEADD(month, -12, CAST(MAX(pd_dt) AS date)) FROM DW_STAGE.ETL.Claims;

SELECT * FROM DW_STAGE.ETL.Claims WHERE pd_dt>@dt;

Things you could do to improve performance:

Reduce the number of columns from * to those specific columns that you need.
Create a covering, non-clustered index for these columns, where the first column of the index is pd_dt or pd_dt DESC.


How to control number of columns after pivoting date as column Here is the sample query:

DECLARE @cols NVARCHAR(MAX) DECLARE @query NVARCHAR(MAX)

Select @cols = COALESCE(@cols +',[' +  CONVERT(VARCHAR(10), ReportedDate,120)+']',      '[' + CONVERT(VARCHAR(10), reportedDate, 120)
+']')       FROM (SELECT DISTINCT CONVERT(VARCHAR(10),reportedDate,120)         AS reportedDate FROM Activity       ) PV ORDER BY reportedDate desc

SELECT @query = 'SELECT * FROM (    SELECT  b.Description, CONVERT(VARCHAR(10), reportedDate, 120) as reportedDate,Status FROM Activity    left join ActivityType b     on b.activityTypeId = Activity.ActivityTypeId ) AS t PIVOT  (   COUNT(reportedDate)    FOR reportedDate IN( ' + @cols + ' )' + ') AS p ;'

EXEC SP_EXECUTESQL @query

This is my sample image:
I want to control the date columns limit into only 8 columns.
as reference of what I mean please see the second table and its different from the first table.

So in my first table date column from '2020-01-07' up to '2019-11-12'
However my second table only up date '2020-01-07' up to '2019-11-19'

I want to get the result as shown in this second Table
assuming that this date '2020-01-07' is newly added.
so the '2019-11-12' will not be included.

I hope I did will explaining my point.

change the inner query to following using TOP () GROUP BY and ORDER BY to limit to 8 distinct date

SELECT  TOP (8) CONVERT(VARCHAR(10), ReportedDate, 120)  AS ReportedDate
FROM    Activity
GROUP BY CONVERT(VARCHAR(10), ReportedDate, 120)
ORDER BY ReportedDate


What are the differences between "Stored Procedures" and "Stored Functions"? So a comment from this question mentions, that there is a slight difference in "Stored Procedrues" and "Stored Funtions" in PostgreSQL.

The comment links to a wikipedia article but some of this don't seem to apply (e.g. that they can be used in a SELECT statement).

The syntax itself seem to be a little bit confusing:

CREATE FUNCTION emp_stamp() RETURNS trigger AS $emp_stamp$
    BEGIN
       [...]
    END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp
    FOR EACH ROW EXECUTE PROCEDURE emp_stamp();

You create a FUNCTION but refer to it as a PROCEDURE.

So what's the difference between these two?

Officially, PostgreSQL only has "functions".  Trigger functions are sometimes referred to as "trigger procedures", but that usage has no distinct meaning.  Internally, functions are sometimes referred to as procedures, such as in the system catalog pg_proc.  That's a holdover from PostQUEL.  Any features that some people (possibly with experience in different database systems) might associate with procedures, such as their relevance to preventing SQL injections or the use of output parameters, also apply to functions as they exist in PostgreSQL.

Now, when people in the PostgreSQL community talk about "stored procedures" or "real stored procedures", however, they often mean a hypothetical feature of a function-like object that can start and stop transactions in its body, something that current functions cannot do.  The use of the term "stored procedure" in this context appears to be by analogy to other database products.  See this mailing list thread for a vague idea.

In practice, however, this distinction of function versus procedure in terms of their transaction-controlling capabilities is not universally accepted, and certainly many programmers without database bias will take a Pascal-like interpretation of a procedure as a function without return value.  (The SQL standard appears to take a middle ground, in that a procedure by default has a different transaction behavior than a function, but this can be adjusted per object.)  So in any case, and especially when looking at questions on Stack Exchange with a very mixed audience, you should avoid assuming too much and use clearer terms or define the properties that you expect.