Multi Select Statement With inner join multiple table user table

id   u_id   name   phone       email
1    912    abcd   23424    abcd@email.com
2    913    xyz    45342     xyz@email.com
3    916    pqrt   23239    wdiw@email.com

subject table

s_id   name
    1     eng
    2     math
    3     phy

city table

c_id   name
    1     city1
    2     city2
    3     city3

location table

l_id   name
    1     loc1
    2     loc2
    3     loc3

user_to_subject table

u_id    s_id
913      2
913      3

user_to_city table

u_id    c_id
913      1
913      3

user_to_location table

u_id    l_id
913      2
913      3

What I am trying to do is to select one statement with complete details. This is what I have tried.

SELECT U.*, GROUP_CONCAT(S.`name`) AS subject, GROUP_CONCAT(C.`name`) AS class,
GROUP_CONCAT(L.`name`) AS location
FROM `user` U
  INNER JOIN `user_to_subject` UTS ON U.`u_id`=UTS.`u_id`
  INNER JOIN `subjects` S ON S.`s_id`=UTS.`s_id`
  INNER JOIN `user_to_class` UTC ON U.`u_id`=UTC.`u_id`
  INNER JOIN `city` C ON C.`c_id`=UTC.`c_id`
  INNER JOIN `user_to_location` UTL ON U.`u_id`=UTL.`u_id`
  INNER JOIN `location` L ON L.`l_id`=UTL.`l_id`
WHERE U.`u_id`=913

I got duplicate subjects, cities and location. Not sure what I am doing wrong here.

Here is the answer

SELECT U.*,
       GROUP_CONCAT(DISTINCT S.`name`) AS subject,
       GROUP_CONCAT(DISTINCT C.`name`) AS class,
       GROUP_CONCAT(DISTINCT L.`name`) AS location
FROM `user` U INNER JOIN
     `user_to_subject` UTS
     ON U.`u_id` = UTS.`u_id` INNER JOIN
     `subjects` S
     ON S.`s_id` = UTS.`s_id` INNER JOIN
     `user_to_class` UTC
     ON U.`u_id` = UTC.`u_id` INNER JOIN
     `class` C
     ON C.`c_id` = UTC.`c_id` INNER JOIN
     `user_to_location` UTL
     ON U.`u_id` = UTL.`u_id` INNER JOIN
     `location` L
     ON L.`l_id`=UTL.`l_id`
WHERE U.`u_id` = 913;

Find the fiddle here. http://sqlfiddle.com/#!9/cbbbda/1


When can a runtime exception occur during commit? I've been asked this question but neither I seem to be able to answer it on my own, nor can I find anything related on the web. So what are the cases that might cause a runtime exception when committing a transaction in Oracle?The only thing that I can think of is the low disk space. Are there any other?

I posted the same question to Oracle community and the answer was deferred constraints.


Find out CPU usage by reporting server Is there a way to found out what is the CPU usage of the reporting server? Sometimes the CPU usage is rised up by 40% percent for several seconds. I think the reporting server is causing it but I am not able to track it down.

I have try different techniques:

SQL Server Performance Dashboard Reports
sp_whoisactive
Activity top session report
this query
looking at ExecutionLog3 View

The major problem is that the CPU usage value is raised from 20 to 60 percent for several seconds only. A lot of queries are executed and I am not able to identify which ones belongs to the reporting server.

Also, a bit of offtopic, but I have read that is a good practice to use the reporting server of the secondary databases when you have Always on Availability Group - is there any other licence requirements?

Regarding running reports from an Availability Group secondary server, you will need to license that instance of SQL Server.  Since the AG requirements require identical equipment, your secondary's licensing cost will reflect the primary server's licensing cost

Using the secondary server strictly for High Availability/Disaster Recovery does not require licensing the secondary server since it is only in use when the primary fails.

Regarding the brief spikes in CPU utilization, is it causing you a problem now?  Since it is hard for you to see who is responsible, consider loading sp_whoisactive from Adam Machanic's site: http://whoisactive.com  This stored procedure has numerous options to control what information you can query.

Brent Ozar also has an article on using sp_whoisactive to track issues within your system, logging the output to a file for later review.  http://www.brentozar.com/responder/log-sp_whoisactive-to-a-table/

Perhaps these tools will help you find out what is happening.


Oracle 19c runInstaller reports PRVF-5311 while checking SSH connectivity I am trying to install Oracle 19c RAC on three nodes, running RHEL 8.3. GRID installation was ok. But, Oracle DB SW install using runInstaller(GUI) is stuck with this issue while checking for SSH connectivity between nodes.
Error Reported:
Cause - Either passwordless SSH connectivity is not setup between specified node(s) or they are not reachable. Refer to the logs for more details.  Action - Refer to the logs for more details or contact Oracle Support Services.  More Details
PRVF-5311 : File "/tmp/InstallActions2020-12-24_01-17-15PM/xyzsvr.getFileInfo184599.out" either does not exist or is not accessible on node "xyzsvr".
When I tried runcluvfy, it reports ok.
$ /u01/app/19.0.0/grid/runcluvfy.sh comp admprv -n xyzsvr1,xyzsvr2,xyzsvr3 -o user_equiv -sshonly -verbose
Verifying User Equivalence ...
Node Name                             Status

xyzsvr2                              passed
xyzsvr3                             passed
Verifying Checking user equivalence for user "oracle" on all cluster nodes ...PASSED
From node     To node                   Status

xyzsvr2      xyzsvr1                  SUCCESSFUL
xyzsvr2      xyzsvr3                 SUCCESSFUL
xyzsvr3     xyzsvr1                  SUCCESSFUL
xyzsvr3     xyzsvr2                  SUCCESSFUL
Verifying User Equivalence ...PASSED
Verification of administrative privileges was successful.
CVU operation performed:      administrative privileges
Date:                         Dec 24, 2020 1:44:57 PM
CVU home:                     /u01/app/19.0.0/grid/
User:                         oracle
There is nothing in Oracle support site.

Try below solution:
Rename the original scp. ( find where is the scp executable file present)
mv /usr/bin/scp /usr/bin/scp.orig
Create a new file .
vi /usr/bin/scp
Add the below line to the new created file .
/usr/bin/scp.orig -T $*
Change the file permission.
chmod 555 /usr/bin/scp
Once installation is done , you can revert to the original
mv /usr/bin/scp.orig /usr/bin/scp
REFER- >
https://dbaclass.com/article/upgrade-grid-infrastructure-from-oracle-12c-to-19c/


What could lead to excesive PAGEIOLATCH_EX I'm having some delays when insert into a table. The funny thing is that it doesn't always happen.
Basically it's part of a load test that I created to compare some new features. I run the store procedure for a bunch of lists and there are some lists that always get delayed taking 3 hours to end and other that always are executed in a acceptable time (10 minutes).
At this point, look at performance monitor and the disk is reading at 1mb/s, so there isn't any of high load on the disk.
Also using extended events, look at "waits", saw the main wait is PAGEIOLATCH_EX, but as I said, the disk is reading at low rate, so what happen here?
CREATE TABLE SubscriberByList (
    SubscriberId INT NOT NULL
    ,ListId INT NOT NULL
    ,AddedAt DATETIME NOT NULL DEFAULT GETUTCDATE()
    ,Active BIT DEFAULT 1
    ,CONSTRAINT [PK_SubscriberByList] PRIMARY KEY CLUSTERED (ListId, SubscriberId)
    );

This table has 2.381.451.668 rows. A non clustered index on SubscriberId. SubscriberId and ListId are FK. Also a indexed view to count the amount of subscribers by each list.
On my test environment I created a procedure as following.
CREATE PROCEDURE [dbo].[LoadTest] @ListId INT
AS
BEGIN
    CREATE TABLE #Subscribers (SubscriberId INT, Active BIT, HashKey as convert(binary(32),HASHBYTES('SHA2_256',convert(varchar,SubscriberId))) PERSISTED,PRIMARY KEY CLUSTERED(HashKey))

    INSERT INTO #Subscribers (SubscriberId, Active)
    SELECT SubscriberId, Active
    FROM dbo.SubscriberByList sxl
    where sxl.ListId = @ListId

    DELETE TOP (1000) dbo.SubscriberByList
    WHERE ListId = @ListId

    WHILE @@ROWCOUNT = 1000
    BEGIN
        DELETE TOP (1000) dbo.SubscriberByList
        WHERE ListId = @ListId
    END

    DECLARE @Offset INT = 0
        ,@Amount INT = 1000
    INSERT INTO dbo.SubscriberByList (SubscriberId, ListId, Active)
    SELECT SubscriberId, @ListId,Active
    FROM (SELECT SubscriberId, Active
        FROM #Subscribers
        ORDER BY HashKey
        OFFSET @Offset ROWS
        FETCH NEXT @Amount ROWS ONLY
        ) x

    WHILE @@ROWCOUNT = @Amount
    BEGIN
        SET @Offset = @Offset + @Amount

        INSERT INTO dbo.SubscriberByList (SubscriberId, ListId, Active)
        SELECT SubscriberId, @ListId,Active
        FROM (SELECT SubscriberId, Active
            FROM #Subscribers
            ORDER BY HashKey
            OFFSET @Offset ROWS
            FETCH NEXT @Amount ROWS ONLY
            ) x
    END

    DROP TABLE #Subscribers
END

The delay is on INSERT INTO dbo.SubscriberByList.
The HashKey is to get the subscribers distributed. As a new test I remove the HashKey and use as PK the SubscriberId. With this change the performance is improved but this isn't as real as distributed insertion and also isn't as expected because it takes 1 hour to complete.
What could happen since one list take 10 minutes to process 2.7 millions of rows and the other with similar amount takes 3 hours (1 hour on the best case)?
Thanks!
EDIT 1:
Thanks for your comments, now I realize that delay also happen on the delete!
here are some stats using the suggested Paul Randal script, taking 3 minutes on each test
Fast Insert:

Slow Insert:

Fast Delete:

Slow Delete:

So, on slow operations there are more number of reads/writes, but a small number of bytes. Right?
Also the delete is on the primary key, why take more time for the same amount of subscribers?
Why is that?

Thanks GabSQL, I found on wait_complete the resource information, after running the test and capture the data, executed the following script to summarize how many times the pageiolatch_EX has to wait by each index.

;WITH cte
AS (
    SELECT ed = CONVERT(XML, target_data)
    FROM sys.dm_xe_session_targets xet
    INNER JOIN sys.dm_xe_sessions xe ON xe.[address] = xet.event_session_address
    WHERE xe.name = N'Track_wait_stat'
        AND xet.target_name = N'ring_buffer'
    )
SELECT resource_description = x.ed.query('.').value(N'(event/data[@name="wait_resource"]/value)[1]', N'varchar(20)')
INTO #t
FROM cte
CROSS APPLY cte.ed.nodes(N'RingBufferTarget/event') AS x(ed);

SELECT obj.name, obj.index_id, count(1) as Occurrences
FROM (
    SELECT Page_Id = right(t.resource_description, charindex(':', reverse(t.resource_description) + ':') - 1)
        ,databaseId = LEFT(t.resource_description, charindex(':', t.resource_description)-1)
        ,FileId = Substring(t.resource_description, charindex(':', t.resource_description) + 1, charindex(':', t.resource_description, charindex(':', t.resource_description) + 1) - charindex(':', t.resource_description) - 1)
    FROM #t t
    ) x
JOIN sys.dm_os_buffer_descriptors AS bd ON x.databaseId = bd.database_id AND bd.page_id = x.Page_Id
INNER JOIN (
    SELECT object_name(object_id) AS name
        ,index_id
        ,allocation_unit_id
    FROM sys.allocation_units AS au
    INNER JOIN sys.partitions AS p ON au.container_id = p.hobt_id
        AND (
            au.type = 1
            OR au.type = 3
            )

    UNION ALL

    SELECT object_name(object_id) AS name
        ,index_id
        ,allocation_unit_id
    FROM sys.allocation_units AS au
    INNER JOIN sys.partitions AS p ON au.container_id = p.partition_id
        AND au.type = 2
    ) AS obj ON bd.allocation_unit_id = obj.allocation_unit_id
GROUP by obj.name, obj.index_id
drop table #t

This info tels me the index who is waiting is the nonclustered with SubscriberId as key. This lead to look at fragmentation and it was of 47%, I was suprised because I have a job who do the maintenance, but for some tricky condition and a server migration few months ago, this index (and others too) where left behinde.

I rebuilded to get the fragmentation to 0 and restart the engine to flush out the cache (is a testing enviroment). After test it again a few times, the performance was improve, but  still there is a delay (10 min vs 30 min) and the waits are on the same index.

Something I noticed is that for the list that runs fast, it uses much less RAM (half), and since this a test enviroment, for the slow one it reach the maximum, so this could be the reason why it take a little more of to finish.

I think I could work with this delays now.

Thanks all.


Database design how to track a change on a patient row and set in a log table I need to track any update on my patients table.

The table contains the following fields:

Patient ID;
Name;
address;
phone
relative name;
relative phone
smoker;
alcoholic.

Now I need to track if a profile of a specific patient is changed in any field, so if he was smoker 2 months ago and now not, I want to see a log that the nurse changed the profile of the patient x from smoker to non-smoker.

First Approach

Now, should I create a similar table to this but it contains a foreign key connected to the initial table and each time an edit/update is made a row is added to the second table ?

Second Approach

Or I need to track every field as a table, so I will make a table for smoking history edit, alcoholic history status table and add a row into them whenever an update happened ?

When I have done similar in the past, I created a changed table. For yours I would keep the patient ID as a foreign key and then list the original value for the field and the new value for the field. You do not need to keep the whole record each time it changes, just a list of the changes. I am assuming you are not required to do so by any regulatory agency, in which case check and see what they require.

I also tracked who made the change and when. You can include a field asking them to justify the change. Good info to have in case there is a question later.


SQL Server Actual Plan. Incorrect row estimates While practicing in tuning queries on StackOverflow database I got stuck on the following one:

SELECT TOP 50 Id AS [User Link],
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 1 AND
          LastEditorUserId = Users.Id AND
          OwnerUserId != Users.Id
) AS QuestionEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE PostTypeId = 2 AND
          LastEditorUserId = Users.Id AND
          OwnerUserId != Users.Id
) AS AnswerEdits,
(
    SELECT COUNT(*)
    FROM Posts
    WHERE LastEditorUserId = Users.Id AND
          OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC;

The plan is here.

Execution stats:

(50 row(s) affected)
Table 'Posts'. Scan count 43217677, logical reads 172988050, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 5, logical reads 12692, physical reads 0, read-ahead reads 0, 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.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 701344 ms,  elapsed time = 192167 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

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

I have three questions here:

What does the operator 'Constant scan' refer to?
Why row estimates can be incorrect in the following blocks:

a.

b.

What to do to make estimates more accurate? What index/technique to consider to improve the query performance?

Any hint will be deeply appreciated.

The question is tough to answer because the query does not match the plan provided, and the query used to generate the plan is somewhat meaningless:

SELECT TOP 50 Id AS [User Link],
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 1 AND
    LastEditorUserId = Users.Id AND
    OwnerUserId != Users.Id
) AS QuestionEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE PostTypeId = 2 AND
    LastEditorUserId = Users.Id AND
    OwnerUserId != Users.Id
) AS AnswerEdits,
(
 SELECT top 1 1
 FROM Posts
 WHERE LastEditorUserId = Users.Id AND
    OwnerUserId != Users.Id
) AS TotalEdits
FROM Users
ORDER BY TotalEdits DESC

Nevertheless:

  What does the operator 'Constant scan' refer to?

A Constant Scan reads from an in-memory table of constant values. It may have one or more rows, and zero or more columns. Yes, a Constant Scan may have zero columns; it is often introduced by the optimizer simply as a source of (empty) rows, to which columns may be added by later operators (typically Compute Scalars).

To a large extent, Constant Scans are often purely architectural artifacts necessary for the correct operation of the query, or as part of an optimization.

When Constant Scans and Compute Scalars sit underneath a Merge Interval, this is a sure sign that these elements were introduced post-optimization to compute and collapse multiple logical intervals into a single interval suitable for driving an Index Seek on that range.

In this particular case, the Constant Scans represent the expansion of OwnerUserId <> Users.Id into:

OwnerUserId < Users.Id; OR
OwnerUserId > Users.Id

The post-optimization rewrite into the Merge Interval subtree is triggered by this x < y OR x > y predicate. The machinery tests the ranges for overlaps and collapses the resulting "merged interval" into start and end points that can be used with an index seek.

The following minimal example shows SQL Server rewriting the predicate in this way so an index seek can be generated:

CREATE TABLE #U (u integer NOT NULL);
CREATE TABLE #P (u integer NOT NULL PRIMARY KEY);

INSERT #U VALUES (1);
INSERT #P VALUES (0), (2);

SELECT
    U.u,
    (
        SELECT TOP (1) 1
        FROM #P AS P
            WITH (FORCESEEK)
        WHERE P.u <> U.u
    )
FROM #U AS U;

DROP TABLE #U, #P

The resulting Clustered Index Seek there has the properties:

The end result is scanning the index forward until the end point (excluding the target value) then restarting the scan just after the target value to the end of the index. [Expr1011] and [Expr1012] are the computed start and end points of the excluded range.

You can read more about this in my article Dynamic Seeks and Hidden Implicit Conversions.

  Why row estimates can be incorrect in the following blocks

SSMS shows estimates on the inner side of a nested loops join per iteration whereas the actual row counts are the total over all iterations. This is confusing to many people, and the result of a questionable design decision.

    Posted by Microsoft on 10/14/2009 at 2:47 PM
    The difference between estimated and actual that you refer to is not really a bug in the optimizer; it's a questionable design decision in the way we present information in showplan. This table is on the inner side of a loop join, and in that position the numbers we report for actual and expected rows don't mean the same thing: The actual row count includes all exections, while the estimated row count is per execution.

You can get a more natural comparison by viewing the plan in Sentry One Plan Explorer. It does the per-iteration calculations for you, so you can compare estimates and actuals more directly. For example, this is a view of one of the Merge Interval subtrees viewed in Plan Explorer:

  What to do to make estimates more accurate? What index/technique to consider to improve the query performance?

The difference in showplan display accounts for the first question. David's answer has addressed rewriting the query for better performance (and meaningful results).


Will a Transactional Replication break the LSN chain in my backups I need to create a fairly up to date read only copy of parts of a production database for reporting purposes.

I did look at log shipping to keep the copy up to date but this will break the LSN and render my backups unrestorable (Log backup every 15 minutes).

I reasonable confident that a Transactional Replication will fit the bill working from the Distribution Database and leaving the source unaffected but I can't find a definitive confirmation of this.

Can anybody put my mind at rest (or suggest something better!)

I did look at log shipping to keep the copy up to date but this will
  break the LSN and render my backups unrestorable (Log backup every 15
  minutes).

Technically, no they won't break the LSN. It simply means when restoring you would have to coordinate restores with both your managed backups and your log shipping backups. While this is a nightmare from a management overhead perspective, it won't actually break your LSN chain like say changing the recovery model would.

  I reasonable confident that a Transactional Replication will fit the
  bill working from the Distribution Database and leaving the source
  unaffected but I can't find a definitive confirmation of this.

Transactional Replication reads the transaction log and writes the transactions affecting tables marked for replication into the distribution database for distribution to your subscribers. This does not mark the transaction log except to mark those transactions it has already processed for future runs of the log reader agent to avoid duplication of work.

Transactional replication will not impact your managed backups, break the chain or affect the recoverability of your database from managed backups.


Learning COMPUTED BY - what right parenthesis does it need? I am reading this document on Automatic columns.
The first snippet in it creates a COMPUTED BY column:
create table PERSON (
(employee_id integer,
employee_id_disp computed by
SUBSTRING (CAST(employee_id + 100000 as VARCHAR (6)) from 2)
);

When I run this snippet, I get this error:
Error starting at line : 1 in command -
create table PERSON (
(employee_id integer,
employee_id_disp computed by
SUBSTRING (CAST(employee_id + 100000 as VARCHAR (6)) from 2)
)
Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:

And whey I try to create my own table as follows:
create table A (
  a1 varchar(4000) not null,
  a1Raw computed by UTL_RAW.cast_to_raw(a1)
)

I get a different error:
Error starting at line : 11 in command -
create table A (
  a1 varchar(4000) not null,
  a1Raw computed by UTL_RAW.cast_to_raw(a1)
)
Error report -
SQL Error: ORA-00907: missing right parenthesis
00907. 00000 -  "missing right parenthesis"
*Cause:
*Action:

I am not overly concerned about the snippet from the Oracle's PDF not working: it sometimes happens that documentation contains bugs. But why is my script not working? I cannot see where a right parenthesis may be possibly missing because all of them match.

Forget COMPUTED BY and just use AS which is the same as GENERATED ALWAYS AS ... VIRTUAL
Syntax summary is in the documentation.
There is no computed by clause and there never has been. OP was looking in the manuals for something called Oracle RDB that runs on OpenVMS and nobody uses.


adding cluster index other than primary key default cluster index create table t1 (id int primary key auto_increment, name varchar(20))

In the above table id is primary key and by default cluster index is added to primary key column.

My question is, can we add cluster index on column (name) too??.

can we add cluster index on column (name) too?

No. Only one index is clustered.

For MySQL:

If primary key exists it is clustered.
If no primary index but unique indexes exists then first unique index is clustered.
If no unique indexes exists then the index by inner record number (hidden, unaccessable) is clustered one.


SSIS Package Validation Error email task when attaching file created by previous task While running in debug mode an SSIS package I get the folowing error.
"file ... does not exist ...",  but the file is created by a task earlier in the process, so most probably it will exist at runtime but not at validation. How to deal with this ?

Package Validation Error
------------------------------
ADDITIONAL INFORMATION:

Error at Send Zip file [Send Mail Task]: Either the file "..." does not exist or you do not have permissions to access the file.

Error at Send Zip file: There were errors during task validation.

 (Microsoft.DataTransformationServices.VsIntegration)

------------------------------
BUTTONS:

OK
------------------------------

Pls make sure you have tried the solutions below

Set DelayValidation property set to True for the Send Mail Task.
Close the filestream of the log file before sending the email


SSIS - insert system date variable as a execute sql task parameter I am trying to insert the system:starttime variable into a table by passing a parameter into an execute sql task.

I dont have much knowledge of SSIS data types, and I am currently having trouble making this functionality work. When I set the parameters datatype to nvarchar, the date is always inserted as 1/1/1900. When I use the DBDATE type, the insert fails with the following error

unsupported data type on parameter binding 0

So my question is how do I pass a SSIS date variable into an execute sql task so that SQL Server can interpret it correctly?

According to BOL DT_DBDATE is "A date structure that consists of year, month, and day". Which can't be used to store System::StartTime output as its DATETIME by data type, so try to use datatype DT_DATE instead of DBDATE. As datatype DT_DATE "A date structure that consists of year, month, day, hour, minute, seconds, and fractional seconds. The fractional seconds have a fixed scale of 7 digits."


Help with this query I have a person table. I also have a car table and books table.  The car and book tables both have a foreign key back to person.

I am trying to count the number of books and cars each person has. I tried:

SELECT
    Person.Name,
    COUNT(BooK.bookid) books,
    COUNT(Car.CarId) cars
FROM Person, Book, Car
WHERE Person.ID = Book.PersonID = Car.PersonID

but I get:

Incorrect syntax near '='

Some points

As @swasheck mentioned, you can't have a condition like WHERE a = b = c in SQL, it's not valid (unlike other languages). You need to make it
 WHERE a = b AND b = c
Using implict joins with WHERE is not good practise any more, 25 years since SQL-92 standards adopted the JOIN syntax (a JOIN b ON <condition>), which has several advantages and should be preferred. One reason is that there are several types of joins available (and all queries except those that use only INNER joins are hard to write using the WHERE syntax):

INNER JOIN   or just JOIN
this is the most common type of join, combines rows from the two joined tables when they match the ON  condition.
LEFT OUTER JOIN  or just LEFT JOIN
very common, too: gets all combinations of INNER JOIN plus all unmatched rows of the left table.
RIGHT OUTER JOIN or just RIGHT JOIN
(not so common) the reverse of LEFT join: gets all combinations of INNER JOIN plus all unmatched rows of the right table.
FULL OUTER JOIN  or just FULL JOIN
this is LEFT and RIGHT join, combined.
CROSS JOIN
NATURAL JOIN   and variations (not supported by SQL-Server)

There are many references and tutorials on the Web about Joins. You can start with MSDN online documentation.
You also need to study how COUNT() works:

COUNT(*) counts the number of rows (of a group).
COUNT(column/expression) counts the number of rows (of a group) where the column or expression is not null. If the column cannot be NULL, this is the same as COUNT(*)
COUNT(DISTINCT column/expression) counts the number of distinct values of the column or expression (within a group).

And here are a few ways to write your query:

Option 1 - inline subqueries:

SELECT
    Person.Name,

    ( SELECT COUNT(*)
      FROM Book
      WHERE PersonID = Person.ID
    ) AS BookCount,

    ( SELECT COUNT(*)
      FROM Car
      WHERE PersonID = Person.ID
    ) AS CarCount
FROM
    Person ;

Option 2 - two LEFT Joins, then GROUP BY and use of COUNT(DISTINCT):

This is very similar to your approach, but has the implicit joins with WHERE turned into explicit joins.

The GROUP BY p.ID, p.Name was added, too, so the query can group rows per Person.

We have to use the COUNT(DISTINCT) in this version because the two joins may produce multiple rows per Person. (If a person has 2 Cars and 500 books, 1000 rows will be produced and then collapsed into 1 with the grouping. You can try with COUNT(*) there to see what (erroneous) results are produced.)

SELECT
    p.Name,
    COUNT(DISTINCT b.BookID)  AS BookCount,
    COUNT(DISTINCT c.CarID)  AS CarCount
FROM
    Person AS p
  LEFT JOIN
    Book AS b
        ON b.PersonID = p.ID
  LEFT JOIN
    Car AS c
        ON c.PersonID = p.ID
GROUP BY
    p.ID, p.Name ;

Option 3 (my preference) - two LEFT Joins to (derived) GROUP BY subqueries:

SELECT
    p.Name,
    COALESCE(BookCount, 0) AS BookCount,   --- using COALESCE() so the NULLs produced
    COALESCE(CarCount, 0)  AS CarCount     --- by the (LEFT) outer joins for persons
                                           --- that have no car or no book (shame!)
                                           --- are turned into 0
FROM
    Person AS p
  LEFT JOIN
    ( SELECT PersonID
           , COUNT(*) AS BookCount,
      FROM Book
      GROUP BY PersonID
    ) AS b
        ON b.PersonID = p.ID
  LEFT JOIN
    ( SELECT PersonID
           , COUNT(*) AS CarCount,
      FROM Car
      GROUP BY PersonID
    ) AS c
        ON c.PersonID = p.ID ;

Option 4 - In SQL Server, there is also the option of using OUTER APPLY to (derived) GROUP BY subqueries. This is similar to LEFT joins but has even more flexibility, which can be very useful in more complex cases. (in other DBMS like PostgreSQL and DB2, the same functionality exists as well, with LATERAL joins).
  Notice how the ON conditions from option 3 have been moved to WHERE, inside the outer apply subqueries:

SELECT
    p.Name,
    COALESCE(BookCount, 0) AS BookCount,   --- using COALESCE() so the NULLs produced
    COALESCE(CarCount, 0)  AS CarCount     --- by the (OUTER APLY) joins for persons
                                           --- that have no car or no book (shame!)
                                           --- are turned into 0
FROM
    Person AS p
  OUTER APPLY
    ( SELECT PersonID
           , COUNT(*) AS BookCount,
      FROM Book
      WHERE PersonID = p.ID
      GROUP BY PersonID
    ) AS b
  OUTER APPLY
    ( SELECT PersonID
           , COUNT(*) AS CarCount,
      FROM Car
      WHERE PersonID = p.ID
      GROUP BY PersonID
    ) AS c ;

All 4 queries will give same results - all Persons and the Count of their Books and Cars, even if they have no book or no car. If you want to show only Person that have at least one Book or at least one Car (or both), options 2, 3 and 4 can be easily modified: just change the respective LEFT OUTER JOIN (or both of them) to INNER JOIN - and the OUTER APPLY to CROSS APPLY.

If the problem involves calculating other aggregates, like MAX(), SUM(), etc., then the options are essentially the same - but note that option 2 can be used with MIN and MAX but not with SUM or AVG. Option 1 can be used with any aggregate function but needs a separate subquery for each aggregate, so if one needs many aggregates from the same table, options 3 and 4 are preferable.


Identify applications with locks In my environment there is a database Sybase ASE 15, that I need to replace with a Microsoft SQL Server 2000-r2.
Many users access this database via some applications, that sometimes "forget" to commit a transaction, then idle indefinitely keeping the table lock. This has a terrible effect: all other applications queue to obtain a lock on the tables affected, and are effectively stuck.
In Sybase I use a query that tells me which user is causing the problem; I can then either kill the task, or even go to him/her and find and correct the problem in the application.
This is the Sybase query:

select l.spid, SysLogin=s.name,SysObject=o.name, dbname
  from master..syslocks l, master..sysprocesses p, SIAM..sysobjects o, master..syslogins s
 where o.type='U'
   and p.spid=l.spid
   and l.id=o.id
   and p.suid=s.suid

The output produced looks like this:

81  john    authors     maindb
88  mary    authors     maindb
88  mary    books       maindb

Unfortunately, syslocks does not exist in Mssql, at least not in the version I am using.
How do I convert the query to work on Mssql?
Of course, if there is an alternative way of achieving the same result that would be great.

You might get it to work using only system procedure sp_lock. Although it's deprecated and it will be removed in a future version, it still works on SQL 2008 R2 and SQL 2012.

USE master;
GO
EXEC sp_lock;
GO
EXEC sp_lock 53; -- 53 is the spid for some specific user session;
GO

Another way is to use the DMV sys.dm_tran_locks.

Select *
from sys.dm_tran_locks dl
join sys.sysprocesses sp on dl.request_session_id = sp.spid

I think that a more clearer way is to use Adam Machanic's procedure WhoIsActive which has a specific parameter for showing locks for a session.

Use them all and choose whatever suits you best, but the WhoIsActive procedure provides more info than just locks.


Will canceling InnoDB compression that's already in progress corrupt data? I have a 155GB table that is in the process of being compressed.

ALTER TABLE $table_name ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8;

This compression has been running for a long time and is impeding my ability to backup.

I'm thinking about killing the compression query, backing up, and trying the compression again once my devs know how long it'll be till the next backup.

Is there any danger in just running a kill statement? Is there a safe way to back out of this compression?

My fear is that backing out of a compression mid query will corrupt the table. I would like to run the following query below but I believe it will be dangerous.

KILL QUERY 12345

If innodb_file_per_table is enabled, then a temp table is being populated with the new compression. The original table is currently intact, just not writable.

Running KILL QUERY 12345 on the Connection running the ALTER TABLE should do the following:

release the write lock
drop the temp table
leave the original table as is


query for COLUMN_NAME LIKE "X" in sybase(Transact SQL)? I need to automate writing a query in Sybase.

The table looks like this:

ID, A1, A2, A3, ..., A24, B1, B2, B3, ..., B24, etc.

I would like a query where the user can choose which columns are returned.  For instance, sometimes the user can choose all the A* columns, sometimes they can choose all the B* columns, sometimes they can choose any of the columns.

I need to write a query to read values from all the columns like:

SELECT a*,b* FROM Users

or

SELECT * FROM Users WHERE column_name LIKE a%, b%

First: This isn't easy to implement and it's going to break very easily (by which I mean performance is going to be horrible, and it's a nice little route for SQL injection attacks if you're not very very careful). I strongly advise you to re-think what you're doing because there has to be a better way.

Second: The question as it stands doesn't actually make sense - what condition are you checking on the columns? NOT NULL? Also, do you want to AND or OR the conditions together?

To actually answer the question, though, to do this you'll need to write some dynamic SQL:

Link sysobjects to syscolumns, filter by the table name and the fields you're interested in.
Build a executable SQL statement using a cursor, then execute it.

Example below is, for each column in the Users table that starts with either 'A' or 'B', return all rows in the Users table that have a non-null value in any of those columns.

Note: I don't have a Sybase install handy to test this. Chances of it actually working are very low, but it should hopefully be enough to make you realise what a bad idea this is.

DECLARE @colname nvarchar(255),
        @sql nvarchar(4000)

SET @sql = 'SELECT * FROM Users WHERE '

DECLARE colcur CURSOR FOR
    SELECT sc.name
    FROM syscolumns sc
            INNER JOIN
         sysobjects so ON so.id = sc.id
    WHERE so.name = 'Users'
        AND so.type = 'U'
        AND (sc.name LIKE 'A%' OR sc.name LIKE 'B%')
    ORDER BY sc.name
OPEN colcur

WHILE 1=1
BEGIN
    FETCH colcur INTO @colname
    SET @sql = @sql + @colname + ' IS NOT NULL OR '
END

DEALLOCATE colcur

SET @sql = LEFT(@sql, LEN(@sql) - 3) -- need to trim off the trailing 'OR'

EXEC @sql


sstableloader returns `java.lang.NumberFormatException: For input string: "Filter.db"` I've been using the Java API to create an SSTable by following the instructions on this site.

The program compiles and works fine; no errors.

However when I try to load the table using the following command sstableloader -v -d 127.0.0.1 /path/to/sstable/keyspace/tablename it returns an error. Here is the stack trace it outputs:

For input string: "Filter.db"
java.lang.NumberFormatException: For input string: "Filter.db"
        at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
        at java.lang.Integer.parseInt(Integer.java:580)
        at java.lang.Integer.parseInt(Integer.java:615)
        at org.apache.cassandra.io.sstable.Descriptor.fromFilename(Descriptor.java:276)
        at org.apache.cassandra.io.sstable.Descriptor.fromFilename(Descriptor.java:235)
        at org.apache.cassandra.io.sstable.Component.fromFilename(Component.java:120)
        at org.apache.cassandra.io.sstable.SSTable.tryComponentFromFilename(SSTable.java:160)
        at org.apache.cassandra.io.sstable.SSTableLoader$1.accept(SSTableLoader.java:84)
        at java.io.File.list(File.java:1161)
        at org.apache.cassandra.io.sstable.SSTableLoader.openSSTables(SSTableLoader.java:78)
        at org.apache.cassandra.io.sstable.SSTableLoader.stream(SSTableLoader.java:162)
        at org.apache.cassandra.tools.BulkLoader.main(BulkLoader.java:109)

NOTES

Tried to google it but there doesn't seem to be anything about this error.

The table that im loading into only has three columns: ( id uuid PRIMARY KEY, datablob text, tablename text ).

I've also tried loading empty data and just auto generating a uuid to check that it's not the data I'm loading but it still doesn't work.

I've fixed this by upgrading Cassandra to the latest version; I was running the last 2.x release and now it's running the latest 3.0.7 version.


SQL 2016 SP1 Mirroring - slow database list refresh and repetitive automatic failovers I recently installed SQL Server 2016 SP1 Standard on two servers and 2016 SP1 Express as a Witness on a third server.  Firewall is off and the servers can ping each other.  I created an empty database on afserver1 (full recovery model), performed a full backup, and restored with norecovery on afserver2.

The first issue I'm seeing is that on afserver2 when I refresh the databases for the first time after restore, two identical database entries show up in the list.  Refreshing again corrects it.

It also takes about 10 seconds to refresh the database list on the secondary even though there is only two db's on the server.  The primary always refreshes the db list instantly.  In the log file on the secondary after the refresh shows a number of these errors:

  Login failed for user 'DOMAIN\USER'.
  Reason: Failed to open the explicitly specified database 'MirrorTest'.
  Error: 18456, Severity: 14, State: 38.

It seems odd that simply refreshing the database list would result in 6 access errors in the logs for a database in a restoring state, but maybe that's normal?

When I go through the wizard to set up the mirror, it succeeds immediately.  However, when I start mirroring it takes a very long time to start (and this is an empty db with no transactions).  It will finally start but after a short time will fail over to the secondary.  In the logs after about a minute, I start seeing entries for both the secondary and witness server:

  Database mirroring connection error 4
  'An error occurred while receiving data:
  '10054(An existing connection was forcibly closed by the remote host.)'.'
  for 'TCP://AFSERVER3.domain.local:5022'.
  Error: 1474, Severity: 16, State: 1.

It appears the database is failing back and forth between afserver1 and 2, with a number of messages in the logs about database mirroring being inactive and then active again.  It feels like a communications problem but the servers are on the same subnet with the firewalls turned off.

Any ideas for what could be causing not only the slow refresh of the database list and the back and forth failover of the database?

I've verified that the account has CONNECT permission to the endpoint on each server.  I'm using domain accounts for everything on these servers.

It looks like CU1 corrects the issue I was having with the spontaneous failovers.  The mirror now seems stable and can fail manually back and forth correctly.

Refreshing the database list on the secondary is still slow, presumably because it is trying to log into the database but can't; log files contain "Login failed for user. Reason: Failed to open the explicitly specified database 'MirrorTest' when I try to refresh the list.


PostgreSQL CREATE TABLE error I'm new in PostgreSQL. I try to create table in database. In psql i write:

CREATE TABLE mail_user (
   user char(50) NOT NULL,
   domain char(50) NOT NULL,
   password char(50) NOT NULL
);

But i get error:

ERROR:  syntax error at or near "user"
LINE 2: user char(50) NOT NULL,
        ^

What's wrong? How can i fix it?

Thank you.

"user" is a reserved word. You may want to try "username" instead. http://www.postgresql.org/docs/8.3/static/sql-keywords-appendix.html


Count Total time difference between selected row My data contains an ID, status and time(hh:mm:ss).

I need to calculate the total time between every 'Location' and 'Out of tunnel' status

Current table

    ID   ||       time       ||     Status
________________________________________________
      1  ||       08:09:14   ||   Out of Tunnel
      2  ||       08:10:59   ||     Location
      3  ||       08:11:42   ||   Out of Tunnel
      4  ||       08:11:55   ||     Location
      5  ||       08:16:36   ||     Location
      6  ||       09:41:36   ||     Location
      7  ||       09:43:10   ||   Out of Tunnel
      8  ||       09:43:19   ||     Location

The 'Location' status marks where I need to start counting. I need the count to stop the next time the status is 'Out Of Tunnel'. If there is no corresponding 'Out of Tunnel' given, then don't count it.

Each total would then be summed to give a grand total. For example:

Start at ID2: 08:10:59
End at ID3: 08:11:42
Total 1: 00:00:43
Start at ID4: 08:11:42
End at ID7: 09:43:10
Total 2: 01:31:28
Grand Total: Total1 + Total2 = 01:32:11

Desired output

 ______________________
| Total Time in Tunnel |
|______________________|
|                      |
|        01:32:11      |
|______________________|

The second round starting with ID 4 has the time 08:11:55 in your sample data, so if indeed that's the time you're supposed to use as the starting point, the total should amount to 01:31:58. At any rate, here's a solution using the LAG and LEAD window functions. If you want to prevent a sort in the plan, make sure you create the following supporting index:

create unique index idx_time_status on dbo.t1(time, status);

If you're running on 2016+, you can enable batch processing by creating the following dummy index:

create nonclustered columnstore index idx_cs on dbo.t1(id) where id = -1 and id = -2;

Prior to SQL Server 2019, SQL Server would not consider using batch processing if there's no columnstore index present on at least one of the participating tables in the query. Creating this dummy index, even if it's really meaningless in its own right, enables the use of the more optimal batch processing for window functions. Check out the plans both with and without the index. I explain this in detail here.

Here's the solution code:

with c1 as
(
  select *,
    case
      when status = 'location'
        and lag(status) over(order by time) = 'location' then 'no'
      else 'yes'
    end as keeper
  from dbo.t1
  where status in ('location', 'out of tunnel')
),
c2 as
(
  select *, lead(time) over(order by time) as nxt
  from c1
  where keeper = 'yes'
)
select dateadd(second, sum(datediff(second, time, nxt)), cast('00:00:00' as time(0))) as total
from c2
where status = 'location';

I formatted the output as TIME assuming the total amount of time will be less than 24 hours. If it could be more, you'd just need to add a bit of formatting logic.


PostgreSQL equivalent of SQL Server SQL_VARIANT_PROPERTY Does PostgreSQL have an equivalent function for determining column metadata?

Reference: SQL_VARIANT_PROPERTY

pg_typeof() appears to be close, but it does not show the precision and scale. Looking up the type in pg_type only shows the generic type info, not the column specific, e.g. SQL Fiddle.

The fact that pg_typeof doesn't show the typmod is frustrating.

To get the fully qualified type you can query the system catalogs. Let's look at how psql does it using psql -E:

$ psql -E regress
psql (9.2.1)
Type "help" for help.

regress=> CREATE TABLE typmodtest ( a numeric(16,2), b varchar(32) );
CREATE TABLE
regress=> \d typmodtest

... tons of information printed, including this query which produces the information we want:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '24641' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

This query can clearly be trimmed if you're not interested in the field collation, NOT NULL status, etc. We can also change the attrelid filter from an oid to a regclass filter:

SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod)
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = 'typmodtest'::regclass
  AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;

producing:

 attname |      format_type
---------+-----------------------
 a       | numeric(16,2)
 b       | character varying(32)

This will only work on relations. I'm not aware of a reliable, generic way to get the typmod-qualified type for any result set, and it's a serious irritation. Among other things it makes it hard for the PostgreSQL JDBC driver to be compliant.


How many databases can an Amazon Aurora instance hold? When the word database is defined for table-families,

Can an Aurora instance hold more than a database?

Does any Aurora instance even has the concept of separated database(table family)?

Thanks

I'm speaking to the currently available version of Aurora which is MySQL 5.6 compatible. On the surface, it works just like MySQL. I have two databases on my Aurora cluster. My guess it can hold as many databases as MySQL or at least more than you would ever need. Where things differ is in how backups are handled. Aurora, like other RDS offerings, does cluster snapshots, not individual database backups. To restore a cluster snapshot, you actually create a new Aurora cluster, switch DNS to the new cluster, and then delete the source cluster. This means that if you have problems with one of your databases on a cluster, you have to restore all databases at the same time. So you might want to group application databases together. For instance, all our ticketing and code review software (Jira and Fisheye) run on the same Aurora cluster. The two apps are used hand-in-hand so the two would always need to be restored together.


Monitor For Disabled Queue On RDS SQL Server I would like to somehow get notification of a queue being disabled due to poisoned messages. (The issue is occasional resource contention. Until we solve it, monitoring would help us be aware.) In my case, SQL Server's service broker is running under Amazon's RDS environment, which limits what is available.

Suggestions?

We are having the same issue, I see no one else has responded.  Our interim solution is to have a job running every 5 mins to just re-enable the queue if disabled.

If you have devised a solution that hasn't been published, I'd love to hear about it.


Copy encrypted database views I need to take a copy of a SQL Server database. The database has approx 40 views that link to other databases, and I need sample data from those views too. Also, the views are encrypted so when I try and generate a script from one of the views this is the error:

Property TextHeader is not available for View '[dbo].[TABLE1]'. This property may not
exist for this object, or may not be retrievable due to insufficient access rights.  The
text is encrypted. (Microsoft.SqlServer.Smo)

What would be the best way to get a copy of this database containing data from the views? I was thinking about creating extra tables and populating with sample data but that seems long-winded, so is there a better way?

Encrypted stored procs and views can be decrypted pretty easily:

http://www.mssqltips.com/tip.asp?tip=1046

That'll give you the unencrypted text of the stored procedures and views.  Just change the CREATE statement to be an ALTER, and it'll overwrite the encrypted versions with decrypted ones.

Don't do this in production - do it in a restored backup of the database.  That way if it's a third party vendor app, you'll still be supported in production.


Lock in SQL Server and Oracle I have been doing some tests of locking/blocking in MSSQL Server and Oracle, I noticed one difference:

In Oracle - I performed an update for one row without issuing commit or rollback and in another session I can view the underlying record, of course, I could view the last committed data and not the value that was yet to be committed.

In MSSQL Server - When I did the same operation in another session the SQL Server kept waiting for commit or rollback of the row that is getting updated.

Could somebody please explain locking mechanism between MSSQL server and Oracle.

This is a default behavior of SQL server, to understand more you need to invoke isolation level. What you have described above falls in isolation level called "Read Committed". If you want similar behavior like Oracle in SQL server then you need to turn on the Read Committed Snapshot database option, basically this option takes a snapshot of your data before modifications and puts them in tempdb, accordingly you would see last committed record which was snapshot of last committed records. Another isolation level Read Uncommitted will in this situation return data but with a big catch i.e. you would read uncommitted records and there is a fair chance that you could read a dirty record.
There is concept of nolock and readpast as lock hint in SQL server for avoiding locking however it has its own impact.
You may read more about isolation level at Microsoft site at this link.
There are tonnes of article on this subject from many SME, I am listing few of them for your reference:

Brent Ozar --> https://www.brentozar.com/archive/2013/01/implementing-snapshot-or-read-committed-snapshot-isolation-in-sql-server-a-guide/

Kendra Little --> https://littlekendra.com/2016/02/18/how-to-choose-rcsi-snapshot-isolation-levels/

Erik Darling --> https://www.brentozar.com/archive/2018/01/heaps-deletes-optimistic-isolation-levels/

Paul White --> https://sqlperformance.com/2014/07/t-sql-queries/isolation-levels

Robert Sheldon --> https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-t-sql-transaction-isolation-levels-you-were-too-shy-to-ask/

There is a video from Brent Ozar (unable to find now) where he has clearly explained the difference between Oracle and SQL server you have mentioned. For this reason, Oracle used to cost lot more earlier than SQL server per core however now the case/scenario is different. One more thing to add here - In azure, RCSI is default behavior.
I hope above helps. Actually this is less of a question than a very important topic and understanding of SQL server and hard to cover them in one answer however above links will definitely guide you in right direction.


When to use auto_increment? I wonder when to use auto_increment. It is said that it can be used for a unique identifier. But why the unique identifier is needed?

Suppose the data of a student has entered in a student table. If I  enter that student data again, it will be ok since it create another id automatically. But I have storing the same data unnecessarily.

What is the meaning of using that?

If I can identify a primary key, I do not need to use that?
For example, For a Hotel table, I can use the combination of Hotel name and address as a primary key. Then, we can avoid storing the same data several times.

Is it better to use to generate a key with auto_increment?

The advantage of using an auto increment field is that new records are automatically given an unique primary key.

  Suppose the data of a student has entered in a student table. If I enter that student data again, it will be ok since it create another id automatically. But I have storing the same data unnecessarily.

It's your duty to take care that the same data is not entered again i.e. verify that the student exists and, if yes, edit his record instead of creating a new one.

  If I can identify a primary key, I do not need to use that?

Yes, if you can identify a natural primary key (e.g. the student ID in an University, the ISBN for a book, the S/N for a piece of hardware) it is much better to use that one.

  For example, For a Hotel table, I can use the combination of Hotel name and address as a primary key.

It is a bad idea to use primary keys which contain meaningful data. What happens if the hotel changes its name? In this example, it is better to use a surrogate primary key.


SQL query for FIFO allocation of items I have a real world business scenario but for the sake of clarity let me rephrase it as a simpler fictional problem.
We have Buyers and Items.
DECLARE @Buyer TABLE
(
 BuyerId INT IDENTITY(1,1),
 Name NVARCHAR(100),
 Budget INT
);

INSERT @Buyer (Name, Budget) VALUES
('Anna', 100),
('Brett', 50),
('Conor', 20)

DECLARE @Item TABLE
(
 ItemId INT IDENTITY(1,1),
 Cost INT
);

INSERT @Item (Cost) VALUES (50),(30),(20),(40),(10),(40),(30),(10),(5);

The goal is to craft a query (no loops or cursor) which allocates items to buyers, based on their budget.
The most expensive items should be allocated in priority.
Expected result:
ItemId BuyerName
1  Anna
4  Anna
6  Brett
7  NULL
2  NULL
3  Conor
5  Anna
8  Brett
9, NULL

From a logical perpective, this is what happens.
First, the items are sorted by descending cost
ItemId Cost
1   50
4   40
6   40
7   30
2   30
3   20
5   10
8   10
9   5

Then we go through each item and try to allocate it to a buyer that has enough budget left.

Item 1 => Can be assigned to Anna. Anna's remaining budget is 100 - 50 = 50
Item 4 => Can be assigned to Anna. Anna's remaining budget is 50 - 40 = 10
Item 6 => Can be assigned to Brett. Brett's remaining budget is 50 - 40 = 10
Item 7 => No one has enough budget left
Item 2 => No one has enough budget left
Item 3 => Can be assigned to Conor. Conor's remaining budget is 20 - 20 = 0
Item 5 => Can be assigned to Anna. Anna's remaining budget is 10 - 10 = 0
Item 8 => Can be assigned to Brett. Brett's remaining budget is 10 - 10 = 0
Item 9 => No one has enough budget left

Nasty, but will do what you wanted:
DECLARE @Out TABLE (BuyerId int, ItemId int);
DECLARE @BuyerId int, @Budget int, @ItemId int, @Cost int;

WHILE EXISTS(SELECT TOP 1 Budget FROM @Buyer WHERE Budget >= (SELECT MIN(Cost) FROM @Item))
BEGIN
    SELECT TOP 1 @BuyerId = BuyerId, @Budget = Budget
    FROM @Buyer
    WHERE Budget >= (SELECT MIN(Cost) FROM @Item)

    SELECT TOP 1 @ItemId = ItemId, @Cost = Cost
    FROM @Item
    WHERE Cost <= @Budget
    ORDER BY Cost DESC, ItemId ASC;

    DELETE FROM @Item WHERE ItemId = @ItemId;
    UPDATE @Buyer SET Budget -= @Cost WHERE BuyerId = @BuyerId;
    INSERT INTO @Out VALUES(@BuyerId,@ItemId)
END
SELECT * FROM @Out

UPDATE (2020-10-16):
Based on OPs comment I decided to try to do as much as possible using set based data flow. First of all solution requires to create all possible permutations without duplicates where the order doesn't matter. For that I used a recursive CTE, then out of all possible price sums we needed only these in the Buyers budget, then I ordered them form the most expensive set (as asked by OP) where we want most expensive item first then the most expensive still in the budget and so on. I ended up with having the order column, JSON array for ItemId and all other base data:
;WITH totalCostPermutations AS(
SELECT
ItemIndex = CAST(CONCAT('"',ItemId,'') as varchar(max)),
X = CAST(ROW_NUMBER() OVER (ORDER BY Cost DESC, ItemId ASC) as varchar(max)),
ItemId, Cost
FROM @Item
UNION ALL
SELECT
ItemIndex = CAST(CONCAT(i1.ItemIndex,'", "',i2.ItemId) as varchar(max)),
X = CONCAT(i1.X, ROW_NUMBER() OVER (ORDER BY i2.Cost DESC, i2.ItemId ASC)),
i2.ItemId, Cost = (i1.Cost + i2.Cost)
FROM totalCostPermutations i1
JOIN @Item i2 ON 1=1
AND i1.ItemId < i2.ItemId
)
SELECT
RN = ROW_NUMBER() OVER(PARTITION BY b.BuyerId ORDER BY Cost DESC, X ASC),
ItemId_JSON = CONCAT('{"ItemId":[',ItemIndex,'"]}'), TotalCost = Cost,  b.*
FROM totalCostPermutations c
JOIN @Buyer b ON c.Cost <= b.Budget;

At this stage our data looks like this:

But from this point I can't think of how could this be possible to tell which ItemId have already been "used" and which not, without looping.
Still, from this output it won't be hard to either loop it in T-SQL or via the app.


Maxscale read write split and route filter I have trouble to forward all SELECT requests to the slave in a MySQL master-slave architecture.

I'm using maxscale's read-write router, which does its job except for

  all statements using temporary tables

being redirected to the master (github doc). But I want those select requests (mostly SELECT COUNT...) being executed on the slave because those are the heaviest ones in terms of cpu load.

I've set up a route filter, supposed to redirect all SELECTs to the slave database, but it isn't working, though maxscale has been restarted and the filter has shown up in the filter chain.

My maxscale configuration:

[maxscale]
threads=4

[MySQL Monitor]
type=monitor
module=mysqlmon
servers=master-db, slave-db
user=root
passwd=*
monitor_interval=10000

# =============== Filters ================ #
[SelectToSlaveFilter]
type=filter
module=namedserverfilter
match=select*
options=ignorecase
server=slave-db

# =============== Services ================ #
[RW Split Router]
type=service
router=readwritesplit
servers=master-db, slave-db
user=client
passwd=*
max_slave_connections=1
enable_root_user=1
filters=SelectToSlaveFilter

[Debug Interface]
type=service
router=debugcli

[CLI]
type=service
router=cli

# ===============  Listeners ================ #
[RW Split Listener]
type=listener
service=RW Split Router
protocol=MySQLClient
port=4446
socket=/var/lib/maxscale/rwsplit.sock

[Debug Listener]
type=listener
service=Debug Interface
protocol=telnetd
address=127.0.0.1
port=4442

[CLI Listener]
type=listener
service=CLI
protocol=maxscaled
port=6603

# =============== Servers ================ #
[master-db]
type=server
address=X.X.X.X
port=3306
protocol=MySQLBackend

[slave-db]
type=server
address=127.0.0.1
port=3306
protocol=MySQLBackend

Additional information that confirms the filter is used in the routing process:

maxadmin -pmariadb 'show session 0x54ae060'
Session 1840 (0x54ae060)
    State:          Session ready for routing
    Service:        RW Split Router (0x54992c0)
    Client DCB:     0x54aff50
    Client Address:     root@localhost_from_socket
    Connected:      Thu Nov 26 09:10:40 2015
    Idle:       4 seconds   Filter: SelectToSlaveFilter
        Match and route:            /select */ -> slave-db
        No. of queries diverted by filter:  3
        No. of queries not diverted by filter:      1

One of the heavy queries still showing up on the master, even with filtering:

Copying to tmp table
SELECT COUNT(*) as nb, DATE(`Comment`.`date_update`) as day FROM `table`.`comments` AS `Comment` WHERE `Comment`.`date_update` >= '2015-11-26 00:00:00' AND `Comment`.`date_update` <= '2015-11-26 23:59:59' GROUP BY DATE(`Comment`.`date_update`)

Maxscale error log:

---     Logging is enabled.
2015-11-26 09:56:44   Error: Failed to obtain address for host ::1, Address family for hostname not supported
2015-11-26 09:56:44   Warning: Failed to add user root@::1 for service [RW Split Router]. This user will be unavailable via MaxScale.
2015-11-26 09:56:44   Warning: Duplicate MySQL user found for service [RW Split Router]: root@X.X.X.X for database: (null)
2015-11-26 09:56:44   Warning: Duplicate MySQL user found for service [RW Split Router]: root@127.0.0.1 for database: (null)
2015-11-26 09:56:44   Warning: Duplicate MySQL user found for service [RW Split Router]: cmon@127.0.0.1 for database: (null)
2015-11-26 09:56:44   Error: Failed to set socket options. Error 95: Operation not supported

The problem was not maxscale's.

I found out that even if the count of routed requests to the slave was incrementing, no connection was ever made to the slave.

Here is a functional status with established connections to the slave, whereas I had always 0 connection before:

maxadmin -pmariadb show servers

Server 0x46d6010 (master-db)
    Server:                         X.X.X.X
    Status:                         Master, Running
    Protocol:                       MySQLBackend
    Port:                           3306
    Server Version:                 10.0.21-MariaDB-wsrep-log
    Node Id:                        1
    Master Id:                      -1
    Slave Ids:                      2
    Repl Depth:                     0
    Number of connections:          54
    Current no. of conns:           0
    Current no. of operations:  0
Server 0x46306a0 (slave-db)
    Server:                         127.0.0.1
    Status:                         Slave, Running
    Protocol:                       MySQLBackend
    Port:                           3306
    Server Version:                 10.0.22-MariaDB
    Node Id:                        2
    Master Id:                      1
    Slave Ids:
    Repl Depth:                     1
    Number of connections:          54
    Current no. of conns:           0
    Current no. of operations:  0

The reason was that the slave went into unsynced mod after testing database creations and deletions because I'm only binlogging one database.

I had to redo the slave with a dump from the following command for innodb databases:

mysqldump -u root -p*** --master-data --single-transaction mydb | gzip > dump.sql.gz

After that, all selects were correctly routed to the slave without the need of a filter.


Is there any difference with specifying the primary key as an include column in a nonclustered index? Don't nonclustered indexes inherently store a reference to the primary key on a table so that it can do a key lookup as needed?...if so, is it any less or more performant to specify the primary key as an included column when creating a nonclustered index?

Side question, why does the nonclustered index default to storing the primary key and not the clustered index fields to do a key lookup on the table?...In the cases where the primary key is not the clustered index, isn't it slower for the key lookup to occur whereas if it stored the clustered index it could do the lookup that way?

Nonclustered indexes contain a row locator back to the base table.

This is a clustered index key for rowstore tables with a clustered index or a physical RID (file/page/slot) for heaps.

So the parts in your question assuming that it uses the primary key (in the event that these are different) are moot.

The row locator is added to the key for non unique nonclustered indexes and as an included column to non clustered indexes declared as unique.

There is no performance advantage of specifying this explicitly for the included case. For the case that it is implicitly added to the key you may need to do this explicitly if your queries would benefit from a different asc/desc direction or multi column ordering than the implicit would give you.

You may prefer to be explicit about it for readability of your code for developers unfamiliar with the implicit behaviour or if your queries would still need those columns in the index even if the clustered index definition was to change in the future.

Regarding the followup question in the comments

  Does that mean it's possible a key lookup might be performed from a
  nonclustered index for the primary key (when the primary key is not
  the clustered index) if that primary key is in the select list?

Yes, you can see this with the below.

CREATE TABLE #T
(
PK INT PRIMARY KEY NONCLUSTERED,
CI INT INDEX CIX CLUSTERED,
OtherCol INT INDEX NCIX NONCLUSTERED
)

SELECT *
FROM #T WITH (FORCESEEK)
WHERE OtherCol = 10

The output list of the seek on NCIX is Uniq1001, CI, OtherCol
The values of CI, Uniq1001 are then used by the lookup to seek into the clustered index...
... and the value for PK is returned from that lookup.

If you were wondering about Uniq1001 this is a part of the clustered index key silently added by the system to act as a uniqueifier as the clustered index was not declared as unique. It is empty except for rows where duplicate exist/existed for the value of CI.


desing the database for survey app with various types of answers I have to create a survey app for which answers should be stored in the database. I created an html form containing some questions (about 30) with various types of answers (check boxes, radio buttons, auto-complete, etc). Now, I am going to design my database.

First, I thought of only 3 tables : Questions, Answers, Users . However after I searched some similar questions such as :Database design for a survey, db design - survey/quiz with questions and answers [closed] I found some other design models e.g creating 4 tables : Questions, PossibleAnswers, UsersAnswers and Users. Now, I have these questions:

Should I create a survey table too ? (at this moment,there is only
one survey for my app, but not sure about the future.)
Which design model would be better in my case?
In case of using the first model, should I create specific column for
each types of answer? (for instance, consider this question: which
are your favorite movie genres? and it has more than 10 possible
answers! then if I want to create a column for each possible answers
of all questions, it would be lots of columns!)
In case of the second model, is it correct? (this is what I
understood from other similar questions, but still I am not sure if
it is useful form me and if yes, what is the advantage of this
model?)

Questions: questionId, question

PossibleAnswers: possibleanswerId, questionId, possibleanswer

UsersAnswers: useranswerId, possibleanswerId, userId, answer

Users: userId, username, password

I really appreciate all your ideas and suggestions

I think you should have the Survey table as it will be much easier to add it now than later on when your app is live.

The second model is much better as it provides the flexibility to have multiple possible answers and user answers for a single question in a normalised fashion. Having a column for each answer is not a good idea.


Indexing SQL Server datetime Columns for Performance Will adding an index to a datetime column increase performance if a WHERE myDateColumn BETWEEN @startDate and @endDate clause is frequently applied to that datetime column?

If yes, should the index be clustered or non-clustered and why?

Adding an index will increase performance on SELECT statements, assuming your range of dates is not sufficiently large as to force an index scan as opposed to an index seek.  Adding an index will decrease performance on INSERT, UPDATE, and DELETE operations, as this new index will need to be maintained.  The clustered/nonclustered question is highly dependent on how many other columns from the table you are including in the SELECT clause.

If this question is more than a homework question, and you have examples you would like to include, it would help my answer to be more complete.

Hope this helps,

Matt


Is it necessary to re-insert all the data in specific order, when I'm going to use the BRIN index? I'm going to use a BRIN index to replace the B-tree index on a date/timestamp column in Postgresql-11. I never used it before.
Since that a BRIN index can be more effective if the data is stored physically in the order that the column is being indexed.
I'm doubt about wether or not I should delete all the data, and then re-insert them in the order.
My data is being used for statical analysis, would not change.

As with everything related to databases, the answer will be “it depends”.
BRIN indexes are efficient if the ordering of the key values follows the organization of blocks in the storage layer. In the simplest case, this could require the physical ordering of the table, which is often the creation order of the rows within it, to match the order of the key(s). Keys on generated sequence numbers or created data are best candidates for a BRIN index.
You mentioned that the index will he made in a date/timestamp column so, as long as these records are already stored sequentially in the table, you will not need to do a dump and reload. If the values are all over the place, then it would be in your best interest to export the data in the new order, drop the table, and reload.
BRIN indexes use far less storage compared to a normal B-Tree index, but they can be quite “lossy”, which will reduce their effectiveness. You may be able to tweak this by specifying a pages_per_range value like this:
CREATE INDEX idx_date_brin ON my_stats_db USING BRIN(transaction_date) WITH (pages_per_range = 32);

BRIN indexes store entries for a range of pages in a corresponding table. The larger the range of pages, the smaller the index. The smaller the index, the lossier it becomes.
Because indexes can be created and destroyed far more quickly than a complete table load (mist of the time), i would suggest playing around on a non-production system to see whether a BRIN is better than a B-tree for your use case and if your table data is sequential enough to not require a reload.
Hope this helps


Mysql config for my.cnf make sofware not response First time  i am sorry about my English , but please help me on my.cnf . I read many post about config Mysql for best optimize but not thing to imporve. My server is Data center that contains two windows and linux . window has 5 core 5 GBram and linux has 18 CPU , 20GB ram . My window has two software that was developed by c# and i am using dll mysql.data . two software ,they will craw data from another website and insert into data to linux , sometime call "select , update , delete" . they work every time about 10s to craw and 10s to craw and send data to linux  . On my linux with 358 user online same time , they click on link . another link and stay very long , they call always call to Database every time . My server has 60% select and 40% write . I dont know how to config my.cnf . But some time software in window insert data to table of database on linux very long about 30s and some time it show not respoding . I dont know why and i dont know what parameter make table innodb lock or make innodb can not be inserted by c# . Now i would like to know how can i change and what parameter(variable) to change to get best performance for select and insert into with larger data . Thank you very much . This is my my.cnf ( i am using mysql 5.6.31 - MySQL Community Server (GPL))

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
skip_name_resolve
skip_external_locking
default_storage_engine=InnoDB
max_connections=20000
join_buffer_size=2M
open_files_limit=65535
query_cache_type=ON
query_cache_size=1G
thread_concurrency=36
thread_cache_size=25
table_open_cache=10048
wait_timeout=300
read_buffer_size=1M
sort_buffer_size=1M
read_rnd_buffer_size=1M
max_connect_errors=1000
long_query_time=7
key_buffer_size=128M
max_allowed_packet=268435456
interactive_timeout=400
myisam_sort_buffer_size = 64M
tmp_table_size=64M
max_heap_table_size=64M
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=512M
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=10
innodb_log_buffer_size=64M
innodb_thread_concurrency=0

max_connections=20000  -- too large; make it only 200
query_cache_size=1G    -- REALLY BAD! -- make it only 50M
table_open_cache=10048  -- too high; try 3000
long_query_time=7  -- Lower to 2, turn on slowlog, keep an eye on it
innodb_buffer_pool_size=10G  -- OK with 16GB of RAM; not ok with 5GB of RAM

On 100

It is not practical, nor efficient, to try perform 100 queries simultaneously.  Because of that, there is no way to "configure" it into existence.  It is better to do...

Combine all the information into a single AJAX call -- the back and forth for AJAX is non-trivial, possibly even more costly than the database queries themselves.
If all the INSERTs are into the same table, then use a single "batch" INSERT with 100 rows.  This will run 10 times as fast as a 100 1-row INSERTs.

If it is not 100 similar inserts, please provide more details about the queries.


Split record data in ssrs Hi all can you please assist me,

I would like to split my records into different year and month see below I want figure one to be split into different categories but this must all display on one page.

my current result set is like this:

Date Year   Date Month  Instructor  Num Of Members
2013            November    Jones       57
2013            November    Smith       40
2013            November    Moreleta    55
2013            November    James       65
2013            November    Soodie      65
2014            January     Jones       47
2014            January     Smith       55
2014            January     Moreleta    60
2014            January     James       73
2014            January     Soodie      68
2014            February    Jones       48
2014            February    Smith       60
2014            February    Moreleta    66
2014            February    James       75
2014            February    Soodie      68

I want them to be split like the one below. Can any of you assist me in doing this.

Date Year   Date Month  Instructor  Num Of Members
2013            November    Jones       57
2013            November    Smith       40
2013            November    Moreleta    55
2013            November    James       65
2013            November    Soodie      65

2014            January     Jones       47
2014            January     Smith       55
2014            January     Moreleta    60
2014            January     James       73
2014            January     Soodie      68

2014            February    Jones       48
2014            February    Smith       60
2014            February    Moreleta    66
2014            February    James       75
2014            February    Soodie      68

You can have 3 grids all with the same data source and a filter on the year and month. This would be a static filter though so you would be better off having 1 grid that step grouped on year and month.

This link shows you everything you need to know in order to do it in one grid.


Open Management Studio with configured connections Maybe this question is a little off topic, I will close if necessary, but, is there a way to configure Management studio, to when I open it, it starts already with x connections? So I don't need to start it and connect to x instances every time.

I'm not sure of a way to do this 'natively' in SSMS, but I've been using SSMS Boost for years and it has that functionality (and a whole lot more). It's a plugin for SSMS and they have a 'free' community version that just needs to be re-downloaded every few months.

Check here and scroll down until you see Download links (updated on 14-th of March 2017):

After installation, there will be a new menu bar option 'SSMSBoost'. Click on the menu bar and select 'Settings'.  Then look for 'Preferred Connections'.  There, you will have a checkbox to open a defined connection on SSMS startup.

You can 'add' preferred connections by clicking the 'plus' sign at the bottom of the screen.  Preferred connections also allow you to specify an 'Important DB Alert' that will actually be displayed on new query windows - I use mine to inform me I'm in a 'production' database.


PostgreSQL find groups with same members Given the following schema:

User
  id

Group
  id

Membership
  group_id
  user_id

(With a unique index on group_id, user_id)

How would I select the group(s) of which the same two users are a member?

What I have now:

SELECT   "Membership"."group_id"
FROM     "Membership"
WHERE    "Membership"."user_id" IN (1, 2)
GROUP BY "Membership"."group_id"
HAVING   COUNT("Membership"."group_id") >= 2

Which seems to work, (it only retuns the group ids of which both users are a member), but looks rather confusing.

Is there a better way to express this query?

EDIT

So given the following data:

Users
-----
id
1
2
3

Group
-----
id
1
2
3

Membership
----------
group_id user_id
1        1
1        2
2        1
2        3
3        2
3        3

I'd like to find groups of which both user 1 and 2 are a member.

So the result of the query with user_id 1 and 2 should be group 1.
The same query for user_id 1 and 3 should return group 2.
And the query for user_id 2 and 3 should return group 3.

This is called relational division and your query is pretty much what you can do with standard SQL.

A different way to write this in Postgres would be to use arrays:

select group_id
from membership
group by group_id
having array_agg(user_id order by user_id) = array[1,3];

It's important to also order the IDs inside the array[1,3] constant because array[1,3] is a different array than array[3,1];

The above returns those groups that consist exactly of those two members. Your query returns those that consists at least of those two members. That can be written with an array as well:

select group_id
from membership
group by group_id
having array_agg(user_id order by user_id) @> array[1,3];

That uses the "contains" operator for arrays which tests if the array on the left side contains all elements of the array on the right side. But the array on the left side is allowed to have more elements.

The check for an exact match is probably faster using the array (because it would require an additional sub-query with standard SQL), but I don't think that the "at least" query makes a big difference in performance.


Optimizing a Query for splitting strings with a known size I have a Column that has a . as a delimiter, it looks like so....

abc.efg.hij

I want a query that turns this into three columns, Col1, Col2, and Col3. I am wondering what the fastest way to do this is. So far I haven't been able to do very well with my limited database experience. I've got a function:

CREATE FUNCTION [dbo].[split](
   @delimited NVARCHAR(MAX),
   @delimiter NVARCHAR(100)
 ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
 AS
 BEGIN
   DECLARE @xml XML
   SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

   INSERT INTO @t(val)
   SELECT  r.value('.','varchar(MAX)') as item
   FROM  @xml.nodes('/t') as records(r)
   RETURN
 END

This is how I'm doing it right now but I believe it could be made to go much faster, I'm also open to a significantly better function or outside the box ideas for splitting strings.I believe I'm running this dbo.split(Name, '.') three times and could only be running it once.

SELECT
      Col1 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '1'),
      Col2 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '2'),
      Col3 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '3')
FROM Mains

any help would be greatly appreciated

Instead of:

SELECT
  Col1 = (SELECT Val from dbo.split(Name, '.') WHERE Id = '1'),
  Col2 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '2'),
  Col3 = (SELECT Val from dbo.split(Name, '.')  WHERE Id = '3')
FROM Mains

Use:

SELECT
  s.*
FROM Mains
CROSS APPLY (
    SELECT
        MAX(CASE WHEN Id = 1 THEN Val END) AS Col1,
        MAX(CASE WHEN Id = 2 THEN Val END) AS Col2,
        MAX(CASE WHEN Id = 3 THEN Val END) AS Col3
    FROM dbo.split(Name,'.') s
    ) s

The idea is that you still want exactly one row per row in Mains. And using an aggregate function inside CROSS APPLY will do just that. By using CASE you only need call split() once per row.

There are plenty of questions around here about splitting strings, and SQL DB does already have a string_split() function built in.


MySQL query exponentially slower with parameter in subquery vs hard-coded value I have the following MySQL query that is querying naughty ip addresses from the last 24 hours and ban count >= 2. I don't want them if they are in an extra naughty ban list from the last 7 days and ban count >= 5. The query takes 3 minutes to run. If I replace @__cutOff_1 with the actual hard coded date inline in the query instead of a variable, the query takes 2 seconds to run.

Is this a bug in MySQL? The query explainer looks like this:

I am using the latest MySQL x64 8.0 on Windows 10 x64 as of 2019-07-30. Submissions table has 8 million rows, ip addresses table 360K. All where clauses are indexed.

SET @__cutOff_0 = '2019-07-29';
SET @__minBanCount_1 = 2;
SET @__cutOff_1 = '2019-07-23';

SELECT `i`.`IPAddress`, `i`.`BanCount`, MAX(`s`.`Timestamp`) AS `c`
FROM `Submissions` AS `s`
CROSS JOIN `IPAddresses` AS `i`
WHERE (((`s`.`Timestamp` >= @__cutOff_0) AND (`i`.`BanCount` >= @__minBanCount_1)) AND (`s`.`IPAddressId` = `i`.`IPAddress`)) AND `i`.`IPAddress` NOT IN (
    SELECT DISTINCT `i2`.`IPAddress`
    FROM `Submissions` AS `s2`
    INNER JOIN `IPAddresses` AS `i2` ON `s2`.`IPAddressId` = `i2`.`IPAddress`
    WHERE (`s2`.`Timestamp` >= @__cutOff_1) AND (`i2`.`BanCount` >= 5)
)
GROUP BY `i`.`IPAddress`, `i`.`BanCount`
ORDER BY `c` DESC

Posted a bug / feature request with Oracle, they say it is a known issue. So there are two work-arounds:

1] Only use hard-coded values in sub-queries, no parameters. Not really an option for me, but may be for some people.

2] Create a temporary data set and filter the outer query with it in code behind. This is the route I went with. I create a set of strings for the inner query using a separate query, then I filter the outer query with this set in code behind to produce the final set.


Return top 100 words from strings Not sure this is even possible but I was wondering if there is any way of using PostgreSQL to return the top 100 most occurring words from a database column containing strings of varying lengths?

The table contains ~ 250k rows, with each row containing a message among much other metadata. So, is there a way of sifting through every word in each row, taking the total number of times it appears and comparing it to a count of all the other words in the same column to return the top 100?

If it is easier, the metadata can be dropped to leave only the column containing the Tweets.

Ideally, I would like to be able to perform what this website does to give the same output, only with many more records.

Assuming that multiple "words" are separated by a space character.

It's not clear whether you want a compare total counts across the whole table or counts per row. for the whole table:

SELECT word, count(*) AS ct
FROM   tbl, unnest(string_to_array(message, ' ')) word  -- implicit LATERAL join
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  100;  -- top 100

Or, faster, but less clean, with the set-returning function in the SELECT list:

SELECT unnest(string_to_array(message, ' ')) AS word, count(*) AS ct
FROM   tbl
GROUP  BY 1
ORDER  BY 2 DESC
LIMIT  100;

For natural language texts you could do a lot more: Remove punctuation characters, compare word stems etc. Full Text Search provides various tools. The debugging function ts_stat() seems particularly handy for this. For an English text:

SELECT word, ndoc, nentry
FROM   ts_stat($$SELECT to_tsvector('english', message) FROM tbl$$)
ORDER  BY nentry DESC
LIMIT  100;

ndoc .. the number of rows
nentry .. the number of occurrences (can be bigger with multiple instances in a single text.

The query returns the most common words in the column across the whole table - just like the first query, but now punctuation and stop words are trimmed and words are reduced to their English word stem.

To find the words contained in the most rows, use instead:

ORDER  BY ndoc DESC

One would think it should be possible to employ a functional GIN index on to_tsvector('english', message) to make this very fast. But I did not find a way in a quick test.


Determine total no of logical/physical reads I am working on the optimization of a SP which contains some business logic using looping. I have removed the looping and converted those piece of code into some simple insert/update statements.

Now I've to do benchmarking and compare old and new code in terms of execution time and logical/physical reads.

My problem is because of the loop in my old code, how can I determine what is the total no of logical/physical reads. Because in SSMS, I can see thousands of IO stats statements like:

"Table 'Employee'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 43, lob physical reads 0, lob read-ahead reads 0."

I can think of 3 options to get the total reads:

Extended Event session
SQL Profiler
IO Statistics Parser

IO Statistics Parser is pretty simple, just copy/paste your STATISTICS IO output and voila!


How does ORDER BY FIELD() in MySQL work internally I understand how ORDER BY clause works and how the FIELD() function works.
What i want to understand is how the both of them work together to sort.
How are the rows retrieved and how is the sort order derived

+----+---------+
| id |  name   |
+----+---------+
|  1 | stan    |
|  2 | kyle    |
|  3 | kenny   |
|  4 | cartman |
+----+---------+

SELECT * FROM mytable WHERE id IN (3,2,1,4) ORDER BY FIELD(id,3,2,1,4)

The query above will result in

+----+---------+
| id |  name   |
+----+---------+
|  3 | kenny   |
|  2 | kyle    |
|  1 | stan    |
|  4 | cartman |
+----+---------+

something similar to saying ORDER BY 3, 2, 1, 4

QUESTIONS

How does this work internally?
How does MySQL get the rows, and calculate the sort order ?
How does MySQL know it has to sort by the id column ?

For the record

SELECT * FROM mytable WHERE id IN (1,2,3,4) ORDER BY FIELD(id,3,2,1,4);

should work as well because you do not have to order the list in the WHERE clause

As for how it works,

FIELD() is a function that returns the index position of a comma-delimited list if the value you are searching for exists.

IF id = 1, then FIELD(id,3,2,1,4) returns 3 (position where 1 is in the list)
IF id = 2, then FIELD(id,3,2,1,4) returns 2 (position where 2 is in the list)
IF id = 3, then FIELD(id,3,2,1,4) returns 1 (position where 3 is in the list)
IF id = 4, then FIELD(id,3,2,1,4) returns 4 (position where 4 is in the list)
IF id = anything else, then FIELD(id,3,2,1,4) returns 0 (not in the list)

The ORDER BY values are evaluated by what FIELD() returns

You can create all sorts of fancy orders

For example, using the IF() function

SELECT * FROM mytable
WHERE id IN (1,2,3,4)
ORDER BY IF(FIELD(id,3,2,1,4)=0,1,0),FIELD(id,3,2,1,4);

This will cause the first 4 ids to appear at the top of the list, Otherwise, it appears at the bottom. Why?

In the ORDER BY, you either get 0 or 1.

If the first column is 0, make any of the first 4 ids appear
If the first column is 1, make it appear afterwards

Let's flip it with DESC in the first column

SELECT * FROM mytable
WHERE id IN (1,2,3,4)
ORDER BY IF(FIELD(id,3,2,1,4)=0,1,0) DESC,FIELD(id,3,2,1,4);

In the ORDER BY, you still either get 0 or 1.

If the first column is 1, make anything but the first 4 ids appear.
If the first column is 0, make the first 4 ids appear in the original order

YOUR ACTUAL QUESTION

If you seriously want internals on this, goto pages 189 and 192 of the Book

for a real deep dive.

In essence, there is a C++ class called ORDER *order (The ORDER BY expression tree). In JOIN::prepare, *order is used in a function called setup_order(). Why in the middle of the JOIN class? Every query, even a query against a single table is always processed as a JOIN (See my post Is there an execution difference between a JOIN condition and a WHERE condition?)

The source code for all this is sql/sql_select.cc

Evidently, the ORDER BY tree is going to hold the evaluation of FIELD(id,3,2,1,4). Thus, the numbers 0,1,2,3,4 are the values being sorted while carrying a reference to the row involved.


Default Schema allows dbo table selection without using schema prefix I have created a Login and a database user on my server as follows:

USE master
GO
CREATE LOGIN MyLogin WITH PASSWORD=N'Password123!'
GO
USE AdventureWorks2014
GO
CREATE USER MyLogin FOR LOGIN MyLogin
GO
ALTER USER MyLogin WITH DEFAULT_SCHEMA=HumanResources
GO
GRANT SELECT ON SCHEMA :: Production TO MyLogin
GO
GRANT SELECT ON SCHEMA :: HumanResources TO MyLogin
GO
GRANT SELECT ON SCHEMA :: dbo TO MyLogin

When I log in as MyLogin I can now select objects in the HumanResources schema without using the four part name (as HumanResources is the default schema for this user)

SELECT * FROM HumanResources.Department

can now be run without the schema name:

SELECT * FROM Department

This is fine. If I then try to select from a table in the Production Schema without using the schema prefix:

SELECT * FROM ProductDescription

I get an error as expected. This can be remedied by using:

SELECT * FROM Production.ProductDescription

So based on this, I need to specify the schema name when selecting from any table outside the default schema.

So why when I select from a table in the dbo schema, do I still not need to use the schema prefix?

SELECT * FROM DatabaseLog

returns results

I am confused. I thought if a schema name does not prefix the table in a SELECT statement, that the default schema is used and selecting from a table in any other schema would require the schema prefix.

Is the dbo schema an exception to this rule?

You don't need the prefix when some object is in your default schema because SQL Server checks for a non-schema-prefixed object in your default schema first. It will then check the dbo schema, so you can actually get away with this laziness in two different ways.

From the documentation:

  When database objects are referenced by using a one-part name, SQL Server first looks in the user's default schema. If the object is not found there, SQL Server looks next in the dbo schema. If the object is not in the dbo schema, an error is returned.

They say "one-part name" but this also applies when you reference an object with a pattern like:

[server].[database]..[name]

or

[database]..[name]

This is nothing but a confirmation that you should just always specify schema name. Always.

Bad habits to kick : avoiding the schema prefix


Mysql - Replication of multiple database I'm trying to do a redudancy with Mysql.
I'm making master-master.
I'm very happy because it works with one database. When I add something in a table, it's added on the table of the second server. The 2 DB are NOT on the same server.
 but I want to add other database.

So, I add this to both server:

replicate-do-db = other
binlog-do-db = other

I restarted mysql, but the result of "show master status" is the same (specially "Binlog_Do_DB" (and the database isn't in both server).

You should remove the filters you placed in the my.cnf file, so you could replicate all the changes you did in all databases


SQL Server Agent Job Next Run DateTime Stuck In The Past The job is enabled, the schedule is enabled, the agent service is running, but the job is not running as it is supposed to. Looking in the Job Activity Monitor, it shows a Next Run that is actually in the past.

Running the job manually will cause the Next Run to advance to the correct scheduled time for a new run, but when the time comes, the job still won't run.

How is this odd issue fixed?

This happened because you did a mass disable and then re-enable by updating the enabled flag in msdb.dbo.sysjobs after a massive outage; instead of doing things the right way and using the stored procedure,
sp_update_job.

All of the jobs that were enabled one-by-one via the GUI worked fine, it was just those mass enabled that were a problem. Doing a quick disable then enable resolved all the issues.

See link for further info:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-update-job-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15

Syntax:

EXEC dbo.sp_update_job  @job_name = N'[Your Job's Name HERE]',
@enabled = 0 --- This disables, set to 1 to enable;

Don't update system tables directly next time.


Transaction stays in capture queue I set up simple bidirectional db replication using Oracle Streams. I used enterprise manager for this. Streams are active and their status is ok. The problem is that the replication is very slow. What happened:

I made insert into on machine A.
I checked but there was no data in machine B.
I opened View Capture Statistics on machine A and my transaction was under Active Streams Transactions List (Running for more than 1 minute) and was Active For over 1000 seconds.
I went to make some food and when I came back transaction had been eventually propagated to machine B.

So it looks like the transaction stayed in some kind of buffer for over 1000 seconds. How can I speed up this process? I am totally new in databases so please forgive me if it is a stupid question :).

Machines are just vbox instances (WinXP + Oracle 11g Enterprise).

Greetings.

I was using sqlplus and I forgot to commit the changes. Before the commit, changes appeared only in the source database, but after I had committed them, the destination database got the new data.


mysql cannot start because of init.d folder? After I setup mysql 5.5, I changed the data dir to another folder and modify the related config in my.cnf. This is quite simple, but mysql cannot start anymore even if reinstall or not change data dir.

The error log has no useful information, so I cannot find any reason.

  111207 16:28:02 mysqld_safe Starting mysqld daemon with databases from
  /var/lib/mysql 111207 16:28:02 mysqld_safe mysqld from pid file
  /var/lib/mysql/localhost.localdomain.pid ended

But if I use the command as the start script, mysql starts normally.

  /usr/bin/mysqld_safe --datadir=/data/mysql/
  --pid-file=/data/mysql/localhost.localdomain.pid > /dev/null 2>&1

Much more strange, I use /usr/share/mysql/mysql.server start instead of /etc/init.d/mysql start, mysql also can start without error, while these two files are the same. So in the end I have to use cp -l /usr/share/mysql/mysql.server /etc/init.d/mysql to fix the problem.

I really want to know why and if there is a better solution!

In the first one it is looking for a DB in /var/lib/mysql (the default) and in the second, it is looking in /data/mysql, where you have moved to.