Retrieve value of select query in a table column

I've tested the following (in SQL Server 2014, but it should work at least as far back at 2008), and I believe it does what you want:

CREATE TABLE QueryList (Id int, Name nvarchar(128), Query nvarchar(4000));

INSERT INTO QueryList
VALUES (1, 'Date', 'SELECT GETDATE()')
      ,(2, 'String', 'SELECT ''What''''s this? Just a varchar string...'' as StrVal')
      ,(3, 'Server', 'SELECT @@SERVERNAME')
;

CREATE TABLE results (Id int, Name nvarchar(128), Result nvarchar(4000));

-----

DECLARE @stmt nvarchar(max);

SELECT @stmt = stuff( (SELECT N'UNION ALL SELECT TOP (1) ' + CAST(Id as nvarchar(10))
                             +N' as Id, ''' + Name + N''', CAST(qr.[Result] as nvarchar(4000)) as [Result] '
                             +N'FROM (' + Query + N') AS qr ([Result])
'
                         FROM QueryList
                          FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
                     ,1,9,'INSERT INTO results') + ';'
;

SELECT @stmt; -- FOR TESTING - displays the resulting query

EXECUTE sp_executesql @stmt;

SELECT * FROM results;

The central query takes all the records from #QueryList, and builds an INSERT statement to place the id, name, and the query result from each row into the #results temp table. Each row's values are in their own SELECT statement, and all the statements are combined into a single result set with UNION ALL. I have taken text files with thousands of rows, converted each one into a similar SELECT ... UNION ALL, and used that to successfully insert thousands of records into SQL Server tables; I don't expect the number of statements being unioned together to be an issue (but I can't guarantee there isn't a limit at some point).

If you haven't seen it before, this uses FOR XML PATH to concatenate the UNION ALL SELECT ... from each row's values into a single string, the STUFF replaces the first UNION ALL with the actual INSERT INTO #results. See this article for more information on aggregating string data across multiple rows (or, move to SQL 2017 and use STRING_AGG()).

Something else that you may not have seen before is FROM (<subquery>) AS qr ([Result]). Just as the AS qr makes qr and alias for the subquery, ([Result]) provides a list of aliases for the columns (in our case, column) in the subquery.

Here's the query that's built from the above data:

INSERT INTO #results SELECT TOP (1) 1 as Id, 'Date', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT GETDATE()) AS qr ([Result])
UNION ALL SELECT TOP (1) 2 as Id, 'String', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT 'This is just a varchar string' as StrVal) AS qr ([Result])
UNION ALL SELECT TOP (1) 3 as Id, 'Server', CAST(qr.[Result] as nvarchar(4000)) as [Result] FROM (SELECT @@SERVERNAME) AS qr ([Result])
;

And, the contents of #results after the query is executed:

Id   Name     Result
---- -------- --------------------------------------
1    Date     Jul 14 2017 12:19PM
2    String   What's this? Just a varchar string...
3    Server   MYSERVERNAME\INSTANCE

Notes:

If your query cannot trivially be used as a subquery (for instance, if you had a query in #QueryList that used a CTE), this will fail.
If you included a closing ; at the end of a query in #QueryList, it will fail (special case of not being trivially used as a subquery; I almost included this in my #QueryList queries, by force of habit).
To be on the safe side, this allows for the possibility that some query may return multiple rows, and simply selects the first row as the "right" value, at random.
If you do run into an issue with the number of statements being combined via UNION ALL, you can break this down into smaller chunks. Assuming that Id and Name taken together, are unique in #QueryList, then:

SELECT @stmt = stuff( (SELECT TOP (10000) N'UNION ALL SELECT TOP (1) ' + CAST(ql.Id as nvarchar(10))
                                        +N' as Id, ''' +ql. Name + N''', CAST(qr.[Result] as nvarchar(4000)) as [Result] '
                                        +N'FROM (' + ql.Query + N') AS qr ([Result])
'
                         FROM #QueryList ql
                                LEFT  JOIN #results r ON (ql.Id = r.Id AND ql.Name = r.Name)
                        WHERE r.Id IS NULL
                          FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')
                     ,1,9,'INSERT INTO results') + ';'
;

would let you grab 1000 #QueryList rows at a time, and only grab ones where the Id and Name don't already exist in #results. You'd have to put this in a loop, and keep going until there were no more rows left in #QueryList. @stmt is NULL if there are no more rows to process, so you could use that to break out of the loop.

Again, I don't expect this to be an issue. And, even if it is, while it takes you back to a WHILE loop, you're dealing with 10,000 (or more) rows at a time, not one. It should be faster than your current cursor implementation.

And - if you find that there are some queries that cause problems, this could be useful for testing; go through subsets of data until you find a set that doesn't work, then check those queries to see if there's an issue with one.


Is it possible to pass in multiple values in the WHEN expression

I believe to have written a query that meets your request below:

DECLARE @table TABLE (UserID INT, Approval VARCHAR(10))

INSERT INTO @table
VALUES (1, 'Reject'), (2, 'Approve')

DECLARE @approvalStatus VARCHAR(10)
DECLARE @UserID INT

SET @approvalStatus = 'Approve'
SET @UserID = 2

UPDATE @table
SET Approval = CASE
        WHEN @approvalStatus = 'Approve'
            THEN 'Approved'
        WHEN @approvalStatus = 'Reject'
            THEN 'Rejected'
        END
FROM @table
WHERE Userid = (@UserID)
    AND Approval = (@approvalStatus)

SELECT *
FROM @table

I created a fake variable table and inserted some similar data as your example illustrates and then I have updated the data in the table and I finally do a select from it to show the value has changed.

Here's the MSDN on CASE WHEN, it will have more examples and more information for you to follow.

Notice how I changed your WHERE clause? This is because with a stored procedure it is hard to pass an array as a variable which is what you are attempting. If you want to make it an array you will need to use Table Valued Parameters.

I modified an answer by Nick Chammas on DBA Stack Exchange. They are using TVP's to pass an array. Here is a code example of your situation and how to pass an array into a stored procedure to update your table. Give this a try in a test environment where you can safely create and drop objects without impacting important systems.

-- Using a staging table now, table variables need to be seen when calling the proc.
-- Most of these pieces are one time runs, run the steps 1-4. Afterwards, the code is repeatable to run
-- and you can adjust the parameters you pass to the TVP to see the effects. I have added more sample
-- code to the table so you can see how it interacts with the data.

-- Step (1)
CREATE TABLE Test (UserID INT, Approval VARCHAR(10))

-- Step (2)
-- Creating test records
INSERT INTO Test
VALUES (1, 'Reject'), (2, 'Approve'), (3, 'test'), (4,'test2'), (5,'Approve'),
(6,'Reject')

-- Step (3)
-- This is our TYPE used for the TVP
CREATE TYPE id_list AS TABLE (
    id INT --NOT NULL PRIMARY KEY
    , approvaltwo VARCHAR(10)
    );
GO

-- Step (4)
-- This is a procedure you would use to update data
CREATE PROCEDURE [dbo].[tvp_test] (
    @customer_list id_list READONLY
    )
AS
BEGIN
-- Update the table
    UPDATE test
    SET Approval = CASE
            WHEN approval = 'Approve'
                THEN 'Approved'
            WHEN approval = 'Reject'
                THEN 'Rejected'
            -- This line updates with what was there previously. Without the ELSE, it would null
            -- cells that are not 'Reject' or 'Approve'
            ELSE Approval
            END
    FROM test t
    -- Joining on TVP to filter rows that should be updated
    INNER JOIN @customer_list cl
        ON t.userid = cl.id
            AND t.approval = cl.approvaltwo;
END;
GO

-- The rest of the steps are now repeatable
-- This is where your code would begin now
-- Declare your TVP
DECLARE @customer_list id_list;

-- Insert into the TVP, this is where you pass your array
INSERT INTO @customer_list (id, approvaltwo)
VALUES (1, 'Reject'), (2, 'Approve'), (3,'test');

-- Execute against the TVP and table
EXECUTE [dbo].[tvp_test] @customer_list = @customer_list;
GO

-- View results
SELECT *
FROM Test

One last note is that you may have to adjust your array in code before passing it to the TVP.


Why does max_used_connections status not get refreshed automatically once it reaches max_connections, even though the Threads_connected falls down

It doesn't come down because it isn't supposed to come down.

Max_used_connections is a status variable, and is defined as "the maximum number of connections that have been in use simultaneously since the server started."

It turns out, you can also reset the value to the current number of connections using FLUSH STATUS.

But that isn't a "partial solution" to anything because it doesn't change anything.  The only thing you are accomplishing with FLUSH STATUS is sort-of baselining the value of an information-only status variable: resetting the value you see, down to the current value of the status variable Threads_connected -- the current number of connections.

This action doesn't change anything about the server's behavior.  If you're seeing a change in behavior after doing that, it's coincidental.

mysql> SHOW STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 225   |
+-------------------+-------+
1 row in set (0.00 sec)

The above would be a more useful value for you to watch, while troubleshooting.  It shows the current count.

But addressing your problem, you either have an application that does not properly release its connections -- which needs to be fixed elsewhere, not in MySQL -- or you may be right -- you need to increase the value of the max_connections global variable -- after you consider what's written in C.5.2.7 Too many connections.  That page also explains why your output above shows a Max_used_connections of 151 compared to max_connections of 150.

Setting thread_cache_size to a value larger than max_connections seems like tremendously unhelpful advice... the cache can't possibly grow larger than max_connections and even a cache anywhere close to that size could only make sense if you have a tremendous amount of churn on your threads... which, in a well-behaved application, won't be the case.


Service Packs and CUs for SQL Server, do these patches make any changes to user database files? or just instance level binaries?

Service packs and CUs do not change user databases. What is changed is binaries and system databases. Sometimes, database that are not strictly system databases but are shipped by microsoft are changed as well via upgrade scripts (sysutility_mdw, SSISDB, distribution...)


Why does ALTER AUTHORIZATION on a table drop all permissions on the target?

Because the new owner don't want to be given an object that a bunch of other people has access to?
Imagine buying a new house. Do you expect other folks to have access to your new house? Or would you say that it is up to you to hand out the keys/codes to your new house - to the people you trust?
It is hard to respond to "why" questions, since we'd have to been in the meeting when this was decided in order to hear the reasoning going back and forth. But above is my guess.
(I thought about this possibly being from the SQL standard, but it doesn't seem like there's an ALTER AUTHORIZATION command in ANSI/ISO SQL...?)


Can you slow down queries for a specific user in a postgres database

To my knowledge, vanilla Postgres does not natively offer these features within the engine.  Per the Priorities PostgreSQL wiki page:

  PostgreSQL has no facilities to limit what resources a particular user, query, or database consumes, or correspondingly to set priorities such that one user/query/database gets more resources than others.  It's necessary to use operating system facilities to achieve what limited prioritization is possible.

As the last sentence alludes to though, there are ways to simulate this behavior by using connection settings (e.g. work_mem and maintenance_work_mem for memory) or OS level tricks with the help of extensions (e.g. prioritize for CPU prioritization).

If you've got a budget, there are customized Postgres engines that claim to provide this functionality as well, one of which is offered by EnterpriseDB.  I haven't used it nor do I have any idea how well it performs, but it's another option if you're looking for more alternatives.


I'm afraid not.

Your options are

install BIDS/SSDT and do the development on the server
develop locally and pay the network penalty
spin up local instances to negate the network penalty

I am a firm believer in the last option as it ensures my package development, pre 2012, used configuration to handle changing servers. If I deployed to Dev and it blew up, there was less stink eye directed my way vs Test when ZOMG, WE'VE BEEN WAITING ON THIS AND YOU'RE STILL MAKING CODING CHANGES!!!!

If it's a we don't allow data on developer machines, well it's a stupid rule and they need to either pony up for a tool like red gate's data generator or provide developers with a sane, safe, PII free backup they can use for local development purposes.


Is there a method I can first sort these fields (or even better have a sorted index of some kind) and then just compare those sorted lists or is there another approach to this?
although I'm thinking about adding a primary key constraint here in any case)

Yes.  A Primary Key constraint is implemented by creating a unique BTree index on the target column(s).  A BTree has, at its leaf level, a doubly-linked list of pages that is maintained in index key order.  And all the index key values are stored on these leaf pages.  There are also non-leaf pages that enable efficiently seeking a page containing a particular key value.  You can read all about this here: SQL Server Index Architecture.
So, when comparing two tables on their PKs SQL Server can either

Scan one table and perform cheap lookups on either table (~4 Logical IOs per lookup), using a Nested Loop Join.

or

Perform a Merge Join, where the sorted indexes are scanned in tandem matching rows.

If the PK is Clustered then scanning the just the key values is more expensive, as the rest of the columns are present on the leaf level of the index.  So a non-clustered primary key constraint is probably the best table design.  And a handy complement to a non-clustered PK is to store the rest of the table in a Clustered Columnstore.


How would I store the result of a select statement so that I can reuse the results to join to different tables?

You may store the results you want to reuse into a nested table type. In the following example, I use a nested table of number(22,0). A nested table in PL/SQL is not a real table, it just exists in memory like an array.

-- Creating some test tables and data
create table table1 (row_id number(22,0));
create table table2 (row_id number(22,0));

insert into table1 values(1);
insert into table1 values(2);
insert into table1 values(3);
insert into table2 values(1);
insert into table2 values(2);

---------

-- Create a nested table type
CREATE OR REPLACE TYPE type_table_number AS TABLE OF NUMBER(22);

declare
 v_tab type_table_number;
 cursor c is
   select row_id
   from   table2
   where  row_id in (SELECT row_id FROM TABLE (v_tab));
begin
  -- Select row_ids into the table v_tab
  -- (this is not a real table, the data is stored in memory)
  select row_id
  bulk   collect into v_tab
  from   table1
  WHERE  row_id in (1,2);
  -- Select some data from a table and use the content
  -- of the in memory table (v_tab) as search criteria:
  for v_rec in c loop
    DBMS_OUTPUT.PUT_LINE(v_rec.row_id);
  end loop;
  -- Select some other data from another table and also
  -- use the same content of the in memory table:
  -- ...
END;
/


Is it possible to enforce corresponding hierarchies?

Based on the comments to my question it looks like the solution is to add product_id to the primary key of both the part and product_instance tables.


Parsing the value and copying it to another column in postgresql

If your data is consistent, so that after a number it always contains a space and then something else, then the following expression will get you the number part:

split_part(original_size, ' ', 1)

This will split up your string along the spaces with the remaining chunks are indexed from 1 up to the last one, then the first one is picked.  There are, of course, other possibilities around, for example using a regex, which probably other will add later :)

Now you can construct your UPDATE statement:

UPDATE size
   SET processed_size = split_part(original_size, ' ', 1);

Notice that there is no WHERE clause as you want to set the second column for every row in the table.

You find an example on SQLFiddle.


Replication between SQL Server 2005 and Azure database

I'm not sure about the core of your question ("Can you set up replication between SQL Server 2005 and SQL Azure Database?"), but my assumption is a resounding "NO".

There are tools to migrate from SQL Server to Azure though - it's been discussed on Stack Overflow here and here.
The consensus seems to be that This SQL Azure Migration Wizard (CodePlex hosted project) is the way to go. It doesn't appear to be a live-sync migration though, so you will probably need to take an outage window unless I'm missing something...


PostgreSQL 9.5: Permission to deny functions body

As I know there is no possibility to restrict access to the DB metadata at least for reading in PostgreSQL.

As an alternative you can to create another DB for login(s) and use foreign-data wrapper to access to the desired object(s) in the your main DB.

Here is a simple scenario (not fully tested):

Login to the main DB and create test table:

create table t (x int);

Create another DB for login(s):

create database logindb with owner = postgres;
grant all on database logindb to postgres;

Create user:

create role testrole password '111' login nosuperuser noinherit;
revoke connect on database postgres from testrole;

Now login to the newely created DB with user postgres and create FDW:

create extension postgres_fdw;
create server myserver foreign data wrapper postgres_fdw options (host 'localhost', dbname 'postgres', port '5432');
create user mapping for postgres server myserver options(user 'postgres', password '111');
create user mapping for testrole server myserver options(user 'postgres', password '111');
create foreign table t(x int) server myserver options(schema_name 'public', table_name 't');
grant insert on t to testrole;

Theoretically thats all. You can use testrole to connect to the logindb and to insert data into the table t in the main DB postgres. And 'testrole' can not to connect to the main DB and see anything in it.


Can you set up mirroring for sql server reporting services?

Mirroring is for individual database.  For reporting services, you can user web server farms (AKA scale out deployment).  It will allow multiple servers in a cluster.  You will need SQL Server Enterprise Edition for this feature, though, as well as a load balancer (Windows has this or NW hardware like BigIP, etc.).  See here for more info:

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


Optimizing simple query that performs a large table migration

An UPDATE with a co-related subquery is typically quite slow, because the sub-query is run once for each row that is updated. It's typically faster to do that only once. To get the latest status for each train, you can use DISTINCT ON and join to the result of that:
update trains t
  set status = ts.status
from (
  SELECT distinct on (ts.id) ts.id, ts.status
  FROM train_statuses ts
  WHERE status in (?)
  ORDER BY ts.id, updated_at DESC
)
WHERE t.id = ts.id;


postgresql - Importing CSV Data with Custom Type

What is going on is a mismatch on the number of columns names and the number of columns in your data. Without information on how the csv is generated and how you are importing it is hard to say, but presuming the separator on your csv is the comma character, this is what the database insert operation is trying to do:
it reads this
'class','category','number','points'
and learns how to map four columns.
Then, when it gets to the second line, it is splitting the data by the commas, so it interprets that line as

('LS 30A'||(18||'W')|| 2)||'Homework'||1||60

where the || signifies a break between values. It interprets the command as:

set
  class = ('LS 30A',
  category = (18,
  number = 'W',
  points = 2)

...and then it does not know what to do with the rest of the data, as it does not fit.

My suggestion would be to use a different separator. It will depend on how the csv is being generated how you do that.


If you have remote access to the server you are transfering I suggest you make a full back up of the database put it in a FTP or torrent (last option not very safe but easier if you don't know how to build a FTP server). Then when the file is transfered to the other server, you restore the backup.
This option will save you the trouble with the internet since the transfer to files will be local and FTP or torrent transfers can be paused/resumed if case of connection failed.


Help - Error : Could not connect to master of shard replica set

the problem seems to be the version of mongodb. Somehow for mongodb < 2.6 socket problem will intermittently occurs. When it occurs, you have to restart your mongos node.


Quick search the most similar objects in the n-dimensional space


If it's showing in your processlist then it's still running.

In the future, for situations like this we're you're pruning 90%+ of the table consider this approach:

create table new_table like old_table;
insert into new_table (select * from old_table where <what you want to keep>)
rename table old_table to old_table_drop_prep, new_table to old_table;
drop table old_table_drop_prep;


You need to change the delimter to $$ and then back to ;:

DELIMITER $$
CREATE TRIGGER `before_delete`
BEFORE DELETE ON `persons`
FOR EACH ROW
BEGIN
  DELETE FROM ldap_entries WHERE ldap_entries.keyval = OLD.id
END $$
DELIMITER ;


How to reset the autoincrement field in Mysql data base?

You can reset the auto-increment column by using the following command:

ALTER TABLE table_name AUTO_INCREMENT = 10;

You can retrieve the most recent automatically generated AUTO_INCREMENT value with the LAST_INSERT_ID() SQL function.

If you wish to set auto-increment value to be 1, you can also truncate the table data.

TRUNCATE TABLE table_name;

I hope this answer will help you. Thanks.


Need to replace only first occurrence of string

This may come across as silly, but if the CMS ignores subsequent  tags, you shouldn't really care if it adds one after every  closing tag. Sure it may make the posts slightly larger than they should be, but since they're just going to be ignored, it seems silly to spend two days trying to not do that when at the end of the day it doesn't really matter whether you do or not.

That said, I am not a MySQL guy, but looks like this is as decent an approach as any, from this StackOverflow answer:

UPDATE wp_posts
 SET post_content = CONCAT(REPLACE(LEFT(post_content,
   INSTR(post_content, '')+4), '', ''),
   SUBSTRING(post_content, INSTR(post_content, '') + 5))
 WHERE INSTR(post_content, '') > 0;

or the (slightly simpler):

UPDATE wp_posts
 SET post_content = CONCAT(LEFT(post_content, INSTR(post_content, '')-1),
                           '',
                           SUBSTRING(post_content, INSTR(post_content, '')+ 5))
 WHERE INSTR(post_content, '') > 0;

Before you do that you may want to check to see how many rows this will affect:

SELECT COUNT(*)
  FROM wp_posts
  WHERE INSTR(post_content, '') > 0;

You'll probably want to add a WHERE clause to only identify those posts that actually contain an  tag (and you may need to define the requirements for what to do in those cases).


Mysql could not be started because unknown variable 'log-syslog=1'

I've run into the same problem as yourself and found this question while looking for solution. Eventually I found the culprit.

You will find that /etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf contains 2 rows:

[mysqld_safe]
syslog

The only thing you have to do is remove syslog. I just commented it out:

[mysqld_safe]
#syslog

And now it works.
It is weird that it comes preconfigured like that since it is deprecated/removed (I did a clean install of v8.0.15).


Partitioning mysql table on the basis of fulltxt(datatype:text) column

Cannot do it.  Syntax errors.  Would not help if you could do it.

Show us the SELECTs that you hope to speed up via PARTITIONing.  (I will probably point out why PARTITIONing will not help any of them.)


SSIS unpivot returns different results when run via a SQL agent job

I think I've fixed it. Thought I'd share the answer in case anyone else comes across this exact same issue. All it took was the addition of IMEX=1 to the Excel connection string.

I stumbled across this post which explains more: Why SSIS always gets Excel data types wrong, and how to fix it!


Is it possible to list column entry only once?

I would say this is easier on the representational layer but if you want it in pure MySQL this will do it:

set @current='';
select show_basket, content
from (
    select case
        when @current = basket
            then ''
            else basket
        end as show_basket,
        content, @current:=basket
    from test_131547
    order by basket
) a;

Output:

+-------------+---------+
| show_basket | content |
+-------------+---------+
| 1           | lemon   |
|             | lime    |
|             | apple   |
|             | banana  |
| 2           | kiwi    |
|             | apple   |
|             | orange  |
+-------------+---------+


Comparison between A non-clustered primary key and a covering index in terms of performance

By removing the primary key and adding a unique covering index with more columns, you are fundamentally changing how the table accepts data.  It may be that with the covering index in place, the table design no longer fits the purpose it was designed for.

Take for instance:

USE tempdb;

IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL
DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
    ID1 int NOT NULL
    , ID2 int NOT NULL
    , SomeDate datetime2(7) NOT NULL
    , CONSTRAINT PK_T1
        PRIMARY KEY NONCLUSTERED
        (ID1, ID2)
);

INSERT INTO dbo.T1 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE())
WAITFOR DELAY '00:00:00.003';
INSERT INTO dbo.T1 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE());

The output:

  (1 row(s) affected)
  Msg 2627, Level 14, State 1, Line 18
  Violation of PRIMARY KEY constraint 'PK_T1'. Cannot insert duplicate key in object 'dbo.T1'. The duplicate key value is (1, 1).
  The statement has been terminated.

The table design is only allowing rows where the tuple (ID1, ID2) is unique.

Then look at this, where the primary key does not exist, and has been replaced with a wider unique covering index:

IF OBJECT_ID(N'dbo.T2', N'U') IS NOT NULL
DROP TABLE dbo.T2;
CREATE TABLE dbo.T2
(
    ID1 int NOT NULL
    , ID2 int NOT NULL
    , SomeDate datetime NOT NULL
);

CREATE UNIQUE INDEX IX_T2
ON dbo.T2 (ID1, ID2, SomeDate);

INSERT INTO dbo.T2 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE())
WAITFOR DELAY '00:00:00.003';
INSERT INTO dbo.T2 (ID1, ID2, SomeDate)
VALUES (1, 1, GETDATE());

  (1 row(s) affected)

  (1 row(s) affected)

Table T2 has both rows, where arguably, the second insert should have failed and the table should only have a single row.  In this design, the table is now accepting rows where the tuple (ID1, ID2) are not necessarily unique.

I'd think carefully about your table design, and potentially just add the covering index without removing the primary key.


Is there any general official MS guide for planning SQL Server hardware for instance:

No.  The hardware requirements for 500 concurrent connections is too dependent rate and cost of the queries that the connections execute.  That's why you see this kind of prescriptive configuration guidance from applications (like SharePoint), after they have tested the application at different scales.

If you can break down the workload by type of request, with peak sustained request rates for each, and cost for each in terms of CPU time, Logical IO, and Log IO required you can start to get a first-order approximation of the required hardware.  EG for

SmallRead  - 100 rps,   2ms CPU,    100 LIO,   0 Log IO
BigRead    - 10  rps, 400ms CPU, 100000 LIO,   0 Log IO
SmallWrite - 80  rps,  40ms CPU,     10 LIO,   2 Log IO
BigWrite   - 2   rps, 400ms CPU,  10000 LIO, 200 Log IO

200+4000+3200+800 ~ 8000 ms/s of CPU time, or 8 about cores.

The Logical IO depends on the size of database, size of memory, and cache hit ratio, so it's hard to esitmate.  But having RAM >= 20% of database size typically provides good caching.

And the the IOPS at the log are also hard to estimate, because concurrent transactions piggyback on log IOs.

For Data Warehouse workloads Microsoft does produce prescriptive guidance for hardware configuration in the Data Warehouse Fast Track program that enables hardware vendors to produce reference configurations rated by data warehouse size.   But that's only possible assuming that Data Warehouse workloads are all very similar to each other.

But if your workload uses a very large database and needs to support analytics, the Fast Track configuration guidance might be relevant.  If you're thinking about using it for a hybrid or OLTP workload, though you'll need to use one of the variants that use all-flash storage.


psql, getting a list of databases to be parsed by a script

I don't see how your two solutions are different...  Since it still has to actually CONNECT to a database instance to see what databases are there, I'm willing to bet the only different is HOW you are connecting.

psql -l -A -t

vs

psql --quiet --no-align --tuples-only --dbname=postgres --username=postgres --host=127.0.0.1 --port=5432 --command="SELECT datname FROM pg_database"

Try instead

psql -q -A -t -c "SELECT datname FROM pg_database"

This should use the default connection settings (database and login as local account, local connection, 5432 port).  If you really need to, then specify the user and database with -U postgres -d postgres .. but otherwise just leave the host and port unspecified.

I'm willing to bet the only reason psql -l -A -t isn't asking for a password is because you have that information in your .pgpass file.


Extended Events Login audit - Reduce login noise

Reason for the numerous connections
Those are connections caused by SSMS to provide you mainly the visual structure of object explorer. Also, When you're using SSMS, each new tab you open is a different session (and more connections - see The multiple connections of SSMS). IntelliSense also creates extra connections as you'll see if you remove the predicate on the query I suggested as a workaround.
try connecting with sqlcmd Utility like this on a CMD prompt:
sqlcmd -A

When I connected with SQLCMD only one log was reported on the Extended Event Session you provided.
Workaround
Instead of filtering the data you collect I suggest you filter the data you query afterwards, here's one way to do that:
WITH
    event_data AS (
        SELECT CAST(event_data AS XML) AS event_data_xml
        FROM fn_xe_file_target_read_file('D:\audits\Sessions\ServerLoginAudit*.xel',NULL,NULL,NULL)
    )
    , event_data_fields AS (
        SELECT
             DATEADD(ms, -DATEPART(ms, event_data_xml.value('(/event/@timestamp)[1]', 'datetime')), event_data_xml.value('(/event/@timestamp)[1]', 'datetime')) AS interval
            , event_data_xml.value('(/event/data[@name="database_name"]/value)[1]', 'varchar(250)') AS database_name
            , event_data_xml.value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(250)') AS client_app_name
            , event_data_xml.value('(/event/action[@name="client_hostname"]/value)[1]', 'nvarchar(250)') AS client_hostname
            , event_data_xml.value('(/event/action[@name="server_principal_name"]/value)[1]', 'nvarchar(250)') AS server_principal_name
            , event_data_xml.value('(/event/action[@name="nt_username"]/value)[1]', 'nvarchar(250)') AS nt_username
        FROM event_data
    )
SELECT
      interval
    , database_name
    , client_app_name
    , client_hostname
    , server_principal_name
    , nt_username
FROM event_data_fields
WHERE
    client_app_name NOT IN ('SQLServerCEIP', 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
GROUP BY
      interval
    , database_name
    , client_app_name
    , client_hostname
    , server_principal_name
    , nt_username;

With that method, if you don't find the info you seek at first, you can reduce the predicates to show all data collected and have a more accurate report of all the connections that happened at the time. Also, if you filter the data you collect by the application name, it would leave a breach on your audit because one can Change the Application Name of SSMS and login to your server unnoticed.


mySQL trigger - prevent insert won't work for multiple inserts

This is expected behaviour: if you raise a SIGNAL, the entire statement is rolled back; if you set new.val to NULL, you insert NULL (I guess your client is messing with you by showing zeroes for NULL values).

You could work around this problem by not inserting ineligible records in the first place, something like this:

insert into test2 (val) select val from (
  -- values to insert
  select 1 as val union all
  select 3
) i
where not exists (
  select 1 from test2 t where abs(t.val - i.val) <= 1
)


Incorrect datetime value for str_to_date

Try it:

UPDATE subscribers SET srtdt = STR_TO_DATE(StartDate, '%Y-%m-%d');


Find rows where integer sequence contains a given subsequence

If you are looking for significant performance improvements to dnoeth's answer, consider using a native C-function and creating the appropriate operator.

Here is an example for int4 arrays. (A generic array variant and the corresponding SQL script).

Datum
_int_sequence_contained(PG_FUNCTION_ARGS)
{
    return DirectFunctionCall2(_int_contains_sequence,
                               PG_GETARG_DATUM(1),
                               PG_GETARG_DATUM(0));
}

Datum
_int_contains_sequence(PG_FUNCTION_ARGS)
{
    ArrayType  *a = PG_GETARG_ARRAYTYPE_P(0);
    ArrayType  *b = PG_GETARG_ARRAYTYPE_P(1);
    int         na, nb;
    int32      *pa, *pb;
    int         i, j;

    na = ArrayGetNItems(ARR_NDIM(a), ARR_DIMS(a));
    nb = ArrayGetNItems(ARR_NDIM(b), ARR_DIMS(b));
    pa = (int32 *) ARR_DATA_PTR(a);
    pb = (int32 *) ARR_DATA_PTR(b);

    /* The naive searching algorithm. Replace it with a better one if your arrays are quite large. */
    for (i = 0; i <= na - nb; ++i)
    {
        for (j = 0; j < nb; ++j)
            if (pa[i + j] != pb[j])
                break;

        if (j == nb)
            PG_RETURN_BOOL(true);
    }

    PG_RETURN_BOOL(false);
}

CREATE FUNCTION _int_contains_sequence(_int4, _int4)
RETURNS bool
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

CREATE FUNCTION _int_sequence_contained(_int4, _int4)
RETURNS bool
AS 'MODULE_PATHNAME'
LANGUAGE C STRICT IMMUTABLE;

CREATE OPERATOR @@> (
  LEFTARG = _int4,
  RIGHTARG = _int4,
  PROCEDURE = _int_contains_sequence,
  COMMUTATOR = '<@@',
  RESTRICT = contsel,
  JOIN = contjoinsel
);

CREATE OPERATOR <@@ (
  LEFTARG = _int4,
  RIGHTARG = _int4,
  PROCEDURE = _int_sequence_contained,
  COMMUTATOR = '@@>',
  RESTRICT = contsel,
  JOIN = contjoinsel
);

Now you can filter rows like this.

SELECT * FROM sequences WHERE sequence @@> '{12, 742, 225, 547}'

I have conducted a little experiment to find how much faster this solution is.

CREATE TEMPORARY TABLE sequences AS
SELECT array_agg((random() * 10)::int4) AS sequence, g1 AS id
FROM generate_series(1, 100000) g1
  CROSS JOIN generate_series(1, 30) g2
GROUP BY g1;

EXPLAIN ANALYZE SELECT * FROM sequences
WHERE        translate(cast(sequence as text), '{}',',,')
 LIKE '%' || translate(cast('{1,2,3,4}'as text), '{}',',,') || '%'

"Seq Scan on sequences  (cost=0.00..7869.42 rows=28 width=36) (actual time=2.487..334.318 rows=251 loops=1)"
"  Filter: (translate((sequence)::text, '{}'::text, ',,'::text) ~~ '%,1,2,3,4,%'::text)"
"  Rows Removed by Filter: 99749"
"Planning time: 0.104 ms"
"Execution time: 334.365 ms"

EXPLAIN ANALYZE SELECT * FROM sequences WHERE sequence @@> '{1,2,3,4}'

"Seq Scan on sequences  (cost=0.00..5752.01 rows=282 width=36) (actual time=0.178..20.792 rows=251 loops=1)"
"  Filter: (sequence @@> '{1,2,3,4}'::integer[])"
"  Rows Removed by Filter: 99749"
"Planning time: 0.091 ms"
"Execution time: 20.859 ms"

So, it is about 16 times faster. If it is not enough, you can add support for GIN or GiST indexes, but this will be much more difficult task.


Database Mirroring setup does not work when the database is above a certain size, logs out of synch

As has already been noted there is likely to be a third party tool taking backups which you weren't aware of.  Stopping that (or at least having control over it) is going to assist no end (obviously be aware that stopping log backups may invalidate the RPO/RTO targets and taking adhoc full and log backups will invalidate the log chain and may make dr recovery more complex or result in larger data loss during this period).

You've mentioned that you are keen to reduce the downtime required; I believe you have a few options available to you to assist.

I note that you state SQL Server 2012.  Backup compression is available in standard edition upwards, enabling this will reduce IO (and therefore time) for both your backup and restore at a relatively small cost of CPU.  I have seen backups reduce by nearly half using compression (depending on what is stored in the DB).  Simply add compression to your with options.  This can be used in conjunction with the below to reduce the outage window further.

Option 1 - Use a differential backup

Rather than taking a full backup at your outage time, take a full backup (or last nights if done daily) and restore that during the day of migration to your other servers.  You could then take a differential at your maintenance window.  As a differential backup only contains the changes since the last full backup it will be much smaller and faster than a full backup.  It also doesn't have any dependency on log backups so as long as your full backup is good you can apply a differential to get it in sync.  Be aware though, the differential must be taken before the next full backup so either know that the planned window is before the nightly backups or suspend the full backup for this database.

Therefore you could:

Take the full backup on A and restore it to both B (NORECOVERY) and C (NORECOVERY) with the application still up (and log backups continuing).
Wait until your outage time, stop log backups and take a differential backup
Update your connection string (or CNAME)
Restore differential to A (with RECOVERY) and B (with NORECOVERY)
Initiate the mirror between B and C
Start log backups on B

Option 2 - Mirroring

As you are not in a mirrored configuration currently you could look to create the mirror between A and B first; even if going up SQL Server versions mirroring will still work one way (from older to newer versions).  Restore and mirror would be done with the application still online, without requiring downtime.

This might look something like:

Backup Server A and restore to B (NORECOVERY)
Stop log backups on A, take differential and restore to B
Initiate mirror (A --> B)
Restart log backups
When your outage window comes up, stop the application services, change the DNS CNAME (or connection string)
Failover the mirror (from server A alter database [mydb] with partner failover;
start the application services again
Start log backup schedule on B
Backup full db on B
Restore to C (NORECOVERY)
If log backup has been taken in time between backup and restore from B to C then take differential on B and restore to C
Initiate mirror

Option 3 - Log shipping

By utilising log shipping you can actually seed both B and C simultaneously.

The process would be similar to below:

Take full backup and restore to B and C (NORECOVERY)
Stop log backups (permanently on third party tool)
Take and restore differential to B and C (NORECOVERY)
Start log shipping configuration with 2 secondaries (B and C, NORECOVERY).  This will be creating the log backup files so may need to mention this to the backup guys so they capture the location, note that the SQL Agent services will need to have read/write permissions over the share(s) for this to work.
When downtime outage comes, take final log backup and restore to secondaries (jobs found in agent for backup/restore jobs on respective instances)
Change CNAME (or connection string)
Bring server B database out of restoring state restore database [mydb] with recovery
Initiate mirror
Start log backups on Server B


Images, PDF, audio and video files in MySQL

My recommendation is, as Max has suggested, to store on the filesystem and store a path in the db as varchar.  This has the advantage of not having to deal with the files in the db.  Even on other db's I think storing frequently used binary files in the db is asking for performance issues (storing less frequently used files in the db, OTOH, is a good thing).

However it does have the disadvantage of requiring multiple backups to get all the data your data needs.  A secondary option might be to store the files in the db as a blob (in a separate table) and then store it on the filesystem too.  This way you can just recreate the file from the db if it is missing and you can count on your database to be a complete set of the files for backup purposes.  This may be needless complexity though.

Also if you are storing video files in the db, one real reason to go to PostgreSQL would be the stream-based LOB interface which would allow you to chunk the output.


Change a specific line of a view with conditions

This is very easy to achieve with PowerShell (in which you can use Regular Expression to do your work)
But here for simplicity purpose, I will use a t-sql solution with a simplified example as shown below

-- assume you have a view called dbo.v and you want to replace one line in this view
use tempdb
go
drop view dbo.v
go
create view dbo.v
as
    select A = 'test 1'
    union all
    select A = 'test 2' -- we will change this to 'test 222'
    union all
    select A = 'test 3'
go
-- assume we will replace the line select A = 'test 2'
declare @sql varchar(max);
declare @original_string varchar(300) = 'select A = ''test 2''';
declare @replace_string varchar(300) = 'select A = ''test 222''';

select  @sql=definition from sys.sql_modules
where object_id = object_id('dbo.v')

set @sql = replace(@sql, @original_string, @replace_string);
set @sql = replace(@sql, 'create view', 'alter view');
--print @sql -- uncomment it if needed

exec (@sql)
go


PostgreSQL recovery file syntax, ver 8 vs ver 9

One benefit of the new syntax is that your restore_command doesn't need to implement its own polling for newly arrived log files, and doesn't have to check for failover triggering, because the core code now does that for you.  That means your restore_command only needs to focus on finding and copying the file, which makes it much simpler and easier to get right.  For example, if you need to fetch your archive from AWS S3 storage, for example, you don't want to also need to implement your own polling and failover.

Another benefit is that it integrates cleanly with streaming replication.  So it can use the WAL archive to get caught up, but then switch to streaming so that it stays caught up in near-real-time.  And then goes back to using the WAL archive automatically if it ever falls too far behind.


Does SELECT remove dead rows like VACUUM does?

From this post on /r/PostgreSQL to an answer by Laurenz Albe it seems that Heap Only Tuples (HOT) updates may be responsible. From the description of HOT updates in src/backend/access/heap/README.HOT

  Effectively, space reclamation happens during tuple retrieval when the
  page is nearly full (<10% free) and a buffer cleanup lock can be
  acquired.  This means that UPDATE, DELETE, and SELECT can trigger space
  reclamation, but often not during INSERT ... VALUES because it does
  not retrieve a row.

The quote is not in the original answer, but the rest is a quote,

  To support or refute this theory, run the following query:

SELECT n_tup_upd, n_tup_hot_upd
FROM pg_stat_user_tables
WHERE schemaname = 'public' AND relname = 'TABLE_NAME';

  If n_tup_hot_upd is greater than zero, we have got a case.


Can you gain the performance benefits of partition elimination without changing SQL module or table definitions?

Possibly, but in my experience you don't partition data for performance reasons. Yes, partition elimination can reduce the IO that some queries consume but there are complications that partitioning will introduce, see here for my favourite.

What partitioning does help with is maintenance. You can rebuild indexes, and stats (on SQL Server 2014+) at the partition level. This can be a huge win when you struggle to keep maintenance operations inside your maintenance window.

Partitioning is also great when it comes to loading and archiving data. The SWITCH statement is your friend here.

Lastly partitioning can also be used to optimise columnstore indexes by introducing a level of ordering to the data. This helps with dictionary pressure. Each partition acts as it's own columnstore index which means you get more global dictionaries as well as delta stores and delta bitmaps. Niko's blog has much much more info here


Delayed durability - can data loss be prevented in case of crash

When you enable delayed durability, transactions aren't written to the transaction log file before they are committed.

That's why transactions aren't available in the log, and they will be permanently lost in that scenario.


Inconsistent Execution Plan for Stored Procedure

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

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

The part of the plan you are referring to is:

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

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

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


SQL server is getting stuck all of a sudden

Try running sp_Blitz, a free health check for your SQL Server (disclaimer - I'm the author).  You can also run it with @OutputType = 'markdown' if you want to share the results here at Stack.

I've got a hunch that you're running into THREADPOOL waits, and sp_Blitz alerts about that. THREADPOOL means your SQL Server ran out of worker threads to service incoming queries. It won't show up in the OS or SQL Server error logs.

When it's happening, you'll be able to connect to SQL Server using the Dedicated Admin Connection (DAC) (disclaimer: that's a blog post on my site.) The DAC is a set-aside CPU scheduler used just for emergency troubleshooting. From there, you'll be able to see which queries are burning up all the worker threads - typically it's a blocking problem.


SQL Server ReadOnly Routing Connection String

You would be required to set the Secondary Replica Copy as Readable, different from Read-Intent, and point the connection string to the actual instance where the replica resides.

This is not advisable, as it negates the HA usability of any Read-Intent replicas during failover.


Install and Configure Oracle 11g using c# program

For Oracle client you can use Installation using Response Files.
Your C# program can generate response file and run installer with particular command line options.


How to split one table into two in a single statement?

Modelling scheme:

CREATE TABLE old_table (id INT, val1 INT, val2 INT);
INSERT INTO old_table VALUES (11,111,1111), (22,222,2222);

CREATE TABLE new_table1 (id INT GENERATED ALWAYS AS IDENTITY, val1 INT);
CREATE TABLE new_table2 (id_table1 INT, val2 INT);

WITH cte AS
(
INSERT INTO new_table1 (val1)
SELECT val1
FROM old_table
RETURNING id, val1
)
INSERT INTO new_table2 (id_table1, val2)
SELECT cte.id, old_table.val2
-- use both old table
FROM old_table
-- and CTE table
JOIN cte
    ON old_table.val1 = cte.val1; -- the relation is set
                                  -- by the value saved into new table
                                  -- not by old primary index

SELECT * FROM new_table1;
SELECT * FROM new_table2;

id | val1
-: | ---:
 1 |  111
 2 |  222

id_table1 | val2
--------: | ---:
        1 | 1111
        2 | 2222

db<>fiddle here

Pay attention - in last INSERT both CTE and source table are used.


Under what circumstances can a division expression reduce the number of rows in a query's result?

You simply cannot change the number of rows returned by changing the SELECT clause.  The problem must lie elsewhere.

If you're absolutely sure that's what's happening, I would run DBCC on the table to make sure it's not corrupted.


There are really two questions in this question:

Why is the query slow?
Why are tempdb properties inaccessible?

Let's start with #2.
Why are tempdb properties inaccessible?
You get this error:

Cannot show requested dialog. (SqlMgmt) Property SpaceAvailable is not
available for Database '[tempdb]'. This property may not exist for
this object, or may not be retrievable due to insufficient access
rights. (Microsoft.SqlServer.Smo)

This will happen when you create a temp table inside a transaction. The CREATE TABLE holds metadata locks on system tables that blocks the queries to populate the database properties window, and causes the opening of that dialog to time out & show this error.
If you run this statement in SSMS, and try to open the tempdb properties window at the same time, I expect it will open just fine:
SELECT o1.object_id
INTO #blep
FROM sys.objects o1
CROSS JOIN sys.objects o2
CROSS JOIN sys.objects o3
CROSS JOIN sys.objects o4
CROSS JOIN sys.objects o5;

However, with the same query dropped into a transaction, and you will get the above error that you reported getting:
BEGIN TRANSACTION
SELECT o1.object_id
INTO #blep
FROM sys.objects o1
CROSS JOIN sys.objects o2
CROSS JOIN sys.objects o3
CROSS JOIN sys.objects o4
CROSS JOIN sys.objects o5;

While you didn't provide the code beyond what is included in the plan, the behavior would indicate that your SELECT...INTO is happening within a transaction, similar to the above example.
I would recommend never creating a temp table within a transaction, due to the blocking on system tables within tempdb. This affects concurrency, in addition to operational issues like the one with the database properties window.
If your procedure requires the use of transactions, you should create the table OUTSIDE the transaction, then begin the transaction & do the necessary work. If you depend on the behavior of SELECT...INTO to define the schema (ex, with a dynamic/unknown schema), use SELECT...INTO...WHERE 1=2; to create the table empty, then proceed after that to begin your transaction and do work.
Now, on to the other question:
Why is the query slow?
I'm not sure which line in the sp_whoisactive output correlates to your query. All of them? Session_id 797 is showing high CXCONSUMER waits, and has been running for 2 hours. I'll assume that is the problematic session. It has performed almost 57.9 million milliseconds of CPU cycles (967 minutes) in 2h 12mi of "real" time (132 minutes). That's about 7x more CPU time than "real" time--which certainly indicates that it is busy doing parallel work.
I can tell from the schema that this is an SAP database. The SAP schema is a bit of a quirky one, with the MANDT column being the leading column on every clustered composite index. This column is basically "company code" and used to separate data for various subsidiaries. SAP supplies this column within the join/filter predicate on EVERY query to ensure it's working with data for the right company. If your company doesn't keep separate books for separate subsidiaries, then the MANDT column is always the exact same value.
Because the non-selective predicate is supplied, it can trick SQL Server into showing a seek on the clustered primary key, when it's really doing a table scan. If every row has a value of AMTWO for MANDT, and you query AND MANDT = 'AMTWO', the optimizer might think "Hey, that's the leading column on the PK, I can seek to that!" But in reality, it seeks to the first occurrence of that value (ie the first row) and reads through to the last occurrence of that value (ie, the last row). Because every row is exactly the same value, so it has to scan every row, even though it shows as a seek in the plan.
The reality is that there's a better index to use, but SQL Server didn't realize that another index (or combination of indexes) would perform better.
Additionally, depending on your SQL Server version and whether you are using Trace Flag 2389, SAP often struggles with the "Ascending key problem" related to their heavy use of dates to key transactional data.
Both of these problems can be resolved by aggressively updating statistics on your SAP database.
There certainly could be something more going on with your query more than stats, but based on my experience with SAP, ensuring you have up-to-date stats would be at the top of my priority list.
You say it "started to hang on transaction for many hours"... how fast was it running before? Was it processing a similar amount of data was fast? The plan indicates it's working off just over a million rows in #repositioning, 200k rows in #stock and 65 million rows in MSEG---so I wouldn't expect the query to be extremely fast to begin with.
After you update stats, I'd be keen to capture actual IO & CPU costs for an execution of this code, using something like Plan Explorer, as that often gives much more insight into the problem, particularly if it's a case where SQL Server has selected a "bad" plan, and a better/faster plan exists but wasn't chosen.


SQL Server 2012 Simple Recovery Model with LOG_BACKUP log_reuse_wait_desc

Alright, spent yesterday day and night investigating, testing and trying to reproduce the problem. Found the root cause:

MODEL database set to the SIMPLE recovery model.

If the model database has been set to the SIMPLE recovery model, and user databases are created with SIMPLE recovery model, SQL Server somehow is treating it as if it is in the FULL recovery model. Hence waiting for LOG_BACKUP to truncate the log.

I have listed the steps in this link to show how I reproduced the problem.

I think the root cause is if a user database was created (not alter after) with simple recovery model, then it has this problem. I blame it on model database as that is the only way to create a user database with the defined recovery model.

This documented in Microsoft Knowledge Base article 2830400 and is fixed in SQL Server 2012 SP1 CU4 and RTM CU7:

FIX: Database does not follow simple recovery model behavior in SQL Server 2012 after you set the recovery model of the "model" database to "Simple"


Use specific array column in the where clause

You put the cast outside the array constructor - use ::varchar[] instead.  The current query will return you the array literal (which is of type varchar) instead of an actual array - see the example SQL Fiddle.

But overall, if you only need the output as an array downstream, it would make your life easier if you returned proper columns upstream - easier to use those in a WHERE clause, for example.  Then construct your array directly before returning it to whatever that needs it.


Basically the philosophy seems to be that its not worth putting much effort on compacting the DB if it will grow again. The way the BDB engine works makes it hard to really reclaim any freed space on workloads that have much insert/update activity, and I think JMS persistence quite possibly is such a workload. The gain from this philosophy, of course, is that on a burst of new messages the DB doesn't need to allocate more pages but can directly write the data on existing pages in the most efficient way. But if the hit on retrieval performance is significant it might be that BDB is indeed not right choice for you workload.

I wonder if the answers provided in these posts in the Oracle forums provide any light to the mystery (the quote comes from the second link).

Understanding compact and database file size

Berkeley DB database file size keeps growing

  There is no specific maximum size that the database must reach before
  page reuse starts. Also, you don't need to close and reopen the
  database handles in order to influence page reusing. BDB reuses pages
  when they are emptied and it does not perform any kind of automatic
  key/page balancing (as it will lead to deadlocks). The factors that
  you should be looking into are the insert, update and delete rates, if
  transactions are long-lived, the deadlock detection policy, the size
  of the database page size and the page fill factor etc.

  One example that might explain the behavior you're seeing is one where
  the delete process/thread(s) gets "late" to a page when trying to
  remove on key found on that page. By the time it acquires the write
  lock on that page, there are no more consecutive keys there, and at
  best the delete process/thread only manages to remove just part of
  those keys (the other keys are ones with higher values, as the insert
  and update processes/threads are far ahead), thus the page will not be
  emptied so that it can be placed onto the free list for reuse. If the
  insert and update processes are very active, this may result in rapid
  page splits (or new page allocations), hence sets of keys are moved
  over onto new pages. In addition, the new keys inserted may land on
  already populated pages, thus making things more difficult for the
  delete process. If the new keys being inserted can be distributed
  based on their values to already populated pages where there is room
  for them, then the pages on the free list, if any, will not be reused.


SQL Server Metric Report Information For Management

If as you say your management don't give you clear directions, I think it gives you an opportunity to be proactive and show them you're worth more than they're currently paying you. Listen to other people reporting their metrics and figure out what management feels is important from the business perspective.

"X batches per second"? Meh. "My databases can support up to x online orders per second with current hardware; this meets our peak workload of y orders per second on Black Friday". Sounds useful.

"Backup status"? Meh. "If hardware fails, we'll be able to be back online in Z minutes". Sounds useful.


How to delete previous incarnation in MySQL (/w Group Replication)

Solved by bringing down the entire cluster, as mentioned in a similar issue here. After the restart, bringing the GR cluster back up from scratch had no mentions of incarnations.


Multiple values shouldn't be assigned to a scalar variable. It makes no sense

You need a table variable (or temporary table).

Declare @StudentName TABLE (sname Nvarchar(max));
INSERT @StudentName (sname) VALUES ('ABC'),('DEF'),('PQR'),('XYZ');

You can JOIN etc to this as you would a normal table. Or use it in an IN clause

WHERE
    X.SomeCol IN (SELECT sname FROM @StudentName)

Edit, after comments

INSERT @StudentName (sname)
SELECT ... -- the other query here


MySQL Workbench - Creating a one-to-one relationship using existing columns?

Right click on the relationship line
Select 'Edit Relationship'
Click on the Foreign Key tab in the Relationship Tab
The Cardinality of the relationship can be changed with the two radio buttons


In BEFORE UPDATE TRIGGER, how do I prevent changing nothing but timestamp?

Make a copy of old, update the timestamp and compare it with new.

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    DECLARE
        rec record;
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            rec=old;
            rec.timestamp = NEW.timestamp;
            IF rec IS DISTINCT FROM NEW THEN
                    RETURN NEW;
            ELSE
                    RAISE WARNING 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;

(edit: thanks @ Granny Aching)

If you don't need OLD later in the trigger you can modify it without harm.

CREATE OR REPLACE FUNCTION verify() RETURNS TRIGGER AS $verify$
    BEGIN
        IF (TG_OP = 'UPDATE') THEN
            OLD.timestamp = NEW.timestamp;
            IF  OLD IS DISTINCT FROM NEW THEN
                    RETURN NEW;
            ELSE
                    RAISE WARNING 'Must change something besides timestamp.';
                    RETURN NULL;
            END IF;
        END IF;
        RETURN NEW;
    END;
$verify$ LANGUAGE plpgsql;


Database table update using single mysql query

Many ways to do this.

One way is using case when ... (assuming no NULLs or other gender values):

update yourtable set gender = (case when gender ='F' then 'M' else 'F' end);


I worked on a similar project, and we asked the source institution to provide a breakdown of the field types, lengths, and delimiters. We then stored the source files as-is for auditing, and processed them into tables in SQL Server.

Ask the bank for a key to the file layout. Guessing and asking the Internet is setting yourself up for failure.


Incremental backups: MySQL Enterprise Backup vs Percona XtraBackup?

ZFS beats both by light years with snapshots and incremental transfers and transparent compression.
ZFS snapshots can be transferred incrementally. You only transfer the blocks that have changed between the two snapshots. And you get lz4 compression on everything for close to free, and the snapshot sending doesn't have to decompress and recompress.
It is guaranteed to be consistent. ZFS being copy-on-write, either the entire transaction sticks, or the entire transaction is lost, nothing in-between. Unless, of course, you disabled safeties at database level, in which case all bets are off anyway. - gordan-bobic


There is no way that saves you from specifying all JSON attributes in the select list, whether implicitly through the "dummy type" or explicitly, e.g. using something like this:

select json_column ->> 'text1' as text1,
       json_column ->> 'text2' as text2,
       ...
from the_table;

What you can do, is to make this simpler by automatically creating a view with all attributes based on the distinct attributes in the JSON documents.

The following code will re-create a view with all distinct keys from the JSON column:

do
$$
declare
  l_keys text;
begin
   drop view if exists v_json_view cascade;

   select string_agg(distinct format('json_column ->> %L as %I',jkey, jkey), ', ')
     into l_keys
   from the_table, json_object_keys(json_column) as t(jkey);

   execute 'create view v_json_view as select '||l_keys||' from the_table';
end;
$$
;

You will need to run the above every time the list of keys in all json documents changes. Theoretically this could be done in a trigger but that's probably not a good idea if you run many updates on that table.

If the total number of JSON keys is somewhat "stable", you could schedule a cron job to re-create that view in regular intervals.

You are also limited by the maximum number of columns in a table or view. If you have more (distinct) keys than approx. 1600 (maybe less) the above will fail.


Both your queries are fine, assuming that (ID) is the primary key of the table (or it has a UNIQUE constraint).

The 2nd query can be simplified, removing the derived table:

SELECT t1.*
FROM table as t1 INNER JOIN
     table as t2
     ON t2.UserID = t1.UserID AND t2.GroupID = t2.GroupID
WHERE t2.ID = @id ;

For performance, you may need an index on (UserID, GroupID) - with any of the queries.


What's the best method to refresh only a few tables within a test database from production?


Now, I personally can't really think of a way of speeding this kind of query up, in the end it has to scan the entire table to perform these aggregations. But just because I can't think of any way doesn't mean there isn't one.

That's basically right, and isn't unique to GROUPING SETS. If you are scanning a lot of blocks then IO will likely dominate execution time so you have only two strategies:

Make IO faster
Reduce IO

Regarding faster IO, you need to look at hardware or configuration changes. Parallel query was mentioned in the comments, but this could be faster or slower and isn't a magic bullet for IO.

Regarding reducing IO, note that your query does not necessarily need to look at the table at all — it could use an appropriate covering index instead:

  Covering indexes are indexes creating for the express purpose of being used in index-only scans. They typically "cover" more columns than would otherwise make sense for an index, typically columns that are known to be part of particular expensive, frequently executed query's selectlist.

So an index including brand and size (in either order) is all that is needed.

test data:

create table items_sold(brand integer, size integer, other text);

insert into items_sold(brand, size, other)
select g%5, random() * 5 + 1, lpad('',1000)
from generate_series(1,100000) g;

vacuum analyze; -- you wouldn't normally run this manually

without covering index:

explain (analyze,buffers)
select brand, size, count(*)
from items_sold
group by grouping sets (brand,size,());

/*
|QUERY PLAN                                                                                |
|:----------------------------------------------------------------------------------------------------------------------------|
|GroupAggregate  (cost=23590.82..33395.88 rows=24 width=16) (actual time=117.246..206.585 rows=12 loops=1)                    |
|  Group Key: brand                                                                                |
|  Group Key: ()                                                                                |
|  Sort Key: size                                                                                |
|    Group Key: size                                                                                |
|  Buffers: shared hit=885 read=13401, temp read=441 written=441                                                              |
|  ->  Sort  (cost=23590.82..23840.82 rows=100000 width=8) (actual time=108.069..129.823 rows=100000 loops=1)                 |
|        Sort Key: brand                                                                                |
|        Sort Method: external merge  Disk: 1752kB                                                                            |
|        Buffers: shared hit=885 read=13401, temp read=220 written=220                                                        |
|        ->  Seq Scan on items_sold  (cost=0.00..15286.00 rows=100000 width=8) (actual time=0.010..49.793 rows=100000 loops=1)|
|              Buffers: shared hit=885 read=13401                                                                             |
|Planning time: 0.034 ms                                                                                |
|Execution time: 207.241 ms                                                                                |
*/

with covering index:

create index on items_sold(brand,size);

explain (analyze,buffers)
select brand, size, count(*)
from items_sold
group by grouping sets (brand,size,());

/*
|QUERY PLAN                                                                                |
|:------------------------------------------------------------------------------------------------------------------------------------------------------------|
|GroupAggregate  (cost=0.29..12159.35 rows=24 width=16) (actual time=10.478..95.557 rows=12 loops=1)                                                          |
|  Group Key: brand                                                                                |
|  Group Key: ()                                                                                |
|  Sort Key: size                                                                                |
|    Group Key: size                                                                                |
|  Buffers: shared hit=1 read=275, temp read=221 written=221                                                                                |
|  ->  Index Only Scan using items_sold_brand_size_idx on items_sold  (cost=0.29..2604.29 rows=100000 width=8) (actual time=0.023..21.911 rows=100000 loops=1)|
|        Heap Fetches: 0                                                                                |
|        Buffers: shared hit=1 read=275                                                                                |
|Planning time: 0.108 ms                                                                                |
|Execution time: 95.953 ms                                                                                |
*/

dbfiddle here

Note the "Buffers" data — the covering index does much less IO here.


Optimization of a big MySQL weather archive table

Too slow and there is a better index?  No brainer.  Add the index.  But...  If you already have INDEX(x) drop it as you add any index starting with x.  (I'm thinking of longitude and RMathis' comment.

You are likely to have trouble with things like latitude=42.75.  latitude is FLOAT; 42.75 is (I think) DOUBLE.  In this case, 42.75 can be represented exactly in both FLOAT and DOUBLE.  But 42.76 cannot, hence it may not find a match due to roundoff.

A pair of floats consumes 8 bytes.  See this for other representation choices.  For weather stations that are no closer than a mile, DECIMAL(4,2)/(5,2) would save space (5 bytes instead of 8) and avoid the potential roundoff problem.

You mentioned "one of the slow queries".  To best serve you, let's see all the frequent queries, even those that are not slow now.  Then we can suggest a set of indexes to best handle all of them.

(When discussing performance issues, please provide SHOW CREATE TABLE; there are several details missing from what you provided.)

How much RAM do you have?  What is the value of key_buffer_size?  (Recommend 20% of available RAM.)  If you have a tiny RAM, then, I will impart some other advice.


alter database backup controlfile vs cp Linux file

If you just move/rename control files (cp, mv ...) and modify accordingly control_files parameter - no need to do any recovery.

You may use cp only when instance is down or in nomount state.
Otherwise, you can get inconsistent control file.
If we do:

  ALTER DATABASE BACKUP CONTROLFILE...

then we make "old", "not current" controlfile - backup controlfile.
With cp we make "current" copy of controlfile.

If we have current controlfile - no recovery is needed.
When controlfile is a backup controlfile then it requires recover and open resetlogs.

Here's a link to documentation -- old, but explains the concepts well.


unique constraint violated (ORA-00001) in concurrent insert

@A.B.Cade you're right! I found another process that modified id_table concurrently for table_identifier = 'TABLE1'. Thanks to all for your help.


Audit login trigger for permission changes sql server

Check  this
out.

CREATE TRIGGER DDL_AUDIT_Logins ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS AS

SET NOCOUNT ON;

DECLARE @EventsTable TABLE (
    EType NVARCHAR(max)
    ,EObject VARCHAR(100)
    ,EDate DATETIME
    ,EUser VARCHAR(100)
    ,ECommand NVARCHAR(max)
    );
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);

SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
 'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',
 'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
 'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseName)[1]',
 'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)');

INSERT INTO @EventsTable
SELECT @EType
    ,@EObject
    ,GETDATE()
    ,SUSER_SNAME()
    ,@ETSQL;

SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '
 + Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject +
CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' +
@ETSQL

SELECT @Subject = 'SQL Server Login changed on ' + @@servername;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'DDL_Alert@companydomain.com'
    ,@body = @EMessage
    ,@subject = @Subject
    ,@body_format = 'HTML';

SET NOCOUNT OFF;
GO

Result:


Postgres WAL logs-- point in time recovery with (time) gaps in the logs?

PostgreSQL does not care when the WAL logs are generated, it just reads them sequentially on restore/recovery.

If the WAL logs were properly sent after the restart of the database, they should be restored as well.

However, if there is even a single WAL file missing for some reason (in this example, I'd guess that'd be either the last segment before the shutdown or one or more of the first ones from after the startup), which is typically either an incorrectly configured archive_command (missing error checks for example) or simply a broken archive server, the restore process cannot go past that point.


Upgrade MongoDB meta-data after binary upgrade

If you are not using a sharded cluster the meta-data upgrade procedure from 2.2 to 2.4 is not required. It involves the config database which does not exist in a non-sharded environment.


Run sp_BlitzCache an agent job to assist performance tuning efforts / baselining query performance?

How specifically has this helped you in your environment?

My laptop instances are running great these days!

But seriously, if you lack a monitoring tool, this is a good way to analyze your plan cache for all sorts of problems, and opportunities for tuning. We warn you about a lot of stuff that may not be obvious just looking at an execution plan.

  Which sp_BlitzCache sort_order do you find most helpful to log? I'm
  considering separate output tables by cpu, reads, duration, and
  executions per minute. But wondering if I should be using "avg X" for
  these sorts.

You may find some overlap in the different sort orders. For instance, CPU and Reads often have a lot of overlap.

As for average vs total, that mostly depends on plan cache stability. If it doesn't change much, averages can be more interesting than totals. Sometimes small, not necessarily tune-able queries rack up a lot of total CPU time because they're run fairly frequently, as well as maintenance tasks. This is something you'll have to figure out for each environment.

  Should I stick with the default of @top = 10? Or go for 20, 30, etc?

This is also subjective, but lends itself a bit to another question of yours. The more plans you choose to bring in to analyze, the more resource intensive it is. Analyzing XML in SQL Server is... challenging.

The default is 10 to keep the footprint small, but also give you some meaningful results back. Of course, if you're not finding anything of interest in the top 10, feel free to experiment with different values to see how long it takes to run, and even if it helps you find anything interesting.

  How frequently would you run such a job? Weekly? Since the plan cache
  doesn't get wiped out until a host / service restart, an explicit
  command to do so (dbcc freeproccache), or in some other perhaps
  unexpected way (to some) such as changing MAXDOP (our Team learned the
  latter the hard way).

This depends a bit on plan cache stability. If the same stored procedures run over and over again, daily or weekly is fine. If your server gets a lot of ad hoc user queries pushed through, you may want to run it hourly, or at some other interval your server can handle.

You can also use the @IgnoreQueryHashes parameter to keep some of the noise out.

  This is more in line with with another StackExchange question just I
  posted about sp_AskBrent and sp_WhoIsActive, but how
  resource-intensive is it to run these SPs on a periodic basis?

We aim to be as resource un-intensive as possible, but the number of plans you choose to analyze, and the size of the plans can cause spikes. Like I said, analyzing XML in SQL Server ain't fun. It's single threaded, and can use a bit of CPU. You can use the @DatabaseName parameter if you're only interested in what one database is doing, which can help focus tuning efforts.

Thanks!


How to Fetch A Record If Shipping And billing Address are Different

SELECT  t.TripId_PK, ls.name StartLocationName, le.name EndLocationName
FROM    trips t
JOIN    locations ls
ON      ls.LocationId_PK = t.StartLocationId_FK
JOIN    locations le
ON      le.LocationId_PK = t.EndLocationId_FK


How to get archive re-do log file name containing the given SCN in Oracle?

If you want to stop recovery at a specific SCN, just run recovery until that specific SCN:

RMAN> recover database until scn 3087500;

If you want the name of the files:

RMAN> list archivelog scn between 3087500 and 3087500;

List of Archived Log Copies for database with db_unique_name GUN_O7CA
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
2       1    22      A 12-FEB-18
        Name: /fra/GUN_O7CA/archivelog/2018_02_12/o1_mf_1_22_f83lq5l2_.arc

6       2    31      A 12-FEB-18
        Name: /fra/GUN_O7CA/archivelog/2018_02_12/o1_mf_2_31_f83lqm9v_.arc


Amazon Postgres RDS: Update pg_attribute to update my custom attribute

If you want to change the length of many varchar columns (which isn't actually a custom attribute), you are better off using ALTER TABLE commands. You don't risk having unwanted side-effects, just in case PostgreSQL needs to do something more than changing the pg_attributes table. It might not do it today, but might do it in any future version.

You can have the database help you generate the ALTER TABLE DDL statements (or even, generate and execute them within a UDF).

To generate the DDL statements, you can use the information_schema.columns view; which is simpler to understand than using pg_attributes together with more catalog tables all joined. [The view actually joins several tables in non-obvious ways, but that's already done for you.]

You first check the columns involved:

-- Check the columns you are interested in
SELECT
    table_schema, table_name, column_name,
    data_type, character_maximum_length
FROM
    information_schema.columns
WHERE
    column_name like '%varchar_column%'
    AND table_schema = 'my_schema' ;

You can get something like:

| table_schema | table_name |            column_name |         data_type | character_maximum_length |
|--------------|------------|------------------------|-------------------|--------------------------|
|    my_schema |         t1 |     one_varchar_column | character varying |                        5 |
|    my_schema |         t1 | another_varchar_column | character varying |                        6 |
|    my_schema |         t2 | a_third_varchar_column | character varying |                        3 |

Once you're sure the list of columns you get is the one you're actually interested, make the database compute the necessary DDL to perform what you want:

--
-- This will generate a collection of `ALTER TABLE` statements
--
-- This is what you're looking for
--
SELECT
    'ALTER TABLE "' || table_schema || '"."' || table_name || '" ' ||
    '    ALTER COLUMN "' || column_name || '" TYPE VARCHAR(9);' AS ddl_to_execute
FROM
    information_schema.columns
WHERE
    column_name like '%varchar_column%'
    -- AND table_schema = 'my_schema'
    -- AND whatever else you need
ORDER BY
    table_schema, table_name;

This will result in the following list of ALTER TABLE statements:

ALTER TABLE "my_schema"."t1" ALTER COLUMN "one_varchar_column" TYPE VARCHAR(9);
ALTER TABLE "my_schema"."t1" ALTER COLUMN "another_varchar_column" TYPE VARCHAR(9);
ALTER TABLE "my_schema"."t2" ALTER COLUMN "a_third_varchar_column" TYPE VARCHAR(9);

Copy them and execute them.

You can check the example at http://rextester.com/GYHR58531


How to stabilize performance on frequently updated table in PostgreSQL

Some things depend on your PostgreSQL version.  On some older versions pay attention to fsm settings to make sure it can track free space in your tables for re-use.  You might also look at scheduling a periodic cluster if you can afford the downtime.  If not, pay attention to the indexes.


How to delete a datafile if the father-tablespace does not exists?

The TS associated with your file is 'BGBA_GAL126344', but you are issuing your DROP TABLESPACE on 'GAL126344'.

'BGBA_GAL126344' != 'GAL126344'

Why were you trying to DROP USER?  Neither files nor tablespaces are owned by users.

Why do you repeatedly issue the same failed DROP TABLESPACE command?

FWIW, in 20+ years of working as an Oracle DBA, I have never heard the term 'father-tablespace'.  I think the more correct term would be a file's 'associated tablespace'.


That would REQUIRE ISSUER in your user definition.


Oracle: Helping hand writing this query. Do I need a sub query here?

Using PIVOT:

WITH
   combined AS (
      SELECT
         o.customer_no,
         EXTRACT(month FROM o.entry_date) AS order_month,
         EXTRACT(year FROM o.entry_date) AS order_year,
         t.order_final_tot
      FROM orders o
      JOIN order_totals t ON (o.order_no = t.order_no)
   )
SELECT *
FROM combined
PIVOT (
   SUM(order_final_tot) AS ord_tot,
   COUNT(*) AS ord_cnt
   FOR order_year IN (2013, 2014)
);


Jobs are DB agnostic: the steps that comprise a job may have a database context

sysjobsteps has a database_name column

  Name of the database in which command is executed if subsystem is TSQL.

You can join this back to sysjobs and the other related tables to get what you need.


How can I tell what recovery model my SQL database has?

Some variety in how to do it...

SELECT D.recovery_model_desc FROM sys.databases D WHERE name = 'MyDB'

or

SELECT name, D.recovery_model_desc FROM sys.databases D

or

SELECT DATABASEPROPERTYEX('MyDB', 'Recovery')


How can I use date_trunc in postgresql to round weekend days to the following weekday?

If I understand right you want to add one day and if the result is Saturday or Sunday add 2 or 1 more days, so the result is the next working day. Then extracting first the day of the week (dow) and using a CASE expression,  will do:

select date_trunc('day',
                  trade_date + ( case extract(dow from trade_date)
                                   when 5 THEN 3     -- when Friday, add 3
                                   when 6 THEN 2     -- when Saturday, add 2
                                   else 1            -- any other day, add 1
                                 end
                               ) * interval '1 day' )
from trade_table ;


how to identify the usage of an specific sql login

As all the sessions for that user are system processes per your comment, this is the system itself, i.e. the "sa" account and/or the SQL Server Service account using Trusted Authentication.


For states (col) receiving exactly X number of items (row), sort the items by weight (col)

I finally figured it out!

SELECT State, Weight FROM testing
WHERE State = ANY (
    SELECT State FROM testing
    GROUP BY State
    HAVING COUNT(*) = 7
)
ORDER BY Weight DESC;


Foreign keys can't be added because they exist but they don't exist!

You cannot add the constraints to tblUserRoles because the data within this table already violates the constraints that you are trying to add.

a) Double check that all UserID values in tblUserRoles reference UserID values in tblUsers

b) Do the same as the above for tblRoles and RoleID.

c) As I imagine both of the above will return records that are not in the referenced table, you'll need to clean up your data before adding the Foreign Keys

d) Name you foreign keys something useful! If I came to work on your database, how am I supposed to know what Relationship26 means?!


This is probably a typo issue. The provider is SQLNCLI, not SQLCNLI


Set search path for Postgres in a Rails app

You can define it in your database.yml using the schema_search_path key with a string value

production:
  adapter: postgresql
  ...
  schema_search_path: 'schema1,public'

You can find out a bit more here: https://til.hashrocket.com/posts/5aa2892b43-set-schema-search-path


Use of HAVING without GROUP BY in SQL queries

No.

They don't have to coexist, as proved by the fact that the following query in Oracle works:

select * from dual having 1 = 1;

Similarly, in PostgreSQL the following query works:

select 1 having 1 = 1;

So having doesn't require group by.

Having is applied after the aggregation phase and must be used if you want to filter aggregate results. So the reverse isn't true, and the following won't work:

select a, count(*) as c
from mytable
group by a
where c > 1;

You need to replace where with having in this case, as follows:

select a, count(*) as c
from mytable
group by a
having c > 1;

NB The following query form will also work:

select *
from (
  select a, count(*) as c
  from mytable
  group by a
)
where c > 1;

You can see that using having is simply a shorthand version of this last query.

In summary, having is applied after the group by phase whereas where is applied before the group by phase.


What are the pros and cons of adding secondary columns to a table's primary key?

Short answer:  The filename in PRIMARY KEY (photoId, filename) buys you nothing (if I correctly understand your use case).

Long answer (and a lot more tips):

AUTO_INCREMENT needs the id to be the first column in some index; nothing more.
The PRIMARY KEY is, but definition, UNIQUE.  Adding extra columns changes that uniqueness constraint.  This is likely to hurt in that you can accidentally insert rows that would violate the previous (shorter) PK.
As far as the disk space for the PK and data (which share the same BTree), there is virtually no change.
On the other hand, the fatter the PK is, the fatter secondary indexes will be.  This is because the PK is implicitly added to each secondary key (as you stated).  Only sometimes does this fattening matter.
For performance, you need to have the appropriate index.  That is, design the indexes, then see if you can do some minor cleanups.  Example:  INDEX(a), INDEX(a,b) --> only INDEX(a,b).
Indexes should tackle WHERE, then GROUP BY, then ORDER BY.  More discussion.  (Without specifics, I can't help in more detail.)
What is retrieved (SELECT filename, ...) is rarely a consideration in designing indexes.  Exception: "Covering" index.
If the Optimizer has decided to use the PK, then adding extra columns to the PK will not help at all.  Adding columns to a secondary index may matter.
It's an old wive's tale that you should avoid varchars for ints in the PK.  It is sometimes beneficial to some degree.  I would rather discuss specific cases.  A BTree works the same way whether it is ordered by an INT or VARCHAR(99).
We need to see the main queries against the table in order to be more specific.

In one application (and using your photo analogy):  The giant Photos table had an auto_inc photoID, but most queries included WHERE userID = 123.  So:

PRIMARY KEY(photoID),
INDEX(..., userID, ...),
INDEX(..., userID, ...),
INDEX(..., userID, ...),
...

Since the data is ordered via the PK, looking up by userID led to jumping around on the disk.  This turned out to be almost twice as fast:

PRIMARY KEY(userID, photoID) -- cluster on userid; photoid for uniqueness
INDEX(photoID),   -- kept auto_inc happy
INDEX(..., userID, ...),
INDEX(..., userID, ...),
...   -- fewer were needed

The uniqueness constraint on photoID was lost, but the INSERTs used auto_inc to effectively never violate the constraint.

This table was much too big to be cached in RAM; the speedup mostly came from clustering on userID.

To loop back to your Question...  That suggested change hurts this query:

SELECT filename FROM Photos WHERE photoID = 235;  (with no other columns)

The simple answer is to change INDEX(photoID) into a 'covering' index:  INDEX(photoID, filename).  Note that this does not contradict anything I said above.


Dependency preserving and lossless join decompositions

As far as I understand the theory, if you can join your decompositions and arrive back at your relation with no loss, that's it.

It looks to me like AB, A, B, F, D are the keys to do so.


In my experience, synonyms are generally used for the sake of convenience.  As an example, in my environment, we employ synonyms so that the developers don't have to worry about properly writing up cross-schema joins.  By utilizing synonyms, our developers don't need to qualify any object with a schema in their SQL statements, because everything they need is either in their schema, or looks to be (because of the synonym).  While some would argue this is poor practice, we're just tired of educating round after round of developers about proper database scripting and security standards.

As Travis alluded to below, creating a synonym for an object in a different schema doesn't mean you don't have to worry about security.  The proper GRANTs to the base objects are still required and using good judgement on the level of permissions granted is always encouraged.

For me, synonyms reduce my total cost of ownership on administrative tasks.  I'm sure there are other uses for Synonyms I've not touched on, but hopefully that helps by giving you a practical situation where synonyms are in fact very useful.


I'm not sure if the issue is difficulty connecting a user to the login on database(db2) or if you are having difficulty unassociating the user with the schema dbo on database(db1).

It sound to me like the larger issue is connecting the user to db2.  If the user already exists on db2 you can associate them with the login using the command

EXEC sp_change_users_login ‘UPDATE_ONE’ , ‘sqlusername’, ‘sqlloginname’;

If the user doesnt exist in db2 you can create them using

CREATE USER 'username' FOR LOGIN 'loginname'
WITH DEFAULT_SCHEMA = 'default schema name';


Schema Design - would you split this table?

count(*) is always slow, so don't do it unless you have to.

The most important factor is usually the physical size of the table, so check that for an explanation for what you observe.

Splitting the table along a one-to-one relationship might speed up counting, but will slow down other queries, which seems like a bad deal.

Partitioning won't help, but done correctly, it can make it easy get rid of old data. Using a recent PostgreSQL version and parallel query with a lot of processes is more promising for fast counting.


Read-only replica of RDS Postrgres instance with modified schema

You can't do this in RDS at the moment. You can spin a replica as a mirrored database but it does not allow the advanced features for logical decoding or partial replication. Unfortunately this is the caveat on the AWS services. The best option will be to move out RDS to EC2.


Reorganize and Rebuild Indexes, Pros and Cons?

From Rebuild or Reorganize: SQL Server Index Maintenance by Kendra Little:
Short version:

Rebuild: An index ‘rebuild’ creates a fresh, sparkling new structure for the index. If the index is disabled, rebuilding brings it back to life. You can apply a new fillfactor when you rebuild an index. If you cancel a rebuild operation midway, it must roll back (and if it’s being done offline, that can take a while).
Reorganize: This option is more lightweight. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).

Should I use REBUILD or REORGANIZE?

If you have a regularly scheduled downtime every weekend, you’re probably fine with straight up index rebuilds, even if you have Standard Edition. Single threaded offline index maintenance may not be the hottest thing in the world, but hey, if you’ve got time for it then embrace the simplicity.
If you have Enterprise Edition, embrace parallel index rebuilds– and use the ONLINE option for indexes that allow it if people need to access the database during your maintenance window.
If you have database mirroring or AlwaysOn Availability Groups, tread with caution– particularly with rebuilds. It’s easy to generate a ton of IO with index maintenance, and it could mean putting your secondaries or mirror so far behind that they can’t catch up.

Read Kendra's article for other important information you should take into account.


Permissions granted are insufficient for this operation

Report builder works on different permissions than the raw SQL Server permissions which enable you to log into the SQLOS and execute code against the DB engine.  Report builder needs you to identify users in other areas.

Microsoft has some documentation on the matter here.  If you are running native mode (non sharepoint) you can check:

https://msdn.microsoft.com/en-us/library/ms365173.aspx

You will need to add users which can be found here on MS docs:

https://msdn.microsoft.com/en-us/library/ms156034.aspx

To add a user or group to a system role:

Start Report Manager (SSRS Native Mode).

Click Site Settings.

Click Security.

Click New Role Assignment.

In Group or user name, enter a Windows domain user or group account in this
format: \. If you are using forms authentication or custom
security, specify the user or group account in the format that is correct for your deployment.

Select a system role, and then click OK.

Roles are cumulative, so if you select both System Administrator and System
User, a user or group will be able to perform the tasks in both roles.
Repeat to create assignments for additional users or groups.


Selecting different values from different columns

It's not very clear what you are after but here's a query under my understanding that you want to find the last timestamp where queues = 'ACS' and the last timestamp where queuesname = 'GHK' for every groupnumb:

with grp as
  ( select groupnumb, max(timestamp) as timestamp
    from tableX
    where queues = 'ACS'
    group by groupnumb
  union all
    select groupnumb, max(timestamp)
    from tableX
    where  queuesname = 'GHK'
    group by groupnumb
  )
select min(timestamp) as min_timestamp,
       max(timestamp) as max_timestamp,
       groupnumb
from grp
group by groupnumb ;


I don't think there is a way to make SQL Server default to truncating incoming values instead of rounding.

You can however control the values in your Java application, before they are sent to the databases.

According to Java 8 docs, java.sql.Timestamp has getNanos() and setNanos() methods you can use (pseudocode, my Java is rusty):

ts = ...                  // the Timestamp object

micros = ts.getNanos() / 1000 ;   // extract and truncate to microseconds
ts.setNanos( micros * 1000 ) ;    // set the truncated value back

insert(..., ts, ...) ;    // INSERT


Assuming that the combination (a,c) is unique in both table, then this is a case of a simple join plus a create table as

create table i3
as
select i1.a, i1.c
from i1
  join i2 on i2.a = i1.a and i2.c = i1.c;

If (a,c) is not unique you will have duplicates in the resulting table. This can be avoided by using the intersect operator:

create table i3
as
select a, c
from i1
intersect
select a,c
from i2;

Note that create table ... as select is ANSI SQL but some DBMS (e.g. SQL Server) don't follow the standard and use a different syntax to create a table based on a select statement.