User assignment to various roles of changing events

First, I would expect there to be a team table:

 TeamId | TeamName
--------+----------
    1   |  Team A
    2   |  Team B

and a separate user table, that identifies the team each user is in:

 UserId |   UserName   | TeamId
--------+--------------+--------
  1001  |   JaneSmith  |   1
  1002  |   JohnJones  |   1
  1003  |   KatieGrin  |   1
  1004  |  AbeSimpson  |   2
  1005  |  BenFranklin |   2
  1006  |  BarbaraEden |   2

Then, assignment would have UserId as a foreign key referencing the user table, just like TeamId in user is a foreign key referencing team.

I would not see value in having separate tables for each team. That would actually make things harder, rather than easier. The assignment table would either need one extra column, to indicate which table the assigned user came from (complicating queries, and preventing most common means to enforce referential integrity), or it will need a unique column to tie back to each distinct team table, in which case each foreign key column would need to allow for NULLs, which can hurt your referential integrity again.

Usually, the main benefit of using an actual value instead of NULL is to avoid the need for functions in your query to adjust the NULL value to as appropriate value. However, using 999 would probably still require some sort of computation, to adjust it to the actual DayEnd.

I do wonder if you wouldn't be better off using the actual DayEnd. I assume you're doing this to avoid having to recalculate the DayEnd values for an event that's duration changes; however, consider carefully if that's really the way you want to go. With NULL (or 999) as your DayEnd, you assume the users can safely be assigned to additional days. I don't know your business rules, but it seems entirely possible that someone may be unable to cover the additional days added to the event; assuming they can cover the additional days simply doesn't seem particularly safe.

Full details on implementation would, of course, depend on your DBMS, and your full business rules.


Deleting from multiple tables with foreign constraints in mysql

You should check out your data, according to your table definition it must work.

drop table if exists xcommitments;
drop table if exists xdonations;
drop table if exists xusers;

create table xusers
(
    user_id int not null,
    name varchar(100),
    primary key (user_id)
);

create table xdonations
(
    donation_id int not null,
    user_id int not null,
    primary key (donation_id),
    constraint fk_users foreign key (user_id)
        references xusers (user_id) on delete cascade
);

create table xcommitments
(
    commitment_id int not null,
    donation_id int not null,
    primary key (commitment_id),
    constraint fk_donations foreign key (donation_id)
        references xdonations (donation_id) on delete cascade
);

insert into xusers values (1, 'John'), (2, 'Anna');
insert into xdonations values (1, 1), (2, 1), (3, 2);
insert into xcommitments values (1, 2), (2, 3);

select      *
from        xusers u
left join   xdonations d
on          u.user_id = d.user_id
left join   xcommitments c
on          d.donation_id = c.donation_id;

| user_id | name | donation_id | user_id | commitment_id | donation_id |
|---------|------|-------------|---------|---------------|-------------|
| 1       | John | 1           | 1       | NULL          | NULL        |
| 1       | John | 2           | 1       | 1             | 2           |
| 2       | Anna | 3           | 2       | 2             | 3           |

delete from xusers
where  user_id = 1;

select      *
from        xusers u
left join   xdonations d
on          u.user_id = d.user_id
left join   xcommitments c
on          d.donation_id = c.donation_id;

| user_id | name | donation_id | user_id | commitment_id | donation_id |
|---------|------|-------------|---------|---------------|-------------|
| 2       | Anna | 3           | 2       | 2             | 3           |

drop table if exists xcommitments;
drop table if exists xdonations;
drop table if exists xusers;

rextester here


Performance Implications of encrypted data in a Clustered Columnstore Index

It's best to avoid asking multiple, unrelated questions in a single question when possible. Your first question is very broad. In general, creating an index is always a trade-off and some queries will benefit from that index. The same is true for NCIs on columnstore tables. I'll focus on the second question instead.

As far as I can tell, encryption by SYMMETRIC KEY can be a very poor match for columnstore. Inserting encrypted data into a CCI means that you're inserting long, relatively unique strings into varbinary columns. CCIs don't do well with those data types and data distributions. You're likely to hit dictionary pressure which will limit the size of your rowgroups and you'll miss out on most of the features of columnstore, except for column elimination.

I found the code at How to use SQL Server Encryption with Symmetric Keys helpful in developing a simple example. I don't know what your data looks like, so I'll just insert unique integers into a table and insert the same unique integers with encryption into a different table. Definition of the key:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'you will never guess this';

CREATE CERTIFICATE MyFirstCertificate
WITH SUBJECT = 'no u';

CREATE SYMMETRIC KEY key_to_answering_217242 WITH
IDENTITY_VALUE = 'yeah nah',
ALGORITHM = AES_256,
KEY_SOURCE = 'closet full of jandals'
ENCRYPTION BY CERTIFICATE MyFirstCertificate;

T-SQL to create two tables and insert data into both of them:

DROP TABLE IF EXISTS dbo.CCI_NOT_ENCRYPTED;

CREATE TABLE dbo.CCI_NOT_ENCRYPTED (
ID BIGINT,
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO dbo.CCI_NOT_ENCRYPTED WITH (TABLOCK)
SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

GO

USE master;
GO

OPEN SYMMETRIC KEY key_to_answering_217242
DECRYPTION BY CERTIFICATE MyFirstCertificate

DROP TABLE IF EXISTS [TEST].dbo.CCI_ENCRYPTED;

CREATE TABLE [TEST].dbo.CCI_ENCRYPTED (
ID VARBINARY(256),
INDEX CCI CLUSTERED COLUMNSTORE
);

INSERT INTO [TEST].dbo.CCI_ENCRYPTED WITH (TABLOCK)
SELECT EncryptByKey(Key_GUID('key_to_answering_217242'), CAST(RN AS VARCHAR(7)))
FROM (
    SELECT TOP (1048576) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) q
OPTION (MAXDOP 1);

CLOSE SYMMETRIC KEY key_to_answering_217242;

GO

There's a clear difference in total table size. The table with encrypted data is over 20X bigger:

Looking at sys.dm_db_column_store_row_group_physical_stats, we can see that the table with encrypted data had its rowgroup size limited by dictionary pressure:

Query performance will of course be greatly impacted as well. The following query takes approximately 0 ms of CPU time:

SELECT COUNT(*)
FROM dbo.CCI_NOT_ENCRYPTED
WHERE ID = 1;

If I run that same query on the encrypted table:

USE master;
GO

OPEN SYMMETRIC KEY key_to_answering_217242
DECRYPTION BY CERTIFICATE MyFirstCertificate

SELECT COUNT(*)
FROM [TEST].dbo.CCI_ENCRYPTED
WHERE CAST(CAST(DecryptByKey(ID) AS VARCHAR(7)) AS BIGINT) = 1;

CLOSE SYMMETRIC KEY key_to_answering_217242;

GO

It takes over 600 ms of CPU time. With all of that said, there appears to be a large overhead in general in working with encrypted data. I cannot say if a columnstore will offer any benefit for your exact data and scenario, only that you definitely won't see the usual benefits of columnstore.


Why do database host services increment id by > 1 unit?

The only reason I can think of is "reserving" some data slots for the future usage. That would work if a table is index-organized ( or clustered in Microsoft terminology). For instance,  if we have rows with id 1,6,11,....1001, and we manually insert row with id =2 it will be physically stored (assuming data page is big enough to store 2 rows) in the same data page as row with id =1.
Personally, I doubt efficiency of such approach, but it seems to make some sense.

Security explanation sounds very weird to me, increasing by 5 (or 3, or 103) is by no means more "secure" than by 1.

There might be some very application specific reasons, but it's better to ask whoever designed particular application and db schema for it.


Can not ping Always On listener from outside primary node

We had the same issues as this. Recreated the DNS entries for the two listeners and making sure "Allow any authenticated user to update DNS records with the same owner name" is checked. Believe the failover event caused issues with the listener not updating it's IP address.


could not read symbolic link error while executing pg_start_backup function in Postgresql DB

Somebody who was not the PostgreSQL operating system user (normally named postgres) messed with your data directory.
This directory should only contain symbolic links whose names are numbers (the object IDs of tablespaces), and those symbolic links (and the directories that they point to) must belong to postgres.
Put the person who created these files on the pillory (but that won't solve your problem).
One should never manually mess with the PostgreSQL data directory, so the safe way to recover would be to create a new, clean PostgreSQL cluster with initdb, use pg_dumpall to export the data from the one that was messed with and import the data into the new cluster. Then delete the cluster that was messed up.
If you know what you are doing, you might also be able to recover by deleting the offending files – but who knows what else was done to this data directory.


How to restore a database then upgrade it in a single SQL script?

You could use dynamic SQL

    Set @SQL =
   'USE MyDatabase -- The database exists by this point

    -- Apply patches ...'

    EXEC SP_ExecuteSql @SQL

You may need to do a wait to allow the database to restore before executing SQL on it.

WAITFOR DELAY '00:00:02';


ON DUPLICATE KEY only works when constraint violation. In this cases no constraint will be violated because the primary key is auto incremented column, so you have to add one unique key constraint in your table.


Why does query error with empty result set in SQL Server 2012?

An initial look at the execution plans shows that the expression 1/0 is defined in the Compute Scalar operators:

Now, even though execution plans do start executing at the far left, iteratively calling Open and GetRow methods on child iterators to return results, SQL Server 2005 and later contains an optimization whereby expressions are often only defined by a Compute Scalar, with evaluation deferred until a subsequent operation requires the result:

In this case, the expression result is only needed when assembling the row for return to the client (which you can think of occurring at the green SELECT icon). By that logic, deferred evaluation would mean the expression is never evaluated because neither plan generates a return row. To labour the point a little, neither the Clustered Index Seek nor the Table Scan return a row, so there is no row to assemble for return to the client.
However, there is a separate optimization whereby some expressions can be identified as runtime constants and so evaluated once before query execution begins.
In this case*, an indication this has occurred can be found in the showplan XML (Clustered Index Seek plan on the left, Table Scan plan on the right):

I wrote more about the underlying mechanisms and how they can affect performance in this blog post. Using information provided there, we can modify the first query so both expressions are evaluated and cached before execution starts:
select 1/0 * CONVERT(integer, @@DBTS)
from #temp
where id = 1

select 1/0
from #temp2
where id = 1

Now, the first plan also contains a constant expression reference, and both queries produce the error message. The XML for the first query contains:

More information: Compute Scalars, Expressions and Performance

An example where an error is reported due to runtime constant caching, but there is no indication in the execution plan (graphical or XML):
SELECT TOP (1) id
FROM #temp2
WHERE id = 1
ORDER BY 1/0;


Dropping columns from a table, truncating it and then re-inserting data as before into the table doesn't reduce space occupied

The first thing I'd ask is are you sure the columns you dropped are decimals?

The reason why I ask is that with variable columns (which Decimal is not), then SQL Server won't automatically reduce space used when these are dropped. To resolve this you can run DBCC CLEANTABLE. You might want to try this anyway.

However, if the columns that you dropped are only decimals, then this probably won't have an effect.

Another possibility is that the decimal columns were just not taking up that much space - depending on the precision, a decimal column can be quite small. You say that there is no perceptible difference, is it just a case that the decimal columns were taking up relatively little space compared to other columns in the table (and rest of the database)?

Check the space used of the actual table before and after the change. I used this query to check table spaced used

SELECT
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages,
 SUM(a.used_pages) AS UsedPages,
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM
 sys.tables t
INNER JOIN
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND
 i.index_id <= 1
GROUP BY
 t.NAME, i.object_id, i.index_id, i.name
ORDER BY
 OBJECT_NAME(i.object_id)

Reference: I got this originally from this SO post.

To note/warning:
I'm assuming you're doing this in some sort of development or test environment, doing this sort of thing in production without testing is really not advisable. This earlier post is similar to yours (but is specifically about variable column lengths).

SpaceUsed
Your query to find space used looks fine, but there is an inbuilt stored procedure sp_spaceused that you might find easier (but probably doesn't make much of a difference.


Yes, it will -- of course if it persistently refuses to you might have to kill the processes, and face instance recovery on restart.


Describe Interim Table and Basic Concept behind it?

Interim table is a table that is generated by joining two tables and not the final result table. In other words, when two tables are joined they create an interim table as resultset but the resultset is not final yet.

Check this link for examples


Does the principle of having an "ever increasing index" also apply for NVARCHAR and VARCHAR?

If I understand your question:
It is a good practice to have an ever increased index - that prevents page splits - read about this issue if you are not familiar with it. It is correct also for char values. To make it simple - sql server will fill page after page without having to insert rows in the middle. Think about it as filling phone book - it is much easier to fill it in abc order than randomly insert names.
So the order "abc", then "def" and then "ghi" is ever increasing.
Don't know why do you think that using char columns as indexes is a bad practice - if you need them there you should create them. Yes, creating logic that will enforce ever increasing char insertion is difficult and sometimes impossible, but it still shouldn't prevent you from creating indexes on those columns.
With all that said if you need to enforce ever increasing clustered index you should probably use numbers(identity or sequence)


Dynamic SELECT and place result in variable columns

Adding an example to Michael's answer.  The problem with the PIVOT is two fold. 1st, it wants to aggregate.  You can get around this by defining your dataset to be distinct and using MAX or MIN functions.  But your example above makes that impossible, due to a user being able to have multiple answer sets for a given question.  You would end up with only one row with the two datasets aggregated for the dupe user.  To get around this I added a "question_set" field, adding a date (distinct enough for this example) to each question/answer set.

2nd, you still have to define the pivoted fields.  If you have 50 questions, that's 50 definitions.  And if you add a question, you will have to add the definition to the query.  Using a loop, I created a question list dynamically and inserted it into the pivot query.  Hope this helps.

--> Create the test data
if OBJECT_ID('QuestionAnswers','u') is not null
    drop table QuestionAnswers

create Table  QuestionAnswers
(
id int identity,
question_set date, --> need something to delineate repeats
user_id varchar(255),
question_id int,
answer varchar(255)
)

if OBJECT_ID('Questions','u') is not null
    drop table Questions

create Table  Questions
(
id int identity,
question_id int,
question_name varchar(255)
)
go

with CTEquestion
as
(
    select 1 QID
    union all
    Select QID+1
    from CTEquestion
    where QID < 11
)
insert questions
select QID, 'Question'+cast(QID as varchar(50))
from CTEquestion

insert QuestionAnswers
values ('2015-04-23', 'a1', 1, 'Canswer1')
    ,  ('2015-04-23', 'a1', 2, 'Ianswer2')
    ,  ('2015-04-23', 'a1', 3, 'Canswer3')
    ,  ('2015-04-23', 'a1', 4, 'Canswer4')
    ,  ('2015-04-23', 'a1', 5, 'Ianswer5')
    ,  ('2015-04-23', 'a1', 6, 'Ianswer6')
    ,  ('2015-04-23', 'a1', 7, 'Canswer7')
    ,  ('2015-04-23', 'a1', 8, 'Canswer8')
    ,  ('2015-04-23', 'a1', 9, 'Canswer9')
    ,  ('2015-04-23', 'a1', 10,'Canswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'b2', 1, 'Canswer1')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 2, 'Canswer2')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 4, 'Canswer4')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 6, 'Ianswer6')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'b2', 10, 'Ianswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'c3', 1, 'Ianswer1')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 2, 'Ianswer2')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 4, 'Ianswer4')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 6, 'Ianswer6')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'c3', 10, 'Ianswer10')

insert QuestionAnswers
values (CONVERT(DATE, GETDATE()), 'a1', 1, 'Canswer1')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 2, 'Ianswer2')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 3, 'Canswer3')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 4, 'Canswer4')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 5, 'Canswer5')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 6, 'Canswer6')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 7, 'Canswer7')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 8, 'Canswer8')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 9, 'Canswer9')
    ,  (CONVERT(DATE, GETDATE()), 'a1', 10, 'Ianswer10')
-->End test data creation

--straight join
select qa.user_id, qa.question_set, q.question_id, qa.answer
from Questions q
    join QuestionAnswers qa on qa.question_id=q.question_id
order by qa.user_id

--dynamic pivot
DECLARE
    @questionList varchar(max)
, @maxQID int
, @qid int

select @questionList='',@maxQID = MAX(question_id), @qid= MIN(question_id)
FROM Questions

while @qid <= @maxQID
begin
    set @questionList=@questionList+'['+cast(@qid as varchar(10))+']'

    select @qid=min(question_id)
    from Questions
    where question_id > @qid

    if @qid<=@maxQID
        set @questionList=@questionList+', '
end

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = N'
select user_id, '+@questionList+'
from
(select q.question_id, qa.question_set, qa.user_id, qa.answer
from Questions q
    join QuestionAnswers qa on qa.question_id=q.question_id) x
PIVOT
(
max(answer)
FOR question_id in ('+@questionList+')
) pvt
order by user_id'

exec sp_executesql @SQL


Is this what you want to happen?

DECLARE @Test1 TABLE
(
  field1 VARCHAR(10),
  field2 INT
);

INSERT INTO @Test1 ( field1, field2 )
    VALUES ( '.05', 12 );

SELECT field1 / CONVERT(DECIMAL(18, 2), field2)
    FROM @Test1;

Since this was marked as the answer: I converted the integer value to a decimal because of data type precedence. The string '.05' can't be converted to an integer, but it makes a perfectly fine decimal.


Check:

WITH
TG1 AS ( SELECT PID AS ID, FVALUE, TVALUE
         FROM mydb.myuser.t1
         UNION
         SELECT PID, FVALUE, TVALUE
         FROM mydb.myuser.t2
         UNION
         SELECT PID, FVALUE, TVALUE
         FROM mydb.myuser.t3 ),
TG2 AS ( SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T4
         UNION
         SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T5
         UNION
         SELECT PID, VALUE, VALUEID, VALUEDESC
         FROM mydb.myuser.T6 )
SELECT TG1.*,
       TG21.PID FPID,
       TG22.PID TPID,
       TG21.VALUEID FVALUEID,
       TG22.VALUEID TVALUEID,
       TG21.VALUEDESC FVALUEDESC,
       TG22.VALUEDESC TVALUEDESC
FROM TG1
JOIN TG2 TG21 ON TG1.FVALUE = TG21.VALUE
JOIN TG2 TG22 ON TG1.TVALUE = TG22.VALUE


RDS Postgres chicken-and-egg problem altering roles

Your RDS master user is not a superuser.

ALTER ROLE changes the attributes of a PostgreSQL role. [...]
Roles having CREATEROLE privilege can change any of these settings except SUPERUSER, REPLICATION, and BYPASSRLS...

Omit incompatible settings from your ALTER ROLE command.


Select records between different work shifts depending on datetime

I feel like this is harder than it needs to be. I like to imagine that in your dataset you have a datetime column that is stored when each request is submitted. If that is the case I think you would have a much better time writing a proc that filters that column.

However, I used your proc and just branched off. I didn't test this since i'm not sure what your dataset looks like but I think you may be looking for something along the lines of this

create procedure [dbo].[GetRequests]
as begin
DECLARE @from datetime
set @from = getdate();
if (DATEPART(HOUR, @from) < 6) set @from = dateadd(day, -1, @from);

DECLARE @start_date datetime = CONVERT(datetime, CONVERT(char(9), @from, 112) +     '06:00');
DECLARE @end_date datetime = DATEADD(HOUR, 24, @start_date)
declare @fshift datetime = dateadd(hh, 8, @start_date)
declare @tshift datetime = dateadd(hh, 8, @fshift)
declare @trshift datetime = dateadd(hh, 8, @tshift)
declare @night datetime = dateadd(hh, 8, trshift)

select *
FROM Requests
where RequestDate in (select *
                      FROM Requests
                      where RequestDate between  @start_date and @end_date)
    and RequestDate between  @fshift and @tshift
    and requestdate between @tshift and @trshift
    and requestdate between @trshift and @night
end


Do indexes get transferred with pg_restore?
  I see other questions that have been answered in the negative
  (i.e. indexes do not get transferred over with the standard pg_restore)

That seems to be a misunderstanding. The index itself (containing all the data) is not in the dump. Just the commands to recreate it. So, indexes get "transferred", but really, they are recreated in pristine condition, without any bloat or dead tuples

  Do I still need to reindex?

No. After the restore you have all indexes in perfect condition. No REINDEX needed. ANALYZE would make sense, though, as advised in the manual in the chapter "Restoring the Dump":

  After restoring a backup, it is wise to run ANALYZE on each database
  so the query optimizer has useful statistics;

And finally:

  Is 9.4 THAT much more efficient?

No, not generally. Well, the size of GIN indexes has been reduced substantially. But what you see is most probably the effect of removing bloat from all tables and indexes (including system tables).


I call this "burning ids".  I think it happens with all flavors of INSERT, including INSERT IGNORE, IODKU, REPLACE, etc.

Here is a workaround:  http://mysql.rjweb.org/doc.php/staging_table#normalization

That describes how to use 2 SQLs to "normalize" a batch of names and get their ids in an efficient way, and without burning ids.  If you have only a single name to insert/lookup, it is still 2 sqls.

INSERT allocates as many ids as it might need, then burns the ones it did not need.

I think the autoinc_mode is global, and hurts performance.
Sure, BIGINT will work, but I hate to use 8 bytes.
And, yeah, my recommendation is similar to your #3.  But look closely; there may be some useful differences, especially if you can batch things.


Questions to ask yourself start with:
How fast do you need it up?  Is it costing you money per hour being down?  Are you out of SLA?  If you don't have SLA's yet for this service can you get them real quick or have at least a good idea if it's OK to be down?

Typically you would want to learn why it is down then decide but sometimes time doesn't allow that.  Other's it could be within maintenance hours so if you know how long it takes to rebuild the cluster you know how much you have to troubleshoot.

Thus my answer would be: figure it out as long as possible while giving yourself enough time to rebuild and test it while keeping the business need it serves in mind as priority.

Personally I try to manage a process and not servers.  I wrote a lot of Powershell automation to build up a brand new FCI from scratch, build out a read only replica AG node, test, open network, test network, etc. etc.  Something like this is great if you're rebuilding and you can start your automation that way if you haven't already.  I can give you some reference on how to begin if you'd like.

Let us know if you need more info by editing your question to give us more background and clarity. Thanks!


Moved a MySQL Database to another server, but socket keeps pointing to localhost

It is a socket file used for inter-process communication, you have to stop the local MySQL server and make sure the mysql.sock doesn't exist. Then you have to re-create the socket file to send the connection to the correct destination (in your case, it is Amazon RDS).

There are many ways to create a socket file, for example you can use socat, and the command is (you may have to modify to your settings) -

socat UNIX-LISTEN:/path/to/mysql.sock,fork,reuseaddr,unlink-early,user=mysql,group=mysql,mode=777 TCP:<Amazon RDS Server>:3306 &

Source: http://linux.die.net/man/1/socat

Note: This isn't the right way to fix this, so you may want to check the bnconfig script to get a proper fix.


AUTO_CREATE_STATISTICS set to OFF in tempdb

http://searchsqlserver.techtarget.com/tip/SQL-Server-tempdb-best-practices-increase-performance

You can further increase tempdb performance by disabling the auto update stats, which will save your tempdb some work. Usually objects created in the tempdb are fairly small and, as such, the statistics will not reach the threshold that causes the statistics to update automatically. By disabling the setting, you stop SQL Server from having to check to see if it needs to update the statistics. However, you'll need to manually update statistics if you occasionally use large temporary objects.

You can also set the auto create statistics option to false. That, too, will increase tempdb performance. By disabling the setting, you stop SQL Server from having to produce the statistics when the object is first created.

Settings should be changed with care. Depending on the kind of load you place on your tempdb, changing settings could adversely impact system performance. You should test the settings in a non-production environment before they are deployed to the production environment. After deployment, monitor these changes closely to ensure the system is running at peak performance.


What can cause Reads to slow down on SQL Server 2008R2?

There could be many reasons for this. A few questions:

Do you do index maintenance?
Do you import data? Or is it all entered via a front-end application by users?

Option 1 - We could look at your wait stats and see if that helps pin-point a direction to look.

Option 2 - We could look at the execution plan of a slow-running query.

For option 1:
Run this via Management Studio (it will run for 30 seconds), after you hit execute, run one of your slow-running queries in a second tab, then switch back to the previous tab and copy and paste the results here:

    DECLARE @FinishSampleTime   DATETIME

    IF OBJECT_ID('tempdb..#WaitStats') IS NOT NULL
        DROP TABLE #WaitStats;

    CREATE TABLE #WaitStats
    (
        Pass TINYINT NOT NULL,
        wait_type NVARCHAR(60),
        wait_time_ms BIGINT,
        signal_wait_time_ms BIGINT,
        waiting_tasks_count BIGINT,
        SampleTime DATETIME
    );

    -- Populate #WaitStats with DMV data. In a second, we'll compare these.
    INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
    SELECT
        1 AS Pass,
        GETDATE() AS SampleTime,
        os.wait_type,
        SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
        SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) as sum_signal_wait_time_ms,
        SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
    FROM sys.dm_os_wait_stats os
    WHERE
        os.wait_type not in (
            'REQUEST_FOR_DEADLOCK_SEARCH',
            'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'SQLTRACE_BUFFER_FLUSH',
            'LAZYWRITER_SLEEP',
            'XE_TIMER_EVENT',
            'XE_DISPATCHER_WAIT',
            'FT_IFTS_SCHEDULER_IDLE_WAIT',
            'LOGMGR_QUEUE',
            'CHECKPOINT_QUEUE',
            'BROKER_TO_FLUSH',
            'BROKER_TASK_STOP',
            'BROKER_EVENTHANDLER',
            'SLEEP_TASK',
            'WAITFOR',
            'DBMIRROR_DBM_MUTEX',
            'DBMIRROR_EVENTS_QUEUE',
            'DBMIRRORING_CMD',
            'DISPATCHER_QUEUE_SEMAPHORE',
            'BROKER_RECEIVE_WAITFOR',
            'CLR_AUTO_EVENT',
            'DIRTY_PAGE_POLL',
            'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            'ONDEMAND_TASK_QUEUE',
            'FT_IFTSHC_MUTEX',
            'CLR_MANUAL_EVENT',
            'SP_SERVER_DIAGNOSTICS_SLEEP',
            'HADR_CLUSAPI_CALL',
            'HADR_LOGCAPTURE_WAIT',
            'HADR_TIMER_TASK',
            'HADR_WORK_QUEUE'
        )
    ORDER BY sum_wait_time_ms DESC;

    -- Wait for the specified amount of time
    SET @FinishSampleTime = DATEADD(second,30,GETDATE());
    WAITFOR TIME @FinishSampleTime;

    -- Populate #WaitStats with DMV data. In a second, we'll compare these.
    INSERT #WaitStats(Pass, SampleTime, wait_type, wait_time_ms, signal_wait_time_ms, waiting_tasks_count)
    SELECT
        2 AS Pass,
        GETDATE() AS SampleTime,
        os.wait_type,
        SUM(os.wait_time_ms) OVER (PARTITION BY os.wait_type) as sum_wait_time_ms,
        SUM(os.signal_wait_time_ms) OVER (PARTITION BY os.wait_type ) as sum_signal_wait_time_ms,
        SUM(os.waiting_tasks_count) OVER (PARTITION BY os.wait_type) AS sum_waiting_tasks
    FROM sys.dm_os_wait_stats os
    WHERE
        os.wait_type not in (
            'REQUEST_FOR_DEADLOCK_SEARCH',
            'SQLTRACE_INCREMENTAL_FLUSH_SLEEP',
            'SQLTRACE_BUFFER_FLUSH',
            'LAZYWRITER_SLEEP',
            'XE_TIMER_EVENT',
            'XE_DISPATCHER_WAIT',
            'FT_IFTS_SCHEDULER_IDLE_WAIT',
            'LOGMGR_QUEUE',
            'CHECKPOINT_QUEUE',
            'BROKER_TO_FLUSH',
            'BROKER_TASK_STOP',
            'BROKER_EVENTHANDLER',
            'SLEEP_TASK',
            'WAITFOR',
            'DBMIRROR_DBM_MUTEX',
            'DBMIRROR_EVENTS_QUEUE',
            'DBMIRRORING_CMD',
            'DISPATCHER_QUEUE_SEMAPHORE',
            'BROKER_RECEIVE_WAITFOR',
            'CLR_AUTO_EVENT',
            'DIRTY_PAGE_POLL',
            'HADR_FILESTREAM_IOMGR_IOCOMPLETION',
            'ONDEMAND_TASK_QUEUE',
            'FT_IFTSHC_MUTEX',
            'CLR_MANUAL_EVENT',
            'SP_SERVER_DIAGNOSTICS_SLEEP',
            'HADR_CLUSAPI_CALL',
            'HADR_LOGCAPTURE_WAIT',
            'HADR_TIMER_TASK',
            'HADR_WORK_QUEUE'
        )
    ORDER BY sum_wait_time_ms DESC;

    ----------------------------
    -- What happened: #WaitStats
    ----------------------------
    ;with max_batch as (
        select max(SampleTime) as SampleTime
        from #WaitStats
    )
    SELECT
        'WAIT STATS' as Pattern,
        b.SampleTime as [Sample Ended],
        datediff(ss,wd1.SampleTime, wd2.SampleTime) as [Seconds Sample],
        wd1.wait_type,
        c.[Wait Time (Seconds)],
        c.[Signal Wait Time (Seconds)],
        (wd2.waiting_tasks_count - wd1.waiting_tasks_count) AS [Number of Waits]
    FROM  max_batch b
        JOIN #WaitStats wd2 on
            wd2.SampleTime =b.SampleTime
        JOIN #WaitStats wd1 ON
            wd1.wait_type=wd2.wait_type AND
            wd2.SampleTime > wd1.SampleTime
        CROSS APPLY (SELECT
            cast((wd2.wait_time_ms-wd1.wait_time_ms)/1000. as numeric(10,1)) as [Wait Time (Seconds)],
            cast((wd2.signal_wait_time_ms - wd1.signal_wait_time_ms)/1000. as numeric(10,1)) as [Signal Wait Time (Seconds)]) AS c
    WHERE (wd2.waiting_tasks_count - wd1.waiting_tasks_count) > 0
        and wd2.wait_time_ms-wd1.wait_time_ms > 0
    ORDER BY [Wait Time (Seconds)] DESC;

--  ===========================================================================
--  ********** Begin Clean-Up **********
--  ===========================================================================
    DROP TABLE #WaitStats

Like I said, this code takes 30 seconds to execute. So you hit 'Execute' then run your slow-running query in a second tab, then come back to the first tab and wait for the results. Please tell me if this is unclear.

For option 2:
Queries that suddenly slow down make me think indexing troubles. If your indexes are badly fragmented it could effect execution plans, which could cause what you are experiencing.

Assuming you are using SQL Management Studio to run your queries, hit 'CTRL+M' before executing the queries. This will generate a graphical execution plan. If you could post a screen shot it might give us something to work from. This is assuming you are ok with users of StackExchange seeing your table/index names.

Depending on how comfortable you are with everything, you could also get a free copy of SQL Sentry Plan Explorer, it will allow you to upload anonymized execution plans so others can look at them in-depth. Get that and the SSMS add-on. When you execute a query after pressing 'CTRL+M' you can right-click on the plan to open it in Plan Explorer.


Function Variable Number of Arguments plsql

DECODE is an SQL function, not PL/SQL. You can not use it in plain PL/SQL, only as part of SQL statements.

There is no nice and easy way for this. There is the method with collections, for example:

create or replace type t_numbers as table of number;
/

create or replace function sum_plsql (p_numbers t_numbers) return number
as
  rv number := 0;
begin
  for n in 1..p_numbers.count
  loop
    rv := rv + p_numbers(n);
  end loop;
  return rv;
end;
/

Then:

select sum_plsql(t_numbers(1, 1, 1, 1, 1, 1, 1, 1, 1)) from dual;

9

There are built-in, non-documented simple list types, so you do not need to define your own types at all circumstances, but relying on non-documented features is generally bad practice, as they may change in newer versions without warning.

If there is a reasonable limit for the number of arguments:

create or replace function sum_plsql2 (p1 number default 0, p2 number default 0, p3 number default 0, p4 number default 0) return number
as
begin
  return p1 + p2 + p3 + p4;
end;
/

select sum_plsql2(1, 1, 1) from dual;

3

Obviously, these are neither tidy nor effective.


I would say that if the company locations and non-company locations store a fair amount of different data, then to avoid a bunch of NULLS I would have two tables, and let the application insert data into the appropriate table. A different approach could be to have a table [Locations], with all the data that it needs, then have a table [CompanyLocations] and another table [Non-CompanyLocations]. The tables for company and non-company locations would then reference a base table using a foreign key, something very like what you have in the image, if I am reading it the same way you are.


Please see the reference on BOL for Database-Level Roles:

  db_owner

  Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database.

The easiest way to see all of the permissions is to use the sys.fn_my_permissions() function.  First verify you are a member of db_owner:

select
    rol.name
from sys.database_principals mem
inner join sys.database_role_members drm
on drm.member_principal_id = mem.principal_id
inner join sys.database_principals rol
on drm.role_principal_id = rol.principal_id
where mem.principal_id = user_id();

Now list out all of the effective database permissions:

select *
from sys.fn_my_permissions(null, 'database');

That should be granular enough for your purposes.  Something to note, sys.fn_my_permissions() returns the effective permissions, so think of it as an aggregation.


my azure sql database is not connecting with SSMS 2014

You should insert the database name:

sqlcmd -U ProvideLogin@Server -P ProvidePassword -S ProvideServerName -d ProvideDatabaseName

Client quick-start code samples to SQL Database


Upgrade with DistributedAG vs adding a node with SQL Server 2019

Per the Microsoft Docs, a Distributed Availability Group is:

a special type of availability group that spans two separate availability groups. The availability groups that participate in a distributed availability group do not need to be in the same location. They can be physical, virtual, on-premises, in the public cloud, or anywhere that supports an availability group deployment. This includes cross-domain and even cross-platform - such as between an availability group hosted on Linux and one hosted on Windows. As long as two availability groups can communicate, you can configure a distributed availability group with them.

Typically, a Distributed Availability Group is used to physically distance two AGs for disaster recovery purposes.   i.e if the primary AG goes down, you failover to the secondary AG, and return to normal operation.
The fact that you've added a SQL Server 2019 node to your existing AG is not a problem as long as you understand that you need to upgrade all the nodes in the AG to 2019 if you plan on ever failing back to those old nodes.  Once a database has been opened in a newer version of SQL Server, the database upgrade process means that you can never open that upgraded database in an older version of SQL Server.  Therefore, you won't be able to fail back to those old nodes if the new SQL Server 2019 node has a problem.


SQL Server's READ COMMITTED SNAPSHOT vs SNAPSHOT

READ COMMITTED SNAPSHOT uses a new snapshot after each statement. That means that less row versions are being kept alive. (The statement you quoted from the docs is slightly misleading because it suggest that this is always true - it is only true in case of long-running SNAPSHOT transactions.) Snapshot row versions are created on writes. The reads do not influence what gets put into tempdb. Writers cannot possibly foresee what reads will be carried out in the future. Readers only influence only what can be purged.
When a SNAPSHOT transaction T1 writes to a row that was modified by another transaction T2 in the time between T1 started and T1 attempted the write, the statement fails with an update conflict error. This is an optimistic concurrency model. With READ COMMITTED SNAPSHOT T1 would wait for T2 to release the X-lock on the row and continue normally.


Queries and updates extremely slow after IndexOptimize

I suspect you've got a different sample rate defined between your two maintenance approaches.  I believe Ola's scripts use default sampling unless you specify the @StatisticsSample parameter, which it doesn't look like you're currently doing.

At this point, this is speculation, but  you can check to see what sampling rate is currently being used on your statistics by running the following query in your database:

SELECT  OBJECT_SCHEMA_NAME(st.object_id) + '.' + OBJECT_NAME(st.object_id) AS TableName
    ,   col.name AS ColumnName
    ,   st.name AS StatsName
    ,   sp.last_updated
    ,   sp.rows_sampled
    ,   sp.rows
    ,   (1.0*sp.rows_sampled)/(1.0*sp.rows) AS sample_pct
FROM sys.stats st
    INNER JOIN sys.stats_columns st_col
        ON st.object_id = st_col.object_id
        AND st.stats_id = st_col.stats_id
    INNER JOIN sys.columns col
        ON st_col.object_id = col.object_id
        AND st_col.column_id = col.column_id
    CROSS APPLY sys.dm_db_stats_properties (st.object_id, st.stats_id) sp
ORDER BY 1, 2

If you see this is coming through a 1s (e.g. 100%) chances are this is your issue.  Maybe try Ola's scripts again including the @StatisticsSample parameter with the percentage getting returned by this query and see if that fixes your problem?

As additional supporting evidence for this theory, the execution plan XML shows vastly different sampling rates for the slow query (2.18233 %):

<StatisticsInfo LastUpdate="2019-09-01T01:07:46.04" ModificationCount="0"
    SamplingPercent="2.18233" Statistics="[INDX_UPP_4]" Table="[UPPDRAG]"
    Schema="[SVALA]" Database="[ulek-sva]" />

Versus the fast query (100 %):

<StatisticsInfo LastUpdate="2019-08-25T23:01:05.52" ModificationCount="555"
    SamplingPercent="100" Statistics="[INDX_UPP_4]" Table="[UPPDRAG]"
    Schema="[SVALA]" Database="[ulek-sva]" />


SQL Server 2008: How do I change the default schema of the dbo?

Each database is owned by a server principal (aka login).  Inside that database, the owning principal is known as dbo (aka *D*ata*B*ase *O*wner).  The database principal (aka user) loses its real name.

For example, for a database I own:

select  [user].name as UserName -- Database specific
,       [login].name as LoginName -- Server wide
from    sys.databases d
join    sys.database_principals as [user]
on      [user].sid = d.owner_sid
join    sys.server_principals as [login]
on      [login].sid = d.owner_sid

Will print "dbo", "Andomar".  If you'd change the owner to sa:

exec sp_changedbowner 'sa'

The query would return "dbo", "sa".

You cannot modify the default schema for the user that owns a database.  It is always user name dbo with default schema name dbo.


Optimize simple query using ORDER BY date and text

According to your comment on a deleted post, you load all rows into a java module to conduct a search there. But searching is better done in the database itself - that's what a database is good at. Only return the rows you actually need.
If you really need all rows, there are many little things to make this faster. 1M rows will never be very fast, though.
Postgres 9.2 or later
You can make the index covering by appending fcv_id:
CREATE INDEX factura_venta_orden
ON factura_venta (fcv_fecha_comprobante, fcv_numero_comprobante, fcv_id);
This way, provided the table isn't updated too much, Postgres can retrieve results with an index-only scan.
The additional column comes last since it does not contribute to the sort order. Explanation:

Is a composite index also good for queries on the first field?

In Postgres 11 or later you could make that:
CREATE INDEX factura_venta_orden
ON factura_venta (fcv_fecha_comprobante, fcv_numero_comprobante) INCLUDE (fcv_id);
CLUSTER / pg_repack
I see you already found CLUSTER. You are aware that this is a one-time operation, that should help your cause, but needs to be re-run after enough updates?
There is also the community tool pg_repack as replacement for VACUUM FULL / CLUSTER.
work_mem
This line in your EXPLAIN output:
Sort Method:  external merge  Disk: 2928kB

tells us, that sorting is not done in RAM, which is expensive. You could probably improve performance by tuning the according setting for work_mem

work_mem (integer)
Specifies the amount of memory to be used by internal sort operations and hash tables before writing to temporary disk files. ...

Setting this too high may have adverse effects. Read the manual carefully. Consider increasing the setting only for the transaction with the big query:
BEGIN;
SET LOCAL work_mem  = '50MB';
SELECT ...;
COMMIT;

50 MB are an estimate based on your EXPLAIN ANALYZE output for 73k rows. Test with 1M rows to get the actual amount you need.


How to store static list of hashes in MySQL effectively?

Some options:

Use InnoDB. If not for these reasons, then for compression! With InnoDB you can:

ALTER TABLE my_table ENGINE=InnoDB ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

Replace 8 with 4 or 2 or 1 to (hopefully) get better compression. Since the table is static, I think this is a great solution for you (with strong compression, writes become slower, but you don't care about that).

Not only data is compressed - indexes also. I would suggest this is the easiest option you have.
Only index part of your column. You agree that the column is not UNIQUE but can be assumed to be. OK; is your index a UNIQUE index? If not, try to:

ALTER TABLE my_table ADD INDEX `md5_prefix_idx` (md5(8))

to only index first 8 bytes of the columns.
Try TokuDB, which is an alternative storage engine to InnoDB, and which has an amazing compression (I've seen data get 20 times smaller than InnoDB with TokuDB aggressive compression).


ASM - How the asm search the disks and where is this parameter from?

Take a look at the ASM_DISKSTRING initiallization parameter for your ASM instance.

ASM_DISKSTRING


I had this problem and it wasn't related to user permissions.
I had done an earlier install of SQL server and chose to install many options except for 'Integration Services',
so I ran the SQL Server Installation Centre application again and this time chose to install the 'Integration Services' feature.
I think that the reason I could see the 'Integration Services Catalogs' node in SMSS despite not having installed 'Integration Services', was due to choosing to install 'Master Data Services' during my earlier SQL Server install.


Connection failed: No such file or directory Mysql - WSL

in PHP  mysqli needs 4 Paarameters

https://www.php.net/manual/de/function.mysqli-connect.php

So it should be

<?php
$servername = "localhost";
$username = "root";
$password = "root";
$database = "mydatabase";

// Create connection
$conn = new mysqli($servername, $username, $password,$database);

// Check connection
if ($conn->connect_error) {
    die("Connection failed ttt: " . $conn->connect_error);
}

echo "Connected successfully";
?>


SQL User Permissions for Continuous Integration via DbUp and Octopus

Assuming you are using SQL Server 2008 and up, A better way of doing is to create a role in the database and grant that role permissions.

You can add users to the role, so they will inherit the permissions of the role.

-- to grant CREATE, ALTER, DROP OBJECTS (tables, procs, functions, views) with ALTER permissions on the schema. You can obviously fine tune below ones as per your needs.

USE db_name;
 CREATE ROLE [new_role] AUTHORIZATION [dbo];
grant alter
      ,delete
      ,execute
      ,insert
      ,references
      ,select
      ,update
      ,view definition
on schema::dbo
to new_role;

grant create table
     ,create procedure
     ,create function
     ,create view
to new_role;

-- Add an existing user to the new role created

  EXEC sp_addrolemember 'new_role', 'DBUserName'
  GO


Should I create a separate user table for different web products within the same platform?

A user is a user, which is different than a person.

A user is a role played by a person. A recruiter is also a role played by a person.

create table party(
  id serial primary key,
  name text not null
);

create table role_type(
  id char(1) primary key,
  name text not null unique
);

insert into role_type values
('u', 'user'),
('r', 'recruiter');

/* use Table Inheritance to add role-specific details. */
create table party_role(
  party_id integer not null references party(id),
  role_type char(1) not null references role_type(id),
  ...
  primary key (party_id, role_type)
);

/*  create a user: */

insert into party values
(1, 'Neil');
insert into party_role values
(1, 'u');

/* create someone that is both a recruiter and user: */
insert into party values
(2, 'Tristan');
insert into party_role values
(2, 'u'),
(2, 'r');


Software for working with SQL Server databases in Windows Server 2008

You can use Management Studio. Download the Express version which, as of SQL Server 2012 Service Pack 1, is fully functional and no longer requires a license and a different installation to gain access to the full set of features. Here is the SP2 version:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

The file you want is SQLManagementStudio_x64_ENU.exe (unless you are on 32-bit, then you want SQLManagementStudio_x86_ENU.exe).

There is some pretty good documentation on how to use Management Studio here:

http://msdn.microsoft.com/en-us/library/ms174173(SQL.110).aspx


Transaction with lock on a specific table

Use a key table to allocate receipt numbers in a way that is concurrency friendly, and still guarantees receipt numbers will never be re-used, and will (almost) never contain gaps.

I've created a minimally complete verifiable example that you could use as a basis for learning about concurrency that should set you on a good path.

Do this work in tempdb so we don't kill anything important in your actual work:

USE tempdb;

If the objects we're creating already exist, then we'll delete them first.
This allows us to easily modify the code below and re-run it multiple times
for testing.

IF OBJECT_ID(N'dbo.Requests', N'U') IS NOT NULL
BEGIN
    DROP PROCEDURE dbo.CreateRequests;
    DROP TABLE dbo.Keys;
    DROP TABLE dbo.Receipts;
    DROP TABLE dbo.Requests;
END
GO

Requests table - add columns as required:

CREATE TABLE dbo.Requests
(
    RequestID int NOT NULL IDENTITY(1,1)
        CONSTRAINT Requests_PK
        PRIMARY KEY CLUSTERED
    , RequestDate datetime NOT NULL
    , Notes nvarchar(100) NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

Receipts table.  ReceiptID is generated by the system, ReceiptNum is the number we'll show to users.

CREATE TABLE dbo.Receipts
(
    ReceiptID int NOT NULL IDENTITY(1,1)
        CONSTRAINT Receipt_PK
        PRIMARY KEY CLUSTERED
    , RequestID int NOT NULL
        CONSTRAINT Receipt_Request_FK
        FOREIGN KEY
        REFERENCES dbo.Requests(RequestID)
    , ReceiptNum int NOT NULL
    , ReceiptCreatedOn datetime NOT NULL
) ON [PRIMARY]
WITH (DATA_COMPRESSION = PAGE);

The Key table, where we store the most recently allocated ReceiptNum value:

CREATE TABLE dbo.Keys
(
    KeyName varchar(100) NOT NULL
        CONSTRAINT Keys_PK
        PRIMARY KEY CLUSTERED
    , LastUsedKeyValue int NOT NULL
) ON [PRIMARY];
GO

The CreateRequests stored procedure:

CREATE PROCEDURE dbo.CreateRequests
(
    @NumReceipts int = 1 /* default to 1 receipt */
    , @RequestNote nvarchar(100) = N''
    , @RequestID int OUTPUT /* this will contain the newly created RequestID */
)
AS
BEGIN
    SET NOCOUNT ON; /* prevent stored procedure from displaying "x records affected" */
    DECLARE @msg nvarchar(1000) = N''; /* used to display error messages */
    DECLARE @ret int = 0; /* stored procedure return value */
    DECLARE @LastUsedKey int;
    DECLARE @keys TABLE
    (
        KeyValue int NOT NULL
    );
    DECLARE @req TABLE
    (
        RequestID int NOT NULL PRIMARY KEY
    );

    IF @NumReceipts > 0 AND @NumReceipts <= 10000 /* 10,000 is the arbitrarily chosen
        maximum number of receipts this stored proc supports */
    BEGIN
        /* Atomically allocate @NumReceipts keys to use for Receipt Numbers */
        UPDATE dbo.Keys
        SET LastUsedKeyValue = LastUsedKeyValue + @NumReceipts
        OUTPUT inserted.LastUsedKeyValue INTO @keys (KeyValue)
        WHERE KeyName = 'ReceiptNum';
        SELECT @LastUsedKey = k.KeyValue
        FROM @keys k;
        IF @LastUsedKey IS NULL /* the first time we run this code, we insert a new Key */
        BEGIN
            INSERT INTO dbo.Keys (KeyName, LastUsedKeyValue)
            VALUES ('ReceiptNum', @NumReceipts)
            SET @LastUsedKey = @NumReceipts;
        END

        /* Create a new Request row, outputting the inserted RequestID
            into a temporary table for use in the Receipts table */
        INSERT INTO dbo.Requests (RequestDate, Notes)
        OUTPUT inserted.RequestID INTO @req(RequestID)
        SELECT GETDATE(), @RequestNote;

        /* Create the new Receipts rows */
        ;WITH src AS ( /* this is configured to support a maximum of 10,000 new receipts */
            SELECT TOP(@NumReceipts)
                ReceiptNum = (v5.Num + (v4.Num * 10) + (v3.Num * 100) + (v2.Num * 1000)
                     + (v1.Num * 10000)) + 1 + (@LastUsedKey - @NumReceipts)
            FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(Num)
               CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v5(Num)
            ORDER BY v1.Num
                , v2.Num
                , v3.Num
                , v4.Num
                , v5.Num
        )
        INSERT INTO dbo.Receipts (RequestID, ReceiptNum, ReceiptCreatedOn)
        SELECT r.RequestID, ReceiptNum, GETDATE()
        FROM @req r
            CROSS JOIN src
        ORDER BY src.ReceiptNum;

        /* we'll return the newly created RequestID in @RequestID */
        SELECT @RequestID = r.RequestID
        FROM @req r;

        /* -1 indicates success */
        SET @ret = -1;
    END
    ELSE
    BEGIN
        SET @msg = N'Invalid number of receipts requested.';
        RAISERROR (@msg, 14, 1);
        SET @ret = 1;
    END
    RETURN @ret;
END
GO

Here, we add three different requests, with a varying number of receipts to show the functionality of the stored procedure:

DECLARE @ReqID int;
DECLARE @ReturnValue int;
EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 19
    , @RequestNote = N'Test Request #1'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     1 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║         1 ║         1 ║          1 ║ 2017-06-01 09:04:37.107 ║
║         2 ║         1 ║          2 ║ 2017-06-01 09:04:37.107 ║
║         3 ║         1 ║          3 ║ 2017-06-01 09:04:37.107 ║
║         4 ║         1 ║          4 ║ 2017-06-01 09:04:37.107 ║
║         5 ║         1 ║          5 ║ 2017-06-01 09:04:37.107 ║
║         6 ║         1 ║          6 ║ 2017-06-01 09:04:37.107 ║
║         7 ║         1 ║          7 ║ 2017-06-01 09:04:37.107 ║
║         8 ║         1 ║          8 ║ 2017-06-01 09:04:37.107 ║
║         9 ║         1 ║          9 ║ 2017-06-01 09:04:37.107 ║
║        10 ║         1 ║         10 ║ 2017-06-01 09:04:37.107 ║
║        11 ║         1 ║         11 ║ 2017-06-01 09:04:37.107 ║
║        12 ║         1 ║         12 ║ 2017-06-01 09:04:37.107 ║
║        13 ║         1 ║         13 ║ 2017-06-01 09:04:37.107 ║
║        14 ║         1 ║         14 ║ 2017-06-01 09:04:37.107 ║
║        15 ║         1 ║         15 ║ 2017-06-01 09:04:37.107 ║
║        16 ║         1 ║         16 ║ 2017-06-01 09:04:37.107 ║
║        17 ║         1 ║         17 ║ 2017-06-01 09:04:37.107 ║
║        18 ║         1 ║         18 ║ 2017-06-01 09:04:37.107 ║
║        19 ║         1 ║         19 ║ 2017-06-01 09:04:37.107 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

Run #2:

EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 3
    , @RequestNote = N'Test Request #2'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     2 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║        20 ║         2 ║         20 ║ 2017-06-01 09:04:37.160 ║
║        21 ║         2 ║         21 ║ 2017-06-01 09:04:37.160 ║
║        22 ║         2 ║         22 ║ 2017-06-01 09:04:37.160 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

Run #3:

EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 9999
    , @RequestNote = N'Test Request #3'
    , @RequestID = @ReqID OUT
SELECT ReturnValue = @ReturnValue, ReqID = @ReqID;
SELECT *
FROM dbo.Receipts rec
WHERE rec.RequestID = @ReqID;

The output:

╔═════════════╦═══════╗
║ ReturnValue ║ ReqID ║
╠═════════════╬═══════╣
║          -1 ║     3 ║
╚═════════════╩═══════╝

╔═══════════╦═══════════╦════════════╦═════════════════════════╗
║ ReceiptID ║ RequestID ║ ReceiptNum ║    ReceiptCreatedOn     ║
╠═══════════╬═══════════╬════════════╬═════════════════════════╣
║        23 ║         3 ║         23 ║ 2017-06-01 09:04:37.210 ║
║        24 ║         3 ║         24 ║ 2017-06-01 09:04:37.210 ║
║        25 ║         3 ║         25 ║ 2017-06-01 09:04:37.210 ║
║        26 ║         3 ║         26 ║ 2017-06-01 09:04:37.210 ║
.....
║     10021 ║         3 ║      10021 ║ 2017-06-01 09:04:37.210 ║
╚═══════════╩═══════════╩════════════╩═════════════════════════╝

To verify concurrency, and that the allocation of Receipt Numbers is reliable, I ran the following code simultaneously in three separate SSMS windows, creating nearly 30,000 receipts:

DECLARE @note nvarchar(100);
DECLARE @ReqID int;
DECLARE @ReturnValue int;
DECLARE @loop int = 1;
DECLARE @maxloops int = 1000;
WHILE @loop < @maxloops
BEGIN
    SET @note = N'Concurrency request loop ' + CONVERT(nvarchar(10), @loop);
    EXEC @ReturnValue = dbo.CreateRequests @NumReceipts = 5
        , @RequestNote = @note
        , @RequestID = @ReqID OUT;
    SET @loop += 1;
END;


Non-overlapping rectangles constrained to a boundary

Basic answer

I suggest to use the geometric type box and combine that with an exclusion constraint (Postgres 9.2+). Should be the perfect solution to your problem. The GiST index this is implemented with (automatically) also supports certain queries.

Combine it with with equality on board_id to hold multiple boards in a single table. You'll need the additional module btree_gist. Once per database:

CREATE EXTENSION btree_gist;

To confine parts to a board add a CHECK constraint. You need the confining box of the board in the board_part table (redundantly). Could look like this:

CREATE TABLE board_part (
   board_id bigint NOT NULL references board
  ,board_box box NOT NULL
  ,part_id bigint NOT NULL references part
  ,part_box box NOT NULL
  ,EXCLUDE USING gist (board_id WITH =, part_box WITH &&)
  ,CHECK (part_box <@ board_box)
);

&& .. "overlaps" operator; <@ .. "contained" operator

The drawback: you need the dimensions of each part and box in table board_part redundantly.

Advanced answer

To avoid redundant storage, I like this new idea better: Fake IMMUTABLE functions returning the box for an id and build constraints upon these functions. Your tables can make do with just the columns of your original design.

I ran a complete test to verify it works.

Complete schema

Base tables:

CREATE TABLE part (
   part_id serial PRIMARY KEY
  ,part text NOT NULL
  ,wide int NOT NULL
  ,high int NOT NULL
);

CREATE TABLE board (
  board_id serial PRIMARY KEY
 ,board text NOT NULL
 ,wide int NOT NULL
 ,high int NOT NULL
);

IMMUTABLE Functions:

CREATE OR REPLACE FUNCTION f_boardbox(_board_id int)
  RETURNS box AS
'SELECT box(point(0,0), point (b.wide, b.high))
 FROM public.board b WHERE board_id = $1'
LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION f_partbox(_part_id int, _wide int, _high int)
  RETURNS box AS
'SELECT box(point($2,$3), point($2 + p.wide, $3 + p.high))
 FROM public.part p WHERE part_id = $1'
LANGUAGE sql IMMUTABLE;

Replace public with the actual schema of your tables.

Main table:

CREATE TABLE board_part (
  board_id int NOT NULL REFERENCES board
 ,part_id int NOT NULL REFERENCES part
 ,wide int NOT NULL
 ,high int NOT NULL
 ,EXCLUDE USING gist (board_id WITH =, f_partbox(part_id, wide, high) WITH &&)
 ,CHECK (f_partbox(part_id, wide, high) <@ f_boardbox(board_id))
);

Of course, if you update dimensions for a board_id or part_id in use, you partly void the index and / or constraint. You would need to recreate any index or constraint building on the promise that the return value of IMMUTABLE function never change. More explanation for the technique in this related answer on SO:
Does PostgreSQL support “accent insensitive” collations?

However, you can avoid this expensive operation with triggers to update only affected rows:

Triggers

CREATE OR REPLACE FUNCTION f_board_upaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE  board_part
   SET     board_id = board_id       -- enough to trigger CHECK constraint
   WHERE   board_id = NEW.board_id;  -- limit to relevant rows

   RETURN NULL;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER board_upaft
AFTER UPDATE OF wide, high ON board  -- limit to relevant columns
FOR EACH ROW EXECUTE PROCEDURE f_board_upaft();

CREATE OR REPLACE FUNCTION f_part_upaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE  board_part
   SET     part_id = 0
   WHERE   part_id = NEW.part_id;

   UPDATE  board_part
   SET     part_id = NEW.part_id
   WHERE   part_id = 0;              -- enough to update EXCL. constraint

   RETURN NULL;
END
$func$
  LANGUAGE plpgsql;

CREATE TRIGGER part_upaft
AFTER UPDATE OF wide, high ON part
FOR EACH ROW EXECUTE PROCEDURE f_part_upaft();

part_id = 0 is a special row in table part with size 0. Needed for the trigger.

Test data:

INSERT INTO board (board, wide, high)
VALUES ('b1010',10,10), ('b2030',20, 30);

INSERT INTO part (part_id, part, wide, high)
VALUES (0,'p0',0,0);          -- special row needed for trigger!

INSERT INTO part (part, wide, high)
VALUES ('p11',1,1), ('p33',3,3);

INSERT INTO board_part (board_id, part_id, wide, high)
VALUES (1,1,3,3), (1,1,5,5)   -- no overlap, inside board
      ,(2,1,3,3);             -- different board, no overlap

Test

These must raise exceptions if the triggers & constraints do their job:

UPDATE part SET wide = 6 , high = 6
WHERE  part_id = 1;            -- violates EXCL. & CHECK constraint

UPDATE part SET wide = 3 , high = 3
WHERE  part_id = 1;            -- violates EXCL. constraint

UPDATE board SET wide = 2 , high = 2
WHERE  board_id = 1;            -- violates CHECK constraint

Its not possible to install extensions on SQLfiddle, so simplified exclusion constraint without btree_gist: SQLfiddle to play with.


From the limited information given, a suitable non-unique clustered index is:

CREATE CLUSTERED INDEX CX_dbo_myTbl_StartDate_EndDate_Username
ON dbo.myTbl (StartDate, EndDate, UserName);

That said, there are all sorts of other considerations that might mean a different choice would be optimal for the workload as a whole. If you improve your question with additional detail, it will be possible to give a better answer.

Also, in general, a PRIMARY KEY (clustered or not) is highly desirable. It is a fundamental principle of relational design that each row (tuple) of a table should be capable of being uniquely identified.


I agree a pivot should get you what you need, but you should be aware that the column names are going to be fixed.  Something like the following should work. You could also extend this example to use dynamic column names if you use dynamic SQL.

SELECT
    *
FROM
(
    SELECT
        [from]  AS [Sender]
        , DATENAME(DW, [date]) + N'' + CAST(DATEPART(DAY, [date]) AS NVARCHAR(4)) AS [FormmattedDay] -- This output must match the 'IN' clause below
    FROM <YourTable>
) as s
PIVOT
(
    COUNT([from]) -- Aggregate
    FOR [FormmattedDay] IN ([Monday 5], [Tuesday 6], [Wednesday 7], etc.) -- Match to pivoted columns
)as [pivot]

Here is my standard disclaimer for PIVOT queries.  Try not to do them if you don't have to, and let the application layer format the data instead.  If you go that route your query would be much nicer.


PostgreSQL EXCLUDE USING error: Data type integer has no default operator class

Install the additional module btree_gist as is mentioned in the manual at the location you linked to:

  You can use the btree_gist extension to define exclusion constraints
  on plain scalar data types, which can then be combined with range
  exclusions for maximum flexibility. For example, after btree_gist is
  installed, the following constraint will reject overlapping ranges
  only if the meeting room numbers are equal:

In modern PostgreSQL you only need to run (once per database):

CREATE EXTENSION btree_gist;

You need to have the "contrib" package installed in your OS first. Details depend on your OS and the software repository used. For the Debian family it's typically postgresql-contrib-9.2 (for Postgres 9.2). Or just postgresql-contrib for the Red Hat family. Consider this related answer on SO:

Error when creating unaccent extension on PostgreSQL


The documentation states the following:

  Indexes can be used via either simple index scans or "bitmap" index
  scans. In a bitmap scan the output of several indexes can be combined
  via AND or OR rules, so it is difficult to associate individual heap
  row fetches with specific indexes when a bitmap scan is used.
  Therefore, a bitmap scan increments the
  pg_stat_all_indexes.idx_tup_read count(s) for the index(es) it uses,
  and it increments the pg_stat_all_tables.idx_tup_fetch count for the
  table, but it does not affect pg_stat_all_indexes.idx_tup_fetch.

  Note: The idx_tup_read and idx_tup_fetch counts can be different even
  without any use of bitmap scans, because idx_tup_read counts index
  entries retrieved from the index while idx_tup_fetch counts live rows
  fetched from the table. The latter will be less if any dead or
  not-yet-committed rows are fetched using the index, or if any heap
  fetches are avoided by means of an index-only scan.

This gives you two possibilities.  The first one you've excluded by rebuilding the index, so one could guess this index is used exclusively via index-only scans and/or bitmap index scans.  You can confirm this buy checking the EXPLAIN output of your queries that may use this index.

You can read a bit more about bitmap scans on depesz's page.


There are really several different things in play here:

Recovery to the current state if the DB server is lost. Synchronous replication is your only option here, but it comes with some risks - if the replica goes down or fails the master will stall, and if there's much latency then performance will suffer due to the need for the replica to confirm before commits are accepted. Setting synchronous_commit = off for transactions that aren't crucial can help.
Recovery to a recent state if the DB server is lost. Asynchronous replication and is what you want here. It doesn't guarantee to be up to date to the latest committed transaction, but has less performance impact.
Recovery to some previous point in time to cope with accidental deletions, corruption, etc. Your two tools here are dumps and PITR (wal archiving), with different advantages. PITR lets you recover to a finer-grained point in time ("what was in my customer table just before Bob dropped it?"), but doesn't protect as effectively against corruption of database storage and can require more disk space. PgBarman helps automate PITR, as does the tool WAL-E. Dumps can be more compact and are portable between PostgreSQL versions and architectures, but are coarser grained snapshots of the DB state - you can't have an incremental dump in Pg.
High availability. HA isn't so much for protection against data loss as to maintain uptime. In PostgreSQL it's implemented with replication and failover. repmgr is one tool that can help you manage this, in combination with something like PgBouncer or HAProxy for connection routing.

Personally I think your minimum requirement is to use PgBarman or WAL-E to archive WAL for PITR, and do nightly dumps as additional redundancy for backup purposes.

Look into synchronous replication if you cannot afford to possibly risk losing a transaction (e.g. if you're processing financials/payments).

Investigate HA if you reach the point where downtime becomes unacceptable.


Can anyone help me in fixing the issue (terminating the instance due to error 474) and then Instance terminated by PMON

I can't see your trc file to confirm, but it sounds an awful lot like this

https://support.oracle.com/knowledge/Oracle%20Database%20Products/1361872_1.html

Instance Terminated With Error ORA-00474: SMON Process Terminated With Error (Doc ID 1361872.1)
Last updated on JULY 23, 2019

If you login to the MOS system, you'll see the the cause is due to a corrupted INDEX. A diagnostic and remediation script is provided to verify and fix the problem.

Any time you get a message like this or a ORA-600 message, the first place to go is My Oracle Support.


How to split a multivalued column, do a lookup and update another column?

I was able to achieve the update of all records my table, using the below set of commands :

DECLARE @MemberNames TABLE(ID VARCHAR(50), Person VARCHAR(255))
INSERT INTO @MemberNames
SELECT P.ID, u.NAME
FROM dbo.tblProjects AS P
CROSS apply dbo.fnSplitString(P.TeamMembers, ',') AS S
     LEFT OUTER JOIN Users u ON s.splitdata = u.EMAILID

DECLARE @Names TABLE(ID VARCHAR(50), Name VARCHAR(MAX) )
INSERT INTO @Names
SELECT t.ID, STUFF(ISNULL((SELECT '; ' + x.Person
            FROM @MemberNames x
           WHERE x.ID = t.ID
        GROUP BY x.Person
         FOR XML PATH (''), TYPE).VALUE('.','VARCHAR(max)'), ''), 1, 2, '')
    FROM @MemberNames t
    GROUP BY t.ID
UPDATE tblProjects SET Team = Name FROM tblProjects pr INNER JOIN @Names ON ID = pr.ID

I have a table-valued function fnSplitString for splitting a string based on a delimiter.

p.s. I couldn't get the updates using a single query, as I had mentioned in my question.


What would be the correct entity-relationship diagram for this CSV file?

I have similar data model. It is not exactly as you expect. But surely it will help you to get start.

From the CSV file, the following columns can be derived:

Key
Article_Number
Color_Code
Description
Price
Action_Price
Delivery
Category
Size
Color

For the above mentioned table, the following model can be designed. Depends on your exact requirement, you may normalize this model or denormalize.

I hope this answer will help you.


Can Records be Replicated in an Availability Group Out of Sequence?

At the time of execution, 5 records that meet the criteria are committed on the primary, but another 3, with slightly earlier timestamps than the first 5 (but within the criteria range) are still in open transactions. Does the nature of availability groups require all transactions to be applied in LSN order...

REDO requires that LSNs be applied in sequence order, this doesn't change inside or outside of an AG. When the REDO thread is working, it'll have to do the log records in order.

  ... (delaying the visibility of all 8 records until all are committed) or do the delayed 3 records get later LSNs and are applied as soon as they are committed, potentially after the ETL process has adjusted its date criteria?

There is no delaying. In Availability Groups we don't ship individual transactions. We also don't wait for them to commit.

The unit of transport is a LOG BLOCK which is a collection of LOG RECORDS, in order. These log blocks hold log records from many different transactions in the database and not just the ETL ones you have going on. Additionally, the log blocks are sent when they are closed and flushed to disk, which happens for a few different reasons and does not necessarily need to be from a commit.

This means the secondary receives information on all transactions that are going on in the database that are making changes. In this case, the secondary has some portion, if not all, of the information about the earlier transactions that are still open.

Now, this hits on another point. Readable secondary servers aren't using read committed isolation level, in fact it is mapped to snapshot isolation under the covers. This is the reason you cannot see any information on the secondary pertaining to the earlier 3 transactions. You cannot add nolock to the query, it won't let you see the data.

Since snapshot isolation is used, we're going to use the version store. This means if you started a transaction on the secondary to read information and it is still running when the open transactions on the primary are committed, it will not "see" them until the transaction is over. This is part of the isolation of snapshot.

To summarize:

Log blocks are shipped, not log records, and NOT just committed transactions
Yes, there is information already on the secondary about those transactions
Readable secondary servers have read committed (default) isolation level mapped to snapshot isolation level automatically
If you need the data on the secondary, avoid large long running transactions


MySQL Help - Combining 2 MySQL Selects - Result in 1

(I am assuming you want the result in a WHERE or some other place for testing?)

This is faster, but is equivalent to COUNT(*) >= 1.

SELECT ...
    WHERE EXISTS ( SELECT * FROM monitor
                   WHERE name = 'job_starttime' AND value < ... )
      AND EXISTS ( SELECT * FROM monitor
                   WHERE name = 'job_active' AND value = 0 )

Otherwise:

SELECT ...
    WHERE ( SELECT COUNT(*) ... ) = 1
      AND ( SELECT COUNT(*) ... ) = 1

Another tip:  false == 0; true == 1.


Check all login users list from back 3 months

Unless you set up something (Server side trace, Profiler trace, Extended event trace, Security audit, Modify SQL Error log retention) and retain that information you will not find what you are looking for.

Based on the comments, I assume what you are looking for will be in the default trace. But unfortunately SQL Server only keeps 5 files, each 20MB. It is a fixed size buffer meaning older events get deleted.

This is a great resource to learn about default trace:

https://www.simple-talk.com/sql/performance/the-default-trace-in-sql-server-the-power-of-performance-and-security-auditing/

Starting SQL 2008 Extended event health session is running continuously unless someone turn it off.

SQL error log keeps a good deal of information. Again by default keep current and 6 older files. If you do not recycle error log and did not restart more than 6 times you might still get some information from there.

This answer by Aaron Bertrand go in details about what information is collected in these default collections.

If you need anything more than these you can always set up your own trace/audit.


Need help merging multiple full SQL Server Backups into single database

SQL server doesn't have any built-in capacity to merge multiple backups into a single database, you'll have to restore each backup with a different database name (like WellProductionInjection_2019 and WellProductionInjection_2020), then do the work yourself to combine them however you need.
Once the databases are restored, you can combine them using a variety of tools; the simplest one is called the "Import/Export Wizard". You can use this to copy entire tables from one database to another (if all tables have different names), or to append the data from table in one database to the tables in the other (if you actually want to combine the data into the same table).


There isn't a clear-cut flow chart online for this, but if you want to understand how it all comes together for InnoDB, one of the best explanations I have seen are at M18 Deep Dive InnoDB Transactions and Write Paths. The slideshow is available at https://mariadb.org/wp-content/uploads/2018/07/Deep-Dive_-InnoDB-Transactions-and-Write-Paths.pdf

That presentation is for MariaDB, but MySQL should be quite similar.

The presentation of overall architecture of MySQL 8.0 at https://www.percona.com/resources/webinars/mysql-80-architecture-and-enhancement is also quite clear.


SELECT with large WHERE IN clause taking long time

This is what Joins are for. You don't have to get a list of patients ids and then send them back to the SQL engine. You can combine the two queries into one, with JOIN:

SELECT x.*
FROM xrays AS x
  JOIN appointments AS a
    ON a.patID = x.patID
WHERE a.docID IN ('docid1', 'docid2', ..... , 'docidn')
ORDER BY x.patID                   --- optional so your application gets
                                   --- the data of patients, ordered.

Other things to check is the indexes you have on the tables. As a rule of thumb, at least all columns used in joins, like your docID, patID, should have an index.


Answer

Since you refer to the website use-the-index-luke.com, consider the chapter:

Use The Index, Luke › The Where Clause › Searching For Ranges › Greater, Less and BETWEEN

It has an example that matches your situation perfectly (two-column index, one is tested for equality, the other for range), explains (with more of those  nice index graphics) why @ypercube's advice is accurate and sums it up:

Rule of thumb: index for equality first — then for ranges.

Also good for just one column?

What to do for queries on just one column seems to be clear. More details and benchmarks concerning that under these related question:

Working of indexes in PostgreSQL
Is a composite index also good for queries on the first field?

Less selective column first?

Apart from that, what if you have only equality conditions for both columns?

It doesn't matter. Put the column first that is more likely to receive conditions of its own, which actually matters.

Consider this demo, or reproduce it yourself. I create a simple table of two columns with 100k rows. One with very few, the other one with lots of distinct values:

CREATE TEMP TABLE t AS
SELECT (random() * 10000)::int AS lots
     , (random() * 4)::int     AS few
FROM generate_series (1, 100000);

DELETE FROM t WHERE random() > 0.9;  -- create some dead tuples, more "real-life"

ANALYZE t;

SELECT count(distinct lots)   -- 9999
     , count(distinct few)    --    5
FROM   t;

Query:

SELECT *
FROM   t
WHERE  lots = 2345
AND    few = 2;

EXPLAIN ANALYZE output (Best of 10 to exclude caching effects):

Seq Scan on t  (cost=0.00..5840.84 rows=2 width=8)
               (actual time=5.646..15.535 rows=2 loops=1)
  Filter: ((lots = 2345) AND (few = 2))
  Buffers: local hit=443
Total runtime: 15.557 ms

Add index, retest:

CREATE INDEX t_lf_idx ON t(lots, few);

Index Scan using t_lf_idx on t  (cost=0.00..3.76 rows=2 width=8)
                                (actual time=0.008..0.011 rows=2 loops=1)
  Index Cond: ((lots = 2345) AND (few = 2))
  Buffers: local hit=4
Total runtime: 0.027 ms

Add other index, retest:

DROP INDEX t_lf_idx;
CREATE INDEX t_fl_idx  ON t(few, lots);

Index Scan using t_fl_idx on t  (cost=0.00..3.74 rows=2 width=8)
                                (actual time=0.007..0.011 rows=2 loops=1)
  Index Cond: ((few = 2) AND (lots = 2345))
  Buffers: local hit=4
Total runtime: 0.027 ms


Merge two XML outputs to one from SQL query

Something close to your desired output:

;with cpt
AS
(
       Select 'abc' as cptname
)
,sys
AS
(
      Select 'a' as "system"
      union all Select 'b' as "system"
      union all Select 'Manual' as "system"
)
,org
AS
(
      Select 'Coll' AS "orgRole"
      union all Select 'Lega'
)

SELECT
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
s."externalCodes"
FROM
   cpt as c1
   CROSS APPLY
   (SELECT
        s."system" as "externalCode/system",
        LTRIM(RTRIM(c1.cptname)) as "externalCode/extCode"
    FROM sys as s
    FOR XML PATH(''), TYPE, ELEMENTS
    )s(externalCodes)

UNION ALL

SELECT
LTRIM(RTRIM(c1.cptname)) +'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) +'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) +'.' as "corgHeader/shortName",
o.orgRole as "orgRole",
null
FROM
   cpt as c1
   CROSS APPLY
   (SELECT org.orgRole
     FROM org
     FOR XML PATH(''), TYPE, ELEMENTS
   ) as o(orgRole)

FOR XML PATH('organisation'), root ('collection')

output for it:

<collection>
   <organisation>
      <orgHeader>
         <orgCode>abc</orgCode>
         <longName>abc</longName>
         <shortName>abc</shortName>
      </orgHeader>
      <orgRole>Branch</orgRole>
      <externalCodes>
         <externalCode>
            <system>a</system>
            <extCode>abc</extCode>
         </externalCode>
         <externalCode>
            <system>b</system>
            <extCode>abc</extCode>
         </externalCode>
         <externalCode>
            <system>Manual</system>
            <extCode>abc</extCode>
         </externalCode>
      </externalCodes>
   </organisation>
   <organisation>
      <orgHeader>
         <orgCode>abc.</orgCode>
         <longName>abc.</longName>
         <shortName>abc.</shortName>
      </orgHeader>
      <orgRole>
         <orgRole>Coll</orgRole>
         <orgRole>Lega</orgRole>
      </orgRole>
   </organisation>
</collection>

You said that the parent tag orgRole should be removed. We are close to the final solution , but not yet there.
I have an idea with FOR XML EXPLICIT

;with cpt
AS
(
       Select 'abc' as cptname
)
,sys
AS
(
      Select 'a' as "system"
      union all Select 'b' as "system"
      union all Select 'Manual' as "system"
)
,org
AS
(
      Select 'Coll' AS "orgRole"
      union all Select 'Lega'
)
,cte_source
AS
(
SELECT LTRIM(RTRIM(c1.cptname)) as cptname,
'Branch' as orgRole,
s.system,
s.extcode
FROM
   cpt as c1
   CROSS APPLY
   (SELECT
        s.system ,
        LTRIM(RTRIM(c1.cptname)) as extCode
    FROM sys as s
    )s(system,extCode)

UNION ALL

SELECT
LTRIM(RTRIM(c1.cptname)) +'.' ,
o.orgRole,
null,
null
FROM
   cpt as c1
   CROSS APPLY
   (SELECT org.orgRole
     FROM org
   ) as o(orgRole)
)

--select * from cte_source

SELECT 1 as Tag
    ,NULL as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,NULL as [orgHeader!3!orgCode!ELEMENT]
    ,NULL as [orgHeader!3!longName!ELEMENT]
    ,NULL as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]

UNION ALL

-- for organisation
SELECT DISTINCT
    2 as Tag
    ,1 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for orgHeader
SELECT DISTINCT
    3 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for orgRole
SELECT DISTINCT
    4 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,c.orgRole as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for externalCodes
SELECT DISTINCT
    5 as Tag
    ,2 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname  as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,NULL as [externalCode!6!system!ELEMENT]
    ,NULL as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

UNION ALL

--for externalCode
SELECT DISTINCT
    6 as Tag
    ,5 as Parent
    ,NULL as [collection!1!]
    ,NULL as [organisation!2!]
    ,NULL as [orgHeader!3!]
    ,c.cptname as [orgHeader!3!orgCode!ELEMENT]
    ,c.cptname as [orgHeader!3!longName!ELEMENT]
    ,c.cptname as [orgHeader!3!shortName!ELEMENT]
    ,NULL as [orgRole!4!]
    ,NULL as [externalCodes!5!]
    ,c.system as [externalCode!6!system!ELEMENT]
    ,c.extCode as [externalCode!6!extCode!ELEMENT]
FROM
  cte_source as c

ORDER BY
    [organisation!2!]
    ,[orgHeader!3!orgCode!ELEMENT]
    --,[orgRole!4!]
    ,[externalCode!6!system!ELEMENT]
    ,[externalCode!6!extCode!ELEMENT]

FOR XML EXPLICIT

the output for it:

<collection>
  <organisation>
    <orgHeader>
      <orgCode>abc</orgCode>
      <longName>abc</longName>
      <shortName>abc</shortName>
    </orgHeader>
    <orgRole>Branch</orgRole>
    <externalCodes>
      <externalCode>
        <system>a</system>
        <extCode>abc</extCode>
      </externalCode>
      <externalCode>
        <system>b</system>
        <extCode>abc</extCode>
      </externalCode>
      <externalCode>
        <system>Manual</system>
        <extCode>abc</extCode>
      </externalCode>
    </externalCodes>
  </organisation>
  <organisation>
    <orgHeader>
      <orgCode>abc.</orgCode>
      <longName>abc.</longName>
      <shortName>abc.</shortName>
    </orgHeader>
    <orgRole>Coll</orgRole>
    <orgRole>Lega</orgRole>
    <externalCodes>
      <externalCode />
    </externalCodes>
  </organisation>
</collection>

a small remark : externalCodes/externalCode still appears (with null) when we have null values in the corespondent columns.

You can use two variables : one to store the first part of the xml and one to store the second part; then add as prefix <collection> and a suffix `'

declare @cpt table
(
    cptname varchar(10)
)
insert into @cpt(cptname)
values('abc')

declare @sys table
(
    system varchar(10)
)
insert into @sys(system)
values('a'),('b'),('Manual')

declare @org table
(
    orgRole varchar(10)
)
insert into @org(orgRole)
values('Coll'),('Lega')

declare @nvc_o1 nvarchar(max)=N''
     ,@nvc_o2 nvarchar(max)=N''

SET @nvc_o1 = (

SELECT
LTRIM(RTRIM(c1.cptname)) as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname)) as "orgHeader/shortName",
'Branch' as "orgRole",
(
SELECT system, extCode from
(
    Select 'a' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
    UNION ALL
    Select 'b' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
    UNION ALL
    Select 'Manual' AS 'system', LTRIM(RTRIM(c1.cptname)) AS
   'extCode'
) a
FOR XML PATH('externalCode'), TYPE, ELEMENTS,Root('externalCodes')
)
from  @cpt as c1
FOR XML PATH(''), root ('organisation')
)

SET @nvc_o2 = (
SELECT
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/orgCode",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/longName",
LTRIM(RTRIM(c1.cptname))+'.' as "orgHeader/shortName",
(
SELECT orgRole from @org
FOR XML PATH(''), TYPE, ELEMENTS
)
from  @cpt as c1
FOR XML PATH(''), root ('organisation')
)

select CAST(N'<collection>' AS NVARCHAR(MAX))
+ @nvc_o1
+ @nvc_o2
+ CAST(N'</collection>' AS NVARCHAR(MAX))

output of this:

<collection>
   <organisation>
      <orgHeader>
         <orgCode>abc</orgCode>
         <longName>abc</longName>
         <shortName>abc</shortName>
      </orgHeader>
      <orgRole>Branch</orgRole>
      <externalCodes>
         <externalCode>
            <system>a</system>
            <extCode>abc</extCode>
         </externalCode>
         <externalCode>
            <system>b</system>
            <extCode>abc</extCode>
         </externalCode>
         <externalCode>
            <system>Manual</system>
            <extCode>abc</extCode>
         </externalCode>
      </externalCodes>
   </organisation>
   <organisation>
      <orgHeader>
         <orgCode>abc.</orgCode>
         <longName>abc.</longName>
         <shortName>abc.</shortName>
      </orgHeader>
      <orgRole>Coll</orgRole>
      <orgRole>Lega</orgRole>
   </organisation>
</collection>

dbfiddle here

LATER ADDED

to export with bcp:
[test].[dbo].[test_xml] it is a stored procedure where you will place your code inside.

declare @sql nvarchar(4000)
select @sql  = N'bcp.exe "EXEC [test].[dbo].[test_xml]" queryout "d:\csv\comm.txt" -c -t, -T -S'+ @@servername
exec master..xp_cmdshell @sql


SSIS 2008 Package Configurations being ignored

You must take into account that when running by BIDS the package will take first the variable value from the config file, and only if the config file doesn't exist, it will throw a warning and the value will be taken from the package.

Now, the situation in command line is a bit different. You can have the following situations:

run the package in cmd line without any config file chosen:

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx"

if the original config file (let's name it Prod) doesn't exist in the same path defined in the metadata of the package, values from inside the package are used and you'll just receive a warning that
config file is missing;
if the original config file exists and is valid, then values from the config file will be used (inner values will be bypassed);

run the package in cmd line without any config file chosen, but with variable set in the call:

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /SET \Package.Variables[checkMe];"outside the package in cmd line"

if the original config file doesn't exist, then the value is taken from the /SET package call;
if the original config file does exist, then the value is taken from the config file and even the /SET is ignored (this is used only in the case above);

run the package in cmd line with a new config file (let's say DEV instead Prod):

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig"

if the new config (Dev) file exists, and old (Prod) doesn't, then values from it are used;
if both the Dev and Prod config file exist, then only values from Prod is used (DEV is bypassed even if specified in command line call);

run the package in cmd line with a new config file and a SET statement in the call:

dtExec /file "e:\Work\TestPackageConfiguration\TestPackageConfiguration\TestPackage.dtsx" /configFile "c:\ETL Config\TestPackage_config_Dev.dtsConfig" /SET \Package.Variables[checkMe];"outside the package in cmd line - DEV config"

if both config files exist, Prod will be used, all others ignored, even the SET;
if no config file exists, SET value will be used;

So, in short, if you want to use a new config file you'll have to rename/move the old one and call the package with /configFile.
If that's not enough and want to override even new config file, then use the /SET variable.
Or you can bypass any config file and just use /SET statements in the batch call.

Hopefully that will shed some light in your possibilities.


Is it possible to have one server acting as secondary for two primaries on AG?

As I understand your question, the answer is Yes.  1 SQL instance can act as a secondary for multiple Availability Groups.  Listeners will point to the appropriate AG.  You may have some security concerns to address if the user base of the different AGs is a different set of users...make sure they can't access each other's databases in the case that they are read only, or acting as primary on the DR(Secondary) server.  Also, all instances will need to be a part of the same WSFC.


Mysql Not Null Columns accepting null values

It actually doesn't accept NULL values it considers it as empty string. That's because you have your server in non-strict mode. That controls how MySQL handles invalid or missing values in inserts and updates. You can read more about modes here: http://dev.mysql.com/doc/refman/5.6/en/sql-mode.html#sql-mode-strict

mysql> insert into cities(state_id) values (20);
Query OK, 1 row affected, 1 warning (0.07 sec)

mysql> show warnings;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1364 | Field 'name' doesn't have a default value |
+---------+------+-------------------------------------------+

mysql> select name is null from cities where id = LAST_INSERT_ID();
+--------------+
| name is null |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> SET sql_mode = 'STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> insert into cities(state_id) values (20);
ERROR 1364 (HY000): Field 'name' doesn't have a default value


pivot a table with cell name as column name

You can use the PIVOT function to get the result but since you have multiple values that you want to convert into columns, you will first want to look at unpivoting the value and wef columns first.

You didn't specify which version of SQL Server you are using but you can use either the UNPIVOT function or you can use CROSS APPLY with VALUES or UNION ALL to convert value/wef into multiple rows.

The syntax will be similar to:

select year, code, col, val
from dbo.yourtable
cross apply
(
  select name, convert(varchar(10), value) union all
  select name+'_wef', convert(varchar(10), wef, 120)
) c (col, val);

See SQL Fiddle with Demo.  This converts your data into the format:

| YEAR | CODE |     COL |        VAL |
|------|------|---------|------------|
| 2014 | a001 |     abe |       2000 |
| 2014 | a001 | abe_wef | 2014-04-01 |
| 2014 | a001 |     def |       3000 |
| 2014 | a001 | def_wef | 2014-05-01 |
| 2014 | a002 |     abe |       2000 |
| 2014 | a002 | abe_wef | 2014-06-01 |

Now you can apply the PIVOT function:

select year, code,
  abe, abe_wef, def, def_wef, mno, mno_wef
from
(
  select year, code, col, val
  from dbo.yourtable
  cross apply
  (
    select name, convert(varchar(10), value) union all
    select name+'_wef', convert(varchar(10), wef, 120)
  ) c (col, val)
) d
pivot
(
  max(val)
  for col in (abe, abe_wef, def, def_wef, mno, mno_wef)
) piv;

See SQL Fiddle with Demo.

The problem that you might run into with the version above is it works great when you have a limited number of values because you can hard-code the query. But if you have an unknown number of values, then the solution won't work. In situations where you have an unknown number of values then you will need to use dynamic sql to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col)
                    from dbo.yourtable
                    cross apply
                    (
                      select name, 1 union all
                      select name+'_wef', 2
                    ) c (col, so)
                    group by col, so
                    order by col, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = N'SELECT year, code,' + @cols +N'
            from
            (
              select year, code, col, val
              from dbo.yourtable
              cross apply
              (
                select name, convert(varchar(10), value) union all
                select name+''_wef'', convert(varchar(10), wef, 120)
              ) c (col, val)
            ) x
            pivot
            (
                max(val)
                for col in (' + @cols + N')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo. Both versions will give a result of:

| YEAR | CODE |    ABE |    ABE_WEF |    DEF |    DEF_WEF |    MNO |    MNO_WEF |
|------|------|--------|------------|--------|------------|--------|------------|
| 2014 | a001 |   2000 | 2014-04-01 |   3000 | 2014-05-01 | (null) |     (null) |
| 2014 | a002 |   2000 | 2014-06-01 | (null) |     (null) | (null) |     (null) |
| 2014 | a003 | (null) |     (null) |   2000 | 2014-04-01 |   5000 | 2014-06-01 |


Revoke alter permissions on service account

You should be able to reset the ownership of the object by executing the following:

ALTER AUTHORIZATION ON PartyMsci TO SCHEMA OWNER;
ALTER AUTHORIZATION ON Product TO SCHEMA OWNER;
ALTER AUTHORIZATION ON SimpleIndustryPICS8 TO SCHEMA OWNER;

You can determine who the current owner is for objects in the database using this query:

SELECT ObjectName = o.name
    , ObjectOwner = CASE WHEN dp.name IS NOT NULL THEN dp.name ELSE so.name END
FROM sys.objects o
    LEFT JOIN sys.database_principals dp ON o.principal_id = dp.principal_id
    LEFT JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals so ON s.principal_id = so.principal_id
ORDER BY o.name;

The Microsoft Docs on ALTER AUTHORIZATION makes this note:

  If the target entity is not a database and the entity is being transferred to a new owner, all permissions on the target will be dropped.

As a result, you should analyze the required permissions on those objects before you change the owner via ALTER AUTHORIZATION, and re-apply appropriate permissions once you have changed the owner.