Seems SQL Server couldn't optimize optional parameters I am trying to optimize a query which has many parameters via Profiler. I tested below queries:

declare @location nvarchar(200) = null;
select count(*) from Places where @location is null or Location = @location

cpu:1249 duration:64 reads:456760

declare @location nvarchar(200) = null;
select count(*) from Places

cpu:315 duration:20 reads:15001

As you can see, both queries are identical because the @location parameter is optional and it is null, but performance metrics are showing very different results. It seems SQL Server couldn't understand both queries are exactly the same.

Why SQL Server can not optimize such a simple query?

both queries are identical because the @location parameter is optional and it is null

They are on this invocation, but the execution plan for the query will be cached and may be reused when the @location parameter has a different value. SQL Server has to build a plan that will produce correct results for all possible cases.

To resolve this, you could write the query using Dynamic SQL, or you could use an option (recompile) query hint. More details, including the trade-offs for each method, in the references below:

Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White
Dynamic Search Conditions - Erland Sommarskog
Catch-all queries - Gail Shaw


Freeing space after deleting rows I have a SQL Server 2008 instance and one table is quite large (log data) so I delete old logs. Now I start to read articles on this problem, here is one.

As I see it there is no solution! If I shrink the database and then rebuild the index, the database will be about the same size as before. If I don't rebuild the index, the database will shrink, but performance will be low, which is not an option.

So shrinking for production databases seems not to be an option. Is there any solution? Does deleting from the database make any sense? How do big companies delete log data from their databases?

Why do you need to shrink the file? Rebuilding the CI after a massive delete (without shrinking the file) will help make those pages available for future data. Shrinking the file just means it has to grow again when you add more data. So there was no benefit to shrinking it in the meantime - ooh, you freed up some space, but only temporarily.

Another thing you should look at is table partitioning (depending on SQL Server edition). It can make disposing of old data much, much easier.


How to handle table design with variable columns I have a table design scenario and as a non-DBA type, would like opinions on which is more scaleable.

Say you are asked to record information on houses for a metro area, starting with a small neighborhood (200 houses) but eventually growing to 5000000+ houses.

You are required to store base information: ID# (A unique lot # we can use as a unique index), Addr, City, State, Zip. Fine, simple table will handle it.

But each year, you are going to be asked to record extra information about all of the houses - and WHAT information will change each year. So, for example, first year, you are asked to record the owners last name and square footage. The second year, you are asked to keep the last name, but dump the square footage and instead begin collecting the owners first names.

Lastly - each year the # of extra columns will change. Might start with 2 extra columns, then go to 6 next year, then back down to 2.

So one table approach is to try to add the custom information as columns in the house tables so there is only one table.

But I have a situation where someone laid out the tables for this as:

"House Table" columns: ID, Addr, City, State, Zip - with one row per house

ID   Addr              City     State  Zip
-------------------------------------------
1    10 Maple Street   Boston      MA  11203

2    144 South Street  Chelmsford  MA  11304

3    1 Main Avenue     Lowell      MA  11280

"Custom Info Table" columns: ID, Name, Value - with table looking like:

ID   Name             Value

1    Last Name        Smith

2    Last Name        Harrison

3    Last Name        Markey

1    Square Footage   1200

2    Square Footage   1930

3    Square Footage

So there are multiple of rows for each individual house record. Each year when the optional information required changes, this table is literally rebuilt, so next year it might look like:

1    Last Name    Smith

2    Last Name    Harrison

3    Last Name    Markey

1    First Name   John

2    First Name   Harry

3    First Name   Jim

Eventually you amass 100,000 house rows AND one year there are 10 extra pieces of information; the second table now is 1,000,000 rows of information, many of which have redundant (description) information. The database requirements overall are that people will need to get the house row information + associated custom field values thousands of times per day.

So my question: Would it be bad (or horrible) practice to instead either:

A) Lay out house table with guess at max # of custom columns (called perhaps "1" through "10") and insert those custom values right in the house rows

OR

B) Store the custom information in the house table, but each year when the requirements change, rebuild the house table with only the # of columns needed for custom information, with the idea that the requirements could go nuts and you never know how many maximum optional fields might be asked for?

Thanks, hope this makes sense!

You have pretty much 4 choices:

NoSQL - definition Every record is stored as a set of Key/Value pairs. It is very flexible and fast. Not all of the report writers out there support this style of storage. There are many example database implementations of NoSQL. The one that seems to be most popular right now, is MongoDB.

EAV - definition This is where you turn either the whole table or a portion (in another table) on its side. This is a good choice if you already have a relational database in-house that you can't move away from easily. The custom info table example you gave is a good example of an EAV table.

Standard tables with XML columns - Think of this as NoSQL meets relational tables. The data stored in an XML column can be any format that XML supports, including multiple correlated sub-data. For the columns that you know are going to be "regular" columns, they can be built as the appropriate type of column to store the data (LastName, Address, City, State, etc.).

Standard tables with lots of extra columns - You have a relational database, you can't use either XML or EAV, and NoSQL is not an option. Add lots of extra columns of each type. I would guess 30 or more varchar, 30 or more integer, 15 or more numerics. And once you use a column for a value, don't re-use it. And don't delete the column either.

Out of all of these solutions, my own opinion is that you will find either the NoSQL or the EAV approach to be the most successful with the least amount of refactoring your code and your schema.

You will have a situation where you collect data one year, not the next, and then collect it again afterward. Trying to get the older data updated with the correct information is problematic and expensive. Storage is neither.


Average value of a row in MySQL I have a row which primary key value is 4 and there are another 8 values.
Those are 45  78 89 54 98  14  19  70. I want to select the average value, ie 58.37 from this row. How do I do this ?
The avg() function takes one argument only but i need to send 8 arguments.
My table picture is

There is a rather simpler solution. (Using information_schema can be dangerous, if there are thousands of tables in it and it can be slow at time)

I propose to use an actual function. The goal is to do a pivotal query (convert col to rows). This can be accomplish using UNION ALL.
The PK of the table in question is passed to the function allowing it to calculate and return the Average.

The beauty about this is you can extend the function and do 95 Percentile and other type of Stats. You can use that function like other functions.

If a new column is added, all you need to do is add that column to the function.
I think it's easier then to have to exclude the column you don't want.

CREATE FUNCTION `row_avg`(p_pk int ) RETURNS decimal(16,2)
BEGIN

DECLARE p_output decimal(16,2);

 select avg(A.rate) into  p_output
 from (

select rate1 as 'rate' from rating where id = p_pk
union all
select rate2 as 'rate' from rating where id = p_pk
union all
select rate3 as 'rate' from rating where id = p_pk
union all
select rate4 as 'rate' from rating where id = p_pk
union all
select rate5 as 'rate' from rating where id = p_pk
union all
select rate6 as 'rate' from rating where id = p_pk
union all
select rate7 as 'rate' from rating where id = p_pk
) as A ;

  RETURN p_output;

END

table:

CREATE TABLE `rating` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `role` int(11) DEFAULT NULL,
  `student_name` varchar(10) DEFAULT NULL,
  `rate1` tinyint(4) DEFAULT NULL,
  `rate2` tinyint(4) DEFAULT NULL,
  `rate3` tinyint(4) DEFAULT NULL,
  `rate4` tinyint(4) DEFAULT NULL,
  `rate5` tinyint(4) DEFAULT NULL,
  `rate6` tinyint(4) DEFAULT NULL,
  `rate7` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

Usage:

select row_avg(id) from test.rating ;
select row_avg(id) from test.rating where id  =3 ;

Enjoy !


Track row origin in MySQL multi source replication Hi I would like to create a multi source replication topology where a few different master databases replicate to one big slave database.
I want to know for each and every row on the slave from which master it came from.
My first instinct would be to add a "system_id" column on each master table but I wonder if there is a better way to track the origin of the rows.

Thanks

Are the rows going into the same table(s)?  If so, your suggestion of an extra column from the Master is probably the only way.

Or are the rows going into different databases (or tables)?  If so, then nothing is needed -- the different dbs/tables provide the clue.

Another approach may be "replicate rewrite" -- this turns same table into different table.  If you then need to "combine" the data (on the unified Slave), use UNION ALL and/or a VIEW.  See here.


SQL Server 2008 R2 (Suspect) mode - how to repair? I have SQL Server 2008 R2 database in Suspect mode. I tried to fix it running this query:

EXEC sp_resetstatus ‘yourDBname’;
ALTER DATABASE yourDBname SET EMERGENCY
DBCC checkdb(’yourDBname’)
ALTER DATABASE yourDBname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB (’yourDBname’, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE yourDBname SET MULTI_USER

But the repair output was this message:

Warning: You must recover this database prior to access.
Msg 8921, Level 16, State 1, Line 5
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.
Warning: The log for database 'ServeDB' has been rebuilt. Transactional consistency has been lost. The RESTORE chain was broken, and the server no longer
has context on the previous log files, so you will need to know what they were. You should run DBCC CHECKDB to validate physical consistency. The database has
been put in dbo-only mode. When you are ready to make the database available for use, you will need to reset database options and delete any extra log files.
Msg 8921, Level 16, State 1, Line 9
Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors.

How to repair the database fully? This repair which i made, works only for some days, then database again goes to Suspect mode...

You cannot repair this database, and the repair you've potentially made has not been complete. You should make sure to understand why your database is becoming corrupt - check your disk system as that's the most likely culprit.

Then restore your database from a backup - but make sure to run DBCC CHECKDB on that very backup to ensure it's not corrupt itself.

DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS is the very last resort you should ever go to. Before that, you should restore a valid backup. Once you do use REPAIR_ALLOW_DATA_LOSS, don't expect your database to magically come back to life again - most likely, irreparable damage has occurred - especially in this case since it complains about system table corruption.


You can do this by putting the GROUP BY in a subquery. Here is a solution:

CREATE TABLE #1 (ID int, TotalAmount decimal(20,2));
CREATE TABLE #2 (ID int, Individualamount decimal(20,2));

INSERT #1 VALUES (100, 1000), (101, 500);
INSERT #2 VALUES (100, 500), (100, 300), (100, 100), (101, 200), (101, 300);

SELECT a.ID
, a.TotalAmount
, c.[Sum(IndividualAmount)]
, d.[Diffence(TA-IA)]
, e.[%of diff]
FROM #1 a
LEFT JOIN
(
    SELECT b.ID, [Sum(IndividualAmount)] = SUM(b.IndividualAmount)
    FROM #2 b
    GROUP BY b.ID
) c
    ON c.ID = a.ID
CROSS APPLY
(
    SELECT [Diffence(TA-IA)] = a.TotalAmount - c.[Sum(IndividualAmount)]
) d
CROSS APPLY
(
    SELECT [%of diff] = 100.0 * [Diffence(TA-IA)] / NULLIF(a.TotalAmount, 0)
) e;

Results:

ID   TotalAmount  Sum(IndividualAmount)  Diffence(TA-IA)    %of diff
100      1000.00                 900.00           100.00   10.000000
101       500.00                 500.00             0.00    0.000000


Is there a standard database layout for repetitive information? I was recently tasked with creating a database to store a modest amount of data. I am learning as I go, but my main hold is figuring out the best way to layout the data. The problem is that for each test, there are n number of identical objects being tested and data is recorded for each, but the test id, date, test operator, etc.. are the same for all n objects.

So I see two options. I could have one entry per test but then have data columns named "test_result_1, test_result_2, ... test_result_n" for every result that we will be recording. This would lead to many columns for each test entry and would make searching for a test result difficult since you would have to search over n columns.

The other option is to have one entry per object, which would result in n entries having identical test ids, dates, test operators, and any other information that is specific to the test but not the objects. This would reduce the number of columns per entry and make searching for test results easier, but it would also increase the total amount of data stored and lead to more input required when done manually.

So is there any standard method for handling data of this type? It is a bit repetitive either way and I can see pros and cons to each side. I could see it being something that is considered on a case by case basis, but I figured I would ask if there is something I'm not taking into account that makes one option the standard.

You need two related tables. E.g.:

Table "Test": TestId PK, operator, test_date

Table "Result": ResultId PK, TestId FK, Object, TestResult

Where the "Test" table is the main table and the "Result" table is the details table. Test.TestId is a primary key (PK). Result.TestId is a foreign key (FK) related to Test.TestId. I.e. you have a one-to-n relation between "Test" and "Result".

In Access' relationships editor it would look like this:

Splitting a table into different tables to avoid repeating information is called Database normalization (Wikipedia)


How to avoid MySQL slave to have errors after crash I have a MySQL master/slave configuration (MariaDB 10.0.17). Sometimes, the slave or the master server crashes. After the crash and restart, I get some errors of duplicated keys on slave. The server get stuck into these errors and I need to take action by deleting the duplicated key entries so it goes on.

How to avoid this or, if not possible, do this "slave recover" automatically?

Regarding de-synchronization of a single or master server: Mosty, the cause of these problems come from the fact that the master is not in full ACID mode or it is using MyISAM tables. In order to avoid problems with a server, only use InnoDB as the engine of your tables and set innodb_flush_log_at_trx commit to 1. This is not efficient -you will lose a lot of performance in a busy server except if you can get advantage of the group commit, but it is the only way to guarantee data consistency with disk (no transaction loss).
Many people, that do not have appropriate hardware (hardware cache), are forced to relax the value of this variable to 0 or 2, but in that case, you (or your hardware) is responsible to assure the availability of the disk/service in case something goes wrong (crash).

The second reason where disk can be out of sync with a master or single server is not using sync_binlog=1. By default, this is set to 0, and that means that you can write data internally, but that doesn't assure that it is written to the binary log. This shouldn't be an issue if you assume the master is always going to be back online, but you must understand that may be an issue. Activating it almost doubles your IOPS requirements, so be careful. It is not usually necessary and I do not recommend you to blindly activate it.

Third, the most common cause of desynchronization is the storage of the replication position in the non-transactional master.info file in the slave. This is the default configuration. I highly recommend changing the configuration of the variables master_info_repository and relay_log_info_repository to use TABLE (InnoDB transactional tables) instead of FILE. This will get rid of the desynchronization problems when the slave crashes.

Additionally, in order to make sure that the master and slave are always in sync, and that no transactions are skipped, I recommend you to use the GTID replication, available in MySQL 5.6/MariaDB 10. While it does not prevent the desync of the master and slave, it makes easier to identify it and resync master and slave. This is a tutorial on how to enable it on MariaDB 10. Please note that GTID implementations between MySQL and MariaDB are not compatible. Once GTID is running, things like failover are greatly simplified.

There could be other reasons for desynchronization, like undeterministic updates, or replication filters. Avoid the later, and you can mitigate the former by using row-based replication binlog_format = ROW.

On a different note, it is important to remark that MySQL default replication protocol is asynchronous, so one can never guarantee the same state on the master and the slave. If slave drift and other associated issues are a no-go, you can try alternatives like the semi-sync plugin for replication or Galera cluster, which both make tighter links between nodes.
Alternatively, I recommend you to both constantly monitor and repair replication problems with tools like pt-heartbeat, pt-table-checksum and pt-table-sync, which will avoid the need to manually provision the slave for every incident you encounter.

In order to have a more accurate answer, I recommend you to hire a consultant so that you can apply the best strategy in your case (some of the above may have an important impact on your performance while not being your specific problem).


How to search a MySQL database with encrypted fields Suppose I need to encrypt certain table-fields of a MySQL database. Additionally, I need to search some of those fields I did encrypt.

How would one search those fields anyway?

Decrypting each record step by step is no option: Suppose I have multiple of thousands of records. It would take too much time and space to decrypt each record and check if each single record matches the search.

UPDATE 2012-09-07

Adding further details to the database schema would be OK, since I'm about to implement a new application. Furthermore, I need to extend applications currently running in production. But even for those application, adding further details would be OK.

UPDATE 2012-09-08

Encryption is the kernel of this question.

Access restrictions, as proposed by some answers, already apply - but do not fit the formal requirement to encrypt data.

This formal requirement is not Payment Card Industry Data Security Standard [PCI].

Obviously they are not meant to be viewed, therefore searching on them would be problematic.

One trick I have used in the past is to hash the encrypted data before encrypting it, and storing the hash in an indexed column.  Of course, this only works if you are searching on the whole value; partial values will not have the same hash.

You could probably extend this by making a "full text" index of hashes, if you needed to, but it could get complicated really fast.

ADDENDUM

It's been suggested that I add a footnote to my answer per a fairly lengthy debate in chat about vulnerability to dictionary attacks, so I will discuss this potential security risk to the above approach.

Dictionary Attack:  A dictionary attack is when someone pre-hashes a list of known values, and compares the hashes to your hashed column in the database.  If they can find a match, it's likely that the known value is actually what is being hashed (It's not definite though, because hashes are not guaranteed to be unique).  This is usually mitigated by hashing the value with a random "salt" appended or prepended so the hash will not match the dictionary, but the above answer cannot use a salt because you lose the searchability.

This attack is dangerous when dealing with things like passwords: if you create a dictionary of popular password hashes, you can then quickly search the table for that hash value and identify a user that has such a password and effectively extract credentials to steal that user's identity.

It is less dangerous for items with a high degree of cardinality, like SSN's, credit card numbers, GUIDs, etc. (but there are different risks [read: legal]  associated with storing these, so I am not inclined to advise on storing them).

The reason for this is in order for a dictionary attack to work, you need to have pre-built a dictionary of possible values and their hashes.  You could, in theory, build a dictionary of all possible SSNs (a billion rows, assuming all formatting permutations are removed; multiple dozens of trillions of entries for credit cards)... but that's not usually the point of a dictionary attack, and basically becomes comparable to a brute-force attack where you are systematically investigating every value.

You could also look for a specific SSN or credit card number, if you're trying to match a SSN to a person.  Again, usually not the point of a dictionary attack, but possible to do, so if this is a risk you need to avoid, my answer is not a good solution for you.

So there you have it.  As with all encrypted data, it's usually encrypted for a reason, so be aware of your data and what you are trying to protect it from.


Creating a single recid stream for multiple tables I'd like to have a single reservoir of RECIDs to apply across multiple tables.

Obviously I can do that by creating a table with a single auto-increment field and inserting every time I need a new RECID.

But that produces a lot of 8-byte+ records that have no real purpose.

A little resident function to delete each record when produced would keep the table pruned back, but I'm wondering whether there's a cleaner way to get to the same goal.

You don't need a "reservoir", rather a way to generate a unique number.

Usually it is sufficient to give that task to one table you already have, and use AUTO_INCREMENT there, plus LAST_INSERT_ID().

But it you really need a sequence generator:

http://forums.mysql.com/read.php?10,616636,617041 (Simulating sequences)
http://dev.mysql.com/doc/refman/5.7/en/information-functions.html (search for "sequence")
http://forums.mysql.com/read.php?10,252807,262654 (A sequence solution)


Show the number of all tables, views and base tables How to do that?
I was thinking of something like:

SELECT COUNT(A.table_name), COUNT(B.table_name)
FROM information_schema.tables AS A, information_schema.views AS B;

But sadly it gives me bad numbers (it multiplies the first COUNT with the second one) and I still don't know how to get BASE TABLES.

information_schema.tables includes tables and views, so you only need:

select count(*)
from information_schema.tables
where table_type in ('VIEW', 'BASE TABLE')

If you want the numbers separately for each type, use a group by

select table_type, count(*)
from information_schema.tables
where table_type in ('VIEW', 'BASE TABLE')
group by table_type

The reason your statement isn't working is because you are creating a cross join (cartesian product) between the two system view.


Detect Index Coalesce Is there a way to know if a COALESCE has been done on an index?  Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes.

Note:  I am not referring to the COALESCE that returns the first non-null expr in the expression list, but the one that is used to make free space in a block contiguous like this:

ALTER INDEX [Index Name] COALESCE;

It is not completely clear what your after with your question. I assume you want to know if and importantly when an alter index .. coalesce was performed.

I think without (an in other answers already mentioned auditing setup) it is impossible to get such a date (although I would want to know if someone knows better).

That said, the effects of an alter index coalesce can be made visible with a analyze index .. compute statistics.

create table ix_test (
  a number
);

create unique index ix_test_ix on ix_test(a);

insert into ix_test
select
  rownum
from
  dba_objects, dba_objects, dba_objects
where
  rownum < 1e7;

commit;

Now that the table is filled, the index is analyzed and some index related values extracted:

analyze index ix_test_ix compute statistics;

select
  LEAF_BLOCKS,
  AVG_LEAF_BLOCKS_PER_KEY,
  CLUSTERING_FACTOR,
  SAMPLE_SIZE
from
  user_indexes
where
  index_name = 'IX_TEST_IX';

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE
----------- ----------------------- ----------------- -----------
          2                   19875                 1       15152

All but one row deleted and index analyzed (again):

delete from ix_test where a != 500000;
analyze index ix_test_ix compute statistics;

The query above returns the same values.

Coalescing the index:

alter index ix_test_ix coalesce;
analyze index ix_test_ix compute statistics;

The query above now returns

LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR SAMPLE_SIZE
----------- ----------------------- ----------------- -----------
          1                       1                 1           1

So, there is a possibility to find out if there was a coalescing done, but it's not straight forward and probably useless, depending on your context.


What are some key configuration settings for using an InnoDb table for session storage? I want to start clustering my PHP app servers but don't want to go for a sticky-sessions load balancing setup at this point. However, I want sessions that are persisted to disk and implement proper locking. I have a server already dedicated to the database with comfortable amount of free ram so I'd like to consider using an additional instance of MySQL purely for session storage on the same machine. Currently my sessions folder on the file system is ~131Mb and has ~23k files. I'm planning on using InnoDb with SELECT ... FOR UPDATE to implement the session locking.

Is running two instances of MySQL optimized for different purposes on the same server a bad idea? (If so, why?)

For the sessions-only instance what are some optimization tips? I want it to be persistent, but don't mind losing a little bit of data in the event of a disaster whereas with the main app db I don't want to risk any data loss.

If I can spare say 256Mb of ram for this MySQL instance (main instance has ~6Gb), how should I allocate it?

Disable query cache or no?

What settings can I use to reduce disk writes (since I don't care about durability so much with this instance)? E.g. innodb_locks_unsafe_for_binlog, innodb_flush_method, etc..

You could experiment with innodb_flush_log_at_trx_commit

According to the MySQL Documentation on innodb_flush_log_at_trx_commit

  If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is
  written out to the log file once per second and the flush to disk
  operation is performed on the log file, but nothing is done at a
  transaction commit. When the value is 1 (the default), the log buffer
  is written out to the log file at each transaction commit and the
  flush to disk operation is performed on the log file. When the value
  is 2, the log buffer is written out to the file at each commit, but
  the flush to disk operation is not performed on it. However, the
  flushing on the log file takes place once per second also when the
  value is 2. Note that the once-per-second flushing is not 100%
  guaranteed to happen every second, due to process scheduling issues.

  The default value of 1 is required for full ACID compliance. You can
  achieve better performance by setting the value different from 1, but
  then you can lose up to one second worth of transactions in a crash.
  With a value of 0, any mysqld process crash can erase the last second
  of transactions. With a value of 2, only an operating system crash or
  a power outage can erase the last second of transactions. InnoDB's
  crash recovery works regardless of the value.

  For the greatest possible durability and consistency in a replication
  setup using InnoDB with transactions, use
  innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master
  server my.cnf file.

  Caution

  Many operating systems and some disk hardware fool the flush-to-disk
  operation. They may tell mysqld that the flush has taken place, even
  though it has not. Then the durability of transactions is not
  guaranteed even with the setting 1, and in the worst case a power
  outage can even corrupt the InnoDB database. Using a battery-backed
  disk cache in the SCSI disk controller or in the disk itself speeds up
  file flushes, and makes the operation safer. You can also try using
  the Unix command hdparm to disable the caching of disk writes in
  hardware caches, or use some other command specific to the hardware
  vendor.

Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.

As far as innodb_flush_method, the default is best. I wrote an earlier post to describe the effects of tweeking it.

As for running multiple instances of MySQL, it's OK as along as you have enough memory for the OS and enough memory of DB Connections for the multiple instances.


How to Hide Sql Server Fingerprint? I have a sql server which being monitor by Nagios through snmp string.
One day, i received some alert from nagios come with some sensitive information like:

Critical Alert: dbi connect (Server name: ABC, Encrypted Password: 1, User:
Sa.......)

How can i hide this ?

Why would you want to?

  Critical Alert: ... User: >>> sa <<< ...

This could indicate a script or application attempting to connect to a database using "sa" credentials, which should never be allowed.

  Critical Alert: ... >>> Encrypted Password: 1 <<<, User: sa ...

This could actually indicate someone trying to hack your "sa" account.

Both of these require your attention.


Postgres optimizing join of 2 large tables I have 2 tables one with around 1M entries and other around 4M. I want to join these tables in a materialized view. But the materialized view creation is taking too long ~4 hours.
I don't see the indexes being used, which is fine as per this answer. When a join involves the whole tables then indices don't help so much.
So what is the workaround? My tables have time-series data. Would TimeScaleDB extension be of any help? Any other suggestion to improve the performance ?
EDIT
CREATE TABLE public.fco_assets
(
  id serial,
  symbol character varying(64) NOT NULL,
  currency integer NOT NULL,
  CONSTRAINT fco_assets_pkey1 PRIMARY KEY (id),
  CONSTRAINT fco_assets_symbol_key1 UNIQUE (symbol)
)

CREATE TABLE public.fco_prices
(
  aid integer NOT NULL,
  cid integer NOT NULL,
  "time" timestamp without time zone NOT NULL,
  close double precision,
  CONSTRAINT fco_prices_pkey PRIMARY KEY (aid, cid, "time")
)

CREATE TABLE public.fco_chr_res
(
  pid integer NOT NULL,
  aid integer NOT NULL,
  cid integer NOT NULL,
  "time" timestamp without time zone NOT NULL,
  res double precision[] NOT NULL,
  CONSTRAINT fco_chr_res_pkey PRIMARY KEY (pid, aid, cid, "time")
)

There are around 8M rows in fco_chr_res. I have created various indices on price and res tables besides the primary key which should act as an index.
 select  count(*)
 FROM fco_prices pr
 join fco_assets a on pr.aid = a.id and pr.cid=a.currency
 LEFT JOIN fco_chr_res ch ON pr.aid = ch.aid AND pr.cid = ch.cid AND pr."time" = ch."time"

For above query here is the Explain/Analysis report

Aggregate (rows=1 loops=1)  1   1
Gather (rows=1 loops=1) 1   1
Aggregate (rows=1 loops=1)  1   1
Merge Left Join (rows=45636664 loops=1) 45636664    1
Merge Inner Join (rows=21023282 loops=1)    21023282    1
Index Only Scan using fco_prices_pkey on fco_prices as pr (rows=21023282 loops=1)   21023282    1
Sort (rows=3428 loops=1)    3428    1
Seq Scan on fco_assets as a (rows=3428 loops=1) 3428    1
Index Only Scan using aid_cid_time on fco_chr_res as ch (rows=34245723 loops=1) 34245723    1

is there any scope for improvement ?

First, about the count query: the explain gives me impression that the query takes time due to IO. The table fco_chr_res contains more than 34M rows and one row in index is around 20 bytes, which results into more than 600MB read from the index. This definitely will not fit work_mem, which is 4MB by default. So it is good to pay attention how good is the disk IO.
It was mentioned that it is timeseries data, i.e., it can be assumed that no data arrives with old timestamps. In such case count on old data can be pre-calculated and stored in an aggregation table. I don't think MATERIALIZED VIEW can help here, since refreshing it to include newly arrived data will re-calculate entire view. Continuous aggregates in TimescaleDB are designed to deal with aggregates with time bucketing in timeseries data, however joins are not supported (which I discuss later).
Another note is that the explain plan shows that the estimated number of rows assumes that there are no referential constraints between the tables, while the comments clarify that they are. Thus introducing foreign keys might help query planner to calculate better estimates.
As discussed in comments applying count is not a common case, instead columns from all three tables will be returned from a query with the same joins. If the query projection will include columns outside indexes, then the query plan will be different and it might increase the amount of data processed significantly. Notice, the indexes are large and close to the actual tables in size, since they include almost all columns. You might want to reconsider them, but they will be needed for foreign keys and duplicate elimination.
Such query produces millions of rows, which is incomprehensible for humans. Thus there is a question how the join result is used and analysed. In particular, will there be grouping and aggregations and/or selections applied to the join result?
Since the question explicitly asks about TimescaleDB and I affiliated with it, I expand my answer with TiemscaleDB. If the data and its usage are timeseries related such as data are inserted mainly in time order and queries do aggregation on time or limited to time intervals, then TimescaleDB can be a good fit and be used to get better performance and to utilise some timeseries specific features. TimescaleDB optimises how data are stored, so it is efficient to insert recent data and to query data in a time interval. Performance can be further improved by using community features: continuous aggregates and compression. In particular, continuous aggregates materialise aggregation results continuously by adding results on new data on schedule without recalculating materialized results if not need. However, a continuous aggregate can be defined on a single timeseries table (hypertable) and no joins can be used. The reason is that in general changes to one of the table (e.g., fco_assets) can affect materialised result, which will waste previous work. A possible solution is to create continuous aggregates on each timeseries table (hypertable) and then join the continuous aggregates in queries. To further investigate if TimescaleDB is applicable, I suggest to check TimescaleDB support slack, where TimescaleDB users discuss also such issues.
Summary of recommendations from the above:

Check disk IO and see if it can be improved: a better disk(s) or a cloud machine with better disk IO;
Materialize the result of count for old data. The automation will require some work;
Define foreign keys;
Reconsider indexes;
Understand better your application if it fits timeseries use cases supported by TimescaleDB.


The difference between what EXPLAIN output shows and the actual number of rows returned by the query means your table statistics are out of date. The explain plan displays the estimated number of rows it thinks the engine will need to read. As the manual says,

  MySQL explains how it would process the statement

(emphasis mine). Strictly speaking, the database engine cannot possibly know how many rows it needs to process until it actually processes all of them.

You can run the ANALYZE TABLE command to update statistics; EXPLAIN might be able to better estimate how many rows would be read.


What are the top 3 performance Issues that you encounter with your SQL Servers? I am a student from Fontys University in Eindhoven, and I am currently carrying out a series of interviews to help with the development of a SQL Server tool and I would like to get feedback from the experts in the field.

One of my questions is:

What are the top 3 performance issues that you encounter with your SQL Server Instances and how do you identify those problems?

Particularly I am interested in the scripts and the tools used to measure this.

Off the top of my head - top 3 query problems:

Implicit conversions
Bad indexing strategies (too many or not enough or the wrong kind)
Using SELECT * instead of naming just the fields you need

There's a lot more around server-level config problems, database schema problems, hardware problems, etc.  I wrote a script to quickly analyze servers looking for these kinds of problems:

http://www.brentozar.com/blitz/


Query Motivation for Cross Joins Say we have two tables 'Vendors' and 'Invoices'.
Then I understand a reason for doing a left join of
'Vendors' with ' Invoices'  along the VendorID column is to obtain information on Vendors without Invoices. A right join is basically a left
join with tables interchanged (This is the main motivation,right)?

But I cannot see a general motivation from cross joins in this sense,
i.e., if I am given tables Vendors, Invoices joined along VendorID like above, what information is it that Vendors Cross Join allows me to obtain that I cannot obtain through a left join, or otherwise?

Thanks.

A CROSS JOIN gives you the Cartesian product of all the rows in the two tables and then applies any filtering from the WHERE clause. It rarely serves a purpose for your more common table relationships, but there are some occasions where it's handy.

For example, you may need to assign relationships between a static table of Properties and some Items stored in another table. The easiest way to add a new Item and relate it's needed Properties would be to insert the results of a CROSS JOIN between the two tables into a linking table between them. Specific values for those properties could then be added later via other interfaces, but they would have the framework assigned already without having to hard-code the interface.


Calculate Employees' Total Working hours using SQL Server Below is the DDL of my table:

CREATE TABLE [dbo].[Logs](
    [ID] [int],
    [empid] [int],
    [time] [datetime],
    [In_Out] [varchar](3)
) ON [PRIMARY]
GO

I need to calculate the total working hours of an employee per day.

Shifts do not matter. Meaning, if an employee clocks in at 11:00 PM and clocks out at 7:00 AM the next day. His total working hours would be 1 hour the first day and 7 hours the next.

There is an In_Out flag to represent if the badge swipe was In/Out. And there can be n number of breaks in between. We need to account for the breaks when calculating the total sum though.

Could someone help out here please?

This is quite a common requirement and I have seen many business need the same type of query. The below is just something I have knocked up super quick but it'll work for you:

CREATE TABLE #logs
(
    [empid] int,
    [Time] DATETIME,
    [In_Out] VARCHAR(3)
);
INSERT #logs
VALUES
(1, '2019-01-01 09:00:00', 'In'),
(1, '2019-01-01 12:00:00', 'Out'),
(1, '2019-01-01 13:00:00', 'In'),
(1, '2019-01-01 17:00:00', 'Out'),
(1, '2019-01-02 23:00:00', 'In'),
(1, '2019-01-03 07:00:00', 'Out');

WITH t
AS
(
    SELECT COALESCE(i.[empid], o.[empid]) AS [empid]
         , COALESCE(i.[Date], o.[Date]) AS [Date]
         , COALESCE(i.[Time], CAST(o.[Time] AS DATE)) AS [in]
         , COALESCE(o.[Time], DATEADD(DAY, 1, CAST(i.[Time] AS DATE))) AS [out]
         , RANK() OVER (PARTITION BY i.[Time] ORDER BY o.[Time]) AS r
      FROM (SELECT [empid], CAST([Time] AS DATE) AS [Date], [Time]
             FROM #logs
            WHERE [In_Out] = 'In') AS i
 FULL JOIN (SELECT [empid], CAST([Time] AS DATE) AS [Date], [Time]
             FROM #logs
            WHERE [In_Out] = 'Out') AS o
        ON i.[empid] = o.[empid]
       AND i.[Date] = o.[Date]
       AND i.[Time] < o.[Time]
)
SELECT [empid], [Date], SUM(DATEDIFF(HOUR, [in], [out])) AS [Hours]
  FROM t
 WHERE r = 1
GROUP BY [empid], [Date]
ORDER BY [empid], [Date]

DROP TABLE #logs;

It works by doing a self join where each side of the join is pre-filtered for the specific entry type you are looking for. Then, you rank the results to ensure you dont count double entries. After that, its simple maths.


postgresql: accept subdomain wildcards? I want to accept all db connections from servers with domain mydomain.com so that all subdomains like tiger.mydomain.com, venus.mydomain.com will have access to the postgresql server.

I've read that you have to create a reverse dns server so that it can lookup the domain and get the appropriate ip address. but it is not clear how to do this or a postgresql specific case.

From the manual:

  address

  Specifies the client machine addresses that this record matches. This field can contain either a host name, an IP address range, or one of the special key words mentioned below.

  ...

  If a host name is specified (anything that is not an IP address or a special key word is processed as a potential host name), that name is compared with the result of a reverse name resolution of the client's IP address (e.g., reverse DNS lookup, if DNS is used). Host name comparisons are case insensitive. If there is a match, then a forward name resolution (e.g., forward DNS lookup) is performed on the host name to check whether any of the addresses it resolves to are equal to the client's IP address. If both directions match, then the entry is considered to match.

  ...

  A host name specification that starts with a dot (.) matches a suffix of the actual host name. So .example.com would match foo.example.com (but not just example.com).

In other words, use

  host all all .mydomain.com md5

or similar in pg_hba.conf, along with reverse DNS configured appropriately as above.


Reducing the size of mysql files I have been working on MySQL database whose size is growing quite huge and I want to save on space on my cloud. I have tables whose engine is Innodb which holds the largest amount of data. I have already run OPTIMIZE TABLE  on them which saved some space. Index data length is only a few MBs. I am looking for any other way to reduce the size of my growing tables and if possible the whole database. I am only keeping binary logs for 3 days for recovery. I will appreciate a solution that will cause no downtime or minimal downtime and risk. I also have a local replicated data of the same.

u can do something like this:
add this to /etc/my.cnf

[mysqld]
expire_logs_days=3

and mysqld will delete them logs for you

if you talking about reduce the size of the table , i would advise this:

1st u need to sort from workbench or phpmyadmin , which tables consumes the most.
2nd maybe u can start plan on how to reduce that 'biggest table'
3rd u think to think or consult with your superior , how long u need to keep the record , some company might wanna keep 3months record only ,then dump and keep somewhere(cloud or Harddisk) , or 1 year or 3 years .
4th before dump , maybe u can create archive table.
eg: your original table 'test' , archive table rename as 'test_archive' then move older records or records that u want to dump there.
5th dump and move to somewhere safer.


Get top 1 row value from third table while joining 3 tables mssql I am new to mssql .here I need to get some data using joins between three tables .

Header join Lines join Images --> Result

Header Table :

Line Table :

For each header record we can have multiple line items .

Images Table :

Each Image will have unique Image or multiple images .Need to get 1 image url from the list of items for the header record.

Result Set :

Query :

SELECT HT.O_ID,
       HT.Type,
       HT.Total,
       IM.Image

FROM HEADER_TABLE HT

JOIN LINE_ITEM_TABLE LIT
ON LIT.O_ID = HT.O_ID

JOIN IMAGE_TABLE IT
ON IT.IMAGE = LIT.ITEM_ID

WHERE IT.SECTION = 'Retail'

This query returns multiple rows .But I need one unique row for each Header record.

Can anyone help me to fix .

You can use CROSS APPLY to SELECT just the TOP 1 image from each particular header. APPLY is similar to a function you can create "on the go" that links columns or expressions from outside to it's filters or joins.

SELECT
    -- Header columns:
    HT.O_ID,
    HT.Type,
    HT.Total,

    -- Columns from the CROSS APPLY result
    I.Image
FROM
    HEADER_TABLE HT
    CROSS APPLY (
        SELECT TOP 1 -- Just retrieve 1 row (for each HT row)
            IT.IMAGE
        FROM
            LINE_ITEM_TABLE LIT
            INNER JOIN IMAGE_TABLE IT ON IT.ITEM_ID = LIT.ITEM_ID
        WHERE
            LIT.O_ID = HT.O_ID AND  -- Link the outmost header "HT" record to it's lines "LIT"
            IT.SECTION = 'Retail') AS I

You can add an ORDER BY inside the CROSS APPLY to determine which image will get selected. You can also change the CROSS APPLY to OUTER APPLY if you want header rows to display even when there is no matching record coming from the APPLY operator (the IMAGE column will be NULL).


The SQL Server (MSSQLSERVER) service terminated unexpectedly. Event ID: 7034 Unable to find the cause why the SQL Server Service was Terminated Unexpectedly? (Event ID: 7034)

I looked into the SQL Server Error Logs but could find the reason about why the SQL Server service was terminated.

So check the windows event Viewer and I found the following error msg:

Event Message : The SQL Server (MSSQLSERVER) service terminated unexpectedly. It has done this 1 time(s).

Error Event Id : 7034

Since last night my SQL server service is terminating with the this error.

This service terminates and restarts automatically and sometimes doesn't restart we have to manually start it again.

Version Details:
Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Standard Edition (64-bit)

OS: Windows Server 2012 R2 Standard 6.3  (Build 9600: ) (Hyper-visor)

Error Event Id : 7034

Service Control Manager (SCM) stops services and driver services. It also reports when services terminate unexpectedly or fail to restart after it takes corrective action.

How to Resolve

Change the recovery actions

To resolve this issue, change the recovery actions that the Service Control Manager (SCM) will take when a service fails.
To perform this procedure, you must have membership in Administrators, or you must have been delegated the appropriate authority.

To change the recovery actions for a service:

Open the Services snap-in by clicking the Start button, Control
Panel, and Administrative Tools, then double-clicking Services.
Note: For Windows Vista, use the Classic View display option in
Control Panel to see the Administration Tools.
In the details pane of the Services snap-in, right-click on the name
    of the service you want to stop and select Properties.
Click the Recovery tab and specify the recovery actions for the
    service (for example, restart the service or the computer, take no
    action, or run a program).

For your further ref Here and Here


Customizing OEM notification title and body I am writing a reporting tool that accepts email notifications for incedents , alerts and events coming from the OEM.

The tool parses the email title and body to extract information from it.

I was wondering if it is possible to customize the title and body of the email by including some string in it.

How can I use the OEM12c console settings to achieve this ??

In OEM 12c Enterprise Manager, you can change both the body and the subject of email alerts through the web-based EM console.

Once logged in, you should see menu options in the top right corner like "Setup" and "Help" with down arrows indicating a drop-down menu.

Click "Setup" -> "Notifications" -> "Customize Email Formats..."

From here, you can select the type of email that is being sent (Metric Alerts, Job Status Changes, etc.), the format (Long or Short), and you can also customize the subject and body of the email by clicking "Customize".  A sample email based on your changes will be shown below the "Customize" button.

When customizing an email format, you can add plain text in both the subject and body, and you can also use the predefined labels and attributes (links to these are on this same page) to include dynamic information based on the type of alert and other status values.

Once you save this, your email notifications from OEM 12c should reflect whatever changes you made.

In addition, if you go to "Setup" -> "Notifications" -> "Notification Methods", you can change the name that appears as the sender of the email (e.g., "OEM 12c Administrator") as well as the email address that the alert appears to be sent from.  This also allows you to use rules and filtering on the email alerts by filtering on the "FROM:" email address and sender name.


Can I tell SQL Server not to place data in the buffer cache? One of the servers I support has an ETL job that runs every morning. This isn't an SSIS package, just a series of stored procedures executed via SQL Server Agent. The job takes several hours to run, and regularly brings the server's page life expectancy down under 100, which isn't surprising as it hits pretty much every table in the main operational database.

Most of the data it's reading isn't needed during regular operational use, so it's just wasting space in the buffer cache until the job is done and the cache can start filling back up with data that's actually useful. Is there any mechanism through which I can tell SQL Server not to replace what's in the buffer cache when running these stored procedures?

Can I tell SQL Server not to place data in the buffer cache?

No. SQL Server must bring pages into memory in order to work with them. There are no configuration options to directly affect how long pages remain in memory, or the various policies used to manage buffer pool usage.

If your process involves changing data, you may be able to influence the impact on the buffer pool to some extent (ha!) by issuing a manual checkpoint. This will force modified (dirty) pages to be written to disk, freeing them up for reuse.

Resource governor currently only supports limiting workspace memory (used by sorts, hashing operations, and parallel buffers). It does not provide a way to limit the use of the buffer pool for cached data and index pages.

Generally, you will have to rely on SQL Server making good decisions around space usage in the buffer pool. The ETL job(s) likely benefit from the current arrangement, so you might well find a negative performance impact were you able to limit its use of the buffer pool.

If you are certain that the vast majority of the buffer pool is being consumed by finished ETL operations, you might find it worthwhile to explicitly flush dirty pages and clear buffer pool at the end of the ETL process:

CHECKPOINT;
DBCC DROPCLEANBUFFERS;

This is usually not indicated for a production system, but you may find it helps in this specific situation. With no dirty pages and an empty buffer pool, SQL Server will begin to acquire pages for the new workload at its maximum rate.

In particular, the buffer pool being lower than the server memory target will enable ramp-up reads - where whole extents are read in whenever a page is requested from persistent storage. This is effective by default in Enterprise Edition and can be enabled for other editions (even Express) using global trace flag 840, see:

FIX: An application query that reads lots of data may be slow, and the SQL Server service may take a long time to start in SQL Server 2005

From that link:

  This hotfix exposes trace flag 840. When trace flag 840 is turned on, SQL Server can perform larger I/O extent reads to populate the buffer pool when SQL Server starts. The larger I/O extent reads populate the buffer pool faster. Additionally, the larger I/O extent reads improve the initial query compilation and the response time when SQL Server starts.

Although that text mentions SQL Server starting, it also applies to buffer pool repopulation as I described above.

You can of course also help this process along by running dummy queries against structures you want to bring into memory at the end of the ETL process.


Implicit conversion causing error part of the time I have been scratching my head about this one for the last day or so - I just can't see why a procedure works in one environment, but then fails in another due to a conversion error (same data, same code).

The plan for (a heavily cut down version of) the working server is here: https://www.brentozar.com/pastetheplan/?id=B1jZWTfOf

(This doesn't seem to be working, so I've uploaded the plan XML to pastebin here: https://pastebin.com/47Q6nniw)

A bit of background for the relevant columns causing the problem:

The propertyInst table holds a column called ValueStr, which is an nvarchar data type. The GeneralLedgerCode table has a column id of data type int. Our developers are attempting to join between the two tables. The ValueStr column holds text data, XML data, int data, decimal data etc. As a matter of course we decided to add a function to ensure only integers are parsed (ISNUMERIC(ValueStr) = 1). What we didn't realise is that this would then also try to convert any decimal values - this is causing a conversion error in production of the nature:

Conversion failed when converting the nvarchar value '0.1' to data type int.

My question here is, given that the Table Scan of the propertyInst table will be picking up all numeric values including decimals, why is the implicit conversion in the scalar operator then not failing with the same conversion error? The output of the ISNUMERIC query returns some of these decimals. I can't see how the attached plan is working at all.

Does an implicit conversion operator never fail outright, and is the conversion error coming from the probe residual in the hash match operator? Then again, how could the value even make it to the operator when it would fail the implicit conversion?

As a small assistance, the plan image is here, with the Compute Scalar I'm asking about circled.

In addition, I can see that there are no actual rows logged against the scalar operator - does this mean the engine decided not to use that particular operator at runtime due to the conversion error?

Any help would be appreciated.

The calculation of the compute scalar operator is deferred to when it is actually used (the probe residual of Node ID = 1) and the rows that would have failed for you are filtered out by the previous Hash Match (Node ID = 3).

Have a look at Compute Scalars, Expressions and Execution Plan Performance by Paul White for more information.

There is no indication in execution plans that evaluation of an expression is deferred, or when (at which node) it was actually evaluated. You can normally code defensively around this using CASE (which comes with an evaluation order guarantee) and/or TRY_CONVERT.

Related Microsoft feedback: SQL Server should not raise illogical errors


Replicating data from Oracle to MySQL I work with a vendor that does data analytics, and they currently receive a replication stream from some of our databases using a product called Goldengate (which is very expensive). Goldengate has been great - it replicates transactions from the Tandem-NSK source and can apply the changes into any supported database - they're using MySQL at the remote end. We're switching our billing system to Oracle, and while we could continue to use Goldengate to move these logs, I'd like to see if there's another option.

We initially chose Goldengate because nothing else could get data out of the Tandem NSK, but now that we're moving to Oracle, there may be some more native (or at least simpler) choices. I've got nothing against them - like I said, it works great - but I'm hoping that two mainstrem databases are easier to do replication between than the NSK.

Are there any products of methods that would help get transactional data from an Oracle system into an MySQL database? I'm not sure if there's any way to do this kind of replication natively (I know we can do Oracle -> MSSQL using native replication, but not any way to target MySQL that I'm aware of), or if anybody knows of a product that could facilitate this (and costs less than Goldengate).

Thanks for any suggestions!

From your question, it doesn't sound like you need true transactional semantics, and that asynchronous replication would be fine.

If that's the case, it's fairly trivial to build a replication system using triggers within Oracle to track data changes, and then replaying those changes into MySQL, using something like perl (or other scripting languages) as the glue code to talk between both systems using SQL.

This might sound like it would be a lot of overhead, but we've built numerous systems like this for our clients, and the overhead from Oracle's standpoint is pretty low; we've run it on some OLTP systems powering top 1000 websites that had hefty transactional workloads, and Oracle handled it like a champ.


Populating a Junction Table I'm a SQLite and SQL beginner so please bear with me. I'm trying my hand a creating a simple podcast index database. Here's the schema for my EPISODE, CAST_MEMBER, and EPISODE_CAST tables.

CREATE TABLE EPISODE
(
    id                  INTEGER PRIMARY KEY,
    show_number         INTEGER UNIQUE NOT NULL,
    air_date            DATE UNIQUE NOT NULL,
    runtime             VARCHAR(255) NOT NULL
);

CREATE TABLE CAST_MEMBER
(
    id          INTEGER PRIMARY KEY,
    person_name VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE EPISODE_CAST
(
    episode_id      INTEGER NOT NULL,
    cast_member_id  INTEGER NOT NULL,
    PRIMARY KEY(episode_id, cast_member_id),
    FOREIGN KEY(episode_id) REFERENCES EPISODE(id),
    FOREIGN KEY(cast_member_id) REFERENCES CAST_MEMBER(id)
);

I'm trying to find a quick way to populate the EPISODE_CAST table. I know what all the relationships should be, I just don't want to have to write a million insert statements. For example, one cast member has been on every episode of the show so I naturally want 550 rows added to the table

episode_id | cast_member_id
===========================
1          | 1
2          | 1
3          | 1

… and so on.

So here's the query that I've written

INSERT INTO EPISODE_CAST (episode_id, cast_member_id) VALUES
   ((SELECT id FROM EPISODE WHERE show_number BETWEEN 1 AND 550),
   (SELECT id FROM CAST_MEMBER WHERE person_name='John Smith'));

But I'm only ever getting one row inserted. (The 1|1 row.) So I'm obviously missing something here. A loop of some sort…

Once I have that one cast member in there, I'd also like to do something similar with the other cast members. But for them I'll be using an IN clause with the set of all the episodes that person was in (most are non-contiguous).

Again, sorry for my naïvety. But thanks for any help.

Try a Cartesian or cross join.  You can test the select clause separately to ensure it selects the desired data before adding it into the insert.

INSERT INTO EPISODE_CAST (episode_id, cast_member_id)
SELECT  episode.id AS episode_id,
        cast_member.id AS cast_member_id
FROM EPISODE CROSS JOIN CAST_MEMBER
WHERE show_number BETWEEN 1 AND 550
AND   person_name='John Smith';


Why is there an aggregate in this UPDATE plan? Given this

declare @Data table (id int, fact char(1));
declare @Summary table (id int, collected varchar(99));

insert @Data(id, fact)
values
    (1, 'a'),
    (1, 'b'),
    (2, 'c'),
    (2, 'd'),
    (2, 'e');

-- Form a list of unique id values
insert @Summary(id, collected) select distinct id, '' from @Data;

-- Accumulate the fact values into collected
update s
set collected = collected + d.fact
from @Summary as s
inner join @Data as d
    on d.id = s.id;

select * from @Summary;

I had expected

id   collected
---  ---------
1    ab
2    cde

However what I get is

id   collected
---  ---------
1    a
2    c

The first fact per id is written and the others skipped. The reason is obvious from the plan

where the Stream Aggregate passes through the first row per id; 5 rows are passed to it from the Nested Loop and it passes 2 rows to the Compute Scalar.

The result and general plan shape is the same with and without primary keys. Changing to temp tables, or real tables makes no difference. I can reproduce on SQL Server 2017 and 2019.

My question is what is the theoretical basis for inserting the aggregate into the plan? My best guess is it is a form of Halloween protection. I understood its purpose was to prevent a row leapfrogging ahead of the current scan position and hence being updated a second time. I can sort-of see that applying here as a row in @Summary would be touched many times in the absence of the aggregate. It seems a very expansive application of Halloween protection, though.

I know there are ways to achieve this - STRING_AGG being the most obvious. My real use-case is around constructing JSON. This is just a minimal reproducible example. My specific question here is about understanding the semantics and behaviour of the optimizer.

This isn't Halloween protection, it's normal UPDATE semantics.

Check out this warning from the docs on UPDATE:

  The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is if the UPDATE statement is not deterministic.

A SET statement doesn't accumulate like you expect it to* - it just updates each qualifying row in the Summary table once.

Since the join results in duplicate potential values for collected, the optimizer introduced a Stream Aggregate - essentially it groups by the "primary key" of the heap (the row locator, referred to as Bmk1000 in the execution plan) to get just one value for collected in each row.

Looking at the plan XML, you can see that the internal only "ANY" aggregate was used to choose among the possible values of collected and fact:

<ScalarOperator ScalarString="ANY(@Summary.[collected] as [s].[collected])">
  <Aggregate AggType="ANY" Distinct="false">
    <ScalarOperator>
      <Identifier>
        <ColumnReference Table="@Summary" Alias="[s]" Column="collected" />
      </Identifier>
    </ScalarOperator>
  </Aggregate>
</ScalarOperator>

*this sort of accumulation does work when you add a variable assignment into the mix, although it's still not "supported."  This is referred to as "quirky updates" (reference)


How many control files should I have? On the Oracle Database Appliance, the default deployment only gives you a single control file.

I find this a little puzzling. The single control file causes a policy violation in the automatically configured Enterprise Manager DB Console and Oracle's recommendation is still, as far as I can tell, that you should always have at least two control files on separate drives and file systems.  Personally, I've always had three copies, just to be on the safe side.

The ODA is configured with ASM and does have a good bit of storage redundancy using triple mirrored drives. Is it OK to run with a single control file in this configuration?

Adding a second control file to the same disk group might not make much sense, would multiplexing the control files to the SSD disk group or perhaps the OS drives of each node make more sense?

KISS principle:  Do you have the storage that itself offers you the required availability level (i.e. disk array)? And you do have RMAN backups with a recovery catalog (you need it anyway, for DUPLICATE), don't you? If yes and yes, my version is: no secondary control file at all, one member per each redo group, as there are no secondary datafiles (quite obviously, even for system01.dbf) or no secondary archivelogs.

Otherwise your database is unnecessarily doing stuff that belongs on the storage level. I would just leave storage things on the storage level - the mirror is done there, and it is done effectively. Or, if you want cross-site mirror, do it on ASM level. Database is occupied with database-related processing. The software mirroring on database level only makes sense if you use bare hard disks.

Most people conservatively stick to having three control files, though. Probably Oracle support would also recommend you to use three.

Not only I've never practically benefited from having a second or a third control file; I'm unable to think of a scenario when secondary copy would be justified (i.e. it provides some benefit for  the lack of simplicity). Logical corruption in control file would be propagated to a second control file, just as in case of hardware mirroring. Physical corruption wouldn't be propagated in either case. If storage fails, you lose redo or data - you need to restore via RMAN anyway. You lose control file, you can restore it with RESTORE CONTROLFILE, which only costs extra minute at most. If you've lost redo, the recovery is incomplete. If you didn't lost redo (but lost all control files), the recovery is complete de facto, but Oracle insists on OPEN RESETLOGS (loss-less in such case).


Split MySql table into two tables with One to many Relationship I have an old database table in MySql. It is used to store the orders from online ecommerce.  Simplified diagram looks like this:

+----+------+-----------------------+-------+--------------+
| id | name | address               | phone | order_number |
+----+------+-----------------------+-------+--------------+
|  1 | Joe  | Joes Address          | 1111  | 1390842      |
|  2 | Paul | Pauls Address         | 2222  | 9082309      |
|  3 | Greg | Gregs Address         | 3333  | 0928340      |
|  4 | Lucy | Lucys Address         | 4444  | 9028340      |
|  5 | Paul | Pauls Address         | 2222  | 8958399      |
|  6 | Tom  | Toms Address          | 5555  | 9084024      |
|  7 | Lucy | Lucys Another Address | 4444  | 9801983      |
|  8 | Paul | Pauls Another Address | 2222  | 0982304      |
+----+------+-----------------------+-------+--------------+

For each order there is a separate name address and phone number. Some customers placed the order 2 or more times ( Lucy and Paul).  Paul placed two orders when he lived at Pauls Address and one order when he moved to Pauls Another Address.  Lucy placed an order and than another order once she moved.

What I need to do is split this table into two tables.  One table will be user_account table that will hold only the name and the phone number, while another table will hold the addresses and will be related to users_account by FK.

user_accounts should look like this:

+----+------+-------+
| id | name | phone |
+----+------+-------+
|  1 | Joe  | 1111  |
|  2 | Paul | 2222  |
|  3 | Greg | 3333  |
|  4 | Lucy | 4444  |
|  5 | Tom  | 5555  |
+----+------+-------+

while addresses should look like this:

+----+-----------------------+-----------------+
| id | address               | user_account_id |
+----+-----------------------+-----------------+
|  1 | Joes Address          | 1               |
|  2 | Pauls Address         | 2               |
|  3 | Gregs Address         | 3               |
|  4 | Lucys Address         | 4               |
|  5 | Toms Address          | 5               |
|  6 | Lucys Another Address | 4               |
|  7 | Pauls Another Address | 2               |
+----+-----------------------+-----------------+

How would I go about generating those 2 tables from my old single table.  Any help appreciated.  I'm completely stuck with this, and not even sure what query should I look for in google to point me into right direction. There is about 100k rows in the original table.

PS.

I would like to create the user_accounts table based on distinct phone numbers in regular table.

Not enough time to test or write out all of the table creation but this is a start...

First create the table to hold the users. You can get data from the following query. One thing I don't know how you want to handle is if a user exists with a different phone number.

SELECT DISTINCT name
  , phone
FROM old_table

After you have the user_accounts table join it back to the old_table to get the address along with the new user id.

SELECT DISTINCT a.address
  , b.id
FROM old_table a
INNER JOIN user_accounts b on a.name = b.name and a.phone = b.phone


Tuning Postgres for Single connection use? Or is postgres wrong tool? Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions?

I'm a social scientist looking to use Postgres not as a database to be shared by multiple users, but rather as my own tool for manipulating a massive data set (I have 5 billion transaction records (600gb in csv) and want to pull out unique user pairs, estimate aggregates for individual users, etc.).

All the advice I can find online on tuning (this, this, this etc.) is written for people anticipating lots of concurrent connections. Anyone have basic rules of thumb for someone working on a database alone for data manipulation?

UPDATE:
- This also means almost no writing, except to creation of new tables based on selections from the main table. (Apparently that's important -- thanks Erwin!)

Updated 2:
- I'm on a Windows 8 VM with 16gb ram, SCSI VMware HD, and 3 cores if that's important.

If possible i would do following : Define 2 tablespaces, one for data , one for result tables. work_mem and maintenance_work_mem to 1Gb and 2Gb , shared buffer to high only if you run two or more queries same time to same set of data, if you don't do that then shared memory will be more or less wasted.

Biggest problems you will have is that Postgresql uses only one core per session , so easiest way to get more done is just run several queries on different sessions. Second problem will be disk i/o, several fast disks + different tablespaces help. Third is RAM settings. I would configure work_mem so that sessions*work_mem is somewhere at 10GB and keep maintenance_work_mem on 1-2GB. maintenance_work_mem can be set from query like this set maintenance_work_mem='6GB'; so you can set it higher when you do maintenance. You can also set work_mem like set work_mem='6GB' for session.

Table definitions also affect space used see: https://stackoverflow.com/questions/2966524/calculating-and-saving-space-in-postgresql . Probably not needed, but with 600GB of data scientific data it may help alot.


Does SQL Server require more RAM to hold columnstore indexed tables than traditional rowstore? I would like to implement (SQL Server 2014) clustered columnstore indexes on some very large, wide tables.  Will I need more RAM to support this?  If so, how do I determine how much?

More RAM as opposed to what?  Compared to a normal clustered index, the data is highly compressed, so should take less memory during querying.  However, the process of building the CCI can be very memory intensive.  See the product documentation

  Plan for enough memory to create columnstore indexes in parallel
  Creating a columnstore index is by default a parallel operation unless memory is constrained. Creating the index in parallel requires more memory than creating the index serially. When there is ample memory, creating a columnstore index takes on the order of 1.5 times as long as building a B-tree on the same columns.

  The memory required for creating a columnstore index depends on the number of columns, the number of string columns, the degree of parallelism (DOP), and the characteristics of the data. For example, if your table has fewer than one million rows, SQL Server will use only one thread to create the columnstore index.

  If your table has more than one million rows, but SQL Server cannot get a large enough memory grant to create the index using MAXDOP, SQL Server will automatically decrease MAXDOP as needed to fit into the available memory grant. In some cases, DOP must be decreased to one in order to build the index under constrained memory.


Check if PostgreSQL dump is valid Suppose a dump is provided from pgdumpall

pg_dumpall > outfile.bkp

How to verify if the outfile is a vaild database file for restoration. Just as a precautionary step before restoring it like

psql -f outfile.bkp postgres

For example: In MySQL we can head first few lines of the backup files produced from mysqldump for having SQL like syntax.

The command you show generates a text backup file.  You can use "head" or "less" on it to verify that it looks like a PostgreSQL text backup file, same as mysql.  I'd also run "tail", just to make sure it didn't get truncated.  It should end with a sign-off like:

--
-- PostgreSQL database dump complete
--

--
-- PostgreSQL database cluster dump complete
--

Of course to be really sure, you would just set up a dummy database server, and restore it.


How to prevent duplicate or similar data from getting processed? I have a stored procedure which calculates the distance between 2 coordinate points:

CREATE PROC loc.CalcDistanceByPostalCode
(
@From VARCHAR(10),
@To   VARCHAR(10)
)
AS
BEGIN
    DECLARE @data table (From_Place VARCHAR(200), From_Region VARCHAR(200), "To_Place" VARCHAR(200), To_Region VARCHAR(200), Distance decimal(8,2))
    DECLARE @postal1 table (ID int IDENTITY(1,1), place_name VARCHAR(200), region_name VARCHAR(200), latitude decimal(12,9), longitude decimal(12,9))
    DECLARE @postal2 table (ID int IDENTITY(1,1), place_name VARCHAR(200), region_name VARCHAR(200), latitude decimal(12,9), longitude decimal(12,9))

    INSERT INTO @postal1 SELECT distinct l.place_name, l.region_name, l.latitude, l.longitude
                         from loc.locations l
                         cross join loc.locations_postal lp
                         where postal_code like @From and lp.city_id = l.ID
    INSERT INTO @postal2 SELECT distinct l.place_name, l.region_name, l.latitude, l.longitude
                         from loc.locations l
                         cross join loc.locations_postal lp
                         where postal_code like @To and lp.city_id = l.ID

    INSERT INTO @data
    SELECT
        t1.place_name,  t1.region_name, t2.place_name, t2.region_name,
        loc.FN_CalcAerialDistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) -- Function calculates Distance
    FROM @postal1 as t1
    CROSS JOIN @postal2 as t2
    WHERE t1.latitude <> t2.latitude and t1.longitude <> t2.longitude

    DECLARE @Distance decimal(8,2) = 0
    select * from @data order by Distance desc
    SELECT @Distance = Distance from @data
    return @Distance;
END

It calculates correct result if @From and @To are different, but gives redundant data if @From and @To are same.

Example: Different @From and @To:

EXEC loc.CalcDistanceByPostalCode @From = '110%', @To = '400%'`

Result:

From_Place  From_Region To_Place     To_Region      Distance
Delhi       Delhi        Mumbai      Maharashtra    1160.65
New Delhi   Delhi        Mumbai      Maharashtra    1157.80
Delhi       Delhi        Vasai-Virar Maharashtra    1115.68
New Delhi   Delhi        Vasai-Virar Maharashtra    1112.87

Example: Same @From and @To:

EXEC loc.CalcDistanceByPostalCode @From = '110%', @To = '110%'`

Result:

From_Place  From_Region To_Place    To_Region   Distance
New Delhi   Delhi       Delhi       Delhi       3.51
Delhi       Delhi       New Delhi   Delhi       3.51

Do I need to write extra code to check if Distance is same then compare the From_Place with To_Place with the rows having the same distance? or any optimised way to prevent the duplicate data?

Try adding And t1.ID > t2.ID to the where clause , it will make sure that the join happens once .
To the following query

SELECT t1.place_name, t1.region_name, t2.place_name, t2.region_name, loc.FN_CalcAerialDistance(t1.latitude, t1.longitude, t2.latitude, t2.longitude) -- Function calculates Distance
FROM @postal1 as t1 CROSS JOIN @postal2 as t2
WHERE t1.latitude <> t2.latitude and t1.longitude <> t2.longitude


How to count multiple occurences on the same date my question is related to the table down below,  where I have to count multiple occurences of "Hours" if they happen on the same "Date" by the same person.

CREATE TABLE [dbo].[Time](
    [GUIDTime] [uniqueidentifier] NOT NULL,
    [GUIDEmployee] [uniqueidentifier] NULL,
    [Date] [datetime] NOT NULL,
    [Hours] [decimal](18, 2) NOT NULL,
    [Info] [varchar](256) NULL,
    [TaskDescription] [varchar](256) NULL,

 GUIDEmployee   Date            Hours
    1       2012-03-02          2.00
    1       2012-03-02          4.50
    1       2012-03-19          1.50
    1       2012-03-19          1.50
    1       2012-03-12          2.00
    1       2012-03-16          4.50

The problem is that in order to make an overtime calculation work, I need to count the hours worked. Normally people only enter it by the end of the day (7h,8h,9h) and thats it, but there are cases like above, where employees enter their hours seperately

2012-03-02   2.00
2012-03-02   4.50

and that makes the calculation invalid.

The outcome should look like

 GUIDEmployee   Date             Hours
    1        2012-03-02          6.50
    1        2012-03-19          3.00
    1        2012-03-12          2.00
    1        2012-03-16          4.50

Favorably I'd like to have another column in the Time table with the calculation of the "whole hours worked per day". I tried Count distinct and group by and tried searching for other ways to solve this issue through calculations in a seperate view but sadly to no avail.


Defining two distinct relationships between a user table and a company table A very basic design question here. I would like to build a scalable app and would want to know what's the best way forward.
So in the database there are two tables : users and companies.

users has the following fields:

-id
-name
-company_id
-join_date
-leave_date

companies has the following fields:

-id
-admin_id
-address

I want a 1-to-M between users and companies as one company could have many users, but one user belongs to only one company. Also, I want to have a 1-to-1 on users and companies because one company has one admin and vice versa.
I'm not sure if I am tackling this right. The admin role would basically have more privileges on the system. But I cannot understand if it should be a 1-to-1 or not. Who would the parent be, etc.

To guarantee that a company, which can have many users, can ever have at most one admin, and further guarantee that the user defined as the admin is also a user defined for the same company, create a separate admin table. However, the foreign key definition has to be created in a specific way.

create table Companies(
  ID      int not null auto_increment,
  Name    varchar not null,
  Address varchar,
  primary key( ID )
);

create table Users(
  ID      int not null auto_increment,
  Name    varchar not null,
  primary key( ID )
);

create table CompanyUsers(
  UserID    int not null references Users( ID ),
  CompanyID int not null references Companies( ID ),
  JoinDate  date not null,
  LeaveDate date,
  primary key( UserID ), -- makes this 1-n
  unique( UserID, CompanyID )
);

create table Admins(
    UserID   int not null,
    CompanyID int not null,
    ..., -- other admin-related data
    primary key CompanyID,
    foreign key( UserID, CompanyID ) references CompanyUsers( UserID, CompanyID )
);

At first, the Admins table appears to define a 1-n relationship the same way as the CompanyUsers table. But if you follow the FK relationship through, you will see that it can only support 1-1 relationships.

Technically, you don't need the CompanyUsers table as you can define the company and dates in the Users table. This maintains the 1-n relationship but then it is no longer in 3nf. If you are going to have attributes (JoinDate, LeaveDate) that are dependent on more than the PK (that is, they describe the relationship rather than the user), they should go in a new table.


What is limiting the batch requests per sec We have a newly created Azure SQL Server 2014 instance. The specs for the server are as follows

Pretty bare bones, I know. Since this is just a testing server, we have opted to set up the data files on the C:\ drive itself.

We wanted to do some performance testing to see what it would be like compared to an on-premise solution. To do this, we created a database with the following table:

CREATE TABLE [dbo].[temptbl_1](
    [seq] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [field1] [varchar](50) NOT NULL,
    [field2] [int] NOT NULL,
    [field3] [datetime] NOT NULL,
 CONSTRAINT [PK_temptbl_1] PRIMARY KEY CLUSTERED
(
    [seq] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Then, we ran update stats on the entire db. We then ran the following insert statement to see what kind of results we would get:

insert into dbo.temptbl_1 (field1, field2, field3)
select 'here is a great big string',123456, getdate()
go 10000

The first time we ran it from my local machine's SSMS (2016 preview), and were shocked to see that it took ~9 minutes!

We then ran the query again from the server itself in SSMS, and it took ~2 minutes. This seemed much more plausible. But why is it taking so long from the client?

Watching the activity monitor, I can see while the query is running from my local machine, the batch requests per/sec never goes above 20. Doing the math you can see why this would never finish faster than 8-9 minutes. However when running the query from the server, we can see that the batch requests per/sec go all the way up to 100.

We have tried to read as much as we can, and have looked through settings, and cannot figure out why it seems to be limiting the query from my local machine, but the query from the server it doesn't seem too.

Why does the server seem to be limiting the query from my workstation, but not when run from the server itself?

This is just a pure guess (I cannot find anything to support this), but I believe that the reason it is much slower when you perform it on your local box is that it is going through the iteration on your local box.

If you did this as a cursor, I believe this would process everything from SQL and be much faster.

AKA GO 10000 is being iterated 10000 times from your local so it has to worry about the network for each statement as well.


Inconsistent Execution Plan for Stored Procedure We have a business-critical stored procedure that normally runs daily at 2am from a scheduled job (in an SSIS package) on the production DB server. The same procedure/package is called from a second job, 15 minutes later, from a SQL Agent job running on a different server (as an emergency failover in case anything goes awry with the first job).

The procedure is defined WITH RECOMPILE.

The procedure normally executes in about 45 seconds. Last Wednesday, and again this morning (also Wednesday, coincidence?!?), the 2am procedure took 90 minutes to execute. While it was executing, the 2:15am job ran and that execution took the usual 45 seconds.

I have the execution plans from both situations. There are some table variable processes that should include estimated row counts in the neighborhood of 200K rows. The faulty plan reports these table variables with an estimated 130 billion rows. [Side note: I have already rewritten the code to use temp tables instead of table variables, based on this discussion and will be moving it to production in the near future]

Our monitoring software (Solar Winds DPA) reports excessive CXPACKET waits for the 2am execution. This seems to indicate issues with parallelism and is likely related to the table variables being used in the procedure.

There is still user activity on the server during this time, and some scheduled jobs, but nothing that I see that would affect this procedure or its execution plan. An index maintenance job is run at 2:30am.

I understand the poorly-performing execution plan is related to the temp tables, but why would this same procedure executed 15 minutes later have such a drastically different execution plan (and why does the 2am execution run fine the rest of the week?)

Here are links to the .sqlplan files: The Bad Plan and the Good Plan.

For the 'good' plan, all the table variable cardinality estimates are 1 row. This is the most common outcome when using table variables, unless trace flag 2453 is enabled, or a statement-level recompilation occurs (for example because OPTION (RECOMPILE) is used, or one of the regular tables in the query has passed its recompilation threshold.
For the 'bad' plan, table variable cardinalities are accurate, implying one of the conditions mentioned above was in play. This may seem counter-intuitive, since better information usually leads to better plans, but table variables do not support statistics, so the extra information is rather limited. The optimizer knows there are 'x' rows, but has no idea about the distribution of values within those rows. A different kind of incomplete information, perhaps, but still.
Anyway, it just so happens that the plan built when the table variables are assumed to contain one row happens to produce good performance. There is more than a little luck involved in this. Unless you enjoy debugging rare plan regressions, I would avoid relying on luck too much.
Specifics

The faulty plan reports these table variables with an estimated 130 billion rows.

The part of the plan you are referring to is:

As you can see, it is the Table Spool that is estimated to produce ~130 billion rows; the table variable emits only 198,411.
The sort and spool combination is designed to optimize repeated scans, by caching the result from one iteration of the nested loop join and replaying the saved result on the next iteration if the correlated parameter(s) have not changed. The sort ensures any potential duplicates arrive together, since the spool only caches the most recent result. The estimate from the spool is the total number of rows (198,411 from the table variable * 653,969 iterations).
The useful predicate relating the rows from the sort with the table variable is stuck on the nested loops left outer join iterator:

Looking at this in conjunction with the output columns from the table variable, we can conclude that an index on the table variable on PatientID, FirstTestDate would almost certainly eliminate this problem.
An analysis of sub_PSTRules could remove the index and table spools seen there, though these are not having much of an effect on performance at this stage:

Nevertheless, it is wasteful to have SQL Server build a temporary nonclustered index each time, then throw it away at the end. The missing (filtered) index is likely:
CREATE INDEX give_me_a_good_name
ON dbo.sub_PSTRules
    (SubscriberSID, CinicSID, OfficeSID)
INCLUDE
    (PSTQuestionGroupSID)
WHERE
    OfficeSID IS NULL;


Enforce uniqueness across multiple rows I'm trying to enforce groups of activities do not have duplicates without using triggers or materialized views if possible.

DDL to create table:

CREATE TABLE groups (group_id NUMBER, activity_id number) ;
INSERT INTO groups (group_id, activity_id) values (1,1) ;
INSERT INTO groups (group_id, activity_id) values (1,2) ;
INSERT INTO groups (group_id, activity_id) values (2,2) ;
INSERT INTO groups (group_id, activity_id) values (2,3) ;

The next query should return no duplicates if all groups are made of unique activities. I'd like to enforce that this query never returns any duplicates:

SELECT LISTAGG(ACTIVITY_ID, '|') WITHIN GROUP(ORDER BY ACTIVITY_ID) FROM GROUPS GROUP BY GROUP_ID ;

My current solution is to insert the result of the LISTAGG query into a table with a unique constraint and use that to control duplicates with my application code handling any constraint violations by searching for the existing row (and thereby getting the group ID to reuse the existing one)

The most frequent issues with these solutions concern concurrency.
For example:

You have group id 'A' with activity 1, and group id 'B' with activity 2.
Ali inserts activity 1 into group 'B' but doesn't commit
Bernie inserts activity 2 into group 'A' but doesn't commit
As far as both Ali and Bernie are concerned, there is no conflict as they cannot see the other individual's change
If your application tries to do a check then commit, you have a race condition where bad data can be recorded between the check and commit
If your application does a commit and then check, you have the possibility of the committed data being left in a bad state if no-one responds to the check.

Depending on how important the check is, and how much concurrency you need to accommodate, the simplest solution is to lock the entire table at the start of the transaction (ie Bernie can't do an insert until Ali does a commit).


Setting up HA and a synced up reporting box all together using SQL Server 2014 Standard Edition Scenario:
We are going to upgrade our third party application. Also we are going to upgrade our DBMS from SQL Server 2008 R2 to SQL Server 2014 Standard edition. We also wanted to have a high availability option for our back end. The HA options available for SQL Server 2014 Standard editions are

Log Shipping,
DB Mirroring(safety full only),
Alwayson Failover Cluster Instances (Node support: 2) and*
Replication.

We also want to have a third box which should have the exact replica of data that is present in above two. This box will be used for reporting purpose.
Question:
Please provide the best possible setup that can be made with all the options available so that we can have best HA option and also a reporting box with synced-up data.
Note: VMs is not a problem. Currently we are allocated 3 VMs, we can go for 4 as well if needed.
It will great if somebody can provide their insights.

I would investigate the following scenario:  Setup a failover cluster to take care of the HA requirement and set up a third box with database mirroring to offload the reporting part.
Take a look at these articles for more details:
https://msdn.microsoft.com/en-us/library/ms191309(v=sql.120).aspx  and  https://msdn.microsoft.com/en-us/library/ms175511(v=sql.120).aspx


Splitting Comma Separated string into columns by using REGEXP_SUBSTR Here is my example string:

select 'po1,qty1,po2,qty2,..,POn,QTYn' str from dual.

The string length is dynamic. It may have more than one PO and QTY.

Now, I want the output displayed as all the POs in first column and quantities in
second column.

select
  regexp_substr(pq, '[^,]+', 1, 1) as po,
  regexp_substr(pq, '[^,]+', 1, 2) as qty
from
(
  select
    regexp_substr('po1,qty1,po2,qty2','[^,]+,[^,]+', 1, level) as pq
  from dual
    connect by regexp_substr('po1,qty1,po2,qty2','[^,]+,[^,]+', 1, level) is not null
);

PO         QTY
---------- ----------
po1        qty1
po2        qty2


How to find Sum of dynamically generated time attribute in sql server 2012 I have a table with two columns (TASKID and Dateadded). I am trying to find difference between timings and would like to calculate the sum of the diff (time) attribute.

 select taskid,dateAdded,CAST((dateadded -lag(dateadded) over (order by dateadded)) as time )as diff
 from xyz where taskid=21240923

with this statement got the difference between times but I am unable to sum the dynamically generated time column diff.

Could any body please suggest to achieve this goal?

https://www.blogger.com/blogger.g?blogID=1341056964785354138#editor/target=post;postID=5797398234800649517;onPublishedMenu=allposts;onClosedMenu=allposts;postNum=0;src=postname

Use your current query as a CTE (Common Table Expression) and select form that with aggregate functions, like:

WITH TimeDiffs AS (<your query>)
SELECT   taskid
       , SUM(diff)
FROM     TimeDiffs
GROUP BY taskid

(assuming you want the total per task, your question is not very clear on the details).

You could also use the existing query as a derived table:

SELECT taskid
     , SUM(diff)
FROM   (<your query>) AS TimeDiffs
GROUP BY taskid

which will produce the same result, though the CTE arrangement usually ends up being more readable and so easier to maintain going forward.


Why does msdb.dbo.backupset have entries for each database every 15 minutes? I have 134 separate databases on a SQL Server.

Every 15 minutes an entry appears in the msdb.dbo.backupset table for each database on the server.

Why does this happen every 15 minutes?
What is causing this to occur?
How do I stop these entries from happening?
Does anyone else see this type of activity on your MSDB database?

Backups for SQL Server can be scheduled in different way and we do not know how it is set up in your case.That is why we cannot tell you where to look. Most common scenarios are:

SQL Server Agent Job
Maintenance plan
3rd party tool

If you run the script mentioned by @Kin in the comment section, it will show that your databases are most likely taking transaction log backup every 15 minutes.

If that is the case you also might want to purge your backup history periodically. You have to be careful on the first run as it might take very long depending on the amount of rows to be deleted and transaction log for MSDB can grow significantly. See the comments by Erik and Randi.


mssql PERCENTILE_CONT group by year I am trying to prepare a dataset for box chart which requires MAX, MIN, MEDIAN and quartiles grouped by years. This is my attempt, however I could not group it by years:
SELECT
    years,
    MAX(NET_MERIT) AS high_end,
    max(q3) as q3,
    max(MEDIAN) as median,
    max(q1) as q1,
    min(NET_MERIT) as low_end

    from(

    select Year(FA_BDATE) as years ,NET_MERIT,
    PERCENTILE_CONT(0.25) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as q1,
    PERCENTILE_CONT(0.50) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as MEDIAN,
    PERCENTILE_CONT(0.75) within group(order by NET_MERIT) over (partition by Year(FA_BDATE)) as q3
    from HerdAnalytics_tbl
    group by Year(FA_BDATE)
    order by Year(FA_BDATE)

    ) as sub
    order by years

The table is like this: db<>fiddle

FA_BDATE
NET_MERIT

01-01-2009
175.23

01-01-2008
185.23

01-01-2009
195.23

01-01-2010
165.23

And I have a small question: is PERCENTILE_CONT(0.50) is the median itself?

Yes, using PERCENTILE_CONT(0.50) is a way of finding the median. You can check the Basic syntax example section of the PERCENTILE_CONT doc to see an example.
About the GROUP BY, the PARTITION BY clause is already creating groups of years, so you don't need to use a group by on the subquery. Also, you shouldn't use ORDER BY on that subquery, or you'll get the error:

The ORDER BY clause is invalid in views, inline functions, derived
tables, subqueries, and common table expressions, unless TOP, OFFSET
or FOR XML is also specified.

Try your query like this:
SELECT
    years,
    MAX(NET_MERIT) AS high_end,
    MAX(q3) AS q3,
    MAX(MEDIAN) AS median,
    MAX(q1) AS q1,
    MIN(NET_MERIT) AS low_end

FROM(

    SELECT YEAR(FA_BDATE) AS years ,NET_MERIT,
    PERCENTILE_CONT(0.25) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS q1,
    PERCENTILE_CONT(0.50) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS MEDIAN,
    PERCENTILE_CONT(0.75) WITHIN GROUP(ORDER BY NET_MERIT) OVER (PARTITION BY YEAR(FA_BDATE)) AS q3
    FROM HerdAnalytics_tbl

) AS sub
GROUP BY years
ORDER BY years;