What sort of exec requests don't have a sql handle? (sys.dm_exec_requests)

The issue with your current query is that you are only looking for queries that could be running at that very moment. To get a good full picture of your server state, you could try to run SQL Server Profiler or check for connections with a built-in stored procedure likesp_who. These approaches are more complicated and time-consuming than using a stored procedure that's already been developed and tested by other DBAs.

I suggest that you install Adam Machanic's free stored procedure, called sp_whoisactive. The most recent version, should be sufficient to help you dig into activity on your server. This procedure will also give you more detailed information than writing a query referencing one or two DMVs. From the documentation, sp_whoisactive uses 15 DMVs.


How to deal with multiple error columns in data flow

Figured it out shortly after posting.

Although SSIS doesn't allow you to delete the error columns in the error output of the first insert, it does allow you to change the names. I was able to modify the names of these columns and then the Union does not throw any errors as the names are not in conflict anymore.


How does Postgres compute the row count estimate during query planning

There is no better way to estimate the row count for the generic plan.
If you chose to go by the frequencies of a most common value (MCV), there would be several problems:

Which MCV do you choose?

The estimate would be better assuming that both parameters are MCV, but that is an unfounded assumption.  If both were rare values, the estimate would be even farther off the mark than the current estimate.

Choosing the average is the best we can do: it is something of a middle ground.
Your case is so far off the mark because one of the values is extremely rare, which lowers the custom plan estimate, but since there are few distinct values and one of them is very frequent, the selectivity is bad at average, and the generic plan estimate is far too high.
You have two choices:

Use dynamic SQL so that you always get a custom plan.

Change the query to read
WHERE filter_1 || '' = ?

so that it cannot use the index.


What's the best design for a logical structure that contains different properties of various types for different entity categories? (MySQL or NoSQL)

I recommend you to read about the dangers of the Entity-Attribute-Value pattern on this presentation by @Bill Karwin.

One of the solutions is storing NoSQL-like data in a serialized BLOB, if you do not need to read and write individual properties. That is, storing a key (product) as the primary key, and the variable property-value pairs all together in a single blob. This is a non-normalized structure, leaving the responsibility of the constraints to the application layer, but it works great for certain patterns (the same that usually work well with NoSQL solutions). Even if you need indexing, you can create inverted indexes on a separate table as needed.

This and other solutions can be seen on this presentation in the latest MySQL Conference, including when you should search for alternative database engines.


Q1

Is it possible that T2 reads,updates and commits the row in question
BEFORE T1 gets to read it ?

A1
Yes, as noted by Paul White here:

The repeatable read isolation level provides a guarantee that data
will not change for the life of the transaction once it has been read
for the first time.

Also due to lock escalation the query can start with row locks and then escalate to a full table lock. If the update runs while row locks are being taken and the row has not been accessed yet, then the update will not be blocked.
Q2

If that is so,then RR would not have an effect,or as a matter in this scenario no isolation level would have an effect becasue
isolation concerns only rows that they've been ALREADY read,not those
not yet read.True?

A2
Yes,
you could use full table locking with a TABLOCK hint to make sure that the entire table is locked in RR but that reduces concurrency.
SELECT * FROM dbo.mytable WITH(TABLOCK) WHERE city='London';

Of course, if you are selecting a lot of data anyway the table lock escalation might happen nonetheless.
This is commonly around the 5000 row mark but can be higher/lower.
Q3

Is however SERIALIZIBLE different? Does it take key range locks which means it locks the rows BEFORE it has read them? So would
SERIALIZIBLE in the code example above take any key ranges locks that
would prevent the scenario of T2 getting to the row and updating
before it is read?

A3
Yes, it takes range locks and the considerations for equality range locks are noted by Sunil Agarwal here:

Equality Predicate

If the key value exists, then the range lock is only taken if the        index is non-unique. In the non-unique index case, the ‘range’ lock
is taken on the requested key and on the ‘next’ key. If the ‘next’
key does not exist, then a range lock is taken on the ‘infinity’
value. If the index is unique then a regular S lock on the key.
If the key does not exist, then the ‘range’ lock is taken on the    ‘next’ key both for unique and non-unique index. If the ‘next’ key
does not exist, then a range lock is taken on the ‘infinity’ value.

In your case the key exists, and the index is probably non-unique so you would have a range lock up to the next value or the infinity value.
However I would be wary of using the serializable isolation level, it can introduces deadlocks and other concurrency issues.


sql server management studio enable encryption

SSMS GUI expects you to Create your server certificate before you mange database encryption.

Ref: CREATE CERTIFICATE (Transact-SQL)

Once you create the server certificate you you will be able to chose from the drop down box.

CREATE CERTIFICATE testencryptioncert
   ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
   WITH SUBJECT = 'Testing Encryption',
   EXPIRY_DATE = '20201031';
GO

Now the above certificate will show up in the drop down list of the GUI.


Sql Server 2012 Restoring Master and Migrating a server

I dont see a need (nor I do it when migrating server) to do a restore of master (or system databases) on the destination server - unless you are trying to achieve something that you have not mentioned in your question.

I find it easy to use sp_help_revlogin to just move logins from one server to another.

so your migration steps would be like below: Server A = Old server ; Server B is NEW server.

script out logins from server A.
script out jobs from server A.
Backup and restore databases from Server A to Server B.
Recreate logins scripted out from server A to Server B.
Sync up orphan users (if any).
Recreate jobs scripted out from server A to server B. Make sure to move packages if any as well.
change compatibility level (if moving from lower to higher version).
run DBCC UPDATEUSAGE
update stats on all tables with fullscan
Run DBCC CHECKDB ('<db_name_goes_here>' ) WITH ALL_ERRORMSGS,NO_INFOMSGS, DATA_PURITY

You can refer to my script at https://dba.stackexchange.com/a/41179/8783 (make sure you do a backup restore and not detach/attach method - now learned it !!).


Yes, these values can be changed (within limits).

1. max_connections

You can change this value via parameter groups. The maximum allowed value is 100 000. Note that you are also constrained by available memory (1 GB in your case).

2. max_questions

This value is stored per user in the mysql database table user. The default value is 0, which means "unlimited". The column type is INT, so the maximum value is 4294967295.

You can check the current values like this:

SELECT `User`,`max_questions` FROM `mysql`.`user`;


How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships)

Snowflake currently supports recursive CTEs.

I just build an sql to parse an account tree to consolidate amounts at the branch level.

From the source account tree data, main is used to build each branch & immediate sub branch/leaves combination

with main as (
  select a.scode as src , b.scode as tgt
  from accttree a
    join accttree b on (a.totalinto=b.scode)
), tree as (
  select src, NULL as bridge, tgt
  from main
  --where hprop = 15365
  union all
  select m.src, m.tgt as bridge, t.tgt as tgt
  from main m
    join tree t on (m.tgt = t.src)
)
--Tree gives the branch and leaf relation for the entire set of branches
Select *
from tree


When should I NOT set the Maximum Server Memory option in SQL Server?

You should always set your max memory away from default and leave some room for OS (see Jonathan's post of how much Memory to leave based on the amount of RAM installed).

Jonathan Kehayias has blogged about : How much memory does my SQL Server actually need?

  reserve 1 GB of RAM for the OS, 1 GB for each 4 GB of RAM installed from 4–16 GB, and then 1 GB for every 8 GB RAM installed above 16 GB RAM.

You can also refer to my answer here for more details.

  would it be best to leave the default setting

NO, dont leave it as default as problems like OS unresponsiveness, Working Set trimming as well as other applications running on the server will be affected adversely. It will affect your backups as well.

Note that Memory Manager for SQL Server 2012 and up has changed.


Mysql 8 ST_GeomFromText giving error Latitude out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]

That's because on MySQL 8, they did the unthinkable and implemented the stupid method of doing geography coordinates (like MySQL). This broke backwards compatability.

When you use Geometry on MySQL (any SRID) except 4326 you use (long,lat). That's (x,y) on a Cartesian system.
When you use Geography on MySQL (SRID = 4326) you use (lat, long). That's (y,x) typically. This is done because English speakers tend to say "latitude and longitude" rather than "longitude, latitude"

Amusingly Oracle which owns MySQL does not do this on Oracle Spatial. Oracle Spatial and PostGIS are consistently (long,lat)`. To resolve this switch the order, not

ST_GeomFromText('POINT(102.1893310546875 3.880696482497261)', 4326)

But, this,

ST_GeomFromText('POINT(3.880696482497261 102.1893310546875)', 4326)

In MySQL 5.x there was no real geographical coordinate system.

Side note

You shouldn't be using WKT for this if you have the points you can just use Point(), and ST_SRID(pt,SRID)

ST_SRID(Point(3.880696482497261, 102.1893310546875), 4326)

More information

For more information see also,

Does MySQL store point datatypes as LAT LNG or LNG LAT?


Stored procedure parameters using tsql functions?

Why not do the CAST when you SET @foo instead?

SET @foo = CAST(Substring('11test',1,2) as int)

You could even use a second variable and do this:

SET @foo1 = '11test'
SET @foo2 = CAST(Substring(@foo1,1,2) as int)

and then just call the proc:

EXEC testMe @test = @foo2


Why is column default value not portable through different RDBMS?

An ORM that is intended to be truly storage-layer agnostic will generally assume the absolute minimum from the data layer (tables, rows, columns of standard types, indexes, PKs, FKs, and that is about it) and will implement everything else like defaults and more complex constraints themselves.

While every relational database I could mention (mssql, oracle, prostgres, sqlite, mysql, ...) supports default values as defined in SQL-92, things vary a lot more once you consider using document oriented DBs and other less traditional arrangements for your storage layer (they might not support them and if they do the operating semantics may not be quite the same).


In a database trigger on event create - How do I reference the created object?

If you're creating trigger in a schema then schema name is the object owner/table owner or you can use sys.dictionary_obj_owner and for table name sys.dictionary_obj_name.

    SQL> SET SERVEROUTPUT ON
    SQL> SHOW USER
    USER is "OT"
    SQL> CREATE TABLE test01 (sno number);

    Table created.

SQL> SELECT dbms_metadata.get_ddl('TABLE','TEST01','OT') FROM dual;   -- before trigger created

DBMS_METADATA.GET_DDL('TABLE','TEST01','OT')
        --------------------------------------------------------------------------------

     CREATE TABLE "OT"."TEST01"
       (    "SNO" NUMBER
       ) SEGMENT CREATION DEFERRED
     PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255          -- default INITRANS is 1
     NOCOMPRESS LOGGING
     TABLESPACE "USERS"

    CREATE OR REPLACE TRIGGER
      TRG_CREATE_TABLE_INITRANS
      AFTER CREATE ON SCHEMA

    BEGIN
       IF SYS.DICTIONARY_OBJ_TYPE = 'TABLE' THEN
          EXECUTE IMMEDIATE 'ALTER TABLE '||sys.dictionary_obj_owner||'.'||sys.dictionary_obj_name||' INITRANS 10';

       END IF;
    END;

    SQL> drop table test01 purge;
    Table dropped.

    SQL> CREATE TABLE test01 (sno number);
    Table created.

    SQL> SELECT dbms_metadata.get_ddl('TABLE','TEST01','OT') FROM dual;

     DBMS_METADATA.GET_DDL('TABLE','TEST01','OT')
        --------------------------------------------------------------------------------
    CREATE TABLE "OT"."TEST01"
     (    "SNO" NUMBER
     ) SEGMENT CREATION DEFERRED
    PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255    -- INITRANS 10
    NOCOMPRESS LOGGING
    TABLESPACE "USERS"


AlwaysOn AG: Current Host Server - Owner Node - Primary Instance

Should the "Current Host Server" the same as the Owner Node of the AlwaysOn AG?

That's the node that is currently hosting the  core cluster resources, it isn't the same and is just another role. Different roles can be owned by different nodes. In this case, they do not need to be the same - and generally most people don't care who owns the core cluster resources in a WSFC that only has SQL Server. It does make a difference in certain situations but for the vast majority, it makes no real difference.

  Imagine if I want to maintain a host. Shall I first failover the AG (via SSMS or with ALTER AVAILABILITY GROUP...) to the second node/another node and at second a failover of the WSFC-Current Host?

If you're going to be maintenance to a host, I'd move all roles (drain) off of that node. So, yes, everything should be moved from it.


I believe this is what you need:

CREATE Trigger upd_pt
BEFORE UPDATE ON user
FOR EACH ROW
SET NEW.point = NEW.votes + NEW.paymentAmt

This will set points before the user record is updated, so that total will be stored.

Based on code from the MySQL 5.7 Reference Manual.

NOTE: For best results, you probably also want to have this as an INSERT trigger, and may want to adjust if votes or paymentAmt can be NULL.


How to setup daily SQL server export to csv text file?

Yes, no problem.  Use BCP (which is a command line tool) to export the data.  Once you have the parameters the way you like, schedule this as a SQL Agent job.


combined contents of 2 columns can't contain duplicates

The only way that I can think of to do this (without using triggers) is to use Firebird. Doesn't really answer the question (MySQL), but is interesting functionality to explore - see the fiddle here!

CREATE TABLE test
(
  column_A INTEGER NOT NULL,
  column_B INTEGER NOT NULL,
  CONSTRAINT a_uq UNIQUE (column_A),
  CONSTRAINT b_uq UNIQUE (column_B),
  CONSTRAINT a_ne_b_ck CHECK (column_A != column_B),
  CONSTRAINT a_ni_b_ck CHECK (column_A NOT IN (SELECT column_B FROM test)),
  CONSTRAINT b_ni_a_ck CHECK (column_B NOT IN (SELECT column_A FROM test))
);

It does make the table declaration more complex (con) but with the massive pro of not having to maintain triggers oneself. It would appear from the error messages that Firebird uses triggers in the background - better that the server maintain them than me!

--
-- The next two INSERTs work (as one would expect)
--
INSERT INTO test VALUES (2, 4);
INSERT INTO test VALUES (3, 5);

Then, to test (standard functionality first):

--
-- This doesn't work - breaches UNIQUE constraint on column_A
-- Look at the quality of the error message!
--
INSERT INTO test VALUES (2, 7);  -- 2 is a dup in column_A

fails with the error:

violation of PRIMARY or UNIQUE KEY constraint "A_UQ" on
table "TEST" Problematic key value is ("COLUMN_A" = 2)

Look at the quality of that error message - even telling you the offending value!

Then we try (again standard CHECK constraint stuff):

--
-- This doesn't work - breaches the column_A != column_B CHECK. Easy, peasy!
-- All RDBMS do this - even MySQL now since version 8!
--
INSERT INTO test VALUES (4, 4);

Error message:

Operation violates CHECK constraint A_NE_B_CK on view or
table TEST At trigger 'CHECK_1'

Now, we test the SQL within the CHECK constraint!

--
-- This is where it gets interesting! Notice that it **_won't_** allow a value
-- that's in column_B be inserted into column_A! Pretty cool!
--
INSERT INTO test VALUES (5, 9);

gives the error:

Operation violates CHECK constraint A_NI_B_CK on view or table
TEST At trigger 'CHECK_3'

The SQL in the CHECK constraint works the other way round also - see the fiddle. Note the part of the error message where it says: table TEST At trigger 'CHECK_3' - triggers at work in the background!

This is really powerful stuff! It's such a shame that Firebird isn't more popular/widespread - it really deserves the no. 2 spot in F/LOSS database ecosphere!

Incredibly, MySQL only recently introduced CHECK constraints (only at least 25 years after all its other major - and not so major - competitors). But, now it appears to be making serious efforts to catch up! But nobody else (AFAIK) has SQL in CHECK constraints, not even the mighty Oracle, MS SQL Server or PostgreSQL (note to self - suggest this on the lists!).


update customer_entity
set website_id = 4
where website_id = 3
AND email in
(
    select tt.email
    from
    (
        SELECT email
        FROM `customer_entity`
        where website_id in (3,4)
        group by email
        having count(email) <= 1
    ) as tt
)


Designing a friendships database structure: Should I use a multivalued column?

Managing an individual piece of information
Assuming that, in your business domain,

a User can have zero-one-or-many Friends;
a Friend must first be registered as a User; and
you will search for, and/or add, and/or remove, and/or modify, single values of a Friend List;

then each specific datum gathered in the Friendlist_IDs multivalued column represents a separate piece of information that carries a very exact meaning. Therefore, said column

entails a proper group of explicit constraints, and
its values have the potential of being manipulated individually by means of several relational operations (or combinations thereof).

Short answer
Consequently, you should retain each of the Friendlist_IDs values in (a) a column that accepts exclusively one sole value per row in (b) a table that represents the conceptual-level association type that can take place between Users, i.e., a Friendship —as I will exemplify in the following sections—.
In this way, you will be able to handle (i) said table as a mathematical relation and (ii) said column as a mathematical relation attribute —as much as MySQL and its SQL dialect permit, of course—.
Why?
Because the relational model of data, created by Dr. E. F. Codd, demands having tables that are composed of columns that hold exactly one value of the applicable domain or type per row; hence, declaring a table with a column that can contain more than one value of the domain or type in question (1) does not represent a mathematical relation and (2) would not permit obtaining the advantages proposed in the aforementioned theoretical framework.
Modeling Friendships between Users: Defining the business environment rules first
I highly recommend starting to shape a database delimiting —before anything else— the corresponding conceptual schema by virtue of the definition of the relevant business rules that, among other factors, must describe the types of interrelationships that exist between the distinct aspects of interest, i.e., the applicable entity types and their properties; e.g.:

A User is primarily identified by his or her UserId
A User is alternately identified by the combination of his or her FirstName, LastName, Gender, and Birthdate
A User is alternately identified by his or her Username
A User is the Requester of zero-one-or-many Friendships
A User is the Addressee of zero-one-or-many Friendships
A Friendship is primarily identified by the combination of its RequesterId and its AddresseeId

Expository IDEF1X diagram
In this manner, I was able to derive the IDEF1X1 diagram shown in Figure 1, which integrates most of the rules previously formulated:

As depicted, Requester and Addressee are denotations that express the Roles carried out by the specific Users that take part in a given Friendship.
That being so, the Friendship entity type portrays an association type of many-to-many (M:N) cardinality ratio that can involve different ocurrences of the same entity type, i.e., User. As such, it is an example of the classic construct known as “Bill of Materials” or “Parts Explosion”.

1 Integration Definition for Information Modeling (IDEF1X) is a highly recommendable technique that was established as a standard in December 1993 by the U.S. National Institute of Standards and Technology (NIST). It is solidly based on (a) the early theoretical material authored by the sole originator of the relational model, i.e., Dr. E. F. Codd; on (b) the entity-relationship view of data, developed by Dr. P. P. Chen; and also on (c) the Logical Database Design Technique, created by Robert G. Brown.

Illustrative SQL-DDL logical design
Then, from the IDEF1X diagram presented above, declaring a DDL arrangement like the one that follows is much more “natural”:
-- You should determine which are the most fitting
-- data types and sizes for all the table columns
-- depending on your business context characteristics.

-- At the physical level, you should make accurate tests
-- to define the mostconvenient INDEX strategies based on
-- the pertinent query tendencies.

-- As one would expect, you are free to make use of
-- your preferred (or required) naming conventions.

CREATE TABLE UserProfile ( -- Represents an independent entity type.
    UserId          INT      NOT NULL,
    FirstName       CHAR(30) NOT NULL,
    LastName        CHAR(30) NOT NULL,
    BirthDate       DATE     NOT NULL,
    GenderCode      CHAR(3)  NOT NULL,
    Username        CHAR(20) NOT NULL,
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
    CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
        FirstName,
        LastName,
        GenderCode,
        BirthDate
    ),
    CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- Single-column ALTERNATE KEY.
);

CREATE TABLE Friendship ( -- Stands for an associative entity type.
    RequesterId     INT      NOT NULL,
    AddresseeId     INT      NOT NULL, -- Fixed with a well-delimited data type.
    CreatedDateTime DATETIME NOT NULL,
    --
    CONSTRAINT Friendship_PK            PRIMARY KEY (RequesterId, AddresseeId), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipToRequester_FK FOREIGN KEY (RequesterId)
        REFERENCES UserProfile (UserId),
    CONSTRAINT FriendshipToAddressee_FK FOREIGN KEY (AddresseeId)
        REFERENCES UserProfile (UserId)
);

In this fashion:

each base table represents an individual entity type;
each column stands for a sole property of the respective entity type;
a specific data typea is fixed for each column in order to guarantee that all the values it contains belong to a particular and well defined set, be it INT, DATETIME, CHAR, etc.; and
multiple constraintsb are configured (declaratively) in order to ensure that the assertions in the form of rows retained in all the tables meet the business rules determined at the conceptual schema.

Advantages of a single-valued column
As demonstrated, you can, e.g.:

Take advantage of referential integrity enforced by the database management system (DBMS for brevity) for the Friendship.AddresseeId column, since constraining it as a FOREIGN KEY (FK for brevity) that makes a reference to the UserProfile.UserId column guarantees that every value points to an existing row.

Create a composite PRIMARY KEY (PK) made up of the combination of columns (Friendship.RequesterId, Friendship.AddresseeId), helping to elegantly distinguish all the INSERTed rows and, naturally, protect their uniqueness.
Of course, this means that the attachment of an extra column for system-assigned surrogate values (e.g., one set up with the IDENTITY property in Microsoft SQL Server or with the AUTO_INCREMENT attribute in MySQL) and the assisting INDEX is entirely superfluous.

Restrict the retained values in Friendship.AddresseeId to a precise data typec (which should match, e.g., the one established for UserProfile.UserId, in this case INT), letting the DBMS take care of the pertinent automatic validation.
This factor can as well help to (a) utilize of the corresponding built-in type functions and (b) optimize disk space usage.

Optimize data retrieval at the physical level by configuring small and fast subordinate INDEXes for the Friendship.AddresseeId column, as these physical elements can substantialy assist in speeding up the queries that involve said column.
Certainly, you can, e.g., put up a single-column INDEX for Friendship.AddresseeId alone, a multi-column one that encompasses Friendship.RequesterId and Friendship.AddresseeId , or both.

Avoid the unnecessary complexity introduced by “searching for” distinct values that are collected together inside the same column (very likely duplicated, wrongly typed, etc.), a course of action that would eventually slow down the functioning of your system, because you would have to resort to resource- and time-consuming non-relational methods to accomplish said task.

So, there are multiple reasons that call for analyzing the relevant business environment carefully in order to mark out the typed of each table column with accuracy.
As expounded, the role played by the database designer is paramount to make the best use of (1) the logical-level benefits offered by the relational model and (2) the physical mechanisms provided by the DBMS of choice.

a, b, c, d Evidently, when working with SQL platforms (e.g., Firebird and PostgreSQL) that support DOMAIN creation (a distinctive relational feature), you can declare columns that only accept values that belong to their respective (fittingly constrained and sometimes shared) DOMAINs.

One or more application programs sharing the database under consideration
When you have to employ arrays in the code of the application program(s) accesing the database, you simply need to retrieve the relevant data set(s) in full and then “bind” it (them) to the concerning code structure or execute the associated app(s) process(es) that should take place.
Further benefits of single-valued columns: Database structure extensions are much more easy
Another advantage of holding the AddresseeId data point in its reserved and properly typed column is that it considerably facilitates extending the database structure, as I will exemplify below.
Scenario progression: Incorporating the Friendship Status concept
Since Friendships can evolve over time you might have to keep track of such a phenomenon, thus you would have to (i) expand the conceptual schema and (ii) declare a few more tables in the logical layout. So, let us arrange the next business rules to delineate the new incorporations:

A Friendship holds one-to-many FriendshipStatuses
A FriendshipStatus is primarily identified by the combination of its RequesterId, its AddresseeId and its SpecifiedDateTime
A User specifies zero-one-or-many FriendshipStatuses
A Status classifies zero-one-or-many FriendshipStatuses
A Status is primarily identified by its StatusCode
A Status is alternately identified by its Name

Extended IDEF1X diagram
Successively, the previous IDEF1X diagram can be extended in order to include the new entity types and interrelationship types described above. A diagram depicting the previous elements associated with the new ones is presented in Figure 2:

Logical structure additions
Afterwards, we can lengthen the DDL layout with the following declarations:
--
CREATE TABLE MyStatus ( -- Denotes an independent entity type.
    StatusCode CHAR(1)  NOT NULL,
    Name       CHAR(30) NOT NULL,
    --
    CONSTRAINT MyStatus_PK PRIMARY KEY (StatusCode),
    CONSTRAINT MyStatus_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE FriendshipStatus ( -- Represents an associative entity type.
    RequesterId       INT      NOT NULL,
    AddresseeId       INT      NOT NULL,
    SpecifiedDateTime DATETIME NOT NULL,
    StatusCode        CHAR(1)  NOT NULL,
    SpecifierId       INT      NOT NULL,
    --
    CONSTRAINT FriendshipStatus_PK             PRIMARY KEY (RequesterId, AddresseeId, SpecifiedDateTime), -- Composite PRIMARY KEY.
    CONSTRAINT FriendshipStatusToFriendship_FK FOREIGN KEY (RequesterId, AddresseeId)
        REFERENCES Friendship  (RequesterId, AddresseeId), -- Composite FOREIGN KEY.
    CONSTRAINT FriendshipStatusToMyStatus_FK   FOREIGN KEY (StatusCode)
        REFERENCES MyStatus    (StatusCode),
    CONSTRAINT FriendshipStatusToSpecifier_FK  FOREIGN KEY (SpecifierId)
        REFERENCES UserProfile (UserId)
);

Consequently, every time that the Status of a given Friendship needs to be put up-to-date, the Users would only have to INSERT a new FriendshipStatus row, containing:

the suitable RequesterId and AddresseeId values —taken from the concerning Friendship row—;

the new and meaningful StatusCode value —drawn from MyStatus.StatusCode—;

the exact INSERTion instant, i.e., SpecifiedDateTime —preferably using a server function so that you can retrieve and retain it in a reliable manner—; and

the SpecifierId value that would indicate the respective UserId that entered the new FriendshipStatus into the system —ideally, with the aid of your app(s) facilities—.

To that extent, let us suppose that the MyStatus table encloses the following data —with PK values that are (a) end user-, app programmer- and DBA-friendly and (b) small and fast in terms of bytes at the physical implementation level—:

 +-——————————-+-—————————-+
 | StatusCode | Name      |
 +-——————————-+-—————————-+
 | R          | Requested |
 +------------+-----------+
 | A          | Accepted  |
 +------------+-----------+
 | D          | Declined  |
 +------------+-----------+
 | B          | Bloqued   |
 +------------+-----------+

So, the FriendshipStatus table may hold data like shown below:

 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 | RequesterId | AddresseeId | SpecifiedDateTime       | StatusCode | SpecifierId |
 +-———————————-+-———————————-+-———————————————————————-+-——————————-+-———————————-+
 |        1750 |        1748 | 2016-04-01 16:58:12.000 | R          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-02 09:12:05.000 | A          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-04 10:57:01.000 | B          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-07 07:33:08.000 | R          |        1748 |
 +-------------+-------------+-------------------------+------------+-------------+
 |        1750 |        1748 | 2016-04-08 12:12:09.000 | A          |        1750 |
 +-------------+-------------+-------------------------+------------+-------------+

As you can see, it can be said that the FriendshipStatus table serves the purpose of comprising a time series.

Relevant posts
You might as well be interested in:

This answer in which I suggest a basic method to deal with a common many-to-many relationship between two dissimilar entity types.
The IDEF1X diagram shown in Figure 1 that illustrates this other answer. Pay special attention to the entity types named Marriage and Progeny, because they are two more examples of how to handle the “Parts Explosion Problem”.
This post that presents a brief deliberation about holding different pieces of information within a single column.


SQLite: Leave only n rows for each field

WITH
cte AS ( SELECT author,
                name,
                rating,
                ROW_NUMBER() OVER ( PARTITION BY author
                                    ORDER BY rating DESC ) rn
         FROM ratings )
DELETE
FROM ratings
WHERE EXISTS ( SELECT 1
               FROM cte
               WHERE ratings.author = cte.author
                 AND ratings.name = cte.name
                 AND cte.rn > 2 );

fiddle


Comparison Tool for MySQL data base schemas

This isn't the a very sexy answer but mysqldump & sdiff.

mysqldump -u root -p --no-data -h oldHost databases db1 db2 db3 > /tmp/old.sql
mysqldump -u root -p --no-data -h newHost databases db1 db2 db3 > /tmp/new.sql
sdiff /tmp/old.sql /tmp/new.sql

sdiff will give you a side by side comparison with differences paired w/ a > or < indicator.  I've found this to be useful in the exact scenario you describe.

The example supposes you have the new schema initialized on a different host, but this certainly isn't a requirement.  It could be a different instance (e.g. port) on the same host or the same instance with different database names.


You have to create a symbolic link that points to the new mountpoint:

  If your server runs out of disk space for journal files, the server
  process will exit. By default, mongod creates journal files in a
  sub-directory of dbpath named journal. You may elect to put the
  journal files on another storage device using a filesystem mount or a
  symlink.

  Note

  If you place the journal files on a separate storage device you will
  not be able to use a file system snapshot tool to capture a valid
  snapshot of your data files and journal files.

Updated link about: Journal files


How should I design a re-usable content like table?

I think this is probably a good design that you proposed. Just make sure that the table is indexed properly. Consider that every query to the table will probably filter by content_type so that should probably be the leading column in a multi-field index.


Can I specify a different disk for a specific MySQL table?

Migrating s single MyISAM table to another disk is only possible in Linux versions, not Windows, of MySQL with the DATA DIRECTORY and INDEX DIRECTORY clauses of ALTER TABLE on a MyISAM table.
However, in Windows, you can manually move the .MYD and .MYI files to where you want.
UPDATE 2012-01-03 22:03 EDT
Interestinging, MySQL 5.5.15 on my Windows 7 machines says symlink support exists:
mysql> show variables like 'have_sym%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_symlink  | YES   |
+---------------+-------+
1 row in set (0.09 sec)

I also discovered that symlinking is possible in Windows:

http://en.wikipedia.org/wiki/NTFS_symbolic_link
http://en.wikipedia.org/wiki/Symbolic_link#Windows_7_.26_Vista_symbolic_link

On my Windows 7 machines at home, the command line utility mklink exists:
C:\Windows\system32>mklink
Creates a symbolic link.

MKLINK [[/D] | [/H] | [/J]] Link Target

        /D      Creates a directory symbolic link.  Default is a file
                symbolic link.
        /H      Creates a hard link instead of a symbolic link.
        /J      Creates a Directory Junction.
        Link    specifies the new symbolic link name.
        Target  specifies the path (relative or absolute) that the new link
                refers to.

C:\Windows\system32>

I AM SHOCKED !!! That means you can experiment with CREATE TABLE ... DATA DIRECTORY='...' INDEX DIRECTORY='...' in Windows.
I just tried this:
use test
drop table if exists data_table;
drop table if exists data_table_sharded;
CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
CREATE TABLE data_table_sharded LIKE data_table;
ALTER TABLE data_table_sharded DATA DIRECTORY='C:\DAT' INDEX DIRECTORY='C:\NDX';

I just got this:
mysql> use test
Database changed
mysql> drop table if exists data_table;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists data_table_sharded;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE data_table_sharded LIKE data_table;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER TABLE data_table_sharded DATA DIRECTORY='C:\DAT' INDEX DIRECTORY='C:\NDX';
Query OK, 0 rows affected, 2 warnings (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+----------------------------------+
| Level   | Code | Message                          |
+---------+------+----------------------------------+
| Warning | 1618 | <DATA DIRECTORY> option ignored  |
| Warning | 1618 | <INDEX DIRECTORY> option ignored |
+---------+------+----------------------------------+
2 rows in set (0.00 sec)

mysql>

You still cannot use the DATA DIRECTORY AND INDEX DIRECTORY option in CREATE TABLE or ALTER TABLE.
UPDATE 2012-01-03 22:45 EDT
I ran these commands
use test
drop table if exists data_table;
drop table if exists data_table_sharded;
CREATE TABLE data_table (a int,primary key(a)) ENGINE=MyISAM;
INSERT INTO data_table VALUES (71),(22),(128),(97),(18),(4),(112),(277);
CREATE TABLE data_table_sharded LIKE data_table;

I made two directories

mkdir C:\dat
mkdir C:\ndx

I created those folders and created hard links for data_table_sharded
C:\MySQL\data\test>mklink /H data_table_sharded.MYD C:\dat\data_table_sharded.MYD
Hardlink created for data_table_sharded.MYD <<===>> C:\dat\data_table_sharded.MYD

C:\MySQL\data\test>mklink /H data_table_sharded.MYI C:\ndx\data_table_sharded.MYI
Hardlink created for data_table_sharded.MYI <<===>> C:\ndx\data_table_sharded.MYI

C:\MySQL\data\test>

I went back to MySQL and loaded the data from data_table:
mysql> flush tables;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO data_table_sharded SELECT * FROM data_table;
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> show create table data_table_sharded\G
*************************** 1. row ***************************
       Table: data_table_sharded
Create Table: CREATE TABLE `data_table_sharded` (
  `a` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select * from data_table_sharded;
+-----+
| a   |
+-----+
|   4 |
|  18 |
|  22 |
|  71 |
|  97 |
| 112 |
| 128 |
| 277 |
+-----+
8 rows in set (0.00 sec)

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql>

So, it can be done. Just use mklink to create hard links instead of symlinks. WOW !!!
I learned something about MySQL for Windows. I doubt if Oracle will implement the DATA DIRECTORY and INDEX DIRECTORY options since the default storage engine is now InnoDB.
Notwithstanding, you create the empty table, move the .MYD and .MYI to different folders from the OS, create hard links, run FLUSH TABLES; and INSERT your data.
Give it a Try, and let me know how it goes...


List of variable defaults for InnoDB engine

SHOW COMMAND

mysql --table -Ae "SHOW GLOBAL VARIABLES LIKE 'innodb%'"

INFORMATION_SCHEMA (MySQL 5.1+)

SQLSTMT="SELECT * FROM information_schema.global_variables"
SQLSTMT="${SQLSTMT} WHERE variable_name LIKE 'innodb%'"
mysql -Ae"${SQLSTMT}"

my.cnf Settings

cat /etc/my.cnf | grep innodb

MySQL Documentation

MySQL 5.6
MySQL 5.5
MySQL 5.1
MySQL 5.0

As for the runtime settings and /etc/my.cnf diff, here it is

mysql -uroot --table -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBRuntime.txt
cat /etc/my.cnf | grep innodb | sed 's/=/ /g' | awk '{print $1}' > InnoDBConfigs.txt
for X in `cat InnoDBConfigs.txt`
do
    grep "${X}" InnoDBRuntime.txt
done

As for the runtime settings and actual defaults, it's a little different

STEP 01) Install mysql on a DevServer with no /etc/my.cnf

STEP 02) Run mysql -uroot -hDevServerIP -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBDefault.txt

STEP 03) Run mysql -uroot -p -ANe"SHOW GLOBAL VARIABLES LIKE 'innodb%'" > InnoDBRuntime.txt

STEP 04) Run the Diff

diff InnoDBDefault.txt InnoDBRuntime.txt

Give it a Try !!!


You shouldn't concatenate inside EXEC like that. Instead, try this (most importantly, it lets you debug your statement before executing it):

DECLARE @datadir VARCHAR(128) = 'C:\SQL2012\SH_DATA';

DECLARE @sql NVARCHAR(MAX) = N'BULK INSERT dbo.CHANNELS FROM '''
  + @DATADIR + '\CHANNELS.txt'' WITH ...;';

PRINT @sql;
-- EXEC sp_executesql @sql;

Also, you need to execute all three of these statements together. You can't execute the declares, then execute the remainder independently, which it seems like you did above.


Is a 'block' and a 'page' the same thing in PostgreSQL?

If you're talking about the 8kB subdivisions of physical table storage, then yes.

These are usually called "pages" in Postgres as well, and that is the terminology used in the docs.


How to design a Table in which its fields varies on its categories?

I would avoid having completely separate tables for each ad type, that would be bad design (you'd end up having to UNION them all together in just about every query). Also avoid having all the fields possible in one table as that would be inefficient also, especially if your database doesn't support sparse rows.

The usual technique for this sort of problem is "table inheritance" (so search that term for more examples, whereby you have one core table that contains all the common properties (in this case things like date, caption, description and owner), a column to indicate the type of advert and an extra table for each type. So for a car the ad_type column would contain "car" and the details would be found ad_detail_car table. Something like:

ad_common          ad_detail_car
===========        =============
advert_id   -----> advert_id
advert_type   |    make
caption       |    manufacturer
description   |
owner         |    ad_detail_book
price         |    ==============
              |--> advert_id
              |    isbn
              |    binding
              |
              |    ad_detail_kitten
              |    ================
              `--> advert_id
                   breed
                   age

advert_id would be the PK for the common table and both the PK and an FK to the common table in the detail tables.

As the number of advert types grows having a separate table for each might become a mess so you might instead consider a property bag like so:

ad_common          ad_extra_properties
===========        ===================
advert_id   -----> advert_id
advert_type        property_name
caption            property_type
description        property_value
owner
price

Here the PK for the property bag is advert_id,property_name. Be warned though, this is a pattern (called the EAV or "Entity Attribute Value" pattern) that has its own set of problems, particularly for reporting, so read around that before considering if it is suitable for your needs.

Another option if going for the EAV model is to do away with the detail or property bag tables completely and just store the extra properties in a format useful for the business logic (JSON, XML) in a column of the main table. This is simpler but means (unless your chosen DB natively supports such documents) you can't report on or filter/sort by the extra properties at all directly in the database.


uses a third table that will link the Event table to the Guest table :


How to easily show very long "canonical" name for each column in all reports?

Identifiers are limited to 63 bytes by default, which doesn't qualify as "extremely long". Quote from the manual:

  By default, NAMEDATALEN is 64 so the maximum identifier length is 63
  bytes. If this limit is problematic, it can be raised by changing the
  NAMEDATALEN constant in src/include/pg_config_manual.h

(and recompiling)

Besides the need to recompile that might give pause, increasing NAMEDATALEN by a large margin is not very good idea, because every single identifier stored in the catalog will consume that size, no matter its length.

To me, the only reasonable way to show arbitrary long column names would be to implement it in the client-side presentation layer. Storing the names themselves could be done in the SQL column comments as you suggest, or in your own custom metadata table. The presentation layer could retrieve that data at the start of the session and cache it so that it would be essentially transparent for the rest of the SQL.


We are having a similar problem when we conduct backups. It looks as though the disk reads are maxing out the EBS connection.

I think the problem is down to the Throughput limits on an instance type. Check the max bandwidth column here:

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSOptimized.html

The only solution seems to be switching to a different instance type with more throughput. Have you found any other solutions?


What is default MySQL binlog retention in AWS RDS?

you need to give it a value if you wish to preserve the binlogs:

call mysql.rds_set_configuration('binlog retention hours', 24);

will cause the binlogs to be stored for 24hrs, for example.


Why doesn't simple recovery model support log backup or tail log backup?

The logs written between the FULL backups and the crash will be overwritten at some point
They are not saved with BACKUP LOG.

My bold describing Simple

  Every transaction is still written to the transaction log, but once
  the transaction is complete and the data has been written to the data
  file the space that was used in the transaction log file is now
  re-usable by new transactions.

From SQL Server docs

  Automatically reclaims log space to keep space requirements small,
  essentially eliminating the need to manage the transaction log space.

In theory, a crash happening just after a full backup that has just a few transactions might work, if the LSNs match.

In practice, this is almost never guaranted to happen so for consistency (and false hope) better to disallow this in simple recovery model


Database Tuning Advisor user permissions

From BOL : Permissions Required to Run Database Engine Tuning Advisor

  After you install SQL Server, a user who is a member of the sysadmin fixed server role must launch Database Engine Tuning Advisor before anyone else. When the sysadmin user launches Database Engine Tuning Advisor that action initializes the application for use by others. After Database Engine Tuning Advisor is initialized, any user who is a member of the db_owner fixed database role can use it to tune tables on databases they own. For more information about initializing Database Engine Tuning Advisor.


Is a functional dependency on a "child" table allowed in third normal form (3NF)?

There is an informal dependency, but it's not a functional dependency.  See the wiki article for the formal definition. Here's an excerpt in simple terms:

  In simple words, if the values for the X attributes are known (say
  they are x), then the values for the Y attributes corresponding to x
  can be determined by looking them up in any tuple of R containing x.

There is no normal form definition which precludes summarizing child record values in the parent record.  However, lots of people would suggest against doing this in transactional databases, particularly where there's a possibility that a child record could be updated (or deleted, or more child records added) such that there's a risk of the attribute in the parent becoming inconsistent with the aggregate value of the children.

If your OrderHeader table had a CustomerName column, then that would violate 3NF because of the transitive functional dependency on CustomerID.

Being informal, a lot of people would say that OrderTotal is denormalized because it's susceptible to things that look like insert, update and delete anomalies and so people draw a comparison with the formal definitions of normalization and normal forms (since these are also about avoiding insert/update/delete anomalies).  There are some sticklers, though, who will be adamant that avoiding summary attributes in parent records is not the same thing as normalization per se.


To perform the join without matching rows on the dates you need to use an outer join. The challenge here is going to be propagating down the nearest rates as you are missing various rates in table2 both ordered ascending and descending. I don't have much experience with MySQL so I'm not sure if Window Functions are available? I believe this will help you accomplish your goal.

ISNULL(ISNULL(rate, (SELECT TOP (1) rate FROM table2 AS t2
WHERE a.date <= t2.date)),
(SELECT TOP (1) rate FROM table2 AS t2
 WHERE a.date >= t2.date
 ORDER BY t2.date DESC))

SELECT a.[date],
       b.[date],
       a. price,
       b.rate,
       price/rate AS adjusted,
       ISNULL(ISNULL(rate,
(SELECT TOP (1) rate FROM table2 AS t2
 WHERE a.date <= t2.date)),
(SELECT TOP (1) rate FROM table2 AS t2
 WHERE a.date >= t2.date ORDER BY t2.date DESC))
FROM table1 AS a
LEFT OUTER JOIN table2 AS b
    ON a.[date] = b.[date];


How to add a flexible database role to a database?

You need to do the user/role mapping per database.

So you create an AD group and add that as a login on the server

Within the database you define a role and set permissions for the role. Then you need to add the AD Group login as a user to the database and set them as a member of the database role. Each role can have multiple members so you can add many groups as members of the role.

You can also map the users to a server role and then add the server role as a user in the database and make that member of a database role.

Robin Page has a simple explanation on simple talk and it's all explained in details in multiple details in many StackExchange answers


Backup of MongoDB collection data and rename the collections

You can with this command rename your collection:
    db.rrecord.renameCollection("record")

Then backup with this:
    mongodump --host mongodb1.example.net --port 3017 --username user --password "pass" --out /opt/backup/file

Tip: You can specify different host for save your backup file.


Generally,

UPSERT is built off of INSERT
MERGE focuses on merging/synchronizing tables and provides

conditionality (where clauses)
DELETE support.

UPSERT lacks conditionality, In PostgreSQL, you had some ability to specify conditions, by proxy of the index that was being violated, for instance

ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
ON CONFLICT (country) DO NOTHING;

But it didn't provide the ability to specify multiple conditions nor the ability to DELETE in any condition, both of which permit a more rich set of rules making it possible to "synchronize tables with minimal work" which seems to be the goal of MERGE.

As another matter, from Peter Geoghegan's post highlighting some of the differences "SQL MERGE is quite distinct from UPSERT"

UPSERT is atomic in its insert-or-update
SQL MERGE doesn't technically require a UNIQUE INDEX.

See also

PostgreSQL patch returned 9/11
PostgreSQL 11 MERGE syntax (since removed)


Inserts very slow near the end of dataset

You have run out of resources!

With 202M records, even one record would be only 64 bytes (you can check with db.collection.stats().avgObjSize), the size is 12GB and then comes index. The optimal situation is when active data, (index and data) is in the memory. This case it is NOT. So, mongod starts using disk heavily and if your disk is not high IOPS (SSD base) and low latency base, your machine starts slowing down.

Can you (for start) increase memory to (at least) 16 - 32GB?


How to grant access to IP range in Postgres?

The /128 in the address is the NUMBER OF BITS to match...  not a range.

So you would want 10.8.101.0/24 (match the first 24 bits).

As an additional example, if you wanted to restrict it to 10.8.101.128 to 10.8.101.255 then you'd do 10.8.101.128/25

Please see the address subsection in the documentation


1) If you are using Oracle 11g or later:

Starting from 11g Oracle, was been introduced a fixed table, X$DBGALERTEXT, from which you can retrieve all the data what alert.log does.
Here is a simple query that retrieves all errors which occured today, like you want.

SET linesize 200 pagesize 200
col RECORD_ID FOR 9999999 head ID
col MESSAGE_TEXT FOR a120 head Message

SELECT record_id, message_text
FROM X$DBGALERTEXT
WHERE originating_timestamp > systimestamp - 1 AND regexp_like(message_text, '(ORA-|error)')

As Twinkles mentioned in his answer, you can take advantage of ADRCI utility to select alert filters that you need (in fact, this utility queries against the X$DBGALERTEXT table). For example, you can build a simple function and call it from OS prompt:

vealert() {
  adrci exec="set home diag\/rdbms\/$DB_UNIQUE_NAME/$ORACLE_SID\; \
   show alert -p \\\"message_text like '%ORA-%' \
     and originating_timestamp > systimestamp - $1\\\""
}

in file .bashrc, for example:

export DB_UNIQUE_NAME=<unique_name>
. $HOME/bin/vealert

Then:

bash
vealert 1

..
Tue Dec 24 17:46:05 2013
ORA-28 signalled during: ALTER TABLESPACE DATA_APP_199903 READ ONLY...
..

2) Otherwise:

Go and have a look at this web page. There is a script that can be used to check any ORA- errors are there in the alertlog file for the present day.

To schedule this job, you can add a line in your cron table specifying the time you want the job to execute. Alternatively, you can take advantage of the Oracle Scheduler.


Does SQL Server query timeout settings change require a server restart?

According MS-DOCS you don't need to restart SQL Server.

Configure the remote query timeout Server Configuration Option

  Follow Up: After you configure the remote query timeout option

  The setting takes effect immediately without restarting the server.


Database Mirror - Host can not be reached or does not exist

Related to question but not the answer: While you got SQL Server 2017 with Enterprise Edition why to consider a feature which is under maintenance and might removed in future.

However, in your case, i would suggest to verify following areas (if not done already), hope it must fix the issue:

Make sure all SQL instance services running under same domain account

That service got permissions (as mentioned in OP) on all the SQL instances

Verify end-point ports are different, if one server got multiple SQL instances

Allow end-point ports at firewall (incoming and outgoing), even firewall disabled

Restart all SQL instance services if it's not done so since the last password change

Check SQL Error log to verify same port numbers are listening


How can I get running totals of recent rows faster?

I recommend testing with a bit more data to get a better idea of what's going on and to see how different approaches perform. I loaded 16 million rows into a table with the same structure. You can find the code to populate the table at the bottom of this answer.

The following approach takes 19 seconds on my machine:

SELECT TOP (10) seq
    ,value
    ,sum(value) OVER (ORDER BY seq ROWS UNBOUNDED PRECEDING) total
FROM dbo.[Table_1_BIG]
ORDER BY seq DESC;

Actual plan here. Most of the time is spent calculating the sum and doing the sort. Worryingly, the query plan does almost all of the work for the entire result set and filters to the 10 rows that you requested at the very end. This query's runtime scales with the size of the table instead of with the size of the result set.

This option takes 23 seconds on my machine:

SELECT *
    ,(
        SELECT SUM(value)
        FROM dbo.[Table_1_BIG]
        WHERE seq <= t.seq
        ) total
FROM (
    SELECT TOP (10) seq
        ,value
    FROM dbo.[Table_1_BIG]
    ORDER BY seq DESC
    ) t
ORDER BY seq DESC;

Actual plan here. This approach scales with both the number of requested rows and the size of the table. Almost 160 million rows are read from the table:

To get correct results you must sum rows for the entire table. Ideally you would perform this summation only once. It's possible to do this if you change the way that you approach the problem. You can calculate the sum for the entire table then subtract a running total from the rows in the result set. That allows you to the find the sum for the Nth row. One way to do this:

SELECT TOP (10) seq
,value
, [value]
    - SUM([value]) OVER (ORDER BY seq DESC ROWS UNBOUNDED PRECEDING)
    + (SELECT SUM([value]) FROM dbo.[Table_1_BIG]) AS total
FROM dbo.[Table_1_BIG]
ORDER BY seq DESC;

Actual plan here. The new query runs in 644 ms on my machine. The table is scanned once to get the complete total then an additional row is read for each row in the result set. There's no sorting and nearly all of the time is spent calculating the sum in the parallel part of the plan:

If you'd like for this query to go even faster you just need to optimize the part that calculates the complete sum. The above query does a clustered index scan. The clustered index includes all columns but you only need the [value] column. One option is to create a nonclustered index on that column. Another option is to create a nonclustered columnstore index on that column. Both will improve performance. If you're on Enterprise a great option is to create an indexed view like the following:

CREATE OR ALTER VIEW dbo.Table_1_BIG__SUM
WITH SCHEMABINDING
AS
SELECT SUM([value]) SUM_VALUE
, COUNT_BIG(*) FOR_U
FROM dbo.[Table_1_BIG];

GO

CREATE UNIQUE CLUSTERED INDEX CI ON dbo.Table_1_BIG__SUM (SUM_VALUE);

This view returns a single row so it takes up almost no space. There will be a penalty when doing DML but it shouldn't be much different than index maintenance. With the indexed view in play the query now takes 0 ms:

Actual plan here. The best part about this approach is the runtime isn't changed by the size of the table. The only thing that matters is how many rows are returned. For example, if you get the first 10000 rows the query now takes 18 ms to execute.

Code to populate table:

DROP TABLE IF EXISTS dbo.[Table_1_BIG];

CREATE TABLE dbo.[Table_1_BIG] (
    [seq] [int] NOT NULL,
    [value] [bigint] NOT NULL
);

DROP TABLE IF EXISTS #t;
CREATE TABLE #t (ID BIGINT);

INSERT INTO #t WITH (TABLOCK)
SELECT TOP (4000) -1 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

INSERT INTO dbo.[Table_1_BIG] WITH (TABLOCK)
SELECT t1.ID * 4000 + t2.ID, 8 * t2.ID + t1.ID
FROM (SELECT TOP (4000) ID FROM #t) t1
CROSS JOIN #t t2;

ALTER TABLE dbo.[Table_1_BIG]
ADD CONSTRAINT [PK_Table_1] PRIMARY KEY ([seq]);


Remove duplicate values from query result

The super quick fix will be to apply DISTINCT ON() to your query.

DISTINCT ON

Postgres allows the use of the DISTINCT qualifier, but allows it to be applied across a subset of your returned columns from your SELECT query.

In this case, you could simply rewrite your query as

SELECT DISTINCT ON(dim_time__week_start_date)
dim_time__week_start_date, f_agent__total_users
FROM f_agent
JOIN dim_time
ON f_agent__dim_time_id = dim_time__id
WHERE dim_time__week_start_date IN (
SELECT generate_series('2015-07-06', '2016-07-03', '7 day'::interval)
);

Refer to the Postgres documentation on DISTINCT ON within SELECT for more info.

A word of caution:  By applying DISTINCT ON, it will keep only the first row, regardless of how the order is returned by your query.  Unless you apply some ORDER BY criteria, you may end up with inconsistent results!

In effect, you could run the query once and get f_agent__total_users as 3, but run it later and get f_agent__total_users as 33.  I would highly recommend some additional predicate criteria to ensure proper and consistent results are returned.


Unable to import DMP file that is made in 11g2 to Oracle 18c

Add the following to the impdp command:

VERSION=11.2.0


SELECT every second row without auto_increment

There is no SQL mechanism for select every other row.

You will have to use an user-defined auto increment variable as follows:

ODD

SET @inc = 0;
SELECT * FROM
(SELECT *,@inc:=1-@inc autoinc
FROM mytable) A
WHERE autoinc = 1 ORDER BY id;

EVEN

SET @inc = 1;
SELECT * FROM
(SELECT *,@inc:=1-@inc autoinc
FROM mytable) A
WHERE autoinc = 1 ORDER BY id;

Give it a Try !!!

MORE INSIGHTS

If the table is very large, try using a LEFT JOIN of id

ODD

SET @inc = 0;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id) A
LEFT JOIN mytable USING (id)
WHERE autoinc = 1;

EVEN

SET @inc = 1;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id) A
LEFT JOIN mytable USING (id)
WHERE autoinc = 1;

PAGINATION

ODD

SET @inc = 0;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id LIMIT 0,20) A
LEFT JOIN mytable USING (id)
WHERE autoinc = 1;

EVEN

SET @inc = 1;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id LIMIT 0,20) A
LEFT JOIN mytable USING (id)
WHERE autoinc = 1;


What architecture is best to store a large 3 dimensional array in MySQL?

Deltas

The data you are "snapshotting" -- how often does it change?

I suggest looking into storing only the "deltas".  When some piece of the snapshot does not change at all, the delta is empty, and you can store nothing.

For reconstructing a snapshot at some point in the past, the processing is costly -- you need to walk through the versions, applying the deltas as you go.

There are two ways to run the deltas -- forward or backward.  Going 'forward', you would start with the original (complete) snapshot, then apply deltas until the desired time.  Going 'backward' has the advantage that the most recent snapshot is complete.  The going backward 'subtracts' off the changes.

Since you say "for years", it is probably wise to take complete snapshots every, say, day.  Then finding a particular 30-second second snapshot won't involve more than 2880 deltas.  This obviously leads to a speed/space tradeoff -- full snapshots are bulky, but infrequent snapshots leads to long 'reconstruction' times.

Trigger

Rather than "snapshotting", use a TRIGGER to build an "audit trail".  This is similar the "deltas" I mentioned, but it is better in that it is continuous, not "every 30 seconds".  The case I remember had over a billion rows in the audit trail; each row had (approximately) the timestamp, table name, PRIMARY KEY, and a compressed JSON blob of all the columns for that rows.  Your needs may be better served by some variant of that.

Schema

Until I see the actual queries, I will advise against PARTITIONing since it is usually of no performance benefit.

The link about table size limits is missing one number:  64TB is the limit for one non-partitioned InnoDB table.


What is the row overhead when using Page Compression?

If you try creating your table without the clustered PK Constraint, and you'll get a slightly different error:

  Msg 1701, Level 16, State 1, Line 1 Creating or altering table
  'Mytable' failed because the minimum row size would be 8067, including
  1530 bytes of internal overhead. This exceeds the maximum allowable
  table row size of 8060 bytes.

In this error message, you can see that there is 1530 bytes of internal overhead for page compression.

Now, you can do the math:

8 bytes for bigint MyTableID
4 bytes for int LastColumn
9 bytes for each of the 593 numeric(19,4) columns (5337 bytes total)
1530 bytes of compression overhead

So, 8 + 4 + (593*9) + 1530 = 6879.
Wait a second....  That's still below 8060. What's up with that?!

The Page Compression algorithm actually stacks several compression algorithms together. The first step is to apply ROW compression. The overhead of row compression is not included in the 1530 bytes of overhead listed in that error message.

You can read more about how row compression works here on my blog and here in BOL. You'll note in the BOL article that it describes the numeric storage as "This storage is exactly same as the vardecimal storage format," but doesn't explain vardecimal. This post covers vardecimal a bit more--essentially, it adds 2 bytes of overhead per column to store the actual length (similar to what varchar does).

Row compression will require an additional 2 bytes for each of the 593 numeric columns, plus the bigint and int will require 1 byte of overhead each.

The row-compressed storage requirements would be:

8 bytes + 1 byte overhead for bigint MyTableID
4 bytes + 1 byte overhead for int LastColumn
9 bytes + 2 bytes overhead for each of the 593 numeric(19,4) columns
1188 bytes of ROW compression overhead

8 + 4 + (593*9) = 5349 bytes data

1 + 1 + (593*2) = 1188 bytes row compression overhead

6537 bytes total for row-compressed schema

Now that we have the row size for the row-compressed schema, we can revisit our math. The page-compressed row size will be the data size + row compression overhead + page compression overhead:

8 bytes for bigint MyTableID
4 bytes for int LastColumn
9 bytes for each of the 593 numeric(19,4) columns
1188 bytes of ROW compression overhead
1530 bytes of PAGE compression overhead

  5349 bytes data
+ 1188 bytes row compression overhead
+ 1530 bytes page compression overhead

8067 bytes total


SSIS Slow Data Transfer between Oracle and SQL Server 2016

My suggestion is don't use SSIS. I have found it to be notoriously slow on transfers from Oracle to MSSQL. I was once presented with an SSIS job that was taking in excess of 4 hours to move less than 1 million rows.  I did an Oracle 10046 trace on the process and found it was doing row-by-row, slow-by-slow processing over a cursor.  I wrote a PL/SQL procedure to do a simple INSERT INTO tablename@sqllvr VALUES (SELECT  FROM oracle_table).  It completed in less than 4 minutes, vs. the 4+ hours for the SSIS method.  It's still running daily, several years later.


How to detect bad execution plans automatically?

I've had that problem for only on query:
1. Scheduled job was looking in query cache for particular query.
2. When query is found it looked for bad pattern in plan. For instance: Index Seek for index XYZ.
3. If bad pattern is found, that query will be automatically deleted from query cache by it's plan-handle using DBCC FREEPROCCACHE


You cannot create a password-protected-certificate, then use that certificate to encrypt a database via TDE since the SQL Server will not be able to open the database.

From Microsoft Docs:

  Transparent Data Encryption (TDE) must use a symmetric key called the database encryption key which is protected by either a certificate protected by the database master key of the master database, or by an asymmetric key stored in an EKM.

Steps involved in encrypting a database using TDE:

Create a master key
Create or obtain a certificate protected by the master key
Create a database encryption key and protect it by the certificate
Set the database to use encryption

An example of the above, taken from the Microsoft Docs page on TDE:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO

That page also says this:

  TDE certificates must be encrypted by the database master key to be accepted by the following statements. If they are encrypted by password only, the statements will reject them as encryptors.

    CREATE DATABASE ENCRYPTION KEY
    ALTER DATABASE ENCRYPTION KEY
    DROP DATABASE ENCRYPTION KEY

  Altering the certificates to be password-protected after they are used by TDE will cause the database to become inaccessible after a restart.


Sort spills to tempdb but estimated rows equals to actual rows

Enable trace flag 7470.

FIX: Sort operator spills to tempdb in SQL Server 2012 or SQL Server 2014 when estimated number of rows and row size are correct

As I wrote in answer to Query Plan question:

  This trace flag corrects an oversight in the calculation. It is quite safe to use, and in my opinion ought to be on by default. The change is protected by a trace flag simply to avoid unexpected plan changes.


Joining two Tables with matching two values

SELECT user_name FROM user u
WHERE EXISTS (SELECT 1 FROM product p
                WHERE product_type = 'laptop'
                AND p.seller_id = u.user_id)


Setting model title in MySQL Workbench for Forward Engineering

The dialog box with these controls is under File->Document Properties.
There is no way to autoincrement the version number in this dialog.


How to get the type of geometry column in postgres?

The geometry_columns view takes care to remove any Z/M suffixes.

You can look up the PostGIS type modifier directly in the system tables:

SELECT postgis_typmod_type(atttypmod)
FROM pg_attribute
WHERE attrelid = 'public.table_name'::regclass
  AND attname = 'col_name';

(The same can be done with postgis_typmod_srid().)


Replication between two different SQL Server versions

It sort of depends whether you're using Database Mirroring, or Transaction Replication (although I suppose you could be using both)

Assuming Transaction Replication:

This is supported, so long as you stick to certain rules, as described in the MSDN article Upgrade Replicated Databases

As long as your Distributor is the same version or greater than your Publisher, and a Subscriber is within two versions of the Publisher, which fits in your case so you're OK. That MSDN article provides instructions.

This is similar to the question/answer asked a couple of years back, but the MSDN article has been updated since.

If you mean Database Mirroring:

I'm assuming you don't mean this, as this question has been asked before in a number of ways, but I'll add the answer in for completeness.

This can be done, but it is supported strictly for minimising downtime during upgrades (which it seems is what you are doing, so that's OK).

Have a look at MSDN - Minimize Downtime for Mirrored Databases When Upgrading Server Instances for information about this (and a walk-through).


A view, being just a query persisted in the database, should not theoretically have any performance hit. Same is true for column or table aliases. The performance hit, if any, will be barely noticeable once the execution plan is compiled and cached by the database engine. So, there is no performance justification not to use column aliases based on views. Having said that, I second Aaron Bertrand that the application should not have been written in incorrect schema. I would fix the application just for the sake of best practices and not to "create a permanent messy layer of indirection" as Aaron Bertrand said.


Interpreting Page Life Expectancy metric

There's no simple figure for thresholds. This is the same as most SQL Server counters. Your system is busy though.

The peaks and such will likely be large queries.

To dig in deeeper you need to understand: interpret PLE as "I'm reading my buffer memory from disk every X seconds, does that sound like a lot?" It's not entirely accurate (buffer < total memory size) but it's a good rule of thumb.

Is 128GB per 23 minutes too much? That's 93MB/s of reads which is a lot to sustain for a busy SAN. But if you had direct attached storage then maybe it isn't.

How low is too low? If your PLE is 0 you're reading faster than the memory can hold. If it's 60 you're reading 128GB a minute. Again that sounds like a lot right. Not much hardware could sustain that.

The thumb guide is 300. Except on your system 300 would mean 25GB of reads per minute or 400MB/s. Also too much except for directly attached SSDs.

Your ultimate threshold is: where users experience pain. Which is likely somewhere near the limits of your sustained storage speed (again remembering SQL is doing more than just reads).

Nobody looks at the threshold for during backups because databases are so large they often exceed memory and need to cycle through the buffer numerous times. Again it's not important unless users are experiencing pain due to it. (And if they were you would increase memory or throttle down backup speed).

How do monitoring applications determine a threshold? They're usually stupid and just pick 300. Or 1000. Some sample at different times of the day and apply a fudge factor and compare against past results - and that gets super complicated if you do backups on a weekly or monthly schedule; after all which past baseline should they compare against?


ORA-01113: file # needs media recovery ORA-01110

Found the answer:

SQL> recover datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF';

alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF';

alter  TABLESPACE EXAMPLE  drop datafile  'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE02.DBF'

But for production DB it needs a lot of time to recover... Can't I drop that file without recovering it?

One more question...
Another datafile was available during adding that file.. is there any chance that newly created datafile was used for storing data? I think oracle uses datafile until it will not exceed its maximum size and then switching to another available file... is not it so?

I am asking this because I want to be sure that newly created file doesn't contain any necessary data...


How to promote hidden mongo node to primary?

We are trying to implement DR setup for our mongodb replica set and
  currently three nodes in DR are configured to run as hidden nodes.

As per MongoDB documentation Configure a Hidden Replica Set Member Hidden members are part of a replica set but cannot become primary and are invisible to client applications. Hidden members may vote in elections.

The most common use of hidden nodes is to support delayed members. If you only need to prevent a member from becoming primary, configure a priority 0 member.

Member Configuration Document

To configure a secondary member as hidden, set its members[n].priority value to 0 and set its members[n].hidden value to true in its member configuration:

{
  "_id" : <num>
  "host" : <hostname:port>,
  "priority" : 0,
  "hidden" : true
}

Configuration Procedure

The following example hides the secondary member currently at the index 0 in the members array. To configure a hidden member, use the following sequence of operations in a mongo shell connected to the primary, specifying the member to configure by its array index in the members array:

cfg = rs.conf()
cfg.members[0].priority = 0
cfg.members[0].hidden = true
rs.reconfig(cfg)

After re-configuring the set, this secondary member has a priority of 0 so that it cannot become primary and is hidden. The other members in the set will not advertise the hidden member in the isMaster or db.isMaster() output.

  We are trying to test failover to DR which will require promoting
  hidden node to primary and secondary respectively. What is the best
  way to achieve this?

Note with WARNING:

The rs.reconfig()
shell method can force the current primary to step down, which causes
an election.
When the primary steps down, the mongod
closes all client connections. While this typically takes 10-20
seconds, try to make these changes during scheduled maintenance
periods.
Avoid reconfiguring replica sets that contain members of different
MongoDB versions as validation rules may differ across MongoDB
versions.


Primary key within JSON data in Postgresql

As explained in Craig's answer neither PRIMARY KEY nor UNIQUE can be used.

However,here is a two part solution to work around this:

To enforce not null, use a check constraint, thus:

ALTER TABLE t ADD CONSTRAINT id_not_null CHECK ((json->>'id') IS NOT NULL);

To enforce uniqueness, create a unique index*, thus:

CREATE UNIQUE INDEX t_id ON t ((json->>'id'))

After this, a \d t on in psql will give you:

Indexes:
    "t_id" UNIQUE, btree ((json ->> 'id'::text))
Check constraints:
    "id_not_null" CHECK ((json ->> 'id'::text) IS NOT NULL)

And you can see both not null and uniqueness are enforced:

test=# INSERT INTO t VALUES ('{"no id" : "field here"}');
ERROR:  new row for relation "t" violates check constraint "id_not_null"
DETAIL:  Failing row contains ({"no id" : "field here"}).

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
INSERT 0 1

test=# INSERT INTO t VALUES ('{"id" : "abc"}');
ERROR:  duplicate key value violates unique constraint "t_id"
DETAIL:  Key ((json ->> 'id'::text))=(abc) already exists

* note that the mere existence of the t_id index is sufficient to enforce uniqueness, even though it isn't explicitly given as a constraint.


Misunderstanding statement in MySQL documentation

If you need to JOIN or compare columns, then ensure they are the same datatype. This avoids implicit conversions which usually invalidate index usage.

However, for JOINs especially this can be completely prevented by defining a foreign key constraint: this forces you to have the same datatype. See MySQL docs which says:

  Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same


Because you're not telling us much of what you need, I'll guess for everything, and we'll make it moderately complex to simplify some of the possible questions.

First thing about MVCC is that in a highly concurrent system you want to avoid table locking. As a general rule, you can't tell what does not exist without locking the table for the transaction. That leaves you one option: don't rely on INSERT.

I leave very little as an exercise for a real booking app here. We don't handle,

Overbooking (as a feature)
Or what to do if there are not x-remaining seats.
Buildout to customer and transaction.

The key here is in the UPDATE. We lock only the rows for UPDATE before the transaction starts. We can do this because we've inserted all seat-tickets for sale in the table, event_venue_seats.

Create a basic schema

CREATE SCHEMA booking;
CREATE TABLE booking.venue (
  venueid    serial PRIMARY KEY,
  venue_name text   NOT NULL
  -- stuff
);
CREATE TABLE booking.seats (
  seatid        serial PRIMARY KEY,
  venueid       int    REFERENCES booking.venue,
  seatnum       int,
  special_notes text,
  UNIQUE (venueid, seatnum)
  --stuff
);
CREATE TABLE booking.event (
  eventid         serial     PRIMARY KEY,
  event_name      text,
  event_timestamp timestamp  NOT NULL
  --stuff
);
CREATE TABLE booking.event_venue_seats (
  eventid    int     REFERENCES booking.event,
  seatid     int     REFERENCES booking.seats,
  txnid      int,
  customerid int,
  PRIMARY KEY (eventid, seatid)
);

Test Data

INSERT INTO booking.venue (venue_name)
VALUES ('Madison Square Garden');

INSERT INTO booking.seats (venueid, seatnum)
SELECT venueid, s
FROM booking.venue
  CROSS JOIN generate_series(1,42) AS s;

INSERT INTO booking.event (event_name, event_timestamp)
VALUES ('Evan Birthday Bash', now());

-- INSERT all the possible seat permutations for the first event
INSERT INTO booking.event_venue_seats (eventid,seatid)
SELECT eventid, seatid
FROM booking.seats
INNER JOIN booking.venue
  USING (venueid)
INNER JOIN booking.event
  ON (eventid = 1);

And now for the Booking Transaction

Now we have the eventid hard coded to one, you should set this to whatever event you want, customerid and txnid essentially make the seat reserved and tell you who did it. The FOR UPDATE is key. Those rows are locked during the update.

UPDATE booking.event_venue_seats
SET customerid = 1,
  txnid = 1
FROM (
  SELECT eventid, seatid
  FROM booking.event_venue_seats
  JOIN booking.seats
    USING (seatid)
  INNER JOIN booking.venue
    USING (venueid)
  INNER JOIN booking.event
    USING (eventid)
  WHERE txnid IS NULL
    AND customerid IS NULL
    -- for which event
    AND eventid = 1
  OFFSET 0 ROWS
  -- how many seats do you want? (they're all locked)
  FETCH NEXT 7 ROWS ONLY
  FOR UPDATE
) AS t
WHERE
  event_venue_seats.seatid = t.seatid
  AND event_venue_seats.eventid = t.eventid;

Updates

For timed reservations

  You would use a timed reservation. Like when you buy tickets for a concert, you have M minutes to confirm the booking, or someone else gets the chance – Neil McGuigan 19 mins ago

What you would do here is set the booking.event_venue_seats.txnid as

txnid int REFERENCES transactions ON DELETE SET NULL

The second the user reserves the seet, the UPDATE puts in the txnid. Your transaction table looks something like this.

CREATE TABLE transactions (
  txnid       serial PRIMARY KEY,
  txn_start   timestamp DEFAULT now(),
  txn_expire  timestamp DEFAULT now() + '5 minutes'
);

Then in every minute you run

DELETE FROM transactions
WHERE txn_expire < now()

You can prompt the user to extend the timer when nearing expiration. Or, just let it delete the txnid and cascade down freeing up the seats.


Can I disable the redo log for a DELETE query or a whole transaction?

If I understand correctly, the problem is:

We have one or more tables (lets call them "parents") that we want to empty, either with DELETE or TRUNCATE. There are several other tables ("children") that have FOREING KEY constraints which reference the first table(s) or some other "child" and we want to empty those tables as well.

If we DELETE, the operation will succeed, as all the FKs have been defined with ON DELETE CASCADE actions. But it will be a very slow operation.
If we TRUNCATE, the operation fails due to the FKs. It's not allowed to TRUNCATE a table that other tables reference it.

To solve this conundrum, I don't think that messing with the redo log is advisable (if at all possible).

I suggest the more simple:

ALTER the children tables and DROP the offending FOREIGN KEY constraints.
TRUNCATE all the tables.
ALTER the children tables and add the constraints again.

If some reason altering the tables even temporarily is forbidden, you should at least follow MySQL's guidelines about optimizing the redo logs: Optimizing InnoDB Redo Logging


ONLINE BACKUP is not performed from the CRON Job on LINUX server (manually it works)

Chances are that the reason your script runs from the command line but fails from cron is that your DB2 environment is already initialized (i.e. via your .profile) for the interactive session, where it is not when called by cron.

Here's a suggested modification:

 #!/bin/bash
 #

 # Setup DB2 Environment
 . $HOME/sqllib/db2profile

 # Back up Database
 db2 -v "backup database maxdb71 online to /data/archiveBackup INCLUDE LOGS WITHOUT PROMPTING"

A few comments:

This will work if you're executing it via cron for the instance owner.  However, because it depends on the $HOME environment variable you would need to use the actual path to the instance owner's home directory if you wanted to run this via cron as a different user.
You should not reference binaries in /opt/ibm/db2/..., you should be using the binaries from the DB2 instance.  Because the script initializes the environment from the db2profile, the db2 command will be in the script's $PATH.
I removed some of the options from your backup command as these are not necessary.  DB2 is very intelligent when it comes to selecting run-time values for these, so you're likely to get worse backup performance if you override the defaults.
When you execute the script from cron, make sure you capture the output in a log file.  It will be very useful for debugging purposes. Example crontab:

00 23 * * * /path/to/script/backup.sh > /path/to/script/backup.log 2>&1


You can not give object names from variables in TSQL, so you have to be a bit hacky and make the statement as a string and run as ad-hoc SQL. Something like:

SET @SQL = '
        INSERT dbNew.dbo. + @tablename
        SELECT * FROM dbOld.dbo.' + @tablename + '
        EXCEPT
        SELECT * FROM dbNew.dbo.' + @tablename + '
        '
EXEC (@SQL)

You are likely to find the following more efficient:

SET @SQL = '
        INSERT dbNew.dbo. + @tablename
        SELECT * FROM dbOld.dbo.' + @tablename + ' oTbl
        EXCEPT
        LEFT OUTER JOIN dbNew.dbo.' + @tablename + ' nTbl
        ON nTbl.primaryKey = oTlb.primaryKey
        '
EXEC (@SQL)

but that does mean you need to work out what the primary key columns are by querying more INFORMATION_SCHEMA/sys.* structures in your cursor.

Another complication that you will hit in a well designed database is that you will need to order the table population to ensure that foreign key constraints are always satisfied during the process (or remove such constraints first and readd them after the transfer).


Joining very distant tables in the absence of a direct relationship

SELECT
    table1.name
    ,table7.name
FROM table1
INNER JOIN table2 on table2_id = table2.id
INNER JOIN table3 on table3_id = table3.id
INNER JOIN table4 on table4_id = table4.id
INNER JOIN table5 on table5_id = table5.id
INNER JOIN table6 on table6_id = table6.id
INNER JOIN table7 on table7_id = table7.id

That is what your query would end up looking like. I would probably use dynamic SQL to piece this together if I needed to regularly.

I would look at the workload on the server. If it happened to be an OLTP server with lots of transactions, it may be semi difficult to adjust things.

If the data does not change regularly or frequently, you could look into creating a mapping table based on master data management / triggers / ETL processes (SSIS / BIML if you are using SQL Server) / Constraints that would piece together a singular table of static data from those tables. Essentially like a physical view.

Another idea is you can create an indexed view joining those tables together as well.

  Question: If we wanted to SELECT table1.name, table7.name using a JOIN
  wouldn't we need a ton of AND clauses (by ton I mean 6)? Would the
  answer change if the tables were not just 6 tables apart as in my
  example, but rather were 1,000 tables apart?

If you use the old syntax join, yes essentially. I would strongly urge you to use the new join as I show above as it is the new standard and is a) easier to read and b) less prone to error.

  I'm not sure if SQL has a special tool for this, or if we just use n-1
  number of AND clauses. If the latter is the case, I'd imagine we'd
  want to write a batch or python file to automate a selection with 999
  AND's.

There is no 'automated' tool, but I gave some suggestions above. There are other third party BI tools out there that can assist with this, especially those that specialize in master data management / ETL / BI practices.


updating the entire column with datetime values based on other values in the row

How about this:

UPDATE [dbo].[MyTable]
SET [ToDateTime] = [ContainedData]
WHERE aid = 123
and pid = 2
and isdate([ContainedData] ) = 1


How do we express a weak entity to be dependent on another weak entity?

Using you syntax, I think the following might be what you're looking for:

CREATE TABLE authors(
   author_id integer,
   author_name varchar(100),
   author_age int,

   PRIMARY KEY (author_id)
);

CREATE TABLE books(
   book_id integer,
   book_title varchar(100),
   book_published date,
   author_id integer,

   PRIMARY KEY (book_id),
   FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

CREATE TABLE chapters(
   chapter_id integer,
   chapter_title varchar(100),
   book_id integer,
   content longblob,

   PRIMARY KEY (chapter_id),
   FOREIGN KEY (book_id) REFERENCES books(book_id)
);

Each table has a unique id column as its primary key and a foreign key relationship to the table "above" it.  These don't need to be part of the primary keys.  For instance, book_id should be unique in and of itself, no need to include author_id as part of the primary key (assuming books only have one author).  I added a table for chapters with basically the same relationship to books as books are to authors.

It might be helpful to think about the relationships a little differently.  You say for instance, "a Book cannot stand alone without association to an Author" or "I have created a relationship where Books relies on Authors".  It might be better to think of authors simply as a look-up [-Who wrote this book? -Author 37 did (details on file elsewhere).] and the foreign-key constraint as simply requiring that look-up to be a legitimate author (i.e. present in the authors table).

To illustrate the non-hierarchy further, imagine that your books table not only has a look-up to authors, but also to publishers and copyright holders and so on (a "star" structure) and that another table, called editions, has multiple rows associated with each book (using the books table as a foreign-key look-up) and each edition also has a look-up to the company that printed that edition and so on and so on.

Of course you would need a completely different structure in the first place if, say, books could have more than one author, or each chapter is a short story with it's own author or authors, etc.

I hope this helps a bit.


Copy SQL database with encryption from one Azure subscription to another

Yes, you can Copy the SQL database with encryption from one Azure subscription to another. But you have to move your database along with an Azure SQL Server.  Not just the database itself. You can follow my steps as a workaround.

I tested this successfully with the following steps:

Subscription 1 Create a new resource group, an Azure SQL Server and Azure SQL database which is empty.
Create a certificate and symmetric key. Example from Microsoft documentation. Certificate and Symmetric key.

CREATE CERTIFICATE Shipping04
  ENCRYPTION BY PASSWORD = 'pGFD4bb925DGvbd2439587y'
  WITH SUBJECT = 'Sammamish Shipping Records',
  EXPIRY_DATE = '20201031';
GO

CREATE SYMMETRIC KEY JanainaKey09
 WITH ALGORITHM = AES_256
 ENCRYPTION BY CERTIFICATE Shipping04;
GO

Create another resource group in the same subscription as the staging area. Create a new Azure SQL Server and copy the database once the backup is available. You need this because when you move the resource they will not available at source after the move.

 Restore-AzSqlDatabase `
-FromPointInTimeBackup `
-PointInTime (Get-Date).AddMinutes(-10) `
-ResourceGroupName $Database.ResourceGroupName `
-ServerName $secondaryServerName `
-TargetDatabaseName $secondarydatabaseName `
-ResourceId $Database.ResourceID `
-Edition "Premium" `
-ServiceObjectiveName "P1" }

Move Azure SQL Server and refreshed database to a different subscription. You can move to an existing resource group and create a new one. Use Move-AzResource command. Details here and here.
Now copy the database to your existing SQL Server.
Delete the resources in step 4.

If you try to move only the database this the error message you get.

"properties": {
        "statusCode": "BadRequest",
        "serviceRequestId": null,
        "statusMessage": "{\"error\":{\"code\":\"InvalidResourceMoveRequest\",\"message\":\"The list of resources in move definition cannot be null or empty.\"}}"
    },


Why do my RDS read IOPS drop after a certain period of time?

I've not found the IO burst balance to work the way the documents state nor the monitoring of the burst balance to reflect reality, for t* class instances.  So while I can't explain your observation, it does not surprise me.

And in general, is there a more efficient way I can be doing this?

A single ALTER TABLE can implement a number of actions, not just a single action.  And it should generally scan the table only once, not once for each action.
ALTER TABLE table_name
   ALTER COLUMN column_name1 SET NOT NULL,
   ALTER COLUMN column_name2 SET NOT NULL,
   ALTER COLUMN column_name3 SET NOT NULL,
   ALTER COLUMN column_name4 SET NOT NULL,
   ALTER COLUMN column_name5 SET NOT NULL...;


what does it mean if the last execution time of a stored procedure is empty

sys.dm_exec_procedure_stats is not a persisted dataset.

It only shows details for queries that currently exist in the Cache. If the SP hasn't been run for some time then the SP will be removed from the cache in favour of more frequently executed queries.

Also the Cache can be emptied by events such as a Server Reboot, and if the SP hasn't been run since the reboot then it won't be there.


SELECT *,
       COUNT(*) AS rvsession_user_id,
       SUM(rvsession_correct=1) AS rvsession_correct
FROM rvsessions
GROUP BY rvsession_user_id


Login as root and grant access privileges to user
GRANT ALL PRIVILEGES ON dbname.* TO 'yourusername'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'yourusername'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
restart mysql /mariadb service.
it will grant user with local as well as remote host(if you want).


Is it recommended to use Identity as an alternative to primary key?

Identity columns and Primary Keys are two very distinct things.  An Identity column provides an auto-incrementing number.  That's all it does.  The Primary Key (at least in SQL Server) is a unique constraint that guarantees uniqueness and is usually (but not always) the clustered key.  Again in MS SQL Server it is also an index (in some RDBMS they are not as closely tied).  As an index it provides faster lookups etc.  Frequently Identity columns are used as the Primary Key if no good natural key exists, but are not a substitute.


How to use a CASE expression in the WHERE clause?

You cannot use the CASE expression in DB2 this way. The result of a CASE expression cannot be a boolean value. Your WHERE clause might look something like this, if one were to blindly translate your code:

WHERE
    COUNTRY_CD = '81930' AND
    LANG_CD = '02' AND (
      ( PARM_ADTR_ID = 'ALL'
        AND (AS_ADTR_ID_P IS NULL OR AS_ADTR_ID_P LIKE '%') )
       OR
      ( (PARM_ADTR_ID != 'ALL' OR PARM_ADTR_ID IS NULL)
        AND AS_ADTR_ID_P LIKE LOC_ADTR_ID )
    ) AND
    DEALER_CDE_VEGA LIKE '8%'

However, if you remove the redundant conditions it can be simplified like so (HT to ypercubeᵀᴹ, who wakes earlier:)

WHERE
    COUNTRY_CD = '81930' AND
    LANG_CD = '02' AND (
      PARM_ADTR_ID = 'ALL' OR AS_ADTR_ID_P LIKE LOC_ADTR_ID
    ) AND
    DEALER_CDE_VEGA LIKE '8%'


According to Importance of the Trace Event Sequence Column and SQL Server 2005 SP2 Changes

  SQL Server 2005 shipped with an Event Sequence stored in a LONG.
  [The MSFT] Support has seen a few traces where the bounds of the signed long are
  exceeded and the Event Sequence becomes negative.

Event Sequence in SQL Server 2005 counts up from 0 to the max of bigint long, and then down from there to 0 again.

"Step 1:  Count up from 0 to 0x7FFFFFFFF
Step 2:  Count down from 0x7FFFFFFF to 0

Start at step 1 again."

  SQL Server 2005 SP2 expands the column to a LONGLONG (signed 64 bit
  integer)

So I guess you're currently in step 2.


should we keep the log file or the data file on the fastest drive?

In my opinion putting the tempdb in the SSD drive without looking at the performance stats is the wrong way to go. SSD drives have high cost/GB and are a premium resource that should be used in the best possible way.

You will probably get better performance moving the most used database files to the SSD, given that you already have broken your database into multiple files and isolated the most problematic objects in their separate filegroup.

That said, SSDs are great for random reads and writes compared to traditional disks, which is the pattern you're seeing mostly in data files. Log files are more subject to sequential reads and writes, which mechanical disks perform just as well as SSDs.

It's true that tempdb is a critical resource for the instance and should be placed in a fast drive, however you're probably going to see a bigger performance gain if you added more RAM (much cheaper than SSDs).

You mileage may vary, but a generic question deserves a generic answer.


What's the procedure to set up username/password on mongodb?

Create an admin to create users:

> use admin
> ( rs.initiate() )
> db.createUser({user:"siteUserAdmin",pwd:"password",roles:[{role:"userAdminAnyDatabase",db:"admin"}]})

Login as userAdmin:

$ mongo -u siteUserAdmin -p password --authenticationDatabase admin

Create an admin for the cluster:

> use admin
> db.createUser({user:"replSetManager",pwd:"password",roles:[{role:"clusterManager",db:"admin"}]})
> db.auth(replSetManager,password)
> rs.status()  -> ok

Create a user that can access all databases but not admin:

> use admin
> var w = { user:"will", roles:["readWriteAnyDatabase"], pwd:"abc"}
> db.createUser(w)

Create a user only for the test database:

> use test
> var a = { user:"joe", pwd:"asdf", roles:["readWrite"]}
> db.createUser(a)

Many roles can be combined and you can create super admins. Information on all the built-in-roles: http://docs.mongodb.org/manual/reference/built-in-roles/

(You can build your own roles also if needed: http://docs.mongodb.org/manual/tutorial/manage-users-and-roles/#create-user-defined-role)


My understanding, reading between the lines, is that BDR replicates row by row, starting after the COMMIT.

Right - but it replicates transactions with the same transaction boundaries as on the original node. So they only become visible at commit time on the downstream.

e.g.

BEGIN;
INSERT INTO x (a) VALUES (1), (2), (3);
INSERT INTO x (a) VALUES (4);
COMMIT;
BEGIN;
INSERT INTO x (a) VALUES (42);
COMMIT;

replicates as

BEGIN;
INSERT INTO x (a) VALUES (1);
INSERT INTO x (a) VALUES (2);
INSERT INTO x (a) VALUES (3);
INSERT INTO x (a) VALUES (4);
COMMIT;
BEGIN;
INSERT INTO x (a) VALUES (42);
COMMIT;

Note, however, that conflict resolution is row-by-row. So if your downstream already had a row with a = 4, and a was the PRIMARY KEY, that INSERT would get ignored but the rest would proceed normally, and the whole xact would commit as an insert of 1, 2, and 4.

  But I need to know the pg_lsn of my last transaction.

Unfortunately, PostgreSQL does not report commit LSNs. I've tried to get optional support for reporting them at commit time, but without success so far. Doing it "right" requires extending the wire protocol, and a few people objected to the backward-compatible way of doing so that I proposed.

The best you can do right now is wait until replay has passed your commit, by doing:

BEGIN;
... do stuff ...;
COMMIT;
SELECT pg_current_xlog_insert_location();

and waiting until replication passes that point. But since this may contain other unimportant work after your commit by the time you call it, and the walsender doesn't rush to replicate unimportant bookkeeping to peers, sometimes it can take a while to pass that LSN. Not usually a problem in production, but can cause stalls in tests and small scale setups.

There's not really an alternative right now though. You can perform a dummy xact after the real one you're interested in to make sure you replicate immediately, at the cost of burning xids, but it must be a real write xact otherwise it'll be silently discarded.


Search SQL Server Execution Plan by cost in SSMS

A much better option would be to use SentryOne's Free Plan explorer. It is 1000% better than SSMS.

You can also have it configured to install it as SSMS addin and then when you right click any plan, it will allow you to open in Plan explorer.