With the data that I'm actually using (vs. the data in the example), I had to add in an additional field for the partition (not shown here), but it would look something like this...
CASE
  WHEN SUBCATEGORY IS NULL THEN
    FIRST_VALUE(SUBCATEGORY)
    OVER (PARTITION BY ADDITIONAL_FIELD,CATEGORY
          ORDER BY SUBCATEGORY NULLS LAST)
  ELSE
    SUBCATEGORY
END AS NEW_SUBCATEGORY

Full query:
SELECT
  ORDERID,
  CATEGORY,
  NEW_SUBCATEGORY,
  SUM(COUNTS) AS NEW_COUNTS
FROM
  (
    SELECT
      ORDERID,
      CATEGORY,
      SUBCATEGORY,
      CASE WHEN SUBCATEGORY IS NULL THEN FIRST_VALUE(SUBCATEGORY) OVER (PARTITION BY CATEGORY ORDER BY SUBCATEGORY NULLS LAST) ELSE SUBCATEGORY END AS NEW_SUBCATEGORY,
      COUNT(*) AS COUNTS
    FROM ORDER_TABLE
    GROUP BY
      ORDERID,
      CATEGORY,
      SUBCATEGORY
  )T
GROUP BY
  ORDERID,
  CATEGORY,
  NEW_SUBCATEGORY;


MySQL High CPU Usage (100% in peek times)

Consider using the Percona configuration tool for MySQL here https://tools.percona.com/

They also have a query advisor tool.

Both require registration in order to use them.


How to fix slow LIMIT queries with big offsets without heavily rewriting them?

You can add a new column to replies, call it position, and fill it with consecutive numbers of replies per thread (the position of the reply in the thread).

For example

id | thread_id | text | position
 1 |         1 | .... | 1
 2 |         2 | .... | 1
 3 |         1 | .... | 2
 4 |         1 | .... | 3
 5 |         2 | .... | 2
 6 |         3 | .... | 1

Further put an index on (thread_id, position, id) and it allows you to write queries like

SELECT * FROM replies
WHERE thread_id = 1234
AND position BETWEEN 125400 AND 125410
ORDER BY id ASC

which runs fast, since this does not need a full index scan.

You can either update this column in your application, or write a database trigger to do this automatically.

The initial effort is quite high I admit. We used this trick a few years ago on a high write frequented, quite large table, and like I said it cost some effort to get it running, but when the solution was in place, the performance gain was overwhelming.


Improve reading speed of MySQL table storing stock prices (100 Mil. entries)

This does not answer the whole question but as a first hint, I would suggest changing the order of the columns in the definition of the unique key, from:

`unique_entry` (`stock_id`, `datasource_id`, `price_time`, `price_time_frame`)

To:

`unique_entry` (`stock_id`, `datasource_id`, `price_time_frame`, `price_time`)

This puts first all the column that come into play in the where predicates, and then the ordering column, so this gives the database a better chance to take full advantage of the index.

Also: do you really need select *? The more columns you get, the bigger the payload the database has to process (and return). You should enumerate the columns that you really want. Ideally, you would only extract columns that belong to the index: this makes the index covering for the query, and the database might be ble to execute the whole query by looking at the index only.


I don't know if it's the best way, but you can always pull the binary log and let it execute somewhere else.

A MySQL node can be Master AND Slave at the same time, but it cannot be Slave of two Masters.

See this similiar post:

https://stackoverflow.com/questions/1576603/is-it-possible-to-do-n-master-1-slave-replication-with-mysql


Reading up on Kimball dimensional modeling, use of a surrogate key, in particular an IDENTITY, will help reduce fragmentation caused by page splits as you'll be appending rows at the end of leaf pages vs. attempting to insert them in the middle if the keys are not in the order of the index (ascending or descending depending on how the index was defined).

But, if the surrogate key will not be used in joins with other tables or in WHERE criteria, use of the surrogate key as a clustered index might not provide any additional benefit after the initial data import. If you keep the composite clustered key, sorting the source data in the order of the six-column composite key prior to import should avoid the page split fragmentation you're witnessing.

As for a choice of clustered index, the best candidate may be the column(s) that would be used in your most common/critical queries for WHERE criteria or joins. Use of a NCI will require a bookmark lookup to obtain the remaining values in the table if needed in the resultset.


How to join SQL tables where value is Between min and max value

Following is the SQL Server version in steps to work on IPs Range this might help you to convert in SqL

Step 1: Create function [usp_stringpart] in your Database

Create By: Aasim Abdullah
Description: Function takes a string and section number of string which
is required and separator, which separates different
sections of given string

Create FUNCTION [dbo].[usp_stringpart]
    (
      @InputString NVARCHAR(1000),
      @Section SMALLINT,
      @Separator NCHAR(1)
    )
RETURNS NVARCHAR(1000)
    BEGIN
        DECLARE @StartPosition INT,
            @EndPosition INT,
            @Cycle INT,
            @ResultString NVARCHAR(1000)
        SELECT  @Cycle = 0, @StartPosition = 0
        WHILE @Cycle < @Section - 1
            BEGIN
                SELECT  @StartPosition = CHARINDEX(@Separator, @InputString,
                                                   @StartPosition) + 1
                IF @StartPosition = 1
                    SELECT  @Cycle = @Section
                ELSE
                    SELECT  @Cycle = @Cycle + 1
            END
        SELECT  @EndPosition = CHARINDEX(@Separator, @InputString,
                                         @StartPosition)
        SELECT  @ResultString = LTRIM(RTRIM(SUBSTRING(@InputString,
                                                      @StartPosition,
                                                      CASE @EndPosition
                                                        WHEN 0
                                                        THEN ( LEN(@InputString) + 1 )
                                                             - @StartPosition
                                                        ELSE ( @EndPosition - @StartPosition )
                                                      END)))
        RETURN @ResultString
    END

Step 2: Add Column to save integer value of IPs

ALTER TABLE TableContainIPs ADD IPIntegers BIGINT

Step 3: Convert IPs to Integers

UPDATE TableContainIPs
SET  IPIntegers  = CAST(
                        (CAST( TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),1,'.')AS BIGINT) *100000000000)
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),2,'.') AS BIGINT) *100000000)
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),3,'.') AS BIGINT) *100000)
                        +(CAST(TableContainIPs.dbo.usp_stringpart(REPLACE(IP,',',''),4,'.')AS BIGINT) *100)
                       AS BIGINT)
 FROM TableContainIPs

Step 4: Get IPs

select P.IP, S.*
FROM  TableContainIPs P
inner JOIN IPLibrary S
ON P.IPIntegers BETWEEN s.IPasINTFrom AND s.IPasINTTo


PostgreSQL truncating values of real numeric type

From https://www.postgresql.org/docs/9.2/static/datatype-numeric.html we read:

Name    Storage Size    Description                 Range
real    4 bytes     variable-precision, inexact     6 decimal digits precision

and precision is defined as such: "The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point" (same definition for all types, note how in the same table decimal and numeric have their precision defined separately from left and right of the decimal point)

You do have below:

  On most platforms, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits.

But that (more than 6 digits of precision) depends on both OS, processor, and compilation.

From your set, you have many numbers over 6 digits in precision, and unfortunately in your case you seem to have exactly only 6 digits available to you as precision, hence all these numbers are not handled as you wrote them:

"2"     "150.8308"
"3"     "150.8316"
"4"     "150.8324"
"5"     "150.83321"
"7"     "150.83479"
"8"     "150.8356"
"9"     "150.8364"

You have this explicit warning in the documentation:

  Rounding might take place if the precision of an input number is too high.

Which explains your observation as if it had rounded reads 3 and 4 to 150.832 since 3 is 150.8316 (rounds naturally to 150.832 because the final 6 is more than 5) and 150.8324 (rounds naturally to 150.832 because the final 4 is less than 5)

See also the bit about the extra_float_digits configuration item.

You solve the problem going to double precision because in that case you have 15 digits of precision...

But the "real"(!) solutions are:

if possible, upgrade your PostgreSQL as 9.2 is not supported anymore,
and more specific to your problem, use appropriate data types, if you need fixed precision for your data set you need at least numeric(8,5) right from the beginning.


The post you mention does not say "modeling is done in 3NF form" in both relational and dimensional models. It only states that 1) star schemas are common in dimensional models and 2) 3NF schemas may also be used. It's an over-simplified statement but keep in mind the post is titled "Data Modeling Explained in 10 Minutes or Less" :).
In a nutshell: the biggest difference between dimensional, tabular and relational modeling is the measure of duplicate data being stored. Dimensional and tabular models contain (much) more duplicate data (de-normalized), but that allows them to be able to quickly 'slice and dice' it. Relational models do their best to store data only once in the model (normalized), allowing for fast transactional processing.
One does not exclude the other. It all comes down to which model provides the best performance and widest range of possibilities. And that, in turn, boils down to using the right tool for the right job.
Which leads me to your last question: relational models are commonly found in OLTP systems and dimensional (or tabular) models are commonly found in OLAP systems.


XAMPP MySQL troubleshooting with MySQL Server

i suspect this

  2017-11-24 17:08:48 9368 [ERROR] Unknown/unsupported storage engine:
  InnoDB

is the problem.

Try deleting

ib_logfile0 and ib_logfile1 from your MySQL directory


Only the timestamp syntax is deprecated, not the whole datatype. According to this, just use the rowversion syntax going forward.

For concurrency checks, rowversion is what you're looking for. The timestamp synonym still works, but is just an alternate name for rowversion. There are no other datatypes that behave similarly.


Where can I find a compatible version of SSMS for SQL Server 2016 CTP?

The SSMS November 2015 preview works against the SQL Server 2016 CTP versions. I actually use this version of SSMS (November 2015 preview) against my local 2012 and 2008 R2 instances and don't have any problems.


Are there any approaches that I should consider given I'd like to keep updates to a minimum, updates atomic, and retrieval queries as simple and "performant" as possible?

I think of this as an esoteric variation of the "True Fractions" approach in that blog post — the advantage being that there is no need to introduce a user-defined type (at least if you are using Postgres).

This works using the natural ordering properties of varbit and mapping a sequence of them onto a binary tree such that it is always possible to generate another varbit value between any two existing adjacent values:

                          _____ 1 ____
                   ______/            \______
              _ 01 _                          11
            _/      \_                    _/      \_
        001            011            101            111
       /   \          /   \          /   \          /   \
    0001   0011    0101   0111    1001   1011    1101    1111

create table foo(id serial primary key, userorder varbit unique);

create function adj(orig varbit, b varbit) returns varbit language sql as $$
  select substring(orig for length(orig)-1)
       ||b
       ||substring(orig from length(orig));
$$;

create function f(l varbit, h varbit) returns varbit language plpgsql as $$

begin
  if l is null and h is null then return B'1'; end if;
  if l is null then return adj(h,B'0'); end if;
  if h is null then return adj(l,B'1'); end if;
  if length(l)>length(h) then return adj(l,B'1'); end if;
  return adj(h,B'0');
end;
$$;

insert into foo(userorder) values(f(null,null));
select * from foo order by userorder;

id | userorder
-: | :--------
 1 | 1

insert into foo(userorder) values(f(null,B'1'));
select * from foo order by userorder;

id | userorder
-: | :--------
 2 | 01
 1 | 1

insert into foo(userorder) values(f(B'01',B'1'));
select * from foo order by userorder;

id | userorder
-: | :--------
 2 | 01
 3 | 011
 1 | 1

insert into foo(userorder)
values(f(B'01',B'011')),(f(B'011',B'1')),(f(B'1',null));

select * from foo order by userorder;

id | userorder
-: | :--------
 2 | 01
 4 | 0101
 3 | 011
 5 | 0111
 1 | 1
 6 | 11

dbfiddle here

If you initially load a lot of ordered rows, you may want to use some other algorithm for generating the initial userorder values as you will hit the worst case for space usage (each row will use one more bit for userorder than the row before). You could step through same-length values for a sufficiently large number of bits (eg for 8 values: B'0001', B'0011', B'0101', B'0111', B'1001', B'1011', B'1101', B'1111').


After doing latest windows10 update 1909 SQL Server Management Studio 17 not working

Two options:

Install "Microsoft Visual C++ 2015 Redistributable"

From some reason the link that worked yesterday now does not work (thank you Microsoft!). The working link is The latest supported Visual C++ downloads

Upgade to a newer version from SQL Server Management Studio 18.4

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL Server and databases. Use SSMS to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts.
Use SSMS to query, design, and manage your databases and data warehouses, wherever they are - on your local computer, or in the cloud.
SSMS is free!

My, personal opinion, choose option2. SSMS 18.4 is available since 4 November 2019 and has (AFAIK) the same functionality compared to SSMS 17.x


If this gives you an error:

insert into mytable values('2019-03-31 02:06:29')

But this doesn't:

insert into mytable values('2019-03-31 03:06:29')

You are most likely using a EU-like timezone, in particular, CET (I strongly suggest not to use tz on databases, and just use UTC, it is just easier). EU (and other countries) changed the clocks from 2 -> 3 am that Sunday for summertime adjustment, so it is not possible, if using such a TZ, to insert a '2019-03-31 02:XX:XX  ' data, as such data is invalid for that timezone.

Do:

SELECT @@time_zone;

To know which timezone you are using. If it says SYSTEM, check it on the os.

Fix: Do not insert it, that clock time didn't exist, either it happened before 2 or after 3. Or fix your timezone of your database or os to match the one you are trying to insert.

Proper fix: Do not handle timezones on the database (never, ever try to implement then on your own), do it only at presentation time, and use UTC for your application backend. Set your servers also to use UTC.


How should I handle constraining options in a database?

Constraint is the exact keyword you'll want to look into (so it was coincendetaly good verbiage you used).
It depends on the database system you end up using (as they each have their minute and not so minute differences) but in Microsoft SQL Server, for example, they call it a "Check Constraint".
You can read about Check Constraints and how they limit a field to specific values here: Create Check Constraints
Specifically it sums it up by saying:

You can create a check constraint in a table to specify the data
values that are acceptable in one or more columns.

For further supplemental reading, W3Schools has a succinct page on them here: SQL CHECK Constraint
They also provide some examples of code implementation across multiple database systems.
Also, if you wanted to use a Table to still hold the values that are enforced by your Check Constraints, in some database systems this is possible. Here's a StackOverflow post with a good example using a Function in the Check Constraint that can reference your values Table: Can a Check constraint relate to another table?


Check the time that PostgreSQL is taking to automatically create existing indexes when you do bulk insert using copy command from Spark

As long as all indexes are B-tree indexes, modifying them all should take roughly the same time.
Of course, an index with an expensive expression will take somewhat longer, as well as an index on a string column with an expensive collation.
You could compare the duration of CREATE INDEX statements on a filled table – if an index takes twice as long to build as another one, it will probably also take about twice the time to modify.
If the amount of newly loaded data is large, it may be faster to drop and re-create the indexes. You'll have to experiment.


Small Squares or Blocks after string in table field

Some checks

Do both your Dev and Prod link to the same Navision DB?
Compare the rows in sys.servers, especially the is_collation_compatible column
Is the linked server via an ODBC/DSN or is it simple "SQL Server"? If ODBC then are settings the same in the DNS?

I suspect number 2 or 3 if the all your servers link to the same box.


You might use NOT IN instead:

SELECT DISTINCT maker
FROM Product
WHERE type='pc'
  AND maker  NOT IN
 (
    SELECT maker
    FROM Product
    WHERE type='b'
      AND maker IS NOT NULL -- to prevent empty results
 );

Caution: This will not work correctly with a NULL maker.

The recommended solution is NOT EXISTS, but you need a Correlated Subquery:

SELECT DISTINCT maker
FROM Product AS p1
WHERE type='pc'
  AND NOT EXISTS
 (
    SELECT maker
    FROM Product AS p2
    WHERE type='b'
      AND p1.maker = p2.maker -- correlation to the outer SELECT
 );

If MySQL supported EXCEPT it would be a simple

SELECT maker FROM Product WHERE type='pc'
EXCEPT
SELECT maker FROM Product WHERE type='b';


Update large table with millions of rows in Azure SQL Server, in Parallel

Why not update the table with a single UPDATE statement?
If your database is set to READ COMMITTED SNAPSHOT and you can afford to block other sessions from writing to the table during the update, that's the simplest option.
Otherwise update them sequentially in batches (option 2).  Running lots of parallel DML statements (Option 1) is too complicated and too prone to blocking and deadlocking issues.  IE it's not going to solve all the problems of using a single UPDATE statement, and it introduces new ones.


How to make a function return a dynamically named column?

No it's not possible in functions, independently of their language.
The structure of a function result set is always static, just like the result set of any given query. In fact it's related, as this structure (column names and types) must be  incorporated into the SQL query that calls the function.

Remember that a client application must be able to prepare a query and obtain a description of its results before executing it. Also a prepared query can be repeateadly executed with different parameters, and cannot return a different structure across executions. It would be impossible to guarantee that property if a function could independantly and dynamically decide what structure it wants to return.

I'm not sure whether the documentation makes that point explicitly somewhere  (of course it tends to describe what can be done, not what is impossible) but here are some bits in the protocol flow documentation that support this answer:

  The Describe message (portal variant) specifies the name of an
  existing portal (or an empty string for the unnamed portal). The
  response is a RowDescription message describing the rows that will be
  returned by executing the portal; or a NoData message if the portal
  does not contain a query that will return rows; or ErrorResponse if
  there is no such portal.

In future versions (PostgreSQL 11 or beyond), we might have stored procedures, which are not called through a SQL query but through a special CALL statement that is not tied to a specific structure of result set. In fact, a stored procedure should be able to return several result sets with different structures, without prior declaration of the return types. This is currently being worked on and discussed in the -hackers mailing list (see Dynamic result sets from procedures and related recent threads on SQL procedures).

But as of PostgreSQL 10, there is no way to practically get dynamic result sets outside of using encapsulating types like JSON or XML.


How to simulate bad backup file with 'ultraedit'

You don't need UltrEdit to destroy a backup file.
Actually, this really powerful text editor will allow you to nose around inside a backup file and even save it without rendering the file completely unusable ...

Not that you should, obviously.

Good old Notepad is far more capable of causing this sort of damage.

Just open [a copy of] a Backup file and save it again.  Mischief Managed.

Notepad's lack of acknowledgement of Operating Systems other than Windows - and the line endings that they use - will cause it to rewrite the file with extra characters that make its contents complete nonsense to Sql Server.


How does functional index in MySQL 8.0.13+ improve query performance?

So it looks like (at least to me) the functional index is not stored at all, and only built on the fly during executing a query. Is this how functional index is used?

Please notice that columns are virtual, the indexes are as real ("materialized") as any other index. That means that indeed, if you request data from the table from a virtual column, it is generated at the moment, but if you use the index, it will be pregenerated on creation, and updated on every insert/update/delete.

That means that if you have a query such as this one:

SELECT * FROM revision WHERE substr(rev_timestamp, 5, 2) == '09'

(get all rows generated in september of any year)

You don't need to create a separate real column with substr(rev_timestamp, 5, 2), you can create a virtual one (which will take no more table space) and generate a real index on it, so you can run:

SELECT * FROM revision WHERE virtual_col == '09'

which should use the new index. Of course the index will take some space, but any index does (and modifying non-unique indexes after UPDATEs is very fast on MySQL). Also think that in most cases you don't need to index the whole column- this is very useful and optimized to index optional fields on JSON type columns without duplicating the whole, large content field.


Since you're using SQL Server 2014 I would use resource governor to limit the amount of IO. Obviously this will make the processes take MUCH longer and you still may have timeouts.

There is no way to disable the IO messages and potential issues except to get a better IO subsystem or do less IO intensive items.


Cannot connect from elsewhere than a console

Probably mysql doesn't listen the TCP sockets at all and console client is connected via filesocket. Try to list all network sockets listened at your host:

ss -l | grep 3306

The other possibility is that you have turned skip_name_resolve variable ON that disable using of localhost instead of 127.0.0.1.


Conflict resolution for hash-backed unique constraint of large text bodies

I don't know why the results of a biochemical experiment would be enforced to be unique in the first place, but you can use an exclusion constraint that tests for equality.  It will automatically resolve hash collisions by doing the character-by-character comparison.
alter table foo add constraint foo__bar__un exclude using hash (bar with =);

But you should upgrade to something newer than 9.6.


High Level Permissions For an Application Owner

A short answer

I had similar issues when I was a BI DBA, trying to control deployments without blocking the business needs.
And generally prevent yet another badly coded csv-parsing function to add to the current 13 or so already pushed to live.

You don't need sysadmin to deploy
Depending on exact need, you need one of ddl_admin/ALTER SCHEMA/db_owner

This way the scope of a screw up is limited to the database and/or schema.

ALTER SCHEMA is very useful for limiting changes and I've done this before.

Do these folk needs to modify security for example? Most likely not.

I'd also implement some kind of DDL auditing (I have a server level DDL trigger) that writes to a DBA-only database. This way all DDL is logged


Oracle: dbms_repair outputs zero errors about known corrupted object

What's left to attempt?

You can just instruct the database to skip corrupt blocks on a table scan:

begin
dbms_repair.skip_corrupt_blocks(
  'USER',
  'TABLE_NAME',
  dbms_repair.table_object,
  dbms_repair.SKIP_FLAG);
end;
/

Then scan and move the whole table (except corrupt parts):

alter table user.table_name move;

Finally disable skipping corrupt blocks:

begin
dbms_repair.skip_corrupt_blocks(
  'USER',
  'TABLE_NAME',
  dbms_repair.table_object,
  dbms_repair.NOSKIP_FLAG);
end;
/

Alternatively, you can set event 10231 and run the export:

alter system set events '10231 trace name context forever, level 10';

Then run your export. Finally, you can disable the event for future sessions by:

alter system set events '10231 trace name context forever, off'

and

dbms_system.set_ev for existing sessions. Or just simply restart the instance.


Effect on performance on retrieval of image column from a table with half a billion records on remote server

You are doing a local join over a huge table, which is tremendously slow. Adding a large makes it even slower, to the point that it times out. You need to redesign your whole logic so that you retrieve only the needed rows from the 1B table, ie. force the filtering to occur remotely. This is not a simple query rewrite, I think you really need to go back to the drawing board and review your whole ETL process, and perhaps use a tool that is actually designed to handle such process, like SSIS (as a side note, SSIS flows that involve a custom W3C common-log-format parser are all to common  and many had rolled their own from logparser.exe->csv->rolap cube... but I digress). While you're at it, also stop using deprecated types, like IMAGE, and use the appropriate VARBINARY(MAX).


While I've never tested this, I would try turning on snapshot isolation on the reporting database. If all the existing queries run using READ COMMITTED (the default), turning on READ_COMMITTED_SNAPSHOT could fix the problem with a flick of the switch.

Using one of the snapshot isolation levels should eliminate the deadlocks completely as the single writer won't block the reader. As a nice byproduct it should also reduce the possibility of inconsistent data ending up in the reports due to concurrently-applied replication changes.


Removing some English token words (such as “are” or “or” etc.) from the system stoplist of full text search?

You can turn off the system stoplist and create your own, eg

ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = OFF

CREATE FULLTEXT STOPLIST myCustomStoplist;

ALTER FULLTEXT STOPLIST myCustomStoplist ADD 'someWord' LANGUAGE 'English';

ALTER FULLTEXT INDEX ON dbo.yourTable SET STOPLIST = myCustomStoplist

If you create an empty stoplist, it means all words are considered, or to put it another way, words that were previously considered noise words are no longer.  You will need to add some words to avoid index bloat, and as mentioned in my other post, if you really leave the word 'a' out of your stoplist, searches for 'a' will return most of your documents, somewhat defeating the object of full-text index ( ie tightly scoped searches ).


Two-step Replication between SQL 2008 and 2017

I need to replicate this data to a 2017 server, which would require a 2017 distributor.

A SQL 2017 subscriber can use a SQL 2014 Distributor.   See:

https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-replicated-databases

https://blogs.msdn.microsoft.com/repltalk/2016/08/12/suppported-sql-server-versions-in-replication-topology/


What program to use for the best compression ratio for DB2 log files?

In version 10.1, the logs are internally compressed, that means that more information is stored in those files.

But if you want to compress them in order to use less space in your disks, I do not see this like a very good practice, because logs are the only part where the latest data is stored in, and if you want to do a restore, you will have to use the log files, to restore the database with an extra process, and probably, to perform a restore you will need even more space than before (the compressed file + the expanded file).

Presonally, this compress operation adds "noise" to the disaster recovery strategy, how are you going to check if a compressed file is ok or not?

Finally, if you want to do a thing like this, you could use the userexit option in order to implement your own process to take the online logs and pass them as offline logs. In this process you could compress and extract the files.

BTW, why don't you use circular logs? Any enterprise knows the importance of archive logs.


Foreign Key - One Child Table to Multiple Parent Tables

I'm not sure I've seen any DBMS support multiple parents for foreign keys like this, it is probably assumed that something needed this is not in good form (the linking field is explicitly overloaded as it can refer to several distinct types of thing, which means it dos not fit an accepted "normal form").

You could instead have a general "AccessableThings" table that contained just the IDs, and then have the UserItems/SystemItems/OtherItems/etc refer to that via FK (to ensure everything in those tables also appears in the IDs table) as well as AccessControl doing so. If you have other generic fields that apply to all types ("display name" for instance) then you might include them in the AccessibleThings table to so you can avoid joining in all the other tables when all you need are generic details.

CompanyItems            AccessibleThings          AccessControl
============            ================          =============
CompanyID PK FK ------> ThingID PK      <--.      AccessRuleID PK
<companyfields>   |     <genericfields>    |----- ThingID FK
                  |                               <AccessRuleFields>
SystemFiles       |
===========       |
FileID PK FK -----|
<filefields>      |
                  |
SomethingElse     |
=============     |
SomethingID ------|

This will enforce things being correct as entered, though can not delete AccessControl on the delete of an Item, just on delete of the AccessibleThings row (which can also cascade to the Items tables of course) so this table becomes the central object in that regard. Obviously your data access layer (or business logic, but the data layer is more likely the correct place) will need to know about the new structure at each relevant point.

Alternative:

You could also enforce your more complex data integrity relationship entirely via triggers, though you need to be very careful with both complexity and efficiency here otherwise you could create a veritable zoo of nightmares for later.


How to calculate AWS RDS Data Transfer Pricing

Based on my experience, AWS does not charge for the query output of RDS Database of MySQL engine, but it charges for Aurora Engine.


Limit number of user logins in SQL Server

Logon triggers are special. Not going too far into it, they must exist in the master database and not any other, thus the answer to your question is: "It can't."

https://msdn.microsoft.com/en-us/library/bb326598.aspx

-Sean


Is there a setting to force an error when asigning a varchar longer than a variable?

This should work for this problem:

declare @code varchar(10)
declare @input VARCHAR(200)
set @input = 'Set your input code here'

IF(LEN(@input) <= 10) -- make sure this number is same as length of 'code' variable
    SET @code = @input

ELSE
BEGIN
    SELECT 'Inputted string is too long for the variable.'
    RETURN
END

select @code


In SQL server 2000 trace file generated in .trc format for more details please refer below link
http://technet.microsoft.com/en-us/library/dd277388.aspx


How to extract fields of anonymous row type in PostgreSQL

New in v13

Allow ROW expressions to have their members extracted with suffix notation (Tom Lane)
For example, (ROW(4, 5.0)).f1 now returns 4.

select (ROW(1,'Hello',false)).f1

Note there are some gotchas. You may need to explicitly cast type
select (ROW(1,'Hello',false)).f2

ERROR:  failed to find conversion function from unknown to text

select (ROW(1,'Hello'::text,false)).f2

See db<>fiddle for more
The relevant commit in source appears to be 8b7a0f1d11 with a discussion at https://www.postgresql.org/message-id/flat/10872.1572202006%40sss.pgh.pa.us
I don't know at this time of any official documentation, but I'll shout out Jack@TA for showing me the new trick.


Replace all Numeric Values with Single Character

Based on your latest comment, I made few changes to your original example.  The following logic prevents repetitive % from being stuffed.

See if this works for you.

DECLARE @Temp NVARCHAR(150) = 'Numara_03_09_2018_07_41_04_OP_NIP_CSA_5739038.XLS'
DECLARE @KeepValues AS VARCHAR(50)

SET @KeepValues = '%[^a-z_%. ]%' -- keep underscore and space

WHILE PatIndex(@KeepValues, @Temp) > 0
BEGIN
    SET @Temp = CASE
            WHEN substring(@temp, PatIndex(@KeepValues, @Temp) - 1, 1) = '%'
                THEN Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')
            ELSE Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '%')
            END
END

SELECT @Temp

Result

Numara_%_%_%_%_%_%_OP_NIP_CSA_%.XLS


How to get the current row in postgres function?

You don't really need a function for that, you can use conditional aggregation:

SELECT "user",
        count(*) filter (where event = 'start') AS start,
        count(*) filter (where event = 'end') AS end
FROM stats
GROUP BY "user";

You can pass a complete row to a function when you declare a parameter with the type of the table:

CREATE OR REPLACE FUNCTION decode_event (p_row stats, p_name varchar(255))
RETURNS integer AS $result$
  BEGIN
    if p_row.event like eventVar
      then return 1;
    else
      return 0;
    END IF;
  END;
$result$ LANGUAGE plpgsql;

Then you can use it like this:

SELECT "user",
        sum(decode_event(stats, 'start')) AS start,
        sum(decode_event(stats, 'end')) AS end
FROM stats
GROUP BY "user";

Note how the table alias is passed to the function. If you give the table a different alias, e.g. from stats as s, you need to pass that alias: decode_event(s, ...)


Why is it necessary to grant access to PostgreSQL sequences?

pg_dump needs to dump the current position of the sequence, so that it can be restored. Otherwise, the restored sequence would start at 1 (or whatever its start value is) again and would generate duplicate keys.
Granting SELECT on the sequence should be enough for that purpose. Then the user can see the current value, but cannot increase or reset the sequence.


How can I log which queries are in a distributed transaction on an MS SQL server?

You should be able to track the DTCTransaction event in SQL Trace or XEvents.

Or you could just shut down MSTDC (not in production, of course) and see what fails.

Also note that updates to .NET's SqlClient enables some distributed transaction scenarios with Azure SQL Database without MSDTC.  See

Distributed transactions across cloud databases


I will be using MS SQL terms below but you can translate them into the DBMS that you will be using. I also made some assumptions that seemed logical, so you can drop those if they do not fit your model. I would setup the tables in the following manner:

Person Table
PersonID int PK
FName Varchar(50)
LName varchar(50)
DoB Date FK

Name Table
Name    varchar(50) PK
Meaning varchar(MAX)
Origin  int FK

DOB Table
DoB                 date    PK
AstrologicalSign    int FK

Wedding Table
WeddingID   int PK
HusbandPersonID int FK
WifePersonID    int FK

Origin Table
OriginID            int         PK
OriginDescription   varchar(50)

Astrological Sign Table
AstrologicalSignID          int         PK
AstrologicalSignDescription varchar(50)

I am a visual person and it helps me to see things laid out graphically, so I am attaching a diagram as well.

The personID, astrologicalsignID, weddingID and originID are all setup as identity fields that auto increment. I can provide a SQL backup of the database if that would help you.


CHECK (NOT (flag_afil = 1 AND fec_renum IS NULL))


Indexes involved in intervals collision detection

This is exactly the kind of query that can benefit from a composite, or multiple-column index: when a single statement filters by multiple columns in the same table. The order of columns in the index will depend on your use cases, but in general the order in which the columns appear in the DML statement is fine, in your case (assuming MySQL but the concept is the same for most RDBMSs):

ALTER TABLE 'venue'
ADD INDEX ('startdate','enddate')

See the documentation for other alternatives, such as adding a column containing the hash of multiple columns.


Does SQL Server use multiversion concurrency control (MVCC)?

Does SQL Server really implement MVCC anywhere

Yes, since SQL Server 2005.

The SQL Server terminology is "row-versioning isolation levels". See the product documentation tree starting at Locking and Row Versioning. Note in particular that there are two separate "MVCC" implementations, read committed isolation using row versioning (RCSI) and snapshot isolation (SI).

  and how does that reconcile with the idea of with (tablock, holdlock) if it does?

Using that combination of hints serializes access to whole table(s). It is the least concurrent option available, so using these hints should be very rare. Whether a particular use could be replaced with RCSI or SI isolation depends on the specific circumstances. You could ask a follow-up question with a specific example if you want us to address that aspect in detail.

You might also like to read my series of articles on SQL Server isolation levels.


MySQL Error 1062 after reboot node in multi master replication

You should use pt-table-checksum to see the differences between the source and the replica. If the differences are many, importing data again from the source could be the fastest solution. Otherwise, you could restart MySQL with --slave-skip-errors=1062, fix the errors with pt-table-sync, and then restart MySQL normally.
To understand how these tools work and how they are used, take a look at this article: MySQL replication primer with pt-table-checksum and pt-table-sync.
You may also want to take a look at another tool called twindb-table-compare, which is complementary to pt-table-checksum. It reads the diffs generated by pt-table-checksum on the source and on the replica, and prints them in a format that is easier to understand, similar to the output of Linux utility diff.


Extending condition in a join with Postgres

select b1, b2 from X where id = A.id limit 1
-- and if the query on 'X' fails then try:
select b1, b2 from Y where id = A.id limit 1

In general, you can implement this with UNION ALL, and then LIMITing the entire query so that the second query is taken only if the first one did not return something:

select b1, b2, 1 as order_me from X where id = A.id
union all
select b1, b2, 2             from Y where id = A.id
order by order_me
limit 1


why is the name `loop` treated differently in PostgreSQL

The word LOOP is a reserved word in PL/pgSQL.  If you want to use it as a column name, you need to quote it.


get data from table having records in hindi

Safe:

SELECT *
FROM table
WHERE urls RLIKE '[^ -~]'

Strong:

SELECT *
FROM table
WHERE urls RLIKE '[ऀ-ॿ]'


Postgres allows to join tables in an UPDATE statement:

update table_1
   set date_of_incident = t2.date_of_incident
from table_2 t2
where table_1.incident_id = t2.incident_id;

This assumes that incident_id is unique in both tables.


The sum is only computed once.

I verified this using

create table mytable (id int, hours int);
insert into mytable values (1, 60);
select sum(hours) from mytable group by id having sum(hours) > 50;

and then used a debugger to check how many times int4_sum (the transition function behind the sum aggregate) was called: once.


How to restore from a backup of a very old version of DB2 (version 6.1 backup)?

Upgrade via restore is usually supported for databases two versions down, so to move from DB2 6.1 to DB2 10.1 (current today) you'd need to move to DB2 8.2 first, then to DB2 9.5, then to DB2 10.1. There are many things that have changed between all these versions, so it's very unlikely that the process will be smooth. Your best option would be to extract the database DDL statements and data, then create a new DB2 10.1 database and load data into it.


How long takes populating catalog in SQL Server 2008 R2 when update a row?

I think ther is no answer. IIRC the change gets logged and full text index processes them async one by one - so the time is "random" - i.e. depending on the amount of changes, the size of the queue.

If you ahve things properly configured it should show up "after some time", but that should - unless you do a TON of updates in one moment - be relatively fast.

This is in line with the business approach (text repositories) where a time delay can be accepted.


Does the MySQL primary key affect the efficiency of a SELECT query?

All things InooDB, the PRIMARY KEY has a major role. As ypercube mentioned towards end of answer, InnoDB uses a clustered key structure, and the key used for clustering is the PRIMARY KEY.

It is very important to have as small footprint for PRIMARY KEY as possible: the depth of the index is the minimum payment you have to pay so as to get to a leaf node. The depth of an InnoDB PRIMARY KEY is the starting payment for any index search.

My general advice is to use AUTO_INCREMENT primary keys for InnoDB. Here are three blog posts of mine which support this:

Reasons to use AUTO_INCREMENT columns on InnoDB
Simple guideline for choosing appropriate InnoDB PRIMARY KEYs
Reducing locks by narrowing primary key


It is difficult to say why MySQL Optimizer is choosing a different join order for 5.7 without seeing the optimizer trace for the query.  However, you can force the same join order as 5.5 by using STRAIGHT_JOIN instead of INNER JOIN.  That will tell MySQL to process the tables in the order they are listed.


Math first: you moved 150M documents in 2 days, which is roughly 860 documents per second including metadata and indices, where reading and writing all occurs on the same machine. That is not what I would call slow. The description coming to my mind is "lightning fast". ;)

Since there is no real distribution of the write load, an easy way to speed things up is to add two or more machines.

A few notes: sharding production data on non-replica shards is dangerous, to say the least. If one of the shards fails, the data contained is permanently unavailable until you get the shard up and running again. Plus, since there was no server to write to for a specific key range, values of that range can not be written. If the shard was a replica set, the failure would lead to the election of a new primary, to which all writes and (depending on the configuration) most or even all reads would go.

_id can be used as a shard key, however it should be hashed.


Automatic fail over in Always on availability group with 3 Node in synchronous mode and 1 Node in Asynchronous Mode

My question is if the primary goes down which node among the remaining 2 replica will be failed over.

The node that is set to Synchronous with Automatic failover will be the chosen one to failover when the primary goes down.  You can only have one node set as the automatic failover target, see the docs for 2014  (it was the same for 2012):

  To configure an availability group for automatic failover, you need to set both the current primary replica and one secondary replica to synchronous-commit mode with automatic failover.

On SQL Server 2016 and newer, you can have a second automatic failover target (it's 3 including the primary):

  To configure an availability group for automatic failover, you need to set both the current primary replica and at least one secondary replica to synchronous-commit mode with automatic failover. You can have up to three automatic failover replicas.

Remember that an Async replica might have data loss if you try to do a force failover.


When was the stored procedure created and altered?

The easiest way is to check information_schema.routines metadata table.

You can get the basic list of all procedures and functions with:

SELECT routine_name, routine_type, created, last_altered
  FROM information_schema.routines

You can include some other fields as needed - the exhaustive list is in the MySQL 5.7 reference - 24.21 The INFORMATION_SCHEMA ROUTINES Table

Information schema contains tables that contain the "database metadata" and describe the database's entities: tables, indexes, procedures and many others.
Every relational database has some kind "database catalog" as per Rule 4 of Codd's 12 rules for RDBMSs.

So whenever you are needing some info about database objects on any RDBMS product you can always check the manual for the specifications of the "database dictionary", "database catalog" or similar.

Check here for other of the INFORMATION SCHEMA tables on Mysql 5.7.


Can the 'returning' clause return source columns that are not inserted?

The documentation on the RETURNING clause says:

  An expression to be computed and returned by the INSERT command after
  each row is inserted. The expression can use any column names of the
  table named by table_name. Write * to return all columns of the
  inserted row(s).

This clearly does not apply for columns from an other table.

Though I don't really get the point of the problem (i. e. why you do this - I imagine it is because it is a bit too abstract version of the original one), a possible solution can be:

WITH w AS (INSERT INTO t(rnd) VALUES (random()) RETURNING *),
     x AS (INSERT INTO t(rnd) SELECT random() FROM w RETURNING *)
SELECT w.rnd, x.rnd
  FROM w, x;

That is, you can put more than one writable CTE to the beginning of a query.  Please see this in action on dbfiddle.


How to calculate quantity of WAL in db without archiving?

There is no easy way as such to get a count of the WAL Files. But what you could do is run the SQL below at a certain time and then run it again 24 hours later. Getting the difference should give you some indication of the number.

SELECT COUNT(*) FROM pg_ls_dir('pg_xlog') WHERE pg_ls_dir ~ '^[0-9A-F]{24}';

Some details Postgreal Admi functions and pgDash blog.


Nested SQL Query (Query a subset of data)

If I understand what you mean correctly (and I'm not sure I do), you should return 2 rows per RowID for a 'matching' result.

Therefore you can do an OR on the key data you are searching for, GROUP BY RowID and count that there are matching results for each subset.

SELECT
  DV.RowID,
  MIN(CASE
        WHEN F.[Key] = 'Title' THEN 1
        WHEN F.[Key] = 'Body' THEN 2
      END
    ) AS Ordinal
FROM DataValues as DV,
INNER JOIN Fields as F ON DV.FieldID = F.ID
INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
AND (
     (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')
OR   (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want')
    )
GROUP BY DV.RowID
HAVING COUNT(*) = 2
ORDER BY Ordinal ASC;

Alternatively, you could do an INTERSECT of the two result sets for all of the RowID values that satisfy both conditions:

SELECT DV.RowID, 1 as Ordinal
FROM DataValues as DV
INNER JOIN Fields as F ON DV.FieldID = F.ID
INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
  AND (F.[Key] = 'Title' AND DV.[Value] = 'Blog Post Title')
INTERSECT
SELECT DV.RowID, 2 AS Ordinal
FROM DataValues as DV
INNER JOIN Fields as F ON DV.FieldID = F.ID
INNER JOIN DataRows as DR ON DV.RowID = DR.ID
WHERE DR.StructureID = 2
  AND (F.[Key] = 'Body' AND DV.[Value] <> 'A Body We Dont Want')
ORDER BY Ordinal ASC;


Formatting out a column value - mm:ss.hh

You could convert to time by adding the seconds to a constant date and then subtracting it:

select timediff(cast(date_add('2001-01-01', interval t.time second)
                     as datetime(3)),
                cast('2001-01-01' as datetime(3))
               ) as ttime
from tableX as t ;

You can then format the time(3) result with the available time formatting function.


Your query is correct and it should return one row (5,6)

DECLARE @table1 TABLE ( a INT, b INT )
DECLARE @table2 TABLE ( x INT, y INT )
DECLARE @table3 TABLE ( x INT, y INT )

INSERT  INTO @table1
        ( a, b )
VALUES  ( 1, 2 ),
        ( 5, 6 ),
        ( 7, 8 )

INSERT  INTO @table2
        ( x, y )
VALUES  ( 1, 0 ),
        ( 0, 0 ),
        ( 9, 5 )

INSERT  INTO @table3
        ( x, y )
VALUES  ( 3, 4 ),
        ( 0, 0 ),
        ( 0, 8 )

SELECT  a, b
FROM    @table1
WHERE   a NOT IN ( SELECT   x AS a
                   FROM     @table2 )
        AND b NOT IN ( SELECT   y AS b
                       FROM     @table3 )


How find all the data loaded on previous day?

Oracle Data Pump Network-Mode Import may be one of possible solutions. You can invoke Data Pump Import in network mode using database link (NETWORK_LINK parameter) and specifying which version of data you want to import with FLASHBACK_SCN or FLASHBACK_TIME parameters.

First, create a database link to you production database on your development database:

CREATE DATABASE LINK prod_db
  CONNECT TO system
  IDENTIFIED BY "hackme"
  USING 'orcl';

Where orcl is the connect string you're using to connect to your production database. You can test if the link is working by querying some table in the production database:

SQL> select count(*) from hr.employees@prod_db;

  COUNT(*)
----------
       107

See Database Administrator's Guide for more info about creating database links.

Then to import data into development database from production database, you would run Data Pump Import like this:

[oracle@oca ~]$ impdp sys/hackme \
SCHEMAS=hr,oe \
CONTENT=ALL \
NETWORK_LINK=prod_db \
FLASHBACK_TIME="TO_TIMESTAMP('13-05-2013 17:35:00', 'DD-MM-YYYY HH24:MI:SS')"

In this example, two schemas are imported, CONTENT specifies that both metadata and data should be imported (default), and FLASHBACK_TIME specifies the time of the snapshot you want to import.

Due to the restrictions of FLASHBACK_TIME:

  The FLASHBACK_TIME parameter pertains only to the Flashback Query
  capability of Oracle Database. It is not applicable to Flashback
  Database, Flashback Drop, or Flashback Data Archive.

You have to ensure that you have enough undo data on your source production database for the snapshot to be consistent, since Flashback Query depends entirely on undo.

Based on your requirements, you would need to guarantee undo retention to at least 24 hours to be able to get the previous versions of the data you're importing. You would also need to size you undo tablespace on the source database appropriately.

To guarantee the retention, connect to your source database and alter your current undo tablespace like this:

SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.

To specify the retention time (in seconds):

SQL> alter system set undo_retention=86400;
System altered.

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_management                      string      AUTO
undo_retention                       integer     86400
undo_tablespace                      string      UNDOTBS1

(86400 is 60 seconds * 60 minutes * 24 hours.)

Sizing the undo tablespace:

SQL> alter database datafile '/oradata/ocaexam/undotbs01.dbf' resize 10g;

You can determine what's the best size for your undo tablespace using Undo Advisor in Enterprise Manager.

If you would like to automate your import process, you could use PL/SQL and Oracle Scheduler as described here.


ERROR: could not connect to the publisher: FATAL: no pg_hba.conf entry for replication connection from host

host replication replicate 192.168.100.22/32 md5

Logical replication needs to connect to an actual database.  The special name 'replication' is only used for physical replication, not logical replication.


Store time series data efficiently without wasting space

First off, a few million rows is nothing to be afraid of. Modern relational database management systems can handle up to trillions of rows on pretty standard hardware, when architected and indexed properly. Secondly, I like to measure things by a minimum of 10 years as a good test of time, so let's take your metric and annualize it over 10 years which gives us closer to ~15.6 million rows, and let's round it up to say 20 million (to account for customer growth etc). We're still in medium sized numbers, as far as number of records in a single table, so no big deal.
To answer your questions directly:
1. "Should I store rows for customers without orders for a given timespan (e.g. the row will be all filled with NULL)? Can avoid it somehow without affecting data visualization?"
A: There's no need to store empty rows. You can create a dates dimension table which will store a single row per date and then you can outer join to it such as SELECT * FROM dateDimensions AS D LEFT OUTER JOIN orders AS O ON D.date = O.orderDate WHERE D.date >= 'some date value' AND D.date < 'some other date value'. (You can join in your other tables here as needed.) This will help keep your table much leaner (maybe that 20 million rows in 10 years becomes 10 million or 5 million roughly) because now you're not storing multiple empty order or empty customer_kpi rows every week for every customer who didn't make an order, you're only storing one row per date (or your dates dimensions table can even be simplified to just storing one row per week - though probably overkill). And outer joining to your dates dimension table will maintain the same visualization you're looking for.
2. Which table structure is more efficient ("thin" table vs "fat" table) given that not all customers buy every week and number of metrics are unlikely to change often?
A: Both table structures have their place, and sometimes a denormalized table structure like your first example structure is better for an OLAP heavy / heavy reporting database regarding performance, but in general normalization is best practice when all things are equal, like your second example structure. Because 20 million rows (worse case number of rows based on my answer to your first question) is really nothing to be concerned about in a single table, and because you get better flexibility with querying about only specific KPIs (which can be more performant by not bringing back a fat row with a bunch of unneeded data), e.g. SELECT * FROM customers_kpi WHERE kpi_name = 'SomeSpecificKPI', I'd personally recommend starting with your second table structure. You can always easily transform it later on to look like your first structure if you need or should you change your mind and want to materialize it as that structure into a new table.


Max function and group by getting differenet count

The first query returns the count of rows across a given load_date.
Whereas the second query gives you the count of rows for maximum load_date.
Say you have 2 dates. First query returns :

LoadDate.           Count(*)
03-04-2014.          12
03-05-2014.           11

Second query returns:

Max(loadDate).      Count(*)
03-05-2014.             23

If you want that the count of just max row be found, then the query will be:

SELECT COUNT(*)
FROM <table name>
WHERE LoadDate = MAX(loadDate);


Live monitoring SQL statements on SQL Server

There's Sql Server Profiler which comes with the installation. It allows you to monitor all SQL statements with various filter criteria. You could also pair it up with PerfMon to investigate performance.

MSDN Sql Server Profiler

I also found this site Receiving Profiler Events. Which allows real-time tracking in .NET.


Postgres requested WAL segment has already been removed (however it is actually in the slave's directory)

I simply took a gamble and copied the one file back to the master which was missing. It worked i still don't know what caused the issue.


Partially contained databases and Windows Logins - best practices?

I was going to just comment the link to the SQL Server 2012 Best Practice security white paper...but found out Microsoft took it off the Internet for some reason. You might be able to find a cached version of it some where online, but I will paste in the contents of contained database section of the document here.

There is also a BOL article for Security Best Practices with Contained Databases, this may be why the white paper was taken off but not sure.

Author of this whitepaper: Bob Beauchemin, SQLSkills; Published January 2012; Applies to SQL Server 2012

  Contained Databases and Authentication

  SQL Server 2012 introduced the concept of contained databases. A
  contained database is a database that includes all of the settings and
  metadata needed for its operation with dependencies on the instance.
  From a security perspective, a contained database makes it easier to
  have user accounts that are limited to a single database. SQL Server
  2012 supports partially contained databases; it does not yet support
  full containment. Although contained databases allow more control by
  the “application administrator” they do have security repercussions.

  Contained databases are interesting for a security point of view in
  that they allow defining users with authentication privileges, i.e.
  users who can log directly into a contained database without a
  corresponding login. Contained databases support two types of users:
  Windows users and groups that can directly connect to the database and
  do not need logins, and users with a password where the password is
  authenticated by the database, not the instance. This is not permitted
  by default; the instance administrator must specifically allow it by
  setting the “contained database authentication” configuration option
  on. To disallow connections to specific contained databases but allow
  access to other contained databases, a login trigger can be used.

  Allowing direct connection of users to a database changes the
  effective threat-level of some existing permissions. For example, the
  permission ALTER ANY USER in a contained database gives permission to
  add user-based access to an instance through a contained database.
  Logins with ALTER ANY DATABASE or CONTROL DATABASE permission could
  also set containment on a database and add instance access through
  users. In addition, contained database users can connect to other
  database in the instance if the guest account is enabled.

  Although you can use the system stored procedure
  sp_migrate_user_to_contained to migrate a database user mapped to a
  SQL login to a contained database user-with-password, contained
  databases’ user-with-password does not use password history and
  expiration policies as SQL logins do, although password complexity
  checks are still used. Therefore, attaching a contained database could
  allow weak passwords. In addition, the password hashes for these
  passwords are stored in the database, not in master. Anyone with
  access to the database file could perform a dictionary attack on a
  separate, unaudited instance.

  There exists the possibility of conflicts, if a login and contained
  database user have the same name. The rule for resolving this conflict
  is that if an initial catalog is specified in the connection string
  and it’s the contained database, access is checking against the user
  based principal, not the login. To ameliorate this possibility, do not
  create conflicting names or specify a contained database as an initial
  catalog in a connection string. In addition, members of the sysadmin
  role should not use initial catalog in a connection string.

  Best practices for contained databases

  Use the default (off) setting for contained database authentication    and only turn this setting on if it is required.
  Protect backups of contained databases using passwords.
  Audit the capabilities of users and modules in contained databases.
  Audit logins that have the ability to set containment, if contained    database authentication is allowed.
  Disable the guest account on databases that share an instance with    contained databases.
  Take care to avoid login/user-with-login naming conflicts
  Avoid connection strings with initial catalog if contained database    authentication is permitted.


Is twitter's character limit a way they found to avoid drastic database fragmentation?

The original character limit in twitter was because it started out as a micro-blogging platform with the option to update via SMS. SMS messages had a limit of 160 bytes. The 140 limit on the message length is because the extra metadata (to identify the user) was up to 20 characters long.
It had nothing to do with things that could affect internal or external fragmentation (rows/page, dynamic/static row sizes, excess page splitting due to key values effectively in random order, ...) or other concerns of the backend database design.


How to track if employees are late which has more than one work schedules or shifts?

If you absolutely cannot add a column to your schedule or employee to reference the shift in question explicitly, you could make assumptions regarding the length of time between a log in and a shift start to determine this.


Assuming that you have full control over the database and can kick everyone out/close all existing process that are connecting to that database, you can use the following statement to force the database into single user mode with you as the only connection, then make your change, then switch it back to multi user.

The T-SQL to do this is:

--Step 1
USE [foo];
GO;

ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO;

--Step 2
ALTER DATABASE [foo] COLLATE <<Colation Name>>;
GO;

--Step 3
ALTER DATABASE [foo] SET MULTI_USER;
GO;

If you want to be nice and identify the processes that are connected to that database first, you can get that information from sys.dm_exec_sessions:

USE [foo];

SELECT
    session_id
    ,login_name
    ,host_name
    ,program_name
FROM
    sys.dm_exec_sessions
WHERE
    database_id = DB_ID()
    AND session_id != @@SPID;


Impact of using UUIDs with an UNIQUE index vs. PK?

I guess you are referring to the InnoDB clustered index performance. If I'm not mistaken, the PK is chosen as the clustered index by default, so any other, secondary index containing the UUID column should not suffer from the same problem.

Alternatively, I think you can create the table without declaring the PK at first, in which case MySQL will create an automatic clustered index based on row IDs. You can then alter the table and add the necessary primary key constraint.


Is MySQL Replication Affected by a High-Latency Interconnect?

The direct answer to your question is Yes, but it depends on the version of MySQL you are running. Before MySQL 5.5, replication would operate as follows:

Master Executes SQL
Master Records SQL Event in its Binary Logs
Slave Reads SQL Event from Master Binary Logs
Slave Stores SQL Event in its Relay Logs via I/O Thread
Slave Reads Next SQL Event From Relay Log via SQL Thread
Slave Executes SQL
Slave Acknowledges Master of the Complete Execution of the SQL Event

As of MySQL 5.5, using Semisynchronous Replication, now replication would operate as follows:

Master Executes SQL
Master Records SQL Event in its Binary Logs
Slave Reads SQL Event from Master Binary Logs
Slave Acknowledges Master of the Receipt of the SQL Event
Slave Stores SQL Event in its Relay Logs via I/O Thread
Slave Reads Next SQL Event From Relay Log via SQL Thread
Slave Executes SQL
Slave Acknowledges Master of the Complete Execution of the SQL Event

This new paradigm will permit a Slave to be closer sync'd to its Master.

Notwithstanding, latency within the network could hamper MySQL Semisync Replication to the point where it reverts back to the old-style asynchronous replication. Why ? If a timeout occurs without any slave having acknowledged the transaction, the master reverts to asynchronous replication. When at least one semisynchronous slave catches up, the master returns to semisynchronous replication.

UPDATE 2011-08-08 14:22 EDT

The configuration of MySQL 5.5 Semisynchronous Replication is straightforward

Step 1) Add these four(4) lines to /etc/my.cnf

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
#rpl_semi_sync_master_enabled
#rpl_semi_sync_master_timeout=5000
#rpl_semi_sync_slave_enabled

Step 2) Restart MySQL

service mysql restart

Step 3) Run these commands in the MySQL client

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
INSTALL PLUGIN rpl_semi_sync_slave  SONAME 'semisync_slave.so';

Step 4) Uncomment the three rpm_semi_sync options after the plugin-dir option

[mysqld]
plugin-dir=/usr/lib64/mysql/plugin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=5000
rpl_semi_sync_slave_enabled

Step 5) Restart MySQL

service mysql restart

All Done !!! Now just setup MySQL Replication as usual.


How can I automate sharding in the presence of continuous reads

Some thoughts on each of the options:

I would say that writing this code in any case is probably a good idea - if something does go wrong on the performance side of things you already have the code written to deal with it in some way, rather than just sending exceptions or timeout messages back to the users.
This sounds like pre-splitting and pre-allocation, which again, is probably a good idea to do anyway - why wait until just before use to create a database?  In fact, this would mirror how mongodb does data file allocation, it always has one empty data file waiting to be filled before it is needed.  The pre-allocation it does is for adding data to a DB though, it can't psychically figure out the need for a new DB, hence that initial allocation may be slowing you down.  Also, make sure you are on ext4/XFS and not using an old kernel to ensure that the allocation is as quick as possible.
I believe that doing pre-allocation is a better option here - I have not heard of anyone using copyDatabase in this manner, so it would require a lot of testing.  Pre-allocating databases in advance though is quite common.
This does sound a bit painful, I think there are better options described above

Overall, if you know you are going to need a new database every hour, create multiple databases in advance of their need.  I know this would mean extra disk usage, but it should be quite predictable (look into the --smallfiles option if your data set is quite small).


Slave stopped : Could not parse relay log event entry

There are 4 possible explanations mentioned in the error message, and a fifth one that isn't mentioned.

the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log),
the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log),
a network problem, or
a bug in the master's or slave's MySQL code.
not mentioned in the message, it seems possible that this condition could be triggered if your slave is an older version of MySQL server than the master, particularly across versions, such as a master running 5.6.x and a slave running 5.5.x.  Slaves must almost always be the same version as the master, or newer.  When upgrading, slaves are done first, master last.

Replication involves the slave requesting a streaming copy of the master's binary log across the network, which is stored in the relay log by the slave IO thread, then executed by the slave SQL thread.

In this case, the slave SQL thread has found incomprehensible content in the relay log, and has concluded that the log was corrupt on the master, or has been corrupted on the slave, or was corrupted in transit.  TCP is "reliable" but its checksums are somewhat primitive and specific corruption patterns can go undetected.

Using CHANGE MASTER TO will discard the slave's relay logs and restart copying at the specified log position, which needs to match Execute_master_log_pos, which is where the slave SQL thread is currently pointing for its next needed event (and isn't valid).

If the log on the master is corrupt, this will not fix things, because the corrupt data will just be transferred again.  In this case, the only option will be to rebuild the slave.

Otherwise, CHANGE_MASTER_TO should remedy the problem if the fault lies in the slave'a relay log (which can be caused by having run out of disk space on the slave, under just the right conditions) or was caused by the network.

The mysqlbinlog utility can be used to validate the logs on both machines, if desired, and will be necessary on the master if you want to confirm the corruption when CHANGE_MASTER_TO doesn't fix the issue.


How to change user privileges from 'Y' to 'N' in MySQL Server?

The GRANT statement enables system administrators to grant privileges and roles, which can be granted to user accounts and roles.
The REVOKE statement revokes one or more privileges from a user account.
So, something like this will most likely do what you need:
REVOKE All ON <DB> FROM user@source;


Update nth element of array using a WHERE clause

How can I select a specific element in "opts" array?

Use the index of the element number as instructed in the manual. For longer paths, the path notation is shorter:

SELECT metadata -> 'options' -> 'opts' -> 0 AS elem0
     , metadata #> '{options, opts, 0}'     AS elem0_path
FROM   product;

Gets the nth element, like the question title asks (JSON array index starts with 0). But your example indicates you actually want the element with "value":"blue". That's not as trivial. You could unnest the nested JSON array with jsonb_array_elements() in a LATERAL join and filter the one you want.

SELECT opt AS elem_blue
--   , metadata #> '{options, opts}' -> (arr.ord::int - 1) AS elem_blue2
FROM   product
     , jsonb_array_elements(metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
WHERE  opt ->> 'value' = 'blue';

Returns nothing if no matching element is found.

WITH ORDNALITY and elem_blue2 are not needed here, but demonstrate a technique we are going to use in the next step.

More explanation:

PostgreSQL unnest() with element number
Query for element of array in JSON column

  2.how to update the "qty" (subtract from its current qty) when one or more of them is sold?

Since Postgres 9.5, there is jsonb_set(). To update the value of the qty key of the first element:

UPDATE product
SET    metadata =  jsonb_set(metadata, '{options, opts, 0, qty}', '"29"', false)
WHERE  ... -- some filter

Aside: Any reason your integer numbers in qty are stored as strings ("30"), not as numbers (30)?

To update the "blue" element, we determine the array index with the technique demonstrated above, plus some UPDATE magic to make the it fully dynamic:

UPDATE product p
SET    metadata = jsonb_set(p.metadata, path, qty, false)
FROM   product p1
     , LATERAL ( -- move computations to subquery
   SELECT ARRAY['options', 'opts', (ord - 1)::text, 'qty'] AS path  -- fix off-by-one
        , to_jsonb((opt ->> 'qty')::int - 1)               AS qty   -- subtract here!
   FROM   jsonb_array_elements(p1.metadata #> '{options, opts}') WITH ORDINALITY arr(opt, ord)
   WHERE  opt ->> 'value' = 'blue'
-- AND    ...  -- more filters
-- FOR UPDATE  -- see below
   ) opt
WHERE  p1.product_id = p.product_id  -- use PK for match

The last query writes a number to "qty" ('9'), not a string ('"9"'), assuming you fixed that as commented.

We need to list the table product in the FROM clause another time to allow the LATERAL join (which wouldn't be possible otherwise) - and self-join to it.

Note a tiny race condition. Under heavy concurrent write load, you might want to add a lock clause to the subquery (FOR UPDATE) to prevent other transactions from changing the row between the inner SELECT and the outer UPDATE. See:

Postgres UPDATE ... LIMIT 1

But you shouldn't, really. As you can see, jsonb (or any document type for that matter) is ill-suited for regular updates to single attributes. That's cumbersome and comparatively expensive. A new row with a new version of the complete document has to be written every time. Use a normalized DB design instead, where only a comparatively very small row has to be rewritten and other parts as well as indexes remain untouched.

If only the quantity is updated regularly, you might move that to a 1:n table and merge it into the JSON document in a VIEW or MATERIALIZED VIEW. The redesign is beyond the scope of this question.


I believe we have several misunderstandings here, so lets try to sort them out one by one and see if that clarifies the big picture in the end.
What you refer to as an "unnamed instance" is what we call a default instance. Just as FYI.
It is not the first installed instance that get port 1433. 1433 is a Well Known Port for the default instance. This is from back in the days when we couldn't have more than one instance (pre-SQL Server 2000).
A named instance get a port number from the Windows dynamic range at first startup. "Dynamic" does not mean a different port number at every startup. The assigned port is stored in the registry and is attempted to be used at following startups. "Dynamic" controls what happens if that fails (i.e., the port last used is now busy). "Dynamic" means "get a different port from Windows". The other option means "fail to start SQL server".
What you see in the GUI you posted as an IP being "disabled" is bad GUI presentation. The individual IP sections are being overridden by the bottom-most config (IPAll). Having a port there means all IP addresses are using that port (as far as I understand). Readers are welcome to comment/edit here.
As for your named instance, I suggest you set the port number under IPAll to 0 (and have nothing for the "static port") and re-start your SQL Server. It should now get a new port from the dynamic Windows range.


This is in general not the best practice.  It depends upon the DB you are using, but generally a relational DB should be more efficient at handling the varying sets of rows if they are all in one table, than if you break the sets of rows into separate tables.  If you let the DB manage the data, it will do a better job of optimizing IO than if you try to surround this with a bunch of tables in the file system.

Indeed, you should make the hashtag column indexed if you are going to commonly retrieve all the rows for a given hashtag.

There is a caveat: if you know that you will only be processing one hashtag value at a time, and you will never want to have data from two hashtag values in process at the same time, there is a possibility that creating tables like you suggest might perform well.  However, I doubt you are in this situation, and it is better to let the DB do what it is supposed to do.


When Oracle uses an index, it has to lookup the actual data of the table in the table itself. Remember, an index is just a list of pointers to the actual data ordered in some way. The index itself only contains the indexed columns.

So, if you specify

select ss.*,r.*
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

You are asking for the contents of ALL columns in v_journal and result. Oracle can then choose based on heuristics (rule based) or statistics (nowadays) what is expected to be cheaper. When the number of expected returned rows is high compared to the total size of the tables, a full table scan is considered cheaper (and is often faster, but please remember it may purge your database cache as a nasty side effect).

When you replace it by something just needing the data in the index like:

select count(*)
from V_JOURNAL ss, result r
where TO_NUMBER(ss.ID_SML||ss.ID_KT||ss.ID_GRADUATE)=r.id_res2

All needed data can be found in the index and the index will be used as a cheap full table scan (full index scan), since the index is probably smaller in size in bytes.


InnoDB tables in an 8GB RAM server: Are these settings optimized?

Take a look at the date on the article you linked to. It's from 2006, which is fifteen years ago as we write this. The latest version of MySQL at that time was 5.0.30.
The key_buffer_size configuration option only applies to MyISAM tables. It's the size of RAM allocated for caching MyISAM indexes. InnoDB tables do not use the key buffer.
I notice one of your settings is default-storage-engine=MyISAM which suggests you have at least some MyISAM tables, unless you have specified each of your tables is InnoDB. You can check what engine is used for each of your tables:
SELECT table_schema, table_name, engine
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');

The default storage engine in MySQL was changed to InnoDB in MySQL 5.5, circa 2010. There are many reasons to prefer to use InnoDB over MyISAM in modern versions of MySQL. You should use InnoDB unless you have a specific reason not to, and have proven with measurements that you need to use MyISAM.
You asked if your settings are optimal for a server with 8GB RAM. We don't have enough information about your usage of the database to know that.
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1

Why do you set this? Do you ever use the slow query log for diagnosing performance problems? Do you rotate the slow.log periodically so it doesn't continue to fill up your storage (IMO, no log should be allowed to exist on a production server unless you have automated log rotation).
performance-schema=0

Why do you disable the performance schema? In some old versions of MySQL, it was slow, but that has been fixed and there's no reason to disable it. I do not disable it in databases I operate, even those that are very sensitive to performance.
max_allowed_packet=32M

Is this large enough? It means you cannot have any SQL query longer than 32MB, or a result set of a query that returns a row larger than 32MB. Do you use BLOB/TEXT columns?
open_files_limit=10000

Why do you set this? Did you measure anything that suggested the default open_files_limit is not enough? How many tables do you have? What is your typical number of Threads_connected? Have you read https://dev.mysql.com/doc/refman/8.0/en/table-cache.html
innodb_buffer_pool_size = 512M

This is half of one GB, but you said you have a server with 8GB RAM. Why not use more? On the other hand, 512MB might be more than enough, if your data is small. You haven't said anything about how much data you store in InnoDB tables.
Note that the innodb config options have no effect on MyISAM tables, so if you do still use MyISAM tables, then there's no benefit to tuning innodb options.
innodb_log_file_size = 128M

Is this enough? The appropriate log file size is determined mostly by your rate of write traffic. Also it must be at least 10x the size of your largest BLOB/TEXT data. Do you have any large BLOB/TEXT columns?
innodb_file_per_table=1

This is the default in current versions of MySQL. So why specify it in the options file if it's the default anyway?
key_buffer_size = 32M

There's no point tuning key_buffer_size if you use InnoDB tables. There are still a few MyISAM tables in the mysql system schema up to version 8.0, but these tables are small and cached in other ways. The key buffer is probably not important to tune.
default-storage-engine=MyISAM

I recommend against setting the default storage engine to MyISAM. It's slower than InnoDB in most benchmarks, and doesn't support ACID properties of an RDBMS.
query_cache_type=2
query_cache_size=128M
query_cache_limit=2M

The query cache is deprecated, as it has been discovered it causes a bottleneck of performance. I recommend setting query_cache_type=0 and query_cache_size=0. This is not necessary in MySQL 8.0, because the query cache has been removed altogether.


Merge replication gone wrong, and snapshot is timing out

Timing out is probably due to performance.
Reduce the retention period gradually and reinitialize all the subscribers. I've copied and pasted the steps to do this from Claudia Silva's technet blog below:

Evaluate the number of rows that will be deleted:

Use coldate from Msmerge_genshistory to get the list of generations that will be removed
With that list (generation) get the number of rows that will be deleted in other system tables

Example:
Select generation from dbo.MSMerge_genhistory where coldata < ‘[current data – retention period you desire]’

Example:
So, from today, the 5th of April 2016, I would like a retention of 7 days: < 29th of March 2016

With the list of generations generated, we can see how many rows of data will need to be deleted from the other system tables (MSmerge_contents / MSmerge_tombstone) as well. If the number is quite high, then we increase the datetime: (example) < 24th of March 2016

Depending on the amount of data needed to be deleted, one may need “one to many” intermediate steps before reaching the target retention desired.

The key to this being a success is estimating the effect of the deletes that needs to be executed (by default batch of 5000 rows).

http://blogs.technet.com/b/claudia_silva/archive/2009/06/22/replication-infinite-retention-period-causing-performance-issues.aspx
and
http://msdn.microsoft.com/en-us/library/ms178557.aspx


When a record is locked in Oracle, can we know which record is locked?

You can't really list all rows that are being locked by a session. However, once a session is being blocked by another, you can find which session/row is blocking it.

Oracle doesn't maintain a list of individual row locks. Rather, locks are registered directly inside the rows themselves -- think of it as an extra column.

You can find which session has acquired a lock on an object through the V$LOCK view, but this will only list general information, not at the row level.

With this view you can also find if a session is being blocked by another. In that case, if a session is blocked by another session, the row information is displayed in the V$SESSION information.

You can retrieve the rowid, let's build an example with 2 sessions:

SESSION1> create table test as select * from all_objects;

Table created

SESSION1> select rowid from test where object_name = 'TEST' for update;

ROWID
------------------
AAMnFEAAaAAALTDAAz

/* setting identifiers to help with identifying this session later */
SESSION2> exec dbms_application_info.set_client_info('012345');

PL/SQL procedure successfully completed

SESSION2> select 1 from test where object_name = 'TEST' for update;
/*  this will block */

Session 2 is now waiting on Session 1. We can discover the blocking row with:

SESSION1> SELECT o.object_name,
       2         dbms_rowid.ROWID_CREATE (1,
       3                                  s.ROW_WAIT_OBJ#,
       4                                  s.ROW_WAIT_FILE#,
       5                                  s.ROW_WAIT_BLOCK#,
       6                                  s.ROW_WAIT_ROW#) rid
       7     FROM dba_objects o, v$session s
       8    WHERE o.object_id = s.row_wait_obj#
       9      AND s.client_info = '012345';

OBJECT_NAME     RID
--------------- ------------------
TEST            AAMnFEAAaAAALTDAAz

For further reading: a description of the process by Tom Kyte.


How to clear the data buffer cache for an Azure SQL database

Azure SQL Database does not have at this time something like DBCC DROPCLEANBUFFERS available for cleaning the data on buffer pool, but scaling up or down the tier of the database has the same effect of DBCC DROPCLEANBUFFERS  and reduces the memory allocation also for the first few minutes.

Hope this helps.


How to influence Oracle backup output size?

Incremental Level 1 backup in RMAN means all data blocks changed since last Incremental Level 1 or 0 backup (0 is the most basic level, it always contains all data).

If you use CUMULATIVE keyword on Dev, the incremental level 1 contains all data blocks changed since last Incremental Level 0 backup. Which means more of them. One possible explanation.

RMAN only cares whether the block was written to, there are no efforts here to de-duplicate basing on the data. Maybe the Prod received less of such block writes, and Dev more. In particular, if you import into Dev, this definitely counts here. Another possible reason.

Manual says, that unused-never-written-to blocks are always skipped but, in contrast, the unused blocks are skipped only in a very specific set of circumstances:

  The COMPATIBLE initialization parameter is set to 10.2 or higher.
  Note that if COMPATIBLE is 10.2, then only tablespaces created with 10.2 compatibility will be optimized to exclude blocks that do not currently contain data. If COMPATIBLE is 11.0.0 or higher, however,
  then the first backup that produces backup sets after COMPATIBLE is
  set to 11.0.0 or higher will update the headers of all locally managed
  datafiles so that all locally managed datafiles can be optimized.
  There are currently no guaranteed restore points defined for the database.
  The datafile is locally managed.
  (sic!) The datafile is being backed up to a backup set as part of a full backup or a level 0 incremental backup.
  The backup set is created on disk or Oracle Secure Backup is the media manager. Thus, when backing up to a media manager other than
  Oracle Secure Backup, RMAN does not skip datafile blocks that do not
  currently contain data.

So, Dev could simply back up unused blocks, and there are plenty of reasons why Prod would have no symmetry here. Another possible reason.

RMAN will do Level 0 (i.e. full) if it believes you erroneously requested Level 1. Manual says:

  The database performs checks when attempting to create a level 1
  incremental backup to ensure that the incremental backup is usable by
  a subsequent RECOVER command. Among the checks performed are:

  A level 0 backup must exist for each datafile in the BACKUP command as
  the base backup for an incremental strategy. Level 0 backups must not
  have status UNAVAILABLE. If no level 0 backup exists, then RMAN makes
  a level 0 backup automatically.
  Sufficient incremental backups taken since level 0 must exist and be
  available such that the incremental backup to be created is usable.

Especially the last point may be tricky. Another possible reason.


How to find out which data I just imported into Oracle?

Using import with show=y won't actually do the import, but will show which tables the import will run against.  There's no practical way, however, to show what rows import will write; it's assumed that you, running the import, would know that.

You may wish to look at some of Oracle's extensive documentation about these utilities to determine what options are available and what they do.


Any way to make stored procedure code dynamically reference other databases, without dynamic sql?

Apart from design considerations raised in the comments, ...

Consider one level of indirection prior to creation of the stored procedure:

write a template for the stored procedure, with token placeholders for the schema names

INSERT DBNAMETOKEN.dbo.Table1 VALUES('a');

write a pre-deployment script (sed?) that creates a new file  specifically for each schema, substituting the actual schema name for the token.
place resulting scripts in per-schema deployment directories, from which they may be installed as usual.

Benefits

Scales well
Both template and custom versions may be under version control
Updates/Fixes may still be deployed through a single change to the template file and propagated through a build step
Possibly easier to schedule/track deployment of updates to each schema  depending on situation
Reduces the stored procedure to a simple(r) set of SQL focussed on the task at hand, rather than the dynamics of making it work across different schema.


Plan changes to include Eager Spool causes the query to run slower

Your queries are using totally different indexes, which means the optimizer is choosing different plans based on different parameters.

You're able to see the difference with the recompile hint (which means parameter sniffing is involved), which is good. How you fix it is up to you.

In the fast plan, these indexes get used:

Object 13: Index 2, Object 12: Index 3, Object 7: Index 4.

In the slow plan, these get used.

Object 13: Index 3, Object 12: Index 4, Object 7: Index 5.

Since the plan is anonymized, and you've only posted pictures of it, what you need to do is look at which parameters were used in each execution, and try out using the OPTIMIZE FOR (please for the love of Joe Sack don't use UNKNOWN) hint to see if the 'fast' plan is still fast for the set of parameters used to get the slow plan.

It may not be, which is totally possible. From there, you'd need to look at the query and indexes to tune things so you have a better general plan. It'll be really hard to help if you keep posting anonymized stuff, though.


How can I make this execution plan more efficient?

It looks like when you populate the #runIds table -- and I'm just taking a wild guess here -- you're using a string splitter function that outputs the values as NVARCHAR(MAX).

You could try converting the values there to get rid of the implicit conversion warnings.

Another possible improvement would be to alter the NonClustereIndex-Cardholder index on RiskProductionStatistics to have model_set_name as a key column, and model_name, run_id, value as included columns. This would address the Key Lookup.

You may also want to check the datatype of model_name. It appears in a Filter operator, and my fear is that it's a MAX datatype, which may prevent the predicate from being pushed down.

Since this is an estimated plan, and you haven't included any metrics about the query, it's hard to say how much improvement these changes will have.


SQL Server total memory less than target memory, lot of extra memory unallocated

This is normal.

Max server memory means that your server cannot use more than that amount, not that it must use that amount.  Min server memory doesn't mean it must use that amount, either;  it means that once the server uses that much it doesn't surrender memory to below that amount.  Basically, SQL Server uses the amount of memory it needs until it reaches the upper limit you set, and if it stops using it it might surrender part of it back unless you've set a min memory.  (However, in my experience, it's pretty memory-greedy.)

In short:  This means that you're currently not using 6GB of memory on your SQL Server.


Is it possible to refer to alias before it is assigned?

FROM is - logically - evaluated before SELECT, so despite the textual order of your query, ta is known to SUM.

There are however a number of things in your query that won't work:

You need a FROM clause
A JOIN is performed between 2 tables

You can probably rewrite your query to:

SELECT table.id
     , SUM(10/ta.value) AS amount
FROM tableA ta
WHERE ...

but perhaps this is what you meant?

SELECT t.id
     , SUM(10/ta.value) AS amount
FROM ... as t
JOIN tableA ta
     ON ta.key = t.key
WHERE ta.key = t.key
GROUP BY t.id

Note that you need a GROUP BY for the latter query

The link provided by Jim Horn below is a good start towards a better understanding, so I'll just add a few lines on a methodology for constructing a query.

Step one, figure out what relations that are involved and how the relate. Example:

FROM customers as c
JOIN orders as o
    ON c.custno = o.custno

SQL is closed under operations like JOIN, UNION and so forth, meaning that the result is a new relation. It is normally not given a name, but in this case, we can think of it as customer_orders

Step two, what customer_orders are we interested in?

FROM customers as c
JOIN orders as o
    ON c.custno = o.custno
WHERE year(order_date) = 2019

What details of these customer_orders are we interested in? In this example, I'll assume that we want to know how many orders each customer have:

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno

It is not valid to refer to an aggregate function in the where clause, since the where clause is evaluated before the aggregation (count(...)), but there is a similar construction that can be used. Say that we want to know which customers that have more than 20 orders:

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno
HAVING count(o.orderno) > 20

This is the same as:

SELECT custno, cnt
FROM (
    SELECT c.custno, count(o.orderno) as num_orders
    FROM customers as c
    JOIN orders as o
        ON c.custno = o.custno
    WHERE year(order_date) = 2019
    GROUP BY c.custno
) as customer_orders
WHERE num_orders > 20

Finally, you may want to retrieve the rows in a particular order.

SELECT c.custno, count(o.orderno)
FROM customers as c
JOIN orders as o
    ON c.custno = o.custno
WHERE year(order_date) = 2019
GROUP BY c.custno
HAVING count(o.orderno) > 20
ORDER BY count(o.orderno) desc -- most orders first

Up until the last step the result is a relation, but the order by invalidates the fundamental property that a relation is unordered. It may seem like an irrelevant note, but it is much easier for the DBMS in question to optimize a query if it does not have to take the order of rows in consideration.


It's extremely hard to make this secure. Event if you are trying to be smart and check for every "possible" aspect. There is also dynamic SQL. And all kinds of ways to obfuscate code.

I wouldn't let the world run any query with more permissions than they should have.

If at all, I would create a user with restricted privileges, so that he can only do what he's allowed to in the first place. Also be aware that some database objects are visible to public by default, some tables in the system catalog like pg_class for instance ...


Unicode support of PostgreSQL and its performance

Unicode encoding support in PostgreSQL is stellar and is implemented using the UTF-8 character encoding.

If the client uses a different encoding than the server, automatic character set conversion occurs, paying a negligible performance penalty.

ASCII data does not require conversion to the UTF-8 character encoding, so no performance penalty there.

Please refer to the PostgreSQL Localization documentation for further information.


Numeric Latitude & Longitude values are rounded up when querying SQL Server Database

The solution to this issue was due to the fact that the way the code was being pulled from the database. In coldfusion if you are going to query the numeric value from the database, you must use a sql query param and specify the return type of the data.