How do I handle multiple rows with variables defined by a query?

It might help to change how you approach the problem from the very beginning. There are a lot of sub-queries, all hitting the same remote table, and all with mostly the same WHERE condition. Rather than thinking about the end result as a collection of separate pieces, think about it in terms of one big set of data, and that you want to tally up how many rows meet certain criteria as you move through each row once.

Assuming that the code in the Question wasn't overly over-simplified, you should be able to do the following, which uses a Table Variable to hold multiple values of "Code" per the given "Key". Once we have those captured, then we can simply JOIN that to the remote table. We JOIN on the criteria that is common to all of the subqueries, and then place the unique criteria within the IIF().

Also, I got rid of the DISTINCT on the @Sys lookup as it was only there due to the SELECT being in a subquery which isn't necessary. And while you could use TOP 1 rather than DISTINCT, there is no reason to force a sort given that it will be the same value that comes back each time, hence the process won't notice that @Sys is potentially being overwritten up to 5 times (with the exact same value). Of course, the @Sys variable is not being used in the original query, so maybe it can be removed entirely?

DECLARE @Codes TABLE (Code VARCHAR(5) NOT NULL);
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

INSERT INTO @Codes (Code)
  SELECT Code
  FROM   LOOKUP.Table1
  WHERE  [Key] = @Key;

-- @Sys is not used in the final query. Should this be removed?
SELECT @Sys = [System] -- multiple rows may return,
FROM LOOKUP.Table1     -- but they all have the same value
WHERE [Key] = @Key;

SELECT cd.Code,
       SUM(IIF(th.[Type] NOT LIKE 'X%', 1, 0)) AS [Total],
       SUM(IIF(th.[Type] LIKE 'Y%', 1, 0)) AS [TypeA],
       SUM(IIF(th.[Type] LIKE 'N%', 1, 0)) AS [TypeB]
FROM   @Codes cd
INNER JOIN [LINKED-SERVER].[DB].[SCHEMA].[Table_H] th
        ON th.[Key] = @Key
       AND th.[Code] = cd.[Code]
GROUP BY th.[Code];


Oracle: How do I set a context variable?

The above works just fine.

SQL> create or replace context t1_ctx using t1_pkg accessed globally;

Context created.

SQL>

create or replace package t1_pkg
as
    procedure setx;
end t1_pkg;
/

Package created.

SQL>

create or replace package body t1_pkg
as
    procedure setx is
    begin
        dbms_session.set_context('t1_ctx','x','1');
    end;
end t1_pkg;
/

Package body created.

SQL> select sys_context('t1_ctx', 'x') from dual;

SYS_CONTEXT('T1_CTX','X')
--------------------------------------------------------------------------------

SQL> exec t1_pkg.setx;

PL/SQL procedure successfully completed.

SQL> select sys_context('t1_ctx', 'x') from dual;

SYS_CONTEXT('T1_CTX','X')
--------------------------------------------------------------------------------
1


Display posted on date and time like1 hr ago or 30 min ago

As I said in the comments above, this should really be formatted in the application layer, where you are controlling the presentation to the user.

The issue lies with the way SQL handles the DATEDIFF function, in that comparing the HOUR difference between 08:59 and 09:00 will result in 1 hour, because the hour has ticked over and there is 1 hour difference between 8 and 9.

My solution may not be the most elegant, however it uses difference in MINUTE to get the number of hours passed since posting and the difference in HOUR to get the number of days.

 declare @date datetime ='2014-07-23 09:50:22.033';
    SELECT
      CASE
        WHEN DATEDIFF(SECOND, @date, GETDATE()) < 60 THEN ' Just Posted'
        WHEN DATEDIFF(MINUTE, @date, GETDATE()) < 60 THEN CAST(DATEDIFF(MINUTE, @date, GETDATE()) AS VARCHAR(10)) + ' Minutes ago'
        WHEN DATEDIFF(MINUTE, @date, GETDATE()) < 24 * 60 THEN CAST(FLOOR(DATEDIFF(MINUTE, @date, GETDATE())/60) AS VARCHAR(10)) + ' Hours ago'
        ELSE CAST(FLOOR(DATEDIFF(HOUR, @date, GETDATE())/24) AS VARCHAR(10)) + ' Days ago'
    END AS Postedon


How do I dump spatial types, like point, with their SRIDs in MySQL?

The 0 in that context is the SRID. Unlike in PostgreSQL, you don't have a method of setting that on a point. You will in version 8 of MySQL. For the purposes of calculation, it's assumed to be 0 on everything and this is horrible. There is likely to be substantial breakage when they fix this.

  In MySQL, all computations are done assuming SRID 0, regardless of the actual SRID value. SRID 0 represents an infinite flat Cartesian plane with no units assigned to its axes. In the future, computations may use the specified SRID values. To ensure SRID 0 behavior, create geometry values using SRID 0. SRID 0 is the default for new geometry values if no SRID is specified. source

So the SRID will almost always be 0 because

it doesn't yet matter
There is no SRID mutator so you have to construct with ST_PointFromText. So the SRID will always be 0 if you construct with a point

That said, to get the SRID (future-safe) in the format provided above, you need to do this

SELECT CONCAT(ST_AsText(x), ',', ST_SRID(x))
FROM ( SELECT POINT(0,0) AS x ) AS t;

+---------------------------------------+
| CONCAT(ST_AsText(x), ',', ST_SRID(x)) |
+---------------------------------------+
| POINT(1 1),0                          |
+---------------------------------------+

This will show differently if your SRID is not 0.

SELECT CONCAT(ST_AsText(x), ',', ST_SRID(x))
FROM ( SELECT ST_PointFromText('POINT(1 1)',4326) AS x ) AS t;

+---------------------------------------+
| CONCAT(ST_AsText(x), ',', ST_SRID(x)) |
+---------------------------------------+
| POINT(1 1),4326                       |
+---------------------------------------+

Compare that to PostgreSQL,

SELECT ST_AsEWKT(x)
FROM ( VALUES (ST_SetSRID(ST_MakePoint(1,1),4326)) ) AS t(x);
      st_asewkt
----------------------
 SRID=4326;POINT(1 1)
(1 row)

Update

Taking a look at your query, there is one glaring mistake in that you do (latitude,longitude) this should be (longitude,latitude)

  So to be safe is my existing insert query is acceptable or should I add the SRID value now.

I would add the SRID value now, but not because your existing query "isn't safe". Only because you're not using placeholders and you're stringifying everything anyway.

",latlong=Point("+floatLatitude+","+floatLongitude+")";

Should be

",latlong=ST_PointFromText(POINT("+floatLongitude+" "+floatLatitude+"), 4326)";

Where 4326 is the SRID of your data..

But again, MySQL doesn't take into consideration SRID yet so be aware.

Objective facts

MySQL and MariaDB have horrible spatial support. Migrate to PostgreSQL and PostGIS, if possible.


How to copy bytea from Postgres to Oracle

Here is a general way of getting a table with bytea data from postgres into a similar Oracle table with a blob.

It should be trivial to adapt my test to your real tables, and you should probably increase the size of the 'chunks' that I used to keep the output readable, from 20 to something more like the 2000 limit for string literals (4000/2 as each byte is 2 bytes hex).

1) postgres
testbed:

begin;
set role dba;
create role stack;
grant stack to dba;
create schema authorization stack;
set role stack;
--
create function random_bytea(p_length in integer)
       returns bytea language plpgsql set search_path to 'stack' as $$
declare
  o bytea := '';
begin
  for i in 1..p_length loop
    o := o||decode(lpad(to_hex(width_bucket(random(), 0, 1, 256)-1),2,'0'), 'hex');
  end loop;
  return o;
end;$$;
--
create function bytea_to_oracle_sql(p_id in integer, p_data in bytea)
       returns text language plpgsql set search_path to 'stack' as $$
declare
  o text := 'insert into foo(id, bar) values('||p_id||', empty_blob());';
begin
  for i in 0..(length(p_data)-1)/20 loop
    o := o||chr(10)||'update foo set bar=append_to_blob(bar,'''
          ||encode(substr(p_data,i*20+1,20),'hex')||''') where id='||p_id||';';
  end loop;
  return o;
end;$$;
--
create table foo(id serial, bar bytea);

insert test data and check hashes:

insert into foo(bar) values(random_bytea(90));
insert into foo(bar) values(random_bytea(90));
select id, md5(bar) from foo;
/*
 id |               md5
----+----------------------------------
  1 | 32a24c8827047eff517e8a40bf3ad8b4
  2 | 25ac852000889ab782ad6437accf1546
*/

generate Oracle script:

\o '/tmp/oracle.sql'
select bytea_to_oracle_sql(id, bar) from foo;
\o

contents of /tmp/oracle.sql:

insert into foo(id, bar) values(1, empty_blob());
update foo set bar=append_to_blob(bar,'ddbe858f7905551862507ddaa3b5410cba8013d4') where id=1;
update foo set bar=append_to_blob(bar,'089d6e66f16b10ba1204a4efc22a7f3c2fd45492') where id=1;
update foo set bar=append_to_blob(bar,'24d26dc887afd4412fe8163786859e77f0af3202') where id=1;
update foo set bar=append_to_blob(bar,'b3d7f2750172b2314606c36bd8313360e008a20f') where id=1;
update foo set bar=append_to_blob(bar,'f0b847763de6ee2e9521') where id=1;
insert into foo(id, bar) values(2, empty_blob());
update foo set bar=append_to_blob(bar,'3148f823befa95712bdc77ef4750207bb0018352') where id=2;
update foo set bar=append_to_blob(bar,'10730b58ea483ed876d4faa81df3ccdbed624d18') where id=2;
update foo set bar=append_to_blob(bar,'3ec40886142901c52a85173bf92393e36bd2bce2') where id=2;
update foo set bar=append_to_blob(bar,'a6b68ac3a9569f97b8ecaff7b1b87dc6e17f8b0b') where id=2;
update foo set bar=append_to_blob(bar,'04a347fdc6dae132ad9d') where id=2;

remove testbed:

rollback;

2) Oracle
testbed:

create table foo(id integer, bar blob);
--
create or replace
function append_to_blob(p_data in blob, p_append_hex in varchar) return blob is
  o blob;
begin
  dbms_lob.createtemporary(o, TRUE);
  dbms_lob.append(o, p_data);
  dbms_lob.writeappend(o, length(p_append_hex)/2, hextoraw(p_append_hex));
  return o;
end;
/

run script:

insert into foo(id, bar) values(1, empty_blob());
update foo set bar=append_to_blob(bar,'ddbe858f7905551862507ddaa3b5410cba8013d4') where id=1
...

check hashes:

select id, dbms_crypto.hash(bar,2) from foo;
/*
 ID DBMS_CRYPTO.HASH(BAR,2)
--- --------------------------------
  1 32A24C8827047EFF517E8A40BF3AD8B4
  2 25AC852000889AB782AD6437ACCF1546
*/

credit to Vincent Malgrat for his concat_blob function I adapted. See here for the random bytea function by itself


How to find dependencies on a table in mysql 5.0?

From MySQL 5.0 on, one place you can check is information_schema.table_constraints

For example, to get the number of foreign key relationships for a given table (mydb.mytable), run this:

SELECT COUNT(1) FROM information_schema.table_constraints
WHERE table_schema = 'mydb'
AND table_name = 'mytable'
AND constraint_type='FOREIGN KEY';

From MySQL 5.1 on, you could also use information_schema.referential_constraints. Here is that table layout:

mysql> show create table information_schema.referential_constraints\G
*************************** 1. row ***************************
       Table: REFERENTIAL_CONSTRAINTS
Create Table: CREATE TEMPORARY TABLE `REFERENTIAL_CONSTRAINTS` (
  `CONSTRAINT_CATALOG` varchar(512) DEFAULT NULL,
  `CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `CONSTRAINT_NAME` varchar(64) NOT NULL DEFAULT '',
  `UNIQUE_CONSTRAINT_CATALOG` varchar(512) DEFAULT NULL,
  `UNIQUE_CONSTRAINT_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `UNIQUE_CONSTRAINT_NAME` varchar(64) DEFAULT NULL,
  `MATCH_OPTION` varchar(64) NOT NULL DEFAULT '',
  `UPDATE_RULE` varchar(64) NOT NULL DEFAULT '',
  `DELETE_RULE` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `REFERENCED_TABLE_NAME` varchar(64) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.01 sec)

Just inspect table_name and referenced_table_name columns.


mssql revert changes made today without backup

My Recovery model is set to simple

In short, there is nothing you can do but learn from this lesson that important databases should use the full recovery model (along with proper restore plans).

  Can i use the logfile for this

Since it's in the simple recovery model, most likely not.

  Is 'ROLLBACK' another option ?

Only if the transaction is currently open and it's a single transaction. If it's been committed, nope.


Very slow query, not sure if MySQL index is being used

entries needs INDEX(valid, tenent_id)
users needs INDEX(active, user_id)

More discussion (Index Cookbook).

More specifics

LEFT probably prevents the optimizer from picking which table to start with.  Assuming the LEFT is important, let's break the query in two:

FROM entries WHERE e.tenant_id=? AND e.valid=1

The link above explains that the optimal index starts with anything with '=', so (tenant_id, valid) in any order.  (I suggested one of them.)

Then it moves on to the other table:

LEFT JOIN users u
    ON u.user_id = ...
WHERE u.active=1

Hence, (active, user_id) in any order.

(Sorry, I need to augment the Cookbook with more discussion of JOIN.)

If you could get rid of LEFT, then a different set of indexes might be useful:

(active)
(valid, user_id, tenant_id) -- in any order

Having both pairs of indexes would let the optimizer pick which is better.  However, some consolidation is desirable (again, see the link).  These two would suffice for either table order.  Now the column order is important:

(active, user_id)   -- also handles (active)
(valid, tenant_id, user_id)  -- also handles (valid, tenent_id)

More on your title question of "not sure if index is being used".  In the EXPLAIN, NULL and 116710 imply that it is not.  But index and Using index imply that it is.  The tie-breaker is Using join buffer (Block Nested Loop) which says that it is loading the index into RAM to use it.  (This is a case where join_buffer_size matters.)

The 4-column index you had was somewhat useful, but it

did not have all the needed columns (active) to be covering
had an extra column (group_id)
was in a sub-optimal order: the extra column came before the all the needed columns, so valid was not as useful as it could be.


correct
not necessarily - it depends on whether a suitable index exists and whether the CBO decides to use it. For example if the table is small or the the statistics lead the optimizer to believe that the filter will be true for a large percentage of the results, it may consider the cost of a FTS to be lower
this is not guaranteed - it is even possible that the 2nd query will perform worse - but your reasoning is fundamentally sound. For example, in an edge case when a FTS would actually be faster, the CBO may choose an index scan instead based on its best guess of the cost - which is always only an estimate of course

A bit of googling turned up this SO question and this interesting looking article on SARGability


Is it possible for a trigger to update a row in a table using a value that was deleted?

If I understand your question right, what you want is an after delete trigger:

DELIMITER $$
CREATE TRIGGER ad_foo AFTER DELETE on foo
FOR EACH ROW
BEGIN
  update table1 SET table1.state=1 WHERE id=OLD.id;
END$$
DELIMITER ;

Example with just the insert: http://sqlfiddle.com/#!2/974b4/1

Example with insert and delete: http://sqlfiddle.com/#!2/0bf72/1


The purpose of the entire activity was to prove(as I am about to give
  presentation on statistics) that SQL Server looks into statistics to
  identify the number of records which matches the filter criteria of
  query before preparing the execution plan and based on the % of
  records matches out of total records in table, it will either decide
  to do a SCAN or SEEK. If % of records matches is approximately equal
  to total number of records in table, it should do SCAN.

this is incorrect so explains why you aren't seeing it. The BETWEEN 43659 AND 73659 range seek is doing a partial scan. It is just able to use the B-tree to seek into the point where to begin the scan (so avoid reading anything lower than 43659 ) and potentially exit early if there are rows with values greater than 73659.

For the rows that are in the range it just reads the pages and follows the linked list to the next leaf page in exactly the same way as an index ordered scan does.

There is no reason to want a scan here. At best it saves a handful of logical reads for navigating from the root to the leaf to find the start point but at the expense of reading additional rows outside the range seeked.


How to write a trigger to add some information before update?

The fieldnames, which contains spaces and parenthesis, must be enclosed by backticks.

In current state they are treated as UDFs, and you must obtain the "undefined function" error (or syntax error).

The trigger contains only one statement, so you do not need in BEGIN-END block and DELIMITER change.

CREATE TRIGGER after_orders_table_update
AFTER UPDATE
ON orders
FOR EACH ROW
INSERT INTO orders_log table
SET orderNumber             = old.orderNumber,
    orderdate               = old.orderdate,
    customerNumber          = new.customerNumber,
    `status(before update)` = old.status,
    `status(after update)`  = new.status,
    action                  = 'update';


Oracle migration unable to create indexes

This is a known bug, and it will be fixed in a future version.

Use CTX_REPORT.CREATE_INDEX_SCRIPT to generate the index scripts, and create them manually.


How does mysql(innoDB) handle same foreign key for join

According to MySQL 5.7 documentation (current as of now):

  8.8.2 EXPLAIN Output Format

  The EXPLAIN statement provides information about how MySQL executes statements. EXPLAIN works with SELECT, DELETE, INSERT, REPLACE, and UPDATE statements.

  EXPLAIN returns a row of information for each table used in the SELECT statement. It lists the tables in the output in the order that MySQL would read them while processing the statement. MySQL resolves all joins using a nested-loop join method. This means that MySQL reads a row from the first table, and then finds a matching row in the second table, the third table, and so on. When all tables are processed, MySQL outputs the selected columns and backtracks through the table list until a table is found for which there are more matching rows. The next row is read from this table and the process continues with the next table.

Emphasis mine. That is, I guess that MySQL isn't intelligent enough to remember that "it already looked some key".

MariaDB uses several Block-Based Join Algorithms in current versions:

  Block Nested Loop (BNL) join
  Block Nested Loop Hash (BNLH) join
  Block Index join known as Batch Key Access (BKA) join
  Block Index Hash join known as Batch Key Access Hash (BKAH) join

The "Batch Key Access" methods seems to take some advantage ... the key part of the docs is:

  Yet BKA join allows to optimize the order in which the records are fetched.


Your screenshot appears to be the SQL Database blade, not the Server blade. If you open the Overview pane on the Database blade, then in the top-right corner there is a property called Server Name with a link. Click the link to open the Server blade and your Manage Backups option is there.


Who and when were changed Database default location settings?

I don't think you'll be able to find that out. SQL Server doesn't track this information by default (e.g. in the default trace or system health extended event session), and in fact if you go through the UI and click the Script button instead of saying OK, you will see it just generates a script like this, which writes directly to the registry:

USE [master]
GO
EXEC xp_instance_regwrite
   N'HKEY_LOCAL_MACHINE',
   N'Software\Microsoft\MSSQLServer\MSSQLServer',
   N'DefaultLog', REG_SZ, N'C:\new path'
GO

This value is then pulled from the registry next time SQL Server starts (so there is no event to trap when the action happens).

Going forward, you may be able to capture calls to xp_instance_regwrite using something like SQL Server Audit or a targeted server-side trace, but that doesn't help you for something that happened in the past, before something like this is set up.

Sounds like a more appropriate action - rather than trying to figure out who did something you didn't want them to do - is to take away their rights to do things you don't want them to do (e.g. people who you can't trust, or who won't own up to doing something like this, shouldn't have sysadmin).


Why does the system generated TNSNAMES.ORA have two entires, one with a full connect descriptor and another with just a network address?

No, it generated 1 entry for the database (DB2), and another for the listener (LISTENER_DB2).

Then your local_listener parameter was set to LISTENER_DB2, so your database could use this address to find the listener and register into it, since it is not listening on the default port (1521).


How to restore percona database from kubernetes?

The copy-backup.sh can be found on the repo
Then following the commands on the documentation will produce xtrabackup.stream file.


Try using an UPDATE JOIN of the table against itself

UPDATE
    animal_table A
INNER JOIN
(
    SELECT
        category_id,summary_id,
        animal_nbr,animal_amount,'00' info3
    FROM animal_table
    WHERE animal_type = 'special'
) B USING (category_id,summary_id)
SET
    A.animal_info1 = B.animal_nbr,
    A.animal_info2 = B.animal_amount,
    A.animal_info3 = B.info3
WHERE
    A.animal_type = 'cats' AND
    A.category_id = 'foo';

Make sure that animal_table has a compound index on category_id and summary_id. If you do not have such an index, please run this:

ALTER TABLE animal_table ADD INDEX (category_id,summary_id);

UPDATE 2011-09-27 13:10 EDT

I just noticed that categrory_id 'foo' limits the dataset. Here is an updated refactoring of my answer ( I moved category_id = foo into the subquery )

UPDATE
    animal_table A
INNER JOIN
(
    SELECT
        category_id,summary_id,
        animal_nbr,animal_amount,'00' info3
    FROM animal_table
    WHERE animal_type = 'special'
    AND category_id = 'foo'
) B USING (category_id,summary_id)
SET
    A.animal_info1 = B.animal_nbr,
    A.animal_info2 = B.animal_amount,
    A.animal_info3 = B.info3
WHERE
    A.animal_type = 'cats';

I also recommend adding this additional index to accommodate the subquery

ALTER TABLE animal_table ADD INDEX (animal_type,category_id);


Asynchronous implies that the running totals do not need to be completely accurate at all times, or your data change patterns are such that a one-off running total build will be valid and accurate until the next load.  Anyway, I'm sure you have thought that part through, so I won't labour the point.

Your main options for a high-performance, supported, method are a SQLCLR function/procedure, or an UPDATE based on Hugo Kornelis' set-based iteration method.  The SQLCLR method (implemented in a procedure, but reasonably easy to translate) can be found here.

I haven't been able to find Hugo's method online, but it is detailed in the excellent MVP Deep Dives (Volume 1).  Sample code to illustrate Hugo's method (copied from one of my posts on another site you may not have a login for) is shown below:

-- A work table to hold the reformatted data, and
-- ultimately, the results
CREATE  TABLE #Work
    (
    Acct_No         VARCHAR(20) NOT NULL,
    MonthDate       DATETIME NOT NULL,
    MonthRate       DECIMAL(19,12) NOT NULL,
    Amount          DECIMAL(19,12) NOT NULL,
    InterestAmount  DECIMAL(19,12) NOT NULL,
    RunningTotal    DECIMAL(19,12) NOT NULL,
    RowRank         BIGINT NOT NULL
    );

-- Prepare the set-based iteration method
WITH    Accounts
AS      (
        -- Get a list of the account numbers
        SELECT  DISTINCT Acct_No
        FROM    #Refunds
        ),
        Rates
AS      (
        -- Apply all the accounts to all the rates
        SELECT  A.Acct_No,
                R.[Year],
                R.[Month],
                MonthRate = R.InterestRate / 12
        FROM    #InterestRates R
        CROSS
        JOIN    Accounts A
        ),
        BaseData
AS      (
        -- The basic data we need to work with
        SELECT  Acct_No = ISNULL(R.Acct_No,''),
                MonthDate = ISNULL(DATEADD(MONTH, R.[Month], DATEADD(YEAR, R.[year] - 1900, 0)), 0),
                R.MonthRate,
                Amount = ISNULL(RF.Amount,0),
                InterestAmount = ISNULL(RF.Amount,0) * R.MonthRate,
                RunningTotal = ISNULL(RF.Amount,0)
        FROM    Rates R
        LEFT
        JOIN    #Refunds RF
                ON  RF.Acct_No = R.Acct_No
                AND RF.[Year] = R.[Year]
                AND RF.[Month] = R.[Month]
        )
-- Basic data plus a rank id, numbering the rows by MonthDate, and resetting to 1 for each new Account
INSERT  #Work
        (Acct_No, MonthDate, MonthRate, Amount, InterestAmount, RunningTotal, RowRank)
SELECT  BD.Acct_No, BD.MonthDate, BD.MonthRate, BD.Amount, BD.InterestAmount, BD.RunningTotal,
        RowRank = RANK() OVER (PARTITION BY BD.Acct_No ORDER BY MonthDate)
FROM    BaseData BD;

-- An index to speed the next stage (different from that used with the Quirky Update method)
CREATE UNIQUE CLUSTERED INDEX nc1 ON #Work (RowRank, Acct_No);

-- Iteration variables
DECLARE @Rank       BIGINT,
        @RowCount   INTEGER;

-- Initialize
SELECT  @Rank = 1,
        @RowCount = 1;

-- This is the iteration bit, processes a rank id per iteration
-- The number of rows processed with each iteration is equal to the number of groups in the data
-- More groups --> greater efficiency
WHILE   (1 = 1)
BEGIN
        SET @Rank = @Rank + 1;

        -- Set-based update with running totals for the current rank id
        UPDATE  This
        SET     InterestAmount = (Previous.RunningTotal + This.Amount) * This.MonthRate,
                RunningTotal = Previous.RunningTotal + This.Amount + (Previous.RunningTotal + This.Amount) * This.MonthRate
        FROM    #Work This
        JOIN    #Work Previous
                ON  Previous.Acct_No = This.Acct_No
                AND Previous.RowRank = @Rank - 1
        WHERE   This.RowRank = @Rank;

        IF  (@@ROWCOUNT = 0) BREAK;
END;

-- Show the results in natural order
SELECT  *
FROM    #Work
ORDER   BY
        Acct_No, RowRank;

In SQL Server 2012, you could use the windowing function extensions e.g. SUM OVER (ORDER BY).


Select most recent inspection, break tie between inspections from same year

You can modify the order by clause in the window function:

row_number() over
    (partition by road_id
     order by extract(year from insp_date) desc,   -- latest year
              length desc                          -- higher length
    ) as rn

Tested in dbfiddle.uk (only in version 11g).

In version 12c, you could also use the FETCH FIRST syntax.

The wanted PARTITION BY makes things more difficult than a simple FETCH though. We'll have to use a LATERAL inline view or CROSS APPLY:

select
    r.*
from
    ( select distinct road_id
      from road_insp
    ) d,
  lateral
    ( select
          ri.insp_id,
          ri.road_id,
          to_char(ri.insp_date, 'DD/MON/YY HH:MI:SSAM') insp_date_formatted,
          ri.length
      from road_insp ri
      where ri.road_id = d.road_id
      order by extract(year from ri.insp_date) desc,
               ri.length desc
      fetch first 1 rows only
    ) r
;


SQL Server : auditing AD Users - how can we identity AD User account from AD Groups

Below is a script that uses xp_login_info (https://msdn.microsoft.com/en-us/library/ms190369.aspx) to get the AD permission paths for each AD user and group with a SQL Server login.

SET NOCOUNT ON;
IF OBJECT_ID(N'tempdb..#results', 'U') IS NOT NULL
     DROP TABLE #results;
CREATE TABLE #results(
      account_name sysname
    , type char(8)
    , privilege char(9)
    , mapped_user_name sysname
    , permission_path sysname NULL
    );

DECLARE @SQL nvarchar(MAX) = (
    SELECT 'INSERT INTO #results EXECUTE xp_logininfo ''' + name + ''', ''all'';'
    FROM sys.server_principals
    WHERE type_desc IN(N'WINDOWS_LOGIN', N'WINDOWS_GROUP')
    FOR XML PATH(''));

EXECUTE(@SQL);

SELECT
      account_name
    , type
    , privilege
    , mapped_user_name
    , permission_path
FROM #results
ORDER BY account_name, mapped_user_name;


Variant.

Create a module. Copy the below code into it:

Public Function CustomRank(Optional key) As Integer
Static CurrentNumber As Integer
If IsMissing(key) Then
    CurrentNumber = 0
Else
    CurrentNumber = 1 + CurrentNumber
End If
CustomRank = CurrentNumber
End Function

Modify your query.

Add a field CustomRank(some_field) AS Rank into output list.

some_field must be some NOT NULL field (the best way - primary key, if PK is composite then use any field from primary key expression). If LEFT JOIN is used then it must be a field from the most left table.

If GROUP BY is used in the query then use any NOT NULL field from GROUP BY expression.

Any NOT NULL expression from output list is safe too.

Add the condition OR (CustomRank() > -1) to your WHERE conditions if no GROUP BY is used, or to your HAVING conditions if GROUP BY is used.
Add proper ORDER BY.

PS. This solution is NOT safe for parallel queries execution.

In your particular case the query will be:

SELECT sourcetable.id, sourcetable.NAME, sourcetable.V1, sourcetable.V2, sourcetable.V3, sourcetable.V4, sourcetable.V8, CustomRank(sourcetable.id) AS Rank
FROM sourcetable
WHERE (CustomRank()>-1)
ORDER BY sourcetable.V1 DESC , sourcetable.V2, sourcetable.V3, sourcetable.V4, sourcetable.V8;

Output:


Does the Query cost in Sql Server account for buffering?

Yes and no, depending on what you mean by 'buffer status'.

The state (content) of the buffer pool does not directly affect the estimated cost of an execution plan (the cost model assumes every query starts with a cold data cache.

The size (not content) of the buffer pool can have an impact on plan selection for several reasons.

The first occurs when memory-consuming operations like hash and sort are present. The amount of memory estimated to be available for the memory grant depends on buffer pool size (note interesting bug in this area).

The following AdventureWorks-based query shows an example of this costing difference based on the size of the buffer pool.  In this case, the plan shape is the same, but these costing differences can, and do, change plan selection with larger tables or more complex queries:

-- Set to 256 MB
EXEC sys.sp_configure
    @configname = 'max server memory (MB)',
    @configvalue = 256;

RECONFIGURE;

-- Estimated cost 5.92241 with 256MB
SELECT DISTINCT
    ProductID,
    TransactionDate,
    Quantity,
    ActualCost
FROM Production.TransactionHistory AS th
OPTION (MAXDOP 1);

-- Set to 2GB
EXEC sys.sp_configure
    @configname = 'max server memory (MB)',
    @configvalue = 2048;

RECONFIGURE;

-- Estimated cost 5.48355 with 2GB
SELECT DISTINCT
    ProductID,
    TransactionDate,
    Quantity,
    ActualCost
FROM Production.TransactionHistory AS th
OPTION (MAXDOP 1);

Second, the optimizer contains logic to assess (for example) the chance that a page read from disk (under the cold cache assumption) during execution will be required again, and if so, whether it will still be available from cache.

More to the point though, optimizer cost estimates are mainly only useful for internal server purposes. They are not intended to be used to assess potential performance, even at a 'high level'. The model is an abstraction that happens to work reasonably well for the internal purposes it was designed for. The chances that estimated costs bear any sensible resemblance to real execution costs on your hardware and configuration is very small indeed.

Choose other metrics to compare performance, based on whatever real issues are important to you.


Influxdb and grafana: howto create series that is the sum of two other series

In theory, switch Grafana query editor to the raw mode and write a query where you use a subquery - outer query will just sum data precalculated by inner query:

SELECT SUM(field_from_main_query) FROM (
  <your current Grafana query with GROUP BY time(10s) hostname>
) GROUP BY time(10s)


Using Union and ORDER BY:

Be explicit with results form first query:

(SELECT DISTINCT rep.name, rep.date as rdate
FROM `reports_new` rep
LEFT JOIN `repuser_new` ru
ON ru.`id_rep` = rep.`id`
JOIN `clients` c
ON c.`id` = rep.`id_client`
WHERE ru.`id_user` = '1' and rep.`name` LIKE 'urgent')

UNION
(SELECT username,rdate FROM users)

ORDER BY rdate DESC

I assumed the second table had a date column. Otherwise and explicit value could be used.


Not sure of the question, but psql doesn't have variables at all. It's just a macro language.

\set p 50
\set z 50::numeric(20,5)

SELECT pg_typeof(:p), pg_typeof(:z);
 pg_typeof | pg_typeof
-----------+-----------
 integer   | numeric
(1 row)

\set after '''1 day''::interval'
\set before '''1 day''::interval'
SELECT 1 WHERE now() BETWEEN now() - :before AND now() + :after;


Does SQL Server 2008 R2 always fail with a severity level 20 error with certain large text manipulation?

There's really no database involved. You can see it hits no tables. But yes we've run this "in" several databases and run CHECKDB against them just to be sure.

The database involved is tempdb (database ID 2).  That's where the value of LOB variables gets written.  You can see that by looking in the sys.dm_db_task_space_usage DMV (if your query weren't failing with an error, that is):

My data database context for that query was "master," but notice that the database_id in the query results in tempdb.
So you could check tempdb for corruption.  This database gets recreated each time the server is restarted, so I'd be surprised if you have persistent corruption there.  Unless you're having disk issues, in which case you should consistently see errors in the Windows "System" Event log that indicate corruption.

All that said, it's also very possible this is a bug in SQL Server 2008 R2 SP3.  There is at least one non-security-related hotfix to that SP (KB3033860).  The errors mentioned there don't look like they match your situation, but could be related (they are assertion failures).
Unfortunately, since SQL Server 2008 R2 is out of support, this won't be fixed even if it is a bug.  Your best bet is to try and work around the problem, or upgrade.


Incremental backup procedure

lock writes on a secondary member
Dump oplog from the recorded oplog position on full (or latest incremental ) backup:

mongodump --host <secondary> -d local -c oplog.rs -o /mnt/mongo-test_backup/1
--query '{ "ts" : { $gt :  Timestamp(1437725201, 50) } }'

Record latest oplog position (same way as for full backups)
Unlock writes

Click Here for detailed information


replicate-do says to replicate that table but nothing else.  That is, the "grant" tables are won't be replicated".
How did you "create the user"?  CREATE USER?  GRANT?  Manipulate mysql.user?  Something else.
Did you do FLUSH PRIVILEGES;?


Can a member of the SQL server 'sysadmin' role login to any database?

A sysadmin can use any database and also specify any database in the connection string. The special dbo user is used whenever a sysadmin does anything inside a database.
If you failed to connect to a specific database, then I'd say that the database doesn't exist (or is inaccessible) or you aren't a sysadmin. :-)
(Sorry I don't have something to point to the specifics about having a database in the connection string.)


What your query does, is putting the COUNT() in the column you want, but it actually counts every row. There's only one condition, where COUNT() distinguishes between "to count" or "not to count". And that is, that when you COUNT(*) every row is counted, no matter what. When you COUNT(column_name), it counts only the rows that are not NULL. But that just aside.

What you actually want to do is this:

SUM(IF((HOUR(comments.date)) = '9', 1, 0)) AS `9-10`,

Another note:
When you do

WHERE
DATE(comments.date) = DATE(NOW()) AND
MONTH(comments.date) = MONTH(NOW()) AND
YEAR(comments.date) = YEAR(NOW())

the comparison for month and year is unnecessary, since this is already covered in DATE().

An even better way to do this is actually doing this:

WHERE comments.date BETWEEN CURDATE() AND CONCAT(CURDATE(), ' 23:59:59')

or

WHERE comments.date >= CURDATE()
AND comments.date < CURDATE() + INTERVAL 1 DAY

This is because MySQL can't use an index on a column (if there is any), when you use a function on this column.


Am I compromising effiency by using CTE instead of indexed tables?

A CTE is just a disposable view. As Erik commented, CTE results are not stored to disk.

In terms of perf, a temp table has many advantages of creating indexes, stats, etc which will outperform CTE when dealing with large tables and complex queries.

When we migrated from sql server 2008 to 2014 (SP2+CU8), I saw queries performing poor (generating bad query plans) and we had to change them to use temp tables.

So - based on my experience - if you need speed, go with temp tables (they are not free as depending on how much data you are dealing with, it hits temp table and memory).

Read Martin's answer at : Which are more performant, CTE or temporary tables?


1 minus percentage returns negative number

Or you could just use GREATEST with 0:

select
    insp_id,
    deficiency_length,
    sidewalk_length,
    greatest(0, 1 - (deficiency_length / sidewalk_length)) as condition_percent
from
    user.sidewalk_insp;

The above function returns 0 or calculated condition percent, whichever is greater.


You might try mydumper which is multi-threaded whereas mysqldump is single-threaded.
Percona have also improved the less locking feature in the most recent version.
The excellent Mysql Performance Blog has this useful article.


Select All Instances of Substring in a String

First, create a string splitting function. For simplicity I'm going to use an XML splitter, but you can see lots of discussion about it here and in all the links that points you to.

CREATE FUNCTION dbo.SplitStrings_XML
(
   @List       nvarchar(max),
   @Delimiter  nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
   RETURN ( SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)')
      FROM ( SELECT x = CONVERT(xml, ''
          + REPLACE(@List, @Delimiter, '')
          + '').query('.')) AS a
          CROSS APPLY x.nodes('i') AS y(i));
GO

With the function in place, you can now use it to chop apart this JSON string:

DECLARE @s varchar(max) = '[
  {"loanId":"1111111111","someotherValue":7},
  {"loanId":"2222222222","someotherValue":4},
  {"loanId":"3333333333","someotherValue":5},
]';

SELECT LoanID = PARSENAME(s2.Item,1)
FROM dbo.SplitStrings_XML(@s, ',') AS s
CROSS APPLY dbo.SplitStrings_XML(s.Item, ':') AS s2
WHERE s.Item LIKE '%"loanId":"%'
AND s2.Item NOT LIKE '%"loanId"%';

Results:

LoanID
----------
1111111111
2222222222
3333333333


Can you direct Availability Groups to fail over to a particular node?

Windows Server Failover Clustering (WSFC) with SQL Server does have the option to failover to a particular node and Availability Groups seem to build on WSFC.

Taken form the MSDN:

  Preferred owner A node on which a resource group prefers to run.
  Each resource group is associated with a list of preferred owners
  sorted in order of preference. During automatic failover, the resource
  group is moved to the next preferred node in the preferred owner list.

http://msdn.microsoft.com/en-us/library/hh270278.aspx


You can use a group by on each table to get the result you're looking for, then wrap the whole thing in a view to filter it. See this fiddle: https://www.db-fiddle.com/f/d313vqnsTkiXDXfiEPTwLt/4

Select shortages.Type, shortages.Shortage from (
    Select a.Type, a.QTY-d.QTY Shortage
    from
        (Select Type, sum(QTY) QTY from Acceptors group by Type) a
         inner join
        (Select Type, sum(QTY) QTY from Donors group by Type) d on a.type = d.type

) shortages
where shortages.shortage > 0

If you need to cover the situation where a value doesn't exist in the donors table at all you should replace the inner join with a left join and use a case statement or an ISNULL to replace the null values with zeros.


Sure, there are plenty of table hints, like:

PAGLOCK
ROWLOCK
TABLOCK
TABLOCKX

However, you should use these with extreme caution. There are very few cases where the average programmer knows better than the engine, and forcing hints can cause more problems than they solve.


Confusion about HA , LOG backups and availability

1) Availability Groups are in sync with it's replicas by log files, right? So the primary replica sends information from the LOG files to the secondary.

Looking at it form a very high level, yes.

  2) With log shipping, I need a job to backup log from primary, copy it to another drive, send it to the netword, and then another job in the secondary replica to restore this log into the database.

You do'nt have to necessarily copy it/move it, etc., if you take the backup to a place that is already shared with all other log shipping partners. The overall steps, though, are the gist.

  I dont need this naymore with Availability groups right? Primary replica already sends log data directly from the log file to the secondary replica. Or is this wrong?

Very high level, you're correct.

  3) I ran a query and I noticed that there aren't backups in this server ( primary) since 2017, and logs since 2018. Is this a problem for Availability groups?

It's a problem in general. Should you need to restore from a backup, there aren't any recent ones. Since the logs haven't been backed up, their log files will continue to grow until they max out or the disk is filled, whichever comes first.

  with no log backup files, there's no information to send to the replica. Or 2) is right already?

The log is transmitted regardless of backup activities.


Copy a single record to a replica table before update using trigger (Oracle 12c)

You can use this format for copying your original values over:

CREATE OR REPLACE TRIGGER update_Trigger
BEFORE UPDATE ON customer
FOR EACH ROW
BEGIN
    IF UPDATING THEN
        INSERT INTO  replica VALUES(:OLD.cID, :OLD.cName, ... );
    END IF;
END;
/

You will need to specify each field you want inserted into replica

This will be slow and cause a lot of network/database traffic if you are updating 1,000s of rows at a time.


How to find records where specific values are different

In principle, the solution should probably be coded as a single SQL statement.  However, you could also use PL/SQL and a CURSOR FOR LOOP.  The question text does not give us any test data.  Please let us know if the following solution needs major adjustments (Oracle version: 12c).

create table a_test(
  first number
, second varchar2(64)
, rates number
);

-- insert some test data
-- first column: range 1-100, every third row: NULL
-- second column: all XX
-- third column: values between 0 and 9
begin
  for i in 1 .. 100
  loop
    insert into a_test values (i, 'XX', mod(i,10));
  end loop;
  update a_test
  set first = null
  where mod(first,3) = 0;
end;

The test table contains 100 rows. Use SQL first ...

-- query1: only the rows where first IS NULL -> 33 rows
select count(*)
from a_test
where first is null
  and second = 'XX'

-- query2: no duplicates for rates -> 10 rows
select first, second, rates
from a_test
where first is null
  and second = 'XX'
group by rates, first, second
order by rates;

When you are using PL/SQL, there are various ways of coding this.  Maybe one of the following will be helpful in your situation:

-- query2 in a cursor for loop
begin
  for rec in (
    select first, second, rates from a_test
    where first is null
      and second = 'XX'
    group by rates, first, second
    order by rates )
  loop
     dbms_output.put_line (
       rec.first || ' ' || rec.second || ' ' || rec.rates  );
   end loop;
end;

-- IF in a cursor for loop (query2 without a WHERE clause)
begin
  for rec in (
    select * from a_test
    group by rates, first, second
    order by rates )
  loop
     if rec.first is null and rec.second = 'XX' then
       dbms_output.put_line (
         'null' || ' ' || rec.second || ' ' || rec.rates  );
       -- alternative: code rec.first instead of 'null'
     end if;
   end loop;
end;


Windows Authentication In SQL DTS packages

You can do this by giving the specified Windows account the necessary permissions to the servers/databases that the SSIS job connects to, and then running the DTS package under that Windows account.

For example, to run a SQL Agent job step with the Windows account, you have to create a SQL Server credential, then a SQL Server Agent proxy for that credential, and then assign the job step that runs the package to run under the proxy.  I don't recommend it, but for completeness will mention that if you are using a domain account for the SQL Server Agent account, you can simply give permissions to that account, and have the job step run with it.  The problem with this is that you have one account with permissions to numerous objects and it becomes a security concern.

If you are using a Windows Scheduled Task, you need to configure that task to run with the specified account.

You'll also need to change the connection strings to remove the SQL login and password, and specify ;Integrated Security=SSPI so that it will use Windows authentication.

This thread also mentions that a linked server can be used, but doesn't provide any further details.


Why isn't space deallocated after deleting all rows from a clustered table?

I was able to reproduce described behavior. I created the same table, fill it with data and then delete all rows.

After deleting I had a look at indexes:

SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID('TableName_Test'), -1, NULL, 'DETAILED')

Results (I hid columns which don't matter):

Thank you,  Scott Hodgin and Erik Darling, you both were partly right and direct me to  the right way in my researches. The issue occurs because Ghost Cleanup process can't clean the version ghost records.

The behavior is similar to one described here: Problem With Too Many version_ghost_records.

Unfortunately, I can't call exactly what is root cause of being the versioned rows still held. I have not found any long transaction on any database which could cause it.

Index rebuilding and sometimes (as told in the article linked above) server restart help to solve the issue.


Best Way to Index Multiple Effective Date Columns

Introduction
I do not have the time for extensive testing, but can suggest from where
to start.
If you rewrite the query in a more symmetrical manner, to
emphasise that both entities are joined to a two-dimensional
cross-section of MyJoinTable:
SELECT E1.Field, E2.Field
FROM MyJoinTable JT
JOIN Entity1 E1 ON E1.Id = JT.Entity1Id
JOIN Entity2 E2 ON E2.Id = JT.Entity2Id
WHERE
    StartDate1 <= @date1 AND EndDate1 > @date1 AND
    StartDate2 <= @date2 AND EndDate2 > @date2

you will see that one reasonably efficient way of executing it is first
to extract that cross-section and then to join the entities to it.
The following equivalent query will serve for illustration purposes
(but do not use it!):
-- A sample to demostrate the desired order or execution:
SELECT E1.Field, E2.Field
FROM
-- 1. Calculate the cross-section:
(   SELECT JT.Entity1Id, JT.Entity2Id
    FROM MyJoinTable JT
    WHERE
        StartDate1 <= @date1 AND EndDate1 > @date1 AND
        StartDate2 <= @date2 AND EndDate2 > @date2
) SECT
-- 2. Join the entity tables:
JOIN Entity1 E1 ON E1.Id = SECT.Entity1Id
JOIN Entity2 E2 ON E2.Id = SECT.Entity2Id

It is the more important since you say that there is only

one relationship row returned for any @Date1, @Date2 combination.

For this reason, joining the entities in before MyJoinTable is reduced
to the required cross-section by @date1 and @date2 is inefficient as
it is likely to have many records for each entity so that the result
will have too many rows. The entites are best joined at the end, using
their natural key field, Id, which I assume is the clustered index.
The solutions below, therefore, propose different ways of calculating
this cross-section, corresponding to the SECT subquery of the example
above—
Soution I: The easiest
Let us try to add useful indices to the original query.  Since MSSQL's
composite indices are hierarchical, they are useless in optimising
interval comparisons, so the best we can do with the given structure is
to index one of the date fields, yet make sure to cover all the other
fields required form the table:
CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable ( StartDate1 )
INCLUDE (Entity1Id, Entity2Id, StartDate2, EndDate1, EndDate2 )

The query then will be executed in the following order:

Using the index SD1, perform an index seek on MyJoinTable
to find records by StartDate1, and filter them by StartDate2,
EndDate1, and EndDate2 in the
residual predicate

Join the entity tables using their natural key Id.

This method is not very efficient because only one of the four date
predicates is fully optimised by an index, resudual predicates being
boring data grinders.
Solution II: Set intersection
Another symmetrical way to obtain the two-dimensional JOIN
cross-section goes through INTERSECTing the results of the four date
predicates:
SELECT Entity1Id, Entity2Id
FROM
(   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date1 >= StartDate1
    INTERSECT
    SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date2 >= StartDate2
    INTERSECT
    SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date1 <  EndDate1
    INTERSECT
    SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date2 <  EndDate2 )
SECT

With the suitable indices,
CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable (StartDate1)
    INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX SD2 ON MyJoinTable (StartDate2)
    INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX ED1 ON MyJoinTable (EndDate1  )
    INCLUDE (Id, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX ED2 ON MyJoinTable (EndDate2  )
    INCLUDE (Id, Entity1Id, Entity2Id)

the plan for this query includes only clean index seek (wihout residual
predicates) and hash match operations.
Observe that the entity keys in query and indices present a
fourfold redundancy, which may be removed—at the expense of a more
complicated execution plan—by joining the entities in separately:
SELECT JT.Entity1Id, JT.Entity2Id
FROM
(   SELECT Id FROM MyJoinTable WHERE @date1 >= StartDate1
    INTERSECT
    SELECT Id FROM MyJoinTable WHERE @date2 >= StartDate2
    INTERSECT
    SELECT Id FROM MyJoinTable WHERE @date1 <  EndDate1
    INTERSECT
    SELECT Id FROM MyJoinTable WHERE @date2 <  EndDate2 )
SECT
JOIN MyJoinTable JT ON JT.Id = SECT.Id

with these indices:
CREATE NONCLUSTERED INDEX SD1 ON MyJoinTable (StartDate1) INCLUDE (Id)
CREATE NONCLUSTERED INDEX SD2 ON MyJoinTable (StartDate2) INCLUDE (Id)
CREATE NONCLUSTERED INDEX ED1 ON MyJoinTable (EndDate1  ) INCLUDE (Id)
CREATE NONCLUSTERED INDEX ED2 ON MyJoinTable (EndDate2  ) INCLUDE (Id)

But since in either case each of four date predicates, bounding the date
on one end only, does not reduce the amount of data sufficiently, hash
matches may have to
spill
data into tempdb. If they do, this method is not fit for your
environment.
Solution III: A compromise
Now we can merge solutions I & II in order to come up with a plan that
does not require so much RAM and at the same time is reasonably fast:
SELECT Entity1Id, Entity2Id FROM
(   SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date1 >= StartDate1 AND @date1 < EndDate1
    INTERSECT
    SELECT Id, Entity1Id, Entity2Id FROM MyJoinTable
    WHERE @date2 >= StartDate2 AND @date2 < EndDate2  )
SECT

Now the date constraints are more efficient in discaring data
because they bound the date on both ends. With the indices below:
CREATE NONCLUSTERED INDEX SE1 ON MyJoinTable (StartDate1)
    INCLUDE (EndDate1, Entity1Id, Entity2Id)
CREATE NONCLUSTERED INDEX SE2 ON MyJoinTable (StartDate2)
    INCLUDE (EndDate2, Entity1Id, Entity2Id)

each constraint uses an index seek with a residual predicate, which is
better than the single index seek in solution I and takes less RAM
than solution II. The plan shows three opportunities of parallisation:
one for each date constraint, and one for the INTERSECT operation.
A non-redundant version of this approach would be:
SELECT JT.Entity1Id, JT.Entity2Id FROM
(   SELECT Id FROM MyJoinTable
    WHERE @date1 >= StartDate1 AND @date1 < EndDate1
    INTERSECT
    SELECT Id FROM MyJoinTable
    WHERE @date2 >= StartDate2 AND @date2 < EndDate2  )
SECT
JOIN MyJoinTable JT ON JT.Id = SECT.Id

with indices
CREATE NONCLUSTERED INDEX SE1 ON MyJoinTable (StartDate1)
    INCLUDE (EndDate1, Id)
CREATE NONCLUSTERED INDEX SE2 ON MyJoinTable (StartDate2)
    INCLUDE (EndDate2, Id)

Although it should work better with your data, where the SECT subquery
returns at most one row, in my crude tests with randomly generated data
it has been less efficient because the server used a hash match instead
of a nested loop join with that single row. Do try it on your side.
Solution IV: Optimised structure
It is possible to optimise your query at the expense of introducing a
more complicated structure that requires additional maintenance and
slows down the modification of data in MyJoinTable. If you are willing
pay the price, store the date ranges as sets of days:
CREATE TABLE MyJoinTable
(   Id         INT IDENTITY(1,1),
    Entity1Id  INT,
    Entity2Id  INT,
    Range1     INT, -- reference to Ranges.Id
    Range2     INT  -- reference to Ranges.Id
)

CREATE TABLE Ranges
(   Id    INT,
    Date  Date
)

and query the relation thus:
SELECT E1.Field, E2.Field
FROM MyJoinTable JT
JOIN Entity1 E1  ON E1.Id = JT.Entity1Id
JOIN Entity2 E2  ON E2.Id = JT.Entity2Id
JOIN Ranges  R1  ON R1.Id = JT.Range1
JOIN Ranges  R2  ON R2.Id = JT.Range2
WHERE
    R1.Date = @date1 AND
    R2.Date = @date2

You will need some testing in order to determine optimal indices, but I
think the following should work:
CREATE NONCLUSTERED INDEX RD ON Ranges      ( Date   ) INCLUDE ( id )
CREATE NONCLUSTERED INDEX RR ON MyJoinTable ( Range1, Range2 )
-- optionally: INCLUDE (Entity1Id, Entity2Id)

Index RD will make sure the ranges are quickly found corresponding to
the specified dates, and index RR will help to find the record
matching these ranges. But then you shall have to devise a means of
filling the Ranges table and keeping it in sync with MyJoinTable,
because doing so by hand is out of the question.


SQL Server Profiler 2005 on remote SQL Server 2008 Express?

Those messages about 'unable to read trace definition' and the fix are always from a newer SQL Profiler version trying to connect to an older SQL Server version. You are trying the other way (from older SQL Profiler to newer SQL Server) and it won't work. AFAIK You need to use SQL Profiler 2008 or newer.


[soapbox]

  I would tell them "for security reasons" you should provide Windows
  Authentication access to that storage. Ask them if they really want
  authentication information in plain text saved to access a box that stores all the data for the organization and would give a user full access to their data (unless you are encrypting your backups)?

[/soapbox]

For your specific issue the parameters in sqlcmd for authentication are to access a SQL Server instance, not to access external storage or other resource. You will need to adjust the scripts to more or less map the drive with the required credentials and then use that path within your backup script.

If you are on SQL Server 2014 I would suggest using PowerShell script or SQL Agent step as this contains easier methods of working with the file system and mapping drives. You can quickly check access to the path before running the T-SQL code to ensure it does not fail prematurely.


You can use an array constructor:

select array(
select zone
from mytable)
as areas;


How does an encrypted DynamoDB (the same question applies to RDS as well) query work?

For DynamoDB encryption at rest, it means the table is encrypted on disk, not each of the attributes of the items in the table. This is true for any database talking about encryption at rest. That at rest means on disk. Now if you were to implement application data level encryption on your own, then what you are talking about would come into play.

One thing to know is that as of a few weeks ago, all DynamoDB tables are encrypted at rest by default.


How to store immutable, unique, ordered, lists?

I would go with a traditional set-based approach for this.  Create entity, list, entityList, and listItem tables to store your data.  Individual list and entity tables allow you to create lists without entities and vice versa.  Constraints on these tables such as unique constraints and foreign keys will help preserve the integrity of the data.

For the list, create a single stored procedure which is the single point for inserting lists and listItems.  This proc has logic to detect if a list already exists and serves as a gateway.  This is straightforward to do using set operators like EXCEPT.

Here's a simple demo which shows how something like this might work.  I'm using a table-valued parameter (TVP) to handle passing around lists and a scalar function to determine if a list already exists.  Scalar functions are evil when used in resultsets (eg SELECT, WHERE clauses, JOIN ) but are ok when used for what they are meant for, returning single values.  Spend some time working through it and see if it could work for you:

USE tempdb
GO

SET NOCOUNT ON
GO

------------------------------------------------------------------------------------------------
-- Setup START
------------------------------------------------------------------------------------------------

-- Reset
IF OBJECT_ID('dbo.entityLists') IS NOT NULL DROP TABLE dbo.entityLists
IF OBJECT_ID('dbo.listItems') IS NOT NULL DROP TABLE dbo.listItems
IF OBJECT_ID('dbo.entities') IS NOT NULL DROP TABLE dbo.entities
IF OBJECT_ID('dbo.lists') IS NOT NULL DROP TABLE dbo.lists
IF OBJECT_ID('dbo.usp_addList') IS NOT NULL DROP PROC dbo.usp_addList
IF OBJECT_ID('dbo.usf_listExists') IS NOT NULL DROP FUNCTION dbo.usf_listExists
IF EXISTS ( SELECT * FROM sys.types st INNER JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'tvp_myList' AND ss.name = N'dbo')
DROP TYPE dbo.tvp_myList
GO

CREATE TABLE dbo.entities
(
    entityId        INT IDENTITY,
    entityName      VARCHAR(50) NOT NULL,

    dateAdded       DATETIME    NOT NULL DEFAULT GETDATE(),
    addedBy         VARCHAR(30) NOT NULL DEFAULT SUSER_NAME(),
    dateModified    DATETIME    NULL,
    modifiedBy      VARCHAR(30) NULL,

    CONSTRAINT PK_entities PRIMARY KEY ( entityId ),
    CONSTRAINT UK_entities__entityName UNIQUE ( entityName )

)
GO

CREATE TABLE dbo.lists
(
    listId          INT IDENTITY(1000,1),
    listName        VARCHAR(50) NOT NULL,

    dateAdded       DATETIME    NOT NULL DEFAULT GETDATE(),
    addedBy         VARCHAR(30) NOT NULL DEFAULT SUSER_NAME(),
    dateModified    DATETIME    NULL,
    modifiedBy      VARCHAR(30) NULL,

    CONSTRAINT PK_lists PRIMARY KEY ( listId ),
    CONSTRAINT UK_lists__listName UNIQUE ( listName )

)
GO

-- Although constraints might appear a little excessive, they prevent 'bad data' getting into the table,
-- whereas lesser constraints would not, eg prevent list with duplicate order
CREATE TABLE dbo.listItems
(
    listItemId      INT IDENTITY(100000,1),
    listId          INT NOT NULL,
    itemOrderId     INT NOT NULL,
    itemValue       INT NOT NULL,

    CONSTRAINT PK_listItems PRIMARY KEY ( listItemId ),         -- prevent list with duplicate order, value
    CONSTRAINT UK_listItems__listId__itemValue UNIQUE ( listId, itemValue ),        -- prevent list with duplicate value
    CONSTRAINT UK_listItems__listId__itemOrderId UNIQUE ( listId, itemOrderId ),    -- prevent list with duplicate order
    CONSTRAINT UK_listItems UNIQUE ( listId, itemOrderId, itemValue ),

    CONSTRAINT FK_listItems__listId FOREIGN KEY ( listId ) REFERENCES  dbo.lists ( listId )

)
GO

CREATE INDEX _idx_listItems__itemOrderId__itemValue ON dbo.listItems ( itemOrderId, itemValue ) -- to be used in list existence check
GO

CREATE TABLE dbo.entityLists
(
    entityId        INT NOT NULL,
    listId          INT NOT NULL,

    CONSTRAINT PK_entityLists PRIMARY KEY ( entityId, listId ),
    CONSTRAINT UK_entityLists__entityId UNIQUE ( entityId ),
    CONSTRAINT UK_entityLists__listId UNIQUE ( listId ),
    CONSTRAINT FK_entityLists__entityId FOREIGN KEY ( entityId ) REFERENCES  dbo.entities ( entityId ),
    CONSTRAINT FK_entityLists__listId FOREIGN KEY ( listId ) REFERENCES  dbo.lists ( listId )

)
GO

-- Create a type for passing lists around; TVP types and constraints mirror target table to act as a quality control
CREATE TYPE dbo.tvp_myList AS TABLE
(
    itemOrderId INT NOT NULL UNIQUE,    -- you could make this an identity column if you want to make sure order is sequential
    itemValue   INT NOT NULL UNIQUE,

    PRIMARY KEY ( itemOrderId, itemValue )

)
GO

-- Scalar function to determine if list already exists
CREATE FUNCTION dbo.usf_listExists
(
    @tvp        AS dbo.tvp_myList   READONLY
)

RETURNS BIT
AS
BEGIN
    DECLARE @listExists_yn  BIT

    IF EXISTS (
        SELECT COUNT(*) OVER() totalItems, itemOrderId, itemValue
        FROM @tvp
        EXCEPT
        SELECT COUNT(*) OVER( PARTITION BY listId ) totalItems, itemOrderId, itemValue
        FROM dbo.listItems
        )
        SET @listExists_yn = 0
    ELSE
        SET @listExists_yn = 1

    RETURN @listExists_yn

END
GO

-- Create a proc to add lists, report if list already exists
CREATE PROC dbo.usp_addList

    @listName   VARCHAR(50) = NULL,
    @tvp        AS dbo.tvp_myList   READONLY,
    @listId     INT OUTPUT

AS

SET NOCOUNT ON

    BEGIN TRY

        -- Check if list name already exists
        IF @listName IS NULL
            SET @listName = NEWID()
        ELSE
        IF EXISTS ( SELECT * FROM dbo.lists WHERE listName = @listName )
            RAISERROR ( 'List name ''%s'' already exists id %s', 16, 1, @listName );

        -- Check if list content already exists
        IF dbo.usf_listExists(@tvp) = 1
        BEGIN
            -- Get list
            --!!TODO add 'get list id' proc
            RAISERROR ( 'List already exists id %i', 16, 1, @listId );
        END
        ELSE
        BEGIN

            BEGIN TRAN

                -- Add the list
                INSERT INTO dbo.lists ( listName ) VALUES ( @listName )
                SET @listId = SCOPE_IDENTITY()

                -- Add the list items
                INSERT INTO dbo.listItems ( listId, itemOrderId, itemValue )
                SELECT @listId, itemOrderId, itemValue
                FROM @tvp

            COMMIT TRAN

        END

    END TRY
    BEGIN CATCH

        DECLARE @errorMessage NVARCHAR(2048) = ERROR_MESSAGE()

        IF @@TRANCOUNT > 0 ROLLBACK
        RAISERROR( @errorMessage, 16, 1 )

    END CATCH

    RETURN

GO

-- Setup END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Initial Data START
-- Add some data; actually entities are irrelevant to this demo
------------------------------------------------------------------------------------------------

;WITH cte AS (
SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.entities ( entityName )
SELECT 'Entity ' + CAST ( rn AS VARCHAR(10) )
FROM cte
GO

---- Add some simple lists
DECLARE @listId INT, @tvp AS dbo.tvp_myList

INSERT INTO @tvp VALUES ( 1, 10 )   -- Single Item List ( 10 )

EXEC dbo.usp_addList 'my New List 1', @tvp, @listId OUT

INSERT INTO @tvp VALUES ( 2, 20 )   -- Two Item List ( 10, 20 )
EXEC dbo.usp_addList 'my New List 2', @tvp, @listId OUT

INSERT INTO @tvp VALUES ( 3, 30 )   -- Three Item List ( 10, 20, 30 )
EXEC dbo.usp_addList 'my New List 3', @tvp, @listId OUT
GO

-- Initial lists
select * from dbo.lists
select * from dbo.listItems

-- Initial Data END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Test Scenarios START
------------------------------------------------------------------------------------------------

-- Try and add any of those lists again
BEGIN TRY

    -- Single item list already exists
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 )
    EXEC dbo.usp_addList 'my New List 4', @tvp, @listId OUT

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Two item list already exists
    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 )
    EXEC dbo.usp_addList 'my New List 5', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 )
    EXEC dbo.usp_addList 'my New List 6', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    -- New four-item list
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 ), ( 4, 40 )
    EXEC dbo.usp_addList 'my New List 7', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    -- New four-item list
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 20 ), ( 3, 30 ), ( 4, 41 )
    EXEC dbo.usp_addList 'my New List 8', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

BEGIN TRY

    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Two item list does not exist, but three-item list with same two components does exist; both get added
    INSERT INTO @tvp VALUES ( 1, 10 ), ( 2, 21 ), ( 3, 30 )
    EXEC dbo.usp_addList 'my New List 9', @tvp, @listId OUT
    PRINT @listId

    DELETE @tvp WHERE itemValue = 30
    EXEC dbo.usp_addList 'my New List 10', @tvp, @listId OUT
    PRINT @listId

END TRY
BEGIN CATCH
    PRINT ERROR_MESSAGE()
END CATCH
GO

-- Final lists
SELECT * FROM dbo.lists
SELECT * FROM dbo.listItems
GO

-- Test Scenarios END
------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------
-- Load Tests START
-- For 1,000 random entities, create a randomn-size list and add; report errors
------------------------------------------------------------------------------------------------

-- Load test the addList proc
DECLARE @i INT = 0

WHILE @i < 1000
BEGIN

    DECLARE @item INT, @items INT
    DECLARE @listId INT, @tvp AS dbo.tvp_myList

    -- Initialise
    SELECT @items = ( RAND() * 9 ) + 1, @item = 1   -- random number between 1 and 10
    DELETE @tvp

    PRINT @items

    WHILE @item <= @items
    BEGIN

        PRINT CHAR(9) + CAST( @item AS VARCHAR(5) )

        INSERT INTO @tvp VALUES ( @item, RAND() * 10000 )

        SET @item += 1

    END

    EXEC dbo.usp_addList NULL, @tvp, NULL

    SET @i += 1

END
GO

-- Load Tests END
------------------------------------------------------------------------------------------------

-- Visualise your lists
SELECT
    l.listId,
    l.listName,
    l.dateAdded,
    (
    SELECT
        li1.listId  "@listId",
        COUNT(*) AS "@count",
        (
        SELECT
            itemOrderId AS "@itemOrderId",
            itemValue AS "@itemValue"
        FROM dbo.listItems li2
        WHERE li1.listId = li2.listId
        ORDER BY itemOrderId
        FOR XML PATH('listItem'), TYPE
        )
    FROM dbo.listItems li1
    WHERE l.listId = li1.listId
    GROUP BY li1.listId
    FOR XML PATH('listItems'), TYPE
    ) listItems
FROM dbo.lists l
GO

-- END
------------------------------------------------------------------------------------------------

I'm a big proponent of XML but it is the wrong choice for this design.  XML can be appropriate for semi-structured, but your design is actually really structured.  The overhead of constantly shredding XML for comparison would be too much, plus you can't directly compare once piece of XML with another easily in SQL Server.  Where XML would be useful here, is visualising your lists, as I've done at the end of my demo.

HTH


What are the "additional actions" available for "Recovery Pending" databases?

Since this is a test server, the best choice would be simply to run RESTORE DATABASE [db_name] WITH RECOVERY; for each database that is in Recovery Pending state.  Check the Microsoft Docs page for RESTORE DATABASE with recovery only for further details.

This code will generate the statements for all databases in recovery pending:

SELECT 'RESTORE DATABASE ' + quotename(d.name) + ' WITH RECOVERY;'
FROM sys.databases d
WHERE d.state_desc = 'RECOVERY_PENDING'
ORDER BY d.name;


Replication from EC2 Master to RDS Slave - Connecting to master

Since you control my.cnf on EC2, you have two options, neither of which require restarting mysql
OPTION #1 : Change old_password Protocol for EC2

Stop replication to the RDS Slave
As root@localhost run this on EC2 : SET GLOBAL old_passwords = 0;
Start replication to the RDS Slave

OPTION #2 : Lower the Security for EC2

Stop replication to the RDS Slave
As root@localhost run this : SET GLOBAL secure_auth = OFF;
Start replication to the RDS Slave

For more information, please read the old_passwords and secure_auth MySQL Documentation.
Give it a Try !!!
UPDATE 2017-07-20 16:32 EDT
Neither of these methods work for MySQL 5.7 as both options are deprecated.


Simplifying Function with Recursive CTE and/or Window Function

You need an extra column to carry along the running total (fiddle).

In the recursive part of the CTE below R refers to the "previous" row and A the current one so referencing the column from R is your SUM(E1, E2, ... En-1).

WITH R
     AS (SELECT N,
                B,
                E = B,
                RunningTotalE = B
         FROM   A
         WHERE  N = 0
         UNION ALL
         SELECT A.N,
                A.B,
                E = A.B * ( 1 - R.RunningTotalE ),
                RunningTotalE = A.B * ( 1 - R.RunningTotalE ) + R.RunningTotalE
         FROM   R
                JOIN A
                  ON A.N = R.N + 1)
SELECT N,
       B,
       E = CAST(E AS DECIMAL(10,9))
FROM   R


SELECT other rows based on one matching row

if i'm reading your question properly, you know there is a 'turkey' tag and want your query to return every row that shares the same area_id but with a tag that is not 'turkey'. Right?
Try...

SELECT a.* FROM tagtable a,
(
SELECT * FROM tagtable WHERE tag = 'turkey'
) As z
WHERE a.area_id = z.area_id
AND a.tag <> z.tag;

hope that helps...


Downlevel versions of SSMS cannot always connect to uplevel versions of SQL.  When configuring replication, please ensure you are using the version of SSMS from your Distributor.  In your case, based on the error message, you are using the SSMS from the Express version of SQL.  Instead, use the version from your Distributor instance.


Execute Stored Procedure against CMS from within a SQL Server Agent job

The answer to this is: not possible, however my research lead me to these tables in MSDB which are used to populate the CMS registered servers. These tables can be referenced in your code:

sysmanagement_shared_registered_servers_internal

sysmanagement_shared_server_groups_internal


How to do an efficient case insensitive search

There is no problem using LIKE, as long as it's in a format such as:

SELECT * FROM My_Table WHERE City_Name LIKE 'Abc%';

This can use an index. However, the following cannot.

SELECT * FROM My_Table WHERE City_Name LIKE UPPER('Abc%');

The use of a function (UPPER()) negates the index - in technical terms, the query is not sargable. Note that the query is also non-sargable in a case such as:

SELECT * FROM My_Table WHERE City_Name LIKE  '%Abc..';

The wild card at the beginning of the search string renders any index non-functional.

An ON INSERT and and ON UPDATE trigger into a new field (up_City_Name) setting the input City_Name to UPPER(input City_Name).

That way, you will always know that your City_Name is in upper case, and your server will always be able to use an index, provided you search on the new field up_City_Name.


PostgreSQL read-only user for pg_dumpall

Backups from a normal user are a nightmare. If you're using PostgreSQL on Linux, I'd recommend relying on sudo (and the /etc/sudoers file) to accomplish this, so that a given specific user can sudo -u postgres pg_dumpall <params> with specific parameters, so that's all they can do as user postgres (and therefore have no write access to the database).


Restore Standard/Enterprise Dump to Express

As far as I can tell, yes - you can import a DMP file whose origin is Standard or Enterprise edition into an Express edition database.

However, there are certain restrictions. One of them seems to be OK - size of user data (you have less than 1 GB, while 11g XE accepts up to 11 GB).

If tables are partitioned in the source database, they won't be imported as XE doesn't support partitioning. (Though, if you pre-create such a table and import only data, that should also be OK.)

The same goes for other features that aren't supported by XE (such as internal Java virtual machine, materialized view query rewrite) - they won't be imported.

If tablespace names are different in the source and XE database, don't forget to include the REMAP_TABLESPACES parameter.

I suggest you give it a try; XE is a nice tool to play with. Review the log file - it'll tell you which objects weren't imported and why. Good thing is that - even if something fails to import - it won't stop other objects from importing. Then you'll know whether the final result suits your needs or not.


Index rebuild after SQL Server upgrade to 2014

You can just rebuild indexes as you normally would as part of your standard maintenance schedule, this doesn't need to be done specifically due to the upgrade. That being said it depending the size of your tables\indexes and how much time you have for your upgrade, it can't hurt to start with fresh rebuilt indexes if you can work it into your upgrade plan. But no, it's not a requirement.

You definitely need to update your statistics however, as this can lead to poor plan generation by the optimizer after upgrading. This needs to happen before you do any index rebuilding. I'll try and dig up the link I had that gave a more detailed explanation.

Here is a great article by Thomas LaRock that can serve as a basic upgrade checklist to build on, to ensure you haven't missed anything:

http://thomaslarock.com/2014/06/upgrading-to-sql-server-2014-a-dozen-things-to-check/


SSIS Data Source: Embedded Queries, Stored Procedures, or Functions

I would recommend using stored procedures.  As far as issues with the metadata, this can occur when temporary tables are used.  Using the WITH RESULT SETS option to execute the stored procedure will allow you to define the metadata of the columns instead and SSIS can bypass this step.  An example of this follows.  Also note that if any column definitions were to change or if new columns are added, this would need to be updated as well.

You may also run into problems with using parameters with an OLE DB source as in your screenshot.  However this can be overcome by using the "SQL command from variable" option for the Data Access field.  A string variable can be created as an expression that executes the stored procedure using a parameter sent to the package (or project).  An example of this is below.  This example uses the DT_STR function to cast the numeric parameter to a string in order to concatenate it.  This is not necessary for string parameters or course, I only included this in case you use numeric parameters.  The WITH RESULT SETS option can also be used in an expression concatenating it with the parameter following the same method.

WITH RESULT SETS

   EXEC ('dbo.YourSP')
    WITH RESULT SETS
      (
        (
            ColumnA INT,
            ColumnB VARCHAR(250),
            ColumnC DATETIME
        )
      );

Variable Expression using Parameter

"EXEC dbo.YourSP " +  (DT_STR, 10, 1252)@ @[$Package::YourParameter]

Functions Followup Response:
If you prefer to use functions and they better suite your needs you can go that route. I tend to prefer stored procedures in general as they offer for flexibility (perform DML/DDL, use temp tables, etc.). If you do use functions, I'd recommend using an inline over a multi-statement table valued version. The inline function is essentially treated like a view and will use the proper statistics to create better execution plans, whereas multi-statement functions won't get these benefits.


I think I found the problem, the other session did not only create those tables but also modified the Schema search path (which was not active in the existing session of course)


Select the minimum of a calculated distance value without sorting


I had something similar happen recently with a table of 3.5 million rows. My update would never finish. After a lot of experimenting and frustration, I finally found the culprit. It turned out to be the indexes on the table being updated.

The solution was to drop all indexes on the table being updated before running the update statement. Once I did that, the update finished in a few minutes. Once the update completed, I re-created the indexes and was back in business. This probably won't help you at this point but it may someone else looking for answers.

I'd keep the indexes on the table you are pulling the data from. That one won't have to keep updating any indexes and should help with finding the data you want to update. It ran fine on a slow laptop.


migrate data from production to development db

First and potentially the easiest option available:
Flash drives are dirt cheap. Literally, you can get a 4gb flash drive for less than top soil.

If you do not have physical access to a USB port then install BIDS or SSDT on your dev machine if you do not already have installed and create a simple Source to Target SSIS package. You need login access to the separate servers and you can copy individual tables as needed.  Eliminates cross server queries/linked servers and can minimize your network use to only necessary data.

Follow a standard BI staging table package for optimal results:
Steps

Truncate destination table
Extract data from source
Write to destination

If you have table constraints I would think any legitimate testing would require them to exist in your test environment, but to make your data import as simple and quick as possible it may be advantageous to drop them temporarily, until you have all your needed data. The following scripts can drop and then re-create your FK constraints. (I've had it saved for a while but took it from a co-worker or blog, if anyone recognizes I'd be happy to update with a source credit.)

SET NOCOUNT ON

DECLARE @table TABLE(
   RowId INT PRIMARY KEY IDENTITY(1, 1),
   ForeignKeyConstraintName NVARCHAR(200),
   ForeignKeyConstraintTableSchema NVARCHAR(200),
   ForeignKeyConstraintTableName NVARCHAR(200),
   ForeignKeyConstraintColumnName NVARCHAR(200),
   PrimaryKeyConstraintName NVARCHAR(200),
   PrimaryKeyConstraintTableSchema NVARCHAR(200),
   PrimaryKeyConstraintTableName NVARCHAR(200),
   PrimaryKeyConstraintColumnName NVARCHAR(200)
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
   U.CONSTRAINT_NAME,
   U.TABLE_SCHEMA,
   U.TABLE_NAME,
   U.COLUMN_NAME
FROM
   INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
         ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
   C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
   PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
   @table T
      INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
         ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintTableSchema  = TABLE_SCHEMA,
   PrimaryKeyConstraintTableName  = TABLE_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
      ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
   PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
      ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--SELECT * FROM @table

--DROP CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
   DROP CONSTRAINT ' + ForeignKeyConstraintName + '

   GO'
FROM
   @table

--ADD CONSTRAINT:
SELECT
   '
   ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
   ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')

   GO'
FROM
   @table

GO


Postgres could not receive data from client: Connection timed out

A few possibilities:

Your application closes connections without telling the PostgreSQL server it's doing so, by simply closing the network socket. It should send a PQclose(conn) if using libpq, or whatever equivalent your language/driver has.
Network address translation timeouts on idle connections or other related network issues cause the TCP connections to break, so client and server lose communication with each other.
A firewall on the client or server is blocking or disrupting connections after they're established (maybe malfunctioning IDS?)


This code should achieve what you want

SELECT [1] AS [Combobox1], [2] AS [Combobox2], [3] AS [Combobox3]
FROM
(
    SELECT [name],
        NTILE(3) OVER (ORDER BY ID) AS [CB]
    FROM #Temp
    WHERE [ID]%2 <> 0
) src
PIVOT
(
    MAX([Name]) FOR [CB] IN ([1], [2], [3])
) pvt
UNION
SELECT [1] AS [Combobox1], [2] AS [Combobox2], [3] AS [Combobox3]
FROM
(
    SELECT [name],
        NTILE(3) OVER (ORDER BY ID) AS [CB]
    FROM #Temp
    WHERE [ID]%2 = 0
) src
PIVOT
(
    MAX([Name]) FOR [CB] IN ([1], [2], [3])
) pvt

Sample output:

Combobox1 Combobox2 Combobox3
--------- --------- ---------
a         c         e
b         d         NULL


Clustered Index - Query Performing Index Scan

The Execution plan shows a non clustered index scan:

Because you have a implicit conversion of your first index seek column SQL Server decided to do a full non clustered index scan. It's not doing a clustered index scan because the non clustered index is a covering index. 1)

You can see that the column customerID needs to be converted to a NVARCHAR(50) datatype.(red conversion). Because the column first needs to be converted, it can't be used. Since it's the first column in your index, it effectively makes the index useless for seeks or estimates.

Why is SQL Server doing a implicit conversion of the column? Because on the right side of the = sign you feed it a Nvarchar. Your not doing this explicit, since also the parameter @CustomerID first gets implictly converted to a NVARCHAR(3) (blue conversion).

I presume this is because your are filling @CustomerID with a Unicode value. However, It would suprise me that you would use a unicode values and yet have non unicode datatypes defined in your table. Maybe somebody else could confirm the reason for the blue conversion.

1) I deleted a wrong assumtion about the relevance of the amount of rows returned that Martin Smith was nice enough to point out to me.


I just removed the locationcode in select and group by clause so they will join eventually and that solved my problem.

select b.Barcode,a.ItemCode,b.Color,b.Size,b.Class,c.price,
SUM(case when e.description = 'Selling Quantity' and d.LocationCode = e.LocationCode         then 1 else 0 end) as 'Selling Qty',
SUM(case when e.description = 'Stock Quantity' then 1 else 0 end) as 'Stock Quantity'
from masterfile.item a
left join masterfile.itemdetail b on a.ItemCode = b.ItemCode
left join masterfile.itemprice c on a.ItemCode = c.Itemcode and
b.Color = c.ItemColor and b.Size = c.ItemSize and b.Class = c.ItemClass
left join masterfile.locationinv d on a.itemcode = d.itemcode and
c.ItemColor = d.Color and c.ItemSize = d.Size and c.ItemClass = d.Class
left join masterfile.location e on d.locationcode = e.locationcode
group by b.Barcode,a.itemcode,b.color,b.size,b.class,c.price


This article has a great step through exactly how to enable TDE.  This includes generating keys/certificates, everything.  When you turn TDE on, it will start encrypting in the background as well as when pages are accessed or flushed to disk.

Also, sidenote, TempDB also gets encrypted so if you have other DBs/SPs that aren't encrypted and use TempDB, there may be a slight performance hit when you lay data down in TempDB and pick it back up.  You're on 2016 so you still get native backup compression with TDE (hooray!)

http://joeysbasisblog.blogspot.com/2013/10/enable-or-disable-transparent-data.html


How should I model an "either/or" relationship?

The way to manage this is that your sub-types have to be determined by the super-type (i.e. the PK of the sub-type is also a FK from the sub-type to the super-type.)

The challenge is understanding whether something is truly mutually exclusive or not.  The attributes of sub-types should apply only to those sub-types, but it may well be that some sub-types are mutually exclusive and some aren't.

If you have some mutually exclusive sub-types, then you can use a partitioning attribute on the super-type to indicate which of the (two or more) mutually exclusive sub-types apply.  This partitioning attribute can be used in with constraints or triggers to enforce the mutual exclusivity.

If you have sub-types that are not mutually exclusive, then they can exist without using any partitioning attribute.

Consider this data model:

You have three super-types, but the FREE_SOFTWARE and NON-FREE_SOFTWARE types are mutually exclusive, based on the SOFTWARE.free_not_free flag partitioning attribute.  Any given piece of software is also potentially an OPERATING_SYSTEM, regardless of whether or not it is free.


The exact behaviour of concurrent modifications depends, in part, on the access path chosen by SQL Server to locate records to change.

If SQL Server uses the clustered index to locate data to change, locks will generally be taken on clustered index keys (and/or pages, etc.). If SQL Server locates rows to change using a nonclustered index, locks will be taken on the nonclustered index. In both cases, exclusive locks are also generally taken on the clustered index before the modification is actually performed.

Anticipating locking behaviour can be a fun and educational exercise, but it often the wrong question to ask. If you are experiencing blocking on locks where it does not seem necessary, then it might be the right question, but it is a very advanced one, requiring detailed internal knowledge to fully explain. I'll show an example based on your data later on.

Most often, the real question is "which isolation level should I use?". Locks are an implementation detail, used to provide the guarantees offered by the various isolation levels. The guarantees are the important thing. You should understand the different behaviours that are possible under each isolation level, and then make an informed choice. Please refer to that link for all the details.

When modifying data, RCSI behaves the same as standard READ COMMITTED. It will block if it needs to read something that is currently locked by another session's uncommitted changes. Once the blocking session commits or rolls back its changes, the blocked update continues, reading the committed values present at the time the blocking lock was released. This behaviour is required to prevent "lost updates", which are not allowed under any isolation level supported by SQL Server.

The following demo shows that the precise blocking behaviour depends on which locks are needed according to the query plan selected by the optimizer. In some cases, the update will block, in other cases, it will not. SQL Server always respects the guarantees provided by the user's isolation level, regardless of the implementation-defined locking behaviour.

Test Table and Data

CREATE TABLE dbo.Table1
(
    PKcol integer PRIMARY KEY,
    NonPKCol integer NULL UNIQUE,
    col1 integer NULL
);

INSERT dbo.Table1
    (PKcol, NonPKCol, col1)
VALUES
    (1,1,0),
    (2,2,0),
    (3,3,0),
    (4,4,0),
    (5,5,0);

Uncommitted Update

On a separate connection, run:

BEGIN TRANSACTION;
UPDATE dbo.Table1 SET NonPKCol = 997 WHERE PKcol = 3;
UPDATE dbo.Table1 SET NonPKCol = 998 WHERE NonPKCol = 3;
UPDATE dbo.Table1 SET NonPKCol = 999 WHERE NonPKCol = 5;

Note the lack of a COMMIT or ROLLBACK TRANSACTION.

Test Results

-- (1) Succeeds (no conflicting locks encountered)
update table1 set col1 = col1 + 1 where PKcol < 3

-- (2) Waits for an X lock for clustered index key PKcol = 3
update table1 set col1 = col1 + 1 where PKcol = 3

-- (3) Waits on U lock for clustered index key PKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol < 3

-- (3) Succeeds when read access is by NONCLUSTERED index
update t set col1 = col1 + 1 from table1 t with(index(2)) where NonPKcol < 3

-- (4) Blocks on U lock for NONCLUSTERED index key NonPKcol = 3
update table1 set col1 = col1 + 1 where NonPKcol = 3

-- (5) Blocks on U lock for nonclustered index key NonPKcol = 5
update table1 set col1 = col1 + 1 where PKcol < 3 and NonPKcol = 5

-- (5) Succeeds when access is by CLUSTERED index
update t set col1 = col1 + 1 from table1 t with(index(1)) where PKcol < 3 and NonPKcol = 5


SQL: What is slowing down INSERTs if not CPU or IO?

I know it's an old Question but this might still help searchers and it's a problem that pops up every now and then.

The main reason why you are hitting a performance ceiling without you seeing any resource bottleneck is because you've reached the limit of what is possible to process within one session single thread. The loop isn't processed in parallel, but all inserts are done serially.

In my case, it takes 36 seconds to insert 3 million rows. That means 36/30000000 = 0.000012 seconds per row. That's pretty fast. On my system, it simply takes 0.000012 to go through all the steps that are necessary.

The only way to get it done faster is start up a second session in parallel.

If I start 2 sessions in parallel both doing 15 million inserts. Both of them finish in 18 seconds. I could scale out more, but my current test setup is hitting 95% cpu with two parallel session, so doing 3 would skew the results since I would hit a CPU bottleneck.

If I start 2 parallel session both inserting 3 million rows, they both finish in 39 seconds. so that is now 6 million rows in 39 seconds.

Okay, that still leaves us with the NETWORK_IO wait showing up.

The NETWORK_IO waits are added by the fact that you are using extended events to trace them. In my case the insert takes 36 seconds (on avg). When using the extended event way (from the link above in the very first comment) this is what is registered:

Wait Type             Wait Count  Total Wait Time (ms) Total Resource Wait Time (ms) Total Signal Wait Time (ms)
NETWORK_IO            3455        68808                68802                         6
PAGEIOLATCH_SH        3           64                   64                            0
PAGEIOLATCH_UP        12          58                   58                            0
WRITE_COMPLETION      8           15                   15                            0
WRITELOG              3           9                    9                             0
PAGELATCH_UP          2           4                    4                             0
SOS_SCHEDULER_YIELD   32277       1                    0                             1
IO_COMPLETION         8           0                    0                             0
LATCH_SH              3           0                    0                             0
LOGBUFFER             1           0                    0                             0

You can see that 68 seconds of NETWORK_IO is registered. But since the insert loop is a single threaded action that took 36 seconds, this can't be. (Yes, multiple threads are used, but the operations are serial, never in parallel, so you can't acummulate more wait time than the total duration of the query)

If I don't use extended events but just the wait stats DMVs on a quiet instance (with just me running the insert) I get this:

Wait Type                   Wait Count  Total Wait Time (ms)  Total Resource Wait Time (ms) Signal Resource Wait Time (ms)
SOS_SCHEDULER_YIELD             8873                 0.21                                    0.01                                    0.20
PAGEIOLATCH_UP                  3                    0.02                                    0.02                                    0.00
PREEMPTIVE_OS_AUTHENTICATIONOPS 17                   0.02                                    0.02                                    0.00
PAGEIOLATCH_SH                  1                    0.00                                    0.00                                    0.00

So the NETWORK_IO you were seeing in the extended events log, wasn't related to your insert loop. (If you wouldn't turn nocount on, you would have massive async network IO waits, +1 Martin)

However I don't know why the NETWORK_IO show up in the extended event trace. Sure the writing out to a async file target of the events accumulates ASYNC_NETWORK_IO, but surely this is all done on a differenent SPID then the one we are filtering on. I might ask this as a new question myself)


In DB2 LUW 9.5, is there any way to change the collating sequence without recreating the db?

No, you can't change the collating sequence without recreating the database.


You cannot change the rest of rows on table with its own trigger- that is by design. Check this answer.

That also provides an alternative method: disable the INSERT privileges on that table and allow inserting only by using a stored procedure, that will do as you want (although I do not like much the logic behind it):

CREATE PROCEDURE insert_inventoryproposalprocessed (...required input values...)
    BEGIN
      INSERT INTO inventoryproposalprocessed VALUES (...input variables...)
      DECLARE size INTEGER;
      SELECT sum( data_length + index_length ) / 1024 / 1024 INTO size FROM information_schema.TABLES where table_schema like 'd40013_x_frodo' ;
      IF size > 99 THEN
         DELETE FROM inventoryproposalprocessed order by _id limit 25;
      END IF;
    END;