How to use COALESCE with DISTINCT with multiple rows and without preceding comma With reference to How to use COALESCE with multiple rows and without preceding comma? ...

I have a similar scenario but with a small difference. I would be grateful if any expert could help me with it.

Data

area | city | state
1 | a | a
2 | a | a
3 | b | a
4 | b | a
1 | a | b
2 | a | b
3 | c | b
4 | c | b

Desired Results

Now I would like to retrieve the results as:

area | city | state
1,2,3,4 | a,b | a
1,2,3,4 | a,c | b

Retrieved Results

I tried myself with the above mentioned solution, but I am receiving the following result set:

area | city | state
1,2,3,4 | a,a,b,b | a
1,2,3,4 | a,a,c,c | b

I need city to be distinct.
I hope I have explained good enough?

Not sure what code you're using now or what version of SQL Server you're using, but FOR XML PATH goes pretty far back. Here's how I would do it:

DECLARE @t TABLE(area int, city char(1), state char(1));

INSERT @t(area,city,state) VALUES
(1,'a','a'),(2,'a','a'),(3,'b','a'),(4,'b','a'),
(1,'a','b'),(2,'a','b'),(3,'c','b'),(4,'c','b');

;WITH x AS
(
  SELECT
    area = STUFF((SELECT ',' + CONVERT(varchar(11),area)
      FROM @t WHERE state = t.state GROUP BY area
      FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)'),1,1,''),
    city = STUFF((SELECT ',' + city
      FROM @t WHERE state = t.state GROUP BY city
      FOR XML PATH(''),TYPE).value(N'.[1]','nvarchar(max)'),1,1,''),
    state
  FROM @t AS t
)
SELECT area,city,state
  FROM x
  GROUP BY area,city,state
  ORDER BY area,city,state;

There is certainly a way to do this with STRING_AGG() in more modern versions (2016 SP1+), but it won't be much less messy.

;WITH x AS
(
  SELECT city,state FROM @t GROUP BY state,city
),
y AS
(
  SELECT x.state, city = STRING_AGG(x.city,',')
  FROM x GROUP BY state
)
SELECT area = STRING_AGG(t.area, ','), y.city, y.state
  FROM y INNER JOIN @t AS t ON t.state = y.state
  GROUP BY y.city, y.state
  ORDER BY area, y.city, y.state;


How to skip table data for replication in MySQL? I would like to create a table on the master, whose data are not replicated to the slave.

I'm temporarily inserting many millions of rows in this table a part of a batch job; the data in this table can be re-created on demand, hence do not need to be replicated on the (slower) slave.

I don't want to use TEMPORARY tables, as I ideally want to keep the table structure in the database (on the slave as well, so that I can reliably use it for backup recovery), and above all I want to be able to recover from the current state if the job exits for any reason, so the table data need to persist on the master if the job dies.

Ideally, I would like to be able to control the replicability of the table from the CREATE statement, to be able to control this from my normal SQL workflow.

Is this possible? If not, close alternatives are welcome.

Create a database.  Add binlog_ignore_database on the master to avoid even sending the data out (as opposed to sending it, then tossing on the slave, as with replicate_ignore_*)

Put all your not-to-be-replicated tables in that database.  Reference them via dbname.tblname.

Be aware of how replication filters:  In SBR, USE controls what to check for do/ignore filters.

There's a trick I did quite successfully:

Setup: RBR, extra db, binlog_ignore_db (as above)
Create tables in that db and load data into them
Processed those tables (only on the Master) -- normalization, cleansing, updating Summary Tables, etc
INSERT stuff from that db into replicated tables (in other dbs). -- This is the only step that was replicated.
And drop the tables in the ignored db.

The net effect was to do a lot of ETL stuff only on the Master, yet still get the end results onto the Slave.


Parent/Child summary rollup I'm struggling to wrap my head around this one. I'm sure someone will simplify it.
We have a parent/child relationship that I'm needing to roll-up based on sold quantities and sales dollar amounts.
We have a table that stores the parent and their children. Looks like this:
Parent    Component
1000      1300
1000      1301

When an order is placed, our users order a part number of 1000, then add in line items 1300, 1301 as options. Parent parts do not have a sales value assigned to them, but components do have a value. So what I'm trying to do is roll up everything to the parent level (sales and quantities) on a sales order.
Here's how a sales order would look:
SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      0
0001          1300         C              1      500
0001          1301         C              1      350
0001          1301                        1      400

As you can see above, we can have components (StockCode 1301) in here without parents (think of these as replacement parts). If the sales order line does not have a ParentChild value, they need to be excluded from the roll-up.
Here's how I'm wanting this data to look. A parent/child should only have a Qty of the parent.
SalesOrder    StockCode    ParentChild    Qty    Price
0001          1000         P              1      850
0001          1301                        1      400

I'm guessing that STUFF/FOR XML PATH would be a good starting point?
Here is the test data:
CREATE TABLE #Structure (
  Parent INT
 ,Component INT )

 INSERT INTO #Structure (Parent, Component)
 SELECT 1000, 1300
 UNION
 SELECT 1000, 1301

 CREATE TABLE #SalesOrder (
   SalesOrder VARCHAR(4)
  ,StockCode INT
  ,ParentChild CHAR(1)
  ,Qty INT
  ,Price INT )

 INSERT INTO #SalesOrder (SalesOrder, StockCode, ParentChild, Qty, Price)
 SELECT '0001', 1000, 'P', 1, 0
 UNION
 SELECT '0001', 1300, 'C', 1, 500
 UNION
 SELECT '0001', 1301, 'C', 1, 350
 UNION
 SELECT '0001', 1301, NULL, 1, 400

 SELECT * FROM #Structure

 SELECT * FROM #SalesOrder

 DROP TABLE #SalesOrder
 DROP TABLE #Structure

TLDR: Final query with comments is at the end, but the following answer goes through the steps of building it out.
So what you likely want is a recursive CTE (I'm not sure STUFF can help you with this, at least not easily).
You should have an Item table that is a distinct list of all your assemblies and component item IDs, similar to this example:

If I recall correctly from your last question, you have a BOM table that should have a similar structure as this example (with other fields too I'm sure):

A recursive CTE will help you flatten this so all child Components at all levels roll up with the main end item (e.g. Component 2003 rolls up with 2001 into the end item parent 1000).
Here's an example of a recursive CTE:
WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, ParentItemId, ComponentItemId
FROM CTE

In my example dataset, this produces the following output (every level of every component rolled back up to the original end item):

Notice end item "1000" on rows 16 through 18. Rows 16 and 17 are "level 1" and row 18 is "level 2" because the component "2004" on row 17 has a component below it, therefor it's the parent on row 18 to component "2005".
Now let's bring in the Component Price column so we can later group by End Item and sum up the total cost like so:
WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, ParentItemId, ComponentItemId, ComponentPrice
FROM CTE

Finally, if you add a GROUP BY on the End Item column and add a SUM(ComponentPrice) column then you can get the total exploded BOM price of the End Item like so:
WITH CTE AS
(
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId
)

SELECT EndItemId, SUM(ComponentPrice) AS EndItemTotalPrice
FROM CTE
GROUP BY EndItemId

End Items' Total Prices:

TLDR:
Your final step would be to join these rolled up End Item prices to your Sales Line table by ItemId to EndItemId like so:
WITH CTE AS -- This is our recursive CTE
(
    SELECT I.ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice -- These are the fields you'll need for recursion and final grouping later on
    FROM #Item AS I -- Items is your starting point in the recursion (i.e. this defines your base case)
    INNER JOIN #BOM AS B -- Join to the BOMs table to get the child Components
        ON I.ItemId = B.ParentItemId -- Gets all BOM lines with this parent Item

    UNION ALL -- This is how we start to call the CTE recursively

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice -- Must select the same fields as before for the UNION ALL
    FROM CTE AS C -- This is our recursive case, a CTE referencing itself
    INNER JOIN #BOM AS B -- Joining to the BOM table again to get all child components but notice the difference in join clause
        ON C.ComponentItemId = B.ParentItemId -- This time the previous call's child becomes the parent (i.e. we're joining the BOM table back in where the child component item is the parent item in the BOM table)
),
RolledUpCte AS -- This rolls up the results of the previous recursive CTE by our end assembly item
(
    SELECT EndItemId, SUM(ComponentPrice) AS EndItemTotalPrice -- Summing up the price of all the components on the end item
    FROM CTE
    GROUP BY EndItemId -- Group by end item
)

SELECT SL.SalesLineId, SL.ItemId, RUC.EndItemTotalPrice -- Final select of the fields we need from our Sales Line table and our rolled up prices of our end items
FROM RolledUpCte AS RUC
INNER JOIN #SalesLine AS SL
    ON RUC.EndItemId = SL.ItemId

Sales Lines w/ their summed up component prices:

Your table schema will obviously vary from my example, but this is the concept and example code of what you'd need to do. Instead of ItemIds it looks like you have StockCodes (or whatever your item's identifier is called in your Item and BOM tables).
The only other important thing to be aware of is if you have a BOM that is more than 2 levels deep (e.g. Parent -> Child -> Grandchild -> Great Grandchild) then you'll only want to sum up the lowest level component items otherwise you end up double counting. E.g. if Item A has component B, and component B has components C and D on it, then you want to only sum the prices of C and D for A. If you sum it all then you'll end up double counting by adding B + C + D for A's price. In my past experience we had a column called ItemType that identified which items are the lowest level component items so that when we did the roll up we could add a WHERE ItemType = 'LowestLevelComponent' but it sounds like in your case, all items that have a child don't have a price so then you probably don't have to worry about double counting.
Finding the total quantity of the lowest level components is the same idea too, but instead you'll need a column in the recursive CTE for the ParentQuantity and another one that multiplies the ParentQuantity by the Child's quantity (in the recursive part of the query) like so:
    SELECT ItemId AS EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice, B.ComponentQuantity, B.ComponentQuantity AS ComponentQuantityMultiplied
    FROM #Item AS I
    INNER JOIN #BOM AS B
        ON I.ItemId = B.ParentItemId

    UNION ALL

    SELECT C.EndItemId, B.ParentItemId, B.ComponentItemId, B.ComponentPrice, B.ComponentQuantity, (B.ComponentQuantity * C.ComponentQuantityMultiplied) AS ComponentQuantityMultiplied
    FROM CTE AS C
    INNER JOIN #BOM AS B
        ON C.ComponentItemId = B.ParentItemId

If you have any questions feel free to let me know and I'll explain as best as I can. If this feels like information overload, maybe just start with the TLDR final full query above and just read the comments to try to understand what it does.


Check the result of a math expression in mysql Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here.
a  b  oper  c
-------------
2  3  +     5
4  2  /     3
6  1  *     9
8  5  -     3

As, we can see in some cases, a <oper> b != c. So, my question is how to filter out such cases?
I've heard of execute, which is used for executing statements, but I don't know how to use it inside where clause.
Also, I'm not generalizing the oper to any arithmetic operation, but it would be nice to know, if any function exists.

Because SQL differentiates code and data reasonably well in the syntax function like the following is required:
CREATE OR REPLACE FUNCTION test(a int, op char(1), b int)
   RETURNS INT DETERMINISTIC RETURN
CASE op
WHEN '+' THEN a + b
WHEN '*' THEN a * b
WHEN '/' THEN a / b
WHEN '-' THEN a - b
END

once you have that you can use a query:
select a, op, b, c
from maths
where test(a,op,b) = c

ref: fiddle


pg_hba.conf from PostgreSQL I have no idea how to configure that pg_hba.conf file, to connect with my server via pgAdmin. Right now i was trying to add:

host all all host.name.org.pl trust

host all all host.name.org.pl md5

host user_name database_name ip_adress trust

host all all ip_adress trust/md5

and others..

I've got a postgresql server. My provider says that i must connect by adress: sql.user_name.org.pl and 5432 port. There is no ssl on the server. I've got user and password. Can somebody please help me, to deal with this?

If you have to connect via TCP/IP using IPv4, i.e., the most common situation, you should check what is your IP address. Ince you have it, add this line to pg_hba.conf file on the server. This line should be added after all lines that start with local and before other lines that start with host:

host databasename username ipaddress/32 md5

if youo don't know your ip address, or if it is variable (for a dialup line), then use 0.0.0.0/0 instead of ipaddress/32. This way you will allow connection from any IP address.

But, if your provider is responsible for postgresql, then he should make thee changes. Are you sure you need to configure that file on the server?

If you are only responsible for your client machine, and you want to connect via pgAdminIII, then just use the hostname portnumber username and password provided by your provider. If you cannot connect using these settings, please come back here and write what is the exact error you get from pgAdminIII.


OLEDB Wait Type and Functions Is there any relationship between the OLEDB Wait Type and functions? Trying to troubleshoot why I would have 77% of my waits as OLEDB wait type when we have no linked servers. I have a vendor database with a high number of both Table-valued functions and Scalar-valued functions and was curious if they could at all be related to the OLEDB wait type.

Most of the documentation I have found points to Linked Server queries for this wait type, but that's not the case for us.

OLEDB waits cover a variety of states, including (but not limited too) client-side profiler traces, some DBCC commands, materialisation of DMVs, possibly some full-text functions in 2005 (IIRC, most have been split out to FT specific waits in 2008) and also linked server queries as you mentioned.

I'm not aware of UDF functions inherently resulting in OLEDB waits, unless they are performing one of the actions I mentioned above.

Rather than looking at the raw percentage wait time attributed to OLEDB, have you checked the average wait time per wait? It may prove to be so low as to be insignificant.


Re-map DB Users/Logins after Restore WITH STANDBY This is related to my previous post..

Differential Backup Issue - Why? Is this Possible?

Basically, the issue which I am having now is that in my restore process (as part of ETL), there are tasks to re-map db users to logins (because they are SQL Auth users in server A so lose mapping when restored on server B). Since restore with STANDBY will not allow this as it will be read-only db.. how can I get around this..?

Your users from db on server A will be moved to the database on server B with the backup. Then all you need is to recreate the logins with the same sid. You do this using WITH SID option:

CREATE LOGIN My_login WITH SID = 0x14585E90117152449347750164BA00A7

the sid obviously is your user sid that you can find in sys.database_principals:

select name, sid
from sys.database_principals
where type_desc = 'SQL_USER';


Pull valid row from table even when joining table has no valid data I have two tables with a 0 to many relationship. Table A holds the one record where Table B can hold 0 to many records. There is a status field in both tables which can be equal to 'U' or 'D'.

I want to be able to pull the one record from TableA where status <> 'D' even where there are no related records in TableB or when all of TableB's related records are equal to 'D'.

In the below code TableA has a good record and TableB has two related records but their Status = 'D'. So, I still want to pull the data from TableA and blank\null fields from TableB:

SELECT A.Field1,
       A.Field2,
       A.KeyField1,
       B.Field1,
       B.Field2,
FROM TableA A
LEFT OUTER JOIN TableB B ON A.KeyField1 = B.KeyField1
AND A.KeyField2 = B.KeyField2
WHERE A.KeyField = '11111'
AND A.Status <> 'D'
AND B.Status <> 'D'
ORDER BY A.KeyField

When I Select from these tables individually such as:

Select *
From TableA
WHERE KeyField = '11111'

Select *
From TableB
WHERE KeyField = '11111'

I get the results I expect, TableA's Status field = 'U' and TableB's Status field = 'D'.  I know the data is there and is equal to what it should be so, I know there's something wrong with my query.

Try rearranging your conditions like so:

...
LEFT OUTER JOIN TableB B ON A.KeyField1 = B.KeyField1
AND A.KeyField2 = B.KeyField2
AND B.Status <> 'D'
WHERE A.KeyField = '11111'
AND A.Status <> 'D'
...

When you put an outer table in the WHERE clause, you force SQL Server to filter only to rows that exist and where that value can be explicitly evaluated, in other words you change your outer join to an inner join.


Normalize SQL to the same format I want to write a package that will manage mysql/pgsql views, but for that I need to check if the view defined in the code is the same as the view in the database which I get with show create table command.

The problem is that database parse query and might slightly change it with adding aliases, etc. Is there a parser that could normalize views to the same format so I could compare them like strings?

Right now I use approach with creating tmp view, getting the definition and dropping the view. It works, but looks not good.

Apache Foundation maintains a SQL parser called Calcite. It can comprehend several dialects including MySQL and Postgres. You could use it to translate a view from both sources to their tokenised parse trees, then compare the trees for interesting differences. Even easier would be to convert the trees back to SQL in  common dialect and perform a string compare on the two outputs.


One NUMA node uses all memory I am running a SQL 2012 SP3 CU9 Standard server with 2 CPU's each having 4 cores (hyperthreading enabled). I have 384 GB RAM (Yes I know that I cannot use it all in SQL standard). Lock pages in memory is enabled.

My problem is that one of the CPU nodes seems to allocate all memory leaving just a little for the secound CPU node, causing one of the nodes to have a very low PLE and the other one a high PLE.

Did I configure something wrong?

When it comes to CPU core usage, Standard Edition starts with the first NUMA node. Whenever it exhausts the available licensing (2016 & newer, it's 4 sockets or 24 cores, whichever comes first - 2014 & prior, it's 4 sockets or 16 cores, whichever comes first), then it doesn't use the remaining cores.

Here's the tricky part: if it exhausts licensing, and whole NUMA nodes remain with zero cores activated, then that entire NUMA node is offline - unusable for CPU work or memory. I've blogged about that in more detail here.

You asked about memory, and I have a hunch (although I don't have a server handy to prove it) that it might work the same way. If the first NUMA node contains 128GB or more of memory, then that might be the vast majority of memory in use.

It's a very rare edge case, and I wouldn't expect to see any guidance from Microsoft to confirm or deny this. (It's certainly not something they'd test as part of mainstream delivery - if you've got 384GB RAM, you'd wanna use Enterprise Edition instead.)

I'm stretching to think about how you might disable this behavior, other than playing around with soft NUMA.


Table design for departments with duplicate department numbers How should I define a database table for my company's departments where we have a set of duplicate department numbers used by an upper management tier.

  dept_id   dept_name   manager_id  director_id   vp_id   description     mission_statement  over_view    web_url
      1       name1      10            20          30       ...
      2       name2      11            21          30
      3       name3      12            22          31
      4       name4      13            23          32
      4       name5      13            24          32
      4       name6      13            25          32

The column which makes the row unique is the director_id.  But I really only want to use the dept_id as the pk because I'm using the department data for a web application. I have the Employee table with a column named dept_id.  When I display an employee on a webpage, I dynamically create a link to the department's splash page containing the above information.  But if I list director 24 I need URL parameters that take me to the department named name5

blahblah.com/view_dept.jsp?dept_id=4

I know the obvious is to composite key with director_id  and have the URL be

blahblah.com/view_dept.jsp?dept_id=4&director_id=24

but when I list a regular employee I don't have the director_id. I suppose I could get it in the query, but it may not be as straight forward as it would be if HR just used unique ID's for all departments. Getting HR to do a reorg is probably out of the question, but it would be nice to know that that is the most recommended answer.

I'd like to hear suggestions and best alternatives to the reorg

The PK of your department table is not dept_id.  The column dept_id is an attribute of a manager table that you have not defined, since it is functionally dependent on the manager_id.

The PK of your department table is dept_name.

Primary keys don't have to be integers.

Note: If your HR department creates departments with the same name, that is going to land you back in hot water.  You might need to create a composite primary key of dept_id and dept_name.  This could still lead to trouble if your HR department can't be trained to keep department names unique within managers.  Excel is not a good data source for being able to enforce constraints.


"Table doesn't exist in engine" after moving tables from one VPS to another I have two VPS's running right now. One, which I will refer to as the "old" server, runs on Debian 7 and gives this output for mysql --version:

mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.2

The other, which I will refer to as the "new" server, is running on a brand new installation of Debian 8, and gives this output:

mysql Ver 15.1 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I am trying to move my databases & tables from the old server to the new server.  I thought that I could do this by simply copying the /var/lib/mysql/ directory from the old server and overwriting the same directory on the new server. However after doing this, I now receive this error in phpMyAdmin on the new server when I click on any of the tables or databases:

#1932 - Table 'phpmyadmin.pma__tracking' doesn't exist in engine

However I can clearly see that it exists:

Is there a solution to this? I'm not sure what I did wrong. Thanks!

You can copy files from one server to the next to move your database. To do this, you must make sure that both instances of your DB are stopped. I also recommend using rsync to copy your database files. In addition to making sure that both database servers are stopped before moving the files, you will most likely need to change the ownership of your files. sudo chown --recursive mysql:mysql /var/lib/mysql before starting your new instance.

There are other options available to move your database. If your database isn't terribly large, mysqldump is probably the most simple. I have attached the documentation for mysqldump below as provided by MariaDB, as you are using MariaDB.

https://mariadb.com/kb/en/mariadb/mysqldump/

Another option to consider, a bit more complex but a little more flexible for size, would be Percona's xtrabackup.

https://www.percona.com/software/mysql-database/percona-xtrabackup


Mysql Procedure error while executing it I am creating procedure which selects member_id in courser.member_id having more that 100000 records. i had declared one variable i which stores one member id at time update it with next query.

can anyone resolve this..? procedure is as follows:-

    DELIMITER //
DROP PROCEDURE IF EXISTS Mock_test_1;
CREATE PROCEDURE Mock_test_1()
   BEGIN
DECLARE i int unsigned;
DECLARE curs1 CURSOR FOR select member_id from client_master;
open curs1;
read_loop: LOOP
      FETCH curs1 INTO i;
update client_master set
         `mobileno`='0000001231',
         `email`=concat('MOCK',member_id,'@mock.com'), `password`=md5('MOCK'),
         `personaladdress` = concat('MOCK',member_id),
         businessaddress = concat('MOCK',member_id),
         imeino = '00000000000000', panno= 'AAAAA66666' ,aadharno = '11111111111111';
insert into client_master_test select * from client_master;
   END //
 DELIMITER ;

can anyone suggest me blocker for this..?

You are fetching into i, but not using it.
There is no end loop.
The insert is copying the whole table over repeatedly?

Consider using a single multi-table UPDATE, not a Stored Procedure with a loop.


How to JOIN each row ON NOT IN ( SELECT ... )? Say I have a table of users, songs, and songs played.

users
id: int

songs
id: int

songs_played
user_id: int
song_id: int

If I want to, for each user, find a song that has not yet been played. How do I do that in one query?

It's pretty straightforward for me to just do something like:

user_ids = SELECT id FROM users;
for user_id in user_ids:
    SELECT *
    FROM songs AS s
    WHERE s.id NOT IN (
        SELECT p.song_id
        FROM songs_played
        WHERE id = user_id
    )
    ORDER BY RANDOM()
    LIMIT 1;

But that's pretty inefficient.

Thanks!

You can use not exists clause to do this.

SELECT
    u. ID AS user_id,
    s. ID AS song_id
FROM
    songs s,
    users u
WHERE
    NOT EXISTS (
        SELECT
            1
        FROM
            user_songs us
        WHERE
            us.user_id = u. ID
        AND us.song_id = s. ID
    )
ORDER BY
    1,
    2


Restarting One Database Not Entire Server Is there anyway to restart one database in SQL Server instead of restarting the entire server?  I have a task in perpetual rollback on one of my databases.  I'd like to restart it without turning off / on the other DBs

You can try taking it offline, and then online again:

use master
go

alter database YourDatabase
set offline
with rollback immediate
go

alter database YourDatabase
set online
go

As per BOL:

  OFFLINE : The database is closed, shut down cleanly, and marked offline. The database cannot be modified while it is offline.


Issue working with Postgres on Windows I am running PostgreSQL 9.3 on my machine. I have PhpPgAdmin running on a cloud, using which I exported the database I am supposed to be working on, selecting "Structure and Data" and "Download". There is an sql file generated. On my machine however, I open my PgAdmin3 client , initialize my databases and run the following import command

psql -U postgres -d postgres -f dump.sql

Nothing happens. I also tried

psql postgres < /path/to/dump.sql

But again nothing happens. I tried on both Windows as well as Linux Mint because I thought it was something wrong with my installation on Windows.
I am new to PostgreSQL and the equivalent for it in MySQL using PhpMyAdmin was simple. Am I missing any security things? I think not because just so that nothing gets confused, I saved all my names as postgres itself.

I have researched everywhere but nothing so far. Any help would be appreciated. Thanks!

The PSQL Console in pgadmin3 consists of opening a terminal and running psql --username yourname [other options] dbname inside it for you, with yourname and dbname being taken from what you've used to log in pgadmin3.

This console runs the psql interpreter which expects either psql meta-commands starting with backslash, or plain SQL commands.

When typing this:

  `psql -U postgres -d postgres -f dump.sql`

it's taken as an unfinished SQL sentence for the interpreter, so it's just waiting for the rest. That's why nothing happens as you wrote. This command is out of context.

What you want is:

\i /path/to/dump.sql

\i means 'include' and is the meta-command to feed a file as a stream of SQL commands to the interpreter.
You'll see the output of these commands on the terminal and when finished , type \q to end the session.


How to completely "reset" an Oracle instance? I'd like to drop all entities from an Oracle instance. I want to delete all tables, users, indices and so on, just like as if it would be a freshly created instance. The result should look, or at least closely approximate from the viewpoint of the clients, as if I had completely deleted and then reinstalled the instance with the same settings.

Doing this with a complete wipe/reinstall is slow, and it is not easily automatizable. I am looking for better options, but it is a possible solution, if there isn't.

Dropping the schemas is not enough, because some entities still remain in place (for example, users).

Is there an easy way to drop everything, to reach the initial clean and empty state, but more quickly?

There are just so many things in the database in addition to user data that checking all of them would take a considerable amount of time. Also there are some parts that were not meant to be removed at all, for example the history of used features (for license audit).

Sure, you could try to check all non-default configuration or rerun catalog scripts (but whats the point of that? creating a new database does exactly that). Its easier to just drop and recreate in 15-30 minutes depending on your selected components.

PS: there is no reinstall involved in dropping and recreating a database.


local administrator does not have permissions to create database in db2 I have a fresh standard installation of DB2 11.5 on Windows server 2016. According to documentation, i need the to have SYSADM or SYSCTRL to create a new Database.
The documentation for "sysadm_group - System administration authority group name configuration parameter" states.

SYSADM authority is determined by the security facilities used in a specific operating environment.

For the Windows operating system, this parameter can be set to local or domain group. Group names must adhere to the length limits specified in SQL and XML limits. The following users have SYSADM authority if "NULL" is specified for sysadm_group database manager configuration parameter:

Members of the local Administrators group
Members of the Administrators group at the Domain Controller if DB2_GRP_LOOKUP is not set or set to DOMAIN
Members of DB2ADMNS group if Extended Security feature is enabled.
The location of the DB2ADMNS group was decided during installation
The LocalSystem account

The parameter is empty and my account is in the DB2ADMN group which was automatically created during the install and also in the local admin group. Extended Security is enabled on that instance. Since my account used to be in these group through another group I added my account explicitly to both groups. Either way, db2 still thinks I don't have enough permissions to create a database.
I checked db2diag but there are no entries logged.
Is there anything that I missed or that I can check to see what's going on?

I had some chats with IBM and the issue is kind of simple. The db2 service is running as a local account (db2admin) just as a default installation would do. My account is a domain account and is in the local administrator group. The local account is not able to verify my domain account and therefore I am denied admin permission.
Solutions:

use a local account for connecting to the database
run db2 service under the local system account
run db2 service under domain account

I changed the setup to local system account. Waiting on IBM for letting me know what the security impact (risk) is.


How to test mirroring with a single Microsoft SQL Server license? I want to test mirroring in Microsoft SQL Server Enterprise in order to decide if I would implement one in production. Buying another copy of SQL Server for another machine is not an alternative, given the high cost of the product.

When configuring the mirroring and specifying the same server as the mirror server instance, the following error is shown:

  The principal and mirror server instances cannot be the same instance of SQL Server. Select another instance as the mirror server instance.

How can I test and discover mirroring feature of Microsoft SQL Server without having to purchase additional licenses?

You can install from the same media on another server without violating licensing; as long as the mirror secondary has equal or less processors than the primary. As long as you don't use it for active read-only connections, it doesn't need a paid license. See page 11[pdf], this is for SQL 2012, but there have been similar provisions as far back as I can remember.


Insert into specific table if the user session group I am trying to insert data into specific table by user session and group.

The tables are users, workers, help.

Structure of table "users":

id | username | password | group
---+----------+----------+---------
1  | john     | 123      | workers
2  | david    | 123      | help

Structure of table "workers" and "help":

id | username | message

I finished login with session pages and include session onto every page.
So when the user login it check and display from "user" table session: Hello "user"

The problem is with "insert into" statement, when john or david logs in and then they post something into database.

Which is the best way to create insert into statement so if it's john all insert into will go into "workers" and if it's david all insert into will go into "help"?

This look s like a job for Dynamic SQL

For this example, let's use david

STEP 01 : Look up the id of the username

SELECT id INTO @userid FROM users WHERE username = 'david';

STEP 02 : Create the SQL string to insert based on the id of username

SET @m = 'Whatever my message is supposed to be';
SELECT
CONCAT('INSERT INTO ',B.group,' (username,message) VALUES (''',B.username,''',''',@m,''')')
INTO @dynamic_sql
FROM information_schema.tables A INNER JOIN users B
ON A.table_name = B.group AND A.table_schema = DATABASE()
WHERE B.id = @userid;

STEP 03 : Compile and Execute the SQL

PREPARE s FROM @dynamic_sql;
EXECUTE s;
DEALLOCATE PREPARE s;

or in your case, just use the formatted SQL in PHP


How do I get a run duration in minutes for each individual step in a single Job? The Run_Duration in sysjobhistory of the the complete job. Is there a way to get this? [msdb].[dbo].[sysjobactivity] only has the last step.

This should get you started...

select
    jobs.name
    ,jobs.description
    ,steps.step_id
    ,steps.step_name
    ,steps.last_run_outcome
    ,last_run_time = stuff(stuff(right('00000' + cast(steps.last_run_time as varchar),6),3,0,':'),6,0,':')
    ,last_run_duration = stuff(stuff(right('00000' + cast(steps.last_run_duration as varchar),6),3,0,':'),6,0,':')
from [msdb].[dbo].[sysjobs] jobs
inner join [msdb].[dbo].[sysjobsteps] steps on
steps.job_id = jobs.job_id
order by jobs.job_id, steps.step_id


Slow query while selecting a higher offset from millions of rows I have sort of security log files containing millions of rows. On my current testing I imported the data for one month which is the sum of ~14,000,000 records. The table contains several set of fields including

id for primary key

log_time index

and other 8 columns.

When I perform a select query sorted by log_time with offset of some million, the query gets a lots of time to process.

My Query:

SELECT * FROM securityData ORDER BY log_time LIMIT 5000753, 50;

Explain

mysql> explain SELECT * FROM securityData ORDER BY log_time LIMIT 5000753, 50;
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | securityData | ALL  | NULL          | NULL | NULL    | NULL | 2664456 | Using filesort |
+----+-------------+------------+------+---------------+------+---------+------+---------+----------------+

Table Details

mysql> explain securityData;

+-----------+-----------------------+------+-----+---------+----------------+
| Field     | Type                  | Null | Key | Default | Extra          |
+-----------+-----------------------+------+-----+---------+----------------+
| id        | bigint(20)            | NO   | PRI | NULL    | auto_increment |
| log_time  | double(18,6) unsigned | NO   | MUL | NULL    |                |
/// more fields
+-----------+-----------------------+------+-----+---------+----------------+

The time of processing go higher and higher when I increase the offset value. I tried in both MyISAM and INNODB

How could I achieve the performance of this query?

You're seeing the intrinsic drawback of OFFSET pagination. Using LIMIT, you force your DB to sort all the records and count them all the way up to your OFFSET base.

Keyset pagination may correspond more closely to your actual needs here and will yield much better performance. As described in the above linked article, simply paginate based on the last key you've seen:

SELECT *
FROM securityData
WHERE id > ?last_seen_id
ORDER BY log_time
LIMIT 50;

This allows your DB to leverage the index over id to directly jump to the first relevant row instead of going through all 5000753 rows preceeding it.


How to grant set the replication attribute on RDS PostgreSQL? On the AWS RDS documentation I see that rds_superuser can:

  Grant and revoke the replication attribute onto all roles that are not
  the rds_superuser role. For more information, see this section in the
  PostgreSQL documentation.

source: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html

But, if I try, while logged in as my rds_superuser:

ALTER ROLE ROLENAME WITH REPLICATION;

I get an error stating that I cannot perform that action due to not being a superuser.

Do you guys know how to set the replication attribute to a user?

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

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

This seems to be working for logical replication.

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


How can I check which database objects are using certain tablespace in Oracle DB? How can I check which database objects are using certain tablespace in Oracle DB? Any query or somehow through SQL Developer GUI?

I'm using the following SQL quite often:

SELECT * FROM dba_segments WHERE TABLESPACE_NAME='USERS' ORDER BY bytes DESC;

It will find all the objects which consume some space in given tablespace and sorts the output by used space.

TEMP objects are always related to user session. So you need to query another view:

SELECT * FROM v$tempseg_usage;


DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002 I am doing a batch process which fetches rows from table 50000 times.

The query is:

SELECT * FROM <table name>  WHERE <condition; this changes for each iteration(50000)>

This batch process is done in java and uses jdbc connection.

The error shown is:

com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -805, SQLSTATE: 51002

This error happens at certain iteration (between 20000 to 30000)

I logged the query which caused the error and took this query and executed it using both external agent and java/jdbc; it worked fine.

what would be the problem?

Sounds like you are not properly deallocating objects like Statement, ResultSet, etc., so your application is holding open statement handles in the database.  This will eventually exhaust "CLI Packages" in the database, resulting in the SQL0805N error.

The proper solution is to make sure that you close these as soon as you're done reading them.

You may also find documents suggesting that you rebind to increase the number of CLI packages at the database level, but keep in mind that this is just a band-aid.


pt-online-schema-change -- can't get it to stop quitting w/ "Threads_running=108 exceeds its critical threshold 100" I've got an InnoDB table over 50GB with indexes. The query I'm running looks like this:

pt-online-schema-change h=localhost,D=db,t=table --critical-load="Threads_running=100" --check-slave-lag "h=XX.XX.XX.XX" --max-lag 10
--execute --pass PASS
--alter "REORGANIZE PARTITION max INTO (
PARTITION p19 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-05-01 00:00:00') ),
PARTITION p20 VALUES LESS THAN ( UNIX_TIMESTAMP('2016-06-01 00:00:00') ),
PARTITION max VALUES LESS THAN maxvalue
)"

No matter what settings I use, I get the Threads_running issue. I have also tried:

--chunk-size 100 (also tried 20)

--max-load="Threads_running=20"

Shouldn't it just wait until threads running gets lowered? Why does it keep quitting? I could raise the critical load to allow 200 threads, but I'm afraid of the server crashing.

Any advice?

The problem was that every now and then an unrelated query put a lot of stress on the server and was causing the threads running to nearly max out. It was doing this regularly before the schema change was first run.


Should I run SQL Server services on multiple servers under different AD accounts? I am looking at changing all of our SQL Server instances to use AD accounts for the running services.  I plan to use a different AD account for each service type, e.g. DB, SSIS, SSRS, etc.  My question is whether I should create an account for each type and use those accounts across all the instances in the environment or create accounts for each service per server.

For example, if I have 2 servers A and B both running a database instance and SSIS:

Scenario 1 (single AD account across domain)

SQLDB (database service)
SQLSSIS (SSIS service)

or

Scenario 2 (multiple AD accounts)

Server A accounts

SVRA_DB (database service on server A)
SVRA_SSIS (SSIS service on server A)
SVRB_DB (database service on server B)
SVRB_SSIS (SSIS service on server B)

I know scenario 2 won't work for failover clusters and does pose a potential headache in terms of maintenance if the AD accounts are set to expire.  Just wondering if there are any other gotchas to either approach and what best practices might be?

As an idea of our environment, we have about ~75 servers running various versions of SQL Server from 2005 up to 2014.

Thanks in advance for any help or insight!

Cheers,

Chris

Security answer: Absolutely every service on any service should have a dedicated account. This is noted in most best practice documents from Microsoft in the sense they are referring to one server. You give yourself less chance that if one service account is comprised then they only have access to that one server.

Overall answer: Base it on what amount of time you can dedicate to managing. In general if you have 75+ servers I would say limit the service account to each server for only certain services. As well, if you have cluster or AGs configured these will always need to be the same service account for SQL Server service (it is required for FCIs as well).

The less likely need is SSIS. This service does not execute packages for you. It is only there to manage Package Store in msdb or if you are using the file system (specific folder in SSIS configuration that is local to the server). Any package execution authentication required is either via a connection manager, or the caller that is executing the package...not the service.

SSRS is based on your reporting needs. If your SSRS subscriptions need to write out to a network share or other domain resource. If you do not require this then there is no real need.

In regards to password expiration. If that is required for security policy, definitely look into automation or reporting. This will help with the headache to know which account is expiring when so you can plan accordingly.

Some may tell you that Managed Service Accounts are an option, but...SQL Server cannot use MSA.


Why indexes are not speeding up multiple join query OK so I have 3 tables: contacts, notes and lastContact.

Questions are:
1) Whenever I want to do a double join of contacts to the other 2 tables the queries explode in time going to up to 15-60 mins.
2) Why with the current indexes just the count of the the primary key in the main table to the other 2 tables take that much time?

They both have a key in common, unique except for notes.

(Structure simplified removing all unnecessary columns)

    CREATE TABLE `contacts` (
    `contactID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `firstName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `lastName` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `phone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `mobilePhone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `altPhone` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
    `street` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `city` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `state` varchar(3) COLLATE utf8_unicode_ci DEFAULT NULL,
    `postalCode` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL,
    `email` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `source` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
    `owner` varchar(64) unsigned DEFAULT NULL,
    PRIMARY KEY (`contactID`),
    KEY `phone` (`phone`),
    KEY `phones` (`phone`,`mobilePhone`,`altPhone`),
    KEY `owner` (`owner`),
    KEY `source` (`contactID`,`source`,`owner`)
    ) ENGINE=InnoDB AUTO_INCREMENT=119138 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

    CREATE TABLE `contacts_notes` (
    `noteID` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `contactID` int(10) unsigned DEFAULT NULL,
    `notes` text,
    `dateline` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`noteID`),
    KEY `contactID` (`contactID`)
    ) ENGINE=Aria AUTO_INCREMENT=1486 DEFAULT CHARSET=utf8 PAGE_CHECKSUM=1;

    CREATE TABLE `lastContacts` (
    `contactID` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
    `ultimo` datetime DEFAULT NULL,
    `callID` int(10) unsigned DEFAULT NULL,
    `userName` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
    `lastContact` datetime DEFAULT NULL,
    `callCount` tinyint(3) unsigned DEFAULT NULL,
    PRIMARY KEY (`contactID`),
    KEY `callid` (`callID`),
    KEY `ultimo` (`ultimo`),
    KEY `username` (`userName`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

There are ~50k records in contacts and lastContacts, contact_notes has ~1k.

The query is:

SELECT COUNT(*) total FROM (
    SELECT l.contactID FROM contacts l
    LEFT JOIN lastContacts lc ON l.contactID = lc.contactID
    WHERE lc.contactID IS NULL
) x
LEFT JOIN contacts_notes ln ON x.contactID = ln.contactID
WHERE  ln.contactID IS NULL

@Jehad Keriaki provided the answer. The lastContacts table's primary key column, ContactID, was defined as a string while the same column in each of the other tables was an integer. Joining tables on that column caused implicit conversion and resulted in poor performance.


How can I enable the reports menu in SQL databases according to this picture the reports menu is disabled.

how can I enable this menu. I thought that maybe it depends on the version of my SQL server.so this is my SQL server version:

Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64)
Enterprise Edition (64-bit) on Windows 8.1 Pro 6.3 <X64> (Build 9600: )

I think it's not because of the version of your DB server but because of the version of your Management Studio.

I tried to connect a SQLServer 2016 with a Management Studio 2014, and indeed, the Report feature is disabled (only for databases).

And then tried with a Management Studio 2016, the Report feature is now available.

So, try to upgrade your Management Studio to the newest version.


Backup to URL not working when using Ola Hallengrens Database Backup I am using Ola Hallengrens DB backup and am trying to get it working with the URL parameter.

I first set up a Shared Access Credential in SQL with the following sql

USE MASTER
CREATE CREDENTIAL [https://exampleblob.blob.core.windows.net/backups]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
,SECRET = 'sv=2015-04-05&ss=bf&srt=sco&sp=rwd&se=2030-11-14T20:07:06Z&st=2016-11-13T12:07:06Z&spr=https&sig=KEY'
GO

Next I tried using the following in my log backup maintenance plan:

sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d DBATools -Q \
"EXECUTE [dbo].[DatabaseBackup] \
@Databases = 'USER_DATABASES', \
@BackupType = 'LOG', \
@Verify = 'Y', \
@Compress = 'Y', \
@CheckSum = 'Y', \
@LogToTable = 'Y', \
@ChangeBackupType='Y', \
@url='https://exampleblob.blob.core.windows.net/backups', \
@Credential='https://exampleblob.blob.core.windows.net/backups'" -b

However when I run the job I get the following error

  Use of WITH CREDENTIAL syntax is not valid for credentials containing
  a Shared Access Signature

I just ran into this issue as well creating a Maintenance Plan in SQL Server. The default backup maintenance plan creates a script that includes WITH CREDENTIAL, but I was able to get it working when I removed that default Task. So I ended up with this code in an Execute T-SQL Statement Task:

declare @backuptime as varchar(250);
set @backuptime = 'https://youraccountname.blob.core.windows.net/backups/mytestdb_' + CONVERT(VARCHAR(19), GETDATE(), 120) + '.bak';

BACKUP DATABASE yourdb
    TO URL = @backuptime


Tables Visible to a Specific User? Is there a way to get a list of tables and views that a specific user can see, without logging in as that user? Initially, I tried something like

select distinct p.table_name as value
from dba_tab_privs p
   join dba_synonyms s on
      p.owner = s.table_owner
      and p.table_name = s.table_name
      and s.owner = 'PUBLIC'
   join all_objects o on
      p.owner = o.owner
      and p.table_name = o.object_name
      and o.object_type in ('TABLE', 'VIEW')
where
   p.privilege = 'SELECT'
   and p.grantee = :user
order by p.table_name

However, while this would show tables and views that a specific user had been explicitly granted select on, it didn't seem to accurately reflect the full range of visible tables and views a user could see upon connecting to the database. For example, one user I tested had a little over 2000 tables and views visible when selecting on all_tables and all_views, but only reflected 300 records using the above query.

Try this...

select owner,table_name,grantable from dba_tab_privs where grantee = 'SCOTT' and privilege = 'SELECT';

Change SCOTT to the user you are interested in.


Is it possible to disable the remember password checkbox in SSMS? Some time we are providing same laptop for 4-5 employees in our office for some testing purpose (since a costly licensed tool is exist in that laptop only), also asked them to do not click the Remember password checkbox in SSMS to store the credentials.

But few of them accidentally click that checkbox, so each time we need to clear the SqlStudio.bin to solve the problem. Instead of that it is nice to disable the Remember password option in the SSMS in that particular laptop.

Is it possible to disable the remember password checkbox in SSMS?

The expecting behavior is as below:

No, this is not possible, unless you reverse engineer SSMS and either hide the checkbox or make it a no-op, then recompile and deploy. And repeat every time you upgrade, apply a service pack, etc. (To be clear: this is not what I recommend.)

I have a better solution, though:

Why don't your 4-5 employees use different profiles? This takes a tiny bit more disk space, sure, but each profile gets its own SqlStudio.bin - then you could use Windows Authentication, too, which means they don't have to type their password or check a box, and it simplifies auditing, too, in case this isn't the most egregious thing they do.


What's the fastest way to replicate a MySQL database from one machine to another? What's the fastest way to replicate a MySQL database from one machine to another? The two machines are connected via LAN.  How to do it without mysqldump?

If using mysqldump, is the fastest way

$ mysqldump -h localhost -u foo -pbar mydb > mydb.sql

and then copy mydb.sql to desination machine and do

$ mysql -h localhost -u foo -pbar < mydb.sql

?   When importing, do I need to specify the database?

I would personally do it in one step with a pipe using mysqldump like so

mysqldump -u user_source -p --opt database_name table_name | mysql -u user_target -p --host=target_db_ip -C target_database

but if mysqldump is not allowed just save the dump somewhere you can access it then use wget to move it to the other server and unpack there using mysql.  I normally use this command because it doesn't require me to stop the database and is a single command with no files rather than a process where I have to manage where the data files are.


How to compare data between staging and operational tables? I just want to ask how to compare two database tables, staging vs. operational tables.

After we did the transformation and convert it to correct datatype like convert flag column (Y/N) nvarchar to bit or to int, how to compare data between the two tables?

There are a couple of options if you want to use T-SQL and SSIS.

You could compare the key columns on the staging table vs ops table to determine if the row already exists and this way you would know if you need to do an INSERT or UPDATE.

If using SSIS you can use the lookup component. Your source componenet will have something like

SELECT keycol FROM OpsTable.

The lookup component will have a source something like

SELECT keycol FROM StagingTable.

You can use the 'No Match Output' option to get all the rows in OpsTable not in StagingTable.

If using T-SQL you can use the EXCEPT operator like so

SELECT keycol FROM OpsTable
EXCEPT
SELECT keycol FROM StagingTable

If you need to compare all the columns (key columns and non key columns) you can use the HASHBYTES and hash all the non key columns for comparison. Please note that HASHBYTES only accepts (VARCHAR, NVARCHAR, or VARBINARY) so you will have to convert any columns that are DATE or such into VARCHAR.

SELECT keycol,
       HASHBYTES('SHA1',col1 + col2 + ISNULL(convert(nvarchar(100),ColDate,109),'')
  FROM OpsTable

Hope this helps.

You can get more information on HASHBYTES here and on the EXCEPT operator here.


generate_series not working in single user mode A table is not being populated with generate_series for below.

backend> create table t1 (i int, j int);
backend> insert into t1 values (generate_series(1,200000), generate_series(1,200000));
backend> select count(*) from t1;
backend>

The INSERT ... VALUES (...), (...), ... ; syntax can accept multiple rows but you can't put a table generating function inside a row.

You need to use the INSERT .. SELECT .. FROM .. ; syntax and put the table generating function in the FROM clause:

insert into t1 (i, j)
select g.i, g.i
from generate_series(1, 200000) as g(i) ;


Alter on huge table consumes all disk space I'm running MySQL 8 on Ubuntu 19. I have a table that is 543G and I have to add a new column and index. I've started with the new column:

alter table hugeTable add column newCol tinyint(1) after existingColumn;

After a few hours of running, I received an error, roughly:

... table 'hugeTable' is full

I was looking around and I didn't see anything that was bad - I still had roughly 294G of disk space left. Looking at the error log, it became apparent what happened - it's generating a #sql file that keeps growing. I kicked it off again to get a feel for how quickly it grows and in just an hour it's over 35G:

-rw-r----- 1 mysql mysql  35G Nov 19 21:53 '#sql-ib1124-819861495.ibd'

and growing quickly.

So the real question is, is there a way around this? I have a backup of this table, so it's OK to risk turning this off and running the update with no undo, #sql, etc files. OR, will I have to use sed to modify the backup file and use that?

Make use of the new, better, ways of doing ALTER:

SET SESSION alter_algorithm='INSTANT' ;
ALTER TABLE foo ADD COLUMN ... ;  -- without the AFTER

SET SESSION alter_algorithm='NOCOPY' ;
ALTER TABLE foo ADD INDEX name(col1, col2);

If these cannot be done with the requested algorithm, they will give you an error without doing the ALTER.  From there, you can decide what to do.

A single-column index on a "flag" is rarely useful.  Perhaps you don't need the new index?


Is Data Warehousing possible in MySQL and PostgresSQL I am fairly new to DB design and development. My requirement is simple drilldown/slicing based on time and language of words (in a language on a particular day). However, my db is mysql. But so far I have no luck of running these kind of queries, so I am manually calculating this data and storing in tables.

To be more specific of my application, I need to show charts/graphs on iPhone. So a users usage of words per language per day/week/month. I dont need realtime but the rows for one month of usage for a user is approx 5000.

I want to understand if it is possible for mysql to have warehouse scehemas and execute such queries without performance issues. What could be my options.

Postgres?
MySQL Enterprise?
Cubes

I really cant pay for enterprise solutions, I am ready to write code for this kind of processing in my application (which I have) but want to migrate to correct way.

I guess the most ideal case for me would be that without using any tools I can create fact and dimensions in my current db and then shift my api to run these olap queries. From what I have gathered this is not possible for mysql. Postgres is showing some promise but still reading.

You can certainly use MySQL or PostgreSQL for this requirement using Python as your database access language. I've never used Python cubes so I can't speak to that.

I would recommend that you use PostgreSQL - it has windowing functions and CTEs (common table expressions). It also supports CHECK CONSTRAINTs and a full range of set operators.

MySQL is reasonably good for read heavy OLTP database loads but
little else.
PostgreSQL is superior for analytical work (DW - data warehousing, or
OLAP) while at the same time being an excellent all-rounder in the
OLTP arena.


Counting Number of Database Connections on a Page I have a LAMP stack(mysql and php) wordpress site and I want to know the number of database connections opened when a page loads. I can find other numbers but I can't figure out the number for a specific page.

One would hope that the answer is "1".

I don't think there is a way to get that answer easily...

Turn on the "general log".  Make sure that that page is loaded (and moved away from) at least once.  Turn off the general log.

Now look at the log.  Part of the info is a "connection number".  Try to decipher which queries apply to your page load and which came from others.  You may need help from the Apache log to see which pages were loaded, when.  If the general log is too 'busy', try again again.

You may be surprised (even appalled) at list of queries that show up.  It might be more than just "number of connections" that is 'inefficient'.  Start a new Question with the list; let's poke at it to see what could be improved.  (And tag it with [wordpress], too.)


Hash function and custom C type for PostgreSQL I am creating a custom type for PostgreSQL. The type is as follow:

typedef struct {
    unsigned long prefix;
    unsigned long long id;
} CustomType;

I have build all the in and out functions. Comparison functions and such, but I don't understand how to build the hash function. Since I will need it for hash joins. I had a look at the hash_numeric function in https://doxygen.postgresql.org/backend_2utils_2adt_2numeric_8c.html#a1358689e8be944cad3a3fad87eb237f1 and don't quite understand it.

How does a hash function works and what is its purpose?

The hash function in this context is used to tranform the set of all possible type values into a substantially smaller set of their hash values. The hash join works by separating values it needs to compare for equality into buckets based on their hash values. Values producing the same hash and therefore falling in the same bucket have a chance to be equal, while values in different buckets cannot be equal and thus can be removed from consideration.

One trivial example of a 10-bucket hash for integer numbers would be a function returning the least significant digit of its parameter. You'll probably be able to use the built-in hash_any function in your case, but read the caveats about HASHES in the manual.


Does the week day number changes according to localization in SQL Server? Does the weekday number changes according to server/database regional configuration in SQL Server (2005+)?

The weekday number is controlled by the SET DATEFIRST setting.

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

This setting is changed when the SET LANGUAGE setting is changed.

SET LANGUAGE English;
GO

SELECT
  @@DATEFIRST as DateFirst,
  CAST('1999-1-1' AS datetime2) AS SelectDate,
  DATEPART(dw, '1999-1-1') AS DayOfWeek;

Returns

DATEFIRST   SELECTDATE  DAYOFWEEK
7   1999-01-01 00:00:00.0000000 6

And

SET LANGUAGE German;
GO

SELECT
  @@DATEFIRST as DateFist,
  CAST('1999-1-1' AS datetime2) AS SelectDate,
  DATEPART(dw, '1999-1-1') AS DayOfWeek;

Returns

DATEFIRST   SELECTDATE  DAYOFWEEK
1   1999-01-01 00:00:00.0000000 5

You can find the DATEFIRST setting for each language by querying
sys.syslanguages view and referencing the datefist column.

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


How to see entire job step output? We have a SQL Agent job running some PowerShell, which creates a pretty long output (it has robocopy output, among other things).

In the SQL Agent Job History, we only see the first 1,000 characters or so of this output.

Is there another place we can look that will have the entire output?
Is there another approach that should be used to see the full output?

In the job step advanced properties, send output to a flat file by inputing the "Output file" location and filename.  That should include all of your output for later viewing.

Or, if you want all of the output directly in the history of the job, you can select the check box "Include step output in history".  It'll use history entry rollover to include all of the data.  That might be a little cumbersome in looking at after-the-fact, though, but still effective.


Help with schema design I am having trouble designing a schema in which there are two models an employee and projects. I am storing employees details in a table structure as:

id | employee | report_to

wherein employee table store the employee id and report_to store the id of the employee to whom the employee reports.

I have another table in which we store diff projects created by employees.

Id | project_name | employee_id

Now if I need to get projects done by employees who report to me I can easily get the list and also get the projects.

But the concern is eventually over time the employee reporting manager changes and hence along with that all the project that falls under let's say project manager 'a' also changes.

How to handle this kind of a problem in database. can this be designed in such a manner so that the projects which were initiated during that time remains under that particular manager only?

If your interest is explicitly to keep track of the direct manager of the employee, then the simplest solution may be to add manager_id to the project table, and populate that from employee.report_to when a project row is created. It's not worth setting up a full-fledged history mechanism just to track this specific relation.

If, on the other hand, this is intended to allow anyone to track down the projects they or their subordinates (at any level) started while they were their subordinates, then you'll need a full history mechanism. Check out the link mustaccio noted in the comments, to the question Best way to capture historical changes between levels in a dimensional hierarchy.


Merge multiple "is exists" queries into one statement I'm trying to figure out how to check if multiple rows exist in multiple tables in one statement.

Given the following tables:

table table_one
========
 id
 'a'
 'b'
 'c'

table table_two
========
  id
 'aa'
 'bb'
 'cc'

This works,

SELECT 1 from table_one where id = 'a'

and

SELECT 1 from table_two where id = 'aa'

works

How can I combine them into one statement?

I tried

 SELECT 1 FROM table_one WHERE id = 'a'
 UNION
 SELECT 1 FROM  table_two WHERE id = 'aa'

but this just merges the results into one column so I cant tell if a particular table was missing a row.

What i'd like is a result row something this

  table_one  table_two  table_three    table_four
  =========  =========  ===========    ==========
  1          1          [null]         1

UNION removes duplicate rows from the overall result, UNION ALL will preserve them.

To know from which table the result came, add a constant value:

SELECT 'one' as source,
       1
FROM table_one
WHERE id = 'a'
UNION ALL
SELECT 'two',
       1
FROM table_two
WHERE id = 'aa'


It seems like Oracle silently restarts my procedure I write a procedure to construct a report on OLAP Oracle server.
It works for some time.
Also after every operation I call autonomous_transaction log procedure to log my steps.
I dont use exception handling, so if something gone wrong, the procedure will failed.

But sometimes I see that in the middle of procedure it runs again! There no other processes which can run/rerun my procedure.
But other processes my drop/truncate/drop partitions.

Is it possible that Oracle restarts my procedure again absolutely SILENTLY - with no exception raising?

Not likely. It is possible that your procedure is being run more than you realize? How does the procedure get run?


Is the "load data infile" command in MySQL unsafe in general, or just when using statement based replication? I'm also trying to understand why MySQL considers "load data infile" unsafe and switches to RBR if running in mixed mode, does that mean it is only unsafe when using SBR?

As always, there is a trace on why they marked it unsafe in 5.5.6 and also in 5.1.52

There are several problems with reproducing LOAD DATA INFILE queries in this manner:

If the user does not know the internal workings of mysqlbinlog, the temp files will
accumulate on disk and never be removed.
There is no easy way to remove the temp files automatically (the safest way is to
parse the output of mysqlbinlog, which is hard and there is still no guarantee that the
format never changes, etc.)
Temp files will accumulate on the pushbuild server.


Are there any way to stop typing 'go' in sqlcmd? If I remember correctly, last time Microsoft Sqlcmd require each command to end with ';' and hit Enter key to execute the command.

However, currently, we need to type the GO each time to execute the command.

So, are there other alternative such that when we hit Enter key after ending the command with ';', it will Execute the command?

No, you need to type a batch separator. The default is GO. However, you can change that by using the -c switch.

For example:

C:\>sqlcmd -SServername -cFOO

1> select @@version

hit enter

2> FOO

hit enter

This will run.

Can't get rid of the intermediate enter for you. Sorry.


How do I easily create the DDL statements for a schema represented by the contents of an XML file? I have been given an xml file and it's pretty obvious in looking at the contents that it contains an inherent schema. What I'd like to do is run a program against it that outputs the schema represented by the contents, then create said schema in / as a database (in Postgres), and then populate said database so I can run SQL queries against the data.

I figured that this has to be a commonly desired capability, but I've never needed to do this before and am not sure what already exists.

I work in Postgres, and it has many XML related tools, so I figure that's a good place to look but so far haven't found it.

Added information:

Doing some digging, I found that the people who gave us the xml file also have an "xml schema file" that matches, and I've gotten a copy. It's on the web here for those that care.)

OK, so step one was satisfied; get to an "xml schema."

Now I need to convert that into an SQL schema...

have a look at liquibase - it's a great tool, and sounds like what you are looking for.
You could use it to create SQL statements using the updateSQL command or apply changes directly to the DB.

for example:

java -jar liquibase.jar \
    --driver=oracle.jdbc.OracleDriver \
    --classpath=jdbcdriver.jar \
    --url=jdbc:oracle:thin:@localhost:1521:oracle \
    --username=scott \
    --password=tiger \
    updateSQL > /tmp/script.sql

will output the required changes to the scripts.sql file.

It supports many db servers including Postgres, and you can model your schema in XML, JSON or any way you like.
See: XML changeset

<changeSet id="1" author="nvoxland">
    <createTable tableName="person">
        <column name="id" type="int" autoIncrement="true">
            <constraints primaryKey="true" nullable="false"/>
        </column>
        <column name="firstname" type="varchar(50)"/>
        <column name="lastname" type="varchar(50)">
            <constraints nullable="false"/>
        </column>
        <column name="state" type="char(2)"/>
    </createTable>
</changeSet>

It's open source and actively maintained.

We've been using it on multiple projects and dbs - highly recommended :)


Rebuilding Index in SQL Server 2014 During index rebuilds through Maintenance Task in SQL Server 2014, do we need to apply Keep Index Online while re-indexing option in the Maintenance Wizard ?

Only you can answer that question.

The option will make the index rebuild operation an online operation. See Perform Index Operations Online. There are certain limitations and restrictions, I recommend you read the link above and Guidelines for Online Index Operations as well. To call out a few issues, online operations have licensing edition restrictions, they consume more disk space, they take longer, and they pin the log from shrinking. However, they do allow for the application to continue to function online while the rebuild is ongoing. So, as you see, only you can answer what you need. Take the time, study, and make an educated decision.

Also, is very unlikely that you need to rebuild indexes for maintenance. Have a look at SQL Server Index and Statistics Maintenance.


How to determine how much disk-space postgresql 9.1 requires as temp_space to run a query? Given a query q to execute. Is there a way to determine how much disk-space postgresql will (or used) as temporary_space in order to execute it (during its execution) ?

I'm under Ubuntu 12.0.4 with postgresql 9.1

Knowing how much will be used is hard.  How much has been used, you there are logging options for that.

set log_temp_files=0;
set client_min_messages TO log;
select count(distinct x) from generate_series(1,10000000) x;

  LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25126.2", size 120127488.

  LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp25126.1", size 140000000


How can I get partition elimination with a variable? I have four identical tables on four servers that I need to query from a fifth.  The fifth server is linked to the other four and has a view created to unify them.

-- Servers 1-4
CREATE TABLE Transactions
(
    TxID int not null primary key,
    Description nvarchar(50) not null
);

-- Server 5
CREATE VIEW vwTransactions
AS
SELECT 1 as ServerID, * FROM Server1.DB.dbo.Transactions
UNION ALL
SELECT 2 as ServerID, * FROM Server2.DB.dbo.Transactions
UNION ALL
SELECT 3 as ServerID, * FROM Server3.DB.dbo.Transactions
UNION ALL
SELECT 4 as ServerID, * FROM Server4.DB.dbo.Transactions;

When I run a query with a constant where clause on the ServerID column, I get a nice happy query plan that eliminates three of the partitions.

SELECT * FROM vwTransactions WHERE ServerID = 1;

But when I use a parameter, or worse – a join, I get a scan of all four tables.

DECLARE @ServerID int = 1;

SELECT *
FROM vwTransactions
WHERE ServerID = @ServerID;

I have also tried using inline table valued functions to no avail.

How do I make a view of these tables which can be queried easily?

In this case, adding an OPTION(RECOMPILE) hint to the query will get you the plan you want.

DECLARE @ServerID int = 1;

SELECT *
FROM vwTransactions
WHERE ServerID = @ServerID
OPTION (RECOMPILE);

The hint allows SQL Server to compile a plan specific to the current value of @ServerID. More specifically, it enables the Parameter Embedding Optimization, meaning the query is compiled as if you had written WHERE ServerID = 1, allowing partition elimination.


Oracle return the first row, not returning the first row I have this query :

select (quarter_1 + quarter_2 + quarter_3 + quarter_4) as total
from budget where as_of_dt < to_date('2016-06-28', 'yyyy-MM-dd')
order by as_of_dt desc;

Which returns bunch of numbers such as :

My requirement is to always capture the first number, so when I try to do that :

select * from (
  select (quarter_1 + quarter_2 + quarter_3 + quarter_4) as total
  from budget where as_of_dt < to_date('2016-05-16', 'yyyy-MM-dd')
  order by as_of_dt desc
) WHERE ROWNUM = 1

I get this result :

Which is why I m puzzled about this, why is that I m getting the second row instead of the first one? What am I doing wrong here?

Update including as_of_dt in result for first query:

The answer is right there, in the data.  (I assume that the time part is 00:00:00 in all of them):

The order by has ties. There is not one "first row" but 3 of them.

So, when the 2nd query restricts the result to one row, one of the 3 is - arbitrarily - chosen. It may not be the same row chosen the next time you run the same query!


How to return a record from function, executed by INSERT/UPDATE rule (trigger)? I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4!
Here is an SQL fiddle.

The fiddle doesn't work for some reason (not really sure if advanced stuff is supported). So basically I have a custom function that first create the gi record, cast the primary ID from the gi record in a variable and then I use this to query some external table and use the result to finally create a joe record.

Now I need to support the following syntax:

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at)
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW()) RETURNING id;

This is a typical insert that is done in our application layer (it inserts a record and then uses the returning id). I'm not able to run this query because I get the following error:

ERROR: cannot perform INSERT RETURNING on relation "gi_joe"
SQL state: 0A000
Hint: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

How can I support the query with the RETURNING set from a custom function. Or is there a way to do everything in one INSERT (with an optional RETURNING field). Please note that my example is simplified.

Table setup:

CREATE TABLE gi
(
  id serial NOT NULL,
  weapon_type_id integer,
  fire_power integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT gi_pkey PRIMARY KEY (id)
);

CREATE TABLE joe
(
  id serial NOT NULL,
  first_name character varying,
  last_name character varying,
  city character varying,
  country character varying,
  gi_id integer,
  external_id integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT joe_pkey PRIMARY KEY (id)
);

CREATE TABLE external
(
  id serial NOT NULL,
  joe_id integer,
  CONSTRAINT external_pkey PRIMARY KEY (id)
);

View:

CREATE OR REPLACE VIEW gi_joe AS (
  SELECT  gi.id AS id,
    gi.weapon_type_id as weapon_type,
    gi.fire_power as weapon_fire_power,
    joe.first_name as first_name,
    joe.last_name as last_name,
    joe.city as city,
    joe.country as country,
    joe.external_id as external_id,
    gi.created_at as created_at,
    gi.updated_at as updated_at
  FROM gi LEFT OUTER JOIN joe ON gi.id = joe.gi_id
);

Function used in rule:

CREATE OR REPLACE FUNCTION gi_joe_insert(new_gi_joe gi_joe) RETURNS integer AS $$
  DECLARE temp_gi_id integer;
  DECLARE temp_joe_id integer;
  BEGIN
    INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
      VALUES (new_gi_joe.weapon_type, new_gi_joe.weapon_fire_power, new_gi_joe.created_at, new_gi_joe.updated_at)
      RETURNING id INTO temp_gi_id;

    SELECT joe_id INTO temp_joe_id FROM external WHERE id = new_gi_joe.external_id LIMIT 1;

    INSERT INTO joe(first_name, last_name, city, country, gi_id, external_id,
                    created_at,updated_at)
      VALUES (NEW.first_name, new_gi_joe.last_name, new_gi_joe.city, new_gi_joe.country, temp_gi_id,
            temp_joe_id, new_gi_joe.created_at, new_gi_joe.updated_at);
  RETURN temp_gi_id;
  END;
$$ LANGUAGE plpgsql;

Rule, insert with the returning id:

CREATE OR REPLACE RULE custom_insert_gi_joe AS ON INSERT TO gi_joe DO INSTEAD (
  SELECT gi_joe_insert(NEW) AS id;
);

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at)
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW()) RETURNING id;

The hint of the error message is right on target:

  You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

Bold emphasis mine. Per documentation:

  RETURNING queries on the view will be rejected if there is no
  RETURNING clause in any available rule.

Don't confuse this with the RETURNS clause of your function. Not the same thing.

Postgres looks for an actual RETURNING clause of an INSERT, UPDATE or DELETE statement. It has to be a plain SQL statement. There can only be one RETURNING clause, even if there are multiple statements in the rule:

  furthermore there can be at most one RETURNING clause among all the rules for the same event.

It will only be executed if the outer call has a RETURNING clause, too.

Since you are inserting into two tables, it gets slightly complicated. Either one of the two INSERT statements in the rule only has the columns of one target table at it's disposal. You would need to select from the other table (or from the view itself) in the RETURNING clause to provide a complete row.

Also, the returned value from the serial column cannot be stored in a variable, since there are no variables in plain SQL. I solved it with lastval() instead.

And NEW is not visible in a CTE, so you can't chain data-modifying CTE either.

But it can be done anyway:

CREATE OR REPLACE RULE custom_insert_gi_joe AS
ON INSERT TO gi_joe DO INSTEAD (

 INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
 VALUES (NEW.weapon_type, NEW.weapon_fire_power, NEW.created_at, NEW.updated_at);

 INSERT INTO joe(first_name, last_name, city, country
               , gi_id, external_id, created_at, updated_at)
 VALUES(NEW.first_name, NEW.last_name, NEW.city, NEW.country, lastval()
      ,(SELECT joe_id FROM external WHERE id = NEW.external_id LIMIT 1)
      , NEW.created_at, NEW.updated_at)
 RETURNING
   joe.gi_id,
   NULL::int,
   NULL::int,
   joe.first_name,
   joe.last_name,
   joe.city,
   joe.country,
   joe.external_id,
   NULL::timestamp,
   NULL::timestamp
);

SQL Fiddle.

I didn't bother to provide the complete row and just filled in NULL values for missing columns from gi, cast to respective types. This could be solved, too, but since you are only interested in the id column, it would be cost for no gain.

Now, this works as expected:

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at)
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW())
RETURNING id;


Export/Import Subset of data in postgres Using the following query, I'm able to export two tables, selecting 500 items randomly from the first:

copy (select * from (
          select 1 + floor(random() * 1040000)::integer as "documentUUID"
          from generate_series(1, 600) g
          group by 1) r
       join "Document" using ("documentUUID")
      inner join "Citation" on ("Document"."citation_id" = "Citation"."citationUUID")
      limit 500)
to '/tmp/data.sql';

I want to import this data into my testing database, but COPY FROM doesn't seem to be able to handle multiple table imports.

How can I make this work?

Your problem is that the output (row structure) of your query does not match either of your tables.

A possible solution would be creating a temporary table to put in your randomly generated IDs and copying data from one table at a time, using the necessary joins.

BEGIN;

CREATE TEMPORARY TABLE doc_ids ON COMMIT DROP AS
SELECT 1 + floor(random() * 1040000)::integer AS documentUUID
  FROM generate_series(1, 500) g;

COPY (
      SELECT d.*
        FROM Document d
        JOIN doc_ids ON i d.documentUUID = i.documentUUID
     )
  TO /tmp/document_data.sql;

COPY (
      SELECT c.*
        FROM Citation c
        JOIN Document d ON d.citation_id = c.citationUUID
        JOIN doc_ids ON i d.documentUUID = i.documentUUID
     )
  TO /tmp/citation_data.sql;

COMMIT;

Note that I removed the double quotes as they don't make any difference for the given table and column names.  Also I don't see what is the merit of grouping when generating the random document IDs.  Furthermore, I won't call the IDs ...UUID as UUID is a specific type used very often in very diverse applications.


problem to import load data infile I am trying to load data into mysql database form text file.

LOAD DATA LOCAL INFILE 'P:\file.txt' INTO TABLE table_january
FIELDS TERMINATED BY '\t'
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

  error code: 1148. The used command is note allowed with this MySQL

MySQL version 8.0.19.

looks like this an issue with mysql workbench 8 . Try this workaround.
1. locate or create your my.cnf file. E.g. if you're on a unix platform, look for ~/.my.cnf and create it if it doesn't exist.
2. update my.cnf to contain

[client]
port        = 3306
[mysqld]
port        = 3306
secure_file_priv=''
local-infile = 1

restart the MySQL server.


Postgresql / split one record on two rows depending of two fields Depending of this users table structure :

CREATE TABLE users
AS
  SELECT id,name,email1,email2
  FROM ( VALUES
    ( 1, 'John', 'john@doe.com', 'foo@bar.com' ),
    ( 2, 'Baz' , 'baz@bar.com' , null )
  ) AS t(id,name,email1,email2);

Is it possible to have this output with one query ?

John | john@doe.com
John | foo@bar.com
Baz  | baz@bar.com

SELECT name, email FROM users
UNION
SELECT name, email2 FROM users;

email and email2 have to be of the same type


What is the meaning of hbcolid field? I just connect to SQL Server and invoke the following command:

select * from sys.sysrscols

The result table contain a field hbcolid, which seem to be identical to rscolid. I think rscolid is surely for rowset column id. Then what is the meaning and usage of hbcolid?

Is there any document on this?

Given that this is an internal system table that you can only access when connecting via the DAC, no, there is no documentation on this. What are you using this table for?

I found several instances here where rscolid was not identical to hbcolid. For example:

SELECT rsid, rscolid, hbcolid
  FROM sys.sysrscols
  -- WHERE rscolid <> hbcolid
  WHERE rsid = 72057594038714368;

rsid                 rscolid   hbcolid
-----------------    -------   -------
72057594038714368    4         2

Where does this row come from?

SELECT OBJECT_NAME(object_id)
  FROM sys.partitions
  WHERE partition_id = 72057594038714368;

-------------------------
queue_messages_1003150619

So what indexes might be involved?

SELECT index_id, name
  FROM sys.indexes
  WHERE object_id = OBJECT_ID('sys.queue_messages_1003150619');

index_id   name
--------   ---------------------
1          queue_clustered_index
2          queue_secondary_index

So going on a guess, I ran this query:

SELECT
  ic1.index_id,
  c.name,
  ic1.column_id, -- column id in the table
  ic1.key_ordinal -- column order in the index
FROM sys.all_columns AS c
INNER JOIN sys.index_columns AS ic1
ON c.[object_id] = ic1.object_id
AND c.column_id = ic1.column_id
AND ic1.column_id <> ic1.key_ordinal
WHERE c.object_id = OBJECT_ID('sys.queue_messages_1003150619');

One of the rows here:

index_id   name                   column_id   key_ordinal
--------   ---------------------  ---------   -----------
1          conversation_group_id  4           2

So, without doing much more investigation, my initial guess is that rscolid is the column id within the base table, and hbcolid is the ordinal position of that column in the index. This holds true for 5 of the columns in the clustered index of this queue table - they all have identical mismatches in sys.sysrscols, and also for one of the columns in the non-clustered index.

But again, whether I'm right or wrong, I'm not sure what you could possibly do with that information: what are you using this table for?


SQL Joins Order of Operations I'm new to SQL but happen to be dealing with a couple of rather larger table (on the order of a billion rows each).

I want to do an inner join like the following:

SELECT *
FROM XTABLE X
   INNER JOIN YTABLE Y
      ON Y.date=X.date
      AND Y.symbol=X.symbol
WHERE X.date > 2011
   AND X.symbol in ('a','b','c')
   AND Y.symbol in ('a','b','c')
   AND Y.date > 2011

My question is:  What will the order of operations be for a query like this?  Will SQL make a new result set for rows with symbols{a,b,c} and dates{>2011} for each table X and Y and then do the inner join?

Short version: It depends. Generally spoken Sybase SQL Server is smart enough to do things the fastest way, though.

Long version:

Sybase's query processor is, at it's core, very similar to the one used in MS SQL Server.

It will create worktables (internal temporary tables; not visible to the user) if the result set is sufficiently large to overflow available memory (similar to a table spill in SQL Server). Otherwise, it'll do a pair of index scans (you do have indexes on date and symbol in both tables, right?), then a join and output, all in memory.

Caveats:

If the indexes (indices?) aren't selective enough, it will default to a table scan instead. I'm not sure what "enough" is, but I think it's around 30% (i.e. if statistics tell the optimiser it'll get more than 30% of the rows in the table, then just scanning straight through the table will cost the optimiser less than index scanning+lookups).
Worktables also can come into play if you have an ORDER BY statement or if the optimiser decides to use a merge join and the join criterion isn't the primary key (since a merge join requires sorted inputs).
Also, the cost estimation at the bottom of any showplan for any non-trivial plan is meaningless, it's a dimensionless value (i.e. just a number) meant for comparisons to other plans for that query only. i.e. You can't compare two queries on two different data sets (even on the same server) and say one's more expensive than the other, because the cost estimation isn't comparable like that. There's also some cases I've seen where the cost estimation for the same query has been wildly off (e.g. merge join with a cost of 1000 takes several hours to complete, SET MERGE_JOIN OFF and it defaults to NL joins with a cost of 10000, but runs in minutes). Optimising Sybase's query processor is a whole different book (actually there's four books, I just linked the advanced QP one).
There are some tiny corner cases where the query processor will do things in an obviously suboptimal way, but in 99% of cases you won't run into those.

That said, based on what I've seen the query plan would look something like this: (this is vastly simplified, the actual output from sp_showplan is rather more detailed)

|ROOT:EMIT (aka select)
|
|  | NL JOIN (nested loop join)
|  |
|  |  | RESTRICT Table X
|  |  |
|  |  |  | SCAN Table X (using index, forward scan)
|  |
|  |  | RESTRICT Table Y
|  |  |
|  |  |  | SCAN Table Y (using index, forward scan)

The reason it shows the RESTRICT after the SCAN is because without the SCAN there's no data to RESTRICT - I'm reasonably sure that you can effectively consider them as one operation in this case (i.e. the restriction conditions will be part of each index scan, rather than the index scan copying a whole lump of data and restrict throwing most of it out).


Started backup via RMAN, any way to nohup it? Backup script was failing, so we started new backup manually through RMAN, is there any way to force RMAN to work after i terminate my ssh session? Ctrl+Z in rman unfortunately does not work.

Once you start an RMAN operation, the process you started it from is required to stay alive and awake, as that is the session coordinating the other sessions.

You can not exit or put that session in background.

Next time use nohup or even better, my personal favorite: screen.


Deprecated raiserror detection I want to upgrade SQL server from 2008 to 2014 versiyon. I checked all of the deprecated features including raiserror syntax. But I have many stored procedures to check. Some of them use new syntax, some of them use old syntax and I need to alter the old syntax from them. How can I check all of the procedures to detect old raiserror syntax?

Below is a PowerShell script example that uses the T-SQL Script DOM parser to parse all existing T-SQL modules in the specified database and display errors if any cannot be parsed for the target SQL version. This will identify discontinued syntax for RAISERROR and any other discontinued T-SQL syntax as well.

$scriptDomAssemblyPath = "C:\Temp\Microsoft.SqlServer.TransactSql.ScriptDom.dll"
$scriptDomNuGetUrl = "https://www.nuget.org/api/v2/package/Microsoft.SqlServer.DacFx.x64/140.3881.1"

# if T-SQL script DOM assembly doesn't already exist at specifed path, download from NuGet and extract assembly from package to $scriptDomAssemblyPath
if(![System.IO.File]::Exists($scriptDomAssemblyPath)) {
    if([String]::IsNullOrWhiteSpace($scriptDomNuGetUrl)) {
        throw "Script DOM assembly not found at $scriptDomAssemblyPath and NuGet package download Url not specified"
    }
    $response = Invoke-WebRequest -Uri $scriptDomNuGetUrl
    if ($response.StatusCode -ne 200) {
        throw "Unable to download Microsoft.SqlServer.TransactSql.ScriptDom NuGet package: $response.StatusCode : $response.StatusDescription"
    }

    $tempZipFilePath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName() + ".zip")
    [System.IO.File]::WriteAllBytes($tempZipFilePath, $response.Content)
    $response.BaseResponse.Dispose()
    $tempUnzipFolderPath = [System.IO.Path]::Combine([System.IO.Path]::GetTempPath(), [System.IO.Path]::GetRandomFileName())
    Expand-Archive -Path $tempZipFilePath -DestinationPath $tempUnzipFolderPath
    $tempZipFilePath | Remove-Item
    Move-Item "$tempUnzipFolderPath\lib\net46\Microsoft.SqlServer.TransactSql.ScriptDom.dll" $scriptDomAssemblyPath
    $tempUnzipFolderPath | Remove-Item -Recurse
}

Add-Type -Path $scriptDomAssemblyPath

# use the appropriate TSqlParser version for target SQL version (TSql120Parser for SQL Server 2014)
$parser = New-Object Microsoft.SqlServer.TransactSql.ScriptDom.TSql120Parser($true)
$parseErrors = New-Object System.Collections.Generic.List[Microsoft.SqlServer.TransactSql.ScriptDom.ParseError]

# parse existing T-SQL modules in database
$connectionString = "Data Source=YourServer;Initial Catalog=YourDatabase;Integrated Security=SSPI;Application Name=Parse Modules"
$connection = New-object System.Data.SqlClient.SqlConnection($connectionString);
$command = New-object System.Data.SqlClient.SqlCommand("SELECT OBJECT_SCHEMA_NAME(object_id) AS SchemaName, OBJECT_NAME(object_id) AS ObjectName, definition FROM sys.sql_modules;", $connection);
$connection.Open()
$reader = $command.ExecuteReader()
while ($reader.Read()) {

    $scriptReader = New-Object System.IO.StringReader($reader["definition"])
    $frament = $parser.Parse($scriptReader, [ref]$parseErrors)
    if($parseErrors.Count -gt 0) {

        Write-Host "error parsing [$($reader["SchemaName"])].[$($reader["ObjectName"])]"
        foreach ($parseError in $parseErrors) {
            Write-Host "`t$($parseError.Message)"
        }
    }

}
$reader.Close()
$connection.Close()


End of file reached. Error 38 in SQL. Passes verification checks Having issues restoring a database in SSMS, error 38.

The databases are transferred up to google via their Drive API.

Download it on my side and has error.

If I transfer it through teamviewer from client PC I still get the same error.

Only happening on two clients the rest are fine.

I have tried backing up with FORMAT options and WITH CHECKSUM. Both succeed and backup is taking when verifying.

I have ran restore fileheaders only and get proper data except the physicalname path has a lowercase 'c' for the drive on the problem databases.

I have read this could be a corrupt database but I don't understand how the checksum and verify sql functions pass if so. Any insight would really help.

This is the backup command used in C#

"BACKUP DATABASE " + database + " TO DISK = '"
+ database + "_" + dateTime.ToString("yyyy_MM_dd_HH") +
"_CloudBackup.bak' WITH NAME = 'database', INIT, CHECKSUM"

EDIT: Running dbcc check on the database produced no errors. I have tried updated the physical name of the .mdf and .ldf it does not work still. Taking a backup from SSMS produces a valid backup file. It has something to do with the services I wrote.

EDIT 2: I am restoring through SSMS GUI. I have also tried RESTORE DATABASE db_name FROM 'backup_path'

Commands

RESTORE VERIFYONLY FROM DISK = 'backup_path'

On both computers, mine and the server PC yields "The backup set on file 1 is valid."

RESTORE FILELISTONLY FROM DISK = 'backup_path'

Have only test on my PC. Returns paths to mdf and ldf, no errors.

Both PCs have sql server 2012 (SP1)

I removed all compression all together (zipping the .bak via ZipArchive library in .NET) and pass it up to google and it works fine now. Something must be going wrong in the compression. Thank you for the lead into looking at compression =)

Originally left in a comment by user Austen Swanson (the OP).

So rather than the database being corrupt, the backup was getting corrupted in the process of trying to compress (or decompress) it using third-party compression.


Why does querying a PostgreSQL database have an overhead of a few dozens of milliseconds? Everything is in the same place, same local computer.
Whenever my Go application as well as pgAdmin 4 queries the PostgreSQL database, it takes at least a few dozens of milliseconds, however short the actual execution time is. Where does this overhead originate from? What causes the delay? It’s the same without the EXPLAIN ANALYSE.
pgAdmin 4

The problem is with “Total query runtime: 68 msec.” for EXPLAIN ANALYZE SELECT 1; taking 5 microseconds. There’s no way to reduce the client overhead other than querying less?

It's the overhead that the SQL client has, in this case pgAdmin.
Use a different SQL client. pgAdmin isn't known to be the fastest. You will always have an overhead. Parsing and network communication (even if everything is local) can never be avoided. The client needs to receive and display the result - that is where pgAdmin seems to be the slowest option (I have never used it myself, but this question shows up here and on Stack Overflow on a regular basis).


How can I list all user-owned functions? Is there a way to quickly list all the current user-owned functions in Oracle?

Something along the same lines as this, which lists the current user's tables

select * from user_tables;

The answer you are probably looking for is

select * from user_objects where object_type = 'FUNCTION';

However this won't return a list of packaged functions. I'm not aware of anyway of returning these other than by querying user_objects for objects of type 'PACKAGE' and then describing each in turn. Someone with a developer hat on might be able to answer the packaged function part of the question.