Domain Account or Windows account for Transactional Replication

Short Answer: Nothing at all is wrong with a domain account. When all the computers participating are in the same domain, go that way. Easier to manage and maintain and secure.

Your confusion here lies in the way the documents you are looking at are structured. In the document you reference in the comments above you see this section:

  If your replication topology includes computers that are not in the
  same domain or are in domains that do not have trust relationships
  with each other, you can use Windows Authentication or SQL Server
  Authentication for the connections made by agents (For more
  information about domains, see the Windows documentation). It is
  recommended as a security best practice that you use Windows
  Authentication.

  To use Windows Authentication:

  Add a local Windows account (not a domain account) for each agent at
  the appropriate nodes (use the same name and password at each node).
  For example, the Distribution Agent for a push subscription runs at
  the Distributor and makes connections to the Distributor and
  Subscriber. The Windows account for the Distribution Agent should be
  added to the Distributor and Subscriber.

  Ensure that a given agent (for example the Distribution Agent for a
  subscription) runs under the same account at each computer.

The article isn't saying "if you want to use a Domain account, we suggest you use a local account" - but I totally see why anyone would read it that way.

The article is saying, instead, "Use a domain account where you can. IF your computers are not in the same domain, here's a little trick you can use."

That trick, sometimes referred to as pass through authentication has you create an account on each computer. Same name. Same password. By doing that - windows will pass the credentials through and, for all intents and purposes of this conversation, the apps (SQL and Replication objects) won't realize it isn't really a "domain account" - and it will just work.

If, however, the computers are on the same domain? Just use the domain account.

If you go back to that first link you commented with - you'll notice the "if your replication topology includes computers that are not in the same domain..." line isn't bulleted or indented. Really the bullet about the local to each should be indented off of that.


Database normalization: Describing trait - foreign key to table, or varchar field with value?

You should use a lookup table

Not all clients will use the ENUM in the application
You will get a reporting or MIS or Excel app connecting at some point
How can do "NOT EXIST" otherwise?
You will be asked this
You won't know about client enum changes
Strings are inefficient compared to a tinyint, especially when you need to index it for your WHERE clause
The data and database will outlive the client application

Also, see these:

"Proper Use of Lookup Tables"
"INT or CHAR for a Type Field"
"Advantages and Disadvantages to using ENUM vs Integer types?"


Figuring out if SQL Server database needs to be defragmented

Logical Scan Fragmentation  Percentage of out-of-order pages returned from scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order page is a page for which the next physical page allocated to the index is not the page pointed to by the next-page pointer in the current leaf page.

Extent Scan Fragmentation   Percentage of out-of-order extents in scanning the leaf pages of an index. This number is not relevant to heaps. An out-of-order extent is one for which the extent that contains the current page for an index is not physically the next extent after the extent that contains the previous page for an index.

Did you try this page: DBCC SHOWCONTIG?


Unable to update a record with a trigger using a dynamic column on a PostgreSQL DB

In PostgreSQL, every UPDATE will create a new row version. An attempt to modify the old row version in a BEFORE trigger won't work.

The new table row version that is about to be added to the database is available in the NEW variable of the trigger. You have to modify NEW in order to modify the new table row.

Dynamic modification of NEW is what you need, but this is hard to impossible in PL/pgSQL.

I suggest that you use a PL/Python trigger instead:

CREATE OR REPLACE FUNCTION update_lastchange() RETURNS trigger
   LANGUAGE plpythonu AS
'TD["new"][TD["args"][0] + "_lastchange"] = plpy.execute("SELECT current_timestamp")[0]["current_timestamp"]
return "MODIFY"';

CREATE TRIGGER update_lastchange
   BEFORE UPDATE ON content
   FOR EACH ROW EXECUTE FUNCTION update_lastchange('cnt');

What does the trigger do?

It runs SELECT current_timestamp on the database to get the current timestamp and retrieves that value from the result set (the column labeled current_timestamp from the first row (row 0) of the result set).

Then it stores that value in the appropriate entry of the TD["new"] mapping that contains NEW in PL/Python.

Finally, "MODIFY" is returned to indicate that the UPDATE should proceed.


Unique index updates and statistics row modification counters

SQL Server always uses the Split, Sort, and Collapse combination of operators when maintaining a unique index as part of an update that affects (or might affect) more than one row.
Working through the example in the question, we could write the update as a separate single-row update for each of the four rows present:
-- Per row updates
UPDATE dbo.Banana SET pk = 2 WHERE pk = 1;
UPDATE dbo.Banana SET pk = 3 WHERE pk = 2;
UPDATE dbo.Banana SET pk = 4 WHERE pk = 3;
UPDATE dbo.Banana SET pk = 5 WHERE pk = 4;

The problem is that the first statement would fail, since it changes pk from 1 to 2, and there is already a row where pk = 2. The SQL Server storage engine requires that unique indexes remain unique at every stage of processing, even within a single statement. This is the problem solved by Split, Sort, and Collapse.
Split
The first step is to Split each update statement into a delete followed by an insert:
DELETE dbo.Banana WHERE pk = 1;
INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W');

DELETE dbo.Banana WHERE pk = 2;
INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X');

DELETE dbo.Banana WHERE pk = 3;
INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y');

DELETE dbo.Banana WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

The Split operator adds an action code column to the stream (here labelled Act1007):

The action code is 1 for an update, 3 for a delete, and 4 for an insert.
Sort
The split statements above would still produce a false transient unique key violation, so the next step is to sort the statements by the keys of the unique index being updated (pk in this case), then by the action code. For this example, this simply means that deletes (3) on the same key are ordered before inserts (4). The resulting order is:
-- Sort (pk, action)
DELETE dbo.Banana WHERE pk = 1;
DELETE dbo.Banana WHERE pk = 2;
INSERT dbo.Banana (pk, c1, c2) VALUES (2, 'A', 'W');
DELETE dbo.Banana WHERE pk = 3;
INSERT dbo.Banana (pk, c1, c2) VALUES (3, 'B', 'X');
DELETE dbo.Banana WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (4, 'C', 'Y');
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

Collapse
The preceding stage is enough to guarantee the avoidance of false uniqueness violations in all cases. As an optimization, Collapse combines adjacent deletes and inserts on the same key value into an update:
-- Collapse (pk)
DELETE dbo.Banana WHERE pk = 1;
UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2;
UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3;
UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4;
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');

The delete/insert pairs for pk values 2, 3, and 4 have been combined into an update, leaving a single delete on pk = 1, and an insert for pk = 5.
The Collapse operator groups rows by the key columns, and updates the action code to reflect the collapse outcome:

Clustered Index Update
This operator is labelled as an Update, but it is capable of inserts, updates, and deletes. Which action is taken by the Clustered Index Update per row is determined by the value of the action code in that row. The operator has an Action property to reflect this mode of operation:

Row modification counters
Note that the three updates above do not modify the key(s) of the unique index being maintained. In effect, we have transformed updates to the key columns in the index into updates of the non-key columns (c1 and c2), plus a delete and an insert. Neither a delete nor an insert can cause a false unique-key violation.
An insert or a delete affects every single column in the row, so statistics associated with every column will have their modification counters incremented. For update(s), only statistics with any of the updated columns as the leading column have their modification counters incremented (even if the value is unchanged).
The statistics row modification counters therefore show 2 changes to pk, and 5 for c1 and c2:
-- Collapse (pk)
DELETE dbo.Banana WHERE pk = 1;                         -- All columns modified
UPDATE dbo.Banana SET c1 = 'A', c2 = 'W' WHERE pk = 2;  -- c1 and c2 modified
UPDATE dbo.Banana SET c1 = 'B', c2 = 'X' WHERE pk = 3;  -- c1 and c2 modified
UPDATE dbo.Banana SET c1 = 'C', c2 = 'Y' WHERE pk = 4;  -- c1 and c2 modified
INSERT dbo.Banana (pk, c1, c2) VALUES (5, 'D', 'Z');    -- All columns modified

Note: Only changes applied to the base object (heap or clustered index) affect statistics row modification counters. Non-clustered indexes are secondary structures, reflecting changes already made to the base object. They do not affect statistics row modification counters at all.
If an object has multiple unique indexes, a separate Split, Sort, Collapse combination is used to organize the updates to each. SQL Server optimizes this case for nonclustered indexes by saving the result of the Split to an Eager Table Spool, then replaying that set for each unique index (which will have its own Sort by index keys + action code, and Collapse).
Effect on statistics updates
Automatic statistics updates (if enabled) occur when the query optimizer needs statistical information and notices that existing statistics are out of date (or invalid due to a schema change). Statistics are considered out of date when the number of modifications recorded exceed a threshold.
The Split/Sort/Collapse arrangement results in different row modifications being recorded than would be expected. This, in turn, means that a statistics update may be triggered sooner or later than would be the case otherwise.
In the example above, row modifications for the key column increase by 2 (the net change) rather than 4 (one for each table row affected), or 5 (one for each delete/update/insert produced by the Collapse).
In addition, non-key columns that were logically not changed by the original query accumulate row modifications, which may number as many as double the table rows updated (one for each delete, and one for each insert).

The number of changes recorded depend on the degree of overlap between old and new key column values (and so the degree to which the separate deletes and inserts can be collapsed). Resetting the table between each execution, the following queries demonstrate the effect on row modification counters with different overlaps:
UPDATE dbo.Banana SET pk = pk + 0; -- Full overlap

UPDATE dbo.Banana SET pk = pk + 1;

UPDATE dbo.Banana SET pk = pk + 2;

UPDATE dbo.Banana SET pk = pk + 3;

UPDATE dbo.Banana SET pk = pk + 4; -- No overlap


Ms-access ODBC calls for linked table behaviour

The only way to get complete control over the query for ODBC is to create them as pass-through queries.

In that mode, Access will not touch them and the SQL will be passed verbatim to the server (so your queries will need to be written in that server's particular SQL syntax or you'll get errors).

In ODBC mode, Access will decompose queries that are bound to controls into batches so it never returns more results than it need to display (that is what you see: it will decompose queries based on the primary key to return specific records only).

So you have to be careful of what you wish: if you optimise your queries using pass-through, you will get less calls, but all the data will be returned.

On the other hand, if you leave Access do the querying for you, it may be chattier, but the net performance may be better since less data may be returned more quickly.

You need to test both and choose what works best depending on the exact query and how it is used.


What are your use-cases for HandlerSocket?

I find it interesting that Percona says that HandlerSocket is not that popular. In fact, it has bugs when writing operations interferes with open HANDLER structures.

What I also find appalling is that the concept of the HandlerSocket (known back then as HANDLER goes way back to MySQL 4.0.3 for both MyISAM and InnoDB. There were only provisions for reading at that time.

The basic usage of HANDLER is also in the book MySQL Reference Manual : Documentation From the Source, Section 6.4.2 Pages 512,513 (I have the book right here next to me)

Question remains : What purpose does the HandlerSocket serve ???

Imagine Facebook engineers trying to read a value in a MyISAM table (Facebook hates MyISAM, BTW) that is heavily trafficked. We all know that MyISAM performs a full table lock for any DML or SELECT performed. What you wanted to do is adjust a single column in a specfic row in that table (via SQL) but you need to know that data right now before SELECT queries lock it down. Do you want to wade through the humongous number of DB Connections hitting the one table you need just to change a single value? You could bypass these waiting SELECTs with no locking using the HANDLER syntax. You can do the same to InnoDB tables and bypass MVCC (MultiVersioning Concurrecy Control) and row-level locks as well.

Only in a high-traffic, high-read environment would you need to takes such a risk, especially if finding out data RIGHT NOW was the highest priority.

If you really want to know what production environments actually use it, I would write Percona directly in the blogs begging for that answer. Since I have never seen coding examples with the HANDLER (today HandlerSocket) do things other than reading, it would only serve the purposes of big MySQL installations like Facebook.

CAVEAT

When you look at the syntax for opening tables, opening indexes and traversing rows, it resembles some programming languages I coded in back in the late 1980's.

I am referring to the languages DBase, Clipper, FoxPro and Visual FoxPro. DBase was the grandfather of all these products. Now, before you start laughing and falling out of your chair, DBASE STILL EXISTS AND SO DOES VISUAL FOXPRO !!! I personally know one person that still codes in FoxPro and Visual FoxPro !!! OK, now you can fall out of your chair, crying in the fetal position. Still don't believe me ??? How about some sample code ???

Enough ranting on old data handling programming languages that resembles hierglyphics !!!


how to structure availability relationships

IMO you overcomplicate things. Just add an available DATE field to the candidate table. Then queries will be simpler and more performant.

SELECT * FROM candidate WHERE available < NOW() ORDER BY available DESC;


How do I create a user-defined aggregate function?

I don't know if there is way to define a new aggregate function, not without messing with MySQL source code.

But if your numbers are all positive, you may well derive from the arithmetic identity:

log( product( Ai ) ) = sum( log( Ai ) )

that you can use EXP(SUM(LOG(x))) to calculate PRODUCT(x). Test in SQL-Fiddle:

SELECT EXP(SUM(LOG(a))) AS product
FROM t ;

SELECT col, EXP(SUM(LOG(a))) AS product
FROM t
GROUP BY col ;

When the data can have 0s, it gets a bit more complicated:

SELECT (NOT EXISTS (SELECT 1 FROM t WHERE a = 0))
       * EXP(SUM(LOG(a))) AS p
FROM t
WHERE a > 0 ;

SELECT d.col,
       (NOT EXISTS (SELECT 1 FROM t AS ti WHERE ti.col = d.col AND ti.a = 0))
       * COALESCE(EXP(SUM(LOG(t.a))),1)  AS p
FROM
    ( SELECT DISTINCT col
      FROM t
    ) AS d
  LEFT JOIN
    t  ON  t.col = d.col
       AND t.a > 0
GROUP BY d.col ;

Tested at SQL-Fiddle

For other DBMS, that do not have MySQL's auto-conversion of boolean values to integers, the

(NOT EXISTS (SELECT ...))

should be replaced with:

(CASE WHEN EXISTS (SELECT 1...) THEN 0 ELSE 1 END)

Specifically for Oracle, a few more changes will be needed, without changing the logic of the answer, only because Oracle does not follow strict ANSI standard in some areas. Tested at SQL-Fiddle-2


If you have a schema already in place, you should use mysqldump options to create the dump of the data to perform in the inserts only

mysqldump -h... -u... -p... --no-create-info --databases ... > MySQLData.sql

You should also make sure to raise the bulk_insert_buffer_size (default is 8M) on all DB Servers to accommodate large extended inserts. This will also help LOAD DATA INFILE if loading non-empty tables.

You are going to have to also adjust your max_allowed_packet (default is 1M).

Try these settings for starters:

[mysqld]
max_allowed_packet=256M
bulk_insert_buffer_size=256M

UPDATE 2011-10-11 06:53 EDT

Regardless of how beefy the hardware is, how much RAM you have allocated, how well tuned the OS is, and how current the version of MySQL is, MySQL will only perform as well as it is configured.

Example: If you have a swimming pool that can hold 10,000 gallons of water, you have a truck holding 10,000 gallons of water, and the hose on the truck is only as big as a straw, you can only push but so much water through the straw to fill the pool. Getting a bigger truck or getting a faster water pump simply will not improve things. You must exchange the hose for a much bigger hose to accommodate more water, thus, more throughput.

In like fashion, MySQL 5.5 out-of-the-box does not come fully tuned.

Example #1: MySQL 5.5 comes with semisynchronous replication. By default, it is disabled. You must do the two step process of starting mysql, runnning INSTALL PLUGIN on master and slave modules, shutdown mysql, add timing and activation options to my.cnf for the semisych features, then starting up mysql for the second time. Only then will semisync replication work.

Example #2: The bulk insert buffer by default is 8M. The bulk insert buffer will not grow because of the presence of any specific hardware or software. It stays 8M until you increase it. It is possible to increase it by either adding it to my.cnf and restarting mysql or running SET bulk_insert_buffer_size = 268435456; to set it to 256M within a session and then load the mysqldump within that same session.

Example #3: MySQL 5.5 is fully capable of engaging multiple CPUs. By default, the features for engaging multiple CPUs is disabled. They require tuning because although MySQL 5.5 is multicore ready, MySQL 5.5 is only as multithreaded as you configure it.

Conclusion: You must configure MySQL to recognize that it has beefy hardware, more RAM, and a cooperative OS at its disposal.


Scripting all of available databases to .sql files containing schema+data+users with single command

I would personally just do a backup for each database, and then attach them to the new server. Just remember to use

EXEC sp_change_users_login 'Auto_Fix', 'username'

for fixing user accounts.

The Wizard approach sounds doable.

If you need another approach for scripting DDL, the scptxfr.exe tool that comes with earlier versions of SQL Server still works with 2008. The following will connect to a local SQL Server instance and create a DDL script for my_database:

scptxfr /s .\SQLExpress /d my_database /I /f database_script.sql /O /H

Without programming, it is going to be difficult to get all the data out in scripts.


The DMV sys.dm_exec_sessions has column host_name which is the computer at the client end of the connection.  You can join that to your posted query through the session_id.


Postgres - Querying Min, Max and getting associated columns data specific to those result of those rows

I would recommend you do something like this - and don't go down the route of having a row per month.

What happens when you have 5 (10...15... x) years' data?

This is superior IMHO. You can always use the CROSSTAB table function, but I would recommend against it.

SELECT
  esite,
  MIN(evalue),
  MAX(evalue),
  AVG(evalue)::INT,
  EXTRACT(MONTH FROM edate) AS emonth,
  CASE
    WHEN EXTRACT(MONTH FROM edate) = 1 THEN 'January'
    WHEN EXTRACT(MONTH FROM edate) = 2 THEN 'February'
    -- <... fill in rest of months here ...>
  END AS litMonth
FROM events
GROUP BY esite, emonth, litMonth
ORDER BY esite, emonth -- , min, max, avg;

This gives the result:

esite;     min;   max;   avg;    emonth;    litmonth
----------------------------------------------------
    1;       7;    66;    24;     1;         January
    1;      15;    61;    37;     2;        February
    2;      40;    90;    61;     1;         January
    2;      23;    70;    50;     2;        February

Note, that I have CAST the AVG as an INTEGER - you can of course ROUND this off to whatever you like. You can add EXTRACT(YEAR  FROM edate) AS eyear, after the EXTRACT(MONTH... for more clarity and a more elegant result.


It's not common but it can work.  Under certain circumstances (and I'm not sure of the rules) you can update a column from a CTE or subquery.  My guess is that it's very similar to being able to update a view and probably has the same rules.

The times I've done it it's quite a bit faster than joining back to the original table and updating it.


Any answer to your question will be subjective and somewhat high level (since there's not a lot of details to go off of).
To clarify the purposes of a Document-oriented database system, it's purpose is for storing non-relational or loosely-structured data. It's not necessarily for actually directly storing files (such as JSON and CSV files) themselves. Rather it'll store loosely-structured data (such as what JSON can be used for) in it's own system (with it's own format under the hood).
When to choose an RDBMS vs a Document-oriented Database System (or a NoSQL database system)?
Choose a RDBMS if your data exhibits most of the following:

Strongly structured, i.e. concretely defined as opposed to loosely defined e.g., the database will have Artists, Albums, and Songs. The structure of each object (entity) type will be mostly similar across each instance of that object type
Each instance of a particular type of data shares mostly the same attributes e.g., all songs will have a Title, Artist, Song Length, and may have lyrics or a genre
Relationships are easily definable among the data, e.g. A Song belongs to one-or-many albums, one-or-many albums belong to an Artist
Your use cases involve various querying that'll involve filtering by different attributes of the data, e.g. You want to know how many Albums are particular Song is on? Or how many new Songs were created in 2019 by Artists of specific Genres? etc

Choose a Document-oriented / NoSQL database system if your data exhibits most of the following:

There isn't a strong structure to the objects and their properties

Or the structure will be vary various or highly changing over time

If you don't need to answer more complicates questions by querying the data via it's structure and relationship with itself

E.g. if some of your Songs will only have Titles, others will only have Genres, and some don't have Artists at all. Sometimes there will be Artists and sometimes there won't be any Artists, and you want to store the historical information about some Artists and their families sometimes. And you want to store all Albums and all Songs in the same place as the Artist they belong to (so when you read back from the database, it's all or nothing).

It sounds like you have a basic relational data use case (by inferring from your example), and while most modern types of database systems can probably handle your use case, the best fit is likely a Relational Database Management System (RDBMS). Examples of an RDBMS are SQLite, PostgreSQL, Microsoft SQL Server, Oracle PL/SQL, MySQL.
Each of the aforementioned RDBMS have their own pros and cons, and features that are available in one system that are lacking in another. Microsoft SQL Server and PostgreSQL probably are the most evolved of the examples, but for what sounds like a very basic use case, any of them would be applicable.
Things you should consider are:

Do you want a free solution or need to use an enterprise level system like Microsoft SQL Server which costs money. (Again, speculating, but your use case is very basic and likely can use of the free solutions available.)
How far do you expect to scale your database?...in other words, in the foreseeable future roughly how big do you expect it to get (data-wise), how many users do you expect will be using it both overall and at a given instance of time, how often will data change inside of it? SQLite is a great basic and free database system which could be perfect for your use case if you don't expect it to scale into a database that is billions of records big and petabytes of data with millions of active users, otherwise a more built out database system like the alternative examples I gave might be the better fit.
What does your current technology stack look like? Certain technologies are better fits and integrate more easily with certain database systems which might drive you to choose PostgreSQL over MySQL, or Microsoft SQL Server over Oracle PL/SQL. (E.g. Microsoft SQL Server works very nicely with Microsoft's technology stack: .NET & C#, etc).
Where do you plan to host the database?...Locally, Mobile Storage, or in the Cloud?...Linux or Microsoft Server? (These will filter down your possible options.)

I don't think any modern solution out there persists the underlying data of the database in JSON or CSV files (though I could be mistaken). Some of the examples I provided above can persist the data in the structured JSON format itself in the database. They also can ingest, digest, store, and export data to / from CSV and JSON files. For example, Microsoft SQL Server has functions for storing, manipulating, and reading back JSON within the database itself. It also can export data into various formats such as CSV.
There are also solutions, like in AWS, where you can store the files as JSON or CSV and then their database technologies utilize another AWS process to load the database from those files, but that's also applicable to other database systems like Microsoft SQL Server.
To directly answer your question (which again this is subjective) is if you're just getting starting with databases, SQLite is a pretty lightweight and easy system to start with. It's free, and is pretty developer friendly for someone who doesn't have a lot of experience writing SQL queries. You can at least get an idea of how databases work while getting started and still in the early phases of your project and could always port it over to any other database system if you choose to later (especially if your use case remains as simple as you described in your question).

For more information on Pros, Cons, and varying features of some of the example Relation Database Management Systems I mentioned above, these articles make for good reads:

SQLite vs MySQL vs PostgreSQL: A Comparison Of Relational Database Management Systems

Difference between Oracle, SQL Server, MySQL and PostgreSQL

PostgreSQL vs. SQL Server (MSSQL) - Extremely Detailed Comparison

To summarize some of the key information above that's relevant:
SQLite: Small footprint, user-friendly, portable BUT limited concurrency & scale, and lack of user management
MySQL: Popularity and ease of use, additional features like replication BUT known limitations (lacks FULL JOINs and other modern features), development of new features has slowed down, larger data concurrency issues
PostgreSQL: SQL compliance, open-source and community-driven, extensible BUT potential memory performance issues
Microsoft SQL Server: Very performant, flexible, lot of built in features out of the box BUT generally costs money (there is a minimalize free version)


How do I combine multiple lines for the same package? (SQL)

I think you just need to add a SUM and I am not sure what the > signs are doing in there.

IF OBJECT_ID('vwPackageRevenue') IS NOT NULL
    DROP VIEW vwPackageRevenue
GO

CREATE VIEW vwPackageRevenue
AS
SELECT (dbo.packageWH.PNAME) AS 'Package Name'
    ,SUM((COSTPERSON * COUNT(GFirstName)))  AS 'Total Revenue'
FROM dbo.packageWH
JOIN dbo.reservationWH ON  dbo.packageWH.P_ID = dbo.reservationWH.P_ID
JOIN guestWH ON dbo.reservationWH.RNUM = dbo.guestWH.RNUM
GROUP BY
dbo.packageWH.PNAME

GO

SELECT *
FROM vwPackageRevenue

Does this work for you?


Duplicate Inserted Row into the new table when running trigger

The statement in your trigger:

INSERT INTO temptable
   (task_name, duration)
SELECT NEW.task_name , NEW.duration
FROM task;

will insert as many rows into temptable as there are currently in task. The SELECT returns one result row for each row in task, and since the SELECT list contains only constants, all these result rows will look the same.

You want to insert a single row, so you should use

INSERT INTO temptable
   (task_name, duration)
VALUES (NEW.task_name , NEW.duration);


In Oracle, empty string '' when assigned to a varchar2 type is NULL for all intents and purposes. In my SQLdeveloper, on a 12c database, the following query results in a (null) for every column. If you display to user a blank (a space, ' ') it would surely be displayed by all tools as a blank.

SELECT NULL, '', CAST('' as varchar2(1)), CAST('' as char(1)), NVL(null,'') FROM dual;

Why Oracle violates the SQL92 standard


Executing stored procedures asynchronously using tasks in SSIS

Instead of SSIS I think you might want to look into SQL Server Service Broker. This lets you send messages asynchronously. See here or here for a start.

MSMQ or RabbitMQ may also be options you should consider.


With innodb_file_per_table configured, the network_type table will exist in a physical file under the DB folder. For example, let's say your table has the following attributes:

Table name somedb.network_type
datadir is /var/lib/mysql

You will find the physical file /var/lib/mysql/somedb/network_type.ibd

That file is the home of all data and index pages in InnoDB. The pre-compiled size of a page is 16K. There is room inside that page for more data, but 16K is the fixed allocation for data and index.

When you ran SELECT data_length FROM information_schema.TABLES  WHERE table_schema = "test";, MySQL simply reported the most granular space InnoDB is using.


Do I need to keep SQL Server Profiler interface open?

Do not use profiler. You can use XEvents or Server Side trace and let it run for certain duration only.

I have written a process to run server side trace and import the results into database.


Auto Increment in MS SQL letter and number

In order to increment groups starting at AA and ending at ZZ, you need to convert the left portion of the number (whatever is above the number of digits you want to keep as integers) into Base 26. You start by truncating the right portion of the number so that you have only the left portion, then you divide by 10 ^ IntegerDigits (e.g. 3 integer digits == 10 ^ 3 == 1000) to get the A_ side, then use modulo on the same 10 ^ IntegerDigits to get the _A side. Those values will get us the offset from the ASCII value for A. For example:

SELECT (123142 / 1000) AS [Truncated],
       (123142 / 1000) / 26 AS [SetsOf26],
       (123142 / 1000) % 26 AS [Remaining],
       CHAR(65) AS [A];

-- Truncated = 123
-- SetsOf26  = 4
-- Remaining = 19
-- A         = A

We can put all of that together in an Inline TVF (for demo purposes, not for using in a computed column in a table) as follows:

CREATE FUNCTION dbo.Base26 (@Base10 INT)
RETURNS TABLE
WITH SCHEMABINDING
AS RETURN
  WITH truncated AS
  (
    SELECT (@Base10 / 1000) AS [Value]
  )
  SELECT @Base10 AS [Actual],
    tr.Value AS [Truncated],
    CHAR(65 + (tr.Value / 26)) AS [1stChar],
    CHAR(65 + (tr.Value % 26)) AS [2ndChar],
    CHAR(65 + (tr.Value / 26))
      + CHAR(65 + (tr.Value % 26))
      + RIGHT('00' + CONVERT(VARCHAR(20), @Base10 % 1000), 3) AS [EndResult]
  FROM truncated tr;
GO

Then we can test with:

SELECT * FROM dbo.Base26(142);
SELECT * FROM dbo.Base26(3142);
SELECT * FROM dbo.Base26(123142);
SELECT * FROM dbo.Base26(123999);

which returns:

Actual   Truncated   1stChar   2ndChar   EndResult
142      0           A         A         AA142

3142     3           A         D         AD142

123142   123         E         T         ET142

123999   123         E         T         ET999

HOWEVER, I see absolutely no reason at all to implement this in your situation. There is no benefit in having a string "CusXXXXXX" value where "XXXXXX" is really just the IDENTITY value. If you were going to obfuscate the "XXXXXX" via a hash or Modular Multiplicative Inverse, then it might be ok, though I am still not sure you would want to store the "Cus" string portion of it. But in its current form, you get no benefit whatsoever from doing this. You are just wasting space in the DB.

Other notes:

You would be better off not abbreviating "Customer" to be "CUS" as a prefix for columns in this table. Just use the full table name, especially for the ID column: CustomerID, and CustomerName.
You should not use a numeric type (i.e. INT) to store phone numbers. Numbers that you do not do mathematical operations on should be stored as strings. This also applies to postal codes, Social Security Numbers (SSNs), etc. In the case of phone numbers, they often have extensions or other non-numeric options, such as prefixing with + if they are outside of the base country.
You should not use a string type (i.e. VARCHAR(10)) to store a repeated code / label such as "gender" (assuming that the full words "male" and "female" or similar are being stored, and that a non-binary / _BIN2 Collation is being used). You should have a "Gender" lookup table with a GenderID TINYINT column as the Primary Key (but not an IDENTITY). Then in the Customer table you would also have GenderID TINYINT but it would be a Foreign Key referencing dbo.Gender (GenderID).

OR:

You could use a CHAR(1) COLLATE Latin1_General_100_BIN2 NOT NULL column with a CHECK Constraint enforcing that only M and F are acceptable. This gets you a "code" that is human readable while still being as efficient with storage, memory, and comparisons as a TINYINT would be.


SQL Server 2016 All Queries recompile all the time (plan cache corruption?)

You should enable Lock Pages in Memory (LPIM).

On SQL Server 2016+, I've encountered a similar scenario that I believe to be a bug. When the server experiences memory pressure, and LPIM is not be enabled, SQL Server will clear the plan cache as part of it's process to release memory back to the OS. This much in normal. SQL Server should only do this when a low memory condition exists.

In some cases, SQL Server will continue to clear the plan cache, even after the low memory condition is resolved. This results in the behavior you are seeing. On a busy server, you will see some plans entering the plan cache, only to get purged seconds later. This is not the proper behavior.

Enabling LPIM will resolve the issue. Enabling LPIM is a best practice for all SQL Server installations, so I would recommend enabling this for every SQL Server instance, regardless of version or whether you've experienced this problem.

To enable the lock pages in memory option

On the Start menu, click Run. In the Open box, type gpedit.msc.
On the Local Group Policy Editor console, expand Computer Configuration, and then expand Windows Settings.
Expand Security Settings, and then expand Local Policies.
Select the User Rights Assignment folder.
The policies will be displayed in the details pane.
In the pane, double-click Lock pages in memory.
In the Local Security Setting – Lock pages in memory dialog box, click Add User or Group.
In the Select Users, Service Accounts, or Groups dialog box, select the SQL Server Service account.
Restart the SQL Server Service for this setting to take effect.


Migrate 7TB of Database for Colo Migration

One could write a book on this topic, but one option would be an out-of-band initial replication of your database to the new site by flying a copy of the server hard drive(s) to New Jersey, followed by log shipping until you catch up, followed by setting up your "transnational" replication to target both old and new sites until you're ready to switch.


In order :

There is one reason to have multiple mongod instances in one machine (other than machine efficient repset setups) and that one reason is the process wide write lock. If you run into a situation where you're bottlenecked by the write lock rather than any hardware resource bottleneck you can go for multiple mongod instances. In all other situations it's usually more practical to have one mongod process, especially given it's memory mapped based storage engine.
There's no ideal number. Adding shards (almost) linearly scales up write and read performance for sharded collections. It's a basic horizontal scaling solution and follows the rules for that sort of scaling.
Replica sets always consist of at least 3 members although 1 of them can be an arbiter (votes but does not replicate). If you add more you gain read performance if your application uses slaveOk flagged queries. Again, ideal number depends on your use case.
Depends. Most people use a hot backup scheme of some sort. There's no difference between MySQL backup conventions and MongoDB here.
Depends on whether or not you want to clone a single database or the entire cluster? Both are well documented.


Innobackupex (Xtrabackup) 2.4.8 incremental backups take the same time as full backup

It's kind of expected assuming how incremental backups work.

Xtrabackup knows the last LSN (a point when a full copy was taken).
Xtrabackup reads all pages. Every page has an LSN in its header of the latest modification:

if a page is newer than the last LSN - save it.
if a page is older than the last LSN - skip it.

So, in the end of the day xtrabackup needs to read same amount of pages, whether it's a full or incremental copy.

But. There is an option in Percona Server can track modified pages. In that enabled, Xtrabackup doesn't have to scan whole tablespace and thus incremental backup time can be greatly reduced.


java.sql.SQLException: ORA-01410: invalid ROWID

go to

http://www.oracle.com/technetwork/apps-tech/jdbc-112010-090769.html

and download

http://download.oracle.com/otn/utilities_drivers/jdbc/11204/ojdbc6.jar
http://download.oracle.com/otn/utilities_drivers/jdbc/11204/orai18n.jar

Remove the class.forname and put the jars above on your classpath.  Loose the ojdbc14.


Return counts for multiple ranges in a single SELECT statement

Just use conditional SUM() statements per column for each number range. The total can be summed by just using SUM(1), assuming all of the data in the table is within one of the ranges - if not, just restrict it as with the others.

select sum(case when score between 0 and 3 then 1 else 0 end) as minrange,
       sum(case when score between 4 and 6 then 1 else 0 end) as midrange,
       sum(case when score between 7 and 10 then 1 else 0 end) as maxrange,
       sum(1) as total
from foo;

SQL Fiddle link.


Single Row Table for Storing an Additional Unique Identifier

I used to work with a program that used a similar system to generate unique IDs for invoices. It did originate in a different database system, one without auto-incrementing IDs.

There's one difference with your system, and it's a difference I like a lot. The invoice table has a unique, auto-incrementing ID - and it has a separate, customer-facing (I'll assume) invoice number.

I went through a process of merging two structurally identical databases. The auto-incrementing ID values overlapped, so we had to reset one set of them as we went through the merge process. Our invoice table used the unique, auto-incrementing ID as the customer-facing invoice number. So, we functionally lost those invoice numbers in the system.

While it would have been potentially problematic to have two invoices with the same (customer-facing) invoice number, at least we would have had that option if the customer-facing value had been distinct from the primary key.


1) To get a single table restored and/or imported, use mysqldump. See the "how" links in my post here.

2) For a hot copy of InnoDB tables, use Perconas XtraBackup tool (BTW, the Percona website generally and their mysqlperformance blog are excellent MySQL resources).

You can also replicate from InnoDB masters to MyISAM slaves and the other way round (although why one would want to do this is a mystery to me).


How to correctly use PFILE keyword in DDS Logical File? (AS400)

Each of these four things should be given its own PF (aka table).  In the real world, any of them might even be best served by additional supplementary tables, as may be appropriate.  For example, suppose a customer wishes to pace an order for several different items.  This would normally be handled with an order header file, to store data pertaining to the order as a whole, and an order detail file for each item being ordered. But we'll stay within the bounds of your assignment, which has been specifically worded with an expected design.

The tables do not access each others information, per se. But they will contain fields (aka columns) which will refer to key field in other tables.  It is generally a good idea to create indexes on each table, for the various ways we can expect it to be accessed, by creating a simple keyed logical file for each such key sequence.

In this case, after you have defined your ORDFILE, you might define a logical file, called for example ORDFILE1, with CUSTOMER as the [first] key field, perhaps followed by ORDRNUM.  You might have another keyed by ITEMNUM. Whatever key sequences may come in handy can each have it's own LF. Indexes are very low overhead on the iSeries, so having several dozen is not a problem, even though other platforms might have a lower recommend guideline. So in the real world you might want to create a good variety over these files, to help the optimizer with various queries that your users may want.  For an assignment like this, create only those that are relevant to the requirements.

The type of LF that would combine fields from various physicals is called a join (or joined) logical file. Unless your professor has mentioned it, that may be outside the scope of his/her expectations.  A query can read multiple PFs, much as a join logical would, so a join LF may be superfluous in this case.

Disclaimer

Modern best practices on this platform would generally be to use SQL for databases, rather than DDS & Query/400.  And it may be handy to know that QM Query can convert Query/400 queries to SQL.  But most companies still have plenty of DB files defined with DDS. It is important to understand these earlier technologies when you are a professional developer on this platform, since you may have to support them.


You can import the (.csv) file through the following commands

mongoimport -d databaseName -c collectionName --type csv --file locationsOftheCSVFile.csv --headerline

For example here I have created the database name test and collection name StackExchange and going to import the LocarionReq.csv file.

> use test
switched to db test
> db.createCollection("StackExchange");
{ "ok" : 1 }
> show collections
StackExchange

C:\Program Files\MongoDB\Server\4.0\bin>mongoimport -d test -c StackExchange --type csv --file C:\test\LocationReq.csv --headerline
2019-04-11T10:58:53.147+0300    connected to: localhost
2019-04-11T10:58:53.263+0300    imported 1000 documents

Now you can check the total document records through the mongo shell command

>db.StackExchange.find().count();
 1000

If you want to find the document records through mongo shell then run the command such as

> db.StackExchange.find().pretty(); -- an easy-to-read attractive format.

Or

> db.StackExchange.find();

  Note : Run mongoimport or mongoexport from the system command line, not the mongo shell.

For your further ref here


Oracle DataGuard: Find primary from standby DB

You can check some options like checking FAL_SERVER parameter, LOG_ARCHIVE_DEST_n parameters, Dataguard Broker, Recovery Catalog, Cloud Control, etc..  if they are used, but none of them are mandatory for a DataGuard configuration.

In fact, the standby nodes doesn't need to send any kind of information to the Primary db at all. Since the heartbeat is checked from the Primary, the best approach is to query v$session and track down sessions connected as the, non-existent, "PUBLIC" schema.


Can I lookup my Primary Key in SQL Server from Excel Spreadsheet?

Yes, Excel can query data from a database. On the Data tab there are tools to create and maintain connections. IIRC it uses ODBC so all major database products can be referenced.


Issues using pt-kill to kill log queries

Try this one:

pt-kill --busy-time 15 --print -u root -p password --match-command Query --victims all

Then add --kill-query if you think this one is what you intend to use.

Edit

Changed --match-all to --victims all


Functional dependencies (hopefully simple question)

No, the name of a student doesn't determine his or her identity and hence the student number.

There can be more than one person with the same name, but they still would have a different student number.

This is the reason why databases usually have artificial numeric identifiers like that, and the reason why you have customer IDs, social security numbers and the like.

When choosing a candidate for a primary key, use the following rules of thumb:

Does the attribute uniquely identify the row? Names will not.
Can the attribute ever be changed? Names can be changed, e.g. when you marry.

Mutable attributes are no good choice for primary keys in a database, because they make referential integrity via foreign keys a mess.

In most practical cases, the primary key of a table will be an artificially generated number.


This works for me:

select trg.tgname,
        CASE trg.tgtype::integer & 66
            WHEN 2 THEN 'BEFORE'
            WHEN 64 THEN 'INSTEAD OF'
            ELSE 'AFTER'
        end as trigger_type,
       case trg.tgtype::integer & cast(28 as int2)
         when 16 then 'UPDATE'
         when 8 then 'DELETE'
         when 4 then 'INSERT'
         when 20 then 'INSERT, UPDATE'
         when 28 then 'INSERT, UPDATE, DELETE'
         when 24 then 'UPDATE, DELETE'
         when 12 then 'INSERT, DELETE'
       end as trigger_event,
       ns.nspname||'.'||tbl.relname as trigger_table,
       obj_description(trg.oid) as remarks,
         case
          when trg.tgenabled='O' then 'ENABLED'
            else 'DISABLED'
        end as status,
        case trg.tgtype::integer & 1
          when 1 then 'ROW'::text
          else 'STATEMENT'::text
        end as trigger_level
FROM pg_trigger trg
 JOIN pg_class tbl on trg.tgrelid = tbl.oid
 JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE trg.tgname not like 'RI_ConstraintTrigger%'
  AND trg.tgname not like 'pg_sync_pg%'

It doesn't include DDL triggers though.


sqlserver 2014 DEFAULT in VALUES using MERGE

According to MS-DOCS:

Table Value Constructor (Transact-SQL)

  DEFAULT

  Forces the Database Engine to insert the default value defined for a column. If a default does not exist for the column and
  the column allows null values, NULL is inserted. DEFAULT is not valid
  for an identity column. When specified in a table value constructor,
  DEFAULT is allowed only in an INSERT statement.

You cannot use DEFAULT in a MERGE statement. I suppose it is because it cannot evaluate DEFAULT value for a single TVC.

SELECT * FROM (VALUES(1, 'D')) AS T(A,B);
GO

 A | B
-: | :-
 1 | D

SELECT * FROM (VALUES(1, DEFAULT)) AS T(A,B);
GO

Msg 156 Level 15 State 1 Line 1
Incorrect syntax near the keyword 'DEFAULT'.

But, as far as you know DEFAULT values of your table, you could change the word DEFAULT by the actual default value:

MERGE INTO [dbo].[IMOS] [T]
USING
    (VALUES ('!Default_Materials_1', 0), ('00_NEW',30)) AS [S] ([NAME], [TYP])
ON [T].[NAME] = [S].[NAME]
WHEN MATCHED THEN
UPDATE SET [TYP]=[S].[TYP]
WHEN NOT MATCHED THEN
INSERT ([NAME], [TYP]) VALUES ([NAME], [TYP]);

Example:

CREATE TABLE TBL
(
    ID INT IDENTITY PRIMARY KEY,
    FOO INT DEFAULT 0,
    BAR VARCHAR(10) DEFAULT 'X'   --< DEFAULT value for BAR
);
GO

INSERT INTO TBL VALUES (1, 'A'), (2, 'B'), (3, 'C');
GO

Now replace DEFAULT by 'X':

MERGE INTO TBL
USING (VALUES (1, 'X')) AS T(A,B)
ON T.A = TBL.FOO
WHEN MATCHED THEN
UPDATE
    SET BAR = B
WHEN NOT MATCHED THEN
INSERT (FOO, BAR) VALUES (T.A, T.B);

SELECT * FROM TBL;
GO

ID | FOO | BAR
-: | --: | :--
 1 |   1 | X
 2 |   2 | B
 3 |   3 | C

db<>fiddle here

In HugSQL:

MERGE INTO TBL
USING (VALUES (?, 'X')) AS T(A,B)
ON T.A = TBL.FOO
WHEN MATCHED THEN
UPDATE
    SET BAR = B
WHEN NOT MATCHED THEN
INSERT (FOO, BAR) VALUES (T.A, T.B);

Using a TYPE

On SQL-Server 2014 you could use a TYPE (alias data type).

CREATE TABLE dbo.MyTest
(
    Id int primary key,
    Val char(3) DEFAULT 'aaa'
);

INSERT INTO dbo.MyTest VALUES (1,'V1'),(2,'V2'),(10,'V10'),(11,'V11');

CREATE TYPE dbo.Mytype AS
TABLE
(
    Id  int PRIMARY KEY,
    Bar char(3) DEFAULT 'aaa'
);

Obviously table and type schema must be the same.

DECLARE @myData Mytype;

INSERT INTO @myData(Id, Bar) VALUES (1, DEFAULT),(3, 'V3')

MERGE INTO dbo.MyTest
USING (SELECT Id, Bar FROM @myData) src
ON MyTest.Id = src.Id
WHEN MATCHED THEN
UPDATE SET Val = src.Bar
WHEN NOT MATCHED THEN
INSERT (Id, Val) VALUES (src.Id, src.Bar);

SELECT * FROM MyTest;
GO

Id | Val
-: | :--
 1 | aaa
 2 | V2
 3 | V3
10 | V10
11 | V11

db<>fiddle here


What is the Query to display the failed sql jobs

I don't think you will really get "all" the job information with one query since jobs can be configured to go to output files. Output files can at times get more information than is reported or written to the msdb tables.

However, the view that is found by looking at the job history via SSMS can be pulled with this query to return only failed jobs (e.g. if a job has 2 steps and the second failed this query will return both steps):

select j.name
    ,js.step_name
    ,jh.sql_severity
    ,jh.message
    ,jh.run_date
    ,jh.run_time
FROM msdb.dbo.sysjobs AS j
INNER JOIN msdb.dbo.sysjobsteps AS js
   ON js.job_id = j.job_id
INNER JOIN msdb.dbo.sysjobhistory AS jh
   ON jh.job_id = j.job_id AND jh.step_id = js.step_id
WHERE jh.run_status = 0


Is there a more efficient way to set up this table

I think your approach is reasanoble.
Because it would show online data (no need for background processing) and would only use resources from active transactions (customer not seeing anything will not need to have their data reprocessed).

I would do it using triggers. You would not have to change your existing code and you could disable/enable this new functionality more easily.

An idea:
Try to use a LRU algorithm.
Try to see the "importance" of an item. For example, adding an extra column for counting the amount of times an item as viewed.
If a user visited an item many times you could not stop showing it on this Top25 List simply because some other 24 random items were searched.


Your query can be collapsed to the single JOIN without subselects:

UPDATE      student_activity AS c
  LEFT JOIN student_activity AS b ON c.student_id = b.student_id
                                 AND c.class_type = b.class_type
                                 AND c.date > b.date
 WHERE b.date IS NULL
   SET c.first_attendance = c.date
;

I hope your table student_activity have the index (student_id, class_type, date)


Oracle Optimizer refuses to use my index

The optimizer does not use your index, not because it does not want to, but becasue it can not. For a query like that the index does not cover all possible outcomes.

When :a, :b, :c are all NULL, and you are searching for rows where column1, column2, column3 are all NULL, those rows are not indexed by a (column1, column2, column3) b-tree index, so it can not be used for retreiving the matching rows.

Adding NOT NULL constraints may help, but if you have NULL values, that is of course not possible (and I am assuming you have NULL values, otherwise why would you construct a query like this).

You could also try creating an index that includes a constant value, so even those rows will be indexed, where the indexed columns are NULL. So instead of an (column1, column2, column3) index, you could try creating a (column1, column2, column3, 'A') index.

The best solution in my opinion is however to have different queries generated whenever a bind variable was not assigned a value. Queries like the above are results of trying to handle multiple cases with a single query. This may seem to be elegant and compact, and yes, I know, generating different queries based on the presence of parameters can be cumbersome, but I have seen this kind of laziness causing severe performance issues countless times.

Yes, Oracle has some tricks such as NVL optimization, OR-expansion, for handling such predicates. Those work perfectly in Powerpoint and training materials, with simple examples, but in reality, with complex queries they can not be always used.


Blank (but not null) columns - PostgreSQL 9.2

You can use get_byte function to detect which character in specific offset:

SELECT *, get_byte(name_first,0) as firstChar,  get_byte(name_first,0) as secondChar FROM ja_customers WHERE id = 8154501


Can't find stored procedure for updating after creating it

The problem here is typically because you were in the wrong database context (in this sense, "wrong" means really "unexpected".

In other words, you were expecting to create the stored procedure in Database1, but you were really in the context of Database2.

This is the exactly reason why I'm always explicit, especially with DDL.  At the top of your script/query window/etc., always put:

use YourDatabase;
go

This will ensure you don't run into this snag again.  I tend to not rely on GUI drop-down lists and my memory to double-check which database is selected before running management SQL queries.


Pass 'interval' value to date_trunc function in PostgreSQL procedure

I am not entirely sure about your objective, but what you show works with legal identifiers. "end" is a reserved word and I wouldn't use "start" either. Reserved words require double-quoting, best avoided to begin with:

DO
$$
DECLARE
   ts_start       timestamp := '2019-03-20 00:00:00';  -- legal name
   ts_end         timestamp := '2019-07-02 01:12:13';
   i              numeric   := (select to_char(age(ts_end, ts_start),'MM'));
   interval_value varchar   := i || ' month';
   xyz            date      := (select date_trunc('month',ts_start::date)
                                     + interval_value::text::interval);
BEGIN
   RAISE NOTICE '%', xyz;
END
$$;

Can be much simpler, though:

DO
$$
DECLARE
   ts_start       timestamp := '2019-03-20 00:00:00';
   ts_end         timestamp := '2019-06-29 01:12:13';
   xyz            date      := date_trunc('month', ts_start)
                             + date_trunc('month', age(ts_end, ts_start));
BEGIN
   RAISE NOTICE '%', xyz;
END
$$;

db<>fiddle here (raising exceptions to display the result)


When try to do Mysqldump Backup from console then getting such as error

if you are able to start mysql from command line then there should be no error in taking dump as well

check to see if following command works, Either traverse to directory containing binaries or include them in path variable.
You can also include variables to include port,socket etc in your path.

  mysql.exe -uroot -p -P3306

In order to take mysqldump use following,use switches to your conveniences.

  mysqldump.exe -uroot -p -P3306 --all-databases --events >outfile.sql

Give It a try and let us know in case of any error.

Regards and Happy New Year.


Maintaining Postgres copies of fdw tables from Oracle

I have a production Oracle 11g server, from which I want to copy data regularly to PostgreSQL 10.14 database for DWH uses ...

Why move the data from Oracle to Postgres at all?
Why not use a Data Guard based solution to populate your Data Warehouse?
Three possibilities:
Logical Standby:
Data Guard applies SQL on the Standby (instead of the more efficient, Redo vectors).
Logical Standbys can be used to load-balance near real-time, read-only activity away from the Primary.
The Logical Standby database can be structurally different (i.e. different indexes, etc.) from the Primary ...   and therein lies your problem if the vendor goes and changes stuff - this will not get carried through into the Logical Standby automatically.  You would have to find out the changes and re-make them in the Standby.
Alternatively, you could rebuild the Logical Standby database after each Product release or build a new Logical Standby, route Users to that one and throw the old one away).
Active Data Guard:
This is a "cost option", but it might be your simplest solution.
Using Active Data Guard, your Physical Standby database becomes readable and is [very] near real-time up-to-date with the Primary.
The Standby database is structurally identical to the Primary.
Snapshot Standby:
Spends some of its life as an inaccessible, Physical Standby database (unless you pay for Active Data Guard), but when you want to use it, you "unplug" it from the Data Guard configuration and open it as a database in its own right, with Data as of when it was "unplugged".  Each night, you "plug" it back into the Data Guard configuration and redo (held on the Standby's host) is re-applied, bringing the database back "up-to-date" with the Primary.
The Standby database is structurally identical to the Primary unless you choose to add extra indexes, etc., after the "unplugging" activity.

Reimport ... still breaks ... constructs like:
INSERT INTO my_pg_mirror_table_archive SELECT * FROM oracle_fdw_view;

And this is just one of the reasons why should you never use "select *" in Production Code.


Based on the idea that you want to update a single record that fits your criteria, but do not care which record that is, this will work for you:

UPDATE tbltokens
SET status='Used', userid=1, serviceid=2
WHERE status = 'Unused' AND userid IS NULL AND serviceid IS NULL
LIMIT 1;

Using LIMIT on an UPDATE query works the same as on a SELECT. It's detailed in the MySQL doc for UPDATE.

You should also use IS NULL instead of = NULL, as this is the correct syntax for checking NULL values.

Also string literals should be quoted with single quotes (like this: 'text') and not with double quotes so the code wil continue to work in different SQL modes (eg ANSI) where double quoted strings are identifiers and not constants.


SQL Query optimization - Long running query

EDITED after question got more details.
I get a Clustered Index Scan and an Index Scan. But I don't have a lot of rows in my table.

CREATE TABLE [Document](
[IdDocument] [int] NOT NULL,
[DocumentType] [int] NOT NULL,
CONSTRAINT PK_Document PRIMARY KEY CLUSTERED (IdDocument)
)

CREATE TABLE [ExternalKey](
[IdExternalKey] [int] NOT NULL,
[RefDocument] [int] NOT NULL,
[EntityType] [int] NOT NULL,
[Value] [int] NOT NULL,
CONSTRAINT PK_ExternalKey PRIMARY KEY CLUSTERED (IdExternalKey),
CONSTRAINT FK_RefDocument FOREIGN KEY (RefDocument) REFERENCES Document(IdDocument),
CONSTRAINT UC_ExternalKey UNIQUE (RefDocument, EntityType, Value)
)

-- Insert a few values
DECLARE @IdDocument INT, @DocumentType INT, @IdExternalKey INT;
Set @IdDocument=0;
SET @DocumentType = 10;
select @IdExternalKey=MAX(IdExternalKey)+1 from ExternalKey ;

set nocount on
WHILE (@IdDocument < 10000)
BEGIN
    INSERT INTO Document(IdDocument, DocumentType) VALUES (@IdDocument, @DocumentType);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey, @IdDocument, 50, 3421);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+1, @IdDocument, 50, 9524);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+2, @IdDocument, 60, 7893);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+3, @IdDocument, 50, 1752);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+4, @IdDocument, 50, 8979);
    INSERT INTO [ExternalKey](IdExternalKey,RefDocument,EntityType,Value) VALUES (@IdExternalKey+5, @IdDocument, 60, 7822);

    SET @DocumentType=CASE WHEN @DocumentType > 100 THEN 10 ELSE @DocumentType + 10 END;
    SET @IdDocument = @IdDocument + 1;
    SET @IdExternalKey = @IdExternalKey+ 6;
END

SELECT TOP 50 IdDocument
FROM Document d
JOIN ExternalKey e on e.RefDocument=d.IdDocument
WHERE
(d.DocumentType IN (10,20,30) AND e.EntityType = 60 AND e.Value IN (4,5,6,7,8)) OR
(d.DocumentType IN (50) AND e.EntityType = 61 AND e.Value IN (110,120,130) OR
(d.DocumentType IN (80,90) AND e.EntityType = 59 AND e.Value IN (1,2,3)))
ORDER BY IdDocument

I would add that if the ORDER BY is not necessary and can be done in the program instead, it can help accelerate the query.

What you really want to do to improve your query would be to fix the "Index Scan" of the ExternalKey table or the scan of the Document table.
For example, since the ExternalKey is unique by RefDocument, entityType, Value, maybe consider using it as clustered index?

The thing is right now, with the keys and indexes, SQL Server has no choice but to scan the whole table to get the rows you need. So if you have a million row, it has to scan every row even if you only get 10 as a result.


This is a nice a example to explain how the query optimizer works, because in my test server, with a couple or rows, your first query uses an index (look at the usage of EXPLAIN):

root@127.0.0.1(test)> EXPLAIN SELECT idPosition , idAgent , LatitudePoint , LongitudePoint , TimeStamp AS StartTime
    -> FROM `agentpositions` `t` IGNORE INDEX(`idAgent`, PRIMARY)
    -> WHERE (
    ->    t.idAgent=880 AND t.PointDate >= "2015-04-04"
    ->    AND (t.TimeStamp >= "2015-04-04 00:00:00" and t.TimeStamp < "2015-04-04 23:59:59"))
    ->    AND t.LatitudePoint != 0 and t.LongitudePoint != 0
    -> ORDER BY t.TimeStamp\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: range
possible_keys: idagent_pointdate
          key: idagent_pointdate
      key_len: 9
          ref: NULL
         rows: 1
     filtered: 7.11
        Extra: Using index condition; Using where; Using filesort
1 row in set, 1 warning (0.00 sec)

While the second doesn't:

root@127.0.0.1(test)> EXPLAIN SELECT idPosition , idAgent , LatitudePoint , LongitudePoint , TimeStamp AS StartTime
    -> FROM agentpositions t IGNORE INDEX(idAgent, PRIMARY)
    -> WHERE
    -> t.idAgent=880 AND t.PointDate in ("2015-04-04","2015-04-05","2015-04-03" )
    -> AND t.LatitudePoint != 0 and t.LongitudePoint != 0
    -> order by t.TimeStamp\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t
   partitions: NULL
         type: ALL
possible_keys: idagent_pointdate
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 5
     filtered: 38.40
        Extra: Using where; Using filesort
1 row in set, 1 warning (0.01 sec)

The main reason why an index gets or not used is, first- if it can be used (this happens for both cases, as you can see on the possible_keys row, and second, if it considers using the key the best strategy to retrieving the rows (based on the cardinality of the columns).

I have shown this graph many times:

If the filter that can be used for an index is not very selective (for example, the range is too large), the optimizer will prefer to use a full table scan, as it is a faster strategy for reading a large number of rows. If you want to know the details for your particular query, and youa re using MySQL 5.6, you can use the OPTIMIZER_TRACE feature to understand why one plan was preferred over another.

If you believe a discarded plan is better, you can force the usage of an index by using FORCE INDEX(idagent_pointdate). Be careful because manually forcing the usage of an index is a dangerous practice, as if the data cardinality changes and the usage of the index is no longer the best plan, you will continue blindly using it.

Aside from your main question, there are many other issues regarding your query and indexes, that I will try to address here:

Your idAgent index is redundant- you can use idagent_pointdate for everything that you can do with that index, so it is useless, and will take away resources (memory, disk) but also slow down certain tasks like the query optimizer having to take it into account and writes (as an additional index has to be updated as well). Use tools like pt-duplicate-key-checker to search for redundant indexes on your database.
I do not get well what you mean with date-int and timestamp-int, but if you mean the practicality of having what basically is having redundant information, one storing second precision and another storing days: that may a useful physical optimization of the table IF you need to index days instad of timestamps. If you just need to index the timestamp using ranges, like t.TimeStamp >= "2015-04-04" and t.TimeStamp < "2015-04-05" (check the syntax, you are missing 1 seccond) this is again a waste of resources. There are cases like having an extra column is justified in order to index, for example, DAYOFWEEK(t.TimeStamp) or HOUR(t.TimeStamp) which cannot otherwise be indexed directly, as mysql lacks that functionality. I do not see its value here.
You will be unable to use 2 range accesses to two separate columns for the same index. Using IN + a list of values is a legit workaround for this limitation (which is intrinsic of B(+)TREE indexes). This may be your main issue here.
The use of the range cancels the possibility (in this particular case) of using the index for sorting- this cannot be workaround -either you use the index for filtering or for sorting.

Update: Because of B(+)TREE indexes, you can only use the first range for a multi-column index. I have another drawing of why here:

A composite index is just a BTREE ordered by the concatenated values:

The index with the reverse column order cannot be used in such an effective way because of the range at the start:

Technically, both indexes would be equally good as the should be of similar size, but for this particular query, the idagent_pointdate (idAgent, PointDate) is preferred because it has the range at the end, while for pointdate_idagent (PointDate, idAgent), only the first row can be used.


You didn't supply any test data to validate my results against and perhaps I'm misunderstanding exactly what you are trying to achieve, but it seems this would give you the correct answer.

Declare @T table
(
[pupil id] int,
[event date] date,
[header note] varchar(50),
[module id] int,
[event id] int
)

insert into @T values(1,'2010-01-01','note1',22,1)
insert into @T values(1,'2016-01-01','latest note',22,1)

insert into @T values(2,'2013-01-01','note1',22,1)
insert into @T values(2,'2015-01-01','latest note',22,1)

SELECT [Pupil ID]
    ,[Event Date]
    ,[Header Note]
    ,[Module ID]
    ,[Event ID] AS ID
FROM @T EV
WHERE [Module ID] = 22
    AND [Event Date] = (
        SELECT MAX([Event Date])
        FROM @t
        WHERE [Pupil ID] = EV.[Pupil ID]
            AND [Module ID] = EV.[Module ID]
        )


Mysql - latin1 (with latin2 content) to utf8

You can try something like:

INSERT INTO original_new()
SELECT CONVERT(CAST(CONVERT(`field` USING latin2) AS BINARY) USING utf8)
FROM original


What kind of data type to use for numbering in the Harmonized System?

"The harmonized system uses a numbering" - they are not numbers, they are codes.  They just happen to use digits instead of Latin letters for the code values.

As you have to retain leading zeros anything numeric will not suit.  You have to go with textual types.  Suppose the codes were

A01        LIVE ANIMALS
A01B03     swine, live
A01B03C10  Purebred breeding animals

I suspect you would have no concerns about using text?  Just think of them this way, but with the A, B and C removed.  Or think of your alphabet as consisting of 36 characters, a to z and 0 to 9.


Each row has a minimum of 7 bytes of overhead when stored in FixedVar format (the default). There will also be a (typically relatively small) number of pages used for the upper levels of the clustered index. Optimally stored, and disregarding the upper index levels, 2 million rows would require just over:

(7 + 8 bytes) * 2,000,000 = 28.61MB.

More importantly, pages are likely to have split (unless the data was loaded in clustered key order), so the current pages will probably be less than 100% full. When a page is split, to accommodate new rows in key order, roughly 50% of the existing rows are moved to a new page, lowering the average density. Also, any deleted rows will only result in space being reclaimed if the whole page from which rows were removed becomes empty. In addition, each 8KB data page has a 96-byte header, and 2 bytes per row on the page for the row offset array.

The following example loads 2,000,000 rows with approximately the same distribution as your data, compacted as much as reasonably possible:

CREATE TABLE dbo.ID_STORE
(
    WORKING_ID bigint NOT NULL PRIMARY KEY CLUSTERED
);

WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12
FROM Nums
WHERE Nums.n <= 2000000;

The output of:

EXECUTE sys.sp_spaceused
    @objname = N'dbo.ID_STORE',
    @updateusage = 'true';

...shows 33,800 KB space reserved for this object.

Truncated and loaded so page splitting occurs:

TRUNCATE TABLE dbo.ID_STORE;

WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12
FROM Nums
WHERE Nums.n <= 1000000;

WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT dbo.ID_STORE WITH (TABLOCKX)
SELECT Nums.n * 12 + 1
FROM Nums
WHERE Nums.n <= 1000000;

The output of:

EXECUTE sys.sp_spaceused
    @objname = N'dbo.ID_STORE',
    @updateusage = 'true';

...now shows 50,632 KB KB space reserved.

Rebuilding the clustered index:

ALTER INDEX ALL
ON dbo.ID_STORE
REBUILD
WITH
(
    MAXDOP = 1,
    SORT_IN_TEMPDB = ON
);

...reduced the space reserved to 33,800 KB again.

Depending on the version and edition of SQL Server you have, this table can be stored even more compactly using row or page compression, clustered columnstore, or clustered columnstore archival storage. In the latter case (SQL Server 2014 Enterprise required), the 2,000,000 rows reserve just 2,960 KB of space.


There's a bug or maybe lack of functionality in various versions of MariaDB/MySQL that prevents update of a table that's part of a sub-select. Small example:

create table A (x int not null, y int not null);
insert into A (x,y) values (1,1),(2,2),(3,3);
update A set y = 5 where x in (
    select x from A where y in (1,2)
);
ERROR 1093 (HY000): Table 'A' is specified twice, both as a target for 'UPDATE' and as a separate source for data

You can fool the DBMS by hiding the table in a nested table

update A
    set y = 5
where x in (
    select x from (
        select x from A where y in (1,2)
    ) as T
);
Query OK, 2 rows affected (0.01 sec)

MySQL on the other hand supports a non standard update where you can update a join, but since you did not provide create table statements I'll leave that out of the answer (I'm too lazy to figure out what your tables look like :-)


output psql results and errors to a file

RAISE NOTICE must be used to display values in a DO block,
as in

DO $$ begin raise notice 'hello'; end $$;

Result:

NOTICE:  hello

If you do just a SELECT, here's what happens:

DO $$ begin select 'hello'; end $$;
ERROR:  query has no destination for result data
HINT : If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT : PL/pgSQL function inline_code_block line 1 at SQL statement

Since it's an error, it doesn't go into the file designated by \o :v1

The output of \timing also won't go into that file.

The documentation says about \o:

  \o or \out [ filename ] \o or \out [ |command ]
     Arranges to save future query results to the file filename or pipe
     future results to the shell command command. If no argument is
     specified, the query output is reset to the standard output.

  ...

  “Query results” includes all tables, command responses, and notices
     obtained from the database server, as well as output of various
     backslash commands that query the database (such as \d); but not
     error messages.

To capture all the output, use redirection at the shell:

$ psql [options] >file 2>&1

Another problem is that the :v2 variable cannot be expanded in the dollar-dollar-quoted string that constitutes the DO block (and it should be written as :'v2', not ':v2', but anyway it doesn't matter here because there's no variable expansion in $$-quoted strings )

Both input parameters and output results are sadly difficult to manage with DO blocks.

For the input side, custom session parameters can be used, as shown below:

test=> \set var 'foo'
test=> set params.v1 to :'var';
SET
test=> show params.v1;
 params.v1
-----------
 foo
(1 row)

test=> do $$ begin raise notice '%', current_setting('params.v1'); end $$;
NOTICE:  foo
DO


In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns?

Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!

Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.

USE MyDB

SET NOCOUNT ON

CREATE TABLE ##nullable  (
    ID INT IDENTITY(1,1),
    SchName VARCHAR(128),
    TblName VARCHAR(128),
    ColName VARCHAR(128),
    hasNulls BIT,
    PRIMARY KEY(ID)
    )

DECLARE @currTbl VARCHAR(128)
DECLARE @currCol VARCHAR(128)
DECLARE @currSch VARCHAR(128)
DECLARE @limit INT
DECLARE @i INT
DECLARE @sql NVARCHAR(4000)

INSERT INTO ##nullable (
    SchName,
    TblName,
    ColName,
    hasNulls
    )
SELECT
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    0 AS hasNulls
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.IS_NULLABLE = 'YES'
AND t.TABLE_TYPE = 'BASE TABLE'

SET @limit = (SELECT MAX(ID) FROM ##nullable)
SET @i = 1

WHILE @i <= @limit
BEGIN
    SELECT @currSch = SchName,
        @currTbl = TblName,
        @currCol = ColName
    FROM ##nullable
    WHERE ID = @i

    SET @sql = 'UPDATE ##nullable
        SET hasNulls = 1
        WHERE ID = ' + CAST(@i AS VARCHAR(20)) + '
        AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + '
        WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'

    EXEC(@sql)

    SET @i = @i + 1
END

SELECT DISTINCT * FROM ##nullable
WHERE hasNulls = 0

DROP TABLE ##nullable


This is indeed a reasonable case for storing object-like or key/value data, and representing it as JSON in jsonb fields in PostgreSQL is a reasonable way to do that.

In general it's time to consider hstore, xml, jsonb, etc when you're starting to look at alternatives like EAV or wide tables where the app adds columns dynamically. jsonb is basically the new hstore with a more standard way of representing its data and with nesting capabilities, so it's preferable most of the time.

Static tables for different kinds of thing, with parent tables for more general categories of thing, still make sense if the things being tracked are (a) fairly static and not user-defined, and (b) not excessively numerous. That does not sound like the case here.

I still recommend keeping attributes as regular columns where:

They form part of a referential relationship (is-a, has-a, contains, is-contained-by, owns, is-owned-by, etc);
They are a natural key where a UNIQUE constraint makes sense;
They are filtered on or otherwise queried in the large majority of queries

You can define a unique constraint - or rather, unique index - over an expression that queries the json field. It's just often cleaner to have them separate. Similarly expression indexes can help with oft-queried fields you want to keep part of the json object. It's really the first point that's the most important, as RI constraints cannot reference the value of a json object key.


Why isn't postgres prompting me about a new user?

First, let's understand how createuser works, run this command and take a look at the results:

> createuser --help

Will you see a lot of information, by those the most important for you now is the following...

This one is what makes PostgreSQL ask for a password for this new user:

-P, --pwprompt            assign a password to new role

This one is the one that will make this user able to create a new database (notice you didn't used it):

-d, --createdb            role can create new databases

At the bottom, you will have a group of arguments:

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to create)
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

These ones are used to connect to PostgreSQL's instance, which means that, to create a new user you will have first to connect to PostgreSQL using an already existent user that is able to create another. On a default installation the only user you have is postgres, so to connect you have to use it as -U postgres. Depending on your pg_hba.conf configuration, the createuser utility will ask for the password of postgres user.

With all these information, you should be able to know how to create your new user:

> createuser -U postgres -e -P -d NewUserName

OBS: -e is not necessary, but is good to learn what is happening behinde the scenes.

Afeter that all, you probably can create a new database using rake db:create. Perhaps you will need to edit pg_hba.conf, but I think this is a new topic.


Case 1:  Do you want the extra column to have only one entry, at the bottom?

Case 2:  Do you want a column with a running total that increases row by row?

For Case 1, there are two approaches:

1a: Do it in your application code; this is probably cleaner.

1b: You want to do it entirely in SQL:

SELECT ... (what you have, plus an extra column with an empty string) ...
UNION ALL
SELECT (a bunch of blanks, then SUM(to get grand total) ...

For Case 2, you need an @variable that keeps the running total:

SELECT ... SUM(e.salary) AS ..., @run := @run + SUM(e.salary) AS ... ...

To get @run initialized, you could either have a separate SET statement or fold it into this query by using a 'cross join':  JOIN ( SELECT @run := 0 ) AS z (with no ON clause).


Can you use "RENAME" instead of "FLASHBACK TABLE" to restore a table in Oracle?

No you cannot. To restore a table from the recycle bin use flashback table <table_name> to before drop;

<table_name> is the original table name and not the object_name from the recycle bin.

Do not rename the table to restore it from the recycle bin!


Once the table gets really big, you will find that looking up an item to find out what list it is in becomes painfully slow.

I discuss that, plus provide efficient code, in my blog on ip-ranges.  Your use case (non-overlapping ranges of items "owned" by various "lists") seems to be the same problem.


What is the "SYS_C00054_14031410:34:03$" column in my regular Oracle table?

Unused columns.

SQL> create table t1 as select * from dba_roles;

Table created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                                  VARCHAR2(8)
 AUTHENTICATION_TYPE                                VARCHAR2(11)

SQL> alter table t1 set unused (AUTHENTICATION_TYPE);

Table altered.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ROLE                                      NOT NULL VARCHAR2(30)
 PASSWORD_REQUIRED                                  VARCHAR2(8)

SQL> select column_name from user_tab_cols where table_name = 'T1';

COLUMN_NAME
------------------------------
SYS_C00003_18012517:25:39$
PASSWORD_REQUIRED
ROLE

SQL> alter table t1 drop unused columns;

Table altered.

SQL> select column_name from user_tab_cols where table_name = 'T1';

COLUMN_NAME
------------------------------
PASSWORD_REQUIRED
ROLE

Edit:

Extra information: I used *_TAB_COLS, because *_TAB_COLUMNS does not list such columns.


Your problem is this line:

c:\Program Files\PostgreSQL\9.5\bin>postgres.exe --single -D c:\Program Files\PostgreSQL\9.5\data

If there are spaces in a command line parameter you need to quote like this

c:\Program Files\PostgreSQL\9.5\bin>postgres.exe --single -D "c:\Program Files\PostgreSQL\9.5\data"

This isn't really a problem with Postgres but just how command line works.


How does SQL Server calculate the initial size of a (compressed) backup?

From BOL :

For compressed backups, the size of the final backup file depends on how compressible the data is, and this is unknown before the backup operation finishes. Therefore, by default, when backing up a database using compression, the Database Engine uses a pre-allocation algorithm for the backup file. This algorithm pre-allocates a predefined percentage of the size of the database for the backup file. If more space is needed during the backup operation, the Database Engine grows the file. If the final size is less than the allocated space, at the end of the backup operation, the Database Engine shrinks the file to the actual final size of the backup.
To allow the backup file to grow only as needed to reach its final size, use trace flag 3042. Trace flag 3042 causes the backup operation to bypass the default backup compression pre-allocation algorithm.

You should follow - Backup and restore best practices in SharePoint 2013.
Also, to me your VM having 2 CPU seems to be underspec for running a 70GB database (Not sure if there is only one database or multiple databases).


SQL SERVER equivalent for Oracle DBA_USERS

The database management views are the newest addition to the SQL Server family as of 2005 and newer and replace a lot of tables that were previously used in older versions of SQL Server. A lot of old sys schema tables have comments which point this out as is stated on the page for sys.syslogins.

This SQL Server 2000 system table is included as a view for backward compatibility. We recommend that you use the current SQL Server system views instead. To find the equivalent system view or views, see Mapping System Tables to System Views (Transact-SQL). This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

Instead you would use the following database management views:

sys.server_principals or sys.sql_logins (instead of sys.syslogins)
sys.database_principals (instead of sys.sysusers)

There is a mapping page which I have linked in the comment, which helps you find the newer views for the relevant data.
All links point to Microsoft Pages in the official SQL Docs
General Catalog Views
There is a list of System Catalog Views (Transact-SQL) that are focused on various areas of SQL Server.
The sys.database_principals, sys.server_principals and sys.sql_logins for example are all part of the Security Catalog Views which can be found on the overview page.
Recommendation
If you are just starting your SQL Server journey, then I would recommend learning the new views, as the old ones are deprecated and could become unsupported.


I just use this backup THE WHOLE MYSQL FILES way to migrate a MySQL database, and it worked!

here is my steps

  1, install MySQL server on the 2nd server with exactly same
  configuration like 1st server;

  2, stop 1st and 2nd mysqld server

  3, change the 2nd mysql data folder name, which you can find in mysql
  configuration file (my.cnf):

[root@ntest1 mysql]# cat /etc/my.cnf|grep dir
#dir
datadir=/usr/local/mysql/data
innodb_data_home_dir=/usr/local/mysql/data
innodb_log_group_home_dir=/usr/local/mysql/data
target_dir=/data/backups/

so I change the data/ name to data_bak/

mv data data_bak

because my innodb data(ibdata1 ...), innodb log(ib_logfile0...), binlgo(mysql-bin.000001...) and other database files (mysql/, yourdatabase/, ...) are all in /user/local/mysql/data/, so I just need copy all this files and folders to the object server with the same location

scp -r 10.88.48.111:/usr/local/mysql/data/* /usr/local/mysql/data/

note that I use root to copy, so I need to change the roles

chown -R mysql.mysql /usr/local/mysql/data

and then MySQLd could start up in the next moment


SQL Server: are backups between same major/minor version but different build compatible among them?

It should always be possible to restore within the same version, irrespective of service packs and other updates. Having said that, it is also recommend that you keep up with service releases so in an ideal world this won't be an issue.

One thing to note that throws some people off is that 2008 and 2008R2 are considered different versions (not one being a service release of the other) so you can't restore a backup from the latter with the former.


Best idea to deal with historic data ( Partitioning? )

We have several huge log tables partitioned by dates, the typical scenario where we just use current month to store data and the rest is queried for support & troubleshooting. We run backups for the whole database, not just single partitions/filegroups as that will imply piecemeal restores later on. And you don't want to be in the middle of the night dealing with piecemeal restores, unless you are really aware of all the steps involved...I'm not. So if in doubt, use partition at will, analyze your scenario to find the best partition function and run a normal backup of the whole database.


Ok, so after few trial and error queries I managed to solve this.

It may not be the best way but it seems to work fine.

select *
from
(
  select CAST([datetime_received] as date) AS ForDate,   DATEPART(hour,[datetime_received]) AS OnHour,     COUNT(*) AS Totals
  from [claims]
  where [datetime_received] between '2019-07-01 00:00:00.000' and '2019-07-31 23:59:00.000'
  GROUP BY CAST([datetime_received] as date),
       DATEPART(hour,[datetime_received])

) src
pivot
(
  sum(Totals)
  for OnHour in    ([0],[1], [2], [3],[4], [5], [6],[7],[8], [9], [10],[11], [12], [13],[14], [15], [16],[17],[18], [19],[20],[21], [22], [23])
) piv
order by ForDate

If any one can help me refine this i will appreciate it.

Thanks


Following the links in the comments provided by Biju jose and vonPryz

Understanding Built-In User and Group Accounts in IIS 7

Configure ASP.NET Impersonation Authentication (IIS 7)

we were following these steps:

To grant an anonymous account rights on the network by using IIS Manager:

Click Start, type INetMgr.exe, and then click Enter. If prompted, click Continue to elevate your permissions.

In the Connections section, click the + button next to the name of your computer.
In IIS Manager, double-click the site that you want to administer.
In the Features View, double-click Authentication.
Select Anonymous Authentication, and then click Edit in the Actions pane.
In the Edit Anonymous Authentication Credentials dialog box, click the Specific user option, and then click Set.
In the Set Credentials dialog box, input the user name and password desired, and then click OK.
But instead of doing the number 6 we just changed the setting to Application Pool Identity as you can see on the second picture below.

Changing the IIS settings to Application Pool Identity allowed the Windows Authentication to kick in and the active directory account was used to login into sql server.

That account had been previously configured to do so, therefore no more login problems for that user.

I would add that: Is “NT AUTHORITY\ANONYMOUS LOGON” login “mandatory”?


Organizing Database Objects in a Database that are not part of the systems original Database Objects?

On one hand, a sensible naming convention can solve part of your problem.

However, a more sophisticated solution is to use schemas: they are a good way to organize your stuff and also make you able to set access rights at this level.

For example, we usually create a data schema (named after the covered functionality and suffixed with '_data') and one or more API schemas.  Tables, the types used for their columns, triggers along with their procedures, sequences and so on all go into the data schema.  As applications are allowed to call API functions only (no arbitrary SQL is allowed), it just makes sense to put all of them into a separate schema, this is what I refer as API schema above.  There may be further ones (utility schema, versioning schema and so on), too.

Of course, your needs may be different, but I hope this illustrates well how schemas can be used.

In your case it is probably better to create schemas mapping the functional grouping.  Then you issue a

CREATE SCHEMA task1_views;

command, and create the objects either with specifying the schema, like

CREATE OR REPLACE VIEW task1_views.important_view ...

or set the search_path first, if you create many objects in a row:

SET search_path TO task1_views;
CREATE OR REPLACE VIEW important_view ... -- no schema specified


Are Stored Procedure failures logged by SQL Server?

You can't, unless the procedure has code to write to the error log, or it results in a certain severity.

Investigate using TRY...CATCH error handling.   You should be able to capture errors and then write those to the error log.


View: Ignore a left join if it is not used?

Think about it. Such a join without further information can not be skipped. Even if you do not select columns from maximo_assets, the join to it may increase the number of rows returned, because for 1 ID in gis_sidewalks, you can have multiple rows in maximo_assets with the same ID.

If the IDs are unique (or PK), and you make this known to the database by creating the proper constraints, then the above is not true anymore, and it will be able to skip the maximo_assets table:

alter table gis_sidewalks add primary key (id);
alter table maximo_assets add primary key (id);

select
    id,
    gis_last_edited_date--,
    --maximo_lastsyncdate,
    --sync_needed
from
    gis_sidewalks_vw;

        ID GIS_LAST_
---------- ---------
         1 01-JAN-19
         2 01-FEB-19
         3 01-MAR-19
         4 01-APR-19

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  f4p3b2huhfk5a, child number 0
-------------------------------------
select     id,     gis_last_edited_date--,     --maximo_lastsyncdate,
  --sync_needed from     gis_sidewalks_vw

Plan hash value: 1031306697

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |     3 (100)|          |
|   1 |  TABLE ACCESS FULL| GIS_SIDEWALKS |     4 |    88 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

The performance with the subquery you posted will not scale well.

Also, multi-purpose SQL is the root of a lot of performance problems, so handle with care.


Linked Server from On-Premise to Azure SQL Database

Depending on how you create the text file, it can be in all sorts of formats.  Unicode or ANSI. With or without a Byte order Mark.  With or without trailing whitespace and line feeds, either posix or Windows style.

JSON is simpler that way.

For instance if you run cmd.exe

c:\>echo CowboYs4EVR! > password.txt

and then dump the bulk column

SELECT BulkColumn
FROM    OPENROWSET(BULK 'C:\temp\password.txt', SINGLE_blob) AS x

You'll see

0x436F77626F79733445565221200D0A

An ANSI tring with a trailing space and trailing CRLF, which you'll need to trim, like this:

set @password = trim(replace(replace(@password, char(13),''),char(10),''))

If you write the file from PowerShell it's likely to be unicode, etc.

But if you store the password in a json file instead, SQL Server will do the file format parsing for you.

eg something as simple as:

C:\temp>echo ["CowboYs4EVR!"] > password.json

and

DECLARE @password VARCHAR(MAX)
SELECT  @password = json_value(BulkColumn, '$[0]')
FROM    OPENROWSET(BULK 'C:\temp\password.json', SINGLE_CLOB) AS x


Left Join table with first match historically

WITH cte AS ( SELECT table_a.value value_a,
                     table_b.value value_b,
                     ROW_NUMBER() OVER (PARTITION BY table_b.value ORDER BY table_a."date" DESC) rn
              FROM table_a
              JOIN table_b ON table_a."date" <= table_b."date")
SELECT value_b, value_a
FROM cte
WHERE rn = 1;

fiddle
Check does this is applicable to your server version.