cumulatively calculate the distance along a line

Fiddling around with cosd() is silly, especially when you do not know the actual latitude. ST_Distance can compute the desired value directly if you give it geography values; if you have lon/lat geometries, you can simply cast them.
To add up the lengths step by step, use a recursive CTE:
WITH RECURSIVE cum(id, geom, dist) AS (
  SELECT id,
         geom,
         0::double precision
  FROM line
  WHERE id = 1

  UNION ALL

  SELECT line.id,
         line.geom,
         cum.dist + ST_Distance(cum.geom::geography, line.geom::geography)
  FROM cum
  JOIN line ON cum.id + 1 = line.id
)
UPDATE line
SET m = dist
FROM cum
WHERE line.id = cum.id;


The citation from the SET related paragraph of the ALTER DATABASE documentation section is "Whenever a new session is subsequently started in that database, the specified value becomes the session default value". So the changes will take effect for new sessions only. You just need to reconnect.


You can use a typed table:

CREATE TYPE mytype AS (some_id int, some_col text);

CREATE TABLE example OF mytype (PRIMARY KEY (some_id));

I added a PK constraint (which you did not ask for).

The syntax is documented as second variant in the manual for CREATE TABLE, which also explains further down:

  OF type_name

  Creates a typed table, which takes its structure from the specified composite type (name optionally schema-qualified). A typed
  table is tied to its type; for example the table will be dropped if
  the type is dropped (with DROP TYPE ... CASCADE).

  When a typed table is created, then the data types of the columns are determined by the underlying composite type and are not specified
  by the CREATE TABLE command. But the CREATE TABLE command can add
  defaults and constraints to the table and can specify storage
  parameters.

There is also a code example at the end of the examples section.


Generally, running compression/decompression on the SQL Server itself is a bad idea.

SQL Server is licensed by the CPU core, so it's the most expensive place in the shop to do CPU-intensive things like compression & decompression. Instead, consider offloading that to an application server.

Now, having said that: SQL Server doesn't support the installation of programs and doesn't have a way to bundle 3rd party apps with the install. You'll want to manage the installation of other apps with a software deployment product or an installer script.


How to transform Streaming Replication Master-Slave to Cascaded Master-Slave1-Slave2 setup?

For a standard cascading replication setup, you need to be on at least PostgreSQL 9.2.

You will also need to set the listen_addresses to something other than localhost, adjust the max_wal_senders parameter in your postgresql.conf to accommodate the additional connection(s), and allow replication connections in your pg_hba.conf.

You'll also want to have WAL archiving set up on your primary, moving the WAL segments to somewhere accessible to the other replicas. This is to ensure that your replicas don't get so far out of sync with your primary that they can no longer catch up due to WAL segment recycling. Also, with WAL archiving, versions before 9.3 can follow the timeline switches without issue.

From there, in your recovery.conf, change the primary_conninfo parameter to point at the cascaded replica.

standby_mode      = 'on'
primary_conninfo  = 'host=<parent_streaming_replica_ip> port=5432 user=replication password=********* sslmode=require' # remove sslmode if you're not enabling ssl
trigger_file      = '/tmp/failover.postgresql.5432'
# To keep in sync with the primary in case of network issues
restore_command   = 'cp /path/to/my/wal/segment/archive/%p %f'
# Follow the latest timeline in the WAL archive in case of a switch.
recovery_target_timeline = 'latest'

As I understand your question, you're proposing a replication topology that looks like this:

Server 1                                                      Server 2
-------------------------------------                         _______________________________________
|PROD1 (primary)                    |------------------------>| PROD2 (secondary streaming replica) |
|                                   |                         |                                     |
|PROD3 (cascaded streaming replica) |<------------------------|                                     |
-------------------------------------                         ---------------------------------------

This would create extra contention for resources between the cascaded replica and the primary, and not give you any extra guaratees of high availability, since two of your three PostgreSQL instances are sharing the same hardware.

It would be better to have a third server, and have that follow the second server's timeline, like this:

Server 1 -----> Server 2 ----> Server 3

So that when you fail over from Server 1 to Server 2, you still have a streaming replica available via Server 3. Once you've fixed the issues with Server 1, you can set it up as a cascading replica of Server 3, as you had when you originally set up Server 3, so that your topology now looks like this:

Server 2 -----> Server 3 ----> Server 1

Alternately, if you don't have a third server available, you can take a new basebackup of the primary PROD2, and set up the former primary as a new secondary PROD1. You could also use pg_rewind, depending on your version of PostgreSQL (9.3+), which would save needing to run a basebackup if that is available to you.

You can find more information out about following timeline switches and why having a WAL archive with streaming replication is a great idea:

Waiting for 9.3: Allow a streaming replication standby to follow a timeline switch

Offsite replication problems and how to solve them -- Streaming replication with WAL shipping example, showing failure modes.

And basics on setup of replication (both streaming and log shipping) are listed on the PostgreSQL Wiki: Binary Replication Tutorial


INSERT data on MySQL to specific position based on its value (similar to ORDER BY)

A table is, by definition, an unordered bag of rows. There is no guarantee that if you say SELECT * FROM table you will get the rows back in the same order you inserted. Think about throwing a bunch of popsicle sticks on the ground while blindfolded; now take off the blindfold and tell me which one hit the floor last.

No ORDER BY is essentially telling the database you don't care about order.

If you do care about order, you need two things:

At least one column that can dictate order (an auto increment column, a date/time column populated with now(), or something you manually specify).
An actual ORDER BY expression on the outermost part of the query that presents the data (and don't forget to include a tie-breaker, if the first column might not be unique).


Aggregate data from two tables with different field names

SELECT year, month, sum(pledged) AS Pledged, sum(donated) AS DONATED FROM
(
SELECT year(pledge_date) as year, month(pledge_date) as month, amount_pledged  as pledged, 0 as donated FROM pledges
UNION
SELECT year(processed_date) as year, month(processed_date) as month, 0 as pledged, amount_donated as donated FROM **donations**
)x
GROUP BY year, month
ORDER BY year, month


databases/tables with nonconventional names

Disclaimer : not a user of phpmyadmin

You may have to check the following

see what character set phpmyadmin operates in
see what character set mysqld operates in
see what character set remote database servers operates in

Find out if you can manually change the default character set before connecting to the the desired DB server

If you connect to a remote DB using mysql client program and the characters for DB names look normal, then you are just dealing with character set issues with phpmyadmin.

WIth regard to the error message, do not surround database names with single quotes ('). Instead, surround them with back quotes (`)


Very Slow MySQL Queries, even with indexes

Not a DBA or MySQL expert here, but let's try :).
So let's take your second query - a bit smaller than the 1st one - and simplify the table names.

We have something like : (LO = logs, CL = cloud_logs, CAV = client_application_versions, CA = client_applications)

 SELECT LO.* FROM LO
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id
 INNER JOIN CA      ON CA.id    = CAV.client_application_id
 WHERE (LO.deleted_at IS NULL)
 AND (CA.account_id = '3')
 AND (CA.id = '5')
 ORDER BY timestamp DESC LIMIT 100 OFFSET 0

And so you say it takes about 100 seconds, correct ?

When you say :

  I have indexes on all applicable fields already.

Yet I believe that's where the flaw is. You don't have that much joins, and you may have 7 billion data or just 700, that should be performing well if indexing is correctly thought, and I think that's probably the order by / limit that is messing with your performance because of poor indexing.

1/ Have you tried :

SELECT LO.* FROM LO WHERE (LO.deleted_at IS NULL)

or

SELECT * FROM CA WHERE (CA.account_id = '3') AND (CA.id = '5')

See how these requests perform in time, if everything ok with these 2 tables ?

2/ Have you indexed timestamp as well ? Indexing the column you are making the "order by" on is crucial as well.
In fact, you should even think about your data and how many values you're gonna have for each of the data you're querying on. This is very well explained right there : http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/  and will certainly help you.

3/ From what I've read on MySQL a few mins ago you could also try a MySQLCheck see if everything ok with your tables if you think your indexing is OK  http://dev.mysql.com/doc/refman/5.0/en/mysqlcheck.html . I know that in older versions of oracle we had to compute stats after creating indexes, maybe something similar here ?

Hope this helps.

[EDIT : 12/01/13 After comments ]

Ok, glad to see you already divided the time by 4 but indeed 25s is way too long.

1/ Have you tried to play with indexes by creating one that would make sense, like explained by Peter here (http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/) ? Like an index on (CA.account_id, CA.id, timestamp) etc ?

2/ How long does it take when you get rid of the order by / limit like below ?

SELECT LO.* FROM LO
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id
 INNER JOIN CA      ON CA.id    = CAV.client_application_id
 WHERE (LO.deleted_at IS NULL)
 AND (CA.account_id = '3')
 AND (CA.id = '5')

To check if this would be the order by/limit that mess up with your performance ?

3/ In case 2 is verified, you could try something like :

SELECT LO.* FROM LO
 INNER JOIN CL      ON CL.id    = LO.cloud_log_id
 INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id
 INNER JOIN CA      ON CA.id    = CAV.client_application_id
 INNER JOIN
 (
    SELECT LO.id FROM LO
    INNER JOIN CL      ON CL.id    = LO.cloud_log_id
    INNER JOIN CAV     ON CAV.id   = CL.client_application_version_id
    INNER JOIN CA      ON CA.id    = CAV.client_application_id
    WHERE (LO.deleted_at IS NULL)
    AND (CA.account_id = '3')
    AND (CA.id = '5')
    ORDER BY timestamp DESC LIMIT 0,100
  ) AS PERF  ON PERF.id = LO.id

Where you replace LO.id by the column that makes sense with Logs (I suppose you have some sort of Logs id . This is based on : http://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
Note you can change the LIMIT 0,100 and keep the OFFSET keyword instead in case you need it (if PostgreSQL compatibility is required).


SQLServer AlwaysOn Availability Groups in 2014 and 2016

There are plenty of reasons to upgrade to 2016 and even possibly 2017, however if a vendor will not support you on those versions, then the benefits of 2016 and 2017 are moot. It is less risky to stay with a supported version than to take on the benefits of what 2016 and 2017 will bring you.


trigger - Sql2008 - Msg 7391 - "no transaction is active/unable to begin trans"

FIXED!

Every site has the same step-by-step.

  Start
  DCOMCNFG
  Expand "Component Services"
  Expand "computers" >
  Expand "My computer"
  Expand "DTC"
  right click on "Local DTC"
  Properties
  Security  check  "Network DTC Access" and " allow remote clientes".

It for sure will help a lot of people. The problem is that the server is in a cluster, so what a did is:

  Start
  DCOMCNFG
  Expand "Component Services"
  Expand "computers" >
  Expand "My computer"
  Expand "DTC"
  Expand "CLUSTERED DTCs" << This is the trick
  Right click on the server and make the same config as above for the "local DTC"

Restart service. Enjoy you boss Happy.


Creating a view with WITH SCHEMABINDING locks the underlying tables and prevents any changes that may change the table schema.
To change the base table you need to remove the SCHEMABINDING option from the objects or drop and recreate these objects once the table has been altered.
To find out if the view is schema bound use below tsql:
select objectproperty(object_id('view_name'), 'IsSchemaBound') as SchemaBound

To remove the with schemabinding option, just alter the view defination :
alter view schemaname.tablename
-- with schemabinding (remove it )
as
select some_column_name
from schema_name.table_name

Then make changes to the table and then alter the view again now with schemabinding
alter view schemaname.tablename
with schemabinding --(adding back )
as
select some_column_name
from schema_name.table_name

from BOL :

SCHEMABINDING :
Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.
Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.


SQL Profiler Audit Logout duration 18446744073709548

I currently see the same duration for Audit logout, among other similar values. For the same SPID, I would expect this number to increase monotonously, but that is not the case. A few subsequent values:

0
3
6
2616
0
49530
196
18446744073709548
18446744073709545
18446744073709541
18446744073709548

(duplicate values omitted)

I did not arrive here by searching for that number, so presumably, these numbers occur frequently.

If it were a duration in ms, this would correspond to a duration of over 584942 years. The only machine I have heard of running for that long is Deep Thought, but that machine is definitely not hosting my SQL Server databases.

My assumption is that somehow, the durations of individual batches are subtracted from the cumulative duration, causing an unsigned long to wrap around.
2^64 = 18446744073709551616, which is very close to 1000 times the numbers we encounter. So, internally, the duration is probably stored in µs.

So, this seems a bug, but I do not think it will learn you anything about the performance of your batches.

I am running SQL Server 12.0 and profiler v17.9.1.


SQL database mail configuration to use a domain account that is not the SQL Service account

Using Database Mail Configuration Wizard
You can achieve that by selecting the Basic authentication option and filling the user name in the Domain\Login format:

You can check the New Account Page doc to understand each parameter of the wizard.
Using Transact-SQL
You can also use the system stored procedure sysmail_add_account_sp to do that. Here I used the example from the doc and added the last three parameters that are related to your question:
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'AdventureWorks Administrator',
    @description = 'Mail account for administrative e-mail.',
    @email_address = 'dba@Adventure-Works.com',
    @display_name = 'AdventureWorks Automated Mailer',
    @mailserver_name = 'smtp.Adventure-Works.com',
    @username = 'Domain\Login',
    @password = 'yourPassWord',
    @enable_ssl = 1;

Security Implication
Notice that on both examples I'm using the SSL option because, as Dan Guzman pointed out, the use of that option

Specifies whether Database Mail encrypts communication using Secure
Sockets Layer

as you'll see in the doc. So, if you're not using a secure connection in your environment, those credentials, along with the email content, are gonna be sent in plain text to the SMTP mail server, therefore being a security issue.


How does adding LIMIT work with Where conditions in mysql?

Your LIMIT 1, 6 is short for LIMIT 6 OFFSET 1, in MySQL parlance. OFFSET means skip this many records from the start of the result set. So what you're saying is, give me 6 records after skipping 1 (records 2 through 7, starting at 1). With only 1 record, and skipping that 1, you have no results.


Galera backup failing with error "mysqldump: Error 1213: Deadlock found when trying to get lock; try restarting transaction when dumping table"

Option 1

Use a replication slave off the galera cluster exclusively for creating backups. You can stop slave to ensure that all DDL operations have finished before doing a dump. Parallel dumping operations like mydumper are also possible.

Don't forget to enable log-slave-updates on the galera member you use as a master.

Option 2

Desync a galera node before doing a backup from it.


How can I SELECT an 'instance' in PostgreSQL?

This is a really bad idea to begin with, you're recreating logical replication. Just refer to the PostgreSQL guide to logical replication.

That said, what you want is the PostgreSQL plpgsql Trigger "special variables.

TG_TABLE_NAME Data type name; the name of the table that caused the trigger invocation.
TG_TABLE_SCHEMA Data type name; the name of the schema of the table that caused the trigger invocation.


Why does SQL Server use a better execution plan when I inline the variable?

In SQL Server, there are three common forms of non-join predicate:
With a literal value:
SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = 1;

With a parameter:
CREATE PROCEDURE dbo.SomeProc(@Reputation INT)
AS
BEGIN
    SELECT COUNT(*) AS records
    FROM   dbo.Users AS u
    WHERE  u.Reputation = @Reputation;
END;

With a local variable:
DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

Outcomes
When you use a literal value, and your plan isn't a) Trivial and b) Simple Parameterized or c) you don't have Forced Parameterization turned on, the optimizer creates a very special plan just for that value.
When you use a parameter, the optimizer will create a plan for that parameter (this is called parameter sniffing), and then reuse that plan, absent recompile hints, plan cache eviction, etc.
When you use a local variable, the optimizer makes a plan for... Something.
If you were to run this query:
DECLARE @Reputation INT = 1

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Reputation = @Reputation;

The plan would look like this:

And the estimated number of rows for that local variable would look like this:

Even though the query returns a count of 4,744,427.
Local variables, being unknown, don't use the 'good' part of the histogram for cardinality estimation. They use a guess based on the density vector.

SELECT  5.280389E-05 * 7250739 AS [poo]
That'll give you 382.86722457471, which is the guess the optimizer makes.
These unknown guesses are usually very bad guesses, and can often lead to bad plans and bad index choices.
Fixing It?
Your options generally are:

Brittle index hints
Potentially expensive recompile hints
Parameterized dynamic SQL
A stored procedure
Improve the current index

Your options specifically are:
Improving the current index means extending it to cover all the columns needed by the query:
CREATE NONCLUSTERED INDEX IX_MyTable_Id_SomeBit_Includes
ON dbo.MyTable (Id, SomeBit)
INCLUDE (TotallyUnrelatedTimestamp, SomeTimestamp, SomeInt)
WITH (DROP_EXISTING = ON);

Assuming that Id values are reasonably selective, this will give you a good plan, and help the optimizer by giving it an 'obvious' data access method.
More Reading
You can read more about parameter embedding here:

Parameter Sniffing, Embedding, and the RECOMPILE Options, by Paul White
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables), Kendra Little


As simple answer to the question, yes, it is possible to do this with a function.

PostgreSQL has several very powerful function languages that you can use for this purpose. Being powerful, you can do some pretty neat things with them. You can also shoot yourself in the foot very easily.

The solution is very brute-force and inelegant, and I wouldn't recommend using it on a production database at all.

The function that answers this doesn't give you any output in the logs, and it's basically an all-or-nothing proposition in your database, so running it in a transaction is essential.

I would highly recommend figuring out exactly what it's doing and why it is doing it before running it.

It would be better to explicitly create an sql script to add ON DELETE CASCADE for each key that you want to update. This would be the most flexible, introspect-able, and modify-able solution to your problem.

The code below worked on the pgbench tables and their foreign keys for me in a test database I created. I don't guarantee it will work in other cases.

It looks something like this:

BEGIN;

DO $$DECLARE r record;
   BEGIN
      FOR r IN SELECT c.relname, conname, cr.oid AS croid FROM pg_catalog.pg_constraint cr, pg_catalog.pg_class c WHERE c.oid = cr.conrelid AND cr.contype = 'f' ORDER BY 1
   LOOP
      EXECUTE 'ALTER TABLE '||quote_ident(r.relname)||' DROP CONSTRAINT '||quote_ident(r.conname)||',ADD CONSTRAINT '||quote_ident(r.conname)||' '||pg_catalog.pg_get_constraintdef(r.croid, true)||' '||' ON DELETE CASCADE';
   END LOOP;
END$$;

It dynamically creates

ALTER TABLE foo DROP CONSTRAINT bar_fk, ADD CONSTRAINT bar_fk FOREIGN KEY (baz) REFERENCES bar(baz) ON DELETE CASCADE;

and executes it in a loop in the anonymous function for every referenced constraint in pg_catalog.pg_constraint of type f (foreign key), joined to the pg_catalog.pg_class table to get the relation name. If you needed it to be smarter than that, you'd want to alter the statement in the loop accordingly to fit your needs.

Once you've checked the results for your tables with \d or some other method, and are satisfied with the results, then you execute the COMMIT at the end.


Is this the correct way to model a relationship between an object which may or may not have another object?

I would favor your first arrangement where you have an Item_Id FK in the Coupon table.

You will be able to enforce the "One coupon per item rule" through a unique constraint on the Item_Id column in your Coupon table.
You will be able to easily allow multiple coupons per item if business decides to change their mind.
Like you said you can easily select and filter your items vs. coupons relationships
Putting the Item_Id in the Coupon table allows you to maintain a history of coupons that were valid for that item which is often useful.

The only potential problem I see is in some ways you are focusing on tying a coupon to a specific item on issuance of the coupon.  This only becomes a problem when business decides that they want to hand out "20% off your highest priced item," or "50% off everything in the store on XYZ date" coupons to their most valuable customers.  This can be resolved of course.  I just mentioned it to get you thinking about ways the design might need to change in the future.


Problem with (not) Locking Tables during Restore

The reason the server is complaining that 'User' was not locked is because of this:

/*!40000 ALTER TABLE `User` DISABLE KEYS */;

That looks like a comment, but it isn't a comment.  MySQL has a special case for comment parsing for backward-compatibility and cross-compatibility.

Comments in this style are processed as if they weren't comments at all, if the server's version is greater than or equal to the number shown, so in this case, any version of MySQL Server 4.0.0 or greater will execute it and any prior version won't.

So, the restore is asking the server to ALTER a table that isn't locked.  You can only do that when your session has no tables locked at all.

If you change that table name to your test table name, the restore should work.

In similar style, if there's an exclamation point in the first position followed by a space, it will be parsed as if not a comment by all versions of MySQL.


Using Left or substring with wildcard in SQL

The following assumptions have been made (some of them possibly repeating parts of your description):

SF/FLEX is always followed either by a number immediately or by a space character and then a number.
There is always one space character before SF/FLEX, unless the item is at the beginning of the string.
There is always a comma or a space after the number that follows SF/FLEX, unless the item is at the end of the string.

With those in mind, here is my approach:

SELECT
  t.*,
  Item = LEFT(item.S, CHARINDEX(' ', item.S + ' ', LEN(prefix.S) + spaces.N + 1))
FROM
  dbo.testdesc AS t
  CROSS JOIN (VALUES ('SF'), ('FLEX')) AS prefix (S)
  CROSS JOIN (VALUES (0), (1)) AS spaces (N)
  CROSS APPLY (SELECT NULLIF(PATINDEX('% ' + prefix.S + SPACE(spaces.N) + '[0-9]%', ' ' + t.PDescription), 0)) AS startpos (N)
  CROSS APPLY (SELECT CHARINDEX(',', t.PDescription + ',', startpos.N + LEN(prefix.S) + spaces.N)) AS endpos (N)
  CROSS APPLY (SELECT SUBSTRING(t.PDescription, startpos.N, endpos.N - startpos.N)) AS item (S)
WHERE
  startpos.N IS NOT NULL
;

This is the output of the above query:

PEvent  PDate                    PDescription                                               Item
------  -----------------------  ---------------------------------------------------------  -------
3       2014-11-21 00:00:00.000  Padding, 90 Series, Stage, SF550                           SF550
4       2014-11-21 00:00:00.000  Padding, 45 Series, 144 Stage SF90, 15 spor, AR            SF90
8       2013-06-14 00:00:00.000  Padding, 900 Series, 140 Stage SF1100, 1/16, Money, TS XB  SF1100
10      2014-12-30 00:00:00.000  Padding, 444 Series, 47 Stage SF330, 5/9, house            SF330
6       2015-01-09 00:00:00.000  Padding, 20 Series, 13 Stage SF 550, Housing               SF 550
5       2013-03-06 00:00:00.000  400 Series, 96 STAGE RE FLEX 30                            FLEX 30
7       2013-06-14 00:00:00.000  Padding, FLEX 10 400 Series, 11 Stage, BYHMP               FLEX 10
9       2015-02-27 00:00:00.000  Flex 10 40 87 stage                                        Flex 10

And to explain some of the trickery employed by the query, this is how it works:

The original row set is cross joined with two inline views, one that represents the required prefixes (prefix) and one that specifies how many spaces there may be between the prefix and the subsequent number (spaces). This way the query will probe for all combinations of prefixes and numbers of delimiting spaces (SF, SF , FLEX, FLEX ).
The PDescription string is searched for each combination, one after one, to calculate the starting position of the prefix. It uses PATINDEX, which, for each prefix and space number, builds its own search pattern. As per the assumptions above, PATINDEX expects a space before the prefix and at least one digit after the prefix and, possibly, a space.

So, if, for instance, the current prefix is SF and the current expected number of spaces after it is 1, the pattern would be % SF [0-9]%. To make sure there is a space before the prefix, a space is added at the beginning of PDescription.

That trick accomplishes two things: it helps to find the prefix if it is at the beginning of the string and it makes the resulting starting position accurate. The latter is important because technically the returned position would match not the prefix itself but the preceding space. So, to get the actual position of the prefix, we would need to increment the result by 1. However, because we had added a space at the beginning of the source, all the positions were shifted forward already. So, in the end, the result points exactly at the beginning of the prefix.

One final touch here is that if the returned position is 0, it is transformed into NULL by NULLIF. That helps to avoid errors about negative length values passed to SUBSTRING later.
The found position is then used in CHARINDEX to find the first comma in PDescription found after that position. Again, to make sure there is a comma, the character is appended to PDescription. The result constitutes the ending position of the sought item.
Using the obtained starting and ending positions, the item is extracted from PDescription using the SUBSTRING function.
Finally, in the SELECT clause, before actually returning the item, the query additionally checks if there is a space after the number, just in case the item was not properly delimited by a comma. By appending a space to the item, it uses the same trick as when searching for the comma position, also taking into account the length of the prefix and the expected number of spaces after the prefix to make sure the found space character will be the one after the number.
In cases where the prefix is not found in the string, Item returns NULL. Such rows, however, are excluded from the output by the WHERE clause, which specifies that the starting position be not NULL.

I am not sure if the above description is clear enough but hopefully it will help together with the code itself.


I'm not sure on what the anomalies caused by the first design are called, but the below is the standard solution for that type of problem.  Or at least, it's the standard solution that I use.

Thea answer is to create another table to hold just phone numbers with a foreign key reference to the person table.  The only sticking point may be the PhoneTypeDescription field which should probably be yet another lookup table, depending on how many phone number types you think you might store.  But possible values could be Work, Cell, Home, Fax.  The use of the "IsPrimaryPhone" is also optional but probably warranted.

CREATE TABLE dbo.Person
    (
    PersonID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , FirstName NVARCHAR(100) NOT NULL
    , LastName NVARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.PhoneNumbers
    (
    PhoneNumberID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
    , PersonID INT NOT NULL REFERENCES dbo.Person(PersonID)
    , PhoneTypeDescription NVARCHAR(10) NOT NULL
    , PHoneNumber NVARCHAR(20) NOT NULL
    , IsPrimaryPhone BIT NOT NULL DEFAULT 0
    )

CREATE NONCLUSTERED INDEX IDX_PhoneNumber_PersonID ON dbo.PhoneNumbers (PersonID, PhoneNumberID)


In SQL Server, is there way to have a field where an auto-number is appended to the end of whatever value the field is set to?

Yes use a Calculated Value column. For example:

ALTER TABLE Table1 ADD ColumnB AS ColumnA + CAST(ID AS varchar(8)) PERSISTED

Assuming ID is where you're getting row 1 or row 2 value.


Unable to connect to FTP server using "FTP Connection Manager"

First make sure that you are able to connect FTP server through windows explorer, and then try three things

For Package:
1- Package Protection Level= EncryptSensitiveWithUserKey

For FTP Connection Manager:
2-Server Port= 21
3-UsePassiveMode= False


MySQL timeout on INSERT lasts almost 60 seconds

Both of these improve the efficiency of the SELECT MAX... in the INSERT..SELECT:

Plan A

UNIQUE KEY `ClusterInstrumentId` (`ClusterId`,`InstrumentId`),
KEY `InstrumentId` (`InstrumentId`, ClusterId) USING BTREE,

This assumes you need to look up things by ClusterId.

Plan B

UNIQUE (`InstrumentId`, ClusterId)

This covers the Uniqueness constraint


MongoDB: Queries running twice slow on NEW server compared to OLD server

I noticed that the server wasn't set to NUMA as interleaved (disabled). Once I did it on the new server, the database started to pickup speed now. But still almost same as OLD server not better.


Stored Procedure Performance Issue in Sharepoint Report

Your Sharepoint db instance will have a ReportingService_weirdsequenceofcharacters database. It stores report execution and performance metrics. There is a view called ExecutionLog2 in that database which has 3 Times.
Use them to find whether the bottleneck is sql, report processing, or rendering. You should also be able to compare these values to the values from your production report server (if you have one). A non-sharepoint report server database will typically be named ReportServer.

TimeDataRetrieval

The number of milliseconds spent interacting with data sources and data extensions for all data sets in the main report and all of its subreports. This value includes:

Time spent opening connections to the data source
Time spent reading data rows from the data extension

Note: If a report has multiple data sources/data sets that can be executed in parallel, TimeDataRetrieval contains the duration of the longest DataSet, not the sum of all DataSets durations.  If DataSets are executed sequentially, TimeDataRetrieval contains the sum of all DataSet durations.

TimeProcessing

The number of milliseconds spent in the processing engine for the request.  This value includes:

Report processing bootstrap time
Tablix processing time (e.g. grouping, sorting, filtering, aggregations, subreport processing), but excludes on-demand expression evaluations (e.g. TextBox.Value, Style.*)
ProcessingScalabilityTime (see AdditionalInfo column)

TimeRendering

The number of milliseconds spent after the Rendering Object Model is exposed to the rendering extension.  This value includes:

Time spent in renderer
Time spent in pagination modules
Time spent in on-demand expression evaluations (e.g. TextBox.Value, Style.*). This is different from prior releases, where TimeProcessing included all expression evaluation.
PaginationScalabilityTime (see AdditionalInfo column)
RenderingScalabilityTime (see AdditionalInfo column)

http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx


Oracle DB | Issue with agent and OMS connection

As they say, time heals all wounds.  After investigating for around two hours, the issue cleared on its own.


The issue is that Postgres requires you to include the braces at the end of the function name when calling DROP FUNCTION.

If you change your code to the following, it'll work:

def downgrade():
    conn = op.get_bind()
    conn.execute(sa.sql.text('''
DROP TRIGGER update_rated_trigger ON rating;
DROP FUNCTION update_rated();
    '''))


ParallelPeriod returning null for Feb. 29 in date dimension

Alright, I think I've got a workaround I can live with. First, I created a named set in the cube, called Feb29:

Filter(
    [Post Date].[Post Date].[Day].ALLMEMBERS,
    MONTH([Post Date].CurrentMember.Member_Caption) = 2 And DAY([Post Date].CurrentMember.Member_Caption) = 29
)

Then I've amended the portion of the calculation which looks for the latest sale date (note the - Feb29):

ParallelPeriod(
    [Post Date].[Post Date].[Year],
    1,
    Tail(
        NonEmpty(
            Descendants(
                [Post Date].CurrentMember,
                ,
                Leaves
            ),
            Measures.[Total Price]
        ) - Feb29,
        1
    ).Item(0)
)

Thus when it looks for the latest sale date in, say, 2012, it will ignore Feb. 29, and compare to the previous year's sales from Jan. 1 to Feb. 28 instead. I'm still open to suggestions if there's a better way to do this (particularly that awful Filter expression I used).


Failed transaction replicated and thrown errors on remaining Percona XtraDB cluster nodes

ALTER Table is executed using TOI which executed independently on all node parallely w/o waiting for it to complete on base node and then being replicated (this applies only for DML).

Also, I see you are using 5.5 which in general had lot of issues in past.
It is good if you can upgrade your setup to 5.6.


You can pivot your data using crosstab, but as usual it requires a well-known column's name.

First you should install tablefunc module.

CREATE EXTENSION tablefunc;

Then you can call a crosstab with

SELECT * FROM
  crosstab('SELECT rank, year::INT, COUNT(*)::INT ct FROM demo
            GROUP BY 1, 2 ORDER BY 1, 2')
  AS ct(rk INT, "2016" INT, "2017" INT)

rk | 2016 | 2017
-: | ---: | ---:
 1 |    1 |    4
 2 |    2 |    7
 3 |    8 |    7

dbfiddle here


Optimize Row Level Security expression in Postgres

At first glance, it might appear that since current_setting and string_to_array functions are stable and immutable respectively and there is an index on the category column, the following condition could do the trick.

CREATE POLICY table_select_policy ON big_table
FOR SELECT
USING (category = ANY(string_to_array(current_setting('mydb.allowed_categories'),',')::int[])));

However, the real problem has nothing to do with the form of the condition. If you take a look at the PostgreSQL system catalog and find those two functions that were used in the condition, you may notice that both functions have their cost parameter set to 1. This makes the optimizer assume that calling those functions to recheck the condition while doing Bitmap Heap Scan is cheap.

In order to illustrate this, consider the big_table table shown below.

CREATE TABLE big_table AS
SELECT c AS category
FROM generate_series(1, 1000000) g,
     generate_series(1, 10) c;

CREATE INDEX big_table_category_idx ON big_table (category);

ANALYZE big_table;

Querying the table with the the query below results in the following plan (look at the number of rows removed by Index Recheck).

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(string_to_array(current_setting('mydb.allowed_categories'), ',')::int[]);

"Bitmap Heap Scan on big_table  (cost=52478.56..195418.17 rows=3036665 width=4) (actual time=166.613..9273.010 rows=3000000 loops=1)"
"  Recheck Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"  Rows Removed by Index Recheck: 5209365"
"  ->  Bitmap Index Scan on big_table_category_idx  (cost=0.00..51719.39 rows=3036665 width=0) (actual time=164.782..164.782 rows=3000000 loops=1)"
"        Index Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"Total runtime: 9341.568 ms"

To avoid that, you have two options. The first is to increase the work_mem parameter in your server config. This will allow the server to store the complete bitmap in memory and save a great deal of time while rechecking the condition. The plan below was obtained when the work_mem parameter was set to 1000M.

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(string_to_array(current_setting('mydb.allowed_categories'), ',')::int[]);

"Bitmap Heap Scan on big_table  (cost=52478.56..195418.17 rows=3036665 width=4) (actual time=193.613..449.385 rows=3000000 loops=1)"
"  Recheck Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"  ->  Bitmap Index Scan on big_table_category_idx  (cost=0.00..51719.39 rows=3036665 width=0) (actual time=184.858..184.858 rows=3000000 loops=1)"
"        Index Cond: (category = ANY ((string_to_array(current_setting('mydb.allowed_categories'::text), ','::text))::integer[]))"
"Total runtime: 513.528 ms"

The second option is to wrap the query condition in a "costly" function.

CREATE OR REPLACE FUNCTION my_categories()
RETURNS int[] AS $$
BEGIN
  RETURN string_to_array(current_setting('mydb.allowed_categories'), ',')::int[];
END;
$$ LANGUAGE plpgsql STABLE COST 100000;

EXPLAIN ANALYZE SELECT * FROM big_table WHERE category = ANY(my_categories());

"Index Only Scan using big_table_category_idx on big_table  (cost=250.44..9363945.19 rows=3036665 width=4) (actual time=0.035..577.094 rows=3000000 loops=1)"
"  Index Cond: (category = ANY (my_categories()))"
"  Heap Fetches: 3000000"
"Total runtime: 642.522 ms"

This option will also make the optimizer end up performing Index Scan instead of Bitmap Scan which may turn out to be a bit slower.


Will a mysql db import be interrupted if my ssh session times out?

If the SSH drops without putting the mysql session into the background (along with nohup), then the DB connection is at the SSH session's mercy. So, yes, mysql would care about the lifecycle of the SSH session.

You should launch it with & at the end as follows:

nohup mysql -u root -p mydb < mydb.sql &

Then, you can walk away feeling OK (you could actually logout on purpose at this point) because process 1 (init) will pick up the process ID that is running the mysql session upon the SSH session termination (voluntary or involuntary) because it was suddenly orphaned (What a great dad !!!).


Spliting changes in separate collection/table or not?

Since you have 16MB limitation of document size in MongoDB also your access pattern doesn't require to store the messages in the main document. you can go with the following model

Parent Referencing:

Store all your messages in a different collection and refer the main document id as parent document id

or

Embed only the frequently accessed (first 10 or 20) messages with the main document and store all messages in a different collection. So for the first load, you don't need to do $lookup . Only to see the other messages you need the second collection.


When creating a many to many relation with an attribute, should I include a non-composite primary key?

What are voltage and efficiency?  Simple numbers (SMALLINT) or FLOATs?  Then do not normalize them and do not use a many:many table.  Simply have the value in the table instead of an id pointing to the value.  There is such a thing as "over-normalization"; it does not scale well.

Use many:many mapping tables for relating things that have lots of attributes.

Back to your question...  A many:many table does not need a surrogate id for the PK.  It is not just OK to have a composite PK, it is actually more efficient to have such.  Save AUTO_INCREMENT for cases where there is no good "natural PK".

Other tips on many:many tables.  Note that it advises having a secondary key for when you need to 'go the other direction'.

(Based on Comment):

Using ENUMs:

voltage ENUM('unknown', "460/60/3", ...)  NOT NULL
efficiency ENUM('unknown', "ie1", ...)  NOT NULL

Using a string:

voltage VARCHAR(15)   CHARACTER SET ascii NOT NULL DEFAULT ''
efficiency VARCHAR(8) CHARACTER SET ascii NOT NULL DEFAULT ''

Normalizing:

voltage_id TINYINT UNSIGNED NOT NULL ... and another table
efficiency_id ...

If there are very few choices, and the list of choices changes only very rarely, ENUM may be optimal.  The 'string' option is not too bad since the strings are rather short.  I feel that the normalizing is "over-normalizing" in this case, namely only a few short strings.


Calculating daily growth using previous row value in new column - SQL Server

I just read this article adressing this topic and I suppose you might be interested, because it uses the lag function to solve exactly your Task:

https://www.mssqltips.com/sqlservertip/3690/identify-sql-server-database-growth-rates/

The Author created the following Script using the LAG Window function:

SELECT DISTINCT
    A.[database_name]
,   AVG( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Avg Size Diff From Previous (MB)]
,   MAX( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Max Size Diff From Previous (MB)]
,   MIN( A.[Backup Size (MB)] - A.[Previous Backup Size (MB)] ) OVER ( PARTITION BY A.[database_name] ) AS [Min Size Diff From Previous (MB)]
,   A.[Sample Size]
FROM
(
    SELECT
        s.[database_name]
    --, s.[backup_start_date]
    ,   COUNT(*) OVER ( PARTITION BY s.[database_name] ) AS [Sample Size]
    ,   CAST ( ( s.[backup_size] / 1024 / 1024 ) AS INT ) AS [Backup Size (MB)]
    ,   CAST ( ( LAG(s.[backup_size] )
            OVER ( PARTITION BY s.[database_name] ORDER BY s.[backup_start_date] ) / 1024 / 1024 ) AS INT ) AS [Previous Backup Size (MB)]
    FROM
        [msdb]..[backupset] s
    WHERE
        s.[type] = 'D' --full backup
    --ORDER BY
    --  s.[database_name]
    --, s.[backup_start_date]
) AS A
ORDER BY
    [Avg Size Diff From Previous (MB)] DESC;
GO


Recommended way to test code updates in Oracle

This sounds like something where pluggable databases could be very useful.
You set up a 'standardised' test PDB and create a copy for each test. Further copies can be created for tests in parallel. Once the test is successful a new 'standard' PDB is created and the cycle continues.

I haven't used the techniques as we aren't currently on 12c here but this is one of the scenarios where we are expecting to be able to improve the testing cycle.


PostgreSQL. Unstable execution time hugely exceeding planning time

The problem must come from varying statistics; perhaps the tables change a lot.

The bad plan comes when PostgreSQL believes that there are so many rows that satisfy the condition that it will be cheaper to scan the rows in ORDER BY order using the primary key index until it hits the first match. Either that assumption is wrong, or the correlation between these conditions is unfortunate.

Anyway, if you know that the first plan is always the correct one, you can tell PostgreSQL not to use the primary key index by modifying the ORDER BY clause like this:

ORDER BY core_bundle.id + 0


Creating Oracle 12c R2 user with default collation binary_ci fails

Works here.

SQL> alter session set container=pdb1;

Session altered.

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;
CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI
                            *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD
is set.

SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      STANDARD

SQL> conn / as sysdba
Connected.

SQL> alter system set max_string_size=extended scope=spfile;

System altered.

SQL> shu immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8801008 bytes
Variable Size             306185488 bytes
Database Buffers          750780416 bytes
Redo Buffers                7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open upgrade;

Pluggable database altered.

SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
[oracle@o71 ~]$ cd $ORACLE_HOME/rdbms/admin/
[oracle@o71 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output_catcon_6969.lst]
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output*.log] files for output generated by scripts
catcon: See [/u01/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utl32k_output_*.lst] files for spool files, if any
catcon.pl: completed successfully
[oracle@o71 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 26 20:47:57 2017

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size                  8801008 bytes
Variable Size             306185488 bytes
Database Buffers          750780416 bytes
Redo Buffers                7974912 bytes
Database mounted.
Database opened.
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> alter session set container=pdb1;

Session altered.

SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.

However, the behaviour is unchanged in the root container:

SQL> conn / as sysdba
Connected.
SQL> show parameter max_string

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
max_string_size                      string      EXTENDED

SQL> CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;
CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI
                               *
ERROR at line 1:
ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD
is set.

The below makes it possible to overcome this, but I do not recommend it, as it is neither documented nor supported (it even allows creating a common user without the common prefix):

SQL> conn / as sysdba
Connected.

SQL> alter session set "_ORACLE_SCRIPT"=true;

Session altered.

SQL> CREATE USER c##a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.

SQL> CREATE USER a IDENTIFIED BY a DEFAULT COLLATION BINARY_CI;

User created.


If a CTE is defined in a query and is never used, does it make a sound?

+1 to Erik, but wanted to add two things (which did not work well in a comment):

You don't even need to look at execution plans to see that they are ignored when not used. The following should produce a "divide by 0" error but does not due to cte2 not being selected from at all:

;WITH cte1 AS
(
  SELECT 1 AS [Bob]
),
cte2 AS (
  SELECT 1 / 0 AS [Err]
  FROM cte1
)
SELECT *
FROM   cte1;

CTE's can be ignored, even if they are the only CTE, and even if they are selected from, if logically all rows would be excluded anyway. The following is a case where the query optimizer knows ahead of time that no rows could be returned from the CTE, so it doesn't even bother to execute it:

;WITH cte AS
(
  SELECT 1 / 0 AS [Bob]
)
SELECT TOP (1) [object_id]
FROM   sys.objects
UNION ALL
SELECT cte.[Bob]
FROM   cte
WHERE  1 = 0;

Regarding performance, the unused CTE is parsed and compiled (or at least compiled in the case below), so it is not 100% ignored, but the cost would have to be negligible and not worth being concerned about.

When only parsing, there is no error:

SET PARSEONLY ON;

;WITH cte1 AS
(
  SELECT obj.[NotHere]
  FROM   sys.objects obj
)
SELECT TOP (1) so.[name]
FROM   sys.objects so

GO
SET PARSEONLY OFF;
GO

When doing everything just short of execution, then there is a problem:

GO
SET NOEXEC ON;
GO

;WITH cte1 AS
(
  SELECT obj.[NotHere]
  FROM   sys.objects obj
)
SELECT TOP (1) so.[name]
FROM   sys.objects so

GO
SET NOEXEC OFF;
GO
/*
Msg 207, Level 16, State 1, Line XXXXX
Invalid column name 'NotHere'.
*/


timelines and history files PostgreSQL 9.1

When the slave is promoted to a new master it creates a new timeline to avoid WAL names overlapping. The .history file contains information about database timeline branches. Recovery process uses this information to determine the timeline it is working with. By default it uses the same timeline that was when base backup was made. So, for example, if you promote one of your slaves to a new master other slaves will not be able to continue recovery from the new master as they use previous timeline.

To make them able to use current timeline, in easy words to switch them to the new master, you need them to know about the new history. Just delete everything in the pg_xlog directory on slaves and copy the history file from the new master. And then set recovery_target_timeline to 'latest' to force the database using the latest found timeline.

Note that the slave pretending to be a new master must be the most catch up one. Otherwise it might lead to data corruption on other slaves after you switch them to the new master.


I don't know a way to log sequential scans only.  Just a small remark: seqscans are not evil, you will get them (especially for smaller tables) all the time.

On the other hand, you can definitely log everything, if you want, and there are multiple ways.

The easiest is probably

[ALTER DATABASE yourdb ] SET log_statement TO 'all';

This will log every query that passes the parser:

  Note: Statements that contain simple syntax errors are not logged even by the log_statement = all setting, because the log message is emitted only after basic parsing has been done to determine the statement type. In the case of extended query protocol, this setting likewise does not log statements that fail before the Execute phase (i.e., during parse analysis or planning). Set log_min_error_statement to ERROR (or lower) to log such statements.

Another insteresting way of achieving it is setting log_min_duration_statement to a (very) low value:

[ALTER DATABASE yourdb ] SET log_min_duration_statement TO 0; -- or somewhat higher

The difference between the two is that the latter will also register the actual parameters passed to functions.

Notes:

you need superuser rights for changing either of these settings
you can use both at the same time.  For details, please read the documentation linked above.


Confirmation on query optimization and performance understanding (seek and scan)

To answer your questions:

Since you say 'all the records meet the criteria' I would advise doing tests on a larger set of rows where some rows are outside the criteria.
There are some circumstances where using an expression against an indexed field will result in the index being seeked, such as WHERE [MyField] LIKE 'SomeString%'.  However, using an expression directly in the field will result in the query not being sargable, and will induce an index scan.  For instance, WHERE LEFT([MyField], 1) = 'T'.
Not having a clustered index on a table (meaning the table is a heap) does not affect the ability for a particular SQL query to use a seek operation on an index.  Not having a clustered index on a table has its own set of issues that is way beyond the scope of this answer.  Check this blog post for more info on heaps vs clustered indexes.


How to query for records based on most recent version

You can use ROW_NUMBER() and OVER clause to "group" (partition) the rows per ordernumber, then order them by date (descending) and keep only the first rows (WHERE rn = 1):

WITH cte AS
  ( SELECT  id, ordernumber, [date], status,
            rn = ROW_NUMBER() OVER (PARTITION BY ordernumber
                                    ORDER BY date DESC)
    FROM    tableX
  )
SELECT  id, ordernumber, [date], status,
FROM    cte
WHERE   rn = 1
  AND   status = 'active' ;

And I really do hope that the date column is a DATE column and not a CHAR one. Otherwise the above ORDER BY will not work as you expect.

After the last, edit, it seems that the date column is actually of DATE dataype, which is good.

But really, you should't use '1-3-04' format for input. For output it may be fine (the users of the application may like that dubious format) but for input, no. Because you can never be sure if the date that will actually be stored in the database will be 2004-01-03 or 2004-03-01 or 2001-03-04 (or some other.)

Use a format that is unambiguous, like '2004-01-03' (the ISO YYYY-MM-DD format) or even better (for SQL-Server specifically) the YYYYMMDD format: '20040103'. See the blog by Aaron Bertrand: Bad habits to kick : mis-handling date / range queries


SQL Server 2012 Visualization of Table relationships

In SQL Server Management Studio, you can create Database Diagrams with the relationships on tables.
This is per database, and not on the Instance level.

Microsoft Reference


replicate-wild-ignore-table does not stop already logged queries on slave

Without seeing the exact my.cnf entries on the slave, or knowing where the update came from (normal UPDATE command, or a stored procedure for example), or knowing what type of binary log format (row vs statement) you are using, all I can do is give you a link to how MySQL handles the replicate table options here.

A couple of things to note on this to explain why the above points are needed:

If you are using statement based logging and the statements are executed from a stored procedure, the slave will run it because --replicate-table rules do not apply to stored objects.
If the table name you are trying to ignore contains an underscore, you might need to escape it like "foo_bar". The same applies to the database name.


SQL Server 2019 (RTM) - 15.0.2000.5 period has expired after upgrade to CU 8

In the version output you provided it states that you're running the Enterprise Evaluation Edition. This edition can only be used for a short period (I believe 180 days) before it expires and cannot be used. It can also never be used for production workload as per the license agreement.
For Production/DR
If you have previously purchased SQL Server licenses, you need to download your product key from the licensing portal and then follow these instructions to apply the product key to your instance of SQL Server.
If you have not purchased licenses, speak to your licensing reseller to obtain licenses, then follow the instructions to update your license key.
NOTE: You can upgrade your evaluation license to Enterprise, Standard or Web Edition of SQL Server depending on your requirements and available licenses.
For UAT
Follow these instructions for upgrading your edition, but instead of applying a license key, select Developer Edition, which is free for non-production workloads.


Database Tuning Advisor Index Recommendations

How does DTA make an index recommendation? What is the technology behind that?

You can find the answers in a detailed paper on the Database Tuning Advisor published by Microsoft (click on the PDF link on that page to download the complete document).

The missing index DMVs are not a good substitute for a full DTA analysis. The DMVs show only very limited information opportunistically gathered during query optimization. DTA performs a much more thorough analysis, though you should not automatically create every index it suggests. You need to assess each recommendation on its merits.


Yes, that (1.4.4) is the latest version of the utilities as of 2014-08-20 (although the version 1.5.1 is probably going to be released very soon). Since approximately when MySQL 5.6 was released, the MySQL utilities have had an independent download from MySQL workbench (so they can be easily installed on text-only servers) and they have its own numbering schema.

The MySQL Utilities 1.5 will probably be released at the same time that Workbench 6.2 (or close). You have the release notes here.


Strange Intermittent Postgres Performance Issue (docker?)

I think this is the answer. I'll update/remove this if I get any contra-indications.

In our (admittedly bizarre) use case, the tables in this ephemeral database are being loaded via COPY FROM statements. Then we start performing transforms.

My hypothesis is that the table statistics do not reflect the actual data population when the queries are run, causing the generation of a poor query plan.

I have forced the execution of an ANALYZE statement after the bulk load, but before the queries began. This seems to have addressed the issue.


percona xtrabackup to backup mysql database

It depends on your MySQL version that you're restoring. The short answer is: for anything under MySQL 5.6, yeah that would corrupt your existing dataset.

However, if you're using 5.6, there is a new feature to export/import individual tablespaces and Percona Xtrabackup supports it.

Some important caveats of the importable tablespace implementation of MySQL 5.6 are as follows:

You have to have innodb_file_per_table enabled.
DISCARD TABLESPACE is not supported for partitioned tables meaning that transportable tablespaces is also unsupported.
DISCARD TABLESPACE has some limitations regarding Foreign Keys

For other limitations, see the Tablespace Copying Limitations and Usage Notes section of the documentation.


Data loading problem with csv file from staging server to Database

The error has nothing to do with the content of the file.  The key message is:
syntax error: found "missing":

Literally, when parsing the control commands it found the word "missing" where it was expecting something else.  So what was immediately preceding the word "missing"??  This:
 FIELDS TERMINATED BY ',' optionally enclosed by
        MISSING FIELD VALUES ARE NULL

So, what are the fields 'optionally enclosed by' ????


How to list objects that are using another object?

This seems to be the query from TOAD when you click the used_by tab - you just need to replace the 'OBJECT_NAME' with whatever the name of the object you are interested in is, and change the object_type to whatever is appropriate for your object.

WITH Objs
     AS (    SELECT object_id
               FROM (SELECT object_id, referenced_object_id
                       FROM public_dependency
                       WHERE referenced_object_id <> object_id) pd
         CONNECT BY PRIOR object_id = referenced_object_id
         START WITH referenced_object_id = (select object_id from dba_objects where object_name = 'OBJECT_NAME' and object_type = 'TABLE'))
SELECT o.owner,
       o.object_type,
       o.object_name,
       o.object_id,
       o.status
  FROM sys.DBA_OBJECTS o, Objs
 WHERE o.Object_id = Objs.Object_id

You can view the SQL that TOAD generates by starting a second session, go into the session browser and then view the current statement to see the query your first TOAD session is running.


Update "NULL" string to Actual NULL value

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ' + QUOTENAME(name) + ' = CASE
  WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
  + QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);

SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';

PRINT @sql;
--EXEC sp_executesql @sql;


PostgreSQL two different ways to generate a UUID: gen_random_uuid vs uuid_generate_v4?

Are they the same?

No.

The Postgres documetation for uuid-ossp suggests using gen_random_uuid() If you only need randomly-generated (version 4) UUIDs,

The uuid-ossp extension also provides other types of UUID (such as mac-addresses based)

  The difference?

I looked at the source and discovered that

uuid_generate_v4() uses arc4random to determine the random part.
gen_random_uuid() uses fortuna instead.

Other than that they do the same job.


Is table partitioning improving performance? Is it worth it?

Using partitioning is only going to help your query performance if the partitioning scheme is built to serve your specific queries.

You're going to have to review your query patterns and see how they are accessing the table in order to identify the best approach. The reason for this is you can only partition on a single column (the partitioning key) and this is what will be used for partition elimination.

There are two factors that affect if partition elimination can occur and how well it will perform:

Partition Key - Partitioning can only occur on a single column and your query must include that column. For example, if your table is partitioned on date and your query uses that date column, then partition elimination should occur. However, if you don’t include the partition key within the query predicate, the engine can not perform elimination.
Granularity - If your partitions are to big, you won’t gain any benefit from elimination because it will still pull back more data than it needs to. However, make it to small and it becomes difficult to manage.

In many ways, partitioning is just like using any other index, with some added benefits. However, you don’t realize those benefits unless you're dealing with incredibly large tables. Personally, I don't even consider partitioning until my table is over 250 GB in size. Most of the time, well defined indexing will cover many of the use cases on tables smaller than that. Based on your description, you're not seeing huge data growth, so it could be that a properly index table will perform just fine for your table.

I would strongly recommend that you review whether partitioning is actually necessary to solve your issues. One would usually partition a very large table for the purpose of:

Distributing data between different types of disk so that more “active” data can be placed on faster, more expensive storage while less active data is placed on cheaper, slower storage. This is mostly a cost savings measure.
Assisting in index maintenance for extremely large tables. Since you can rebuild partitions individually, this can assist in keeping indexes properly maintained with minimal impact.
Leveraging partitioning for improved archival process.  See sliding windows.


It is a well known pattern.  If you want extensive literature on this topic, search on "temporal database".


How to display '0' where no data found in PL/SQL Oracle Reports

The format mask needs to be changed to 999,999,999,990.  The 9 format suppresses the value if it is a leading zero, whereas 0 prints it.


I solved problem using 'db.product.update' instead of 'db.product.save'


Oracle Developers guide 10g is not connecting to server

You said "I check my TNSNAMES.ORA file at my database host machine."  (emphasis mine).  The tnsnames.ora file is used by the client process. There are some other things to check as well. See this. But from what you've shown so far, I'd guess you are looking at the wrong file.  Oh, and since the nature of the problem means oracle couldn't even get as far as sending a request (because he didn't know where to send it), the listener never enters the picture for this error.


Optimizing queries against a set of tables that contain partitioned data

Before table partitioning was introduced the way of doing the very thing you asked was called Partitioned Views:

  A partitioned view joins horizontally partitioned data from a set of
  member tables across one or more servers, making the data appear as if
  from one table.

Partitioned Views are not only a UNION ALL over each table. By using appropriate check constraints on the underlying tables partitioning column the optimizer can do some fancy stuff, including partition elimination.

PS. Obviously you can generate the view pragmatically. You can even use Event Notifications to generate it automatically every time your 3rd party tool creates a new table, assuming you're running on SQL Server 2005 or later.


Azure SQL Database - Error Starting Extended Events Session

So the answer was to check every database in the elastic pool for extended event sessions and then stop and delete the few running ones I found. After that a retry worked.
The hint was in the error message :-

Check sys.dm_xe_database_sessions for active sessions that can be stopped or altered.

I'd like to claim I wrote some smart powershell to iterate through every database - but actually I did a LOT of clicking in SSMS (Powershell next time).


Can't run Alter Table command due to locks

Stop (do your alter) and start DTC and all will be fine.


How to find out all the permissions that belong to a specific server/ database role in SQL Server?

Hope the following would help:

Server Level permissions

For server role permissions
sp_srvrolepermission
For Logins permissions

select  ge.name as Grantee,
        gr.name as Grantor,
        o.name as ObjectName,
        p.permission_name,
        P.state_desc
from sys.server_permissions as p
        left join sys.server_principals as Ge on p.grantee_principal_id = ge.principal_id
        left join sys.server_principals as Gr on p.grantor_principal_id = gr.principal_id
        left join sys.objects as o on p.major_id = o.object_id

Database Level Permissions

select  ge.name as Grantee,
        gr.name as Grantor,
        o.name as ObjectName,
        p.permission_name,
        state_desc
from sys.database_permissions as p
        left join sys.database_principals as Ge on p.grantee_principal_id = ge.principal_id
        left join sys.database_principals as Gr on p.grantor_principal_id = gr.principal_id
        left join sys.objects as o on p.major_id = o.object_id
go


How to enable group replication again after dissolving a MySQL Innodb Cluster using mysqlsh?

creating a new innodb cluster and adding the instances will start the group replication again on those instances but I'm still unable to find how I can enable group replication manually with "start group replication" sql command without a cluster


Insert into a view derived from a table with a discriminator field

You should do your INSERTs into the base table only
That way you can INSERT queries like these
insert into base_table (a, b, c, x) VALUES (null, 'xxx', 'xxx',1);
insert into base_table (b, c, x) VALUES ('xxx', 'xxx',2);

If you do INSERT queries like these
insert into base_table (a, b, c) VALUES (null, 'xxx', 'xxx');
insert into base_table (b, c) VALUES ('xxx', 'xxx');

then you should assign a default value of 1 for x in the table definition
create table base_table (
  a int unsigned auto_increment primary key,
  b char(10) default null,
  c char(10) default null,
  x tinyint unsigned default 1
);

Otherwise, you should only INSERT into base_table since there exists no mechanism for dynamically switching the default value of x within the definition of a view. The x column has no ability to be selected in the view (because you left it out of the SELECT clause). Consequently, it would definitely have no ability to be updated or assigned in the view.
Give it a Try !!!


sqlite database: advanced SQL or redesign

I think the tables o_products, o_qtys, o_prices should be combined into a single table, with a one-to-many relationship with the orders table as follows:

order_details
(
    order_id INTEGER,
    order_item_id INTEGER,
    product_id INTEGER,
    price REAL,
    quantity INTEGER,
    PRIMARY KEY (order_id, order_item_id)
    FOREIGN KEY (order_id) REFERENCES Orders(o)
)

When you want to find out the total quantity of a given product, then

SELECT SUM(quantity)
FROM order_details
WHERE product_id = :product_id


Temporarily disable SQL Server Mirroring

From the documentation, you could just shutdown the witness instead of removing it from all the servers.
It says that the mirroring will then be a "partner-to-partner" quorum, and that no automatic failover will occur.
Of course, I would test it on a dev setup before trying it in production.

A partner-to-partner quorum that consists of the two partners.
As long as the partners retain quorum, the database continues in a SYNCHRONIZED state, and manual failover remains possible. Without the witness, automatic failover is not possible; but when the witness regains quorum, the session resumes regular operation, and automatic failover is supported again.


mysql 5.1 -- unknown variable 'basedir=c:/mysql/'

Thank for posting the my.ini

PROBLEM: You have the basedir and datadir under the [client] section

basedir and datadir are server-only options, not client program options.

SOLUTION : Remove them from under the [client] section and you are good to go !!!

When done, this

[client]
port= new_port

##The MySQL server
Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/MySQL/"
#Path to the database root
datadir="C:/MySQL/"
#Set the default character set.
default-character-set=latin1

should now be this:

[client]
port= new_port

Give it a Try !!!


The concept you are exploring is called "nested tables".  Doing a search on this will yield a lot of items to read.  I haven't read those items, so I'm not going to make recommendations.

Many database products, such as Oracle, have a provision for defining table types as domains, and then referencing those domains when defining columns of other tables.  These are called nested tables.  To my knowledge, standard SQL does not have such a provision.

The concept of nested tables is closely related to the concept of First Normal Form, which Ed Codd originally called simply Normal Form, back in 1970.  In his paper, he talked about how databases could be limited to First Normal Form without limiting the expressive power of the relational model of data.  It turns out that a relational model that conforms to 1NF can be implemented in SQL without using nested tables.

You might also want to revisit CJ Date's treatment of the subject, where he presents the idea that a relation is always in 1NF.

Revisiting the history of ideas in computing can seem dry and boring, but given the question you are exploring, these historical pointers I'm providing are vital to understanding why SQL has not latched onto this concept.

As you go deeper, you'll begin to see how this connects to another phenomenon, which is the difference between relational data modeling and object modeling.  Object modeling is profoundly different from relational data modeling, to the point where experts in one form of modeling think the other is incomprehensible.  This often causes problems when an application is designed using an object model and the database is designed using the relational model.  This is sometimes called the "object relational mismatch".

I can't do justice to Codd's thinking here, but in very simple terms, he expected the first few relational DBMS systems to employ a very simple form of indexing to speed things up, and a table with nested tables can't use simple indexes to speed up keyed reference to data in subtables.


GRANT does not have an expiration date, but roles have. So:
CREATE ROLE foo VALID UNTIL '2019-05-01 00:00+02';

GRANT my_privilege_bundle TO foo;

And my_privilege_bundle would be a group role bundling all required privileges.
The manual:

VALID UNTIL 'timestamp'
The VALID UNTIL clause sets a date and time after which the role's password is no longer valid. If this clause is omitted the password will be valid for all time.

Note that this does not end existing sessions. The role just can't make new connections with password after the expiration date. The manual once more

The VALID UNTIL clause defines an expiration time for a password only, not for the role per se. In particular, the expiration time is not enforced when logging in using a non-password-based authentication method.


What are these generic looking activities from sp_who2?

All of that appears to be the result of normal Sql Agent activity.

Assuming you don't have any 'real (non-system)' Sql Agent job running (meaning you don't really need the Sql Agent running), my suggestion would be to create a Windows Task scheduler process using SQLCMD to run your sp_who2 process on your defined interval and then STOP the Sql Agent.  What gets logged into your table then?


Find the user with closest taste of mine

You can make a table which holds the users with similar taste for every user.

CREATE TABLE `similar_users` (
    `uid1` INT,
    `uid2` INT,
    `score` INT,
    PRIMARY KEY (`uid1`, `uid2`)
)

The algorithm for calculating the score can be anything, as long as a higher score means a closer taste.

You you should limit the amount of similar users to a fixed number and also exclude the ones with a score less than a certain value (the ones with a not so similar tastes).

The contents of the table will be calculated periodically (e.g. every day).

Then step 3 from your solution becomes something like this:

SELECT w.film
FROM similar_users su
JOIN wich w ON w.uid = su.uid2 AND w.rate > 2
LEFT JOIN wich wn ON wn.film = w.film AND wn.uid = {current_user}
WHERE su.uid1 = {current_user}
ORDER BY su.score * w.rate DESC


Which file do I need to modify my.cnf MariaDB on Ubuntu 18.04

I had a similar problem but rectified it by adding the following line to /etc/mysql/my.cnf

!include /etc/mysql/mariadb.cnf

This seemed to fix the issue of the mariadb config file not being read.


Lost connection to MySQL server during query

8 million rows? - network speed will determine how long it takes to download the rows, but as to why you are losing connection, you'd have to dig deeper to see if you have a timeout setting?


Storing a 3D point array representing a flight path?

This is clearly geospatial. My advice to you is to install PostGIS. Store them as a point. Points can have z-cordinates. Creating a custom type is a bad idea.

CREATE TABLE foo (
  geog geography(POINT),
  alt  int
);

INSERT INTO foo(geog,alt) VALUES
  (ST_MakePoint(long,lat), alt);

If you want to extend to include time, just add a timestamp column.

I'm not sure what you mean when you say "an array of 3D points", but typically that either means you need to normalize into a point table, or store a MULTIPOINT, or by array you mean LINESTRING.

Flight path

If you're trying to store a flight path you want a LINESTRING you can create one with ST_MakeLine.

CREATE TABLE foo (
  id   serial                  PRIMARY KEY,
  path geography(LINESTRINGZ)
);
INSERT INTO foo(path) VALUES
  (
    ST_MakeLine(ARRAY[
      ST_MakePoint(1,2,3),
      ST_MakePoint(5,5,4),
      ST_MakePoint(10,10,5)
    ])
  );

# SELECT id, ST_AsText(path) FROM foo;
 id |             st_astext
----+------------------------------------
  1 | LINESTRING Z (1 2 3,5 5 4,10 10 5)
(1 row)


SQL daily value (today value - yesterday value)

The data don't fit your wanted resuLt, so i had to change the date
Schema (SQLite v3.30)
CREATE TABLE table1 (
  `ID` INTEGER,
  `Quantity` INTEGER,
  `DATE` DATE
);

INSERT INTO table1
  (`ID`, `Quantity`, `DATE`)
VALUES
  ('1', '40', '2020/10/10'),
  ('2',  '0', '2020/10/11'),
  ('3', '10', '2020/10/12');

Query #1
SELECT
*,
    Quantity - LAG ( Quantity, 1, 0 ) OVER (
        ORDER BY `DATE`
    ) PreviousYearTotal
    FROM table1;

| ID  | Quantity | DATE       | PreviousYearTotal |
| --- | -------- | ---------- | ----------------- |
| 1   | 40       | 2020/10/10 | 40                |
| 2   | 0        | 2020/10/11 | -40               |
| 3   | 10       | 2020/10/12 | 10                |

View on DB Fiddle


SQL Server - How does Always On in a shared nothing config maintain transaction durability during a failover?

You use synchronous commit mode. This means transactions committed on the primary will also be committed on the secondary (over your network). So, if you failover during a transaction it will be rolled back when the secondary is brought online after recovery.

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

  Synchronous-commit mode protects your data by requiring the data to be synchronized between two places, at the cost of somewhat increasing
  the latency of the transaction.


I have seen this happens a lot in Amazon RDS. This occurs with row-based replication when a Master get hammered with lots of writes and they get serialized to one I/O thread.

What you should do is dynamically change the following:

SET GLOBAL innodb_flush_log_at_trx_commit = 2;

Problem is you need the SUPER privilege, which neither Amazon RDS nor Google CloudSQL allow. For Amazon RDS, you change the value in the DB Parameter Group (the list of server options for the MySQL Instance). If you can change the dynamic option through some option group in Google CloudSQL platform, this is the option to change. When replication catches back up, change back to 1.

The answer posted by Rick James would be great if you had multiple databases in the CloudSQL instance. He said

  All operations going into a single database? Any chance of upgrading past 5.6; multi-threaded replication may help.

Try change the dynamic option for now to get rid of the current replication lag, and try his suggestion of going with MySQL 5.7 and setup multithreaded replication as the long-term solution.


InnoDB: SELECT COUNT(*) using primary key 25 times slower on Windows

It turns out that the difference was the size of innodb_buffer_pool_size. On the Win box it was too low.

It appears that, if you need the primary key, all the records (8 million times 1000 chars) need to be read. InnoDB seems to use the primary key as a "clustered index", meaning that it's welded to each record. That's why reading just the values of the secondary key takes so much less memory, and thus less use of the memory buffer. If it's too small, the read will take a long time.

Increasing innodb_buffer_pool_size to 8G helped speed up operations.

I certainly don't understand the minute details, please correct me if this is nonsense.


Split two delimited strings in same order without function

Create the function in msdb or somewhere else.

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  varchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH src(r) AS
      (
        SELECT 1 UNION ALL SELECT r + 1 FROM src WHERE r < 10
      ),
      Numbers(Number) AS
      (
        SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
        FROM src AS s1, src AS s2 -- add more if you need longer strings
      ),
      parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM Numbers AS n1, Numbers AS n2
        ON  n1.Number <= LEN(@List1)
        AND n2.Number <= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );

Then, as @gbn noted, reference it by 3-part name wherever your query has to run.

CREATE TABLE #temp
(id   INT,
 keys VARCHAR(50),
 vals VARCHAR(50)
);

INSERT INTO #temp
VALUES
(1, '1,2,3', 'one,two,three'),
(2, '4,5,6', 'four,five,six'),
(3, '7,8,9', 'seven,eight,nine');

SELECT t.id, f.s1, f.s2 FROM #temp AS t
  CROSS APPLY msdb.dbo.SplitTwoStringsWithSameOrder(keys, vals, ',') AS f
  ORDER BY t.id, f.r1;
GO

DROP TABLE #temp;

Results:

The resulting plan, shown in Plan Explorer (disclaimer: I'm the Product Manager), is not the prettiest thing I've ever seen (click to enlarge a little bit):

But there is exactly one scan of #temp (4% cost). The biggest costs are two sorts and a spool, and there is some I/O due to a worktable which I am not sure is avoidable.

If you KNOW you will only ever have 50 characters in either of these strings, then you can get a much simpler plan with a built-in Numbers table (people object to these, but they're very useful, and they are almost always in memory if you reference them enough). This doesn't help I/O but removing the recursive CTE and other constructs of building the numbers inside the function is quite helpful for CPU etc.

First, the numbers table:

DROP TABLE dbo.Numbers;

;WITH n AS
(
    SELECT
        TOP (50) rn = ROW_NUMBER() OVER
        (ORDER BY [object_id])
    FROM sys.all_columns
    ORDER BY [object_id]
)
SELECT [Number] = rn - 1
INTO dbo.Numbers
FROM n;

CREATE UNIQUE CLUSTERED INDEX n ON dbo.Numbers([Number]);

Then a second version of the function:

CREATE FUNCTION dbo.SplitTwoStringsWithSameOrder2
(
    @List1  varchar(50),
    @List2  varchar(50),
    @Delim  nvarchar(10)
)
RETURNS TABLE
AS
    RETURN
    (
      WITH parsed(s1,s2,r1,r2)
      AS
      (
        SELECT
          SUBSTRING(@List1, n1.Number, CHARINDEX(@Delim, @List1
            + @Delim, n1.Number) - n1.Number),
          SUBSTRING(@List2, n2.Number, CHARINDEX(@Delim, @List2
            + @Delim, n2.Number) - n2.Number),
          r1 = ROW_NUMBER() OVER (ORDER BY n1.Number),
          r2 = ROW_NUMBER() OVER (ORDER BY n2.Number)
        FROM dbo.Numbers AS n1
        INNER JOIN dbo.Numbers AS n2
        ON  n1.Number <= LEN(@List1)
        AND n2.Number <= LEN(@List2)
        AND SUBSTRING(@Delim + @List1, n1.Number, LEN(@Delim)) = @Delim
        AND SUBSTRING(@Delim + @List2, n2.Number, LEN(@Delim)) = @Delim
      )
      SELECT s1, s2, r1, r2 FROM parsed WHERE r1 = r2
    );
GO

Here is the simpler plan that results (again, click to enlarge):

The plan still has two sort operations, but the spool is gone, there is still only one scan of #temp, and in my limited tests the cost numbers (absolute cost numbers, not %) were better every time.

I don't know precisely either of these will scale with a lot more rows, but it's worth testing, and if you weigh this against other solutions and it can't scale well, that may be a point your reconsider the design (store these relationally instead of as comma-separated sets).


SQL Server - Identical query on near identical database takes 20 seconds on sql2012 but does not complete after 50+ hours on sql2014

Why is there such a difference in performance...

Check out this part of the estimated plan for the slow query (second server):

This estimates that 21 rows will come out of the join to RatesByCategory (which is after _Results323 has been joined to AttributesTable twice, and DataStreamDirection).  If we look at the equivalent part of the actual plan for the fast query:

There will actually be ~16,000,000 rows coming out of that join.  These is bad news, because those millions of rows flow right into a wall of Nested Loops joins, which is likely where that query slows to a crawl.

You were on the right track looking at estimates and trying to update stats, but this actually seems to be because of a row goal set by the semi-joins later in the execution plan.

Here's where the semi-join lives, and notice that everything "under it" has an estimate of 1 row:

  ...and what can I do to fix this?

Try adding this hint to the end of the query:

OPTION (QUERYTRACEON 4138)

This will disable the row goal and should you give you a different plan (possibly similar to the one on the lower compatibility level).

Since you said this query is generated, your best bet for solving this might be to create a plan guide so that you can add that hint without having to change the application that produces the query.

  If i try running the query on the second, but changing the compatibility level to 120 with trace 2312, the query finishes in 40 minutes.

Changing compat level to 120 would have enabled the "new" cardinality estimator, resulting in a different query plan - likely one that was less affected by the row goal (and thus why you were able to see it finish in 40 minutes, where the compat 110 query ran for 50 hours without finishing).


Best way to write SQL Query that checks a column for non-NULL value or NULL

This pattern

column = @argument OR (@argument IS NULL AND column IS NULL)

can be replaced with

EXISTS (SELECT column INTERSECT SELECT @argument)

This will let you match a NULL with a NULL and will allow the engine to use an index on column efficiently. For an excellent in-depth analysis of this technique, I refer you to Paul White's blog article:

Undocumented Query Plans: Equality Comparisons

As there are two arguments in your particular case, you can use the same matching technique with @Blah – that way you will be able to rewrite the entire WHERE clause more or less concisely:

WHERE
  EXISTS (SELECT a.Blah, a.VersionId INTERSECT SELECT @Blah, @VersionId)

This will work fast with an index on (a.Blah, a.VersionId).

  Or the query optimizer makes it essentially the same?

In this case, yes. In all versions (at least) from SQL Server 2005 onward, the optimizer can recognize the pattern col = @var OR (@var IS NULL AND col IS NULL) and replace it with the proper IS comparison. This does rely on internal rewrite matching, so there may be more complex cases where this is not always reliable.

In versions of SQL Server from 2008 SP1 CU5 inclusive, you also have the option of using the Parameter Embedding Optimization via OPTION (RECOMPILE), where the runtime value of any parameter or variable is embedded in the query as a literal before compilation.

So, at least to a large extent, in this case the choice is a matter of style, though the INTERSECT construction is undeniably compact and elegant.

The following examples show the 'same' execution plan for each variation (literals versus variable references excluded):

DECLARE @T AS table
(
    c1 integer NULL,
    c2 integer NULL,
    c3 integer NULL

    UNIQUE CLUSTERED (c1, c2)
);

-- Some data
INSERT @T
    (c1, c2, c3)
SELECT 1, 1, 1 UNION ALL
SELECT 2, 2, 2 UNION ALL
SELECT NULL, NULL, NULL UNION ALL
SELECT 3, 3, 3;

-- Filtering conditions
DECLARE
    @c1 integer,
    @c2 integer;

SELECT
    @c1 = NULL,
    @c2 = NULL;

-- Writing the NULL-handling out explicitly
SELECT *
FROM @T AS T
WHERE
(
    T.c1 = @c1
    OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND
(
    T.c2 = @c2
    OR (@c2 IS NULL AND T.c2 IS NULL)
);

-- Using INTERSECT
SELECT *
FROM @T AS T
WHERE EXISTS
(
    SELECT T.c1, T.c2
    INTERSECT
    SELECT @c1, @c2
);

-- Using separate queries
IF @c1 IS NULL AND @c2 IS NULL
    SELECT *
    FROM @T AS T
    WHERE T.c1 IS NULL
    AND T.c2 IS NULL
ELSE IF @c1 IS NULL
    SELECT *
    FROM @T AS T
    WHERE T.c1 IS NULL
    AND T.c2 = @c2
ELSE IF @c2 IS NULL
    SELECT *
    FROM @T AS T
    WHERE T.c1 = @c1
    AND T.c2 IS NULL
ELSE
    SELECT *
    FROM @T AS T
    WHERE T.c1 = @c1
    AND T.c2 = @c2;

-- Using OPTION (RECOMPILE)
-- Requires 2008 SP1 CU5 or later
SELECT *
FROM @T AS T
WHERE
(
    T.c1 = @c1
    OR (@c1 IS NULL AND T.c1 IS NULL)
)
AND
(
    T.c2 = @c2
    OR (@c2 IS NULL AND T.c2 IS NULL)
)
OPTION (RECOMPILE);


Sql server 2016 Change only server and without all databases

You'll need to run setup with the REBUILDDATABASE option to change the server collation without changing the collation of existing user databases. This will create fresh system databases so you'll need to re-create server-level objects, re-attach user databases, and remap logins afterwards.

Note the tempdb collation will also be changed and can result in collation conflict errors after the operation depending on the existing user database collations and how queries are coded (e.g. creates temp tables without an explict DATABASE_DEFAULT column collation specified).

A system collation change is not a trivial matter and needs to be thoroughly tested.


It sounds like you have highly varying data, for example a few VARCHAR(MAX) columns that are sometimes filled in heavily and other times not at all. That is why 12,000,000 rows doesn't necessarily result in 12 times the size of 1 million rows. You need to use a larger sample size to more accurately determine what your data growth will be. For example if you wanted to know what the smaller table will look like in a month then you need to measure against at least a months worth of data (360,000,000 rows). And honestly probably should measure against a few months for a more accurate estimation if possible (though I'm assuming it's not since you're trying to do initial provisioning).

Without more information it's hard to tell why your log file is so relatively slow. What Recovery Model is your database set to? Is it possible someone ran a SHRINK operation against it?

The AlwaysOn solution won't materially affect the size on your Primary Replica. Though keep in mind that AlwaysOn results in a literal copy of your database in a Secondary Replica on a separate server, so you'll be storing two copies of the same data.

To answer your root question, the advice I gave in #1 in regards to using as big of a data sample for your calculations as possible and adding in a worst case estimate buffer is your best bet. E.g. if you calculate a months worth of data to be 1 TB, provision for 1.25 or 1.5 TB to be safe for the first month and adjust as needed over time. This will be a continuous task for you to revisit periodically, re-calculate, re-evaluate, and re-provision in the beginning until you become more intimate with your data. It's better to be safe over provisioning in the beginning than under provisioning.


Good database structure for scenario with orders that have a state and the state has a date

Since you already put order_state_id in your orders table why you don't put a timestamp or a datetime column along with it?

When you update order_state_id to the desired state update the timestamp along with it.

If you think that there is a high probability that your status list will expand over time then you may have a need to keep history of status changes for each order. In order to do that you'll need to throw out order_state_id and the timestamp from orders and create an association table like that will link orders with order_state :

orders_state_history
record_id (optional)
order_id
state_id
timestamp/datetime


Oracle AQ: History Table for Queue Table

It seems likely to me that you could use the retention property of the queue to do this. See http://docs.oracle.com/cd/B10500_01/appdev.920/a96587/qsample.htm#158007 and also dbms_aqadm.alter_queue for details. If you really need a separate table then how about creating a multiple subscriber queue and creating an audit subscription that populates the history table. I'd use built in functionality first.


How do I find the transactions making my log grow?

I found a number of troubleshooting tips for this situation here.

I've summarized them below:

"AVAILABILITY_REPLICA" indicates that the logged changes in the availability database at the primary replica that have not arrived and that these changes were applied to the availability database at one of the secondary replicas. Until logged changes arrive and are applied, the changes cannot be truncated from the availability database log at the primary replica.

Troubleshooting

"Log Send Queue" and "Redo Queue" are measurable data points during availability database synchronization. You can monitor these data points in order to determine whether an availability database log cannot be truncated because of the log uses type AVAILABILITY_REPLICA.

Log Send Queue -
When a transaction is performed at the primary, the logged blocks must be delivered and hardened to the database log file at the secondary. Any delay will prevent truncation of those logged changes in the database at the primary replica. A common reasons for a sustained log send queue is latency in the network or during hardening (write to disk) of the log blocks on the secondary.
Redo Queue -
As soon as it is hardened to the secondary database log file, a dedicated redo thread applies the log records. If the redo operation cannot keep up with the transaction log that is generated, log growth may occur. If the secondary replica redo operation is behind in applying those changes to a corresponding secondary database, the primary will truncate the transaction log.

Identify the secondary database that is delaying log truncation

If there is more than one secondary, there may be a particular secondary that is responsible for the log truncation issue. To identify the secondary database that is delaying log truncation, log on to the primary replica, query the truncation_lsn column of the sys.dm_hadr_database_replica_states dynamic management view (DMV), and then review the log_send_queue and redo_queue data points to help diagnose the issue.

The AlwaysOn dashboard and the sys.dm_hadr_database_replica_states DMV help monitor the redo progress. The following table lists some key fields:

For example, execute the following query against the primary replica in order to report the log_send_queue and redo_queue values for each secondary availability database.

SELECT ar.replica_server_name
    ,drs.truncation_lsn
    ,drs.log_send_queue_size
    ,drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
WHERE drs.is_local = 0

Corrective measures may include but are not limited to the following:

Make sure that there is no resource or performance bottleneck of the secondary.
Make sure that the Redo thread is not blocked in the secondary.
Make sure that the Redo thread does not fall behind because resource contention created a reporting workload.


If you are on Linux or relatives, you can use top to see what the top processes are.  If you hit 'c' within top it should show you the process titles, which will tell you if you have selects, updates, inserts, or if it is other maintenance tasks taking up the CPU (or if you have been hacked and are now mining cryptocurrencies for someone, which seems to be common lately).  You can also see if there is a small number of processes each using about a single CPU each, or a large number all fighting over their slice of the CPU.

You can use the pg_stat_statements to keep track of how many times each query was run and how long it took to run.  This reports the wall-clock time, not the CPU time.  If you turn on track_io_timing, you can also get the time spent reading from disk, which is good to know but still doesn't give you CPU time as some time could go to waiting on locks, or waiting for a slice of CPU time.

If the problem is more intermittent and hard to catch in the act, then auto_explain and log_min_duration_statement could help.

For more advanced profiling, you can use system tools like perf top or strace -y -p <pid> and maybe even gdb.  These usually require quite a bit of knowledge of the PostgreSQL guts in order to interpret them.  These are usually used to investigate ways to improve PostgreSQL itself (i.e. for future versions), but I often find they can help diagnose tuning problems as well.  You would have to install the debug symbols to get maximum use of perf or gdb.