Did you find an answer yet? If not, here is the solution:

IPv6 is disabled by default, you have to enable it for binding on IPv6. The following net-config should work:

net:
    ipv6: true
    port: 27017
    bindIp: 127.0.0.1,192.168.0.15,2001:db8:0:0:0:0:2:1 (IPs are made up)


Temp Table Clustered Key Not Being Honored: Bug or Expected Functionality?

There is no guarantee of ORDER without ORDER BY.

The execution plan for both has "Ordered = False".

This means you may get the results in key order but equally may not.

Specifically see When can allocation order scans be used?

  The only time such a scan will be used is when there’s no possibility
  of the data changing (e.g. when the TABLOCK hint is specified, or when
  the table is in a read-only database) or when its explicitly stated
  that we don’t care (e.g. when the NOLOCK hint is specifed or under
  READ UNCOMMITTED isolation level). As a further twist, there’s a
  trade-off with setup cost of the allocation order scan against the
  number of pages that will b read – an allocation order scan will only
  be used if there’s more than 64 pages to be read.

As the local temp table is not accessible to other connections you get this behaviour without explicitly taking a table lock however the comment about size of table still applies which is why you see the difference in your two cases.

If you need a specific order add an ORDER BY to get scan in key order (with "Ordered = True").


Is it beneficial to include ORDER BY fields in the list of SELECT fields?

There is no benefit to including the ORDER BY columns in the SELECT list. On the contrary, having unrequired columns in the SELECT list incurs a fractional overhead in run time and a larger one in maintenance.


How do I recreate the XE database in Oracle 11g Express?

You should have dropped the owners (AKA schemas AKA users that created the tables) of the "mess of unused tables."  If you dropped the database, no way to restore it (unless you have a backup).  Just recreate the database.  And in the future you can drop just the non-system users, like to drop the Oracle Sample schema SCOTT:

drop user SCOTT cascade;


Postgresql update timestamp without timezone to have timezone

I also don't see the problem

Create sample data,

CREATE TABLE foo(ts) AS VALUES (
  now()::timestamp without time zone
);

Display it,

TABLE foo;
             ts
----------------------------
 2017-09-30 14:25:24.954084
(1 row)

You can see in the above no tz in output. Now let's change to use a with time zone.

ALTER TABLE foo
  ALTER COLUMN ts
  SET DATA TYPE timestamp with time zone;

Here is the output, notice you have a tz of -05

TABLE foo;

              ts
-------------------------------
 2017-09-30 14:25:24.954084-05
(1 row)

What's your result of SHOW TIME ZONE?

Interestingly though, I do see what you're talking about with,

# SELECT now()::timestamp with time zone::timestamp;
            now
----------------------------
 2017-09-30 14:27:53.061616
(1 row)

However, we have to know what you're asking.


HOWTO configure the banner

In the sqlnet.ora file on the server, set the values for:

SEC_USER_AUDIT_ACTION_BANNER
SEC_USER_UNAUTHORIZED_ACCESS_BANNER

You may have to restart the database for the changes to take affect.

Application Requirements

The application must:

use the OCI driver
fetch the banner (using OCI calls)
display the banner

  Oracle Call Interface (OCI) applications can make use of OCI features to retrieve this banner and display it to the user.

The quote is from Oracle Documentation.

Servers cannot force clients to do something.

Will it work with ...?

SQL Plus - Yes.  See Sam's post
SQL Developer - Yes.  See Sam's post
PLSQL Developer - unknown.  You'll have to use the OCI driver and try it.
TOAD - unknown.  You'll have to use the OCI driver and try it.


How to schedule jobs in Sql server 2008 for less than 10 sec?

Create a job that is scheduled to start every minute.  Have the job do something like:

WHILE 1=1
BEGIN
    EXEC dbo.SomeProcedure; /*  this would be the
        name of a stored procedure that does the
        actual work */
    WAITFOR DELAY '00:00:03.000';
END


Only if you have a WAL archive. In that case, you can configure restore_command to catch up from the archive.
Otherwise, rebuilding the standby us your only option.


From the more complete error message:

  Cannot open backup device 'D:\AlomohasebSQL.bak'. Operating system error 2 (Invalid path). Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

Do you know the exact SQL restore statement you are performing? No matter what front-end language you are using, it should be executing something like this against the SQL Server:

RESTORE DATABASE myDatabaseName
FROM FILE = 'D:\myDatabaseBackup.BAK'
WITH (various options here)

In your case, I think the error message indicates that the backup file location ("D:\AlomohasebSQL.bak" in your error above) is incorrect. Obviously, make sure this is a valid drive, path and filename.

IMPORTANT NOTE: This path needs to be visible to the SQL Server, not to wherever you are running this app. (Thanks @ColinMackay for bringing this up). In a related note, check to see that the SQL Server service account has the proper permissions to read this file at that location.

There is a (smaller) possibility that it doesn't know where to place the database files after it restores them. SQL does have a "default" path, but this is sometimes out of date. If necessary you can specify the path to restore the files to:

RESTORE DATABASE myDatabaseName
FROM FILE = 'D:\myDatabaseBackup.BAK'
WITH MOVE 'primarydatafilename' To 'D:\DB\data.mdf',
     MOVE 'logfilename' To 'D:\DB\log.ldf'

You'd obviously need to know the files that are part of that database. If you don't, just use the SSMS restore wizard. There are ways to find that via the RESTORE command, let me know if you need that.


SQL Server high availability fn_hadr_backup_is_preferred_replica returns 0 zero on secondary

This is wrong and should be set to 1.

If you want it set to 1, then you should configure your backup preferences properly.

The reason it is not returning a 1 is because it shouldn't be based on your current preferences. Right now, all of your replicas have the exact same backup priority which means we're going to next look at replica names. Since UK-DR-SQL001\WSS sorts first, that should be the secondary (given that UK-SB-SQL01\WSS is your primary) that the function returns a 1 on.

If you want the backups to happen on a different server, then set the priority appropriately.

Here is an extremely simple repro showing that given your current settings, UK-DR-SQL001\WSS should be the replica which a 1 is returned.

CREATE TABLE #Replicas
(
    ReplicaName VARCHAR(40) NOT NULL,
    BackupPriority INT NOT NULL
)
GO

INSERT INTO #Replicas(ReplicaName, BackupPriority)
Values('UK-DR-SQL001\WSS', 50)
,('UK-SB-SQL01\WSS',50)
,('UK-SB-SQL02\WSS',50)
GO

-- current case UK-SB-SQL01\WSS is the PRIMARY
-- prefer secondary is set
-- default collation assumed

SELECT ReplicaName, BackupPriority
FROM #Replicas
WHERE ReplicaName <> 'UK-SB-SQL01\WSS'
ORDER BY BackupPriority DESC --most important first
, ReplicaName ASC

  I'm running the Ola Hallengren Maintenance Solution and noticed the transaction logs weren't being backed up on the secondary, hence not being truncated, despite the AG backup preference being set to Prefer Secondary

It seems to be that this isn't setup or running on the DR server, or a backup would at least be attempted.


Harvester query to identify databases where the ID has reset

A_I does not go back to zero; it hits the max for the datatype and then is stuck there.  This leads to errors on INSERT that you should be checking for.

This will get you the SELECTs necessary to do the investigation:

SELECT  CONCAT("SELECT  MAX(", column_name, ") AS max_id, '", data_type, "' AS type, ", (column_type LIKE '%unsigned%'), " AS 'unsigned'  FROM  ", table_schema, ".", table_name, ";"
              ) AS stmts
    FROM information_schema.`COLUMNS`
    WHERE extra LIKE '%auto_increment%' ;

Sample row:

 SELECT MAX(AId) AS max_id, 'int' AS type, 1 AS 'unsigned'
 FROM alerts.action;

Running that sample gets:

+--------+------+----------+
| max_id | type | unsigned |
+--------+------+----------+
|      4 | int  |        1 |
+--------+------+----------+

The next step is to understand that int unsigned has a max of 2^32-1, and compare that to the 4.

All of this could be done in a long stored procedure, or in some application code (I leave that as an exercise to the reader).


Mysql replication is enabled but stuck at "waiting for binlog to be updated"

MySQL Replication is running just fine. How do I know ?

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

This indicates that the two replication threads are alive.

To verify this, run the following on the Slave:

SELECT COUNT(1) ReplicationThreadCount FROM information_schema.processlist
WHERE user = 'system user';

If ReplicationThreadCount > 1, then replication is running just fine.

You can also run SHOW PROCESSLIST; and look for two connections whose user is system user

The two states you mentioned for the Master and Slave are normal for a running replication status

As you add data to the Master, you can know that data is coming to the Slave by looking at

Read_Master_Log_Pos
Exec_Master_Log_Pos
Relay_Log_Space

in the output of SHOW SLAVE STATUS\G. These numbers will be changing, which indicate its working.


DBCC SHRINKFILE works with file_id but not with logical name

Revised my answer, testing did not show as a valid reason for it not to work.

Couple of things to check

Are there leading or trailing spaces in the file name?
Did you recently upgrade to SP3 by any chance?

Try switching the recovery model from full, to simple, back to full again. There is an apparent bug when updating to SP3. Changing the recovery model will reset the log sequence number and for whatever reason that clears the issue, at least temporarily.

social.msdn conversation about this


Getting repeated value in a group_concat only if ids are different

First group by both activity_type and prod_id and then another group by activity_type:

SELECT
    a.activity_type AS Activity,
    COUNT(DISTINCT p.id) AS Products,
    CONVERT(GROUP_CONCAT(p.category_id SEPARATOR ',  ') USING utf8)
      AS Categories
FROM
    product AS p
  JOIN
    ( SELECT activity_type
           , prod_id
      FROM activity
      WHERE activity_type <> ''
      GROUP BY activity_type
             , prod_id
    ) AS a
    ON p.id = a.prod_id
GROUP BY
    activity_type
ORDER BY
    Products DESC;

Tested in SQL-Fiddle (thank you @Mr.Radical)

You could also safely replace COUNT(DISTINCT p.id) with COUNT(*) in the above, as for every activity type, there are only distinct product IDs (this is taken care in the internal group by).


#1194 - table 'user' is marked as crashed and should be repaired

Just posted my question and I got a comment answer from my previous question on Stack Overflow:

First try to run the following command: mysqlcheck -u root -p --auto-repair --check --all-databases – Philip Petrov 1 hour ago


DB2 Query Error SQL0204N Even With The Schema Defined

Maybe there is a problem with upper and lower case. I created a table using the following statement:

CREATE TABLE "FOOBAR"."Users"  (col1 INTEGER)

If I try try to run on of the following statements at the CLP I get an error SQL -204

db2 "select * from db2root1.Users"

db2 "select * from db2root1.USERS"

db2 "select * from db2root1.users"

Always the error message  SQL0204N  "DB2ROOT1.USERS" is an undefined name.  SQLSTATE=42704 is returned.

I would suggest to run the following statement and check the spelling of the table name.

select t.tabname from syscat.tables t where lower(t.tabname) like 'user%'

Some tools are creating the tables with a case sensitive table name. This may result in an SQL -204 in some scenarios.

In this case drop and recreate the table without any quotation marks.


Find records with same string with extra character

The SOUNDEX function can be applied to a column as well.

But since

  there's thousands like that

I wouldn't suggest just writing a query to join on a function to do that.

This will likely not perform very well on larger tables:

SELECT *
FROM dbo.vehicle AS v
JOIN dbo.vehicle AS v2
ON SOUNDEX(v2.Owner_Name) = SOUNDEX(v.Owner_Name)
AND v2.Owner_Name <> v.Owner_Name;

I'd rather do something that will make finding this easier in the long-term.

Here's an example:

CREATE TABLE dbo.vehicle (Owner_Name VARCHAR(50));
INSERT dbo.vehicle ( Owner_Name )
SELECT *
FROM (
VALUES
('JACOB JAMISON & JESSICA'),
('JACOB JAMISON M & JESSICA B'),
('BLACKSON BARRINGTON'),
('BLACKSON BARRINGTON H'),
('BRUSTER MICHAEL')
) AS x (Owner_Name);

I'm going to add a computed column based on the function, and then add an index to aid my query.

ALTER TABLE dbo.vehicle ADD Owner_Soundex AS SOUNDEX(Owner_Name);

CREATE INDEX ix_whatever ON dbo.vehicle (Owner_Soundex, Owner_Name);

Validate that everything looks good...

SELECT *
FROM dbo.vehicle AS v

Use a query like this to find imprecise matches:

SELECT *
FROM dbo.vehicle AS v
JOIN dbo.vehicle AS v2
ON v2.Owner_Soundex = v.Owner_Soundex
AND v2.Owner_Name <> v.Owner_Name;


Person-to-Address Relationship Cardinality — One-to-One or Many-to-One (In this context)?

The easiest and more realistic real world scenario to maintain and query is going to be either 1-1 or 1-N, choose whichever is appropriate for your business logic. Several of the products I've worked on have used either of these methods and they are far easier to maintain than trying to get N-1 to work.

Maintaining N-1 for addresses would very challenging because you will inevitably run into misspelling and different grammar scenarios (as you mentioned), but additionally you will get convoluted logic to maintain the single record reference on a table that may not necessarily be static.

Something else we've learned is to avoid separate State, County, and City reference tables as they can create some convoluted logic for querying addresses. Try turning those tables into a single hierarchical Jurisdiction table and store the 'smallest' appropriate reference (usually the town) for the address record. The hierarchy maintains the other references and allows easy expansion when necessary.

If you're using MS SQL they have a HierarchyID type for this specific use case, but you can also create your own with a simple path style NVARCHAR field combined with a type field. Some text indexing and trailing wild card queries will cover all your expansion and filtering needs. Even if using MS SQL I would recommend using the NVARCHAR option alongside the HierarchyID as it generally performs better than the HierarchyID functions in most cases.


MySQL Replication: How to restore master FROM slave?

1) Backups:

You can verify if your backup is successful by tailing the mysqldump for "dump completed successfully" to verify the success of a mysqldump.
Invalid backup is as-good-as (or as-bad-as) no backup. It's a good idea employ a backup-validation process by restoring it to a provisioned location as possible. Do it.
If you want to stick to logical backup, try using with myduper/myloader, which will be quicker than mysqldump.
Again physical backups are faster  than logical and this is specifically true for large sizes. If you can giveup on MySQLDump which is slow for restores and especially if you have all-innodb, you should consider using xtrabackup from Percona (for hotbackup).
Check installing and configuring xtrabackup with holland framework.

2) Restoring master from slave.

As such there are few questions and articles available but i will quickly notedown steps I can makeout for restoring master from  slave.

Considering your master is M and slave is S. (M --> S)
M is down.
S is promoted to Master ( readonly=false, binlogging is enabled)
M is later fixed and ready to join but need a restore.
Restore M from latest backup, mark it as readonly=true.
Upon restore, make it slave of S. (S --> M)
Once it catches up, you may want to failback to original master.
Stop production traffic, Mark M as readonly=false, S as readonly=true.
Mark S as slave of M (should be simple change master to from present binlog of M)
Thus making them active-passive master-master pair.
Move traffic to M.
You might want to break the master-master but it's fine to keep active-passive.
Making sure nothing writes to Passive master.

Hope this helps.


Need help with relationships in MS Access

You'll notice that I do not mention every table.  Hopefully that would not be necessary as you begin to understand the proper normalization patterns.  You should be able to analyze and setup relationships on those other tables using the same patterns.

Once you make these changes, you may find that you have questions about the proper way to get and display related data, for example the contract number for a given CLIN--especially if you've eliminated the redundant fields as I advise.  Look into crafting proper queries and using such queries as the Record Source for forms and reports, etc.  Further questions about such queries would require new questions.  Also, question regarding query specifics and SQL may be more well received on Stack Overflow.

The overall principle here should be proper database normalization.  Although it is often not practical or necessary to adhere to every level of normalization, it should at least a guide for the eventual table and relationship structure.  These concepts can be a bit abstract, but it is still best to study it.
Delete duplicate fields like [CLIN Info Table].[Contract Number], [CLIN Info Table].[Delivery Order Number], [Delivery Order Table].[Contract Number], etc.  The point of a relational database is to always get such information via the relationships.  If you want a [Contract Number] for a given CLIN, then you execute a query which returns the related value from the proper table.
Looking at the relationships as they are now defined (in the original image), you can see 1-to-many relationship between [Delivery Order Table] and the [Base Contract Data], but as I understand your description this relationship is backwards.  You want multiple Delivery Orders per contract, not the other way around.

For this relationship, I suggest 1) deleting the existing relationship between the two tables; 2) eliminating [Base Contract Data].[Delivery Order ID];  3) Creating an enforced relationship between [Base Contract Data].[Contract ID] (the primary key) to [Delivery Order Table].[Contract ID] (the foreign key).

Delete [Delivery Order Table].[CLIN ID] to eliminate confusion.
Since [CLIN Info Table] will have records linked directly to [Base Contract Data] and directly to [Delivery Order Table], then it needs to have two separate "relationships".  This can be accomplished in at least two ways.

The first scheme involves having two foreign keys in [CLIN Info Table], one for each of the tables.  This scheme is only valid if a single [CLIN Info Table] can be linked to only one [Delivery Order ID] record or one [Base Contract Data] record.  If the answer to my comment question is that the exact same [CLIN Info Table] can be linked to multiple Delivery Orders or Contacts, then you'll need to skip to the alternative scheme.

One of them you already have established correctly: [CLIN Info Table].[Delivery Order ID] (foreign key) already related to [Delivery Order Table].[Delivery Order ID] (primary key).  Leave that as is.
Next remove the existing relationship between [CLIN Info Table] and [Base Contract Data] and delete [Base Contract Data].[CLIN ID].  You'll notice this is the same type of incorrect relationship as in #2 above.
Add a relationship between the existing fields [Base Contract Data].[Contract ID] (the primary key) to [CLIN Info Table].[Contract ID] (the foreign key).

The alternative scheme satisfies the next level of normalization.  It may also be necessary if the exact same [CLIN Info Table] can be linked to multiple other-table records.  It requires a third table for establishing each relationship.  It avoids the problem of having multiple null foreign keys.  I'm sure this topic could be heavily debated, but I personally think it is just a matter of preference.  Adding a third table in a relationship necessarily makes queries more complex.  Although this pattern is meant to eliminate certain other relationship problems, it also establishes the ability to have orphaned child records, which if a huge problem, you'd need some way of managing those.

Eliminate foreign key fields from [CLIN Info Table]
Create a new table named something like [Oder CLIN] with only two required fields:  [Delivery Order ID] and [CLIN ID].  You need to create appropriate indexes for these field, depending on your answer to whether the same exact CLIN can be linked to different Delivery Orders.
Create another new table named something like [Contract CLIN] with only two required fields:  [Contract ID] and [CLIN ID].  You need to create appropriate indexes for these field, depending on your answer to whether the same exact CLIN can be linked to different Contracts.
Create relationships between the foreign keys in the new tables to their corresponding primary tables.


The query below will return the maximum of the difference age from the guessed age:

SELECT MAX(DiffAge) AS MaxDifference
FROM (
    SELECT (`avg` - `age`) AS DiffAge
    FROM `AgeTest`
) AS Result

Sample execution with given sample data:

CREATE TABLE `AgeTest` (`age` INT, `avg` numeric(18,2));

INSERT INTO `AgeTest` (`age`, `avg`) VALUES (18,34.45);
INSERT INTO `AgeTest` (`age`, `avg`) VALUES (32,33.23);
INSERT INTO `AgeTest` (`age`, `avg`) VALUES (45,28.21);

SELECT MAX(DiffAge) AS MaxDifference
FROM (
    SELECT (`avg` - `age`) AS DiffAge
    FROM `AgeTest`
) AS Result

Working Demo: http://rextester.com/BSEEG25743


What is the CURRENT_DATE or current date value function for SQL Server?

You can use either GETDATE (return type datetime) or SYSDATETIME (return type datetime2), with the difference being the precision up to nanoseconds for SYSDATETIME().

Example:

SELECT GETDATE() fn_GetDate, SYSDATETIME() fn_SysDateTime

Results:

fn_GetDate                 fn_SysDateTime
2018-06-27 10:31:18.963    2018-06-27 10:31:18.9659170

See Date and Time Data Types and Functions (Transact-SQL) in the product documentation.

For completeness, SQL Server also recognises CURRENT_DATE as mentioned in the question, as an ODBC scalar function:

SELECT {fn CURRENT_DATE()};

This returns varchar(10), so would need an explicit cast or convert to the date data type:

SELECT CONVERT(date, {fn CURRENT_DATE()});

The built-in functions are recommended over ODBC scalar functions.


Discover missing foreign keys and/or indexes

I remembered seeing a query sometime ago in a post so quick searching resulted in:

CREATE FUNCTION pg_temp.sortarray(int2[]) returns int2[] as '
  SELECT ARRAY(
      SELECT $1[i]
        FROM generate_series(array_lower($1, 1), array_upper($1, 1)) i
    ORDER BY 1
  )
' language sql;

  SELECT conrelid::regclass
         ,conname
         ,reltuples::bigint
    FROM pg_constraint
         JOIN pg_class ON (conrelid = pg_class.oid)
   WHERE contype = 'f'
         AND NOT EXISTS (
           SELECT 1
             FROM pg_index
            WHERE indrelid = conrelid
                  AND pg_temp.sortarray(conkey) = pg_temp.sortarray(indkey)
         )
ORDER BY reltuples DESC
;

From http://mlawire.blogspot.nl/2009/08/postgresql-indexes-on-foreign-keys.html

That will give you what you want.

And also found https://github.com/pgexperts/pgx_scripts/blob/master/indexes/fk_no_index.sql but I haven't used this myself.


Are nonclustered indexes used when joining tables?

Have a look at the query execution plan, that should tell you if it is using an index.

I don't know why a sort join couldn't use an index, keys can be read from the index and then sorted if required, which saves scanning the table.


PLPGSQL: Cannot use parameters inside a function query

Well in your function/procedure you are passing a string to the crosstab table function.
In the context of the string the value for mth can't be passed on as a variable in the function. You might have to concatenate the string like this:
CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = ' || mth || '
AND extract(year from starts) = ' || yr || '
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
)
AS (
week int,
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

The relevant parts being:
...
WHERE extract(month from starts) = ' || mth || ' -- <<< HERE
AND extract(year from starts) = ' || yr || '     -- <<< AND HERE
GROUP BY week, dow
...

This way the value can be concatenated together with the string and executed in the context of the crosstab table function.
Working Solution
A working example can be found at this db<>fiddle
Create Table
create table events(
starts date,
eventtext varchar(20)
);

Insert Sample Data
insert into events(starts, eventtext)
values
('2020-03-01', 'test1'),
('2020-03-01', 'test2')

Create Function/Procedure
CREATE OR REPLACE FUNCTION get_month_events(
yr int,
mth int,
OUT week int,
OUT sun int, OUT mon int, OUT tue int, OUT wed int,
OUT thu int, OUT fri int, OUT sat int
)

RETURNS SETOF RECORD AS
$$
BEGIN
RETURN QUERY
SELECT * FROM crosstab('
SELECT
extract(week from starts) as week,
extract(dow from starts) as dow,
count(*)
FROM events
WHERE extract(month from starts) = ' || mth || '
AND extract(year from starts) = ' || yr || '
GROUP BY week, dow
ORDER BY week, dow',
'SELECT * FROM generate_series(0,6) AS dow'
)
AS (
week int,
sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY week;
END;
$$
LANGUAGE plpgsql;

Select Function/Procedure
select get_month_events(2020,03)

Output

get_month_events
----------------
(9,2,,,,,,)


How to use Weekday() function inside join in MySQL?

SELECT ...
FROM order o
JOIN order_frequency of ON CASE o.frequency
                           WHEN 111 THEN WEEKDAY(of.orderDate)=0
                           WHEN 234 THEN WEEKDAY(of.orderDate) BETWEN 1 AND 3
                           END
...


SQL Server Management Studio 2012 Application Cannot Start

When I had this issue, I was able to resolve the issue by deleting the folder C:\Users\<User account>\AppData\Roaming\Microsoft\SQL Server Management Studio\.

This may remove any personalized settings, etc., returning you to how SSMS works on a fresh install.


How can i update my database from another database using temporary tables?

You can indeed use stored procedures to refresh the data in your database.  It just means coding the INSERTs and UPDATEs appropriate to your data.

If you are replacing the existing data with new data, you could use:

INSERT INTO ... SELECT FROM ... syntax, after deleting the existing data, to pull the data from your query and insert it into the now empty table. Of course, you could run individual steps to update many tables, one at a time.

Syntax documentation on INSERT INTO ... SELECT FROM is here: https://dev.mysql.com/doc/refman/5.7/en/insert-select.html

If you want to UPDATE ... SELECT FROM for only some values in a table, you could follow the different examples here:

https://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query

Also general UPDATE syntax is described here: https://dev.mysql.com/doc/refman/5.7/en/update.html


Update column based on other column with concat by underscore and manage duplicate value MySQL query for mysql 5.7.27

akina: You need to emulate ROW_NUMBER(). There are 2 general ways:

Subquery which uses 2 table references (see, for example, Order by is ignored if inside a derived table of a update, with the correction that the subquery must be moved to main query's UPDATE clause); and
Subquery which uses user-defined variables (see, for example, query to replace text with sequential numbers).


Decrypt T-SQL log backup header and read LSN

Could anyone confirm or denies that restoring the header only wouldn't
  affect the sys.fn_dblog or anything else?

RESTORE HEADERONLY doesn't target any database in its syntax:

RESTORE HEADERONLY FROM <backup_device>

It reads the backup device and returns data to the client about what's in the backup.  It won't affect your databases in any way.


AUTO_UPDATE_STATISTICS and FULLSCAN in SQL Server 2008 R2

Auto updates always use sampling with the percentage being calculated from the number of rows in the index.

From your SO post:

 Just FYI, the two indexes were PKs of tables having 280M+ and 55M+ rows, respectively.

I guess this is a good example of why SQL Server won't allow a FULLSCAN auto update. The impact of reading that many rows unplanned could cause as many headaches as it solves.

Two options:

Disable auto update and schedule the full scan.
Nudge/hint/sledgehammer the query optimiser in to using the preferred plan, without relying on statistics being 100% accurate.

John Sansom was trying to lead you down the second route with his comment on your SO post, your response suggests you're not keen on the idea?

  I don't really see what that has to do with the actual question...
  anyway, consider that the suboptimal plan used two clustered-index
  scans whereas in the optimal one they are not present (replaced by
  seeks on the same indexes).

If it's not viable to full scan frequently enough to keep the statistics current and maintain the good execution plan, there are other options. The right options can be suggested given the right information on the problem execution plan. A couple of suggestions to start:

Ascending Keys and Auto Quick Corrected Statistics - A not uncommon statistics issue with large tables is ascending data, dates and identity columns for example. Filters on data created after the last statistics update can result in execution plans expecting very low selectivity when in fact large numbers of rows exist.

Hints and/or plan guides - OPTIMIZE FOR and RECOMPILE may be applicable to your scenario. KEEPFIXED PLAN could be used in conjunction with forcing a recompile immediately following a scheduled statistics update.

Plan Forcing - Capture your good plan, attach it to the problem query.

Edit: update following comment regarding this being a vendor application

This is exactly why plan guides exist, giving the DBA a chance to influence execution plans when modifying source code is not possible. There are two approaches (both code samples taken from BOL):

Create an object (if the target is a stored procedure) or SQL Plan (if matching a single statement) guide, as outlined in Understanding Plan Guides and either apply OPTION hints or attach the good execution plan by capture.

USE AdventureWorks2008R2;
GO
SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;
GO
DECLARE @xml_showplan nvarchar(max);
SET @xml_showplan = (SELECT query_plan
    FROM sys.dm_exec_query_stats AS qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
    CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, DEFAULT, DEFAULT) AS qp
    WHERE st.text LIKE N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;%');

EXEC sp_create_plan_guide
    @name = N'Guide1_from_XML_showplan',
    @stmt = N'SELECT City, StateProvinceID, PostalCode FROM Person.Address ORDER BY PostalCode DESC;',
    @type = N'SQL',
    @module_or_batch = NULL,
    @params = NULL,
    @hints = @xml_showplan;
GO

Create a guide from the cached execution plan, while it's "good", using sp_create_plan_from_guide

USE AdventureWorks2008R2;
GO
SELECT WorkOrderID, p.Name, OrderQty, DueDate
FROM Production.WorkOrder AS w
JOIN Production.Product AS p ON w.ProductID = p.ProductID
WHERE p.ProductSubcategoryID > 4
ORDER BY p.Name, DueDate;
GO
-- Inspect the query plan by using dynamic management views.
SELECT * FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO
-- Create a plan guide for the query by specifying the query plan in the plan cache.
DECLARE @plan_handle varbinary(64);
DECLARE @offset int;
SELECT @plan_handle = plan_handle, @offset = qs.statement_start_offset
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
CROSS APPLY sys.dm_exec_text_query_plan(qs.plan_handle, qs.statement_start_offset, qs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';

EXECUTE sp_create_plan_guide_from_handle
    @name =  N'Guide1',
    @plan_handle = @plan_handle,
    @statement_start_offset = @offset;
GO
-- Verify that the plan guide is created.
SELECT * FROM sys.plan_guides
WHERE scope_batch LIKE N'SELECT WorkOrderID, p.Name, OrderQty, DueDate%';
GO


Are two logically equal indices physically the same index?

How can the 2 indexes be physically the same? The values of id could be more example:

1,2,3,4,6,7,8,9

while the parent_id values could be:

NULL,1,1,8,NULL,NULL,2,NULL

So the answer to the question is: No, they would be two completely separate and different structures. It doesn't make any other sense. Even if the parent_id were all, one by one, identical to the id values, it still would not make any sense either. Because the server cannot know or be sure that the values will be always identical. Rows can be updated and new rows with different id - parent_id values can be inserted. So two separate indexes have to be used.


Why would a table use its primary key as a foreign key to itself

Like you said. A FOREIGN KEY constraint referencing the same table is typically for a hierarchy structure and it would use another column to reference the primary key.  A good example is a table of employees:

EmployeeId    Int     Primary Key
EmployeeName  String
ManagerId     Int     Foreign key going back to the EmployeeId

So in this case there is a foreign key from the table back to itself.  All managers are also employees so the ManagerId is actually the EmployeeId of the manager.

Now on the other hand if you mean someone used the EmployeeId as the foreign key back to the Employee table then it was probably a mistake.  I did run a test and it's possible but it wouldn't have any real use.

CREATE TABLE Employee (EmployeeId Int PRIMARY KEY,
                        EmployeeName varchar(50),
                        ManagerId Int);

ALTER TABLE Employee ADD CONSTRAINT fk_employee
    FOREIGN KEY (EmployeeId) REFERENCES Employee(EmployeeId);


The first option you present is a "NoSQL" design pattern & doesn't allow for easy querying in a normal RDBMS.

Your second option is the most suitable. Have a 3rd table that tracks which products are in which store. Possibly called "Stock" - you could add a count to this if you wanted to track how many of each item each store has.


You shouldn't care about whether it's 17:00 or 5:00 PM. How SQL Server ends up storing the time is irrelevant (formatting is for the presentation tier) - you should be focusing on converting the data to the right time value and storing it as the right data type (in this case probably datetime, but in modern versions of SQL Server, just time).

DECLARE @s char(5) = '0500p', @x datetime;

-- first convert it to a datetime-friendly format
SELECT @x = STUFF(@s,3,0,':') + 'm';

-- now some options for output:
SELECT @x, CONVERT(CHAR(5), @x, 108);

Results:

1900-01-01 17:00:00.000        17:00

Be sure that you consider formatting the value as a string only in the presentation tier. It's not really SQL Server's job to handle formatting for display, and your client tools are better suited for that translation anyway.


Does Postgres read blocks sequentially, even if a disk is high-latency?

In the case of bitmap heap scan, it can issue async block reads.  This is controlled by effective_io_concurrency.


Understanding "max_wal_size" and "min_wal_size" parameters default values from postgresql.conf file

These are 16 MB WAL segments by default. The manual:

  The system physically divides this sequence into WAL segment files,
  which are normally 16MB apiece (although the segment size can be
  altered when building PostgreSQL)

So, it's just default values as advertised:

select name, setting, setting::int * 16 || 'MB' AS setting_in_mb
from pg_settings
where name in ('min_wal_size', 'max_wal_size');

unit in pg_settings is present when it's an actual base unit like second or MB. In this case the "unit" would be "WAL segments", which can be configured before compiling Postgres. The unit column is just NULL for those.


Database Normalization: A Case Of The NULLs

I beg to disagree with this author.  The results are expected, but may not be the desired values.  Null values don't work well with logical comparisons as the value is unknown, as is the result of a comparison.

If you are using nullable columns as query criteria, you do need to handle them appropriately.  These are some of method I have used:

WHERE   (nullable = ? OR nullable IS NULL)
WHERE   coalesce(nullable, -1) = ?
WHERE   nvl(nullable, -1) = ?

EDIT: I do agree with the author that avoiding NULL values on searchable fields is desirable, but designing a database without NULL columns can result in other issues.  As design goals, avoiding duplicate and NULLs are appropriate,  However a database row is a representation of something else, sometimes an imperfect representation.

For code lists values like "Unspecified", "Unknown", "Not applicable" and "To be determined" may be appropriate.  If NULL has meaning, then it is may be appropriate to use that meaning rather than NULL for the column value.

You may also want to consider whether the data has value.  I do encounter data which has been gathered because it could be, but is neither used nor useful.  Consider the privacy issues or other legal issues resulting from gathering the data.

Nullable columns are common when subtypes occur in the same table.  Understanding the subtypes helps in deciding on the appropriate approach to handling these columns.  Using separate tables for each subtype can make the design more complex.


Difference between Relational database and time series database?

Time series describes the type of data you will be storing.  The use case that springs to mind is experimental or research data being collected at different time points.  You may have seen the term "Data Warehouse". That too is another type of data.  As it happens, you can store these data "types" in a relational database.  For time series data this may not be optimal.  There are a number of vendors in the space that cater directly to time series data.  See the wiki page referred to above.  Good luck.


I searched the Internet for an answer last night and found nothing. Of course, today after posting this question I try again--and find an adequate response almost immediately.
The gist of it? Put your commands in a text file and direct it to SQLite using the input file descriptor, or just script everything in a bash script.
First Method:
sqlite3 database.db < commands.txt
Second Method:
#!/bin/bash --
sqlite3 -batch $1 <<"EOF"
CREATE TABLE log_entry ( <snip> );
.separator "\t"
.import logfile.log log_entry
EOF

And then on the command line:
import.sh database.db


How do I selectively purge MySQL table rows using a list of column values?

DELETE FROM MyBigUberTable WHERE Link NOT IN (...);

That should do it. Just replace the ellipsis with a comma-separated list of of Link values (or a SELECT statement that produces said list).


Do you have a Central Management Server? If so, you could use this query and run it against all your sql instances on all your servers:

SELECT CONVERT(nvarchar(128), SERVERPROPERTY('Servername')) AS
Server,
msdb.dbo.sysjobs.job_id,
msdb.dbo.sysjobs.name,
msdb.dbo.sysjobs.enabled AS Job_Enabled,
msdb.dbo.sysjobs.description,
msdb.dbo.sysjobs.notify_level_eventlog,
msdb.dbo.sysjobs.notify_level_email,
msdb.dbo.sysjobs.notify_level_netsend,
msdb.dbo.sysjobs.notify_level_page,
msdb.dbo.sysjobs.notify_email_operator_id,
msdb.dbo.sysjobs.date_created,
msdb.dbo.syscategories.name AS Category_Name,
msdb.dbo.sysjobschedules.next_run_date,
msdb.dbo.sysjobschedules.next_run_time,
msdb.dbo.sysjobservers.last_run_outcome,
msdb.dbo.sysjobservers.last_outcome_message,
msdb.dbo.sysjobservers.last_run_date,
msdb.dbo.sysjobservers.last_run_time,
msdb.dbo.sysjobservers.last_run_duration,
msdb.dbo.sysoperators.name AS Notify_Operator,
msdb.dbo.sysoperators.email_address,
msdb.dbo.sysjobs.date_modified,
GETDATE() AS Package_run_date,
msdb.dbo.sysschedules.name AS Schedule_Name,
msdb.dbo.sysschedules.enabled,
msdb.dbo.sysschedules.freq_type,
msdb.dbo.sysschedules.freq_interval,
msdb.dbo.sysschedules.freq_subday_interval,
msdb.dbo.sysschedules.freq_subday_type,
msdb.dbo.sysschedules.freq_relative_interval,
msdb.dbo.sysschedules.freq_recurrence_factor,
msdb.dbo.sysschedules.active_start_date,
msdb.dbo.sysschedules.active_end_date,
msdb.dbo.sysschedules.active_start_time,
msdb.dbo.sysschedules.active_end_time,
msdb.dbo.sysschedules.date_created AS
Date_Sched_Created,
msdb.dbo.sysschedules.date_modified AS
Date_Sched_Modified,
msdb.dbo.sysschedules.version_number,
msdb.dbo.sysjobs.version_number AS Job_Version
FROM msdb.dbo.sysjobs
INNER JOIN msdb.dbo.syscategories ON
msdb.dbo.sysjobs.category_id = msdb.dbo.syscategories.category_id
LEFT OUTER JOIN msdb.dbo.sysoperators ON
msdb.dbo.sysjobs.notify_page_operator_id = msdb.dbo.sysoperators.id
LEFT OUTER JOIN msdb.dbo.sysjobservers ON
msdb.dbo.sysjobs.job_id = msdb.dbo.sysjobservers.job_id
LEFT OUTER JOIN msdb.dbo.sysjobschedules ON
msdb.dbo.sysjobschedules.job_id = msdb.dbo.sysjobs.job_id
LEFT OUTER JOIN msdb.dbo.sysschedules ON
msdb.dbo.sysjobschedules.schedule_id = msdb.dbo.sysschedules.schedule_id
WHERE msdb.dbo.sysjobs.enabled = 1
AND msdb.dbo.sysjobservers.last_run_outcome = 0


Mysql 5.5.38 ERROR 1062 (23000): Duplicate entry '' for key 'anykey'

If you want to hunt down what key combinations are preventing the unique key from working, run this

SELECT add1, city, state,COUNT(1) rowcount
FROM mytable GROUP BY add1, city, state
HAVING COUNT(1) > 1;

To see the actual rows, run this

SELECT A.* FROM mytable A INNER JOIN
(SELECT add1, city, state,COUNT(1) rowcount
FROM mytable GROUP BY add1, city, state
HAVING COUNT(1) > 1) B USING (add1, city, state);

This will give you the opportunity to clean up the data

Alternatively, you may decide to make it a regular nonunique index.


Which one is more efficient: select from linked server or insert into linked server?

Suppose I have to export data from one server to another.

Best is to use

IF you want all data use Backup / Restore; BCP OUT & BCP IN or SSIS
IF you want subset of data (some tables only) use SSIS or BCP OUT & BCP IN

TO move data, depending on the amount/size of data and n/w bandwidth, Linked server will kill the performance.

  Executing in source server Or executing in target server - Which one will be faster and consume fewer resourcers in total (both source and target server)?

-- Executing in source server:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

This is called PUSHING Data as you are executing the query on source server and pushing the data into destination server. This will be expensive operation.

--- executing in target server

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

This is called PULLING Data as you are executing the query on destination server and pulling data from source server. This will be a lot faster and less resource intensive compared to the former one (depending on how much data is being pulled).

In the case of the pull method, using SQL Profiler you will see that a single SQL statement is executed across the linked server (source server), and the resultset is pulled over from source server  to destination server which is a huge performance gain over PUSH method.

Another point to be noted is :

Between Linked server (4 part naming convention used servername.databasename.schema.tablename a.k.a Distributed Queries) and OPENQUERY, generally OPENQUERY will be fast. Why ?

For Linked Server - Query optimizer creates an execution plan by looking at the query nomenclature and breaks it into remote and local queries. Local queries are executed locally and data for remote queries are collected from the remote servers, scrubbed locally, combined together and presented to end user as single record set.

For OPENQUERY - Executes the specified pass-through query on the specified linked server. SQL Server sends pass-through queries as un-interpreted query strings to an OLE DB data source . Hence, SQL won’t apply any kind of logic on the query and won’t try to estimate what that query would do, it would simply pass the specified query as it is to the target linked server. Open queries are useful when you are not referencing multiple servers in one query. It’s generally fast as SQL does not  break it into multiple operations and does not perform any local action on the output received.

Excellent reading references :

Linked servers and performance impact: Direction matters!
Distributed Queries
OPENQUERY


Can I setup several mariadb clusters, each for a schema

If the Pis are the source of the data, use "multi-source" replication from them to MariaDB.

Then have 3 nodes for the cluster (2 is mostly useless), and have all the data for both databases on all the nodes.

Or, more simply, the 2 Pis replicate to a single Master (no cluster).


count (distinct item_id)

SELECT
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date,
  count(*) Total_Sold,
  count (distinct item_id) as distinct_item_id

FROM
  `project.dataset.items`
GROUP BY
  FORMAT_TIMESTAMP('%Y-%m', Sell_Date) AS Sell_Date
ORDER BY
  Sell_Date


You need a slightly different query, since spatial indexes are tied to internal tables, not the base object. This does not aggregate or account for partitions, nor did I tweak it to drop into your current code; just meant to point you in the right direction. You can leave off either where clause for discovery obviously. And the joins to indexes and objects are only necessary if you need to output the table name, or index name, or individual rows per index - if you just want a sum of row counts or other data from the partition-related DMVs for all special indexes for a table - which will include FTS and XML indexes - you can leave out both joins).

SELECT
  [internal_table_name] = N'sys.' + t.name,
  table_name = o.name,
  index_name = i.name,
  i.index_id,
  p.*
FROM sys.internal_tables AS t
INNER JOIN sys.objects AS o
ON t.parent_object_id = o.[object_id]
INNER JOIN sys.indexes AS i
ON t.[object_id] = i.[object_id]
INNER JOIN sys.partitions AS p -- dm_db_partition_stats probably more useful
ON t.[object_id] = p.[object_id]
AND i.index_id = p.index_id
WHERE o.name = N'<Name of table with spatial column(s)>'
  AND i.name = N'<Name of spatial index>';


Your UPDATE statement is wrong. It is updating all the records in the table because it doesn't have any WHERE clause. If you want to update only the current row, you need to use the updated/inserted records (like in Jack's example: REFERENCING NEW AS N_ROW and use it as filter).


SQL Server - why are window functions not permitted in update statements?

Window functions aren't permitted in UPDATE statements because UPDATE isn't compatible with SELECT or ORDER BY.

Window functions are like scoped SELECT statements that re-examine the relevant rows and apply conditions like PARTITION BY and ORDER BY. In addition, many window functions require an ORDER BY clause (ROW_NUMBER, LAG, and FIRST_VALUE, for example).

UPDATE statements use SET instead of SELECT, so SELECT is not allowed anywhere in the same query level. Any SELECT appearing with UPDATE must be contained in a subquery.

Disallowing the ORDER BY makes sense considering an UPDATE statement is indifferent to the order in which it updates rows.

There's no inherent downside to using a CTE or other subquery as a workaround to get an UPDATE to use a window function. That's the common practice advocated by T-SQL experts like Itzik Ben-Gan. (See page 29 of his book, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions where he covers this exact scenario.)


Is it possible in Oracle to trace SQL statements that result in errors?

While I'm not sure if it's the easiest way, you can use an after servererror on database trigger to log all errors to a table.

From Log all database errors to a table by Ferdous Nadvi:

drop trigger log_errors_trig;
drop table   log_errors_tab;

create table log_errors_tab (
 error     varchar2(30),
 timestamp date,
 username  varchar2(30),
        osuser    varchar2(30),
        machine   varchar2(64),
 process   varchar2(8),
 program   varchar2(48));

create or replace trigger log_errors_trig
 after servererror on database
declare
 var_user     varchar2(30);
 var_osuser   varchar2(30);
 var_machine  varchar2(64);
 var_process  varchar2(8);
 var_program  varchar2(48);
begin
 select username, osuser, machine, process, program
 into   var_user, var_osuser, var_machine, var_process, var_program
 from   sys.v_$session
 where  audsid = userenv('sessionid');

 insert into log_errors_tab
   values(dbms_standard.server_error(1),sysdate,var_user,
          var_osuser,var_machine,var_process,var_program);
end;
/

See also Oracle System Event Triggers on PSOUG and the official documentation for system event triggers


Recreate existing database with a different charset

In your current setup, you seem to try to convert directly from NONE to UTF8, when this is done, the bytes stored as NONE are checked if they are valid UTF8, and then stored as-is. A malformed string error means that the data in a column NONE was not actually a valid combination of bytes in UTF8.

You will need to find out what the actual character set is of the data before you can convert it correctly (and even if you don't get errors, that does not necessarily mean the conversion was logically correct). You may want to try if the data currently stored isn't a different character set (for example WIN1252 is common in Western Europe and the US).

When you pump manually, this means that you either have to explicitly cast data. That is from NONE to WIN1252 (or whatever it is) and then to UTF8. Eg if the data is stored in column1, then you'd use

cast(cast(column1 as varchar(<length>) character set WIN1252) as varchar(<length>) character set UTF8)

I have no experience with fbclone, but the fact you specify UTF8 twice suggests that one is the source and the other the target character set. You may want to try if changing one or the other to WIN1252 (or a different one) to see which works.

Make sure to check the data afterwards.

In the worst case, you may have NONE data that is actually a mix of different character sets, and in that case you may have to inspect and convert each row manually.


Backup to Azure Blob Failing with Hallengren Framework

I have added support for BLOCKSIZE, and MAXTRANSFERSIZE, when backing up to Azure Blob Storage block blobs (using Shared Access Signature).

https://ola.hallengren.com/versions.html


For most workloads, once a processor, the number of vcpus, and the amount of vRAM has been chosen for the vm, the best choice will vcpus and vRAM spread evenly across the smallest number of vNUMA nodes which allow the vcpus and vRAM to align with physical resources on the same number of physical NUMA nodes underneath.
This blog post from Mark Achtemichuk explains this idea quite well.
Virtual Machine vCPU and vNUMA Rightsizing – Guidelines
https://blogs.vmware.com/performance/2017/03/virtual-machine-vcpu-and-vnuma-rightsizing-rules-of-thumb.html
So, for SQL Server Standard Edition which allows the lesser of 4 virtual sockets or 24 vcpus: all other things being equal, all vcpus in a single vNUMA node, with all vcpus and vRAM coming from a single underlying physical NUMA node is prefered.  If the amount of RAM(or the vcpus) for that vm won't fit in one physical NUMA node, split it across 2 NUMA nodes.  Only go to 4 NUMA nodes if you have to.  And... based on the analytics workloads I work with... try to stay on 4 NUMA nodes as long as possible before going to 8.
The blog post from from Mark Achtemichuk also links to some material from Frank Denneman - he delves into some of the specific VMware parameters that may be needed to balance a VM as desired.
While VMware has in the recent past recommended a default setting cores-per-scoket=1 for all Windows VMs including SQL Server up to 64 vcpus* to let ESXi automatically plan the vNUMA configuration, they are transitioning to recommending setting cores-per-socket to the expected number of vcpus in a vNUMA node.
I have seen one particular SQL Server workload and system where even though all vcpus and vRAM could fit on one vNUMA node on a single physical NUMA node, it was intentionally placed on 2 vNUMA nodes balanced across 2 physical NUMA nodes.  This was done because a partiticular memory object was only partitioned at the node level and could not be dynamically partitioned to the cpu level as many objects can be.  The result, while all vcpus were in a single vNUMA node, was significant spinlock contention that was affecting performance.  This spinlock contention was relieved by splitting the same number of vcpus into two vNUMA nodes (and balancing those vNUMA nodes across 2 physical NUMA nodes).  That is a very exceptional case, however, and quite frankly I expect them to transition back to a single vNUMA node on a single physical NUMA node as that workload matures.
*For Windows VMware VMs above 64 vcpus, cores-per-socket muct be set higher than one in order to conform to the Windows 64 socket limit.


You need to change the my.cnf file to include the single line under [mysqld] in your file

max_allowed_packet=16M

now restart the MySQL service and you are done.

You can see its current value in mysql like this:

SHOW VARIABLES LIKE 'max_allowed_packet'

You can try changing it like this, but it's unlikely this will work on shared hosting:

SET GLOBAL max_allowed_packet=16777216;

Also please read this: http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html


It's not normalized. StudentMarks has one value for homework. That does not sound ideal.

Makes more sense, to me, to have a another table GRADES, or the like. CREATE TABLE grades ( category text, grade float ). Then you can insert into it VALUES ('homework', 100) keep all those grades and then later at query time avg() them.


A SQL Server 2008 R2 mirror is not itself readable.

If you are using Enterprise Edition, you can create a named snapshot database from the mirror.  This allows you to create a point-in-time readable database that can be used for pretty much any query that does not do any updates.  (Though you could use another database (tempdb or a workdb) to do auxiliary work that would not fit the read-only snapshot.)

See: https://msdn.microsoft.com/en-us/library/ms175158(v=sql.105).aspx

When the snapshot gets 'old' enough you create a new snapshot database. You will eventually need to drop the older snapshot, but it is possible to support several snapshots. (This depends on the amount of activity in your database and server.)

If you use Standard Edition and have the same need, look into log shipping using RESTORE with STANDBY.  You will need to manage when the next restores get made in order to use the STANDBY feature.

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

It says of the STANDBY option: "The standby file allows a database to be brought up for read-only access between transaction log restores and can be used with either warm standby server situations or special recovery situations in which it is useful to inspect the database between log restores."

Therefore, this method requires you to delay the restore of logs to match the schedule for how long you want to keep this point-in-time in action.  A LOG Restore will end the STANDBY and restore to the following point, which could be restored with a new STANDBY period.

Depending on your needs one of these approaches can give you a readable point-in-time  database. No updates become visible in the database for the period of (a) the snapshot, or (b) the period between restoring updates.


I've just concluded a week of back and forth with Microsoft's gold partner support. They have confirmed that this situation is not getting any better and that these slow deletes are the normal behaviour of SQL Azure/2016.


Adding a Far DR secondary server Log shipping on already configured SQL server

The issue is copying a full backup might take more than 12 hours from primary to really far DR and there is 4 schedules of daily full backup on Primary each of 6 hours interval.
... configure the log shipping on the Far DR without any delay ...

These two are competing items. If it takes 12 hours to transfer and you must transfer full backups every time then I don't see how that will fit in a 6 hour window, let alone be able to do anything of use with it.
You'll either need to push back on doing this or ask for better bandwidth.
If, however, you don't have to send the same full backup each time, then a one time full with logs between may be possible depending on the rate of change in the database. Obviously this all depends on what the requirements say.

[comment]...consider using mirroring or AlwaysOn High Availability Groups instead.

Potentially, depending upon the requirements (which we currently don't have or know), although this could lead to other undesirable consequences if the latency is very high. Availability groups do not like latency.


Can full drive backup be used instead of MSSQL database backup?

Backup has no special functionality related to MSSQL.

Typically server backup programs will coordinate with SQL Server through the Windows Volume Shadow Copy Service (VSS) to ensure that SQL Server places the database files in a consistent state before volume snapshots are taken.  At a minimum backup programs should create backups that are consistent on a per-volume basis.

However, even in an enterprise environment where you have trusted professionals performing server backups using a configuration you can verify, very few DBAs would actually rely on server backups.  Remember backups alone are irrelevant.  Restores are what you actually care about.

So how are you going to perform a restore if you rely on server backups?  Not easily, that's for sure.

So take backups.  Then download them regularly from your hoster, or upload them to a cloud storage provider, eg:

Sign up for an Azure account
Provision a storage account (LRS + Cool default tier)
Use SQL Server's Backup to URL feature to take database backups directly to your storage account.


I sometimes find this technique useful for judging the performance of a query, or for comparing two possible queries.  It is especially helpful before the table is large enough to get reliable timings.

FLUSH STATUS;
SELECT ... ;
SHOW SESSION STATUS LIKE 'Handler%';

That will give a few counters.  If I see numbers that are about equal to the number of rows in a table I am selecting from, then I know there was a table scan.  Numbers near the resultset size tend to be more beneficial.  Handler...write... indicates that a temp table needed to be generated, and the number says how big it was.

Even without understanding what the numbers represent, it is often 'obvious' which of two SELECTs is better.


Set PostgreSQL table to LOGGED after data loading

Problem

I believe currently SET LOGGED rewrites the table using the WAL (essentially doing the whole operation), and rewrites the indexes.

So I found a thread about this on the lists

    A new relfilenode is filled with the data - the old one, including the init fork, gets removed by the normal mechanics of rewriting rels.

  There was a long thread about it on -hackers. Doing it without a rewrite and without loosing transactional semantics is really rather hard. And having the capability of doing it with a rewrite is better than not
  having it at all.

You can see the patch that added the SET (LOGGED|UNLOGGED). The implementation hasn't changed much, though there was a plan to fix it that acknowledged the problems

  this design lead us to performance problems with large relations because we need to rewrite the entire content of the relation twice, one into a new heap and other into the WAL, so this project will change the current desing of the mechanism of change an unlogged table to logged without the need to rewrite the entire heap, but just by removing the init forks and if the wal_level != minimal we'll write the contents to the WAL too.

But it seems no more work was done. Looking at the code you can see it,

case AT_SetLogged:        /* SET LOGGED */
    ATSimplePermissions(rel, ATT_TABLE);
    tab->chgPersistence = ATPrepChangePersistence(rel, true);
    /* force rewrite if necessary; see comment in ATRewriteTables */
    if (tab->chgPersistence)
    {
        tab->rewrite |= AT_REWRITE_ALTER_PERSISTENCE;
        tab->newrelpersistence = RELPERSISTENCE_PERMANENT;
    }

Checking that comment,

 * There are two reasons for requiring a rewrite when changing
 * persistence: on one hand, we need to ensure that the buffers
 * belonging to each of the two relations are marked with or without
 * BM_PERMANENT properly.  On the other hand, since rewriting creates
 * and assigns a new relfilenode, we automatically create or drop an
 * init fork for the relation as appropriate.

So you can see, that a rewrite is still required. I guess.

Potential solution

You may be better of copying all of the data into the table on the same transactions that creates the table. Which disables the rewrite, and is mentioned in the docs.

  In minimal level, WAL-logging of some bulk operations can be safely skipped, which can make those operations much faster (see Section 14.4.7). Operations in which this optimization can be applied include:

    COPY into tables that were created or truncated in the same transaction

This would skip the WAL write and heap rewrite.


How can I rename an auto-named constraint when I get this error 15248?

You can use sp_rename manually.

Note that this is fixed in SSMS 2012 (I don't remember if I have SP1 installed or not).

EXEC sp_rename
    @objname = N'[myschema].[DF__AccountTy__Allow__7E3AEC52]',
    @newname = N'DF_whatever',
    @objtype = 'OBJECT';


How to use a postgres variable in the select clause

SQL has no support for variables, this is only possible in procedural languages (in Postgres that would e.g. be PL/pgSQL).
The way to to this in plain SQL is to use a CTE (which is also a cross platform solution and not tied to any SQL dialect):
with vars (foo) as (
  values ('bar')
)
select foo
from vars;

like injecting constant values where a select is providing the input to an insert table when copying from a staging table.

Well you don't need a variable for that:
insert into target_table (c1, c2, c3)
select col_1, col_2, 'some value'
from staging_table;


Keep in mind that Oracle manages the FRA space and will delete files if they are no longer needed, to make space.  You can have FRA 90% used, and it's OK.  For example, archive logs may be backed up to tape, but they will remain in the FRA for faster access if the space is not needed by other files.

First - check for reclaimable space with SELECT * FROM V$RECOVERY_FILE_DEST;. The NAME field will either be a disk path, or an ASM disk group.  If space is reclaimable, then the files should automatically be cleaned up.

Second - check for free space on the device that stores your FRA (NAME from V$RECOVERY_FILE_DEST).  If you have space, you can increase the amount of space you allow FRA to use (see DB_RECOVERY_FILE_DEST_SIZE).  https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams061.htm#REFRN10235

  DB_RECOVERY_FILE_DEST_SIZE specifies (in bytes) the hard limit on the total space to be used by target database recovery files created in the flash recovery area.

More information is needed to know if any of the files can be cleaned up.


MongoDB delayed member replication mechanism

The implementation is as per your first suggestion: a delayed secondary applies operations from the source oplog based on the delay. Your concern is also valid: if the source oplog period is insufficient to cover the secondary delay, the delayed secondary will become stale and require a re-sync.

A delayed secondary will help you with some data recovery scenarios (for example, a collection drop that hasn't been applied yet), but for more comprehensive recovery options I would consider a continuous backup solution such as MongoDB Cloud Manager (or MongoDB Ops Manager, which is the on-premise equivalent). Cloud Manager Backup provides continuous backups with queryable snapshots that allow you to preview and restore a subset of data. Base snapshots are taken every 6 hours by default, with a customisable snapshot interval and retention policy.


Virtualizing an Oracle RAC isn't necessarily a stupid idea, but it should only be done with a full understanding of the benefits and risks.  You are asking questions, so that is a good step in the right direction.

Your question doesn't say which virtualization technology, the Oracle version, or the edition.  For now I will assume this is for production on VMWare with the enterprise edition of Oracle 11.2.0.2+.

Virtualization provides some of the same benefits of RAC, though in a dramatically different way.  Both can reduce or eliminate downtime due to some hardware failures, and both can allow servers to be restarted without affecting availability.  RAC increases capacity and allows for rolling upgrades, which are things virtualization can't do.  VMware on the other hand can allow servers to be restarted, hardware to be changed, and storage to be relocated all without bringing any instances down.

Licensing

With VMWare the entire server has to be licensed even if it will be running multiple virtual machines only one of which is for Oracle.  This can either be a good thing or a bad thing.  If you are virtualizing Oracle onto an existing cluster with other VMs, then all the machines in the cluster will need Oracle licenses.  This could significantly increase your licensing costs likely to the point of making the purchase of additional hardware miniscule in comparison.

On the other hand, a VMWare cluster dedicated to Oracle can allow virtualized instances to float to less busy nodes and under-utilized hardware can handle multiple instances, potentially decreasing the overall hardware costs and licensing costs.

Support

Oracle's official stance for running databases on VMWare can be found in ID 249212.1, which says certification has not been done for it's products on VMWare. Since virtualization can be viewed as a hardware layer, this would be in keeping with the general lack of hardware certification.

The following note was added with 11.2.0.2:

  NOTE:  Oracle has not certified any of its products on VMWare.  For
  Oracle RAC, Oracle will only  accept Service Requests as described in
  this note on Oracle RAC 11.2.0.2 and later  releases.

Read the entire note for the full context, but it seems that Oracle is beginning to make allowances for even RAC on VMWare.

Application Support

If your applications are all RAC aware and can gracefully handle the loss of an instance, then some of the benefits that VMWare add would not be as worthwhile.  On the other hand, if the applications are not RAC aware and will need to re-connect anyway, then the transparency of VMWare VMotion would be more desirable.

Complexity/Flexibility

The combination of RAC and virtualization allows for a lot of flexibility, but also increases complexity considerably.  To restart a server you could VMotion all the instances off of that node to other nodes using VMWare, or you could bring the instances on that box down using RAC. Each option has pros and cons that you would have to consider.

Thoughts

Since virtualization provides some of the benefits of RAC, you could abandon RAC and potentially save a lot of licensing cost and reduce the overall complexity.
Moving multiple nodes of the RAC to the same virtual machine host would be a very bad idea.  You would loose some of the benefits of RAC while increasing the complexity and decreasing the availability.
It will likely all come down to licensing.  If you have to license even one additional CPU due to the move to VMWare, it would likely cost less to just buy more hardware.  Of course there are many factors that could affect this.

There is much more information that could be discussed on this subject, so please don't use this information here as your sole source in making your decision.


In MySQL, when using InnoDB, what happens to the way data is laid out on disk when the primary key is dropped?

If there is a not null unique key, that will be used as an internal primary key. If there is no candidate for a primary key, a 6-byte internal identified will be used as a hidden primary key that, unlike Oracle's ROWID, it cannot be accessed by the user.

As that implies that potentially an arbitrary value will be used to cluster the data, it is always recommended to have an explicit primary key.

When a new primary key is added, essentially the table (B+tree) is fully rebuilt around the new primary key value, including the secondary keys. That is why any ALTER TABLE operation involving the primary key will be very costly (and in MySQL 5.6 cannot be done online).


how to drop job with lowercase name ORACLE (=> ORA-27475 )

When you need to work with case-sensitive identifiers, you need to put them between double quotation marks, otherwise the database handles them as case-insensitive and automatically converts to uppercase.

begin
  dbms_scheduler.create_job
  (
   job_name             => '"job1"',
   job_type             => 'plsql_block',
   job_action           => 'begin null; end;'
  );
end;
/

select job_name from dba_scheduler_jobs where job_name = 'job1';

JOB_NAME
------------------------------
job1

This fails:

begin
  dbms_scheduler.drop_job('job1');
end;
/

Error report -
ORA-27475: "BP.JOB1" must be a job
...

But this works:

begin
  dbms_scheduler.drop_job('"job1"');
end;
/

PL/SQL procedure successfully completed.


How to relate 2 user profile tables to a single login table?

A Login maybe a Student or an Employee.

It sounds like you trying to define a FK relationship from Login to both Student and Employee, this is not correct.  You should be defining a FK relationship from Student to Login and Employee to Login

Edit for clarification:

If the PK emp_id for Employee and PK std_id for Student are the FK field then there can only be 1 for each given Login (user_id value).


You can use a view if all the objects returned are scalars not lobs.  In this case if you returned the first 4000 characters of the LOB that should work as the documentation indicates that using a function that creates a temporary lob on the source is allowed as long as the result that is returned is not a LOB.
The documentation for 12 is here. It does not say that creating a view will work so in the Oracle way that means only what they describe will work:

create table t as select * from ...
insert into t select * from ...
use a function that returns a scalar such as DBMS_LOB.SUBSTR(your_lob, 32767, 1);

Another workaround is to create your view with trimmed lobs on the source database and then create a view of the view on the target database.
Note the little gotcha where selecting LOBS into objects in the destination automatically converts it to the character set of the destination. The workaround is to cast to RAW and select then cast back.


SQL Server query with date range in WHERE - execution plan uses clustered PK instance not date column index

What is the datatype of itemTimestamp? Is that column indexed? Is the column nullable?

Assuming the answers are datetime, Yes, and No respectively you might consider

WHERE  a.itemTimestamp >= isnull(@timestamp_start, '17530101')
       AND a.itemTimestamp < isnull(@timestamp_end, '99991231')

If you were to change to datetime2 and actually store dates before 1753 you would need to change the lower bound condition to 00010101. This also won't bring back dates matching the higher bound of 9999-12-31 - but in the real world this is unlikely to cause a problem.


As long as we don't have sample data, I built simple table with three columns.

Sample solution to achive your problem:

CREATE TABLE TenPercent (Id INT IDENTITY(1,1), Name VARCHAR(256), Listprice NUMERIC(10,2));
GO

INSERT INTO TenPercent (Name,Listprice)
SELECT * FROM (VALUES ('Name',100.00)) T(Name,Listprice);
GO

CREATE TRIGGER TenPercent_Guard ON TenPercent
FOR UPDATE
AS
BEGIN
IF (UPDATE(ListPrice) AND
    EXISTS (SELECT * FROM TenPercent tp
        JOIN DELETED d ON tp.Id=d.Id AND tp.Listprice>d.Listprice*1.1))
    THROW 50000,'You can''t UPDATE Listprice more then 10 percent.',1;
END
GO

UPDATE TenPercent
SET Listprice=150;
GO

SELECT * FROM TenPercent;
GO

UPDATE TenPercent
SET Listprice=110;
GO

SELECT * FROM TenPercent;
GO

I used a JOIN in the trigger body to achive a situation when more then one row will be updated.

Obviously you should fit this solution to your table structure.


One option is

SELECT *
  FROM tabela
 WHERE  'OK' =
         CASE WHEN   1 = :parametro and coluna = 1 then 'OK'
              WHEN   0 = :parametro and coluna in (2, 3) THEN 'OK'
         END


What (if any) impact to mirrored databases will occur after applying -t1222 and restarting the server?

Since 1222 writes deadlock information to the log, the impact to mirroring itself will be nothing. Secondary effects should also be intangible in a normal environment. I'd only be worried if, for some reason, you have so many deadlocks that the Errorlog ends up filling your drive as this is fairly benign.

Also, by default, the extended events session does track deadlock information but it may not have all of the deadlock graph given the size of the deadlock graph.


How to create Unicode parameter and variable names

Well, identifiers are always Unicode / NVARCHAR, so technically you can't create anything that doesn't have a Unicode name 🙃.

The problem you are having here is due entirely to the classification of the character(s) being used. The rules for regular (i.e. non-delimited) identifiers are:

First letter must be:

A letter as defined by the Unicode Standard 3.2.
underscore (_), at sign (@), or number sign (#)

Subsequent letters can be:

Letters as defined in the Unicode Standard 3.2.
Decimal numbers from either Basic Latin or other national scripts.
underscore (_), at sign (@), number sign (#), or dollar sign ($)

Embedded spaces or special characters are not allowed.
Supplementary characters are not allowed.

I bolded the only rules that matter in this context. The reason that the "First letter" rules are not relevant here is that the first letter in all local variables and parameters is always the "at sign" @.

And to be clear: what is considered a "letter" and what is considered a "decimal digit" is based upon the properties that each character is assigned in the Unicode Character Database. Unicode assigns many properties to each character, such as: is_uppercase, is_lowercase, is_digit, is_decimal, is_combining, etc, etc. This is not a matter of what we mortals would consider letters or decimal digits, but which characters have been assigned these properties. These properties are often used in Regular Expressions to match on "punctuation", etc. For example, \p{Lu} matches any upper-case letter (across all languages / scripts), and \p{IsDingbats} matches any "Dingbats" character.

So, in your attempt to do:

DECLARE @¯\_(ツ)_/¯ INT;

only the _ (underscore or "low line") and ツ (Katakana Letter Tu U+30C4) characters fit into those rules. Now, all of the characters in ¯\_(ツ)_/¯ are fine for delimited identifiers, but unfortunately it seems that variable / parameter names and GOTO labels cannot be delimited (although cursor names can be).

So, for variable / parameter names, since they cannot be delimited, you are stuck with using only characters that qualify as being either "letters" or "decimal digits" as of Unicode 3.2 (well, according to the documentation; I need to test if classifications have been updated for newer versions of Unicode since classifications are handled differently than sort weights).

HOWEVER #1, things are not as straight-forward as they should be. I have now been able to complete my research and have found that the stated definition is not entirely correct. The precise (and verifiable) definition of which characters are valid for regular identifiers is:

First character:

Can be anything classified in Unicode 3.2 as "ID_Start" (which does include "Letters" but also "letterlike numeric characters")
Can be _ (low line / underscore) or ＿ (fullwidth low line)
Can be @, but only for variables / parameters
Can be #, but if schema-bound object, then only for Tables and Stored Procedures (in which case they indicate that the object is temporary)

Subsequent characters:

Can be anything classified in Unicode 3.2 as "ID_Continue" (which includes "decimal" numbers, but also "spacing and nonspacing combining marks", and "connecting punctuation marks")
Can be @, #, or $
Can be any of the 26 characters classified in Unicode 3.2 as format control characters

(fun fact: the "ID" in "ID_Start" and "ID_Continue" stands for "Identifier". Imagine that ;-)

According to "Unicode Utilities: UnicodeSet":

Valid starting characters

[:Age=3.2:] & [:ID_Start=Yes:]

-- Test one "Letter" from each of 10+ languages, as of Unicode 3.2
DECLARE @ᔠᑥᑒᏯשፙᇏᆇᄳᄈლဪඤaൌgೋӁｳﺲﶨ   INT;
-- works

-- Test a Supplementary Character that is a "Letter" as of Unicode 3.2
DECLARE @ INT;-- Mathematical Script Capital W (U+1D4B2)
/*
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near '0xd835'.
*/

Valid continuation characters

[:Age=3.2:] & [:ID_Continue=Yes:]

-- Test various decimal numbers, but none are Supplementary Characters
DECLARE @६৮༦൯௫୫９ INT;
-- works (including some Hebrew and Arabic, which are right-to-left languages)

-- Test a Supplementary Character that is a "decimal" number as of Unicode 3.2
DECLARE @ INT; -- MATHEMATICAL DOUBLE-STRUCK DIGIT FOUR (U+1D7DC)
/*
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near '0xd835'.
*/
-- D835 is the first character in the surrogate pair D835 DFDC that makes up U+1D7DC

HOWEVER #2, not even searching the Unicode database can be that easy. Those two searches do produce a list of valid characters for those categorizations, and those characters are from Unicode 3.2, BUT the definitions of the various categorizations changes across versions of the Unicode Standard. Meaning, the definition of "ID_Start" in Unicode v 10.0 (what that search is using today, 2018-03-26) is not what it was in Unicode v 3.2. So, the online search cannot provide an exact list. But you can grab the Unicode 3.2 data files and grab the list of "ID_Start" and "ID_Continue" characters from there to compare to what SQL Server actually uses. And I have done this and confirmed an exact match to the rules I stated above in "HOWEVER #1".

The following two blog posts detail the steps taken to find the exact list of characters, including links to the import scripts:

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 1
The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 2

Finally, for anyone that just wants to see the list and is not concerned with what it took to discover and verify it, you can find that here:

Completely Complete List of Valid T-SQL Identifier Characters
(please give the page a moment to load; it's 3.5 MB and almost 47k lines)

Regarding "valid" ASCII characters, such as / and -, not working: the issue has nothing to do with whether or not the characters are also defined in the ASCII character set. In order to be valid, the character has to have either the ID_Start or ID_Continue property, or be one of the few custom characters noted separately. There are quite a few "valid" ASCII characters (62 of the 128 total — mostly punctuation and control characters) that are not valid in "Regular" Identifiers.

Regarding Supplementary Characters: while they certainly can be used in delimited identifiers (and the documentation does not appear to be stating otherwise), if it is true that they cannot be used in regular identifiers, that is most likely due to them not being fully supported in built-in functions prior to Supplementary Character-Aware Collations were introduced in SQL Server 2012 (they are treated as two individual "unknown" characters), nor could they even be differentiated from each other in non-binary Collations prior to the 100-level Collations (introduced in SQL Server 2008).

Regarding ASCII: 8-bit encodings are not being used here since all identifiers are Unicode / NVARCHAR / UTF-16 LE. The statement SELECT ASCII('ツ'); returns a value of 63 which is a "?" (try: SELECT CHAR(63); ) since that character, even if prefixed with an upper-case "N", is certainly not in Code Page 1252. However, that character is in the Korean Code Page and it produces the correct result, even without the "N" prefix, in a Database with a Korean default Collation:

SELECT UNICODE('ツ'); -- 12484

Regarding the first letter affecting the outcome: this is not possible since the first letter for local variables and parameters is always @. The first letter that we get to control for these names is actually the 2nd character of the name.

Regarding why local variable names, parameter names, and GOTO labels cannot be delimited: I suspect this is due to these items being a part of the language itself and not something that will find its way into a system table as data.


What is actually happening when postgresql discovers the trigger file and promotes it self from slave to master?

A replica server (whether running as hot_standby or not) is running in what PostgreSQL calls "recovery mode". As the name suggests, this was originally developed to perform recovery from an unsafe stop: in this situation, it uses the recovery.conf file which defines how to find transaction logs (pg_xlog files) and keeps applying them until no more are available, and then exits from recovery mode into "normal" mode, and starts generating new transaction logs itself. When exiting recovery mode, the recovery.conf file is renamed to recovery.done, so that a subsequent restart goes directly to normal mode.

The changes from this process for a replication slave are:

the transaction log data may not be coming from log files, but streamed from the master server: recovery.conf specifies which
replicas do not exit recovery mode when no more data is available, they wait until the trigger is fired.

So due to the renaming of recovery.conf, restarting your newly-elected master preserves the property of it being a master.

However, one point to note: after recovery has completed and a server transitions from being in recovery to being in normal mode, it changes "timeline". Essentially, at this point you have forked your database: one version is the possible continuation of transaction logs from wherever they were coming from, and one version (the new version) is from the database that has just finished recovery. But you can only apply log files from a master server that is on the same timeline! This is why, after doing a failover, you need to rebuild your old master server from the new master, rather than just starting it to let it "catch up".

(This above paragraph is subject to some doubt as my experience with thee failovers is currently limited, but it's from my understanding of the documentation, and how the system works overall).

Also note that the hot_standby flag means only that a database cluster will accept connections and allow read-only queries even while it is in recovery mode: not a normal situation for a database that is recovering from a crash. Once a server is running in "normal mode", this setting is irrelevant, so it is not a problem to have it set on the old slave/new master.


SQL Server - Required Permissions For Executing Stored Procedures With SHOWPLAN ON

The user must have permission to run the stored procedure at the minimum to get the showplan information.  You can just grant the user access to run the one stored procedure and not all of them.

GRANT EXECUTE ON sp_name TO username;


If you just want to disambiguate two rows with similar content, you can use the ROWID functionality in SQLite3, which helps uniquely identify each row in the table.

Something like this:

DELETE FROM sms WHERE rowid NOT IN (SELECT min(rowid) FROM sms GROUP BY address, body);

should work to get you the rows with the minimum rowid, which will be the first occurrence of that row in the table.

Be sure to run this in a transaction however, to make sure that it does exactly what you want, and of course, take a backup beforehand. =)


varchar storage and comparisons in SQL Server 2008

Add a persistent calculated field that contains a CHECKSUM on the 5 fields, and use that to perform the comparisons.

The CHECKSUM field will be unique for that specific combination of fields, and is stored as an INT that results in a much easier target for comparisons in a WHERE clause.

USE tempdb; /* create this in tempdb since it is just a demo */

CREATE TABLE dbo.t1
(
    Id       bigint constraint PK_t1 primary key clustered identity(1,1)
    , Sequence int
    , Parent   int not null constraint df_T1_Parent DEFAULT ((0))
    , Data1    varchar(20)
    , Data2    varchar(20)
    , Data3    varchar(20)
    , Data4    varchar(20)
    , Data5    varchar(20)
    , CK AS CHECKSUM(Data1, Data2, Data3, Data4, Data5) PERSISTED
);

GO

INSERT INTO dbo.t1 (Sequence, Parent, Data1, Data2, Data3, Data4, Data5)
VALUES (1,1,'test','test2','test3','test4','test5');

SELECT *
FROM dbo.t1;
GO

/* this row will NOT get inserted since it already exists in dbo.t1 */
INSERT INTO dbo.t1 (Sequence, Parent, Data1, Data2, Data3, Data4, Data5)
SELECT 2, 3, 'test', 'test2', 'test3', 'test4', 'test5'
WHERE Checksum('test','test2','test3','test4','test5') NOT IN (SELECT CK FROM t1);

/* still only shows the original row, since the checksum for the row already
exists in dbo.t1 */
SELECT *
FROM dbo.t1;

In order to support a large number of rows, you'd want to create an NON-UNIQUE index on the CK field.

By the way, you neglected to mention the number of rows you are expecting in this table; that information would be instrumental in making great recommendations.

In-row data is limited to a maximum of 8060 bytes, which is the size of a single page of data, less the required overhead for each page.  Any single row larger than that will result in some off-page storage of row data.  I'm certain other contributors to http://dba.stackexchange.com can give you a much more concise definition of the engine internals regarding storage of large rows.  How big is your largest row, presently?

If items in Data1, Data2, Data3... have the same values occurring in a different order, the checksum will be different, so you may want to take that into consideration.

Following a brief discussion with the fantastic Mark Storey-Smith on The Heap, I'd like to offer a similar, although potentially better choice for calculating a hash on the fields in question.  You could alternately use the HASHBYTES() function in the calculated column.  HASHBYTES() has some gotchas, such as the necessity to concatenate your fields together, including some type of delimiter between the field values, in order to pass HASHBYTES() a single value.  For more information about HASHBYTES(), Mark recommended this site.  Clearly, MSDN also has some great info at http://msdn.microsoft.com/en-us/library/ms174415.aspx


Running a Job from a Stored Procedure in another server?

With a linked server - much more reliable and secure than xp_cmdshell:

EXEC Server2.msdb.dbo.sp_start_job @job_name = N'Job2';


Is there a way to force an index to stay in memory with SQL Server 2008?

There used to be a DBCC PINTABLE command but I believe that stopped working in 6.5 or maybe 7.0. The statement will probably still suggest that it worked if you try it, but it just returns, it really is a no-op.

Unfortunately there's not really any way to control which indexes are kept in cache - the best workaround I know of for tables that are periodically hot is to keep them hot manually (which you've already described in your question).

For which indexes are in memory, you can get a rough idea from sys.sm_os_buffer_descriptors. I published a tip about this:

http://www.mssqltips.com/sqlservertip/2393/determine-sql-server-memory-use-by-database-and-object/


How to INSERT INTO ... RETURNING for matrices in PostgreSQL?

Looks good, though I have to wonder if this isn't a better match for PostGIS's raster, or another tool entirely.

INSERT INTO c_matrices (measurement_id, event_index_start, event_index_end, c_matrix_data, c_size_in_bytes)
    VALUES (1, 2, 3, 'testBinaryData', 1234)
    RETURNING c_id -- generating a serial id -- TODO right?
    FROM   ins1;

Not sure what this is supposed to do. To have a FROM you must have a SELECT, RETURNING comes after FROM. Close though.

-- Sample data.
# CREATE TABLE foo (id serial, b int);

-- Demo
# INSERT INTO foo (b) SELECT 42 RETURNING id;
 id
----
  1
(1 row)


Do IOTs generate significantly more undo per block for deletes?

No, this type of delete generates roughly the same quantity of undo regardless of whether it is an IOT or heap.

In contrast, inserting into the IOT generates far more undo.

heap:

drop table foo;
create table foo( id integer not null,
                  val char(100) default 'A' not null);
--
insert into foo(id) select level from dual connect by level<100000;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK
----------------------
149
*/
--
analyze table foo compute statistics;
--
select blocks from all_tab_statistics where table_name='FOO';
/*
BLOCKS
----------------------
3274
*/
--
delete from foo;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK
----------------------
5264
*/

IOT:

drop table foo;
create table foo( id integer not null primary key,
                  val char(100) default 'A' not null) organization index;
--
insert into foo(id) select level from dual connect by level<100000;
--
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK
----------------------
2030
*/
--
analyze table foo compute statistics;
select leaf_blocks from all_ind_statistics where table_name='FOO';
/*
LEAF_BLOCKS
----------------------
2942
*/
--
delete from foo;
select used_ublk
from v$transaction join v$session on(addr=taddr)
where sid=sys_context('USERENV','SID');
/*
USED_UBLK
----------------------
4741
*/


Is it possible to use a default partition for all reads?

If there are no situations where you need to query soft-deleted records, a simpler mechanism would be to have a table just for deleted records. You could then implement a scheduled job to copy all is_deleted = true records to the historical table and remove the originals from the source table.
Doing this would ensure the primary table contained all of the active records and no application queries would need to be updated.
If writes don’t happen very often, you could even look at using an AFTER UPDATE trigger to automatically copy (and maybe remove) soft-deleted records from the table as they’re invalidated.
This, I believe, would be much simpler to implement if going through all of the application SQL queries is untenable


could not load library "/usr/pgsql-10/lib/postgis-2.5.so": /usr/pgsql-10/lib/postgis-2.5.so: undefined symbol: GEOSFrechetDistanceDensify

Had the same problem on CentOS after an update. I had 3.6 AND 3.7 installed.

$yum list installed | grep geos
geos36.x86_64                           3.6.3-1.rhel7                  @pgdg10
geos37.x86_64                           3.7.0-1.rhel7                  @pgdg10

I had to remove the 3.6:

$yum remove geos36

And restart PostgreSQL

$systemctl restart postgresql-10


DB2 9.7 - Find rows inserted by a specific trigger

You are correct in your assumption – there is nothing done at the database level that would track the source of a row.  You would need to modify your history table definition and trigger logic to add this kind of tracking.


When restoring a SQL Server DB from dump files on a remote NAS share, copy before restore or restore directly?

In my experience, there has been nothing to gain by copying files locally before carrying out the restore. If the target of the restore has sufficient IO capacity, you should be able to restore from the network share at very close to the speed of a file copy.


How to go about extending volumes on SQL Server boxes?

You can extend disks while the database is online, but I will recommend you plan for a brief 15 - 30 minute outage during which you can take the services offline.

I've extended disks hundreds of times while databases were online and running without issues.  However one time, and only one time at this point in my career, the operation caused corruption in some SharePoint Content databases that were running and I lost a fair chunk of my hair getting things back up and working.

If you want/have to do this while the database is running, the key is that while you're extending the disks, you do NOT want any file growth activities occurring lest they try to grab some of the not-quite-ready new space.  I think this is what happened in my situation as our Sharepoint environments were treated as a "better" fileshare for the company and I'm pretty sure an auto-growth event occurred during the disk extend operation.

For what it's worth, MS also recommends you turn off the services while performing this operation.  After the disks are extended also be sure to run a DBCC CHECKDB to be certain no corruption occurred.


Connecting to Oracle SQL Developer locally on Mac?

SQL Developer doesn't come with an Oracle database, it is a client application. You can't "connect to SQL Developer", you use SQL Developer to connect to an existing database.

There are no supported editions of Oracle database for Mac OS X at this time, the latest that was available was 10gR2. You'll need to set one up in a VM on your machine (Windows or Linux, using the Oracle XE edition seems like it would fit your need), or see if you can somehow connect to your lab databases (possibly via a VPN to your school if they have that available).


If there is only one item of a device type from a manufacturer in inventory, then this would be 3NF.  This would be the case for the information provided.

However, it is likely that you might have more than one type of LG boiler in inventory table.  As you dig into the design more details may arise which break the 3NF conditions.

Typically inventory items mighte have a natural key of manufacturer and manufacturer's part number.  This would be complicated if inventory items which can be sourced from different manufacturers are sold or used interchangably.  In this case, you would have an inventory (item) id for the inventory table.  This would link to a table with a tri-part key (inventory-id, manufacturer-id, manufactuers-part-number).