Use xQuery to extract attribute values that also exist in SQL variable XML structure:

<ns0:message xmlns:ns0='xxx:testing'>
  <ns0:field name='AAA'>...</ns0:field>
  <ns0:field name='BBB'>...</ns0:field>
  <ns0:field name='VVV'>...</ns0:field>
  <ns0:field name='CAR'>...</ns0:field>
</ns0:message>

I have SQL that will extract the values of the attributes titled name:

SELECT
  ( (SELECT ',' + CHAR(13) + P.N.value('@name', 'varchar(max)')
       FROM myTable.message_xml.nodes('declare namespace ns0="xxx:testing";
                                       ns0:message/ns0:field[ @name = "AAA" or
                                                              @name = "BBB"]') P(N)
        FOR XML PATH(''), type).value('substring(text()[1], 3)', 'varchar(max)')) as ATTRIBUTE_VALUES

This returns a column that looks like:

ATTRIBUTE_VALUES
---------------
AAA,
BBB

My problem is that the list of potential attribute values is quite large.
Instead of repeating @name = "AAA" in my query for every attribute value I want to check for, I was hoping I could declare it as a variable like:

DECLARE @ATTRIBUTES VarChar(Max)
SET @ATTRIBUTES = '(AAA,BBB,CAR,XYZ)'

And then just stick the variable in the sql like:

[@name = sql:variable("@ATTRIBUTES")]

but this is not working for any combination of parens,commas,etc I use to build the variable.

XML in SQL Server is good at shredding and getting values so for performance it is probably almost best to focus on extracting the data you want and then do the logic in SQL instead.

Put the values you are looking for in a table of some kind and filter your extracted values against that table instead. If your input is a string you could use a string split function of your choice to get them into a table.

declare @T table(X xml);
insert into @T(X) values('<ns0:message xmlns:ns0=''xxx:testing''>
  <ns0:field name=''AAA''>...</ns0:field>
  <ns0:field name=''BBB''>...</ns0:field>
  <ns0:field name=''VVV''>...</ns0:field>
  <ns0:field name=''CAR''>...</ns0:field>
</ns0:message>');

declare @V table(name varchar(10));
insert into @V(name) values('AAA'),('BBB'),('CAR'),('XYZ');

with xmlnamespaces(default 'xxx:testing')
select T.name
from (
     select F.X.value('@name', 'varchar(10)') as name
     from @T as T
       cross apply T.X.nodes('/message/field') as F(X)
     ) as T
where exists (
             select *
             from @V as V
             where V.name = T.name
             );

If you are really looking for a XQuery solution you could use contains

declare @T table(X xml);
insert into @T(X) values('<ns0:message xmlns:ns0=''xxx:testing''>
  <ns0:field name=''AAA''>...</ns0:field>
  <ns0:field name=''BBB''>...</ns0:field>
  <ns0:field name=''VVV''>...</ns0:field>
  <ns0:field name=''CAR''>...</ns0:field>
</ns0:message>');

declare @S varchar(100) = ',AAA,BBB,CAR,XYZ,';

with xmlnamespaces(default 'xxx:testing')
select T.X.query('data(/message/field/@name[contains(sql:variable("@S"), concat(",", ., ","))])')
from @T as T;


Oversize DB restore to SQLExpress I made a back of an express DB, it was 14.7GB big (how is this possible) but anyway, I need to restore it back to express but obviously it is too big, but there must be somehow I can do this. I need to get it restored and then I can shrink it. Please help this is urgent and need to get it up in 1 hour.

You should be able to just do the restore. Did you try it?

SQL Server will create the mdf and ldf files for you, with the same size they had when the backup was produced. And if the backup came from an express, then it shouldn't be possible to have a more than 10 GB size mdf - and there's no restriction on the ldf.

So just try the restore command (possibly using the GUI) and if it doesn't work, let us know what command you executed (you can script it in SSMS) and the error message(s) you got.


Average of last 4 runs of a product I need to show the average weight of a product over its last 4 production runs. I'm not sure how best to describe it other than an example: Let's imagine I have the following table that lists a product by date it was created, and the average weight of the product for that day:

+---------+---------+--------+
| Product |  Date   | Weight |
+---------+---------+--------+
|  900000 | Jan 1   | 20.0   |
|  900000 | March 3 | 12.2   |
|  900000 | July 6  | 15.0   |
|  900000 | July 7  | 14.0   |
|  900000 | Aug 6   | 3.0    |
|  800000 | June 2  | 14.0   |
|  800000 | June 3  | 12.0   |
+---------+---------+--------+

The end result I'm looking for is to add a column that includes the average Weight for the last 4 dates the product was run for, so something like this:

+---------+---------+--------+----------------+
| Product |  Date   | Weight | Average Weight |
+---------+---------+--------+----------------+
|  900000 | Jan 1   | 20.0   | NULL           |
|  900000 | March 3 | 12.2   | NULL           |
|  900000 | July 6  | 15.0   | NULL           |
|  900000 | July 7  | 14.0   | NULL           |
|  900000 | Aug 6   | 3.0    | 15.3           | Jan1+Mar3+July6+July7/4
|  900000 | Aug 8   | 13.0   | 11.05          | Mar3+July6+July7+Aug6/4
|  800000 | June 2  | 14.0   | NULL           |
|  800000 | June 3  | 12.0   | NULL           |
|  800000 | June 4  | 12.0   | NULL           |
|  800000 | June 5  | 12.0   | NULL           |
|  800000 | June 6  | 12.0   | 12.5           | etc...
+---------+---------+--------+----------------+

The NULL's are just there since in this sample you can't calculate the average over the last 4 runs because the data isn't there.

Could anyone point me in the direction I need to be looking to do something like this?

Sample data:

CREATE TABLE dbo.Thing
(
    Product integer NOT NULL,
    TheDate date NOT NULL,
    TheWeight decimal(5, 1) NOT NULL
);

INSERT dbo.Thing
    (Product, TheDate, TheWeight)
VALUES
    (900000, CONVERT(date, '20160101', 112), 20.0),
    (900000, '20160303', 12.2),
    (900000, '20160706', 15.0),
    (900000, '20160707', 14.0),
    (900000, '20160806', 3.0 ),
    (900000, '20160808', 13.0 ),
    (800000, '20160602', 14.0),
    (800000, '20160603', 12.0),
    (800000, '20160604', 12.0),
    (800000, '20160605', 12.0),
    (800000, '20160606', 12.0);

Solution:

The general idea here is to use the extended window aggregate functions available in SQL Server 2012 and later.

The only wrinkle is that AVG does not return null over a window if it is smaller than the required four rows. To address that, we also calculate the number of rows found in the window using COUNT. A simple CASE expression can then be used to return a null if the window holds fewer than four rows:

SELECT
    T.Product,
    T.TheDate,
    T.TheWeight,
    [Average Weight] =
        CASE
            WHEN
                4 > COUNT_BIG(*) OVER (
                    PARTITION BY T.Product
                    ORDER BY T.Product, T.TheDate
                    ROWS BETWEEN 4 PRECEDING
                    AND 1 PRECEDING
                    )
                THEN NULL
            ELSE
                AVG(T.TheWeight) OVER (
                    PARTITION BY T.Product
                    ORDER BY T.Product, T.TheDate
                    ROWS BETWEEN 4 PRECEDING
                    AND 1 PRECEDING
                    )
        END
FROM dbo.Thing AS T
ORDER BY
    T.Product,
    T.TheDate;

Run the query on Stack Exchange Data Explorer

Output:

More information:

Window Functions in SQL Server

Related question:

Date range rolling sum using window functions


First, since the table is tiny (only 1 row?), you cannot trust EXPLAIN to give you information that will apply in a 'real' situation.
Indexing a flag is rarely useful.  The Optimizer looks at the situation as says (roughly):  "If I need to scan more than 20% of the table, I won't bother using the index."  This is a reasonable decision because using an index requires bouncing between the index's BTree and the data's BTree.  Whereas, scanning the table sequentially is likely to be more efficient, in spite of having to check and throw out rows that are not needed.
OK, id is not a flag.  But, to the optimizer it may as well be -- since you have only 1 row in your table?


Is MongoDB IXSCAN operator a scan or a seek? It seems to me that the only operators I see in a MongoDB query plan are COLLSCAN or IXSCAN. The indexes being b-tree, I guess MongoDB can do index seek and index scan ? If so, how do we know that a seek id performed ?

For instance, in this plan (just a fragment) :

 "executionStats" : {
    "executionSuccess" : true,
    "nReturned" : 17,
    "executionTimeMillis" : 42,
    "totalKeysExamined" : 25359,
    "totalDocsExamined" : 1553,
    "executionStages" : {
        "stage" : "FETCH",
        "filter" : {
            "address.zipcode" : {
                "$eq" : "10075"
            }
        },
        "nReturned" : 17,
        "executionTimeMillisEstimate" : 30,
        "works" : 25360,
        "advanced" : 17,
        "needTime" : 25342,
        "needYield" : 0,
        "saveState" : 198,
        "restoreState" : 198,
        "isEOF" : 1,
        "invalidates" : 0,
        "docsExamined" : 1553,
        "alreadyHasObj" : 0,
        "inputStage" : {
            "stage" : "IXSCAN",
            "filter" : {
                "cuisine" : {
                    "$regex" : ".*alian",
                    "$options" : "i"
                }
            },
            "nReturned" : 1553,
            "executionTimeMillisEstimate" : 30,
            "works" : 25360,
            "advanced" : 1553,
            "needTime" : 23806,
            "needYield" : 0,
            "saveState" : 198,
            "restoreState" : 198,
            "isEOF" : 1,
            "invalidates" : 0,
            "keyPattern" : {
                "cuisine" : 1.0
            },
            "indexName" : "cuisine_1",
            "isMultiKey" : false,
            "multiKeyPaths" : {
                "cuisine" : []
            },
            "isUnique" : false,
            "isSparse" : false,
            "isPartial" : false,
            "indexVersion" : 2,
            "direction" : "forward",
            "indexBounds" : {
                "cuisine" : [
                    "[\"\", {})",
                    "[/.*alian/i, /.*alian/i]"
                ]
            },
            "keysExamined" : 25359,
            "seeks" : 1,
            "dupsTested" : 0,
            "dupsDropped" : 0,
            "seenInvalidated" : 0
        }

I can guess it is a scan (I did a regexp search on purpose) :

"totalKeysExamined" : 25359,
"totalDocsExamined" : 1553,

but I see

"seeks" : 1,

Is there any rule as how to interpret the plan, and why there is no IXSEEK operator ?

I guess MongoDB can do index seek and index scan ?

Yes MongoDB can do index seek and index scan, but it can do both during one fetch.  That is what happened in your case. If started with a seek to the leftmost record of your index cuisine_1 and did a scan on 25359 keys.  During the scan of 25359 keys 1553 key matched your search criteria. Index not being a covering one, it had to do a lookup (for other columns) from 1553 documents.

If so, how do we know that a seek id performed ?

It will always do a seek if it it using a index, I guess your question is when it is not doing a seek + scan but only seek?

IXSCAN means:

  If the query planner selects an index, the explain result includes a
  IXSCAN stage. The stage includes information such as the index key
  pattern, direction of traversal, and index bounds.

If you see this, it will indicate a seek to one index key and no scan.  But anything more than one will indicate a seek + scan.

  "nReturned" : 1,
  "totalKeysExamined" : 1,


Is an automatic failover to the primary replica of a readonly replica in an availability group possible? I want to set up an availability group with two nodes. One node will be the primary replica and the second one will be set up as a read-only replica.

In my application I do all active stuff with a read/write-connection and the reporting stuff with a read-only connection. The read-only connection will connect to the read-only replica.

But what happens if the node with the read-only replica goes offline? As far as I understand the documentation, a read-only connection can't be established any more if there is no read-only replica available in the group.

Is there a mechanism I can use to fallback with the read-only connection to the primary replica? Or do I have to build such a fallback manually in my application?

This is covered in the documentation: Configure Read-Only Routing for an Availability Group (SQL Server), but for this approach to work, you will need to configure your read only routing URL on both replicas.

  Connections with the "ReadOnly" property set in the connection string will be redirected to the secondary replica. If this secondary replica is not readable (as determined by the ConnectionModeInSecondaryRole setting), the connection will be directed back to the primary replica.

This is a PowerShell example from MSDN on how to add read only routing URLs to both the replicas available:

Set-Location SQLSERVER:\SQL\PrimaryServer\default\AvailabilityGroups\MyAg
$primaryReplica = Get-Item "AvailabilityReplicas\PrimaryServer"
$secondaryReplica = Get-Item "AvailabilityReplicas\SecondaryServer"

Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://PrimaryServer.domain.com:1433" -InputObject $primaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingConnectionUrl "TCP://SecondaryServer.domain.com:1433" -InputObject $secondaryReplica
Set-SqlAvailabilityReplica -ReadOnlyRoutingList "SecondaryServer","PrimaryServer" -InputObject $primaryReplica

There are some requirements for connection strings as well,if you want to use read only routing:

Use the TCP protocol.
Set the application intent attribute/property to readonly.
Reference the listener of an availability group that is configured to support   read-only routing.
Reference a database in that availability group.


Pull more records based on IDs in comma separated list returned by GROUP_CONCAT Is it possible to pull more records based on IDs in comma separated list return by GROUP_CONCAT? Is a LEFT JOIN possible here? If so, how can it be done. I am constantly failing at figuring this out, in addition, I've googled a lot already.

I have these two tables:

CREATE Table users (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL
);

CREATE Table articles (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT(100) NOT NULL,
  title VARCHAR(50) NOT NULL,
  content TEXT,
  published DATETIME NOT NULL
);

Insert:

INSERT INTO users (username, password)
VALUES ('Bob', '5f4dcc3b5aa765d61d8327deb882cf99');

INSERT INTO users (username, password)
VALUES ('David', '630bf032efe4507f2c57b280995925a9');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Science 101', 'Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.',
        '2015-11-30 09:18:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Health Care', 'Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.',
        '2016-01-10 15:20:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('2', 'Physics 101', 'Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.',
        '2016-01-17 14:18:43');

Query:

SELECT
  articles.id,
  articles.author_id,
  users.username,
  COUNT(articles.id) AS number_of_articles,
  GROUP_CONCAT(DISTINCT articles.id) AS articles_published
FROM articles
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id
ORDER BY articles.published DESC;

Result:

id  author_id   username    number_of_articles  articles_published
3   2           David       1                   3
2   1           Bob         2                   2,1

Expected result:

Bob's articles

Health Care
Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.

Science 101
Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.

------

David's articles

Physics 101
Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.

-----

SQLfiddle

I think you could use the following query to get the data you need:

SELECT
  users.username,
  title, content
FROM articles
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id , title
ORDER BY  articles.author_id ,articles.published DESC;

The presentation of it depends on the programming language you will use to display the results.


Invert a Boolean expression which can return UNKNOWN Example
I have a table
ID  myField
------------
 1  someValue
 2  NULL
 3  someOtherValue

and a T-SQL Boolean expression which can evaluate to TRUE, FALSE or (due to SQL's ternary logic) UNKNOWN:
SELECT * FROM myTable WHERE myField = 'someValue'

-- yields record 1

If I want to get all the other records, I cannot simply negate the expression
SELECT * FROM myTable WHERE NOT (myField = 'someValue')

-- yields only record 3

I know how why this happens (ternary logic), and I know how to solve this specific issue.
I know I can just use myField = 'someValue' AND NOT myField IS NULL and I get an "invertible" expression which never yields UNKNOWN:
SELECT * FROM myTable WHERE NOT (myField = 'someValue' AND myField IS NOT NULL)

-- yields records 2 and 3, hooray!

General Case
Now, let's talk about the general case. Let's say instead of myField = 'someValue' I have some complex expression involving lots of fields and conditions, maybe subqueries:
SELECT * FROM myTable WHERE ...some complex Boolean expression...

Is there a generic way to "invert" this expession? Bonus points if it works for subexpressions:
SELECT * FROM myTable
 WHERE ...some expression which stays...
   AND ...some expression which I might want to invert...

I need to support SQL Server 2008-2014, but if there's an elegant solution requiring a newer version than 2008, I'm interested to hear about it too.

You could enclose the condition in a CASE expression that returns a binary result, for instance 1 or 0:

SELECT
  ...
FROM
  ...
WHERE
  CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1
;

Negating the expression will give you all the other rows from the same data source, including those where someColumn is null:

SELECT
  ...
FROM
  ...
WHERE
  NOT CASE WHEN someColumn = someValue THEN 1 ELSE 0 END = 1
  -- or: CASE WHEN someColumn = someValue THEN 1 ELSE 0 END <> 1
;

Since SQL Server 2012 you also have the IIF function, which is just a wrapper around a binary CASE like above. So, this CASE expression:

CASE WHEN someColumn = someValue THEN 1 ELSE 0 END

will look like this if rewritten using IIF:

IIF(someColumn = someValue, 1, 0)

And you can use it exactly the same way as the CASE expression. There will be no difference in performance, only the code will be slightly more concise, possibly cleaner that way too.


Psycopg2 error at or near % Note location of cursor. At first I thought it was the lack of a semi colon, but as you can see, putting one in does not solve the problem or change the error. I compared this code to samples I found on http://initd.org/psycopg/docs/usage.html :

Based on this, I don't see the problem. FWIW, Pylint in ST3 has no issues with this code. thanks.

from os import getcwd, listdir
import psycopg2
from io import open

conn = psycopg2.connect("dbname=ktab user=malikarumi")
cur = conn.cursor()
path = getcwd()
filenames = listdir(path)

for filename in filenames:
with open(filename, 'rb') as f:
    f1 = f.read()
    cur.execute("INSERT INTO testable (title, content, chron_date, clock)\
    VALUES (%s, %s, %s, %s);"),
    (filename, f1, '2017-12-30', '23:59:00'),

conn.commit()
cur.close()
conn.close()

You have misplaced parenthesis.

with open(filename, 'rb') as f:
    f1 = f.read()
    cur.execute(
        "INSERT INTO testable (title, content, chron_date, clock) VALUES (%s, %s, %s, %s);", # remove parenthesis here, which ends the execute call
        (filename, f1, '2017-12-30', '23:59:00')
    ) # move above parenthesis here


How to get dates from a table while the date range is from the date belonging to another table? I have a table datedim which has all the dates of a month in it. I have another table attendance that has dates on which attendance were taken. I need to write a query where i can get all the dates from the start till current.
For eg. if attendance table has records starting from 3-Apr-2019 to 3-June-2019, i should get all the dates in between them.

You can use the BETWEEN conditional operator to search for anything in a range. The BETWEEN operater is inclusive so will find records for the start and end dates too.

You will be able to query the attendance table directly:

SELECT * FROM attendance WHERE attendancedate BETWEEN '2019-04-03' AND '2019-06-03'

This question has some examples:
https://stackoverflow.com/questions/3822648/how-do-i-query-between-two-dates-using-mysql

I hope that helps!


sp_whoisactive in SSRS I have setup sp_whoisactive as a report in SS Reporting Services so people less familiar with SSMS can run it and see what is happening when needed. I have it working good and BUT I can't seem to figure out how to show the "sql text" column as a hyperlink in SSRS as it's shown in SSMS. Anyone have any ideas?
Thanks!

fyi, we ended up doing this:

https://docs.microsoft.com/en-us/sql/reporting-services/report-design/add-a-hyperlink-to-a-url-report-builder-and-ssrs
... add a hyperlink

On the Action tab, select Go to URL. An additional section appears in the dialog box for this option.
In Select URL, type or select a URL or an expression that evaluates to a URL, or click the drop-down arrow and click the name of a field that contains a URL.
For an item published to a report server configured for native mode, use a full or relative path. For example, http:///images/image1.jpg.
For an item published to a report server configured in SharePoint integrated mode, use a fully qualified URL. For example, http:////Documents/images/image1.jpg.
Click OK.

After you add a hyperlink

(Optional) The text is not automatically formatted as a link. For text, it is helpful to change the color and effect of the text to indicate that the text is a link. For example, change the color to blue and the effect to underline in the Font section in the Home tab of the Ribbon.
To test the link, click Run to preview the report, and then click the report item that you set this link on.


Updating SQL Server Database I have a database AfricanRatings on which I used the query

INSERT INTO AfricanRatingsAVG
SELECT RecipeID,
       Count(*) AS cnt,
       AVG(Rating)
FROM   AfricanRatings
GROUP  BY RecipeID
ORDER  BY RecipeID

to get the average rating per recipe and the total count per recipeID.  The average and the count were placed into a table AfricanRatingsAVG via the query. On the initial query, AfricanRatingsAVG is updated but not with subsequent ratings.  I have tried all sorts of UPDATES to get the AfricanRatingAVG table to update every time a new rating is added to the AfricanRatings table, however, no update occurs.  Any guidance in the right direction would be appreciated.

Use an indexed view instead:

CREATE VIEW AfricanRatingsAVG
WITH SCHEMABINDING
AS
SELECT RecipeID,
       COUNT_BIG(*) AS cnt,
       AVG(Rating) as rating
FROM   dbo.AfricanRatings
GROUP  BY RecipeID;
GO

CREATE UNIQUE CLUSTERED INDEX cdxAfricanRatingsAVG on AfricanRatingsAVG(RecipeID);

The engine will maintain the cnt and avg for you. Use the NOEXPAND hint when querying the view:

  When NOEXPAND is specified for a view, the query optimizer considers
  using any indexes defined on the view.


Does the Fill Factor setting affect heaps / heap tables with non-clustered indexes? I came across this old article on Brent Ozar's blog (written by Kendra Little) regarding Fill Factor: https://www.brentozar.com/archive/2013/04/five-things-about-fillfactor/

In it, she briefly mentions that the Fill Factor setting does not affect Heaps.

Does this still apply today, and does she mean only pure heaps that don't even have non-clustered indexes?...or to any heap table regardless if it has non-clustered indexes?

The non-clustered indexes on a heap will use the fill factor, but a heap is not an index (although it does appear in sys.indexes).

Since there is no way to reorganize a heap it can't have a fill factor. Fill factor only comes into the picture when rebuilding or creating an index; it only applies to indexes.

I believe How Forwarded Records are Read and Processed in a SQL Server Heap by Uwe Ricken may help you out. Specifically regarding how SQL Server uses PFS to determine inserts for records into the heap, and also how a non-clustered index uses the RID in relation to the heap.


Why did full backup file size grow after data movement for Availability Group replica was suspended? Found an error log informing that data movement to a secondary availability replica was suspended. I'm not sure whether the cause of the suspension is relevant to answering my question, but the cause was "Error 3456 ... could not redo log record" etc. This is a known issue with a resolution involving patching SQL Server.
However this condition wasn't addressed for a few days. The nightly full backup file sizes (taken against the primary replica) began increasing by about 10% per night. The database also has log backups made every 15 minutes for most hours in the day, but log backups are disabled during a window at night while indexes are rebuilt and full backups made.
To get the secondary back online it was required to restore from backup. In this instance some issues were encountered - first an attempt was made to rejoin the AG but that hung for ages. Then a command was given to have that replica database removed from the AG but of course it hung too. Killed both those processes but could not kill the session that had faulted in redoing the log records (as it is not a user session - although its ID was above 50 by the way). I therefore restarted SQL Server on that replica before eventually restoring from backup and rejoining the AG.
Having resolved the issue, the next nightly full backup file size (on the primary, again) had reduced back to normal size.
Why would the full backup file increase in size until this issue was resolved? Is it because the log could not be cleared and this was being included in the full backup? If so, then what was getting backed up in the 15-minutely log backups? Imagine for a moment that the primary had suffered a failure during this period - could I not have restored from the available full and log backup files I had (ie. because obviously something remained in the log such that it wasn't clearing)? And if it was a case of the log not clearing, then why (because the secondary had been suspended, so surely the primary no longer waits for the secondary before committing its own log)?
Afterthought: the system process that I could not kill without a server restart was the one that faulted while attempting to apply the logs to the secondary - leading the secondary to be suspended. I suppose this is a "long running transaction"? If this is the reason, can someone still please clarify for me the earlier questions - particularly about how "at risk" the primary was until this was resolved?

I'm going to hazard a guess here -
Given that the fault that led to the replica being suspended is an acknowledged bug in SQL Server, my guess is that as part of the bug, the primary was somehow continually re-trying to send synchroisation updates to the secondary. Those could not be hardened on the secondary's log so the primary retained the transaction information and the continual re-tries slowly increased the amount of log being used for this transaction.
Further, all other transactions on the primary were not being synced to the secondary, and so were persisted to the primary's log and the log backups on the primary were backing up this information.
Lastly, the fact the primary "held on" to the secondary in this way (with the thread that didn't die) is simply also a part of the bug, or is an additional bug that was exposed when the secondary failed to apply the logs. EDIT: It looks like this is a second known issue. In other words, SQL Server should have "let go" of the secondary once the secondary was suspended, but did not, and that is also a bug.
As to the question of what might have been lost had the primary failed during this extended period - my best guess is that the loss would have been limited to information that was tying up the log and that information related to keeping the secondary in sync. If the primary wasn't persisting the changes until receiving confirmation from the secondary that the secondary had hardened that log, then whatever changes were being carried out by the transaction being synced, would have been lost - but all other transaction would have been persisted to the primary (only) and subsequently backed up during the log backups (and then nightly full backups).
Certainly open to feedback on this answer, as it is a "best guess" for the scenario.


How to access oracle PDB remotely? I have create oracle 12c instance with 2 PDBs, I am able to connect to CDB from python using cx_Oracle module but not sure how to connect to PDB.

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

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

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

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

5 rows selected.

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

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

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

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

3 rows selected.

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

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

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

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

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

system@XEPDB1>

Best of luck!


Configuring SQL Server profiler Every 15 seconds or so, the application I am trying to profile fires:

@@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN
IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF
IF @@TRANCOUNT > 0 COMMIT TRAN SET IMPLICIT_TRANSACTIONS OFF
elect top 1 accountId from account with (nolock)

Is this a regular SqlServer pattern?

And how I can configure the profiler so I do not see it?

IT is coming from the same process  and user as the process,user  for which I want to configure SQL profiling for.

Many thanks

If you want to omit those specific SQL statements, you could probably put them into the TextData NOT LIKE filter for the trace. Escape the underscore with [_].


phpMyAdmin Strange permissions #1142 error after deleting a table record with success! A have a live/production CentOS 6.8 server with Nginx, PHP (multiple versions), MySQL 5.7.18 and latest (when writing this question) phpMyAdmin 4.7.0.

This server is running for many years and with no problems, but recently I noticed a problem when I tried to delete a record from a table using phpMyAdmin, I got the following error:

  #1142 - SELECT command denied to user 'test_user'@'localhost' for
  table 'pma__tracking'

Strange thing is that it deletes the record but it can't refresh the result after the deletion! Also, all SELECT queries work and every data browse successfully fetches data on each table!

I have each database to has its' own user with all privileges granted.

I have properly installed $cfg['Servers'][$i]['controluser'] and $cfg['Servers'][$i]['controlpass'] and all the tables exists inside $cfg['Servers'][$i]['pmadb'] = 'phpmyadmin'; database.

My question is, why this is happening and how can I fix it? It's strange for me, because I expect that the user defined inside phpMyAdmin config should access pmadb ('pma'@'localhost') and the tables in it and NOT the user that connects to a database!

This ia a bug for PMA 4.7.0.
Here is the bug tracking https://github.com/phpmyadmin/phpmyadmin/issues/13214

If anyone comes here with the same problem for PMA 4.7.0, then open the PMA/libraries/Tracker.php and alter the following lines:

Index: Tracker.php
===================================================================

// - Remove/Comment
$result = $GLOBALS['dbi']->fetchValue($sql_query);

// + Add
$row = $GLOBALS['dbi']->fetchArray(PMA_queryAsControlUser($sql_query));

// - Remove/Comment
return ($result == 1);

// + Add
return isset($row[0])
    ? $row[0]
    : -1;


Select row at the nth percentile I have two tables, table1 and table2. Let the two tables contain date, id and latency column.

I have a simple query that performs a join on the two tables and returns a set of rows:

Select table1.date,(table2.latency - table1.latency) as ans from table1, table2
where table1.id = table2.id order by ans;

I need to find the nth percentile row from the returned set of rows, lets say I need to find 90%, 99% and 99.9% percentile row from the data.

I need to display the data in a form like this:

    date       |   percentile  | ans
    01-12-1995 |    90         | 0.001563
    02-12-1999 |    99         | 0.0015
    05-12-2000 |    99.9       | 0.012

This is my first encounter to PostgreSQL. I am confused as to how should I proceed.

I was having a look at PERCENT_RANK() function. Please guide me in the correct direction.

Use the window function ntile() in a subquery (requires Postgres 8.4 or later).
Then select the segments you are interested in (corresponding to percentiles) and pick the row with the lowest value from it:

SELECT DISTINCT ON (segment)
       the_date, to_char((segment - 1)/ 10.0, '99.9') AS percentile, ans
FROM  (
    SELECT t1.the_date
          ,ntile(1000) OVER (ORDER BY (t2.latency - t1.latency)) AS segment
          ,(t2.latency - t1.latency) AS ans
    FROM   table1 t1
    JOIN   table2 t2 ON t1.id = t2.id
   ) sub
WHERE  segment IN (601, 901, 991, 1000)
ORDER  BY segment, ans;

The Postgres-specific DISTINCT ON comes in handy for the last step. Detailed explanation in this related answer on SO:
Select first row in each GROUP BY group?

To get the 90, 99 and 99.9 percentile I picked the matching granularity with ntile(1000). And added a 60 percentile as per comment.

This algorithm picks the row at or above the exact value. You can add a line to the subquery with percent_rank() to get the exact relative rank of the select row in addition:

 percent_rank() OVER (ORDER BY (t2.latency - t1.latency)) AS pct_rank

Aside: I replaced the column name date with the_date since I am in the habbit of avoiding reserved SQL key words as identifiers, even if Postgres would permit them.


SSIS 2012: What rights are needed to connect to SSDB via DMO I'm trying to connect to the SSDB catalog to execute a package using the .NET object model e.g.

using Microsoft.SqlServer.Management.IntegrationServices;

var sqlConn = new SqlConnection(connectionString);
var ssis = new IntegrationServices(sqlConn);

var project = ssis.Catalogs["SSISDB"].Folders[package.Path].Projects[package.Project];
var pinfo = project.Packages[package.Name + ".dtsx"];
var id = pinfo.Execute(false, env, packageParams)

If I run this I get an error

The server principal "XXXX" is not able to access the database \"SSISDB\" under the current security context

If I grant the user sysadmin rights it works, so it's definitely a permission issue, but I can't use that as the solution.

Under the old Integration Services model you would make the user a member of db_ssisadmin, db_ssisdtuser or db_ssisoperator - tried that and it doesn't help :-(

The answer is contained in http://blogs.msdn.com/b/mattm/archive/2012/03/20/ssis-catalog-access-control-tips.aspx

  1.To allow a login to be able to read/execute only one project, but not able to access other objects (projects or environments) in a
  folder where the project is in: a.Map it to a member of the SSISDB
  database role -- public. (This leverages SQL security mechanism.)
  b.Grant it Read to the folder, and grant it Read/Execute to the
  project. (This uses SSIS Catalog security mechanism.)

  2.To allow a login (user or group) to be able to read/execute all projects in a folder: a.Map it to a member of the SSISDB database role
  -- public.  b.Grant it Read/Execute/Read Objects to the folder.

  3.To allow a login to be able to do anything on SSISDB: a.make it a member of the SSISDB database role -- ssis_admin.


Select permission inside stored procedure? I have granted a user execute permission for a stored procedure that uses dynamic SQL.  But when he tries to execute it, he gets the error:

  The SELECT permission was denied on the object '[table name]', database '[database name]', schema 'dbo'.

Does the user need to be granted permission for any tables that the stored procedure uses? That wouldn't really make any sense to me.

Ok, on the basis of the above comment and as per my suspicion - it seems as though you are trying to execute dynamic SQL within your stored procedure.

What you need to remember is that when you do this it does not get executed within the context of the stored procedure - it gets executed within a new session. Because of this, the fact that the statement is being called within a stored procedure is a moot point, and you will need to grant explicit permission on the objects that your dynamic SQL is using.

If you don't want to do this I would refactor your stored procedure to not use dynamic SQL.

The below link from Microsoft should help you with your problem:

PRB: Security Context of Dynamic SQL Statements Inside a Stored Procedure (Wayback Machine archive)

  This behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.

This is also discussed in the (more current) Microsoft Docs article:

Writing Secure Dynamic SQL in SQL Server

  Executing dynamically created SQL statements in your procedural code breaks the ownership chain, causing SQL Server to check the permissions of the caller against the objects being accessed by the dynamic SQL.


Working example of OPENDATASOURCE command using SQL Authentication I need to run some ad-hoc queries on several hundred servers that are not part of a domain.  Each server has a low-privilege SQL user account that has read-only access to a few tables of interest.

My idea was to have the names of these servers stored in a table, and use that table to drive an OPENDATASOURCE or OPENROWSET command.

The servers are either in different domains, non-trusted domains, work-groups, etc. A big mess.

I cannot for the life of me find an example of how to configure these commands to use a SQL Server account/password combination.

Note:  This happens in a sandbox, it is not meant for the real world.  It is simply a proof of concept, so security is not a major concern.  These servers are cranked out by a process that is simulating a real-world growth of a network.

For the record, I was able to get OPENROWSET to work:

SELECT *
FROM OPENROWSET('SQLNCLI',
   'DRIVER={SQL Server};SERVER=MyServer;UID=MyUserID;PWD=MyCleverPassword',
   'select @@ServerName')

To run it with a SQL login, just need to specify User ID and Password in the connection string (called "init string" in BOL)

SELECT
    * FROM
OPENDATASOURCE (
   'SQLNCLI', -- or SQLNCLI
   'Data Source=OtherServer\InstanceName;Catalog=RemoteDB;User ID=SQLLogin;Password=Secret;').RemoteDB.dbo.SomeTable


how to query the plan cache to find how healthy it is? This morning looking at my monitoring tool I get a warning about
High Compiles

  Query plan compiles should generally be <15% of batches per second.
  Higher values indicate plan reuse is low, and will often correlate
  with high CPU, since plan compilation can be a CPU-intensive
  operation. High compiles may correlate with low plan cache hit ratios,
  and can be an indicator of memory pressure, since there may not be
  enough room to keep all plans in cache.

  If you see consistently high compiles, run a Quick Trace and sort the
  results by Cache Misses, then expand details to view actual compiling
  statements (SP:CacheMiss events, highlighted) along with the reason
  (SubClass) and procedure (Object).

WHile doing a superficial investigation I notice the
CPU is ok according to the picture below

But although I have set optimize for ad hoc workload to 1,
when I look at the SQL Server Memory Usage I see that lots of it is still used by the plan cache instead of buffer cache.
as can be seen on the picture below.

  The optimize for ad hoc workloads option is used to improve the
  efficiency of the plan cache for workloads that contain many single
  use ad hoc batches. When this option is set to 1, the Database Engine
  stores a small compiled plan stub in the plan cache when a batch is
  compiled for the first time, instead of the full compiled plan. This
  helps to relieve memory pressure by not allowing the plan cache to
  become filled with compiled plans that are not reused.

The question:

How can I find out what I can remove from the plan cache?
or at least how would I start this investigation?

Starters

Setting the SQL Server option optimize for ad hoc workloads is not really a solution to fix high recompilation values in the query plan cache.

It is however a good solution when your application is performing lots of ad hoc (hence the name) queries that run only once and which would otherwise pollute (waste) the query plan cache space. This can be, for example, an application that allows the users to select the columns of tables dynamically that they wish to see the results of.

Main Course

When SQL Server Database Engine (DBE) executes a query and this query has never been executed before, then the Database Engine has to determine how it will access the data. As soon as the DBE has determined the best way to access the data it will store this information in the Query Plan Cache (QPC), so that the users will benefit the next time the application performs the same query again (albeit, probably with slightly different values).

The SQL Server DBE will search the QPC each time the application requires a statement be run. If the DBE finds an adequate Query Plan in the QPC then it will select that Query Plan to retrieve the data. If however, the DBE is unable to determine an adequate Query Plan in the QPC (not found or timeout value reached for querying the QPC), then the DBE will create a new Query Plan. This is the resulting compiles/s you are observing.

Dessert

The root cause however can vary.

SQL Server may be under memory pressure and is unable to store enough compiled Query Plans in the QPC. The DBE will kick out old plans and insert the new ones.
 (Solution: Add more memory to the SQL Server instance)

The application is indeed generating a large amount of queries that have never before been executed and/or have slightly different values than the Query Plans stored in the QPC. (Solution: Remove complexity in application)

Answers to your questions

You can't. The query plans belong in the query plan cache. You can either clear the QPC or leave the DBE to do its best. (Selective deletion of query plans can be achieved with DBCC FREEPROCCACHE(, but I wouldn't recommend this.)
Determine what query plans are stored in the QPC and optimise the application and/or the memory settings for the SQL Server instance. (See script in Cigar Lounge and join with sys.dm_exec_sql_text and/or other DMV according to description for plan_handle column in sys.dm_exec_cached_plans documentation)

Cigar Lounge

The following query will list all cached plans stored in the QPC, and can be linked to other relevant DMVs to retrieve additional information:

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject,
    omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
JOIN sys.dm_os_memory_objects AS omo
    ON ecp.memory_object_address = omo.memory_object_address
    OR ecp.memory_object_address = omo.parent_address
WHERE cacheobjtype = 'Compiled Plan';
GO

Reference: sys.dm_exec_cached_plans (Transact-SQL) (Microsoft Docs)

  Returns a row for each query plan that is cached by SQL Server for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

Reference Material

Why would I NOT use the SQL Server option “optimize for ad hoc workloads”? (DBA Stackexchange)
sys.dm_exec_query_plan (Transact-SQL) (Microsoft Docs)
sys.dm_exec_cached_plans (Transact-SQL) (Microsoft Docs)
Troubleshooting Plan Cache Issues (Microsoft MSDN)
sp_BlitzFirst® Result: High Compilations per Second (Brent Ozar)
Forcing Query Plans (Microsoft Technet)


Retire a SQL instance without impacting the primary server? I have a SQL Server 2008 instance (TST01) that has some databases on it that are being migrated, still a few weeks of work to finish up, before the server is retired, this is a Virtual Server. There is also a separate instance on the server (TST01/ATST01) which is no longer required.

In theory I can run an uninstall and only uninstall the instance (TST01/ATST01). But I believe this will require a restart. As with any uninstall or update there is also some risk, and this is not worth any significant risk to the primary server.

I have off lined the user databases, disabled jobs, and maintenance plans.

This particular instance also has SSRS (built but never used) on it.

Is there anything else I can or should do?

The goal is to release as many resource to the VM pool, as practical, pending the full server retirement.

The goal is to release as many resource to the VM pool, as practical, pending the full server retirement.

You can just turn off sql server service of the instance that you are retiring and change the startup mode to disable.

Also, make sure to allocate the memory to other instance that is running form the decommissioned instance.

In a maintenance window, you can go ahead and uninstall SQL Server instance that you offlined.


How to not allow to insert a new record if count of current records exceeds a specified limit I have two tables as the following
team
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
`member_limit` INT NOT NULL

team_member
`id` BIGINT PRIMARY KEY AUTO_INCREMENT,
`team_id` BIGINT NOT NULL,
`member_id` BIGINT NOT NULL,

CONSTRAINT `fk_team_member_team_id` FOREIGN KEY (`team_id`) REFERENCES `team` (`id`),
CONSTRAINT `fk_team_member_member_id` FOREIGN KEY (`member_id`) REFERENCES `user` (`id`)

My question is on MySQL
How to not allow to insert a new member into team_member when the inserted member's team_id reference to team that count of its current members on team_member exceeds its member_limit

CREATE TRIGGER tr_bi_check_limit
BEFORE INSERT
ON team_member
FOR EACH ROW
BEGIN
    IF ( SELECT COUNT(*)
         FROM team_member
         WHERE team_id = NEW.team_id ) > ( SELECT member_limit - 1
                                           FROM team
                                           WHERE id = NEW.team_id ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Team limit reached!';
    END IF;
END

fiddle with some explanations.


Aruba-MySQL: can't create/change table to engine=INNODB We have our database stored in aruba (mysql.aruba.it) where there is a table called "task". Because of many changes in the requirements we decided to drop the table and create it again from 0 with different fields and constraints. The problem is that MySQL/Aruba won't let us create a called "task" table anymore. Or better, we can create another task table only with engine MyISAM but we need INNODB because we will use contraints and foreign keys in the table. So I have tried to create a MyISAM table and then convert it into INNODB but I get an error like this:

ALTER TABLE  `task` ENGINE = INNODB
#1025 - Error on rename of './Sql689345_4/#sql-6962_1891f' to './Sql689345_4/task' (errno: -1)

I don't know why there is this problem with this table: for other tables we have we can drop them and re-create them as many times as we want.
Is there a way to fix it?

Take a look at the InnoDB Internals (Picture from Percona CTO Vadim Tkachenko)

Please note the data dictionary inside ibdata1 (the system tablespace)

It sounds like you have a broken data dictionary entry on the task table when it existed

The only way around would be move all the tables into another database.

For starters, suppose you have database mydb1. Create another database mydb2

CREATE DATABASE mydb2;

For example, to move mydb1.mytable into mydb2, do this

ALTER TABLE mydb1.mytable RENAME mydb2.mytable;

Move all your tables sideways into mydb2.

You would then have to drop your old database

DROP DATABASE mydb1;

You should be able to convert the task table in mydb2 into InnoDB.

USE mydb2
ALTER TABLE `task` ENGINE = INNODB;

Give it a Try !!!


MariaDB: Querying a timestamp partition I'm having difficulties getting my queries to make use of the partitions when I'm selecting. They need to be able to do > sometime > sometime and between sometime and anothertime. However, if I do anything other than a straight equality check, MariaDB uses all the partitions.

This is how I have my table setup:

CREATE TABLE IF NOT EXISTS events
(
  time TIMESTAMP(3) DEFAULT CURRENT_TIMESTAMP,
  id BIGINT NOT NULL AUTO_INCREMENT,
  CONSTRAINT id PRIMARY KEY (id, time)
)
-- Need to take the FLOOR. See  https://mariadb.com/kb/en/mariadb/unix_timestamp/
PARTITION BY RANGE (FLOOR(UNIX_TIMESTAMP(time))) (
  PARTITION p20150801 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-01 00:00:00'))),
  PARTITION p20150807 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-07 00:00:00'))),
  PARTITION p20150814 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-14 00:00:00'))),
  PARTITION p20150821 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-21 00:00:00'))),
  PARTITION p20150828 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-28 00:00:00'))),
  PARTITION p20150830 VALUES LESS THAN (FLOOR(UNIX_TIMESTAMP('2015-08-30 00:00:00')))
);

And this is how my query is using the partions.

MariaDB [events]> explain partitions select * from events where time > TIMESTAMP("2015-08-27T01:02:03");
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | partitions                                                  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | events | p20150801,p20150807,p20150814,p20150821,p20150828,p20150830 | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using where |
+------+-------------+--------+-------------------------------------------------------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

MariaDB [events]> explain partitions select * from events where time = TIMESTAMP("2015-08-27T01:02:03");
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | events | p20150828  | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+------+-------------+--------+------------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

Any suggestions would be greatly appreciated. This has me tearing out my hair.

Your expression is too complex.  There are many severe limitations of what can go into BY RANGE.  Use simply UNIX_TIMESTAMP(time).  Even if it would work, FLOOR does not add anything to the expression.

BTW, the trailing hh:mm:ss is optional in this situation.  These are identical:

UNIX_TIMESTAMP('2015-08-01 00:00:00')
UNIX_TIMESTAMP('2015-08-01')

Now, on to my favorite question:  Why are you using PARTITIONing?  PARTITIONingdoes not inherently provide any performance.  There are only a few use cases I know of where you can trick PARTITION into providing more performance than a carefully chosen index.


Why "SET LOCAL statement_timeout" does not work as expected with PostgreSQL functions? My understanding is that PostgreSQL functions are executed similar to a transaction. However, when I tried to "SET LOCAL statement_timeout" within a function, it did not work. Here's how it works within a transaction:

BEGIN;
  SET LOCAL statement_timeout = 100;
  SELECT pg_sleep(10);
COMMIT;

where the results are (as expected):

BEGIN
SET
ERROR:  canceling statement due to statement timeout
ROLLBACK

However, if I put the same commands within a function body:

CREATE OR REPLACE FUNCTION test() RETURNS void AS '
    SET LOCAL statement_timeout = 100;
    SELECT pg_sleep(10);
' LANGUAGE sql;

SELECT test();

the timeout does not occur, and the function test() takes 10 seconds to execute.

Please advise on why the two cases differ, and how I can correct it to set statement timeouts within a function.

The way statement_timeout works, the time starts counting when the server receives a new command from the client.

Queries launches inside server-side functions are not commands from a client, they don't reset that timer or push a new one onto a stack of timers.

This is why SET LOCAL statement_timeout = 100; has no effect.

And if a function does SET statement_timeout = 100; it will have an effect only starting at the next command from the client.

I don't see any way to control the execution time of individual queries inside a function.


Why is SQL Server 2008R2 using more memory than the "max server memory" setting? Possible Duplicate:
  Microsoft SQL Server 2008 Memory

I've explicitly set the Maximum Memory value in SQL server 2008R2 to 13,000 MB, however when I check task manager I see it is using 14,071,448 KB (13,741 MB)

I'm concerned because the server crashed recently due to a low memory condition where SQL server had allocated almost all of the server memory.

I've checked and the number I have entered is correct (i.e not entered in KB by mistake).

For SQL Server 2008 R2, "max memory" is for buffer pool only (basically data cache)
Other memory is used for other things.

This is changed in SQL Server 2012


Inner join returns duplicate results I need to select data from 2 tables based on 1 filed UID and display some from both tables.

Data from first table "a.symbol, a.order_type, a.price_open, a.time_open, a.size", and data from second table "b.bid, b.point".

When i use INNER JOIN to get data from both tables i get many results, but there is only 5 results that i have data for, when i use it without joins as you can see on 1st image.

Ok here is an query i have problems with:

SELECT a.symbol, a.order_type, a.price_open, a.time_open, a.size, b.bid, b.point
FROM trade_log a
INNER JOIN currency_rates b ON a.uid = b.uid
WHERE a.uid = '23' AND a.status = 'Open' AND a.order_type IN ('0', '1')
ORDER BY a.time_open ASC

So this is a data i get when i use query without inner join, from trade_log table

This i a data what i'm getting now with inner join, but i need just to add "bid" and "point" to table like on this upper image, but i don't know why he multiply results.

And this is a data from "currency_rates" where UID is 23

As far as you don't give us more information I'll throw a bet.

Using only uid to join both tables you get 20 records (5 x 4). I suppose you should add symbol.

SELECT     a.symbol,
           a.order_type,
           a.price_open,
           a.time_open,
           a.size,
           b.bid,
           b.point
FROM       trade_log a
INNER JOIN currency_rates b
ON         a.uid = b.uid
AND        left(a.symbol, 6) = b.symbol
WHERE      a.uid = '23'
AND        a.status = 'Open'
AND        a.order_type IN ('0', '1')
ORDER BY   a.time_open ASC;


SQL Recover from .bak file with NOINIT This will, hopefully, be a nice and quick question for someone out there. It continues from my previous question.
I have a .BAK file that I'm trying to restore. The .BAK was created with the NOINIT argument, to try and save previous version, and so has newer data appended (as I understand it). I was wondering if this meant that - upon restore - restore would try to use an old version of the backup?
I.e. I have backed up the DB, added new tables to it, performed another full back-up and dropped the added tables. When I try to restore, I am not getting the 'new' tables back, is this because the restore is finding a version of the database in the .BAK which was created before the 'new tables'? If so, how can I specify to use the latest data?
EDIT: Apologies for not stating sooner, this is using an SQL Server database

That's because by not specifying the FILE parameter of RESTORE DATABASE, it is defaulting to 1 which will be the 1st backup set on that media.  See the section on this page of Specifying a Backup Set.

Instead of:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak';
go

You will want to do:

restore database YourDb
from disk = 'C:\yourpath\backupfile.bak'
with file = <n>;
go

Where N is the backup set number in the media.  N can be obtained from running RESTORE HEADERONLY on the media.  The column you will be concerned with is Position to use with the FILE option.


pull records that are inactive I need help with this query. I have records in customer table

 Main table - M

+---------+---------+----------+
| company | Account |  Active  |
+---------+---------+----------+
| A       | A123    | Active   |
| A       | A123    | InActive |
| B       | B123    | Inactive |
+---------+---------+----------+

Reference table - R

+---------+---------+
| company | Account |
+---------+---------+
| A       | A123    |
| B       | B123    |
| C       | C123    |
+---------+---------+

Pull all records from M which exists in R but are inactive. The problem here is a company may have both an inactive and an active account in M, I want to pull only those that only have inactive account and no active account. So from the two table, I should get

+---------+---------+----------+
| company | Account |  Active  |
+---------+---------+----------+
| B       | B123    | Inactive |
+---------+---------+----------+

Because A while it does exist in R, it also has an Active Account status so I must exclude it.

a company may have both an inactive and an active account in M

SO I assume it can be only one active and one inactive row. Here is a solution with JOINs only. If there can be multiple (active) rows, you should use EXISTS instead, as @ypercube already mentioned in his comment.

SELECT R.*, M1.active
FROM   R
JOIN   M AS M1 ON M1.company = R.company
              AND M1.account = R.account
              AND M1.active  = 'inactive'
LEFT   JOIN M AS M2 ON M2.company = R.company
                   AND M2.account = R.account -- or no active account at all?
                   AND M2.active  = 'active'
WHERE  M2.company IS NULL;

As @ypercube commented: it did not become entirely clear whether you don't want any active account to invalidate the company or just a matching active account.

I went with matching account in my query. Remove AND M2.account = R.account from the JOIN condition if you want any.


SQL Server spatial filter with additional conditions performance issue I have a table Event that has 30 million rows and a table Location that has 35 million rows in SQL Server. An event can have multiple locations.

I have a spatial index, geometry auto grid (cells per object: 4000), on the Location table. The bounding box of all the locations covers and area of about 500 000 km2.

The performance is fine when only using STContains in the WHERE clause. If I try to filter on a additional column(s) the performance takes a big hit.

The following query takes 171ms:

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[Id] = [L].[EventId]
WHERE
    --[E].[Price] >= @Price
    --AND
    @BoundingBox.STContains([L].[Position]) = 1

If I add the filter for Price, the same query takes 10 seconds to run. If I remove the spatial index the query takes 500ms. Depending on the size of the bounding box and the value of price the query takes either like second or less or anywhere between three to thirty seconds. Only filtering on the spatial column gives good performance for all bounding box sizes.

Every article I've found about spatial queries in SQL Server never says anything about additional column(s) filters. They only describes the spatial filter and how to tune performance for the spatial index/query and it this case the spatial filter works fine with different sized bounding boxes. The problem is the additional filter columns I need.

Is it possible to get performance from a query with a spatial filter and with additional column(s) filters?

EDIT

95% of the price values are below 5000. So filtering on a high value will only return around 100 rows. I've tried with a bounding box that is 24000 sq km and contains around 4 million events. So the issue might be with the distribution of the price values. I have a second column Date which has a better distribution of values and filtering on that column is fast as long as we don't get to close the the max date.

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[Id] = [L].[EventId]
WHERE
    [E].[Date] >= @Date
    AND
    @BoundingBox.STContains([L].[Position]) = 1

Also the geometry have srid 4326 if that makes any difference.

The answer is yes it is possible to get performance with filters over multiple columns including spatial columns.

I've tried to replicate your tables and closely as I can to your brief description using RAND to create prices, location points and link them to events.  But I can't duplicate you performance problem.  Have I got the basic table details right?

--  Event table
SELECT TOP 30000000
    N EventID,
    RAND(CAST(NEWID() AS VARBINARY)) * 10000 + 5000 Price
    into Event
FROM Tally;

ALTER TABLE Event ALTER COLUMN EventID INT NOT NULL;
GO
ALTER TABLE Event ADD CONSTRAINT Event_PK PRIMARY KEY (EventID);
GO
CREATE INDEX Event_Price_IDX ON Event(Price);
GO

-- Location Table
SELECT TOP 35000000
    N LocationID,
    CEILING(RAND(CAST(NEWID() AS VARBINARY)) * 30000000) EventID,
    Geometry::Point(RAND(CAST(NEWID() AS VARBINARY)) * 710000 + 40000, RAND(CAST(NEWID() AS VARBINARY)) * 710000 + 40000, 0) Location
    into Location
FROM Tally;

Running the 3 versions of your query with hopefully sensible parameters

-- Test Query
DECLARE @price FLOAT = 13000.00 -- ~20% of records
-- 10 sq km query window
DECLARE @BoundingBox Geometry = Geometry::STGeomFromText('POLYGON(( 300000 300000, 310000 300000, 310000 310000, 300000 310000, 300000 300000))',0)

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE
    --[E].[Price] >= @Price
    --AND
    @BoundingBox.STContains([L].[Location]) = 1

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE
    [E].[Price] >= @Price
    --AND
    --@BoundingBox.STContains([L].[Location]) = 1

SELECT TOP 2000 [E].*
FROM [dbo].[Event] AS [E]
INNER JOIN [dbo].[Location] AS [L] ON [E].[EventId] = [L].[EventId]
WHERE
    [E].[Price] >= @Price
    AND
    @BoundingBox.STContains([L].[Location]) = 1

I get 93ms, 4ms and 93ms respectively, removing the TOP 2000 from the queries causes the price filtered query to blow out to a minute or so.

Are you able to give more details (execution plans, DDL, etc) to help us replicate your issue.  My initial thought was that the TOP 2000 was hiding the underlying problem.  I think that may still be the case.


Difference in results between case syntax I have been playing around with CASE syntax and noticed the following difference in behaviour which I don't full understand.

Given the below query

DECLARE @Value1 INT

SELECT
    'Col1' = CASE WHEN @Value1 IS NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col2' = CASE @Value1 WHEN NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Val' = @Value1

The result set I am getting is this:

  Col1 | Col2 | Val

  Yay | Oh | NULL

I would have thought they would return the same result. Would anyone be happy to explain what's going here?

For the same reason Col3 I've added below returns "Oh". In your Col2 you are testing if NULL equals @Value1. NULL is undefined, it doesn't equal anything. As Codd would say

  Rule 3: Systematic treatment of null values:

  The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.

DECLARE @Value1 INT

SELECT
    'Col1' = CASE WHEN @Value1 IS NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col2' = CASE @Value1 WHEN NULL THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Col3' = CASE WHEN NULL = NULL  THEN 'Yay'
    ELSE 'Oh'
    END
    ,'Val' = @Value1


How to solve the error Msg 547, Level 16, State 0, Line 1 Please suggest me how to solve the error How to solve the error

  Msg 547, Level 16, State 0, Line 1
  The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "MyForeignKeyName". The conflict occurred in database "MyDatabase", table "dbo.MyTable", column 'IMyColumn'.

If you are trying to make foreign key and your child table already have some data that does not match with your parent table, you will get this error message.

For Example:

CREATE TABLE MyParent(Job_id int PRIMARY KEY,Job_Name Varchar(50));

INSERT INTO MyParent Values(1,'CEO');
INSERT INTO MyParent Values(2,'Business Manager');
INSERT INTO MyParent Values(3,'DBA');

CREATE TABLE MyChild(Emp_id int,Emp_Name Varchar(50),Job_Id int)

INSERT INTO MyChild Values(1,'Lavanya',1);
INSERT INTO MyChild Values(1,'JP Chauhan',3);
INSERT INTO MyChild Values(1,'Rakesh',5);

See the last record of MyChild table that has value in Job_Id is 5, which is not in MyParent table.

If you try to make foreign key relation like:

ALTER TABLE MyChild
ADD CONSTRAINT fk_My_foreignKey FOREIGN KEY(Job_Id)REFERENCES MyParent(Job_Id);

The error message you will get:

Msg 547, Level 16, State 0, Line 1
The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "fk_My_foreignKey". The conflict occurred in database "mydatabase", table "dbo.MyParent", column 'Job_id'.


Is any possible way to get warning message through by oracle I am working in developing oracle data base tool, I want display warning message when executing function. For example:

CREATE OR REPLACE FUNCTION FN_OEDS_CONVERTDATE2(
V_DATE DATE) RETURN VARCHAR2
    IS
BEGIN
DECLARE
    V_RETVALUE VARCHAR2(15) :='';
    BEGIN

    SELECT
        CASE V_DATE
            WHEN NULL THEN ''
            ELSE TO_CHAR(V_DATE, 'DD-MM-YYYY')
        END INTO V_RETVALUE FROM DUAL

     RETURN V_RETVALUE;
    END;

END;
/

Above function will execute successfully, but it wont through any error message , but it status would be invalid, how to get the warning message through by out in java.

Compiler messages are not returned to the client through the getWarnings() on the statement or the connection.

Instead once you have identified the warning through Statement.getWarnings() you need to parse retrieve the compiler errors from the view ALL_ERRORS

Something like:

SELECT line,
       position,
       text,
       name,
       type
FROM all_errors
WHERE owner = user
AND type = 'FUNCTION'
AND name = 'FN_OEDS_CONVERTDATE2'
ORDER BY line, position


Install MySQL for Windows from .zip and reset root password I wanted to have on my PC (with Windows 8.1 x64) the MySQL server only, without Workbench or something. So I downloaded .zip archive from dev.mysql.com/downloads. It's a download for Win64 on x86_64 version 5.7.9 (MySQL Community Server (GPL)).

I installed it as a Windows service, but there were no mysql database, only information_schema. So I executed this:

mysql_upgrade.exe --upgrade-system-tables

And mysql databases were created. But along with it something happened with root user, because I couldn't access mysql anymore.

So I decided to reset this suddenly appeared password (because I didn't have it before that). I founded the following solution in the official manual, I started the server like this:

mysqld.exe --skip-grant-tables --console

Then I opened mysql without password:

mysql.exe –u root

And then tried to reset the root password:

SET PASSWORD FOR 'root'@'localhost' = PASSWORD(‘passhere');

But I got this error:

  ERROR 1131 (42000): You are using MySQL as an anonymous user and anonymous users are not allowed to change passwords".

What is this? How am I anonymous? All commands were executed in cmd.exe under the Administrator.

What should I do to reset the root password in this situation?

Update 1: I tried to check the current user:

SELECT USER(), CURRENT_USER();

That gives:

+--------+----------------+
| USER() | CURRENT_USER() |
+--------+----------------+
| root@  | @              |
+--------+----------------+

That's weird, because I started it like mysql.exe -u root.

Then I checked users table:

SELECT user FROM mysql.user;

That gives:

+-----------+
| user      |
+-----------+
| mysql.sys |
+-----------+

That's even more weird. Also there is no password field:

SELECT user, password FROM mysql.user;

  ERROR 1054 (42S22): Unknown column 'password' in 'field list'

So I cannot change its password.

And I cannot create a new user:

CREATE USER 'root'@'localhost' IDENTIFIED BY 'passhere';

  ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Now I'm lost.

Update 2: I guess, I did everything wrong from the beginning. Apparently, I missed a mysqld.exe --initialize command somehow.

So, this is how I installed MySQL from .zip-archive, thanks to @RolandoMySQLDBA:

Unpack archive, prepare my.ini.
Execute mysqld.exe --initialize, get temporary password from the .err file.
Install MySQL as a service and run it

mysqld --install MySQL --defaults-file="d:/path/to/mysql/my.ini"

Connect to it mysql -u root -p with temporary password.
Change the temporary password:

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORD';

Since you started mysqld with --skip-grant-tables, you cannot execute any standard GRANT, REVOKE, or SET PASSWORD commands. Notwithstanding, you can change the password of root@localhost as follows:
UPDATE mysql.user SET password=PASSWORD(‘passhere') WHERE user='root' and host='localhost';

Then, restart mysqld and you are back in business.
As to you being anonymous, if you login right now and run
SELECT USER() HowYouAttemptedToLogin,CURRENT_USER() HowYouWereAllowedToLogin;

You will note that the HowYouWereAllowedToLogin will have an blank username and some host.
GIVE IT A TRY !!!
UPDATE 2015-11-17 16:24 EST
The column known as password in mysql.user no longer exists in MySQL 5.7.
It was renamed authentication_string.
Proper Approach
What you should have done is run
mysqld --initialize

That would create the data folder for you.
The root@localhost was assigned a temporary password, which is visible in the error file starting with the following datetime and string
2015-11-17T20:56:02.175980Z 1 [Note] A temporary password is generated for root@localhost:

You could log in with it and then run ALTER USER
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';

See the MySQL 5.7 Documentation on SET PASSWORD


storing as smallint, querying as int A multi-billion-row fact table in our database has 10 measures stored as int columns. The value ranges for some of these columns won't ever be above the +/-32K range of a smallint. To save I/O, we're investigating whether it's practical to store these columns as smallint instead of int.

But we're concerned about what problems might crop up from doing this, including:

SUM(SomeSmallInt) will frequently overflow, so many queries would have to be rewritten to cast these smallints to ints before aggregating, e.g. SUM(CAST(SomeSmallInt as int)).
This table is read in many places, so changing the data type of these columns might involve a lot of review, change, and testing.

So we're wondering if there's a lower-cost solution that would store as smallint but expose the colunms as ints to readers. Like this:

create smallint "storage" columns that the table's (only) writer will use, but otherwise no other client will care about.
to support existing readers, create int-typed computed columns that are the same names as the original int columns.

What are the pros and cons of this approach? What can go wrong?  Is there a better way to reduce storage & I/O without causing problems with overflow and requiring existing readers to be rewritten?

To prove the benefit of data compression for this question, I quickly built the following test:

USE tempdb;

CREATE TABLE dbo.IntTest
(
    IntTest_ID INT NOT NULL
        CONSTRAINT PK_IntTest
        PRIMARY KEY CLUSTERED
)
WITH (DATA_COMPRESSION = PAGE);

CREATE TABLE dbo.SmallIntTest
(
    SmallIntTest_ID SMALLINT NOT NULL
        CONSTRAINT PK_SmallIntTest
        PRIMARY KEY CLUSTERED
)
WITH (DATA_COMPRESSION = NONE);
GO

INSERT INTO dbo.IntTest (IntTest_ID)
SELECT TOP(65535)
    CONVERT(SMALLINT, ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id, o2.object_id) - 32768)
FROM sys.objects o
    , sys.objects o1
    , sys.objects o2
ORDER BY o.object_id, o1.object_id, o2.object_id;

INSERT INTO dbo.SmallIntTest (SmallIntTest_ID)
SELECT TOP(65535)
    CONVERT(SMALLINT, ROW_NUMBER() OVER (ORDER BY o.object_id, o1.object_id, o2.object_id) - 32768)
FROM sys.objects o
    , sys.objects o1
    , sys.objects o2
ORDER BY o.object_id, o1.object_id, o2.object_id;

SELECT o.name
    , i.name
    , p.partition_number
    , p.rows
    , au.used_pages
    , au.total_pages
    , AvgRowsPerPage = p.rows / CONVERT(DECIMAL(10,2), au.used_pages)
FROM sys.allocation_units au WITH (NOLOCK)
    INNER JOIN sys.partitions p WITH (NOLOCK) ON ((au.type = 1 OR au.type = 3) AND au.container_id = p.hobt_id) OR (au.type = 2 AND au.container_id = p.partition_id)
    INNER JOIN sys.indexes i ON p.object_id = i.object_id AND p.index_id = i.index_id
    INNER JOIN sys.objects o WITH (NOLOCK) ON p.object_id = o.object_id
    INNER JOIN sys.schemas s WITH (NOLOCK) ON o.schema_id = s.schema_id
WHERE s.name = 'dbo'
    AND o.name IN (
        'IntTest'
        , 'SmallIntTest'
    );

The output from the last query, showing actual pages used by each table, is:

+-----------------+-----------+-------+------------+-------------+-------------+
|      NAME       | PARTITION | ROWS  | USED_PAGES | TOTAL_PAGES | AVG ROWS    |
|                 | NUMBER    |       |            |             | PER PAGE    |
+-----------------+-----------+-------+------------+-------------+-------------+
| PK_IntTest      | 1         | 65535 | 92         | 97          | 712.3369573 |
| PK_SmallIntTest | 1         | 65535 | 92         | 97          | 712.3369573 |
+-----------------+-----------+-------+------------+-------------+-------------+

As you can see from the above results, compressed INTs containing SMALLINTs actually consume precisely the same amount of space.


Deleting from multiple tables with foreign constraints in mysql I have three tables as seen below.

what my requirement is, when I delete a row from a donate_club table, I need to delete all the entries related to this row from 'donationRequest' and 'committments'. Here 'commitments' table has a foreign key relation ship with 'donatinRequest' table. So when I delete an entry from 'donationRequest' table the corresponding entries are being deleted from 'committments' table also. That works fine. Also 'donationRequest' table has a foreign key relationship to 'donate_club' table, so when I delete an entry from 'donate_club' it also deletes related entries from 'donationRequest' table. It is also working fine. Now my requirement is when I delete an entry from 'donate_club', I need to delete all the related entries from both 'doantionRequest' and 'commitments' table.

Now the situation is, when I delete a row from 'donate_club', it automatically deletes related entries from 'donationRequest' table but it doesnt delete anything from 'committments' table eventhough 'committments' table has an entry related to the deleted row from 'donationRequest' table

EDIT:
donate_club table create query

CREATE TABLE `donate_club` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `firstName` varchar(100) DEFAULT NULL,
 `secondName` varchar(100) DEFAULT NULL,
 `email` varchar(100) DEFAULT NULL,
 `password` varchar(20) DEFAULT NULL,
 `mobile` varchar(20) DEFAULT NULL,
 `bloodGroup` varchar(10) DEFAULT NULL,
 `age` varchar(10) DEFAULT NULL,
 `gender` varchar(20) DEFAULT NULL,
 `country` varchar(50) DEFAULT NULL,
 `location` varchar(50) DEFAULT NULL,
 `latitude` varchar(50) DEFAULT NULL,
 `longitude` varchar(50) DEFAULT NULL,
 `profilePicFIleName` varchar(100) DEFAULT NULL,
 `profilePicURL` text,
 PRIMARY KEY (`id`),
 UNIQUE KEY `mobile` (`mobile`),
 UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=68 DEFAULT CHARSET=utf8

donationRequest create query

CREATE TABLE `donationrequest` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `latitude` varchar(50) DEFAULT NULL,
 `longitude` varchar(50) DEFAULT NULL,
 `mobile` varchar(15) DEFAULT NULL,
 `contactPerson` varchar(50) DEFAULT NULL,
 `bloodGroup` varchar(10) DEFAULT NULL,
 `bloodBank` varchar(200) DEFAULT NULL,
 `location` varchar(100) DEFAULT NULL,
 `postedDate` varchar(30) DEFAULT NULL,
 `userID` int(11) DEFAULT NULL,
 `fcmToken` varchar(1000) DEFAULT NULL,
 `requiredDate` varchar(20) DEFAULT NULL,
 `need` varchar(20) DEFAULT NULL,
 `unit` varchar(5) DEFAULT NULL,
 `patient` varchar(50) DEFAULT NULL,
 `status` tinyint(4) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `userID` (`userID`),
 CONSTRAINT `fk_to_donate_club_id` FOREIGN KEY (`userID`) REFERENCES `donate_club` (`id`)
     ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=332 DEFAULT CHARSET=utf8

committments create table query

CREATE TABLE `commitments` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `userID` varchar(50) DEFAULT NULL,
 `fcmTockenPatient` varchar(10000) DEFAULT NULL,
 `fcmTockenDonor` varchar(10000) DEFAULT NULL,
 `committedDate` varchar(15) DEFAULT NULL,
 `patientName` varchar(100) DEFAULT NULL,
 `byStanderName` varchar(100) DEFAULT NULL,
 `byStanderMobile` varchar(15) DEFAULT NULL,
 `bloodGroup` varchar(5) DEFAULT NULL,
 `location` varchar(100) DEFAULT NULL,
 `hospital` varchar(100) DEFAULT NULL,
 `requestID` int(11) DEFAULT NULL,
 `isFinished` tinyint(2) DEFAULT NULL,
 `receiverID` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `requestID` (`requestID`),
 CONSTRAINT `fk_to_donation_request` FOREIGN KEY (`requestID`) REFERENCES `donationrequest` (`id`)
     ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=286 DEFAULT CHARSET=utf8

You should check out your data, according to your table definition it must work.

drop table if exists xcommitments;
drop table if exists xdonations;
drop table if exists xusers;

create table xusers
(
    user_id int not null,
    name varchar(100),
    primary key (user_id)
);

create table xdonations
(
    donation_id int not null,
    user_id int not null,
    primary key (donation_id),
    constraint fk_users foreign key (user_id)
        references xusers (user_id) on delete cascade
);

create table xcommitments
(
    commitment_id int not null,
    donation_id int not null,
    primary key (commitment_id),
    constraint fk_donations foreign key (donation_id)
        references xdonations (donation_id) on delete cascade
);

insert into xusers values (1, 'John'), (2, 'Anna');
insert into xdonations values (1, 1), (2, 1), (3, 2);
insert into xcommitments values (1, 2), (2, 3);

select      *
from        xusers u
left join   xdonations d
on          u.user_id = d.user_id
left join   xcommitments c
on          d.donation_id = c.donation_id;

| user_id | name | donation_id | user_id | commitment_id | donation_id |
|---------|------|-------------|---------|---------------|-------------|
| 1       | John | 1           | 1       | NULL          | NULL        |
| 1       | John | 2           | 1       | 1             | 2           |
| 2       | Anna | 3           | 2       | 2             | 3           |

delete from xusers
where  user_id = 1;

select      *
from        xusers u
left join   xdonations d
on          u.user_id = d.user_id
left join   xcommitments c
on          d.donation_id = c.donation_id;

| user_id | name | donation_id | user_id | commitment_id | donation_id |
|---------|------|-------------|---------|---------------|-------------|
| 2       | Anna | 3           | 2       | 2             | 3           |

drop table if exists xcommitments;
drop table if exists xdonations;
drop table if exists xusers;

rextester here


MySQL: CAST + SUBSTR vs. FLOOR + MOD I have a field  in a mysql database where dates are stored as BIGINT like YYYYMMDDHHMMSS (PHP: date('YmdHis')). I'm building a query, where I check the amount of rows per hour and have tried two different things:

SELECT x hour, ... FROM table WHERE ... GROUP BY x ...

x = SUBSTR(CAST(time AS CHAR(14)), 9, 2)
x = MOD(FLOOR(time / 10000), 100)

I built a quick benchmark (did 1000 querys with each version) and both of them seem to be equally quick so I'm really interested in what database designers would advise me to do?

Database designers would definitely advise you to use proper data format and built-in date calculation functions


Why Table Lock Escalation is happening in my scenario I have a table Table1 that is updated in small chunks using below query:

update top (1000) Table1
    set VarcharColumn1 = 'SomeValue'
from Table1
where ID in (select ID from Table2)
      and VarcharColumn1 is NULL

Additional details:

Table2 has 90000 rows, and total number of rows that need to be updated in Table1, is also 90000 (1 to 1 relationship)

Also when Table1 rows are updated, there is a trigger on Table1 that inserts rows as they were before update, to table Table1History
So this means when I update 1000 rows in Table1, 1000 rows inserted into Table1History

Question:

When I update top 100 rows, no Table Lock Escalation happens

I monitor this using Extended Events "lock_escalation" event, and also in Performance Monitor - SQLServer:Access Methods - Table Lock Escalations / sec

When I update top 1000 or 500 rows, Table Lock Escalation IS happening on Table1

So I wonder, what is the mechanism or formula that is used by SQL Server
to escalate locking to table level ?

In google it says 5000 rows is the threshold, but obviously in my case 1000 or 500 rows is enough to cause Table Lock Escalation on Table1

Lock escalation is not exactly based on 5k rows, but rather a number of locks pr. level in the locking hierarchy. Once you hit the threshold, locks escalate to the next object level, until you eventually reach table-level which usually has a negative impact on the application. To work around this the easiest, consider how many rows you are affecting in  the DELETE TOP() and how many pages that correlates to. The amount of rows in a page is defined by with width of the row. If you have many columns (requiring lots of byte-storage), your row-width is bigger, then a table with fewer columns. A page can contain roughly 8k-bytes of data.

  "Books Online states that the threshold for a lock escalation is reached when the statement has acquired in excess of 5,000 row or page level locks on a single instance of an object. Also some blogs (which I came across in the Internet) just take 5000 value as a threshold point and not consider other parameters. When rows exceed 5000 an attempt is made to convert a higher-level lock on the object (either an IS or an IX lock) to an S lock or an X lock on the table. If this fails, perhaps because the table is being used by someone else, then SQL Server will back off and try again if it acquires another 1,250 locks on the same instance of the object. Remember that all locks attained must originate from the same object within the statement to qualify for exceeding the lock threshold, so 4,000 locks from tableA that is self-joined (i.e., another set of 4,000 locks) would not qualify." [taken from book "Professional SQL Server 2008 Internals and Troubleshooting" by Christian Bolton et al. published by Wrox ]

Good info in the page-object in SQL Server:
https://www.sqlskills.com/blogs/paul/inside-the-storage-engine-anatomy-of-a-page/

There is an option to disable lock escalation as part of your table-definition. Buts it not advisable in most cases. I'd advise to not go this route until you've tried designing with lock escalation in mind. See more here: What is disadvantage of disabling lock escalation in SQL Server 2016


PostgreSQL 9.3: Dump a database excluding the data of some tables Consider a production database with a few dozens of normal-size tables, and a handful of huge tables.

I am looking for a convenient way to pg_dump the database content, excluding the few huge tables, for my own local laptop. I can easily dump a small sample of the huge tables using \COPY, if I occasionally need to address these tables.

If I use the --exclude-table=table argument, both the schema and the data of the huge table is omitted, which breaks queries expecting these table on the local development environment.

Is there a way to dump a data base with a complete schema, excluding the content of some given tables?

I think you need to use --exclude-table-data=table option. From the docs:

--exclude-table-data=table
Do not dump data for any tables matching the table pattern. The pattern is interpreted according to the same rules as for -t. --exclude-table-data can be given more than once to exclude tables matching any of several patterns. This option is useful when you need the definition of a particular table even though you do not need the data in it.
To exclude data for all tables in the database, see --schema-only.


What is the best practice location for Memorize Optimization (OLTP) files? Best practices for log and mdf files is to split across drives (e.g. G:\SQL Data & I:\SQL Logs).
What are the best practices for memory optimization files? Should they reside on their own drive? Can they reside on a Azure Temp drive (better IO performance but files will be lost on redeploy, tempdb can be hosted here)?
I am still unclear on the purpose of the Memory Optimized file set and what is actually stored there and the rate of IO.

I think we are talking about In Memory OLTP ?
The files are necessary for disaster recovery
Once added they can't be removed without dropping the database !!!
You need x4 the amount of memory as disk files - if the disk files fill up your database will go suspect.
A fast disk subsystem is recommended.
I would advise careful testing before you deploy to production.
https://docs.microsoft.com/en-us/sql/relational-databases/in-memory-oltp/the-memory-optimized-filegroup?view=sql-server-ver15


SQL function with SELECT vs. PLPGSQL function with RETURN QUERY SELECT? Is there a difference between a plain SQL function doing a SQL query:

create function get_sports() returns setof sport as
$body$
    select * from sport;
$body$
language sql stable;

and PLPGSQL function returning that same SQL query:

create function get_sports() returns setof sport as
$body$
begin
    return query select * from sport;
end
$body$
language plpgsql stable;

with regards to performance? In which situation should which version be used?

And what if we pass arguments, does that change anything? Such as:

create function get_sports(status int) returns setof sport as
$body$
    select * from sport where status = $1;
$body$
language sql stable;

create function get_sports(status int) returns setof sport as
$body$
begin
    return query select * from sport where status = $1;
end
$body$
language plpgsql stable;

There are various subtle differences.

Simple SQL functions can be inlined when nested in an outer query - provided they meet some preconditions. Not possible for a PL/pgSQL function.

All statements in the same SQL function are planned at once at call time, while PL/pgSQL function treat nested SQL DML statements much like prepared statements - only planning each when executed the first time, and possibly saving the query plan for the duration of a session. This is a major difference that influences behavior and performance.

For example, you cannot create a (temporary) table and use it later in the same SQL function, because it would have to exist at call time for the later statements to be planned. You need PL/pgSQL for that.

Code branches in PL/pgSQL functions that are not reached and thus never planned or executed may harbor undetected bugs until finally executed. PL/pgSQL function are only tested superficially at creation time.

Also, an SQL function only returns the output of the last query. The manual:

  When an SQL function is declared as returning SETOFsometype, the function's final query is executed to completion, and each row it outputs is returned as an element of the result set.

Bold emphasis mine. (You might use UNION (ALL) to combine multiple query results.)

RETURN QUERY, on the other hand, appends to the result and can be called multiple times. The manual:

  RETURN NEXT and RETURN QUERY do not actually return from the function
  — they simply append zero or more rows to the function's result set.
  Execution then continues with the next statement in the PL/pgSQL
  function. As successive RETURN NEXT or RETURN QUERY commands are
  executed, the result set is built up. A final RETURN, which should
  have no argument, causes control to exit the function (or you can just
  let control reach the end of the function).

Related:

FUNCTION syntax error

Passing arguments works pretty much the same for either PL. (There used to be more differences in old versions, like SQL functions couldn't refer to  parameter names. But that's history.)

There are other, more or less subtle differences. See:

Difference between language sql and language plpgsql in PostgreSQL functions
Function Performance
EXPLAIN ANALYZE shows no details for queries inside a plpgsql function
SQL injection in Postgres functions vs prepared queries


Calculated Field Updating Every Row I added a calculated field to my table, and only want to update NULL rows (or "new" rows).  Something is going awry, and every row in my table is being updated with the current DateTime when 1 row is inserted.  This is the Create Table syntax, what must I do so that only the "new" rows are updated with the current DateTime

CREATE TABLE [dbo].[SE](
[dbID] [int] IDENTITY(1,1) NOT NULL,
[uqID] [varchar](31) NOT NULL,
[POD] [varchar](255) NULL,
[EC] [varchar](255) NULL,
[S1] [varchar](35) NULL,
[S2] [varchar](35) NULL,
[S3] [varchar](35) NULL,
[S4] [varchar](35) NULL,
[S5] [varchar](35) NULL,
[S6] [varchar](35) NULL,
[S7] [varchar](35) NULL,
[PSD] [varchar](50) NULL,
[DS] [datetime] NULL,
[CN] [varchar](max) NULL,
[autodate]  AS (getdate()),
PRIMARY KEY CLUSTERED
(
[dbID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

You don't want a calculated column, instead just use a datetime (non-nullable) column, with a default constraint using getdate().

The syntax for the column in the table definition SQL is as follows:

[autodate] DATETIME NOT NULL DEFAULT(GETDATE())

A Computed Column in SQL Server is calculated when it is queried (unless it is PERSISTED, then it has a value stored on disk and can be indexed, however it is still computed). It is intended to be changeable, and be the result of calculations on another column or columns.

A DEFAULT Constraint simply inserts a default value into the column for any inserts, but then it is held as a  fixed value just like any other data column.


Problem Getting Simple Replication to Work in SQL Server 2008/2012 I can't resolve a problem with replication. The publisher/distribution side seems just fine, but when I subscribe and view in the replication monitor I always get this error: "a required privilege is not held by the client"

I thought I had it figured out with an MSDN article explaining the SQL Agent user/pw needs to be set in SQL Config Mgr (instead of Windows Services) -- that was peculiar, but it did describe our situation. Then, we reset the user/pw using SQL Config Manager and I still get the same error.

I deleted my subscription, and setup a new one, but still the same error.

FWIW the publisher/distributor is SQL Server 2008 and the subscriber is 2012 (doing pull subscriptions).

This is a permissions issue and you can find more details in the verbose agent log.

Verify that your replication agent process accounts have the necessary permissions listed in the section Permissions That Are Required by Agents in Replication Agent Security Model.

Note that Microsoft recommends that you use a Windows accounts for replication agent process accounts.  Do not impersonate the SQL Server Agent account when you create the subscription, use a Windows account.

The Distribution Agent process account for a pull subscription requires:

Member of db_owner in the subscription database.

The account that is used to connect to the Distributor requires:

Be a member of the PAL.
Have read permissions on the snapshot share.