What are the consequences, positive or negative, to having a surrogate primary key for a table which already has a guaranteed unique column? I recently made a table that looks like the one below

CREATE TABLE example(
  id SERIAL PRIMARY KEY,
  subindusty_id INT UNIQUE NOT NULL,
  name TEXT NOT NULL
  -- other fields...
)

I realized that in this table I could just remove the column called id and make subindustry_id the primary key.

Thinking back, the only reason I can think of for why I didn't make subindustry_id the primary key is for the sake of consistency, considering that I have a lot of other tables with a column called id.

Except for things like consistency*, are there any tangible benefits/downsides to having this additional id column as the primary key when there's already another column that'd be a good primary key?

*Consistency as in "that's the way the rest of the tables look". The kind where you're enforcing a certain type of style. I guess the point of this question is to question the practice of giving every single table an id column regardless of the fact that they might already have a more suitable candidate for a primary key.

I agree a lot with comments from @MDCCL:

  You should analyze each particular case —that is, every particular table within its whole context, that is, the entire database structure— to define if it requires the appending of a system-generated surrogate column, as such an artifact is an additional aspect that requires its particular administration. Consistency with the way the rest of the tables look is irrelevant, from the logical and physical points of view

A few pointers from my side (the ones that come to my mind at the moment, and that probably are not all there is to it)

Negative consequences:

You need more space for everything: each row is wider, and you have an extra index to maintain. Every write will take (a little bit) more time, and so will every read, because you use more space to read and write the same real information. Let's say you have a log of the readings of several thermometers, one per second. You have one table thermometers (thermometer_id PK, ...etc...) and one readings (thermometer_id, reading_timestamp, value_read). Your readings table has a natural key: (thermometer_id, reading_timestamp), because you cannot have two different readings at the same time. I would normally make this my PK, and forget about surrogates.
Using surrogate keys tend to obscure the real structure and meaning of your data. For instance, imagine you have a tables persons,  books and books_liked_by_persons. This last table is a m x n table, and most of the times contains only (person_id, book_id) pairs. Adding a third id is in such cases most probably meaningless and noisy.

Positive consequences:

In some very specific cases, you may want to have different tables with share one part of their structure (let's say, for instance, you want some of them to have always a country_code, and xxx_id) so that some application is easier to develop because the same pattern can be used to process different tables. [This tends to be rather uncommon, AFAIK, but is also possible.]
Using a surrogate key, even if there are some other alternative keys might help you save space (and probably, time). Imagine your natural key is a PGP key (note 1). It takes a char(800) (approx.) to store it. Imagine you have several tables that reference this one. It will save (a lot of) space all around to have a surrogate integer key (8 or 16 bytes) on the original table, because the referencing tables will store 8 (or 16) bytes instead of 800. The accounting needs to be done for each specific case.
If you ever have to edit your data "by hand" (and, some day, you probably will), you don't want to look for keys which are really long. You prefer surrogates in that case.
Some databases (not PostgreSQL, at least as of now) will cluster the data of your tables using your primary key as a clustering index (or can do it by default unless you explicitly state the contrary). In such cases, it is normally advisable to have an always incrementing primary key; to avoid fragmentation.

So: every case needs analyzing, weighting and deciding. Sometimes consistency may be confused by flying on autopilot.

Side note: There are several different naming conventions. I prefer the one where id is never used, and a persons table has a person_id column if needed.

Note 1: A PGP key looks more or less like:

mQENBFiBGbcBCADBAe1p70ZPGwM6gmAAFTd13gv68rOUQEqlhddjkJZoiKs8SJuX
m4Hpu29+4msQ91071R2yGZ+iydO2laRqWD1jFmF1qOTWiwYkrxYc288/XLbGi7NY
gz9CPKlaH8N4VJBPC6eoRJgpUawBygERf+YFxBEXipWrZbusFrnHCilGGuOwIDAc
UmYf7XNFZrg4+i+QEkBqWnBtd4Q7tkdTTywFDMZ3H+fwrDp+8M3zUwi10PCxJbQd
JkKzXDFemaOHiDxWKNRg7CFyCBRq5K6mfXZuPeAtVOxFMFRnvyJ75HWv4Ss5n40b
tpL/kmSQ9cHgl0nWHS+LMF55zZsAKToNaW6FABEBAAG0FXNvbWV0aGluZ0Bub3do
ZXJlLmNvbYkBHAQQAQIABgUCWIEZtwAKCRDigzJ94dxj1hujCACFt22wZD5yn5/J
3cGLonszqgDRMNzmkdlJjiZi+2fC51z2mkQcy3rlzrQX9K1Mu3TLxPfwyMMiZkut
trleZI4KyAD1hQRm4hQ4f+xx++xOqnMh5c7KsE6AwnGxeDgLy8hD3IJCmrDFwZ93
4XgZ+iXEzFxMcwmLeJ7xg7re4NTlovpIj1QetXqfEHydHQ1AJCqBjGSs805DBHGO
nKAwBXuPI67Kcry+HIhn4ZaIXmXLGvXStJAz2bX8o/nQaVoC/YOyUTMZadR3jtdf
ka5OeyvudCiKTNL3I1oDbhFFfGEhF2w66wp8acJ4z5bYkaQ5RFpEMI1ISfSn7Ypg
FZln1sTo
=RYbd


how to get last date of today in datetime datatype? select dateadd(s, -1, dateadd(month, datediff(month, 0, getdate())+1,0))

when i execute this query, it gives '2012-01-30 23:59:59.000'. I donno how to get it as '2012-01-30 23:59:59.997'. If anybody Know, please say.

thanking you!!!

Why on earth do you need the last possible value of today? Stop using BETWEEN; it is much easier (and much more reliable, and consistent across various date/time data types) to use an open ended range:

DECLARE @today SMALLDATETIME;

SET @today = DATEADD(DAY, DATEDIFF(DAY, '20000101', GETDATE()), '20000101');

SELECT ... FROM ...
WHERE date_column >= @today
AND date_column < DATEADD(DAY, 1, @today);

The problem with what you're doing is that the data type can affect what happens. If the underlying data type is SMALLDATETIME, it rounds up; if the underlying data type is DATETIME or DATETIME2, subtracting a second is not going to capture all of the data from that day. This is actually one of my favorite slides in my T-SQL : Bad Habits and Best Practices talk. For a lot more details, please read these blog posts:

What do BETWEEN and the devil have in common?
Bad Habits to Kick : mis-handling date / range queries


Speedup DROP huge MySQL replicated table I've a huge InnoDB table customer_users_old (150G) that it's not in use anymore in the database, it was renamed from customer_users.

My setup has a Master-Slave replication of MySQL 5.5 with innodb_file_per_table activated, so the table has it's own files.
As the DROP command takes a lot of time, I'm afraid it can have some inpact on the prodcution database.

So I'm thinking on disaling unique and foreign key checks for the session as it was suggested on DROP TABLE on a huge InnoDB table:

SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;

And disabling the binary log to drop the table first on the master and then on the slave as it was suggested on Deleting data from a large table with replication:

SET sql_log_bin = 0;

Will this speedup the process and minimize the impact on the servers load?

In the end we realized that the process was so slow because we did try to do the DROP while the mysqldump backup was running and the table was blocked by the backup process.

After the backup process had finished the DROP was quite fast and took only some minutes and did not have a big inpact on the server performance.


MySQL not updating a row (zero row(s) affected) I cannot figure out why MySQL 5.6.25 will not update an existing row:

SELECT
  lcl_booking.file_number_id,
  lcl_booking.entered_datetime,
  lcl_booking.modified_datetime
FROM lcl_booking
WHERE lcl_booking.file_number_id = 197467;

yields:

file_number_id entered_datetime     modified_datetime
197467         2015-06-26 15:26:44  2015-07-08 10:39:15

Now, I do this:

UPDATE lcl_booking SET
  modified_datetime = "2015-07-21 10:54:04"
WHERE file_number_id = 197467;

returns:

1 queries executed, 1 success, 0 errors, 0 warnings

Query: UPDATE lcl_booking SET modified_datetime = "2015-07-21 10:54:04" WHERE file_number_id = 197467

0 row(s) affected

Execution Time : 0.019 sec
Transfer Time  : 0 sec
Total Time     : 0.019 sec

and....

SELECT
  lcl_booking.file_number_id,
  lcl_booking.entered_datetime,
  lcl_booking.modified_datetime
FROM lcl_booking
WHERE lcl_booking.file_number_id = 197467;

yields:

file_number_id entered_datetime     modified_datetime
197467         2015-06-26 15:26:44  2015-07-08 10:39:15

Here is the table (minus a bunch of columns)

SHOW CREATE TABLE lcl_booking;

CREATE TABLE `lcl_booking` (
  `file_number_id` bigint(20) unsigned NOT NULL,
  `entered_datetime` datetime NOT NULL,
  `modified_datetime` datetime NOT NULL,
PRIMARY KEY (`file_number_id`),
KEY `lcl_booking_fk1` (`file_number_id`),
CONSTRAINT `lcl_booking_fk1` FOREIGN KEY (`file_number_id`) REFERENCES `lcl_file_number` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='LCL Booking Header';

Anyone have any ideas?

It was an undocumented trigger that was causing the problem.


Check server activity with code I would like to see the activity on of the SQL server. So I can run a query when the server load is low. I know there is an activity monitor in SQL server but my company doesn't want me to give the rights to use this tool. Apparently you can kill other people queries and this is too dangerous.

Is there a way to check the status of the server with code?
(Or view the activity without needing those rights so I can manually run them?)

The below query uses the  sys.dm_os_performance_counters DMV to capture the performance counters of SQL Server. You can change the WAITFOR DELAY options and the Numeric suffix after the second GO command to modify the time interval and the number of times you want the code to be executed.

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('Tempdb..#PerformanceMonitor') IS NOT NULL
DROP TABLE #PerformanceMonitor;

CREATE TABLE #PerformanceMonitor
(
[object_name]   NVARCHAR(255) NOT NULL,
[counter_name]  NVARCHAR(255) NOT NULL,
[instance_name] NVARCHAR(255) NULL,
[cntr_value]    BIGINT NOT NULL,
[cntr_type]     INT NOT NULL,
[LoadedOn]      DATETIME NOT NULL DEFAULT GETDATE(),
)
GO
INSERT INTO #PerformanceMonitor
([object_name],[counter_name],[instance_name],[cntr_value],[cntr_type])
(SELECT object_name,counter_name,instance_name,cntr_value,cntr_type
 FROM sys.dm_os_performance_counters)

WAITFOR DELAY '00:00:10' --Change the interval here
GO 10 --Change the number of times the data will be captured here
SELECT * FROM #PerformanceMonitor

Once you have enough data and you have narrowed down the counters which are relevant, you can decide upon the best time to run your queries. And also as mentioned in the answer by @Yaroslav ,it is very important to keep in mind what type of query you want to run and decide accordingly.
And the most important thing, be careful with production data!!!


Oversize non-clustered index I have a table with 220 rows. It has about 20 columns. One of the non-clustered indexes has a datetime as the key value and includes a unique identifier and a varchar(128). The clustered PK is a bigint.
The datetime for every row is updated very frequently (40 times a second).
If I rebuild the index it is about 200KB but after a couple of hours, it will have grown to about 50MB. (And it appears to keep growing). Given how often it is accessed, scanning 50MB compared with 200KB is hurting a lot. SQL 2016 SP2 13.0.5201.2
Can anyone explain, or point me to something that explains, how an index with only 3 columns, on a 200 row table can get to be 50MB? And even better anything I can do about this? (The 40 updates a second is not something happy with but I haven't been able to convince anyone that this is excessive yet).
Unfortunately, in-memory is not an option. Nor is changing the code.
Edit: Added output from sys.dm_db_index_physical_stats function as suggested by Andrew Sayer

Possibly related info - Server is not in an availability group. Allow Snapshot Isolation is false but is_read_committed_snapshot_on is true.

This should not be possible, from an architectural standpoint:
The non-clustered index has 220 rows. Even if you have only one row per page in your index, you would have only 1.7 MB (plus a little bit in the non-leaf levels, but that is marginal). So, something is "not normal" here - this should be impossible.
Are you sure you aren't looking at the size of the data, and that the table is a heap table? SQL Server has a well known issue with not free up space for heaps. That would explain it. The solution is to not have a heap. I see, however that you don't have a heap, since you mention that the clustered PK.
A very far-fetched theory is that SQL Server's meta-data doesn't reflect reality regarding the space used by the index. We had that prior to SQL Server 7.0 (or was it 2005 that this was fixed?). We refreshed the meta-data using DBCC UPDATEUSAGE. You can of course use this, just in case. It doesn't explain bad performance, but it is a quick thing to verify. I.e., the theory is that you are exposed to some code-path in SQL Server where this issue (non-correct space usage meta-data) isn't handled.
If this was me, I would first rule out the meta-data aspect. Look at space usage using several methods, just to verify that they agree on the size. And last resort would probably be to see what is really there using commands such as DBCC PAGE, sys.dm_db_database_page_allocations() and such...
(Come to think of it, row versioning could potentially explain some extra space usage, but that would be in tempdb...)


If at least a negative number, sum; otherwise just show I have a need to sum values together only if there is a negative value present in a partition.  If there are no negative values in a partition, it should just output the row.

Here's what I have right now.  Initial data is provided as a CTE.

DML

;with ledger as (
    select accountId, type, amount
    from (
        values
         (1, 'R', -10)
        ,(1, 'V', 10)
        ,(1, 'R', 30)
        ,(2, 'R', 20)
        ,(2, 'R', -5)
        ,(2, 'V', 5)
        ,(3, 'R', 20)
        ,(3, 'R', 30)
    )   x (accountId, type, amount)
)
,b as ( --identifies accountid, type with negatives
    select
        accountid
        ,type
    from ledger
    group by accountid, type
    having min(amount) < 0
)
,onlyPositives as (
    select
         l.accountid
        ,l.type
        ,l.amount
    from ledger l
    left join b on b.accountid = l.accountid
                and b.type = l.type
    where b.accountid is null
)
,aggregatedNegatives as (
    select
         l.accountid
        ,l.type
        ,amount = sum(l.amount)
    from ledger l
    inner join b on b.accountid = l.accountid
                and b.type = l.type
    group by l.accountid, l.type
)
select  accountid, type, amount
from    onlyPositives

union all

select  accountid, type, amount
from    aggregatedNegatives

I'm expecting an output like this, and the query above outputs correctly.

1, R, 20  (summed because -10+30=20)
1, V, 10  (left alone)
2, R, 15  (summed because 20-5=15)
2, V, 5   (left alone)
3, R, 20  (left alone)
3, R, 30  (left alone)

The query so far looks beastly and feels unnecessarily complex.  Is there a simpler query I could write that I have overlooked?

This is a slight variation on the issue: If positive, sum all items. If negative, return each one

rextester -> https://rextester.com/EZRT33825

You can accomplish what you're after using window functions, but I can't make any promises about performance if you're running this query over a lot of rows. The idea is to calculate the sum, minimum, and an unordered row number for every partition. Keep all rows with a minimum > 0 but only keep the first row of a partition if the minimum < 0.

-- put data into temp table for illustration purposes
select accountId, type, amount into #t220618
from (
    values
     (1, 'R', -10)
    ,(1, 'R', 30)
    ,(1, 'V', 10)
    ,(2, 'R', 20)
    ,(2, 'R', -5)
    ,(2, 'V', 5)
    ,(3, 'R', 20)
    ,(3, 'R', 30)
)   x (accountId, type, amount);

SELECT
  accountId
, type
, CASE WHEN part_min < 0 THEN part_sum else amount END amount
FROM (
    SELECT
      accountId
    , type
    , amount
    , SUM(amount) OVER (PARTITION BY accountId, type) part_sum
    , MIN(amount) OVER (PARTITION BY accountId, type) part_min
    , ROW_NUMBER() OVER (PARTITION BY accountId, type ORDER BY (SELECT NULL)) part_rn
    FROM #t220618
) q
WHERE q.part_min > 0 OR (part_min < 0 AND part_rn = 1);


How to select not exists data in SSIS package i dived into SSIS because of curiosity, what i want to do is inserting "Not Exists" Data to postgresql. i want to select "Not Exists" data from  Postgresql which is joined to the data selected from Mssql, and insert into Postgresql. Below is the Data Flow Task.

Sorry for my English, i hope you got i what i want to do.

i achieved by following this post;
How to get unmatched data between two sources in SSIS Data Flow?

and here is my screen shot;

by doing this, i am joining 2 different sources and splitting unmatched data than i insert into destination.

i appreciate your efforts. Thank you so much.


Do Inactive MySQL Databases Consume Memory? I have inactive databases that are on the server just for archive purposes.

I know I can do mysqldump for all of them, but I am not 100% sure they are inactive.

When I started my system I didn't have a way to determine active/inactive. If there are no queries on them, will they still consume memory ?

PROLOGUE
As I mentioned earlier in Adding new tables -- memory usage increases, adding new tables increases memory usage. That being the case, tables that are not in active use would still consume memory in the INFORMATION_SCHEMA.
Determining what is active is an arbitrary process. If you need to see when was the last time a table had written changes, you have to find out based on the Storage Engine of each table.
MyISAM
Learning the last time a MyISAM table had written changes is straightforward.
Suppose you need to see all MyISAM tables that have not been written to in over a month. Simply run this query again INFORMATION_SCHEMA.tables:
SELECT table_schema,table_name,update_time FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql','performance_schema')
AND engine='MyISAM' and update_time > (NOW() - INTERVAL 1 MONTH);

Note: update_time for a MyISAM table is the timestamp of the .MYD file. If you run ANALYZE TABLE mydb.mytable;, the index statistics are written to the .MYI file. The .MYI's file timestamp is not reflected in the INFORMATION_SCHEMA.
InnoDB
Getting InnoDB timestamps is quite a challenge because InnoDB does not store update_time in the INFORMATION_SCHEMA. If innodb_file_per_table is disabled, everything InnoDB and its grandmother is encased in the system tablespace file ibdata1. Gettting the timestamp of ibdata1 gives you the last time any write was received. That could be anything

INSERT, UPDATE, DELETE (altering data and index pages)
ANALYZE TABLE (altering index pages)
SELECT (data snapshots via MVCC)

Therefore, with innodb_file_per_table disabled, there is not way to know.
On the other hand, if innodb_file_per_table is enabled, the question remains: HOW DO YOU FIND OUT THE LAST TIME AN INNODB TABLE WAS WRITTEN ?
You must check the file timestamp of the .ibd.
Rather than reinvent the wheel, please read my posts on how to get that info

Dec 21, 2011 : Fastest way to check if InnoDB table has changed
Jun 03, 2013 : Is there a way to find the least recently used tables in a schema?

EPILOGUE
The main point to keep in mind is to find out timestamp of the table and subtract it from NOW() (or UNIX_TIMESTAMP(NOW()) from the OS point-of-view). You have to arbitrarily determine how old is too old for an inactive table.


Multiple service_name and hosts with same alias I am currently working on a duplicated infrastructure like this :

We have two data server, with two database on each.

Server 1

Oracle instance 1
Oracle instance mirror 1

Server 2

Oracle instance 2
Oracle instance mirror 2

I cannot change the instance name on the database.

DWH_connection =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server1)(PORT = 1521)
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Oracleinstance1)
      (SERVER = DEDICATED)
    )
  )

Here is what I know we can do, but I am stuck and can't find any docs. Maybe I am bad at searching, but can't find a way.

I would like to get the Server 1 - Oracle instance 1 with the same alias as Server 2 - Oracle instance 2. So in case Server 1 is not up, our application will go the Server 2. So this is what I would like to have:

DWH_connection =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server1)(PORT = 1521)
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521)
    )
    (CONNECT_DATA =
      (SERVICE_NAME = Oracleinstance1)
      (SERVICE_NAME = Oracleinstance2)
      (SERVER = DEDICATED)
    )
  )

You need different DESCRIPTION sections to have different SERVICE_NAME in the CONNECT_DATA section.

DWH_connection =
  (DESCRIPTION_LIST =
    (LOAD_BALANCE=OFF)
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server1)(PORT = 1521))
      (CONNECT_DATA =
        (SERVICE_NAME = Oracleinstance1)
        (SERVER = DEDICATED)
      )
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Server2)(PORT = 1521))
      (CONNECT_DATA =
        (SERVICE_NAME = Oracleinstance2)
        (SERVER = DEDICATED)
      )
    )
  )

The default value of LOAD_BALANCE is ON, but in your case, connections should go to the first server, and go to the second one only if that fails, so LOAD_BALANCE=OFF is needed, otherwise connections would be distributed between the 2 servers.


how to select the not null value of a column when it has both null and not null value in teradata? consider a table named "TABLE-A" with below data.
ID   hcc    D_CD
1    null   1
1    5      1
2    null   2
2    10     2
2    20     3
3    null   3

I need to select ID,hcc fields from TABLE-A in the combination of ID,D_CD.
when the combination has single hcc value (even it is null) it should be fetched.
But, when it has both null and not null values, it should fetch the not null value only.
So the output must be as below.
OUTPUT:
ID   hcc
1     5
2     10
2     20
3     null

can anyone please help me on this? thanks in advance.

What I did to solve this was:
CREATE TABLE x
(
  id SMALLINT NOT NULL,
  hcc SMALLINT NULL,
  d_cd SMALLINT NOT NULL
);

and populate it:
INSERT INTO x VALUES
(1,    null,   1),
(1,    5   ,  1),
(2,    null,  2),
(2,    10,     2),
(2,    20,     3),
(3,    null,   3);

And then run this query:
WITH y AS
(
  SELECT id, hcc
  FROM x
  WHERE hcc IS NOT NULL
),
z AS
(
  SELECT id, hcc
  FROM x
  WHERE hcc IS NULL AND id NOT IN (SELECT id FROM y)
)
SELECT id, hcc FROM y
UNION
SELECT id, hcc FROM z
ORDER BY id;

Result:
ID   hcc
1     5
2     10
2     20
3     null

As desired!
In future, could you please provide a fiddle with your table DDL and DML! This should work with Teradata - I double-checked and it does support Common Table Expressions!


Is there any unique identifier for PostgreSQL? How can the master identify its slave unqiuely? Is there any way to have more than one PostgreSQL slave servers in the same system.

If yes, how come a master detect each server as different slaves from the below query

select * from pg_stat_replication;

will give one row for each slave which are connected to it, with PID, IP-address/client_address, client_port, etc..

If two servers are running in the same machine gives the same IP-address so it is not a unique identifier.

If slave got restarted then both client_port and PID will change so these are also cant be a unique identifier.

So, what is the unique identifier used by Postgres master to identify each slave server uniquely? Or how it is finding its slave servers differently?

The master does not "find" the replicas.  The replicas find the master.

The master identifies the replicas by either their slot_name, or their application_name, or not at all, depending on the context.

What are you trying to do?


Macroeconomic Time-Series Database I have decided to improve my well-being and in case being successful write a note about this and share it with my peers for free and try to help them improve there well-being as well. But on this road, I need foundational advice from the database practitioners.
Background Description:
In my research, I need to collect a lot of different types of time-series (GDP, Real GDP, Nominal GDP, Consumption, Investment, Percapita GDP, Number of Hours Work, Unit Labor Cost and many more). Right now, I am following and collecting about 155 time-series. Until now, I have been updating them manually as soon as an update was published on statistical web pages. I have been doing this using Excel spreadsheet (like, downloading new excel and then copying and pasting the necessary data point into my excel). However, this is a very daunting task, and it is prone to a lot of errors. Checking and finding the error is another nightmare. So, in total, maintaining this Excel database (updating checking and documenting) takes about 10-12 hours per week approximately.
My Task:

I want to create a database where I will have all these and many more
variables (I want each variable to have a name, description, source
description (ideally with URL included), date of last update etc.) stored.
I also want to be able to update the database automatically from
downloaded Excel or CSV file (These files come in different formats
from various webpages. Some are structure vertically some horizontally).
I also want to have some error checkers for easier debugging the base.
The series are of different frequencies (some are daily, some weekly, some monthly and some quarterly and yet some annual). I want to be able to easily convert from daily to monthly or from monthly to quarterly or vice versa using my own predetermined formula. (being it average or median or whatever the current task requires)
I also want to then later to be able to easily query any variable or several of the variables and create a dashboard in various programs like MATLAB or Python or Julia to visualize dynamics and use them for regression or model estimations in these programs.

Question
For all the tasks described above which database management program would you recommend (ideally, it would be better to be it free and open source)
P.S. I tried writing MATLAB script for automatic updates, but it's very inconvenient.
P.P.S This is a cross-post from Cross Validated in hope to receive the answer somewhere.
Thanks,
Giorgi.
As an example of how my data set looks like please see the following screenshot:

Your root question is subjective and it's a little hard to fully envision what your data looks like without some examples, but it sounds like a Relational Database Management System (RDBMS) could work for you based on the type of querying you'll be doing.
If you solely want something free than PostgreSQL. If you want something with the most features built into it out of the box and are ok with paying then Microsoft SQL Server. Both can house your data, have Views (and other types of objects) which can save your queries (predetermined formulas) for conversion, and support importing data from CSVs and other files if needed as well (though I recommend you do as much of your data gathering and importing into the database via a non-database language like Python).
Your additional requirements for creating dashboards and other ad-hoc querying will be supported by either of these databases as well.


Preparing a former master to run as slave again I have a streaming replication setup using postgresql 9.3. I have two servers, the master (server_1) and the slave (server_2).

The other day I did a failover to my slave (server_2). Now server_2 has been running as master for a couple of days, and I have now setup my primary master (server_1) again, and it now runs as master. I would like to avoid reinstalling server_2 before turning it into a slave of server_1 again. How can I do that? What dir's should I delete etc, in order to have a "clean" server that I can use as slave again?

The only officially supported method is to make a new base backup, or:

pg_start_backup() on the new master
rsync from new master to old master
pg_stop_backup()
ensure WAL got copied over and applied
create recovery.conf
start resynced server

The 3rd party tool pg_rewind is intended for that purpose. I haven't worked with it as yet and can't vouch for it in production.


pg_restore "CREATE CAST" not executed if using public or pg_catalog schemas While moving database to another instance I'm using pg_dump and pg_restore with intermediate file.

Dump file was created like this:

pg_dump --host $SRC_DB_HOST --port 5432 "dbname=$SRC_DB user=$SRC_DB_USER sslrootcert=rds-combined-ca-bundle.pem sslmode=verify-full" --no-owner --verbose -Fc --file db.bak

I definitely see that there are casts in the dump

$strings db.bak | grep "CREATE \(TYPE\|CAST\)"
CREATE TYPE public.score_group_name AS ENUM (
CREATE TYPE public.score_group_type AS ENUM (
CREATE CAST (character varying AS public.score_group_name) WITH INOUT AS IMPLICIT;
CREATE CAST (character varying AS public.score_group_type) WITH INOUT AS IMPLICIT;

But when I restore database running pg_restore with params:

pg_restore --verbose -Fc --no-acl --no-owner -n public --single-transaction db.bak > migration.log

there are no queries produced for creating it:

$ grep -e "\(CREATE\|DROP\) \(TYPE\|CAST\)" migration.log
CREATE TYPE public.score_group_name AS ENUM (
CREATE TYPE public.score_group_type AS ENUM (

Source PostgreSQL version:

PostgreSQL 9.6.11 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3 20140911 (Red Hat 4.8.3-9), 64-bit

Target PostgreSQL version:

PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit

PostgreSQL Utils versions:

$ pg_dump --version
pg_dump (PostgreSQL) 9.6.14
$ pg_restore --version
pg_restore (PostgreSQL) 9.6.14

UPDATE
It seems that the root of the problem is -n public parameter, without schema filtering -- CREATE CAST statement is produced, but which schema should I add to the restore? Because -n public and -n pg_catalog filter out such statements.

The reason is that type casts do not live in a schema.

If you specify -n public, you will only get objects from that schema, and that does not include the type cast.


Add function parameter to SQL query WHERE clause I have a web application in java and it uses a query. I don't want to write the query into Java, so I made a function:

CREATE OR REPLACE FUNCTION testFunc(inputs text) RETURNS TABLE(...) AS
$$
    SELECT .... FROM ...
    JOIN ...
    where true
    ;
$$
LANGUAGE SQL;

I want the Function parameter INPUTS to also be in the WHERE clause so if inputs is

AND speed = 0 AND ....

Where clause looks like

where true AND speed = 0 AND ...

How can i achieve this?

EDIT

Also it is acceptable to have many parameters (a int, b string, c string ..) but then i need to have

WHERE speed = * AND stop = * AND ...

which is not acceptable. How can i achieve this ?

OR can i put a if statement inside it ? Like

Select .. . from ...
JOIN ...
WHERE true
IF (a != null){AND speed = $1}
IF ....
;

If you don't always pass all parameters, create a function with parameter defaults. The basic, simple form would be an SQL function without dynamic SQL:

CREATE OR REPLACE FUNCTION func(_a int  = NULL
                              , _b text = NULL
                              , _c text = NULL)
  RETURNS TABLE(...) AS
$func$
    SELECT ... FROM ...
    WHERE (speed = $1 OR $1 IS NULL)
    AND   (stop  = $2 OR $2 IS NULL)
    ...
$func$  LANGUAGE sql;

Now you can call the function with any number of parameters using named notation:

SELECT * FROM func(_c => 'foo', _a => 123);  -- no _b, which defaults to NULL

Note, the assignment operator in the call is => (or := for Postgres 9.4 or older), not =!
See:

The forgotten assignment operator “=” and the commonplace “:=”
Functions with variable number of input parameters

Aside: "string" is not a data type, text is.

Much more is possible with dynamic SQL with EXECUTE in a plpgsql function.

More comprehensive answer on SO with complete recipes:

Test for is null or = value in function


table or view does not exist when using insert into I'm trying to insert into but getting the "table or view does not exist" error.

It tells me the specific line where the error occurs but I can't figure out what the problem is:

INSERT INTO c_ord_cur (ordid, total)
          VALUES (v_ord_rec.ordid, v_ord_rec.total);

I have already created the Table and it shows it on the left panel of oracle:

I'm using oracle developer sql, any ideas what is causing this problem?

Whole code:

Set serveroutput on

DECLARE
    CURSOR c_ord_cur IS
    SELECT
 *
    FROM
      shoba.ord;

    v_ord_rec   c_ord_cur%ROWTYPE;

BEGIN
    OPEN c_ord_cur;
    LOOP
        FETCH c_ord_cur into v_ord_rec;
        EXIT WHEN c_ord_cur%NOTFOUND;

        IF v_ord_rec.orderdate between '01/01/2009' and '31/12/2009' THEN
                INSERT INTO c_ord_cur (ordid, total)
              VALUES (v_ord_rec.ordid, v_ord_rec.total);
            dbms_output.put_line(v_ord_rec.orderdate||' is in ords86');
    END IF;

    IF v_ord_rec.orderdate between '01/01/2010' and '31/12/2010' THEN
                INSERT INTO c_ord_cur (ordid, total)
          VALUES (v_ord_rec.ordid, v_ord_rec.total);
        dbms_output.put_line(v_ord_rec.orderdate||' is in ords87');
        END IF;

    END LOOP;
    CLOSE c_ord_cur;

    COMMIT;

END;

It is the definition v_ord_rec   c_ord_cur%ROWTYPE; that is wrong. It must be v_ord_rec   shoba.ord%ROWTYPE;. Also the insert statements should insert into real table and not into the cursor.


Question about MySQL Semisynchronous Replication I was researching about MySQL Semisynchronous Replication, my understanding about the workflow is the follow:

Master Receive a successful transaction from a client session.
Master write it to his binaty log
Master wait for rpl_semi_sync_master_timeout milliseconds to receive an acknowledgment from at least one slave
Slave pick this successful transaction, write it to his relay log, flush it to disk
Slave return an acknowledgment to master
Master return successful to his client session.

My question is, at any point, this type of replication ensures that the transaction is executed on slave(SQL_TREAD applies it). Having in mind that it will add a delay on each transaction and in a case of master failure, is not 100% guaranteed that the slave will be consistent with master, what is the benefit(s) of using this type of replication?

Baron Schwartz wrote an excellent article on this, and I think one key sentence answers your question:

  What does semi-synchronous replication guarantee me then? If there are no errors or timeouts, then the guarantee is essentially that only one transaction per client is likely to be lost if the master crashes.

So semi-synchronous replication minimises (but doesn't entirely eliminate) the chance of this exact problem:

Replicant B's replication IO Thread starts running behind its master - Server A - for some reason
Server A dies - unrecoverably
Transactions are lost forever

As noted in Baron's post (and based on your wording I'm sure you're aware of this), it isn't bulletproof - MySQL will fall back to regular replication if it stops getting a response from the replicants - but irrecoverable data loss is less likely to occur.

The upshot is that it prevents the situation above, it's "out of the box", and it doesn't require any third-party or infrastructure-level solutions (e.g. Galera/Xtradb Cluster, or DRBD). Whether the benefits are worth the cost is likely to vary depending on your situation.


Compare tables on 2 db sqlserver I have SQL Server with 2 databases.

I want to compare them and see which tables are on the first database and not on the second database.

Only the names of tables matter, I don't care about the values in those tables.

How can I do it?

Tables in db1 that don't exist in db2:

SELECT name FROM db1.sys.tables
EXCEPT
SELECT name FROM db2.sys.tables
ORDER BY name;

For more elaborate comparisons (like columns, indexes, and data types), don't re-invent the wheel, as there are already tools that solve this problem.* For more info on this in general, see this blog post from ages ago.

Disclaimer: I work for SentryOne.


Multiple Many-to-Many Relations I've never come up against a similar situation and so I'm looking to gather some tips on how to accomplish this. Here's the situation:

I'm creating an application to track and manage sheet music and their accompanying information. Books can have many songs (Many-to-many). Where I get tripped up is when we start tracking the artists. Books can have multiple artists, but songs can also have different artists.

For example, a Garth Brooks compilation book (Garth is the artist for the book) has a song that was a duet with Trisha Yearwood. Trisha Yearwood isn't a co-artist for the entire book, but can be for multiple songs IN that book. I would like to relate Books to Artists as well as Songs to Artists so I can search and find all Books and Songs that Garth Brooks has been an artist in.

-------
|Books|
-------
|id   |
|name |
-------

-------
|Songs|
-------
|id   |
|name |
-------

//Join Table for Books and Songs
--------------
|Book_songs  |
--------------
|id          |
|books_id FK |
|songs_id FK |
--------------

---------
|Artists|
---------
|id     |
|name   |
---------

Would this be as simple as a separate join table for both Books and Songs?:

----------------
|Book_artists  |
----------------
|id            |
|books_id   FK |
|artists_id FK |
----------------

----------------
|Song_artists  |
----------------
|id            |
|songs_id   FK |
|artists_id FK |
----------------

Or is there a better way to design these relations?

Thanks for your help in advance!

I think your solution (two join tables) is valid. However, I suspect that you are tracking two different things, and wanted to be sure that you were clear on this in terms of design.

The artist(s) associated with a book do not necessarily have anything to do with the artists associated with a song, at the database level. Logically, the artist associated with a book will (almost) certainly also be associated with every song in the book; but, that's irrelevant in terms of the relationship between the artist and the song or the artist and the book.

Some things not to do (not that you necessarily were going to do any of them, but for the sake of someone else looking at this in the future):

Do not use the artists associated with the songs to define the artist for the book. I'm assuming that "artist" = "performer", but let's say it also included the writer of the song. A book of Elton John songs might show that every song not only has Elton John, performing, but Bernie Taupin writing; however, that's not necessarily relevant by default. You need the artist to be associated to the book directly.
Do not ignore the association between the book artist and each song. This is self-evident once a song is included in two different books, based on two different artists, but at an early stage, one may think that with the artist defined at the book level, one could just add additional artists at the dong level and let the book-level association cover that artist being associated with all the songs. Again, if the Brooks/Yearwood duet was going into a Brooks book and a Yearwood book, this would become problematic. You need that association at both levels, book and song.


Performing SQL string manipulations on a single column I recently came across a problem at an interview which I wasn't able to answer. The ask was to write some SQL fetch the first three number(s) after crossing two 2 decimals.
For example:
Input table:
IP address
1.2.3.4.5
11.22.33.44.55
5.6.7.8.9
111.222.333.444
.
.
.

Output table:
IP address
1.2.3
11.22.33
5.6.7
111.222.333
.
.

My initial approach was to use the inbuilt substring. For example: select substring(ip,0,5) from table. But this is a broken logic as it does not account for values such as 11.22.33 or 111.222.333
How can this be achieved?


where to find TPC-C queries I've been looking around for specific TPC-C queries but haven't found good sources. Does anyone have a pointer?

As I'm aware of, TPC-H only has 22 queries which could be easily found here http://www.qdpma.com/tpch/TPCH100_Query_plans.html and here https://github.com/hortonworks/hive-testbench/tree/hive14/sample-queries-tpch. I was wondering if it's the same case for TPC-C benchmark. Does it also have a fixed number of distinct queries and where could I find them? I just have a specific task in which I need to measure the performance of each query and I thought both TPC-H and TPC-C queries are good references. Thanks!

TPC-C contains a fixed number of transactions, not queries. They are described in Clause 2 of http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-c_v5.11.0.pdf. Sample implementations including SQL are in Appendix A.


Some basic questions about SQL and TLS 1.0 and 1.2 (I don't believe this is a duplicate of any other TLS-related question, like the authoritative question about SQL Compatibility with TLS or its many duplicates, or questions about specific TLS-related issues you might encounter with SQL CLRs or Classic ASP or SSRS or whatever. This question is a bit more broad.)

All our SQL servers (SQL 2012-2017) are on recent patch levels that support TLS 1.2 (see first link above), and yet when we disabled TLS 1.0 on our SQL servers, nearly all our applications broke (SharePoint farms, web apps, dot.net apps, PowerShell scripts, many vendor apps, heck, even some SQL jobs running on the same box failed).

I thought that clients already tried to negotiate the most secure encryption protocol that both the app and SQL support (TLS 1.2), but that's clearly not the case here.

So... what am I missing? Is there a master switch at the Windows OS level on our application servers that was never flipped to tell applications they should start using TLS 1.2? (We run Windows 2012 R2 and 2016.) Are there different registry entries for enabling "client" TLS 1.2 vs enabling "server" TLS 1.2?

I believe MS components like .Net are up to date as well. I know we will eventually need to look at individual applications, but I can't help but think there is something more obvious going on here.

Also, is there a SQL query that can tell me what connection method was attempted by each application (none vs TLS 1.0 vs TLS 1.2)? This page suggests encrypt_option from sys.dm_exec_connections, but that appears to be a boolean, not one that tells me the type (plus all my rows show FALSE, so I don't think that's right).

Update: Added bounty for more attention. I have determined that at least a couple of our older SQL servers are Windows 2012 (not R2), which (I believe) does not have TLS 1.2 enabled by default, where it should be for Windows 2012 R2 and later?? Is that right?

Also still interested if anyone has a query and/or other method to determine what protocols different clients are using when they attempt their first connection to SQL; or would that be a network-layer tool?

This might be different for each client, so you need to test them separately.

First of all for most clients (since they use SChannel) your Windows machines need to be recent enough to support TLSv1.2 (I.e. Server 2008 R2 and later). For those you also need to make sure TLSv1.2 is actually turned on in the Schannel registry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2\Client
    Dword: DisabledByDefault = 0

Some clients might need client software updates, this includes a certain minimum .Net Framework (before 4.6 all need patches) as well as a specific SQL Server Native Client version.

And also, if you have a Java application the JVM must actually be recent in order to be TLSv1.2 able (and have it enabled by default). Some Java applications might turn the TLSv1.2 protocol off despite it beeing enabled in Java 8, then you need to check the application.

But in regards to your assumption it requires other things to be negotiated, I don’t think so: in all cases TLSv1.2 will be negotiated if it is supported on both sides. If you are sure your SQL Server is capable then it’s the client configuration.

The TLSv1.2 KB article lists quite a few known issues and required patches (including client requirements):

https://support.microsoft.com/en-us/help/3135244/tls-1-2-support-for-microsoft-sql-server

In some cases you might also need to exchange provider=SQLOLEDB with SQLNCLI11.


SQL Server In-Memory Table Conversion I have a table that I want to convert to memory optimized.
I have 160 million rows in my table. According to this topic my table will need:

(24B(header size) + 8B(IndexPointer) + 30B(dataRowSize)) * 160mln = 9460.45 MB of memory to store the table and 4B*160mln = 610.35 MB memory to store the index.
Total memory need is about 10GB.

I gave 16,000MB memory to my SQL Server, and I think this might be enough, but when I try to populate my table with data I am getting "out of memory"

I use the following script to change my disk based table to memory-optimized:

USE [master]
GO

ALTER DATABASE [BGD] ADD FILEGROUP [memory_optimized_filegroup_0] CONTAINS MEMORY_OPTIMIZED_DATA
GO

ALTER DATABASE [BGD] ADD FILE ( NAME = N'memory_optimized_file_122569704', FILENAME = N'E:\Artash\LocalBGD\Data\memory_optimized_file_122569704' ) TO FILEGROUP [memory_optimized_filegroup_0]
GO

USE [BGD]
GO

EXEC dbo.sp_rename @objname = N'[dbo].[household_mem]', @newname = N'household_mem_old', @objtype = N'OBJECT'
GO

USE [BGD]
GO

SET ANSI_NULLS ON
GO

CREATE TABLE [dbo].[household_mem]
(
    [location2id] [SMALLINT] NULL,
    [location3id] [SMALLINT] NULL,
    [location4id] [SMALLINT] NULL,
    [location5id] [SMALLINT] NULL,
    [location6id] [INT] NULL,
    [location7id] [INT] NULL,
    [location8id] [INT] NULL,
    [location9id] [INT] NULL,
    [povertyscore] [SMALLINT] NULL,
    [householdid] [INT] IDENTITY(1,1) NOT NULL,

 CONSTRAINT [household_mem_primaryKey]  PRIMARY KEY NONCLUSTERED
(
    [householdid] ASC
)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )

GO

SET IDENTITY_INSERT [BGD].[dbo].[household_mem] ON

GO

INSERT INTO [BGD].[dbo].[household_mem] ([location2id], [location3id], [location4id], [location5id], [location6id], [location7id], [location8id], [location9id], [povertyscore], [householdid]) SELECT [location2id], [location3id], [location4id], [location5id], [location6id], [location7id], [location8id], [location9id], [povertyscore], [householdid] FROM [BGD].[dbo].[household_mem_old]

GO

SET IDENTITY_INSERT [BGD].[dbo].[household_mem] OFF

GO

Caution: Changing any part of an object name could break scripts and stored procedures.
The statement has been terminated.
Msg 701, Level 17, State 109, Line 47
There is insufficient system memory in resource pool 'default' to run this query.

I have also tried to add a resource pool for my database but still get the same error.

Microsoft recommends 2x data/index size as a starting point for memory allocation, but that's a generalization.

Is any of the data and/or indexes in the source table compressed?

How much memory on the server?

How much memory allocated to SQL Server?

When you created the resource group, did you bind the database to it, and then offline/online the database to make the binding effective? What percent did you allocate to the resource group?

Please post the result of:
SELECT committed_target_kb
FROM sys.dm_os_sys_info

And:
SELECT @@version

Agree with @sp_blitzErik that an atomic insert of 100 million rows to a memory-optimized table is not the best way to migrate data, but not necessarily because it will require less memory - batching will perform much better.


how would i merge a column where matches a different column? It's very similar to the below post

How to merge column in mysql depending on match

table user

I would like to Merge customer_id from table user to table cust where customers_firstname AND customers_name match

table cust

You need to apply update with join if you want to update the cust table

update cust c
join user u on u.customers_firstname = c.customers_firstname
and u.customers_lastname = c.customers_lastname
set c.customers_id = u.customers_id


DatabaseMail didn't start when an email is queued This is about SQL Server 2005.  I have configured SQL agent jobs to send an email when it failed.  So far I have received email reliably.

However,  I have an weird problem where an email would get queued but not delivered immediately.  As an example, a job failed on April 1 at 9 AM.  When I query

SELECT * FROM dbo.sysmail_mailitems

It will show the error mail has a send_request date on 2015-04-01 09:01:30.000, however, I just received the email today, and the sent_date is shown as 2015-04-02 10:10:51.000.

I checked the database mail log, and the database mail process did NOT start on 2015-4-1.  It is only started on 2015-04-02 10:10:51.000, and at that time I open the Database Mail dialog box (I did not change anything - I just opened it to check a few settings).

The database mail log (sysmail_event_log)

2015-03-22 01:00:18.597 DatabaseMail process is started
2015-03-22 01:10:21.013 DatabaseMail process is shutting down
2015-04-02 10:10:50.393 DatabaseMail process is started
2015-04-02 10:20:53.960 DatabaseMail process is shutting down

The previous job error was on 3/22 and the email was sent properly.  The latest error on 4/1 didn't.

The server is not under stressed, and there is no error message in the server log.  It is just the Database Mail process did not start even when a mail is queued for some reason.

Summary:

Expected behaviour:

Job failed
Agent queue an email
Databasemail process starts
Email sent
Databasemail process stops

Actual behaviour:

Job failed
Agent queue an email
Databasemail process did not start immediately
Databasemail process decided to start after 2 days for unknown reason
Email sent, 2 days late
Databasemail process stops

see what happens when you try to send an email using powershell instead.

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql"
$body = "test email from ccatsql "
$smtp.Send($emailFrom, $emailTo, $subject, $body)

and checked which .Net versions you have installed

the problem might be in the .NET. Check the windows\applications error log.
I had a similar problem (see the question here) then I installed .NET 3.5 and all started to work nicely.

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

How can I see the current Database Mail configuration?

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

EXEC msdb.dbo.sysmail_help_account_sp

Have a look at this question and answer:
Comprehensive troubleshooting of DatabaseMail


Can't set up Unicode support I am creating a toy website locally with XAMPP and I want it to support Unicode.

index.php contains a simple form that I can use to add a new entry (a UTF-8 string) to a table "myTable" in the DB. In addition to that, I am also echoing with PHP every entry from "myTable" on the body of index.php, so that I can see if the UTF-8 strings that I am entering through the form make all the way into the database and back without any issues.

Here are the measures that I took toward Unicode support:

Every file on my website (ex.: index.php) is encoded in UTF-8 without BOM;
<meta charset='utf-8'> everywhere applicable too;
mysqli_set_charset($myConnection, "utf-8"); in my "db.inc.php" file;
At the phpMyAdmin "control panel" (?), under "Database Server" I see "Server charset: UTF-8 Unicode (utf8)";
I created a database and a table for this specific purpose. Here is the result of SHOW CREATE TABLE `myTable`:

CREATE TABLE `myTable` (
`row1` varchar(16) COLLATE utf8mb4_bin NOT NULL,
`row2` varchar(32) COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

The outcome was as follows: when I print the entries of "myTable", everything does get displayed correctly on the browser indeed, however only owing to a stroke of luck, because apparently my strings are being internally represented badly. When I browse my table with phpMyAdmin I see mojibake, and I looked over at myTable.ibd and I see the same mojibake.
The string joão e maría for example, is displayed as joÃ£o e marÃa; for the first ã -> Ã£, it could be UTF-8 bytes being interpreted as latin-1, though I see Ã£ even when I tell npp to interpret the file as UTF-8.

Running the query SELECT `row1`, HEX(`row1`) FROM `myTable` WHERE 1 I see 6A6FC383C2A36F2065206D6172C383C2AD61 as the bytes of joÃ£o e marÃa, so in fact the mojibake is "correctly" encoded as UTF-8 (C3 83 C2 A3 is UTF-8 for Ã£).

Ideas?

UPDATE

After more careful inspection I verified that mysqli_set_charset($myConnection, "utf-8"); was returning false. I replaced it with mysqli_set_charset($myConnection, "utf8"); and voilà, strings with fancy characters that I enter through the form now make it all the way into the database unscathed. However, these new strings aren't correctly displayed by the browser.

For example: the string bênção is inserted into the DB through the form. I query the table with SELECT `row`, HEX(`row1`) (...) and find out that it is stored as 62C3AA6EC3A7C3A36F inside the table, which is the correct UTF-8 representation of the string in question. I access index.php and I see b�n��o being displayed. I download the HTML of the webpage and inspect it, and I find that the offending bytes are respectively EA, E7, E3, which, surely enough, correspond to ê, ç and ã in latin-1.

<meta charset="UTF-8"> -- correct for outside MySQL

utf8mb4 is the term you need to use inside MySQL, including

mysqli_set_charset($myConnection, "utf8mb4");

Ã£ is Mojibake for ã.  This happens when latin1 is mistakenly somewhere in the data flow.

Hex EA E7 E3 is the the latin1 encoding for êçã.
Hex C3AA C3A7 C3A3 is the UTF-8 (utf8mb4) encoding for them.

Since you have presented different symptoms, let me present to you 'Best Practice' and how to diagnose the symptoms for 'black diamond with question mark', 'Mojibake', etc.  Note that there are 4-5 places where you have to consistently use UTF-8.


Incorrect result when using 'case when count()=0' & 'where' I would very appreciate if someone could help me out with this query.
I can't seem to get the correct result when using case when count()=0 & where

I work with SQL Server, with a DB of a system that is alike "Facebook".

I'm trying to write a query that shows the email address, tagging status & the number of posts posted in the past month.
The tagging status should be as follows:
If tagging 1-2 posts - display "few"
If tagging 3-5 posts - display "medium"
If tagging 5+ posts - display "many"
If not tagging at all - display "none".

Below is the query I wrote. The problem with the query I wrote is that because of the condition in WHERE, I will never receive 'none'.

How can I change the query in order to display the correct result?

select U.Mail, count(P.ID) as PostCount,
  case
    when count(P.ID) = 0 then 'none'
        when count(P.ID) <= 2 then 'few'
        when count(P.ID) <= 5 then 'medium'
    else 'many'
  end PostCountCategory
from Users U
    left join Tagging T on U.Mail = T.Mail
    left join Post P on T.IDPost = P.ID
where datediff(day,P.DatePosted,getdate()) <= 30 --Because of this condition I would never get 'none'
group by U.Mail, U.Gender

Data for example:

The required input should be: Kelly-'none', Lilly-'none', Nelly-'few', Owen-'none'.

create table Users
(
Mail nvarchar (20) primary key check(Mail like '_%@_%._%' and (Mail like '%[0-9]%' Or Mail like '%[a-z]%'Or Mail like '%[A-Z]%')),
Password nvarchar (8) check (Password like '%[0-9]%' and Password like '%[az]%' and len(password) <= 8) not null,
FirstName nvarchar (20) not null,
LastName nvarchar (20) not null,
BirthDate date check (datediff(year,BirthDate,getdate())>=18) not null,
JoinDate date check (JoinDate<=getdate()) not null,
Gender nchar(1) check(Gender = 'F' or Gender = 'M' or Gender = 'O'),
NickName nvarchar(20),
Photo nvarchar(20),
Phone bigint check (Phone like '%[0-9]%' and len(Phone) <= 10) not null
)

INSERT INTO Users
VALUES
('Kelly@gmail.com','k1000000','Kelly','Ka','1992-05-15','2016-09-04','F','Kelly','Kelly.jpg','546296100'),
('Lilly@gmail.com','l1101111','Lilly','La','1999-04-03','2012-04-04','F','Lilly','Lilly.jpg','542448300'),
('Nelly@gmail.com','n130131','Nelly','Na','1994-03-07','2020-04-13','F','Nelly','NellyNa.jpg','541234567');
('Owen@gmail.com','o140141','Owen','Oa','1992-02-02','2020-05-13','M','Owen','OwenOa.jpg','541234567');

create table Post
(
ID int identity(1,1) primary key,
Photo nvarchar(20),
Text nvarchar(200),
Location nvarchar(50),
Video int,
DatePosted date check (datediff(month,DatePosted,getdate())<=3) not null,
UserMail nvarchar (20) references Users(Mail) on delete cascade on update
cascade not null
)

INSERT INTO Post
VALUES
('','my name is nellu','','','2020-05-08','Nelly@gmail.com'),
('','hii','','','2020-02-19','Lilly@gmail.com');

create table Tagging
(
Mail nvarchar (20) references Users(Mail) not null,
IDPost int references Post(ID) not null,
TagMail nvarchar(20) references Users (Mail) not null,
primary key (Mail,IDPost);
)

INSERT INTO Tagging
VALUES
('Nelly@gmail.com','1','Kelly@gmail.com'),
('Nelly@gmail.com','1','Owen@gmail.com');

just move where conditions to the join part:

select U.Mail, count(P.ID) as PostCount,
  case
    when count(P.ID) = 0 then 'none'
        when count(P.ID) <= 2 then 'few'
        when count(P.ID) <= 5 then 'medium'
    else 'many'
  end PostCountCategory
from Users U
    left join Tagging T on U.Mail = T.Mail
    left join Post P on T.IDPost = P.ID AND datediff(day,P.DatePosted,getdate()) <= 30
group by U.Mail, U.Gender

also your original condition doesn't alloow SQL Server to use index seek. It would be better to rewrite it this way:

P.DatePosted >= dateadd(day, -30, cast(getdate() as date))


How does direct path load work when dataguard is enabled? There's a few posts about direct path and dataguard but I just want to understand exactly how it actually works.

How maybe I made an incorrect assumption, but if direct path means it writes to the data files  (as per the docs) then doesnt that mean no redo is generated?

And if no redo is generated then how does dataguard work?  ( which is soley based upon transferring redo to the standby machine right? )

If the database is setup correct: no direct path load. If a standby database is depending on the changes captured on the primary database, the primary database should be setup with force logging.

If for some reason the force logging setting is not in place, after such an operation the affected datafiles should be transferred to the standby site[s]. Failing to do so will make queries on the loaded parts of the table[s] in the standby database to fail.


MongoDB query execution time I'm using Locustio service as load testing tool for my app. Without details, I'm using the same aggregation query with simulated 100 users, in this case RPS (requests per second) is about 20. In first 30 seconds every query time is about 10-50ms, but then (when about 65 users active) it increases to 500-1000ms. I'm using MMAPv1, so query is cached for sure, so the question is: why query execution time is increasing?

Of course, it's only test case, in real I'm doing different queries, but in that case I wasn't sure that all the queries was cached. That's why I decided to test It only with one query. Sorry for my bad explanation.

I think the problem is in connection number, still it's only 100. How cna I optimize the performance of my app?

UPDATE1

db.serverStatus about connections number:

    "connections" : {
        "current" : 20,
        "available" : 51180,
        "totalCreated" : NumberLong(32)
    },

All of my engine settings are defaults
This is my db.collection_name.findOne() part, which involved in aggregate query:

{
    "_id" : ObjectId("571764be1e31c8556e80b589"),
    "ppl_ids" : [
        19,
        1,
        20,
        21,
        22,
    ],
}

The pipeline of aggregate query itself:

{$match: {ppl_ids: {$in: [16,1245,592] } } }

The part of db.collection_name.getIndexes():

    {
        "v" : 1,
        "key" : {
            "ppl_ids" : 1
        },
        "name" : "ppl_ids_1",
        "ns" : "dbname.collection_name",
        "background" : false
    },

Update based on MongoEngine usage in comments:

MongoEngine defers to pymongo for the connection pool setting. Hence, based on the pymongo FAQ:

  In versions before 2.6, the default max_pool_size was 10, and it did
  not actually bound the number of open connections; it only determined
  the number of connections that would be kept open when no longer in
  use.

  Starting with PyMongo 2.6, the size of the connection pool is capped
  at max_pool_size, which now defaults to 100. When a thread in your
  application begins an operation on MongoDB, if all other connections
  are in use and the pool has reached its maximum, the thread pauses,
  waiting for a connection to be returned to the pool by another thread.

Assuming you are using a version later than 2.6 you will have a default of 100, which theoretically should be enough.  However even if the responses are very quick you still have some overhead (connect, run query, return result, return connection to pool) with the pool in order to reach 100 concurrent users on the front end. Therefore, I would still look at increasing the max_pool_size parameter to see if that unblocks your load testing (say 150 to start with).


Which version of Oracle supports Streams I am using Oracle 10g Express Edition. I want to enable the streams feature in my current edition. However, when I searched on the net I couldn't find any information regarding streams setup on Oracle XE

Can anyone tell me which version of Oracle supports streams? Is it only the Enterprise Edition that supports it? If yes how can I add streams support to my express  or standard editions

XE does not support Streams, as officially documented. SE and SE1 support Streams but without redo capture. EE and PE (Personal Edition) fully support Streams, and also support Logical Standby.


Query To Show Quarterly Data I am attempting to write a query to show data for two quarters.  I have my query returning accurate data from my calculations, BUT it returns two rows of data instead of just a single row like I need.  TO further show this, see this screenshot

This is my sample DDL

Create Table Test
(
  saledate date
  ,value float
  ,admincharges1 float
  ,admincharges2 float
  ,admincharges3 float
  ,admincharges4 float
  ,admincharges5 float
  ,admincharges6 float
)

Insert Into Test
(saledate, value, admincharges1, admincharges2, admincharges3, admincharges4, admincharges5, admincharges6) Values
('01/01/2016', 200, 10, 20, 10, 10, 10, 10)
,('06/01/2016', 400, 20, 20, 20, 20, 20, 20)

And this is the query that I use to try to get the "I want Returned" from my image above.

Select
case when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 1
Then Sum([value])/Sum([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
end As Q1
,case when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 2
Then Sum([value])/Sum([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
end As Q2
FROM Test
Group by Datepart(Year, [saledate]), DATEPART(Quarter, [saledate])

What do I need to alter in my query in order to return my desired output?

You're getting two rows because your GROUP BY forces two rows of output, and your case statements run on each.

By putting the case statements inside the SUMs and removing the GROUP BY, you can return a single row.

I think below should work:

SELECT
SUM(
    case
    when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 1
    Then [value]/([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
    end
) As Q1
,SUM(
    case
    when Datepart(Year, [saledate]) = '2016' AND DATEPART(Quarter, [saledate]) = 2
    Then [value]/([value]-(ISNULL([admincharges1],0)+ISNULL([admincharges2],0)+ISNULL([admincharges3],0)+ISNULL([admincharges4],0)+ISNULL([admincharges5],0)+ISNULL([admincharges6],0)))
    end
) As Q2
FROM Test


SQL Server 2008 job failure I have a problem on my servers sitting on different domains.

I created a linked server that executes successfully and I can list the catalogue but when I schedule a job to execute I get this error:

  Access to the remote server is denied because the current security context is not trusted.

How can I resolve this?

Condsiderations for linked server security options in SQL Server

(Two things to mention for typical simple configurations)

If you have the option set to Be made using the login's current security context then . . .

ensure that the security principal (and context) that the job will RUN AS from the SQL Agentjob also has appropriate access to any of the linked server DB(s) which the job will need to access for it's queries, etc.
if the security principal is a local SQL login and not a domain or trusted domain account, then be sure that the local SQL account is created with a matching SID on both the linked server SQL Server instance and the SQL Server instance where the job is using the linked server from.

If you have the option set to Be made using the security context: then . . .

ensure the local SQL login security principal is created with a matching SID on both the linked server SQL Server instance and the SQL Server instance where the job is using the linked server from.


Why Oracle doesn't have nolock? In MS SQL Server nolock can be used for the purpose.

Why can't we use it in Oracle and plsql?

SQL Server normally uses a different locking strategy to Oracle. The default strategy used by SQL Server means that selecting rows causes read locks (on rows, pages or the whole table) to be placed on them*.  Hence the NOLOCK is sometimes a useful clause - although the general advice is to never use it, as it changes the semantics of isolation levels and may cause inconsistent results in the queries' output.

* (Note: that is the default. If SNAPSHOT isolation is chosen, the behaviour is different and readers do not block writers.)

In Oracle a read process will never block a write process. The following is an excerpt from 'Oracle Database Concepts 11g Release 2'. I encourage you to take a look if you are interested in how this is handled by Oracle.

Data Concurrency and Consistency

  Summary of Locking Behavior

  The database maintains several different types of locks, depending on
  the operation that acquired the lock. In general, the database uses
  two types of locks: exclusive locks and share locks. Only one
  exclusive lock can be obtained on a resource such as a row or a table,
  but many share locks can be obtained on a single resource.

  Locks affect the interaction of readers and writers. A reader is a
  query of a resource, whereas a writer is a statement modifying a
  resource. The following rules summarize the locking behavior of Oracle
  Database for readers and writers:

  •A row is locked only when modified by a writer.

  When a statement updates one row, the transaction acquires a lock for
  this row only. By locking table data at the row level, the database
  minimizes contention for the same data. Under normal circumstances
  1  the database does not escalate a row lock to the block or table
  level.

  •A writer of a row blocks a concurrent writer of the same row.

  If one transaction is modifying a row, then a row lock prevents a
  different transaction from modifying the same row simultaneously.

  •A reader never blocks a writer.

  Because a reader of a row does not lock it, a writer can modify this
  row. The only exception is a SELECT ... FOR UPDATE statement, which is
  a special type of SELECT statement that does lock the row that it is
  reading.

  •A writer never blocks a reader.

  When a row is being changed by a writer, the database uses undo
  data to provide readers with a consistent view of the row.


Reorganize Index Task Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance?

Is this an adequate step of triage for degraded application/query performance?

Can the Reorganize Index task (maint' plan or ssis package) be used to 'performance tune?

Performance tuning is an art. You should avoid knee jerk performance tuning methods - instead you should try to narrow down your investigation.

  Is this an adequate step of triage for degraded application/query performance?

It may or may not be, depending on how many pages the index has and how bad the fragmentation. Brent Ozar has a nice post on - Stop Worrying About SQL Server Fragmentation and Kendra Little explains - Why Index Fragmentation and Bad Statistics Aren’t Always the Problem (Video) ?

What I would suggest you is have a baseline - e.g you can baseline your waitstats and check for any anomalies as a starting point. Then you can narrow down your investigation and find the root cause and address it.

Depending on sql server version you are using, use SQL Server Diagnostic Information Queries or SQL Server Health Check Script - sp_Blitz. These are free and very efficient tools written by experts and given to the community as free tools.

As a side note, as RLF suggested - use SQL Server Index and Statistics Maintenance for doing index maintenance. Its very widely used in the community and its free as well. Dont reinvent the wheel by writing your own solution.


In MySQL, what's the most efficient way to query an entire table in chunks? If I have a table User is it faster to do something like:

SELECT * FROM user WHERE id > ? ORDER BY id LIMIT 10000

(With each subsequent query using the previous last row's id as the param)

Or to use a LIMIT with an offset, like this:

SELECT * FROM user ORDER BY id LIMIT ?, 10000

(With total number of records retrieved so far being the offset).

As an additional complication, what if I wanted to perform a similar query based on a linking table join? Like say a user could belong to multiple roles, so I wanted a query like

SELECT u.*
 FROM user u
 INNER JOIN user_roles ur ON ur.user_id = u.id
 WHERE ur.role_name IN ('Admin', 'SuperUser', 'etc.')
 LIMIT ?, 10000

Does a join affect the decision as to which style query is preferred?

OFFSET is very bad.  Instead of processing the data in Order(N) time, it will take Order(N*N).  This is because of the effort to skip past the "offset" rows.

Instead "remember where you left off".  I go into details about such in these two blogs:

Pagination via OFFSET is bad:  http://mysql.rjweb.org/doc.php/pagination (It gives details on "left off" as a good alternative)
How to delete lots of rows via chunking:  http://mysql.rjweb.org/doc.php/deletebig . You need to adapt it from DELETE to what you are doing.  It also gives tips on how to walk through the PRIMARY KEY to efficiently discover the next 'chunk'.

These tips apply to all versions of MySQL.


Trigger that will stop from deleting if a foreign key record is not deleted I've got two tables: Countries (Id, Name) and Cities (Id, CountryId, Name), CountryId is a Foreign Key to Cities.

I need a trigger that will prevent deleting certain Cities if the Country is not being deleted, in other words certain Cities can only be deleted when Country is being deleted.

CREATE TABLE Countries
(
    Id [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    Name [VARCHAR] (100) NOT NULL
);

INSERT INTO Countries VALUES ('Germany'), ('Spain'), ('Italy');

CREATE TABLE Cities
(
    Id [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    CountryId [int],
    Name [VARCHAR] (100) NOT NULL,
    CONSTRAINT FK_Cities_Countries
        FOREIGN KEY (CountryId) REFERENCES Countries(Id)
);

INSERT INTO Cities VALUES
(1, 'Berlin'), (1, 'Dresden'), (2, 'Madrid'), (2, 'Barcelona'), (3, 'Rome'), (3, 'Milano');

ERP system allows to delete records both from Cities and Countries (when Country deletion action is called first records from Cities are deleted then from the Countries. I need to block deletion from Cities if Country is not being deleted i.e.

DELETE FROM Cities WHERE Name='Berlin' --needs to be stopped
DELETE FROM Countries WHERE Name='Germany' --needs to go

Hope it is clear now.

As Scott Hodgin pointed out, you can't delete a country meanwhile there are cities belonging to this country.

DELETE FROM Countries WHERE Name = 'Germany';

  Msg 547 Level 16 State 0 Line 1
  The DELETE statement conflicted with the REFERENCE constraint "FK_Cities_Countries". The conflict occurred in database "db", table "dbo.Cities", column 'CountryId'.

You could change NO ACTION by ON DELETE CASCADE

CONSTRAINT FK_Cities_Countries
    FOREIGN KEY (CountryId) REFERENCES Countries(Id)
    ON DELETE CASCADE

But according to your question you want a trigger that blocks delete action for some Cities if it Country still exists.

CREATE TRIGGER trgCities_Delete
ON Cities
INSTEAD OF DELETE
AS
BEGIN

    DELETE c
    FROM   Cities c
    JOIN   deleted d
    ON     c.ID = d.ID
    WHERE  NOT EXISTS (SELECT 1
                       FROM   Countries
                       WHERE  Id = d.CountryId);
END

Then when you try to create the trigger you'll receive next error:

  Msg 2113 Level 16 State 1 Line 1
  Cannot create INSTEAD OF DELETE or INSTEAD OF UPDATE TRIGGER 'trgCities_Delete' on table 'Cities'. This is  because the table has a FOREIGN KEY with cascading DELETE or UPDATE.

Humm, that's a catch-22 situation.

Ok, let me remove ON DELETE CASCADE action and now I try to delete some city.

DELETE FROM Cities WHERE Name = 'Barcelona';

SELECT * FROM Cities;

Id | CountryId | Name
-: | --------: | :--------
 1 |         1 | Berlin
 2 |         1 | Dresden
 3 |         2 | Madrid
 4 |         2 | Barcelona
 5 |         3 | Rome
 6 |         3 | Milano

Barcelona is still there. The trigger works as expected.

Now try to delete 'Spain':

DELETE FROM Countries WHERE Name = 'Spain';

  Msg 547 Level 16 State 0 Line 1
  The DELETE statement conflicted with the REFERENCE constraint "FK_Cities_Countries". The conflict occurred in database "db", table "dbo.Cities", column 'CountryId'.

Catch-22 again. I can't delete Barcelona neither Spain!! That's a bit mental.

Let me suggest another approach. If you need to avoid deletes, flag the affected records using some field and let foreign keys do their job.

ALTER TABLE Cities ADD Blocked int NULL;

UPDATE Cities
SET    Blocked = 1
WHERE  Name = 'Berlin';

Now change the trigger by:

ALTER TRIGGER trgCities_Delete
ON Cities
INSTEAD OF DELETE
AS
BEGIN

    DELETE c
    FROM   Cities c
    JOIN   deleted d
    ON     c.ID = d.ID
    AND    c.Blocked <> 1;

END

DELETE FROM Cities WHERE Name = 'Berlin';
SELECT * FROM Cities;

Id | CountryId | Name      | Blocked
-: | --------: | :-------- | ------:
 1 |         1 | Berlin    |       1
 2 |         1 | Dresden   |       0
 3 |         2 | Madrid    |       0
 4 |         2 | Barcelona |       0
 5 |         3 | Rome      |       0
 6 |         3 | Milano    |       0

Well Berlin still there, now if you want to delete Spain you must delete Madrid and Barcelona before.

DELETE FROM Cities WHERE Name IN ('Barcelona', 'Madrid');
DELETE FROM Countries WHERE Name = 'Spain';
SELECT * FROM Cities;

Id | CountryId | Name    | Blocked
-: | --------: | :------ | ------:
 1 |         1 | Berlin  |       1
 2 |         1 | Dresden |       0
 5 |         3 | Rome    |       0
 6 |         3 | Milano  |       0

dbfiddle here


Adding an additional "order by" column gives me a much worse plan in other words, how can I get rid of the sort operator on the picture below?

the picture above shows the execution plan of the following 2 selects together:

                SELECT   TOP 1 so.OrgType,
                              ch.Status,
                              rcs.DBSstatusDescription,
                              cid.ApplicationId
                   FROM   tbl_application_crb_initialData cid

                   INNER JOIN  tbl_season_organisationId so
                          ON   cid.OrganisationId = so.OrgId

                   LEFT JOIN  tbl_crbHistory ch
                          ON  cid.ClientReference = ch.ClientReference

                   LEFT JOIN  ref_crbStatus rcs
                         ON   ch.Status = rcs.statusId

                   ORDER BY  cid.DateAdded DESC, ch.DateAdded DESC

                SELECT   TOP 1 so.OrgType,
                              ch.Status,
                              rcs.DBSstatusDescription,
                              cid.ApplicationId
                   FROM   tbl_application_crb_initialData cid

                   INNER JOIN  tbl_season_organisationId so
                          ON   cid.OrganisationId = so.OrgId

                   LEFT JOIN  tbl_crbHistory ch
                          ON  cid.ClientReference = ch.ClientReference

                   LEFT JOIN  ref_crbStatus rcs
                         ON   ch.Status = rcs.statusId

                   ORDER BY  cid.DateAdded DESC--, ch.DateAdded DESC

the only difference is that on the second query, there is only one column in the order by.

would it make a difference, as I am using top 1?

I believe all the needed info are on the indexes and table definitions that can be seen on the query plan.

if anything else would help to get rid of that sort just let me know, tomorrow I will post all the possible info.

Your question is missing a lot of detail but I can reproduce something similar.

Setup

CREATE TABLE T1(X INT PRIMARY KEY, Y INT INDEX IX)

CREATE TABLE T2(X INT, Y INT , PRIMARY KEY(X, Y))

INSERT INTO T2
OUTPUT INSERTED.* INTO T1
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID), ROW_NUMBER() OVER (ORDER BY @@SPID)
FROM sys.all_objects o1, sys.all_objects o2;

Query 1

SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y;

Query 2

SELECT TOP 1 T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y, T2.Y

Query 3

WITH T  AS
(
SELECT TOP 1 WITH TIES T1.Y AS T1Y, T2.Y AS T2Y
FROM T1 JOIN T2 ON T1.X = T2.X
ORDER BY T1.Y
)
SELECT TOP 1 *
FROM T
ORDER BY T2Y

Query 1 just picks off the TOP 1 from the index in the desired sort order and does the needed joins on the other table for that row. If the join is successful it stops there otherwise it tries the next one in index order until it finds a row that matches or runs out of rows.

Query 2 When adding the new sort column this plan is no longer valid as there could be multiple matches tied with the TOP 1 value and SQL Server decides to join the whole lot and then get the TOP 1 from that.

Query 3 This encourages SQL Server to stick with the first strategy and then just does a TOP 1 Sort on any rows tied with the same value for the first sort key.

For my example data Query 3 works out better than Query 2 but if you have many duplicates tied for the value of the first sort key your milage may differ.

You can try this rewrite and see how it fares

WITH T
     AS (SELECT TOP 1 WITH TIES so.OrgType,
                                ch.Status,
                                rcs.DBSstatusDescription,
                                cid.ApplicationId,
                                ch.DateAdded AS chDateAdded
         FROM   tbl_application_crb_initialData cid
                INNER JOIN tbl_season_organisationId so
                        ON cid.OrganisationId = so.OrgId
                LEFT JOIN tbl_crbHistory ch
                       ON cid.ClientReference = ch.ClientReference
                LEFT JOIN ref_crbStatus rcs
                       ON ch.Status = rcs.statusId
         ORDER  BY cid.DateAdded DESC)
SELECT TOP 1 OrgType,
             Status,
             DBSstatusDescription,
             ApplicationId
FROM   T
ORDER  BY chDateAdded DESC


EAV-like table with both primary column pointing to the same foreign column Background: I'm a programmer who, while had to work with simple databases several times, never had to work with complex ones. I.e. I'm proficient with basic stuff, but clueless about advanced features and designs.

I need to create a table with the results of matches between two users, i.e.:

table_A: user_id (primary int), other stuff
table_B: user1 (primary foreign key pointing to table_A user_id), user2 (primary foreign key pointing to table_A user_id), other stuff

Can I enforce user2<user1? This would be in order to prevent rows from having user1==user2, and to prevent the creation of rows were the couple would be inverted, i.e. I need not to have both (alice,bob) and (bob,alice).

-OR- I'm doing it all wrong in the first place and there's another, completely different, approach to do that?

Obviously I can (and will) check the values before adding them, but as far as I understand, if I can add a constraint in SQL, I would better add it, just to be sure it's consistent.

Database currently is sqlite, but in case this can't be done in sqlite but can be done with other engines (especially if InnoDB), it could be a useful answer too.

PS: I'm not sure if this still counts as EAV or not, since AFAIK EAV implies two primaries from different tables, and a single extra column while instead I'll have more than one.

A simple CHECK constraint works just fine:

$ sqlite
SQLite version 3.8.4.1 2014-03-11 15:27:36
...
sqlite> CREATE TABLE table_B(
   ...>     user1,
   ...>     user2,
   ...>     [other stuff],
   ...>     CHECK (user2 < user1)
   ...> );
sqlite> INSERT INTO table_B VALUES (1, 0);
sqlite> INSERT INTO table_B VALUES (2, 3);
Error: CHECK constraint failed: table_B

(This has nothing to do with EAV.)


Understanding Plan Choice When Query Store Doesn't Respect Forced Plan I'm trying to figure out a situation that I encountered last week where, in an attempt to temporarily fix a plan regression during an incident, I forced an execution plan using Query Store, but found it didn't work as expected. I have read Andrew Kelly's article on how forced doesn't always mean forced and think this may be what I'm seeing, but I'm hoping for some more insight into it, as in my case the plans are more different than I'd expect. I've also had a look over the Plan Forcing Limitations section of the Query store documentation on plan forcing, but I don't think any of the restrictions here apply.

The view in query store is shown below - around 8:30 a new plan with a higher cost showed up (2.14178 vs 0.894238), and I forced the plan which was in use previously. As can be seen in the graph, the new plan is shown against the metrics for the query from this point on, despite me having forced the old plan:

Looking at sys.query_store_plan, I can see that the old plan is showing as forced and has nothing against it suggesting any failures in forcing it:

The strange thing here is that when I later looked in the plan cache, neither of these plans were in use, though the plan in use did have the same query plan hash value as plan 13449 shown above, despite being quite different. The plan that was actually in use had a much higher estimated cost at 72.6743.

I took the compiled values from each of these plans and ran the query for the three of them to get an idea of what the actual metrics looked like for the plans and the values are quite different from the estimates. Notably, the plan I took from the cache produced a ~400 MB memory grant due to it using a different index and having to sort the data, compared to an 8 MB memory grant on the other 2 plans, with no sort. The estimated costs of the three plans were a bit closer, but the 2 from query store had much higher estimated costs than were in the plans shown in query store.

This is the query this happened with:

(
    @id_group int,
    @create_date datetime,
    @rows_per_page int,
    @page_number int
)
SELECT *
FROM ts_customer
WHERE
    id_group = @id_group
    AND enabled = 'Y'
    AND (create_date >= @create_date)
ORDER BY
    full_name,
    id_customer desc
offset
    ((@page_number - 1) * @rows_per_page) rows
    fetch next @rows_per_page rows only

Could anyone help me understand why the plan wasn't forced, and why the one in use was so much more expensive?

I know that select * will be causing the large memory grant as it is a wide table, but I'm mainly trying to understand the behaviour seen here with query store, and to a lesser extent, why on the plan that was used instead of the forced plan it used a different index and had to do the sort.

The database this happened with is running on Azure SQL DB, compatibility level 140, and using the legacy cardinality estimator.

I've attached all 3 estimated plans, as well as the plan that was generated when I ran the query with each set of compiled values. These can be accessed here.

Could anyone help me understand why the plan wasn't forced

Looking through the plan XML, plan id 6194 was compiled on version 15.0.700.539 of SQL Server, while plan id 13449 was compiled on version 15.0.900.210.

The only differences of note (other than cardinality estimates) between the two plans are these:

The compute scalar was affected by a row goal (due to the implicit TOP from OFFSET / FETCH):

EstimateRowsWithoutRowGoal="170.008"

And the NL join was optimized with undordered prefetch:

<NestedLoops Optimized="true" WithUnorderedPrefetch="true">

It seems that these slight differences resulted in the problem described in Andrew Kelly's article, which you linked to.  The plan is essentially the same, with minor implementation differences.  Grant Fritchey describes this as follows:

  There is a relatively obscure situation where a "morally-equivalent" plan, a plan that is identical in all the core essentials, but not necessarily perfectly identical, can be used instead of the precise plan you define. However, this isn't common.

From page 473 of his free e-book about execution plans.

  and why the one in use was so much more expensive?

The really expensive plan in use has 4 extra columns, so I think that one is not really comparing apples to apples.  The higher costs appear to mostly be due to the parameters used (and thus higher cardinality estimates).  Incidentally, it's on another completely different build: 15.0.1300.566.  Things sure do move fast in the cloud =P

These are all "new" columns coming out of the SELECT *:

<ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="delete_requested_date_utc" />
<ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="retention_policy_applied_date_utc" />
<ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="retention_policy_type" />
<ColumnReference Database="[app-live-au]" Schema="[dbo]" Table="[ts_customer]" Column="id_portal_user" />

The combined test you provided were all inline with this latest version of things.


Total duration "server" was in "In" state per day I have data of a server start & stop time on a daily basis for one month. I need the result containing the first start time of the day, last stop time of the day, total time the server was started in the day, for each server and each day.

Data sample:

Date & Time           Reader ServerID
3/14/2016 6:36:20 AM    IN    123
3/14/2016 6:58:45 AM    OUT   123
3/14/2016 8:06:19 AM    IN    123
3/14/2016 9:32:48 AM    OUT   123
3/15/16 6:00:00 AM      IN    123
3/15/16 6:01:00 AM      OUT   123
3/14/2016 9:46 AM       IN    124
3/14/2016 10:01 AM      OUT   124
3/14/16 11:01 AM        IN    124
3/14/16 12:01 PM        OUT   124

Expected output:

ServerID  FirstIN  Last Out  TotalInTime (min)  Date
123       6:00     09:32     86                 3/14
123       06:00    06:01     1                  3/15
124       9:46     12:01     75                 3/14

Please note that each IN will always have a corresponding OUT in the source table.

How can I solve this?

Here are the CREATE TABLE and INSERT statements:

create table tbl1 (
    serverid numeric (18,0)
    , Reader varchar (20)
    , Date_Time datetime
);
Insert into tbl1 values (123, 'In', '2015-08-24 06:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 07:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 09:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 11:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-25 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-25 11:00:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 06:15:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 07:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 09:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 11:30:00.000');

Since you are certain the source data is always reliable, you might use this:

;WITH src AS
(
    SELECT t1.ServerID
        , [Date] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, t1.InDate))
        , t1.InDate
        , OutDate = (
        SELECT TOP(1) OutDate = al.DateAndTime
        FROM dbo.tbl1 al
        WHERE al.Reader = 'OFF'
            AND al.ServerID = t1.ServerID
            AND al.DateAndTime > t1.InDate
        ORDER BY al.DateAndTime
        )
    FROM (
        SELECT al.ServerID
            , al.Reader
            , InDate = al.DateAndTime
        FROM dbo.tbl1 al
        WHERE al.Reader = 'ON'
        ) t1
)
, src2 AS
(
    SELECT *
        , Duration = DATEDIFF(MINUTE, src.InDate, src.OutDate)
    FROM src
)
SELECT src2.ServerID
    , src2.Date
    , FirstIn = MIN(src2.InDate)
    , LastOut = MAX(src2.OutDate)
    , Duration = SUM(src2.Duration)
FROM src2
GROUP BY src2.ServerID
    , src2.Date
ORDER BY src2.ServerID
    , src2.Date;

Output (I used the first set of sample data, since you didn't originally provide the INSERT statements):

This code collapses your source table so each row has an "in" and an "out" date, which enables us to use the DATEDIFF function to calculate the duration.

This line, [Date] = DATEADD(DAY, 0, DATEDIFF(DAY, 0, t1.InDate)) strips the time component from the InDate so we can use it to display one row per day per server.  This could be moved into the table definition as a calculated, persisted column.  The table definition would be:

CREATE TABLE dbo.tbl1
(
    DateAndTime DATETIME NOT NULL
    , Reader CHAR(3) NOT NULL
    , ServerID INT NOT NULL
    , DateOnly AS DATEADD(DAY, 0, DATEDIFF(DAY, 0, DateAndTime))
        PERSISTED
);


You can not correct a bad implemented application from the database side in such a way that it will always work as expected. With cursor sharing the database tries to prevent parsing. This works when the application uses bind variables when working on item/person level and literals when working on group/class level.
You could try cursor_sharing force. This could correct some queries but at the same time hurt others.
You could try sql plan management but in my experience, when you really need it, it fails. Especially with literal littered applications. Sometimes you have luck but don't underestimate the amount of work you need to put in them. Auto sampling of plans is not the best idea here ....


Completion of SQL Job triggers start of job on a different SQL Server I want the completion of a SQL Server agent job to trigger the start of a job on another server.

More specifically, I want my SSRS server to email a subscription report whenever an FTP/Import job completes on a different production server.

if I do:
EXEC msdb.dbo.sp_start_job @job_name = 'Remote job name', @server_name = 'Remote server'
I receive:
The specified @job_name ('Remote Job Name') does not exist.
(maybe trying to run locally?)

if I do:
EXEC [Remote Server].msdb.dbo.sp_start_job @job_name = "Remote job name'
I receive:
Could not connect to server 'RemoteServer' because 'RemoteUser' is not defined as a remote login at the server. Verify that you have specified the correct login name.

This user is one we use to query across linked servers all the time, can query across, and has db_owner (just to test) rights on msdb.

If you're on SQL Server Enterprise Edition:

You could set up a Data-driven subscription in SSRS. On your database with the FTP/Import Job, the system database MSDB contains a table called sysjobhistory. On your SSRS server, set up a Data-driven subscription to run when the step in your FTP/Import job reports success to this table.

Instructions for setting up a Data-driven subscription in SSRS: Books Online

If you are on Standard Edition:

Please use a linked server as outlined by Martin Smith here:

  Data Driven subscription is an Enterprise Edition feature. Just creating a regular subscription with a schedule in the past and calling EXEC LinkedServer.ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='whatever' would do what they need.

To implement Martin's suggestion, use the SQL Server agent job that is created automatically by SSRS when a subscription is created. The job's name will be capital letters and numbers, separated by hypens.

You should create this subscription, but then alter the job itself to have a schedule in the past, where it will not run. When you call this dbo.AddEvent over your linked server, you can add a new run time to this job


Oracle PL-SQL "Regular expression" to replace each "(space)and(space)" with ',' in a string I have a string like this x=y and g=h and u=1 and I need to replace each (space)and(space) with ,. I'm using the powerful regular_expression for this but it does not give me the desired result.

select regexp_replace('x=y and g=h and u=1','(^[[:space:]]*)AND(^[[:space:]]*)', ',')
from dual;

I was wondering if you could help me out here. thanks in advance.

select regexp_replace('x=y and g=h and u=1','([[:space:]]*)and([[:space:]]*)', ',')
from dual;

REGEXP_REPL
-----------
x=y,g=h,u=1