PostgreSQL 9.1 runs into inconsistent data, but pg_dump corrects the problem? We are running a PostgreSQL 9.1.20 with a database of about 50GB.

All things works fine.
After some days strange unexpected values appear in numeric columns,  like negative or bigger values in unexpected places.
The strange part: if we run a pg_dump of the same "corrupted" database, and then restore it (in the same server), the data becomes all ok. Now the same queries return different values!

Why does the database dump have different values when we are taking the dump exactly from the "corrupted" database? (The dump has corrected values from our perception!)

Can this be related to some kind of broken transaction or hard drive trouble? Note that restarting the database does not solve the problem.

What can be the cause behind this problem?

As requested in the comments some examples of data:

    #psql
    \c base
    select * from salXXXX where sal_id=2323;
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX
--------+-----------+---------------+--------------------+------------+-----------------------+------------
   2323 |     -30.43 | 42586501      |                  6 |         13 | f                     |

    \quit
    pg_dump -h 127.0.0.1 -p 5432 --user=postgres >backup.sql
    psql
    drop database base;
    create database base;
    \c base
    \i backup.sql
    (no errors found here)
    select * from salXXXX where sal_id=2323;
    sal_id | sal_XXXX | XXXXXX | XXXXXX | XXXXX | XXXXX | XXXXX
--------+-----------+---------------+--------------------+------------+-----------------------+------------
   2323 |    245.43 | 42586501      |                  6 |         13 | f                     |

It is hard to emulate it, rarely occurs, but when it occurs we just do a dump, it is a ridiculous solution, but it works.

This is just an example, many more wrong values start to appear, even if we reprocess the data. But if the database is dumped and restored, values are in the expected range again.

Might be index corruption - assuming you have an index on sal_id.

If so, REINDEX TABLE salXXXX; (or just REINDEX INDEX index_name would do the same trick as your dump/restore cycle, which also recreates all indexes from scratch.

The manual advises:

  Note that while this method does not require locking out other
  clients, it might still be wise to prevent other users from connecting
  to the damaged database until repairs have been completed.

Generally, consider upgrading to a current version of Postgres. Version 9.1 has just reached its EOL in Sept. 2016. There have been many related updates since, might very well fix the problem for good.


DB2 SUM of MAX values, single table I have looked through many of the questions asked and answered but can't seem to find my answer, even kludging the provided answers into my queries - most usually fail, the remainder provide garbage results.

NOTE THAT THIS IS FOR IBM DB2 v9.7!  NOT SQL Server or anything newer.

I have a single table that contains quasi-distinct rows, but has no key.  The odds of finding a duplicate are next to none based on ALL the fields, and the probability of duplication increases with the fewer fields selected in the query.  I'm mostly ok with that at this point.  There are more fields than listed below, and I have added a key just for reference...

PSEUDO-QUERY

select sum(max(NumImages)) from table where Source in ('A','B') and Event='190'

TABLE

Key   ID    NumImages   Source    Event
1     1     4           A         190
2     1     -           -         247
3     1     2           B         190
4     1     -           -         134

5     2     7           C         190
6     2     2           A         190
7     2     1           B         190
8     2     5           A         190

9     3     5           A         190
10    3     -           -         247
11    3     -           -         134
12    3     -           -         815

13    4     2           A         190
14    4     9           A         247
15    4     5           B         190
16    4     4           B         134

PARAMETERS

Source in ('A','B')
Event = '190'
NumImages is not null

(*I don't think that will ever happen with the preceding parameters)

OUTPUT

ID    Sum
1     6
2     6
3     5
4     7

Because:

1 = 6 ; keys 1 and 3 are both max for event 190 and source A or B
2 = 6 ; key 8 (5 - max for Event 190 Source A) + key 7 (1 - max for Event 190 Source B).  Source C (key 5) is ignored.
3 = 5 ; key 9 is the only value matching the parameters
4 = 7 ; keys 13 and 15 are the only ones that match Event 190, even though Source and NumImages have values that match against the other parameters.

I think you want multiple aggregations:

First find the max for each ID-Source combination, then sum for every ID:

select ID, sum(MaxNumImages) as SumMaxNumImages
from
  (
    select ID, max(NumImages) as MaxNumImages
    from table
    where Source in ('A','B') and Event = '190'
      and NumImages is not null
    group by ID, Source
  )
  as agg
group by ID ;


Replacing a large object in PostgreSQL What should one do when one wishes to replace a large object with a new large object. An example would be to update an uploaded file with a new version.

One way would presumably be to create a new large object, update links to the new oid, and then unlink the old object. But is it reasonable to instead replace the existing data, say, by writing over the original (perhaps truncating the contents first)?

FWIW I'd be doing this in JDBC and for PostgreSQL 9.4.

The idea behind the large object API is to mimic a file-like API. The OID is like the path of the file, and the file descriptor obtained by lo_open or lo_creat  is the equivalent of the POSIX open() and creat() system calls for files.

JDBC provides LargeObject.truncate() and the both libpq (in C) and the server have built-in lo_truncate() functions.

So yes it  makes sense to replace LO contents (keeping the same OID) by a truncate followed by lo_write if libpq, or lowrite if server-side code, or LargeObject.write() if using the dedicated JDBC class, as you could do with a file on a filesystem.

It would work as well to create a new LO with a new OID and then unlink the old one, it's just less elegant and it consumes a new OID for no good reason.


How do I use a dynamic command with copy from program? I'm trying to read a JSON API with the Postgres copy from program command.

When I call

copy _temp from program 'curl -uri "https://url"';

it works fine but the API has paging and I need to loop through multiple calls.

When I call it like this:

_command := 'curl -uri "https://url?&page='||(10::text)||"';
copy _temp from program _command;`

I get

ERROR:  syntax error at or near "_command"

You can't even concatenate in place like

copy _temp from program 'curl -uri "https://url?&page='||(10::text)||'"';

Percent parametric replacement in the raise notice style doesn't work either.

What gives? program is a literal string with single quotes so what is the difference between specifying a literal string and using a text or varchar variable? There doesn't seem to be any program data type (::program cast does nothing), what am I missing?

In the docs it says 'it is best to use a fixed command string' not that you can only use a fixed command string...

How do I use a dynamic command string?

If you want to use dynamic commands, you need the right context for it.  Plain SQL is not one.

One possibility is using PL/pgSQL, and it's EXECUTE functionality.  Use DO to get into the context:

DO $$
DECLARE page integer;
BEGIN
FOR page IN SELECT i FROM generate_series(1, 10) AS t(i)
LOOP
    EXECUTE format($cmd$ COPY _temp
                         FROM PROGRAM 'curl -uri "https://url?&page=%s"'$cmd$,
                   page::text);
END LOOP;
END; $$;

The other way is to run the loop on the shell side.  The following example is using bash:

for i in $(seq 1 10); do
    psql -c "COPY _temp FROM PROGRAM 'curl -uri https://url?&page=${i}'"
done

Notes:

I am using two levels of dollar-quoting ($$ and $cmd$) to prevent quote multiplication.
I am also using format() instead of the messy concatenation.
If you really have to quote the URI of the curl command, you will have to escape the quotes in there.


Why my query does not return correct data (NORTHWIND)? select *
from orders o
WHERE EXISTS
(
  select 1
  from orders o2
  where o2.EmployeeID = o.EmployeeID
    AND YEAR(o2.OrderDate) = 1997
    and MONTH(o2.OrderDate) = 9
    AND DAY(o2.OrderDate) = 5
)

I expect to receive only two records in result instead of 228.
As you can see this is EXISTS on the same table.
Thank you in advance.

There are two orders with an orderdate=1997-09-05.  One for employee=4 and one for employee=7.  Now, for employee=4, there are 156 rows on the order table and for employee=7, there are 72 rows on the order table.  That totals to 228.  Your query is returning all orders where the employeeid matches those two id's.

Alter your query to include a check for o2.OrderDate=o.OrderDate

SELECT *
FROM orders o
WHERE EXISTS (
        SELECT 1
        FROM orders o2
        WHERE o2.EmployeeID = o.EmployeeID
            AND YEAR(o2.OrderDate) = 1997
            AND MONTH(o2.OrderDate) = 9
            AND DAY(o2.OrderDate) = 5
            AND o2.OrderDate = o.OrderDate
        )

Based on your example, you would get the same results by simplifying the query to:

select * from orders where orderdate = '1997-09-05'


Scaling for MariaDB / Percona / Galera Scaling I ventured into the world of NoSQL database and as great as they are the lack of true ACID compliance has been a nightmare resulting in a lot of bad data so I am looking into relational databases.

I am very new into this, but have been looking into MariaDB and Percona clusters with Galera. I see a basic setup is 3 nodes, all which can be read/written to, and all contain identical data in each node.

My question is what happens if I need to scale horizontally:

slow write/reads
disk i/o issues
low memory

QUESTIONS

How can I scale to more machines?
What is the best practice?

I have not been able to find clear documentation on this.

Rolando may be right, or I may be right; it depends on the situation...

A single Galera cluster (PXC or MariaDB or hand-rolled) can have more than 3 nodes, but probably should not have a lot more than that.
Writes somewhat scale with Galera.  Especially if you have complex transactions that are heavy on reads.  In that case, the transactions are spread across the nodes, hence the reads are spread around.
Slaves hanging off each node can provide arbitrary read scaling.
In WAN setups, writes are faster in certain situations.  But perhaps you are talking only about LAN?
"low memory" and "disk I/O issues" are pretty much the same with or without Galera.  Maybe you want to address them separately?
Slow reads are usually caused by too many connections, poor indexing, poor SELECT formulation, etc.  Again, this is generally independent of Galera.
Adding a node to a Galera cluster is "trivial".  (Well not quite, but much of the work is automated.)

The only real way to get write scaling is via Sharding.  There is no "automatic" sharding (aside from Spider and Clustrix), and I am suspicious about how "automatic" they can be.


Strange Oracle ORDER BY I have a problem sorting strings in french :

This is my query :

SELECT LIBELLE as LibelleCourant,
       CODE as code
FROM S_MARQST
ORDER BY LibelleCourant;

And this is the result

LIBELLECOURANT  | CODE
----------------------
BAYARD          | BAY
LESIER          | LES
<Non renseigné> | INC
PAM             | PAM

As you can see, oracle seem to ignore the '<' charactrer and order only with alphabet characters. <Non renseigné> is neither at the top nor at the bottom. I tried NLSSORT(name, 'NLS_SORT=french'); but the result is the same.

I would like to have a basic ordering, with special chars either at the top or at the bottom (or both if it uses char codes, nevermind). As the data is user-generated, i cannot predict which chars will be used so i think i can not use CASE statements.

Thank you.

PS: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod

I use this solution but it is not an elegant solution. It converts all accented french characters to english equivalents and strips out odd characters.

order by translate(LibelleCourant,'ÉéÀàÇçÈèÙù''/*;"<>() ','EeAaCcEeUu')

Edit:  if you want to keep the special characters to sort by then change the translate function.
Example: you want to keep the < and > symbols, just add them to the end string
order by translate(LibelleCourant,'ÉéÀàÇçÈèÙù<>''/*;"() ','EeAaCcEeUu<>')

What is happening is that any single character which is present in the first string and has a corresponding value in the second string will be replaced.  Extra characters will be removed.  These characters will be replaced ÉéÀàÇçÈèÙù<>
and these characters will be removed:          ''/*;"()

See the Oracle docs for more info.


Why are those estimates way off? I have problems understanding the following query plan: https://www.brentozar.com/pastetheplan/?id=SyoD_PosP
First there is the bottom right index seek. The estimation says over 22 million rows but the actual data is less than 2 million rows. My guess is, the estimation is based on just the rows matching the Tag column and a look at the index's histogram seems to corroborate that. Am I right in thinking that no assumption is made about the result of matching those rows to the predicate of the join and that this is what results in the differnce between the estimation and the actual result?
Second and more importantly, the Hash Match estimates that it will return 140k rows when in actuality it is about twelve times that. This leads to the sort operator spilling into tempdb a lot, if I understand what is happening correctly. Why is the estimate so low? And is there any way to improve it?
Update: Coincidentally, the values of Record.Tag are exactly [10399, 10438]. In principle there is no guarantee that it is a continous interval, tho. I tried J.D.'s suggestion and split the query into four queries and UNION ALL'ed them together. No improvement there.
Next I tried filling a temporary table with the Record rows of those Tags:
SELECT * INTO #tmprecord
FROM Record
WHERE (Record.Tag>=10399 AND Record.Tag<=10438)

SELECT DateAndTime, r.Reading, r.Tag
FROM RecordSet
INNER JOIN #tmprecord AS r
ON RecordSet.RecordSetId = r.RecordSet
WHERE RecordSet.Collector=10090
AND RecordSet.DateAndTime >= '20201101 00:00:00'
AND RecordSet.DateAndTime <= '20210101 00:00:00'
ORDER BY r.RecordId

DROP TABLE #tmprecord

The estimation here seems better (four times instead of twelve times), but the spill takes about the same number of tempdb pages (76xx) and even more memory.

I believe this is due to all the ORs in your WHERE predicate on the Record.Tag field. It's causing a bad cardinality estimate. That is a rather long predicate and set of ORs, could you try breaking it into multiple smaller queries for different sets of Tags and the. UNION ALL the results of each query back together? It may help with the cardinality estimate.
You could also then change the ORs for a range when you break it up to make it less verbose at least, such as:
WHERE Record.Tag >= 10400 AND < 14006


Automate CSV files to MySQL Database? I'm a little mind struck at the second as I am looking to automate the following sequence:

Extract Data from Microsoft Access
Save files as CSV
Connect toMySQL server
Run one SQL file that exports all the CSV's using LOAD DATA LOCAL INFILE

I've written the entire SQL file that DROPs each table then re-uploads it and this runs like a dream in MySQL WorkBench. I just don't know how to get this to do the task without me physically carrying out the task...

I am now stumped how to automate part 4... There's no visual basic style backend to MySQL WorkBench and I thought requesting SSH from my provider would fix this, turns out my provider states SSH access is for web files not databases.

Is this the case with all MySQL or do some providers allow SSH directly to the database? If not how do I go about automating this sequence?

Many thanks.

Having researched and tested out a few ideas I have now found a solution.

1) Access or Excel and utilise VBA to Save the CSV files to the FTP of where your website is.
2) After the SaveAs VBA statement I then had another line of code that fires open a web browser which loads a PHP page and executes a INSERT with LOAD DATA LOCAL INFILE...

Even with a 50mb CSV file the saving process over BT Infinity only takes a minute or so and the PHP runs in seconds.

I'm no natural born coder, I find solutions to what I need to achieve and here's what I have working...

Here's the PHP (ensure MyTable.csv is in the same folder as the PHP file else modify below):

<?php

$sql1 = "TRUNCATE TABLE MyTable";

$sql2 = "LOAD DATA LOCAL INFILE 'MyTable.csv'
INTO TABLE MyTable
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r'
(@date_time_variable, field1, field2, field3, field4)
SET datestamp = STR_TO_DATE(@date_time_variable, '%m/%d/%Y %H:%i');";

$con=mysqli_connect("mysql_server_address","mysql_username","mysql_password","mysql_table");

if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
};

$result1 = mysqli_query($con, $sql1);
$result2 = mysqli_query($con, $sql2);

if (mysqli_affected_rows($con) == 1) {
  $message = "The data was successfully added!";
} else {
  $message = "The user update failed: ";
  $message .= mysqli_error($con);
};

echo $message;

mysqli_close($con);

?>

The VBA I used to save to FTP:

ThisWorkbook.SaveAs "ftp://ftp_username:ftp_password@ftp_site_address/MyTable.csv"

Thanks for the responses to the original question to those that tried to help but as you can see I needed a solution that required no extra input from the user, one button does all.

The only thing you will need to ensure is that the MySQL server you are scripting to allows the use of LOAD DATA LOCAL INFILE as some restrict this on shared servers. Also take note that you'll have to constantly have FTP access enabled or enabled from your IP Address as many hosts like this locked down these days.

Hope this helps someone given the fact it wasn't easily answered.


Postgres query plan of a function invocation written in plpgsql It's possible when using the pgadmin or plsql to get a hold of a query plan for a sql statement executed inside a user defined function (UDF) using EXPLAIN. So how do I get hold of the query plan for a particular invocation of a UDF? I see the UDF abstracted away into a single operation F() in pgadmin.

I have looked at documentation but I couldn't find anything.

Currently I'm pulling out the statements and running them manually. But this isn't going to cut it for large queries.

For example, consider the UDF below. This UDF, even though it has the ability to print out its query string, will not work with a copy-paste as it has a local created temporary table, which does not exist when you paste and execute it.

CREATE OR REPLACE FUNCTION get_paginated_search_results(
    forum_id_ INTEGER,
    query_    CHARACTER VARYING,
    from_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    to_date_ TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL,
    in_categories_ INTEGER[] DEFAULT '{}')
RETURNS SETOF post_result_entry AS $$
DECLARE
    join_string CHARACTER VARYING := ' ';
    from_where_date CHARACTER VARYING := ' ';
    to_where_date CHARACTER VARYING := ' ';
    query_string_ CHARACTER VARYING := ' ';
BEGIN
    IF NOT from_date_ IS NULL THEN
        from_where_date := ' AND fp.posted_at > ''' || from_date_ || '''';
    END IF;

    IF NOT to_date_ IS NULL THEN
        to_where_date := ' AND fp.posted_at < ''' || to_date_ || '''';
    END IF;

    CREATE LOCAL TEMP TABLE un_cat(id) ON COMMIT DROP AS (select * from unnest(in_categories_)) ;

    if in_categories_ != '{}' THEN
        join_string := ' INNER JOIN forum_topics ft ON fp.topic_id = ft.id ' ||
        ' INNER JOIN un_cat uc ON uc.id = ft.category_id ' ;
    END IF;

    query_string_ := '
    SELECT index,posted_at,post_text,name,join_date,quotes
    FROM forum_posts fp
    INNER JOIN forum_user fu ON
    fu.forum_id = fp.forum_id AND fu.id = fp.user_id' ||
        join_string
    ||
    'WHERE fu.forum_id = ' || forum_id_ || ' AND
    to_tsvector(''english'',fp.post_text) @@ to_tsquery(''english'','''|| query_||''')' ||
        from_where_date ||
        to_where_date
    ||';';

    RAISE NOTICE '%', query_string_ ;

    RETURN QUERY
    EXECUTE query_string_;
END;
$$ LANGUAGE plpgsql;

I addition to @rfusca's advice: SQL statements inside plpgsql functions are considered nested statements and you need to set the additional Parameter auto_explain.log_nested_statements.
Unlike some other extensions, you don't have to run CREATE EXTENSION for this one. Just load it dynamically into your session with LOAD. You must be  superuser for this.
Your session could look like this:
LOAD 'auto_explain';
SET auto_explain.log_min_duration = 1; -- exclude very fast trivial queries
SET auto_explain.log_nested_statements = ON; -- statements inside functions
-- SET auto_explain.log_analyze = ON; -- get actual times, too
SELECT * FROM get_paginated_search_results(...);

May produce a lot of log output.
The current manual on auto_explain.
Depesz wrote a blog article about it when it was introduced with PostgreSQL 8.4.


Maintain table with too many insert I have to create a table. Its size is going to increase very fast.

Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for username

I want to use the key so that after the value reaches the maximum possible value next key is generated with value 1. How can this be achieved? Old data is not useful so I can delete the data at the starting rows. I know UNSIGNED BIGINT has a huge range but is there any way I can create the database with a trigger so that key reuses old values? What can be the downside of trying to write user defined function to accomplish this?

There isn't any way to reset autoincrement such that it goes back to the beginning and just overwrites existing data.  Autoincrement isn't built that way.

What you could do is update rather than insert.  You'd have to either pre-populate every record with blank data so that every entry is always an update, or you could write a procedure which tries to read the next ID first and then either inserts or updates accordingly.  Either way, you will need to track the next ID yourself instead of letting the database handle it for you (with autoincrement).  When you get to the maximum value, reset the next ID to 1 and keep going.

Thinking Practically:

The largest value for UNSIGNED BIGINT is 18,446,744,073,709,551,615.  Depending on what is in your record you are looking at thousands (even millions) of petabytes of data.  Are you sure you aren't going to have space constraints with that much data?  You may find that what you need to do is purge out data that is old enough to be discarded as you go just to make room in your database.  If you do that, then you can avoid the prepopulation and just insert every time.

If you purge periodically, say every night, then you just need to wait until the gap between where your remaining data is and ID=1 is big enough that you don't have to worry about ID collisions.  When that happens, reseed your ID and start inserting from ID=1 again.  If you do this, then you can go back to using autoincrement.

Edit: Additional Information about reseeding...

OP mentioned in a comment that the command for reseeding an autoincrement column in mysql is of interest.  This command will reset the starting autoincrement value:

ALTER TABLE table AUTO_INCREMENT = 1

I have read that this should be done with caution because it can take a long time in a large table.

See also the documentation for AUTO_INCREMENT.


Cluster migration to new hardware, same OS, but SQL Server 2012 to 2016 I have a 3 node cluster running on Windows Server 2012 R2. Two nodes for the databases, the 3rd node is an Always On db used for reporting. I have new hardware built running the same OS, but will need to upgrade the SQL Server version from 2012 to 2016. All servers will utilize the same SAN.

Should I stand up a new cluster running SQL Server 2016, restore the database to it, going through all the renaming and such? Or can I do a type of rolling upgrade where I join the new servers to the existing cluster (I guess I would need to install 2012 on them first and then kick off the upgrade to 2016 on them) once I fail over to the new servers, I would evict the old nodes without upgrading them?

This seems like it would have the least amount of downtime. But I'm not sure  if I can easily rejoin the old servers to the cluster and rollback to the 2012 instance if there are problems with the 2016 instance. Most whitepapers I see revolve around OS changes, not same OS, but new SQL versions.

You will encounter no problem during the migration because even if the database coming from SQL Server 2012 pass to SQL Server 2016 the Compatibility Level stays the same (110).
After the upgrade use Data Migration Assistant and check if your database is compatible and can be actually migrated to SQL Server 2016 (130).
SQL Server 2016 will hold a database coming from SQL Server 2012 without problem. Changing the Compatibility Level is the trick.


HADR solutions for SQL Server 2016 Standard Edition I'm looking for some HADR solutions for our organization. I know we should have Enterprise edition to have the full fledged features but what we have is Standard Edition of SQL Server. I'm new to this organisation and in my previous endeavours I have set up HADR solutions only in Enterprise edition.

While I researched I found we can have Basic AG with Standard edition but I cant have more than one DB in the AG which makes the HA plan disastrous for our applications. Transaction Replication is not allowed in standard edition.

Is there any simple HA Plan any of you have configured with standard edition? I'm looking for the best options I'm left with before proposing Enterprise edition. Please advise.

Log Shipping for DR.  Not so much HA as no automatic failover, but still better than nothing.   Use Windows cluster for HA, and Log ship them to somewhere else.

transactional replication IS allowed in standard:
https://docs.microsoft.com/en-us/sql/sql-server/editions-and-supported-features-for-sql-server-2016#a-namereplicationa-replication

But is not really an HADR solution


how to design a good database design which suits for both RDMS and noSQL At present I am working in a startup company. There I am involved in both development and in database design. For this project we are using MySQL. In future we may migrate it to noSQL things like MongoDB or some other based on the traffic. So here my doubt is what should we keep in mind when designing a database, that should be fit for both RDBMS and nosql databases?

I know RDBMS and noSQL are entirely different. But what points should be considered when designing a database? How to give a good database design which will suit for RDBMS and noSQL?

The database designs for relational and document stores will be entirely different. You will not be able to take one and simply implement it in the other.

What will endure, however, is the data model - the logical representation of the entity types, connections between them, cardinalities, constraints, attributes, domains and rules governing them. The data model represents what your customers know and how they act. The database is one physical representation of this, limited by the product chosen and hardware. Getting the model right and keeping it up to date will aid current development and smooth any future transition.

The data model is a logical construct. It should not, in theory, be affected in any way by the features of the chosen storage product. From one data model several database designs can be derived. For a relational storage product the translation is quite direct. An entity type from the data model becomes a table in the database. A relationship from the data model becomes a foreign key in the database. Take the example of an order processing system. The entity types "Order" and "OrderLine" become the tables Orders and OrderLines, with OrderLines having a foreign key column of OrderNumber.

For a document store the translation may be more complex. The designer has more choices to make. Order and OrderLine may be mapped to different collections, they may be mapped to different documents within the same collection, or order lines may become sub-documents nested within the order document. The relationship between the logical entity types Order and OrderLine can be implemented either by copying the order's number into the order line's document, having an array of the order line's IDs in the order document or by nesting the order lines as sub-documents within the order document. Which to choose will be determined by the application's requirements. Whatever the physical implementation the logical analysis in the data model remains constant: one Order,  many order lines.

The next obvious point is to be scrupulous in separating to data access tier from the others. This way future rework will be focused on that area. Having good test scripts will pay off now and will be doubly valuable after the rewrite, to speed regression testing.

Finally, the chance of you migrating is slim. I do not doubt you will be successful. I do doubt you can guess now what your business and technical environment will look like several years from now. Everthing I have read by successful software companies says they rewrite the product three times before they get it "right." Use one of those re-writes to change persistence product and concentrate now on delivery and customer service.


Likes or votes for posts I am making a small program where users makes posts or write blogs. On those posts, other users can like or dislike the post as in facebook or upvote or downvote the post as in stackoverflow. I would like to know a good database structure which is commonly used & the program works efficiently with that structure. I have two options

First

Post:

id   head   message   datepost   likes   dislikes
1     ab    anchdg     DATE      1,2,3   7,55,44,3

In the above way, id is the postid. In the likes column, 1,2,3 is the user's id who liked or upvoted the post or blog. 7,55,44,3 is the id of the users who disliked or downvoted the post or blog.

Second

Post:

id    head  message   datepost
1     ab    anchdg     DATE

Likes:

id    postid    userid
1       1         1
2       2         2

Dislikes:

id    postid    userid
1       1         7
2       1         55

In this way, I have to create two separate tables for likes & dislikes to get post's likes. In this way, the tables i.e. Likes & Dislikes will get heavily filled. This might make table heavy & processing slow.

So, I would like to know which is the better & standard way to achieve this task?

The problem you face is known as "Normal forms" of databases, especially the first normal form. https://en.wikipedia.org/wiki/First_normal_form.

Your databse with the concatenated user IDs (first version) is not in first normal form.

See https://en.wikipedia.org/wiki/Database_normalization for why and how normalisation is generally considered good.

In your first example, the query for "user 4 does not like the post anymore" becomes complicated. It will have to do string operations, which will have to consider side effects and corner cases (user is the only "liking" user, user is the last liking user, user is in the middle of the liking user string). I would find this bad. Don't do it. Use a normalized design.

re: database gets heavy

If you have a post that has 4 million likes, in database design 1 you would have one row with a "likes" column that is at least 4 million characters wide (because you'll need the comma as seperator chars). You will then have to perform string operations on four million digit wide strings. This is very unperformant and slow.

On the other hand, databases are designed to handle millions of rows. We have databases with several hundred million rows, and count()-operations are fast. Extremely fast. So no, this will not be a performance bottleneck.

The next issue would be readability and maintainability.

For example, tell me what these 2 statements do:

select count(*)
from posts
inner join likes on posts.postid = likes.postid
where postid = 7

select len(likes) - len(replace(likes, ',', ''))
from posts
where postid = 7


Variable to select database in SQL query If I want use a variable in the FROM clause of a  SQL query I need to use dynamic SQL. Which might be a pain if the query is long and complex.

There are few different ways of running a query from a database you are not currently connected to.  The two methods I use most often are these..

Select * from msdb..sysjobs_view

or

USE MSDB
Select * from sysjobs_view

If you start on the master database both of the above will give you the same results.

When I put these both into dynamic SQL one works and the other does not

Starting from master, this works

DECLARE @DBN SYSNAME
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'Select * from ' + @DBN + '..sysjobs_view'

EXEC (@Sql)

But this does not

DECLARE @DBN SYSNAME
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN

EXEC (@Sql)
Select * from sysjobs_view

I get the error

  Msg 156, Level 15, State 1, Line 14
  Incorrect syntax near the keyword 'EXEC'.

My reason for this: One line of dynamic SQL in big complex query is going to be less painful to code than trying to turn the whole query into dynamic SQL.  My question here is just about why its not working with the USE clause.

Why can't I use dynamic SQL with the USE clause to change the database context?

NOTE: The below does work and would be easy to code.  But my question about why remains.

DECLARE @DBN SYSNAME
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN  +'
Select * from sysjobs_view
'

EXEC (@Sql)

Per the comment by Tibor Karaszi the change only applies "to the EXEC scope"  This is validated by the Microsoft statement below.

In SQL Server, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.
Changes in database context last only until the end of the EXECUTE statement.  Source

The easiest work around is probably, the final example in the question. Adding a couple of lines at the beginning and end of your code can change the context for just a single execution. The rest of your code stays on whatever the context was before the `EXEC'
DECLARE @DBN SYSNAME
SET @DBN = 'msdb'
DECLARE @Sql NVARCHAR(MAX)
SET @Sql = N'USE ' + @DBN  +' -- Add this
Select * from sysjobs_view
-- Up to 2GB of code here
' --And add this

EXEC (@Sql)

There are limitations to using this dynamic SQL solution  If there are single quotes in your 2GB of code, they have to be escaped by doubling the single quote.  This can be a huge pain if there is a lot of code to review and test.


partition in ndbcluster My environment will use 2-3 cluster nodes. The db schema at first was designed without knowing the limitation/rules with NDBCLUSTER on (BIG mistake! I know). Some things I notice and want to understand more:

(1) NDB Cluster normally partitions NDBCLUSTER tables automatically. So when I tried to check my table, it shows there are 2 partitions inside

mysql> explain partitions select * from my_request;
+----+-------------+------------+------------+------+
| id | select_type | table      | partitions | type |
+----+-------------+------------+------------+------+
|  1 | SIMPLE      | my_request | p0,p1      | ALL  |
+----+-------------+------------+------------+------+

but when I tried to check which record going to which partition, it gave me error. why?

mysql> select * from my_request partition (p0);
ERROR 1747 (HY000): PARTITION () clause on non partitioned table

(2) Some of my tables will have billions of records, and I (successfully) partitioned it BY RANGE into daily/weekly/monthly. However NDBCLUSTER only allow partition BY (LINEAR) KEY. Since the PRIMARY KEY is a UUID, I kind of lost how to partition the table.

Note: The partition is used not only for optimized query but also to dump and back-up (like dump the data older than 1y). I created an event and procedure to check from partition to do this. ty.

You can partition on a subset of the columns in the primary key. NDB
currently doesn't support dropping a partition. So to get rid of old
data one needs to use DELETE queries with proper WHERE clauses.


How to transfer data from denormalized table to normalized tables in Oracle? Hi I currently have a database set up with a table like this:

Grades: student name|class name|overall|hw|test|quiz|project|val

where student name and class name are type varchar2 with the rest being type number. The categories overall,hw,test and quiz are expected to be 1 or 0 and val is the actual grade out of 100. So a sample row with a student getting a 95 on her test would look like this:

Grades: Sally | Biology | 0 | 0 | 1 | 0 | 0 | 95

This structure is clearly suboptimal, so I decided to normalize this database with help from this question here to :

Student: id|firstname | lastname (pk = id)

Class: id|name|room num (pk = id)

Categories: id|name|weight (pk = (id, name, weight))

Grades: student_id (fk)| class_id (fk)| category_id (fk) | name | grade_val

ClassEnrollment: student_id (fk)| class_id (fk) | overall_grade (pk = student_id, class_id)

My main question is how to get the data from three tables to fill out the class enrollment table?

I can select all the overall grade using:

SELECT val
FROM grades_old
WHERE overall = 1;

And I can select the student id in a number of steps :

INSERT INTO temp(student, class)
SELECT student_name, class_name
FROM grades_old;

SELECT temp.student_name, students.name, students.id
INNER JOIN students
ON temp.student_name = students.name

However, I'm not sure how to put all this together to get the overall grade, student_id and class_id in one query.

Edit:

I have manually filled out the Category database since there is only 5 categories in the system right now (overall, hw, test, quiz and project). I have a name column in the student table for now for purposes of matching, I figured I could worry about splitting it later then drop that column once I have split the names.

So I guess my focused question is for now how can I select the overall grade, student_id and class_id from the original grades table to insert it into the class Enrollment table?

General

In general, you need to fill out the table in hierarchical order (parent, child, grandchild, etc).  The best way to do that is using SELECT DISTINCT.

This works very well if the text value (eg class_name) is UNIQUE.  This way, you can JOIN your source data to get the parent Id ( class_id ) for your child tables.

In some cases, as the process gets more complicated, you may want to consider creating some staging tables. These are tables whose purpose in life is to hold partially processed data temporarily so that you can review it before going onto the next step.

Your Specific

First, I am assuming that one and only one of the Category columns will have a value of 1.

First thing you need to do is UNPIVOT all of your data into a staging table so that you can use @Akina's method.

with data as (
SELECT
  'Sally' AS first_name
 ,'Biology' AS class_name
 ,0 AS overall
 ,0 AS hw
 ,1 AS test
 ,0 AS quiz
 ,0 AS project
 ,95 AS val
FROM dual
)
select *
from data
UNPIVOT (
  is_used
  for Category in ( OVERALL,HW,TEST,QUIZ,PROJECT)
)
WHERE IS_USED = 1;

This will return a row like this

Name  | Class | Val | Category | IS_USED
Sally | Biology | 95 | TEST | 1

Now you can

SELECT DISTINCT student_name to get individual students for
Student.
SELECT DISTINCT class_name to get individual Classes for each
Class.
SELECT DISTINCT student_name, class_name to get everyone's
ClassEnrollment

You'll need to join to Class on class_name to get class_id
You'll need to join to Student on student_name to get student_id

Finally, after you JOIN all the tables together, you can record
everyone's Grades

This assumes that you have clean data.  @Akina's method seems to assume your data is not clean (which is usually the case)


Merge two rows from a table in a single row I think it's pretty simple but I can't get around this one…
I have a table contactsFields like this :

| id | contactId | fieldType | order | value |

I want to get the email with order 0 and company with order 0 as well.

Pretty straightforward to get either one :

SELECT "contactId", "value"
FROM "contactFields"
WHERE "order" = 0 AND "fieldType" = '<email OR company>';

This query can return no row, or a single one because we enforce that there are never 2 fields with same fieldType, contactId and order

But I would like to get both of them at the same time (i.e. merge 2 results on a single row), like this:

| contactId | emailWithOrder0 | companyWithOrder0 |

Can I do this with a join?

@a_horse_with-no_name's query improved:

SELECT "contactId",
        email,
        company
FROM
    ( SELECT "contactId", "value" AS email
      FROM "contactFields"
      WHERE "fieldType" = 'email'
        AND "order" = 0
    ) AS e
  FULL JOIN
    ( SELECT "contactId", "value" AS company
      FROM "contactFields"
      WHERE "fieldType" = 'company'
        AND "order" = 0
    ) AS c
  USING ("contactId") ;


Log all errors in queries I have a Microsoft SQL Server 2008 R2 database.

I have some applications in different programming languages. Some of them are legacy and I would not like to modify them.

Is there a way to log at server level all query errors, regardless of the application causing it?

I would like to know, for each error, the query causing it, the error type, and ideally loginame and hostname.

If you are just looking to log this information, you can set up an Extended Events session and capture the error_reported event.  Here is an example:

CREATE EVENT SESSION [ErrorCapture]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.client_hostname,
        sqlserver.database_id,
        sqlserver.sql_text,
        sqlserver.username
    )
    WHERE
    (
        [severity] >= (11)
    )
)
ADD TARGET package0.asynchronous_file_target
(
    SET filename=N'C:\SqlServer\Testing\ErrorCapture.xel'
)
WITH
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=30 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);
GO

ALTER EVENT SESSION [ErrorCapture]
ON SERVER
STATE = START;
GO

To test this out, here is a test error with RAISERROR():

raiserror('This is a test error', 2, 1);
go

Then by looking at the XE log through the sys.fn_xe_file_target_read_file system function, you will be able to see all of the logged errors.


Questions about tempdb version store We ran across an error today:

  Error: 3967, Severity: 17

and from what I can tell it's basically 'full version store'. This has raised some questions that I've had trouble answering, as at my shop we've never had this error before:

If there is a preset max size (based on total tempdb size or otherwise), is it dynamic?
Is there a setting that would make it dynamic if it isn't?
Can we set up alerts based on this? (i.e. DMVs to use to track size etc.)

Severity 17 indicates SQL Server ran out of a required resource.  You typically see this when a database runs out of space.  In this case, it sounds like tempdb is being used for read committed snapshot isolation version rows for a query that needed to spill to tempdb.

This could be quite common if tempdb has a limited maximum size and a poorly written query is attempted that, for instance, performs a very large cross join.

You can use this query to see the current size of the version store in tempdb:

SELECT *
FROM sys.dm_os_performance_counters dopc
WHERE dopc.counter_name LIKE 'Version %';

Technet has an interesting article on version store usage at https://technet.microsoft.com/en-us/library/cc966545.aspx#EDAA

This MSDN page mentions error 3967:

  When tempdb runs out of space, the Database Engine forces the version stores to shrink. During the shrink process, the longest running transactions that have not yet generated row versions are marked as victims. A message 3967 is generated in the error log for each victim transaction. If a transaction is marked as a victim, it can no longer read the row versions in the version store. When it attempts to read row versions, message 3966 is generated and the transaction is rolled back. If the shrinking process succeeds, space becomes available in tempdb.

Neither of the pages I've linked above contain explicit advise about the maximum size of the version store in tempdb, which leads me to believe the only limitation is the maximum configured size of the tempdb itself, minus whatever space is being used in tempdb for other objects.


Help needed to tidy up deprecated (and mal-functioning) SQL code I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4).

I've inherited a chunk of SQL which apparently uses quite a lot of deprecated stuff and, importantly, "sometimes" yields wrong results (seems to be picking an outdated instance of data: my hunch here is that some of the many joins are buggy).

First off, I'm therefore trying to clean it up and then see why "wrong" results are (sometimes) produced.

My first ideas of what needs cleaning up is this (please feel free to add further ideas to the list :):

the Oracle (+) Operator, by re-writing it to LEFT OUTER JOIN's
the mix of "old, ancient and fragile implicit joins" by re-writing them
as explicit JOIN's

Here goes an attempt at producing an equivalent of an MWE (minimal working example), though I don't have online data to share here by which this thing could actually fly. This is an attempt to take the main elements of the inherited code and syntax:

SELECT
    *
FROM
    a1,
    a2,
    a3,
  ( select * from b3 where col4 in
        (select col5 from b6 where co7 = '007')
  ) c,
    a7
WHERE
      a1.col8 = c.col9 (+)
AND
      c.col4 = a2.col5 (+)
AND
      a2.bla = a3.bla
AND
      a2.id = a7.id (+)

Here's the code above with the schemas present in the FROM section (if that's of any help?). In the WHERE section, the aliases are used already and no schema seems to be referenced...

Oracle seems to have a specific understanding of what a schema actually is... Below, SCHEMA_A would a name in the tree-branch-list under "Other Users" and a1 would be a table beneath that (on a twig of said branch).

SELECT
    *
FROM
    SCHEMA_A.a1,
    SCHEMA_A.a2,
    SCHEMA_A.a3,
  ( select * from SCHEMA_B.b3 where col4 in
        (select col5 from b6 where co7 = '007')
  ) c,
    SCHEMA_A.a7
WHERE (...)

Related links:

https://stackoverflow.com/questions/23851976/left-outer-join-with-subquery-syntax
https://stackoverflow.com/questions/49549506/left-join-on-multiple-tables-error-table-or-view-does-not-exist
https://stackoverflow.com/questions/4020786/oracle-operator
https://docs.oracle.com/database/121/SQLRF/queries006.htm#SQLRF52354 (Oracle recommending to not use the Oracle (+) operator, for several reasons)

First attempt at 1. (re-writing the Oracle (+) Operators as LEFT OUTER JOIN's); the subquery-part still isn't good though I guess; this is very-much draft-stage...

SELECT
    *
FROM
    a1

LEFT JOIN ( select * from b3 where col4 in
        (select col5 from b6 where col7 = '007')
  ) c ON a1.col8 = c.col9

LEFT JOIN a2 ON c.col4 = a2.col5

INNER JOIN a3 ON a2.bla = a3.bla

LEFT JOIN a7 ON a2.id = a7.id


Determining which SQL Server tables are accessed over a week? I inherited a large, pretty-much undocumented SQL Server 2008 R2 general purpose database, used by dozens of different applications.

We know many of the tables are no longer in use.

Any suggestions on how to determine what tables are still being routinely accessed over a weeks time? Or how to best configure Profiler to gather that info?

One possible way is to look into index usage statistics, such as sys.dm_db_index_usage_stats() (and several others with similar names).

Two possible catches here are:

Their data isn't persisted between server restarts, so make sure
that uptime is sufficiently long.
If you have scheduled maintenance jobs, for example, regular index rebuilds, they will spoil the statistics. But at least you will be able to separate your objects into 2 categories: one with similar access counts across all of them and the rest is well above that.

Overall it's not very reliable, but at least will give you some quick initial insight. The second step could be, for example, denying all permissions on suspected tables and watch for your work phone / email box :).


Postgres Refresh Materialized View Locks I have a materialized view in Postgres, and want to know what locks (if any) are taken out when refreshing that view.

CREATE TABLE people ( name VARCHAR(30) );
INSERT INTO people VALUES ('Alice'), ('Bob'), ('Cher');
CREATE MATERIALIZED VIEW test AS SELECT * FROM people;
REFRESH MATERIALIZED VIEW test;

Specifically, I'm trying to understand whether the REFRESH MATERIALIZED VIEW command takes out an ACCESS EXCLUSIVE lock.

I tried an explain with no success:

#> EXPLAIN REFRESH MATERIALIZED VIEW test;

                QUERY PLAN
-------------------------------------------
Utility statements have no plan structure

REFRESH MATERIALIZED VIEW CONCURRENTLY takes an EXCLUSIVE lock, so SELECTs may still run. Per the manual.

There seems to be an oversight with REFRESH MATERIALIZED VIEW (without CONCURRENTLY) as it's not listed there. A search of the source code shows that ExecRefreshMatView take an ACCESS EXCLUSIVE lock, as you might expect, so no other queries may run on the view.

I've submitted a docs patch to list it.


BEGIN / COMMIT atomic update I try to generate unique identifiers for the ticket table's records.
A ticket belongs to a file record, which also have an identifier.
If the file's identifier is 'SOMEPREFIX/F01' then its tickets should be
'SOMEPREFIX/F01/PT001', 'SOMEPREFIX/F01/PT002', 'SOMEPREFIX/F01/PT003' ...

The architecture: JS Client, a node.js API with some ORM, but I am also able to run raw SQL statements. DB: PG 9.6

The problem: If the client send (almost) the same time a bunch of ticket create requests, the DB generates sometimes the same identifier.

Here is an sql query I generate dynamically in the API after the ticket is already inserted:

      BEGIN;
      UPDATE ticket SET
      ticket_number_index = (COALESCE((SELECT MAX(ticket_number_index)
                        FROM ticket
                        WHERE file_id = 'D530'), 0) + 1),
      ticket_number = CONCAT('SOMEPREFIX\F717\PT', TO_CHAR((COALESCE((SELECT MAX(ticket_number_index)
                        FROM ticket
                        WHERE file_id = 'D530'), 0)  + 1), 'fm00000'))
      WHERE id = 'D3571';
      SELECT * FROM ticket WHERE id = 'D3571';
      COMMIT;

I would assume that BEGIN and COMMIT makes the code atomic, and after the first UPDATE the next one can't produce the same identifier.
Although I have just saved saved two tickets (two POST queries started almost the same time by the client), and I ended up with 'SOMEPREFIX\F717\PT1' and 'SOMEPREFIX\F717\PT1'

Your approach is broken because of the atomic transactions.

Any change to the table is not visible to other transactions until the change is committed.

So if three transactions are started at the same time, the max() value will be the same for all three of them resulting in the same generated identifier. The only way to avoid this, is to exclusively lock the entire table before you insert a row. Which means you can not have more than one transaction inserting rows at one given time.

In general using this approach is a really bad idea because implementations are either broken (as you have discovered) or they won't scale because of the locking needed.

The only scalable and safe way to generate unique numbers is to use a sequence.

There is also no need to store the prefix twice, just store the prefix e.g.:   'SOMEPREFIX\F717\PT' in one column and store the unique identifier generated by a sequence in another column. If you need to display them as one, do that in the application.


Subqueries' aliases same as main queries' aliases I have an SQL query whose aliases are the same as some of its subquery's aliases.

For example:

select *
from ROOM r
where ...
         (
              select *
              from ROAD r
              where ...
         )

This works fine, as the subquery's alias seems to hide the main one's.

Will it work that way in all cases?
Will I ever get undefined results?
If it's OK to do that, how can I make a reference to the main query's r?

It's OK for nested subqueries to use the same aliases as used in the parent query, although it might be a bit confusing for someone reading the code.  The name space for aliases on a nested subquery is separate from the name space on the parent.  For example the query below has a nested subquery b that also has an alias b used within it.  This would be potentially confusing to the programmer but fine with the DBMS engine:

   select a.foo
          ,b.bar
          ,b.BarCount
      from (select b.bar
                  ,count (*) as BarCount
              from BarTable b
              join OtherTable o
                on b.OtherTableID = o.OtherTableID
             group by b.bar) b
      join Foobar a
        on a.bar = b.bar

On a correlated subquery you have access to the parent's aliases, so the aliases must be unique across the parent query and correlated subquery.  If we take a correlated subquery such as the one below we have a single, global name space shared between the parent query and the correlated subquery:

select a.foo
      ,b.bar
  from Foobar a
  join Bar b
    on b.FooBarID = a.FooBarID
 where not exists
       (select 1
          from Bar b2
         where b2.BarCategoryID = b.BarCategoryID
           and b2.BarDate > b.BarDate)

The correlated subquery does not have an alias as it does not participate in a join as such1.  The references b and b2 for bar are both available to the subquery as correlated subqueries share their namespace for aliases with the parent.

1 Note that the optimiser may choose to use join operators within the plan behind the scenes, although the actual operation specified is a correlated subquery and not a join against a nested subquery.


Does "WHERE 1=1" usually have an impact on query performance? I recently saw the question "where 1=1 statement"; a SQL construct I have used often in constructing dynamic SQL in an effort to write cleaner code (from the perspective of the host language).

Generally speaking, does this addition to a SQL statment negatively affect query performance? I'm not looking for an answer in regard to a specific database system (because I have used it in DB2, SQL Server, MS-Access, and mysql)-- unless it's impossible to answer without getting into specifics.

All the major RDBMS, as far as I know, have built in constant evaluations.  This should evaluate pretty much instantaneously in any of them.


What is the difference between Logical connection and User connections? I have a simple question.

What is the difference between Logical connection and User connection in MS SQL server ?

I am using perfmon to get SQL connection count. In that I found two options, Logical connection and User connection. But no where I am getting the exact differences between these.

So please help me.

I looked into the Books Online Doc, it says

  Number of logical connections to the system.

  The main purpose of logical connections is to service multiple active
  result sets (MARS) requests. For MARS requests, every time that an
  application makes a connection to SQL Server, there may be more than
  one logical connection that corresponds to a physical connection.

  When MARS is not used, the ratio between physical and logical
  connections is 1:1. Therefore, every time that an application makes a
  connection to SQL Server, logical connections will increase by 1.


Single user mode looses connection So just a quick background, we are trying to update the database design, in a production environment. But we want to be sure, no users try to login during that time. So we started looking into single user mode, but that gave us some trouble, sometimes we would lose the connection in the middle of the update.
So we setup a test environment to replicate the behavior.
We are using Microsoft SQL server 2017, with the AdventureWorks2017 database to replicate the issue.
On the database we have turned off Auto close and Auto Update Statistics Asynchronously
If we then have two connections to the server, using the master database.
Tell one of them to run this script
USE MASTER
SET DEADLOCK_PRIORITY HIGH
ALTER DATABASE [AdventureWorks2017] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses
WHERE spid > 50 AND dbid = DB_ID('AdventureWorks2017')
EXEC(@kill);

USE AdventureWorks2017
GO

DECLARE @cnt INT = 0;
WHILE @cnt < 10000
BEGIN
  SELECT TOP 1000 * from Person.Person;
   SET @cnt = @cnt + 1;
end;

And then on the other repeatedly run
SELECT TOP 1000 * FROM AdventureWorks2017.Person.Person;
GO;

At some point the first script stops working, and complains with an error

Database 'AdventureWorks2017' is already open and can only have one user at a time.

But to our understanding, this should not happen cause it still has the connection.
Note this doesn't happen all the time. But it's still fairly consistent.
Is there anything that we are missing, or can this be an issue with the SQL server?

When you set a database to single_user, you don't have any control over which session gets to connect, unless you're already connected to the database.
So simply connect before setting single_user to eliminate the race condition:
USE AdventureWorks2017
ALTER DATABASE CURRENT SET SINGLE_USER WITH ROLLBACK IMMEDIATE


MySQL Workbench copies Schema, but not data, from SQL Server 2008 I need to copy/migrate a database from MSSQL (SQL Server 2008) into MySQL. I am using the MySQL Workbench kit, and work through the process of the Migration. Each respective DB user has full access, the ODBC drivers are in place as well as the DSN.

It winds up copying the schemata successfully, but not any of the data. There are no errors or warnings (other than charset notices) and I am drawing blanks as to why data will not transfer over. There are not a lot of options in the migration steps, so I don't think I missed anything.

According to the MySQL Documentation, the steps for the Migration Wizard should be

Connecting to the databases
Schemata Retrieval and Selection
Reverse Engineering
Object Selection
Migration
Manual Editing
Target Creation Options
Schema Creation
Create Target Results
Data Migration Setup
Bulk Data Transfer
Migration Report

Sounds like you got to Step 9. You have to make sure to setup Data Migration. Apparently, it is not automatic.

If you are having problems with the import, you are not alone. This has been a source of heartache for many. You may need to try :

MySQL Migration Toolkit
SQLyog


Ops Manager clarification I am quite new to MongoDB ops manager backup process

I have understood basic ops Manager setup from the document  https://docs.opsmanager.mongodb.com/v4.0/tutorial/install-basic-deployment/

My doubt is for using Filesystem snapshot storage do we need to have separate storage mount point for keeping Oplog store or can we use the same mongodb instance which running backup database ?

As per MongoDB documentation here  The Ops Manager Backup Agent provides scheduled snapshots and point-in-time recovery of your MongoDB replica sets and sharded clusters.

How it Works

A lightweight Backup Agent runs within your infrastructure and backs up data from the MongoDB processes you have specified.

  NOTE  : Only sharded clusters or replica sets can be backed up. To back up a standalone mongod process, you must first convert
  it to a single-member replica set.

Backup Data

When you start backing up a MongoDB deployment, the agent performs an initial sync of the deployment's data as if it were creating a new, invisible member of a replica set. For a sharded cluster, the agent syncs each shard’s primary member and each config server. The agent sends the initial sync and oplog data over HTTPS back to Ops Manager.

The Backup Agent then tails each replica set's oplog to maintain on disk a standalone database, called a head database. Ops Manager maintains one head database for each backed-up replica set. The head database is consistent with the original primary up to the last oplog supplied by the agent.

The Backup Agent executes the initial sync and the tailing of the oplog using standard MongoDB queries. The cluster being backed up is unaware of the additional copy of the backup data.

The Backup Agent uses a MongoDB instance version equal to or greater than the version of the replica set it backs up.

The Backup Agent takes and stores snapshots based on a user-defined snapshot retention policy. Sharded cluster snapshots temporarily stop the balancer so that they can insert a marker token into all shards and config servers in the cluster. Ops Manager takes a snapshot when the marker tokens appear in the snapshot data.

For further your ref here , here and here


Most efficient table storage for determining recurrence What is the most optimal table structure to store schedules / recurrence definitions so that we can efficiently query it for all events that will occur within a date range (min:1 max:32 days)?

My problem is currently MySQL but comments on other RDBMS will also help, since algorithms are mostly transferrable.

Consider the schema:  Event -|--has one or many--<- Schedules

Schedule is a record defining a recurrence, e.g.

Monthly, every month Monthly, first Wednesday of every month
Monthly, Monday and Tuesday of every 3rd month from start date
Monthly, Monday and Tuesday of every May, June and July
Weekly, every Tuesday through Saturday every week
Weekly, Monday every 3rd week from start date

My table will have about 10,000 records at most.

For optimal store and later search, I would consider using two tables.

The first table would hold the event/schedule definition (frequency, internal, recurrences etc) - holding 1 line per event
The second table will be maintained by a process running on the first table and calculating->adding the data for future occurrences of each event.

Based on your system requirements you can decide how far ahead the second table should be filled.

This will make it easy to later query a certain events running within a specific time frame.

This solution should be more elegant than trying to calculate the date range on-the-fly, which is more complex and definitely slower in performance.


Very slow merge replication synchronization I had an unforeseen issue with a sub going down (generic error, was probably a connection issue) and now that I've reconnected the sub it is insanely slow re-syncing. It seems to download about 1000 changes then sit there for about an hour before adding another 1000.

Any ideas why this is going so slowly? This is just one table of dozens and I feel like this won't catch up before Monday (when I have users meant to be coming online).

Any help is appreciated!
Thanks!

UPDATE

It's been running for over 24 hours now and is slowly chewing through the records but at a still very slow pace. It now toggles between the shot above and 'Enumerating changes in filtered articles using Subscriber's assigned partition ID'. Yes, there is a filter in place but it's been there since I first set up replication and has never caused an issue before.

SECOND UPDATE

When I go into Replication Monitor there is an error:

The replication agent has not logged a progress message in 10 minutes. This might indicate an unresponsive agent or high system activity. Verify that records are being replicated to the destination and that connections to the Subscriber, Publisher, and Distributor are still active.

But what is strange is that there is zero system activity and I have another subscriber on this publication working as well a secondary replication from the same publisher to the same subscriber that is working perfectly. I've tried stopping and restarting, taking and applying a fresh snapshot but nothing seems to work. There are no blocked processes, no active queries that are deadlocking or anything else I can think to check.

To be clear:

Publication A => Sub 1: WORKING
Publication A => Sub 2: NOT WORKING
Publication B => Sub 1: WORKING
Publication B => Sub 2: WORKING

If anyone has any help, I'd much appreciate it.

There are many factors that can affect replication. Some common factors that I've come across are related to slow networking, blocking and storage issues. But the one that may be a factor is the performance problem with virtual log files. If you are initializing a database with a large amount of data and the default growth factors are in place, then sql server will grow the data 1mb at a time and the log 10 percent at a time. To check for the VLF issue, run dbcc loginfo. If that command returns over 100 records, I would be concerned. If it returns thousands of records, then it will have a real impact on performance. There are plenty of articles written on this subject. The basic fix is to adjust the autogrowth settings on all data and log files to reasonable sizes, then shrink the log file and initialize it back to the original size. I would check all of the databases, including the distribution database. There could also be other reasons why the distribution database is slow to distribute the transactions. I've experienced this on several transactional replication systems in the past and was suprised at the impact of correcting this every time.

I would also suspect locking at the publisher. Identify the spids involved in the replication and use the dynamic management view sys.dm_os_waiting_tasks to determine which waits are involved in these sessions. This will help identify what these subscriptions are waiting on.


Returning Right of a string with no specific known length I want to return the data from a column from the right. This would mean using the RIGHT() function, however this means entering a length of what to return. For example, I have the following data:

NAME:

A. Brown
S. Smith
C. Taylor
G. L. Potter
L. M. Lyle

I want to return just the surname, so ideally from the right up to the first space which is a different length with each name. So I want to return Brown, Smith, Taylor, Potter, Lyle.  Im not sure what the best way to do this is... Would substring be better? How would I get that to return from the right too?

Thanks

If names have blanks separating First, Middle, and Last, you want to use SUBSTRING_INDEX. The function takes three parameters: the string, the delimiter, and the token position.

If the token position > 0, you collect substring from the left.

If the token position < 0, you collect substring from the right.

Let's pick one of the name from your question : G. L. Potter

Using this query

SET @str = 'G. L. Potter';
SELECT
    SUBSTRING_INDEX(@str,' ',-3) token_3,
    SUBSTRING_INDEX(@str,' ',-2) token_2,
    SUBSTRING_INDEX(@str,' ',-1) token_1,
    SUBSTRING_INDEX(@str,' ', 0) token0,
    SUBSTRING_INDEX(@str,' ', 1) token1,
    SUBSTRING_INDEX(@str,' ', 2) token2,
    SUBSTRING_INDEX(@str,' ', 3) token3
\G

Please note how you can iterate and parse tokens with SUBSTRING_INDEX:

mysql> SET @str = 'G. L. Potter';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     SUBSTRING_INDEX(@str,' ',-3) token_3,
    ->     SUBSTRING_INDEX(@str,' ',-2) token_2,
    ->     SUBSTRING_INDEX(@str,' ',-1) token_1,
    ->     SUBSTRING_INDEX(@str,' ', 0) token0,
    ->     SUBSTRING_INDEX(@str,' ', 1) token1,
    ->     SUBSTRING_INDEX(@str,' ', 2) token2,
    ->     SUBSTRING_INDEX(@str,' ', 3) token3
    -> \G
*************************** 1. row ***************************
token_3: G. L. Potter
token_2: L. Potter
token_1: Potter
 token0:
 token1: G.
 token2: G. L.
 token3: G. L. Potter
1 row in set (0.00 sec)

mysql>

If you want the second token from the right, you have to do this:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1);

It runs like this:

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1);
+-----------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-2),' ',1) |
+-----------------------------------------------------+
| L.                                                  |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

How about the third token from the right ?

mysql> SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-3),' ',1);
+-----------------------------------------------------+
| SUBSTRING_INDEX(SUBSTRING_INDEX(@str,' ',-3),' ',1) |
+-----------------------------------------------------+
| G.                                                  |
+-----------------------------------------------------+
1 row in set (0.00 sec)

mysql>

You will have to spend some time setting up iterative code around SUBSTRING_INDEX, but at least a parsing mechanism is possible when done as I described.


SQL 2005 Full Recovery Mode Point in Time Restore w/out Backup We have a database that is in full recovery mode.  The database is not in production yet so does not have a backup plan in place and is thus not being backed up.  In other words, there is no existing backup. Recently, during an update, about 400 rows of data were wiped out.

Is there a way to recover those rows given that we have full transaction logging?   I understand that if we had a full backup w/ transaction logs we could do a point in time recovery.  Will this work if we now take a full backup of transaction logs and the database, then do a recover?

I feel like there is one path to recovery, I'm just unclear of the order in which to proceed.

Thanks for your assistance!

UPDATE:  We ended up using this tool http://www.apexsql.com/sql_tools_recover.aspx I would still be interested if anyone comes across this and knows a better way.  For now, we paid the price, but we got the results.  Thanks for the responses!

Not just using the transaction log, no. It records redo, but not undo, so you can only roll forwards from a point in time, which is the time of a backup. It may be possible to replay the entire log from the start on a blank database, but I don't know you'd "tell" SQL Server that you wanted to do that, even creating a database "for load" probably wouldn't allow it...


Restoring SQL Server 2000 database to SQL Server 2008 Is it necessary to restore system databases of SQL Server 2000 to SQL Server 2008, if I am restoring my other databases from 2000 to 2008?

No. In fact you can't restore the system databases from a SQL Server 2000 system to a SQL Server 2008 instance. If you did parts of (or the entire) database engine won't work as the SQL Server 2000 system databases are formatted very differently from SQL Server 2008.


MS SQL Server 2008: How to improve performance after deleting transaction log file Due to the size of transaction log file (it grew up to 200 GB) I had to delete it because the disk was running out of space. I know this is not a good practice but anyway after that the performance is very bad.

Does anyone know whether it is possible to improve performace and how in this situation?

The problem probably occurred because in order to delete the transaction log you would have had to stop the SQL Server. This causes the buffer pool (cached data and execution plans) to be erased - nothing can be in memory for a process that isn't running anymore. Now that you've restarted your server, all of your execution plans need to be recompiled.

I would also guess that you're running the server with the default transaction log settings wich specify a default size of 1MB for the transaction log and an automatic growth of 10%. These are very bad settings for performance. The transaction log has to 0 out the file when it grows. You can read more about transaction log management in 8 Steps to Better Transaction Log Management and Database Maintenance Best Practices Part III - Transaction Log Maintenance.

The first question is what did you do to the transaction log? You should never delete a transaction log. This is how your database is able to recover from a crash.

The second question is do you have a log backup solution in place? That is the only reason your transaction logs are growing so large. I suggest looking at Ola Hallengren's excellent backup scripts.

Typically, you want to grow your transaction log to a fixed, sane, size to start with and leave it there. This will depend on your database, but in the past I've monitored log file growth over the course of the week with full database backups happening every morning and log file backups running every 15 minutes. Once I've figured out how large the transaction log can get, I size the transaction log to about 15% larger than that (but as a multiple of 8GB).


Limit the number of votes I have a table of content. Users can vote on a content row. They can vote up to three times.  I'm looking for the most efficient way to make sure they don't go over three votes.

I can think of two main designs...

1) Each user can add one row per vote.

CREATE TABLE Vote
   int userId
   int contentId

2) Each user can add one vote row and then its quantity value is incremented until 3 has been reached.

CREATE TABLE Vote
   int userId
   int contentId
   int quantity

To do this properly (i.e. thread safe) I think the sequence for these cases would be as follows...

1)
a) Read the parent User row and hold an update lock on it.
b) Count the number of votes.
c) If less than three then insert a row.
d) Commit

2)
a) Read the parent User row and hold an update lock on it.
b) Do an exists on the vote row.
c) If not there then add it.
d) If there then check the quantity is below 3 and update if true.
e) Commit

Any ideas? Any way around holding an update lock on User? (At the very least this always seem to be awkward to do with an ORM ;))

If I forget the transaction then I think method 2 is the safest and the worst case scenario is that the odd vote gets lost or a user gets a few more than 3 votes.

Any ideas on how best to achieve this?

If performance is your primary concern, I suggest the second approach.  No need to touch the parent User table.  Use the serializable isolation level to address concurrency if the same user can have multiple sessions.

CREATE TABLE Vote(
     userId int NOT NULL
   , contentId int NOT NULL
   , quantity int NOT NULL
   , CONSTRAINT PK_Vote PRIMARY KEY CLUSTERED (userId, contentId)
   );
GO

CREATE PROC dbo.CastVote
    @userId int
   ,@contentId int
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;

BEGIN TRAN;

INSERT INTO dbo.Vote (userId, contentId, quantity)
SELECT @userId, @contentId, 1
WHERE NOT EXISTS(
    SELECT *
    FROM dbo.Vote WITH(UPDLOCK)
    WHERE
        userId = @userId
        AND contentId = @contentId
        );

IF @@ROWCOUNT = 1
BEGIN
    COMMIT;
END
ELSE
BEGIN
    UPDATE dbo.Vote
    SET quantity = quantity + 1
    WHERE
        userId = @userId
        AND contentId = @contentId
        AND quantity <= 2;

    IF @@ROWCOUNT = 1
    BEGIN
        COMMIT;
    END
    ELSE
    BEGIN
        RAISERROR('Nor more than 3 votes may be cast', 16, 1);
        ROLLBACK;
    END;
END;

GO

If the more common case is that users will cast more than 2 votes , you could reverse the logic and update first:

CREATE PROC dbo.CastVote
    @userId int
   ,@contentId int
AS
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;

BEGIN TRAN;

UPDATE dbo.Vote
SET quantity = quantity + 1
WHERE
    userId = @userId
    AND contentId = @contentId
    AND quantity <= 2;

IF @@ROWCOUNT = 1
BEGIN
    COMMIT;
END
ELSE
BEGIN

    INSERT INTO dbo.Vote (userId, contentId, quantity)
    SELECT @userId, @contentId, 1
    WHERE NOT EXISTS(
        SELECT *
        FROM dbo.Vote
        WHERE
            userId = @userId
            AND contentId = @contentId
            );

    IF @@ROWCOUNT = 1
    BEGIN
        COMMIT;
    END
    ELSE
    BEGIN
        RAISERROR('Nor more than 3 votes may be cast', 16, 1);
        ROLLBACK;
    END;
END;

GO


Passing a variable in OPENJSON WITH statement in SQL Server Below script calls an API with base and other currency as input and gets foreign exchange(FX) value from its response. While reading the response from api we have to specify '$.rates.GBP' in openjason statement to read value of FX(last statement of below script). I am not able to pass this as a parameter to make SampleValue dynamic.
I tried using dynamic SQL but did not succeed. Please help.
/*
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

EXEC sp_configure 'Ole Automation Procedures';
GO

*/
DECLARE   @Object   Int
        , @hr       int
        , @Base     char(3)
        , @Symbol   char(3)
        , @Url      varchar(1000)
        ,@Param varchar(1000)
DECLARE   @json  as table(Json_Table nvarchar(max))

SET @Base   = 'USD'
SET @Symbol ='GBP'

SET @Url = 'https://api.ratesapi.io/api/latest?base=' + @Base + '&symbols=' + @Symbol    ;--Your Web Service Url (invoked)
SET @Param = '$.rates.' + @Symbol;

select @Url;  --https://api.ratesapi.io/api/latest?base=USD&symbols=GBP

Exec @hr=sp_OACreate 'MSXML2.ServerXMLHTTP.6.0', @Object OUT;
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'open', NULL,'get', @Url, 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object
Exec @hr=sp_OAMethod @Object, 'responseText', @json OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @Object

INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'

SELECT  *
FROM OPENJSON((select Json_table from @json), N'$')
WITH (
      SampleValue nvarchar(max) '$.rates.GBP' ,
      SampleDate DATE '$.date'
      ) ;

    /*DECLARE @SQL NVARCHAR(MAX) =
    'SELECT  *  FROM OPENJSON((select Json_table from @json), N''$'') WITH (           SampleValue  nvarchar(max)  ''$.rates.GBP'',  SampleDate DATE ''$.date'' ) ;'

 select @SQL;
 EXEC sp_executesql @SQL;

      DECLARE @SQLstring NVARCHAR(MAX) =
'SELECT  *
FROM OPENJSON((select Json_table from @json), N''$'') WITH (
      SampleValue nvarchar(max) '+ ''''+ @param + ''''+ ' ,
      SampleDate DATE'  + ''''+ '$.date'  + '''' + ' ) ;'

      select @SQLstring ;
EXEC sp_executesql @SQLstring ;*/

EXEC sp_OADestroy @Object

It looks like there were two issues with your dynamic SQL approach:

You didn't properly escape the single quotes in the @Param variable
You can't access a table variable inside the scope of a dynamic SQL statement

For details on this, and workarounds, see Erik Darling's article Starting SQL: Dynamic SQL And Temporary Objects

With those things addressed, here's a slimmed down version of the code that demonstrates how the dynamic SQL can work, using a temporary table instead of a table variable.
CREATE TABLE #json
(
    Json_Table nvarchar(max)
);

DECLARE @Param varchar(1000),
        @Symbol char(3),
        @Sql nvarchar(max);

SET @Symbol ='GBP';
SET @Param = '$.rates.' + @Symbol;
SET @Sql = N'
SELECT  *
FROM OPENJSON((SELECT Json_Table FROM #json), N''$'')
WITH
(
    SampleValue nvarchar(max) ''' + @Param + ''',
    SampleDate date ''$.date''
);
';

INSERT INTO #json
    (Json_Table)
SELECT
    N'{"base":"USD","rates":{"GBP":0.7739357155},"date":"2020-10-15"}';

EXEC sys.sp_executesql @SQL;

See an interactive example of this at the db<>fiddle link.


SQL Server Index Fragmentation When should you rebuild an index? vs reorganize?

What index fragmentation value is considered healthy?

Rebuilding an index takes more CPU than reorganizing it.  It locks the database so that has to taken into account.  Indexes should be rebuilt when the fragmentation is more than 40% or so.  After that it becomes too slow and cumbersome for the server to reorganize.  You should reorganize an index when the fragmentation is roughly 10%-40%.  So healthy index is basically less than 10% fragmentation, little bit less than 10% perhaps.

These are my rules of thumb and they have worked for me, but they are guidelines.  So many things change the performance so you need to find out what woorks for you in your environment.

I think that you can rebuild an index online nowadays, at least if you use the Enterprise version of SQL Server, the keyword ONLINE if I am not mistaken.


Force to cache SQL query exection plan I have a problem with long execution of select query first time in the morning. I guess it is because this query is not cached and SQL Server is caching it. Other few possible causes were checked and set aside.

I would like to make an stored procedure that will execute each 8 a. m. or each few hours, for example, and cache my sql query.

I can't figure out does this method to fight with long performance in first time is fine? How else I can force to cache my query?

You could force the SQL Server instance to cache a query plan for the statement or procedure that you think is missing from the query plan cache. Or you could verify that it is indeed a query plan cache issue or not.

Caching a Query Plan

This is accomplished with the stored procedure sp_create_plan_guide (Transact-SQL)

sp_create_plan_guide takes the following parameters:

sp_create_plan_guide [ @name = ] N'plan_guide_name'
    , [ @stmt = ] N'statement_text'
    , [ @type = ] N'{ OBJECT | SQL | TEMPLATE }'
    , [ @module_or_batch = ]
      {
                    N'[ schema_name. ] object_name'
        | N'batch_text'
        | NULL
      }
    , [ @params = ] { N'@parameter_name data_type [ ,...n ]' | NULL }
    , [ @hints = ] { N'OPTION ( query_hint [ ,...n ] )'
                 | N'XML_showplan'
                 | NULL }

Please read the online manual on how to use the various parameters.

Example on how you could cache the statement of a stored procedure:

exec sp_create_plan_guide
    @name = N'Forced_Plan_Guide_For_MyProc',
    @stmt = N'SELECT pi.product_name FROM product.items as pi WHERE pi.product_id = @product_id',
    @type = N'OBJECT',
    @batch_or_module = N'MyProc', --name of your procedure
    @params = NULL,
    @hints = N'OPTION (OPTIMIZE FOR (@product_id > 1000)';

This would create a query plan for the procedure named MyProc and optimize the query plan for all product.items which have a pi.product_id greater than 1000.

This is just an example on how to create a query plan for a procedure. It is slightly different for a stand-alone SQL Statement.

You can then edit this into a scheduled job and let it run once in the morning or whatever your preferences are.

Alternatives

The above solution will not result in the data being stored in SQL Server's data cache. You might be encountering an issue with data cache and not actually with the plan cache.

Check out Brent Ozar's sp_BlitzCache stored procedure to give your SQL Server's cache a quick check.

  Disclosure: Not affiliated with Brenz Ozar's company


How can I send HTTP requests from SQL Server 2012 to a non-.Net application? I know basic only concepts (DDL,DML queries) of the SQL Server 2012. I explain the workflows of both my application and the database below.

Workflow of the application:

I build web application in Java. Whenever it receives an HTTP request, it fetches data from the database, does some computations on the data and the computed data will be stored in another database.

Before explaining the database workflow, I will explain what a database modification is from my point of view: If any operation happens like creating new tables, data modification, data deletion in tables, etc. indirectly all these are effects on the database.

Workflow of the database:

I have SQL Server 2012. Whenever the database is modified, it will send an HTTP request to the application. The Application runs on an Apache Tomcat server. Once the application receives the HTTP request, it continues that individual workflow.

If you understand my question, we are not sending HTTP requests from a browser (i.e. end-users can't access this application), we are sending HTTP request from SQL Server.

I hope you understand what my question is.

I read some tutorials for this, but I didn't get anything.

Can anyone suggest the correct way of doing this?

You send a request, when you need something back. If you want to let someone to know something, but do not need anything back you notify with a notification.

There is such a feature called SQL Server Service Broker. It allows communitaion over boundaries of different systems and guarantees consistency and timing of the messages.

Further reading:
 - SQL Server Service Broker  on MSDN
 - SQL Server Service Broker Explained


Ideal settings for "read only" table I'm building an application where a certain group of programmatically-created Azure SQL Server tables will only ever be read from.

The app will create a new table, insert a bunch of data, and then serve queries from that data. After the initial inserting the data will never be changed in any way.

Given this usage pattern, are there efficiencies I could take advantage of when setting up such a table, or the indexes on it?

Rebuild indexes with a FILLFACTOR 100 so pages are packed as tight as possible.
Update statistics on the table WITH FULLSCAN so the query optimizer has the most complete information possible to generate a query plan.


Alternative ways to find tables with specific storage engine? I have a VPS with three accounts and 5 db's. Recently I added MySQLTuner. All of my tables are MyISAM, but the results MySQLTuner are showing that I have 14 tables that use InnoDB.

[--] Data in MyISAM tables: 257M (Tables: 618)
[--] Data in InnoDB tables: 560K (Tables: 14)

I looked all of my databases on phpMyAdmin and I couldn't find those tables.

Is there any way to find them?

Login as root@localhost and query information_schema.tables by Storage Engine

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB';

To locate the InnoDB tables that are belong to you, run this

SELECT table_schema,table_name
FROM information_schema.tables
WHERE engine='InnoDB'
AND table_schema NOT IN
('information_schema','performance_schema','mysql');

GIVE IT A TRY !!!


How to migrate a huge application to CONCAT_NULL_YIELDS_NULL ON Finally, none of our customers are using SQL Server 2005 anymore, so we can drop support for it and start using all those awesome "new" SQL Server 2008 features (indexed views, filtered indexes, query notifications, ...) in our applications.

Or so we thought.

Unfortunately, those new features require CONCAT_NULL_YIELDS_NULL to be set to ON---not only when using the new features but basically whenever the database is accessed: If I create an indexed view V on tables A and B, CONCAT_NULL_YIELDS_NULL must be set to ON not only when working with (new) view V, but also when inserting/updating values in (old) tables A and B.

Now we have a huge legacy application with a lots of SQL statements scattered all over the code: A quick search for SELECT over the code base yields ~10.000 results. The DB layer executes SET CONCAT_NULL_YIELDS_NULL OFF after opening the DB connection, so all SQL statements were written under the assumption that A + NULL = A rather than A + NULL = NULL.

Is there any "slow upgrade path" to move from CONCAT_NULL_YIELDS_NULL OFF to ON without a one-time-effort to check, rewrite and test Every. Single. SQL statement?

I was thinking about parsing "old" SQL statements in the DB layer and replacing expression1 + expression2 with ISNULL(expression1, '') + ISNULL(expression2, ''), but for that I'd need to (1) implement a parser for the subset of T-SQL that we currently use and (2) determine the result type of T-SQL expressions, so that I know whether + means string concatenation or addition. Both should be doable but sound like a huge amount of non-trivial work.

Is there any other option that I've missed? Or did, by any chance, someone already implement a converter from non-CONCAT_NULL_YIELDS_NULL-ON-compatible SQL statements to CONCAT_NULL_YIELDS_NULL-ON-compatible SQL statements?

Writing a parser to change specific bits of your queries would indeed require some effort. Adding just a single line to every one of them, on the other hand, seems a relatively easy task. So, you could mass-update all existing SQL queries/batches in your application with this line added to the beginning of each batch:

SET CONCAT_NULL_YIELDS_NULL OFF;

and re-configure the DB layer to issue SET CONCAT_NULL_YIELDS_NULL ON; (or just omit it if ON is the default) on connection from then on.

That would allow you to review/modify each existing query in your own time while forcing all new queries, if any, to comply with the new behaviour.

Each existing query, once reviewed, would be stripped off the added SET statement. You could review the queries in groups based on the objects they work with, so that new features could be rolled out on those objects sooner (because all the relevant queries would then be executing in the context of CONCAT_NULL_YIELDS_NULL = ON).


How do I order the columns of a SQL statement (not the rows)? I'm an ERP analyst, and when I find something I haven't seen in a long time, I need to jog my memory to know which column contains the data I want. I need to examine the data to do this, so a query against USER_TAB_COLS isn't sufficient. I normally just do a SELECT * and start scrolling.

The tables often have over 100 columns, and more than a handful have over 200. It's mentally exhausting to scroll to the right in my GUI tools to scan manually. If I could alphabetize the columns, I'd have an easier time finding what I need.

I came up with this solution but would definitely welcome improvements and other answers.

CREATE SEQUENCE column_sort_seq
   START WITH 0
   MINVALUE 0
   MAXVALUE 9999
   INCREMENT BY 1
   NOCACHE
   CYCLE;

CREATE OR REPLACE PACKAGE column_sort_api IS
   FUNCTION sort_query_columns(
      sql_ IN VARCHAR2) RETURN VARCHAR2;
   PROCEDURE drop_all_temporary_views;
END column_sort_api;
/

CREATE OR REPLACE PACKAGE BODY column_sort_api IS
   temporary_view_prefix_ CONSTANT VARCHAR2(30) := 'COLUMN_SORT_TEMP_VIEW_';

   FUNCTION get_next_temporary_view_name_ RETURN VARCHAR2;

   FUNCTION sort_query_columns(
      sql_ IN VARCHAR2) RETURN VARCHAR2
   IS
      PRAGMA AUTONOMOUS_TRANSACTION;
      temporary_view_name_ VARCHAR2(30) := get_next_temporary_view_name_;
      modified_sql_ VARCHAR2(32767);
      ddl_ VARCHAR2(32767);
      column_concatenation_ VARCHAR2(32767);
   BEGIN
      modified_sql_ := RTRIM(TRIM(sql_), ';');
      ddl_ := 'CREATE OR REPLACE VIEW ' || temporary_view_name_ || ' AS '
           || 'SELECT * FROM (' || modified_sql_ || ')';
      EXECUTE IMMEDIATE ddl_;
      SELECT LISTAGG(LOWER(column_name), ', ') WITHIN GROUP (ORDER BY column_name ASC)
        INTO column_concatenation_
        FROM user_tab_cols
       WHERE table_name = temporary_view_name_;
      ddl_ := 'DROP VIEW ' || temporary_view_name_;
      EXECUTE IMMEDIATE ddl_;
      RETURN 'SELECT ' || column_concatenation_ || ' FROM (' || modified_sql_ || ')';
      COMMIT;
   END sort_query_columns;

   FUNCTION get_next_temporary_view_name_ RETURN VARCHAR2
   IS
      view_name_index_ NUMBER;
      record_count_ NUMBER;
      view_name_ VARCHAR2(30);
      attempts_ NUMBER := 0;
   BEGIN
      LOOP
         IF attempts_ > 10000 THEN
            drop_all_temporary_views;
         END IF;
         view_name_index_ := column_sort_seq.NEXTVAL;
         view_name_ := temporary_view_prefix_ || TO_CHAR(view_name_index_, 'FM0000');
         SELECT COUNT(*)
           INTO record_count_
           FROM user_views
          WHERE view_name = view_name_;
         attempts_ := attempts_ + 1;
         EXIT WHEN record_count_ = 0;
      END LOOP;
      RETURN view_name_;
   END get_next_temporary_view_name_;

   PROCEDURE drop_all_temporary_views
   IS
   BEGIN
      FOR rec_ IN (
         SELECT view_name
           FROM user_views
          WHERE view_name LIKE temporary_view_prefix_ || '%'
      ) LOOP
         EXECUTE IMMEDIATE 'DROP VIEW ' || rec_.view_name;
      END LOOP;
   END drop_all_temporary_views;

END column_sort_api;
/

It creates a view, then examines the metadata of that view to build a new query. To support a limited amount of concurrency, it works with a set of 10,000 possible view names. In case the system crashes, it checks to make sure the view doesn't exist before using that name. To clear any possible lint, there's a feature to remove all the views.

Here it is in action:

https://livesql.oracle.com/apex/livesql/s/hvqxaqn9pwi8yk276bg4zt5gn

SELECT column_sort_api.sort_query_columns('SELECT * FROM hr.employees WHERE rownum <= 5;')
  FROM DUAL;

Output:

SELECT commission_pct, department_id, email, employee_id, first_name, hire_date, job_id, last_name, manager_id, phone_number, salary FROM (SELECT * FROM hr.employees WHERE rownum <= 5)

Using that output is a matter of copying the result and pasting it back into the editor.

If you can improve this answer, I would like to know if it's possible to bypass the final copy-and-paste step and produce the data via a pipelined function.


Why do linked servers have a limitation of 10 branches in a CASE expression? Why does this CASE expression:

SELECT CASE column
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        ... c -> i
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END [col]
FROM LinkedServer.database.dbo.table

Produce this result?

  Error message: Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Msg 125, Level 15, State 4, Line 1 Case expressions may only be nested to level 10.

Clearly there isn't a nested CASE expression here, though there are more than 10 "branches."

Another oddity. This inline table-valued function produces the same error:

ALTER FUNCTION [dbo].[fn_MyFunction]
(
     @var varchar(20)
)
RETURNS TABLE
AS
RETURN
(
    SELECT CASE column
            WHEN 'a' THEN '1'
            WHEN 'b' THEN '2'
            ... c -> i
            WHEN 'j' THEN '10'
            WHEN 'k' THEN '11'
        END [col]
    FROM LinkedServer.database.dbo.table
)

But a similar multi-statement TVF works fine:

ALTER FUNCTION [dbo].[fn_MyFunction]
(
    @var varchar(20)
)
RETURNS @result TABLE
(
    value varchar(max)
)
AS
BEGIN
    INSERT INTO @result
    SELECT CASE column
            WHEN 'a' THEN '1'
            WHEN 'b' THEN '2'
            ... c -> i
            WHEN 'j' THEN '10'
            WHEN 'k' THEN '11'
        END [col]
    FROM LinkedServer.database.dbo.table

RETURN;
END

Clearly there isn't a nested CASE expression here.

Not in the query text, no. But the parser always expands CASE expressions to the nested form:

SELECT CASE SUBSTRING(p.Name, 1, 1)
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        WHEN 'c' THEN '3'
        WHEN 'd' THEN '4'
        WHEN 'e' THEN '5'
        WHEN 'f' THEN '6'
        WHEN 'g' THEN '7'
        WHEN 'h' THEN '8'
        WHEN 'i' THEN '9'
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END
FROM AdventureWorks2012.Production.Product AS p

That query is local (no linked server) and the Compute Scalar defines the following expression:

This is fine when executed locally, because the parser does not see a nested CASE statement over 10 levels deep (though it does pass one on to the later stages of local query compilation).

However, with a linked server, the generated text may be sent to the remote server for compilation. If that is the case, the remote parser sees a nested CASE statement more than 10 levels deep and you get error 8180.

  Another oddity. This inline table-valued function produces the same
  error

The in-line function is expanded in-place into the original query text, so it's no surprise the same error results with the linked server.

  But a similar multi-statement TVF works fine

Similar, but not the same. The msTVF involves an implicit conversion to varchar(max), which happens to prevent the CASE expression being sent to the remote server. Because the CASE is evaluated locally, a parser never sees an over-nested CASE and there is no error. If you change the table definition from varchar(max) to the implicit type of the CASE result - varchar(2) - the expression is remoted with the msTVF and you will get an error.

Ultimately, the error occurs when an over-nested CASE is evaluated by the remote server. If the CASE is not evaluated in the Remote Query iterator, no error results. For example, the following includes a CONVERT that is not remoted, so no error occurs even though a linked server is used:

SELECT CASE CONVERT(varchar(max), SUBSTRING(p.Name, 1, 1))
        WHEN 'a' THEN '1'
        WHEN 'b' THEN '2'
        WHEN 'c' THEN '3'
        WHEN 'd' THEN '4'
        WHEN 'e' THEN '5'
        WHEN 'f' THEN '6'
        WHEN 'g' THEN '7'
        WHEN 'h' THEN '8'
        WHEN 'i' THEN '9'
        WHEN 'j' THEN '10'
        WHEN 'k' THEN '11'
    END
FROM SQL2K8R2.AdventureWorks.Production.Product AS p