Is it possible to have xid before and after wrap around with the same timestamp?

Assuming updatedAt stores the transaction timestamp as reported by now() or CURRENT_TIMESTAMP. See:

Difference between now() and current_timestamp

Then yes, xmin behaves like a simple sequence, basically.
But you may want to look at cmin additionally. The manual:

The command identifier (starting at zero) within the inserting transaction.

Related:

How did this statement update 3 rows in Postgres?

And, subtransactions (incurring a SAVEPOINT) may very well interfere with your plans, as those advance the transaction ID (and hence the xmin of rows written after that) within the same transaction, while the transaction timestamp stays the same.
So, that's a YES to the question in the title. It is possible to have xid before and after wrap around with the same timestamp.
There are various ways subtransactions may get involved:

Calling the SQL commands manually, obviously
A PL/pgSQL block with an EXCEPTION clause
The ON_ERROR_ROLLBACK setting in Postgres
Some replication solutions involve savepoints (I think)

Related:

How to view tuples changed in a PostgreSQL transaction?

Related discussion on ppgsql-general


SQL Server Configuration Manager shows no services

You have just downloaded the Management Studio, which is nothing else as an IDE. It seems to me, that you haven't downloaded/installed the SQL Server itself.

You can download it right here. On the website, just hit the download button and check the SQL Server, which is called something like SQLEXPR_x64... or if you running a 32 bit system SQLEXPR_x86....

For those who haven't SSMS downloaded, you can check SSMS on the same download page too and it will be downloaded with the server.


Does MERGE prevent deadlocks and server blocking?

In the example above you will see that I use WITH(HOLDLOCK) to prevent
  primary key violation and deadlocks.

That is necessary and (normally) sufficient to prevent PK violations with concurrent MERGE.  Deadlocks depend on the other locks in your transaction, but if you have a simple clustered index key lock for your MERGE you should be fine.

  I use @RecordID parameter to check if record exist and based on that
  MERGE will execute Insert or Update. This field is auto incremented id
  that I pass back and forth in order to make sure I always update
  correct rows in the table.

That is quite strange and probably bad.  You should use the Primary Key to "make sure you always update the correct rows".  I don't see why you have that column at all, let alone using it in your MERGE.  Just use the PK.


You can do this using the BCP format parameter. This will create a format file of the table for you.

Based on your requirement, you can create XML/native/character formatted files.

eg: bcp db_name.schema_name.table_name format nul -T -n -f format-file.fmt


Carry over long sequence of missing values with Postgres

I would form groups with the window function count() and then take the first value for each group:

SELECT foo_label
     , first_value(foo_price) OVER (PARTITION BY foo_label, grp ORDER BY foo_date) AS fixed_foo_price
     , foo_date
FROM  (
   SELECT foo_label
        , count(foo_price) OVER (PARTITION BY foo_label ORDER BY foo_date) AS grp
        , foo_price
        , foo_date
   FROM   foo
   ) sub;

This works because count() only counts non-null values. So all rows with NULL end up in the same group as the last row with an actual value. Exactly what you need.


Having now spoken to Microsoft about this, it's a known issue in SSMS 17.1.  At least we now know!

They rightly recommended to use sys.fn_get_audit_file instead.


How to select two fields from a table using join with another table

It is messy and tedious.  You would need a UNION or OR, plus worry about NULL.

It would be better to have a separate table relating book_id and book_subject, and allow any number of rows in that table.  This has the benefit of allowing more than a fixed "2".

Counting

SELECT  book_subject,
        SUM(ct) as ct
    FROM  (
            ( SELECT  book_subject, COUNT(*) AS ct
                    FROM  books
                    GROUP BY  book_subject )
            UNION  ALL
            ( SELECT  book_subject2, COUNT(*)
                    FROM  books
                    GROUP BY  book_subject2 )
          ) AS s
    GROUP BY  book_subject;
    WHERE  book_subject IS NOT NULL

That gets the counts by id; then another JOIN is needed to get the names.

Or, if you avoid splaying an array (subjects) across columns...

I would not have a mapping table, but simply a tagging table (1:many instead of many:many)

create table subjects
(
  `book_id` int(11) NOT NULL,
  `subject_name` VARCHAR(99) NOT NULL
);

SELECT  subject_name,
        COUNT(*) as ct
    FROM  subjects
    GROUP BY  subject_name;


Are certain environment variables required for mysql login-path?

mysqldump needs -u and -p for some mysql "user".


PROBLEM NUMERO ONE

GO is the default query batch separator. It is a feature of certain client utilities (mainly SQLCMD and SSMS) and is unknown to the SQL Server engine itself. GO needs to be on a line by itself (optionally followed by only an INT value to cause repetition of that batch) as it is parsed by these client utilities to indicate where to split up what is being submitted to SQL Server.

It cannot be included in any Dynamic SQL as it is, again, unknown to SQL Server. It is also unnecessary to include in Dynamic SQL since each call to EXEC() is its own query batch.

So,

If you are generating a SQL script to exec via SSMS or SQLCMD, then simply put a newline / CRLF between COMMIT TRANSACTION; and GO
If you are generating Dynamic SQL to run each of these blocks via an EXEC(@SQL) call, then simply remove the GO.

PROBLEM NUMERO TWO

After fixing the GO situation, you will find that you still have a syntax error. It is also indicated in that you have two error messages. The problem is that you are missing the semicolon ( ; ) that needs to terminate the MERGE statement. If you try to execute after fixing the GO, you will get the following error:

  Msg 10713, Level 15, State 1, Line XXXXX
  A MERGE statement must be terminated by a semi-colon (;).

Just add a semicolon before the COMMIT TRANSACTION and the whole thing should work. As in:

values (cell,[date],valueFlag,[value]); -- <-- that guy, right there :-)


You can use set timestamp <unix_epoch> which will make now() be fixed -- always return that value. See https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_timestamp

Note that this will not affect sysdate(). To fix sysdate, you can start MySQL with the --sysdate-is-now option which will make sysdate() return the same value as now(), so in conjunction with above sysdate() will now be fixed. See
  https://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_sysdate-is-now

(People coming from the Oracle world know this parameter as fixed_date.)


Is this acceptable practice for a many to many?

I notice that your specifications have turned a bit intricate (intentionally, right?), and I consider that this is a beneficial fact since this kind of scenarios will help you broaden your perspective on relational design.

Regarding such situation, I am going to suggest you two similar methods to deal with these new conditions. In the first one I propose the use of a (super)type-subtype cluster, and in the second one I recommend two one-to-many relationships. This way you would be addressing the relationships that you specify between the entities Person (or Club) and TelephoneNumber, and between Person (or Club) and EmailAddress.

My understanding of your business rules

I will describe my suggestions within the context of your individual questions but, before I do that, you first need to know the way I understand your scenario, and I understand it as follows:

Telephone and Email are both types of ContactMeans.
A particular type of ContactMeans must be fixed for a Person (or a Club).
Once a specific type of ContactMeans has been fixed for a Person (or a Club), this Person (or Club) can be reached through one-to-many occurrences of such type of ContactMeans, and you want to make sure that each one of these occurrences belong to the same type, either Telephone or Email.

Proposed methods and answers to your individual questions

First method

  Personally i thought it was better to work with an extra table contact and have person and club link to this table and then have phone numbers link to this table also.

  But,...

  I find myself in the same situation when it comes to e-mail addresses. So, my idea was to also link them to that same contact table. Was this a good step, or should i have added a separate table for the relationship between club - email and person - e mail?

Yes, adding a separate table is a good step and, to me, the best method, but it needs some refinements. Here is where my first suggestion comes into play, you should make use of two supertype-subtype clusters.

In the first case, I would include a table called Party (which has been mentioned in my other answer). This table will serve the purpose of relating a particular Club or Person (the Party subtypes) with a given ContactMeans, Telephone or Email.

You need to store the fact that a specific Party is fixed with only one type of means of contact, so I recommend you including a table called ContactMeansType, with a PRIMARY KEY named ContactMeansTypeCode. Then, in the Party table, you should add a FOREIGN KEY pointing to ContactMeansType.ContactMeansTypeCode.

And then comes the second supertype-subtype cluster. In this case I would call your Contact table “ContactMeans” (the supertype of Telephone and Email) holding a PRIMARY KEY composed of two columns, PartyId (FOREIGN KEY referencing Party.PartyId) and ContactMeansNumber. Every ContactMeans must hold a type, so I would add a FOREIGN KEY named ContactMeansTypeCode pointing to ContactMeansType.ContactMeansTypeCode.

Then, you should establish a validation method in order to guarantee that any given occurrence of a Party (Club or Person) can only be reached through the ContactMeansType (Telephone or Email) that has being fixed for such occurrence. Involve the columns Party.ContactMeansTypeCode and ContactMeans.ContactMeansTypeCode in this effort. For instance:

Suppose that a particular Party has been fixed to be reached only by means of Telephone, then when there is an attempt to INSERT a new ContactMeans, you must make sure that the value contained in this ContactMeans.ContactMeansTypeCode is the same as the value contained in Party.ContactMeansTypeCode, if it is so, then let the INSERT go on, otherwise deny said INSERT attempt. You are using MySQL, so this content about TRIGGERS from the MySQL Reference Manual may be relevant in this purpose.

See Figure 1, which shows an IDEF1X data model depicting a logical structure for this method.

* Do not overlooke the vast power that relational keys have to offer, since they are being used to shape most of your business rules.

In this sense, e.g., it is worth noting how the PRIMARY KEY called PartyId has migrated from Party to ContactMeans, and then from ContactMeans to Telephone or Email. Said PRIMARY KEY has also migrated from Party to Club or Person (receiving the rolenames ClubId and PersonId, respectively), and then to ClubMember (PersonId being rolenamed as MemberId). Thereby, all this “chain” of tables is provided with referential integrity.

In this external document in .PDF format I give a more detailed treatment to this method.

Second method

  As i understand it, this isn't exactly a many to many relation but a many to one.

  However, phone number can belong to 2 other tables. So, should i add 2 columns to phone numbers then? 1 with a FK to person and 1 with a FK to club? Leaving one of them null when a phone number is entered?

Yes, as per the business rules that you have presented, this situation could be handled through a many-to-one (or one-to-many) relationship.

And yes, one given Telephone can belong to a Person or a Club. But no, I do not recommend the addition to the Telephone table of the two FOREIGN KEYS that make reference to Person and Club, nor I advise you leaving them NULL because, according to my personal experience, this may be a sign that the modeling stage needs to be extended.

Instead of the above, I propose a second method in which, again, you would be using the supertype table called Party. This table will “link” Club or Person with Telephone or Email.

It is necessary to add a column that represents the fact that a given Party has been fixed with a specific type of means of contact, so I would add the column Party.ContactMeansCode that makes to the PRIMARY KEY (ContactMeans.ContactMeansCode) of a table named ContactMeans. Or you could deal with this value by way of a BOOLEAN column called Party.IsFixedWithEmail or Party.IsFixedWithTelephone, also.

As you can see in Figure 2, in this option Telephone and Email are structured with a many-to-one relationship with Party, and then, through the latter, to Club or Person.

I included the column denominated IsPrimary to Telephone and Email, which should be of use to store the fact that an individual Telephone (or Email) has been established as the main instance (of its corresponding type) for contacting a particular Party. Regarding this aspect you must create a method to ensure that there could be only one instance set as primary.

Then, you need to define a procedure to validate that any particular instance of a Party (Club or Person) can only be reached through (or contacted via) the ContactMeans (Telephone or Email) that has being fixed for such instance. The column Party.ContactMeansCode is useful in achieving this goal. For example:

Say an individual Party has been fixed to be contacted only via Email (
by virtue of a specific value in the Party.ContactMeansCode column), so you have to accept only INSERTS of new Emails for such Party and, of course, reject every INSERT attempt in the Telephone table for this Party. Again, since you are using MySQL, you may have to do it via TRIGGERS.

See an IDEF1X data model illustrating a logical structure for this procedure in Figure 2.

In the same external document in .PDF format I give a more detailed treatment to this second method, as well.


Mongodb multikey index and $all in queries

First, not sure if you have altered the question or not since posted, but contrary to one of the answers, this is not doing a full collection scan, it is using an index, as evidenced by the "cursor" : "BtreeCursor in the explain output.

As for your question - you are asking about the $all operator, but I think your issue here is that you are using the $elemMatch operator incorrectly.  When you do:

$elemMatch: {
          title: 'OS',
          value: 'Android'
        }

Or similar, you are running $elemMatch against a document, not an array, which is how it is intended.  Hence the explain shows the bounds on the index as specific values of just OS and Android on properties.title and properties.value respectively rather than what you probably wanted, which I think is to only return if all the various properties are present in the array.

Try without $elemMatch:

{
  category_id: ObjectId("50bcc2f0b910a6c1936a4424"),
  properties: {
    $all: [
      {
          title: 'OS',
          value: 'Android'
      },
      {
          title: 'Display',
          value: '4.8"'
      }
    ]
  }
}


Select column values linked to exact set of values in another column

Assuming a PRIMARY KEY on (chat_id, user_id) - or a UNIQUE constraint plus NOT NULL on both columns.

To allow any number of IDs, use a 1-dimensional array parameter:

SELECT chat_id
FROM   chats_users c
WHERE  user_id = ANY ('{1,2,3}'::int[])
GROUP  BY 1
HAVING count(*) = array_length('{1,2,3}'::int[], 1)
AND    NOT EXISTS (
   SELECT 1
   FROM   chats_users
   WHERE  chat_id = c.chat_id
   AND    user_id <> ALL ('{1,2,3}'::int[])
   );

Typically, in function or prepared statements, you provide the array parameter only once. Example for prepared statement:

PREPARE foo(int[]) AS
SELECT chat_id
FROM   chats_users c
WHERE  user_id = ANY ($1)
GROUP  BY 1
HAVING count(*) = array_length($1, 1)  -- all required user_id
AND    NOT EXISTS (
   SELECT 1
   FROM   chats_users
   WHERE  chat_id = c.chat_id
   AND    user_id <> ALL ($1)  -- no additional user_id
   );

Tests:

EXECUTE foo('{1,2,3}');  -- 1
EXECUTE foo('{1,3}');    -- 2
EXECUTE foo('{2,3}');    -- 3
EXECUTE foo('{1,2}');    -- empty

db<>fiddle here
Old sqlfiddle.

That's basically the first solution by Martin in this related answer on SO (with some modifications to fit your case):

How to filter SQL results in a has-many-through relation?

There are faster solutions for a small, given number of IDs.


Recover data in a SQL Server table without backups

If you have "lost the data" because of corruption, you may be able to retrieve some of the data from nonclustered indexes. Or if the corruption is only in nonclustered indexes you may have lost nothing and can rebuild the indexes to recover the data. Search for DBCC PAGE and you may find advice from some experts on what to do.

If you have "lost the data" because someone has deleted it and you have no backup, then that data is simply gone. It doesn't exist anywhere.

I would recommend your client make an insurance claim.


Spanish characters saved incorrect in Mysql

Check and log/dump the insert/update queries - this looks to me as if the php code somehow recodes your data. The 00f1 is the hexadecimal representation of the character ñ and in the form \u00f1 it might be used as an special/escape sequence (for example in JSON).


Querying an Oracle view don't return records using some clients but it does using others

The TNS Listener running on the server hosting the database has a log file located (on Unix-type servers using Oracle recommended naming) at

/u01/app/oracle/diag/tnslsnr/$HOSTNAME/listener/trace/listener.log

The file listener.log may be named something else if you have multiple listeners.  That log file will show the connection parameters presented by users connecting to the database via the TNS Listener.  I would compare the information in the log for the two connections to see the differences.  And if that looks the same, Michael Kutz' comments would be accurate; there may be a login trigger that looks at the program connecting; if you look at the table  v$session you can see the program column might be used to restrict access.


Is it possible to use just one shared folder for log shipping?

Yes you can, I remember a shop where we had this kind of setup in that case even the copy job was disabled because there were no need to copy the files.
You just need to be sure your file retention is ok and the Share is available at all the times. You don't want your share folder go offline during a long weekend or something and your transaction log on prod gets full.


Summing Column values based on group by criteria

So to get the hours from the two timestamps as they were summed in the group by (note that tt_timeadjust is by default, in minutes) I used

SELECT SQL_CALC_FOUND_ROWS *, ROUND((SUM( TIMESTAMPDIFF(MINUTE, tbltimetracker.tt_begintime, tbltimetracker.tt_endtime ) + tbltimetracker.tt_timeadjust)) / 60, 2) AS hours FROM tbltimetracker LEFT JOIN tblcustomers on tbltimetracker.customer_id = tblcustomers.customer_id LEFT JOIN tblusers on tbltimetracker.user_id = tblusers.user_id WHERE  tbltimetracker.owner_id = ?  GROUP BY tblcustomers.customer_id tblusers.user_id LIMIT 0, 50


How to match on a longer word using MySQL Full Text Search?

Here is your original query
SELECT
    SQL_CALC_FOUND_ROWS company.*,
    MATCH(company.c_title) AGAINST('SEARCH TEXT') as nameScore,
    MATCH(category.tags) AGAINST('SEARCH TEXT') as tagScore
FROM company LEFT JOIN category ON company.c_cat_id = category.c_id
WHERE MATCH(company.c_title) AGAINST('SEARCH TEXT')
OR MATCH(category.tags) AGAINST('SEARCH TEXT')
ORDER BY nameScore DESC

You may want to try running the fulltext searching before doing the LEFT JOIN
SELECT
    SQL_CALC_FOUND_ROWS company.*,
    company.nameScore,category.tagScore
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
ORDER BY nameScore DESC;

As for the search string emlakçı, try using a wildcard character. In other words,
MATCH(tags) AGAINST ('emlak*' IN BOOLEAN MODE)

This may pick up more occurrences but should get emlak and similar strings
UPDATE 2012-07-30 18:01 EDT
I don't know your table structure. Assuming name is a field in category, try this:
SELECT
    company.name,category.name,COUNT(1) CategoryCount
FROM
    (SELECT *,WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) nameScore
    FROM company WHERE MATCH(c_title) AGAINST('SEARCH TEXT' IN BOOLEAN MODE)) company
    LEFT JOIN
    (SELECT *,MATCH(tags) AGAINST('SEARCH TEXT' IN BOOLEAN MODE) as tagScore
    FROM category WHERE MATCH(tags) AGAINST ('SEARCH TEXT' IN BOOLEAN MODE)) category
    ON company.c_cat_id = category.c_id
GROUP BY company.name,category.name;


Retrieving the value of $1, $2 etc. from a Postgres Query

If you set log_min_duration_statement = 0 in postgresql.conf and reload, all statements and the parameter values will be written to the log file.

Warning: on a busy system, logging all statements can be a bottleneck.


MySQL - Split results of count function into columns derived from a separate column

'Bug':  Since BETWEEN is inclusive, a 10:00 delivery will be in the '0900-1000' bucket.

See the [pivot] tag for discussion of how to solve your problem.  Here's a crude attempt:

SELECT  CONCAT(hr, "-", hr+1, "00") AS TimePeriod
        SUM(IF(service_type = 'SERVICE1', ct, 0)) AS SERVICE1,
        SUM(IF(service_type = 'SERVICE2', ct, 0)) AS SERVICE2,
        SUM(IF(service_type = 'SERVICE3', ct, 0)) AS SERVICE3,
        SUM(IF(service_type = 'SERVICE4', ct, 0)) AS SERVICE4
    FROM (
        SELECT  HOUR(Delivery_Time) AS hr,
                service_type,
                COUNT(*) as ct
            FROM service_data
            GROUP BY 1,2
         ) AS x

Notice that I approached the hours in a different way.

To do "outside", change CONCAT(hr, "-", hr+1, "00") AS TimePeriod to

IF ( hr BETWEEN 8 AND 20,   -- assuming hours are 08:00 to 20:59
     CONCAT(hr, "-", hr+1, "00"),
     'Outside Opening Hours' )  AS TimePeriod


Seems like you need a conditional sum based on CASE:

select member_number,
   sum(case when social_activity = 'challenges' then step_count else 0 end) as challenges,
   sum(case when social_activity = 'posts' then step_count else 0 end) as posts
from ABC
group by member_number


You could set the owner of model database as [DEVELOPMENT\d-testuser] and then any new databases that are created will automatically be owned by [DEVELOPMENT\d-testuser].

I normally tend to set the database owner to sa. Our applications are not using (should never use) sa :-) so there is no harm in setting the db owner to sa.


Managing CTE execution order under uncertainty

This should work but I'm not really sure if it's the best regarding efficiency:

WITH copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        SELECT column_a, column_b
            FROM main_table
        WHERE column_a = 1
),
main_table_deleted AS (
    DELETE
    FROM main_table
    WHERE column_a = 1
      AND NOT EXISTS (SELECT 1 FROM another_table
                      WHERE column_a = 1
                        AND column_b = main_table.column_b)

)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = 1
        EXCEPT
        SELECT column_a, column_b
            FROM main_table WHERE column_a = 1 ;

But what is wrong with the original query?

First, the (SELECT COUNT(*) ...) >= 0 is completely redundant. A count aggregate will always return a values of 0 or more so that condition is always true.
Second, there is no need at all to have any condition at all there, because all the rows from main that you want copied to the other table, you also want them tobe deleted from main. There is no reason to "check" if they have copied before you delete them . All 3 subqueries, (the 2 CTEs and the main query) will "see" the same tables with the same exact number of rows and data.
The third part is more tricky. It might seem at first glance, that no checking is needed either for the "interaction" between the 2nd (delete) and the 3rd (insert) part. Both are to the same table but if the 2nd cte is performed before the main query then all should be well.
Alas, the order of execution is not consecutive. From Postgres docs:

  Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule for SELECT in WITH: as stated in the previous section, execution of a SELECT is carried only as far as the primary query demands its output.

  The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" each others' effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

As a test, you could altering the order of the 3 sub-statements. The result will be the same:

WITH main_table_deleted AS (
    DELETE
    FROM main_table
    WHERE column_a = 1
      AND NOT EXISTS (SELECT 1 FROM another_table
                      WHERE column_a = 1
                        AND column_b = main_table.column_b)

),
 copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        SELECT column_a, column_b
            FROM main_table
        WHERE column_a = 1
)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = 1
        EXCEPT
        SELECT column_a, column_b
            FROM main_table WHERE column_a = 1 ;

The related issue is when the unique constraints are checked. I'm not 100% sure about the detail of those checks in combination with CTEs but unique constraints should be checked at the end of statements. It appears that they are also checked concurrently for each modifying cte.
(Note: This behaviour seems like a bug to be honest - unless I missed something in the documentation.)
Regarding your last question, setting isolation level to SERIALIZABLE would not have solved the issue as the whole operation is one statement, with 3 sub-statements. You could however, split the actions into 2 or 3 statements and then they would be executed one after the other. So, the 2nd would see the results of the 1st and the 3rd, the results of the first two. (If you do that, put the 2 or 3 statements inside a transaction, to isolate the operation from other executing statements.)

Another way - that is more close to your original query - would be to use the RETURNING clause to force the execution of the sub-statements in a specific order, i.e. the 3rd after the 2nd (The 1st can stay without RETURNING and executed concurently). Test in SQLFIddle-3:

WITH copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        SELECT column_a, column_b
            FROM main_table
        WHERE column_a = 1
),
main_table_deleted AS (
    DELETE FROM main_table WHERE column_a = 1
        RETURNING *
)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = 1
        EXCEPT
        (TABLE main_table_deleted EXCEPT TABLE another_table) ;

or slightly improved by doing the delete (2nd) cte first and then using its RETURNIING output in both the other two:

WITH main_table_deleted AS (
    DELETE FROM main_table WHERE column_a = 1
        RETURNING *
),
copy_to_other_table AS (
    INSERT INTO other_table (column_a, column_b)
        TABLE  main_table_deleted
)
INSERT INTO main_table (column_a, column_b)
        SELECT column_a, column_b
            FROM another_table WHERE column_a = 1
        EXCEPT
        (TABLE main_table_deleted EXCEPT TABLE another_table) ;


AG listener with MultiSubnetfailover True on Named Instance

As HandyD stated - the official doc does not mention issues with named instances. I also have not experienced any issues with AO multi-subnet failover with named instances.
The SQL Shack article to which you linked states: "It does not support SQL named instances. We can connect named SQL instances using a SQL listener" (emphasis added).  I think the confusion is caused by poor choice of words.  I suspect what the author meant to say is that in order for multi-subnet failover to work, a listener must be used to connect, using the instance names to connect will not work.


Install SQL Server 2012 missing vs_Setup.msi from Visual Studio 2010

In my case, I had Visual Studio 2010 Enterprise on my system when I initiated the setup for SQL Server 2012 Standard. After the SQL 2012 got underway, it stopped and prompted for the VS 2010 setup.msi. At that point, I ejected the SQL Server disc and inserted the VS 2010 setup disc. Then I used the dialog presented by the SQL Server 2012 setup program to browse to the root of the VS 2010 DVD, selected the msi there, and opened it. The SQL 2012 setup continued and when I saw that it was again installing SQL components (rather than VS 2010 components), I ejected the VS 2010 disc (which caused the SQL 2012 setup program to display an error that there was no disc in the drive), placed the SQL 2012 setup disc back into the drive, then clicked the Try Again button. At that point, SQL Server 2012 resumed and after some time it completed successfully.


errno: 13 should be enough of a clue here. 13 is generally permission denied. Check the .so file in your plugindir. It will likely have privs that will prevent the user running MySQL from opening it.


Modeling a graph of different node types and edge types using SQL Graph

How do I reconstruct the graph for this particular project?

To send data from SQL Server to the client, you use TDS, which means one result set per table "shape", or use JSON/XML encoded in a unicode string.

So yes, if you needed all the details for each node type, you would have one query per node type.  You can join the different tables together into fewer queries (as in EF Eager Loading), but that doesn't really improve the efficiency.


SSIS data flow task failed for no reason

Think about how the reference and input data sets are configured.
The reference data set (smallest set) should be the only cached data set, while the input rows(i.e. the 10M) pass through that execution tree's buffer.

Also, running parallel lookups on different subsets of the data and aggregating all outputs with a UNION ALL at the end might yield better performance, but test it.

Lasty, look for any block transformations (i.e. Sort, Aggregates, etc.) before or after the lookup.

Getting Optimal Performance with Integration Services Lookups

Integration Services: Performance Tuning Techniques


Masking or substring data while replicating data

Yes, you can use custom replication stored procedures to do this.  Custom replication stored procedures are especially useful when an application requires custom logic such as this and can allow the data to be transformed in-flight to Subscribers.

Have a look at the sections Default and custom stored procedures and Considerations for Using Custom Stored Procedures in Specify How Changes Are Propagated for Transactional Articles, and the section To generate and use custom stored procedures in Set the Propagation Method for Data Changes to Transactional Articles.


Why can't I persist binary(4) computed column?

I'm not sure what "http://www.sql-server-helper.com/" is, but this is from the current, official documentation:

The following built-in functions from other categories are always nondeterministic.
...
PARSENAME

Side note: it looks like PARSENAME was deterministic, at least on SQL Server 2005 (thanks for that link, jpa).  So perhaps that other site just has outdated information on it.
You would need to re-implement this function using only deterministic functions (for instance, SUBSTRING and CHARINDEX).
Just to show that it can be deterministic (note that this implementation doesn't deal with general IP addresses, it's just an example):
CREATE OR ALTER FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
GO

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fnBinaryIPv4'), 'IsDeterministic') AS IsDeterministic;
GO

CREATE OR ALTER FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @bin AS BINARY(4)

    SELECT @bin = CAST( CAST( SUBSTRING( @ip, 0, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( SUBSTRING( @ip, 4, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( SUBSTRING( @ip, 8, 3 ) AS INTEGER) AS BINARY(1))
                + CAST( CAST( SUBSTRING( @ip, 12, 3 ) AS INTEGER) AS BINARY(1))

    RETURN @bin
END
GO

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fnBinaryIPv4'), 'IsDeterministic') AS IsDeterministic;
GO


SQL Server 2005, non-yielding scheduler Spinloop on Snapshot

The default is the problem, as a rogue process would utilize all the available threads and will lead to thread starvation. Better is to adjust the maxdop setting as described in
What is a good, repeatable way to calculate MAXDOP on SQL Server?

Also, let the rollback complete as if you restart sql server when a rollback is in progress, it will redo everything that it has already done when the service is restarted.

A similar case can be found Need to understand parallel query execution error. This has more details that I put in and will explain you better.


Ola Hallengren Index Optimise Script not Rebuilding Indexes

Your index only has 679 pages. Ola's solution is set to ignore indexes with less than 1000 pages (see the @PageCountLevel parameter). You can override that so that it cares about indexes with fewer than 1000 pages, but why? Wasted effort IMHO.

I would stop worrying about small tables like this - let Ola's solution do its job, and worry about fragmentation when you can actually prove it is causing a tangible performance problem for a specific index. "Fragmentation is high" is not a problem on its own.


A FD (functional dependency) X -> Z is transitive when there exists a Y such that X -> Y AND Y -> Z AND NOT Y -> X AND NOT Y = Z. (X, Y & Z are attribute sets.)
There can be lots of X, Y & Zs where X -> Y AND Y -> Z but X -> Z is not transitive because Y -> X. Eg this happens whenever X and Y are CKs (candidate keys). (Because CKs functionally determine all attributes--each determines the other and Z.) And there can be lots of transitive FDs. Eg this happens whenever there is a FD whose attributes are non-prime. (Because a CK determines that FD's determinant which itself doesn't determine the CK--if it did then it would be a CK, which with non-prime attributes it isn't.) The FDs among all those ones that are important for normalization are transitive FDs of non-prime attributes on CKs when a relation is in 2NF. Because it is always possible to losslessly decompose such a relation to components free of them, ie in 3NF.


Merge Replication: Where are rowguids of conflict losers stored?

Straight from Advanced Merge Replication Conflict Detection and Resolution:

The Conflict Viewer displays information from three system tables:

Replication creates a conflict table for each table in a merge
article, with a name in the form
MSmerge_conflict__.
Conflict tables have the same structure as the tables on which they
are based. A row in one of these tables consists of the losing
version of a conflict row (the winning version of the row is in the
actual user table).
The MSmerge_conflicts_info table provides information about each
conflict, including the conflict type.
The sysmergearticles table identifies which user tables have conflict
tables and provides information about the conflict tables.

This means that if you find a record in the MSmerge_conflict_[PublicationName]_[ArticleName] table, it is the "loser".  The record that you find in the actual table is the "winner".

Hope this helps,

Matt


Large table and poor performance - what to do next?

If you were using Enterprise Edition, I would recommend partitioning. Separate files on separate logical drives is unlikely to provide much help, since the data is all going through the same controller and writing to the same underlying disk (and without partitioning, you don't have much control over proportional fill, meaning many queries will likely still have to hit several or all files anyway).

Since you're not using Enterprise Edition, one alternative would be to split the data into multiple tables, and then have views that combine them. Assuming the older data no longer gets updated, you could even put it on read-only filegroups, which will alleviate some resource contention. You can make the query logic as complicated and picky as you want; for example, the simplest solution would be to just issue all queries against the view, but your data access layer or stored procedure logic could - based on the date range parameters - decide which table(s) to access at runtime. You can even use filtered indexes so that certain views will pull a very limited subset of data even from the subset that exists in its own table. This isn't necessarily an easy button but I can elaborate on some of these details if you think you may be interested in putting in some elbow grease to implement something.

The cheapest fix, of course, would be to throw hardware at the problem: specifically, such that more of your database will fit into memory. RAM is cheap and assuming your box supports it I would say that an upgrade from 22 GB (what an odd number?) to 128 64 GB will go a long way. (And if the OS is Standard also, even 32 GB might help, but not as much.) While I'd love to see an actual execution plan and statistics I/O metrics for a query that takes half an hour to return from an SSD, if a larger portion of the data is in memory it will be that much faster.


How to turn off identity for a database column of a table

If I understand you correctly, try this one -

SET IDENTITY_INSERT <your_table> ON

INSERT INTO ... (ID, ...)

SET IDENTITY_INSERT <your_table> OFF


What are the pros and cons of using my customer codes as a primary key?

Pros - It is the natural key, it makes sense and it will likely be searched on, I presume?

Cons - The default behavior (which is totally changeable) is for a primary key to be the clustered index. An alphanumeric doesn't make the best candidate because inserts can cause page splits because they aren't set on an ever increasing value like an identity column. The Int identity column will take less space (4bytes) compared to the character data (40+bytes for the unicode) . This makes your other indexes larger since the clustered key is part of them. If you ever change how you identify your customers and make customer codes, this all breaks - going with a surrogate insulates you from those type of changes.

In this situation, I tend to optimize for the insert performance and go with an identity column more often than not for the clustered key and primary key. I really like integer clustered indexes. (Now I know your question was not about clustered index, it was about primary key... You could still choose some other column to be the clustered index and make this your primary key, you could also put a unique constraint on this and treat it as a natural key but not make it your primary key).

I would at the very least index this with a unique constraint and treat it like a natural key. I just don't know if you really need to make it your primary key.

Kimberly Tripp is a trusted resource who has a lot to say about primary keys and (more so) clustered keys on her blog - https://www.sqlskills.com/blogs/kimberly/guids-as-primary-keys-andor-the-clustering-key/

This is all just my opinion - YMMV.


How to UPDATE by COUNT from JOINed tables

I did manage to do this through SQL. However, I think that this is a task that is far better better suited for TRIGGERs - i.e. if a record insertion/deletion is made on the project_map table, then a TRIGGER should fire adding +/- 1 to the relevant company's project field. I gave this a +1 because it was trickier than it seems initially.

Giving the DDL was good, but it would have been even better if you had also provided DML (which I had to create myself for testing). I've put my DDL + DML at the bottom of this post.

The SQL is:

UPDATE companies c
JOIN
(
  SELECT cc.company_id AS cid, SUM(cc.pcount) AS pcount
  FROM
  (
    SELECT e.company_id, ep.eid, ep.pcount
    FROM employees e
    INNER JOIN
    (
      SELECT e.employee_id AS eid, COUNT(p.employee_id) AS pcount
      FROM project_map p
      INNER JOIN
        employees e
        ON p.employee_id = e.employee_id
      GROUP BY eid
      ORDER BY eid
    ) AS ep
    ON e.employee_id = ep.eid
  ) AS cc
  GROUP BY cid
  ORDER BY cid
) AS b
ON c.company_id = b.cid
SET c.projects = b.pcount;

The many derived tables in the above query are not necessary but they can serve as an illustration of the step by step process of how the query was developed. The actual query can be as simple as this:

UPDATE companies c
JOIN
(
  SELECT e.company_id AS cid, COUNT(p.employee_id) AS pcount
  FROM project_map p
  INNER JOIN
    employees e
    ON p.employee_id = e.employee_id
  GROUP BY cid
) AS b
ON c.company_id = b.cid
SET c.projects = b.pcount;

And the result in either case is:

company_id, company,  projects
1,          companyA,        4
2,          companyB,        4
3,          companyC,        1
4,          companyD,        0

It is relatively easy to see from the DML (below) that this is the correct result.

-------------------- DDL and DML ----------------------

DML

This is a minimal "implementation" - of course, one would have to create PRIMARY KEYs, FOREIGN KEYs, NOT NULLs, UNIQUE KEYs and just plain old KEYs.

CREATE TABLE companies
(
company_id INTEGER,
company VARCHAR(255),
projects INTEGER
);

CREATE TABLE employees
(
employee_id INTEGER,
company_id INTEGER,
name VARCHAR(255)
);

CREATE TABLE project_map
(
employee_id INTEGER,
project_id INTEGER
);

And the DML for the test data:

INSERT INTO  companies (company_id, company, projects) VALUES (1, 'companyA', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (2, 'companyB', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (3, 'companyC', 0);
INSERT INTO  companies (company_id, company, projects) VALUES (4, 'companyD', 0);

INSERT INTO employees (employee_id, company_id, name) VALUES (1, 1, 'emp1_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (2, 1, 'emp1_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (3, 2, 'emp2_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (4, 2, 'emp2_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (5, 3, 'emp3_1');
INSERT INTO employees (employee_id, company_id, name) VALUES (6, 3, 'emp3_2');
INSERT INTO employees (employee_id, company_id, name) VALUES (7, 5, 'emp5_1');

INSERT INTO project_map VALUES(1, 15);
INSERT INTO project_map VALUES(1, 16);
INSERT INTO project_map VALUES(1, 17);
INSERT INTO project_map VALUES(2, 26);
INSERT INTO project_map VALUES(3, 35);
INSERT INTO project_map VALUES(3, 36);
INSERT INTO project_map VALUES(3, 37);
INSERT INTO project_map VALUES(3, 38);
INSERT INTO project_map VALUES(5, 55);


Error with Oracle trigger, invalid identifier

HOLIDAY_RESERVATION, which is aliased as R in your UPDATE statement , is not in the FROM clause in the SELECT query.


Most efficient way to filter on two columns in WHERE clause?

Here is the FROM (VALUES... version, with an EXISTS thrown in to get some speedy search shortcuts happening:

SELECT *
FROM tbltrainerinfo
WHERE EXISTS(
    SELECT *
    FROM (VALUES
        ('Arpay','Belly Joy')
        ,('Bebelyn','Abulog')
        ,('Cabrera','Virgie')
        ,('Christopher','Sajonia')
        ,('Edilberto','Galaura')
        ,('Embanecido','Efren')
        ,('Gertrulyn','Natonton')
        ,('Gina','Gindap')
        ,('Gindap','Gina')
        ,('Jeffrey','Ladiero')
        ,('JIMENEZ','JUNPORT')
        ,('Jocelyn','Paloma')
        ,('Ladera','Chared')
        ,('Lago','Michelle Joy')
        ,('Maratima','Untal')
        ,('Marciano','Iloreta')
        ,('Marianita','Gemina')
        ,('Pala','Sittie Norjanna')
        ,('Untal','Maratima')
        ) AS List(LName, FName)
    WHERE LName = lastName
      AND FName = firstname
    )


How to return a boolean response for a unique and new entry

If I understand your question correctly, you want to select a boolean value based on whether another query (that determines if there are any new entries in the table based on some new flag) selects any rows.

Someone could probably do this in a more optimised way, but it could be achieved by doing this:

SELECT CASE
           WHEN COUNT(y.class) > 0 THEN
               1
           ELSE
               0
       END
FROM (SELECT class
    FROM Features
    WHERE account_username = 'someusername'
    GROUP BY class) y
    LEFT JOIN
    (
        SELECT class
        FROM Features
        WHERE new = 0 AND account_username = 'someusername'
        GROUP BY class
    ) x
        ON x.class = y.class
WHERE x.class IS NULL

This selects 1 if the inner query selects any rows, otherwise 0. The inner query selects the rows that have a new flag associated with them by performing a left join on all the possible classes and all the classes where the flag is 0 where the x.class is null.

To run it on a single username, create a stored procedure and parameterise the username column.

Hope this helps!


How to prevent PostgreSQL from automatically rounding numeric types?

I want this to be an error

You could use a check constraint that validates the scale of the value, rather than the definition of the data type:
CREATE TABLE ttable
(
  tcol numeric,
  constraint check_tcol_scale check (scale(tcol) <= 4)
);

Note also that the column itself needs to be redefined as just numeric, rather than numeric(19,4).
Then the following:
insert into ttable values (123.45678);

will result in

ERROR: new row for relation "ttable" violates check constraint "check_tcol_scale"
   Detail: Failing row contains (123.45678)

But
insert into ttable values (123.4567);

will succeed.
Check out this fiddle to see this solution in action.


How to grant set the replication attribute on RDS PostgreSQL?

We hit this head on too, and after much frustration discovered:

CREATE ROLE some-user WITH LOGIN PASSWORD 'long-password';
grant rds_replication to user;

This seems to be working for logical replication.

UPDATE: Note that the user on the publisher needs to have SELECT access too!


How to create the multiple sum query with joins

I believe you can avoid joining the same table 3 times by

SELECT
    i.invoice_id,
    SUM(CASE
        WHEN a.type_id IN (1,2,3) THEN a.amount
        ELSE 0 END) AS sum1,
    SUM(CASE
        WHEN a.type_id IN (4,5,6) THEN a.amount
        ELSE 0 END) AS sum2,
    SUM(CASE
        WHEN a.type_id IN (7,8,9) THEN a.amount
        ELSE 0 END) AS sum3
FROM
    invoices AS i
LEFT JOIN
    amounts AS a ON a.invoice_id = i.invoice_id
                AND a.type_id IN (1,2,3,4,5,6,7,8,9)
GROUP BY
    i.invoice_id ;

Note: depends on your requirements, LEFT JOIN can be replaced with INNER (then you won't have invoices without amounts in resultset)


Copying (hundreds of) tables from one server to another (with SSMS)

Here's a quick & dirty approach that only needs a linked server, in each direction, with sufficient privileges, collation compatibility, and data access enabled. You run this on the source linked server to generate the dynamic SQL that will be executed on the destination linked server.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'SELECT * INTO [database].dbo.' + QUOTENAME(name)
  + N' FROM [source_linked_server].[database].dbo.' + QUOTENAME(name) + N';'
FROM sys.tables
WHERE name LIKE N'Cart[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]';

PRINT @sql; -- this will only print 8K, enough to spot check
--EXEC [destination_linked_server].master.sys.sp_executesql @sql;


Cannot take backup into NAS storage at specific time

Operating system error 5(Access is denied.)

This error is somewhat cryptic, but it is helpful. This error is coming from Windows when it tries to write to file. Windows then passes the error message along back to SQL Server, who dutifully tells you exactly what it was told--unfortunately without a better explanation.

"Operating system error 5" generally means that you have access to that location, but not write access. For example, if your SQL Agent service account is only granted read permission on a folder, and you try to write backups there, you would see this message.

Except in your case, permissions work most of the time.

Your backups only fail late at night, then recover and start working again. That means it's probably not a "normal" permissions issue. Your target folder is on a NAS, so probably there is a scheduled process on the NAS that is impacting your backups.

Given the timing, I might suspect that your NAS is being backed up at that time, and doing something that is affecting your ability to write there. A lot of commercial file system backup software will do something to momentarily pause writes so that it can have a consistent image to back up, then quickly snapshot the storage and begin allowing writes again. It's plausible that you are hitting a backup process like this, or similar.

If that's the case, then I'd first evaluate adjusting the backup schedule to minimize impact. Depending on your RPO/RTO requirements, it may be perfectly ok to have one transaction log backup fail, so long as the next execution runs ok. If that's the case, just document the reason, and start ignoring the 22:45 errors.


Single user mode while restoring database

You would only need to put the SQL Server instances in single user mode if you were restoring the master database.  For user databases, you have to make sure there are no active connections to the database you're restoring.  You'd either have to determine and kill any active SPID's (which would not require the database to be in single user mode) or actually put the database in single user mode using one of the following (referencing a post by Greg Robidoux Getting exclusive access to restore SQL Server databases:

ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
OR
ALTER DATABASE [Test4] SET SINGLE_USER WITH ROLLBACK AFTER 30
OR
ALTER DATABASE [Test4] SET SINGLE_USER WITH NO_WAIT

WITH ROLLBACK IMMEDIATE - this option doesn't wait for transactions
to complete it just begins rolling back all open transactions
WITH ROLLBACK AFTER nnn - this option will rollback all open
transactions after waiting nnn seconds for the open transactions to
complete. In our example we are specifying that the process should
wait 30 seconds before rolling back any open transactions.
WITH NO_WAIT - this option will only set the database to single user
mode if all transactions have been completed. It waits for a
specified period of time and if the transactions are not complete the
process will fail. This is the cleanest approach, because it doesn't
rollback any transactions, but it will not always work if there are
open transactions.

  Once the database has been put in single user mode, you have exclusive
  access to the database and can then do the restore without a problem.

  Note: when using the ROLLBACK option you are rolling back any open
  transactions that still exist for the database. The rollback process
  should work without issue, but if you have very long running
  transactions the rollback process could take a long time, so be aware
  of what is running on your systems. For test and development systems
  since you are doing a restore you don't care about the transactions
  anyway, so rolling things back should not be an issue, but you still
  need to be aware that long running transactions may take some time to
  rollback.


How to query data in MySQL and be able to save in SQL Server? (Python)

There is an option in MSSQL called linked server, where you can connect to MySQL server by using MSSQL queries. So you can query data from MSSQL linked server (MySQL data) and insert data into MSSQL.

I hope this works for your query. Give a try.


Having clause can only exclude rows which exist in data - in your case only possibility to get count(*)=0 means having no matching rows - so nothing to return in the first place.

You probably want to count existing matches in the left join - so COUNT(m.MATCH_ID) will only count rows where match_id is not null.

Edit: One more thing I noticed - you have lot of conditions in WHERE which touch left-joined tables - this will actually cancell the effect of left join, because ie "m.LEAGUE_YEAR = 1" is false when no matchng row exists and so league_year is null - you want to move those conditions to ON as "join-cond AND additionalCont1 AND ..." for each table (m and sqm in your case) - then it will get the chance to actually give you some of those players without a match.


I would use a CTE here to provide row numbers to your table data (partitioned by the empName). That way you can update the cte, which has the advantage of actually updating the base table.

No bear in mind that because you have no ordering column there is no guarantee of which row would actually get updated (you would want more restrictive sorting conditions around this), however for a base example you can use the following.

DECLARE @Table1 TABLE
    (
      empName VARCHAR(100) ,
      fieldtoupdate VARCHAR(10)
    );

INSERT  INTO @Table1
        ( empName )
VALUES  ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Blue' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Red' ),
        ( 'Green' ),
        ( 'Green' ),
        ( 'Green' );
WITH    cteTop1Table ( RowNum, empName, fieldtoupdate )
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY empName ORDER BY empName ) AS RowNum ,
                        empName ,
                        fieldtoupdate
               FROM     @Table1
             )
    UPDATE  cteTop1Table
    SET     fieldtoupdate = 'Top 1'
    WHERE   RowNum = 1;

SELECT  *
FROM    @Table1;

empName fieldtoupdate
Blue    Top 1
Blue    NULL
Blue    NULL
Blue    NULL
Blue    NULL
Blue    NULL
Red     Top 1
Red     NULL
Red     NULL
Red     NULL
Green   Top 1
Green   NULL
Green   NULL


The certificate chain was issued by an authority that is not trusted

On connection pop up click on options

Then make sure the "Trust server certificate" is checked


MySql Insert signal if where clause fails

You can try using mysqli_affected_rows to find how many rows are inserted from the last query and generate your own error in case no rows are inserted.


The lazy writer process is closely related to checkpoints, so I'll start with that first

Best SQL Server performance is achieved when pages are read from the buffer. To provide enough free space in the buffer, pages are moved from the buffer to disk. These pages are usually moved at a check point, which can be:

automatic (occurs automatically to meet the recovery interval request)
indirect (occurs automatically to meet the database target recovery time)
manual (occurs when the CHECKPOINT command is executed)
internal (occurs along with some server-level operations, such as backup creation)

At a checkpoint, all dirty pages are flushed to disk and the page in the buffer cache is marked for overwriting

  “For performance reasons, the Database Engine performs modifications
  to database pages in memory—in the buffer cache—and does not write
  these pages to disk after every change. Rather, the Database Engine
  periodically issues a checkpoint on each database. A checkpoint writes
  the current in-memory modified pages (known as dirty pages) and
  transaction log information from memory to disk and, also, records
  information about the transaction log.”

Database Checkpoints (SQL Server)

The lazy writer process periodically checks the available free space in the buffer cache between two checkpoints. If a dirty data page (a page read and/or modified) in the buffer hasn’t been used for a while, the lazy writer flushes it to disk and then marks as free in the buffer cache

If SQL Server needs more memory and the buffer cache size is below the value set as the Maximum server memory parameter for the SQL Server instance, the lazy writer will take more memory

If SQL Server is under memory pressure, the lazy writer will be busy trying to free enough internal memory pages and will be flushing the pages extensively. The intensive lazy writer activity affects other resources by causing additional physical disk I/O activity and using more CPU resources

To be sure that the server is under memory pressure, check Page Life Expectancy. If its value is low (below 300 seconds), this is a clear indication of memory pressure. Check the Free List Stalls/sec value as well. If above 2, consider adding memory to the server


Best way to backup & truncate transaction logs on a schedule

Only overnight SSIS is making writes, daytime is all reads - I only need daily recovery.

You should choose your recovery model based on your business needs :

How much data business can loose and at the same time survive ?

Based on the above answer, you should carefully choose your database recovery model.

In simple terms (not discussing bulk logged recovery model),

A full recovery model allows log backups that allows point-in-time recovery.

Log truncation can occur when you take transaction log backups i.e. Log file space will be reused after each log backup and wont bloat !

A Simple recovery model only allows you to take FULL backups. Point-in-time recovery is not possible.

Log truncation can only occur when  a checkpoint occurs (manually or automatic) i.e since you do regular full backups, you do not have to worry about transaction log as CHECKPOINT will take care of reusing the inactive portion of the log file.

Remember that log truncation is NOT physical reduction in size of transaction log file It means that the inactive portion of the transaction log file is marked as reusable.

Hence you should properly presize your transaction log file (and data files). Growing the log file will kick in autogrowth events (if your database is set to autogrow as a last resort). Check my answer - Autogrowth - Percentage Use?

I would highly suggest you to ditch maintenance plans and implement [a smart maintenance solution - that is easy, flexible and follows best practices] - 5. - Ola's backup solution (and Index maintenance solution as well).

lets address your questions :

  Will backing up the transaction logs like this automatically truncate them, or there's something else I need to do?

Please do not append backups or set them to expire. They create a big mess. Use INIT and take separate log backups with datetime stamp. Easy to maintain. Use Ola's backup solution for that. The solution is flexible to delete old backups as well.

  Is it okay to run data and transaction log backups concurrently? If not, then what's a proper way of doing this?

A full backup has no effect on a T-log backup. A full backup contains only enough transaction log necessary so that in the event of a restore, the database can be transactionally consistent to the time at which the data reading portion of the full backup completed. Check - how much transaction log a full backup includes ?

Also, a log backup during a full backup will not truncate the transaction log. A (couple of) log backup/s after the full backup finishes will truncate the log.

  The backup files are being picked up overnight by another process that grabs all the files on the server and stores them elsewhere - would it be a good idea to expire the backup set after 2 days? Do I need to make them expire at all?

  Cleanup tasks respectively remove "old" .bak and .trn files under the subfolders of G:\Backups. Does that make sense?
  Would it be better to do this in SSIS, so I can fail my ETL if/when the backups fail? Or should my ETL process even care?

For above both, use Ola's backup maintenance solution. It will take care of deleting old files.


An index doesn't need to be unique to be efficient, and in fact I've rarely found making my indexes unique made much of a difference in terms of performance, in my experience. (It does in certain cases, data size dependent, but not enough to be measurably important to me so far.) On that note, my experience is within Microsoft SQL Server, so knowing which database system, version, and implementation (when applicable) would be helpful here.
That said, in most modern Relational Database Management Systems (RDBMS), the standard index is a B-Tree index. That means your data is stored in a B-Tree logical data structure which is much more efficient for searching than the entire unindexed Table itself. It doesn't matter if your data is "almost" unique vs perfectly unique. If your data was completely redundant (every value for the column(s) you anticipate indexing are exactly the same) then indexing probably won't help you much, but in your case a regular B-Tree index sounds like it would work very good.


AWS RDS SQL Server extremely slow compared to EC2 with SQL Server

It seems that the tool I used to migrate my DB from EC2 to RDS added a bunch of weird indexes which caused the performance of the DB to tank.  Once I removed any and all elements of the DB with 'azure' in their name my performance returned to normal!  The tool was extremely useful but if anyone happens upon this question know that you have to remove anything Azure created other than your original data.


Innodb Isolation levels and locking performance

In my opinion...  If you are tweaking isolation levels for performance then you have inefficient/inadequate code/schema/indexes and should focus on that instead.

Think about it...  You can perform hundreds of 'simple' queries per second from dozens of connections.  So, if your queries are 'simple' and you don't have too many of them, then they will rarely collide -- hence, the isolation mode won't matter!

select i2 from t where i1 =60; -- The obvious index is INDEX(i1).  Better would be PRIMARY KEY(i1), but that may not be appropriate.  If i1 were not the PK, then the best is a "covering" index:  INDEX(i1,i2) -- the query is performed in the index, not touching the data.

You will be hard pressed to run enough of the queries you mentioned fast enough from enough connections to have them stumble over each other.  In fact, you are more likely to be blocked by the "Query cache".  For serious production work, that is usually turned completely off:  query_cache_size = 0 and query_cache_type = 0.

Back to my first point...  Changing the Isolation Level changes the results that you might get from queries!  So, doing it for performance reasons could mess with the application output.


How does '+' operator behave in following statement?

It is parsed as a unary plus, and ignored.

The following was given in answer to Connect item 718176 on the subject (Connect has since been retired, and no archive of this page is available):

    After some investigation, this behavior is by design since + is an unary operator. So the parser accepts "+ , and the '+' is simply ignored in this case.

    Changing this behavior has lot of backward compatibility implications so we don't intend to change it & the fix will introduce unnecessary changes for application code.

(Community Wiki answer generated from a comment on the question by Martin Smith)


Strange MySQL replication error 1146 (Table doesn't exist)

Not too sure why still, but this problem disappeared after some of the troubleshooting steps I took to hunt down the culprit. Maybe it was the open files limit that caused the errors while opening the MyISAM tables, perhaps because of the high activity of other services running on the slave2 server, like Rackspace's cloud backup... But, whichever the case, replication has been running smoothly on slave2 off of my master server for a number of weeks already after loading it up once more with a fresh data snapshot taken from slave1 in the same fashion as described in my original question.

So, although unfortunately I can't provide a clear answer to the problem, I can definitely say it looks to have been solved. So I'm closing this post for the time being.


Full text index does not honour deleted words - They are still indexed

In real world environments the situations where a keyword is encountered and then removed and will never be encountered again is quite low.

The process for updating a record in the full text index process probably removes all previous references to the record then updates all new references. As such when the references to a keyword are removed it does not know that keyword will no longer exist because it has not yet indexed the new document.

It takes a trivial but non-zero amount of time to insert new keywords (locks and similar) and it would affect index update performance to remove and reinsert these keywords all the time (especially since in most cases there are seldom too many orphaned words). But most of all I suspect the biggest reason is the impact such a change would have on index fragmentation.

There may be a way to remove the keyword from the index when it no longer is referenced but in the real world you're not going to see any noticeable performance decrease by keeping these orphaned keywords around. The only situation where you would see a significant % of keywords appearing and going away on record updates is where you're populating content with lots of random content - in such a situation your index performance is going to be very bad anyway because your keywords will cover so very small % of all documents.


Sql service account password change for environment on Availability groups 2012

I would do it in this order:

Change on Async Replica and restart SQL.

Make sure everything is still synchronizing after restart

Change Sync Replica and restart.

Make sure everything is still synchronizing after restart

Failover to Sync Replica
Change on former Primary (Current Sync Replica), and restart.

Make sure everything is still synchronizing after restart

You can then fail back to the old primary if need be, or leave on the Sync Replica if all things are equal.


Is MySQL/MariaDB getting a better IOPS deal than Postgres in RDS?

As I read that page, "Provisioned IOPS works with an I/O request size of 32 KB. An I/O request smaller than 32 KB is handled as one I/O; for example, 1000 16 KB I/O requests are treated the same as 1000 32 KB requests.", your conclusion is correct.

MySQL has an additional advantage: innodb_flush_neighbors.  If on, it will try to combine adjacent blocks together, thereby cutting down the number of IOPs.  (This is designed to cut down on head seeks on hard drives, but counterproductive on SSDs since there is no seek time.)

(I don't know if Postgres has a similar thing.)


MySQL Semi-synchronous replication with Multi-Master

The idea of semi-synchronous is to wait for acknowledgement from at least one  slave and since you would be using master-master so you might have to install both plugins i.e. for master & slave on both nodes. This is because when one master would write the other would behave like it's slave and same goes for the other case. Hope it helps


MySQL: How select the count of two queries while selecting another field too?

In your query you are adding the results (SELECT COUNT(*)....) from two queries together. There is no reference to a video_title in this count. I added this reference to the video_title which makes sure the count is done for every video_title.

The left join makes sure the every video is selected, and that's why IFNULL(..) is added. If the link to a subquery fails, the count is NULL, the function IFNULL replaces that with 0.

SELECT
   v.video_title,
   ISNULL(c1.count,0)+ISNULL(c2.count,0) as count
FROM videos v
LEFT JOIN
   (
    SELECT  video_title,CEILING(COUNT(*)/4) as count FROM videos
        INNER JOIN wtb_order_archives
        ON wtb_order_archives.object_name = videos.video_name
    WHERE wtb_order_archives.is_test=0
        and wtb_order_archives.bypass=0
        and videos.is_movie=1
        and videos.owner = 'user'
        and wtb_order_archives.is_paid=1
        and wtb_order_archives.duration =168
        and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
    GROUP BY video_title
   )  c1 on c1.video_title = v.video_title
LEFT JOIN
   (
    SELECT  video_title, COUNT(*) as count
    FROM videos
        INNER JOIN wtb_order_archives
        ON wtb_order_archives.object_name = videos.video_name
    WHERE wtb_order_archives.is_test=0
        and wtb_order_archives.bypass=0
        and videos.is_movie=1
        and videos.owner = 'user'
        and wtb_order_archives.is_paid=1
        and wtb_order_archives.duration =720
        and DATE(wtb_order_archives.paid_on) between '2019-01-01' AND '2019-05-01'
    GROUP BY video_title
   ) c2 on c2.video_title = v.video_title
ORDER BY count ASC

Because no input data was given, this is only a guess on what the desired query should look like


When you create a foreign key between two tables the data type of the columns that are taking part in the relationship must have the same data type. In the example you have posted above it is failing because the Categories table defines CatID as a BIGINT wheras in the Projects table you have defined it as a INT. Changing the data type of CatID in the Projects table to BIGINT should resolve your problem.

I hope this helps you.


INSERT INTO SELECT causing data to double

Might be caused by a trigger on the target table


ERROR 1329 (02000): No data - zero rows fetched, selected, or processed

That error is not about your RETURN; it's this:

fetch cur1 into pid;

When the cursor runs out of data, this error is thrown.  This could be because the SELECT query declared in the cursor didn't find any rows, or because it did find rows but as you iterated through it, you read past the last row.  If nothing happened when the cursor ran out of data, how would you ever break out of read_loop?

You need to trap the error with a handler, which will prevent execution of the procedure from terminating, and set a variable you can use to break out of the loop.

At the top, with the other variables:

DECLARE done TINYINT DEFAULT 0;

Then, after declaring but before opening the cursor:

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

Inside your loop:

fetch cur1 into pid;
IF done = 1 THEN LEAVE read_loop; END IF;

This should solve your immediate problem.

Other observations:

Your function modifies the database, so at the top, you should have this:

returns text
MODIFIES SQL DATA
language sql

...although, really, this would be more appropriately written as a stored procedure rather than a function.  MySQL supports non-deterministic stored functions that modify the database, you there are potential issues with the binary log used for point-in-time recovery or replication that can pop up.

To process all of the data rather than one value at a time, you could use nested cursors -- the outer cursor fetching whatever data you're feeding into this function and the inner cursor -- inside an additional BEGIN/END block doing the logic you've got, above with values fetched in the outer cursor.  This requires resetting of the done value back to 0 after each iteration of the inner block so that the other block doesn't stop prematurely.

The inner subquery is unnecessary, since the cursor could be declared with...

SELECT p.programName
  FROM records r JOIN Programs p ON p.programID = r.usageProgramID
 WHERE ...

...but, in fact, this entire function seems like it may be unnecessary.

In SQL, it's typically best if you you tell the database what needs to be done ("declarative"), as opposed to how to do it ("procedural").

Thinking too procedurally is an SQL antipattern.

I don't know your column names in the 'login' table, so you'll need to correct those, but consider the following query.  As far as I can tell, it does exactly what the function does, for all logins and all matching records.

INSERT INTO login_apps(`sid`, `programName`)
SELECT l.session_id, p.programName
  FROM logins l
  JOIN records r ON r.usageUser = l.user_name
                AND r.usageWhen >= l.start_time
                AND r.usageWhen <= l.end_time
                AND r.usageProgramID != ""
  JOIN Programs p ON p.programID = r.usageProgramID;

Done.

For future maintenance of the "login_apps" table, a trigger on the "records" table could look up the related login and write the new entries into "login_apps" every time "records" has an insert or an update... or when the stop time is logged on the "login" table, an update trigger could read "records" and insert into "login_apps".

Final thought, it would probably be better design if you stored the program ID rather the program name in the login_apps table... unless the "records" table actually has an auto_increment primary key, in which case, that would be the value you should really store in login_apps.


About Global Temporary Table and Stored Procedure in Oracle

GLOBAL TEMPORARY

  Specify GLOBAL TEMPORARY to indicate that the table is temporary and
  that its definition is visible to all sessions with appropriate
  privileges. The data in a temporary table is visible only to the
  session that inserts the data into the table.

http://docs.oracle.com/database/121/SQLRF/statements_7002.htm#SQLRF01402


Firstly, having 4 separate 2 node clusters with one FCI deployment in each seems to me to be a little extreme. The second model you describe does not really make sense to me. Instead of either deployment I would suggest running 2 Clusters of 4 nodes (you would need a witness for each) and consider one node in each as a dedicated failover node (i.e. that you install 3 SQL FCIs to each cluster).

Also you should try to deploy on Windows 2012 R2 because of its dynamic quorum and dynamic witness capabilities (2012 only had dynamic quorum) -which will help to maintain maximum uptime upon node failure.

As you will be aware, the biggest issue with not having enough dedicated failover nodes is that ultimately you are exposed to SQL co-existence considerations (such as setting the correct max-memory upon failover if shared with other SQL FCIs amongst other things). Furthermore in truly Highly Available environments you probably would not want to fail-back post failover (otherwise it is a further addition of down-time), so sharing dedicated failover nodes would probably necessitate failback of one instance (assuming two had failed over).

However, that said, all system deployments are a compromise and provisioning too many redundant failover nodes does not make sense and it is important to find the right balance between HA requirement and cost effectiveness.

As a general rule of thumb you should consider an approximate 1 dedicated (failover node) per (2 SQL FCI) 3 node cluster, 2 dedicated per (5 FCI) 7 node cluster and 3 dedicated per (8 FCI) 11 node cluster.
These figures are generous approximations from nearly 20 years personal experience of SQL Failover Clustering but obviously there are many other factors that can affect your design strategy.

p.s. There are some technical Clustering reasons why it makes sense not to deploy a huge number of cluster nodes so I would probably not go beyond a 5 node cluster anyway (and in that case I would probably still be happy with 1 dedicated failover node).


generate a series of dates in progress-db

Disclaimer: I'm not familiar with progress-db.
generate_series() is a PostgreSQL, set-returning function. AFAIK, generate_series() is not natively available in any other DBMS. (Except maybe in DBMS that are derived from PostgreSQL: Redshift, Greenplum, etc.)
You can get the same effect from most DBMS, though. There are a lot of ways to do that; I don't know whether progress-db supports any of these.

Program a stored procedure.
Use a calendar table.
Use a common table expression (CTE). (Not supported)
And I'm sure there are other ways.

A calendar table seems like the "lowest common denominator". It works in all SQL DBMS. For whole days, 100 years takes less than 40k rows. A hundred years of whole minutes takes about 53 million rows. Your query, running against a calendar table of 100 years of minutes (53m rows, near worst case) boils down to this.
select distinct cal_minute::date as cal_date
from caltest
where cal_minute between '2021-01-07' and '2021-01-12'
order by cal_date;
----
cal_date
date
--
2021-01-07
2021-01-08
2021-01-09
2021-01-10
2021-01-11
2021-01-12

On my computer, which isn't anything special, it consistently runs in less than 10ms, and most of that time is sorting. If the order of rows doesn't matter--and it probably shouldn't--it runs in less than 3ms. (A wider range will take longer, of course.)
Against a 40k table of just dates, the equivalent of your query clocked in at about 0.1ms.


Can I make postgres more verbosely tell me what it's doing?

The idle in transaction state shown in the pg_stat_activity output generally means that there's something that starts a transaction with BEGIN, does some work, and then doesn't issue the COMMIT so that the transaction is finished. This has the effect of leaving the ACCESS SHARE locks acquired by the SELECT statements hanging around, which block the ALTER TABLE statement you're trying to run from acquiring the ACCESS EXCLUSIVE lock it needs to change the type of a column for every row in the table.

They could only be considered "ghost queries", in the sense that they are hanging around waiting for an answer of COMMIT or ROLLBACK that will probably never come. But they are problematic, nonetheless as you figured out.

There might be a bug somewhere in your application that has left all these connections open and waiting for a commit. I'd definitely check that out, and see if perhaps a code block doesn't have a terminating COMMIT.

More commonly (in my experience, YMMV of course), people start database sessions with a BEGIN, do some work, and then go to lunch and never close their session.

It's best to fix the problem at the source, but if you absolutely, positively need to do something right now, pg_cancel_backend and pg_terminate_backend are not bad ways to go.

Commenting on your answer, I'd specifically select the state of idle in transaction, rather than LIKE '%idle%' because those are the ones causing your problems. The idle state backends are just waiting around patiently for new client commands, and had last ran a ROLLBACK command.

You can look at the Explicit Locking documentation for more information about what blocks what.

The description of idle-in-transaction is in the Unix Monitoring Tools section for reference.

pg_cancel_backend and pg_stat_activity issue SIGINT and SIGTERM signals, respectively, and the effects of those signals (and warnings about other signals) are documented in the Server Shutdown section.

Hope that helps explain what you saw and sheds some light on your mystery. =)


How to load data to a table from SQL Server Audit File?

The following process has worked well for us.

We write our audit files to a file share.  I have a Sql Server instance dedicated to processing these audit files.  On this SQLAudit instance, I have a job that runs every minute and executes a stored procedure.

The stored procedure:

Uses Powershell to move any audit files (that are not currently being written to) to a staging fileshare.  The powershell command uses the -ErrorAction SilentlyContinue parameter which allows the command to 'skip' over files that are currently being written to when copying to the staging fileshare.  Eventually, the 'locked' files will become available to be copied on a future interval.
Now that I have available SQLAudit files to read from the staging
fileshare, we're able to use sys.fn_get_audit_file to process all
SQLAudit files from the staging fileshare by using wildcards.
After consuming the files from the staging fileshare, I use Powershell to delete the files from the staging fileshare.

Here's the stored procedure.

CREATE PROCEDURE [dbo].[SqlAuditCaptureAuditLogs]
AS
BEGIN
    SET XACT_ABORT ON

    EXEC xp_cmdshell 'powershell.exe "Move-Item \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\*.sqlaudit \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging -ErrorAction SilentlyContinue"'
        ,no_output

    INSERT INTO SQLAUDIT.[dbo].[SQLAUDIT_HISTORY] (
        event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
        )
    SELECT event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
    FROM sys.fn_get_audit_file('\\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.*', DEFAULT, DEFAULT)
    WHERE server_principal_name NOT IN (
            ,'mfoperational'
            ,'mfreader'
            ,'aperioapplication'
            ,'aperioreader'
            ,'aperiosync'
            ,'aperiobuild'
            ,'DataFix'
            ,'mapinfo'
            ,'GeoSpatialUpdater'
            ,'sa'
            ,'ussql1'
            ,'usweb'
            ,'uswebs'
            ,'vmview'
            ,'dba-admin'
            )

    EXEC xp_cmdshell 'powershell.exe "Remove-Item \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.* -ErrorAction SilentlyContinue"'
        ,no_output
END


Postgres join where foreign table has ALL records

Here are a couple of alternate approaches which don't involve using array_agg.

Use the INTERSECT operator against the the sets of people_id returned for each tag:

WITH both_tags AS (
    SELECT people_id FROM tags WHERE tag = 'np'
    INTERSECT
    SELECT people_id FROM tags WHERE tag = 'yw')
SELECT *
  FROM people
  WHERE id IN (SELECT people_id FROM both_tags);

Or you could use a COUNT(DISTINCT tag) = 2 to find people with both tags. (Note that the DISTINCT was added to handle the case that a person may have the same tag twice. If that is impossible, it's safe to remove.)

WITH both_tags AS (
    SELECT people_id
      FROM tags
      WHERE tag IN ('np', 'yw')
      GROUP BY people_id
      HAVING COUNT(DISTINCT tag) = 2)
SELECT *
  FROM people
  WHERE id IN (SELECT people_id FROM both_tags);

This second approach would be easier to extend to accept an arbitrary number of tags, though the first approach would not be impossible.


Select / update query doesn't finish and await call on query doesn't get resolved resulting in script to get stuck

We are now looking at docker + logs as having caused the issue. It's not the database.


How to model schedule and keep good constraints?

I think this can be done with an additional column (say Day) that will be allowed to hold only 2 possible values (1 and 2).

Assuming a team cannot be in 2 or more stores in the same date, then you could have a UNIQUE constraint on (Team, Date) and another on (Cycle, Team, Store, Day).

The constraints would be:

UNIQUE CONSTRAINT uq_1
  (Team, Date),
UNIQUE CONSTRAINT uq_2
  (Cycle, Team, Store, Day),
CHECK CONSTRAINT ck_max_2_days_per_team_store_cycle
  (Day in (1,2))

and the data:

Cycle Team Store Date   Day
1     1     1    1-Dec   1
1     1     1    2-Dec   2
1     1     2    3-Dec   1
1     1     2    4-Dec   2
1     1     3    8-Dec   1
1     1     3    9-Dec   2
1     1     4   10-Dec   1
1     1     4   11-Dec   2
1     2    10    1-Dec   1
1     2    10    2-Dec   2
1     2    11    3-Dec   1
1     2    11    4-Dec   2
1     2    12    8-Dec   1
1     2    12    9-Dec   2
1     2    13   10-Dec   1
1     2    13   11-Dec   2


Conditional string concatenation in PostgreSQL

The function concat_ws() does exactly what you want. The first parameter is used as glue between the rest. Nulls are ignored:

select concat_ws('//', owner_addr1, owner_addr2, owner_addr3)

Test:

red=# select concat_ws('//', 'abc', null, null, 'xx', null, 'xyz', null)
          as address;
   address
--------------
 abc//xx//xyz
(1 row)


is Sql Server AlwaysOn AG a multi-site active-active replication technology?

the failover cluster instance is not a active-active solution for databases.
Here you can find the microsoft documentation:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/always-on-failover-cluster-instances-sql-server?view=sql-server-ver15
The secondary node(s) is(are) not accessible (their sql server services are stopped). You can build on it an active active solution installing multiple sql server instances having active instances on different nodes but a single database can be accessible only in one node at time.
In alwayson availablility group you can have readable secondaries (only with enterprise license) to offload some tasks like backups, readonly activities etc...
Here you can find the microsoft documentation:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15
Both solutions are not considered active active sql server installation if you consider a single database.
There are other solution that can be used to reach some kind of active active solution. It is the Sql server replication. Here you can find the documentation:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication?view=sql-server-ver15
This last solution is not an instance or database high availability solution. It's more at application level because you have to choose to replicate tables or other specific database objects  to another instance. In this case the peer-to-peer replication is the nearest to an active-active multi master solution. But is also the worst by the point of view of maintenance and performace because your server have to sustain the io load of the whole peer-to-peer network.


Ram & Concurrency: MySQL InnoDB LOAD INLINE FILE on 50 30GB CSV files

There are no restrictions on importing/loading a 30GB file to MySQL (Unless your proc/OS are 32 bit which I doubt). The only additional variable I'll suggest to increase is max_allowed_packet=1G... Later you can return it back to something about 128M (after import)

About the second question: As DBA you will select the storage engine depending on the use of the data after imported, not depending exclusively on the import step. So following standards and current support on transactional procedures, backups, reliability and so on, I suggest you to use Innodb, but again this will depend on the future use of your data.

Since 30 GB actually is not that big, concurrency won't make a huge difference here when importing data.


Does sp_indexoption cause index rebuilds?

Under the covers sp_indexoption just does an ALTER INDEX as well.

My recommendation would be to forget about sp_indexoption and just utilize ALTER INDEX:

alter index IX_YourIndex
on dbo.YourTable
set
(
    allow_row_locks = on
);
go

As noted in the BOL reference for ALTER INDEX, when you specify just the set options:

  Specifies index options without rebuilding or reorganizing the index


How to query based on related table without returning records from related table

You don't need a join, since you have no columns from table_B in the output. You could use a semi-join, i.e an EXISTS subquery:

SELECT a.Name, 'Microsoft' AS Company
FROM Table_A AS a
WHERE EXISTS
      ( SELECT *
        FROM Table_B AS b
        WHERE a.ID = b.Foreign_ID
          AND b.Company = 'Microsoft'
      ) ;

If you did want some other column from table_B (say the Specialty) and still only 1 row, you could use either a derived table with a window function, or CROSS APPLY with TOP 1. CROSS APPLY is a special join where we can reference previous tables. Note that it's similar to INNER JOIN (you had LEFT JOIN while you need INNER JOIN):

SELECT a.Name, 'Microsoft' AS Company, b.Specialty
FROM Table_A AS a
  CROSS APPLY
    ( SELECT TOP (1) bi.Specialty
      FROM Table_B AS bi
      WHERE a.ID = bi.Foreign_ID
        AND b.Company = 'Microsoft'
      ORDER BY Specialty
    ) AS b ;


Can not start SQL Server, model db in RESTORING state

After trying large number of alternative steps was able to fix the problem and get the database started.

Here are the steps I took:

On another SQL Server 2008 R2, I moved my model and temp db files to same location as on my problem server. Refer to this http://technet.microsoft.com/en-us/library/ms345408.aspx
Replaced model and tempdb .mdf and .ldf files on my problem server. Old files I saved in another directory just incase.
Tried to start the SQL Server services, verified error message

  The database 'model' is marked RESTORING and is in a state that does
  not allow recovery to be run. Error: 927, Severity: 14, State: 2.
  Database 'model' cannot be opened. It is in the middle of a restore.
  Could not create tempdb. You may not have enough disk space available.
  Free additional disk space by deleting other files on the tempdb drive
  and then restart SQL Server. Check for additional errors in the event
  log that may indicate why the tempdb files could not be initialized.

After that I opened two separate Command Prompt windows.

Below to make it easier I call first command prompt windows CPA, and second one CPB.

in CPA run Sqlservr.exe -sInstanceName -T3608 -c -f -T3609 (note: I had to navigate to binary location) don't forget to replace InstanceName with yours

-T3608 only starts master Trace Flags documentation

-T3609 skips creation of tempdb

in CPB run SQLCMD -E -SADMIN:Servername\InstanceName replace with your ServerName\InstanceName. Using a Dedicated Administrator Connection
in CPB run sp_detach_db 'model' then go
in CPB run the following code, just replace with your file location.

CREATE DATABASE [model] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\model.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.R2\MSSQL\DATA\modellog.ldf' ) FOR ATTACH
go

in CPA you should see Starting 'MODEL' messsage
in CPA hit Ctrl+C that will show message if you want to exit hit Y

Using SQL Server Configuration Manager or Services page start SQL Server service.

After I was able to connect using SSMS.


Cannot select all data from a table php/sql

user_id != NULL

always fails.  Instead, say

user_id IS NOT NULL

And you need to ->execute it.


Is it possible to query "NOT IN" in NO SQL?

The answer to your question is "no."  And no, I don't think it will ever be possible at least in any helpful way.

Relational databases are designed to operate over sets of tuples.  Non-relational databases are not.  This means that one of the key tradeoffs is being able to have very fast access (with "good enough" consistency controls) to single pieces of data in NoSQL vs high-end consistency controls and set-based math on the relational side.

So the answer is "no, no, and no."

If you want relational math, pick a relational database.


How to qualify variable inside trigger body (PL/SQL )?

Interesting question.  As far as I can tell, this is not possible, though it seems like it should be.  Unless someone else can show how it can be done, a workaround would be to nest the code in a block:

create or replace trigger trg_before_insert_temp1 before insert on temp1 for each row
declare
begin
   <<bob>> declare
      int_val int;
   BEGIN
      bob.int_val := 0;
   END;
end trg_before_insert_temp1;


How to generate an error when backing up to an existing file

INIT will simply overwrite the existing file if it already exists, rather than append. However, you can prevent this from happening if you use the RETAINDAYS option, and set it to a really long time (you will have to maintain and manage these files as they get older, of course). If all of your backup files are initially set to last 10 years, and you always use INIT, you can't overwrite or append.

-- succeeds:
BACKUP DATABASE model TO DISK = 'c:\temp\m.bak'
  WITH INIT, RETAINDAYS = 3652;

-- fails:
BACKUP DATABASE model TO DISK = 'c:\temp\m.bak'
  WITH INIT, RETAINDAYS = 3652;

Error message:

  Msg 4030, Level 16, State 1The medium on device 'c:\temp\m.bak' expires on Apr 21 2025  9:12:27:000AM and cannot be overwritten.
  Msg 3013, Level 16, State 1
  BACKUP DATABASE is terminating abnormally.

If you want to avoid the extra maintenance involved with making your backups "last" extraordinarily long, you'll need to check for the file name yourself before specifying it (and possibly delete it first, or just pick a new name that doesn't conflict).

(The documentation suggests that you could do this using a unique NAME for the backup set, but on SQL Server 2014, I could not get that method to work.)

You could, of course, avoid the issue entirely by using a unique filename. Many people build the filename dynamically so that it is guaranteed to be unique and reflects - at least roughly - the time of the backup.

DECLARE @db SYSNAME = N'model', @filename NVARCHAR(255);

SELECT @filename = N'C:\temp\' + @db + N'_'
  + REPLACE(REPLACE(GETDATE(), ' ', '_'),':','_') + '.bak';

SELECT @filename; -- C:\temp\model_Apr_22_2015__9_12AM.bak

BACKUP DATABASE @db TO DISK = @filename
WITH INIT; -- shouldn't need INIT but
-- better to overwrite than append IMHO


Create Auto Increment Position Column when you Create product TABLE

EXAMPLE

               Create Table category_product
               (
              id_category int,
              id_product int,
               position int Auto_Increment=1
               )

Then Simple Insert

    Insert into category_product
     select 2,id_product from Product


Why is Log File Auto Grow sometimes performed by my application user and sometimes by sa?

Whichever transaction requires additional log space will grow the log file within that transaction, so it can complete.

So if userA is performing a bulk load of data and uses up all available space in the log file, you will see a growth event for the log being called by userA.


Most efficient way to cascade delete in MySQL

While you are using foreign keys in the way that they were designed for, in reality disks can put a limit to the speed of certain operations.

There are certain configuration options that I would advise to check for performance first (innodb_flush_log_at_trx_commit, innodb_log_file_size, etc.) but I am going to assume that there is no performance problems because misconfiguration, and that proper indexes are in place. Profiling your query can help make sure that that is correct.

I would advise you against large transactions modifying lots of rows. Your server, specially under high load can end up impacted quite a lot by such operations.  I would recommend using the JOIN syntax to extract the id's of all rows to be deleted (could be millions?) and delete them in batches. A tool like pt-archiver can help you automatize that, making sure that you are doing things in small transactions, minimizing impact on the server by large write operations.

Another option that may help you with maintenance is mysql PARTITIONING. When a table is paritioned, instead of deleting every row one by one, you can delete whole partitions, which are on different physical locations, resulting in faster delete operations.

I know these suggestions are a bit general, but that is what I can do for you with the information provided.


Meaning of the number of days in an average age?

age() returns intervals. In SQL-92 "an Interval is an unanchored directional duration of the time line" [1]. They have two types due to different month lengths in Gregorian calendar (year to month and day to second). Oracle gives you an error message, if you try to do what you did.
Intervals can be positive and negative. They need to be anchored to have a precise meaning, like date + interval = date2. Taking averages of these values is ill-defined in general and trying it for age-values might still give you unexpected results (as it does).

So what is the meaning of a 3 month 32 days in Postgres? Well only the code can tell (or the one who has written it) for sure. I guess it means "advance one month, then 32 days". One can't convert days to months or vice versa.

How can it happen to be there? Average is sensitive to outliers, so if some rather large value is in there for days, it will have an influence. How are null values handled? Did some users specify dates in the future or gave unreasonable values? Is there some implicit conversion going on? Have Postgres developers created a special average function for intervals?

For your problem at hand I recommend using (sorry if this doesn't work, I don't have a database at hand):

select avg(extract(epoch from now()) - extract(epoch from birthday)) from "user";

This works for users of reasonable age, but if you had users from the 1700th,the answer would also depend on location, as the new calendar was adopted later in some countries.

Read the following book to get to know other quirks with dates.

[1] "Developing time-oriented database applications in SQL", Richard T Snodgrass, Morgan-Kaufmann, 1999. See his homepage, Pages 30-32.


How to get SQL Server Developer edition for installation in non-production for the organisation

Licensing questions are generally not applicable to this forum, but this is a pretty high level one that's more about process, and for an edition that technically doesn't require paid licensing.
Developer Edition is freely available right off of Microsoft's website for SQL Server. The direct download link is here. This will be for the latest version, 2019.
From a process perspective, you just need one copy of the installer and your company's System Admin should be able to use it to install Developer Edition on all of the servers you need to test with.