Trying to map postal codes to their respective cities, but running into some issues The Problem
I am creating a database of Countries, Regions, Cities and Postal Codes. Here's an example to give an idea of the database structure as it is now:

Now, let's take Tuskegee University as an example to illustrate my problem. Here is Tuskegee University's address:

1200 W Montgomery Rd
Tuskegee, AL 36088

For the city database, I grabbed postal code data from Geonames, and I cross referenced this data with USPS. In both dumps, for '36088' I get Tuskegee Institute. So, when I do the following:
SELECT a1.name,a1.address,b1.name,b1.postal_code
   FROM university a1
   JOIN city b1 ON a1.city_id = b1.city_id
   WHERE a1.name = 'Tuskegee University';

I get back:

"Tuskegee University";"Kresge Center, 3rd Floor";"Tuskegee Institute";"36088"

Thus, making my address:

Kresge Center, 3rd Floor
Tuskegee Institute, AL 36088

The Question
There are many problems here (e.g., the address: I downloaded university data from IPEDS and obviously their address does not match Google Places). However, what I want to focus on is the issue with my city table. I feel like the schema here may not be the best approach to the problem, but I'm not really sure what the best approach might be. Do I create separate city and postal code tables? But, in the end, how do I associate 36088 with Tuskegee, AL? Is there a better source here that I have just not found?
A Possible Solution (with problems)
Say I switched up the DB schema, such that it looked like this:

Then, I could have the postal code and city be independent of each other. Thus, the university table points to Tuskegee for city_id, and the 36088 for postal code. However, I now have an issue. If someone types in a city, I cannot tell them cities within a certain radius. I could get the lat/long for cities and create a geog column in the city table as well, but that seems a bad solution. I could associate cities with postal codes (i.e., city_id column in postal_codes table), but then I am faced with the same problem of mapping 36088 to Tuskegee, or having 'Tuskegee Institute'  in my city table (which I obviously don't want).
An Interesting Side Note
CityGrid seems to have completely ignored the problem I am facing, going with Tuskegee Inst for the city.
As well, a USPS lookup for this zip code tells me the following:

The preferred city in 36088 is...
TUSKEGEE INSTITUTE AL
Other acceptable cities for 36088 are
TUSKEGEE INST AL

However, the mailing address according to Tuskegee University's website says the following:

ATTN: (Recipient's name)
Tuskegee University (Dept. or Office name)
(Building name, Room #)
1200 W. Montgomery Rd.
Tuskegee, AL 36088

So, it seems that Tuskegee should also be an acceptable name for USPS, but it's not listed as such.

Your question is not very clear.

You say you are trying to create a database of cities and zip codes, but then you say you are using Geonames, which is a database of cities and zip codes. Why are you trying to re-create it?

Places are not hierarchical. You need to think about the institution responsible for the place's boundaries. State boundaries are determined by the US Constitution. City/county boundaries are determined by city and/or state governments. Zip boundaries are determined by USPS.

Some towns contain zips. Some zips contain towns. Some zips cross state boundaries.

A mailing address needs a zip AND some other info. Usually it is a city OR a "surveyed area" such as a street address (which in turn may belong to a city).

A university also likely has more than one address ;)

I would use a little table inheritance and give country, state/prov, city and surveyed area all the same supertype, that way you can point a single foreign key at any of them.


In SQL Server, how do I relate each date in a set to the subsequent date from the same set? I have a large table with a DATETIME2 column. I would like to write a query that relates each distinct date in that column to the date from the same column that immediately follows the first date. I suspect that I need to use a self-join, but I'm unsure about what to do next. How do I write such a query?
I don't have any preferences about how the case of the last date is handled. Its NextDate can be set to NULL or the row can be excluded.
Example:
DROP TABLE IF EXISTS #Dates

CREATE TABLE #Dates (
    MyDate DATETIME2 NOT NULL
)

INSERT INTO #Dates (MyDate) VALUES ('20210101')
INSERT INTO #Dates (MyDate) VALUES ('20210102')
INSERT INTO #Dates (MyDate) VALUES ('20210103')
INSERT INTO #Dates (MyDate) VALUES ('20210104')
INSERT INTO #Dates (MyDate) VALUES ('20210105')

SELECT
    MyDate AS CurrentDate
    -- , ??? AS NextDate
FROM #Dates
ORDER BY CurrentDate

How do I get:
+-------------+-------------+
| CurrentDate |  NextDate   |
+-------------+-------------+
|    20210101 |    20210102 |
|    20210102 |    20210103 |
|    20210103 |    20210104 |
|    20210104 |    20210105 |
|    20210105 |    NULL     |
+-------------+-------------+

Unless you're on a very old version of SQL Server, the LEAD function should work.
SELECT
    MyDate AS CurrentDate,
    LEAD(MyDate, 1, NULL) OVER
    (
        ORDER BY
            MyDate
    ) AS NextDate
FROM #Dates
ORDER BY CurrentDate;


MySQL conditional trigger delete I have a few tables (6 to be exact) and I want to make a trigger that will delete a row from a table based on a value from the main table.

To elaborate,

Let's say I have a main table called "main" and there is a column there called "table_short" and that row contains either "S", "P", "A", "Z" or "K".
"S" points to the "Stanovi" table, "K" points to the "Kuce" table and so on, meaning the data in the "table_short" in the main table is associated with another table. In the main table there is also a "id_t" column, that points to a specific row inside the table associated in the "table_short" column.

Now, I want to make a trigger statement that would, upon deleting a row in the main table, delete the row number in the table associated in "table_short", with the id specified in "id_t".

How could I do this?
Hopefully someone understood what I mean.

I tried this but it gives me an error when trying to make a trigger.

BEGIN
 CASE old.sifra
  WHEN "S" THEN SET @table="stanovi";
  WHEN "K" THEN SET @table="kuce";
  WHEN "A" THEN SET @table="apratmani";
  WHEN "P" THEN SET @table="poslovne_zgrade";
  WHEN "Z" THEN SET @table="zemljista";
 END CASE;
 DELETE FROM @table WHERE id = old.id_t;
END

it can not check trigger condition at the moment of save trigger code:

DELETE FROM @table WHERE @table.id = old.id_t;

because table name at this moment unidentified

BEGIN
 CASE old.sifra
  WHEN "S" THEN DELETE FROM stanovi WHERE id = old.id_t;
  WHEN "K" THEN DELETE FROM kuce WHERE id = old.id_t;
  WHEN "A" THEN DELETE FROM apratmani WHERE id = old.id_t;
  WHEN "P" THEN DELETE FROM poslovne_zgrade WHERE id = old.id_t;
  WHEN "Z" THEN DELETE FROM zemljista WHERE id = old.id_t;
 END CASE;
END

will be work


How to reduce foreign key performance impact in PostgreSQL Foreign keys add significant overhead...more than +600% in the example below (Docker PostgreSQL 11.4):

CREATE TABLE example_1 (a int PRIMARY KEY);
CREATE TABLE example_2 (a int NOT NULL REFERENCES example_1 (a));
CREATE TABLE example_3 (a int NOT NULL);

INSERT INTO example_1 SELECT generate_series(1, 1000);

-- with FK
INSERT INTO example_2 SELECT g FROM generate_series(1, 1000) g, generate_series(1, 10000);
Time: 91301.783 ms (01:31.302)

-- without FK
INSERT INTO example_3 SELECT g FROM generate_series(1, 1000) g, generate_series(1, 10000);
Time: 12395.756 ms (00:12.396)

Any ways to reduce this high FK overhead?

If you are a superuser, you can temporarily disable the system triggers that implement the foreign key checks:

BEGIN;
ALTER TABLE example_2 DISABLE TRIGGER ALL;
INSERT INTO example_2
   SELECT g FROM generate_series(1, 1000) g,
                 generate_series(1, 10000);
ALTER TABLE example_2 ENABLE TRIGGER ALL;
COMMIT;

Dire warning: Do this only if you are 100% certain that your data modifications will not produce inconsistencies. This is dangerous, which is also the reason why only superusers can do it.


SSMS crashes when attempting to create a database diagram I'm currently using the latest version of SSMS (17.8.1) on windows 10 and everytime I try to create a database diagram it crashes without giving me any error message. I've already tried to re-install SSMS but the problem still occurs.

I've found that the database diagram tool seems to crash if you modify things outside of the diagram.
Diagrams appear to remain stable if:

The database diagram tool is used to do all of your modifications, thus retaining all metadata of the process within the diagram.
Your database structure is modified outside of the diagram which is then deleted and recreated each time.
The entire database is built, ground up, to a point that you are satisfied and feel that it won't change.  The database should then be diagrammed post-beta.

Whichever method is used, this is a known issue.
To confirm, I have this issue while using SSMS 18.4 against a SQL 2016 Express instance.
Current Status - 10/03/2021 - SSMS Version 18.7.1
I still have issues with the diagram tool. It is slightly more stable, though, if you modify anything outside of a current diagram, the editor complains about metadata and you lose your work. In fact, I found that I had to also close and reopen SSMS to ensure I could edit or create a diagrams.  So, while SSMS remains a little more stable (that's debatable), whatever has been done is not a solution.
Roll on version 37.n - let's hope it's fixed by then!
Further Notes From a Small Island (12/03/2021)
There also appears to be an issue with putting your PC to sleep, particularly if you're working over a VPN. I found that when you sleep your computer, then attempt to modify a diagram that was left open, the editor errors with various meta-data related faults (there has been more than one fault). The remedy to this was to reconnect the VPN, then refresh the database diagrams folder in SSMS...

This should allow you to save the diagram.


Use variables in filename with INTO OUTFILE (timestamped filename) SELECT *
FROM project
INTO OUTFILE 'C:\desktop\[timestamped filename like 01/05/2014.csv]';

Can anyone help me out?

Has said in this post, you can use:

SET @sql_text =
   CONCAT (
       "SELECT * FROM `project ` into outfile 'C:\desktop\"
       , DATE_FORMAT( NOW(), '%d/%m/%Y')
       , ".csv'"
    );

PREPARE s1 FROM @sql_text;
EXECUTE s1;
DROP PREPARE s1;

Thanks Haim Evgi for the original answer.

Max.


SQL Server: SSMS "Save Results As" creates CSV that Excel mangles Using the Save Results As option, shown below.
CSV looks good!

But when Excel opens/imports it:

Long strings are mangled as numbers (if the data is largely numeric)
Dates are not detected properly
My users beef about all this

How do I get a query to a clean excel file (a real xls/xlsx) without dancing? (We like everything to actually in the SQL file itself... I wish we could do "SELECT INTO FILE:C:\SQLOUTPUT\DATA.XLS ...")

First, make sure you've got the query options set up the way you want.

Go to the Query menu, and choose Query Options:

The first of the two highlighted options tells SSMS to include the column headers in your CSV file. The second tells SSMS to put single quotes around columns that include a comma.

I suspect you've already done this, but thought I should mention it for completeness.

If this isn't sufficient to get your data into a format Excel will correctly interpret, my next step might be (as noted in the comments) to use the Import/Export Wizard to generate a SSIS package that does this. However, the wizard does not always get the quoting correct - in particular, it can put things in double quotes, but won't "double up" the double quotes embedded in the string.

Generally at that point, I resort to brute force.

I create a modified query that:

Replaces double quotes in the data (") with double double quotes ("");
Converts numeric/date values to strings, and
Puts all data into double quotes, and (finally)
Outputs all the columns concatenated together, separated by commas.

So, instead of

 ID | First Name | Last Name |      Address
----+------------+-----------+-------------------
  1 |  John "JT" |   Smith   |  123 Wayne's Way

I generate:

One_Big_Column
------------------------------------------------
"1","John ""JT""","Smith","123 Wayne's Way"

If I'm going to use the query regularly, it'll include a sortOrder column (set to 1), and be UNION ALLed with a SELECT that creates the header row. I then wrap the UNION ALLed queries (making them a subquery), so I can select just One_Big_Column, and sort by sortOrder.

SELECT One_Big_Column
  FROM (
        SELECT '"Id","First Name","Last Name","Address"' as One_Big_Column
              ,0 as sortOrder
        UNION ALL
        SELECT '"' + CAST(Id as varchar(30)) + '","'
                    + REPLACE(firstname, '"','""') + '","'
                    + REPLACE(lastname, '"','""') + '","'
                    + REPLACE(address, '"','""') + '"'
              ,1 AS sortOrder
          FROM myTable
       ) sq
 ORDER BY sortOrder
;

You can see this run here.

Note: If you're formatting the CSV manually, you'll probably want both of the check boxes noted above turned off.


Sql Server Management Studio can't remember passwords When I connect to a database using ssms, I also have a checkbox labelled "Remember password". However, ssms will not remember the password. The next time I connect to the same server, I have to write it again.

What is the actual functionality of the "remember password" checkbox?

As said in the link from comment, It's a bug in SQL Server Management Studio

SQL Server 2012 Management Studio spuriously forgets password. and SQL Server Management studio solution explorer doesn't remember SQL authentication password

Some alternate solutions may work for you.


Obvious inefficiencies in this Netezza query? Why might this query be taking nearly 2 hours to run when similar queries complete within a minute?

CREATE TABLE T3 AS
SELECT
        A.*,
        COALESCE(C.FIELD1,0) AS FIELD1A,
        COALESCE(B.FIELD2,0) AS FIELD2A,
        COALESCE(B.FIELD3,0) AS FIELD3A,
        COALESCE(C.FIELD4,0) AS FIELD4A,
        COALESCE(C.FIELD5,0) AS FIELD5A,
        B.KEY1,
        COALESCE(C.FIELD6,0) AS FIELD6A
FROM T1 A
LEFT JOIN T2 B
ON   A.KEY2 = B.KEY1
AND  B.TIMEFIELD1 <= DATE '28FEB2013'
AND  B.TIMEFIELD2 > DATE '28FEB2013'
AND  B.FIELD7= 'X'
LEFT JOIN T2 C
ON   A.KEY3 = C.KEY1
AND  C.TIMEFIELD1  <= DATE '28FEB2013'
AND  C.TIMEFIELD2 > DATE '28FEB2013'
AND  C.FIELD7= 'X'

DISTRIBUTE ON RANDOM

T1 has about a 400,000 rows; T2 has about a million rows. Both are distributed on random. T3 has the same row count as T1 (as expected).

EXPLAIN suggests the query plan uses nested loops.

(Apologies for the generic column names).

I have noticed that the optimizer doesn't enforce the constraints on left joins efficiently.  I have success in this situation by forcing the constraints in sub queries.  Consider revising your query like below:

CREATE TABLE T3 AS
SELECT
        A.*,
        COALESCE(C.FIELD1,0) AS FIELD1A,
        COALESCE(B.FIELD2,0) AS FIELD2A,
        COALESCE(B.FIELD3,0) AS FIELD3A,
        COALESCE(C.FIELD4,0) AS FIELD4A,
        COALESCE(C.FIELD5,0) AS FIELD5A,
        B.KEY1,
        COALESCE(C.FIELD6,0) AS FIELD6A
FROM T1 A
LEFT JOIN
(select * from T2 where TIMEFIELD1 <= DATE '28FEB2013'
AND  TIMEFIELD2 > DATE '28FEB2013'
AND  FIELD7= 'X' ) B
ON   A.KEY2 = B.KEY1
LEFT JOIN
(select * from T2  where AND  TIMEFIELD1  <= DATE '28FEB2013'
AND  TIMEFIELD2 > DATE '28FEB2013'
AND  FIELD7= 'X') C
ON   A.KEY3 = C.KEY1

DISTRIBUTE ON RANDOM


How to restore operational Oracle 11g Database from full backup? Imagine that things gone bad and all we have is a fresh Windows Server and full cold backup of our Oracle database.

In case of SQL Server 2000/2005/2008/2012 it was really a matter of few mouse clicks: copy backup files, install db instance, restore files with few clicks, recreate users and we are good to go.

But now we are setting up Oracle 11g DB Standard as our database solution and from what I've seen over Internet it's a really complicated issue up to the point where this cold backup files might become completely useless without "actions" taken outside "usual" backup procedure.

While looking for answers, I've ran across certain topics like "db duplication form backup without access to flash recovery area" and etc which looks like to be close to what we were doing in SQL Server. Problem is all this things are far outside of my own experience (SQL Server, remember?) so I can't even tell if I'm looking in a right direction.

So my question is (I'll split it in two, because I think it matters in Oracle):

1) What should I do in order to properly "backup" my production database and restore it lately on different machine in case of emergency if we have not got yet any emergency instance installed?

2) What should I do in order to clone my production database and restore it on prepared instance (to clone production db).

What's most important: is there a common approach to both this tasks like in SQL Server?

In short - yes, there are standard operating procedures for doing all of this with Oracle. You should start by looking into RMAN (Recovery MANager). I have put together a high level overview of RMAN as well as an introduction to Oracle backups for SQL Server DBAs. I suggest watching both of those and then heading over to the Oracle Database Backup and Recovery User's Guide to learn more about the options available to you.

Basically, though - RMAN can perform all of these tasks for you. If you need additional tooling, RMAN + Oracle Enterprise Manager can provide you with automation, cloning, and other reporting capabilities. Oracle Enterprise Manager Database Control (dbcontrol) will also be very helpful, it will give you additional web-based GUI tools.

To get to a point where you can get to restoring from a bare bones Windows server and cold backup file is going to take significant training. You need to work with the business to define effective RPO/RTO plans, budget training for the future, and then do what you can right now to mitigate the risks before you're stuck with an empty server.

Improving the original answer slightly...

You should start using Oracle Recovery Manager (RMAN) to back up your databases. Depending on how you configure RMAN, you may be sending this to NFS or to a set of disks on a different SAN. The way you configure this has a lot to do with your RPO/RTO policies. If you need to send this to a cool standby server, you should look into Oracle Data Guard. Depending on the configuration, this may take the place of either log shipping or an AlwaysOn Availability Group.
You can clone a database using RMAN, just make sure you have everything in place and you practice it.


Checking whether my pgAdmin III is a 32 or 64 bits version I need to check if my pgAdmin III is a 32 or 64 bits version. Where can I find this information? The About... menu in pgAdmin GUI does not mention it (only the version number like 1.20).

I've looked for this on the net, without success...

EDIT:

I get a win32 error when pgAdmin crashes (connection lost). See image below (sorry my Windows is a french version):

Your image path may give that away:

get-wmiobject win32_process | where {$_.Name -like "pgadmin3.exe"} | select -property Name,ExecutablePath | fl


Why am I unable to create this Availability Group? (error 35220) I was attempting to create an Availability Group on a SQL Server 2017 instance and was running into error 35220:

  Could not process the operation. Always On Availability Groups replica
  manager is waiting for the host computer to start a Windows Server
  Failover Clustering (WSFC) cluster and join it. Either the local
  computer is not a cluster node, or the local cluster node is not
  online. If the computer is a cluster node, wait for it to join the
  cluster. If the computer is not a cluster node, add the computer to a
  WSFC cluster. Then, retry the operation.

I was stumped, because the host was most definitely part of a WSFC, and the node was online.  I eventually found the answer in a blog that was focused on Multi-Subnet Availability Groups, which I hadn't looked at earlier because mine was not Multi-Subnet.

Thus, I'm posting this here to give better visibility to the solution in case some other poor DBA gets stumped the same way I did.  I will post the solution below.

Here's the blog where I found the answer: https://bahtisametcoban.home.blog/2019/01/04/always-on-availability-groups-with-multi-subnet-failover-cluster/.

In case you don't feel like reading through it, the answer is

  If you enable [Availability Groups] before cluster is fully operational and ready  you will get an error when you try to create AG

When I installed and configured SQL Server on the machines in question, I jumped the gun on enabling the Availability Groups feature (SQL Server Configuration Manager->Services->SQL Server->Properties->AlwaysOn High Availability tab) and checked the box (and restarted the service) before the WSFC was created.  This is what caused the error when I tried to actually create an AG.

The fix is easy: just disable Availability Groups, restart the service, then re-enable them and restart it again.

Hope this has been helpful!


Automatic failover support on SQL Server having only two servers We have two servers each one occupied by a dedicated storage. I am looking for the a way to setup these two servers to provide a database on MS SQL Server with automatic fail-over support.

Using Database Mirroring seems to be ineffective for automatic fail-over when only two servers are available.

More info on why: For automatic fail-over, Database Mirroring needs a third element called witness to coordinate Principal/Mirror roles between two DB instances and assist switching during fail-over. However, I only have two servers available so I am forced to install witness on one of the servers that can be either Primary (Principal) or Secondary (Mirror) DB instance. So when a hardware failure occurs on the server that both principal DB and witness are running, the mirror DB cannot become online and automatic fail-over doesn't happen.

A recent feature of SQL Server called Always On Availability Groups seems to provide the similar functionality for mirroring plus more. Can automatic fail-over be achieved using Always On Availability Groups having only two servers?

Do you have any other recommendation for having automatic fail-over support, particularly against power failures, by two servers?

For automatic failover, you need a tie-breaker.

Otherwise, what would happen when the two servers had a network split and couldn't see each other? You wouldn't want them both automatically promoting themselves to primary, and both accepting writes. Think about what would happen on a table with an identity field, for example: both servers could quickly end up with two different records with the same identity field.

This is called a split brain scenario, and you always want SQL Server to fail into a non-available state when that happens.

To break the tie, database mirroring can use a witness server, but it can be an absolutely free SQL Server Express Edition.

Always On Availability Groups is different than mirroring in that it's built atop Windows clustering, which means it relies on quorum voting to achieve the tiebreaker. Normally, you would want a third Windows server involved, but good news: it doesn't have to have SQL Server installed. You can use a file share to act as a quorum witness, or even a cloud witness.

You could even get fancy and remove the quorum votes from one of your servers - but if that server goes down, your entire cluster will go down.


Is using DISTINCT as a hint in subqueries useful? Does adding DISTINCT in the following example have any impact on the query running time?
Is it wise to use it as a hint sometimes?

SELECT *
FROM   A
WHERE  A.SomeColumn IN (SELECT DISTINCT B.SomeColumn FROM B)

When wondering about things like this you should compare the execution plans for your queries.

The shape of the execution plan for your query will of course differ depending on how many rows you have in your tables and what indexes is defined.
One scenario that shows there is no difference in performance is when there are substantially more rows in A than there are in B. The optimizer will then choose B as the driving table in a nested loop join against A. In order to get a correct result back it has to use a Stream Aggregate on table B in both queries to get only the distinct rows from B. So in this case the distinct keyword has no impact on performance.

The execution plan for two other obvious cases to test, more rows in B than A and equal number of rows in the tables, also shows the exact same execution plan for the queries.

Update

Before query optimization takes place the query goes through a simplification phase. You can see what the logical tree looks like using the trace flag 8606.

The Input Tree for the queries are clearly different but after simplification they are the same.

Ref: More Undocumented Query Optimizer Trace Flags and Query Optimizer Deep Dive – Part 2

Input tree and simplified tree for query using distinct:

*** Input Tree: ***
        LogOp_Project QCOL: [xx].[dbo].[A].SomeColumn
            LogOp_Select
                LogOp_Get TBL: A A TableID=213679909 TableReferenceID=0 IsRow: COL: IsBaseRow1002
                ScaOp_SomeComp 2
                    ScaOp_Identifier QCOL: [xx].[dbo].[A].SomeColumn
                    LogOp_GbAgg OUT(QCOL: [xx].[dbo].[B].SomeColumn,) BY(QCOL: [xx].[dbo].[B].SomeColumn,)
                        LogOp_Project
                            LogOp_Project
                                LogOp_Get TBL: B B TableID=229679966 TableReferenceID=0 IsRow: COL: IsBaseRow1006
                                AncOp_PrjList
                            AncOp_PrjList
                        AncOp_PrjList
            AncOp_PrjList
*******************
*** Simplified Tree: ***
        LogOp_LeftSemiJoin
            LogOp_Get TBL: A A TableID=213679909 TableReferenceID=0 IsRow: COL: IsBaseRow1002
            LogOp_Get TBL: B B TableID=229679966 TableReferenceID=0 IsRow: COL: IsBaseRow1006
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [xx].[dbo].[A].SomeColumn
                ScaOp_Identifier QCOL: [xx].[dbo].[B].SomeColumn
*******************

Input tree and simplified tree for query not using distinct:

*** Input Tree: ***
        LogOp_Project QCOL: [xx].[dbo].[A].SomeColumn
            LogOp_Select
                LogOp_Get TBL: A A TableID=213679909 TableReferenceID=0 IsRow: COL: IsBaseRow1002
                ScaOp_SomeComp 2
                    ScaOp_Identifier QCOL: [xx].[dbo].[A].SomeColumn
                    LogOp_Project
                        LogOp_Get TBL: B B TableID=229679966 TableReferenceID=0 IsRow: COL: IsBaseRow1006
                        AncOp_PrjList
            AncOp_PrjList
*******************
*** Simplified Tree: ***
        LogOp_LeftSemiJoin
            LogOp_Get TBL: A A TableID=213679909 TableReferenceID=0 IsRow: COL: IsBaseRow1002
            LogOp_Get TBL: B B TableID=229679966 TableReferenceID=0 IsRow: COL: IsBaseRow1006
            ScaOp_Comp x_cmpEq
                ScaOp_Identifier QCOL: [xx].[dbo].[A].SomeColumn
                ScaOp_Identifier QCOL: [xx].[dbo].[B].SomeColumn
*******************


Proper tuning for 30GB InnoDB table on server with 48GB RAM I have a 30GB 15M rows InnoDB MySQL 5.5.15 table. It is running on a server with many other MyISAM tables (300GB db). The system has 48GB RAM. Besides the default configurations, I've changed the following values:

InnoDB buffer pool to 10GB
log file size 5M

Note that my key_buffer_size of MyISAM is 8GB and I'm also allowing Memcache to use 8GB, and have Redis installed (not sure how much he takes).

Are there any more values I should try to configure?

MyISAM Key Cache
You said you have key_buffer_size at 8GB.
Question: Do you really have 8GB of MyISAM indexes?
Please run this query
SELECT
    KBS/power(1024,0) KBS_BB,
    KBS/power(1024,1) KBS_KB,
    KBS/power(1024,2) KBS_MB,
    KBS/power(1024,3) KBS_GB
FROM
(
    SELECT SUM(index_length) KBS
    FROM information_schema.tables
    WHERE engine='MyISAM' AND table_schema NOT IN
    ('information_schema','performance_schema','mysql')
) A;

This will reveal how big your key_buffer_size would need to be IF ALL MYI PAGES WERE LOADED. To get a realistic picture run these please:
SELECT
   FLOOR((A.variable_value * B.variable_value)/power(1024,2)+0.5) KBS_USED_IN_MB
FROM
   information_schema.global_variables A,
   information_schema.global_status B
WHERE
   A.variable_name = 'key_cache_block_size' AND
   B.variable_name = 'Key_blocks_used'
;

This will give you how many MB (megabytes) the key_buffer_size is in use. You should round this up to the nearest GB.
Log File Size
5MB is the default size for innodb_log_file_size. Percona's mysqlperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out
SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

Whatever number comes back for @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?


MySQL TIMESTAMP isn't behaving as advertised I'm using MySQL 5.5.34. The 5.5 Documentation says I can create a table like this:

CREATE TABLE t1 (
  ts1 TIMESTAMP DEFAULT 0,
  ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP);

From the documentation:

  For t1, ts1 is NOT NULL and assigning it a value of NULL sets it to the current timestamp.

The documentation also says I can only assign CURRENT_TIMESTAMP as the default to a single TIMESTAMP field only. No multiple fields auto updating.

So, I tried this exact trick:

CREATE TABLE `users` (
  blah blah blah,
  `record_created` TIMESTAMP DEFAULT 0,
  `last_updated` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP);

When I execute the CREATE command, I get this back:

ERROR 1067 (42000): Invalid default value for 'record_created'

and the table doesn't actually get created.

What am I missing?

Your mysql server's global configuration don't allow you to insert ZEROS into table column.
The global configuration parameter SQL_mode is responsible for this behavior. You can find it in your my.ini file if configured or you can also query mysql server to display its value,

select @@SQL_mode;

It should return you 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE', which needs to be reset by sql command set global SQL_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; or as per your requirement.


Discover available HD storage from inside SQL Server I have a SQL Server 2012 instance hosted in a VPS. I can't login on VPS's Windows directly. I know that there are multiple instances installed on that VPS.

I have a query that shows me each DB's file name, that file's size, used and unused space. By their file paths, I can see they aren't all on the same partition (IDK why the infrastructure admin created multiple partitions...).

I need to see each partition's size and free space. Is there a way to find that from a query in SQL Server without having to login on Windows?

I use the below to get SQL Server OS drive partition\volume information SQL Server per instance in both GB and MB.

INFORMATION: This is using a SQL system DM (sys.dm_os_volume_stats) and sys.master_files table with the CROSS APPLY to get the volume information from which that instance can see.  It then does mathematic calcutations (e.g. dividing by 1024 a time or two or three or perhaps multiplying by 100, etc. as you see below in the CAST statements) to these and lists in both MB and GB as and uses the NUMERIC function.

This will get both TOTAL and FREE space space per disk partition in both GB and MB as well as the percentage of FREE SPACE per disk partition using straight TSQL.

SELECT DISTINCT UPPER(vs.volume_mount_point) AS Drive_Letter
    ,vs.logical_volume_name AS Drive_Label
    ,CAST((vs.total_bytes / 1024.0 / 1024) AS NUMERIC(18, 1)) AS Total_Size_MB
    ,CAST((vs.available_bytes / 1024.0 / 1024) AS NUMERIC(18, 1)) AS Free_Space_MB
    ,CAST((vs.total_bytes / 1024.0 / 1024 / 1024) AS NUMERIC(18, 1)) AS Total_Size_GB
    ,CAST((vs.available_bytes / 1024.0 / 1024 / 1024) AS NUMERIC(18, 1)) AS Free_Space_GB
    ,CAST((vs.available_bytes / 1024.0 / 1024 / 1024) / (vs.Total_Bytes / 1024.0 / 1024 / 1024) AS NUMERIC(18, 3)) * 100 AS Free_Space_Percentage
FROM sys.master_files f
CROSS APPLY sys.dm_os_volume_stats(f.database_id, f.file_id) AS vs
ORDER BY UPPER(vs.volume_mount_point)

SQL Server get drive space information, get volume space information, get disk space information, get disk free space, disk space allocation, report disk space, report storage space


Restoring database without worring about partitions On our our production database there is a partitioned table called FullLog. This is updated via triggers so we know who changed the database and when. Which is a problem for me when I restore the production database on our development servers as it doesn't have an F drive. Is there a easier way I can tell it to move to the specified location without knowing how many ndf files are there.

RESTORE DATABASE MartinUAT
  FROM DISK = 'C:\DBBaks\MARTIN_PreChange_2015_06_09_0800.bak'
  WITH MOVE 'CMTS_dat' TO 'e:\SQLRestore\MARTIN_UAT_.mdf',
  MOVE 'P1' TO 'e:\SQLRestore\MARTIN_UAT__1.ndf',
  MOVE 'P2' TO 'e:\SQLRestore\MARTIN_UAT__2.ndf',
  MOVE 'P3' TO 'e:\SQLRestore\MARTIN_UAT__3.ndf',
  MOVE 'P4' TO 'e:\SQLRestore\MARTIN_UAT__4.ndf',
  MOVE 'P5' TO 'e:\SQLRestore\MARTIN_UAT__5.ndf',
  MOVE 'P6' TO 'e:\SQLRestore\MARTIN_UAT__6.ndf',
  MOVE 'P7' TO 'e:\SQLRestore\MARTIN_UAT__7.ndf',
  MOVE 'P8' TO 'e:\SQLRestore\MARTIN_UAT__8.ndf',
  MOVE 'P9' TO 'e:\SQLRestore\MARTIN_UAT__9.ndf',
  MOVE 'P10' TO 'e:\SQLRestore\MARTIN_UAT__10.ndf',
  MOVE 'P11' TO 'e:\SQLRestore\MARTIN_UAT__11.ndf',
  MOVE 'P12' TO 'e:\SQLRestore\MARTIN_UAT__12.ndf',
  MOVE 'P13' TO 'e:\SQLRestore\MARTIN_UAT__13.ndf',
  MOVE 'P14' TO 'e:\SQLRestore\MARTIN_UAT__14.ndf',
  MOVE 'P15' TO 'e:\SQLRestore\MARTIN_UAT__15.ndf',
  MOVE 'P16' TO 'e:\SQLRestore\MARTIN_UAT__16.ndf',
  MOVE 'P17' TO 'e:\SQLRestore\MARTIN_UAT__17.ndf',
  MOVE 'P18' TO 'e:\SQLRestore\MARTIN_UAT__18.ndf',
  MOVE 'P19' TO 'e:\SQLRestore\MARTIN_UAT__19.ndf',
  MOVE 'P20' TO 'e:\SQLRestore\MARTIN_UAT__20.ndf',
  MOVE 'CMTS_log' TO 'e:\SQLRestore\MARTIN_UAT__21.ldf'
  ,REPLACE

I would suggest automating generation of the restore script. There are a few versions of this out there but Paul Brewer has a T-SQL and PowerShell version available.

I have seen some setups as well that just include generating a restore script when the backup is accomplished.

Which, just a note that a new open source backup solution was released by Sean and Jen McCown called Minion Backup. It includes the functionality of generating your restore scripts and migrating backups to development.


PostgreSQL force upper case for all data Is there a way to force all text data to uppercase without recurring to writing a function for every table or doing it client side?

Forcing correct values is one thing. Simple CHECK constraints per column can do the job reliably:

CREATE TABLE foo
   foo_id serial PRIMARY KEY
 , text_column text CHECK (upper(text_column) = text_column)
 , ...
);

Auto-correcting all input is another thing, and not as simple. But it can be done with a generic trigger function that looks up column names and data types in the system catalog and converts all character data to upper case. Safe enough, but not quite as fast and not as bullet-proof because more complex and triggers can more easily be circumvented or counteracted by other triggers.

One generic trigger function for all:

CREATE OR REPLACE FUNCTION trg_all_upper()
  RETURNS trigger AS
$BODY$
DECLARE
   -- basic character types. possibly add citext, domains or custom types
   _typ   CONSTANT regtype[] := '{text, bpchar, varchar, \"char\"}';
   _sql   text;
   _found bool;
BEGIN
   SELECT INTO _sql, _found
          'SELECT ' || string_agg(
              CASE WHEN a.atttypid = ANY(_typ)
              THEN format ('upper(%1$s)::%2$s AS %1$s'
                         , a.col, a.atttypid::regtype)
              ELSE col END
            , ', ') || ' FROM (SELECT ($1).*) t'
        , bool_or(a.atttypid = ANY(_typ))
   FROM  (
      SELECT a.atttypid, quote_ident(attname) AS col
      FROM   pg_attribute a
      WHERE  a.attrelid = TG_RELID  -- object ID of table that fired trigger
      AND    a.attnum >= 1          -- exclude tableoid & friends
      AND    NOT a.attisdropped     -- exclude dropped columns
      ORDER  BY a.attnum
      ) a;

   -- RAISE NOTICE '%', _sql;
   IF _found THEN
      EXECUTE _sql USING NEW INTO NEW;
   END IF;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql VOLATILE;

A trigger per table:

CREATE TRIGGER all_upper_bef_insupd
BEFORE INSERT OR UPDATE ON big
FOR EACH ROW EXECUTE PROCEDURE trg_all_upper();

Converts all values in columns with a character data type as defined in  _typ to upper case.
Tested in Postgres 9.4.

Related:

Create Alias for PostgreSQL Table
Replace empty strings with null values
How to set value of composite variable field using dynamic SQL


Updating a table efficiently using JOIN I have a table that has the details of households and another that has the details of all the persons associated with the households. For the household table I have a primary key defined using two columns in it - [tempId,n]. For the person table I have a primary key defined using 3 of its columns [tempId,n,sporder]

Using the sorting dictated by the clustered indexing on primary keys, I have generated a unique ID for each household [HHID] and each person [PERID] record (the snippet below is for generating PERID]:

 ALTER TABLE dbo.persons
 ADD PERID INT IDENTITY
 CONSTRAINT [UQ dbo.persons HHID] UNIQUE;

Now, my next step is to associate each person with the corresponding households ie; map a [PERID] to a [HHID]. The crosswalk between the two tables is based on the two columns[tempId,n]. For this I have the following inner join statement.

UPDATE t1
  SET t1.HHID = t2.HHID
  FROM dbo.persons AS t1
  INNER JOIN dbo.households AS t2
  ON t1.tempId = t2.tempId AND t1.n = t2.n;

I have a total of 1928783 household records and 5239842 person records. The execution time is currently very high.

Now, my questions:

Is it possible to optimize this query further? More generally, what
are the thumb rules for optimizing a join query?
Is there another query construct that can achieve the result I want with better
execution time?

I have uploaded the execution plan generated by SQL Server 2008 for the whole script to SQLPerformance.com

I'm pretty certain the table definitions are close to this:

CREATE TABLE dbo.households
(
    tempId  integer NOT NULL,
    n       integer NOT NULL,
    HHID    integer IDENTITY NOT NULL,

    CONSTRAINT [UQ dbo.households HHID]
        UNIQUE NONCLUSTERED (HHID),

    CONSTRAINT [PK dbo.households tempId, n]
    PRIMARY KEY CLUSTERED (tempId, n)
);

CREATE TABLE dbo.persons
(
    tempId  integer NOT NULL,
    sporder integer NOT NULL,
    n       integer NOT NULL,
    PERID   integer IDENTITY NOT NULL,
    HHID    integer NOT NULL,

    CONSTRAINT [UQ dbo.persons HHID]
        UNIQUE NONCLUSTERED (PERID),

    CONSTRAINT [PK dbo.persons tempId, n, sporder]
        PRIMARY KEY CLUSTERED (tempId, n, sporder)
);

I don't have statistics for these tables or your data, but the following will at least set the table cardinality correct (the page counts are a guess):

UPDATE STATISTICS dbo.persons
WITH
    ROWCOUNT = 5239842,
    PAGECOUNT = 100000;

UPDATE STATISTICS dbo.households
WITH
    ROWCOUNT = 1928783,
    PAGECOUNT = 25000;

Query Plan Analysis

The query you have now is:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n;

This generates the rather inefficient plan:

The main problems in this plan are the hash join and sort. Both require a memory grant (the hash join needs to build a hash table, and the sort needs room to store the rows while sorting progresses). Plan Explorer shows this query was granted 765 MB:

This is quite a lot of server memory to dedicate to one query! More to the point, this memory grant is fixed before execution starts based on row count and size estimates.

If the memory turns out to be insufficient at execution time, at least some data for the hash and/or sort will be written to physical tempdb disk. This is known as a 'spill' and it can be a very slow operation. You can trace these spills (in SQL Server 2008) using the Profiler events Hash Warnings and Sort Warnings.

The estimate for the hash table's build input is very good:

The estimate for the sort input is less accurate:

You would have to use Profiler to check, but I suspect the sort will spill to tempdb in this case. It is also possible that the hash table spills too, but that is less clear-cut.

Note that the memory reserved for this query is split between the hash table and sort, because they run concurrently. The Memory Fractions plan property shows the relative amount of the memory grant expected to be used by each operation.

Why Sort and Hash?

The sort is introduced by the query optimizer to ensure that rows arrive at the Clustered Index Update operator in clustered key order. This promotes sequential access to the table, which is often much more efficient than random access.

The hash join is a less obvious choice, because it's inputs are similar sizes (to a first approximation, anyway). Hash join is best where one input (the one that builds the hash table) is relatively small.

In this case, the optimizer's costing model determines that hash join is the cheaper of the three options (hash, merge, nested loops).

Improving Performance

The cost model does not always get it right. It tends to over-estimate the cost of parallel merge join, especially as the number of threads increases. We can force a merge join with a query hint:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n
OPTION (MERGE JOIN);

This produces a plan that does not require as much memory (because merge join does not need a hash table):

The problematic sort is still there, because merge join only preserves the order of its join keys (tempId, n) but the clustered keys are (tempId, n, sporder). You may find the merge join plan performs no better than the hash join plan.

Nested Loops Join

We can also try a nested loops join:

UPDATE P
SET HHID = H.HHID
FROM dbo.households AS H
JOIN dbo.persons AS P
    ON P.tempId = H.tempId
    AND P.n = H.n
OPTION (LOOP JOIN);

The plan for this query is:

This query plan is considered the worst by the optimizer's costing model, but it does have some very desirable features. First, nested loops join does not require a memory grant. Second, it can preserve the key order from the Persons table so that an explicit sort is not needed. You may find this plan performs relatively well, perhaps even good enough.

Parallel Nested Loops

The big drawback with the nested loops plan is that it runs on a single thread. It is likely this query benefits from parallelism, but the optimizer decides there is no advantage in doing that here. This is not necessarily correct either. Unfortunately, there is no built-in query hint to get a parallel plan, but there is an undocumented way:

UPDATE t1
  SET t1.HHID = t2.HHID
  FROM dbo.persons AS t1
  INNER JOIN dbo.households AS t2
  ON t1.tempId = t2.tempId AND t1.n = t2.n
OPTION (LOOP JOIN, QUERYTRACEON 8649);

Enabling trace flag 8649 with the QUERYTRACEON hint produces this plan:

Now we have a plan that avoids the sort, requires no extra memory for the join, and uses parallelism effectively. You should find this query performs much better than the alternatives.

More information on parallelism in my article Forcing a Parallel Query Execution Plan:


Can I export a MySQL schema to latex? I have asked this question on SO before, but did not get a good answer.

I would like to be able to export a MySQL schema to a set of Latex tables.

phpMyAdmin does this, but it is buggy and does not export table comments in LaTeX.

Are there other linux-compatable, open-source options?

EMS Data Export for MySQL

When I search "MySQL Export Latex" dozens of links leads to this product.


MySQL: records, inserted via phpMyAdmin, do not replicate As it says on the topic, for some reason records inserted/ modified via PMA do not get replicated to the slave, If I do exactly the same insert/modification on master via mysql command line, or application, it does get replicated as it should be.

Is it some security feature ? Or something wrong ? I use MySQL 5.5.28 with CentOS 6 mostly.

Any ideas why ?

I do not use phpMyAdmin so maybe i'm out but take a look on a potential autocommit option in the UI... Maybe your PMA's session isn't in autocommit (test with : SHOW VARIABLES LIKE  "autocommit").

Other thing, do you have filtered replication ? (do-replicate, do-ignore...)

Max.

Best regards.


Select those entries where the relationship of the columns f1:f2:f3 is roughly number1:number2:number3 I have three columns f1, f2 and f3

f1  f2  f3
1   3   5
5   7   3
...

My goal is to select those entries where the relationship of f1:f2:f3 is roughly(+-2) number1:number2:number3

Example:

SELECT those rows where the relationship is roughly 5:7:4 -> Result 5 7 3.

What does the SELECT command look like?

Maybe something like ...

-- test table and data

CREATE TABLE `t` (
    `f1`    INTEGER,
    `f2`    INTEGER,
    `f3`    INTEGER
);

insert into t ( f1, f2, f3 )
values (1,3,5),(5,7,3);

select * from t;

f1  f2  f3
1   3   5
5   7   3

Query:

   -- roughly 5:7:4 :: +/-1

    select * from t
    where (
          ( f1 between 4 and 6 )
      and ( f2 between 6 and 8 )
      and ( f3 between 2 and 4 )
    ) ;

    -- result
    f1  f2  f3
    5   7   3

Dbfiddle

--  roughly 5:7:4 :: +2

select * from t
where (
      ( f1 between 5 and 7 )
  and ( f2 between 7 and 9 )
  and ( f3 between 4 and 6 )
) ;

-- no rows returned


Impact of shrinking transaction log file after transaction log backup in SQL Server In SQL Server, is there any performance issue if we shrink log file after taking the transaction log backup.
If yes, then please explain the reason.

Yes there may be performance issues if you continuously shrink the log files. When autogrowth event is triggered so that log file can grow for the database requirement all activities in database freeze to honor the growth request once this is acknowledged and done the processes resumes. Now if you daily shrink your log file it would grow frequently because you would run transactions and this frequent growth would frequently cause autogrowth events and thus frequent freezings. All this combined on a busy system can impact performance. This is why it is always advise to pre size your transaction log file to avoid as much less autogrowth as possible. Also note since Instant File Initialization is not there for log files the autogrowth would take some time as some more time would be spent on zeroing of the records.

Please read
Importance of proper transaction log size management By Paul Randal

PS: It does not matter when you shrink transaction log file it always has some negative affect. You may shrink the log file when some unwanted transaction has bloated the log file and there is a space crunch but never make it a habit. Think of shrink button as option provided to you to get  out of unavoidable circumstances/situations(like disk space issue, transaction log bloat) not for daily use.


Database existence not happened correctly I am having problems running a SQL script to CREATE a database on SQL Server 2014.

My script fails with the following error, however I am not sure how to resolve the issue as I am a beginner with SQL Server.

CREATE DATABASE [BIDS]
  CONTAINMENT = NONE ON PRIMARY
  ( NAME = N'BIDS',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\BIDS.mdf' ,
    SIZE = 4096KB ,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 1024KB
  )
  LOG ON
  ( NAME = N'BIDS_log',
    FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\BIDS_log.ldf' ,
    SIZE
    ---

What should I do to resolve this error?

  Msg 1803, Level 16, State 1, Line 4
  The CREATE DATABASE statement failed. The primary file must be at least 5 MB to accommodate a copy of the model database.

  Msg 5011, Level 14, State 5, Line 11
  User does not have permission to alter database 'BIDS', the database does not exist, or the database is not in a state that allows access checks.

Sounds like someone changed the defaults that exist in the model database on the server (512 is usually the default from what I've seen--not 4096). Since this is for a deployment on possibly any SQL Server (where model specifications could well be different) just remove the SIZE = 4096KB in both the mdf and ldf sections--to avoid this issue altogether. Each SQL Server will create the database based on specifications of the model database on SQL Server.

Example:

CREATE DATABASE [BIDS] CONTAINMENT = NONE
ON PRIMARY ( NAME = N'BIDS', FILENAME = N'C:\temp\BIDS.mdf' ,
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON ( NAME = N'BIDS_log', FILENAME = N'C:\temp\BIDS_log.ldf' ,
  MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB)


How to create a relationship between one table and multiple other entities (PLEASE NOTE: I am aware that in a few cases there are one-to-one relationships where one-to-many should be present. It's not intentional, it's just a mistake on my side. Take no notice.)

I am designing a database for my project and I've ran into a small problem.

In the database there will be a few entities that can be in a one-to-many relationship with tables: email and phone. Here they are:

Now, I have such entities as client_rep, other_party and third_party that I want to relate one-to-many to the aforementioned tables. Since there are multiple of those, I need to create an intermediate table, in the following fashion:

That's still not that bad, but when the third party comes into play, that's what I get:

Getting nastier and nastier, right? But when I'd add more tables and more relationships, this stuff's getting, for my taste, too hideous and hard to manage.

Now, one solution I could come up with, was to add an intermediary table, like an "entity". Then, it could look something like that:

Much, much nicer. But:

Now I make each of the the entities (client_rep, other_party, third_party) dependent on entity and I'm not sure that's good.
I create a seemingly useless table containing only and ID, nothing else.
This way I can't be sure that one value of  entity isn't connected to to multiple types of other entities (e.g. client_rep.entity_id could very likely be equal to other_party.entity_id and I have no way of knowing that, except for by issuing a query, ie. from inside of my application. Still, I guess I had the same problem in the first design, where client_rep_email.email_id could be equal to other_party_email.email_id.

Another way to solve this problem would be to create entity-specific tables for phone and email.

That looks ugly at least, but this way I can be sure, that no phone number and email will be referenced by two different entities. Still, there are a few very specific and important problems with this layout:

When I want to change the length of accepted phone numbers or email addresses, I will have to edit 3 times more tables. And imagine what if added one more entity?
It's simply not right, because the same object (e.g. email address) is represented multiple times.

There is another way of doing that. Let's take a look at the client_rep table. It's obvious, that in reality it looks like this:

I could, of course, create a specific rep for each entity, like that:

This layout has other problems, like the others.

No way of being sure that client_rep.rep_id is not equal to other_party_rep.rep_id.
I have to create a entity-specific rep (ie. client_rep, other_party_rep, third_party_rep.

But there's a slight, which is that I can enter entity's rep-specific values. Still, in case of some entities (e.g. other_party), there won't ever be a rep per se. There will be only general contact information.

And I'm lost. There are to many solutions available and I can't choose the best one (although I can identify a few better than others).

I faced this same problem 4 or so years ago here https://stackoverflow.com/questions/3636061/database-design-similar-contact-information-for-multiple-entities.

In the end I went with my method one which did involve many more joins etc during the life of the project but I always liked the fact that contact information only had to be stored in one place, once in the database.

Another way to look at this is to maybe see if you can reduce the number of other tables. Client_rep, other_party, third_party all seem like types of users. have you considered a super type - sub type relationship. For example dbo.user is the SuperType with client_rep, other_party and third_party related as the subtype. You would then only need two associative tables dbo.phoneUser and dbo.emailuser between dbo.phone, dbo.email and dbo.user


Trigger for multiplying two columns from different tables with a functions in mysql I have been endelessly trying to get a trigger to work, with many different variations and all ending in errors.

I have finally just decided to ask for help, since im still a beginner at this.

So say I have a many-to-many table that contains pk's of o.orderId and p.productId, and within this table I have two columns called amount and totalPrice

I have then created a function, that returns the price of products based on their productiD.

CREATE FUNCTION func_getProductPrice($pId varchar(45)) RETURNS int(11)
    DETERMINISTIC
BEGIN
DECLARE origPrice int(11);
SET origPrice = (select o.productPrice from (productId_has_orderId
inner join productId on productId = $pId);
RETURN origPrice;
END

And a before insert trigger that is meant to call on this function to get the price, multiply the returned value based on new entry and then update the price field of this new entry.

delimiter |
CREATE TRIGGER `vet`.`calcProdutctPrice` before INSERT ON `productId_has_orderId`
FOR EACH ROW
IF NEW.amount <> 1 THEN
BEGIN
DECLARE newPrice int(11);
set newPrice = ((SELECT SUM((new.amount) * (select func_getProductprice(new.p.productId))) AS total from productId_has_orderId));
    begin
        update productId_has_orderId
        set price = newPrice
        where orderId = new.orderId;
        end;
    END;
end if
|
delimiter ;

Which gives this error when inserting data

Cant't update table 'productId_has_orderId' in stored function/trigger because it is already used by statement which invoked this stored function/trigger

An another variation of the trigger which essentially works the same, but the price is not a declared variable and is instead just updated to the new price

delimiter |
CREATE TRIGGER `vet`.`calcProdutctPrice` before INSERT ON `productId_has_orderId`
FOR EACH ROW
IF NEW.amount <> 1 THEN
BEGIN
        update productId_has_orderId
        set price = ((SELECT SUM((new.amount) * (select func_getProductprice(new.p.productId))) AS total from productId_has_orderId));
        where orderId = new.orderId;
    END;
end if
|
delimiter ;

Which then instead gives this error when inserting values

insert into productId_has_orderId (orderId, productId, amount) values (5100002, 3100002, 3)

Error Code: 1093. You can't specify target table 'productId_has_orderId' for update in FROM clause 0.000 sec

I am not sure what else I can do by now, I was thinking about creating a trigger calling a stored procedure, but I don't know if I can pass values from a trigger to a procedure.

Any help is appreciated, thank you

Assume we have (schematically):

Products table

CREATE TABLE products (productID INT,
                       price DECIMAL);

Orders table

CREATE TABLE orders (orderID INT,
                     total_price DECIMAL);

Joining table

CREATE TABLE joining (productID INT,
                      orderID INT,
                      amount DECIMAL,
                      separate_price DECIMAL);

There is a lot of records in products table. We form new order and add new record into orders table. Now we begin to insert records into joining table, and we want to calculate both separate_price (for each separate product) and total (for whole order).

The trigger for to calculate separate price will be:

CREATE TRIGGER calculate_separate
BEFORE INSERT
ON joining
FOR EACH ROW
SET NEW.separate_price := (SELECT NEW.amount * products.price
                           FROM products
                           WHERE products.productID = NEW.productID);

The trigger for to calculate total price will be:

CREATE TRIGGER calculate_separate
AFTER INSERT
ON joining
FOR EACH ROW
UPDATE orders
    SET total_price = total_price + NEW.separate_price
    WHERE orderID = NEW.orderID;

If there is no separate_price field in joining table (and there is no trigger which calculates the value for that field) we will use

CREATE TRIGGER calculate_separate
AFTER INSERT
ON joining
FOR EACH ROW
UPDATE orders
    SET total_price = total_price + (SELECT NEW.amount * products.price
                                     FROM products
                                     WHERE products.productID = NEW.productID)
    WHERE orderID = NEW.orderID;

Pay attention: we calculate separate value (which is stored in the same table, in the record which creation fires a trigger) in BEFORE trigger (so the calculated value then will be inserted), whereas we re-calculate total value in AFTER trigger (and update a record in another table, and only in case when new record successfully inserted).

PS. Function mentioned in a question is not used - we do not need in it. We can use it, of course, but its usage is excess.

PPS. Each trigger above contains one single statement. So we do not need in BEGIN..END blocks ade DELIMITER reassign.


Can't understand results of pg_stat_statements view I configured my postgresql.conf (my version is 9.1) with the normal persistent configuration:

shared_preload_libraries = 'pg_stat_statements,plperl'
custom_variable_classes = 'pg_stat_statements,plperl'
pg_stat_statements.max = 100
pg_stat_statements.track = all
pg_stat_statements.save = on

I let one month to run within a database which is constantly accessed with read/write queries.

After a month (or generally enough time to be sure lots of queries were executed) I do the documentation query with top five slowest queries, and get this result:

-[ RECORD 1 ]---------------------------------------------------------------
query       | UPDATE XXXXXXXX
calls       | 63486
total_time  | 238.271792999997
rows        | 40740
hit_percent | 99.9938060778852878
-[ RECORD 2 ]---------------------------------------------------------------
query       | SELECT XXXXXXXX
calls       | 67347
total_time  | 0.852444000000713
rows        | 67347
hit_percent | 100.0000000000000

... and so on. I see the real number of calls as total_time is (very) much bigger for this queries than this view says. I don't understand how this works, because I expected hours in the total_time field as my server is running for more than 3 months without restarting.

In general is a no-change-from-doc configuration, and I get nonsense results.

I quote the manual (at the page you are linking to, just for the appropriate  version 9.1)

  total_time ... Total time spent in the statement, in seconds

67347 calls totaling 0.852 seconds. That's 0.012 ms per call.
Not uncommon for a simple SELECT.

If that cannot explain it, consider this quote from the manual:

  Note that statements are considered the same if they have the same
  text, regardless of the values of any out-of-line parameters used in
  the statement. Using out-of-line parameters will help to group
  statements together and may make the statistics more useful.

We'd have to study your exact statement(s) to assess that.

There is also the function pg_stat_statements_reset() to reset statistics (superusers only). Are you sure it wasn't called?

And there are a number of configuration parameters to regulate what is tracked. Any fancy settings there?


Second instance of query designer for a single table in SSMS (Express)? Forgive me if this is the wrong exchange site for this question. It seems like the right one.

I have been a fan of writing queries manually for a long time, but recently I've got into the habit of using the query designer in Management Studio for one reason - it allows editing of data in-place.  (There's probably a way of doing this in the manual query window but I haven't found out yet)

Anyway,  today I wanted to create a new instance of it for a table, and preserve the one I already had open.  It wouldn't let me.  When I clicked 'open table' on the object explorer it just took me to the window I already had open.  This meant that if I want to query the data in a different way I have to abandon the query I had set up.

Is there a way of getting a second (or more) query designer window for the same table?

It occurs to me that I can just open a second instance of the entire app (SSMS) but that seems overkill for what must be a simple/common need.

I don't believe there is a way to do this. SSMS seems to prevent you from opening more than one instance, and the only workaround I can think of is not to kick SSMS but rather just open a new instance of the entire application (as you've already guessed as well).

In any case, I strongly recommend you revert to your previous behavior - the query designer is full of bugs and your "edit data in place" feature actually means that the grid has to place unnecessary locks on the underlying table. On a concurrent system this can be disastrous - what kind of concurrency handling do you think is there? You should try to open two instances of SSMS and try to edit the same row in each copy. Fun fun!

Even though it is slightly more work to write, using proper DML (update/insert/delete) in a proper query window is a much better approach to ad-hoc data modification IMHO. And I have long been advocating to stay away from the SSMS visual designers in all their various forms. The team tried but some of the faulty code has been in there since Query Analyzer and it will never be fixed.


Is it safe to delete "root" user? I have hosted one web application on a staging server where I have deleted "root" users from the database. I don't want to use a common name for superuser. I have also created another admin user(Same as root user).
After this change I have tested my application and did not find any issue. But I reviewed articles where people mentioned that they were getting errors after deleting the root user.
My queries are ,

Is it safe to delete the root database?
Will it create any issue in future if remove root user?

I am still doing testing on staging server. Once this poc get done , I am planning to delete the root user from the production server as well.
EDIT
This is a new web application which I am going to host on production server and there is no admin script running.

For administrative purposes, you need to have a SUPER user WITH GRANT OPTION, but the name ('root') does not matter.  But...

Are there any scripts that assume it is root?
Such a user should be granted access only from localhost, thereby adding the OS security on top of that login.
Do not give SUPER or WITH GRANT OPTION to any other user (without understanding the security remifications)
Each application should have its own login into MySQL.  That is, use root (or your replacement name) only for admin purposes, not for applications.
Each application login should limit what it can touch.  Commonly that is one database, hence GRANT ... ON dbname.* ..., not ON *.*.  (There are exceptions and variations on this rule.)


SQL Server choose at random from giving data My problem is that I don't know how to choose the random data from a giving set. For example: I have a Node column which need to produce different inserts but only from these numbers: 16, 17, 18, 22, 24. I have looked at many different posts but couldn't find what I'm looking for. Any tips, hints or very simple sample query would be much appreciate.

I'm using SQL Server Management Studio 2014

You mean how to pick a random row from the data? This is one way to do it:

declare @data table (
  value int
)

insert into @data values (16), (17), (18), (22), (24)

select top 1 value from @data order by newid()


How do I find out if Sql server Agent's login has rights to a DB? How do I find out if the SQL server Agent's login has rights to the target DB, that is the DB on which I will be executing a query ?

Also, how do I find the service that's controlling Agent on the job server and find out the login it's using ?

Is there a query for these things ?

Thanks.

Account

There is no documented way to find out what the current service account for the agent is, at least not before SQL Server 2008R2.

As Jon commented below, in SQL Server 2008R2 SP1 and later you can use this query:

SELECT * FROM sys.dm_server_services;

For versions prior to that you can try one of the following:

Aaron suggested a xp_regread solution here: https://stackoverflow.com/questions/7324407/get-service-account-details-of-the-sql-agent-service
But I currently can't get it to work, and even if you get it to work, there is no guarantee that it still works after the next SQL Server patch was installed.

The easiest would be to start the SQL Server Configuration Manager on the instance's server and look it up in there.

Alternatively you could use the SUSER_SNAME() function to do something like

SELECT SUSER_SNAME() AS LoginName
  INTO tempdb.dbo.AgentAccountTable;

Put this statement into a job, execute that job and then look up the value in that table:

SELECT * FROM tempdb.dbo.AgentAccountTable;

Permissions

As for the permissions: The Agent Server Account must be a member of the sysadmin fixed server role (http://technet.microsoft.com/en-us/library/ms191543.aspx). Therefore it will have access to any database.


Is it worth it to create an "Id" column? I have a table which has a column Name, which is of type VARCHAR2(20), unique, cannot be null, and cannot be changed.

At first I used it as a Primary Key for this table but I was wondering if it was better to create another column Id (with a more appropriate naming of course) and handle table relations with it.

I know it's a common best practice to have an Id column but I've heard that clustering the DB with tons of meaningless Id columns is to be avoided (a Name column has more semantics attached to it).

What would you advise in this situation?

As name is unique and will never change, it is certainly a good candidate key from a relational theory point of view.

You might find using a integer surrogate key preferable for space and performance reasons though as it will take less space than the text in every table that has a foreign key to this table (and every index as FKs are usually indexed columns). Operations to search/join on an integer column will be faster too, though for queries that join in this table and need to output and/or sort by the name you might find the extra work going from id to name removes some of that benefit. Of course the space and performance differences may not be significant enough to make a difference to your project at which point this comes down to preference.

In fact I suspect even using a UUID might be faster than using name, assuming your DBMS has a proper UUID type so they are stored in compact binary form not as a text field, despite the type's fixed 16 byte length probably being longer than the average length of your name values, as comparisons against fixed length binary values are faster than those between variable length strings (it would be interesting to benchmark this and see if the difference is indeed significant) - though unless you have reason to use a UUID (replication issues or such) the smaller integer type will of course be more efficient and take a quarter of the space.

tl;dr: the name column as described is a perfectly good candidate key, perfect in theory in fact, but in practise an integer surrogate key will be more space and processing efficient.


Error when I run sp_BlitzCache specifying an output db, schema, and table I am using sp_BlitzCache version 4.1 (20161210) and executing it on a 2008R2 instance (10.5.4042).

If I execute the sp without the @OutputDatabaseName, @OutputSchemaName, and @OutputTableName, it works fine. However, when I set those values, it runs through its process and at the very end, and error appears:

  "The select list for the INSERT statement contains more items than the insert list. The number of SELECT values must match the number of INSERT columns."

The specific statement I am running is:

EXEC [dbo].[sp_BlitzCache] @OutputDatabaseName = 'Maintenance_Stats'
,@OutputSchemaName = 'dbo'
,@OutputTableName = 'BlitzCacheResults'

The database, schema, and table already exist, but has no records in it. So...somewhere in the sp code, it created the table, but is trying to stuff too many values into the table.

I am using this to try to troubleshoot an application (SCCM) hang issue, and as recommended, have created a SQL Agent job that the helpdesk can execute when they encounter the issue (it is intermittent), but I tried executing it myself, and it failed with the above error.

The comments are right, but I figured I'd chime in. That is fixed in the latest release

Full disclosure: I am owned and operated by Brent Ozar Unlimited, and contributed both the bug and the bug fix to this code.

On my birthday.


What is the use of creating full text catalog in full text indexing Just want to know about the processes happening in full text indexing. I know by defining the full text indexing for a table, it will index all the words saved in that table.

But i didn't get the use of creating a catalog for a full text index. And how an index related to catalog. Simply what the full text catalog will do in full text indexing.

From BOL:

After columns have been added to a full-text index, users and applications can run full-text queries on the text in the columns. These queries can search for any of the following:

One or more specific words or phrases (simple term)
A word or a phrase where the words begin with specified text (prefix
term)
Inflectional forms of a specific word (generation term)
A word or phrase close to another word or phrase (proximity term)
Synonymous forms of a specific word (thesaurus)
Words or phrases using weighted values (weighted term)

A full-text catalog is a logical concept that refers to a group of full-text indexes. Catalogs makes it easier for maintaining full text indexes.


Restore Database wizard won't open in SSMS 2014 On a dev machine running Windows Server 2012 R2, I just upgraded SQL Server from 2012 to 2014.

Using SQL Server Management Studio 2014, when I right-click a database and go to Tasks -> Restore -> Database... nothing happens. The Restore Database wizard does not appear as expected.

No errors or crashes are in Event Viewer. The cursor doesn't even turn into an hourglass.

Using T-SQL to restore works fine, and if I go back and use SSMS 2012 it works fine. Any ideas on how to get the wizard to work in SSMS 2014?

I am connecting to the same instance of SQL Server in both versions of SSMS, and I have tried running SSMS as an administrator.

It is Service Pack 1:

Microsoft SQL Server Management Studio 12.0.4100.1
Microsoft Analysis Services Client Tools 12.0.4100.1
Microsoft Data Access Components (MDAC) 6.3.9600.17415
Microsoft MSXML 3.0 5.0 6.0
Microsoft Internet Explorer 9.11.9600.18125
Microsoft .NET Framework 4.0.30319.34209
Operating System 6.3.9600

I have seen that behavior myself in both SSMS 2012 and SSMS 2014. What it typically means is that the backup / restore history for the database is very long and it is taking a long time for the wizard to come up, because it needs to read the entire history.

Make sure you have a maintenance plan that truncates the history. To find out, run this query:

SELECT TOP 1 backup_start_date
FROM msdb.dbo.backupset WITH (NOLOCK)
ORDER BY backup_set_id ASC

That query will be quick, since it's sorted by the primary clustered key of the table. If you've got more than a month or two of history in there, it's probably time to clear it out. (Keep in mind that clearing the history will involve locks, so it can be a slow process if you're doing backups at the same time, or if your MSDB is on slow storage.)


Is it safe to move big table to another tablespace while working with it? I have one big bloated table (real data size approx 65 Gb, 140 Gb on disk). Vacuum does not work cause of low space on default tablespace disk. So I want to clean the database from trash by moving fat tables to another tablespace, vacuuming them and returning back.

Can I do it safely while not turning off services working with that table?  It is around 15 separate one row inserts  and ~7 select operations per minute.

And few more insignificant questions:

What manuals/guides or parameters settings would you advice for autovacuuming table, that works in such load.
Is it better to pass old data delete operations with frequency of inserts or delete big stacks of rows (every day,week, month,etc..)?

The table will be AccessExclusive locked for the duration of its move to a new tablespace, so your application will seize up during that time.

You haven't really described what your workload is, so it is hard to recommend settings to accommodate it.  Surely it wasn't 15 separate one row inserts and ~7 select operations per minute which lead to the existing bloat?

Generally it is best to delete rows once you become aware that they are eligible for deletion.  Creating a deletion-queue table is just another table which can get bloated, needs to be maintained, have deletions done on it itself, be vacuumed, have code written to accommodate it, etc.


SQL Server 2012 Enterprise: Copy Databases to New SQL Server, Same Domain, Users, Etc Am having trouble with what seemingly should be an easy process.  I'm a SQL Server n00b, so please be gentle and very explanatory with your answers.

Currently, I have a SQL Server 2012 cluster (running on top of Windows Server 2008R2), and have created a new SQL Server 2012 Enterprise server in the same AD domain that I want to move all my databases to so I can start using this new instance (its running all SSD drives for better performance with databases, logs, temp logs, etc, all on different 512GB SSD drives).  The new instance uses the same AD group as SQL administrators, so logins are the same.

I want to copy all the databases from the "old" SQLCluster to the new SQL instance (the new instance is standalone). I've tried the Copy Database Wizard, making sure firewalls are stopped on both instances (seems easiest to avoid conflicts), telling it to make a text log file at the root of C: drive in case anything goes wrong.  However, the wizard gets to the last step (running the job) and bombs out, but doesn't create a log file ... it just says the job failed, giving me little to go on.  Both instances have the SQL Agent running.

Can someone give me some guidance on the easiest way to do this while leaving the databases on the source cluster online?  Note that I also have hourly backups that run via R1Soft that make copies of the database and log files; I thought perhaps I could just dump the MDF and LDF files from this backup in a directory and re-attach them, but the file structure is different on the new SQL Server 2012 machine with log files and DB files on different drives, and I get an error there with that, and don't know how to change those paths to reattach the databases from the backup (they are straight images of the MDF/LDF files, NOT SQL Server backups).

Thanks for any help you can provide.

Run this on your current instance:

select
    'CREATE LOGIN ['+p.name+'] FROM WINDOWS;'
from sys.server_principals as p
where type in ('U','G')

Take it's output and run it on the new instance.  Those AD accounts will now have logins on both instances. (This is a good opportunity to audit your access and ensure all those users really are needed.)

Backup your databases (a proper SQL Server backup, not a copy of mdf and ldf files) and restore these to the new instance. You may have to get creative with the restores if the drive names have changed.  If there are a lot of DBs some RESTORE FILELISTONLY and a script will save much time.

Finally, on each DB, script an ALTER USER to reconcile LOGINS to USERS.  This can be meta-scripted as above.

Test connections to the new instance.  Update the applications' connection strings.  Take the old DBs offline and, after a few days, tidy away the old server.


Trigger firing despite no rows being affected This is more of a general question but the motivation for this question was a problem I faced while using SQL Server.

I have this trigger attached to an Insert event on a table that contains some logic that, as a side effect, would raise error if no rows were inserted. Upon further investigation, I discovered that the trigger was firing despite no rows being inserted.

The language used in the Microsoft Docs on DML Triggers seems to contradict this behavior:

  DML triggers is a special type of stored procedure that automatically takes effect when a DML event takes place that affects the table or view defined in the trigger.

Is this a default behavior across DBMSs? Is there a particular reason to fire a trigger when no rows are affected?

For DML actions, there are row based and statement based triggers.

Row triggers fire when (before, after or instead of) each row is affected (inserted/updated/deleted). So they will fire 100 times if 100 rows are affected and not at all if 0 rows are affected.
Statement triggers fire when an INSERT / UPDATE / DELETE statement is executed. It doesn't matter if no rows are affected. Statement level triggers fire anyway and only once for a statement (whether it's 0, 100 or a billion rows affected).

Some DBMS have only row level triggers (MySQL).

Others (like SQL Server* which is your DBMS) have only statement level triggers.

Some others (DB2, Oracle, Postgres) have both kinds of triggers.

* SQL Server CREATE TRIGGER docs state:

  DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view. These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.


Aggregating rows from a condition in a linking table in SQL I am having trouble wrapping my head around a query that gives me these results
I have a table that gives me Devices uses per day.
dbo.DailyUses
Uses |   Date   |  Device
3    | 2020-3-1 |    A2
5    | 2020-3-1 |    B3
1    | 2020-3-2 |    A2
12   | 2020-3-3 |    C4

I have another table gives me the possible ErrorCodes of each devices.
dbo.Code_SKU_Combos
ErrorCode |  Device
   12.24  |    A2
   12.25  |    A2
   12.26  |    A2
   12.24  |    B3
   12.26  |    B3
   12.25  |    C4

With those two tables, I want to get how much uses per day did each "errorcode cable devices" where used
Example Output
Uses |   Date   |  ErrorCode
8    | 2020-3-1 |    12.24
3    | 2020-3-1 |    12.25
8    | 2020-3-1 |    12.26
5    | 2020-3-2 |    12.24
5    | 2020-3-2 |    12.25
5    | 2020-3-2 |    12.26
0    | 2020-3-3 |    12.24
12   | 2020-3-3 |    12.25
0    | 2020-3-3 |    12.26

I was thinking of using window functions or join, but the fact that the two tables will be using repeating values just confuses me on creating Partitions and Group By. I feel like I am over thinking it.
I created a cross table of my date range and Error Codes, now I just need to create a SUM() function that looks at dbo.DailyUses based on a filter created from dbo.Code_Sku_Combos
Also I am using Microsoft SQL

A possible solution can be :
The first select gets all combination of date and error combination that exist in the tables.
If  you actually want all possible Dates, you need to add another created table, with all possiible dates

CREATE TABLE Code_SKU_Combos
    ([ErrorCode] varchar(5), [Device] varchar(2))
;

INSERT INTO Code_SKU_Combos
    ([ErrorCode], [Device])
VALUES
    ('12.24', 'A2'),
    ('12.25', 'A2'),
    ('12.26', 'A2'),
    ('12.24', 'B3'),
    ('12.26', 'B3'),
    ('12.25', 'C4')
;
GO

CREATE TABLE DailyUses
    ([Uses] int, [Date] Date, [Device] varchar(2))
;

INSERT INTO DailyUses
    ([Uses], [Date], [Device])
VALUES
    (3, '2020-3-1', 'A2'),
    (5, '2020-3-1', 'B3'),
    (1, '2020-3-2', 'A2'),
    (12, '2020-3-3', 'C4')
;
GO

SELECT DISTINCT [Date],[ErrorCode] FROM DailyUses CROSS JOIN  Code_SKU_Combos
GO

Date       | ErrorCode
:--------- | :--------
2020-03-01 | 12.24
2020-03-01 | 12.25
2020-03-01 | 12.26
2020-03-02 | 12.24
2020-03-02 | 12.25
2020-03-02 | 12.26
2020-03-03 | 12.24
2020-03-03 | 12.25
2020-03-03 | 12.26

SELECT ISNULL (SUM([Uses]),0) AS Uses, DateComp as [Date],[errorcomp] AS ErrorCode
FROM (SELECT DISTINCT [Date] as DateComp,[ErrorCode] AS errorcomp FROM DailyUses CROSS JOIN  Code_SKU_Combos) t1
LEFT JOIN (
DailyUses d INNER JOIN Code_SKU_Combos c ON d.[Device] = c.[Device]) ON t1.DateComp = d.[Date]
AND  t1.errorcomp = c.[ErrorCode]

GROUP BY [DateComp],[errorcomp]
ORDER BY [DateComp],[errorcomp]
GO

Uses | Date       | ErrorCode
---: | :--------- | :--------
   8 | 2020-03-01 | 12.24
   3 | 2020-03-01 | 12.25
   8 | 2020-03-01 | 12.26
   1 | 2020-03-02 | 12.24
   1 | 2020-03-02 | 12.25
   1 | 2020-03-02 | 12.26
   0 | 2020-03-03 | 12.24
  12 | 2020-03-03 | 12.25
   0 | 2020-03-03 | 12.26

db<>fiddle here


Why does Oracle yield multiple levels per row in this hierarchical query? I'm trying to reproduce the first simple example from this tutorial.

However, I don't understand why I get so many rows back. I would expect that 1 reports to 2 only at level 1 and not at level 2 as well.

SQL> select employee_id, reports_to from employees;

EMPLOYEE_ID REPORTS_TO
----------- ----------
      1      2
      2
      3      2
      4      2
      5      2
      6      5
      7      5
      8      2
      9      5

9 rows selected.

SQL> select employee_id, reports_to, LEVEL from employees connect by prior employee_id = reports_to;

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      1      2      1
      8      2      1
      5      2      1
      6      5      2
      9      5      2
      7      5      2
      4      2      1
      3      2      1
      6      5      1
      9      5      1
      7      5      1

EMPLOYEE_ID REPORTS_TO      LEVEL
----------- ---------- ----------
      2         1
      1      2      2
      8      2      2
      5      2      2
      6      5      3
      9      5      3
      7      5      3
      4      2      2
      3      2      2

20 rows selected.

You're getting many rows because without START WITH it outputs all possible hierarchies .  You see 1 reports 2 on level 1 (1->2), and another one on level 2 (1->2->NULL).   Adding  SYS_CONNECT_BY_PATH(reports_to, '/') "Path" to select will help in visualizing that.
Let's connect just 2 rows for illustration:

with test1 as
(
select 1 as employee_id, 2 as reports_to from dual
union all
select 2 as employee_id, NULL as reports_to from dual

)
select employee_id,reports_to ,level ,SYS_CONNECT_BY_PATH(NVL(to_char(reports_to),'NULL'), '/') "Path"

from test1
--start with employee_id=2
connect by prior employee_id  =reports_to;

will output :

Employee    ReportTo   Level Path
1              2        1     /2
2              <null>   1     /NULL
1              2        2     /NULL/2

If we uncomment "start with" , the result will be

2   <null>  1   /NULL
1      2    2   /NULL/2


Subscription database ER model design I'm designing a database for a review site.

There are products, and then for each product there can be multiple reviews.

I'm trying to add a subscription system, so that a user can subscribe to reviews and see the new ones when they login to the system.

This is an simple ER diagram with only the part of product and the review, I'm having doubts about how to do the subscription part.

How would you do it?

My thoughts are that first I have to save what the user subscribes to, something like this:

Would you do this in relation to the product or the reviewproduct? Also this is a many-to-many, so I would decompose in an associative entity.

Now my biggest doubt, I need to save the new reviews for the products that the user subscribed, how would you do this in the ER?

Thanks for the help,
Br,
Tiago

First, if the idea is that users subscribe to products, so that they can hear about new reviews on that product, then you definitely want the (many-to-many) relation to be between user and product, not user and reviewproduct. You're not subscribing to a review, you're subscribing to a product.

Regarding how do you track the new reviews: My first thought would be to keep a timestamp on the user-product subscription entity. When you want to know if there are new reviews for a product, you just query for all reviews created after the timestamp, and then you update the timestamp (or you update it whenever the user views them, or whatever your requirements are exactly).

(Side note: Not sure why you're calling reviews “reviewproduct”. I'd just call it “review”, or if you need to differentiate it from some other kind of review, maybe “productreview”.)


How to get the value of column that the same for few rows by selecting values from other columns? Since I am not a MySQL expert, I am having trouble determining how to ask a search engine for advice on this topic.

I have the following table:

+------------+--------------+-------------------+
| product_id | attribute_id | text              |
+------------+--------------+-------------------+
|       1770 |           57 | wi-fi             |
|       1770 |           58 | bluetooth         |
+------------+--------------+-------------------+

There are many other products with many attributes.

I need to select product_id that has text = wi-fi and text = bluetooth (in different rows). The table name is product_attribute.

I have tried the following:

SELECT *
FROM product_attribute
WHERE product_attribute.text = 'wi-fi'
    AND product_attribute.text = 'bluetooth';

However no results are returned since in one row we have text = wi-fi and another row has text = bluetooth, whereas my query is looking for both wi-fi and bluetooth in the same row.

How can I get product_id = 1770 by making the query select from the text column in different rows with the same product_id?

Here is SQL Fiddle.

This may work:

SELECT product_attribute.product_id
FROM product_attribute
WHERE product_attribute.text = 'wi-fi'
    OR product_attribute.text = 'bluetooth'
GROUP BY product_attribute.product_id
HAVING COUNT(*) = 2;

The idea here is to use an OR in the WHERE clause to allow rows that have either wi-fi or bluetooth in the text column, then we only return products that have at least two rows.

Thanks to @jkavalik for creating a test sqlfiddle that works, and proves the above code!


How to link data from a table to another table in sqlite database? I need to link data from a table to another table. I'm not sure how to do this. I have never link a table to another table. Anyone can help me? Or maybe can give me examples or sample codes? Thanks =)

Here is an idea that seems to work. Generally speaking though, you can do this like this:

Table1
------
table_key
data_type1
data_type2

Table2
------
table_key
table_key_in_table1 (linked to table1)
data_type1
data_type2
...

And so on.


Why am I getting "Invalid object name 'sys.sysobjvalues'." When I run

SELECT OBJECT_DEFINITION(OBJECT_ID('sys.assembly_files'));

I can see that there is a self-join on sys.sysobjvalues, but when I try to select that table I get,

Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.sysobjvalues'.

What's going on here? Why is that object name invalid?

This is one of the tables that can only be accessed over DAC, from this blog

  They had been moved to the imageval column of sys.sysobjvalues, which can only be accessed when using the Dedicated Admin Connection (DAC).

Prolly not a good idea, but for proof it'll work connect to DAC (-S admin:localhost on Linux)

sqlcmd -S admin:localhost -U sa -q "SELECT * FROM sys.sysobjvalues;" | less -S


PostgreSQL Linux PAM Authentication Error I'm using PSQL version 12.5 on Ubuntu 20.04.1. My pg_hba.conf file looks like this:
# DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database superuser can access the database using some other method.
# Noninteractive access to all databases is required during automatic
# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# PAM Authentication
host    all             all             0.0.0.0/0               pam
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I create a USER\Role with:
CREATE USER test WITH LOGIN;

Where test is a Linux user and is working fine. But when I run:
psql -U test

I get the error:
postgres@ubuntu2004:~$ psql -U test
psql: error: FATAL:  Peer authentication failed for user "test"

Am I missing something?

Since you didn't specify the -h option, PostgreSQL connects via local Unix socket. This is governed by the local entry in pg_hba.conf. Hence you are authenticated with "peer" authentication. But since you are not operating system user test, peer authentication fails.
To get PAM authentication, connect with -h 127.0.0.1.


using regexp substitution part in postgres string functions I hit this question trying to answer one in SO. When I pass \1 as first argument to lpad(string text, length int [, fill text]) function, it looks like it interprets \1 as regexp substitution part looking for string to pad chars, but then interprets same \1 as text (it is literally - '\1') calculating its length to substract from second argument: lpad('aaa',5,'b') calculates 5-length('\1'), not 5-length('aaa').

So the question is - are we meant to use SQL string functions with regexp substitution part meta syntax? What are the restrictions?
Eg: concat, format, repeat, substr understand it, while length, initcap, ascii interpret it as just text.

Now I see that length, initcap, ascii accept string as argument, while format, repeat accept text, but then ltrim(text... and lpad(text... seem to be mix - they "understand" regexp metasyntax to find position and then fail to "convert string to text" to count input length?..

Here an example:

t=# with a as (select 'a 2 b'::text s)
t-# select
t-#   regexp_replace(s,' (\d){1} ','')
t-# , regexp_replace(s,' (\d){1} ','\1') _regular
t-# , regexp_replace(s,' (\d){1} ',concat('.','\1','.')) concat_regular
t-# , regexp_replace(s,' (\d){1} ',length('.\1')::text) length_string
t-# , regexp_replace(s,' (\d){1} ',reverse('\1')) reverse_string
t-# , regexp_replace(s,' (\d){1} ',initcap('\1')) initcap_broken
t-# , initcap(regexp_replace(s,' (\d){1} ','\1')) initcap_orig
t-# , regexp_replace(s,' (\d){1} ',ltrim('\1','a')) ltrim_broken
t-# , regexp_replace(s,' (\d){1} ',lpad('\1',5,'.')) lpad_broken
t-# , regexp_replace(s,' (\d){1} ',ascii('\1')::text) ascii_broken
t-# , regexp_replace(s,' (\d){1} ',format('\1')::text) format_regular
t-# , regexp_replace(s,' (\d){1} ',repeat('\1',3)) repeat_regular
t-# from a
t-# ;
 regexp_replace | _regular | concat_regular | length_string | reverse_string | initcap_broken | initcap_orig | ltrim_broken | lpad_broken | ascii_broken | format_regular | repe
at_regular
----------------+----------+----------------+---------------+----------------+----------------+--------------+--------------+-------------+--------------+----------------+-----
-----------
 ab             | a2b      | a.2.b          | a3b           | a1\b           | a2b            | A2b          | a2b          | a...2b      | a92b         | a2b            | a222
b
(1 row)

Time: 0.420 ms


How do I handle multiple rows with variables defined by a query? I am trying to write a query that uses variables to feed into a sub query.

Query:

DECLARE @Code VARCHAR(5)
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

SET @Code = (SELECT Code
FROM LOOKUP.Table1
WHERE Key = @Key)

SET @Sys = (SELECT DISTINCT System
FROM LOOKUP.Table1
WHERE Key = @Key)

SELECT
@Code AS Code,
(SELECT DISTINCT
        COUNT(ID)
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H]
    WHERE [Type] NOT LIKE 'X%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS Total,

(SELECT
        COUNT(Type)
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H]
    WHERE [Type] LIKE 'Y%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS TypeA,

(SELECT
        COUNT(Type)
    FROM [LINKED-SERVER].[DB].[SCHEMA].[Table_H]
    WHERE [Type] LIKE 'N%'
        AND [Key] = @Key
        AND [Code] IN (@Code)
) AS TypeB

The problem comes when [Key] is used for multiple values of [Code]:

  Msg 512, Level 16, State 1, Line 7
  Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Some have as many as 5 values. I can't figure out how to make this query work when SET @Code would return more than 1 value.

It might help to change how you approach the problem from the very beginning. There are a lot of sub-queries, all hitting the same remote table, and all with mostly the same WHERE condition. Rather than thinking about the end result as a collection of separate pieces, think about it in terms of one big set of data, and that you want to tally up how many rows meet certain criteria as you move through each row once.

Assuming that the code in the Question wasn't overly over-simplified, you should be able to do the following, which uses a Table Variable to hold multiple values of "Code" per the given "Key". Once we have those captured, then we can simply JOIN that to the remote table. We JOIN on the criteria that is common to all of the subqueries, and then place the unique criteria within the IIF().

Also, I got rid of the DISTINCT on the @Sys lookup as it was only there due to the SELECT being in a subquery which isn't necessary. And while you could use TOP 1 rather than DISTINCT, there is no reason to force a sort given that it will be the same value that comes back each time, hence the process won't notice that @Sys is potentially being overwritten up to 5 times (with the exact same value). Of course, the @Sys variable is not being used in the original query, so maybe it can be removed entirely?

DECLARE @Codes TABLE (Code VARCHAR(5) NOT NULL);
DECLARE @Sys INT
DECLARE @Key INT

SET @Key = 12345

INSERT INTO @Codes (Code)
  SELECT Code
  FROM   LOOKUP.Table1
  WHERE  [Key] = @Key;

-- @Sys is not used in the final query. Should this be removed?
SELECT @Sys = [System] -- multiple rows may return,
FROM LOOKUP.Table1     -- but they all have the same value
WHERE [Key] = @Key;

SELECT cd.Code,
       SUM(IIF(th.[Type] NOT LIKE 'X%', 1, 0)) AS [Total],
       SUM(IIF(th.[Type] LIKE 'Y%', 1, 0)) AS [TypeA],
       SUM(IIF(th.[Type] LIKE 'N%', 1, 0)) AS [TypeB]
FROM   @Codes cd
INNER JOIN [LINKED-SERVER].[DB].[SCHEMA].[Table_H] th
        ON th.[Key] = @Key
       AND th.[Code] = cd.[Code]
GROUP BY th.[Code];