Query to compare the structure of two tables in MySQL

TWO TABLES IN THE CURRENT DATABASE

If you want to know if two tables are different, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema=DATABASE()
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

TWO TABLES IN A SPECIFIC DATABASE

If you want to know if two tables are different in database mydb, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE table_schema='mydb'
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

TWO TABLES IN TWO DIFFERENT DATABASES

If you want to know if db1.tb1 and db2.tb2 are different, run this

SELECT IF(COUNT(1)>0,'Differences','No Differences') Comparison FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

If you actually need to see the differences, run this

SELECT column_name,ordinal_position,data_type,column_type FROM
(
    SELECT
        column_name,ordinal_position,
        data_type,column_type,COUNT(1) rowcount
    FROM information_schema.columns
    WHERE
    (
        (table_schema='db1' AND table_name='tb1') OR
        (table_schema='db2' AND table_name='tb2')
    )
    AND table_name IN ('product_today','product_yesterday')
    GROUP BY
        column_name,ordinal_position,
        data_type,column_type
    HAVING COUNT(1)=1
) A;

GIVE IT A TRY !!!


Is it against normal form to include columns with date parts if there is a datetime column in the same table?

It is against either 2nd or 3rd normal form, depending on whether the datetime column is part of the key.

You could be harmed by an update anomaly if you change a batch date, but forget to change one of the fields that are derived from it.  Now the database is self cntradictory.

When I was faced with this, I adopted an alternative approach.  I created a table called almanac, with a date type key, and all those other fields like weekday, fiscal quarter, and so on.  I then wrote a program to fill this table with ten years worth of dates.  Then, whenever I wanted all those fields with respect to any date, I just did a join.  This was especially useful because fiscal quarter was a company defined attribute, and wasn't in any built in function.  We had dozens of date fields, and reporting by fiscal quarter was a requirement.


Foreign key constraint is incorrectly formed (MySQL)

Without seeing your categories table, your only problem is that the referenced columns like inart_ps_category_id need an index, which you can achieve by using KEY(inart_ps_category_id):
CREATE TABLE IF NOT EXISTS `inart_ps_categories` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inart_ps_category_id` int(11) UNSIGNED NOT NULL,
  `parent_id` int(11) UNSIGNED DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `path` varchar(255) DEFAULT NULL,
  `num_children` int(11) DEFAULT '0',
  `flag` tinyint(1) NOT NULL,
  PRIMARY KEY (`id`),
  KEY(`inart_ps_category_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;

CREATE TABLE IF NOT EXISTS `inart_ps_category_associations` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `inart_ps_category_id` int(11) UNSIGNED NOT NULL,
  `categoryid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
    FOREIGN KEY (categoryid) REFERENCES `categories` (categoryid) ON DELETE CASCADE,
    FOREIGN KEY (inart_ps_category_id) REFERENCES `inart_ps_categories` (inart_ps_category_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;


Deadlock occur in a delete insert scenario

An example of how it can happen:

Two processes, each one has a shared lock on the same page, then an exclusive lock is needed by each process. Each process can't exclusively lock the page because of the shared row locks.

So, a deadlock happens on the same page.

More examples here from @AlexK


Badly-formed histogram causes bad estimates on Nested Loop

What can cause the badly-formed histogram?

This is a bug of course. It occurs when SQL Server scales the inexact histogram and encounters a unique column with apparently more than one value per key. This was partially addressed for SQL Server 2016, but it seems to me the full fix is only present on SQL Server 2017 (I tested on CU21).

Is there a way to solve this issue?

Use FULLSCAN statistics, the original CE, upgrade to SQL Server 2017, or wait for the full fix to be back-ported.
Demo
Using my standard Numbers table creation script:
DROP TABLE IF EXISTS dbo.Numbers;
GO
-- Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT
    -- Destination column type integer NOT NULL
    ISNULL(CONVERT(integer, N.n), 0) AS n
INTO dbo.Numbers
FROM Nums AS N
WHERE N.n >= 1
AND N.n <= 10 * 1000 * 1000
OPTION (MAXDOP 1);
GO
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);

Update the statistics to be sampled:
UPDATE STATISTICS dbo.Numbers;

Trivial query (request an estimated plan only):
SELECT *
FROM dbo.Numbers AS N1
WHERE EXISTS
(
    SELECT *
    FROM dbo.Numbers AS N2
    WHERE N2.n = N1.n
)
OPTION (QUERYTRACEON 3604, QUERYTRACEON 2363);

The trace output includes:
Begin selectivity computation

Input tree:

  LogOp_LeftSemiJoin
      CStCollBaseTable(ID=1, CARD=1e+007 TBL: dbo.Numbers AS TBL: N1)
      CStCollBaseTable(ID=2, CARD=1e+007 TBL: dbo.Numbers AS TBL: N2)
      ScaOp_Comp x_cmpEq
          ScaOp_Identifier QCOL: [N2].n
          ScaOp_Identifier QCOL: [N1].n

Plan for computation:
  CSelCalcExpressionComparedToExpression( QCOL: [N1].n x_cmpEq QCOL: [N2].n )

Loaded histogram for column QCOL: [N1].n from stats with id 1
    *** WARNING: badly-formed histogram ***

Selectivity: 1

Validated on Microsoft SQL Server 2016 (SP2-CU14).
The statistics histogram obtained via:
DBCC SHOW_STATISTICS
    (Numbers, [PK_Numbers_n])
    WITH HISTOGRAM;

has entries like:

on SQL Server 2017 the histogram is:

Note the EQ_ROWS steps are pre-scaled rather than relying on the CE to do it.


Business logic in stored proc vs middle layer (c#)

A) Scale  The middle tier can be scaled easily - hence the web farm concept. Scaling out the DB tier is much more difficult. While some products can do this it is not yet trivial and mainstream.

B) Cost Typically web servers are common-or-garden boxes. DB servers, however, tend to be larger, more complex and more resilient. This all translates to "expensive." A recent employer estimated a CPU tick on the DB was ten times more expensive than one on an application server.

C) Reuse Logic embodied in a stored procedure cannot simply be linked into, say, a stand-alone mobile app. Changing the SP affects every application which uses that DB, whether that application is ready for the change or not.

D) Reuse Logic embodied in a stored procedure is common to all applications which use the DB. Programmers cannot side-step the rules at a whim. Changing the SP affects every application which uses that DB, ensuring consistency across the enterprise.

E) Tooling There are more, and more sophisticated, languages, tools and techniques available for development in the application tier than there are in the database (from comments, with thanks).

F) Network traffic Typically a business function will require many reads and / or writes. Often the output of one statement will be the input to a following statement. If SQL statements are held in the application each will require a network round-trip to the server. If the SQL is held in a stored procedure there will be a single network trip, sending the parameters in and receiving only the final result back; the network cost of the intermediate results is avoided.


.NET executes a SQL query and Active Monitor shows multiple rows blocking each other

Multiple rows in activity monitor for the same SPID means your query has been chosen to be executed in parallel across multiple threads.

Each row on Activity Monitor actually represents one ECID, not one SPID.

Activity monitor only exposes the SPID column (session_id), but there is an additional column exposed in sys.sysprocesses called ECID (Execution Context ID) - this is a unique identifier for each thread the query is utilising.

The sysprocesses system view is deprecated, but you can find ECID by another name (exec_context_id) in the sys.dm_os_tasks view (as well as other task-related views).

Here is a sample query that captures all of the execution context IDs associated with queries running in a particular session, and what, if anything, they are waiting on:

SELECT
    dot.session_id,
    dot.exec_context_id,
    dot.task_state,
    dowt.wait_type,
    dowt.wait_duration_ms,
    dowt.blocking_session_id,
    dowt.resource_description
FROM sys.dm_os_tasks dot
    LEFT JOIN sys.dm_os_waiting_tasks dowt
        ON  dowt.exec_context_id = dot.exec_context_id
            AND dowt.session_id = dot.session_id
WHERE dot.session_id = 51
ORDER BY exec_context_id;

In SQL 2016 two new columns were added to sys.dm_exec_requests - DOP and parallel_worker_count - these can be used to check the whether the request is running in parallel or not.

Another key item to note is that the CXPACKET wait is inherently a parallelism wait - this by itself tells us the query is using more than one thread.

The PAGEIOLATCH_SH is a wait indicating those threads are reading data from disk into memory.


Postgres 11 added a feature where a table rewrite is not necessary if you add a column with a default value.

If you had used

alter table big_and_loaded_table
    add column col1 boolean default false;

the statement would have executed nearly instantaneous with the same effect.

But an ALTER TABLE statement will acquire an exclusive lock to prevent access to the table while it's being changed, there is no way around it.


At this point, I guess SQL Server with an AlwaysOn cluster will
  satisfy HA, but what about read/write?

I think this SQL Server 2012 whitepaper will be a good read for you at this point to get a feel for what's possible with the two different features (Availability Groups and Fail-over Clustered Instances) that come under the AlwaysOn brand name.

  Deploy a datacenter in every region you want and every database will
  contain the same information with read/write and HA capabilities

You ask about the read/write capabilities and I think this is where you will be disappointed. Even with a combination of local FCIs with a cross site AG it is only ever possible to have one node as the PRIMARY (writable) node. The other nodes can act as read-only nodes to allow you to off load reads and backups.

From my experience with building systems like this you really want to keep things as simple as possible. For this reason I wouldn't recommend trying to extend an AlwaysOn FCI + AG to more than 2 sites. I would also stay clear of bidirectional transnational replication as this has a tendency to break.

Without more information it is difficult to recommend a solution for you. Some very general topics to consider would be; a caching layer in your app at each site to help with the latency or a SQL Server Azure deployment.

After the update to the question:

I really wouldn't recommend you to use SQL Server in that way. Bidirectional replication between 2 nodes is bad enough. >2 nodes plus the cost of all those Enterprise Editions of SQL Server just sounds like a bad idea. Maybe take a look at what Azure has to offer.


Audit select queries against specified tables by specified users

I have found a way to prevent duplicate rows in FGA audit trail with parallel query

First create a function that return 1 if AUTHENTICATION_METHOD is PQ_SLAVE in USERENV context (coordinator process gets 0)

create or replace function is_pg_slave return integer as
begin
  if (sys_context('USERENV', 'AUTHENTICATION_METHOD') = 'PQ_SLAVE') then
    return 1;
  else
    return 0;
  end if;
end;
/

Then add policy to table

begin
      dbms_fga.add_policy(
        object_name       => 'table_name',
        policy_name       => 'noaudit_pq_slave',
        audit_condition   => 'is_pg_slave = 0',
        statement_types   => 'SELECT');
end;
/

Now only the query coordinator is audited, so no matter what the parallel degree is, only one row is inserted into fga audit trail.

I have not noticed any performance issues with this approach compared to normal auditing or no auditing.


You're not switching sources so there is nothing required to migrate from an A master to a B master.
To clear up the record of A being a master of B however the following may be desirable:

(B) STOP SLAVE will stop (B) from trying to get more binlog events
(B) RESET SLAVE ALL will make (B) forget about the (A) master.
(B) RESET MASTER is wrong. (B) is the master of (C). It was even before (A) is removed.


I think that your OLE DB source is causing the issue

Create a new table with the same schema as your current source and point the component at it.
Within the package, update to your actual source, as this will force it to handle it at run-time.  The easiest way I have found to do this is to create a variable and assign it to the source expression.  See an example of this at https://stackoverflow.com/questions/18288183/parameterized-oledb-source-query

Another possible solution is to set the DelayValidation property of the Data Flow task to True - See https://technet.microsoft.com/en-us/library/aa337088(v=sql.105).aspx#Anchor_4


How to keep smaller transaction logs files while rebuilding index of very large table

The most important advice I can give you is to test against a smaller table first. Don't just dive into converting a 37 billion row table. You can refine your method against a smaller table and use that to get an estimate on converting the large table. Also recovering from mistakes is far less costly (no more 24 hour rollback operations). You may also be able to use the smaller table to test query performance to verify that the conversion that you're doing is worth the effort.

The second most important advice is to get someone to check out your storage configuration. If you recently added a lot of storage it may not have been done in a good way to maximize throughput.

To be honest, it's not clear to me why you're seeing so much transaction log activity. Converting the CCI to a rowstore should be a minimally logged operation since you're using a recovery model of simple.

I'll walk through what I know about how SQL Server implements the following statement:

CREATE CLUSTERED INDEX [Analog_ColumnStoreIndex]
ON [dbo].[Analog]
(
    [LogTime] ASC,
    [CTDIID] ASC,
    [WindFarmId] ASC,
    [StationId] ASC
)
WITH (
    DROP_EXISTING = ON,
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON,
    ONLINE = OFF
)
ON [AnalogMonthlyPScheme]([LogTime]);

Before writing any rows SQL Server needs to sort the entire table's data by LogTime, CTDIID, WindFarmId, and StationId. The size of the sort will be massive. I suspect that it will be about the size of the uncompressed clustered rowstore table. It will probably spill over from tempdb to disk. Perhaps that part of the operation is not minimally logged? I can't really guess as to why else you are seeing so much log activity. Note that sorting on LogTime would be sufficient for your conversion and might make the sort cheaper but I don't think it would reduce the workspace required for the sort.

Along with the sort the data will be written to a new table. The old CCI table will be dropped after the new table is finished. For your biggest table you estimate that this will be 20 TB. It could take a long time to write 20 TB of data.

If you want to convert your data avoiding huge sorts and huge transactions it should be possible to do, but messy. Before going into this I need to point out that if your CCI is already partitioned the right thing to do is to use partition switching to better organize your rowgroups. Based on your question it seems like tables aren't partitioned yet but I want to be sure. I also have not tested any of the following.

Create a partitioned rowstore heap or a partitioned view. The partition scheme of these needs to match your future CCI partition scheme. Delete the TOP N rows from the CCI into the partitioned heap using the TABLOCK hint. That should get you minimal logging for the insert. You will not get minimal logging for the delete against the CCI. However, a delete against a CCI is just changing values in a bitmap so it should require far less logging than a standard delete.

The above insert should require a sort for the heap and will probably require a sort for the partitioned view as well. However, since you control the value for N you can limit the size of the sort to fit it within memory + tempdb.

Note that as you continue to do loops your query will probably take longer and longer to read data from the CCI. This is because it needs to scan past the deleted rows. Periodically you should issue a REORGANIZE against the CCI to clean up those deleted rows.

After the loops are finished you'll have all of your data in the correct partitions but unsorted. At this point for each partition or table you can partition switch into a heap, build the rowstore clustered index on the table, build the CCI with DROP_EXISTING = ON and MAXDOP 1, and finally partition switch into the new CCI table. After you are done with all of the partition switching you should have a partitioned CCI with excellent rowgroup elimination by LogTime.

Two notes about loading new data into a partitioned CCI. If you want perfectly ordered, full rowgroups you can't use parallel inserts. That's because the parallel reads of the source data will lead to unevenly distributed rows on threads and each thread will load to a different rowgroup. Also, plan your partitions carefully so that you don't need to split a partition with data. You can't directly SPLIT a partition without dropping the CCI which you obviously don't want to do.


Cannot get a message to go to a SQL Server Service Broker Queue

Look at the transmission_status column in sys.transmission_queue in your database:

use <testdb>;
go

select transmission_status, *
from sys.transmission_queue;
go

The status will tell what the issue is.


postgresql, select sum of rows for multiple ids

As you can see, i remodelled your query(only with two to get you an idea how to do it
W
What you make you make for every SELECT that you have a seperate subquery and join them with cs.id, as you only want certain ids

SELECT *
FROM
    (SELECT cs.id,SUM(du.total) AS daily_total
            FROM daily_unit_big_int as du
            INNER JOIN measuring_point as mp on mp.id = du.measuring_point_id
            INNER JOIN consumption_sector as cs on cs.id = mp.consumption_sector_id
            WHERE
            mp.type='devices_logger' AND -- an enumerate
            cs.id in (1,2) AND
            du.day = '2020-01-01' -- a date I'd like to give as parameter/variable
     GROUP BY cs.id) dayly
     INNER JOIN
     (SELECT cs.id,SUM(total) monthly
        FROM daily_unit_big_int du
        INNER JOIN measuring_point mp ON mp.id = du.measuring_point_id
        INNER JOIN consumption_sector cs ON cs.id = mp.consumption_sector_id
        WHERE
        mp.type='devices_logger' AND
        cs.id in (1,2) AND
        EXTRACT(MONTH FROM du.day) = '01' AND
        EXTRACT(YEAR FROM du.day) = '2020'
        GROUP BY cs.id
        ) monthly ON dayly.id = monthly.id

id | daily_total | id | monthly
-: | ----------: | -: | ------:
 1 |        1555 |  1 |   31665
 2 |        2000 |  2 |    6000

db<>fiddle here


The problem was that when the database was installed, conflicting other parameters were set. So I needed to export those from the "spfile" to a "pfile", back them up, edit them, test them and import from the pfile to the spfile:

SQL> create pfile='some/file/path' from spfile;

File created.

Then I set the conflicting parameters to zero:

...
--*.pga_aggregate_target=3270m
*.pga_aggregate_target=0m
...
--*.sga_target=9811m
*.sga_target=0m
...

I then try starting up again:

SQL> startup pfile='some/file/path';

ORACLE instance started.
...

And check the memory_target and memory_max_target parameters as above. Once happy with things, to keep the settings I did:

SQL> create spfile from pfile='some/file/path';

File created.


MySQL function: Get string/number separated by some place holder

For this solution I've opted for the 'easy' way out ... a user-defined function ...

Proposed function:

drop function if exists myinsert;

create function myinsert (instring char(100), inchar char(10))
returns char(100)
deterministic
begin
  declare i int;
  set i = char_length(instring);
  while i > 1 do
    set instring = insert(instring,i,0,inchar);
    set i = i - 1;
  end while;
return instring;
end;

set i = char_length(instring) : get length of input string
while i/set i : loop through values of i down to '2'
insert(instring,i,0,inchar) : at position i in our string, insert inchar
at this point we've stepped backwards through instring, inserting the inschar string before every character in instring (except for the first character)

Calling the function:

select myinsert(cast(product_id as char),'/') as new_string
from   product
where  sku = 'abc';

new_string
-----------
1/4/5/6/8/7

select myinsert(cast(product_id as char),'-/-') as new_string
from   product
where  sku = 'abc';

new_string
---------------------
1-/-4-/-5-/-6-/-8-/-7

Here are rextester and a db-fiddle fiddles


Converting Heap to MyISAM issue on temp table defined as innodb

"Temporary table" is an overloaded term.

CREATE TEMPORARY TABLE - a table you create and manage.
An internal temp table used in a big, complex, SELECT.

The latter case causes the message "converting heap to MyISAM".  Here's the flow:

The SELECT creates a temp table (for subquery, GROUP BY, etc),
starts it as MEMORY, then
finds that it is too big ("a few hundred thousand rows" -- bigger than both tmp_table_size and max_heap_table_size).

It is normal.  It should usually be ignored.  The temp table was never InnoDB, so no conversion there happened.  (In 8.0, tmp tables will be InnoDB, not MyISAM or MEMORY.)

If you would like to show us the query, together with SHOW CREATE TABLE and EXPLAIN SELECT, we may have tips on improving it.

If you create your own large MEMORY table, you are taking RAM away from other things (eg, innodb_buffer_pool_size), thereby slowing down the system in general.

Do not try to simulate internal temp tables in InnoDB.  There is too much baggage you can't avoid, but it can.  (Redo log, undo log, transactional semantics, etc.)


How can I restore the sql_variant_property of baseType back to a table variable in SQL Server

A lot of problems with what you are trying that I just don't think this particular approach is going to work (I think the goal is interesting):

returns @result table (reportID bigint) - this is basically schema-bound.  You can't alter the schema of the returned table - I see later you are going to attempt to "ALTER" this variable - that's just not going to happen.

Even if you could, this part:

select @alterTableStr = 'alter table @result add '+ convert(varchar(2000),@dataLabel)+' '+ convert(varchar(2000),@dataType) ;

exec sp_executesql @alterTableStr;

isn't going to affect the @result in the outer part - these dynamic SQL parts have their own scope and can't get to the variables in the calling part this way

And then it really comes down to the error you are currently getting - which is sp_executesql can't be run inside a function anyway.

Anyway, to aid you a little in making progress, may I suggest you read the following two articles which are are tangentially related (and I have used recently) regarding parsing CSV and JSON data.  Both use his hierarchy idea (an unpivoted name/value thing) and repivoting and you might find some useful techniques there:

http://www.simple-talk.com/sql/t-sql-programming/the-tsql-of-csv-comma-delimited-of-errors/

http://www.simple-talk.com/sql/t-sql-programming/consuming-json-strings-in-sql-server/

If you can use the CLR, this article which was published might also be useful:

http://www.sqlservercentral.com/articles/.Net/94922/


FOR XML is generating a empty first line

After a bit of trial and error, I found this is caused by the USE statement occuring after XML mode is turned on. I have no idea why; I assume it's simply an artifact of sqlcmd processing.

Here are three solutions I came up with from playing around with the code:

Remove the USE completely, and specify the database name on the command line using the -d parameter instead.

:XML ON

SET NOCOUNT ON

SELECT * FROM ProgramacionDia as programa order by hora
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS

SET NOCOUNT OFF

Remove the USE completely, and three-part-name the table.

:XML ON

SET NOCOUNT ON

SELECT * FROM [MyDatabaseName]..[ProgramacionDia] as programa order by hora
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS

SET NOCOUNT OFF

Move the USE statement above the :XML ON directive, and delete the GO (this will suppress the "Changed database context to 'MyDatabaseName'" message in the output). I have no idea why this also fixes the issue.

USE MyDatabaseName

:XML ON

SET NOCOUNT ON

SELECT * FROM ProgramacionDia as programa order by hora
    FOR XML AUTO, ROOT ('toflash'), ELEMENTS

SET NOCOUNT OFF

Depending on what your actual script looks like, one of these options should be adaptable for you.


Schedule Maintenance Plan - Request For a Good Sequence

It looks like your goal is to "chain" certain jobs: to run an integrity check, then an index optimization, then a backup.

First - it may make sense to take a backup before the check and index optimization. If something were to go horribly wrong during those steps, you could get back to the start to try to resolve things. If you take differential backups of this DB, you might want to take a final diff backup before, and a full backup after.

Second, the chaining seems reasonable in general. However, until you've run the jobs, you have no idea how long they'll take. If there's a chance that the DB check and the index optimization could take longer than an hour, I'd space things out more. Overlapping these things isn't necessarily a good idea.

If the tightest possible chaining is desirable, then move the job steps into a single job, and order them as desired. Or, as suggested by @AaronBertrand, only set and actual schedule for the first job; have the first job kick off the second as its final step, the second job kick off the third, and so forth.


Why is a Shared lock blocking on an IX lock when Read Committed Snapshot is enabled?

Read Committed Snapshot don't affect the lock compatibility matrix.
For reads it means that shared locks aren't acquired, and reads will use row versioning instead. Assuming you are using read committed, that is, since this is the only isolation level that is "reconfigured" by the Read Committed Snapshot database knob.
You evidently have shared locks, and such will block/be blocked by IX locks, according to the log compatibility matrix at https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms186396(v=sql.105)?redirectedfrom=MSDN.
So the question for you to investigate is why you have shared locks. Perhaps some parts of your app don't use Read Committed?
Paul mentions in a different thread that S locks are used for FK validations when you modify data in RCSI, which can be an explanation for the S locks:
Shared locks taken under Read Committed Snapshot Isolation (RCSI)


I think you want coalesce outside the subquery expression.

SELECT 1000 < coalesce((SELECT numOfItems FROM inventory ORDER BY inventoryDate DESC LIMIT 1), 0);


Database Design - hierarchical structure

This could be a possible model, depending on the length of MyString, you could replace surrogated keys on B and C by this column.

CREATE TABLE B
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [MyString] nvarchar(500),

    CONSTRAINT [PK_B] PRIMARY KEY ([ID])
);

CREATE TABLE C
(
    [ID] int IDENTITY(1,1) NOT NULL,
    [MyString] nvarchar(500),
    [MyDouble] decimal(18,2) DEFAULT(0),
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_C] PRIMARY KEY ([ID]),
    CONSTRAINT [FK_C_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B([ID])
);

CREATE TABLE A
(
    [ID] int NOT NULL,
    [C_ID] int NOT NULL,

    CONSTRAINT [PK_A] PRIMARY KEY ([ID]),
    CONSTRAINT [FK_A_C_ID] FOREIGN KEY ([C_ID]) REFERENCES C ([ID])
);

LIST/ARRAYS

CREATE TABLE A_B_LIST
(
    [A_ID] int NOT NULL,
    [Index] int NOT NULL, /* Index of array */
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_A_B_LIST] PRIMARY KEY ([A_ID], [Index]),
    CONSTRAINT [FK_A_B_LIST_A_ID] FOREIGN KEY ([A_ID]) REFERENCES A ([ID]),
    CONSTRAINT [FK_A_B_LIST_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B ([ID]),
);

CREATE TABLE B_NUM
(
    [B_ID] int NOT NULL,
    [Index] int NOT NULL,
    [MyNumber] decimal(18,2),

    CONSTRAINT [PK_B_NUM] PRIMARY KEY ([B_ID], [Index]),
    CONSTRAINT [FK_B_NUM_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B([ID])
);

CREATE TABLE C_B_LIST
(
    [C_ID] int NOT NULL,
    [Index] int NOT NULL,
    [B_ID] int NOT NULL,

    CONSTRAINT [PK_C_B_LIST] PRIMARY KEY ([C_ID], [Index]),
    CONSTRAINT [FK_C_B_LIST_C_ID] FOREIGN KEY ([C_ID]) REFERENCES C ([ID]),
    CONSTRAINT [FK_C_B_LIST_B_ID] FOREIGN KEY ([B_ID]) REFERENCES B ([ID])
);

db<>fiddle here


Sum purchased & sold quantities for all items on daily/weekly

i need to display purchased & sold quantities for all items and for all days of a date range.

According to this I applied Cartesian product between dimDates and Items using OUTER APPLY, that should return a combination of all dates and all items.

Then I've used two subqueries in the SELECT but you could LEFT JOIN Purchases and Sales tables.

SELECT d.[Date],
       i.Product_Code,
       i.Product_Name,
       COALESCE((SELECT SUM(Purchased_Qty)
                 FROM   dbo.Purchase
                 WHERE  Product_Code = i.Product_Code
                 AND    Purchase_Date = d.Date), 0) Purchased,
       COALESCE((SELECT SUM(Sale_Qty)
                 FROM   dbo.Sales
                 WHERE  Product_Code = i.Product_Code
                 AND    Sale_Date = d.Date), 0) Saled
FROM   [dbo].[DimDate] d
OUTER APPLY (
            SELECT Product_Code, Product_Name
            FROM   dbo.Items
            ) i
WHERE  d.[Date] >= '20180115'
AND    d.[Date] <= '20180116';
GO

Date                | Product_Code | Product_Name | Purchased | Saled
:------------------ | :----------- | :----------- | :-------- | :----
15/01/2018 00:00:00 | P1           | ProductA     | 26        | 20
15/01/2018 00:00:00 | P2           | ProductB     | 5         | 20
15/01/2018 00:00:00 | P3           | ProductC     | 20        | 40
15/01/2018 00:00:00 | P4           | ProductD     | 0         | 0
15/01/2018 00:00:00 | P5           | ProductE     | 0         | 0
16/01/2018 00:00:00 | P1           | ProductA     | 15        | 21
16/01/2018 00:00:00 | P2           | ProductB     | 37        | 22
16/01/2018 00:00:00 | P3           | ProductC     | 23        | 3
16/01/2018 00:00:00 | P4           | ProductD     | 0         | 0
16/01/2018 00:00:00 | P5           | ProductE     | 0         | 0

DECLARE @Start_Date date = '20180115';
DECLARE @End_Date date = '20180116';

SELECT d.[Date],
       i.Product_Code,
       i.Product_Name,
       COALESCE(p.Purchased_Qty, 0) Purchased_Qty,
       COALESCE(s.Sale_Qty, 0) Sale_Qty
FROM   [dbo].[DimDate] d
OUTER APPLY (
            SELECT Product_Code, Product_Name
            FROM   dbo.Items
            ) i
LEFT JOIN (SELECT Product_Code, Purchase_Date, SUM(Purchased_Qty) Purchased_Qty
           FROM   dbo.Purchase
           WHERE  Purchase_Date >= @Start_Date
           AND    Purchase_Date <= @End_Date
           GROUP BY Product_Code, Purchase_Date) p
ON p.Product_Code = i.Product_Code
AND p.Purchase_Date = d.[Date]
LEFT JOIN (SELECT Product_Code, Sale_Date, SUM(Sale_Qty) Sale_Qty
           FROM   dbo.Sales
           WHERE  Sale_Date >= @Start_Date
           AND    Sale_Date <= @End_Date
           GROUP BY Product_Code, Sale_Date) s
ON s.Product_Code = i.Product_Code
AND s.Sale_Date = d.[Date]
WHERE  d.[Date] >= @Start_Date
AND    d.[Date] <= @End_Date
ORDER BY d.[Date], i.Product_Code;
GO

Date                | Product_Code | Product_Name | Purchased_Qty | Sale_Qty
:------------------ | :----------- | :----------- | :------------ | :-------
15/01/2018 00:00:00 | P1           | ProductA     | 26            | 20
15/01/2018 00:00:00 | P2           | ProductB     | 5             | 20
15/01/2018 00:00:00 | P3           | ProductC     | 20            | 40
15/01/2018 00:00:00 | P4           | ProductD     | 0             | 0
15/01/2018 00:00:00 | P5           | ProductE     | 0             | 0
16/01/2018 00:00:00 | P1           | ProductA     | 15            | 21
16/01/2018 00:00:00 | P2           | ProductB     | 37            | 22
16/01/2018 00:00:00 | P3           | ProductC     | 23            | 3
16/01/2018 00:00:00 | P4           | ProductD     | 0             | 0
16/01/2018 00:00:00 | P5           | ProductE     | 0             | 0

dbfiddle here


PostgreSQL remove numbers before first comma

That looks like an array. If that is the case, you can remove the first element of the array using an UPDATE statement:

update the_table
  scrap_value = scrap_value[2:];

[2:] selects all elements of the array starting with the second. The result of that is then used to override the existing array.


Does this mean the 'public' has full select, insert, update, and delete access?

This means that if the user has not had permissions explicitly set for the securable (or inherited from permissions explicitly set) that public has permissions for, then those will be applied.  In other words, if you have User1 with DELETE permissions denied on User_Table, then User1 will not be able to delete data from that table.

Please see BOL's reference on Database-Level Roles:

  public Database Role
  Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.

Example

use TestDB;
go

create login Login1
with
    password = 'password',
    check_policy = off;
go

create user User1
for login Login1;
go

-- this will return 1 (meaning, yes it is a role member)
select is_rolemember('public', 'User1');

-- let's do a test to show the above theory
create table SomeTable
(
    id int identity(1, 1) not null,
    SomeText varchar(30) not null
        default replicate('a', 30)
);
go

insert into SomeTable
values(default);
go 10

-- give the public role permissions to SELECT on SomeTable
grant select
on SomeTable
to public;
go

-- this will be successful, because User1 is part of public
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go

-- create a new role to deny SELECT on SomeTable
exec sp_addrole 'Role1';
go

deny select
on SomeTable
to Role1;
go

-- add User1 to this new role
exec sp_addrolemember 'Role1', 'User1';
go

-- this will not be successful because User1 now has been denied SELECT on SomeTable
execute as user = 'User1';
go

select *
from SomeTable;

revert;
go


MySQL does not start - Ubuntu 14.04 LTS after migration to new disk

You only changed the ownership and permissions for the mysql datadir, not all the files below it. You should apply those changes recursively with the -R option.

I would do it this way:

$ sudo chown -R mysql:mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

$ sudo chmod -R g+rwX /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

The datadir doesn't need to be other-accessible.

Also make sure that the full chain of directories above (/media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/) are readable & executable by mysql:mysql.


How can I find out what stats objects were used during a SQL Server 2014 query?

Yes, it’s flag 2363! Flag 3604 is just for redirecting the debugging output to the Messages window, so that stays the same in 2014.

SQL Server 2012

SELECT * FROM Test
WHERE Pred1 = 1 AND Pred2 = 2
OPTION
(
QUERYTRACEON 3604,
QUERYTRACEON 9292,
QUERYTRACEON 9204
)

SQL Server 2014

SELECT * FROM Test
WHERE Pred1 = 1 AND Pred2 = 2
OPTION
(
QUERYTRACEON 3604,
QUERYTRACEON 2363
)

Source: the SQL Server 2014 Selectivity Trace Flags section on this link.


incorrect Estimated Number of Rows vs Actual number of rows

If you include cd_key in your index, it won’t have to do those lookups and the estimates will have less of an impact. Otherwise, correcting that estimate is going to cause it to do a Clustered Index Scan, which could also be slow. cd_camp is already in there as it’s part of the clustered index key.

But really, the index you want here is:

CREATE CLUSTERED INDEX ixYearCamp ON campdiv (cd_campyr, cd_camp) include (cd_key);


Methods for finding new Trace Flags in SQL Server

There's nothing you can do to find the list except ask or pick them up from posts/slide decks/etc. The list only exists in the code, in a header file where the valid trace flag numbers are mapped to names in a big enum in the C++ code, and then the names are used in the rest of the code.

As Aaron said, you can enable any trace flag number, and if it doesn't do anything, or you don't exercise the functionality that the trace flag is relevant to, you won't notice any difference in behavior.

DBCC TRACEON doesn't check anything - as there's no run-time list of which numbers are valid or not - it just enables that trace flag number in a bitmap of what flags are set for that connection/globally.

The problem with having a validity check is that would expose which trace flags are valid, allowing them to be discovered. This way the 'valid list' is effectively obfuscated, which is what the SQL team wants.

Regarding Kin's suggestion in a comment that SQL Server should have select * from sys.available_trace_flags - Yes and no. There are many trace flags that are highly detrimental to performance and are only necessary for debugging problems under guidance from Product Support, but SQL Server could list the 'safe' flags.


My MySQL knowledge is rusty (at best), but subqueries like this can normally be replaced by a JOIN and GROUP BY to achieve the desired result.

Something like this will get you on your way:

SELECT  name,
        qty_ordered,
        item_id,
        original_price,
        discount_percent,
        price,
        tax_percent,
        MAX(CASE WHEN mea.attribute_code = 'cip' THEN mcpev.value END) AS cip,
        MAX(CASE WHEN mea.attribute_code = 'cip7' THEN mcpev.value END) AS cip7,
        MAX(CASE WHEN mea.attribute_code = 'gtin_13' THEN mcpev.value END) AS gtin13
FROM    magento_sales_flat_order_item msfoi
INNER JOIN magento_catalog_product_entity_varchar mcpev
  ON msfoi.product_id = mcpev.entity_id
INNER JOIN magento_eav_attribute mea
  ON mcpev.attribute_id = mea.attribute_id
WHERE   order_id = 1
GROUP BY name, qty_ordered, item_id, original_price, discount_percent, price, tax_percent


Difference between SQL batch, statement and RPC?

Well, I suppose you're talking mostly about the Profiler classes, but the explanation stands anyway.

An SQL batch is a set of one or more statements grouped together and separated by a GO statement. EG: more SELECT and INSERT statements form a batch if they have a GO at the end.

A RPC call is a call that comes from a client application to the database. EG: a windows service, a web application, a windows app, whatever needs a connection to the database actually makes a RPC call.

Now, in Profiler you'll see everything that touches the database server.
A batch from Management Studio, an RPC call (which is either a batch or a stored procedure call) from an external application, a procedure execution from Management Studio.

Each of them is formed of TSQL statements, so this Profiler class is useful in case you want to expand the execution further, to see what's actually executed. What inserts, selects..etc.

The easiest way to look at them in Profiler is to enable only End RPC call, or End batch call and you'll see there all the statistics needed (duration, IO, CPU). Then, move further by enabling TSQL Statements class and dig deeper.


xml queries with exist and contains giving unexpected results

You have not specified the length of @id. Using only varchar will in this case give you a length of one so the content of @id will be x.

Your first query checks for nodes that contain x and that matches all rows. The second query checks for nodes that has x as value and no row has that.

Change the declaration of @id to DECLARE @id varchar(3) and everything will work as expected.


Troubleshooting oracle database errors from cloud application

ORA-01000: maximum open cursors exceeded is almost always an application error.  I'll put a large wager down that some part of the Java middle tier is failing to close a ResultSet in every case.  That causes it to leak cursors and will eventually cause this error.  You can increase the max_open_cursors setting in Oracle so that the cursor leak hits the limit more slowly.  You may want to combine that with more aggressive restarts of the middle tier and/or recycling of the connection pool as a temporary measure.  But the right answer would be to figure out where the application is leaking cursors and plug the leak.

Do you have any additional logging around the unable to create a connection error?  It is possible that you're hitting the maximum number of connections specified by your sessions setting (and the processes setting on which it depends).  If you really need to support more connections, you can always bump that up or move some of the connections over to shared server connections if they're using dedicated server connections today.  But I'd be concerned given the cursor error that the middle tier is also leaking connections.


Do SQL OS Background Tasks Yield to Normal Tasks?

The background tasks are there for a purpose & are generally necessary so everything else being equal I wouldn't try and turn them off. I believe that some of them at least do act as you describe and reduce their pressure when CPU load is high - ie Query Store.
A detailed answer to this question would be level 500 internals and extremely long - probably better to wait until your CPU is stuck with a way higher load than 22%. There is undoubtedly lower hanging fruit to optimize


I was left wondering because you mentioned SSH which implied connecting from your database server to a different machine (free tip: You use "SSH" to connect to a machine, but the things you type after that are "shell commands", not "SSH commands."  I googled that phrase just now and was horrified at how frequently they're improperly called "SSH commands") ... and in Sqlbot's world, a "slave" is a different machine -- a replication slave -- which also was initially confusing.

You mentioned MYD and MYI files but not IBD files, so either you are using MyISAM and not InnoDB, or you're using a mix but you never set innodb_file_per_table = 1.

Your path to recovery depends on whether you are using InnoDB.

If you're not using InnoDB, then you can -- believe it or not -- just copy the old database directories and files into place and fix the permissions if needed.  MySQL is extremely forgiving about this with MyISAM tables.

Connect to your MySQL server and locate the data directory.  From an old CentOS machine of mine (probably still the same for you):

mysql> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

Find the comparable directory on your old hard drive, and copy all of the directories -- but not the files, and not the "mysql" directory -- into your datadir (probably /var/lib/mysql).

Then, if the files aren't owned by the "mysql" user,

root@host# chown -R mysql:mysql /var/lib/mysql

Then see if it worked.

mysql> SHOW DATABASES;

If you are using MyISAM only, your databases and tables should be on the new server, though you will need to do CHECK TABLES on all of your tables or use the mysqlcheck utility to verify that they are all intact and repair them as needed.

If you're using InnoDB, the steps will be different, so see if this works for you.


Make sure MySQL is not accepting remote connections

Do:

netstat -an|grep 3306 | grep LISTEN

If something similar to the following line is returned:

tcp        0      0 0.0.0.0:3306                  0.0.0.0:*                   LISTEN

.. it means that it's listening on all interfaces.

If something similar to the following line is returned, and no other lines:

tcp        0      0 127.0.0.1:3306               0.0.0.0:*                   LISTEN

.. it's already configured to only listen on localhost.

If there are lines with other IP addresses before the :3306, it means that it's  listening on those interfaces.

To change MySQL to only listen on localhost, edit your configuration file (usually /etc/my.cnf), add the following:

bind-address = 127.0.0.1

Restart the service and voila!


SQL Server 2019: Rasing errors between 1 and 13000

Try RAISERROR syntax below;
RAISERROR(
          'Test error' -- Error message
          ,0 -- Severity
          ,1 -- State
         ) WITH LOG;

For more information, you can check out the document.


Date range rolling sum using window functions

Great question, Paul! I used a couple different approaches, one in T-SQL and one in CLR.
T-SQL quick summary
The T-SQL approach can be summarized as the following steps:

Take the cross-product of products/dates
Merge in the observed sales data
Aggregate that data to the product/date level
Compute rolling sums over the past 45 days based on this aggregate data (which contains any "missing" days filled in)
Filter those results to only the product/date pairings that had one or more sales

Using SET STATISTICS IO ON, this approach reports Table 'TransactionHistory'. Scan count 1, logical reads 484, which confirms the "single pass" over the table.  For reference, the original loop-seek query reports Table 'TransactionHistory'. Scan count 113444, logical reads 438366.
As reported by SET STATISTICS TIME ON, the CPU time is 514ms.  This compares favorably to 2231ms for the original query.
CLR quick summary
The CLR summary can be summarized as the following steps:

Read the data into memory, ordered by product and date
While processing each transaction, add to a running total of the costs. Whenever a transaction is a different product than the previous transaction, reset the running total to 0.
Maintain a pointer to the first transaction that has the same (product, date) as the current transaction.  Whenever the last transaction with that (product, date) is encountered, compute the rolling sum for that transaction and apply it to all transactions with the same (product, date)
Return all of the results to the user!

Using SET STATISTICS IO ON, this approach reports that no logical I/O has occurred!  Wow, a perfect solution!  (Actually, it seems that SET STATISTICS IO does not report I/O incurred within CLR.  But from the code, it is easy to see that exactly one scan of the table is made and retrieves the data in order by the index Paul suggested.
As reported by SET STATISTICS TIME ON, the CPU time is now 187ms.  So this is quite an improvement over the T-SQL approach.  Unfortunately, the overall elapsed time of both approaches is very similar at about half a second each.  However, the CLR based approach does have to output 113K rows to the console (vs. just 52K for the T-SQL approach that groups by product/date), so that's why I've focused on CPU time instead.
Another big advantage of this approach is that it yields exactly the same results as the original loop/seek approach, including a row for every transaction even in cases where a product is sold multiple times on the same day.  (On AdventureWorks, I specifically compared row-by-row results and confirmed that they tie out with Paul's original query.)
A disadvantage of this approach, at least in its current form, is that it reads all data in memory.  However, the algorithm that has been designed only strictly needs the current window frame in memory at any given time and could be updated to work for data sets that exceed memory.  Paul has illustrated this point in his answer by producing an implementation of this algorithm that stores only the sliding window in memory.  This comes at the expense of granting higher permissions to CLR assembly, but would definitely be worthwhile in scaling this solution up to arbitrarily large data sets.

T-SQL - one scan, grouped by date
Initial setup
USE AdventureWorks2012
GO
-- Create Paul's index
CREATE UNIQUE INDEX i
ON Production.TransactionHistory (ProductID, TransactionDate, ReferenceOrderID)
INCLUDE (ActualCost);
GO
-- Build calendar table for 2000 ~ 2020
CREATE TABLE dbo.calendar (d DATETIME NOT NULL CONSTRAINT PK_calendar PRIMARY KEY)
GO
DECLARE @d DATETIME = '1/1/2000'
WHILE (@d < '1/1/2021')
BEGIN
    INSERT INTO dbo.calendar (d) VALUES (@d)
    SELECT @d =  DATEADD(DAY, 1, @d)
END
GO

The query
DECLARE @minAnalysisDate DATE = '2007-09-01', -- Customizable start date depending on business needs
        @maxAnalysisDate DATE = '2008-09-03'  -- Customizable end date depending on business needs
SELECT ProductID, TransactionDate, ActualCost, RollingSum45, NumOrders
FROM (
    SELECT ProductID, TransactionDate, NumOrders, ActualCost,
        SUM(ActualCost) OVER (
                PARTITION BY ProductId ORDER BY TransactionDate
                ROWS BETWEEN 45 PRECEDING AND CURRENT ROW
            ) AS RollingSum45
    FROM (
        -- The full cross-product of products and dates, combined with actual cost information for that product/date
        SELECT p.ProductID, c.d AS TransactionDate,
            COUNT(TH.ProductId) AS NumOrders, SUM(TH.ActualCost) AS ActualCost
        FROM Production.Product p
        JOIN dbo.calendar c
            ON c.d BETWEEN @minAnalysisDate AND @maxAnalysisDate
        LEFT OUTER JOIN Production.TransactionHistory TH
            ON TH.ProductId = p.productId
            AND TH.TransactionDate = c.d
        GROUP BY P.ProductID, c.d
    ) aggsByDay
) rollingSums
WHERE NumOrders > 0
ORDER BY ProductID, TransactionDate
-- MAXDOP 1 to avoid parallel scan inflating the scan count
OPTION (MAXDOP 1)

The execution plan
From the execution plan, we see that the original index proposed by Paul is sufficient to allow us to perform a single ordered scan of Production.TransactionHistory, using a merge join to combine the transaction history with each possible product/date combination.

Assumptions
There are a few significant assumptions baked into this approach.  I suppose it will be up to Paul to decide whether they are acceptable :)

I am using the Production.Product table.  This table is freely available on AdventureWorks2012 and the relationship is enforced by a foreign key from Production.TransactionHistory, so I interpreted this as fair game.
This approach relies on the fact that transactions do not have a time component on AdventureWorks2012; if they did, generating the full set of product/date combinations would no longer be possible without first taking a pass over the transaction history.
I am producing a rowset that contains just one row per product/date pair.  I think that this is "arguably correct" and in many cases a more desirable result to return.  For each product/date, I have added a NumOrders column to indicate how many sales occurred.  See the following screenshot for a comparison of the results of the original query vs. the proposed query in cases where a product was sold multiple times on the same date (e.g., 319 / 2007-09-05 00:00:00.000)

CLR - one scan, full ungrouped result set
The main function body
There isn't a ton to see here; the main body of the function declares the inputs (which must match the corresponding SQL function), sets up a SQL connection, and opens the SQLReader.
// SQL CLR function for rolling SUMs on AdventureWorks2012.Production.TransactionHistory
[SqlFunction(DataAccess = DataAccessKind.Read,
    FillRowMethodName = "RollingSum_Fill",
    TableDefinition = "ProductId INT, TransactionDate DATETIME, ReferenceOrderID INT," +
                      "ActualCost FLOAT, PrevCumulativeSum FLOAT, RollingSum FLOAT")]
public static IEnumerable RollingSumTvf(SqlInt32 rollingPeriodDays) {
    using (var connection = new SqlConnection("context connection=true;")) {
        connection.Open();
        List<TrxnRollingSum> trxns;
        using (var cmd = connection.CreateCommand()) {
            //Read the transaction history (note: the order is important!)
            cmd.CommandText = @"SELECT ProductId, TransactionDate, ReferenceOrderID,
                                    CAST(ActualCost AS FLOAT) AS ActualCost
                                FROM Production.TransactionHistory
                                ORDER BY ProductId, TransactionDate";
            using (var reader = cmd.ExecuteReader()) {
                trxns = ComputeRollingSums(reader, rollingPeriodDays.Value);
            }
        }

        return trxns;
    }
}

The core logic
I've separated out the main logic so that's easier to focus on:
// Given a SqlReader with transaction history data, computes / returns the rolling sums
private static List<TrxnRollingSum> ComputeRollingSums(SqlDataReader reader,
                                                        int rollingPeriodDays) {
    var startIndexOfRollingPeriod = 0;
    var rollingSumIndex = 0;
    var trxns = new List<TrxnRollingSum>();

    // Prior to the loop, initialize "next" to be the first transaction
    var nextTrxn = GetNextTrxn(reader, null);
    while (nextTrxn != null)
    {
        var currTrxn = nextTrxn;
        nextTrxn = GetNextTrxn(reader, currTrxn);
        trxns.Add(currTrxn);

        // If the next transaction is not the same product/date as the current
        // transaction, we can finalize the rolling sum for the current transaction
        // and all previous transactions for the same product/date
        var finalizeRollingSum = nextTrxn == null || (nextTrxn != null &&
                                (currTrxn.ProductId != nextTrxn.ProductId ||
                                currTrxn.TransactionDate != nextTrxn.TransactionDate));
        if (finalizeRollingSum)
        {
            // Advance the pointer to the first transaction (for the same product)
            // that occurs within the rolling period
            while (startIndexOfRollingPeriod < trxns.Count
                && trxns[startIndexOfRollingPeriod].TransactionDate <
                    currTrxn.TransactionDate.AddDays(-1 * rollingPeriodDays))
            {
                startIndexOfRollingPeriod++;
            }

            // Compute the rolling sum as the cumulative sum (for this product),
            // minus the cumulative sum for prior to the beginning of the rolling window
            var sumPriorToWindow = trxns[startIndexOfRollingPeriod].PrevSum;
            var rollingSum = currTrxn.ActualCost + currTrxn.PrevSum - sumPriorToWindow;
            // Fill in the rolling sum for all transactions sharing this product/date
            while (rollingSumIndex < trxns.Count)
            {
                trxns[rollingSumIndex++].RollingSum = rollingSum;
            }
        }

        // If this is the last transaction for this product, reset the rolling period
        if (nextTrxn != null && currTrxn.ProductId != nextTrxn.ProductId)
        {
            startIndexOfRollingPeriod = trxns.Count;
        }
    }

    return trxns;
}

Helpers
The following logic could be written inline, but it's a little easier to read when they are split out into their own methods.
private static TrxnRollingSum GetNextTrxn(SqlDataReader r, TrxnRollingSum currTrxn) {
    TrxnRollingSum nextTrxn = null;
    if (r.Read()) {
        nextTrxn = new TrxnRollingSum {
            ProductId = r.GetInt32(0),
            TransactionDate = r.GetDateTime(1),
            ReferenceOrderId = r.GetInt32(2),
            ActualCost = r.GetDouble(3),
            PrevSum = 0 };
        if (currTrxn != null) {
            nextTrxn.PrevSum = (nextTrxn.ProductId == currTrxn.ProductId)
                    ? currTrxn.PrevSum + currTrxn.ActualCost : 0;
        }
    }
    return nextTrxn;
}

// Represents the output to be returned
// Note that the ReferenceOrderId/PrevSum fields are for debugging only
private class TrxnRollingSum {
    public int ProductId { get; set; }
    public DateTime TransactionDate { get; set; }
    public int ReferenceOrderId { get; set; }
    public double ActualCost { get; set; }
    public double PrevSum { get; set; }
    public double RollingSum { get; set; }
}

// The function that generates the result data for each row
// (Such a function is mandatory for SQL CLR table-valued functions)
public static void RollingSum_Fill(object trxnWithRollingSumObj,
                                    out int productId,
                                    out DateTime transactionDate,
                                    out int referenceOrderId, out double actualCost,
                                    out double prevCumulativeSum,
                                    out double rollingSum) {
    var trxn = (TrxnRollingSum)trxnWithRollingSumObj;
    productId = trxn.ProductId;
    transactionDate = trxn.TransactionDate;
    referenceOrderId = trxn.ReferenceOrderId;
    actualCost = trxn.ActualCost;
    prevCumulativeSum = trxn.PrevSum;
    rollingSum = trxn.RollingSum;
}

Tying it all together in SQL
Everything up to this point has been in C#, so let's see the actual SQL involved.  (Alternatively, you can use this deployment script to create the assembly directly from the bits of my assembly rather than compiling yourself.)
USE AdventureWorks2012; /* GPATTERSON2\SQL2014DEVELOPER */
GO

-- Enable CLR
EXEC sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

-- Create the assembly based on the dll generated by compiling the CLR project
-- I've also included the "assembly bits" version that can be run without compiling
CREATE ASSEMBLY ClrPlayground
-- See http://pastebin.com/dfbv1w3z for a "from assembly bits" version
FROM 'C:\FullPathGoesHere\ClrPlayground\bin\Debug\ClrPlayground.dll'
WITH PERMISSION_SET = safe;
GO

--Create a function from the assembly
CREATE FUNCTION dbo.RollingSumTvf (@rollingPeriodDays INT)
RETURNS TABLE ( ProductId INT, TransactionDate DATETIME, ReferenceOrderID INT,
                ActualCost FLOAT, PrevCumulativeSum FLOAT, RollingSum FLOAT)
-- The function yields rows in order, so let SQL Server know to avoid an extra sort
ORDER (ProductID, TransactionDate, ReferenceOrderID)
AS EXTERNAL NAME ClrPlayground.UserDefinedFunctions.RollingSumTvf;
GO

-- Now we can actually use the TVF!
SELECT *
FROM dbo.RollingSumTvf(45)
ORDER BY ProductId, TransactionDate, ReferenceOrderId
GO

Caveats
The CLR approach provides a lot more flexibility to optimize the algorithm, and it could probably be tuned even further by an expert in C#.  However, there are also downsides to the CLR strategy.  A few things to keep in mind:

This CLR approach keeps a copy of the data set in memory.  It is possible to use a streaming approach, but I encountered initial difficulties and found that there is an outstanding Connect issue complaining that changes in SQL 2008+ make it more difficult to use this type of approach.  It's still possible (as Paul demonstrates), but requires a higher level of permissions by setting the database as TRUSTWORTHY and granting EXTERNAL_ACCESS to the CLR assembly.  So there is some hassle and potential security implication, but the payoff is a streaming approach that can better scale to much larger data sets than those on AdventureWorks.
CLR may be less accessible to some DBAs, making such a function more of a black box that is not as transparent, not as easily modified, not as easily deployed, and perhaps not as easily debugged.  This is a pretty big disadvantage when compared to a T-SQL approach.

Bonus: T-SQL #2 - the practical approach I'd actually use
After trying to think about the problem creatively for a while, I thought I'd also post the fairly simple, practical way that I would likely choose to tackle this problem if it came up in my daily work.  It does make use of SQL 2012+ window functionality, but not in type of groundbreaking way that the question was hoping for:
-- Compute all running costs into a #temp table; Note that this query could simply read
-- from Production.TransactionHistory, but a CROSS APPLY by product allows the window
-- function to be computed independently per product, supporting a parallel query plan
SELECT t.*
INTO #runningCosts
FROM Production.Product p
CROSS APPLY (
    SELECT t.ProductId, t.TransactionDate, t.ReferenceOrderId, t.ActualCost,
        -- Running sum of the cost for this product, including all ties on TransactionDate
        SUM(t.ActualCost) OVER (
            ORDER BY t.TransactionDate
            RANGE UNBOUNDED PRECEDING) AS RunningCost
    FROM Production.TransactionHistory t
    WHERE t.ProductId = p.ProductId
) t
GO

-- Key the table in our output order
ALTER TABLE #runningCosts
ADD PRIMARY KEY (ProductId, TransactionDate, ReferenceOrderId)
GO

SELECT r.ProductId, r.TransactionDate, r.ReferenceOrderId, r.ActualCost,
    -- Cumulative running cost - running cost prior to the sliding window
    r.RunningCost - ISNULL(w.RunningCost,0) AS RollingSum45
FROM #runningCosts r
OUTER APPLY (
    -- For each transaction, find the running cost just before the sliding window begins
    SELECT TOP 1 b.RunningCost
    FROM #runningCosts b
    WHERE b.ProductId = r.ProductId
        AND b.TransactionDate < DATEADD(DAY, -45, r.TransactionDate)
    ORDER BY b.TransactionDate DESC
) w
ORDER BY r.ProductId, r.TransactionDate, r.ReferenceOrderId
GO

This actually yields a fairly simple overall query plan, even when looking at the both of the two relevant query plans together:

A few reasons I like this approach:

It yields the full result set requested in the problem statement (as opposed to most other T-SQL solutions, which return a grouped version of the results).
It is easy to explain, understand, and debug; I won't come back a year later and wonder how the heck I can make a small change without ruining the correctness or performance
It runs in about 900ms on the provided data set, rather than the 2700ms of the original loop-seek
If the data were much denser (more transactions per day), the computational complexity does not grow quadratically with the number of transactions in the sliding window (as it does for the original query); I think this addresses part of Paul's concern about wanted to avoid multiple scans
It results in essentially no tempdb I/O in recent updates of SQL 2012+ due to new tempdb lazy write functionality
For very large data sets, it is trivial to split the work into separate batches for each product if memory pressure were to become a concern

A couple potential caveats:

While it technically does scan Production.TransactionHistory just once, it's not truly a "one scan" approach because the #temp table of similar size and will need to perform additional logicion I/O on that table as well.  However, I don't see this as too different from a work table that we have more manual control over since we have defined its precise structure
Depending on your environment, the usage of tempdb could be viewed as a positive (e.g., it's on a separate set of SSD drives) or a negative (high concurrency on the server, lots of tempdb contention already)


AWS Postgres RDS dumping data into sql format

There is an interesting thing in RDS postgresql.  I had the same issue, normal pg_dump won't work. Use the below command

pg_dump -Fc -v -h [endpoint of instance] -U [master username] [database] > [database].dump

Refer link: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL.Procedural.Importing.html


Issue with one query - can some Index help to make it faster?

A 3-column index on (parent, siteid, woclass) will most probably improve the efficiency of the query.

It will certainly help to avoid the table scan. Most likely, with conditions on 3 columns, the rows that match will be very few or at least a very small percentage of the table and an index seek will be far more efficient than a table scan.

Even better, if the number of rows is small, the required sort will be efficient as well so you won't need to include the order by columns in the index (which may not be an option anyway, as the order by could be arbitrary, for all we know). If the query always returns 1 row, as you say, you certainly don't need the additional columns in the index. Why is there an ORDER BY in the query, if that's the case?

So, test the behaviour (of the query but also the overall behaviour of the applications and other queries) in your production/stage environment after you add the index and if the efficiency gain is good enough, roll the change in production.

(By the way, I'm not 100% sure about DB2 but you don't need to stop anything to add an index in most DBMS, I don't think you'd need it in DB2 either. You might get some locking off course and applications could be interrupted due to the locks, especially if the table is big, and various DBMS have different options to address these side effects. So it might be good to add the index in some "quiet" period, say between 04:00 and 06:00 that you know the database / applications have the least usage.)


Galera Cluster with MariaDB 10 and asynchronous slave

Galera cluster auto-provisions its nodes, but that is not the case with traditional replication. You must do it manually.

Look at MariaDB documentation for setting up a slave from a backup. The process is essentially the same as doing it in MySQL, but the syntax is slightly incompatible. It usually just involves mysqldump with the --master-data and --gtid options or using  Percona Xtrabackup (these methods are handled automatically among Galera nodes).

I also include a link to the MySQL documentation on setting up a new slave because even MariaDB decided to become incompatible with it, the common explanations are way better documented than on the MariaDB wiki (e.g. using mysqldump to backup a server).


SSIS File System Task Copy File Could not find part of the path error

Mapped drives, like P:\ in your case are dependent on the user logged in.  Just because you can see the drive letter when logged into the server machine does not mean SQL Server can "see" the drive letter.  If you specify the UNC (Universal Naming Convention) name in the path instead of using the drive letter, and the account SQL Server is using has access to the share and path, then it will work.

Instead of P:\mypath\myfile.txt use \\machine\share\mypath\myfile.txt


How to update SQL Server 2005 clustered instances?

I think this is a pitfall. I recall SQL 2005 and older versions requires active nodes to be updated. SQL 2008 and later versions allow passive node updates like the walkthrough you described. A posting from Linchi Shea explains it well.


AG listener issues with ApplicationIntent=ReadOnly

The reason that this fails outside of the internal Rackspace location is due to the URL Endpoints being set to a value that is not able to be connected through from your local environment.
I discuss this process at a high level in this blog post, however to quickly recap the point that needs to be made here:
The endpoint url is the address where the connection will be routed in order to connect and run their queries. [roughly speaking]

The URL is active directory specific FQDN, so I cannot use it as is
from ourside the the racksapce domain. The ROR URL is some thing like
"tcp://1234-db1.abc.intensive.int:1433"

This means that the client driver is going to be re-pointed to this address; if the address is not reachable then the driver won't be able to connect and you'll have a problem - which you happened to run into.

In your example, the client driver will get back
"tcp://1234-db1.abc.intensive.int:1433" and attempt to connect to it like it would any other instance of SQL Server. Since it can't (as
you've stated) you won't be able to be routed and your connection
should then be on the primary.  What I did was I changed the ROR URL
for each node to public IP address instead of the FQDN/ Host-name, so
when the request is sent to AG listener from outside it knows where to
hand-off in the public facing IP. It looks like, if we have to connect
from outside I have to use public IP (Eg: 72.32.XX.XX) as opposed to
tcp://1234-db1.abc.intensive.int:1433 in the ROR.

While the IP may or may not need to be public (different architectures at different companies' may or may not require different things), it seems in your case it does... though I'm not a networking architecture guru so I can't comment on how/why this was implemented or any technical challenges observed.

Also when we deploy the application in the same domain as the db
servers Active Directory domain "abc.intensive.int" would that pose a
problem when apps try to connect to the db AG listener?

The domain shouldn't make a difference here, the real difference is whether or not your DNS is setup where it can do the lookups required to get the information (IIRC this would be a reverse lookup zone but don't quote me on that). If you can resolve the DNS name, it will work fine, if you can't it obviously won't... this is more of a question for your DNS/AD admins as they hold all the information to that kingdom.


sp_ExecuteSQL, performance and table variables

There is an implicit CREATE TABLE at the beginning and DROP TABLE @T at the end as the table goes out of scope that aren't associated with either the INSERT or the SELECT statement.

If you replace with a #temp table you should see some additional reads for both the drop and create statements. For me I see 36 for the CREATE and 100 for the DROP so that accounts for 136 of your missing 137 reads.

(Script with #temp table)

EXEC sp_executesql N'
   CREATE TABLE #T
  (
     x INT
  )

INSERT INTO #T
            (x)
SELECT TOP 10 number
FROM   master..spt_values

SELECT *
FROM   #T
UNION
SELECT *
FROM   #T

DROP TABLE #T
'


How can I set two values at a single command? Here I want to set Country name for Los Vegas and Alaska from a single sql command

You can use an OR condition:

UPDATE drcat SET Country='USA' WHERE address='California' OR address='Alaska';

or you can use IN ([List of values]):

UPDATE drcat SET Country='USA' WHERE address IN ('California', 'Alaska');


How do I export Postgres data in "Insert ... on conflict" format instead of "cp" format?

pg_dump won't allow you to do exactly what you ask for, but you have an option that might be good enough. According to the documentation of pg_dump, you have the --inserts option:

  --inserts

  Dump data as INSERT commands (rather than COPY). This will make restoration very slow; it is mainly useful for making dumps that can be loaded into non-PostgreSQL databases. However, since this option generates a separate command for each row, an error in reloading a row causes only that row to be lost rather than the entire table contents. Note that the restore might fail altogether if you have rearranged column order. The --column-inserts option is safe against column order changes, though even slower.

For instance, let's assume you use myhost and mydb.

We create and populate one table (in one schema):

CREATE SCHEMA s1 ;

CREATE TABLE s1.t1
(
   id serial PRIMARY KEY,
   column_1 text,
   column_2 text
) ;

INSERT INTO
    s1.t1 (column_1, column_2)
VALUES
    ('Some value', 'and another one'),
    ('Again some value', 'and some more') ;

At this point, we back it up:

pg_dump --host myhost --format custom --section data --inserts --verbose --file "t1.backup" --table "s1.t1" "mydb"

After the backup, we delete one of the rows at the table, but we still leave one:

DELETE FROM
    s1.t1
WHERE
    id = 1 ;

At this point, we do restore the backup (this is what you would normally do on your second database), and get the following messages:

pg_restore --host myhost --dbname "mydb" --section data --data-only --table t1 --schema s1 --verbose "t1.backup"

pg_restore: connecting to database for restore
pg_restore: processing data for table "s1.t1"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2759; 0 21286 TABLE DATA t1 postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  duplicate key value violates unique constraint "t1_pkey"
DETAIL:  Key (id)=(2) already exists.
    Command was: INSERT INTO t1 VALUES (2, 'Again some value', 'and some more');
pg_restore: setting owner and privileges for TABLE DATA "s1.t1"
WARNING: errors ignored on restore: 1

Process returned exit code 1.

The restore process generated one error (the row that was already on the table), but did insert the rest of the data.

Although this is not exactly what you were asking for, for all practical purposes you achieve the result you're looking for.


Non matching attribute 'INSTANT COLUMN(s)' between partition and table

Specify ALGORITHM=INPLACE (or ALGORITHM=COPY if in-place is not supported for your table's engine) in the ALTER TABLE statement explicitly.


UPDATE adds a new row when I merely want to edit an old one

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

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

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


MS SQL Server 2008 R2 duplicate key row in object 'sys.syscommittab'

Eventually I found a Microsoft article on how to do this.  You can't simply delete the rows as they are in protected system tables so you need to put SQL into single user mode and run the script provided in the article from a DAC connection in order to delete the duplicate rows

https://support.microsoft.com/en-gb/help/3083381/duplicate-key-rows-from-the-sys-syscommittab-table-in-sql-server

Transact-SQL script from above article:

--Create a temporary database to store the necessary rows required to remove the duplicate data
if exists(select 1 from sys.databases where name = 'dbChangeTrackingMetadata')
begin
drop database dbChangeTrackingMetadata
end
go
create database dbChangeTrackingMetadata
go

--Table to store the contents of the SYSCOMMITTABLE
use dbChangeTrackingMetadata
go
create table dbo.t_SYSCOMMITTABLE (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
)
go

--Table to store the duplicate rows to be removed from the sys.syscommittab table
create table dbo.t_syscommittab (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
,dbfragid int
)
go

--Enable the usage of OPENROWSET
exec sys.sp_setbuildresource 1
go

--Change <AFFECTED_DB> to the database that contains the duplicate values
USE <AFFECTED DB>
go
declare @rowcount bigint
SET @rowcount = 0

--Copy all rows from the SYSCOMMITTABLE into the temporary database
insert into dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE
SELECT commit_ts, xdes_id, commit_lbn, commit_csn, commit_time
FROM OpenRowset (table SYSCOMMITTABLE, db_id (), 0, 0)

--Save the duplicate values into the temporary database
insert into dbChangeTrackingMetadata.dbo.t_syscommittab
select ondisk_ct.* from sys.syscommittab as ondisk_ct
join dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE as inmem_ct
on ondisk_ct.xdes_id = inmem_ct.xdes_id

--Delete the duplicate values
delete from sys.syscommittab
where xdes_id in ( select xdes_id from dbChangeTrackingMetadata.dbo.t_syscommittab )
set @rowcount = @@rowcount
if (@rowcount > 0)
begin
print ''
print 'DELETED '+CAST(@rowcount as NVARCHAR(10))+' rows from sys.syscommittab that were also stored in SYSCOMMITTABLE'
print ''
end
else
begin
print ''
print 'Failed to DELETE DUP rows from sys.syscommittab'
print ''
end
exec sys.sp_setbuildresource 0
go


Incorrect uniqueidentifier works without a Primary Key but fails with one

I was able to repro this.

Both plans had the row from #temptable driving a nested loops join.

When #DatabaseTable has a primary key there is an index to seek into so the inside of the join has a seek. It is necessary to cast the varchar to uniqueidentifier to get the value to seek at which point the attempt to convert the invalid value fails.

When there is no primary key the plan has a scan on the inside of the nested loops instead. it can defer the evaluation until it has a row to compare, there is no row output from the scan on #DatabaseTable (as it is empty) so the CONVERT_IMPLICIT is never required to be evaluated.

The position of the compute scalar may lead you to believe otherwise but see Compute Scalars, Expressions and Execution Plan Performance for more about that.


Count how many records belong to 2 categories in a n to n relation

When you join topics to topics_to_articles to articles, each article may be tied to multiple topics. And, multiple topics may be associated with each collection. So, you may be counting the same article multiple times.

For example:

Collection A is tied to topics a,b,c
Collection B is tied to topics b,d,e
Article 1 is tied to topics a,b,e
Article 2 is tied to topics a,c
Article 3 is tied to topics b, d, e

If cat_1_id is 1 and cat_2_id is 2:

the IN clause will contain article IDs 1 and 3 (while the query will list them more than once, the IN clause doesn't care how many times an ID is listed, just whether it's there or not)
The main query will count:

article 1, topic a
article 1, topic b
article 1, topic c
article 3, topic b

If cat_1_id is 2 and cat_2_id is 1:

the IN clause will contain article IDs 1, 2, and 3
The main query will count:

article 1, topic b
article 1, topic e
article 3, topic b
article 3, topic d
article 3, topic e

The first query gives you 4; the second query gives you 5; you should really get 2 (because there are two distinct articles returned).

And, that word is the solution - DISTINCT

Change your two lines to:

SELECT
  COUNT(DISTINCT A.article_id) AS total

This way, when the same article_id shows up multiple times, it will still only be counted once.


What are the mandatory/required oracle 11g tablespaces?

You will have to examine how the question is worded.  By default 11g will create all three tablespaces.  You can however create a database with only SYSTEM and SYSAUX.  Technically speaking you can upgrade pre-10g database and not add SYSAUX, or remove the SYSAUX tablespace after the database is created and therefore have a database with only a SYSTEM tablespace, but you would be ill advised to do so and I really don't think they would look for that in an answer.

From the 11.2 Administration Guide:

  The SYSAUX tablespace is always created at database creation.

From another section of the Administration Guide:

  An auto-extending undo tablespace named UNDOTBS1 is automatically
  created when you create the database with Database Configuration
  Assistant (DBCA)...

  If no undo tablespace is available, then the
  instance starts without an undo tablespace, and undo data is written
  to the SYSTEM tablespace. You should avoid running in this mode.

From the 10.2 Concepts Guide:

  ...the SYSAUX tablespace is always created during database creation or
  database upgrade...

  During normal database operation, the Oracle database server does not
  allow the SYSAUX tablespace to be dropped or renamed. Transportable
  tablespaces for SYSAUX is not supported.

  Note: If the SYSAUX tablespace is unavailable, such as due to a media
  failure, then some database features might fail.

From the 10.1 SQL Reference:

  You cannot drop the SYSTEM tablespace. You can drop the SYSAUX
  tablespace only if you have the SYSDBA system privilege and you have
  started the database in MIGRATE mode.

From the 10.1 Administrators Guide:

  If the SYSAUX tablespace becomes unavailable, core database
  functionality will remain operational. The database features that use
  the SYSAUX tablespace could fail, or function with limited capability.

http://oradbpedia.com/wiki/The_SYSAUX_Tablespace has this take:

  The SYSAUX tablespace is a new tablespace that is required in Oracle
  Database 10g...

  When you migrate to Oracle Database 10g, the SYSAUX tablespace needs
  to be created as a part of that migration. This is done after mounting
  the database under the new Oracle Database 10g database software. Once
  you have mounted it, open the database in migrate mode with the
  startup migrate command. After the database is open, you can create
  the SYSAUX tablespace.

  Note: The loss of the SYSAUX tablespace is not fatal to your database.
  In our testing it appears that the only real impact is that certain
  functionality related to the occupants of the SYSAUX tablespace is
  lost.

Earlier versions did not have a SYSAUX tablespace:

  SYSAUX is the name of the compulsory tablespace, introduced in Oracle
  10g.


First run of planner very slow on massively partitioned PostgreSQL 9.6 table

PostgreSQL holds lot of catalog attributes in session based system cache. When query uses lot of partitions (more than 100), then first plannning requires lot of reading operations. And it can be too slow.

The number of partitions should not be bigger than 100. If you have more, then you can see some artefacts.

In your case, the connection pooling can be solution. Try to install pgbouncer.


How I can find out the foreign wrapper that a foreign table belongs to?

This should do the trick:

SELECT
  foreign_table_name,
  foreign_server_name

FROM
  information_schema.foreign_tables

You may find more information on information_schema.foreign_tables in the documentation.


So, you are looking for the difference in Years between the two dates, correct?  And you want to show one decimal place in the calculation, correct?  Then a simple DATEDIFF() will not work for you.  Try this instead:

SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1)

This is also imprecise (because of leap years) but matches what you have now.

EDIT:
You might want to check this logic, as the current ROUND() logic would show a person at 1.0 years when they hit 0.95 years. If you want to wait until they actually have 1.0 years or more to show them at 1.0 years, use this variant: SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1, 1). This adds a trailing 1 to the current command. That added parameter TRUNCATES at the point specified instead of rounding up, so 0.99' would still show '0.9'


ssrs expression to display first 3 parameter values if "ALL" is selected in Parameter List

I don't think you can influence the behaviour of the "Select All" check box, but you could achieve something similar using  Cascading Parameters.

This is a fairly messy solution, especially if you query for the parameter list is slow, but could be an acceptable solution for your end users.

For example, this could work for you.

First: Create a parameter of boolean type, in my example named "Top3":

Second: Add the same data set twice, in the screenshot below you see 2 datasets, Dataset and Dataset_Filter and 2 parameters, Top3 and Dataparameter

The 2 datasets are essentially the same, based on this query on the adventureworks 2014 database

SELECT  CurrencyCode, Name, ROW_NUMBER() OVER (ORDER BY CurrencyCode ASC) as RowNumber
FROM    Sales.Currency
ORDER BY CurrencyCode ASC

The row number is necessary to be able to select x number of rows by default.

The dataset_filter dataset has an additional filter expression added to it, Please note that it is of type integer

In the value you put whatever "top" number of values you want selected.

The trick lies in the expression, the expression is either the row number or something that will always be smaller than the number of values you want to select. In the expression I use the Top 3 parameter.

=IIF(Parameters!Top3.Value, Fields!RowNumber.Value,1)

So essentially this is saying, either the "rownumber has to be lower than or equal to 3" if  top 3 is true or "1 has to be lower than or equal to 3 if top3 is false"

Then on the DataParameter you say the "Available values" come from dataset:

And on the "Default values" you pic the Dataset_Filter dataset (remember, this will either be the entire dataset or only the first 3 rows)

Once you have that set up you end up with a report that has a parameter to allow you to select "top 3" or "select all" like this:

Top 3 selected:

Top 3 false:

Entire RDL XML can be found here on pastebin but requires adventure works 2014 to be installed on localhost.

If you only want to display the first 3 values the obvious dataset query would be

SELECT TOP 3 CurrencyCode, Name, ROW_NUMBER() OVER (ORDER BY CurrencyCode ASC) as RowNumber
FROM    Sales.Currency
ORDER BY CurrencyCode ASC


DBCC CHECKTABLE taking 15+ minutes to run on an empty table

I ended up opening an incident with Microsoft and it was determined that the sys.sysrscols (that can only be accessed via DAC) had over 200 million rows.  After they did a review it was found as part of DBCC SQL Server always does checks on system tables even for DBCC CHECKTABLE. From the DBCC CHECKTABLE manual:

Integrity checks are always performed on all system table indexes.

My understanding is sys.sysrscols does some column tracking and the reason for the large size is a number of tables (200+) we have that have 2,000+ partitions and 163 columns.  Almost all of them were temporary working tables that should have been dropped by the application but were not.  I cleared them out and the process returned to a normal response time for an empty table of less than 1 minute.
They also noted it is listed in the documentation that partitions can impact DBCC checks. From Partitioned Tables and Indexes:

DBCC Commands
With a larger number of partitions, DBCC commands could take longer to execute as the number of partitions increases.


SQL trigger that outputs incremental numbers from zero whenever the month changes

One way to do this would be to just maintain a table with the sequence you want to use, something like this:

Create the table:

mysql> CREATE TABLE seq1 (seqnum int, primary key(seqnum));

Insert one time to have a row to update later:

mysql> INSERT INTO seq1 VALUES (0);

Create a stored procedure to update the sequence number:

DELIMITER $
DROP PROCEDURE IF EXISTS setseq1;
CREATE PROCEDURE `setseq1`(OUT newseq INT)
-- Get a new sequence number that is the greater of the two-digit month number
-- plus a 5-digit sequence number, or the current sequence number + 1
BEGIN
DECLARE myseq INT;
DECLARE monthseq INT;
START TRANSACTION;
   SET myseq=(SELECT seqnum FROM seq1 FOR UPDATE);
   SET monthseq=(SELECT DATE_FORMAT(NOW(), '%y%m') * 100000 + 1);
   SET newseq = IF(myseq < monthseq, monthseq, myseq + 1);
   UPDATE seq1 SET seqnum= newseq;
COMMIT;
END
$

Whenever you need a new sequence number, call the procedure, something like this:

mysql> CALL setseq1(@mynewnumber);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @mynewnumber;
+--------------+
| @mynewnumber |
+--------------+
|    170300016 |
+--------------+
1 row in set (0.00 sec)

mysql> CALL setseq1(@mynewnumber);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @mynewnumber;
+--------------+
| @mynewnumber |
+--------------+
|    170300017 |
+--------------+
1 row in set (0.00 sec)

The first time the procedure is called in any given month, it will set the sequence number to %y%m00001.  Thereafter it will be incremented by 1 each time the procedure is called until the month changes again.

Be sure not to mess with the sequence number table other than to insert one row initially and to call the procedure.


automating a database backup, restore and stored proc call

BACKUP DATABASE [Database A]
TO DISK = 'C:\Temp\DatabaseA.bak' WITH FORMAT
GO
use master
GO
RESTORE DATABASE [Database X]
FROM  DISK = N'C:\Temp\DatabaseA.bak'
,  MOVE N'DataFile' TO N'C:\Foo\DatabaseX.mdf'
,  MOVE N'LogFile' TO N'C:\Bar\DatabaseX.ldf'
,  NOUNLOAD,  REPLACE,  STATS = 10

GO
use [Database X]
GO
exec StoredProcedureName...
GO
use master
GO
DBCC ShrinkDatabase( [Database X] )


The message seems pretty clear: you cannot be using the DB1 database when trying to restore it - instead, use the master database!

So in SQL Server Management Studio, first close all windows that are open and using that database! Then run this:

USE master
GO

RESTORE DATABASE [DB1]
FROM DISK = N'D:\new.bak'
WITH RESTRICTED_USER, FILE = 1, NOUNLOAD, REPLACE, STATS = 10;


When using an ORM, what are some things to watch out for in your database design

Go through standard normalization techniques, mainly to 3rd normal form. Some ORMs can pick up on the relationships defined at the database level so you won't have to manually.

I would really go the other way and ask what should I know about ORMs. Make sure you know:
 - How do I profile the queries?
 - How can I override the ORM and write my own query?
 - Can I use stored procs instead?

The database should be ORM agnostic since, more than likely, it will outlast the app.


The \ symbol is often used in mathematics for set difference, besides dash or minus symbol (-)

Since tables and relations are very similar to sets and the operations of union, intersection and difference are similarly defined (see Wikipedia: Relational algebra, set operators), the \ symbol is also sometimes used for difference.

If the Q = R\S expression is found in a database theory book and Q, R and S are relations, it means that Q is a relation (table) that has the same attributes (columns) as R and S and also has those tuples (rows) that are in relation R but not in relation S.


Error while running script to backup redo logs

RMAN can not connect to the recovery catalog, because the username or password is incorrect.

Fix the username and password in the catalog database and the script.


That depends:

Restore Points are nothing more than pointers to flashback logs in the FRA.  If you are creating these pointers via:create restore point <rpname>, then you can do something like:

begin
  for rp in (SELECT name FROM V$RESTORE_POINT where restore_point_time < sysdate - 30)
  loop
    execute immediate 'drop restore point ' || rp.name;
  end loop;
end;

However, if you are not explictly creating these restore points, and you are just concerned about space consumption and reclamation, then take a look at the FRA:

show parameter db_flashback_retention_target;
show parameter db_recovery_file_dest;
show parameter db_recovery_file_dest_size;

The relationship between these parameters is as follows:

The flashback logs will be stored in db_recovery_file_dest.

db_flashback_retention_target is how long (in minutes) you want to keep these flashback logs for.

However, the logs will only consume space until the size specified in db_recovery_file_dest_size is reached.

You can estimate how large to make db_recovery_file_dest_size with the following query:

SELECT ESTIMATED_FLASHBACK_SIZE/1024/1024/1024 --in gigabytes
FROM V$FLASHBACK_DATABASE_LOG;

Therefore, if you want to keep flashback logs for 30 days, you would:

alter system set db_flashback_retention_target = 43200 scope=both;
--43200 = 1440 minutes in a day times 30

After some amount of time, check the estimated_flash_back_size in v$flashback_database_log, and size db_recovery_file_dest_size appropriately.  The flashback logs will be cleaned up automatically without the need for any sort of cleanup script.  When sizing the FRA, keep in mind that you may be storing other things in the FRA (like RMAN backups), which you can check with:

select * from  V$FLASH_RECOVERY_AREA_USAGE;

You can read up on restore points, flashback database, and the FRA from the Oracle docs here.


You can use this script written by Dimitri Furman from Microsoft. It will  measure transactions/sec per database.  You need to run this against the database where you want to measure transactions/sec. I tested this against V12 version and it works.

https://blogs.msdn.microsoft.com/dfurman/2015/04/02/collecting-performance-counter-values-from-a-sql-azure-database/


Does using secondary replica for read operations still required license?

Yes, it required license as both of the instances are active/running.

Availability Group has set of databases that you want to synchronize to secondary replica. It's not necessary to add all databases to availability group, you can add the databases for which you want fault tolerance.

So if your primary replica goes down only those database are part of the availability group would fail-over to secondary & it becomes primary.

MS doesn't restrict you to use the secondary nodes having read-only secondary replica for read-only purpose only, you can also create and use databases on the secondary nodes.

So, it needs a license for secondary replica whether you use it for read-only or read-write both.

Thanks!


You can create a new job step at the end of the existing job that executes the "on-failure" job. You use the procedure sp_start_job to call this "on-failure" job. If the job fails, then have the on-failure action to execute that new job step. For this new step, make sure to set the on-success-action to "Quit the job reporting failure" so you know it failed.


Extract data along a path (lat, long, altitude and time)

First, to make sense of this we convert to use geography points, rather than lat-long. I assume this is in WGS84. We do this because measuring a difference in lat/long doesn't make any sense. As you approach the poles the differences between 3 degrees of lat/long will be nothing compared to the equator.

BEGIN;
  ALTER TABLE flightpath ADD COLUMN geog geography;
  UPDATE flightpath SET geog =
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;
  ALTER TABLE flightpath DROP COLUMN lon, DROP COLUMN lat;
COMMIT;

BEGIN;
  ALTER TABLE weather ADD COLUMN geog geography;
  UPDATE weather SET geog =
    ST_SetSRID(ST_MakePoint(lon, lat), 4326)::geography;
  ALTER TABLE weather DROP COLUMN lon, DROP COLUMN lat;
COMMIT;

Now you can use ST_DWithin and datetime math.

SELECT
  id,
  f.alt,
  f.time,
  coalesce(w.temp::text, 'no temperature') AS temp
FROM flightpath AS f
LEFT OUTER JOIN weather AS w
  ON (
    (
      w.time - f.time < '3 minutes'::interval      -- diff in time
      AND  f.time - w.time < '3 minutes'::interval
    )
    AND abs(w.alt - f.alt) <= 1000                 -- diff in alt
    AND ST_DWithin(w.geog, f.geog, 1000000)        -- diff in distance (meters)
  )
WHERE id = 1
ORDER BY f.alt;

That outputs something like this..

 id |  alt  |        time         |      temp
----+-------+---------------------+----------------
  1 | 29000 | 2017-01-01 00:01:05 | no temperature
  1 | 34000 | 2017-01-01 00:01:31 | -9
  1 | 37000 | 2017-01-01 00:01:59 | -11
  1 | 40100 | 2017-01-01 00:02:28 | -15


MSSQL - Primary Key Clustered specifying multiple columns

Primay key doesn't take any extra storage, its just the way data is stored in that sort order. Whether Primary key(clustered index) should be id(identity column) or something else depends on the database designer and the requirement of application. I would suggest you to start with non-clustered index and check whether they are utilized or not by your select queries. Indexes are good only for select however bad for all DML operations(mostly) i.e. Insert/Update/Delete.

Non-clustered indexes are the ones which takes separate storage, since more than 50% of your database is stored as index, you may need to check their usage(reads). It is possible that they are simply creating overhead in writing and not very useful for scan/seek operation. Just to add, having 50% or more storage for index is not always bad, it just depends whether you have more read/write operation - Dataware house/reporting/OLTP.

You may download first responder kit provided by Mr. Brent Ozar from here and can run sp_blitzindex for your database and it would check everything regarding your index - whether they are too much, too less or some other issue.

In a normal scenario, there is rule of 5 non-clustered index per table however again depends on so many other factors. Kindly provide more details on the issue you are facing due to these indexes. for your two questions - I have tried to answer them.

Kindly let us know if that helped.