How to design a table for storing news items?

A few things you will have to think about before starting:

How do you decide if a title is the same as a previous one?  Say there is an article 'A god table for storing news items'.  Your script discovers and stores it in your DB.  Later the editors of that news site notice the typo and correct the title as 'A good table for storing news items'.  What do you do in such a case?  (The same applies to the content.)
If you want to do something with your data after storing, you will need a unique identifier in the table.  Using this, you will be able to point to an item from other tables.
You may find useful to store the URL of the articles.  However, from time to time these can change just like titles do.
Short news are typically found in many places on the internet.  Both their title and contents are the same (like in the case of short news from news agencies) - are these the same or not?  If you store the URL, then these are different.  If not, these are the same.  What to do with these?
If you decide that you will store these from each site you process, than you will find useful to identify your source sites.  (Be careful, the URL will look like 'http://dba.stackexchange.com/questions/23568/how-to-design-a-good-table-for-real-time-news-extraction' and the site will be 'dba.stackexchange.com'.  Here the latter forms a part of the former, but this is not necessarily true for other sites.)  In this case, you will have to create another table where you list all the source sites.  This table will be referenced from your other table.

So far we have the following database structure:

Table "news_item"

+--------------+------------+--------------+---------------------+-----+-----------+
| news_item_id |   title    |   content    |     time_created    | URL | source_id |
+--------------+------------+--------------+---------------------+-----+-----------+
| 1            | First item | some content | 2012-09-03 22:53:14 | ... | 1         |
.                                                                                .
.                                                                                .
.                                                                                .
+--------------+------------+--------------+---------------------+-----+-----------+

Table "source"

+-----------+-----------------------+
| source_id | name                  |
+-----------+-----------------------+
| 1         | dba.stackexchange.com |
.                                   .
.                                   .
.                                   .
+-----------+-----------------------+

You can write your SQL script only after answering at least the above questions.


If you can't modify the schema per db2's answer, then try:

WHERE
       ColumnAA LIKE @VariableA + '%'
   AND LEN(@VariableA) >= 17          -- safety check

Note that if @VariableA is shorter than 17 characters, then you may match unwanted strings using just a LIKE.  For example, 'The quick brown fox jumped over the lazy dog.' is LIKE 'The%', but that doesn't satisfy the requirements of your original SUBSTRING query.

Aaron also brings up a good point that the DISTINCT may be the ultimate culprit.  Why do you have it in there?  Is it something that should be replaced with an appropriate unique index?


jdbc+mysql: "Deadlock found when trying to get lock; try restarting transaction"

Looking at the Stored Procedure, I see something rather unnatural.
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN

  DECLARE KeepPixels,DeleteLimit,MaxID INT;

  SET KeepPixels = 5;
  SET DeleteLimit = 100;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;

  DROP TABLE IF EXISTS pixel_window;
  CREATE TEMPORARY TABLE pixel_window
  (id INT NOT NULL PRIMARY KEY) ENGINE=MyISAM;

  SET @sqlstmt= CONCAT('INSERT INTO pixel_window ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);
  DELETE A.* FROM pixels A LEFT JOIN pixel_window B USING (id)
  WHERE A.type=GivenType AND B.id IS NULL;
  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;
  DROP TABLE IF EXISTS pixel_window;

END $$

DELIMITER ;

The thing I see that is unnatural is the DELETE ... JOIN query that is mixing an InnoDB table and a MyISAM table. Such JOINs tend to be rather clumsy to mysqld.
Let's make the pixel_window table. InnoDB. In fact, let's not make it a temporary table. Of course, the hard part is to create a unique pixel_window table for the session. The pixel_window temp table will be the prefix 'pixel_window_' with the Connection ID appended.
DELIMITER $$

DROP PROCEDURE IF EXISTS `adam_matan`.`AddPixel` $$
CREATE PROCEDURE `adam_matan`.`AddPixel`
(
    GivenType VARCHAR(20),
    GivenPixelData BLOB
)
TheStoredProcedure:BEGIN

  DECLARE KeepPixels,DeleteLimit,MaxID INT;

  SET KeepPixels = 5;
  SET DeleteLimit = 100;

  INSERT INTO pixels (type,pixel_data)
  VALUES (GivenType,GivenPixelData);
  SELECT MAX(ID) INTO MaxID FROM pixels;
  IF MOD(MaxID,DeleteLimit) > 0 THEN
      LEAVE TheStoredProcedure;
  END IF;

  SET @pwtable = CONCAT('pixel_window_',CONNECTION_ID());

  #
  # Create the pixel_window Table
  #
  SET @sqlstmt = CONCAT('CREATE TABLE ',pwtable,' (id INT NOT NULL PRIMARY KEY) ENGINE=InnoDB');
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  #
  # Load the pixel_window Table
  #
  SET @sqlstmt= CONCAT('INSERT INTO ',@pwtable,' ',
      'SELECT id FROM pixels WHERE type=''',GivenType,
      ''' ORDER BY id DESC LIMIT ',KeepPixels);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id; SELECT SLEEP(10);

  #
  # Use the pixel_window Table to perform DELETE...JOIN
  #
  SET @sqlstmt = CONCAT('DELETE A.* FROM pixels A LEFT JOIN ',
  @pwtable,' B USING (id) WHERE A.type=GivenType AND B.id IS NULL');
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

  SELECT * FROM pixels WHERE type=GivenType ORDER BY id;

  #
  # Drop the pixel_window Table
  #
  SET @sqlstmt = CONCAT('DROP TABLE IF EXISTS ',@pwtable);
  PREPARE st FROM @sqlstmt; EXECUTE st; DEALLOCATE PREPARE st;

END $$

DELIMITER ;

Give it a Try !!!
CAVEAT : Since I created this monster, I needed to slay it as well.


Separate indices for the field I want to get the MAX() and the ones in the WHERE clause

The index that will give you the most benefit is one on

fldB, fldC, fldA desc

or on

fldC, fldB, fldA desc

depending on your data. You'll need to check your data and keys to determine which column should go first.

The last column of the index is the column for which the maximum value is being computed. By including it in the index you will avoid table access. And by including the keyword desc it means that Oracle can use the index to calculate the maximum value -- it will be the first entry in the index for which the first two columns match.

In the ideal case, you will have many repeated values in the leading column of your index. In this case you might get some measurable improvement by using index key compression. In this case your create index statement will look something like

create index humongoustable_idx on humongoustable(fldB, fldC, fldA desc) compress 1;

Don't forget to refresh statistics on humongoustable after creating your new index!


How do I calculate a field based on multiple non-numeric records

the solution that I thought involves creating a calculated column on table2 called the_level which will give you the exact level depending on the ActivityCode.

the second step is on your query, where you join table1 and table2,
there you need to identify whether or not the item is
entered within the timeframe set by the query, and change the the_level accordingly.

the_level has been created as PERSISTED, you did not mention indexes and performance but you said billion hours, so you will need some indexes there.

this is an example of calculated column with case and not null.

I have, in the past, create calculated columns that reference a different table,
but in my environment the performance was not the best. Somehow it slowed writes, but I had an index on the calculated column. I am not stating this is a rule, but I would monitor it before doing it on a busy updated table.

    create table table2(

    [ActivityCode]  varchar(10) not null,
    [ActivityDate]  datetime,
    [ActivityOutcome] int,
    [ActivityEval?] char(3) not null default('Yes')
    )
    go

    -- add the facilityID, as a primary key at least for the exercise
    alter table table2
    add [FacilityID] int not null identity(1,1) primary key clustered
    go

    --insert the activity codes
    insert into table2 ([ActivityCode])
     select  'EVALA'
union all select  'EVALB'
union all select  'EVALC'
union all select  'EVALD'
union all select  'EVALE'
union all select  'EVALF'
union all select  'EVALG'
union all select  'EVALH'
union all select  'EVALI'
union all select  'EVALJ'
union all select  'NOEVALA'
union all select  'NOEVALB'
union all select  'NOEVALC'
union all select  'NOEVALD'
     go

     -- have a look at the table
     select * from table2

     -- test the calculation of the the_level column
     select [ActivityCode],
            the_level =
            CASE WHEN ActivityCode    LIKE '%EVALA%' OR
                      ActivityCode    LIKE '%EVALB%' OR
                      ActivityCode    LIKE '%EVALF%' OR
                      ActivityCode    LIKE '%EVALG%' OR
                      ActivityCode    LIKE '%EVALH%' OR
                      ActivityCode    LIKE '%EVALI%' OR
                      ActivityCode    LIKE '%EVALJ%'THEN 1
              ELSE
              CASE WHEN ActivityCode    LIKE '%EVALC%' OR
                        ActivityCode    LIKE '%EVALD%' OR
                        ActivityCode    LIKE '%EVALE%' THEN 2
                 ELSE 0
              END
            END
    FROM TABLE2

    ----------------------------------------------------
    -- ADDED the_level as A persisted column to table2
    ----------------------------------------------------
     alter table table2
    add  the_level AS ISNULL(
    CASE WHEN ActivityCode    LIKE '%EVALA%' OR
                      ActivityCode    LIKE '%EVALB%' OR
                      ActivityCode    LIKE '%EVALF%' OR
                      ActivityCode    LIKE '%EVALG%' OR
                      ActivityCode    LIKE '%EVALH%' OR
                      ActivityCode    LIKE '%EVALI%' OR
                      ActivityCode    LIKE '%EVALJ%'THEN 1
              ELSE
              CASE WHEN ActivityCode    LIKE '%EVALC%' OR
                        ActivityCode    LIKE '%EVALD%' OR
                        ActivityCode    LIKE '%EVALE%' THEN 2
                 ELSE 0
              END
    END,0) PERSISTED

    -- after this do in your query something like this
    -- maybe the case in the line
    --  WHEN date_entered BETWEEN '2016-04-01' AND '2016-04-07'
    -- does not need to exist

    -- you will need to add your business logic on the query below
    -- but at least you have the level calculated

    select T1.[FacilityID]
    t2.[ActivityCode],
    T2.[ActivityOutcome],

    the_FINAL_level = CASE
        WHEN date_entered BETWEEN '2016-04-01' AND '2016-04-07' THEN
             CASE WHEN T2.THE_LEVEL = 1 THEN T2.THE_LEVEL+1
                  ELSE
                   CASE WHEN T2.THE_LEVEL = 2 THEN THE_LEVEL+1
                   ELSE -- ADD BUSINESS LOGIC HERE
                   END
              END
        ELSE 0
    END

    FROM TABLE1 T1
    INNER JOIN TABLE2 T2
            ON T1.[FacilityID] = T2.[FacilityID]
    WHERE T2.[ActivityDate] BETWEEN '2016-04-01' AND '2016-04-07'
      AND T2.[ActivityEval?] ='Yes'


Cross database reference select access with disabled login for DB user

Make sure DB_CHAINING is ON.
Map TestUser Login to DB1 and DB2.
Grant SELECT on V2.
Don't grant SELECT on V1.

See Ownership Chains in the documentation.

Basically, what it does is that, because V1 and V2 has the same owner (that's mandatory) and because DB_CHAINING is ON, when TestUser will SELECT V2, permissions will not be checked on V1, so you don't need to grant it any permission.

TestUser will be able to retrieve data from V2 but SELECT on V1 will be denied.

Double-check that DB_CHAINING is ON by executing:

SELECT name, is_db_chaining_on
FROM sys.databases;

If it's not the case, set it with:

ALTER DATABASE DBX SET DB_CHAINING ON

...and make sure both views have the same owner.

You can try following code to validate that it's working:

Connect as sysadmin and create Databases DB1 and DB2

USE master
GO
CREATE DATABASE DB1;
GO
CREATE DATABASE DB2;
GO

in DB1:

Create a sample table TB1

Insert a value in TB1 table (this is only because I dont like empty    result set)

Create a sample view selecting all rows in TB1 table

USE DB1
GO
CREATE TABLE TB1 (id int);
GO
INSERT INTO TB1 VALUES(42);
GO
CREATE VIEW V1 AS SELECT * FROM TB1;
GO

in DB2:

Create a sample view V2 selecting all rows in DB1.dbo.V1 view

USE DB2
GO
CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
GO

Create TestUser Login

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

in DB1:

Create TestUser User mapped to TestUser Login

USE [DB1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO

in DB2:

Create TestUser User mapped to TestUser Login

Grant SELECT on V2 view to TestUser user

USE [DB2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
GRANT SELECT ON V2 TO TestUser;

Connect with TestUser Login

In DB2:

Try to select from V2

USE DB2
GO
SELECT * FROM V2;

This should fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.

Connect as sysadmin

Enable DB_CHAINING on DB1

USE master
GO
ALTER DATABASE DB1 SET DB_CHAINING ON;
GO

Enable DB_CHAINING on DB2

ALTER DATABASE DB2 SET DB_CHAINING ON;
GO

Connect with TestUser Login

In DB2:

Try to Select From V2 again, that time it should succeed

USE DB2
GO
SELECT * FROM V2;

In DB1:

Try to Select From V1

USE DB1
GO
SELECT * FROM V1;
GO

As we didnt grant TestUser any permission this will fail with error message: The SELECT permission was denied on the object 'V1', database 'DB1', schema 'dbo'.


Cannot find Database Mail feature in SQL Server Express 2012 SP1

You can use SQL Mail with SQL Server Express edition.

Borrowing liberally from Sending Mail using SQL Server Express Edition

To configure SQL mail we need to follow below steps.

--Create Sysmail Account
--Use sysmail_add_account_sp stored procedure of MSDB database to configure sysmail  account.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'MailTest',
@description = 'Sent Mail using MSDB',
@email_address = 'YourEmail@test.com',
@display_name = 'DisplayName',
--@username='umashankar@queryingsql.com',
--@password='password',
@mailserver_name = 'mail.server.com'

--Creating Database Profile
--Use sysmail_add_profile_sp stored procedure of MSDB database to configure Database Profile.
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'MailTest',
@description = 'Profile used to send mail'

--Add database Mail account to profile
--Use sysmail_add_profileaccount_sp stored procedure of MSDB database to map database mail account to Profile.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'MailTest',
@account_name = 'MailTest',
@sequence_number = 1

--Grants permission for a database user or role to use a Database Mail profile.
--To Grant permission for a database user or role to use a Database Mail profile use sysmail_add_principalprofile_sp.
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'MailTest',
@principal_name = 'public',
@is_default = 1 ;

--Enable 'Database Mail XPs'.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO

--Send Mail using Created Profile
exec msdb.dbo.sp_send_dbmail @profile_name = 'MailTest', @recipients = 'receiver@queryingsql.com', @subject = 'Mail Test', @body = 'Mail Sent Successfully', @body_format = 'text'

I will say that SQL Mail is deprecated and may be removed in a future version of SQL Server Express, but it still seems to work in the latest edition.


Kafka Partitions vs Mongo Sharding which one is better throughput

My question is how we can compare which will provide better through
  put and run time scalability in case of heavy load as i understand
  both use same mechanism adding new partitions in case of Kafka or
  adding new shards in case of Mongo.

Definitely each and every technology they have their own pros and cons. In case on MongoDB and Kafka, it's depends on your types of data like.

MongoDB does do data distribution and balancing by itself, but it is only for Document data.

Streaming like Kafka or MapR Streams can handle streaming data on HDFS/MapRFS.

I am writing down some of pros and cons of MongoDB as well as Kafka as mention below. May be it will help out to you.

MongoDB

MongoDB stores data in JSON-like documents that can vary in structure, offering a dynamic, flexible schema. MongoDB was also designed for high availability and scalability, with built-in replication and auto-sharding.

Pros

Document-oriented storage
No sql
Ease of use
Fast
High performance
Free
Open source
Flexible
Replication & high availability
Easy to maintain

Cons

Very slowly for connected models that require joins

Kafka

Kafka is a distributed, partitioned, replicated commit log service. It provides the functionality of a messaging system, but with a unique design.

Pros

High-throughput
Distributed
Scalable
High-Performance
Durable
Publish-Subscribe
Simple-to-use
Written in Scala and java. Runs on JVM
Open source
Message broker + Streaming system

Cons

Needs Zookeeper
Non-Java clients are second-class citizens

I would say, it all depends on your use cases.

If you only have one type of data, like Document data, and you do not need to process other data types, then yes, MongoDB alone is the best bet as of now.
However in modern big data environment, different data sources and data types may require different processing engines.

For further your ref here, here and here


It's not clear from your question how much of double entry bookkeeping your application will be supporting. Your database is overly constrained when it comes to the various transactions that can occur in full blown double entry bookkeeping.

In particular, there are certain transactions where the debit is split among two or more accounts that receive the debit, or the credit is split among two or more accounts that receive the credit.  These transactions represent unusual transactions in your case, if I understand your case correctly.  But you may find it necessary to support them.

To illustrate the above, consider when a homeowner makes a mortgage payment.  In the homeowner's books the debit side of the transaction will be split between Mortgage Principal and Mortgage Interest.  Mortgage Interest is an expense, while Mortgage Principal is a liability, which the mortgage payment reduces.

Again, You may or may not need to support split transactions.

Given that this is your first time out with DB design for double entry bookkeeping, it may well be worth your while to bone up on the subject, on order to avoid delivering a substandard product.  There are many excellent books on this subject.  In addition,  if you want to see some accounting databases that are freely available, you can go to http://www.databaseanswers.org/data_models/ and look under data models for accounting.

I'll give an oversimplified summary here.

Many double entry bookkeeping databases split transaction data into two tables, which I'll call TransactionHeader and TransactionDetail.  TransactionHeader has columns for data that occurs only once per transaction, such as TransactionID and Date.  TransactionDetail contains data about a transaction as it affects just one Account, and there are at least two transaction details for each transaction.  Data in TransactionDetail includes TransactionID, ItemID, AccountID,  DebitAmount, and CreditAmount.

Split transactions have more than two TransactionDetails associated with them.
The sum of the credits must equal the sum of the debits for any transaction.  Sometimes, this can be expressed as a database constraint.  Other times, the programmer is forced to maintain this aspect of data integrity in the application.

Some database builders collapse CreditAmount and DebitAmount into a single column called Amount.  Credits are entered as negative amounts.  If you do sums on groups of items, the right thing will happen.  There are, however, downsides to taking this shortcut.  I'll leave it up to the books that teach you about bookkeeping databases to fill that in.

Again, if this is overkill in your situation, feel free to disregard this answer.

Hopefully, no one will come along later and attempt to use your databases for purposes it was never intended to cover.  But I have seen this happen in the real world, with disastrous consequences.


Can i add another language based on an existing one, and set de field datefirst to 6 ?
Can i modify an existing syslanguage ?

You cannot modify sys.language in 2005 and up.

All what i want, is te set it to 6 parmanently, because i need it to get the right week number.

Is there any specific reason to do this at sql server level ? Why cant this be done at the application level ?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=62891 has a method to do it using registry changes, but be CAUTIOUS of doing it without proper testing !!

Edit: Added my comment in answer:

How about using SET DATEFIRST N (where N = 1 through 7 and 7 is default). Ref: msdn.microsoft.com/en-us/library/aa259210(v=sql.80).aspx


Importing tablespace files - Length mismatch error for DATETIME field

In all honesty, I know that DATETIME did change from 5.5 to 5.6. How do I know this ???

I ran into situation where I was setting up replication from MySQL 5.5 to 5.6 and replication would break because 5.6  wasexpecting microseconds in the relay log for DATETIME, tried to unpack the DATETIME and found out it was not the right length as 5.5 did not have microseconds (This was originally discovered by my manager then). The workaround was to switch to STATEMENT replication.

Here you are mentioning 5.7 to 5.7. I don't see why this should happen, but I have a theory.

If the source data on the running MySQL 5.7.14 instance came from an older version of MySQL as a result of doing inplace upgrades, then I can see this happening if MySQL 5.7.22 stores DATETIME different between minor 5.7 releases.

According to MySQL Documentation on Date and Time Data Type Representation:

    DATETIME

    Before MySQL 5.6.4 : 8 bytes, little endian
    After MySQL 5.6.4 :5 bytes + fractional-seconds storage, big endian

So, there was change in data storage format starting in MySQL 5.6.4 in terms of looking at 5 bytes + an unknown number of bytes for microseconds (WEIRD) !!!

I am just speculating at this point, but if the internals for DATETIME changed for 5.7 and it was not published, SHAME ON YOUR, ORACLE !!!. I am just kidding. What you should do from here is research if this bug was reported already. If not, you must report this one and let Oracle have some fun with it (They have missed a patch or overlooked microseconds somewhere).

You may have to get a mysqldump (Logical Dump) of the data and load it into target DB.

I wish you well in this adventure !!!


MySQL converting column of type TEXT to TINYTEXT truncates value at non-intuitive position, why?

There is essentially no good reason to ever use TINYTEXT.  And there are some obscure drawback.

If you are using utf8 or utf8mb4, then you may have "multi-byte" characters.  TINYTEXT is limited to 255 bytes.  VARCHAR(255), which is similar in many ways, is limited to 255 characters.

If your text is any any Asian language, then only about 85 characters can be stored in TINYTEXT.  For VARCHAR(255), a full 255 characters can be stored.

More

I agree that the ALTER is messing thing up in a strange way.  Here's a shorter example:

DROP TEMPORARY TABLE tmp;
create temporary table tmp (
    text_col text
) collate = 'utf8_general_ci' engine = InnoDB;

insert into tmp
    (text_col)
values
    (repeat('a', 257)), -- 1 byte/character
    (repeat('ア', 86)); -- 3 bytes/character

SHOW CREATE TABLE tmp\G
SELECT text_col, length(text_col) FROM tmp;
alter table tmp
    change column text_col text_col tinytext;
SELECT text_col, length(text_col), HEX(text_col) FROM tmp;  -- Strangly truncated
SHOW CREATE TABLE tmp\G

The HEX comes back with the first two bytes of Katakana 'A', which is E382A2, so I will guess that the ALTER did some kind of modulo -- picking (258 % 256 == 2) bytes to keep.

Fails for me on 5.5.43, 5.6.22 and 8.0.2.  On the other hand, MariaDB 10.0.28 and 10.3.7 complain about the ALTER:

mysql> alter table tmp
    ->     change column text_col text_col tinytext;
ERROR 1406 (22001): Data too long for column 'text_col' at row 1

File a bug at bugs.mysql.com.


Heroku PostgresApp - Fails to start with edited postgresql.conf

This is probably a kernel resources issue. Check the PostgreSQL log files to see for sure. They should be in the pg_log directory, alongside postgresql.conf.

The PostgreSQL documentation on kernel resources has a section on increasing shared memory limits for Mac OS X.

Note that increasing some settings in postgresql.conf also requires you to increase shared_buffers. This is usually documented in the info about that configuration option on the PostgreSQL site, and you'll be told in the PostgreSQL logs if you need to increase shared_buffers.


How can I check if a user have a quota on tablespace

DBA_TS_QUOTAS also contains information on the assigned quota, even if the user did not create any tables.

From the manual

  MAX_BYTES - User's quota in bytes, or -1 if no limit

Example:

SQL*Plus: Release 12.1.0.2.0 Production on Tue Oct 18 10:35:29 2016

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select tablespace_name, username, bytes, max_bytes
  2  from dba_ts_quotas
  3  where tablespace_name = 'USERS'
  4    and username = 'FOOBAR';

no rows selected

SQL> create user foobar
  2    identified by welcome
  3    default tablespace users
  4    quota 10M on users;

User created.

SQL> select tablespace_name, username, bytes, max_bytes
  2  from dba_ts_quotas
  3  where tablespace_name = 'USERS'
  4    and username = 'FOOBAR';

TABLE USERNA      BYTES  MAX_BYTES
----- ------ ---------- ----------
USERS FOOBAR          0   10485760

1 row selected.

SQL>


Dealing with varying fill factors in a SQL Server 2014 environment

I'm not sure why you think those are the only options.

If you look in IndexOptimize, the @FillFactor variable is passed in as NULL by default.

The command that appends Fill Factor looks like this:

IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4)
SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = '
+ CAST(@FillFactor AS nvarchar)

If you leave it NULL, the index will get rebuilt at its current Fill Factor.

Your only task would be to remove any Fill Factor hints from the Agent jobs, etc.


Delete millions of rows based on ids in another table

Since you are deleting a large percentage of the table, it would be better to build a new table with the rows to keep:

CREATE TABLE new LIKE real;
INSERT INTO new
    SELECT * FROM real
        LEFT JOIN extract  ON ...
        WHERE ... IS NULL;
RENAME TABLE real TO old,
             new TO real;    -- swap tables (sort of)
DROP TABLE old;              -- clean up

Even that may be too invasive.  See the following for techniques for deleting "in chunks".  They won't finish "fast", but they won't have nearly as much impact on other activity.  http://mysql.rjweb.org/doc.php/deletebig

(FOREIGN KEYs and TRIGGERs are likely to cause trouble.)


Fileshare witness for 3 node Multisubnet AAG setup

I am planning for a 3 node AG, 2 in DC and 1 in San Jose. Host OS will be Windows Server 2012 R2.

I'm going to assume that the 2 in DC are your favored site (I feel dirty typing that) and San Jose is truly only for DR.

  We dont have an option of a 3rd site...

That's a shame - if you were running Windows Server 2016 there is a cloud witness.

  ...so my question is where can I place the FSW if I have to do a planned manual failover?

Quite literally... anywhere. The witness may act as a voter (if it has a vote, depends on if dynamic quorum is turned on, the current state of nodes and votes, etc.) and will be used for arbitration.

Doing a manual failover involves having quorum and a synchronized secondary. Nothing in there requires a witness.

Let's dig a little deeper. The point of having a witness is to help with having enough votes to keep quorum and for arbitration so that you don't split brain. Since you only have the single vote (I'm assuming defaults, here) in San Jose - it's a moot point. If you truly had a DR issue you'd have (from the point of SJC) a single vote out of a possible of 3, which isn't enough to have quorum and thus the cluster service would shut down. It doesn't matter what the AG settings are, that's how WSFC works. You'd have to force quorum, regardless of if you can see to witness or not.

We're not going to get into everything with WSFC but that's the gist of your current setup. Thus, I'd put the witness where it will do the most good, which would be at site DC.

  Also, once manual failover is performed, should I be disabling the votes of the nodes in the primary site?

What issue are you trying to solve? If you do a manual failover then it is implied that the replicas are synchronous commit and synchronized also that the cluster has quorum.

If the issue that you are trying to solve is that you don't want a failover to happen or you want the AG to stay in SJC after the failover - then yes, you'll need to adjust the node weight to 0 for the DC replicas. However, if this is done, you're a single point of failure again as any hiccup on the SJC node means your cluster is down (I mean, you removed the voters - we have a bunch of watchers but no voters).

  I mentioned about the manual failover with 2 scenarios in mind 1) To failover the AAG to SanJose in case any maintenance activities have to be performed on the DC nodes.

In that case I'd do maintenance on a node at a time and keep it within DC, not immediately go out to SJC. You could also ADD a node at any time in DC just for the purposes of that. If you application or user base is mostly in DC, the added latency to SJC might not be palatable. I can't tell you if it will or won't be.

Summary: First I would try to keep the AG in DC and only use SJC as DR, unless all replicas are Synchronous commit, then it doesn't really matter. Taking away the votes would be beneficial in the case for keeping the cluster up, but still wouldn't help all that much.

  2) In case of DR - as you mentioned - a force fail over is the only option as I do not have enough votes to sustain quorum. Is it a better idea to have 2 nodes at SanJose as well?

"It depends™" Again, what are you trying to solve? I wouldn't over-engineer a solution to try and solve every possible edge case or problem. Have your top 3-5 true issues and solve for that.

  we do have a 3rd datacenter in Atlanta - can that be used as a third site for the witness?

So you lied to me! I see how it is...

Yes, you can and should absolutely use that location for a FSW :)


Does "WHERE 1=1" usually have an impact on query performance?

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


how does SQL Server 2008 handle joins internally?

The internals of any query operation can be reviewed in the query execution plan.  There are many factors involved in how the data is extracted and queried from the source tables, but for the purposes of your question, there are two elements you need to be concerned about:

Seeks and Scans

The first step to any query is to extract the data from the source tables.  This is performed using one of two actions (and variations within): a seek or a scan.  A seek is where the query engine can leverage an index to narrow down the data retrieval.  If no index is available, then the engine must perform a scan of all records in the table.  In general, seeks typically perform better than scans due because it is more selective.

Physical Joins

Once data is retrieved from the tables, it must be physically joined.  Note, this is not the same as logical joins (INNER and OUTER).  There are three physical join operators that can be used by the engine:

Nested Loop Join
Merge Join
Hash Join

The query engine will evaluate several factors to determine which physical join will be the most efficient for the query execution.  As a simple example, I assembled this SQL Fiddle example, where you can view a basic execution plan using your sample schema. Note, however, that this plan could change with more data being queried.  You will need to review the query plans in your environment to truly understand how your data is being retrieved.

Query plan analysis and performance tuning is beyond the scope of this answer, but I strongly recommend getting a hold of Grant Fritchey' SQL Server 2012 Query Performance Tuning for a comprehensive explanation of query execution internals and tuning approaches.


Data warehouse vs Data mart (sql server)

This is a pretty good question. This topic, many times, gets over-thought. It really all comes down to business needs.

If the need to compartmentalize data in to different subject areas (Finance, Sales, Manufacturing, Inventory, etc...) If this is far out of scope for the business cases, then you should probably stick with your standard database schema.

From my view point, a single data mart is not terrible useful except for in fringe cases where a single department has needs for a data warehouse like reporting infrastructure... Then a data mart might be useful.


Any general reasonable performance tests for DBs on application server and DBs on separate server?

If you can capture a typical workload with Profiler, you could replay it against a point-in-time restored copy of the database via the RML tools. Summary of the RML tools can be found in Testing Stored Procedure Scalability.

What you decide to measure as part of these tests in dependent on the application. Measuring response time is simple for a web application for example, not so easy with a desktop client.

It would be rare to find that performance degrades when you move the database to a dedicated server. Typically this only occurs when the application is overly "chatty" i.e. executing lots of iterative queries (incorrect use of an ORM a common cause), rather than set operations. In this situation the additional overhead of marshalling data over the wire via TCP or Named Pipes vs Shared Memory Protocol outweighs the performance gained from dedicating a server to SQL.


Sudden periods of performance degradation

During that period can you get a list of the sql processes running?

show full processlist;

Is this a server where you don't have root access to the actual OS like in AWS?

If you do have root access and this is something you can research it might be unrelated to MySQL, i.e., some other process on the server is affecting performance. This might be out of scope of the question but use iostat, top, free for utilities to use in that regard.

For MySQL specific strategies you can also enable the query log which will log every query. Careful with this because it'll fill up disk very quickly depending on query traffic. Also requires a restart. If you can't do that then you probably want to watch traffic on the wire:

tcpdump -i any -s0 -A -n -nn port 3306

or

tcpdump -i any -s0 -A -n -nn port 3306 -w /tmp/mysql.pcap

to output to a file and then view with a UI tool like Wireshark. Then you can see the actual queries running.


Oracle EXPDP using QUERY parameter with results from SELECT

No, there is not.

You have already defined manually the filters for each table, why not use that? Just dynamically generate the parameterfile before running expdp:

select 'QUERY=' || table_owner || '.' || table_name || ':"' || where_condition || '"'
from t_definition;

QUERY=DEV.TBL1:"WHERE CMP_ID=0"
QUERY=DEV.TBL2:"WHERE 1=0"
QUERY=DEV.TBL3:"WHERE SEQ_ID=10 OR CMP_ID=0"
...

Or use DBMS_DATAPUMP.


Viewing the SQL of a view from a backup set

Using native tools, no.  Idera Virtual Database will allow you to read the backup file in the manner you're describing.  Other tools, such as SQL Litespeed and Red Gate SQL Backup will allow you to do object level recovery.  However, you will have to pay for these tools (though you might be able to get away with using the trials short term).

Otherwise, you'll need to restore the database file somewhere.  This is why it is important to also back up your schema to script files or store your database object definitions in source control.


How many databases does a sybase instance have?

ASE can have up to 32K dbs (includes various system dbs).

The master db holds server wide metadata (eg, logins, roles, configs, databases) and can also hold user data (though this is discouraged).

Other system databases include sybsystemprocs (collection of system stored procs and functions), tempdb (temporary scratch space; DBA can create additional temporary dbs), model (template used when creating a new database), sybsystemdb (used to manage distributed txns); there are several other system dbs that can exist depending on options/features the DBA enables.

Any other databases would fall under the heading of user databases.

And yeah, a user db would be similar to a HANA tenant db, though all ASE databases are accessed via the same host:port combo (HANA tenants have their own port if I recall correctly).


Oracle 11gR2 + RedHat 6.5 cannot start sqlplus

You need to set LD_LIBRARY_PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

and to add $ORACLE_HOME/bin (sqlplus path is inside) in your path

PATH=:$ORACLE_HOME/bin:$PATH


Assuming that you have SQLite 3.25.0 or later, use a window function to compute the number of a row in each sorted team:

SELECT Team,
       sum(Buy)
FROM (SELECT Team,
             Buy,
             row_number() OVER (PARTITION BY Team
                                ORDER BY Buy     ) AS RowNum
      FROM Players)
WHERE RowNum <= 10
GROUP BY Team
ORDER BY sum(Buy);


Optimization issue with user defined function

There are three main technical reasons you get the plan you do:

The optimizer's costing framework has no real support for non-inline functions. It does not make any attempt to look inside the function definition to see how expensive it might be, it just assigns a very small fixed cost, and estimates the function will produce 1 row of output each time it is called. Both these modelling assumptions are very often completely unsafe. The situation is very slightly improved in 2014 with the new cardinality estimator enabled since the fixed 1-row guess is replaced with a fixed 100-row guess. There is still no support for costing the content of non-inline functions, however.
SQL Server initially collapses joins and applies into a single internal n-ary logical join. This helps the optimizer reason about join orders later on. Expanding the single n-ary join into candidate join orders comes later, and is largely based on heuristics. For example, inner joins come before outer joins, small tables and selective joins before large tables and less selective joins, and so on.
When SQL Server performs cost-based optimization, it splits the effort into optional phases to minimize the chances of spending too long optimizing low-cost queries. There are three main phases, search 0, search 1, and search 2. Each phase has entry conditions, and later phases enable more optimizer explorations than earlier ones. Your query happens to qualify for the least-capable search phase, phase 0. A low enough cost plan is found there that later stages are not entered.

Given the small cardinality estimate assigned to the UDF apply, the n-ary join expansion heuristics unfortunately reposition it earlier in the tree than you would wish.

The query also qualifies for search 0 optimization by virtue of having at least three joins (including applies). The final physical plan you get, with the odd-looking scan, is based on that heuristically-deduced join order. It is costed low enough that the optimizer considers the plan "good enough". The low cost estimation and cardinality for the UDF contributes to this early finish.

Search 0 (also known as the Transaction Processing phase) targets low-cardinality OLTP-type queries, with final plans that usually feature nested loops joins. More importantly, search 0 runs only a relatively small subset of the optimizer's exploration abilities. This subset does not include pulling an apply up the query tree over a join (rule PullApplyOverJoin). This is exactly what is required in the test case to reposition the UDF apply above the joins, to appear last in the sequence of operations (as it were).

There is also an issue where the optimizer can decide between naive nested loops join (join predicate on the join itself) and a correlated indexed join (apply) where the correlated predicate is applied on the inner side of the join using an index seek. The latter is usually the desired plan shape, but the optimizer is capable of exploring both. With incorrect costing and cardinality estimates, it can choose the non-apply NL join, as in the submitted plans (explaining the scan).

So, there are multiple interacting reasons involving several general optimizer features that normally work well to find good plans in a short period of time without using excessive resources. Avoiding any one of the reasons is enough to produce the 'expected' plan shape for the sample query, even with empty tables:

There is no supported way to avoid search 0 plan selection, early optimizer termination, or to improve the costing of UDFs (aside from the limited enhancements in the SQL Server 2014 CE model for this). This leaves things like plan guides, manual query rewrites (including the TOP (1) idea or using intermediate temporary tables) and avoiding poorly-costed 'black boxes' (from a QO point of view) like non-inline functions.

Rewriting CROSS APPLY as OUTER APPLY can also work, as it currently prevents some of the early join-collapsing work, but you have to be careful to preserve the original query semantics (e.g. rejecting any NULL-extended rows that might be introduced, without the optimizer collapsing back to a cross apply). You need to be aware though that this behaviour is not guaranteed to remain stable, so you would need to remember to retest any such observed behaviours each time you patch or upgrade the SQL Server.

Overall, the right solution for you depends on a variety of factors that we cannot judge for you. I would, however, encourage you to consider solutions that are guaranteed to always work in future, and that work with (rather than against) the optimizer wherever possible.


Without knowing what exactly you did, all I can say is it should work, but if you use Grid Infrastructure, you may need some additional configuration.
ORA-28365: wallet is not open when starting database with srvctl or crsctl when TDE is enabled (Doc ID 2711068.1)
One more thing, in the -wallet parameter we specify a directory usually, and not cwallet.sso, which will be generated automatically.
Below just works:
SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------- ------------------
file                 /u01/app/oracle/admin/$ORACLE_SID/wallet           CLOSED

SQL> !orapki wallet create -wallet /u01/app/oracle/admin/$ORACLE_SID/wallet -auto_login -pwd Oracle.123
Oracle PKI Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------- ------------------
file                 /u01/app/oracle/admin/$ORACLE_SID/wallet           OPEN_NO_MASTER_KEY

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

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size             536872856 bytes
Database Buffers         1593835520 bytes
Redo Buffers                4923392 bytes
Database mounted.
Database opened.
SQL> select * from v$encryption_wallet;

WRL_TYPE             WRL_PARAMETER                                      STATUS
-------------------- -------------------------------------------------- ------------------
file                 /u01/app/oracle/admin/$ORACLE_SID/wallet           OPEN_NO_MASTER_KEY


SQL Server AlwaysOn ReadOnly Routing to Secondary

Does anyone know what is wrong with the settings or reason for this? I checked altering the settings but didn't work.

You may verify the read-only routing configuration based on this post

Once all set (upon validation): If you're using SQL Login, try to mention default database that is part of AG, then your connection must be re-direct to secondary replica.

or mention following parameter from SSMS connection. This is how usually every application connects.

ApplicationIntent=READONLY; Initial Catalog=YourDatabase


Will redirecting queries to dev/null warm up indexes?

I see one of the comments in the question mentions caching indexes

  please note that only one index will be cached in the buffer pool (most probably PRIMARY, but check EXPLAIN to be sure). You may need secondary indexes in the cache as well, so you have to run additional SELECTs

It just so happens I have post from Feb 04, 2012 that lets you make those queries (MySQL warm procedure). From that post, here are the queries to show all queries you need to run:

LOADING INNODB BUFFER POOL

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,
    ' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
    FROM
    (
        SELECT
            engine,table_schema db,table_name tb,
            index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
        FROM
        (
            SELECT
                B.engine,A.table_schema,A.table_name,
                A.index_name,A.column_name,A.seq_in_index
            FROM
                information_schema.statistics A INNER JOIN
                (
                    SELECT engine,table_schema,table_name
                    FROM information_schema.tables WHERE
                    engine='InnoDB'
                ) B USING (table_schema,table_name)
            WHERE B.table_schema NOT IN ('information_schema','mysql')
            ORDER BY table_schema,table_name,index_name,seq_in_index
        ) A
        GROUP BY table_schema,table_name,index_name
    ) AA
ORDER BY db,tb

LOADING MyISAM KEY CACHE

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER
BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT
B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name
FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema
NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb
;

LOADING INNODB BUFFER POOL AND MyISAM KEY CACHE

SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',
    db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
    SELECT
        engine,table_schema db,table_name tb,index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM (
        SELECT
            B.engine,A.table_schema,A.table_name,
            A.index_name,A.column_name,A.seq_in_index
        FROM
            information_schema.statistics A INNER JOIN
            (
                SELECT engine,table_schema,table_name
                FROM information_schema.tables
                WHERE engine IN ('InnoDB','MyISAM')
            ) B USING (table_schema,table_name)
        WHERE
            B.table_schema NOT IN ('information_schema','mysql')
            AND A.index_type <> 'FULLTEXT'
        ORDER BY
            table_schema,table_name,index_name,seq_in_index
        ) A
    GROUP BY
        table_schema,table_name,index_name
) AA
ORDER BY
    engine DESC,db,tb
;

Simply redirect the output to a text file like /root/IndexWarmup.sql

Then, login to mysql and run

mysql> source /root/IndexWarmup.sql

OPTIONAL SUGGESTION

I would highly recommend upgrading to MySQL 5.6. Why? You can do the following:

Dump and Load a map of the InnoDB Buffer Pool Shutdown and Startup

[mysqld]
innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Dump and Load a map of the InnoDB Buffer Pool on Demand

mysql> set global innodb_buffer_pool_dump_now = 1;
mysql> set global innodb_buffer_pool_load_now = 1;

That way, you never have to read index pages into the Buffer Pool you never access.

Please see the MySQL Documentation

innodb_buffer_pool_dump_at_shutdown
innodb_buffer_pool_load_at_startup
innodb_buffer_pool_dump_now
innodb_buffer_pool_load_now


Point in time restore fails - backup file from before the restore point not available (why does this matter)

for a restore to a point in time, you just need the latest full-backup and all the transaction logs thereafter.

this script will show you when was the last backup taken and where it is located.

declare @backup_type CHAR(1) = 'D' --'D' full, 'L' log

                ;with Radhe as (
                    SELECT  @@Servername as [Server_Name],
                    B.name as Database_Name,
                    ISNULL(STR(ABS(DATEDIFF(day, GetDate(), MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
                    ISNULL(Convert(char(11), MAX(backup_finish_date), 113)+ ' ' + CONVERT(VARCHAR(8),MAX(backup_finish_date),108), 'NEVER') as LastBackupDate
                    ,BackupSize_GB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00/1024.00 AS NUMERIC(18,2))
                    ,BackupSize_MB=CAST(COALESCE(MAX(A.BACKUP_SIZE),0)/1024.00/1024.00 AS NUMERIC(18,2))
                    ,media_set_id = MAX(A.media_set_id)
                    ,[AVG Backup Duration]= AVG(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,[Longest Backup Duration]= MAX(CAST(DATEDIFF(s, A.backup_start_date, A.backup_finish_date) AS int))
                    ,A.type
                    FROM sys.databases B

                    LEFT OUTER JOIN msdb.dbo.backupset A
                                 ON A.database_name = B.name
                                AND (@backup_type IS NULL OR A.type = @backup_type  )

                    GROUP BY B.Name, A.type

                )

                 SELECT r.[Server_Name]
                       ,r.Database_Name
                       ,[Backup Type] = r.type
                       ,r.DaysSinceLastBackup
                       ,r.LastBackupDate
                       ,r.BackupSize_GB
                       ,r.BackupSize_MB
                       ,F.physical_device_name
                       ,r.[AVG Backup Duration]
                       ,r.[Longest Backup Duration]

                   FROM Radhe r

                    LEFT OUTER JOIN msdb.dbo.backupmediafamily F
                                 ON R.media_set_id = F.media_set_id

                    ORDER BY r.Server_Name, r.Database_Name

for the transaction logs, use the same script above with @backup_type CHAR(1) = 'L'

see what you have inside your backup file doing a restore filelistonly

RESTORE FILELISTONLY FROM DISK=N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak'
--- CHECK MDF ,NDF AND log files with size

  check the disk space available

---------------------------------------
--BEFORE RESTORING - ALWAYS CHECK THE AVAILABLE DISK SPACE
--> 4 - Free space by drive.
EXEC master..xp_fixeddrives
---------------------------------------

  replication

---------------------------------------
--> 5 - see if the database is involved in replication
---------------------------------------
SELECT
    name as [Database name],
    CASE is_published
        WHEN 0 THEN 'No'
        ELSE 'Yes'
        END AS [Is Published],
    CASE is_merge_published
        WHEN 0 THEN 'No'
        ELSE 'Yes'
        END AS [Is Merge Published],
    CASE is_distributor
        WHEN 0 THEN 'No'
        ELSE 'Yes'
        END AS [Is Distributor],
    CASE is_subscribed
        WHEN 0 THEN 'No'
        ELSE 'Yes'
        END AS [Is Subscribed]
FROM sys.databases
WHERE database_id > 4

this is an example of a point in time restore.
the database is called IntEng2 and it already existed, so it needs to be overwritten.

it will be restored to this point in time: 2014-08-22T11:30:00

--================================================
-- RESTORE THE DATABASE TO A POINT IN TIME - the database IntEng2 is NOT replicated
--================================================

-- put database in single user mode - only me - the man doing the job ;)
USE [master]
ALTER DATABASE [IntEng2] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

-- backup the tail of the log
BACKUP LOG [IntEng2] TO  DISK = N'H:\SQLBackups\IntEng2_LogBackup_2014-08-22_14-48-05.bak' WITH NOFORMAT, NOINIT,  NAME = N'IntEng2_LogBackup_2014-08-22_14-48-05', NOSKIP, NOREWIND, NOUNLOAD,  NORECOVERY ,  STATS = 5

-- restore the full backup - with norecovery
-- with replace on the log file because for some reason this database has 2 log files and we want to keep it like that
RESTORE DATABASE [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_021056_1290970.bak'
WITH  FILE = 1,  MOVE N'IntEng2_Log2' TO N'G:\SQLLog\IntEng2_Log2.ldf',
NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5

-- resotore the log files - IN THE SPECIFIC ORDER ---
-- with NORECOVERY

RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_030002_2577183.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_031501_5336409.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_033001_6524314.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_034501_7888108.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_040002_3541104.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_041506_9327770.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_043001_9532881.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_044502_0339177.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_050002_0942167.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_051506_3762732.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_053005_8234237.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_054505_2202118.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_060002_3445813.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_061502_1611989.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_063002_2204240.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_064501_8698190.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_070002_1582584.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_071501_8781090.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_073002_2857856.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_074502_4935206.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_080001_8744757.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_081501_8779343.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_083001_7488322.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_084501_5171402.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_090002_4606122.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_091501_9724206.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_093002_3221461.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_094502_0465413.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_100001_7103846.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_101501_8737945.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_103002_2375779.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_104501_8634658.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_110002_4519941.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_111501_5088114.trn' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5

-- restore the log closest to the time when the disaster happened
-- this will be the restore with STOP AT
RESTORE LOG [IntEng2] FROM  DISK = N'H:\SQLBackups\IntEng2\IntEng2_backup_2014_08_22_113001_5819154.trn'
WITH  FILE = 1,  NOUNLOAD,  STATS = 5,
STOPAT = N'2014-08-22T11:30:00'

--================================================
-- BRING THE DATABASE BACK TO LIFE
--================================================
-- have a look at the database,
-- if everything is ok
-- bring the database back to multiuser
ALTER DATABASE [IntEng2] SET MULTI_USER

GO

there were other things I did not include here:

1) before a restore - make sure all the processes that use that database are stopped.

2) before a restore - script all the permissions, so that you can re-apply them later on.


So there is a way to do this using a couple of features that have probably never been used together in the real world: snapshot isolation and bound sessions.

Bound Sessions in particular is an extremely old, obscure and deprecated feature (although like many deprecated features it may never actually be removed).

Anyway the procedure sp_bindsession allows a session to participate in a transaction started by a different session.  And if both sessions are in SNAPSHOT isolation, then they will share a point-in-time consistent view of the database.

So setup:

create database testdb
alter database testdb set allow_snapshot_isolation on
go
use testdb
drop table if exists t
create table t(id int)

Then from one session:

--session 1
use testdb
if @@trancount > 0 rollback

set transaction isolation level snapshot
begin transaction

declare @token varchar(255)
exec sp_getbindtoken @token out
select @token

insert into t(id) values (1)

and from another session, after copying and pasting the bind token:

--session 2
use testdb
go

set transaction isolation level snapshot
declare @token varchar(255) = '7-`L>5B0-1[OgFb4FVTWU]5---/=AM--'

select @@trancount --returns 0

exec sp_bindsession @token

select @@trancount --returns 1

go

select * from t --returns a row

rollback --rolls back the transaction started in the other session

go

select * from t --no rows

But any real-world scenario, you should probably either use Multiple Active Result Sets and run the queries in the same session, or create a Database Snapshot


Can two primary AlwaysOn servers be each other's secondary--for completely seperate sets of databases?

Yes, it is possible to have two availability groups configured in this manner.  Each availability group has its own settings in regard to failover policies, so you can set up one availability group to be primary on SQLA, and configure SQLB for manual failover.  So when SQLA reboots, the AG goes offline, but doesn't fail over to SQLB unless you manually fail it over.  When SQLA comes back online, it takes ownership of the AG and brings the database online and continues on just as before.

The second availability group would be configured to be primary on SQLB, and SQLA would be set for manual failover.

This is a completely reasonable setup and makes sense in a scenario where both sites have production databases in use.


How to Join Two Result sets to query on output came from Two statements

The problem that you are having is most likely coming from the tbl_sales because your item can appear in the table more than once.  When you then JOIN your tables together, you are returning the qty from tbl_purchases for each row in tbl_sales.

You can see this behavior by performing a SELECT on your tables:

SELECT e.*, p.qty purQty, s.qty salesQty
FROM tbl_matentry e
LEFT OUTER JOIN tbl_purchases p
 ON p.item = e.eid
LEFT OUTER JOIN tbl_sales s
  ON s.item=e.eid
ORDER BY e.eid;

See Demo. You will see in my sample, that the rupa qty appears twice because there are two entries in tbl_sales.

One way to get the result would be to calculate the TotalSold and TotalStock in subqueries:

select e.eid,
  e.item,
  coalesce(p.TotalStock, 0) TotalStock,
  coalesce(s.TotalSold, 0) TotalSold,
  coalesce(p.TotalStock, 0) - coalesce(s.TotalSold, 0) BalanceStock
from tbl_matentry e
left join
(
  select item, sum(qty) TotalSold
  from tbl_sales
  group by item
) s
  on e.eid = s.item
left join
(
  select item, sum(qty) TotalStock
  from tbl_purchases
  group by item
) p
  on e.eid = p.item;

See SQL Fiddle with Demo.

This could also be written using a single subquery to tbl_sales:

select e.eid,
  e.item,
  sum(p.qty) TotalStock,
  coalesce(s.TotalSold, 0) TotalSold,
  coalesce(sum(p.qty)) - coalesce(s.TotalSold, 0) BalanceStock
from tbl_matentry e
left join tbl_purchases p
  on e.eid = p.item
left join
(
  select item, sum(qty) TotalSold
  from tbl_sales
  group by item
) s
  on e.eid = s.item
group by e.eid, e.item, TotalSold;

See SQL Fiddle with Demo


sp_BlitzFirst - File Stats Interpretation

Disclaimer: I'm one of the authors of sp_BlitzFirst and the First Responder Kit.

The wait stats section is on top for a reason: you need to focus on your top wait types. It doesn't really matter how fast or slow your drives are if SQL Server isn't waiting on them. Great example: say you've got a read-only database hosted on a server with enough memory to cache the entire database, and none of your queries happen to hit TempDB. Once the data's up in cache, does it matter how slow your drives are? Kinda - for maintenance tasks like backups and index rebuilds - but not for end user queries.

Now, look at your server's top 3 waits:

CXPACKET 1,530 hours - related to parallelism
OLEDB 502 hours (typically harmless, but I leave it in there because sometimes it isn't harmless in large quantities)
LATCH_EX 250 hours - usually goes hand in hand with CXPACKET
PAGEIOLATCH_SH 191 hours - reading data pages from a data file.

Yo, man - your top wait is coming up on TEN TIMES LARGER than your storage wait.

Forget the storage: you're barking up the wrong tree. Focus on your top wait type. To tune parallelism, go to https://BrentOzar.com/go/cxpacket. The short story:

Set Cost Threshold for Parallelism to 50 (some folks will say 40 or 75, and that's fine too)
Set MAXDOP to the number of cores in each processor
Find the queries reading large amounts of data, and tune them or the indexes they need. My favorite way is sp_BlitzCache @SortOrder = 'reads'
Ask SQL Server which indexes it wishes it would have had during query execution. My favorite way is sp_BlitzIndex @GetAllDatabases = 1.


PXC and Galera Cluster do not write scale very well.
I mentioned this in my answer to the old post Transaction speed benchmarks for mySQL v5.6 replication - seems very slow where I said the following:
Please keep in mind that Percona XtraDB Cluster does not write scale very well to begin with. Note what the Documentation says under its drawbacks (Second Drawback):

This can’t be used as an effective write scaling solution. There might be some improvements in write throughput when you run write traffic to 2 nodes vs all traffic to 1 node, but you can’t expect a lot. All writes still have to go on all nodes.

What this means for you is this: The more nodes in the Cluster, the more network communication between nodes is required to certify that all 9 nodes can commit together or rollback together. This increased communication, which makes n(n-1) calls between nodes (where n is the number of nodes), will definitely become the bottleneck for a single INSERT, UPDATE, or DELETE. It becomes worse with multi-statement transactions. This is why most examples of PXC/Galera stress using three nodes. You could probably get away with 5 nodes but you should expect worse write performance than 3 nodes.
You could probably setup 3 different 3-node clusters and use one node from each cluster in Circular Replication (See my post MySQL 5.5 Replication to Galera/Percona XtraDB Cluster). This could easily create split brain scenarios if MySQL Replication breaks between clusters.
EPILOGUE
Please stick with 3-node cluster. Should you go with 9 nodes, you must live with the network communication across zones. If so, try to make nodes reside on the East Coast in different zones, or on the West Coast in different zones.


Filter on time difference between current and next row

Consider the sequence of events in a SELECT query, as explained here:

Best way to get result count before LIMIT was applied

Window functions operate on the result set after WHERE clauses have been applied. It would be a logical contradiction to have a window functions in a WHERE clause. Like mustaccio said: use a subquery - or a CTE - to filter on the result of a window function.

But also get the logic right:

  where the time difference to the next row is greater than e.g. 10 seconds.

SELECT *
FROM  (
   SELECT created
        , lead(created) OVER (ORDER BY created) - created AS delta
   FROM   tbl
   ) t
WHERE  delta > interval '10 sec'
ORDER  BY created;


Filter one column by value in second column postgres

I think you want a self join like this,

SELECT f1.title AS book, f2.title AS subtitle
FROM foo AS f1
JOIN foo AS f2
  USING (id)
WHERE f1.title <> f2.title
  AND f1.title LIKE 'Book%'
ORDER BY 1, 2;

    title     |   title
--------------+-----------
 Book Title 1 | Chapter 1
 Book Title 1 | Chapter 2
 Book Title 2 | Chapter 1
(3 rows)

If all subtitles are chapters, you can also do this

SELECT f1.title AS book, f2.title AS chapter
FROM foo AS f1
JOIN foo AS f2
  USING (id)
WHERE f1.title <> f2.title
  AND f1.title LIKE 'Book%'
  AND f2.title LIKE 'Chapter%'
ORDER BY 1, 2;


Sybase Database : least permission/roles to extract ddl

From the ASE Utility Guide; Section: ddlgen, regarding Permissions required to use ddlgen:

Users must have either sa_role or sso_role to generate DDL for:
- Encryption keys
- Logins
- Roles
For all other objects, users do not need any specific permissions or roles to generate DDL

Is your user receiving errors when they attempt to use ddlgen (and if so, what errors)?


For modern MySQL version:

WITH cte AS (          SELECT Ball1 ball FROM lottery
             UNION ALL SELECT Ball2      FROM lottery
             UNION ALL SELECT Ball3      FROM lottery
             UNION ALL SELECT Ball4      FROM lottery
             UNION ALL SELECT Ball5      FROM lottery
             UNION ALL SELECT Ball6      FROM lottery)
SELECT ball, COUNT(*) cnt
FROM cte
GROUP BY ball

For ancient MySQL version:

SELECT ball, COUNT(*) cnt
FROM (          SELECT Ball1 ball FROM lottery
      UNION ALL SELECT Ball2      FROM lottery
      UNION ALL SELECT Ball3      FROM lottery
      UNION ALL SELECT Ball4      FROM lottery
      UNION ALL SELECT Ball5      FROM lottery
      UNION ALL SELECT Ball6      FROM lottery) cte
GROUP BY ball


What is the correct term to describe a "persistent transaction"?

I know sort-of what you mean, we do it on the storage using NetApp FlexClones. A FlexClone "looks" just like a copy of a filesystem, but the underlying blocks on the disks are copy-on-write, so the only space it actually occupies is that of the changes you make.

However, the clone is not a stream of changes, like a redo log. The way NetApp works is that each block you see at the filesystem level is really a just pointer to a block on the "real" storage. Creating a FlexClone is merely a matter of copying the list of pointers, so it is a very lightweight operation. When you change a block on a FlexClone the pointer is moved to a fresh block, and the original is copied, then it proceeds as normal. This has two interesting implications:

Once you have changed a block once, you can go on changing it as many times as you like - it will occupy no more storage
Once the initial COW is done, speed of access to the FlexClone is identical to that of a real volume.

We would typically put the primary into hotbackup mode (for Oracle, but since it's on the storage this will work with any database that has an equivalent mode), FlexClone the storage, mount the clone with NFS onto the (much smaller) test server, then create a new controlfile, recover the database from archived redo logs as necessary and open it up with resetlogs. We allow a "snap reserve" of 10% by default (i.e. you can change up to 10% of the clone) but this is fully configurable - you can make it 100% and do a "split" which creates an independent copy too. This technology (with some clever shell scripting) has brought creating test environments down from 3+ days to a matter of minutes at my site, I am a huge fan.


If you want to use aggregate results as a filter, you need to use the HAVING clause.

Something like this should work for you.

SELECT name
    , MAX(date) AS max_date
FROM table
GROUP BY name
HAVING MAX(date(date)) <> DATE(NOW())


How do I determine the current "idle timeout" value for PostgreSQL?

Before 9.6, no such timeout exists in PostgreSQL. Thereafter, it only exists if you are idle in the middle of a transaction.

show idle_in_transaction_session_timeout;

Note however that any router, gateway, or firewall between you and the server can nuke your connection at any time it chooses.  TPC keepalives may or may not dissuade them.  PostgreSQL can't offer much insight into what your networking equipment does.


DB2 - Inserting a timestamp operation result in a timestamp field

Following sequence of statements worked fine in Db2 (for LUW)

create table mschema.MDUMMY (k varchar(10), n int, t timestamp)

INSERT INTO mschema.MDUMMY (k, n, t) VALUES (   '70',   70,   (SELECT
TIMESTAMP('1965-07-27-15.30.00') + 59 SECONDS FROM SYSIBM.SYSDUMMY1)
);

SELECT * FROM mschema.MDUMMY

I just copied the second statement from your question.
This means you made some other mistake running it or you are maybe not running it on Db2 LUW - this is why it is always useful to specify the OS.


How To get value of column when it exist in content value of column by sql server 2016

Under some simplistic / naive assumptions (that match the given data):

The descriptions have only spaces and no other whitespace or connecting characters (dots, comma, question marks, dashes, etc.)
Titles are single words (no spaces, no extra characters either).

We can use LIKE to check whether a description contains a title:

concat(' ', description, ' ') LIKE concat('% ', title, ' %')

to first check that the matches are according to requirements:

select s.description, coalesce(t.title, 'Others') as title
from table2 as s
  left join table1 as t
  on concat(' ', s.description, ' ')
     LIKE concat('% ', t.title, ' %') ;

and then do the update:

update s
set s.names = coalesce(t.title, 'Others')
from table2 as s
  left join table1 as t
  on concat(' ', s.description, ' ')
     LIKE concat('% ', t.title, ' %') ;

Test in dbfiddle.uk


The key of the relation resulting from a full outer join

Short Answer
Closure in relational algebra has nothing to do with the concept of a key.  A relational variable holds a relation so long as that relation has the following properties:

Tuples are not ordered - order holds no implicit meaning
Tuples are distinct - there are no duplicates
Attributes are not ordered - order holds no implicit meaning
Attributes are single valued - only one of whatever type it is

The basic operators of the relational algebra are indeed closed and thus when applied on relations always yield relations.  Keys are irrelevant as by definition each relation has no duplicate tuples.  Any operation that would result in duplicate tuples (for example the projection of a single attribute over the relation) by definition eliminate those duplicate tuples when providing the result.  As you know SQL does not properly implement this.
Long Answer
In order to really understand the short answer, and to understand the addtional nuances that "outer join" throws into the equation, we need to delve into the foundations of relational algebra in number algebra.
Number Algebra
Algebra is "the part of mathematics in which letters and other general symbols are used to represent numbers and quantities in formulae and equations" as defined by google.  One algebraic property is operation, meaning there are actions that work to change numbers.  For numbers these are addition, subtraction, multiplication, division, raising to a power, and taking of a root.   There are many other algebraic properties as well such as the associative property which states that you can group numbers in any way without changing the result of the operations.  These properties give algebra its unique power, in that with these properties we can use letters to represent numbers, manipulate them via the defined operators in equations, and know the transformation will be absolutely correct regardless of what actual number is eventually substituted for the letters.
Closure is another of the algebraic properties which states that if an operator which works on numbers always produces numbers then numbers are closed under that operation.  Real numbers we know are closed under addition for example.  Add any two real numbers and the result will always be a real number.  Real numbers are not closed under the square root operation as the square root of -1 is not a real number.  All of this comes straight out of the property glossary of the Math Forum @ Drexel.
Relational Algebra
Chris Date says that Ted Codd was a genius.  Codd's unique genius was the realization that mathematical relations, combined with set theory and logic, would be very practical when applied to data management.  Fabian Pascal's Practical Database Foundation Series provides an excellent explanation of why this is so, and I'll briefly review a few of his insights.
One of the primary database problem of the 1960's was one where a program had to be written for each "question" you would want to ask of the DBMS managing the data.  Codd showed that by taking the concept of a relation and adapting it for databases a logical model of data input and output could be developed completely distinct from how data is physically stored.  Part of that logical model was the set of operators that would apply to relations - the relational algebra.  Just like number algebra allows us to exploit the algebraic properties to manipulate massively complex equations where letters stand for any potential number and guarantee correct results, the relational algebra can do the same for relations.
Why is this important?  Because with algebra, operations and their results do not depend of what the relations and their attributes actually mean.  Then, by adapting logic, which like algebra proscribes valid forms which can be used to evaluate an argument to be true or false regardless of what the propositions mean, a generalized query language can be implemented that enables a user of the DBMS to ask a true question of the data - one of arbitrary complexity - and the DBMS can be programmed using the rules of relational algebra to give a provably correct answer!
Now just like with number algebra, closure is the essential property that gives the algebra its power.  Relations are closed under the basic operators and as such you can arbitrarily nest these operations at will, in any order, to declare practically any "question" you could ever think of.  Once the DBMS is created that implements the relational algebra, unique programs no longer have to be written to find the answer to each question.  The DBMS user simply declares the question using the relational algebra.
Outer Join
Thus far I have been saying basic operators.  That is because the theory does not account for missing data.  Each relation by definition contains tuples with a value for every attribute.  But to adapt the mathematics to the practical realities of missing data some solution has to be developed for when an attribute value is unknown.  SQL implemented the NULL and 3VL.  Codd later proposed marks and 4VL.  The join operator had to be extended to preserve un-matched tuples, and SQL implemented the extension by producing NULLs instead of values for the attributes of un-matched tuples.  Date shows that by doing this outer join is:

a kind of shotgun marriage: It forces tables into a kind of union — yes, I do mean union, not join — even when the tables in question fail to conform to the usual requirements for union (see Chapter 6). It does this, in effect, by padding one or both of the tables with nulls before doing the union, thereby making them conform to those usual requirements after all. But there’s no reason why that padding shouldn’t be done with proper values instead of nulls. (Date, C. J. (2011-12-16). SQL and Relational Theory: How to Write Accurate SQL Code (Kindle Locations 2601-2602). O'Reilly Media. Kindle Edition.)

Conclusion
So are relations closed under the operation of outer join (full, left, or right)?  I guess it depends on how that operator is implemented.  I would say yes if you do consider a relation to still be a relation even with markers for the missing values of the attributes of un-matched tuples.  Or yes if you don't consider a relation to still be a relation even with markers for missing values but can substitute default values for the missing attributes values.  But if you don't consider a relation to still be a relation even with markers for missing values, and your DBMS implements SQL NULLs, and you can't or don't substitute values for the SQL supplied NULLs in the outer join, then no, relations are not closed under that outer join.  Two relations went in, but a relation did not come out.


Software for backing up SQL Server databases

In my opinion, the best product for performing SQL backups is SQL Server itself.  When space permits, I backup to local disk (full + differential, tx log backups every 10 minutes).  These files can then be backed up by your third party software (to tape, NAS, etc.).

If I need to do an emergency restore (assuming a non-drive related issue), I already have the files local to the server, no need to mess around with restoring from tape or copying across a network.

One client of mine had used a third party software to backup files directly to tape (a proprietary format).  Two years later they needed to do a restore (audit).  They had a great deal of difficulty performing the restore because in the meantime they had upgraded the OS and the backup software, which no longer was compatible with the tapes they had from before.


Inserting records into Mongodb using Pymongo is using up my RAM incrementally and is eventually exhausting all memory

You can set the WiredTiger cache size, see hardware considerations.

Other than this, run MongoDB on containers (Kubernetes, Docker, rkt), where you can easily control resource usage. If you are using systemd for running MongoDB, you can also set resource usage.


MYSQL usually comes with MyISAM/INNODB databases.

For MyISAM the files are:

.MYD for data files
.MYI for index files
.frm for table format files

InnoDB uses a single container file for the data and .frm for the definition and .ibd for the indexes.

Are you trying to load data from SQL Server .MDF into MySQL DB? In simple words are you tring to migrate from SQL server to MYSql?

Regards.


differences (if any) between Select count () from table vs select count (*) from table

According to Sybase documentation:

COUNT(*) and COUNT() are semantically equivalent.


I don't know if that applies to Amazon RDS, but a possible reason for getting an empty list of queries with pgtop is when using a version older than 3.7.0 (released in August 2013) against a 9.2 or newer PostgreSQL instance.

I just noticed this with the ptop 3.6.2 package that currently ships with Ubuntu 12.04 LTS. I guess this may be also not fixed in other distributions.

The empty list problem occurs with older versions of pgtop because they expect the postgres process ID in  pg_stat_statements.procpid, and this column was renamed to pg_stat_statements.pid in PG 9.2.

This is combined with pgtop being unhelpful by not reporting any error when its SQL command fails. Instead it just displays a blank list of processes. The problematic query can be spotted in the server's log.


How different clauses treat Null/Unknow in SQL Server 2012?

There are subtle differences on how UNKNOWN and NULL are treated in the various clauses.

Three clauses where a boolean expression can appear are the WHERE, HAVING and FROM (in the ON sub-clause). In all three, the UNKNOWN is treated the same way.

WHERE <boolean expression>:

a row "passes" the <boolean expression> and is returned if the expression evaluates to TRUE. If the expression evaluates to FALSE or UNKNOWN, the row does not pass and is removed from further evaluation of the query.
HAVING <boolean expression>:

The rows examined in the HAVING clause are those created by the aggregation of the previous GROUP BY clause. Similar to above a row (created by the aggregation) "passes" the <boolean expression> and is returned if the expression evaluates to TRUE. If the expression evaluates to FALSE or UNKNOWN, the row does not pass and is removed from further evaluation of the query.
a JOIN b ON <boolean expression>:

Every row from a is checked against every row from b. If the expression is TRUE, then the check passes and the combination of the two rows is valid for further processing of the query. If FALSE or UNKNOWN, then the combination is rejected.
There is a slight change when the join is an outer join (LEFT, RIGHT or FULL) but not regarding how UNKNOWN is treated. What happens here is that if a row from the left table (in a LEFT join) matches no row from the right table (i.e. all its checks evaluate to FALSE or UNKNOWN), then this row is still forwarded and the missing values for the b columns are filled with nulls.)

The situation is different in the constraints, there is a more "relaxed" treatment for UNKNOWN. I guess this has to do with the decision of allowing nulls in the first place. A null value will result in an UNKNOWN result in most constraints, so it makes sense to allow those rows to be inserted. Otherwise, the effect would be the same as declaring the columns as NOT NULL.

CHECK constraint: CHECK <expression>

a row is allowed to be inserted (or updated) if the expression evaluates to TRUE or to UNKNOWN. It is rejected only if the expression evaluates to FALSE.
FOREIGN KEY constraint, with nullable columns.

similar to the check constraints, if a row has a null in one of the columns that participate in a foreign key constraint, the verification of the FOREIGN KEY constraint is skipped and the row is allowed to be inserted (or updated.) One can think of a foreign key constraint in table a:

FOREIGN KEY (column) REFERENCES b (column)

to be equivalent to:

CHECK (column IN (SELECT column IN b))

So, if a.column is NULL, the expression is evaluated to UNKNOWN and thus the row is allowed.
UNIQUE constraints

Here SQL-Server deviates from the standard which state that if a row with a unique constraint contains NULL, then the unique check is skipped and the row is allowed.
SQL-Server treats nulls as if they were regular values, when it comes to unique constraints and allows only one NULL value, in such columns.

NULL values have also special treatment in some clauses:

GROUP BY:

If a grouping column contains null values, all null values are considered equal, and they are put into a single group (the same thing happens in a SELECT DISTINCT query.)

Aggregate functions (MIN(), MAX(), SUM(), AVG() and others) ignore nulls with the exception of COUNT() which can do a lot of different things:

COUNT(*) and COUNT(constant) will count all rows.
COUNT(column) and COUNT(expression) will ignore nulls as the other aggregate functions and count only rows where the column or expression is not null.
COUNT(DISTINCT column) and COUNT(DISTINCT expression) will also ignore nulls and count only the distinct non-null columns or expressions.

So the following 4 queries may return 4 different results if there are nulls in the column:

   SELECT COUNT(*) FROM t ;
   SELECT COUNT(c) FROM t ;
   SELECT COUNT(DISTINCT c) FROM t ;
   SELECT COUNT(*) FROM (SELECT DISTINCT c FROM t) AS x ;

ORDER BY:

In comparisons between them, NULL values are treated as equal.
When comparing them to non-null values, null values are treated as the lowest possible value, lower than any non-null value. As a result, they appear first when the order is ASC and last when the order is DESC.
(There is a NULLS LAST and NULLS FIRST attribute defined in the latest standard but SQL Server has not implemented it yet.)


Unexpected extremely long query time (~5 minutes using nested WHEN-INs)

First step: get rid of subqueries.  Convert them to proper joins, or co-related subqueries.


Many to Many Link Table Return full chain from one record

Just to follow up with a solution, with thanks to Akina in the comments.

I had to convert the above example to a proper table, and use the following select:

WITH Test (ID, Link1, Link2) AS
(SELECT O.ID, R.Link1, R.Link2
FROM TestObject AS O
LEFT OUTER JOIN TestRelationships AS R
ON R.Link1 = O.ID
WHERE O.ID NOT IN (SELECT Link2 FROM TestRelationships)
 AND O.Code = 'TEST1111'
UNION ALL
SELECT T.ID, R1.Link1, R1.Link2
FROM TestRelationships AS R1
INNER JOIN Test AS T
ON T.Link2 = R1.Link1)
SELECT * FROM Test


How do I compare all tables with nonzero rowcounts (in an archive db) with tables of the same name in the line of business db?

;with t1 as (
SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   ARC_DB.sys.tables ta
   INNER JOIN ARC_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN ARC_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
   and pa.rows > 0
GROUP  BY sc.NAME,
      ta.NAME
)
select t1.tablename, t1.rowcnt, t2.rowcnt, t1.rowcnt-t2.rowcnt as diff
from t1
join (
SELECT sc.NAME + '.' + ta.NAME tablename,
   Sum(pa.rows)            rowcnt
FROM   LOB_DB.sys.tables ta
   INNER JOIN LOB_DB.sys.partitions pa
           ON pa.object_id = ta.object_id
   INNER JOIN LOB_DB.sys.schemas sc
           ON ta.schema_id = sc.schema_id
WHERE  ta.is_ms_shipped = 0
   AND pa.index_id IN ( 1, 0 )
GROUP  BY sc.NAME,
      ta.NAME
) as t2 on t2.tablename = t1.tablename


How to get 0 as array_length() result when there are no elements

should return 0 rather than nothing (NULL), right?

It's up for debate what this should return. But the way Postgres defines it, the result for any dimension that does not exist is NULL.
COALESCE is the tool to fix your query, but the better solution is not to break it to begin with.
Alternative in Postgres 9.4
Postgres 9.4 or later provides a separate function cardinality() that per documentation:

returns the total number of elements in the array, or 0 if the array is empty

Bold emphasis mine.
Seems like you deal with 1-dimensional arrays exclusively and just want to use that.
test=# SELECT cardinality('{}'::int[]);
 cardinality
-------------
           0

However, this is not the proper fix for your problem, yet. The whole array might be NULL, which returns NULL either way and still sorts on top.
Fix query with NULLS LAST
SELECT id, array_length(users_who_like_ids, 1) AS ct
FROM   queryables
ORDER  BY 2 DESC NULLS LAST;

Why do NULL values come first when ordering DESC in a PostgreSQL query?

This always deals with NULL values properly. You might still want to use cardinality() to sort empty arrays before NULL. But be aware of the difference when dealing with multi-dimensional arrays.


MySQL subquery with WHERE filters not working as expected

Seems like I had to use a larger subquery and join it with the main query. Works fine this way.  Not sure why it did not work the above way though...
SELECT tags_x.tag_id, tags.tag_name, questions.question_id,X.num
FROM tags_x
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id
LEFT JOIN questions ON questions.question_id = tags_x.question_id
LEFT JOIN
(SELECT tags_x.tag_id, count(tags_x.tagsx_id) AS num
FROM tags_x
LEFT JOIN questions ON tags_x.question_id = questions.question_id
LEFT JOIN tags ON tags.tag_id = tags_x.tag_id
WHERE questions.suspended =0 AND questions.offline = 0 AND tags.tag_name IS NOT NULL
GROUP BY tags.tag_id) AS X ON X.tag_id=tags.tag_id
WHERE questions.question IS NOT NULL
ORDER BY `questions`.`question_id` ASC


Are Cataloge and relation cache in PostgreSQL per connection or global to the server?

Those caches are per connection.  They are not held in shared memory.


Add [Is Primary Key Column] to this query

Query that adds "is_primary_key" field for each column

The sys.key_constraints and sys.index_columns catalog views will identify the PK columns. You can JOIN them together and then use that set as a derived table to LEFT JOIN to your main query which will allow for not filtering out columns that are not part of a PK.

Also, you want to use [user_type_id] instead of [system_type_id] to avoid a Cartesian product. Most of the time there won't be a difference between these two fields. But if you have User Defined Data Types (UDDTs), or use the sysname datatype (which is an alias to NVARCHAR(128)), then the [system_type_id] value will be repeated in the sys.types catalog view.

The following query lists all fields in all tables, adding a computed field to denote if the column is part of the PK or not. It handles composite PKs as well.

SELECT tbl.[name] AS [TableName],
       col.[name] AS [ColumnName],
       ty.[name] AS [DataType],
       col.[max_length] AS [MaxLength],
       col.[is_nullable] AS [Nullable],
       col.[is_identity] AS [Identity],
       CONVERT(BIT, IIF(pkcol.index_id IS NOT NULL, 1, 0)) AS [PrimaryKey]
FROM       sys.tables tbl
INNER JOIN sys.columns col
        ON col.[object_id] = tbl.[object_id]
INNER JOIN sys.types ty
        ON ty.[user_type_id] = col.[user_type_id] -- do not use system_type_id
LEFT JOIN (
           SELECT ind.[object_id] AS [table_object_id], ind.[index_id], ind.[column_id]
           FROM   sys.index_columns ind
           INNER JOIN sys.key_constraints pks
                   ON pks.[parent_object_id] = ind.[object_id]
                  AND pks.[unique_index_id] = ind.[index_id]
           WHERE  pks.[type] = 'PK'
          ) pkcol
       ON pkcol.[table_object_id] = tbl.[object_id]
      AND pkcol.[column_id] = col.[column_id]
ORDER BY tbl.[name], col.[name];

Info on max_length column

The max_length column in sys.columns is the maximum number of bytes that the column can take up per row. In the case of fixed-length fields such as INT, DATETIME, etc, those fields always take up their maximum amount of space, unless you are using the SPARSE option (per column setting) or have enabled Data Compression (per index setting).

For variable-length fields such as VARCHAR, NVARCHAR, XML, etc, that value is the maximum number of bytes it can take up. A value of -1 indicates a value of approx 2 GB (Int32.MaxValue) which is used by the MAX (VARCHAR, NVARCHAR, and VARBINARY) and XML types.

The Unicode string types (NCHAR and NVARCHAR) that are not declared as MAX will display a max_length of 2 * declared_max since the (safe) assumption is that they use 2 bytes per "character". This is not always the case since Supplementary Characters are actually 4 bytes per "character". But, this is why sysname, being an alias for NVARCHAR(128), has a max_length of 256. Similarly, the less frequently used Double Byte Character Set (DBCS) collations that allow for mapping more than 256 characters in an 8-bit VARCHAR / CHAR field will store characters in either 1 or 2 bytes, depending on the character. Meaning, for VARCHAR data using a DBCS collation, and for NVARCHAR data, declaring a column or variable as VARCHAR(x) or NVARCHAR(x) does not guarantee x characters are storable: you can only fit x characters if all of those characters are of the standard length for that type (i.e. 1 byte for VARCHAR, 2 bytes for NVARCHAR). So, an NVARCHAR(3) field is given 6 bytes maximum to use. That can fit: 3 regular two-bytes Unicode characters, or 1 regular two-byte character and 1 Supplementary Character at 4 bytes (which is only 2 characters, not 3). It cannot fit 2 Supplementary Characters as that would require 8 bytes.

The deprecated TEXT, NTEXT, and IMAGE types that nobody is using anymore (that's sarcasm) show a max_length of 16, presumably being the size of the pointer left on the data page that points to the LOB page holding the data.


Why MySQL performs so poor on SHOW PROCEDURE STATUS?

The "meta" commands (SHOW...) are generally not designed for performance.  The presumption is that you rarely, if ever, need to run them.  And when you do, it is more of a "one-time" operation.
Please migrate from MyISAM to InnoDB.  (This will probably have no effect on the Question at hand, but is important for many other reasons.)
Before 8.0, the information for many of the SHOW commands was scattered around various files.  For example, each table had a .frm file containing, effectively, the CREATE TABLE information.  "File open" took time, leading to slowness.
8.0 moved all that info into InnoDB tables so, in theory, it should be straightforward to fetch all the data more efficiently.  However, the implementation seems to involve layers of VIEWs and inadequate indexing.
In any case, 250K of anything takes time.  I could have mentioned the leading wildcard (%) on the LIKE because it prevents the use of an index.  However, I am not convinced that there is a relevant index.


First of all: stored procedures in SQL Server are NOT "pre-compiled" or anything.

Just like an ad-hoc SQL query, a stored procedure is analyzed when it's first used, an execution plan is determined and cached.

When the stored procedure is executed again, then that pre-existing execution plan is reused. Same applies to a properly parametrized SQL ad-hoc query.

The point is: as long as the actual SQL statement is IDENTICAL (down to the last comma or space), then a possibly pre-existing, cached execution plan is reused and the whole step of determining an execution plan can be skipped. If the SQL statement text varies - even by a single space - the whole process of analyzing and coming up with an execution plan for that query is run again.

I don't know about how Java handles it's prepared statements, but in C# using ADO.NET, when you run a query something like

SELECT * FROM user_master WHERE user_name = @userName

and you supply a value for @userName (e.g. 'vicky.thakor'), then the SQL Server will NOT received what you seem to think it will receive - the parameter is NOT replaced in the query text - instead, SQL Server will execute this as

EXEC sp_executesql @stmt, N'@userName', @userName = 'vicki.thakor'

SQL Server will receive a parametrized query and a list of parameters and their values.

Therefore, if you call this same query again, with a different value for @userName, the actual SQL statement text is identical and the cached execution plan is reused - only the value of the parameter has changed.


Add user to Server Role in a transaction

Of course, just after posting, I found it myself:

USE [master];
GRANT ADMINISTER BULK OPERATIONS TO [user]

It's that easy.