postgres window functions: using CASE to partition by age group

You could use a LATERAL join for this. And no need for joining the users table twice:

FROM
    competition_users AS cu
  LEFT JOIN users AS u
    ON cu.user_id = u.user_id
  LEFT JOIN LATERAL
    ( SELECT CASE WHEN u.dob > '2000-01-01' AND u.dob < now()
                 THEN '0-15'
                 ELSE '15+'
             END AS ageGroup
    ) AS age
    ON TRUE
  LEFT JOIN current_competition_sessions AS ccs
    ON cu.competition_user_id = ccs.competition_user_id
WHERE cu.left_competition = FALSE
  AND cu.competition_id = :compId

and then the WINDOW would be left (almost) unchanged:

WINDOW
    ageGroupTimes AS (PARTITION BY age.ageGroup ORDER BY cus.time_in_seconds ASC)

Alternatively, a simple CTE or derived table with a normal LEFT JOIN could be used, without the need for the (9.3+ available only) LATERAL. With the following you'll only need to slightly modify your upper-level SELECT and WINDOW (removing table aliases mainly):

FROM
  ( SELECT
        cu.*,
        cus.time_in_seconds,
        u.gender,
        CASE WHEN u.dob > '2000-01-01' AND u.dob < now()
            THEN '0-15'
            ELSE '15+'
        END AS ageGroup
    FROM
        competition_users AS cu
      LEFT JOIN users AS u
        ON cu.user_id = u.user_id
      LEFT JOIN current_competition_sessions AS ccs
        ON cu.competition_user_id = ccs.competition_user_id
    WHERE cu.left_competition = FALSE
      AND cu.competition_id = :compId
  ) AS d


It seems that the following code solves my problem:

SELECT DISTINCT groupid
FROM pupilgroupteacher x
WHERE teacherid=@teacher AND startdate<@month+INTERVAL 1 MONTH AND
NOT EXISTS(
SELECT *
FROM pupilgroupteacher
WHERE groupid=x.groupid AND teacherid!=@teacher AND startdate>x.startdate AND startdate<@month
)

(Here @month is the date of the first day of the specified month and @teacher is the teacher ID.)

But:

is this code error-free? Please help to check it.
can it be optimized to use fewer server resources?


Big Query Cache == Big Trouble.

It takes a long time to purge the QC of table entries every time you write to the table.
I robs RAM from other useful tasks.
With 1GB for QC and 835 for buffer_pool, you are very probably swapping.  Swapping is terrible for performance; MySQL assumes its caches are not swapped!

Do not make the QC bigger than 50M.  In most production system it is better to turn off the QC.

With 2GB of RAM, you probably should not set innodb_buffer_pool_size bigger than 500M.

Listing the indexes does not help without seeing the SELECTs.  Please also provide SHOW CREATE TABLE.

50 SELECTs/sec -- Swapping and/or poor indexes and/or poor formulation of queries.  5000/sec may be possible with your hardware.

Abandon the discussion of MEMORY until you work through my comments.  We may (but probably won't) need to come back to MEMORY.


Update stats takes significantly longer with a 50% sample rates versus a full scan

When you run stat updates WITH FULLSCAN, it goes parallel.  I believe it has since SQL 2005.

From TechNet:

"Parallel statistics gathering for fullscan: For statistics gathered with fullscan, creation of a single statistics object can be done in parallel for both non-partitioned and partitioned tables."

Source: https://technet.microsoft.com/en-us/library/cc966419.aspx

When you don't use FULLSCAN the engine leverages the TABLESAMPLE option which is a parallelism inhibitor.  In SQL 2016, sampled stat updates can also go parallel.

Source: Parallel Statistics Update


Is there a kind of deadlock that SQL Server won't kill automatically?

As an anecdote that may or may not shed some light on your particular issue, we've had a similar problem to this where an external application would execute a stored procedure and improperly roll back when an error was encountered.

This procedure was vendor provided and unable to be modified.  Adding to this, XACT_ABORT was set to the default value of OFF and could not be modified.

The application would reach a CommandTimeout event (due to SqlException being thrown and the application not handling this appropriately), but the session would remain open.

Since XACT_ABORT is OFF and explicit transaction statements were used in the SP without appropriate error handling, the session would maintain any locks held by the aborted procedure. The session itself would be in a "SLEEPING" status with "AWAITING COMMAND".

This was a somewhat insidious issue for us, because the failed session would not be immediately obvious. However, contention would rapidly grow in the way that a vehicle stopping in the middle of a busy intersection will cause a multi-car incident. Unrelated applications attempting to access the locked resources held by the now-orphaned session would simply WAIT, entering a SUSPENDED status while potentially holding locks of their own, creating a scenario of branching contention and lock escalation.

While there are certainly other methods, this was a very busy application database which limited the usefulness of more direct methods which would have been optimal in a more controlled or quieter environment. In order to quickly and easily identify the ROOT session responsible, we cobbled together the following query, which returns the contention "tree" and allowed us to KILL the offending session (once appropriately identified).

USE master
GO;

WITH CTE_Blks
AS (
 SELECT Blocked [SPID]
 FROM sysprocesses
 WHERE Blocked <> 0
 )
 , CTE_Tree
AS (
 SELECT DB_NAME(sp.dbid) AS [Database]
  , spid AS [Session]
  , Blocked [BlockedBy]
  , sp.STATUS AS [Status]
  , cmd [Command]
  , loginame AS [Login]
  , hostname AS [Host]
  , program_name AS [Program]
  , ec.client_Net_Address [IPv4]
  , ec.Client_TCP_Port AS [Port]
  , Last_Batch
  , ST.Query [Transaction]
  , ST2.Query AS [MostRecentStmnt]
 FROM sysprocesses SP
 LEFT JOIN sys.dm_exec_connections ec ON ec.session_id = sp.spid
 CROSS APPLY (
  SELECT TEXT AS [processing-instruction(x)]
  FROM sys.dm_exec_sql_text(SP.sql_handle)
  FOR XML PATH('')
   , TYPE
  ) AS ST(Query)
 CROSS APPLY (
  SELECT TEXT AS [processing-instruction(x)]
  FROM sys.dm_exec_sql_text(ec.most_recent_sql_handle)
  FOR XML PATH('')
   , TYPE
  ) AS ST2(Query)
 WHERE blocked <> 0
  OR spid IN (
   SELECT SPID
   FROM CTE_Blks
   )
 )
SELECT CASE
  WHEN BlockedBy = 0
   THEN 'ROOT'
  WHEN BlockedBy <> 0
   AND EXISTS (
    SELECT 1
    FROM CTE_Tree s1
    WHERE s1.BlockedBy = t1.Session
    )
   THEN 'BRANCH'
  ELSE 'LEAF'
  END [NodeType]
 , t1.*
FROM CTE_Tree t1
ORDER BY [NodeType]

To add some explanation to this:

ROOT is obviously the orphaned session and should be KILLed.
A LEAF is a blocked session which is not, itself, blocking other transactions.
A BRANCH is a blocked session which, due to its own locks, is blocking another session--either another BRANCH in the chain, or a LEAF (terminal).

The use of returning our sql_handle text as an XML processing-instruction
may seem odd, but was deliberate to circumvent truncation, preserve formatting, and bypass the need to explicitly escape characters.


mysqlslap running very slow on a specific computer

Well, I found out what the simple solution was. I needed to configure mysqlslap with --host and --user options for some reason yet unknown.


Why does MySQL choose this execution plan?

An indexed column in InnoDB always has an additional key to the gen_clust_index (aka Clustered Index) attached to it. This would be traversed by the first query to get to row 4000000 in the order of the index. Since it is the only column being requested, access to the table is unnecessary.
The second query has to collect the non-indexed column from the table along with the indexed column into a temp table. The temp table is then sorted before presenting the non-indexed column as SELECT output.
Notice another contrast

The table count is 4636881
The EXPLAIN plan for the first query traversed 4000010 indexed_column keys. No need to read the last 636871 keys.
The EXPLAIN plan for the second query traversed 4636881 rows ordered by indexed_column. For every row picking up the non-indexed column out of the table, the indexed column (already sorted by the index) is looked up and comes along for the ride. The tmp table gets ordered by the indexed column, and mysqld then dismisses the first 4000000 rows, leaving 10 rows. All that interaction between table and index just for 10 rows is the bottleneck.

COMMON THINGS
In both instances, the query specfies the number of rows to traverse. Since the number of rows in the table is 4636881, we should readily expect a full scan. The contrast becomes apparent when the MySQL Query Optimizer decides where to perform the full scan.

The first query is referencing an indexed column only in the SELECT list and the WHERE clause. The MySQL Query Optimizer chooses to perform a full index scan with no need to contact the table since everything needed is in the index.
The second query is referencing an indexed column in the WHERE clause. However, it must reach out the table to retrieve the corresponding non-indexed column. The MySQL Query Optmizer was tipped off by the query that it must not use the index becasue of the number of rows it was expected to read. As a rule of thumb for any RDBMS, if more than 5% of a table has to be read to fulfill a query, the MySQL Query Optimizer will just throw the index 'under the bus' and do a full table scan.

Doing the math, here is what MySQL Query optimizer computes:

5% of 4636881 is 231844
The second query is commanded to read 4000000 rows, which is way higher than 231844
MySQL Query Optimizer realizes there would be too much interaction between table (because of the non-indexed column) and index (because of the indexed column) to get the needed data. It decides to read the table only (because both the indexed and non-indexed columns are residing together in the table) rather than bounce back and forth between them.

In my honest opinion, with the row count of the table, the present indexes the table has, and the number of rows prescribed by the query, the MySQL Query Optimizer made the correct decision.
RECOMMENDATION
Create this index
ALTER TABLE `table` ADD INDEX mynewndx (indexed_column,some_other_column);

and your second query will never touch the table again going forward. The MySQL Query Optimizer will behave quite differently when it sees this new index.


You can use pg_depend table to resolve that :

select objid::regclass
from pg_depend
where refobjid = (select oid
                  from pg_namespace
                  where nspname='mySchema');


A backup was restored to the server sans views.  Simple really.


Importing CSV file to MySQL via PHPMyAdmin

What you're looking for is...

ESCAPED BY '"'

In CSV, one way of including literal quotation marks inside a single field is by doubling them up.

In the example file you posted, that's exactly what's happening at line 44.

The rest of your statement looks correct.  Replace the ESCAPED BY part with what I've shown above, and you should be good.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

  Occurrences of the ENCLOSED BY character preceded by the ESCAPED BY
  character are interpreted as part of the current field value.

...which is exactly what you're needing.


Query session parameter values of running session

I know this question is more than a year old, but in case anyone stumbles here like I did, you can query V$SES_OPTIMIZER_ENV:

SYS> select name, value
        from  V$SES_OPTIMIZER_ENV
        where sid=54
           and name='hash_area_size';

NAME                                     VALUE
—————————————- ————————-
hash_area_size                           100000

Source: http://oracleinaction.com/other-session-params/


How to insert the gathered dates into another table using INSERT SELECT in MySQL?

PROPOSED QUERY

insert into rfdate (date)
select selected_date from
(
    select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date
    from
        (select 0 t0 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t0,
        (select 0 t1 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t1,
        (select 0 t2 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t2,
        (select 0 t3 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t3,
        (select 0 t4 union select 1 union select 2 union select 3 union select 4
        union select 5 union select 6 union select 7 union select 8 union select 9) t4
) A where selected_date between '2015-02-01' and '2015-02-28'
and dayname(selected_date) in ('Monday', 'Tuesday');

PROOF THAT IT WORKS

mysql> DROP DATABASE jeffvalerio;
Query OK, 1 row affected (0.02 sec)

mysql> CREATE DATABASE jeffvalerio;
Query OK, 1 row affected (0.00 sec)

mysql> USE jeffvalerio
Database changed
mysql> CREATE TABLE rfdate
    -> (
    ->     id int not null auto_increment,
    ->     date date,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rfdate (date)
    -> select selected_date from
    -> (
    ->     select adddate('1970-01-01',t4*10000 + t3*1000 + t2*100 + t1*10 + t0) selected_date
    ->     from
    ->         (select 0 t0 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t0,
    ->         (select 0 t1 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t1,
    ->         (select 0 t2 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t2,
    ->         (select 0 t3 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t3,
    ->         (select 0 t4 union select 1 union select 2 union select 3 union select 4
    ->         union select 5 union select 6 union select 7 union select 8 union select 9) t4
    -> ) A where selected_date between '2015-02-01' and '2015-02-28'
    -> and dayname(selected_date) in ('Monday', 'Tuesday');
Query OK, 8 rows affected (0.09 sec)
Records: 8  Duplicates: 0  Warnings: 0

Here is the table's contents

mysql> select * from rfdate;
+----+------------+
| id | date       |
+----+------------+
|  1 | 2015-02-02 |
|  2 | 2015-02-03 |
|  3 | 2015-02-09 |
|  4 | 2015-02-10 |
|  5 | 2015-02-16 |
|  6 | 2015-02-17 |
|  7 | 2015-02-23 |
|  8 | 2015-02-24 |
+----+------------+
8 rows in set (0.00 sec)

mysql>

GIVE IT A TRY !!!


Free universe tool monitoring all database

Not that I'm fond of it, but if you use Nagios, it's rather easy to add some database-related plugins. You could do worse than have a look at:

PostgreSQL: Very useful. The latest versions are available at Bucardo.
MySQL: quite a few are available, make your pick.
Oracle: but you'd better ask the Oracle experts.
MsSQL: I'm not using it yet but will test it soon.


Check your output for DBCC LOGINFO within the context of your database.  This will show you the status of all VLFs within the log file.  You shrink operation will not reduce the log file past the last active one (Status=2).  As VLFs are used in a sequential, round-robin fashion, you will need to run transaction log backups enough times until your active VLFs are at physical point in the file that you want to shrink to.

While you state the database is not very active, transaction log backups once a day seem very infrequent and, depending on the kind of transactions that run against this database, could be contributing to the continued growth of this database.  I would recommend running your log backups at least hourly.  If you don't need this kind of recovery in your database and just need to restore to sometime in the last 24 hours, I suggest you convert the database to SIMPLE mode and do a full backup once a day.

Mike Walsh provides a detailed explanation here.

Your reindexing will do very little to actually shrink or allow the file to shrink and, in fact, may hamper it.  This is because reindexing will create additional transactions in your log that will be active until you run a log backup.

Additionally, some best practices recommendations:

Turn off autoshrink.  This is an incredibly bad practice for a number of reasons.  You are likely creating more problems for yourself by enabling this "feature".
Do not ever use truncate_only.  This basically makes your log useless for purposes of recovery.  You might as well go with SIMPLE mode and full backups at this point.


You can setup a linked server on SERVER_A to SERVER_B and access the databases using four part naming (SERVER_B.database.schema.table). See How to Create a Linked Server in the documentation.

You can also just add a secondary IP address to SERVER_B.

If Server_B is on the same VLAN as Server_A, you can add an alias to its IP address at the client level. But what you want to do is not very feasible.


Set all images name of a product to the same field in other table separated by comma in MySQL

You can use GROUP_CONCAT().

UPDATE importer
JOIN (
    SELECT product_reference, GROUP_CONCAT(name) AS name
    FROM Product_images
    GROUP BY product_reference
) pi ON importer.sku= pi.product_reference
SET importer.images = pi.name;

I should mention though, that your schema design may not be the best, depending on what you want to do. Read about normal forms, if you don't know what that is. A OLTP database usually is in at least 3rd normal form. Comma separated values violate the 1st normal form already.


SSMS shortcut to comment/uncomment a specific part of the query?

You can easily do this with Code Snippets, something like this will work for your example:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
    <_locDefinition xmlns="urn:locstudio">
        <_locDefault _loc="locNone" />
        <_locTag _loc="locData">Title</_locTag>
        <_locTag _loc="locData">Description</_locTag>
        <_locTag _loc="locData">Author</_locTag>
        <_locTag _loc="locData">ToolTip</_locTag>
        <_locTag _loc="locData">Default</_locTag>
    </_locDefinition>
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title>SLASH STAR COMMENT (surrounds)</Title>
            <Shortcut></Shortcut>
            <Description>Surround selected text with slash star comment block /**/</Description>
            <Author>wBob</Author>
            <SnippetTypes>
                <SnippetType>SurroundsWith</SnippetType>
            </SnippetTypes>
        </Header>
        <Snippet>
            <Declarations>
                <Literal>
                    <!--<ID>x</ID><ToolTip>y</ToolTip><Default>z</Default>-->
                </Literal>
            </Declarations>
            <Code Language="SQL">
                <![CDATA[/* $selected$ $end$ */]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

Save the file with a .snippet extension and follow the instructions from here to add the snippet to SQL Server Management Studio (SSMS).

Instructions for use

highlight the text you want to surround with the comment block in the query editor
Press Ctrl+K, Ctrl+S to summon the 'Surround With' snippets.  Note the comma between the two sequences, it means "press Ctrl+K, then press Ctrl+S in quick succession"
Select your snippet from the list
Your highlighted text is surrounded with the comment block
press Ctrl+Z to Undo - this is the normal windows undo command.  It will remove the comment block as long as you haven't made any other edits.


I think what you're looking for is, "What are all the things I need to keep in mind when designing a data warehouse?"

There's a lot of good resources out there on it:

https://www.cu.edu/irm/stds/ddg/dzproces.html
http://www.slideshare.net/markginnebaugh/architecting-a-data-warehouse-a-case-study
http://msdn.microsoft.com/en-us/library/aa902672%28SQL.80%29.aspx


To expand on MguerraTorres' answer:

(Updated with the info from your secondary query)

In your first query UPDATE cte says to update the table from the CTE.

FROM cte as a says to refer to the table from the CTE as a.

So, we've referred to our CTE in two places.

What you may not realize is that a CTE is re-evaluated for each time it appears in your query, just as if you replaced the reference with a subquery. Since you've referenced the CTE two separate times, you've generated two separate resultsets for the DB engine to work with.

When you say to use b.Value where a.ID = b.ID, we've got two rows - one where b.Value is 100, and one where it's 200 - from table b and from our second CTE resultset.

However, we're updating the first CTE resultset based on these two rows. Therefore, it updates each row in that first resultset from the two rows returned. There's no relationship between the two resultsets, even though they represent the same underlying data. The engine is doing a CROSS JOIN between the results of your join, and the first resultset, to perform the update.

Your UPDATE statement updates both of your rows to to 200, then to 100 (because the engine decides the quickest way to apply the cross-joined rows, they may not go in the order in which they were entered). Both rows are updated to the same value because they're being updated from the same multiple rows.

Your first query is functionally identical to:

DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);

WITH cte AS
(
    SELECT * FROM @a
)
UPDATE cte
SET    Value = b.Value
FROM   (SELECT * FROM @a) AS a
INNER JOIN @b AS b
ON     b.ID = a.ID

SELECT * FROM @a
GO

In your second query, the DB engine knows that both a and @a reference a table outside the query, and it knows that a and @a mean the same thing, so it correctly ties the rows from @b to @a when performing the update.

In the comments, you asked:

  Would the result be always be 100 for both? or can it be 200 for both sometimes -- As I see there is no clear rule here?

Whether it's 100 or 200 can vary.

I would say that it's likely that, given the same statements shown in your first query, executed in the same way, you would almost certainly get the same outcome.

However, in the real world, with tables seeing other activity, you couldn't really on one outcome or the other, especially over time. It would depend on how the DB engine matched the tables in the join, and then processed the rows in applying the update.


Probably best to:

Import the entire CSV to a table using Import and Export Wizard.
Run the query to pull the data you want from the table.
Use the Import and Export Wizard to import to the other database.
Clean up the CSV import, probably by truncating or deleting the table.

Unless you want to use SSIS? Then you can design a package to do it easily without importing the whole file directly.


Always On Availability Groups on Windows 2012 Standard

Per the information you provided from SELECT @@VERSION you are running the x86 version of SQL Server 2012. It would appear that the OS and SQL Server architecture must match in order for Availability Groups to function.

This means that you would either need to change your SQL Server installation to the x64, or go to a prior version of Windows (2008) and install the x86 version of that.

My recommendation would be to adjust your existing install and make it x64. This provides many enhancements such as not needing to mess with things like PAE and AWE when using more than 4GB of memory.


SQL a stored procedure (CPU~19%, Disk 1MB read, 05.MB write) it is slower than on my laptop (i7,16 GB ram,SSD and SQL Express....).

Probably because your laptop is faster than your server.

Many i7s are significantly faster than a 2.2Ghz Xeon for single-threaded operations.  And not all "SSD" drives are the same.  The flash storage devices on modern laptops can be much faster than what might be used by your hoster.

The Xeon has more cores, and so can perform more work overall.  So a realistic load test should show that the server can support more concurrent users.  But for a single user, you shouldn't expect the server to be faster

  How to improve performances on my VPS?

So that's normal SQL Server performance analysis and optimization.  Good news is that you can do the testing and optimization work on your laptop :)


Mongo DB Replica set - not able to read also

The reason

The answer is quite simple: when two voting nodes of three are down, the remaining one reverts to secondary, to which – by definition – one can't write. The default read preference is to read from and write to the primary of a replica set only. Hence, when only a secondary remains in the replica set, you can not read.

tl;dr

You have to change your read preference at least to primaryPreferred, allowing reads from secondaries.


count(id) on Char(0) column does not work!

COUNT(somecolumn) ignores NULLs
COUNT(*) won't

You can see this with

SELECT count(id),count(*) FROM test WHERE id IS NULL

Coincidently, all aggregate functions ignore NULL except COUNT(*)


A variation on @a_horse_with_no_name's answer would be to first create the table with the constraint and then insert into the table, still using a transaction, so everything rolls back in case of constraint violations. This is something you should consider if the rows to be inserted are a lot (i.e. in the hundreds of thousands or millions).

If it's difficult to create the create table statement dynamically, you could separate that part into 2 statements, with something like this:

begin transaction ;
    create table new_table                       -- create the table
    as
    select v.*
    from (select ....) as v                      -- as before
    where false ;                                -- but insert 0 rows

    alter table new_table add constraint ... ;   -- add the constraint

    insert into new_table                        -- and then attempt
    select ... ;                                 -- the insert
end ;

The difference with the other way is that the constraint will be there before any row is inserted into the table. And since NOT NULL and CHECK constraints are not deferrable but always checked immediately when a row is inserted or modified (and not at the end of the statement), your insert statement will fail (and rollback) with the first row that violates the constraint(s) you have placed.

If you are inserting multi-millions of rows, it will be faster, both because it may find an error more quickly but also because it will have to rollback less insertions.


I can think of two possible explanations for why you experience poor performance with that query.

The first relates to blocking. Have you checked the wait events while the query is running? If it is blocked by another session and waiting for a lock then you don't have a performance problem. Instead, you have a concurrency problem.

The second relates to the table and index structure on the table that you are deleting from. The PriceDiffs table has 194807000 rows. If you don't have an index that can seek on the UserID column it could take a long time to find the single row to delete. There's also a scan on the PriceDifferenceRecords table which I assume is there to enforce a constraint or a foreign key, but that table only has 15843 rows.

You might be wondering why the query takes two minutes to run when it has a relatively low estimated subtree cost of 0.0569578. This is probably due to the row goal introduced to the query by your use of TOP 1. SQL Server scans the IX_PriceDiffs_PriceDate index to find a matching row but the filter is implemented as predicate instead of a seek predicate. I assume this is because UserID is not a key column. Thus, SQL Server scans the index in order until it finds a row where UserID = 384166. If you are really lucky the first matching row could be in the first page of the index. If you are really unlucky the first matching row could be in the last page of the index, so your DELETE query would scan the entire table to delete just one row.

It's worth noting that deleting just one row from a table often isn't a good way to test performance when you need to delete lots of rows. For a starting point, consider creating an index on UserId and deleting in batches.


Getting Records with Min Max Value on Grouped in Single Table in postgresql

I've reduced a bit your sample data by entering only the first 10 rows.

create table tbl (id int, dt date, code text, trans text, rate int);

insert into tbl values
(1, '20181201', 'AAA', '2018120101', 399),
(2, '20181201', 'BBB', '2018120102', 244),
(3, '20181201', 'AAA', '2018120103', 400),
(4, '20181201', 'AAA', '2018120104', 397),
(5, '20181202', 'AAA', '2018120201', 395),
(6, '20181202', 'AAA', '2018120202', 405),
(7, '20181202', 'AAA', '2018120203', 399),
(8, '20181202', 'DDD', '2018120204', 355),
(9, '20181202', 'AAA', '2018120205', 402),
(10, '20181202', 'AAA', '2018120206', 403);

select
    row_number() over (order by t0.code, t0.dt) as id,
    t0.code,
    t0.dt,
    min_trans,
    t1.rate as min_rate,
    max_trans,
    t2.rate as max_rate
from
    (select   code, dt, min(trans) min_trans, max(trans) max_trans
     from     tbl
     group by code, dt) t0
join
    tbl t1
    on t1.code = t0.code
    and t1.trans = t0.min_trans
join
    tbl t2
    on t2.code = t0.code
    and t2.trans = t0.max_trans;

id | code | dt         | min_trans  | min_rate | max_trans  | max_rate
-: | :--- | :--------- | :--------- | -------: | :--------- | -------:
 1 | AAA  | 2018-12-01 | 2018120101 |      399 | 2018120104 |      397
 2 | AAA  | 2018-12-02 | 2018120201 |      395 | 2018120206 |      403
 3 | BBB  | 2018-12-01 | 2018120102 |      244 | 2018120102 |      244
 4 | DDD  | 2018-12-02 | 2018120204 |      355 | 2018120204 |      355

db<>fiddle here

The first sub-query calculates MIN and MAX trans value grouped by code and date.

from
    (select   code, dt, min(trans) min_trans, max(trans) max_trans
     from     tbl
     group by code, dt) t0

Then you can join twice your original table using code and max/min trans value fetched by the first sub-query.

join
    tbl t1
    on t1.code = t0.code
    and t1.trans = t0.min_trans
join
    tbl t2
    on t2.code = t0.code
    and t2.trans = t0.max_trans;

Adding a WHERE clause you can filter the final result by code.


Finally found the solution by myself:

in the systemd service configuration file /lib/systemd/system/mongodb.service
set the following values to override the systemd tasklimit:

# (disable systemd tracking of number of tasks)
TasksAccounting=false

# (disable systemd tasklimit) possible values: infinity, a absolute number, or percentage of system wide limit
TasksMax=infinity


SQL Server 2012 is putting [brackets] around Table and Column Names

There isn't a way to prevent this. Microsoft feels that it is better to prevent you from shooting yourself in the foot, probably since the number of people out there who create databases, objects or columns with bad names (e.g. my table or date) or reserved keywords (e.g. log or timestamp) outnumber those who never do so.

There was a Connect item asking for this option, and it was very quickly dismissed and closed as "Won't Fix." And in fact many more users were asking that square brackets be added to places where they previously didn't work.


Best Approach to Migrating Oracle to MySQL - Run on EC2 in AWS

There are two tasks here. The migration to MySQL and setting up AWS. Setting up AWS is off topic here - try Superuser or Stackoverflow.

You are in for a tough job. MySQL is a much simpler database and does not have as many features as Oracle. Also many things in Oracle are non-standard making it difficult to migrate to / from.

The process is basically as follows:

Dump Oracle database schema / DDL. You will end up with a file full of CREATE statements
Update the DDL to allow for types that Oracle does not support. Read the MySQL manual. Use InnoDB as the backend
Dump Oracle functions
Rewrite the Oracle functions. This is probably going to be the most time consuming step. You will have to look at the Oracle and MySQL documentation
Run your converted DDL and functions against your new MySQL database
Dump the data from Oracle
Restore the data into MySQL. You may have to temporarily disable some contraints


group result of multiple table to get unique result after count operation

I've modified your query and I used CASE statement to COUNT:

Query:

select
    survey_categories.survey_cat_id,
    survey_question.ques_id,
    survey_details.store_id,
    count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent,
    count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good,
    count(CASE WHEN survey_details.answer = 'good' THEN 1 END) AS Good,
    '' AS Average,
    count(CASE WHEN survey_details.answer = 'poor' THEN 1 END) AS Poor
from survey_categories
INNER JOIN survey_question ON survey_categories.survey_cat_id = survey_question.survey_cat_id
INNER JOIN survey_details ON survey_question.ques_id = survey_details.ques_id
group by survey_categories.survey_cat_id,survey_question.ques_id
order by ques_id,answer;

Test:

mysql> select
    -> survey_categories.survey_cat_id,
    -> survey_question.ques_id,
    -> survey_details.store_id,
    -> count(CASE WHEN survey_details.answer = 'excellent' THEN 1 END) AS Excellent,
    -> count(CASE WHEN survey_details.answer = 'very Good' THEN 1 END) AS Very_Good,
    -> count(CASE WHEN survey_details.answer = 'good' THEN 1 END) AS Good,
    -> '' AS Average,
    -> count(CASE WHEN survey_details.answer = 'poor' THEN 1 END) AS Poor
    -> from survey_categories
    -> INNER JOIN survey_question ON survey_categories.survey_cat_id = survey_question.survey_cat_id
    -> INNER JOIN survey_details ON survey_question.ques_id = survey_details.ques_id
    -> group by survey_categories.survey_cat_id,survey_question.ques_id
    -> order by ques_id,answer;
+---------------+---------+----------+-----------+-----------+------+---------+------+
| survey_cat_id | ques_id | store_id | Excellent | Very_Good | Good | Average | Poor |
+---------------+---------+----------+-----------+-----------+------+---------+------+
|             1 |       1 |      102 |         2 |         0 |    0 |         |    0 |
|             1 |       2 |      102 |         0 |         2 |    0 |         |    0 |
|             1 |       3 |      102 |         0 |         0 |    0 |         |    0 |
|             2 |       4 |      102 |         0 |         2 |    0 |         |    0 |
|             2 |       5 |      102 |         0 |         0 |    0 |         |    2 |
|             3 |       6 |      102 |         2 |         0 |    0 |         |    0 |
|             3 |       7 |      102 |         0 |         0 |    0 |         |    0 |
+---------------+---------+----------+-----------+-----------+------+---------+------+
7 rows in set (0.00 sec)

mysql>

By the way, for AVG, what is your expected calculation against which fields?


How can I trace the cause of a memory spike in Postgres?

First, you should configure Linux so that it does not overcommit memory, because there's nothing you can get from a backend that is killed by the OOM killer.
Set vm.overcommit_memory = 2 and set vm.overcommit_ratio to the value calculated as
100 * (RAM - swap) / RAM

Then you should get an OOM error from PostgreSQL rather than PostgreSQL going into crash recovery.
Such an OOM error will cause a memory context dump in the PostgreSQL log file, so you can see which memory context uses all that memory. Also, you get the query that caused the error, so you can experiment with that.


loading a csv file which is on local system in to Mysql DB which is on remote server

Are you looking for LOAD DATA LOCAL INFILE?

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

http://dev.mysql.com/doc/refman/5.5/en/load-data.html

Update: The original question exhibits a significant misunderstanding about LOAD DATA INFILE that originally escaped my attention:

'load data infile into table name' command can only be used for loading in local system only.

The client was referred to as being "local" and the server was referred to as being "remote," which makes that statement 100% incorrect.
LOAD DATA INFILE requires that the file already be on the server's filesystem, and adding LOCAL means it must be on the client's filesystem.
From the documentation:

If LOCAL is specified, the file is read by the client program on the client host and sent to the server.
If LOCAL is not specified, the file must be located on the server host and is read directly by the server.


How to model medium.com paragraph comment

You can insert a comment marker in the text itself. This marker is not displayed. As text is being changed, the marker stay embedded in the text itself and moves around as text is being edited. If the paragraph is removed then the comment can be orphaned and garbage collected later. Of course, this has nothing to do with DBs, is just pure text processing.

If you want a DB centric approach then the paragraphs are assigned primary keys during creation and the comments reference this primary key. As PK, of course, they never change. Order of paragraphs within text can be persisted as a relation previous-current-next (implemented as a separate table, or as next_id and prev_id fields. You can also throw in versioning (history). I'm not a editable text storage expert so I don't know what is the state of the art.

Your problem, as you state it, is not a problem of text processing but a problem arrise from the use of a natural primary key (paragraph position or paragraph hash). Of course, we all know that natural keys are bad because they change. In your example they change very frequently. Using surrogate keys solve the problem of PK volatility. the rest is just a modeling exercise on storing the relations (order of paragraphs in text, paragraph changes as text evolves etc). I'm letting as an exercise to you the problem of paragraph split during edit (which shard inherits which comments) and the problem of paragraph merge.


Differential backups in SQL Server 2008 R2

Differential backups include data that has changed since the last full backup (ignoring any full backups taken with the COPY_ONLY option).

If you take a differential backup immediately after a full backup, it will be very small, as little (or no) data will have changed. As time goes on, the differential backups will become larger and larger, until you do another full backup, which resets the differential change map.

Note that you must still have the last full backup taken prior to the differential backup, or the differential backup will be useless.


How to output query to file using SQLite3

The sqlite3 command-line tool has the .output setting.


Table structure for keeping track of multiple similar accounts

A separate table for each account will be difficult to work with.  For example, if you need aggregate values spanning all 30 accounts, you would need to look in all 30 tables.  And if you need aggregate values for only a subset, say 5, of those accounts you would need to look in just 5 tables but then you must specify the names of those 5.  It gets too darn fiddly fast.

You should consolidate those account tables into a single Accounts table which includes a field to identify which account each row is associated with. And when you want to see all the information for any one account, instead of an actual table, you can dynamically create a virtual table with an appropriate SELECT query.

SELECT [field expression list]
FROM Accounts
WHERE acct_num = 32;

Also think about happens when/if you need to modify the table structures.  You really don't want to have to make the same change to 30 tables.  BTW, I'm also uneasy about needing to add a new column each year.  Aim for a design which doesn't require that type of change.  Store information for different years by row rather than by column.  A rule of thumb is "columns are expensive; rows are cheap".  Try to work cheap!  :-)


What could justify a non explicit - non atomic primary key

in what circumstances would it be justified to use a primary key that is a combination of the type of the record(let's say, CH for Chinese food, In for Indian and MX for Mexican) and a sequence number ?

Under no circumstances would it be justified.  A column containing two different data elements as one value is a violation of first normal form.  1NF requires, among other things, that each column contain a single value.  This practice also destroys the guaranteed logical access of every data element by table name, column name, and key value.  Now if each data element were in its own column, and the combination of columns made up a key, that is not a problem.  Ultimately this depends on the entity type for which the table holds entity occurrences.  The key chosen should be something used by people in the real world to identify occurrences.  Perhaps this makes sense if we are talking about a menu, and the menu has a section for Chinese food, and then gives each dish a number like #1, #2, etc.

  Second, in what circumstances would it be justified not to have a primary key defined but you have a column that distinctively distinguish each row ?

Again, under no circumstances.  If there is a column whose values uniquely identify each row that column must be declared as a unique constraint so as to ensure the occurrences entered are not duplicated and thus become inconsistent with the real world entities they represent.

  Third, is my perception correct that I see the combination of the type string(CH, IN, MX) and a sequence number is against the 1st normalization ?

Absolutely, as described above.

  Fourth, in the table, there is only one "type string" value (let's say CH). Is it okay to add that string to the pk while it does not seem necessary since there is only one value ?

No, for the same reason as this violates 1NF.  Again that column contains 2 different values and each column must contain only a single value to be in 1NF.

Fabian Pascal's Practical Database Foundation Series is a great reference to dive more deeply into these fundamentals.  Paper #4 addresses keys specifically.


Is there some kind of table-level replication in PostgreSQL?

I finally managed to find a solution that works so far. I installed plpython procedural language module along with the psycopg2 module, wrote a FUNCTION and created two TRIGGERs, one for INSERT and one for DELETE (I won't have any UPDATE events).

The function code would be something like this:

CREATE OR REPLACE FUNCTION synchronize()
RETURNS trigger AS
$$
    import psycopg2

    conn = psycopg2.connect("host=... dbname=... user=... password=...")
    cur = conn.cursor()

    # If inserting...
    if TD['event'] == 'INSERT':
      cur.execute(...)

    # If deleting...
    if TD['event'] == 'DELETE':
      cur.execute(...)

    conn.commit()
    cur.close()
    conn.close()
$$
LANGUAGE 'plpython2u' VOLATILE;

Then, I defined both triggers:

CREATE TRIGGER replica_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE PROCEDURE synchronize();

CREATE TRIGGER replica_delete
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE PROCEDURE synchronize();

Maybe over-complicated, or patchy, but right now this is the easiest way I found how to do it. If someone improves it or has an alternative (preferably native) way to do it, I'll be glad to change the accepted answer.


Change Oracle user home directory at OS level after Oracle Databse/Client Software Installation

As per your comment:

  Yes, I meant the home directory of oracle user at OS level!

You can easily achieve that.

We have a user called ora

[root@myserver /]# su - ora
[ora@myserver ~]$ pwd
/home/ora

And it has its home directory as /home/ora.
Let's create another home directory for the user ora.

[root@myserver ora]# mkdir -p /home/profile

Let's move the files such as .bash_profile and directories like Desktop and Documents, from the old home directory to new one.

[root@myserver ora]# cp -a /home/ora /home/profile/

Finally, modify the user to change the home directory. Make sure that there are no active sessions exist for the user.

[root@myserver ora]# usermod -d /home/profile/ora/ ora

Let's check the result.

[root@myserver ora]# su - ora
[ora@myserver ~]$ pwd
/home/profile/ora/


Getting conversion error, for value that doesnt exist in database

I was able to solve the problem by thinking about the error message for a minute. The error message says something about not being able to convert from varchar to int. So I looked for columns that should store a number, but were actually saving it as a varchar. The necessity of a cast was an indicator.

So, I looked through each column in the EmployeeAttendance table, and found the offending record.

Empname A B was stored in a column which should have either 1 or 0 values. This column is not a BIT, but a varchar(50)

dies in shame

This table was a hand-me-down, and I think I need to get my janitors uniform out.


The point of COPY is to receive the data from outside the query.
The closest syntax to what you're asking is

COPY users(username) FROM STDIN;

If done from within the psql interpreter in interactive mode, it displays this invite:

  Enter data to be copied followed by a newline. End with a backslash
  and a period on a line by itself, or an EOF signal.

If done from a non-interactive program, dedicated functions can be used to send the data. They're provided by the PostgreSQL driver that the program uses.


How does one scale out with master-slave replication?

I have to keep my answer very generic as you have not mentioned any particular database system and what type of master slave implementation you want to know about.

However, they can only write to the master.

True in most cases but there are solutions where you can write to more than one target at the same time. Your data will be replicated to both nodes, synchronized and conflicts will be resolved.

Oracle has multi master implementation. Generic idea explained here.

SQL Server has merge and peer to peer replication. In merge replication where you can update on the subscriber side and data will be merged between two. Details here. In peer to peer replication there is no conflict resolution compare to merge replication.  Therefore it is recommended only in configurations where only one copy of a given data element is updated. For example, if Peer-to-Peer replication is used to maintain the inventory of a chain of food stores, only the store that owns an inventory entry would be allowed to update that entry. Details here.

I also understand one-way replication seems generally preferred to two-way for the same reasons.

I would not say it is preferred but definitely used less compare to one way replication. It will depend on your need. Main challenge with two way replication is conflict between 2 or more masters. Conflicts might occur and, when they do, you need the ability to detect and resolve them.

How do you tell an application to write to one database and read from another? Does this mean this actually has to be programmed into the application layer?

Depends. As an example SQL Server Always On Availability Group you do it by configuring your secondaries available to read by clients. Then you set up your connection string in such a way that your reads will be automatically routed to read_only nodes. Details here.

Whereas in MongoDB config server keeps track of primary and secondary. Each Mongos has this information. Mongos will redirect the traffic to the correct node based on your Read Preference setting. Details here.

How does the client compensate for latency when he reloads the page and it re-queries the local db (missing that write)? This seems so error-prone!

In SQL Server this is dictated by the setting of isolation level and implemented by lock/latch on different objects (database, table, page, row etc).  This is a good reading about this topic. I do not know the details for other DBMS, but I am sure it will be something similar.

How would this then scale? Understood that reads dominate a majority of a database's work. But what happens when you scale enough that one server can't handle all the writes? What options do you have?

In general by Sharding your objects horizontally. Sharding can be implemented in may different ways. I worked with MongoDB sharding for many years and it works perfectly. Details here. Some scaling out idea using SQL server explained here.


On Amazon RDS, what consumes storage space beyond the database files?

AWS support came back with a comprehensive answer.  In this case, it was transaction log backups, being written to the SQL disk before offloading to S3.  There are a few more things which can consume space, mostly temporarily.  As their tech outlined:

  As a best Practice, I would recommend to keep at around 5% free space for similar processes (Log Backups, Error Log Files, Trace Files, etc), however this may vary based on high transactional volume or bulk data upload process , which may need high disk storage temporarily.


How do I filter null values from a query?

If I'm interpreting your question correctly (that you want to return Y if any User_Data_Field for any matching row is 14), you can do something like:

SELECT CASE WHEN SUM(IIF(User_Data_Field1 = 14, 1, 0)
                    +IIF(User_Data_Field2 = 14, 1, 0)
                    +IIF(User_Data_Field3 = 14, 1, 0)
                    +IIF(User_Data_Field4 = 14, 1, 0)
                    +IIF(User_Data_Field5 = 14, 1, 0)) > 0 THEN 'Y'
ELSE 'N'
END USER_DATA_FIELD
FROM RHOP..EDSLKP.VENDOR_SITES VS
WHERE vendor_id = '91'
GROUP BY VENDOR_ID
ORDER BY VENDOR_ID

IIF is only supported in SQL 2012 and above, so prior to that you'll have to stick with CASE WHEN.


Can i restore mysql database to point of time when it was just installed/factory default?

For PIT recovery, see if log_bin was turned on.

DROP table/database/etc promptly and irrevocably removes the data and schema.  DROP cannot be in a transaction (until Version 8.0).

If you are hoping for a "recycle bin", be aware that COMMITting a transaction effectively flushes what you might think of as a recycle bin.


Replacement for Nested SELECT statements for every row in SELECT clause

It's the classic EAV pattern, which is a strong indicator of a design issue. I hope you understand that this design is against the core rules of relational databases. I have a blog post on this subject if you want to dig deeper.

However, if the design can't be changed, you can use a PIVOT query to extract your data:

DECLARE @BaseTable TABLE (
    IDKey int PRIMARY KEY,
    Name varchar(50)
)

INSERT INTO @BaseTable
VALUES
(1, 'Ian'),
(2, 'John'),
(3, 'Ann');

DECLARE @Table1 TABLE (
    ID int,
    Amount int,
    Type char(1),
    PRIMARY KEY (ID, Type)
)

INSERT INTO @Table1
VALUES
(1, 100, 'X'),
(1, 200, 'Y'),
(2, 300, 'X'),
(2, 400, 'Y'),
(3, 500, 'X'),
(3, 600, 'Y');

SELECT *
FROM @BaseTable AS B
INNER JOIN (
    SELECT ID, X AS AmountX, Y AS AmountY
    FROM @Table1 AS T
    PIVOT (MIN(Amount) FOR Type IN ([X],[Y])) AS P
) AS T1
    ON B.IDKey = T1.ID;


The first thing that catches my eye is the index setup for friends.

You have this at the moment:

friends
-------
userid
friendid
primary key (`userid`,`friendid`),
key `friendid` (`friendid`)

When crosschecking for mutual friendship, it could incur a little expense because the userid may be retrieved from the table when traversing the friendid index. Perhaps you could index as follows:

friends
-------
userid
friendid
primary key (`userid`,`friendid`),
unique key `friendid` (`friendid`,`userid`)

This might remove any need to access the table and search the index only.

Now, in terms of the queries, both of them may improve with the new unique index. Creating the unique index also eliminates the need to insert (A,B) and (B,A) into the table because (A,B) and (B,A) would be the index anyway. Thus, the second query would not have to gore through the table to see if someone is a friend of someone else because another person initiated the friendship. That way, if the friendship is broken by just one person, there are no orphaned friendships that are one-sided (seems a lot like life these days, doesn't it?)

Your first query looks like it would benefit more from the unique index. Even with millions of rows, locating friends using the indexes only would avoid touching the table. Still, since you did not present a UNION query, I would like to recommend a UNION query:

SET @givenuserid = ?;
SELECT B.name "Friend's Name"
FROM
(
    SELECT userid FROM friends WHERE friendid=@givenuserid
    UNION
    SELECT friendid FROM friends WHERE userid=@givenuserid
) A INNER JOIN user B USING (userid);

This will let you see who are the friends of each userid

To see all friendships, run this:

SELECT A.userid,A.name,B.friendid,C.name
FROM user A
INNER JOIN friends B ON A.userid=B.userid
INNER JOIN user C on B.friendid=C.userid;

First, here is some sample data:

mysql> drop database if exists key_ilyuk;
Query OK, 2 rows affected (0.01 sec)

mysql> create database key_ilyuk;
Query OK, 1 row affected (0.00 sec)

mysql> use key_ilyuk
Database changed
mysql> create table user
    -> (
    ->     userid INT NOT NULL AUTO_INCREMENT,
    ->     name varchar(20),
    ->     primary key(userid)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into user (name) values
    -> ('rolando'),('pamela'),('dominique'),('carlik'),('diamond');
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> create table friends
    -> (
    ->     userid INT NOT NULL,
    ->     friendid INT NOT NULL,
    ->     primary key (userid,friendid),
    ->     unique key (friendid,userid)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.03 sec)

mysql> insert into friends values (1,2),(2,5),(1,3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from user;
+--------+-----------+
| userid | name      |
+--------+-----------+
|      1 | rolando   |
|      2 | pamela    |
|      3 | dominique |
|      4 | carlik    |
|      5 | diamond   |
+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from friends;
+--------+----------+
| userid | friendid |
+--------+----------+
|      1 |        2 |
|      1 |        3 |
|      2 |        5 |
+--------+----------+
3 rows in set (0.00 sec)

mysql>

Let's look at all the relationships

mysql> SELECT A.userid,A.name,B.friendid,C.name
    -> FROM user A
    -> INNER JOIN friends B ON A.userid=B.userid
    -> INNER JOIN user C on B.friendid=C.userid
    -> ;
+--------+---------+----------+-----------+
| userid | name    | friendid | name      |
+--------+---------+----------+-----------+
|      1 | rolando |        2 | pamela    |
|      1 | rolando |        3 | dominique |
|      2 | pamela  |        5 | diamond   |
+--------+---------+----------+-----------+
3 rows in set (0.00 sec)

mysql>

Let's look at all 5 userid's and see if the relationships are correctly shown

mysql> SET @givenuserid = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
+---------------+
| Friend's Name |
+---------------+
| pamela        |
| dominique     |
+---------------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
+---------------+
| Friend's Name |
+---------------+
| rolando       |
| diamond       |
+---------------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
+---------------+
| Friend's Name |
+---------------+
| rolando       |
+---------------+
1 row in set (0.01 sec)

mysql> SET @givenuserid = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
Empty set (0.00 sec)

mysql> SET @givenuserid = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT B.name "Friend's Name"
    -> FROM
    -> (
    ->     SELECT userid FROM friends WHERE friendid=@givenuserid
    ->     UNION
    ->     SELECT friendid FROM friends WHERE userid=@givenuserid
    -> ) A INNER JOIN user B USING (userid);
+---------------+
| Friend's Name |
+---------------+
| pamela        |
+---------------+
1 row in set (0.00 sec)

mysql>

They all look correct to me.

Now, let's use your second query to see if it matches up...

mysql> SET @givenuserid = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+-----------+----------+
| name      | friendid |
+-----------+----------+
| pamela    |        2 |
| dominique |        3 |
+-----------+----------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 2;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+---------+----------+
| name    | friendid |
+---------+----------+
| diamond |        5 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SET @givenuserid = 3;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
Empty set (0.00 sec)

mysql> SET @givenuserid = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
Empty set (0.00 sec)

mysql> SET @givenuserid = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
Empty set (0.00 sec)

mysql>

Why don't match up? That's because I did not load the (B,A) for every (A,B). Let me load the (B,A) relationships and try your second query again.

mysql> insert into friends values (2,1),(5,2),(3,1);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SET @givenuserid = 1;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+-----------+----------+
| name      | friendid |
+-----------+----------+
| pamela    |        2 |
| dominique |        3 |
+-----------+----------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+---------+----------+
| name    | friendid |
+---------+----------+
| rolando |        1 |
| diamond |        5 |
+---------+----------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 3;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+---------+----------+
| name    | friendid |
+---------+----------+
| rolando |        1 |
+---------+----------+
1 row in set (0.00 sec)

mysql> SET @givenuserid = 4;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
Empty set (0.00 sec)

mysql> SET @givenuserid = 5;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid
    -> FROM friends f inner join user u ON ( u.userid = f.friendid )
    -> WHERE f.userid = @givenuserid;
+--------+----------+
| name   | friendid |
+--------+----------+
| pamela |        2 |
+--------+----------+
1 row in set (0.00 sec)

mysql>

They still don't match. That's because your second query is only checking one side.

Let's check your first query against every value with only (A,B) and not (B,A):

mysql> SET @givenuserid = 1;
SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
+-----------+--------+----------+
| name      | userid | friendid |
+-----------+--------+----------+
| pamela    |      2 |        2 |
| dominique |      3 |        3 |
+-----------+--------+----------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 2;
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
+---------+--------+----------+
| name    | userid | friendid |
+---------+--------+----------+
| rolando |      2 |        1 |
| diamond |      5 |        5 |
+---------+--------+----------+
2 rows in set (0.00 sec)

mysql> SET @givenuserid = 3;
SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
+---------+--------+----------+
| name    | userid | friendid |
+---------+--------+----------+
| rolando |      3 |        1 |
+---------+--------+----------+
1 row in set (0.00 sec)

mysql> SET @givenuserid = 4;
FROM friends f
    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
Empty set (0.01 sec)

mysql> SET @givenuserid = 5;
FROM friends f
Query OK, 0 rows affected (0.00 sec)

    inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
mysql> SELECT u.name, f.friendid userid, IF(f.userid = @givenuserid, f.friendid, f.userid) friendid
    -> FROM friends f
    ->     inner join user u  ON ( u.userid = IF(f.userid = @givenuserid, f.friendid, f.userid))
    -> WHERE ( f.userid = @givenuserid or f.friendid = @givenuserid  );
+--------+--------+----------+
| name   | userid | friendid |
+--------+--------+----------+
| pamela |      5 |        2 |
+--------+--------+----------+
1 row in set (0.00 sec)

mysql>

Your first works well. I am sure it is benefiting from the unique index as I said earlier, but IMHO I think the UNION is simpler. With than unique index, it would appear to be six of one and half dozen of the other in terms of execution and output.

You would have to benchmark your first query against my suggestion UNION and see.

This was a good question you asked today. +1 for your question.


Add SQL Server Agent to existing SQL Installation

Try to run a repair intallation from the installation media and then install service pack and cu after that.

You can run the repair from the command prompt:

Setup.exe /q /ACTION=Repair /INSTANCENAME=instancename

Or by opening the installation center and select Maintenance in the left-hand navigation area, and then click Repair to start the repair.

Step-by-step instructions are found in the documentation


Is it possible to allow write-through computed columns for legacy code that expects a column to be read/write?


Do you have any replications setup?
Maybe you also want to include a step to remove those incoming or outgoing replications  after your step 2?

It should be fine even if you omit this step, but it might generate unnecessary alerts (if you have set monitoring for replication) to the oncall.


Can't see some stored procedures in sys.procedures

To ensure you can see the objects you are looking for:

Make sure you are in the right database / right instance - seems simple and silly but it happens all the time. This also includes not using things like AttachDbFileName in your connection string - this means that two different programs will actually have two separate copies of your database, so if one adds a procedure, the other will never see it.
Don't add unnecessary filters that don't accomplish anything (sys.procedures already filters out system objects, and if you use the sp_ prefix for your own procedures - which you shouldn't - you probably don't want that filter to leave those objects out, either).
Validate that you have VIEW DEFINITION access to all of the potential schemas where these objects may live. These are going to be protected in the metadata / catalog views but may still be exposed to you in certain scenarios under Object Explorer (especially if your Object Explorer connection is running under a different context than your query window(s)).

Also note that these are potential answers for future readers too, not just for the current OP (who we now know had an issue with the last bullet).


How to sync daily the prod database to dev database in Azure SQL Server service?

You can try "Geo-replication"
Go to your prod "SQL Database", Settings > Geo-replication
Configure your dev server/database to be readable "secondary"
All changes made at prod database , will be applied to dev database right away
Beware that you won't be able to change (insert,update,delete,create,etc) data at dev database while it is in the "readable secondary" role - only read (select)


I would expect that after shutting down all other sessions and running
  a VACUUM ANALYSE, the way the tables are used wouldn't make any
  difference.

It's the VACUUM ANALYZE that does not make a difference on the size of tables. Because it does not immediately recover the bloat resulting from old versions of rows, it merely marks these rows are reusable.

From the documentation (VACUUM):

  Plain VACUUM (without FULL) simply reclaims space and makes it
  available for re-use

Only VACUUM FULL would recover that space, by rewriting the table from scratch with only the live rows.


Estimating a Size of Joining a Relation with itself

Doing a natural self-join is like joining on all the columns, so like joining on the primary key (as the set of all columns is certainly a superset of the primary key.) Every row of the left copy of the table will be joined to exactly one row (its copy) of the right copy of the table.

Therefore, the size of the resulting set R NATURAL JOIN R will be exactly the same as the original R itself (actually the situation is even better, the resulting set is exactly the original R.)

If on the other side, you self-join on (one or more) columns that are not a superset of the primary key, the size can be anything from 0 rows up to the square of size(R).


Assign to NEW by key in a Postgres trigger

First of all, there is no "trigger body" (unlike Oracle). In Postgres you have a trigger function (also, misleadingly, called "procedure") with a function body and 0-n triggers (without body) calling this function.
The special variable NEW in plpgsql trigger functions is neither a map nor an array; it's a record holding the new row:

NEW
Data type RECORD; variable holding the new database row for
INSERT/UPDATE operations in row-level triggers. This variable is
unassigned in statement-level triggers and for DELETE operations.

Assigning to a field (or column) of NEW is simple. The documented assignment operator is :=. (Since Postgres 9.4 also =.)
NEW.some_key := 5;

What you seem to be looking for is to parameterize the column name, which isn't quite as simple.
The additional module hstore provides the #= operator. (It's included in pretty much all standard distributions.) Install the module once per database with:
CREATE EXTENSION hstore;

Then you can:
NEW := NEW #= '"some_key"=>"5"'::hstore;

Sets the column some_key to '5' - if the column exists.

An explicit cast to hstore is optional. The operator #= coerces a string literal to the right data type automatically.
hstore only stores text strings, so a given literal for the value may have to be cast twice - a very minor drawback compared to alternative solutions.
The given string literal has to fit the data type of the column, or an exception is raised.
If no column with the given name exists, nothing is changed, no exception raised.

Related answer with details and an alternative solution:

How to set value of composite variable field using dynamic SQL

Code example
CREATE OR REPLACE FUNCTION trg_tbl_ins_bef()
  RETURNS trigger AS
$func$
BEGIN
   NEW := NEW #= '"some_key"=>"5"';
   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER ins_bef
BEFORE INSERT ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_tbl_ins_bef();

Postgres 11 or later allows to replace the misleding term PROCEDURE (the old syntax will keep working for the foreseeable future):
...
FOR EACH ROW EXECUTE FUNCTION trg_tbl_ins_bef();


SQL Services Wont Start After Removing TLS

You have to patch the sql server instance to a newer version to be able to use TLS 1.2

Take a look at TLS 1.2 support for Microsoft SQL Server

Either patch at least to SP2 CU10 (preferably a higher CU)
 or SP3 CU1.

When patching to SP3 CU1

You would still be 9 CU updates behind, the latest CU for SP3 at the time of writing is SP3 CU10.

So when going this route I would go for SP3 CU10 but preferably choose for SP4

  Note: TLS 1.2 support is also available in 2012 SP4.

When patching to SP4 (preferred)

In short, simply patching to SP4 would be the preferred option, you have to remember that there is a hotfix out for this Service Pack.

More questions and answers on disabling tls 1.0 and the sql server service not being able to start
here and here


You have to call SHOW CREATE USER 'username'@'localhost' and SHOW GRANTS FOR 'username'@'localhost' for all the users fetched by

SELECT user, host
  FROM mysql.user
 WHERE host = 'localhost'

and to replace all 'localhost' entries by '%' in the returned statements.


Set up AlwaysOn in virtual environment: How to add nodes to domain?

Finally after some reading more articles on Windows and AD Networking and speaking with abunch of people, this is what helped.

Settings on DC machine:

IP:                  192.168.1.10
Subnetmask:          255.255.255.0
Gateway: (none)

Preferred DNS: 127.0.0.1
Alternate DNS: (none)

On Each VM:

IP:                  192.168.1.11  (/12/13.../15 - on each node)
Subnetmask:          255.255.255.0
Gateway: (none)

Preferred DNS: 192.168.1.10
Alternate DNS: (none)

Restart the Network Adapter. BOOM !!

Now I can ping the DC and add them to the domain.

Why does it work now (but not before):

For DC: I made the DNS as loopback IP as preferred DNS (rather than its own IP - 192.168.1.10). This way it knows that the machine itself is the DNS.

For VMs: Gave them valid static IPs, rather than dynamic IP. Set the DNS to the DC (this was set correctly even before).

Glad I learnt something new,
_UB


import data from csv to mysql - good database structure

You will want to have two database tables for this.

One table with id int/bigint autoincremenmt, site, time and prod.
id is the primaty key.

And one table with id (same as in previous table but no autoincrement, it refers to the id in the first table)  the number of the EST, and EST . The primary key of this table would span two columns, id and number of the EST (this may require some fiddling with the database table creation tool you are using).

As for date columns, no mysql doesn't understand that format, it has its own date and datetime formats for columns. At our place here we are using unix timestamps and store these in the database as integers or bigints.


How do I replace this where clause with a join?

Your NOT EXISTS is more efficient in most cases.

LEFT JOIN internally matches all rows then filters to IS NULL. NOT EXISTS doesn't. This row multiplication also happens in all JOIN based code so you may need an extra aggregate (DISTINCT) to fix the output

NOT IN is generally wrong because NULLs cause no match.

You can also use EXCEPT which gives the same plan as NOT EXISTS.

SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID
FROM @Check CHK
EXCEPT
SELECT @ProjectId, RecordTypeCID, @NTID,1, GETDATE(), GETDATE(), @NTID
FROM Subscriptions
WHERE ProjectId=@ProjectId
and NTID=@NTID

FYI, as per the SQL-92 standard (page 191, Case 3a) the SELECT bit of the EXISTS is ignored.


First of all your replication is not synchronized. That means there can be always slight differences between master and slave. if you change configuration like below your master won't return commit until the replication processes.

  synchronous_standby_names = '*'

In your case, synchronized replication can be problematic because your slave is quite distant from the master so that the commits get slower.

Still with synchronized commit running a query 2 times in different transactions can get different results because of data changes.

I recommend you to read the blog; Evolution of Fault Tolerance in PostgreSQL: Synchronous Commit.


Why is this query with WHERE, ORDER BY and LIMIT so slow?

The problem with this statement is, that the query planner has no usable statistics for jsonb_array_lower(tags).

As seen in the first explain:

(cost=0.00..1395.00 rows=10000 width=0) (actual time=0.010..0.010 rows=2 loops=1)

The planner expects 10.000 rows to be returned if he uses the filter jsonb_array_lower(tags) ? lower('Qui') but there are just two rows returned.

The same happens in the last statement. Because of this missing information, the planner assumes, that a scan on the index tmp_posts_lists_idx_score_desc1532588309 would be more efficient.

You could try to avoid the need of lower and normalize the input during INSERT and UPDATE.

Another method would be:

WITH c AS (
    SELECT id FROM posts_lists
    WHERE jsonb_array_lower(tags) ? lower('Qui')
)
SELECT * FROM posts_lists l, c
WHERE l.id = c.id
ORDER BY score_rank(score, id) DESC
LIMIT 100;

This statement uses a CTE as optimization fence, but be careful, the performance may get worse for other WHERE conditions or table content.


Single User mode can't be turned off and no users are connected

If a user - or process-  is using multiple databases, sp_who2 can only report on one of the databases per spid.

Have you tried something like the following - it will rollback all transaction not completed:

ALTER DATABASE [mydb] set multi_user with rollback immediate


How to link my Azure VM running SQL server to my on-premise domain or Active Directory?

Yes you can.  You'll need to have a site to site VPN between Azure and your environment.

Once that's one you've got a couple of options.

You can change the DNS settings on the vNet to point to your office domain controllers.
Add domain controllers to Azure (with static IPs), then change the vNet to use the Azure DCs as for authentication.

Once one of those is done, reboot your VM and you should be able to add it to your domain. We do this for clients all the time, and we've done it with our
lab, and it works perfectly.

The product Azure Active Directory isn't needed to do this at all.


A T-SQL solution won't help here because SQL Server always uses . as the decimal separator and, as a server application, doesn't honor client regional settings.  One easy way to get the regional setting of the SQL Agent job step account is with a Powershell step with script like:

$numberDecimalSeparator = ((Get-Culture).NumberFormat.NumberDecimalSeparator)
Write-Output "NumberDecimalSeparator is '$numberDecimalSeparator'"

The fact you are having problems with decimal separators suggests the scheduled code is constructing dynamic SQL strings instead of using parameters.  Parameterized queries pass values in native form rather than strings, thus avoiding problems with regional settings that affect date formatting, decimal separators, etc.


Installing SQL Server 2008 R2 Express on Server 2012 - Could not load file or assembly

Its complaining about the userSettings section of the Microsoft.SqlServer.Configuration.LandingPage.Properties.Settings file

Run below command :

rd /s /q %localappdata%\Microsoft_Corporation

Refer connect item and here for more details on resolving it.


T-SQL manages the execution plan of non-inline table-valued functions separately from the code that calls the function. It treats it like a black box, which is why you see the UDF in the query plan instead of the UDF’s contents. This probably is related to the fact that non-inline table-valued functions can have multiple statements and a query plan is only capable of showing a single statement(?) (or at least it looks that way).

Mikael Eriksson commented:

  You have a multi statement table valued function and SQL Server can not look into that and show the execution plan for the function. The scan you see here is a scan of the rows returned by the function and since you have no where clause, a scan is the best thing to do.

...and linked to this pastebin showing how to see the cached query plan of the non-inline table-valued function.

If you have executed your function at least once (e.g., SELECT * FROM myUdf()), then it should be cached in SQL Server. To show that cached plan, take the name of your function minus any schema and plug it into the query below (quoted from Mikael's code):

select
  PlanCreated       = qs.creation_time,
  ObjectName        = object_name(st.objectid),
  QueryPlan         = cast(qp.query_plan as xml),
  QueryText         = substring(st.text, 1 + (qs.statement_start_offset / 2), 1 + ((isnull(nullif(qs.statement_end_offset, -1), datalength(st.text)) - qs.statement_start_offset) / 2))
from sys.dm_exec_query_stats as qs
  cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
  cross apply sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) as qp
where object_name(st.objectid) = 'FunctionName'

In the result set, the QueryText column should show a statement from the function and the QueryPlan should contain an XML query plan. In SSMS, if you click on a QueryPlan cell, it will automatically open up a new query plan viewer window. You will need to look at the source of your function and cross reference it to the statements and click through the query plans to search for performance issues.


How to create a row for every day in a date range using a stored procedure?

One option is a recursive CTE:

DECLARE @StartDate datetime = '2017-03-05'
       ,@EndDate   datetime = '2017-04-11'
;

WITH theDates AS
     (SELECT @StartDate as theDate
      UNION ALL
      SELECT DATEADD(day, 1, theDate)
        FROM theDates
       WHERE DATEADD(day, 1, theDate) <= @EndDate
     )
SELECT theDate, 1 as theValue
  FROM theDates
OPTION (MAXRECURSION 0)
;

(MAXRECURSION hint added thanks to Scott Hodgin's comment, below.)


How to speed up looped INSERT INTO statements?

I really don't think table hints or BULKINSERT are going to help you here - your approach is still to process each varbinary value one at a time, and this will be your downfall regardless - especially when you discard the idea of set-based queries because you "don't think it's possible."

Here's a set-based approach with no awful loops or cursors. This assumes that the pattern is always the same (LocationID is the first byte, and CityID is the next two).

DECLARE @x TABLE(x VARBINARY(32));

INSERT @x VALUES(0x010734),(0x030735040736),(0x030742050743060712);

;WITH n(n) AS
(
  SELECT TOP (300) (number*3)+1
  FROM [master].dbo.spt_values -- your own Numbers table is better
  WHERE [type] = N'P' ORDER BY number
)
-- INSERT dbo.LocationCities(LocationId, CityId)
SELECT
  x.x,      -- comment this out before insert
  LocationID = CONVERT(INT, SUBSTRING(x.x, n, 1)),
  CityID     = CONVERT(INT, SUBSTRING(x.x, n+1, 2))
FROM @x AS x INNER JOIN n ON LEN(x) > n.n;

Results:

x                        LocationID    CityID
---------------------    ----------    ------
0x010734                 1             1844
0x030735040736           3             1845
0x030735040736           4             1846
0x030742050743060712     3             1858
0x030742050743060712     5             1859
0x030742050743060712     6             1810

Some articles that will help you understand numbers tables and why generating sets in SQL Server is far superior to even the most efficient loop you can derive.

http://web.archive.org/web/20150411042510/http://web.archive.org/web/20150411042510/http://web.archive.org/web/20150411042510/http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html
Why are numbers tables "invaluable"?
http://sqlperformance.com/generate-a-set-1
http://sqlperformance.com/generate-a-set-2
http://sqlperformance.com/generate-a-set-3


How can I change the location for backup - Oracle 11g

That should be pretty simple. You should do this from rman. Also run a "show all;" to see how your backups are configured.

CONNECT TARGET /
SHOW ALL;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\rman_backup\%d_%Y_%M_%D_%t_%s_%c_%p';


Depending on how the application is setup, one (maybe the only?) way to pass data at the connection level would be to utilize the ApplicationName portion of the connection string.

You could have the application set this value based on the logged in user, making it accessible via APP_NAME() on the database side.

Example:

Server=myServer;User Id=myUsername;Password=Pass;ApplicationName="MyApp - User LowlyDBA"


How can I list all tables without a primary key?

To list all tables without a primary key, you can use this:

select tbl.table_schema,
       tbl.table_name
from information_schema.tables tbl
where table_type = 'BASE TABLE'
  and table_schema not in ('pg_catalog', 'information_schema')
  and not exists (select 1
                  from information_schema.key_column_usage kcu
                  where kcu.table_name = tbl.table_name
                    and kcu.table_schema = tbl.table_schema)

It's not clear to me if you want to find tables that are not referencing any other tables or tables that are not referenced by other tables. But both things can be obtained by querying information_schema.referential_constraints in a similar way as the above query.


DBMS_REDEFINITION vs EXCHANGE PARTITION in oracle

in both ways you will end up using exchange partition and probably a series of split partitions. If your table does not have many dependencies like constraints and triggers, the route using redefinition can be easier because it buys you a lot of time. You prepare the new table as a copy and exchange the object id's on the end phase. If there are a lot of dependencies, you have to recreate them using different names.
The operation will take some time, depending on the size.
If you go for safe .... in both cases, the operations fail or succeed.
How is the load on the table? Does it receive a lot of updates/inserts/deletes during the migration? Do you have downtime available? I would go for simplicity if possible using downtime and not using redefinition.


To speed up query I would recommend indexes on the columns you join in:

records.id, records.type, records.label (combined index)
records_labels.id
records_types.id
records_serial.record

Depending on the data in records_serial.serial and records_barcode.* (which column do you want to query here?) it may be also useful to add indexes here.

That index suggestion is optimized for this single query. You should also consider the disadvantages for other queries.


What Happens when Cartesian Product is applied to Relations with same attribute name

By way of example, using SQL Server, the simplest answer is an INNER JOIN:

USE tempdb;
GO
CREATE TABLE Names1
(
    FirstName varchar(50)
);
CREATE TABLE Names2
(
    LastName varchar(50)
);
INSERT INTO Names1 (FirstName) VALUES ('Max');
INSERT INTO Names1 (FirstName) VALUES ('Vernon');
INSERT INTO Names2 (LastName) VALUES ('Max');
INSERT INTO Names2 (LastName) VALUES ('Vernon');

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    INNER JOIN dbo.Names2 N2 ON N1.FirstName = N2.LastName;

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2;

The results:

However, if you are prepared to complicate your query unnecessarily, you could do the following:

SELECT n1.FirstName, n2.LastName
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2
WHERE n1.FirstName = n2.LastName;

Results:

In your question, you ask if using the same field names is going to be an issue; plainly it is not:

USE tempdb;
GO

CREATE TABLE Names1
(
    [Name] varchar(50)
);
CREATE TABLE Names2
(
    [Name] varchar(50)
);
INSERT INTO Names1 ([Name]) VALUES ('Max');
INSERT INTO Names1 ([Name]) VALUES ('Vernon');
INSERT INTO Names2 ([Name]) VALUES ('Max');
INSERT INTO Names2 ([Name]) VALUES ('Vernon');

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    INNER JOIN dbo.Names2 N2 ON N1.[Name] = N2.[Name];

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2;

SELECT n1.[Name], n2.[Name]
FROM dbo.Names1 N1
    CROSS JOIN dbo.Names2 N2
WHERE n1.[Name] = n2.[Name];

Results:


Are there RDBMS which can count rows fast?

Most RDBMS mostly use BTrees for indexes. These hold no summary information internally about the fan-out or total number of descendents. So the only way to get to a desired offset within the data is to start at the beginning a step through.

If you were able to explicitly define a sequencing column within your table the RDBMS may be able to use an index on it to more quickly get to your offset (no guarantees; which indexes the optimiser chooses to use is a complex topic). The query would then become

SELECT * FROM table
WHERE some_col = something
AND sequence_col > 10000        -- may get an index seek because of this, maybe not
ORDER BY sequence_col LIMIT 10  -- ordering by the new column

There are many risks associated with this, including

maintaining separate sequence columns for each desired sort order
concurrent changes in the data while a read is in progress
holes appearing in the sequence

Usually you are much better off simply indexing the existing business-required columns in an appropriate way and implementing pagination. @Vladimir has given a link for this.


Taking a backup of a streaming WAL slave

Chained replication is supported on PostgreSQL 9.2.

9.1 does not support chained replication. You will need to either take the base backup from the master, or:

Stop the replica
Copy the replica manually
Start the replica back up


SQL Server Select line where jobCategoryKey EXISTS once for one candidateKey

All the proposed solutions using COUNT may prove to be inefficient as the aggregates will most likely need to be computed for the entire set, unless the optimizer will be able to push the predicate down. There is a more natural solution which IMHO is much clearer, and typically more efficient. It involves wording the question in negative form:

  "Show me all candidates for category 1245, who have no other
  association with any other category".

This translates to SQL immediately:

SELECT *
FROM Candidates_to_Jobs AS C1
WHERE jobCategoryKey = 1245
      AND
      NOT EXISTS (
                  SELECT NULL
                  FROM Candidates_to_Jobs AS C2
                  WHERE C1.CandidateKey = C2.CandidateKey
                        AND C2.jobCategoryKey <> 1245
                 );


"Recover" data from running PostgreSQL instance; data directory was deleted

I am afraid your chances are somewhere between infinitesimally small and zero.

There are a lot of files in that directory and it's sub-directories. Any which were actually open by one of the postgresql backend processes at the time you deleted the directory, and have not been closed since, are still present in the filesystem, but any that were not are long gone. So the best you would be able to hope for is to recover some of the files, and the database is unlikely to be able to start up properly with missing files.

Assuming a Linux system, you can see which files are actually open (and therefore in theory at least still recoverable) using this command:

lsof -u postgres | grep /var/lib/pgsql

You will see entries such as:

postmaste 2418 postgres 5u REG  253,0 8192 274804 /var/lib/pgsql/9.2/data/global/5445770

The second column is the PID of the process, and the numeric part of the 4th column is the FD (file descriptor) number. The file can be recovered using a command like:

cp /proc/{PID}/fd/{FD} {dest_file}

replacing the {PID}, {FD} and {dest_file} as appropriate.

However, as I said, your chances of recovering ALL the lost files, or even enough of them to be able to start the database up again afterwards, are very very slim. Even the ones you do recover may be corrupted, as the other process which has them open will be writing to them at the same time.

Instead, it might be worth trying pg_dump. It is likely to fail to dump the whole database, but if you are lucky you might recover some of it that way. If there are some tables which are more important to you than others, and it appears that the running database is still able to access them, then you might try using pg_dump to dump only those tables.

It is possible (but I would say quite unlikely) that you could restore the underlying files using system file recovery tools.


Bi-directional replication for the same MySQL table

I'm assuming these are different instances of MySQL (most likely on different servers).

You can set up Master-master replication, where dbA is a master of dbB and vice versa.

Then, to avoid auto-increment collisions, set the following in my.cnf on dbA:

auto_increment_increment=2
auto_increment_offset=1

And the following on dbB:

auto_increment_increment=2
auto_increment_offset=2


Just in case someone else comes across this needing help... the Couchbase UI is both command line and web-based, the latter of which is typically running on port 8091.

So, for example, if you've installed it on your computer, it would be http://localhost:8091/

If Couchbase is installed elsewhere, say IP 192.168.1.101, it would be http://192.168.1.101:8091

This is covered in the Couchbase 3.x post-install tasks as well as the post-install tasks for Couchbase 4.x.


Greenplum DB changing table column length

It's not even about the type or subtype. Changing a column on a table requires you to drop and recreate all dependencies that reference it. You can do this in a single transaction.

CREATE TABLE foo ( a varchar(10) );
CREATE VIEW bar AS TABLE foo;

BEGIN;
  DROP VIEW bar;
  ALTER TABLE foo
    ALTER COLUMN a
    SET DATA TYPE varchar(100);
  CREATE VIEW bar AS TABLE foo;
COMMIT;


My environment (18c XE docker):
sqlplus "sys/oracle@localhost:1521/XE as sysdba"
sys@XE> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO
         4 XEPDB2                         MOUNTED
sys@XE>

You will find the corresponding SERVICE_NAME for PDBS by:
sys@XE> select service_id,name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME                                     PDB                      CON_ID
---------- ---------------------------------------- -------------------- ----------
         1 SYS$BACKGROUND                           CDB$ROOT                      1
         4 xe                                       CDB$ROOT                      1
         3 xeXDB                                    CDB$ROOT                      1
         2 SYS$USERS                                CDB$ROOT                      1
         8 XEPDB1                                   XEPDB1                        3

5 rows selected.

You may define the SERVICE_NAME the way you want for your PDB. It is a good practice to name your database service. You can also configure your database to expose multiple SERVICE_NAMEs for the same database.
For example at a TV station you have multiple channels. Their content is different so they go into separate schemas (CNN_NATIONAL, CNN_INTERNATIONAL).
sqlplus user/pass@something/CNN_NATIONAL
sqlplus user/pass@something/CNN_INTERNATIONAL

The point is you only need ONE Oracle Database to serve multiple applications separated by schemas. Now that we have containers we can have even better isolation/separation.
sys@XE> @g XEPDB1
sys:XEPDB1@XE> exec dbms_service.CREATE_SERVICE('cnn_int_prod','cnn-int-prod.cnn.com');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05
sys:XEPDB1@XE> exec dbms_service.start_service('cnn_int_prod');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

sys:XEPDB1@XE> select service_id,name,network_name,pdb,con_id from cdb_services order by con_id;

SERVICE_ID NAME                           NETWORK_NAME                   PDB                      CON_ID
---------- ------------------------------ ------------------------------ -------------------- ----------
         8 XEPDB1                         XEPDB1                         XEPDB1                        3
         1 SERVEMEWELL                    pdbprod1.us.oracle.com         XEPDB1                        3
         2 cnn_int_prod                   cnn-int-prod.cnn.com           XEPDB1                        3

3 rows selected.

sqlplus system/oracle@localhost:1521/cnn-int-prod.cnn.com

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 11 15:21:12 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Last Successful login time: Tue Aug 11 2020 14:58:37 +02:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

system@XEPDB1>

Best of luck!


SQL Server Services empty in SQL Server configuration

I did the upgrade on my laptop with SQL Server 2008 R2 and SQL Server 2012 already installed. The issue I had was that SSCM actually disappeared from the Start Menu, if after repair it would not put them back. I ended up finding them under the Windows root directory, but have since done a complete reinstall of Windows 10 on my laptop, and reinstalled SQL Server.

I would expect you are looking at a similar situation that is going to require a reload of Windows 10. You might try just doing the refresh, where it does not completely remove everything...but I doubt that is going to work. I did a factory reset where I went back to Windows 8.1, then forced an upgrade to Windows 10. Which I might add, doing that seemed to have improved Windows 10 at least on my laptop (Lenovo Y 40-80) compared to running it after the upgrade.

One last chance you might attempt is to install the SSMS preview (standalone of SSMS) from here.


Import MySQL table from a textfile to a remote host with compression

Use the --compress flag.

mysql --compress -u username ...

  --compress, -C

  Compress all information sent between the client and the server if both support compression.

  http://dev.mysql.com/doc/refman/5.6/en/mysql-command-options.html#option_mysql_compress

Virtually any MySQL client and server will support compression.


How to increase performance of UPDATE of ~ 50.000.000 entries

One more thing. If these tables are very larges even if you have good indexes, you should updates these tables rows in cycle, because the transaction log is growing every time and big transactions takes more times.

Example:

CREATE TABLE UpdateTable(id int IDENTITY(1,1), name varchar(200), namenew varchar(200))

INSERT INTO UpdateTable VALUES ('Old1','New2');
INSERT INTO UpdateTable VALUES ('Old2','New2');
go

SET NOCOUNT ON;
DECLARE @cnt INT = -1;

WHILE (@cnt<> 0)
BEGIN
    UPDATE TOP (1) UpdateTable
    SET NAME = namenew
    WHERE name <> namenew

    SET @cnt = @@ROWCOUNT;
 END

You should change the top (1) to an acceptable value like 100000 or bigger you can measure it on your environment


Oracle Logical IO / Consistent Reads inconsistent?

My guess would be that the first query doing the extra work of delayed block cleanout because some other session at some earlier point in time modified (or inserted) the data without being able to do a commit cleanout.  That causes Oracle to do a bit of extra work the next time the dirty block is read to update its status.  Here is a good example of the impact of delayed block cleanouts on consistent gets.  I'm not 100% certain that delayed block cleanouts are reflected in LAST_CR_BUFFER_GETS from DBMS_XPLAN but I would certainly expect that they are.