How to stop a job for the rest of the day after the relevant condition has been satisfied once?

I actually wrote a similar tip at mssqltips.com.
For your current case, the general idea would be like this:

you may add an additional step at the end of the job, once your primary job steps succeed, the job will run the final step (i.e. the newly added step)
If your job fails in your primary steps, your job will just exit and no need to run the final added step.
In you final job step, you will do one thing, i.e. updating your job schedule' start date to be next day.

use msdb

declare @active_date int, @sch_name varchar(128);

select @sch_name=s.name
from msdb.dbo.sysschedules s
inner join msdb.dbo.sysjobschedules js
on js.schedule_id = s.schedule_id
and js.job_id = $(ESCAPE_NONE(JOBID));

set @active_date = cast(replace(cast(cast(getdate()+1 as date) as varchar(10)), '-', '') as int);

exec sp_update_schedule @name=@sch_name, @active_start_date= @active_date;

Of course, I assume your job has one dedicated schedule only.
The approach is all self-dependent and you do not need any additional external jobs to manage your current job.


Insufficient Privileges on selecting Views

Does USER_02 have permission to see VIEW_01? The grants on the view have nothing to do with the grants on the base tables. Being granted SELECT on the tables is not sufficient to have SELECT access to the view.

On your last paragraph, it's true that SCHEMA_01 needs to have the SCHEMA_09 tables granted to create a view against them, including the WITH GRANT OPTION so that SCHEMA_01 can be allowed to provide access to the SCHEMA_09 tables to other users through the VIEW_01 view. However, it sounds like you already have this if the view was created without errors.


Backing up database with missing datafile

You can specify SKIP options in your backup command.

For example:

BACKUP DATABASE SKIP INACCESSIBLE;

or take the datafile offline, and:

BACKUP DATABASE SKIP OFFLINE;

skipSpec


PostgreSql JSONB SELECT against multiple values

From docs (http://www.postgresql.org/docs/9.4/static/datatype-json.html)
try to use expression index:

CREATE INDEX idx_jsonthings_names ON jsonthings USING gin ((d -> 'name'));
SELECT d FROM jsonthings WHERE d @> '{"name": ["First", "Second"]}';


How to get all messages raised when a statement fails

This is expected behavior with TRY/CATCH in T-SQL.  The related Connect bug report was closed as "won't fix":

You'll need to use a client application (e.g. SQLCMD, PowerShell) to capture all error messages.


Add an index if you'll use your temp table and its index twice or more during the query run.

Or to maintain usual index tasks, like uniqueness

If your data loaded into temp table are already sorted, the to create temp table with the same clustered index as sort of data

BUT

taking into account sql server's feature of temp tables reuse- if you decide to create an index on temp table - try to do it in CREATE TABLE statement. If you'll add an index explicitly after table creation - it will prevent sql server to reuse that table next time


ROW_NUMBER() is a ranking window function, and ranking window functions require a mandatory ORDER BY clause.
If you try to write it without the ORDER BY you will get a syntax error.

SELECT ROW_NUMBER() OVER()
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 4112, Level 15, State 1, Line 1
-- The function 'ROW_NUMBER' must have an OVER clause with ORDER BY.

The trick with the subquery was discovered by someone who blogged about it, as a performance optimization.
SQL Server always performs a sort operation since constants are not allowed for the ORDER BY clause:

SELECT ROW_NUMBER() OVER(ORDER BY NULL)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5309, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions
-- do not support constants as ORDER BY clause expressions.

And neither are integers which are treated as indices:

SELECT ROW_NUMBER() OVER(ORDER BY 1)
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);
-- Msg 5308, Level 16, State 1, Line 1
-- Windowed functions, aggregates and NEXT VALUE FOR functions
-- do not support integer indices as ORDER BY clause expressions.

Turns out that due to some glitch in the code, you can circumvent this limitation by using a subquery, which for some reason is allowed, and eliminates the sort operator.

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1))
FROM (VALUES ('A'), ('B'), ('C')) AS X(Y);

You can use any constant in the sub query, the NULL is probably a reminiscent of the habit of using SELECT NULL within EXISTS predicates, which in the early history of SQL Server had a performance impact as opposed to * or any other column expression, as the optimizer wasn't smart enough to ignore it.

HTH

UPDATE
@Erik-Darling commented that you can also circumvent it by using a computed expressions:

You can do SELECT ROW_NUMBER() OVER (ORDER BY 1/0);


cardinality & column order in compound index (MySQL/InnoDB)

It is an old wives' tale that cardinality matters.

INDEX(a,b) and INDEX(b,a) perform virtually identically when in a BTree.  The depth of the BTree is the same.

What does matter is that both columns are searched with =, as in WHERE a=12 AND b=45.

The PRIMARY KEY in InnoDB is a BTree, and so is each secondary index.  The only difference is what else is in the leaf nodes.  The PK's leaf nodes contains all columns; a secondary index's leaf nodes contains the columns of the PK.

In InnoDB (not in some competing products), the PK is, by definition, clustered and UNIQUE.

As for your table,...

Are you allowing to rows for each ref_a--ref_b combo?  One 'active', one not?  This seems unlikely.

Why have id at all?  Why not promote the UNIQUE key to be the PK?

Is is_active ever NULL?

See my tips on many:many mapping tables.


How to set Database Recover Mode to Full for all databases?

How can I automatically execute this on all databases in server that are in simple mode now (using SQL Server 2012) ?

Below T-SQL will help you. It will check for ONLINE databases with SIMPLE  recovery model and will print TSQL to change it into FULL Recovery mode. Run below code in TEXT Mode -- SSMS CTRL+T. Once you are happy, run the output generated  from another session :

set nocount on
go

if exists (
        select 1
        from sys.databases
        where recovery_model_desc = 'SIMPLE'
            and state_desc = 'ONLINE'
        )
begin
    print '-- You are setting up database to FULL recovery mode. '
    print '-- Make sure you take first full backup and then schedule LOG BACKUPS for proper transaction log maintenance !'

    select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET RECOVERY FULL with ROLLBACK IMMEDIATE;'
    from sys.databases
    where recovery_model_desc = 'SIMPLE' -- since you only want SIMPLE recovery model databases to get changed to FULL recovery.
            and state_desc = 'ONLINE'
end
else
    select 'ALL the databases are in FULL RECOVERY MODE - Make sure you take proper LOG BACKUPS !!'

NOTE : For any databases that are newly created, its better to change the recovery model of model database to FULL, so that the new databases that are created inherit the FULL recovery setting from model database.

Worth mentioning : Why Does the Transaction Log Keep Growing or Run Out of Space?, since you are going to switch to FULL recovery and if you dont take care of your transaction log, then you will end up with a bloated transaction log. Read the answer linked for more details.


Values larger than 1/3 of a buffer page cannot be indexed

You have an UNIQUE index on (content, ref_id), called editor_contentmodel_content_2192f49c_uniq

"editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id)

I'm not sure why this there to begin with. So let's step back and address what this does. This makes sure that content, and ref_id are unique. However, in PostgreSQL the UNIQUE constraint is implemented with a btree which makes this a poor solution. Using this method, you're creating a btree with content which essentially duplicates the size of this small table, and makes for a gigantic index. A gigantic index that is still limited by content size though -- as you've found. It raises a few questions

Do you care that content is unique? If you do care that content is unique for ref_id, then what you probably want is to store the hash of the content. Something like..

CREATE TABLE foo ( ref_id int, content text );
CREATE UNIQUE INDEX ON foo (ref_id,md5(content));

This will instead store the md5sum of content on the btree. So long as ref_id has content with a unique md5 over that ref_id, you're good.
If you don't care that content is unique consider removing it entirely.

It may be worth nothing that when you implement a UNIQUE constraint with a btree (as PostgreSQL does), you get an added index for free. Under a normal circumstance this has a fringe benefit.

CREATE TABLE foo ( ref_id int, content text );
CREATE UNIQUE INDEX ON foo (ref_id,content);

Will speed up the query

SELECT *
FROM foo
WHERE ref_id = 5
  AND content = 'This content'

However, when you chance it to use the functional md5() variant there is no longer an index on content, so now to use that index you'll have to

Only query on ref_id,
Add to ref_id a clause that md5(content) = md5('This content')

The whole text = text is over rated. That's almost never what you want. If you're looking to speed up query-time over text the btree is pretty useless. You likely want to look into

pgtrgm
text_pattern_ops
Full text search (FTS)

UPDATE 1

Base on your JSON I would suggest storing it as a jsonb, and then creating the index on md5(content); so perhaps rather than the above instead run this.

ALTER TABLE public.editor_contentmodel
  ALTER COLUMN content
  SET DATA TYPE jsonb
  USING content::jsonb;

CREATE UNIQUE INDEX ON foo (ref_id,md5(content::text));

UPDATE 2

You ask which indexes you should remove

gollahalli_me_django=> create unique index on editor_contentmodel (ref_id, md5(content::text));
CREATE INDEX
gollahalli_me_django=> \d editor_contentmodel;
        Table "public.editor_contentmodel"
  Column   |           Type           | Modifiers
-----------+--------------------------+-----------
 ref_id    | character varying(120)   | not null
 content   | jsonb                    | not null
 timestamp | timestamp with time zone | not null
Indexes:
    "editor_contentmodel_pkey" PRIMARY KEY, btree (ref_id)
    "editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id) <---- 1
    "editor_contentmodel_ref_id_md5_idx" UNIQUE, btree (ref_id, md5(content::text))
    "editor_contentmodel_ref_id_8f74b4f3_like" btree (ref_id varchar_pattern_ops) <----2

Here is the surprising answer: you should remove all of them except: editor_contentmodel_pkey which says that all ref_id's need to be unique.

editor_contentmodel_content_2192f49c_uniq this index makes sure that you're UNIQUE on ref_id AND content, but if you can't have a duplicate ref_id you can never have a duplicate content for that ref_id. So you can never violate this index without also violating editor_contentmodel_pkey. That makes it pointless.
editor_contentmodel_ref_id_md5_idx this index is also pointless for the same reason. You can never have a duplicate md5(content::text) over ref_id because regardless of what the value of md5(content::text) is you can never have a duplicate ref_id.
editor_contentmodel_ref_id_8f74b4f3_like is also a bad idea because you're duplicating the index over ref_id. This isn't useless, it's just not-optimal. Instead, if you need a varchar_pattern_ops use it instead over just the content field.

As a last note, we don't much use varchar in PostgreSQL because it's implemented as a varlena with a check constraint. There is no gain to it, and there is nothing lost when you simply use text. So unless there is a concrete reason why ref_id can ever be 120 characters but it can be 119 characters, then I would simply use the text type.

UPDATE 3

Let's go back to your earlier problem..

psycopg2.OperationalError: index row size 3496 exceeds maximum 2712 for index "editor_contentmodel_content_2192f49c_uniq"

This is telling you the problem is specifically with index "editor_contentmodel_content_2192f49c_uniq". You've defined that as

"editor_contentmodel_content_2192f49c_uniq" UNIQUE CONSTRAINT, btree (content, ref_id)

So the problem here is that you're trying to create an index over content. But, again, the index itself stores the actual json content of content, and that's what exceeds the limit. This isn't actually an issue, because even if that limit wasn't in place editor_contentmodel_content_2192f49c_uniq would be totally useless. Why? again you can't add more uniqueness to a row that is already guaranteed to be 100% unique. You don't seem to be getting this. Let's keep it simple.

ref_id | content
1      | 1
1      | 1
1      | 2
2      | 1

In the above a lone unique index/constraint (with no other indexes) over (ref_id, content) makes sense because it would stop the duplication of (1,1). An index over  (ref_id, md5(content)) would also make sense because it would stop the duplication of (1,1) by proxy of stopping the duplication of (1, md5(1)). However all of this works because in the example I've given ref_id is NOT guaranteed to be UNIQUE. Your ref_id is not this ref_id. Your ref_id is a PRIMARY KEY. That means it is guaranteed to be UNIQUE.

That means the duplicate (1,1) and the row of (1,2) could NEVER be inserted. That also means that indexes over anything in addition to ref_id can not guarantee more uniqueness. They would have to be less strict than the index you currently have. So your table could only look like this

ref_id | content
1      | 1
2      | 1


Indexed columns in SQL Server do not appear to work for basic queries according to execution plan

If you want SQL Server to use that index for that specific query, you need to include the session_id column.  Otherwise, for each row it finds it will have to go do a key lookup into the base table.  It will choose to do this for small result sets, but once you get above a certain number of rows ("the tipping point"), SQL Server thinks it's more efficient to just scan the whole base table.
You can use the INCLUDE clause of the CREATE INDEX syntax to accomplish this:
CREATE NONCLUSTERED INDEX IX_Start_Time
ON GoogleAnalytics.SessionStartTimes (session_start_time)
INCLUDE (session_id);


Disk sizing considerations for 'tempdb' - Growing substantially two times a year

The "real" answer is: work with HR and/or their software vendor to fix or improve their twice-annual jobs so they don't do that.

Assuming that's not possible, you're left with two three lousy options:

Leave tempdb huge all year long.
Leave enough empty space on disk so that tempdb can grow huge, then shrink it back down immediately afterwards.
Coordinate with systems to add disk space just before processing, then shrink tempdb afterwards and recover the disk space.

1 and 2 are pretty much functionally identical, although I'd tend toward 2 (just in case the vendor got its act together sometime in the last 6 months and fixed their processing job without you knowing it).

Exactly how to undertake #3 would depend on lots of technical details that will vary by your environment. One method I'd consider would be:

Before processing, add a large temporary drive (say z:) to the server
Do an ALTER DATABASE tempdb MODIFY FILE for each tempdb file, specifying both the new location on z: and a larger file size
Restart SQL for that change to take effect (SQL re-creates tempdb every time it restarts)
Let tempdb use up as much space as it needs on z: during processing
After processing is done, re-execute the ALTER DATABASE tempdb MODIFY FILE to redirect tempdb back to the original drive (and the original smaller size)
Restart SQL again for the change to take effect
Drop/remove the now-empty disk

In our environment, this is preferable because (with our particular configuration of SAN/virtualization), it happens to be vastly easier to add and later remove a drive than it is to expand an existing drive and (especially) to shrink an existing drive. (If I remember right, shrinking an existing drive literally requires presenting a whole new disk and migrating data). But your mileage may vary depending on your configuration.


You could do something like this I suppose:

DECLARE @EndDate DATE
SET @EndDate ='20160728' --{?EndDate}

SELECT
    A.LineType,
    CASE B.ItemCode WHEN 'FG/RM/001' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/001],
    CASE B.ItemCode WHEN 'FG/RM/002' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/002],
    CASE B.ItemCode WHEN 'FG/RM/003' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/003],
    CASE B.ItemCode WHEN 'FG/RM/004' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/004],
    CASE B.ItemCode WHEN 'FG/RM/005' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/005],
    CASE B.ItemCode WHEN 'FG/RM/006' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/006],
    CASE B.ItemCode WHEN 'FG/RM/007' THEN CASE A.LineType WHEN 'Litres' THEN SUM(B.IssuedQty) WHEN 'Tons' THEN SUM(B.CmpltQty) ELSE Null END END AS [FG/RM/007]
FROM
    (
        SELECT 'Litres' AS LineType
    UNION ALL
        SELECT 'Tons' AS LineType
    ) A
CROSS JOIN
    (
        SELECT T1.ItemCode,SUM(T0.IssuedQty) [Litres],SUM(T1.CmpltQty) [Tons]
        FROM    WOR1 T0 INNER JOIN
                OWOR T1 ON T0.DocEntry = T1.DocEntry INNER JOIN
                OITM T2 ON T1.ItemCode = T2.ItemCode
        WHERE   T0.ItemCode ='FURNACE OIL/0001' AND T1.Type ='S' AND
                (T1.PostDate BETWEEN (SELECT Dateadd(month, Datediff(month, 0, @EndDate), 0)) AND @EndDate)
        GROUP BY T1.ItemCode
    ) B
GROUP BY
    A.LineType


Recommended best practice for server naming

I would say, if you have a naming convention, stick to it. Having 3 naming conventions due to chaninging best naming conventions over time, is not OK.


MySQL support a FROM_UNIXTIME(...) function :-)

select FROM_UNIXTIME(1433433155)

And the other way around, UNIX_TIMESTAMP

Applying it to your example, assuming timecreated and timemodified are those Unix timestamps:

SELECT
      category
      ,fullname
      ,shortname
      ,idnumber
      ,startdate
      ,visible
      , FROM_UNIXTIME (timecreated)
      , FROM_UNIXTIME (timemodified)
  ,count( my.id ) AS mydata
FROM mdl_mytable
JOIN mycontext ct ON ( ct.my = cat.id )
LEFT JOIN myjoin ra ON ( mycontext = cat.id )
WHERE ct.mynumber =10000000
GROUP BY shortname, fullname, .category
      ,.idnumber
      ,.startdate
      ,.visible
      , FROM_UNIXTIME (timecreated)
      , FROM_UNIXTIME (timemodified)
ORDER BY myorder ASC


Soft delete: Copy row data to archive table when deleting

(This answer is assuming SQL Server)

-- create the audit table
select *
into YourTableDeleteAudit
from YourTable
where 1 = 2
go

-- create the trigger
create trigger YourTableDeletes
on YourTable
after delete
as
    insert into YourTableDeleteAudit
    select *
    from deleted
go

This would result in an audit table of a copy of the deleted records.  I would recommend putting a datetime stamp on that (and maybe more fields for typical auditing).  You could do that like this:

-- create the audit table
select *
into YourTableDeleteAudit
from YourTable
where 1 = 2
go

alter table YourTableDeleteAudit
add DeleteDate datetime null
go

-- create the trigger
create trigger YourTableDeletes
on YourTable
after delete
as
    insert into YourTableDeleteAudit
    select *, getdate()
    from deleted
go


Is there a T-SQL equivalent for punctuation as [0-9] is for numbers and [a-z] is for letters?

The biggest difficulty in coming to a precise solution is in defining exactly what characters are to be included (or excluded, whichever direction makes more sense for the operation). Meaning:

Are we talking about VARCHAR / ASCII data or NVARCHAR / Unicode data? The list of punctuation characters for ASCII data depends on the Code Page which in turn depends on the Collation. (in this Question we are dealing with ASCII data).
Are we dealing with case-sensitive or case-insensitive searches?
What Collation is the column set to? The Collation will tell us both the Code Page and case-sensitivity. (in this question we are dealing with Latin1_General_CI_AS)
is the term "punctuation" to mean just standard punctuation characters (e.g. ., ,, ;, :, etc) or does it mean non-alphanumeric characters?
Are whitespace characters included?
Are Control characters included?
What about currency symbols such as ¢, £, ¥, etc?
What about symbols such as © and ™?
What characters are considered "alpha"? Are non-English characters such as Â, É, Ñ, ß, Þ included?
Since this Question deals with UK keyboards (see discussion for this Question), what about the Æ / æ character?

In order to help facilitate clarity regarding expected behavior, the following query will show all 256 characters of the Latin1 character set (i.e. Code Page 1252) and how two variations of @Shaneis's proposed solution operate. The first field (labeled as Latin1_General_CI_AS) shows the LIKE clause as proposed by @Shaneis (as of this writing) and the second field (labeled as Latin1_General_100_BIN2) shows a modification where I overrode the Collation to specify a binary one (i.e. a Collation ending in _BIN2; the _BIN Collations are deprecated so don't use them if you have access to the _BIN2 versions) which meant I also needed to add in the A-Z range to filter out upper-case letters as the current Collation is case-insensitive:

;WITH nums AS
(
  SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS [Decimal]
  FROM   [master].[sys].[all_objects]
)
SELECT nm.[Decimal],
       CHAR(nm.[Decimal]) AS [Character],
       CASE WHEN CHAR(nm.[Decimal]) LIKE '%[^a-z0-9]%'
               THEN 'x' ELSE '' END AS [Latin1_General_CI_AS],
       CASE WHEN CHAR(nm.[Decimal]) LIKE '%[^a-z0-9A-Z]%' COLLATE Latin1_General_100_BIN2
               THEN 'x' ELSE '' END AS [Latin1_General_100_BIN2]
FROM   nums nm;

UPDATE

It should be mentioned that IF one is truly seeking to find characters that are classified as being "punctuation" (and not "currency symbol", "mathematical symbol", etc), and IF one is not prohibited from using SQLCLR / loading a custom Assembly (SQLCLR was introduced with SQL Server 2005, and I have yet to come across a good reason for not allowing it, especially since Azure SQL Database V12 supports SAFE Assemblies), then you can use Regular Expressions, but not for the reason that most people would guess.

Rather than using Regular Expressions to build a more functional character range, or even rather than using something like \w (meaning any "word" character), you can specify the Unicode Category of the characters you want to filter on, and there are several defined categories:

https://www.regular-expressions.info/unicode.html#category

You can even specify the Unicode Block to filter on, such as "InBengali" or "InDingbats" or "InOptical_Character_Recognition", etc:

https://www.regular-expressions.info/unicode.html#block

There are numerous examples of creating RegEx functions for SQL Server (though most examples don't follow SQLCLR best practices), or you can download the Free version of the SQL# library (which I created), and use the scalar RegEx_IsMatch function as follows:

SQL#.RegEx_IsMatch(Unicode-String-Expression, N'\p{P}', 1, NULL)

The \p{P} expression means \p = Unicode Category, and {P} = all punctuation (as opposed to a specific type of punctuation, such as "Connector Punctuation"). AND, the "Punctuation" category includes all punctuation across all languages! You can see the full list on the Unicode.org site via the following link (there are currently 717 Code Points in that category):

http://unicode.org/cldr/utility/list-unicodeset.jsp?a=%5B%3AGeneral_Category%3DPunctuation%3A%5D

An updated version of the test query shown above, including another field that uses SQL#.RegEx_IsMatch with \p{P}, and the results of all 3 tests across all 256 characters of Code Page 1252 (i.e. Latin1_General) has been posted on PasteBin.com at:

T-SQL query and results for filtering types of characters

UPDATE
The following was mentioned in the related discussion:

  You've made a good point about accented characters, with them being hotel names from around the world there will be accented characters in the names, for my problem I would want to class these as valid alpha characters.

In this case:

There are 11 non-English characters that are included in the Latin1 character set / Code Page that are not matched by the a-z range. They are: ð Ð  Þ þ  œ Œ  š Š  ž Ž  Ÿ. These need to be added to the wildcard, and while not necessary at the moment, it wouldn't hurt to add in A-Z so that the pattern works just as well on a case-sensitive collation. The end result is:
LIKE '%[^a-zA-Z0-9ðÐÞþœŒšŠžŽŸ]%'
Considering that this data can include "hotel names from around the world", I would highly recommend changing the datatype of the column to be NVARCHAR so that you can store all characters from all languages. Keeping this as VARCHAR runs a very high risk of eventually having data loss since you can only represent the Latin-based languages, and not even fully for those given the six supplemental Unicode categories that provide additional Latin-related characters.


mysql database update possible timing issue

The issue was that since the queue run for 1 minute, there were cases that one cronjob was not finished yet, and another was starting. So they were processing the same tasks from the database.

The solution is to run cronjob using flock(), thus making sure that only 1 is run:

* * * * * /usr/bin/flock -n /tmp/fcj.lockfile /usr/local/bin/frequent_cron_job


why from_utf8 does not exist in postgresql?

As documented in the the manual the function you are looking for is convert_from()

Converts a binary string representing text in encoding src_encoding to text in the database encoding

So the query should probably be:
SELECT convert_from(name, 'UTF8') AS name,
       COUNT(DISTINCT target) AS cnt
FROM directory_entry_file
GROUP BY convert_from(name, 'UTF-8')
ORDER BY cnt DESC
LIMIT 1;


SELECT @a := @a + 1 serial, name, age
FROM students, (SELECT @a :=0) AS dummy
WHERE age > 22;

This works for MySQL. I was modifying from this example.


Using Mixed mode authentication to authenticate Active Directory users to an application

Having an application connect would be very different than an end user on SSMS.  Applications tend to use connection strings.  Your applications connection string or config might need to be modified to connect to the SQL Server as an AD user as opposed to putting in a username/password for a SQL user.

You will want that user to have the same rights as the SQL User and probably want it to own the database.  Contacting the vendor might be a good case.  Going with AD users gives you more auditing visibility anyways which should please auditors.

Some connection string examples:
https://www.connectionstrings.com/sql-server-2012/


Configuring Transactional Replication for an OLTP Database

Using RCSI on the subscriber(s) is a common way to design this type of a scenario to avoid blocking. RCSI will allow readers and writes to play nice together but won't solve writers blocking writers. Since the report queries are readers and the transactional replication is a writer, this feature is a good fit for this.

You just need to make sure that your TempDB is configured to support your workload for the versioning. Also remember that enabling RCSI adds 14 bytes to each inserted/updated row which might cause internal fragmentation.


Error: Incorrect usage of UNION and ORDER BY

Wrap the selects into parentheses

(SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) = EXTRACT(month FROM (NOW())) ORDER BY MONTH(`Date`) ASC )
UNION
(SELECT DISTINCT CONCAT (MONTH(`Date`),'-',YEAR(`Date`)) from wp_result_plugin WHERE Bet=2 AND month(`Date`) ORDER BY MONTH(`Date`) ASC)


Missing FROM-clause entry with constraint on value from type in PostgreSQL

You need to put the domain name in parentheses:

CONSTRAINT price_cannot_be_less_than_ten
  CHECK ( (oi_article_price).m_amount >= 10)


Performance of XML to Table Function in Stored Procedures

To answer part of your question, yes there is a better way to parse the values from the XML.

Always( * ) extract the text() from the xml node at the earliest opportunity.

( * - "it depends" but always test to make sure)

In your case, this means changing the "nodes" method to use the text() node as part of the xpath query:

ALTER function [dbo].[XMLIdentifiers] (@xml xml)
returns table
as
return (
    --get the ids from the xml
    select Item.value('.', 'int') as id from @xml.nodes('//id/text()') as T(Item)
)

In this simple test, you will see not only does a simple statistics test show a dramatic improvement, but also the execution plan changes significantly.

declare @x xml
select @x = (select top(100000) row_number() over(order by @@spid) as [n] from sys.columns as [a],sys.columns as [b] for xml auto,elements,type);

declare @c int;

set statistics io,time on;

-- Usual suspect
select @c = nd.value('.','int')
from @x.nodes('//n') x(nd);

-- Using text() when we might also need other parts of the node
select @c = nd.value('(./text())[1]','int')
from @x.nodes('//n') x(nd);

-- Using text() when that is all we need from the node
select @c = nd.value('.','int')
from @x.nodes('//n/text()') x(nd);

set statistics io,time off;

RESULTS

Not using text():
   CPU time = 1281 ms,  elapsed time = 1459 ms.

Using text() late:
   CPU time = 719 ms,  elapsed time = 739 ms.

Using text() early:
   CPU time = 406 ms,  elapsed time = 473 ms.


what is the best approach for messaging system with mysql?

My advice to you would be to download a few (3 - 5 of the most popular) apps which perform similar functionality to what you want to achieve.

Then, I would adopt and adapt [some | a few | various] ideas from these to obtain what you want - or as close as you can reasonably come with reasonable effort. Particularly, check out their schemas - no matter what the database.

Just a word about database selection - if you haven't started this project yet, then permit me to recommend PostgreSQL instead of MySQL. It has windowing (analytic) functions, CTEs (Common Table Expressions - AKA WITH clauses), proper SET operators and CHECK constraints. This is just IMHO, but it is my firm belief that having this functionality integral to the database will save you a lot of time, grief and debugging from having to write all this functionality into your app yourself - or having to choose restricted functionality.


Security implications disabling promotion of distributed transaction for linked server

When you do INSERT INTO...EXEC locally, sql server will honor it, but when you use Linked server, sql server has to initiate a local transaction and promote / esclate to a distrubuted transaction.

From --> How to create an autonomous transaction in SQL Server 2008

  'remote proc transaction promotion' is a new option on SQL Server 2008, which allows you to control whether or not you want to enlist remote stored procedure call in a distributed transaction. When this option is off  (FALSE), the local transaction will not be promoted to distributed transaction. This is how we are able to separate outer and inner transactions in a "autonomous transaction" fashion.

As far as I am aware, there is no security implication.

Also, refer to sp_serveroption for more details.


Having a "dummy" location with id=0 to represent the unknown location is possible.

My personal solution would however to just use NULL for that purpose, i.e. set the location_id to NULL to reflect unknown locations. That will be simpler since you will not need the dummy location "0". Also it should be more efficient, as the foreign key constraint will not be checked when you insert or update a row with an unknown location. And if you have an index on location_id, then that index will not contain any key for the rows with NULL location ids. This is more efficient than having a large set of duplicates for location id "0".

After all, that is what NULLs are for: to represent the absence of information (whether that information is unknown or just does not apply).

The flip side is that your application must be ready to handle nulls. Looking up all records for a given location id remains the same. Looking up records with an unknown location will need changing: from WHERE LOCATION_ID=0 to WHERE LOCATION_ID IS NULL.


What is the difference between right/left placement of column name in a WHERE clause?

There is no semantic difference. It is purely a matter of style.

The first form is much more common, in my experience.

The order of WHERE clauses with respect to AND does not matter either.


SQL Server 2008 R2 SP1 - Tempdb "Available free space" showing negative value

Open Query Analyzer
Run DBCC UPDATEUSAGE('tempdb')
After it is done, check temdb value and it should show the true values.

Also, this might help you.

Also, check if there are any open transactions using dbcc opentran or sp_whoisactive (Adam Mechanic's SP)

This was a bug in SQL Server 2008 SP1 described here.

Out of curiosity, is it causing any performance problems ?


Automatic Failover or Database Mirroring?

What you're describing sounds like Microsoft Sql Server HADR commonly called AlwaysOn Availability Groups. It combines traditional Windows clustering with Database Mirroring to allow you a primary replica and up to 4 (Sql Server 2012) secondary replicas.

Once set up correctly it will provide you with a single IP address and port # to use as a connection target for your application. Setting up the mirroring portion is a bit more than just deploying your database to the connection though there are ways to automate that also.

The replicas may be geographically separated as well as residing on different sub-nets and domains.


Perhaps you could the following:

split each value into little two character strings
convert each 2-character string into a decimal numebr
XOR each decimal number side by side
CONCAT all results
Lower Case the Result

Sounds simple, eh??

Wanna see the code in MySQL ??

Here we go ...

SET @h1 = '439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2';
SET @h2 = 'c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43';
SELECT LCASE(GROUP_CONCAT(HEX(v1^v2) SEPARATOR '')) HashResult FROM
(SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
FROM (SELECT z+z-1 x FROM
(SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
 UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
 UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
) AAA) AA) A;

Wanna see it executed ??? Here we go ...

mysql> SET @h1 = '439c366749b149e0238c249682e015603efa46660a3c647d08bce69a184a7ab2';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @h2 = 'c0196f85f4d764213504089251c6bc0726610bcf5e62fc2c1cbd7ee3aebe2d43';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT LCASE(GROUP_CONCAT(HEX(v1^v2) SEPARATOR '')) HashResult FROM
    -> (SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
    -> FROM (SELECT z+z-1 x FROM
    -> (SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
    ->  UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
    ->  UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ->  UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
    ->  UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
    ->  UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
    ->  UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
    ->  UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
    -> ) AAA) AA) A;
+----------------------------------------------------------------+
| HashResult                                                     |
+----------------------------------------------------------------+
| 838559e2bd662dc116882c4d326a967189b4da9545e98511419879b6f457f1 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

CAVEAT : Sorry, it's late and I was not in the mood to do a Stored Procedure !!!

If you are interested why this works, this query let's look at the details:

SELECT *,LCASE(HEX(v1^v2)) v3 FROM
(SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
FROM (SELECT z+z-1 x FROM
(SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
 UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
 UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
 UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
 UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
 UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
 UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
 UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
) AAA) AA) A;

Here are the details:

mysql> SELECT *,LCASE(HEX(v1^v2)) v3 FROM
    -> (SELECT x,CONV(SUBSTR(@h1,x,2),16,10) v1,CONV(SUBSTR(@h2,x,2),16,10) v2
    -> FROM (SELECT z+z-1 x FROM
    -> (SELECT      1 z UNION SELECT  2 UNION SELECT  3 UNION SELECT  4
    ->  UNION SELECT  5 UNION SELECT  6 UNION SELECT  7 UNION SELECT  8
    ->  UNION SELECT  9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12
    ->  UNION SELECT 13 UNION SELECT 14 UNION SELECT 15 UNION SELECT 16
    ->  UNION SELECT 17 UNION SELECT 18 UNION SELECT 19 UNION SELECT 20
    ->  UNION SELECT 21 UNION SELECT 22 UNION SELECT 23 UNION SELECT 24
    ->  UNION SELECT 25 UNION SELECT 26 UNION SELECT 27 UNION SELECT 28
    ->  UNION SELECT 29 UNION SELECT 30 UNION SELECT 31 UNION SELECT 32
    -> ) AAA) AA) A;
+----+------+------+------+
| x  | v1   | v2   | v3   |
+----+------+------+------+
|  1 | 67   | 192  | 83   |
|  3 | 156  | 25   | 85   |
|  5 | 54   | 111  | 59   |
|  7 | 103  | 133  | e2   |
|  9 | 73   | 244  | bd   |
| 11 | 177  | 215  | 66   |
| 13 | 73   | 100  | 2d   |
| 15 | 224  | 33   | c1   |
| 17 | 35   | 53   | 16   |
| 19 | 140  | 4    | 88   |
| 21 | 36   | 8    | 2c   |
| 23 | 150  | 146  | 4    |
| 25 | 130  | 81   | d3   |
| 27 | 224  | 198  | 26   |
| 29 | 21   | 188  | a9   |
| 31 | 96   | 7    | 67   |
| 33 | 62   | 38   | 18   |
| 35 | 250  | 97   | 9b   |
| 37 | 70   | 11   | 4d   |
| 39 | 102  | 207  | a9   |
| 41 | 10   | 94   | 54   |
| 43 | 60   | 98   | 5e   |
| 45 | 100  | 252  | 98   |
| 47 | 125  | 44   | 51   |
| 49 | 8    | 28   | 14   |
| 51 | 188  | 189  | 1    |
| 53 | 230  | 126  | 98   |
| 55 | 154  | 227  | 79   |
| 57 | 24   | 174  | b6   |
| 59 | 74   | 190  | f4   |
| 61 | 122  | 45   | 57   |
| 63 | 178  | 67   | f1   |
+----+------+------+------+
32 rows in set (0.00 sec)

mysql>


Assuming these are not contained databases, you need to worry about the logins (server level), not the users (database level). Contained users will be transferred automatically without difficulty.

Manually creating a login with the same name on the other server is not good enough - the SID will not match.

Robert Davis has written a post that describes how to generate the login scripts with the right SID and the right password hash. This is what you need to run on each of the replicas:

Transferring Logins to a Database Mirror

(Also see the comment thread on this answer.)

In order to just script all of the logins on the local server, change the stored procedure by commenting out this section (and the End that goes with it):

--If Not Exists (Select 1 From sys.server_principals
--          Where name = @LoginName)
--  Begin

(There are other, similar IF NOT EXISTS sections you'll want to comment out in order to get roles and permissions.)

Then call the stored procedure with hard-coded @@SERVERNAME and @debug = 1. This will print out CREATE LOGIN commands with the password hash and you can pick the ones you need and run those commands on the replicas.

Jonathan Kehayias wrote an SSMS add-in that handles all of the uncontained objects (don't forget about things like jobs), but I don't know if it has been maintained for newer versions of SSMS:

Synchronize Availability Group Logins and Jobs


As explained by How do I create a user-defined aggregate function? there is no such aggregate function (also see here).

However you can emulate your product by:

log(product(x) ) = sum(log(x))

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

Beware, there could be rounding errors. If this is all integers, do ROUND(...) around the EXP.


SQLServerLogMgr::LogWriter: Operating system error 1117

That being said, how should the sql server dba handle this error aside from reporting it to the storage team and checking for database corruption?

There really isn't anything you can do from the database side. SQL Server is a victim of the underlying hardware and virtualization (if any) having issues. The underlying issue (driver, hardware, config, etc.) needs to be fixed. Note that if you're in a virtualized environment it could be a software layer in between or an issue with the host/guest config, etc., and not a physical hardware or storage issue.

Realistically, removing all the filter drivers and associated software, stopping and in-between layers by removing them and putting this on physical (if virtual) and/or changing storage solutions (for example, using local instead of remote/SAN) can help aid in troubleshooting the issue. Updating drivers (for example multipathing, device, firmware, etc.) could also be helpful but not something I would charge a DBA to do but a datacenter or systems admin.

  I am wondering if there is any more information I may be able to gather from the sql server side which may help in their investigation?

Not really. Underneath we're calling read and write API's through Windows. The return code from the API call through Windows is this Windows error code which we're bubbling up so that the admin of SQL Server knows why SQL Server is having issues.

If anything, since it's a single volume they should be able to isolate that on the backend and enable infrastructure tracing. If this is a physical machine this would be from the HBA/scsi controller and below through the hardware. If it's virtual, then from the Host through the same layers.

Sadly, this is easier to say than do and most places are ill equipped to actually investigate these style of issue - especially when the environment is virutalized.

  Final Thoughts

What do the system event logs say? Are there further NTFS or other corruption issues coming up? Are devices being reset? The system event log should be dissected with an extremely fine toothed comb to see if there are a series of events or items that seem to lead up to this or if it's spontaneous. Additionally I've found these events to generally cluster around certain items such as times of high use on a specific controller or overhwelmed SANs.


Is there a way to recover this database (MySQL)

It might not be a lost cause, if you can figure out which files are which, but you'll most likely have to do that manually.  Presumably your 8.3 format preserved the extensions of the files, which should make things somewhat easier.

I would start with a new installation of the same version of MySQL, verify that it works, and then stop it.

References to the "data" directory below would be whatever SHOW VARIABLES LIKE 'datadir' returns.  Often, this is either /usr/local/mysql/data or /var/lib/mysql.

Identify which files in the backup were ibdata1, ib_logfile0, and ib_logfile1 in the "data" directory, and rename them to the proper names and copy them into place over the files from your new install.

Each schema should be a directory inside "data" in your backup.  Copy all of those directories into "data" from your new install (including, especially the "mysql" directory inside "data"), then rename them to the original names of the schemas.

Then, inside each of those directories, you'll find the table files, views, and triggers, which hopefully have their original extensions... rename those to their original names, also, where .frm is a table or a view, .myi, .myd, .ibd would be tables, .trn would be triggers.  Each of these needs its original filename, which would also be the names of the original table/view/trigger.

You can use the strings(1) unix command line utility to get some idea of what's in the files, to help you identify them.  The frm files, for example, will have table or view definitions, which you could use to identify which table you're dealing with, if the filenames are ambiguous.  Example:

sqlbot@dev:/usr/local/mysql/data/sakila# strings actor.frm
PRIMARY
idx_actor_last_name
InnoDB
)
        actor_id
first_name
last_name
last_update
actor_id
first_name
last_name
last_update

The same thing on the .ibd file will show the row data (assuming the tables weren't stored compressed on disk).  It isn't pretty or formatted, but it's readable.

You don't have to go through all of this work, though, to test whether this is going to be a viable route for you.  After you've fixed the ibdata1 and ib_log* files and the data/mysql files (compare with the names of the files in the reinstalled data/mysql directory) and fixed up the names of a couple of tables to test, you should be able to start the server and try to access only those tables that you've fixed the names on so far, and see if you can access the data.

If you don't try to access the tables you haven't yet fixed, the server should come up.   if not, the hostname.err log should contain hints as to what you might have overlooked.

Then shut it down again to do more renaming.

If this process proves successful, I would then suggest doing a mysqldump of all the databases and restore it elsewhere, since this resurrected instance wouldn't be something I'd want to put in production.


Range Extraction Algorithm for Muti-Part aka Composite Indexes in MySQL

Historically, MySQL has kept the relevant algorithms simple, even if less than optimal.  In particular, OR has been poorly optimized.  5.7 and 8.0 have new code, but I don't know if your examples have improved any.

EXPLAIN FORMAT=JSON sometimes gives clues.  Also, see the "Optimizer Trace".

If you have sample data, use the Handler counts to deduce whether they are well optimized:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts.

But, be aware that MySQL will often do a table scan instead of using an obvious index.  This is legitimate because of the cost of bouncing between the BTree for the secondary index and the BTree that contains the data.  Typically, if more than 20% of the index needs to be touched, it will prefer to do a table scan.  (The "20%" is no a hard-coded number, but is a gross simplification of the "cost-based" model that is used.)

Search for mysql cost based optimization.  There have been a few articles written on the topic.


How to express a "some of which are" and "not all are" relationship with enhanced entity-relationship diagrams?

You can represent a 1..* relationship

Notation is quite standard but it may vary a little depending on your EER tool... Since I work with Oracle, I'm used to Oracle Data Modeler notation...

Check the picture... and good luck!


Ok, thanks this answer I figured out that I needed to put the config WITHIN [mysqld] block in my.cnf to take effect.

My mistake was that I just had appended the commands at the end of my.cnf file.

Then at mysql restart I encounterd

InnoDB: Error: log file ./ib_logfile0 is of different size 0 5242880 bytes

To resolve this error, I needed to deleted log

  rm -f /var/lib/mysql/ib_logfile[01]

and restart as Rolando told here.


How to update one table based on another table's values on the fly?

UPDATE ips INNER JOIN country
    ON ips.iso = country.iso
SET ips.countryid = country.countryid

Using MySQL update multiple table syntax:

14.2.11 UPDATE Syntax

Note that you have two different lengths and data types on your iso columns.  There are, in fact, two separate sets of ISO codes, 2-letter and 3-letter, so you may not in reality be able to join these columns:

ISO 3166-1

The join condition USING (iso) instead of ON ips.iso = country.iso works too.


MySQL tables/databases into different disks

I'm not aware of a specific term for this practice but you can specify different data and/or index dirs using the create table syntax.

This only works for MyISAM tables and your OS must support symlinks.

In my practice though, we generally just segment sets databases into their own LV. These are generally a segment per functional application.  Having a single logical volume with all the tables actually in there it's easier to keep track of an manage disk space for each DB.

Those LVs though, are generally in a RAID 10 config to help balance IO.


How to upgrade SQL Server 2008 to 2012 with table partition?

If you're talking about partitioning that utilizes partition schemes and partition functions in Sql Server 2008, you're obviously using Enterprise Edition.  That functionality has been supported in all Enterprise releases since its inception in 2005.

As of Sql server 2016 SP1, that functionality is supported in ALL editions.

Generally speaking, anytime you are preparing to upgrade from one version of Sql Server to another (2008 -> 2012, etc.), it's prudent to run the Upgrade Advisor tool for the release you are targeting.

  Upgrade Advisor analyzes installed components from earlier versions of
  SQL Server, and then generates a report that identifies issues to fix
  either before or after you upgrade.


how to setup mongodb replica on cloud server?

If you try to set up replica set of two (or more) nodes at one physical server... You must use IP-addresses and not host names. So 127.0.0.1:27017, 127.0.0.1:27019


What's a pathological case where a bitmap filter would not allow the PROBE(Field, IN-ROW) semijoin reduction optimization?

I figured out the answer to my question - sort of.

When you specify MAXDOP(8), the optimized bitmap filter optimization will create 8 hash buckets.  The exact spread of integers across those hash buckets is probably Pearson hashing.  I am curious if anyone in the comments knows the exact hashing technique, though.

Quoting: https://sqlserverfast.com/blog/hugo/2018/05/plansplaining-part-5-bitmaps/

  Simple example: Let’s say that only three stores in our database have less than 30 employees. These stores have StoreKey 17, 38, and 82. The first row the Bitmap operator receives is for StoreKey 17; it hashes the number 17 and the result is 29, so bit 29 in the bitmap is set to one. It then reads the next row, for StoreKey 38; hashes the number 38 which results in 15; and sets bit 15 to one. The third row it reads is for StoreKey 82; due to a hash collision this number also hashes to 29 so it will once more set bit 29 to one (effectively not changing anything). There are no more rows so the end result will be a bitmap with bits 15 and 29 set to one and all other bits set to zero.


what you read and did is correct ?

Suppose, you have no index on user table,then

CREATE INDEX ix_bla on USERS (Reputation)
INCLUDE (Id, DisplayName)
WHERE Reputation > 400000;

This index is ok.

But this was not the point @Erik wanted to demonstrate.

According to that blog,suppose you already have any such index like,

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )

And you are require to create filtered index on Reputation then just be mere mentioning it in Include will do the job and save from creating expensive index.

so he dis this,

CREATE UNIQUE NONCLUSTERED INDEX Users_400k_Club
 ON dbo.Users ( DisplayName, Id )
 INCLUDE ( Reputation )
 WHERE Reputation > 400000
 WITH ( DROP_EXISTING = ON );

Filtered Indexes: Just Add Includes This is the new point about Filtered Index that he wanted to show.


Trying to join 2 tables to get a count of orders and grouped by email

You're kind of close by using a LEFT JOIN but you should start with your Customer table like this:
SELECT Customer.[Customer Email], COUNT(Orders.[Order ID]) AS OrderCount,
FROM Customer,
LEFT JOIN Orders
    ON Customer.[Customer ID] = Orders.[Customer ID]
GROUP BY Customer.[Customer Email]

Note this assumes the Customer Email field is unique in the Customer table, otherwise if two Customers can share the same email, then you should group on Customer ID instead. Also you should avoid spaces in your columns names, it's best practice to not use special characters.

Additional note, an edit suggestion was made to use COUNT(DISTINCT Orders.[Order ID]) instead, on the query I suggested, in case Order ID was not unique in the Orders table. While it's a good thought, it's also an assumption (and likely not the case in this context). Using extra operators like DISTINCT (when not needed) could lead to alternative execution plans that are potentially less performant (e.g. DISTINCT will likely result in an additional sort operation to be performed). Again I think it's a good thought, but I don't like to make answers on assumptions when there are other potential consequences, so I think the query I proposed is sufficient unless OP provides any additional information that states otherwise.


Host Name Resolution in mysql.db and mysql.user tables

If you want to grant access to the app databases to the same users, do this:
GRANT ALL PRIVILEGES ON app1.* TO 'user'@'db.domain.com';
GRANT ALL PRIVILEGES ON app2.* TO 'user'@'db.domain.com';
GRANT ALL PRIVILEGES ON app3.* TO 'user'@'db.domain.com';

If you want to replace 'lb.domain.com' with 'db.domain.com' do this:
UPDATE mysql.user SET host='db.domain.com'
WHERE user='user' AND host='lb.domain.com';
UPDATE mysql.db   SET host='db.domain.com'
WHERE user='user' AND host='lb.domain.com' AND db in ('app1','app2','app3');
FLUSH PRIVILEGES;

Give it a Try !!!
UPDATE 2011-10-26 12:43 EDT
Perhaps you may want to try masking the domain as follows:
GRANT ALL PRIVILEGES ON app1.* TO 'user'@'%.domain.com';
GRANT ALL PRIVILEGES ON app2.* TO 'user'@'%.domain.com';
GRANT ALL PRIVILEGES ON app3.* TO 'user'@'%.domain.com';

or replacing the domain:
UPDATE mysql.user SET host='%.domain.com'
WHERE user='user' AND host='lb.domain.com';
UPDATE mysql.db   SET host='%.domain.com'
WHERE user='user' AND host='lb.domain.com' AND db in ('app1','app2','app3');
FLUSH PRIVILEGES;

That way, any authentication of user from the domain.com domain would be acceptable.
UPDATE 2011-10-26 18:05 EDT
Personally, I hate using DNS names in mysql.user and mysql.db
You can actually get mysqld to bypass having to use DNS as follows
First replace all DNS names with hard IP addresses. Also, replace domain names with IP netblocks (instead of *.domain.com using 10.20.30.%)
Then, add the following to /etc/my.cnf and restart mysql
[mysqld]
skip-name-resolve
skip-host-cache

DNS resolution should, then, become a thing of the past.


text column compares equal to where clause but does not select matching row

A corrupted index would be the prime suspect here. Test with:
SELECT * FROM names WHERE name || '' = 'Сергей Иванович МЕНЯЙЛО';

The expression name || '' cannot use any indexes, so you get a sequential scan. If that query finds your entry, you have your diagnosis: corrupted index. Probably the one on just (name), but since multiple indexes qualify, recheck with EXPLAIN. (It's probably not the PK as that one still throws a unique violation in your test, but that one may be corrupted, too ...)
There is a note in the release notes for Postgres 11.11 in particular:

... see the second changelog item below, which describes cases in which reindexing indexes after the upgrade may be advisable.

Or maybe locales have been updated in your underlying OS? Same fix: reindex.
There are other reasons for index corruption, but the only other common one is hardware issues. That should trigger more drastic measures immediately, starting with a backup.
Recreate affected indexes. You can use REINDEX:
REINDEX INDEX ix_names_name;

If you need to allow concurrent access to the table use the non-blocking (but slower) CONCURRENTLY:
REINDEX INDEX ix_names_name CONCURRENTLY;

If there is reason to believe the problem might be systemic, recreate all indexes on the table:
REINDEX TABLE names;

Or the whole the database:
REINDEX DATABASE name_of_current_database;

In case of a bigger cleanup, I would advise a maintenance window without concurrent access. And plenty of maintenance_work_mem.
Aside, you have these indexes for the two columns name and processed_name:
"names_pkey" PRIMARY KEY, btree (name, processed_name)
"ix_names_name" btree (name)
"ix_names_processed_name" btree (processed_name)

The PK index on (name, processed_name) can be used for everything that the additional index on just (name) can be used. That additional index only might be useful if processed_name is a rather large column - in which case I would consider a more efficient PK to begin with. See:

Is a composite index also good for queries on the first field?


How to identify row change timestamp columns in Db2 Table

In Db2 for z/OS this information is in the DEFAULT column of the SYSIBM.SYSCOLUMNS catalog table. In your case the value you'll be looking for is 'E'.


PostgreSQL Master server act as a slave for 2nd DB on new server

After a lot more digging I now know a couple of things:

A PostgreSQL server can't be a master of one DB and a slave for a second DB. In order to have a master DB and a slave DB on the same physical hardware with PostgreSQL I need to run two separate PostgreSQL services/clusters.
Updates are actually more expensive than Inserts because Updates have to seek out the row first. Also always try to append your Insert(or use a clustered index if necessary).

Thanks


MSSQL - find immediate past iteration of field

If you're using SQL Server 2012 or later you can use LAG, although you'll need to handle the lack of normalization first:
SELECT
  SKU
 ,CASE
    WHEN SKUPrefix <> LAG(SKUPrefix,1,'') OVER (PARTITION BY 1 ORDER BY SKU) THEN ''
    ELSE LAG(SKU) OVER (PARTITION BY 1 ORDER BY SKU)
  END AS PriorSKU
FROM
  (
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
  FROM
    <Your Table>
  ) SKUFix

One might as "Why not partition by SKUPrefix and avoid the CASE statement and the answer is if a suitable index of the table (clustered or otherwise) starts with SKU then the query will be able to execute without sorting the data.
Prior to 2012 things are... uglier:
WITH SKUTemp AS
(
  SELECT
    SKU
   ,CASE
      WHEN CHARINDEX('(',SKU) = 0 THEN SKU
      ELSE SUBSTRING(SKU,1,CHARINDEX('(',SKU)-1)
    END AS SKUPrefix
   ,ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY SKU) AS SKURank
  FROM
    <Your Table>
)
SELECT
  SKU.SKU
 ,PriorSKU.SKU AS PriorSKU
FROM
  SKUTemp SKU
LEFT JOIN
  SKUTemp PriorSKU
    ON PriorSKU.SKUPrefix = SKU.SKUPrefix
        AND PriorSKU.SKURank = SKU.SKURank - 1

DDL to test:
CREATE TABLE #SKUTest
(
  SKU   CHAR(8)   NOT NULL
)
GO

INSERT INTO #SKUTest
VALUES
  ('ABCD')
 ,('ABCD(B)')
 ,('ABCD(E)')
 ,('ABDX')
 ,('ABDX(B)')
 ,('ABDX(F)')
 ,('BCDF(C)')
 ,('BCDF(P)')
 ,('BCDX')
 ,('BCDX(B)')
 ,('BCDX(G)')
GO

Results:
SKU         | PriorSKU
ABCD        | NULL
ABCD(B)     | ABCD
ABCD(E)     | ABCD(B)
ABDX        | NULL
ABDX(B)     | ABDX
ABDX(F)     | ABDX(B)
BCDF(C)     | NULL
BCDF(P)     | BCDF(C)
BCDX        | NULL
BCDX(B)     | BCDX
BCDX(G)     | BCDX(B)


Getting "sqlcmd sqlcmd: command not found" in Linux?

There are two methods of resolving this.

User-specific method advocated in the manual: add the directory to the environmental variable PATH.
System-wide method: symlink the executable to a directory already in the path.

User-specific Method
You can use the tutorial's recommended method. The tutorial states the following:

Optional: Add /opt/mssql-tools/bin/ to your PATH environment variable in a bash shell.
To make sqlcmd/bcp accessible from the bash shell for login sessions, modify your PATH in the ~/.bash_profile file with the following command:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile

To make sqlcmd/bcp accessible from the bash shell for interactive/non-login sessions, modify the PATH in the ~/.bashrc file with the following command:
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

This works, but it only affects that user's sessions, and only if they use bash.
System-wide Method
Link the MS SQL tools into a location accessible to all users:
sudo ln -s /opt/mssql-tools/bin/* /usr/local/bin/

This could be in their postinst script, which would make sense for installing an optional package.
Both methods are compliant with the File system Hierarchy Standard, which states:

Programs to be invoked by users must be located in the directory /opt/<package>/bin


Applying indexes on columns used in join condition

B-tree indexes provide three main benefits:

Seek to a specific value or range of values
Return rows in index order
Smaller row size than the base table (clustered index or heap)

Without useful indexes, the optimizer's main join strategy is hash join, where the join keys from the smaller table are used to build a hash table, then join keys from the larger table are used to probe into that hash table for matches. Hash join requires memory to hold the complete hash table.
With useful indexes, the optimizer may also consider indexed nested loops join or merge join strategies:
Indexed nested loops join
This physical join strategy primarily takes advantage of benefit #1 above. It reads from the smaller table then seeks into an index on the larger table on the join keys. For equi-join, index key order is not significant. Seeking on (col1, col2) is possible whether the index keys are (col1, col2) or (col2, col1).
For your example, an index on (col1, col2) or (col2, col1) for either table would enable an indexed nested loops join strategy. Ideally, the index would be on the larger table.
Merge join (without explicit sorts)
This physical join primarily uses benefit #2 above (index order). The indexes must include all the join columns, in the same key order on both tables. A merge join on (col1, col2) can use indexes on (col1, col2) or (col2, col1), but the key order must be the same for both tables.
Merge join is most efficient when at least one of the inputs is guaranteed unique on the join keys.
For your example, indexes on (col1, col2) or (col2, col1) on both tables would be required. The indexes would need to have the same key order on both, and ideally the index would be unique on those columns for at least one table.

db<>fiddle demo

The textual order of ON clause predicates is not important. The following are exactly equivalent. Which you use is purely a matter of style:
-- I prefer this style
SELECT *
FROM dbo.table_a AS TA
JOIN dbo.table_b AS TB
    ON TB.col1 = TA.col1
    AND TB.col2 = TA.col2;

SELECT *
FROM dbo.table_a AS TA
JOIN dbo.table_b AS TB
    ON TA.col1 = TB.col1
    AND TA.col2 = TB.col2;

More generally, how indexes impact on physical join choice is only part of the equation. Indexes can be useful for other (non-join) predicates in the query, as well as other operations like aggregation, windowing, and final presentation order.
Balancing index usefulness for the whole query, and all queries in the workload, versus the cost of maintaining those indexes is a significant part of the art of database administration and tuning.


Is it necessary to archive master WAL files in order to implement streaming replication in postgresql-11?

Setting up the standby via a base backup is similar, and is not described in detail in both places.  One place refers you to the other.  That is all that is meant. You can have streaming without a WAL archive.


Retrieve multiple rows from table2 based on single row from table1 in SQL Server

The query you need is:

SELECT
    camp.Campiagn_Id,
    camp.Campiagn_Name,
    camp_list.Campign_Id,
    camp_list.EmalList
FROM
    [table1] AS camp
    LEFT JOIN [table2] AS camp_list ON (camp.Campiagn_Id = camp_list.Campiagn_Id);

This will show all campaigns with all emails lists for each campaign. It will show also campaign with no emails list.

You can add or remove fields in the select as you need.


Additional info on some rows in the table

It sounds like your categories are a case of the subtypes pattern.  Accordingly, I've added the tag subtypes to your question.  There are two design patterns that you may want to look into.

The first one is called "Single Table Inheritance".  This design involves collecting all the subtypes (or, if you prefer, subclasses) into one table, and leaving values missing (NULL) in places where they do not pertain.  Views can be used to project this table onto subclass virtual tables.  You generally need a "category" field to make the projections.  It's simple, but it doesn't address all the problems.

The second one is called "Class Table inheritance".  This design involves creating one table for the superclass and one table for each subclass.  Attributes are placed in the appropriate table.  Often, the PK for the subclass table is a copy of the PK for the superclass table.  This enforces the one-to-one nature of the IS-A relationship, and provides for easy joins. Views can be used to join each subclass table with the relevant subset of the superclass table.

Your solutions #2 and #1 are somewhat like these two design patterns.

If you want to look up more detail on the web,  I suggest searching for Martin Fowler's presentation of these two techniques.


Different execution plans and joins using synonym or table

Bug 18856999 - Wrong results (wrong cursor) with schema table masking public synonym (Doc ID 18856999.8)

There is a one-off patch for this with the same number for 12.1.0.2. The fix is in some later PSUs and starting with 12.2.0.1.

As usual, the above requires an active support subscription.


How do I synchronize rows in tables in remote databases?

If the GUID is the absolute truth about what is supposed to match, then the GUID must be the PRIMARY KEY.  Get rid of id.

If you want semi-automated "conflict resolution based on time", you want MySQL's NDB Cluster.


If you wish to compute statistical data a Relational Database is generally better than a non-relational as all the links for your statistics will be inherent in your table design. Millions of rows will require watching your performance and adjusting appropriately but is not in the danger zone architecturally speaking, and since your data is being inserted once a day that gives you a reliable window for any index rebuilds, table statistic updates, etc...

However, do not create millions of tables for each song, that is a very bad idea. You'll instead want to create a series of tables representing the relations between your Users and the Songs that you will pull the statistics from. Based on your examples I would just add a single linking table between Users and Tracks instead of your millions of tables, it should look something like this:

CREATE TABLE UserTracks
(
    userID INT
    ,trackID INT
    ,streamSource NVARCHAR(100)
    ,CONSTRAINT FK_UserTracks_Users FOREIGN KEY (userID) REFERENCES Users (userID)
    ,CONSTRAINT FK_UserTracks_Tracks FOREIGN KEY (trackID) REFERENCES Tracks (trackID)
)
GO
CREATE CLUSTERED INDEX CI_UserTracks ON dbo.UserTracks (userID,trackID)
GO

Since it's a pure joining table between your other two tables creating the Clustered Index on the join columns that would be present most of the time should help with performance. If for some statistics you only need to do half the join (for example Users per track or Tracks per User) you'll probably want to add two more indexes like the following:

GO
CREATE INDEX NCI_UserTracks_userID_streamSource ON dbo.UserTracks (userID) INCLUDE (streamSource)
GO
CREATE INDEX NCI_UserTracks_trackID_streamSource ON dbo.UserTracks (trackID) INCLUDE (streamSource)
GO

Those should cover most of your potential performance problems right off the bat. Remember, clean INNER JOINs are your friend, make your structures do most of the work for you, avoid trying to piece the data together yourself via complex dynamic queries.


Order by taking less time than max() in oracle


The majority of the unmatched objects will be Undo.

You can join v$bh with DBA_ROLLBACK_SEGS to identify the Undo segments involved.

SELECT segment_name, owner, tablespace_name, class#, count(class#)
FROM v$bh bh, dba_rollback_segs drs
WHERE NOT EXISTS (SELECT 1 FROM sys.obj$ o2 WHERE o2.dataobj# = bh.objd)
AND drs.segment_id = class#
GROUP BY segment_name, owner, tablespace_name, class#
HAVING count(class#) > 1;


Is our use case suitable for a NoSQL Database solution?

I see your interest in JSON objects.

  Our database is not very large (generally under 2GB and growing
  slowly) and the entries could actually very well be broken down to
  simple json objects, which made us look at a NoSQL solution.

MongoDB is better suited for huge databases, in your case, I suggest  PostgreSQL.

You can use JSON types in PostgreSQL: https://www.postgresql.org/docs/current/static/datatype-json.html

Should be easy enough to migrate your app to PostgreSQL, because it's SQL all the way, and I doubt that you will find that stupid join limit.

  Either way, is this data / layout / query strategy something that
  resonates well with a NoSQL DB ?

Performance has costs when using NoSQL, many times, you will have to modify your code, your database schema, your server configuration. Are you prepared to pay the price?

Answering your question, I try to avoid MongoDB aggregation, preferring to manually aggregate on the spot (my source code is my SQL), but I do it for small ranges. If doing the entire table/collection, would be better to use Map-Reduce or Aggregation or custom code? It depends, who is doing the job faster?

Most of the time, people will advise to denormalize your data, but doing this can cause another range of issues. Again, pay the price.


SQL performance issues with remote query across linked server

You can turn on trace flag 7300 which might give you a more detailed error message

How many rows does a representative query return? How fast/reliable is the network connection between the two servers?

It's possible that a large dataset is taking too long to transfer (on top of the actual query time).  You could raise the timeout value.

You can try to reconfigure the timeout setting as follows:

Set the remote login timeout to 300 seconds:

sp_configure 'remote login timeout', 300
go
reconfigure with override
go

Set the remote query timeout to 0 (infinite wait):

sp_configure 'remote query timeout', 0
go
reconfigure with override
go

Update: SQL Server 2012 SP1 onwards: users with SELECT permission will be able to access DBCC SHOW_STATISTICS which will improve read only performance on linked servers. Ref: https://msdn.microsoft.com/en-us/library/ms174384(v=sql.110).aspx

Update:  You are correct in saying it's not the size of data or connection speed.  It rang a bell in my foggy memory and I remembered where I had seen it: Slow in the Application, Fast in SSMS?(An Issue With Linked Servers). It's not parameter sniffing, it is the statistics themselves that are missing (due to permissions), causing a bad query plan to be used:

  You can see that the estimates are different. When I ran as sysadmin,
  the estimate was 1 row, which is a correct number, since there are no
  Orders in Northwind where the order ID exceeds 20000. But when I ran
  as a plain user, the estimate was 249 rows. We recognize this
  particular number as 30 % of 830 orders, or the estimate for an
  inequality operation when the optimizer has no information.
  Previously, this was due to an unknown variable value, but in this
  case there is no variable that can be unknown. No, it is the
  statistics themselves that are missing.

  As long as a query accesses only tables in the local server, the
  optimizer can always access the statistics for all tables in the
  query; there are no extra permission checks. But this is different
  with tables on a linked server. When SQL Server accesses a linked
  server, there is no secret protocol that is only used for inter-server
  communication. No, instead SQL Server uses the standard OLE DB
  interface for linked servers, be other SQL Server instances, Oracle,
  text files or your home-brewed data source, and connects just like any
  other user. Exactly how statistics is retrieved depends on the data
  source and the OLE DB provider in question. In this case, the provider
  is SQL Server Native Client which retrieves the statistics in two
  steps. (You can see this by running Profiler against the remote
  server). First the provider runs the procedure
  sp_table_statistics2_rowset which returns information about which
  column statistics there are, as well as their cardinality and their
  density information. In the second step, the provider runs DBCC
  SHOW_STATISTICS, a command that returns the full distribution
  statistics. (We will look closer at this command later in this
  article.) Here is the catch: to run DBCC SHOW_STATISTICS, you must be
  member of the server role sysadmin or any of the database roles
  db_owner or db_ddladmin.

  And this is why I got different results. When running as sysadmin I
  got the full distribution statistics which indicated that there are no
  rows with order ID > 20000, and the estimate was one row. (Recall that
  the optimizer never assumes zero rows from statistics.) But when
  running as the plain user, DBCC SHOW_STATISTICS failed with a
  permission error. This error was not propagated, but instead the
  optimizer accepted that there were no statistics and used default
  assumptions. Since it did get cardinality information, it learnt that
  the remote table has 830 rows, whence the estimate of 249 rows.

  Whenever you encounter a performance problem where a query that
  includes access to a linked server is slow in the application, but it
  runs fast when you test it from SSMS, you should always investigate if
  insufficient permissions on the remote database could be the cause.
  (Keep in mind that the access to the linked server may not be overt in
  the query, but could be hidden in a view.) If you determine that
  permissions on the remote database is the problem, what actions could
  you take?

  You can add the users to the role db_ddladmin, but since this gives them right to add and drop tables, this is not recommendable.
  By default, when a users connect to a remote server they connect as themselves, but you can set up a login mapping with
  sp_addlinkedsrvlogin, so that users map to a proxy account that has
  membership in db_ddladmin. Note that this proxy account must be an SQL
  login, so this is not an option if the remote server does not have SQL
  authentication enabled. This solution too is somewhat dubious from a
  security perspective, although its better the previous suggestion.
  In some cases you can rewrite the query with OPENQUERY to force evaluation on the remote server. This can be particularly useful, if
  the query includes several remote tables. (But it can also backfire,
  because the optimizer now gets even less statistics information from
  the remote server.)
  You could of course use the full battery of hints and plan guides to get the plan you want.
  Finally, you should ask yourself whether that linked-server access is needed. Maybe the databases could be on the same server? Could data
  be replicated? Some other solution?


SQL TFS Visual Studio Solution: One Database or Multiple Databases

A solution is meant to cover multiple like projects together. So for example, I have a "Data Warehouse Solution" that has all of my SSDTs related to our data warehouse. I then have a different solution for my "RDBMS Project," with more projects inside of that. It is a logic grouping for user ease.

There is nothing stopping you from having one master solution with tons of projects, or one project per solution.

EXCEPT

I found the need to have an SSDT for the database server itself, if you are keeping track of server objects created at the server level. For example, security objects. Then I found that you couldn't create a reference to the database server. You had to create a SSDT project just for it.


How to reduce table partition timing for existing table in SQL Server

ALTER PARTITION SCHEME PS_Tbl_3rdParatition  NEXT USED
  [SWPPartitionFG2]

  ALTER PARTITION FUNCTION PF_Tbl_3rdParatition()  SPLIT RANGE (1000)

  ALTER INDEX [CidxTbl_3rdParatition] ON [dbo].[Tbl_3rdParatition ]
  REBUILD  WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON, MAXDOP = 1)

Why are you rebuilding the clustered index after creating the new partition?

When you SPLIT a RANGE LEFT function, the partition containing the new boundary is split. The new partition on SWPPartitionFG2 is created to the left of the SWPPartitionFG1 partition. The new partition on SWPPartitionFG2 becomes partition 2 and the original SWPPartitionFG1 partition is renumbered as partition 3. Existing rows in the spilt partition that are less than or equal to the new boundary are moved into the new partition during the process. This is a very expensive operation, requiring about 4 times the logging of normal DML. That is why one should plan to split only empty partitions, or at least ensure no rows must be moved.

It would be best to use a RANGE RIGHT function here so that incremental boundaries are created to the right of the SPLIT partition. Create a new RANGE RIGHT partition function and scheme and rebuild indexes specifying the new scheme with the 'DROP_EXISTING = ON` option. I would expect this to take much less time than the current operation and, going forward, the SPLIT to create incremental partition boundaries will be a fast meta-data only operation.

CREATE PARTITION FUNCTION PF_Tbl_3rdParatition_Right(NUMERIC(18,0)) AS RANGE LEFT
FOR VALUES(500,1000);

CREATE PARTITION SCHEME PS_Tbl_3rdParatition_Right AS
    PARTITION PF_Tbl_3rdParatition_Right TO ([PRIMARY],[SWPPartitionFG1],[SWPPartitionFG2]);

CREATE UNIQUE CLUSTERED INDEX CidxTbl_3rdParatition ON dbo.Tbl_3rdParatition(PK)
WITH(DROP_EXISTING = ON)
ON PS_Tbl_3rdParatition_Right(PK);

See Table Partitioning Best Practices for more information.


Using Oracle12C Client with Oracle11.g database

The document that has the client/server compatibility matrix is 207303.1 Client / Server / Interoperability Support Matrix For Different Oracle Versions. (Oracle support account required)

Oracle client version 12c is compatible with Oracle server version 11.2 (and below), so no your error isn't likely to come from that.

Check that the listener is indeed started on the database server, check that the database is up, and check that the database is registered with the listener.


How to append datetime in filename in postgresql

If using psql via bash command line, you can simply do:

psql -c "COPY (select  query) To '/tmp/test_`date +"%Y-%m-%d_%H%M%S"`.csv' DELIMITER ',' CSV HEADER;"


MySQL: InnoDB data dictionary has unknown flags

you probably have multi different version binary of mysql installed in your server, make sure you are in the correct version path...


That's what GROUP BY is used for. Get one row (per group). In this case, it will show all distinct user_id values and for the rest of the columns, you can (have to) use aggregate functions like MIN(), MAX(), AVG(), SUM() as you will have more than one values per group and only one can be shown.

SELECT
    user_id
  , MIN(comment) AS comment  -- it will show the first in alphabetical order
                             -- you could also use MAX()
FROM
    tableX
GROUP BY
    user_id ;

MySQL allows also the following unorthodox solution, that will return one (more or less random) comment per user:

SELECT
    user_id
  , comment
FROM
    tableX
GROUP BY
    user_id ;

This last query will not work but raise an error if the (stricter) ONLY_FULL_GROUP_BY mode is enabled. In the recently released 5.7 version, this mode is the default and a new function, ANY_VALUE(), is provided. For more details, see the MySQL Handling of GROUP BY page. The query can be written now:

SELECT
    user_id
  , ANY_VALUE(comment) AS comment
FROM
    tableX
GROUP BY
    user_id ;

Note that with either the "unorthodox" version or using the recent ANY_VALUE() function, if we add more columns in the SELECT list, their values is not guaranteed to be from the same row, just from a row in the same group. The way they are selected is not exactly random, depends on the execution plan and the indexes used.


I don't see any better hope of answering this, but you can use Neo4J from Erlang...  see https://github.com/nerlo/nerlo/wiki/howto-use-neo4j-from-erlang


Cumulate count between date with 0 when there are records for a given day

You write in your question:

I think I'd need GENERATE_SERIES, but I don't know how to do it.

Indeed you do - and you can as follows (all the code below is also given in the fiddle here):
CREATE TABLE cal_tab (cal_date) AS
(
  SELECT  GENERATE_SERIES
  (
    '2021-04-01'::DATE,
    '2021-04-07'::DATE,
    '1 DAY'
  )
);

Just to check:
SELECT cal_date::DATE FROM cal_tab;

Result:
cal_date
2021-04-01
2021-04-02
2021-04-03
...
... snipped for brevity - you determine how many rows there are with the
... second parameter to GENERATE_SERIES
...

Then, I use your data:
CREATE TABLE test
(
    id         INT4 NOT NULL GENERATED ALWAYS AS IDENTITY,
    data       INT4,
    created_at DATE NOT NULL DEFAULT NOW()
);

Populate (slightly modified from the question - so as not to have the id the same as the provided data (i.e. 1...6), - instead, I explicitly inserted hundreds - improves legibility):
INSERT INTO test (data, created_at) VALUES (100, '2021-04-01');
INSERT INTO test (data, created_at) VALUES (100, '2021-04-01');

INSERT INTO test (data, created_at) VALUES (200, '2021-04-02');
INSERT INTO test (data, created_at) VALUES (300, '2021-04-03');
                                                               -- GAP for 4th of April
INSERT INTO test (data, created_at) VALUES (500, '2021-04-05');
                                                               -- GAP for 6th of April
INSERT INTO test (data, created_at) VALUES (600, '2021-04-07');

And then run the following SQL:
SELECT
  DISTINCT  -- try with and without DISTINCT
  ct.cal_date::DATE,
  COALESCE(t.data, 0) AS data, -- t.created_at::DATE,

  COUNT(t.created_at) OVER (PARTITION BY t.created_at
                              ORDER BY ct.cal_date ASC) AS "Cnt/day",
  COUNT(t.created_at) OVER (ORDER BY ct.cal_date ASC) AS "Cum. cnt/day",

  SUM(t.data) OVER (ORDER BY ct.cal_date::DATE ASC) AS "Sum/day"
FROM
  cal_tab ct
LEFT OUTER JOIN test t
  ON ct.cal_date = t.created_at::DATE
ORDER BY ct.cal_date::DATE ASC;

Result:
  cal_date  data    Cnt/day Cum. cnt/day  Cum. sum/day
2021-04-01  100           2            2           200
2021-04-02  200           1            3           400
2021-04-03  300           1            4           700
2021-04-04    0           0            4           700
2021-04-05  500           1            5          1200
2021-04-06    0           0            5          1200
2021-04-07  600           1            6          1800

A result which, I believe covers all your requested elements.
In this case, you don't have to worry about the FRAME clause (i.e. the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) bit - FRAMEs are a kind of sub-partitioning method - but the long phrase above is actually the default - see here for a good introduction to window frames in PostgreSQL.
You could also (depending on your requirements) use a CTE (Common Table Expression) for your query if you don't wish to create a permanent calendar table (see my answer here for an example of this). (+1 for having made me think!).


The issue was a poorly written QueryPlan in response to a defined Trigger as per @Aaron Bertrand presumed. I rewrote the QueryPlan to return more than one row which was what was causing the rather misleading error message.


After running VBA, I lose exclusive access to the database

To make a long story short... Run Compact and Repair. I thought I had already attempted that. Perhaps I had before I read Scott's post. But after fixing that and then Running Compact and Repair, everything is working exactly the way it should be. I don't typically have to do that for the first time on a database until I've done a LOT more work, I'm guessing because this Database has close to twenty thousand entries, and most of the ones I work with have less then 1000, it makes a huge difference. Lesson learned. Always learning, always growing.


Nonclustered index storage on clustered columnstore

The "bookmark" is the columnstore index original locator (per "Pro SQL Server Internals" by Dmitri Korotkevitch). This is an 8-byte value, with the columnstore index's row_group_id in the first 4-bytes and an offset in the second 4-bytes.
If you use DBCC PAGE to look at the non-clustered index, the 8-byte columnstore index original locator appears in the "uniquifier" column of the DBCC PAGE output. This shows that a unique non-clustered index does not need to include the columnstore row locator, whereas a non-unique non-clustered index does.

The following code creates a columnstore-organized table with a unique and non-unique b-tree nonclustered index on the same column:

CREATE TABLE dbo.Heapish
(
    c1 bigint NOT NULL,
    c2 bigint NOT NULL,
    INDEX CCI_dbo_Heapish CLUSTERED COLUMNSTORE
);
GO
INSERT dbo.Heapish WITH (TABLOCKX)
    (c1, c2)
SELECT TOP (1024 * 1024 * 8)
    c1 = ROW_NUMBER() OVER
        (ORDER BY C1.[object_id], C1.column_id),
    c2 = ROW_NUMBER() OVER
        (ORDER BY C1.[object_id], C1.column_id)
FROM master.sys.columns AS C1
CROSS JOIN master.sys.columns AS C2
ORDER BY
    c1
OPTION (MAXDOP 1);
GO
CREATE UNIQUE NONCLUSTERED INDEX UNIQUE_c2 ON dbo.Heapish (c2) WITH (MAXDOP = 1);
CREATE NONCLUSTERED INDEX NONUNIQUE_c2 ON dbo.Heapish (c2) WITH (MAXDOP = 1);

We can see the size of the index row at different levels of the b-tree using sys.dm_db_index_physical_stats:

SELECT
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.record_count,
    DDIPS.min_record_size_in_bytes,
    DDIPS.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'dbo.Heapish', N'U'),
    INDEXPROPERTY(OBJECT_ID(N'dbo.Heapish', N'U'), N'UNIQUE_c2', 'IndexID'),
    NULL, 'DETAILED'
) AS DDIPS;

SELECT
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.record_count,
    DDIPS.min_record_size_in_bytes,
    DDIPS.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'dbo.Heapish', N'U'),
    INDEXPROPERTY(OBJECT_ID(N'dbo.Heapish', N'U'), N'NONUNIQUE_c2', 'IndexID'),
    NULL, 'DETAILED'
) AS DDIPS;

The output is:

Both structures have the same row size at the leaf level, but the nonunique nonclustered index is 12 bytes larger than the unique nonclustered index at the non-leaf levels due to the 8-byte columnstore locator, plus 4 bytes of overhead for the first variable-length column in a row (uniquifier is variable length).


Oracle Standby Database Techniques for faster application of redo logs

I am not very proficient with this topic but I think there are the following options, the first three of them need extra
licenses. You should to check if my recommendations contradict your DR requirements.

The problem of of the logical dataguard is that one SQL statement on the primary server can cause a lot of SQL statement
on the standby server.

Example: the statement

delete from mytable where entry_date <=to_date('2014-01-01','YYYY-MM-DD')

may cause the deletion of 1 000 000 rows in the primary database in a more or less efficient way. This statements causes
the execution of 1 000 000
statements on the standby side of the form

delete from mytable where id=:id

where id is the primary key field of mytable. This kind of deletion is the most imperformant way to delete rows.

If one uses s physical dataguard the situation is much better: the redo records transmitted to the standby contains the
change records that must be written to the datablock
and the standby applies these changes. This is a lot of less activity on the standby side.

You should run the dataguard in an asynchronous mode (maximum performance mode) so that the SQL-execution on the standby side
does not influence the performance on the primary site.
There is a parameter in sql_apply, PRESERVE_COMMIT_ORDER, which is TRUE by default and can be set to FALSE to simplify
log apply.

Buy additional licenses:

Use partitioning and truncate partitions instead of deleting rows.
If  if  is possible to organize your database in such a way then this is a very good solution because the truncation of a
partition does generate almost no redo information. On the standby side the truncate command will be executed in the same
way as on the primary side. An additional license is necessaary to use partitioning.
use physical standby with active datafguard.  Active dataguard allows running the database in read only mode while redos
are applied. a physical standby is more performant as a logical one as explained above.
An additional license is necessary to use active dataguard.
You can combine 1.1) and 1.2). This will be the most performant way.

create a solution using physical standby
If you don't want to bou additional licenses you can tinker around with your own solution.

mimic active dataguard: mix "open read only / apply redo" phases with your physical dataguard: snapshot database

apply the redo to your standby database
stop redo apply
open it as snapshot database
do your reporting
close your database
convert back to physical standby and continue with a)

you can improve solution 2.1): before step  "apply the redo to your standby database" that you have gathered during the reporting phase you
can make an incremental backup (with respect of the scn of the restore point) from the primary and apply this to the
standby site this may save you a lot of redo apply.

improve your sql apply solution
I assume that you have one table that is heavily affected by your batch job. I call it mytable.

before you start the batch process exclude mytable from the sql apply process. after your job has finished
reinstantiate the table and include it in sql apply again. This makes sense if the percentage of deleted rows in the
table is rather high.
exclude mytable from sql apply and replicate the data of mytable to the primary database  by other methods. This
replication method should handle the deletes of the batch job in a suitable way. One possibility maybe Advanced
Replication, especally procedureal replication. Another may be oracle Streams.


MySQL on RDS, data transfer from one instance to another as a production job

MySQL RDS allows you to do two options
OPTION #1
You set up a Read Slave. Then, run the reports from it. There is a tool in the RDS CLI called rds-create-db-instance-read-replica to create a Read Slave from a Running RDS instance. The following are done under the hood:

Snapshot Made
Snapshot Brought Up
CHANGE MASTER TO executed with proper binary log and position

OPTION #2
Take a live snapshot of Production. Then, open a new instance using that snapshot.
The tools for this are

rds-create-db-snapshot
rds-restore-db-instance-from-db-snapshot

CONCLUSION
The only difference between these options

the Read Slave is continuous
Snaphot brought up as a point-in-time Instance

In both cases, once you done, simply drop the new Instance (saves money) at your discretion.
If you combine the two ideas, you could load your PHP script's output into the point-time instance. Any needed data to retrieve from production could be read from the Read Slave to prevent further load issues with production.
WARNING
Bringing up instances can be time-consuming. So, plan to have the instance brought up early enough for report and import time.
This is more ranting and brainstorming than a concrete answer.


How to Determine the input and output values of stored procedures?

You should be able to do that using the system view INFORMATION_SCHEMA.PARAMETERS.
You'll have there what you need.

It "returns one row for each parameter of a user-defined function or stored procedure that can be accessed by the current user in the current database. For functions, this view also returns one row with return value information."

PS: if the answer to Martin's question is "columns in the result set", then ignore my answer, it is only for the parameters of the procedures, not for any result.


Is there a way to allow only DELETES from CASCADES only?

For that you can use Foreign keys (referential integrity constraints):

REFERENCES orders ON DELETE CASCADE,

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row.

See the manual
I explain it a bit better.
You have a parent table A with an id, and you have a second table B, which has a reference to the id in Table A with CASCADE DELETE.
As the check constraints don't support referencing other tables, you can use a  trigger with a function:
CREATE FUNCTION delete_tabentry () RETURNS trigger AS $$
BEGIN
  IF EXISTS(SELECT 1 FROM parent WHERE id= OLD.id ) THEN
    RAISE NOTICE 'Cannot delete order that is partially or completely done.';
  ELSE
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER before_delete_child
  BEFORE DELETE ON child
  FOR EACH ROW EXECUTE PROCEDURE delete_tabentry();

This would prevent deletion from child until its parent record is deleted.


Clustered Primary Key that is never used vs. Non-Clustered Primary Key on Multiple Columns

I'm working on a similar problem right now except more columns and millions of rows. We have a PK that isn't used in any queries. We ran a preliminary test where we changed it to a non-clustered PK and I found two columns that are used in where clauses and created a clustered index on those.

Many queries ran faster and out of more than two dozen indexes on the table we think we will be able to delete 25 of them.

In theory what were are looking to do is not best practices because the columns aren't unique that we're looking to use for the clustered index. But in practice it allows us to save a lot of space by making many non-clustered indexes unneeded and improve I/O by deleting these indexes.


psql variable holding text with multiple lines

To answer my own question, the \gset command should be used for multiline variable content,

select '''2
3''' x \gset


Stop rollback of anonymous DO code block?

You can commit inside the DO, as long as it is not already inside a transaction.

create table foo (z double precision);

do language PLPGSQL $$
declare i int;
begin
  for i in 1 .. 12 loop
    insert into foo values (sqrt(10-i));
    commit;
  end loop;
end;$$;

ERROR:  cannot take square root of a negative number
CONTEXT:  SQL statement "insert into foo values (sqrt(10-i))"
PL/pgSQL function inline_code_block line 1 at SQL statement

select count(*) from foo;

  10


SQL Server Express to SQL Server Standard upgrade?

Do I need to upgrade to SQL Standard Edition?
  Our website has been very slow and unreliable lately

Depends.

If your sites are still working then that implies that SQL server hasn't stopped accepting new data. I would expect exceptions along the lines of "Could not allocate space because the '<FG_NAME>' filegroup is full" if you were hitting the 10Gb limit already.

The 10Gb is per database not per server, so if your 38Gb is from several databases you may not have hit the limit yet. Also it doesn't count transaction log files so you might have massive log files (because your backup and other maintenance plans are not suitable for your recovery model for instance) that are responsible the majority of the space consumed.

  Is it expensive to upgrade?

This is not the right place for licensing/pricing information - such information changes regularly so any answer would quickly become obsolete and therefore incorrect. For current absolute prices consult your preferred software dealer(s) or the manufacturer directly. For a view on what is expensive and what isn't consult your accountant!

  Would the web hosting company be able to provide us with the license?

Depends. You'll have to ask them that. Some will, some won't. We don't even know what sort of hosting arrangement you have so we wouldn't be able to help even if we were the right people to ask.