Select only neighbour rows who pass a where-test

I don't know if this will work in sqlite3, but you didn't tag the question with that, so here is an Oracle solution.

SELECT ID, Speed FROM
(
    SELECT ID, Speed, MIN(Speed) OVER (ORDER BY ID) InSequence
    FROM
    (
        SELECT ID, Speed, SUM(Speed) OVER (ORDER BY ID) SpeedSum
        FROM location
    )
    WHERE SpeedSum > 0
)
WHERE InSequence > 0
ORDER BY ID;

The SUM(Speed) analytic removed the leading zero speed rows and the MIN(Speed) analytic allows us to eliminate rows starting with the first zero speed row remaining.  The sample data can be created as follows:

drop table location;

create table location as (select level id,
   case when level between 3 and 5 then 3
        when level between 9 and 11 then 3
        else 0 end speed from dual connect by level <=12);

select * from location;

It seems to behave correctly through all the following variations, but there could be a pattern I've missed:

UPDATE location SET Speed=1 WHERE ID=2;
UPDATE location SET Speed=2 WHERE ID=3;
UPDATE location SET Speed=1 WHERE ID=6;
DELETE FROM location WHERE ID IN (7,9);

UPDATE location SET Speed=0 WHERE ID=2;
UPDATE location SET Speed=5 WHERE ID=1;

This solution only requires one full table scan rather than the two Noam's solution requires, but it only finds the first set of values whereas Noam's finds all the sets.  It also assumes a threshold of zero, but could be modified for any threshold.


What is the advantage of partition Date field into individual Year(int), Month(int), Day(int), Time(time) columns?

I guess it depends on how you want to use that data. There may be some advantage to the application using it. For example it might make reporting or filtering by year or month easier.

But I've never heard of that as some kind of best practice on its own.

The downside of this approach is that you lose the ability to use the standard date functions in SQL. Simple things like duration between 2 dates or times is simple using datetime fields but a nightmare with ints. You also lose the inherent validation of a proper date datatype. Ask your  boss if there is anything stopping you from inserting 30th of February or a 13th month.


Sql Server, Is "Reorganize Files Before Releasing Unused Space" better or worse?

That is the equivalent of shrinking. To prove it, check the box and click the Script button at the top to see the shrink commands that it produces.

Generally speaking, that’s a great learning tool too - whenever you want to learn what something in the UI does, try that script button.


If you are expecting a rebuild to make the data files in the file system smaller, this is not how it works.

In fact, rebuilding can even lead to larger data files on disk, as new pages and extents are allocated to hold the copy of the data. When the old pages are dropped from the index, they're not actually removed from anything, they're just deallocated.

You've rebuilt your indexes and saved some space inside the data file. This means that as you add more data (to this and other tables), the data file doesn't immediately have to grow to make room for the new data.

This is a good thing. Growing a file is an expensive, blocking operation, and you want to minimize this. Shrinking the file to get back some disk space temporarily seems like a lost cause. The data file is just going to grow again later, in which case, what are you going to do with that disk space you got back for a little while? Lease it out to someone and then evict them when your database has to grow again? Just leave it at the size it has grown to now, because unless you mark it as read-only, it will more than likely grow to that size again.

The data file is just a container. Left alone, it will only ever grow. It won't shrink just because you've deleted some rows or rebuilt some indexes or dropped a table. SQL Server doesn't release that disk space and shrink the file for you for the same time it won't release memory back to the operating system just because you've dropped an index that happened to be loaded into the buffer pool: it assumes you're going to use that space / memory again, and knows that it's expensive to keep releasing it and re-claiming it. So it just holds onto it.

And, in almost all cases, you're going to want it that way. If you really need to reclaim some space that you know you won't use again, see this post.


Difference between reorg-rebuild and index re-create in Sybase ASE

Reorg rebuild vs drop/create index

reorg does not perform sort.
reorg uses the index to retrieve rows while rebuilding the table.
reorg rebuils is minimally logged(should be faster than creating clustered index).

Reorg usage

All reorgs for indexes can only run on DOL tables.
Reorg for tables can run on APL and DOL.
Cannot run on corrupt or suspect index.
Cannot run on text or image chain.
Cannot run within a transaction.

Rebuilding indexes using reorg (reorg rebuild tablename indexname)

Not necessary to set 'select into'.
Less free space required.
Dump tran can be done after rebuilding index using reorg.
Only available for DOL tables.
Works in small transaction; de-allocates pages once copied.
Only the index pages are rebuilt, data pages untouched, not locked.
Only a small number of index pages are locked at any given time.
Estimate time to complete : approx 2-3 times of 'select count(*) from tablename(indexname)'.
If commannd interupted, index is consistent.
If command reissued, starts afresh but does not rebuild those parts of index that already has the desired space usage and page clustering.


Why Does the Update Statement also updates indexes?

For consistency. There are times the access path will use only the data from the indexes or will start from the index and jump to the table. In both cases the informatoin on these two entities should be compatible.

A index is just a separate ordered set of data from a table. When you remove an entry from the table, the entry on the index pointing to this row should also be removed. The same should happen when you update/insert. That's the burden of indexes: faster select and slower DML (insert, update, delete).

When you do, for example, a

select count(*) from table t1 where c1 = :value;

Your result should be the same whether you have indexes or not. And the beauty of Database Management Systems is that they do that automatically for you. :)

You cannot prevent this update. The only time this will not happen is when you do not change the data referenced on the index.
If you have a table with four columns (a, b, c, d), and two of them are indexed (a, b), updating only the other two (c, d) will not trigger this extra update.


How to make 6 partitions of table by 6 months and 7th partion of older than 6 month data?

Possible, but efficiently.  Only works for PARTITION BY RANGE.

Let's say you are starting to populate partition "2106_09" and need to migrate data in partition "2016_02" to partition "Older".  The command would be something like

ALTER TABLE tbl
    REORGANIZE PARTITION `2016_02`, `Older`  INTO (
        PARTITON `Older`  LESS THAN ('2016-03-01')
                 );

(Caveat: check the syntax.)

Note that you will need to construct this with a different partition name and "less than" each month.

But the problem is that it will rebuild "Older", which will take an amount of time based on how big it is.

This . may have some tips and code that is useful.


SQL Server 2012 - Multiple named Instances with Always On

It doesn't really matter which one you pick. If it were my servers though, I would use the same instance names on each server. So INST1 would appear on SERVER1 and SERVER2, and then you'd AG INST1 with INST1.

But I don't like stacking instances on a server as it makes it hard to troubleshoot and do maintenance. If these are virtual machines, setup another virtual machine to host the other instance, or in your case setup two more virtual machines.


sys.master_files is a DMV for databases, so it is by design that it does not show drives that don't have databases.

If you need this functionality, consider writing an xp_cmdshell script inside your job to get this information. You'll need to know Powershell.


Restore a database to different databases without losing any data

You cannot backup the structure and restore that structure to another DB using SSMS; though there are third party tools that can help you, like Redgate's SQL compare which I have used previously for this.

One suggestion is to save your TSQL scripts, and then you can apply these scripts to the other DBs by using Powershell.

Another option is to use SQL CMS, but you will need at least one designated SQL 2008 instance.


Is there a more concise way in Postgres to extract a portion of a string?

You can use regexp_replace() to replace everything after the first ? with nothing:

select regexp_replace(url, '\?.*$', '')

The following example:

with data (url) as (
   values
    ('http://foo.bar/some/page'),
    ('http://foo.bar/some/page?id=42&name=Marvin')
)
select url, regexp_replace(url, '\?.*$', '') as clean_url
from data;

returns:

url                                        | clean_url
-------------------------------------------+-------------------------
http://foo.bar/some/page                   | http://foo.bar/some/page
http://foo.bar/some/page?id=42&name=Marvin | http://foo.bar/some/page


MySQL Workbench: "Operation has completed with warnings"

The message is a bit unconcrete, so here's a translation: it appears when the statement 'SELECT @@lower_case_table_names' returns -1 or 0 (only for Win and OS X). So check, if case sensitivity is an issue for you.

Update:

The message you see is in fact only the title for a (potential) error dialog. The actual message is not listed in that small log. Look in the WB log (Help -> Show Log File) for the details.


Check entry based on entry in another column

One way would be a check constraint, but this will not give meaningful error messages

create table Table1
 (PaymentType varchar(5) not null,
 CCNumber varchar(16),
 PONumber varchar(30),
 CheckNumber varchar(10));
 GO
 ALTER Table Table1
 add CONSTRAINT CHK_PAYMENTTYPE CHECK
  (CASE WHEN PaymentType = 'CC' and CCNumber is NOT NULL and PONumber is NULL and CheckNumber is NULL THEN 0
    WHEN PaymentType = 'PO' AND PONumber IS NOT NULL and CCNumber IS NULL and CheckNumber is NULL THEN 0
    WHEN PaymentType = 'Check' AND CheckNumber IS NOT NULL and CCNumber IS NULL and PONumber is NULL THEN 0
    ELSE 1 END = 0);

It would probably be better to do a foreign key relationship or just have a single column for the number with a check constraint that checks that the number is of correct format for each payment type

ps: dont store CreditCard numbers unencrypted in a database...


Does a single rollback affect all transactions?

Yes commit and rollback behave differently in this respect.

Commit just decrements the @@trancount. Nothing is actually committed until that reaches zero.

Rollback will always rollback the transaction and set @@trancount to zero.

Just to be clear there is only one transaction going on, irrespective of the value of @@trancount. Nested transactions in SQL Server are a myth. See Paul Randal's blog post:

A SQL Server DBA myth a day: (26/30) nested transactions are real.


What is a basic model for making a database with users and groups?

The traditional way to model this is using a pattern called Role-Based Security.

The idea is not just to have groups of users, but also groups of permissions. Here is how the pattern looks:

Note that you want to avoid reserved words for table names, so don't name your tables exactly as shown in the diagram.

The way it works is that your groups or Roles have not only a list of users assigned to them but also a list of permissions assigned to them.  This allows you to table-drive both who can do what, but also what it is they can do, if you follow me.


Which of the following data duplication options across shards is recommended?

As you point out, having triggers between the various shards is silly; the whole reason for sharding is independent database
  operations. So you can throw it out right away.

  Updating both tables at the same time is the approach with the fewest
  moving parts. Over the long term, it will be the most maintainable.
  And it will be the easiest to debug if something goes wrong.

  But if response time is important, then you might think of some sort
  of messaging approach: update the comments-by-entry table, and queue a
  message to update the comments-by-user table. If it takes an hour for
  that message to be processed -- or if it gets lost in a system crash
  -- no big deal, you can always recover. By no means should you use a messaging approach to update both tables.

Answer by: @kdgregory Link: https://softwareengineering.stackexchange.com/a/134607/41398


Did anybody use sqlcmd mode in practice?

I use the sqlcmd mode in SSMS on occasion. Typically when migrating databases from one environment to another.  For example if I need to backup the database from production, then restore it to dev, I'll do this all in one script using SQLCMD mode to switch servers.  I've used to here and there for one thing or another, but I don't use it all that often.


Is it necessary to connect to a postgresql database in order to create a new server?

You always have to connect to a database to run CREATE DATABASE. This is the main raison d'être for the postgres database.

There is the createdb executable that does this for you automatically.


How to find the system impact of a query in postgres

Add pg_stat_statements in shared preload libraries
Create extension pg_stat_statements.
Then query on pg_stat_statements to get time related stats of the query.

Query run by can be tracked from pg_stat_activity for the currently running statements.

For blocks you can query  as below
SELECT
    activity.pid,
    activity.usename,
    activity.query,
    blocking.pid AS blocking_id,
    blocking.query AS blocking_query
FROM pg_stat_activity AS activity
JOIN pg_stat_activity AS blocking ON blocking.pid = ANY(pg_blocking_pids(activity.pid));


INSERT trigger dying with with #target columns not equal to #expressions, but they are equal

Remove parens around column list in select. This doesn't do what you think it does.


Postgres: understanding amount of WAL files/size generated

When you update a row in PostgreSQL, it generally makes a copy of the entire row (not just the column that was updated) and marks the old row as deleted.  The new copy is going to need to get WAL logged in its entirety.  The old row is probably also going to be WAL logged in its entirety, on average, if you have full_page_writes turned on and you are checkpointing too closely together.

Almost all of the updated rows are probably going to need to update all of the indexes for it, as well.  That is because the new version of the row won't fit on the same page as the old version, so the indexes have to know where to find the new version.

So you are logging the entire table twice (once for the old rows, once for the new ones) and all if its indexes as well.  And WAL records have quite a bit of overhead.  And if you have full_page_writes turned on and checkpoint frequently, that will make it even worse.

So what are your options to reduce the volume?

1) If many of your updates are degenerate (updated to the value they already have) you can suppress those updates with an additional where clause:

WITH table2_only_names AS (
    SELECT id , name FROM table2
)
UPDATE table1
SET table2_name = table2_only_names.name
FROM table2_only_names
WHERE table1.table2_id = table2_only_names.id
AND table2_name is distinct from table2_only_names.name;

2) Most WAL files are extremely compressible.  You can include a compresssion command in your archive_command, something like

 archive_command = 'set -C -o pipefail; xz -2 -c %p > /backup/wal/%f.xz'

Of course you will have to make your recovery_command do the reverse.

3) Since you are using 9.5, you can try turning wal_compression on.

4) You could try turning off full_page_writes, although this does but your data at risk of corruption in the case of a crash, on most storage hardware.  Or, if you have frequent checkpoints during this operation you could make checkpoints occur much less frequently, which will lessen the impact of having full_page_writes turned on.


Not possible, redo logs get transferred unprocessed. If you want to transfer changes related to just some specific objects or schemas, Streams (deprecated, but free) or GoldenGate (requires extra license) does that.

Another option for reducing network traffic is to configure redo transport compression (requires the Advanced Compression option), but this could still produce much more network traffic compared to above solutions depending on the ratio of the changes related to the replicated objects.


64-Bit OLEDB Provider for ODBC (MSDASQL) issues

Ok, just read this on a blog. YMMV

http://www.ryanjadams.com/2011/03/dsn-windows-64bit/


Update rows in table with set-returning function

This is not going to be fast, but you need a subquery that assigns row numbers for each row that can be used for joining the generated values with the table. You can use with ordinality to generate such number for the result of a set-returning function.

with numbered as (
  select id,
         row_number() over (order by id) as rn
  from foo
)
update foo
  set r = t.val
from (
  select n.id,
         nr.val
  from numbered n
    join normal_rand(1000, 5, 3) with ordinality nr(val,idx) on n.rn = nr.idx
) t
where t.id = foo.id

Replace id with the primary key column of your table.

Online example


SQL Server is running slow after a certain amount of time

I'm not sure how this recommendation will be viewed, but based on the vague nature of your problem description and report of steps you've taken, I'd recommend going with something that attempts to answer some of the 'what do I look for / at?' for you like the stored procedures Sp_blitz and sp_askbrent over at brentozar.com.  You may need to start with some basic health checks before you dig into the causes of for your performance issues on particular jobs. While this doesn't specifically answer your question, it's somewhere you can start. Brent's work is awesome and you'll find actionable output  that you will point you to potential solutions. Otherwise start with using activity monitor to see what else may be hitting the same tables while your jobs are running. At the same time check your growth patterns and see if you've had some growth recently within the tables your hitting. You may be limited in what you can retrieve depending on whether or not you're warehousing DMVs (or restarting the services for that matter) so setting of the built-in warehouse to store growth (etc) might be something you need to look into. In general if you have a solution in place to retrieve and retain DMVs you'll be able to act when problems like this come up. Sorry if I'm all over the place with this response.


pg_ctl by design isn't in the path of the version of PostgreSQL Debian (and therefore Ubuntu) distributes. PostgreSQL is installed as a service in Debian/Ubuntu

sudo service postgresql {start|stop|restart|reload|force-reload|status} [version ..]

That command calls an init.d script which wraps around pg_ctlcluster. You can see that by opening /etc/init.d/postgresql. You can call pg_ctlcluster if you must. pg_ctrlcluster is a perl script that wraps around pg_ctl which isn't exposed (in the path) by design.

  pg_ctrlcluster: multiversion/cluster aware pg_ctl wrapper; this also supplies the correct configuration parameters to 'start', and makes sure that postgres really stops on 'stop'.

pg_ctl is technically distributed, but there is no reason to ever call it directly. To find it run

find /usr/lib/postgresql/ -name pg_ctl

You can call pg_ctl explicitly with the full path, however don't.

Also, going above and beyond and teaching you how to fish, you can always use apropos if you have these kind of questions later.

$ apropos pg_ctl
pg_ctl (1)           - initialize, start, stop, or control a PostgreSQL server
pg_ctlcluster (1)    - start/stop/restart/reload a PostgreSQL cluster


What are the naming rules for named instances on a cluster?

You are correct in noting that the virtual name needs to be distinct. I'm having a hard time understanding what you're describing so I'm going to try to lay it all out.

SINGLE INSTANCE FAILOVER CLUSTER

If you're truly looking for "Active/Passive" then you're going to need at least four servers - two nodes for each instance. Let's call the instance SQLPROD-EAST.

You could then have:

Node A: SQLPROD-EASTA
Node B: SQLPROD-EASTB

Virtual Name: SQLPROD-EAST

as your nodes.

The virtual name would be SQLPROD-EAST and you'd install the default instance of SQL Server would would take the name SQLPROD-EAST. You'd then replicate this topology with SQLPROD-WEST:

Node A: SQLPROD-WESTA
Node B: SQLPROD-WESTB

Virtual Name: SQLPROD-WEST

MULTI-INSTANCE FAILOVER CLUSTER

This is where your description got confusing because it seems like you began describing Active/Active. This might look like:

Node A: SQLPRODA
Node B: SQLPRODB

Virtual Name: SQLPROD

as your nodes.

The virtual name would be SQLPROD and you'd install two named instances of SQL Server SQLPROD\EAST and SQLPROD\WEST.


Efficient SQL query to return parents that satisfy constraints on children

You need something like that:

SELECT e.Id, count(1) AS cnt
  FROM Entity    AS e
  JOIN Attribute AS a ON a.EntityID = e.Id
  JOIN Value     AS v ON v.AttributeId = a.Id
 WHERE (a.Name = 'color' AND v.Value = 'red')
    OR (a.Name = 'size'  AND v.Value = 'small')
--  OR ( ... ) -- extended here
 GROUP BY e.Id
HAVING cnt = 2 -- exact number of desired attributes
;


Upgrading using pg_upgrade on windows fails

Open an administrator cmd window as described in the link above.

Make sure you cd into an empty writable folder (for example, c:\temp\somewhere).

Execute the pg_upgrade command, and make sure to add an -u postgres parameter to it:

"C:\Program Files\PostgreSQL\9.3\bin\pg_upgrade.exe" -u postgres
    --old-datadir "C:/Program Files/PostgreSQL/9.2/data"
    --new-datadir "C:/Program Files/PostgreSQL/9.3/data"
    --old-bindir "C:/Program Files/PostgreSQL/9.2/bin"
    --new-bindir "C:/Program Files/PostgreSQL/9.3/bin"


I think trims can change through the year so you need a specific date start for a trim.
Model + body types is considered a 'sub model' AFAIK, and is not determined by 'trims-to-body types'. A Toyota Corolla Sedan may have different trims than the Coupe.
Also, how about you try this with natural keys and see what happens:

Manufacturers = (Manufacturer VARCHAR(20) PRIMARY KEY);

BodyTypes = (BodyType VARCHAR(10) PRIMARY KEY);

Models = (Manufacturer VARCHAR(10) NOT NULL REFERENCES Manufacturers(Manufacturer), Model VARCHAR(10) NOT NULL, PRIMARY KEY (Manufacturer, Model);

SubModels = (Manufacturer VARCHAR(10) NOT NULL, Model VARCHAR(10) NOT NULL, BodyType VARCHAR(10) NOT NULL REFERENCES BodyTypes (BodyType), FOREIGN KEY (Manufacturer, Model) REFERENCES Models(Manufacturer, Model), PRIMARY KEY(Manufacturer, Model, BodyType));

Trims = (Trim VARCHAR(10) PRIMARY KEY);

SubModelTrims = (Manufacturer VARCHAR(10) NOT NULL, Model VARCHAR(10) NOT NULL, BodyType VARCHAR(10) NOT NULL, Trim VARCHAR(10) NOT NULL REFERENCES Trims(Trim), SubModelTrimStartDate DATE NOT NULL, PRIMARY KEY(Manufacturer, Model, BodyType, Trim, TrimStartDate), FOREIGN KEY (Manufacturer, Model, BodyType) REFERENCES SubModels (Manufacturer, Model, BodyType));

What joins do you need now?

HTH


What inefficiency does sharding & partitioning attempt to address?

This should get you started to wonder more:

The Basics of Database Sharding
Shard (database architecture)
Partition (database)
SQL Server Table Partitioning: Resources
SQL Server 2005 Partitioned Tables and Indexes

A lot to read but will give you all the answers that you're looking for!


MySQL: Replicate data within 2 same schemas on 1 mysql server

You could setup a script to read the binary log, convert it to SQL (with the utility mysqlbinlog) and change the schema on-the-fly before applying the changes. You could also setup triggers on every table to INSERT/UPDATE/DELETE every row on the copy schema. But that is a lot of work to do it right.

Running 2 instances of MySQL on the same machine is very easy, and may be closer the the production deployment. Tools like MySQL sandbox makes running "n" different instances of MySQL even easier, taking just a command to setup an instance or a complex replication topology! You can even simulate replication lag with MASTER_DELAY or pt-slave-delay to see how your application behaves when the state of both schemas is not the same.


GROUP BY using columns not in the SELECT list - when is this practical, elegant or powerful?

I have used this technique (grouping on a column not used in the SELECT list) to combine an ordered list of values (which has gaps) into ranges of contiguous values.

First I do a rownum() on the ordered set of values. Then we diff the rownum() and the value. By grouping on the diff, we get the ranges.

CREATE TABLE #TMP (ID INT)

INSERT INTO #TMP
SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 6 UNION
SELECT 8 UNION
SELECT 9 UNION
SELECT 10 UNION
SELECT 15 UNION
SELECT 16 UNION
SELECT 17 UNION
SELECT 18 UNION
SELECT 19 UNION
SELECT 20

WITH RN AS
(
    SELECT
        ROW_NUMBER() OVER (ORDER BY ID) AS RN, ID
    FROM
        #TMP
),
SRC AS
(
    SELECT
        RN,
        ID,
        ID-RN DIFF
    FROM
        RN
)
SELECT
    MIN(ID) RANGE_START,
    MAX(ID) RANGE_END,
    COUNT(*) CNT_VALUES_IN_RANGE
FROM
    SRC
GROUP BY
    DIFF


MongoDB replica index filled hard drive stuck in fatal

If your data exist on a node then this node is named Primary from now on and the other 2 secondaries.

1) Start the Primary as standalone and drop the index.

2) Take a backup of your data just in case

3) Stop secondaries and delete their data directory.

4) Start the Primary as a member of the replica set.

5) Start the first secondary. This will cause an initial sync of the data, when the member join the set as secondary start the other secondary.


What is the maximum length of a comment in PostgreSQL?

COMMENT has no impositions other than it fit into a TEXT field (verified in IRC with RhodiumToad),

From the docs,

  Very long values are also stored in background tables so that they do not interfere with rapid access to shorter column values. In any case, the longest possible character string that can be stored is about 1 GB.

So if you're under a GB or thereabout, you're probably fine.


Model data with many to many and overrides

This doesn't seem complex, just normal relationships, just introduce a "Part" table which represents the physical aspects of your "Piece" (I imagine the part for the toy, while it can be made of something different, has to have the same physical parameters - length/width etc). The design I can imagine from your description:

Note the "ToyDefaultPiece" table which would resolve which piece is used by default. I have added in some extra fields which I can imagine would exist in a Toy design to hopefully make it more clear what the entity represents.

In addition, I can imagine you would query Toys/Parts/Pieces with something like:

SELECT ToyName
    , ToyPart
    , PartName
    , PartLength
    , COALESCE(def_pc.PieceID, pc.PieceID) as PieceID
    , COALESCE(def_F.FactoryID, pc.FactoryID) as FactoryID
    , COALESCE(def_F.FactoryName, pc.FactoryName) as FactoryName
    , COALESCE(def_M.MaterialName, pc.MaterialName) as MaterialName
FROM Toy t
INNER JOIN ToyPart tp ON t.ToyID = tp.ToyID
INNER JOIN Part p ON tp.PartID = p.PartID
INNER JOIN Piece pc ON p.PartID = pc.PartID
INNER JOIN Factory f ON pc.FactoryID = f.FactoryID
INNER JOIN Material m ON pc.MaterialID = m.MaterialID
LEFT OUTER JOIN ToyDefaultPiece def ON t.ToyID = def.ToyID
    AND p.PartID = def.PartID
LEFT OUTER JOIN Piece def_pc ON def.PieceID = def_pc.PieceID
LEFT OUTER JOIN Factory def_F ON def_pc.FactoryID = def_F.FactoryID
LEFT OUTER JOIN Material def_M ON def_pc.MaterialID = def_M.MaterialID

Using the COALESCE functions will enable you to override pieces with values you store in the ToyDefaultPiece table.


MySQL Replication - Master Master - Single Master write

With a single write master <> master setup, I don't need to worry about setting the auto_increment_offset correct?

Yes, you are right. I have setup Master-Master setups for years with one Write Master. I never touched the auto_increment options. Never had an incident.

  Does MySQL replicate all databases by default, or do I need to explicitly identify them via binlog-do-db?

Yes, it is by default. Although binlog-do-db exists, I would not recommend it since you are doing Master-Master. If you have additional slaves, it is best to setup filtering on the slave. That way, you have all binlog events to point-in-time recovery and other incidentals. Such incidentals might include Star Topologies (which feature Distribution Masters, a Master that does not house any data, only binlogs Such binlogs could be filter with binlog_do_db to reduce binlog traffic to slaves).

  Do I need to do an export from master1 and import to master2, before I start replication ?

You do need to to export from master1 and import to master2.

  can I start master 2 to pick up all transactions over the last several months?

The only way you can do that is if you had every binlog since you loaded master1 aznd never erased any of the binlogs. Most people rotate their binlogs, making this impossible.

I need to setup a "replicator" user (with replication perms) on both master1 and master2?

Yes

EPILOGUE

I have setup ucarp over the years as the failover mechanism for the floating IP. Most use Linux Heartbeat, Pacemaker, and others. So, you are in the ballpark of what needs to be done.


Deleting Billion records in a range vs exact ID lookup MYSQL

I would use much smaller batches, and delete in specific ranges:
DELETE from table_name where id BETWEEN ? AND ?

Start with the first number equal to the minimum id of the table, and the second number 1000 higher than that. Doing batches of 1000000 is too large, it'll take too long and make the rollback segment grow too large.
Then as you loop, increase the start and end of the range by 1000, terminating when it gets to 1762163840.
It will take a long time to do this no matter what.
One alternative strategy some people use instead of deleting a great majority of data is to make a new table, copy only the small number of rows you want to keep, and then swap the tables. That will take less time, but it's complicated to manage concurrent updates. It's easier if your table is insert-only.


How to upgrade SQL Server 2008 R2 RTM to SQL Server 2012 SP3 on the same server?

Unless you have user-created objects in the system databases, I would not try to restore them.

In fact, if you do have user-created objects, I'd script those out and add them into the new system databases as part of the restore process.

Some of the views, functions, procedures, etc. in the system databases may have version-specific code in them, so it's safest to just use the ones from the new version you're installing.


Partitioning in MySql 5.7 with Primary Key and Unique Key

Partitions have a lot of restrictions
You can'_t use HASH with multiple columns
If you want multiple  columns they have to be in the primary key, like shown in the example.
For your time you see in the second example. you have to make even more sacrifices and if that is what you seek, you have to test it
.

CREATE TABLE abc
 (
  itemid bigint(20) NOT NULL AUTO_INCREMENT,
  ownerid int(11) NOT NULL,
  product_id int(11) NOT NULL,
  creation_time datetime DEFAULT NULL,
  yearmonth VARCHAR(20) GENERATED ALWAYS AS (CONCAT(YEAR(creation_time),'-',MONTH(creation_time))),
  used_time datetime DEFAULT NULL,
  PRIMARY KEY (itemid,ownerid,product_id),
  UNIQUE KEY owner_product_uidx (itemid,ownerid,product_id),
  KEY prod_indx (product_id)
) ENGINE=INNODB
PARTITION BY KEY(itemid,ownerid,product_id)
 PARTITIONS 5;

✓

CREATE TABLE abc2
 (
  itemid bigint(20) NOT NULL AUTO_INCREMENT,
  ownerid int(11) NOT NULL,
  product_id int(11) NOT NULL,
  creation_time datetime NOT NULL,
  month_ tinyint NOT NULL,
  used_time datetime DEFAULT NULL,
  PRIMARY KEY (itemid,creation_time,month_),
  UNIQUE KEY owner_product_uidx (itemid,creation_time,month_),
  KEY prod_indx (product_id)
) ENGINE=INNODB
PARTITION BY RANGE (YEAR(`creation_time`))
SUBPARTITION BY KEY (month_)
SUBPARTITIONS 12 (
    PARTITION p2011 VALUES LESS THAN (2012),
    PARTITION p2012 VALUES LESS THAN (2013),
    PARTITION p2013 VALUES LESS THAN (2014),
    PARTITION p2014 VALUES LESS THAN (2015),
    PARTITION p2015 VALUES LESS THAN (2016),
    PARTITION p2016 VALUES LESS THAN (2017),
    PARTITION p2017 VALUES LESS THAN (2018),
    PARTITION p2018 VALUES LESS THAN (2019),
    PARTITION p2019 VALUES LESS THAN (2020),
    PARTITION p2020 VALUES LESS THAN (2021),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

✓

db<>fiddle here


Can this duplicate 'points' data be prevented?

No, there is no problem.
activity_area points are the attribute for the activity.
And the user_area_points points are an attribute to user_area.
i don't see why you put the pints into its own table, it is clearly an attribute to user_area


Multiple aggregate operations via a pivot table

Use conditional aggregation:
SELECT u.id AS user_id,
       sum(a.value) AS AS all_time
       sum(a.value) filter (au.inserted_at >= current_date - interval '3 months') as last_three_months
FROM users u
  JOIN achievements_users au ON u.id = au.user_id
  JOIN achievements a ON a.id = au.achievement_id
GROUP BY u.id;

You don't really need the users table though:
SELECT au.user_id,
       sum(a.value) AS AS all_time
       sum(a.value) filter (au.inserted_at >= current_date - interval '3 months') as last_three_months
FROM achievements_users au
  JOIN achievements a ON a.id = au.achievement_id
GROUP BY u.id;


Based on my understanding of your needs, you could use a Common Table Expression (CTE) to achieve this. Define your main query within the CTE and then do a JOIN on a second SELECT which calculates the total per JobID:

WITH results AS
(
    SELECT ID,
           JobID,
           NAME,
           ORDERNUM,
           STARTAFTER,
           FINISHBEFORE,
           START_OFFSET_MINS,
           TOTAL_MINUTES_REQ -
           (SELECT COALESCE(
                     SUM(TABLE_PROCESSTIMES.END_TIME -
                         TABLE_PROCESSTIMES.START_TIME) / 60, 0)
              FROM TABLE_PROCESSTIMES
             WHERE TABLE_PROCESSTIMES.PROCESSID = TABLE_PROCESSES.ID
               AND TABLE_PROCESSTIMES.START_DATE <= '24.04.2018'
           ) AS STILL_REQUIRED
      FROM TABLE_PROCESSES
)
SELECT x.*
  FROM results x
  JOIN (SELECT JobID, SUM(STILL_REQUIRED) AS STILL_REQUIRED
          FROM results
      GROUP BY JobID) y
    ON x.JobID = y.JobID
 WHERE y.STILL_REQUIRED > 0


Found important issue with SQL Server 2008 R2. Need Advice

In most cases where you hit this issue you will just need to follow the manual uninstall steps Microsoft provides here. That will, in cases I have had to, get the system to a point where you can successful reinstall SQL Server to a working state.


Finding repeating sets of data by foreign key

This is a hard one. I'll add a solution that will only work in vNext version, that has the new STRING_AGG() function for string concatenation:

WITH g AS
  ( SELECT
        ResultID,
        Items = STRING_AGG (ItemValue, ',')
                    WITHIN GROUP (ORDER BY ItemValue)
    FROM dbo.ResultItem
    GROUP BY ResultID
  )
SELECT
    ResultId = MAX(ResultId),
    Items
FROM g
GROUP BY Items
HAVING COUNT(*) > 1 ;

The separator (',') should be a character that doesn't appear anywhere in
ItemValue column.

For current and older versions, I've adapted the FOR XML PATH method, found in many articles (see Aaron Bertrand's blog post Grouped Concatenation in SQL Server which also has many other methods to do string concatenation):

WITH g AS
  ( SELECT
        ResultID = r.IDResult,
        Items = STUFF(
                   ( SELECT N',' + ri.ItemValue
                     FROM ResultItem AS ri
                     WHERE ri.ResultID = r.IDResult
                     ORDER BY ri.ItemValue
                     FOR XML PATH(N'')
                   ), 1, 1, N'')
    FROM Result AS r
  )
SELECT
    ResultId = MAX(ResultId),
    Items
FROM g
GROUP BY Items
HAVING COUNT(*) > 1 ;

Tested in rextester.com

See also the answer by Martin Smith from a similar problem: How to find parent rows that have identical sets of child rows?, where he adds an temporary table (appropriately indexed) to improve efficiency of this type of query.


Fact table design - Level or contribution measures

What is the difference in terms of analysis.

Storing the values that apply to the snapshot period would be the natural fit for most kinds of reporting, including "requests on trending the coin-in data for example by month per machine."

The risk is that if a snapshot goes missing the total on the machine may not match the totals in the cube. Storing both would give you the means to cross-check.


From this documentation - SET TRANSACTION ISOLATION LEVEL - under REMARKS

  Only one of the isolation level options can be set at a time, and it
  remains set for that connection until it is explicitly changed. All
  read operations performed within the transaction operate under the
  rules for the specified isolation level unless a table hint in the
  FROM clause of a statement specifies different locking or versioning
  behavior for a table.

You can use this query to determine the isolation level you are currently at:

SELECT CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM sys.dm_exec_sessions
where session_id = @@SPID


Handling lossy data before character- set migration

Straight from the Manual:

  The easiest approach to migrating convertible data dictionary data is to create a new database in the target character set and then re-create all of the data dictionary and schema definitions by exporting and importing. However, this method means creating a new database.

Based on what I'm reading in this manual, that's hands-down the safest and easiest way to do this migration.


Clustered index scan in query with non clustered index filter

One way to coax the optimizer to use narrow nonclustered indexes is to use a CTE:

WITH fix_me AS (
SELECT OrganizationId
FROM Operations
WHERE OrganizationId = 'some uniqueindetifier'
)
SELECT *
FROM Operations AS o
JOIN fix_me AS f
ON f.OrganizationId = o.OrganizationId

I'm guessing that your nonclustered index isn't covering, meaning the optimizer has a choice between doing a Key Lookup, or scanning the Clustered Index.

Using a CTE in this way can help guide the optimizer to use a narrow index to get only the necessary rows, but may also be ignored.

Since you gave an incomplete picture of your query and table design, feel free to adjust that to fit your needs.

I talk about this technique more in this presentation:

Improving SELECT * Query Performance


Warehouse storage system (FIFO by BBD and date)

Check this solution:

WITH
cte1 AS ( SELECT *,
          SUM(CASE WHEN Quantity > 0
                   THEN Quantity END) OVER (PARTITION BY ItemId
                                            ORDER BY InventoryDate) incomeQuantity,
          SUM(Quantity) OVER (PARTITION BY ItemId
                              ORDER BY InventoryDate) balance
          FROM inventory ),
cte2 AS ( SELECT ItemID, -SUM(Quantity) spent
          FROM inventory
          WHERE Quantity < 0
          GROUP BY ItemId )
SELECT cte1.ItemID,
       cte1.InventoryID,
       cte1.InventoryDate,
       cte1.Quantity,
       cte1.MoneyValuePerUnit,
       CASE WHEN cte1.incomeQuantity - cte2.spent <= cte1.Quantity
            THEN cte1.incomeQuantity - cte2.spent
            WHEN @amount + cte2.spent >= cte1.incomeQuantity
            THEN Quantity
            ELSE GREATEST(@amount +
                          cte2.spent -
                          COALESCE(LAG(cte1.incomeQuantity) OVER (PARTITION BY ItemId
                                                                  ORDER BY InventoryDate), 0), 0)
            END expense
FROM cte1
JOIN cte2 USING (ItemId)
WHERE cte1.incomeQuantity > cte2.spent
  AND cte1.Quantity > 0
ORDER BY cte1.InventoryDate

where @amount - the amount of good (itemId) which must be sold. The variable is used because only one itemId is present in data, really this must be a joined table order (itemId unique, amount), which contains the amount for each good to be sold in the order.

fiddle - test it carefully in all possible datasets. But please note - the query does not show that the requested quantity is greater than the stock balance. It also does not take into account the value of BBU if it is not NULL, add the appropriate sorting level.


WAL archiving from a standby backup strategy Postgresql 9.3

Postgresql provides a pg_receivexlog utility that can be used instead of archive_command to do wal log archiving.

From the Documentation:

  pg_receivexlog is used to stream transaction log from a running PostgreSQL cluster. The transaction log is streamed using the streaming replication protocol, and is written to a local directory of files. This directory can be used as the archive location for doing a restore using point-in-time recovery


Run stored procedures in other schemas from stored procedure

You can call procedures in schema B from procedures in schema A. Depending on the version of Oracle, you may have to grant execute privileges on each procedure explicitly from B to A, using the "with grant" option, rather than through a role. Consult documentation for your specific version to verify what permissions are required.

In general I would also recommend putting orchestration logic into the back-end (database) rather than client-side code. Keep your back-end and front-end business logic separate.


scramble the value of one column and unscramble it?

There are plenty of generic data masking script, but the only problem is that no one understands your data better than you.

You have to write your own masking script based on what data you consider sensitive.

When you do a backup/restore of PROD data on UAT/QC environments for testing, its advisable to mask the data with dummy data. Masking of data can be done through updating the confidential information in the database like SSN , Password,Username etc with some other values.

You can even log what data is going to change in tables with different schema. For e.g

If person.Employee table is going to be masked, then the before value can be written to maskPerson.Employee table. This way, you will have before values as well. (dont really need this as in UAT or QC you dont care of the before value, which is PROD value you are masking).

Some good references :

Data Masking Script
Obfuscating your SQL Server Data
Data Generator for SQL SERVER 2000,2005 and MSDE

I found this connect item wherein Microsoft is going to provide a data masking feature in future release ... (still could not make it for 2012).


I typically create the primary key constraint directly with the table:

CREATE TABLE dbo.MyTable
(MyTableID INT IDENTITY(1,1) NOT NULL
     CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED,
 .......
)

I don't see any point in deferring that PK constraint to later (same also applies to DEFAULT and CHECK constraints which I typically also define "inline" with the table creation script).

However, the FK constraint, I typically define those as separate ALTER TABLE .. ADD CONSTRAINT ... statements, that can be run right after the table creation, or after all tables have been created - doesn't really matter:

ALTER TABLE dbo.MyTable WITH CHECK
ADD CONSTRAINT FK_MyTable_OtherTable
FOREIGN KEY (OtherTableID) REFERENCES dbo.OtherTable(OtherTableID)

ALTER TABLE dbo.MyTable CHECK CONSTRAINT FK_MyTable_OtherTable


Suppressing SELECT output before \gexec?

Thus, as stated in the Subject, is there any way to suppress the SELECT output?

Yes, by omitting the semi-colon after the SQL statement.
That is, \gexec itself ends the SQL statement.
test=> select 'select 1' \gexec
 ?column?
----------
        1
(1 row)


Is there DMV for seeing view access count?

No, but what the presenter was probably doing was using Grant Fritchey's technique to search the plan cache for a string. You can search the plan cache for your view name.

That technique has a few drawbacks. It's very slow on a busy/large production server - it doesn't hold folks back by blocking, but it can just take a really long time to search, say, 10-100GB of query plans looking for a string. Also, it only searches the literal plan - if you have a view calling another view, the nested view's name may not show up in the query or the plan.


How I can force the TSQL-Developers to use not deprecated TSQL?

Bjorn,

There is nothing out of the box that will force the developers to use non-deprecated features (as they are deprecated, not removed). The most tailored solution that could be created is with DDL trigger(s), but note that they can be tricky... especially if not extremely familiar with them.

I would suggest PBM, but it has limitations that won't necessarily capture everything you're looking for. As already discussed, you can use extended events (in the previous link I had) to check for the use of the deprecated items.

Here is an example that will stop the use of fn_get_sql() and give a helpful message while stopping the use. Note that multiple checks can be made in the same trigger, this is just a trivial example.

/* Create a test datbase */
CREATE DATABASE No_Deprecated_Items;
GO

USE No_Deprecated_Items;
GO

CREATE TRIGGER Stop_Deprecated_Items
ON DATABASE
AFTER CREATE_FUNCTION, ALTER_FUNCTION, CREATE_PROCEDURE, ALTER_PROCEDURE, CREATE_TRIGGER, ALTER_TRIGGER
AS
BEGIN
    DECLARE @Command NVARCHAR(MAX)

    SELECT @Command = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')

    IF(PATINDEX('%fn_get_sql%', @Command) > 0)
    BEGIN
        RAISERROR('fn_get_sql is deprecated, use sys.dm_exec_sql_text() instead!', 16, 1)
        ROLLBACK
    END
END
GO

/*
This is created as it passed the constraints in the trigger
*/

CREATE PROCEDURE Good_Procedure
AS
BEGIN

    DECLARE @RandomVariable INT

    SET @RandomVariable = 0

    SELECT * FROM sys.dm_exec_sql_text(@RandomVariable)

    Print 'COMPLETE!'
END
GO

-- See the SP is created
SELECT * FROM sys.procedures
GO

/*
This will be rolledback as it fails the trigger logic.
*/
CREATE PROCEDURE Bad_Procedure
AS
BEGIN

    DECLARE @RandomVariable INT

    SET @RandomVariable = 0

    SELECT * FROM ::fn_get_sql(@RandomVariable)

    Print 'COMPLETE!'
END
GO

-- See the SP is NOT created
SELECT * FROM sys.procedures
GO

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

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

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


Instead of static list, using a table select result for IN key

In most cases replacement IN clause with EXISTS clause gives performance boost.
Try this:

WHERE exists (SELECT 1 FROM LOVs WHERE value = STypeID )


Obtaining database information and values in extended properties

You could use a cursor to loop over the databases and store the resultsets in a temporary table

CREATE TABLE #TEMP(Databasename nvarchar(500),
                   value sql_variant)

DECLARE @sql NVARCHAR(max);
DECLARE @dbname NVARCHAR(500);
DECLARE c cursor LOCAL FAST_FORWARD FOR SELECT NAME
                                        FROM sys.databases
                                        WHERE database_id > 4
                                        ORDER BY NAME;
OPEN c
FETCH NEXT FROM c INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = '
use '+QUOTENAME(@dbname)+'

SELECT '''+QUOTENAME(@dbname)+''' as databasename,
        value
FROM sys.extended_properties
WHERE class_desc=''DATABASE'' and name = ''Application Name''';

INSERT INTO #TEMP
EXEC(@sql);

FETCH NEXT FROM c INTO @dbname;
END
CLOSE c;
DEALLOCATE c;

SELECT * FROM #TEMP;
DROP TABLE #TEMP;

Testing

USE my_test;
GO
EXEC sp_addextendedproperty
@name = N'Application Name',
@value = 'ValueHere';

USE Test;
GO
EXEC sp_addextendedproperty
@name = N'Application Name',
@value = 'ValueHere';

Result

Databasename    value
[my_test]       ValueHere
[Test]          ValueHere


What are some efficient ways in using COUNT(*) with WHERE

SET NOCOUNT has nothing to do with your ability to perform counts or the efficiency of doing so.  This setting merely enables the reporting of affected rowcounts to the client (e.g. the n row(s) affected. message in Management Studio). The reason it is recommended to be off is because it is unnecessarily chatty and can break some applications / APIs.

As for getting more efficient counts from complex joins, it's possible that your joins could be made more efficient, but we can't see them. It's also possible that you can pay that cost at a different time by using filtered indexes, indexed views, or pre-aggregating yourself at insert/update time, using a trigger, etc. Sometimes this can pay off but other than a general suggestion there's not much else we'll be able to offer you without a lot more details.


Migrating between two very different DBMS requires a lot more than just migrating the data. But migration of the data is usually the easiest part.

The way that I have tried, which is free and I can confirm that it works:

create a mysql schema only dump
Adjust the SQL using a text editor and a lot of search and replace
run the converted SQL in Postgres
Create a plain text dump from MySQL (e.g. CSV, or some other delimited format)
import the data using PostgreSQL's COPY command

Importing the data might actually be difficult if you relied on MySQL's behaviour to accept illegal data (like the 31st of February)

My guess is, that this is going to be quicker than searching for a tool, evaluating a bunch of them and then trying to understand the one that you chose. But it depends on what kind of "big" you are referring to. If big is several hundreds of tables this might not be feasible. If big only referred to the number of rows, then this is probably the quickest way to do it.

There are some tools out there that can dump a database schema in a DBMS independent (XML) format, like Liquibase, SchemaSpy or WbSchemaReport. With Liquibase probably being the easiest to use. The others will require some manual work writing/extending the XSLT to transform the generated XML.

If you are using triggers and stored procedures in MySQL, I don't believe there will be any automated tool that can translate them without requiring a major manual fixing afterwards - and then the generated procedures would probably not use any advanced features of the target DBMS.


Obligatory disclosure: I have worked for MySQL AB/Sun Microsystems & Percona (I never worked for Oracle).  I'm now independent, and have no shares/remaining stake-holdings in any. I do however acknowledge that I have a stake holding in MySQL succeeding in general, as I have invested time in a specific skill-set.

It used to upset me how features were decided at MySQL...  How was it decided that partitioning was a critical feature for 5.1, but backup totally missed the radar?

There seemed to be a bunch of low hanging fruit (years old bugs) that were not being addressed, and I was always cynical that unless it could check off a box on a features grid, it would never be handled.

There was a bit of talk, but no indication it was any better under Sun's management.  However, now that Oracle is in control, several years old bugs are being addressed, performance has become a feature, and I actually find really compelling reasons to upgrade to 5.5 and 5.6.

I feel awkward having to defend one of the world's biggest software companies, but they're really not getting enough praise.  Instead everyone is making claims they are somehow screwing the project.  Most of the projects they 'screwed' made no commercial sense to them... however they make a non trivial amount of money on OEM licenses and subscriptions/professional services for MySQL.


I decided to do it in the following way:

Make a table strings with an ID and a VARCHAR, which will store both URLs and keywords, both normalized forms and unmodified user input.
Make a table seo which will hold (in addition to the row ID) four numeric fields referring to strings: unmodified user input for URLs and keywords, normalized URLs and keywords.

The list of normalized URL/keyword pair will be generated on the fly using DISTINCT.


Restore without specifying location for each file

Is there a way to specify one data and one log folder in a script that will restore all files to those folders?

You just have to use a good existing tool (hint : restoregene). It does take care of multiple filegroups dynamically.

dont put all the filegroups in one folder - as if the drive fails, you loose your entire database. The whole point of having filegroups is to have them on different disks/LUNs.

EDIT: Based on your comment

  The purpose of the restore script is to restore a copy of the production database to QA and CI servers.

PowerShell is the way to go - Start-SqlServerMigration.ps1

Basically, you have to use WITH MOVE option in restore command since the disk and folder layouts wont match from PROD to QA or other servers.


T-SQL SELECT use multiple indexes for no reason

In your first query the index is used to find rows matching [InverterData].[Date] = '05.01.2016' then it needs to lookup the rest of the row data to satisfy being able to return ACPower and DCPower - if you remove these columns from the output you'll see the extra lookup go away.

You could include the extra columns in the index with:

CREATE NONCLUSTERED INDEX [NonClusteredIndex]
ON [InverterData] ([Date] DESC, [InverterID] ASC) INCLUDE ([ACPower], [DCPower])

This removes the extra lookup via the clustered index at the expense of making the non-clustered index consume more space on disk (and in memory). This query speed and used space trade-off is something you'll have to decide upon by running benchmarks on the bits of the application that use that table.

Note that you could also do:

CREATE NONCLUSTERED INDEX [NonClusteredIndex]
ON [InverterData] ([Date] DESC, [InverterID] ASC, [ACPower], [DCPower])

which uses the extra columns as part of the key rather than just INCLUDING them. The former example is likely to be more efficient as the values are unlikely to be filtered/sorted upon so it saves page splits caused by the engine trying to keep the (effectively random) values stored in order.


Obtain a "list" of stored procedures which include INSERTs without naming the columns involved

There is a DMV called sys.dm_sql_referenced_entities. It returns a column is_insert_all documented thus

  1 = The object is used in an INSERT statement without a column list (object-level only).

This seems to be what you're looking for.

Here's an example of it in practice. First we'll create a simple one-column table

drop table if exists dbo.T1;
go
create table dbo.T1(c int);

Now two stored procedures, one references columns explicitly the other does not.

create or alter procedure dbo.p_named
as
begin
    insert dbo.T1(c)  -- columns are listed
    select 1;
end
go

create or alter procedure dbo.p_not_named
as
begin
    insert dbo.T1   -- no column list
    select 2;
end
go

The DMV is a table-valued function. As such it can be invoked using the CROSS APPLY syntax. The names of procedures to pass to this function can be obtained from the catalog view sys.procedures. The documentation for this DMV states

  schema_name is required when the referencing class is OBJECT.

We can get the schema name using a built-in metadata function handily called SCHEMA_NAME(). Putting it all together we get

select
    p.name, is_insert_all -- other columns exist
from sys.procedures as p
cross apply sys.dm_sql_referenced_entities(CONCAT(SCHEMA_NAME(p.schema_id), '.', p.name), 'OBJECT');

Which returns this

name          is_insert_all
------------- -------------
p_named       0
p_named       0
p_not_named   1


Suitability of mysql on a raid 5 server with raid card

When you're comparing, RAID-10 is still a better architecture than RAID-5. My opinion is that regardless of hardware improvement the underlying functionality still remains the same for RAID-5.

(Even though Ronado's answer has "If you do not use innodb_file_per_table...", he'd still recommend the same.)


How to grant all privileges on a database?

What you ask is hardly practical - you would typically use a superuser for this. Because, quoting the manual:

  The right to modify or destroy an object is always the privilege of the owner only.

And:

  The right to drop an object, or to alter its definition in any way, is
  not treated as a grantable privilege; it is inherent in the owner, and
  cannot be granted or revoked. (However, a similar effect can be
  obtained by granting or revoking membership in the role that owns the
  object; see below.) The owner implicitly has all grant options for the
  object, too.

So, for a non-superuser to have these privileges, too, it would have to be granted membership (directly or indirectly) in all roles that are allowed to create objects.

GRANT any_role TO admin_role;

Granting membership in a superuser role does not make the member a superuser.

  The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be
  thought of as special privileges, but they are never inherited as
  ordinary privileges on database objects are.

But the member role can now SET ROLE and become superuser. May or may not be what you want.

Then, as owner, admin_role can also GRANT any additional privileges (that may have been revoked) himself.

Or you set up a regime that assigns ownership of all existing and future objects to a role and grant membership in that role only. The quick way to do that for all objects of given roles is REASSIGN OWNED. Related:

Is there a means to set the owner of all objects in a PostgreSQL database at the same time?

But would you even want to grant ownership on objects in the system catalog, too? Those are owned by the superuser postgres in default setup.

Instead, I would use a superuser where "all" privileges are needed. And grant only required privileges ( incl. CREATEDB and CREATEROLE) to an admin role. A recipe for a powerful admin role (without all privileges):

How to manage DEFAULT PRIVILEGES for USERs on a DATABASE vs SCHEMA?


How to find out CPU utilization per Oracle Database on Linux?

All the oracle processes are kicked off by the oracle executable, and if you're running multiple databases on a server, all of the processes for each database will show up with just that executable.

Adding the args option to the ps command will add the database name both for the instance processes (such as pmon) and server processes (those satisfying user activity) to the ps output to allow grepping.

What I don't know is what you'll get is useful, considering ps is a snapshot of activity as of a given instant in time, and that seems like a very strange way to do capacity planning.


JOIN 2 tables, and query data from them 3 times

Given your table structure, this should extract what you are looking for:

SELECT DISTINCT p1.value
FROM postmeta p1
INNER JOIN postmeta p2 ON p1.post_id = p2.post_id
WHERE p2.key = 'make'
    AND p2.value = 'Chevy'
    AND p1.key = 'model';


If I understand the task correctly, then try something like

WITH
cte1 AS ( select date,
                 time_in,
                 time_out,
                 leave_start,
                 leave_end,
                 leave_type_id,
                 leave_type_descr
         from table1 ),
cte2 AS ( select date,
                 time_in,
                 time_out,
                 null leave_start,
                 null leave_end,
                 leave_type_id,
                 leave_type_descr
          from  table2 )
SELECT cte1.date,
       cte1.time_in,
       cte1.time_out,
       cte1.leave_start,
       cte1.leave_end,
       COALESCE(cte2.leave_type_id, cte1.leave_type_id) leave_type_id,
       COALESCE(cte2.leave_type_descr, cte1.leave_type_descr) leave_type_descr
FROM cte1
LEFT JOIN cte2
   ON cte1.date=cte2.date
  AND cte1.time_in=cte2.time_in
  AND cte1.time_out=cte2.time_out

Maybe FULL OUTER JOIN and COALESCE() for all output fields with arguments taken from both tables in proper order...


Oracle SQL query - select or rows without all ids in other tables

first approach for customers without account

DELETE FROM CUSTOMER WHERE
ID NOT IN (SELECT CUSTOMER_ID FROM ACCOUNT);

the inner select gives you the list of customer_id's in the accounts table - the remaining data-sets in the customers table can be deleted after your first sentence.

based on that you build a similar select for the case without operations

DELETE FROM CUSTOMER WHERE
(
  (ID NOT IN (SELECT CUSTOMER_ID FROM OPERATION) ) AND
  (ID NOT IN (SELECT id_account_sender FROM OPERATION) ) AND
  (ID NOT IN (SELECT id_account_receiver FROM OPERATION) )
);

both where conditions can be combined using OR

of course if you first want to run a select to review what is going to be deleted, you can replace "DELETE" with "SELECT *" in the 2 queries I posted


Execute Permissions for a Store Procedure that creates databases

It did not work because the EXECUTE AS clause of a CREATE {object} statement can only reference a User, which is a database-level principal. CREATE DATABASE is an instance-level permission, so it needs to be granted to a login (an instance-level principal), not to a user.

This is easy to accomplish using Module Signing:

SETUP

USE [tempdb];
CREATE LOGIN [DbCreator] WITH PASSWORD='create a DB';
CREATE USER [DbCreator] FOR LOGIN [DbCreator];

EXEC(N'CREATE PROCEDURE dbo.CreateDatabase
(
  @NewDatabaseName sysname
)
AS
SET NOCOUNT ON;

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N''CREATE DATABASE '' + QUOTENAME(@NewDatabaseName);

EXEC(@SQL);
');

GRANT EXECUTE ON dbo.[CreateDatabase] TO [DbCreator];

APPLY MODULE SIGNING

-- 1) Create the Certificate:
CREATE CERTIFICATE [Permission$CreateDatabase]
    ENCRYPTION BY PASSWORD = 'UseBetterPassword!'
    WITH SUBJECT = 'CREATE DATABASE permission',
    EXPIRY_DATE = '2099-12-31';

-- 2) Sign the Module:
ADD SIGNATURE
    TO [dbo].[CreateDatabase]
    BY CERTIFICATE [Permission$CreateDatabase]
    WITH PASSWORD = 'UseBetterPassword!';

-- 3) Backup the Private Key to a VARBINARY string
--    to be copied and pasted somewhere safe (optional):
SELECT CERTPRIVATEKEY(CERT_ID(N'Permission$CreateDatabase'),
                      'NewPassword!', 'UseBetterPassword!');

-- 4) Remove the Private Key (optional):
ALTER CERTIFICATE [Permission$CreateDatabase]
    REMOVE PRIVATE KEY;

-- 5) Copy Certificate to [master] (Public Key only):
DECLARE @Cert NVARCHAR(4000) =
         CONVERT(NVARCHAR(4000),
                 CERTENCODED(CERT_ID(N'Permission$CreateDatabase')), 1);

EXEC (N'USE [master];
CREATE CERTIFICATE [Permission$CreateDatabase]
FROM BINARY = ' + @Cert);

-- 6) Create Login and Grant Permission:
EXEC (N'USE [master];
CREATE LOGIN [Permission$CreateDatabase]
    FROM CERTIFICATE [Permission$CreateDatabase];

GRANT CREATE ANY DATABASE TO [Permission$CreateDatabase];');

TEST

EXECUTE AS LOGIN = N'DbCreator';
SELECT SESSION_USER;
-- DbCreator

CREATE DATABASE [_TEST:FAIL];
/*
Msg 262, Level 14, State 1, Line XXXXX
CREATE DATABASE permission denied in database 'master'.
*/

EXEC dbo.[CreateDatabase] N'_TEST:PASS';
-- Success!!

SELECT * FROM sys.databases WHERE [name] = N'_TEST:PASS';

REVERT;
SELECT SESSION_USER;
-- dbo
DROP DATABASE [_TEST:PASS];

For a detailed explanation of the steps taken to apply module signing, please see my post:

Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level

For more information about module signing in general, and why you should use it instead of the easier yet more dangerous SET TRUSTWORTHY ON, please see my post:

PLEASE, Please, please Stop Using Impersonation, TRUSTWORTHY, and Cross-DB Ownership Chaining


Can you use multiple types of indexes in one composite index

FULLTEXT and BTree cannot be mixed in a single composite index.

Please provide SHOW CREATE TABLE and the SELECT for further discussion of alternatives.

Always(?), the Optimizer will pick a FULLTEXT index over any other.  There are a few cases where complicating the query with a subquery can make it run faster.  (But let's see what you are doing.)


AWS DMS connect to on premise SQL Server DB

The network address of a server instance (its server network address or Endpoint URL) contains the port number of its endpoint, as well as the system and domain name of its host computer. The port number uniquely identifies a specific server instances.

You can try for example below format as we use for Database Mirroring :

TCP://srv5.corp.abc.com:5022

For your reference:

Configure SQL Server Database Mirroring Using SSMS
The Database Mirroring Endpoint (SQL Server)


How do you run T-SQL commands on multiple servers from one place?

There are a couple of ways to go about this, but natively it sounds like Central Management Servers is a pretty good fit for you.

If it is just "one" script and "one" functionality to pull the information, consider writing a quick PowerShell script that loops through all of your servers (the list of servers can be contained in a text file or a database table, etc.) and executes the script.  This is quite simple and painless.

It all depends on how often your script(s) will change and what your comfort level is with each of the different approaches.


Log Drive getting filled up in SQL Server

I suggest you read this, as this is applicable in all scenario (even you have Always On Availability group) when your database is in full recovery model .

Why Does the Transaction Log Keep Growing or Run Out of Space?

Yes you should implement transaction log backup immediately.  Read this for details.

Best way to backup & truncate transaction logs on a schedule

Shrink will not help as your logs are not being truncated. If you are truncating manually and shrinking you will not be able to recover point in time. Moreover files will grow again and you have to keep doing this manually all the time. Once you pre grow your file based on your usage and set up scheduled transaction log backup you should not have to adjust file size, truncate manually or shrink file.

After you set this up then you can decide if you need to expand your E drive.

I also suggest change your transaction log autogrowth from percent to a fixed size. Read this.


How to replicate MySql database between two 2012 server

The replication process on windows servers is similar to doing it in any other server system.

First of all I would recommend reading the official MySQL replication documentation (https://dev.mysql.com/doc/refman/5.6/en/replication.html) and then check out this similar question from a while ago. (mysql replication on windows server)

Personally I would suggest using tools that allow you to replicate data between database servers. They can save you all the messy work and even impove performance.

When I wanted to replicate my MySQL database to Amazon Redshift data warehouse (https://www.alooma.com/integrations/mysql/redshift) I used a tool called Alooma that supports several replication methods and provides a 'Near Realtime View' which allows you to query the latest snapshot of your database in your data warehouse.


Modify an Oracle Dataguard destination database

If you setup a logical standby, then you can change the data in the logical standby. You will probably have trouble supporting the kind of change that you seem to want. Have you thought about talking to the vendor to see if they can add the logic that you want to the product you pay them for? If the vendor makes the change, then they will/should support it. You can create materialized views in a different schema and add whatever functionality that you want to the materialized views. You then just need to refresh them when needed.


Migrate Oracle BLOB to Postgres BYTEA or OID

1) BYTEA.  Because oid is an unrelated concept.

2) Work mostly out of box, see https://github.com/darold/ora2pg/issues/100 .


Change existing column in PG to auto-incremental primary key

Create a sequence and use it as the default value for the column:

create sequence measures_measure_id_seq
   owned by measures.measure_id;

alter table measures
   alter column measure_id set default nextval('measures_measure_id_seq');

commit;

That essentially what serial does.

See the manual for details:
http://www.postgresql.org/docs/current/static/datatype-numeric.html#DATATYPE-SERIAL


Linked Servers security context and transaction logging

Don't use a linked server for this. It will likely be slow. Linked servers are suitable for small queries over remote databases, not for bulk loading data.

I would use SSIS or whichever ETL system you feel more comfortable with.


Hmm  so as I mentioned in the comments, your actual execution plan reveals there is a severe cardinality estimate issue (screenshot of operation details for reference) on the Clustered Index Seek operation occuring on the atividade table. I'm not seeing much else in the execution plan or your query that jumps out at me explaining why you might be experiencing this type of issue other than the predicate in your WHERE clause being a decent size in the IN list, which essentially is syntactical sugar for a bunch of ORs. This also happens to be what the seek predicates are on for the aforementioned Clustered Index Seek operation, so it's a little suspect.
Unfortunately I think this one might require a little trial and error of query re-writing to see what helps the optimizer in generating a better plan without a cardinality estimate issue. Could you please try re-writing your query like this logical equivalent query (if possible) and re-running it to see if there's any performance improvement?
SELECT
    id_atributo_andamento AS idatributoandamento,
    id_atividade AS idatividade,
    nome,
    valor,
    id_origem AS idorigem
INTO #atributo_andamento
FROM atributo_andamento
WHERE id_atividade IN
(
   43796020, 43795933, 43795931, 43795681,
   43795672, 43793093, 43793090, 43793085,
   43790358, 43790342, 43789167, 43789155,
   43789154, 43788970, 43788928, 43788924,
   43788776, 43788774, 43788712, 43788699,
   43788674, 43788673, 43788626, 43788625,
   43788227, 43788219, 43787557, 43787530,
   43787088, 43787075, 43787063, 43787041,
   43787002, 43786368, 43786351, 43786244,
   43786243, 43786223, 43786222, 43786151,
   43417954, 43417953, 43417929, 43417294,
   43417293, 43417286, 43416500, 43416334,
   43416304, 43416288, 43416089, 43416034,
   43416016, 43416015, 43416014, 43416013,
   43416012, 43416011, 43416010, 43416009,
   43416008, 43416007, 43415753, 43401569,
   43400701, 41524028, 41316604, 41311555,
   41309174, 41308146, 41308136, 41286794,
   41281375, 41281374, 41281294, 41263751,
   41263749, 40960597, 38574969, 38133930,
   38133868, 38133516, 38132482, 37630401,
   37630400, 37630399, 37630398, 37630397,
   37630396, 37630395, 37629851, 37629846,
   37301414, 37298436, 37298433, 37298431,
   37298429, 37298427, 37298424, 37298398
)

CREATE CLUSTERED INDEX IX_Temp_atributo_andamento_idatividade ON #atributo_andamento (idatividade)
CREATE NONCLUSTERED INDEX IX_Temp_atributo_andamento_idatributoandamento ON #atributo_andamento (idatributoandamento);

SELECT
    atributo_andamento.idatributoandamento,
    atributo_andamento.idatividade,
    atributo_andamento.nome,
    atributo_andamento.valor,
    atributo_andamento.idorigem,
    atividade.id_tarefa AS idtarefaatividade,
    Cast(atividade.id_protocolo AS VARCHAR)  AS idprotocoloatividade,
    atividade.id_unidade_origem AS idunidadeorigematividade,
    atividade.id_unidade AS idunidadeatividade,
    atividade.dth_abertura AS aberturaatividade,
    atividade.id_usuario AS idusuarioatividade,
    u.sigla AS siglausuarioatividade,
    u.nome AS nomeusuarioatividade,
    atividade.id_usuario_origem AS idusuarioorigematividade,
    uo.sigla AS siglausuarioorigematividade,
    uo.nome AS nomeusuarioorigematividade,
    unidade.sigla AS siglaunidadeorigematividade,
    unidade.descricao AS descricaounidadeorigemativi001,
    tarefa.id_tarefa_modulo AS idtarefamodulotarefa
FROM #atributo_andamento AS atributo_andamento
   INNER JOIN atividade
               LEFT JOIN usuario u
                      ON atividade.id_usuario = u.id_usuario
               INNER JOIN usuario uo
                       ON atividade.id_usuario_origem = uo.id_usuario
               INNER JOIN unidade
                       ON atividade.id_unidade_origem = unidade.id_unidade
               INNER JOIN tarefa
                       ON atividade.id_tarefa = tarefa.id_tarefa
           ON atributo_andamento.idatividade = atividade.id_atividade
ORDER  BY atributo_andamento.idatributoandamento ASC

The above query breaks out the logic of the filtering on the id_atividade field for the atributo_andamento table and inserts the results into a temp table first. Then it creates an index on the idatividade field that is used in the INNER JOIN predicate to the atividade table later on. I also am testing creating an index on idatributoandamento which is what is used in the ORDER BY clause at the end of your query. Not sure if the query optimizer will use both indexes, but it's worth trying as a start.
Please run the above and let me know how it turns out. We can try adjusting a couple more things and still end up with a logically equivalent query, to see if we can fix the cardinality estimate issue, and worst case we may need to recalculate the statistics or possibly use a query hint.


Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation

The error is actually being caused by the line following the line you have highlighted in your screenshot (i.e. not the line starting with EXEC master.dbo.xp_instance_regread... )
This error is caused by a collation mismatch between msdb and tempdb. The reason that tempdb is even involved here is due to #syscollector_upload_package being a temporary stored procedure.
The following test will reproduce the error:
Setup
CREATE DATABASE [__TempCollationErrorTest] COLLATE SQL_EBCDIC278_CP1_CS_AS;
ALTER DATABASE [__TempCollationErrorTest] SET RECOVERY SIMPLE;
GO
USE [__TempCollationErrorTest];

Test
GO
CREATE PROCEDURE #ThisShouldError
AS
DECLARE @installpath nvarchar(2048) = N's';
IF @installpath = 'b' PRINT 4;
GO

Executing the code shown above will result in the following error:

Msg 468, Level 16, State 9, Procedure #ThisShouldError, Line XXXXX [Batch Start Line YYYYY]
Cannot resolve the collation conflict between "SQL_EBCDIC278_CP1_CS_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

Cleanup
GO
USE [master];
DROP DATABASE [__TempCollationErrorTest];

I'm guessing that when this instance was created (using Latin1_General_CI_AS for the instance-level collation), someone restored [msdb] from an instance that used SQL_Latin1_General_CP1_CI_AS as its instance-level collation, hence [msdb] having that collation.
Of course, a far less likely yet still technically possible scenario is this instance being created using SQL_Latin1_General_CP1_CI_AS as its instance-level collation, and someone (for whatever reason) restored the [model] database from an instance that used Latin1_General_CI_AS for the instance-level collation, hence [tempdb] having that collation.
You can check to see where things starting going wrong by executing the following query, focusing on the first two columns — [tempdb] and [msdb]:
SELECT DATABASEPROPERTYEX(N'tempdb', 'collation') AS [tempdb],
       DATABASEPROPERTYEX(N'msdb', 'collation') AS [msdb],
       DATABASEPROPERTYEX(N'model', 'collation') AS [model],
       DATABASEPROPERTYEX(N'master', 'collation') AS [master],
       SERVERPROPERTY('collation') AS [instance];

I'm not sure how much data you have in that [msdb] database (assuming that it is the one with the collation that doesn't master the others), but you will need to change its collation to match the others. This is easiest if this is a new install, but perhaps that is not the case with this being a system that is being patched.
Maybe you could export the data from [msdb] to a file, restore [msdb] from an instance created using Latin1_General_CI_AS for the instance-level collation, then reload the data into [msdb]. For the data import/export, perhaps look into using the SqlPackage.exe utility that comes with SQL Server Data Tools (SSDT). For getting a new [msdb], you might need to create a new instance of SQL Server (a throw-away, just to get a clean [msdb]), in which case you might be able to use SQL Server Express, or maybe best to use the install media used to create the current instance.
If that does not seem possible, then perhaps consider updating the collation of all databases, including the system DBs and even the instance itself, to Latin1_General_100_CI_AS_SC using the undocumented approach that I have described in detail in the following post:
Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?
I am suggesting Latin1_General_100_CI_AS_SC as it is:

a safe upgrade given it's the same code page (Windows 1252) and sensitivities (case-insensitive, accent-sensitive)
much improved over the base Latin1_General_* collations (i.e. those without _100_ in their name)
includes support (in terms of the built-in functions) for supplementary characters

I mean, Latin1_General_CI_AS is definitely much better than the US default of SQL_Latin1_General_CP1_CI_AS, but anyone using SQL Server 2008 or newer really should be using the version 100 (or newer) collations.
I recommend this due to assuming that the current instance level collation is Latin1_General_CI_AS, and the sqlservr -q approach makes no changes if attempting to change the collation to the current instance-level collation. So, a different collation would be necessary to use anyway in order to get the operation to process the databases, and the safest collation to use would be something very similar, such as the one I suggested, or even just Latin1_General_CI_AS_KS. If you must maintain using Latin1_General_CI_AS, then you can use Latin1_General_CI_AS_KS as a temporary collation to get all DBs to match, then run the operation a second time to make everything the desired Latin1_General_CI_AS. In this case, assuming you don't want to touch any user databases, those can be detached prior to running sqlservr -q, and then re-attach them once the system databases are all using the desired collation. This will keep the user databases from being affected by the operation (which is the safest approach if their collation ultimately won't be changing).


Backup from Server1 and restore it to Server2?

It's a little bit strange to have log file bigger than data file.
I guess you didn't take a backup for long time and that is a reason for such a huge log file.

First try with this link: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/633ea08c-942d-4703-bc03-f2befd35f612/log-file-size-keeps-growing-replication?forum=sqlreplication

The simplest solution is log backup, log backup should reduce the size of file.

If that fails you can try to truncate log file (see dbcc command). However, you should use this as a last resort since truncation of log file is "quick and dirty" solution.


Inserting into three different tables, using foreign keys -- 500000x each

Read the entire CSV into a new MyISAM table with no indexes.
Write SQL statements to migrate that data en-masse to the other 4 tables.
Drop that table.

100-150 IOPs -- not bad.  That's about all you can get with a consumer-quality disk.

So, the trick is to minimize the IOPs.

What engine are you using?  (InnoDB has some tunables that can help/hurt.)
What indexes?  (The more indexes, the more I/O.  And the cache may not be set optimally.)


pgBouncer does not use all available CPUs

Pgbouncer implementation is based on epoll system call and runs one thread only.
You pretty much have to run several pgbouncer processes to utilize more cores(one process per core).
But it is what you want to do only when pgbouncer is a bottleneck.


What happens when we take a timediff between timestamp and datetime

In addition to Rolando's answer, please note that a TIMESTAMP column is timezone-aware, while DATETIME is not.

If you don't have multiple time zones, then you're OK; but still, if ever you wanted to convert to other timezones, the TIMESTAMPDIFF calculation will provide with corrupted results.


SQL Server 2016: create credential and add SQL Server Agent proxy for it

The operation failed because credential '@credential_name' identity is
  not a valid Windows account

That is because you did not use Windows account, but SQL Server account, that is wrong. You just misunderstand SQL Server Agent Proxies

  SQL Server Agent proxies use credentials to store information about
  Windows user accounts.  The user specified in the credential must have
  "Log on as a batch job" permission on the computer on which SQL Server
  is running.

  SQL Server Agent checks subsystem access for a proxy and gives access
  to the proxy each time the job step runs.  If the proxy no longer has
  access to the subsystem, the job step fails.  Otherwise, SQL Server
  Agent impersonates the user that is specified in the proxy and runs
  the job step.

In your cited article there is this phrase that you did not read carefully:

  A credential is a record that contains the authentication information
  that is required to connect to a resource outside SQL Server.

That is, CREDENTIAL for Agent is some account to access OS(Windows) resources, so it's Windows account, OS does not know nothing about your SQL Server logins. The permissions to Windows resources are granted to Windows accounts, and one of this account you can use as a proxy for Agent.


MySQL 8.0 cannot insert NULL values to date field

It's because MySQL is actually trying to insert the literal string 'NULL' into a DATE column, not the actual value NULL.

Your choices are:

Global search for |NULL| in your file and replace with ||
Change your LOAD DATA statement to deal with it:

  LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat' INTO TABLE
  Item FIELDS TERMINATED BY '|'
      (i_item_sk integer ,
                                      i_item_id,
                                      i_rec_start_date,
                                      @i_rec_end_date,
                                      i_item_desc,
                                      i_current_price,
                                      i_wholesale_cost,
                                      i_brand_id,
                                      i_brand,
                                      i_class_id,
                                      i_class,
                                      i_category_id,
                                      i_category,
                                      i_manufact_id,
                                      i_manufact,
                                      i_size,
                                      i_formulation,
                                      i_color,
                                      i_units,
                                      i_container,
                                      i_manager_id,
                                      i_product_name )  SET
      i_rec_end_date = nullif(@i_rec_end_date,'NULL');


Can you force SQL Server to use an index on computed column for a commutative operation?

As far as I know, your only options are to create the second computed column, contact the vendor of the software to ask for a fix, or to submit an enhancement request to Microsoft to get better support for your scenario. On the surface, the functionality that you're asking for could be viewed as simple: why can't SQL Server figure out that the expressions are equivalent when they obviously are to a programmer? However, it requires at least all of the following:

SQL Server needs to know that DATEADD(minute, ValMin, DATEADD(hour, ValHour, ValDate)) is equal to DATEADD(hour, ValHour, DATEADD(minute, ValMin, ValDate)). There are a lot of date equivalences that SQL Server isn't yet aware of, and this is one of them.
The query optimizer would need to look for commutative matches during computed column matching.
The query optimizer would need to be able to find your match during the query optimization process, which is designed to very quickly give you a "good enough" plan.

I am sympathetic to your problem, but my guess is that the scenario that you're describing just isn't common enough for Microsoft to make improvements in this area, especially when there is a straightforward workaround that could be accomplished through changing code or by adding another computed column to the table.


X files, with X being the number of processors up to a limit (8, 16 or so).
Preallocate size.
Always on separate volume because I really like the ability to check the IO load on that one. Tempdb overload happens rarely, but if it happens.... it is good to be able to set an alarm.

Regarding drives: if you CAN keep them on the same drive, you either have a very small database (in which case - get it on an M.2 / U.2 SSD with enough IOPS) or have a problem to start with - of the drive is "fake" (like a SAN delivering a LUN that in reality has a lot behind) I keep all my volumes these days on shared storage, and at the moment even on realtively slow HDD (7200 RPM), but it is backed by at the moment 6.4 TB of M.2 SSD which will even grow larger, so my read access time is pretty much guaranteed below one millisecond. Even with all ending up on the same discs, I really want separate volumes to check contention with one look.

Same with mdf and ldf - all on their own volume.


Ola Hallengren maintenance script - how can I tell a backup file is still being written to?

You could use POWERSHELL and the COPY-ITEM cmdlet.

It will skip over files that are currently in use.

$ErrorActionPreference = 'SilentlyContinue' prevents errors from being displayed due to locked files.

powershell.exe "$ErrorActionPreference = 'SilentlyContinue';Copy-Item 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Backup\*.bak' c:\TestOut


Given your error ("OFUSER_PK"), this is an indication that the primary key on the OFUSER table is the problem. Check your insert statements for OFUSER - something seems wonky there.


How can I script out a copy of the backup to multiple locations?

You want to use the MIRROR TO option. This will allow you to create up to 3 different backups - each effectively a mirror or copy of the main backup you are doing.

In your case:

BACKUP DATABASE TestDB
TO DISK = 'C:\TestDB1.BAK'
MIRROR TO DISK = 'D:\TestDB2.BAK'
MIRROR TO DISK = 'E:\TestDB3.BAK' WITH FORMAT
GO

You can see more about the commands and options for backup here. There are many other options you should look into - such as verify, checksum, etc.

A couple notes also - 1.) The WITH FORMAT is necessary the first time I execute this here since this mirrored set is not yet formatted/created. So the backup doesn't operate like a traditional backup in that regard - because you are effectively creating a new mirrored backup set. The link above will help explain that a bit more.

And seeing you ask this does lead me to ask the question of "What is the goal?" - This is a feature for a reason and it may just be a requirement you have. But it could also be that you are trying to handle something that may be better handled in other ways. For instance backing up to a device with redundancies built in, backing up to a network drive, backing up to a SAN that has proper SAN replication in place, grabbing a copy of a backup right after you take it, etc.


Keeping (Partial) Backups Small when using SQL Server FILESTREAM

The problem this caused was that the log file (which also gets backed up) is now unnecessarily large because it includes the FILESTREAM data.

I'm not sure if you mean the log file itself is too large or that the log file backups become too large.

If it's the former then how often were you backing the log up? Depending on the application design you may be able to reduce the size by backing up more frequently (and every 5 minutes is not too frequently). However if you were already doing that and it was still ballooning then you're probably out of luck. Why is the large log file a problem again?

If it's the latter - it sounded like you were happy to continue with simple recovery model and no point in time restores if it let you have smaller backups; in which case stay in full mode and discard your log backups.


Unlike some other databases, in DB2 schema name does not necessarily match any user name. Users are created and authenticated outside the database, in the operating system. After a user successfully connects to the database, the schema name defaults to its login ID. For example, connecting as DB2ADMIN and issuing the statement CREATE TABLE MYTAB ... will create the table DB2ADMIN.MYTAB. If that same user issues the statement CREATE TABLE BOGUS.MYTAB ..., the table BOGUS.MYTAB (and implicitly the schema BOGUS) will be created, although there may be no user named bogus.

DB2 takes the concept of separation of duties quite seriously. For example, the database instance administrator, which presumably DB2ADMIN is, does not by default have access to data, except the tables created by that same user. There are a variety of GRANT statements that allow you to grant access to objects and data as necessary. You can read more about them in the documentation.


Something like

(   SELECT *
    FROM `template`
    WHERE DATE(`valid`) >= DATE(:date)
    ORDER BY DATE(`valid`)
    LIMIT 1
)
UNION ALL
(   SELECT *
    FROM `template`
    WHERE DATE(`valid`) <= '1970-01-01' OR `valid` IS NULL
)
ORDER BY `valid` DESC
LIMIT 1

or

SELECT *
FROM `template`
WHERE CASE WHEN DATE(`valid`) > '1970-01-01'
           THEN `valid`
           ELSE '2038-01-19'
      END >= DATE(:date)
ORDER BY CASE WHEN DATE(`valid`) > '1970-01-01'
              THEN `valid`
              ELSE '2038-01-19'
         END
LIMIT 1

and so on...


BULK INSERT Data from file but ignore the first occurence of field terminator

If the amount of data is small enough or you can otherwise afford to spend a bit more resources on the operation, there are ways around this. Most of the scenarios where I've done bulk inserts required additional data manipulation in any case, so I usually just insert the bulk data to a specially created bulk insert table, and then manipulate and transfer the data to the real table from there. Makes tasks such as this one a lot easier. But of course there's an increased overhead from handling the same data twice.


High CPU usage, low memory usage on Mariadb 10.5.8 -- SQL tuner complaining about memory, performance seems fine

Increase innodb_buffer_pool_size to be about 70% of available RAM.
High CPU implies poor indexing and/or poor query formulation.  Let's see some queries and the matching SHOW CREATE TABLE.
 INDEX(`rule_id`,`device_id`,`state`)  -- handles the following:
 INDEX(`rule_id`,`device_id`)   -- DROP
 INDEX(`rule_id`)   -- DROP

Add
 INDEX(state, time_logged)

Shrink INTs to some smaller datatype.  Saving space speeds up the query a little.
Invert the query -- Find the 50 rows, then do the JOINs:
SELECT ...
    FROM (
        SELECT id FROM log
            WHERE ...
            ORDER BY  time_logged DESC
            LIMIT  0,50
         )
    JOIN log AS e USING(id)  -- to get other columns from log
    JOIN the other tables for the other columns
    ORDER BY  time_logged DESC  -- repeat if ORDER matters

Use pt-query-digest to summarize the slowlog; it will be easier to spot the "worst" queries.
Other issues...

max_connections = 200000 is ridiculously high, even dangerously high.  That is the number of "connections" to allow at any one time.  RAM is consumed for each connection.  Do SHOW GLOBAL STATUS LIKE 'Max_used_connections'; to see that you have not come anywhere near that value.  100 is a safe starting value until you have a reason to increase it.
Notice (in slowlog) Rows_examined: 973090.  How many rows in each of the tables?  The changes I suggest will probably bring that number down, though probably not down to the optimal of 50 (as in Rows_sent: 50).
An owl can turn its head almost all the way around.  That is how my head feels when I see a LEFT JOIN and RIGHT JOIN in the same statement.  My head would appreciate it if you could turn that RIGHT JOIN into a suitable LEFT JOIN or, if appropriate, INNER JOIN.  In particular, in the queries with AND  R.severity >= 2, it really is an INNER JOIN.


Display the choices even if no rows found in associated table

http://sqlfiddle.com/#!9/2a073/6 - "v.option_id = o.option_id" is supposed to be part of join condition so use ON instead of USING and move it there - when it is in WHERE, it works "after" the LEFT of join, so for not existing matches it is never true. And the group by per @ypercube is needed too.


Net Configuration Assistant doesn't open, created Listener in Net Manager but it isn't recognized when creating database Win 7

Try to run Netca "as Administrator".

The way Net Configuration Assistant and Net Manager create listener is the same - they both create configuration file - listener.ora. The difference is - Netca starts the service, but Net Manager is not.

Also, you can check if your listener is running by looking into Control Panel - Administration - Services - something like "OracleOraDB11gHome1TNSListener".


Scrapy data analyzed with R, mongoDB or other pipeline?

I won't tell you what are the differences as there are too many.
I would recommend to go with SQL databases unless NoSQL tightly fits to your use case.
SQL has much more standardized interface and is much easier to integrate and query.

The most easiest to deal with and very performant database from R is sqlite. Package RSQLite ships sqlite libraries so there is no external dependencies required. You only need to install RSQLite which is available in CRAN.


How to provision the SQL Server Agent service after SQL Server Edition upgrade

Let me answer your question point wise

1.SQL Server express has SQL server agent code built in but disabled. When you go to sp_configure you can see Agent XP enable/disable option this is so because if any point of time you are planning to upgrade SQL Server Express Microsoft upgrade process does not have to do much change but just a minimum to enable SQL server agent functionality. So before upgrading to Standard it wants you to use proper service account as a start up for SQL server agent so that after upgrade when database engine tries to bring agent services up it can do it eaisly. By default NT Authority/System account has admin privileges so it was able to bring Agent services up. Also please make sure that you always change SQL Server/Agent service account using SQL Server configuration manager it preserves the ACL's

2.As a good security practice SQL server should not run with Built in account instead a domain account should be created and granted least privileges to run it. Below Microsoft web resource will tell you what rights are required. The article is bit lengthy but beautifully written

http://msdn.microsoft.com/en-gb/library/ms143504.aspx


Can I update an Access table with an Oracle table?

It's been a long, LONG time since I did any Access development, but if Access still supports linked tables, you can.

In that case the linked table is really just a view (select * from some_schema.some_table_in_oracle) and you can write an Access query that you use to update the Access table using the linked Oracle table.


The mongos processes control when automatic splitting happens (you can also pre-split, and split manually).  The heuristic they use is a bit more complicated than I am about to describe, but you can use it as a rough guide:

Each mongos keeps track of data written to a particular chunk
At ~20% of the max chunk size (default is 64MB), it will try an autosplit
If valid split points are found and returned (by splitVector), it will split
If not, it will try again at 40% (+20%)

Hence if you have inserted enough data for valid split points to be found, you will see splitting before the 32MB mark (number of documents are a valid reason to split, it's not all size related).

If you do not wish for this to happen, you can start the mongos you will be using to write data with the --noAutoSplit option or you could run with a config server down (which means all splits will fail when attempted because the meta data is read only).  Generally, that would not be recommended for production, but can sometimes be needed for testing purposes.


Create a read only excel file from SSIS?

That's a filesystem concern, not an SSIS one. Any file that SSIS creates will be owned by the user SSIS runs as. The best solution would be to set the ACL on the target folder to be what you want; when SSIS creates the file it will get those permissions by default.

If you can't do that for some operational reason, you can manipulate the file after creation with a command task. You can use SetACL or any command line tools for this.