MySQL Workbench secretly running SHOW INDEX after every query A few days ago, I revised some innodb_stats_* parameters and ran mysqlcheck --analyze --all-databases on some Percona 5.5.38 servers. Now, whenever I execute any simple query in MySQL Workbench, it seems to execute a background query of SHOW INDEX ON t1;  Needless to say, this caused some issues when it blocked updates to the table on production servers.

I have ascertained these things:

it happens with any account with sufficient priviledges
it doesn't happen from the command line
it happens for other users
it happens even after I upgrade to 6.2.4 and delete all the settings
it DOESN'T happen against my Percona 5.6 database
SHOW INDEX starts about 5 seconds AFTER my query

I haven't been able to find any explanation for this behavior on the net, so wanted to see if any experienced DBA's have run into this.

Current stats related settings:

innodb_stats_auto_update 0
innodb_stats_method  nulls_equal
innodb_stats_on_metadata ON
innodb_stats_sample_pages    8
innodb_stats_update_need_lock    1
innodb_use_sys_stats_table   OFF

Thanks for any pointers anyone can provide.

UPDATE
After looking through the source code, it turns out that the SHOW INDEX happens for every query on a single table. It checks to see if the selected columns contain a primary key or other unique index, so it can decide whether you can edit the data.

The source code contains this comment:

// XXX this can be slow because of the I_S queries, depending on the server

I went to see why my INFORMATION_SCHEMA was slow, and found a helpful post from Percona

It turns out that innodb_stats_on_metadata had gotten turned on during this process, which causes MySQL to recalculate the statistics every time INFORMATION_SCHEMA is accessed. So I just turned it back off.

What a nightmare.

Scanning the source code of MySQL Workbench you can see a few places where SHOW INDEX is sent out. The application needs to run quite a number of queries for its tasks, e.g. the index view or the table editor. Some of them are only active when you open a view from the management section. Others, like the SQL editor need a SHOW INDEX to find info about PK columns which is needed to decide if a resultset returned for a query can be edited.


Database encryption and key management with pg_crypto I am looking at the possibility of having to store root passwords for virtual machines in reversibly encrypted format in a database.  The reason is that local logins will sometimes be required to machines as much as they are discouraged and we need to be able to look up root passwords.  Access of course would be heavily restricted to a few admins.  These would be unused except when the vm is inaccessible over the network.  Synchronizing users and passwords in this regard poses similar problems, namely that if the network is down, how does the sudoer log in with the guaranteed most recent password?

What is the best way to plan for keys to be rotated?  Should I just use a passphrase that every admin has?  This raises some red flags in my mind.  Is there a way to securely manage keys such that admins can set their own passwords?  Maybe use public key encryption and encrypt a symmetric key with each admin's public key?

Here I am looking just for general outlines of a solution regarding key management of encrypted sensitive data.  If I can't figure out how to implement from that level I can ask more questions.

Note that I am not a crypto systems or security expert. This is what I've seen done and it makes sense, but I cannot claim it doesn't have security issues beyond what I outline below. As always, get a proper security audit if it's important.

In the following, "credential" can refer to ssh keys, passwords, or whatever needs storing.

The typical model is to use a couple of levels of crypto.

For each user, generate and store a public/private keypair
Store the public part of the user's key in the clear in the user's record
Store the private part of the user's key encrypted to the user's passphrase in the user's record.
Encrypt each credential to a symmetric key that's unique to that credential
Encrypt each credential's symmetric key to the public key of every user who should have access to that key, and store the resulting encrypted material in a user_credentials mapping table.

Essentially, you maintain a keyring, much like the gpg keyring where the public part is in cleartext and the private part is encrypted to a passphrase. You should make sure the passphrase selected by each user is strong.

Now you can generate a symmetric key for each encrypted credential. Encrypt this key to every user's public key and store it in a user-credentials join table. Never store this symmetric key unencrypted, and use a different symmetric key for each credential.

You land up with something like this

[user]
user_id
username
login_password_salt
login_password_hash
public_key_text
private_key_sym_encrypted_to_passphrase

[user_credential]
user_id
credential_id
credential_key_encrypted_to_user_public_key

[credential]
credential_id
credential_data_encrypted_to_credential_key

There are quite a few advantages to doing things this way:

If a dump of your database is leaked your credentials are still safe, or as safe as your passphrases and key strengths.
Since you have a different symmetric key for each credential you can control which users have access to which credentials
Any user with access to a given credential can give another user access to it; they just decrypt it using their key and encrypt it to the other user's public key. You don't need to get everybody together to add credentials or add new users.
Because you're encrypting each credential to a symmetric key, and then encrypting that key to the user's key, you can change the stored credential without having to re-encrypt it to each user's key. It just makes updating stored credentials more convenient.

However, if the database host is compromised, an attacker can easily extract the passwords any given user has access to as soon as that user logs in. They capture the user's passphrase by wrapping functions or by enabled detailed logging and searching the logs. Then they can decrypt passwords in a stolen dump or from the live DB. A compromised database host is no danger until a user logs in to use it but then all is lost.

This means that the host of the database is security critical. Leaking the dump isn't fatal to security, but someone being able to modify the code running on the DB is a fatal security breach.

In addition to using your credentials management DB to extract ssh key material, passwords, etc when you need it, you can also use it as an authentication proxy where it can auth against a service without your user having to ever be able to learn the credential used. For example, your app might decrypt an ssh key, add it to an ssh agent, and use the decypted key to log in to a server via the agent without the user ever being able to see and access the key directly.


Do all users need access to the postgres database in pg_hba.conf? I was just testing the user creation process in pgAdmin. I have a database db1 for which I want to create a user to be able to connect to it. I want this to be the only database the user can connect to.

After creating the user, I opened my pg_hba.conf file and added a couple of lines:

host    db1 dbuser  127.0.0.1/32    md5
host    db1 dbuser  ::1/128         md5

The postgres user has access to all the databases:

host    all postgres    127.0.0.1/32    md5
host    all postgres    ::1/128         md5

When I connect to the server through pgAdmin, I get an error indicating that dbuser doesn't have access to the postgres database.

Is this normal? Should dbuser even require access to the postgres database, when I want him to only access db1? As soon as I add postgres to the list of databases for dbuser, I can connect.

What you see is pgAdmin connecting to the configured  Maintenance DB - which is postgres by default. Select a server in the object browser pane and choose "Properties" from the context menu (right-click). The drop-down menu only offers the typical choices, but you can just type in any database name.

If a user's access is limited to one database in pg_hba.conf (which is the most efficient place to do that), you can make it work with pgAdmin by configuring this one database as maintenance db. Nothing bad will come of it.

The other option is to grant access to the maintenance DB (postgres or whatever) in pg_hba.conf additionally.

You can also open all gates in pg_hba.conf and regulate access with database permissions, like @a_horse explained in his answer. This is easier to manage, but restricting access in pg_hba.conf is safer and faster - more robust against DoS attacks and heavy load. Won't make a big difference in most cases, though.

Quoting the documentation of pgAdmin 1.22 (final release of pgAdmin III):

  The maintenance DB field is used to specify the initial database that
  pgAdmin connects to, and that will be expected to have the pgAgent
  schema and adminpack objects installed (both optional). On PostgreSQL
  8.1 and above, the maintenance DB is normally called 'postgres',
  and on earlier versions 'template1' is often used, though it is
  preferable to create a 'postgres' database for this purpose to avoid
  cluttering the template database.


Reusing sql query values for another query in single request I have an sql table for some chats with a table messages, containing  id int pk, chat_id int, created_at timestamp.

Given a message id, is it possible to select it, along with 5 messages after it in same chat, in single query?

Or can I somehow combine the two following subqueries (having starting_message_id as an input)?

SELECT * FROM messages WHERE chat_id IN (
    SELECT chat_id from messages where id='starting_message_id'
) AND created_at > (
    SELECT created_at from messages where id='starting_message_id'
) ORDER BY created_at ASC LIMIT 5;

I feel that only two requests are sufficient:

selecting chat_id and created_at columns by chat id
selecting some messages having all data known

What would the correct optimised syntax be?

I believe you want something like:

SELECT msg.*
FROM messages msg
JOIN (SELECT chat_id, created_at
  FROM messages
  where id='starting_message_id') nested
ON (msg.chat_id=nested.chat_id and msg.created_at>=nested.created_at)
ORDER BY msg.created_at
LIMIT 6;


Compute the total of SUM(column) I have this code that sums up the qty for a certain item (itemid) and by its product date code (proddte).

select sum(qty), itemid, proddte
from testtable where ....
group by itemid, proddte

What I want to do is to get the total of all qty regardless of itemid/proddte. I have tried:

select sum(qty), itemid, proddte, sum(qty) over() as grandtotal
from testtable
where ....
group by itemid, proddte

But it says I should also have qty in the group by clause. If I did that, the result will not be equal to my expected result.

It does not absolutely need to be represented as a separate column, with the same value in every row. Any representation is accepted as long as I can display the overall total.

This is valid syntax, too:

       sum(sum(qty)) over ()

It's a bit confusing when one sees it at first but you only have to remember that the window functions - e.g. sum() over () - are applied after the group by so everything that can appear in the select list of a group by query can be placed inside a window aggregate. So (the qty cannot but) the sum(qty) can be placed inside sum() over ():

select sum(qty), itemid, proddte,
       sum(sum(qty)) over () as grandtotal
from testtable
where ....
group by itemid, proddte ;

Having said that, I'd prefer the GROUPING SETS query provided by Aaron Bertrand. The total sum needs to be shown once and not in every row.

Also note that while the sum of sums can be used to calculate the total sum, if you wanted the total count, you'd have to use the sum of counts (and not the count of counts!):

sum(count(*)) over ()  as grand_count

And if one wanted the average over all the table, it would be even more complicated:

sum(sum(qty)) over ()
/ sum(count(qty)) over ()  as grand_average

because the average of averages is not the same as the average over all. (If you try the avg(avg(qty)) over () you'll see that it may yield a different result than the above grand average.)


Measure the size of a PostgreSQL table row I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor.
I need all of the fields (or nearly all of them), so selecting just a subset isn't a quick fix. Selecting the fields that I want is still slow.
Here's my table schema minus the names:
integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   |
integer                  | not null default 0
text                     | default '{}'::text
text                     |
timestamp with time zone |
integer                  |
timestamp with time zone |
integer                  |

The size of the text field may be any size. But still, no more than a few kilobytes in the worst case.
Questions

Is there anything about this that screams 'crazy inefficient'?
Is there a way to measure page size at the Postgres command-line to help me debug this?

Q2: way to measure page size
PostgreSQL provides a number of Database Object Size Functions. I packed the most interesting ones in this query and added some Statistics Access Functions at the bottom. (The additional module pgstattuple provides more useful functions, yet.)
This is going to show that different methods to measure the "size of a row" lead to very different results. It all depends on what you want to measure, exactly.
This query requires Postgres 9.3 or later. For older versions see below.
Using a VALUES expression in a LATERAL subquery, to avoid spelling out calculations for every row.
Replace public.tbl with your optionally schema-qualified table name to get a compact view of collected row size statistics. You could wrap this into a plpgsql function for repeated use, hand in the table name as parameter and use EXECUTE ...
SELECT l.metric, l.nr AS bytes
     , CASE WHEN is_size THEN pg_size_pretty(nr) END AS bytes_pretty
     , CASE WHEN is_size THEN nr / NULLIF(x.ct, 0) END AS bytes_per_row
FROM  (
   SELECT min(tableoid)        AS tbl      -- = 'public.tbl'::regclass::oid
        , count(*)             AS ct
        , sum(length(t::text)) AS txt_len  -- length in characters
   FROM   public.tbl t                     -- provide table name *once*
   ) x
CROSS  JOIN LATERAL (
   VALUES
     (true , 'core_relation_size'               , pg_relation_size(tbl))
   , (true , 'visibility_map'                   , pg_relation_size(tbl, 'vm'))
   , (true , 'free_space_map'                   , pg_relation_size(tbl, 'fsm'))
   , (true , 'table_size_incl_toast'            , pg_table_size(tbl))
   , (true , 'indexes_size'                     , pg_indexes_size(tbl))
   , (true , 'total_size_incl_toast_and_indexes', pg_total_relation_size(tbl))
   , (true , 'live_rows_in_text_representation' , txt_len)
   , (false, '------------------------------'   , NULL)
   , (false, 'row_count'                        , ct)
   , (false, 'live_tuples'                      , pg_stat_get_live_tuples(tbl))
   , (false, 'dead_tuples'                      , pg_stat_get_dead_tuples(tbl))
   ) l(is_size, metric, nr);
Result:
              metric               | bytes    | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 44138496 | 42 MB        |            91
 visibility_map                    |        0 | 0 bytes      |             0
 free_space_map                    |    32768 | 32 kB        |             0
 table_size_incl_toast             | 44179456 | 42 MB        |            91
 indexes_size                      | 33128448 | 32 MB        |            68
 total_size_incl_toast_and_indexes | 77307904 | 74 MB        |           159
 live_rows_in_text_representation  | 29987360 | 29 MB        |            62
 ------------------------------    |          |              |
 row_count                         |   483424 |              |
 live_tuples                       |   483424 |              |
 dead_tuples                       |     2677 |              |
For older versions (Postgres 9.2 or older):
WITH x AS (
   SELECT count(*)               AS ct
        , sum(length(t::text))   AS txt_len  -- length in characters
        , 'public.tbl'::regclass AS tbl      -- provide table name as string
   FROM   public.tbl t                       -- provide table name as name
   ), y AS (
   SELECT ARRAY [pg_relation_size(tbl)
               , pg_relation_size(tbl, 'vm')
               , pg_relation_size(tbl, 'fsm')
               , pg_table_size(tbl)
               , pg_indexes_size(tbl)
               , pg_total_relation_size(tbl)
               , txt_len
             ] AS val
        , ARRAY ['core_relation_size'
               , 'visibility_map'
               , 'free_space_map'
               , 'table_size_incl_toast'
               , 'indexes_size'
               , 'total_size_incl_toast_and_indexes'
               , 'live_rows_in_text_representation'
             ] AS name
   FROM   x
   )
SELECT unnest(name)                AS metric
     , unnest(val)                 AS bytes
     , pg_size_pretty(unnest(val)) AS bytes_pretty
     , unnest(val) / NULLIF(ct, 0) AS bytes_per_row
FROM   x, y

UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;
Same result.
Q1: anything inefficient?
You could optimize column order to save some bytes per row, currently wasted to alignment padding:
integer                  | not null default nextval('core_page_id_seq'::regclass)
integer                  | not null default 0
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   |
text                     | default '{}'::text
text                     |
timestamp with time zone |
timestamp with time zone |
integer                  |
integer                  |

This saves between 8 and 18 bytes per row. I call it Column Tetris. See:

Configuring PostgreSQL for read performance
Calculating and saving space in PostgreSQL

Also consider:

Would index lookup be noticeably faster with char vs varchar when all values are 36 chars


Date range rolling sum using window functions I need to calculate a rolling sum over a date range. To illustrate, using the AdventureWorks sample database, the following hypothetical syntax would do exactly what I need:

SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 = SUM(TH.ActualCost) OVER (
        PARTITION BY TH.ProductID
        ORDER BY TH.TransactionDate
        RANGE BETWEEN
            INTERVAL 45 DAY PRECEDING
            AND CURRENT ROW)
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

Sadly, the RANGE window frame extent does not currently allow an interval in SQL Server.

I know I can write a solution using a subquery and a regular (non-window) aggregate:

SELECT
    TH.ProductID,
    TH.TransactionDate,
    TH.ActualCost,
    RollingSum45 =
    (
        SELECT SUM(TH2.ActualCost)
        FROM Production.TransactionHistory AS TH2
        WHERE
            TH2.ProductID = TH.ProductID
            AND TH2.TransactionDate <= TH.TransactionDate
            AND TH2.TransactionDate >= DATEADD(DAY, -45, TH.TransactionDate)
    )
FROM Production.TransactionHistory AS TH
ORDER BY
    TH.ProductID,
    TH.TransactionDate,
    TH.ReferenceOrderID;

Given the following index:

CREATE UNIQUE INDEX i
ON Production.TransactionHistory
    (ProductID, TransactionDate, ReferenceOrderID)
INCLUDE
    (ActualCost);

The execution plan is:

While not horribly inefficient, it seems like it should be possible to express this query using only window aggregate and analytic functions supported in SQL Server 2012, 2014, or 2016 (so far).

For clarity, I am looking for a solution that performs a single pass over the data.

In T-SQL this is likely to mean that the OVER clause will do the work, and the execution plan will feature Window Spools and Window Aggregates. All language elements that use the OVER clause are fair game. A SQLCLR solution is acceptable, provided it is guaranteed to produce correct results.

For T-SQL solutions, the fewer Hashes, Sorts, and Window Spools/Aggregates in the execution plan, the better. Feel free to add indexes, but separate structures are not allowed (so no pre-computed tables kept in sync with triggers, for example). Reference tables are allowed (tables of numbers, dates etc.)

Ideally, solutions will produce exactly the same results in the same order as the subquery version above, but anything arguably correct is also acceptable. Performance is always a consideration, so solutions should be at least reasonably efficient.

Dedicated chat room: I have created a public chat room for discussions related to this question and its answers. Any user with at least 20 reputation points can take part directly. Please ping me in a comment below if you have less than 20 rep and would like to take part.

Discussion for "Date range rolling sum using window functions"

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

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

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

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

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

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

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

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

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

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

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

        return trxns;
    }
}

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

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

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

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

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

    return trxns;
}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

A few reasons I like this approach:

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

A couple potential caveats:

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


PowerShell Script To Identify Databases I have enough PowerShell skills to get off the ground, but have none with SQL server. Can someone please point me in the right direction of how to use PowerShell to get information on remote SQL servers to identify what databases are running on them.

Thanks for any help.

Edit - Using SMO Code

I have tried to put this code together, but get the error: Cannot find type [Microsoft.SqlServer.Management.Smo.Server] make sure the assembly containing this type is loaded

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MSQLULTDBS04"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}

You have two options, both of which will require SQL Server Management Studio to be installed on the host you are running the commands from:

SQLPS
SMO

I lean toward the latter because it includes a bit easier format to learn for basic task such as getting a list of objects in an instance.

With SMO you have to load the appropriate assemblies you want to work with. In most cases Microsoft.sqlserver.management.smo.server will be the most common one used.

With SQLPS you have to know what version of SQL Server you are working with. SQL Server 2008 R2 (and R1) you will add-pssnapin *sql* and SQL Server 2012 and beyond you will import-module SQLPS.

Now to the question at hand of getting a list of databases.

SQLPS method

dir SQLSERVER:\\SQL\ServerName\Default\Databases | select name

The above would be for a default named instance, you would change "default" to your instance name if it was a named instance.

SMO method

$srv = New-Object 'Microsoft.SqlServer.Management.SMO.Server' "myInstance"
$srv.Databases | select name

Now the above code can easily be wrapped into a function or for each loop that would allow you to easily pass in multiple server names.

Edit

Depending on what literature you read on PowerShell you will find that most folks that take time to write out a script, will go ahead and spend the time to make it a repeatable one. This is done either through a function (basic level for me) and then modules (more advanced that I have not touched yet).

So for your example code:

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$sqlServer = New-Object("Microsoft.SqlServer.Management.Smo.Server") "MSQLULTDBS04"
foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}

Your foreach loop is not needed in order to output the list of databases. This is an object within SMO that contains multiple properties, one of which is name and PowerShell will simply output that in the default format, as shown below:

A difference to note between SMO and SQLPS here is that SQLPS would not output the system databases by default, SMO will.

If you look up blog post on PowerShell Profiles a common thing to do is add the assembly command to that so ever time you open your PowerShell command it is already available. I will then add functions that are basically commands I use often, and happen to have one for databases:

function Get-DBList ($server)
{
    $srv = New-Object 'Microsoft.SqlServer.Management.Smo.Server' $server
    $srv.Databases | Select name, RecoveryModel,
        @{Label="CompatibilityLevel";Expression={($_.CompatibilityLevel).ToString().Replace("Version", "")}}
} #end Get-DBList

You can modify this to your liking but if I call it using my local instance I get the output below.

I will go ahead and let you know something that I found out the hard way. Using SMO you can connect to SQL Server 2000 up to SQL Server 2014, however, each version of SQL Server can vary on where particular properties are located. I had a learning conversation with Jonathan Kehayias on Twitter one afternoon learning this but I cannot recall what property I was looking for. In these situations the .NET class library on MSDN for SMO is your friend because it can help guide you to properties you are looking for.


Replace value of strongly typed xml element in SQL Server with XQuery Given a element, defined within an XML Schema Collection as such:

<xsd:element name="xid">
    <xsd:simpleType>
        <xsd:restriction base="xsd:string">
            <xsd:maxLength value="32" />
        </xsd:restriction>
    </xsd:simpleType>
</xsd:element>

How would you update the element using XQuery?

The element is found within the ns namespace in the schema collection. I have been trying to update the element the below query:

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com";
     replace value of (/ns:*/ns:xid)[1] with "X00011793" cast as element(ns{http://www.anon.com}:xid,#anonymous) ?')
 where id = 11793

but this generates the following error:

  Msg 9301, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: In this version of the server, 'cast as
  ' is not available. Please use the 'cast as  ?' syntax.

If I remove the cast entirely and use this query:

update cm.item
   set data.modify(
    'declare namespace ns="http://www.anon.com";
     replace value of (/ns:*/ns:xid)[1] with "X00011793"')
 where id = 11793

I get this error:

  Msg 2247, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: The value is of type "xs:string", which
  is not a subtype of the expected type "<anonymous>".

If I issue this query:

update cm.item
   set data.modify(
      'declare namespace ns="http://www.anon.com/";
       replace value of (/ns:*/ns:xid/text())[1] with "X00011793"')
 where id = 11793

I get this error:

  Msg 9312, Level 16, State 1, Line 2 XQuery
  [cm.item.data.modify()]: 'text()' is not supported on simple typed
  or 'http://www.w3.org/2001/XMLSchema#anyType' elements, found
  '(element(ns{http://www.anon.com/}:xid,#anonymous) ?) *'.

I am targeting SQL Server 2008 R2.

Thanks!

I have not found a simple way to just modify the replace value of statement to work with anonymous simple type definitions.

Simple repro of what you have:

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid">
                    <xs:simpleType>
                        <xs:restriction base="xs:string">
                            <xs:maxLength value="30"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

set @X.modify('replace value of /root/xid  with "2"');

Result:

  Msg 2247, Level 16, State 1, Line 25 XQuery [modify()]: The value is
  of type "xs:string", which is not a subtype of the expected type
  "<anonymous>".

One workaround is to modify your schema to use a named simple type xidType and cast the new value.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid" type="xidType"/>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
    <xs:simpleType name="xidType">
        <xs:restriction base="xs:string">
            <xs:maxLength value="30"/>
        </xs:restriction>
    </xs:simpleType>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';

set @X.modify('replace value of /root/xid  with "2" cast as xidType?');

Another way is to extract the XML to an untyped XML variable, modify the variable and put it back to the table.

drop xml schema collection dbo.SimpleTypeTest;

go

create xml schema collection dbo.SimpleTypeTest as
N'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="root">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="xid">
                    <xs:simpleType>
                        <xs:restriction base="xs:string">
                            <xs:maxLength value="30"/>
                        </xs:restriction>
                    </xs:simpleType>
                </xs:element>
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>';

go

declare @X xml(document dbo.SimpleTypeTest) = '<root><xid>1</xid></root>';
declare @X2 xml = @X;

set @X2.modify('replace value of (/root/xid/text())[1]  with "2"');
set @X = @X2;


MySql not displaying all columns in the result I am trying to teach myself DBA. when I execute this query:

SELECT *, CASE WHEN Trans_type LIKE '%C%'
             THEN 'Credit Card'
             ELSE NULL
          END AS type
FROM Transactions
WHERE cust_no=2 && Trans_type LIKE '%C%';

I get the following:

+---------+------------+-------------------+----------------------+--------+------------+-------------+
| cust_no | date       | Category          | Subcategory          | Amount | Trans_Type | type        |
+---------+------------+-------------------+----------------------+--------+------------+-------------+
         | Credit Card | Bills & Utilities | Bills & Utilities Ot | 5      | C
         | Credit Card | Household/Home    | DIY/Home Improvement | 15     | C
         | Credit Card | Shopping          | Clothing & Accessori | 215    | C
         | Credit Card | Shopping          | Clothing & Accessori | 135    | C
+---------+------------+-------------------+----------------------+--------+------------+-------------+

4 rows in set (3.53 sec)

Why are the cust_no, date not displaying and also the CASE type displaying at the start in my mysql console?

Thanks.

The Table Transactions Description below:

+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| cust_no     | varchar(10) | YES  | MUL | NULL    |       |
| date        | date        | YES  |     | NULL    |       |
| Category    | varchar(20) | YES  |     | NULL    |       |
| Subcategory | varchar(20) | YES  |     | NULL    |       |
| Amount      | varchar(6)  | YES  |     | NULL    |       |
| Trans_Type  | varchar(5)  | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

Kind of looks like there's a CR (carriage return, ASCII 13) after the C in Trans_Type. Try specifying the column names, and put that first; it would disappear, but the other columns would be visible.

You can explicitly check for non-printing characters like this using the ORD() function. Let's say Trans_Type is a CHAR(3) (or NCHAR(3)):

SELECT ORD(SUBSTR(TransType,1,1)) as FirstChar
      ,ORD(SUBSTR(TransType,2,1)) as SecondChar
      ,ORD(SUBSTR(TransType,3,1)) as ThirdChar
  FROM Transactions
 WHERE cust_no=2 && Trans_type LIKE '%C%';

would show you the character codes for each character; check the appropriate code page to see what the characters actually are (as noted, for ASCII, a 13 represents a carriage return, which would move the cursor to the start of the current line in Windows at least).

If this is the problem, you should be able to use REPLACE(Trans_Type, @BadChar, '') to remove the offending character from your results. For ASCII and the carriage return character, @BadChar would actually be CHAR(13 using ascii). So, your query would be:

SELECT `cust_no`, `date`, `Category`, `Subcategory`, `Amount`
      ,REPLACE(Trans_Type, CHAR(13 USING ascii), '') as Trans_Type
      ,CASE WHEN Trans_type LIKE '%C%'
         THEN 'Credit Card'
         ELSE NULL
       END AS type
  FROM Transactions
 WHERE cust_no=2 && Trans_type LIKE '%C%';

Note: mostly untested, as I don't have your data.


Troubleshooting "Can't connect ..." from MySQL slave to master Although I can connect from slave to master via a command-line mysql client, my slave can't connect to the master as part of its replication task. SHOW SLAVE STATUS cites a "Permission denied" error:

Last_IO_Error: error connecting to master 'replicant@dbmaster:5000' - retry-time: 60  maximum-retries: 86400  message: Can't connect to MySQL server on 'dbmaster' (13 "Permission denied")

I've checked the user permissions as well as a command-line client connection. What else can I check?
The user has SELECT and REPLICATION SLAVE permissions on the master:
mysql -h dbmaster -P 5000 -u replicant "-p$PASS" --ssl -e 'SHOW GRANTS'
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+
# | Grants for replicant@192.168.187.101                                                                                |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+
# | GRANT SELECT, REPLICATION SLAVE ON *.* TO `replicant`@`192.168.187.101` IDENTIFIED BY PASSWORD '[HASH-REDACTED]' REQUIRE SSL |
# +--------------------------------------------------------------------------------------------------------------------------------------------------------+

And I've set up the slave to use the same username, password, host, port, ssl:
cat <<-EOF | mysql
STOP SLAVE;
CHANGE MASTER TO MASTER_SSL=1, MASTER_DELAY=259200,
MASTER_HOST='dbmaster', MASTER_PORT=5000,
MASTER_USER='replicant', MASTER_PASSWORD='$PASS',
MASTER_LOG_FILE='mysql-bin.041117', MASTER_LOG_POS=733093730;
START SLAVE;
EOF

The server is running Mariadb 10.3.

cat <<-EOF | mysql    Line OK
STOP SLAVE; Line OK
CHANGE MASTER 'AnyName4Slave' TO MASTER_HOST = '',MASTER_PORT=3306, MASTER_USER = 'user', MASTER_PASSWORD = 'password' ,MASTER_LOG_FILE='mysql-bin.041117', MASTER_LOG_POS=733093730;
START SLAVE 'AnyName4Slave';
EOF


How to get the return value from a stored procedure executed with sp_executesql I need to call a stored procedure in another database and check the return value. The name of the other database can vary so I'm trying to use sp_executesql, but I haven't been able to find how to get the return value from the procedure.

This is a simplified version of what I've got:

DECLARE @errorLogId INT
DECLARE @otherDbName NVARCHAR(MAX) = 'Foo' -- Get the database name from somewhere
DECLARE @sql NVARCHAR(MAX)

SET @sql = @otherDbName + '.dbo.SomeProc'

-- I want @errorLogId to be the return value from SomeProc, this didn't work.
EXECUTE @errorLogId = sp_executesql @sql

IF @errorLogId <> 0
   RAISERROR('SomeProc failed, ErrorLogId = %d.', 16, 1, @errorLogId) WITH SETERROR

If an error occurs in SomeProc it is caught and an entry is written to an error table (in the other database), then SomeProc returns the error table ID (identity) of the record that was written. Otherwise SomeProc returns 0.

I know SomeProc is failing because an error is written to the error log in the other database, but in the local database @errorLogId is 0 which is treated as success. I thought the return value would be 'passed through' sp_executesql, but I think the return value is actually from sp_executesql not SomeProc (i.e. sp_executesql can be successful regardless of whether SomeProc succeeded or failed).

Is there a way to access the return value from @otherDbName.dbo.SomeProc in the local database?

Thanks

You need to use the OUTPUT parameter, as it is described in the MSDN article:

Given this dummy stored procedure:

CREATE PROCEDURE sp_test AS
RETURN 2

This code will give you the result:

declare @ret int

exec sp_executesql N'exec @ret = sp_test', N'@ret int OUTPUT', @ret = @ret OUTPUT
select @ret as result

result
-----------
2

(1 row(s) affected)


How to query EndDate value from ReportServer..Subscriptions.MatchData In SQL 2008 R2, I'm putting together some administrative queries on SSRS metadata to check proper configuration, and other sorts of proactive things.

While querying the ReportServer db, I've been successful in checking reports, shared data sources, etc. The one thing that I am having trouble with is querying for any value within the Subscriptions.MatchData column. Everything from this column comes back as NULL (unless I return the entire contents of the column).

Specifically, I need the value for EndDate. I've tried a number of tweaks to no avail, including toying around with XPath, XMLNamespace, etc. I found this basic example of how it is supposed to work in 2005. What is different about 2008 that this will not work? And how can I query this value in 2008 R2?

`
SELECT
[Description]
,x.m.value('StartDateTime[1]','varchar(30)') AS StartDateTime
,x.m.value('EndDate[1]','varchar(10)') AS EndDate
FROM
(
    SELECT
        [description]
        ,CAST(MatchData AS XML) AS MatchData
    FROM ReportServer.dbo.Subscriptions
) AS P
CROSS APPLY MatchData.nodes('//ScheduleDefinition') x(m)
`

What's throwing it off is the XML name spaces in the tags. To get around this you need to use the with XMLNameSpaces clause.

Give this query a go.
I've tested this on a test instance (2008, not 2008 r2), so you may need to change the actual namespace definition to match your xml data.

with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices' as rs)
SELECT
[Description]
,m.value('rs:StartDateTime[1]','varchar(30)') AS StartDateTime
,m.value('rs:EndDate[1]','varchar(30)') AS EndDate
FROM
(
    SELECT
    [description],
    CAST(MatchData AS XML) AS MatchData
    FROM ReportServer.dbo.Subscriptions
) AS P
CROSS APPLY MatchData.nodes('/ScheduleDefinition') x(m)

SQL BOL reference for the WITH XMLNAMESPACES clause:

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


Align auto increment index to next thousand I am using MySQL.

When applying a big change to a production database, I would like to change the next auto-increment index of all my tables to align them to the next available thousand.

In other words, for each table, make the next new row in that table bear an ID that is 1000-aligned.

That would make it easier for humans to detect pre- and post-migration records without impairing DB functionality.

Is there a quick automated way to do that?
More importantly, is there an important drawback I missed?

If using mariadb-10.3 you can use sequences with a sequence defined to a 1000 increments that can be used for columns (ref).

MySQL and MariaDB have an auto_increment_increment global system variable that globally controls the amount that an AUTO_INCREMENT column will increment. It is global in the sense it will affect all tables. You'll need to alter the table in question to have the next AUTO_INCREMENT value at a 1000 offset. Its designed around a multi-master replication purpose so you can't use it with bi-directional (or galera cluster) replication configurations.

In general an AI value is meant to be an internal detail rather than something for humans to consider so another differentator might be better. Its hard to read your business case.


Common `event` (datetime) table for say a manufacturing DB, an anti pattern? There are many different types of events that can happen during a manufacturing process.
E.g.

Built Step1
Build Step2
Test
Checked into repairs
Checkout out of repairs
Packaged
Dspatched, etc.

I see two ways to model each step:

Have separate independant tables. Each table with its own timestamp field.
Extract the timestamp field from the tables, and place it in a common event tables which has say 2 fields:

timestamp field
A column which links it to which type of event it is (which table has the specific details for that type of event).
Could also have a user field to track who did what etc.

Any reasons why not to use way 2 (event table)? It seems advantages for running reports on what events happened during a certain time frame. A way of uniting events in the factory. But my research seems to always point towards avoiding any common function tables.

Having an EVENT table sounds reasonable, particularly if there is a set of attributes common to all events. I would tend towards a more descriptive table name, perhaps MANUFACTURING_EVENT (or some terminology used by people in that industry). I would then have detail tables for specific manufacturing events with a FK relationship to the parent.


Getting multiple out when using NOT EQUAL operator How to display name of the department managers (fname, lname) who have no dependent?

Tried this:

Select DISTINCT EMPLOYEE.Fname,EMPLOYEE.Lname
from EMPLOYEE
INNER JOIN DEPENDENT ON (EMPLOYEE.Ssn != DEPENDENT.Essn);

To achieve this, I did the following:

I created two tables, Employee and Dependent - note that I only created the important fields - name and SSN - you can add the details for yourself.

CREATE TABLE `Employee`
(
  `LName` varchar(20) DEFAULT NULL,
  `SSN` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and

CREATE TABLE `Dependent` (
  `Name` varchar(10) DEFAULT NULL,
  `Essn` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `Employee` VALUES ('Smith','12345-6789'),('Wong','33344-5555'),('Zelaya','99988-7777'),('Wallace','98765-4321'),('Mr-X','22222-3333');  -- For NULL values of Essn, see below.

and

INSERT INTO `Dependent` VALUES ('Alice','33344-5555'),('Theodore','33344-5555'),('Joy','33344-5555'),('Abner','98765-4321'),('Michael','12345-6789'),('Alice','12345-6789'),('Elizabeth','12345-6789');

You can see that Mr-X and Zelaya (employees) have no Essn in the Dependent tables - i.e. they have no dependents. Now for the SQL:

mysql> SELECT * FROM Employee e
WHERE e.SSN
NOT IN
(
  SELECT DISTINCT(Essn)
  FROM Dependent
);

Only works if the tables join on a NOT NULL field.

+--------+------------+
| LName  |       SSN  |
+--------+------------+
| Zelaya | 99988-7777 |
| Mr-X   | 22222-3333 |
+--------+------------+
2 rows in set (0.00 sec)
mysql>

For NULL SSN in the Employee table, use the following query, (as pointed out by @Paul White). I also added this line to test:

mysql> INSERT INTO Employee VALUES('EBorg', NULL);
Query OK, 1 row affected (0.05 sec)

SELECT * FROM Employee emp
WHERE emp.LName NOT IN
(
  SELECT DISTINCT(e.LName)
  FROM Employee e
  JOIN Dependent d
  ON e.SSN = d.Essn
);

Result:

+--------+------------+
| LName  |       SSN  |
+--------+------------+
| Zelaya | 99988-7777 |
| Mr-X   | 22222-3333 |
| EBorg  | NULL       |
+--------+------------+
3 rows in set (0.00 sec)

Initially, I assumed that everyone would have an SSN, however if they
don't, then the second query will work - I'm assuming no NULL LNames :-). It's very bad practice not to have a PRIMARY KEY which would mean that the simpler query would word.

Or, for NULLs, you could just a add a UNION of my first query and SSN IS NULL thus

SELECT * FROM Employee e
WHERE e.SSN
NOT IN
(
  SELECT DISTINCT(Essn)
  FROM Dependent
)
UNION
SELECT * FROM Employee e
WHERE e.SSN IS NULL;  -- same result as above.

Following more discussion with @PaulWhite, there are two more queries which will work providing the (correct 3 line) result shown above.

SELECT e.*
FROM Employee e
WHERE NOT Exists
(SELECT 1
 FROM Dependent d
 WHERE e.SSN = d.Essn
);

And (the best one!)

SELECT  e.*
FROM Employee e
LEFT JOIN
Dependent d
ON      e.SSN = d.Essn
WHERE   d.Essn IS NULL;


Splitting Tables in MySQL. Good practice? I have started working on an existing project and the previous developer had split up a table into 10 separate tables with identical schemas but different data.

The tables look like this:

[tableName_0]
[tableName_1]
[tableName_2]
[tableName_3]
[tableName_4]
[tableName_5]
[tableName_6]
[tableName_7]
[tableName_8]
[tableName_9]

The primary key is an integer id field. The application uses a hash algorithm (id mod 10) to know what table to access when doing lookups. For example id = 10 would result to [tableName_0].

Combined, the tables have probably 100,000 rows and the growth rate is relatively low.

So, my question is whether or not this is a viable solution or even if it's a good practice in any situation. My theory is to push to have them combined as it will make things easier as far as UNIONs, etc go. The main downside is changing all the application code and whether it is even worth it in the long run.

I think everyone is over-complicating this.  The key point here is:

  Combined, the tables have probably 100,000 rows and the growth rate is relatively low.

This is a piece of cake for any RDBMS to handle.  Go with one table, index it properly, and consider it a solved problem.

You don't need to consider partitioning, whether "homemade" or otherwise, until you start handling extremely large volumes of data--think billions of rows and up.


Improving performance of count where only result > 0 matters I have following query:

select count(*) -- rp.tag
from submission
inner join post rp on submission.id = rp.submission_id
where user_name = ?1
and lower(rp.tag) like ('%verified%')
and not lower(rp.tag) like ('%unverified%');

If the result is greater than 0 I do something in my application for this user.

I run this query against a big list of users and it takes close to an hour to finish the whole process. And I only need to know whether the result is greater than 0.

Is there a way to finish the query early if any of the rp.tag match my condition?

You can add LIMIT 1, but LIMIT is applied after aggregation (so after the count). Adding it in the same query level does nothing useful, since there is only 1 row left to return anyway: the row count. Would be misleading nonsense.

If you want to go that route, add LIMIT in a subquery and count in the outer SELECT, like:

SELECT count(*) FROM (
   SELECT             -- empty SELECT list
   FROM   submission
   JOIN   ...
   WHERE  ...
   LIMIT 1
   ) sub

For this special purpose it's fastest to leave the SELECT list empty. count(*) only counts the existence of rows without regard to any columns.

You can do that with any LIMIT, really. To check whether there are at least N rows. See:

Fast way to discover the row count of a table in PostgreSQL

Checking for N = 1 is just your special case. And while we only care whether any rows exist and not how many, EXISTS is the better, faster tool:

SELECT EXISTS (
   SELECT FROM submission s
   JOIN   post p ON s.id = p.submission_id
   WHERE  s.user_name = ?
   AND    p.tag ~* 'verified'
   AND    NOT p.tag ~* 'unverified'
   );

Without index, lower(col) LIKE '%something%' is a bit faster than col ~* 'something'. But if your tables are big and performance matters (like your question indicates), you should have matching indexes to begin with, a trigram index on tag to be precise. Assuming tag is from table post (you left room for interpretation there):

CREATE INDEX post_tag_gin_trgm_idx ON post USING gin (tag gin_trgm_ops);

Then the simpler expression is just as fast. See:

LOWER LIKE vs iLIKE
How is LIKE implemented?

Depending on cardinalities and data distribution it might make sense to combine that with submission_id in a multicolumn index:

CREATE INDEX post_foo_trgm_idx ON post USING gin (submission_id, tag gin_trgm_ops);

You'd need the additional module btree_gin for that. See:

Best index for similarity function

And it might make sense to have another multicolumn index on submission(id, user_name) - or on submission(user_name, id), again depending on the mentioned details. The first variant is more likely to be useful in case Postgres expects the predicate on submission.user_name to be more selective.

(Your handling of tags might be optimized further.)

Aside:

  If the result is greater than 0 I do something in my application for this user.

If that something is another SQL DML statement, make it a single statement for additional gains. Example:

UPDATE foo
SET    bar = 'baz'
WHERE  EXISTS ( ...) -- like above
AND    bar IS DISTINCT FROM 'baz' -- avoid empty updates

About the added last line:

How do I (or can I) SELECT DISTINCT on multiple columns?

Or you embed the expression in plpgsql code for procedural needs, like demonstrated here:

PL/pgSQL checking if a row exists


Migrate Availability Groups from SQL Server 2014 to 2016 Is it possible to migrate AGs 2014 to 2016?
We are currently on 2014.

Or we need to rebuild from scratch on 2016 with old backup files.

Anyone who have done it please suggest the pros and cons .

What things to consider for this change .

Yes it is possible and you do not need to rebuild from scratch.

This is well documented here and you can follow the steps and best practices from this document.

Upgrading Always On Availability Group Replica Instances

To check all versions that qualify for rolling upgrade see this document.

Supported Version and Edition Upgrades

This question was answered before here.


Is there a way to programmatically script all objects associated with a given table? I know that in SQL management studio I can right click a table/trigger/key and script object as.... Is there a way to do this programmatically, given an object's name?

If so, is there a way to find all objects (primary keys, foreign keys, triggers) associated with a given table and script all of them programmatically?

This is a technique in C# using Server Management Objects - I don't know of a way to do it in pure T-SQL.


Sql Server - Build a json array of integer from an aggregate I want to use an aggregate to build a simple single-dimensional JSON array of scalar values, like the LuckyNumbers array in the example below:
[{
    "id": 1,
    "name": "Josian",
    "LuckyNumbers": [581, 777]
}, {
    "id": 2,
    "name": "Paul",
    "LuckyNumbers": [123551, 5, 646464, 1345, 75, 76]
}, {
    "id": 3,
    "name": "Seasonique",
    "LuckyNumbers": [1]
}]

With Postgresql 12, you can do it using native aggregate functions ( fiddle )
--Postgresql 12 schema
create table person (   Id int primary key,   name varchar(20) );
insert into person values (1,'Josian'), (2,'Paul'), (3,'Seasonique');
create table LuckyNumbers (PersonId int references person(Id), LuckyNumber int);
insert into LuckyNumbers (PersonId, LuckyNumber) values (1,581), (1,777), (2,123551), (2,5), (2,646464), (2,1345), (2,75), (2,76), (3,1);

--Query to JSON
select
  array_to_json(array_agg(row_to_json(r))) "PersonsNumbers"
from (
    select
      p.id,p.name,json_agg(ln.LuckyNumber) "LuckyNumbers"
    from person as p
      inner join
        LuckyNumbers as ln
        on
          p.id=ln.PersonId
    group by p.id,p.name
  )
  r

This will output the desired result, exact as above.
The SqlServer2019 example below however requires the use of string_agg which is a string function and then manually concatenating array brackets. ( fiddle )
--Sqlserver 2019 schema creation
create table person (   Id int primary key,   name nvarchar(20) );
insert person values (1,'Josian'), (2,'Paul'), (3,'Seasonique');
create table LuckyNumbers (PersonId int foreign key references person(Id), LuckyNumber int);
insert LuckyNumbers (PersonId, LuckyNumber) values (1,581), (1,777), (2,123551), (2,5), (2,646464), (2,1345), (2,75), (2,76), (3,1);

--Query to JSON
SELECT p.id
    ,p.NAME
    ,Json_Query('[' + string_agg(ln.LuckyNumber, ',') + ']') 'LuckyNumbers'
FROM person p
INNER JOIN LuckyNumbers ln ON p.id = ln.PersonId
GROUP BY p.id
    ,p.NAME
FOR json path

--JSON to Query : OPENJSON and out apply allow you to read back the array !
DECLARE @JsonOutput NVARCHAR(max) = (
        SELECT p.id
            ,p.NAME
            ,Json_Query('[' + string_agg(ln.LuckyNumber, ',') + ']') 'LuckyNumbers'
        FROM person p
        INNER JOIN LuckyNumbers ln ON p.id = ln.PersonId
        GROUP BY p.id
            ,p.NAME
        FOR json path
        );

SELECT id
    ,NAME
    ,luckynumber
FROM openjson(@JsonOutput) WITH (
        id INT 'strict $.id'
        ,NAME NVARCHAR(50) '$.name'
        ,LuckyNumbers NVARCHAR(MAX) '$.LuckyNumbers' AS JSON
        )
OUTER APPLY OPENJSON(LuckyNumbers) WITH (LuckyNumber NVARCHAR(8) '$');

Is there a better way to do this with Sqlserver ? Did I miss a native aggregation function similar to Postgre's json_agg ?

You could approximate the desired output like this:

SELECT p.id
    , p.[name]
    , LuckNumbers = (
            SELECT ln.LuckyNumber
            FROM #LuckyNumbers ln
            WHERE ln.PersonId = p.Id
            FOR JSON PATH
        )
FROM #person p
FOR JSON PATH;

Essentially, creating a JSON array of int values for each person's lucky numbers.  I'm not sure how to remove the "LuckyNumber" tag from each value; this is my first time with JSON on SQL Server.

The output looks like:

[
    {
        "id":1
        ,"name":"Josian"
        ,"LuckNumbers":[
             {"LuckyNumber":581}
            ,{"LuckyNumber":777}
            ]
    }
    ,{
        "id":2
        ,"name":"Paul"
        ,"LuckNumbers":[
             {"LuckyNumber":123551}
            ,{"LuckyNumber":5}
            ,{"LuckyNumber":646464}
            ,{"LuckyNumber":1345}
            ,{"LuckyNumber":75}
            ,{"LuckyNumber":76}
            ]
    }
    ,{
        "id":3
        ,"name":"Seasonique"
        ,"LuckNumbers":[
            {"LuckyNumber":1}
            ]
    }
]


Find and replace/wrap certain terms in html tags I'm trying to replace certain text in a html string with a html anchor tag surrounding that text, I've tried using REPLACE to simply find and replace the term but then it ends up finding matches inside words and html attributes which I don't want. The closest I've got to it actually working is using PATINDEX and STUFF shown in the example below however it only replaces the first occurrence and it replaces the boundary characters (i.e. the [ ,.] chars) as well as the actual term.

Example

DECLARE @start TABLE (
    id INT IDENTITY(1,1) PRIMARY KEY,
    val VARCHAR(100) NOT NULL
);

DECLARE @end TABLE (
    id INT PRIMARY KEY,
    val VARCHAR(100) NOT NULL
);

INSERT INTO @start
VALUES ('change me plz'),('change me and me plz too'),('dont change this meme'),('nope'),('not me and change me')

DECLARE @searchTerm VARCHAR(50) = 'me';
DECLARE @replaceTerm VARCHAR(50) = 'me';
DECLARE @pattern VARCHAR(50) = '%[ ,.]' + @searchTerm + '[ ,.]%';
DECLARE @currentVal VARCHAR(100)
DECLARE @currentId INT = (
    SELECT TOP 1 id FROM @start WHERE val like @pattern
);

SELECT * FROM @start

WHILE(@currentId IS NOT NULL)
BEGIN
PRINT @currentId;
    SELECT @currentVal = STUFF(val, PATINDEX(@pattern, val), LEN(@searchTerm)+2, @replaceTerm)
    FROM @start
    WHERE id = @currentId

    INSERT INTO @end (id, val)
    VALUES (@currentId, @currentVal)

    DELETE FROM @start
    WHERE
    id = @currentId

    SET @currentId = (
        SELECT TOP 1 id FROM @start WHERE val like @pattern
    );
END

SELECT * FROM @end

tl;dr;

how do you add html tags around text in a html string so that the html is still valid and you only replace the whole word/term not substrings of other words

SQL Server is not the best with String Manipulation.  If this functionality will change over time or become complex in any nature, I would suggest you look into CLR as a way to search/replace complex strings.  Here's a great article from Phil Factor on Simple Talk that walks through how to use CLR for complex string operations.


SQL rolling back all iterations of a while loop when each iteration has its own explicit transaction For the past few days I've been trying to write a query for an automated process that will delete records from a log table within a specified date range (a month) to another table, so they can then be dumped to disk and the records deleted from the database. A first, naïve approach, á la
DELETE FROM <sometable>
OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}

of course blew up the transaction log. I then tried doing the same, but now in batches of 1m rows, as shown below
WHILE @@ROWCOUNT > 0
BEGIN
    DELETE TOP (1000000) FROM <sometable>
    OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
    WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}
END

but that did nothing to change the transaction log growth. Next, I tried using an explicit transaction within the loop.
WHILE @@ROWCOUNT > 0
BEGIN
    BEGIN TRAN

    DELETE TOP (1000000) FROM <sometable>
    OUTPUT DELETED.* INTO [<sometable>_{year}_{month}]
    WHERE DATEPART(YEAR, [DateTime]) = {year} AND DATEPART(MONTH, [DateTime]) = {month}

    COMMIT
END

Again. No difference. What's worse, if I cancel this query and do a ROLLBACK to release the table, it rolls back everything. Not just the last iteration, as one would expect, but the whole thing. Why aren't the individual transactions within the loop being treated as such? Surely there must be a way to move huge amounts of rows around the database without SQL throwing a hissy fit and blowing up on its user(s)?

The key here is this:

What's worse, if I cancel this query and do a ROLLBACK to release the
table, it rolls back everything. Not just the last iteration, as one
would expect, but the whole thing.

So apparently your work was already transaction protected. Breaking it up to several DELETE did noting for you, since apparently they are all still one transaction. You need to find out why. You don't say from where you are executing your code.
If you use SSMS, then perhaps somebody turned on SET IMPLICIT_TRANSACTIONS ON. This turns off autocommit. Can be done in a config option in SSMS or using above command. But then you would have to commit your work when you are done, or SSMS would catch that you have an open transaction and ask if you want to commit or rollback.
Perhaps you use some other environment that will protect your work and submit rollback if SQL Server throws an exception. this is where I would start. that would explain everything - but there's some more digging from your side to do.


What Problems Does an Eager Spool Indicate Currently running on SQL Server 2008 R2

I am attempting to increase performance of an UPDATE statement.  I notice an Eager Spool operation in the showplan popping up.  My understanding of spooling operations is pretty basic - they create temporary storage for the table during the update.

I also know that, while they are preventing much worse execution times, eager spools are often indicative of underlying problems with table structure and/or query statements.

My question is pretty simple:  When you see an Eager Spool in your query plan, what problems do you first look to address?

I will be analyzing every part of our system to increase performance - I'm just looking for guidance as to where I should start.

I am attempting to increase performance of an UPDATE statement. I notice an Eager Spool operation in the showplan popping up.

Eager Spools may be added for a variety of reasons, including for Halloween Protection, or to optimize I/O when maintaining nonclustered indexes.

Without seeing (even a picture of) the execution plan, it is hard to be certain which of these scenarios might apply in your particular case. If data sensitivity is a concern, consider uploading an anonymized version of the plan for analysis using SentryOne Plan Explorer.

It may well be that the Eager Spool is not the thing you should be concentrating on anyway; many factors influence the actual performance of queries that change data. If you're basing your tuning efforts on the estimated percentage cost shown for the Eager Spool operator, please consider that those estimates are generated using a model that is not intended to match the capabilities of your particular hardware configuration.


Need to install Oracle Express 11g Release 2 on a Windows 7 64-bit laptop I need the Oracle 11g Release 2 sample schemas (HR, OE, etc.) in order to do most of the available online tutorials.  I was hoping to install Oracle Express Edition on my Windows 7 laptop to get these; but I have never heard of anybody successfully installing Oracle XE on a 64-bit Windows platform.

Is there a version of Oracle XE 11g R2 available for Windows 7?  And if so, could you please point me to it?

Thanks...

For that purpose you can download the pre-built virtual Machines (VMs) provided by Oracle. They only need Oracle Virtual Box in order to check them out (https://www.virtualbox.org/wiki/Downloads).

You can download them here:
http://www.oracle.com/technetwork/community/developer-vm/index.html

This is the best solution for learning Oracle DB, without actually messing up your system.


Splitting data into two tables, in one go I am using SQL Server 2008 and have a problem which I don't know how to solve without using many temporary tables and an unreliable join.

Table 1 contains 6 columns of data, which is then split into two tables. Col1 to Col3 goes into Table 2, and Col4 to Col6 goes into Table 3. Getting the data into Table 2 and Table 3 is the easy part. However T2ID in Table 3 is a foreign key to the ID in Table 2.

Performance is key so I don't want to use variables and/or iterate through the data row by row, ideally I just want one insert which does the lot.

I've tried using a Link Table but the data in Table 2 and Table 3 is not unique which makes joining unreliable.

Any suggestions?

Create Table T1 (
  ID INT IDENTITY(1,1),
  Col1 VARCHAR(10),
  Col2 VARCHAR(10),
  Col3 VARCHAR(10),
  Col4 VARCHAR(10),
  Col5 VARCHAR(10),
  Col6 VARCHAR(10)
)

Create Table T2 (
  ID INT IDENTITY(1,1),
  Col1 VARCHAR(10),
  Col2 VARCHAR(10),
  Col3 VARCHAR(10)
)

Create Table T3 (
  ID INT IDENTITY(1,1),
  T2ID INT,
  Col4 VARCHAR(10),
  Col5 VARCHAR(10),
  Col6 VARCHAR(10)
)

I can't change T2 or T3. T1 is not a staging Table, and I can't alter that either. There are other packages also writing to T1, T2, and T3. Although it is possible to schedule them at different times should I need to.

If you use MERGE to insert the data into T2, you can generate a mapping table between T1.ID and T2.ID:

DECLARE @Mapping TABLE
(
  T1ID int,
  T2ID int
);
MERGE INTO
  dbo.T2 AS tgt
USING
  dbo.T1 AS src
ON
  1 = 0
WHEN NOT MATCHED THEN
  INSERT (    Col1,     Col2,     Col3)
  VALUES (src.Col1, src.Col2, src.Col3)
OUTPUT
  src.ID, inserted.ID INTO @Mapping (T1ID, T2ID)
;

Unlike INSERT, which would let you reference the inserted table's columns only in the OUTPUT clause, the MERGE statement allows you to reference the source table's columns as well. That is key to this solution, because that is how you associate the source IDs with the target IDs.

Once you have the mapping table, you can just use it in a join when inserting into T3. A plain INSERT ... SELECT will do this time:

INSERT INTO
  dbo.T3 (T2ID, Col4, Col5, Col6)
SELECT
  m.T2ID,
  t.Col4,
  t.Col5,
  t.Col6
FROM
  dbo.T1 AS t
  INNER JOIN @Mapping AS m ON t.ID = m.T1ID
;

Wrap both statements in one transaction to make your split operation atomic.

Discussion about the MERGE mapping method can be found in this thread:

Using merge..output to get mapping between source.id and target.id


MongoDB secondaries become unresponsive during replication We got 3 Servers running. One Primary, two as secondaries. Primary has 4 vcpus, 16 GB memory, both secondaries have 8 vcpus, 64 GB memory.

Every night, we run a full sync with several large collections on the primary with multiple threads.

During that sync, both secondaries become unavailable from time to time.
mongod.log states following notice:

serverstatus was very slow: { after basic: 0, after asserts: 0, after
backgroundFlushing: 0, after connections: 0, after dur: 0, after extra_info:
0, after globalLock: 0, after locks: 0, after network: 0, after opLatencies:
0, after opcounters: 0, after opcountersRepl: 0, after repl: 0, after
security: 0, after storageEngine: 0, after tcmalloc: 0, after wiredTiger:
4992, at end: 4992 }

Mongostat during that time states:

Our clients have readPreference set to secondaries only, but we dont have much connection during that time tough.

Standard is default, so default mongodb config with no special tweaks.

So the only thing is see is, that the mongodb log states an "after wiredTiger" message with an higher amount of time. Any clue what's happening here?

Used mongoDB Version is 3.4.16

What you are observing is an expected effect of replication batches being applied on your MongoDB 3.4 secondaries. The original design intent of replica sets was to provide data redundancy and failover, so efficient application of replicated writes takes priority over secondary reads. Replicated writes are applied on secondaries in multithreaded batches organised for better write throughput, which means that the order of writes may temporarily diverge from the primary. To avoid out-of-order secondary reads during critical sections of write activity, read operations will periodically have to wait while a replication batch is being applied.

This will have a more noticeable impact during periods of high replication activity or where your secondary is generally having challenges keeping up with write activity.

The items you've highlighted are expected in MongoDB replica sets prior to 4.0:

Intermittent increased latency for  serverStatus commands or read operations on secondaries.
Since mongostat collects serverStatus output on a timed interval, the "no data received" indicates that a result wasn't returned before the next reporting interval.

Improvements in subsequent releases of MongoDB have enabled introduction of non-blocking secondary reads in MongoDB 4.0. If you rely on secondary reads and are impacted by latency under write load, it would definitely be worth planning an upgrade. For more information about the changes in MongoDB 4.0, see: Scaling Your Replica Set: Non-Blocking Secondary Reads in MongoDB 4.0.

For some general caveats on using secondaries for read scaling, see: Can I use more replica nodes to scale?.


postgres: Insert multiple rows into a table with id from other table if not exists insert to other table I have done similar task where I can insert a row into a table if data doesn't exists:
WITH
        user_exists AS (
            Select id from users where username='%s'
        ),
        user_new AS (
            INSERT INTO users (username)
            SELECT w.username FROM (values ('%s')) w(username)
                WHERE not exists
                (SELECT 1 FROM users u WHERE u.username = w.username)
                returning id
        )
INSERT INTO feedbacks ('static_row', userid)
SELECT
'static_data',
(SELECT id FROM users_exists UNION ALL SELECT id FROM users_new) AS userid

Above works well when we insert a new row to feedbacks table. If user doesn't exists it inserts data in users table and returns id which is used for inserting data to feedbacks table.
But now my use case is, I have to insert multiple rows into the feedback table. Something like this:
user_variable = ['a','b', ...]
Insert into feedbacks ('static_row', userid)
VALUES
('sample_data', (Select if from users where username='a')),
('sample_data', (Select if from users where username='b')),
('sample_data', (Select if from users where username='c'))

For above case, how we can insert a new row to users table if username='b' doesn't exists.

Why not split up your separate goals into separate queries to handle them appropriately? It seems a little dangerous to be modifying data within a CTE that is then used to be read from, though not sure if this is common convention in PostgreSQL.
For example:
WITH CTE_UsersToBeAdded AS
(
    SELECT 'a' AS username
    UNION ALL
    SELECT 'b' AS username
    UNION ALL
    SELECT 'c' AS username
)

INSERT INTO users (username)
SELECT c.username
FROM CTE_UsersToBeAdded c
LEFT JOIN users u
    ON c.username = u.username
WHERE u.usernams IS NULL; -- Filters out already existing usernames

INSERT INTO feedbacks ('static_row', userid)
VALUES
('sample_data', (Select id from users where username='a')),
('sample_data', (Select id from users where username='b')),
('sample_data', (Select id from users where username='c'))


Does MySQL use its index metadata to optimize COUNT(*) queries? Let's consider a user_actions table with a standard indexed primary key user_action_id.
I want to know the number of user actions up to a certain key (say 100 millions):
SELECT COUNT(*) AS `user_actions_count`
FROM `user_actions`
WHERE `user_action_id` < 100000000

In my understanding, the index b-tree has to keep the sub-node counts at each tree node for balancing purpose. Would that information be used to perform such an obvious query or would the engine have to scan the whole index to actually count rows?
As a matter of fact, I encounter unexpected performance issues on that query.
Thanks for your help.
EDIT:
Here is the EXPLAINation of the query:
id  select_type table        type  possible_keys key     key_len ref  rows      Extra
1   SIMPLE      user_actions range PRIMARY       PRIMARY 4       NULL 268537554 Using where; Using index

It has to scan the whole index. Look at the output of:
SHOW INDEX FROM user_actions;

You will see the approximate cardinality of each index. The approximation is based on a few random dives into the index, not an exhaustively checked exact number. The approximation is sufficient for balancing.


Decision criteria on when to use a non-dbo schema vs a new Database I'm mostly an application developer but find myself having to do all the up-front database work for my current project (btw... its MS SQL Server 2008). As a first decision, I'm trying to figure out whether to divide my state using seperate Databases or using seperate Schema's in the same database. I've done a little reading on SQL Server Schema's and it seems like a natural way to seperate object domains (which I like), but I'm not sure if there may be hidden costs to this pattern.

What are the more practical things I should consider when selecting between these two approachs? If I avoid the dbo.mytable in favor of myschema.mytable will I be creating other challenges (or problems) for my architecture?

As a side note... At some point this will be handed over to a real DBA to maintain/support so I'm trying to make sure I don't make their lives harder.

I'll start by saying don't consider schemas as namespaces or object domains in the OO sense. Schemas are essentially permission containers with some added value (see below)

Also, "separate schemas" or "separate databases" are 2 different concepts. Data that needs to be transactionally and referentially consistent needs to be in the same database.  See One Database or Ten? blog article for more.

In that database, you may or may not use schemas for organising your objects.

Personally, I'm a fan of schemas and always use them but for things like permissions and logical grouping. For that, I'll refer you to previous questions where you can see the general opinion is in favour of them:

Schema design - best practices?
What standard should I follow when naming tables and views?
Over use/correct use of schemas?

For the case for separate databases, see Aaron's answer, but this all hinge on the "transactionally and referentially consistent" requirement.


Possible reasons for really long database recovery This is cross-posted at SSC, but restore is now going on 6 hours.

Hello,

Overnight I ran restores with NORECOVERY for a 5tb database, along with several other 250GB - 1.3tb databases. I did that because sometimes our 3rd party backup software throws wacky LSN errors if it thinks something timed out that really didn't. Anyway, the restore completed with no errors. This morning I started running the RESTORE WITH RECOVERY commands to bring them online for DBCC checks.

Ran on the first database fine, got stuck on the 5tb database for about an hour, then I ran the commands on the remaining six databases in parallel to the 5tb. All of them recovered very quickly. The 5tb database has been restoring now for about 3 hours.

Any thoughts on:

Why this in particular would be taking much longer than both:

a) how long it usually takes to recover, and
b) much longer than other databases took to recover?

If I should cancel/kill the transaction and try to start over

I can see it doing something in sp_WhoIsActive, i.e. informational columns are incrementing, and the wait info changes (though the wait that it always seems to be on is IO_COMPLETION), though status seems to always be suspended.

While researching I came across one article that suggested high VLF count can cause this, and there are about 5k VLFs in the log file, but I'm on a version of 2012 that is patched for the issue:

Microsoft SQL Server 2012 - 11.0.5532.0 (X64)
Jul 14 2014 15:00:27
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.3  (Build 9600: ) (Hypervisor)

Thanks

EDIT:

This is on my DBA box that I use for offloading DBCC checks, so I'm using a good backup. The growth settings for these databases are controlled by best practices issued by the 3rd party vendor. I'm not allowed to touch them. They're set to percentages, which I disagree with, but it's out of my control. Management isn't open to changing because we charge clients by space used for hosting. I do monthly maintenance on VLFs but they have been this high before and I've run the restore with no issues.

EDIT 2:

Finished after 21 hours!

RESTORE DATABASE successfully processed 0 pages in 77454.736 seconds (0.000 MB/sec).

If this is a PRODUCTION environment, definitely DO NOT CANCEL recovery.  This will only prolong your pain.  DO NOT RESTART SQL SERVER.

Wait until recovery is finished, no matter how long it takes.
Ensure you have a good backup.
Reduce the Virtual Log File count on databases that have an unreasonably high number of them.
Set autogrowth on the database to a reasonable number, NOT 10MB or 10%!  If your log file is currently 50GB or larger, for instance, you might want to consider a growth size of 4GB or 8GB.
Use this script, or some variation thereof, to monitor VLF counts on your databases, and pro-actively manage them so you don't have this problem in future.

This answer has many great points about VLF performance.

Kimberly Tripp has an excellent article about how to get better transaction throughput.

MSDN has a blog post about slow database recovery from high VLF counts.  The article is targeted at SAP users, but the information applies equally well to all SQL Server installations.

Linchi Shea has some good points over at sqlblog.com

If this is some non-production environment, then all bets are off.  You can kill that sucker as much as you like to find out the cause/solution.


Database schema for date-time ranges Let's say, I have a course which consists of N lessons. I want to offer it via email subscription. Each N days an email with content should be sent to all the users who've subscribed.
I want it to be sent to the users every 1, 2 or 3 days. For instance.
I'm looking into a way to create a database scheme where I'll able to store these time ranges, and of other types (weeks, months...), in the future.
Date-time ranges can be in minutes, hours, days, weeks and months. And there should be a possibility to specify at what time during a day it'll be sent, or which day of week, etc...
Note that each newly subscribed user should also go through the same process: he'll receive the same N lessons with the same frequency or date-time ranges, for instance: 1st lesson on day 1 at 15:00 o'clock UTC, 2nd on day 3 at 9:30 o'clock, 3rd on day 8 at 21:15 o'clock and so on.
This type of scheme isn't the same as the one for scheduling re-occuring tasks and cron in Linux.
And I want it to be flexible enough too.
I've began with these fields:
* frequency_unit: enum (minute, hour, day, week, month)
* frequency_value: int

How to create it?

You need to record T0 for each student and ΔT for each lesson.
DATE math assumes a difference of 1 === 1 day.  That means a value of 0.5 === half day. etc. etc.  Some RDBMS support an INTERVAL data type that could be used instead.  It is up to you which one you want to use.
So, to calculate "when to start a lesson", you just need to take the date_started value (T0) and add the delta_T value (ΔT) of the lesson to get the DATE-TIME to send the email.
create table lesson_plan (
  lesson_id int -- identifier
  ,class_id      int not null
  ,delta_T       number -- ( or whatever is best for your RDBMS)
  ,constraint lesson_plan_pk primary key (lesson_id)
  ,constraint lesson_plan_fk1 foreign key (class_id)
      references classes(class_id) on delete cascade
);

comment on table lesson_plan is 'Stores chapters and when to send the lesson';

create table student_classes (
   student_id     int
  ,class_id       int
  ,date_started   date -- needs to record DATE and TIME
  ,constraint student_classes_pk primary key (student_id,class_id)
  ,constraint sudent_classes_fk1 foreign key (student_id)
      references students(student_id) on delete cascade
  ,constraint student_classes_fk2 foreign key (class_id)
      references classes(class_id) on delete cascade
);

comment on table student_classes is 'Records when each student started a class.';

create or replace
view student_lessons
as
select a.student_id
      ,b.class_id
      ,c.lesson_id
      ,a.date_started + c.delta_T as lesson_start_datetime
from student_lessons a
  join classes b on a.class_id=b.class_id
  join lesson_plan c on c.class_id=b.class_id;

comment on view is 'When a lesson starts for a specific student.';


Measuring SQL execution time in PostgreSQL? MySQL has a performance_schema database that allows one to capture SQL statement execution time data in a table (e.g. performance_schema.events_statements_history_long ; useful MySQL link).

I was wondering if a similar set of tools existed in PostgreSQL?

There isn't a reasonably easy way to do this, but you can:

Log slow queries
Use EXPLAIN ANALYZE to see where the query is actually spending its time.

You could probably store plans in the database somehow but I am not quite sure how this would be done (maybe with a pl/perl function?).  You'd have to roll your own solution though.

Storing plans in the database however is not easy.  One of the huge problems that you will have is getting the recursive structure right so you can query it.  A simple approach might be to take a JSON format and store that, or store XML.


I need to use a permenent table instead of temporary but I still need the table's data to be private to the connection I currently have an issue whereby an application which I maintain uses temporary tables. This is an issue as it's causing replication errors like found in this  mariadb issue.

I'm trying to think of a way that I can use permanent tables instead however I have the following dilemma.

The application in question is used concurrently by many users (not huge volume but enough for this to be a problem). An example temp table's data is populated with data specific to that user.

If I make the table permanent and truncate it at the beginning of every execution of the action in question then it has the potential to cause conflicts with other users on the system at the time.

Is there a way I can use transaction(s) and their isolation levels to be able to use a permanent table in the same manner as a temporary table?

Dynamically create a table name of

CONCAT("T_", CONNNECTION_ID())

With that, create the table.  (You cannot use "binding" to do so; you must create the entire CREATE TABLE string.)