MySQL 1175 Error While Using Primary Keys

Well it's quite simple.

You have enabled the safe update mode (as the error stated). Your statement has a where clause, right. But it's interpreted as a JOIN not as a WHERE as you provide a syntax like a INNER JOIN.

Your code:

UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;

Is technically the same (and interpreted as such) as this one:

UPDATE a
FROM table_a a
INNER JOIN table_b b
        ON a.a_id = b.b_id;b_id
SET a.update_me = b.update_from_me;

Which means, there is no really WHERE. You update the whole table table_a, which is matching with the other table table_b.

If you really want to achieve this, you can disable the Safe Update Mode for this query using this code:

SET SQL_SAFE_UPDATES=0;
UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;

Anyway, your mysql server is running with mysqld --safe-updates or mysqld --i-am-a-dummy which is pretty the same option.

You can check if this is really enabled using one of the following commands:

SELECT @@SQL_SAFE_UPDATES
-- or
SHOW VARIABLES LIKE 'sql_safe_updates'

By the way, you can also try a workaround to get the update done. Safe Update Mode requires a WHERE or a LIMIT. Which will limit the impact of the Update. Another idea can be to use a pretty high LIMIT for your UPDATE.

UPDATE table_a a, table_b b
SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id
LIMIT 100000;

If the Safe Update Mode is enabled, you need to be aware of DELETE's too. They need both. A WHERE and a LIMIT. See the docs.


Percona pt-table-checksum output is in infinite loop "Waiting for the --replicate table to replicate"

On Master DB Server

(i) For safe side, I made copy of original /bin/pt-table-checksum

cp /bin/pt-table-checksum /bin/pt-table-checksumorg

(ii) Open the /bin/pt-table-checksum file

vi /bin/pt-table-checksum

(iii) Go to line number 8590
 Press esc, type 8590 , press shift g

(iv) replace the line
   my @lagged_slaves = map { {cxn=>$_, lag=>undef} } @$slaves;
by
    my @lagged_slaves = ();

the program immediately works and returns the expected results.


Aurora Serverless alter table Error 1114 table is full

On the AWS Forums, LawrenceAtAWS said,

  the amount of temporary storage space associated with Serverless increases linearly with the number of associated ACUs. If you're having issues running out of temporary storage space for large table mutations, you could temporarily scale the cluster up to a larger number of ACUs and then run the modify statement. This should increase the associated temporary storage an allow the statement to succeed.

The way to achieve this with the CLI is
  aws rds modify-current-db-cluster-capacity --db-cluster-identifier sample-cluster --capacity 64


how to count specific product in sql table

Sample data:

create table product (maker varchar2(100), type char(1), model varchar2(100));
insert into product values ('ABC', '1', 'MODEL1');
insert into product values ('ABC', '1', 'MODEL2');
insert into product values ('ABC', '2', 'MODEL3');
insert into product values ('DEF', '3', 'MODEL4');
insert into product values ('GHI', '4', 'MODEL5');
insert into product values ('GHI', '4', 'MODEL6');
commit;

Query:

select
  maker
from
  product
group by
  maker
having
  count(distinct type) = 1
  and count(distinct model) > 1
;

MAKER
-----
GHI

First we GROUP BY maker, so we inspect each maker on its own. In the HAVING part, count(distinct type) = 1 means the maker produces exactly 1 type of product. Finally, count(distinct model) > 1 means the maker produces more than one model.

With count(distinct type) = 1, we have only 1 distinct type value, so we got lucky in this case and we can use a simple aggregate function on type, we will get the expected value:

select
  maker,
  min(type)
from
  product
group by
  maker
having
  count(distinct type) = 1
  and count(distinct model) > 1
;

MAKER TYPE
----- ----
GHI   4


Failed to update SQL Server 2008 R2 RTM to SP3

I saw a SQL update in Windows Update pulled from our WSUS server, Security update for SQL Server 2008 R2 Service Pack 3 and not realizing that it's an cumulative update, it managed to update the SQL server to 10.50.6220 (SP3 = 10.50.6000) without any error. I'm guessing probably some issue with the original SP3 setup. I'll update here again if I face any issue when we schedule to update the other SQL server.


MySQL: Change compression on an existing InnoDB table


Setting prompt for MariaDB 10.2 in my.cnf?

across all our database servers for everyone

This is a function of the client. You can't set it on the server. MySQL ships with a client, and a server. The client determines the prompt. There is nothing you can do on the server to fix that. The client could be something totally different that has for its prompt

L33tHAX\###->#

You can see the code here construct_prompt(). Nothing there hits the server. That said, it gets set here in the main

default_prompt = my_strdup(getenv("MYSQL_PS1") ?
                 getenv("MYSQL_PS1") :
                 "\\N [\\d]> ",MYF(MY_WME));
current_prompt = my_strdup(default_prompt,MYF(MY_WME));

So it would seem you could set it through the MYSQL_PS1 environmental variable.


According to MSDN...http://msdn.microsoft.com/en-us/library/ms177652.aspx

table
    Is the name of the table that has been marked for full-text querying. table or view can be a one-, two-, or three-part database object name. When querying a view, only one full-text indexed base table can be involved.
    table cannot specify a server name and cannot be used in queries against linked servers.

Since your view has full text indexes on more than one table you will not be allowed to do this.

You could possibly use a union to check both tables in your view.

SELECT {your select columns}
FROM Message AS M
    INNER JOIN FREETEXTTABLE(dbo.Message, {your message columns}, 'Doe') AS KEY_TBL
        ON M.Id = KEY_TBL.[KEY]
    INNER JOIN MessageRecipient MR on M.Id = MR.MessageId AND MR.RecipientTypeId=1

Union

SELECT {your select columns}
FROM Message AS M
    INNER JOIN MessageRecipients MR on  M.Id = MR.MessageId AND MR.RecipientTypeId=1
    INNER JOIN FREETEXTTABLE(MR.MessageRecipients, {your MessageRecipients columns}, 'Doe') AS KEY_TBL
        ON MR.Id = KEY_TBL.[KEY];

I don't have access to a SQL installation right now, but let me know if it works.


This is rather risky business, and I can understand why. It has to do with the way MySQL processes subqueries. I wrote about it back on Feb 22, 2011 : Problem with MySQL subquery
Performing JOINs involving SELECTs and subquery SELECTs are OK. On the flipside of things, UPDATEs and DELETE can be a rather death-defying adventure.
SUGGESTION
Try refactoring the query so that it is an INNER JOIN of two tables
UPDATE
    products_description pd INNER JOIN products_description pd2 ON
    (pd.products_id=pd2.products_id AND pd2.language_id=1 AND pd.language_id<>1)
SET pd.products_seo = pd2.products_seo;

Give it a Try !!!


Replicating PostgreSQL data into Citus/Greenplum?

After some research I've managed to come up with an answer. Kudos to @a_horse_with_no_name for pointing me in the right direction.
Real-time sync between different data sources can be done using Kafka Connect.
Kafka Connect allows to send data from various systems to a Kafka cluster via Source Connectors and read data from Kafka using Sink Connectors. All the platform-specific code for getting data from a system to Kafka or transferring data from Kafka to a system is abstracted away by the maintainers of the specific connector.
Connector maintainers usually will respect some kind of a "contract" between Sinks and Sources, i.e. they will agree in advance on a normalized message format, which allows to use disparate Sources and Sinks (e.g. you can have a PostgreSQL Source and Elasticsearch & Neo4J Sinks in parallel, despite the fact they have completely different paradigms).
For the case of syncing Postgres with Citus/Greenplum, it should be possible to do via Debezium PostgreSQL Source Connector and JDBC Sink connector (both Citus and Greenplum have JDBC support).


How can I model direct ownership alongside group ownership?

A simple way is to make Companies sell only Bundles.
A Bundle can contain one or more Products
A Bundle has a flag that enforces "single product" and/or handling of it

Schema:

company
  company_id (PK)
  ...

bundle
  bundle_id (PK)
  company_id REFERENCES company(company_id)
  is_single_product
  ...
  UNIQUE (bundle_id, company_id)

product
  product_id (PK)
  company_id REFERENCES company(company_id)
  UNIQUE (product_id, company_id)
  ...

product_bundle
  bundle_id REFERENCES bundle(bundle_id, company_id)
  product_id REFERENCES product(product_id, company_id)
  company_id REFERENCES company(company_id)
  PK (bundle_id , product_id )
  ...

Logic to enforce (trigger, view, whatever)

  NOT "count(*) > 1 FROM product_bundle JOIN bundle where is_single_product"

Variation which allows FKs only is_single_product to restricted_product_id

bundle
  bundle_id
  company_id REFERENCES company(company_id)
  restricted_product_id REFERENCES product(product_id) (nullable)

Then the logic is "no rows in product_bundle if restricted_product_id is NOT NULL"

Edit, added company_id to bundle and products with super keys to push down "unique company per product"


Add the option ENABLE=BROKEN into tnsnames.ora. This will enforce sending TCP Keepalive packets over TCP connection. These keepalives are not sent by Oracle client but by OS Kernel directly.

Then you must configure your TCP stack. Set TCP sending interval to value lower than 20 minutes.

Most probably you have a FW on the path and this FW drops tracking information for every in-active TCP connection.


SQL Server 2012 Patching Service Pack and CU Question

You should only require a server reboot if something is locked, correct.

However, during the patching, the SQL service will be stopped, patched then restarted so you'll actually be bouncing the service itself twice.  Once for the SP, then again for the CU.


Two options I can think of. First, if you are on MyISAM or InnoDB 5.6+, you could store the concatenation in a separate field and use a FULLTEXT index on that field.

The other option is to index the first_name and last_name fields separately. Then change your query to:

WHERE a.first_name LIKE "twain%" OR a.last_name LIKE "twain%"

Removing the wildcard from the beginning will allow the indexes to be used.


commit statement missing for a dml statement in binary log

mysql will write rollback to binary logs provided the transaction has mixture of engines (myisam and innodb tables). As the myisam can not be rollbacked.

I guess you'd like this piece of documentation...

  ... This ensures that the binary log reflects the exact data of InnoDB
  tables, and so, that the slave remains in synchrony with the master
  (not receiving a statement which has been rolled back).


GRANT EXECUTE ON PROCEDURE unable to USE database

SUGGESTION #1

Check the user in the Stored Procedure and leave if it is not the right user

DELIMITER //
CREATE DEFINER=`billy`@`%` PROCEDURE `fetchData`(IN `id` INT UNSIGNED)
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  SQL SECURITY DEFINER
  COMMENT 'Accepts an ID and returns the record'
ThisStoredProcedure:BEGIN
  SET @cur_user = CURRENT_USER();
  IF LEFT(@cur_user,LOCATE('@',@cur_user) - 1) = 'otherUser' THEN
    LEAVE ThisStoredProcedure;
  END IF:
  SELECT T.id, T.name
    FROM table AS T
    WHERE T.id = id;
END;
//
DELIMITER ;

SUGGESTION #2

Create New Proc with SQL Security to Invoker and change grants of otherUser to match

DELIMITER //
CREATE DEFINER=`billy`@`%` PROCEDURE `fetchDataForOthers`(IN `id` INT UNSIGNED)
  LANGUAGE SQL
  NOT DETERMINISTIC
  READS SQL DATA
  SQL SECURITY INVOKER
  COMMENT 'Accepts an ID and returns the record'
BEGIN
  SELECT T.id, T.name
    FROM table AS T
    WHERE T.id = id;
END;
//
DELIMITER ;


sql error on script execution. try deleting repository before creating repository

It seems that the video tutorial missed a grant, the correct command is the following:

GRANT
    CONNECT,
    RESOURCE,
    CREATE SESSION,
    CREATE VIEW,
    CREATE MATERIALIZED VIEW
TO migration_repo
IDENTIFIED BY migration_repo;

The database must also have the USERS tablespace.
I found the previous information in this thread.


How to create a table to a specific user

CREATE TABLE {database}.{tablename}

creates a table in the specified database. As such you'll need a user only table
CREATE TABLE user1Table...

To give a user access to the table:

GRANT ALL ON {database}.user1Table TO 'user1'@'%'

Where {database} is the database in which the tables are created.


I have been doing a ton of migrations at my current job and the one time we tried doing an in place upgrade we ended up losing a days worth of work. This is my normal process when moving from an older system to a new one.

IT Creates a new VM with SQL Server configuration (multiple drives for splitting data, temp, log, etc.), usually with the same specs as the old system.
I install SQL Server on the new server
Restore the latest backup from the old system onto the new one and assign basic permissions to allow users/developers to test.
Once they confirm compatibility I take advantage of DBATools and Powershell to migrate users, jobs, and everything else required to the new server. I will also make a fresh backup/restore of the old system databases.
Users/Developers test again and confirm system is operational.
Schedule a transition period, usually on weekends late at night. IT makes a snapshot of the VM. Set old databases as read only, backup/restore databases to new system, once the new system is ready users and developers test.
Only after they confirm it is working IT will shutdown the old system (they delete the VM after a month of no issues).

This is obviously fairly generic, sometimes if the system is small enough I will use DBATools migrate command and just move everything over at once. Sometimes the time between steps is days or weeks, even months if its a core system.


Is it a good practice to create tables dynamically in a site?

Short answer: In general it is no good practice to dynamically create tables and columns. But there can be rare special cases there it is good practice.

Long answer:

You may run (at least) on the following problems with your solution:

A user can have only 1 garden, because the garden_id is in the table "users".
If you want to add some more infos to a flower, e. g. color or size you need one more column for each flower.
You must know all the flower names (column names) to write the sql-query.

If I understand your requirement correctly, this is a case for an intersection table (many to many relation between flowers and gardens).

Maybe this will solve your needs:

Create the following tables:

Create a table "Flowers" which contains all possible flowers with columns: "Id", "Flower". You may add additional columns which describe the flowers, e. g. color, size,...
Create a table "Users" with colums "Id", "username",... This table contains only the users and no info about his garden or flowers.
Create a table "Gardens" with colums "Id", "User_Id". You may add additional columns which describe the garden, e. g. size. This table contains infos about the garden and to which user the garden belongs. But no infos about the flowers in the garden.
Create a table "Garden_Flowers" with columns "Id", "Garden_Id", "Flower_Id". This table contains the information, which flowers are in which garden. It may contain additional infos, e. g. when the flowers was planted.

Now you can add new flowers into table "Flowers" without the need to add new colunns or tables.
You can define which flowers are in which garden with the intersection table "Garden_Flowers". Just insert one record for each flower with the flower_id and the garden_id.


Naming Convention for Login, User and Roles?

As far as I know there won't be one documented for objects like logins users and roles. Just to make a note to keep the names not very familiar or similar to database name or any common naming convention that can be prone to hack. Check your company standards how existing environments uses the setup, as I am sure this won't be the first of them.


Returning empty string when string_agg has no records

Use COALESCE to catch and replace NULL values:

SELECT f.name AS foo
     , 'Bazzes: ' || COALESCE(string_agg(b.baz, ', '), '') AS bazzes
FROM   foo f
LEFT   JOIN bar b ON b.fooid = f.id
GROUP  BY 1;

concat() is another convenient option as you found yourself, in particular to concatenate multiple values. I suggest the variant concat_ws() ("with separator"), though, to avoid the trailing space.

concat_ws(' ', 'Bazzes:', string_agg(b.baz, ', ')) AS bazzes

Why NULL?

Almost all aggregate function return NULL if all source fields are NULL (no non-null values, to be precise) - count() being the exception for practical reasons. The manual:

  It should be noted that except for count, these functions return a
  null value when no rows are selected. In particular, sum of no rows
  returns null, not zero as one might expect, and array_agg returns null
  rather than an empty array when there are no input rows. The coalesce
  function can be used to substitute zero or an empty array for null when necessary.

Related:

How to concatenate columns in a Postgres SELECT?
Combine two columns and add into one new column


SCAN listener redirects connection to another port

I found PDF about Oracle Single Client Access Name (SCAN)

Using SCAN with Multiple Ports on the Same Subnet

For the purpose of allowing applications to maintain a distinguishable connect string on host / port
level using one SCAN as the host or to enable port-based firewall configurations, SCAN can be set up
to support more than one port for the same subnet.

  **     It needs to be noticed that different ports are only used for an initial connect. Once a connection using
      SCAN on any SCAN port has been established, port assignment is not maintained and routing of
      connections within the Oracle RAC cluster is performed using database services registered with one or
      more local listeners.
      This means that all SCAN ports are treated the same, so that a connection that comes in on any SCAN
      port can be directed to any registered local listener on any port within the same subnet.
  **

Note also that the multi-port assignment differs per version. Oracle Grid Infrastructure 11.2.0.3 would
not let you set up multiple ports on one SCAN anymore, while Oracle Grid Infrastructure 11.2.0.2
would have let you use this configuration. Support for this configuration is resumed with Oracle Grid
Infrastructure 11.2.0.3.6 and higher as well as Oracle Grid Infrastructure 12c.

I have local_listener set to value:
(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip-hq)(PORT=1530))(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip-hq)(PORT=1521)))

So my local listeners are 1530 and 1521. and incoming connection from scan can be redirected to any of them.

I have added another listener(port 1522) statically registered database with this listener and did not indicated this listener in local_listener parameter.

Just connection to this port must be done trough vip address, not by scan. like that:

> MYDB_JOB =   (DESCRIPTION =
>     (ADDRESS_LIST=
>      (FAILOVER=on)
>      (LOAD_BALANCE=on)
>      (ADDRESS=(PROTOCOL=tcp)(HOST= node1-vip-hq)(PORT=1522))
>      (ADDRESS=(PROTOCOL=tcp)(HOST= node1-vip-hq)(PORT=1522))
>      )
>     (CONNECT_DATA =
>       (SERVER = DEDICATED)
>       (SERVICE_NAME = MYDB)
>     )   )


SET @@GLOBAL.wait_timeout = 30;

Have your users disconnect when they are through.

Do not have more than 20 for MaxClients in Apache.


Some data is missing in the table of SQL

This is the default setting SQL Server Management Studio, Navigate on Menu button as follows

If you are using Result to Grid:

Tools --> Options --> Expand Query Results --> Expand SQL Server -- > select result to Grid

then at the right side you will see Maximum number of characters Retrieved and Non XML Data with the Text box, enter 65535(This is maximum possible value) then click on OK.

If you are using result to Text:

Tools --> Options --> Expand Query Results --> Expand SQL Server -- > select result to Text

then at the right side you will see Maximum number of characters displayed in each Column and the Text box, enter 8192 (This is maximum possible value) then click on OK.

Note:
You need to open new Window for the changes to take effect, it will not be applicable for opened windows.


Django Multi Table Inheritance and Preserving Child and Child History

I suggest you have a look at this answer.

Use deletion.Collector with manual collection:

from django.db.models import deletion

collector = deletion.Collector(using=minor._state.db)
collector.add([minor])
collector.delete()


Testing Geometry Polygon Data is Valid Using a Stored Procedure

This issue was being caused because the ogr_geometry coming from the recordset had not been cast to a string.

When I changed the field in the recordset to ogr_geometry.ToString() AS sGeometry and used sGeometry instead of ogr_geometry all worked as expected.


Here is a quick-and-dirty solution:

set global innodb_stats_on_metadata = 0;
show table status;
set global innodb_stats_on_metadata = 1;

You want to turn innodb_stats_on_metadata  on immediately after the show table status; so that metadata is used efficiently for Query Optimizer analysis when evaluating queries involving InnoDB. Leaving it off will provide more stable Query Execution plans, but the index statistics grow stale quickly in a heavy-write environment.

Give it a Try !!!


They are meant to do a check on each node to see whether it's the primary or not before continuing to run the rest of the logic but they didn't bother.

It sounds like someone set it up under the belief that it will never interfere with each other concurrently (maybe because it has file locks or something else).

No biggie. If you're redesigning it I would add the primary replica check anyway. The question about 30s to 5m is impossible to say though. It's not going to break the AG but what it does to the software is another question.


After reseting root password, I could connect with workbench. I still can't connect with old users (which were already created), but connecting with root and creating new users with workbench allows me to connect with these new users.

It looks it has something to do with the password, or the way MySQL Server stores password for users. I don't know the actual reason for the error, but this solved my problem.

Thanks for the suggestions!


WiredTiger prefix compression for index on MongoDB

By default collection stats only include information on the collection data (which uses block-level compression rather than prefix compression).

To see index details you need to provide an additional indexDetails option, eg:

db.myCollection.stats({indexDetails:true}).indexDetails._id_.creationString

You should see "prefix_compression=true" in the index creationString value.


ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Solved :

I solve this problem, by creating new table like this spool. when we want to create a table with utf8mb4 COLLATE utf8mb4_unicode_ci character set we can not use varchar(250) because 250 * 4 = 1000 (4 byte per character in this new encoding). 1000 is more than max key 767 byte for this encoding. so I create a table spoolll with varchar(100) :

CREATE TABLE `spoollll` (
  `username` varchar(100) NOT NULL,
  `xml` text NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=23492667 DEFAULT CHARSET=utf8

and now I change the character set :

mysql> ALTER TABLE spoollll CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

I would appreciate if someone comment on it and complete this issue.


"find all cars with color red" is a relational question, and therefore a normalized relational database with the table structure you designed in your second example makes more sense than JSON. JSON makes things easier for highly variable and structureless scenarios, but when you have relationships between objects (entities) like Cars and Colors, then it's the "lazy developer solution" to use JSON instead of a relational database. And it's more work in the long run when you have to ask relational questions like your example, when the data is stored in JSON as opposed to a relational structure with normalized tables.


Is it possible to alias a column based on the result of a select+where?

Sorry to say but your table structure is difficult to work with considering what you want to do. There are various ways you can probably get the result, one way would be to use UNPIVOT and PIVOT, but it's ugly.

You could start by UNPIVOTing the data in TB1 from your columns to rows:

select *
from tb1 t1
unpivot
(
    val
    for col in (A001, A002, A003)
) u

This is going to return the data in the format of:

| COD |  val |  col |
|-----|------|------|
|   1 | cars | A001 |
|   1 | baby | A002 |
|   1 | nasa | A003 |

Then you could take that result and join it to TB2:

select
    d.COD,
    d.val,
    t2.DESCRIPTION
from
(
    select *
    from tb1 t1
    unpivot
    (
        val
        for col in (A001, A002, A003)
    ) u
)d
inner join tb2 t2
    on d.col = t2.NO_COL_TB1;

Which gives the result:

| COD |  val | DESCRIPTION |
|-----|------|-------------|
|   1 | cars |   Something |
|   1 | baby |     lasagna |

Now you have your new column names in the Description and the val in rows, but you want them in columns, so now you can apply the PIVOT function to it:

select *
from
(
    select
        d.COD,
        d.val,
        t2.DESCRIPTION
    from
    (
        select *
        from tb1 t1
        unpivot
        (
            val
            for col in (A001, A002, A003)
        ) u
    )d
    inner join tb2 t2
        on d.col = t2.NO_COL_TB1
) x
pivot
(
    max(val)
    for description in (Something, Lasagna)
) p;

This generates the final result you want:

| COD | Something | Lasagna |
|-----|-----------|---------|
|   1 |      cars |    baby |

Now all that is great if you know all the columns that you need to UNPIVOT and then PIVOT, but if you don't then you'll need to use dynamic SQL to solve it. Which will look something like this:

DECLARE
    @colsUnpivot AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsUnpivot
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'tb1' and
                 C.column_name like 'A%'
           for xml path('')), 1, 1, '')

select @colsPivot
  = stuff((select ','+quotename([DESCRIPTION])
           from Tb2
           for xml path('')), 1, 1, '')

set @query = 'select *
            from
            (
                select
                    d.COD,
                    d.val,
                    t2.DESCRIPTION
                from
                (
                    select *
                    from tb1 t1
                    unpivot
                    (
                        val
                        for col in ('+ @colsUnpivot +')
                    ) u
                )d
                inner join tb2 t2
                    on d.col = t2.NO_COL_TB1
            ) x
            pivot
            (
                max(val)
                for description in ('+@colsPivot+')
            ) p;';

exec sp_executesql @query;

It's long, but it should give you the same result. (dbfiddle demo)


Setting up simple question & answer database

Here's one idea:

CREATE TABLE dbo.AnswerGroups
(
  AnswerGroupID INT IDENTITY(1,1) PRIMARY KEY,
  Description NVARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.Answers
(
  AnswerID INT IDENTITY(1,1) PRIMARY KEY,
  AnswerGroupID INT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Questions
(
  QuestionID INT IDENTITY(1,1) PRIMARY KEY,
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.AnswerGroupQuestions
(
  AnswerGroupID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  QuestionID INT NOT NULL UNIQUE FOREIGN KEY
    REFERENCES dbo.Questions(QuestionID),
  PRIMARY KEY(AnswerGroupID, QuestionID)
);

That makes all of this legal:

INSERT dbo.AnswerGroups(Description) SELECT 'YesNo';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(1,'Yes'),(1,'No');

INSERT dbo.AnswerGroups(Description) SELECT 'TrueFalse';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(2,'True'),(2,'False');

INSERT dbo.Questions(DisplayText) SELECT 'Driver''s License?';

INSERT dbo.AnswerGroupQuestions(AnswerGroupID, QuestionID)
  VALUES(1, 1);

INSERT dbo.Questions(DisplayText) SELECT 'Married?';

INSERT dbo.AnswerGroupQuestions(AnswerGroupID, QuestionID)
  VALUES(1, 2);

But prevents this assigning, say, one question to more than one answer group:

INSERT dbo.AnswerGroupQuestions VALUES(2, 1);

Result:

  Msg 2627, Level 14, State 1, Line 1
  Violation of UNIQUE KEY constraint 'UQ_AnswerGr_0DC06F8DA7923A33'. Cannot insert duplicate key in object 'dbo.AnswerGroupQuestions'. The duplicate key value is (1).
  The statement has been terminated.

A better design, as @DForck42 correctly observed:

CREATE TABLE dbo.AnswerGroups
(
  AnswerGroupID INT IDENTITY(1,1) PRIMARY KEY,
  Description NVARCHAR(32) NOT NULL UNIQUE
);

CREATE TABLE dbo.Answers
(
  AnswerID INT IDENTITY(1,1) PRIMARY KEY,
  AnswerGroupID INT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerGroupID),
  DisplayText NVARCHAR(255) NOT NULL
);

CREATE TABLE dbo.Questions
(
  QuestionID INT IDENTITY(1,1) PRIMARY KEY,
  DisplayText NVARCHAR(255) NOT NULL,
  AnswerGroupID INT NOT NULL FOREIGN KEY
    REFERENCES dbo.AnswerGroups(AnswerID)
);

Only slightly different sample inserts:

INSERT dbo.AnswerGroups(Description) SELECT 'YesNo';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(1,'Yes'),(1,'No');

INSERT dbo.AnswerGroups(Description) SELECT 'TrueFalse';

INSERT dbo.Answers(AnswerGroupID,DisplayText)
  VALUES(2,'True'),(2,'False');

INSERT dbo.Questions(DisplayText, AnswerGroupID)
  SELECT 'Driver''s License?', 1
  UNION ALL SELECT 'Married?', 1;

And of course since there is only one possible value to link a Question to an AnswerGroup, there is no need to demonstrate the prevention of multiple Question -> AnswerGroup associations.


There are two key differences between EXCEPT and NOT IN.

EXCEPT

EXCEPT filters the DISTINCT values from the left-hand table that do not appear in the right-hand table. It's essentially the same as doing a NOT EXISTS with a DISTINCT clause.

It also expects the two tables (or subset of columns from the tables) to have the same number of columns in the left and right hand side of the query

For example, you cannot do:

SELECT ID, Name FROM TableA
EXCEPT
SELECT ID FROM TableB

This would result in the error:

  All queries combined using a UNION, INTERSECT or EXCEPT operator must
  have an equal number of expressions in their target lists.

NOT IN

NOT IN does not filter for DISTINCT values and returns all values from the left-hand table that do not appear in the right-hand table.

NOT IN requires you compare a single column from one table with a single column from another table or subquery.

For example, if your subquery was to return multiple columns:

SELECT * FROM TableA AS nc
WHERE ID NOT IN (SELECT ID, Name FROM TableB AS ec)

You'd get the following error:

  Only one expression can be
  specified in the select list when the subquery is not introduced with
  EXISTS.

However, if the right-hand table contains a NULL in the values being filtered by NOT IN, an empty result set is returned, potentially giving unexpected results.

EXAMPLE

CREATE TABLE #NewCustomers (ID INT);
CREATE TABLE #ExistingCustomers (ID INT);

INSERT INTO #NewCustomers
        ( ID )
VALUES
     (8), (9), (10), (1), (3), (8);

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( 1) , (2), (3), (4), (5);

-- EXCEPT filters for DISTINCT values
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN returns all values without filtering
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

From the above two queries, EXCEPT returns 3 rows from #NewCustomers, filtering out the 1 and 3 that match #ExistingCustomers and the duplicate 8.

NOT IN does not do this distinct filtering and returns 4 rows from #NewCustomers with the duplicate 8.

If we now add in a NULL to the #ExistingCustomers table, we see the same results returned by EXCEPT, however NOT IN will return an empty result set.

INSERT INTO #ExistingCustomers
        ( ID )
VALUES
        ( NULL );

-- With NULL values in the right-hand table, EXCEPT still returns the same results as above
SELECT * FROM #NewCustomers AS nc
EXCEPT
SELECT * FROM #ExistingCustomers AS ec

-- NOT IN now returns no results
SELECT * FROM #NewCustomers AS nc
WHERE ID NOT IN (SELECT ID FROM #ExistingCustomers AS ec)

DROP TABLE #NewCustomers;
DROP TABLE #ExistingCustomers;

Instead of NOT IN, you should really look at NOT EXISTS and there is a good comparison between the two on Gail Shaw's blog .


Can you restore a ScyllaDB from only a backup of /var/lib/scylla/data folder?

All user data keyspace/s + system keyspaces (system_auth, system_schema and others) reside under /var/lib/scylla/data.
So if you have a backup of /data folder (with all it's subfolders -> tables, and sstables files etc.)
You should defenetly be able to restore your cluster with it's data.
You will need to recreate the cluster's /var/lib/scylla/scylla.yaml
The part about "no snapshots" is not clear...


Just through trial and error and persistence, I found a solution after about 2.5 hours:

select REGEXP_REPLACE(post_content,'(*CRLF)<noindex>.*</noindex>','') from wp_posts where ID = 318

If anyone could explain why the above solutions in my question didn't work though, that would still be appreciated.

Reference to (*CRLF) on this page:
https://mariadb.com/kb/en/library/pcre/#multiline-matching


I would be interested to hear what you have tried and what issues you have faced.

You say you have SSMS 2012 installed, but it's not clear if you have a database engine in which to host a database and associated tables?

If you do not have a database engine installed, you can install the SQL Express engine locally. Download it here.

You are going to need to import the data from Excel into a table within an existing SQL Server database.

To do this, you can use SSIS, if you have access to it, and create a simple ETL package involving a Source (Excel) and OLEDB Destination (SQL Server). You will need to create an existing table in your database, with appropriate fields and data-types, in which to 'dump' the data.

Or,

You can import the data through Management Studio directly, but you will need an existing database. Right click the database, click Tasks --> Import Data, and then follow the steps, selecting Excel Source and SQL Destination.


How to make an existing column autoincrement in Oracle

If you insist on using IDENTITY, it is not be possible  - you cannot modify non-identity column to identity.  However,  Oracle lets you use sequence.nextval as default, so you may get similar functionality  :

CREATE SEQUENCE SEQ_USER START WITH 1000; --assuming max(USERS.user_id) =999
ALTER TABLE USERS MODIFY (USER_ID DEFAULT SEQ_USER.NEXTVAL);

It will work as GENERATED BY DEFAULT AS IDENTITY.


You can create a table of 24 rows representing the 24 hours in a day to check the number of active users in each hour. Example below.

NOTE: In my example, I only really have data for a single day. You can add a WHERE clause to the subquery to filter larger data sets to a single day to check active users for a single day.

Setup:

CREATE TABLE #Hours (Hour INT)

;WITH CTE AS (
    SELECT 1 AS Level
    UNION ALL
    SELECT 1+Level
    FROM CTE
    WHERE Level <= 23
)

INSERT INTO #Hours (Hour)
SELECT Level
FROM CTE

CREATE TABLE #Sessions (UserID INT,
    StartDate DATETIME,
    EndDate DATETIME
)

INSERT INTO #Sessions (UserID, StartDate, EndDate)
VALUES
(1, '2019-05-29 08:00:00', '2019-05-29 16:00:00')
,(2, '2019-05-29 10:00:00', '2019-05-29 21:00:00')
,(3, '2019-05-29 02:00:00', '2019-05-29 08:00:00')
,(4, '2019-05-29 15:00:00', '2019-05-29 23:00:00')
,(5, '2019-05-29 09:00:00', '2019-05-29 17:00:00')
,(6, '2019-05-29 13:00:00', '2019-05-29 18:00:00')
,(7, '2019-05-29 04:00:00', '2019-05-30 00:00:00')

Query:

SELECT h.Hour,
    COUNT(UserID) AS [ActiveUsers]
FROM
(
    SELECT UserID, StartDate, EndDate,
        CASE
            WHEN DATEPART(HOUR, StartDate) = 0 THEN 24
            ELSE DATEPART(HOUR, StartDate)
        END AS StartHour,
        CASE
            WHEN DATEPART(HOUR, EndDate) = 0 THEN 24
            ELSE DATEPART(HOUR, EndDate)
        END AS EndHour
    FROM #Sessions
) s
FULL OUTER JOIN #Hours h ON h.Hour BETWEEN StartHour AND EndHour
GROUP BY h.Hour

Results:

Hour    ActiveUsers
-------------------
1       0
2       1
3       1
4       2
5       2
6       2
7       2
8       3
9       3
10      4
11      4
12      4
13      5
14      5
15      6
16      6
17      5
18      4
19      3
20      3
21      3
22      2
23      2
24      1


Can't update trigger because of activity

When you issue an ALTER TRIGGER statement, that process will attempt to acquire a SCH-M (schema modification) lock on the table object as well as the trigger object.  This is an extremely low-concurrency lock and will cause the expected blocking that you're running into.

See this reference for a chart on lock compatibility.  For referencing sake, here is the chart linked directly from that document:

As you can see, that SCH-M lock will conflict and cause a concurrency blocker.

Much like anything that can cause an interruption, or be blocked itself, I'll have to agree with the comment above (from David Crowell).  You simply need to find a time when there is little to no activity on these objects.


Who is using a SQL Server database user credential?

You could use a logon trigger perhaps, going forward (see here and here).

For historical information, by default, SQL Server doesn't even track successful logins (though this can be turned on - however it means you will need to parse the SQL Server log for some little bit of information). So while you might get some information (such as which workstations were using which logins) from the default trace, it would rely on them performing actions that get logged there (and would only go back as far as your default trace data goes anyway).

Also see this answer.


Best way to quickly and effectively drop a database and delete all files?

Unless you are in the context of the database, you can't ensure you are the only one using it. Also, don't use batch separators. Run it all as a single batch.

Use AlwaysEncryptedSample;

ALTER DATABASE AlwaysEncryptedSample SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

Use master;

DROP DATABASE AlwaysEncryptedSample;


WMI error: 0x800700a4 - The @wmi_query could not be executed in the @wmi_namespace provided

Fix #1
In some cases I was able to solve the problem by restarting the WMI service "Windows Management Instrumentation", and then re-running the script. This did not help in every case however.

Fix #2
On both of the SQL 2008 R2 servers, I noticed that the WMI service ("Windows Management Instrumentation") was running as Local System, so I checked to see if it had login rights on the SQL instance. It did indeed already have a login "NT AUTHORITY\SYSTEM", with no particular rights granted to it. I gave it sysadmin temporarily, and was able to run my script successfully. I then removed sysadmin. and my WMI alert continued to work.

Fix #3
Another possible reason is the account that runs the Windows service "Windows Management Instrumentation" is disabled as a SQL login. (If you are running SQL 2012+, look for the login 'NT SERVICE\winmgmt'). (Source: Blog by 'rahmanagoro' )


Just set slow_query_log = 1 (if >=5.6.1) or
log_slow_queries = 1(if <5.6.1) and
slow_query_log_file = /path/to/logfile and long_query_time = 0 in my.cnf, then restart mysql and have a look at the logfile. I won't keep it on on production envs, such a logging is a performance killer and may result in bad things if you are on heavy load. It may also get quickly very big as long_query_time 0 results in logging everything, so "/path/to" should have plenty of free space.


How to delete foreign key with lots of references

If the only concern is timeout there are probably 3 ways to get around that.

Like referenced by Lennart, proper indexing should allow even the deletion of 1000's of rows to happen fairly quickly.  Assuming you can run a query like below fairly quickly, a DELETE would only be negatively impacted by the number of indexes on [Table B] that have to be updated for each row removed.

SELECT [Table B].ID
FROM [Table B]
    INNER JOIN [Table A]
        ON [Table B].[Table A ID] = [Table A].ID
WHERE [Table A].ID = @Value

Extend out the command timeout on the operation that triggers this delete.  The user would need to wait for it to complete, but they would be notified of a failure if there was one and would know for sure that the row (and all its child data) was removed.
(Full-disclosure, I dislike this option but it make serve your purposes if the above two don't help enough) You can also create a Delete Log table.  Where you would store whatever records from [Table A] need to be removed.  Your Delete operation on the end user application would be a simple Insert into this Delete Log table.  You would then have a background process (through either an Automation tool or SSIS/Sql Agent) that takes that log and removes the associated Table B and Table A records.  This would then have all the time it needs to remove the records in question.  The main downside is all the records exist until the background routine Runs and then completes so the end user would need to understand that their records are not removed immediately.

Hopefully this helps.


Modelling a database for products, categories and features

If a category has related features then you have to link them independently from product. If a feature is only present through a category then you can omit the link between feature and product. If not, you have to link feature with both category and product, or else you might have inconsistencies between a product category's features and the actual features for that product.

Assuming that a category has a set of features and a product might have specific features (aside from those from it's category), I would suggest the following scheme:

CREATE TABLE Feature (
    FeatureID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(1000))

CREATE TABLE Category (
    CategoryID INT PRIMARY KEY,
    Name VARCHAR(100),
    Description VARCHAR(1000))

CREATE TABLE Product (
    ProductID INT PRIMARY KEY,
    Name VARCHAR(100))

-- Each product has a set of categories (N to M)
CREATE TABLE CategoryByProduct (
    ProductID INT,
    CategoryID INT,
    PRIMARY KEY (ProductID, CategoryID),
    FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
    FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID))

-- Each category has a set of features (N to M)
CREATE TABLE FeatureByCategory (
    CategoryID INT,
    FeatureID INT,
    PRIMARY KEY (CategoryID, FeatureID),
    FOREIGN KEY (CategoryID) REFERENCES Category (CategoryID),
    FOREIGN KEY (FeatureID) REFERENCES Feature (FeatureID))

-- Each product might have additional features aside from the one's from it's category (N to M)
CREATE TABLE FeatureByProduct (
    ProductID INT,
    FeatureID INT,
    PRIMARY KEY (ProductID, FeatureID),
    FOREIGN KEY (ProductID) REFERENCES Product (ProductID),
    FOREIGN KEY (FeatureID) REFERENCES Feature (FeatureID))

So if you want to retrieve a specific product's features you have to traverse through it's categories and particular features:

-- Features by product's category
SELECT
    P.ProductID,
    P.Name,
    F.FeatureID,
    F.Name
FROM
    Product P
    LEFT JOIN CategoryByProduct CP ON P.ProductID = CP.ProductID
    LEFT JOIN FeatureByCategory FC ON CP.CategoryID = FC.CategoryID
    LEFT JOIN Feature F ON FC.FeatureID = F.FeatureID
WHERE
    P.ProductID = 123 -- Particular product ID

UNION ALL

-- Stand-alone features
SELECT
    P.ProductID,
    P.Name,
    F.FeatureID,
    F.Name
FROM
    Product P
    LEFT JOIN FeatureByProduct FP ON P.ProductID = FP.ProductID
    LEFT JOIN Feature F ON FP.FeatureID = F.FeatureID
WHERE
    P.ProductID = 123 -- Particular product ID

Following this approach, you just have to add a category to a product to "inherit" it's features, and you have the possibility to add custom features to particular products. Also changing the features of a category will automatically impact all products related to that category.


Force chain to start immediately after completion instead of on a timer

The easiest way to do this is to add another step prior to the end of the chain that changes the job to start execution of the chain at SYSDATE + 1/1440.

Something like this:

CREATE OR REPLACE PROCEDURE move_next_execution
AS
BEGIN
    -- change to the correct job_id
    dbms_job.next_date(666,'sysdate+1/1440');
    -- need to commit so that the scheduler (which runs in a different session) can see it
    commit;
END;
/

sys.dbms_scheduler.create_program(
    program_name => 'PHIL.MOVE_EXEC',
    program_action => 'PHIL.move_next_execution',
    program_type => 'STORED_PROCEDURE',
    number_of_arguments => 0,
    comments => NULL,
    enabled => FALSE);

sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.MOVE_EXEC');

(Untested, but will test it when I have time - unsure if the end of the chain & job will again modify the next_date.)

I have tested the following, which works OK (but involves creating a new job):

-- schedule next execution
CREATE OR REPLACE PROCEDURE schedule_next_execution
AS
BEGIN
    -- not that job_name will have to be generated so it's unique each time
    DBMS_SCHEDULER.CREATE_JOB (
      job_name        => 'chain_job',
      job_type        => 'CHAIN',
      job_action      => 'CHAIN',
      start_date => systimestamp + interval '1' minute,
      enabled         => TRUE);

END;
/

-- just an example proc
CREATE OR REPLACE PROCEDURE do_nothing_for_2minutes
AS
BEGIN
  DBMS_LOCK.SLEEP(120);
END;
/

BEGIN
    sys.dbms_scheduler.create_program(
        program_name => 'PHIL.NOTHING',
        program_action => 'PHIL.do_nothing_for_2minutes',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.NOTHING');

    sys.dbms_scheduler.create_program(
        program_name => 'PHIL.SCHEDULE',
        program_action => 'PHIL.schedule_next_execution',
        program_type => 'STORED_PROCEDURE',
        number_of_arguments => 0,
        comments => NULL,
        enabled => FALSE);

    sys.DBMS_SCHEDULER.ENABLE(name=>'PHIL.SCHEDULE');
END;
/

BEGIN
    sys.dbms_scheduler.create_chain(
        comments => 'example chain',
        chain_name => 'PHIL.CHAIN'
    );

    sys.dbms_scheduler.enable(name=>'PHIL.CHAIN');
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        STEP_NAME  => 'STEP1',
        PROGRAM_NAME => 'PHIL.NOTHING'  );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_STEP  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        STEP_NAME  => 'STEP2',
        PROGRAM_NAME => 'PHIL.SCHEDULE'  );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'TRUE',
        action => 'START STEP1'
        );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'STEP1 SUCCEEDED',
        action => 'START STEP2'
        );
END;
/

BEGIN
    sys.DBMS_SCHEDULER.DEFINE_CHAIN_RULE  (
        CHAIN_NAME  => 'PHIL.CHAIN',
        condition => 'STEP2 SUCCEEDED',
        action => 'END'
        );
END;
/

-- get timestamp
select systimestamp from dual;

-- run for first time
BEGIN
  DBMS_SCHEDULER.RUN_CHAIN (
    chain_name   => 'CHAIN',
    start_steps  => null );
END;
/

-- wait 2 mins
select DBMS_LOCK.SLEEP(120) from dual;

-- check for new job
select start_date from dba_jobs where job_name = 'CHAIN_JOB';


Recommending movies customers haven't rented yet based on their favorite genres, according to their rental history

Most common ...

In statistics, this is called mode.

Yes, PostgreSQL has an aggregate called mode.  I haven't messed with it in PostgreSQL.

Retrieves those not wanted...

You want the resulting set of all movies for a genre minus the data set containing all movies rented by a customer for that genre

In PostgreSQL, you'll want to say EXCEPT instead of MINUS.


What would the syntax be to write an update statement to update the null values for fields power1, power2, power3 with the values already listed in the table?

I take this to mean that each company's nulls should be populated based on the values taken from the one populated entry that, as you say elsewhere in your post, each company has. So, in pseudo-code, your UPDATE statement would need to look something like this:

UPDATE
  #Rentarious
SET
  power1 = power1 from the same company’s populated row,
  power2 = power2 from the same company’s populated row,
  power3 = power3 from the same company’s populated row
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

One way to implement the above pattern could be to use correlated subqueries:

UPDATE
  #Rentarious
SET
  power1 = (SELECT power1 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power1 IS NOT NULL),
  power2 = (SELECT power2 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power2 IS NOT NULL),
  power3 = (SELECT power3 FROM #Rentarious AS src WHERE src.c1 = #Rentarious.c1 AND src.power3 IS NOT NULL)
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

While that would work, such an update might not be very efficient, because the same table would be touched three extra times to obtain the source value. Since you know that the non-null values are stored in one row for each company, you could obtain them all in just one extra pass over the table by using a derived table and the proprietary "update with a join" syntax:

UPDATE
  #Rentarious
SET
  power1 = sub.power1,
  power2 = sub.power2,
  power3 = sub.power3
FROM
  (
    SELECT
      c1,
      power1,
      power2,
      power3
    FROM
      #Rentarious
    WHERE power1 IS NOT NULL
      AND power2 IS NOT NULL
      AND power3 IS NOT NULL
  ) AS sub
WHERE #Rentarious.c1 = sub.c1
  AND #Rentarious.power1 IS NULL
  AND #Rentarious.power2 IS NULL
  AND #Rentarious.power3 IS NULL
;

You can also rewrite it to use the explicit JOIN syntax:

UPDATE
  tgt
SET
  power1 = src.power1,
  power2 = src.power2,
  power3 = src.power3
FROM
  #Rentarious AS tgt
  INNER JOIN
  (
    SELECT
      c1,
      power1,
      power2,
      power3
    FROM
      #Rentarious
    WHERE power1 IS NOT NULL
      AND power2 IS NOT NULL
      AND power3 IS NOT NULL
  ) AS sub
  ON tgt.c1 = sub.c1
WHERE tgt.power1 IS NULL
  AND tgt.power2 IS NULL
  AND tgt.power3 IS NULL
;

As you can see, there is only one subquery in both variations and it provides all three values to populate the other rows.

Note, though, that it is also possible to solve this problem without any extra scans. First of all, if this were a SELECT statement, you could return the populated row's values in every row by using a window aggregate function, like this:

SELECT
  c1,
  yryryryr,
  power1,
  power2,
  power3,
  populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
  populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
  populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
FROM
  #Rentarious
;

The MAX function works in this situation because it returns the maximum value only across the non-null values in the specified set. In your case, there would be only one non-null value per partition of c1 in each of the three cases, so the function would return that one value. This would be the result of the query for the example in your question:

c1               yryryryr    power1  power2  power3  populatedPower1  populatedPower2  populatedPower3
---------------  ----------  ------  ------  ------  ---------------  ---------------  ---------------
Building Blocks  2012-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2013-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2014-01-01  NULL    NULL    NULL    Red              Blue             Green
Building Blocks  2016-01-01  Red     Blue    Green   Red              Blue             Green
Red Cement       2012-01-01  Pink    Purple  Orange  Pink             Purple           Orange
Red Cement       2016-01-01  NULL    NULL    NULL    Pink             Purple           Orange
Red Cement       2011-01-01  NULL    NULL    NULL    Pink             Purple           Orange

So the only thing remaining would be just to

SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3

And it is indeed possible to do so in SQL Server, because the result of the above SELECT query can be used as the target of your UPDATE statement. You can use it as a derived table:

UPDATE
  tgt
SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3
FROM
  (
    SELECT
      c1,
      yryryryr,
      power1,
      power2,
      power3,
      populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
      populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
      populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
    FROM
      #Rentarious
  ) AS tgt
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

or implement it as a CTE (Common Table Expression) and use the CTE's alias as the target:

WITH tgt AS
  (
    SELECT
      c1,
      yryryryr,
      power1,
      power2,
      power3,
      populatedPower1 = MAX(power1) OVER (PARTITION BY c1),
      populatedPower2 = MAX(power2) OVER (PARTITION BY c1),
      populatedPower3 = MAX(power3) OVER (PARTITION BY c1)
    FROM
      #Rentarious
  )
UPDATE
  tgt
SET
  power1 = populatedPower1,
  power2 = populatedPower2,
  power3 = populatedPower3
WHERE power1 IS NULL
  AND power2 IS NULL
  AND power3 IS NULL
;

Both would work equally well and result in all nulls replaced with corresponding values, i.e. from this:

c1               yryryryr    power1  power2  power3
---------------  ----------  ------  ------  ------
Building Blocks  2016-01-01  Red     Blue    Green
Red Cement       2012-01-01  Pink    Purple  Orange
Building Blocks  2012-01-01  NULL    NULL    NULL
Building Blocks  2013-01-01  NULL    NULL    NULL
Building Blocks  2014-01-01  NULL    NULL    NULL
Red Cement       2016-01-01  NULL    NULL    NULL
Red Cement       2011-01-01  NULL    NULL    NULL

to this:

c1               yryryryr    power1  power2  power3
---------------  ----------  ------  ------  ------
Building Blocks  2016-01-01  Red     Blue    Green
Red Cement       2012-01-01  Pink    Purple  Orange
Building Blocks  2012-01-01  Red     Blue    Green
Building Blocks  2013-01-01  Red     Blue    Green
Building Blocks  2014-01-01  Red     Blue    Green
Red Cement       2016-01-01  Pink    Purple  Orange
Red Cement       2011-01-01  Pink    Purple  Orange


Identifying rows which don't match a master row

You can also do this with dynamic SQL without having to manually build out all the column names.

DECLARE @sql NVARCHAR(MAX), @c1 NVARCHAR(MAX), @c2 NVARCHAR(MAX);

SELECT @c1 = N'', @c2 = N'';

SELECT
  @c1 = @c1 + ',' + QUOTENAME(name),
  @c2 = @c2 + ' AND m.' + QUOTENAME(name) + ' = s.' + QUOTENAME(name)
 FROM sys.columns
 WHERE name <> 'LocationID'
 AND [object_id] = OBJECT_ID('dbo.table1');

SET @sql = ';WITH s AS (
       SELECT ' + STUFF(@c1, 1, 1, '') + ' FROM dbo.table1
       EXCEPT
       SELECT ' + STUFF(@c1, 1, 1, '') + ' FROM dbo.table1_master
     )
     SELECT m.LocationID
 FROM s INNER JOIN dbo.table1 AS m ON 1 = 1
 ' + @c2;

SELECT @sql;
--EXEC sp_executesql @sql;

You can take the output of this query as is and store the query somewhere, or you can comment out the SELECT and uncomment the EXEC and leave it as permanent dynamic SQL - in this case it will automatically adapt to column changes in the two tables.

Another idea (assuming LocationID is unique) - and it occurred to me you may want to include the master row so you can quickly spot the columns that are different:

  ;WITH c AS
  (
    SELECT t.LocationID, m.setting1, m.setting2, ...
      FROM dbo.table1 AS t CROSS JOIN dbo.table1_master AS m
  )
  SELECT DISTINCT src = '> master', setting1, setting2, ...
    FROM c
  UNION ALL
  (
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM dbo.table1
    EXCEPT
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM c
  )
  ORDER BY src;

This version is a little cheaper (mostly by avoiding the DISTINCT against the master table, at the cost of needing to specify all of the columns one more time - which again you can automate as per above):

  ;WITH m AS
  (
    SELECT setting1, setting2, ...
      FROM dbo.table1_master
  ),
  c AS
  (
    SELECT src = RTRIM(t.LocationID), m.setting1, m.setting2, ...
      FROM dbo.table1 AS t CROSS JOIN m
  )
  SELECT src = '> master', setting1, setting2, ...
    FROM m
  UNION ALL
  (
    SELECT RTRIM(LocationID), setting1, setting2, ...
      FROM dbo.table1
    EXCEPT
    SELECT src, setting1, setting2, ...
      FROM c
  )
  ORDER BY src;

However all of these options are poorer performers with worse plans than Rachel's simple LEFT JOIN. I tried to stick to the theme of using EXCEPT even though it is more about syntax than performance.

The key takeaway is that if the column count is too high to deal with manually, you can use the dynamic SQL approach above to construct whatever query you want to use - and you can do that one time and store the result, or have the code generated every time. To generate Rachel's query using dynamic SQL, not much needs to change:

DECLARE @sql NVARCHAR(MAX), @and NVARCHAR(MAX), @anycol NVARCHAR(128);
SELECT @sql = N'', @and = N'';

SELECT @and = @and + ' AND t.' + QUOTENAME(name) + ' = m.' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('dbo.table1_master');

SELECT TOP (1) @anycol = QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('dbo.table1_master')
  ORDER BY name;

SET @sql = 'SELECT locationID
FROM dbo.table1 AS t
LEFT OUTER JOIN dbo.table1_master AS m ON 1 = 1'
  + @and + ' WHERE m.' + @anycol + ' IS NULL;';

SELECT @sql;
--EXEC sp_executesql @sql;


SQL Server Actual Plan. Incorrect row estimates

The question is tough to answer because the query does not match the plan provided, and the query used to generate the plan is somewhat meaningless:

SELECT TOP 50 Id AS [User Link],
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 1 AND
    LastEditorUserId = Users.Id AND
    OwnerUserId != Users.Id
) AS QuestionEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 2 AND
    LastEditorUserId = Users.Id AND
    OwnerUserId != Users.Id
) AS AnswerEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE LastEditorUserId = Users.Id AND
    OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC

Nevertheless:

  What does the operator 'Constant scan' refer to?

A Constant Scan reads from an in-memory table of constant values. It may have one or more rows, and zero or more columns. Yes, a Constant Scan may have zero columns; it is often introduced by the optimizer simply as a source of (empty) rows, to which columns may be added by later operators (typically Compute Scalars).

To a large extent, Constant Scans are often purely architectural artifacts necessary for the correct operation of the query, or as part of an optimization.

When Constant Scans and Compute Scalars sit underneath a Merge Interval, this is a sure sign that these elements were introduced post-optimization to compute and collapse multiple logical intervals into a single interval suitable for driving an Index Seek on that range.

In this particular case, the Constant Scans represent the expansion of OwnerUserId <> Users.Id into:

OwnerUserId < Users.Id; OR
OwnerUserId > Users.Id

The post-optimization rewrite into the Merge Interval subtree is triggered by this x < y OR x > y predicate. The machinery tests the ranges for overlaps and collapses the resulting "merged interval" into start and end points that can be used with an index seek.

The following minimal example shows SQL Server rewriting the predicate in this way so an index seek can be generated:

CREATE TABLE #U (u integer NOT NULL);
CREATE TABLE #P (u integer NOT NULL PRIMARY KEY);

INSERT #U VALUES (1);
INSERT #P VALUES (0), (2);

SELECT
    U.u,
    (
        SELECT TOP (1) 1
        FROM #P AS P
            WITH (FORCESEEK)
        WHERE P.u <> U.u
    )
FROM #U AS U;

DROP TABLE #U, #P

The resulting Clustered Index Seek there has the properties:

The end result is scanning the index forward until the end point (excluding the target value) then restarting the scan just after the target value to the end of the index. [Expr1011] and [Expr1012] are the computed start and end points of the excluded range.

You can read more about this in my article Dynamic Seeks and Hidden Implicit Conversions.

  Why row estimates can be incorrect in the following blocks

SSMS shows estimates on the inner side of a nested loops join per iteration whereas the actual row counts are the total over all iterations. This is confusing to many people, and the result of a questionable design decision.

    Posted by Microsoft on 10/14/2009 at 2:47 PM
    The difference between estimated and actual that you refer to is not really a bug in the optimizer; it's a questionable design decision in the way we present information in showplan. This table is on the inner side of a loop join, and in that position the numbers we report for actual and expected rows don't mean the same thing: The actual row count includes all exections, while the estimated row count is per execution.

You can get a more natural comparison by viewing the plan in Sentry One Plan Explorer. It does the per-iteration calculations for you, so you can compare estimates and actuals more directly. For example, this is a view of one of the Merge Interval subtrees viewed in Plan Explorer:

  What to do to make estimates more accurate? What index/technique to consider to improve the query performance?

The difference in showplan display accounts for the first question. David's answer has addressed rewriting the query for better performance (and meaningful results).


Increase in temporary tablespace while import

The size needed in a temporary tablespace depends on the data volume manipulated, but including potentially other actions also requiring temporary space. What you should be doing is this : monitor temporary tablespace usage, (temporarily) expand the temporary tablespace.

Maybe, you need to recreate your temp tablespace. That is, if there is corruption of some kind. One way - but not the only one - is stopping the database, rename the file(s) of the temporary tablespace, tail the alert.log, and startup the database. Obviously, don't do without backup, and don't test this on production.


Update field1.table1 based on compound join between table1 and table2 - Oracle

update table1 set flag_column = 1
 where exists (select 1 from table2
                where table1.columnx = table2.columnx
                  and table1.columny = table2.columny)

This will update every row in table 1 where the x, y column combination exists in table2.


Microsoft has guidance for your scenario where upon upgrading you experience query performance degradation. You may not be able to follow the exact steps outlined here if you were upgrading from a version without Query Store, but the general principles are still the same. It is expected that some queries will run slower after upgrade as Microsoft makes changes to the cardinality estimator. A first defense against that is to find the queries that are running for too long and to force them to use a previous plan. Methods of doing that:

Forcing a previous good plan with Query Store
Setting compatibility level at the database level or query level
Enabling the legacy CE at the database level or query level

For the root cause of why this particular query is slow, 98% of the query's time is spent on a nested loop join against Object4. It is hard to give specifics for an anonymized plan, but the following broad principles apply:

Use a temp table or some other method to improve the cardinality estimate coming into the nested loop join. The optimizer is likely picking a loop join because the estimate is just 1 row.
Define an index on Object4 which allows for more effective seeking against that table. This can only be a partial fix and won't get the query execution time down to 8 seconds.
If required, force a different join type for the table access on Object4. The nested loop join reads a total of 173,354,476 rows from a table with just 38,775 rows. A hash or merge join would be more efficient.
If required, achieve a different join order.

As a final note, you really can't expect to get good cardinality estimates for query constructs like this:
Function2(Function3(Object3.Column38)) LIKE Function2(Function3(Object2.Column11)) + ?

It may be true that the legacy cardinality estimator gives you good performance just due to luck. To keep the same performance on a newer CE you may need to use the invasive techniques that I described above.


PostgreSQL Continuous Archiving & Point-in-Time Recovery in combination with netbackup

There is one crucial thing wrong here: you never, ever delete files from pg_wal.
You need to configure archive_mode = on and set archive_command so that PostgreSQL archives transaction log files when they have been completed.
Much simpler than creating a pg_basebackup on a local disk and then backing that up would be to backup the data directory directly. You have to get PostgreSQL into backup mode first (pg_start_backup) and end backup mode once you are done backing up files (pg_stop_backup). See the documentation for more.


How to handle a many to many relationship with varying number of columns needed?

The preferred method is to use an intermediate table to just intersect friends and hikes.  Then join through that table to list off friends who have gone on which hikes.

I've included a sample below as well as a sample query that shows each hike and the friends that have gone on that with you.

CREATE TABLE dbo.Hikes
    (
    HikeID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , LocationID INT NOT NULL
    , Summit NVARCHAR(100) NULL
    , DateStart DATETIME2 NOT NULL
    , DateEnd DATETIME2 NOT NULL
    )

CREATE TABLE dbo.Friend
    (
    FriendID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , FullName NVARCHAR(100) NULL
    , IsRealFriend BIT NULL DEFAULT 0
    )

CREATE TABLE dbo.HikeFriend
    (
    HikeFriendID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , HikeID INT NOT NULL REFERENCES dbo.Hikes (HikeID)
    , FriendID INT NOT NULL REFERENCES dbo.Friend (FriendID)
    )

CREATE UNIQUE INDEX IDXU_HikeFriend ON dbo.HikeFriend (HikeID, FriendID)

--Sample Query
SELECT H.LocationID
    , H.Summit
    , H.DateStart
    , H.DateEnd
    , F.FullName
FROM dbo.Hikes AS H
    LEFT OUTER JOIN dbo.HikeFriend AS HF ON HF.HikeID = H.HikeID
    LEFT OUTER JOIN dbo.Friend AS F ON F.FriendID = HF.FriendID
WHERE H.Summit = 'Kilimanjaro'


Can database administrators change physical schema?

One of the innovation of the relational model was to separate thr logical view that programmers see (tables of columns) from the physical storage (files on disks). To function the DBMS must be aware of both and hence must have the ability to amend both. So yes, if you define DBA as "someone who manages a DBMS" a DBA can change the physical schema.
Most DBMS implement a permission system. Different permissions will affect the creation of tables and the use of disks. In smaller teams it may be that one person has granted both of these permissions. In larger teams it becomes more likely the tasks are separated. You will find application DBAs defining tables and queries and operational DBAs looking after disks and CPUs. In businesses dealing with financial data or PII it may be that neither of those roles has access to any of the data.


Backup database and clean old backup question

You should probably move from maintenance plans to SQL Agent Jobs and leverage a script like those written by http://ola.hallengren.com/.  You would create a Job Step for each database.  It will give you the flexibility that you are looking for.

UPDATE: My info above was incorrect. You can use the Ola.Hallengren.com script to do what you are looking for without having to add a job step for each database.  After reviewing the script info I saw this:

From: http://ola.hallengren.com/sql-server-backup.html

  CleanupTime

  Specify the time, in hours, after which the backup files are deleted.
  If no time is specified, then no backup files are deleted.

  Backup files are deleted after each database is backed up and
  verified. Backup files are deleted only if the backup and verification
  of the database were successful.

  DatabaseBackup has a check to verify that transaction log backups that
  are newer than the most recent full or differential backup are not
  deleted. This is to guarantee that you can always perform a
  point-in-time restore.


SQL Server permissions to create, restore, delete only some databases

Yes it is possible through a couple permissions.

First - to create a database - You would need to grant the server level permission Create Any Database. This permission does just what it sounds like - the power to create a database. Note this is not giving the fixed server role of dbcreator to the login, since that fixed server role gives alter/delete permissions on any database. Create Any Database allows the login to have that power over databases they own, only. This will also give the ability to restore.

(So by doing "Create Any Database" ONLY - you are giving the ability to create a new database, but avoiding the ability to Drop or Alter ANY database that you'd get by using the dbcreator fixed server role)

To grant this "Create Any Database" permission -

use [master]
GO
GRANT CREATE ANY DATABASE TO [LoginName]
GO

For the ability to run DBCC CHECKDB and to drop databases - the fixed database role db_owner is sufficient. This grants all of what you are requesting. Please Note: You are also granting this user the ability to select, delete, truncate, update and insert with db_owner. I like to think of this as sysadmin within a database.

You only will apply this permission to the database you intend to have this ability on. This will allow you to control which databases the user can drop, restore or checkdb on. Any databases that you have not given the user these permissions on will be safe from this user doing these actions on

To do this you would first map the login to a database user:

USE [DatabaseName]
GO
CREATE USER [UserName] FOR LOGIN [LoginName]
GO

And then add that user to the db_owner role (Works in SQL Server 2012 and on):

ALTER ROLE [db_owner] ADD MEMBER [frank]
GO

For SQL Server 2008 you would have to use the system stored procedure to add a role member as a commenter below rightly pointed out:

EXEC sp_addrolemember 'db_owner', 'frank';

You can also do all of this through the GUI. The login information you would do through the security folder in SSMS at the instance level: Right click on the login -> Properties -> Securables. For the database level, you would do it through the security folder at the database level: right click and select new user -> choose the login you wish to user from your list of server logins/give the database user a name -> go to the membership tab to select the role membership.

SQL Server's help system, Books Online, is a fantastic resource for most permissions questions also - if you determine you need to assign other permissions. Simply do a search for the T-SQL command you wish to give permissions to and there is typically a Permissions section for that command in the article letting you know what permissions are required to do this action. You can look at the article for DBCC CHECKDB for an example - about 7/8 of the way down the article is the permissions section.


Logging only mySQL logins on heavily used system

I really wish I could take credit for this, but I can't.  I chanced across this while thinking about your question:

http://www.fromdual.com/mysql-logon-and-logoff-trigger-for-auditing

It never occurred to me to call a stored procedure from init_connect... but I (somewhat enthusiastically, I might add) tested this solution before posting it here, and found that it works exactly as described, on off-the-shelf MySQL 5.5.30.

It logs every login of every user who lacks the SUPER privilege.  This is a bit of a limitation, except, of course, for the fact that a SUPER user could manipulate the audit table anyway.

To capture the logouts with similar logic, you have to modify the server source, since init_connect does not have a built-in counterpart that gets executed at the end of the session.


Job showing the running sign but not currently running

Use script instead to check status of currently running job.

SELECT  job.name,
        job.job_id,
        job.originating_server,
        activity.run_requested_date,
        DATEDIFF(MINUTE, activity.run_requested_date, GETDATE()) as Elapsed,
        case when activity.last_executed_step_id is null
             then 'Step 1 executing'
             else 'Step ' + convert(varchar(20), last_executed_step_id + 1)
                  + ' executing'
        end
FROM    msdb.dbo.sysjobs_view job
        JOIN msdb.dbo.sysjobactivity activity ON job.job_id = activity.job_id
        JOIN msdb.dbo.syssessions sess ON sess.session_id = activity.session_id
        JOIN ( SELECT   MAX(agent_start_date) AS max_agent_start_date
               FROM     msdb.dbo.syssessions
             ) sess_max ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE   run_requested_date IS NOT NULL
        AND stop_execution_date IS NULL


SELECT describable_id
FROM table
WHERE (property_id, value) IN ((6, 'Red'), (4, '150'))
GROUP BY describable_id
HAVING COUNT(DISTINCT property_id, value) = 2

For strongly 2 pairs

SELECT DISTINCT t1.describable_id
FROM table t1
JOIN table t2 ON t1.describable_id = t2.describable_id
WHERE t1.property_id = 6
  AND t1.value = 'Red'
  AND t1.property_id = 4
  AND t1.value = '150'

If (property_id, value) is unique (by index!) you may remove DISTINCT from both queries.


Copy primary keys/clustered indexes from Prod to DEv/Test env

For a start unless this was an absolute emergency you have done everything backwards: you should not make changes to production that have not been made in dev/test first to make sure they have the desired effect. Develop, test, then release the changes to production.

Setting that aside: There are a number of tools out there that claim to compare schema, and some try to generate a script to bring one schema into sync with another reference one (though I'd be wary of running such a generated script without a detailed review first!).

http://opendbdiff.codeplex.com/ came up in a quick search though I've not used the project myself. It seems to fit the need you describe. There are also products from RedGate & other SQL related software houses (search for "schema compare") and a tool built in to some editions of Visual Studio which your developers may already have available.


DatabaseMail didn't start when an email is queued

see what happens when you try to send an email using powershell instead.

$smtpServer = "200.1.1.223"
$smtpPort = 25
$emailFrom = "donotreply@xxxxx.co.uk"
$emailTo = "mmiorelli@xxxxx.co.uk"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Port = $smtpPort
$subject = "ccatsql"
$body = "test email from ccatsql "
$smtp.Send($emailFrom, $emailTo, $subject, $body)

and checked which .Net versions you have installed

the problem might be in the .NET. Check the windows\applications error log.
I had a similar problem (see the question here) then I installed .NET 3.5 and all started to work nicely.

SQL Server 2016 - Database Mail doesn't work without .NET 3.5

How can I see the current Database Mail configuration?

EXEC msdb.dbo.sysmail_help_configure_sp;
EXEC msdb.dbo.sysmail_help_account_sp;
EXEC msdb.dbo.sysmail_help_profile_sp;
EXEC msdb.dbo.sysmail_help_profileaccount_sp;
EXEC msdb.dbo.sysmail_help_principalprofile_sp;

EXEC msdb.dbo.sysmail_help_account_sp

Have a look at this question and answer:
Comprehensive troubleshooting of DatabaseMail


Create Temporary Access to production support

We looked, and didn't find a packaged product that met our similar needs. We wanted to be able to get away from having so many developers with dbowner (and even sysadmin) access to databases and instances.
We ended up writing our own desktop application. It doesn't quite match your requirements, but it is close.

The user runs the application, which authenticates them via Windows authentication. There is a lookup that then determines which instances/databases they can request elevated permissions to. And how far they can elevate. All of which has to be approved ahead of time by their supervisor. There are four options for elevation level: (lowest to highest )

R+W – Read and write only on a specific database.
DDL and R+W – You can create objects, change schema on a specific database as well as read and write.
[DB]OWNER – Full access to a specific database.
SYSADMIN – Grants you full access to the instance and all the databases.

They also have to fill out a field to explain why they need the elevated permissions. The program then creates a temporary SQL login for them that they can use to do their elevated work. (We tried to do it by adding them to an Active Directory group, but something didn't work right. I don't remember what it was. We'll try that again some day.)
It logs the fact that they asked for elevated rights to a table that we can then run reports from. We also set up our third-party compliance auditing tool to specifically audit all activities from these temporary elevated logins. The process also sends an e-mail message to the person's supervisor whenever they request elevated access.
We then have a scheduled job that removes these temporary logins at 6:00 a.m. the next day.


MySQL slave replication permission error

you may need to grant super privileges to the user. Replication client is responsible only to replicate binary logs to slave


Full to Simple recovery mode during massive data update?

The only thing you're really changing by using simple recovery is you're shifting the time when you have to perform additional backup work. Instead of taking log backups throughout the process (which shouldn't be extra work, because you should already be doing that!), now you have to take a full backup at the end of the process. In the meantime, you've opened yourself up to real problems because should anything go wrong between the start of the operation and the completion of the full backup, you are going to be in some hot water because you've lost the ability to recover to a point in time.

(Since the bulk of your activity is delete, you can't even benefit from minimal logging - and even if you could, Thomas' advice to use bulk-logged is a better approach than switching to simple - this still allows you to recover to a point in time that has been backed up by the log and is not inside a minimally-logged transaction.)

As you're planning to delete 90% of your data, you may want to think about this in a different way: move the data you want to keep to a new table, then drop the old table and rename the new one. The stuff that costs a lot of log is when there is a lot of data movement. DROP is logged too, but unlike DELETE, only the deallocation, no movement. You'll need a little extra space to support this (~10%) but you'll also avoid any after-the-fact rebuild.

All of these options involve trade-offs, but I think your plan to change recovery to simple offers the least benefit and introduces you to the most risk. I wouldn't do it without having your resume dusted off.


How to create a database from an XML file's DTD

Use the following tools:

  1) create full sql schema from DTD.

DTDParse + XML::RDB

  2) convert XML doc with data into SQL.

https://stackoverflow.com/questions/13121456/transform-xml-using-xslt-to-sql

References

DTD Tools (the link no longer works)
XML Matters: Using Python modules xml2sql and dtd2sql


Select row with MAX(), or choose one if no MAX() exists

It is possible with UNION ALL. Basically, you have three criteria, therefore you need three SELECT statements. To simplify the select statements, lets create views from your selects as follows:

CREATE VIEW data1 AS
SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp <= t0
AND data.event <> 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event <> 'stopped';

CREATE VIEW data2 AS
SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp < t0
AND data.event = 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event = 'stopped';

Now, the select statement will be as easy as this one:

select data2.*
from data1 inner join data2
on data1.name_id = data2.name_id and data1.time_stamp = data2.time_stamp
UNION ALL
select data1.*
from data1 inner join data2
on data1.name_id = data2.name_id and data1.time_stamp > data2.time_stamp
UNION ALL
select data2.*
from data1 inner join data2
on data1.name_id = data2.name_id and data1.time_stamp < data2.time_stamp

SQL Fiddle (I used INT datatype for time_stamp column in the SQL Fiddle, but it shouldn't matter, as the principle is the same for timestamp datatype)

You do not need to use VIEWs, you can embed you queries into the above statement as subqueries. I have used the views just to demonstrate the technique


Even though it's a bit late, I'm going to field a response with hope that it helps or at least spurns some additional ideas/commentary on this issue because I think it's a good question.

First, and I don't know if you're doing this or not, but please don't assume that high fragmentation levels on the index are always going to cause poor performance.  Stale statistics (e.g. sys.dm_db_stats_properties) and high amounts of white space per page (i.e. avg_page_space_used_in_percent column in sys.dm_db_index_physical_stats dmv) hold more relevance regarding performance issues than fragmentation alone.  Yes, highly fragmented indexes will generate more read-aheads and you typically do see stale statistics and higher levels of white space per page coupled with fragmentation, but fragmentation isn't directly tied to query plan optimizations nor how much memory loading the index from disk will actually consume.  Query plans are affected by statistics and your memory footprint bloats with more white space.  For instance, an index that is 99% fragmented but has less than 5% avg. white space and up-to-date statistics is likely not causing you drastic performance issues as compared to either a bad execution plan as a result of stale statistics or constant paging of an index that's too big to fully fit in memory because there's a significant amount of white space present per page.

If fragmentation is truly an issue, you can reduce it, ONLINE, by issuing an ALTER INDEX ... REORGANIZE statement as identified by Dan Guzman in the comments.  This won't create as streamlined an index as a REBUILD operation will, but it will reduce your fragmentation.  The key here is to identify windows of lower usage on your database and run it then.  This could be 15 minutes or multiple hours, obviously the longer the better, but the key here is this operation doesn't rollback and retains any progress made even if you kill it mid-execution.

If, in a perfect world where your fragmentation was eliminated, would it make more sense to utilize partitioning on this table?  Azure SQL Database does allow for table partitioning and Microsoft has a great article outlining some Partitioning strategies for Azure SQL Database.  If your data is non-volitile, partitioning it may help reduce maintenance needs, and if coupled with Table Compression, you may even be able to reduce your overall storage footprint as well.  Alberto Murillo's earlier answer alludes to utilizing Horizontal Partitioning based on a data region, and this approach may help create some maintenance windows for you as your data would be more regionally specific instead of global.

Transitioning to a partitioned table won't be easy with your current absence of maintenance windows, but you may be able to utilize an approach outlined by Maria Zakourdaev which uses Partitioned Views over the top of your current table and a new partitioned table to start partitioning future data.  As time goes on (and hopefully your old data is purged), you can eventually transition fully over to the partitioned table.  Again, I don't know your data or application, but maybe this approach is something you can employ.


Help to understand explain plan in Oracle

Explain plan does not tell you what is actually the most costly "operation". The "Cost" column is a guess - it is a value estimated by optimizer. So is "Cardinality" column and "Bytes" column. http://docs.oracle.com/cd/B28359_01/server.111/b28274/ex_plan.htm#i18300

In your example, your optimizer tells you:  I decide to use this plan because I guess that looping would cost about 5,483. And I hope this would be the most costly part of the execution, but I can't guarantee this.

The same applies recursively to all the depths of the tree.

If you go in-depth to the lowest levels (that is by intuition most-looped, most-executed levels) you see that the operation that especially sticks out, both in terms of expected cost and expected number of elements, is the

6 INDEX RANGE SCAN INDEX RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM Cost: 662  Cardinality: 102,068

So, optimizer guessed that optimal execution of this query is to loop a lot around a poor workhorse RAIDPIDAT.IDX_HISTORY_STATE_TABLE_1TPALM. I really cannot see which part of your query directly relates to it, but I suspect t1.data_tratado condition. And, again, I cannot see if it is really the most costly part.

I'll try to translate the syntax of loops in the explain plan to procedural pseudo-code:

/* begin step 13 (by "step 13" I mean a line that reads "   13 NESTED LOOPS") */
  /* begin step 7 */
    do step 5
    myresult = rows from step 5
    for each row from myresult {
       do step 6
       for each row from step 6 {
           join to a row from myresult the matching row from step 6
       }
    }
  /* end step 7 */
  for each row from myresult {
     do step 12
     for each row from step 12 {
         join to a row from myresult the matching row from step 12
     }
  }
/* end step 13 */
return myresult

Seems complicated, but really aim of each "nested loop" is to create a join (a single table made of two tables) in the most naive way, a loop-inside-a-loop.


Change Tracking Retention from a backup?

According to TechNet Change Tracking is not supported across a data restore - for not only the complexities you reveal in your question but more specifically because clients who might be keeping track of version info separate from the server will now be out of sync.  Read the technet article for more info.

If client version tracking doesn't apply to your scenario and you really need the change history you can most likely keep the server from auto-cleaning it after a restore.  The reason I say this is that the auto_cleanup task is a background thread that runs every 30 minutes (according to Microsoft).  So immediately after a restore you should have a small window of time to turn the auto_cleanup setting off and maintain any change tracking retained within the table.  But yes, if you leave auto_cleanup on it will be gone the next time the task runs.

As for your follow up question.

Paraphrase: Is retention period of 1 day a 24 hour period or a calendar day?

I can find nothing official but my gut says it's got to be a 24 hour period.  Making a change at 11:30PM shouldn't disappear an hour later (12:30AM) if retention is set to 1 day.

I have no documentation to back this up - just my gut on that one.  But I will have proof in 24 hours as I am testing it as I type :)

Follow up question test: UPDATE

My test has proven inconclusive.  I set retention for 1 day and 48 hours later and the change tracking info has still not been removed.  I tested a different table with a 1 hour retention period and it was removed 86 minutes later.  So I can not definitively answer if 1 day period is 24 hours or a full calendar day.

Microsoft documentation says that retention period refers to the minimum amount of time it should keep the data.  So the one thing my testing has proven is it doesn't mean it will be deleted the very moment that time period elapses.


How to select and show all the tickets from last calendar year?

Depending on your needs, you can use:

Using extract:

select *
from tickets
where extract(year from insert_time) = 2016

Using between:

select *
from tickets
where insert_time between timestamp'2016-01-01 00:00:00.0' and timestamp'2016-12-31 23:59:59.99999'

Depending on the volume of data, and the presence of an index on insert_time or not, the second may be preferable over the first.


Equivalent of utf8_general_ci in Postgres/ICU?

The dotless I is a special case. It's processed by the ICU collation service with rules that depend on the language.
If the locale refered to the Turkish or Azerbaijani languages, it would produce the result that speakers of these languages might expect (that is, i and ı are two different letters with İ and I being their respective uppercase counterparts; cross-comparisons return false). Otherwise the result is normally that i is the lowercase version of I, whereas ı is not.

postgres=# CREATE COLLATION "undefined_ci_nondet_old" (
  PROVIDER = 'icu',
  LOCALE = 'tr@colStrength=secondary',
  DETERMINISTIC = false
);
CREATE COLLATION

postgres=# SELECT 'ı' = 'I' COLLATE undefined_ci_nondet_old;
 ?column?
----------
 t
(1 row)

postgres=# select 'i'='İ' COLLATE undefined_ci_nondet_old;
 ?column?
----------
 t
(1 row)

postgres=# select 'i'='I' COLLATE undefined_ci_nondet_old;
 ?column?
----------
 f
(1 row)

postgres=# select 'ı'='İ' COLLATE undefined_ci_nondet_old;
 ?column?
----------
 f
(1 row)


SQL Server 2008 Cluster Across Two VMWARE Hosts

As said in the comments, I can (almost) conclusively confirm that you will require shared raw LUNs with RDM, when you want to cluster 2 x virtual (vmware) SQL Server nodes that are on different hosts, instead of using VMFS.

The VMWare documentation also explicitly mentions the use of unformatted LUNs

*I guess (and I have never done touched on it before) the only possible exception that spring to mind may be in you use SQL Server 2012 AlwaysOn on SSDs. Oh, and possibly iSCSI, which is as you said, not an option. *


how to select the particular records with filter conditions when duplicates found in combination of two columns?

You'll need the RANK function (here is documentation, assuming you're using Oracle). You basically group the records (normally you'd do that with GROUP BY, here it's the PARTITION BY clause) and rank/order them within that group. Then, you select the records with rank 1:

SELECT s.no FROM (
  SELECT s.no, RANK() OVER (PARTITION BY name, last_name
                            ORDER BY status ASC, join_date DESC) AS rank
    FROM school
) temp WHERE rank = 1

(I'm not really familiar with Oracle, I hope this compiles; it would work on sql-server.)


How to switch between Oracle 11g and 12c on same linux

Check the /etc/oratab file. In there the ORACLE_HOME is defined in the second field. Make sure that both instances point to the right ORACLE_HOME. If you do an  . oraenv to change from one instance to another this scripts sets the correct ORACLE_HOME so you connect with the right binaries to start, stop, and access the instance. The file is normally also used to stat-up the instances at boot time.

Example of a line in the /etc/oratab file:

DEVA:/u01/app/oracle/product/11.2.0/db_2:Y

DEVA is the name (SID) of the instance, /u01/app/oracle/product/11.2.0/db_2 is the ORACLE_HOME, and Y indicates that the instance must be started at boot time.

You say that you have no DB background so be aware that you cannot run the same instance in both versions of Oracle.


Why is using a CTE so much faster than using a #Temp Table in this query?

I believe if you look at the execution plans you'll see that the CTE gives you a better plan because it can take better advantage of the TOP. The Temp table will have to create the table in TempDB, persist all of the data to disk, and then select the top record. If you remove the TOP from both queries I bet you'll get much closer performance. That being said I would generally expect the CTE to out perform the temp table in a single use scenario like you have here since the CTE is doing less work than creating/inserting into a temp table.

This is probably just sample code but it is worth mentioning your results are non-determinate because you're using TOP without an ORDER BY clause.

For further reading you should check out this question. This answer to that question seems to apply to your situation in particular.

For the implied second half of your question:

Since you say you need to use the data from the CTE at least twice then you have four options:

Copy and paste your CTE to both places
Use the CTE to insert data into a Table Variable, and use the data in the table variable to perform the next two operations.
Use the CTE to insert data into a Temp Table, and use the data in the temp table to perform the next two operations.
Write a better tailored CTE. I assume you're doing different things so the queries must be slightly different.

I tend to prefer the option 2 (table variable) or option 4 (tailored CTE's) approach. I don't like the duplication and extra maintenance of copy/pasted CTE's. I also like the explicitly reduced scope of the table variable over a temp table. For an authoritative treatment on the differences between table variables and temp tables check out this answer. As far as choosing between options 2 and 4; that will depend on whatever your profiling dictates and/or what constraints you have to work within.


In InnoDB, how does fuzzy checkpointing's recovery consistency work?

(I'm pretty sure of the following.)

InnoDB does not depend on "dirty" pages for recovery.  Recovery is guaranteed by what is stored in iblog* and the double-write buffer.

The presumption is that the information about a transaction can be more compactly stored, and more rapidly written to disk, in the redo log (versus the actual table).

The log files are overwritten, but not until LNS says it is OK.  So, the optimal dirty page to flush is either the "least recently used" or the one with the oldest position in the log.  I don't know what algorithm it uses to decide between these conflicting things.

If there is a lot of activity causing the percentage of the buffer_pool to be 'too close' to 100% dirty, InnoDB shifts gears and becomes more aggressive at flushing dirty pages.  This, also, is a tradeoff.

Note also that the writing of changes to non-unique secondary indexes is also 'delayed'.  This is in the "change buffer", which (by default) occupies 25% of the buffer_pool.  The hope with that is that the updates can be somewhat sorted and written to disk with fewer read-modify-write cycles.  Again, recovery does not depend on this having been completely flushed to disk, and the redo log is the critical part.

The double-write buffer protects against "torn-pages".  This is a potentially disastrous situation where the disk subsystem write can't write all 16KB in an atomic operation.  A few newer disks guarantee atomicity, so the setting can be turned off.

InnoDB is crash-proof.  But, it is also "fast" because of delaying I/O, together with these various techniques that work efficiently under high load.


ORA-02292: integrity constraint violated - child record found while creating procedures in ORACLE SQL Developer?

No code needed for this, just define the constraint with ON DELETE CASCADE option.

alter table orders drop constraint ORDERS_FK_CUSTOMERS;
alter table orders add constraint ORDERS_FK_CUSTOMERS
  foreign key (customer_id) references customers(customer_id) on delete cascade;

Same for order_details.


Calculating Running Average using Over clause in SQL server

You can apply your final filter on @dekade after computing the running averages.

In order to reduce the number of rows that need to be processed for the running averages, you can apply an earlier filter on [dekade] IN (@dekade, (@dekade+1)%36, (@dekade+2)%36) to ensure that you are processing the minimal amount of rows but still including all the rows that are necessary to including the following 11 rows in the running average. (The only reason for the % 36 is to handle values at @dekade that fall at the end of the year.)

This will still result in a table scan given your current table structure, but at least the rows can be filtered out earlier on in the query plan.

DECLARE @dekade TINYINT = 1
SELECT *
FROM (
    SELECT
        LOCID,
        [Year],
        [Date],
        [dekade],
        AVG(cast(RTT as float)) OVER
            (PARTITION BY LOCID ORDER BY Date
                ROWS BETWEEN CURRENT ROW AND 11 FOLLOWING) AS avgRTT
    FROM TimeSeries
    -- If you want to limit the rows that are use when computing the running average,
    -- you can make sure that only the desired @dekade plus the following two @dekades
    -- (which may be needed to get the following 11 rows) are used for each year
    WHERE [dekade] (@dekade, (@dekade+1)%36, (@dekade+2)%36)
) x
-- Filter your results be @dekade after computing the running average
WHERE x.[dekade] = @dekade

It would be helpful to post some sample data so that we can actually run the query though.


Will MySQL redirect a read request to the master after a certain time?

Why do you mean by "certain time"? Replication Lag? Query Response Time?

In a simple Master/Slave(s) infrastructure, the distribution of the reads/writes operations should be done with a load balancer or directly in your apps.

Max.