Select value in Left Join ordered by Date I am trying to LEFT JOIN a value to sort by the most recent date, but for some reason it is only returning a result for the last returned result.

These are my table layouts

cs_users

+---------+-------------+
| user_id | user_login  |
+---------+-------------+
|       1 | cornerstone |
|       2 | claire      |
|       3 | ben         |
+---------+-------------+

cs_login_log

+----------+---------------+-----------------+---------------------+--------------+
| login_id | login_user_id | login_user_type |      login_dtm      | login_status |
+----------+---------------+-----------------+---------------------+--------------+
|        2 |             1 |               1 | 2019-09-02 15:23:38 |            1 |
|        6 |             1 |               1 | 2019-09-02 15:49:20 |            1 |
|        8 |             2 |               1 | 2019-09-02 16:10:14 |            1 |
|        9 |             3 |               1 | 2019-09-02 16:12:14 |            1 |
|       10 |             5 |               1 | 2019-09-02 16:12:33 |            1 |
|       25 |             2 |               1 | 2019-09-15 19:18:07 |            1 |
+----------+---------------+-----------------+---------------------+--------------+

The code I've tried so far is as follows:

Attempt 1

SELECT u.user_id,
u.user_login,
l.login_dtm
FROM cs_users AS u
LEFT JOIN (
  SELECT * FROM cs_login_log WHERE login_user_type = '1' AND login_status = '1'
) l ON l.login_user_id = u.user_id
WHERE u.user_status = '1'
ORDER BY user_login ASC LIMIT 25

Attempt 2

SELECT u.user_id,
u.user_login,
l.login_dtm
FROM cs_users AS u
LEFT JOIN (
  SELECT login_user_id, login_dtm, MAX(login_dtm) FROM cs_login_log WHERE login_user_type = '1' AND login_status = '1'
) l ON l.login_user_id = u.user_id
WHERE u.user_status = '1'
ORDER BY user_login ASC LIMIT 25

But for some reason in all of the results it only returns the most recent login of the last row!

Returned data

+---------+-------------+---------------------+
| user_id | user_login  |      login_dtm      |
+---------+-------------+---------------------+
|       3 | ben         | null                |
|       2 | claire      | null                |
|       1 | cornerstone | 2019-09-02 15:23:38 |
+---------+-------------+---------------------+

I'm only new to this kind of JOIN so no idea if I'm even anywhere near being close? Basically I want to be able to get a list of my users and their most recent successful login timestamp.

You're on the right track - except that you need a RIGHT JOIN and not a LEFT one - that's IF you want to include the person with the login_user_id of 5 in your results. If not, a LEFT JOIN will indeed work.

Firstly, the RIGHT JOIN. You're either looking for this (works on version 5.6 - see fiddle here):

SELECT
  tab.login_user_id AS id,
  COALESCE(cs.user_login, '---------') AS name,
  COALESCE(cs.user_id, 0) AS lid,
  tab.dtm AS tme
FROM
  cs_users cs
RIGHT JOIN
(
  SELECT login_user_id, MAX(login_dtm) AS dtm
  FROM cs_login_log
  -- WHERE clauses as applicable
  GROUP BY login_user_id
) tab
ON cs.user_id = tab.login_user_id
-- WHERE clauses as applicable
ORDER BY tab.login_user_id;

or something like this which works for version 8 up (fiddle).

SELECT
  cs.user_id AS id,
  COALESCE(cs.user_login, '--------') AS name,
  tab.ltm AS tme,
  tab.rn
FROM
  cs_users cs
RIGHT JOIN
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY csl.login_user_id
                     ORDER BY csl.login_dtm DESC) AS rn,
    csl.login_user_id AS id,
    csl.login_dtm AS ltm
  FROM cs_login_log csl
  -- WHERE clauses as applicable
) tab
ON cs.user_id = tab.id
WHERE tab.rn = 1
-- AND whatever other clauses are applicable
ORDER BY tab.id;

This second query uses the (not strictly necessary in this case) ROW_NUMBER() Analytic (aka Window) function. These functions are very powerful and well worth getting to know and I would urge you to explore this fiddle and at least "play with" an instance of version 8 on your own personal laptop if you don't have it in work.

Also the COALESCE function is not strictly necessary here - but it can be a great help for the likes of reporting, making them much more aesthetically pleasing.

Result:

id  name        lid tme
1   cornerstone 1   2019-09-02 15:49:20
2   claire      2   2019-09-15 19:18:07
3   ben         3   2019-09-02 16:12:14
5   ---------   0   2019-09-02 16:12:33

You can use EXPLAIN EXTENDED to see which one works better for you - I have no running MySQL instances, only fiddles, so I can't do that sort of performance analysis. From version 8.20 there will be serious enhancements to this part of MySQL's functionality in this area - well worth an upgrade.

On the other hand, if you're only looking for the most recent login times for the three users in the cs_users table, then all you have to do is simply turn the RIGHT JOIN into a LEFT one (see the fiddle here).

SELECT
  tab.login_user_id AS id,
  COALESCE(cs.user_login, '---------') AS name,
  COALESCE(cs.user_id, 0) AS lid,
  tab.dtm AS tme
FROM
  cs_users cs
LEFT JOIN
(
  SELECT login_user_id, MAX(login_dtm) AS dtm
  FROM cs_login_log
  WHERE login_user_type = '1' AND login_status = '1'
  GROUP BY login_user_id
) tab
ON cs.user_id = tab.login_user_id
WHERE cs.user_status = 1  -- no data provided!
ORDER BY tab.login_user_id;

Result:

id  name    lid tme
1   cornerstone 1   2019-09-02 15:49:20
2   claire  2   2019-09-15 19:18:07
3   ben 3   2019-09-02 16:12:14

As noted in the SQL, I have no data provided for the cs_users field user_status (my working assumption is that it's 1 for everybody). The key to your understanding this problem is to realise that there is an ENORMOUS bug in all versions of MySQL prior to (AFAIK) 5.7 (I'm sure that all versions of 8 don't suffer from it - at least by default). See the above fiddle also.

There is a (super-)variable called sql_mode which is (more or less) one massive hack! There are various bits and pieces you can add or subtract from this variable - it's basically a long line of different sub-variables. One of these is ONLY_FULL_GROUP_BY which isn't on by default in version 5.6 - the one that you're using. This means that queries like this:

SELECT
  login_user_id,
  login_dtm,
  MAX(login_dtm)
FROM cs_login_log
WHERE login_user_type = '1'
  AND login_status = '1'

give a result with your data.

login_user_id   login_dtm                      MAX(login_dtm)
            1   2019-09-02 15:23:38 2019-09-15 19:18:07

Try running that query on PostgreSQL (a decent server!) and you'll get this (fiddle):

ERROR:  column "cs_login_log.login_user_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 2:   login_user_id,

So, the reason that your (second) SQL didn't work as you'd hoped was because your internal query only returned one data point and therefore you could only join to that one record and you got NULL for the rest - to be expected. When you GROUP BY the login_user_id (as @Akina also suggested) you get a result for all the users, which is what you want.

My advice is to add ONLY_FULL_GROUP_BY to the sql_mode of your server and you won't be caught by this again! It bit me in the ass when I was starting out on my SQL journey also! :-)

What I did was created and populated the tables:

CREATE TABLE cs_users
(
  user_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_login VARCHAR (20) NOT NULL
);

and

CREATE TABLE cs_login_log
(
  login_id INTEGER NOT NULL,
  login_user_id INTEGER NOT NULL,
  login_user_type INTEGER NOT NULL,
  login_dtm DATETIME NOT NULL,
  login_status INTEGER NOT NULL
);

populate:

INSERT INTO cs_users (user_login)
VALUES
('cornerstone'), ('claire'), ('ben');

and

INSERT INTO cs_login_log
VALUES
(        2 ,             1 ,               1 , '2019-09-02 15:23:38' ,            1 ),
(        6 ,             1 ,               1 , '2019-09-02 15:49:20' ,            1 ),
(        8 ,             2 ,               1 , '2019-09-02 16:10:14' ,            1 ),
(        9 ,             3 ,               1 , '2019-09-02 16:12:14' ,            1 ),
(       10 ,             5 ,               1 , '2019-09-02 16:12:33' ,            1 ),
(       25 ,             2 ,               1 , '2019-09-15 19:18:07' ,            1 );

Run the query (either one):

SELECT
  tab.login_user_id AS id,
  COALESCE(cs.user_login, '---------') AS name,
  COALESCE(cs.user_id, 0) AS lid,
  tab.dtm AS tme
FROM
  cs_users cs
RIGHT JOIN
(
  SELECT login_user_id, MAX(login_dtm) AS dtm
  FROM cs_login_log
  GROUP BY login_user_id
  -- WHERE clauses as applicable
) tab
ON cs.user_id = tab.login_user_id
-- WHERE clauses as applicable
ORDER BY tab.login_user_id;

Result:

id  name        lid tme
1   cornerstone 1   2019-09-02 15:49:20
2   claire      2   2019-09-15 19:18:07
3   ben         3   2019-09-02 16:12:14
5   ---------   0   2019-09-02 16:12:33

Note the name of '--------' and lid of 0 for user with login_user_id of 5 in the cs_login_log table. Of course, these placeholders are there because there is nobody with a user_id of 5 in the cs_users table.

In the second fiddle, I've left in the rn (ROW_NUMBER()) field - yet again, not strictly necessary, but it helps to show my train of thought as I constructed the query - obviously, delete as applicable!

Next time you ask a question, you might get better responses if you include your tables as DDL (CREATE TABLE foo (...);) and your table data as DML (INSERT INTO foo VALUES (...);), or you could construct a fiddle (just make sure to put the fiddle stuff here also) - help us to help you! There are some articles on how to ask questions here on my profile, you might want to take a look? p.s. welcome to the forum!


MySQL query not using an index when table contains many records I have a table:

CREATE TABLE `p` (
    `id` BIGINT(20) unsigned NOT NULL,
    `rtime` DATETIME NOT NULL,
    `d` INT(10) NOT NULL,
    `n` INT(10) NOT NULL,
    PRIMARY KEY (`rtime`,`id`,`d`) USING BTREE
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

A query that I run against this table is:

SELECT id, d, SUM(n)
FROM p
WHERE rtime BETWEEN '2012-08-25' AND DATE(now())
GROUP BY id, d;

I'm running explain on this query on a tiny table (2 records) and it tells me it's going to use my primary key index:

id  | select_type  | table | type   | possible_keys | key     | key_len | ref  | rows | Extra
1   | SIMPLE       | p     | range  | PRIMARY       | PRIMARY | 8       | NULL | 1    | Using where; Using temporary; Using filesort

When I run this query on the same table with 350 million records - it prefers to go through all the records and ignore my keys:

id  | select_type  | table  | type | possible_keys  | key  | key_len | ref  | rows      | Extra
1   | SIMPLE       | p      | ALL  | PRIMARY        | NULL | NULL    | NULL | 355465280 | Using where; Using temporary; Using filesort

Obviously, this is extremely slow.

I've tried losing the GROUP BY and even changed the BETWEEN to a simple '>', but it still won't use the key.

The only time I got it to use the key was when I used 'rtime = ..'.

I should note that all this data is actually only going about a week back, so when I try to get rtime > 3-days-ago I'm expecting a significant chunk from those 350 million records.

This query is supposed to run every 15 minutes, so the current 30-40 minute execution time is definitely unacceptable.

How can I construct the query such that it uses the indexes, or how should I index the table in order to get the fastest performance?

The table size is not the villain.  It's the estimated number of rows.

The query optimizer, in this case (MyISAM, key starting with rtime, etc), will do something like this:

estimate the percentage of the table to scan, based on "WHERE rtime BETWEEN..."
If that is "small" (say, less than 20%, but that is not a hard number), use the INDEX; else do a table scan.

Step 1 depends on the "statistics" that are kept with the MyISAM table.  The stats are usually pretty accurate, but they can become less accurate.  ANALYZE TABLE is the fix for that.  (I don't think I have ever seen a need for ANALYZE being run more than monthly; usually it is not needed at all.)

The reason for the to-INDEX-or-not-to-INDEX question goes something like this...  When using the INDEX, the execution has to bounce between index 'rows' and data rows.  The data rows are potentially randomly scattered, leading to (potentially) lots of I/O.  Hence, doing a 'table scan' is preferred after some point.


Copy tables selected from sys.tables to new DB I have a DB with thousands of tables.

I want to copy all tables with name like 'TableType1%' to a new DB.

I can easily get a list of the tables:

  select * from sys.tables t where schema_name(t.schema_id) = 'S1' AND
  [name] LIKE 'TableType1%'

But how do I copy them to a new DB?

I CANNOT do this manually, as there are too many tables.

I would like to use

  INSERT INTO

type statement, but don't know how to put it into select statement above.

Something like:

select * from sys.tables t
insert int [NewDB].[S1].[ t.name ] <<< What should go here?
where schema_name(t.schema_id) = 'S1' AND [name] LIKE 'TableType1%'

If you are looking for a quick one-off solution, you can try this.

In the source database, run this command (making the necessary changes for TrgDb for your target database and SrcDb for your source database.  Also change the where clause to pick your tables.

SELECT 'select * into TrgDb.S1.' + t.NAME + ' from  SrcDb.S1.' + t.NAME + ';'
FROM sys.tables t
WHERE NAME LIKE 'blah%';

Take the results and paste the commands into a new SSMS window that points to the target database.


Data exchange between 2 remote postgresql databases As per subject I'm looking into a reliable way to synchronize 2 tables that have a same structure between two remote postgresql databases.

I was thinking about dblink but an not really sure as in the places where these db's are connectivity is a big issue and very unreliable and this would force me to expose my databases to the internet which is an idea I'm not comfortable with.

Another option would be to write myself a daemon implementing integrity checks but before proceeding I'd like to know if anyone has undergone a similar challenge and what solutions have been undertaken.

If you only need one-way sync then I strongly recommend Londiste. For two-way, Londiste 3 or Bucardo can both do what you want with trigger-based replication.

Some ETL tools can do periodic table syncs, including two-way merges. Look into CloverETL, Pentaho Kettle, Talend Studio.

Don't try to roll your own. It's harder than you think.

The BDR project is working toward adding this to PostgreSQL, hopefully in 9.5, but it's currently in early development; I'm mentioning it so you're aware it exists for the future, and for later readers.


Can't restore sql bak file to a new database I can't seem to restore a bak file to the same server but a new database. It works fine if I do it on another computer. I use SQL Server 2012 through SSMS. Everytime I do it it says Exclusive access could not be obtained because the database is in use, even though it's a nonexistent destination database.

Here are the steps:

Right click Databases
Restore Database...
[General] Source from Device, select the bak file
[General] Change the Destination database name to a new name
[Files] Change mdf and ldf Restore As paths to a different, new path
[Options] (unnecessary on the other computer) Check Overwrite the existing database (WITH_REPLACE)
[Options] (unnecessary on the other computer) Check Close existing connections to destination database
Click OK

This is the error message:

  Restore of database 'DELVIPROD_JUNE' failed.
  (Microsoft.SqlServer.Management.RelationalEngineTasks)

  Additional information:
  System.Data.SqlClient.SqlError:Exclusive access could not be obtained because the database is in use.
  (Microsoft.SqlServer.SmoExtended)

Close all tabs on the right side in the management studio and try again.  It is possible that you clicked on the database and some tab on the right side is accessing it.

Alternatively, you may use this sql (replace file name and path)

USE master;
GO
ALTER DATABASE DELVIPROD_JUNE SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
restore database DELVIPROD_JUNE FROM DISK = 'C:\temp\db.bak'   WITH replace
ALTER DATABASE DELVIPROD_JUNE SET MULTI_USER;

If you want to make a copy of a database, this might help https://serverfault.com/questions/62590/how-to-duplicate-mssql-database-on-the-same-or-another-server


Enforce a constraint problem This business model is for a case management database. This is closely modeled on the idea of a file folder representing the phase and a sequential checklist representing the stages. A case consists of a phase that can have one or more stages.  A phase can only have one stage that is "Current" or open at any one point in time.  A case can only start from one type of stage but can progress to any one of a number of stages that are end types. In this business model there are many different types of phases and stages

An example: you apply for a license. The process always starts with you submitting a form but can have different endings: the application is approved or rejected or sent back for more information.

Edit: @Colin 't Hart  asks what a phase is in relation to a case. Here is where trying to simplify a question can omit details.  The complete schema structure is:
- one case can have one or more phases but only one phase is open or "current" at at time.
- each phase can have one or more stages but only one phase is open or "current" at a time.
- there are different types of cases/phases/stages and transitions from the current unit to the next unit require adding a close date to the current and inserting a new record with an open date.
An example: a production line for widgets

a production ticket initiates the creation of the case
the first phase: sourcing components is created
the first stage: contacting suppliers is created
the first stage is completed, the second stage: orders from suppliers is opened
orders stage is closed, inventory check stage is created
inventory stage is closed, sourcing components phase is closed
new phase: assembly is opened
new stage: move components to shop floor is opened
moving components stage is closed, new stage production line is opened
and so on....

Problem:

the existing table structure is flawed in that the same information (what is the first type of stage for a kind of phase) is stored in two different tables
You can have more than one entry in STAGE where IS_START_STAGE = 1 which violates a business rule
You can insert a new entry into STAGE where IS_START_STAGE = 1 and this does not match the corresponding entry in PHASE_FIRST_STAGE
the relationship should be something like constraint PHASE_FIRST_STAGE.STAGE_ID can only be in the entries in STAGE where IS_FIRST_STAGE = 1
Is there anyway to enforce these business rules?

CREATE TABLE PHASE_FIRST_STAGE
(
  PHASE_ID           NUMBER(9)           NOT NULL, --PRIMARY KEY and foreign key to PHASE
  STAGE_ID           NUMBER(9)           NOT NULL,  --FOREIGN KEY to STAGE table
);
ALTER TABLE PHASE_FIRST_STAGE ADD (CONSTRAINT PFS01
  FOREIGN KEY (PHASE_ID)
  REFERENCES PHASE (ID),
  FOREIGN KEY (STAGE_ID)
  REFERENCES STAGE (ID));
COMMENT ON TABLE PHASE_FIRST_STAGE IS 'Contains the default first stages to enter when a phase is entered.';

CREATE TABLE STAGE
(
  ID               NUMBER(9)           NOT NULL, --PRIMARY KEY
  PHASE_ID         NUMBER(9)           NOT NULL,  --FOREIGN KEY to PHASE
  DISABLED         NUMBER(1)           DEFAULT 0   NOT NULL,  --CHECK IN (0,1)
  IS_START_STAGE            NUMBER(1),--CHECK IN (0,1)
  IS_END_STAGE              NUMBER(1) --CHECK IN (0,1)
);

COMMENT ON TABLE STAGE IS 'Contains all the stages a phase can have. Each stage must have only one phase. ';
--not shown is a similar table called PHASE with a one phase => many type of stage relationship

Justin Cave's answer here and Tom Kyte's pointed me to a solution using a function based index. I think this can be made even simpler with some more thought but this works now:
CREATE OR REPLACE FUNCTION UNIQUE_START_STAGE (
   phase_id_in   IN NUMBER,
   stage_id_in   IN NUMBER)
   RETURN NUMBER
   DETERMINISTIC
IS
 -- PURPOSE:enforce business logic that a phase can have only one stage where
 -- the disabled field has a value of 0 and IS_START_STAGE has a value of 1

   v_count   NUMBER (9);
BEGIN
   SELECT COUNT (s.id)
     INTO v_count
     FROM STAGE s
    WHERE     S.IS_START_STAGE = 1
          AND s.disabled = 0
          AND S.PHASE_ID = phase_id_in;

 IF v_count = 1
 THEN
     --return the primary key if there is only one
     v_count := stage_id_in;
  ELSIF v_count < 1
  THEN
     v_count := NULL;
  END IF;

   RETURN v_count;
END UNIQUE_START_STAGE;

and then we create an index based the idea that there can only be one child stage that is enabled for a phase that is the start stage
CREATE UNIQUE INDEX unique_start_stage_idx
   ON stage (
      CASE
         WHEN disabled = 1 THEN NULL
         WHEN is_start_stage = 0 THEN NULL
         ELSE UNIQUE_START_STAGE (phase_id, id)
      END);

--and add the same constraint to the other table

CREATE UNIQUE INDEX unique_start_stage_idx2
ON PHASE_FIRST_STAGE (
UNIQUE_START_STAGE (phase_id, stage_id));
This solution partially solves the problem:

it enforces that there is only one entry in STAGE for each value of PHASE_ID where IS_START_STAGE =1 and DISABLED = 0
it enforces this same uniqueness in PHASE_FIRST_STAGE
it does not enforce that an entry in STAGE is also in PHASE_FIRST_STAGE
you could replace the PHASE_FIRST_STAGE table with a view of STAGE that cleans up the last issue


SQL Server suggestion to create nonclustered index - on 2 columns, reversed I have a query, where SQL Server 2012 suggested a create a nonclustered index as follows:

CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([courseID])
INCLUDE ([userID])

I created the index with this code.

Running the query, it now is suggesting a similar index, but with the INCLUDE reversed:

CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([userID])
INCLUDE ([courseID])

Excuse my ignorance... is there a (significant) difference between these 2, or is this one of the cases I am reading, where the SQL Server index suggestions may be off?

Many thanks

UPDATE:

Here is the query - its a pretty basic query, getting a list of courses, and the number of people (men/women/paid/not paid) for each course. Would be happy to hear if there is a better way...

Interestingly, adding in the eventID = 2 at the very end, made the query run in 2 mins+ rather than 4 seconds without that filter!

    select C.*
    ,(
        select COUNT(*)
        from tblUserCourses UC
        inner join tblUserEvents on UC.UserID = tblUserEvents.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
        ) as PaidParticipants
    ,(
        select COUNT(*)
        from tblUsers U
        inner join tblUserEvents on U.UserID = tblUserEvents.UserId
        inner join tblUserCourses UC on U.UserID = UC.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
            and UPPER(U.gender) = 'M'
        ) as Males
    ,(
        select COUNT(*)
        from tblUsers U
        inner join tblUserEvents on U.UserID = tblUserEvents.UserId
        inner join tblUserCourses UC on U.UserID = UC.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID = 50
            and tblUserEvents.EventID = C.eventID
            and UPPER(U.gender) = 'F'
        ) as Females
    ,(
        select COUNT(*)
        from tblUserCourses UC
        inner join tblUserEvents on UC.UserID = tblUserEvents.UserId
        inner join tblCourses on tblCourses.courseID = UC.courseID
        where UC.courseID = C.CourseID
            and tblUserEvents.StatusID < 50
            and tblUserEvents.EventID = C.eventID
        ) as NonPaidParticipants
from [tblCourses] C
where (eventID = 2)
order by scheduledDate
    ,internalOrder

Yes there is a big difference between both indices.
The following index will use the courseID as a seek predicate. This will help  SQL Server to find all pages with a defined courseID. In addition, the column userID is included in the pages of the index. This will allow SQL Server to serve those values directly from the index.
CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([courseID])
INCLUDE ([userID])

The creation of this index will help SQL Server to perform quick responses for Queries like this:
SELECT userID FROM yourTable WHERE courseID = 1

The courseID can be used for the search in the index. Where the userID is served from the leaf level pages from the index.
Where as the following index works exactly the same but the other way round. It will use the userID for the seek and can provide the courseID directly from the leaf level pages from the index.
CREATE NONCLUSTERED INDEX [<name>]
ON [dbo].[tblUserCourses] ([userID])
INCLUDE ([courseID])

The index will improve the performance of this query:
SELECT courseID FROM yourTable WHERE userID = 1

SQL Server may also use indices and join the results of both indices.
In example, you can serve this query totally from the indices:
SELECT userID, courseID FROM yourTable

This will be served by a hash match using the both indices.
Another one which could benefit from both together may be this one:
SELECT COUNT(*) FROM yourTable WHERE userID=2 and courseID=10

It might happen, that your SQL Server provides an index hint and sees that he could perform better by using a special index. After you created those index, he recognizes (during the new index) that another one would be even better. This isn't uncommon.
This may also happen if your query optimizer runs into a timeout. This can happen very fast. In the case your query optimizer times out, SQL Server will use the best plan he found until the timeout. This can vary and this may have some bad effects. Using the indices, you help your query optimizer and he will use it's time more efficient. To provide good plans.
To really see if your indices are a good choice, you need to show a bit more of your code. Additionally you can try to evaluate your index usage. There is a good DMV for this called dm_db_index_usage_stats. It will provide you how many reads, seeks, updates, etc. happend on an index. This will help you to get your own decisions if it's good or bad. An index is mostly on then useful if you read it more than you write it.
SELECT * from sys.dm_db_index_usage_stats

Hopefully this will help to clear things up for you.
Edit
Due to the lastly provided information, I would suggest to try an index based on EventID, CourseID (same order!).
On your other table maybe an index over EventID, StatusID too.


Postgresql Two-phase Commit Transaction Identifier Is it possible to perform a two-phase commit in Postgresql when the databases exist on the same server?

As a simple test, I would connect to the first database and run:

BEGIN;
PREPARE TRANSACTION 'test';

and in a new terminal tab I would connect to the second database and run the same commands. This outputs the error:

  ERROR:  transaction identifier "test" is already in use

So my solution was to simply not use the same transaction identifier across all databases.  The Transaction Manager can keep track of the various identifiers and commit the databases accordingly.

Seems ridiculously obvious so please correct me if I'm wrong.


Connect to another host from inside mysql stored procedure I have two virtual servers on one physical machine.

There are MySQL users and passwords the same for both of them.

I want to write something like this:

SELECT * FROM [host_name].[db_name].[table_name]

Federated / FederatedX is not a solution for me. At least I want to avoid using it if it is possible.

If what I want is not possible I need some solution to do the following:

I have two MySQL servers: server1 and server2.

I want to set a trigger on a few tables in server1 so i.e. when new row appears there a part of that row should go to server2.

I don't want an entire row to be replicated. There might be some values on which I will want to remove some rows form server2 and e.t.c.

I was thinking I should call some procedure on the trigger if it is possible.

No.  One connection can talk to only one MySQL server.
Any Stored Routine lives in a single MySQL server, so another connection is not allowed for.


Is it safe to rely on a shadow volume backup of the mdf and ldf files? We are looking at replacing traditional SQL server backups with a VSS based backup of mdf and ldf files.  As a db person I'm somewhat twitchy about this, and yet I can find no evidence that this won't work?

Can anyone suggest a trial I can setup that would demonstrate where we can lose transactions with this strategy? [Yanking out the power cord during a long running transaction is fine].

The system we are looking at creates an initial snapshot of the mdf and ldf files and then copies across changes. I can't imagine a scenario where that could fail us.

Hopefully you can help me convince my boss that we need to keep traditional backups!

Sorry to disappoint you, but VSS Backups of SQL Server are fully supported by Microsoft through the SQL Writer Service (http://technet.microsoft.com/en-us/library/ms175536.aspx).

When the third party backup application wants to take a backup, SQL Writer tells SQL Server to "Freeze IO" on the database.  After the IO is frozen, then a snapshot is taken of the mdf and ldf files.  After the snapshot completes, IO is unfrozen.  This does prevent all write activity to the database while the snapshot is being generated, and could take 60 seconds or longer. The third party backup app is then copies the snapshots of ldf and mdf files.  If you test the third party backup application and you see messages in the error log about Freezing and Unfreezing of IO for your databases, then it's using the SQL Writer interface, and the backups are good.

Full Description here:  http://technet.microsoft.com/library/Cc966520


Efficiency of foreign keys If I have a table A which has a composite (two column) foreign key on another table B, should I:

a) create a two-column key in B to match the foreign key in A?
b) or, MySQL automatically creates the 'missing key' in B to match A's foreign key?
c) or, if neither a) nor b) should I assume MySQL might do a table scan on inserting into A?

I'm trying to avoid dubious keys in B as my index already grows at an alarming rate. However, I do not want to penalize insertion/updates into  A and would rather take the index growth hit.

Please advise.

You seem to have two questions -- one about efficiency of FKs, one about composite keys.

FOREIGN KEYs are are inherently inefficient.  This is because they involve a lookup in another table to check for consistency.  This happens even if you have thoroughly debugged your application, or if there is not need to the check.

So, if efficiency is the question, I say "get rid of FKs".

FOREIGN KEYs implicitly create the index needed for the above check.  This index could have been manually created.  Either way, the index probably benefits some JOIN in some queries.  That is efficient, although there might be an even better index than the one that the FK decided to create.

Check to see what the FK generated by doing

SHOW CREATE TABLE table_name

You also mentioned "dubious keys".  Here's a short list of "redundant" indexes to watch out for:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#redundant_excessive_indexes


What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology? What is the MySQL equivalent of SQL Server 2016 Always Encrypted technology or is there anything similar in MySQL that can act as AE?

The Always Encrypted feature provides end-to-end encryption, which encrypts data both in-flight, and at rest. Data is encrypted & decrypted on the client at the driver. Data is encrypted everywhere except for on he client machine. This is one step beyond encryption at rest (which is handled in SQL Server by Transparent Data Encryption).

Other RDBMS products do not have a feature which is quite the same. To do something similar on another RDBMS, you could perform encryption on the client within your application, and then send data in its encrypted format to the database.

Also, with deterministic encryption, SQL Server Always Encrypted allows point lookups, equality joins, grouping, and indexing on encrypted columns, which is often not handled by home-grown encryption.


What does "thread" mean in "thread checkpoints" context? The following excerpt is from Oracle concepts. Maybe this is a stupid question, but I really need your help to understand it.

My question is:

What's does the thread mean in the context thread checkpoints?
Does it means redo log thread(all redo log files that belong to a specific instance)?
What does a certain target means?

Thanks all! English is not my mother tongue, and I feel a little difficult to fully understand this.

When Oracle Database Initiates Checkpoints

The checkpoint process (CKPT) is responsible for writing checkpoints to the data file
headers and control file. Checkpoints occur in a variety of situations. For example,
Oracle Database uses the following types of checkpoints:

■   Thread checkpoints
    ^^^^^^
    The database writes to disk all buffers modified by redo in a specific thread before
                                                             ^^^^^^^^^^^^^^^^^^^^
    a certain target. The set of thread checkpoints on all instances in a database is
    ^^^^^^^^^^^^^^^^^
    a database checkpoint. Thread checkpoints occur in the following situations:

    – Consistent database shutdown

    – ALTER SYSTEM CHECKPOINT statement

    – Online redo log switch

    – ALTER DATABASE BEGIN BACKUP statement

■   Tablespace and data file checkpoints

    The database writes to disk all buffers modified by redo before a specific target. A
    tablespace checkpoint is a set of data file checkpoints, one for each data file in the
    tablespace. These checkpoints occur in a variety of situations, including making a
    tablespace read-only or taking it offline normal, shrinking a data file, or executing
    ALTER TABLESPACE BEGIN BACKUP.

■   Incremental checkpoints

    An incremental checkpoint is a type of thread checkpoint partly intended to avoid
    writing large numbers of blocks at online redo log switches. DBWn checks at least
    every three seconds to determine whether it has work to do. When DBWn writes
    dirty buffers, it advances the checkpoint position, causing CKPT to write the
    checkpoint position to the control file, but not to the data file headers.

Other types of checkpoints include instance and media recovery checkpoints and
checkpoints when schema objects are dropped or truncated.

Yes, a thread in this case is the redo generated by an instance. If you had a 4-node RAC, you would have 4 redo threads. This is thread# in v$archived_log. The certain target is almost certainly referring to an SCN.


How to write a query to find all tables in a db that have a specific column name I've got a database with about 100 tables and I need to build a join query to get specific data from two of them.  I know one but not the other.  Basically I need something like:

select <tables> from <database> where exists table.column name;

How can I do this?

Using information_schema

This is the standards-compliant cross-RDBMS way to do it.

SELECT table_catalog, table_schema, table_name, column_name
FROM INFORMATION_SCHEMA.columns
WHERE column_name = '<your column name>';

You can see this documented

PostgreSQL
SQL Server
MySQL 8.0


why index scan is slower then seq scan? slava=# explain (analyze) SELECT * from pending_posts WHERE user_id <> 1456
slava-# ;
                                                       QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
 Seq Scan on pending_posts  (cost=0.00..17906.00 rows=999994 width=14) (actual time=0.021..231.803 rows=999994 loops=1)
   Filter: (user_id <> 1456)
   Rows Removed by Filter: 6
 Planning time: 0.108 ms
 Execution time: 289.845 ms
(5 rows)

slava=# explain (analyze) select * from pending_posts where user_id <> 1456 order_by user_id;
ERROR:  syntax error at or near "order_by"
LINE 1: ...select * from pending_posts where user_id <> 1456 order_by u...
                                                             ^
slava=# explain (analyze) select * from pending_posts where user_id <> 1456 order by user_id;
                                                             QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using p_i on pending_posts  (cost=0.42..50103.17 rows=999994 width=14) (actual time=0.033..899.520 rows=999994 loops=1)
   Filter: (user_id <> 1456)
   Rows Removed by Filter: 6
 Planning time: 0.141 ms
 Execution time: 953.982 ms
(5 rows)

Given that user_id is integer indexed with btree, should't it be faster to sort and then select using tree search?

PostgreSQL will evaluate all (or a high number) of possible query plans, and make a good guess at which one will be the least costly. If the different cost parameters (that depend, for instance, on the speed of your disks; the amount of available memory, etc.) are well set, most of the times that guess will be accurate and the choice of plans will be optimum.

You're asking PostgreSQL two different queries. One doesn't need sorting, the other one does. The rows returned are the same, because the rest of the query is the same. The query with the ORDER BY takes longer. That is to be expected.

PostgreSQL could have decided that performing a Seq Scan then a Sort would be quicker than performing an Index Scan. With the actual settings, it decided the opposite.

Performing an Index Scan takes longer than performing a Seq Scan just because, when scanning the index, PostgreSQL can just skip the entries with (user_id <> 1456) (the info for user_id is in the index). For the non-skipped values, it gets the (internal) reference to the corresponding row (which is also part of the index), and it has to retrieve the actual row in the table. It won't need to sort them, because by following the index, the values are already in the proper order.

With standard settings, the costs for for seq_page_cost and random_page_cost (in arbitrary units) are 4 and 1. That takes into account that, with HDD, jumping from one page to another needs a significant mechanical movement, and needs more time than reading pages one after next. You can assume a Seq Scan will read pages sequentially, and an Index Scan will mostly jump from one to another. [If you use SSD instead of HDD, the value for random_page_cost should probably be put to just 1.05].

If you would be using a covering index (one index that contains at least all the columns that are in your SELECT), then, PostgreSQL would be able to perform an Index Only Scan (meaning it doesn't have to retrieve the whole rows from the actual *table). That would speed up your query significantly. For a covering index to work, your table has to be properly maintained (i.e.: autovacuum must have had time to maintain it).

Try this query and see what the execution plan is:

EXPLAIN (analyze)
SELECT user_id
FROM pending_posts
WHERE user_id <> 1456
ORDER BY user_id ;

If you don't get an Index Only Scan, perform a:

VACUUM ANALYZE pending_posts ;

and retry it.

Note that indexes can help you in several areas:

In the SELECT clause if they are covering indexes. If the indexes contain all the columns specified, Index Only Scans are possible.
In the WHERE clause if they allow to filter out (or go directly to) specific) rows.
In the ORDER BY clause, if they provide already the rows in the sort order you specify.
In the GROUP BY, if the aggregation is done by sorting the rows according to the groups (other techniques are possible).

References:

Manoji Nesh Blog:  Seq scan vs Index Scan
PostgreSQL query plan and SQL performance notes - Types of index scans
Stack Overflow's Why does PostgreSQL perform sequential scan on indexed column?


Pattern for copying hierarchical data from one table to another? Imagine a table with three columns: an ID primary key, a nullable ParentID referencing the ID column on that same table, and a TreeID to indicating the records that make up a particular tree. A basic hierarchical structure might look like this. Note that the ordinal values don't necessarily have an effect on a record's position in the hierarchy.

      dbo.TreeNodes             Visual representation:
+----+----------+--------+    --------------------------
| ID | ParentID | TreeID |       Tree 7:     Tree 15:
+----+----------+--------+       1           10
|  1 | NULL     |      7 |       |–3         |–9
|  2 | 4        |      7 |       |–4
|  3 | 1        |      7 |       |–|–2
|  4 | 1        |      7 |       |-5
|  5 | 1        |      7 |
|  9 | 10       |     15 |
| 10 | NULL     |     15 |
+----+----------+--------+

Given another unrelated table with an identical definition (dbo.TreeNodes2, let's say), I've found that copying the data from the first table to the second while maintaining its self-referential hierarchy isn't so straightforward. I get stuck after inserting the new dbo.TreeNodes2 records when it comes time to populate the ParentID values. I would have to have a mapping table of some kind to know which records from the first table map to the new records in the second table. Here's what I've got so far:

DECLARE @TreeID INT = 7;
-- hard code arbitrary TreeID value for the 2nd table for the sake of demo
DECLARE @NewTreeID INT = 42;

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID INTO #InsertedRows
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

I could add a non-related OldID column to dbo.TreeNodes2 to copy the original IDs to, which I could then use to create a mapping to join on and populate the new ParentID records, then null out all the OldID values when I'm done, but this seems like bad design. If SQL Server allowed the OUTPUT clause of an INSERT to include columns from a join, then this would be easy, but all of the documentation, as well as my own testing, indicates that only Inserted.* columns, Deleted.* columns, and scalar values/literals can be used in the OUTPUT clause. Nothing from joins.

My pipe dream, non-valid T-SQL that does what I want would look something like this:

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY,
    OldID INT NOT NULL
);

INSERT INTO dbo.TreeNodes2
(
    ParentID,
    TreeID
)
OUTPUT Inserted.ID, tn1.ID INTO #InsertedRows (ID, OldID)
SELECT
    NULL, -- ParentID can't be added yet because the IDs don't yet exist.
          -- They need to be updated in later.
    @NewTreeID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

From here, I would have a mapping table I can use to populate the ParentID values in the new table.

Is there a pattern for handing these kinds of cross-table hierarchical inserts without relying on an extra column whose only purpose is to allow the migration, after which it's an irrelevant column?

In this scenario I would not use IDENTITY for primary keys, but switch to sequences.
This way you can reserve sequence numbers to a temporary table, which became your mapping table, something like:

CREATE SEQUENCE TreeNodes2Sequence START WITH 1 INCREMENT BY 1 ;

CREATE TABLE TreeNodes2 (
    ID int PRIMARY KEY DEFAULT (NEXT VALUE FOR TreeNodes2Sequence),
    ...
)

CREATE TABLE #InsertedRows
(
    ID INT NOT NULL PRIMARY KEY,
    OldID INT NOT NULL
);

INSERT INTO #InsertedRows
(
    Id,
    OldId
)
SELECT
    NEXT VALUE FOR TreeNodes2Sequence,
    tn1.ID
FROM dbo.TreeNodes AS tn1
WHERE tn1.TreeID = @TreeID;

Then use this temp table to insert all values with null parent and then update parents.


Mysql update table using ubuntu terminal I have a CSV file that has 2 columns

Name
Email

I have a table in my database that has the same columns + some extra columns.

Now I would like to update my db table using that csv file. I mean like this

UPDATE `mytable` SET `email` = 'csvemail' WHERE `name` = 'csvname'

Can someone help me to do that from my ubuntu terminal?

MySQL has built-in support for importing data from CSV files, just use the command-line client :

  mysql -u <user> -p

(replace <user> with your MySQL username and enter the password when asked to)

Then, tell MySQL to use your database :

  USE <database>;

(replace <database> with the database name)

And finally, use this to import everything from your CSV file :

  LOAD DATA INFILE '<path>' INTO TABLE <table>;

(replace <path> with the full path to the CSV file such as /home/user/file.csv and <table> with the table name).

Look at this question and this one that may be related to your question.


Partitioned Database restore is very slow on SQL Server 2012 with SP2 Team,

We have a Partitioned database which is very small in size(112MB backup size). This is partitioned with 15000 files for one table and 7500 files for other table.

Currently we are running into issues in restore taking significant amount of time which is having implications on application build process.
We tested with both Virtual and Physical hardware.

Please let us know your thoughts on the slowness? We have explored CU6 for SQL Server 2012 SP2 as well with no luck

Regards,
Sharath

Well I don't wonder about the restoring time. I wonder about your file amount. 15k files for 112MB? What the h...?

Your symptom is nearly the same as during file copying. If you have two disc (disc1, disc2) and you try to copy 100.000 files each 4KB in size from disc1 to disc2 it may take hours. If you copy one zip file without compression (containing those files) with 400.000KB file size and then extract it afterwards on disc2, it would take a few seconds.

You'll stressing your I/O subsystem with many I/O operations. Consider to merge those files back to one or just a few. I'm not sure what has driven you to those design?


Ideal way to represent a Grade -> Section -> Student -> Marks database Although it's marks for now, I'm going to change the last entity to something else later. So I need every mark of every student in one table. What I'm thinking is for every mark to belong to one student, every student to belong to one section, and every section to one grade.

I'll be analysing the marks in different ways too, i.e, I want to present information on all marks under a particular student, all marks under a particular section, and all marks under a particular grade.

One thing is to store as what I said in the first paragraph. If I want to work on every mark of every student in a grade, I must select, for each section that belongs under the grade, all marks such that the student who owns the mark must be in that section.

However in that case I'm wondering if the queries might take too long. (I'm new to db so I'm not sure). Or should I, for every mark, also have a column for its section and its grade, so that querying under a given grade/section/student is easy?

Thanks

Tie the marks to the student, the student to the section, and the section to the grade using foreign keys.  The join will usually be quick as long as you have your indexes set up to include those columns.

As far as making it easy to tie together you can create a view that ties them together so you only have to query one place to get what you are looking for.


Understanding of Oracle multi-tenant PDBs I'm a new user to Oracle. The document states

  The CDB root, also called simply the root, is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.

and

  A PDB is a portable collection of schemas, schema objects, and nonschema objects that appears to an Oracle Net client as a non-CDB.

So both CDB and PDB are a collection of schema, schema objects and nonschema objects. How could PDBs belong to CDBs? What does it mean by something like schema object a belong to schema object b?

It simply means a PDB looks like a normal database instance. When you log into a PDB you only see the users and their database objects which belong to that PDB. The CDB is the actual sever instance, and it has the objects it needs for maintaining the server. In the CDB you can log in, and the tables there have a new column to mark the records associated with a specific PDB.


Adding Data to Multiple Tables Using Single Query I want to add data to multiple tables using a single INSERT INTO query, can I do this?

Please guide me and provide me syntax if this is possible.

This is not possible.  The INSERT statement takes a single destination object as per the syntax (BOL reference):

[ WITH <common_table_expression> [ ,...n ] ]
INSERT
{
        [ TOP ( expression ) [ PERCENT ] ]
        [ INTO ]
        { <object> | rowset_function_limited
          [ WITH ( <Table_Hint_Limited> [ ...n ] ) ]
        }
    {
        [ ( column_list ) ]
        [ <OUTPUT Clause> ]
        { VALUES ( { DEFAULT | NULL | expression } [ ,...n ] ) [ ,...n     ]
        | derived_table
        | execute_statement
        | <dml_table_source>
        | DEFAULT VALUES
        }
    }
}
[;]

Take note of the <object> placeholder on the 6th line of the syntax snippet.

Is there a particular reason why you want to insert into two tables in one statement?  Perhaps you are looking for both inserts to either succeed or fail as one?  In that case, you would be looking at explicit transactions:

begin tran;
    insert into dbo.YourTable1 ...
    insert into dbo.YourTable2 ...
commit tran;

Besides code brevity and atomicity, I wouldn't see any other reason that you'd want to combine multiple destination tables into a single INSERT statement.


Query 2 servers in one SQL script I have 2 databases on server A ("srvA") that I use to build a data cube on server B ("srvB").  It's taken me an embarrassingly long time to discover that populating my cubes fails because the batch file opens my script on the first server and it is not directed toward the server B for the second half of the scripts.  I have:

DECLARE @theDay dateTime
Set     @theDay = GetDate()

EXEC    [db1].[dbo].[sp_deltas] @theDay
EXEC    [db2].[dbo].[sp_deltas] @theDay

GO

--doesn't work
--Use [srvB].[cubeDB]
--Go

--works if I open as a new query in server B
DECLARE @theDay dateTime
Set     @theDay = GetDate()

EXEC    [dbo].[sp_CUBEpop]
        @DBname = 'db1',
        @startDate = @theDay,
        @endDate = @theDay

EXEC    [dbo].[sp_CUBEpop]
        @DBname = 'db2',
        @startDate = @theDay,
        @endDate = @theDay
GO

The sp_deltas stored procedure runs just fine, but the "Use [srvB].[cubeDB]" statement doesn't work, and I suspect that having a linked server names "srvB" may not be helping matters.  The login I am using can run the stored procedures just fine on srvB as long as I navigate there manually and open a new query. How can I run them all in the same script so I am sure that the actions happen sequentially?

You can't use USE to change server context. You can use four-part naming for your objects, like Shawn suggested, if srvB is a linked server.

Another option is using SQLCMD mode, then you can say:

-- do stuff on server A
:CONNECT srvB
-- do stuff on server B

But that doesn't work well if you are trying to share data etc. between the two batches. For variables you can use SQLCMD variables instead.

More info here.


need better db design for project I have two tables:

notes
users

Notes Table:

id || description ||CreatedBy(which is userid)||......other columns
---------------------------------------

Users table:

id || name || ......other columns
---------------------------------

Now I have a scenario, in which a user can create a note and share it with
other users. Each user can create a note and then can share this with other users.

To Implement the above task, I will create a new table 'UserNotes'.

UserNotes Table:

id || userId || NotesID
-----------------------

But suppose I have one note which I want to share with all other users then
my usernotes table will have 100 entries.
Similarly if I have 1000 notes and all notes are shared with all users then my new table will have 1000*1000 entries.

Also a user who has created a note and shared with other users, can remove the sharing also for some users.
For example: if a user creates a notes and shared with 100 users initially, he can later on remove some user associations or add some new users also.

Is my approach correct?

Or is there a better approach to achieve the above scenario considering ease of insert, update and select?

I am using POSTGRESQL as my RDBMS.

Can you please help?

I think the Notes table is missing a userID FK column (and put an index on it)
You need to identify the owner of the Note

The UserNotes table does not need an ID
just:

NoteID || UserId

That is a sufficient composite PK
And I would do it in that order for quick counting NoteID
select count(*) from UserNotes where NoteID = X would be very very fast

So what 1000 x 1000 rows.  You have 1000 x 1000 unique relationships.  It is just two Int.

One trick is have a UserID Public ID 0 for all

Just a preference but I don't use plural in a table name
Tables are used to store many rows


How to Recover mysql Database From mysql data directory in Xampp I have reinstall Windows on my System.I forget to export databases.Now i have .frm and .ibd files in my xampp folder how can i recover my database From these Files?

[mysqld]
innodb_file_per_table=1
innodb_force_recovery=5
--This will make MySQL skip a lot of checks on your data
--create a dummy database, with dummy tables that have the same names as the --ones you wish to restore.
-- stop mysql instance
--copy over your .frm files now
--start mysql
mysql> use recover;
mysql> flush tables;
mysql> exit;


How to create a trigger that will insert values into another table twice? I'm trying to create a trigger that will insert two values into another table.
From table booking to table flight;

Here is my code for the booking table:

CREATE TABLE Booking (
Booking_ID            NUMBER(10)   NOT NULL PRIMARY KEY,
Company_ID            NUMBER(10)   NOT NULL REFERENCES
Penrhyn_Jet_Charter(Company_ID),
Customer_ID           NUMBER(10)   NOT NULL REFERENCES
Customer(Customer_ID),
Aircraft_ID           NUMBER(10)   NOT NULL REFERENCES
Aircraft(Aircraft_ID),
Assignment_No         NUMBER(10)   NOT NULL,
Booking_Date          DATE         DEFAULT SYSDATE,
Charter_Cost          NUMBER(14,2) CHECK(Charter_Cost > 0),
Departure_Date        DATE         NOT NULL,
Departure_Location    CHAR(3)      NOT NULL,
Arrival_Date          DATE         NOT NULL,
Arrival_Destination   CHAR(3)      NOT NULL
);

Here is my code for table flight:

CREATE TABLE Flight
(
Aircraft_ID  NUMBER(10),
Flight_Date  date,
constraint pk_flight primary key (aircraft_id, flight_date)
);

The point of the trigger is to check for double bookings, so the Aircraft_ID and Departure_Date will be taken to the flight table as one entry.

Then the Aircraft_ID and Arrival_Date will be taken to the flight table as another entry.

if any of the entries were to be the same, then it would result in an error as in table flight, Aircraft_ID and Flight_Date are composite primary keys, at least that's the idea anyway.

So I'm stuck here trying to create a trigger:

CREATE OR REPLACE TRIGGER Check_Double_Booking
BEFORE INSERT OR UPDATE ON Booking
FOR EACH ROW
BEGIN

Any ideas? Thank you!

So the following trigger works perfectly to prevent double bookings for the case stated in the question:

CREATE OR REPLACE TRIGGER Check_Double_Booking
BEFORE INSERT OR UPDATE OF Aircraft_ID, Departure_Date, Arrival_Date ON

Booking
FOR EACH ROW
BEGIN
INSERT INTO Flight VALUES (:new.Aircraft_ID, :new.Departure_Date);
INSERT INTO Flight VALUES (:new.Aircraft_ID, :new.Arrival_Date);
END;


Changes between Mysql version 5.1 TO 5.7 I have recently migrated Mysql 5.1 to Mysql 5.7. DB Migration was successful but when the application started firing few selected queries, Mysql started taking hours to execute them for which previously execution times wherein 4-5 seconds. That's  why wanted to know changes between this two versions.
 One of the query creating issue:

select count(*) COUNT_PRODUCTS
from (select d.ID,
             d.EAN,
             d.NAME,
             d.SAP_CATEGORY_ID,
             d.SAP_VENDOR_ID,
             d.RELEASE_DATE,
             d.REMOVED_DATE,
             d.IS_DRAFT,
             d.DATA_STATE,
             d.gc,
             ATTRIBUTE_CODE AS ATTRIB_NAME,
             ATTRIBUTE_VALUE AS APP_STATUS
      from (select b.ID,
                   b.EAN,
                   b.NAME,
                   b.SAP_CATEGORY_ID,
                   b.SAP_VENDOR_ID,
                   b.RELEASE_DATE,
                   b.REMOVED_DATE,
                   b.IS_DRAFT,
                   b.DATA_STATE,
                   group_concat(pl.LIFECYCLE_VALUE) gc
            from  (select a.ID,
                          a.EAN,
                          a.NAME,
                          a.SAP_CATEGORY_ID,
                          a.SAP_VENDOR_ID,
                          a.RELEASE_DATE,
                          a.REMOVED_DATE,
                          a.IS_DRAFT,
                          a.DATA_STATE
                   from (select p.ID,
                                p.EAN,
                                p.NAME,
                                p.SAP_CATEGORY_ID,
                                p.SAP_VENDOR_ID,
                                p.RELEASE_DATE,
                                p.REMOVED_DATE,
                                p.IS_DRAFT,
                                p.DATA_STATE
                          from PRODUCTS as p
                          order by p.EAN, p.IS_DRAFT) as a
                   group by a.EAN ) as b
            left join PRODUCT_LIFECYCLE_STATES pl
               on pl.PRODUCT_ID = b.ID  group by b.ID ) as d
       left join PRODUCT_ATTRIBUTE_VALUES AS PRODAV
          ON ID=PRODAV.PRODUCT_ID
         AND PRODAV.ATTRIBUTE_VALUE_ID IN (select ID
                                           from ATTRIBUTE_VALUES
                                           where ATTRIBUTE_CODE='APPROVED_ATTRIBUTES')
       left join ATTRIBUTE_VALUES AS ATVALS
          ON PRODAV.ATTRIBUTE_VALUE_ID = ATVALS.ID
         AND ATVALS.ATTRIBUTE_CODE ='APPROVED_ATTRIBUTES'
       ORDER BY d.SAP_CATEGORY_ID, d.NAME ) f

I did one change in my.cnf file to  suppress GROUP BY error by changing FULL_GROUP_BY.
This is my first quesion , so please let me know if any further information or rectification is required.

I am not able to reply to comments. Even after removing ORDER BY clause it is taking same time of hours. Actually, this query is also used without the COUNT in some places. If I keep FULL_GROUP_BY it is ending up with an error.

After running EXPLAIN on this query I am getting the different result in the old and new server.

New Server

Old Server

Do a JOIN instead of AND PRODAV.ATTRIBUTE_VALUE_ID IN (select ID ...

FULL_GROUP_BY needs to be heeded.

p:   INDEX(EAN, IS_DRAFT)  -- in this order
pl:  INDEX(PRODUCT_ID)
PRODAV:  INDEX(PRODUCT_ID, ATTRIBUTE_VALUE_ID)  -- in this order (after doing the JOIN)
ATVALS:  INDEX(ID, ATTRIBUTE_CODE)  -- in either order

PRODAV.ATTRIBUTE_VALUE_ID = ATVALS.ID -- If this is a key-value store, do not normalize the key or the value; it will make for terrible performance.

As for why the performance changed between revisions, I can only guess that things like FULL_GROUP_BY messed up the optimization.  Some of my suggestions may uncork it.


CAP Theorem vs. BASE (NoSQL) CAP Theorem vs. BASE (NoSQL)

Hi,
I’m trying to write a small paper for my work about NoSQL and have described the CAP Theorem as, if not all, then most NoSQL databases adheres to. I later read a paper about the difference between NoSQL and RDBMS which stated that NoSQL databases use the ACID counterpart BASE.

I know the properties of BASE, ACID, and CAP but I’ve a hard time figuring out what relation the CAP theorem and BASE has to each other. As I understand it, the CAP theorem leads to the BASE acronym but is it the right conclusion? Or is it two different approaches to “built” a database around which share some similar properties??

In this blog post (http://www.johndcook.com/blog/2009/07/06/brewer-cap-theorem-base/) the author writes:

  It’s harder to develop software in the fault-tolerant BASE world compared to the fastidious ACID world, but Brewer’s CAP theorem says you have no choice if you want to scale up. However, as Brewer points out in this presentation, there is a continuum between ACID and BASE. You can decide how close you want to be to one end of the continuum or the other according to your priorities.

In another article the author writes:

  Many of the NOSQL databases above all have loosened up the requirements on Consistency in order to achieve better Availability and Partitioning. This resulted in systems know as BASE (Basically Available, Soft-state, Eventually consistent). These have no transactions in the classical sense and introduce constraints on the data model to enable better partition schemes (like the Dynamo system etc). A more comprehensive discussion of CAP, ACID and BASE is available in this introduction.

This clearly state that CAP results in BASE.

I hope someone can clarify this to me and remove my confusion..

Thanks
- Mestika

CAP is basically a continuum along which BASE and ACID are on opposite ends.

CAP is Consistency, Availability, and Partition tolerance.  Basically you can pick 2 of those but you can't do all 3.

ACID focuses on Consistency and availability.

BASE focuses on Partition tolerance and availability and throws consistency out the window.


SQL Server 2005 Replication Stops after a minute without an error Background:

I have a SQL Server 2005 setup with master, slave1, slave2 replication set up as a pull replication from slaves. The distribution database resides on the slave1 machine, both slaves pull.

A problem began today where the replication on slave1 simply stops running. It claims that it completed successfully, but it does not restart, and manually starting the process finishes in roughly one minute, again without an error message.

Replication is running fine on slave2, but I can't seem to figure out what's wrong on slave1. I've tried the obvious Windows debugging 101: "restart the machine" technique, but to no avail.

Has anyone encountered this before Does anyone have an idea of what I could check or change to get it working again? I'm especially at a loss as SQL Server claims that the job is just finishing successfully.

SQL Server Replication is a notorious error hider.

Usually if replication just stops, a problem occurred and there is on place where you can see what that problem is. All other places you might look at show no sign of trouble.

Places to include when hunting for errors:

The replication monitor
The job history of each job involved (Look at the history. Even the SQL Agent Monitor hides a replication problem every once in a while.)
The SQL Server error log
The windows event log

3 and 4 are rarely needed. 2 is usually all you need to look at to find the problem if 1 did not help.

There is a lot more information about potential errors and how to identify them in my replication stairway articles. Particularly in Level 10.


Is this a good way to model course assignments? I'm having trouble designing a data model that allows for the following:

Courses can have many assignments.
Assignments can be defined recursively. e.g. "There is reading homework every tuesday".
Users can each complete an instance of the recurrent assignment at their own time and with their own grade and their own priority.

So, focusing on the assignment aspect of these requirements I came up with the following partial ER model: (It's missing how users are related to courses, teachers, etc...):

*There's a mistake in the picture, an event is related to 0 or 1 assignment

A teacher would then create an assignment for their course, let's say: 'Hand in math lab every tuesday due for tuesday next week'.

A single assignment will be created along with all the events for each occurrence.

When a user interacts with a specific event instance of the assignment. The assignmentUser will be created with the corresponding data.

Is this a good way to model this? I'm worried about the recurrent events side of this. Maybe it would be better to only have one event per assignment and generate all assignments that fit the recurrence rule on creation?

I like the gist of what you're doing. I think the entity names are a bit confusing though, and I would put some fields in different places. From my understanding of the system you're trying to build, here's a diagram I recommend:

In this model, Assignments have one AssignmentOccurrence for every time it occurs. If an Assignment occurs once, it would have one AssignmentOccurrence.

Take the values of the AssignmentOccurrence with a grain of salt, there's not enough information for me to know what data needs to be stored in there.


How to query chart data in a database agnostic way? I have several tables with data like this:

+---------------------+---------------+
| Creation Timestamp  | Numeric Value |
+---------------------+---------------+
| 2017-02-01 09:15:00 |        100.00 |
| 2017-02-01 11:10:00 |        150.00 |
| 2017-02-03 10:25:00 |         80.00 |
+---------------------+---------------+

and I need to query data for a chart, so I came up with this:

SELECT
    TRUNC_TO_DAY([Creation Timestamp]),
    COUNT(*),
    SUM([Numeric Value])
FROM
    [MyTable]
GROUP BY
    TRUNC_TO_DAY([Creation Timestamp])

which returns this results:

+------------+---+--------+
| 2017-02-01 | 2 | 250.00 |
| 2017-02-03 | 1 |  80.00 |
+------------+---+--------+

which are correct, by I need to fill the gaps too, like this:

+------------+---+--------+
| 2017-02-01 | 2 | 250.00 |
| 2017-02-02 | 0 |   0.00 |
| 2017-02-03 | 1 |  80.00 |
+------------+---+--------+

Some restrictions:

I can't create additional (real) tables nor views;
I've already implemented truncation functions, like this:

In SQL Server, function TRUNC_TO_DAY:

DATEADD(DAY, DATEDIFF(DAY, 0, ?), 0)

the query must work with different truncation unit (year, month, day, hour, minute);
the value to fill the gap too must be configurable (i.e. 0 or NULL).

I'm looking for a SQL Server solution primarily, but then I need to port it also to MySQL so any suggestion on that too would be very appreciated.

I have got something that I believe solves the problem, however I have not worked with dynamic truncation:

  the query must work with different truncation unit (year, month, day,
  hour, minute);

Basically I generate a table with random date and random values, and from that table #foo, I create the report with the help of table #bar.

I have only done partition by date, not year, month or hour or minute, but I guess it could follow the same logic.

everything else should be fine.

-- some topics:
-- how to check if a temp table exists?
-- create a random datetime
-- create a random money
-- checksum
-- newid
-- create a computed column
-- tally table
-- sp_executesql with parameters
-- cte
-- getting rid of duplicates

use tempdb
go

IF OBJECT_ID('tempdb..#foo') IS NOT NULL
    drop table #foo

IF OBJECT_ID('tempdb..#bar') IS NOT NULL
    drop table #bar

-- creates the temp tables

create table #foo (
        creation datetime not null,
        value money)
go

create table #bar (dt date primary key clustered)
go

-- insert 1000 rows into the table
-- there might be cases when we get a duplicate value but rarely

SET NOCOUNT ON
INSERT INTO #FOO(CREATION,VALUE)
SELECT DATEADD(MILLISECOND, ABS(CHECKSUM(NEWID()) * 45000.98 % 3650), DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2001-01-01')) AS RANDOM_DATE,
       ABS(CHECKSUM(NEWID()) * 45000.98 % 3650) AS RANDOM_MONEY
GO 1000

--- add a date column to the #foo table so that we can use it for joints later on
ALTER TABLE #FOO
ADD DT AS CONVERT(DATE,CREATION)
PERSISTED
GO

-- add a clustered index on the dt column
CREATE CLUSTERED INDEX CI_FOO ON #FOO(DT)
GO

--get the min date and the max date to build a tally table

declare @min datetime
declare @max datetime
DECLARE @TOTAL INT

DECLARE @sql        nvarchar(4000),
        @paramlist  nvarchar(4000)

select @total=datediff(day,[min],[max]),
       @min = [min],
       @max = [max]
from (
        select min (creation) as [min],
               max(creation) as [max]
        from #foo ) x

--convert @min to date
--using a tally table adds all dates to the end
--save them into table #bar

select [min]=@min,
       [max]=@max,
       [total]=@TOTAL

SELECT @SQL = '
;WITH    cteTally
          AS (SELECT TOP 500000
                ROW_NUMBER() OVER (ORDER BY AC.[name]) AS n
              FROM
                sys.all_columns AS AC CROSS JOIN
                sys.all_columns AS AC2  )

SELECT TOP ( ' + CAST (@TOTAL AS VARCHAR) + ' ) DATEADD(DAY,N-1,CONVERT(DATE,@MIN)) AS DT FROM cteTally '

SELECT @paramlist = '@min  datetime'

INSERT INTO #bar
exec sp_executesql @statement=@sql,@paramlist=@paramlist,@min=@min

-- by now we have the tables and the data to do our query

-- just having a look at the table here
SELECT *
FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.DT

-- prepares all the calculations that we need
WITH RADHE AS (
SELECT THE_ROW=ROW_NUMBER() OVER(PARTITION BY B.DT ORDER BY B.DT),
       THE_DATE=B.dt,
       THE_NUMBER_OF_RECORDS_ON_THIS_DAY=CASE WHEN F.DT IS NULL THEN 0 ELSE COUNT(*) OVER (PARTITION BY F.DT ) END ,
       THE_TOTAL_VALUE_FOR_THE_DAY=COALESCE(SUM(F.VALUE) OVER (PARTITION BY b.DT ),0)

FROM #BAR B
LEFT OUTER JOIN #FOO F
ON B.dt = F.dt
)

--get rid of the duplicates and present the result
SELECT
THE_DATE,
THE_NUMBER_OF_RECORDS_ON_THIS_DAY,
THE_TOTAL_VALUE_FOR_THE_DAY
FROM RADHE
WHERE THE_ROW = 1
--OPTION (RECOMPILE,MERGE JOIN)

--- drops the temp tables
IF OBJECT_ID('tempdb..#foo') IS NOT NULL
    drop table #foo

IF OBJECT_ID('tempdb..#bar') IS NOT NULL
    drop table #bar


Integrating intranet web logins with AD emails For various reasons, one of our corporate intranet sites in development cannot use Active Directory groups or passwords. Users logins will be stored within a MSSQL database instead.

However, we do want the ability to send email notifications to these users using their AD registered email address. All users will exist in AD.

So my general question is what is the better approach:

Use SQL to query the AD for emails when the web application wants to notify selected user(s)
Store the email addresses in the MSSQL database and update records via a batch process each night?

The first seems to have less duplication, but performance could be slower (and vice-versa). Is there another method?

Edit

To answer your questions, the emails will generally be initiated by other users. For instance, after modifying a document the editor will have the option to send a notification to be sent immediately. The plan was to have the ASP.NET application handle sending mail. In terms of volume, peak usage would be around 200-300 a day.

It would appear that the responsibility of sending the email lies with the ASP.NET application not the database.

I would perform a lookup of the users email from AD within the ASP.NET application and generate the email that way.


A User has Paths, which have Blocks I'm wondering if I'm doing this right. I have the following tables

Users
Paths
Blocks

  A Path consists of blocks, A User has one Path, A Block can only belong to one path (so no many-to-many between blocks and paths is needed

This is my blocks table

+----+---------+-------------+
| id | path_id | description |
+----+---------+-------------+
|  1 |       1 | block 1     |
|  1 |       2 | block 2     |
+----+---------+-------------+

I have the following scheme for the user_paths

+---------+---------+
| path_id | user_id |
+---------+---------+
|       1 |       1 |
|       1 |       2 |
+---------+---------+

And this is my user_blocks table

+----------+---------+-----------+
| block_id | user_id | completed |
+----------+---------+-----------+
|        1 |       1 | true      |
|        2 |       1 | true      |
+----------+---------+-----------+

This is my 'simplified' scheme in picture

Now I'm wondering if this is the right way to go about this? Because

Blocks belong to Paths, but still I'm joining the blocks with the Users table straight.

I feel like this isn't right?

But otherwise it isn't possible to check wich user has completed which block, right?

Any idea's or tips?
Thank you!

If 2 tables are 1:1, then probably they should be a single table.  If 1:many, then 2 tables and the 'many' table has the id of the other table.  many:many requires a 3rd table.

It sounds like you have 1:many, so you don't need the user_paths table.

More on optimal schema for many:many.


Getting 'Connection refused' after installing PostgreSQL and PG Admin 4 I had installed PostgreSQL Server on a machine. I had installed PGAdmin4 on another machine but when trying to connect, I am getting error on the startup of the PG Admin4 'Connection refused'.

How can I get it working?  I have just started using PostgreSQL.

Edit postgresql.conf and change listen_addresses = 'localhost' to listen_addresses = '*'
Edit pg_hba.conf file and add an entry for the IP/network you are connecting from.
To allow anyone to connect add:

host    all             all             0.0.0.0/0            md5

Only one IP (10.0.0.10):

host    all             all             10.0.0.10/32            md5
Change firewall settings on the server to allow connections to port 5432.


What kind of time should I be hoping to achieve inserting 300k records into a SQLite DB? I've written an app in UWP for Windows Phone 10.

When the app starts, I need to insert 307,750 records into a table. It's currently taking approximately 120 seconds to complete - I've wrapped it in a transaction.

I've been trying to improve performance changing various things but nothing seems to get it any faster than it currently is. Some of the blogs I've read about performance suggest using transactions but I'm already doing this.

does anyone know if this insert time is about the best I'm going to get? Example of the SQL below:

BEGIN;
INSERT INTO Point (X, Y, PolygonId)
VALUES (1,1,1) ,(1,1,2), (1,1,3), ...300,000 more...
COMMIT;

Things to improve performance of multiple inserts in SQLITE:

wrap in a transaction

You already done it. This is the first and most important thing.

Don't use big sentences

You can split the insert statement in serveral

INSERT INTO Point (X, Y, PolygonId)
  VALUES (1,1,1) ,(1,1,2), (1,1,3), ...100 or less... ;
INSERT INTO Point (X, Y, PolygonId)
  VALUES (2,1,1) ,(2,1,2), (2,1,3), ...100 or less...;
... 10.000 or more statements

Use prepared statements

If the API allows that you can use prepared queries:

INSERT INTO Point (X, Y, PolygonId) values ($1, $2, $3);

and send the data separately


Configuring SQL Server 2016 to allow remote connections I have SQL Server 2016 installed on Windows Server 2016 Core.  Locally, it seems to be working.  I can connect using SQLCMD.exe and do some basic selects and what not.

From remote, I cannot connect.  Using SQLCMD.exe on a remote machine:

sqlcmd -S boldiq_db3
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes
    Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout
    expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related
    or instance-specific error has occurred while establishing a connection
    to SQL Server. Server is not found or not accessible. Check if instance
    name is correct and if SQL Server is configured to allow remote
    connections. For more information see SQL Server Books Online..

And from SQL Server Management Studio:

In the installation INI I installed from, I have:

  TCPENABLED=1

So, I believe TCP is enabled.  It might be a Firewall issue, but I've run:

  netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

Which appears to have completed successfully.  Are there any other steps to diagnose where the problem is happening?  Thanks!

Figured it out!  Since the server is running on a remote VMWare ESXi instance, the subnets are completely different.  I changed:

  netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

To:

  netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = any profile = DOMAIN

And everything immediately started working.  Ugh, firewalls.


SSAS - Calculation in DSV or in view from data source This wasn't an easy question to Google, a lot of word overlap.

My table has a column with a date. I'd like another column that is a calculation on that date. I can do this with a Named Calculation on the dsv. Or, I could do this in the view from the data source that feeds this table into SSAS. Trying to figure out which would be better.

This calculated column will be for a dimension attribute.

Adding calculations either in DSV or in a view do not have much difference, it is just a choice, where you wish to maintain your SQL code. You can take a decision based on, which approach is best suited for you, from maintenance point of view.

following link can be helpful:
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/42215fd0-9cc4-4463-b739-de116f1d38be/named-calculations-vs-calculated-members?forum=sqlanalysisservices


SQL server Installation I have received a new server. The server has the following specification.
It has 500 gig 7.2K RPM disk,
 SSD
 NVME
1.2 TB  10K RPM disk.

All are in RAID 1.

I am planning to install Windows Sever on the 7.2K RPM disks. I want to install SQL Server on the OS drive( i.e. 7.2K RPM drive). I am planning to keep log and data files on SSD and NVME. The 10k RPM drive will be used for backups.
NVME drive is faster than SSD.
My Windows team recommend that I should install Windows Server on the SSD or 10K drives for performance. But all I care is about SQL Server performance. I don't think that Windows Server will perform badly on the 7.2k drive.

Does anyone see a reason to install Windows Server on any drive other than the 7.2K RPM drives?

In short, put the OSes on slow storage and all the data/log & backup files on fast storage.

I know that my environment is different that yours, but I think similar principles apply.  We have a SAN and the storage pools range in different speeds, 7.2k, 10k, 15k.

We install the OSes on the slower drive pools and have all backup files and data/log files stored on the 15k drives.  There was a time when we accidentally put everything on the 15k drives and we didn't notice a performance difference.  It was brought to our attention by our SAN admin after an audit that the OS was stored on the faster drives.

We monitor SQL Server performance with perfmon and Dell Spotlight.  For grins, we went and looked at the historical metrics that mattered, and saw nothing to note.


MongoDB: why does a DB with 0 connections still get "commands" and "getMores"? I'm in the process of migrating an old mongoDB database.
Before shutting down the old one, I want to make sure nothing is still writing to it or reading from it. Looking at Compose's MongoDB classic monitoring tool, the graph indicates 0 inserts, 0 queries, 0 updates and 0 deletes BUT, also a steady stream of "getMores" and "commands".

I assume the commands must be internal processes of MongoDB, but which ones? What's going on? Also, from the bit of search I've made, it seems that getMores have something to do with cursors, what internal process would be using cursors?

Since this is a replica set, the getMores are likely due to replication. Even with no write activity, replica set members will poll for updates on the oplog.rs collection.

The commands are probably related to monitoring (for example, serverStatus to get the opcounters that are being plotted).


Azure SQL Database Elastic Pools - TSQL to get current Elastic Pool I would like to be able to fetch the name of the elastic pool that the current database belongs to using TSQL  but the only DMV I can find with a column containing elastic is sys.elastic_pool_resource_stats
& I don't see any reference to databases there. I also checked sys.databases to see if an extra column got sneaked on there but nothing I could recognise.
Ideally this should work like @@servername or db_name()
(when on earth will they get around to creating server_name() or @@dbname so that we can code consistently ?)
Does anyone know a suitable command ?
(btw no tag for "elastic pools")

What about this?
-- TSQL to find Databases, corresponding elastic pool names and DB edition

SELECT
       @@SERVERNAME as [ServerName],
       dso.elastic_pool_name,
       d.name as DatabaseName,
       dso.edition
FROM
       sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id
WHERE d.Name <> 'master'
ORDER BY
       d.name, dso.elastic_pool_name


what is the quickest way to check whether a XML column is empty ( @xml = '') in the where clause? following the conclusion of the question
how to test if XML = '' in sql server?

to cut a long story short there is a procedure which has one of its parameters a XML variable @ListNoteTypeCode.

It is a XML because it can contain more than one value and I am using sql server 2005.

there are 2 ways to check whether or not the @ListNoteTypeCode parameters is empty or contain value(s).

1) @ListNoteTypeCode is NULL OR @ListNoteTypeCode.exist('*') = 0

2) @ListNoteTypeCode is NULL OR DATALENGTH(@ListNoteTypeCode) = 5

which one performs better?

I was intrigued as which one would perform better so I did a test and would like to share the results here.

I create a table to keep put some data in XML and for testing later on

USE MY_TEST_DATABASE
GO

--drop table radhe_01
--GO

CREATE TABLE RADHE_01 ( i int not null identity(1,1) primary key clustered,
                        MYXML XML NOT NULL
                       )
GO

Ops, the MYXML column should be able to accept nulls - need to change it

ALTER TABLE RADHE_01
ALTER COLUMN MYXML XML NULL

I create a procedure in order to create random values to be added to MYXML column

I want to add one million records to that table

CREATE PROCEDURE [dbo].[SpGenerateRandomString]
@sLength tinyint = 10,
@randomString varchar(50) OUTPUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @counter tinyint
DECLARE @nextChar char(1)
SET @counter = 1
SET @randomString = ''

WHILE @counter <= @sLength
BEGIN
SELECT @nextChar = CHAR(48 + CONVERT(INT, (122-48+1)*RAND()))

IF ASCII(@nextChar) not in (58,59,60,61,62,63,64,91,92,93,94,95,96)
BEGIN
SELECT @randomString = @randomString + @nextChar
SET @counter = @counter + 1
END
END
END

I test the procedure to check it is working nicely

declare @randomString varchar(50)
exec SpGenerateRandomString 30, @randomString output
select @randomString

I insert one million rows into the table.

I kept my XML format as I have in the live procedure.

This XML has 2 values, I forgot about this detail and left the first value there GarmentComposition.

set nocount on
declare @randomString varchar(50)
declare @i int

select @i =1
while @i < 1000000 begin

      exec SpGenerateRandomString 30, @randomString output
      insert into radhe_01 (myxml) values (N'<BulletPoint><NoteTypeCode>GarmentComposition</NoteTypeCode><NoteTypeCode>' + @randomString  + '</NoteTypeCode></BulletPoint>')
      select @i = @i +1
end

I play with the data, in order to test the results later on,
I will change some rows to NULL and some rows to empty ''

SELECT COUNT(*) FROM
RADHE_01
WHERE I%117=0
--8547

SELECT COUNT(*) FROM
RADHE_01
WHERE I%1217=0
--821

SET NOCOUNT OFF
UPDATE RADHE_01
SET MYXML = ''
WHERE I%117=0
-- 8547 WILL BE EMPTY
UPDATE RADHE_01
SET MYXML = NULL
WHERE I%1217=0
-- 821 WILL BE NULL
SET NOCOUNT ON

--(8547 row(s) affected)

--(821 row(s) affected)

Now doing the test

I have run both queries as you can see on the script below.

set statistics io off
set statistics time off

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

set statistics io on
set statistics time on

--find the number of records that are null or empty - using XML.exists
select t.*
from RADHE_01 t
where t.MYXML.exist('*') = 0 OR t.MYXML IS NULL

set statistics io off
set statistics time off

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

set statistics io on
set statistics time on

--find the number of records that are null or empty - using datalength
select t.*
from RADHE_01 t
where DATALENGTH(t.MYXML) = 5 OR t.MYXML IS NULL

set statistics io oFF
set statistics time oFF

The result of the statistics TIME and statistics IO are as follows:

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Table 'RADHE_01'. Scan count 1, logical reads 23902, physical reads 3, read-ahead reads 23445, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2187 ms,  elapsed time = 2646 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Table 'RADHE_01'. Scan count 5, logical reads 24132, physical reads 1, read-ahead reads 23477, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 1853 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

This is the execution plan (picture) from sql server

This is the full XML execution plan of the query and picture above.

This is another picture using another tool to compare execution plans.

Conclusion:

Using DATALENGTH on this occasion, for this situation, has been proven quicker.


Is sharding effective for small collections? It looks like database sharding is great if I have huge collections. What if I have lots of fairly sized collections ? Let's say that for 1 collection of 100 000 000 documents (not very big comments) sharding is effective. Is it also effective for 10 000 collections with 10 000 documents each ?

(I think this question is still valid for table oriented databases if you replace collections with tables and documents with rows. If possible I'd like to know the theoretical answer as well as the answer in the specific MongoDB scenario, if different from the theoretical answer.)

Is it also effective for 10 000 collections with 10 000 documents each ?

Most people have the "single large collection" problem and so the sharding is clearly useful for reducing headaches of balancing this data.

However, when you have 10 000 small collections, your headache is probably not "balancing the data". With this many small collections your problem is likely about tracking these collections. Depending on your document size, you may not even break the lower limit for sharding to actually happen.

For the really small collections, you can use the little-known movePrimary command to manage the location of your data.

Of course, the other way to look at this is why do you have 10k collections? A collection doesn't need homogeneous objects and with 10k collections most of them have to be generated. It's quite possible to store different "types" of data in the same collection, reduce the number of collections and then include the type as part of the shard key.


When a previously-fast SQL query starts running slow, where do I look to find the source of the issue? Background

I have a query running against SQL Server 2008 R2 that joins and/or left-joins about 12 different "tables". The database is fairly large with many tables over 50 million rows and about 300 different tables. It's for a large-ish company that has 10 warehouses across the country. All the warehouses read and write to the database. So it's pretty large and pretty busy.

The query I'm having trouble with looks something like this:

select t1.something, t2.something, etc.
from Table1 t1
    inner join Table2 t2 on t1.id = t2.t1id
    left outer join (select * from table 3) t3 on t3.t1id = t1.t1id
    [etc]...
where t1.something = 123

Notice that one of the joins is on a non-correlated sub-query.

The problem is that starting this morning, without any changes (that I or anybody on my team knows of) to the system, the query which usually takes about 2 mins to run, started taking an hour and a half to run -- when it ran at all. The rest of the database is humming along just fine. I've taken this query out of the sproc that it usually runs in and I've run it in SSMS w/ hard-coded parameter variables with the same slowness.

The strangeness is that when I take the non-correlated sub-query and throw it into a temp table, and then use that instead of the sub-query, the query runs fine. Also (and this is the strangest to me) if I add this piece of code to the end of the query, the query runs great:

and t.name like '%'

I've concluded (perhaps incorrectly) from these little experiments that the reason for the slow-down is due to how SQL's cached execution plan is set up -- when the query is a little different, it has to create a new execution plan.

My question is this: When a query that used to run fast suddenly starts running slowly in the middle of the night and nothing else is affected except for this one query, how do I troubleshoot it and how do I keep it from happening in the future? How do I know what SQL is doing internally to make it so slow (if the bad query ran, I could get its execution plan but it won't run -- maybe the expected execution plan would give me something?)? If this issue is with the execution plan, how do I keep SQL from thinking that really crappy execution plans are a good idea?

Also, this is not a problem with parameter sniffing. I've seen that before, and this is not it, since even when I hard-code the varaibles in SSMS, I still get slow performance.

When a query that used to run fast suddenly starts running slowly in the middle of the night and nothing else is affected except for this one query, how do I troubleshoot it...?

You can start by checking if the execution plan is still in the cache. Check sys.dm_exec_query_stats, sys.dm_exec_procedure_stats and sys.dm_exec_cached_plans. If the bad execution plan is still cached you can analyze it, and you can also check the execution stats. The execution stats will contain information as logical reads, CPU time and execution time. These can give strong indications what the problem is (eg. large scan vs. blocking). See Identifying problem queries for an explanation how to interpret the data.

  Also, this is not a problem with parameter sniffing. I've seen that before, and this is not it, since even when I hard-code the varaibles in SSMS, I still get slow performance.

I'm not convinced. Hard-coding variables in SSMS does not prove that the past bad execution plan was not compiled against a skewed input. Please read Parameter Sniffing, Embedding, and the RECOMPILE Options for a very good article on the topic. Slow in the Application, Fast in SSMS? Understanding Performance Mysteries
is another excellent reference.

  I've concluded (perhaps incorrectly) from these little experiments that the reason for the slow-down is due to how SQL's cached execution plan is set up -- when the query is a little different, it has to create a new execution plan.

This can be easily tested. SET STATISTICS TIME ON will show you the compile vs. execution time. SQL Server:Statistics performance counters will also reveal whether compilation is an issue (frankly, I find it unlikely).

However, there is something similar that you may hit: the query grant gate. Read Understanding SQL server memory grant for details. If your query requests a large grant at a moment no memory is available, it will have to wait, and it will all look as 'slow execution' to the application. Analyzing wait info stats will reveal if this is the case.

For a more general discussion about what to measure and what to look for, see How to analyse SQL Server performance


Postgres trigram match acting strange for specific characters I'm using pg_trgm to run a query, but I'm getting a lot of 1 matches when searching with a symbol difference. I have the following query:

SELECT my_column, similarity('$ Hello', my_column) AS sml
FROM my_table
WHERE my_column % '$ Hello'
ORDER BY sml DESC, my_column;

In my_table, I have the following:

- Hello
? Hello
| Hello
$ Hello
! Hello
!? Hello

They all return with a similarity match of 1. Do I need to escape the '$' or something similar?

SELECT show_trgm('$ Hello');

            show_trgm
---------------------------------
 {"  h"," he",ell,hel,llo,"lo "}
(1 row)

SELECT show_trgm('- Hello');

            show_trgm
---------------------------------
 {"  h"," he",ell,hel,llo,"lo "}
(1 row)

All non-alphanumeric characters are stripped from a string before the trigrams are calculated.

That's why the strings have similarity 1.

You cannot use trigram indexes for searching for symbols.

The documentation has it in its first sentence (emphasis mine):

  The pg_trgm module provides functions and operators for determining the similarity of alphanumeric text based on trigram matching


Determine the SQL Server version and edition from the available set up, before actually installing it? We are going through a downgrade process from SQL Server 2008 R2 Enterprise to Standard edition.

To kick off the above activity, we were looking for available Standard Edition of SQL Server. We found one, but got confused on below points:

The setup files (saved by an earlier team member) are in a folder that says it is SQL Server 2008 R2, but just to cross check before we start installing, we went through the default.ini file, which says:

SQLSERVER2008 Configuration File
[SQLSERVER2008]

This created a doubt if this is the correct version of SQL Server required. So is there any other way, in case above mentioned method is incorrect to determine the version, before we actually go ahead and try installing?
Can we also check the edition of that available SQL Server, because on the set up, we can hardly figure out if it is really Standard Edition?

We cannot simply download new media, per our security policies. Also, we have no rights to download.

To answer your first question :

If you are not sure, then just manually  launch the setup.exe and verify.

When you launch SQL Server 2008 installation using setup.exe, you will see:

Below is for SQL Server 2008R2 :

An alternative to running the setup executable, is to right click and look for the version there. It'll be something like 10, 10.50, 11, etc. so you do need to convert

To answer your second question :

From SQL Server 2008 and up, Microsoft has changed as the edition depends on the PID that can be found in DefaultSetup.ini. The DefaultSetup.ini for SQL Server 2008 and 2008R2 has same format. So essentially if you don't know, you have to make sure that you have the correct installation files.

;SQLSERVER2008 Configuration File
[SQLSERVER2008]
PID="xxxxx-xxxxx-xxxxx-xxxxx-xxxxx"

For SQL Server 2005, it was stated in the Servers folder default.htm. See

How to tell what edition your SQL Server setup files are

I don't think you can guess from the PID a particular edition of SQL Server. A full media will be good for all editions of a particular SQL Server version.